【只读表空间--恢复-1】-mysql教程-PHP中文网

首页

数据库

mysql教程

【只读表空间--恢复-1】

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:12 PM

只读备份恢复故障数字空间

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: &#39;/u01/oracle/oradata/jadl10g/users01.dbf&#39;
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 &#39;alter database datafile 4 offline &#39;; --数据文件脱机
using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline


RMAN> restore datafile 4; --恢复数据文件，此处可以是文件的路径 restore datafile &#39;/u01/oracle/oradata/jadl10g/users01.dbf&#39;;
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 &#39;alter database datafile 4 online &#39;; ---连接数据文件


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: &#39;/u01/oracle/oradata/jadl10g/users01.dbf&#39;
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 &#39;alter database datafile 4 offline &#39;;


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 &#39;alter database datafile 4 online &#39;;


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

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

在MySQL中使用视图的局限性是什么？May 14, 2025 am 12:10 AM

mysqlviewshavelimitations：1）他们不使用Supportallsqloperations，限制DatamanipulationThroughViewSwithJoinSorsubqueries.2）他们canimpactperformance，尤其是withcomplexcomplexclexeriesorlargedatasets.3）

确保您的MySQL数据库：添加用户并授予特权May 14, 2025 am 12:09 AM

porthusermanagementInmysqliscialforenhancingsEcurityAndsingsmenting效率databaseoperation.1）usecReateusertoAddusers，指定connectionsourcewith@'localhost'or@'％'。

哪些因素会影响我可以在MySQL中使用的触发器数量？May 14, 2025 am 12:08 AM

mysqldoes notimposeahardlimitontriggers，butacticalfactorsdeterminetheireffactective：1）serverConfiguration impactactStriggerGermanagement; 2）复杂的TriggerSincreaseSySystemsystem load; 3）largertablesslowtriggerperfermance; 4）highConconcConcrencerCancancancancanceTigrignecentign; 5）; 5）

mysql：存储斑点安全吗？May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

mySQL：通过PHP Web界面添加用户May 14, 2025 am 12:04 AM

通过PHP网页界面添加MySQL用户可以使用MySQLi扩展。步骤如下：1.连接MySQL数据库，使用MySQLi扩展。2.创建用户，使用CREATEUSER语句，并使用PASSWORD()函数加密密码。3.防止SQL注入，使用mysqli_real_escape_string()函数处理用户输入。4.为新用户分配权限，使用GRANT语句。

mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

See all articles