Oracle Orion and IO Calibrate -- Check IO efficiency

 

Oracle  Orion - ORacle I/O Numbers

Since Oracle 11g, the Oracle ORION package has bee shipped within the Oracle Database and Grid Infrastructure binaries $ORACLE_HOME/bin/orion and requires no downloads and only minimal set-up, so is really easy to be get started quickly.

NOTE: orion requires you to provide a full path e.g. $ORACLE_HOME/bin/orion , if you try to call without providing a full path orion will return an ORA-56727 error.

Setting-Up Orion

Now we have prepared and presented our storage we can configure Orion, by default Orion looks for orion.lun, you can also use alternative configuration files by using the testname switch. (Example bellow)

[oracle@z-oracle orion_test]$ cat orion.lun 

/dev/mapper/orion-vol1

/dev/mapper/orion-vol2

/dev/mapper/orion-vol3

/dev/mapper/orion-vol4

/dev/mapper/orion-vol5

Orion support 5 types of workload tests Simple, Normal, Advanced, DSS and OLTP. (example output from orion -help

   simple   - Tests random small (8K) IOs at various loads,

              then random large (1M) IOs at various loads.

   normal   - Tests combinations of random small (8K) IOs and 

              random large (1M) IOs.

   advanced - Tests the workload specified by the user 

              using optional parameters.

   dss      - Tests with random large (1M) IOs at increasing 

              loads to determine the maximum throughput.

   oltp     - Tests with random small (8K) IOs at increasing 

              loads to determine the maximum IOPS.

For a preliminary set of data

 -run simple 

For a basic set of data

 -run normal 

To evaluate storage for an OLTP database

 -run oltp 

To evaluate storage for a data warehouse

 -run dss 

To generate combinations of 32KB and 1MB reads to random locations

 -run advanced 

 -size_small 32 -size_large 1024 -type rand 

 -matrix detailed

To generate multiple sequential 1MB write streams, simulating RAID0 striping

 -run advanced 

 -simulate RAID0 -write 100 -type seq

 -matrix col -num_small 0

# $ORACLE_HOME/bin/orion -run simple -testname simpletest

# $ORACLE_HOME/bin/orion -run advanced -testname advancedtest -matrix max -num_small 4 -num_large 4 -size_large 512

# $ORACLE_HOME/bin/orion -run oltp -testname oltp_write -write 20

./orion_linux_em64t -run advanced -testname orion1 -num_disks 1 -write 0 -simulate concat -matrix detailed

./orion_linux_em64t -run advanced -testname orion3 -num_disks 3 -write 0 -simulate concat -matrix detailed

%> . ./orion -run oltp -testname test -write 60


I can see TWP was released in 2009 and not updated after that. 
https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=810394.1&attachid=810394.1:LTP&clickstream=yes

Please make sure that Orion tool only interact with RAW disk provided by Infra team. If we use write option and provide ASM disk to orion it will corrupt ASM disk. 



#################################
ORION
#################################

Oracle Orion tool:

1) For Predicting the performance of an Oracle database without having to install Oracle or create a database
2) Orion for Oracle Administrators: Oracle administrators can use Orion to evaluate and compare different storage arrays, based on the expected
workloads.
3) Unlike other I/O calibration tools, Oracle Orion is expressly designed for simulating Oracle database I/O workloads using the same
   I/O software stack as Oracle.
4)  Orion can also simulate the effect of striping performed by Oracle Automatic Storage Management
5) Orion can run tests using different I/O loads to measure performance metrics such as MBPS, IOPS, and I/O latency Run Orion when the storage is idle

ORION (ORacle IO Numbers) mimics the type of I/O performed by Oracle databases, 
which allows you to measure I/O performance for storage systems without actually installing Oracle.
It used to be available to download for a number of platforms from OTN, but that download is no longer available. 
Instead, it is included in the "$ORACLE_HOME/bin" directory of Grid Infrastructure (GI) and database installations.
Doing the run using the "normal" option can take a long time, so you may want to try using the "basic" option first.

Orion Command Line Samples
The following provides sample Orion commands for different types of I/O workloads:
To evaluate storage for an OLTP database:
-run oltp
To evaluate storage for a data warehouse:
-run dss
For a basic set of data:
-run normal
To understand your storage performance with read-only, small and large random I/O workload:
$ orion -run simple 
To understand your storage performance with a mixed small and large random I/O workload:
$ orion -run normal 
To generate combinations of 32KB and 1MB reads to random locations:
$ orion -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed
To generate multiple sequential 1 MB write streams, simulating 1 MB RAID-0 stripes:
$ orion -run advanced -simulate raid0 -stripe 1024 -write 100 -type seq -matrix col -num_small 0
To generate combinations of 32 KB and 1 MB reads to random locations:
 -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed
