Anuj Singh Oracle DBA

Monday, 27 June 2011

Oracle DBMS_ADVISOR.SQLACCESS_ADVISOR

Oracle QUICK_TUNE
Oracle quick tune
SQLACCESS_ADVISOR

SQL> connect scott/tiger
Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE <<<<<<<<<<<<<<,---------------
SALGRADE TABLE
T TABLE

from sys

SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM scott.emp WHERE EMPNO = 7788');
END;
/

PL/SQL procedure successfully completed.

SQL> SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune') AS script
FROM dual;
SET PAGESIZE 24

SQL> SQL> 2
SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: emp_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."PK_EMP" */

or

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
exec :sql_stmt := 'SELECT COUNT(*) FROM scott.emp WHERE empno = 999';
exec :task_name := 'ANUJ_QUICKTUNE_TASK';
exec DBMS_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, :task_name, :sql_stmt);

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('ANUJ_QUICKTUNE_TASK') AS script FROM dual;
SET PAGESIZE 24

======================================

begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;

SQL> begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;
/

PL/SQL procedure successfully completed.

col ERROR_MESSAGE format a50
select TASK_NAME, STATUS, PCT_COMPLETION_TIME, ERROR_MESSAGE from DBA_ADVISOR_LOG where TASK_NAME ='anuj_quick_tune';

TASK_NAME STATUS PCT_COMPLETION_TIME ERROR_MESSAGE
------------------------------ ----------- ------------------- --------------------------------------------------
anuj_quick_tune COMPLETED 100

1 row selected.

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('anuj_quick_tune') AS script FROM dual;

SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: anuj_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."SYS_C0022543" */

1 row selected.

-- if you need to terminate the executing task (may be time consuming)

exec DBMS_ADVISOR.CANCEL_TASK(TASK_NAME =>'anuj_quick_tune');

select REC_ID, RANK, BENEFIT, TYPE "Recommendation Type"
from DBA_ADVISOR_RECOMMENDATIONS
where TASK_NAME = 'anuj_quick_tune'
order by RANK;

REC_ID RANK BENEFIT Recommendation Type
---------- ---------- ---------- ------------------------------
1 1 0 RETAINS_ONLY

1 row selected.

select REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS COMMAND
from USER_ADVISOR_ACTIONS where TASK_NAME = 'anuj_quick_tune'
ORDER BY rec_id, action_id;

select SQL_ID, REC_ID, PRECOST, POSTCOST,(PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
from dba_ADVISOR_SQLA_WK_STMTS
where TASK_NAME = 'anuj_quick_tune'

SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
3cx27884dw7ar 1 2 2 0

1 row selected.

DBMS_ADVISOR.quick_tune error

ORA-13600, QSM-00794, ORA-06512 when running DBMS_ADVISOR
ORA-13600: error encountered in Advisor

solution
Create the table in other schama rather than SYS, SYSTEM for DBMS_ADVISOR.quick_tune

SQL> show user
USER is "SYS"

SQL> create table anuj(x number)
/
Table created.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'anuj_quick_tune',
attr1 => 'SELECT * FROM anuj WHERE x = 88');
END;
/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1808
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 180
ORA-06512: at "SYS.PRVT_ADVISOR", line 3636
ORA-06512: at "SYS.DBMS_ADVISOR", line 711
ORA-06512: at line 2

Saturday, 25 June 2011

Unix Search a TEXT in file

Unix Search a staring
Search a TEXT in file
search a word in file

find . -type f -exec grep -il "iapi" {} \;

Friday, 24 June 2011

Is Oracle 32-bit or 64-bit and Oracle version ?

Oracle 32-bit or 64-bit ? oracle 32bit or 64bit oracle 32 bit or 64 bit Oracle version [code]

 
-bash-3.2$ sqlplus / as sysdba


apt-rdbms-01.tus.co.uk:APTDB\sys> 

select length(addr)*4 || '-bits' word_length 
from  v$process where rownum<2

-----
             
 
                                                                                                                 SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


or



SQL> define
DEFINE _DATE           = "04-AUG-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE _RC             = "0" (CHAR)




