plusread.php,【只读表空间--恢复-1】

于 2021-03-12 08:40:36 发布
阅读量58 收藏
点赞数
文章标签： plusread.php
本文详细讲述了在Oracle环境中，针对不同类型的故障（只读、只读可写、可写只读），如何通过RMAN进行数据文件备份、还原和恢复的过程，包括检查备份文件、切换表空间权限、脱机/在线数据文件，以及使用`recover`和`restore`命令。
摘要由CSDN通过智能技术生成

1只读 ---备份只读 故障 只读 还原有故障的数据文件 2只读 ---备份只读 可写 故障 还原备份数据文件，重做recover 3只读 ---备份可写 只读 故障 还原备份数据文件，recover 1 进入rman，确保是否有备份的文件： [oracle@oracle ~]$ rman target /Recovery Man

1>只读 ---备份只读 故障 只读 还原有故障的数据文件

2>只读 ---备份只读 可写 故障 还原备份数据文件，重做recover

3>只读 ---备份可写 只读 故障 还原备份数据文件，recover

1>

进入rman，确保是否有备份的文件：

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 09:44:08 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JADL10G (DBID=2011508104)

RMAN> list backup of tablespace users;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1 Full 609.23M DISK 00:00:38 06-NOV-14

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141106T063059

Piece Name: /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T063059_b5o994hx_.bkp

List of Datafiles in backup set 1

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

4 Full 422818 06-NOV-14 /u01/oracle/oradata/jadl10g/users01.dbf

修改表空间为只读表空间：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:45:35 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> alter tablespace users read only;

Tablespace altered.

进入rman，重新备份数据库，删除以前备份的可以节省空间使用delete backup命令可以删除；

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 09:50:29 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JADL10G (DBID=2011508104)

RMAN> backup database; --备份数据库

Starting backup at 06-NOV-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/oracle/oradata/jadl10g/system01.dbf

input datafile fno=00003 name=/u01/oracle/oradata/jadl10g/sysaux01.dbf

input datafile fno=00005 name=/u01/oracle/oradata/jadl10g/example01.dbf

input datafile fno=00002 name=/u01/oracle/oradata/jadl10g/undotbs01.dbf

input datafile fno=00004 name=/u01/oracle/oradata/jadl10g/users01.dbf

channel ORA_DISK_1: starting piece 1 at 06-NOV-14

channel ORA_DISK_1: finished piece 1 at 06-NOV-14

piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp tag=TAG20141106T095036 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 06-NOV-14

channel ORA_DISK_1: finished piece 1 at 06-NOV-14

piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_ncsnf_TAG20141106T095036_b5oo15kf_.bkp tag=TAG20141106T095036 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 06-NOV-14

RMAN> exit

Recovery Manager complete.

删除users表空间(只读的文件)

[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/users01.dbf

连接到DB，执行查询发现报错：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:52:33 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select * from scott.dept;

select * from scott.dept

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

连接到rman：

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 09:53:40 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JADL10G (DBID=2011508104)

RMAN> sql 'alter database datafile 4 offline '; --数据文件脱机

using target database control file instead of recovery catalog

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4; --恢复数据文件，此处可以是文件的路径 restore datafile '/u01/oracle/oradata/jadl10g/users01.dbf';

Starting restore at 06-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=136 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp tag=TAG20141106T095036

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 06-NOV-14

RMAN> sql 'alter database datafile 4 online '; ---连接数据文件

sql statement: alter database datafile 4 online

RMAN> exit

Recovery Manager complete.

连接到DB，执行查询发现可以查询到结果：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:55:00 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

2>

连接DB，修改表空间为可写：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 10:04:27 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Editio【本文来自鸿网互联 (http://www.68idc.cn)】n Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> alter tablespace users read write;

Tablespace altered.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

删除users表空间(可写的表空间)：

[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/users01.dbf

连接到DB，执行查询发现报错：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 10:05:32 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select * from scott.dept;

select * from scott.dept

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

连接到rman：

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 10:06:31 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JADL10G (DBID=2011508104)

RMAN> sql 'alter database datafile 4 offline ';

using target database control file instead of recovery catalog

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 06-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp tag=TAG20141106T095036

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 06-NOV-14

RMAN> recover datafile 4;

Starting recover at 06-NOV-14

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 06-NOV-14

RMAN> sql 'alter database datafile 4 online ';

sql statement: alter database datafile 4 online

RMAN> exit

Recovery Manager complete.

连接到DB，执行查询发现可以查询到结果：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 10:08:18 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

weixin_39743622
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值