To generate multiple sequential 1 MB write streams, simulating RAID0 striping:
 -run advanced -simulate raid0 -write 100 -type seq -matrix col -num_small 0




Create a file to hold the LUN configuration. In this case I will call my test “test”, so my LUN configuration file must be called “test.lun”. It is assumed it is present in the current directory. The file should contain a list of the luns used in the test.
# cat test.lun
/luns/lun1
/luns/lun2
/luns/lun3
/luns/lun4
If you don’t specify a test name, the utility assumes the test is called “orion” and looks for the presence of the “orion.lun” file.
# $ORACLE_HOME/bin/orion -run normal -testname test




#################################
Calibrate I/O 
#################################

Introduced in Oracle Database 11g Release 1, the CALIBRATE_IO procedure gives an idea of the capabilities of the storage system from within Oracle. 
There are a few restrictions associated with the procedure.
The procedure must be called by a user with the SYSDBA priviledge.
TIMED_STATISTICS must be set to TRUE, which is the default when STATISTICS_LEVEL is set to TYPICAL.
Datafiles must be accessed using asynchronous I/O. This is the default when ASM is used.


SELECT d.name,
       i.asynch_io
FROM   v$datafile d,
       v$iostat_file i
WHERE  d.file# = i.file_no
AND    i.filetype_name  = 'Data File';



SET SERVEROUTPUT ON
DECLARE
  l_latency  PLS_INTEGER;
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
BEGIN
   DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, 
                                       max_latency        => 20,
                                       max_iops           => l_iops,
                                       max_mbps           => l_mbps,
                                       actual_latency     => l_latency);
 
  DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops);
  DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps);
  DBMS_OUTPUT.put_line('Latency  = ' || l_latency);
END;
/






Calibration runs can be monitored using the V$IO_CALIBRATION_STATUS view.
SET LINESIZE 100
COLUMN start_time FORMAT A20
COLUMN end_time FORMAT A20
SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time,
       TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time,
       max_iops,
       max_mbps,
       max_pmbps,
       latency,
       num_physical_disks AS disks
FROM   dba_rsrc_io_calibrate;






#################################
Using system views 
#################################



DECLARE
run_duration number := 3600;
capture_gap number := 5;
loop_count number :=run_duration/capture_gap;
rdio number;
wtio number;
prev_rdio number :=0;
prev_wtio number :=0;
rdbt number;
wtbt number;
prev_rdbt number;
prev_wtbt number;
BEGIN s
FOR i in 1..loop_count LOOP
SELECT SUM(value) INTO rdio from gv$sysstat
WHERE name ='physical read total IO requests';
SELECT SUM(value) INTO wtio from gv$sysstat
WHERE name ='physical write total IO requests';
SELECT SUM(value)* 0.000008 INTO rdbt from gv$sysstat
WHERE name ='physical read total bytes';
SELECT SUM(value* 0.000008) INTO wtbt from gv$sysstat
WHERE name ='physical write total bytes';
IF i > 1 THEN
INSERT INTO peak_iops_measurement (capture_timestamp,
total_read_io, total_write_io, total_io, total_read_bytes,
total_write_bytes, total_bytes)
VALUES (sysdate,(rdio-prev_rdio)/5,(wtio-prev_wtio)/5,((rdioprev_rdio)/5)+((wtio-prev_wtio))/5,(rdbt-prev_rdbt)/5,(wtbtprev_wtbt)/5,((rdbt-prev_rdbt)/5)+((wtbt-prev_wtbt))/5);
END IF;
prev_rdio := rdio;
prev_wtio := wtio;
prev_rdbt := rdbt;
prev_wtbt := wtbt;
DBMS_LOCK.SLEEP(capture_gap);
ND LOOP;
COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
END;
/



SELECT SUM(value) , name  from gv$sysstat  WHERE name like 'physical read%'  or name like 'physical write%' group by name  ;
 


#################################
SLOB
#################################

Oracle Database Smon recovery -- Disable , enable and Tuning Rollback

 

Most of time we face performance issues , we miss to  check if there  is rollback ongoing . However we come across many situations where rollback is ongoing which impacts database performance .

We can speed up and slowdown rollback seeing business hours .

 If Undo tablespace is used up and rollback is slow , we can add new undo tablespace and change default undo tablespace 

If smon recovery is causing performance issues we may opt to disable smon recovery temporarily and re-enable back after business hours .  Similarly we can speed up and reduce speed of smon recovery 

