只读表空间的备份和恢复与普通表空间稍有不同。
热备份的时候，只读表空间不需要执行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 3
SQL> 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 controlfile
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
------------------------------------------------------- ----------
PRACTICE MOUNTED
SQL> col error for a10
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
1 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
2 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
3 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
4 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
5 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
6 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
7 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
8 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
ERROR
9 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
10 ONLINE ONLINE UNKNOWN 203235 05-JUN-03
ERROR
SQL> 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.LOG
2 ONLINE
C:ORACLEORADATAPRACTICEREDO02.LOG
1 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 PATH
Log 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
----------
200
SQL> 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">（出处：清风软件下载学院）
|
