Skip to Content

The R3COPY (for releases 4.X) is a supported database-specific (backup/restore) procedure to perform faster Homogeneous System Copies.

The common script available correspond to Oracle 7x or 8x, unfortunately at this moment most of current systems are running on Oracle 9x, so i want to share with you in this blog the modified script to perform this system copy with Oracle 9x.

NOTE: The procedure to perform this kind of system copies on SAP NetWearver releases is known as OraBRcopy, please refer to the NW ABAP system copy guide.

Detailed information about the procedure can be found at:

Homogenous System Copy Guide – Chapter 6

– SAP Notes 89188 & 659509

The script:

#!/bin/sh

#

  1. @(#)R3copy      20.3.4.1   SAP  97/10/09

#====================================================================

  1.       SAP AG Walldorf                                                    

  2.       Systeme, Anwendungen und Produkte in der Datenverarbeitung         

  3.                                                                          

  4.       (C) Copyright SAP AG 1995                                          

#====================================================================

#====================================================================

  1. NAME:    R3copy

#

  1. PURPOSE: Copy of a customer system via backup.

  2.           R3copy generates the SQL-script CONTROL.SQL on

  3.           the source system. CONTROL.SQL describes the whole

  4.           database structur.

  5.           R3copy creates new control files at the target system.

  6.           A rename of the internal database name is possible.

  7.           

#

  1. AUTHOR:  d002359 

  2.          

  3.           changed GROUP 1 … 4 to GROUP 11 … 14 //d020894          

#

#

  1. Global variable definition, database independent

#

NAME=`basename $0`

LOG=$SAPDATA_HOME/sapreorg/$NAME.log

TMP_FILE=$SAPDATA_HOME/sapreorg/$NAME.tmp

TMP_CHK=$SAPDATA_HOME/sapreorg/$NAME.chk

TMP_AWK=$SAPDATA_HOME/sapreorg/$NAME.awk

CONTROLFILE=$SAPDATA_HOME/sapreorg/CONTROL.SQL

LINE=”—-


TRUL=’tr “abcdefghijklmnopqrstuvwxyz” “ABCDEFGHIJKLMNOPQRSTUVWXYZ”‘

TRLL=’tr “ABCDEFGHIJKLMNOPQRSTUVWXYZ” “abcdefghijklmnopqrstuvwxyz”‘

###

      1. FUNCTIONS

###

#====================================================================

#

  1. FUNCTION: init_log

#

  1. PURPOSE:  Initialize logfile

#

init_log()

{

clear

echo “The Log is written to &gt $LOG < “

sleep 3

echo ” ” &gt&gt $LOG

  1. Permission

chmod 600 $LOG

echo $LINE &gt&gt $LOG

echo “$NAME started at:” &gt&gt $LOG

echo `date` &gt&gt $LOG

echo $LINE &gt&gt $LOG

echo ” ” &gt&gt $LOG

echo ” ” &gt&gt $LOG

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: suppress_check

#

  1. PURPOSE:  Determine how to suppress newline with echo command.

#

supress_check()

{

case ${N}$C in

    “”) if echo “\c” | grep c &gt/dev/null 2&gt&1; then

            N=’-n’; export N

        else

            C=’\c’; export C

        fi ;;

esac

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: new_screen

#

  1. PURPOSE: user interface

#

  1. PARAMETER: new_screen <string&gt

#

new_screen()

{

clear

echo $LINE | tee -a $LOG

echo “$NAME – $1” | tee -a $LOG

date &gt&gt $LOG

echo $LINE | tee -a $LOG

echo ” ” | tee -a $LOG

echo ” ” | tee -a $LOG

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: confirm

#

  1. PURPOSE: user interface

#

#

confirm()

{

echo ” ” | tee -a $LOG

echo “Confirm this message – o.k. (y/n)?”

read answer

echo $answer &gt&gt $LOG

case $answer in

     y | Y | j | J ) echo “Values confirmed” | tee -a $LOG

             sleep 3 ;;

    

  • ) echo “Values not confirmed – please start again” | tee -a $LOG

         exit ;;

esac

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: environment_check

#

  1. PURPOSE:  Checking environment variables

#

environment_check()

{

sleep 1

case $ORACLE_HOME in

    “”) echo “==== ERROR occured: ORACLE_HOME not set ! ====” | tee -a $LOG

        echo “Set and export ORACLE_HOME, then restart script.” | tee -a $LOG

        exit 1 ;;

esac

case $ORACLE_SID in

    “”) echo “==== ERROR occured: ORACLE_SID not set ! ====” | tee -a $LOG

        echo “Set and export ORACLE_SID, then restart script.” | tee -a $LOG

        exit 1 ;;

esac

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: user_check

#

  1. PURPOSE:  Check login user, confirm all settings

#

user_check()

{

USER=”`id | awk -F\( ‘{print $2}’ | awk -F\) ‘{print $1}’`”

ORAUSER=”ora`echo $ORACLE_SID | $TRLL`”

if [ “$USER” != “$ORAUSER” ]; then

   echo “Please start  $NAME as user $ORAUSER” | tee -a $LOG

   exit

fi

#

  1. Confirm environment and user settings

#

new_screen “CONFIRM ENVIRONMENT AND USER SETTINGS”

echo ” ” | tee -a $LOG

echo “ORACLE_HOME  is &gt$ORACLE_HOME<” | tee -a $LOG

echo “ORACLE_SID   is &gt$ORACLE_SID<” | tee -a $LOG

echo “UNIX user    is &gt$ORAUSER<” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “Confirm this settings – o.k. (y/n)?”

read answer

echo $answer &gt&gt $LOG

case $answer in

     y | Y ) echo “Environment confirmed” | tee -a $LOG

             sleep 3 ;;

    

  • ) echo “Please verify: ” | tee -a $LOG

         echo ”   1. You are connected as user orasid?”  | tee -a $LOG

         echo ”   2. The database is running?” | tee -a $LOG

         echo ”   3. The environment is correct?”  | tee -a $LOG

         exit ;;

