Category Archives: DB Generic

How to Recover Loss of DATA – (Without a Backup!)

DB Generic, , , , ,

How to Recover Loss of DATA (Without a Backup!) using Oracle Flash Back Query 

IT WILL WORK ONLY FOR DML DATA LOSS

Issue Description:

No Flashback Enable
No Recyclebin
No Restore Point
No Backup

Developer accidentally deleted all rows from table and then committed too. He want DBA to recover Loss of DATA

Solution:

It depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 1 hour, then users can recover from their mistakes made since last 1 hour only.

Lets do Practical……………..

Table of Contents
___________________________________________________________________________________________________

1. No Flashback Enable
2. No Recyclebin
3. No Restore Point and No Backup
4. Archive Log Enabled
5. Simulation
6. Action Plan
___________________________________________________________________________________________________


1. No Flashback

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DELL      READ WRITE

SQL>

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO  <-----------------------------

SQL>


2. No Recyclebin

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF <------
SQL>


3. No Restore Point

SQL> select * from v$restore_point;

no rows selected <------

SQL>


4. Archive Log On

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG <------

SQL>


5. Simulation

SQL> conn test/test;
Connected.
SQL>
SQL> CREATE TABLE SUGI (NAME    VARCHAR2(20),ROLE VARCHAR2(20));

Table created.

SQL> insert into sugi values ('SUGI','DBA');

1 row created.

SQL> insert into sugi values ('TEJU','DBA');

1 row created.

SQL> insert into sugi values ('RAJ','DBA');

1 row created.

SQL> COMMIT;

Commit complete. <--------

SQL> select * from sugi;

NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA

SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

SQL> conn test/test;
Connected.
SQL> select * from sugi;

NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
--------------------
20-SEP-2018 08:39:43  <----------------- at this time Data available

6. Delete the rows

SQL> delete from sugi;  <-------

3 rows deleted.

SQL> commit;  <------

Commit complete.

SQL> select * from sugi;

no rows selected <-----

SQL>


Action Plan:

SQL> conn / as sysdba
Connected.

SQL> FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS');
FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS')
                     *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> ALTER TABLE TEST.SUGI ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.  <----

SQL> SELECT * FROM TEST.SUGI;

NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA

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
Email: br8dba@gmail.com

Enable/Disable JOBS during Maintenance

DB Generic, , , , , , ,

Enable/Disable JOBS during Maintenance

Contents
___________________________________________________________________________________________________

Disable Before Maintenance

1. Capture values
2. Disable ALL DBMS_JOBS
3. Disable ALL DBMS_SCHEDULER JOBS

Enable After Maintenance

4. Enable ALL DBMS_JOBS
5. Enable ALL DBMS_SCHEDULER JOBS
___________________________________________________________________________________________________

Disable Before Maintenance


1. Capture values

-- For DBMS_JOBS

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4000  <-----

SYS @ CDB2 >

-- FOR DBMS_SCHEDULER JOBS

SYS @ CDB2 > COL ATTRIBUTE_NAME FOR A40
SYS @ CDB2 > COL VALUE FOR A60
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                           VALUE
---------------------------------------- ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                              30
DEFAULT_TIMEZONE                         Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION                  NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT                      6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                       0
CURRENT_OPEN_WINDOW

11 rows selected. <----

SYS @ CDB2 >


2. Disable ALL DBMS_JOBS

To disable jobs created by dbms_jobs set job_queue_processes to zero.

SYS @ CDB2 > alter system set job_queue_processes=0 scope=both sid='*';

System altered.

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0  <-------

SYS @ CDB2 >


3. Disable ALL DBMS_SCHEDULER JOBS

SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

PL/SQL procedure successfully completed.

SYS @ CDB2 > 
SYS @ CDB2 > col ATTRIBUTE_NAME for a30
SYS @ CDB2 > col VALUE for a60
SYS @ CDB2 > set lines 180
SYS @ CDB2 > set pages 999
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                 VALUE
------------------------------ ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                    30
DEFAULT_TIMEZONE               Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION        NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT            6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT             0
CURRENT_OPEN_WINDOW
SCHEDULER_DISABLED             TRUE  <---------

12 rows selected.  <-----

SYS @ CDB2 >

 

Enable After Maintenance


4. Enable ALL DBMS_JOBS

SYS @ CDB2 > alter system set job_queue_processes=4000 scope=both sid='*';

System altered.

SYS @ CDB2 > show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     4000 <-------

SYS @ CDB2 >


5. Enable ALL DBMS_SCHEDULER JOBS

SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

PL/SQL procedure successfully completed.

SYS @ CDB2 > 
SYS @ CDB2 > col ATTRIBUTE_NAME for a30
SYS @ CDB2 > col VALUE for a60
SYS @ CDB2 > set lines 180
SYS @ CDB2 > set pages 999
SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                 VALUE
------------------------------ ------------------------------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                    30
DEFAULT_TIMEZONE               Europe/Vienna
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION        NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT            6F4E2CF66A235446E055000000000001::19440::SCHED::0001
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT             0
CURRENT_OPEN_WINDOW

11 rows selected.  <------

SYS @ CDB2 >

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
Email: br8dba@gmail.com

How to change SQL  prompt to show connected user and database name

DB Generic,

How to change SQL  prompt to show connected user and database name

Ans: Connect as oracle user and add the following line at the end of th glogin.sql script which is located under $ORACLE_HOME/sqlplus/admin

[oracle@rac1 ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/sqlplus/admin
[oracle@rac1 admin]$ ls -ltr
total 20
-rw-r--r--. 1 oracle dba  813 Mar  7  2006 plustrce.sql
-rw-r--r--. 1 oracle dba  226 Apr 29  2015 libsqlplus.def
-rw-r--r--. 1 oracle dba 2683 Jan  6  2016 pupbld.sql
drwxr-xr-x. 2 oracle dba 4096 Jan 13  2017 help
-rw-r--r--. 1 oracle dba  422 Aug 29 09:27 glogin.sql
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
[oracle@rac1 admin]$ 

-- Connect as oracle user and add the following line at the end of the glogin.sql script.
-- Added by Rajasekhar Amudala
set sqlprompt "_user '@' _connect_identifier > "

[oracle@rac1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
-- Added by Rajasekhar Amudala
set sqlprompt "_user '@' _connect_identifier > "

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 29 09:28:14 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS @ CDB2 > select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB2      READ WRITE

SYS @ CDB2 > show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB4                           READ WRITE NO
SYS @ CDB2 >
SYS @ CDB2 > connect sys@PDB1 AS SYSDBA
Enter password:
Connected.
SYS @ PDB1 >
SYS @ PDB1 > show con_name

CON_NAME
------------------------------
PDB1
SYS @ PDB1 >

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
Email: br8dba@gmail.com

Error TNS-12543

DB Generic,

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 ~]#

THIS IS TEST MACHINE, HENCE I STOPPED firewall.

if you don't to stop then 
You can you add entry into firewall table to allow connection

[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

DB Generic

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

DB Generic,

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

DB Generic, , , ,

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

DB Generic, ,

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

DB Generic, ,

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

DB Generic,

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

DB Generic,

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

DB Generic

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

DB Generic

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.