Troubleshooting

ORA-00600: internal error code, arguments: [723], [132616], [156344], [memory leak], [], [], [], [], [], [], [], []

On 12.1.0.2, the following error was reported in the alert log when running a Spatial query:

ORA-00600: internal error code, arguments: [723], [140904], [195968], [memory leak], [], [], [], [], [], [], [], []

The trace file shows the leak is in “mdorelOutputRing”

========================================
SUMMARIZED MEMORY USAGE FOR THIS PROCESS
—————————————-
34337704 bytes, 127 chunks: “free memory ”
top uga heap ds=0x7fec27e5ddc0 dsprt=(nil)
17102032 bytes, 79 chunks: “free memory ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
1287448 bytes, 48 chunks: “permanent memory ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
263256 bytes, 3 chunks: “permanent memory ”
top call heap ds=0x7fec27e5dba0 dsprt=(nil)
260984 bytes, 1 chunk : “free memory ”
top call heap ds=0x7fec27e5dba0 dsprt=(nil)
189344 bytes, 42 chunks: “mdorelOutputRing:c “
pga heap ds=0x7fec27e579a0 dsprt=(nil)
4352 bytes, 1 chunk : “mdrcncColumnNameCache ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
2088 bytes, 1 chunk : “mdcsWktCache ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
184 bytes, 1 chunk : “heap_kdlipga ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
112 bytes, 1 chunk : “permanent memory ”
top uga heap ds=0x7fec27e5ddc0 dsprt=(nil)

******************************************************
HEAP DUMP heap name=”pga heap” desc=0x7fec27e579a0
extent sz=0x20c0 alt=240 het=32767 rec=0 flg=3 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xffff0 heap=(nil)
fl2=0x60, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7fec23706b60
dsx empty ext bytes=5800416 subheap rc link=0x7fec23706bd0,0x7fec23706bd0
pdb id=0
EXTENT 0 addr=0x7fec1e100010
Chunk 7fec1e100020 sz= 1048544 free ” ”
EXTENT 1 addr=0x7fec1e0c0010
Chunk 7fec1e0c0020 sz= 5016 free ” ”
Chunk 7fec1e0c13b8 sz= 10712 freeable “mdorelOutputRin”

BUG 22828244 – MEMORY LEAK IN SDO_UNION().

Apply Patch 22828244.

Advertisements
Standard
AIX, Troubleshooting

HPUX-ia64 Error: 28: No space left on device

Hello, problem HPUX-ia64 Error: 28: No space left on device .

ROCPSTEST oracle@rocpstest:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 27 14:04:48 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-09817: Write to audit file failed.
HPUX-ia64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

I solve it immediately. Is database   runnig ?

ROCPSTEST oracle@rocpstest:/home/oracle>ps -ef |grep smon
root 5200 1 0 Nov 12 ? 7:31 /etc/opt/resmon/lbin/p_client
root 4443 1 0 Nov 12 ? 5:35 /etc/opt/resmon/lbin/emsagent
oracle 5452 1 0 Nov 12 ? 9:25 asm_smon_+ASM
oracle 9690 9400 0 14:06:07 pts/1 0:00 grep smon

