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


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

21 April, 2021

Primary and Standby in the same RMAN Catalog

 A quick demo of an RMAN Catalog shared by both Primary and Standby databases

For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby

At the Primary :



oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 2021
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 name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCLCDB
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit


Recovery Manager complete.
oracle19c>


Now, at the Standby



oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 2021
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 name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   MOUNTED

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STDBYDB
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 04/21/2021 22:26:19
RMAN-01005: Mounted control file type must be CURRENT to register the database

RMAN> quit


Recovery Manager complete.
oracle19c>


(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)

It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).

So, how do I run RMAN Backups and have them saved in the Catalog ?

On the Primary, I run a FULL Backup.


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=280 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


Then, I run a separate backup at the Standby :


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=249 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


So, I now have two different database backups of the same NAME and DBID registered in one catalog.

How do I distinguish them ?  From the RMAN command lime query  I can verify in this manner (querying only for datafile 1 for the purpose of this demo)

At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245    Full    231.82M    DISK        00:00:38     21-APR-21
        BP Key: 1255   Status: AVAILABLE  Compressed: YES  Tag: TAG20210421T223134
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
  List of Datafiles in backup set 1245
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 11072202   21-APR-21              NO    /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


And at the Standby :

oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300    Full    241.08M    DISK        00:00:40     21-APR-21
        BP Key: 1308   Status: AVAILABLE  Compressed: YES  Tag: TAG20210421T223703
        Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
  List of Datafiles in backup set 1300
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 11061162   21-APR-21 11072277     NO    /opt/oracle/oradata/STDBYDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).

But the listing does show that 

the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf  is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp 

while  the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.

You might also notice that the Checkpoint SCNs are different.



Can I run an SQL query in the RMAN Catalog schema itself ?

oracle19c>sqlplus rmanschema/rmanschema@rmancat

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select db_key, dbid, name from rc_database;

    DB_KEY       DBID NAME
---------- ---------- --------
         1 2778483057 ORCLCDB

SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;

  SITE_KEY     DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
         3          1 PRIMARY ORCLCDB
       804          1 STANDBY STDBYDB

SQL>
SQL> l
  1  select site.site_key, site.database_role, site.db_unique_name,
  2  bs.bs_key, bs.backup_type
  3  from rc_site site, rc_backup_set bs
  4  where site.site_key=bs.site_key
  5  and site.db_key=bs.db_key
  6  and bs.start_time > sysdate-1
  7* and bs.bs_key in (1245,1300)
SQL> /

  SITE_KEY DATABAS DB_UNIQUE_NAME       BS_KEY B
---------- ------- ---------------- ---------- -
         3 PRIMARY ORCLCDB                1245 D
       804 STANDBY STDBYDB                1300 D

SQL>
SQL> l
  1  select site.site_key, site.database_role, site.db_unique_name,
  2  df.bs_key, df.file#, df.checkpoint_change#
  3  from rc_site site, rc_backup_set bs, rc_backup_datafile df
  4  where site.site_key=bs.site_key
  5  and site.db_key=bs.db_key
  6  and site.db_key=df.db_key
  7  and bs.bs_key=df.bs_key
  8  and bs.start_time > sysdate-1
  9  and bs.bs_key in (1245,1300)
 10* and df.file#=1
SQL> /

  SITE_KEY DATABAS DB_UNIQUE_NAME       BS_KEY      FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
         3 PRIMARY ORCLCDB                1245          1           11072202
       804 STANDBY STDBYDB                1300          1           11061162


Here we can set that RC_DATABASE has only 1 record for the actual database.  But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.

BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN.  Today's backup of Datafile#1 at the Primary was in Backup Set  1245 while that of the same datafile at the Standby was in Backup Set 1300.  (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.

There are many more RC_%  Tables and Views in the RMAN Schema that you can query.


11 April, 2021

Python with Oracle using sqlalchemy and cx_oracle

 Here is a simple demo of using Python and the sqlalchemy and cx_oracle libraries


This is the code :

#import required libraries
import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError

