Oracle : Common database connection errors (For junior DBAs)

## ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ##
SQL> conn test/test@tdb1
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The connection request reaches listener but connection fails possibly due to below reasons.

Reason :

1) Service name used in connection description in tnsnames.ora or easy connect string does not exits.
2) Service is not running in database.
3) Service is not registered in listener.

## ORA-12545: Connect failed because target host or object does not exist ##

SQL> conn test/test@tdb1
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Connection request does not reach the listener as host name where listener running is wrong.

Reason :

1) Host is wrong in tnsnames.ora file.
2) Host is not reachable from the machine that invokes client utility to connect to database.

## ORA-12541: TNS:no listener ##

SQL> conn test/test@tdb1
ERROR:
ORA-12541: TNS:no listener

Reason :

1) The listener port specified in tnsnames.ora is wrong.
2) The listener is not running on the host specified in tnsnames.ora

## ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit ##

SQL> conn test/test@tdb1
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


Warning: You are no longer connected to ORACLE.

Reason :

1) The session limit specified in associated user profile exceeds SESSIONS_PER_USER.

Check the profile associated with the user connecting to database.

SQL> select username,profile from dba_users where username='TEST';

USERNAME                       PROFILE
------------------------------ ----------
TEST                           DEFAULT

SQL>

Check the profile limit SESSIONS_PER_USER

SQL> select profile,resource_name,limit from dba_profiles where resource_name='SESSIONS_PER_USER' and profile='DEFAULT';

PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ----------
DEFAULT    SESSIONS_PER_USER                2

## ORA-00020: maximum number of processes (33) exceeded ##

SQL> conn test/test
ERROR:
ORA-00020: maximum number of processes (33) exceeded


Reason :

1) The new process could not spawned as it reached the limit specified in processes parameter.



SQL> select * from v$resource_limit where resource_name='processes';

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       32              33         33         33

SQL>

## ORA-01017: invalid username/password; logon denied ##

SQL> conn test/test1@tdb1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

The connection request reaches listener but it is rejected by database due to below reason.

Reason :

1) Either database username or password used for connection to database is not correct.

@@@@ - @@@@

Oracle 12c New Features - Change in DDL log location

Unlike Oracle 11g the DDL statement is not logged to alert log file in Oracle 12c. The ddl statement is logged into another directory log/ddl in diagnostic_dest location.

 Enable DDL logging in Oracle 11g 

In Oracle 11g the ENABLE_DDL_LOGGING parameter logs the DDL statements into the database instance alert log.

Let us test it.

Set the ENABLE_DDL_LOGGING parameter to TRUE.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
enable_ddl_logging                   boolean     FALSE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE SCOPE=MEMORY;

System altered.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
enable_ddl_logging                   boolean     TRUE

Let us execute some DDL statements here

SQL>
SQL> CONN test@ddb
Enter password:
Connected.
SQL> CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30));

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ENAME;

Table altered.

SQL>

The alert log shows the record of DDL statments executed in database.

-- Snippet of alert log --
...
....
..
Mon Jan 12 19:23:50 2015
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;
Mon Jan 12 19:24:54 2015
CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30))
Mon Jan 12 19:25:13 2015
ALTER TABLE T1 DROP COLUMN ENAME
....
..

---

 Enable DDL logging in Oracle 12c 

There is a change in Oracle 12c how DDL statement is recorded when ddl logging is enabled.The DDL statement is recorded in DDL log (not in alert log)


SQL> show parameter enable_ddl_logging

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean FALSE

SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

System altered.

SQL> show parameter enable_ddl_logging

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean TRUE

SQL> DROP TABLE T1 PURGE;

Table dropped.

SQL> CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ename;

Table altered.

SQL>

The DDL statement in recorded in DIAG_DEST/rdbms/<db name>/<instance_name>/ddl

[oracle@norac ddl]$ pwd
/u01/app/oracle/diag/rdbms/db12c/DB12C/log/ddl
[oracle@norac ddl]$ cat log.xml
<msg time='2015-01-12T23:41:22.286+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'
 version='1'>
 <txt>DROP TABLE T1 PURGE
 </txt>
