30 May, 2021

Performance Hub for On-Premises Database

Previously, I have demonstrated the Performance Hub on an Oracle Autonomous Transaction Processing database on the Oracle Cloud.

 However, you can use the DBMS_PERF package for a similar report on your On-Premises database.

The code to generate such a report is (you can connect explicitly to the target PDB to generate the Performance Hub report) :

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool DBMS_PERF_REPORT.html
select dbms_perf.report_perfhub(is_realtime=>1,type=>'active') from dual;
-- is_realtime 1 and active shows the report for the last 1hour
-- for more options see the documentation on DBMS_PERF
spool off
-- then edit the html file to remove the first line and the spool off command if necessary


I have generated a similar report for the database in my On-Premises VM.


See the 19c documentation on DBMS_PERF for more details.


Note :   The window is only 5minutes because the default selected_start_time parameter is 5minutes before the current time (i.e. the time when the report is generated)


27 May, 2021

A DDL Trigger ... and writing to the alert log file

 Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.



SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb1;

Session altered.

SQL> @Drop_Table_not_permitted.sql
SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED
  2  after ddl
  3  on database
  4  begin
  5    if (ora_sysevent='DROP'  and  ora_dict_obj_type = 'TABLE')
  6    then
  7   --- code for INSERT into an audit log table
  8   --- INSERT INTO ....
  9   ---
 10   --- code below is to write to the alert log file
 11   --- dbms_log is undocumented but available since 12c or 11.2.0.4
 12   ---   see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/
 13   --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt
 14   ---   see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html
 15       dbms_log.ksdwrt(2,' Warning :  ' || sys_context('USERENV','SESSION_USER')
 16                      || ' tried to drop  ' || ora_dict_obj_name
 17                      || '  in SID ' || sys_context('USERENV','SID')
 18                      || ', AUDSID ' || sys_context('USERENV','SESSIONID')
 19                      || '  and service name  ' || sys_context('USERENV','SERVICE_NAME'));
 20      raise_application_error(-20001,'You are not permitted to Drop Tables');
 21    end if;
 22  end;
 23  /

Trigger created.

SQL>


When I connect to the PDB and attempt to execute a DROP TABLE ...

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc dummy_tab
 Name                                                           Null?    Type
 -------------------------------------------------------------- -------- ------------------------
 ID_COL                                                                  NUMBER

SQL> drop table dummy_tab;
drop table dummy_tab
           *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not permitted to Drop Tables
ORA-06512: at line 17


SQL>


The alert log shows this entry :

2021-05-27T21:38:11.859746+08:00
ORCLPDB1(3): Warning :  HEMANT tried to drop  DUMMY_TAB  in SID 272, AUDSID 1061583  and service name  orclpdb1


You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.

Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file.  "3" would reference both files.

23 May, 2021

All Email Subscriptions have been removed

 I have removed email subscriptions to this blog.

Please try the "Follow This Blog On follow.it"  link on the right



20 May, 2021

Email Subscriptions to be disabled (per notice from FeedBurner)

 I have received this notice from FeedBurner :

Starting in July, we are transitioning FeedBurner onto a more stable, modern infrastructure. This will keep the product up and running for all users, but it also means that we will be turning down most non-core feed management features, including email subscriptions, at that time.

 Therefore, email subscriptions to this blog may stop working sometime in July.

My apologies for the inconvenience.

This blog still does get aggregated by different websites, the one I prefer is OracleFAQ.  So, you may visit that website for this and other Oracle Blog posts.

In the meantime, I might explore if I can setup another RSS Feed for this blog.



19 May, 2021

Does a Conventional INSERT block Parallel or Direct Path INSERTs ?


Note : This test is in a 19c database

 In my previous post, I have demonstrated how a Parallel or Direct Path INSERT blocks a Conventional INSERT.

Does a Conventional INSERT block a Parallel or Direct Path INSERT ?  OR is there a different effect on the session attempting the Parallel or Direct Path ?

Taking the same target table,  this is the first session, with a Conventional INSERT :



