Limitation of ILM Assistant tool

Few days back I got chance to work on ILM Assistant tool.

I faced lots of challenges in ILM Assistant tool. Few are mentioned below:
1) There is no support for ILM Assistant tool on My Oracle Support site (SR)
2) There are lots of bugs in ILM Assistant
3) ILM Assistant can manage one database at a time
4) ILM Assistant can not move data automatically.It only recommends data to be moved
5) ILM Assistant would not archive data.Its only recommend when it is time to archive
6) We can not manage all the EBS tables by ILM Assistant.Only range partitioned by date tables can be managed
7) ILM Assistant would not purge/delete data
8) Modifying pre-partitioned Oracle E-Business Suite indexes and tables is not recommended or supported as it can cause application errors
9) ILM Assistant recommends partitioning only on DATE column.

ORA-04031

Alert log:
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_6476.trc (incident=6176661):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area:PLSQL","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_19831.trc (incident=6178405):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_16403.trc (incident=6177549):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Tue Oct 28 15:02:36 2014
Errors in file /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_2108.trc (incident=6178773):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INSERT INTO APPS.XXcustom_MODI...","sql area","tmp")
Incident details in: /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/incident/incdir_6176661/ORCL_ora_6476_i6176661.trc
Incident details in: /oracle/db/tech_st/11.1.0/admin/ORCL_orcl12db/diag/rdbms/ORCL/ORCL/incident/incdir_6178405/ORCL_ora_19831_i6178405.trc
...


Trace File :
The ORCL_ora_19831.trc trace file shows:

*** 2014-10-28 15:02:35.455
*** SESSION ID:(1843.3362) 2014-10-28 15:02:35.455
*** CLIENT ID:(RSOW BHUSHAN) 2014-10-28 15:02:35.455
*** SERVICE NAME:(SYS$USERS) 2014-10-28 15:02:35.455
*** MODULE NAME:(e:SQLAP:frm:APXINWKB) 2014-10-28 15:02:35.455
*** ACTION NAME:(SQLAP/PAY_USER_INV_BGPPL- OSSB_W) 2014-10-28 15:02:35.455

=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=10/28/2014 15:02:34
Dump Count=1
Allocation request for: tmp
Heap: 0x5cec12f48, size: 32
******************************************************
HEAP DUMP heap name="sga heap" desc=0x60001170
extent sz=0x9800 alt=216 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x1 heap=(nil)
fl2=0x60, nex=(nil)
ds for latch 1: 0x60047bd8 0x60049430 0x6004ac88 0x6004c4e0
ds for latch 2: 0x60051400 0x60052c58 0x600544b0 0x60055d08
ds for latch 3: 0x6005ac28 0x6005c480 0x6005dcd8 0x6005f530
ds for latch 4: 0x60064450 0x60065ca8 0x60067500 0x60068d58
ds for latch 5: 0x6006dc78 0x6006f4d0 0x60070d28 0x60072580
ds for latch 6: 0x600774a0 0x60078cf8 0x6007a550 0x6007bda8
reserved granule count 0 (granule size 268435456)
******************************************************
HEAP DUMP heap name="sql area" desc=0x5cec12f48
extent sz=0xfe8 alt=32767 het=336 rec=0 flg=2 opc=2
parent=0x60001170 owner=0x5cec12e30 nex=(nil) xsz=0x10000000 heap=(nil)
fl2=0x26, nex=(nil)





Call Stack Trace:
... kghnospc kghalf kksLoadChild kxsGetRuntimeLock kksfbc opiexe opiefn0 kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real ssthrdmain main __libc_start_main _start
----- End of Call Stack Trace -----

   
This is due to unpublished bug:9267837 fixed in 11.2.0.2 and documented in Note:9267837.8.
   
Download and apply one-off patch number 9267837 for 11.1.0.7.0 on Linux x86-64.

DBMS_SQLPA object is Invalid

Issue:
DBMS_SQLPA object is Invalid.

Findings:
DBMS_SQLPA is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column.
That is why we get an error as SYS.plan_table does not have this column.

Solution:
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Note: It is suggested to do this process in Odd hours.

RAC Server Rebooted Abnormally

There are many reasons of Server reboot.You need to analyze the log files to diagnose the root cause.

Issue
The Server test1(10.10.10.1) was abnormally rebooted on 26-02-2011.

Fact
Node 1 was evicted by node 2 because connections could not be made from node 2 to node 1.


