Unique Columns For V$Sql

Published on: 2022-11-11 by Mustafa Comment

Hi,

Until yesterday I was saying SQL_ID and CHILD_NUMBER is enough to get a sql child cursor from V$SQL and today I know I was wrong.

Probably many times you needed the sql statement that a session is running. To do that you query V$session and find the session information and then use SQL_ID column to reach out V$Sql. if you write a query like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

--where username = :some_user -- you filter

;

you can write any filter you want here but you will immediately realized that some session rows are multiplied. Why? Because you didn’t use “CHILD_NUMBER” while reaching v$sql. V$SQL is not main data source for unique sql statements in your database. V$SQLAREA is. So instead of v$sql, you can use v$sqlarea in this query but if you need specific information about the exact sql cursor that session is currently running then you must use v$sql.

Note: I will not go deep Parent-Child cursors here. basically, every sql statements has child cursors. they all same sql but for some reason they have different properties and your session is running a “child cursor”.

so your query should be like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
                         ses.sql_child_number = sql.child_number
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number

--where username = :some_user -- you filter

;

and now you reached to exact correct sql child cursor that is running for that session. right? well, I did know so but I find out, it is not! On one of my clients system, I discovered that this query returns 2 rows from v$sql which means for same SQL_ID and CHILD_NUMBER there are 2 rows in v$sql. I couldn’t find anything on docs or google (maybe couldn’t search enough) and started to dig about this sql statements.

here is the necessary information about this sql:

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID
from v$sql
where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS          CHILD_ADDRESS    HASH_VALUE SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I
---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -
00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N
00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID

from v$sql

where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS CHILD_ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I

---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -

00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

almost everything is same but ADDRESS and CHILD_ADDRESS. CHILD_ADDRESS is the memory address that this cursor resides on so it must be different obviously. Only thing that create this difference is ADDRESS column. ADDRESS column in V$sql is the address of the PARENT cursor which is in V$SQLAREA view. So let’s check this view:

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value
From V$sqlarea
Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
---------------- ---------- ------------- --------------- --------------------
00000001AAD5C000 2053771386 6r317atx6ny3u      3635437843             11086287

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value

From V$sqlarea

Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS HASH_VALUE SQL_ID PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE

---------------- ---------- ------------- --------------- --------------------

00000001AAD5C000 2053771386 6r317atx6ny3u 3635437843 11086287

there is only one of them exists. So some of those child cursors in v$sql are not (I am not sure the term) “valid”. they might be used before (and they were in my example) but now they are remaining of a past sql. So which child cursor in v$sql is the sql statements that session is currently running? Now we need to use ADDRESS column too. SQL_ADDRESS column in v$session is the parent address of the sql statement. So, I should add it into my queries too:

select * 
from v$session ses
       join v$sql sql on ses.sql_id           = sql.sql_id and
                         ses.sql_child_number = sql.child_number and
                         ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

-- OR for the parent cursor only

select /*+ ordered */ * 
from v$session ses
       join v$sqlarea sql on ses.sql_id           = sql.sql_id and
                             ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

-- OR for the parent cursor only

select /*+ ordered */ *

from v$session ses

join v$sqlarea sql on ses.sql_id = sql.sql_id and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

thanks for reading. comments are welcome. wish you healthy happy days.

How to Check Empty Space in a Securefile LOB

Published on: 2022-09-272022-10-12 by Mustafa Comment

Hi,

PS: please check last sql statement in this post for shorter and faster solution. first sql is for showing all information about the lob.

Yesterday, while checking segment space usage data in a client’s database and some of the LOB segments were absurdly big. So, I want to check what is in it and how much free space I can gain.

To do that, I cannot use just DBMS_LOB.GetLength function because for CLOB data type, it takes much more space than original data because of charsets (especially if you use AL32UTF8 charset) so I just want to check currently empty space in lob segment. if you need that kind of data then, DBMS_SPACE is the solution. DBMS_SPACE has a SPACE_USAGE procedure which checks a segment and return space information but starting 12c, SECUREFILE is the default lob type and for securefile you cannot use same space_usage procedure with the one you use for tables.

space_usage has overloaded versions and second and third overloaded version (order in dbms_space package from top to bottom) is for SECUREFILE lob segments.

here is a sample script I wrote to check currently empty space in securefile lobs:

Caution: I check all LOBS in the database so, you might not want to do that, just modify sql and add necessary filters.

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;
    end;
    get_info as (
      select f_lob_space_info(owner, segment_name, 
                              decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type), 
                              partition_name) as lob_info,
             segment_type, owner, segment_name, partition_name, bytes
      from   dba_segments  
      where segment_subtype='SECUREFILE'
        and segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    ),
    parse_info as (
      select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,
             to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024          as used_size_mb,
             to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024       as expired_size_mb,
             g.owner, l.table_name, l.column_name, g.segment_name lob_name
      from get_info g
             join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name
               join dba_tablespaces t on t.tablespace_name = l.tablespace_name
    )
select segment_size_mb - used_size_mb empty_size, parse_info.*
from parse_info
order by empty_size desc 
/


EMPTY_SIZE	SEGMENT_SIZE_MB	USED_SIZE_MB	EXPIRED_SIZE_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125	120.1875	29.984375	89.390625	MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625	32.125		4.3984375	27.0703125	SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625	24.1875		9.546875	14.015625	SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875	16.1875		5.0703125	10.5		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;

end;

get_info as (

select f_lob_space_info(owner, segment_name,

decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type),

partition_name) as lob_info,

segment_type, owner, segment_name, partition_name, bytes

from dba_segments

where segment_subtype='SECUREFILE'

and segment_type in ('LOBSEGMENT', 'LOB PARTITION')

parse_info as (

select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,

to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as used_size_mb,

to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as expired_size_mb,

g.owner, l.table_name, l.column_name, g.segment_name lob_name

from get_info g

join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name

join dba_tablespaces t on t.tablespace_name = l.tablespace_name

)

select segment_size_mb - used_size_mb empty_size, parse_info.*

from parse_info

order by empty_size desc

EMPTY_SIZE SEGMENT_SIZE_MB USED_SIZE_MB EXPIRED_SIZE_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 120.1875 29.984375 89.390625 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 48.1875 0 47.7109375 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 40.1875 0 39.71875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 32.125 4.3984375 27.0703125 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 24.1875 9.546875 14.015625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 16.1875 5.0703125 10.5 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

first with function block is to call dbms_space.space_usage procedure and return data in string, on second with clause (get_info) call the function and get lob information, by the way SEGMENT_TYPE for a lob segment is “LOB SEGMENT” but dbms_space.space_usage does not accept it as segment_Type, you must send “LOB” instead, then parse result as columns and list SEGMENT_SIZE_MB which is total size of LOB segment size in megabytes
USED_SIZE_MB which is actual used size in megabytes
EXPIRED_SIZE_MB which is expired data size (for versioning) in megabytes.

to calculate empty space just subtract used size from segment size. this is approximately empty size in your lob segment. if you have a partitioned table then you will get result for each partition (like mustafa.tmp table in the example). I hope this script helps in your job.

EDIT:

I forgot to mentioned why lob segments have empty spaces. biggest reason is delete operation of course. if you delete a big portion of the table (or lob segment) then deleted space will be considered as empty space. So, if you delete on a table, should you shrink the lob segment or table? NO! that space will be used by the table again. if this is your standard operation then empty spaces in table/lob segment will be reused but let’s say you inserted many rows by a mistake or because of a bug in your code and then deleted those rows. if you have enough disk space for the database then there is nothing to worry about (mostly). That table will get many inserts in time and it will use that space eventually but if too much space is reserved in the table / lob segment and maybe you have a disk space issues then you can reclaim it (by shrinking or moving table/lob segment). So, this is not a regular thing.

EDIT 2:

