Oracle 11G Active DataGuard角色切换-编程学习网

1.切换前准备：

1.1主库检查：

1.1.1 FAL检查

SQL> show parameter fal;

NAME TYPE VALUE

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

fal_client string

fal_server string

SQL> alter system set fal_client='pri_1522';

SQL> alter system set fal_server='std_1522';

SQL> show parameter fal;

NAME TYPE VALUE

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

fal_client string pri_1522

fal_server string std_1522

1.1.2 Standby文件自动管理

SQL> show parameter standby_file_management;

NAME TYPE VALUE

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

standby_file_management string MANUAL

SQL> alter system set standby_file_management='auto';

SQL> show parameter standby_file_management;

NAME TYPE VALUE

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

standby_file_management string auto

1.1.3 Convert参数设定

设定数据文件、重做日志转换目录

SQL> show parameter convert;

NAME TYPE VALUE

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

db_file_name_convert string /u01/app/oradata/testdb, /u01/

app/oradata/testdb

log_file_name_convert string /u01/app/archivelog/testdb, /u

01/app/archivelog/testdb

1.1.4 Log_archive_dest参数

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string location=/u01/app/archivelog/t

estdb valid_for=(all_logfiles,

all_roles) db_unique_name=pri_

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

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

log_archive_dest_2 string service=std_1522 lgwr sync val

id_for=(online_logfiles,primar

y_role) db_unique_name=std_db

1.2备库检查

1.2.1 FAL检查

SQL> show parameter fal;

NAME TYPE VALUE

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

fal_client string std_1522

fal_server string pri_1522

1.2.2 Standby文件自动管理

SQL> show parameter standby_file_management;

NAME TYPE VALUE

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

standby_file_management string auto

1.2.3 Convert参数设定

SQL> show parameter convert;

NAME TYPE VALUE

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

db_file_name_convert string /u01/app/oradata/testdb, /u01/

app/oradata/testdb

log_file_name_convert string /u01/app/archivelog/testdb, /u

01/app/archivelog/testdb

1.2.4 Log_archive_dest参数

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string location=/u01/app/archivelog/t

estdb valid_for=(all_logfiles,

all_roles) db_unique_name=std_

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

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

log_archive_dest_2 string service=pri_1522 lgwr sync val

id_for=(online_logfiles,primar

y_role) db_unique_name=pri_db

1.2.5 确定日志归档、force logging打开

SQL> SELECT log_mode, force_logging, open_mode FROM v$database;

LOG_MODE FOR OPEN_MODE

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

ARCHIVELOG YES READ ONLY

2.主库切换成物理备库

2.1检查主库状态

2.1.1切换前查看主库是否有GAP

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY RESOLVABLEGAP READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> SELECT status, gap_status, dest_id FROM v$archive_dest_status WHERE dest_id=2;

STATUS GAP_STATUS DEST_ID

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

VALID RESOLVABLE GAP 2

2.1.2有GAP，进行一次日志切换

SQL> alter system switch logfile;

SQL> SELECT status, gap_status, dest_id FROM v$archive_dest_status WHERE dest_id=2;

STATUS GAP_STATUS DEST_ID

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

VALID NO GAP 2

SQL> SELECT database_role, switchover_status, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY TOSTANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2.2备库的状态

SQL> SELECT switchover_status, database_role, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2.3备库取消应用日志

SQL> alter database recover managed standby database cancel;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY SWITCHOVER PENDING READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2.4主库切换成物理备库

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database

ERROR at line 1:

ORA-01507: database not mounted

2.5原备库切换成新主库

2.5.1查看状态

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY SWITCHOVER PENDING READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2.5.2原备库需要应用日志（因原主库已发生切换产生了日志）

SQL> alter database recover managed standby database disconnect;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY TO PRIMARY READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2.5.3原备库切换成新主库

SQL> alter database commit to switchover to primary;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY NOTALLOWED MOUNTED MAXIMUM PERFORMANCE UNPROTECTED

2.5.4新主库（原备库）置为open状态：

SQL> alter database open;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY FAILED DESTINATION READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

到此原备库已经切换成新的主库了，原主库已切换成新备库并关闭。

3.新备库管理

3.1启动新备库，并置于mount状态

由于原主库切换时已经关闭，需要重启实例，并置于mount状态：

SQL> ! ps -ef | grep ora_

oracle 28136 1 011:10 ? 00:00:01 ora_pmon_testdb

oracle 28138 1 011:10 ? 00:00:00 ora_vktm_testdb

oracle 28142 1 011:10 ? 00:00:00 ora_gen0_testdb

oracle 28144 1 011:10 ? 00:00:00 ora_diag_testdb

