DataGuard之switchover_status狀態not allowed解決過程
本在VB中搭建了一套dataguard,前兩天PC機自動重啟,當時恰在試驗主備庫間的switchover和failover，於是重新啟動報錯：主庫switchover_status狀態一直是:not allowed狀態.
---->>查詢官方說明得知:
NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
------->>DG配置如下:
|
|
主機1 |
主機2 |
|
作業系統 |
Redhat5 64位 |
Redhat5 64位 |
|
主機名 |
dawn.com |
dg.com |
|
IP |
192.168.11.110 |
192.168.11.112 |
|
資料庫軟體版本 |
oracle 11.2.0.1.0 |
oracle 11.2.0.1.0 |
|
ORACLE_BASE |
/oracle/app/oracle/ |
/oracle/app/oracle/ |
|
ORACLE_HOME |
$ORACLE_BASE/product/11.2.0/db_1 |
$ORACLE_BASE/product/11.2.0/db_1 |
|
ORACLE_SID |
primary |
standby |
|
閃回區 |
2G |
2G |
|
歸檔 |
開啟 |
開啟 |
----------------------------->>下面摘錄出解決過程:
主庫:
[oracle@dawn dbs]$ tnsping standby
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2014 15:07:21
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))
OK (30 msec)
[oracle@dawn dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 25 15:07:41 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from database;
select open_mode from database
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(primary,standby)
SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=/u01/app/oracle/orada
ta/arch
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=standby LGWR SYNC AFFI
RM valid_for=(online_logfiles,
primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL> select status,error from v$archive_dest;
SQL> select status,error from v$archive_dest;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
BAD PARAM
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
STATUS ERROR
--------- -----------------------------------------------------------------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
STATUS ERROR
--------- -----------------------------------------------------------------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
31 rows selected.
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string primary
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby LGWR SYNC AFFI
RM valid_for=(online_logfiles,
primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28 string
log_archive_dest_29 string
SQL> alter system set log_archive_dest_2='service=standby arch async valid_for(online_logfiles,primary_role) db_unique_name=standby';
alter system set log_archive_dest_2='service=standby arch async valid_for(online_logfiles,primary_role) db_unique_name=standby'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_2 contains repeated or conflicting
attributes
SQL> alter system set log_archive_dest_2='service=standby arch async valid_for(online_logfiles,primary_role)';
alter system set log_archive_dest_2='service=standby arch async valid_for(online_logfiles,primary_role)'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_2 contains repeated or conflicting
attributes
SQL> alter system set log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role)';
alter system set log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role)'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16052: DB_UNIQUE_NAME attribute is required.
SQL> alter system set log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby';
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> select status,error from v$archive_dest;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
STATUS ERROR
--------- -----------------------------------------------------------------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
STATUS ERROR
--------- -----------------------------------------------------------------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
31 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby';
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
------------------>>備庫:
[oracle@dg trace]$ tnsping primary
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2014 15:14:56
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (40 msec)
[oracle@dg trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 25 15:15:02 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dg trace]$ tail -f alert_standby.log
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 33-33
DBID 1685534234 branch 848051550
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
[oracle@dg trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 25 15:20:03 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter client
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
fal_client string primary
SQL>
SQL> alter system set fal_client=standby;
System altered.
SQL> alter system set fal_server=primary;
System altered.
----------------------------------------->>主庫狀態對照:
[oracle@dawn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 25 14:47:52 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY NOT ALLOWED
SQL>
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY RESOLVABLE GAP
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1280682/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 物理dataguard 正常切換 角色轉換，switchover_status 狀態改變
- 物理dataguard 正常切換 腳色轉換，switchover_status 狀態改變
- Physical Standby Switchover_status Showing Not Allowed
- Physical Standby Switchover_status Showing Not Allowed._1392763.1
- 透過crontab更改DataGuard物理庫managed recover狀態
- 通過crontab更改DataGuard物理庫managed recover狀態
- Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)
- Oracle資料庫啟動過程及狀態詳解Oracle資料庫
- 【DataGuard】Oracle DataGuard 日誌傳輸狀態監控Oracle
- Flutter狀態管理Provider(二)過程分析FlutterIDE
- 上手MySQL之解決問題：not allowed to connect to this MySQL serverMySqlServer
- Data Pump Import速度問題之解決過程Import
- oracle 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- 【DATAGUARD 學習】學習DATAGUARD 過程中遇到的問題
- Android透明狀態列解決方案Android
- 解決IIS無響應假死狀態
- 關於 鎖的四種狀態與鎖升級過程 圖文詳解
- crontab失敗的解決過程
- iTunes Connect(二) —— 應用上架過程中App狀態指南APP
- oracle crs監聽狀態為offline處理過程Oracle
- 啟動dataguard備庫到read-only狀態
- oracle10g 物理standby dataguard 建立過程Oracle
- 虛擬機器實現dataguard配置過程虛擬機
- Elasticsearch叢集狀態健康值處於red狀態問題分析與解決（圖文詳解）Elasticsearch
- 狀態機解決複雜邏輯及使用
- Docker容器的退出狀態碼及解決方法Docker
- HTTP協議無狀態，該怎麼解決？HTTP協議
- 一個lua問題解決過程
- ORA-01461解決過程
- 分散式儲存Ceph之PG狀態詳解分散式
- Oracle 11G DataGuard重啟詳細過程Oracle
- Oracle RAC之－－安裝過程中碰到的問題及解決方法Oracle
- 解決crs_stat狀態為UNKNOWN問題
- Flutter 狀態管理之BLoCFlutterBloC
- react之redux狀態管理ReactRedux
- 一個 ExpressionChangedAfterItHasBeenCheckedError 錯誤的解決過程ExpressError
- ORA-2049錯誤解決過程
- ORA-01461的解決過程~~