Oracle Rman の dbms_backup_restore

最新推荐文章于 2021-04-12 07:46:36 发布

原创最新推荐文章于 2021-04-12 07:46:36 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #2010 #数据库 #thread #sql #null

Oracle Rman 专栏收录该内容

2 篇文章

订阅专栏

本文通过实战演示了在Oracle数据库中使用RMAN进行备份及在控制文件丢失情况下的恢复流程。详细介绍了如何利用dbms_backup_restore包恢复数据文件，并重建控制文件以实现数据库的正常启动。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前看文章，说建议数据库用rman备份的话，最好采用catalog数据库，将备份信息保留在catalog库中。对于没有catalog的数据库rman备份，备份信息将保留在控制文件中。

因此如果控制文件丢失，还原起来就比较麻烦，需要用dbms_backup_restore来进行恢复（恢复方式见文章的附录），其实想想，dbms_backup_restore其实也没那么常用了。因为：

1、控制文件如果有自动备份，那么可以从自动备份还原。

2、如果有单独备份控制文件，那么可以从这个单独备份的backupset处还原。

3、如果之前有做数据库全备，全备中就包含了控制文件和spfile的备份，可以从全备中还原。

4、另外还可以从控制文件的snapshot还原。

5、从上面的四种方法都无法还原控制文件，那么只能从备份集restore数据文件以及arch，然后重建控制文件，再resetlogs打开数据库。

====================================
附录（在控制文件都无法恢复的情况，用dbms_backup_restore restore数据文件，再重建控制文件，在open resetlogs）：

SQL> l
  1* select count(*) from t1
SQL> /

  COUNT(*)
----------
   1024

SQL>
SQL>
SQL>
SQL> insert into t1 select * from t1;

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> select count(*) from t1;

  COUNT(*)
----------
   8192

SQL>
SQL>--commit前数据为1024行，insert到8192行，但是未commit
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:/Documents and Settings/Administrator>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 9 16:53:31 2010

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

connected to target database: ORA10G (DBID=4045875789)

RMAN> run {
2> allocate channel c1 type disk;
3> backup
4>   incremental level=0
5>  filesperset 5
6>   format 'df_%s_%p_%t'
7>   (database);
8>  sql 'alter system archive log current';
9> release channel c1;
10> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=146 devtype=DISK

Starting backup at 09-SEP-10
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF
input datafile fno=00002 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF
input datafile fno=00003 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF
input datafile fno=00004 name=D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/DF_19_1_729276821 tag=TAG20100909T165341 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:50
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/DF_20_1_729276871 tag=TAG20100909T165341 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-SEP-10

sql statement: alter system archive log current

released channel: c1

RMAN>

RMAN> run {
2>   allocate channel c1 type disk;
3>   backup
4> filesperset 20
5> format 'arch_%s_%p_%t'
6> (archivelog all delete input);
7> }

allocated channel: c1
channel c1: sid=146 devtype=DISK

Starting backup at 09-SEP-10
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=71 recid=42 stamp=729276793
input archive log thread=1 sequence=72 recid=43 stamp=729276794
input archive log thread=1 sequence=73 recid=44 stamp=729276795
input archive log thread=1 sequence=74 recid=45 stamp=729276878
input archive log thread=1 sequence=75 recid=46 stamp=729276880
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/ARCH_21_1_729276880 tag=TAG20100909T165440 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archive log(s)
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_71_68K83SGM_.ARC recid=42 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_72_68K83TF4_.ARC recid=43 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_73_68K83VG3_.ARC recid=44 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_74_68K86GD3_.ARC recid=45 stamp=7292
archive log filename=D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_1_75_68K86HW7_.ARC recid=46 stamp=7292
Finished backup at 09-SEP-10
released channel: c1

RMAN>

RMAN> run {
2>   allocate channel c1 type disk;
3>   backup
4> filesperset 20
5> format 'ctrl_%s_%p_%t'
6> current controlfile;
7> }

allocated channel: c1
channel c1: sid=146 devtype=DISK

Starting backup at 09-SEP-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 09-SEP-10
channel c1: finished piece 1 at 09-SEP-10
piece handle=D:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/CTRL_22_1_729276884 tag=TAG20100909T165444 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 09-SEP-10
released channel: c1

RMAN>

RMAN>--备份全库，arch文件，控制文件。其实后面我们可以从全库备份或者控制文件备份还原的，但是这里我们不演示用这种方式还原，我们用dbms_backup_restore包来做还原

RMAN> exit

Recovery Manager complete.

C:/Documents and Settings/Administrator>exit

SQL>

SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>
C:/Documents and Settings/Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 9 17:20:29 2010

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

Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size   1248116 bytes
Variable Size   79692940 bytes
Database Buffers   121634816 bytes
Redo Buffers   7139328 bytes
SQL>
SQL>--开始用dbms_backup_restore包来restore数据文件：
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=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF');
  8  sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF');
  9  sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF');
10  sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF');
11  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/DF_19_1_729276821',params=>null);
12  sys.dbms_backup_restore.devicedeallocate;
13  end;
14 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>--重建控制文件：
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG
  2   MAXLOGFILES 16
  3   MAXLOGMEMBERS 3
  4   MAXDATAFILES 100
  5   MAXINSTANCES 8
  6   MAXLOGHISTORY 292
  7  LOGFILE
  8   GROUP 1 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO01.LOG'  SIZE 50M,
  9   GROUP 2 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO02.LOG'  SIZE 50M,
10   GROUP 3 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/REDO03.LOG'  SIZE 50M
11  DATAFILE
12   'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF',
13   'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/UNDOTBS01.DBF',
14   'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSAUX01.DBF',
15   'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/USERS01.DBF'
16  CHARACTER SET ZHS16GBK
17 ;

Control file created.

SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 1239559 generated at 09/09/2010 16:53:41 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_74_%U_.ARC
ORA-00280: change 1239559 for thread 1 is in sequence #74

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
SQL> --需要74号arch来做recover
SQL> --restore 74号arch
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.restoresetarchivedlog(destination=>'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09');
  7  sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>74);
  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/ARCH_21_1_729276880',params=>null);
  9  sys.dbms_backup_restore.devicedeallocate;
10  end;
11 /

PL/SQL procedure successfully completed.

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.restoresetarchivedlog(destination=>'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09');
  7  sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>75);
  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:/oracle/product/10.2.0/db_1/database/ARCH_21_1_729276880',params=>null);
  9  sys.dbms_backup_restore.devicedeallocate;
10  end;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL> recover database using backup controlfile;
ORA-00279: change 1239644 generated at 09/09/2010 16:54:38 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_75_%U_.ARC
ORA-00280: change 1239644 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:/oracle/product/10.2.0/flash_recovery_area/ORA10G/ARCHIVELOG/2010_09_09/0727003661_001_00075.ARC
ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_76_%U_.ARC
ORA-00280: change 1239656 for thread 1 is in sequence #76
ORA-00278: log file
'D:/oracle/product/10.2.0/flash_recovery_area/ORA10G/ARCHIVELOG/2010_09_09/07270
03661_001_00075.ARC' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF
_1_76_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) The system cannot find the file specified.

SQL>
SQL>
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORA10G/SYSTEM01.DBF'

SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1
ORA-00289: suggestion :
D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORA10G/ARCHIVELOG/2010_09_09/O1_MF_
1_76_%U_.ARC
ORA-00280: change 1239656 for thread 1 is in sequence #76

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: cannot open archived log 'cancel;'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) The system cannot find the file specified.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from test.t1;

  COUNT(*)
----------
   1024

SQL>--我们看到还原到crash前未commit的状态。