把备份集中数据恢复到另一个数据库(数据库名不同)

怼烎@ 2022-01-13 08:43 467阅读 0赞

环境：

\[oracle@rac1 coldbackup\]$ uname -a  
Linux rac1 2.6.32-200.13.1.el5uek \#1 SMP Wed Jul 27 21:02:33 EDT 2011 x86\_64 x86\_64 x86\_64 GNU/Linux

\[oracle@rac1 coldbackup\]$ sqlplus /nolog

SQL\*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 16:30:42 2013

原实例及数据库名字是ecdata，新实例及数据库的名字是ecdata2

一、RMAN备份数据库：

RMAN> list backupset summary;

specification does not match any backup in the repository

RMAN> alter database open;

database opened

RMAN> backup database format '/test/hotbackup/hotbk\_\_%U';

二、生成控制文件重建脚本：

SQL> alter database backup controlfile to trace;

Database altered.

select c.value || '/' || d.instance\_name || '\_ora\_' ||  
to\_char(a.spid,'fm99999') || '.trc'  
from v$process a, v$session b, v$parameter c, v$instance d  
where a.addr = b.paddr  
and b.audsid = userenv('sessionid')  
6 and c.name = 'user\_dump\_dest';

C.VALUE||'/'||D.INSTANCE\_NAME||'\_ORA\_'||TO\_CHAR(A.SPID,'FM99999')||'.TRC'  
\--------------------------------------------------------------------------------  
/u01/app/oracle/diag/rdbms/ecdata/ecdata/trace/ecdata\_ora\_8438.trc

在上面文件中找到创建控制文件的脚本(要做适当修改)：

CREATE CONTROLFILE set DATABASE "ECDATA2" RESETLOGS ARCHIVELOG  
MAXLOGFILES 16  
MAXLOGMEMBERS 3  
MAXDATAFILES 100  
MAXINSTANCES 8  
MAXLOGHISTORY 292  
LOGFILE  
GROUP 1 '/test/ecdata2/redo01.log' SIZE 50M BLOCKSIZE 512,  
GROUP 2 '/test/ecdata2/redo02.log' SIZE 50M BLOCKSIZE 512,  
GROUP 3 '/test/ecdata2/redo03.log' SIZE 50M BLOCKSIZE 512  
\-- STANDBY LOGFILE  
DATAFILE  
'/test/ecdata2/system01.dbf',  
'/test/ecdata2/sysaux01.dbf',  
'/test/ecdata2/undotbs01.dbf',  
'/test/ecdata2/users01.dbf',  
'/test/ecdata2/ogg01.dbf',  
'/test/ecdata2/mysys01.dbf',  
'/test/ecdata2/mdu01.dbf',  
'/test/ecdata2/elite01.dbf',  
'/test/ecdata2/elitetm01.dbf'  
CHARACTER SET ZHS16GBK  
;

三、创建新实例的pfile文件initecdata2.ora，可以通过create pfile from spfile，也可以直接copy原来实例的pfile文件(其中粗体部分的路径要先建好)：

ecdata2.\_\_db\_cache\_size=159383552  
ecdata2.\_\_java\_pool\_size=25165824  
ecdata2.\_\_large\_pool\_size=4194304  
ecdata2.\_\_oracle\_base='/u01/app/oracle'\#ORACLE\_BASE set from environment  
ecdata2.\_\_pga\_aggregate\_target=255852544  
ecdata2.\_\_sga\_target=478150656  
ecdata2.\_\_shared\_io\_pool\_size=0  
ecdata2.\_\_shared\_pool\_size=281018368  
ecdata2.\_\_streams\_pool\_size=0  
\*.audit\_file\_dest=**'/u01/app/oracle/admin/ecdata2/adump'**  
\*.audit\_trail='db'  
\*.compatible='11.2.0.0.0'  
\*.control\_file\_record\_keep\_time=8  
\*.control\_files=**'/test/ecdata2/control01.ctl','/u01/app/oracle/flash\_recovery\_area/ecdata2/control02.ctl'**  
\*.db\_block\_size=8192  
\*.db\_domain=''  
\*.db\_name='ecdata2'  
\*.db\_recovery\_file\_dest='/u01/app/oracle/flash\_recovery\_area'  
\*.db\_recovery\_file\_dest\_size=4070572032  
\*.diagnostic\_dest='/u01/app/oracle'  
\*.dispatchers='(PROTOCOL=TCP) (SERVICE=ecdata2XDB)'  
\*.log\_archive\_format='%t\_%s\_%r.dbf'  
\*.memory\_target=734003200  
\*.O7\_DICTIONARY\_ACCESSIBILITY=FALSE  
ecdata2.open\_cursors=400  
\*.processes=150  
\*.remote\_login\_passwordfile='EXCLUSIVE'  
\*.sec\_case\_sensitive\_logon=TRUE  
\*.undo\_tablespace='UNDOTBS1'

四、startup nomount状态，加载initecdata2.ora,执行创建控制文件语句：

SQL> conn /as sysdba  
Connected to an idle instance.  
SQL> startup nomount;  
ORACLE instance started.

Total System Global Area 730714112 bytes  
Fixed Size 2216944 bytes  
Variable Size 566234128 bytes  
Database Buffers 159383552 bytes  
Redo Buffers 2879488 bytes