Oracle vesrion


col COMP_NAME format a50
SQL> r
  1* select COMP_NAME ,VERSION,STATUS from dba_registry

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Application Express                         4.0.2.00.07                    VALID
OWB                                                11.2.0.1.0                     VALID
OLAP Catalog                                       11.2.0.1.0                     VALID
Spatial                                            11.2.0.1.0                     VALID
Oracle Multimedia                                  11.2.0.1.0                     VALID
Oracle XML Database                                11.2.0.1.0                     VALID
Oracle Text                                        11.2.0.1.0                     VALID
Oracle Expression Filter                           11.2.0.1.0                     VALID
Oracle Rules Manager                               11.2.0.1.0                     VALID
Oracle Workspace Manager                           11.2.0.1.0                     VALID
Oracle Database Catalog Views                      11.2.0.1.0                     VALID
Oracle Database Packages and Types                 11.2.0.1.0                     VALID
JServer JAVA Virtual Machine                       11.2.0.1.0                     VALID
Oracle XDK                                         11.2.0.1.0                     VALID
Oracle Database Java Packages                      11.2.0.1.0                     VALID
OLAP Analytic Workspace                            11.2.0.1.0                     VALID
Oracle OLAP API                                    11.2.0.1.0                     VALID

17 rows selected.



perl $ORACLE_HOME/OPatch/opatch.pl version
Invoking OPatch 11.1.0.6.6

OPatch Version: 11.1.0.6.6

OPatch succeeded.


SELECT PLATFORM_NAME
    FROM V$TRANSPORTABLE_PLATFORM
    WHERE PLATFORM_ID =
       ( SELECT PLATFORM_ID FROM V$DATABASE );


SQL --  !lsnrctl version

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2011 09:09:24

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
        TNS for Linux: Version 11.2.0.1.0 - Production
        Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production,,
The command completed successfully

Thursday, 23 June 2011

To Change the permissions On Unix

To Change the permissions On Unix
Unix permissions
Unix permission

666 : Default permission
002 : - umask value
664 : final permission

#=root

# chmod 777 *.log

u user
g group
o others

