How to recover the database when only backupset left

具体的操作在Oracle的Note（60545.1 ）里有详细的描述，我这里记录一下操作的过程和一些问题：

 首先做好数据库的备份


RMAN> backup database;

Starting backup at 2011-05-11:19:27:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/db/s01/oradata/system01.dbf
input datafile fno=00003 name=/u01/db/s01/oradata/sysaux01.dbf
input datafile fno=00002 name=/u01/db/s01/oradata/undotbs01.dbf
input datafile fno=00004 name=/u01/db/s01/oradata/tools1.dbf
input datafile fno=00005 name=/u01/db/s01/oradata/users1.dbf
input datafile fno=00006 name=/u01/db/s01/oradata/users2.dbf
input datafile fno=00007 name=/u01/db/s01/oradata/indx1.dbf
channel ORA_DISK_1: starting piece 1 at 2011-05-11:19:27:15
channel ORA_DISK_1: finished piece 1 at 2011-05-11:19:27:41
piece handle=/home/oracle/app/oracle/product/10.2.0/db_1/dbs/26mc374j_1_1 tag=TAG20110511T192714 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 2011-05-11:19:27:41

Starting Control File and SPFILE Autobackup at 2011-05-11:19:27:41
piece handle=/home/oracle/app/oracle/product/10.2.0/db_1/dbs/c-1265331578-20110511-27 comment=NONE
Finished Control File and SPFILE Autobackup at 2011-05-11:19:27:45

RMAN> quit

 模拟磁盘上所有的信息包括catalog库 control file  datafiles spfile都丢失的情况
2.1因为我的数据文件都在s01里所以直接删除
$mv /u01/db/s01  /u01/db/s01bak
2.2 删除pfile和spfile
[oracle@test s01]$ cd $ORACLE_HOME
[oracle@test db_1]$ ls
assistants   config  demo         install.platform  jlib  md       oc4j    oracore      perl     relnotes     sqlplus  xdk
bin          crs     diagnostics  inventory         jre   mesg     odbc    oraInst.loc  plsql    root.sh      srvm
cdata        css     has          javavm            ldap  mgw      olap    ord          precomp  root.sh.old  sysman
cfgtoollogs  ctx     hs           jdbc              lib   network  OPatch  oui          racg     slax         uix
clone        dbs     install      jdk               log   nls      opmn    owm          rdbms    sqlj         wwg
[oracle@test db_1]$ cd dbs
[oracle@test dbs]$ ls
26mc374j_1_1              hc_mfmn.dat  initdw.ora   inits01.ora  lkS01              orapwr01      snapcf_s01.f
c-1265331578-20110511-27  hc_r01.dat   init.ora     lkR01        lkTSPITR_S01_CTFC  orapws01      spfiles01.ora
hc_Ctfc.dat               hc_s01.dat   initr01.ora  lkS00        lkTSPITR_S01_MFMN  snapcf_r01.f
[oracle@test dbs]$ mkdir bak
[oracle@test dbs]$ ls -al *s01*
-rw-rw----  1 oracle dba    1544 May 10 08:24 hc_s01.dat
-rw-r--r--  1 oracle dba     509 May 10 09:00 inits01.ora
-rw-r-----  1 oracle dba    2048 May 10 09:03 orapws01
-rw-r-----  1 oracle dba 7520256 May 11 19:27 snapcf_s01.f
-rw-r-----  1 oracle dba    2560 May 11 19:12 spfiles01.ora
[oracle@test dbs]$ mv spfiles01.ora  bak
[oracle@test dbs]$ mv inits01.ora  bak

停 Catalog库 ，模拟catalog库丢失


开始恢复的过程：

1.手工生成一个pfile文件：
vi inits01

control_files=("/u01/db/s01/oradata/control01.ctl","/u01/db/s01/oradata/control02.ctl")
log_archive_dest_1='LOCATION=/u01/db/s01/arch'
core_dump_dest=/u01/db/s01/cdump
user_dump_dest=/u01/db/s01/udump
db_name="s01"
instance_name=s01
service_names=s01
processes=100
sga_target=209715200
log_archive_format=arch_%t_%s_%r.arc
log_archive_max_processes=1
db_block_size=8192
undo_management=AUTO
undo_tablespace=UNDOTBS1
java_pool_size=0

"inits01.ora" [New] 17L, 1457C written                                                                           
[oracle@test dbs]$ pwd
/home/oracle/app/oracle/product/10.2.0/db_1/dbs

5.创建pwd文件
orapwd file=./orapws01 password=oracle force=y

[oracle@test dbs]$ ls -al orapws01
-rw-r-----  1 oracle dba 2048 May 10 09:03 orapws01

准备确认好备份，因为我的备份就在磁盘上，这里和具体的情况可能有出入，如果用磁带的话需要参照 note 60545.1里的设置

