Restore Oracle database full backup which was encrypted in Production

 Hi Guy's,

Please find below steps to restore backup which has already encrypted in production.

############# CREATE pfile and add below directory structure to store encryption KEYS ############

/u01/software/prdwallet/tde >>>> But not mention TDE in pfile.

 Below Pfile used to open database.

*.compatible='19.0.0'

*.control_files='/u01/oradata/prd/control01.ctl'

*.db_block_size=8192

*.db_flashback_retention_target=2880

*.db_name='prd'

*.db_create_file_dest='/u01/oradata/prd/'

*.db_create_online_log_dest_1='/u01/oradata/prd/'

*.db_create_online_log_dest_2='/u01/oradata/prd/'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prdXDB)'

*.log_archive_dest_1='LOCATION=/u01/oradata/prd/arc'

*.log_archive_format='arch%t_%s_%r.arc'

*.open_cursors=2500

*.processes=1000

*.pga_aggregate_limit=4G

*.pga_aggregate_target=2G

*.sga_target=4G

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_retention=2289

*.undo_tablespace='UNDOTBS1'

*.wallet_root='/u01/oradata/prd/WALLET'

*.tde_configuration='KEYSTORE_CONFIGURATION=FILE'

*.wallet_root='/u01/software/prdwallet'

*.tde_configuration='KEYSTORE_CONFIGURATION=FILE'

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 4294960104 bytes

Fixed Size                  8881128 bytes

Variable Size             872415232 bytes

Database Buffers         3405774848 bytes

Redo Buffers                7888896 bytes

col host_name for a21

col startup_time for a19

select host_name,instance_name,status,to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from gv$instance;

HOST_NAME             INSTANCE_NAME    STATUS       STARTUP_TIME

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

sunread               prd          STARTED      10/27/2024 10:32:25

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors

ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;

Database altered.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 736353926 generated at 10/20/2024 20:10:32 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch2_1919_1145696945.arc

ORA-00280: change 736353926 for thread 2 is in sequence #1919

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 736353926 generated at 10/20/2024 20:10:30 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch1_3360_1145696945.arc

ORA-00280: change 736353926 for thread 1 is in sequence #3360

ORA-00308: cannot open archived log

'/u01/oradata/prd/arc/arch1_3360_1145696945.arc'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 7

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

ORA-01110: data file 1: '/u01/oradata/prd/system.261.1145696951'

>>>> Backup team Restored missed Archives and Recovery started again <<<<<<<<<<<<<<<

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 736353926 generated at 10/20/2024 20:10:32 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch2_1919_1145696945.arc

ORA-00280: change 736353926 for thread 2 is in sequence #1919

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 736353926 generated at 10/20/2024 20:10:30 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch1_3360_1145696945.arc

ORA-00280: change 736353926 for thread 1 is in sequence #3360

ORA-00283: recovery session canceled due to errors

ORA-28365: wallet is not open <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<-------Need wallet keys

ORA-01112: media recovery not started

########### Execute below commands in production and copy wallet keys to target #################

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/software/kk' IDENTIFIED BY ikea12345#

keystore altered.

SQL> administer key management merge keystore '+DATA/prd/WALLET/tde' identified by "ikea12345#" into existing keystore '/u01/software/kk' identified by "ikea12345#" with backup;

keystore altered.

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS   

CLOSED

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ikea12345#

keystore altered.

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS   

OPEN

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 736353926 generated at 10/20/2024 20:10:32 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch2_1919_1145696945.arc

ORA-00280: change 736353926 for thread 2 is in sequence #1919

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 736353926 generated at 10/20/2024 20:10:30 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch1_3360_1145696945.arc

ORA-00280: change 736353926 for thread 1 is in sequence #3360

ORA-00279: change 736360385 generated at 10/20/2024 20:12:21 needed for thread

ORA-00289: suggestion : /u01/oradata/prd/arc/arch2_1920_1145696945.arc

ORA-00280: change 736360385 for thread 2 is in sequence #1920

ORA-00278: log file '/u01/oradata/prd/arc/arch2_1919_1145696945.arc' no

longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/oradata/prd/arc/arch2_1920_1145696945.arc'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 7

SQL> alter database open resetlogs;

Database altered.

Happy Learning...

Encryption in Oracle 19c Database (TDE)

 Hi Guy's,

Please find the below steps to do encryption & decrypt at tablespace level using TDE. 

It has advanced security feature that encrypts sensitive data stored in tables, tablespaces, and database backups, protecting it from unauthorized access at the storage level. The encryption and decryption processes are managed automatically by the database.

Encryption Steps :-

Configure the Software Keystore Location. one time configuration 

Create directory at OS level  +DATA_DG/prddb/WALLET/tde 

mkdir -p wallet/tde

Need to reboot for effect this parameter, wallet root can be ASM or File system 

ALTER SYSTEM SET WALLET_ROOT='+DATA_DG/prddb/WALLET' SCOPE=SPFILE SID = '*';

No need to reboot

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*';

Change the database default encryption algorithm 

ALTER SYSTEM SET "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'AES256' SCOPE = BOTH SID = '*';

 Create the Software Keystore.

Creating a Password-Protected Software Keystore , user shoud has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege

SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY ikea12345#;

 Open the Keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ikea12345#;

Check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS

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

OPEN_NO_MASTER_KEY

Set the master encryption key by executing the following command:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ikea12345# WITH BACKUP USING 'prddbtest_BKP';

Check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS

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

OPEN

Creating an Auto-Login Software Keystore for auto start whenever database got restart and also its useful in dataguard environment for applying redologs after a role switch (Failover or Switchover)

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DG/prddb/WALLET/tde' IDENTIFIED BY ikea12345#;

Aautomatically encrypt future tablespaces that you will create.

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS;

Start Encrypting Tablespaces

ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE DATA_TS  ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE INDEX_TS ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

Optional Tablespaces:

 ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' ENCRYPT ; 

                                 *********** Done ************

• You can also Decrypt the tablespace but ensure you have enough space as this process creates new datafiles while decrypting.

Why Extra Space is Required:-

• Parallel Datafiles: During an ONLINE DECRYPT operation, Oracle creates a new, unencrypted version of every datafile in the target tablespace.
• Double Storage: For the duration of the conversion, the tablespace effectively occupies double its normal size because both the original encrypted files and the new decrypted files must exist simultaneously on disk.
• Data Consistency: This approach allows users to continue reading from and writing to the tablespace while it is being decrypted. Oracle manages the synchronization between the old and new files until the process is finished.
• Example :- If datafile size 10 Gb and data occupied in this datafile 1gb then how to ensure this size in enough to start decrypt.

The Space Rule for Online Decryption:-

• Allocation: It requires space equal to the allocated size (10 GB), not the occupied data (1 GB).
• Total Requirement: You must have at least 10 GB of free space on the storage mount or ASM diskgroup to hold the temporary duplicate.

Rollback or Decrypt 

• ALTER TABLESPACE USERS  ENCRYPTION ONLINE DECRYPTED ;
• ALTER TABLESPACE DATA_TS ENCRYPTION ONLINE DECRYPTED ;

Happy Learning

Label the raw LUNS in oracle database for Solaris environment by using AFD

  Hi Guy's,

Please find below steps to label the raw LUNS in Solaris environment by using AFD utility and below scenario is where you have storage level replication not dataguard.

• Query the present LUN's from both Nodes.

Node1:-

oracle@kdbracc1:/dev/capexhalaASM> ls -ltr capexhaladata*

lrwxrwxrwx   1 root     root          67 Aug 23  2022 capexhaladata1 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030443741:g,raw

lrwxrwxrwx   1 root     root          67 Aug 23  2022 capexhaladata2 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030443742:g,raw

lrwxrwxrwx   1 root     root          67 Dec 12  2022 capexhaladata3 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030444443:g,raw

lrwxrwxrwx   1 root     root          67 Jun 22  2023 capexhaladata4 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030463246:g,raw

lrwxrwxrwx   1 root     root          67 Feb 14 12:41 capexhaladata5 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030354343:g,raw

