11gR2数据库RMAN完全恢复数据库

创建测试数据

create table test(number id);

declare

a number(2);

begin

for a in 1001 … 20000 loop

insert into test values(a);

end loop;

commit;

end;

/

RMAN备份数据库

破坏数据文件

more redo03.log >example01.dbf

1. 检查数据库告警日志文件

   1.1 告警文件日志

   show parameter diag;

   NAME TYPE VALUE

   ---

   diagnostic_dest string /u01/app/oracle

   cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace

   1.2 打开告警日志文件

   如果文件太大，使用如下命令：tail -n 10000 alert_orcl.log >1.log

   日志文件类似于如下报错：

Starting background process SMCO

Fri May 27 15:40:43 2016

SMCO started with pid=25, OS id=4604

Fri May 27 16:21:33 2016

Hex dump of (file 5, block 154) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5057.trc

Corrupt block relative dba: 0x0140009a (file 5, block 154)

Bad header found during buffer read

Data in bad block:

type: 1 format: 2 rdba: 0x000009a0

last change scn: 0x8010.00000006 seq: 0xb1 flg: 0x67

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x00000000

check value in block header: 0x100

computed block checksum: 0x0

Reading datafile ‘/diskA/orcl/example01.dbf’ for corruption at rdba: 0x0140009a (file 5, block 154)

Reread (file 5, block 154) found same corrupt data (no logical check)

Fri May 27 16:21:33 2016

2. 使用RMAN验证数据库

   通过RMAN命令验证，数据库哪些文件损坏

   rman target /

RMAN> validate database;

Starting validate at 27-MAY-16

using channel ORA_DISK_1

RMAN-06169: could not read file header for datafile 5 error reason 7

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

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

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

RMAN-03002: failure of validate command at 05/27/2016 16:25:30

RMAN-06056: could not access datafile 5

使用report schema确定数据文件

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles

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

File Size(MB) Tablespace RB segs Datafile Name

---

1 750 SYSTEM *** /diskA/orcl/system01.dbf

2 540 SYSAUX *** /diskA/orcl/sysaux01.dbf

3 105 UNDOTBS1 *** /diskA/orcl/undotbs01.dbf

4 5 USERS *** /diskA/orcl/users01.dbf

5 0 EXAMPLE *** /diskA/orcl/example01.dbf

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---

1 29 TEMP 32767 /diskA/orcl/temp01.dbf

3. 还原中会使用哪些备份文件

使用RESTORE DATABASE PREVIEW;命令，查看restore数据库时，需要使用哪些备份集和归档日志文件。此命令仅仅访问RMAN repository，查询备份元数据，并不会读备份文件，无法确保他们能够被还原。

RMAN> RESTORE DATABASE PREVIEW;

Starting restore at 27-MAY-16

using channel ORA_DISK_1

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

---

3 Full 1.10G DISK 00:00:49 27-MAY-16

    BP Key: 3   Status: AVaiLABLE  Compressed: NO  Tag: TAG20160527T153108

    Piece Name: /diskB/ORCL/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T153108_cnhxxy09_.bkp

List of Datafiles in backup set 3

File LV Type Ckp SCN Ckp Time Name

---

1 Full 1004896 27-MAY-16 /diskA/orcl/system01.dbf

2 Full 1004896 27-MAY-16 /diskA/orcl/sysaux01.dbf

3 Full 1004896 27-MAY-16 /diskA/orcl/undotbs01.dbf

4 Full 1004896 27-MAY-16 /diskA/orcl/users01.dbf

5 Full 1004896 27-MAY-16 /diskA/orcl/example01.dbf

List of Archived Log Copies for database with db_unique_name ORCL

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

Key Thrd Seq S Low Time

---

2 1 7 A 27-MAY-16

    Name: /diskB/1_7_912952844.dbf

Media recovery start SCN is 1004896

Recovery must be done beyond SCN 1004896 to clear datafile fuzziness

Finished restore at 27-MAY-16

如上所示，还原会使用备份集3和归档日志文件/diskB/1_7_912952844.dbf

4.验证备份

RMAN> RESTORE DATABASE VALIDATE;

Starting restore at 27-MAY-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece /diskB/ORCL/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T153108_cnhxxy09_.bkp

channel ORA_DISK_1: piece handle=/diskB/ORCL/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T153108_cnhxxy09_.bkp tag=TAG20160527T153108

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:16

Finished restore at 27-MAY-16

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

Starting restore at 27-MAY-16

using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log /diskB/1_6_912952844.dbf

channel ORA_DISK_1: scanning archived log /diskB/1_7_912952844.dbf

Finished restore at 27-MAY-16

5.恢复数据库

RMAN>STARTUP MOUNT;  
RMAN>RESTORE DATABASE;
RMAN>RECOVER DATABASE;
RMAN>ALTER DATABASE OPEN;

示例

[oracle@node1 trace]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 27 17:29:50 2016

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 914440192 bytes

Fixed Size 2258600 bytes

Variable Size 583010648 bytes

Database Buffers 322961408 bytes

Redo Buffers 6209536 bytes

RMAN> restore database;

Starting restore at 27-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /diskA/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /diskA/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /diskA/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /diskA/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /diskA/orcl/example01.dbf

channel ORA_DISK_1: reading from backup piece /diskB/ORCL/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T153108_cnhxxy09_.bkp

channel ORA_DISK_1: piece handle=/diskB/ORCL/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T153108_cnhxxy09_.bkp tag=TAG20160527T153108

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 27-MAY-16

RMAN> recover database;

Starting recover at 27-MAY-16

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 27-MAY-16

RMAN> alter database open;

database opened

附录

validate database正确的输出

Starting validate at 27-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00001 name=/diskA/orcl/system01.dbf

input datafile file number=00002 name=/diskA/orcl/sysaux01.dbf

input datafile file number=00005 name=/diskA/orcl/example01.dbf

input datafile file number=00003 name=/diskA/orcl/undotbs01.dbf

input datafile file number=00004 name=/diskA/orcl/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:36

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---

1 OK 0 15342 96074 1006667

File Name: /diskA/orcl/system01.dbf

Block Type Blocks Failing Blocks Processed

---

Data 0 63486

Index 0 13175

Other 0 3997

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---

2 OK 0 21198 69185 1006615

File Name: /diskA/orcl/sysaux01.dbf

Block Type Blocks Failing Blocks Processed

---

Data 0 11532

Index 0 8011

Other 0 28379

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---

3 OK 0 870 13440 1006662

File Name: /diskA/orcl/undotbs01.dbf

Block Type Blocks Failing Blocks Processed

---

Data 0 0

Index 0 0

Other 0 12570

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---

4 OK 0 18 667 952214

File Name: /diskA/orcl/users01.dbf

Block Type Blocks Failing Blocks Processed

---

Data 0 91

Index 0 39

Other 0 492

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---

5 OK 0 31439 40083 986608

File Name: /diskA/orcl/example01.dbf

Block Type Blocks Failing Blocks Processed

---

Data 0 4432

Index 0 1159

Other 0 3050

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

including current control file for validation

including current SPFILE in backup set

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

List of Control File and SPFILE

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

File Type Status Blocks Failing Blocks Examined

---

SPFILE OK 0 2

Control File OK 0 594