#setup connection
try:
    oracle_pdb = sqlalchemy.create_engine("oracle+cx_oracle://hemant:hemant@localhost/?service_name=orclpdb1", arraysize=100)
except SQLAlchemyError as e:
   print(e)
   
#setup query and pandas dataframe for results
try:
    employees_query = """SELECT * FROM hr.employees order by employee_id""";   
    df_employees = pd.read_sql(employees_query, oracle_pdb)
except SQLAlchemyError as e:
   print(e)

#Info on the dataframe
print(df_employees.info())
#the first five rows
print(df_employees.head())

#create a new dataframe with a subset of columns
df_emp_selected_cols=df_employees
df_emp_selected_cols.drop(['email','phone_number','salary','commission_pct','manager_id','department_id'],axis=1, inplace=True)
print(df_emp_selected_cols.head())


And here is the output from my database :

Info on the dataframe

RangeIndex: 108 entries, 0 to 107
Data columns (total 11 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   employee_id     108 non-null    int64         
 1   first_name      108 non-null    object        
 2   last_name       108 non-null    object        
 3   email           108 non-null    object        
 4   phone_number    107 non-null    object        
 5   hire_date       108 non-null    datetime64[ns]
 6   job_id          108 non-null    object        
 7   salary          107 non-null    float64       
 8   commission_pct  35 non-null     float64       
 9   manager_id      106 non-null    float64       
 10  department_id   107 non-null    float64       
dtypes: datetime64[ns](1), float64(4), int64(1), object(5)
memory usage: 9.4+ KB
None
The first 5 rows
   employee_id first_name last_name  ... commission_pct manager_id department_id
0          100     Steven      King  ...            NaN        NaN          90.0
1          101      Neena   Kochhar  ...            NaN      100.0          90.0
2          102        Lex   De Haan  ...            NaN      100.0          90.0
3          103  Alexander    Hunold  ...            NaN      102.0          60.0
4          104      Bruce     Ernst  ...            NaN      103.0          60.0

[5 rows x 11 columns]
With selected columns only
   employee_id first_name last_name  hire_date   job_id
0          100     Steven      King 2003-06-17  AD_PRES
1          101      Neena   Kochhar 2005-09-21    AD_VP
2          102        Lex   De Haan 2001-01-13    AD_VP
3          103  Alexander    Hunold 2006-01-03  IT_PROG
4          104      Bruce     Ernst 2007-05-21  IT_PROG


Once you are familiar with this method, you can use numpy, matplotlib and a host of other python libraries with the dataset.

This article by Yuli Vasiliev is a good starter.


03 April, 2021

Using the SESSION_LONGOPS view with DBMS_APPLICATION_INFO

 I have, in the past, demonstrated DBMS_APPLICATION_INFO   here    and   here.

Also, I have demonstrated how V$SESSION_LONGOPS can be used (and misunderstood !!) 


Here is a demo of how you can use both features to enable monitoring of a (batch ?) job run.

The job is modified to include calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.

The DBA can then monitor the job from V$SESSION_LONGOPS.


The job is to update a PRODUCTS table with new PRODUCT_IDs, after some validation.  Since, this can take some time to run, the DBA wants to monitor it via V$SESSION_LONGOPS.


Here is the PL/SQL code for the batch (job) run :(look up the documentaion on DBMS_APPLICATION_INFO in the PL/SQL Developers Guide for our version -- I know that is available since at least 10.2, although this demonstation below is in 19c)



DECLARE
rindex    BINARY_INTEGER;
slno      BINARY_INTEGER;
target_count  number;
updated_count     number;

product_row  products%rowtype;
check_flag varchar2(8);

BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
updated_count := 0;
select num_rows into target_count   --- this is an approximation based on last updated statistics on the table
from user_tables
where table_name = 'PRODUCTS';

-- begin the updates here
for product_row in (select product_id, product_name from products)
loop

 -- validate_for_update is a function that checks if this product should get a new PRODUCT_ID
 select validate_for_update(product_row.product_id, product_row.product_name) into check_flag from dual ;

 if check_flag='OK' then
   update products set product_id = product_id+10000 where product_id=product_row.product_id;
   insert into update_run_log values (product_row.product_id,systimestamp);
   updated_count := updated_count+1;
   dbms_application_info.set_session_longops(rindex,
    slno,
    op_name=>'New_Product_IDs',
    target=>0,   -- default, not used by me
    context=>0,   -- default, not used by me
    sofar=>updated_count,
    totalwork=>target_count,
    target_desc=>'Table : PRODUCTS',
    units=>'rows');
 else
   null;
 end if;

end loop;
END;
/


commit;



And here is the DBA monitoring the job as it is running :



23:22:15 SQL> l
  1  select sid, opname, target_desc, sofar, totalwork, units, start_time, elapsed_seconds, time_remaining
  2  from v$session_longops
  3* where username = 'HEMANT'
23:22:15 SQL> /

no rows selected

23:22:16 SQL>
23:22:21 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS          3        500 rows   03-APR-21 23:22:18               2            331

23:22:22 SQL>
23:22:30 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         12        500 rows   03-APR-21 23:22:18              12            488

23:22:31 SQL>
23:22:44 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         26        500 rows   03-APR-21 23:22:18              26            474

23:22:45 SQL>
23:23:26 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS         67        500 rows   03-APR-21 23:22:18              69            446

23:23:27 SQL>
23:26:28 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        245        500 rows   03-APR-21 23:22:18             251            261

23:26:30 SQL>
23:28:31 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        364        500 rows   03-APR-21 23:22:18             373            139

23:28:32 SQL>
23:29:24 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        416        500 rows   03-APR-21 23:22:18             426             86

23:29:25 SQL>
23:30:16 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        467        500 rows   03-APR-21 23:22:18             478             34

23:30:17 SQL>
23:30:43 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        493        500 rows   03-APR-21 23:22:18             505              7

23:30:44 SQL>
23:30:48 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        497        500 rows   03-APR-21 23:22:18             509              3

23:30:49 SQL>
23:30:50 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        500        500 rows   03-APR-21 23:22:18             512              0

23:30:51 SQL>
23:31:04 SQL> /

  SID OPNAME             TARGET_DESC           SOFAR  TOTALWORK UNITS  START_TIME         ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
   18 New_Product_IDs    Table : PRODUCTS        500        500 rows   03-APR-21 23:22:18             512              0

23:31:05 SQL>


(In this demo, all 500 rows actually do pass the "VALIDATE_FOR_UPDATE" check) 
 Normally, we expect V$SESSION_LONGOPS to be populated by Long Running Operations (I specify Operations because of the "misinterpretation" that I demonstrate here).  Operations that are Full Table Scans  or Parallel Execution or RMAN Operations --- all of these are cases where the view is populated "automatically" by Oracle.

However, as I have demonstrated above, you can use your own code to populate this view so that it can be used to monitor Long Running "Operations" that you have defined (or, rather, worked with the Developers to define if you are the DBA).



19 March, 2021

Patching -- opatch and datapatch in Oracle vs a single executable in SQL Server

 In the Oracle universe, when applying a Release Update patch, the DBA has to run "opatch" to patch the binaries and library files but also has to run a separate "datapatch" to update the data dictionary with SQL "Apply" Actions in each database.

In what seems to be a contrast, SQL Server patching requires only execution of the Patch exe file.  Does that mean that no SQL "Apply" Actions are required ?  See how it is done in SQL Server in my other blog post.



18 February, 2021

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

 As a follow up on a question in the previous blog post,  I demonstrate it again without restoring the Controlfile


The current SCN and available backups (Level-0 and Level-1) :



SQL> select  current_scn from v$database;

CURRENT_SCN
-----------
    1084836

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     6
Current log sequence           8
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 18 21:50:02 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list backup;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Incr 0  67.09M     DISK        00:00:19     18-FEB-21
        BP Key: 44   Status: AVAILABLE  Compressed: YES  Tag: TAG20210218T212223
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
  List of Datafiles in backup set 44
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/system.dbf
  2    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/sysaux.dbf
  3    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/undotbs.dbf
  4    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
  5    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx01.dbf
  6    0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/HR_DATA.dbf
  10   0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx02.dbf
  11   0  Incr 974397     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45      Full    11.52M     DISK        00:00:02     18-FEB-21
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20210218T212249
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-00
  SPFILE Included: Modification time: 18-FEB-21
  SPFILE db_unique_name: HEMANT
  Control File Included: Ckp SCN: 974397       Ckp time: 18-FEB-21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Incr 1  1.84M      DISK        00:00:16     18-FEB-21
        BP Key: 46   Status: AVAILABLE  Compressed: YES  Tag: TAG20210218T212541
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
  List of Datafiles in backup set 46
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/system.dbf
  2    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/sysaux.dbf
  3    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/undotbs.dbf
  4    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
  5    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx01.dbf
  6    1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/HR_DATA.dbf
  10   1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx02.dbf
  11   1  Incr 975490     18-FEB-21              NO    /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47      Full    11.52M     DISK        00:00:01     18-FEB-21
        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20210218T212606
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-01
  SPFILE Included: Modification time: 18-FEB-21
  SPFILE db_unique_name: HEMANT
  Control File Included: Ckp SCN: 975490       Ckp time: 18-FEB-21

RMAN>


Now my current SCN is 1084826 .
My Level-0 Backup (BackupSet 44) was at 974397 (and so was the Controlfile backup in BackupSet 45)
My Level-1 Backup (BackupSet 46) was at 975490 (and so was the Controlfile backup in BackupSet 47).

So, all of those backups are older than the current SCN.

Can I restore and recover the database without restoring the Controlfile ?


RMAN> quit


Recovery Manager complete.
oracle19c>rm /opt/oracle/oradata/HEMANT/system.dbf
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 21:53:38 2021
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 current_scn from v$database;

CURRENT_SCN
-----------
    1084978

SQL>
SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
toracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 18 21:57:23 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 mount;

Oracle instance started
database mounted

Total System Global Area    1207958960 bytes

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

RMAN>
RMAN> restore database;

Starting restore at 18-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1 tag=TAG20210218T212223
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-FEB-21

RMAN> recover database noredo;

Starting recover at 18-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1 tag=TAG20210218T212541
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 18-FEB-21

RMAN>
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/18/2021 21:59:38
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 22:00:12 2021
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> recover datbase using backup controlfile until cancel;
ORA-00905: missing keyword


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 975490 generated at 02/18/2021 21:25:11 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_4_1063318051.dbf
ORA-00280: change 975490 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}  -- commented the RET as it was being treated as an HTML Tag
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     975656

