数据库读写分离解决方案--DG实施方案

Oracle Data Guard实施方案

17 YES 18 YES 19 YES

同步成功。 至此Oracle 的Data Guard 环境已经搭建完成。

5.16 DataGuard日常维护

在日常维护中，请严格按照以下顺序来操作：

启动顺序

启动的时候，先启动备库，然后启动主库。

一、启从、主库的监听Listener 从库orcldg： $lsnrctl start 主库orcl： $lsnrctl start

二、启动备库数据库，执行如下： $sqlplus “/ as sysdba” SQL> startup nomount

SQL> alter database mount standby database; disconnect;

#让备库处于standby

#开始同步

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE 三、启动主库数据库(上述第二步执行完毕后，方可执行如下命令)： SQL>startup

Oracle Data Guard实施方案

关闭顺序

关闭的时候正好相反，先关闭主库，然后关闭从库。

? 关闭主库

CMD>su – oracle

CMD>sqlplus “/ as sysdba” SQL>shutdown immediate; ? 关闭从库

telnet 120.4.7.50 su – oracle

CMD>sqlplus “/ as sysdba”

SQL>alter database recover managed standby database cancel; #停止同步 SQL>shutdown immediate

查看备库的数据

SQL>alter database recover managed standby database cancel; SQL> alter database open read only; Database altered.

SQL> select count(1) from user_tables; …….

SQL操作完成后，需执行以下语句以令从库继续处于接收状态：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

5.17 主备库切换

1. 查看主库的状态：确认a是否可以做switch over ? 不能切换的情况

Oracle Data Guard实施方案

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ------------------ SESSIONS ACTIVE

在这种情况下，说明还有活动session，需要先kill掉，只留下当前sys进程

select sid,SERIAL# from v$session where sid>10; SID SERIAL# ---------- ---------- 12 14 17 3

查出当前连接session

SQL> select distinct ss.sid from v$mystat ss;

杀掉其他session

SQL> alter system kill session '12,14'; System altered

? 可以切换的情况

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY

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 1 row selected 注：如果是TO PRIMARY表示可以正常切换，不过还会遇到NOT ALLOWED和PENDING或者LATENT，实际操作下来如果备库的switchover_status为not allowed或者to primary都可以正常切换

5.切换备库至主库模式

SQL> alter database commit to switchover to primary with session shutdown;

6.打开新的主数据库

SQL> ALTER DATABASE OPEN;

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

Oracle Data Guard实施方案

SQL> alter database recover managed standby database using current logfile disconnect from session;

5.18 灾难恢复（failover）

Step 1 Flush any unsent redo from the primary database to the target standby database

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

Step 2 Verify that the standby database has the most recently archived redo log file for each primary database redo thread.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) - > OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 3 Identify and resolve any archived redo log gaps.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 4 Repeat Step 3 until all gaps are resolved.

Step 5 Stop Redo Apply.

Issue the following SQL statement on the target standby database: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 6 Finish applying all received redo data.

Issue the following SQL statement on the target standby database: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Step 7 Verify that the target standby database is ready to become a primary database.

Step 8 Switch the physical standby database to the primary role. Issue the following SQL statement on the target standby database: