Oracle Sql performance Analyzer using Sql tuning sets for testing Optimizer version change .

Since  between  Migrations we were required  to compare sql performance  with different optimizer modes .   Here old  tool  SPA  helped us    .  

Documenting handy steps of myself and will help  others Too . 

The steps involved in the SQL PERFORMANCE Analyzer are

1) Create the Sql Tuning Set (STS).

2) Create a task to run Sql Performance Analyzer.

3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).

         Make a change that needs to tested.

4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).

5) Comparing SQL Trials.

6) Generate Compare report.

You can use the SQL Performance Analyzer to analyze the SQL performance impact of any type of  system change. Examples of common system changes include:

•Database upgrades

•Configuration changes to the operating system, hardware, or database

•Database initialization parameter changes

•Schema changes, such as adding new indexes or materialized views

•Gathering optimizer statistics

•SQL tuning actions, such as creating SQL profiles

Check  what metrics  SPA compare performance of : 

SQL> SELECT metric_name FROM v$sqlpa_metric;

METRIC_NAME   

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

PARSE_TIME               

ELAPSED_TIME             

CPU_TIME                 

USER_IO_TIME             

BUFFER_GETS              

DISK_READS               

DIRECT_WRITES            

OPTIMIZER_COST           

IO_INTERCONNECT_BYTES  

 

9 rows selected.

Report generation Options : 

DBMS_SQLPA.REPORT_ANALYSIS_TASK(

  task_name      IN VARCHAR2,

  type           IN VARCHAR2 := 'text',

  level          IN VARCHAR2 := 'typical',

  section        IN VARCHAR2 := 'summary',

  object_id      IN NUMBER   := NULL,

  top_sql        IN NUMBER   := 100,

  task_owner     IN VARCHAR2 := NULL,

  execution_name IN VARCHAR2 := NULL)

RETURN CLOB;

Level	Description
BASIC	Same as typical
TYPICAL (default)	Information about all statements
ALL	Details of all SQL
IMPROVED	Only improved SQL
REGRESSED	Only regressed SQL
CHANGED	SQL with changed performance
UNCHANGED	Opposite of CHANGED
CHANGED_PLANS	Only SQL with plan changes
UNCHANGED_PLANS	Opposite of above
ERRORS	SQL with errors only

SPA  Steps :

1)  create a STS

exec DBMS_SQLTUNE.create_sqlset (sqlset_name=> '94rn6s4ba24wn') ;