SQL>


I can't exactly use the same method as I did in the previous blog post.  This is because RMAN doesn't properly recognise this as an Incomplete Recovery if I have used the current Controlfile.
What is the workaround ?  Use sqlplus !  I can use the SQL command "recover database using backup controlfile until cancel" and then CANCEL to simulate an Incomplete Recovery that allows me to "open resetlogs" !
This is similar to the "OPEN RESETLOGS without really doing a Recovery" demo that I had presented earlier.

Note : The CURRENT_SCN is now 975656.  This is because I have restored and recovered from "older" database backups (BackupSet 45 at SCN 975490) and ignored any transactions after those backups.  The OPEN RESETLOGS recreates the Online Redo Logs and resynchronizes the Controlfile but, in the process some SCNs are incremented so it is higher than 975490 and lower than 1084978.
This doesn't mean that User Transactions after 975490 have been recovered. They have, actually been discarded.


11 February, 2021

Checking for Active Transactions

 Oracle 11.2 introduced the WAIT_ON_PENDING_DML function in the DBMS_UTILITY Package.

Here is a demonstration of how to use it :



17:05:21 SQL> @Check_for_Transactions
17:05:22 SQL> declare
17:05:22   2  check_for_transactions boolean;
17:05:22   3  scnvalue number;
17:05:22   4  begin
17:05:22   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:22   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:05:22   7                             timeout=>60,
17:05:22   8                             scn=>scnvalue);
17:05:22   9  if check_for_transactions then
17:05:22  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:22  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:22  12  else
17:05:22  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:22  14  end if;
17:05:22  15  end;
17:05:22  16  /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:05:22 SQL>


