Physical Standby Database using RMAN Backup and
Restore
1)
Check init parameters
file:
Some important parameters that
need to check
For Primary Database:
*.db_name='ABCPROD'
*.DB_UNIQUE_NAME='FC_ABCPROD'
*.fal_client='FC_ABCPROD'
*.fal_server='DR_ABCPROD'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(FC_ABCPROD,DR_ABCPROD)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/ABCPROD/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=FC_ABCPROD'
*.LOG_ARCHIVE_DEST_2='SERVICE=DR_ABCPROD LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DR_ABCPROD'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='ABCPROD_%t_%s_%r.arc'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*******New Parameter************
*.DG_BROKER_START=true
alter system set DG_BROKER_START=true scope=both;
System altered.
For Standby Database:
*.db_name='ABCPROD'
*.DB_UNIQUE_NAME='DR_ABCPROD'
*.FAL_CLIENT='DR_ABCPROD'
*.FAL_SERVER='FC_ABCPROD'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(FC_ABCPROD,DR_ABCPROD)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/ABCPROD/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= DR_ABCPROD'
*.LOG_ARCHIVE_DEST_2='SERVICE=DR_ABCPROD LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FC_ABCPROD'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ABCPROD_%t_%s_%r.arc'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DG_BROKER_START=true
2)
Check
Directory Structure:
If Primary and Standby database
directory structure is same then no Action required.
Otherwise need to create same
directory structure on DR server as well.
3)
Check
Connectivity from each other:
Edit Tns Entry in
both the server to make sure they will communicate to each other:
As per Gilead setup for DR listener must be running through
listener.ora
4)
Check Rman
backup:
Check and validate
rman backup, it should be successful.
If till point 4
every thing is fine then we are good to create physical standby database through rman backup and restore.
5)
Create
password file and Start Standby database in nomount mode.
orapwd
file=$ORACLE_HOME/dbs/orapwABCPROD password=oracle entries=5
sqlplus “/as sysdba”
startup nomount
6)
Move backup
pieces to DR server.
Make a tarball of all backup pieces and zip that tar file. It will
be very small file and will fast transfer to DR location.
Either use scp or ftp to transfer.
Start restoration from below script.
[oracle@drabcdbval01
oradata]$ cat /home/oracle/RESTORE/stdby_restore.sh
DATE=`date
'+%Y%m%d'`
DATETIME=`date
'+%Y%m%d-%H%M'`
export ORAENV_ASK=NO
export ORACLE_SID=ABCPROD
export
PATH=/usr/local/bin:$PATH
#. /usr/local/bin/oraenv
export
PATH=$ORACLE_HOME/bin:$PATH
export
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"
rman
log="/home/oracle/RESTORE/stdby_restore.log" << EOF
connect target /
run {
restore controlfile from '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F
';
sql 'alter database mount';
set until time
"to_date('11-FEB-13 05:28:00','DD-MON-YY HH24:MI:SS')";
allocate channel t1 device
type disk format '/orabkup/ABCPROD/${ORACLE_SID}_dbf_%t_%s_%p';
restore database ;
release channel t1;
}
EOF
Exit;
7)
Above script
will open database in mount state, but no need
to open it. It may need some archlog so if that needed restore it from
tape but don’t start recovery.
[oracle@drabcdbval01
oradata]$ cat restoredbarch.sh
DATE=`date
'+%Y%m%d'`
DATETIME=`date
'+%Y%m%d-%H%M'`
export ORAENV_ASK=NO
export ORACLE_SID=ABCPROD
export
PATH=/usr/local/bin:$PATH
#. /usr/local/bin/oraenv
export
PATH=$ORACLE_HOME/bin:$PATH
export
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"
rman
log="/home/oracle/RESTORE/rman_restore_arch.log" << EOF
connect target /
run {
CATALOG START WITH '/orabkup/ABCPROD/BAKUP_19th';
RESTORE ARCHIVELOG FROM
SEQUENCE 434 UNTIL SEQUENCE 435';
}
8)
Create
standby Controlfile from production (Primary) database and scp same in standby
server /orabkup direcoty.
alter database create standby controlfile as ‘/orabkup/ABCPROD/abcprod_standby.ctl’;
scp /orabkup/ABCPROD/abcprod_standby.ctl drabcdbval01:/orabkup/ABCPROD/.
9)
On Standby
server shut down the database and move both controlfile with different name and
copy standby controlfile from /orabkup/ABCPROD
cd /oradata/ABCPROD
mv control01.ctl control01.ctl.old
mv control02.ctl control02.ctl.old
mv control03.ctl control03.ctl.old
cp /orabkup/ABCPROD/abcprod_standby.ctl .
cp abcprod_standby.ctl control01.ctl
cp abcprod_standby.ctl control02.ctl
cp abcprod_standby.ctl control03.ctl
10)
Now mount
database as a standby database.
startup nomount
alter database mount standby
database;
11)
Now start
standby recovery and start db as a physical standby.
alter database recover managed standby database
disconnect from session;
12)
Check mrp
process
[oracle@drabcdbval01 ABCPROD]$ ps -ef |grep mrp
oracle 7250
1 0 Feb12 ? 00:00:00 ora_mrp0_ABCPROD
oracle 14780 14471
0 12:24 pts/1 00:00:00 grep mrp
There is other way to create physical
standby from RMAN:
After Step #3
4) If it is adhoc and need to take backup then
use as per below:
From crontab : run below script.
############################################################################
# Full RMAN Database backup
00 5 * * *
/oradba/backup/rman_bkp_fulldb.sh ABCPROD >/dev/null 2>&1
Or below script:
#!/bin/ksh
DATE=`date '+%Y%m%d'`
DATETIME=`date
'+%Y%m%d-%H%M'`
export ORAENV_ASK=NO
export ORACLE_SID=ABCPROD
export
PATH=/usr/local/bin:$PATH
. /usr/local/bin/oraenv
export
PATH=$ORACLE_HOME/bin:$PATH
export
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"
rman
log="/orabkup/${ORACLE_SID}/rman_${ORACLE_SID}_$DATE.log" <<
EOF
connect TARGET /
run {
allocate channel d1 type
disk;
configure controlfile
autobackup on;
set controlfile autobackup
format for device type disk to '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F';
setlimit channel d1 kbytes
2097200 maxopenfiles 32 readrate 200;
backup
format '/orabkup/${ORACLE_SID}/${ORACLE_SID}_dbf_%t_%s_%p'
database;
sql 'alter system archive
log current';
crosscheck archivelog all;
backup
format
'/orabkup/${ORACLE_SID}/${ORACLE_SID}_arc_%t_%s_%p'
archivelog all
delete all input;
release channel d1;
}
exit;
EOF
Run full database RMAN backup manually with
controlfile for standby
Below is the script:
[oracle@drabcdbval01 oradata]$
cat backupstdby.sh
#!/bin/ksh
DATE=`date
'+%Y%m%d'`
DATETIME=`date
'+%Y%m%d-%H%M'`
export ORAENV_ASK=NO
export ORACLE_SID=ABCPROD
export PATH=/usr/local/bin:$PATH
. /usr/local/bin/oraenv
export
PATH=$ORACLE_HOME/bin:$PATH
export
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"
rman
log="/orabkup/${ORACLE_SID}/rman_${ORACLE_SID}_$DATE.log" <<
EOF
$ORACLE_HOME/bin/rman
log=backupstandby_${log_dt}.log << EOF
set echo on
connect target /
run {
set controlfile autobackup
format for device type disk to '/orabkup/${ORACLE_SID}/${ORACLE_SID}_cf_%F';
allocate channel d1 type
disk maxpiecesize=2000M;
allocate channel d2 type
disk maxpiecesize=2000M;
allocate channel d3 type
disk maxpiecesize=2000M;
setlimit channel d1 kbytes
2097200 maxopenfiles 32 readrate 200;
backup
format
'/orabkup/${ORACLE_SID}/${ORACLE_SID}_dbf_%t_%s_%p'
database include current
controlfile for standby;
sql 'alter system archive
log current';
crosscheck archivelog all;
backup
format
'/orabkup/${ORACLE_SID}/${ORACLE_SID}_arc_%t_%s_%p'
archivelog all
release channel d1;
release channel d2;
release channel d3;
}
EOF
exit;
5)
After its
successful completion we are good to restore it in standby server.
6)
Start
Standby database in nomount mode.
7)
Start
restoration, But it is not normal restore, we are creating duplicate database
for standby.
Use Below script:
[oracle@drabcdbval01
oradata]$ cat restoredbstb.sh
export
NLS_DATA_FORMAT='DD-MON-YY HH24:MI:SS'
log_dt=`date
'+%d%m'`
echo "Restore started
at `date` "
$ORACLE_HOME/bin/rman log=standby_restore_rman_${log_dt}.log
<<EOF
connect target sys/<password>@prim_<DB
Name>
connect AUXILIARY /
run {
allocate auxiliary channel d1
type disk;
allocate auxiliary channel d2
type disk;
allocate auxiliary channel d3
type disk;
allocate auxiliary channel d4
type disk;
set until time
"to_date('11-FEB-13 05:28:00','DD-MON-YY HH24:MI:SS')";
DUPLICATE TARGET DATABASE
FOR STANDBY dorecover nofilenamecheck;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
EOF
exit;
8)
Here we need
not to recover the database, all archive file will start transferring to its
standby location.
9)
Check the
database status, it should be in mount mode.
SQL> select name, open_mode
from v$database;
NAME OPEN_MODE
---------
--------------------
ABCPROD MOUNTED
10)
Skip
this point as per above method
11)
Skip
this point as per above method
12)
Put the
standby database in managed recovery mode.
alter database recover
managed standby database disconnect from session;
13)
Now check
MRP process:
[oracle@drabcdbval01 ABCPROD]$ ps -ef |grep mrp
oracle 7250
1 0 Feb12 ? 00:00:00 ora_mrp0_ABCPROD
oracle 14780 14471
0 12:24 pts/1 00:00:00 grep mrp
In Both Method:
14)
On Standby
Database, use below commands to check the DR status :
SQL> select
name,log_mode,database_role,open_mode from v$database;
NAME LOG_MODE DATABASE_ROLE OPEN_MODE
--------- ------------
---------------- --------------------
ABCPROD ARCHIVELOG
PHYSICAL STANDBY MOUNTED
SQL> select
PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from V$MANAGED_STANDBY;
PROCESS STATUS
CLIENT_P SEQUENCE#
--------- ------------
-------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
MRP0 WAIT_FOR_LOG N/A 647
RFS IDLE ARCH 0
RFS IDLE LGWR 647
RFS IDLE UNKNOWN 0
RFS IDLE UNKNOWN 0
9 rows selected.
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
646
SQL> select
TYPE,ARCHIVED_THREAD#,ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# from V$ARCHIVE_DEST_STATUS;
TYPE ARCHIVED_THREAD# ARCHIVED_SEQ#
APPLIED_THREAD# APPLIED_SEQ#
--------------
---------------- ------------- --------------- ------------
LOCAL 0 0 0 0
LOCAL 0 0 0 0
LOCAL 0 0 0 0
LOCAL 0 0 0 0
LOCAL 0 0 0 0
LOCAL 0 0 0 0
UNKNOWN 1 646 1 646
15)
On primary
Database:
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
647
SQL> select
name,log_mode,database_role,open_mode from v$database;
NAME LOG_MODE DATABASE_ROLE OPEN_MODE
--------- ------------
---------------- --------------------
ABCPROD ARCHIVELOG
PRIMARY READ WRITE
SQL> select
TYPE,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ# from V$ARCHIVE_DEST_STATUS;
TYPE ARCHIVED_THREAD# ARCHIVED_SEQ#
APPLIED_THREAD# APPLIED_SEQ#
--------------
---------------- ------------- --------------- ------------
LOCAL 1 647 0 0
PHYSICAL 1 647 1 646
LOCAL 1 647 0 0
LOCAL 1 647 0 0
LOCAL 1 647 0 0
LOCAL 1 647 0 0
NOTE:
Primary :
select max(sequence#) from v$archived_log;
Standby : select max(sequence#) from v$archived_log;
Both output must be same or at least 1 one less in Standby, if the Output
gap margin is large then it means primary and standby are not in sync with each
other.
