--- @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
SELECT
p.sql_id
,p.plan_hash_value
,p.child_number
,t.phv2
FROM
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 p.sql_id = '&sql_id'
AND p.other_xml is not null;check sql profile exists or not
define sql_id='XXXXXX'
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
order by
created;
SELECT NAME,SIGNATURE,CREATED,STATUS,SQL_TEXT FROM DBA_SQL_PROFILES
where 1=1
and name like '%XXXXXXX%'
order by created ;
Disable the SQL Profiles BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'SYS_SQLPROF_789tfag56hjli0004', attribute_name => 'STATUS', value => 'DISABLED'); END; /
Drop the SQL Profile
exec dbms_sqltune.drop_sql_profile('coe_XXXXXX');
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Drop all profiles in a database,
don't run !!!!!!!!!!!!!!
declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
then create profile
[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
====
set linesize 300
col name for a35
col created for a30
col FORCE_MATCHING for a25
col STATUS for a12
SELECT
CON_ID,
p.name,
p.CREATED,
-- p.LAST_MODIFIED,
p.STATUS,
p.FORCE_MATCHING
FROM
CDB_SQL_PROFILES p
where 1=1
--and p.CREATED >sysdate -7
;
to drop ---
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','ENABLE');
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');
set pages 9999 lines 200 autotrace off trimspool on echo on
column name format a30
column category format a10
column created format a30
column last_modified format a30
column task_exec_name format a20
column signature format 99999999999999999999
column description format a20
set linesize 700
col DESCRIPTION for a20
col PROFILE_NAME for a20
col SQL_TEXT for a50 wrap
col TASK_EXEC_NAME for a15
col CATEGORY for a15
col LAST_MODIFIED for a20
col SIGNATURE for 9999999999999999999999
SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME
and prof.CREATED >sysdate -7
;
define sql_id=' '
set linesize 500 pagesize 300
col PARSE_USR for A14
col SQLTEXT for a50
col SQL_PROFILE for a27
col FORCE_MATCHING_SIGNATURE for 9999999999999999999999999
break on parse_usr
prompt ========== from v$sql ==========
select
con_id,
parsing_schema_name parse_usr
,lpad(plan_hash_value,10,' ')||' '||
case when sql_profile is not null then 'P'
when sql_plan_baseline is not null then 'B'
else ' '
end phashp
--,is_bind_sensitive||is_bind_aware||is_shareable pas
--,optimizer_mode m
--,is_obsolete o
,users_executing ue
,substr(object_status,1,1)||':'||lpad(child_number,5,' ') s_cn
--,to_char(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MMDD HH24MI') lltime
,to_char(last_active_time,'MMDD HH24:MI') latime
,(sysdate-last_active_time)*1440 min_ago
,decode(executions,0,1,executions) exec
,cpu_time/decode(executions,0,1,executions)/1000 cpx
,elapsed_time/decode(executions,0,1,executions)/1000 elpx
,(cpu_time/elapsed_time)*100 cpct
,buffer_gets/decode(executions,0,1,executions) bgpx
,user_io_wait_time/decode(executions,0,1,executions)/1000 iowpx
,(user_io_wait_time/elapsed_time)*100 ipct
,disk_reads/decode(executions,0,1,executions) drpx
,application_wait_time/decode(executions,0,1,executions)/1000 apwpx
,concurrency_wait_time/decode(executions,0,1,executions)/1000 conw
,rows_processed/decode(executions,0,1,executions) rwpx
,sorts/decode(executions,0,1,executions) sortx
,fetches/decode(executions,0,1,executions) fetchx
,module module
,SQL_PROFILE
,FORCE_MATCHING_SIGNATURE
,replace(sql_text,chr(13)) sqltext
from v$sql
where sql_id = '&&sql_id'
--and last_active_time > sysdate-3
order by parsing_schema_name
,last_active_time
;
===
define sql_id=' '
col apwpx format 99999.999 head 'AppWms|PerX'
col bgpx format 999999999 head 'BGets|PerX'
col conw format 99999.999 head 'Conwms|PerX'
col cpx format 99999999.999 head 'CPUms|PerX'
col drpx format 999999999999.99 head 'DReads|PerX'
col fetchx format 99999 head 'Fetchs|PerX'
col sortx format 999 head 'Sorts|PerX'
col elpx format 99999999.999 head 'Elapms|PerX'
col exec format 999999999999 head 'Execs'
col iowpx format 99999999.999 head 'IOWms|PerX'
col latime format a11 head 'Last Active'
col lltime format a09 head 'Last Load'
col maxsnapid format 999999 head 'Max|SnapId'
col m format a01 trunc
col minsnapid format 999999 head 'Min|SnapId'
col module format a14 trunc head 'Module'
col o format a01 head 'O' trunc
col opt_mode format a08 trunc head 'Opt|Mode' trunc
col parse_usr format a08 head 'ParsUser' trunc
col phash format 9999999999 head 'PlanHash'
col phashp format a12 head 'PlanHash P'
col rwpx format 9999999999.99 head 'RwsP|PerX'
col s_cn format a07 head 'S:Child' trunc
col sql_id format a15 head 'SQL Id'
col sqltext format a12 trunc head 'Sql Text'
col ue format 999
col cpct format 999 head 'CPU|Pct' trunc
col ipct format 999 head 'IO|Pct' trunc
col btime format a11 head 'Begin Time'
col smem format 99999 head 'ShrMem|KB'
col FORCE_MATCHING_SIGNATURE for 999999999999999999999999999999
break on sql_id on opt_mode on parse_usr skip 1
prompt
prompt ===== from dba_hist_sqlstat =====
select parsing_schema_name parse_usr
,plan_hash_value phash
,optimizer_mode opt_mode
,min(snap_id) minsnapid
,max(snap_id) maxsnapid
,avg(sharable_mem/1024) smem
,sum(decode(executions_delta,0,1,executions_delta)) exec
,sum(cpu_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 cpx
,sum(elapsed_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 elpx
,(sum(cpu_time_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 cpct
,sum(buffer_gets_delta)/sum(decode(executions_delta,0,1,executions_delta)) bgpx
,sum(iowait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 iowpx
,(sum(iowait_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 ipct
,sum(disk_reads_delta)/sum(decode(executions_delta,0,1,executions_delta)) drpx
,sum(apwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 apwpx
,sum(ccwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 conw
,sum(rows_processed_delta)/sum(decode(executions_delta,0,1,executions_delta)) rwpx
,sum(sorts_delta)/sum(decode(executions_delta,0,1,executions_delta)) sortx
,sum(end_of_fetch_count_delta)/sum(decode(executions_delta,0,1,executions_delta)) fetchx
,module module
,sql_id
,FORCE_MATCHING_SIGNATURE
,SQL_PROFILE
from dba_hist_sqlstat ss
where sql_id = '&&sql_id'
group by sql_id
,parsing_schema_name
,plan_hash_value
,optimizer_mode
,module
,FORCE_MATCHING_SIGNATURE
,SQL_PROFILE
having sum(decode(executions_delta,0,1,executions_delta)) >0
order by min(ss.snap_id),parsing_schema_name
;
Saturday, 28 December 2024
Oracle database upgrade from 19c to 21c .
Oracle database upgrade from 19c to 21c
=============================================
+ASM:/u01/app/21.3.0/grid:N # line added by Agent
v14mpc:/u01/app/oracle/product/19.0.0/dbhome1:N >>>> aim to upgrade to 21 c
cdborcl:/u01/app/oracle/product/21.0.0/dbhome1:N
xxx:/u01/app/oracle/product/19.0.0/dbhome1:N
Note :
Starting with Oracle Database Release 21c, preupgrade tool functionality (preupgrade.jar) has been integrated into the AutoUpgrade tool (autoupgrade.jar -preupgrade option). Refer to Oracle Database Upgrade Guide for details on usage.
=============================
Oracle 19c !!!!
Check all the pdbs should be read write mode
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 V14MPCPDB READ WRITE NO
======
https://nyoug.org/wp-content/uploads/2019/12/OOW2019_AutoUpgrade19c.pdf
options
$ java -jar autoupgrade.jar -config config.cfg -mode analyze
$ java -jar autoupgrade.jar -config config.cfg -mode fixups
$ java -jar autoupgrade.jar -config config.cfg -mode upgrade
-mode analyze|fixups|deploy
[oracle@oragrid ~]$ /usr/bin/java -jar /u01/app/oracle/product/21.0.0/dbhome1/rdbms/admin/autoupgrade.jar -config /u01/app/oracle/autoupgrade1/config1.txt -mode analyze
AutoUpgrade 24.8.241119 launched with default internal options
Processing config file ...
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------+
| 104| v14mpc|PRECHECKS|EXECUTING|RUNNING| 06:22:38|16s ago|Executing Checks|
+----+-------+---------+---------+-------+----------+-------+----------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------+
| 104| v14mpc|PRECHECKS|EXECUTING|RUNNING| 06:22:38|19s ago|Executing Checks|
+----+-------+---------+---------+-------+----------+-------+----------------+
Total jobs 1
upg> Job 104 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Please check the summary report at:
/u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.log
[oracle@oragrid ~]$ cat /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.log
==========================================
Autoupgrade Summary Report
==========================================
[Date] Sat Dec 28 06:26:49 GMT 2024
[Number of Jobs] 1
==========================================
[Job ID] 104
==========================================
[DB Name] v14mpc
[Version Before Upgrade] 19.3.0.0.0
[Version After Upgrade] 21.3.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2024-12-28 06:22:38
[Duration] 0:04:11
[Log Directory] /u01/app/oracle/autoupgrade1/v14mpc/104/prechecks
[Detail] /u01/app/oracle/autoupgrade1/v14mpc/104/prechecks/v14mpc_preupgrade.log
Check passed and no manual intervention needed
------------------------------------------
[oracle@oragrid ~]$ ls -ltr /u01/app/oracle/autoupgrade1/v14mpc/104/prechecks
total 1212
-rw-r----- 1 oracle oinstall 362101 Dec 28 06:25 prechecks_cdb_root.log
-rw-r----- 1 oracle oinstall 294935 Dec 28 06:25 prechecks_v14mpcpdb.log
-rw-r----- 1 oracle oinstall 294684 Dec 28 06:26 prechecks_pdb_seed.log
-rw-r----- 1 oracle oinstall 31427 Dec 28 06:26 v14mpc_checklist.xml
-rw-r----- 1 oracle oinstall 33148 Dec 28 06:26 v14mpc_checklist.json
-rw-r----- 1 oracle oinstall 9930 Dec 28 06:26 v14mpc_checklist.cfg
-rw-r----- 1 oracle oinstall 64911 Dec 28 06:26 v14mpc_preupgrade.html ----check html file
-rw-r----- 1 oracle oinstall 93796 Dec 28 06:26 upgrade.xml
-rw-r----- 1 oracle oinstall 39551 Dec 28 06:26 v14mpc_preupgrade.log
[oracle@oragrid ~]$
====================================================================
Run below as per update v14mpc_preupgrade.html file
[oracle@oragrid ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 28 06:53:13 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 V14MPCPDB READ WRITE NO
SQL> alter session set container=V14MPCPDB ;
Session altered.
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
SQL>
================================================================================== upgade start from here !!!!!!!!!!!!!!!! Now use … -mode deploy /usr/bin/java -jar /u01/app/oracle/product/21.0.0/dbhome1/rdbms/admin/autoupgrade.jar -config /u01/app/oracle/autoupgrade1/config1.txt -mode deploy [root@oragrid trace]# pwd /u01/app/oracle/diag/rdbms/v14mpc/v14mpc/trace [root@oragrid trace]# tail -100f alert_v14mpc.log to update the locate ... updatedb locate cfgtoollogs|grep -i 28 <<<< date /u01/app/21.3.0/grid/cfgtoollogs/opatch/opatch2024-12-13_15-14-28PM_1.log /u01/app/21.3.0/grid/cfgtoollogs/opatch/opatch2024-12-13_15-28-42PM_1.log /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15 /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32 /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15/progress.json /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15/status.html /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15/status.json /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15/status.log /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_18_15/v14mpc_prechecks.success /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32/progress.json /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32/status.html /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32/status.json /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32/status.log /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status_2024_12_28_06_26_32/v14mpc_prechecks.success -rw-r----- 1 oracle oinstall 4077850 Dec 28 09:00 autoupgrade.log [root@oragrid auto]# pwd /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto [root@oragrid auto]# [root@oragrid auto]# pwd /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto [root@oragrid auto]# tail -100f autoupgrade.log upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|31s ago|52%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|99s ago|52%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|63s ago|52%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|60s ago|76%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+--------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+--------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|117s ago|76%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+--------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|31s ago|85%Upgraded CDB$ROOT| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43| 4s ago|0%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|26s ago|1%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|35s ago|22%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj -e +----+-------+-----+---------+------+----------+-------+-------+ |Job#|DB_NAME|STAGE|OPERATION|STATUS|START_TIME|UPDATED|MESSAGE| +----+-------+-----+---------+------+----------+-------+-------+ +----+-------+-----+---------+------+----------+-------+-------+ Total jobs 0 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|48s ago|22%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|11s ago|52%Upgraded PDB$SEED| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|87s ago|52%Upgraded PDB$SEED| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|37s ago|52%Upgraded PDB$SEED| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|78s ago|52%Upgraded PDB$SEED| +----+-------+---------+---------+-------+----------+-------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+--------+--------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+--------+--------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|120s ago|52%Upgraded PDB$SEED| +----+-------+---------+---------+-------+----------+--------+--------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|46s ago|58%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj -e +----+-------+-----+---------+------+----------+-------+-------+ |Job#|DB_NAME|STAGE|OPERATION|STATUS|START_TIME|UPDATED|MESSAGE| +----+-------+-----+---------+------+----------+-------+-------+ +----+-------+-----+---------+------+----------+-------+-------+ Total jobs 0 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|87s ago|76%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43| 2s ago|76%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|62s ago|76%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+---------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+---------------------+ | 105| v14mpc|DBUPGRADE|EXECUTING|RUNNING| 07:39:43|36s ago|85%Upgraded V14MPCPDB| +----+-------+---------+---------+-------+----------+-------+---------------------+ Total jobs 1 upg> lsj +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ | 105| v14mpc|SYSUPDATES|EXECUTING|RUNNING| 07:39:43|34s ago|RAC configurations have finished success| +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ Total jobs 1 upg> lsj +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ | 105| v14mpc|SYSUPDATES|EXECUTING|RUNNING| 07:39:43|39s ago|RAC configurations have finished success| +----+-------+----------+---------+-------+----------+-------+----------------------------------------+ Total jobs 1 upg> Job 105 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs restored [0] Jobs pending [0] ---- Drop GRP at your convenience once you consider it is no longer needed ---- Drop GRP from v14mpc: drop restore point AUTOUPGRADE_9212_V14MPC193000 Please check the summary report at: /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.log ====
2024-12-28 09:44:12.451 INFO ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs restored [0] Jobs pending [0] ---- Drop GRP at your convenience once you consider it is no longer needed ---- Drop GRP from v14mpc: drop restore point AUTOUPGRADE_9212_V14MPC193000 Please check the summary report at: /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto/status/status.log - Exit.summaryInfoSection#279 2024-12-28 09:44:12.459 INFO Starting files protection - SetProtection.setGlobalProtections#268 2024-12-28 09:44:12.459 INFO Protecting global dir - SetProtection.setGlobalProtections#269 2024-12-28 09:44:12.460 INFO The console is closing now as per interruption request - ConsoleReader.run#70 2024-12-28 09:44:12.460 INFO Closing - ConsoleReader.run#108 2024-12-28 09:44:12.461 INFO Starting - SetProtection.protectPath#307 2024-12-28 09:44:12.461 INFO Protecting path /u01/app/oracle/autoupgrade1/cfgtoollogs/upgrade/auto - SetProtection.protectPath#308 2024-12-28 09:44:12.462 INFO Bye Bye thread queue_reader | daemon false - AbstractJobMonitor.stopTheParty#97 2024-12-28 09:44:12.462 INFO Bye Bye thread cmd-0 | daemon false - AbstractJobMonitor.stopTheParty#100 2024-12-28 09:44:12.462 INFO Bye Bye thread console | daemon false - AbstractJobMonitor.stopTheParty#100 2024-12-28 09:44:12.462 INFO Bye Bye thread job_manager-0 | daemon false - AbstractJobMonitor.stopTheParty#97 2024-12-28 09:44:12.462 INFO Bye Bye thread StatUpg | daemon true - AbstractJobMonitor.stopTheParty#97 2024-12-28 09:44:12.462 INFO Bye Bye thread main | daemon false - AbstractJobMonitor.stopTheParty#97 2024-12-28 09:44:12.462 INFO End - AbstractJobMonitor.stopTheParty#103 2024-12-28 09:44:12.462 INFO Closing - DeployTypeMon.shutdownCheck#217 2024-12-28 09:44:12.463 INFO Protecting elapsed time 0 seconds - SetProtection.protectPath#336 2024-12-28 09:44:12.463 INFO End - SetProtection.protectPath#337 2024-12-28 09:44:12.463 INFO FilesProtector running for job 105 - Exit.lambda$coreExit$3#208 2024-12-28 09:44:12.463 INFO Starting files protection - SetProtection.setJobProtections#284 2024-12-28 09:44:12.463 INFO Protecting local dir - SetProtection.setJobProtections#285 2024-12-28 09:44:12.464 INFO Starting - SetProtection.protectPath#307 2024-12-28 09:44:12.464 INFO Protecting path /u01/app/oracle/autoupgrade1/v14mpc/105 - SetProtection.protectPath#308 2024-12-28 09:44:12.468 INFO Protecting elapsed time 0 seconds - SetProtection.protectPath#336 2024-12-28 09:44:12.468 INFO End - SetProtection.protectPath#337 2024-12-28 09:44:12.468 INFO Starting - SetProtection.protectPath#307 2024-12-28 09:44:12.468 INFO Protecting path /u01/app/oracle/autoupgrade1/v14mpc/temp - SetProtection.protectPath#308 2024-12-28 09:44:12.469 INFO Protecting elapsed time 0 seconds - SetProtection.protectPath#336 2024-12-28 09:44:12.469 INFO End - SetProtection.protectPath#337 2024-12-28 09:44:12.469 INFO End processing cmd [exit] - CommandController.processCmd#116 2024-12-28 09:44:12.469 INFO Executing global actions after running AutoUpgrade - ActionsExecutor.runGlobalAfterAction#249
Friday, 27 December 2024
Linux virtual machine can't ping google while host can do it !!!!!
1. on vm > setting > network > bridged adapter
2.
on laptop
=====================================================
on DOS cmd !!!!!
C:\Users\>ipconfig -all
Windows IP Configuration
Host Name . . . . . . . . . . . . : DESKTOP-*****
Primary Dns Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Hybrid
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
DNS Suffix Search List. . . . . . : ******
Ethernet adapter Ethernet 5:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : VirtualBox Host-Only Ethernet Adapter
Physical Address. . . . . . . . . : 0A-00-27-00-00-17
DHCP Enabled. . . . . . . . . . . : No
Autoconfiguration Enabled . . . . : Yes
Link-local IPv6 Address . . . . . : fe80::d6d3:a222:fb62:c216%23(Preferred)
IPv4 Address. . . . . . . . . . . : 192.168.56.1(Preferred)
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :
DHCPv6 IAID . . . . . . . . . . . : 822738983
DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-2D-80-59-CC-8C-04-BA-09-51-49
NetBIOS over Tcpip. . . . . . . . : Enabled
Wireless LAN adapter Local Area Connection* 1:
Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Microsoft Wi-Fi Direct Virtual Adapter
Physical Address. . . . . . . . . : D0-AB-D5-2C-F5-DE
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Wireless LAN adapter Local Area Connection* 10:
Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Microsoft Wi-Fi Direct Virtual Adapter #2
Physical Address. . . . . . . . . : D2-AB-D5-2C-F5-DD
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Wireless LAN adapter WiFi:
Connection-specific DNS Suffix . : lan
Description . . . . . . . . . . . : Intel(R) Dual Band Wireless-AC 8265
Physical Address. . . . . . . . . : D0-AB-D5-2C-F5-DD
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
IPv6 Address. . . . . . . . . . . : 2a00:23c6:9735:5900:ddd6:4f4d:42d7:b65e(Preferred)
IPv6 Address. . . . . . . . . . . : fd78:4c15:7f9d::fa5(Preferred)
Lease Obtained. . . . . . . . . . : 25 December 2024 06:20:46
Lease Expires . . . . . . . . . . : 28 December 2024 07:22:17
IPv6 Address. . . . . . . . . . . : fd78:4c15:7f9d:0:241d:5b8:815f:9899(Preferred)
Temporary IPv6 Address. . . . . . : 2a00:23c6:9735:5900:18c3:619d:8145:53f5(Preferred)
Temporary IPv6 Address. . . . . . : 2a00:23c6:9735:5900:589a:f4b4:6eac:936a(Deprecated)
Temporary IPv6 Address. . . . . . : 2a00:23c6:9735:5900:6c49:e836:4352:6eb5(Deprecated)
Temporary IPv6 Address. . . . . . : 2a00:23c6:9735:5900:ecbc:b24d:a806:8b47(Deprecated)
Temporary IPv6 Address. . . . . . : 2a00:23c6:9735:5900:fd1f:74b6:8106:6ba2(Deprecated)
Temporary IPv6 Address. . . . . . : fd78:4c15:7f9d:0:18c3:619d:8145:53f5(Preferred)
Temporary IPv6 Address. . . . . . : fd78:4c15:7f9d:0:589a:f4b4:6eac:936a(Deprecated)
Temporary IPv6 Address. . . . . . : fd78:4c15:7f9d:0:6c49:e836:4352:6eb5(Deprecated)
Temporary IPv6 Address. . . . . . : fd78:4c15:7f9d:0:ecbc:b24d:a806:8b47(Deprecated)
Temporary IPv6 Address. . . . . . : fd78:4c15:7f9d:0:fd1f:74b6:8106:6ba2(Deprecated)
Link-local IPv6 Address . . . . . : fe80::f22a:c00e:45e2:27b7%2(Preferred)
IPv4 Address. . . . . . . . . . . : 192.168.1.213(Preferred)
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Lease Obtained. . . . . . . . . . : 25 December 2024 06:20:55
Lease Expires . . . . . . . . . . : 28 December 2024 07:22:05
Default Gateway . . . . . . . . . : fe80::ba6a:f1ff:fec4:ae90%2
192.168.1.254
DHCP Server . . . . . . . . . . . : 192.168.1.254
DHCPv6 IAID . . . . . . . . . . . : 47229909
DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-2D-80-59-CC-8C-04-BA-09-51-49
DNS Servers . . . . . . . . . . . : 192.168.1.254 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< check dns !!!!!!!!!!
NetBIOS over Tcpip. . . . . . . . : Enabled
Ethernet adapter Bluetooth Network Connection:
Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Bluetooth Device (Personal Area Network)
Physical Address. . . . . . . . . : D0-AB-D5-2C-F5-E1
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
=======================================================================================================
on vm !!!!
[root@srv1 ~]# ifconfig -a
enp0s3: flags=4163 mtu 1500 >>>> card name enp0s3
inet 192.168.1.120 netmask 255.255.255.0 broadcast 192.168.1.255
inet6 fd78:4c15:7f9d::677 prefixlen 128 scopeid 0x0
inet6 fe80::8f73:744d:ce36:6e12 prefixlen 64 scopeid 0x20
inet6 2a00:23c6:9735:5900:9af9:fed8:2570:486b prefixlen 64 scopeid 0x0
inet6 fd78:4c15:7f9d:0:1155:a65a:49d8:fcc8 prefixlen 64 scopeid 0x0
inet6 2a00:23c6:9735:5900::677 prefixlen 128 scopeid 0x0
ether 08:00:27:a5:38:cd txqueuelen 1000 (Ethernet)
RX packets 13009 bytes 1299156 (1.2 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 2974 bytes 305294 (298.1 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
=========================================================================================
[root@srv1 ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.1.1 0.0.0.0 UG 100 0 0 enp0s3 <<<<<<<<<<<<<<<<<----- wrong gateway should be 192.168.1.254
192.168.1.0 0.0.0.0 255.255.255.0 U 100 0 0 enp0s3
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@srv1 ~]#
[root@srv1 ~]# ip a
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:a5:38:cd brd ff:ff:ff:ff:ff:ff
inet 192.168.1.120/24 brd 192.168.1.255 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
inet6 fd78:4c15:7f9d::677/128 scope global noprefixroute dynamic
valid_lft 86203sec preferred_lft 43003sec
inet6 2a00:23c6:9735:5900::677/128 scope global noprefixroute dynamic
valid_lft 86203sec preferred_lft 43003sec
inet6 fd78:4c15:7f9d:0:1155:a65a:49d8:fcc8/64 scope global noprefixroute
valid_lft forever preferred_lft forever
inet6 2a00:23c6:9735:5900:9af9:fed8:2570:486b/64 scope global noprefixroute dynamic
valid_lft 315274393sec preferred_lft 315274393sec
inet6 fe80::8f73:744d:ce36:6e12/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:bd:bd:d5 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:bd:bd:d5 brd ff:ff:ff:ff:ff:ff
[root@srv1 ~]#
cat /etc/sysconfig/network-scripts/ifcfg-enp0s3
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=enp0s3
UUID=85024e4e-c958-4e04-8cda-3ed286b66822
DEVICE=enp0s3
ONBOOT=yes
IPV6_PRIVACY=no
IPADDR=192.168.1.120
PREFIX=24
GATEWAY=192.168.1.254 <<<< add dns router ip or add via this command >>> route add default gw 192.168.1.254 enp0s3
DNS1=192.168.1.254
================================
command !!!! or add on network card
route add default gw 192.168.1.254 enp0s3
=====================================
chattr -i /etc/resolv.conf
=====================================
edit below file
[root@srv1 ~]# cat /etc/resolv.conf
# Generated by NetworkManager
search localdomain
nameserver 192.168.1.254
nameserver 8.8.8.8
nameserver 8.8.4.4
chattr +i /etc/resolv.conf
=====================================
[root@srv1 ~]# ip route show
default via 192.168.1.1 dev enp0s3 proto static metric 100
192.168.1.0/24 dev enp0s3 proto kernel scope link src 192.168.1.120 metric 100
192.168.122.0/24 dev virbr0 proto kernel scope link src 192.168.122.1 linkdown
[root@srv1 ~]#
[root@srv1 ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.1.254 0.0.0.0 UG 100 0 0 enp0s3
192.168.1.0 0.0.0.0 255.255.255.0 U 100 0 0 enp0s3
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
nmcli connection down "connection_name"
nmcli connection up "connection_name"
nmcli connection down "enp0s3"
systemd-resolve --flush-caches
nmcli networking off
nmcli networking on
[root@srv1 ~]# systemctl restart NetworkManager
[root@srv1 ~]# systemctl status NetworkManager
[root@srv1 ~]# ping 8.8.8.8
PING 8.8.8.8 (8.8.8.8) 56(84) bytes of data.
64 bytes from 8.8.8.8: icmp_seq=1 ttl=117 time=6.84 ms
64 bytes from 8.8.8.8: icmp_seq=2 ttl=117 time=8.01 ms
64 bytes from 8.8.8.8: icmp_seq=3 ttl=117 time=7.42 ms
64 bytes from 8.8.8.8: icmp_seq=4 ttl=117 time=7.09 ms
64 bytes from 8.8.8.8: icmp_seq=5 ttl=117 time=7.15 ms
=============================================================================================================
dhcp setting
cat /etc/sysconfig/network-scripts/ifcfg-enp0s3
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="dhcp"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="enp0s3"
UUID="fdc2fc8c-f5b5-4e81-9c9e-b579991f2249"
DEVICE="enp0s3"
ONBOOT="yes"
[root@oragrid ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.1.254 0.0.0.0 UG 100 0 0 enp0s3
192.168.1.0 0.0.0.0 255.255.255.0 U 100 0 0 enp0s3
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@oragrid ~]#
Subscribe to:
Posts (Atom)
