Database Resident Connection Pooling (DRCP) in Oracle

Database Resident Connection Pooling (DRCP) in Oracle

DRCP is introduced in 11g version of Oracle. It is used for sharing connection to achieve scalability in multi process and multi threaded environment.
DRCP Pools are like dedicated it’s used to shared the connection between multiple application process from different hosts.

Note: Database Resident Connection Pooling is consuming less memory as compared to other dedicated or Shared connection.

POOLED SERVER
Database server processes and sessions combination is known as a pooled server.

CONNECTION BROKER
A connection broker manages the pooled server in database instance. Client are connected and authenticated to the broker. The background process called is Connection Broker process(CMON).

Steps follow by Client for Connection
1. Client request for connection
2. Broker authenticated and pick the pooled server and hand-off client to that pooled server.
3. The client directly communicates with the pooled server for all its database activity.
4. The pooled server is handed back to the broker when the client releases it.

Configure the DRCP

1. Start or Enabled the Pool by connecting with sysdba
Following command start the broker and register with database listener. It must be started before client started request.

execute dbms_connection_pool.start_pool;

2. Check DRCP is started or configured:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS     MAXSIZE
------------------------------ ---------- ----------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE   20

SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS   MAXSIZE
------------------------------ -------- -------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE   20

3. Disable or Stop the DRCP

execute dbms_connection_pool.stop_pool();

4. For using the Pool you need to make some connection changes at tns entry level or application level
Add POOLED keyword in tns entry and application connection string

host1.oracle.com:1521/orcl:POOLED
OR
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host1.oracle.com)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)
(SERVER=POOLED)))

5.Configure and ALTER DRCP with package DBMS_CONNECTION_POOL.
This is step needed if you want to change the default setting before configuring the DRCP pool.
Following example already set with default value:

execute dbms_connection_pool.configure_pool(
pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
minsize => 4,
maxsize => 40,
incrsize => 2,
session_cached_cursors => 20,
inactivity_timeout => 300,
max_think_time => 600,
max_use_session => 500000,
max_lifetime_session => 86400);

execute dbms_connection_pool.alter_param(
pool_name => 'DRPC_POOL',
param_name => 'MAX_THINK_TIME',
param_value => '1200');


Parameter meaning as:
POOL_NAME: Name of the pool. Default is SYS_DEFAULT_CONNECTION_POOL
minsize: minimum number of pooled server in pool. (default 4)
maxsize: maximum number of pooled server in pool. (default 40)
incrsize: increased number of pooled server is not available if pool is not max limit( default 2)
session_cached_cursors: SESSION_CACHED_CURSORS for all connections in the pool(default 20)
inactivity_timeout: time to remain an idle server in the pool. If a server remains idle upto time limit, it is killed. (default 300 seconds)
max_think_time: Maximum time of inactivity the PHP script is allowed after connecting. (default 120 seconds)
max_use_session: Maximum number of times a server can be taken and released to the pool before it is flagged for restarting. (default 500000)
max_lifetime_session: Time to live for a pooled server before it is restarted. (default 86400 seconds)
num_cbrok: The number of connection brokers that are created to handle connection (default 1)
maxconn_cbrok: The maximum number of connections that each connection broker can handle.(default 40000)

6. Monitor the DRCP pooling from following views:

select connection_pool, status, maxsize from dba_cpool_info;

select num_requests, num_hits, num_misses, num_waits from v$cpool_stats;

select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

Advertisements

Force Full Database Caching Mode in Oracle

Force Full Database Caching Mode in Oracle

Force full database caching mode means that place full database is buffer cache. If you have sufficent space in buffer cache then you are able to use the feature in Oracle
This feature is present from Oracle Database 12c Release 1 (12.1.0.2). Caching the full database might increase the performance of the Queries.Database must have compatibility 12.0.o or higher.

Check the database is in full Caching Mode

SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

Yes - means database is in full caching mode

Steps to configure the Full DB CACHING

1. Check that you have enough space in buffer cache

SELECT NAME, BYTES FROM V$SGAINFO WHERE NAME='Buffer Cache Size';

SELECT COMPONENT, CURRENT_SIZE FROM V$SGA_DYNAMIC_COMPONENTS WHERE COMPONENT LIKE 'DEFAULT buffer cache';

Note: Estimate buffer cache size in case of AMM Configured
SGA_TARGET is USED then SGA_TARGET is X value then buffer cache estimate is 60% of X
MEMORY_TARGET is USED then MEMORY_TARGET is x then SGA is 60% of X and buffer cache is 60% of SGA.

Example:
1. SGA is Set.
SGA_TARGET is 100 GB then buffer cache estimate size is 60 GB.
2. MEMORY is Set.
MEMORY_TARGET is 100 GB then SGA is 60 GB and Buffer cache is 36 GB.

Enabling Force Full Database Caching Mode

1. Open database is in mount state
STARTUP MOUNT
2. Enable the force full database cache
ALTER DATABASE FORCE FULL DATABASE CACHING;
3. Open the database
ALTER DATABASE OPEN;

Disable the Force full database caching

