Oracle Database Lob Maintenance -- Checking Lob Fragmentation and Performing reorg of Lob

Most of time we are concerned  with Growing space of Lob .  Like  reorg of Table we can also  perform Reorg of Lob . 

1453350.1  is master document that  walks  thro0ugh different approach of checking fragmentation and  performing   reorg   in both  scenario of  Basic File and    Secure file Lob .

Before Performing Reorg   we need to check Free-able / Fragmented space 

Note : 

1) CLOBs are stored using a two-byte fixed width character set ([ed] if the session character is multi-byte variable length) , 

which means they may take much more space than you might be expecting, and (2) the getlength() function reports characters not bytes.

2) getlength() will report the decompressed length for every (logical) copy of a LOB value, so summing it across the table could be over-reporting quite dramatically.

3) pctversion is silently ignored by securefiles but used by basicfiles. On the other hand retention can be used for securefiles or basicfiles (assuming automatic undo management is enabled), but its usage with basicfiles doesn’t match its usage with securefiles. Moreover if you include both retention and pctversion in your table declaration Oracle raises error: ORA-32600: RETENTION and PCTVERSION cannot be used together for both basicfiles and securefiles (so Oracle is not quite ignoring pctversion for securefiles). (It may be worth mentioning that if you use export/import to upgrade your database you may find that this “spontaneously” changes a basicfile lob to a securefile lob.)

4) freepools is another parameter that is silently ignored for securefiles but can have a significant impact on the way that basicfiles reuse space from old copies of LOB values hence on the amount of allocated but unused space.

Lob RETENTION  and Pctversion 

 In the olden days, we would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments.

Starting with Oracle 11g, you can now use the RETENTION parameter.

The RETENTION parameter will use the UNDO_RETENTION parameter for determining how long to keep LOB data for read-consistency purposes. But please be advised that the RETENTION parameter does not use the Undo tablespace, and the LOB segment in  tablespace is used for read-consistency purposes.

When you change the UNDO_RETENTION parameter, the LOB segment   is retention value is not modified. If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter. To change the LOB segment  RETENTION value to match the new UNDO_RETENTION value, do the following:

ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);

ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);

By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used. You need to do this for all LOB segments that you intend to modify.

The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

You Cannot Specify Both PCTVERSION and RETENTION for LOBs Creation.

Also You Cannot Specify RETENTION if the Database is Running in Manual Undo Mode.

PCTVERSION :

Specify the maximum percentage of overall LOB storage space to be used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.

RETENTION :

If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION.  NOTE:  RETENTION will remain constant in DBA_LOBS even after subsequent changes to UNDO_RETENTION.  However, LOB undo should take into affect changes to the parameter UNDO_RETENTION over time. 

Checking  Fragmented  and Reclaimable space in Lob  : 

There are  3 approach we can  use  to  check  free-able space  depending  if its  SecureFile  or BasicFile Lob 

1) Manual  steps  mentioned   in doc 1453350.1 for basic file Lob 

2)  Using  Segment Space Advisor: as  mentioned below 

2)  Using  dbms_space.space_usage   for secure file Lob  which is  also documented in  doc 1453350.1.  eg  is  below 

 

 

declare

segment_size_block NUMBER;

segment_size_byte NUMBER;

used_block NUMBER;

used_byte NUMBER;

expired_block NUMBER;

expired_byte NUMBER;

unexpired_block NUMBER;

unexpired_byte NUMBER;

begin

dbms_space.space_usage ('OWNER', '<lob_Segment name>', 'LOB', segment_size_block,

segment_size_byte, used_block, used_byte, expired_block, expired_byte,

unexpired_block, unexpired_byte, null);

dbms_output.put_line('segment_size_blocks = '||segment_size_block);

dbms_output.put_line('segment_size_bytes = '||segment_size_byte);

dbms_output.put_line('used_blocks = '||used_block);

dbms_output.put_line('used_bytes = '||used_byte);

dbms_output.put_line('expired_blocks = '||expired_block);

dbms_output.put_line('expired_bytes = '||expired_byte);

dbms_output.put_line('unexpired_blocks = '||unexpired_block);

dbms_output.put_line('unexpired_bytes = '||unexpired_byte);

end;

/

For Segment  space advisor   below is  working example  and  options  that can be  used 

Run the Segment Space Advisor:

DECLARE

seg_task_id   number;

seg_task_name varchar2(100);

seg_task_desc varchar2(500);

BEGIN

seg_task_name := 'SecureFileDefragmentation1';

seg_task_desc := 'Manual Segment Advisor Run for table BLOGS';

dbms_advisor.create_task (

advisor_name := 'Segment Advisor',

task_id      := seg_task_id,

task_name    := seg_task_name,

task_desc    := seg_task_desc);

END;

/

 

DECLARE

obj_id        number;

BEGIN

dbms_advisor.create_object (

task_name   := 'SecureFileDefragmentation1',

object_type := 'TABLE',

attr1       := 'JULIAN',

attr2       := 'BLOGS', 

attr3       := NULL,

attr4       := NULL,

attr5       := NULL,

object_id   := obj_id);

