Recuperando un Datafile

Detalles: Categoría de nivel principal o raíz: Oracle; Categoría: RMAN; Publicado: 27 Junio 2016

Generalmente cuando uno apaga un equipo sin darlo de baja de manera ordenada, puede quedar un datafile con estatus RECOVER, aquí muestro como podemos recuperarnos de este error.

El error que podemos encontrar en el alert de oracle es el siguiente:

ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '+DATA01/bd1/datafile/bd1_data.257.909657173'

La manera de diagnosticar el problema es haciendo un select sobre la vista v$datafile;

select name, status, file# from v$datafile;

Si encontramos algún datafile con status RECOVER, entonces necesitaremos recuperarlo, para eso siempre debemos de contar con nuestros respaldos completos y de redologs.

SQL> recover datafile '+DATA01/db1/datafile/db1_data.257.909657173';
ORA-00279: change 11036167507535 generated at 06/25/2016 02:59:03 needed for thread 1
ORA-00289: suggestion : /db/admin/db1/arch01/arch_db1_0001_0000001977_909664474.arc
ORA-00280: change 11036167507535 for thread 1 is in sequence #1977
 
Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/db/admin/db1/arch01/arch_db_0001_0000001977_909664474.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

En este caso requerimos del archivelog '/db/admin/db1/arch01/arch_db_0001_0000001977_909664474.arc', por lo que procederemos a recuperarlo.

 
1    1974    11036167505824 25-JUN-16 11036167506142 25-JUN-16
  1    1975    11036167506142 25-JUN-16 11036167506955 25-JUN-16
  1    1976    11036167506955 25-JUN-16 11036167507535 25-JUN-16
  1    1977    11036167507535 25-JUN-16 11036167507848 25-JUN-16
  1    1978    11036167507848 25-JUN-16 11036167508790 25-JUN-16
  1    1979    11036167508790 25-JUN-16 11036167510167 25-JUN-16
  1    1980    11036167510167 25-JUN-16 11036167532948 27-JUN-16
  1    1981    11036167532948 27-JUN-16 11036167544227 27-JUN-16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
121     74.38M     DISK        00:00:09     27-JUN-16
        BP Key: 121   Status: AVAILABLE  Compressed: YES  Tag: TAG20160627T091418
        Piece Name: /db/db1/db1/backupset/2016_06_27/o1_mf_annnn_TAG20160627T091418_cq2f5tg0_.bkp

  List of Archived Logs in backup set 121

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1982    11036167544227 27-JUN-16 11036167547148 27-JUN-16
  1    1983    11036167547148 27-JUN-16 11036167553060 27-JUN-16
 

RMAN>restore archivelog from sequence 1977 until sequence 1981;

 Starting restore at 27-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1977
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1978
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1979
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1980
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1981
channel ORA_DISK_1: reading from backup piece /db/dump01/db1/backupset/2016_06_27/o1_mf_annnn_TAG20160627T071759_cq26zzq8_.bkp
channel ORA_DISK_1: piece handle=/db/dump01/db1/backupset/2016_06_27/o1_mf_annnn_TAG20160627T071759_cq26zzq8_.bkp tag=TAG20160627T071759
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:05
Finished restore at 27-JUN-16

RMAN>exit

Y finalmente aplicamos el recovery

SQL>recover datafile '+DATA01/db1/datafile/db1_data.257.909657173';

ORA-00279: change 11036167507535 generated at 06/25/2016 02:59:03 needed for thread 1
ORA-00289: suggestion : /db/admin/db1/arch01/arch_b1_0001_0000001977_909664474.arc
ORA-00280: change 11036167507535 for thread 1 is in sequence #1977
 
Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 11036167507848 generated at 06/25/2016 02:59:57 needed for thread 1
ORA-00289: suggestion : /db/admin/db1/arch01/arch_db1_0001_0000001978_909664474.arc
ORA-00280: change 11036167507848 for thread 1 is in sequence #1978

 ORA-00279: change 11036167508790 generated at 06/25/2016 03:01:00 needed for thread 1
ORA-00289: suggestion : /db/admin/db1/arch01/arch_db1_0001_0000001979_909664474.arc
ORA-00280: change 11036167508790 for thread 1 is in sequence #1979

ORA-00279: change 11036167510167 generated at 06/25/2016 03:02:40 needed for thread 1
ORA-00289: suggestion : /db/admin/db1/arch01/arch_db1_0001_0000001980_909664474.arc
ORA-00280: change 11036167510167 for thread 1 is in sequence #1980

Log applied.
Media recovery complete.
SQL>
alter database datafile '+DATA01/db1/datafile/db1_data.257.909657173' online;

SQL>

Database altered.

Y verificamos

SQL> select name,status,file#  from v$datafile;
NAME                      STATUS       FILE#
------------------------- ------- ----------
+DATA01/bd1/datafile/bd1_data.257.909657173 ONLINE           6

Recuperando un Datafile

You are not authorised to post comments.

DBASupport - Foro