lrwxrwxrwx   1 root     root          67 Feb 14 12:42 capexhaladata6 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030354344:g,raw

Node2:-

oracle@kdbracc2:/dev/capexhalaASM> ls -ltr capexhaladata*

lrwxrwxrwx   1 root     root          67 Aug 23  2022 capexhaladata1 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030443741:g,raw

lrwxrwxrwx   1 root     root          67 Aug 23  2022 capexhaladata2 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030443742:g,raw

lrwxrwxrwx   1 root     root          67 Dec 12  2022 capexhaladata3 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030444443:g,raw

lrwxrwxrwx   1 root     root          67 Jun 22  2023 capexhaladata4 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030463246:g,raw

lrwxrwxrwx   1 root     root          67 Feb 14 12:42 capexhaladata5 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030354343:g,raw

lrwxrwxrwx   1 root     root          67 Feb 14 12:42 capexhaladata6 -> ../../devices/scsi_vhci/ssd@XXX0000970000297000221533030354344:g,raw

•  Query the existing labels.

oracle@kdbracc2:/dev/capexhalaASM> cd /var/opt/oracle/oracleafd/disks

oracle@kdbracc2:/var/opt/oracle/oracleafd/disks> ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata1

•  Label the raw LUN's using AFD_LABEL utility.

oracle@kdbracc1:/dev/capexhalaASM> /u01/app/19.0.0/grid/bin/asmcmd afd_label 'capexhaladata5' '/dev/capexhalaASM/capexhaladata5'

oracle@kdbracc1:/dev/capexhalaASM>  /u01/app/19.0.0/grid/bin/asmcmd afd_label 'capexhaladata6' '/dev/capexhalaASM/capexhaladata6'

oracle@kdbracc1:/dev/capexhalaASM>  cd /var/opt/oracle/oracleafd/disks

oracle@kdbracc1:/var/opt/oracle/oracleafd/disks> ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 04:43 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 04:43 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 04:43 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 04:43 capexhaladata1

-rw-rw-r--   1 oracle   dba           31 Feb 15 13:02 capexhaladata5

-rw-rw-r--   1 oracle   dba           31 Feb 15 13:03 capexhaladata6

• Add the Labeled LUN's to the diskgroup.

SQL> alter diskgroup capexhaladata_DG rebalance power 8;

Diskgroup altered.

SQL> ALTER DISKGROUP capexhaladata_DG ADD DISK 'AFD:capexhaladata5';

Diskgroup altered.

SQL> ALTER DISKGROUP capexhaladata_DG ADD DISK 'AFD:capexhaladata6';

Diskgroup altered.

SQL> select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number and dg.name like '%AP%' group by dg.name order by dg_name;   

DG_NAME                                      TOTAL_GB       FREE_GB

CAPEXHALA_DG                 1798           676

CAPEXHALA_FLASHBACK_DG       399                  194

CAPEXHALA_REDO01_DG                79                  63

CAPEXHALA_REDO02_DG                79                  63

• EXECUTE AFD_SCAN ON NODE2 

• New labels not available before SCAN.

oracle@kdbracc2:/var/opt/oracle/oracleafd/disks> ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata1

•  After executing AFD_SCAN ON NODE2 

oracle@kdbracc2:~> /u01/app/19.0.0/grid/bin/asmcmd afd_scan

oracle@kdbracc2:~> cd /var/opt/oracle/oracleafd/disks

oracle@kdbracc2:/var/opt/oracle/oracleafd/disks> ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 05:33 capexhaladata1

-rw-rw-r--   1 oracle   dba           31 Feb 15 13:02 capexhaladata5 <<<<<--- After scan

-rw-rw-r--   1 oracle   dba           31 Feb 15 13:03 capexhaladata6 <<<<<--- After scan

• Email OS team to stop replication.
• Login to DRC and below output is before running AFD_SCAN
• Setting Environment for GRID 19c

oracle@drckdbracc1:~] cd /var/opt/oracle/oracleafd/disks

oracle@drckdbracc1:/var/opt/oracle/oracleafd/disks] ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata1

•  Execute scan and see the nee labels.