END;

/

 

BEGIN

dbms_advisor.set_task_parameter(

task_name := 'SecureFileDefragmentation1',

parameter := 'recommend_all',

value     := 'TRUE');

END;

/

 

exec dbms_advisor.execute_task('SecureFileDefragmentation1');

SQL> select message,more_info from dba_advisor_findings where task_name='SecureFile

Performing Reorg  of Lob : 

3  approach can be  used to  Perform Defragmentation of Lob : 

1)  Shrink Command 

2)  Move command 

3) export  Import . 

alter table <table name> move partition <table partition name>

lob (<lob column name>) store as <optional lob partition name> (tablespace <lob tablespace name>);

-or-

alter table <table name> move partition <table partition name>

lob (<lob column name>) store as (tablespace <lob tablespace name>); 

Using Shrink option to reorg Of Lob :

Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause. The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for IOTs.

There are 2 important options/keywords with the shrink space syntax:

COMPACT: If you specify COMPACT, then Oracle only defragments the segment space and compacts the table rows for subsequent release. Meaning Oracle will recover space but will not amend the high water mark (HWM). So, Oracle does not release the space immediately.

CASCADE: If you specify CASCADE, then Oracle performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables. Meaning Oracle will recover space for the object and all dependent objects.

ALTER TABLE blogs ENABLE ROW MOVEMENT;

ALTER TABLE blogs SHRINK SPACE CASCADE   Parallel 8;

ALTER TABLE blogs DISABLE ROW MOVEMENT;

-- Shrink a LOB segment (basicfile only until 21c).

ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);

ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Recover space and amend the high water mark (HWM).

ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).

ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.

ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Using Move  option to reorg Of Lob :

 ALTER TABLE TABLE_NAME MOVE LOB (LOB_COLUMN_NAME) STORE AS (NOCOMPRESS);

or 

 ALTER TABLE TABLE_NAME MOVE LOB (LOB_COLUMN_NAME) STORE AS (tablespace ybs1 ) parallel 8 update indexes ;

Lob Options in Datapump : 

impdp .. TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE

DEFAULT – no lob storage clause is set for CREATE TABLE

NO_CHANGE – use settings from dump file

BASICFILE – creates LOBs as basicfile

SECUREFILE – creates LOBs as securefile

Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Compression for Lob : 

A table compression doesn't affect the LOB compression. It means that if table is compressed, the LOB Segement will not be compressed automatically.

Compression level

MEDIUM and HIGH (Onwards 11gR1)

LOW (Onwards 11gR2)

create table tbl_lob_nocompress (id number,lob_data clob) 

    lob(lob_data) store as securefile (tablespace example nocompress)

create table tbl_lob_compress (id number,lob_data clob) 

   lob(lob_data) store as securefile (tablespace example compress

alter table tbl_lob_compress modify lob(lob_data)(compress high);

set linesize 150

col column_name format a10

select table_name,column_name,segment_name,securefile from user_lobs where table_name like 'TBL%';

show parameter db_secure

select securefile from user_lobs where table_name='TBL_LOB_TEST';

 

 

Views : 

/*  */

set lines 132 pages 50

col owner format a15 heading 'Owner'

col segment_name format a27 heading 'Segment|Name'

col tablespace_name format a15 heading 'Tablespace'

column extents format 9,999 heading 'Extents'

column bytes format 99,999 heading 'Meg'

col segment_type format a10 heading 'Segment|Type'

col column_name format a15 heading 'Column|Name'

col segment_name format a26 heading 'Segment|Name'

col index_name format a26 heading 'Index|Name'

col owner format a15 heading 'Owner'

col table_name format a22 heading 'Table|Name'

col in_row format a3 heading 'In|Row'

start title132 'Database Lob Column Data'

 

spool rep_out\&db\lob_seg

select owner,segment_name,segment_type,tablespace_name,extents,bytes/(1024*1024) bytes from dba_segments where owner not in ('SYS','SYSTEM','PRECISE','MAULT','PATROL','QDBA','OUTLN','XDB','WMSYS','MDSYS','CTXSYS','ODM','SYSMAN') and segment_type like 'LOB%'

/

/*  */

select owner,table_name,column_name,segment_name,index_name,in_row from dba_lobs

where owner not in ('SYS','SYSTEM','PRECISE','MAULT','PATROL','QDBA','OUTLN','XDB','WMSYS','MDSYS','CTXSYS','ODM','SYSMAN')

/

spool off

ttitle off

 Sample commands  for Lob  creation is placed below . 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/lob-sample-codes.html

Below   script can be used to check used and free space in all   SecureFile  Lob 

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 

/

 

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 

/

Reference :

Tempory LOB segment in TEMP tablespace keep increaseing when customer uses CONNECTION POOL (Doc ID 2297060.1)

LOB Partition Segment Uses Excessive Space (Doc ID 2720886.1)

How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)

How To Move Or Rebuild A Lob Partition (Doc ID 761388.1)

LOB segment size is significantly increasing despite of small actual size of data in it (Doc ID 2326423.1)

Why is no space released after an ALTER TABLE ... SHRINK?  (Doc ID 820043.1)

How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)

How LOB columns can be compressed and storage savings can be gained by using Oracle 11g 

Advanced Compression features.11g Advanced Compression - How to Check Space Occupied by LOB  Compression (Doc ID 861344.1)

How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary (Doc ID 422826.1)

Oracle sys grants missing after Schema level export import

During  schema level export/import    there are high  chances of   grants to go missing .    This is  one of case where  sys grants  are found missing  . It  has been  nicely explained  in Oracle Doc 1911151.1 .

Workaround is  to  manually  get DDL  for grants from source database and apply in target  using below   script . 

spool grants_tc.out

col GRANTS for a80

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-- Add below the users and/or roles as appropriate for GRANTEE

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||

      ' to ' || grantee || ';' "GRANTS"

 from dba_tab_privs

where owner = 'SYS' and privilege not in ('READ', 'WRITE')

  and grantee in ('TC')

order by 1;

spool off

References : 

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)

Export/Import Oracle Dataguard / Dg Broker Configuration

 

Most   of time  we  feel   need  to re-create  Dataguard Configuration  however we are scared  to loose   current   configuration . 

From 18c we have option to  backup and  Import  dataguard configuration  making life easy  

To Export 

DGMGRL>export configuration to 'dgbrokerconfigurationexport.txt';

or

DGMGRL>export configuration to '/<location>/dgbrokerconfigurationexport.txt';

If 'TO' clause is not used then the configuration is exported to filename 'SID_dmon_processID-of-DMON_brkmeta_serial-number.trc. ' in the tracedump.

Example : prod_dmon_1000_brkmeta_2.trc , if prod is the SID and pmon process id is 1000

To Import : 

DGMGRL>import configuration from 'dgbrokerconfigurationexport.txt';

or

DGMGRL>import configuration from '/<location/dgbrokerconfigurationexport.txt';

Reference : 

How to Export/Import DG Broker Configuration (Doc ID 2951452.1)

Change Oracle database TIME_ZONE

Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI). 

For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.

When we change   Database Timezone   , we also need to update cluster and  database  Scheduler : 

On standby we  just need to  make changes on Crs .

Note 

--A PDB can have a different time zone.

--You can only change the database time zone if you have no TSLTZ columns

To locate the tables that uses the data type LOCAL TIME ZONE WITH LOCAL TIMESTAMP:

select t.owner, t.table_name, t.column_name, t.data_type

  from dba_tab_cols t, dba_objects o

 where t.data_type like '%WITH LOCAL TIME ZONE'

   and t.owner=o.owner

   and t.table_name = o.object_name

   and o.object_type = 'TABLE'

order by 1

/

 

Changing Time Zone at database level 

We can do only at Pdb level in case of     Pluggable database environment 

alter database set TIME_ZONE='+00:00'; 

ALTER DATABASE SET TIME_ZONE='Europe/London';

 alter pluggable database <pdb1> set time_zone = '00:00';  

Update Clusterware with  Timezone Data:

Please note to change   for Listener only if  we have dedicated listener for your database other wise no need 

During the GI installation, Oracle saves Timezone information in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt file, that makes TZ not to change for CRS even it is changed on OS level.

Timezone can be changed for the database using srvctl:

 timedatectl status|grep zone

 srvctl setenv database -d rspaws -t   'TZ=America/Phoenix'

 srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'

 restart database and listener 

 srvctl getenv database -d rspaws

 srvctl getenv listener

Changing Database  Scheduler TimeZone :

select dbms_scheduler.stime from dual;

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','EUROPE/LONDON')

select dbms_scheduler.stime from dual;

Changing  Time Zone In environment Variable : 

We also need   check  environment variable ORA_TZFILE  as  that also influence  Db timezone . 

eg :   export ORA_TZFILE = America/Phoenix

Changing  Timezone for sql developer and  Windows  Registry : 

If you need to change the time zone of Oracle SQL Developer (or Oracle Data Modeler), then this is how to do it:

Go to the installation directory of Oracle SQL Developer.

Open the file located at: sqldeveloper/bin/sqldeveloper.conf.

At the end of file, add the following line: AddVMOption -Duser.timezone=GMT-4.

Restart your Oracle Sql Developer.

Verifying  Timezone in Oracle Client 

Oracle client uses your TimeZone environment  set at OS 

Use same Timezone for Database and Oracle Client .   Oracle Instant Client comes  with its own timezone  file imbedded in it 

We can check Timezone for Oracle client using ./genezi utility  under $ORACLE_HOME/bin/  .  

We will  see Oracle errors like ORA-01805 and ORA-01804  if  Timezone for Cleint  doesnt matches  database Timezone 

If we are using Full Oracle Client , sometime copying  files under $ORACLE_HOME/oracore/zoneinfo  folder  from Database server to Client home  does fix   for ORA-01804

 ORA-01804  is also reported due to missing libociei.dylib into /usr/locale/lib folder.  

In worse case , to fix    ORA-01804     reinstall Oracle client after fixing  Timezone Environment  at OS level 

To Verify  Environment Variable  for Windows Registry 

Registry Key HKCU\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

Registry Key HKLM\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

(resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ)

Environment variable ORA_SDTZ

Current locale settings of your machine (most likely).

Database time zone if none from above is found (just an assumption)

Change the time zone at session level

-- use alter session commands

ALTER SESSION SET TIME_ZONE=local;

ALTER SESSION SET TIME_ZONE=dbtimezone;

ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';

ALTER SESSION SET TIME_ZONE='+10:00';

  SELECT sessiontimezone FROM DUAL;

 

   

  col SYSTIMESTAMP for a50

  col STIME for a50

 select (select TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi') from dual) sdate,SYSTIMESTAMP, (select dbms_scheduler.stime from dual) STIME, DBTIMEZONE,( SELECT TZ_OFFSET( SESSIONTIMEZONE ) FROM DUAL) SESSIONTIMEZONE,

(SELECT TZ_OFFSET( 'EUROPE/LONDON' ) FROM DUAL) "specific_time_zone" from dual;

Views :

SELECT dbtimezone FROM DUAL;

 select value$ from props$ where name = 'DBTIMEZONE';

select systimestamp from dual;

SELECT tz_version FROM registry$database;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM   database_properties

WHERE  property_name LIKE 'DST_%'

ORDER BY property_name;

set lines 240

col SYSTIMESTAMP forma a40

col CURRENT_TIMESTAMP forma a40

alter session set NLS_DATE_FORMAT='dd-MON-yyyy:hh24:mi:ss';

select dbtimezone,systimestamp,sysdate,current_timestamp,localtimestamp from dual;

References :

Configure Different Timezones per PDB (Doc ID 2143879.1)

ORA-1804: Failure to Initialize Timezone Information (Doc ID 365558.1)

Upgrade DST Time Zone Version in 19C using utltz_upg_apply.sql to avoid ORA-39405 during import

Difference In Dst TZ is  very common  issue  during  migration where we see  below message .  

Luckily  fix is  made very easy in 19c . 

Oracle Data Pump does not support importing from a source database with TSTZ version 35 or 36 into a target database with TSTZ version 34.

OR 

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32.

Upgrade Part : 

For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory

In  case if  Pluggable database we  need to run  script in both cdb and pdb 

The following scripts get delivered with Oracle Database 18c onward

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql

    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required. 

    

    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql

    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

    

    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

    Time zone upgrade check script

    

    $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

    Time zone apply script. Warning: This script will restart the database and adjust time zone data.

Note: If you want to see what is happening when the scripts utltz_upg_check.sql and utltz_upg_apply.sql are being executed, run the following commands:

set PAGES 1000

-- query the V$SESSION_LONGOPS view

select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'),

TIME_REMAINING, SOFAR, TOTALWORK, SID, SERIAL#, OPNAME

from V$SESSION_LONGOPS

where sid in

(select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')

and

SOFAR < TOTALWORK

order by START_TIME;

-- query the V$SESSION and V$SQLAREA views

select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,

S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT,

S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,

S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",

Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"

from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS

where S.SQL_ID = Q1.SQL_ID(+) and

S.PREV_SQL_ID = Q2.SQL_ID(+) and

S.BLOCKING_SESSION = BS.SID(+) and

S.CLIENT_INFO = 'upg_tzv';

 

Have  documented Old method in below Article : 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/12/upgrade-timezone-version-on-195-target.html

Views : 

SELECT version FROM v$timezone_file; 

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

SELECT tz_version FROM registry$database;

SELECT DBMS_DST.get_latest_timezone_version FROM   dual;

Reference : 

Multitenant - CDB/PDB - Upgrading DST using scripts - 12.2 and above - ( With Example Test Case - 19.11 ) (Doc ID 2794739.1)

https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/datetime-data-types-and-time-zone-support.html#GUID-7A1BA319-767A-43CC-A579-4DAC7063B243

Different Time Zone Version In Registry$Database And V$Timezone_file (Doc ID 1255474.1)

Tracking Child Sqlid / Session Id executed By Plsql and Procedure using program_id

Its not easy to track and tune  Child sql that are  executed internally as part  of plsql. 

Unfortunately  Sqlid attached execution plan  and Sql tuning advisor doesnt support on sqlid for plsql hence we need to identify  Child sql that are  executed internally as part  of plsql and tune child sqlid .

While this execution is in-progress, we could easily track the SQL that is currently executing along with its line no# with in the package.

The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID from V$SESSION would tell us the name of the package and method that is currently executing.

Select owner,object_name,object_type,object_id  from dba_objects  where object_name in( 'MYPKG' );

OWNER      OBJECT_NAM  OBJECT_TYPE           OBJECT_ID

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

ABDUL      MYPKG      PACKAGE BODY              99507

ABDUL      MYPKG      PACKAGE                   99506

Check the query is triggered from any procedure

SELECT s.sql_id, s.sql_text FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id and o.object_name = '&procedure_name';

 select sql_fulltext, program_id, program_line#,

        (select object_name

          from all_objects

         where object_id = program_id) object_name ,

       plsql_entry_object_id c1,

       plsql_entry_subprogram_id c2,

        plsql_object_id  c3,

      plsql_subprogram_id c4

   from v$session b1,

       v$sql b2

  where b1.program_id in ( '99506'  ,'99507') 

  and b1.sql_id = b2.sql_id

  and b1.sql_child_number = b2.child_number;

SQL_FULLTEXT         PROGRAM_ID PROGRAM_LINE# OBJECT_NAME          C1         C2      C3            C4

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

SELECT B1.OWNER,B2.U      99507             5 MYPKG             99506          2

SERNAME FROM ALL_OBJ

ECTS B1, ALL_USERS B2

PROGRAM_LINE# represents the line no# at which this SQL is present.

  column text format a40

  select line, text

  from all_source

  where name ='MYPKG'

  and type ='PACKAGE BODY'

  and owner ='DEMO'

  and line <=10

  order by line ;

      LINE TEXT

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

         1 package body mypkg

         2 as

         3      procedure p1 as

         4      begin

         5              for x in (select b1.owner,b2.username

         6                      from all_objects b1, all_users b2)

         7              loop

         8                      null;

         9              end loop;

        10      end;

10 rows selected.

If we want to get  only  objects details  that are accessed  by package we can get  it by below sql 

select pname, tab, type, text from (

select ui.name pname, ud.table_name tab, us.type, 

       ui.st, ui.en, us.line, us.text,

       max(line) over (partition by us.name, us.type) mx_line

from   user_source us

join   user_tables ud

on     upper(us.text) like '%' || table_name || '%'

join   (select name, object_type, object_name, 

               line st, lead(line) over (partition by object_type order by line)-1 en

        from   user_identifiers

        where  type in ('FUNCTION', 'PROCEDURE')

        and    usage in ('DECLARATION', 'DEFINITION')

        and    object_type like 'PACKAGE%') ui

on     ui.object_name = us.name

and    ui.object_type = us.type

where  us.name = 'PKG'

)

where  line between st and nvl(en, mx_line);

 WITH TESTING AS

(

select

DISTINCT

name,

type,

decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,

line body_line,

object_name

from user_identifiers ui

where type = 'PROCEDURE'

and usage_context_id = (select usage_id

from user_identifiers

where object_name = ui.object_name

and object_type = ui.object_type

and usage_context_id = 0)

and object_name = 'MY_PACKAGE_NAME'

and object_type = 'PACKAGE BODY'

order by line

);

Oracle Database Connection pooling Strategies : Database Resident Connection Pooling (DRCP)

Connection pooling is very  less known  to dba community and hence it becomes tough to  troubleshoot connection  issues 

Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database (say 20 connections).

These connections are stored in a pool in the middle tier, an "array" if you will. Each connection is set to "not in use"

When a user submits a web page to the application server, it runs a piece of your code, your code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to you.

We have many connection  poling strategies  for oracle database . 

1)  Database side Resident Connection Pooling ( database side ) 

2)  Jdbc connection pooling using Hikari pool and other 3rd party application 

