10g standby database using dataguard

Primary and standby :

tnsnames.ora on both servers

testDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1521))
    (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/10.2.0/dbs/

scp  orapwtest oracle@10.0.1.2:/opt/app/oracle/product/10.2.0/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
mkdir -p /opt/app/oracle/admin/test/bdump
mkdir -p /opt/app/oracle/admin/test/cdump
mkdir -p /opt/app/oracle/admin/test/udump




Primary:

alter database create standby controlfile as'/export/home/oracle/test_stby.ctl' reuse;

scp  /export/home/oracle/test_stby.ctl oracle@10.0.1.2:/export/home/oracle/


Run incremental/ full backup

Once completed. Make sure the backup location is accessible by standby database.


Standby:

startup nomount


rman target=sys/password@test auxiliary=/

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK 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;


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;