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;
/

ASM Diskgroup Total Size, Free Size with Percentage

 

SQL>  select name,total_mb,free_mb, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;

              NAME          TOTAL SIZE(GB)     FREE SIZE (GB)      %Free

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

               CRS                        19,085               18,689                   97.925072

               DATA                     19,085               17,397                   91.1553576

               FRA                        23,846               23,751                   99.6016103

              ARCH                     23,846               23,705                    99.4087059

Enjoy Learning.....

Hot to Extract All Triggers Of a Particular Table in Oracle

 

Hi Guy's,

You can easily extract the DDL of  triggers available in your table.

 SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SQL> SELECT DBMS_METADATA.GET_DDL('TRIGGER', TRIGGER_NAME) FROM USER_TRIGGERS where table_name=' TABLE_NAME';

Enjoy....

How to find who locked the User in Oracle

Hi Guy's,
Pleas check this query output,
How to find Machine,Terminal of the user from where it get locked.


SQL> select os_username,username,userhost,terminal,timestamp,action_name from dba_audit_session where username='DRDISA';


SQL> select os_username,username,userhost,terminal,timestamp,action_name from dba_audit_session where username='DRDISA' order by timestamp desc;





Happy Reading....