3)  OCI Session Pooling : Active session Pooling   and Persistent Pooling

In this article we will  talk about DRCP . 

######################

Jdbc COnnection Pooling 

######################

Will document  on Jdbc / Hirakapool  pooling   in another  Blog  however we can get some insight in  below doc 

https://docs.oracle.com/cd/B28359_01/java.111/e10788/optimize.htm#CFHDDFCI

######################

Oci connection pool  details can be fetched from below link :

######################

The OCI8 extension provides three different functions for connecting to Oracle. 

The standard connection function is oci_connect(). This creates a connection to an Oracle database and returns a resource used by subsequent database calls.

The oci_pconnect() function uses a persistent cache of connections that can be re-used across different script requests.

 This means that the connection overhead will typically only occur once per PHP process (or Apache child).

The function oci_new_connect() always creates a new connection to the Oracle server, regardless of what other connections might already exist.

High traffic web applications should avoid using oci_new_connect(), especially in the busiest sections of the application.

References :

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/session-and-connection-pooling.html#GUID-DFA21225-E83C-4177-A79A-B8BA29DC662C

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI091

######################

DRCP 

######################

DRCP is introduced in 11g version of Oracle. It is used for sharing connection to achieve scalability in multi process and multi threaded environment.

Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios in which an application acquires a database connection, works on it for a relatively short duration, and then releases it.

Database resident connection pooling pools “dedicated” servers process.

A pooled server is the equivalent of a server foreground process and a database session combined.

Having a pool of readily available servers has the additional benefit of reducing the cost of creating and closing client connections.

This feature is useful for applications that must maintain persistent connections to the database and optimize server resources (such as memory).

DRCP Pools are like dedicated it’s used to share the connection between multiple application process from different hosts.

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

POOLED SERVER

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

CONNECTION BROKER

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

Clients obtaining connections out of the database resident connection pool are persistently connected to a background process, the connection broker, instead of the dedicated servers. The connection broker implements the pool functionality and performs the multiplexing of inbound connections from the clients to a pool of dedicated servers with sessions.

