归档模式下测试:
一.open状态下物理删除数据文件,关闭情况恢复:
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
11 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
11 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
11 rows selected.
SQL> conn test/test;
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
1000
原文:http://7642644.blog.51cto.com/7632644/1600357