Orcl-Experts :: Your Choice.......................Your Info

Main Menu

Main Menu

Hot Jobs

ORA- Search (Press GO)

Doc Search

Login

Scripts

FAQ

Forum

ORA-38500: USING CURRENT LOGFILE option not available without stand

ORA-32018: parameter cannot be modified in memory on another instance

Message:ORA-01034: ORACLE not available,ORA-27123: unable to attach to shared memory segment

We applied the CPU Patch in 2 node RAC cluster (10.2.0.2, Linux As3.0). DBA used sqlplus to bring up the instances. We started the monitoring and found out that the monitoring is alerting the below messages:

ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux Error: 22: Invalid argument
Additional information: 1
Additional information: 1081344

All the instances were up. After 30-40 minutes analysis we found the workaround to resolve this issue.

I believe that there is an unknown bug with sqlplus vs. svrctl -. I am sharing the workaround we used to resolve the issue.

Here is what happened when we used sqlplus:

SQL> startup
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 1262200 bytes
Variable Size 205524360 bytes
Database Buffers 385875968 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
[oracle@bev2 oracle]$ sqlplus system/xxxx@nep1

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jun 2 16:05:36 2007

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux Error: 22: Invalid argument
Additional information: 1
Additional information: 1081344

Our workaround:

Use srvctl to bring up the instances

[oracle@expeerts1 oracle]$ srvctl start instance -d nep -i nep1 -o open

[oracle@experts2 oracle]$ sqlplus system/xxx@nep1

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jun 2 16:10:47 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

ORA-03135: connection lost contact

How to resolve ORA-03135: connection lost contact

Test cases:

At client side set
trace_directory_client =/data02/home/oracle/network/trace
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = CLIENT
TRACE_TIMESTAMP_ CLIENT = ON
TRACE_UNIQUE_CLIENT = ON

1. I reproduced the error ORA-03135: connection lost contact. No changes made in the existing setup

I made a test connection from test server to rman server at Fri Oct 19 02:07:17

[oracle@orcl-experts.info ~]$ sqlplus test/xxxxx@recoverycatalog

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 02:07:17 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from dual;

At 19-OCT-2007 03:21:41:341,

SQL> select * from dual;

select * from dual

ERROR at line 1:

ORA-03135: connection lost contact

[19-OCT-2007 03:21:41:341] nsdo: nsctx: state=8, flg=0x400d, mvd=0
[19-OCT-2007 03:21:41:341] nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
[19-OCT-2007 03:21:41:341] nsdo: switching to application buffer
[19-OCT-2007 03:21:41:341] nsrdr: entry
[19-OCT-2007 03:21:41:341] nsrdr: recving a packet
[19-OCT-2007 03:21:41:341] nsprecv: entry
[19-OCT-2007 03:21:41:341] nsprecv: reading from transport...
[19-OCT-2007 03:21:41:341] nttrd: entry
[19-OCT-2007 03:37:06:855] ntt2err: entry
[19-OCT-2007 03:37:06:855] ntt2err: exit
[19-OCT-2007 03:37:06:855] nttrd: socket 9 had bytes read=0
[19-OCT-2007 03:37:06:855] nttrd: exit
[19-OCT-2007 03:37:06:855] nsprecv: error exit
[19-OCT-2007 03:37:06:855] nserror: entry
[19-OCT-2007 03:37:06:855] nserror: nsres: id=0, op=68, ns=12535, ns2=12560; nt[0]=505, nt[1]=110, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[19-OCT-2007 03:37:06:855] nsrdr: error exit
[19-OCT-2007 03:37:06:855] nsdo: nsctxrnk=0
[19-OCT-2007 03:37:06:855] nsdo: error exit
[19-OCT-2007 03:37:06:855] nioqrc: wanted 1 got 0, type 0
[19-OCT-2007 03:37:06:855] nioqper: error from nioqrc
[19-OCT-2007 03:37:06:855] nioqper: ns main err code: 12535
[19-OCT-2007 03:37:06:855] nioqper: ns (2) err code: 12560
[19-OCT-2007 03:37:06:855] nioqper: nt main err code: 505
[19-OCT-2007 03:37:06:855] nioqper: nt (2) err code: 110
[19-OCT-2007 03:37:06:855] nioqper: nt OS err code: 0
[19-OCT-2007 03:37:06:855] nioqer: entry
[19-OCT-2007 03:37:06:855] nioqer: incoming err = 12151
[19-OCT-2007 03:37:06:855] nttaddr2bnd: entry
[19-OCT-2007 03:37:06:855] snlinGetNameInfo: entry
[19-OCT-2007 03:37:06:855] snlinGetNameInfo: Using numeric form of host's address 10.47.3.11
[19-OCT-2007 03:37:06:855] snlinGetNameInfo: exit

