Category Archives: DB Generic

Error TNS-12543

Error TNS-12543 while connect to a remote database on a Linux

Issue:

Environment

Source: rac1 192.168.2.101
Target: rac2 192.168.2.102

[oracle@rac1 ~]$ tnsping CDB1_DG

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-JUL-2018 10:53:54

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
TNS-12543: TNS:destination host unreachable
[oracle@rac1 ~]$

Action Plan:

-- Verify Firewall on target database
-- logon to RAC2 (192.168.2.102)

[root@rac2 ~]# hostname
rac2.rajasekhar.com
[root@rac2 ~]#
[root@rac2 ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0
3    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination
1    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

[root@rac2 ~]#
[root@rac2 ~]# service iptables stop  <--- turn off firewall
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
[root@rac2 ~]#
[root@rac2 ~]# chkconfig iptables off
[root@rac2 ~]#
[root@rac2 ~]# service iptables status
iptables: Firewall is not running.  <-----
[root@rac2 ~]#

[oracle@rac1 ~]$ tnsping CDB1_DG

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-JUL-2018 11:05:12

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
OK (0 msec)
[oracle@rac1 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

Create Service using DBMS_SERVICE

Create Service using DBMS_SERVICE

AIM: We want to create new service (TEST) in database POCNDEV without using NETCA/NETMGR

[oracle@rac2 ~]$ ps -ef | grep tns
root         9     2  0 05:10 ?        00:00:00 [netns]
oracle    3468     1  0 05:13 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    5301  3976  0 05:38 pts/1    00:00:00 grep tns
[oracle@rac2 ~]$ lsnrctl status LISTENER | grep -i TEST <-- no output
[oracle@rac2 ~]$

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
POCNDEV   READ WRITE

SQL>

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'TEST', NETWORK_NAME=>'TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.START_SERVICE('TEST');

PL/SQL procedure successfully completed.

SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [POCNDEV] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ lsnrctl status LISTENER | grep -i TEST
Service "TEST.rajasekhar.com" has 1 instance(s). <----
[oracle@rac2 ~]$

[oracle@rac2 ~]$ lsnrctl status LISTENER
..
..
Service "TEST.rajasekhar.com" has 1 instance(s).
  Instance "POCNDEV", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin/
[oracle@rac2 admin]$ cat tnsnames.ora | grep -i TEST
[oracle@rac2 admin]$

-- dbms_service.CREATE_SERVICE will not add TNS entry to tnsnames.ora file.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar

Create Service using DBMS_SERVICE

Create Service using DBMS_SERVICE

AIM: We want to create new service (TEST) in database POCNDEV without using NETCA/NETMGR

[oracle@rac2 ~]$ ps -ef | grep tns
root         9     2  0 05:10 ?        00:00:00 [netns]
oracle    3468     1  0 05:13 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    5301  3976  0 05:38 pts/1    00:00:00 grep tns
[oracle@rac2 ~]$ lsnrctl status LISTENER | grep -i TEST <-- no output
[oracle@rac2 ~]$

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
POCNDEV   READ WRITE

SQL>

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'TEST', NETWORK_NAME=>'TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.START_SERVICE('TEST');

PL/SQL procedure successfully completed.

SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [POCNDEV] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ lsnrctl status LISTENER | grep -i TEST
Service "TEST.rajasekhar.com" has 1 instance(s). <----
[oracle@rac2 ~]$

[oracle@rac2 ~]$ lsnrctl status LISTENER
..
..
Service "TEST.rajasekhar.com" has 1 instance(s).
  Instance "POCNDEV", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin/
[oracle@rac2 admin]$ cat tnsnames.ora | grep -i TEST
[oracle@rac2 admin]$

-- dbms_service.CREATE_SERVICE will not add TNS entry to tnsnames.ora file.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar

Database Link

Database link

1. Overview
2. Environment
3. Add TNS Entry
4. List db links
5. Create PUBLIC db link
6. Create PRIVATE db link
7. List db links again
8. Verify the db link results
9. Drop Public Database link
10. Drop Private Database link


1. Overview

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

Type of Database Links:

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.

How to find Global name? SELECT * FROM global_name;


2. Environment

Source Details

Hostname: rac1.rajasekhar.com

DB Name: w148p

Schema name/password: scott/tiger

TNS Entry: 

w148p =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w148p)
    )
  )

