Soumya's Database Blog : 2022

How to Resolve OEM Agent ‘Availability Evaluation Error’

Issue : While registering a database as a target in OEM 13c the agent was failing during start with following error ‘Availability Evaluation Error’

Solution:-

To resolve the issue make I followed the following steps in following order .

Run this from db target server which you are trying to add as a target in OEM

cd $AGENT_HOME/agent_inst/bin

$ ping omshost

$ nslookup omshost

$ emctl pingOMS

$ emctl config agent addinternaltargets

$ emctl secure agent

$ emctl config agent listtargets

$ emctl clearstate agent

$ emctl upload agent

$ emctl reload agent

$ emctl start agent

The agent should show UP now, otherwise continue with the following steps:

$ emctl stop agent

$ emctl secure agent

$ emctl clearstate agent

$ emctl secure agent

$ emctl start agent

$ emctl upload agent

$ emctl reload agent

$ emctl pingOMS

$ emctl status agent

How to fix No ADR base is set message

No ADR homes are set

Recently for one my customer, I upgraded database from 12c to 19c. So after upgrade when I tried to use adrci utility It showed me “No ADR base is set” message

[oracle@oda02 ~]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Sun Nov 20 12:12:58 2022

No ADR base is set

adrci> show homes

No ADR homes are set

Solution:-

The reason behind above message is adrci utility is unable to find any information about ADR base .

Create following directory in new oracle home

[oracle@oda02 ~]$ echo $ORACLE_HOME

/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1

[oracle@oda02 dbhome_1]$ mkdir -p $ORACLE_HOME/log/diag

Now copy the following file from old ORACLE_HOME i.e. 12c home in our case

