standby
Source database : frepo
unq - frepo
db_name - frepo
instance names - frepo1, frepo2
Target database : frepodr
unq - frepodr
db_name - frepo
instance names - frepo1, frepo2
Create temporary listener
LISTENER_TEMP=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL=TCP)(HOST=exadb01)(PORT=1525))
)
)
SID_LIST_LISTENER_TEMP =
(SID_LIST=
(SID_DESC=
(SID_NAME=frepo1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
)
)
******************************************************************************
Primary
Add tns entries of source and target on all the 2 nodes.
frepo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo)
)
)
frepoDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo1)
)
)
******************************************************************************
Standby
Add tns entries of source and target on all the 3 nodes.
frepo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo)
)
)
frepoDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo1)
)
)
******************************************************************************
Primary
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepo' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=frepodr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepodr' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both sid='*';
alter system set FAL_SERVER='frepodr'scope=both sid='*';
alter system set FAL_CLIENT='frepo'scope=both sid='*';
alter system set log_archive_config='dg_config=(frepo,frepodr)' scope=both sid='*';
create pfile from spfile;
alter user sys identified by "xxxxxxxx" account unlock;
orapwd file=orapwfrepo1 password=xxxxxxxx entries=5 force=y
scp initfrepo1.ora oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initfrepo1.ora
scp orapwfrepo1 oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo1
scp orapwfrepo1 oracle@exadb02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo2
******************************************************************************
Standby
At this point Parameter file on standby server will have all parameters including RAC
Rename the init file as below to differentiate
cp initfrepo1.ora initfrepo1.ora.rac
Now edit the initfrepo1.ora and remove all the RAC related parameters which includes below
instance related, cluster_database, threads, undo etc.. Keep the frepo1_xxx parameters as is.
2nd instance pfile
frepo1.__db_cache_size=1056964608
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo1.__pga_aggregate_target=536870912
frepo1.__sga_target=1610612736
frepo1.__shared_io_pool_size=0
frepo1.__shared_pool_size=419430400
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='exadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'
Create respective directories here as per pfile
. oraenv=frepo1
startup nomount
******************************************************************************
Primary
rman target sys/xxxxxxxx@frepo auxiliary sys/xxxxxxxx@frepoDR
duplicate target database for standby from active database dorecover;
After finish exit rman console
******************************************************************************
Standby
create a rac pfile called initmain.ora
It looks like below
frepo1.__db_cache_size=1056964608
frepo2.__db_cache_size=1056964608
frepo2.__java_pool_size=16777216
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo2.__large_pool_size=33554432
frepo2.__pga_aggregate_target=536870912
frepo1.__pga_aggregate_target=536870912
frepo2.__sga_target=1610612736
frepo1.__sga_target=1610612736
frepo2.__shared_io_pool_size=0
frepo1.__shared_io_pool_size=0
frepo2.__shared_pool_size=419430400
frepo1.__shared_pool_size=419430400
frepo2.__streams_pool_size=0
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
frepo1.instance_number=1
frepo2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='pxadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
frepo2.thread=2
frepo1.thread=1
frepo2.undo_tablespace='UNDOTBS2'
frepo1.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='+DATA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'
Make spfile from that
create spfile='+DATA/frepo/parameterfile/spfilefrepo.ora' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initmain.ora';
shut immediate;
vi initfrepo1.ora
spfile='+DATA/frepo/parameterfile/spfilefrepo.ora'
scp initfrepo1.ora oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/initfrepo2.ora
******************************************************************************
Standby
Adding the 2 instances to cluster
srvctl add database -d frepo -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p +DATA/frepo/parameterfile/spfilefrepo.ora -r physical_standby -a DATA,REDO
srvctl add instance -d frepo -i frepo1 -n exadb01
srvctl add instance -d frepo -i frepo2 -n exadb02
srvctl start database -d frepo
srvctl status database -d frepo
srvctl config database -d frepo
******************************************************************************
Standby
After verifying the config stop the database on all the 2 instances and start on a single instance.
srvctl start instance -d frepo -i frepo1
sqlplus / as sysdba
start MRP process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
******************************************************************************
Note
To stop MRP process use
alter database recover managed standby database cancel;
******************************************************************************
To very dataguard sync use below commands
Standby
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
PRIMARY
select thread#,max(sequence#) from v$archived_log group by thread#;
or
select max(sequence#) from v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
Source database : frepo
unq - frepo
db_name - frepo
instance names - frepo1, frepo2
Target database : frepodr
unq - frepodr
db_name - frepo
instance names - frepo1, frepo2
Create temporary listener
LISTENER_TEMP=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL=TCP)(HOST=exadb01)(PORT=1525))
)
)
SID_LIST_LISTENER_TEMP =
(SID_LIST=
(SID_DESC=
(SID_NAME=frepo1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
)
)
******************************************************************************
Primary
Add tns entries of source and target on all the 2 nodes.
frepo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo)
)
)
frepoDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo1)
)
)
******************************************************************************
Standby
Add tns entries of source and target on all the 3 nodes.
frepo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo)
)
)
frepoDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = frepo1)
)
)
******************************************************************************
Primary
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepo' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=frepodr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepodr' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both sid='*';
alter system set FAL_SERVER='frepodr'scope=both sid='*';
alter system set FAL_CLIENT='frepo'scope=both sid='*';
alter system set log_archive_config='dg_config=(frepo,frepodr)' scope=both sid='*';
create pfile from spfile;
alter user sys identified by "xxxxxxxx" account unlock;
orapwd file=orapwfrepo1 password=xxxxxxxx entries=5 force=y
scp initfrepo1.ora oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initfrepo1.ora
scp orapwfrepo1 oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo1
scp orapwfrepo1 oracle@exadb02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo2
******************************************************************************
Standby
At this point Parameter file on standby server will have all parameters including RAC
Rename the init file as below to differentiate
cp initfrepo1.ora initfrepo1.ora.rac
Now edit the initfrepo1.ora and remove all the RAC related parameters which includes below
instance related, cluster_database, threads, undo etc.. Keep the frepo1_xxx parameters as is.
2nd instance pfile
frepo1.__db_cache_size=1056964608
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo1.__pga_aggregate_target=536870912
frepo1.__sga_target=1610612736
frepo1.__shared_io_pool_size=0
frepo1.__shared_pool_size=419430400
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='exadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'
Create respective directories here as per pfile
. oraenv=frepo1
startup nomount
******************************************************************************
Primary
rman target sys/xxxxxxxx@frepo auxiliary sys/xxxxxxxx@frepoDR
duplicate target database for standby from active database dorecover;
After finish exit rman console
******************************************************************************
Standby
create a rac pfile called initmain.ora
It looks like below
frepo1.__db_cache_size=1056964608
frepo2.__db_cache_size=1056964608
frepo2.__java_pool_size=16777216
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo2.__large_pool_size=33554432
frepo2.__pga_aggregate_target=536870912
frepo1.__pga_aggregate_target=536870912
frepo2.__sga_target=1610612736
frepo1.__sga_target=1610612736
frepo2.__shared_io_pool_size=0
frepo1.__shared_io_pool_size=0
frepo2.__shared_pool_size=419430400
frepo1.__shared_pool_size=419430400
frepo2.__streams_pool_size=0
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
frepo1.instance_number=1
frepo2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='pxadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
frepo2.thread=2
frepo1.thread=1
frepo2.undo_tablespace='UNDOTBS2'
frepo1.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='+DATA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'
Make spfile from that
create spfile='+DATA/frepo/parameterfile/spfilefrepo.ora' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initmain.ora';
shut immediate;
vi initfrepo1.ora
spfile='+DATA/frepo/parameterfile/spfilefrepo.ora'
scp initfrepo1.ora oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/initfrepo2.ora
******************************************************************************
Standby
Adding the 2 instances to cluster
srvctl add database -d frepo -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p +DATA/frepo/parameterfile/spfilefrepo.ora -r physical_standby -a DATA,REDO
srvctl add instance -d frepo -i frepo1 -n exadb01
srvctl add instance -d frepo -i frepo2 -n exadb02
srvctl start database -d frepo
srvctl status database -d frepo
srvctl config database -d frepo
******************************************************************************
Standby
After verifying the config stop the database on all the 2 instances and start on a single instance.
srvctl start instance -d frepo -i frepo1
sqlplus / as sysdba
start MRP process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
******************************************************************************
Note
To stop MRP process use
alter database recover managed standby database cancel;
******************************************************************************
To very dataguard sync use below commands
Standby
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
PRIMARY
select thread#,max(sequence#) from v$archived_log group by thread#;
or
select max(sequence#) from v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
