Open database with missing datafile and Rman recovery catalog creation

To open a database with missing datafile

$ sqlplus sys as sysdba
Enter password : ********

SQL> startup mount

SQL> alter database datafile '/directory/filename' offline drop;

SQL> alter database open;

SQL> drop tablespace ts_name;

To drop the tablespace with the contents and datafiles

SQL> drop tablespace ts_name including contents and datafiles;

To create RMAN recovery catalog and backup the database with RMAN. 

In Oracle 19c we have to connect to a dba user (say system) in a PDB and create this tablespace and recovery catalog.

SQL> create tablespace rman_catalog datafile '/u01/app/oracle/oradata/RMANDB/rman_catalog.dbf' size 500M autoextend ON extent management local uniform size 100M; 

SQL> CREATE USER rcat identified by rcat default tablespace rman_catalog quota unlimited on rman_catalog; 

SQL> GRANT recovery_catalog_owner TO rcat; 

$ rman target / catalog rcat@orclpdb
Recovery Manager: Release 21.0.0.0.0 - Production on Mon Jul 15 11:33:59 2024
Version 21.3.0.0.0

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

connected to target database: ORCL (DBID=1700090799)
recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog tablespace rman_catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog

full resync complete

RMAN> configure retention policy to recovery window of 2 days;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure channel device type disk format '/u01/app/oracle/backup/Backup%d_DB_%U_%S_%P';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backup/Backup%d_DB_%U_%S_%P';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

For the first time need to take a full database backup. 

Afterwared can take an incremental, full or archivelog backup depending on your backup policy and space available. 

If we try to backup the database in RMAN when it is in NOARCHIVELOG mode we will get the below error RMAN backup database command

RMAN>backup database plus archivelog;

Starting backup at 15-JUL-24
Oracle error from target database:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 15-JUL-24

Starting backup at 15-JUL-24
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/15/2024 12:00:56
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

For this we need to ensure database is in archivelog mode

SQL > select name,log_mode from v$database;
NAME      LOG_MODE
--------- -----------
ORCL      NOARCHIVELOG 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3674207416 bytes
Fixed Size		    9692344 bytes
Variable Size		  822083584 bytes
Database Buffers	 2835349504 bytes
Redo Buffers		    7081984 bytes
Database mounted.
 
SQL> alter database archivelog
Database altered.
 
SQL> select name,log_mode from v$database;

NAME	  LOG_MODE
--------- ------------
ORCL	  ARCHIVELOG

After the database is in ARCHIVELOG mode we can take the backup as follows

RMAN> run{
backup database plus archivelog;
delete noprompt obsolete;
}

Now the backup should not give any errors. At the end of the backup you will get log messages on console as follows

starting full resync of recovery catalog
full resync complete

Starting backup at 15-JUL-24
current log archived
allocated channel: ORA_DISK_1
...
... 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-JUL-24

Starting Control File and SPFILE Autobackup at 15-JUL-24
piece handle=/u01/app/oracle/homes/OraDB21Home5/dbs/c-1700090799-20240715-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15-JUL-24

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
using channel ORA_DISK_1
no obsolete backups found

References 

https://www.techtarget.com/searchoracle/answer/How-to-drop-tablespace-with-missing-datafile https://rajeevjhaoracle.wordpress.com/2013/07/09/step-by-step-rman-recovery-catalog-creation-and-configuration-windows/ https://somireddy.wordpress.com/2013/07/16/rman-cold-backup-and-restore-method-1/

Thought for the day

Do not withhold good from those to whom it is due, 

   when it is in your power to do it. 

Do not say to your neighbor, “Go and come again; 

   tomorrow I will give it,” when you have it with you.

Proverbs 3:27-28