1. Open database is in mount state
STARTUP MOUNT
2. Disable the force full database caching
ALTER DATABASE NO FORCE FULL DATABASE CACHING;
3. Open the database:
ALTER DATABASE OPEN;

Listener Oracle Net Services Tracing on the Client and Server

Listener Oracle Net Services Tracing on the Client and Server

Enable or Disable trace on the Listener or Server level
Two ways for Server trace choose as you wish:
1. You can enable or disable trace on Server from listener.

--- Check the Trace Level
LSNRCTL> show trc_level
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "trc_level" set to off
The command completed successfully

-- Enable the Trace
LSNRCTL> set trc_level 16
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "trc_level" set to support
The command completed successfully

-- Check the trace file created on listener log file location
LSNRCTL> show trc_File
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "trc_file" set to ora_9132_5280.trc
The command completed successfully

-- Disable the trace
LSNRCTL> set trc_level 0
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
LISTENER parameter "trc_level" set to off
The command completed successfully

2. Enable trace of Server by editing the SQLNET.ORA file of Server directory(listener is configured)
Go to the Server directory %ORACLE_HOME%\NETWORK\ADMIN folder and open SQLNET.ORA file and place following entries:

TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = client
TRACE_DIRECTORY_SERVER = E:\oracle12c\trace
TRACE_TIMESTAMP_SERVER = ON
TRACE_UNIQUE_SERVER = ON
TRACE_FILELEN_SERVER=10000
TRACE_FILENO_SERVER=10
DIAG_ADR_ENABLED= OFF

Enable or disable the Client SQLNET file trace
Go to the SQLNET file location of Oracle Client that is %ORACLE_CLIENT%\NETWORK\ADMIN folder and open SQLNET.ORA file and place all the given parameter in the SQLNET.ORA file.

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = E:\oracle12c\trace
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
TRACE_FILELEN_CLIENT=10000
TRACE_FILENO_CLIENT=10
DIAG_ADR_ENABLED= OFF

Parameter meaning

TRACE_LEVEL_[ CLIENT | SERVER | ] = Define the trace level 0 (no trace), 4 (user trace), 6 (admin trace), 16 (support all)
TRACE_FILE_[ CLIENT | SERVER | ] = Define the file name of trace file Default Server (srv_pid.trc) Client (sqlnet.trc) and listener (listener.trc)
TRACE_DIRECTORY_[ CLIENT | SERVER | ] = Define the directory location where trace generated. default is %ORACLE_HOME%\network\trace
TRACE_UNIQUE_[ CLIENT | SERVER | ] = Define ON append the same file and OFF doesnot append in same file.
TRACE_TIMESTAMP_[ CLIENT | SERVER | ] = Define ON place timestamp in each entry and OFF no timestamp
TRACE_FILELEN_[ CLIENT | SERVER | ] = Define the Size of trace file in bytes.
TRACE_FILENO_[ CLIENT | SERVER | ] = Define that file written in cycle fashion, rotate itself when last is filled write to first one.
LOG_FILE_[ CLIENT | SERVER | ] = Define the file name of log file.
LOG_DIRECTORY_[ CLIENT | SERVER | ] = Define the directory for log.

Disable the OS authentication in Listener Oracle

Disable the OS authentication in Listener Oracle

Oracle listener is securing listener by using Local OS authentication.
Local OS authentication means you are working on user account in machine which the listener is running and configured.
(means user is the owner of Oracle while installation)

Check listener Status to See OS authentication is on or off

lsnrctl status

C:\oracle>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 19-APR-2018 02:10:32
Copyright (c) 1991, 2017, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.85)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 18-APR-2018 13:05:31
Uptime 0 days 13 hr. 5 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF

Disable Local OS Authentication

Go to the Location %ORACLE_HOME%\network\admin and add a new parameter in Listener.ora File

local_os_authentication_=off

In my case, i am having default listerner name, so used LISTENER
local_os_authentication_LISTENER=off

Manage Sequence in Oracle

Managing Sequence in Oracle

Sequence is the oracle database object used for generating the unique sequence number.
It is may or may not be used as primary key.

Create Sequence

CREATE SEQUENCE emp_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

Note:
Start with: starting value of the Sequence it is anything
CACHE clause: preallocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster.
Note: In case of CACHE enabled, the database might skip sequence numbers.
For example, when an instance abnormally shuts down sequence numbers that have been cached are lost.

Alter Sequence
Alter a sequence to change any of the parameters that define it while creating.

ALTER SEQUENCE emp_seq
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;

Use of Sequence with NEXTVAL or CURRVAL
CURRVAL: current sequence value of your session
NEXTVAL: generate and use a sequence number
Note: Each time you use nextval it always show the next value as defined by INCREENT Clause in Sequence

--Check the current value of SEQUENCE
select emp_seq.CURRVAL from dual;

--Insert current value of Sequence
INSERT INTO Employee (id, empid, name)
VALUES (emp_seq.CURRVAL, 20321, 'RAM');

-- Generate next value from Sequence
SELECT emp_seq.NEXTVAL FROM dual;

-- Insert next value in Sequence
INSERT INTO Employee (id, empid, name)
VALUES (emp_seq.NEXTVAL, 10324, 'ANIL');

