数据库版本:12.1.0.2.0

OS版本:Red Hat Enterprise Linux Server release 7.2 (Maipo)

监听日志报错

17-SEP-2019 14:32:24 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=test))(SERVER=DEDICATED)(SERVICE_NAME=sapdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.2.22)(PORT=39571)) * establish * sapdb * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12536: TNS:operation would block

TNS-12560: TNS:protocol adapter error

TNS-00506: Operation would block

Linux Error: 11: Resource temporarily unavailable

17-SEP-2019 14:32:25 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=test))(SERVER=DEDICATED)(SERVICE_NAME=sapdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.2.259)(PORT=59488)) * establish * sapdb * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12536: TNS:operation would block

TNS-12560: TNS:protocol adapter error

TNS-00506: Operation would block

Linux Error: 11: Resource temporarily unavailable

---参考官方文档

Ora-12518 on Oracle 12c Multitenant Architecture (文档 ID 2252001.1)

SOLUTION

PDB instance_name 'wd1' will always exists within in the CDB instance_name 'ct1'. Configure the SID for PDB service name wd1 has shown below.

SID_LIST_LSNRPD =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ct1)

(ORACLE_HOME = d:\orasfw\prd1)

(SID_NAME = ct1)

)

(SID_DESC =

(GLOBAL_DBNAME = wd1) <<<< PDB Instance_name

(ORACLE_HOME = d:\orasfw\prd1)

(SID_NAME = ct1) <<<< CDB Instance_name

)

)

---实际操作过程

---停 OGG

stop mgr

stop *

--停DG

--主库

alter system set log_archive_dest_state_2=defer;

---备库取消应用

alter database recover managed standby database cancel;

2.停监听

lsnrctl status

[oracle@sadb ggs12]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-SEP-2019 02:29:45

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.2.90)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 01-JUN-2018 02:10:55

Uptime 474 days 0 hr. 18 min. 50 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/sadb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.2.90)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "sadb" has 2 instance(s).

Instance "sadb", status UNKNOWN, has 1 handler(s) for this service...

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "salesdbXDB" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sadb_DGB" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sadb_DGMGRL" has 1 instance(s).

Instance "sadb", status UNKNOWN, has 1 handler(s) for this service...

Service "sapdb" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sastdydb" has 1 instance(s).

Instance "sastdydb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

netstat -lanp |grep 1521

lsnrctl stop

ps -ef|grep -i local=no

--kill -9 `ps -ef|grep oraclesasdb|grep LOCAL=NO|grep -v grep|awk '{print $2}'`

---修改监听

cd $ORACLE_HOME/network/admin

cp listener.ora listener.ora_0917

---原始文件为；

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =sadb)

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

(SID_NAME = sadb)

)

(SID_DESC =

(GLOBAL_DBNAME =sastdydb)

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

(SID_NAME = sastdydb)

)

(SID_DESC =

(GLOBAL_DBNAME =sadb_DGMGRL)

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

(SID_NAME = sadb)

)

)

---打算修改为

vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =sadb)

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

(SID_NAME = sadb)

)

(SID_DESC =

(GLOBAL_DBNAME =sastdydb)

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

(SID_NAME = sastdydb)

)

(SID_DESC =

(GLOBAL_DBNAME =sadb_DGMGRL)

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

(SID_NAME = sadb)

(SID_DESC =

(GLOBAL_DBNAME =sapdb)

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

(SID_NAME = sadb)

)

)

)

----启动监听

lsnrctl status

lsnrctl start

[oracle@sadb ggs12]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-SEP-2019 02:34:12

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 18-SEP-2019 02:32:55

Uptime 0 days 0 hr. 1 min. 17 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/sadb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sadb)(PORT=1521)))

Services Summary...

Service "sadb" has 2 instance(s).

Instance "sadb", status UNKNOWN, has 1 handler(s) for this service...

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sadbXDB" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "salesdb_DGB" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sadb_DGMGRL" has 1 instance(s).

Instance "sadb", status UNKNOWN, has 1 handler(s) for this service...

Service "sapdb" has 1 instance(s).

Instance "sadb", status READY, has 1 handler(s) for this service...

Service "sastdydb" has 1 instance(s).

Instance "sastdydb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

--启动 OGG

start mgr

start *

--主库

alter system set log_archive_dest_state_2=enable;

---启动备库应用进程

alter database recover managed standby database using current logfile disconnect from session;

查询备库日志是否应用

select sequence#,applied from v$archived_log;

---查询备库进程是否正常

select process,client_process,sequence#,status from v$managed_standby;

select ERROR from v$archive_dest;

select dest_name,status,error from v$archive_dest where rownum<3;

---查询主备库状态

select DATABASE_ROLE,SWITCHOVER_STATUS from v$database;