sql above is aiming to show all information about lob like used space, total space, expired space etc. probably , you will just need to see empty space in lob. so you can use this sql statement. this will work faster because no regexp usage and parsing operations. I also subtracted “unexpired bytes” from total size since this space is still in use.

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return segment_size_bytes - used_bytes - unexpired_bytes;
    end;
    get_info as (
      select f_lob_free_space_info(s.owner, s.segment_name, 
                                   decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type), 
                                   s.partition_name)/1024/1024 as potential_empty_space_in_mb,
             s.segment_type, s.owner,
             l.table_name, l.column_name, s.segment_name lob_name, s.partition_name
      from   dba_segments s
               join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name
      where s.segment_subtype='SECUREFILE'
        and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    )
select *
from get_info
order by potential_empty_space_in_mb desc 
/

EMPTY_SPACE_IN_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125		MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625		SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625		SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return segment_size_bytes - used_bytes - unexpired_bytes;

end;

get_info as (

select f_lob_free_space_info(s.owner, s.segment_name,

decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type),

s.partition_name)/1024/1024 as potential_empty_space_in_mb,

s.segment_type, s.owner,

l.table_name, l.column_name, s.segment_name lob_name, s.partition_name

from dba_segments s

join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name

where s.segment_subtype='SECUREFILE'

and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION')

)

select *

from get_info

order by potential_empty_space_in_mb desc

EMPTY_SPACE_IN_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

thanks for reading, your comments are always welcome. wish you healthy days.

catcon.pl cheat sheet

Published on: 2022-08-262022-09-01 by Mustafa Comment

Hello,

Oracle uses a container database architecture permanently anymore, you should be familiar with “catcon.pl” perl file. Let’s say you have container database with 10 different PDBs and you need to run a script file on all of those pdbs (maybe even in root and seed too) so what you need is catcon.pl.

catcon.pl will automatically execute your script file and run it on all or specified containers. Since it is a perl file it must be called with perl binary (perl in linux, perl.exe in windows) and you can find it under $ORACLE_HOME/perl/bin directory and catcon.pl itself is under $ORACLE_HOME/rdbms/admin .

so, you have a sql file to run under the /home/oracle/scripts directory called myfile.sql. let’s see how we can execute it in all pdbs:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle/scripts -b MyScript myfile.sql

1	$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle/scripts -b MyScript myfile.sql

that is it. myfile.sql file will be run in all containers (including root and seed) with the command above. Let’s dissect the command:

$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle/scripts => directory where script is located.
-b MyScript => Prefix for the log files so you can separate them from other scripts logs.
myfile.sql => actual script name

actual script file must be the last parameter. It is that easy. in this example ORACLE_HOME and ORACLE_SID should be set before you run the command otherwise you cannot connect to the database. This is a local connection and by default it is a SYSDBA connection. So whatever you run will be run under SYS user.

To add some more functionality:

-c : list of containers that you want to execute the script:
….catcon.pl -c ‘SALES PDB1 TEST’ => execute the script only at SALES, PDB1 and TEST pdbs.
-C : list of containers that you don’t want to execute the script.
-l : log directory
-s : spool the output of script files.
-S : (capital S) don’t execute the script in root and seed. (Edit: this option also don’t set “_oracle_scripts” parameter as true, so if you are creating a user it won’t be Oracle Maintained user)
-u : specify username/password.
-z : ez-connect string
-n : number of workers. if you want to execute a script for 100 pdbs then you can increase the number of workers to execute script simultaneously on different pdbs. default is 1

Can we use catcon.pl to run a script on a remote server? Yes with a combination of -u and -z

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-d /home/oracle/scripts \
-b MyScript \
-l /home/oracle/logs \
-u mustafa/mustafa \
-z mydbserver.mydomain:1521/CDBX \
-n 2 \
-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'
myfile.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

-d /home/oracle/scripts \

-b MyScript \

-l /home/oracle/logs \

-u mustafa/mustafa \

-z mydbserver.mydomain:1521/CDBX \

-n 2 \

-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'

myfile.sql

run myfile.sql script under /home/oracle/scripts directory on CDBX database and only specified pdbs (PDB1…PDB8) which is on mydbserver.mydomain server using mustafa user and create 2 worker process so script will be run on 2 pdbs at a time and write log files under /home/oracle/logs.

catcon has more parameters but I just write about the ones I used most.

thanks for reading.

Expanded controlfile section n from nnn to nnn records

Published on: 2022-08-10 by Mustafa Comment

Hello everyone,

I have been already write revealing information that control file stores: https://mustafakalayci.me/2020/08/11/control-file-content-and-the-limits/.

Data in control file is separated into “SECTIONS” and you can see those sections name (type) via V$CONTROLFILE_RECORD_SECTION view. for example, database separates a section for “BACKUP SET”s. every section has a record size limit . All those information can be queried via v$controlfile_record_section of course.

RECORDS_TOTAL number of records allocated for that section.
RECORDS_USED number of used records.

You might encounter an alert log message from time to time like “Expanded controlfile section 11 from 100 to 200”. this is a message that database need to store more data in control file and currently allocated space is not enough, so oracle expands the control file. in this sample message, 100 is current RECORDS_USED and RECORDS_TOTAL. since all records are filled, it expands to 200, so RECORDS_TOTAL become 200. how about the section? what is section 11? unfortunately there is no SECTION_ID but V$controlfile_record_section always return rows in specific order and you can use this select to learn section ids:

Select rownum-1 section_code, type, records_total, records_used from v$controlfile_record_section;


SECTION_CODE TYPE                             RECORDS_TOTAL RECORDS_USED
------------ -------------------------------- ------------- ------------
           0 DATABASE                                     1            1
           1 CKPT PROGRESS                               11            0
           2 REDO THREAD                                  8            1
           3 REDO LOG                                    16            8
           4 DATAFILE                                  1024           12
           5 FILENAME                                  4146           31
           6 TABLESPACE                                1024           14
           7 TEMPORARY FILENAME                        1024            3
           8 RMAN CONFIGURATION                          50            0
           9 LOG HISTORY                                584          561
          10 OFFLINE RANGE                             1063           22
          11 ARCHIVED LOG                               896          561
          12 BACKUP SET                                1022            2
          13 BACKUP PIECE                              1006            2
          14 BACKUP DATAFILE                           1063            2
          15 BACKUP REDOLOG                             215            0
          16 DATAFILE COPY                             1000            3
          17 BACKUP CORRUPTION                         1115            0
          18 COPY CORRUPTION                           1227            0
          19 DELETED OBJECT                             818          590
          20 PROXY COPY                                1004            0
          21 BACKUP SPFILE                              131            2
          22 DATABASE INCARNATION                       292            2
          23 FLASHBACK LOG                             2048            0
          24 RECOVERY DESTINATION                         1            1
          25 INSTANCE SPACE RESERVATION                1055            1
          26 REMOVABLE RECOVERY FILES                  1000            1
          27 RMAN STATUS                                141           43
          28 THREAD INSTANCE NAME MAPPING                 8            8
          29 MTTR                                         8            1
          30 DATAFILE HISTORY                            57            0
          31 STANDBY DATABASE MATRIX                    128          128
          32 GUARANTEED RESTORE POINT                  2048            0
          33 RESTORE POINT                             2108            0
          34 DATABASE BLOCK CORRUPTION                 8384            0
          35 ACM OPERATION                               64           11
          36 FOREIGN ARCHIVED LOG                      1002            0
          37 PDB RECORD                                  10            3
          38 AUXILIARY DATAFILE COPY                    128            0
          39 MULTI INSTANCE REDO APPLY                    1            0
          40 PDBINC RECORD                              113            0
          41 TABLESPACE KEY HISTORY                     151            0

Select rownum-1 section_code, type, records_total, records_used from v$controlfile_record_section;

SECTION_CODE TYPE RECORDS_TOTAL RECORDS_USED

------------ -------------------------------- ------------- ------------

