Accidentally Deleted Oracle Database Datafile on Linux Server

Sysadmin accidentally deleted an Oracle database datafile on Linux server. There are a couple of ways to recover this database datafile, like restoring the deleted datafile from RMAN backup, etc.

In this special situation, sysadmin has just deleted the dbf file, and then realized it immediately. So we can try a different way to recover this datafile from OS file descriptor, which is still open in memory by Oracle database background process.

IDENTIFY which datafile has been wrongly deleted

...
..
.
ORA-63999: data file suffered media failure
ORA-01116: error in opening database file 13
ORA-01110: data file 13: '/u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
...
.
.

Do not offline the deleted file in database until recovering datafile, otherwise file descriptor ( FD) of the deleted file will be lost and unavailable for the rest steps.

Check which OS process still have File Descriptor open for the deleted file

[oracle@oemnode1 trace]$ lsof |grep hr.dbf
ora_dbw0_ 9885 oracle 272uW REG 252,0 104865792 235862719 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf (deleted)
...
..
.

We can see the file descriptor is 272 by Oracle db writer background process 9885.

Recover datafile from file descriptor

[oracle@oemnode1 proc]$ cd /proc/9885/fd

[oracle@oemnode1 fd]$ ls -ltr 272
lrwx------ 1 oracle oinstall 64 Aug 5 22:33 272 -> /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf (deleted)

[oracle@oemnode1 fd]$ cat 272 > /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf

[oracle@oemnode1 fd]$ ls -ltr /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
-rw-r--r-- 1 oracle oinstall 104865792 Aug 5 22:38 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
SQL> alter database datafile 13 offline;

Database altered.

SQL> recover datafile 13;

Media recovery complete.

SQL> alter database datafile 13 online;

Database altered.

Check and confirm datafile is restored and Recovered successfully

[oracle@oemnode1 fd]$ lsof |grep hr.dbf
oracle_46 4606 oracle 269u REG 252,0 104865792 235862728 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
ora_dbw0_ 9885 oracle 272uW REG 252,0 104865792 235862728 /u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf
SQL>select FILE_NAME, TABLESPACE_NAME, STATUS  from dba_data_files where file_id=13

FILE_NAME                                      TABLESPACE_NAME  STATUS
----------------------------------------------- ---------------- -------
/u01/app/oracle/oradata/CUMDB/CUMDBPDB1/hr.dbf     HR            AVAILABLE

Please note this method works only when database is still up running after the datafile accidentally being deleted.

DUPLICATE FROM ACTIVE DATABASE Fails With RMAN-03009 ORA-17628 ORA-19505

While creating a new physical standby database by using RMAN duplicate command, the following errors occur:

RMAN> run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate auxiliary channel standby1 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
...
..
.
RMAN-03009: failure of backup command on prim2 channel 
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

SOLUTION

  1. Check and remove the old standby database data files.
  2. Make sure directories for standby database data files created and accessible.

How to Exclude Tablespaces from RMAN Backup

You can use “CONFIGURE EXCLUDE FOR TABLESPACE” to exclude tablespaces from RMAN  backup command “BACKUP DATABASE”.

To exclude tablespace TEST_TBS from RMAN backup:

$ rman target / catalog rman/passwd@rman

RMAN>CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS;

Tablespace TEST_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete


RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';

RMAN>

To exclude PDB tablespace TEST_TBS  from RMAN backup:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS;

Tablespace RACTESTPDB:TEST_TBS will be excluded from future whole 
database backups. 
new RMAN configuration parameters are successfully stored. 
starting full resync of recovery catalog
full resync complete

RMAN>show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';
CONFIGURE EXCLUDE FOR TABLESPACE 'RACTESTPDB:TEST_TBS';

The following rman backup will excluded the above mentioned tablespaces:

RMAN> BACKUP DATABASE;

The excluded tablespace can be backed up by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option:

RMAN> BACKUP DATABASE NOEXCLUDE;

RMAN> BACKUP TABLESPACE TEST_TBS;

To disable the exclusion for RMAN backups:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS CLEAR;

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS CLEAR;

RMAN> show exclude;

 

ORA-17628 RMAN-03009 duplicate target database for standby from active database

While run RMAN “duplicate target database for standby from active database nofilenamecheck” in 11g database, then got below errors:

...
..
.
set newname for datafile 3 to "/u02/oradata/TESTDBSTY/undotbs01.dbf";
...
..
.
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/TESTDB/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/15/2019 15:53:17
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
...
..
.

The workaround is to setup below two parameters with full path:

db_file_name_convert='/u02/oradata/TESTDB/', '/u02/oradata/TESTDBSTY/'
log_file_name_convert='/u03/oradata/TESTSB/', '/u03/oradata/TESTDBSTY'

The short name like this is not enough in 11g, but in 12c it is fine:

db_file_name_convert='TESTDB', 'TESTDBSTY'
log_file_name_convert='TESTSB','TESTDBSTY'

So it seems a bug for 11g.

ORA-00904: “SYS”.”DBMS_RCVMAN”.”NUM2DISPLAYSIZE”: invalid identifier

SYMPTOM

The following errors occurred from 11.2.0.4 standby databases:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select * from v$rman_backup_job_details;
select * from v$rman_backup_job_details
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "SYS"."DBMS_RCVMAN"."NUM2DISPLAYSIZE": invalid identifier

CAUSE

The problem is that DBMS_RCVMAN and other packages are not initialized when database in MOUNT mode, only in OPEN mode, hence the error when using SYS.DBMS_RCVMAN

dbms_rcvman package is not loaded in memory until database is open or RMAN client is ran explicitly.

SOLUTION

This error is expected because the standby instances are in MOUNT mode and the DBMS_RCVMAN does not get initialized.

Workaround: just make a call to rman before running the query in the Standby database.  This will initialize the DBMS_RCVMAN package.

SQL> select * from v$rman_backup_job_details;
select * from v$rman_backup_job_details
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "SYS"."DBMS_RCVMAN"."NUM2DISPLAYSIZE": invalid identifier


SQL> exit

$ rman target /

RMAN> exit

$ sqlplus / as sysdba

SQL> select * from v$rman_backup_job_details;
...
..
.