Setup for the Environment
——————-
Exadata X4-2 Quater Rack n Primary and Standby Site
Grid Version : 12.1.0.2
DB Home Version : 12.1.0.2
Primary, 2 Node RAC, db_unique_name = primeprd
Instance 2 : primeprd1
Instance 2 : primeprd2
TNS entry: PRIMEPRD, but below entry in tnsnames.ora files on both node with DB Home user, most likely it will be Oracle user.
PRIMEPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primeprd)
)
)
Standby, 2 Node RAC, db_unique_name= primedr
Instance 2 : primedr1
Instance 2 : primedr2
TNS ENTRY: PRIMEDR, but below entry in tnsnames.ora files on both node with DB Home user, most likely it will be Oracle user.
PRIMEDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primedr)
)
)
1. Enable Force Logging on the Database
[code language="sql"] alter database force logging; [/code]
Database altered.
2.1 change the below parameter in Primary database.
[code language="sql"]
ALTER SYSTEM SET db_recovery_file_dest='+RECOC1' scope=both sid='*';
System altered.
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primeprd,primedr)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primeprd' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=primedr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primedr' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server=primedr scope=both sid='*';
alter system set fal_client=primeprd scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert=('+DATAC1/primedr/', '+DATAC1/primeprd/', '+RECOC1/primedr/', '+RECOC1/primeprd/') scope=both sid='*';
alter system set log_file_name_convert=('+DATAC1/primedr/', '+DATAC1/primeprd/', '+RECOC1/primedr/', '+RECOC1/primeprd/') scope=both sid='*';
-- Query to check if parameters are set correctly --
set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');
[/code]
3.1. verify the logfile using the below querys.
[code language="sql"] select group#,THREAD#,bytes/1024/1024,status,members from v$log; select member from v$logfile; [/code]
3.2. Add the below logfile and standby logfile in primary database, make sure you have large redolog files. In this day and age, 100MB does not really cut it.
[code language="sql"]
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATAC1','+RECOC1') SIZE 1G;
select * from v$standby_log;
-- Add Standby Redolog files, number of standby redo log files = Redo logfile Groups + 2 --
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 15 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 16 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 17 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 18 ('+DATAC1','+RECOC1') SIZE 1G;
[/code]
4. Take the rman backup using below command.
[code language="sql"]
rman target / nocatalog
run
{
sql 'alter system archive log current';
backup as compressed backupset database plus archivelog format '/u03/rmanbkp/online/Primary_bkp_for_stndby_%U';
backup current controlfile for standby format '/u03/rmanbkp/online/stby.ctl';
sql 'alter system archive log current';
}
[/code]
5.1. create the init file using the below command in primary database.
[code language="sql"] create pfile='/u03/rmanbkp/prime/pfile_for_standby.txt' from spfile; [/code]
— Primary PFILE —
[code language="sql"] primeprd1.__data_transfer_cache_size=0 primeprd2.__data_transfer_cache_size=0 primeprd1.__db_cache_size=7046430720 primeprd2.__db_cache_size=7147094016 primeprd1.__java_pool_size=67108864 primeprd2.__java_pool_size=67108864 primeprd1.__large_pool_size=301989888 primeprd2.__large_pool_size=301989888 primeprd2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primeprd1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primeprd1.__pga_aggregate_target=3221225472 primeprd2.__pga_aggregate_target=3221225472 primeprd1.__sga_target=9663676416 primeprd2.__sga_target=9663676416 primeprd1.__shared_io_pool_size=469762048 primeprd2.__shared_io_pool_size=469762048 primeprd1.__shared_pool_size=1644167168 primeprd2.__shared_pool_size=1577058304 primeprd1.__streams_pool_size=100663296 primeprd2.__streams_pool_size=67108864 *.audit_file_dest='/u03/app/oracle/admin/primeprd/adump' *.audit_trail='db' *.cluster_database=TRUE *.compatible='12.1.0.2.0' *.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763' *.cpu_count=16 *.db_block_size=8192 *.db_create_file_dest='+DATAC1' *.db_domain='' *.db_file_name_convert='+DATAC1/primedr/','+DATAC1/primeprd/','+RECOC1/primedr/','+RECOC1/primeprd/' *.db_name='primeprd' *.db_recovery_file_dest='+DATAC1' *.db_recovery_file_dest_size=536870912000 *.db_writer_processes=6 *.ddl_lock_timeout=30 *.diagnostic_dest='/u03/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primeprdXDB)' *.fal_client='primeprd' *.fal_server='primedr' primeprd2.instance_number=2 primeprd1.instance_number=1 *.log_archive_config='dg_config=(primeprd,primedr)' *.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primeprd' *.log_archive_dest_2='service=primedr LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primedr' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='log%t_%s_%r.arc' *.log_file_name_convert='+DATAC1/primedr/','+DATAC1/primeprd/','+RECOC1/primedr/','+RECOC1/primeprd/' *.nls_length_semantics='CHAR' *.open_cursors=2000 *.optimizer_adaptive_features=FALSE *.pga_aggregate_target=3072m *.processes=1500 *.remote_login_passwordfile='exclusive' *.resource_manager_cpu_allocation=48 *.resource_manager_plan='DEFAULT' *.session_cached_cursors=100 *.sessions=2272 *.sga_max_size=17179869184 *.sga_target=9216m *.standby_file_management='AUTO' *.temp_undo_enabled=TRUE primeprd2.thread=2 primeprd1.thread=1 primeprd2.undo_tablespace='UNDOTBS2' primeprd1.undo_tablespace='UNDOTBS1' [/code]
— DR PFILE —
[code language="sql"] primedr1.__data_transfer_cache_size=0 primedr2.__data_transfer_cache_size=0 primedr1.__db_cache_size=7046430720 primedr2.__db_cache_size=7147094016 primedr1.__java_pool_size=67108864 primedr2.__java_pool_size=67108864 primedr1.__large_pool_size=301989888 primedr2.__large_pool_size=301989888 primedr2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primedr1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primedr1.__pga_aggregate_target=3221225472 primedr2.__pga_aggregate_target=3221225472 primedr1.__sga_target=9663676416 primedr2.__sga_target=9663676416 primedr1.__shared_io_pool_size=469762048 primedr2.__shared_io_pool_size=469762048 primedr1.__shared_pool_size=1644167168 primedr2.__shared_pool_size=1577058304 primedr1.__streams_pool_size=100663296 primedr2.__streams_pool_size=67108864 *.audit_file_dest='/u03/app/oracle/admin/primeprd/adump' # Create on Both Nodes *.audit_trail='db' *.cluster_database=TRUE *.compatible='12.1.0.2.0' #*.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763' *.cpu_count=16 *.db_block_size=8192 *.db_create_file_dest='+DATAC1' *.db_domain='' *.db_file_name_convert='PRIMEPRD','PRIMEDR' ##### Changed for dataguard *.db_unique_name='primedr' ##### Changed for dataguard *.db_name='primeprd' *.db_recovery_file_dest='+RECOC1' *.db_recovery_file_dest_size=536870912000 *.db_writer_processes=6 *.ddl_lock_timeout=30 *.diagnostic_dest='/u03/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primedrXDB)' ##### Changed for dataguard *.fal_client='primedr' ##### Changed for dataguard *.fal_server='primeprd' ##### Changed for dataguard primedr2.instance_number=2 primedr1.instance_number=1 *.log_archive_config='dg_config=(primeprd,primedr)' *.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primedr' ##### Changed for dataguard *.log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd' ##### Changed for dataguard *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='log%t_%s_%r.arc' *.log_file_name_convert='PRIMEPRD','PRIMEDR' ##### Changed for dataguard *.remote_listener='exadr-scan:1521' ##### Changed for dataguard *.nls_length_semantics='CHAR' *.open_cursors=2000 *.optimizer_adaptive_features=FALSE *.pga_aggregate_target=3072m *.processes=1500 *.remote_login_passwordfile='exclusive' *.resource_manager_cpu_allocation=48 *.resource_manager_plan='DEFAULT' *.session_cached_cursors=100 *.sessions=2272 *.sga_max_size=17179869184 *.sga_target=9216m *.standby_file_management='AUTO' *.temp_undo_enabled=TRUE primedr2.thread=2 primedr1.thread=1 primedr2.undo_tablespace='UNDOTBS2' primedr1.undo_tablespace='UNDOTBS1' [/code]
cp -ip pfile_for_standby.txt pfile_to_apply.ora
vi pfile_to_apply.ora
— Actual PFILE after modifying all parameters —
[code language="sql"] primedr1.__data_transfer_cache_size=0 primedr2.__data_transfer_cache_size=0 primedr1.__db_cache_size=7046430720 primedr2.__db_cache_size=7147094016 primedr1.__java_pool_size=67108864 primedr2.__java_pool_size=67108864 primedr1.__large_pool_size=301989888 primedr2.__large_pool_size=301989888 primedr2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primedr1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment primedr1.__pga_aggregate_target=3221225472 primedr2.__pga_aggregate_target=3221225472 primedr1.__sga_target=9663676416 primedr2.__sga_target=9663676416 primedr1.__shared_io_pool_size=469762048 primedr2.__shared_io_pool_size=469762048 primedr1.__shared_pool_size=1644167168 primedr2.__shared_pool_size=1577058304 primedr1.__streams_pool_size=100663296 primedr2.__streams_pool_size=67108864 *.audit_file_dest='/u03/app/oracle/admin/primeprd/adump' *.audit_trail='db' *.cluster_database=TRUE *.compatible='12.1.0.2.0' #*.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763' *.cpu_count=16 *.db_block_size=8192 *.db_create_file_dest='+DATAC1' *.db_domain='' *.db_file_name_convert='PRIMEPRD','PRIMEDR' *.db_unique_name='primedr' *.db_name='primeprd' *.db_recovery_file_dest='+RECOC1' *.db_recovery_file_dest_size=536870912000 *.db_writer_processes=6 *.ddl_lock_timeout=30 *.diagnostic_dest='/u03/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primedrXDB)' *.fal_client='primedr' *.fal_server='primeprd' primedr2.instance_number=2 primedr1.instance_number=1 *.log_archive_config='dg_config=(primeprd,primedr)' *.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primedr' *.log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='log%t_%s_%r.arc' *.log_file_name_convert='PRIMEPRD','PRIMEDR' *.remote_listener='exadr-scan:1521' *.nls_length_semantics='CHAR' *.open_cursors=2000 *.optimizer_adaptive_features=FALSE *.pga_aggregate_target=3072m *.processes=1500 *.remote_login_passwordfile='exclusive' *.resource_manager_cpu_allocation=48 *.resource_manager_plan='DEFAULT' *.session_cached_cursors=100 *.sessions=2272 *.sga_max_size=17179869184 *.sga_target=9216m *.standby_file_management='AUTO' *.temp_undo_enabled=TRUE primedr2.thread=2 primedr1.thread=1 primedr2.undo_tablespace='UNDOTBS2' primedr1.undo_tablespace='UNDOTBS1' [/code]
6. Create same RMAN backup directory in DR and copy the backup Piece and init file from Production to DR server.
Also do not forget to create directory for adump in path ‘/u03/app/oracle/admin/primeprd/adump’
[code language="sql"] startup nomount pfile='/u03/rmanbkp/prime/pfile_to_apply.ora'; [/code]
[code language="sql"] rman target sys/*****@primeprd auxiliary / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 28 10:05:31 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMEPRD (DBID=111111111111111) connected to auxiliary database: PRIMEPRD (not mounted) show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name PRIMEPRD are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/rmanbkp/prime/autobackup_control_file_%F'; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/rmanbkp/prime'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/app/oracle/product/12.1.0.2/payapps/dbs/snapcf_primeprd1.f'; # default DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; [/code]
9. start the MRP process using the below command in SQL prompt.
[code language="sql"] ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; [/code]
10. In case if you get error while receiving logging onto standby use the below command and create password files on both nodes of production and dr
— For Oracle 12c, do not create password files with this method. Go to section below for Dataguard Broker Setup
[code language="sql"] orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimeprd1 force=y ignorecase=y orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimeprd2 force=y ignorecase=y [/code]
[code language="sql"] orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimedr1 force=y ignorecase=y orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimedr2 force=y ignorecase=y select * from v$pwfile_users; [/code]
Check by logging in from sqlplus from both production site and DR site from both nodes
From DR both nodes
[code language="sql"] sqlplus sys/*****@primeprd as sysdba [/code]
From Prod Both nodes
[code language="sql"] sqlplus sys/******@primedr as sysdba [/code]
Stop and start recovery again in DR
[code language="sql"] alter database recover managed standby database cancel; alter database recover managed standby database using current logfile disconnect from session; select process,status from v$managed_standby; [/code]
11. Create the spfile and add the newly created control files
[code language="sql"] show parameter control_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATAC1/PRIMEDR/CONTROLFILE/current.636.915704919, +RECOC1/PRIMEDR/CONTROLFILE/current.15794.915704923 [/code]
Add the controlfile string in the pfile you created earlier called pfile_to_apply.ora
cd /u03/rmanbkp/prime/
vi pfile_to_apply.ora
*.control_files=’+DATAC1/PRIMEDR/CONTROLFILE/current.636.915704919′,’+RECOC1/PRIMEDR/CONTROLFILE/current.15794.915704923′
[code language="sql"] shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. startup nomount pfile='/u03/rmanbkp/prime/pfile_to_apply.ora'; create spfile='+DATAC1/primedr/spfileprimedr.ora' from pfile='/u03/rmanbkp/prime/pfile_to_apply.ora'; shutdown immediate; [/code]
cd /u03/app/oracle/product/12.1.0.2/payapps/dbs/
vi initprimedr1.ora
–add only one line pointing to the SPFILE in ASM
spfile=’+DATAC1/primedr/spfileprimedr.ora’
[code language="sql"] startup mount pfile='/u03/app/oracle/product/12.1.0.2/payapps/dbs/initprimedr1.ora'; show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATAC1/primedr/spfileprimedr. ora alter database recover managed standby database using current logfile disconnect from session; [/code]
12. Create cluster server using the below command.
[code language="bash"] srvctl add database -d primedr -o /u03/app/oracle/product/12.1.0.2/payapps -p +DATAC1/primedr/spfileprimedr.ora -r physical_standby -a DATAC1,RECOC1 -s MOUNT srvctl add instance -d primedr -i primedr1 -n exadrdbadm01 srvctl add instance -d primedr -i primedr2 -n exadrdbadm02 srvctl add service -db onlndr -s onlnprod -preferred onlndr1,onlndr2 srvctl add service -db primedr -s primeprod -preferred primedr1,primedr2 srvctl config service -s onlnprod -db onlndr srvctl config service -s primeprod -db primedr srvctl stop database -d primedr -- if gives error stop from sqlplus srvctl start database -d primedr -o mount [/code]
— Start Recovery Again —
[code language="sql"] alter database recover managed standby database using current logfile disconnect from session; [/code]
13.1. Add the below entries in both production nodes tnsnames.ora file as a oracle user. (Not required from ORacle 12c 12.1.0.2 onwards)
primeprd_DGMGRL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primeprd_DGMGRL) ) )
13.2. Add the below entries in both DR nodes tnsnames.ora file as a oracle user.
primedr_DGMGRL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primedr_DGMGRL) ) )
13.3. Add the below entries in production node1 listener.ora file as a grid user.
(SID_DESC = (GLOBAL_DBNAME = primeprd_DGMGRL) (ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps) (SID_NAME = primeprd1) )
13.4. Add the below entries in production node2 listener.ora file as a grid user.
(SID_DESC = (GLOBAL_DBNAME = primeprd_DGMGRL) (ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps) (SID_NAME = primeprd2) )
13.5. Add the below entries in DR node1 listener.ora file as a grid user.
(SID_DESC = (GLOBAL_DBNAME = primedr_DGMGRL) (ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps) (SID_NAME = primedr1) )
13.6. Add the below entries in DR node2 listener.ora file as a grid user.
(SID_DESC = (GLOBAL_DBNAME = primedr_DGMGRL) (ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps) (SID_NAME = primedr2) )
13.7. Using the below command to restart the listener.
[code language="sql"] $ srvctl stop listener $ srvctl start listener [/code]
14.1. Set the below parameter in primary database.
[code language="sql"] alter system set dg_broker_config_file1 = '+DATAC1/PRIMEPRD/dr1primeprd.dat' scope=both sid='*'; alter system set dg_broker_config_file2 = '+DATAC1/PRIMEPRD/dr2primeprd.dat' scope=both sid='*'; alter system set dg_broker_start = true scope=both sid='*'; [/code]
14.2. Set the below parameter in standby database.
[code language="sql"] alter system set dg_broker_config_file1 = '+DATAC1/PRIMEDR/dr1primedr.dat' scope=both sid='*'; alter system set dg_broker_config_file2 = '+DATAC1/PRIMEDR/dr2primedr.dat' scope=both sid='*'; alter system set dg_broker_start = true scope=both sid='*'; [/code]
15.1. Configure the dataguard brocker using the below command from production database server
[code language="bash"] /home/oracle::11g- dgmgrl connect sys/***** create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd; add database primedr as connect identifier is primedr maintained as physical; Configuration "dg_primeprd" created with primary database "primeprd" add database primedr as connect identifier is primedr maintained as physical; Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Failed. remove configuration; Removed configuration [/code]
Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.
[code language="sql"] alter system set log_archive_dest_2='' scope=both sid='*'; [/code]
Disable then Enable the broker parameter on both the Primary and Standby databases.
— Primary
[code language="sql"] alter system set dg_broker_start=false scope=both sid='*'; alter system set dg_broker_start=true scope=both sid='*'; [/code]
— Standby
[code language="sql"] alter system set dg_broker_start=false scope=both sid='*'; alter system set dg_broker_start=true scope=both sid='*'; [/code]
On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.
[code language="bash"] /home/oracle::11g-- dgmgrl connect sys/***** create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd; add database primedr as connect identifier is primedr maintained as physical; enable configuration; [/code]
Set back the log_archive_dest_2 on both production and dr
— In Production Database —
[code language="sql"] alter system set log_archive_dest_2='service=primedr LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primedr' scope=both sid='*'; [/code]
— In DR Database —
[code language="sql"] alter system set log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd' scope=both sid='*'; [/code]
If in Broker you get below error
[code language="bash"] show configuration Configuration - dg_primeprd Protection Mode: MaxPerformance Members: primeprd - Primary database primedr - Physical standby database Error: ORA-16664: unable to receive the result from a database Fast-Start Failover: DISABLED Configuration Status: ERROR (status updated 40 seconds ago) Check in Broker Log File. Mon Jul 04 14:41:46 2016 Error 1017 received logging on to the standby ------------------------------------------------------------ Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191 Failed to send message to site . Error code is ORA-01017. [/code]
Then re-create password files in ASM in 12c like below, the correct format is pwd
Primary
[code language="bash"] srvctl config database -d primeprd orapwd file='+DATAC1/PRIMEPRD/pwdprimeprd' entries=10 dbuniquename=primeprd password=****** force=y ignorecase=y [/code]
Standby
Check configuration now and the error should have vanished and the status is success
[code language="bash"] show configuration Configuration - dg_primeprd Protection Mode: MaxPerformance Members: primeprd - Primary database primedr - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 18 seconds ago) [/code]
— Try doing Switchover using DGMGRL Now —
[code language="bash"] switchover to primedr Performing switchover NOW, please wait... Error: ORA-16644: apply instance not available Failed. Unable to switchover, primary database is still "primeprd" edit database primedr set state='APPLY-ON' with apply instance='primedr1'; show database verbose primedr Database - primedr Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 6.00 KByte/s Active Apply Rate: 755.00 KByte/s Maximum Apply Rate: 827.00 KByte/s Real Time Query: OFF Instance(s): primedr1 (apply instance) primedr2 switchover to primedr Performing switchover NOW, please wait... New primary database "primedr" is opening... Oracle Clusterware is restarting database "primeprd" ... Switchover succeeded, new primary is "primedr" show configuration Configuration - dg_primeprd Protection Mode: MaxPerformance Members: primedr - Primary database primeprd - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 40 seconds ago) edit database primeprd set state='APPLY-ON' with apply instance='primeprd1'; show database verbose primeprd Database - primeprd Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 307.00 KByte/s Active Apply Rate: 0 Byte/s Maximum Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): primeprd1 (apply instance) primeprd2 DGMGRL show configuration Configuration - dg_primeprd Protection Mode: MaxPerformance Members: primedr - Primary database primeprd - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 55 seconds ago) Connect to DR site broker to intiate switchover switchover to primeprd ; Performing switchover NOW, please wait... Operation requires a connection to instance "primeprd1" on database "primeprd" Connecting to instance "primeprd1"... Connected as SYSDBA. New primary database "primeprd" is opening... Oracle Clusterware is restarting database "primedr" ... Switchover succeeded, new primary is "primeprd show configuration Configuration - dg_primeprd Protection Mode: MaxPerformance Members: primeprd - Primary database primedr - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 11 seconds ago [/code]
