Use Rman for geo-recovery of database experiments (RAC recovery to a single-instance database)

Objective

Rman in the actual application of a few, many times a project began to set up a backup strategy, the follow-up is basically not used, unless the database is abnormal, it can be said that Rman is a less common but very important skills, but it is very easy to forget. In order not to drop the chain at the critical moment, an Rman geo-recovery database experiment was done.

Introduction to the test environment

	Operating system	Database version	Rac?
SOURCE System	CentOS 6.3 x64	11.2.0.4	2 nodes
Target system	RedHat 6.4 x64	11.2.0.4	Single node
Rman Backup: Level 0 Database fully prepared

Operation Steps

1. Copy the Rman backup file from the source library to the target library/dbbackup/full/20150815 directory to prepare for the job

2. Set Target library sid,pfile and so on; Pfile can be obtained by source system;

[Email protected] ~]$ export ORACLE_SID=TESTDB

3. Create SPFile from Pfile, launch instance to Nomount state

[Email protected] ~]$ sqlplus/as sysdbasql> create SPFile from pfile= '/home/oracle/pwss.ora '; Sql> startup Nomountoracle Routine has been started.

4. Connect Rman, restore Controlfile

rman> restore Controlfile from '/dbbackup/full/20150815/ctl_wssdb_d1qek9nr_1_120150815.bak '; start Restore on 2015-09-22 09:32:30 Use the target database control file instead of the channel for recovering directory allocations: Ora_disk_1 channel ora_disk_1:sid=133 device type =disk channel ora_disk_1: Restoring control File Channel Ora_ Disk_1: Restore Complete, spents: 00:00:04 output file name =/app/oradata/wssdb/control01.ctl output file name =/app/oradata/wssdb/control02.ctl complete restore to 2015-09-22 09:32:34rman> ALTER DATABASE mount;

5. Using Rman to recover data files

Because the source system uses ASM storage, but the target system is a single instance, using a local disk as storage, the data file directory is inconsistent, so we want to use the set newname for Datafiel clause to make the data file a new directory and file name, the recovery script is as follows:

Rman> run{allocate channel c1 type disk;allocate channel c2 type  disk;allocate channel c3 type disk;set newname for datafile 1  to  '/APP/ORADATA/WSSDB/SYSTEM01.BDF ';set newname for datafile 2 to  '/ APP/ORADATA/WSSDB/SYSAUX01.BDF ';set newname for datafile 3 to  '/app/oradata/ WSSDB/UNDOTBS1_01.BDF ';set newname for datafile 4 to  '/app/oradata/wssdb/ UNDOTBS2_01.BDF ';set newname for datafile 5 to  '/APP/ORADATA/WSSDB/USERS_01.BDF ' ;set newname for datafile 6 to  '/APP/ORADATA/WSSDB/WSS_01.BDF '; set newname  for datafile 7 to  '/APP/ORADATA/WSSDB/SMICWRPS_TABLESPACE_01.BDF '; set newname  for datafile 8 to  '/APP/ORADATA/WSSDB/XM_DB_PORTAL.BDF '; set newname for  datafile 9 to&nbsP; ' /APP/ORADATA/WSSDB/XM_DB_PUSH.BDF ';set newname for datafile 10 to  '/app/oradata/ WSSDB/XDBTS.BDF '; restore database;switch datafile all;release channel c1;release  CHANNEL C2;RELEASE CHANNEL C3;}