17:05:43 SQL> @Check_for_Transactions
17:05:44 SQL> declare
17:05:44   2  check_for_transactions boolean;
17:05:44   3  scnvalue number;
17:05:44   4  begin
17:05:44   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:44   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:05:44   7                             timeout=>60,
17:05:44   8                             scn=>scnvalue);
17:05:44   9  if check_for_transactions then
17:05:44  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:44  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:44  12  else
17:05:44  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:44  14  end if;
17:05:44  15  end;
17:05:44  16  /
One or More Active Transaction(s) present until Timeout

PL/SQL procedure successfully completed.

17:06:44 SQL>


17:07:08 SQL> @Check_for_Transactions
17:07:09 SQL> declare
17:07:09   2  check_for_transactions boolean;
17:07:09   3  scnvalue number;
17:07:09   4  begin
17:07:09   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:07:09   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:07:09   7                             timeout=>60,
17:07:09   8                             scn=>scnvalue);
17:07:09   9  if check_for_transactions then
17:07:09  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:07:09  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:07:09  12  else
17:07:09  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:07:09  14  end if;
17:07:09  15  end;
17:07:09  16  /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:07:23 SQL>


When I ran the Check code at17:05:22, there were no active transaction against the target table "HEMANT.MY_TXN_TABLE", so the Check completed immediately (the returned BOOLEAN is TRUE)

