Recovery cases: perte d’un datafile (non système)

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.

Laisser un commentaire