esac

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: startup_messages

#

  1. PURPOSE: security hints

#

#

startup_messages()

{

new_screen “Security hint”

echo “WARNING: ” | tee -a $LOG

echo “$NAME must only be used as described in the installation” | tee -a $LOG

echo “documentation. Improper usage may destroy your system!” | tee -a $LOG

confirm

new_screen “Usage of $NAME”

echo “$NAME will create an exact (homogeneous) copy of an existing”         | tee -a $LOG

echo “Oracle database. The data for the target system will come from”       | tee -a $LOG

echo “an offline backup or the file system of your source host.”            | tee -a $LOG

echo ” ”                                                                    | tee -a $LOG   

echo “$NAME actions on the source system:”                                  | tee -a $LOG

echo ”  – Generating the Script CONTROL.SQL”                                | tee -a $LOG

echo ”    $NAME generates the files CONTROL.SQL and init<SID&gt.ora,”         | tee -a $LOG

echo ”    which will be used at the target system. In addition”             | tee -a $LOG

echo ”    $NAME will backup the control file to trace. This file will”      | tee -a $LOG

echo ”    provide you with additional information, if the actual”           | tee -a $LOG

echo ”    configuration of the database differs from the SAP standard.”     | tee -a $LOG

echo ”  – Force log switches”                                               | tee -a $LOG

echo ”    $NAME forces n log switches to clean the redo logs.”              | tee -a $LOG

echo ”    This is necessary, because the CREATE CONTROLFILE at the”         | tee -a $LOG

echo ”    target system must be executed with the RESETLOGS option.”        | tee -a $LOG

echo ” ”                                                                    | tee -a $LOG

echo “$NAME actions on the target system:”                                  | tee -a $LOG

echo ”  – Executing the script CONTROL.SQL with $NAME.”                     | tee -a $LOG

   

confirm

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: exit_messages

#

  1. PURPOSE:

#

#

 

exit_messages()

{

new_screen “Exit $NAME”

echo “se06 … ” | tee -a $LOG

echo “licence …” | tee -a $LOG

echo “sapconf update …” | tee -a $LOG

confirm

exit

}

   

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: get_system_pass

#

  1. PURPOSE: prompt password for SYSTEM for database connect

#

#

get_system_pass()

{

new_screen “Password of database administration user SYSTEM”

echo “All database connects during this procedure are done as”

echo “user SYSTEM.”

echo “Please enter password for Oracle DBA user SYSTEM:$C”

stty -echo

read SYSTEM_PW

echo $N

stty echo

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: connect_check

#

  1. PURPOSE: try a dummy connect to confirm the password

#

#

connect_check()

{

echo “Connect to the database to verify, that the database is open”

sleep 3

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

END

grep ‘ORA-‘ $TMP_FILE

if test $? -eq 0

then

  echo ‘Error by Oracle connect…    exiting’ | tee -a $LOG

  echo ‘database not available or wrong password of user SYSTEM’ | tee -a $LOG

  exit 1

fi

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: check_offline_files

#

  1. PURPOSE: check database: does any offline file exists

#

#

check_offline_files()

{

new_screen “Checking offline datafiles or tablespaces”

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;

END

grep ‘OFFLINE’ $TMP_FILE

if test $? -eq 0

then

  echo ‘OFFLINE tablespaces or datafiles detected – see $LOG.’ | tee -a $LOG

  echo ‘This datafiles will be ignored at the target system!’ | tee -a $LOG

  echo ‘Please check, if the offline files are necessary.’ | tee -a $LOG

confirm

fi

echo “offline files checked.” | tee -a $LOG

sleep 3

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: make_initora

#

  1. PURPOSE:

make_initora()

{

new_screen “GENERATE INIT.ORA”

echo “SID of the source system: SID = $ORACLE_SID” | tee -a $LOG

echo “Please enter the SID of the target system (3 characters):” | tee -a $LOG

read answer

echo $answer &gt&gt $LOG

TARGET_SID=`echo $answer | $TRUL`

echo “generating target init.ora …” | tee -a $LOG

echo “sed -e ‘s/$ORACLE_SID/$TARGET_SID/g’ \

$ORACLE_HOME/dbs/init$ORACLE_SID.ora \

&gt $SAPDATA_HOME/sapreorg/init$TARGET_SID.ora” &gt $SAPDATA_HOME/sapreorg/tmp.sh

sh $SAPDATA_HOME/sapreorg/tmp.sh

echo “$SAPDATA_HOME/dbs/init$TARGET_SID.ora generated” | tee -a $LOG

sleep 3

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: make_cntrlscript

#

  1. PURPOSE:

make_cntrlscript()

{

new_screen “GENERATE CONTROL.SQL”

echo “generating $CONTROLFILE” | tee -a $LOG

echo “REM ====================================================================” &gt $CONTROLFILE

echo “REM CONTROL.SQL” &gt&gt $CONTROLFILE

echo “REM”             &gt&gt $CONTROLFILE

echo “REM       SAP AG Walldorf ” &gt&gt $CONTROLFILE

echo “REM       Systeme, Anwendungen und Produkte in der Datenverarbeitung” &gt&gt $CONTROLFILE

echo “REM”             &gt&gt $CONTROLFILE

echo “REM       (C) Copyright SAP AG 1995” &gt&gt $CONTROLFILE

echo “REM ====================================================================” &gt&gt $CONTROLFILE

echo ” ”               &gt&gt $CONTROLFILE

echo “REM Generated at:”  &gt&gt $CONTROLFILE

echo “REM”  `date` &gt&gt $CONTROLFILE

echo “REM on” &gt&gt $CONTROLFILE

echo “REM for target system $TARGET_SID” &gt&gt $CONTROLFILE

echo “REM SOURCE_OS=” `uname -a` &gt&gt $CONTROLFILE

echo ” ”               &gt&gt $CONTROLFILE

echo ” ”               &gt&gt $CONTROLFILE

echo ” ”               &gt&gt $CONTROLFILE

echo “CONNECT / AS SYSDBA” &gt&gt $CONTROLFILE

echo “STARTUP NOMOUNT;” &gt&gt $CONTROLFILE

echo “CREATE CONTROLFILE REUSE” &gt&gt $CONTROLFILE

echo “SETDATABASE $ORACLE_SID ” &gt&gt $CONTROLFILE

echo “LOGFILE” &gt&gt $CONTROLFILE

  1. The first select returns 0 rows, if no ORACLE mirroring

  2. If ORACLE mirroring is used, the first select returns

  3. all mirrored (exept one) logmembers of thread n “with a comma”

  4. The second select returns on logmember “without a comma”

echo “GROUP 11 ( ” &gt&gt $CONTROLFILE

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MEMBER || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 11

MINUS

SELECT ”” || MAX(MEMBER) || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 11;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

  1. now select the n’th logfile without a comma

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MAX(MEMBER) || ””

FROM V\$LOGFILE WHERE GROUP# = 11;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

echo “) SIZE 20 M,” &gt&gt $CONTROLFILE

echo “GROUP 12 ( ” &gt&gt $CONTROLFILE

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MEMBER || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 12

MINUS

SELECT ”” || MAX(MEMBER) || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 12;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

  1. now select the n’th logfile without a comma

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MAX(MEMBER) || ””

FROM V\$LOGFILE WHERE GROUP# = 12;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

echo “) SIZE 20 M,” &gt&gt $CONTROLFILE

echo “GROUP 13 ( ” &gt&gt $CONTROLFILE

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MEMBER || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 13

MINUS

SELECT ”” || MAX(MEMBER) || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 13;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

  1. now select the n’th logfile without a comma

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MAX(MEMBER) || ””

FROM V\$LOGFILE WHERE GROUP# = 13;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

echo “) SIZE 20 M,” &gt&gt $CONTROLFILE

echo “GROUP 14 ( ” &gt&gt $CONTROLFILE

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MEMBER || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 14

MINUS

SELECT ”” || MAX(MEMBER) || ”” || ‘ ,’

FROM V\$LOGFILE WHERE GROUP# = 14;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

  1. now select the n’th logfile without a comma

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/LOGFILES.TMP

SELECT ”” || MAX(MEMBER) || ””

FROM V\$LOGFILE WHERE GROUP# = 14;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/LOGFILES.TMP &gt&gt $CONTROLFILE

echo “) SIZE 20 M” &gt&gt $CONTROLFILE

  1. RESETLOGS necessary for rename SID

echo “RESETLOGS” &gt&gt $CONTROLFILE

echo “DATAFILE” &gt&gt $CONTROLFILE

  1. select datafiles into tempfile

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET VERIFY OFF;

SET TERMOUT OFF;

SPOOL $SAPDATA_HOME/sapreorg/DATAFILES.TMP

COL MAXDATA new_value MAXDATA;

SELECT MAX(FILE#) MAXDATA FROM V\$DBFILE;

SELECT ”” || NAME || ”” || ‘, ‘

FROM V\$DBFILE WHERE FILE# < &MAXDATA;

SELECT ”” || NAME || ””

FROM V\$DBFILE WHERE FILE# = &MAXDATA;

SPOOL OFF;

END

  1. append the result to CONTROL.SQL

grep “/ora” $SAPDATA_HOME/sapreorg/DATAFILES.TMP &gt&gt $CONTROLFILE

echo “MAXLOGFILES 16” &gt&gt $CONTROLFILE

echo “MAXLOGMEMBERS 3” &gt&gt $CONTROLFILE

echo “MAXDATAFILES 1022” &gt&gt $CONTROLFILE

echo “MAXINSTANCES 50” &gt&gt $CONTROLFILE

echo “ARCHIVELOG;” &gt&gt $CONTROLFILE

  1. now substitute source sid with target sid

  2. first the occurence of ” $ORACLE_SID “

cp $CONTROLFILE $TMP_FILE

echo “sed -e ‘s/ $ORACLE_SID / $TARGET_SID /g’ $TMP_FILE &gt $CONTROLFILE” &gt $SAPDATA_HOME/sapreorg/tmp.sh

sh $SAPDATA_HOME/sapreorg/tmp.sh

  1. then the occurence of “/$ORACLE_SID/”

cp $CONTROLFILE $TMP_FILE

echo “sed -e ‘s/\/$ORACLE_SID\//\/$TARGET_SID\//g’ $TMP_FILE &gt $CONTROLFILE” &gt $SAPDATA_HOME/sapreorg/tmp.sh

sh $SAPDATA_HOME/sapreorg/tmp.sh

echo “$CONTROLFILE generated!”

sleep 3

cat $CONTROLFILE &gt&gt $LOG

}

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: offline_actions

#

  1. PURPOSE: Target system, offline backup

#

#

offline_actions()

{

  1. shutdown abort – database may be started in a

  2. reset case

echo “CONNECT / AS SYSDBA;” &gt $TMP_FILE

echo “SHUTDOWN ABORT;” &gt&gt $TMP_FILE

echo “EXIT;” &gt&gt  $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

  1. Create the new controlfiles

$DBATOOL <<END &gt $TMP_FILE 2&gt&1

`cat $CONTROLFILE`

END

cat $TMP_FILE &gt&gt $LOG

grep ‘ORA-‘ $TMP_FILE

if test $? -eq 0

then

  echo ‘Error at creating controlfiles.’ | tee -a $LOG

  echo ‘see $LOG. exiting …’ | tee -a $LOG

  exit 1

fi

  1. startup open

echo “CONNECT / AS SYSDBA;” &gt $TMP_FILE

echo “ALTER DATABASE OPEN RESETLOGS;” &gt&gt $TMP_FILE

echo “EXIT;” &gt&gt  $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

exit_messages

}

 

#

  1. END OF FUNCTION

#

#====================================================================

  

#====================================================================

#

  1. FUNCTION: online_actions

#

  1. PURPOSE: Target system, online backup

#

#

online_actions()

{

  1. shutdown abort – database may be started in a

  2. reset case

echo “CONNECT / AS SYSDBA;” &gt $TMP_FILE

echo “SHUTDOWN ABORT;” &gt&gt $TMP_FILE

echo “EXIT;” &gt&gt  $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

  1. Create the new controlfiles

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $CONTROLFILE`

END

 

    

  1. Recovery and startup open

new_screen “RECOVER DATABASE AFTER ONLINE BACKUP”

echo “Please enter filenames of archives:” | tee -a $LOG

echo “CONNECT / AS SYSDBA;” &gt $TMP_FILE

echo “ALTER DATABASE MOUNT;” &gt&gt $TMP_FILE

echo “RECOVER DATABASE;” &gt&gt $TMP_FILE

echo “ALTER DATABASE OPEN;” &gt&gt $TMP_FILE

echo “EXIT;” &gt&gt  $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

     

exit_messages

}

#

  1. END OF FUNCTION

#

#====================================================================

  

#====================================================================

#

  1. FUNCTION: backup_to_trace

#

  1. PURPOSE:  Backup controlfile to trace

  2.           This procedure is usefull, if the actual configuration

  3.           of the database differs from the SAP standard.

#

backup_to_trace()

{

  1. new_screen “Confirm source system”

  2. echo “Please confirm, that $NAME is running on the source system” | tee -a $LOG

  3. echo “If database is not running, please start it up first!” | tee -a $LOG

  4. confirm

  5. get_system_pass;

  6. connect_check;

  7. check_offline_files;

  8. make_initora;

new_screen “Generating SQL commands into trace”

echo “ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;” | tee -a $LOG

$SQLPLUS SYSTEM/$SYSTEM_PW <<END &gt $TMP_FILE 2&gt&1

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

END

grep ‘ORA-‘ $TMP_FILE

if test $? -eq 0

then

  echo ‘ALTER DATABASE failed  … exiting’ | tee -a $LOG

  exit 1

fi

sleep 3;

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: source_system

#

  1. PURPOSE: check database and create control.sql

#

#

source_system()

{

new_screen “Confirm source system”

echo “Please confirm, that $NAME is running on the source system” | tee -a $LOG

echo “If database is not running, please start it up first!” | tee -a $LOG

confirm

get_system_pass;

connect_check;

check_offline_files;

make_initora;

make_cntrlscript;

backup_to_trace;

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: target_system

#

  1. PURPOSE: create controlfiles and start recovery

#

#

target_system()

{

new_screen “Confirm target system”

echo “Please confirm, that $NAME is running on the target system” | tee -a $LOG

confirm

#

  1. now check, if CONTROL.SQL exists

#

echo ” ” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “Check existance of $CONTROLFILE …”  | tee -a $LOG

if ; then

   echo “$CONTROLFILE exists – continue” | tee -a $LOG

else

   echo ” ” | tee -a $LOG

   echo “Cannot find $CONTROLFILE!” | tee -a $LOG

   echo “Please generate the script CONTROL.SQL at the” | tee -a $LOG

   echo “source system, copy it to /oracle/<SID&gt/sapreorg” | tee -a $LOG

   echo “at the target system, and restart $NAME.” | tee -a $LOG

   exit

fi

new_screen “Security hint”

echo “Improper usage may destroy your system … Continue? …” | tee -a $LOG

confirm

new_screen “Equality of OS”

echo “Support from SAP and ORACLE is only guaranteed, if source” | tee -a $LOG

echo “and target system have the same hardware, the same type of” | tee -a $LOG

echo “hard disks and the same disk management system.” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “source system:” | tee -a $LOG

      grep SOURCE_OS $CONTROLFILE | tee -a $LOG

echo “target system:” | tee -a $LOG

      uname -a | tee -a $LOG

echo ” ” | tee -a $LOG

echo “Please confirm, that the hardware is identical:” | tee -a $LOG

confirm

#

  1. online or offline backup ?

#

  1. d002359, 18.09.95: only offline supported at the moment

#

offline_actions

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: setDBATOOL

#

  1. PURPOSE: sets the variable DBATOOL to “$ORACLE_HOME/bin/sqlplus”

  2.             if this exe is available, else to “…/sqldba lmode=y”.

  3.             If non of those is available, we exit

#

#

setDBATOOL()

{

if test -x $ORACLE_HOME/bin/sqlplus; then

   DBATOOL=$ORACLE_HOME/bin/sqlplus” /nolog”

elif test -x $ORACLE_HOME/bin/sqldba; then

   DBATOOL=$ORACLE_HOME”/bin/sqldba lmode=y”

else

   echo “==== ERROR occured: non of sqlplus or sqldba found! ====” | tee -a $LOG

   echo “One of them must be located in $ORACLE_HOME/bin.” | tee -a $LOG

   exit 1

fi

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: setSQLPLUS

#

  1. PURPOSE: sets the variable SQLPLUS to “$ORACLE_HOME/bin/sqlplus”

  2.             if this exe is available, else we exit

#

#

setSQLPLUS()

{

if test -x $ORACLE_HOME/bin/sqlplus; then

   SQLPLUS=$ORACLE_HOME/bin/sqlplus

else

   echo “==== ERROR occured: sqlplus not found! ====” | tee -a $LOG

   echo “Check if it is located in $ORACLE_HOME/bin/.” | tee -a $LOG

   echo ”      and is executable.” | tee -a $LOG

   exit 1

fi

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: db_shutdown_immediate

#

  1. PURPOSE: database will be shutdown immediate

#

#

db_shutdown_immediate()

{

new_screen “Shutdown DB”

echo ” ” | tee -a $LOG

echo “The database will be shutdown!” | tee -a $LOG

confirm

echo “CONNECT / AS SYSDBA;”     &gt $TMP_FILE

echo “SHUTDOWN IMMEDIATE;”  &gt&gt $TMP_FILE

echo “EXIT;”                &gt&gt $TMP_FILE

$DBATOOL <<END | tee $TMP_CHK &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

grep assword $TMP_CHK

if test $? -eq 0

then

  echo “Connect internal failed ” | tee -a $LOG

  exit

fi

sleep 3

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: db_startup

#

  1. PURPOSE: database will be started up

#

#

db_startup()

{

new_screen “Startup DB”

echo “CONNECT / AS SYSDBA;” &gt $TMP_FILE

echo “STARTUP;”             &gt&gt $TMP_FILE

echo “EXIT;”                &gt&gt $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

sleep 3

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: switch_logfiles <how_many&gt

#

  1. PURPOSE:  forces <how_many&gt log switches

#

#

switch_logfiles()

{

new_screen “Forcing $NUMLOGGROUPS log switches”

echo “CONNECT / AS SYSDBA;”               &gt  $TMP_FILE

i=0

while test $i -lt $NUMLOGGROUPS

do

   echo “ALTER SYSTEM SWITCH LOGFILE;” &gt&gt $TMP_FILE

   i=`expr $i + 1`

done

echo “EXIT;”                           &gt&gt $TMP_FILE

$DBATOOL <<END &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

sleep 3

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: setNUMLOGGROUPS

#

  1. PURPOSE:  sets the variable NUMLOGGROUPS to the number of redo log

  2.           groups

#

  1.          

setNUMLOGGROUPS()

{

echo “CONNECT / AS SYSDBA;”     &gt $TMP_FILE

echo “SELECT COUNT(DISTINCT GROUP#) FROM V\$LOGFILE;” &gt&gt $TMP_FILE

echo “EXIT;”                                          &gt&gt $TMP_FILE

$DBATOOL <<END | tee $TMP_CHK &gt&gt $LOG 2&gt&1

`cat $TMP_FILE`

END

createNUMLOGGROUPSawk $TMP_AWK;

NUMLOGGROUPS=`awk -f $TMP_AWK $TMP_CHK`;

echo “Number of redo log groups detected: $NUMLOGGROUPS” &gt&gt $LOG 2&gt&1

}

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: createNUMLOGGROUPSawk

#

  1. PURPOSE:  creates awk script <script&gt for getting NUMLOGGROUPS

#

#

createNUMLOGGROUPSawk()

{

echo “”                                                | tee $1    &gt&gt $LOG 2&gt&1

echo “Creating awk script for getting NUMLOGGROUPS:”               &gt&gt $LOG 2&gt&1

echo “BEGIN {”                                         | tee -a $1 &gt&gt $LOG 2&gt&1

echo ”        a=”unknown”;”                            | tee -a $1 &gt&gt $LOG 2&gt&1

echo ”        astat=0;”                                | tee -a $1 &gt&gt $LOG 2&gt&1

echo ”      }”                                         | tee -a $1 &gt&gt $LOG 2&gt&1

echo “/COUNT/    { astat=1; }”                         | tee -a $1 &gt&gt $LOG 2&gt&1

echo “/—–/    { if(astat==1) astat=2; next; }”      | tee -a $1 &gt&gt $LOG 2&gt&1

echo “/selected/ { if(astat==3) print a; exit;}”       | tee -a $1 &gt&gt $LOG 2&gt&1

echo “/^[

t]*$/ { if(astat==3) {print a; exit;}}”       | tee -a $1 &gt&gt $LOG 2&gt&1

echo ”           { if(astat==2) { a=\$1; astat=3; } }” | tee -a $1 &gt&gt $LOG 2&gt&1

echo “”                                                | tee -a $1 &gt&gt $LOG 2&gt&1

sleep 1

}

#

#

  1. END OF FUNCTION

#

#====================================================================

#====================================================================

#

  1. FUNCTION: force_switch

#

  1. PURPOSE:  forces as many log switches as there are redo log groups

#

#

force_switch()

{

new_screen “Force log switches before backup”

db_shutdown_immediate;

db_startup;

setNUMLOGGROUPS;

echo log is $NUMLOGGROUPS

switch_logfiles $NUMLOGGROUPS;

db_shutdown_immediate;

sleep 3

new_screen “Continue with manual actions on source system”

echo “$NAME has finished the necessary steps on the source system.” | tee -a $LOG

echo “Check the logfile ${NAME}.log carefully before you continue with” | tee -a $LOG

echo “the manual actions as described in the documentation.” | tee -a $LOG

echo ” ” | tee -a $LOG

confirm

exit

}

#

  1. END OF FUNCTION

#

#====================================================================

###

      1. END OF LOCAL FUNCTIONS

###

#===================================================================

#

#

  1. MAIN PROCEDURE

#

  1. initial checks

#

init_log;

setDBATOOL;

startup_messages;

supress_check;

environment_check;

user_check;

#

  1. Global variable definition, database environment dependent

#

LOWERSID=”`echo $ORACLE_SID | $TRUL`”

setSQLPLUS;

#

  1. main loop, forever (unless someone enters ‘q’)

#

while true

do

new_screen “MAIN MENU”

echo “(a) Source system: Generating the script CONTROL.SQL”  | tee -a $LOG

echo ” ” | tee -a $LOG

echo “(b) Source system: Forcing necessary log switches before backup” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “—-


” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “(c) Target system: Executing CONTROL.SQL to create new control files” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “—-


” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “(q) Quit” | tee -a $LOG

echo ” ” | tee -a $LOG

echo ” ” | tee -a $LOG

echo ” ” | tee -a $LOG

echo “Please select (a|b|c|q):$C”

read answer

echo $answer &gt&gt $LOG

case $answer in

     a | A )  source_system ;;

     b | B )  force_switch ;;

     c | C )  target_system ;;

     q | Q )  exit ;;

    

  • ) echo “Wrong input – try again” | tee -a $LOG

         sleep 3 ;;

esac

  1. end of main loop

done

#

  1. END OF MAIN PROCEDURE

#

#====================================================================

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply