归档模式下测试:
(普通文件系统):open状态下物理删除数据文件,未关闭情况恢复:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/CPP/example01.dbf
/u01/oracle/oradata/CPP/users01.dbf
/u01/oracle/oradata/CPP/undotbs01.dbf
/u01/oracle/oradata/CPP/sysaux01.dbf
/u01/oracle/oradata/CPP/system01.dbf
SQL> create tablespace test datafile '/u01/oracle/oradata/CPP/test01.dbf' size 10M;
Tablespace created.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> 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> conn /as sysdba
Connected.
SQL> alter system checkpoint;
System altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/CPP/example01.dbf
/u01/oracle/oradata/CPP/users01.dbf
/u01/oracle/oradata/CPP/undotbs01.dbf
/u01/oracle/oradata/CPP/sysaux01.dbf
/u01/oracle/oradata/CPP/system01.dbf
/u01/oracle/oradata/CPP/test01.dbf
6 rows selected.
[root@orcl1 CPP]# ls
control01.ctl redo01.log redo03.log system01.dbf test01.dbf users01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
[root@orcl1 CPP]# rm -rf test01.dbf
SQL> create table t2 as select * from t1;
create table t2 as select * from t1 *
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/oracle/oradata/CPP/test01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@orcl1 ~]$ ps -ef | grep dbw0
oracle 2898 1 0 09:24 ? 00:00:00 ora_dbw0_CPP
oracle 5423 5382 3 09:50 pts/2 00:00:00 grep dbw0
[oracle@orcl1 ~]$ su - root
Password:
[root@orcl1 ~]# cd /proc/2898/
[root@orcl1 2898]# ls
attr clear_refs cwd fdinfo maps mountstats oom_score root smaps status
autogroup cmdline environ io mem net oom_score_adj sched stack syscall
auxv coredump_filter exe limits mountinfo numa_maps pagemap schedstat stat task
cgroup cpuset fd loginuid mounts oom_adj personality sessionid statm wchan
[root@orcl1 2898]# cd fd
[root@orcl1 fd]# ls
0 1 10 11 2 256 257 258 259 260 261 262 263 264 3 4 5 6 7 8 9
[root@orcl1 fd]# ll
total 0
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 7 09:51 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 10 -> /u01/oracle/product/11.2.0/db_1/dbs/lkCPP
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 11 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Jan 7 09:51 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 256 -> /u01/oracle/oradata/CPP/control01.ctl
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 257 -> /u01/oracle/fast_recovery_area/CPP/control02.ctl
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 258 -> /u01/oracle/oradata/CPP/system01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 259 -> /u01/oracle/oradata/CPP/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 260 -> /u01/oracle/oradata/CPP/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 261 -> /u01/oracle/oradata/CPP/users01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 262 -> /u01/oracle/oradata/CPP/example01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 263 -> /u01/oracle/oradata/CPP/temp01.dbf
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 264 -> /u01/oracle/oradata/CPP/test01.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 6 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 7 -> /proc/2898/fd
lr-x------ 1 oracle oinstall 64 Jan 7 09:51 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Jan 7 09:51 9 -> /u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat
[oracle@orcl1 fd]# cp 264 /u01/oracle/oradata/CPP/test01.dbf
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
/u01/oracle/oradata/CPP/system01.dbf SYSTEM
/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE
/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE
/u01/oracle/oradata/CPP/users01.dbf ONLINE
/u01/oracle/oradata/CPP/example01.dbf ONLINE
/u01/oracle/oradata/CPP/test01.dbf ONLINE
6 rows selected.
SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' offline;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
/u01/oracle/oradata/CPP/system01.dbf SYSTEM
/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE
/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE
/u01/oracle/oradata/CPP/users01.dbf ONLINE
/u01/oracle/oradata/CPP/example01.dbf ONLINE
/u01/oracle/oradata/CPP/test01.dbf RECOVER
6 rows selected.
SQL> recover datafile '/u01/oracle/oradata/CPP/test01.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' online;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
/u01/oracle/oradata/CPP/system01.dbf SYSTEM
/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE
/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE
/u01/oracle/oradata/CPP/users01.dbf ONLINE
/u01/oracle/oradata/CPP/example01.dbf ONLINE
/u01/oracle/oradata/CPP/test01.dbf ONLINE
6 rows selected.
SQL> conn test/test;
Connected.
SQL> create table t2 as select * from t1;
Table created.
(ASM文件系统)open状态下物理删除数据文件,关闭情况恢复:
SQL> create tablespace test datafile '+DATA/mecbs/datafile/test01.dbf' size 10M;
Tablespace created.
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
转载于:https://blog.51cto.com/7642644/1600357