关于DATAGUARD主备库网络连通性的问题

显示全部楼层 · 2015-3-4 14:55:27

搭建dg的过程中卡在主备库无法实现网络连通性的问题，大家帮忙看看哪里出问题了。说明：主库的ip：192.168.133.100备库的ip：192.168.133.101

主库的listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

主库的tnsnames.ora:
PDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb)
)
)
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdb)
)
)

备库的listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

备库的tnsnames.ora:
PDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb)
)
)
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdb)
)
)

接下来在主库上尝试测试pdb和sdb的连通性，但是总是出现权限的问题：
SQL> conn sys/oracle@pdb as sysdba
ERROR:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

备库也是一样的情况！
接下来查看用户oracle的属相：
[oracle@dg1 admin]$ id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

并没有问题。
接着再去查看主库中remote_login_passwordfile的值：
SQL> show parameter remote_login_passwordfile;
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile
stringEXCLUSIVE

接着去查看密码文件
[oracle@dg1 dbs]$ ll|grep orapworcl
-rw-r----- 1 oracle oinstall 1536 Jun 14 16:31 orapworcl

名字也没有错。
真的不知道什么问题了。
望各位大虾指点！！！

千问 · 2015-3-4 14:55:27

主备库主库的tnsnames.ora: (SERVICE_NAME = orcl)
lsnrctl status看看侦听的状态
主备tnsping一下看看通否再尝试sysdba登录

千问 · 2015-3-4 14:55:27

houyichong 发表于 2015-6-14 18:02
主备库主库的tnsnames.ora: (SERVICE_NAME = orcl)
lsnrctl status看看侦听的状态
主库的监听器监听状态：
[oracle@dg1 dbhome_1]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-JUN-2015 19:24:32
Copyright (c) 1991, 2009, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date
14-JUN-2015 18:34:24
Uptime
0 days 0 hr. 50 min. 7 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
=====================================================
主库备库的tnsping都是没有问题的，说明配置的tnsnames.ora里面没有错误：
主库：
[oracle@dg1 dbhome_1]$ tnsping dg1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-JUN-2015 19:25:28
Copyright (c) 1997, 2009, Oracle.All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)
[oracle@dg1 dbhome_1]$ tnsping dg2
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-JUN-2015 19:25:32
Copyright (c) 1997, 2009, Oracle.All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.101)(PORT=1521)))
OK (0 msec)
备库：
[oracle@dg2 admin]$ tnsping dg1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-JUN-2015 19:25:50
Copyright (c) 1997, 2009, Oracle.All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.100)(PORT=1521)))
OK (10 msec)
[oracle@dg2 admin]$ tnsping dg2
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-JUN-2015 19:25:53
Copyright (c) 1997, 2009, Oracle.All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)

千问 · 2015-3-4 14:55:27

看看你的/etc/hosts文件。
dg1
dg2
不要指向127.0.0.1；

千问 · 2015-3-4 14:55:27

主库的DB_NAME 和备库的 DB_NAME 是一致的，只是UNIQUE NAME 不一样，SID NAME 不一样，你主库DB GLOBAL DB NAME PDB, 备库 GLOBAL DB NAME 是SDB，感觉就不太对。
并且你TNSPING DG2，怎么从127.0.0.1 连上去的，这个感觉也不对