SQL> l  
1 CREATE CONTROLFILE set DATABASE "ECDATA2" RESETLOGS ARCHIVELOG  
2 MAXLOGFILES 16  
3 MAXLOGMEMBERS 3  
4 MAXDATAFILES 100  
5 MAXINSTANCES 8  
6 MAXLOGHISTORY 292  
7 LOGFILE  
8 GROUP 1 '/test/ecdata2/redo01.log' SIZE 50M BLOCKSIZE 512,  
9 GROUP 2 '/test/ecdata2/redo02.log' SIZE 50M BLOCKSIZE 512,  
10 GROUP 3 '/test/ecdata2/redo03.log' SIZE 50M BLOCKSIZE 512  
11 -- STANDBY LOGFILE  
12 DATAFILE  
13 '/test/ecdata2/system01.dbf',  
14 '/test/ecdata2/sysaux01.dbf',  
15 '/test/ecdata2/undotbs01.dbf',  
16 '/test/ecdata2/users01.dbf',  
17 '/test/ecdata2/ogg01.dbf',  
18 '/test/ecdata2/mysys01.dbf',  
19 '/test/ecdata2/mdu01.dbf',  
20 '/test/ecdata2/elite01.dbf',  
21 '/test/ecdata2/elitetm01.dbf'  
22 CHARACTER SET ZHS16GBK  
23\*  
SQL> /

Control file created.

SQL> alter database mount;  
alter database mount  
\*  
ERROR at line 1:  
ORA-01100: database already mounted ----很奇怪为什么创建完controlfile数据库就已经mount了。

五、注册之前的备份集到新建的control file中：

\[oracle@rac1 ~\]$ env|grep ORA  
ORACLE\_SID=ecdata2  
ORACLE\_BASE=/u01/app/oracle  
ORACLE\_HOME=/u01/app/oracle/product/11.2.0/db\_1  
\[oracle@rac1 ~\]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 10 17:16:53 2013

connected to target database: ECDATA2 (DBID=4161708524, not open)

RMAN> list backupset summary;

using target database control file instead of recovery catalog  
specification does not match any backup in the repository

RMAN> catalog backuppiece '/test/hotbackup/hotbk\_\_0inv313m\_1\_1';

cataloged backup piece  
backup piece handle=/test/hotbackup/hotbk\_\_0inv313m\_1\_1 RECID=1 STAMP=804359913

RMAN> catalog backuppiece '/test/hotbackup/hotbk\_\_0jnv3180\_1\_1';

cataloged backup piece  
backup piece handle=/test/hotbackup/hotbk\_\_0jnv3180\_1\_1 RECID=2 STAMP=804359942

RMAN> list backupset summary;

List of Backups  
===============  
Key TY LV S Device Type Completion Time \#Pieces \#Copies Compressed Tag  
\------- -- -- - ----------- --------------- ------- ------- ---------- ---  
1 B F A DISK 10-JAN-13 1 1 NO TAG20130110T163355  
2 B F A DISK 10-JAN-13 1 1 NO TAG20130110T163355

六、恢复数据库

RMAN> restore database;

Starting restore at 10-JAN-13  
allocated channel: ORA\_DISK\_1  
channel ORA\_DISK\_1: SID=19 device type=DISK

channel ORA\_DISK\_1: starting datafile backup set restore  
channel ORA\_DISK\_1: specifying datafile(s) to restore from backup set  
channel ORA\_DISK\_1: restoring datafile 00001 to /test/ecdata2/system01.dbf  
channel ORA\_DISK\_1: restoring datafile 00002 to /test/ecdata2/sysaux01.dbf  
channel ORA\_DISK\_1: restoring datafile 00003 to /test/ecdata2/undotbs01.dbf  
channel ORA\_DISK\_1: restoring datafile 00004 to /test/ecdata2/users01.dbf  
channel ORA\_DISK\_1: restoring datafile 00005 to /test/ecdata2/ogg01.dbf  
channel ORA\_DISK\_1: restoring datafile 00006 to /test/ecdata2/mysys01.dbf  
channel ORA\_DISK\_1: restoring datafile 00007 to /test/ecdata2/mdu01.dbf  
channel ORA\_DISK\_1: restoring datafile 00008 to /test/ecdata2/elite01.dbf  
channel ORA\_DISK\_1: restoring datafile 00009 to /test/ecdata2/elitetm01.dbf  
channel ORA\_DISK\_1: reading from backup piece /test/hotbackup/hotbk\_\_0inv313m\_1\_1  
channel ORA\_DISK\_1: piece handle=/test/hotbackup/hotbk\_\_0inv313m\_1\_1 tag=TAG20130110T163355  
channel ORA\_DISK\_1: restored backup piece 1  
channel ORA\_DISK\_1: restore complete, elapsed time: 00:03:08

RMAN> recover database;

Starting recover at 10-JAN-13  
using channel ORA\_DISK\_1

starting media recovery

unable to find archived log  
archived log thread=1 sequence=3  
RMAN-00571: ===========================================================  
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  
RMAN-00571: ===========================================================  
RMAN-03002: failure of recover command at 01/10/2013 17:23:33  
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 4360924

\-----由于我备份的时候没有备份归档日志文件的缘故

RMAN> alter database open resetlogs;

database opened

RMAN>

到这里实验已经完成，相当于对生产库在同一台server上做了一个副本。

转载于:https://blog.51cto.com/chinadm123/1111055