Failed to use SQL plan baseline for this statement

What is baseline? Baseline is a benchmark threshold for a job or sql or task etc.. for marking the performance base level.

It is essential for every database to have a baseline to determine the actual capability of a sql query to execute with in a given amount of time with available resources.

If there is no baseline, then DBA may have infinite never ending process for tuning a sql.

Example: If an adhoc query executes in 5 seconds, then the baseline for that query is 5 seconds. After couple of days, the same query take 1 minute to execute due to changes in the execution plan. In this case, baseline can be checked to get the good plan or initial plan which took 5 seconds and feed optimizer to accept and use the good plan.

During an upgrade from 11g to 12c or 12c to 19c or 19c to 21c, there may be situations where the execution plans may change in the upgraded version due to additional features and enhancements.

There may be cases where the baseline plan need to be exported from preupgrade version of database to newly upgraded database.

If the baseline is not reproducible, then it may throw the below error due to below main reasons,

Failed to use SQL plan baseline for this statement

  • Object id used in the query changes
  • Object is no longer available (Index invisible or dropped)
  • Plan is not reproducible in new version due to invalid hints or unused hints in outline data
  • Bugs

Enable auto baseline capture for the sql’s which are executed in the database.

SQL> show parameter baseline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> ALTER system set optimizer_capture_sql_plan_baselines=TRUE;

System altered.

Create a sample table and select the records with a where clause. Note that the columns are not indexed. This is a new query which execute for the first time in the database.

SQL> CREATE table dspm AS SELECT * from dba_source;
Table created

SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
DBSNMP
DBSNMP
DBSNMP
DBSNMP

Generate the execution plan for the previous query

SQL> set lines 200 pages 1000
SQL> SELECT * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b6kqbg298h1j1, child number 0
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')

Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1570 (100)|          |
|*  1 |  TABLE ACCESS FULL| DSPM |     5 |   370 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
              "OWNER"<>'SYSTEM'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

Create a new index for the table column which is used in ‘where’ clause of the query and select the data.

SQL> CREATE index dtype on dspm(type);

Index created.

SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN

Generate the new execution plan with newly created index.

SQL> SELECT * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b6kqbg298h1j1, child number 0
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')

Plan hash value: 2382681726

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |       |       |  1401 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DSPM  |     5 |   370 |  1401   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DTYPE | 40538 |       |   149   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OWNER"<>'SYS' AND "OWNER"<>'SYSTEM'))
   2 - access("TYPE"='PROCEDURE')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

There are two plans generated for sql ‘b6kqbg298h1j1’. Check the available baselines for this new query. The plan is enabled, accepted and not fixed. This means that, the plan will not be statically used for the query and plan may get changed if a better one is available.

SQL> col SQL_HANDLE for a15
SQL> col SQL_TEXT for a20
SQL> col PLAN_NAME for a20
SQL> col VERSION for a20
SQL> SELECT SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME,VERSION from dba_sql_plan_baselines;

 SIGNATURE SQL_HANDLE      SQL_TEXT             PLAN_NAME            ENA ACC FIX ELAPSED_TIME VERSION
---------- --------------- -------------------- -------------------- --- --- --- ------------ --------------------

7.0999E+18 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES YES NO             0 19.0.0.0.0
           c8c87           pm WHERE type='PROCE 4787226bf8
                           DURE' and owner not
                           in ('SYS','SYSTEM')

Make the index invisible

SQL> ALTER index dtype unusable;

Index altered.

SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM')
*
ERROR at line 1:
ORA-01502: index 'SYS.DTYPE' or partition of such index is in unusable state

SQL> ALTER index dtype invisible;

Index altered.

Again execute the same query and now keenly notice the ‘Note‘ section in the plan. The subject of this blog occurred as error saying that the baseline which is available in the database has the better plan 2382681726 but cannot be used for some reason and an alternative plan 3837617297 is used which was already used before creating an index. Now you should be knowing the reason, why the plan was not used as the index dtype was marked invisible to the optimizer.

The baseline plan 2382681726 stored in the data dictionary contains the good plan though optimizer cannot pick the plan because the plan contains index range scans and optimizer cannot do index scans without an index or invisible index.

SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
DBSNMP

SQL> SELECT * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b6kqbg298h1j1, child number 1
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')

Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1570 (100)|          |
|*  1 |  TABLE ACCESS FULL| DSPM |     5 |   370 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
              "OWNER"<>'SYSTEM'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Failed to use SQL plan baseline for this statement <===


25 rows selected.

The exact reason can be identified by v$sql_plan or dba_hist_sqlplan if history. If you notice, there are two outputs for b6kqbg298h1j1 which has recursive depth of 1. Also, for phv 2382681726, there are two objects with object id 73446 and 73451 which are both table dspm and its index dtype but for phv 3837617297, there is only one object which is the table dspm itself. To find the full reason, there is a column called OTHER_XML in v$sql_plan which gives some additional information on the plan.

SQL> SELECT sql_id,plan_hash_value,object#,depth FROM v$sql_plan where plan_hash_value=3837617297;


SQL_ID        PLAN_HASH_VALUE    OBJECT#      DEPTH
------------- --------------- ---------- ----------
b6kqbg298h1j1      3837617297                     0
b6kqbg298h1j1      3837617297      73446          1

SQL> SELECT sql_id,plan_hash_value,object#,depth FROM v$sql_plan where sql_id='b6kqbg298h1j1';


SQL_ID        PLAN_HASH_VALUE    OBJECT#      DEPTH
------------- --------------- ---------- ----------
b6kqbg298h1j1      3837617297                     0
b6kqbg298h1j1      3837617297      73446          1
b6kqbg298h1j1      2382681726                     0
b6kqbg298h1j1      2382681726      73446          1
b6kqbg298h1j1      2382681726      73451          2

6 rows selected.


SQL> SELECT other_xml FROM v$sql_plan where plan_hash_value=3837617297;

OTHER_XML
--------------------------------------------------------------------------------

<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0<
/info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="dynamic_samp
ling" note="y">2</info><info type="plan_hash_full">3029714510</info><info type="
plan_hash">3837617297</info><info type="plan_hash_2">3029714510</info><info type
="baseline_repro_fail" note="y">yes</info><stats type="compilation"><stat name="
bg">68</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><
t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline
_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZ
ER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hi
nt><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hi
nt><hint><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")]]></hint></outline_data></other_
xml>

Get the exact matching signature of the sql

SQL> select sql_id,exact_matching_signature from v$sql where sql_id='b6kqbg298h1j1';

SQL_ID                                                                EXACT_MATCHING_SIGNATURE
------------- --------------------------------------------------------------------------------
b6kqbg298h1j1                                                              7099895177836006535
b6kqbg298h1j1                                                              7099895177836006535

Substitute the signature in the baseline and get the sql handle details of the spm.

SQL> col SIGNATURE for 9999999999999999999
SQL> SELECT SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME,VERSION from dba_sql_plan_baselines WHERE signature=7099895177836006535;

           SIGNATURE SQL_HANDLE      SQL_TEXT             PLAN_NAME            ENA ACC FIX   ELAPSED_TIME VERSION
-------------------- --------------- -------------------- -------------------- --- --- --- -------------- --------------------
 7099895177836006535 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES YES NO               0 19.0.0.0.0
                     c8c87           pm WHERE type='PROCE 4787226bf8
                                     DURE' and owner not
                                     in ('SYS','SYSTEM')

 7099895177836006535 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES NO  NO               0 19.0.0.0.0
                     c8c87           pm WHERE type='PROCE 47b495c64e
                                     DURE' and owner not
                                     in ('SYS','SYSTEM')

Get the comparison between two different plans and the reason behind the baseline not being used.


SQL> SELECT * from table(dbms_xplan.display_sql_plan_baseline('SQL_6287e50c216c8c87',format=>'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_6287e50c216c8c87
SQL text: SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
          ('SYS','SYSTEM')
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_651z51hhqt34787226bf8         Plan id: 2267180024
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Outline Data from SMB:

  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
--------------------------------------------------------------------------------

Plan hash value: 2382681726

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     5 |   370 |  1401   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DSPM  |     5 |   370 |  1401   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DTYPE | 40538 |       |   149   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DSPM@SEL$1
   2 - SEL$1 / DSPM@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"<>'SYS' AND "OWNER"<>'SYSTEM')
   2 - access("TYPE"='PROCEDURE')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OWNER"[VARCHAR2,128]
   2 - "DSPM".ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1
        ]]></s></h></f></q>


--------------------------------------------------------------------------------
Plan name: SQL_PLAN_651z51hhqt347b495c64e         Plan id: 3029714510
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Outline Data from SMB:

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DSPM"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
--------------------------------------------------------------------------------

Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   370 |  1570   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DSPM |     5 |   370 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DSPM@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DSPM"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
              "OWNER"<>'SYSTEM')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OWNER"[VARCHAR2,128]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><
        ![CDATA[SEL$1]]></s></h></f></q>


153 rows selected.
Types Of Segments in Oracle

SQL> SELECT distinct segment_type from dba_Segments;

SEGMENT_TYPE
------------------
INDEX
CLUSTER
TABLE PARTITION
LOBINDEX
TABLE SUBPARTITION
SYSTEM STATISTICS
LOBSEGMENT
INDEX PARTITION
ROLLBACK
TABLE
LOB PARTITION

SEGMENT_TYPE
------------------
NESTED TABLE
TYPE2 UNDO

13 rows selected.
Convert Non Partitioned Table To Partitioned Table

Create a sample non partitioned table

kIsH@zxd00<^>create table tp as
select
    rownum as rid,
    case
        when mod(trunc(dbms_random.value(1,100000)),2) = 0 then 'Male'
        when mod(trunc(dbms_random.value(1,100000)),2) = 1 then 'Female'
        else 'Others'
    end as Gender,
    add_months(sysdate, - trunc(dbms_random.value(1,10))) as cdate
from dual
connect by rownum <= 100000
/  2    3    4    5    6    7    8    9   10   11   12

Table created.

Enable constraints and index on the table

kIsH@zxd00<^>ALTER table tp add constraint t_pk primary key(rid);

Table altered.

kIsH@zxd00<^>CREATE index tidx on tp(cdate);

Index created.

Check the segments created.

kIsH@zxd00<^>col SEGMENT_NAME for a29
kIsH@zxd00<^>col SEGMENT_NAME for a20
kIsH@zxd00<^>col segment_type for a20
kIsH@zxd00<^>   SELECT segment_name,segment_type FROM dba_segments WHERE segment_name in ('TP','TIDX','T_PK') order by segment_type;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- --------------------
T_PK                 INDEX
TIDX                 INDEX
TP                   TABLE

A single command is sufficient to convert a non partitioned table to partitioned table.

kIsH@zxd00<^>ALTER table tp modify partition by range(cdate) interval(numtoyminterval(1, 'MONTH'))(partition p_default values less than (to_date('2023-01-01', 'YYYY-MM-DD')));

Table altered.

Check the converted partitions after the process.

kIsH@zxd00<^>SELECT segment_name,segment_type FROM dba_segments WHERE segment_name in ('TP','TIDX','T_PK');

SEGMENT_NAME         SEGMENT_TYPE
-------------------- --------------------
T_PK                 INDEX
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TIDX                 INDEX PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION
TP                   TABLE PARTITION

19 rows selected.
Recover Database When Current Redolog File Is Lost

SQL> SELECT * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         19  209715200        512          1 YES INACTIVE               2801844 26-MAY-23      2914294 13-JUN-23          0
         2          1         20  209715200        512          1 YES INACTIVE               2914294 13-JUN-23      3021428 22-SEP-23          0
         3          1         21  209715200        512          1 NO  CURRENT                3021428 22-SEP-23   1.8447E+19                    0

SQL> col MEMBER for a20
SQL> SELECT * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER               IS_     CON_ID
---------- ------- ------- -------------------- --- ----------
         3         ONLINE  /apps01/oradata/HYDR NO           0
                           A1/redo03.log

         2         ONLINE  /apps01/oradata/HYDR NO           0
                           A1/redo02.log

         1         ONLINE  /apps01/oradata/HYDR NO           0
                           A1/redo01.log

SQL> SELECT MEMBER from v$logfile lf,v$log l WHERE lf.group# = l.group# and l.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------
/apps01/oradata/HYDRA1/redo02.log


RMAN> run
2> {
3> backup as backupset database plus archivelog delete input;
4> backup current controlfile;
5> }


Starting backup at 22-SEP-23
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=102 device type=DISK

SQL> !rm -rf /apps01/oradata/HYDRA1/redo02.log

SQL> CREATE table t as select * from dba_source;

Table created.

SQL> INSERT into t SELECT * from dba_source;

291055 rows created.

SQL> commit;

Commit complete.

SQL> SELECT MEMBER from v$logfile lf,v$log l WHERE lf.group# = l.group# and l.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------
/apps01/oradata/HYDRA1/redo02.log

SQL> INSERT into t SELECT * from dba_source;

291055 rows created.

SQL> INSERT into t SELECT * from dba_source;

291055 rows created.

SQL> commit;

Commit complete.

SQL>  SELECT MEMBER from v$logfile lf,v$log l WHERE lf.group# = l.group# and l.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------
/apps01/oradata/HYDRA1/redo03.log

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/apps01/oradata/HYDRA1/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2023-09-22T20:44:00.554450+05:30
Errors in file /u01/app/oracle/diag/rdbms/hydra1/hydra1/trace/hydra1_mz00_30790.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/apps01/oradata/HYDRA1/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 2 new persistent data failures

SQL> ALTER database clear unarchived logfile GROUP 2;

Database altered.

SQL> ALTER database drop logfile group 2;

Database altered.
SQL> ALTER database add logfile group 2;

Database altered.

SQL> ALTER system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/

System altered.

SQL>
System altered.
DBMS_STATS Fails With ORA-20000 Inside A Stored Procedure

As per Oracle documentation, any user who create a user defined stored procedure and embed any SYS related stored procedure like DBMS_STATS inside it, any role which is granted to the user is skipped inside PL/SQL or procedure or trigger etc.. and throws an error ORA-20000. But individual grants work correctly.

Grant the basic privileges to new user with gather_system_statistics role to gather system statistics.

kIsH@zxd00<^>alter session set "_ORACLE_SCRIPT"=true;

Session altered.

kIsH@zxd00<^>create user test identified by password;

User created.


kIsH@zxd00<^>grant create session,connect,resource,gather_system_statistics to test;

Grant succeeded.

The new user is able to gather system stats without issues with the normal method.

kIsH@zxd00<^>conn test/password
Connected.
kIsH@zxd00<^>EXEC dbms_stats.gather_system_stats();

PL/SQL procedure successfully completed.

Now, create a stored procedure and embed DBMS_STATS command in it.

CREATE OR REPLACE procedure sysstat
AS 
BEGIN
dbms_stats.gather_system_stats();
dbms_output.put_line('Executed!');
END;
/

kIsH@zxd00<^>CREATE OR REPLACE procedure sysstat
AS
BEGIN
dbms_stats.gather_system_stats();
dbms_output.put_line('Executed!');
END;
/  2    3    4    5    6    7

Procedure created.

This does not allow the new user to gather system statistics with ORA-20000

kIsH@zxd00<^>EXEC sysstat
BEGIN sysstat; END;

*
ERROR at line 1:
ORA-20000: Unable to gather system statistics : insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 46421
ORA-06512: at "SYS.DBMS_STATS", line 46314
ORA-06512: at "TEST.SYSSTAT", line 4
ORA-06512: at line 1

Grant individual privilege to the user on aux_stats$ and wri$_optstat_aux_history internal tables.

kIsH@zxd00<^>col TABLE_NAME for a20
kIsH@zxd00<^>col PRIVILEGE for a20
kIsH@zxd00<^>SELECT role,privilege,table_name FROM role_tab_privs WHERE role='GATHER_SYSTEM_STATISTICS';

ROLE                 PRIVILEGE            TABLE_NAME
-------------------- -------------------- --------------------
GATHER_SYSTEM_STATIS DELETE               AUX_STATS$
TICS

GATHER_SYSTEM_STATIS INSERT               AUX_STATS$
TICS

GATHER_SYSTEM_STATIS SELECT               AUX_STATS$
TICS

GATHER_SYSTEM_STATIS UPDATE               AUX_STATS$
TICS

GATHER_SYSTEM_STATIS DELETE               WRI$_OPTSTAT_AUX_HIS
TICS                                      TORY

GATHER_SYSTEM_STATIS INSERT               WRI$_OPTSTAT_AUX_HIS
TICS                                      TORY

GATHER_SYSTEM_STATIS SELECT               WRI$_OPTSTAT_AUX_HIS
TICS                                      TORY

GATHER_SYSTEM_STATIS UPDATE               WRI$_OPTSTAT_AUX_HIS
TICS                                      TORY


8 rows selected.

kIsH@zxd00<^>grant select,insert,update,delete on aux_stats$ to test;

Grant succeeded.

kIsH@zxd00<^>grant select,insert,update,delete on wri$_optstat_aux_history to test;

Grant succeeded.

Once the individual privilege is granted, the user defined procedure executed successfully.

kIsH@zxd00<^>conn test/password
Connected.

kIsH@zxd00<^>set serveroutput on
kIsH@zxd00<^>EXEC sysstat
Executed!

PL/SQL procedure successfully completed.
Install Softwares Using Chocolatey

PS C:\Windows\system32> choco install virtualbox --version=7.0.8 -y
Chocolatey v1.2.1
Installing the following packages:
virtualbox
By installing, you accept licenses for the packages.

virtualbox v7.0.8 [Approved]
virtualbox package files install completed. Performing other installation steps.
#< CLIXML
<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04"><Obj S="progress" RefId="0"><TN RefId="0"><T>System.Management.Automation.PSCustomObject</T><T>System.Object</T></TN><MS><I64 N="SourceId">1</I64><PR N="Record"><AV>Preparing modules for first use.</AV><AI>0</AI><Nil /><PI>-1</PI><PC>-1</PC><T>Completed</T><SR>-1</SR><SD> </SD></PR></MS></Obj><Obj S="progress" RefId="1"><TNRef RefId="0" /><MS><I64 N="SourceId">1</I64><PR N="Record"><AV>Preparing modules for first use.</AV><AI>0</AI><Nil /><PI>-1</PI><PC>-1</PC><T>Completed</T><SR>-1</SR><SD> </SD></PR></MS></Obj><S S="debug">Host version is 5.1.19041.3031, PowerShell Version is '5.1.19041.3031' and CLR Version is '4.0.30319.42000'.</S><S S="verbose">Exporting function 'Format-FileSize'.</S><S S="verbose">Exporting function 'Get-ChecksumValid'.</S><S S="verbose">Exporting function 'Get-ChocolateyPath'.</S><S S="verbose">Exporting function 'Get-ChocolateyUnzip'.</S><S S="verbose">Exporting function 'Get-ChocolateyWebFile'.</S><S S="verbose">Exporting function 'Get-EnvironmentVariable'.</S><S S="verbose">Exporting function 'Get-EnvironmentVariableNames'.</S><S S="verbose">Exporting function 'Get-FtpFile'.</S><S S="verbose">Exporting function 'Get-OSArchitectureWidth'.</S><S S="verbose">Exporting function 'Get-PackageParameters'.</S><S S="verbose">Exporting function 'Get-PackageParametersBuiltIn'.</S><S S="verbose">Exporting function 'Get-ToolsLocation'.</S><S S="verbose">Exporting function 'Get-UACEnabled'.</S><S S="verbose">Exporting function 'Get-UninstallRegistryKey'.</S><S S="verbose">Exporting function 'Get-VirusCheckValid'.</S><S S="verbose">Exporting function 'Get-WebFile'.</S><S S="verbose">Exporting function 'Get-WebFileName'.</S><S S="verbose">Exporting function 'Get-WebHeaders'.</S><S S="verbose">Exporting function 'Install-BinFile'.</S><S S="verbose">Exporting function 'Install-ChocolateyEnvironmentVariable'.</S><S S="verbose">Exporting function 'Install-ChocolateyExplorerMenuItem'.</S><S S="verbose">Exporting function 'Install-ChocolateyFileAssociation'.</S><S S="verbose">Exporting function 'Install-ChocolateyInstallPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyPath'.</S><S S="verbose">Exporting function 'Install-ChocolateyPinnedTaskBarItem'.</S><S S="verbose">Exporting function 'Install-ChocolateyPowershellCommand'.</S><S S="verbose">Exporting function 'Install-ChocolateyShortcut'.</S><S S="verbose">Exporting function 'Install-ChocolateyVsixPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyZipPackage'.</S><S S="verbose">Exporting function 'Install-Vsix'.</S><S S="verbose">Exporting function 'Set-EnvironmentVariable'.</S><S S="verbose">Exporting function 'Set-PowerShellExitCode'.</S><S S="verbose">Exporting function 'Start-ChocolateyProcessAsAdmin'.</S><S S="verbose">Exporting function 'Test-ProcessAdminRights'.</S><S S="verbose">Exporting function 'Uninstall-BinFile'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyEnvironmentVariable'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyPackage'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyZipPackage'.</S><S S="verbose">Exporting function 'Update-SessionEnvironment'.</S><S S="verbose">Exporting function 'Write-FunctionCallLogMessage'.</S><S S="verbose">Exporting alias 'Get-ProcessorBits'.</S><S S="verbose">Exporting alias 'Get-OSBitness'.</S><S S="verbose">Exporting alias 'Get-InstallRegistryKey'.</S><S S="verbose">Exporting alias 'Generate-BinFile'.</S><S S="verbose">Exporting alias 'Add-BinFile'.</S><S S="verbose">Exporting alias 'Start-ChocolateyProcess'.</S><S S="verbose">Exporting alias 'Invoke-ChocolateyProcess'.</S><S S="verbose">Exporting alias 'Remove-BinFile'.</S><S S="verbose">Exporting alias 'refreshenv'.</S><S S="debug">Loading community extensions</S><S S="debug">Importing 'C:\ProgramData\chocolatey\extensions\chocolatey-compatibility\chocolatey-compatibility.psm1'</S><S S="verbose">Loading module from path 'C:\ProgramData\chocolatey\extensions\chocolatey-compatibility\chocolatey-compatibility.psm1'.</S><S S="debug">Function 'Get-PackageParameters' exists, ignoring export.</S><S S="debug">Function 'Get-UninstallRegistryKey' exists, ignoring export.</S><S S="debug">Exporting function 'Install-ChocolateyDesktopLink' for backwards compatibility</S><S S="verbose">Exporting function 'Install-ChocolateyDesktopLink'.</S><S S="debug">Exporting function 'Write-ChocolateyFailure' for backwards compatibility</S><S S="verbose">Exporting function 'Write-ChocolateyFailure'.</S><S S="debug">Exporting function 'Write-ChocolateySuccess' for backwards compatibility</S><S S="verbose">Exporting function 'Write-ChocolateySuccess'.</S><S S="debug">Exporting function 'Write-FileUpdateLog' for backwards compatibility</S><S S="verbose">Exporting function 'Write-FileUpdateLog'.</S><S S="verbose">Importing function 'Install-ChocolateyDesktopLink'.</S><S S="verbose">Importing function 'Write-ChocolateyFailure'.</S><S S="verbose">Importing function 'Write-ChocolateySuccess'.</S><S S="verbose">Importing function 'Write-FileUpdateLog'.</S><S S="debug">Importing 'C:\ProgramData\chocolatey\extensions\chocolatey-core\chocolatey-core.psm1'</S><S S="verbose">Loading module from path 'C:\ProgramData\chocolatey\extensions\chocolatey-core\chocolatey-core.psm1'.</S><S S="verbose">Exporting function 'Get-AppInstallLocation'.</S><S S="verbose">Exporting function 'Get-AvailableDriveLetter'.</S><S S="verbose">Exporting function 'Get-EffectiveProxy'.</S><S S="verbose">Exporting function 'Get-PackageCacheLocation'.</S><S S="verbose">Exporting function 'Get-WebContent'.</S><S S="verbose">Exporting function 'Register-Application'.</S><S S="verbose">Exporting function 'Remove-Process'.</S><S S="verbose">Importing function 'Get-AppInstallLocation'.</S><S S="verbose">Importing function 'Get-AvailableDriveLetter'.</S><S S="verbose">Importing function 'Get-EffectiveProxy'.</S><S S="verbose">Importing function 'Get-PackageCacheLocation'.</S><S S="verbose">Importing function 'Get-WebContent'.</S><S S="verbose">Importing function 'Register-Application'.</S><S S="verbose">Importing function 'Remove-Process'.</S><S S="debug">Importing 'C:\ProgramData\chocolatey\extensions\chocolatey-dotnetfx\chocolatey-dotnetfx.psm1'</S><S S="verbose">Loading module from path 'C:\ProgramData\chocolatey\extensions\chocolatey-dotnetfx\chocolatey-dotnetfx.psm1'.</S><S S="verbose">Exporting function 'Install-DotNetFramework'.</S><S S="verbose">Exporting function 'Install-DotNetDevPack'.</S><S S="verbose">Importing function 'Install-DotNetDevPack'.</S><S S="verbose">Importing function 'Install-DotNetFramework'.</S><S S="debug">Importing 'C:\ProgramData\chocolatey\extensions\chocolatey-visualstudio\chocolatey-visualstudio.extension.psm1'</S><S S="verbose">Loading module from path 'C:\ProgramData\chocolatey\extensions\chocolatey-visualstudio\chocolatey-visualstudio.extension.psm1'.</S><S S="verbose">Exporting function 'Add-VisualStudioComponent'.</S><S S="verbose">Exporting function 'Add-VisualStudioWorkload'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstaller'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstallerHealth'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstance'.</S><S S="verbose">Exporting function 'Get-VisualStudioVsixInstaller'.</S><S S="verbose">Exporting function 'Install-VisualStudio'.</S><S S="verbose">Exporting function 'Install-VisualStudioInstaller'.</S><S S="verbose">Exporting function 'Install-VisualStudioVsixExtension'.</S><S S="verbose">Exporting function 'Remove-VisualStudioComponent'.</S><S S="verbose">Exporting function 'Remove-VisualStudioProduct'.</S><S S="verbose">Exporting function 'Remove-VisualStudioWorkload'.</S><S S="verbose">Exporting function 'Uninstall-VisualStudio'.</S><S S="verbose">Exporting function 'Uninstall-VisualStudioVsixExtension'.</S><S S="verbose">Importing function 'Add-VisualStudioComponent'.</S><S S="verbose">Importing function 'Add-VisualStudioWorkload'.</S><S S="verbose">Importing function 'Get-VisualStudioInstaller'.</S><S S="verbose">Importing function 'Get-VisualStudioInstallerHealth'.</S><S S="verbose">Importing function 'Get-VisualStudioInstance'.</S><S S="verbose">Importing function 'Get-VisualStudioVsixInstaller'.</S><S S="verbose">Importing function 'Install-VisualStudio'.</S><S S="verbose">Importing function 'Install-VisualStudioInstaller'.</S><S S="verbose">Importing function 'Install-VisualStudioVsixExtension'.</S><S S="verbose">Importing function 'Remove-VisualStudioComponent'.</S><S S="verbose">Importing function 'Remove-VisualStudioProduct'.</S><S S="verbose">Importing function 'Remove-VisualStudioWorkload'.</S><S S="verbose">Importing function 'Uninstall-VisualStudio'.</S><S S="verbose">Importing function 'Uninstall-VisualStudioVsixExtension'.</S><S S="debug">Importing 'C:\ProgramData\chocolatey\extensions\chocolatey-windowsupdate\chocolatey-windowsupdate.psm1'</S><S S="verbose">Loading module from path 'C:\ProgramData\chocolatey\extensions\chocolatey-windowsupdate\chocolatey-windowsupdate.psm1'.</S><S S="verbose">Exporting function 'Install-WindowsUpdate'.</S><S S="verbose">Exporting function 'Test-WindowsUpdate'.</S><S S="verbose">Importing function 'Install-WindowsUpdate'.</S><S S="verbose">Importing function 'Test-WindowsUpdate'.</S><S S="verbose">Exporting function 'Format-FileSize'.</S><S S="verbose">Exporting function 'Get-ChecksumValid'.</S><S S="verbose">Exporting function 'Get-ChocolateyPath'.</S><S S="verbose">Exporting function 'Get-ChocolateyUnzip'.</S><S S="verbose">Exporting function 'Get-ChocolateyWebFile'.</S><S S="verbose">Exporting function 'Get-EnvironmentVariable'.</S><S S="verbose">Exporting function 'Get-EnvironmentVariableNames'.</S><S S="verbose">Exporting function 'Get-FtpFile'.</S><S S="verbose">Exporting function 'Get-OSArchitectureWidth'.</S><S S="verbose">Exporting function 'Get-PackageParameters'.</S><S S="verbose">Exporting function 'Get-PackageParametersBuiltIn'.</S><S S="verbose">Exporting function 'Get-ToolsLocation'.</S><S S="verbose">Exporting function 'Get-UACEnabled'.</S><S S="verbose">Exporting function 'Get-UninstallRegistryKey'.</S><S S="verbose">Exporting function 'Get-VirusCheckValid'.</S><S S="verbose">Exporting function 'Get-WebFile'.</S><S S="verbose">Exporting function 'Get-WebFileName'.</S><S S="verbose">Exporting function 'Get-WebHeaders'.</S><S S="verbose">Exporting function 'Install-BinFile'.</S><S S="verbose">Exporting function 'Install-ChocolateyEnvironmentVariable'.</S><S S="verbose">Exporting function 'Install-ChocolateyExplorerMenuItem'.</S><S S="verbose">Exporting function 'Install-ChocolateyFileAssociation'.</S><S S="verbose">Exporting function 'Install-ChocolateyInstallPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyPath'.</S><S S="verbose">Exporting function 'Install-ChocolateyPinnedTaskBarItem'.</S><S S="verbose">Exporting function 'Install-ChocolateyPowershellCommand'.</S><S S="verbose">Exporting function 'Install-ChocolateyShortcut'.</S><S S="verbose">Exporting function 'Install-ChocolateyVsixPackage'.</S><S S="verbose">Exporting function 'Install-ChocolateyZipPackage'.</S><S S="verbose">Exporting function 'Install-Vsix'.</S><S S="verbose">Exporting function 'Set-EnvironmentVariable'.</S><S S="verbose">Exporting function 'Set-PowerShellExitCode'.</S><S S="verbose">Exporting function 'Start-ChocolateyProcessAsAdmin'.</S><S S="verbose">Exporting function 'Test-ProcessAdminRights'.</S><S S="verbose">Exporting function 'Uninstall-BinFile'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyEnvironmentVariable'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyPackage'.</S><S S="verbose">Exporting function 'Uninstall-ChocolateyZipPackage'.</S><S S="verbose">Exporting function 'Update-SessionEnvironment'.</S><S S="verbose">Exporting function 'Write-FunctionCallLogMessage'.</S><S S="verbose">Exporting function 'Install-ChocolateyDesktopLink'.</S><S S="verbose">Exporting function 'Write-ChocolateyFailure'.</S><S S="verbose">Exporting function 'Write-ChocolateySuccess'.</S><S S="verbose">Exporting function 'Write-FileUpdateLog'.</S><S S="verbose">Exporting function 'Get-AppInstallLocation'.</S><S S="verbose">Exporting function 'Get-AvailableDriveLetter'.</S><S S="verbose">Exporting function 'Get-EffectiveProxy'.</S><S S="verbose">Exporting function 'Get-PackageCacheLocation'.</S><S S="verbose">Exporting function 'Get-WebContent'.</S><S S="verbose">Exporting function 'Register-Application'.</S><S S="verbose">Exporting function 'Remove-Process'.</S><S S="verbose">Exporting function 'Install-DotNetDevPack'.</S><S S="verbose">Exporting function 'Install-DotNetFramework'.</S><S S="verbose">Exporting function 'Add-VisualStudioComponent'.</S><S S="verbose">Exporting function 'Add-VisualStudioWorkload'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstaller'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstallerHealth'.</S><S S="verbose">Exporting function 'Get-VisualStudioInstance'.</S><S S="verbose">Exporting function 'Get-VisualStudioVsixInstaller'.</S><S S="verbose">Exporting function 'Install-VisualStudio'.</S><S S="verbose">Exporting function 'Install-VisualStudioInstaller'.</S><S S="verbose">Exporting function 'Install-VisualStudioVsixExtension'.</S><S S="verbose">Exporting function 'Remove-VisualStudioComponent'.</S><S S="verbose">Exporting function 'Remove-VisualStudioProduct'.</S><S S="verbose">Exporting function 'Remove-VisualStudioWorkload'.</S><S S="verbose">Exporting function 'Uninstall-VisualStudio'.</S><S S="verbose">Exporting function 'Uninstall-VisualStudioVsixExtension'.</S><S S="verbose">Exporting function 'Install-WindowsUpdate'.</S><S S="verbose">Exporting function 'Test-WindowsUpdate'.</S><S S="verbose">Exporting alias 'Get-ProcessorBits'.</S><S S="verbose">Exporting alias 'Get-OSBitness'.</S><S S="verbose">Exporting alias 'Get-InstallRegistryKey'.</S><S S="verbose">Exporting alias 'Generate-BinFile'.</S><S S="verbose">Exporting alias 'Add-BinFile'.</S><S S="verbose">Exporting alias 'Start-ChocolateyProcess'.</S><S S="verbose">Exporting alias 'Invoke-ChocolateyProcess'.</S><S S="verbose">Exporting alias 'Remove-BinFile'.</S><S S="verbose">Exporting alias 'refreshenv'.</S></Objs>
0
Downloading virtualbox 64 bit
  from 'https://download.virtualbox.org/virtualbox/7.0.8/VirtualBox-7.0.8-156879-Win.exe'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\virtualbox\7.0.8\VirtualBox-7.0.8-156879-Win.exe (105.5 MB).
