admin

RMAN完全恢复案例

admin 数据库 2021-12-02 978浏览 0

目录

一、归档模式

示例一：所有数据文件丢失，控制文件也丢失

示例二：新建表空间对应的数据文件丢失

示例三：某数据文件丢失，且所在的磁盘损坏

二、非归档模式

示例四：某数据文件丢失，日志未覆盖情况下做RMAN完全恢复

示例五：某数据文件丢失，日志被覆盖情况下做RMAN不完全恢复



一、归档模式

首先，使用RMAN对数据库做一个数据库全备份

RMAN> run{

2> allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> backup database filesperset 1 format  '/u01/app/oracle/RMAN_Backup/hot/full_%n_%T_%t_%s_%p.bak';

5> backup spfile format='/u01/app/oracle/RMAN_Backup/hot/spfile_%n_%U_%T.bak';

6> sql 'alter system archive log current';

7> backup archivelog all format '/u01/app/oracle/RMAN_Backup/hot/arch_%d_%T_%s_%p.bak' delete input;

8> backup current controlfile format '/u01/app/oracle/RMAN_Backup/hot/ctl_%d_%T_%s_%p.bak';

9> release channel c1;

10> release channel c2;

11> }

 

released channel: ORA_DISK_1

allocated channel: c1

channel c1: SID=36 device type=DISK

 

allocated channel: c2

channel c2: SID=31 device type=DISK

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/OraDB11g/system01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak tag=TAG20170808T153703 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:35

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/OraDB11g/example01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak tag=TAG20170808T153703 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:35

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak tag=TAG20170808T153703 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:08

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak tag=TAG20170808T153703 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:07

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak tag=TAG20170808T153703 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:00

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/OraDB11g/users01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak tag=TAG20170808T153703 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 08-AUG-17

including current control file in backup set

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak tag=TAG20170808T153703 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak tag=TAG20170808T153703 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak tag=TAG20170808T153749 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

sql statement: alter system archive log current

 

Starting backup at 08-AUG-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=4 STAMP=949239430

input archived log thread=1 sequence=9 RECID=5 STAMP=951418831

input archived log thread=1 sequence=10 RECID=6 STAMP=951429659

channel c1: starting piece 1 at 08-AUG-17

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=1 sequence=11 RECID=7 STAMP=951458673

input archived log thread=1 sequence=12 RECID=8 STAMP=951474337

input archived log thread=1 sequence=13 RECID=9 STAMP=951493070

input archived log thread=1 sequence=14 RECID=10 STAMP=951493070

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak tag=TAG20170808T153750 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

channel c1: deleting archived log(s)

archived log file name=/u01/app/oracle/arch/arch_1_949237404_8.log RECID=4 STAMP=949239430

archived log file name=/u01/app/oracle/arch/arch_1_949237404_9.log RECID=5 STAMP=951418831

archived log file name=/u01/app/oracle/arch/arch_1_949237404_10.log RECID=6 STAMP=951429659

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak tag=TAG20170808T153750 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:03

channel c2: deleting archived log(s)

archived log file name=/u01/app/oracle/arch/arch_1_949237404_11.log RECID=7 STAMP=951458673

archived log file name=/u01/app/oracle/arch/arch_1_949237404_12.log RECID=8 STAMP=951474337

archived log file name=/u01/app/oracle/arch/arch_1_949237404_13.log RECID=9 STAMP=951493070

archived log file name=/u01/app/oracle/arch/arch_1_949237404_14.log RECID=10 STAMP=951493070

Finished backup at 08-AUG-17

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/hot/ctl_ORADB11G_20170808_23_1.bak tag=TAG20170808T153754 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

released channel: c1

 

released channel: c2

 

 

--查看备份的内容

RMAN> list backup summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

12      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

13      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

14      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

15      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

16      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

17      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

18      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

19      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153703

20      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153749

21      B  A  A DISK        08-AUG-17       1       1       NO         TAG20170808T153750

22      B  A  A DISK        08-AUG-17       1       1       NO         TAG20170808T153750

23      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T153754

 

************************************************************************************************************************************

示例一：所有数据文件丢失，控制文件也丢失

 

场景：在seiang用户下，创建一张wjq1表，并插入三条数据（第一条数据提交归档、第二条数据只提交不归档，第三条数据不提交不归档），之后突然断电，然后删除所有的控制文件及数据文件，在此情况，对数据库做RMAN完全恢复；

 