oracle@drckdbracc1:/u01/app/19.0.0/grid]  asmcmd afd_scan

oracle@drckdbracc1:/u01/app/19.0.0/grid] cd /var/opt/oracle/oracleafd/disks

oracle@drckdbracc1:/var/opt/oracle/oracleafd/disks]  ls -ltr capexhaladata*

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata4

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata3

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata2

-rw-rw-r--   1 oracle   dba           31 Jan 19 06:38 capexhaladata1

-rw-r--r--   1 oracle   dba           31 Feb 15 14:00 capexhaladata6 <<<<<<<< New Labels Visible

-rw-r--r--   1 oracle   dba           31 Feb 15 14:00 capexhaladata5 <<<<<<< New Labels Visible

******** Happy Learning **********

How to fetch historical execution plans for specific SQL in oracle

 Hi Guy's,

Please find below script which retrieves historical execution data and execution plans for a specific SQL statement using the Automatic Workload Repository (AWR) views.

SQL Performance History Report for an Oracle Database. It 

set echo off

set feedback off timing off verify off pagesize 100 linesize 200 recsep off echo off

set serveroutput on size 1000000 format wrapped trimout on trimspool on

col phv heading "Plan|Hash Value"

col snap_time format a12 truncate heading "Snapshot|Time"

col execs format 999,990 heading "Execs"

col lio_per_exec format 999,999,999,990.00 heading "Avg LIO|Per Exec"

col pio_per_exec format 999,999,999,990.00 heading "Avg PIO|Per Exec"

col cpu_per_exec format 999,999,999,990.00 heading "Avg|CPU (secs)|Per Exec"

col ela_per_exec format 999,999,999,990.00 heading "Avg|Elapsed (secs)|Per Exec"

col sql_text format a64 heading "Text of SQL statement"

clear breaks computes

ttitle off

btitle off

accept V_SQL_ID prompt "Enter the SQL_ID: "

accept V_NBR_DAYS prompt "Enter number of days (backwards from this hour) to report (default: ALL): "

variable v_nbr_days number

spool sqlhistory_&&V_SQL_ID

declare

cursor get_phv(in_sql_id in varchar2, in_days in integer)

is

select ss.plan_hash_value,

min(s.begin_interval_time) min_time,

max(s.begin_interval_time) max_time,

min(s.snap_id) min_snap,

max(s.snap_id) max_snap,

sum(ss.executions_delta) sum_execs,

sum(ss.disk_reads_delta) sum_disk_reads,

sum(ss.buffer_gets_delta) sum_buffer_gets,

sum(ss.cpu_time_delta)/1000000 sum_cpu_time,

sum(ss.elapsed_time_delta)/1000000 sum_elapsed_time

from dba_hist_sqlstat ss,

dba_hist_snapshot s

where ss.dbid = s.dbid

and ss.instance_number = s.instance_number

and ss.snap_id = s.snap_id

and ss.sql_id = in_sql_id

/* and ss.executions_delta > 0 */

and s.begin_interval_time >= sysdate-in_days

group by ss.plan_hash_value

order by sum_elapsed_time desc;

        --

cursor get_xplan(in_sql_id in varchar2, in_phv in number)

is

select plan_table_output

from table(dbms_xplan.display_awr(in_sql_id, in_phv, null, 'ALL -ALIAS'));

--

v_prev_plan_hash_value number := -1;

v_text_lines number := 0;

v_errcontext varchar2(100);

v_errmsg varchar2(100);

v_display_sql_text boolean;

--

begin

--

v_errcontext := 'query NBR_DAYS from DUAL';

select decode('&&V_NBR_DAYS','',10,to_number(nvl('&&V_NBR_DAYS','10')))

into :v_nbr_days

from dual;

--

v_errcontext := 'open/fetch get_phv';

for phv in get_phv('&&V_SQL_ID', :v_nbr_days) loop

--

if get_phv%rowcount = 1 then

--

dbms_output.put_line('+'||

rpad('-',12,'-')||

rpad('-',10,'-')||

rpad('-',10,'-')||

rpad('-',12,'-')||

rpad('-',15,'-')||

rpad('-',15,'-')||

rpad('-',12,'-')||

rpad('-',12,'-')||'+');

