--- @dbupgdiag.sql -- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - -- NAME: DBUPGDIAG.SQL -- Version: 1.2 -- Executed as SYS as sysdba -- ------------------------------------------------------------------------ -- AUTHOR: -- Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group -- Copyright 2008, Oracle Corporation -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is intended to provide a user friendly output to diagonise -- the status of the database before (or) after upgrade. The script will -- create a file called db_upg_diag__ .log in your local -- working directory. This does not make any DDL / DML modifications. -- -- This script will work in both Windows and Unix platforms from database -- version 9.2 or higher. -- ------------------------------------------------------------------------ -- DISCLAIMER: -- This script is provided for educational purposes only. It is NOT -- supported by Oracle World Wide Technical Support. -- The script has been tested and appears to work as intended. -- You should always run new scripts on a test instance initially. -- ------------------------------------------------------------------------ -- -- col TODAY NEW_VALUE _DATE col VERSION NEW_VALUE _VERSION set termout off select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL; select version from v$instance; set termout on set echo off set feedback off set head off set verify off Prompt PROMPT Enter location for Spooled output: Prompt DEFINE log_path = &1 column timecol new_value timestamp column spool_extension new_value suffix SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM sys.dual; column output new_value dbname SELECT value || '_' output FROM v$parameter WHERE name = 'db_name'; spool &log_path/db_upg_diag_&&dbname&×tamp&&suffix set linesize 150 set pages 100 set trim on set trims on col Compatible for a35 col comp_id for a12 col comp_name for a40 col org_version for a11 col prv_version for a11 col owner for a12 col object_name for a40 col object_type for a40 col Wordsize for a25 col Metadata for a8 col 'Initial DB Creation Info' for a35 col 'Total Invalid JAVA objects' for a45 col 'Role' for a30 col 'User Existence' for a27 col "JAVAVM TESTING" for a15 Prompt Prompt set feedback off head off select LPAD('*** Start of LogFile ***',50) from dual; select LPAD('Oracle Database Upgrade Diagnostic Utility',44)|| LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual; Prompt Prompt =============== Prompt Hostname Prompt =============== select host_name from v$instance; Prompt Prompt =============== Prompt Database Name Prompt =============== select name from v$database; Prompt Prompt =============== Prompt Database Uptime Prompt =============== SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time" FROM v$instance; Prompt Prompt ================= Prompt Database Wordsize Prompt ================= SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize" FROM v$process; Prompt Prompt ================ Prompt Software Version Prompt ================ SELECT * FROM v$version; Prompt Prompt ============= Prompt Compatibility Prompt ============= SELECT 'Compatibility is set as '||value Compatible FROM v$parameter WHERE name ='compatible'; Prompt Prompt ================ Prompt Archive Log Mode Prompt ================ Prompt archive log list Prompt Prompt ================ Prompt Auditing Check Prompt ================ Prompt set head on show parameter audit Prompt Prompt ================ Prompt Cluster Check Prompt ================ show parameter cluster_database Prompt DOC ################################################################ If CLUSTER_DATABASE is set to TRUE, change it to FALSE before upgrading the database ################################################################ # Prompt Prompt =========================================== Prompt Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$) Prompt =========================================== select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name; Prompt Prompt ============================================================================ Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing Prompt ============================================================================ Prompt set head off select count(*) as Records from sys.aud$ where dbid is null; Prompt Prompt Prompt ============================================================================================ Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed Prompt ============================================================================================ set head off select count(*) from system.aud$ where dbid is null; Prompt Prompt Prompt ============================================================================= Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing Prompt ============================================================================= set head off select count(*) from sys.fga_log$ where dbid is null; Prompt Prompt prompt Prompt ========================================== Prompt Oracle Label Security is installed or not Prompt ========================================== set head off SELECT case count(schema) WHEN 0 THEN 'Oracle Label Security is NOT installed at database level' ELSE 'Oracle Label Security is installed ' END "Oracle Label Security Check" FROM dba_registry WHERE schema='LBACSYS'; Prompt Prompt ================ Prompt Number of AQ Records in Message Queue Tables Prompt ================ Prompt SET SERVEROUTPUT ON SIZE 100000 declare V_COUNT NUMBER; cursor c1 is select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS'); begin for c in c1 loop execute immediate 'select count(1) from ' || c.owner || '.' || c.queue_table into v_count; dbms_output.put_line(c.owner || ' - ' || c.queue_table || ' - ' || v_count); end loop; END; / Prompt Prompt ================ Prompt Time Zone version Prompt ================ Prompt SELECT version from v$timezone_file; Prompt Prompt ================ Prompt Local Listener Prompt ================ Prompt select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener'; Prompt Prompt ================ Prompt Default and Temporary Tablespaces By User Prompt ================ Prompt set head on COLUMN USERNAME FORMAT A28 COLUMN TEMPORARY_TABLESPACE FORMAT A22 COLUMN DEFAULT_TABLESPACE FORMAT A22 SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS; Prompt Prompt Prompt ================ Prompt Component Status Prompt ================ Prompt SET SERVEROUTPUT ON; DECLARE ORG_VERSION varchar2(12); PRV_VERSION varchar2(12); P_VERSION VARCHAR2(10); BEGIN SELECT version INTO p_version FROM registry$ WHERE cid='CATPROC' ; IF SUBSTR(p_version,1,5) = '9.2.0' THEN DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| RPAD('Status',10) ||RPAD('Version', 15)); DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| RPAD(' ',10,'-') ||RPAD(' ',15,'-')); FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id, SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,SUBSTR(dr.version,1,15) version FROM dba_registry dr,registry$ r WHERE dr.comp_id=r.cid and dr.comp_name=r.cname ORDER BY 1) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) || RPAD(SUBSTR(x.comp_name,1,35),35)|| RPAD(x.status,10) || RPAD(x.version, 15)); END LOOP; ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| RPAD('Status',10) ||RPAD('Version', 15)|| RPAD('Org_Version',15)||RPAD('Prv_Version',15)); DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')|| RPAD(' ',15,'-')); FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id, SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status, SUBSTR(dr.version,1,11) version,org_version,prv_version FROM dba_registry dr,registry$ r WHERE dr.comp_id=r.cid and dr.comp_name=r.cname ORDER BY 1) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) || RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) || RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15)); END LOOP; END IF; END; / SET SERVEROUTPUT OFF Prompt Prompt Prompt Prompt ====================================================== Prompt List of Invalid Database Objects Owned by SYS / SYSTEM Prompt ====================================================== Prompt set head on SELECT case count(object_name) WHEN 0 THEN 'There are no Invalid Objects' ELSE 'There are '||count(object_name)||' Invalid objects' END "Number of Invalid Objects" FROM dba_objects WHERE status='INVALID' AND owner in ('SYS','SYSTEM'); Prompt DOC ################################################################ If there are no Invalid objects below will result in zero rows. ################################################################ # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID' AND owner in ('SYS','SYSTEM') ORDER BY owner,object_type; set feedback off Prompt Prompt ================================ Prompt List of Invalid Database Objects Prompt ================================ Prompt set head on SELECT case count(object_name) WHEN 0 THEN 'There are no Invalid Objects' ELSE 'There are '||count(object_name)||' Invalid objects' END "Number of Invalid Objects" FROM dba_objects WHERE status='INVALID' AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS', 'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP'); Prompt DOC ################################################################ If there are no Invalid objects below will result in zero rows. ################################################################ # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID' AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS', 'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP') ORDER BY owner,object_type; set feedback off Prompt Prompt ====================================================== Prompt Count of Invalids by Schema Prompt ====================================================== Prompt select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; Prompt ============================================================== Prompt Identifying whether a database was created as 32-bit or 64-bit Prompt ============================================================== Prompt DOC ########################################################################### Result referencing the string 'B023' ==> Database was created as 32-bit Result referencing the string 'B047' ==> Database was created as 64-bit When String results in 'B023' and when upgrading database to 10.2.0.3.0 (64-bit) , For known issue refer below articles Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6 ########################################################################### # Prompt SELECT SUBSTR(metadata,109,4) "Metadata", CASE SUBSTR(metadata,109,4) WHEN 'B023' THEN 'Database was created as 32-bit' WHEN 'B047' THEN 'Database was created as 64-bit' ELSE 'Metadata not Matching' END "Initial DB Creation Info" FROM sys.kopm$; Prompt Prompt =================================================== Prompt Number of Duplicate Objects Owned by SYS and SYSTEM Prompt =================================================== Prompt Prompt Counting duplicate objects .... Prompt SELECT count(1) FROM dba_objects WHERE object_name||object_type in (SELECT object_name||object_type from dba_objects where owner = 'SYS') AND owner = 'SYSTEM' AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ; Prompt Prompt ========================================= Prompt Duplicate Objects Owned by SYS and SYSTEM Prompt ========================================= Prompt Prompt Querying duplicate objects .... Prompt SELECT object_name, object_type, subobject_name, object_id FROM dba_objects WHERE object_name||object_type in (SELECT object_name||object_type FROM dba_objects WHERE owner = 'SYS') AND owner = 'SYSTEM' AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ; Prompt DOC ################################################################################ Below are expected and required duplicates objects and OMITTED in the report . Without replication installed: INDEX AQ$_SCHEDULES_PRIMARY TABLE AQ$_SCHEDULES If replication is installed by running catrep.sql: INDEX AQ$_SCHEDULES_PRIMARY PACKAGE DBMS_REPCAT_AUTH PACKAGE BODY DBMS_REPCAT_AUTH TABLE AQ$_SCHEDULES If any objects found please follow below article. Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema Read the Exceptions carefully before taking actions. ################################################################################ # Prompt Prompt ======================== Prompt Password protected roles Prompt ======================== Prompt DOC ################################################################################ In version 11.2 password protected roles are no longer enabled by default so if an application relies on such roles being enabled by default and no action is performed to allow the user to enter the password with the set role command, it is recommended to remove the password from those roles (to allow for existing privileges to remain available). For more information see: Note 745407.1 : What Roles Can Be Set as Default for a User? ################################################################################ # Prompt Prompt Querying for password protected roles .... Prompt break on "Password protected Role" select r.ROLE "Password protected Role", p.grantee "Assigned by default to user" from dba_roles r, dba_role_privs p where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role and p.default_role = 'YES' and p.grantee <> 'SYS' and r.role not in (select role from dba_application_roles); Prompt Prompt ================ Prompt JVM Verification Prompt ================ Prompt SET SERVEROUTPUT ON DECLARE V_CT NUMBER; P_VERSION VARCHAR2(10); BEGIN -- If so, get the version of the JAVAM component EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM'' AND status <> 99' INTO p_version; SELECT count(*) INTO v_ct FROM dba_objects WHERE object_type LIKE '%JAVA%' AND owner='SYS'; IF SUBSTR(p_version,1,5) = '8.1.7' THEN IF v_ct>=6787 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN IF v_ct>=8585 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN IF v_ct>=8585 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN IF v_ct>=13866 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN IF v_ct>=14113 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored'); END; / SET SERVEROUTPUT OFF Prompt Prompt ================================================ Prompt Checking Existence of Java-Based Users and Roles Prompt ================================================ Prompt DOC ################################################################################ There should not be any Java Based users for database version 9.0.1 and above. If any users found, it is faulty JVM. ################################################################################ # Prompt SELECT CASE count(username) WHEN 0 THEN 'No Java Based Users' ELSE 'There are '||count(*)||' JAVA based users' END "User Existence" FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%'; Prompt DOC ############################################################### Healthy JVM Should contain Six Roles. For 12.2 Seven Roles If there are more or less than six role, JVM is inconsistent. ############################################################### # Prompt SELECT CASE count(role) WHEN 0 THEN 'No JAVA related Roles' ELSE 'There are '||count(role)||' JAVA related roles' END "Role" FROM dba_roles WHERE role LIKE '%JAVA%'; Prompt Prompt Roles Prompt SELECT role FROM dba_roles WHERE role LIKE '%JAVA%'; set head off Prompt Prompt ========================================= Prompt List of Invalid Java Objects owned by SYS Prompt ========================================= SELECT CASE count(*) WHEN 0 THEN 'There are no SYS owned invalid JAVA objects' ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects' END "Total Invalid JAVA objects" FROM dba_objects WHERE object_type LIKE '%JAVA%' AND status='INVALID' AND owner='SYS'; Prompt DOC ################################################################# Check the status of the main JVM interface packages DBMS_JAVA and INITJVMAUX and make sure it is VALID. If there are no Invalid objects below will result in zero rows. ################################################################# # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE object_type LIKE '%JAVA%' AND status='INVALID' AND owner='SYS'; set feedback off Prompt DOC ################################################################# If the JAVAVM component is not installed in the database (for example, after creating the database with custom scripts), the next query will report the following error: select dbms_java.longname('foo') "JAVAVM TESTING" from dual * ERROR at line 1: ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier If the JAVAVM component is installed, the query should succeed with 'foo' as result. ################################################################# # Prompt set heading on select dbms_java.longname('foo') "JAVAVM TESTING" from dual; set heading off Prompt SET FEEDBACK ON HEAD ON serveroutput on Prompt =================================== Prompt Oracle Multimedia/InterMedia status Prompt =================================== Prompt DECLARE v_count NUMBER; v_version varchar2(200); v_user_count number; v_status VARCHAR2(200); v_xdb_installed NUMBER; v_xdk_installed NUMBER; v_javavm_installed NUMBER; TYPE string_tt IS TABLE OF VARCHAR2 (100) INDEX BY BINARY_INTEGER; v_user string_tt; BEGIN v_count := 0; v_version := ''; v_user_count := 0; v_status := ''; v_xdb_installed := 0; v_xdk_installed := 0; v_javavm_installed := 0; SELECT 1,version,status INTO v_count, v_version, v_status FROM dba_registry WHERE comp_id='ORDIM'; IF v_count > 0 then DBMS_OUTPUT.PUT_LINE ('.'); DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status); DBMS_OUTPUT.PUT_LINE ('.'); /* check if all users are installed.*/ v_user(1) := 'ORDSYS'; v_user(2) := 'ORDPLUGINS'; v_user(3) := 'MDSYS'; v_user(4) := 'SI_INFORMTN_SCHEMA'; v_user(5) := 'ORDDATA'; DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...'); FOR i IN v_user.first .. v_user.last LOOP SELECT COUNT(username) INTO v_user_count FROM dba_users WHERE username = v_user(I); /* ORDDATA user only exists starting 11.2 so no test if v_version is different */ IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2; END IF; /* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */ IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2; END IF; CASE v_user_count WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.'); WHEN 2 THEN NULL; -- user does not exist in that version ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.'); END CASE; END LOOP; DBMS_OUTPUT.PUT_LINE('.'); /* Prerequisites Check*/ DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...'); /* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */ SELECT COUNT(1) INTO v_javavm_installed FROM dba_registry WHERE comp_id='JAVAVM'; IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid'); END IF; IF SUBSTR(V_VERSION,1,2) IN ('11','12') OR SUBSTR(V_VERSION,1,6) = ('10.2.0') THEN SELECT COUNT(1) INTO v_xdk_installed FROM dba_registry WHERE comp_id='XML'; IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid'); END IF; SELECT COUNT(1) INTO v_xdb_installed FROM dba_registry WHERE comp_id='XDB'; IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid'); END IF; END IF; /* for versions >= 11 we run validate_ordim */ DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)'); IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN EXECUTE IMMEDIATE 'begin validate_ordim; end;'; ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND v_status <> 'VALID' THEN DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation'); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level'); END; / set feedback off head off select LPAD('*** End of LogFile ***',50) from dual; set feedback on head on Prompt spool off Prompt set heading off set heading off set feedback off select 'Upload db_upg_diag_&&dbname&×tamp&&suffix from "&log_path" directory' from dual; set heading on set feedback on Prompt -- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - =================================================================================================================================================================================================== SQL> @dbupgdiag.sql *** Start of LogFile *** Oracle Database Upgrade Diagnostic Utility 01-08-2025 06:26:44 =============== Hostname =============== rac01.int.XXXXXXXX =============== Database Name =============== UGARYD =============== Database Uptime =============== 02:36 07-JAN-25 ================= Database Wordsize ================= This is a 64-bit database ================ Software Version ================ Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 ============= Compatibility ============= Compatibility is set as 12.1.0.2.0 ================ Archive Log Mode ================ Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 219 Next log sequence to archive 221 Current log sequence 221 ================ Auditing Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/ugaryd/a dump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 ================ Cluster Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 DOC>################################################################ DOC> DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before DOC> upgrading the database DOC> DOC>################################################################ DOC># =========================================== Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$) =========================================== OWNER TABLESPACE_NAME ------------ ------------------------------ SYS SYSTEM ============================================================================ count of records in the sys.aud$ table where dbid is null- Standard Auditing ============================================================================ 0 ============================================================================================ count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed ============================================================================================ select count(*) from system.aud$ where dbid is null * ERROR at line 1: ORA-00942: table or view does not exist ============================================================================= count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing ============================================================================= 0 ========================================== Oracle Label Security is installed or not ========================================== Oracle Label Security is NOT installed at database level ================ Number of AQ Records in Message Queue Tables ================ SYS - ALERT_QT - 0 SYS - AQ$_MEM_MC - 0 SYS - AQ_EVENT_TABLE - 0 SYS - AQ_PROP_TABLE - 0 SYS - KUPC$DATAPUMP_QUETAB - 0 SYS - SCHEDULER$_EVENT_QTAB - 0 SYS - SCHEDULER$_REMDB_JOBQTAB - 0 SYS - SCHEDULER_FILEWATCHER_QT - 0 SYS - SYS$SERVICE_METRICS_TAB - 0 SYSTEM - DEF$_AQCALL - 0 SYSTEM - DEF$_AQERROR - 0 ================ Time Zone version ================ 18 ================ Local Listener ================ (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) ================ Default and Temporary Tablespaces By User ================ USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE ---------------------------- ---------------------- ---------------------- ANONYMOUS TEMP SYSAUX DBSNMP TEMP SYSAUX XDB TEMP SYSAUX APPQOSSYS TEMP SYSAUX GSMADMIN_INTERNAL TEMP SYSAUX GSMCATUSER TEMP SYSTEM SYSBACKUP TEMP SYSTEM OUTLN TEMP SYSTEM DIP TEMP SYSTEM SYSDG TEMP SYSTEM ORACLE_OCM TEMP SYSTEM SYSKM TEMP SYSTEM XS$NULL TEMP SYSTEM GSMUSER TEMP SYSTEM AUDSYS TEMP SYSTEM SYSTEM TEMP SYSTEM SYS TEMP SYSTEM ================ Component Status ================ Comp ID Component Status Version Org_Version Prv_Version ------- ---------------------------------- --------- -------------- -------------- -------------- CATALOG Oracle Database Catalog Views VALID 12.1.0.2.0 CATPROC Oracle Database Packages and Types VALID 12.1.0.2.0 RAC Oracle Real Application Clusters VALID 12.1.0.2.0 XDB Oracle XML Database VALID 12.1.0.2.0 ====================================================== List of Invalid Database Objects Owned by SYS / SYSTEM ====================================================== Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ================================ List of Invalid Database Objects ================================ Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ====================================================== Count of Invalids by Schema ====================================================== ============================================================== Identifying whether a database was created as 32-bit or 64-bit ============================================================== DOC>########################################################################### DOC> DOC> Result referencing the string 'B023' ==> Database was created as 32-bit DOC> Result referencing the string 'B047' ==> Database was created as 64-bit DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0 DOC> (64-bit) , For known issue refer below articles DOC> DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While DOC> Upgrading Or Patching Databases To 10.2.0.3 DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6 DOC> DOC>########################################################################### DOC># Metadata Initial DB Creation Info -------- ----------------------------------- B047 Database was created as 64-bit =================================================== Number of Duplicate Objects Owned by SYS and SYSTEM =================================================== Counting duplicate objects .... COUNT(1) ---------- 0 ========================================= Duplicate Objects Owned by SYS and SYSTEM ========================================= Querying duplicate objects .... DOC> DOC>################################################################################ DOC>Below are expected and required duplicates objects and OMITTED in the report . DOC> DOC>Without replication installed: DOC>INDEX AQ$_SCHEDULES_PRIMARY DOC>TABLE AQ$_SCHEDULES DOC> DOC>If replication is installed by running catrep.sql: DOC>INDEX AQ$_SCHEDULES_PRIMARY DOC>PACKAGE DBMS_REPCAT_AUTH DOC>PACKAGE BODY DBMS_REPCAT_AUTH DOC>TABLE AQ$_SCHEDULES DOC> DOC>If any objects found please follow below article. DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema DOC>Read the Exceptions carefully before taking actions. DOC> DOC>################################################################################ DOC># ======================== Password protected roles ======================== DOC> DOC>################################################################################ DOC> DOC> In version 11.2 password protected roles are no longer enabled by default so if DOC> an application relies on such roles being enabled by default and no action is DOC> performed to allow the user to enter the password with the set role command, it DOC> is recommended to remove the password from those roles (to allow for existing DOC> privileges to remain available). For more information see: DOC> DOC> Note 745407.1 : What Roles Can Be Set as Default for a User? DOC> DOC>################################################################################ DOC># Querying for password protected roles .... ================ JVM Verification ================ JAVAVM - NOT Installed. Below results can be ignored ================================================ Checking Existence of Java-Based Users and Roles ================================================ DOC> DOC>################################################################################ DOC> DOC> There should not be any Java Based users for database version 9.0.1 and above. DOC> If any users found, it is faulty JVM. DOC> DOC>################################################################################ DOC># User Existence --------------------------- No Java Based Users DOC> DOC>############################################################### DOC> DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles DOC> If there are more or less than six role, JVM is inconsistent. DOC> DOC>############################################################### DOC># Role ------------------------------ No JAVA related Roles Roles ========================================= List of Invalid Java Objects owned by SYS ========================================= There are no SYS owned invalid JAVA objects DOC> DOC>################################################################# DOC> DOC> Check the status of the main JVM interface packages DBMS_JAVA DOC> and INITJVMAUX and make sure it is VALID. DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################# DOC># no rows selected DOC> DOC>################################################################# DOC> DOC> If the JAVAVM component is not installed in the database (for DOC> example, after creating the database with custom scripts), the DOC> next query will report the following error: DOC> DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual DOC> * DOC> ERROR at line 1: DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier DOC> DOC> If the JAVAVM component is installed, the query should succeed DOC> with 'foo' as result. DOC> DOC>################################################################# DOC># select dbms_java.longname('foo') "JAVAVM TESTING" from dual * ERROR at line 1: ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier =================================== Oracle Multimedia/InterMedia status =================================== Oracle Multimedia/interMedia is NOT installed at database level PL/SQL procedure successfully completed. *** End of LogFile *** not spooling currently Enter value for log_path: Upload db_upg_diag_UGARYD_08_Jan_2025_0626.log from "" directory
Wednesday, 8 January 2025
dbupgdiag script ---- @dbupgdiag.sql
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE Script
ORA-14508: ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE (Doc ID 111990.1)
set linesize 300 pagesize 300
col xx for a100
select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME dba_TAB_PARTITIONS FROM dba_TAB_PARTITIONS WHERE TABLE_NAME ='XXXX';
set pagesize 0
-- spool p.sql
select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME || chr(10) ||'analyze table ' ||TABLE_OWNER||'.'||TABLE_NAME || ' partition (' ||PARTITION_NAME ||') validate structure cascade online into invalid_rows;' || chr(10) || ' select count(*) from invalid_rows ;'
FROM dba_TAB_PARTITIONS
WHERE TABLE_NAME ='XXX';
-- spool off
Friday, 3 January 2025
coe_xfr_sql_profile.sql example
[oracle@localhost ~]$ sqlplus system/system@//localhost:1521/free
*****************************************************************************************************************
--- coe_xfr_sql_profile.sql
--- coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
-- Transfer (copy) a SQL Profile from PDBx on CDBa into PDBy on CDBb
SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: coe_xfr_sql_profile.sql 2020/03/10 carlos.sierra $
REM
REM AUTHOR
REM Carlos Sierra
REM
REM SCRIPT
REM coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM This script generates another that contains the commands to
REM create a manual custom SQL Profile out of a known plan from
REM memory or AWR. The manual custom profile can be implemented
REM into the same SOURCE system where the plan was retrieved,
REM or into another similar TARGET system that has same schema
REM objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM 2. Plan Hash Value for which a manual custom SQL Profile is
REM needed (required). A list of known plans is presented.
REM You may choose from list provided or enter a valid phv
REM from a version of the SQL modified with Hints.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as user with access to data dictionary.
REM Do not use SYS.
REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM plan hash value (parameters can be passed inline or until
REM requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_xfr_sql_profile.log
REM 2. If SQLT is installed in SOURCE, you can use instead:
REM sqlt/utl/sqltprofile.sql
REM 3. Be aware that using DBMS_SQLTUNE requires a license for
REM Oracle Tuning Pack.
REM 4. Use a DBA user but not SYS.
REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"
REM Then you may consider this change (only in a test and disposable system):
REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1.';
UNDEF 1;
PRO
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs,
SUM(executions) executions
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_delta)/SUM(executions_delta) avg_et_secs,
SUM(executions_delta) executions
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_delta > 0
GROUP BY
plan_hash_value )
SELECT
TO_CHAR(ROUND(m.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_mem,
TO_CHAR(ROUND(a.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_awr,
p.plan_hash_value,
m.executions executions_mem,
a.executions executions_awr
--TO_CHAR(ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 6), '999,990.000000') avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
NVL(m.avg_et_secs, a.avg_et_secs) NULLS LAST, a.avg_et_secs;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
UNDEF 2;
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;
VAR sql_text CLOB;
VAR sql_text2 CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :sql_text2 := NULL;
EXEC :other_xml := NULL;
-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
FOR i IN (SELECT DISTINCT piece, sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = TRIM('&&sql_id.')
ORDER BY 1, 2)
LOOP
IF :sql_text IS NULL THEN
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
END IF;
-- removes NUL characters
l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
-- adds a NUL character at the end of each line
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
END LOOP;
-- if found in memory then sql_text is not null
IF :sql_text IS NOT NULL THEN
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text FROM DUAL;
-- get sql_text from awr
DECLARE
l_sql_text VARCHAR2(32767);
l_clob_size NUMBER;
l_offset NUMBER;
BEGIN
IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
SELECT sql_text
INTO :sql_text2
FROM dba_hist_sqltext
WHERE sql_id = TRIM('&&sql_id.')
AND sql_text IS NOT NULL
AND ROWNUM = 1;
END IF;
-- if found in awr then sql_text2 is not null
IF :sql_text2 IS NOT NULL THEN
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
l_offset := 1;
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
-- store in clob as 64 character pieces plus a NUL character at the end of each piece
WHILE l_offset < l_clob_size
LOOP
IF l_clob_size - l_offset > 64 THEN
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
ELSE -- last piece
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
END IF;
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
l_offset := l_offset + 64;
END LOOP;
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text2 FROM DUAL;
SELECT :sql_text FROM DUAL;
-- validate sql_text
SET TERM ON;
BEGIN
IF :sql_text IS NULL THEN
RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
END IF;
END;
/
SET TERM OFF;
-- get other_xml from memory
BEGIN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from memory from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
--WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
--WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND dbid = (SELECT dbid FROM v$database)
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
SELECT :other_xml FROM DUAL;
-- validate other_xml
SET TERM ON;
BEGIN
IF :other_xml IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
END IF;
END;
/
SET TERM OFF;
-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
l_pos NUMBER;
l_clob_size NUMBER;
l_offset NUMBER;
l_sql_text VARCHAR2(32767);
l_len NUMBER;
l_hint VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');
DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');
DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');
DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');
DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');
DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');
DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');
DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
DBMS_OUTPUT.PUT_LINE('REM None.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM NOTES');
DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');
DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');
DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');
DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');
DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');
DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');
DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');
DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');
DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');
DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');
DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
DBMS_OUTPUT.PUT_LINE('END wa;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
l_offset := 1;
WHILE l_offset < l_clob_size
LOOP
l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
IF l_pos > 0 THEN
l_len := l_pos - l_offset;
ELSE -- last piece
l_len := l_clob_size - l_pos + 1;
END IF;
l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
/* cannot do such 3 replacement since a line could end with a comment using "--"
l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
*/
l_offset := l_offset + l_len + 1;
IF l_len > 0 THEN
IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
l_sql_text := '['||l_sql_text||']';
ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
l_sql_text := '{'||l_sql_text||'}';
ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
l_sql_text := '<'||l_sql_text||'>';
ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
l_sql_text := '('||l_sql_text||')';
ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
l_sql_text := '"'||l_sql_text||'"';
ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
l_sql_text := '|'||l_sql_text||'|';
ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
l_sql_text := '~'||l_sql_text||'~';
ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
l_sql_text := '^'||l_sql_text||'^';
ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
l_sql_text := '@'||l_sql_text||'@';
ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
l_sql_text := '#'||l_sql_text||'#';
ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
l_sql_text := '%'||l_sql_text||'%';
ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
l_sql_text := '$'||l_sql_text||'$';
ELSE
l_sql_text := CHR(96)||l_sql_text||CHR(96);
END IF;
DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
LOOP
l_hint := i.hint;
WHILE NVL(LENGTH(l_hint), 0) > 0
LOOP
IF LENGTH(l_hint) <= 500 THEN
DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
l_hint := NULL;
ELSE
l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
l_hint := ' '||SUBSTR(l_hint, l_pos);
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');
DBMS_OUTPUT.PUT_LINE('profile => h,');
DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');
DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');
DBMS_OUTPUT.PUT_LINE('validate => TRUE,');
DBMS_OUTPUT.PUT_LINE('replace => TRUE,');
DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
DBMS_OUTPUT.PUT_LINE('PRINT signature');
DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
DBMS_OUTPUT.PUT_LINE('SPO OFF;');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.
**********************************************************
set linesize window
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
set lines 300
col sql_sql_text head SQL_TEXT format a50 word_wrap
col sql_child_number head CH# for 9999
column EXACT_MATCHING_SIGNATURE format 99999999999999999999
column force_matching_signature format 99999999999999999999
select
s.sql_id,
s.hash_value,
s.plan_hash_value,
s.force_matching_signature,
s.EXACT_MATCHING_SIGNATURE,
t.phv2,
s.sql_text sql_sql_text
from
v$sql s,
v$sql_plan p
,xmltable('for $i in /other_xml/info
where $i/@type eq "plan_hash_2"
return $i'
passing xmltype(p.other_xml)
columns phv2 number path '/'
) t
where
s.sql_id=p.sql_id
AND
s.plan_hash_value=p.plan_hash_value
AND
s.child_number=p.CHILD_NUMBER
AND
p.other_xml is not null
-- AND s.sql_text like 'sql_text%'
and s.sql_id in ('dg8duxp7zprxp','29v6w386wqnzt')
order by 3 desc
coe_xfr_sql_profile.sql dg8duxp7zprxp 1288918533
coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE
SQL> ed coe_xfr_sql_profile
SQL> @coe_xfr_sql_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: dg8duxp7zprxp
AVG_ET_SECS_MEM AVG_ET_SECS_AWR PLAN_HASH_VALUE EXECUTIONS_MEM EXECUTIONS_AWR
--------------- --------------- --------------- -------------- --------------
0.017320 1130139812 1
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1130139812
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "dg8duxp7zprxp"
PLAN_HASH_VALUE: "1130139812"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
on TARGET system in order to create a custom SQL Profile
with plan 1130139812 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
##########################
SQL>!grep -i force coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
SQL>
SQL>@coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql 11.4.4.4 2025/01/03 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID dg8duxp7zprxp based on plan hash
SQL>REM value 1130139812.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_dg8duxp7zprxp_1130139812');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[with s1 as (
15 select
16 a.n2 n2,
17 a.vc vc
18 from
19 t1 a
20 )]');
21 wa(q'[,
22 s2 as (
23 select
24 b.n4 n4,
25 b.vd vd
26 from
27 t2 b
28 )
29 se]');
30 wa(q'[lect
31 s1.n2,
32 s1.vc,
33 s2.n4,
34 s2.vd,
35 s1.n2||s2.n4 as N
36 fro]');
37 wa(q'[m
38 s1,
39 s2
40 where
41 s1.n2 = s2.n4
42 and s1.vc = 'xY'
43 AND s2]');
44 wa(q'[.n4 = 0]');
45 DBMS_LOB.CLOSE(sql_txt);
46 h := SYS.SQLPROF_ATTR(
47 q'[BEGIN_OUTLINE_DATA]',
48 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
49 q'[OPTIMIZER_FEATURES_ENABLE('23.1.0')]',
50 q'[DB_VERSION('23.1.0')]',
51 q'[ALL_ROWS]',
52 q'[OUTLINE_LEAF(@"SEL$18780C30")]',
53 q'[MERGE(@"SEL$1" >"SEL$3")]',
54 q'[MERGE(@"SEL$2" >"SEL$3")]',
55 q'[OUTLINE(@"SEL$3")]',
56 q'[OUTLINE(@"SEL$1")]',
57 q'[OUTLINE(@"SEL$2")]',
58 q'[FULL(@"SEL$18780C30" "B"@"SEL$2")]',
59 q'[INDEX_RS_ASC(@"SEL$18780C30" "A"@"SEL$1" ("T1"."N2"))]',
60 q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$18780C30" "A"@"SEL$1")]',
61 q'[LEADING(@"SEL$18780C30" "B"@"SEL$2" "A"@"SEL$1")]',
62 q'[USE_MERGE_CARTESIAN(@"SEL$18780C30" "A"@"SEL$1")]',
63 q'[END_OUTLINE_DATA]');
64 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
65 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
66 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
67 sql_text => sql_txt,
68 profile => h,
69 name => 'coe_dg8duxp7zprxp_1130139812',
70 description => 'coe dg8duxp7zprxp 1130139812 '||:signature||' '||:signaturef||'',
71 category => 'DEFAULT',
72 validate => TRUE,
73 replace => TRUE,
74 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
75 DBMS_LOB.FREETEMPORARY(sql_txt);
76 END;
77 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5336375310114799588
SIGNATUREF
---------------------
14577638555275281096
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_dg8duxp7zprxp_1130139812 completed
col name for a35
col created for a30
col FORCE_MATCHING for a25
SELECT
--s.sql_id,
-- s.plan_hash_value,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
dba_sql_profiles p,
v$sqlstats s
where
p.signature = s.FORCE_MATCHING_SIGNATURE
AND s.sql_id='dg8duxp7zprxp';
set linesize 300
col name for a35
col created for a30
col FORCE_MATCHING for a25
SELECT
--s.sql_id,
-- s.plan_hash_value,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
dba_sql_profiles p ;
NAME CREATED STATUS FORCE_MATCHING
----------------------------------- ------------------------------ -------- -------------------------
coe_dg8duxp7zprxp_1130139812 03-JAN-25 02.30.19.728687 PM ENABLED NO
Subscribe to:
Posts (Atom)