</msg>
<msg time='2015-01-12T23:43:11.838+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users
 </txt>
</msg>
<msg time='2015-01-12T23:43:45.599+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>ALTER TABLE T1 DROP COLUMN ename
 </txt>
</msg>
[oracle@norac ddl]$

- 0 -

Oracle 12c New Features - Invisible Columns

Oracle 12c allows us to make a column invisible.
The invisible column is not allowed for the below table types

External tables
Cluster tables
Temporary tables

Let us check how to make a column invisible.

SQL> CREATE TABLE t1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                 NUMBER
 ENAME         VARCHAR2(30)

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
--------------------   ----------         --------------------
T1                                         1           EID
T1                                         2           ENAME

SQL> 

SQL> INSERT INTO t1 VALUES(100,'Test Employee 1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1

-- > Set the column invisible

SQL> ALTER TABLE t1 MODIFY (ename INVISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                     NUMBER

-- > Setting the sqlplus to show invisible column in DESCRIBE output (set colinvisible off | on)

SQL> set colinvisible on 
SQL> DESC t1
 Name      Null?                   Type
 -------------------------------- -------- ----------------------
 EID                                    NUMBER
 ENAME (INVISIBLE)      VARCHAR2(30)


-- > The column_id in user_tab_columns does not show any value after making column invisible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME   COLUMN_ID COLUMN_NAME
------------------------------ ---------- ------------------------------
T1                                            1   EID
T1                                                 ENAME

-- > The ename column is not visible to the "SELECT *" query.

SQL> SELECT * FROM t1;

       EID
----------
       100

-- > Still the ename column shows value by explicitly specifying the ename column in query 

SQL> select ename from t1;

ENAME
------------------------------
Test Employee 1
Test Employee 2

-- > The below error shows that ename column is not avilable to insert statement


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');
INSERT INTO t1 VALUES(100,'Test Employee 2')
            *
ERROR at line 1:
ORA-00913: too many values

-- > Let us make the column visible as below

SQL> ALTER TABLE t1 MODIFY (ename VISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                    NUMBER
 ENAME            VARCHAR2(30)


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1
       200 Test Employee 2

SQL> 

-- > The column_id in user_tab_columns shows value as 2 after making column visible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
-------------------- ---------- --------------------
T1                                       1           EID
T1                                       2           ENAME

Oracle 12c New Features - Renaming and Relocating Online Data Files

Oracle 12c new feature allows us to rename,relocate,copy datafile online with asm or non-asm type of storage.

The below test shows that datafile can be renamed online without taking the datafile offline.

SQL> SELECT name,open_mode FROM v$database;

NAME  OPEN_MODE
--------- --------------------
DB12C  READ WRITE

SQL> SELECT file_id,file_name,tablespace_name,status FROM dba_data_files WHERE tablespace_name='USERS';

   FILE_ID FILE_NAME       TABLESPACE_NAME    STATUS
---------- ----------------------------------- -------------------- ---------
6 +DATA/DB12C/users01.dbf       USERS    AVAILABLE

SQL> ALTER DATABASE MOVE DATAFILE '+DATA/DB12C/users01.dbf' TO '+DATA/DB12C/users02.dbf';

Database altered.

SQL> SELECT file_id,file_name,tablespace_name,status FROM dba_data_files WHERE tablespace_name='USERS';

   FILE_ID FILE_NAME       TABLESPACE_NAME    STATUS
---------- ----------------------------------- -------------------- ---------
6 +DATA/DB12C/users02.dbf       USERS    AVAILABLE

Oracle : Data Security - Hide data from unauthorized users using dbms_rls

Hide Data in Oracle using DBMS_RLS

The DBMS_RLS package is helpful in masking data in oracle.

Let’s see the below steps to test a simple data masking functionality using dbms_rls.

Setting up access policy

1) Create two users. The data_owner user is the owner of the table. The data_user is used for reading the tables from data_owner user.

conn / as sysdba

CREATE USER data_owner IDENTIFIED BY data_owner;

GRANT CONNECT,RESOURCE TO data_owner;

GRANT EXECUTE ON dbms_rls TO data_owner; 

CREATE USER data_user IDENTIFIED BY data_user;

GRANT CONNECT,RESOURCE TO data_user;

2) Create a table in the data owner.

CONN data_owner/data_owner@tdb

CREATE TABLE t_employee (empid NUMBER PRIMARY KEY, ename VARCHAR2(30),location VARCHAR2(30),salary NUMBER);

INSERT INTO t_employee VALUES (1001,'Alex','NY','2000');
INSERT INTO t_employee VALUES (1002,'Scott','NY','5000');
INSERT INTO t_employee VALUES (1003,'Tim','NJ','10000');
INSERT INTO t_employee VALUES (1004,'Ryan','NJ','2000');
INSERT INTO t_employee VALUES (1005,'Anderson','NJ','15000');

commit;

3) Grant select privilege to data_user