Node 2 cssd.log
[ CSSD]2011-02-26 03:04:01.745 [10] >TRACE: clssgmClientConnectMsg: Connect from con(6000000000048870) proc(6000000000103df0) pid() proto(10:2:1:1)
[ CSSD]2011-02-26 03:04:32.370 [5] >TRACE: clssnm_skgxncheck: CSS daemon failed on node 0
[ CSSD]2011-02-26 03:04:32.371 [5] >TRACE: clssnmDiscHelper: node test1 (0) connection failed ========>>>>>>>>>>>> cannot connect to node test1
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmDoSyncUpdate: Initiating sync 13
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmDoSyncUpdate: diskTimeout set to (597000)ms
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmSetupAckWait: Ack message type (11)
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmSetupAckWait: node(0) is ALIVE
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmSetupAckWait: node(1) is ALIVE
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmSendSync: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmWaitForAcks: Ack message type(11), ackCount(2)
[ CSSD]2011-02-26 03:04:32.708 [9] >TRACE: clssnmHandleSync: Acknowledging sync: src[1] srcName[poiddb02] seq[1] sync[13]
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmWaitForAcks: node(0) is expiring, msg type(11)
[ CSSD]2011-02-26 03:04:32.708 [9] >TRACE: clssnmHandleSync: diskTimeout set to (597000)ms
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmWaitForAcks: done, msg type(11)
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmDoSyncUpdate: Terminating node 0, test1, misstime(21531) state(3)
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSetupAckWait: Ack message type (13)
[ CSSD]2011-02-26 03:04:32.709 [1] >USER: NMEVENT_SUSPEND [00][00][00][03]
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSetupAckWait: node(1) is ACTIVE
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSendVote: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.710 [16] >TRACE: clssnmWaitForAcks: Ack message type(13), ackCount(1)
[ CSSD]2011-02-26 03:04:32.710 [9] >TRACE: clssnmSendVoteInfo: node(1) syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.711 [16] >TRACE: clssnmWaitForAcks: done, msg type(13)
[ CSSD]2011-02-26 03:04:32.711 [16] >TRACE: clssnmCheckDskInfo: Checking disk info...
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmEvict: Start =====================>>>>>>>>>>>>>>>>>>>> Node 2 evicts node 1
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmWaitOnEvictions: Start
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmWaitOnEvictions: Node(0) down, LATS(2088370148),timeout(21643)
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSetupAckWait: Ack message type (15)
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSetupAckWait: node(1) is ACTIVE
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSendUpdate: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.713 [16] >TRACE: clssnmWaitForAcks: Ack message type(15), ackCount(1)
[ CSSD]2011-02-26 03:04:32.713 [9] >TRACE: clssnmUpdateNodeState: node 0, state (0/0) unique (1293953635/1293953635) prevConuni(1293953635) birth (10/0) (old/new)
[ CSSD]2011-02-26 03:04:32.713 [9] >TRACE: clssnmDeactivateNode: node 0 (test1) left cluster


Findings
When two systems have access to the shared storage, integrity of the data depends on the systems communication through "HEARTBEATS" using the private interconnects. When the PRIVATE LINKS are LOST and FAILED,each system thinks the other system has exited the cluster, then it tries to become the master or form a sub-cluster and claim exclusive access to the shared storage.
To avoid such a tricky and undesirable situation,the basic approach is STOMITH(Shoot the Other Machine in the Head) fencing. In STOMITH systems, the errant cluster node is simply reset and forced to reboot.


Solution
Please check with System Admin for Network errors around the incident time.

Workflow mailer is not getting up (Oracle R12)

Issue:
Workflow mailer got down.Tried to start it but not getting Up


Error:
at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:300)
at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:522)
at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1238)
at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)
at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2212)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)
at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)


Finding:
Autoconfig has changed the Outbound Server Name


Solution:
Set Outbound Server name to test-mac.ABC.com

Note:Inbound Server name is same as Outbound Server Name in our case.

ORA-24247: network access denied by access control list (ACL)

Issue:
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "XXX", line 36
ORA-06512: at line 1

Changes:
The problem occured after migrating to 11g from 10g.

Findings:
SQL> select owner,object_name,object_type,status from dba_objects where object_name='MAILSERVER_ACL';
no rows selected

Solution:
Step 1: connect to the Database and create a procedure as given below:
SQL> set serveroutput on
SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/
Procedure created.

