Oracle Apps DBA: September 2022

Wednesday, September 21, 2022

Create SQL Plan Baselines for High Resource Intensive SQL's from AWR - 12C

=> Identify SNAP ID's for the interval

Here I am collecting resource intensive SQL's ran on a week from Monday (12-SEP-22) to Friday (16-SEP-22)

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SITPDB READ WRITE NO

SQL> alter session set container=SITPDB;

Session altered.

SQL>
SQL>
SQL> select min(snap_id) from dba_hist_snapshot where trunc(begin_interval_time)='12-SEP-22';

MIN(SNAP_ID)
------------
3324

SQL> select max(snap_id) from dba_hist_snapshot where trunc(begin_interval_time)='16-SEP-22';

MAX(SNAP_ID)
------------
3439

SQL> select user from v$database;

USER
------------------------------
SYS
SQL>

=> Create an AWR Baseline

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 3324,
end_snap_id => 3439,
baseline_name => 'weekly_baseline_sep_12_16');
END;
/

PL/SQL procedure successfully completed.
SQL>

=> Create a SQL Tuning Set Object

BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'weekly_awr_dev1'
,description => 'STS from AWR');
END;
/

PL/SQL procedure successfully completed.
SQL>

=> Populate the SQL Tuning Set with High-Resource Queries Found in AWR Baseline

Here I am populating top 20 SQL's based on elapsed_time

DECLARE
base_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN base_cur FOR
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
'weekly_baseline_sep_12_16', null, null,'elapsed_time',
null, null, null, 20)) x;
dbms_sqltune.load_sqlset(
sqlset_name => 'weekly_awr_dev1',
populate_cursor => base_cur);
END;
/

PL/SQL procedure successfully completed.
SQL>

To view the queries within the SQL tuning set, run below query

select * from dba_sqlset_statements where sqlset_name = 'weekly_awr_dev1';

=> Use the Tuning Set As Input to DBMS_SPM to Create Plan Baselines for Each Query Contained in the SQL Tuning Set

DECLARE
dev_plan1 PLS_INTEGER;
BEGIN
dev_plan1 := dbms_spm.load_plans_from_sqlset(
sqlset_name=>'weekly_awr_dev1');
END;
/

PL/SQL procedure successfully completed.
SQL>

Now each query in the SQL tuning set has an enabled plan baseline associated with it.

SQL> select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines order by elapsed_time desc;

OCI VMDB System : Upgrade Database from 12c to 19c using dbcli

Environment : OCI VMDB System

OS : Oracle Linux 7

Note : dbcli commands should be run as ROOT user

=> Obtain Pre-Authenticated Request URL

You need a Pre-Authenticated Request URL (PAR URL), which is a unique URL generated for an Oracle Database 19c Oracle Home image that is stored as a zip file in the Oracle Cloud bucket.

In addition to the PAR URL, you will also need values for the following two parameters that will be supplied to the dbcli tool when you run the dbcli create-dbhome command in Step 2 of Section 2.

Version of the Oracle Database 19c Oracle Home image (--cloneVersion)

Checksum (--sha256sum) of the zip file

You can obtain the PAR URL and its two supporting parameters from Oracle Support by raising a service request as follows:

Click the Create Non-Technical SR button.

Enter Problem Summary as: Request PAR URL for Database Upgrade

Choose Problem Type: Software Download Issues > 02. Database Upgrade

Select your correct Support Identifier.

Click Next.

Bypass instructions to review displayed KM document by clicking Next.

Click Next.

Verify your contact information.

Click Submit.

=> Upgrade Grid Infrastructure to latest version

Check below link on how to update dcs client and agent and upgrade GI

https://www.apps-dba.com/2022/09/oci-vmdb-system-apply-gi-psu-using-dbcli.html

=> Install 19C Oracle Home using below command (No downtime needed)

Duration : 10 - 20 mins

# dbcli create-dbhome --cloneVersion 19.15.0.0.0 --parUrl https://objectstorage.us-phoenix-1.oraclecloud.com/p/##########/n/dev/b/DB-Images/o/CustomImage/db1915.220419.tar.gz --sha256sum ###################################

