Step by Step
1) Activate Force logging on the primary database
In case of unlogged writes to primary database (NOLOGGING and DIRECT LOAD operations using SQL*Loader), redo data is not generated. As the redo required to synch the changes to the standby is not generated, the standby database will not be consistent with the primary. To prevent such issues, the primary database is forced to log operations. It may also be noted that it is recommended to take a backup of the database after UNRECOVERABLE/NOLOGGING operations as those operations are not recoverable as the redo data is not generated.Use the below command on the primary database to force logging on the database.
SQL> alter database force logging;
This must be done before building the standby database.
2) Prepare Initialization files for the primary and standby databases
# Initialization file for primary database – initorcl.ora
*.audit_file_dest='/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'
,'/oradata/orcl/control03.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/oradata/arch/orcl/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=316669952
*.undo_tablespace='UNDOTBS1'
##### Standby related parameters ########
*.db_unique_name='orcl'
*.fal_client='ORCL'
*.fal_server='STBY'
*.DB_FILE_NAME_CONVERT=('/oradata/stby/','/oradata/orcl/')
*.LOG_FILE_NAME_CONVERT=('/oradata/stby/','/oradata/orcl/') *.log_archive_config='dg_config=(orcl,stby)'
*.log_archive_dest_2='SERVICE=STBY valid_for=online_logfiles,primary_role)'
*.db_unique_name='STBY'
*.log_archive_dest_state_2=enable
*.standby_file_management=auto
##### End Standby related parameters #######
# Initialization file for standby database - initstby.ora
*.audit_file_dest='/oracle/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/oradata/stby/control01.ctl','/oradata/stby/control02.ctl',
'/oradata/stby/control03.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/oradata/arch/stby/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=316669952
*.undo_tablespace='UNDOTBS1'
##### Standby related parameters#######
*.db_unique_name='stby'
*.log_archive_dest_2='SERVICE=ORCL valid_for=(online_logfile,primary_role)
*.DB_FILE_NAME_CONVERT=('/oradata/orcl/','/oradata/stby/')
*.LOG_FILE_NAME_CONVERT=('/oradata/orcl/','/oradata/stby/')
*.fal_server='ORCL'
*.fal_client='STBY'
*.log_archive_config='dg_config=(orcl,stby)'
*.standby_file_management=auto
##### End Standby related parameters #######
3) Configure Listener for Static service registration
Static service registrations is requierd for the RMAN active database duplication to work.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521)))) SID_LIST_LISTENER = ( SID_LIST = (SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/11.1.0) (SID_NAME = orcl)) (SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/11.1.0) (SID_NAME = stby)) )
4) Create service names in tnsnames.ora
Add tnsnames.ora entries for primary and standby databases.
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) STBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby)))
5) Create password file for the standby database
The easiest way to do this is to copy the password file from the primary database and to rename it in proper format on the standby server.
$ pwd /oracle/app/oracle/product/11.1.0/dbs $ cp orapworcl orapwstby
6) Perform Oracle 11g standby creation using RMAN DUPLICATE command
run
{
duplicate target database for standby dorecover from active database;
}
The primary database is duplicated as a standby database on the target server (or on same server in this case) using the datafiles from the active database. There is no need of a backup and the datafiles are copied over the network to the target server and is recovered to the point when the duplication was fired.
7) Logs
11g-standby-database-creation-with-rman-duplicate-command – log