SQL> show errors
No errors.

Step 2: create an ACL as given below:
SQL> begin
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'TEST_USER', -----Username sending mail
TRUE,
'connect',
'abc-smtp-int.MAIL_SERVER.com',  ---Mail Server
25);  ---Port of mail server
end;
/
ACL dropped.....
ACL created.....
ACL assigned.....
ACL commited.....
PL/SQL procedure successfully completed.

Also be sure that mail server entry should be in host file

ACL Views
The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings.

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.
COLUMN host FORMAT A30
COLUMN acl FORMAT A30
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
10.1.10.* /sys/acls/test_acl_file.xml
192.168.2.3 80 80 /sys/acls/test_acl_file.xml
2 rows selected.

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.
COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
ACL PRINCIPAL PRIVILE IS_GR START_DATE END_DATE
------------------------------ ------------------------------ ------- ----- ----------- -----------
/sys/acls/test_acl_file.xml TEST1 connect true 02-APR-2008
/sys/acls/test_acl_file.xml TEST2 connect false
2 rows selected.

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.
conn test1/test1@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect GRANTED
192.168.2.3 80 80 connect GRANTED
2 rows selected.

conn test2/test2@db11g
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.* connect DENIED
192.168.2.3 80 80 connect DENIED
2 rows selected.

Checking Privileges
In addition to the ACL views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package.

conn sys/password@db11g AS SYSDBA
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;
PRIVILE
-------
GRANTED
1 row selected.

COLUMN acl FORMAT A30
COLUMN host FORMAT A30
SELECT acl,host,
DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST2', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;
PRIVILE
-------
DENIED

TNS-12557 TNS-12560 TNS-00527

Issue:
Not able to start the listener.Getting below mentioned error.

Started with pid=15898 TNS-01150: The address of the specified listener name is incorrect
TNSLSNR for HPUX: Version 10.2.0.3.0 - Production on 17-AUG-2012 09:35:49
Copyright (c) 1991, 2006, Oracle.  All rights reserved.

System parameter file is /usr/oracle102/network/admin/listener.ora
Log messages written to /usr/oracle102/network/log/listener.log
Trace information written to /usr/oracle102/network/trace/listener.trc
Trace level is currently 0

Started with pid=15966
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.11.50.110)(PORT=1521)))
Error listening on: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.50.226)(PORT=1521)))
TNS-12557: TNS:protocol adapter not loadable
TNS-12560: TNS:protocol adapter error
TNS-00527: Protocol Adapter not loadable

Findings:
In listener.log same message

Enable the trace on listener
--------------------------------------
Add the below enties in listener.log and try to start the listener.
TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER = LIST_01.trc
TRACE_DIRECTORY_LISTENER = /oracle
TRACE_TIMESTAMP_LISTENER = ON

DIAG_ADR_ENABLED_LISTENER = OFF <== this parameter is required on 11g Sever Version in order to create a listener trace file

Issue verified from the listener trace :
--------------------------------------------------
17-AUG-2012 11:17:27:333] nsgllsn: DH supported/allowed for the endpoint
[17-AUG-2012 11:17:27:333] nsgllsn: DH enabled for the endpoint
[17-AUG-2012 11:17:27:333] nsglhins: entry
[17-AUG-2012 11:17:27:333] nsglhins: exit
[17-AUG-2012 11:17:27:333] nslisten: entry
[17-AUG-2012 11:17:27:333] nsc2addr: entry
[17-AUG-2012 11:17:27:333] nsc2addr: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))
[17-AUG-2012 11:17:27:333] sntuscrt: entry
[17-AUG-2012 11:17:27:333] sntuscrt: illegal permission
[17-AUG-2012 11:17:27:333] sntuscrt: exit
[17-AUG-2012 11:17:27:333] nserror: entry
[17-AUG-2012 11:17:27:333] nserror: nsres: id=0, op=78, ns=12557, ns2=12560; nt[0]=527, nt[1]=515, nt[2]=-1; ora[0]=0, ora[1]=0, ora[2]=0
[17-AUG-2012 11:17:27:333] nsc2addr: error exit
[17-AUG-2012 11:17:27:333] nslisten: error exit
[17-AUG-2012 11:17:27:333] nsglhfre: entry
[17-AUG-2012 11:17:27:333] nsglhrem: entry
[17-AUG-2012 11:17:27:333] nsglhrem: entry
[17-AUG-2012 11:17:27:333] nsglhrem: exit
[17-AUG-2012 11:17:27:333] nsglhfre: Terminating listening endpoint: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.corp.abc.co.in)(PORT=1521)))
[17-AUG-2012 11:17:27:333] nsdisc: entry
[17-AUG-2012 11:17:27:333] nsclose: entry
[17-AUG-2012 11:17:27:333] nstimarmed: entry
[17-AUG-2012 11:17:27:333] nstimarmed: no timer allocated
[17-AUG-2012 11:17:27:333] nstimarmed: normal exit
[17-AUG-2012 11:17:27:333] nttctl: entry
[17-AUG-2012 11:17:27:333] nttctl: entry
[17-AUG-2012 11:17:27:333] nsdo: entry
[17-AUG-2012 11:17:27:333] nsdo: cid=1, opcode=98, *bl=0, *what=0, uflgs=0x40, cflgs=0x2
[17-AUG-2012 11:17:27:333] nsdo: rank=64, nsctxrnk=0
[17-AUG-2012 11:17:27:333] nsdo: nsctx: state=7, flg=0x4002, mvd=0
[17-AUG-2012 11:17:27:333] nsevunreg: entry
[17-AUG-2012 11:17:27:333] nsevunreg: cid=1, sgt=0, rdm=0