Job details
----------------------------------------------------------------
ID: ba06af5a-4349-4e05-be63-f866ca4c7afc
Description: Database Home creation with Id : 03f77e36-98c1-4f0d-ac7f-923e9073a654
Status: Created
Created: September 6, 2022 10:06:55 AM GST
Progress: 0%
Message: Create Database Home
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------

[root@sitdb ~]# dbcli describe-job -i ba06af5a-4349-4e05-be63-f866ca4c7afc
Job details
----------------------------------------------------------------
ID: ba06af5a-4349-4e05-be63-f866ca4c7afc
Description: Database Home creation with Id : 03f77e36-98c1-4f0d-ac7f-923e9073a654
Status: Running
Created: September 6, 2022 10:06:55 AM GST
Progress: 0%
Message: Create Database Home
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Database home creation September 6, 2022 10:06:56 AM GST September 6, 2022 10:06:56 AM GST Running
[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-job -i ba06af5a-4349-4e05-be63-f866ca4c7afc

Job details

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

ID: ba06af5a-4349-4e05-be63-f866ca4c7afc

Description: Database Home creation with Id : 03f77e36-98c1-4f0d-ac7f-923e9073a654

Status: Success

Created: September 6, 2022 10:06:55 AM GST

Progress: 100%

Message: Create Database Home

Error Code:

Task Name Start Time End Time Status

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

Database home creation September 6, 2022 10:06:56 AM GST September 6, 2022 10:16:18 AM GST Success

[root@sitdb ~]#

=> Run the following commands to create an nls directory on each of the Oracle RAC nodes:

unset ORACLE_HOME

unset ORACLE_UNQNAME

unset LD_LIBRARY_PATH

unset ORACLE_SID

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

[oracle@sitdb ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Creating directory /u01/app/oracle/product/19.0.0.0/dbhome_1/nls/data/9idata ...

Copying files to /u01/app/oracle/product/19.0.0.0/dbhome_1/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/19.0.0.0/dbhome_1/nls/data/9idata!

[oracle@sitdb ~]$

=> (Conditional) Revert group permissions on oracle executable.

It is recommended that you always start the database using srvctl, which automatically runs setasmgidwrap and resets the group to asmadmin.

Alternatively, you can manually run setasmgidwrap as the grid user to revert the group to asmadmin using the following commands:

$ sudo su grid

$ . oraenv

$ ORACLE_SID = [+ASM1] ? +ASM1

$ export PATH=$GRID_HOME/bin:$PATH

$ setasmgidwrap -o /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/oracle

[oracle@sitdb 19cUpgd]$ ls -ltr /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 449406720 Sep 6 10:16 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/oracle

[oracle@sitdb 19cUpgd]$

=> Download and run hcheck.sql (in both CDB and PDB) from My Oracle Support Knowledge Document 136697.1, hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above.

The hcheck.sql script is a generic lightweight script that checks for this issue and other common data dictionary problems.

Note: If the hcheck.sql script reports any HCKE-nnnn errors, contact Oracle Support before proceeding further with the upgrade.

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

06-SEP-2022 11:44:29 Elapsed: 1 secs

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

Found 0 potential problem(s) and 0 warning(s)

Note that hcheck has been run on CDB$ROOT, consider to run it in each PDB

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:

/u01/app/oracle/diag/rdbms/sitcdb_dxb1mz/SITCDB/trace/SITCDB_ora_13048_HCHECK.trc

=> Parameter Changes

SQL> create pfile='/home/oracle/init-orig.ora' from spfile;
File created.

=> unset local_listener parameter

SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_SITCDB
SQL> alter system set local_listener='';
System altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL>

=> Take count of invalid objects from CDB and PDB's

select name,user from v$database;

select count(*) from dba_objects where status='INVALID';

create table invalids_ch as (select object_name,owner from dba_objects where status='INVALID');

select count(*) from invalids_ch ;

=> Get database id and oracle home id

[root@sitdb ~]# dbcli list-databases

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID

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

17982ac7-8753-4209-bed9-2d12b8a4f355 SITCDB Si 12.1.0.2.220719 true Oltp ASM Configured 6f64870a-8b63-4530-8a9d-132438e273a5

[root@sitdb ~]# dbcli list-dbhomes

ID Name DB Version Home Location Status

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

6f64870a-8b63-4530-8a9d-132438e273a5 OraDB12102_home1 12.1.0.2.220719 /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured

03f77e36-98c1-4f0d-ac7f-923e9073a654 OraDB19000_home1 19.15.0.0.0 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured

[root@sitdb ~]#

=> Run Pre-Check

dbcli upgrade-database -i <source Oracle 12c database ID> -dh <target 19C Oracle Home ID> --upgradeOptions "-keepEvents" --precheck

[root@sitdb ~]# dbcli upgrade-database -i 17982ac7-8753-4209-bed9-2d12b8a4f355 -dh 03f77e36-98c1-4f0d-ac7f-923e9073a654 --upgradeOptions "-keepEvents" --precheck

{

"jobId" : "2009ef7f-70a3-4043-9533-5e2e9392a10d",

"status" : "Created",

"message" : null,

"errorCode" : "",

"reports" : [ ],

"createTimestamp" : "September 06, 2022 13:09:57 PM GST",

"resourceList" : [ ],

"description" : "Database upgrade precheck with dbResId : 17982ac7-8753-4209-bed9-2d12b8a4f355",

"updatedTime" : "September 06, 2022 13:09:57 PM GST",

"percentageProgress" : "0%",

"cause" : null,

"action" : null

}

[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-job -i 2009ef7f-70a3-4043-9533-5e2e9392a10d -l Verbose

Job details

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

ID: 2009ef7f-70a3-4043-9533-5e2e9392a10d

Description: Database upgrade precheck with dbResId : 17982ac7-8753-4209-bed9-2d12b8a4f355

Status: Success

Created: September 6, 2022 1:09:57 PM GST

Progress: 100%

Message:

Error Code:

Task Name Start Time End Time Status

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

Database Service upgrade precheck September 6, 2022 1:09:57 PM GST September 6, 2022 1:11:48 PM GST Success

Database Service upgrade precheck September 6, 2022 1:09:57 PM GST September 6, 2022 1:11:47 PM GST Success

Database Service upgrade precheck September 6, 2022 1:09:57 PM GST September 6, 2022 1:09:57 PM GST Success

Generating SSH key September 6, 2022 1:09:57 PM GST September 6, 2022 1:09:57 PM GST Success

Setting up ssh equivalance September 6, 2022 1:09:57 PM GST September 6, 2022 1:09:57 PM GST Success

Database Service upgrade precheck September 6, 2022 1:09:57 PM GST September 6, 2022 1:09:57 PM GST Success

Database Service upgrade precheck September 6, 2022 1:09:58 PM GST September 6, 2022 1:09:58 PM GST Success

PreCheck executePreReqs September 6, 2022 1:09:58 PM GST September 6, 2022 1:11:35 PM GST Success

Removing ssh keys September 6, 2022 1:11:35 PM GST September 6, 2022 1:11:47 PM GST Success

Removing Znode September 6, 2022 1:11:35 PM GST September 6, 2022 1:11:35 PM GST Success

Removing keys from authorized_keys file September 6, 2022 1:11:35 PM GST September 6, 2022 1:11:35 PM GST Success

Removing PrivatePublic key Files September 6, 2022 1:11:35 PM GST September 6, 2022 1:11:35 PM GST Success

Running updatedb command September 6, 2022 1:11:35 PM GST September 6, 2022 1:11:47 PM GST Success

Running SSHKey Gen September 6, 2022 1:11:47 PM GST September 6, 2022 1:11:47 PM GST Success

[root@sitdb ~]#

=> Upgrade Database (Downtime)

Duration : 2 Hours

[root@sitdb ~]# dbcli upgrade-database -i 17982ac7-8753-4209-bed9-2d12b8a4f355 -dh 03f77e36-98c1-4f0d-ac7f-923e9073a654 --upgradeOptions "-keepEvents"

{

"jobId" : "cd96cef4-b030-4690-b38c-483a1748362f",

"status" : "Created",

"message" : null,

"errorCode" : "",

"reports" : [ ],

"createTimestamp" : "September 06, 2022 13:13:54 PM GST",

"resourceList" : [ ],

"description" : "Database upgrade with resource Id : 17982ac7-8753-4209-bed9-2d12b8a4f355",

"updatedTime" : "September 06, 2022 13:13:59 PM GST",

"percentageProgress" : "0%",

"cause" : null,

"action" : null

}

[root@sitdb ~]

[root@sitdb ~]# dbcli describe-job -i cd96cef4-b030-4690-b38c-483a1748362f -l Verbose

Job details

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

ID: cd96cef4-b030-4690-b38c-483a1748362f

Description: Database upgrade with resource Id : 17982ac7-8753-4209-bed9-2d12b8a4f355

Status: Running

Created: September 6, 2022 1:13:54 PM GST

Progress: 0%

Message:

Error Code:

Task Name Start Time End Time Status

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

Database Service Upgrade September 6, 2022 1:13:59 PM GST September 6, 2022 1:13:59 PM GST Running

Database Service Upgrade September 6, 2022 1:13:59 PM GST September 6, 2022 1:13:59 PM GST Success

Database Service Upgrade September 6, 2022 1:13:59 PM GST September 6, 2022 1:13:59 PM GST Running

Database Service Upgrade September 6, 2022 1:13:59 PM GST September 6, 2022 1:14:00 PM GST Success

Generating SSH key September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:00 PM GST Success

Setting up ssh equivalance September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:00 PM GST Success

Database Service Upgrade September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:00 PM GST Success

Updating SqlNet for db upgrade. September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:00 PM GST Success

Update RECO disk Size September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:03 PM GST Success

task:TaskZJsonRpcExt_1600 September 6, 2022 1:14:00 PM GST September 6, 2022 1:14:03 PM GST Success

PreCheck executePreReqs September 6, 2022 1:14:04 PM GST September 6, 2022 1:14:04 PM GST Running

[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-job -i cd96cef4-b030-4690-b38c-483a1748362f

Job details

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

ID: cd96cef4-b030-4690-b38c-483a1748362f

Description: Database upgrade with resource Id : 17982ac7-8753-4209-bed9-2d12b8a4f355

Status: Success

Created: September 6, 2022 1:13:54 PM GST

Progress: 100%

Message:

Error Code:

Task Name Start Time End Time Status

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

PreCheck executePreReqs September 6, 2022 1:14:04 PM GST September 6, 2022 1:15:21 PM GST Success

Database Upgrade September 6, 2022 1:15:23 PM GST September 6, 2022 2:54:10 PM GST Success

drop GRP September 6, 2022 2:56:34 PM GST September 6, 2022 2:56:40 PM GST Success

[root@sitdb ~]#

=> Check CDB and PDB Status

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 SITPDB READ WRITE YES

SQL> alter pluggable database SITPDB close immediate;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 SITPDB MOUNTED

SQL> alter pluggable database SITPDB open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 SITPDB READ WRITE NO

SQL>

SQL> alter pluggable database SITPDB save state;

Pluggable database altered.

=> Check for any new invalid objects

SQL> select count(*) from dba_objects where status='INVALID' and object_name not in (select object_name from invalids_ch);

COUNT(*)

----------

SQL> alter session set container=SITPDB;

Session altered.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

SQL> exit

=> Check lspatches

[oracle@sitdb ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH

[oracle@sitdb ~]$ opatch lspatches

33308736;DATAPATCH HANGS AS CATCON TRIES TO START MANY PROCESSES IN EXA ENVIRONMENTS

31424070;APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334

34096213;Fix for Bug 34096213

33810360;CVE-2021-45943 REPORTED IN (OPEN SOURCE GEOSPATIAL FOUNDATION/GEOSPATIAL DATA ABSTRACTION LIBRARY/OPENGIS SIMPLE FEATURES REFERENCE IMPLEMENTATION (GDAL/OGR)/3.3.0)

34088989;ONE-OFF REQUEST FOR DELETE DIR FOR SUPTOOLS/TFA + SUPTOOLS/ORACHK + SUPTOOLS/ORACHK.ZIP FROM DB PSU/BP/RU/RUR

33809062;TRACKING BUG FOR REGRESSION RTI 24544369 CAUSED BY PKNAGGS_BUG-32472737 APPROVED/INCLUDED IN 21.0.0.0 ADBSBP

33613829;RDBMS - DSTV37 UPDATE - TZDATA2021E

32327201;RDBMS - DSTV36 UPDATE - TZDATA2020E

31335037;RDBMS - DSTV35 UPDATE - TZDATA2020A

30432118;MERGE REQUEST ON TOP OF 19.0.0.0.0 FOR BUGS 28852325 29997937

33808367;OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)

31732095;UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32

33810130;JDK BUNDLE PATCH 19.0.0.0.220419

33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596)

33806152;Database Release Update : 19.15.0.0.220419 (33806152)

OPatch succeeded.

[oracle@sitdb ~]$

=> Database is successfully upgraded from 12C to 19C

Friday, September 16, 2022

AWR Comparison Report

The awrddrpt.sql SQL script generates a report that compares performance attributes and configuration settings between two selected time periods on the local database instance.

The report compares top wait events, SQL performance, parameter changes etc between two snapshot intervals.

Sample Test Case : Change SGA of PDB from 6 GB to 3 GB and verify it in AWR comparison report

=> For the purpose of testing, I am changing the snapshot interval to 15 minutes

SQL> col SNAP_INTERVAL for a20
SQL> col retention for a20
SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
-------------------- --------------------
+00000 01:00:00.0 +00008 00:00:00.0

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 15,retention => 11520);

PL/SQL procedure successfully completed.

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
-------------------- --------------------
+00000 00:15:00.0 +00008 00:00:00.0
SQL>

=> Change SGA of PDB

SQL> alter session set container=SITPDB;

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 11G
sga_min_size big integer 0
sga_target big integer 6G
unified_audit_sga_queue_size integer 1048576

SQL> alter system set sga_target=3g;
System altered.

SQL>

=> Wait till snapshot generation and run awr comparison report

SQL> alter session set container=SITPDB;

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Review the init.ora parameters section in the report, to verify the sga change

=> Useful scripts:

awrddrpi.sql => for specific instance

awrgdrpt.sql => for all instances in RAC database

awrgdrpi.sql => for a set of instances in RAC database

Monday, September 12, 2022

OCI VMDB System : Delete old Oracle Home post database upgrade using dbcli

After the database upgrade from 12c to 19c, you will find two Oracle Homes on the server. Follow below steps to delete 12c oracle home

=> Find the database home ID of 12c oracle home

[root@sitdb ~]# dbcli list-dbhomes

ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
6f64870a-8b63-4530-8a9d-132438e273a5 OraDB12102_home1 12.1.0.2.220719 /u01/app/oracle/product/12.1.0.2/dbhome_1 Configured
03f77e36-98c1-4f0d-ac7f-923e9073a654 OraDB19000_home1 19.15.0.0.0 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
[root@sitdb ~]#

=> Run delete command

dbcli delete-dbhome -i 6f64870a-8b63-4530-8a9d-132438e273a5

=> [root@sitdb log]# dbcli delete-dbhome -i 6f64870a-8b63-4530-8a9d-132438e273a5

[root@sitdb log]# dbcli describe-job -i 9b02b85f-6123-4def-a738-5062701483bb

Job details
----------------------------------------------------------------
ID: 9b02b85f-6123-4def-a738-5062701483bb
Description: Database Home OraDB12102_home1 Deletion with id 6f64870a-8b63-4530-8a9d-132438e273a5
Status: Failure
Created: September 6, 2022 3:38:50 PM GST
Progress: 5%
Message: DCS-10329:Failed to delete database home with id : 6f64870a-8b63-4530-8a9d-132438e273a5.
Error Code: DCS-10329
Cause: Failed to delete database home.
Action: Contact Oracle support for assistance.
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
DbHome service deletion for 6f64870a-8b63-4530-8a9d-132438e273a5 September 6, 2022 3:38:50 PM GST September 6, 2022 3:39:10 PM GST Failure
DbHome service deletion for 6f64870a-8b63-4530-8a9d-132438e273a5 September 6, 2022 3:38:50 PM GST September 6, 2022 3:39:10 PM GST Failure
Delete dbhome with ID: 6f64870a-8b63-4530-8a9d-132438e273a5 September 6, 2022 3:38:51 PM GST September 6, 2022 3:39:09 PM GST Failure

[root@sitdb log]#

=> Failed due to below perl errors

com.oracle.dcs.commons.utils.CommonsUtils: perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LANG = "en_US.UTF-16"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
perl: warning: Setting locale failed.

Fix: Delete all patches from 12c home

unset 19c environment and set 12c environment

unset ORACLE_HOME
unset ORACLE_UNQNAME
unset LD_LIBRARY_PATH
unset ORACLE_SID
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

[oracle@sitdb dbhome_1]$ opatch lspatches
33066573;
31413047;
21923026;
33613829;
34421053;
34269744;
34006650;
34006614;
32327201;
31335037;
30432084;
33912892;DATABASE PERL UPDATE IN 12.1.0.2.0 TO V5.32-1 (CVE-2022-23990 - LIBEXPAT UPDATE)
34086863;Database PSU 12.1.0.2.220719,Oracle JavaVM Component (JUL2022)
34113570;JDK BUNDLE PATCH 12.1.0.2.220719
34057733;Database Bundle Patch : 12.1.0.2.220719 (34057733)
32758932;OCW PATCH SET UPDATE 12.1.0.2.210720 (32758932)
OPatch succeeded.

[oracle@sitdb dbhome_1]$

opatch nrollback -id 33066573,31413047,21923026,33613829,34421053,34269744,34006650,34006614,32327201,31335037,30432084,33912892,34086863,34113570,34057733,32758932

[oracle@sitdb dbhome_1]$ opatch lspatches

There are no Interim patches installed in this Oracle Home "/u01/app/oracle/product/12.1.0.2/dbhome_1".
OPatch succeeded.

[oracle@sitdb dbhome_1]$

=> oracle home status will be still in deleting state. Update it backup to configured state before issuing delete command

[root@sitdb ~]# su - mysql

Last login: Wed Aug 24 09:53:41 +04 2022

[mysql@sitdb ~]$ /opt/oracle/mysql_compact/mysql-shell/bin/mysqlsh

MySQL Shell 8.0.21-commercial
Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > \sql
Switching to SQL mode... Commands end with ;
MySQL SQL > \connect root@localhost:3306
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl SQL > \use metastore
Default schema set to `metastore`.
Fetching table and column names from `metastore` for auto-completion... Press ^C to stop.
MySQL localhost:3306 ssl metastore SQL >
MySQL localhost:3306 ssl metastore SQL > select * from DatabaseHome;
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
| id | updatedTime | createTime | name | status | dbEdition | dbHomeLocation | dbVersion | isCustomClone | ocid | unifiedAuditing |
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
| 03f77e36-98c1-4f0d-ac7f-923e9073a654 | 2022-09-06 10:16:17.755000 | 2022-09-06 10:06:54.962000 | OraDB19000_home1 | Configured | EE | /u01/app/oracle/product/19.0.0.0/dbhome_1 | 19.15.0.0.0 | 0 | NULL | false |
| 6f64870a-8b63-4530-8a9d-132438e273a5 | 2022-09-06 15:38:51.746000 | 2022-08-24 09:56:58.041000 | OraDB12102_home1 | Deleting | EE | /u01/app/oracle/product/12.1.0.2/dbhome_1 | 12.1.0.2.220719 | 0 | NULL | false |
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
2 rows in set (0.0023 sec)
MySQL localhost:3306 ssl metastore SQL > update DatabaseHome set status='Configured' where id='6f64870a-8b63-4530-8a9d-132438e273a5';
Query OK, 1 row affected (0.0125 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL localhost:3306 ssl metastore SQL > commit;
Query OK, 0 rows affected (0.0002 sec)
MySQL localhost:3306 ssl metastore SQL > select * from DatabaseHome;
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
| id | updatedTime | createTime | name | status | dbEdition | dbHomeLocation | dbVersion | isCustomClone | ocid | unifiedAuditing |
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
| 03f77e36-98c1-4f0d-ac7f-923e9073a654 | 2022-09-06 10:16:17.755000 | 2022-09-06 10:06:54.962000 | OraDB19000_home1 | Configured | EE | /u01/app/oracle/product/19.0.0.0/dbhome_1 | 19.15.0.0.0 | 0 | NULL | false |
| 6f64870a-8b63-4530-8a9d-132438e273a5 | 2022-09-06 15:38:51.746000 | 2022-08-24 09:56:58.041000 | OraDB12102_home1 | Configured | EE | /u01/app/oracle/product/12.1.0.2/dbhome_1 | 12.1.0.2.220719 | 0 | NULL | false |
+--------------------------------------+----------------------------+----------------------------+------------------+------------+-----------+-------------------------------------------+-----------------+---------------+------+-----------------+
2 rows in set (0.0010 sec)
MySQL localhost:3306 ssl metastore SQL >
MySQL localhost:3306 ssl metastore SQL > \quit
Bye!

[mysql@sitdb ~]$ exit

[root@sitdb ~]# dbcli list-dbhomes

[root@sitdb ~]#

=> Now run the delete command with force option

[root@sitdb ~]# dbcli delete-dbhome -i 6f64870a-8b63-4530-8a9d-132438e273a5 --force

[root@sitdb ~]# dbcli describe-job -i 3784213a-f374-4d07-aec1-603bdc4e55d8

Job details
----------------------------------------------------------------
ID: 3784213a-f374-4d07-aec1-603bdc4e55d8
Description: Database Home OraDB12102_home1 Deletion with id 6f64870a-8b63-4530-8a9d-132438e273a5
Status: Success
Created: September 12, 2022 1:39:51 PM GST
Progress: 100%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Delete dbhome with ID: 6f64870a-8b63-4530-8a9d-132438e273a5 September 12, 2022 1:39:53 PM GST September 12, 2022 1:41:11 PM GST Success
[root@sitdb ~]# dbcli list-dbhomes
ID Name DB Version Home Location Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
03f77e36-98c1-4f0d-ac7f-923e9073a654 OraDB19000_home1 19.15.0.0.0 /u01/app/oracle/product/19.0.0.0/dbhome_1 Configured
[root@sitdb ~]#

Saturday, September 10, 2022

OCI VMDB System : Apply GI PSU using dbcli

Environment : OCI VMDB System
OS : Oracle Linux 7

=> dbcli commands should be run as root user.

=> Update dcs client and agent

[root@sitdb ~]# cliadm update-dbcli
Job details
----------------------------------------------------------------
ID: 1ac41360-f581-4ce8-b631-2b91e9ba1d64
Description: DcsCli patching
Status: Created
Created: September 6, 2022 10:03:16 AM GST
Message: Dcs cli will be updated
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------

[root@sitdb ~]# dbcli describe-job -i 1ac41360-f581-4ce8-b631-2b91e9ba1d64

Job details
----------------------------------------------------------------
ID: 1ac41360-f581-4ce8-b631-2b91e9ba1d64
Description: DcsCli patching
Status: Success
Created: September 6, 2022 10:03:16 AM GST
Progress: 100%
Message:
Error Code:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
[root@sitdb ~]# rpm -qa |grep dcs-cli
dcs-cli-22.3.1.0.0_220727.0606-1.x86_64
[root@sitdb ~]# rpm -qa |grep dcs-agent
dcs-agent-22.3.1.0.0_220727.0606-12.x86_64
[root@sitdb ~]#

=> Verify the latest version of GI available. In this case installed version is 19.15 and available version is 19.16

[root@sitdb ~]# dbcli describe-latestpatch

componentType availableVersion
--------------- --------------------
gi 12.2.0.1.220719
gi 12.1.0.2.220719
gi 18.16.0.0.0
gi 19.16.0.0.0
gi 21.7.0.0.0
db 11.2.0.4.220719
db 12.2.0.1.220719
db 12.1.0.2.220719
db 18.16.0.0.0
db 19.16.0.0.0
db 21.7.0.0.0

[root@sitdb ~]# dbcli describe-component

System Version
---------------
22.3.1.0.0
Component Installed Version Available Version
---------------------------------------- -------------------- --------------------
GI 19.15.0.0.0 19.16.0.0
DB 12.1.0.2.220419 12.1.0.2.220719
[root@sitdb ~]#

=> Run Pre Check

[root@sitdb ~]# dbcli update-server --precheck

{
"jobId" : "62323bb3-012a-41f6-8971-d95ff68e29e2",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "August 24, 2022 11:33:10 AM GST",
"resourceList" : [ ],
"description" : "Server Patching Prechecks",
"updatedTime" : "August 24, 2022 11:33:10 AM GST",
"percentageProgress" : "0%",
"cause" : null,
"action" : null
}
[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-job -i 62323bb3-012a-41f6-8971-d95ff68e29e2

Job details
----------------------------------------------------------------
ID: 62323bb3-012a-41f6-8971-d95ff68e29e2
Description: Server Patching Prechecks
Status: Success
Created: August 24, 2022 11:33:10 AM GST
Progress: 100%
Message:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching August 24, 2022 11:33:10 AM GST August 24, 2022 11:35:38 AM GST Success
Server Patching August 24, 2022 11:35:38 AM GST August 24, 2022 11:38:19 AM GST Success
[root@sitdb ~]#

=> Apply Latest PSU to GI (Downtime Activity)

[root@sitdb ~]# dbcli update-server

{
"jobId" : "ed3c3491-cafd-47e9-9eb9-642d78035cea",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "August 24, 2022 11:46:37 AM GST",
"resourceList" : [ ],
"description" : "Server Patching",
"updatedTime" : "August 24, 2022 11:46:37 AM GST",
"percentageProgress" : "0%",
"cause" : null,
"action" : null
}
[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-job -i ed3c3491-cafd-47e9-9eb9-642d78035cea

Job details
----------------------------------------------------------------
ID: ed3c3491-cafd-47e9-9eb9-642d78035cea
Description: Server Patching
Status: Success
Created: August 24, 2022 11:46:37 AM GST
Progress: 100%
Message:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching August 24, 2022 11:46:38 AM GST August 24, 2022 11:48:24 AM GST Success
Server Patching August 24, 2022 11:48:24 AM GST August 24, 2022 12:14:00 PM GST Success
[root@sitdb ~]#

[root@sitdb ~]# dbcli describe-component

System Version
---------------
22.2.3.1.0
Component Installed Version Available Version
---------------------------------------- -------------------- --------------------
GI 19.16.0.0.0 up-to-date
DB 12.1.0.2.220419 12.1.0.2.220719
[root@sitdb ~]#

Oracle Apps DBA

Wednesday, September 21, 2022

Create SQL Plan Baselines for High Resource Intensive SQL's from AWR - 12C

OCI VMDB System : Upgrade Database from 12c to 19c using dbcli

Friday, September 16, 2022

AWR Comparison Report

Monday, September 12, 2022

OCI VMDB System : Delete old Oracle Home post database upgrade using dbcli

Saturday, September 10, 2022

OCI VMDB System : Apply GI PSU using dbcli

Non-Equijoins and Self-Joins in Oracle SQL

Search This Blog