Recovery cases: perte d’un datafile SYSTEM

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.

Laisser un commentaire