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

最新推荐文章于 2024-04-02 16:58:29 发布

茶柯一

最新推荐文章于 2024-04-02 16:58:29 发布

阅读量2.9k

点赞数

文章标签： oracle not allow

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

主机名

dawn.com

dg.com

192.168.11.110

192.168.11.112

数据库软件版本

oracle11.2.0.1.0

ORACLE_BASE

/oracle/app/oracle/

ORACLE_HOME

$ORACLE_BASE/product/11.2.0/db_1

ORACLE_SID

primary

standby

闪回区

归档

开启

----------------------------->>下面摘录出解决过程:

主库:

[oracle@dawn dbs]$ tnsping standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-SEP-2014 15:07:21

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

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;

STATUS ERROR

--------- -----------------------------------------------------------------

VALID

BAD PARAM

INACTIVE

STATUS ERROR

--------- -----------------------------------------------------------------

INACTIVE

STATUS ERROR

--------- -----------------------------------------------------------------

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

INACTIVE

STATUS ERROR

--------- -----------------------------------------------------------------

INACTIVE

STATUS ERROR

--------- -----------------------------------------------------------------

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

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

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

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

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
评论
oracle not allow,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 datab...
复制链接

扫一扫