Stetps to create physical standby database using RMAN backup

How to  create physical standby database using RMAN backup?

Primary Database (PRIME):

Checks:

•          Archive log mode must be enabled on primary database.

sql>Archive log list

 (or)

sql>SELECT LOG_MODE FROM V$DATABASE;

If the DB is in "NOARCHIVELOG" mode, please perform the following to make it ARCHIVELOG mode:

http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html

•          Primary database must be forced logging.

SELECT FORCE_LOGGING FROM V$DATABASE;

ALTER DATABASE FORCE LOGGING;

•          Make sure the DB Name and DB Unique Name are set.

SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      PRIME

SQL>SHOW PARAMETER DB_UNIQUE_NAME

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      PRIME

•          set the parameter Use LOG_ARCHIVE_CONFIG using DB_UNIQUE_NAME

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIME,STAND)' scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAND LGWR ASYNC max_failure=10 max_connections=5 reopen=180   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAND' scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

•          Set FAL_SERVER and FAL_CLIENT parameters

ALTER SYSTEM SET FAL_SERVER=STAND scope=both;

ALTER SYSTEM SET FAL_CLIENT=PRIME scope=both;

•          Set the below parameters

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oradata/log','/u04/oradata/logs' scope=spfile;

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/dbf','/u01/oradata/data' scope=spfile;

•          Add stand by redo log files

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/log/styredo_05a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/log/styredo_06a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/log/styredo_07a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/oradata/log/styredo_08a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u01/oradata/log/styredo_09a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

RMAN>backup current controlfile for standby;

Take full backup of database  and send the dump to standby site:

http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

Make sure the authentication is exclusive and password file is created.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys/admin123 entries=10 force=y

•          Prepare a TNSnames.ora file

PRIME=

      (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

    (HOST = bgl-db01.ttd.com)

        (PORT = 1521)))

    (CONNECT_DATA =

    (SID = PRIME)))

STAND=

      (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

      (HOST = bgl-dr01.dot.com)

        (PORT = 1521)))

      (CONNECT_DATA =

      (SID = PRIME)))

•          Start the listener listener_PRIME

lsnrctl start listener_PRIME

•          Create pfile:

Create pfile from spfile;

----------------------------------------------------------------------------------------------------------------------

Standby database (STAND):

scp the pfile and the password files to $ORACLE_HOME/dbs destination.

Copy the tnsnames.ora file from primary to standby in location to $ORACLE_HOME/network/admin

Modify the parameters in pfile such as diagnostic_dump,adump,controlfile path, log_archive_dest_1, log_archive_dest_2, fal_client, fal_server, db_unique_name.

e.g.

log_archive_config='DG_CONFIG=(STAND,PRIME)' scope=both

log_archive_dest_1=/u04/oradata/logs

db_unique_name=STAND

instance_name=PRIME

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_2='SERVICE=PRIME  LGWR ASYNC max_failure=10 max_connections=5 reopen=180   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=PRIME

*.fal_client='STAND'

*.fal_server='PRIME'

sql>startup nomount pfile=initSTAND.ora

sql > show parameter db_unique

RMAN> set dbid=110374; ? DBID of PRIME database

RMAN> restore standby controlfile from ‘/u01/rman_bkp/’;

RMAN> sql 'alter database mount standby database';

RMAN> crosscheck backup;

RMAN> delete noprompt expired backup;

RMAN> list backup summary;

Follow the below step to register the backup dump to our instance

rman> catalog start with '/u01/rman_bkp/';

RMAN> restore database;

RMAN> list backup of archivelog all;

RMAN> recover database until sequence 1135 à  sequence number till what we have backed up archivelog files

SQL> alter database recover managed standby database disconnect from session;

Database altered.