Channels allocated:  C1 channel  c1: SID=133  device type =disk assigned channel:  C2 channel  c2: SID=10  device type =disk assigned channel :  C3 channel  c3: SID=135  device type =disk executing command:  set newname executing command: set  newname executing command:  set newname executing command:  set newname executing command:  set newname executing command:  set  newname executing command:  set newname executing command:  set newname executing command:  set newname executing command:  set newname start  restore  on  2015-09-22 09:53:48 channel  c1:  starting restore data file backup set Channel  c1:  specifying a data file channel to restore from a backup set  c1:  convert data Files  00001  restore to  /app/oradata/wssdb/ SYSTEM01.BDF Channel  c1:  convert data file  00003  restore to  /APP/ORADATA/WSSDB/UNDOTBS1_01.BDF channel  C1:   Data file  00005  restore to  /APP/ORADATA/WSSDB/USERS_01.BDF channel  c1:  data file  00008  restore to  /APP/ORADATA/WSSDB/XM_DB_PORTAL.BDF channel  c1:  convert data Files  00010  restore to  /app/oradata/wssdb/ XDBTS.BDF Channel  c1:  reading backup fragment  /dbbackuP/full/20150815/wssdb_crqek9mr_1_120150815.bak Channel  c2:  Starting restore data file backup set channel  c2:  specifying the data file channel to restore from the backup set  c2:  data file  00002  restore to  /APP/ORADATA/WSSDB/SYSAUX01.BDF channel  c2:   00004 data file   revert to  /APP/ORADATA/WSSDB/UNDOTBS2_01.BDF channel  c2:  convert data file  00006  restore to  /app/oradata/ WSSDB/WSS_01.BDF channel  c2:  convert data Files  00007  restore to  /app/oradata/wssdb/smicwrps_tablespace_01. BDF Channel  c2:  data file  00009  restore to  /APP/ORADATA/WSSDB/XM_DB_PUSH.BDF channel  c2:  reading backup fragments  /dbbackup/full/20150815/wssdb_cqqek9mr_1_120150815.bak Channel  c1:  segment handle  = /dbbackup/full/ 20150815/wssdb_crqek9mr_1_120150815.bak  tagged  = tag20150815t001003 channel  c1:  restored backup fragment   1 channel  c1:  Restore complete,  time:  00:33:05 Channel  c2:  segment handle  = /dbbackup/full/20150815/wssdb_ cqqek9mr_1_120150815.bak  tagged  = tag20150815t001003 channel  c2:  restored backup fragment  1 channel  C2:   Restore Complete,  spents:   00:34:58 finish  restore   2015-09-22 10:28:47 data file  1  converted to data file copy input data file copy  recid=11  STAMP=891080928  file name =/APP/ORADATA/WSSDB/SYSTEM01.BDF data file  2  converted to data file copy input data file copy  recid= 12 stamp=891080928  file name =/APP/ORADATA/WSSDB/SYSAUX01.BDF data file  3  converted data file copy input data file copy  recid =13 stamp=891080928  file name =/APP/ORADATA/WSSDB/UNDOTBS1_01.BDF data file  4  converted to data file copy input data file copy   recid=14 stamp=891080928  file name =/APP/ORADATA/WSSDB/UNDOTBS2_01.BDF data file  5  converted to a data file copy of the input data file  RECID=15 STAMP=891080928  file name =/APP/ORADATA/WSSDB/USERS_01.BDF data file  6  Converted to data file copy input data file copy  RECID=16 STAMP=891080928  file name =/APP/ORADATA/WSSDB/WSS_01.BDF data file  7  Converted to data file copy input data file copy  RECID=17 STAMP=891080928  file name =/app/oradata/wssdb/smicwrps_tablespace_01. BDF data file  8  converted to data file copy input data file copy  RECID=18 STAMP=891080928  file name =/app/oradata/wssdb/xm_db_ PORTAL.BDF data file  9  converted to data file copy inputData file copy  RECID=19 STAMP=891080928  file name =/APP/ORADATA/WSSDB/XM_DB_PUSH.BDF data file  10  Converted to data file copy input data file copy  RECID=20 STAMP=891080928  file name =/APP/ORADATA/WSSDB/XDBTS.BDF released Channel:  c1 released Channel :  c2 released Channel:  C3

The recovery is complete, because the online log in the control file is still the original ASM path, we need to rebuild the control file, the steps are as follows:

To add a new online log:

sql> ALTER DATABASE Add logfile thread 1 Group 7 '/app/oradata/wssdb/onlinelog_01_128m_001.log ' size 128M; sql> ALTER DATABASE Add logfile thread 1 Group 8 '/app/oradata/wssdb/onlinelog_02_128m_001.log ' size 128M; sql> ALTER DATABASE Add logfile thread 1 Group 9 '/app/oradata/wssdb/onlinelog_03_128m_001.log ' size 128M;

Delete the original online log:

The sql> alter database drop logfile group 5; database has changed. sql> alter database drop logfile group 1;alter database drop  Logfile group 1*  1  line Error:ora-01624:  log  1  is an emergency recovery instance  wssdb  (thread   1)   Required ora-00312:  online logs  1  threads  1:  ' +data/wssdb/onlinelog/group_1.257.874510059 ' ora-00312:  Online logs  1  threads  1:  ' +data/wssdb/onlinelog/group_1.273.874523985 ' sql>  alter database drop logfile group 2;alter database drop logfile  group 2*  1  line Error:ora-01623:  log  2  is the current log of instance  wssdb  (thread  1)    -  cannot delete ora-00312:  online logs  2  threads  1:  ' +data/wssdb/onlinelog/group_2.258.874510061 ' ora-00312:  online logs  2  threads  1:  ' +data/wssdb/onlinelog/group_2.274.874523987 ' sql>  alter database drop logfile group 3;alter database drop logfile group 3*  1  line Error:ora-01624:  log  3  is an emergency recovery instance  wssdb  (thread  1)   required ora-00312:  online logs  3  threads  1:  ' + data/wssdb/onlinelog/group_3.259.874510065 ' ora-00312:  online logs  3  threads  1:  ' +data/wssdb/ onlinelog/group_3.275.874523989 ' sql> alter database backup controlfile to  Trace; The database has changed.