listener trace shows following error before the error:

[17-Aug-2012 11:17:27:333] sntuscrt: illegal permission ====> folder permission issue. listener trace shows following message before the error:

Cause
1) Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.
2) Confirm that the user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.
3) If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.

To implement the solution, please use the following example:
1. cd /var/tmp
2. Check the whether the .oracle directory exists:
cd .oracle
3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777

mkdir /var/tmp/.oracle chmod 01777 /var/tmp/.oracle chown root /var/tmp/.oracle chgrp root /var/tmp/.oracle

4. Next try starting the TNS Listener using the ‘lsnrctl start <listener_name>’ command.

If still the issue persists...
Check read/write permission on /tmp to oracle user.Oracle will write some files under /tmp/.oracle and /var/tmp/.oracle, and the listener trace shows "illegal permission" error which exactly means it can't write on it. Also confirmed that oracle user does not have read/write permission on /tmp

Grant read/write permission on /tmp to oracle user

How to release undo space in oracle

Issue:
The database is not able to release UNDO space.

Findings:
a) Undo tablespace always shows 100% utilization

b) There is No error in the alert log

c) We have queried the v$undostat and we found the value of tuned_undo_retention is quite bigger than the undo_retention which is 54000

Solution:
Set "_smu_debug_mode= 33554432" in database

It should allow the expired segments to be released and re-used and you should see a drop in the number of unexpired segments.

There is no negative impact of using this parameter. Its the fixed value to specify its mode.

This mode helps auto tune of undo to work effectively and specially in such cases where max_query_length < tuned_undoretention, it helps alot.

Create SQL Profile

Sometimes we faced SQL plan changed issue.Using sqlt we can confirm whether plan of any sql has changed or not.

If plan is changed and we have best plan then we can set sql profile.

How to create SQL profile
Download the sqlt from metalink and Install it.
Once you generate the sqlt of any sqlid and confirmed the plan has changed then go to sqlt directory.

cd sqlt/utl
conn / as sysdba

SQL> START coe_xfr_sql_profile.sql 1jfdhkb18cg4c 3818097359;

1jfdhkb18cg4c --> SQL ID
3818097359--> BEST SQL hash value from sqlt report

This will generate a script to create the sql profile. Use it to create the profile.

ORA-1078 ORA-1565 Errors while starting database with SPFILE on ASM/RAC (shared location)

Fact:
Create pfile='/data01/pfile.ora' from spfile;
Did some changes in pfile
Startup the database using pfile
Now create spfile from pfile using coomand,
create spfile='/data01/oradata/ORCL/spfileORCL.ora' from pfile;
Then I startup the database Node1,but I got the below mentioned error

Node 1:
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/ices/data01/oradata/ORCL/spfileORCL.ora'
ORA-27041: unable to open file
HPUX-ia64 Error: 24: Too many open files
Additional information: 2

Node 2:
SYS@ORCL1>startup
ORA-03113: end-of-file on communication channel