Check the Sequence Details

select * from DBA_SEQUENCES;

Making a Sequence Scalable
In Sequence Scalable number 6 digit is added in front of the Sequence number.
scalable sequence number = 6 digit scalable sequence offset number || normal sequence number

6 digit scalable sequence offset number = 3 digit instance offset number || 3 digit session offset number.

SYNTAX:

CREATE | ALTER SEQUENCE sequence_name
...
SCALE [EXTEND | NOEXTEND] | NOSCALE

In this you have two options:
EXTENDED:
In Extended it will choose the sequence number as 6 digit of instance offset and y digit of MAXVALUE digit.
If MAXVALUE defined in sequence creating time is 100 then 3 digit is choose as Y digit.

6 digit scalable sequence offset number || 001
6 digit scalable sequence offset number || 002
6 digit scalable sequence offset number || 003

6 digit scalable sequence offset number || 100

NOEXTENDED:
In No Extended, the number of scalable sequence digits cannot exceed the number of digits specified in the MAXVALUE clause.
Example, it MAXVALUE is 1000000 7 digit then Y digit has only 1 digit left because 6 digit is already taken by instance offset

6 digit scalable sequence offset number || 1
6 digit scalable sequence offset number || 2
6 digit scalable sequence offset number || 3

6 digit scalable sequence offset number || 9

When you tried to add 10 at last then it will generate the following error:

ORA-64603: NEXTVAL cannot be instantiated for SQ. Widen the sequence by 1 digits
or alter sequence with SCALE EXTEND.

Dropping Sequences

DROP SEQUENCE emp_seq;

External Tables in Oracle

External Tables in Oracle

External tables are the table that does not reside in the Oracle Database.
They can be in any format and read by access driver. When you create external table its metadata is exists in Oracle database.

You can select, join on external table but no DML operation such as insert, update & delete is possible.No index can be created on External Table.

External tables is used for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing. It is a way to use select statement on outside data without load into the database.

An Access driver is method used to read external data. Default access driver is ORACLE_LOADER,its allows the reading of data from external files(like SQL loader). Another is ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table.

In Oracle Database 12c Release 2 (12.2), new access drivers ORACLE_HIVE and ORACLE_HDFS are available.
ORACLE_HIVE access driver can extract data stored in Apache Hive.
ORACLE_HDFS access driver can extract data stored in a Hadoop Distributed File System (HDFS).

In Oracle 18c, Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as persistent object in the data dictionary. It is like temporary.

Creating an External Table

1. For external table you need comma separated CSV files

emp_detail.dat contains the following data:

1000,Ram,Kapoor,MRG,1000,15-MAY-2008
1001,Mark,Marshal,ITO,1000,12-MAY-2009
1002,Pooja,Semi,ITO,1000,17-MAY-2010
1003,Sham,Marsha,Developer,1000,17-MAY-2011

2. Create directories and assign permission to them in Oracle.
Note: Make sure the following directories exists in Operating system and has permission

CREATE OR REPLACE DIRECTORY ext_dat_dir AS 'D:\employeefiles\data';
CREATE OR REPLACE DIRECTORY ext_log_dir AS 'D:\employeefiles\log';
CREATE OR REPLACE DIRECTORY ext_bad_dir AS 'D:\employeesfiles\bad';
GRANT READ,WRITE ON DIRECTORY ext_dat_dir TO hr;
GRANT READ,WRITE ON DIRECTORY ext_log_dir TO hr;
GRANT READ,WRITE ON DIRECTORY ext_bad_dir TO hr;

3. Create an external table in hr.schema and load data

-- create the external table
CREATE TABLE ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile ext_bad_dir:'empxt%a_%p.bad'
logfile ext_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy"
)
)
LOCATION ('emp_detail.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;

4. Check the external table with select commands:

SELECT * FROM ext_employees;

Parameters:
TYPE : ORACLE_LOADER is default or ORACLE_DATAPUMP
REJECT LIMIT clause specifies no limit on the number of errors. if it specified 10 then allow up to 10 rejections.
PARALLEL clause enables parallel query on the data sources.

Modified the parameter used to create external table
Alter Reject limit
ALTER TABLE ext_employees REJECT LIMIT 100;
Alter default directory in external table
ALTER TABLE ext_employees DEFAULT DIRECTORY dat2_dir;
Alter access parameter
ALTER TABLE ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');
Alter location of files
ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');

Override the parameter during Select statement on External Table

SELECT * FROM sales_external EXTERNAL MODIFY (LOCATION ('sales_9.csv')
REJECT LIMIT UNLIMITED);

Inline External Tables
Inline external tables is using the runtime definition of an external table as part of a SQL
statement, without creating the external table as in the data dictionary.
Without creating extrenal table, use select statement with define all other parameters.

SELECT * FROM EXTERNAL (
( id number not null,
time_id DATE NOT NULL,
prod_id INTEGER NOT NULL,
quantity_sold NUMBER(10,2),
amount_sold NUMBER(10,2))
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|')
LOCATION ('sales_2008.csv')
REJECT LIMIT UNLIMITED) sales_data;

Note: need to create data_dir1 directory