0 DATABASE 1 1

1 CKPT PROGRESS 11 0

2 REDO THREAD 8 1

3 REDO LOG 16 8

4 DATAFILE 1024 12

5 FILENAME 4146 31

6 TABLESPACE 1024 14

7 TEMPORARY FILENAME 1024 3

8 RMAN CONFIGURATION 50 0

9 LOG HISTORY 584 561

10 OFFLINE RANGE 1063 22

11 ARCHIVED LOG 896 561

12 BACKUP SET 1022 2

13 BACKUP PIECE 1006 2

14 BACKUP DATAFILE 1063 2

15 BACKUP REDOLOG 215 0

16 DATAFILE COPY 1000 3

17 BACKUP CORRUPTION 1115 0

18 COPY CORRUPTION 1227 0

19 DELETED OBJECT 818 590

20 PROXY COPY 1004 0

21 BACKUP SPFILE 131 2

22 DATABASE INCARNATION 292 2

23 FLASHBACK LOG 2048 0

24 RECOVERY DESTINATION 1 1

25 INSTANCE SPACE RESERVATION 1055 1

26 REMOVABLE RECOVERY FILES 1000 1

27 RMAN STATUS 141 43

28 THREAD INSTANCE NAME MAPPING 8 8

29 MTTR 8 1

30 DATAFILE HISTORY 57 0

31 STANDBY DATABASE MATRIX 128 128

32 GUARANTEED RESTORE POINT 2048 0

33 RESTORE POINT 2108 0

34 DATABASE BLOCK CORRUPTION 8384 0

35 ACM OPERATION 64 11

36 FOREIGN ARCHIVED LOG 1002 0

37 PDB RECORD 10 3

38 AUXILIARY DATAFILE COPY 128 0

39 MULTI INSTANCE REDO APPLY 1 0

40 PDBINC RECORD 113 0

41 TABLESPACE KEY HISTORY 151 0

So, 11 is ARCHIVED LOG. which means to be able to store currently required (based on your deletion policy in rman) database need to increase number of records in control file.

Database needs to increase those sections when required data to be stored increase. You can see the alert log messages above when this occur.

wish you all good, healthy days.

lsnrctl or new db connection is too slow

Published on: 2022-07-202022-07-21 by Mustafa Comment

Hello everyone,

I know this is not new but I haven’t seen this problem for a long time and it takes a while to find out it.

A team leader (who is an elder brother to me) from the company I started to my first job called me and asked about a problem on their customer. Their customer complained about too slow db connection and no problem after the connection. first things first, I wanted to check database availability but because of some PATH problems (this was a Windows server) it took some time. after fixing environment variables, I finally reached out to database via local sysdba connection and database is up and running. also, connection was fast. by the way database was an 11.2 version which is quite old.

So, I started to focus on LISTENER. I first checked the status but “lsnrctl status” command was quite slow. then I stopped and started again but these two operations was also slow. I checked the logfile under: $ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/trace but everything seemed ok! When I tried to logon via listener on the database server (using the net service name) it took around 70 seconds.

I started a listener trace (adding tracing parameters into sqlnet.ora file and restarting the listener) and tried to make a connection again. as soon as I issue my connection command (sqlplus username/pass@db) trace file is created. this means network connection was fast but while reading the trace file, after passing the connection information, listener was waiting for around 60-70 seconds.

I understand that network has no issue, connection arrives immediately but db connections and lsnrctl commands were slow. as I said I already checked, listener trace directory because I know that if listener.log file under trace directory has a big size, this could cause a slow operations on listener but it was around few megabytes. I checked few more things and finally I realized that I never checked “alert” directory under diag ($ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/alert) and when I opened it, there were over 1900 log.xml file which is the xml version of listener.log file. I deleted them all and it worked like a charm. Listener was checking all xml files to decide to write log data in which xml file. that was the cause and we were able to solve the problem.

Always check your listener log files and keep their size minimum as much as possible.

Wish you all healthy days.

SESSION_CACHED_CURSORS Parameter and How can It Affect the Database

Published on: 2022-07-02 by Mustafa Comment

Hello,

Let’s talk about the SESSION_CACHED_CURSORS parameter. It might have significant affect on your system so understanding how it works and what it cause is important.

This parameter limits the number of cached cursors on the session. As you know there is 2 kind of parse SOFT and HARD. Hard parse is creating the cursor from the scratch. Checking grants, syntax and semantics, creating execution plans etc. Soft parse is using a previously created cursor. So, if Oracle has already calculated all information for a query (sql statement, cursor) then, it stores it in the shared pool and can use it over and over again. not calculating everything from scratch is a big performance increment.

Even in a soft parse, Oracle still need to search through library cache and find the cursor first. SESSION_CACHED_CURSOR allows you to cache a cursor in your sessions PGA (and still in SGA). This way if you run a query over an over, it will be found and execute even faster. Basically, SESSION_CACHED_CURSOR is kind of “Softer” parse. Starting from 11g its default value is 50 and in 21c it is the same.

You can see cached cursors via V$OPEN_CURSOR view. Let’s open a new sqlplus session and check its cached information. to check cache information I will use a separate session because I don’t want to affect the main session. here is main session:

sqlplus mustafa/mustafa@orcl

select userenv('SID') from dual;
USERENV('SID')
--------------
          1470

sqlplus mustafa/mustafa@orcl

select userenv('SID') from dual;

USERENV('SID')

--------------

1470

so my main sessions SID is 1470. from monitoring session I will check cached objects in the session:

-- Monitoring session:


select cursor_type, last_sql_active_time, sql_text from v$open_cursor 
where sid =1470
order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc;

CURSOR_TYPE                                                      LAST_SQL_ACTIVE_TIM SQL_TEXT                                                    
---------------------------------------------------------------- ------------------- ------------------------------------------------------------

