系統表空間檔案丟失無備份用控制程式碼的辦法恢復

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;