Easy way of Data Guard / Standby Database 10g (on windows)

Data Guard - Oracle's Answer to Disaster Recovery

• See how to quickly implement a Data Guard physical standby database in a day.
• Learn how to switch over to your standby database in minutes.
• Possibly offload your batch reporting workload to your standby database.
• Replace your forebodings about crashes with "Don't worry ... be happy!"

Data Guard/Standby 10g (on windows)


Default (Maximum Performance) primary site= dba1 standby site= dba2

Pr Site:

• Archive Mode
  SQL>archive log list
  SQL> shutdown immediate
  SQL> startup mount
  SQL> alter database archivelog;
  SQL> alter database open;
  SQL> archive log list
  
• FORCE LOGGING must be enabled:SQL> select force_logging from v$database;SQL> alter database force logging;
  
• MAXLOGFILES >= (2 * Current Redo Log Groups) + 1:
  
  SQL> select records_used "Current Groups",records_total "Max Groups",
  decode(sign(records_total-(records_used*2+1)),-1,LPAD('YES',21),LPAD('NO',21))
  "Recreate MAXLOGFILES?"
  from v$controlfile_record_section where type = 'REDO LOG';
  
  If not available please increase MAXLOGFILES form Control file to trace
  
  
• Listener
  Create Listener
  
• Tnsnames
  Configure tnsnames both
  
• Edit sqlnet.ora (Optional)
  sqlnet.expire_time=2

• Create a password file if it doesn't exist.$orapwd file=pwdPRIM.ora password=xxx force=y

Note: sys passwd on Primayr db MUST be the same as the sys passwd on Standby db, otherwise no Log hipping is possible.

Main Task :

· Create pfile from spfile ( pr. database)
create pfile='d:\oracle\db1\oradata\standby_pfile.ora' from spfile;

· Create StandBy Control file from pr. database
· goto database Mount mode

· alter database create standby controlfile as 'd:\oracle\db1oradata\standby_controlfile.ctl';

· shutdown pr. Database


· Modify pfile for Dr. Database


background_dump_dest = /pgms/oradata/PPRD2/bdump # Location for Dr side
core_dump_dest = /pgms/oradata/PPRD2/cdump# Location for Dr side
user_dump_dest = /pgms/oradata/PPRD2/udump # Location for Dr side
audit_file_dest = /pgms/oradata/PPRD2/audit # Location for Dr side


CONTROL_FILES='G:\oracle\product\10.2.0\oradata\dba2\standby_controlfile.ctl'
#(Dr. database location)

DB_NAME=dba1 #(Pr databse SID)
DB_UNIQUE_NAME=dba2 #(Dr databse SID)
SERVICE_NAMES=dba2 #(Dr databse SID) # Connect String or SERVICE_NAMES

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dba1,dba2)'

LOG_ARCHIVE_DEST_1= 'LOCATION=G:\oracle\product\archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dba2

LOG_ARCHIVE_DEST_2= 'SERVICE=dba1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dba1'

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=dba1
FAL_CLIENT=dba2
DB_FILE_NAME_CONVERT=
'G:\oracle\product\10.2.0\oradata\dba1','G:\oracle\product\10.2.0\oradata\dba2',
'P:\oracle\product\10.2.0\oradata\dba1','G:\oracle\product\10.2.0\oradata\dba2'

LOG_FILE_NAME_CONVERT=
'G:\oracle\product\10.2.0\oradata\dba1','G:\oracle\product\10.2.0\oradata\dba2'
STANDBY_FILE_MANAGEMENT=AUTO

• cold backup (all data file without temp file)
  
• Copy to standby_controlfile.ctl, pfile, all cold datafile backup.
  
  Modify Pfile for PR Database:
  
  DB_NAME=dba1
  DB_UNIQUE_NAME=dba1
  SERVICE_NAMES=dba1
  LOG_ARCHIVE_CONFIG='DG_CONFIG=(dba1,dba2)'
  LOG_ARCHIVE_DEST_1= 'LOCATION=G:\oracle\product\archivelog
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=dba1'
  LOG_ARCHIVE_DEST_2= 'SERVICE=dba2
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=dba2'
  LOG_ARCHIVE_DEST_STATE_1=ENABLE
  LOG_ARCHIVE_DEST_STATE_2=ENABLE
  REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
  LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
  
  FAL_SERVER=dba2
  FAL_CLIENT=dba1
  DB_FILE_NAME_CONVERT=
  'G:\oracle\product\10.2.0\oradata\dba2','G:\oracle\product\10.2.0\oradata\dba1'
  LOG_FILE_NAME_CONVERT=
  'G:\oracle\product\10.2.0\oradata\dba2','G:\oracle\product\10.2.0\oradata\dba1'
  STANDBY_FILE_MANAGEMENT=AUTO
  
  · dr database startup
  SQL> startup nomount pfile=’location’

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

SQL> exit

· pr database
SQL> startup


** stop & start both pr. & dr. database listener

Finish your Standby database for Maximum performance mode

Startup standby Database to Recovery mode

1. startup nomount -----pfile='G:\oracle\pfile\init.ora.12'
2. alter database mount standby database;
3. alter database recover managed standby database disconnect from session;
4. alter database recover managed standby database cancel;

startup standby for checkiong of reporting purpose
5. alter database open read only;
6. shutdown immediate
7. (1-6)

verifing the standby
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#SQL> /
SQL> select sequence#, archived, applied from v$archived_log order by sequence#;/

Role switchover (from Primary to standby)

To Primary site===================

SQL> SELECT switchover_status FROM v$database;
SWITCHOVER_STATUS--------------------SESSIONS ACTIVE
SQL> SELECT username FROM v$session

WHERE status = 'ACTIVE' AND username IS NOT NULL AND username != 'SYS';
no rows selected
(SQL> alter database commit to switchover to physical standby;)

if SESSIONS ACTIVE remain then

SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomountORACLE instance started.
Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 155192196 bytes

Database Buffers 448790528 bytes

Redo Buffers 7135232 bytes

SQL> alter database mount standby database;
Database altered.
SQL> alter system set log_archive_dest_state_2 = defer;
System altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

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

SQL> /
SQL> select sequence#, archived, applied from v$archived_log order by sequence#;/

To standby site===========================
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS--------------------TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediateORA-01109: database not open
Database dismounted.ORACLE instance shut down.

SQL> startup open

ORACLE instance started.
Total System Global Area 612368384 bytesFixed Size 1250452 bytesVariable Size 167775084 bytesDatabase Buffers 440401920 bytesRedo Buffers 2940928 bytesDatabase mounted.

Database opened.

SQL> alter system set log_archive_dest_state_2 =enable;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter tablespace temp add tempfile 'G:\oracle\product\10.2.0\oradata\dba2\temp1.dbf' size 50m;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>

Summary

• Data Guard provides an automated standby database which can essentially eliminate downtime of your production data.
  
• Setup is easy and fairly straightforward.
  
• Maintenance is minimal.
  
• Switchovers and failovers can be done within a few minutes.
  
• Reporting can be offloaded to the standby to ease the workload on the primary.
  
• And … It's Free! (Included with Enterprise Edition)