SESSION CURSOR CACHED                                           02/07/2022 12:07:50 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select text from view$ where obj#=:1                       
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select spare6 from user$ where user#=:1                    
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select privilege# from sysauth$ where (grantee#=:1 or grante
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION',
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select to_char(userenv('INSTANCE')) my_inst, to_char(s.sid) 
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
SESSION CURSOR CACHED                                           02/07/2022 12:07:50 select /*+ connect_by_filtering index(sysauth$ i_sysauth1) *
OPEN-RECURSIVE                                                  02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0
OPEN                                                             02/07/2022 12:09:15 select userenv('SID') from dual

-- Monitoring session:

select cursor_type, last_sql_active_time, sql_text from v$open_cursor

where sid =1470

order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc;

CURSOR_TYPE LAST_SQL_ACTIVE_TIM SQL_TEXT

---------------------------------------------------------------- ------------------- ------------------------------------------------------------

SESSION CURSOR CACHED 02/07/2022 12:07:50 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;SESSION CURSOR CACHED 02/07/2022 12:07:50 select text from view$ where obj#=:1

SESSION CURSOR CACHED 02/07/2022 12:07:50 select spare6 from user$ where user#=:1

SESSION CURSOR CACHED 02/07/2022 12:07:50 select privilege# from sysauth$ where (grantee#=:1 or grante

SESSION CURSOR CACHED 02/07/2022 12:07:50 SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION',

SESSION CURSOR CACHED 02/07/2022 12:07:50 select to_char(userenv('INSTANCE')) my_inst, to_char(s.sid)

SESSION CURSOR CACHED 02/07/2022 12:07:50 select decode(upper(failover_method), NULL, 0 , 'BASIC', 1,

SESSION CURSOR CACHED 02/07/2022 12:07:50 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U

SESSION CURSOR CACHED 02/07/2022 12:07:50 select /*+ connect_by_filtering index(sysauth$ i_sysauth1) *

OPEN-RECURSIVE 02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0

OPEN 02/07/2022 12:09:15 select userenv('SID') from dual

I won’t be able to put all output because it will be much bigger in a minute. simple explanation for cursor types:

OPEN is currently opened (last run) sql statement.
OPEN-PL/SQL is currently opened cursors in plsql. it could be more than one, you can open many cursor remember that.
OPEN-RECURSIVE is recursive sql statements for your actual statement (for example Oracle needs a privilege check if you have access to those objects, it runs a query to find that)
SESSION CURSOR CACHED is half of what we are looking for here. direct sql statements which has been run in this session and cached in session PGA.
PL/SQL CURSOR CACHED is the other big half of what we are looking for here. Sql/plsql statements which has been run in a plsql object which is called in this session.

there are few more types but I won’t go detail all of them right now. if session runs a sql statements directly (select, insert, update, delete, merge etc) it will be cached as SESSION CURSOR CACHED. if session calls a plsql objects, all sql statements in it will be cached as PL/SQL CURSOR CACHED. Remember, to cache an sql statement, it must be run at least 3 times, only then it will be cached. by saying “run” cursor must be opened and closed. if you just run a query 3 times, you will not see it in cache because last one is still opened. run something else and then it will be in the cache.

you can see session cached cursors count via session statistics:

select s.sid, n.name, value
from v$statname n, v$sesstat s
where n.name like 'session cursor cache %'
  and s.statistic# = n.statistic# and s.sid=1470;

NAME                         VALUE
---------------------------- ----------
session cursor cache hits            46
session cursor cache count           31

select s.sid, n.name, value

from v$statname n, v$sesstat s

where n.name like 'session cursor cache %'

and s.statistic# = n.statistic# and s.sid=1470;

NAME VALUE

---------------------------- ----------

session cursor cache hits 46

session cursor cache count 31

“session cursor cache hits” is how many times a new statement has been found in session cursor cache. if you run a query 100 times repeatedly, after first 3 (after 3 runs it will be cached) next 97 will be read from cache and session cursor cache hits will increase by 97. “session cursor cache count” is currently number of cached objects (all sql, plsql and dictionary object queries).

as I said by default SESSION_CACHED_CURSORS is 50 so my session can cache up to 50 different statements and if there is more? Oracle will use a Least Recently Used (LRU) algorithm to keep most used (and recently) queries. Previously issued statements will be deleted from the session cache (not from library cache).

Let’s start some tests and run 3000 different queries 3 times (so they can be cached). of course instead of calling 3000 different queries I will use a simple plsql. those queries will be cached as PL/SQL CURSOR CACHED. Also, I will create a tmp table as copy of dba_objects to do my tests:

-- main session
MUSTAFA@orcl-1> create table tmp as select * from dba_objects;

MUSTAFA@orcl-1>
begin
  
  for i in 1..3000 loop
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
  end loop;
end;
/

-- main session

MUSTAFA@orcl-1> create table tmp as select * from dba_objects;

MUSTAFA@orcl-1>

begin

for i in 1..3000 loop

execute immediate 'select count(*) from tmp where object_id = ' ||i;

end loop;

end;

before continue, you might wonder why I used dynamic sql. if I use a static sql as “select count(*) from t where object_id = i” then i would be a bind variable and every query I run would be same. so, we couldn’t see the difference.

From the monitoring session let’s check V$OPEN_CURSOR for main session (a little bit long output)

--monitoring session

select cursor_type, last_sql_active_time, sql_text from v$open_cursor 
where sid =1470
order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc;

CURSOR_TYPE           LAST_SQL_ACTIVE_TIM SQL_TEXT                                                    
--------------------  ------------------- ------------------------------------------------------------
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2987            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2964            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2972            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2952            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2998            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2994            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2951            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2999            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2991            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2961            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2995            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2984            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2977            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2986            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2982            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2983            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2971            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 3000            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2978            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2974            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2990            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2954            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2956            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2965            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2981            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2985            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2953            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2966            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2955            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2969            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2979            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2980            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2992            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2975            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2962            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2957            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2976            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2958            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2960            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2996            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2989            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2968            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2997            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2988            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2970            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2993            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2959            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2973            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2967            
PL/SQL CURSOR CACHED  02/07/2022 19:57:32 select count(*) from tmp where object_id = 2963            
OPEN-RECURSIVE        02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0
OPEN                   02/07/2022 19:57:32 begin    for i in 1..3000 loop     execute immediate 'select

--monitoring session

select cursor_type, last_sql_active_time, sql_text from v$open_cursor

where sid =1470

order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc;

CURSOR_TYPE LAST_SQL_ACTIVE_TIM SQL_TEXT

-------------------- ------------------- ------------------------------------------------------------

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2987

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2964

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2972

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2952

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2998

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2994

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2951

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2999

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2991

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2961

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2995

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2984

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2977

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2986

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2982

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2983

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2971

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 3000

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2978

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2974

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2990

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2954

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2956

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2965

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2981

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2985

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2953

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2966

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2955

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2969

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2979

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2980

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2992

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2975

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2962

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2957

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2976

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2958

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2960

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2996

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2989

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2968

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2997

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2988

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2970

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2993

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2959

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2973

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2967

PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2963

OPEN-RECURSIVE 02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0

OPEN 02/07/2022 19:57:32 begin for i in 1..3000 loop execute immediate 'select

as you see, 50 different select statement has been cached which IDs starting from 2951 to 3000. out last 50 queries. also OPEN cursor is our initial plsql code.

so what would happen if we increase the cache? is it a good thing or a bad thing? well, it depends. it is a good thing because you will be doing “softer” parse. your session will access to repeated queries faster but since this information is stored in PGA (session specific memory area) it will increase the usage of PGA. if you don’t have enough PGA it could be a pain for your database. PGA is automatically managed by Oracle. it grows or shrinks based on session needs. So, you might end up too much pga resize operation and even cannot cache that much queries at all.

Let’s check current PGA size for our main session now, from monitoring session:

col name for a30

select name, round(value/1024/1024) size_in_mb
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# 
  and  s.sid=a.sid
  and  name = 'session pga memory'
  and  s.sid = 1470;

NAME                           SIZE_IN_MB
------------------------------ ----------
session pga memory                     3

col name for a30

select name, round(value/1024/1024) size_in_mb

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid=a.sid

and name = 'session pga memory'

and s.sid = 1470;

NAME SIZE_IN_MB

------------------------------ ----------

session pga memory 3

so our main (testing) session is using 3 MB pga size. now, I will increase the size of session_cached_cursors to 1000 and run previous 3000 queries again.

-- main session

MUSTAFA@orcl-1> alter session set session_cached_cursors=1000;

MUSTAFA@orcl-1> 
begin
  
  for i in 1..3000 loop
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
    execute immediate 'select count(*) from tmp where object_id = ' ||i;
  end loop;
end;
/

-- main session

MUSTAFA@orcl-1> alter session set session_cached_cursors=1000;

MUSTAFA@orcl-1>

begin

for i in 1..3000 loop

execute immediate 'select count(*) from tmp where object_id = ' ||i;

end loop;

end;

check the pga size now:

--monitoring session

select name, round(value/1024/1024) size_in_mb
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# 
  and  s.sid=a.sid
  and  name = 'session pga memory'
  and  s.sid = 1470;

NAME                           SIZE_IN_MB
------------------------------ ----------
session pga memory                     8

--monitoring session

select name, round(value/1024/1024) size_in_mb

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid=a.sid

and name = 'session pga memory'

and s.sid = 1470;

NAME SIZE_IN_MB

------------------------------ ----------

session pga memory 8

now it uses 8MB of PGA. Numbers could be small but think it this way; we are using 2.5 times higher PGA then previous. Also, if you have 10.000 active sessions, this could be a problem. it is a high usage. if you have enough memory then, there is nothing to be afraid of or at least you should be adjusting your memory components accordingly. there is no correct size or ratio because this is highly depend on your application coding. You must test the values because if your application uses same queries during a business job then, you can have a smaller session_cached_cursors but if not then, you must increase the size based on your memory.

how about the good side? Yes, let’s measure that. Assume we have a business process running with 400 different queries for every run and run this process 1000 times in a day. how it would affect the performance increasing or decreasing the parameter. I will start with 100.

--main session

MUSTAFA@orcl-1> set timing on

MUSTAFA@orcl-1>alter session set session_cached_cursors=100;

Session altered.

MUSTAFA@orcl-1>
begin
  for k in 1..1000 loop
    for i in 1..400 loop
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
    end loop;
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.38

--main session

MUSTAFA@orcl-1> set timing on

MUSTAFA@orcl-1>alter session set session_cached_cursors=100;

Session altered.

MUSTAFA@orcl-1>

begin

for k in 1..1000 loop

for i in 1..400 loop

execute immediate 'select count(*) from tmp where object_id = ' ||i;

end loop;

end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.38

it took 29.38 seconds to run all queries. Now increasing the parameter value to 400 (which is almost sufficient to store all queries I run):

--main session

MUSTAFA@orcl-1> set timing on

MUSTAFA@orcl-1>alter session set session_cached_cursors=400;

Session altered.

MUSTAFA@orcl-1>
begin
  for k in 1..1000 loop
    for i in 1..400 loop
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
      execute immediate 'select count(*) from tmp where object_id = ' ||i;
    end loop;
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.63

--main session

MUSTAFA@orcl-1> set timing on

MUSTAFA@orcl-1>alter session set session_cached_cursors=400;

Session altered.

MUSTAFA@orcl-1>

begin

for k in 1..1000 loop

for i in 1..400 loop

execute immediate 'select count(*) from tmp where object_id = ' ||i;

end loop;

end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.63

24.63 seconds. 5 seconds shorter which means around %17 percent faster. which is not bad and actually good. think your system is running faster by %17 percent without doing a complex sql tuning or changing the hardware. if you set session_cached_cursors parameter between 100 and 400, elapsed time would be between 24 and 29 accordingly, pga usage too.

finally, you can also query how many times your session used session cached cursors :

select n.name, value
from v$statname n, v$sesstat s
where n.name like 'session cursor cache %'
  and s.statistic# = n.statistic# and s.sid=1470;

NAME                                VALUE
------------------------------ ----------
session cursor cache hits        2011646
session cursor cache count           399

select n.name, value

from v$statname n, v$sesstat s

where n.name like 'session cursor cache %'

and s.statistic# = n.statistic# and s.sid=1470;

NAME VALUE

------------------------------ ----------

session cursor cache hits 2011646

session cursor cache count 399

as you see currently, 399 different queries (sql statements) has been cached in the session and those queries has been accessed 2.011.646 times.

This is a quite nice trick to me but must be used cautiously because of the increase of PGA usage.

I hope this helps to increase your databases efficiency.

Wish you all healthy days.

Marked as unrecoverable datafile in V$datafile even if they are not!

Published on: 2022-06-292022-07-18 by Mustafa Comment

Hello,

it has been a while since I posted. I lost a close relative to me and it wasn’t easy. So, let’s continue.

I run orachk on one my development databases yesterday and result was very informative. Interestingly, I had a “FAILED” check that indicates some of the datafiles are not recoverable! This is a serious issue so I checked. Check detail indicates result of the query below shouldn’t return a row:

select file#, unrecoverable_time, unrecoverable_change# from v$datafile 
where unrecoverable_time is not null and unrecoverable_change# > 0;

1 2	select file#, unrecoverable_time, unrecoverable_change# from v$datafile where unrecoverable_time is not null and unrecoverable_change# > 0;

and there were rows returned indeed. this database is not in force logging mode because it is just the development database and customer agrees with that. for out of curiosity I checked the production database and there were some datafiles returned from the query above. that concerns me. production database is in force logging mode . When I checked unrecoverable_time column, I realized that dates are really old and this is odd to me because I get full backup every day. How could these files be unrecoverable?

I run “report unrecoverable” command on RMAN and it shows nothing (for both production and development database). That is good but why those datafiles marked as unrecoverable and still listed as that way? if you do a unrecoverable operation on a table then, datafiles that those tables are stored in will be marked as unrecoverable in V$datafile.

of course, it is turned out this is a lack of knowledge of me about unrecoverable_time column on v$datafile. here is 19c documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-DATAFILE.html#GUID-7BF7955C-9705-40F4-B2F6-5D7F3A32DD30

UNRECOVERABLE_TIME DATE Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

This column is “updated only”. so, if you did a unrecoverable operation like sqlloder direct load on a table, related datafile will be marked as unrecoverable and unrecoverable_time column will be updated on v$datafile. Even after a full backup, this won’t be updated back to null! it will just keep the last unrecoverable time information.

Better way to check unrecoverable datafiles could be this:

select df.*
from   v$datafile df, v$backup bk 
where  df.file#=bk.file# 
  and  df.unrecoverable_change# <> 0 
  and  df.UNRECOVERABLE_TIME >
   (select max(end_time)
    from   v$rman_backup_job_details
    where  input_type in ('DB FULL' ,'DB INCR')
   );

select df.*

from v$datafile df, v$backup bk

where df.file#=bk.file#

and df.unrecoverable_change# <> 0

and df.UNRECOVERABLE_TIME >

(select max(end_time)

from v$rman_backup_job_details

where input_type in ('DB FULL' ,'DB INCR')

);

This also make me think why ORAchk runs the first query at all since it is not provide an exact information about unrecoverable datafiles. Maybe a minor bug or maybe still there is something that I don’t know.

Edit: I realized that I didn’t mention how to clear unrecoverable_time and unrecoverable_change# columns. it won’t be clear if you take a full (or any kind of) backup. these information is stored in control file and as I mentioned before, this column is only updated. So, you need to recreate your control file and this operation requires a shutdown.

Generate “create controlfile” statement, shutdown database, startup in nomount mode, recreate controlfile, recover database if needed and then open the database. one post step is to catalog recovery area because this new control file won’t have any recovery file information.

alter database backup controlfile to trace as ‘/tmp/my_control_file2.ctl’ noresetlogs ;
shutdown immediate;
startup nomount;
create controlfile … — use noresetlogs option for not to loose any data
alter database open;

thanks for reading. wish you all healthy, happy days.

How to Convert LONG to Clob PART 2

Published on: 2022-05-062022-05-06 by Mustafa Comment

Hi,

I just want to write a small post today. Some time ago, I shared a way to convert long to clob in a query directly: https://mustafakalayci.me/2020/04/06/how-to-convert-a-long-column-to-clob-in-a-query/ Even if this method works, it works really slow and anything slow annoys me!

So, I want to share another method which is much much faster than Sys_dburigen function but THIS IS UNDOCUMENTED and UNSUPPORTED! It is used by internally by oracle so it works fine but you must be careful. Since, no one uses LONG column in their application (right? you don’t! please don’t!) this kind of code is handy for mostly admins because for backward compatibility data dictionary has some LONG data type columns.

What we are going to use is a LIBRARY called UTL_XML_LIB . I almost never create objects under SYS user, so I will create this code under a privileged admin user. first we must grant privileges on UTL_XML_LIB library and then create an EXTERNAL procedure in plsql.

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;
grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

sqlplus sys/oracle@orcl as sysdba

create user mustafa identified by mustafa;

grant dba to mustafa;

grant all on UTL_XML_LIB to mustafa;

now create external procedure:

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt    in varchar2,
                          rowid   in rowid,
                          lobloc  in out nocopy clob
                         ) is
  external
  name "kuxLong2Clob"
  language c
  library sys.utl_xml_lib
  with context
  parameters (context,
              stmt     ocistring,      
              stmt     indicator sb4,
              rowid    ocistring,      
              rowid    indicator sb4,
              lobloc   ociloblocator,  
              lobloc   indicator sb4
             );
             /

sqlplus mustafa/mustafa@orcl

create or replace procedure mustafa.my_long2cloc (stmt in varchar2,

rowid in rowid,

lobloc in out nocopy clob

) is

external

name "kuxLong2Clob"

language c

library sys.utl_xml_lib

with context

parameters (context,

stmt ocistring,

stmt indicator sb4,

rowid ocistring,

rowid indicator sb4,

lobloc ociloblocator,

lobloc indicator sb4

);

“kuxLong2Clob” is a function which returns void (basically it is a procedure) written in C by Oracle. this procedure takes a select statement for your LONG column and rowid. procedure returns CLOB data as an out parameter. I created my_long2clob procedure but to be able to call it via SQL statement, it must be a function, so:

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as
  retval clob;
begin
  dbms_lob.createtemporary(retval,true);
  mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);
  return retval;
end;
/

create or replace function mustafa.f_my_long2cloc (p rowid, p_table varchar2, p_col varchar2) return clob as

retval clob;

begin

dbms_lob.createtemporary(retval,true);

mustafa.my_long2cloc('select ' ||p_col ||' from ' || p_table || ' where rowid = :1', p, retval);

return retval;

end;

to test it, I won’t create a new table with a LONG column but instead I will use one of in the data dictionary (which I use these kind of codes against). Let’s use SYS.TRIGGER$ table which is the base table for DBA_TRIGGERS view. ACTION# column is a LONG data type. of course we must first grant select privilege on SYS.TRIGGER$ to mustafa user.

sqlplus sys/oracle@orcl as sysdba

 grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select  mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#') 
from SYS.TRIGGER$; 

...
...
...

sqlplus sys/oracle@orcl as sysdba

grant select on TRIGGER$ to mustafa;

sqlplus mustafa/mustafa@orcl

select mustafa.f_my_long2cloc(rowid, 'SYS.TRIGGER$', 'ACTION#')

from SYS.TRIGGER$;

...

I suppressed the output but here it is. really fast long to clob conversion in SQL.

as a CON, it will only work against tables not VIEWs! you cannot use it as “f_my_long2cloc(rowid, ‘DBA_TRIGGERS’, ‘TRIGGER_BODY’)”. Since it requires ROWID, you must be referring underlying table and its rowid.

beyond that, if you want to get LONG as VARCHAR2, similarly you can use “kpdbLong2Varchar2” procedure in DBMS_PDB_LIB library. as UTL_XML_LIB it is very fast. only difference is, it returns VARCHAR2 and trims the overflow part. Where is it in use? Check how DBA_VIEWS shows TEXT_VC column data 😉

I hope this helps to fight against LONG columns in your codes.

Wish you all healthy, happy days.

Zero Data Loss DISASTER Database for Standard Edition 2

Published on: 2022-04-22 by Mustafa Comment

Hi,

As you know Data Guard is an Enterprise Edition option and only accepted method to protect your data against a “DISASTER” (DR) is data guard. How about Standard Edition 2(SE2)? Is this possible? Well I did it so, king of yes 🙂 with some risk of course.

Warining: I don’t take any responsibility for wrong settings. Wrong actions can damage your database. Please be careful.

if you google Oracle SE2 Data guard or Manual Log Shipping, you will find many blog post and documents about it. Even youtube has videos about this but almost all of them (at least all I’ve seen and read) are not “Zero Data Loss”. Steps are quite easy:

you have a production database based on SE2
you use another server as your DR server. (it could be on the same server if you use duplicate snapshot method)
copy all your backup
restore database and keep it in MOUNT mode
copy your archivelog ever X minutes to DR server
recover database using rman or sqlplus.

And that is it. This is a valid method. Only GAP is in this, you are transferring your archive logs but not redo logs. This means, you will certainly loose some data in case of a disaster because changes in redo logs will be lost (if you cannot reach out the server). So, what can we do? basic answer is by copying redo logs too but how? Redo logs are changing constantly. Even if you copy redo logs with in a 1 minute interval, you are still be able to lost 1 minute data.

To achieve this, you must force database to write your both redo logs and archive logs onto DR server. Not copying files in an interval, force database to write it there. Probably, you already understood what I did. to use this kind of method you should (must) have a strong, reliable, stable, fast network connection between your production and disaster servers. Because, we will send redo logs and archivelogs instantly to DR server.

I am using 19.15 databases on Oracle Linux 8 (virtual box). Same things can be done in other operating systems too. Logic will be same.

Here are the steps that I am about to do:

On the DR server, create a folder to share over network via NFS.
On the PROD server, Mount this shared folder on PROD server.
On the PROD server, Add an archive log destination to this shared folder.
On the PROD server, Add redo log members for each group to this shared folder.
On the DR server, create a copy of prod database via rman (not duplicate)
On the DR server, catalog archive logs, which resides on shared folder and recover database.
In a disaster situation, copy redo logs to their original location with original name and recover database.

that’s it. Let me demonstrate those steps.

I created 2 virtual machines. They have both Oracle DB Software and one of them (PROD) has a database and I want to use other virtual machine as DR.

Oracle home is under /u01/app/oracle/product/19c/dbhome_1
I (always) use OMF (Oracle Managed Files).
data files are under /u01/app/oracle/oradata
recovery area is under /u01/app/oracle/fast_recovery_area
SID of database is cdb
PROD server ip is 192.168.56.101
DR server ip is 192.168.56.104

1- On the DR server, create a folder to share over network via NFS

As the oracle user (Oracle db software owner user), create necessary directories.

mkdir /u01/PROD_LOGS
chmod 1777 /u01/PROD_LOGS

1 2	mkdir /u01/PROD_LOGS chmod 1777 /u01/PROD_LOGS

this is the folder that I will share over the network and mount it (map it) on the production server.

to share over network, as root user:

vim /etc/exports
#add the lines below to /etc/exports
/u01/PROD_LOGS 192.168.56.101(rw,sync)
    
exportfs -r
exportfs -v
# output of exportfs -v should be like this:
/u01/PROD_LOGS	192.168.56.101(sync,wdelay,hide,no_subtree_check,sec=sys,rw,secure,root_squash,no_all_squash)

#enable necessary services    
systemctl start nfs-server.service   
systemctl enable nfs-server.service  
systemctl start rpcbind            
systemctl enable rpcbind

vim /etc/exports

#add the lines below to /etc/exports

/u01/PROD_LOGS 192.168.56.101(rw,sync)

exportfs -r

exportfs -v

# output of exportfs -v should be like this:

/u01/PROD_LOGS 192.168.56.101(sync,wdelay,hide,no_subtree_check,sec=sys,rw,secure,root_squash,no_all_squash)

#enable necessary services

systemctl start nfs-server.service

systemctl enable nfs-server.service

systemctl start rpcbind

systemctl enable rpcbind

2- On the PROD server, Mount this shared folder on PROD server

as oracle user, create a directory to mount shared directory on DR. /u01/app/oracle/fast_recovery_area/DR_LOGS will be used for mount point. directories in it is for OMF.

mkdir /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog -p
mkdir /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/archivelog -p

1 2	mkdir /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog -p mkdir /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/archivelog -p

as root user:

mount -t nfs 192.168.56.104:/u01/PROD_LOGS /u01/app/oracle/fast_recovery_area/DR_LOGS
    
vim /etc/fstab
#add the line below to fstab so after a restart it will be automatically mounted.
192.168.56.104:/u01/PROD_LOGS /u01/app/oracle/fast_recovery_area/DR_LOGS nfs defaults 0 0

mount -t nfs 192.168.56.104:/u01/PROD_LOGS /u01/app/oracle/fast_recovery_area/DR_LOGS

vim /etc/fstab

#add the line below to fstab so after a restart it will be automatically mounted.

192.168.56.104:/u01/PROD_LOGS /u01/app/oracle/fast_recovery_area/DR_LOGS nfs defaults 0 0

at this point if I put a file into /u01/app/oracle/fast_recovery_area/DR_LOGS directory on PROD server, it will be written to /u01/PROD_LOGS on DR server

3- On the PROD server, Add an archive log destination to the shared folder

By default for OMF, if there is no value for log_archive_dest_n parameters, then Oracle internally use log_archive_dest_1 parameter as LOCATION=USE_DB_RECOVERY_FILE_DEST. that way, archivelogs are written under /u01/app/oracle/fast_recovery_area directory with sub directory structure of course (/u01/app/oracle/fast_recovery_area(<SID>/archivelog/YYYY_MM_DD).

but! if you assign a value to any of those parameters, then it will just use that location to create archivelogs. so I will set 2 log_archive_dest_n parameter, one location for PROD server and one for shared folder.

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/archivelog';
      
alter system set log_archive_min_succeed_dest=2;

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/archivelog';

alter system set log_archive_min_succeed_dest=2;

by setting these 2 parameters (log_archive_dest 1 and 2) we make sure that oracle will create 2 copies at the same time to 2 different location and one of them is actually out DR server. As you see there is one more parameter which is very important. log_archive_min_succeed_dest, by default is 1 and if you don’t set this as 2, in an access problem situation (network might have issues or DR server could be shutdown), Oracle will just write the first location and pass the inaccessible location. this will cause not writing necessary archive logs to DR server. if we want “zero data loss” DR db then oracle must write archive log to dr server. (also you can use MANDATORY clause).

4- On the PROD server, Add redo log members for each group to the shared folder

as the archive logs, we need to do the same things for redo log too.

alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/';
alter system set db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area';
alter system set db_create_online_log_dest_3='/u01/app/oracle/fast_recovery_area/DR_LOGS';

alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo1.log' to group 1;
alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo2.log' to group 2;
alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo3.log' to group 3;

create pfile from spfile;

alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/';

alter system set db_create_online_log_dest_2='/u01/app/oracle/fast_recovery_area';

alter system set db_create_online_log_dest_3='/u01/app/oracle/fast_recovery_area/DR_LOGS';

alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo1.log' to group 1;

alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo2.log' to group 2;

alter database add logfile member '/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog/redo3.log' to group 3;

create pfile from spfile;

as log_archive_dest_n parameter, if we set any of db_create_online_log_dest_n parameter, we need to set all three of them. 2 locations are on PROD server and 1 location is on DR server. this is not a mandatory step actually but it is a good practice. Because, we can just add redo log members (last 3 command above) to the shared location and this is enough but if one day you need to create another log group and forget to add a log member to shared folder then, you could be exposed to data loss! so set it any way.

then, add 1 member for each log group to shared folder. I use standard naming here because I need to know the groups of files by their name (we will use this later).

lastly, I create pfile to move to DR server, so we will create our DR database.

5- On the DR server, create a copy of prod database via rman

on PROD server, database “cdb” is up and running. Let’s start:

I don’t want to go all the details about standard copy backups and restore a database so, I won’t put outputs here.

On PROD server, take a backup of database via RMAN and then copy everything to DR server via scp.

rman target /
backup database;
alter system switch logfile;
exit;

rman target /

backup database;

alter system switch logfile;

exit;

from shell:

scp -r /u01/app/oracle/fast_recovery_area/CDB/ 192.168.56.104:/u01/app/oracle/fast_recovery_area/
scp -r /u01/app/oracle/product/19c/dbhome_1/dbs/initcdb.ora 192.168.56.104:/u01/app/oracle/product/19c/dbhome_1/dbs/

1 2	scp -r /u01/app/oracle/fast_recovery_area/CDB/ 192.168.56.104:/u01/app/oracle/fast_recovery_area/ scp -r /u01/app/oracle/product/19c/dbhome_1/dbs/initcdb.ora 192.168.56.104:/u01/app/oracle/product/19c/dbhome_1/dbs/

copy all backups, archivelogs etc and also pfile.

then go to DR server and create necessary directories & restore your database:

mkdir -p /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog
mkdir -p /u01/app/oracle/oradata/CDB/onlinelog/

mkdir /u01/app/oracle/admin/cdb/adump -p

mkdir -p /u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog

mkdir -p /u01/app/oracle/oradata/CDB/onlinelog/

mkdir /u01/app/oracle/admin/cdb/adump -p

these directories will be needed for database. restore database:

rman target /
startup nomount
restore controlfile from autobackup;
alter database mount;
restore database;

rman target /

startup nomount

restore controlfile from autobackup;

alter database mount;

restore database;

at this point we have a copy prod database on DR in MOUNT mode.

somet very important thing to do

rm -r /u01/app/oracle/fast_recovery_area/CDB/onlinelog/*

1	rm -r /u01/app/oracle/fast_recovery_area/CDB/onlinelog/*

DO THIS ON DR SERVER ONLY! we are deleting any copied redo logs because during recovery mode, there must not be any redo log!

6- On the DR server, catalog archive logs, which resides on shared folder and recover database

from now on, you can create a recover.sql script and call it ever n minute from cron to recover database regularly. content of recover.sql is:

catalog start with '/u01/PROD_LOGS/CDB/archivelog' noprompt;
recover database;

1 2	catalog start with '/u01/PROD_LOGS/CDB/archivelog' noprompt; recover database;

we must catalog newly arrived archivelogs first and then recover database. I want to remind that /u01/PROD_LOGS directory is the one that we shared over network and map it on to prod server.

every recovery command will end up with an ERROR:

RMAN> @recover.sql

RMAN> catalog start with '/u01/PROD_LOGS/CDB/archivelog' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/PROD_LOGS/CDB/archivelog

List of Files Unknown to the Database
=====================================
File Name: /u01/PROD_LOGS/CDB/archivelog/1_17_1102635802.dbf
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/PROD_LOGS/CDB/archivelog/1_17_1102635802.dbf

RMAN> recover database;
Starting recover at 22-APR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/22/2022 00:50:33
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 2803040

RMAN> @recover.sql

RMAN> catalog start with '/u01/PROD_LOGS/CDB/archivelog' noprompt;

using target database control file instead of recovery catalog

searching for all files that match the pattern /u01/PROD_LOGS/CDB/archivelog

List of Files Unknown to the Database

=====================================

File Name: /u01/PROD_LOGS/CDB/archivelog/1_17_1102635802.dbf

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/PROD_LOGS/CDB/archivelog/1_17_1102635802.dbf

RMAN> recover database;

Starting recover at 22-APR-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=257 device type=DISK

starting media recovery

unable to find archived log

archived log thread=1 sequence=18

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/22/2022 00:50:33

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 2803040

this is an expected behavior. since we don’t apply redo logs, Oracle is looking for the next archive log file in the sequence but couldn’t find it. our recovery is now working. after archive log with sequence 18 is arrived, recover command will raise the same error above for sequence 19.

7- In a disaster situation, copy redo logs to their original location with original name and recover database

from now on, our archive and redo log files are stored on DR server too. if there is a disaster situation, you must open the database with applying all logs including redo logs. this is the only moment that we will apply redo logs.

these are the redo log locations on PROD:

/u01/app/oracle/oradata/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog (this one is the network directory on DR server)

actual directory that redo files are written is /u01/PROD_LOGS on DR server. Those directories must be existed on DR server too (we already created them in scripts above). We must copy redo logs under /u01/PROD_LOGS to actual locations now. I deliberately named redo logs as redoN.log so that I can identify which redo file is belong to which redo group. a very simple sql script and shell script will copy redo logs to original locations:

------------------------------------
move_logs.sql
------------------------------------

set page 0
set heading off
set timing off
set trimspool on
set feed off

spool move_logs.output
select 'cp /u01/PROD_LOGS/CDB/onlinelog/redo'||group#||'.log ' || member from v$logfile;
spool off
exit;

/*************************************************************************/
/*************************************************************************/

