Recovery en cas de perte d’un datafile non système: il suffit de mettre le datafile offline, de faire un restore datafile + recover datafile et de le remettre online.
Ici un exemple en utilisant Recovery Advisor, sur une base de demo créée ici.
J’ai les datafiles suivants:
RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name DEMO100 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM *** /u/DEMO/DATA/DEMO100/system01.dbf 2 323 EXAMPLE *** /u/DEMO/DATA/DEMO100/example01.dbf 3 790 SYSAUX *** /u/DEMO/DATA/DEMO100/sysaux01.dbf 4 160 UNDOTBS1 *** /u/DEMO/DATA/DEMO100/undotbs01.dbf 6 10 USERS *** /u/DEMO/DATA/DEMO100/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 88 TEMP 32767 /u/DEMO/DATA/DEMO100/temp01.dbf Recovery Manager complete.
Je supprime le datafile du tablespace USERS:
+ rm /u/DEMO/DATA/DEMO100/users01.dbf + ls -l /u/DEMO/DATA/DEMO100/users01.dbf ls: cannot access /u/DEMO/DATA/DEMO100/users01.dbf: No such file or directory
d’où l’erreur suivante en interrogeant une table de ce tablespace:
RMAN> select * from TEST; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 11/12/2013 16:04:36 ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/u/DEMO/DATA/DEMO100/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Voici la résolution automatique avec Recovery Advisor:
RMAN> list failure;
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 ---------- -------- --------- ------------- ------- 8 HIGH OPEN 12-NOV-13 One or more non-system datafiles are missing
RMAN> advise failure;
RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 8 HIGH OPEN 12-NOV-13 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=243 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. If file /u/DEMO/DATA/DEMO100/users01.dbf was unintentionally renamed or moved, restore it Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 6 Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_1074024518.hm
RMAN> repair failure;
RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_1074024518.hm contents of repair script: # restore and recover datafile sql 'alter database datafile 6 offline'; restore ( datafile 6 ); recover datafile 6; sql 'alter database datafile 6 online'; Do you really want to execute the above repair (enter YES or NO)? executing repair script sql statement: alter database datafile 6 offline Starting restore at 12-NOV-13 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 00006 to /u/DEMO/DATA/DEMO100/users01.dbf channel ORA_DISK_1: reading from backup piece /u/DEMO/backup/0qoon9qd_1_1 channel ORA_DISK_1: piece handle=/u/DEMO/backup/0qoon9qd_1_1 tag=TAG20131111T185157 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-NOV-13 Starting recover at 12-NOV-13 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 11 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_11/o1_mf_1_11_9826sn83_.arc archived log for thread 1 with sequence 12 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_12_983tcnhw_.arc archived log for thread 1 with sequence 13 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_13_9840xv86_.arc archived log for thread 1 with sequence 14 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_14_9841jrl3_.arc archived log for thread 1 with sequence 15 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_15_9841wbqh_.arc archived log for thread 1 with sequence 16 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_16_984dgnq2_.arc archived log for thread 1 with sequence 17 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_17_984f83n6_.arc archived log for thread 1 with sequence 18 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_18_984g92pf_.arc archived log for thread 1 with sequence 19 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_19_984gm55y_.arc archived log for thread 1 with sequence 20 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_20_984jdzkt_.arc archived log for thread 1 with sequence 21 is already on disk as file /u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_21_984jqp6m_.arc archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_11/o1_mf_1_11_9826sn83_.arc thread=1 sequence=11 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_12_983tcnhw_.arc thread=1 sequence=12 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_13_9840xv86_.arc thread=1 sequence=13 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_14_9841jrl3_.arc thread=1 sequence=14 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_15_9841wbqh_.arc thread=1 sequence=15 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_16_984dgnq2_.arc thread=1 sequence=16 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_17_984f83n6_.arc thread=1 sequence=17 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_18_984g92pf_.arc thread=1 sequence=18 archived log file name=/u/DEMO/FRA/DEMO100/archivelog/2013_11_12/o1_mf_1_19_984gm55y_.arc thread=1 sequence=19 media recovery complete, elapsed time: 00:00:02 Finished recover at 12-NOV-13 sql statement: alter database datafile 6 online repair failure complete Recovery Manager complete.
La base est restée ouverte, seul le datafile en question était indisponible pendant la panne. Aucune perte de données.