yes. After this I checked space available in the FS. I got this:
ROCPSTEST oracle@rocpstest:/home/oracle> df -g
/home (/dev/vg00/lvol4 ) :
8192 file system block size 8192 fragment size
655360 total blocks 652013 total free blocks
646929 allocated free blocks 163232 total i-nodes
163000 total free i-nodes 163000 allocated free i-nodes
1073741828 file system id vxfs file system type
0x10 flags 255 file system name length
/home file system specific string
/opt (/dev/vg00/lvol5 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 497345 total free blocks
493471 allocated free blocks 214080 total i-nodes
124307 total free i-nodes 124307 allocated free i-nodes
1073741829 file system id vxfs file system type
0x10 flags 255 file system name length
/opt file system specific string
/oracle (/dev/vgrocps/lvol3 ) :
8192 file system block size 1024 fragment size
157286400 total blocks 0 total free blocks  -> the problem is here
0 allocated free blocks 365152 total i-nodes
155812 total free i-nodes 155812 allocated free i-nodes
1073872899 file system id vxfs file system type
0x10 flags 255 file system name length
/oracle file system specific string
/rocps_data2 (/dev/vgrocps/lvol1 ) :
8192 file system block size 8192 fragment size
268451840 total blocks 220795054 total free blocks
219085406 allocated free blocks 56915456 total i-nodes
55198754 total free i-nodes 55198754 allocated free i-nodes
1073872897 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_data2 file system specific string
/rocps_home2 (/dev/vgrocps/lvol2 ) :
8192 file system block size 1024 fragment size
62914560 total blocks 53639679 total free blocks
50290045 allocated free blocks 13412360 total i-nodes
13409919 total free i-nodes 13409919 allocated free i-nodes
1073872898 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_home2 file system specific string
/tmp (/dev/vg00/lvol6 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 1198551 total free blocks
1189189 allocated free blocks 301152 total i-nodes
299637 total free i-nodes 299637 allocated free i-nodes
1073741830 file system id vxfs file system type
0x10 flags 255 file system name length
/tmp file system specific string
/usr (/dev/vg00/lvol7 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 860929 total free blocks
854207 allocated free blocks 245344 total i-nodes
215213 total free i-nodes 215213 allocated free i-nodes
1073741831 file system id vxfs file system type
0x10 flags 255 file system name length
/usr file system specific string
/var (/dev/vg00/lvol8 ) :
8192 file system block size 8192 fragment size
3682304 total blocks 2872951 total free blocks
2850644 allocated free blocks 770176 total i-nodes
718236 total free i-nodes 718236 allocated free i-nodes
1073741832 file system id vxfs file system type
0x10 flags 255 file system name length
/var file system specific string
/stand (/dev/vg00/lvol1 ) :
8192 file system block size 8192 fragment size
262144 total blocks 191626 total free blocks
190135 allocated free blocks 48256 total i-nodes
47901 total free i-nodes 47901 allocated free i-nodes
1073741825 file system id vxfs file system type
0 flags 255 file system name length
/stand file system specific string
/ (/dev/vg00/lvol3 ) :
8192 file system block size 8192 fragment size
262144 total blocks 228445 total free blocks
226673 allocated free blocks 59808 total i-nodes
57110 total free i-nodes 57110 allocated free i-nodes
1073741827 file system id vxfs file system type
0x10 flags 255 file system name length
/ file system specific string
ROCPSTEST oracle@rocpstest:/home/oracle>ls -la

I changed /oracle directory space size:

ROCPSTEST oracle@rocpstest:/home/oracle>df -g
/home (/dev/vg00/lvol4 ) :
8192 file system block size 8192 fragment size
655360 total blocks 652013 total free blocks
646929 allocated free blocks 163232 total i-nodes
163000 total free i-nodes 163000 allocated free i-nodes
1073741828 file system id vxfs file system type
0x10 flags 255 file system name length
/home file system specific string
/opt (/dev/vg00/lvol5 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 497345 total free blocks
493471 allocated free blocks 214080 total i-nodes
124307 total free i-nodes 124307 allocated free i-nodes
1073741829 file system id vxfs file system type
0x10 flags 255 file system name length
/opt file system specific string
/oracle (/dev/vgrocps/lvol3 ) :
8192 file system block size 1024 fragment size
157286400 total blocks 41163168 total free blocks
38629464 allocated free blocks 10499968 total i-nodes
10290792 total free i-nodes 10290792 allocated free i-nodes
1073872899 file system id vxfs file system type
0x10 flags 255 file system name length
/oracle file system specific string
/rocps_data2 (/dev/vgrocps/lvol1 ) :
8192 file system block size 8192 fragment size
268451840 total blocks 220794999 total free blocks
219085351 allocated free blocks 56915456 total i-nodes
55198747 total free i-nodes 55198747 allocated free i-nodes
1073872897 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_data2 file system specific string
/rocps_home2 (/dev/vgrocps/lvol2 ) :
8192 file system block size 1024 fragment size
62914560 total blocks 53640771 total free blocks
50291069 allocated free blocks 13412632 total i-nodes
13410191 total free i-nodes 13410191 allocated free i-nodes
1073872898 file system id vxfs file system type
0x10 flags 255 file system name length
/rocps_home2 file system specific string
/tmp (/dev/vg00/lvol6 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 1198551 total free blocks
1189189 allocated free blocks 301152 total i-nodes
299637 total free i-nodes 299637 allocated free i-nodes
1073741830 file system id vxfs file system type
0x10 flags 255 file system name length
/tmp file system specific string
/usr (/dev/vg00/lvol7 ) :
8192 file system block size 8192 fragment size
1310720 total blocks 860929 total free blocks
854207 allocated free blocks 245344 total i-nodes
215213 total free i-nodes 215213 allocated free i-nodes
1073741831 file system id vxfs file system type
0x10 flags 255 file system name length
/usr file system specific string
/var (/dev/vg00/lvol8 ) :
8192 file system block size 8192 fragment size
3682304 total blocks 2873730 total free blocks
2851423 allocated free blocks 770176 total i-nodes
718413 total free i-nodes 718413 allocated free i-nodes
1073741832 file system id vxfs file system type
0x10 flags 255 file system name length
/var file system specific string
/stand (/dev/vg00/lvol1 ) :
8192 file system block size 8192 fragment size
262144 total blocks 191626 total free blocks
190135 allocated free blocks 48256 total i-nodes
47901 total free i-nodes 47901 allocated free i-nodes
1073741825 file system id vxfs file system type
0 flags 255 file system name length
/stand file system specific string
/ (/dev/vg00/lvol3 ) :
8192 file system block size 8192 fragment size
262144 total blocks 228507 total free blocks
226735 allocated free blocks 59808 total i-nodes
57110 total free i-nodes 57110 allocated free i-nodes
1073741827 file system id vxfs file system type
0x10 flags 255 file system name length

after delete old *trm and *trc files older than 7 days.

find /oracle/diag/rdbms/rocpstest/ROCPSTEST/trace/*.trc -mtime +7 -exec rm {} \;

find /oracle/diag/rdbms/rocpstest/ROCPSTEST/trace/*.trm -mtime +7 -exec rm {} \;

connect to oracle as sysdba:

ROCPSTEST oracle@rocpstest:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 27 15:42:44 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select version from v$instance;

VERSION
—————–
11.2.0.4.0

SQL>

 

problem is solved,

Br.Erman

Standard
Troubleshooting

LRM-00109: could not open parameter file

Hello Everyone,

When trying to start the instance , getting this ..
Enter user-name: sys as sysdba;
Enter password:
Connected to an idle instance.
SQL> startup;

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/initDemo.ora’

solutuon one :
Check the Oracle SID name.  Wrong settings in bash profile.

Solution two :

I used to face this issue.

I realized that the error can be caused by the pfile not being found. You have to start the Oracle instance using a fully referenced pfile by issuing the following command:

SQL> startup pfile=<your pfile full path here>

path location: cd $ORACLE_HOME/db/dbs/init<SID>.ora

demo : /oracle/product/11.2.0.4/db/dbs/initDEMO.ora

SQL> startup pfile=’/oracle/product/11.2.0.4/db/dbs/initDEMO.ora’;

then sql>create spfile from pfile;

have a good day.

Standard
Oracle, Troubleshooting

Reset Oracle SYS password

I have list my SYS password in Oracle and I need to recover the SYS password, or at least restore the password.  How can I recover my lost SYS password?

sqlplus /nolog
connect / as sysdba
ALTER USER sys IDENTIFIED BY “new_password”;

demo :

[oracle@mezon01 ~]$ . profile_db_PDEV
[oracle@mezon01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 1 17:19:52 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> ALTER USER sys IDENTIFIED BY System_2018;

User altered.

SQL>
SQL>
SQL>
SQL> exit

Standard
Troubleshooting

EXP – IMP: ORA-04031

The ORA-04031 error returns whenever oracle attempts to allocate a large piece of contiguous memory in the shared pool but fails to allocate. Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned.

Since in 11g automatic memory management in enabled and this allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool with the single parameter SGA_TARGET. So simply increase of SGA_TARGET likely solve the problem.

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 16 bytes of shared memory (“shared pool”,”SELECT job_id FROM v$datapum…”,”SQLA”,”tmp”)
ORA-06512: at “SYS.KUPV$FT_INT”, line 2904
ORA-06512: at “SYS.KUPC$QUE_INT”, line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
ORA-06512: at line 1

 

Solution
Let’s see the sga_target value.
SQL> show parameter sga_t
NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 64G

And SGA_MAX_SIZE value,
SQL> show parameter sga_max
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 64G

We see these two parameter settings are low. So we increase it and restart database. As setting of SGA_TARGET is depend on SGA_MAX_SIZE and to change SGA_MAX_SIZE we need to bounce the database in order to effect.
SQL> alter system set sga_max_size=64G scope=spfile;

System altered.

SQL> alter system set sga_target=100G scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 96469504 bytes
Database Buffers 209715200 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> show parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 100G

problem is solved.

Standard
Oracle, Troubleshooting

Why parallel expdp gives unable to write error DATAPUMP With PARALLEL > 1 ON 11.2 RAC

hello, Problem:

Datapump on 11.2 RAC with PARALLEL > 1  hits the following errors.

ORA-31693: Table data object “IIR_USER”.”IDS_00_INUSE” failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file “/export/expıırdb_04.dmp” for write
ORA-19505: failed to identify file “/export/expıırdb_04.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

Cause

From 11.2, Datapump new parameter CLUSTER is introduced.

CLUSTER : Default=Y

Purpose :
Determines whether Data Pump can use Oracle Real Application Clusters (RAC)
resources and start workers on other Oracle RAC instances.

Syntax and Description : CLUSTER=[Y | N]

Solution

To force Data Pump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N.

export scripts :

[oracle@jery scripts]$ cat expIIRDB.sh
expdp userid=\”/ as sysdba\” DIRECTORY=EXP01 full=Y DUMPFILE=expıırdb_%U.dmp LOGFILE=expıırdb.log parallel=4 compression=all CLUSTER=N
[oracle@jery scripts]$

 

Br. Erman.

Standard
Oracle, Troubleshooting

Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)

Hello,In Oracle 11g, the new initialization parameter DDL_LOCK_TIMEOUT controls the duration (in seconds) for which a DDL statement will wait for a DML lock.The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

The default value of zero indicates a status of NOWAIT.

SQL> show parameter DDL_LOCK_TIMEOUT;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

If these locks are not available, the commands return with an “ORA-00054: resource busy” error message.

solutions:

  1.  kill oracle user session,
  2. Update DDL_LOCK_TIMEOUT
    SQL> set timing onSQL> alter session set ddl_lock_timeout=60;

    Session altered.
    Elapsed: 00:00:00.02

br Erman.

Standard