rman恢复rac到单实例数据库

妖狐艹你老母 2022-08-13 18:39 163阅读 0赞

**In this Document**

<table style="color:rgb(0,0,0); font-family:Simsun; font-size:12px; text-align:left"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=tx5js4je8_9&amp;_afrLoop=168444814225485#FIX" rel="nofollow">Solution</a></td> 
  </tr> 
 </tbody> 
</table>

<table style="color:rgb(0,0,0); font-family:Simsun; font-size:12px; text-align:left"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=tx5js4je8_9&amp;_afrLoop=168444814225485#REF" rel="nofollow">References</a></td> 
  </tr> 
 </tbody> 
</table>

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

## APPLIES TO: ##

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later  
Information in this document applies to any platform.  
\*\*\*Checked for relevance on 15-Apr-2014\*\*\*

## GOAL ##

\- You have a RAC database backed up by RMAN to disk location  
\- You need to restore this backup as Single Instance on another node

## SOLUTION ##

1) Take appropriate RMAN backup of the production RAC database. Note that you should turn on the CONTROLFILE AUTOBACKUP configuration so that we have the controlfile backed up after the database backup. When we restore the controlfile on new host from this autobackup piece, it will have the information of the latest backup.

> RMAN> run\{  
> 2> allocate channel c1 type disk format '/oracle/10g/backup/%U';  
> 3> backup database;  
> 4> backup archivelog all;  
> 5> \}  
>   
> allocated channel: c1  
> channel c1: sid=133 instance=racdb1 devtype=DISK  
>   
> Starting backup at 12-FEB-07  
> channel c1: starting full datafile backupset  
> channel c1: specifying datafile(s) in backupset  
> input datafile fno=00001 name=/ocfs2/oradata/racdb/system01.dbf  
> input datafile fno=00002 name=/ocfs2/oradata/racdb/undotbs01.dbf  
> input datafile fno=00005 name=/ocfs2/oradata/racdb/undotbs02.dbf  
> input datafile fno=00003 name=/ocfs2/oradata/racdb/sysaux01.dbf  
> input datafile fno=00004 name=/ocfs2/oradata/racdb/users01.dbf  
> channel c1: starting piece 1 at 12-FEB-07  
> channel c1: finished piece 1 at 12-FEB-07  
> piece handle=/oracle/10g/backup/09i9sruq\_1\_1 tag=TAG20070212T162458 comment=NONE  
> channel c1: backup set complete, elapsed time: 00:01:05  
> Finished backup at 12-FEB-07  
>   
> Starting backup at 12-FEB-07  
> current log archived  
> channel c1: starting archive log backupset  
> channel c1: specifying archive log(s) in backup set  
> input archive log thread=1 sequence=56 recid=3 stamp=613417105  
> input archive log thread=1 sequence=57 recid=4 stamp=613417106  
> input archive log thread=1 sequence=58 recid=5 stamp=614363168  
> input archive log thread=2 sequence=1 recid=1 stamp=613417090  
> input archive log thread=2 sequence=2 recid=2 stamp=613417093  
> input archive log thread=2 sequence=3 recid=6 stamp=614363170  
> channel c1: starting piece 1 at 12-FEB-07  
> channel c1: finished piece 1 at 12-FEB-07  
> piece handle=/oracle/10g/backup/0ai9ss14\_1\_1 tag=TAG20070212T162610 comment=NONE  
> channel c1: backup set complete, elapsed time: 00:00:04  
> Finished backup at 12-FEB-07  
>   
> Starting Control File and SPFILE Autobackup at 12-FEB-07  
> piece handle=/oracle/10g/backup/c-610677177-20070212-00 comment=NONE  
> Finished Control File and SPFILE Autobackup at 12-FEB-07  
> released channel: c1

2) Create a PFILE for the single instance database using the production RAC parameter file  
a) don't forget to modify the following parameters depending on the directory structure of the new host: audit\_file\_dest, background\_dump\_dest, control\_files, core\_dump\_dest, log\_archive\_dest\_1, user\_dump\_dest etc  
b) remove RAC specific parameters such as cluster\_database\_instances, cluster\_database etc  
c) for the parameter undo\_tablespace, mention any one undo tablespace name

