Brief description of Oracle physical standby database configuration and management

Configuration of Oracle physical standby database is quite simple,brief steps are as follows:

1.Install Oracle database software and create a database as primary database

2.Enable primary database to forced logging
SQL>alter database force logging;

3.Create standby redo logs,the number of standby redo logs follows the equation below:
(Maximum number of logfiles for each thread + 1) * maximum number of threads
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb1/stdby_redo01.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb1/stdby_redo02.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb1/stdby_redo03.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb1/stdby_redo04.log' size 50m;

4.Modify initialization parameters for the primary database:
SQL>alter system set db_name=mydb1;
SQL>alter system set db_unique_name=mydb1;
SQL>alter system set log_archive_config='DG_CONFIG=(mydb1,mydb2)';
SQL>alter system set log_archive_dest_1='LOCATION=/opt/oracle/arch1/mydb1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydb1';
SQL>alter system set log_archive_dest_2='SERVICE=mydb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydb2';
SQL>alter system set log_archive_dest_state_1=enable;
SQL>alter system set log_archive_dest_state_2=enable;
SQL>alter system set fal_server=mydb2;
SQL>alter system set fal_client=mydb1;
SQL>alter system set db_file_name_convert='/opt/oracle/oradata/mydb1','/opt/oracle/oradata/mydb2' scope=spfile;
SQL>alter system set log_file_name_convert='/opt/oracle/oradata/mydb1','/opt/oracle/oradata/mydb2' scope=spfile;
SQL>alter system set standby_file_management=auto;

5.Put the primary database in archivelog mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;

6.Backup the primary database using rman:
RMAN>backup database;

7.Create standby controlfile and parameter file:
SQL>alter database create standby controlfile as '/opt/oracle/oradata/mydb2/control01.ctl';
SQL>create pfile='/opt/oracle/dbs/initmydb2.ora' from spfile;

8.Multiplex the controlfiles for standby database and modify the parameter file for standby database:
bash-3.00$cp /opt/oracle/oradata/mydb2/control01.ctl /opt/oracle/oradata/mydb2/control02.ctl
bash-3.00$cp /opt/oracle/oradata/mydb2/control01.ctl /opt/oracle/oradata/mydb2/control03.ctl

bash-3.00$vi /opt/oracle/dbs/initmydb2.ora

db_name=mydb1
db_unique_name=mydb2
control_files='/opt/oracle/oradata/mydb2/control01.ctl','/opt/oracle/oradata/mydb2/control02.ctl','/opt/oracle/oradata/mydb2/control03.ctl'
log_archive_config='DG_CONFIG=(mydb1,mydb2)'
log_archive_dest_1='LOCATION=/opt/oracle/arch/mydb2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydb2'
log_archive_dest_2='SERVICE=mydb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydb1'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=mydb1
fal_client=mydb2
db_file_convert='/opt/oracle/oradata/mydb1','/opt/oracle/oradata/mydb2'
log_file_convert='/opt/oracle/oradata/mydb1','/opt/oracle/oradata/mydb2'
standby_file_management=auto

9.Create a password file for the standby database,make sure the password is identical to the password of primary database:
bash-3.00$orapwd file=/opt/oracle/dbs/orapwmydb2 password=db2pwd

10.Create listeners for primary and standby database,create Oracle Net service names for both primary and standby database on both primary and standby database,they will be used by redo transport services:

11.Start the standby database instance and mount:
SQL>startup nomount;
SQL>create spfile from pfile;
SQL>alter database mount standby database;

12.Restore standby database:
bash-3.00$rman target /

RMAN>restore database;

13.Create standby redo log files and put standby database into managed realtime redo apply recovery mode:
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb2/stdby_redo01.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb2/stdby_redo02.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb2/stdby_redo03.log' size 50m;
SQL>alter database add standby logfile '/opt/oracle/oradata/mydb2/stdby_redo04.log' size 50m;
SQL>alter database recover managed standby database using current logfile disconnect from session;

Several administrative SQL commands for physical standby database:

1.put physical standby database into managed realtime redo log apply recovery mode:
SQL>alter database recover managed standby database using current logfile disconnect from session;

2.put physical standby database into managed archived redo log apply recovery mode:
SQL>alter database recover managed standby database disconnect from session;

3.stop applying redo log:
SQL>alter database recover managed standby database cancel;

4.switchover roles:
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>alter database commit to switchover to primary with session shutdown;

5.monitor recovery progress:
SQL>select sequence#,name,applied from v$archived_log;