Oracle Dataguard Setup

Data Guard Setup 

Oracle dataguard is the high availability and Disaster recovery concept provided by Oracle.

For configuring the dataguard, we need two servers located in different locations let’s say Singapore and Hong Kong. Dataguard configuration maintains transactionally consistent copies of the production databases in the standby database. So, for some reason due to Disaster or Planned outage if production database is not available then standby database can be switched to the production role.

Around 5 years earlier, I had configured the first dataguard setup and the below steps are drafted for my reference.

Steps for Configuring the Oracle Dataguard

1)    Primary database should be in Archive log mode.

2)    Take RMAN Backup on the primary Database.

a)    $ RMAN target /

RMAN> backup as compressed backupset device type disk format '/optware/oracle/tmp/chtigu_%U' database;

b)    RMAN> backup device type disk format '/optware/oracle/tmp/chtigu_standby.ctl' current controlfile for standby;

         (or) at DB level

 ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/optware/oracle/tmp/chtigu_standby.ctl';

3)    Take pfile from primary server and amend it to use at standby server

create pfile='/optware/oracle/tmp/initchtisu.ora' from spfile;

vi initchtisu.ora

chtisu.__db_cache_size=314572800

chtisu.__java_pool_size=4194304

chtisu.__large_pool_size=12582912

chtisu.__oracle_base='/optware/oracle'#ORACLE_BASE set from environment

chtisu.__pga_aggregate_target=364904448

chtisu.__sga_target=683671552

chtisu.__shared_io_pool_size=0

chtisu.__shared_pool_size=339738624

chtisu.__streams_pool_size=0

*.audit_file_dest='/optware/oracle/admin/chtisu/audit'

*.audit_sys_operations=true

*.audit_trail='db'

*.compatible='11.2.0.3'

*.control_file_record_keep_time=100

*.control_files='/uchtigu/uoradata/chtigu/chtisu_control01.ctl'

*.db_block_size=8192

*.db_file_multiblock_read_count=16

*.db_name='chtigu'

*.db_unique_name='chtisu'

*.fal_client='chtisu'

*.fal_server='chtigu'

*.job_queue_processes=10

*.local_listener=listener_chtisu

*.log_archive_config='dg_config=(chtigu,chtisu)'

*.log_archive_dest_1='location=/utiguarch/uoradata/chtigu_arch valid_for=(all_logfiles,all_roles) db_unique_name=chtisu'

*.log_archive_dest_2='service=chtigu LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=chtigu'

*.log_archive_dest_state_2='DEFER'

*.log_archive_format='chtigu_%s_%t_%r.arc'

*.log_archive_max_processes=10

*.memory_target=1000M

*.nls_date_format='DD/MM/RRRR'

*.open_cursors=5000

*.os_authent_prefix='EXT$'

*.processes=500

*.recyclebin='off'

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=true

*.sql92_security=TRUE

*.standby_file_management='AUTO'

*.tape_asynch_io=false

*.undo_retention=7200

*.undo_tablespace='undotbs'

4)    If the DB files FS differs primary to standby, make sure to add below parameters

db_file_name_convert=’/DB FS location_primary’ , ‘/DB FS location_standby’

log_file_name_convert=’/ DB log FS location_primary’ , ‘/DB log FS location_standby’

5)    Create password file, set remote_login_passwordfile to Exclusive and enable force_logging

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwchtigu password=sys123 force=y entries=5 ignorecase=Y

$ sqlplus / as sysdba

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;

SQL> Alter system set force_logging=enable scope=spfile;

restart DB

SQL> select * from v$pwfile_users;

USERNAME          SYSDB   SYSOP   SYSAS

----------------------------------------- ----- ----- -----

SYS                          TRUE    TRUE     FALSE

6)    Set parameters in primary server for DG configuration

a)    alter system set log_archive_config='dg_config=(chtigu,chtisu)' scope=both ;

b)    alter system set log_archive_dest_1='location=/upacregarch/uoradata/cnpacu_arch valid_for=(all_logfiles,all_roles) db_unique_name=chtigu' ;

