Pre-Requisite
a) Database (PRIMARY) is up and running and the datafiles, controlfiles residing in ASM.
b) ASM instance and diskgroups configured in STANDBY server.
c) Network connectivity between PRIMARY and STANDBY server
PROCEDURE
1.Enable force logging in PRIMARY.
2.Create SRL(standby redo logs) in PRIMARY.
3.Backup the PRIMARY database.
4.Make proper changes in the parameter file of PRIMARY.
5.Create the parameter file for STANDBY
6.Copy the files (RMAN Backup, init.ora) to STANDBY
7.Establish the connectivity between PRIMARY and STANDBY.
8.Start the STANDBY instance and use RMAN duplicate to create standby database
9. Create SRL on standby.
10.Start the MRP process,
11. Verify whether the log are shipped and applied properly @the standby
Consider two databases of names PRIMARY= PRIMA and STANDBY= MYSTD
1. Enable Forced Logging on PRIMARY
SQL> ALTER DATABASE FORCE LOGGING;
2. Configure Standby Redo Log on PRIMARY
a. Check the log files and sizes,
SQL>SELECT GROUP#,BYTES FROM V$LOG;
b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
c. Verify the standby redo log file groups were created(do this after the creation of stanby database)
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
3. Use RMAN to backup
PRIMARY database with archivelog & controlfile.
RMAN>backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN>backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';
4. Make the necessary changes to PRIMARY .
DB_NAME=pri
DB_UNIQUE_NAME=pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
LOG_ARCHIVE_DEST_2= 'SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYSTD'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=MYSTD
FAL_CLIENT=PRIMA
DB_FILE_NAME_CONVERT='MYSTD','PRIMA'
LOG_FILE_NAME_CONVERT='MYSTD','PRIMA'
STANDBY_FILE_MANAGEMENT=AUTO
5. Create the parameter file for standby,
a. CREATE PFILE='<specify any location>' from spfile; (@primary,)
b. Make the necessary changes, for example,
DB_NAME=PRIMA
DB_UNIQUE_NAME=MYSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
CONTROL_FILES='+DATA','+FRA'
DB_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMA
FAL_CLIENT=MYSTD
Note: If the ASM Disk groups names are different between PRIMARY and STANDBY do the necessary changes in STANDBY with the parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n
6. Copy the files (RMAN Backup, init.ora) to STANDBY
a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical locationExample:scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*b) Copy the init.ora (step 5) to STANDBY
c) Recreate the password file in standby using orapwd utility
7. Establish the connectivity between primary and standby.
In PRIMARY (TNSNAMES.ORA)
MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)
In STANDBY (TNSNAMES.ORA)
PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)
NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY
8. Start the STANDBY instance and use RMAN duplicate to create standby database
NOTE: Make sure the ASM instance also running.
$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’<specify the newly created parameter location>’
SQL>startup nomount
NOTE: Connect to catalog if your primary database has catalog database.
$RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd>
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}
9. Create SRL(Standby Redo logs) on standby,
NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE
For Example,
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
10. Start the MRP process in PRIMARY
In PRIMARY
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
In STANDBY
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
11. Verify whether the log are shipped and applied properly in the standby
a. execute on PRIMARY database
SQL> ALTER SYSTEM SWITCH LOGFILE;
b. execute on STANDBY database
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
c. Issue more log switches at PRIMARY
SQL> ALTER SYSTEM SWITCH LOGFILE;
d. Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