When a client must perform database work, the connection broker picks up a dedicated server process from the pool and assigns it to the client. Subsequently, the client is directly connected to the dedicated server until the request is served. After the server finishes processing the client request, the server goes back into the pool and the connection from the client is restored to the connection broker.

Steps follow by Client for Connection

1. Client request for connection

2. Broker authenticated and pick the pooled server and hand-off client to that pooled server.

3. The client directly communicates with the pooled server for all its database activity.

4. The pooled server is handed back to the broker when the client releases it.

In 19.10 they have added two new initialization parameters to control number of processes to handle session authentication 

min_auth_servers       

max_auth_servers      

In 19.11 they have added DRCP_DEDICATED_OPT parameter to have the pool behave like a dedicated server (so as a one-to-one client/server) in case you are below the maximum number of pool connection. This is again a trade between performance and resources consumption (the V$AUTHPOOL_STATS view is NOT existing, probably a bug):

DRCP_DEDICATED_OPT

Configure the DRCP

1. Start or Enabled the Pool by connecting with sysdba

Following command start the broker and register with database listener. It must be started before client started request.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS     MAXSIZE

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

SYS_DEFAULT_CONNECTION_POOL    INACTIVE   20

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS   MAXSIZE

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

SYS_DEFAULT_CONNECTION_POOL    ACTIVE   20

2. Disable or Stop the DRCP

execute dbms_connection_pool.stop_pool();

3. For using the Pool you need to make some connection changes at TNS entry level or application level

Add POOLED keyword in TNS entry and application connection string

-- For JDBC connection modified :

host1.oracle.com:1521/orcl:POOLED

OR

-- For TNS connection

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host1.oracle.com)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)

(SERVER=POOLED)))

4. Configure and ALTER DRCP with package DBMS_CONNECTION_POOL.

This is step needed if you want to change the default setting before configuring the DRCP pool.

Following example already set with default value:

execute dbms_connection_pool.configure_pool(

pool_name => 'SYS_DEFAULT_CONNECTION_POOL',

minsize => 4,

maxsize => 40,

incrsize => 2,

session_cached_cursors => 20,

inactivity_timeout => 300,

max_think_time => 600,

max_use_session => 500000,

max_lifetime_session => 86400);

execute dbms_connection_pool.alter_param(

pool_name => 'DRPC_POOL',

param_name => 'MAX_THINK_TIME',

param_value => '1200');

Parameter meaning as:

POOL_NAME: Name of the pool. Default is SYS_DEFAULT_CONNECTION_POOL

minsize: minimum number of pooled server in pool. (default 4)

maxsize: maximum number of pooled server in pool. (default 40)

incrsize: increased number of pooled server is not available if pool is not max limit( default 2)

session_cached_cursors: SESSION_CACHED_CURSORS for all connections in the pool(default 20)

inactivity_timeout: time to remain an idle server in the pool. If a server remains idle upto time limit, it is killed. (default 300 seconds)

max_think_time: Maximum time of inactivity the PHP script is allowed after connecting. (default 120 seconds)

max_use_session: Maximum number of times a server can be taken and released to the pool before it is flagged for restarting. (default 500000)

max_lifetime_session: Time to live for a pooled server before it is restarted. (default 86400 seconds)

num_cbrok: The number of connection brokers that are created to handle connection (default 1)

maxconn_cbrok: The maximum number of connections that each connection broker can handle.(default 40000)

6. Monitor the DRCP pooling from following views:

select connection_pool, status, maxsize from dba_cpool_info;

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

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

At Linux level it will create background processes, called connection broker to handle client request and free server processes:

ps -ef | grep ora_l00 | grep -v grep

oracle   10316     1  0 15:24 ?        00:00:00 ora_l000_orcl

oracle   13522     1  0 15:24 ?        00:00:00 ora_l001_orcl

Views : 

DBA_CPOOL_INFO

V$CPOOL_CC_INFO

V$CPOOL_CC_STATS

V$CPOOL_STATS

 set lines 200

 col username for a15

 col cclass_name for a15

 select username, cclass_name, process_id, program, machine from v$cpool_conn_info;

Reference : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-resident-connection-pooling.html#GUID-D4F9DBD7-7DC6-4233-B831-933809173E39

https://docs.oracle.com/javase/8/docs/technotes/guides/net/http-keepalive.html

Oracle Exadata Cell offloading/ smart scan Not working for sqlid

 

Recently  user reported  that cell offloading was not working for  certain  sql id  . To explore further  on this  planned to  write handy Blog  with some handily information 

Below are parameters controlling offloading : 

cell_offload_compaction: Cell packet compaction strategy.

cell_offload_decryption: Enable SQL processing offload of encrypted data to cells.

cell_offload_parameters: Additional cell offload parameters.

cell_offload_plan_display: Cell offload explain plan display.

cell_offload_processing: Enable SQL processing offload to cells. 

What operations benefit from Smart Scan

Full scan of a heap table.

Fast full scan of a B-Tree or bitmap index.

What operations do not benefit from Smart Scan

Scans of IOTs or clustered tables.

Index range scans.

Access to a compressed index.

Access to a reverse key index.

Secure Enterprise Search.

CREATE INDEX with NOSORT

LONG or LOB column