22:30:18 SQL> insert into target_objects_list
22:30:20   2  select * from dba_objects
22:30:32   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 
22:30:43   4  /

1 row created.

22:30:44 SQL>


With the Conventional INSERT not having issued a COMMIT yet, the second session attempts a Parallel INSERT

22:32:14 SQL> alter session enable parallel dml;

Session altered.

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30   2  select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37   3  /



With the second session waiting, let's check what the wait is

WAITING_SESSION
------------------------------------------------------------------------------------------------------------------------------------
LOCK_TYPE         MODE_REQUE MODE_HELD  LOCK_ID1   LOCK_ID2
----------------- ---------- ---------- ---------- ----------
325
None

 w*82
DML               Exclusive  Row-X (SX) 78449      0


2 rows selected.

SQL> select sql_text from v$sql where sql_id =
  2  (select sql_id from v$session
  3  where sid=82)
  4  /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert /*+ PARALLEL (t 2) */ into target_objects_list t select /*+ PARALLEL (s 2) */ * from objects_list s

1 row selected.

SQL>

Note how the MODE_REQUESTED and MODE_HELD are different from the previous blog post.

What happens when the Conventional INSERT issues a COMMIT ?

22:30:18 SQL> insert into target_objects_list
22:30:20   2  select * from dba_objects
22:30:32   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 
22:30:43   4  /

1 row created.

22:30:44 SQL>
22:35:54 SQL>
22:35:55 SQL> commit;

Commit complete.

22:35:56 SQL>


And here is the second session now :

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30   2  select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37   3  /

289641 rows created.

22:36:00 SQL>
22:36:50 SQL> select count(*) from target_objects_list;
select count(*) from target_objects_list
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


22:36:58 SQL> commit;

Commit complete.

22:37:13 SQL> select count(*) from target_objects_list;

  COUNT(*)
----------
    289642

22:37:20 SQL>


The Parallel INSERT succeeded after the COMMIT by the Conventional INSERT.

If you compare the MODE_REQUESTED and MODE_HELD (from dba_locks) in the two blog posts, the Parallel, Direct Path INSERT requests an Exclusive Lock.

FYI, LOCK_ID1=78449 is :
SQL> l
  1  select object_id,  object_name
  2  from dba_objects
  3* where object_id = 78449
SQL> /

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     78449 TARGET_OBJECTS_LIST

1 row selected.

SQL>


Here's an experiment you can try :
What if the target is not a table but a Partition of a Table ?
What if the target is an explicitly named Partition ?


18 May, 2021

Parallel or Direct Path INSERT blocks Conventional INSERT

 Note : This test is in a 19c database

A session executing Parallel (or Direct Path, with APPEND) INSERT will block conventional insert from another session.

(a bit of explanation : A Parallel INSERT is always a Direct Path INSERT, without requiring the APPEND Hint.  But a non-Parallel INSERT with an APPEND Hint is also a Direct Path INSERT)



23:02:31 SQL> l
  1   insert /*+ PARALLEL (t 2) */ into target_objects_list t
  2* select /*+ PARALLEL (s 2) */ * from objects_list s
23:02:31 SQL> /

289641 rows created.

23:02:39 SQL>


Session 1 ran a Parallel INSERT from 23:02:31 to 23:02:39.  What happens to another session attempting Conventional Insert from a different session -- even if the second INSERT begins after the Parallel INSERT has completed (but not COMMITed) ?

23:02:36 SQL> insert into target_objects_list
23:02:50   2  select * from dba_objects
23:02:55   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58   4  /


A Blocker-Waiter Tree shows :

WAITING_SESSION
------------------------------------------------------------------------------------------------------------------------------------
LOCK_TYPE         MODE_REQUE MODE_HELD  LOCK_ID1   LOCK_ID2
----------------- ---------- ---------- ---------- ----------
17
None

 w*60
DML               Row-X (SX) Exclusive  78449      0


2 rows selected.

SQL> select sql_text from v$sql where sql_id =
  2  (select sql_id from v$session
  3   where sid=60)
  4  /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into target_objects_list select * from dba_objects where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML

1 row selected.

