Standby databases may be set up in two ways.
1. Physical
2. Logical
The characteristics and pros n cons can be discussed in parallel with setup details.
Physical Standby :- Its a physical copy of the primary database. Till 10g the physical standby is kept open in "mount" mode so of actually no use other then a disaster recovery. But 11g onwards the physical standby database can also be used for queries and reporting as the open mode for the standby database can be "read_only".
For creating standby using RMAN, below can be the steps.
( Primary database is ORCL and standby is STANDBY in below example which is the db_unique_name for them, while ORACLE_SID and the DB_NAME is same i.e.ORCL)
1. Create the oracle home and listener services for the dataguard. This may be on the different or on the same box.
2. Below is the set of parameters which has to be added to both Primary and the standby to make standby work as well to enable primary to work as standby in case of switchover.
a. On Primary:-
FAL_CLIENT='ORCL'
FAL_SERVER='STANDBY'
log_archive_config='DG_CONFIG=(ORCL,STANDBY)'
log_archive_dest_1=
'LOCATION=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORCL'
log_archive_dest_2=
'service=STANDBY LGWR ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=standby'
DB_FILE_NAME_CONVERT=('/tjain/oracle/app/oracle/oradata/orcl','/home/oracle/app/oracle/oradata/orcl')
LOG_FILE_NAME_CONVERT=('/tjain/oracle/app/oracle/oradata/orcl','/home/oracle/app/oracle/oradata/orcl')
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_max_processes=30
b. On standby:-
DB_FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/orcl','/tjain/oracle/app/oracle/oradata/orcl')
LOG_FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/orcl','/tjain/oracle/app/oracle/oradata/orcl')
fal_server=ORCL
fal_client=STANDBY
log_archive_config='DG_CONFIG=(ORCL,STANDBY)'
standby_file_management=auto
log_archive_dest_1=
'LOCATION=/tjain/oracle/app/oracle/flash_recovery_area/STANDBY/archivelog/
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STANDBY'
log_archive_dest_2=
'service=ORCL LGWR ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=orcl'
3. Take the full backup of primary database.
4. Start Standby DB in nomount mode.
SQL> startup nomount pfile=initorcl.ora
ORACLE instance started.
5. Connect to standby and primary database and catalog if exists, and run rman duplicate database to create standby as below.
[standby@localhost dbs]$ rman target sys/oracle@ORCL auxiliary / catalog rman/rman@ORCL
.
connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate ..
.
.
Finished Duplicate Db at ..
RMAN>
6. Connect to standby db with sqlplus and check the status.
SQL> select name , open_mode , database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBY
7. Start recovery on standby.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select name , open_mode , database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
------------------------------- -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBY
8. Switch log on primary and check if that gets shipped and applied to standby.
on primary -
SQL> Alter system switch logfile;
on standby -
SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 7 NO
1 6 NO
1 5 NO
1 4 NO
1 3 NO
1 2 NO
1 1 NO
Check alert log for Primary and standby to find out issues.
9. I found some issue with log arch destination due to listener in my alert log and after fixing I had to refresh that. So once the issue got fixed.
On Primary:-
SQL> select dest_name , status from v$archive_dest where dest_name like '%2';
DEST_NAME STATUS
---------------------- ---------
LOG_ARCHIVE_DEST_2 ERROR
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> select dest_name , status from v$archive_dest where dest_name like '%2';
DEST_NAME STATUS
---------------------- ---------
LOG_ARCHIVE_DEST_2 DISABLED
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select dest_name, status from v$archive_dest where dest_name like '%2';
DEST_NAME STATUS
------------------------- ---------
LOG_ARCHIVE_DEST_2 VALID
On standby:-
SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 7 NO
1 6 YES
1 5 YES
1 4 YES
.
We can even check the archvie logs gets shipped and is available to standby's archvie location.
11g onwards we can have the physical standby database in Readonly mode, so below are the steps to do that.
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
Cancel the recovery
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
By this time the recovery is not there in progress so the archvie may gets shipped but will not get applied.
SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 22 NO
1 21 NO
1 20 NO
1 19 YES
.
.
Start the recovery again
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select name , open_mode , database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------------- -------------------- ----------------
ORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 22 NO
1 21 IN-MEMORY
1 20 YES
1 19 YES
.
.
Now you can run query from any of the tables even from physical standby.
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
/tjain/oracle/app/oracle/oradata/orcl/users011.dbf
.
.
Like to thanks my colleague Puneeth for helping me out with this Standby Setup.
No comments:
Post a Comment