Cache clause queries

Query more than 255 column

Clustered table

Below checks  were  done to check if   cell offloading is working and to force offloading

--> Check if   cell offloading enabled on database 

show parameter cell 

--> Check if   sqlid is  eligible for  Cell offloading 

select  sql_id , sum(IO_OFFLOAD_ELIG_BYTES_TOTAL) eligible from dba_hist_sqlstat   where  sqlid='99jjh86790j'   group by sql_id ;

--> Check if  offloading is working fine at database level  for other sql 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name like 'cell physical IO%'  group by stat_name ; 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name  in ('cell physical IO bytes saved by storage index',

'cell physical IO interconnect bytes returned by smart scan')  group by   STAT_NAME ; 

--> Check if Cell offloading working for sql id 

column  sql_test format a10 

select sql_id , IO_CELL_OFFLOAD_ELIGIBLE_BYTES , IO_INTERCONNECT_BYTES, 100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)/IO_CELL_OFFLOAD_ELIGIBLE_BYTES "IO_SAVED_%" , SQL_text  from v$sql where  sql_id='cbhjyp1iio'; 

try generating sql monitoring report 

set pagesize 999

set lines 190

col sql_text format a40 trunc

col child format 99999 heading CHILD

col execs format 9,999,999

col avg_etime format 99,999.99

col avg_cpu  format 9,999,999.99

col avg_lio format 999,999,999

col avg_pio format 999,999,999

col "IO_SAVED_%" format 999.99

col avg_px format 999

col offload for a7

select sql_id, child_number child,

decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,

decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,

100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)

/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_                                      SAVED_%",

(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,

--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,buffer_gets/decode(nvl(executions,0),0                                      ,1,executions),null) avg_lio,

--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,disk_reads/decode(nvl(executions,0),0,                                      1,executions),null) avg_pio,

sql_text

from v$sql s

where upper(sql_text) like upper(nvl('&sql_text',sql_text))

and sql_text not like 'BEGIN :sql_text := %'

and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'

and sql_id like nvl('&sql_id',sql_id)

order by 1, 2, 3

/

-->  Ensure Database should not be in DST upgrade state. 

Reference  Exadata: Database Performance Degrades when Database is in Timezone Upgrade Mode (Doc ID 1583297.1)

select name, value$ from sys.props$ where name like '%DST%';

Ways to  force cell offloading 

using parallel hint 

"_serial_direct_read"=TRUE 

 /*+ OPT_PARAM('cell_offload_processing' 'true') */

alter session set "_simple_view_merging"=TRUE;

alter session set "_with_subquery"=materialize;

Making index invisible 

Use full hint . Eg   /*+ FULL(emp) */ 

Handling errors faced while Generating Oracle Awr report / Awr snapshots

Considering we were facing lot of issues related to awr snapshots and awr report  thought of documenting  known  troubleshooting and  known  fix 

Known Issues : 

Issue 1 )    Generating AWR report Hangs on 'Control File Sequential Read

Solution ::  alter session set "_hash_join_enabled"=true  as per Doc  Id : 2045523.1

Issue 2)  AWR snaoshot hangs with enq: WF - contention 

Solution 1:  As per Doc ID 2695000.1 Kill the MMON child processes and the MMON process for the instance that is not generating AWR reports so that it is restarted by the database.

 ps -ef|grep ora_m0

 ps -ef|grep _mmon_

Solution 2 :  Gather stats on these 3 tables and check the manual report generation again . If possible try gathering   fixed objects stats 

SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');

SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRTSQLPLAN');

SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEHSQT');

Verify with the table last analyzed details

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name ='X$KEWRSQLIDTAB';

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KEWRTSQLPLAN';

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KEHSQT';

Solution 3  : As per   Doc ID 2649588.1  MMON Process Causing Blocking And Contention - enq: WF - contention waits   due to increase in  size of awr data which  needs to be purged 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/mmon-process-causing-blocking-and.html  --  Doc ID 2649588.1

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/troubleshooting-missing-automatic.html

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/purging-oracle-awr-details.html

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/manually-purge-optimizer-statistics-awr.html

Obtain flush timing for all the tables. (obtained from note 1301503.1)

set pagesize 999

column name format a28

column time format a29

variable dbid number

exec select dbid into :dbid from v$database;

variable snap_id number

exec select max(snap_id) into :snap_id from wrm$_snapshot where dbid=:dbid;

select table_name_kewrtb name, end_time-begin_time time

from wrm$_snapshot_details, x$kewrtb

where snap_id = :snap_id

and dbid = :dbid

and table_id = table_id_kewrtb

order by table_id;

Data Collection for analysis  : 

1) 10046   tracing 

Try generating Snapshots manually  . Try generating 10046   tracing  in session where you are trying manual snapshots 

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set tracefile_identifier = '10046_awr';

alter session set events '10046 trace name context forever,level 12';

set timing on

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

2) Try generating Tfa 

$TFA_HOME/bin/tfactl diagcollect -srdc dbawrspace

3) Verify Mmon trace files and  alert log files 

4) . AWR info report