Found the deletion error, then we re-controlfile to delete these online logs, as follows:

The sql> shutdown immediateora-01109:  database is not open and the database has been unloaded. The oracle  routine has been closed. The sql> sql> sql> sql> startup nomountoracle  routine has been started. total system global area  334036992 bytesfixed size     2253024 bytesvariable size  113250080 bytesdatabase buffers  213909504  bytesRedo Buffers    4624384 bytesSQL> SQL> SQL>  sql> create controlfile reuse database  "Wssdb"   RESETLOGS   archivelog    maxlogfiles 192    maxlogmembers 3     MAXDATAFILES 8192    MAXINSTANCES 32     maxloghistory 292logfile  group 7  '/app/oradata/wssdb/onlinelog_01_128m_001.log '   size 128m blocksize 5  2    3    4    5    6     7    8  12,  GROUP 8  '/app/oradata/wssdb/onlinelog_02_ 128m_001.log '   SIZE 128M BLOCKSIZE 512,  GROUP 9  '/app/oradata/ Wssdb/onlinelog_03_128m_001.log '   SIZE 128M BLOCKSIZE 512-- STANDBY  logfiledatafile   '/app/oradata/wssdb/system01.bdf ',   '/app/oradata/wssdb/  9    10   11   12   13   14   SYSAUX01.BDF ',   '/app/oradata/wssdb/undotbs1_01.bdf ',   '/app/oradata/wssdb/undotbs2_ 01.BDF ',   '/app/oradata/wssdb/users_01.bdf ',   '/APP/ORADATA/WSSDB/WSS_01.BDF ',   '/APP/ORADATA/WSSDB/SMICWRPS_TABLESPACE_01.BDF ',   '/app/oradata/wssdb/xm_db_portal.bdf 15    16   17   18   19   20   ',   '/app/oradata/wssdb /XM_DB_PUSH.BDF ',   '/APP/ORADATA/WSSDB/XDBTS.BDF ' character set zhs16gbk; 21  The   22   23   24   control file has been created.

This time, I use the ALTER DATABASE mount; command to change the database to mount state, but found an error, this because after rebuilding the control file, the database will automatically boot to mount state.

Open the database by using ALTER DB open resetlogs:

sql> ALTER DATABASE open resetlogs;

ALTER DATABASE open Resetlogs

*

An error occurred on line 1th:

ORA-01152: File 2 has not been restored from an older backup

ORA-01110: Data file 2: '/APP/ORADATA/WSSDB/SYSAUX01.BDF '

sql> recover database until cancel;

Ora-00283:recovery session canceled due to errors

Ora-01610:recovery using the BACKUP controlfile option must is done

Still error, this time through the _allow_resetlogs_corruption=true parameter forced to open the database

Sql> alter system set "_allow_resetlogs_corruption" =true scope=spfile; sql> shutdown immediateORA-01109: Database is not open already unloaded database. The ORACLE routine has been closed. Sql> startup Mountoracle Routine has been started. Total System Global area 334036992 bytesfixed size 2253024 bytesvariable size 113250080 bytesdatabase buffers 213909 504 Bytesredo buffers 4624384 bytes database loaded.  sql> ALTER DATABASE Open Resetlogs;alter database open resetlogs* 1th line error: ORA-38856: Unable to unnamed_instance_2 instance (Redo thread 2) Mark as Enabled

Find a bug in this Oracle by looking up the data, the solution is as follows:

Sql> alter system set "_no_recovery_through_resetlogs" =true scope=spfile;

sql> shutdown immediateORA-01109: Database is not open already unloaded database. The ORACLE routine has been closed. Sql> sql> sql> Startup Mountoracle routine has been started. Total System Global area 334036992 bytesfixed size 2253024 bytesvariable size 113250080 bytesdatabase buffers 213909 504 Bytesredo buffers 4624384 bytes database loaded. sql> sql> ALTER DATABASE open resetlogs;

Database geo-recovery complete!

About

ORA-38856: Unable to mark instance unnamed_instance_2 (Redo thread 2) as Enabled

This error can be referenced by

http://dbaway.blog.51cto.com/7099215/1697030

This article is from the "Database Road" blog, make sure to keep this source http://dbaway.blog.51cto.com/7099215/1697049

Use Rman for geo-recovery of database experiments (RAC recovery to a single-instance database)