Findings:
Error first states that it has failed to process the parameter. Second states that it has failed to identify the spfile and is unable to open.
But I was able to open the database normally with pfile. Also the spfile was present in the Shared Location and accessible from both the Nodes.

Problem here was that I had started the database with pfile from non-default location and pfile located in $ORACLE_HOME/dbs had following entries


Solution:
Specify the Pfile path manually while creation of Spfile

SQL> CREATE SPFILE='/data01/oradata/ORCL/spfileORCL.ora' from pfile='/data01/pfile.ora';
File created.

Now,retest the issue.

ORA-12012 ORA-31623: a job is not attached to this session via the specified handle

Issue:
Few Jobs are failing

Error Message:
Errors in file /orahome/oracle/product/10.2.0/admin/orcl/bdump/orcl1_j001_2694.trc:
ORA-12012: error on auto execute of job 716137
ORA-31623: a job is not attached to this session via the specified handle

Findings:
All these jobs are found to be scheduled to run at the same time (4AM daily)

The problem in running these jobs all at the same time is due to the fact that all these jobs are using the same Table 'DAYJOB'
So, each job tries to create a new table 'DAYJOB' and hence each job fails.

Solution:
1.) Schedule these jobs at different interval of time. Make sure that none of the jobs overlap with each other.
or
2.) Use a different table name for each of the jobs (DAYJOB1,DAYJOB2...etc).
     Please check with the application team the impact of using a different table name for each job. 

Bug 9689310 Excessive child cursors / high VERSION_COUNT / OERI:17059 due to bind mismatch

We have observed High version Count for the below queries in ORCL database

Data Collected:

SQL_ID        MODULE                                                           VERSION_COUNT

92bxfrxwu51v5 oracl@orcl01.xyz.in (TNS V1-V3)                                     17
087g1afdgd4fh oracl@orcl01.xyz.in (TNS V1-V3)                                     12
carh6s6nmqh5w oracl@orcl01.xyz.in (TNS V1-V3)                                     25
b0kxk28kwfhn9 otrans@picggx02.xyz.in (TNS V1-V3)                                     46
ftuhvswznfpv8 otrans@picggx02.xyz.in (TNS V1-V3)                                     17


SqlText ( b0kxk28kwfhn9)
INSERT INTO M_CACHE01_ITEM( MESG_TYP,SITE_ID,JOB_NO,JOB_DT,INV_S
ERNO,ITEM_NO,SCHEME_CD,RITC_CODE,DESC_GD01,DESC_GD02,DESC_GD03,Q
TY_UNITS,QUANTITY,ITEM_RATE,UNIT_OF_RATE,NO_OF_UNITS,PMV,NOTN_NO
,THIRD_PARTY,FILE_NAME,UNIQUE_ID) VALUES (:1,:2,:3,TO_DATE(:4,'Y
YYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:1
6,:17,:18,:19,:20,:21)


Cause:
V$SQL_BIND_METADATA showed that the bind lengths are different among cursors for the same bind variables


Impact:
 High amount of latch: library cache wait event is observed in the database due to high version count


Solution:
Apply Patch  9689310 

ORA-609 and TNS-12537 in 11g Alert.log

Issue:  ORA-609 and TNS-12537 in 11g Alert.log


Findings:
1) lsnrctl status hanged
2) conn user/pwd@orcl hanged
3) Database is up and running
4) Error in alert log
nt OS err code: 0
Fatal NI connect error 12537, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for Linux: Version 11.1.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.6.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.6.0 - Production
Time: 25-JAN-2012 12:38:49
Tracing not turned on.
Tns error struct:
ORA-609 : opiodr aborting process unknown ospid (4018_47693892033648)
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (4016_47051970669680)

Cause:
The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection
process before the server process was completely spawned.
Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.

Solution:
Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.
If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120


I had faced this issue on Linux Server.In may case the issue got resolved after changing one of the Kernel Parameter.
Change the value of net.ipv4.ip_forward from 0 to 1 in /etc/sysctl.conf file.
# Controls IP packet forwarding
net.ipv4.ip_forward = 1

Digital Certificate Expired Details

Digital Certificate Expired Details:
The digital certificate used to sign Oracle Forms deployment related JAR files has expired on Thu Mar 24 19:59:59 EDT 2011.
So, a dialog box appears whenever form application opens.


Impact on the Application:
The expiration of the JAR signature does not prevent the use of the product or applications created with it . It simply means that inspection of the certificate details will show the expiration date.  It also means that end users may be presented with a dialog at startup which indicates that the ce! rtificate has expired, but are offered an option to run the application regardless.