2.. Changed SQLNET.EXPIRE_TIME =10

No errors

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 19 03:50:27 2007

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> ! date

Fri Oct 19 03:51:01 UTC 2007

SQL> select * from dual;

SQL> ! date

Fri Oct 19 05:03:50 UTC 2007

SQL> select * from dual;

SQL> ! date

Fri Oct 19 12:54:48 UTC 2007

SQL> exit

[19-OCT-2007 03:50:27:168] nsdofls: DATA flags: 0x0
[19-OCT-2007 03:50:27:168] nsdofls: sending NSPTDA packet
[19-OCT-2007 03:50:27:168] nspsend: entry
[19-OCT-2007 03:50:27:168] nspsend: plen=52, type=6
[19-OCT-2007 03:50:27:168] nttwr: entry
[19-OCT-2007 03:50:27:168] nttwr: socket 9 had bytes written=52
[19-OCT-2007 03:50:27:168] nttwr: exit
[19-OCT-2007 03:50:27:168] nspsend: packet dump
[19-OCT-2007 03:50:27:168] nspsend: 00 34 00 00 06 00 00 00 |.4......|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 03 68 1A 01 00 00 |...h....|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 00 04 00 00 00 01 |........|
[19-OCT-2007 03:50:27:168] nspsend: 00 00 00 00 |.... |
[19-OCT-2007 03:50:27:168] nspsend: 52 bytes to transport
[19-OCT-2007 03:50:27:168] nspsend: normal exit
[19-OCT-2007 03:50:27:168] nsdofls: exit (0)
[19-OCT-2007 03:50:27:168] nsdo: nsctxrnk=0
[19-OCT-2007 03:50:27:168] nsdo: normal exit
[19-OCT-2007 03:50:27:168] nsdo: entry
[19-OCT-2007 03:50:27:168] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
[19-OCT-2007 03:50:27:168] nsdo: rank=64, nsctxrnk=0
[19-OCT-2007 03:50:27:168] nsdo: nsctx: state=8, flg=0x400d, mvd=0
[19-OCT-2007 03:50:27:168] nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
[19-OCT-2007 03:50:27:168] nsdo: switching to application buffer
[19-OCT-2007 03:50:27:168] nsrdr: entry
[19-OCT-2007 03:50:27:168] nsrdr: recving a packet
[19-OCT-2007 03:50:27:168] nsprecv: entry
[19-OCT-2007 03:50:27:168] nsprecv: reading from transport...
[19-OCT-2007 03:50:27:168] nttrd: entry
[19-OCT-2007 03:50:27:168] nttrd: socket 9 had bytes read=22
[19-OCT-2007 03:50:27:168] nttrd: exit
[19-OCT-2007 03:50:27:168] nsprecv: 22 bytes from transport
[19-OCT-2007 03:50:27:168] nsprecv: tlen=22, plen=22, type=6
[19-OCT-2007 03:50:27:168] nsprecv: packet dump
[19-OCT-2007 03:50:27:168] nsprecv: 00 16 00 00 06 00 00 00 |........|
[19-OCT-2007 03:50:27:168] nsprecv: 00 00 08 02 00 00 00 09 |........|
[19-OCT-2007 03:50:27:168] nsprecv: 05 00 00 00 18 00 |...... |
[19-OCT-2007 03:50:27:168] nsprecv: normal exit
[19-OCT-2007 03:50:27:168] nsrdr: got NSPTDA packet
[19-OCT-2007 03:50:27:168] nsrdr: NSPTDA flags: 0x0
[19-OCT-2007 03:50:27:168] nsrdr: normal exit
[19-OCT-2007 03:50:27:168] nsdo: *what=1, *bl=2001
[19-OCT-2007 03:50:27:168] nsdo: nsctxrnk=0
[19-OCT-2007 03:50:27:168] nsdo: normal exit
[19-OCT-2007 03:50:27:168] nioqrc: exit
[19-OCT-2007 05:03:39:066] nioqsn: entry
[19-OCT-2007 05:03:39:066] nioqsn: exit
[19-OCT-2007 05:03:39:066] nioqrc: entry
[19-OCT-2007 05:03:39:066] nsdo: entry
[19-OCT-2007 05:03:39:066] nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3

