In the below scenario , i'm creating clone database using cold backup , and forgot to mention undo datafile in the create controlfile script.
SQL> @createctl.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
RECOVER
found that one datafile needs recovery
SQL> select name from v$datafile where status='RECOVER';
NAME
--------------------------------------------------------------------------------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379 ( created the dummy file in default location ; which $ORACLE_HOME/dbs)
SQL> select * from dba_data_files where file_name like '%MISS%';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379
379 APPS_UNDOTS1 AVAILABLE
379
RECOVER
(confirmed that the datafile belongs to undotablespace)
SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;
Tablespace dropped.
(since i have modified undo_management parameter to manual in pfile before starting database; this is being accepted)
(Also , undo data might be required in some case for rollback /roll forward transactions in cold backup and recovery scenario too)
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
SQL> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '/d01/ora_vis/data/undo01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2G;
Tablespace created.
shu immediate
change params
undo_management to auto
undo_tablespace to apps_undots1
startup
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
VIS READ WRITE
PS : So don't forget to include your undo datafile in create controlfile script while cloning using cold or hot backup.
This use-case applies to recover of database if you loss undo datafiles after clean shutdown of database.
SQL> @createctl.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
RECOVER
found that one datafile needs recovery
SQL> select name from v$datafile where status='RECOVER';
NAME
--------------------------------------------------------------------------------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379 ( created the dummy file in default location ; which $ORACLE_HOME/dbs)
SQL> select * from dba_data_files where file_name like '%MISS%';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379
379 APPS_UNDOTS1 AVAILABLE
379
RECOVER
(confirmed that the datafile belongs to undotablespace)
SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;
Tablespace dropped.
(since i have modified undo_management parameter to manual in pfile before starting database; this is being accepted)
(Also , undo data might be required in some case for rollback /roll forward transactions in cold backup and recovery scenario too)
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
SQL> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '/d01/ora_vis/data/undo01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2G;
Tablespace created.
shu immediate
change params
undo_management to auto
undo_tablespace to apps_undots1
startup
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
VIS READ WRITE
PS : So don't forget to include your undo datafile in create controlfile script while cloning using cold or hot backup.
This use-case applies to recover of database if you loss undo datafiles after clean shutdown of database.