Tuesday, May 13, 2025

oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues

oratop:

-------

Provides near real-time database monitoring.

For more information, see My Oracle Support note 1500864.1.

oratop is an interface that takes data from top and oracle databases on a host.
The utility combines the data to OS data and session data.
It allows the viewer to see how multiple databases are interacting with the host and identify high CPU consumers, memory consumers, and IO consumers.

orachk:

-------

Health Checks For The Oracle Stack Using ORAchk

Oracle ORAchk and Oracle EXAchk provide a lightweight and non-intrusive health check framework for the Oracle stack of software and hardware components.

Oracle ORAchk and Oracle EXAchk:

Automates risk identification and proactive notification before your business is impacted
Runs health checks based on critical and reoccurring problems
Presents high-level reports about your system health risks and vulnerabilities to known issues
Enables you to drill-down specific problems and understand their resolutions
Enables you to schedule recurring health checks at regular intervals
Sends email notifications and diff reports while running in daemon mode
Integrates the findings into Oracle Health Check Collections Manager and other tools of your choice
Runs in your environment with no need to send anything to Oracle

$ export ORACLE_HOME=<path>

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/suptools/oratop:$PATH

/u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba

/u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

More details are available on this Oracle Documentation

https://www.oracle.com/a/otn/docs/database/ahf-performance-tuning-using-oratop.pdf

Installation of oratop:

Download the oratop utility:

Use the metalink Doc ID 1500864.1 for download:

Rename to downloaded file to proper name

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools

mv oratop* oratop

provide execute permission:

chmod 755 oratop

Installation of oracheck:

[root@node1 ~]# cp -r /home/oracle/Desktop/orachk.zip /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk

[root@node1 ~]# unzip orachk.zip

[root@node1 ~]# cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/

[root@node1 ~]# chwon oracle:oinstall oracheck

[oracle@node1 ~]$ ./orachk -v

[oracle@node1 ~]$ ./orachk -debug

[root@node1 ~]# ./orachk

Example 1: Running the oratop

[oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba

oratop: Release 15.0.0 Production on Mon May 12 19:47:24 2025

Connecting ..

Processing ...

Oracle 19c - DEV 01:17:17 up: 24d, 2 ins, 0 sn, 0 us, 3.4G sga, 0%db

ID %CPU %DCP LOAD AAS ASC ASI ASW IDL MBPS %FR PGA UTPS RT/X DCTR DWTR

2 6.5 0.0 0.4 0.0 0 0 0 0 0.1 5 1.2G 0 3.2m 108 0

1 6.8 0 0.1 0 0 0 0 0 0.1 8 1.2G 0 0 0 0

EVENT (C) TOT WAITS TIME(s) AVG_MS PCT WAIT_CLASS

DB CPU 30418 75

control file sequential read 19574633 5909 0.3 15 System I/O

IMR slave acknowledgement msg 12738390 2899 0.2 7 Other

control file parallel write 1575061 691 0.4 2 System I/O

ASM file metadata operation 3841939 690 0.2 2 Other

ID SID SPID USR PROG S PGA SQLID/BLOCKER OPN E/T STA STE EVENT/*LA W/T

[oracle@node1 ~]$

Example 2: Running the orachk

[oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

Running orachk

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

PATH : /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk

VERSION : 18.4.0_20181129

COLLECTIONS DATA LOCATION : /u01/app/oracle/orachk/

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

This version of orachk was released on 29-Nov-2018 and its older than 180 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.

Do you want to download latest version from my oracle support? [y/n] [y] n

orachk cannot be use as its older than a year.

Exiting...

[oracle@node1 ~]$

Mallikarjun Ramadurg

THE ORACLE SME – ORACLE ACE, EXADATA, OCI, OCP, OCA, RAC EXPERT. Mallikarjun Ramadurg is the one of the active member of Oracle Community. Mallikarjun is an Oracle DBA and EXADATA administration Expert and has a deep knowledge in Oracle EBS APPS. He is having more than 11 Years of experience. He has got In-depth knowledge of database design, build, cloning and administration and he has got good exposure to environments like database HA, RAC, Oracle Cloud and Datawarehouse. He has worked on Oracle ERP, having sound knowledge on EBS application administration, apps cloning and upgradation, patching, security and performance turning. He has worked on informatica administration, implementation, design, mappings, workflows. He has good knowledge on data modeling, Erwin data modeling. He has good hands-on experience in Oracle Cloud and infrastructure level. He is currently working as Oracle SME & Staff Customer Success Engineer @ Rubrik Inc, Bangalore India

Query to get object DDL in Oracle database

Query to get object DDL in Oracle database:

1. DBA will most commonly get request to provide the metadata information of a object in a database

2. DBA will most commonly get a request a create object on development database similar to object which are in PROD database.

These requests may be for various purpose like

auditing,

migration,

backup purposes etc

Query to GET DDL structure:

set long 9999999

set lines 200 pages 2000

col METADATA format a180 word_wrapped

select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

Provide input:

Enter value for object_type: INDEX/TABLE/VIEW etc

Enter value for object_name: NAME_OF_OBJECT

Enter value for owner: OWNER_OF_OBJECT

Example:

1. Connect to database as mallik user and create an dummy table T1

[oracle@node1 ~]$ env |grep ORA

ORACLE_SID=DEVDB1

ORACLE_BASE=/u01/app/oracle

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

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus mallik/mallik

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:05 2025

Version 19.3.0.0.0

Last Successful login time: Tue May 13 2025 00:15:56 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select * from T1;

STNO STNAME DOA FEES

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

1 MALLIK 12-SEP-25 300

SQL> describe T1;

Name Null? Type

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

STNO NUMBER(3)

STNAME VARCHAR2(15)

DOA DATE

FEES NUMBER(3)

SQL>

2. Connect to sys user and get the DDL definition of that dummy table T1 belongs to mallik user

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:38 2025

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set long 9999999

SQL> set lines 200 pages 2000

SQL> col METADATA format a180 word_wrapped

SQL> select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

Enter value for object_type: TABLE

Enter value for object_name: T1

Enter value for owner: MALLIK

old 1: select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual

new 1: select dbms_metadata.get_ddl(upper('TABLE'),upper('T1'),upper('MALLIK')) "METADATA" from dual

METADATA

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

CREATE TABLE "MALLIK"."T1"

( "STNO" NUMBER(3,0),

"STNAME" VARCHAR2(15),

"DOA" DATE,

"FEES" NUMBER(3,0)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$

Mallikarjun Ramadurg

Mallik034 (Vismo Technologies)

Tuesday, May 13, 2025

oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues

oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues

oratop:

orachk:

Installation of oratop:

Installation of oracheck:

Example 1: Running the oratop

Example 2: Running the orachk

Query to get object DDL in Oracle database

Query to get object DDL in Oracle database:

1. DBA will most commonly get request to provide the metadata information of a object in a database

2. DBA will most commonly get a request a create object on development database similar to object which are in PROD database.

-bash: oraenv: No such file or directory

Social Plugin

Blog Archive