Target Details

Hostname: rac2.rajasekhar.com

DB Name: CAT

Schema name/password: test/test

TNS Entry: 

CAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )


3. Add TNS Entry

Add target db TNS entry in source database tnsnames.ora

CAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )


4. List db links

SQL> select * from dba_db_links;

no rows selected

SQL>


5. Create PUBLIC database link

-- create public db link On Source db W148P 

We want to access the TEST schema objects (resides on CAT database) from source db (w148p Database) 

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
W148P     READ WRITE

SQL> show user
USER is "SYS"
SQL>

CREATE PUBLIC DATABASE LINK link_name
CONNECT TO remote_user_name
IDENTIFIED BY remote_user_password
USING 'remote_service_name';

SQL> CREATE PUBLIC DATABASE LINK test_remote
   CONNECT TO test IDENTIFIED BY test
   USING 'CAT';  2    3

Database link created.

SQL>

--- OR ---

-- Create Public db link without modify TNS entry

CREATE PUBLIC DATABASE LINK test_remote1
 CONNECT TO test IDENTIFIED BY test
 using
 '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )'
  /

SQL> show user
USER is "SYS"
SQL> CREATE PUBLIC DATABASE LINK test_remote1
 CONNECT TO test IDENTIFIED BY test
  2    3   using
  4   '(DESCRIPTION =
  5      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
  6      (CONNECT_DATA =
  7        (SERVER = DEDICATED)
  8        (SERVICE_NAME = cat)
  9      )
 10    )'
 11    /

Database link created.

SQL>

-- OR --

-- Create PUBLIC DB Link Using EASY CONNECT

SQL> CREATE PUBLIC DATABASE LINK test_remote2
CONNECT TO test IDENTIFIED BY test
USING 'rac2.rajasekhar.com:1521/CAT';  2    3

Database link created.

SQL>


6. Create PRIVATE database Link

Private database link belongs to a specific schema of a database. 

Only the owner of a private database link can use it.

CREATE DATABASE LINK link_name
CONNECT TO remote_user_name
IDENTIFIED BY remote_user_password
USING 'remote_service_name';

SQL> grant create database link to scott;

Grant succeeded.

SQL> conn scott/tiger; <-- If you don't know password then use proxy user . PROXY USER
Connected.
SQL>

SQL> CREATE DATABASE LINK REMOTE_PRIVATE1
   CONNECT TO test IDENTIFIED BY test
   USING 'CAT';  2    3

Database link created.

SQL>

-- OR --

-- Create PRIVATE db link without modify TNS entry

CREATE DATABASE LINK REMOTE_PRIVATE2
 CONNECT TO test IDENTIFIED BY test
 using
 '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )'
  /

SQL> CREATE DATABASE LINK REMOTE_PRIVATE2
 CONNECT TO test IDENTIFIED BY test
  2    3   using
  4   '(DESCRIPTION =
  5      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
  6      (CONNECT_DATA =
  7        (SERVER = DEDICATED)
  8        (SERVICE_NAME = cat)
  9      )
 10    )'
 11    /

Database link created.

SQL>

-- OR --
-- Create DB Link Using EASY CONNECT

SQL> CREATE DATABASE LINK REMOTE_PRIVATE3
CONNECT TO test IDENTIFIED BY test
USING 'rac2.rajasekhar.com:1521/CAT';  2    3

Database link created.

SQL>

SQL> conn / as sysdba
Connected.
SQL> revoke create database link from scott; <----

Revoke succeeded.

SQL>


7. List database links again