CONN data_owner/data_owner@tdb

GRANT SELECT ON t_employee to data_user;

4) Create a policy function for column level masking. The other users except data_owner shouldn't see the SALARY column.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_colmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= '1=2';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

5) Add the policy for column masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_COLMASK',
   sec_relevant_cols=>'SALARY',
   sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);

END;
/

6) The data_owner is able to see all the data

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

7) The salary column is not visible to data_user due to column masking on SALARY column.

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY              
      1002 Scott           NY              
      1003 Tim             NJ             
      1004 Ryan            NJ            
      1005 Anderson        NJ

8) Create a policy function for row masking as well.

The users except data_owner should see only rows of NY location. Not the rows of other locations.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_rowmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= 'LOCATION=''NY''';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

9) Add the policy function for row level masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_ROWMASK',
   sec_relevant_cols=>'SALARY');

END;
/

10) Now the policy has row level masking and column level masking

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Exempt Access policy

SYS user is always exempted from access policy. Other users can be exempted from access policy by using exempt access policy privilege.

conn / as sysdba

grant exempt access policy to data_user;

CONN data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

User data_user is able to see all the data because of exemption of access policy

conn / as sysdba

revoke exempt access policy from data_user;

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Viewing policy details

V$VPD_POLICY displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache.

Dropping policy

The policy can be dropped using DBMS_RLS.DROP_POLICY procedure.

BEGIN

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY');

END;
/

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY');

END;
/

Useful Oracle Links

Oracle In-Memory Database : http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

ORACLE DATABASE 12 C IN-MEMORY OPTION : http://www.oracle.com/us/solutions/sap/nl23-db12c-imo-en-2209396.pdf

- 0 -

Oracle Technology Articles : http://www.oracle.com/technetwork/articles/database/index.html

Oracle By Example : http://www.oracle.com/technetwork/tutorials/index.html

- 0 -

Parallel Execution Fundamentals : http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf

- 0 -

Exadata 3D Overview : https://blogs.oracle.com/databaseinsider/entry/oracle_exadata_database_machin

- 0 -

Oracle : Verify index access without creating a real index

Index creation is a resource consuming operation. Creating index on a very big table is waste of resource and time, if it finally appears to be not used by query.

There is a simple way to understand if an index can be useful before creating the real index.

[ SYS @ DDB ] create table test.t_obj as select object_id,object_name from dba_objects;

Table created.

[ SYS @ ddb ] exec dbms_stats.gather_table_stats('TEST','T_OBJ');

PL/SQL procedure successfully completed.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id) nosegment;

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

no rows selected

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=true;

Session altered.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.

[ SYS @ ddb ]

But the execution plan above shows the index can be used if we create it. The nosgment index is not a real index.

Now as we undersrtood index can be used, we can create a real index using the below statement without "nosegment" key word.


[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=false;

Session altered.

[ SYS @ ddb ] drop index test.t_idx;

Index dropped.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id);

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

OWNER                          TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           T_OBJ                          T_IDX

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.


[ SYS @ ddb ]