DECLARE

  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

  OPEN cursor1 FOR SELECT VALUE(p)

  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''94rn6s4ba24wn''')) p;

 

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);

END;

/

2)  create a SPA task and associate the STS with it 

DECLARE

  task_name VARCHAR2(64);

  sts_task  VARCHAR2(64);

BEGIN

  task_name := 'Task01';

 

  sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS01', task_name => task_name, description => 'Task for sql_id 94rn6s4ba24wn');

END;

/

3) Execute CONVERT SQLSET Before Change TEST EXECUTE 

SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'CONVERT SQLSET', execution_name => 'convert_sqlset');

 

Generate the report with a SQL statement like this:

SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_before_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

result file that with this particular execution all different plans are displayed, while in compare performance objective SPA is taking only one plan (the one it is currently parsing with information currently available). In any case comparing all plans of same sql_id would provide very complex reports that would probably be not usable…

4) Create Tets execute task ( pre and post changes  ) 

SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'before_change');

 SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_before_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'after_change');

 SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_after_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

5)  Run Compare Performance : 

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_performance');

 

PL/SQL PROCEDURE successfully completed.

Or 

BEGIN

  DBMS_SQLPA.execute_analysis_task(

    task_name        => :v_task,

    execution_type   => 'compare performance', 

    execution_params => dbms_advisor.arglist(

                          'execution_name1', 

                          'before_change', 

                          'execution_name2', 

                          'after_change')

    );

END;

/

  SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_compare_performance.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

Sample Compare report is  published below :

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/spa-sample-compare-report.html

Views : 

select sql_id, plan_hash_value, buffer_gets, elapsed_time, substr(sql_text,1, 30

) text, executions from dba_sqlset_statements 

where sqlset_name = :sts_name

order by sql_id, plan_hash_value;

SQL> SET lines 200

SQL> col description FOR a30

SQL> SELECT * FROM dba_sqlset;

SELECT task_name, status

FROM dba_advisor_tasks

WHERE task_name = :tname;

select execution_name,status, execution_end

from DBA_ADVISOR_EXECUTIONS where task_name='my_sqlpa_demo_task';

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, status

     FROM dba_advisor_executions

     WHERE task_name='Task01';

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, advisor_name, status

     FROM dba_advisor_executions

     WHERE task_name='Task01';

SQL> SELECT last_execution,execution_type,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status

     FROM dba_advisor_tasks

     WHERE task_name='Task01';

SQL> col EXECUTION_NAME FOR a15

SQL> SELECT execution_name, plan_hash_value, parse_time, elapsed_time, cpu_time,user_io_time,buffer_gets,disk_reads,direct_writes,

     physical_read_bytes,physical_write_bytes,rows_processed

     FROM dba_advisor_sqlstats

     WHERE task_name='Task01';

SQL> col PLAN FOR a140

SQL> SET pages 500

SQL> SELECT p.plan_id, RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' ||

     RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,40,'.') ||

     NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') ||

     'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' ||

     NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' ||

     p.partition_start || ' PStop:' || p.partition_stop,'') ||

     'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN

     FROM dba_advisor_sqlplans p

     WHERE task_name='Task01'

     oder BY p.plan_id, p.id, p.parent_id;

SQL> col message FOR a80

SQL> col FINDING_NAME FOR a30

SQL> col EXECUTION_NAME FOR a20

SQL> SELECT execution_name,finding_name,TYPE,impact,message FROM dba_advisor_findings WHERE task_name='Task01';

Reference : 

SQL Performance Analyzer Example (Doc ID 455889.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/analyzing-database-changes-sql-performance.html#GUID-28869D5B-ECCD-4A89-8391-116AE4C6A7D4

Retention and Purging Unified Audit record data from Oracle database

I have   documented in below   article how to  enable  auditing . However  we need to  also understand  how to retain and  purge audit data 

Ideally audit data   resides  in sysaix    tablespace .  We need to move   same to other tablespace 

How to enable audit : 

https://abdul-hafeez-kalsekar.blogspot.com/2020/05/oracle-database-auditing.html

Below   steps are will be used to execute  following s for unified audit data 

1) Changing Unified Audit table location 

2)  Changing Unified Audit table partition range 

3)  Changing Unified audit write mode 

4) Purging Unified audit  data 

5)  Taking backup of Unified audit data 

Audit Location : 

Changing location of AUD$UNIFIED

begin

  dbms_audit_mgmt.set_audit_trail_location(

    audit_trail_type => dbms_audit_mgmt.audit_trail_unified,

    audit_trail_location_value => 'NEW_TSPACE');

end;

/

begin

  dbms_audit_mgmt.set_audit_trail_location(

    audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,

    audit_trail_location_value => 'NEW_TSPACE');

end;

/

SQL> col owner format a10

SQL> col table_name format a15

SQL> col tablespace_name format a12

SQL> col interval format a20     

SQL> set lines 200

          

SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner=’AUDSYS’;

Change Audit table partition range : 

By default aud$unified  table are monthly partitioned  which we  will be  converting to  daily .  

Please note  this  will take effect  from next month    as current data is already enabled with monthly partition . 

 

begin 

dbms_audit_mgmt.alter_partition_interval   ( 1 , 'DAY') ;

END ; 

/ 

Write Mode : 

 Default  wrote mode for  unified audit   is queued mode  where  there is  chance   we will loose audit data   as  audit data is written to memory first and then   to  disk 

 we will be changing  write mode to  immediate 

Confirm from the DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE function.

SET SERVEROUTPUT ON

DECLARE

  value NUMBER;

BEGIN

  value := DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE);

  CASE value

    WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE

      THEN DBMS_OUTPUT.PUT_LINE(value||':QUEUED WRITE MODE');

    WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE

      THEN DBMS_OUTPUT.PUT_LINE(value||':IMMEDIATE WRITE MODE');

    ELSE DBMS_OUTPUT.PUT_LINE('UNKNOWN MODE');

  END CASE;

END;

/

Change the write mode:

Change to queued write mode.

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,

    audit_trail_property_value  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);

END;

/

Change to immediate write mode.

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,

    audit_trail_property_value  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);

END;

/

SELECT PARAMETER_VALUE FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME='AUDIT WRITE MODE';

Purging  Unified audit data : 

Purging Unified audit trail can be achieved  either using last_archive_timestamp  or through  scheduled job 

Set Purge  Archive  timestamp   and Manually  Purge Archive based on  Last_archive_timestamp 

Begin 

  dbms_audit_mgmt.set_last_archive_timestamp (

 DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , 

trunc (SYSTIMESTAMP) -10 ) ; 

end ;

/

Begin 

  dbms_audit_mgmt.clean_audit_trail  ( 

  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , 

  use_last_archive_timestamp => true   ) ; 

end ;

/

BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   USE_LAST_ARCH_TIMESTAMP    =>  TRUE,

   CONTAINER                 =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT );

END;

/ 

Or 

BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time => TO_TIMESTAMP('10-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'));

END;

/

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   use_last_arch_timestamp  =>  TRUE);

END;

/

Purging audit data by Adding Purge schedule 

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

audit_trail_purge_interval => 24 /* hours */,

audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_UNIFIED',

use_last_arch_timestamp => TRUE);

END;

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'audit_last_archive_time',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-10);

END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',

end_date => NULL,

enabled => TRUE,

comments => 'Automatically set audit last archive time.');

END;

/

OR 

begin 

DBMS_SCHEDULER.CREATE_JOB ( 

JOB_NAME => 'UNIFIED_AUDIT_TRAIL_PURGE' ,

JOB_TYPE  => 'PLSQL_BLOCK' , 

job_action =>  'begin     dbms_audit_mgmt.set_last_archive_timestamp ( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,  trunc (SYSTIMESTAMP) -10 ) ; end ;

  Begin   dbms_audit_mgmt.clean_audit_trail  (  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,  use_last_archive_timestamp => true   ) ; 

end ;  ',

enabled => true  ,

end_date => NULL,

reepat_interval  => 'FREQ=DAILY ; BYHOUR=1 ; BYTIME=0 ; BYSECOND=0 ; '  ,

START_DATE =>  SYSTIMESTAMP ,

comment  => 'UNIFIED AUDIT PURGE'   ) ; 

END ; 

/ 

Taking export backup of   Unified audit data 

from 19c onwards . 

expdp system 

full=y 

directory=aud_dp_dir 

logfile=audexp_log.log 

dumpfile=audexp_dump.dmp 

version=18.02.00.02.00 

INCLUDE=AUDIT_TRAILS

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-8140CCBF-77EE-4F86-A055-B9F9AB8B4573

References : 

How To Prevent The Unified Audit Trail From Being Created in SYSAUX, And Change Its Default Partitioning Behavior (Doc ID 2548804.1)

12.1 upgrade to 19c: DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION does not move AUD$UNIFIED table to a different tablespace (Doc ID 2816457.1)

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

How to confirm/change write mode in unified auditing (Doc ID 2520310.1)

Recover Oracle Standby / Dataguard Database From Service

 

Though there are many documents  online for recovering standby using service , i thought of documenting mine  to have handy steps for myself and  help others too 

Before  we take decision  to recover from service . try  below troubleshooting steps  first 

-- take new archive from primary .  This is ensure  current log on standby is not corrupted 

-- restart standby  database 
-- take new standby controlfile from primary 
--  try to start  mrp  with no real time apply 
--  try to start  mrp   in mount 

--  try to start  mrp without parallel 

--  try to start  mrp on another rac node 

 

If there are any nologging changes we can  use  below doc to fix  nologging changes . 

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Recovery  Steps : 

1. Get the latest SCN from standby:

select to_char(current_scn) from v$database;

 

CURRENT_SCN#

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

644203931

2. Stop Managed Recovery and Redo Transport  via Data Guard Broker 

DGMGRL> edit database <primary> set state=TRANSPORT-OFF;

Succeeded.

DGMGRL> edit database <standby> set state=APPLY-OFF;

Succeeded.

3. Take  backup of location  of files 

select name from v$datafile  

union all 

select member from v$tempfile 

union all 

select memeber from   v$logfile ;

4.  Restore Standby Controlfile 

$ srvctl stop database -d <standbydb> -o immediate

$ rman target / nocatalog 

RMAN> startup nomount

RMAN> restore standby controlfile from service <tns alias for primary database>;

RMAN> alter database mount;

RMAN> catalog start with '<DATA DISKGROUP>/<standby db_unique_name/';

RMAN> catalog start with '<RECO DISKGROUP>/<standby db_unique_name>/';

5. Restore Any Missing Files  that are added  recently after gap 

RMAN> select file# from v$datafile where creation_change# >= 644203931;   -- from step 1 .  ( this is  run on primary ) 

Below are  on standby 

RMAN> run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

allocate channel c5 type disk;

allocate channel c6 type disk;

allocate channel c7 type disk;

allocate channel c8 type disk;

set newname for database to NEW;

restore datafile <comma separate list of files> from service <tns alias for primary database> section size <section size>;

}

RMAN> switch database to copy;

6. Clear Online Redo Logs and Standby Redo Logs  .

Execute the following query in SQL*PLUS on the standby to create new logfiles.

SQL> begin

for log_cur in ( select group# group_no from v$log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

/

SQL> begin

for log_cur in ( select group# group_no from v$standby_log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

7.  Restart All Instances to Mount and Re-Enable Redo Transport 

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o mount

DGMGRL> edit database <primary> set state=TRANSPORT-ON;

8 Run Recover From Service 

Dont use noredo    or  "recover standby database"  because  in some cases the RECOVER DATABASE NOREDO command does not apply the incremental changes to the standby database and it just completes in few seconds.

The NOREDO clause specifies that the archived redo log files must not be applied during recovery. Its mainly use  when Olr are lost on standby . 

The RECOVER STANDBY DATABASE command restarts the standby instance, refreshes the control file from the primary database, and automatically renames data files, temp files, and online logs. It restores new data files that were added to the primary database and recovers the standby database up to the current time.

When you use the RECOVER STANDBY DATABASE command to refresh a standby database, you specify either a FROM SERVICE clause or a NOREDO clause. The FROM SERVICE clause specifies the name of a primary service. The NOREDO clause specifies that backups should be used for the refresh, which allows a standby to be rolled forward to a specific time or SCN

.

If you wat to  prefer to use  "RECOVER STANDBY DATABASE"    method its documented in   2431311.1 .  However  we might face  Implicit Crosschecking and Cataloging issue  using  "RECOVER STANDBY DATABASE"    and  we have to set     db_recovery_file_dest=''  as documented in  Doc  1489027.1

$ rman target sys/<password>  <- It is necessary to connect with the password

RMAN > run {

allocate channel c1 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c2 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c3 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c4 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c5 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c6 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c7 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c8 type disk connect '/@<standby instance 2 SID_NAME>';

recover database from service '<primary unique name>' section size <section size> using compressed backupset;  

}

select INST_ID,SID,SERIAL#,OPNAME, (sofar/totalwork)*100 as "%complete", ELAPSED_SECONDS, TIME_REMAINING

from gv$session_longops

where sofar<>totalwork

and totalwork<>0

and SID||SERIAL# in (select SID||SERIAL# from v$session);

From the primary:

SQL> alter system archive log current;

Then from SQL*PLUS on the standby issue the command below.  The UNTIL CONSISTENT clause cannot be used in RMAN.

SQL> recover automatic standby database until consistent;

select inst_id,GROUP#,TYPE,MEMBER from gv$logfile    -->  Verify    log mapped to right path 

Verify standby_file_management  on standby 

9.  Re-Enable Flashback Database

SQL> alter database flashback on;

10.  Restart the Standby and Managed Recovery

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o 'read only'

DGMGRL> edit database <standby> set state=APPLY-ON;

 

Reference : 

How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)

Roll Forward a Physical Standby Database Using RMAN Incremental Backups 'Recover Database Noredo' Does Nothing (Doc ID 841765.1)

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)

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');

select message,more_info from dba_advisor_findings where task_name='SecureFileDefragmentation1';

Or use below procedure 

Find the reclaimable space from lob:

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

create or replace procedure show_space

( p_segname in varchar2,

  p_owner   in varchar2 default user,

  p_type    in varchar2 default 'TABLE',

  p_partition in varchar2 default NULL )

-- this procedure uses authid current user so it can query DBA_*

-- views using privileges from a ROLE and so it can be installed

-- once per database, instead of once per user that wanted to use it

authid current_user

as

    l_free_blks                 number;

    l_total_blocks              number;

    l_total_bytes               number;

    l_unused_blocks             number;

    l_unused_bytes              number;

    l_LastUsedExtFileId         number;

    l_LastUsedExtBlockId        number;

    l_LAST_USED_BLOCK           number;

    l_segment_space_mgmt        varchar2(255);

    l_unformatted_blocks number;

    l_unformatted_bytes number;

    l_fs1_blocks number; l_fs1_bytes number;

    l_fs2_blocks number; l_fs2_bytes number;

    l_fs3_blocks number; l_fs3_bytes number;

    l_fs4_blocks number; l_fs4_bytes number;

    l_full_blocks number; l_full_bytes number;                                                                                

                                      

    -- inline procedure to print out numbers nicely formatted

    -- with a simple label

    procedure p( p_label in varchar2, p_num in number )

    is

    begin

        dbms_output.put_line( rpad(p_label,40,'.') ||

                              to_char(p_num,'999,999,999,999') );

    end;

begin

   -- this query is executed dynamically in order to allow this procedure

   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES

   -- via a role as is customary.

   -- NOTE: at runtime, the invoker MUST have access to these two

   -- views!

   -- this query determines if the object is a ASSM object or not

   begin

      execute immediate

          'select ts.segment_space_management

             from dba_segments seg, dba_tablespaces ts

            where seg.segment_name      = :p_segname

              and (:p_partition is null or

                  seg.partition_name = :p_partition)

              and seg.owner = :p_owner

              and seg.tablespace_name = ts.tablespace_name'

             into l_segment_space_mgmt

            using p_segname, p_partition, p_partition, p_owner;

   exception

       when too_many_rows then

          dbms_output.put_line

          ( 'This must be a partitioned table, use p_partition => ');

          return;

   end;

 -- if the object is in an ASSM tablespace, we must use this API

   -- call to get space information, else we use the FREE_BLOCKS

   -- API for the user managed segments

   if l_segment_space_mgmt = 'AUTO'

   then

     dbms_space.space_usage

     ( p_owner, p_segname, p_type, l_unformatted_blocks,

       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,

       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,

       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

      p( 'Unformatted Blocks ', l_unformatted_blocks );

     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );

     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );

     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );

     p( 'FS4 Blocks (75-100)', l_fs4_blocks );

     p( 'Full Blocks        ', l_full_blocks );

  else

     dbms_space.free_blocks(

       segment_owner     => p_owner,

       segment_name      => p_segname,

       segment_type      => p_type,

       freelist_group_id => 0,

       free_blks         => l_free_blks);

                                                                                          

                                      

     p( 'Free Blocks', l_free_blks );

  end if;e                                                                                         

                                      

  -- and then the unused space API call to get the rest of the

  -- information

  dbms_space.unused_space

  ( segment_owner     => p_owner,

    segment_name      => p_segname,

    segment_type      => p_type,

    partition_name    => p_partition,

    total_blocks      => l_total_blocks,

    total_bytes       => l_total_bytes,

    unused_blocks     => l_unused_blocks,

    unused_bytes      => l_unused_bytes,

    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,

    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,

    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

                                                                                          

                                      

    p( 'Total Blocks', l_total_blocks );

    p( 'Total Bytes', l_total_bytes );

    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );

    p( 'Unused Blocks', l_unused_blocks );

    p( 'Unused Bytes', l_unused_bytes );

    p( 'Last Used Ext FileId', l_LastUsedExtFileId );

    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

    p( 'Last Used Block', l_LAST_USED_BLOCK );

end;

/

exec show_space( 'SYS_LOB0000081488C00033$$','VRD_OWNER','LOB');

We can also use below procedure as per Doc ID 386341.1   which i have also  copied in my belwo article 

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/09/how-to-determine-actual-size-of-lob.html

set serveroutput on

declare

TOTAL_BLOCKS number;

TOTAL_BYTES number;

UNUSED_BLOCKS number;

UNUSED_BYTES number;

LAST_USED_EXTENT_FILE_ID number;

LAST_USED_EXTENT_BLOCK_ID number;

LAST_USED_BLOCK number;

begin

dbms_space.unused_space('<owner>','<lob segment name>','LOB',

TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,

LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,

LAST_USED_BLOCK);

dbms_output.put_line('SEGMENT_NAME = <LOB SEGMENT NAME>');

dbms_output.put_line('-----------------------------------');

dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);

dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES);

dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);

dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES);

dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);

dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);

dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);

end;

/

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 

-- i preferred this way 

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

Restrictions :

The following statement will error with ORA-00997: illegal use of LONG datatype :

ALTER TABLE foo MOVE LOB(lobcol) STORE AS lobsegment (TABLESPACE new_tbsp STORAGE (new_storage));

The ALTER TABLE ... MOVE command cannot be used to move a table containing a LONG or LONG RAW column.  

Updating  Tablespace attribute after Lob Movement: 

If  Lob or its partitions are moved to another tablespace 

Doc ID 1388957.1

 

alter table <table name> move partition <table partition name> lob (<lob column name>) store as (tablespace <lob tablespace name>) parallel 8 ;

ALTER TABLE "table-name" MODIFY DEFAULT ATTRIBUTES FOR PARTITION "partition-name" TABLESPACE "new tablespace";

ALTER TABLE "table-name" MODIFY DEFAULT ATTRIBUTES TABLESPACE "new tablespace";

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)

How to Move LOB Data to Another Tablespace When the Table Also Contains a LONG Column (Doc ID 453186.1)

How To Reclaim Wasted Space on The Segment (Table, Index and LOB) and Tablespace Levels ( Doc ID 1682748.1 ) How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? ( Doc ID 1394613.1 ) How To Move Partitioned Tables to Another Tablespace When They Contain LOBs ( Doc ID 1388957.1 ) ***** https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_REDEFINITION.html#GUID-2BA796C4-8B4D-49B4-8A35-4C6F789CD374

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)