c)    alter system set log_archive_dest_2='service=chtisu LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=chtisu' scope=both;

d)    alter system set fal_server='chtisu' ;

e)    alter system set fal_client=’chtigu';

f)     alter system set standby_file_management='AUTO' scope=both;

g)    alter system set log_archive_max_processes=10 scope=both;

h)   alter system set log_archive_dest_state_2='defer';

7)    Create standby logfiles at primary side

alter database add standby logfile  thread 1 group 4 '/upacreg1/uoradata/chtigu/ chtigu _standby_log01.dbf' size 104857600;

alter database add standby logfile  thread 1 group 5 '/upacreg1/uoradata/chtigu/ chtigu _standby_log02.dbf' size 104857600;

alter database add standby logfile  thread 1 group 6 '/upacreg1/uoradata/chtigu/ chtigu _standby_log03.dbf' size 104857600;

alter database add standby logfile  thread 1 group 7 '/upacreg1/uoradata/chtigu/ chtigu _standby_log04.dbf' size 104857600;

alter database add standby logfile  thread 1 group 8 '/upacreg1/uoradata/chtigu/ chtigu _standby_log05.dbf' size 104857600;

alter database add standby logfile  thread 1 group 9 '/upacreg1/uoradata/chtigu/ chtigu _standby_log06.dbf' size 104857600;

8)    Copy password file, amended pfile, RMAN DB backup files, standby controlfile and tnsnames.ora file to Standby server

cp -ip orapwchtigu /optware/oracle/tmp/orapwchtisu

scp

9)    Login with sys user on primary and standby to cross-check connectivity.

Primary -- $ sqlplus sys@chtigu as sysdba

Standby -- $ sqlplus sys@chtisu as sysdba  ( this step we can check only when DB is mount)

10) On standby server

a)    Set DG sid

b)    Create Spfile from pfile

c)    $ rman target /

RMAN> startup nomount

RMAN> restore standby controlfile from '/optware/oracle/tmp/chtisu_standby.ctl';

RMAN> alter database mount;

RMAN> configure default device type to disk;

RMAN> restore database;

11) If the restore is from RMAN, connect to the standby database and drop standby logfiles.  If the restore is from file system cold backup it is not needed.

sqlplus /as sysdba

alter database drop standby logfile group 4;

alter database drop standby logfile group 5;

alter database drop standby logfile group 6;

alter database drop standby logfile group 7;

alter database drop standby logfile group 8;

alter database drop standby logfile group 9;

alter database add standby logfile  thread 1 group 4 '/upacreg1/uoradata/chtigu/ chtigu _standby_log01.dbf' size 104857600;

alter database add standby logfile  thread 1 group 5 '/upacreg1/uoradata/chtigu/ chtigu _standby_log02.dbf' size 104857600;

alter database add standby logfile  thread 1 group 6 '/upacreg1/uoradata/chtigu/ chtigu _standby_log03.dbf' size 104857600;

alter database add standby logfile  thread 1 group 7 '/upacreg1/uoradata/chtigu/ chtigu _standby_log04.dbf' size 104857600;

alter database add standby logfile  thread 1 group 8 '/upacreg1/uoradata/chtigu/ chtigu _standby_log05.dbf' size 104857600;

alter database add standby logfile  thread 1 group 9 '/upacreg1/uoradata/chtigu/ chtigu _standby_log06.dbf' size 104857600;

12) Put the standby database in managed recovery mode

SQL> recover managed standby database using current logfile disconnect;

13) On Primary database enable archive dest state2

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

SQL> alter system switch logfile;

14) Check standby side whether log shipping is happening or not

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

15) Incase of any error crosscheck from primary side dest status

SQL> select DEST_ID,ERROR,STATUS from v$archive_dest_status where dest_id in (1,2);

16) If  standby DB has to be on RAC, then register DB through SRVCTL

$ srvctl add database -d chtisu -o /optware/oracle/11.2/db -s mount -r physical_standby