Database connections and controlling them - DB Profiles

 We often see developers coming to us and requesting that their session is stuck in the database and needs to be killed as they don't see them in their front end or they get involved in locking situation (in different context here w.r.to orphaned connections). While this may be general in non production environments, there are poorly designed production systems as well where the connections don't get closed properly or gracefully and left inside the database when the front end gets disconnected from the database. 

In this post, we will see how we can control the connections or sessions that gets connected to the database.

We will see the below method with some details, of course. 

• Database Profile

Database Profiles: 

The first and easy method is to create profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. 

We have to set the session related parameter idle_time or connect_time under that specific profile as limit, post the specified duration the session will be killed automatically by Oracle. 

Let's take an example by setting 2 minutes for the idle_time parameter. 

SQL> alter profile test limit IDLE_TIME 2;

Profile TEST altered.

SQL> alter user test profile test;

User TEST altered.

SQL> select * from dba_profiles where profile='TEST'
  2  and resource_name='IDLE_TIME';

   PROFILE    RESOURCE_NAME    RESOURCE_TYPE    LIMIT    COMMON    INHERITED    IMPLICIT
__________ ________________ ________________ ________ _________ ____________ ___________
TEST       IDLE_TIME        KERNEL           2        NO        NO           NO

SQL> sho user
USER is "SYSTEM"
SQL>

Now, we will connect to the DB using user TEST and leave the session idle for > 2 minutes and run a query again. Let's see what happens.

C:\Users\MJ>sql test/test@orcl

SQLcl: Release 21.1 Production on Sun Jul 04 20:56:23 2021

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

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


SQL> show user
USER is "TEST"
SQL> -- sitting idle for more than 2 minutes
SQL> select 1 from dual;

Error starting at line : 1 in command -
select 1 from dual
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-02396: exceeded maximum idle time, please connect again
02396. 00000 -  "exceeded maximum idle time, please connect again"
*Cause:    as stated
*Action:
SQL>

In the mean time we can see the below entries in the alert log

2021-07-04T20:59:44.952961+05:30
KILL SESSION for sid=(20, 8481):
  Reason = profile limit idle_time
  Mode = KILL SOFT -/-/-
  Requestor = PMON (orapid = 2, ospid = 2839, inst = 1)
  Owner = Process: USER (orapid = 44, ospid = 7137)
  Result = ORA-0

The existing connection will get the SQL Error: ORA-02396 only when the session tries to run the query from the session which was already killed automatically. 

Note, the session might take a minute or 2 to get killed post the idle_time specified in the profile. 

If you see the error like above, then we can conclude it's due to the profile settings. 

Since, most applications are designed very well to gracefully end the connection to the backend, we don't have to specify the connect_time limit, which will specify the total elapsed time for the session be it active or inactive unless you need aggressive control over the connections. If the parameter is set, oracle behaves the same killing session similar to idle_time parameter but the session will be terminated no matter you are idle or active. You can check this behavior as an exercise.. 

We will discuss about other option in next post here

Reference: 

CREATE PROFILE (oracle.com)

Courtesy: John Wick Reload GIF - JohnWick Reload OhBoyHereIGoKillingAgain - Discover & Share GIFs (tenor.com)

Happy Killing...!!!

IMPDP, ORA-39083, ORA-02304 and the fix

 I was trying some PoC today with an impdp job involving sample schemas that I had in my lab database. The objective I was testing was to check on LONG column support for impdp via a network link (more of network link import in this link). 

I am aware LONG column is not supported in Oracle version 12.1 if we use network_link and is already documented in this official link but the same is mentioned as supported and as a new feature in the Oracle 12.2 official link here.

Well, testing makes us to be confident on what we are going to present to our clients, isn't it? :)

So in the due process of testing this, I just stumbled up on this error below. 

DB env: Oracle 12.2

Issue:

* Output truncated

[oracle@linux75-2 ~]$ impdp system REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=users:tt1 schemas=pm

Import: Release 12.2.0.1.0 - Production on Sun Jun 20 20:30:26 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=users:tt1 schemas=pm
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.81 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NWIMP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:"NWIMP"."ADHEADER_TYP" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "NWIMP"."ADHEADER_TYP"   OID '82A4AF6A4CCE656DE034080020E0EE3D'

  AS OBJECT
    ( header_name        VARCHAR2(256)
    , creation_date      DATE
    , header_text        VARCHAR2(1024)
    , logo               BLOB
    );

ORA-39083: Object type TYPE:"NWIMP"."TEXTDOC_TYP" failed to create with error:
ORA-02304: invalid object identifier literal
 ...
 ...
 ...

The IMPDP operation failed during the import of TYPE_SPEC and says invalid object identifier literal. 

This is a scenario which would occur when we try to duplicate a schema or duplicate objects and their dependent objects within the same database. What I'm trying here is importing a sample schema from source database via network_link which I already have in the target database into another schema and so in short, this also is sort of duplicating the schema.

When the types are exported, we also export the object_identifier (OID) of the types. Within the current architecture, the object-identifier needs to be unique in the database and the OID of the types already exist in target, the types can't be created and hence the error.

Solution: 

We can overcome this by precreating the type and tables in the target database under the schema nwimp and then import the schema with table_exists_action parameter set to append. 

or

Starting Oracle version 10.2, we have a parameter TRANSFORM which is used to alter object creation DDL for the objects being imported. So we will now use this parameter to tell Oracle not to perform OID checking when looking for an existing matching type on the target database and also to assign a new OID to the object. 

TRANSFORM=OID:N

* Output truncated

[oracle@linux75-2 ~]$ impdp system transform=oid:n REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=example:tt1 schemas=pm

Import: Release 12.2.0.1.0 - Production on Sun Jun 20 20:37:45 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** transform=oid:n REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=example:tt1 schemas=pm
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.81 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NWIMP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"NWIMP"."ADHEADER_TYP" already exists

ORA-31684: Object type TYPE:"NWIMP"."TEXTDOC_TYP" already exists

ORA-31684: Object type TYPE:"NWIMP"."TEXTDOC_TAB" already exists

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "NWIMP"."PRINT_MEDIA"                            4 rows
. . imported "NWIMP"."TEXTDOCS_NESTEDTAB"                    12 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
...
...
...
 

Now, we can see the import says the objects are already existing and just continued with the import of table object and data without issues. 

By the way, I'm still pursuing my testing on long columns. That's all for today... :)

References: 

Data Pump Import (oracle.com)

Oracle Data Pump

DataPump Import Of Object Types Fails With Errors ORA-39083/ORA-39082 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)

Happy importing...!!!