The message ‘Waiting for SMON to disable tx recovery’ will be posted in the alert log as well.

After killing the shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by examining v$transaction.used_ublk how the rollback procedure proceeds.

How to check Smon recovery : 

set linesize 100

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

  select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
   from x$ktuxe
   where ktuxecfl = 'DEAD';
  
 
select sql_text
from v$sqlarea
where address = (select sql_address from v$session where sid in (select SID 
from v$transaction,dba_rollback_segs,v$session        
where SADDR=SES_ADDR and
      XIDUSN=SEGMENT_ID and
      flag=7811));
 
 

 set linesize 100 

  alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

  select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", 

             decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 

              "Estimated time to complete" 

   from v$fast_start_transactions; 

 alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

 SELECT usn, state, undoblockstotal “Total”, undoblocksdone “Done”,

undoblockstotal-undoblocksdone “ToDo”,

DECODE(cputime,0,’unknown’,SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Finish at”

FROM v$fast_start_transactions;

SELECT r.NAME “RB Segment Name”, dba_seg.size_mb,

DECODE(TRUNC(SYSDATE – LOGON_TIME), 0, NULL, TRUNC(SYSDATE – LOGON_TIME) || ‘ Days’ || ‘ + ‘) ||

TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), ‘SSSSS’), ‘HH24:MI:SS’) LOGON,

v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,

v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,

v$session.PROGRAM, v$session.module, action

FROM v$lock l, v$process p, v$rollname r, v$session,

(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments

WHERE segment_type = ‘TYPE2 UNDO’ ORDER BY bytes DESC) dba_seg

WHERE l.SID = p.pid(+) AND

v$session.SID = l.SID AND

TRUNC (l.id1(+)/65536)=r.usn AND

l.TYPE(+) = ‘TX’ AND

l.lmode(+) = 6

AND r.NAME = dba_seg.segment_name

–AND v$session.username = ‘SYSTEM’

–AND status = ‘INACTIVE’

ORDER BY size_mb DESC;

SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, ‘None’) orauser,

s.program,

r.name undoseg,

t.used_ublk * TO_NUMBER(x.value)/1024||’K’ “Undo”

FROM sys.v_$rollname r,

sys.v_$session s,

sys.v_$transaction t,

sys.v_$parameter x

WHERE s.taddr = t.addr

AND r.usn = t.xidusn(+)

AND x.name = ‘db_block_size’;

SELECT state,

       UNDOBLOCKSDONE,

       UNDOBLOCKSTOTAL,

       UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100

FROM gv$fast_start_transactions;

ALTER SESSION

SET nls_date_format='dd-mon-yyyy hh24:mi:ss';

SELECT usn,

       state,

       undoblockstotal "Total",

       undoblocksdone "Done",

       undoblockstotal-undoblocksdone "ToDo",

       decode(cputime, 0, 'unknown', sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"

FROM v$fast_start_transactions;

SELECT a.sid,

       a.username,

       b.xidusn,

       b.used_urec,

       b.used_ublk

FROM v$session a,

     v$transaction b

WHERE a.saddr=b.ses_addr

ORDER BY 5 DESC;

Disable parallel rollback / smon recovery
 

-- to check if parallel smon recovery is in progress
select * from v$fast_start_servers;  
select pid, spid from v$process where pid in ( select pid from v$fast_start_servers);
 

-- set PID of smon

- not be killed . main smon  
select pid, program from v$process where program like '%SMON%'; -

 -- disable SMON transaction rollback/recovery
 oradebug setorapid 10  

oradebug event 10513 trace name context forever, level 2

-- kill parallel  smon processes if its exists
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
 where pid in (select pid from v$fast_start_servers)) p
 where s.paddr=p.addr;
 

-- disable parallel smon
alter system set fast_start_parallel_rollback=false;  
select * from v$fast_start_servers;
select pid from v$fast_start_servers ;
show parameter fast
 

-- enable  SMON transaction rollback/recovery
oradebug event 10513 trace name context off ; 
 

 
Speed up smon recovery
 
1)
select * from v$fast_start_servers;
If all the processes are in state RECOVERING, then you can benefit from adding more processes:
  
SQL> alter system set fast_start_parallel_rollback = high;
 
This will create parallel servers as much as 4 times the number of CPUs.
 
 
2)
Increase the parameter ‘_cleanup_rollback_entries’ to 400. The default is 100.
This parameter cannot be changed dynamically, so in order to change this the database will need to be restarted. As outlined above this is not advisable  and basically needs to be done when no alternative. This needs to be done only on suggestion of  Oracle support 

 