Download of VirtualBox-7.0.8-156879-Win.exe (105.5 MB) completed.
Hashes match.
Installing virtualbox...
virtualbox has been installed.
Adding to PATH if needed
PATH environment variable does not have C:\Program Files\Oracle\VirtualBox in it. Adding...
virtualbox installed to 'C:\Program Files\Oracle\VirtualBox'
virtualbox registered as vbox
Only an exit code of non-zero will fail the package by default. Set
 `--failonstderr` if you want error messages to also fail a script. See
 `choco -h` for details.
  virtualbox may be able to be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of virtualbox was successful.
  Software installed as 'EXE', install location is likely default.

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install vagrant --version=2.3.4 -y
Chocolatey v1.2.1
Installing the following packages:
vagrant
By installing, you accept licenses for the packages.
Progress: Downloading vagrant 2.3.4... 100%

vagrant v2.3.4 [Approved]
vagrant package files install completed. Performing other installation steps.
Downloading vagrant 64 bit
  from 'https://releases.hashicorp.com/vagrant/2.3.4/vagrant_2.3.4_windows_amd64.msi'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\vagrant\2.3.4\vagrant_2.3.4_windows_amd64.msi (249.57 MB).
Download of vagrant_2.3.4_windows_amd64.msi (249.57 MB) completed.
Hashes match.
Installing vagrant...
vagrant has been installed.
==> vagrant: A new version of Vagrant is available: 2.3.7 (installed version: 2.3.4)!
==> vagrant: To upgrade visit: https://www.vagrantup.com/downloads.html
System.Management.Automation.RemoteException
Updating installed plugins...
All plugins are up to date.
Repairing currently installed global plugins. This may take a few minutes...
Installed plugins successfully repaired!
  vagrant may be able to be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of vagrant was successful.
  Software installed as 'msi', install location is likely default.

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).

