系统表空间数据文件丢失，无备份，无重启，通过句柄恢复

大家好！

昨天和大家讨论了，数据文件丢失，无备份恢复过程， 那如果是系统表空间文件丢失呢？

 

能否用句柄的办法恢复呢？

 

让Aning和大家一起来验证：

 

1.1.  模拟数据文件丢失

1.1.1 查看当前数据文件位置

SQL> set line 200

SQL> col NAME FOR a60

SQL> select file#,tablespace_name,name from v$datafile_header;

 

     FILE# TABLESPACE_NAME                NAME

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

         1 SYSTEM                         /u01/app/oracle/oradata/PROD/disk1/system01.dbf

         2 UNDOTBS                        /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf

         3 SYSAUX                         /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf

         4 EXAMPLE                        /u01/app/oracle/oradata/PROD/disk1/EXAMPLE_01.dbf

         5 INDX                           /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf

         6 TOOLS                          /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf

         7 USERS                          /u01/app/oracle/oradata/PROD/disk1/USERS_01.dbf

         8 OLTP                           /u01/app/oracle/oradata/PROD/disk1/OLTP_01.dbf

         9 ANING                          /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf

 

9 rows selected.

1.1.2 在ANING表空间插入测试数据

SQL> create table aning(id number) tablespace SYSTEM;

Table created.

 

SQL> select * from aning;

no rows selected

 

SQL> insert into aning values(1);

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from aning;

        ID

----------

         1

1.1.3 手动删除ANING01.dbf 模拟数据文件丢失

[oracle@secdb1 ~]$ ll /u01/app/oracle/oradata/PROD/disk1/system01.dbf

-rw-r----- 1 oracle oinstall 94380032 Jan 24 15:04 /u01/app/oracle/oradata/PROD/disk1/system01.dbf

SQL> !rm -f /u01/app/oracle/oradata/PROD/disk1/system01.dbf

 

SQL>

[oracle@secdb1 ~]$ ll /u01/app/oracle/oradata/PROD/disk1/system01.dbf

ls: /u01/app/oracle/oradata/PROD/disk1/system01.dbf: No such file or directory

1.1.4 在ANING表空间插入测试数据，失败

SQL> create table aningt1 tablespace SYSTEM as select * from dba_objects;

create table aningt1 tablespace SYSTEM as select * from dba_objects

                                                       *

ERROR at line 1:

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

1.2.  数据库在丢失时没有重启，通过句柄恢复

1.2.1查看dbw进程pid

[oracle@secdb1 ~]$ ps -ef|grep ora_dbw

oracle    7125     1  0 14:54 ?        00:00:00 ora_dbw0_PROD

oracle    6420  6200  0 15:17 pts/2    00:00:00 grep ora_dbw

1.2.2 根据pid查找句柄

[oracle@secdb1 ~]$ cd /proc/7125/fd

[oracle@secdb1 fd]$ ll

total 0

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 0 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 10 -> /u01/app/oracle/admin/PROD/adump/ora_3918.aud

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 11 -> /dev/zero

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 12 -> /dev/zero

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 13 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_PROD.dat

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 15 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkPROD

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 16 -> /u01/app/oracle/oradata/PROD/disk1/control01.ctl

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 17 -> /u01/app/oracle/oradata/PROD/disk2/control02.ctl

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 18 -> /u01/app/oracle/oradata/PROD/disk3/control03.ctl

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 19 -> /u01/app/oracle/oradata/PROD/disk1/system01.dbf

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 20 -> /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 21 -> /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 22 -> /u01/app/oracle/oradata/PROD/disk1/EXAMPLE_01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 23 -> /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 24 -> /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 25 -> /u01/app/oracle/oradata/PROD/disk1/USERS_01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 26 -> /u01/app/oracle/oradata/PROD/disk1/OLTP_01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 27 -> /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 28 -> /u01/app/oracle/oradata/PROD/disk1/temp01.dbf

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 29 -> /u01/app/oracle/oradata/PROD/disk1/temp1_01.dbf

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 3 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 30 -> /u01/app/oracle/oradata/PROD/disk1/temp2_01.dbf

lr-x------ 1 oracle oinstall 64 Jan 24 15:20 4 -> /dev/null

l-wx------ 1 oracle oinstall 64 Jan 24 15:20 5 -> /u01/app/oracle/admin/PROD/udump/prod_ora_3918.trc

l-wx------ 1 oracle oinstall 64 Jan 24 15:20 6 -> /u01/app/oracle/admin/PROD/bdump/alert_PROD.log

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstPROD (deleted)

l-wx------ 1 oracle oinstall 64 Jan 24 15:20 8 -> /u01/app/oracle/admin/PROD/bdump/alert_PROD.log

lrwx------ 1 oracle oinstall 64 Jan 24 15:20 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_PROD.dat

1.2.3 用句柄还原丢失的数据文件

[oracle@secdb1 fd]$ cp 27 /u01/app/oracle/oradata/PROD/disk1/system01.dbf

[oracle@secdb1 fd]$ ll /u01/app/oracle/oradata/PROD/disk1/system01.dbf

-rw-r----- 1 oracle oinstall 94380032 Jan 24 15:23 /u01/app/oracle/oradata/PROD/disk1/system01.dbf

 

1.3 查询数据文件恢复状态

SQL> select file#,tablespace_name,status from v$datafile_header;

 

     FILE# TABLESPACE_NAME                STATUS

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

         1 SYSTEM                         ONLINE

         2 UNDOTBS                        ONLINE

         3 SYSAUX                         ONLINE

         4 EXAMPLE                        ONLINE

         5 INDX                           ONLINE

         6 TOOLS                          ONLINE

         7 USERS                          ONLINE

         8 OLTP                           ONLINE

         9 ANING                          ONLINE

 

9 rows selected.

1.4 在ANING表空间插入测试数据

SQL> create table aningrestore(id number) tablespace system;

Table created.

 

SQL> insert into aningrestore values(2);

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> create table aningt tablespace system as select * from dba_objects;

Table created.

 

 

1.3.  重启数据库 

这里需要恢复system表空间的数据文件， 由于系统表空间不能offline，所以只能重启数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'

 

 

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

 

Database altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12457158/viewspace-753330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12457158/viewspace-753330/