Choosing Cancel or Deny, will prevent the Forms applet from running.


Workaround:
Clicking on yes on the dialog box,  the application runs fine.


Solution:
Apply the following patches:
1.) Forms bundle patch: 9593176 (Size :5.9MB)
2.) Patch:11710576 (Size :4.7MB)


Validity of the solution:
These patches are available to extend the jar file expiration date to Sat Jul 06 19:59:59 EDT 2013.


Approach:
The patch will be applied on the Pre-prod environment first. Testing will be done in preprod first and if successful the patch will be applied to the Production Environment.

Listener Services stops automatically every 2-3 minutes

Issue:
Listener Services stops automatically every 2-3 minutes.


Error in listener log:
Started with pid=9907
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.1)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
26-FEB-2011 11:50:47 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=test01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870080)) * status * 0
26-FEB-2011 11:51:39 * service_register * unixpi * 0
26-FEB-2011 11:51:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=test01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870080)) * status * 0
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.1)(PORT=1521)))
26-FEB-2011 11:52:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=test01)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870080)) * stop * 0


Findings:
As listener it seems that indeed listener is stopped "automatically every 2-3 minutes" but this as a result of COMMAND=STOP
issues by USER=oracle (it's OS user here) and this is issued from HOST=test01 ... and command comes from a Oracle
software having as client libraries VERSION=169870080 (A200300 in hex which means 10.2.0.3) ... the only suggestion we can
offer is to check on the machine test01 for a script or (in case it's a cluster node) a rule in cluster software ... about a sequence
where it is checked the status,probably the script or the rules are having some bad conclusion about that ... and the consequence
is that the command issued is COMMAND=STOP. It is visible the same sequence all the time ....

(COMMAND=status)
(COMMAND=status)
(COMMAND=STOP)...

So there is a check about something expected to be found in the status command, the status is considered not the one expected
and as a result the listener is restarted. If this is a cluster then you will need the CLUSTER admin to check about the crs scripts...
The CRS scripts could provide the same results / symptoms if the checks for the as named "cluster resources" (listener is one)
will not be interpreted well. The script is checking is a listener status is OK in base of some scripts logics (specific to your script)
and if not then the listener is restarted. Usually such would require manual (human) interventions. Other possible source is a DBA
script running in in there ... performing the same check in loop. LSNRCTL STATUS (2 times) ... the result is parsed and if there is a
certain condition meed the listener is stopped using LSNRCTL STOP command... This is not a listener issue, some DBA have to
check and fix the condition checked or to simple disable such check ... Please verify and confirm this ...


Exact Issue:
The Application(hard coded) is checking the status of default listener and stop it.


Solution
Change the Name of the Listener.

ORA-00600: [kpnatdm-1], [2501], [533]

Errors in file /orahome/oracle/product/10.2.0/admin/orcl/udump/orcl2_ora_26752.trc:
ORA-00600: internal error code, arguments: [kpnatdm-1], [2501], [533], [], [], [], [], []

Findings:
The error encounters when the below mentioned SQL is fired by program oracle@test02 from machine test02 using operating system user orauser.

SELECT :1||"A1"."ERR_DESC"||',' FROM "DIR"."DI_ERROR_LIST" "A1" WHERE "A1"."ERR_CD"=SUBSTR(:1,1,3) AND "A1"."MESG_ID"='ALCHI01'

This type of argument [kpnatdm-1], [2501], [533] for ORA-00600 is unknown in the bug database.

Recomendation:
It is suggested to Upgrade the database to 10.2.0.5

ORA-48913 - error message in Oracle 11g alert log

Error Message in Alert Log:

On 11.2.0.1, encountered the following error in the alert.log.
Wed Jan 04 11:23:10 2012
Non critical error ORA-48913 caught while writing to trace file "/orahome/app/oracle/diag/rdbms/vis/vis2/trace/vis2_lmd0_991420.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

Cause:
Parameter MAX_DUMP_FILE_SIZE  is set too low


Solution:
Increase the setting for the parameter MAX_DUMP_FILE_SIZE or set it to unlimited

nt secondary err code in Oracle 11g Alert log

Error message in 11g database alert log:

Wed Jan 04 18:56:39 2012
*********************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 04-JAN-2012 18:56:39
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))