[oracle@oda02$ cd /u01/app/odaorahome/oracle/product/12.1.0.2/dbhome_1/log/diag/

[oracle@oda02$ cp adrci_dir.mif $ORACLE_HOME/log/diag/

Now lets check ADR home using adrci utility

[oracle@oda02 diag]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Sun Nov 20 12:23:30 2022

ADR base = "/u01/app/odaorabase0"

adrci> show homes

ADR Homes:

diag/rdbms/orcl/orcl

Now we are able to see the homes in ADR base.

Steps to move AUD$ table to a different tablespace in 19c

By default oracle stores audit data in system tablespace. Most of the customers tend to keep it in default tablespace but as a best practice its best to move the AUD$ table in a non system tablespaces.

Step 1:-To find out existing tablespace of AUD$ table:-

SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 as Size from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME Size

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

SYS AUD$ TABLE SYSTEM 10.0625

Step 2:- Create a tablespace to move AUD$ table data

SQL> create tablespace TBS_AUDIT datafile 'D:\ORACLE\ORADATA\ORCL\TS_AUDIT01.DBF' size 15G autoextend on next 1G maxsize unlimited;

Step 3:- Use DBMS_AUDIT_MGMT procedure to move the data into newly created tablespace

SQL> BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'TBS_AUDIT');

END;

PL/SQL procedure successfully completed.

Step 4: Check the AUD$ tablespace

SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME Size

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

SYS AUD$ TABLE TBS_AUDIT 10.0625

How to change compatible parameter in Standby & Primary database

Env Details:-

Primary DB Version: 19.15

Env : RAC 2 node Exadata

GI: 19.15.0.0

OS: OEL 7

Standby DB Version : 19.15

Env : Standalone DB on ODA

GI: 19.15.0.0

OS: OEL 7

Scenario: - For one of my customers, there was a requirement to change compatible parameter in both primary and standby database. The customer went through a database upgrade (12c to 19c) activity and had GRP (Guaranteed Restore Point) in the database. The GRP was kept for a week post DB upgrade to make sure there is in case of any requirement Database can fall back to its old version. Now after a week, once GRP was dropped, we had to set compatible parameter to 19.0.0 in both primary and standby database. Following are the steps that I performed to achieve this.

Steps:-

First make sure there is no log gap between primary & Standby Database

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",

2 APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

3 FROM

4 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN

5 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

6 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

7 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

8 WHERE

9 ARCH.THREAD# = APPL.THREAD#

10 ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 97989 97989 0

2 90142 90142 0

Current compatible set in database

SQL> show parameter compatible

NAME TYPE VALUE

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

compatible string 12.1.0.2.0

Now change the compatible parameter in standby database then do it on primary database.

On Standby DB:-

STANDBYDB_SQL> alter system set compatible='19.0.0' scope=spfile ;

After changing we need to bounce the database to get it in effect, so cancel the MRP apply.

STANDBYDB_SQL> alter database recover managed standby database cancel;

Now stop and start the standby database.

[oracle@standbydb]$ srvctl stop database -d $ORACLE_UNQNAME

[oracle@standbydb]$ srvctl start database -d $ORACLE_UNQNAME -o mount

Now start the MRP process to apply the redologs in standby db.

STANDBYDB_SQL> alter database recover managed standby database disconnect from session;

On Primary DB:-

PRIMARYDB_SQL> alter system set compatible='19.0.0' scope=spfile sid='*';

[oracle@primarydb]$ srvctl stop database -d $ORACLE_UNQNAME

[oracle@primarydb]$ srvctl start database -d $ORACLE_UNQNAME

Various odacli commands for ODA

[root@oda02 ~]# odaadmcli show env_hw

BM ODA X7-2 Medium

[root@hkoda02 ~]# fwupdate list disk

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

CONTROLLER

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

ID Type Manufacturer Model Product Name FW Version BIOS Version EFI Version FCODE Version Package Version NVDATA Version XML Support

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

c0 HDC Intel 0xa182 0x4866 - - - - - - N/A

DISKS

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

ID Manufacturer Model ATA Model Chassis Slot Type Media Size(GiB) FW Version ATA FW Ver XML Support

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

c0d0 INTEL SSDSGFTG480G7 INTEL_SSDSCKJB874G7 - 1 sata SSD 447 0121 N2010121 N/A

c0d1 INTEL SSDSGFTG480G7 INTEL_SSDSCKJB874G7 - - sata SSD 447 0121 N2010121 N/A

[root@oda02 ~]# odaadmcli show server

Power State : On

Open Problems : 0

Model : ODA X7-2M

Type : Rack Mount

Part Number : ODA X7-2M

Serial Number : 19355THJ026

Primary OS : Not Available

ILOM Address : 172.10.38.191

ILOM MAC Address : 00:B0:E0:F9:12:F6

Description : Oracle Database Appliance X7-2 Medium 19355THJ026

Locator Light : Off

Actual Power Consumption : 256 watts

Ambient Temperature : 23.250 degree C

Open Problems Report : System is healthy

[root@hkoda02 ~]# odacli describe-component

System Version

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

19.15.0.0.0

System node Name

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

oda02

Local System Version

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

19.15.0.0.0

Component Installed Version Available Version

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

OAK

19.15.0.0.0 up-to-date

19.15.0.0.220419 up-to-date

DB {

[OraDB12102_home1]

12.1.0.2.190716 12.1.0.2.220419

[OraDB12102_home2]

12.1.0.2.220419 up-to-date

[OraDB19000_home1]

19.15.0.0.220419 up-to-date

}

DCSCONTROLLER

19.15.0.0.0 up-to-date

DCSCLI

19.15.0.0.0 up-to-date

DCSAGENT

19.15.0.0.0 up-to-date

DCSADMIN

19.15.0.0.0 up-to-date

7.9 up-to-date

ILOM

5.0.2.24.r141466 up-to-date

BIOS

41100500 up-to-date

LOCAL CONTROLLER FIRMWARE {

[c6]

80000690 80000681

[c7]

214.2.271.9 up-to-date

}

SHARED CONTROLLER FIRMWARE {

[c1,c2]

QDV1RF32 up-to-date

[c3,c4,c5]

VDV1RL04 up-to-date

}

LOCAL DISK FIRMWARE

0121 up-to-date

SHARED DISK FIRMWARE

0121 up-to-date

HMP

2.4.8.0.601 up-to-date

[root@oda02 ~]# odacli describe-dbsystem-image

DB System Image details

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

Component Name Supported Versions Available Versions

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

DBVM 19.15.0.0.0 not-available

GI 19.15.0.0.220419 19.15.0.0.220419

19.14.0.0.220118 not-available

19.13.0.0.211019 not-available

19.12.0.0.210720 not-available

19.11.0.0.210420 not-available

21.6.0.0.220419 not-available

21.5.0.0.220118 not-available

21.4.0.0.211019 not-available

21.3.0.0.210720 not-available

DB 19.15.0.0.220419 19.15.0.0.220419

19.14.0.0.220118 not-available

19.13.0.0.211019 not-available

19.12.0.0.210720 not-available

19.11.0.0.210420 not-available

21.6.0.0.220419 not-available

21.5.0.0.220118 not-available

21.4.0.0.211019 not-available

21.3.0.0.210720 not-available

[root@oda02 ~]# odaadmcli show fs

Type Total Space Free Space Total DG Space Free DG Space Diskgroup Mount Point

acfs 5120M 4801M 24419840M 9252364M DATA /opt/oracle/dcs/commonstore

acfs 153600M 118108M 24419840M 9252364M DATA /opt/oracle/oak/pkgrepos/orapkgs/clones

acfs 10240M 8409M 24419840M 9252364M DATA /u01/app/odaorabase0

acfs 81920M 30011M 24419840M 9252364M DATA /u01/app/odaorahome

[root@oda02 ~]# odacli list-jobs

ID Description Created Status

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

7d552349-af92-4bb0-833e-f19168bef783 Repository Update July 27, 2022 5:59:25 AM HKT Success

9e4d4405-805a-4449-973a-ded6983a060a Repository Update July 27, 2022 6:58:53 AM HKT Success

5441d731-b434-4158-bc04-e16ae988b134 Repository Update July 27, 2022 7:16:46 AM HKT Success

fee90149-bd73-49a6-bb06-62a34534cb25 User creation(oda-admin) July 27, 2022 7:24:22 AM HKT Success

4675f8d7-6a4e-48d7-9434-ba0701600499 Restore node service - GI July 27, 2022 7:24:24 AM HKT Success

f6da68f6-cff2-4b67-b1f2-d835f304c35c Configure database home storage July 27, 2022 4:48:29 PM HKT Success

f1e7229c-1286-418a-8657-66b049dfbb13 Restore node service - Database July 27, 2022 4:52:40 PM HKT Created

27ccad9f-df93-4ac3-8e2d-3430c132ee2e Repository Update July 27, 2022 7:17:01 PM HKT Success

6b5f2338-cf2d-40cb-8991-7accd3733aec Restore node service - Database July 27, 2022 7:21:53 PM HKT Success

348df102-50c8-4e12-b8b0-c997a2af065f Database Home OraDB12102_home2 creation with version :12.1.0.2.220419 August 2, 2022 3:03:33 PM HKT Success

e9972ef8-86d1-4459-addc-2d46ff4352f3 Repository Update November 7, 2022 4:34:43 PM HKT Created

abf6f3b0-3470-4490-95c6-66190ae32628 Repository Update November 7, 2022 5:15:10 PM HKT Success

dd5beffe-388b-4d8c-bc3b-18370c9c8293 Database Home OraDB19000_home1 creation with version :19.15.0.0.220419 November 7, 2022 5:42:05 PM HKT Success

[root@oda02 ~]# odacli list-availablepatches

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

ODA Release Version Supported DB Versions Available DB Versions Supported Platforms

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

19.15.0.0.0 21.6.0.0.220419 Clone not available DB System

19.15.0.0.220419 19.15.0.0.220419 Bare Metal, DB System

12.1.0.2.220419 12.1.0.2.220419 Bare Metal

[root@oda02 ~]# odacli list-dbhome-storages

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

ID Node Description Disk Group Volume Size(GB) Status

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

040561b1-40c4-4fb1-8cb2-409e2898279e 0 ORACLE_HOME DATA orahome_sh 80 CONFIGURED

cb698aed-544e-407d-b5e2-a4e12a72ad71 0 ORACLE_BASE DATA odabase_n0 10 CONFIGURED

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

[root@oda02 ~]# odacli list-dbhomes

ID Name DB Version Home Location Status

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

a051cc7b-e678-44bc-be26-b03bda48c413 OraDB12102_home1 12.1.0.2.190716 /u01/app/odaorahome/oracle/product/12.1.0.2/dbhome_1 CONFIGURED

f12ae9fd-165d-4550-bb0f-ef8f89a828b9 OraDB12102_home2 12.1.0.2.220419 /u01/app/odaorahome/oracle/product/12.1.0.2/dbhome_2 CONFIGURED

e91973c3-b7b2-41df-a2b5-a80e8fca408c OraDB19000_home1 19.15.0.0.220419 /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1 CONFIGURED

[root@oda02 ~]# odaadmcli show disk

NAME PATH TYPE STATE STATE_DETAILS

pd_00 /dev/nvme0n1 NVD ONLINE Good

pd_01 /dev/nvme1n1 NVD ONLINE Good

pd_02 /dev/nvme4n1 NVD ONLINE Good

pd_03 /dev/nvme3n1 NVD ONLINE Good

pd_04 /dev/nvme2n1 NVD ONLINE Good

[root@oda02 ~]# odacli list-dgstorages

DiskGroup Redundancy Physical Total Space Physical Reserved Space Physical Free Space Logical Free Space

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

DATA NORMAL 23.28 TB 3.49 TB 8.82 TB 4.41 TB

RECO NORMAL 5.82 TB 894.0 GB 4.29 TB 2.14 TB

[root@oda02 ~]# odacli list-dbhome-storages

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

ID Node Description Disk Group Volume Size(GB) Status

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

040561b1-40c4-4fb1-8cb2-409e2898279e 0 ORACLE_HOME DATA orahome_sh 80 CONFIGURED

cb698aed-544e-407d-b5e2-a4e12a72ad71 0 ORACLE_BASE DATA odabase_n0 10 CONFIGURED

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

[root@oda02 ~]# odacli describe-cpucore

Node Cores Modified Job Status

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

0 10 July 27, 2022 7:46:41 AM HKT CONFIGURED

[root@hkoda02 ~]# odacli describe-system -b