put_redo_logs.sh
---------------------------------
#!/bin/sh

CopyCommands=$(sqlplus -s / as sysdba @move_logs.sql)

while read -r row; do
	eval "$row";
	echo "$row";
done < move_logs.output

------------------------------------

move_logs.sql

------------------------------------

set page 0

set heading off

set timing off

set trimspool on

set feed off

spool move_logs.output

select 'cp /u01/PROD_LOGS/CDB/onlinelog/redo'||group#||'.log ' || member from v$logfile;

spool off

exit;

/*************************************************************************/

put_redo_logs.sh

---------------------------------

#!/bin/sh

CopyCommands=$(sqlplus -s / as sysdba @move_logs.sql)

while read -r row; do

eval "$row";

echo "$row";

done < move_logs.output

calling put_redo_logs.sh file will run an sql script which will produce copy shell commands.

After that, you can run recover.sql one more time and this time RMAN will not raise any error and you will see “Recovery Completed” message. now you can open your database and use it.

Now, why did I delete copied redo log files via scp command on DR server? if you recover a redo log in mount mode, control file will raise some flags and all recover commands will start from the sequence of applied redo log, this will cause problems after some time because archivelogs will be deleted eventually because of retention policy. This also means, if you apply a redo log during recovery, to have a healthy copy of database, you must restore both control file & database all over again.

