Drop datafile without dropping a tablespace in Oracle 11g

alter-databasedatafileoracle-11g-r2rmantablespaces

I was trying to take backup of database in Oracle 11g by using RMAN

      $ rman target /
    Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 11 10:14:37 2015

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

    connected to target database: ORCL (DBID=1415405400)

RMAN> backup incremental level 0 as compressed backupset database;

Starting backup at 11-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/11/2015 10:16:58
RMAN-06056: could not access datafile 5

I have misplaced the datafile then I have added that datafile executing a touch command but still I was getting an error that the database is unable to lock the file. I put the database in mount mode then I took that file offline and after I started backup there was the same error. So how can I solve this error without getting tablespace deleted

Best Answer

The Database Backup and Recovery Reference says that you can do a backup by

backup incremental level 0 as compressed backupset database skip inaccessible;

or better

backup incremental level 0 as compressed backupset database skip offline;

But your problem is that the data from datafile 5 is lot if do do not have a valid backup.

If you have a valid backup vou can restore datafile 5.

If you don't have a valid backup you have two option:

you drop the tablespace and everything from this tablespace will be removed forever
export as much as possible , drop and recreate the tablespace and import what you have exported before.

"export as much as possible" means:

you can export the definitions of the objects from this tablespace because these definitions reside in the SYSTEM tablespace.
if the tablespace consists of more than one datafile you can export the data from all remaining datafiles of this taplespace.

These export can be done with the expdp datapump tool. Use the TABLESPACES parameter to specify the name of the tablespace you want to export. If you only want to export the definitions of the objects set CONTENT=METADATA_ONLY

The import can be done with the impdp tool.

Related Solutions

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea. You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?

Will I have to restore controlfile in RMAN level 1 backupset before recover database

The error might be a bit misleading. It is correct to restore the control file from the latest backups assuming you want to recover to as late in time as possible. If your control_file_record_keep_time is long enough to also contain the backup pieces of the level 0 backup, rman will start restoring the level 0 backup and continue with the level 1 backup. If your level 0 backup is not visible in the restored control file you need to do the catalog so the info gets loaded into the control file.

The problem is with the ending of the recovery because the database keeps requesting archives until it sees the end of redo marker. This means that there was a clean shutdown. If everything is OK, you also have the archive log files, in backup or in the archive destination.

To get this database open after restoring the level 0 backup, you have to prepare the restore by setting until what moment the recovery has to be done. Without this setting the recovery will try to recover almost forever.

set until can be done until time or until sequence or until scn. It is my guess that "

Apr 13 21:06 inr01_0413_1_dbf_9fr2v0t0_1_1_RAC_20160413_42287

" is a backup of log sequence 42287 so your recovery would succeed if you specify

set until sequence 42287 {
restore database;
recover database;
}

Best Answer

Related Solutions

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

Will I have to restore controlfile in RMAN level 1 backupset before recover database

Related Question