Data guardIntroduction:
Dataguard is configured to ensure the availability of database in case of disaster.
This document explains how to configure the physical standby database for switchover and fail over operations.
Prerequest:
• Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• The hardware and Operating system architecture on primary and standby location must be same.
• Each primary and standby database must have its own control file.
• If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
• Primary database must be FORCE LOGGING mode.
Preparing Primary Database for standby creation
To ensure that database in archive log mode, execute the following command.
SQL>archive log list
Enable database in Force logging mode
SQL>Alter database force logging;
Identify the location of the datafiles
SQL>select name from v$datafile;
Make a copy of the primary database
First step: Shutdown the primary database
SQL>shutdown immediate;
Second step: copy the datafiles manually to standby location
SQL>startup;
Create the standby control file by using the following command
SQL>alter database create standby controlfile as ‘D:\oracle\oradata\controlstby.ctl’
Create pfile from spfile. And save the same pfile to standby location;
Set the initialization parameters for the primary database and standby database as given below
Parameter file for Primary Database aq_tm_processes=1
background_dump_dest='D:\oracle\admin\Orclc\bdump'
compatible='9.2.0.0.0'
control_files='C:\oracle\oradata\Orclc\CONTROL01.CTL',
'C:\oracle\oradata\Orclc\CONTROL02.CTL','C:\oracle\oradata\Orclc\CONTROL03.CTL'
core_dump_dest='D:\oracle\admin\Orclc\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='Orclc'
dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
fast_start_mttr_target=300
hash_join_enabled=TRUE
instance_name='Orclc'
java_pool_size=33554432
job_queue_processes=10
large_pool_size=8388608
log_archive_dest_1='LOCATION=C:\oracle\ora92\database\archive MANDATORY'
log_archive_dest_2='SERVICE=stby'
log_archive_dest_state_2='ENABLE'
log_archive_start=true
open_cursors=300
pga_aggregate_target=25165824
processes=150
query_rewrite_enabled='FALSE'
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=50331648
sort_area_size=524288
star_transformation_enabled='FALSE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
user_dump_dest='D:\oracle\admin\Orclc\udump'Parameter file for Standby Databaseaq_tm_processes=1
background_dump_dest='D:\oracle\admin\stby\bdump'
compatible='9.2.0.0.0'
control_files='D:\oracle\oradata\stby\CONTROL_SB01.CTL'
core_dump_dest='D:\oracle\admin\stby\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='Orclc'
dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
fast_start_mttr_target=300
hash_join_enabled=TRUE
instance_name='stby'
java_pool_size=33554432
job_queue_processes=10
large_pool_size=8388608
log_archive_dest_1='LOCATION=D:\oracle\admin\stby\archive'
log_archive_dest_state_1=enable
log_archive_start=true
open_cursors=300
pga_aggregate_target=25165824
processes=150
query_rewrite_enabled='FALSE'
remote_login_passwordfile='NONE'
shared_pool_size=50331648
sort_area_size=524288
star_transformation_enabled='FALSE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
user_dump_dest='D:\oracle\admin\stby\udump'
standby_archive_dest='C:\oracle\ora92\database\archive\ORCLC'
db_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
log_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
standby_file_management=AUTO
remote_archive_enable=TRUE
lock_name_space=stby
Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Orcl)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = Orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = stby)
)
)
Restart the listeners using LSNRCTL utility.
% lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database.
stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
Start Physical standby database
Start up the stand by database using following commands
C:\>set oracle_sid=stby
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
12 - Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.
Issue following commands from primary database session:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
System altered.
13 - Initiate Log apply services
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
Now go to primary database prompt
SQL> alter system switch logfile;
Go to stand by database prompt
SQL> alter database open read only;
Database altered.
14 - Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
Archive the current log on the primary database using following statement.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
Now connect scott/tiger@orclc on primary database and create table or insert row in any table.
Now connect as sys on primary database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
And check whether the changes applied on the standby database or not.