SQL> set lines 180 pages 999
SQL> col owner for a15
SQL> col DB_LINK for a15
SQL> col USERNAME for a15
SQL> col HOST for a39
SQL> col CREATION_DATE for a20
SQL> select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;

OWNER           DB_LINK         USERNAME        HOST                                    CREATION_DATE
--------------- --------------- --------------- --------------------------------------- --------------------
PUBLIC          TEST_REMOTE     TEST            CAT                                     11/06/2016 21:02:22

PUBLIC          TEST_REMOTE1    TEST            (DESCRIPTION =                          11/06/2016 21:37:58
                                                    (ADDRESS = (PROTOCOL = TCP)(HOST =
                                                rac2.rajasekhar.com)(PORT = 1521))
                                                    (CONNECT_DATA =
                                                      (SERVER = DEDICATED)
                                                      (SERVICE_NAME = cat)
                                                    )
                                                  )

PUBLIC          TEST_REMOTE2    TEST            rac2.rajasekhar.com:1521/CAT            11/06/2016 21:43:00

SCOTT           REMOTE_PRIVATE1 TEST            CAT                                     11/06/2016 22:10:47

SCOTT           REMOTE_PRIVATE2 TEST            (DESCRIPTION =                          11/06/2016 22:11:25
                                                    (ADDRESS = (PROTOCOL = TCP)(HOST =
                                                rac2.rajasekhar.com)(PORT = 1521))
                                                    (CONNECT_DATA =
                                                      (SERVER = DEDICATED)
                                                      (SERVICE_NAME = cat)
                                                    )
                                                  )

SCOTT           REMOTE_PRIVATE3 TEST            rac2.rajasekhar.com:1521/CAT            11/06/2016 22:12:13

6 rows selected.

SQL>


8. Verify the db link results

-- Since it is public db link any user in source database can access the TEST schema objects of targert db

-- Even new user can access. eg.... create new user <----

--- verfiy public db links ---

SQL> create user one identified by one; <----

User created.

SQL> grant connect to one;

Grant succeeded.

SQL> conn one/one; <----
Connected.
SQL> select count(*) from sales@TEST_REMOTE; <-- with TNS entry

  COUNT(*)
----------
    918843

SQL> select count(*) from sales@TEST_REMOTE1; <-- with TNS Connect String

  COUNT(*)
----------
    918843

SQL> select count(*) from sales@TEST_REMOTE2; <-- Easy connect string

  COUNT(*)
----------
    918843

SQL>

--- verfiy private db links ---

Private database link belongs to a specific schema of a database. 

Please note only the owner of a private database link can use it.

SQL> conn scott/tiger;
Connected.
SQL> select count(*) from sales@REMOTE_PRIVATE1; <-- With TNS Entry

  COUNT(*)
----------
    918843

SQL> select count(*) from sales@REMOTE_PRIVATE2;<-- TNS connect string

  COUNT(*)
----------
    918843

SQL> select count(*) from sales@REMOTE_PRIVATE3; <-- Easy connect

  COUNT(*)
----------
    918843

SQL>


9. Drop Public Database link

-- Please login as owner of db link

SQL> drop public database link TEST_REMOTE;

Database link dropped.

SQL> drop public database link TEST_REMOTE1;

Database link dropped.

SQL> drop public database link TEST_REMOTE2;

Database link dropped.

SQL>


10. Drop Private Database link

-- Please login as owner of db link

SQL> conn scott/tiger; <----
Connected.
SQL> drop database link REMOTE_PRIVATE1;

Database link dropped.

SQL> drop database link REMOTE_PRIVATE2;

Database link dropped.

SQL> drop database link REMOTE_PRIVATE3;

Database link dropped.

SQL>

SQL> conn / as sysdba
Connected.
SQL> select * from dba_db_links;

no rows selected <----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference

http://www.morganslibrary.org/reference/db_link.html
https://oracle-base.com/articles/misc/managing-database-links

Thank you,
Rajasekhar Amudala

Proxy User

Proxy User and Connect Through