SYS@seiang11g>select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/OraDB11g/system01.dbf

/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

/u01/app/oracle/oradata/OraDB11g/users01.dbf

/u01/app/oracle/oradata/OraDB11g/example01.dbf

/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

 

--创建表wjq1

SYS@seiang11g>create table seiang.wjq1(id number,name varchar2(30)) tablespace seiang;

Table created.

 

--第一条数据，提交，归档

SYS@seiang11g>insert into seiang.wjq1 values(1,'wjq1');

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>alter system switch logfile;

System altered.

 

--第二条数据，提交，不归档

SYS@seiang11g>insert into seiang.wjq1 values(2,'wjq2');

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

--第三条数据，不提交，不归档

SYS@seiang11g>insert into seiang.wjq1 values(3,'wjq3');

1 row created.

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1         16 CURRENT

         2         14 INACTIVE

         3         15 ACTIVE

 

 

--模拟断电，手动删除所有的数据文件和控制文件

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/*.dbf

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/*.ctl

SYS@seiang11g>host rm /u01/app/oracle/fast_recovery_area/OraDB11g/*.ctl

 

 

--尝试启动数据库

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             805310440 bytes

Database Buffers          436207616 bytes

Redo Buffers                8892416 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

 

--登录到RMAN，开始执行恢复

[oracle@seiang11g ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 15:51:33 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB11G (not mounted)

 

RMAN> restore controlfile;

 

Starting restore at 08-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/08/2017 15:52:18

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

 

 

RMAN> restore controlfile from '/u01/app/oracle/RMAN_Backup/hot/ctl_ORADB11G_20170808_23_1.bak';

 

Starting restore at 08-AUG-17

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/OraDB11g/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl

Finished restore at 08-AUG-17

 

 

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

RMAN> run{

2> allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> restore database;

5> recover database;

6> alter database open;

7> }

 

allocated channel: c1

channel c1: SID=21 device type=DISK

 

allocated channel: c2

channel c2: SID=17 device type=DISK

 

Starting restore at 08-AUG-17

Starting implicit crosscheck backup at 08-AUG-17

Crosschecked 11 objects

Finished implicit crosscheck backup at 08-AUG-17

 

Starting implicit crosscheck copy at 08-AUG-17

Finished implicit crosscheck copy at 08-AUG-17

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

 

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak

channel c2: starting datafile backup set restore

channel c2: specifying datafile(s) to restore from backup set

channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/OraDB11g/system01.dbf

channel c2: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak

channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak tag=TAG20170808T153703

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak

channel c2: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak tag=TAG20170808T153703

channel c2: restored backup piece 1

channel c2: restore complete, elapsed time: 00:00:35

channel c2: starting datafile backup set restore

channel c2: specifying datafile(s) to restore from backup set

channel c2: restoring datafile 00005 to /u01/app/oracle/oradata/OraDB11g/example01.dbf

channel c2: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak

channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak tag=TAG20170808T153703

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:04

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak

channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak tag=TAG20170808T153703

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:07

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf

channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak

channel c2: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak tag=TAG20170808T153703

channel c2: restored backup piece 1

channel c2: restore complete, elapsed time: 00:00:11

channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak tag=TAG20170808T153703

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:01

Finished restore at 08-AUG-17

 

Starting recover at 08-AUG-17

 

starting media recovery

 

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo01.log

channel c1: starting archived log restore to default destination

channel c1: restoring archived log

archived log thread=1 sequence=13

channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak

channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak tag=TAG20170808T153750

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/arch/arch_1_949237404_13.log thread=1 sequence=13

archived log file name=/u01/app/oracle/oradata/OraDB11g/redo02.log thread=1 sequence=14

archived log file name=/u01/app/oracle/oradata/OraDB11g/redo03.log thread=1 sequence=15

archived log file name=/u01/app/oracle/oradata/OraDB11g/redo01.log thread=1 sequence=16

media recovery complete, elapsed time: 00:00:01

Finished recover at 08-AUG-17

 

released channel: c1

released channel: c2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 08/08/2017 15:55:34

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

--只能使用resetlogs打开数据库

RMAN> alter database open resetlogs;

database opened

 

 

SYS@seiang11g>select * from seiang.wjq1;

 

        ID NAME

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

         1 wjq1

         2 wjq2

       

由恢复的结果可以发现，恢复到最后一次提交。

 

总结：
当所有数据都丢失（控制文件、数据文件）时，采用RMAN完全恢复的步骤为：
1、通过restore controlfile from ”备份控制文件的路径”
2、alter database mount
3、restore database
4、recover databse
5、alter database open  resetlogs；

 

************************************************************************************************************************************

示例二：新建表空间对应的数据文件丢失

 

场景：新建表空间good，然后在其上新建表wjq2，并插入三条数据，然后模拟断电，删除good表空间所对应的数据文件，在此情况下，做RMAN的完全恢复（因是新建的表空间，故没有相关的数据文件备份）。

 

SYS@seiang11g>create tablespace good datafile '/u01/app/oracle/oradata/OraDB11g/good01.dbf' size 50M;

Tablespace created.

 

SYS@seiang11g>create table seiang.wjq2(id number,age number) tablespace good;

Table created.

 

--第一条数据，提交，归档

SYS@seiang11g>insert into seiang.wjq2 values(1,20);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          1 CURRENT

         2          0 UNUSED

         3          0 UNUSED

 

SYS@seiang11g>alter system switch logfile;

System altered.

 

--第二条数据，提交，不归档

SYS@seiang11g>insert into seiang.wjq2 values(2,30);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

--第三条数据，不提交，不归档

SYS@seiang11g>insert into seiang.wjq2 values(3,40);

1 row created.

 

SYS@seiang11g>select * from seiang.wjq2;

 

        ID        AGE

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

         1         20

         2         30

         3         40

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          1 ACTIVE

         2          2 CURRENT

         3          0 UNUSED

 

 

--模拟断电，手工删除good表空间所对应的数据文件

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

 

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf

 

 

--尝试启动数据库

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'

 

 

SYS@seiang11g>select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         7 ONLINE  ONLINE  FILE NOT FOUND                                                             0

 

 

--登陆RMAN，执行恢复操作

[oracle@seiang11g ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 16:20:15 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORADB11G (DBID=3377212249, not open)

 

RMAN>

 

RMAN> run{

2> sql 'alter database datafile 7 offline';

3> alter database open;

4> restore datafile 7;

5> recover datafile 7;

6> sql 'alter database datafile 7 online';

7> }

 

using target database control file instead of recovery catalog

sql statement: alter database datafile 7 offline

 

database opened

 

Starting restore at 08-AUG-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=34 device type=DISK

 

creating datafile file number=7 name=/u01/app/oracle/oradata/OraDB11g/good01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 08-AUG-17

 

Starting recover at 08-AUG-17

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 08-AUG-17

 

sql statement: alter database datafile 7 online

 

从恢复过程可以发现，虽然没有新建的good01.dbf的备份信息，但使用restore datafile语句时，RMAN实际上是做了create datafile 操作


SYS@seiang11g>select * from seiang.wjq2;

 

        ID        AGE

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

         1         20

         2         30

 

总结：
当新建表空间对应数据文件丢失时，采用RMAN恢复步骤：
1、sql 'alter database datafile 数据文件号 offline'；
2、alter database open；
3、restore datafile 数据文件号；
4、recover datafile 数据文件号；
5、sql 'alter database datafile 数据文件号 online'；

 

 
************************************************************************************************************************************

 

示例三：数据文件丢失，且所在的磁盘损坏

 

场景：在seiang用户下，创建wjq3表，隶属于good表空间，并插入三条数据，之后突然断电，且其所在碰盘坏掉了。

 

SYS@seiang11g>select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/OraDB11g/system01.dbf

/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

/u01/app/oracle/oradata/OraDB11g/users01.dbf

/u01/app/oracle/oradata/OraDB11g/example01.dbf

/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

/u01/app/oracle/oradata/OraDB11g/good01.dbf

 

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables

  2  where owner='SEIANG';

 

OWNER                          TABLE_NAME                     TABLESPACE_NAME

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

SEIANG                         WJQ1                           SEIANG

SEIANG                         WJQ2                           GOOD

 

--创建表wjq3

SYS@seiang11g>create table seiang.wjq3(id number,address varchar2(23)) tablespace good;

Table created.

 

--第一条数据，提交，归档

SYS@seiang11g>insert into seiang.wjq3 values(1,'beijing');

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          1 INACTIVE

         2          2 INACTIVE

         3          3 CURRENT

 

SYS@seiang11g>alter system switch logfile;

System altered.

 

--第二条数据，提交，不归档

SYS@seiang11g>insert into seiang.wjq3 values(2,'lanzhou');

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

--第三条数据，不提交，不归档

SYS@seiang11g>insert into seiang.wjq3 values(3,'tianjin');

1 row created.

 

SYS@seiang11g>select * from seiang.wjq3;

 

        ID ADDRESS

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

         1 beijing

         2 lanzhou

         3 tianjin

 

--突然断电，good表空间对应的数据文件丢失，并且所在的磁盘损坏

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

SYS@seiang11g>

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf

 

 

--尝试启动数据库

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'

 

SYS@seiang11g>select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         7 ONLINE  ONLINE  FILE NOT FOUND                                                             0

 

假设磁盘介质损坏了，更换需要时间，先把数据文件恢复到一个新的目录下（不同的物理位置）

 

RMAN> run{

2> sql 'alter database datafile 7 offline';

3> alter database open;

4> set newname for datafile 7 to '/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf';

5> restore datafile 7;

6> switch datafile 7;

7> recover datafile 7;

8> sql 'alter database datafile 7 online';

9> }

 

using target database control file instead of recovery catalog

sql statement: alter database datafile 7 offline

 

database opened

 

executing command: SET NEWNAME

 

Starting restore at 08-AUG-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 device type=DISK

 

creating datafile file number=7 name=/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 08-AUG-17

 

datafile 7 switched to datafile copy

input datafile copy RECID=3 STAMP=951496524 file name=/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf

 

Starting recover at 08-AUG-17

using channel ORA_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_1.log

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_2.log

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_3.log

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_4.log

archived log file name=/u01/app/oracle/arch/arch_1_951494153_1.log thread=1 sequence=1

archived log file name=/u01/app/oracle/arch/arch_1_951494153_2.log thread=1 sequence=2

media recovery complete, elapsed time: 00:00:00

Finished recover at 08-AUG-17

 

sql statement: alter database datafile 7 online

 

说明：
①set newname for 告诉RMAN还原数据文件的新位置在哪里。这个命令在restore前出现。
②switch datafile 更新controlfile，让控制文件使用这个新位置恢复数据。这个命令要在recover前出现。

 

 

--恢复完成

SYS@seiang11g>select * from seiang.wjq3;

 

        ID ADDRESS

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

         1 beijing

         2 lanzhou

 

SYS@seiang11g>select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/OraDB11g/system01.dbf

/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

/u01/app/oracle/oradata/OraDB11g/users01.dbf

/u01/app/oracle/oradata/OraDB11g/example01.dbf

/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf

 

 

--损坏的磁盘介质更换完成后，将表空间移回原来的位置

SYS@seiang11g> alter tablespace good offline;

Tablespace altered.

 

SYS@seiang11g>host mv /u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf /u01/app/oracle/oradata/OraDB11g

 

 

--更新控制文件中数据文件位置的信息

SYS@seiang11g>alter tablespace good rename datafile '/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf' to '/u01/app/oracle/oradata/OraDB11g/good01.dbf';

Tablespace altered.

 

SYS@seiang11g>alter tablespace good online;

Tablespace altered.

 

SYS@seiang11g>select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/OraDB11g/system01.dbf

/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

/u01/app/oracle/oradata/OraDB11g/users01.dbf

/u01/app/oracle/oradata/OraDB11g/example01.dbf

/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

/u01/app/oracle/oradata/OraDB11g/good01.dbf

 

 

总结：
当被破坏的数据文件其所在磁盘都坏掉时，采用rman恢复步骤为：
1、sql 'alter database datafile 数据文件号 offline'；
2、alter database open；
3、set newname for datafile 数据文件号 to ’数据文件新存放路径‘；
4、restore datafile 数据文件号；
5、switch datafile 数据文件号；
4、recover datafile 数据文件号；
5、sql 'alter database datafile 数据文件号 online'；

 

************************************************************************************************************************************

 

二、非归档模式

 

先删除原来的RMAN备份，然后切换成非归档模式，用RMAN做冷备，注意非归档模式不能做热备


--删除原来的备份

RMAN> delete noprompt backup;

 

using channel ORA_DISK_1

 

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

12      12      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak

13      13      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak

14      14      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak

15      15      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak

16      16      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak

17      17      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak

18      18      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak

19      19      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak

20      20      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak

21      21      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak

22      22      1   1   AVAILABLE   DISK        /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak RECID=12 STAMP=951493023

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak RECID=13 STAMP=951493023

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak RECID=14 STAMP=951493060

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak RECID=15 STAMP=951493058

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak RECID=16 STAMP=951493066

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak RECID=17 STAMP=951493066

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak RECID=18 STAMP=951493067

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak RECID=19 STAMP=951493067

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak RECID=20 STAMP=951493069

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak RECID=21 STAMP=951493071

deleted backup piece

backup piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak RECID=22 STAMP=951493071

Deleted 11 objects

 

 

--切换到非归档模式

SYS@seiang11g>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@seiang11g>startup mount

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

SYS@seiang11g>alter database noarchivelog;

Database altered.

 

SYS@seiang11g>archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/arch

Oldest online log sequence     3

Current log sequence           5

SYS@seiang11g>alter database open;

Database altered.

 

 

--用RMAN对数据库做一个冷备份

[oracle@seiang11g ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:13:14 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORADB11G (DBID=3377212249)

 

RMAN> run{

2> shutdown immediate;

3> startup mount;

4> allocate channel c1 type disk;

5> allocate channel c2 type disk;

6> backup database filesperset 2 format  '/u01/app/oracle/RMAN_Backup/cold/full_%n_%T_%t_%s_%p.bak';

7> backup spfile format='/u01/app/oracle/RMAN_Backup/cold/spfile_%n_%U_%T.bak';

8> backup archivelog all format '/u01/app/oracle/RMAN_Backup/cold/arch_%d_%T_%s_%p.bak';

9> backup current controlfile format '/u01/app/oracle/RMAN_Backup/cold/ctl_%d_%T_%s_%p.bak';

10> alter database open;

11> release channel c1;

12> release channel c2;

13> }

 

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area    1252663296 bytes

 

Fixed Size                     2252824 bytes

Variable Size                788533224 bytes

Database Buffers             452984832 bytes

Redo Buffers                   8892416 bytes

 

allocated channel: c1

channel c1: SID=18 device type=DISK

 

allocated channel: c2

channel c2: SID=19 device type=DISK

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/OraDB11g/system01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/OraDB11g/users01.dbf

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:45

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/OraDB11g/example01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/OraDB11g/seiang01.dbf

channel c1: starting piece 1 at 08-AUG-17

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:45

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/OraDB11g/good01.dbf

channel c2: starting piece 1 at 08-AUG-17

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:03

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:11

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 08-AUG-17

including current control file in backup set

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498882_30_1.bak tag=TAG20170808T171345 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498874_29_1.bak tag=TAG20170808T171345 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/spfile_ORADB11G_0vsbde43_1_1_20170808.bak tag=TAG20170808T171443 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

Starting backup at 08-AUG-17

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=13 RECID=14 STAMP=951494133

channel c1: starting piece 1 at 08-AUG-17

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=1 sequence=14 RECID=16 STAMP=951494153

input archived log thread=1 sequence=15 RECID=17 STAMP=951494153

input archived log thread=1 sequence=16 RECID=15 STAMP=951494153

channel c2: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_32_1.bak tag=TAG20170808T171444 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=18 STAMP=951495099

input archived log thread=1 sequence=2 RECID=19 STAMP=951495624

input archived log thread=1 sequence=3 RECID=20 STAMP=951496104

input archived log thread=1 sequence=4 RECID=21 STAMP=951496520

channel c1: starting piece 1 at 08-AUG-17

channel c2: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_33_1.bak tag=TAG20170808T171444 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:01

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_34_1.bak tag=TAG20170808T171444 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

Starting backup at 08-AUG-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 08-AUG-17

channel c1: finished piece 1 at 08-AUG-17

piece handle=/u01/app/oracle/RMAN_Backup/cold/ctl_ORADB11G_20170808_35_1.bak tag=TAG20170808T171447 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-AUG-17

 

database opened

 

released channel: c1

 

released channel: c2

 

 

RMAN> list backup summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

23      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

24      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

25      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

26      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

27      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

28      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171345

29      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171443

30      B  A  A DISK        08-AUG-17       1       1       NO         TAG20170808T171444

31      B  A  A DISK        08-AUG-17       1       1       NO         TAG20170808T171444

32      B  A  A DISK        08-AUG-17       1       1       NO         TAG20170808T171444

33      B  F  A DISK        08-AUG-17       1       1       NO         TAG20170808T171447

 

 

************************************************************************************************************************************ 

 

示例四：某个数据文件丢失，日志未覆盖情况下做RMAN完全恢复

 

 

--查看当前日志

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          4 INACTIVE

         2          5 CURRENT

         3          3 INACTIVE

 

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables

  2  where owner='SEIANG';

 

OWNER                          TABLE_NAME                     TABLESPACE_NAME

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

SEIANG                         WJQ1                           SEIANG

SEIANG                         WJQ2                           GOOD

SEIANG                         WJQ3                           GOOD

 

--创建表test1,隶属于good表空间

SYS@seiang11g>create table seiang.test1(id number,grade number) tablespace good;

Table created.

 

--第一条数据，提交，归档

SYS@seiang11g>insert into seiang.test1 values(1,90);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>alter database archive log current;

alter database archive log current

                       *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

（非归档模式下，不能使用该命令切换日志）

 

SYS@seiang11g>alter system switch logfile;

System altered.

 

--第二条数据，提交，不归档

SYS@seiang11g>insert into seiang.test1 values(2,85);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

--第三条数据，不提交，不归档

SYS@seiang11g>insert into seiang.test1 values(3,99);

1 row created.

 

SYS@seiang11g>select * from seiang.test1;

 

        ID      GRADE

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

         1         90

         2         85

         3         99

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          4 INACTIVE

         2          5 ACTIVE

         3          6 CURRENT

 

 

--突然断电，手工删除good表空间对应的数据文件

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf

 

--尝试启动数据库

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'

 

 

--登陆到RMAN，执行恢复操作

[oracle@seiang11g ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:24:08 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORADB11G (DBID=3377212249, not open)

 

RMAN> run{

2> restore database;

3> recover database;

4> alter database open;

5> }

 

Starting restore at 08-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 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 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

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 /u01/app/oracle/oradata/OraDB11g/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

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 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/OraDB11g/good01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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 00005 to /u01/app/oracle/oradata/OraDB11g/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 08-AUG-17

 

Starting recover at 08-AUG-17

using channel ORA_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log

archived log file name=/u01/app/oracle/oradata/OraDB11g/redo02.log thread=1 sequence=5

archived log file name=/u01/app/oracle/oradata/OraDB11g/redo03.log thread=1 sequence=6

media recovery complete, elapsed time: 00:00:01

Finished recover at 08-AUG-17

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 08/08/2017 17:25:05

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

RMAN> alter database open resetlogs;

database opened

 

SYS@seiang11g>select * from seiang.test1;

 

        ID      GRADE

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

         1         90

         2         85

由恢复的结果可以看出，恢复到最后一次提交。

 

 

总结：
非归档模式下，当某个数据文件丢失时且日志没有被覆盖的情况下，采用RMAN完全恢复的步骤为：
1、restore datafile 数据文件号（通过select file#,error from v$recover_file查询获取）
2、recover datafile 数据文件号
3、alter database open；
恢复方式，与归档模式下的一般恢复模式一样。

 


************************************************************************************************************************************

 

示例五：某个数据文件丢失，日志被覆盖情况下做RMAN不完全恢复

 

--创建托test2，隶属于good表空间

SYS@seiang11g>create table seiang.test2(name varchar2(20),grade number) tablespace good;

Table created.

 

--第一条数据，提交，归档

SYS@seiang11g>insert into seiang.test2 values('wjq',89);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

SYS@seiang11g>alter system switch logfile;

System altered.

 

--第二条数据，提交，不归档

SYS@seiang11g>insert into seiang.test2 values('seiang',90);

1 row created.

 

SYS@seiang11g>commit;

Commit complete.

 

--第三条数据，不提交，不归档

SYS@seiang11g>insert into seiang.test2 values('wjqgood',100);

1 row created.

 

SYS@seiang11g>select * from seiang.test2;

 

NAME                      GRADE

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

wjq                          89

seiang                       90

wjqgood                     100

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          1 ACTIVE

         2          2 CURRENT

         3          0 UNUSED

 

--三个日志文件，执行四次切换，使得之前的日志被覆盖掉

SYS@seiang11g>alter system switch logfile;

System altered.

 

SYS@seiang11g>/

System altered.

 

SYS@seiang11g>/

System altered.

 

SYS@seiang11g>/

System altered.

 

SYS@seiang11g>select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

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

         1          4 INACTIVE

         2          5 INACTIVE

         3          6 CURRENT

 

--突然断电，good表空间对应的数据文件丢失

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

SYS@seiang11g>

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf

 

--尝试启动数据库

SYS@seiang11g>startup

ORACLE instance started.

 

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'

 

SYS@seiang11g>select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         7 ONLINE  ONLINE  FILE NOT FOUND                                                             0

 

 

--登陆RMAN，执行恢复操作

[oracle@seiang11g ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:36:02 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORADB11G (DBID=3377212249, not open)

 

RMAN> restore datafile 7;

 

Starting restore at 08-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 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 00007 to /u01/app/oracle/oradata/OraDB11g/good01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 08-AUG-17

 

RMAN> recover datafile 7;

 

Starting recover at 08-AUG-17

using channel ORA_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo01.log

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log

RMAN-08187: WARNING: media recovery until SCN 1138946 complete

Finished recover at 08-AUG-17

 

以上红色字体标识内容，可知recover失败了，提示信息不够明确，原因就是指日志被覆盖，不连续了，在此我们只能做整个数据库的恢复，具体操作如下：


[oracle@seiang11g ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:38:35 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area    1252663296 bytes

 

Fixed Size                     2252824 bytes

Variable Size                788533224 bytes

Database Buffers             452984832 bytes

Redo Buffers                   8892416 bytes

 

 

RMAN> restore controlfile from '/u01/app/oracle/RMAN_Backup/cold/ctl_ORADB11G_20170808_35_1.bak';

 

Starting restore at 08-AUG-17

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/OraDB11g/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl

Finished restore at 08-AUG-17

 

 

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

 

RMAN> restore database;

 

Starting restore at 08-AUG-17

Starting implicit crosscheck backup at 08-AUG-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

Crosschecked 10 objects

Finished implicit crosscheck backup at 08-AUG-17

 

Starting implicit crosscheck copy at 08-AUG-17

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-AUG-17

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

using channel ORA_DISK_1

 

skipping datafile 7; already restored to file /u01/app/oracle/oradata/OraDB11g/good01.dbf

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 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

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 /u01/app/oracle/oradata/OraDB11g/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

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 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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 00005 to /u01/app/oracle/oradata/OraDB11g/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak

channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 08-AUG-17

 

RMAN> recover database;

 

Starting recover at 08-AUG-17

using channel ORA_DISK_1

 

starting media recovery

 

RMAN-08187: WARNING: media recovery until SCN 1138946 complete

Finished recover at 08-AUG-17

 

 

--查看当前数据库的SCN，发现已经恢复成功

SYS@seiang11g>select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

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

           1138946

 

SYS@seiang11g> select file#,checkpoint_change# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

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

         1            1138946

         2            1138946

         3            1138946

         4            1138946

         5            1138946

         6            1138946

         7            1138946

 

SYS@seiang11g> select file#,checkpoint_change# from v$datafile_header;

 

     FILE# CHECKPOINT_CHANGE#

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

         1            1138946

         2            1138946

         3            1138946

         4            1138946

         5            1138946

         6            1138946

         7            1138946

 

SYS@seiang11g>alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

--由于在线日志文件的SCN和控制文件，数据文件不一致，只能使用resetlogs打开数据库

SYS@seiang11g>alter database open resetlogs;

Database altered.

 

SYS@seiang11g>select * from seiang.test2;

select * from seiang.test2

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables

  2  where owner='SEIANG';

 

OWNER                          TABLE_NAME                     TABLESPACE_NAME

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

SEIANG                         WJQ1                           SEIANG

SEIANG                         WJQ2                           GOOD

SEIANG                         WJQ3                           GOOD

 

由此可见，这里只能恢复至备份状态，因为日志被丢失不能做完全恢复，只能恢复到开始备份时的状态。

总结：
非归档模式下，当某个数据文件丢失时且日志被覆盖的情况下，采用RMAN完全恢复的步骤为：：
1、通过restore controlfile from方式转储最新的控制文件备份
2、alter database mount
3、restore database
4、recover databse
5、alter database open  resetlogs；
只是因为日志被丢失，只能做不完全恢复。


转载请注明：梦绘设计 » 数据库 » RMAN完全恢复案例

版权声明

本站《作品展示》类文章均为原创，转载必须注明出处，技术分享类文章部分来源于网络，版权归原作者所有,若侵权请留言。

你可能喜欢:
最新文章
热门文章
热评文章
标签列表
Copyright 2018-2022 梦绘设计工作室 All Rights Reserved 杭州蔓牛网络科技有限公司
Powered By Z-BlogPHP