Creating a Standby Database using RMAN - 10g - Unix

Pre-required/Assumes:

* On Primary:
* db_name = ctgbp
* db_unique_name = ctgbp

* On Standby:
* db_name = ctgbp
* db_unique_name = ctgbpdr

* Directory structure is the same on both primary and standby machines.

* TNSNAMES entry STANDBY points to standby db on both machines.

CTGBDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.250.2)(PORT = 1521))
)
(CONNECT_DATA = (SID = ctgbp)
)
)

* TNSNAMES entry PROD points to prod db on both machines.

CTGBP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.51)(PORT = 1521))
)
(CONNECT_DATA = (SID = ctgbp)
)
)

* Backup is copying locally to disk at the same location as source_backup_directory

RMAN> show all;using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ctgb/backup/ctgbp/control_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ctgb/backup/ctgbp/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ctgb/backup/ctgbp/snapcf_ctgbp.f';

1. Create Password File for Standby Database

Login to the standby database server and create a password file:
$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwctdbp password=change_on_install

2. Create a Standby Controlfile

Login to the Primary database server
$ ORACLE_SID=ctdbp; export ORACLE_SID
$ rman target /
RMAN> backup current controlfile for standby format='/orabackup/rman/ctdbp/stby_cfile.%U';

3. Record Last Log Sequence of Primary DB

$ sqlplus "/ as sysdba"
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
208

4. Make a full backup of Primary DB

$ rman target /
RMAN> backup database;

(2nd option:
Use old full backup of PROD; Create a Standby Controlfile; Backup New Archive Log Files (RMAN> backup archivelog;)
)

5. Initialization Parameter Setting

On Primary:
SQL> create pfile='/u01/app/oracle/product/9.2.0/dbs/initctdbp.ora' from spfile;

Copy the initialization parameter to the STDBY database, change at least the following parameters for the Standby database:

db_name='ctgbp'
db_unique_name='ctgbpdr'
fal_client = 'ctgbpdr' /* STDBY tnsname */
fal_server = 'ctgbp' /* PROD tnsname */
log_archive_dest_2='service=CTGBP optional reopen=300 valid_for=(online_logfiles,primary_role) db_unique_name=ctgbp'
log_archive_dest_state_2 = 'defer'

Change parameters on Primay db also:

db_name='ctgbp'
db_unique_name='ctgbp'
fal_client = 'ctgbp' /* PROD tnsname */
fal_server = 'ctgbp'dr /* STDBY tnsname */
log_archive_dest_1='location=/ctgb/flashback/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=ctgbp'
log_archive_dest_2='service=CTGBDR optional reopen=300 valid_for=(online_logfiles,primary_role) db_unique_name=ctgbdr'
log_archive_dest_state_2 = 'defer'

6. Start the Standby (Auxiliary) Instance

$ export ORACLE_SID=ctgbdr;
$ sqlplus "/ as sysdba"
SQL> startup nomount

7. Create Standby Database

Run the following:
$ rman target sys/password@PROD auxiliary sys/password@STDBY
RMAN>run {
# Set the last log sequence number
set until sequence = 208 thread = 1;
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;
# Duplicate the database to ORA920
duplicate target database for standby dorecover nofilenamecheck ;
}

(2nd option:
RMAN>run {duplicate target database for standby dorecover nofilenamecheck ;}

RMAN> exit;

8. Put the Standby in Managed Recovery Mode

On the standby database:
$ sqlplus "/ as sysdba"
SQL> recover standby database; /* optional */
SQL> alter database recover managed standby database disconnect;
Database altered.