数据库读写分离解决方案--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

关闭顺序

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

? 关闭主库

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。

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:

Oracle Data Guard实施方案

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Step 9 Open the new primary database. SQL> ALTER DATABASE OPEN;

Step 10 Back up the new primary database.

Oracle recommends that a full backup be taken of the new primary database.

Step 11 Restart Redo Apply if it has stopped at any of the other physical standby databases in your Data Guard configuration. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE - > DISCONNECT FROM SESSION; 附：

1. 启动到管理模式

SQL>shutdown immediate; SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standby database disconnect from session; 2.启动到只读方式

SQL>shutdown immediate; SQL>startup nomount;

SQL>alter database mount standby database; SQL>alter database open read only; 3如果在管理恢复模式下到只读模式

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