Step by Step Setup of Physical Standby database on 11g

Creating Physical Standby Database

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	CTMSD1	CTMSD1
Physical standby	CTMSP1	CTMSP1

A)    Performing Tasks on Primary DB

a.      Enable Archiving on the Primary database

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> Archive log start;

b.     Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

c.      Setup Primary DB Initialization Parameters

-----Sample Parameters (Required for Primary Database Role) -------

DB_NAME=CTMSD1

DB_UNIQUE_NAME=CTMSD1

‘--LOG_ARCHIVE_CONFIG='DG_CONFIG= (ctmsd1, ctmsp1)'

CONTROL_FILES='E:\oracle\CTMSD1\CONTROL01.CTL', 'E:\oracle\CTMST1\CONTROL02.CTL', ‘E:\oracle\CTMST1\CONTROL03.CTL'

LOG_ARCHIVE_DEST_1='LOCATION= E:\archive\ctmsd1’

LOG_ARCHIVE_DEST_2='SERVICE=ctmsp1 SYNC AFFIRM’   

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=CDM%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=10

FAL_SERVER=ctmsp1

FAL_CLIENT=ctmsd1

DB_FILE_NAME_CONVERT='ctmsp1','ctmsd1'

LOG_FILE_NAME_CONVERT='E:\archive\ctmsp1\','E:\archive\ctmsd1\'

STANDBY_FILE_MANAGEMENT=AUTO

----- Actual parameters file on Primary ----------------------

ctmsd1.__db_cache_size=218103808

ctmsd1.__java_pool_size=4194304

ctmsd1.__large_pool_size=4194304

ctmsd1.__oracle_base='E:\Oracle'#ORACLE_BASE set from environment

ctmsd1.__pga_aggregate_target=322961408

ctmsd1.__sga_target=536870912

ctmsd1.__shared_io_pool_size=0

ctmsd1.__shared_pool_size=301989888

ctmsd1.__streams_pool_size=0

*.audit_file_dest='E:\Oracle\admin\CTMSD1\adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='E:\Oracle\CTMSD1\control01.ctl','E:\Oracle\CTMSD1\control02.ctl','E:\Oracle\CTMSD1\control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='E:\oracle\CTMSP1\','E:\oracle\CTMSD1\'

*.db_name='CTMSD1'

*.db_recovery_file_dest='E:\Oracle\flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='E:\Oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=CTMSD1XDB)'

*.fal_client='CTMSD1'

*.fal_server='CTMSP1'

*.LOG_ARCHIVE_DEST_1='LOCATION=E:\archive\ctmsd1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ctmsd1'

*.LOG_ARCHIVE_DEST_2='SERVICE=ctmsp1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ctmsp1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='cdm%t_%s_%r.arc'

*.log_archive_max_processes=10

*.log_file_name_convert='E:\archive\ctmsp1\','E:\archive\ctmsd1\'

*.memory_target=857735168

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

d.     Create a backup copy of the database datafiles of Primary DB

Backup copy of database datafiles would be used for creating standby database.

e.      Create a Control File for the Standby Database

Shutdown the primary database and mount the database for creating standby control file –

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘E:\Oracle\CTMSP1\ctmsp1.ctl';

SQL> ALTER DATABASE OPEN;

B)    Performing Tasks on Standby DB

a.      Prepare the initialization parameter file for the standby database

Create a text parameter file from the primary database –

SQL> CREATE PFILE='e:\initctmsp1.ora' FROM SPFILE;

b.     Modify initialization parameters for Standby database

------- Sample standby DB initialization Parameters ----------------

DB_NAME=ctmsd1

DB_UNIQUE_NAME=ctmsp1

‘-- LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

CONTROL_FILES='E:\oracle\CTMSP1\Ctmsp1.CTL'

DB_FILE_NAME_CONVERT='ctmsd1','ctmsp1'

LOG_FILE_NAME_CONVERT='E:\archive\ctmsd1\','E:\archive\ctmsp1\'

DB_FILE_NAME_CONVERT = 'E:\oracle\CTMSD1\' , 'E:\oracle\CTMSP1\'

LOG_ARCHIVE_FORMAT=CDM%t_%s_%r.arc

LOG_ARCHIVE_DEST_1='LOCATION='E:\archive\ctmsp1'

LOG_ARCHIVE_DEST_2='SERVICE=ctmsd1 SYNC AFFIRM’   

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=ctmsd1

FAL_CLIENT=ctmsp1

---------- Actual File Standby Init<SID>.ora-----------------

ctmsp1.__db_cache_size=218103808

ctmsp1.__java_pool_size=4194304

ctmsp1.__large_pool_size=4194304

ctmsp1.__oracle_base='E:\Oracle'#ORACLE_BASE set from environment

