只读表空间的备份和恢复[一]_数据库

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

发表于：2007-06-22来源：作者：点击数：标签：

只读表空间的备份和恢复与普通表空间稍有不同。热备份的时候，只读表空间不需要执行begin backup.直接使用操作系统命令备份就可以。一旦表空间变为read-only, Oracle 就不会再写对应的数据文件。如下对只读表空间执行begin backup,提示错误信息 alter tab

　　只读表空间的备份和恢复与普通表空间稍有不同。

　　热备份的时候，只读表空间不需要执行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;

原文转自：http://www.ltesting.net

软件测试 > 测试开发技术 > 软件测试开发语言 > 数据库 >