逻辑Data Guard维护篇.docx
- 文档编号:2751053
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:14
- 大小:17.82KB
逻辑Data Guard维护篇.docx
《逻辑Data Guard维护篇.docx》由会员分享,可在线阅读,更多相关《逻辑Data Guard维护篇.docx(14页珍藏版)》请在冰点文库上搜索。
逻辑DataGuard维护篇
逻辑DataGuard维护
1、主备库的转换之switchover
SQL>showparameterFAL;
NAMETYPEVALUE
-----------------------------------------------------------------------------
fal_clientstringorcl_p.1_tns
fal_serverstringorcl_s.2_tns
SQL>showparameterlog_archive_dest;
NAMETYPEVALUE
-----------------------------------------------------------------------------
log_archive_deststring
log_archive_dest_1stringlocation=/u01/arch
log_archive_dest_10string
log_archive_dest_2stringservice=orcl_s.2_tnsarchvali
d_for=(online_logfiles,primar
y_role)db_unique_name=orcl2
log_archive_dest_3string
log_archive_dest_4string
log_archive_dest_5string
log_archive_dest_6string
log_archive_dest_7string
NAMETYPEVALUE
-----------------------------------------------------------------------------
log_archive_dest_8string
log_archive_dest_9string
log_archive_dest_state_1stringenable
log_archive_dest_state_10stringenable
log_archive_dest_state_2stringENABLE
log_archive_dest_state_3stringenable
log_archive_dest_state_4stringenable
log_archive_dest_state_5stringenable
log_archive_dest_state_6stringenable
log_archive_dest_state_7stringenable
log_archive_dest_state_8stringenable
NAMETYPEVALUE
-----------------------------------------------------------------------------
log_archive_dest_state_9stringenable
SQL>altersystemsetlog_archive_dest_1='location=/u01/arch1valid_for=(online_logfiles,all_roles)db_unique_name=orcl1';
Systemaltered.
SQL>altersystemsetlog_archive_dest_3='location=/u01/arch2valid_for=(standby_logfiles,standby_role)db_unique_name=orcl1';
Systemaltered.
SQL>showparameterlog_archive_dest;
NAMETYPEVALUE
-----------------------------------------------------------------------------
log_archive_deststring
log_archive_dest_1stringlocation=/u01/arch
log_archive_dest_10string
log_archive_dest_2stringservice=orcl_s.2_tnsarchvali
d_for=(online_logfiles,primar
y_role)db_unique_name=orcl2
log_archive_dest_3stringlocation=/u01/arch2valid_for=
(standby_logfiles,standby_rol
e)db_unique_name=orcl1
log_archive_dest_4string
log_archive_dest_5string
。
。
。
。
。
。
创建三组standby的redolog(至少也要两组)
SQL>alterdatabaseaddstandbylogfilegroup4'/u01/app/oracle/oradata/orcl/stdredo01.log'size50m;
Databasealtered.
SQL>alterdatabaseaddstandbylogfilegroup5'/u01/app/oracle/oradata/orcl/stdredo02.log'size50m;
Databasealtered.
SQL>alterdatabaseaddstandbylogfilegroup6'/u01/app/oracle/oradata/orcl/stdredo03.log'size50m;
Databasealtered.
查看一下primary是switchover状态,结果为TOSTANDBY或者SESSIONSACTIVE视为正常
SQL>selectswitchover_statusfromv$database;
SWITCHOVER_STATUS
--------------------
SESSIONSACTIVE
将primary数据库置为将要装换的状态
SQL>alterdatabasepreparetoswitchovertologicalstandby;
Databasealtered.
查看当前数据库状态
SQL>selectswitchover_statusfromv$database;
SWITCHOVER_STATUS
--------------------
PREPARINGSWITCHOVER
将逻辑standby数据库转换为primary
STANDBY>alterdatabasepreparetoswitchovertoprimary;
Databasealtered.
查看备份数据库的状态
STANDBY>selectswitchover_statusfromv$database;
SWITCHOVER_STATUS
--------------------
PREPARINGSWITCHOVER
此时查看primary数据库的转换状态
PRIMARY>selectswitchover_statusfromv$database;
SWITCHOVER_STATUS
--------------------
TOLOGICALSTANDBY
将primary数据库转换为逻辑的standby
PRIMARY>alterdatabasecommittoswitchovertologicalstandby;
Databasealtered.
返回再去看看原standby数据库的状态,很神奇
STANDBY>selectswitchover_statusfromv$database;
SWITCHOVER_STATUS
--------------------
TOPRIMARY
将该standby数据库转换为primary
STANDBY>alterdatabasecommittoswitchovertoprimary;
Databasealtered.
逻辑standby转换完成,注意将新转换来的standby启动SQL应用
PRIMARY>setsqlprompt'NEW_STANDBY>'
NEW_STANDBY>alterdatabasestartlogicalstandbyapplyimmediate;
Databasealtered.
在心的primary端添加数据进行检测:
STANDBY>setsqlprompt'NEW_PRIMARY>'
NEW_PRIMARY>select*fromscott.dg_test;
ID
----------
DG_TEST_1
DG_TEST_2
DG_TEST_3
DG_TEST_4
NEW_PRIMARY>insertintoscott.DG_TEST
2values('DG_TEST_5')
3/
1rowcreated.
NEW_PRIMARY>select*fromscott.DG_TEST;
ID
----------
DG_TEST_1
DG_TEST_2
DG_TEST_3
DG_TEST_4
DG_TEST_5
NEW_PRIMARY>commit;
Commitcomplete.
NEW_PRIMARY>altersystemswitchlogfile;
Systemaltered.
在新的standby端查看数据是否接收
NEW_STANDBY>select*fromscott.dg_test;
ID
----------
DG_TEST_1
DG_TEST_2
DG_TEST_3
DG_TEST_4
DG_TEST_5
小结:
1、在进行转换前,要确定备库的设置健全,比如几个不同日志存放目录的设置
2、在将数据库置为开始转换状态时,由于,或因某些原因另一数据库无法自动到’TOSTANDBY’或’TOPRIMAYR’状态,可用下列语句取消转换状态:
alterdatabasepreparetoswitchovercancel;
2、逻辑DG主备库转换之failover
既然是failover,那么就认为PRIMARY数据库已经崩溃,需要马上将其中的一个逻辑STANDBY数据库置为PRIMAYD
虽然primary数据库已经崩溃,如果能打开数据到mount状态,可以先查看一下日志情况,这样可以对日志进行一些操作,尽量的来减少数据的丢失,当然,也可以在数据库不能mount的时候去归档路径下查看归档信息。
SQL>selectmax(sequence#)fromv$archived_log;
MAX(SEQUENCE#)
--------------
15
STANDBY>selectsequence#,appliedfromdba_logstdby_log;
SEQUENCE#APPLIED
------------------
15CURRENT
此处日志已经完全被应用,如果发现有日志未被发送到STANDBY,可以手工的将日志复制过去,并执行alterdatabaseregisterlogicallogfile‘filename’语句手动的注册
查看逻辑standby的日志应用情况
STANDBY>selectapplied_scn,latest_scnfromv$logstdby_progress;
APPLIED_SCNLATEST_SCN
---------------------
540775540775
如果两结果一致则证明日志已经应用完毕,如果不一致,则需要启动SQL应用
配置一下日志的归档路径
STANDBY>altersystemsetlog_archive_dest_1='location=/u01/log/arch_onlinevalid_for=(online_logfiles,all_roles)db_unique_name=orcl_std';
Systemaltered.
STANDBY>altersystemsetlog_archive_dest_3='location=/u01/log/arch_pri_logvalid_for=(standby_logfiles,standby_roles)db_unique_name=orcl_std’;
Systemaltered.
STANDBY>showparameterarchive_dest;
NAMETYPEVALUE
-----------------------------------------------------------------------------
log_archive_deststring
log_archive_dest_1stringlocation=/u01/log/arch_online
valid_for=(online_logfiles,al
l_roles)db_unique_name=orcl_s
td
log_archive_dest_10string
log_archive_dest_2stringservice=orcl_p_tnsarchvalid_
for=(online_logfiles,primary_r
ole)db_unique_name=orcl_pri
log_archive_dest_3stringlocation=/u01/log/arch_pri_log
valid_for=(standby_logfiles,
standby_roles)db_unique_name=
orcl_std
转换之前先看一下数据库的角色
STANDBY>selectdatabase_role,force_loggingfromv$database;
DATABASE_ROLEFOR
-------------------
LOGICALSTANDBYYES
开始转换
STANDBY>alterdatabaseactivatelogicalstandbydatabasefinishapply;
Databasealtered.
该语句会听着该standby数据库的RFS服务,并应用当前已经接收但并未应用的日志,并执行完毕手关闭SQL应用
再次查看数据库的角色
STANDBY>selectdatabase_role,force_loggingfromv$database;
DATABASE_ROLEFOR
-------------------
PRIMARYYES
将该新的primary数据库加入到原来的DataGuard环境中
在standby数据库执行:
SQL>altersessiondisableguard;
Sessionaltered.
SQL>createdatabaselinkdg_recoverconnecttoscottidentifiedbyoracleusing'orcl_p_tns';
Databaselinkcreated.
注意连接用户必须有select_catalog_role的权限,可以在想要的standby数据库端赋予权限
SQL>grantselect_catalog_roletoscott;
Grantsucceeded.
SQL>altersessionenableguard;
Sessionaltered.
SQL>selectsysdatefromdual@dg_recover;
SYSDATE
---------
09-MAR-14
将该standby数据库加入新的DG环境中去
SQL>alterdatabasestartlogicalstandbyapplynewprimarydg_recover;
Databasealtered.
要是有多个standby只需将它们添加到新的primary即可。
三:
逻辑DG的相关视图
DBA_LOGSTDBY_EVENTS视图记录了逻辑STANDBY的操作日志,该视图默认的只记录100条信息
SQL>selectevent_time,statusfromdba_logstdby_events;
EVENT_TIMSTATUS
---------------------------------------------------------------------
08-MAR-14ORA-16111:
logminingandapplysettingup
08-MAR-14ORA-16128:
Userinitiatedstopapplysuccessfullycompleted
08-MAR-14ORA-16111:
logminingandapplysettingup
08-MAR-14ORA-16128:
Userinitiatedstopapplysuccessfullycompleted
08-MAR-14ORA-16111:
logminingandapplysettingup
08-MAR-14ORA-16128:
Userinitiatedstopapplysuccessfullycompleted
08-MAR-14ORA-16111:
logminingandapplysettingup
08-MAR-14ORA-16128:
Userinitiatedstopapplysuccessfullycompleted
08-MAR-14ORA-16111:
logminingandapplysettingup
08-MAR-14ORA-16128:
Userinitiatedstopapplysuccessfullycompleted
08-MAR-14ORA-16111:
logminingandapplysettingup
修改该视图保留时间的最大条数
修改前需关闭SQL应用
STANDBY>alterdatabasestoplogicalstandbyapply;
Databasealtered.
STANDBY>EXECDBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED','200');
PL/SQLproceduresuccessfullycompleted.
启动SQL应用并查看保留条数
STANDBY>alterdatabasestartlogicalstandbyapplyimmediate;
Databasealtered.
STANDBY>select*fromv$logstdby_statswherenamelike'%recorded%';
NAMEVALUE
----------------------------------------------------------------------
maximumeventsrecorded200
DBA_LOGSTDBY_LOG视图显示归档日志的SQL应用情况
SQL>selectSEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIEDfromdba_logstdby_log;
SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#APPLIED
-------------------------------------------
14494521494573CURRENT
15494573545758NO
16545758572067NO
上面的结果中出现应用结果为NO的情况,是因为在数据库重启期间SQL应用未开启,开启SQL应用即可
SQL>alterdatabasestartlogicalstandbyapplyimmediate;
Databasealtered.
稍等片刻之后
SQL>selectSEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIEDfromdba_logstdby_log;
SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#APPLIED
-------------------------------------------
14494521494573CURRENT
15494573545758CURRENT
16545758572067NO
SQL>selectSEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIEDfromdba_logstdby_log;
SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#APPLIED
-------------------------------------------
16545758572067CURRENT
最后完成SQL应用完全完成后发现少了几组重做日志信息,是因为默认情况下stanbyd数据库SQL应用之后自动将废弃的归档文件删除,当然,也可以通过设置相关参数使之保留
取消自动删除归档文件设置:
SQL>execDBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE','FALSE');
PL/SQLproceduresuccessfullycompleted.
查看一下修改后的standby参数
SQL>select*fromdba_logstdby_parameters;
NAMEVALUE
--------------------------------------------------
PREP_DICT_RECEIVED
LMNR_SID1
GUARD_STANDBYREADY
FIRST_SCN491619
PRIMARY1369377775
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 逻辑Data Guard维护篇 逻辑 Data Guard 维护
![提示](https://static.bingdoc.com/images/bang_tan.gif)