Monday, December 29, 2008

How to create a physical STANDBY database "Simple command" series

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:
  • 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.ora file while setting and create a spfile after finished installation:
create pfile from spfile;
Rename spfile to old make modifications to init.ora file and restart the instance after modified.

- Set Primary DB on archivelog mode

SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#,     ARCHIVE_CHANGE# from V$DATABASE;

SQL> ALTER SYSTEM ARCHIVE LOG START;
- Shutdown and BK

set LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT on init.ora


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;



SQL> select database_role from v$database;
- Modify initialization parameters

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
FAL_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
Prepare an Initialization Parameter File for the Standby Database
(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'

Add section to tnsnames.ora on Primary_DB

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_DB

Primary_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Primary_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Primary_DB)
    )
  )

Add section to listener.ora on the Standby_DB and reload

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
Command list:-Primary DB on archivelog mode (we'll use a hot backup copy)

-Enable Forced LoggingSQL> select name, FORCE_LOGGING from v$database;
SQL> ALTER DATABASE FORCE LOGGING;

-Create standby controlfile on primary DB:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/some_path_to_disk/controlStandby_DB1.ctl';

- Copy files from primary DB hot backup location to standby db location:
scp -C Primary_DB_server:/path_to_hotbackup/Primary_DB/*/path_to_files/Standby_DB/
(decompress files if necessary)

-Copy control files after generated:
scp -C Primary_DB_server:/some_path_to_disk/controlStandby_DB1.ctl /path_to_files/Standby_DB/
cd
/path_to_files/Standby_DB/
cp controlStandby_DB1.ctl controlStandby_DB2.ctl

Note: to avoid password prompt on scripts, we can generate a public ssh rsa key and copy it to primary DB server.
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
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

SQL> set AUTORECOVERY ONSQL> alter database recover managed standby database cancel;
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>
Recover standby database;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>
alter database recover managed standby database disconnect from session;
SQL> exit;

Test on primary DB if experience error with archives destination:

set lines 132
col Archive_dest for a40

SELECT dest_id "ID", status "DB_status", destination "Archive_dest", error "Error"FROM v$archive_dest;


Links, References:
http://download.oracle.com/docs/cd/B13789_01/server.101/b10823/standby.htm#52114
http://www.orafaq.com/node/957

1 comment:

Anonymous said...

Extremely rated post. I be taught something totally new on different blogs everyday. Deciding on one . stimulating to read the paper content from different writers and study a bit of something from their website. I’d like to use sure of this content material on my blog you’re mind. Natually I’ll give a link here we're at your internet-site. Admire your sharing.