--- @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
Anuj Singh Oracle DBA
Search This Blog
Total Pageviews
Wednesday, 8 January 2025
dbupgdiag script ---- @dbupgdiag.sql
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE Script
ORA-14508: ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE (Doc ID 111990.1) set linesize 300 pagesize 300 col xx for a100 select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME dba_TAB_PARTITIONS FROM dba_TAB_PARTITIONS WHERE TABLE_NAME ='XXXX'; set pagesize 0 -- spool p.sql select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME || chr(10) ||'analyze table ' ||TABLE_OWNER||'.'||TABLE_NAME || ' partition (' ||PARTITION_NAME ||') validate structure cascade online into invalid_rows;' || chr(10) || ' select count(*) from invalid_rows ;' FROM dba_TAB_PARTITIONS WHERE TABLE_NAME ='XXX'; -- spool off
Friday, 3 January 2025
coe_xfr_sql_profile.sql example
[oracle@localhost ~]$ sqlplus system/system@//localhost:1521/free ***************************************************************************************************************** --- coe_xfr_sql_profile.sql --- coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE]; -- Transfer (copy) a SQL Profile from PDBx on CDBa into PDBy on CDBb SPO coe_xfr_sql_profile.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>; SET SERVEROUT ON SIZE UNL; REM REM $Header: coe_xfr_sql_profile.sql 2020/03/10 carlos.sierra $ REM REM AUTHOR REM Carlos Sierra REM REM SCRIPT REM coe_xfr_sql_profile.sql REM REM DESCRIPTION REM This script generates another that contains the commands to REM create a manual custom SQL Profile out of a known plan from REM memory or AWR. The manual custom profile can be implemented REM into the same SOURCE system where the plan was retrieved, REM or into another similar TARGET system that has same schema REM objects referenced by the SQL that generated the known plan. REM REM PRE-REQUISITES REM 1. Oracle Tuning Pack license. REM REM PARAMETERS REM 1. SQL_ID (required) REM 2. Plan Hash Value for which a manual custom SQL Profile is REM needed (required). A list of known plans is presented. REM You may choose from list provided or enter a valid phv REM from a version of the SQL modified with Hints. REM REM EXECUTION REM 1. Connect into SQL*Plus as user with access to data dictionary. REM Do not use SYS. REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and REM plan hash value (parameters can be passed inline or until REM requested). REM REM EXAMPLE REM # sqlplus system REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE]; REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663; REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm; REM SQL> START coe_xfr_sql_profile.sql; REM REM NOTES REM 1. For possible errors see coe_xfr_sql_profile.log REM 2. If SQLT is installed in SOURCE, you can use instead: REM sqlt/utl/sqltprofile.sql REM 3. Be aware that using DBMS_SQLTUNE requires a license for REM Oracle Tuning Pack. REM 4. Use a DBA user but not SYS. REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1" REM Then you may consider this change (only in a test and disposable system): REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500); REM SET TERM ON ECHO OFF; PRO PRO Parameter 1: PRO SQL_ID (required) PRO DEF sql_id = '&1.'; UNDEF 1; PRO WITH p AS ( SELECT plan_hash_value FROM gv$sql_plan WHERE sql_id = TRIM('&&sql_id.') AND other_xml IS NOT NULL UNION SELECT plan_hash_value FROM dba_hist_sql_plan WHERE sql_id = TRIM('&&sql_id.') AND dbid = (SELECT dbid FROM v$database) AND other_xml IS NOT NULL ), m AS ( SELECT plan_hash_value, SUM(elapsed_time)/SUM(executions) avg_et_secs, SUM(executions) executions FROM gv$sql WHERE sql_id = TRIM('&&sql_id.') AND executions > 0 GROUP BY plan_hash_value ), a AS ( SELECT plan_hash_value, SUM(elapsed_time_delta)/SUM(executions_delta) avg_et_secs, SUM(executions_delta) executions FROM dba_hist_sqlstat WHERE sql_id = TRIM('&&sql_id.') AND executions_delta > 0 GROUP BY plan_hash_value ) SELECT TO_CHAR(ROUND(m.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_mem, TO_CHAR(ROUND(a.avg_et_secs/1e6, 6), '999,990.000000') avg_et_secs_awr, p.plan_hash_value, m.executions executions_mem, a.executions executions_awr --TO_CHAR(ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 6), '999,990.000000') avg_et_secs FROM p, m, a WHERE p.plan_hash_value = m.plan_hash_value(+) AND p.plan_hash_value = a.plan_hash_value(+) ORDER BY NVL(m.avg_et_secs, a.avg_et_secs) NULLS LAST, a.avg_et_secs; PRO PRO Parameter 2: PRO PLAN_HASH_VALUE (required) PRO DEF plan_hash_value = '&2'; UNDEF 2; PRO PRO Values passed to coe_xfr_sql_profile: PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO SQL_ID : "&&sql_id." PRO PLAN_HASH_VALUE: "&&plan_hash_value." PRO SET TERM OFF ECHO ON; WHENEVER SQLERROR EXIT SQL.SQLCODE; -- trim parameters COL sql_id NEW_V sql_id FOR A30; COL plan_hash_value NEW_V plan_hash_value FOR A30; SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL; VAR sql_text CLOB; VAR sql_text2 CLOB; VAR other_xml CLOB; EXEC :sql_text := NULL; EXEC :sql_text2 := NULL; EXEC :other_xml := NULL; -- get sql_text from memory DECLARE l_sql_text VARCHAR2(32767); BEGIN -- 10g see bug 5017909 FOR i IN (SELECT DISTINCT piece, sql_text FROM gv$sqltext_with_newlines WHERE sql_id = TRIM('&&sql_id.') ORDER BY 1, 2) LOOP IF :sql_text IS NULL THEN DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); END IF; -- removes NUL characters l_sql_text := REPLACE(i.sql_text, CHR(00), ' '); -- adds a NUL character at the end of each line DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00)); END LOOP; -- if found in memory then sql_text is not null IF :sql_text IS NOT NULL THEN DBMS_LOB.CLOSE(:sql_text); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM); :sql_text := NULL; END; / SELECT :sql_text FROM DUAL; -- get sql_text from awr DECLARE l_sql_text VARCHAR2(32767); l_clob_size NUMBER; l_offset NUMBER; BEGIN IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN SELECT sql_text INTO :sql_text2 FROM dba_hist_sqltext WHERE sql_id = TRIM('&&sql_id.') AND sql_text IS NOT NULL AND ROWNUM = 1; END IF; -- if found in awr then sql_text2 is not null IF :sql_text2 IS NOT NULL THEN l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0); l_offset := 1; DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); -- store in clob as 64 character pieces plus a NUL character at the end of each piece WHILE l_offset < l_clob_size LOOP IF l_clob_size - l_offset > 64 THEN l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' '); ELSE -- last piece l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' '); END IF; DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00)); l_offset := l_offset + 64; END LOOP; DBMS_LOB.CLOSE(:sql_text); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM); :sql_text := NULL; END; / SELECT :sql_text2 FROM DUAL; SELECT :sql_text FROM DUAL; -- validate sql_text SET TERM ON; BEGIN IF :sql_text IS NULL THEN RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); END IF; END; / SET TERM OFF; -- get other_xml from memory BEGIN FOR i IN (SELECT other_xml FROM gv$sql_plan WHERE sql_id = TRIM('&&sql_id.') AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) AND other_xml IS NOT NULL ORDER BY child_number, id) LOOP :other_xml := i.other_xml; EXIT; -- 1st END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM); :other_xml := NULL; END; / -- get other_xml from awr BEGIN IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN FOR i IN (SELECT other_xml FROM dba_hist_sql_plan WHERE sql_id = TRIM('&&sql_id.') AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) AND dbid = (SELECT dbid FROM v$database) AND other_xml IS NOT NULL ORDER BY id) LOOP :other_xml := i.other_xml; EXIT; -- 1st END LOOP; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM); :other_xml := NULL; END; / -- get other_xml from memory from modified SQL BEGIN IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN FOR i IN (SELECT other_xml FROM gv$sql_plan WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) --WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) AND other_xml IS NOT NULL ORDER BY child_number, id) LOOP :other_xml := i.other_xml; EXIT; -- 1st END LOOP; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM); :other_xml := NULL; END; / -- get other_xml from awr from modified SQL BEGIN IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN FOR i IN (SELECT other_xml FROM dba_hist_sql_plan WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) --WHERE full_plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')) AND dbid = (SELECT dbid FROM v$database) AND other_xml IS NOT NULL ORDER BY id) LOOP :other_xml := i.other_xml; EXIT; -- 1st END LOOP; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM); :other_xml := NULL; END; / SELECT :other_xml FROM DUAL; -- validate other_xml SET TERM ON; BEGIN IF :other_xml IS NULL THEN RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); END IF; END; / SET TERM OFF; -- generates script that creates sql profile in target system: SET ECHO OFF; PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql. SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR; SET SERVEROUT ON SIZE UNL FOR WOR; SPO OFF; SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql; DECLARE l_pos NUMBER; l_clob_size NUMBER; l_offset NUMBER; l_sql_text VARCHAR2(32767); l_len NUMBER; l_hint VARCHAR2(32767); BEGIN DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;'); DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM AUTHOR'); DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM SCRIPT'); DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION'); DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql'); DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom'); DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash'); DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..'); DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script'); DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature'); DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.'); DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM PARAMETERS'); DBMS_OUTPUT.PUT_LINE('REM None.'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM EXAMPLE'); DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('REM NOTES'); DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.'); DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.'); DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.'); DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:'); DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');'); DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license'); DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.'); DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired'); DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.'); DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original'); DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;'); DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;'); DBMS_OUTPUT.PUT_LINE('REM'); DBMS_OUTPUT.PUT_LINE('DECLARE'); DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;'); DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;'); DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS'); DBMS_OUTPUT.PUT_LINE('BEGIN'); DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);'); DBMS_OUTPUT.PUT_LINE('END wa;'); DBMS_OUTPUT.PUT_LINE('BEGIN'); DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);'); DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);'); DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.'); DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),'); DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.'); l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0); l_offset := 1; WHILE l_offset < l_clob_size LOOP l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset); IF l_pos > 0 THEN l_len := l_pos - l_offset; ELSE -- last piece l_len := l_clob_size - l_pos + 1; END IF; l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset); /* cannot do such 3 replacement since a line could end with a comment using "--" l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP */ l_offset := l_offset + l_len + 1; IF l_len > 0 THEN IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN l_sql_text := '['||l_sql_text||']'; ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN l_sql_text := '{'||l_sql_text||'}'; ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN l_sql_text := '<'||l_sql_text||'>'; ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN l_sql_text := '('||l_sql_text||')'; ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN l_sql_text := '"'||l_sql_text||'"'; ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN l_sql_text := '|'||l_sql_text||'|'; ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN l_sql_text := '~'||l_sql_text||'~'; ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN l_sql_text := '^'||l_sql_text||'^'; ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN l_sql_text := '@'||l_sql_text||'@'; ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN l_sql_text := '#'||l_sql_text||'#'; ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN l_sql_text := '%'||l_sql_text||'%'; ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN l_sql_text := '$'||l_sql_text||'$'; ELSE l_sql_text := CHR(96)||l_sql_text||CHR(96); END IF; DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');'); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);'); DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR('); DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'','); FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d) LOOP l_hint := i.hint; WHILE NVL(LENGTH(l_hint), 0) > 0 LOOP IF LENGTH(l_hint) <= 500 THEN DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'','); l_hint := NULL; ELSE l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1); DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'','); l_hint := ' '||SUBSTR(l_hint, l_pos); END IF; END LOOP; END LOOP; DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');'); DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);'); DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);'); DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE ('); DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,'); DBMS_OUTPUT.PUT_LINE('profile => h,'); DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'','); DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''','); DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'','); DBMS_OUTPUT.PUT_LINE('validate => TRUE,'); DBMS_OUTPUT.PUT_LINE('replace => TRUE,'); DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );'); DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);'); DBMS_OUTPUT.PUT_LINE('END;'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE'); DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;'); DBMS_OUTPUT.PUT_LINE('PRINT signature'); DBMS_OUTPUT.PUT_LINE('PRINT signaturef'); DBMS_OUTPUT.PUT_LINE('PRO'); DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created'); DBMS_OUTPUT.PUT_LINE('PRO'); DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";'); DBMS_OUTPUT.PUT_LINE('SPO OFF;'); DBMS_OUTPUT.PUT_LINE('PRO'); DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed'); END; / SPO OFF; SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>; SET SERVEROUT OFF; PRO PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql PRO on TARGET system in order to create a custom SQL Profile PRO with plan &&plan_hash_value linked to adjusted sql_text. PRO UNDEFINE 1 2 sql_id plan_hash_value CL COL PRO PRO COE_XFR_SQL_PROFILE completed. ********************************************************** set linesize window SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')); set lines 300 col sql_sql_text head SQL_TEXT format a50 word_wrap col sql_child_number head CH# for 9999 column EXACT_MATCHING_SIGNATURE format 99999999999999999999 column force_matching_signature format 99999999999999999999 select s.sql_id, s.hash_value, s.plan_hash_value, s.force_matching_signature, s.EXACT_MATCHING_SIGNATURE, t.phv2, s.sql_text sql_sql_text from v$sql s, v$sql_plan p ,xmltable('for $i in /other_xml/info where $i/@type eq "plan_hash_2" return $i' passing xmltype(p.other_xml) columns phv2 number path '/' ) t where s.sql_id=p.sql_id AND s.plan_hash_value=p.plan_hash_value AND s.child_number=p.CHILD_NUMBER AND p.other_xml is not null -- AND s.sql_text like 'sql_text%' and s.sql_id in ('dg8duxp7zprxp','29v6w386wqnzt') order by 3 desc coe_xfr_sql_profile.sql dg8duxp7zprxp 1288918533 coe_xfr_sql_profile.sql dg8duxp7zprxp PLAN_HASH_VALUE SQL> ed coe_xfr_sql_profile SQL> @coe_xfr_sql_profile Parameter 1: SQL_ID (required) Enter value for 1: dg8duxp7zprxp AVG_ET_SECS_MEM AVG_ET_SECS_AWR PLAN_HASH_VALUE EXECUTIONS_MEM EXECUTIONS_AWR --------------- --------------- --------------- -------------- -------------- 0.017320 1130139812 1 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 1130139812 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "dg8duxp7zprxp" PLAN_HASH_VALUE: "1130139812" SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; SQL>BEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 4 END IF; 5 END; 6 / SQL>SET TERM OFF; Execute coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql on TARGET system in order to create a custom SQL Profile with plan 1130139812 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. ########################## SQL>!grep -i force coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); SQL> SQL>@coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql 11.4.4.4 2025/01/03 carlos.sierra $ SQL>REM SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved. SQL>REM SQL>REM AUTHOR SQL>REM carlos.sierra@oracle.com SQL>REM SQL>REM SCRIPT SQL>REM coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script is generated by coe_xfr_sql_profile.sql SQL>REM It contains the SQL*Plus commands to create a custom SQL>REM SQL Profile for SQL_ID dg8duxp7zprxp based on plan hash SQL>REM value 1130139812. SQL>REM The custom SQL Profile to be created by this script SQL>REM will affect plans for SQL commands with signature SQL>REM matching the one for SQL Text below. SQL>REM Review SQL Text and adjust accordingly. SQL>REM SQL>REM PARAMETERS SQL>REM None. SQL>REM SQL>REM EXAMPLE SQL>REM SQL> START coe_xfr_sql_profile_dg8duxp7zprxp_1130139812.sql; SQL>REM SQL>REM NOTES SQL>REM 1. Should be run as SYSTEM or SYSDBA. SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege. SQL>REM 3. SOURCE and TARGET systems can be the same or similar. SQL>REM 4. To drop this custom SQL Profile after it has been created: SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_dg8duxp7zprxp_1130139812'); SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license SQL>REM for the Oracle Tuning Pack. SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below. SQL>REM By doing so you can create a custom SQL Profile for the original SQL>REM SQL but with the Plan captured from the modified SQL (with Hints). SQL>REM SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL>REM SQL>VAR signature NUMBER; SQL>VAR signaturef NUMBER; SQL>REM SQL>DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 PROCEDURE wa (p_line IN VARCHAR2) IS 5 BEGIN 6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line); 7 END wa; 8 BEGIN 9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE); 10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE); 11 -- SQL Text pieces below do not have to be of same length. 12 -- So if you edit SQL Text (i.e. removing temporary Hints), 13 -- there is no need to edit or re-align unmodified pieces. 14 wa(q'[with s1 as ( 15 select 16 a.n2 n2, 17 a.vc vc 18 from 19 t1 a 20 )]'); 21 wa(q'[, 22 s2 as ( 23 select 24 b.n4 n4, 25 b.vd vd 26 from 27 t2 b 28 ) 29 se]'); 30 wa(q'[lect 31 s1.n2, 32 s1.vc, 33 s2.n4, 34 s2.vd, 35 s1.n2||s2.n4 as N 36 fro]'); 37 wa(q'[m 38 s1, 39 s2 40 where 41 s1.n2 = s2.n4 42 and s1.vc = 'xY' 43 AND s2]'); 44 wa(q'[.n4 = 0]'); 45 DBMS_LOB.CLOSE(sql_txt); 46 h := SYS.SQLPROF_ATTR( 47 q'[BEGIN_OUTLINE_DATA]', 48 q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 49 q'[OPTIMIZER_FEATURES_ENABLE('23.1.0')]', 50 q'[DB_VERSION('23.1.0')]', 51 q'[ALL_ROWS]', 52 q'[OUTLINE_LEAF(@"SEL$18780C30")]', 53 q'[MERGE(@"SEL$1" >"SEL$3")]', 54 q'[MERGE(@"SEL$2" >"SEL$3")]', 55 q'[OUTLINE(@"SEL$3")]', 56 q'[OUTLINE(@"SEL$1")]', 57 q'[OUTLINE(@"SEL$2")]', 58 q'[FULL(@"SEL$18780C30" "B"@"SEL$2")]', 59 q'[INDEX_RS_ASC(@"SEL$18780C30" "A"@"SEL$1" ("T1"."N2"))]', 60 q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$18780C30" "A"@"SEL$1")]', 61 q'[LEADING(@"SEL$18780C30" "B"@"SEL$2" "A"@"SEL$1")]', 62 q'[USE_MERGE_CARTESIAN(@"SEL$18780C30" "A"@"SEL$1")]', 63 q'[END_OUTLINE_DATA]'); 64 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 65 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE); 66 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 67 sql_text => sql_txt, 68 profile => h, 69 name => 'coe_dg8duxp7zprxp_1130139812', 70 description => 'coe dg8duxp7zprxp 1130139812 '||:signature||' '||:signaturef||'', 71 category => 'DEFAULT', 72 validate => TRUE, 73 replace => TRUE, 74 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 75 DBMS_LOB.FREETEMPORARY(sql_txt); 76 END; 77 / PL/SQL procedure successfully completed. SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 5336375310114799588 SIGNATUREF --------------------- 14577638555275281096 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_dg8duxp7zprxp_1130139812 completed col name for a35 col created for a30 col FORCE_MATCHING for a25 SELECT --s.sql_id, -- s.plan_hash_value, p.name, p.CREATED, -- p.LAST_MODIFIED, p.STATUS, p.FORCE_MATCHING FROM dba_sql_profiles p, v$sqlstats s where p.signature = s.FORCE_MATCHING_SIGNATURE AND s.sql_id='dg8duxp7zprxp'; set linesize 300 col name for a35 col created for a30 col FORCE_MATCHING for a25 SELECT --s.sql_id, -- s.plan_hash_value, p.name, p.CREATED, -- p.LAST_MODIFIED, p.STATUS, p.FORCE_MATCHING FROM dba_sql_profiles p ; NAME CREATED STATUS FORCE_MATCHING ----------------------------------- ------------------------------ -------- ------------------------- coe_dg8duxp7zprxp_1130139812 03-JAN-25 02.30.19.728687 PM ENABLED NO
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=4163mtu 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 ~]#
Sunday, 22 December 2024
Linux version Or unix version
cat /etc/oracle-release cat /etc/redhat-release cat /etc/os-release lsb_release -id lsb_release -a [root@oragrid ~]# lsb_release -a LSB Version: :core-4.1-amd64:core-4.1-noarch Distributor ID: CentOS Description: CentOS Linux release 8.5.2111 Release: 8.5.2111 Codename: n/a [root@oragrid ~]# lsb_release -d Description: CentOS Linux release 8.5.2111 [root@oragrid ~]# lsb_release -i -s CentOS [root@oragrid ~]# lsb_release -rcs 8.5.2111 n/a [root@oragrid ~]# lsb_release -s :core-4.1-amd64:core-4.1-noarch [root@oragrid ~]# lsb_release -si CentOS [root@oragrid ~]# cat /etc/redhat-release CentOS Linux release 8.5.2111 [root@oragrid ~]# cat /etc/os-release NAME="CentOS Linux" VERSION="8" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8"
Friday, 20 December 2024
[INS-30502] No ASM Disk Group found
[INS-30502] No ASM Disk Group found
as root
vi /etc/group
dba:x:54322:grid,oracle oper:x:54323:oracle backupdba:x:54324: dgdba:x:54325: kmdba:x:54326: asmdba:x:54327:grid,oracle <<<<< oracle should be part of asmdba asmoper:x:54328:grid asmadmin:x:54329:grid racdba:x:54330: postdrop:x:90: postfix:x:89: osdba:x:1020:oracle,grid osoper:x:1030:oracle osbackupdba:x:1040:oracle oskmdba:x:1050:oracle osdgdba:x:1060:oracle osracdba:x:1070:oracle [root@oragrid ~]#
Subscribe to:
Posts (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)