NON-AUTOMATIC STANDBY DATABASE CREATION

Contents:

• Steps to Create
• Open the DB in read-only  mode
• Activate standby as PRODUCTION
• Some Scripts
• Resolving Errors

As you may know, Oracle SE (Standard Edition) does not include the automatic DataGuard feature, but that doesn't mean that you can't use it!!!!
Here is a description about how to "manually" setup a Dataguard or Standby:

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 1shutdown 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;
shutdown immediate;
startup nomount;
alter database mount standby database;
5. Convert the standby database to a production environment:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
shutdown immediate
startup;
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

There are 4 files:
. generic.sh : you can duplicate this file in order to set up as many standby as needed, etc. It calls other scripts in order to:
. archivemove.sh : Get the archived redo logs to the standby host
. recover.sh : Synch the standby
. getrecid.sql : get the maximum progess point on the Manual Standby (used by archivemove.sh)

These scripts are used from the standby host. Remember to throughly check it before relying on it for production.

generic.sh

#!/bin/sh
 
# Be sure environment variable are set. If not, then it might fail!
# These environment variables are those for the Manual Physical Standby host
export ORACLE_HOME=/logical/oracle/Ora9i
export ORACLE_BASE=/logical/oracle
export ORACLE_STANDBY=tns_alias
export ORACLE_STANDBY_SYSDBA_PASS=change_on_install
export PATH=$ORACLE_HOME/bin:$PATH
export SOURCE_HOST=primary_host
export SOURCE_DRIVE=/primary/absolute/path/to/archived/redo/logs
export LOCAL_ARC_PARTH=/path/to/logical/archive/dest
 
# Check the date command usage depending on the platform
dateexec=`date "+%Y-%m-%d-%H-%M"`
 
# copy archived redo logs from main database
archivemove.sh > $dateexec.generic.log
 

# recover/sync the Manual Standby Database
recover.sh >> $dateexec.generic.log

archivemove.sh

#!/bin/sh
 
 
echo ----------------------------------------------------------------
echo ----------------------------------------------------------------
echo Get what log has last been applied to: $ORACLE_STANDBY
echo ----------------------------------------------------------------
sqlplus /nolog @getrecid.sql $ORACLE_STANDBY
echo ----------------------------------------------------------------
maxval=`tail -1 recid.log`
echo maxval=$maxval
rm recid.log
echo ----------------------------------------------------------------
 
# Check source drive to see what we're missing locally (source = primary)
for filename in `remsh $SOURCE_HOST 'ls $SOURCE_DRIVE' | sort`
do
 # get archive number. 
 # WARNING here I'm based on MY archived redo log name format! Put yours for the cut
 filename_parsed=`echo $filename | cut -c12-16`
 # Check if the number is after the last one applied to standby
  if [ $filename_parsed -gt $maxval ]
  then
   # grab it!
   echo $filename
   rcp $SOURCE_HOST:$SOURCE_DRIVE/$filename $LOCAL_ARC_PARTH
  fi
done
 
echo ----------------------------------------------------------------
echo Removing old files
echo ----------------------------------------------------------------
# Check in local directory
for filename in `ls $LOCAL_ARC_PATH | sort`
do
 # WARNING again about filename format
 filename_parsed=`echo $filename | cut -c12-16`
 # Check the arc number...
  if [ $filename_parsed -lt `expr $maxval - 15` ]
  then
   # Delete it!
   echo $filename
   rm -f $LOCAL_ARC_PATH/$filename 
  fi
done
 
echo ----------------------------------------------------------------
echo end archivemove.sh
echo ----------------------------------------------------------------

recover.sh

#!/bin/sh
 
echo ----------------------------------------------------------------
echo Traitement de la base $ORACLE_STANDBY
echo ----------------------------------------------------------------
sqlplus /nolog << EOF
connect sys/$ORACLE_STANDBY_SYSDBA_PASS@$ORACLE_STANDBY as sysdba
SELECT MAX(RECID) "Log id now" FROM V\$LOG_HISTORY;
RECOVER AUTOMATIC DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
CANCEL
SELECT MAX(RECID) "Log id after recover" FROM V\$LOG_HISTORY;
exit;
EOF
echo ----------------------------------------------------------------
echo End of recovery process
echo ----------------------------------------------------------------

getrecid.sql

connect sys/change_on_install@&1 as sysdba
SET HEAD OFF FEEDBACK OFF VERIFY OFF TERMOUT ON ECHO OFF TRIMSPOOL ON SERVEROUTPUT OFF
SPOOL recid.log
SELECT MAX(RECID) FROM V$LOG_HISTORY;
SPOOL OFF
exit

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

Resolving Errors
If you get an error because the Standby DB can't recognized a database file name that was added or dropped perform the following:
alter database recover automatic standby database until cancel;
alter database create datafile '/u01/app/oracle/OraHome_1/dbs/UNNAMED00002' as '/u01/app/oracle/oradata/CCOM/undotbs01.dbf';