Packages requiring reboot:
 - vagrant (exit code 3010)

The recent package changes indicate a reboot is necessary.
 Please reboot at your earliest convenience.
PS C:\Windows\system32> choco install git -y
Chocolatey v1.2.1
Installing the following packages:
git
By installing, you accept licenses for the packages.
Progress: Downloading git.install 2.42.0... 100%
Progress: Downloading git 2.42.0... 100%

git.install v2.42.0 [Approved]
git.install package files install completed. Performing other installation steps.
Using Git LFS
Installing 64-bit git.install...
git.install has been installed.
git.install installed to 'C:\Program Files\Git'
  git.install can be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of git.install was successful.
  Software installed to 'C:\Program Files\Git\'

git v2.42.0 [Approved]
git package files install completed. Performing other installation steps.
 The install of git was successful.
  Software installed to 'C:\ProgramData\chocolatey\lib\git'

Chocolatey installed 2/2 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install corretto11jdk -y
Chocolatey v1.2.1
Installing the following packages:
corretto11jdk
By installing, you accept licenses for the packages.
Progress: Downloading corretto11jdk 11.0.20... 100%

corretto11jdk v11.0.20 [Approved]
corretto11jdk package files install completed. Performing other installation steps.
Downloading corretto11jdk 64 bit
  from 'https://corretto.aws/downloads/resources/11.0.20.8.1/amazon-corretto-11.0.20.8.1-windows-x64.msi'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\corretto11jdk\11.0.20\amazon-corretto-11.0.20.8.1-windows-x64.msi (161.17 MB).
Download of amazon-corretto-11.0.20.8.1-windows-x64.msi (161.17 MB) completed.
Hashes match.
Installing corretto11jdk...
corretto11jdk has been installed.
  corretto11jdk may be able to be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of corretto11jdk was successful.
  Software installed as 'msi', install location is likely default.

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install maven -y
Chocolatey v1.2.1
Installing the following packages:
maven
By installing, you accept licenses for the packages.
Progress: Downloading maven 3.9.4... 100%

maven v3.9.4 [Approved]
maven package files install completed. Performing other installation steps.
C:\Users\kisha\.m2
PATH environment variable does not have C:\ProgramData\chocolatey\lib\maven\apache-maven-3.9.4\bin in it. Adding...
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of maven was successful.
  Software installed to 'C:\ProgramData\chocolatey\lib\maven\apache-maven-3.9.4'

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install awscli -y
Chocolatey v1.2.1
Installing the following packages:
awscli
By installing, you accept licenses for the packages.
Progress: Downloading awscli 2.13.16... 100%

