| A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery. |
- Hardware & OS configurations
- Configuring Primary Database
- Oracle Net Configuration
- Configuring Standby Database
- Verifying the Physical Standby
|
| |
| 1. Hardware & OS configurations |
|
- Oracle Release 11g (11.2.0)
- Hostname: rhel5401.shannura.com (192.168.1.4)
- Operating System: RHEL 5.4 (x64)
- Database Name [DB_NAME]: 'dbuat01'
- Database domain [DB_DOMAIN]: 'shannura.com'
- Instance name [INSTANCE_NAME]: 'dbuat01'
- Database Unique Name [DB_UNIQUE_NAME]: 'dbuat01'
- TNS Name: 'dbuat01srv'
- File structure: /u02/oradata/dbuat01 |
|
- Oracle Release 11g (11.2.0)
- Hostname: rhel5402.shannura.com (192.168.1.5)
- Operating System: RHEL 5.4 (x64)
- Database Name [DB_NAME]: 'dbuat01'
- Database domain [DB_DOMAIN]: 'shannura.com'
- Instance name [INSTANCE_NAME]: 'dbtau01'
- Database Unique Name [DB_UNIQUE_NAME]: 'dbtau01'
- TNS Name: 'dbtau01srv'
- File structure: /u02/oradata/dbtau01 |
| |
| 2. Configuring Primary Database |
| |
| Let's first create the file structures: |
| |
# mkdir -p /u02/oradata/dbuat01/
# cd /u02/oradata/dbuat01/
# mkdir -p archived_logs redo data control |
| |
| Ensure the database is running in ARCHIVELOG mode. Because DG is all about archivelogs. Also verify if the database is enabled 'FORCE LOGGING' mode. |
| |
SQL> select log_mode, force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ -------------
ARCHIVELOG YES
|
| |
| Create a password file (e.g. orapwdbuat01). The same file could be copied, renamed (e.g. orapwdbtau01) and used for standby database as well. This ensures the SYS passwords remain identical in both databases. |
| |
| Create STANDBY REDO logs (optional, however good practice). When creating the standby redo logs, it is recommended to have one more standby redo log file group than the number of online redo log file groups on the primary database. In addition, the standby redo log file groups must be the same size as the online redo log file groups. |
| |
SQL> select group#, type, member from v$logfile order by group#, member
GROUP# TYPE MEMBER
---------- ------- ------------------------------------------------------------
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m1.log
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m2.log
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m1.log
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m2.log
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m1.log
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m2.log
4 STANDBY /u02/oradata/dbuat01/redo/redo_g4_m1.log
4 STANDBY /u02/oradata/dbuat01/redo/redo_g4_m2.log
5 STANDBY /u02/oradata/dbuat01/redo/redo_g5_m1.log
5 STANDBY /u02/oradata/dbuat01/redo/redo_g5_m2.log
6 STANDBY /u02/oradata/dbuat01/redo/redo_g6_m1.log
6 STANDBY /u02/oradata/dbuat01/redo/redo_g6_m2.log
7 STANDBY /u02/oradata/dbuat01/redo/redo_g7_m1.log
7 STANDBY /u02/oradata/dbuat01/redo/redo_g7_m2.log
14 rows selected. |
| |
| initdbuat01.ora |
| |
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
*.audit_file_dest='$ORACLE_BASE/admin/dbuat01/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u02/oradata/dbuat01/control/control01.ctl','/u02/oradata/dbuat01/control/control02.ctl','/u02/oradata/dbuat01/control/control03.ctl'
*.db_block_size=4096
*.db_domain='shannura.com'
*.db_name='dbuat01'
*.instance_name='dbuat01'
*.diagnostic_dest='$ORACLE_BASE/admin'
*.global_names=TRUE
*.log_archive_dest_1='location=/u02/oradata/dbuat01/archived_logs'
*.log_archive_format='dbuat01_%t_%s_%r.arc'
*.memory_max_target=1073741824
*.memory_target=1073741824
*.open_cursors=100
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='auto'
*.undo_tablespace='undotbs01'
*.log_archive_config='dg_config=(dbuat01,dbtau01)'
# --- primary role parameters ---- #
*.db_unique_name='dbuat01'
*.log_archive_dest_1='location=/u02/oradata/dbuat01/archived_logs valid_for=(all_logfiles,all_roles) db_unique_name=dbuat01'
*.log_archive_dest_2='service=dbtau01srv valid_for=(online_logfiles,primary_role) db_unique_name=dbtau01'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer' # this must be enabled later
*.service_names='dbuat01srv,dbuat01'
# --- standby role parameters ---- #
*.db_file_name_convert='/dbtau01/','/dbuat01/'
*.log_file_name_convert='/dbtau01/','/dbuat01/'
*.fal_server='dbuat01','dbtau01'
*.fal_client='dbuat01'
*.standby_file_management='auto' |
| |
| Restart the database to effect the parameters' changes. Then perform RMAN backup of the primary database. |
| |
# rman target sys/mypassword@dbuat01srv
RMAN> backup device type disk format '/u02/oradata/backup/dbuat01/rman/%U' database plus archivelog;
RMAN> backup device type disk format '/u02/oradata/backup/dbuat01/rman/%U' current controlfile for standby; |
| |
| Copy the RMAN backup files from primary host to the standby host. |
| |
# scp /u02/oradata/backup/dbuat01/rman/* rhel5402:/u02/oradata/backup/dbuat01/rman/ |
| |
| |
| 3. Oracle Net Configuration: |
|
SID_LIST_LISTENER_DBUAT01=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dbuat01.shannura.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=dbuat01)))
INBOUND_CONNECT_TIMEOUT_LISTENER_DBUAT01 = 0
LISTENER_DBUAT01=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=rhel5401.shannura.com)(PORT=1521))))
|
|
SID_LIST_LISTENER_DBTAU01=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dbtau01.shannura.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=dbtau01)))
INBOUND_CONNECT_TIMEOUT_LISTENER_DBTAU01 = 0
LISTENER_DBTAU01=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=rhel5402.shannura.com)(PORT=1521))))
|
|
dbuat01srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel5401.shannura.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbuat01.shannura.com)))
dbtau01srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel5402.shannura.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtau01.shannura.com)))
|
|
dbtau01srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel5402.shannura.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtau01.shannura.com)))
dbuat01srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel5401.shannura.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbuat01.shannura.com)))
|
| |
| |
| 4. Configuring Standby Database: |
| |
| Let's create the file structures as we did for the primary: |
| |
# mkdir -p /u02/oradata/dbtau01/
# cd /u02/oradata/dbtau01/
# mkdir -p archived_logs redo data control
|
| |
| initdbtau01.ora |
| |
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
*.audit_file_dest='$ORACLE_BASE/admin/dbtau01/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u02/oradata/dbtau01/control/control01.ctl','/u02/oradata/dbtau01/control/control02.ctl','/u02/oradata/dbtau01/control/control03.ctl'
*.db_block_size=4096
*.db_domain='shannura.com'
*.db_name='dbuat01'
*.instance_name='dbtau01'
*.diagnostic_dest='$ORACLE_BASE/admin'
*.global_names=TRUE
*.log_archive_dest_1='location=/u02/oradata/dbtau01/archived_logs'
*.log_archive_format='dbuat01_%t_%s_%r.arc'
*.memory_max_target=1073741824
*.memory_target=1073741824
*.open_cursors=100
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='auto'
*.undo_tablespace='undotbs01'
*.log_archive_config='dg_config=(dbuat01,dbtau01)'
# --- primary role parameters ---- #
*.db_unique_name='dbtau01'
*.log_archive_dest_1='location=/u02/oradata/dbtau01/archived_logs valid_for=(all_logfiles,all_roles) db_unique_name=dbtau01'
*.log_archive_dest_2='service=dbuat01srv valid_for=(online_logfiles,primary_role) db_unique_name=dbuat01'
*.log_archive_dest_state_2='enable'
*.service_names='dbtau01srv,dbtau01'
# --- standby role parameters ---- #
*.db_file_name_convert='/dbuat01/','/dbtau01/'
*.log_file_name_convert='/dbuat01/','/dbtau01/'
*.fal_server='dbuat01','dbtau01'
*.fal_client='dbtau01'
*.standby_file_management='auto'
|
| |
| Starting up the standby instance: |
| |
# export ORACLE_SID=dbtau01
# sqlplus / as sysdba
SQL> startup nomount |
| |
| Let's duplicate the primary database using RMAN: |
| |
# rman target sys/mypassword@dbuat01srv auxiliary sys/mypassword@dbtau01srv
RMAN> duplicate target database for standby; |
| |
| Start Redo Apply on the standby database |
| |
SQL> alter database recover managed standby database disconnect; |
| |
| |
| 5. Verifying the Physical Standby Database: |
| |
| In the primary database, modify the 'LOG_ARCHIVE_DEST_STATE_2' parameter. |
| |
SQL> alter system set log_archive_dest_state_2 = enable scope=both; |
| |
| Perform a couple of log switches in the primary database and then verify the transmissions of the archived redo log file was successful. VALID indicates successful. |
| |
SQL> select status, error from v$archive_dest where dest_id = 2;
STATUS ERROR
---------- -------------------------------
VALID |
| |
| Then, verify Redo Apply on the standby database: |
| |
# sqlplus sys/mypassword@dbtau01srv as sysdba
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#; |
| |
| Verifying the alertlog of the standby database |
| |
# tail -f alert_dbtau01.log |
| |
| [ Top ] |
| |
| Last modified: Jul 03, 2012 10:42 IST |
No comments:
Post a Comment