mysql ora01031_as sysdba 出现ORA-01031: insufficient privileges 解决办法

最近Dataguard做了好多测试，刚刚开始的时候出现最多的就是监听、tnsnames.ora配置有问题导致各种连接报错。

总结一下各种报错的处理方法：

情景1：主库是RAC，备库是single

oracle同一个报错引起的原因可能是很多，比如ORA-01031、ORA-12514。所以本文不打算针对报错去解决问题，而是讨论一下配置Dataguard要注意的地方。

情景1：主库是RAC，备库是single

1.pfile中dg参数注意service name，详见我之前的一篇博文

主要报错

ERROR at line 1:ORA-10458: standby database requires recoveryORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '/oracle/oradata/BSTYLE/system.309.766573511'

alter.log有报错

Error 12154 received logging on to the standby

2.备库静态监听配置

新手首先要搞清楚 动态监听、静态监听的区别 因为做备库同步的时候，备库一定是要配置静态监听的。这里我贴个能用的备库Listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))

)

)

)

3. 备库密码文件的创建，可以直接复制主库的密码文件。如果你复制的是节点1那么rman做duplicate的时候就连节点1，否则会报密码文件错误。

情景2 主库是RAC,备库也是RAC

1.静态监听的配置与单实例不同了，这里我贴个能用的备库Listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0.3/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl1)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

)

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))

)

LISTENER_SCAN2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))

)

ADR_BASE_LISTENER_SCAN3 = /u01/app/grid

LISTENER_SCAN1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))

)

ADR_BASE_LISTENER_SCAN2 = /u01/app/grid

ADR_BASE_LISTENER_SCAN1 = /u01/app/grid

根据自己环境修改相关的地方，修改后用crsctl重启监听

crsctlstop res ora.LISTENER.lsnr

crsctlstart res ora.LISTENER.lsnr

crsctlstatus res ora.LISTENER.lsnr

lsnrcltstatus

2. 如果你遇到使用sqlplus sys/oracle@orcl as sysdba不能登录而sqlplus sys/oracle@orcl可以，或者在备库没有nomount便不能连接的时候，请检查一下静态监听的配置，ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1这个是oracle用户下的$ORACLE_HOME而不是grid用户的$ORACLE_HOME