Active Dataguard on RAC - Two node - Oracle 11gR2
servers:
primary nodes: prim-serv1, prim-serv2
stadby nodes : stdby-serv1, stdby-serv2
scans: prim-scan-name
: stdby-scan-name
VIPs : prim-serv1-vip, prim-serv2-vip
: stdby-serv1-vip, stdby-serv2-vip
Primary DB: PRIM (PRIM1, PRIM2)
Standby DB: STDBY (STDBY1, STDBY2)
----------------------------------------------
<prim-serv1>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PRIM_DGMGRL)
(SID_NAME=prim1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<prim-serv2>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PRIM_DGMGRL)
(SID_NAME=PRIM2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<stdby-serv1>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=STDBY_DGMGRL)
(SID_NAME=STDBY1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<stdby-serv2>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=STDBY_DGMGRL)
(SID_NAME=STDBY2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
-----------------
--logon to both primary and standby all nodes in both the clusters:
cd /u01/app/oracle/product/11.2.0/db_1/network/admin
--add to tnsnames.ora:
PRIM_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-serv1>-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-serv2>-vip)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIM_DGMGRL))
)
)
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-scn-name>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <PRIM_SID>)
)
)
PRIM1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-serv1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM1)
(SID = PRIM1)
)
)
PRIM2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-serv2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PRIM2)
(SID = PRIM2)
)
)
STDBY_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv2-vip)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY_DGMGRL))
)
)
STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-scan-name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =STDBY)
)
)
STDBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY1)
(SID = STDBY1)
)
)
STDBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY2)
(SID = STDBY2)
)
)
--on prim-serv1
export ORACLE_SID=prim1
sqlplus as sysdba
SQL>create pfile='initPRIM.ora' from spfile;
SQL>alter system set log_archive_config = 'DG_CONFIG=(PRIM,STDBY)' scope=both sid='*';
SQL>alter system set log_archive_dest_1 = 'LOCATION=+<DG>FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both sid='*';
SQL>alter system set log_archive_dest_2 = 'SERVICE=STDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY' scope=both sid='*';
SQL>alter system set log_archive_dest_state_1=enable scope=both;
SQL>alter system set log_archive_dest_state_2=enable scope=both;
SQL>alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*';
SQL>alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';
SQL>alter system set standby_file_management=auto scope=both sid='*';
SQL>alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=both sid='*';
SQL>alter system set db_block_checksum = typical scope=both sid='*';
SQL>alter system set db_block_checking=true scope=both sid='*';
--Stop database using srvctl
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list
SQL>alter database force logging;
SQL>shutdown immediate;
Start database using srvctl
--Create standby redo logs: (# of logs vary) (same size as the online redo logs)
SQL>alter database add standby logfile group 7 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 8 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 9 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 10 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 11 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 12 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 13 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 14 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 15 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 16 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 17 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 18 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 19 ('+REDO1','+REDO2') size 500M;
SQL>select * from v$log;
SQL>select * from v$standby_log;
--ON PRIMARY
--Copy password files. Create pfile. Start standby instance.
% scp $ORACLE_HOME/dbs/orapwPRIM1 stdby-serv1:$ORACLE_HOME/dbs/orapwPRIM1
% scp $ORACLE_HOME/dbs/orapwPRIM1 stdby-serv2:$ORACLE_HOME/dbs/orapwPRIM2
--ON Both STANDBY nodes create adump directory
mkdir -p /u01/app/oracle/admin/STDBY/adump
--On first standby server create initSTDBY1.ora in /tmp with only db_name=STDBY
cd /tmp vi initSTDBY1.ora
db_name=STDBY
export ORACLE_SID=STDBYP1
sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/initSTDBY1.ora;
--test connection:
sqlplus sys/mypasswd@<STDBY>_DGMGRL as sysdba
--ON PRIMARY
--Use RMAN to create standby database.
--on prim-serv1
cd to <scrpit-dir>
export ORACLE_SID=PRIM1
echo $ORACLE_SID
rman
connect target /
connect auxiliary sys/password@STDBY_DGMGRL
--- returns connected to (not started)
@create_phys_sby.cmd
----start create_phy_sby.cmd ----
run {
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL sby TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
parameter_value_convert 'PRIM','STDBY'
SET db_unique_name='STDBY'
SET LOG_ARCHIVE_MAX_PROCESSES='5'
SET LOG_ARCHIVE_DEST_2='SERVICE=PRIM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM'
SET FAL_SERVER='PRIM'
SET FAL_CLIENT='STDBY1'
SET STANDBY_FILE_MANAGEMENT='AUTO'
SET log_archive_config='DG_CONFIG=(PRIM,STDBY)'
-- set db_create_file_dest='+<STDBY-DG>' --if different DG is used
set control_files='+STDBY-DG/STDBY/controlfile/control01.dbf'
set instance_number='1'
set audit_file_dest='/u01/app/oracle/admin/STDBY/adump'
;
}
----------end create_phy_sby.cmd --------------
--on STANDBY location create spfile for standby database from spfile created from rman
export ORACLE_SID=STDBY1
sqlplus / as sysdba
sqlplus> create pfile='/tmp/initSTDBY1.ora' from spfile;
--Edit the new pfile and replace every occurrence of PRIM, with STDBy, other than the db_name,
--which must stay the same. Pay attention to instance_number. Make sure the control file
--reflects the control file that was used in the rman duplicate ('+STDBY-DG/STDBY/controlfile/control01.dbf')
--Pay attention some enteries already have standby name so rename might mess up
--VERIFY adump sid is correct, db_unique_name is correct, etc
--*.db_name='PRIM' (VERIFY & DO NOT CHNGE)
SQL> create spfile='+STDBY-DG/STDBY/spfileSTDBY.ora' from pfile='/tmp/initSTDBY1.ora';
--Create a pointer pfile for each instance and copy to $ORACLE_HOME/dbs on the stanby hosts
--remove spfile created by rman
--on stdby-serv1
--create initSTDBY1.ora under $ORACLE_HOME/dbs with the following line:
spfile='+STDBY-DG/STDBY/spfileSTDBY.ora'
--on stdby-serv2
--create initSTDBY2.ora under $ORACLE_HOME/dbs with the following line:
spfile='+STDBY-DG/STDBY/spfileSTDBY.ora'
--Create two new control files for stdby on REDO1 and 2 (which ever disk group you choose).
--On stdby-serv1
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter system set control_files='+REDO1','+REDO2' scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
rman target /
restore controlfile from '+STDBY-DG/STDBY/controlfile/control01.dbf';
SQL> startup mount;
select process,status,sequence# from v$managed_standby;
alter database recover managed standby database cancel;
alter database open read only;
select status,instance_name,database_role,open_mode from v$database,v$instance;
alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;
select process,status,sequence# from v$managed_standby;
-- Make sure this query return rows
SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D
WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';
--start stdby2 instance on node 2 also. We have the recovery on first node so just open on second
export ORACLE_SID=stdby2
sqlplus / as sysdba
startup mount;
alter database open read only;
select process,status,sequence# from v$managed_standby;
--Verify configuration parameters on both primary and standby databases, adjust if needed.
Standby
alter system set db_block_checksum=full scope=both sid='*';
alter system set db_block_checking=full scope=both sid='*';
alter system set fal_client='STDBY1' scope=both sid='STDBY1';
alter system set fal_client='STDBY2' scope=both sid='STDBY2';
alter system set fal_server='PRIM' scope=both sid='*';
alter system set log_archive_config='dg_config=(PRIM,STDBY)' scope=both sid='*';
alter system set log_archive_max_processes=5 scope=both sid='*';
alter system set log_archive_dest_2='service=PRIM ARCH db_unique_name=PRIM valid_for=(all_logfiles,primary_role)' scope=both sid='*';
--If using DG broker
alter system set dg_broker_config_file1='+REDO1/STDBY/parameterfile/dr1STDBY.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+REDO2/STDBY/parameterfile/dr2STDBY.dat' scope=both sid='*';
--Primary
alter system set fal_client='PRIM1' scope=both sid='PRIM1';
alter system set fal_client='PRIM2' scope=both sid='PRIM2';
alter system set fal_server='STDBY' scope=both sid='*';
alter system set db_block_checksum=full scope=both sid='*';
alter system set db_block_checking=full scope=both sid='*';
alter system set log_archive_max_processes=5 scope=both sid='*';
alter system set dg_broker_config_file1='+REDO1/PRIM/parameterfile/dr1PRIM.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+REDO2/PRIM/parameterfile/dr2PRIM.dat' scope=both sid='*';
use asmcmd to create
+REDO1/PRIM/parameterfile
+REDO2/PRIM/parameterfile
+REDO1/STDBY/parameterfile
+REDO2/STDBY/parameterfile
--on standby:
srvctl add database -d STDBY -o /u01/app/oracle/product/11.2.0/db_1 -p
'+STDBY-DG/STDBY/spfileSTDBY.ora' -n STDBY -r physical_standby -s 'READ ONLY'
srvctl add instance -d STDBY -i STDBY1 -n stdby-serv1
srvctl add instance -d STDBY -i STDBY2 -n stdby-serv2
--shutdown all standby instances via sqlplus and start database using srvctl
srvctl start database -d STDBY
*****start the managed recovery every time the standby is re-started as shown below ****
*****only on one node *******
--Logon to standby instance (STDBY1) as SYSDBA, and issue:
SBY SQL> alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;
select process,status,sequence# from v$managed_standby;
-- Make sure this query return rows
SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D
WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';
ADG
--------------------------------
Using Active Data Guard
--On all other standby nodes run the following to see that it is open read only.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN STDBY2 PHYSICAL STANDBY READ ONLY WITH APPLY
--on primary ( test if the DG is working)
PRI SQL> alter system switch logfile;
--Monitor alert logs, make sure logs are shipped and applied.
--Also make sure that the archived logs are applied on all standby destinations.
select process,status,sequence# from v$managed_standby; --- on both primary & standby
----IF DG Broker is used ----
Start Data Guard broker and create configuration.
FOR BOTH DATABASES
PRI SQL> alter system set DG_BROKER_START=TRUE scope=both;
SBY SQL> alter system set DG_BROKER_START=TRUE scope=both;
ON PRIMARY
> dgmgrl /
DGMGRL> create configuration 'DGConfigPRIM' as primary database is PRIM connect identifier is PRIM;
DGMGRL> add database STDBY as connect identifier is STDBY;
DGMGRL> show configuration verbose
DGMGRL> enable configuration;
DGMGRL> show configuration verbose
DGMGRL> show database verbose PRIM
DGMGRL> show database verbose STDBY
DGMGRL> show instance verbose <all instances>
ie: show instance verbose PRIM1
Check StaticConnectIdentifier,
StandbyArchiveLocation, ...
Following is the sample how to modify Data Guard parameters.
DGMGRL> EDIT DATABASE <dbname> SET PROPERTY StandbyArchiveLocation =
'<location>';
servers:
primary nodes: prim-serv1, prim-serv2
stadby nodes : stdby-serv1, stdby-serv2
scans: prim-scan-name
: stdby-scan-name
VIPs : prim-serv1-vip, prim-serv2-vip
: stdby-serv1-vip, stdby-serv2-vip
Primary DB: PRIM (PRIM1, PRIM2)
Standby DB: STDBY (STDBY1, STDBY2)
----------------------------------------------
<prim-serv1>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PRIM_DGMGRL)
(SID_NAME=prim1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<prim-serv2>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PRIM_DGMGRL)
(SID_NAME=PRIM2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<stdby-serv1>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=STDBY_DGMGRL)
(SID_NAME=STDBY1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
<stdby-serv2>
cd /u01/app/grid/11.2.0.3/network/admin
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=STDBY_DGMGRL)
(SID_NAME=STDBY2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
lsnrctl reload
lsnrctl status
-----------------
--logon to both primary and standby all nodes in both the clusters:
cd /u01/app/oracle/product/11.2.0/db_1/network/admin
--add to tnsnames.ora:
PRIM_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-serv1>-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-serv2>-vip)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIM_DGMGRL))
)
)
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <prim-scn-name>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <PRIM_SID>)
)
)
PRIM1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-serv1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM1)
(SID = PRIM1)
)
)
PRIM2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-serv2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PRIM2)
(SID = PRIM2)
)
)
STDBY_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv2-vip)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY_DGMGRL))
)
)
STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-scan-name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =STDBY)
)
)
STDBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY1)
(SID = STDBY1)
)
)
STDBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-serv2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY2)
(SID = STDBY2)
)
)
--on prim-serv1
export ORACLE_SID=prim1
sqlplus as sysdba
SQL>create pfile='initPRIM.ora' from spfile;
SQL>alter system set log_archive_config = 'DG_CONFIG=(PRIM,STDBY)' scope=both sid='*';
SQL>alter system set log_archive_dest_1 = 'LOCATION=+<DG>FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both sid='*';
SQL>alter system set log_archive_dest_2 = 'SERVICE=STDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY' scope=both sid='*';
SQL>alter system set log_archive_dest_state_1=enable scope=both;
SQL>alter system set log_archive_dest_state_2=enable scope=both;
SQL>alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*';
SQL>alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';
SQL>alter system set standby_file_management=auto scope=both sid='*';
SQL>alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=both sid='*';
SQL>alter system set db_block_checksum = typical scope=both sid='*';
SQL>alter system set db_block_checking=true scope=both sid='*';
--Stop database using srvctl
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list
SQL>alter database force logging;
SQL>shutdown immediate;
Start database using srvctl
--Create standby redo logs: (# of logs vary) (same size as the online redo logs)
SQL>alter database add standby logfile group 7 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 8 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 9 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 10 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 11 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 12 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 13 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 14 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 15 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 16 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 17 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 18 ('+REDO1','+REDO2') size 500M;
SQL>alter database add standby logfile group 19 ('+REDO1','+REDO2') size 500M;
SQL>select * from v$log;
SQL>select * from v$standby_log;
--ON PRIMARY
--Copy password files. Create pfile. Start standby instance.
% scp $ORACLE_HOME/dbs/orapwPRIM1 stdby-serv1:$ORACLE_HOME/dbs/orapwPRIM1
% scp $ORACLE_HOME/dbs/orapwPRIM1 stdby-serv2:$ORACLE_HOME/dbs/orapwPRIM2
--ON Both STANDBY nodes create adump directory
mkdir -p /u01/app/oracle/admin/STDBY/adump
--On first standby server create initSTDBY1.ora in /tmp with only db_name=STDBY
cd /tmp vi initSTDBY1.ora
db_name=STDBY
export ORACLE_SID=STDBYP1
sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/initSTDBY1.ora;
--test connection:
sqlplus sys/mypasswd@<STDBY>_DGMGRL as sysdba
--ON PRIMARY
--Use RMAN to create standby database.
--on prim-serv1
cd to <scrpit-dir>
export ORACLE_SID=PRIM1
echo $ORACLE_SID
rman
connect target /
connect auxiliary sys/password@STDBY_DGMGRL
--- returns connected to (not started)
@create_phys_sby.cmd
----start create_phy_sby.cmd ----
run {
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL sby TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
parameter_value_convert 'PRIM','STDBY'
SET db_unique_name='STDBY'
SET LOG_ARCHIVE_MAX_PROCESSES='5'
SET LOG_ARCHIVE_DEST_2='SERVICE=PRIM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM'
SET FAL_SERVER='PRIM'
SET FAL_CLIENT='STDBY1'
SET STANDBY_FILE_MANAGEMENT='AUTO'
SET log_archive_config='DG_CONFIG=(PRIM,STDBY)'
-- set db_create_file_dest='+<STDBY-DG>' --if different DG is used
set control_files='+STDBY-DG/STDBY/controlfile/control01.dbf'
set instance_number='1'
set audit_file_dest='/u01/app/oracle/admin/STDBY/adump'
;
}
----------end create_phy_sby.cmd --------------
--on STANDBY location create spfile for standby database from spfile created from rman
export ORACLE_SID=STDBY1
sqlplus / as sysdba
sqlplus> create pfile='/tmp/initSTDBY1.ora' from spfile;
--Edit the new pfile and replace every occurrence of PRIM, with STDBy, other than the db_name,
--which must stay the same. Pay attention to instance_number. Make sure the control file
--reflects the control file that was used in the rman duplicate ('+STDBY-DG/STDBY/controlfile/control01.dbf')
--Pay attention some enteries already have standby name so rename might mess up
--VERIFY adump sid is correct, db_unique_name is correct, etc
--*.db_name='PRIM' (VERIFY & DO NOT CHNGE)
SQL> create spfile='+STDBY-DG/STDBY/spfileSTDBY.ora' from pfile='/tmp/initSTDBY1.ora';
--Create a pointer pfile for each instance and copy to $ORACLE_HOME/dbs on the stanby hosts
--remove spfile created by rman
--on stdby-serv1
--create initSTDBY1.ora under $ORACLE_HOME/dbs with the following line:
spfile='+STDBY-DG/STDBY/spfileSTDBY.ora'
--on stdby-serv2
--create initSTDBY2.ora under $ORACLE_HOME/dbs with the following line:
spfile='+STDBY-DG/STDBY/spfileSTDBY.ora'
--Create two new control files for stdby on REDO1 and 2 (which ever disk group you choose).
--On stdby-serv1
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter system set control_files='+REDO1','+REDO2' scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
rman target /
restore controlfile from '+STDBY-DG/STDBY/controlfile/control01.dbf';
SQL> startup mount;
select process,status,sequence# from v$managed_standby;
alter database recover managed standby database cancel;
alter database open read only;
select status,instance_name,database_role,open_mode from v$database,v$instance;
alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;
select process,status,sequence# from v$managed_standby;
-- Make sure this query return rows
SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D
WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';
--start stdby2 instance on node 2 also. We have the recovery on first node so just open on second
export ORACLE_SID=stdby2
sqlplus / as sysdba
startup mount;
alter database open read only;
select process,status,sequence# from v$managed_standby;
--Verify configuration parameters on both primary and standby databases, adjust if needed.
Standby
alter system set db_block_checksum=full scope=both sid='*';
alter system set db_block_checking=full scope=both sid='*';
alter system set fal_client='STDBY1' scope=both sid='STDBY1';
alter system set fal_client='STDBY2' scope=both sid='STDBY2';
alter system set fal_server='PRIM' scope=both sid='*';
alter system set log_archive_config='dg_config=(PRIM,STDBY)' scope=both sid='*';
alter system set log_archive_max_processes=5 scope=both sid='*';
alter system set log_archive_dest_2='service=PRIM ARCH db_unique_name=PRIM valid_for=(all_logfiles,primary_role)' scope=both sid='*';
--If using DG broker
alter system set dg_broker_config_file1='+REDO1/STDBY/parameterfile/dr1STDBY.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+REDO2/STDBY/parameterfile/dr2STDBY.dat' scope=both sid='*';
--Primary
alter system set fal_client='PRIM1' scope=both sid='PRIM1';
alter system set fal_client='PRIM2' scope=both sid='PRIM2';
alter system set fal_server='STDBY' scope=both sid='*';
alter system set db_block_checksum=full scope=both sid='*';
alter system set db_block_checking=full scope=both sid='*';
alter system set log_archive_max_processes=5 scope=both sid='*';
alter system set dg_broker_config_file1='+REDO1/PRIM/parameterfile/dr1PRIM.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+REDO2/PRIM/parameterfile/dr2PRIM.dat' scope=both sid='*';
use asmcmd to create
+REDO1/PRIM/parameterfile
+REDO2/PRIM/parameterfile
+REDO1/STDBY/parameterfile
+REDO2/STDBY/parameterfile
--on standby:
srvctl add database -d STDBY -o /u01/app/oracle/product/11.2.0/db_1 -p
'+STDBY-DG/STDBY/spfileSTDBY.ora' -n STDBY -r physical_standby -s 'READ ONLY'
srvctl add instance -d STDBY -i STDBY1 -n stdby-serv1
srvctl add instance -d STDBY -i STDBY2 -n stdby-serv2
--shutdown all standby instances via sqlplus and start database using srvctl
srvctl start database -d STDBY
*****start the managed recovery every time the standby is re-started as shown below ****
*****only on one node *******
--Logon to standby instance (STDBY1) as SYSDBA, and issue:
SBY SQL> alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;
select process,status,sequence# from v$managed_standby;
-- Make sure this query return rows
SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D
WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';
ADG
--------------------------------
Using Active Data Guard
--On all other standby nodes run the following to see that it is open read only.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN STDBY2 PHYSICAL STANDBY READ ONLY WITH APPLY
--on primary ( test if the DG is working)
PRI SQL> alter system switch logfile;
--Monitor alert logs, make sure logs are shipped and applied.
--Also make sure that the archived logs are applied on all standby destinations.
select process,status,sequence# from v$managed_standby; --- on both primary & standby
----IF DG Broker is used ----
Start Data Guard broker and create configuration.
FOR BOTH DATABASES
PRI SQL> alter system set DG_BROKER_START=TRUE scope=both;
SBY SQL> alter system set DG_BROKER_START=TRUE scope=both;
ON PRIMARY
> dgmgrl /
DGMGRL> create configuration 'DGConfigPRIM' as primary database is PRIM connect identifier is PRIM;
DGMGRL> add database STDBY as connect identifier is STDBY;
DGMGRL> show configuration verbose
DGMGRL> enable configuration;
DGMGRL> show configuration verbose
DGMGRL> show database verbose PRIM
DGMGRL> show database verbose STDBY
DGMGRL> show instance verbose <all instances>
ie: show instance verbose PRIM1
Check StaticConnectIdentifier,
StandbyArchiveLocation, ...
Following is the sample how to modify Data Guard parameters.
DGMGRL> EDIT DATABASE <dbname> SET PROPERTY StandbyArchiveLocation =
'<location>';
No comments:
Post a Comment