Pros

obviously, you have a DR db with full consistent data.

Cons

This approach contains high risk because if database cannot write archived or redo log to shared folder then production database will hang! You might have some issues about network or DR server might be restarted or shutdown. You might loose disk drives on DR server which shared folder resides on etc etc…

I will write another post about those situations and make some tests. if loosing some data for a small time then, still refreshable pluggable database would be a better option. I will also write a post about it.

if you don’t have a reliable network, this option could be catastrophic! So, try it carefully. I hope this helps in some way.

Wish you all healthy, happy days.

Indexes with NOSEGMENT a.k.a Virtual Index

Published on: 2022-03-302022-03-30 by Mustafa 2 Comments

Hi,

Today, an old trainee has contacted with my on linkedin and we talk about some performance issues about a query. During the conversation I suggest to use “virtual indexes” to simulate the index so I wanted to write about virtual indexes and their benefits in this post.

if you don’t work on a large database (or a small database server) then, you probably don’t get bored while creating an index but if you do then, index creation can be a pain. Let’s say you examined a query and decided to create an index based on some columns could get increase the performance but will it? if your table is a large one then, creating an index could take serious amount of time. During that time, your server will have extra work load and if that index does not work as you assumed then all those time and work load will be for nothing! Also, you will have generated too many archivelogs. Those archivelogs could increase your recovery time in case of a failure.

