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

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;

NAMETYPEVALUE

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

diagnostic_deststring/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) TablespaceRB segs Datafile Name

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

1750SYSTEM***/diskA/orcl/system01.dbf

2540SYSAUX***/diskA/orcl/sysaux01.dbf

3105UNDOTBS1***/diskA/orcl/undotbs01.dbf

45USERS***/diskA/orcl/users01.dbf

50EXAMPLE***/diskA/orcl/example01.dbf

List of Temporary Files

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

File Size(MB) TablespaceMaxsize(MB) Tempfile Name

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

129TEMP32767/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 KeyType LV SizeDevice Type Elapsed Time Completion Time

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

3Full1.10GDISK00:00:4927-MAY-16

BP Key: 3Status: AVAILABLECompressed: NOTag: 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 SCNCkp TimeName

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

1Full 100489627-MAY-16 /diskA/orcl/system01.dbf

2Full 100489627-MAY-16 /diskA/orcl/sysaux01.dbf

3Full 100489627-MAY-16 /diskA/orcl/undotbs01.dbf

4Full 100489627-MAY-16 /diskA/orcl/users01.dbf

5Full 100489627-MAY-16 /diskA/orcl/example01.dbf

List of Archived Log Copies for database with db_unique_name ORCL

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

KeyThrd SeqS Low Time

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

217A 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 (c) 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 Area914440192 bytes

Fixed Size2258600 bytes

Variable Size583010648 bytes

Database Buffers322961408 bytes

Redo Buffers6209536 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

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

1OK015342960741006667

File Name: /diskA/orcl/system01.dbf

Block Type Blocks Failing Blocks Processed

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

Data063486

Index013175

Other03997

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

2OK021198691851006615

File Name: /diskA/orcl/sysaux01.dbf

Block Type Blocks Failing Blocks Processed

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

Data011532

Index08011

Other028379

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

3OK0870134401006662

File Name: /diskA/orcl/undotbs01.dbf

Block Type Blocks Failing Blocks Processed

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

Data00

Index00

Other012570

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4OK018667952214

File Name: /diskA/orcl/users01.dbf

Block Type Blocks Failing Blocks Processed

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

Data091

Index039

Other0492

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

5OK03143940083986608

File Name: /diskA/orcl/example01.dbf

Block Type Blocks Failing Blocks Processed

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

Data04432

Index01159

Other03050

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 TypeStatus Blocks Failing Blocks Examined

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

SPFILEOK02

Control File OK0594