[19-OCT-2007 05:03:39:066] nsdo: rank=64, nsctxrnk=0
[19-OCT-2007 05:03:39:066] nsdo: nsctx: state=8, flg=0x400d, mvd=0
[19-OCT-2007 05:03:39:066] nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
[19-OCT-2007 05:03:39:066] nsdofls: entry
[19-OCT-2007 05:03:39:066] nsdofls: DATA flags: 0x0
[19-OCT-2007 05:03:39:066] nsdofls: sending NSPTDA packet
[19-OCT-2007 05:03:39:066] nspsend: entry
[19-OCT-2007 05:03:39:066] nspsend: plen=137, type=6
[19-OCT-2007 05:03:39:066] nttwr: entry
[19-OCT-2007 05:03:39:066] nttwr: socket 9 had bytes written=137
[19-OCT-2007 05:03:39:066] nttwr: exit
[19-OCT-2007 05:03:39:066] nspsend: packet dump
[19-OCT-2007 05:03:39:066] nspsend: 00 89 00 00 06 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 03 5E 1B 61 80 00 |...^.a..|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 01 36 00 |......6.|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 01 0D 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 01 00 00 00 00 01 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 01 01 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 01 12 73 65 6C 65 63 |...selec|
[19-OCT-2007 05:03:39:066] nspsend: 74 20 2A 20 66 72 6F 6D |t.*.from|
[19-OCT-2007 05:03:39:066] nspsend: 20 64 75 61 6C 01 00 00 |.dual...|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 01 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 00 00 00 00 00 00 00 |........|
[19-OCT-2007 05:03:39:066] nspsend: 00 |. |
[19-OCT-2007 05:03:39:066] nspsend: 137 bytes to transport
[19-OCT-2007 05:03:39:066] nspsend: normal exit
[19-OCT-2007 05:03:39:067] nsdofls: exit (0)
[19-OCT-2007 05:03:39:067] nsdo: nsctxrnk=0
[19-OCT-2007 05:03:39:067] nsdo: normal exit

No errors after 7 hours :-)

SQLNET.EXPIRE_TIME =10
SQLNET.INBOUND_CONNECT_TIMEOUT = 300
SQLNET.SEND_TIMEOUT = 300
SQLNET.RECV_TIMEOUT = 300

In case, you are receiving the following error message then set the above in sqlnet.ora in RMAN catalog server

BR4SPC: RMAN-06004: ORACLE ERROR FROM RECOVERY CATALOG: ORA-03135: CONNECTION

ORA-38500: USING CURRENT LOGFILE option not available without stand

Managed Standby Recovery not using Real Time Apply

ORA-38500: USING CURRENT LOGFILE option not available without stand

Solution:

SQL> alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;
alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without stand

Standby DB:

SQL> alter database add standby logfile '/data01/home/oracle/ns/stdby_log.log' size 10m;

Database altered.

SQL> alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

Database altered.

SQL>

Alert log:

MRP0: Background Managed Standby Recovery process started (ns)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 32
Wed Dec 5 16:50:52 2007
Completed: alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE

ORA-32018: parameter cannot be modified in memory on another instance

ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system set db_cache_size=1200m;
alter system set db_cache_size=1200m
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Solution:

SQL>alter system set db_cache_size=1200m scope=both sid='EXPERT2';

System altered.

but if you run the above with different sid from the same session then you will receive the same error.

SQL> alter system set db_cache_size=1200m scope=both sid='EXPERT1';
alter system set db_cache_size=1200m scope=both sid='EXPERT1'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Login locally to each instance and then execute the statement

Node 2:

SQL> alter system set db_cache_size=1200m scope=both sid='EXPERT2';

System altered.

Node 1:

SQL> alter system set db_cache_size=1200m scope=both sid='EXPERT1';

System altered.


Login User name Password Remember me Log in Problems? New User? Sign Up!


Scripts


FAQ


Forum Forums

G r e a t D e a l s

Feb 25, 2008 - 03:11 AM

Orcl-Experts