So what can we do? You can use “virtual index”. Virtual index is an index without a segment which means this index has no data, does not index anything at all! So, what good come out of it? it’s creation time almost instantaneous and no work load on your server, no archivelog generation, no performance impact in any way. So, why do we create it? Because you can check your execution plan and see the cost and other metric values so that you can decide whether this index should be created or not!

This will save you from wasting too much time and give an initial idea.

Here is an example:

select count(8) from dba_objects;

  COUNT(8)
----------
     72544

select count(8) from dba_objects;

COUNT(8)

----------

72544

I have 72.544 rows in my dba_objects view. By using this view, I created a table:

create sequence seq cache 10000;

alter session enable parallel dml;
alter session enable parallel ddl;

create /*+ parallel */ table test1 as 
  select seq.nextval ID, d.*
  from dba_objects d, (select 1 from dba_objects where rownum < 16000)r;

Table TEST1 created.

Elapsed: 00:35:36.25

create sequence seq cache 10000;

alter session enable parallel dml;

alter session enable parallel ddl;

create /*+ parallel */ table test1 as

select seq.nextval ID, d.*

from dba_objects d, (select 1 from dba_objects where rownum < 16000)r;

Table TEST1 created.

Elapsed: 00:35:36.25

I created a BIG table which contains 1.1 Billion rows in it. Those rows are copies of dba_objects view and it takes 35 minutes to create it in parallel (I enabled parallel dml and ddl operations to achieve that). Table size is around 185GB.

