物理删除oracle数据文件的恢复
归档模式下测试：(普通文件系统)：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'>
Tablespace created.SQL> create user 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 select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 TEST
SQL> conn /as sysdba
Connected.
SQL>>
System> 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.
# ls
control01.ctlredo01.logredo03.log system01.dbftest01.dbf users01.dbf
example01.dbfredo02.logsysaux01.dbftemp01.dbf undotbs01.dbf
# 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
$ ps -ef | grep dbw0
oracle 2898 10 09:24 ? 00:00:00 ora_dbw0_CPP
oracle 542353823 09:50 pts/2 00:00:00 grep dbw0
$ su - root
Password:
# cd /proc/2898/
# ls
attr clear_refs cwd fdinfo maps mountstatsoom_score root smapsstatus
autogroupcmdline environio mem net oom_score_adjsched stacksyscall
auxv coredump_filterexe limits mountinfonuma_maps pagemap schedstatstat task
cgroup cpuset fd loginuidmounts oom_adj personality sessionidstatmwchan
# cd fd
# ls
01101122562572582592602612622632643456789
# ll
total 0
lr-x------ 1 oracle oinstall 64 Jan7 09:51 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan7 09:51 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan7 09:51 10 -> /u01/oracle/product/11.2.0/db_1/dbs/lkCPP
lr-x------ 1 oracle oinstall 64 Jan7 09:51 11 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Jan7 09:51 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan7 09:51 256 -> /u01/oracle/oradata/CPP/control01.ctl
lrwx------ 1 oracle oinstall 64 Jan7 09:51 257 -> /u01/oracle/fast_recovery_area/CPP/control02.ctl
lrwx------ 1 oracle oinstall 64 Jan7 09:51 258 -> /u01/oracle/oradata/CPP/system01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 259 -> /u01/oracle/oradata/CPP/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 260 -> /u01/oracle/oradata/CPP/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 261 -> /u01/oracle/oradata/CPP/users01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 262 -> /u01/oracle/oradata/CPP/example01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 263 -> /u01/oracle/oradata/CPP/temp01.dbf
lrwx------ 1 oracle oinstall 64 Jan7 09:51 264 -> /u01/oracle/oradata/CPP/test01.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Jan7 09:51 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan7 09:51 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan7 09:51 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan7 09:51 6 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Jan7 09:51 7 -> /proc/2898/fd
lr-x------ 1 oracle oinstall 64 Jan7 09:51 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Jan7 09:51 9 -> /u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat
# 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>>
Database> 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>>
Database> 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'> Tablespace created.
SQL> conn /as sysdba
Connected.
SQL> create user 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 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>>
Tablespace> 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 Area484356096 bytes
Fixed>
Variable> Database Buffers209715200 bytes
Redo Buffers 8142848 bytes
Database mounted.
SQL>>
Database> SQL> recover datafile '+DATA/mecbs/datafile/test01.dbf';
Media recovery complete.
SQL>>
Database> 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>>
Tablespace> 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
页:
[1]