dbms_output.put_line('|'||

rpad('Plan HV',12,' ')||

rpad('Min Snap',10,' ')||

rpad('Max Snap',10,' ')||

rpad('Execs',12,' ')||

rpad('LIO',15,' ')||

rpad('PIO',15,' ')||

rpad('CPU',12,' ')||

rpad('Elapsed',12,' ')||'|');

dbms_output.put_line('+'||

rpad('-',12,'-')||

rpad('-',10,'-')||

rpad('-',10,'-')||

rpad('-',12,'-')||

rpad('-',15,'-')||

rpad('-',15,'-')||

rpad('-',12,'-')||

rpad('-',12,'-')||'+');

--

end if;

--

dbms_output.put_line('|'||

rpad(trim(to_char(phv.plan_hash_value)),12,' ')||

rpad(trim(to_char(phv.min_snap)),10,' ')||

rpad(trim(to_char(phv.max_snap)),10,' ')||

rpad(trim(to_char(phv.sum_execs,'999,999,990')),12,' ')||

rpad(trim(to_char(phv.sum_buffer_gets,'999,999,999,990')),15,' ')||

rpad(trim(to_char(phv.sum_disk_reads,'999,999,999,990')),15,' ')||

rpad(trim(to_char(phv.sum_cpu_time,'999,990.00')),12,' ')||

rpad(trim(to_char(phv.sum_elapsed_time,'999,990.00')),12,' ')||'|');

--

v_errcontext := 'fetch/close get_phv';

--

end loop;

dbms_output.put_line('+'||

rpad('-',12,'-')||

rpad('-',10,'-')||

rpad('-',10,'-')||

rpad('-',12,'-')||

rpad('-',15,'-')||

rpad('-',15,'-')||

rpad('-',12,'-')||

rpad('-',12,'-')||'+');

--

v_errcontext := 'open/fetch get_phv';

for phv in get_phv('&&V_SQL_ID', :v_nbr_days) loop

--

if v_prev_plan_hash_value <> phv.plan_hash_value then

--

v_prev_plan_hash_value := phv.plan_hash_value;

v_display_sql_text := FALSE;

--

v_text_lines := 0;

v_errcontext := 'open/fetch get_xplan';

for s in get_xplan('&&V_SQL_ID', phv.plan_hash_value) loop

--

if v_text_lines = 0 then

dbms_output.put_line('.');

dbms_output.put_line('========== PHV = ' ||

phv.plan_hash_value ||

'==========');

dbms_output.put_line('First seen from "'||

to_char(phv.min_time,'MM/DD/YY HH24:MI:SS') ||

'" (snap #'||phv.min_snap||')');

dbms_output.put_line('Last seen from  "'||

to_char(phv.max_time,'MM/DD/YY HH24:MI:SS') ||

'" (snap #'||phv.max_snap||')');

dbms_output.put_line('.');

dbms_output.put_line(

rpad('Execs',15,' ')||

rpad('LIO',15,' ')||

rpad('PIO',15,' ')||

rpad('CPU',15,' ')||

rpad('Elapsed',15,' '));

dbms_output.put_line(

rpad('=====',15,' ')||

rpad('===',15,' ')||

rpad('===',15,' ')||

rpad('===',15,' ')||

rpad('=======',15,' '));

dbms_output.put_line(

rpad(trim(to_char(phv.sum_execs,'999,999,999,990')),15,' ')||

rpad(trim(to_char(phv.sum_buffer_gets,'999,999,999,990')),15,' ')||

rpad(trim(to_char(phv.sum_disk_reads,'999,999,999,990')),15,' ')||

rpad(trim(to_char(phv.sum_cpu_time,'999,999,990.00')),15,' ')||

rpad(trim(to_char(phv.sum_elapsed_time,'999,999,990.00')),15,' '));

dbms_output.put_line('.');

end if;

--

if v_display_sql_text = FALSE and

   s.plan_table_output like 'Plan hash value: %' then

--

v_display_sql_text := TRUE;

--

end if;

