Site-Wide Activity

Oracle Database 12c Release 1 (12.1) RAC On RedHat Linux 5
Pre-requisites :

Before you install the Grid,Clusterware and the Database, you must complete several pre-installation tasks.

1) 2 Public IP’s – […]

Release : Oracle EM 12c Release 4 (12.1.0.4)
Problem :
Running emcli setup command gives session expired error message.
for e.g :!–more–

[oraemagent@progcsrv01 ~]$ emcli setup […]

We have tried changing weblogic password from command line considering the password is lost or forgotten Oracle EBS R12(12.2.x)!–more–

Actions to be taken :

Shut down all running services (adstpall.sh), […]

Recently I had one issue after I applied a one-off patch (17618077), application got distorted which shows in below screen:

 

Cause:

This issue is documented in BUG:12852588  FIELDS APPEAR A BIT […]

The following query helps us to check the total size and free space of individual tablespaces in a tabular format

set linesize 1200
set pagesize 1200
column tablespace_name format a20 heading […]

Database Link

A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another.

The following link types are supported:

Private database link- belongs to a specific schema of a database. Only the owner of a private database link can use it.
Public database link- all users in the database can use it.
Global database link- defined in an OID or Oracle Names Server. Anyone on the network can use it.

What is a database link?

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. A database link connection allows local users to access data on a remote database.

Prerequisites:

To create a database link, you must have the CREATE DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

In the following example:
There are two databases: DEV and PORTAL
We are creating a db link on DEV so that we can acces the tables of portal user from DEV database.

Make TNS Entries on both databases

dev =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.1.106)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev)
)
)

Portal_New =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.1.115)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = portal)
)
)

Check TNS on both databases

From portal database
C:Documents and SettingsAdministrator>tnsping dev

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 – Production on 16-SEP-2
014 23:20:52

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:oracle11gproduct11.1.0db_1networkadminsqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.254.
0.206) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev))
)
OK (10 msec)

From dev database
oratst#tnsping portal_new

TNS Ping Utility for HPUX: Version 10.2.0.3.0 – Production on 17-SEP-2014 12:45:30

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/Clone/db03/oracle/10R2/network/admin/DEV_oratst/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.115) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = portal)))
OK (110 msec)

connect to dev database from apps user:
sqlplus apps/apps

Creating a Private Database Link:

create database link db link name connect to username identified by “password” using ‘tns name’;

In the following statement, user portal on the remote database defines a fixed-user database link named Portal_New to the portal schema on the local database:

create database link DEVTOPORTAL.192.168.1.106 connect to portal identified by "portal123" using 'Portal_New';

Once this database link is created, you can query tables in the schema PORTAL on the remote database in this manner:


SELECT * FROM portal.emp@DEVTOPORTAL.192.168.1.106;


How to check DB link:

select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
APPS devtoportal.192.168.1.106 PORTAL portal_new 29-APR-14

TESTING:

SQL> select * from dual@”devtoportal.192.168.1.106";

D
-
X


For dropping a database link.

drop database link;


drop database link DEVTOPORTAL.192.168.1.106;


Cause:
Blocking sessions occur when one session holds an exclusive lock on an object and doesn’t release it before another session wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.
Solution:
The following query shows all the blocking sessions which can help you to identify the problem.

col WAIT_CLASS for a12;
select blocking_session,sid,serial#,wait_class,seconds_in_wait,status from v$session where blocking_session is not NULL order by blocking_session;

BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT STATUS
—————- ———- ———- ———— ————— ——–
5290 5527 590 Application 2146 ACTIVE
5438 5024 5728 Application 118 ACTIVE
5438 5369 4674 Application 2025 ACTIVE
5438 5251 2139 Application 2072 ACTIVE
5499 5438 2805 Application 2073 ACTIVE
5508 5901 4208 Application 6468 ACTIVE
5898 5290 4533 Application 2149 ACTIVE
5901 5019 11812 Application 5163 ACTIVE
5901 5135 1236 Application 6045 ACTIVE
5901 4934 1797 Application 792 ACTIVE
5901 5947 583 Application 1985 ACTIVE
5901 5204 4373 Application 241 ACTIVE
5901 5751 2989 Application 2171 ACTIVE
5901 5752 3331 Application 3641 ACTIVE
5993 5646 544 Commit 0 ACTIVE
5993 5486 3187 Commit 0 ACTIVE

16 rows selected.

In this case, we find that session 5901 is blocking 7 other sessions.


select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5901

SID SERIAL# EVENT MODULE ACTION STATUS PROGRAM BLOCKING SESSION LAST_CALL_ET
———————————————————————————–
5901 4208 enq: TX – row lock contention WSHINTERFACE Concurrent Request ACTIVE 5508 6493

From the above query we find that session 5901 is being blocked by session 5508.


select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5508

SID SERIAL#
———- ———-
EVENT
—————————————————————-
MODULE
————————————————
ACTION STATUS
——————————– ——–
PROGRAM BLOCKING_SESSION LAST_CALL_ET
———————————————— —————- ————
5508 7300
SQL*Net message from client
OEXOETEL
FRM:CLIMAM:Receiptionist Parts – INACTIVE
7554

From the above query we find that session 5508 has become inactive.

In this case, we find that session 5901 is blocking 7 other sessions which in turn has been blocked by session 5508 and has been for 7554 seconds.

So, we kill this inactive session.
To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement:

SQL> alter system kill session ‘sid,serial#’;


alter system kill session'5508,7300';

System altered.

This command tells the specified session to rollback any un-committed changes and release any acquired resources before terminating cleanly. In some situations, this cleanup processing may take a considerable amount of time, in which case the session status is set to “marked for kill” until the process is complete and not killed immediately, the alter system kill session command can be forced by adding the immediate keyword:

SQL> alter system kill session ‘sid,serial#’ immediate;

Cause
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work.

 

Please execute the below query to find the blocking sessions and kill if necessary.
Solution

col WAIT_CLASS for a12;
select blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait,status
from
v$session
where
blocking_session is not NULL
order by
blocking_session;


We have installed OID & OAM with same version 11.1.1.7. Starting managed service in OAM domain was giving error, when we checked the logs it was giving below information
Caused By: […]

The most important new feature in Oracle E-Business Suite Release 12.2 is the ability to patch a running system, instead of having to take the system down for a significant period of time while the patches are […]

Oracle E-Business Suite Release 12.2 has seen significant changes to the locations under which log files are stored. In large part, this is because of the introduction of online patching and the additional file […]

Lets start with 12.2.0 and learn the installation steps for 12.2.0 on Linux box(64) bit.

 

Operating System Requirements for OEL/RHEL 5.8 (x86-64)

Update 5 (5.5) or higher of Oracle Linux 5 is […]

We have Oracle EBS running on a Windows 2008R2 64 bit machine and Oracle EM12c running on a Linux (RHEL 6.3) machine.
We are trying to deploy an agent on our Windows box however it fails with the below error […]

Upgrading Oracle Application database to 11.2.0.3 with EBS R12(12.1.1)
Application version : 12.1.1
Database Current Version : 11.1.0.7
Database Name : TEST
Operating System : Red Hat Enterprise Linux 5 (64 […]