Physical removal of Oracle data file Recovery

Test under Archive mode:

A. Open state physical Delete data file, shutdown condition Recovery:

sql> Create tablespace Test datafile ' +data/mecbs/datafile/test01.dbf ' size 10M;

Tablespace created.

Sql> Conn Scott/scott;

Connected.

Sql> Conn/as SYSDBA

Connected.

Sql> create user test identified by test default tablespace test;

User created.

Sql> Grant Connect,resource to test;

Grant succeeded.

Sql> Conn Test/test;

Connected.

Sql> Conn/as SYSDBA

Connected.

Sql> Grant DBA to test;

Grant succeeded.

Sql> Conn Test/test

Connected.

Sql> CREATE TABLE T1 as select * from Dba_objects where rownum<=1000;

Table created.

Sql> select Table_name,tablespace_name from User_tables;

TABLE_NAME Tablespace_name

------------------------------ ------------------------------

T1 TEST

Sql> select file_name from Dba_data_files;

file_name

--------------------------------------------------------------------------------

+data/mecbs/datafile/users.259.862339391

+data/mecbs/datafile/undotbs1.258.862339391

+data/mecbs/datafile/sysaux.257.862339391

+data/mecbs/datafile/system.256.862339387

+data/mecbs/datafile/example.264.862339751

+data/mecbs/datafile/undotbs2.265.862341013

+data/mecbs/datafile/system01.dbf

+data/mecbs/datafile/crm01.dbf

+data/mecbs/datafile/test01.dbf

+data/mecbs/datafile/cross.dbf

+data/mecbs/datafile/aix_trans.dbf

One by one rows selected.

Asmcmd [+data/mecbs/datafile] > ls

aix_trans.281.868377837

crm.276.863565267

crosstbs.279.868372675

example.264.862339751

sysaux.257.862339391

system.256.862339387

system.275.863564943

test.278.868380831

UNDOTBS1.258.862339391

UNDOTBS2.265.862341013

users.259.862339391

Aix_trans.dbf

Crm01.dbf

Cross.dbf

System01.dbf

Test01.dbf

sql> alter tablespace test offline;

Tablespace altered.

Asmcmd [+data/mecbs/datafile] > RM-RF test01.dbf

Asmcmd [+data/mecbs/datafile] >

Sql> select Name,status from V$datafile;

NAME STATUS

------------------------------------------------------------ -------

+data/mecbs/datafile/system.256.862339387 System

+data/mecbs/datafile/sysaux.257.862339391 ONLINE

+data/mecbs/datafile/undotbs1.258.862339391 ONLINE

+data/mecbs/datafile/users.259.862339391 ONLINE

+data/mecbs/datafile/example.264.862339751 ONLINE

+data/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/MECBS/DATAFILE/SYSTEM01.DBF SYSTEM

+DATA/MECBS/DATAFILE/CRM01.DBF ONLINE

+DATA/MECBS/DATAFILE/TEST01.DBF OFFLINE

+DATA/MECBS/DATAFILE/CROSS.DBF ONLINE

+DATA/MECBS/DATAFILE/AIX_TRANS.DBF ONLINE

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sql> startup Mount;

ORACLE instance started.

Total System Global area 484356096 bytes

Fixed Size 2254464 bytes

Variable Size 264243584 bytes

Database buffers 209715200 bytes

Redo buffers 8142848 bytes

Database mounted.

sql> ALTER DATABASE Create datafile ' +data/mecbs/datafile/test01.dbf ';

Database altered.

sql> recover datafile ' +data/mecbs/datafile/test01.dbf ';

Media recovery complete.

sql> ALTER DATABASE open;

Database altered.

Sql> select Name,status from V$datafile;

NAME STATUS

------------------------------------------------------------ -------

+data/mecbs/datafile/system.256.862339387 System

+data/mecbs/datafile/sysaux.257.862339391 ONLINE

+data/mecbs/datafile/undotbs1.258.862339391 ONLINE

+data/mecbs/datafile/users.259.862339391 ONLINE

+data/mecbs/datafile/example.264.862339751 ONLINE

+data/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/MECBS/DATAFILE/SYSTEM01.DBF SYSTEM

+DATA/MECBS/DATAFILE/CRM01.DBF ONLINE

+DATA/MECBS/DATAFILE/TEST01.DBF OFFLINE

+DATA/MECBS/DATAFILE/CROSS.DBF ONLINE

+DATA/MECBS/DATAFILE/AIX_TRANS.DBF ONLINE

One by one rows selected.

sql> alter tablespace test online;

Tablespace altered.

Sql> select Name,status from V$datafile;

NAME STATUS

------------------------------------------------------------ -------

+data/mecbs/datafile/system.256.862339387 System

+data/mecbs/datafile/sysaux.257.862339391 ONLINE

+data/mecbs/datafile/undotbs1.258.862339391 ONLINE

+data/mecbs/datafile/users.259.862339391 ONLINE

+data/mecbs/datafile/example.264.862339751 ONLINE

+data/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/MECBS/DATAFILE/SYSTEM01.DBF SYSTEM

+DATA/MECBS/DATAFILE/CRM01.DBF ONLINE

+DATA/MECBS/DATAFILE/TEST01.DBF ONLINE

+DATA/MECBS/DATAFILE/CROSS.DBF ONLINE

+DATA/MECBS/DATAFILE/AIX_TRANS.DBF ONLINE

One by one rows selected.

Sql> Conn Test/test;

Connected.

Sql> Select COUNT (*) from T1;

COUNT (*)

----------

1000

Physical removal of Oracle data file Recovery