您现在的位置是:首页 >学无止境 >Oracle DataGuard奇怪的ORA-16494错误网站首页学无止境
Oracle DataGuard奇怪的ORA-16494错误
简介Oracle DataGuard奇怪的ORA-16494错误
Oracle数据库DataGuard数据无法同步,主库查询v$archive_dest出现ORA-16494错误。
数据库版本Oracle 12.1.0.2.0:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
0
PL/SQL Release 12.1.0.2.0 - Production
0
CORE 12.1.0.2.0 Production
0
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
0
NLSRTL Version 12.1.0.2.0 - Production
0
检查主库DG参数:
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(orcl,orcladg)
SQL>
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=orcladg LGWR ASYNC VAL
ID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=orcladg
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> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
检查备库参数:
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(orcl,orcladg)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=orcl LGWR ASYNC VALID_
FOR=(ONLINE_LOGFILES,PRIMARY_R
OLE) DB_UNIQUE_NAME=orcl
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> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string pdborcl
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcladg
检查主备库之间的网络连通性
主库:
C:UsersAdministrator>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 08-5月 -2023 22:42:27
Copyright (c) 1997, 2014, Oracle. All rights reserved.
已使用的参数文件:
C:apppdborclproduct12.1.0dbhome_1
etworkadminsqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-VJ841V9N3PU)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
OK (20 毫秒)
C:UsersAdministrator>tnsping orcladg
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 08-5月 -2023 22:42:39
Copyright (c) 1997, 2014, Oracle. All rights reserved.
已使用的参数文件:
C:apppdborclproduct12.1.0dbhome_1
etworkadminsqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.188.201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl)))
OK (20 毫秒)
备库:
C:UsersAdministrator>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 08-5月 -2023 22:42:27
Copyright (c) 1997, 2014, Oracle. All rights reserved.
已使用的参数文件:
C:apppdborclproduct12.1.0dbhome_1
etworkadminsqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-VJ841V9N3PU)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
OK (20 毫秒)
C:UsersAdministrator>tnsping orcladg
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 08-5月 -2023 22:42:39
Copyright (c) 1997, 2014, Oracle. All rights reserved.
已使用的参数文件:
C:apppdborclproduct12.1.0dbhome_1
etworkadminsqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.188.201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl)))
OK (20 毫秒)
主备库之间网络是畅通的。
主库切换日志,查询v$archive_dest:
SQL> alter system switch logfile;
系统已更改。
出现ORA-16494错误,该错误在网上找不到,而且MOS上也没有相关的记载。
再次检查备库,会不会是PDB模式的,而且有名为PDBORCL的PDB,导致主库通过tns直接连到了PDB中。
备库查询:
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED MOUNTED
3 PDBORCL MOUNTED
果然有个叫PDBORCL的PDB。
查看数据库的数据库名:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBY
所以,如果想连到CDB中,应该将service_names指定为orcl,由于是备库,可设置为rzorcl或orclstd等,方便识别,但是一定要与PDBORCL不同名。
修改备库service_names
SQL> alter system set service_names='rzorcl';
在修改主库和备库中tnsnames.ora文件中备库连接串中SERVICE=RZORCL。
再次尝试切换日志,错误消失。
系统已更改。
SQL>
SQL>
SQL>
SQL> select error from v$archive_dest;
ERROR
-----------------------------------------------------------------
ERROR
-----------------------------------------------------------------
ERROR
-----------------------------------------------------------------
已选择 31 行。
SQL>
再次查看备库日志接收情况:
SQL> select process,sequence#,block# from v$managed_standby;
PROCESS SEQUENCE# BLOCK#
--------- ---------- ----------
ARCH 103988 43008
ARCH 0 0
ARCH 103987 4096
ARCH 103989 40960
MRP0 103990 29955
RFS 0 0
RFS 103990 29955
RFS 0 0
RFS 0 0
9 rows selected.
备库日志接收正常,问题解决。
风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。