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