> Known issue which may require an additional parameter to be set :  
>     [Note 334899.1 ][Note 334899.1]RMAN Duplicate from RAC backup fails ORA-38856

3) Move the backup pieces and the modified INIT.ORA file to the new host. Starting from 10g it is NO longer compulsory to copy the RMAN backup pieces to exactly the same location on the new host as the production location.  
  
4) Use the pfile created above to STARTUP NOMOUNT the database on the new host

> oracle@test-br ractest\]$ sqlplus "/ as sysdba"  
>   
> SQL\*Plus: Release 10.2.0.1.0 - Production on Fri Feb 16 03:14:23 2007  
>   
> Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
>   
> Connected to an idle instance.  
>   
> SQL> startup nomount;  
> ORACLE instance started.  
>   
> Total System Global Area  167772160 bytes  
> Fixed Size                  1218292 bytes  
> Variable Size              62916876 bytes  
> Database Buffers           96468992 bytes  
> Redo Buffers                7168000 bytes

5) Now invoke RMAN and restore the controlfile specifying the location where the controlfile autobackup piece is restored on this new server. You can mount the database once the controlfile is restored successfully.

> \[oracle@test-br ractest\]$ rman target / nocatalog  
>   
> Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 16 03:16:31 2007  
>   
> Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
>   
> connected to target database: racdb (not mounted)  
> using target database control file instead of recovery catalog  
>   
> RMAN> restore controlfile from '/u01/oracle/oradata/ractest/c-610677177-20070212-00';  
>   
> Starting restore at 16-FEB-07  
> allocated channel: ORA\_DISK\_1  
> channel ORA\_DISK\_1: sid=156 devtype=DISK  
>   
> channel ORA\_DISK\_1: restoring control file  
> channel ORA\_DISK\_1: restore complete, elapsed time: 00:00:02  
> output filename=/u01/oracle/oradata/ractest/control01.ctl  
> Finished restore at 16-FEB-07  
>   
> RMAN> alter database mount;  
>   
> database mounted  
> released channel: ORA\_DISK\_1

6) You can skip this step if you have restored the RMAN backup pieces to exactly the same location they were backed up on production. If this is not the case then you need to catalog the RMAN backup pieces to make RMAN aware of thier new location on the new host. Note that CATALOG BACKUPPIECE command is available only starting from 10g.

> RMAN> catalog backuppiece '/u01/oracle/oradata/ractest/09i9sruq\_1\_1';  
>   
> cataloged backuppiece  
> backup piece handle=/u01/oracle/oradata/ractest/09i9sruq\_1\_1 recid=10 stamp=614661579  
>   
> RMAN> catalog backuppiece '/u01/oracle/oradata/ractest/0ai9ss14\_1\_1';  
>   
> cataloged backuppiece  
> backup piece handle=/u01/oracle/oradata/ractest/0ai9ss14\_1\_1 recid=11 stamp=614661599

7) Now we'll determine the point upto which media recovery should run on the restored database.

> RMAN> list backup of archivelog all;  
>   
> List of Archived Logs in backup set 9  
> Thrd Seq     Low SCN    Low Time  Next SCN   Next Time  
> \---- ------- ---------- --------- ---------- ---------  
> 1    56      214541     01-FEB-07 226238     01-FEB-07  
> 1    57      226238     01-FEB-07 226240     01-FEB-07  
> 1    58      226240     01-FEB-07 233107     12-FEB-07  
> 2    1       186185     28-JAN-07 225714     01-FEB-07  
> 2    2       225714     01-FEB-07 226037     01-FEB-07  
> 2    3       226037     01-FEB-07 233110     12-FEB-07

Check the last archive sequence for all redo threads and select the archive sequence having LEAST "Next SCN" among them. In our case sequence 58 of thread 1 has Next SCN of 233107 while sequence 3 of thread 2 has Next SCN of 233110. Since squence 58 of thread 1 has least Next SCN we will recover upto this point. (If you are keen to have recovery run until some specific time you can always give SET UNTIL TIME)  
  