Owner Group others Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute
5 (u+Rx) 5 (g+Rx) 5 (o+Rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character equivalents can be used in the chmod command .

#chmod o+rwx *.log
#chmod g+r *.log
#chmod -Rx *.log

chmod -R g+rw hft/

-R recursive

# chown -R oinstall.dba *

Owner Group The others Permission

7 7 7 read + write + execute
6 6 6 write + execute
5 5 5 read + execute
4 4 4 read only
2 2 2 write only
1 1 1 execute only

Oracle Create Soft link

-bash-3.2$ pwd
/usr/lib/oracle/instantclient10_1
-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1

ln -s libclntsh.so.10.1 libclntsh.so.11.1

-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 23 09:28 libclntsh.so.11.1 -> libclntsh.so.10.1
-bash-3.2$

Monday, 20 June 2011

Oracle patch info

on new metalink

for Oracle and grid patch info !!!!!!!!

KA958 Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases

KA958

https://support.oracle.com/support/?page=sptemplate&sptemplate=km-article&documentId=KA958

Quick Links

https://updates.oracle.com/Orion/QuickLinks/process_form?type=nonapps

oracle@apt-amd-02:/opt/app/oracle/product/11.2/OPatch> ./opatch lsinventory -bugs_fixed

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Oracle Home : /opt/app/oracle/product/11.2

Central Inventory : /opt/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.6

OUI version : 11.2.0.1.0

OUI location : /opt/app/oracle/product/11.2/oui

Log file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch2011-06-20_14-51-20PM.log

Patch history file: /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2011-06-20_14-51-20PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

opatch lsinventory -bugs_fixed | grep -i 'GI PSU'

opatch lsinventory -bugs_fixed | grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

to download opatch utility go to

metalink >> patch and update

then search for 6880880 patch and OS

-bash-3.00$ opatch lsinventory -detail

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

Oracle Home : /opt/oracle/product/10.2

Central Inventory : /opt/oracle/oraInventory

from : /var/opt/oracle/oraInst.loc

OPatch version : 10.2.0.4.3

OUI version : 10.2.0.4.0

OUI location : /opt/oracle/product/10.2/oui

Log file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/opatch2011-07-22_11-14-02AM.log

Lsinventory Output file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory2011-07-22_11-14-02AM.txt

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

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

There are 3 products installed in this Oracle Home.

Installed Products (188):

Agent Required Support Files 10.2.0.1.0

Agent Required Support Files Patch 10.2.0.4.0

Assistant Common Files 10.2.0.1.0

=======================

opatch lsinventory

select * from dba_registry_history;

select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

select * from sys.registry$history;

col COMP_NAME for a50

set lines 200

col action_time for a30

col comments for a30

col ACTION for a15

select action_time, action,namespace, version, id, bundle_series, comments from registry$history;

select action_time, action, version, id, comments from dba_registry_history order by action_time;

select COMMENTS, ACTION_TIME from sys.registry$history;

select COMP_NAME, VERSION, STATUS from dba_registry;

How to Use MOS Patch Planner to Check and Request the Conflict Patches? (Doc ID 1317012.1)

google it and find patch info

Map of Public Vulnerability to Advisory/Alert

http://www.oracle.com/technetwork/topics/security/public-vuln-to-advisory-mapping-093627.html

===========

to download

https://updates.oracle.com/download/6880880.html

unzip p6880880_210000_Linux-x86-64.zip -d $ORACLE_HOME/

set serveroutput on;

execute dbms_optim_bundle.getBugsforBundle;

12.2.0.1.190416DBRU:

Bug: 25405100, fix_controls: 25405100

execute dbms_optim_bundle.listBundlesWithFCFixes; ----<<<<

bundleId: 180116, bundleName: 12.2.0.1.180116DBRU

bundleId: 181017, bundleName: 12.2.0.1.181017DBRU

bundleId: 190115, bundleName: 12.2.0.1.190115DBRU

bundleId: 190416, bundleName: 12.2.0.1.190416DBRU

PL/SQL procedure successfully completed.

====

with a as

(select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

select x.*

from a,

xmltable('InventoryInstance/patches/*' passing a.patch_output

columns patch_id number path 'patchID',

patch_uid number path 'uniquePatchID',

description varchar2(80) path 'patchDescription',

applied_date varchar2(30) path 'appliedDate',

sql_patch varchar2(8) path 'sqlPatch',

rollbackable varchar2(8) path 'rollbackable') x

Oracle Database 19c Release Update & Release Update Revision October 2022 Known Issues (Doc ID 19202210.9)

RDBMS Online Patching Aka Hot Patching (Doc ID 761111.1)

https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=6880880



cd $ORACLE_HOME
mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

ls -ld OPatch*
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

===

cd $ORACLE_HOME
unzip -oq /home/oracle/Downloads/p6880880_210000_Linux-x86-64.zip

ls -ld OPatch*
drwxr-x---. 15 oracle oinstall 4096 Apr 15 14:41 OPatch
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

db_1]$

===

export PATH=$ORACLE_HOME/OPatch:$PATH

opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

or


./opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

***********

export OPATCH_DEBUG=TRUE  <<<< if requited


 export PATH=$ORACLE_HOME/OPatch:$PATH
 opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"

opatch lsinventory | grep -E "(^Patch)|(^Sub-patch)"


 export PATH=$ORACLE_HOME/OPatch:$PATH

to clean ..
 export PATH=$ORACLE_HOME/OPatch:$PATH
opatch util listorderedinactivepatches
opatch util deleteinactivepatches
opatch util cleanup


mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`
mv: cannot move 'OPatch' to 'OPatch.2024-12-13': Permission denied

[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ logout

login as root 

[root@oragrid 19.2.0]# cd /u01/app/21.3.0/grid

[root@oragrid grid]# id
uid=0(root) gid=0(root) groups=0(root)
[root@oragrid grid]# mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

[root@oragrid grid]# ls -ld OPatch*
drwxr-xr-x 13 grid oinstall 303 Jul 27  2021 OPatch.2024-12-13
[root@oragrid grid]#



As root 
[root@oragrid ~]# cd /u01/app/21.3.0/grid
[root@oragrid grid]# unzip -oq /home/grid/p6880880_210000_Linux-x86-64.zip

[root@oragrid grid]# ls -ld OPatch*
drwxr-x--- 15 root root     4096 Oct  4 19:48 OPatch
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2024-12-13


[root@oragrid grid]# pwd
/u01/app/21.3.0/grid


[root@oragrid grid]# chown -R grid:oinstall OPatch/

to download patch


https://support.oracle.com/support/?anchorId=&documentId=KA958&page=sptemplate&sptemplate=km-article


Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases
KA958

===================

for Grid



cd /u01/app/21.3.1/grid
mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall 303 Jul 27  2021 OPatch.2026-01-02


unzip -qqd /u01/app/21.3.1/grid /home/grid/p6880880_230000_Linux-x86-64.zip

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2026-01-02
drwxr-x--- 14 root root     4096 Oct  7 15:16 OPatch


chown -R grid:oinstall OPatch

[root@oragrid grid]# chown -R grid:oinstall OPatch

[root@oragrid grid]# ls -ltrd OPatch*
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2026-01

========================

opatch listorderedinactivepatches
opatch util deleteinactivepatches






set linesize 300 pagesize 300
  col action_time for a20
  col id for a10
  col action for a12
  col COMMENTS for a25
    select substr(action_time,1,30) action_time,substr(id,1,10) id,substr(action,1,10) action,substr(version,1,8) version,
	substr(BUNDLE_SERIES,1,6) bundle,substr(comments,1,20) comments 
	from registry$history;
  
  

set lines 132  verify  off head off  feedback on  long 1000
select distinct
'======================================================'||chr(10)||
'id...........................: '||id   		||Chr(10)||
'ACTION.......................: '||ACTION 		||Chr(10)||
'ACTION_TIME..................: '||ACTION_TIME 	||Chr(10)||
'NAMESPACE....................: '||NAMESPACE   	||Chr(10)||
'VERSION......................: '||VERSION   	||Chr(10)||
'BUNDLE_SERIES................: '||BUNDLE_SERIES||Chr(10)||
'COMMENTS.....................: '||COMMENTS    
from registry$history  ---<<<<---DBA_REGISTRY_HISTORY does not get updated  Bundle Patch
order by 1
/

set lines 132  verify  on head on  feedback on  long 1000


set linesize 300
with /*+ PARALLEL */  a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
     select x.*
       from a,
     xmltable('InventoryInstance/patches/*'
     passing a.patch_output
     columns
        patch_id number path 'patchID',
        patch_uid number path 'uniquePatchID',
        description varchar2(80) path 'patchDescription',
     constituent number path 'constituent',
     patch_type varchar2(20) path 'patchType',
     rollbackable varchar2(20) path 'rollbackable',
     sql_patch varchar2(8) path 'sqlPatch',
     DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'
   ) x
/



set lines 132 verify off head off feedback on long 1000

select distinct
  '======================================================'||chr(10)||
  'PATCH_ID.....................: '||PATCH_ID           ||chr(10)||
  'PATCH_UID....................: '||PATCH_UID          ||chr(10)||
  'ACTION.......................: '||ACTION             ||chr(10)||
  'ACTION_TIME..................: '||ACTION_TIME        ||chr(10)||
  'STATUS.......................: '||STATUS             ||chr(10)||
  'VERSION......................: '||VERSION            ||chr(10)||
  'BUNDLE_SERIES................: '||BUNDLE_SERIES      ||chr(10)||
  'BUNDLE_ID....................: '||BUNDLE_ID          ||chr(10)||
  'INSTALL_ID...................: '||INSTALL_ID         ||chr(10)||
  'FLAGS........................: '||FLAGS              ||chr(10)||
  'LOGFILE......................: '||LOGFILE            ||chr(10)||
  'POST_LOGFILE.................: '||POST_LOGFILE       ||chr(10)||
  'DESCRIPTION..................: '||DESCRIPTION
from sys.registry$sqlpatch
where 1=1
and STATUS not like '%ERROR%'
order by 1
/

select distinct
  '======================================================'||chr(10)||
  'PATCH_ID.....................: '||PATCH_ID           ||chr(10)||
  'PATCH_UID....................: '||PATCH_UID          ||chr(10)||
  'ACTION.......................: '||ACTION             ||chr(10)||
  'ACTION_TIME..................: '||ACTION_TIME        ||chr(10)||
  'STATUS.......................: '||STATUS             ||chr(10)||
  'VERSION......................: '||VERSION            ||chr(10)||
  'BUNDLE_SERIES................: '||BUNDLE_SERIES      ||chr(10)||
  'BUNDLE_ID....................: '||BUNDLE_ID          ||chr(10)||
  'INSTALL_ID...................: '||INSTALL_ID         ||chr(10)||
  'FLAGS........................: '||FLAGS              ||chr(10)||
  'LOGFILE......................: '||LOGFILE            ||chr(10)||
  'POST_LOGFILE.................: '||POST_LOGFILE       ||chr(10)||
  'DESCRIPTION..................: '||DESCRIPTION
from sys.registry$sqlpatch
where 1=1
--and STATUS not like '%ERROR%'
order by 1
/

set lines 132 verify on head on feedback on long 1000





alter session set nls_date_format='dd-mm-yyyy hh24:mi';
SET LINESIZE 500 PAGESIZE 1000  SERVEROUT ON LONG 2000000

COLUMN action_time FORMAT A26
COLUMN action FORMAT A29
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A50
COLUMN namespace FORMAT A20
COLUMN status FORMAT A16
COLUMN version FORMAT A10

--spool check_patches.txt

SELECT 
action_time
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;







SQL> alter session set "_oracle_script" = true;
SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed open read write;
SQL> create table registry$sqlpatch_org as select * from registry$sqlpatch ;
SQL> delete from registry$sqlpatch where PATCH_ID=xxx;
SQL> commit;
SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed OPEN READ ONLY;  
SQL> alter session set "_oracle_script"=FALSE;





Datapatch precheck failed : Release update is BEING installed to PDB but is not installed in CDB$ROOT.This will cause a patch mismatch between this PDB and CDB$ROOT (Doc ID 2604940.1)
Execute datapatch individually in each of the containers, starting with CDB$ROOT :


1. Apply datapatch is CDB$ROOT :
  ===================================
  ./datapatch -verbose -pdbs CDB\$ROOT

2. Apply datapatch is PDB :
  ===================================
  $ ./datapatch -verbose -pdbs <pdbname>

3. Apply datapatch is PDB$SEED :
  ==================================
  SQL> alter session set container=PDB$SEED;
  SQL> alter session set "_oracle_script"=TRUE;
  SQL> alter pluggable database pdb$seed close immediate instances=all;
  SQL> alter pluggable database pdb$seed OPEN READ WRITE;
  SQL> select open_mode from v$database;
  SQL> exit

  Execute: ./datapatch -verbose -pdbs PDB\$SEED

  SQL> alter session set "_oracle_script"=FALSE;
  
4. show pdbs -> If this shows the PDB$SEED in restricted mode, bounce the database once.

5. Check for the relevant patch existence in the registry$sqlpatch for all the containers (CDB$ROOT, PDB$SEED and PDB) :

  set linesize 150
  col logfile for a90
  select patch_id, action, logfile, status from registry$sqlpatch;



 $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/sqlpatch -I$ORACLE_HOME/rdbms/admin -I$ORACLE_HOME/sqlpatch/lib $ORACLE_HOME/sqlpatch/sqlpatch.pl $@

SQL Patching tool version 12.2.0.1.0 Production on Sun May 24 12:47:20 2026
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_116489_2026_05_24_12_47_20/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 260120 in the binary registry and ID 260120 in PDB CDB$ROOT, ID 260120 in PDB PDB$SEED, ID 260120 in PDB ORCLPDB1

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB1
    Nothing to roll back
    Nothing to apply

SQL Patching tool complete on Sun May 24 12:47:35 2026