【RMAN】利用ADR诊断和修复数据库
利用DRA(Data Recovery Adviser)诊断和修复数据库案例
一、创建一个测试表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/T1/test.dbf' size 10m autoextend off; Tablespace created.
二、RMAN下备份数据库
# 这里使用的是CDB进行测试,所以只备份了CDB
RMAN> backup database root; Starting backup at 26-JUL-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/T1/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/T1/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/T1/undotbs01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/T1/test.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/T1/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-JUL-21 channel ORA_DISK_1: finished piece 1 at 26-JUL-21 piece handle=/u01/app/backup/backupset/T1_20210726_1078934694.bak tag=TAG20210726T160454 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 26-JUL-21 Starting Control File and SPFILE Autobackup at 26-JUL-21 piece handle=/u01/app/backup/backupset/c-2888150031-20210726-06.ctl comment=NONE Finished Control File and SPFILE Autobackup at 26-JUL-21
三、关闭实例,OS下删除test表空间
# 这时启动数据库会报错 SQL> startup ORACLE instance started. Total System Global Area 2516582152 bytes Fixed Size 9137928 bytes Variable Size 536870912 bytes Database Buffers 1962934272 bytes Redo Buffers 7639040 bytes Database mounted. ORA-01157: cannot identify/lock data file 13 - see DBWR trace file ORA-01110: data file 13: '/u01/app/oracle/oradata/T1/test.dbf'
四、进入RMAN诊断问题
RMAN> list failure; using target database control file instead of recovery catalog Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 1142 HIGH OPEN 26-JUL-21 One or more non-system datafiles are missing
五、生成有关故障建议
RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 1142 HIGH OPEN 26-JUL-21 One or more non-system datafiles are missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file /u01/app/oracle/oradata/T1/test.dbf was unintentionally renamed or moved, restore it Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 13 Strategy: The repair includes complete media recovery with no data loss ----------修复策略 Repair script: /u01/app/oracle/diag/rdbms/t1/T1/hm/reco_1743182304.hm ----------修复脚本
六、系统下查看脚本
# restore and recover datafile restore ( datafile 13 ); recover datafile 13; sql 'alter database datafile 13 online';
七、RMAN下执行脚本中的命令
RMAN> restore ( datafile 13 ); Starting restore at 26-JUL-21 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 00013 to /u01/app/oracle/oradata/T1/test.dbf channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210726_1078934694.bak channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210726_1078934694.bak tag=TAG20210726T160454 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 26-JUL-21 RMAN> recover datafile 13; Starting recover at 26-JUL-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 26-JUL-21 RMAN> sql 'alter database datafile 13 online'; sql statement: alter database datafile 13 online
八、打开数据库确认是否恢复成功
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ------------ OPEN