ctmsp1.__pga_aggregate_target=322961408

ctmsp1.__sga_target=536870912

ctmsp1.__shared_io_pool_size=0

ctmsp1.__shared_pool_size=301989888

ctmsp1.__streams_pool_size=0

*.instance_name=ctmsp1

*.audit_file_dest='E:\Oracle\admin\ctmsp1\adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='e:\oracle\ctmsp1\ctmsp1.ctl'

*.db_block_size=8192

*.db_unique_name=ctmsp1

*.db_domain=''

*.db_file_name_convert='E:\oracle\ctmsd1\','E:\oracle\ctmsp1\'

*.db_name='ctmsd1'

*.db_recovery_file_dest='E:\Oracle\flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='E:\Oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ctmsp1XDB)'

*.fal_client='CTMSP1'

*.fal_server='ctmsd1'

*.LOG_ARCHIVE_DEST_1='LOCATION=E:\archive\ctmsp1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ctmsp1'

*.LOG_ARCHIVE_DEST_2='SERVICE=ctmsd1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ctmsd1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='cdm%t_%s_%r.arc'

*.log_archive_max_processes=10

*.log_file_name_convert='E:\archive\ctmsd1\','E:\archive\ctmsp1\'

*.memory_target=857735168

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

Note: Ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases.

c.      Setup Environment to support Standby database

a.       Create a Windows-based service

b.      oradim -new -sid ctmsp1 -syspwd manager -startmode manual -pfile E:\Oracle\product\11.1.0\db_1\database\initctmsp1.ora

c.       Copy the remote login password file from the primary database system to the standby database system. If this step get missed then it would give authentication error on standby database.                

Note: This step is optional if operating system authentication is used for administrative users and if SSL is used for redo transport authentication

d.      Configure listener for primary and standby databases – use Netmgr utility to configure listener. To start and stop the listener at both primary and secondary.

e.      To start and stop the listener –

Lsnrctl stop

Lsnrctl start

f.        Create Oracle Net Services (configuring tnsnames.ora), for Primary and Standby log shipping, using Oracle Net Manger or configure manually.

g.       Create SP file for Standby database

SQL> CREATE SPFILE FROM PFILE='initctmsp1.ora';

h.       Create a Control File for the Standby Database

If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:

SQL> STARTUP MOUNT;

Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'e:\oracle\ctmsp1\ctmsp1.ctl';

SQL> ALTER DATABASE OPEN;

B)      Start the Physical Standby database

a.       Start the physical Standby database

On the standby database, issue the following SQL statement to start and mount the database:

SQL> STARTUP MOUNT;

Note: The AFFIRM attribute is used to specify that redo received from a redo source database is not acknowledged until it has been written to the standby redo log. The NOAFFIRM attribute is used to specify that received redo is acknowledged without waiting for received redo to be written to the standby redo log

b.      Prepare the Standby database to Receive Redo data

Copy the password file from the primary database to standby database. This ensure the heart bit for log shipping.

c.       Create online Redo log on the Standby Database (optional)

Although the step is optional but Oracle recommends that an online redo log be created when a standby database is created. By this standby database would get ready for quick transitioning to Primary role.

d.      Start Redo Apply

On the standby database, issue the following command to start Redo Apply:

SLQ>alter database mount standby database;

Note: Copy the primary archived logfiles to F: \oracle\admin\STDHCL\Archive’

SQL>RECOVER STANDBY DATABASE;

Now of course, you’ll want to test your standby database, You do this by starting up in read only mode.

SQL>alter database open read only;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.

C)      Verify the Physical Standby Database Is Performing Properly

a.       Identify the existing archived redo log files.

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

          FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

b.       Force a log switch to archive the current online redo log file

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

c.       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#;

d.      Verify that received redo has been applied.

On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG

          ORDER BY SEQUENCE#;

Starting Redo Apply

                                                                                           

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

OR

To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Stopping Redo Apply

To stop Redo Apply, issue the following SQL statement in another window:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To Check the archived files of the Primary database

SQL> select sequence# from v$archived_log where dest_id=1 order by sequence#;

To Check the Applied archive files of the Standby database

SQL> select sequence#, applied from v$archived_log order by sequence#;

On either database, you can query the V$LOGFILE view and obtain a list of the SRL files that have been created:

SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY';

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘../..’;

SQL> SELECT DESTINATION FROM V$ARCHIVE_DEST WHERE DEST_ID=1;    -- On Primary it is Primary DB archive

SQL> SELECT DESTINATION FROM V$ARCHIVE_DEST WHERE DEST_ID=2; -- on Primary it is secondary DB archive destination

Assuming we have three ORL groups of 50MB each, we will create four SRL groups on the primary database:

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M

SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 50M