Oracle 利用dbms_backup_restore恢复测试（数据文件和控制文件全部丢失了)

￡神魔★判官ぃ 2022-07-12 05:28 193阅读 0赞

利用dbms\_backup\_restore恢复数据库

环境信息：

OS: RedHat5.9

ORACLE: 12C

测试场景：Oracle数据库的存储怀了，数据文件和控制文件全部丢失了，只有数据文件的备份集，且备份集中无控制文件

测试步骤：

1.     首先将数据库进行全备：

将控制文件自动备份关掉

ocrl:/home/oracle@oracle1>rmantarget /

RecoveryManager: Release 12.1.0.2.0 - Production on Mon Feb 13 11:37:22 2017

connectedto target database: OCRL (DBID=864450697)

RMAN>show all;

usingtarget database control file instead of recovery catalog

RMANconfiguration parameters for database with db\_unique\_name OCRL are:

CONFIGURERETENTION POLICY TO REDUNDANCY 1; \# default

CONFIGUREBACKUP OPTIMIZATION OFF;

CONFIGUREDEFAULT DEVICE TYPE TO DISK; \# default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data/backup/%F';

CONFIGUREDEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; \# default

CONFIGUREDATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; \# default

CONFIGUREARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; \# default

CONFIGUREMAXSETSIZE TO UNLIMITED; \# default

CONFIGUREENCRYPTION FOR DATABASE OFF; \# default

CONFIGUREENCRYPTION ALGORITHM 'AES128'; \# default

CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;\# default

CONFIGURERMAN OUTPUT TO KEEP FOR 7 DAYS; \# default

CONFIGUREARCHIVELOG DELETION POLICY TO NONE; \# default

CONFIGURESNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db\_1/dbs/snapcf\_ocrl.f';\# default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

old RMANconfiguration parameters:

CONFIGURECONTROLFILE AUTOBACKUP ON;

new RMANconfiguration parameters:

CONFIGURECONTROLFILE AUTOBACKUP OFF;

new RMANconfiguration parameters are successfully stored

开始全备

RMAN> backup format '/data/backup/%U' database;

Startingbackup at 2017/02/13 11:44:44

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA\_DISK\_1

channelORA\_DISK\_1: SID=27 device type=DISK

channel ORA\_DISK\_1:starting full datafile backup set

channelORA\_DISK\_1: specifying datafile(s) in backup set

inputdatafile file number=00002 name=/data/oradata/ocrl/datafile/sysaux01.dbf

inputdatafile file number=00001 name=/data/oradata/ocrl/datafile/system01.dbf

inputdatafile file number=00003 name=/data/oradata/ocrl/datafile/undotbs01.dbf

inputdatafile file number=00004 name=/data/oradata/ocrl/datafile/users01.dbf

inputdatafile file number=00005 name=/data/oradata/ocrl/datafile/test.dbf

inputdatafile file number=00006 name=/data/oradata/ocrl/datafile/tstest.dbf

channelORA\_DISK\_1: starting piece 1 at 2017/02/13 11:44:46

channelORA\_DISK\_1: finished piece 1 at 2017/02/13 11:45:15

piece handle=/data/backup/6ersfite\_1\_1 tag=TAG20170213T114446comment=NONE

channelORA\_DISK\_1: backup set complete, elapsed time: 00:00:29

channelORA\_DISK\_1: starting full datafile backup set

channelORA\_DISK\_1: specifying datafile(s) in backup set

includingcurrent control file in backup set

includingcurrent SPFILE in backup set

channelORA\_DISK\_1: starting piece 1 at 2017/02/13 11:45:17

channelORA\_DISK\_1: finished piece 1 at 2017/02/13 11:45:18

piece handle=/data/backup/6frsfiub\_1\_1 tag=TAG20170213T114446comment=NONE

channelORA\_DISK\_1: backup set complete, elapsed time: 00:00:01

Finishedbackup at 2017/02/13 11:45:18

RMAN> exit

查看备份：

ocrl:/data/backup@oracle1>cd/data/backup/

ocrl:/data/backup@oracle1>ll

total1721972

\-rw-r-----1 oracle dba 1750786048 Feb 13 11:45 6ersfite\_1\_1

\-rw-r-----1 oracle dba   10780672 Feb 13 11:456frsfiub\_1\_1

2.删除控制文件和数据文件

