Restoring Corrupted Datafile Using RMAN in Oracle

Datafile corrupt is a partial disaster in database. it will impact for all objects belonging the tablespace. In this case, DBA can either restore the problematic/corrupted datafile from backup.

Create Tablespace, User and Table:

SQL> CREATE TABLESPACE tbs_hr DATAFILE '/u02/oradata/NONCDB/hr01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Tablespace created.

SQL> CREATE USER samad IDENTIFIED BY Oracle
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE tbs_hr
  QUOTA UNLIMITED ON tbs_hr;  

User created.

SQL> GRANT DBA TO SAMAD;

Grant succeeded.

SQL> CONN SAMAD/Oracle
Connected.


SQL> CREATE TABLE TBL_TEST (ID NUMBER, NAME CHAR(2000)) TABLESPACE TBS_HR;

Table created.

SQL> INSERT INTO TBL_TEST VALUES (1001, 'SAMAD');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1002, 'ALL DBA');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1003, 'ALL DBA1');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1004, 'ALL DBA2');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1005, 'ALL DBA3');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1006, 'ALL DBA3');

1 row created.

SQL> COMMIT;

Commit complete.

Object Details:

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME =  'TBL_TEST';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93574      93574


SQL> SELECT ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
                DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
                FROM SAMAD.TBL_TEST;

        ID      FILE#     BLOCK#
---------- ---------- ----------
      1001         12        133
      1002         12        133
      1003         12        133
      1004         12        134
      1005         12        134
      1006         12        134
      1007         12        135
      1008         12        135

9 rows selected.

SQL>
SQL> COL NAME FOR A70
SQL> SET LINESIZE 100
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u02/oradata/NONCDB/datafile/o1_mf_system_hn33p6nh_.dbf
         2 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm37yg_.dbf
         ...
        11 /u02/oradata/cat/rccat01.dbf
        12 /u02/oradata/NONCDB/hr01.dbf

12 rows selected.

Database Backup:

We can restore corrupted datafile from backup either from full backup or datafile backup.

[oracle@ocm12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 7 13:18:55 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: NONCDB (DBID=1867026507)

RMAN> backup database;

Starting backup at 07-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u02/oradata/NONCDB/datafile/o1_mf_undotbs1_hn33qbtl_.dbf
input datafile file number=00006 name=/u02/oradata/NONCDB/datafile/o1_mf_users_hn33q9r9_.dbf
input datafile file number=00001 name=/u02/oradata/NONCDB/datafile/o1_mf_system_hn33p6nh_.dbf
input datafile file number=00003 name=/u02/oradata/NONCDB/datafile/o1_mf_sysaux_hn33ofkb_.dbf
input datafile file number=00005 name=/u02/oradata/NONCDB/datafile/tbs_test_01.dbf
input datafile file number=00011 name=/u02/oradata/cat/rccat01.dbf
input datafile file number=00012 name=/u02/oradata/NONCDB/hr01.dbf
input datafile file number=00002 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm37yg_.dbf
input datafile file number=00007 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm3823_.dbf
input datafile file number=00008 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm39m5_.dbf
input datafile file number=00009 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_hormf0ly_.dbf
input datafile file number=00010 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horoo9m9_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T132319_htfsjr8j_.bkp tag=TAG20201107T132319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_ncsnf_TAG20201107T132319_htfskvwh_.bkp tag=TAG20201107T132319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-20

RMAN> exit

Recovery Manager complete.

SQL> INSERT INTO SAMAD.TBL_TEST VALUES(1009,'TEST1');

1 row created.

SQL> COMMIT;

Commit complete.

Datafile Corruption:

Assumed datafile has been corrupted at this point.

For demonstration, I corrupted/rename a datafile manually but don’t try this on your real life database.

[oracle@ocm12c ~]$ mv /u02/oradata/NONCDB/hr01.dbf /u02/oradata/NONCDB/hr01001.dbf

[oracle@ocm12c ~]$ exit
exit

SQL> SELECT ID FROM SAMAD.TBL_TEST;

        ID
----------
      1001
      1002
      1003
      1004
      1005
      1006
      1007
      1008
      1009

9 rows selected.

#Above data retrieve from Database Buffer Cache. 

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT ID FROM SAMAD.TBL_TEST;
SELECT ID FROM SAMAD.TBL_TEST
                     *
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> INSERT INTO SAMAD.TBL_TEST VALUES(1010,'TEST1');
INSERT INTO SAMAD.TBL_TEST VALUES(1010,'TEST1')
                  *
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Recover Datafile from backup:

It will not impact for other tablespace and data.

[oracle@ocm12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 7 14:39:57 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: NONCDB (DBID=1867026507)

RMAN> SQL 'ALTER DATABASE DATAFILE 12 OFFLINE';

using target database control file instead of recovery catalog
sql statement: ALTER DATABASE DATAFILE 12 OFFLINE

RMAN> RUN {
RESTORE DATAFILE 12;
RECOVER DATAFILE 12;
}

Starting restore at 07-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK

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 00012 to /u02/oradata/NONCDB/hr01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T132319_htfsjr8j_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T132319_htfsjr8j_.bkp tag=TAG20201107T132319
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-NOV-20

Starting recover at 07-NOV-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 07-NOV-20

RMAN> SQL 'ALTER DATABASE DATAFILE 12 ONLINE';

sql statement: ALTER DATABASE DATAFILE 12 ONLINE

Access the table or corrupted block’s data after recovered:

SQL> SELECT ID FROM SAMAD.TBL_TEST;

        ID
----------
      1001
      1002
      1003
      1004
      1005
      1006
      1007
      1008
      1009

9 rows selected.

Restoring Corrupted Datafile Using RMAN in Oracle

About Me

Categories

Archives

VISITORS