NON-AUTOMATIC STANDBY DATABASE CREATION

Contents:


Steps

On the Primary database (it should be in ARCHIVE LOG MODE):

1. Modify the init.ora files on PROD defining location for archived redo log files on both primary and the standby server. Example:

create pfile from spfile;

*.log_archive_dest_1='LOCATION=/u02/oradata/ARCH'
*.log_archive_format='CCOM%t_%s_%r.dbf'

# LOG_ARCHIVE_DEST_2 is the service name of a remote database accessible via Net8
# The REOPEN parameter specifies that if the standby is unreachable for
# some reason, the arch process will attempt to reconnect in 60 seconds.
#*.log_archive_dest_2 = "service=STDBY mandatory reopen=60"
#*.log_archive_dest_state_2 = enable

# Tell Oracle both destinations MUST succeed in order to mark the online redo ready for reuse.
#*.log_archive_min_succeed_dest = 2

Note that the LOG_ARCHIVE_START initialization parameter is obsolete in Oracle Database 10g.
Archiving is automatically enabled when you put your database into archive log mode.

2.  Ensure that your primary database is in archive log mode
archive log list;
create spfile from pfile;
alter system archive log current;
alter system switch logfile;
   (insures consistency in backup, standby controlfile, and logfiles)

If archiving hasn't been enabled on your primary database, run the following:
shutdown immediate;
create spfile from pfile;
startup mount;
alter database archivelog;
alter database open;
archive log list;

If archiving has been enabled, re-start the DB with the modifications that you performed to the init.ora on step 1
shutdown immediate;
create spfile from pfile;
startup;

3 Place the primary database in FORCE LOGGING mode
alter database force logging;

4. Backup the datafiles of your primary database. Cold backup is easier but you can use hot backup too.

