[20140422]使用dgmgrl管理dataguard(13)

简介: [20140422]使用dgmgrl管理dataguard(13).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.

[20140422]使用dgmgrl管理dataguard(13).txt

参考链接:
http://blog.itpub.net/267265/viewspace-1142649/
http://blog.itpub.net/267265/viewspace-1143027/
http://blog.itpub.net/267265/viewspace-1143058/
http://blog.itpub.net/267265/viewspace-1143126/
http://blog.itpub.net/267265/viewspace-1143480/
http://blog.itpub.net/267265/viewspace-1144742/
http://blog.itpub.net/267265/viewspace-1145573/
http://blog.itpub.net/267265/viewspace-1145697/
http://blog.itpub.net/267265/viewspace-1145727/
http://blog.itpub.net/267265/viewspace-1146558/
http://blog.itpub.net/267265/viewspace-1146575/
http://blog.itpub.net/267265/viewspace-1147481/

-- 我的测试环境:primary的tnsnames别名test,standby主机的tnsnames别名testdg。数据库版本:11GR2.
-- 今天做一个SNAPSHOT STANDBY的测试.

--有时候开发要做一些测试,测试环境要比较真实的反应生产系统的情况,使用备用库比较合适,但是11G下虽然打开apply read only模式,
--但是往往还包含一些dml语句,必须打开读写模式.这样要求转换备用库到SNAPSHOT STANDBY.完成后在转换回来.

DGMGRL> show configuration
Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

SYS@testdg> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  YES YES

--使用sqlplus要执行如下:SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;这个测试忽略,使用dgmgrl看看.

DGMGRL> convert database testdg to SNAPSHOT STANDBY;
Converting database "testdg" to a Snapshot Standby database, please wait...
Error: ORA-16668: operation cannot be performed on the fast-start failover target standby database

Failed.
Failed to convert database "testdg"
--不能打开fast-start failover.关闭fast-start failover,继续测试.

DGMGRL> disable fast_start failover ;
Disabled.
DGMGRL> show configuration

Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> convert database testdg to SNAPSHOT STANDBY;
Converting database "testdg" to a Snapshot Standby database, please wait...
Database "testdg" converted successfully
--OK完成.


SYS@testdg> Select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON,resetlogs_change#,prior_resetlogs_change# from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      FOR FLASHBACK_ON       RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE#
---------------- -------------------- -------------------- --- ------------------ ----------------- -----------------------
SNAPSHOT STANDBY READ WRITE           MAXIMUM PERFORMANCE  YES YES                       3269794846              3269769399

--已经打开了读写模式.
SYS@testdg> select * from v$restore_point;
       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                           RESTORE_PO PRE NAME
---------- --------------------- --- ------------ ------------------------------ ---------- --- --------------------------------------------------
3269794844                     8 YES     52428800 2014-04-23 09:46:06.000000000             YES SNAPSHOT_STANDBY_REQUIRED_04/23/2014 09:46:06

--数据库记录了转成SNAPSHOT STANDBY的scn.做一些DML操作.

SCOTT@testdg> select * from scott.dept1 where deptno=60;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        60 MMMM           DDDDDx

SCOTT@testdg> update SCOTT.dept1 set loc='EEEEEE' where deptno=60;
1 row updated.

SCOTT@testdg> commit ;
Commit complete.

--转换回来.使用sqlplus的命令是alter database convert to physical standby;这个测试忽略,使用dgmgrl看看.

DGMGRL> show configuration
Configuration - study
  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> convert database testdg to physical STANDBY;
Converting database "testdg" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Continuing to convert database "testdg" ...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Database "testdg" converted successfully

DGMGRL> show configuration

Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


--查看testdg的情况

SCOTT@testdg> Select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON,resetlogs_change#,prior_resetlogs_change# from v$database;
DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      FOR FLASHBACK_ON       RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE#
---------------- -------------------- -------------------- --- ------------------ ----------------- -----------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  YES YES                       3269769399              3269705600

SCOTT@testdg> select * from scott.dept1 where deptno=60;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        60 MMMM           DDDDDx

--可以发现dml的信息已经还原.

总结:
--做SNAPSHOT STANDBY,无论是sqlplus还是dgmgrl都是很简单的.命令如下:
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
alter database convert to physical standby;

convert database testdg to SNAPSHOT STANDBY;
convert database testdg to physical STANDBY;

目录
相关文章
|
Oracle 网络协议 关系型数据库
|
数据库 Go 测试技术
[20151109]使用dgmgrl管理dataguard(15)
[20151109]使用dgmgrl管理dataguard(15).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
1072 0
|
SQL 监控 Oracle
11g dataguard使用总结
11g的dataguard相比于10g来说,最优越的特性应该算就是active dataguard了,这一点改进在很大意义上促使用户需要把数据库从10g升级到11g,读写分离在这个时候得到了升华,而且在后台会根据需要进行数据的同步,相比于使用10g,想读数据的时候把数据库启动到read only 阶段,但这个时候不接受日志同步数据,如果需要同步数据还需要把数据库再启动到mount阶段,感觉还是比较繁琐的。
1247 0
dataguard dubugs
alter database open read only;alter database open read only*ERROR at line 1:ORA-10456: cannot open standby database; media recovery session may be in...
731 0
|
Oracle 关系型数据库 数据库
配置DATAGUARD时报ORA-12528
说明:在dataguard的搭建中,运行rman恢复的时候报错ORA-12528的错误; 脚本如下:RMAN>  connect auxiliary sys/oracle@SCMPRD02     解决...
952 0
|
SQL Oracle 关系型数据库
[20140422]使用dgmgrl管理dataguard(14)
[20140422]使用dgmgrl管理dataguard(14).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
888 0
|
Oracle 关系型数据库 数据库
[20140422]使用dgmgrl管理dataguard(12)
[20140422]使用dgmgrl管理dataguard(12).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
949 0
|
测试技术 数据库 关系型数据库
[20140422]使用dgmgrl管理dataguard(11)
[20140422]使用dgmgrl管理dataguard(11).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
968 0
|
Oracle 关系型数据库 测试技术
[20140420]使用dgmgrl管理dataguard(10)
[20140420]使用dgmgrl管理dataguard(10).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
766 0