RAC dataguard

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)

11g standby database using dataguard

For 11g databases you need to create a temporary listener with a different port on the target server or else it will show blocked status when database is started at nomount

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


Edit or change parameters from db

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 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set FAL_SERVER='testDR';
alter system set FAL_CLIENT='test';



--*.DB_FILE_NAME_CONVERT='stand','prim'*.INSTANCE_NAME='prim'
--*.LOG_FILE_NAME_CONVERT='stand','prim'


create pfile from spfile;

alter user sys identified by "password" account unlock;

orapwd file=orapwtest password=password entries=5

scp  inittest.ora oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/11.2.0/11.2.0.4/dbs/


Standby:

Modify pfile

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_2='SERVICE=test VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='test'
*.FAL_CLIENT='testdr'
*.control_files='+DATA/test/controlfile/control01.ctl','+REDO/test/controlfile/control02.ctl'
*.audit_file_dest='/opt/app/oracle/admin/test/adump'
*.background_dump_dest='/opt/app/oracle/admin/test/bdump'
*.core_dump_dest='/opt/app/oracle/admin/test/cdump'
*.user_dump_dest='/opt/app/oracle/admin/test/udump'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create directories

mkdir -p /opt/app/oracle/admin/test/adump



Standby:

startup nomount

Primary :

rman target sys/password@test auxiliary sys/password@testDR


duplicate target database for standby from active database dorecover;

create spfile from pfile;

shut immediate;

startup;

select open_mode from v$database;

output should be read only

Now START MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;



Verify Dataguard configuration is syncing by using below queries


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)


************************************************************************************
Steps to activate standby:


STOP MRP
alter database recover managed standby database cancel;


Change Redolog entries in control files before activating standby


on STANDBY
ALTER DATABASE ACTIVATE STANDBY DATABASE;


If you get any errors regards to redolog.. Fix the issue



select * from v$logfile;

select 'alter database rename file '''||MEMBER||''' to ''+REDO'';' from v$logfile;


alter database rename file '/orabase/origlogA/test/redo01a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo01b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo02a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo02b.log' to '+REDO';
alter database rename file '/orabase/origlogA/test/redo03a.log' to '+REDO';
alter database rename file '/orabase/mirrlogA/test/redo03b.log' to '+REDO';
alter database rename file '/orabase/origlogB/test/redo04a.log' to '+REDO';
alter database rename file '/orabase/mirrlogB/test/redo04b.log' to '+REDO';



Then try to activate standby again

ALTER DATABASE ACTIVATE STANDBY DATABASE;



Use this below to stop archivelog transfer from primary to standby
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;