Rollback used by session :

Before  killing session we can check  undo  blocks  hold  by session  to   get estimation of  rollback 

select value rlbk from v$sysstat where name='user rollbacks';

 select sql_text

from v$sqlarea

where address = (select sql_address from v$session where sid in (select SID

from v$transaction,dba_rollback_segs,v$session

where SADDR=SES_ADDR and

XIDUSN=SEGMENT_ID and

flag=7811));

select sysdate,sql_text

from v$sqlarea

where address in (select sql_address

from v$session where sid in (select SID

from v$transaction,dba_rollback_segs,v$session

where SADDR=SES_ADDR and

XIDUSN=SEGMENT_ID and

bitand(flag,power(2,7))<>0));

SELECT username, terminal, osuser,

       t.start_time, r.name, t.used_ublk "ROLLB BLKS",

       DECODE(t.SPACE, 'YES', 'SPACE TX',

          DECODE(t.recursive, 'YES', 'RECURSIVE TX',

             DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)

       )) status

FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s

WHERE t.xidusn = r.usn

  AND t.ses_addr = s.saddr

/

References :

 Note 238507.1: How to Disable Parallel Transaction Recovery When Parallel Txn Re very is Active 

Note 144332.1   Parallel Rollback may hang database, Parallel query servers get 100% cpu

How to Install and Configure ASM Filter Driver -- For Oracle Database

From Oracle 12.1.0.2 Start , have access to asmfd To replace udev Under the rules asm Disk device binding , At the same time, he also has the ability to filter illegal IO Characteristics of operation .

Check that the operating system version supports ASMFD, You can use the following code ：

acfsdriverstate -orahome $ORACLE_HOME supported

 In redhat or centos 7.4 or above, you need to upgrade kmod to enable AFD

If we  are planning to Upgrade   from Asmlib to Afd   we can  refer  (Doc ID 2172754.1)

 

Installation Steps : 

Step  1> Stop Crs

Step 2> Configure AFD (ASM Filter Driver)

 To check whether the operating system version supports ASMFD, you can use the following code:

 

acfsdriverstate -orahome $ORACLE_HOME supported

  asmcmd afd_configure

Step 3> Configure Disk Discovery for AFD

Modify the below mentioned files as shown

 cat /etc/afd.conf

afd_diskstring='/dev/xvd*'

afd_filtering=enable

 cat /etc/oracleafd.conf

afd_diskstring='/dev/xvd*'

afd_filtering=enable

At this point AFD is configured you can verify the status as below

  asmcmd afd_state

ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'wdtest05'

I would recommend to restart acfs before you proceed

 acfsload stop

 acfsload start

 lsmod | grep acfs

Or by using dsset  

[root@rac1 ~]# asmcmd dsset '/dev/asm*','AFD:*'

[root@rac1 ~]# $ORACLE_HOME/bin/asmcmd dsget   

parameter:/dev/asm*, AFD:*

profile:/dev/asm*,AFD:*

Step5> Label the Disks using AFD to be used by ASM

#asmcmd afd_label DATA01 /dev/xvhg

#asmcmd afd_label FRA01 /dev/xvhh

#asmcmd afd_label REDO01 /dev/xvhi

#asmcmd afd_lsdsk

asmcmd afd_dsget 

Checking Storage Multipath information :

[root@rac1 yum.repos.d]# ll /dev/mapper/mpath*

lrwxrwxrwx 1 root root 7 Feb 15 17:18 /dev/mapper/mpathc -> ../dm-1

lrwxrwxrwx 1 root root 7 Feb 15 17:18 /dev/mapper/mpathd -> ../dm-0

The multipath devices mpathc and mpathd are used here

[root@rac2 ~]# multipath -ll

mpathd (14f504e46494c45526147693538302d577037452d39596459) dm-1 OPNFILER,VIRTUAL-DISK    

size=30G features='0' hwhandler='0' wp=rw

|-+- policy='service-time 0' prio=1 status=active

| `- 34:0:0:1 sdc 8:32 active ready running

|-+- policy='service-time 0' prio=1 status=enabled

| `- 35:0:0:1 sde 8:64 active ready running

|-+- policy='service-time 0' prio=1 status=enabled

| `- 36:0:0:1 sdg 8:96 active ready running

`-+- policy='service-time 0' prio=1 status=enabled

  `- 37:0:0:1 sdi 8:128 active ready running

mpathc (14f504e46494c45524f444c7844412d717a557a2d6b7a6752) dm-0 OPNFILER,VIRTUAL-DISK    

size=40G features='0' hwhandler='0' wp=rw

|-+- policy='service-time 0' prio=1 status=active

| `- 34:0:0:0 sdb 8:16 active ready running