awscli v2.13.16 [Approved]
awscli package files install completed. Performing other installation steps.
Downloading awscli 64 bit
  from 'https://awscli.amazonaws.com/AWSCLIV2-2.13.16.msi'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\awscli\2.13.16\AWSCLIV2-2.13.16.msi (37.44 MB).
Download of AWSCLIV2-2.13.16.msi (37.44 MB) completed.
Hashes match.
Installing awscli...
awscli has been installed.
  awscli may be able to be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of awscli was successful.
  Software installed as 'MSI', install location is likely default.

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install intellijidea-community -y
Chocolatey v1.2.1
Installing the following packages:
intellijidea-community
By installing, you accept licenses for the packages.
Progress: Downloading intellijidea-community 2023.2.1... 100%

intellijidea-community v2023.2.1 [Approved]
intellijidea-community package files install completed. Performing other installation steps.
WARNING: No registry key found based on  'IntelliJ IDEA Community Edition*'
C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2023.2.1
Downloading intellijidea-community 64 bit
  from 'https://download.jetbrains.com/idea/ideaIC-2023.2.1.exe'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\intellijidea-community\2023.2.1\ideaIC-2023.2.1.exe (663.63 MB).
Download of ideaIC-2023.2.1.exe (663.63 MB) completed.
Hashes match.
Installing intellijidea-community...
Chocolatey timed out waiting for the command to finish. The timeout
 specified (or the default value) was '2700' seconds. Perhaps try a
 higher `--execution-timeout`? See `choco -h` for details.
The install of intellijidea-community was NOT successful.
Error while running 'C:\ProgramData\chocolatey\lib\intellijidea-community\tools\chocolateyInstall.ps1'.
 See log for details.

Chocolatey installed 0/1 packages. 1 packages failed.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).

Failures
 - intellijidea-community (exited -1) - Error while running 'C:\ProgramData\chocolatey\lib\intellijidea-community\tools\chocolateyInstall.ps1'.
 See log for details.
PS C:\Windows\system32> choco install vscode -y
Chocolatey v1.2.1
Installing the following packages:
vscode
By installing, you accept licenses for the packages.
Progress: Downloading vscode.install 1.82.0... 100%
Progress: Downloading vscode 1.82.0... 100%

vscode.install v1.82.0 [Approved]
vscode.install package files install completed. Performing other installation steps.
WARNING: No registry key found based on  'Microsoft Visual Studio Code'
Merge Tasks: !runCode, desktopicon, quicklaunchicon, addcontextmenufiles, addcontextmenufolders, associatewithfiles, addtopath
Downloading vscode.install 64 bit
  from 'https://update.code.visualstudio.com/1.82.0/win32-x64/stable'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\vscode.install\1.82.0\VSCodeSetup-x64-1.82.0.exe (89.97 MB).
Download of VSCodeSetup-x64-1.82.0.exe (89.97 MB) completed.
Hashes match.
Installing vscode.install...
vscode.install has been installed.
  vscode.install can be automatically uninstalled.
Environment Vars (like PATH) have changed. Close/reopen your shell to
 see the changes (or in powershell/cmd.exe just type `refreshenv`).
 The install of vscode.install was successful.
  Software installed to 'C:\Program Files\Microsoft VS Code\'

vscode v1.82.0 [Approved]
vscode package files install completed. Performing other installation steps.
 The install of vscode was successful.
  Software installed to 'C:\ProgramData\chocolatey\lib\vscode'

Chocolatey installed 2/2 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).
PS C:\Windows\system32> choco install sublimetext3.app -y
Chocolatey v1.2.1
Installing the following packages:
sublimetext3.app
By installing, you accept licenses for the packages.
Progress: Downloading SublimeText3 3.2.2... 100%
Progress: Downloading SublimeText3.app 3.0.0.3065... 100%
Progress: Downloading SublimeText3.PowershellAlias 0.1.0... 100%
Progress: Downloading SublimeText3.PackageControl 2.0.0.20140915... 100%

SublimeText3 v3.2.2 [Approved]
sublimetext3 package files install completed. Performing other installation steps.
Downloading SublimeText3 64 bit
  from 'https://download.sublimetext.com/Sublime%20Text%20Build%203211%20x64%20Setup.exe'
Progress: 100% - Completed download of C:\Users\kisha\AppData\Local\Temp\chocolatey\SublimeText3\3.2.2\Sublime Text Build 3211 x64 Setup.exe (10.42 MB).
Download of Sublime Text Build 3211 x64 Setup.exe (10.42 MB) completed.
Hashes match.
Installing SublimeText3...
SublimeText3 has been installed.
  sublimetext3 can be automatically uninstalled.
 The install of sublimetext3 was successful.
  Software installed to 'C:\Program Files\Sublime Text 3\'

SublimeText3.PowershellAlias v0.1.0
sublimetext3.powershellalias package files install completed. Performing other installation steps.
Added subl alias to Powershell profile to launch Sublime Text 3!
WARNING: Write-ChocolateySuccess was removed in Chocolatey CLI v1, and have no functionality any more. If you are the maintainer, please remove it from from your package file.
WARNING: If you are not the maintainer, please contact the maintainer to update the SublimeText3.PowershellAlias package.
 The install of sublimetext3.powershellalias was successful.
  Software install location not explicitly set, it could be in package or
  default install location of installer.

SublimeText3.PackageControl v2.0.0.20140915 [Approved] - Possibly broken
sublimetext3.packagecontrol package files install completed. Performing other installation steps.
C:\Users\kisha\AppData\Roaming\Sublime Text 3\Installed Packages
cmdlet Get-ChocolateyWebFile at command pipeline position 1
packageName
  Confirmation (`-y`) is set.
  Respond within 30 seconds or the default selection will be chosen.
WARNING: Write-ChocolateyFailure was removed in Chocolatey CLI v1. If you are the package maintainer, please use 'throw $_.Exception' instead.
WARNING: If you are not the maintainer, please contact the maintainer to update the SublimeText3.PackageControl package.
ERROR: Cannot process command because of one or more missing mandatory parameters: packageName.
The install of sublimetext3.packagecontrol was NOT successful.
Error while running 'C:\ProgramData\chocolatey\lib\SublimeText3.PackageControl\tools\chocolateyInstall.ps1'.
 See log for details.

