只读表空间的备份和恢复[一]
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">（出处：清风软件下载学院）
