oracle not allow,DataGuard之switchover_status状态not allowed解决过程

884514acfddf4a6f2c383c0a99dc64b3.png

ea4348ea5d50c64714d9d00207fac04d.png

ffd95cc3d5de4b2b0e9e86f44b76e79b.png

本在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

操作系统

Redhat564位

Redhat564位

主机名

dawn.com

dg.com

IP

192.168.11.110

192.168.11.112

数据库软件版本

oracle11.2.0.1.0

oracle11.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

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值