[oracle@test dbs]$ ls -alrt
total 250068
-rw-r-----   1 oracle dba      8385 Sep 11  1998 init.ora
-rw-r-----   1 oracle dba     12920 May  3  2001 initdw.ora
-rw-rw----   1 oracle dba        24 May 10 07:50 lkS00
-rw-rw----   1 oracle dba      1544 May 10 08:24 hc_s01.dat
-rw-rw----   1 oracle dba        24 May 10 08:24 lkS01
-rw-r-----   1 oracle dba      2048 May 10 09:03 orapws01
-rw-rw----   1 oracle dba      1544 May 10 09:46 hc_r01.dat
-rw-rw----   1 oracle dba        24 May 10 09:46 lkR01
-rw-r--r--   1 oracle dba       396 May 10 10:22 initr01.ora
-rw-r-----   1 oracle dba      2048 May 10 10:23 orapwr01
drwxr-x---  54 oracle dba      4096 May 10 11:45 ..
-rw-r-----   1 oracle dba   7061504 May 11 08:03 snapcf_r01.f
-rw-rw----   1 oracle dba      1544 May 11 18:08 hc_mfmn.dat
-rw-rw----   1 oracle dba        24 May 11 18:09 lkTSPITR_S01_MFMN
-rw-rw----   1 oracle dba      1544 May 11 18:13 hc_Ctfc.dat
-rw-rw----   1 oracle dba        24 May 11 18:13 lkTSPITR_S01_CTFC
-rw-r-----   1 oracle dba 233431040 May 11 19:27 26mc374j_1_1
-rw-r-----   1 oracle dba   7520256 May 11 19:27 snapcf_s01.f
-rw-r-----   1 oracle dba   7602176 May 11 19:27 c-1265331578-20110511-27
drwxr-xr-x   2 oracle dba      4096 May 11 19:29 bak
-rw-r--r--   1 oracle dba      1457 May 11 19:29 inits01.ora
drwxr-x---   3 oracle dba      4096 May 11 19:30 .
[oracle@test dbs]$ exit

我的备份在 c-1265331578-20110511-27 和26mc374j_1_1 里


恢复控制文件：


SQL>                                                                                                                                 
SQL> DECLARE                                                                                                                         
  2  devtype varchar2(256);                                                                                                          
  3  done boolean;                                                                                                                   
  4  BEGIN                                                                                                                           
  5  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');                                                          
  6  sys.dbms_backup_restore.restoreSetDatafile;                                                                                     
  7  sys.dbms_backup_restore.restoreControlfileTo (cfname=>'/u01/db/s01/oradata/control02.ctl' );                       
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/c-1265331578-20110511-27', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;                                                                                       
END;                                                                                                                            
/ 10   11 

PL/SQL procedure successfully completed.

SQL> show parameters control

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
/u01/db/s01/oradata/control01.
ctl, /u01/db/s01/oradata/contr
ol02.ctl
SQL> host
[oracle@test dbs]$ cp /u01/db/s01/oradata/control02.ctl /u01/db/s01/oradata/control01.ctl
[oracle@test dbs]$ exit
exit

SQL> alter database mount;


检查控制文件里记录的datafile 和他们的位置：

SQL> set head off
SQL> /

         1 /u01/db/s01/oradata/system01.d
           bf

         2 /u01/db/s01/oradata/undotbs01.
           dbf

         3 /u01/db/s01/oradata/sysaux01.d
           bf

         4 /u01/db/s01/oradata/tools1.dbf
         5 /u01/db/s01/oradata/users1.dbf
         6 /u01/db/s01/oradata/users2.dbf
         7 /u01/db/s01/oradata/indx1.dbf

7 rows selected.


恢复数据文件：


SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
[oracle@test dbs]$ sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 11 19:32:52 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u01/db/s01/oradata/system01.dbf');
  8  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u01/db/s01/oradata/undotbs01.dbf');
  9  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u01/db/s01/oradata/sysaux01.dbf');
 10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u01/db/s01/oradata/tools1.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u01/db/s01/oradata/users1.dbf');
 12  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/u01/db/s01/oradata/users2.dbf');
 13  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/u01/db/s01/oradata/indx1.dbf');
 14  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/26mc374j_1_1',params=>null);
 15  sys.dbms_backup_restore.deviceDeallocate;
 16  END;
 17  /


PL/SQL procedure successfully completed.

SQL> SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

忘记备份archive log了，只能copy一份回来了，这个最好恢复一个回来，否则即使是level0的备份也不能保证scn是一致的。

我copy了2个回来，模拟不完全恢复的场景：

[oracle@test arch]$ ls
arch_1_10_750874397.arc  arch_1_9_750874397.arc


[oracle@test arch]$ sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 11 19:34:05 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

a
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

SQL>recover database using backup controlfile until cancel;
ORA-00279: change 229583 generated at 05/11/2011 19:27:16 needed for thread 1
ORA-00289: suggestion : /u01/db/s01/arch/arch_1_11_750874397.arc
ORA-00280: change 229583 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/db/s01/arch/arch_1_11_750874397.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/db/s01/arch/arch_1_11_750874397.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database open resetlogs;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/598601/viewspace-695156/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/598601/viewspace-695156/