DGTST001 Primary
|| DGTST001_STBY Standby
On Primary side -
alter
system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGTST001,DGTST001_STBY)'
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=DGTST001' scope=both sid='*';
alter
system set LOG_ARCHIVE_DEST_2='SERVICE=DGTST001_STBY ARCH
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGTST001_STBY'
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=DGTST001_STBY scope=both sid='*';
alter
system set db_file_name_convert='DGTST001_STBY','DGTST001' scope=spfile
sid='*';
alter
system set log_file_name_convert='DGTST001_STBY','DGTST001' scope=spfile
sid='*';
alter
system set standby_file_management=AUTO scope=both sid='*';
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')
On Primary and standby
update below tnsnames:
Update in all 3 nodes on
primary side and one node where we are restoring on standby side
DGTST001_PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_host_1)(PORT
= 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_host_2)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGTST001)
)
)
DGTST001_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby_host_1)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGTST001_STBY)
)
)
ON Standby Listener :
If 1st node -
SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(GLOBAL_DBNAME = DGTST001_STBY ) (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_3)
(SID_NAME = DGTST0011) ) )
On Primary
Create 1 standby group additional than the
existing no. of redo log groups:
8 on primary, so creating (8+1) groups for
standby redo log on primary.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 26
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 27
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 28
('+DATA_DG01','+FRA_DG01') size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 29 ('+DATA_DG01','+FRA_DG01')
size 1G;
Database altered.
Copy the remote login password file
(orapwDGTST0011) from the primary database system to the $ORACLE_HOME/dbs
directory on the standby database system, renaming it to orapwDGTST0011 (If 1st node used on standby)
ASM> pwcopy +DATAC1/<PRIMARY DB>/pwDGTST001 /tmp/orapwDGTST001
scp /tmp/pwDGTST001 standby-host-1: /u01/app/oracle/product/12.1.0.2/dbhome_3/dbs/orapwDGTST0011
In the $ORACLE_HOME/dbs
directory of the standby system, create an initialization parameter file named
initDGTST0011.ora with following parameters: DB_NAME=DGTST001
DB_UNIQUE_NAME=DGTST001_STBY
CLUSTER_DATABASE=FALSE
remote_login_passwordfile=EXCLUSIVE
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu
Aug 6 13:49:56 2015
Copyright (c)
1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 513642496 bytes
Fixed Size 2254624 bytes
Variable Size 402655456 bytes
Database Buffers 100663296 bytes
Redo Buffers 8069120 bytes
RMAN script -
From standby 1st node –
Create script ->
rman_standby_create_DGTST001.cmd
connect target sys/password123@DGTST001_PRIM
connect auxiliary sys/password123@DGTST001_STBY
run {
allocate channel ch1_prmy type disk;
allocate channel ch2_prmy type disk;
allocate channel ch3_prmy type disk;
allocate channel ch4_prmy type disk;
allocate auxiliary channel ch1_stby type disk;
allocate auxiliary channel ch2_stby type disk;
allocate auxiliary channel ch3_stby type disk;
allocate auxiliary channel ch4_stby type disk;
duplicate target database for standby from active
database
spfile
parameter_value_convert 'DGTST001','DGTST001_STBY'
set audit_file_dest='/u02/app/oracle/admin/DGTST001_STBY/adump'
set db_file_name_convert='DGTST001', 'DGTST001_STBY'
set log_file_name_convert='DGTST001', 'DGTST001_STBY'
set control_files='+DATAC1','+RECOC1'
set log_archive_max_processes='5'
set fal_client='DGTST001_STBY'
set fal_server='DGTST001'
set standby_file_management='AUTO'
set remote_listener='standby-host-scan:1521'
set log_archive_config='dg_config=(DGTST001,DGTST001_STBY)'
set db_unique_name='DGTST001_STBY'
set log_archive_dest_2='service=DGTST001 ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DGTST001';
}
nohup rman
cmdfile=rman_standby_create_DGTST001.cmd log=rman_standby_create_DGTST001.log
&
Now, once the restore completes, it will have a
spfile in dbs location.
Add the following entries on all 3 nodes of
primary and standby –
****************
DGTST001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Primary-host-scan)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGTST001)
)
)
DGTST001_STBY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby-host-scan)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGTST001_STBY)
)
)
****************
On standby
Create pfile='/tmp/pfileDGTST001.ora' from
spfile;
Create spfile='+DATA_DG01/DGTST001_STBY/spfileDGTST001.ora'
from pfile='/tmp/pfileDGTST001.ora';
Edit initDGTST0013.ora and have only one
parameter
spfile=’+DATA_DG01/DGTST001_STBY /spfileDGTST001.
scp initDGTST0011.ora qc-or-dbadm02:/u02/app/oracle/product/11.2.0.4.RAC/dbs/initDGTST0012.ora
scp initDGTST0011.ora qc-or-dbadm02:
/u01/app/oracle/product/12.1.0.2/dbhome_3/dbs/initDGTST0012.ora
Make sure u have this already from primary –
DGTST001_STBY
alter system set cluster_database_instances=2 scope=spfile
sid='*';
alter system set cluster_database=true
scope=spfile sid='*';
alter system set instance_number=1 scope=spfile
sid='DGTST0011';
alter system set instance_number=2 scope=spfile
sid='DGTST0012';
alter system set
undo_tablespace='UNDOTBS1' sid='DGTST0011' scope=spfile;
alter system set undo_tablespace='UNDOTBS2'
sid='DGTST0012' scope=spfile;
shut immediate;
$srvctl add database -d DGTST001_STBY -o /u01/app/oracle/product/12.1.0.2/dbhome_3
$ srvctl modify database -d DGTST001_STBY -s
MOUNT -r physical_standby
$srvctl add instance -d DGTST001_STBY -i
DGTST0011 -n standby_host_1
$srvctl add instance -d DGTST001_STBY -i DGTST0012
-n qc-or-dbadm02
$ srvctl start database -d DGTST001_STBY
Primary -
Alter system set dg_broker_config_file1='+DATA_DG01/DGTST001/dr1DGTST001.dat'
scope=both sid='*';
Alter system set
dg_broker_config_file2='+DATA_DG01/DGTST001/dr2DGTST001.dat' scope=both
sid='*';
Alter system set dg_broker_start=TRUE scope=both
sid='*';
Standby -
Alter system set
dg_broker_config_file1='+DATA_DG01/DGTST001_STBY/dr1DGTST001_STBY.dat'
scope=both sid='*';
Alter system set
dg_broker_config_file2='+DATA_DG01/DGTST001_STBY/dr2DGTST001_STBY.dat'
scope=both sid='*';
Alter system set dg_broker_start=TRUE scope=both
sid='*';
Enable dg –
$dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit
Production
Copyright (c) 2000, 2009, Oracle. All rights
reserved.
Welcome to DGMGRL, type "help" for
information.
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does
not exist
Configuration details cannot be determined by
DGMGRL
DGMGRL> create configuration 'DG_DGTST001' as
primary database is 'DGTST001' connect identifier is 'DGTST001';
Configuration "DG_DGTST001" created
with primary database "DGTST001"
DGMGRL> show configuration
Configuration - DG_DGTST001
Protection Mode: MaxPerformance
Databases:
DGTST001 - Primary database
Fast-Start Failover: DISABLED
Configuration
Status:
DISABLED
DGMGRL> add database 'DGTST001_STBY' as
connect identifier is 'DGTST001_STBY' maintained as physical;
Database "DGTST001_STBY" added
DGMGRL> show configuration
Configuration - DG_DGTST001
Protection Mode: MaxPerformance
Databases:
DGTST001 - Primary database
DGTST001_STBY- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
[oracle@primary_host_1
admin]$ dgmgrl sys/password123@DGTST001
DGMGRL for
Linux: Version 12.1.0.2.0 - 64bit Production
Copyright
(c) 2000, 2013, Oracle. All rights reserved.
Welcome to
DGMGRL, type "help" for information.
Connected as
SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
show configuration
Configuration
- DG_DGTST001
Protection Mode: MaxPerformance
Members:
DGTST001
- Primary database
DGTST001_STBY - Physical standby database
Fast-Start
Failover: DISABLED
Configuration
Status:
SUCCESS (status updated 53 seconds ago)
DGMGRL>
switchover to 'DGTST001_STBY';
Performing
switchover NOW, please wait...
Operation
requires a connection to instance "DGTST0012" on database "DGTST001_STBY"
Connecting
to instance "DGTST0012"...
Connected as
SYSDBA.
New primary
database "DGTST001_STBY" is opening...
Oracle
Clusterware is restarting database "DGTST001" ...
Switchover succeeded,
new primary is "DGTST001_STBY"
DGMGRL> show configuration
Configuration
- DG_DGTST001
Protection Mode: MaxPerformance
Members:
DGTST001_STBY - Primary database
DGTST001
- Physical standby database
Fast-Start
Failover: DISABLED
Configuration
Status:
SUCCESS (status updated 20 seconds ago)