8) Having determined the point upto which media recovery should run, start the restore/recovery using:

> RMAN> run \{  
> 2> set until sequence 59 thread 1;  
> 3> set newname for datafile 1 to '/u01/oracle/oradata/ractest/data/system01.dbf';  
> 4> set newname for datafile 2 to '/u01/oracle/oradata/ractest/data/undotbs01.dbf';  
> 5> set newname for datafile 3 to '/u01/oracle/oradata/ractest/data/sysaux01.dbf';  
> 6> set newname for datafile 4 to '/u01/oracle/oradata/ractest/data/users01.dbf';  
> 7> set newname for datafile 5 to '/u01/oracle/oradata/ractest/data/undotbs02.dbf';  
> 8> restore database;  
> 9> switch datafile all;  
> 10> recover database;  
> 11> \}

In case you want to have the datafiles on a ASM diskgroup (+DATA for eg) for the clone/destination database, you may use SET NEWNAME as below:  
  
set newname for datafile 1 to '+DATA';  
set newname for datafile 2 to '+DATA';  
....  
  
Known issue in Oracle10g:  
    [Note 1146703.1][] Rman Restore From RAC ASM To Single Instance Non ASM Fails With ORA-00600 \[kgeade\_is\_0\]

**CAUTION !!!!:**  
  
If Online Redolog's (ORL's) are Oracle Managed Files (OM), and the Clone is being done on any of Source RAC Node (where ORLs are accessible from), renaming ORL will try to remove Source ORL files!   
  
Please review:  [http://docs.oracle.com/cd/E11882\_01/server.112/e25494/omf.htm\#ADMIN11517][http_docs.oracle.com_cd_E11882_01_server.112_e25494_omf.htm_ADMIN11517]  
  
The following statements are used to rename files:  
  
    ALTER DATABASE RENAME FILE  
  
    ALTER TABLESPACE ... RENAME DATAFILE  
  
These statements do not actually rename the files on the operating system, but rather, the names in the control file are changed. If the old file is an Oracle managed file and it exists, then it is deleted. You must specify each filename using the conventions for filenames on your operating system when you issue this statement."  
  
Instead of renaming, we can recreate the controlfile with desired name for ORLs:

SQL> alter database backup controlfile to trace as '/tmp/control.sql' resetlogs ;

Edit /tmp/control.sql and modify desired ORL names.

Since we determined previously that media recovery should run until sequence 58 hence we use  SET UNTIL SEQUENCE 59 (+1) above. You also need to use SET NEWNAME clause to restore datafiles to a location on the new host which is different from the production path. Finally, SWITCH DATAFILE ALL clause updates these new datafile locations in the controlfile.  
  
9) Once RMAN restore/recovery finishes, you will want to rename the online redolog files before opening the database in case the production path of redo log files is not available on the new host. After renaming the redolog files, the database can be opened with RESETLOGS

> SQL> select member from v$logfile;  
>   
> MEMBER  
> \--------------------------------------------------------------------------------  
> /ocfs2/oradata/racdb/redo01.log  
> /ocfs2/oradata/racdb/redo02.log  
> /ocfs2/oradata/racdb/redo03.log  
> /ocfs2/oradata/racdb/redo04.log  
> /ocfs2/oradata/racdb/redo05.log  
> /ocfs2/oradata/racdb/redo06.log  
>   
> 6 rows selected.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo01.log' to '/u01/oracle/oradata/ractest/log/redo01.log';  
>   
> Database altered.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo02.log' to '/u01/oracle/oradata/ractest/log/redo02.log';  
>   
> Database altered.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo03.log' to '/u01/oracle/oradata/ractest/log/redo03.log';  
>   
> Database altered.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo04.log' to '/u01/oracle/oradata/ractest/log/redo04.log';  
>   
> Database altered.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo05.log' to '/u01/oracle/oradata/ractest/log/redo05.log';  
>   
> Database altered.  
>   
> SQL> alter database rename file '/ocfs2/oradata/racdb/redo06.log' to '/u01/oracle/oradata/ractest/log/redo06.log';  
>   
> Database altered.  
>   
> SQL> alter database open resetlogs;  
>   
> Database altered.

