只讀表空間的備份和恢復[一]
2024-07-21 02:34:17
供稿：網友
只讀表空間的備份和恢復與普通表空間稍有不同。 熱備份的時候，只讀表空間不需要執行begin backup.直接使用操作系統命令備份就可以。一旦表空間變為read-only,Oracle就不會再寫對應的數據文件。 如下 對只讀表空間執行begin backup,提示錯誤信息 alter tablespace mgn_readonly_Test read only;
SQL> alter tablespace mgn_readonly_Test begin backup;
alter tablespace mgn_readonly_Test begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'MGN_READONLY_TEST' 只讀表空間恢復 當對數據庫進行完全恢復的時候，只需要將READONLY表空間數據文件拷貝回去即可。 當對數據庫進行不完全恢復的時候，oracle8i和oracle9i處理方式不同。 8i方式：當使用備份控制文件進行數據庫不完全恢復的時候， 1)需要將所有只讀表空間的數據文件offline； 2)執行recover database using backup contrlfile until … 3)將offline的只讀表空間數據文件online; 4)然后resetlogs 打開數據庫。 假如沒有將只讀數據文件offline,進行不完全恢復的時候會提示ORA-01233錯誤.ORA-01233:
file string is read only - cannot recover using backup controlfile
Cause:
An attempt to do media recovery using a backup control file found that one of the files is marked read only. Read-only files do not normally need to be recovered, but recovery with a backup control file must recover all online files.
Action:
If the file really is read only, take it offline before the recovery, and bring the read-only tablespace online after the database is open. If the file does need recovery use a control file from the time the file was read-write. If the correct control file is not available, use CREATE CONTROLFILE to make one. 9i方式 當使用備份控制文件進行數據庫不完全恢復的時候，不需要offline只讀表空間的數據文件。直接recover就可以。 全文為9i有只讀表空間不完全恢復的示例SQL> create tablespace readonly
2 datafile 'C:oracleoradataPRacticereadonly01.dbf' size 2M
3 uniform size 128k;Tablespace created.SQL> alter tablespace readonly read only;Tablespace altered.SQL> connect /as sysdba;
Connected.SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:oracleoradataarchivepractice
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> alter system switch logfile;System altered.SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:oracleoradataarchivepractice
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3SQL> col name for a55
SQL> select name,status,enabled from v$datafile;NAME STATUS ENABLED
------------------------------------------------------- ------- ----------
C:ORACLEORADATAPRACTICESYSTEM01.DBF SYSTEM READ WRITE
C:ORACLEORADATAPRACTICEUNDOTBS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICECWMLITE01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEDRSYS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEEXAMPLE01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEINDX01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEODM01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICETOOLS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEUSERS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEXDB01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEREADONLY01.DBF ONLINE READ ONLY
11 rows selected.SQL> create table t (x number)
2 tablespace users;SQL> insert into t values(200);SQL> commit;SQL> alter system switch logfile;SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:oracleoradataarchivepractice
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> alter system switch logfile;SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:oracleoradataarchivepractice
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> shutdown abort
ORACLE instance shut down.NOTE: I HAVE REPLACED THE CURRENT CONTROL FILES WITH BACKUP ONES.SQL> startup
ORACLE instance started.Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:ORACLEORADATAPRACTICESYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfileSQL> select name,open_mode from v$database;NAME OPEN_MODE
------------------------------------------------------- ----------
PRACTICE MOUNTEDSQL> col error for a10
SQL> select * from v$recover_file;FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
1 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR2 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR3 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR4 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERRORFILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------5 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR6 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR7 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR8 ONLINE ONLINE UNKNOWN 203235 05-JUN-03FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
ERROR9 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR10 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERRORSQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:ORACLEORADATAPRACTICESYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile
SQL> recover database using backup controlfile; <---- IT DID NOT SAY ora 01233 THE FILE IS READ ONLY .....
ORA-00279: change 202309 generated at 06/05/2003 15:11:47 needed for thread 1
ORA-00289: suggestion : C:ORACLEORADATAARCHIVEPRACTICEARCH_3.ARC
ORA-00280: change 202309 for thread 1 is in sequence #3
Specify log: {<RET>=suggested filename AUTO CANCEL}
ORA-00279: change 203233 generated at 06/05/2003 15:37:53 needed for thread 1
ORA-00289: suggestion : C:ORACLEORADATAARCHIVEPRACTICEARCH_4.ARC
ORA-00280: change 203233 for thread 1 is in sequence #4
ORA-00278: log file 'C:ORACLEORADATAARCHIVEPRACTICEARCH_3.ARC' no longer
needed for this recovery
Specify log: {<RET>=suggested filename AUTO CANCEL}ORA-00279: change 203235 generated at 06/05/2003 15:37:55 needed for thread 1
ORA-00289: suggestion : C:ORACLEORADATAARCHIVEPRACTICEARCH_5.ARC
ORA-00280: change 203235 for thread 1 is in sequence #5
ORA-00278: log file 'C:ORACLEORADATAARCHIVEPRACTICEARCH_4.ARC' no longer
needed for this recovery
Specify log: {<RET>=suggested filename AUTO CANCEL}ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:ORACLEORADATAARCHIVEPRACTICEARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6
ORA-00278: log file 'C:ORACLEORADATAARCHIVEPRACTICEARCH_5.ARC' no longer
needed for this recovery
Specify log: {<RET>=suggested filename AUTO CANCEL}ORA-00308: cannot open archived log
'C:ORACLEORADATAARCHIVEPRACTICEARCH_6.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.SQL> select * from v$logfile;GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
C:ORACLEORADATAPRACTICEREDO03.LOG2 ONLINE
C:ORACLEORADATAPRACTICEREDO02.LOG1 ONLINE
C:ORACLEORADATAPRACTICEREDO01.LOG
SQL> recover database using backup controlfile;
ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:ORACLEORADATAARCHIVEPRACTICEARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6
Specify log: {<RET>=suggested filename AUTO CANCEL}C:ORACLEORADATAPRACTICEREDO02.LOG <---------- CURRENT REDOLOG FILE PATHLog applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;Database altered.SQL> select * from t;X
----------
200SQL> col name for a55
SQL> select name,status,enabled from v$datafile;NAME STATUS ENABLED
------------------------------------------------------- ------- ----------
C:ORACLEORADATAPRACTICESYSTEM01.DBF SYSTEM READ WRITE
C:ORACLEORADATAPRACTICEUNDOTBS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICECWMLITE01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEDRSYS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEEXAMPLE01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEINDX01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEODM01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICETOOLS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEUSERS01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEXDB01.DBF ONLINE READ WRITE
C:ORACLEORADATAPRACTICEREADONLY01.DBF ONLINE READ ONLY right">（出處：清風軟件下載學院）
