ORA-03135 : connection lost contacts
Platform :Oracle Database Version: 11.1.0.7.0
Operating System:
[oracle@stage1]$ uname -a
Linux stage1.ktmb.com 2.6.39-400.278.3.el6uek.x86_64 #1 SMP Thu May 19 11:54:25 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux
Message :
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 5276
Session ID: 5505 Serial number: 3
After database instance is started on nomount stage, have checked that instance is not running yet but sql prompt it is showing that instance is started.
[oracle@stage1]$ ps -ef | grep pmon
oracle 6830 11926 0 12:47 pts/3 00:00:00 grep pmon
[oracle@stage1]$
Stage :
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2159472 bytes
Variable Size 4227861648 bytes
Database Buffers 1073741824 bytes
Redo Buffers 40968192 bytes
SQL>
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 5276
Session ID: 5505 Serial number: 3
[oracle@stage1]$ ps -ef | grep pmon
oracle 6830 11926 0 12:47 pts/3 00:00:00 grep pmon
[oracle@stage1]$
SQL> startup pfile='/d001/app/oracle/product/11.1.0/OracleDB/dbs/initspotprod.ora' nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2159472 bytes
Variable Size 4227861648 bytes
Database Buffers 1073741824 bytes
Redo Buffers 40968192 bytes
SQL>
SQL>
SQL>
SQL> show parameter control;
ORA-03135: connection lost contact
Process ID: 5017
Session ID: 5505 Serial number: 3
[oracle@stage1]$ ps -ef | grep pmon
oracle 6830 11926 0 12:50 pts/3 00:00:00 grep pmon
[oracle@stage1]$
Analysis :
[oracle@stage1 dbs]$ adrci
ADRCI: Release 11.1.0.7.0 - Production on Tue Jun 28 11:01:34 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/d001/app/oracle"
adrci>
adrci>
adrci>
adrci> show homes
ADR Homes:
diag/tnslsnr/stage1/listener
diag/rdbms/spotprod/spotprod
adrci>
adrci>
adrci>
adrci>
adrci> set home diag/rdbms/spotprod/spotprod
adrci>
adrci>
adrci> show alert -tail 100
2016-06-28 10:55:05.129000 +08:00
System parameters with non-default values:
processes = 5000
nls_date_format = "DD-MM-RRRR HH24:MI:SS"
sga_target = 3008M
memory_target = 3G
memory_max_target = 5G
control_files = "/d001/app/oracle/oradata/spotprod/control1.ctl"
control_files = "/d001/app/oracle/oradata/spotprod/control2.ctl"
db_block_size = 8192
compatible = "11.1.0"
log_archive_dest_1 = "location=/d003/archprod"
log_archive_format = "spot_%t_%s_%r.arc"
dml_locks = 900
undo_tablespace = "UNDOTBS2"
undo_retention = 2400
remote_login_passwordfile= "EXCLUSIVE"
audit_sys_operations = TRUE
db_domain = "KTMB.COM.MY"
global_names = FALSE
distributed_lock_timeout = 1000
dispatchers = "(PROTOCOL=TCP) (SERVICE=ORCLXDB)"
local_listener = "(ADDRESS=(PROTOCOL=tcp)(HOST=spodbprd)(PORT=1521))"
job_queue_processes = 1000
audit_file_dest = "/d001/app/oracle/admin/spotprod/adump"
audit_trail = "DB"
db_name = "SPOTPROD"
open_cursors = 2500
pga_aggregate_target = 0
diagnostic_dest = "/d001/app/oracle"
PMON started with pid=2, OS id=4812
VKTM started with pid=3, OS id=4814 at elevated priority
VKTM running at (20)ms precision
DIAG started with pid=4, OS id=4818
DBRM started with pid=5, OS id=4820
PSP0 started with pid=6, OS id=4822
DIA0 started with pid=7, OS id=4824
MMAN started with pid=8, OS id=4826
DBW0 started with pid=9, OS id=4828
LGWR started with pid=10, OS id=4830
CKPT started with pid=11, OS id=4832
SMON started with pid=12, OS id=4834
RECO started with pid=13, OS id=4836
MMON started with pid=14, OS id=4838
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=15, OS id=4840
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /d001/app/oracle
2016-06-28 10:55:08.215000 +08:00
Errors in file /d001/app/oracle/diag/rdbms/spotprod/spotprod/trace/spotprod_mman_4826.trc:
ORA-27103: internal error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: -1
Additional information: 1
MMAN (ospid: 4826): terminating the instance due to error 27103
2016-06-28 10:55:09.225000 +08:00
Instance terminated by MMAN, pid = 4826
2016-06-28 10:59:00.928000 +08:00
Starting ORACLE instance (normal)
2016-06-28 10:59:01.998000 +08:00
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =605
LICENSE_MAX_USERS = 0
SYS auditing is enabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile /d001/app/oracle/product/11.1.0/OracleDB/dbs/initspotprod.ora on machine stage1.ktmb.com
System parameters with non-default values:
processes = 5000
nls_date_format = "DD-MM-RRRR HH24:MI:SS"
sga_target = 3008M
memory_target = 3G
memory_max_target = 5G
control_files = "/d001/app/oracle/oradata/spotprod/control1.ctl"
control_files = "/d001/app/oracle/oradata/spotprod/control2.ctl"
db_block_size = 8192
compatible = "11.1.0"
log_archive_dest_1 = "location=/d003/archprod"
log_archive_format = "spot_%t_%s_%r.arc"
dml_locks = 900
undo_tablespace = "UNDOTBS2"
undo_retention = 2400
remote_login_passwordfile= "EXCLUSIVE"
audit_sys_operations = TRUE
db_domain = "KTMB.COM.MY"
global_names = FALSE
distributed_lock_timeout = 1000
dispatchers = "(PROTOCOL=TCP) (SERVICE=ORCLXDB)"
local_listener = "(ADDRESS=(PROTOCOL=tcp)(HOST=spodbprd)(PORT=1521))"
job_queue_processes = 1000
audit_file_dest = "/d001/app/oracle/admin/spotprod/adump"
audit_trail = "DB"
db_name = "SPOTPROD"
open_cursors = 2500
pga_aggregate_target = 0
diagnostic_dest = "/d001/app/oracle"
PMON started with pid=2, OS id=4984
VKTM started with pid=3, OS id=4986 at elevated priority
VKTM running at (20)ms precision
DIAG started with pid=4, OS id=4990
DBRM started with pid=5, OS id=4992
PSP0 started with pid=6, OS id=4994
DIA0 started with pid=7, OS id=4996
MMAN started with pid=8, OS id=4998
DBW0 started with pid=9, OS id=5000
LGWR started with pid=10, OS id=5002
CKPT started with pid=11, OS id=5004
SMON started with pid=12, OS id=5006
RECO started with pid=13, OS id=5008
MMON started with pid=14, OS id=5010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=15, OS id=5012
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /d001/app/oracle
2016-06-28 10:59:05.786000 +08:00
Errors in file /d001/app/oracle/diag/rdbms/spotprod/spotprod/trace/spotprod_mman_4998.trc:
ORA-27103: internal error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: -1
Additional information: 1
MMAN (ospid: 4998): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 4998
adrci>
adrci>
adrci> exit
[oracle@stage1 dbs]$
Trace File :
[oracle@stage1]$ more /d001/app/oracle/diag/rdbms/spotprod/spotprod/trace/spotprod_mman_4998.trc
Trace file /d001/app/oracle/diag/rdbms/spotprod/spotprod/trace/spotprod_mman_4998.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /d001/app/oracle/product/11.1.0/OracleDB
System name: Linux
Node name: stage1.ktmb.com
Release: 2.6.39-400.278.3.el6uek.x86_64
Version: #1 SMP Thu May 19 11:54:25 PDT 2016
Machine: x86_64
Instance name: spotprod
Redo thread mounted by this instance: 0
Oracle process number: 8
Unix process pid: 4998, image: oracle@stage1.ktmb.com (MMAN)
*** 2016-06-28 10:59:05.786
*** SESSION ID:(5498.1) 2016-06-28 10:59:05.786
*** CLIENT ID:() 2016-06-28 10:59:05.786
*** SERVICE NAME:() 2016-06-28 10:59:05.786
*** MODULE NAME:() 2016-06-28 10:59:05.786
*** ACTION NAME:() 2016-06-28 10:59:05.786
error 27103 detected in background process
ORA-27103: internal error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: -1
Additional information: 1
*** 2016-06-28 10:59:05.786
MMAN (ospid: 4998): terminating the instance due to error 27103
[oracle@stage1]$
Cause :
The problem is due to Bug 7272646, introduced in 11.1.0.7 patchset on 64bit Linux.
In some cases, after applying the 11.1.0.7 patch set on 64Bit Linux x86 the instance will not start when MEMORY_TARGET > 3GB.
Solution :
Step#1: Downloaded the patch: Patch 7272646 : ORA-27103 WHEN MEMORY_TARGET > 3G
Step#2: Check Readme file using below link -
https://updates.oracle.com/Orion/Services/download?type=readme&aru=10574320
Step#3: Unzipped the patch
[oracle@stage1 DB11107]$ unzip p7272646_111070_Linux-x86-64.zip
Archive: p7272646_111070_Linux-x86-64.zip
creating: 7272646/
creating: 7272646/files/
creating: 7272646/files/lib/
creating: 7272646/files/lib/libgeneric11.a/
inflating: 7272646/files/lib/libgeneric11.a/sskgm.o
creating: 7272646/etc/
creating: 7272646/etc/config/
inflating: 7272646/etc/config/inventory.xml
inflating: 7272646/etc/config/actions.xml
creating: 7272646/etc/xml/
inflating: 7272646/etc/xml/GenericActions.xml
inflating: 7272646/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 7272646/README.txt
[oracle@stage1 DB11107]$
[oracle@stage1 DB11107]$ ls -ltr
total 1575724
drwxr-xr-x 5 oracle oinstall 4096 Sep 15 2008 Disk1
-rw-r--r-- 1 oracle oinstall 121850 Sep 19 2008 README.html
drwxr-xr-x 4 oracle oinstall 4096 Oct 14 2008 7272646
-rw-r--r-- 1 oracle oinstall 1613366248 Jun 21 10:04 p6890831_111070_Linux-x86-64.zip
-rw-r--r-- 1 oracle oinstall 35033 Jun 28 11:19 p7272646_111070_Linux-x86-64.zip
[oracle@stage1 DB11107]$
[oracle@stage1 DB11107]$ cd 7272646
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$ ls -ltr
total 12
drwxr-xr-x 4 oracle oinstall 4096 Oct 14 2008 etc
drwxr-xr-x 3 oracle oinstall 4096 Oct 14 2008 files
-rw-rw-r-- 1 oracle oinstall 1599 Apr 21 2009 README.txt
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$
Note :
Here also you will get README.txt file. This readme file is same as previous one which we have read from our link in Step#2.
Step#4: Apply the patch
[oracle@stage1 OPatch]$ cd /home/oracle/binaries/DB11107/7272646/
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$ /d001/app/oracle/product/11.1.0/OracleDB/OPatch/opatch apply
Invoking OPatch 11.1.0.6.2
Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /d001/app/oracle/product/11.1.0/OracleDB
Central Inventory : /home/oracle/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.2
OUI version : 11.1.0.7.0
OUI location : /d001/app/oracle/product/11.1.0/OracleDB/oui
Log file location : /d001/app/oracle/product/11.1.0/OracleDB/cfgtoollogs/opatch/opatch2016-06-28_11-40-10AM.log
ApplySession applying interim patch '7272646' to OH '/d001/app/oracle/product/11.1.0/OracleDB'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/d001/app/oracle/product/11.1.0/OracleDB')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '7272646' for restore. This might take a while...
Backing up files affected by the patch '7272646' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/d001/app/oracle/product/11.1.0/OracleDB/lib/libgeneric11.a" with "lib/libgeneric11.a/sskgm.o"
Running make for target client_sharedlib
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
Running make for target ioracle
ApplySession adding interim patch '7272646' to inventory
Verifying the update...
Inventory check OK: Patch ID 7272646 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7272646 are present in Oracle Home.
The local system has been patched and can be restarted.
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67215:
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
--------------------------------------------------------------------------------
OPatch Session completed with warnings.
OPatch completed with warnings.
[oracle@stage1 7272646]$
[oracle@stage1 7272646]$
Verification after applying the patch :
[oracle@stage1 ~]$
[oracle@stage1 ~]$
[oracle@stage1 ~]$ . oraenv
ORACLE_SID = [spotprod] ?
ORACLE_HOME = [/home/oracle] ? /d001/app/oracle/product/11.1.0/OracleDB
The Oracle base for ORACLE_HOME=/d001/app/oracle/product/11.1.0/OracleDB is /d001/app/oracle
[oracle@stage1 ~]$
[oracle@stage1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jun 28 11:41:39 2016
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2159472 bytes
Variable Size 4227861648 bytes
Database Buffers 1073741824 bytes
Redo Buffers 40968192 bytes
SQL>
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /d001/app/oracle/oradata/spotp
rod/control1.ctl, /d001/app/or
acle/oradata/spotprod/control2.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
SQL>
OS Level Verification :
[oracle@stage1]$ ps -ef | grep pmon
oracle 7425 11926 0 13:16 pts/3 00:00:00 grep pmon
oracle 11566 1 0 Jun28 ? 00:00:10 ora_pmon_spotprod
References :
1. ORA-27103 when Memory target parameter is set to more than 3 GB (Doc ID 743012.1)
2. Patch 7272646: ORA-27103 WHEN MEMORY_TARGET > 3G
3. Readme File: https://updates.oracle.com/Orion/Services/download?type=readme&aru=10574320
4. https://taliphakanozturken.wordpress.com/tag/opatch-apply/
No comments:
Post a Comment