dataguard mysql,[dataguard同步数据库]Dataguard环境下数据库的备份与恢复

在线QQ客服：1922638

专业的SQL Server、MySQL数据库同步软件

在部署完active data guard后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源，下面演示下利用备库备份来还原主库数据的过程！

一:主库上创建表空间，并在表空间上建表，插入测试数据，同时检查备库的同步情况

SQL> create tablespace test01 datafile ‘/u01/app/Oracle/oradata/db1/test01.dbf’ size 10M;

Tablespace created.

SQL> create table rman tablespace test01 as select object_id,object_name from dba_objects;

Table created.

SQL> select count(*) from rman;

COUNT(*)

———-

76379

[oracle@db2 db1]$ sqlplus/nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 29 11:37:38 2012

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

SQL> conn/as sysdba

Connected.

SQL> select count(*) from rman;

COUNT(*)

———-

76379

二:在备库上使用rman对新建的表空间test01进行备份

[oracle@db2 db1]$ rman target/

Recovery Manager: Release 11.2.0.3.0-Production on Mon Oct 29 11:38:01 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: DB1 (DBID=1387827106)

RMAN> report schema;

RMAN> backup tablespace test01 format ‘/home/oracle/test01_%U’;

Starting backup at 2012-10-29-11:38:50

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00034 name=/u01/app/oracle/oradata/db1/test01.dbf

channel ORA_DISK_1: starting piece 1 at 2012-10-29-11:38:51

channel ORA_DISK_1: finished piece 1 at 2012-10-29-11:38:52

piece handle=/home/oracle/test01_03novc2b_1_1 tag=TAG20121029T113851 comment=NONE

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

Finished backup at 2012-10-29-11:38:52

三:关闭主库后删除表空间数据文件，模拟数据丢失场景

[oracle@db1 ~]$ sqlplus/nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 29 11:39:49 2012

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

SQL> conn/as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

[oracle@db1 ~]$ rm-rf/u01/app/oracle/oradata/db1/test01.dbf

四:重新启动主库，只能启动到mount状态

[oracle@db1 ~]$ sqlplus/nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 29 11:41:42 2012

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

SQL> conn/as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1536602112 bytes

Fixed Size 2228624 bytes

Variable Size 1174408816 bytes

Database Buffers 352321536 bytes

Redo Buffers 7643136 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 34-see DBWR trace file

ORA-01110: data file 34: ‘/u01/app/oracle/oradata/db1/test01.dbf’

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

————————————

MOUNTED PRIMARY