DataGuard之switchover_status狀態not allowed解決過程

dawn009發表於2014-09-25


本在VB中搭建了一套dataguard,前兩天PC機自動重啟,當時恰在試驗主備庫間的switchover和failover,於是重新啟動報錯:主庫switchover_status狀態一直是:not allowed狀態.
----&gt>查詢官方說明得知:
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.

-------&gt>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

歸檔

開啟

開啟

-----------------------------&gt>下面摘錄出解決過程:
主庫:
[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.

------------------&gt>備庫:
[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.

-----------------------------------------&gt>主庫狀態對照:
[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章