|-+- policy='service-time 0' prio=1 status=enabled

| `- 35:0:0:0 sdd 8:48 active ready running

|-+- policy='service-time 0' prio=1 status=enabled

| `- 36:0:0:0 sdf 8:80 active ready running

`-+- policy='service-time 0' prio=1 status=enabled1

  `- 37:0:0:0 sdh 8:112 active ready running

Moving disk from  one  diskgroup to another when using asm filter and multipath : 

1) Drop disk  from asm  ( example ASM_DISKGROUP1_11  ) 

2)  afdtool -delete  ASM_DISKGROUP1_11    ( on one node by  Unix team using root ) 

3)  afdboot -scandisk  / -rescan   ( on all nodes  by  Unix team using root ) 

4)  remap ASM_DISKGROUP1_11 to  new disk name in  /etc/multipath.conf  , relabel  disk  and reload       multipath      ( on all nodes  by  Unix team using root ) 

To  reload Mulipath service :     service multipathd restart 

5)  Wait for disk to be visible in /dev/mapper on all nodes   

    afdtool -add /dev/mapper/ASM_DISKGROUP1_12  ( on one node by  Unix team using root ) 

6)  afdboot -scandisk /  -rescan   ( on all nodes  by  Unix team using root )   

    afdtool -getdevlist 

7) asmcmd afd_lsdsk

Known Issue 1 ) 

AFD: AFD Is Not Loaded Automatically After Node Reboot Due To Incorrect Dependencies (Doc ID 2724726.1)

Bug 31771370 - INCORRECT OHASD/AFD SERVICE DEPENDENCIES AT OS LEVEL AFTER OS UPDATE TO LINUX 7.8 AFFECTING AFD AND/OR CLUSTERWARE STARTUP 

Solution we did was to rescan afd_scan  

. oraenv 

+ASM4

crsctl stop crs -f 

asmcmd afd_state 

acfsload stop  # stop acfs driver stack 

afdload stop # stop acfsd driver  

asmcmd afd_scan # scan the devices 

acfsload start # start acfs driver stack 

asmcmd afd_lsdsk  # list asm disks

asmcmd afd_filter -e   # enable ASM filter 

asmcmd afd_state 

crsctl start crs -wait   # start crs 

$GRID_HOME/bin/afdload start # start acfsd driver  

/usr/bin/afdboot -scandisk 

$GRID_HOME/bin/crsctl stop crs -f 

$GRID_HOME/bin/crsctl start crs 

We same issue was faced after server migration we also  executed below  before enable ASM filter 

asmcmd afd_configure 

References : 

https://docs.oracle.com/database/121/OSTMG/GUID-302F7233-B905-4D1E-A1AD-9A00EDC6A6F3.htm#OSTMG95732

ASMFD : How to Migrate ASM Diskgroups from ASMLIB to ASMFD (ASM Filter Driver) on Oracle Grid Infrastructure (RAC) (Doc ID 2172754.1)

Oracle _fix_control and DBMS_OPTIM_BUNDLE

_FIX_CONTROL is a special hidden dynamic parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes. This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant  effect on execution plans.

The value parameter set to 1 means bug fix is activated 

When you upgrade oracle database version, you can be face  a problem of CBO that changes its behavior.

 

To enable:

"_fix_control"='Bugno:ON'    (OR)   "_fix_control"="Bugno:1"

To disable:
"_fix_control"='Bugno:OFF'  (OR)   "_fix_control"="Bugno:0"


Eg
alter system set "_fix_control"='27268249:0';   --> Disable 

For _FIX_CONTROL to work, several conditions must be met:

1) The patch that is referenced must have the option to use _FIX_CONTROL.  Using _FIX_CONTROL can't be used to backout any patch.  The patch (usually an Optimizer patch)  has to be enabled to use the _FIX_CONTROL parameter.

2)  The patch must be installed and visible in the V$SYSTEM_FIX_CONTROL view.  To check this:
SQL>  SELECT * FROM V$SYSTEM_FIX_CONTROL;

Note: To determine which bug fixes have been altered one can select from the fixed views GV$SYSTEM_FIX_CONTROL, GV$SESSION_FIX_CONTROL or their V$ counterparts.

Handling _fix_control    at session level  . 

SQL> alter session set "_fix_control"='4728348:OFF';
Session altered.
SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;



Handling _fix_control   using Hints 

