在歸檔模式下恢復非關鍵資料檔案，只要有有效的備份檔案，恢復起來還是
不太複雜的。以下是測試的過程：

----歸檔模式下丟失非關鍵資料檔案並恢復：

--準備資訊：

scott@PROD>select table_name,tablespace_name

2 from user_tables;

TABLE_NAME TABLESPACE_NAME

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

DEPT USERS

BONUS USERS

SALGRADE USERS

MYLOADER USERS

HISLOADER USERS

EMP USERS

T1 USERS

DEPT_CHANGE USERS

8 rows selected.

--資料檔案資訊：

sys@PROD>select file_name,file_id,tablespace_name

2 from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME

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

/u01/app/oracle/oradata/PROD/ts_ctl01.dbf 7 TS_CTL

/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf 6 TS_XXF

/u01/app/oracle/oradata/PROD/example01.dbf 5 EXAMPLE

/u01/app/oracle/oradata/PROD/users01.dbf 4 USERS

/u01/app/oracle/oradata/PROD/undotbs01.dbf 3 UNDOTBS1

/u01/app/oracle/oradata/PROD/sysaux01.dbf 2 SYSAUX

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

/u01/app/oracle/oradata/myspace_01.dbf 8 MYSPACE

8 rows selected.

---檢視資料庫的歸檔模式：

sys@PROD>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

---建立測試表：

scott@PROD>create table test(x int,y int);

Table created.

---透過rman備份users表空間：

[oracle@enmo ~]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 13:57:41 2016

connected to target database: PROD (DBID=338469376)

RMAN> backup tablespace users;

Starting backup at 22-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-NOV-16

channel ORA_DISK_1: finished piece 1 at 22-NOV-16

piece handle=/u01/app/backup/db_0prlibih_1_1.rmn tag=TAG20161122T140401 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 22-NOV-16

Starting Control File and SPFILE Autobackup at 22-NOV-16

piece handle=/u01/app/FRA/PROD/autobackup/2016_11_22/o1_mf_s_928591444_d37qyo2v_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 22-NOV-16

#備份完成。

---往測試表中插入測試資料：

scott@PROD>insert into test values(1,10);

1 row created.

scott@PROD>insert into test values(2,20);

1 row created.

scott@PROD>commit;

Commit complete.

scott@PROD>select * from test;

X Y

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

1 10

2 20

#共插入兩條測試資料。

---刪除users表空間的資料檔案：

sys@PROD>!rm /u01/app/oracle/oradata/PROD/users01.dbf

sys@PROD>!ls /u01/app/oracle/oradata/PROD/users01.dbf

ls: /u01/app/oracle/oradata/PROD/users01.dbf: No such file

#已經成功刪除users表空間資料檔案：

---嘗試再往表test中插入兩條測試資料：

scott@PROD>insert into test values(3,30);

insert into test values(3,30)

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 10753

Session ID: 18 Serial number: 23

#資料檔案被刪除，不能完成插入動作。

scott@PROD>conn / as sysdba

Connected.

sys@PROD>conn scott/tiger

Connected.

scott@PROD>

scott@PROD>show user

USER is "SCOTT"

scott@PROD>

scott@PROD>insert into test values(3,30);

insert into test values(3,30)

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

#發現報錯沒有users資料檔案：

---致性關庫並嘗試重新開啟資料庫：

sys@PROD>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@PROD>startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/users01.dbf'

---透過rman備份過載資料檔案4並恢復資料：

--目前資料庫的狀態：

sys@PROD>select status from v$instance;

STATUS

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

MOUNTED

#資料庫已經處於mount狀態。

[oracle@enmo ~]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 14:21:36 2016

connected to target database: PROD (DBID=338469376, not open)

RMAN> restore tablespace users;

Starting restore at 22-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_0prlibih_1_1.rmn

channel ORA_DISK_1: piece handle=/u01/app/backup/db_0prlibih_1_1.rmn tag=TAG20161122T140401

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 22-NOV-16

RMAN> recover tablespace users;

Starting recover at 22-NOV-16

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 22-NOV-16

---嘗試開啟資料庫：

RMAN> alter database open;

database opened

sys@PROD>select status from v$instance;

STATUS

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

OPEN

#恢復已經完成，併成功開啟資料庫：

---嘗試連線到Scott使用者並檢視資料丟失情況：

scott@PROD>conn / as sysdba

Connected.

sys@PROD>conn scott/tiger

Connected.

scott@PROD>

scott@PROD>select * from test;

X Y

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

1 10

2 20

#資料沒有丟失。

歸檔模式下丟失普通資料檔案並恢復

相關文章