删除数据文件：

ocrl:/data/backup@oracle1>cd/data/oradata/ocrl/datafile/

ocrl:/data/oradata/ocrl/datafile@oracle1>ls

sysaux01.dbf  system01.dbf test.dbf  tstest.dbf  undotbs01.dbf users01.dbf

ocrl:/data/oradata/ocrl/datafile@oracle1>rm-rf \*

lsocrl:/data/oradata/ocrl/datafile@oracle1>ls

删除控制文件：

ocrl:/data/oradata/ocrl/datafile@oracle1>cd/data/oradata/ocrl/control/

ocrl:/data/oradata/ocrl/control@oracle1>ls

control01.ctl  control02.ctl

ocrl:/data/oradata/ocrl/control@oracle1>rm-rf \*

删除redo和tmpfile

ocrl:/data/oradata/ocrl/control@oracle1>cd/data/oradata/ocrl/redo/

ocrl:/data/oradata/ocrl/redo@oracle1>rm-rf \*

ocrl:/data/oradata/ocrl/redo@oracle1>cd/data/oradata/ocrl/tempfile/

ocrl:/data/oradata/ocrl/tempfile@oracle1>ls

temp01.dbf

ocrl:/data/oradata/ocrl/tempfile@oracle1>rm-rf \*

3.开始恢复

如果之前有参数文件，就用之前的参数文件，如果没有就自己手动修改init.ora文件，先将实例启动到nomount状态；

这里就用之前的参数文件启动

ocrl:/data/oradata/ocrl/tempfile@oracle1>sqlplus   / as sysdba

SQL\*Plus: Release 12.1.0.2.0Production on Mon Feb 13 15:06:07 2017

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  754974720 bytes

Fixed Size                            2928968 bytes

Variable Size                      524291768bytes

Database Buffers            222298112 bytes

Redo Buffers                       5455872bytes

SQL>

构造控制文件

使用dbms\_backup\_restore将控制文件从备份集中构造出来：

**SQL> declare**

**devtype varchar2(256);**

**done boolean;**

**begin**

**     devtype:=dbms\_backup\_restore.DeviceAllocate(type=>'',ident=>'t1');**

**     dbms\_backup\_restore.RestoreSetDatafile;**

**     sys.dbms\_backup\_restore.RestoreControlfileTo(cfname=>'/data/oradata/ocrl/control/control01.ctl');   **

**    sys.dbms\_backup\_restore.RestoreBackupPiece(done=>done,handle=>'/data/backup/6frsfiub\_1\_1',params=>null);**

**    sys.dbms\_backup\_restore.DeviceDeallocate;**

**end;**

**/**

**PL/SQL procedure successfully completed.**

可以在路径下看到控制文件被构造出来了。

ocrl:/u01/app/oracle/product/12.1.0/db\_1@oracle1>cd/data/oradata/ocrl/control/

ocrl:/data/oradata/ocrl/control@oracle1>ls

control01.ctl

由于启动的参数文件里面控制文件做了冗余，如下:

SQL> show parameter control

NAME                                   TYPE       VALUE

\----------------------------------------------- ------------------------------

control\_file\_record\_keep\_time         integer    7

control\_files                           string     /data/oradata/ocrl/control/con

trol01.ctl, /data/oradata/ocrl

/control/control02.ctl

control\_management\_pack\_access        string     DIAGNOSTIC+TUNING

SQL>

这里先将数据库关闭，然后利用下面的方法从刚才构建的控制文件再copy一份

ocrl:/data/oradata/ocrl/control@oracle1>ls

control01.ctl

ocrl:/data/oradata/ocrl/control@oracle1>cpcontrol01.ctl control02.ctl

这样在启动到mount状态下就不会报错

ocrl:/data/oradata/ocrl/tempfile@oracle1>sqlplus/ as sysdba

SQL\*Plus: Release 12.1.0.2.0 Production onMon Feb 13 15:17:59 2017

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  754974720 bytes

Fixed Size             2928968 bytes

Variable Size               524291768 bytes

Database Buffers     222298112 bytes

Redo Buffers                  5455872 bytes

SQL> alter database mount;

Database altered.

查看数据文件的file\#号和绝对路径：

SQL> col name format a40

SQL> select file\#,name from v$datafile;

FILE\# NAME

\---------- ----------------------------------------

1 /data/oradata/ocrl/datafile/system01.dbf

2 /data/oradata/ocrl/datafile/sysaux01.dbf

3 /data/oradata/ocrl/datafile/undotbs01.dbf

4 /data/oradata/ocrl/datafile/users01.dbf

5 /data/oradata/ocrl/datafile/test.dbf

6 /data/oradata/ocrl/datafile/tstest.dbf

6 rows selected.

还原数据文件

现在知道数据文件的file\#和name后，还是利用dbms\_backup\_restore从备份集中还原数据文件

**SQL>declare**

**    devtype varchar2(256);**

**    done boolean;**

**begin**

**   devtype:=sys.dbms\_backup\_restore.DeviceAllocate(type=>'',ident=>'t1');**

**    sys.dbms\_backup\_restore.RestoreSetDatafile;**

**  sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>01,toname=>'/data/oradata/ocrl/datafile/system01.dbf');**

**   sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>02,toname=>'/data/oradata/ocrl/datafile/sysaux01.dbf');**

**   sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>03,toname=>'/data/oradata/ocrl/datafile/undotbs01.dbf');**

**   sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>04,toname=>'/data/oradata/ocrl/datafile/users01.dbf');**

**   sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>05,toname=>'/data/oradata/ocrl/datafile/test.dbf');**

**    sys.dbms\_backup\_restore.RestoreDatafileto(dfnumber=>06,toname=>'/data/oradata/ocrl/datafile/tstest.dbf');**

**    sys.dbms\_backup\_restore.RestoreBackupPiece(done=>done,handle=>'/data/backup/6ersfite\_1\_1',params=>null);**

**    sys.dbms\_backup\_restore.DeviceDeallocate;**

**end;**

**/**

