Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
-----
crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
=======
RAC: Frequently Asked Questions (RAC FAQ) (Doc ID 220970.1)
p6880880_210000_Linux-x86-64.zip
p34762026_190000_Linux-x86-64.zip >>>>>2.8GB file
unzip p34762026_190000_Linux-x86-64.zip
Archive: p34762026_190000_Linux-x86-64.zip
creating: 34762026/
inflating: 34762026/bundle.xml
creating: 34762026/34768569/
creating: 34762026/34768569/files/
[grid@srv1 ~]$ cd 34762026
[grid@srv1 34762026]$ pwd
/home/grid/34762026
[grid@srv1 34762026]$ ls -ltr
total 168
drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768569
drwxr-x---. 5 grid oinstall 81 Feb 1 2023 34765931
drwxr-x---. 4 grid oinstall 48 Feb 1 2023 33575402
-rw-r--r--. 1 grid oinstall 0 Feb 1 2023 README.txt
drwxr-x---. 2 grid oinstall 4096 Feb 1 2023 automation
drwxr-x---. 4 grid oinstall 48 Feb 1 2023 34863894
drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768559
-rw-rw-r--. 1 grid oinstall 5824 Feb 1 2023 bundle.xml
-rw-rw-r--. 1 grid oinstall 156720 Feb 1 2023 README.html
patch conflicts check.
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$ echo $ORACLE_HOME
/u01/app/19.0.0/grid
---
stop datacases only !!!!!!!
as root to patch GI home:
[root@srv1 ~]# cd /home/grid
export ORACLE_HOME=/u01/app/19.0.0/grid
export PATH=$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
[root@srv1 ~]# export ORACLE_HOME=/u01/app/19.0.0/grid
[root@srv1 ~]# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@srv1 ~]# echo $ORACLE_HOME
/u01/app/19.0.0/grid
[root@srv1 ~]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
opatch lsinventory | grep 19.18
[root@srv1 grid]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
OPatchauto session is initiated at Sun May 5 09:06:22 2024
System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2024-05-05_09-06-32AM.log.
Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2024-05-05_09-06-38AM.log
The id for this session is EU5X
Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid
Patch applicability verified successfully on home /u01/app/19.0.0/grid
Executing patch validation checks on home /u01/app/19.0.0/grid
Patch validation checks successfully completed on home /u01/app/19.0.0/grid
Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.0.0/grid
Prepatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2024-05-05_09-08-01AM.log
CRS service brought down successfully on home /u01/app/19.0.0/grid
Start applying binary patch on home /u01/app/19.0.0/grid
Binary patch applied successfully on home /u01/app/19.0.0/grid
Running rootadd_rdbms.sh on home /u01/app/19.0.0/grid
Successfully executed rootadd_rdbms.sh on home /u01/app/19.0.0/grid
Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.0.0/grid
Postpatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2024-05-05_09-19-15AM.log
CRS service started successfully on home /u01/app/19.0.0/grid
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:srv1
SIHA Home:/u01/app/19.0.0/grid
Version:19.0.0.0.0
Summary:
==Following patches were SUCCESSFULLY applied:
Patch: /home/grid/34762026/33575402
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34765931
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34768559
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34768569
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34863894
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
OPatchauto session completed at Sun May 5 09:21:37 2024
Time taken to complete the session 15 minutes, 6 seconds
======================
as grid user
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18
Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)"
Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)"
Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)"
32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988
[grid@srv1 ~]$
$ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18
Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)"
Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)"
Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)"
32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch 34863894 : applied on Sun May 05 09:18:11 GST 2024
Patch 34768569 : applied on Sun May 05 09:17:40 GST 2024
Patch 34768559 : applied on Sun May 05 09:17:12 GST 2024
Patch 34765931 : applied on Sun May 05 09:12:51 GST 2024
Patch 33575402 : applied on Sun May 05 09:10:03 GST 2024
[grid@srv1 ~]$
rid@srv1 ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[grid@srv1 ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]
[grid@srv1 ~]$ crsctl query has releasepatch
Oracle Clusterware release patch level is [3161362881] and the complete list of patches [33575402 34765931 34768559 34768569 34863894 ] have been applied on the local node. The release patch string is [19.18.0.0.0].
[grid@srv1 ~]$ crsctl query has softwarepatch
Oracle Clusterware patch level on node srv1 is [3161362881].
crsctl stat res -t – to check the status of the resources in the Oracle Restart stack managed by OHASD
crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADISK.dg
ONLINE ONLINE srv1 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE srv1 STABLE
ora.OCRDISK.dg
ONLINE ONLINE srv1 STABLE
ora.asm
ONLINE ONLINE srv1 Started,STABLE
ora.ons
OFFLINE OFFLINE srv1 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE srv1 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE srv1 STABLE
ora.oradb.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
--------------------------------------------------------------------------------
[grid@srv1 ~]$
to stop has
[grid@srv1 ~]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv1'
CRS-2673: Attempting to stop 'ora.DATADISK.dg' on 'srv1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv1' succeeded
CRS-2677: Stop of 'ora.DATADISK.dg' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.OCRDISK.dg' on 'srv1'
CRS-2677: Stop of 'ora.OCRDISK.dg' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'srv1'
CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'srv1'
CRS-2677: Stop of 'ora.evmd' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'srv1'
CRS-2677: Stop of 'ora.cssd' on 'srv1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[grid@srv1 ~]$
crsctl start has – to manually start the Oracle Restart stack when running disabled or after manually stopping it
crsctl stop has [-f] – to manually stop the Oracle Restart stack. The -f option
crsctl enable has – to enable the stack for automatic startup at server reboot
crsctl disable has – to disable the stack for automatic startup at server reboot
crsctl config has – to display the configuration of Oracle Restart
crsctl check has – to check the current status of Restart
Some of the crsctl commands used for clusters may also be used for Oracle Restart. A typical example follows but there are many more:
======
Patch 35940989 is a superset of patch 35642822
Note
This patch was originally replaced by patch 35940989. The most recent replacement for this patch is 36916690.
Replacement Options (Patches or Patchsets known to Include or Supersede this Patch)
35940989 GI RELEASE UPDATE 19.22.0.0.0 Patch
Oracle 19c Deinstall Utility on OL/RHEL 8 fails with "ERROR:null" (Doc ID 2738856.1)
./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/app/oracle/product/19.0.0/dbhome_1"}
[INS-04013] The argument [-deinstall] is not supported. Run /u01/app/oracle/product/19.0.0/dbhome_1/deinstall/deinstall for deinstallation.
use deinstall
on oracle 19c!!!!!!!!!!!!!!!!!
[oracle@oracledb dbhome_1]$ export CV_ASSUME_DISTID=OL7
[oracle@oracledb dbhome_1]$ ./deinstall
-bash: ./deinstall: Is a directory
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd deinstall/
[oracle@oracledb deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DECONFIG TOOL START ############
######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /u01/app/oracle/product/19.0.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
## [END] Install check configuration ##
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2024-04-02_02-48-23PM.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2024-04-02_02-48-23PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []: Database Check Configuration END
######################### DECONFIG CHECK OPERATION END #########################
####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/19.0.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2024-04-02_02-48-23PM.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2024-04-02_02-48-23PM.log
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
######################### DECONFIG CLEAN OPERATION END #########################
####################### DECONFIG CLEAN OPERATION SUMMARY #######################
#######################################################################
############# ORACLE DECONFIG TOOL END #############
Using properties file /tmp/deinstall2024-04-02_02-47-44PM/response/deinstall_2024-04-02_02-48-20-PM.rsp
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL TOOL START ############
####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to oracledb
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2024-04-02_02-47-44PM/oraInst.loc
Setting oracle.installer.local to false
## [END] Preparing for Deinstall ##
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node : Done
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/version/db_1'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
## [START] Oracle install clean ##
## [END] Oracle install clean ##
######################### DEINSTALL CLEAN OPERATION END #########################
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.
Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'oracledb'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL TOOL END #############
from web https://connor-mcdonald.com/2020/08/07/modifying-scheduler-windows/
set linesize 500 pagesize 400
col WINDOW_NAME for a25
col REPEAT_INTERVAL for a70
col DURATION for a25
col SCHEDULE_OWNER for a14
select SCHEDULE_OWNER ,window_name, repeat_interval, duration from dba_scheduler_windows
--where 1=1
order by WINDOW_NAME
;
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 *
9 rows selected.
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
for i in 1 .. x.count
loop
DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/
output !!!
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
SQL> SQL> 2 3 4 5 for i in 1 .. x.count
6
7 loop
8 DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
9
10 DBMS_SCHEDULER.set_attribute(
11 name => 'SYS.'||x(i)||'_WINDOW',
12 attribute => 'REPEAT_INTERVAL',
13 value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/ 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
======================================================================
from
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
to
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW FREQ=WEEKLY;BYDAY=SAT;BYHOUR=03;BYMINUTE=0;BYSECOND=0 +000 01:00:00
How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1)
want use sql patch on below sql for hint select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/
select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
and d.deptno> 10
group by d.deptno,d.dname;
set linesize 400
col sql_text for a50
select SQL_ID,SQL_TEXT from gv$sql where 1=1 and SQL_TEXT like '%and d.deptno> 10%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de
pt d where e.deptno = d.deptno and d.deptno> 10 gr
oup by d.deptno,d.dname
from web !!
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'g2z10tbxyz6b0',
name => 'validate_fk',
-- hint_text => 'ignore_optim_embedded_hints'
-- hint_text => 'parallel(a@sel$1 8)' -- worked
-- hint_text => 'parallel(8)' -- worked
-- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked
hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
);
dbms_output.put_line(v1);
end;
/
SET SERVEROUTPUT ON
DECLARE
v_sql_id VARCHAR2 (13) := '';
v_patch_name VARCHAR2 (30);
v_hint VARCHAR2 (4096) := 'NO_QUERY_TRANSFORMATION(@"SEL$1")';
BEGIN
v_patch_name :=
DBMS_SQLDIAG.create_sql_patch (sql_id => v_sql_id, hint_text => v_hint);
DBMS_OUTPUT.put_line (v_patch_name);
END;
/
===
set serveroutput on
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'fzsf6kw7q2vxt',
name => 'optimizer_extended_cursor_sharing_rel',
hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
);
dbms_output.put_line(v1);
end;
/
set linesize 400
col sql_text for a50
col name for a37
select name, status, created, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel';
set linesize 400
set numf 99999999999999999999999999
select SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,plan_hash_value plan_hash,EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt';
SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXACT_MATCHING_SIGNATURE
------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------------- ---------------------------
fzsf6kw7q2vxt 0 2006461124 9282672672555810008
fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124 9282672672555810008
col OUTLINE_HINTS for a40
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = 9282672672555810008)
) x;
OUTLINE_HINTS
----------------------------------------
opt_param('_optimizer_extended_cursor_sh
aring_rel' 'none')
If needed patch can be disabled using
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('optimizer_extended_cursor_sharing_rel', 'STATUS', 'DISABLED');
If you want to drop the patch,
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=> 'optimizer_extended_cursor_sharing_rel');
===
Test ----
declare
v1 varchar2(128);
primary:sys@IBRAC-ibrac2 sqlplus> 2 3 begin
v1 := dbms_sqldiag.create_sql_patch(
4 5 sql_id => 'fzsf6kw7q2vxt',
6 name => 'optimizer_extended_cursor_sharing_rel',
7 hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
8 );
9 dbms_output.put_line(v1);
10 end;
11 /
optimizer_extended_cursor_sharing_rel
PL/SQL procedure successfully completed.
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 8311823694834541849)) x;
below hint for Bind mismatch(33)? and test
/*+ opt_param('_optimizer_use_feedback' 'false')
================================================
set pagesize 60
set linesize 300
set trimspool on
column sql_text format a40
column plan_name format a30
column signature format 999999999999999999999
column hint format a50 wrap word
select
prf.plan_name,
prf.sql_text,
prf.signature,
extractvalue(value(hnt),'.') hint
from (
select
so.name plan_name,
so.signature,
so.category,
so.obj_type,
so.plan_id,
st.sql_text,
sod.comp_data
from
sqlobj$ so,
sqlobj$data sod,
sql$text st
where
sod.signature = so.signature
and st.signature = so.signature
and st.signature = sod.signature
and sod.category = so.category
and sod.obj_type = so.obj_type
and sod.plan_id = so.plan_id
and so.obj_type = 3
-- and so.name = 'SQL_Patch_xxxxxxxx'
order by signature, obj_type, plan_id
) prf,
table ( select xmlsequence(
extract(xmltype(prf.comp_data),'/outline_data/hint')
)
from dual ) hnt;
https://github.com/tanelpoder/tpt-oracle/blob/master/nonshared2.sql
define 2='48vf4pg4g5510'
set linesize 200
col REASON_XML for a40
col REASON for a20
define 1=100
COL nonshared_sql_id HEAD SQL_ID FOR A13
COL nonshared_child HEAD CHILD# FOR A10
COL nonshared_reason_and_details HEAD REASON FOR A60 WORD_WRAP
COL reason_xml FOR A100 WORD_WRAP &1
col REASON for a20
BREAK ON nonshared_sql_id
SELECT
'&2' nonshared_sql_id
, EXTRACTVALUE(VALUE(xs), '/ChildNode/ChildNumber') nonshared_child
, EXTRACTVALUE(VALUE(xs), '/ChildNode/reason') || ': ' || EXTRACTVALUE(VALUE(xs), '/ChildNode/details') nonshared_reason_and_details
, VALUE(xs) reason_xml
FROM TABLE (
SELECT XMLSEQUENCE(EXTRACT(d, '/Cursor/ChildNode')) val FROM (
SELECT
--XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))
-- the XMLSERIALIZE + XMLTYPE combo is included for avoiding a crash in qxuageag() XML aggregation function
XMLTYPE (XMLSERIALIZE( DOCUMENT XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))) ) d
FROM
v$sql_shared_cursor c
, TABLE(XMLSEQUENCE(XMLTYPE('<doc>'||c.reason||'</doc>'))) x
WHERE
c.sql_id = '&2' and c.child_number < 5
)
) xs
/
Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)
====
example from Web
-- purge_cursor.sql
DECLARE
l_name VARCHAR2(64);
l_sql_text CLOB;
BEGIN
-- get address, hash_value and sql text
SELECT address||','||hash_value, sql_fulltext
INTO l_name, l_sql_text
FROM v$sqlarea
WHERE sql_id = '&&sql_id.';
-- not always does the job
SYS.DBMS_SHARED_POOL.PURGE (
name => l_name,
flag => 'C',
heaps => 1
);
-- create fake sql patch
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
sql_text => l_sql_text,
hint_text => 'NULL',
name => 'purge_&&sql_id.',
description => 'PURGE CURSOR',
category => 'DEFAULT',
validate => TRUE
);
-- drop fake sql patch
SYS.DBMS_SQLDIAG.DROP_SQL_PATCH (
name => 'purge_&&sql_id.',
ignore => TRUE
);
END;
/
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'g2z10tbxyz6b0',
name => 'validate_fk',
hint_text => 'ignore_optim_embedded_hints'
-- hint_text => 'parallel(a@sel$1 8)' -- worked
-- hint_text => 'parallel(8)' -- worked
-- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked
Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)
alter system set "_fix_control"='17443547:ON';
set linesize 300 pagesize 300
col fix_control_value for a18
col sql_feature for a30
select bugno,value,
case
when value=1 then 'fix_control_on'
when value=0 then 'fix_control_off'
end as fix_control_value,
optimizer_feature_enable,
sql_feature,
is_default,
event,
con_id,
description
from v$system_fix_control
where bugno=17443547
;
BUGNO VALUE FIX_CONTROL_VALUE OPTIMIZER_FEATURE_ENABLE SQL_FEATURE IS_DEFAULT EVENT CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
17443547 1 fix_control_on 12.2.0.1 QKSFM_CURSOR_SHARING_17443547 1 0 1 Adaptive Cursor Sharing for single bind constant expressions
sqlplus> alter system set "_fix_control"='17443547:OFF';
System altered.
BUGNO VALUE FIX_CONTROL_VALUE OPTIMIZER_FEATURE_ENABLE SQL_FEATURE IS_DEFAULT EVENT CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
17443547 0 fix_control_off 12.2.0.1 QKSFM_CURSOR_SHARING_17443547 0 0 1 Adaptive Cursor Sharing for single bind constant expressions
===
to check fix control on prod and standby
col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_on" from v$system_fix_control where 1=1 and value=1 ;
col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_off" from v$system_fix_control where 1=1 and value=0 ;
col DB_UNIQUE_NAME for a15
col "v$parameter" for 99999
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "v$parameter" from v$parameter ;
SET LINESIZE 300
COLUMN name FORMAT A30
COLUMN current_value FORMAT A30
COLUMN sid FORMAT A8
COLUMN spfile_value FORMAT A30
col DB_UNIQUE_NAME for a15
SELECT p.name,
SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME,
i.instance_name AS sid,
p.value AS current_value,
sp.sid,
sp.value AS spfile_value
FROM v$spparameter sp,
v$parameter p,
v$instance i
WHERE 1=1
and sp.name = p.name
--AND sp.value != p.value
and sp.name like '%fix_control%';
SQL> show user
USER is "SCOTT"
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
*****
as sys or system
grant below to scott !!
GRANT SELECT ON v_$session TO scott ;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott ;
GRANT SELECT ON v_$sql_plan TO scott ;
GRANT SELECT ON v_$sql TO scott ;
===
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 45sygvgu8ccnz, child number 0
-------------------------------------
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49999 | 1464K| 273 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