When I re-ran the Check code at 17:05:44, there were one or more transactions (uncommitted) present.  The Check code ran for 60 seconds until the specified timeout and returned the message "One or More Active Transaction(s) present until Timeout"

When I ran the Check code again at 17:07:09 there were one or more transactions present.  However, they committed within the 60seconds timeout so the Check ended at 17:07:23  (i.e. the function returned TRUE at 17:07:23)

However, this Check only checks for transactions present as at the time it began running.  If a third or fourth session begins a transaction after this start and yet does not commit, it would not be identified by this Check.  

With the caveat that the Check doesn't check for *new* transactions, this is useful when you are monitoring for the presence of transactions at a specific time --- .e.g you expected an ETL job to complete by 17:30 and know that no other session would have any transaction against the target table.

The "TABLES" parameter can actually take a comma-separated list of tables.
The "SCN" parameter is an IN OUT in that you can put in a specific SCN prior to which transactions may begun.  If a NULL or invalid value is passed, the function takes the current SCN.

31 January, 2021

RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

 Oracle does support all Incremental (as well as Full) Backups of Databases running in NOARCHIVELOG mode.  Such backups can be made when the database is in MOUNT (not OPEN) mode.

There are 2 "downsides" to Backups in NOARCHIVELOG mode :

1.  The database is unavailable (as it is not OPEN) for the duration of the BACKUP DATABASE run.  So, it would be a good idea to make frequent Incremental Level-1 backups as they could be faster (shorter duration) than the Level-0 backups (which could be scheduled during longer maintenance weekend hours)

2. If you lose any datafile(s) (one or more) you have to RESTORE and RECOVER the *whole* database.  You cannot restore and recover individual datafiles for a database in NOARCHIVELOG mode as you would be able to do with backups with ArchiveLogs.


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 20:01:50 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/31/2021 20:02:07
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN>
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1207958960 bytes

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

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-00 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 20:05:44 2021
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 max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
           865      864084

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/archivelog/HEMANT
Oldest online log sequence     863
Current log sequence           865
SQL>


At approximately 20:05 on 31-Jan-2021, the database is in NOARCHIVELOG mode. So, an RMAN BACKUP DATABASE command fails when the Database is OPEN.  I must restart the Database Instance in MOUNT (no OPEN) state to run an RMAN Backup.  I am particular to make this backup explicitly a Level-0 backup so that I can later take a Level-1 backup.

The highest Log Sequence# is 865 (the CURRENT Redo Log file) and the SCN is 864084.

Later ...


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:40:27 2021
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 max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
           872      869174

SQL> select count(*) from hemant.my_test_table;

  COUNT(*)
