11g-12c-18c-19c-21c-23c Compatibility Matrix

11g Compatibility:

12c Compatibility:

18c Compatibility:

19c Compatibility:

 

21c Compatibility:

23c Compatibility:

Easy/Different steps build lower environment or build your DR setup

PROD database to Lower environment (PROD -> DEV/TEST/UAT) 

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

With PROD backup (RMAN-backups):

Option 1: Restore recover from backup 

- restore 

- recover 

- open database with reset logs 

Option 2: RMAN clone / RMAN duplicate from backup 

- duplicate target database to TESTDB backup location '/u01/backup' nofilenamecheck;

Without PROD backup (without RMAN-backups):

Option 3: RMAN active database duplicate 

$rman target sys/password@PROD 

RMAN> connect auxiliary sys/password@TESTDB

RMAN> duplicate target database to TESTDB from active database;

PROD database to DR environment (PROD -> DR) 

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

With PROD backup (RMAN-backup):

Option 1: Restore recover from backup 

- restore 

- recover 

- start MRP 

Option 2: RMAN clone / RMAN duplicate from backup 

- duplicate target database for standby database backup location '/u01/backup' filenamecheck/nofilenamecheck;

- start MRP 

Without PROD backup (without RMAN-backup):

Option 3: RMAN active database duplicate 

$rman target sys/password@PROD 

RMAN> connect auxiliary sys/password@DR

RMAN> duplicate target database for standby database from active database;

- start MRP 

https://mallik034.blogspot.com/2021/05/19c-dataguard-build-document.html

Option 4: Service based standby build (from 12c onwards) (Without PROD backup)

rman target / (on Standby Side) 

RMAN> restore standby controlfile from PROD service;

RMAN> restore standby database from PROD service;

RMAN> recover standby database from PROD service;

-bash: oraenv: No such file or directory

What Are Root.sh And OrainstRoot.sh Scripts In A Standalone RDBMS Installation? (Doc ID 1493121.1)

Issue:

Unable to run oraenv to set oracle environmental variable 

Error Message:

[oracle@oraclelab1 ~]$ . oraenv

-bash: oraenv: No such file or directory

Cause:

/usr/local/bin/oraenv environmental executable file might have got corrupted or root.sh script has not ran as part of Oracle Home installation

Fix:

recreate or rebuild /usr/local/bin/oraenv environmental executable file by running root.sh script as root user

Error Logs and commands output:

1. Check the database instance status and oracle owner

[root@oraclelab1 ~]# ps -ef|grep smon

root     18125 17601  0 23:48 pts/1    00:00:00 grep --color=auto smon

oracle   28764     1  0 May01 ?        00:00:31 ora_smon_MALLIK

[root@oraclelab1 ~]#

[root@oraclelab1 ~]# su - oracle

Last login: Mon Jun 16 23:33:49 IST 2025 on pts/1

[oracle@oraclelab1 ~]$

2. When we are trying the set the oracle environmental variable by running .oraenv it is failing with error message oraenv: No such file or directory

/usr/local/bin/oraenv environmental executable file might have got corrupted or root.sh script has not ran as part of Oracle Home installation

[oracle@oraclelab1 ~]$ . oraenv

-bash: oraenv: No such file or directory

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ which oraenv

/usr/bin/which: no oraenv in (/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin)

[oracle@oraclelab1 ~]$ env |grep ORA

[oracle@oraclelab1 ~]$

3. recreate or rebuild /usr/local/bin/oraenv environmental executable file by running root.sh script as root user

[root@oraclelab1 ~]# cat /etc/oratab

#

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third field indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

MALLIK:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

[root@oraclelab1 ~]#

[root@oraclelab1 ~]# /u01/app/oracle/product/19.0.0.0/dbhome_1/root.sh

Performing root user operation.

The following environment variables are set as:

    ORACLE_OWNER= oracle

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

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

   Copying oraenv to /usr/local/bin ...

The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Oracle Trace File Analyzer (TFA - Standalone Mode) is available at :

    /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tfactl

Note :

1. tfactl will use TFA Service if that service is running and user has been granted access

2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed

[root@oraclelab1 ~]#

4. Now try to set the oracle environmental variable by running .oraenv which worked fine without any issue. 

[root@oraclelab1 ~]# su - oracle

Last login: Mon Jun 16 23:48:35 IST 2025 on pts/1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? MALLIK

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=MALLIK

ORACLE_BASE=/u01/app/oracle

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

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ which oraenv

/usr/local/bin/oraenv

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 16 23:49:48 2025

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> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS

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

MALLIK           OPEN

SQL> exit

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

Version 19.3.0.0.0

[oracle@oraclelab1 ~]$

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

Copyright (c) 2011, Oracle.  All rights reserved.

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 ~]$

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

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

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

         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

 

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> 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 ~]$