/*+ OPT_PARAM('_fix_control’ ’9195582:0') */



Handling _FIX_CONTROL using DBMS_OPTIM_BUNDLE

This package is created to manage (enable/disable) optimizer fixes provided as part of PSU/bundles. Optimizer fixes are those provided as part of bundle which has a fix-control and can possibly cause a plan change.

This package has existed in some previous versions of the database, was dropped again most recently from 19.3 which is why it was again dropped from the Library. Oracle reintroduced it with 19.4 which the Library did not research so, from our perspective, it is "new" again in 20c.

dbms_optim_bundle.enable_optim_fixes(
action                     IN VARCHAR2 DEFAULT 'OFF',
scope                      IN VARCHAR2 DEFAULT 'MEMORY',
current_setting_precedence IN VARCHAR2 DEFAULT 'YES);

DBMS_OPTIM_BUNDLE.ENABLE_OPTIM_FIXES(
     action                     => 'ON' | 'OFF' ,
     scope                      => 'MEMORY' | 'SPFILE' | 'BOTH' | 'INITORA' ,
     current_setting_precedence => 'YES' | 'NO' )


exec dbms_optim_bundle.enable_optim_fixes('ON','MEMORY', 'NO');

exec dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');
exec dbms_optim_bundle.enable_optim_fixes;
exec dbms_optim_bundle.enable_optim_fixes('ON', 'INITORA');
execute dbms_optim_bundle.enable_optim_fixes('OFF','BOTH','NO');

set serveroutput on;
execute dbms_optim_bundle.getBugsforBundle;

exec dbms_optim_bundle.listbundleswithfcfixes;
exec dbms_optim_bundle.getBugsForBundle(170718);

From Oracle 19.12.0 the API got extended  .  We can  have one of fix set to disable and rest set to enable using same command .

SQL> exec dbms_optim_bundle.set_fix_controls('27268249:0','*', 'BOTH','YES');




References : 
How to use the _FIX_CONTROL hidden parameter (Doc ID 827984.1)
MOS Note:2147007.1 – Automatic Fix Control Persistence (FCP) for Database Proactive Bundle Patch
 

Oracle Rac database Cloning Shell Script .

 
We  recently  had requirement to write script to Clone Rac database on same server with different database name . Below is script I prepared.  

In  script i am resetting source database password on each execution . If we don't want to change password  we can keep same password and encrypt password  in script . Below Blog mentions  how we can encrypt password/.

https://abdul-hafeez-kalsekar.blogspot.com/2021/10/oracle-password-encrypt-for-shell.html

Reference : STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1)

 

 ##########################################################
#Script clone_auto.sh to clone  database on same server 
#Author: Abdul  Hafeez Kalsekar                          #
#Date  : 29th August 2021                                 #

#Usage : Need to pass four argument to run script 
#clone_auto.sh Target_Database_Isntance_Name  Target_Database_Name  ASM_Isntance_Name Source_Database_Instance_Name SOURCE_DATABASE_NAME       #
#clone_auto.sh CLONE1  CLONE  +ASM1 RMAN1 RMAN      #

# Before Runing Cloning we need to ensure we have  backup pfile for new clone database   
     #`echo $DATABASE_HOME`/dbs/`echo $INSTANCE_NAME`_non_rac.ora'

# In case we loose pfile for Clone Database we can create pfile from spfile of source database and chaneg below parameters 
# DB_FILE_NAME_CONVERT / LOG_FILE_NAME_CONVERT / db name / db unique name /  controlfile ,  local_listener , huge pages , cluster_database

# Below is Sequence of script 
# 1) Unlock Sys account in source 
# 2) Drop Existing Clone Daatabase 
# 3) Modfiy new parameters in Clone spfile 
# 4) Copy spfile and Password file to Asm 
# 5) Lock Sys account in source database 

