oracle dg snapshot,【DG】之 Snapshot standby模式

Snapshot

standby(备库操作)

Snapshot standby模式，即在备库进行，开启此模式时为了在备库进行一些测试操作，而又不行留存在数据库中，当备库切换回physical standby物理备库时，

之前在snapshot standby模式进行的测试将会被丢弃。

Oracle 11g物理Data Guard之Snapshot

Standby数据库功能

注意：1.需首先确认备库已经结束日志应用了！

2.snapshot

standby模式时，闪回数据库功能可开启也也可关闭，默认是关闭状态；但必须设置快速恢复区大小及路径

——查询状态：(本次是不开闪回数据库功能)

SQL>

select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL>select database_role,open_mode from

v$database;

DATABASE_ROLEOPEN_MODE

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

PHYSICAL STANDBYREAD ONLY

——进行切换到snapshot standby模式：

SQL>alter

databaseconvert to snapshot standby;(是在open状态下操作的)

Database altered.

——再次查看状态：(变为mount)

SQL> select database_role,open_mode from

v$database;

DATABASE_ROLEOPEN_MODE

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

SNAPSHOT STANDBYMOUNTED

SQL>select flashback_on fromv$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

——最后启动数据库至open下(已经变为read write，可以做任何测试操作了)

SQL>

alter database open;

Database

altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLEOPEN_MODE

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

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

SNAPSHOT

STANDBYREAD WRITE

SQL>

此时可以查看alert日志，会发现数据库创建了一个guaranteed restore point，确保我们切回主备，可应用日志。

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

alter database convert to snapshot standby

Starting background process RVWR

Thu Oct 20 19:52:16 2016

RVWR started with pid=53, OS id=10342

Allocated 3981120 bytes in shared pool for

flashback generation buffer

Created guaranteed restore point

SNAPSHOT_STANDBY_REQUIRED_10/20/2016 19:52:16

Killing 3 processes with pids

10290,10294,10298 (all RFS) in order to disallow current and future RFS

connections. Requested by OS process 10039

All dispatchers and shared servers shutdown

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

——此时备库已经打开，可以做任何测试，测试如下：

SQL>

show user

USER

is "SYS"

SQL>

create table hr.st(x int);

Table

created.

SQL>

insert into hr.st values(1);

1

row created.

SQL>

commit;

Commit

complete.

SQL>

select * from hr.st;

X

----------

1

——恢复物理备库，数据库需要在mount下完成切换：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>startup mount;

ORACLE instance started.

Total System Global Area830930944 bytes

Fixed Size2257800 bytes

Variable Size679480440 bytes

Database Buffers146800640 bytes

Redo Buffers2392064 bytes

Database mounted.

SQL>

SQL>alter databaseconvert

tophysical standby;

Database altered.

切换完成后，数据库需要再次重启至mount

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

SQL>startup mount;

ORACLE instance started.

Total System Global Area830930944 bytes

Fixed Size2257800 bytes

Variable Size679480440 bytes

Database Buffers146800640 bytes

Redo Buffers2392064 bytes

Database mounted.

——应用日志，使同步：

SQL>recover managed standby

database using current logfile disconnect from session;

Media recovery complete.

SQL>

SQL>select database_role,open_mode from v$database;

DATABASE_ROLEOPEN_MODE

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

PHYSICAL STANDBYMOUNTED

SQL>recover managed standby database cancel;

Media recovery complete.

SQL>

SQL>alter database open;

Database altered.

SQL>select database_role,open_mode

from v$database;

DATABASE_ROLEOPEN_MODE

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

PHYSICAL STANDBY READ ONLY

——最后验证snapshot

standby模式下创建的表(正常是不存在的)

SQL> select * from hr.st;

select * from hr.st

*

ERROR at line 1:

ORA-00942: table or view does not exist

结束！！！！！！！