Changes:
No change are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database.
Note: Prior to 11gR1 these same 'Fatal NI connect error 12170' are written to the sqlnet.log


Cause:
These time out related messages are mostly informational in nature.
The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */


For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.  See (Doc ID 454927.1).

 
Suggested Actions:

- Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.

For the message incident below you would search the listener log for the 'Client address' string:

(ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))

The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '04-JAN-2012 18:56:39'.

-Corresponding listener log entry:

04-JAN-2012 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0

Note the time of the client corresponding client connection(s) in the listener log.  Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.

You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:

To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

- Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

BSLN_MAINTAIN_STATS_JOB is getting failed

Issue:
BSLN_MAINTAIN_STATS_JOB is getting failed




Error in Alert Log:
Sun Jan 01 13:30:01 2012
Errors in file /orahome/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_j000_1781806.trc:
ORA-12012: error on auto execute of job 353101
ORA-06550: line 1, column 780:
PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
ORA-06550: line 1, column 780:
PL/SQL: Statement ignored



Trace file:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /orahome/app/oracle/product/11.2.0/dbhome_1
System name:    AIX
Node name:      PROD
Release:        1
Version:        6
Machine:        00C8A8E54C00
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 56
Unix process pid: 1781806, image: oracle@PROD (J000)
*** 2012-01-01 13:30:01.195
*** SESSION ID:(308.19730) 2012-01-01 13:30:01.195
*** CLIENT ID:() 2012-01-01 13:30:01.195
*** SERVICE NAME:(SYS$USERS) 2012-01-01 13:30:01.195
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-01 13:30:01.195
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2012-01-01 13:30:01.195
ORA-12012: error on auto execute of job 353101
ORA-06550: line 1, column 780:
PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
ORA-06550: line 1, column 780:
PL/SQL: Statement ignored





Findings:
SQL> select object_name from dba_objects where object_id=353101;

OBJECT_NAME
-----------------------------------
BSLN_MAINTAIN_STATS_JOB


09:58:16 orcl1 > select log_date,status
from dba_scheduler_job_run_details
where job_name='BSLN_MAINTAIN_STATS_JOB'
order by log_date desc;

LOG_DATE                                                                    STATUS
------------------------------------------------------------------------ ------------
01-JAN-12 01.30.01.224309 PM +05:30                                         FAILED
25-DEC-11 01.30.00.855555 PM +05:30                                         FAILED
18-DEC-11 01.30.00.761203 PM +05:30                                         FAILED
11-DEC-11 01.30.00.583605 PM +05:30                                         FAILED
04-DEC-11 01.30.00.450731 PM +05:30                                         FAILED





Role of BSLN_MAINTAIN_STATS_JOB:
This job is a compute statistics job. This job runs the   BSLN_MAINTAIN_STATS_PROG program on the
BSLN_MAINTAIN_STATS_SCHED schedule. The program BSLN_MAINTAIN_STATS_PROG will keep the default
baseline's statistics up-to-date.




Cause:
Table DBSNMP.BSLN_BASELINES contains inconsistent information. After database cloning , existing records in table “DBSNMP.BSLN_BASELINES” conflict with new baseline information inserted in the cloned database. The DBSNMP user needs to be dropped and re-created.


Fixed:
SQL> @?/rdbms/admin/catnsnmp.sql

SQL> @?/rdbms/admin/catsnmp.sql

Check Oracle High Redo Generation

Step 1: Get the segment that experienced the most changes 
during  the period
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta)
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time
BETWEEN to_date('2008_05_02 12','YYYY_MM_DD HH24')
       AND to_date('2008_05_04 12','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name
 
Step 2: Run the following query to examine the changes during 
the period to what they were historically.
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        sum(db_block_changes_delta)
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND dhsso.object_name = 'OT_PENDING'
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')

 Step 3: Gather all SQL statements that were run during the 
period that referenced the table, using what is below
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%OT_PENDING%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
    AND dhss.sql_id = dhst.sql_id

 Step 4: Take the SQL_ID for the statement and searched 
through DBA_HIST_ACTIVE_SESS_HISTORY, using what is below
SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,
         program
  FROM dba_hist_active_sess_history
  WHERE sql_id = 'bxjz5d4p6x02u'
    AND snap_id BETWEEN 43212 AND 43220

One of the best document to check the high generation of redo:
http://appcrawler.com/wordpress/2009/04/15/who-is-generating-all-the-redo/