oracle 利用flashback将备库激活为read wirte(10g 及上)

oracle 利用flashback将备库激活为read wirte(10g 及上)
环境:
OS: CENTOS 6.5 X64
DB: ORACLE 10.2.0.5
主库操作:
SQL> alter system switch logfile;

System altered.

SQL>
 
备库操作
取消归档应用
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> 

创建还原点

SQL>  create restore point restore_point_dg guarantee flashback database;

Restore point created.

SQL> 

主库操作
将备库的归档目的设置为defer(这样后台不会报错,防止主库传输数据到备库)

官方解释
defer
Specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enable
SQL> alter system set log_archive_dest_state_2=defer;

将备库激活为read/wirte
SQL> alter database activate standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> 

备库状态为read/write
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> 

这样我们可以在备库Export操作了
[oracle@oracle10g-dg2-213-101 backup]$ expdp directory=backup dumpfile=test1`date +%F`.dmp logfile=test1`date +%F`.log schemas=test1

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 15 September, 2014 22:37:17

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=backup dumpfile=test12014-09-15.dmp logfile=test12014-09-15.log schemas=test1 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST1"."TEST"                              4.960 KB       5 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/dump/test12014-09-15.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:37:31

[oracle@oracle10g-dg2-213-101 backup]$ 

主库操作
主库上添加记录
SQL> insert into test values(10);

1 row created.

SQL> insert into test values(10);

1 row created.

SQL> insert into test values(10);

1 row created.

SQL> insert into test values(11);

1 row created.

SQL> insert into test values(11);

1 row created.

SQL> commit;

Commit complete.

System altered.

SQL> 

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> 

备库操作
备库上删除表
[oracle@oracle10g-dg2-213-101 backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 15 22:40:53 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, OLAP, Data Mining and Real Application Testing options

SQL> conn test1/test1
Connected.
SQL> drop table test purge;

Table dropped.

SQL> 

准备重新恢复到dg备库环境
SQL> startup mount force;
ORACLE instance started.

Total System Global Area  704643072 bytes
Fixed Size		    2098912 bytes
Variable Size		  192940320 bytes
Database Buffers	  503316480 bytes
Redo Buffers		    6287360 bytes
Database mounted.
SQL> 

闪回数据库
SQL>  flashback database to restore point restore_point_dg;

Flashback complete.

SQL> 

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  704643072 bytes
Fixed Size		    2098912 bytes
Variable Size		  192940320 bytes
Database Buffers	  503316480 bytes
Redo Buffers		    6287360 bytes
SQL> alter database mount standby database; #或者alter database convert to physical standby;

Database altered.


主库操作
SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

SQL> 

备库操作,检查进程是否正常

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH	  CONNECTED
ARCH	  CONNECTED
ARCH	  CONNECTED
MRP0	  WAIT_FOR_LOG
RFS	  IDLE
RFS	  IDLE

6 rows selected.

SQL> 

主库操作
切换日志
SQL> alter system switch logfile;

System altered.

SQL> 

备库已正常
FS[1]: Successfully opened standby log 8: ‘/u01/app/oracle/oradata/netdata/st_redo08a.log‘
Mon Sep 15 22:50:22 CST 2014
Media Recovery Log /u01/app/oracle/archive/netdata/1_79_857898543.arc
Media Recovery Waiting for thread 1 sequence 80

 

oracle 利用flashback将备库激活为read wirte(10g 及上)

上一篇:mysql函数笔记


下一篇:MySQL库表设计小技巧