rman备份之rman advisor(转)

最新推荐文章于 2024-06-17 14:41:35 发布

cuipingjian9673

最新推荐文章于 2024-06-17 14:41:35 发布

阅读量138

点赞数

文章标签：数据库

RMAN Advisor

# new for 11g

DRA(Data Recover Advisor) 数据恢复建议器

工作原理: 一个严重的数据库错误会导致Health Monitor做数据完整检查,

检测到故障就会记录在ADR中,之后DRA根据错误信息做出修复策略.

list failure;

list failure 282 detail;

advise failure;

repair failure preview;

repair failure;

validate datafile 4;

List Failure – lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.

Advise Failure – presents manual and automatic repair options

Repair Failure – automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.

[oracle@hua ~]$ rlwrap rman target /

RMAN> list backup; # 确认有全备

RMAN> backup as compressed backupset database;

RMAN> validate check logical datafile 4;

Starting validate at 03-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

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

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

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4 OK 0 12301 64000 17866733

File Name: /u01/app/oracle/oradata/orcl/users01.dbf

Block Type Blocks Failing Blocks Processed

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

Data 0 39604

Index 0 3907

Other 0 8188

Finished validate at 03-AUG-12

[oracle@hua ~]$ dd f=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=280 <

> XXX

> EOF

0+1 records in

0+1 records out

4 bytes (4 B) copied, 0.02506 seconds, 0.2 kB/s

