Connection issue from

While connecting to Database from client or using DB link if you face below issue , Please follow below mentioned steps.

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

I wrote this piece after a team of operation at my workplace required a partial database. When I prepared what they wanted and sent them a TNS to connect, they informed me that they could not  connect because of our login trigger.

That was quite easy to understand the problem. Our login trigger was recording sessions in a table that resides on a tablespace that I have not restored =)

If you wat to disable all the system triggers in your database, you must set an undocumented parameter in your parameter file as below:

_system_trig_enabled=FALSE

 alter system set "_system_trig_enabled"=FALSE; --- On Database side.

Thanks.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch Log file:

 Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5931)
StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:644)
StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1088)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
StackTrace: java.lang.reflect.Method.invoke(Method.java:592)
StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:317)
StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2581)
StackTrace: oracle.opatch.OPatch.main(OPatch.java:626)


Cause:

Linux/Unix:

According to the above  error message, oracle binary ($ORACLE_HOME/bin/oracle) is still active or in use.
It can also  fail with other ORACLE_HOME active files/libraries .
Example:
/u01/app/oracle/10.2.0/bin/tnslsnr
/u01/app/oracle/11.2.0.3/lib/libclntsh.so.11.1

Windows:

Please be aware that before applying patches, you must shutdown the database, listener, sqlplus, EM dbconsole/dbcontrol and any and all processes, including 3rd-party processes, which use files in the ORACLE_HOME.  The error messages in the OPatch log files show that there are one or more programs still running that are using files in the ORACLE_HOME you are trying to patch.

Linux/Unix:

Verify and ensure that all database instances/services that are running from the $ORACLE_HOME being patched are cleanly shutdown.
Also  identify the process that is using these active files/libraries. Then stop the process who is still accessing it and then apply/rollback the patch.

To identify active process, execute

$ fuser -c $ORACLE_HOME

Windows:

If the database and Oracle services are already shutdown, then perhaps it is a 3rd-party program.  If you do not already have it, you should consider installing "Process Explorer" from Microsoft.  After starting procexp.exe, press Ctl-F (Find) and type in one of the DLLs from the "active" list (in the OPatch log). For this example, based on the "active" list shown above, the first DLL to check is orageneric11.dll.  That should tell you what process is using that DLL so you can shut it down. Process Explorer is fairly easy to use, but if you need help with downloading or using it, please contact Microsoft Support as usage of Process Explorer is outside the scope of Oracle Support.

There are a variety of non-Oracle 3rd-party programs which could be using files in the ORACLE_HOME.  One culprit is WMI (Windows Management Instrumentation).  Once you shutdown the 3rd-party process(es) using files in the ORACLE_HOME, and Process Explorer comes back clean, then attempt to apply the patch again and the OUI-67074 error should not reoccur.

Thanks.

How to check Oracle Application URL in 11i and R12 on UNIX prompt

Connect to Application tier with applmgr account.

[appl@prapp1 ~]$cat SID_hostnameapp1.xml| grep http | grep login

How to find Oracle Applications Login URL from database

Execute either of the following query logging into Oracle Applications database from "APPS" user:-

1.
SELECT home_url
  FROM icx_parameters;


2.
SELECT profile_option_value
  FROM fnd_profile_option_values
 WHERE profile_option_id=
   (SELECT profile_option_id                          
      FROM fnd_profile_options WHERE profile_option_name ='APPS_FRAMEWORK_AGENT')                            
   AND level_value = 0;

Thanks

ORA-00020: maximum number of processes (1000) exceeded

ORA-00020: maximum number of processes (1000) exceeded

Alert log:

ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Dec 29 22:48:29 2014
ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Mon Dec 29 22:52:32 2014
ORA-00020: maximum number of processes (1000) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

Solution:

If you cannot connect to the sqlplus traditinal method use prelim option. This is one of the bypass option to connect the sqlplus.

sqlplus -prelim is a backdoor method to connect the sqlplus where no othere method not supporting to create new session. It will connect only SGA and it will not create any new session.

$ sqlplus -prelim

oradb@CGABCDRADBOP01:/oracle_home/app $ sqlplus -prelim

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 30 13:04:12 2014

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

Enter user-name: / as sysdba
 
SQL>

Bounce the DB and  you can increase the processes with below options.


processes=x
sessions=x*1.1+5
transactions=sessions*1.1
sql> show parameter sessions

sql> show parameter processes
sql> show parameter transactions
sql> alter system set processes=500 SCOPE=SPFILE; 
sql> alter system set sessions=555 SCOPE=SPFILE; 
sql> alter system set transactions=610 SCOPE=SPFILE; 

and Restart the database.

Below parameters values also must be changed.....

sql>alter system set open_cursors=1500 scope=both;
sql>alter system set processes=3000 scope=spfile;
sql>alter system set job_queue_processes=4 scope=both;
sql>alter system set undo_retention=10800 scope=both;
sql>alter system set max_shared_servers=50 scope=both;
sql>alter system set db_files=2000 scope=spfile; 
sql>alter system set sessions=3305 SCOPE=SPFILE;  
sql>alter system set transactions=3635 SCOPE=SPFILE; 

Issue get fixed.

1. After you connect with “-prelim” mode, it’s also possible to use oradebug commands to examine the situation. 

2. It will not create new session and connecting only SGA. So you can performance all the oradebug utilities to analyse the issue.
 

List Session ID for a Given OS Process ID

-- -- List Session ID for a Given OS Process ID. --


 SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT
   CHR(10)||
'Check for SESSION ID ----> '||LPAD( s.sid, 4 )||CHR(10)||CHR(10) as "Session ID"
FROM   
v$session s, v$process p
WHERE   
p.addr = s.paddr
AND   
p.spid = &Enter_OSPid /


========================================


SELECT
a.sid,
b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
and b.spid = 1234;

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

test db$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.6

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

 

 

Oracle Home       : /test_bin/prod/db/tech_st/11.2.0.4

Central Inventory : /home/test/oraInventory

   from           : /test_bin/prod/db/tech_st/11.2.0.4/oraInst.loc

OPatch version    : 11.2.0.3.6

OUI version       : 11.2.0.4.0

Log file location : /test_bin/prod/db/tech_st/11.2.0.4/cfgtoollogs/opatch/opatch2014-10-20_05-31-01AM_1.log

 

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

 

 

Following executables are active :

/test_bin/prod/db/tech_st/11.2.0.4/lib/libclntsh.so.11.1

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /test_bin/prod/db/tech_st/11.2.0.4/cfgtoollogs/opatch/opatch2014-10-20_05-31-01AM_1.log

 

OPatch failed with error code 73

Description:

Your Listener is in active status. It means Listener is up. So ensure that  Instance & Listener must be down when applying the Opatch.

After bounce the Listener try the opatch apply steps.

Thanks..

ORA-00844: Parameter not taking MEMORY_TARGET into account ORA-00851: SGA_MAX_SIZE ****** cannot be set to more than MEMORY_TARGET ********. ORA-01078: failure in processing system parameters

SQL> startup ;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2449473536 cannot be set to more than MEMORY_TARGET 855638016.
ORA-01078: failure in processing system parameters;



[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 15 17:51:47 2014

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

SQL> conn sys/admin@orcl as sysdba

Connected to an idle instance.

 SQL> create pfile='/u01/app/oracle/product/pfile.ora' from SPFILE='+DATA/orcl/spfileorcl.ora';

File created.

Note: switch to grid user and connect ASM instance (asmcmd) find the spfile location.

Go to pfile location and remove below lines;

*.sga_max_size=123456789

*.sga_target=0

save the file.

[oracle@rac1 product]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 15 18:26:55 2014

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

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/pfile.ora';

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1348244 bytes

Variable Size             616566124 bytes

Database Buffers          226492416 bytes

Redo Buffers                5124096 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile='/u01/app/oracle/product/pfile.ora';

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[oracle@rac1 product]$ srvctl start database -d ORCL
[oracle@rac1 product]$ srvctl status database -d ORCL
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2
[oracle@rac1 product]$
[oracle@rac1 product]$


Thank you...