--

if v_display_sql_text = TRUE then

--

dbms_output.put_line(s.plan_table_output);

--

end if;

--

v_text_lines := v_text_lines + 1;

--

end loop;

--

end if;

--

v_errcontext := 'fetch/close get_phv';

--

end loop;

--

exception

when others then

v_errmsg := sqlerrm;

raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);

end;

/

break on report

compute sum of execs on report

compute avg of lio_per_exec on report

compute avg of pio_per_exec on report

compute avg of cpu_per_exec on report

compute avg of ela_per_exec on report

ttitle center 'Summary Execution Statistics Over Time'

select to_char(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,

ss.executions_delta execs,

ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec,

ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec,

(ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,

(ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec

from  dba_hist_snapshot s,

dba_hist_sqlstat ss

where ss.dbid = s.dbid

and ss.instance_number = s.instance_number

and ss.snap_id = s.snap_id

and ss.sql_id = '&&V_SQL_ID'

/* and ss.executions_delta > 0 */

and s.begin_interval_time >= sysdate - :v_nbr_days

order by s.snap_id;

clear breaks computes

break on phv skip 1 on report

compute sum of execs on phv

compute avg of lio_per_exec on phv

compute avg of pio_per_exec on phv

compute avg of cpu_per_exec on phv

compute avg of ela_per_exec on phv

ttitle center 'Per-Plan Execution Statistics Over Time'

select ss.plan_hash_value phv,

to_char(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,

ss.executions_delta execs,

ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec,

ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec,

(ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,

(ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec

from  dba_hist_snapshot s,

dba_hist_sqlstat ss

where ss.dbid = s.dbid

and ss.instance_number = s.instance_number

and ss.snap_id = s.snap_id

and ss.sql_id = '&&V_SQL_ID'

/* and ss.executions_delta > 0 */

and s.begin_interval_time >= sysdate - :v_nbr_days

order by ss.plan_hash_value, s.snap_id;

clear breaks computes

spool off

set verify on echo on feedback on

ttitle off

************ Happy Learning ***************

How to create a SQL baseline in oracle

 Hi Guy’s,

Please check the below steps to create SQL baseline.

Find Good has value from AWR.

Mentioned the hash value with sql-id in below procedure to create SQL handler and plan name.

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8fc16qy3tqd3y',plan_hash_value => 862757774);

PL/SQL procedure successfully completed.

 

Check the sqlhandler and plan created

set line 999

col CREATED format a30

select CREATOR,CREATED,sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where to_char(created,'dd/mm/yyyy') = '07/03/2024'

order by created desc;

 

CREATOR                          CREATED                    SQL_HANDLE                      PLAN_NAME                   ENA    ACC    FIX

SYS     07-MAR-24 05.31.36.046408 PM    SQL_a31118028547b7bb   SQL_PLAN_a648s0a2ngdxv5648d7fa   YES    YES    NO

Fix Baseline

var v_num number;

 exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_a31118028547b7bb',plan_name => 'SQL_PLAN_a648s0a2ngdxv5648d7fa', attribute_name=> 'FIXED',attribute_value  => 'YES');

 PL/SQL procedure successfully completed.

Check SQL-ID fixed or not.

SQL> select CREATOR,CREATED,sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where to_char(created,'dd/mm/yyyy') = '07/03/2024' order by created desc; 

CREATOR                          CREATED                          SQL_HANDLE                               PLAN_NAME                                  ENA   ACC   FIX

SYS               07-MAR-24 05.31.36.046408 PM   SQL_a31118028547b7bb    SQL_PLAN_a648s0a2ngdxv5648d7fa    YES YES YES <<--Fixed

 Ø    Go the OEM in SQL SEARCH mention the sql-id and check baseline is showing or not.

************ Happy Learning **************

HOW TO GATHER STATS OF A TABLE

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName           => 'FCM',
TabName           => 'CASH_FACT',
Estimate_Percent  => 10,
Method_Opt        => 'FOR ALL COLUMNS SIZE 1',
Degree            => 16,
Cascade           => TRUE,
No_Invalidate  => FALSE);
END;
/