分享
 
 
 

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

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

热备份的时候,只读表空间不需要执行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"(出处:清风软件下载学院)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有