Monday, August 15, 2016

cloning using cold backup

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.