Dataguard - Creating physical standby database using RMAN Duplicate command on Oracle 11gR2
-------------------------------------------
* Primary database should be in archivelog mode.
* Forced logging is on in primary database.
* Initialization parameter "DB_NAME" should be same on both primary and standby database.
* Initialization parameter "DB_UNIQUE_NAME" should be different on primary and standby database.
Primary Database : AMER
Standby Database : AMERSTD
0. Check if the primary database is in archive log mode enabled or not.If not enable the archive log mode.
For this, we have to shutdown the primary database and startup the database in MOUNT state to enable the archive log mode.
Primary Database:
1. Check if the primary database is using the password file or not. If not, then create one as below.
SELECT * FROM V$PWFILE_USERS;
Connect to the user prompt and issue the following command.
$orapwd file=$ORACLE_HOME/dbs/orapwAMER password=oracle force=y
2. Add the following parameters in the initialization parameter file of the primary database.
$vi $ORACLE_HOME/dbs/initAMER.oracle
amer.__db_cache_size=159383552
amer.__java_pool_size=4194304
amer.__large_pool_size=4194304
amer.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
amer.__pga_aggregate_target=155189248
amer.__sga_target=264241152
amer.__shared_io_pool_size=0
amer.__shared_pool_size=88080384
amer.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/amer/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/amer/control01.ctl','/u01/app/oracle/oradata/amer/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='amer'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=amerXDB)'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.service_names='amer'
*.undo_tablespace='UNDOTBS1'
##Dataguard Configuartion Parameters:
*.db_unique_name='amer'
*.db_file_name_convert='/u01/app/oracle/oradata/amerstd','/u01/app/oracle/oradata/amer'
*.log_file_name_convert='/u01/app/oracle/oradata/amerstd','/u01/app/oracle/oradata/amer'
*.fal_client='amer'
*.fal_server='amerstd'
*.log_archive_config='dg_config=(amer,amerstd)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/amer_arch valid_for=(all_logfiles,all_roles) db_unique_name=amer'
*.log_archive_dest_2='service=amerstd valid_for=(online_logfile, primary_role) db_unique_name=amerstd'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
3. Listener and tnsnames entries on primary database.
Location of the network configuration files:
/u01/app/oracle/product/11.2.0/db_1/network/admin
--------------------
### LISTENER.ORA ###
--------------------
[oracle@amer admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.22)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = amer)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[oracle@amer admin]$
--------------------
### TNSNAMES.ORA ###
--------------------
[oracle@amer admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
AMER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = amer)
)
)
AMERSTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.33)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = amerstd)
)
)
[oracle@amer admin]$
4. Now copy the orapwd file, init<sid>.ora and network configuration (listener.ora/tnsnames.ora) files from primary database (amer) to standby database (amerstd) located at $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin
$cd $ORACLE_HOME/dbs
$scp orapwdAMER oracle@192.168.141.33:/u01/app/oracle/product/11.2.0/db_1/dbs
$scp initAMER.ora oracle@192.168.141.33:/u01/app/oracle/product/11.2.0/db_1/dbs
$cd $ORACLE_HOME/network/admin
$scp listener.ora oracle@192.168.141.33:/u01/app/oracle/product/11.2.0/db_1/network/admin
$scp tnsnames.ora oracle@192.168.141.33:/u01/app/oracle/product/11.2.0/db_1/network/admin
Here, oracle is the username of standby database and 192.168.141.33 is standby database ip.
Now go to STANDBY Database server and do change parameters and file names accordingly.
5. Now change the name of the password file with standby database name.
$mv orapwdAMER orapwdAMERSTD
6. Change the init file name and parameters as below.
amerstd.__db_cache_size=159383552
amerstd.__java_pool_size=4194304
amerstd.__large_pool_size=4194304
amerstd.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
amerstd.__pga_aggregate_target=155189248
amerstd.__sga_target=264241152
amerstd.__shared_io_pool_size=0
amerstd.__shared_pool_size=88080384
amerstd.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/amerstd/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/amerstd/control01.ctl','/u01/app/oracle/oradata/amerstd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='amer'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=amerstdXDB)'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.undo_tablespace='UNDOTBS1'
#Dataguard Config Parameters
db_unique_name=amerstd
log_archive_config='dg_config=(amerstd,amer)'
log_archive_dest_1='location=/u01/app/oracle/oradata/amerstd_arch valid_for=(all_logfiles,all_roles) db_unique_name=amerstd'
log_archive_dest_2='service=amer valid_for=(online_logfile, primary_role) db_unique_name=amer'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
fal_client=amerstd
fal_server=amer
service_names = amerstd
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/amer','/u01/app/oracle/oradata/amerstd'
log_file_name_convert='/u01/app/oracle/oradata/amer','/u01/app/oracle/oradata/amerstd'
7. Now change the tns and listener entries on stand by database as below.
[oracle@euro admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.33)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = amerstd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[oracle@euro admin]$
[oracle@euro admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
AMERSTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.33)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = amerstd)
)
)
AMER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.141.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = amer)
)
)
[oracle@euro admin]$
8. Now start the database with startup nomount on standby database with standby pfile.
startup nomount pfile='$ORACLE_HOME/initamerstd.ora';
Standby databse should be in nomount state.
9. Now start listener on standby database.
10. Now open primary database in READ WRITE mode and start listener.
11. Test connectivity to auxilary (amerstd) and target (amer) instances from both hosts using tns:
Make sure your connectivity to the source database and to your auxiliary instance works fine;
Otherwise, duplicate from active database wont work.
sqlplus sys/passwd@amer as sysdba
sqlplus sys/passwd@amerstd as sysdba
Try above commands on both target and auxiliary hosts amer and amerstd. Do not continue unless your connectivity is fine.
12. If every thing is fine. Now connect to the primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the primary database is open and the standby database is in nomount stage (Started).
Primary Database (AMER/amer) - Open State (READ-WRITE)
Standby Database (AMER/amerstd) - NOMount State (NOMOUNT)
From primary (AMER) database:
amer$rman target sys/oracle@amer auxiliary sys/oracle@amerstd
Then issue the following command from primary database to create standby database in
rman>duplicate target database for standby from active database nofilenamecheck;
13. Once the duplicate is completed, Close the RMAN prompt and connect to the standby database through SQL.
sqlplus sys/oracle@amerstd as sysdba
check the status of the standby database by make sure it is in mount stage.
select status, instance_name,database_role from v$instance, v$database;
14. Now start the managed recovery process on the standby database.
alter database recover managed standby database disconnect from session;
Now check if the managed recovery process (MRP) has been started on the standby database or not.
select process, status, sequence# from v$managed_standby;
Here, the MRP has been started and is waiting for the log sequence# 89. If MRP is not started, then the above query would not show up the MRP0 under the process column.
15. On the primary database, perform a few log switches and check if the logs are applied to the standby daabase.
sqlplus sys/<password>@amer as sysdba
Now connect to standby database and check..whether the sequence is same or not.
sqlplus sys/<password>@amerstd as sysdba
Here the maximum sequence# generated on the primary database is 90 and the maximum sequence# applied on the standby database is also 90 which means that the standby database is in SYNC with the primary database.
## All the best......!!!
No comments:
Post a Comment