Sunday, November 4, 2012

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

No comments:

Post a Comment