----------
     24554

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 21:43:17 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 mount

Oracle instance started
database mounted

Total System Global Area    1207958960 bytes

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

RMAN> backup as compressed backupset incremental level 1 database;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> alter database open;

Statement processed

RMAN> exit


Recovery Manager complete.
oracle19c>


So, with further transactions between 20:05 and 21:43, the highest Log Sequence# has gone from 865 to 872 (none of which are Archived) and the Database SCN has gone from 864084 to 869174.
I use the table "HEMANT.MY_TEST_TABLE" as the reference table at this point.

After some time :
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:57:21 2021
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 max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
           877      870492

SQL> select count(*) from hemant.my_test_table;

  COUNT(*)
----------
     27554

SQL>


There have been more transactions (incremented Log Sequence#, SCN and Row Count). However, I do not have a fresh backup of the database (and the database does not generate ArchiveLogs).

If I lose some or all of the Datafiles (and, possibly, even the Redo Log and Control Files) :


SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

RMAN> shutdown abort;

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
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 '/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01';

Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/HEMANT/control01.ctl
output file name=/opt/oracle/oradata/HEMANT/control02.ctl
Finished restore at 31-JAN-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>
RMAN> restore database;

Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-JAN-21

RMAN> recover database noredo;

Starting recover at 31-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished recover at 31-JAN-21

RMAN>
RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 22:07:53 2021
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 current_scn from v$database;

CURRENT_SCN
-----------
     870157

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
             1

SQL>
SQL> select count(*) from hemant.my_test_table;

  COUNT(*)
----------
     24554

SQL>


Note that I was able to use the controlfile autobackup. Then, the RESTORE DATABASE command restored datafiles from the Backup Piece(s) /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 that contained the Level-0 backup.  The RECOVER DATABASE NOREDO actually copied datafile incremental changes from the Level-1 backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1.  

I have to OPEN RESETLOGS because I must discard the Online Redo Logs as they are not consistent with what been restored (the Online Redo Logs, even if still present on disk, are in the "future" of the Recover and I do not have ArchiveLogs to bring the datafiles in sync).  The Redo Logs get reset to Sequence#=1.  The CURRENT_SCN would be slightly higher than the SCN recorded at the time of the backup -- it should not be lower than that at the time of the Incremental Backup.

All new rows inserted in the MY_TEST_TABLE are lost, as the Row Count reverts to 24,554 that was present when the Level-1 backup was taken.  All other transactions (and SCN increments) since the Level-1 Backup are also lost.

Note : It does not matter that I don't use an FRA.  The method is the same whether an FRA is used or not.

This shows the Level-0 and Level-1 backps (I am listing for only 1 datafile).  
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 22:20:04 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Incr 0  73.66M     DISK        00:00:12     31-JAN-21
        BP Key: 41   Status: AVAILABLE  Compressed: YES  Tag: TAG20210131T200317
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
  List of Datafiles in backup set 41
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1    0  Incr 863156     31-JAN-21              NO    /opt/oracle/oradata/HEMANT/system.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Incr 1  2.83M      DISK        00:00:10     31-JAN-21
        BP Key: 43   Status: AVAILABLE  Compressed: YES  Tag: TAG20210131T214349
        Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
  List of Datafiles in backup set 43
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1    1  Incr 869282     31-JAN-21              NO    /opt/oracle/oradata/HEMANT/system.dbf

RMAN>


For the Level-0 Backup, the datafile Checkpoint SCN is lower than that I had from the SQL Query because the SQL query was when the database was opened *after* the Backup.
For the Level-1 Backup, the datafile Checkpoint SCN is higher than that had from the SQL Query because the SQL query was before the Backup was taken.
Similarly, the Restored database has a higher SCN because the act of Restore+Recover+Open also increments the Database SCN.


25 January, 2021

RMAN's CATALOG command

The CATALOG START WITH command allows you to update the RMAN Repository with information about backup pieces (or archivelogs) in the specified location.  

For example, if older backups have already been purged from RMAN but are now restored from tape, they can be made visible to RMAN with the CATALOG START WITH command.

Another case would be if you relocate backups  to an alternate filesystem or diskgroup and the RMAN repository needs to updated to identify the new location.

If you copy a backup to another server and then restore the controlfile from a different backup, you can have the controlfile updated with information about the copied backups using this command.

You can also take a backup from a Primary database and catalog it to a Standby (e.g. when you want to update the Standby which is significantly lagging). Oracle also allows you to catalog a backup from a Standby into the Primary server if the backup / backups is/are not available on the Primary.


 A few demonstrations :



Demonstration 1 : Relocated Backup Set / BackupPiece for Datafile Backup(s)

SQL> select file#, name, checkpoint_change#
  2  from v$datafile
  3  where name = '/opt/oracle/oradata/ORCLCDB/users01.dbf'
  4  /

     FILE# NAME                                             CHECKPOINT_CHANGE#
---------- ------------------------------------------------ ------------------
         7 /opt/oracle/oradata/ORCLCDB/users01.dbf                     7583758

SQL>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:20 2021
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> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:26 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)