1. Overview
2. Grant Proxy authentication
3. List Proxy Users
4. Revoke Proxy authentication


1. Overview

-- We want to login to database with user scott, but i don't the password.
-- We can change the scott's user password, but i don't want to do this,
   becuase some apps jobs might be  interrupted
-- It is possible using Proxy User and Connect Through


2. Grant Proxy authentication

CONN / AS SYSDBA

CREATE USER test_raj IDENTIFIED BY raj; <-- create dummy user

ALTER USER scott GRANT CONNECT THROUGH test_raj;

CONN test_raj[scott]/raj

SHOW USER

Output

SQL> CONN / AS SYSDBA
Connected.
SQL> CREATE USER test_raj IDENTIFIED BY raj;

User created.

SQL> ALTER USER scott GRANT CONNECT THROUGH test_raj;

User altered.

SQL> CONN test_raj[scott]/raj
Connected.
SQL> SHOW USER
USER is "SCOTT"
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL>


3. List Proxy Users

SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM proxy_users;

PROXY     CLIENT  AUT FLAGS
--------- ------- --- -----------------------------------
TEST_RAJ  SCOTT   NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL>


4. Revoke Proxy authentication

SQL> conn / as sysdba
Connected.
SQL> ALTER USER scott REVOKE CONNECT THROUGH test_raj;

User altered.

SQL> SELECT * FROM proxy_users;

no rows selected

SQL>

SQL> CONN test_raj[scott]/raj
ERROR:
ORA-28150: proxy not authorized to connect as client


Warning: You are no longer connected to ORACLE.
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

Blocking Sessions

How to find Blocking Sessions

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 session until the first session has done its work.

1. Simulation
2. Finding Out Who’s Holding a Blocking Lock
3. Solution


1. Simulation

