Purpose:
We'd want to have a transactionally consistent copy of an Oracle production database that would initially be created from a backup copy of the primary database. A Failover for our mission critical production databases. When production database crashes, applications can quickly switchover to the standby databases. Being part of "Simple command" series (old procedures collection) it wont touch new 11g features.
Versions: Oracle 9i, 10g
Description:
A physical standby database is physically identical to the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are identical. We can create, maintain and monitor the log transport and log apply services of a physical standby database by SQL/Shell Scripts (we can also use Data Guard or EM). When it is not performing recovery, a physical standby database can be opened in read-only mode.
A physical standby database provides the following benefits:
A physical standby database provides the following benefits:
- Disaster recovery and high availability
- Data protection
- Reduction in primary database workload (w/RMAN - off-load backups from the primary database)
- Performance (propagate changes among databases)
Note: A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time. Thus, the logical standby database can be used concurrently for data protection and reporting operations. A logical standby database has some restrictions on datatypes, types of tables, and types of DDL and DML operations and is not part of the scope of this article.
Pre-Procedure:- Create STBY DB folders structure (audit_file_dest, background_dump_dest, core_dump_dest, user_dump_dest, file directories, etc.)
- Create a password file on both primary DB, standby DB with the same sys password.
remote_login_passwordfile='EXCLUSIVE'
Note: I prefer to work over an init
create pfile from spfile;
Rename spfile to old make modifications to init
- Set Primary DB on archivelog mode
SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE;
- Shutdown and BK
set LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT on init
SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; SQL> archive log all; SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE;- Modify initialization parametersSQL> select database_role from v$database;
Setting Primary Database Initialization Parameters
Primary Role Initialization Parameters
DB_NAME='Primary_DB' DB_UNIQUE_NAME='Primary_DB' SERVICE_NAMES='Primary_DB' LOG_ARCHIVE_CONFIG='DG_CONFIG=(Primary_DB,Standby_DB)' CONTROL_FILES='/path_to_files/Primary_DB/control1.ctl', '/path_to_files/Primary_DB/control2.ctl' LOG_ARCHIVE_DEST_1= 'LOCATION=/path_to_arch/Primary_DB/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Primary_DB' LOG_ARCHIVE_DEST_2= 'SERVICE=Standby_DB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Standby_DB' LOG_ARCHIVE_DEST_STATE_1='ENABLE' LOG_ARCHIVE_DEST_STATE_2='ENABLE' REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=arc_Primary_DB_%t_%s_%r.arc
Note: These parameters control how log transport services transmit redo data to the standby system and the archiving of redo data on the local file system. Note that the example assumes the use of the ARCn processes (the default) to transmit redo data. If you specify the LGWR process to transmit redo data to both the local and remote destinations, also include the NET_TIMEOUT attribute on the LOG_ARCHIVE_DEST_2 initialization parameter.
Standby Role Initialization Parameters
Standby Role Initialization Parameters
Prepare an Initialization Parameter File for the Standby DatabaseFAL_SERVER='Primary_DB' FAL_CLIENT='Standby_DB' DB_FILE_NAME_CONVERT='/path_to_files/Primary_DB', '/path_to_files/Standby_DB' LOG_FILE_NAME_CONVERT='/path_to_files/Primary_DB/redo', 'path_to_files/Standby_DB/redo' STANDBY_ARCHIVE_DEST='/path_to_arch/Standby_DB/arch1/' STANDBY_FILE_MANAGEMENT=AUTO
(SQL> CREATE PFILE='/tmp/init.ora' FROM SPFILE;Modifying Initialization Parameters for a Physical Standby Database)
DB_NAME='Primary_DB' DB_UNIQUE_NAME='Standby_DB' SERVICE_NAMES='Standby_DB' LOG_ARCHIVE_CONFIG='DG_CONFIG=(Standby_DB,Primary_DB)' CONTROL_FILES= '/path_to_files/Standby_DB/controlStandby_DB1.ctl', '/path_to_files/Standby_DB/controlStandby_DB1.ctl' DB_FILE_NAME_CONVERT='/path_to_files/Primary_DB/', '/path_to_files/Standby_DB/' LOG_ARCHIVE_FORMAT=arc_Primary_DB_%t_%s_%r.arc LOG_ARCHIVE_DEST_1= 'LOCATION=/path_to_arch/Standby_DB/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Standby_DB' LOG_ARCHIVE_DEST_2= 'SERVICE=Primary_DB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Primary_DB' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE STANDBY_ARCHIVE_DEST='/path_to_arch/Standby_DB/arch1/' STANDBY_FILE_MANAGEMENT=AUTO INSTANCE_NAME='Standby_DB' FAL_SERVER='Primary_DB' FAL_CLIENT='Standby_DB'Command list:-Primary DB on archivelog mode (we'll use a hot backup copy)Primary_DB Add section to tnsnames.ora on Standby_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Standby_host)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Standby_DB) ) ) Add section to tnsnames.ora on Standby_DBPrimary_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Primary_host)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Primary_DB) ) ) Standby_DB Add section to listener.ora on the SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/product/10.2.0) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = Standby_DB) (ORACLE_HOME = /oracle/product/10.2.0) (SID_NAME = Standby_DB) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Standby_host)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF and reload
-Enable Forced LoggingSQL> select name, FORCE_LOGGING from v$database;
SQL> ALTER DATABASE FORCE LOGGING;
-Create standby controlfile on primary DB:
- Copy files from primary DB hot backup location to standby db location:
(decompress files if necessary)
-Copy control files after generated:
cd /path_to_files/Standby_DB/
cp controlStandby_DB1.ctl controlStandby_DB2.ctl
on the standby server:
cd /oracle/.ssh
ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /oracle/.ssh/id_rsa.
Your public key has been saved in /oracle/.ssh/id_rsa.pub.
The key fingerprint is:
5e:d5:97:f1:91:31:1b:e2:24:66:61:67:6f:a8:d2:61 oracle@standby
cat id_rsa.pub
and copy the entire content to the primary server /oracle/.ssh/authorized_keys2 file
Finally we must start and synchronize the standby DB:
sqlplus /nolog
conn / as sysdba
SELECT thread#,MAX( SEQUENCE# ) AS LAST_RECIVED_LOG
FROM SYS.v$archived_log GROUP BY thread#;
SELECT thread#,MAX( SEQUENCE# ) AS LAST_APPLIED_LOG ,APPLIED
FROM SYS.v$archived_log WHERE APPLIED LIKE'YES' GROUP BY thread#,APPLIED;
SQL>
FROM SYS.v$archived_log GROUP BY thread#;
FROM SYS.v$archived_log WHERE APPLIED LIKE'YES' GROUP BY thread#,APPLIED;
SQL>
Test on primary DB if experience error with archives destination:
col Archive_dest for a40
SELECT dest_id "ID", status "DB_status", destination "Archive_dest", error "Error"FROM v$archive_dest;
http://download.oracle.com/docs/cd/B13789_01/server.101/b10823/standby.htm#52114
http://www.orafaq.com/node/957