# Before we start Cloning below steps were already completed 
# 1) Create Diskgroup for clone database      
# 2) Create  parameter file for clone databse for cloning 
# 3) Create password file for new clone database 
# 4) Create tns  entry for new database 
# 5) Create  new listener for auxiliary instance  
# 6) Added entry for CLone database in Oratab
# 7) Encrypt Password 
# 8) Do manual cloning once and add database to ocr and   
#    check srvctl config database if password file and spfile pointing to desired location as mentioned in script 
#Below are common issues faced 
#RMAN-04006 ORA-01017 when connecting to auxiliary database on Standby From Primary (Doc ID 2445873.1)
#RMAN Active Duplication Fails With Ora-17629, Ora-17627 Errors (Doc ID 2119741.1)
# Below was used to add new listener and add manual entry to  listener.ora under grid home 
#srvctl add listener -l LISTENER_DBNAME   -p TCP:4191 -s
#SID_LIST_LISTENER_DBNAME =
#  (SID_LIST =
#    (SID_DESC =
#      (GLOBAL_DBNAME=DB_NAME )
#      (ORACLE_HOME =  /u01/app/oracle/product/19c/dbhome_1  )
#      (SID_NAME = Instance_name)
#    )
#  )
#Below  entry   was added to  database   home    tnsnames.ora
#Instance_name  =
#   (DESCRIPTION =
#     (ADDRESS_LIST =
#       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
#    )
#     (CONNECT_DATA =
#       (SID =  Instance_name)
#     )
#   )
############################ Start of actual script  ###############################
set -e
INSTANCE_NAME=$1
DBNAME=$2
ASM_INSTANCE_NAME=$3
SOURCEINSTANCE=$4
SOURCEDATABASE=$5
export DATABASE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME  | cut -d ":" -f 2`
export ASM_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
export PWFILE=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`_clone_donotdelete
date=$(date +'%a_%m_%d_%Y')
export LOG_FILE=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_clone_$date.log
export RMAN_LOG=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_RMAN_$date.log

export LOG_DIRECTORY='/u01/app/oracle/log/clone_logs/'
if [ -d $LOG_DIRECTORY ]
then
    echo "Directory already exists"
else
    mkdir -p  $LOG_DIRECTORY
    chmod 744 $LOG_DIRECTORY
fi

if [ -f "$LOG_FILE" ] ; then
    mv "$LOG_FILE" "$LOG_FILE".old
fi
if [ -f "$RMAN_LOG" ] ; then
    mv "$RMAN_LOG" "$RMAN_LOG".old
fi

####################
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
$ORACLE_HOME/bin/sqlplus "/as sysdba"  << EOF  >> $LOG_FILE 
alter user sys IDENTIFIED BY Pass#`echo $date`  account unlock  ; 
exit 
EOF

if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
    rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE` password=Pass#`echo $date`    >> $LOG_FILE 
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
set +e
$ASM_HOME/bin/asmcmd  ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`  
if [ $? -eq 0 ]
then
 $ASM_HOME/bin/asmcmd  rm +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`  
fi
$ASM_HOME/bin/asmcmd  ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd  cp   `echo $DATABASE_HOME`/dbs/orapw`echo $SOURCEDATABASE`  +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
set -e
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
    rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
 
####################
if [ `ps -ef | grep pmon | grep -i $INSTANCE_NAME | grep -v grep | wc -l` == 1 ]; then
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
$ORACLE_HOME/bin/sqlplus "/as sysdba"     << EOF >> $LOG_FILE
alter system set cluster_database=false scope=spfile sid='*' ;
exit 
EOF
$ORACLE_HOME/bin/srvctl stop database -db  $DBNAME  >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus "/as sysdba"     << EOF >> $LOG_FILE
startup mount restrict ;
drop database ; 
exit 
EOF
$ORACLE_HOME/bin/srvctl remove database -db `echo $DBNAME`   << EOF  >> $LOG_FILE
y
EOF
fi 

export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
if [ -f "$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`" ] ; then
    rm $ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME` password=Pass#`echo $date`    >> $LOG_FILE 
$ORACLE_HOME/bin/sqlplus "/as sysdba"    << EOF >> $LOG_FILE
startup nomount pfile='${ORACLE_HOME}/dbs/init`echo $INSTANCE_NAME`_non_rac.ora' ;  
exit 
EOF
echo "cloning start , please refer to  cloning log  `echo $RMAN_LOG`" >> $LOG_FILE
if [ -f ${INSTANCE_NAME}_clone.sql  ] ; then
    rm ${INSTANCE_NAME}_clone.sql 
fi
 
echo " run " >>  `echo $INSTANCE_NAME`_clone.sql 
echo " { " >>    `echo $INSTANCE_NAME`_clone.sql 
echo " allocate channel src1 type disk; " >>    `echo $INSTANCE_NAME`_clone.sql 
echo " allocate channel src2 type disk;" >>    `echo $INSTANCE_NAME`_clone.sql 
echo " allocate channel src3 type disk; " >>     `echo $INSTANCE_NAME`_clone.sql 
echo " allocate channel src4 type disk;  " >>     `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux1 type disk; " >>     `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux2 type disk; " >>     `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux3 type disk; " >>     `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux4 type disk;  " >>    `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux5 type disk;  " >>   `echo $INSTANCE_NAME`_clone.sql 
echo " allocate auxiliary channel aux6 type disk;  " >>     `echo $INSTANCE_NAME`_clone.sql 
 echo " set newname for database to '+`echo $DBNAME`_DATADG';  " >>     `echo $INSTANCE_NAME`_clone.sql 
