主庫磁盤問題，導致主庫宕機，因為歸檔還沒有應用，導致備庫無法轉為主庫

先查看一下備庫當前的信息：

SQL>?select?*?from?v$version; BANNER ---------------------------------------------------------------------Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.3.0?-?64bit?Production PL/SQL?Release?11.2.0.3.0?-?Production CORE????11.2.0.3.0??????Production TNS?for?Linux:?Version?11.2.0.3.0?-?Production NLSRTL?Version?11.2.0.3.0?-?Production

?

查看當前數據庫的狀態：

SQL>?select?open_mode?from?v$database; OPEN_MODE -------------------- READ?ONLY

?該啟動狀態分為NOMOUNT,MOUNT,READ ONLY，READ WRITE四種狀態

?

查看DG主備庫的切換狀態：

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

DG失效切換狀態包括NOT ALLOWED，SESSIONS ACTIVE，TO STANDBY，TO PRIMARY。當主庫為TO STANDBY時，表示主庫可以進行主備角色的切換。

ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PHYSICAL?STANDBY;

如遇到以下錯誤提示，說明主備間還有活動的會話鏈接

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

可以通過以下語句強制關閉活動會話：

ALTER?DATABASE?COMMIT?TO?SWITCHOVER?TO?PHYSICAL?STANDBY?WITH?SESSION?SHUTDOWN;

ORA-16139: media recovery required

ALTER?DATABASE?RECOVER?MANAGED?STANDBY?DATABASE?FINISH?SKIP?STANDBY?LOGFILE;

?

主庫角色切換為備庫后，還有部分參數需要修改。

1、修改獲取歸檔的C/S端，這里的FAL（Fetch Archive Log）

SQL>?show?parameters?fal NAME???????TYPE????????VALUE ----------?-----------?-------- fal_client?string??????SID_PRI fal_server?string??????SID_DG

2、修改主庫log_archive_dest_state_n及log_archive_dest_n。

SQL>?show?parameters?log_archive_dest_state_2 NAME??????????????????????TYPE????????VALUE -------------------------?-----------?-------log_archive_dest_state_2??string??????DEFERSQL>?show?parameters?log_archive_dest_2 NAME???????????????TYPE????????VALUE ------------------?-----------?------------------log_archive_dest_2?string??????service=SID_DG

3、修改備庫log_archive_dest_state_n及log_archive_dest_n。

SQL>?show?parameters?log_archive_dest_state_2 NAME??????????????????????TYPE????????VALUE -------------------------?-----------?-------log_archive_dest_state_2??string??????ENABLESQL>?show?parameters?log_archive_dest_2 NAME???????????????TYPE????????VALUE ------------------?-----------?------------------log_archive_dest_2?string??????service=SID_PRI

4、查看備庫狀態 SESSIONS ACTIVE 切換角色為主庫

alter?database?commit?to?switchover?to?primary; shutdown?immediate startup?mount alter?database?open?read?write

5、查看備庫的文件管理狀態

SQL>?show?parameters?standby_file_management NAME????????????????????TYPE????????VALUE -----------------------?-----------?---------standby_file_management?string??????AUTO

6、切換備庫進入恢復模式：

alter?database?recover?managed?standby?database?disconnect?from?session;

7、查看主備歸檔同步情況：

SQL>?select??process,?status,sequence#,block#,blocks,?delay_mins?from?v$managed_standby; PROCESS???STATUS????????SEQUENCE#?????BLOCK#?????BLOCKS?DELAY_MINS ---------?------------?----------?----------?----------?---------- ARCH??????CONNECTED?????????????0??????????0??????????0??????????0 ARCH??????CONNECTED?????????????0??????????0??????????0??????????0 ARCH??????CONNECTED?????????????0??????????0??????????0??????????0 ARCH??????CONNECTED?????????????0??????????0??????????0??????????0 MRP0??????WAIT_FOR_LOG???????8048??????????0??????????0??????????0 RFS???????IDLE??????????????????0??????????0??????????0??????????0 RFS???????IDLE???????????????8048?????170025????????824??????????0 RFS???????IDLE??????????????????0??????????0??????????0??????????0

只要備庫的MRP（Managed Recovery Process）進程啟動，就表示歸檔正常進行

SQL>?select?dest_name,status,target,archiver,schedule,?valid_type,valid_role,db_unique_name?from?v$archive_dest?where?dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME?????????????????STATUS????TARGET??ARCHIVER???SCHEDULE?VALID_TYPE??????VALID_ROLE???DB_UNIQUE_NAME -------------------------?---------?-------?----------?--------?---------------?------------?------------------------------ LOG_ARCHIVE_DEST_2????????VALID?????STANDBY?LGWR???????ACTIVE???ALL_LOGFILES????ALL_ROLES????NONE

?

轉載于:https://blog.51cto.com/onlinekof2001/1589080

總結

以上是生活随笔為你收集整理的ORACLE DataGuard主备切换的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。