SublimeText3.app v3.0.0.3065 [Approved] - Possibly broken
sublimetext3.app package files install completed. Performing other installation steps.
 The install of sublimetext3.app was successful.
  Software installed to 'C:\ProgramData\chocolatey\lib\SublimeText3.app'

Chocolatey installed 3/4 packages. 1 packages failed.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).

Failures
 - sublimetext3.packagecontrol (exited -1) - Error while running 'C:\ProgramData\chocolatey\lib\SublimeText3.PackageControl\tools\chocolateyInstall.ps1'.
 See log for details.
PS C:\Windows\system32>
CREATE FLOWCHART FOR ORACLE PERFORMANCE TUNING IN PYTHON

This flow chart can be used for presentation to explain for easy understanding of performance diagnosis.

MVIEW REFRESH GROUP EXPLAINED

Refresh group is nothing but a package or tool to refresh a group of similar MVIEWS to keep them consistent with refresh.

To create a refresh group on a materialized view or group of mviews, then use the following procedure.

BEGIN
 DBMS_REFRESH.MAKE(name => 'XSCALIBAL',
 list => 'MT,RPMV',
 next_date => sysdate,
 interval => 'sysdate+ 1/1440') ;
END;
/

Check the MVIEWS which are placed under refresh group XSCALIBAL

SQL> col OWNER for a20
SQL> col NAME for a20
SQL> col RNAME for a20
SQL> col INTERVAL for a20
SQL> set lines 200 pages 1000
SQL> SELECT owner,name,rname,interval FROM all_refresh_children;

OWNER                NAME                 RNAME                INTERVAL
-------------------- -------------------- -------------------- --------------------
SYS                  MT                   XSCALIBAL            sysdate+ 1
/1440
SYS                  RPMV                 XSCALIBAL            sysdate+ 1/1440

To manually start the refresh, use refresh function.

BEGIN
 DBMS_REFRESH.REFRESH(name => 'XSCALIBAL') ;
END;
/

There may be situations where the mview refresh happen recursively even though the refresh is not needed To disable the mview refresh group temporarily or permanently, use the following procedure.

BEGIN
 DBMS_REFRESH.CHANGE( name => 'XSCALIBAL',
 next_date => NULL,
 interval => 'NULL') ;
END;
/ 
AWR Snapshots And AWR Objects Are Inconsistent Between Min AND Max Snap ID’S

Snap id from base snapshot table and the certain AWR objects are not consistent between each other with minimum and maximum snapshot totally different from snap_id column of snapshot table wrm$_snapshot.

SQL> SELECT min(snap_id),max(snap_id) FROM wrm$_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          755


SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_sqltext;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         393          805

SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_active_session_history;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         739          755

SQL> SELECT min(snap_id),max(snap_id) FROM wrh$_sql_bind_metadata;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          805

SQL> SELECT min(snap_id),max(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         749          755

Both wrm$_snapshot and wrh$_sql_bind_metadata are compared to find the excess rows in wrh$_sql_bind_metadata which seems to be like a mismatch.

SQL> SELECT distinct a.snap_id FROM wrh$_sql_bind_metadata a WHERE a.snap_id not in (SELECT snap_id FROM wrm$_snapshot) ORDER BY a.snap_id;

   SNAP_ID
----------
       757
       758
       759
       760
       761
       762
       763
       764
       765
       766
       767

   SNAP_ID
----------
       768
       769
       770
       771
       772
       774
       775
       776
       777
       778
       779

   SNAP_ID
----------
       780
       781
       782
       783
       784
       785
       786
       787
       788
       789
       790

   SNAP_ID
----------
       791
       792
       793
       794
       795
       796
       797
       798
       799
       800
       801

   SNAP_ID
----------
       802
       803
       804
       805
       806

Wondered if baseline can be the cause for inconsistency but there is no user created baseline apart from system moving window which is inbuilt.

SQL> col BASELINE_NAME for a20
SQL> SELECT BASELINE_ID,BASELINE_NAME,START_SNAP_ID,END_SNAP_ID,BASELINE_TYPE from wrm$_baseline;

BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID BASELINE_TYPE
----------- -------------------- ------------- ----------- -------------
          0 SYSTEM_MOVING_WINDOW                           MOVING_WINDOW

In case, if there are orphaned rows for tables wrh$_sqltext or wrh$_sql_plan or wrh$_sqlstat, they can be purged with dbms_workload_repository.purge_sql_details little by little. The argument value are number of rows which are before the retention period. Example: If the number of orphaned rows are 1 for wrh$_sqltext, then that one row will be deleted beyond retention. All the rows inside the retention period which are needed for use are contained and not removed.

SQL> EXEC dbms_workload_repository.purge_sql_details(1,&dbid);
Enter value for dbid: 1185489427

PL/SQL procedure successfully completed.

SQL> SELECT count(*) FROM wrh$_sqltext;

  COUNT(*)
----------
      4142

SQL> SELECT count(*) FROM wrh$_sqlstat;

  COUNT(*)
----------
      4655

SQL> SELECT count(*) FROM wrh$_sql_plan;

  COUNT(*)
----------
     31478

Cause:

The SNAP_ID column in the following tables is distinct from the WRM$_SNAPSHOT column with the same name: WRH$_SQLTEXT, WRH$_SQL_PLAN, WRH$_DATAFILE, and WRH$_TEMPFILE. These tables reflect the most recent (so far) snapshot that has data for the questioned SQL.

There are two primary categories of tables for the AWR:
Standard snapshot tables. Data is recorded in snapshots. We merely need to look at the snapshot id to purge these tables.

(2) Data-normalized tables. Tables like WRH$_SQLTEXT, WRH$_SQL_PLAN, WRH$_DATAFILE, and WRH$_TEMPFILE fall under this category. We only record this information once because the SQL text or file name may be quite extensive, and this information is shared by numerous rows in the base snapshot tables.The snap_id in these tables refers to a previous snapshot rather than the snapshot where the data was gathered. Given that this is fuzzy logic, the latest snapshot at which the normalised data is referred need not be precise.

Summary:

The snap_id in WRH$_SQLTEXT should not be compared to the dropped snapshots.

Exadata Power ON OFF Sequence

Power off : DB => Storage => RACK with RoCE switches

crsctl stop cluster -all
dcli -l root -g <db_group> shu -h now
dcli -l root -g <cell_group> shu -h now

Power on : RoCE switches => Storage => DB

Power on RACK and wait for few minutes => Check cell and disks status => Start cluster and database