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).



25 April, 2021

OJVM : Loading Java Code and Running it from a Database Session

 Oracle's JVM in the database, available since 8i and also known as "OJVM" allows you (Developer or DBA) to load Java code into the database and execute it from a database session.  Note that any OS calls that it makes will be from the server process, not the client process. Thus, if the code tries to read or write to a filesystem or to connect to an external website, the call will be executed by the database server process, not your client (sqlplus or any other program).


Here is a simple example :



SQL> -- grant Java Code permission to only READ only /home/oracle/tmp
SQL> -- this should have been provided by the DBA
SQL>
SQL> begin
  2     dbms_java.grant_permission('HEMANT',
  3                                      'SYS:java.io.FilePermission',
  4                                      '/home/oracle/tmp',  'read');
  5
  6     dbms_java.grant_permission( 'HEMANT',
  7                                      'SYS:java.io.FilePermission',
  8                                       '/home/oracle/tmp/*',
  9                                       'read' );
 10
 11     dbms_java.grant_permission( 'HEMANT',
 12                                      'SYS:java.lang.RuntimePermission',
 13                                       'getFileSystemAttributes',
 14                                       '' );
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL>
SQL> -- here is where I load the Java code into my schema
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL>
SQL> -- load the java code into the database
SQL> create or replace and compile java source named "readOSDirectory"
  2      as
  3  /* using  java.io */
  4      import java.io.*;
  5
  6  /* create the main class */
  7      public class readOSDirectory
  8      {
  9  /* create the class to be executed from a procedure */
 10       public static void getList(String directory)
 11       {
 12  /* use File class from java.io */
 13         File directoryPath = new File( directory );
 14         File filesList[] = directoryPath.listFiles();
 15
 16  /* read till end of list */
 17         for (File file : filesList)
 18           {
 19             System.out.println("File Name: "+file.getName()
 20                                + "  File Size:  "+file.length());
 21           }
 22        }
 23       }
 24  /

Java created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> /* create a PL/SQL procedure to call the getList class */
SQL> create or replace
  2     procedure Read_Directory( p_directory in varchar2 )
  3     as language java
  4     name 'readOSDirectory.getList( java.lang.String )';
  5  /

Procedure created.

SQL>
SQL> /* enable output to screen-- serveroutput is for my sqlplus session */
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> CALL dbms_java.set_output (100000);

Call completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle/tmp');
  3  end;
  4  /
File Name: File_1.txt  File Size:  13
File Name: File_2.txt  File Size:  25
File Name: Run_Sequence_Loop.sql  File Size:  1047
File Name: List_LongOps.sql  File Size:  303
File Name: create_db_HEMANT.sql  File Size:  1530
File Name: create_db_HEMANT.lst  File Size:  47

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2  Read_Directory('/home/oracle');
  3  end;
  4  /
Exception in thread "Root Thread" java.security.AccessControlException: the Permission ("java.io.FilePermission" "/home/oracle"
"read") has not been granted to HEMANT. The PL/SQL to grant this is dbms_java.grant_permission( 'HEMANT',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
        at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
        at java.security.AccessController.checkPermission(AccessController.java:926)
        at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
        at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
        at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
        at java.io.File.list(File.java:1117)
        at java.io.File.listFiles(File.java:1207)
        at readOSDirectory.getList(readOSDirectory:11)
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read") has not been granted to HEMANT. The PL/SQL to grant this is
dbms_java.grant_permission( 'HEMANT', 'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at "HEMANT.READ_DIRECTORY", line 1
ORA-06512: at line 2


SQL>
SQL> !ls -l /home/oracle/tmp
total 24
-rw-r--r--. 1 oracle oinstall   47 Apr 25 18:46 create_db_HEMANT.lst
-rw-r--r--. 1 oracle oinstall 1530 Apr 25 18:46 create_db_HEMANT.sql
-rw-r--r--. 1 oracle oinstall   13 Apr 25 18:07 File_1.txt
-rw-r--r--. 1 oracle oinstall   25 Apr 25 18:34 File_2.txt
-rw-r--r--. 1 oracle oinstall  303 Apr 25 18:46 List_LongOps.sql
-rw-r--r--. 1 oracle oinstall 1047 Apr 25 18:46 Run_Sequence_Loop.sql

SQL>


(Note : The Java call reads from the OS's directory object "as is" returned by the directory.  The "ls -l" command sorts the output, so you may see a difference in the sorted output)

Since OJVM runs in a database server process, strong permission controls are enforced.  So, the first set of calls show permissions being granted by the DBA to the "HEMANT" schema to only read from /home/oracle/tmp

Then, the user (HEMANT) connects and loads his Java code into the database.  (Note : The first time you run this, it may take some time to load the java classes).  Then he creates a PL/SQL procedure as a "wrapper" that can execute the Java code.

Finally, he executes the java code to read the target directory /home/oracle/tmp

I also demonstrate how his attempt to read /home/oracle fails as he has not been granted explicit permission on this directory