Recovery en cas de perte d’un datafile du tablespace SYSTEM: Pas d’arrêt normal possible (on ne peut pas mettre SYSTEM offline) donc shutdown abort et il faut faire un restore datafile + recover datafile puis réouvrir la base.
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
Je supprime le datafile du tablespace SYSTEM:
+ rm /u/DEMO/DATA/DEMO100/system01.dbf + ls -l /u/DEMO/DATA/DEMO100/system01.dbf ls: cannot access /u/DEMO/DATA/DEMO100/system01.dbf: No such file or directory
d’où l’erreur suivante:
RMAN> shutdown immediate; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of shutdown command at 11/12/2013 16:22:08 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u/DEMO/DATA/DEMO100/system01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Résolution en utilisant Recovery Advisor:
RMAN> list failure; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of list command at 11/12/2013 16:22:10 ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u/DEMO/DATA/DEMO100/system01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Ce n’est pas possible car la base est restée ouverte:
RMAN> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
On redémarre (la base va rester en mount):
RMAN> startup force Oracle instance started database mounted RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 11/12/2013 16:22:23 ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u/DEMO/DATA/DEMO100/system01.dbf'
et on peut continuer 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 ---------- -------- --------- ------------- ------- 3704 CRITICAL OPEN 12-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' is missing
RMAN> advise failure;
RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 3704 CRITICAL OPEN 12-NOV-13 System datafile 1: '/u/DEMO/DATA/DEMO100/system01.dbf' is missing analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=5 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/system01.dbf was unintentionally renamed or moved, restore it Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 1 Strategy: The repair includes complete media recovery with no data loss Repair script: /u/DEMO/diag/rdbms/demo100/DEMO100/hm/reco_2148409986.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_2148409986.hm contents of repair script: # restore and recover datafile restore ( datafile 1 ); recover datafile 1; sql 'alter database datafile 1 online'; Do you really want to execute the above repair (enter YES or NO)? executing repair script 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 00001 to /u/DEMO/DATA/DEMO100/system01.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:55 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:07 Finished recover at 12-NOV-13 sql statement: alter database datafile 1 online repair failure complete Do you want to open the database (enter YES or NO)? database opened Recovery Manager complete.
La base a été indisponible le temps du restore/recovery. Aucune perte de données.