Oracle 11g DataGuard 配置详细说明

—

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7('/oracledata/db1/standby /slog4.rdo') SIZE 50M;

standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。

standby redolog的组成员数和大小也尽量和online redolog一样。 删除命令：

SQL>alter database drop standby logfile group4 验证是否创建成功：

sql>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; 14. Start Redo Apply 启动redo应用

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 启动实时应用

SQL> alter database recover managed standby database disconnect from session; 提示：disconnect from session子句并非必须，该子句用于指定启动完应用后自动退出到命令操作符前，如果不指定的话，当前session就会一直停留处理redo应用，如果想做其它操作，就只能新建一个连接。 停止standby

正常情况下，我们停止也应该是先停止redo应用，可以通过下列语句： SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 然后再停止standby数据库 SQL>SHUTDOWNIMMEDIATE;

查看哪些归档日志被APPLY了 在备库

sql>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在主库强制日志切换到当前的online redo log file.

欢迎下载

11

—

sql> ALTER SYSTEM ARCHIVE LOG CURRENT; 在备库查看新的被归档的redo data

sql>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在备库查看接收到的被应用的redo

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 查看数据库的角色 主库上：

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- --------------------

PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 备库上

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- --------------------

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 15、DataGuard关启状态 启用备用数据库

SQL > STARTUP NOMOUNMT;

SQL >alter database mount standby database;

SQL >alter database recover managed standby database disconnect from session; （注： alter database recover managed standby database using current logfile disconnect from session #切换到实时恢复管理模式） 关闭备用数据库

SQL >alter database recover managed standby database cancel;

欢迎下载

12

—

SQL >shutdown immediate; 从关闭状态打开 SQL >startup nomount;

SQL >alter database mount standby database; SQL >alter database open read only; 从正在恢复状态只读打开

SQL >alter database recover managed standby database cancel; SQL >alter database open read only; 切换回到恢复状态

SQL >alter database recover managed standby database disconnect from session;

16、主备数据库切换 16.１、正常切换： 主服务器

SQL >alter database commit to switchover to physical standby; SQL >shutdown immediate SQL >startup nomount;

SQL >alter database mount standby database;

SQL >alter database recover managed standby database disconnect from session 备用服务器

SQL >alter database commit to switchover to primary SQL >shutdown immediate; SQL >startup

16.２、非正常切换：（即主服务器当机的情况）启动failover 备服务器

SQL >alter database recover managed standby database finish; SQL >alter database commit to switchover to primary; SQL >shutdown immediate; SQL >startup;

欢迎下载

13

—

主备库切换 1）查看主库的状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 2）将主库切换至备用模式

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH - > SESSION SHUTDOWN; 3）关闭、装载主数据库 SQL> SHUTDOWN ABORT; SQL> STARTUP MOUNT; 4）查看备库准备向主库模式切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO_PRIMARY

5）切换备库至主库模式

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; 6）打开新的主数据库

SQL> ALTER DATABASE OPEN;

7）在新的备库服务器上启动 REDO apply。

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE - > DISCONNECT FROM SESSION; 常用维护SQL 添加几个常用命令 备库启动归档日志应用

alter database recover managed standby database disconnect from session; 备库启动实时归档应用

欢迎下载

14

—

alter database recover managed standby database using current logfile disconnect from session

备库停止归档日志应用

alter database recover managed standby database cancel; 查询归档日志是否被应用，查询V$archived_log视图的applied列

select sequence#,dest_id,first_time,next_time, applied from v$archived_log; 查看备库是否和主库同步，查询V$archive_dest_status视图

select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;

监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少 select * from v$archive_gap; 查看当前主机的运行状态

select switchover_status,database_role,protection_mode from v$database; 查看备库接收、应用redo数据的过程 select message from v$dataguard_status;

备库端查看RFS(Remote File Service)接收日志情况和MRP应用日志同步主数据库的情况(Physical Standby Database Only) 记录当前备库的一些进程情况和进程ID select process,status,thread#,sequence#,block#,blocks from v$managed_standby; V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息 SELECT * FROM V$STANDBY_LOG;

启动Data Guard 后， 查看同步情况：： SQL> select error from v$archive_dest; 用SQL 查看了一下同步正常：

SQL> select sequence#,applied from v$archived_log; 主库归档：

SQL> ALTER SYSTEM SWITCH LOGFILE; --对单实例数据库或RAC中的当前实例执行日志切换

欢迎下载

15