只读表空间的备份和恢复[一]

　　只读表空间的备份和恢复与普通表空间稍有不同。　　热备份的时候，只读表空间不需要执行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">（出处：清风软件下载学院）