Now, Let’s create indexes on it:

create unique index i_test1_1 on test1(id);

Index created.

Elapsed: 00:17:49.45

SQL> create index i_test1_2 on test1(object_id);

Index created.

Elapsed: 00:10:08.74

create unique index i_test1_1 on test1(id);

Index created.

Elapsed: 00:17:49.45

SQL> create index i_test1_2 on test1(object_id);

Index created.

Elapsed: 00:10:08.74

Unique index took almost 18 minutes to complete and non unique took 10 minutes (no parallel). I must say that, my laptop is a monster with 10th gen i9 (8core), 64GB ram and nvme ssds. those timings could be double or triple on a regular db server. As I said, after all this time, if indexes aren’t useful then you drop them.

Instead of that, we can use NOSEGMENT clause while creating the index (virtual index)

Drop index i_test1_1;
Drop index i_test1_2;

SQL> create unique index i_test1_1 on test1(id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00
SQL> create unique index i_test1_2 on test1(object_id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00
SQL>

Drop index i_test1_1;

Drop index i_test1_2;

SQL> create unique index i_test1_1 on test1(id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00

SQL> create unique index i_test1_2 on test1(object_id) NOSEGMENT;

Index created.

Elapsed: 00:00:00.00

SQL>

As you see, it is fast because it just created an index definition into oracle data dictionary. Literally, there is no such index. check dba_indexes:

Select * from dba_indexes where index_name in ('I_TEST1_1', 'I_TEST1_2');

no rows selected

Select * from dba_indexes where index_name in ('I_TEST1_1', 'I_TEST1_2');

no rows selected

How Oracle can use an index if it is not actually created? Answer is STATISTICS. To use virtual index and get a consistent result from it, your statistics must be up to date. Of course, result will not be certain but close enough to give us an idea. By default Oracle will not use virtual indexes in your execution plan so you must set a hidden parameter, then your explained plans will have virtual indexes:

SQL> set autotrace traceonly explain
SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   139 |  6450K  (1)| 00:04:12 |
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |   139 |  6450K  (1)| 00:04:12 |
---------------------------------------------------------------------------

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

   1 - filter("ID"=56789034)



SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.


SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan
----------------------------------------------------------
Plan hash value: 2636153387

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   139 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |     1 |   139 |     4   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | I_TEST1_1 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("ID"=56789034)

SQL> set autotrace traceonly explain

SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan

----------------------------------------------------------

Plan hash value: 4122059633

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 139 | 6450K (1)| 00:04:12 |

|* 1 | TABLE ACCESS FULL| TEST1 | 1 | 139 | 6450K (1)| 00:04:12 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=56789034)

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select * from mustafa.test1 where id = 56789034;

Execution Plan

----------------------------------------------------------

Plan hash value: 2636153387

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 139 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 139 | 4 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | I_TEST1_1 | 1 | | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=56789034)

after setting hidden parameter _use_nosegment_indexes optimizer will create an execution plan with considering virtual indexes and I can see that the cost of the query has decreased to 2 from 6.450.000. This saves so much time. after checking your execution plans, if you are ok with them you can actually create the index.

EDIT: There is 2 things that I forgot to mention. after creating virtual index run DBMS_STATS.GENERATE_STATS procedure to generate some statistics of your virtual indexes.

exec dbms_stats.generate_stats(user, 'I_TEST1_1');

1	exec dbms_stats.generate_stats(user, 'I_TEST1_1');

and the second thing is, this is can be used only in enterprise edition not standard edition. Standard edition has no deferred segment creation so you cannot create a data object without data segment.

To create this virtual index as a real one, you must drop it and re-create it without nosegment clause.

Time is the most valuable thing for us. Don’t waste your time carelessly.

Wish you all healthy, beautiful days.