程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle讀寫-只讀表空間回復詳解

Oracle讀寫-只讀表空間回復詳解

編輯:Oracle教程

Oracle讀寫-只讀表空間回復詳解


(一) 使用備份時的控制文件進行恢復,如下圖,即使用備份位置的控制文件進行恢復

\

1) 創建測試表空間ts1及相關測試表(表空間為ts1)

SYS@ORCL>create tablespace ts1 datafile '/u01/app/oracle/oradata/ORCL/ts1.dbf' size 10m;

Tablespace created.

SYS@ORCL>create table scott.t(x int) tablespace ts1;

Table created.

SYS@ORCL>insert into scott.t select rownum from dual connect by rownum<=10;

10 rows created.

SYS@ORCL>commit;

Commit complete. 

SYS@ORCL>

2) 通過RMAN備份ts1表空間和控制文件

RMAN> backup tablespace ts1 include current controlfile; 

Starting backup at 17-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=141 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/ts1.dbf

channel ORA_DISK_1: starting piece 1 at 17-JAN-13

channel ORA_DISK_1: finished piece 1 at 17-JAN-13

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp tag=TAG20130117T101646 comment=NONE

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

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 17-JAN-13

channel ORA_DISK_1: finished piece 1 at 17-JAN-13

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp tag=TAG20130117T101646 comment=NONE

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

Finished backup at 17-JAN-13

RMAN>

3) 刪除t表部分記錄

SYS@ORCL>delete scott.t where x>=6;

5 rows deleted. 

SYS@ORCL>commit; 

Commit complete. 

SYS@ORCL>

4) 切換幾次日志,讓上面的動作歸檔(對於測試來說,意義不是特別的大)

SYS@ORCL>alter system switch logfile; 

System altered. 

SYS@ORCL>/ 

System altered. 

SYS@ORCL>/ 

System altered. 

SYS@ORCL>/ 

System altered.

SYS@ORCL>

5) 在users表空間上創建t1表

SYS@ORCL>create table scott.t1(x int) tablespace users;

Table created. 

SYS@ORCL>insert into scott.t1 select rownum from dual connect by rownum<=2; 

2 rows created. 

SYS@ORCL>commit;

Commit complete. 

SYS@ORCL>

6) 將表ts1修改為只讀模式

SYS@ORCL>alter tablespace ts1 read only; 

Tablespace altered. 

SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS1'; 

TABLESPACE_NAME STATUS

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

TS1 READ ONLY 

SYS@ORCL>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS1';

FILE_ID FILE_NAME STATUS ONLINE_

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

6 /u01/app/oracle/oradata/ORCL/ts1.dbf AVAILABLE ONLINE

SYS@ORCL>

7) 刪除ts1表空間數據文件和所有控制文件

SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts1.dbf

SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/*.ctl

8) 重啟數據庫到nomount狀態

SYS@ORCL>shutdown abort;

ORACLE instance shut down.

SYS@ORCL>

SYS@ORCL>startup nomount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

SYS@ORCL>

9) 通過RMAN備份的控制文件完成控制文件的恢復,恢復完成,數據庫修改為mount

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp';

Starting restore at 17-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/u01/app/oracle/oradata/ORCL/control01.ctl

output filename=/u01/app/oracle/oradata/ORCL/control02.ctl

output filename=/u01/app/oracle/oradata/ORCL/control03.ctl

Finished restore at 17-JAN-13 

RMAN> alter database mount; 

database mounted

released channel: ORA_DISK_1 

RMAN>

10) 恢復ts1表空間

RMAN> restore tablespace ts1; 

Starting restore at 17-JAN-13

Starting implicit crosscheck backup at 17-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 17-JAN-13 

Starting implicit crosscheck copy at 17-JAN-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 17-JAN-13 

searching for all files in the recovery area

cataloging files...

cataloging done 

List of Cataloged Files

=======================

File Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_ncnnf_TAG20130117T101646_8hgqrhvd_.bkp

using channel ORA_DISK_1 

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/ts1.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp

channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T101646_8hgqrg9h_.bkp tag=TAG20130117T101646

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

Finished restore at 17-JAN-13

RMAN>

11) 表空間恢復完畢,嘗試open數據庫

--當然,我們知道一定是無法open數據庫的,因為控制文件恢復的是老版本的

SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts1.dbf

/u01/app/oracle/oradata/ORCL/ts1.dbf

SYS@ORCL>alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--提示resetlogs方式打開

SYS@ORCL>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

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

--提示需要恢復1號數據文件

SYS@ORCL>recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

--提示using BACKUP CONTROLFILE方式完成恢復

SYS@ORCL>

12) using backup controlfile恢復數據庫

注:如果要恢復到控制文件SCN以後的時間。這時候,就需要用using backup controlfile. 恢復就不會受“當前controlfile所紀錄的SCN”的限制。

這時候的限制就來自於你的語句(until time , until scn),或者可用的archive log(until cancel)

SYS@ORCL>recover database using backup controlfile until cancel;

ORA-00279: change 493760 generated at 01/16/2013 17:10:46 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_16/o1_mf_1_7_%u_.arc

ORA-00280: change 493760 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

--此處回車則是利用下一個歸檔日志文件進行恢復。不過在這裡也可以輸入CANCEL,表示恢復到此結束,不再使用後面的歸檔日志文件

……

--在恢復的過程中又出現了如下警告

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

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

SYS@ORCL>

13) 嘗試再次resetlogs打開數據庫

SYS@ORCL>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

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

SYS@ORCL>

14) 采用極端辦法

注:此時只能采取極端手段:隱含參數_allow_resetlogs_corruption強制啟動數據庫,設置此參數之後,在數據庫Open過程中,Oracle會跳過某些一致性檢查,從而使數據庫可能跳過不一致狀態,Open打開:

--查看隱藏參數_allow_resetlogs_corruption,默認值為false

SYS@ORCL>SELECT ksppstvl, ksppdesc

2 FROM x$ksppi x, x$ksppcv y

3 WHERE x.indx = y.indx

4 AND ksppinm = '_allow_resetlogs_corruption';

KSPPSTVL KSPPDESC

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

FALSE allow resetlogs even if it will cause corruption

SYS@ORCL>

--將隱藏參數設置為true

SYS@ORCL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SYS@ORCL>

--由於是靜態參數,重啟數據庫使其生效

SYS@ORCL>shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@ORCL>

--數據庫啟動到mount狀態

SYS@ORCL>startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 83887696 bytes

Database Buffers 197132288 bytes

Redo Buffers 2973696 bytes

Database mounted.

SYS@ORCL>

15) 再次嘗試open數據庫,提示RESETLOGS方式open

SYS@ORCL>alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ORCL>

16) 數據庫成功以resetlogs方式open

SYS@ORCL>alter database open resetlogs;

Database altered. 

SYS@ORCL>

SYS@ORCL>select count(*) from scott.t; 

COUNT(*)

----------

SYS@ORCL>select count(*) from scott.t1;

select count(*) from scott.t1

*

ERROR at line 1:

ORA-00942: table or view does not exist 

SYS@ORCL>

17) 恢復參數,重啟數據庫

SYS@ORCL>alter system set "_allow_resetlogs_corruption"=false scope=spfile;

System altered. 

SYS@ORCL>startup force;

ORACLE instance started. 

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 88082000 bytes

Database Buffers 192937984 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS@ORCL>

5.1.1. 小結

如果在生產中使用了只讀表空間,那麼切記,在修改為只讀以後,第一件事兒就是備份,這樣會省去不必要的麻煩和損失。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved