Oracle DBA User Guide

Friday, April 10, 2015

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

Tuesday, December 30, 2014

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.

Wednesday, November 5, 2014

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;

Monday, October 20, 2014

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

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..

Tuesday, July 15, 2014

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

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...

How to configure Oracle Enterprise Manager on two node cluster

[oracle@rac2 cfgtoollogs]$ emca -reconfig dbcontrol -cluster -EM_NODE rac2 -EM_NODE_LIST rac2

STARTED EMCA at Jul 15, 2014 3:04:51 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database unique name: orcl
Service name: orcl
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 15, 2014 3:04:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/em ca_2014_07_15_15_04_51.log.
Jul 15, 2014 3:05:03 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 15, 2014 3:05:05 PM oracle.sysman.emcp.EMAgentConfig performDbcReconfigurati on
INFO: Propagating /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl/sysman/confi g/emd.properties to remote nodes ...
Jul 15, 2014 3:05:05 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 15, 2014 3:06:21 PM oracle.sysman.emcp.EMDBPostConfig performDbcReconfigurat ion
INFO: Database Control started successfully
Jul 15, 2014 3:06:21 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMes sage
INFO:
**************** Current Configuration ****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
---------- ---------- ---------------------

orcl rac1 rac1.localdomain
orcl rac2 rac2.localdomain

Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 15, 2014 3:06:21 PM
[oracle@rac2 cfgtoollogs]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://rac2.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orc l/sysman/log
[oracle@rac2 cfgtoollogs]$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.4
OMS Version : 10.2.0.4.4
Protocol Version : 10.2.0.4.4
Agent Home : /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl
Agent binaries : /u01/app/oracle/product/11.2.0/dbhome_1
Agent Process ID : 9325
Parent Process ID : 9303
Agent URL : https://rac2.localdomain:3938/emd/main
Repository URL : https://rac2.localdomain:1158/em/upload/
Started at : 2014-07-15 15:05:07
Started by user : oracle
Last Reload : 2014-07-15 15:05:07
Last successful upload : 2014-07-15 15:10:36
Total Megabytes of XML files uploaded so far : 48.62
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 39.61%
Data channel upload directory : /u01/app/oracle/product/11.2.0/db home_1/rac2_orcl/sysman/recv
Last successful heartbeat to OMS : 2014-07-15 15:10:21
---------------------------------------------------------------
Agent is Running and Ready

Check the first node :

Node 1:

[oracle@rac1 emca]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://rac1.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac1_orcl/sysman/log

Node 2:

[oracle@rac2 cfgtoollogs]$ emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

https://rac2.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.

------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/rac2_orcl/sysman/log

[oracle@rac2 cfgtoollogs]$

This is indicates two Grid controls for two individual node with 2 instance.

Thank you...

Saturday, July 12, 2014

Metadata file does not match checksum Trying other mirror.

Error message

http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/repodata/filelists.xml.gz: [Errno -1] Metadata file does not match checksum
Trying other mirror.
public_ol6_latest/primary | 39 MB 01:48
http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/repodata/primary.xml.gz: [Errno -1] Metadata file does not match checksum
Trying other mirror.
Error: failure: repodata/primary.xml.gz from public_ol6_latest: [Errno 256] No more mirrors to try.

Solution :

I have seen the "Metadata file does not match checksum" error once too and was able to resolve it by:

1) Setting http_caching=none in /etc/yum.conf file

2) yum clean metadata

3) yum update

It will work fine......

Thank you.......