[oracle@hua ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192

RMAN> validate check logical datafile 4;

Starting validate at 03-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/u01/app/oracle/oradata/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

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

4 OK 1 143 64007 17866733

File Name: /u01/app/oracle/oradata/orcl/users01.dbf

Block Type Blocks Failing Blocks Processed

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

Data 0 239

Index 0 172

Other 0 63446

Finished validate at 03-AUG-12

RMAN> list failure

2> ;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

RMAN> list failure 461 detail

2> ;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

Impact: Some objects in tablespace might be unavailable

List of child failures for parent failure ID 461

Failure ID Priority Status Time Detected Summary

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

464 HIGH OPEN 03-AUG-12 Block 280 in datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' is media corrupt

Impact: Object owned by might be unavailable

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

Impact: Some objects in tablespace might be unavailable

List of child failures for parent failure ID 461

Failure ID Priority Status Time Detected Summary

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

464 HIGH OPEN 03-AUG-12 Block 280 in datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' is media corrupt

Impact: Object owned by might be unavailable

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

no manual actions available

Automated Repair Options

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

Option Repair Description

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

1 Perform. block media recovery of block 280 in file 4

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_628118166.hm

RMAN> host

2> ;

[oracle@hua ~]$ cat /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_628118166.hm

# block media recovery

recover datafile 4 block 280;

[oracle@hua ~]$ exit

exit

host command complete

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_628118166.hm

contents of repair script.:

# block media recovery

recover datafile 4 block 280;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_628118166.hm

contents of repair script.:

# block media recovery

recover datafile 4 block 280;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting recover at 03-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_03/o1_mf_nnndf_TAG20120803T205127_81qlbjlq_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_03/o1_mf_nnndf_TAG20120803T205127_81qlbjlq_.bkp tag=TAG20120803T205127

channel ORA_DISK_1: restored block(s) from backup piece 1

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

starting media recovery

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

Finished recover at 03-AUG-12

repair failure complete

################################################

更严重的错误

sys@oray>conn scott/tiger;

Connected.

scott@oray>create table tt as select * from dba_objects;

Table created.

scott@oray>insert into tt select * from tt;

72461 rows created.

scott@oray>commit;

Commit complete.

scott@oray>conn / as sysdba

Connected.

sys@oray>select tablespace_name,file_id,block_id,blocks from dba_extents where segment_name='TT' and wner='SCOTT';

TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS

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

USERS 4 1792 8

USERS 4 1800 8

USERS 4 1808 8

USERS 4 1816 8

USERS 4 1824 8

USERS 4 1832 8

...

USERS 4 1408 128

USERS 4 1920 128

USERS 4 2048 128

USERS 4 2176 128

USERS 4 2304 128

USERS 4 2432 128

32 rows selected.

sys@oray>

sys@oray>show parameter block_size;

NAME TYPE VALUE

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

db_block_size integer 8192

SQL> show parameter block_size

破坏数据块不要破坏数据文件头

[oracle@hua ~]$ dd f=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=280 <

> xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx

> EOF

0+1 records in

0+1 records out

45 bytes (45 B) copied, 9e-05 seconds, 500 kB/s

[oracle@hua ~]$ dd f=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=640 <

> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

> EOF

0+1 records in

0+1 records out

45 bytes (45 B) copied, 7.2e-05 seconds, 625 kB/s

[oracle@hua ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.1.0 - Production on Fri Aug 3 21:53:50 2012

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf

Page 280 is marked corrupt

Corrupt block relative dba: 0x01000118 (file 4, block 280)

Bad header found during dbv:

Data in bad block:

type: 120 format: 0 rdba: 0x78787878

last change scn: 0x7878.78787878 seq: 0x78 flg: 0x78

spare1: 0x78 spare2: 0x78 spare3: 0x7878

consistency value in tail: 0xe04a0602

check value in block header: 0x7878

block checksum disabled

Page 640 is marked corrupt

Corrupt block relative dba: 0x01000280 (file 4, block 640)

Bad header found during dbv:

Data in bad block:

type: 88 format: 0 rdba: 0x58585858

last change scn: 0x5858.58585858 seq: 0x58 flg: 0x58

spare1: 0x58 spare2: 0x58 spare3: 0x5858

consistency value in tail: 0xe0622001

check value in block header: 0x5858

block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined : 64000

Total Pages Processed (Data) : 1375

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 172

Total Pages Failing (Index): 0

Total Pages Processed (Other): 62308

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 143

Total Pages Marked Corrupt : 2

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 17883381 (0.17883381)

RMAN> list failure;

no failures found that match specification

RMAN> validate datafile 4;

Starting validate at 03-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

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

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

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4 FAILED 0 143 64006 17883381

File Name: /u01/app/oracle/oradata/orcl/users01.dbf

Block Type Blocks Failing Blocks Processed

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

Data 0 2303

Index 0 172

Other 2 61382

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/orcl/oray/trace/oray_ora_18121.trc for details

Finished validate at 03-AUG-12

RMAN> list failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

# 继续破坏

[oracle@hua ~]$ cp /u01/app/oracle/oradata/orcl/redo01.log //u01/app/oracle/oradata/orcl/system01.dbf

[oracle@hua ~]$ rm -rf /u01/app/oracle/oradata/orcl/control0*

[oracle@hua ~]$ rm -rf /u01/app/oracle/oradata/orcl/redo0*

# DB崩溃

sys@oray>conn / as sysdba

Connected.

sys@oray>alter system checkpoint;

ERROR:

ORA-03114: not connected to ORACLE

alter system checkpoint

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 21191

Session ID: 138 Serial number: 121

# 用RMAN修复

[oracle@hua ~]$ rlwrap rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 3 22:00:15 2012

connected to target database (not started)

RMAN> startup;

Oracle instance started

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

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

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

RMAN-03002: failure of startup command at 08/03/2012 22:00:44

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

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

545 CRITICAL OPEN 03-AUG-12 Control file /u01/app/oracle/oradata/orcl/control01.ctl is missing

505 CRITICAL OPEN 03-AUG-12 System datafile 1: '/u01/app/oracle/oradata/orcl/system01.dbf' is corrupt

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

545 CRITICAL OPEN 03-AUG-12 Control file /u01/app/oracle/oradata/orcl/control01.ctl is missing

505 CRITICAL OPEN 03-AUG-12 System datafile 1: '/u01/app/oracle/oradata/orcl/system01.dbf' is corrupt

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

analyzing automatic repair options complete

Not all specified failures can currently be repaired.

The following failures must be repaired before advise for others can be given.

Failure ID Priority Status Time Detected Summary

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

545 CRITICAL OPEN 03-AUG-12 Control file /u01/app/oracle/oradata/orcl/control01.ctl is missing

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

no manual actions available

Automated Repair Options

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

Option Repair Description

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

1 Use a multiplexed copy to restore control file /u01/app/oracle/oradata/orcl/control01.ctl

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_2041894805.hm

# 恢复控制文件

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_2041894805.hm

contents of repair script.:

# restore control file using multiplexed copy

restore controlfile from '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';

sql 'alter database mount';

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_2041894805.hm

contents of repair script.:

# restore control file using multiplexed copy

restore controlfile from '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 03-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy

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

output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Finished restore at 03-AUG-12

sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

# 恢复数据库

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

620 CRITICAL OPEN 03-AUG-12 Redo log group 3 is unavailable

614 CRITICAL OPEN 03-AUG-12 Redo log group 2 is unavailable

608 CRITICAL OPEN 03-AUG-12 Redo log group 1 is unavailable

505 CRITICAL OPEN 03-AUG-12 System datafile 1: '/u01/app/oracle/oradata/orcl/system01.dbf' is corrupt

623 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo03.log is missing

617 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo02.log is missing

611 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo01.log is missing

461 HIGH OPEN 03-AUG-12 Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

analyzing automatic repair options complete

Not all specified failures can currently be repaired.

The following failures must be repaired before advise for others can be given.

Failure ID Priority Status Time Detected Summary

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

620 CRITICAL OPEN 03-AUG-12 Redo log group 3 is unavailable

614 CRITICAL OPEN 03-AUG-12 Redo log group 2 is unavailable

608 CRITICAL OPEN 03-AUG-12 Redo log group 1 is unavailable

505 CRITICAL OPEN 03-AUG-12 System datafile 1: '/u01/app/oracle/oradata/orcl/system01.dbf' is corrupt

623 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo03.log is missing

617 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo02.log is missing

611 HIGH OPEN 03-AUG-12 Redo log file /u01/app/oracle/oradata/orcl/redo01.log is missing

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

1. If file /u01/app/oracle/oradata/orcl/redo03.log was unintentionally renamed or moved, restore it

2. If file /u01/app/oracle/oradata/orcl/redo02.log was unintentionally renamed or moved, restore it

3. If file /u01/app/oracle/oradata/orcl/redo01.log was unintentionally renamed or moved, restore it

Automated Repair Options

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

Option Repair Description

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

1 Perform. incomplete database recovery to SCN 17883245

Strategy: The repair includes point-in-time recovery with some data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_4190794936.hm

RMAN> repair failure preview;

Strategy: The repair includes point-in-time recovery with some data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_4190794936.hm

contents of repair script.:

# database point-in-time recovery

restore database until scn 17883245;

recover database until scn 17883245;

alter database open resetlogs;

# scn是从这里获取的

sys@oray>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

17883245

sys@oray>select file#,checkpoint_change#,last_change# from v$datafile;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

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

1 17883245

2 17883245

3 17883245

4 17883545

sys@oray>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

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

1 0

2 17883245

3 17883245

4 17883545

sys@oray>select SEQUENCE#,name,first_change#,NEXT_CHANGE# from v$archived_log;

SEQUENCE#

----------

NAME

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

FIRST_CHANGE# NEXT_CHANGE#

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

800

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_03/o1_mf_1_800_81qoqvyo_.arc

17862048 17883245

# 在线日志已经全部删除，只能回到最新的归档日志的scn号

# 完成恢复

RMAN> repair failure;

Strategy: The repair includes point-in-time recovery with some data loss

Repair script. /u01/app/oracle/diag/rdbms/orcl/oray/hm/reco_4190794936.hm

contents of repair script.:

# database point-in-time recovery

restore database until scn 17883245;

recover database until scn 17883245;

alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 03-AUG-12

using channel ORA_DISK_1

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/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

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

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_03/o1_mf_nnndf_TAG20120803T205127_81qlbjlq_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_03/o1_mf_nnndf_TAG20120803T205127_81qlbjlq_.bkp tag=TAG20120803T205127

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:06:14

Finished restore at 03-AUG-12

Starting recover at 03-AUG-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 800 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_03/o1_mf_1_800_81qoqvyo_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_03/o1_mf_1_800_81qoqvyo_.arc thread=1 sequence=800

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

Finished recover at 03-AUG-12

database opened

repair failure complete

# 可尝试，不备份，resetlog之后继续破坏数据库。然后进行还原和恢复，可以清晰的看到日志接档

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24679076/viewspace-748843/，如需转载，请注明出处，否则将追究法律责任。