$ cp /u02/oradata/prod/* /oracle/BCKUP
or
select 'cp ' || name || ' /oracle/BCKUP' from v$datafile
UNION
select 'cp ' || member || ' /oracle/BCKUP' from  v$logfile;

5. Create the standby controlfile
alter database create standby controlfile as '/oracle/BCKUP/standby.ctl';


ON THE STANDBY

6. Copy the datafiles, archived redo logs, the standby control file, init.ora and passwd file to the standby host (be sure to place copies of this file with the appropriate name in the equivalent locations on the standby where the production control files would have existed, if you had copied them. Otherwise you will need to use the parameters in the standby database to change the directories). Make sure you do not copy the production control files.
--Delete files
cd
cd FROMPROD
rm *
cd /u01/app/oracle/OraHome_1/dbs
rm alert_CCOM.log hc_CCOM.dat initCCOM.ora orapwCCOM
rm /u01/app/oracle/oradata/CCOM/*
rm /u02/oradata/CCOM/*
rm /u02/oradata/ARCH/*
rm /u01/app/oracle/admin/CCOM/bdump/*
rm /u01/app/oracle/admin/CCOM/cdump/*
rm /u01/app/oracle/admin/CCOM/udump/*

--START COPY PROCESS
cp /mnt/prod/BCKP/CC_dat_*.dbf  /u02/oradata/CCOM
cp /mnt/prod/BCKP/CC_indx_*.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/system01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/undotbs01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/sysaux01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/users01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/orapwCCOM /u01/app/oracle/OraHome_1/dbs
cp /mnt/prod/BCKP/initCCOM.ora /u01/app/oracle/OraHome_1/dbs
cp /mnt/prod/BCKP/CCOM1_*.dbf /u02/oradata/ARCH
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control01.ctl
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control02.ctl
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control03.ctl

7. Modify the init.ora file. I recommend keeping the parameters the same as configured on the primary database, with the following modifications to the secondary server init.ora file:
vi /u01/app/oracle/OraHome_1/dbs/initCCOM.ora
# location where archive redo logs are being written in standby environment
*.standby_archive_dest ='/u02/oradata/ARCH'
#*.fal_client=CCOM
#*.fal_server=CREDPROD


fal_client and fal_server are new parameters that enable archive-gap management. In this example, standby1 is the Oracle Net name of the standby database and primary1 is the Oracle Net name of the primary database. The fetch archive log (FAL) background processes reference these parameters to determine the location of the physical-standby and primary databases.

Convert all datafile pathnames that contain the names of your databases. As you can see this works ONLY if you have all your files in one place. If you have them in different directories (as OFA recommends), then you don't need use this parameter, you MUST rename each one of the files after mounting the database with the command ALTER DATABASE RENAME FILE '/oldplace/filename' TO '/newplace/filename' ;

db_file_name_convert = "/path/from/prod/db","/path/in/stdby/db"
log_file_name_convert = "/path/from/prod/db","/path/in/stdby/db"


8. Start the standby database in recovery mode.

sqlplus "/ as sysdba"
create spfile from pfile;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
exit

If necessay perform :  ALTER DATABASE RENAME FILE '/oldplace/filename' TO '/newplace/filenam' ;

9. Put the database into sustained recovery mode:
set echo on
sqlplus "/ as sysdba"
spool c:\scripts\logapply.log
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL;
alter database recover cancel;
spool off
exit;

--Check Alert
cd /u01/app/oracle/admin/CCOM/bdump
tail -f alert_CCOM.log

At this point, you should manually transfer the arch redo log files from PROD to the STDBY.

If by any reason you want to open the STDBY database in read more you can perform the following:
shutdown immediate
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
Alter database open read only;

How to open the standby database in Read only mode.
Disadvantage is that the (sustained) recovery has to be cancelled. If the database is opened in read only mode and users (including System and Sys) need sorting,  a locally managed sort tablespace should be their default sort tablespace. Steps to open the standby database in read only mode:

1- Create a locally managed temporary tablespace (if you created before the backup go to step #3)
The new locally managed temporary tablespace is created  on the primary database and is  propagated to the standby database by applying the logs or can be created before the backup for the standby is made.
        CREATE TEMPORARY TABLESPACE temp_local TEMPFILE
        '/unix1/app/oracle/oradata/v816/oradata/v816/temp_local01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

2- Be sure to set all the users that are going to make sorts in the standby DB (including sys and system) to have their temporary tablespace set to this locally managed temporary tablespace (alter user .. temporary tablespace temp_local;) , do this on the primary DB and have this propagated to the standby by the archives. Check carefully the location of the directories in both places. "Send" the archive log:
ALTER SYSTEM ARCHIVE LOG CURRENT

3- Cancel the sustained recovery
    Alter database recover managed standby database cancel;

4- Open the database in readonly mode
    Alter database open read only;

5- Add a temporary file at the standby database to the locally managed temporary tablespace
V$datafile or sys.file$ on the primary database do not show the created datafile belonging to the locally managed tablespace temp_local. The added tempfile on the primary database doesn't change sys.file$. The redo is not generated and not propagated to the standby database while the entry in sys.ts$ is, but ther's no file.  Issue on the standby database:
    alter tablespace temp_local add tempfile 'path/temp_local01.dbf' size 100M;

6- Afterward restart sustained or manual recovery when needed when no active sessions are connected. If necessary open a new session as internal and issue shutdown immediate;
 
 

ACTIVATE STANDBY DATABASE as PRODUCTION:

1. To activate the standby, first try to archive your current production database logs
ALTER SYSTEM ARCHIVE LOG CURRENT

2. Then copy the most recent archive logs and current online redo log to the standby and apply them.

3. If your standby database has not timed out from your recovery, simply open a new SQL session into the standby database, by using a DBA account, and issue
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Locate the end of the standby database's alert.log, and identify the last archived log that was applied. Manually apply any remaining logs to the standby database:
ALTER DATABASE RECOVER [FROM 'pathname'] STANDBY DATABASE;
 

4. When you have applied the remaining logs to the standby database, stop the recovery by issuing
ALTER DATABASE RECOVER CANCEL;

5. Convert the standby database to a production environment:
ALTER DATABASE ACTIVATE STANDBY DATABASE;

6. Then shutdown the standby to reset file headers and clear all buffers. You can't copy online redo logs from the primary to the standby. The standby is dismounted when activated. The standby bit in the controlfile is now set, so you can never go back - the standby is now your primary database. Since the redo log sequence was reset when the standby was opened, it is a good idea to take a full backup at this point.
 



Some Scripts

Standby Database Startup Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.

start_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE : start_db_recover_mode.ksh
# | AUTHOR : Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC. : This script is responsible for starting the Oracle standby database
# | in managed recovery mode. The major steps in this script include
# | mounting the database in standby mode, copy all archived redo log
# | files from the primary database server (using rcp), applying all
# | archived redo logs from the primary database, and finally putting
# | the database in managed recovery mode - automatically accepting and
# | applying archived redo logs from the primary database.
# |
# | NOTE : Since this script uses one of the r* commands, namely rcp, it
# | assumes that a valid .rhosts (or /etc/hosts.equiv) exists on the
# | primary host to ensure that the standby host can login as the
# | "oracle" user account.
# |
# | SYNTAX : nohup start_db_recover_mode.ksh ORACLE_SID
# | PRIMARY_DB_SERVER
# | PRIMARY_ARCH_LOG_DEST
# | STANDBY_ARCH_LOG_DEST
# |
# | EXAMPLE
# | CALL : nohup start_db_recover_mode.ksh -
# | ORA817 -
# | linux3 -
# | /u06/app/oradata/ORA817/archive -
# | /u06/app/oradata/ORA817/archive > -
# | /u01/app/oracle/common/log/start_db_recover_mode.log 2>&1 &
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 4 )); then
echo " "
echo "Usage: $SHORT_NAME ORACLE_SID PRIMARY_DB_SERVER PRIMARY_ARCH_LOG_DEST STANDBY_ARCH_LOG_DEST"
echo " "
echo " Invalid number of arguments."
echo " "
exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

PRIMARY_DB_SERVER=$2
export PRIMARY_DB_SERVER

PRIMARY_ARCH_LOG_DEST=$3
export PRIMARY_ARCH_LOG_DEST

STANDBY_ARCH_LOG_DEST=$4
export STANDBY_ARCH_LOG_DEST

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +-------------------------------------+
# | CHECK FOR DATABASE INSTANCE OFFLINE |
# +-------------------------------------+

echo " "
echo "Checking for database instance offline..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? != 1 ]]; then
echo "ERROR - Database Instance is up. Is this thing already in Managed Recovery Mode?"
echo " Process listing is to follow..."
ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
echo " Exiting script."
echo " "
exit
fi

# +---------------------------------------------------------+
# | TRY TO GET ANY ARCHIVED LOG FILES FROM PRIMARY DATABASE |
# +---------------------------------------------------------+

echo " "
echo "Attempting to get any archived log files from ${PRIMARY_DB_SERVER}:${LOG_ARCH_DEST} ..."
echo " "

rcp ${PRIMARY_DB_SERVER}:${PRIMARY_ARCH_LOG_DEST}/* $STANDBY_ARCH_LOG_DEST

# +---------------------------------------------+
# | STARTUP/MOUNT STANDBY DB & RECOVER ALL LOGS |
# +---------------------------------------------+

echo " "
echo "Startup/Mount Database and recover all archived redo logs..."
echo " "

sqlplus /nolog << END
connect / as sysdba
startup nomount
alter database mount standby database;
recover standby database until cancel;
auto
exit;
END

# +--------------------------------------------+
# | STARTUP DB IN MANGED STANDBY RECOVERY MODE |
# +--------------------------------------------+

echo " "
echo "Startup Database in Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database;
connect / as sysdba
shutdown immediate
exit;
END


Standby Database Shutdown Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.

stop_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE : stop_db_recover_mode.ksh
# | AUTHOR : Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC. : This script is responsible for stopping an Oracle standby database
# | that is in managed recovery mode. The major steps in this script
# | include checking that the database instance is indeed running and
# | then canceling managed recovery mode for the given database.
# |
# | SYNTAX : stop_db_recover_mode.ksh ORACLE_SID
# |
# | EXAMPLE
# | CALL : stop_db_recover_mode.ksh ORA817 > /u01/app/oracle/common/log/stop_db_recover_mode.log 2>&1
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 1 )); then
echo " "
echo "Usage: $SHORT_NAME ORACLE_SID"
echo " "
echo " Invalid number of arguments."
echo " "
exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +------------------------------------+
# | CHECK FOR DATABASE INSTANCE ONLINE |
# +------------------------------------+

echo " "
echo "Checking for database instance online..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? == 1 ]]; then
echo "ERROR - database not in recovery mode. Did someone already shutdown the DB Instance?"
echo " Process listing is to follow..."
ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
echo " Exiting script."
echo " "
exit
fi

# +---------------------------------------------+
# | TAKE DB OUT OF MANAGED RECOVERY MODE. |
# +---------------------------------------------+

echo " "
echo "Take Database out of Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database cancel;
exit;
END