SQL>


As soon as I issue a COMMIT in the first session :

23:11:10 SQL>
23:11:11 SQL> select count(*) from target_objects_list
23:11:20   2  /
select count(*) from target_objects_list
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


23:11:23 SQL> commit;

Commit complete.

23:11:28 SQL>


The waiting session with a single-row Conventional INSERT succeeds :

23:02:36 SQL> insert into target_objects_list
23:02:50   2  select * from dba_objects
23:02:55   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58   4  /

1 row created.

23:11:27 SQL>


So, be careful when attempting Parallel or Direct Path (APPEND) INSERTs into a table where other sessions may be attempting Conventional INSERTs.



09 May, 2021

Tracking the Standby Lag from the Primary

 Here is a quick way of tracking the Standby Lag from the Primary.

This relies on the information in V$ARCHIVE_DEST on the Primary.

Note that this query will not work if the lag is so great that the SCN_TO_TIMESTAMP mapping fails (because the underlying table holds only a limited number of records) OR if the Standby instance is shutdown and the Primary cannot communicate with it.


Note : The lag based on "SCN_TO_TIMESTAMP" is always an approximation.  

SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 00:41:09.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:22.000000000

SQL>
SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:58.000000000

SQL>
SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:16.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:37.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000

SQL>


Here, the lag was 4 days and it took some time for the Standby to catchup with the Primary.
(this is my Lab environment, not a real production environment at my work place, so don't ask how I managed to create a lag of 4 days or how long it took for the Standby to catch-up with the Pirmary)

Note : If the Standby database is down and/or the lag is very high, you will get error :
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

for the "applied_scn" from v$archive_dest.  (If the Standby is down, the value for "applied_scn" in v$archive_dest on the Primary is "0").


If you have access to the Standby you can run this query :

select name, value from v$dataguard_stats where name like '%lag'


The demo above is only a quick way by querying the Primary without accessing the Standby

05 May, 2021

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

 You are attempting to restore a database to another server.  

So, you have verified that you have controlfile and datafile backups on the source server  :



RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    11.52M     DISK        00:00:01     20-FEB-21
        BP Key: 51   Status: AVAILABLE  Compressed: NO  Tag: TAG20210220T114245
        Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
  Control File Included: Ckp SCN: 1093419      Ckp time: 20-FEB-21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55      Full    11.52M     DISK        00:00:02     04-MAY-21
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20210504T232054
        Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
  Control File Included: Ckp SCN: 1126526      Ckp time: 04-MAY-21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56      Full    11.48M     DISK        00:00:01     04-MAY-21
        BP Key: 56   Status: AVAILABLE  Compressed: NO  Tag: TAG20210504T232851
        Piece Name: /home/oracle/controlfile.bak
  Control File Included: Ckp SCN: 1126757      Ckp time: 04-MAY-21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57      Full    11.52M     DISK        00:00:02     04-MAY-21
        BP Key: 57   Status: AVAILABLE  Compressed: NO  Tag: TAG20210504T232853
        Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
  Control File Included: Ckp SCN: 1126766      Ckp time: 04-MAY-21

RMAN>


You have copied the backups to the target, new, server and attempt to restore :

oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 5 22:27:26 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1207958960 bytes

Fixed Size                     8895920 bytes
Variable Size                318767104 bytes
Database Buffers             872415232 bytes
Redo Buffers                   7880704 bytes

RMAN> restore controlfile from '/home/oracle/controlfile.bak';

Starting restore at 05-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/05/2021 22:27:47
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>ls /home/oracle/controlfile.bak
/home/oracle/controlfile.bak
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


So, why do you get the RMAN-06172 error ?  All the controlfile backups, including the manual backup to /home/oracle/controlfile.bak and the three autobackups, one from February 2021 and two from 04-May-2021 are available.

oracle19c>oerr rman 6172
6172, 1, "no AUTOBACKUP found or specified handle is not a valid copy or piece"
// *Cause:  A restore could not proceed because no AUTOBACKUP was found or
//          specified handle is not a valid copy or backup piece.
//          In case of restore from AUTOBACKUP, it may be the case that a
//          backup exists, but it does not satisfy the criteria specified in
//          the user's restore operands.
//          In case of restore from handle, it may be the handle is not a
//          backup piece or control file copy. In may be that it does not
//          exist.
// *Action: Modify AUTOBACKUP search criteria or verify the handle.
oracle19c>
oracle19c>ls -l /home/oracle/controlfile.bak
-rw-r-----. 1 root root 12058624 May  4 23:28 /home/oracle/controlfile.bak
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
-rw-r-----. 1 root root 12091392 Feb 20 11:42 /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
-rw-r-----. 1 root root 12091392 May  4 23:20 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
-rw-r-----. 1 root root 12091392 May  4 23:28 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


You get the "error" message that there are no AUTOBACKUPs because the "oracle19c" account is unable to actually *read* those pieces.  It can list them using "ls" because it has permission to read the OS folders containing them, but it does no have permission to read the files owned by root without having granted read permission.

So, before you start wondering about your AUTOBACKUP configuration or search criteria specification like "RESTORE CONTROLFILE FROM AUTOBACKUP MAXDAYS 30",  check if the backup pieces are readable.


02 May, 2021

My Posts on RMAN

 My series of posts on RMAN :

1. 1 : Backup Job Details

2. 2 : ArchiveLog Deletion Policy

3. 3 : The DB_UNIQUE_NAME in Backups to the FRA

4. 4 : Recovering from an Incomplete Restore

5. 4b : Recovering from an Incomplete Restore with OMF Files

6. 5 : Useful KEYWORDs and SubClauses

7. 5b : (More) Useful KEYWORDs and SubClauses

8. 5c : (Some More) Useful KEYWORDs and SubClauses

9. 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

10. 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?

11. 8 : Using a Recovery Catalog Schema

12. 9 : Querying the RMAN Views / Catalog

13. 10 : VALIDATE


An older series of "tips" :

14. Tips -- 1

15. Tips -- 2

16. Tips -- 3

17. Tips -- 4


Other RMAN posts not in the  above series : (not in any particular order)

18. RMAN's CATALOG command

19. RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

20. RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups -- 2nd Post

21. Primary and Standby in the same RMAN Catalog

22. Understanding Obsolescence of RMAN Backups

23. "SET TIME ON" in RMAN

24. RMAN Backup of a Standby Database

25. RMAN Image Copy File Names

26. Verifying an RMAN Backup

27. Verifying an RMAN Backup - Part 2

28. Misinterpreting RESTORE DATABASE VALIDATE

29. RMAN Backup and Recovery for Loss of ALL Files

30. CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

31.RMAN Consistent ("COLD" ?) Backup and Restore

32. Archive Log Deletion Policy with a Standby Database

33. Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

34. Read Only Tablespaces and BACKUP OPTIMIZATION

35. Full Recovery of Standby Database over the network

36. Restoring a lost Datafile on a Standby Database and knowing about V$RECOVER_FILE

37. Multiple Channels in RMAN are not always balanced

38. RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

39. Restoring a Standby Database to the Primary


01 May, 2021

Pro*C in Oracle

 Oracle also ships a Pro*C Precompiler that can convert a Pro*C source file to a C source file which can then be compiled  using a C Compiler (e.g  using "gcc").  Of course, you need the Pro*C Developer Licence to use this product.

Here is a quick demo with the command line display and then the actual code below.



oracle19c>ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 2255 May  1 18:07 instancedbinfo.pc
-rwxr--r--. 1 oracle oinstall  786 May  1 18:14 Compile_my_ProC.SH
-rwxr--r--. 1 oracle oinstall  356 May  1 18:15 Run_my_ProC.SH
oracle19c>./Compile_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set C_INCLUDE_PATH
*****PreCompile Pro*C program file

Pro*C/C++: Release 19.0.0.0.0 - Production on Sat May 1 18:15:17 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /opt/oracle/product/19c/dbhome_1/precomp/admin/pcscfg.cfg

*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so
*****Compiled files:
-rw-r--r--. 1 oracle oinstall  2255 May  1 18:07 instancedbinfo.pc
-rw-r--r--. 1 oracle oinstall     0 May  1 18:15 instancedbinfo.lis
-rw-r--r--. 1 oracle oinstall 11875 May  1 18:15 instancedbinfo.c
-rwxr-xr-x. 1 oracle oinstall 14424 May  1 18:15 instancedbinfo
oracle19c>
oracle19c>
oracle19c>
oracle19c>./Run_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set Connection String
*****Execute the program
Connected to ORACLE
At ORCLCDB which is on oracle-19c-vagrant  running 19.0.0.0.0 and is OPEN, started at 01-MAY-21 17:54:52
This is ORCLPDB1 database running in READ WRITE mode since 01-MAY-21 05.55.21.573 PM +08:00

oracle19c>


The file "instancedbinfo.pc" is my Pro*C source code.
I Precompile it using the "proc" precompiler into "instancedbinfo.c".  Any compilation errors would have been logged into "instancedbinfo.lis"
Then, the same script "Compile_my_ProC.SH" compiles the C program source code into an executable "instancedbinfo" using "gcc"

Finally, I use "Run_my_ProC.SH" to execute the file "instancedbinfo"  (which is now an executable) and the execution displays information about the Pluggable database it is connected to.


Here is the code for the two shell scripts :


oracle19c>cat Compile_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH


echo "*****Set C_INCLUDE_PATH"
C_INCLUDE_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib:/opt/oracle/product/19c/dbhome_1/precomp/public
export C_INCLUDE_PATH

echo "*****PreCompile Pro*C program file"
proc instancedbinfo.pc

echo "*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so"
gcc instancedbinfo.c -o instancedbinfo  -L /opt/oracle/product/19c/dbhome_1/lib -l clntsh

echo "*****Compiled files:"
ls -ltr instancedbinfo*
oracle19c>


oracle19c>cat Run_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH

echo "*****Set Connection String"
CNCTSTRING=hemant/hemant@orclpdb1
export CNCTSTRING

echo "*****Execute the program"
./instancedbinfo
oracle19c>


The Compilation script specifies the LD_LIBRARY_PATH and the Paths to the Include (.h Header) files.  
It then executes "proc"  (which is in $ORACLE_HOME/bin) to precompile the "instancedbinfo.pc" source file.
Finally, it calls "gcc" to compile the c-language source code file (generated by the Precomipler), also specifiying the client shared library file libclntsh.so  in $ORACLE_HOME/lib  (only "-l clntsh" is sufficient to identify the file name).  The compiled executable is called "instancedbinfo" with Execute Permission.

The Run script specifies the Connect-String that the executable will be reading from the environment and executes it.


Here is the code of the source Pro*C file :


oracle19c>cat instancedbinfo.pc

/* standard C includes */
#include << stdio.h >>
#include << stdlib.h >>
#include << string.h >>



/* Oracle Pro*C includes from $ORACLE_HOME/precomp/public */
#include << sqlca.h >>
#include << sqlda.h >>
#include << sqlcpr.h >>




/* my variables */
varchar     MYinstanceName[16];
varchar     MYhostName[64];
varchar     MYversion[17];
varchar     MYstatus[12];
varchar     MYinstanceStartupTime[18];
varchar     MYdbName[128];
varchar     MYdbOpenMode[10];
varchar     MYdbOpenTime[32];



/* function for error handling */
void sql_error(msg)
    char msg[200];
{
    char err_msg[128];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(EXIT_FAILURE);
}


/* MAIIN program */
int main(argc,argv)
   int   argc;
   char *argv[];
{

/* read Connection String from environment -- or, it could have been hardcoded here */
   const char *conn = getenv("CNCTSTRING");
      if (!conn) {
        printf("! require CNCTSTRING env variable\n");
        return (1);
      }

    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

/* connect to targe database */
    EXEC SQL CONNECT :conn ;
    printf("Connected to ORACLE \n");


/* execute query and populate variables */
/* NOTE : This expects to connect to a PDB ! */
/* If the target is a Non-PDB, change references from v$pdbs to V$database */
    EXEC SQL SELECT  instance_name,host_name, version,
                     to_char(startup_time,'DD-MON-RR HH24:MI:SS'), status,
                     name, open_mode, to_char(open_time)
             INTO    :MYinstanceName, :MYhostName, :MYversion,
                     :MYinstanceStartupTime, :MYstatus,
                     :MYdbName, :MYdbOpenMode, :MYdbOpenTime
             FROM    v$instance, v$pdbs ;


/* display query results */
printf("At %s which is on %s  running %s and is %s, started at %s \n",
        MYinstanceName.arr, MYhostName.arr, MYversion.arr, MYstatus.arr, MYinstanceStartupTime.arr);
printf("This is %s database running in %s mode since %s \n",
        MYdbName.arr, MYdbOpenMode.arr, MYdbOpenTime.arr);
printf("\n");

/* end of MAIN */
}
oracle19c>


(Note :  I have put doube angle brackets for the #includes so as to preserve them in HTML)
Pro*C allows embedding of SQL calls into a C program be including the Proc include files and then running the source code through a Precompiler.
My Pro*C source code file is 2,255 bytes and the C source code is 11,875 bytes.

Note that the variables defined as varchar in my Pro*C source file are actually become C structures :

/* my variables */
/* varchar     MYinstanceName[16]; */
struct { unsigned short len; unsigned char arr[16]; } MYinstanceName;

/* varchar     MYhostName[64]; */
struct { unsigned short len; unsigned char arr[64]; } MYhostName;

/* varchar     MYversion[17]; */
struct { unsigned short len; unsigned char arr[17]; } MYversion;

/* varchar     MYstatus[12]; */
struct { unsigned short len; unsigned char arr[12]; } MYstatus;

/* varchar     MYinstanceStartupTime[18]; */
struct { unsigned short len; unsigned char arr[18]; } MYinstanceStartupTime;

/* varchar     MYdbName[128]; */
struct { unsigned short len; unsigned char arr[128]; } MYdbName;

/* varchar     MYdbOpenMode[10]; */
struct { unsigned short len; unsigned char arr[10]; } MYdbOpenMode;

/* varchar     MYdbOpenTime[32]; */
struct { unsigned short len; unsigned char arr[32]; } MYdbOpenTime;


Similarly, my EXEC SQL query also gets re-written :
{
    struct sqlexd sqlstm;
    sqlstm.sqlvsn = 13;
    sqlstm.arrsiz = 8;
    sqlstm.sqladtp = &sqladt;
    sqlstm.sqltdsp = &sqltds;
    sqlstm.stmt = "select instance_name ,host_name ,version ,to_char(startup\
_time,'DD-MON-RR HH24:MI:SS') ,status ,name ,open_mode ,to_char(open_time) int\
o :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7  from v$instance ,v$pdbs ";
    sqlstm.iters = (unsigned int  )1;
    sqlstm.offset = (unsigned int  )51;
    sqlstm.selerr = (unsigned short)1;
    sqlstm.sqlpfmem = (unsigned int  )0;
    sqlstm.cud = sqlcud0;
    sqlstm.sqlest = (unsigned char  *)&sqlca;
    sqlstm.sqlety = (unsigned short)4352;
    sqlstm.occurs = (unsigned int  )0;
    sqlstm.sqhstv[0] = (unsigned char  *)&MYinstanceName;
    sqlstm.sqhstl[0] = (unsigned long )18;
    sqlstm.sqhsts[0] = (         int  )0;
    sqlstm.sqindv[0] = (         short *)0;
    sqlstm.sqinds[0] = (         int  )0;
    sqlstm.sqharm[0] = (unsigned long )0;
    sqlstm.sqadto[0] = (unsigned short )0;
    sqlstm.sqtdso[0] = (unsigned short )0;
    sqlstm.sqhstv[1] = (unsigned char  *)&MYhostName;
and so on .....


Pro*C is a very good way of combining C programming with SQL and creating an executable binary instead of an interpreted file (like a Java or Python program outside the database).