Check to see which objects are the largest in the AWR by running an AWR info report

conn / as sysdba

@?/rdbms/admin/awrinfo.sql

4) Other Views 

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

SQL> select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;

SQL> select * from cdb_hist_wr_control;

COLUMN NAME FORMAT a40

COLUMN VALUE FORMAT a30

set lines 200 pages 200

select ksppinm name,

ksppstvl value

from sys.x$ksppi x,

sys.x$ksppcv y

where (x.indx = y.indx)

and ksppinm = '_awr_mmon_cpuusage';

Select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY ;

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history;

select min(savtime) from sys.wri$_optstat_histgrm_history;

SELECT MIN(SNAP_ID), MAX(SNAP_ID), COUNT(*) FROM SYS.WRH$_SYSMETRIC_HISTORY A

WHERE NOT EXISTS

(SELECT * FROM SYS.DBA_HIST_SNAPSHOT B WHERE B.SNAP_ID = A.SNAP_ID AND A.DBID = B.DBID);

SELECT COUNT(1) Orphaned_ASH_Rows

FROM wrh$_active_session_history a

WHERE NOT EXISTS

(SELECT 1

FROM wrm$_snapshot

WHERE snap_id = a.snap_id

AND dbid = a.dbid

AND instance_number = a.instance_number

);

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

**************************

set markup html on spool on

spool /tmp/AWRCheck25thNov.html

set echo on

select systimestamp from dual;

select instance_name,version,host_name,status from gv$instance;

alter session set container = CDB$ROOT;

show con_name

show parameter awr

select * from cdb_hist_wr_control;

select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;

alter session set container=FREXR1P;

exec dbms_workload_repository.create_snapshot();

alter session set container = CDB$ROOT;

select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;

spool off

set markup html off

**************************

References : 

Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (Doc ID 1301503.1)

AWR Snapshots Not Generating ORA-13516 ( Doc ID 2756259.1 )

ORA-13516 AWR Operation failed: SWRF Schema not initialized ORA-06512 SYS.DBMS_WORKLOAD_REPOSITORY ( Doc ID 459887.1 )

Problem: ORA-13516 When Trying To Make A Manual Snapshot Of Repository Database ( Doc ID 365097.1 )

AWR Snapshots Not Generating ( Doc ID 308003.1 )

ORA-13516: AWR Operation Failed: CATPROC Not Valid (Doc ID 2547174.1)

MMON Trace Shows: "*** KEWRAFC: Flush slave failed, AWR Enqueue Timeout" ( Doc ID 560204.1 )

AWR Snapshots Fail to Generate With Associated "Suspending MMON action '%s' for 82800 seconds" alert log messages and Underlying ORA-12751 Errors ( Doc ID 2043531.1 )

Oracle sysresv utility to release shared memory / semaphores -- No need for ipcs and ipcrm

After shutdown of Oracle database , shared memory segments staying behind is known issues   in Linux .  Ideally Dba use Ipcs and Ipcrm  to release  shared  memory segments .

Also there is less known Linux utility pmap 

Eg :  [oracle]$ pmap `pgrep -f lgwr`

There is  Oracle utility sysresv  that can  be used to release shared memory segments and there is no need  to use Ipcs and Ipcrm .  

Eg :  sysresv -l instance1 

 

Usage:

------

sysresv: 

usage   : sysresv [-if] [-d <on/off>] [-l sid1 <sid2> ...]

          -i : Prompt before removing ipc resources for each sid

          -f : Remove ipc resources silently, oevrrides -i option

          -d <on/off> : List ipc resources for each sid if on

          -l sid1 <sid2> .. : apply sysresv to each sid

Default : sysresv -d on -l $ORACLE_SID

Note    : ipc resources are attempted to be deleted for a

          sid only if there is no currently running instance

          with that sid. 

   
Examples:
---------

o  Instance is not running:

   /u02/app/oracle/product/8.1.7> sysresv

   IPC Resources for ORACLE_SID "R817" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "R817" 


o  Instance is running:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "ORCL"


o  Attempting to remove memory and semphores using sysresv when Oracle 
   detects an instance is running:

   /u03/app/oracle/product/8.1.6> sysresv -f

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "ORCL"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "ORCL" 


o  Removing IPC resources:

   [Sysresv shows memory and semaphores exist but Oracle determines the 
    instance is not alive.  Cleanup is needed.]

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "ORCL" 
 

o  Removing IPC resources using sysresv:

   /u03/app/oracle/product/8.1.6> sysresv -i

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "ORCL"
   Remove ipc resources for sid "ORCL" (y/n)?y
   Done removing ipc resources for sid "ORCL"
   /u03/app/oracle/product/8.1.6


   Verify the resources were removed:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "ORCL"  

     
o  If you need to remove memory segments, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -m <memid>

   Where <memid> is the memory id shown in the sysresv output.

   Example:
   % ipcrm -m 16437

   If you need to remove semaphores, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -s <semid>

   where <semid> is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802

Reference :

SYSRESV Utility (Doc ID 123322.1)

Startup fail with ORA-29701 & ORA-29702 after the DB crash. (Doc ID 2160481.1)