Session 1:

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> create table t (a varchar2(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where a='z' for update;

A
-
z

SQL>
SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46

SQL>

Session 2:

In second session try to update the rows which you have selected above. 

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
39

SQL>
SQL> update t set a='x' where a='z';

-- hanging here --
..
..


2. Finding Out Who’s Holding a Blocking Lock

sqlplus / as sysdba

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;
	
select sid, serial#, username,status from v$session where sid in ('holder','waiter');

-- OR --

select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;

-- OR --

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;

-- OR --
   
SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
   
-- OR --

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';
SELECT object_name FROM dba_objects WHERE object_id='&object_id_from_above_output';
select sid,type,lmode,request,ctime,block from v$lock;
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;

Output:

SQL> SELECT s1.username || '@' || s1.machine
  2      || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
  3    4    5      WHERE s1.sid=l1.sid AND s2.sid=l2.sid
  6      AND l1.BLOCK=1 AND l2.request > 0
  7      AND l1.id1 = l2.id1
  8      AND l1.id2 = l2.id2;

BLOCKING_STATUS
----------------------------------------------------------------------------------
SH@rac2.rajasekhar.com ( SID=46 )  is blocking SH@rac2.rajasekhar.com ( SID=39 )

SQL> select sid, serial#, username,status from v$session where sid in (46,39);

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        39         77 SH                   ACTIVE <-- waiter
        46         13 SH                   INACTIVE <-- Holder

SQL>


SQL>

-- OR --

SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;  2    3    4    5    6

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 46                                           589826       1571          6          0 TX
Waiter: 39                                           589826       1571          0          6 TX

SQL>

-- OR --

SQL> SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;  2    3    4    5    6    7    8    9

BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------
              46         39         77            1626

SQL>

-- OR --

SQL> SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;  2    3    4    5    6    7    8    9

BLOCKING_SESSIONS
----------------------------------------------------------
46 is blocking 39

SQL>


3. Solution


Inform to the holder to commit/rollback.

--- OR ----

kill the holder session, if it is ok

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,13';

System altered.

SQL>

-- After killing holder session, waiter session got completed

SQL> update t set a='x' where a='z';

1 row updated.

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

ORA-00054

ORA-00054 resource busy and acquire with NOWAIT specified

1. Simulation
2. Identify the session that has locked the resource
3. Solution


1. Simulation

To simulate this error for learning purposes, do as below in 2 sessions

Session 1 :

[oracle@rac2 ~]$ sqlplus sh/sh;
SQL> create table lock_table (a number);

Table created.

SQL> insert into lock_table values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> update lock_table set a=500;

1 row updated.

SQL>

---- Do not commit -----

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46 <----

SQL>

Session 2:

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/  2    3    4    5    6
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 4

SQL>


2. Identify the session that has locked the resource

The below query helps to identify the session that has locked the resource that your session is trying to lock.

[oracle@rac2 ~]$ sqlplus / as sysdba

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');

select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (46);

-- OR --

set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
  ,  s.sid || ',' || s.serial# "SID,SESSION#"
  ,  owner || '.' || object_name object
  ,  object_type
  ,  decode( l.block
     ,       0, 'Not Blocking'
     ,       1, 'Blocking'
     ,       2, 'Global') status
  ,  decode(v.locked_mode
    ,       0, 'None'
    ,       1, 'Null'
    ,       2, 'Row-S (SS)'
    ,       3, 'Row-X (SX)'
    ,       4, 'Share'
    ,       5, 'S/Row-X (SSX)'
    ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
 from       v$locked_object v
 ,  dba_objects d
 ,  v$lock l
 ,  v$session s
 where      v.object_id = d.object_id
 and        v.object_id = l.id1
 and        v.session_id = s.sid
 order by oracle_username,session_id;
 
To identify the locked rows, use the below query:

set lines 200
col object_name for a30
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from    v$session s
,   dba_objects do
where   s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('&OBJECT_NAME_WITHOUT_SCHEMA_NAME')
;

-- OR --

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

Output

SQL> SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');  2    3    4

 OBJECT_ID SESSION_ID    INST_ID
---------- ---------- ----------
     82844         46          1  <--- Please note session ID 46

SQL> select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (46);  2    3

       SID    SERIAL#    INST_ID PROGRAM                                          SPID    
---------- ---------- ---------- ------------------------------------------------ --------
        46         11          1 sqlplus@rac2.rajasekhar.com (TNS V1-V3)          5768

SQL>


3. Solution

You can try executing the query/job after some time (to allow the other session to complete). 

--- OR ----

kill the session that has locked the resource

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,11';

System altered.

SQL>


SQL> declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/  2    3    4    5    6

PL/SQL procedure successfully completed. <-- executed without error after killing problematic session 

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

Enable Archivelog

Enable the database in Archivelog mode.

Step 1: Set the following parameters in parameter file.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/oradata/MYSID/archive/' SCOPE=both;

Step 2: Enable archivelog using below commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ARCHIVE LOG LIST;
ALTER DATABASE OPEN;

It is recommended to take a full backup before/after you brought the database in archive log mode.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. 

Change Archive Dest

How to change Archive destination

Use similar commands to change archive destination, to change archive destination no need to bounce the database.

1. Verify existing values

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch/vada <------
Oldest online log sequence     147
Next log sequence to archive   149
Current log sequence           149
SQL>
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch/vada

SQL>

2. Modify the archive destination

SQL> alter system set log_archive_dest_1='LOCATION=/u02/arch/vada' scope=both;

System altered.

SQL>

3. Verify the results

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch/vada <----
Oldest online log sequence     147
Next log sequence to archive   149
Current log sequence           149
SQL>
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u02/arch/vada

Note: If you set log_archive_dest_1 and log_archive_dest_2, it will store same log file in both locations.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Drop Database

How to Drop the Database Manually

Note: DROP DATABASE command will delete the database. The database must be mounted exclusive and not open, and started in RESTRICT mode.  DO NOT RUN ON PRODUCTION

SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             666897792 bytes
Database Buffers          163577856 bytes
Redo Buffers                2396160 bytes
Database mounted.
SQL>
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only.