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.