oracle 28146 1 011:10 ? 00:00:00 ora_dbrm_testdb

oracle 28148 1 011:10 ? 00:00:00 ora_psp0_testdb

oracle 28150 1 011:10 ? 00:00:01 ora_dia0_testdb

oracle 28152 1 011:10 ? 00:00:02 ora_mman_testdb

oracle 28154 1 011:10 ? 00:00:00 ora_dbw0_testdb

oracle 28156 1 011:10 ? 00:00:01 ora_lgwr_testdb

oracle 28158 1 011:10 ? 00:00:01 ora_ckpt_testdb

oracle 28160 1 011:10 ? 00:00:01 ora_smon_testdb

oracle 28162 1 011:10 ? 00:00:00 ora_reco_testdb

oracle 28164 1 011:10 ? 00:00:01 ora_mmon_testdb

oracle 28166 1 011:10 ? 00:00:00 ora_mmnl_testdb

oracle 28290 1 011:25 ? 00:00:00 ora_s001_testdb

oracle 28292 1 011:25 ? 00:00:00 ora_d000_testdb

oracle 28299 18875 0 11:29 pts/0 00:00:00 /bin/bash -c ps -ef | grep ora_

oracle 28301 28299 0 11:29 pts/0 00:00:00 grep ora_

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

3.2新备库应用日志：

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

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY TO PRIMARY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

3.3新备库状态置为open：

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-10456: cannotopen standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

此处报错是因为备库在应用日志

3.4新备库应用日志：

3.4.1创建standby日志组

如果不创建standby日志组，应用日志会报错。

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

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

ERROR at line 1:

ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

建立Standby log来应用来自主库（原备库）日志（11G新特性redo only with apply）

SQL> alter database add standby logfile thread 1 group 4 '/u01/app/oradata/testdb/stdredo04.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 5 '/u01/app/oradata/testdb/stdredo05.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 6 '/u01/app/oradata/testdb/stdredo06.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 7 '/u01/app/oradata/testdb/stdredo07.rdo' size 50m;

SQL> column member format a50;

SQL> SELECT * FROM v$logfile order by group#;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /u01/app/oradata/testdb/redo01.rdo NO

2 ONLINE /u01/app/oradata/testdb/redo02.rdo NO

3 ONLINE /u01/app/oradata/testdb/redo03.rdo NO

4 STANDBY /u01/app/oradata/testdb/stdredo04.rdo NO

5 STANDBY /u01/app/oradata/testdb/stdredo05.rdo NO

6 STANDBY /u01/app/oradata/testdb/stdredo06.rdo NO

7 STANDBY /u01/app/oradata/testdb/stdredo07.rdo NO

3.4.2新备库应用日志

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

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

4状态检查及数据验证

4.1新主库检查

当新备库（原主库）启动正常时，新主库状态就正常了。

4.1.1检查新主库是否有GAP

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY RESOLVABLE GAP READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> alter system switch logfile;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

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

PRIMARY TOSTANDBY READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

4.2数据验证

4.2.1新主库建表，并插入数据

SQL> SELECT username, account_status FROM dba_users WHERE username='HR';

USERNAME ACCOUNT_STATUS

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

HR OPEN

SQL> create table hr.table2(id int, name varchar2(20));

SQL> insert into hr.table2 values (01, 'Active DataGuard');

1 row created.

SQL> SELECT * FROM hr.table2;

ID NAME

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

1 Active DataGuard

SQL> commit;

4.2.2新备库（原主库）验证

SQL> SELECT * FROM hr.table2;

ID NAME

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

1 Active DataGuard

附件：http://down.51cto.com/data/2367432

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

历年真题答案解析
备考技巧名师总结
高频考点精准押题

资料下载
历年真题

2024上半年软考中级软件测评师考试基础知识真题
193.9 KB下载数265
2024上半年软考中级软件设计师考试基础知识真题
191.63 KB下载数245
2023下半年-系统集成项目管理工程师-真题考点汇总（完整版）
143.91 KB下载数1148
2023年下半年系统集成项目管理工程师第一、二、三批次真题考点整理(考友回忆版)
183.71 KB下载数642
2023年上半年软考中级《系统集成项目管理工程师》-基础知识-考试真题及答案
644.84 KB下载数2756

2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）
难度 813人已做
查看
【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析
难度 354人已做
查看
【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析
难度 318人已做
查看
2024年上半年软考高项第一、二批次真题考点汇总（完整版）
难度 435人已做
查看
2024年上半年系统架构设计师考试综合知识真题
难度 224人已做
查看

发现更多好内容

猜你喜欢

AI推送时光机