In case you want to create the online redo logs on a ASM diskgroup (+DATA for eg) for the clone/destination database, you may use below commands:  
  
alter database rename file '/ocfs2/oradata/racdb/redo01.log' to '+DATA';  
alter database rename file '/ocfs2/oradata/racdb/redo02.log' to '+DATA';  
...

10) Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances

> SQL> select THREAD\#, STATUS, ENABLED  
>   2  from v$thread;  
>   
>    THREAD\# STATUS ENABLED  
> \---------- ------ --------  
>          1 OPEN   PUBLIC  
>          2 CLOSED PRIVATE  
>   
> SQL> select group\# from v$log where THREAD\#=2;  
>   
>     GROUP\#  
> \----------  
>          4  
>          5  
>          6  
>   
> SQL> alter database disable thread 2;  
>   
> Database altered.  
>   
> SQL> alter database drop logfile group 4;  
> alter database drop logfile group 4  
> \*  
> ERROR at line 1:  
> ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived  
> ORA-00312: online log 4 thread 2: '/u01/oracle/oradata/ractest/log/redo04.log'  
>   
> SQL> alter database clear unarchived logfile group 4;  
>   
> Database altered.  
>   
> SQL> alter database drop logfile group 4;  
>   
> Database altered.  
>   
> SQL> alter database drop logfile group 5;  
>   
> Database altered.  
>   
> SQL> alter database drop logfile group 6;  
>   
> Database altered.  
>   
> SQL> select THREAD\#, STATUS, ENABLED from v$thread;  
>   
>    THREAD\# STATUS ENABLED  
> \---------- ------ --------  
>          1 OPEN   PUBLIC

11) Now you can remove the undo tablespaces of other instances and create a new temporary tablespace to complete the activity.

> SQL> sho parameter undo;  
>   
> NAME                                 TYPE        VALUE  
> \------------------------------------ ----------- ------------------------------  
> undo\_management                      string      AUTO  
> undo\_retention                       integer     900  
> undo\_tablespace                      string      UNDOTBS1  
> SQL>  
> SQL>  
> SQL> select tablespace\_name from dba\_tablespaces where contents='UNDO';  
>   
> TABLESPACE\_NAME  
> \------------------------------  
> UNDOTBS1  
> UNDOTBS2  
>   
> SQL> drop tablespace UNDOTBS2 including contents and datafiles;  
>   
> Tablespace dropped.  
>   
> SQL> select name from v$tempfile;  
>   
> NAME  
> \--------------------------------------------------------------------------------  
> /ocfs2/oradata/racdb/temp01.dbf  
>   
>   
> SQL> select tablespace\_name from dba\_tablespaces where contents='TEMPORARY';  
>   
> TABLESPACE\_NAME  
> \------------------------------  
> TEMP  
>   
> SQL> create temporary tablespace TEMP1  
>   2  tempfile '/u01/oracle/oradata/ractest/data/temp01.dbf'  
>   3  size 50M;  
>   
> Tablespace created.  
>   
> SQL> alter database default temporary tablespace TEMP1;  
>   
> Database altered.  
>   
> SQL> drop tablespace TEMP including contents and datafiles;  
>   
> Tablespace dropped.

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

## REFERENCES ##

[NOTE:1146703.1][Note 1146703.1]  - Rman Restore From RAC ASM To Single Instance Non ASM Fails With ORA-00600 \[kgeade\_is\_0\]

[Note 334899.1]: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=415579.1&id=334899.1
[Note 1146703.1]: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=415579.1&id=1146703.1
[http_docs.oracle.com_cd_E11882_01_server.112_e25494_omf.htm_ADMIN11517]: http://docs.oracle.com/cd/E11882_01/server.112/e25494/omf.htm#ADMIN11517