RMAN> list backup of datafile 7;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    229.31M    DISK        00:00:26     14-NOV-20
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: TAG20201114T162700
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7343626    14-NOV-20              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 53   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
-- Datafile 7 is currently at a higher SCN (7583758) then the latest backup as of 25-Jan-21





RMAN> crosscheck backup of datafile 7;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Crosschecked 1 objects


RMAN>
----- both backups are no longer available on disk





oracle19c>pwd
/var/tmp/For_Restore
oracle19c>ls -l
total 318016
-rw-r-----. 1 oracle oinstall   9194496 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
-rw-r-----. 1 oracle oinstall   4457984 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
-rw-r-----. 1 oracle oinstall   2251776 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
-rw-r-----. 1 oracle oinstall     62976 Jan 25 22:15 o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
-rw-r-----. 1 oracle oinstall 240459776 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
-rw-r-----. 1 oracle oinstall  69206016 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp
oracle19c>
----- backups of 25-Jan have been restored from Tape to /var/tmp/For_Restore





RMAN> catalog start with '/var/tmp/For_Restore';

searching for all files that match the pattern /var/tmp/For_Restore

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp

RMAN>
RMAN> list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    229.31M    DISK        00:00:26     14-NOV-20
        BP Key: 42   Status: EXPIRED  Compressed: YES  Tag: TAG20201114T162700
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7343626    14-NOV-20              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size
------- ---- -- ----------
53      Full    229.31M
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

  Backup Set Copy #2 of backup set 53
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     25-JAN-21       YES        TAG20210125T221421

    List of Backup Pieces for backup set 53 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    64      1   AVAILABLE   /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

  Backup Set Copy #1 of backup set 53
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     25-JAN-21       YES        TAG20210125T221421

    List of Backup Pieces for backup set 53 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    53      1   EXPIRED     /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

RMAN>
----- Now RMAN finds that there is one more backup in /var/tmp/For_Restore
----- RMAN also identifies that Backup Set 53 actually has 2 copies -- Copy#2 being in /var/tmp/For_Restore
----- The BackupSet is 53 but the BackupPiece is 53 at the FRA location and 64 for the Copy at /var/tmp/For_Restore
----- So, the CATALOG command has added this copy is a new BackupPiece in the Repository





RMAN> crosscheck backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=64 STAMP=1062800572
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 2 objects


RMAN> delete expired backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
42      42      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
53      53      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Deleted 1 EXPIRED objects

deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Deleted 1 EXPIRED objects


RMAN> list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 64   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- after running CROSSCHECK and DELETE EXPIRED, RMAN now identifies that Backupset 53 has only one BackupPiece at /var/tmp/For_Restore
----- Any attempt to RESTORE DATAFILE 7 would now use this BackupPiece




Demonstration 2 : Relocated ArchiveLog and Backup of ArchiveLog

RMAN> list archivelog from  sequence 119 until sequence 119;

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
286     1    119     A 25-JAN-21
        Name: /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf


RMAN> list backup of archivelog from  sequence 119 until sequence 119;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
51      2.15M      DISK        00:00:01     25-JAN-21
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221418
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

  List of Archived Logs in backup set 51
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    119     7582383    25-JAN-21 7583492    25-JAN-21

RMAN>
RMAN> crosscheck archivelog from  sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects


RMAN>
----- The CROSSCHECK command finds that both the ArchiveLog and it's backup are missing





RMAN> catalog start with '/var/tmp/ArchLogs_Restore/';

searching for all files that match the pattern /var/tmp/ArchLogs_Restore/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

RMAN> crosscheck archivelog from  sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation succeeded for archived log
archived log file name=/var/tmp/ArchLogs_Restore/1_119_1036108814.dbf RECID=299 STAMP=1062801628
Crosschecked 1 objects

validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
RMAN> delete expired backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
51      51      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Deleted 1 EXPIRED objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
----- After I CROSSCHECK in the new (restored) location, RMAN finds the ArchiveLog and it's backup
----- I can DELETE the EXPIRED backup
----- (note that the missing ArchiveLog /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf is no longer listed as the CROSSCHECK had already marked it as "validation failed")



Demonstration 3 : Datafile Backup from Standby available at Primary

----- Backup of Datafile 7 taken at the Standby
RMAN> backup as compressed backupset datafile 7  format '/var/tmp/For_Primary/datafile_7.bkp';

Starting backup at 25-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-JAN-21
channel ORA_DISK_1: finished piece 1 at 25-JAN-21
piece handle=/var/tmp/For_Primary/datafile_7.bkp tag=TAG20210125T225828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JAN-21

Starting Control File and SPFILE Autobackup at 25-JAN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_01_25/o1_mf_s_1062802630_j0xq4pmm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JAN-21

RMAN> 
----- The backup is then copied over to the Primary Server





RMAN> catalog start with '/var/tmp/From_Standby/';
RMAN> catalog start with '/var/tmp/From_Standby/';

searching for all files that match the pattern /var/tmp/From_Standby/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/From_Standby/datafile_7.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/From_Standby/datafile_7.bkp

RMAN>  list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 70   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 60
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62      Full    1.18M      DISK        00:00:00     25-JAN-21
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T225828
        Piece Name: /var/tmp/From_Standby/datafile_7.bkp
  List of Datafiles in backup set 62
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7591636    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- The Primary now recognises that there are 2 distinct backups of datafile 7
----- That in /var/tmp/For_Restore  is as of CheckPoint SCN 7583529  (it has a new BS Key and BackupPiece as I have deleted and re-cataloged it for this, third, demo)
----- The one from the Standby at /var/tmp/From_Standby  is at CheckPoint SCN 7591636  -- which is a higher SCN as it is a more recent backup
----- I can actualy use the backup from th Standby and Restore to the Primary





RMAN> sql 'alter database datafile 7 offline';

sql statement: alter database datafile 7 offline

RMAN> restore datafile 7;

Starting restore at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/From_Standby/datafile_7.bkp
channel ORA_DISK_1: piece handle=/var/tmp/From_Standby/datafile_7.bkp tag=TAG20210125T225828
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 01/25/2021 23:02:55
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 7 online
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/opt/oracle/oradata/ORCLCDB/users01.dbf'

RMAN> recover datafile 7;

Starting recover at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online

RMAN>
----- So, when datafile 7 is corrupt at the Primary, I take it OFFLINE and then issue a RESTORE command
----- RMAN automatically identifies that, of the two backups, the "From_Standby/datafile_7.bkp' is more recent 
----- So, the Backup from the Standby can be Restored to the Primary and the datafile brought ONLINE
----- RECOVERy is still required because the Primary database is currently at a higher SCN than the backup of that datafile from the Standby
----- So, the RECOVER command applies all Redo that is for SCN higher than 7591636 that needs to be applied to Datafile 7
----- For the duration when I had datafile 7 OFFLINE I had stopped Database Recovery at the Standby


Thus, there are different uses for the CATALOG START WITH command in RMAN
(what I haven't demonstrated here is restoring a Full Database -- either on the same server or to another server, when the BackupPieces are at an alternate locatoin).