**PL/SQLprocedure successfully completed**.

查看数据文件路径下是否已经将数据文件还原到指定目录

ocrl:/data/oradata/ocrl/datafile@oracle1>ls

sysaux01.dbf  system01.dbf test.dbf  tstest.dbf  undotbs01.dbf users01.dbf

至此，数据文件已经被还原至指定目录

现在执行不完全恢复

RMAN> recover database;

Startingrecover at 2017/02/13 14:38:11

Startingimplicit crosscheck backup at 2017/02/13 14:38:11

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA\_DISK\_1

channelORA\_DISK\_1: SID=24 device type=DISK

Crosschecked5 objects

Finishedimplicit crosscheck backup at 2017/02/13 14:38:12

Startingimplicit crosscheck copy at 2017/02/13 14:38:12

usingchannel ORA\_DISK\_1

Finishedimplicit crosscheck copy at 2017/02/13 14:38:12

searchingfor all files in the recovery area

catalogingfiles...

nofiles cataloged

usingchannel ORA\_DISK\_1

startingmedia recovery

archivedlog for thread 1 with sequence 2 is already on disk as file/data/oradata/ocrl/redo/redo02.log

archivedlog file name=/data/oradata/ocrl/redo/redo02.log thread=1 sequence=2

mediarecovery complete, elapsed time: 00:00:00

Finishedrecover at 2017/02/13 14:38:15

恢复完成，之后将实例打开，因为redo文件也丢失了，执行不完全恢复，要加resetlogs参数

RMAN> alter database open resetlogs;

Statementprocessed

RMAN>

看看redo文件是否被重建？

ocrl:/data/oradata/ocrl/redo@oracle1>cd/data/oradata/ocrl/redo/

ocrl:/data/oradata/ocrl/redo@oracle1>ls

redo01.log  redo02.log redo03.log

ocrl:/data/oradata/ocrl/redo@oracle1>

至此数据库恢复完成。

Oracle 利用dbms_backup_restore恢复测试（数据文件和控制文件全部丢失了)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle控制文件丢失：备份和重建方法

相关 Oracle控制文件丢失：备份策略与恢复操作

相关 RMAN备份与恢复之控制文件丢失

相关丢失了所有控制文件副本后进行恢复以trace文件恢复

相关 Oracle 利用dbms_backup_restore恢复测试（数据文件和控制文件全部丢失了)

相关 Oracle数据文件转移和丢失处理

相关 linux利用文件句柄恢复oracle数据

相关 Oracle 重建控制文件（在丢失控制文件的情况下，如何恢复数据库）

相关 Oracle 利用bbed破坏数据文件测试

相关控制文件丢失恢复实验