Create Standby Database From RMAN Backup

Create a RAC Standby Database from Overnight RMAN Tape Backups with 20 steps.

This post demonstrates how to build a RAC standby database by using overnight  RMAN ( Netbackup ) backups in 11.2.

Subscribe to get access

Read more of this content when you subscribe today.

How to Relocate Standby Database ASM Datafiles

The alternative for relocating ASM datafiles by RMAN commands :
CATALOG START WITH “+DATA2/../”
SWITCH DATABASE TO COPY.

TASK

This task is to relocate all the datafiles of diskgroup DATA1 to another diskgroup DATA2 for a standby database.

SOULUTION

1)Check the diskgroup space availability.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024 
       from v$asm_diskgroup;

NAME T    TOTAL_MB/1024 FREE_MB/1024 USABLE_FILE_MB/1024
--------- ------------- ------------ -------------------
DATA1     1024.00977      15.6894531   15.6894531
OCR_VOTE    19.9931641    15.4257813   15.4257813
FRA        500.069336    210.331055   210.331055
DATA2     4000.55469    1816.02344   1816.02344

2)Leave managed standby database recovery still going. Create a RMAN script by using below query:

SQL> select 'backup as copy datafile '||file#||' format ''+DATA2'';' 
       from v$datafile 
      where name like '+DATA1%' order by 1;

'BACKUPASCOPYDATAFILE'||FILE#||'FORMAT''+DATA2'';'
---------------------------------------------------
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
.....
....
...
..
.

The final RMAN script content:

$cat move_DATA1_to_DATA2.rman

run {
allocate channel stdbych1 type disk;
allocate channel stdbych2 type disk;
allocate channel stdbych3 type disk;
allocate channel stdbych4 type disk;
backup as copy datafile 124 format '+DATA2';
backup as copy datafile 125 format '+DATA2';
backup as copy datafile 126 format '+DATA2';
backup as copy datafile 127 format '+DATA2';
....
...
..
.
}

3) Run the RMAN script to make a copy of all datafiles on diskgroup DATA1 onto diskgroup DATA2.

$nohup rman target / cmdfile=/u01/app/oracle/admin/scripts/move_DATA1_to_DATA2.rman  log=/u01/app/oracle/admin/scripts/move_DATA1_To_DATA2.log &

4) Stop the managed standby database recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

5) Switch the datafiles.

RMAN>switch datafile 124 to copy;
RMAN>switch datafile 125 to copy;
RMAN>switch datafile 126 to copy;
RMAN>switch datafile 127 to copy;
....
...
..
.

6) Start the recovery.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

5) Archive log switch, check applied from alert.log, etc.

SQL>alter system archive log current ;
System altered.

6) Unmount diskgroup DATA1 on all the nodes, except the current node.

SQL> alter diskgroup data1 dismount;
Diskgroup altered.

7) Drop the diskgroup DATA1.

SQL> drop diskgroup data1;
drop diskgroup data1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "DATA1" contains existing files

SQL> drop diskgroup data1 including contents;
Diskgroup dropped.

8) Scan disks and confirm the ASM Disks of DATA1 have been removed from ASM disks.

$su - 

# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
ASM_SSD1  <----- DATA1 diskgroup
OCR_VOTE01

# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Cleaning disk "ASM_SSD1"
Scanning system for ASM disks...

# oracleasm listdisks
ASM_DISK01
ASM_DISK02
ASM_DISK03
ASM_DISK04
ASM_DISK05
ASM_DISK06
ASM_DISK07
ASM_DISK08
ASM_FRA01
OCR_VOTE01

ORA-19909: datafile 1 belongs to an orphan incarnation

One database might have multiple incarnations.

The following errors occurred in a standby database alert log :

MRP0: Detected orphaned datafiles!
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/PROD/datafile/system.238.875096219

Check standby database incarnation :

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID    STATUS Reset SCN    Reset Time
------ ------- ------ ---------- ----- ------------ ----------------
1      1       PROD   3779924890 PARENT 1           08-01-2015 10:00
2      2       PROD   3779924890 CURRENT 2620491565 01-02-2016 12:01

Reset database to old incarnation :

RMAN> reset database to incarnation 1; 

database reset to incarnation 1 


RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name DB ID      STATUS  Reset      SCN Reset Time
------- ------- ------- ---------- ------- ---------- ---------------
1       1       PROD    3779924890 CURRENT 1          08-01-2015 10:00
2       2       PROD    3779924890 ORPHAN  2620491565 01-02-2016 12:01

Then everything is working fine.

ORA-12514 from DGMGRL Switchover Database

For DGMGRL to switchover database, add an entry into LISTENER.ora with static registration with listeners for each host where database resides on respectively.

ISSUES

When using DGMGRL command line to switchover database, we see ORA-12514 error.  The following are two databases for primary and standby database :

Sydney – Primary database
Melbourne – Physical standby database

DGMGRL> switchover to 'Melbourne';
Performing switchover NOW, please wait...
Operation requires a connection to instance "Melbourne" on database "Melbourne"
Connecting to instance "Melbourne"
connected
New primary database "Melbourne" is opening ...
Operation requires startup of instance "Sydney" on database "Sydney"
Starting instance "Sydney"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: you are no longer connected to ORACLE

Please complete the following steps to finish switchover:
start up and mount instance "Sydney" of database "Sydney"

SOLUTION

we need have static registration with listeners, where a standby database/Data Guard scenario exists. Basically once dmon terminates the instance it was unable to connect back to it again to restart the new standby database without a static entry.

Add an entry into LISTENER.ora for each host where database resides on respectively.

For host where Sydney database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Sydney_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Sydney)
)

For host where Melbourne database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Melbourne_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Melbourne)
)

Check property “StaticConnectIdentifier” to see static registration successfully or not.

DGMGRL> show database "Sydney" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosta)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Sydney_DGMGRL)(INSTANCE_NAME=Sydney)(SERVER=DEDICATED)))'
DGMGRL> show database "Melbourne" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Melbourne_DGMGRL)(INSTANCE_NAME=Melbourne)(SERVER=DEDICATED)))'

The dataguard broker keeps killing process RSM

Dataguard broker keeps killing RSM process overnight and oncall DBA is pagered by alerts.

DG 2014-10-04-04:17:17 0 2 0 DMON: killing process RSM0, pid = 26742
DG 2014-10-04-04:17:18 0 2 0 DMON: waiting for subscribers to disappear...
DG 2014-10-04-04:17:18 0 2 0 PMON: delete state object for RSM0
DG 2014-10-04-04:17:18 0 2 0 PMON: RSM0 died unexpectedly while processing request 1.1.833218860,
DG 2014-10-04-04:17:18 0 2 0 notifying DMON of RSM failure
DG 2014-10-04-04:17:18 0 2 0 DMON: Creating process RSM0
DG 2014-10-04-04:17:21 0 2 0 RSM0: Attach state object
DG 2014-10-04-04:17:21 0 2 0 DMON: Process RSM0 re-created with pid = 22898

WORKAROUND

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '30'

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=120;
Property "operationtimeout" updated

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '120'
DGMGRL>

Reference Oracle Doc:
Data Guard: Server Hang And Crash Because RSM0 Keeps Re-spawning (Doc ID 1322877.1)
Cause : The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low.