echo "  duplicate target database to '`echo $DBNAME`' from active database   NOFILENAMECHECK;  " >>     `echo $INSTANCE_NAME`_clone.sql 
echo "   } " >>     `echo $INSTANCE_NAME`_clone.sql 
$ORACLE_HOME/bin/rman target  sys/Pass#`echo $date`@$SOURCEINSTANCE   auxiliary sys/Pass#`echo $date`@$INSTANCE_NAME  log=$RMAN_LOG    cmdfile=`echo $INSTANCE_NAME`_clone.sql   << EOF >> $LOG_FILE
EOF
echo "cloning completed" >> $LOG_FILE
echo " alter system set cluster_database=true scope=spfile sid='*';  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system reset  undo_tablespace scope=spfile sid='*' ; "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set undo_tablespace=UNDOTBS2  scope=spfile sid='`echo $DBNAME`2'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set undo_tablespace=UNDOTBS1  scope=spfile sid='`echo $DBNAME`1'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system reset instance_number scope=spfile sid='*' ; "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set instance_number=1 scope=spfile sid='`echo $DBNAME`1'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set instance_number=2 scope=spfile sid='`echo $DBNAME`2'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system reset thread scope=spfile sid='*' ;     "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set thread=1  scope=spfile sid='`echo $DBNAME`1'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
echo " alter system set thread=2  scope=spfile sid='`echo $DBNAME`2'  ;  "      >>    `echo $INSTANCE_NAME`_postclone_parameters.sql 
$ORACLE_HOME/bin/sqlplus "/as sysdba"  << EOF >> $LOG_FILE
@`echo $INSTANCE_NAME`_postclone_parameters.sql     
shu immediate 
exit 
EOF
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
set +e
$ASM_HOME/bin/asmcmd  ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
if [ $? -eq 0 ]
then
 $ASM_HOME/bin/asmcmd  rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
fi
set -e


set +e 
$ASM_HOME/bin/asmcmd  ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
 if [ $? -eq 0 ]
then
 $ASM_HOME/bin/asmcmd  rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
fi
set -e

set +e 
$ASM_HOME/bin/asmcmd  ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd  ls  +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
fi 
set -e
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora  +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME`   +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
 
if [ -f nodename.log ] ; then
    rm nodename.log
fi
$ASM_HOME/bin/olsnodes -n >> nodename.log
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 

srvctl add database -db `echo $DBNAME` -oraclehome `echo $DATABASE_HOME`  -dbtype RAC  -spfile +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora   -pwfile  +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`   -dbname  `echo $DBNAME`   >>  $LOG_FILE 
cat nodename.log  |while read LINE ;
 do 
 srvctl add instance -db `echo $DBNAME`  -i `echo $DBNAME``echo ${LINE}|awk '{print $2}'` -n `echo ${LINE}|awk '{print $1}'`  
 done
srvctl start database -db `echo $DBNAME`  >>  $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora   >>  $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME`    >>  $LOG_FILE
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE  | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:. 
sqlplus "/as sysdba"  << EOF >>  $LOG_FILE
alter user sys  account lock password expire  ; 
exit 
EOF

###########  end of  script  #####################

Oracle Password Encrypt for shell script -- Unix perl Command

 
We usually face situation where we need to encrypt password for shell scripts .  Recently we faced this  requirement and below is how we added encrypted password in shell  script  

We need to retain  file containing hexadecimal value  .   in our case it was  /dbmonitoring/clone/clone.conf 

################ Encrypt Phase #################
cat /tmp/pass.lst
Pass#Fri_09_24_2021
echo ${pass} > /tmp/pass.lst
hex=`perl -e 'print unpack "H*",\`cat /tmp/pass.lst\`'`
  
echo $hex
50617373234672695f30395f32345f323032315f74617267657a
echo $hex > /dbmonitoring/clone/clone.conf
 
cat /dbmonitoring/clone/clone.conf
50617373234672695f30395f32345f323032315f74617267657a


################ Decrypt Phase #################
decrypt=`perl -e 'print pack "H*",\`cat /dbmonitoring/clone/clone.conf\`'`
echo $decrypt
Pass#Fri_09_24_2021 ▒
Password=${decrypt:0:-2}
echo $Password
Pass#Fri_09_24_2021

sqlplus sys/${Password}@CLONE1 as sysdba