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.

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

Datapump in Oracle ADB using SQL Developer Web

 If you have a small schema in the Oracle Cloud Autonomous Database, you can actually run DataPump from SQL Developer Web.  DATA_PUMP_DIR maps to a DBFS mount inside the Oracle Database.

Logged in to my Oracle ADB as "ADMIN"

I check if DATA_PUMP_DIR exists  and I find that it is in dbfs  :

I run a PLSQL Block to export the HEMANT schema using the DBMS_DATAPUMP API :

After I drop the two tables in the schema, I run the import using the DBMS_DATAPUMP API and then refresh the list of Tables owned by "HEMANT" :

This method is a quick way of using the Autonomous Database itself when you don't have an external Object Store to hold the Datapump file.  So, I'd use this only for very small schemas as the dump is itself loaded into DBFS.

The PLSQL Code is :

REM  Based on Script posted by Dick Goulet, posted to oracle-l@freelists.org
REM  With modifications by me.  
REM  Hemant K Chitale

REM Export schema "HEMANT"
declare
   h1   NUMBER := 0;
   h2 varchar2(1000);
   ex boolean := TRUE;
   fl number := 0;
   schema_exp varchar2(1000) := 'in(''HEMANT'')';
   f_name varchar2(50) := 'My_DataPump';
   dp_mode varchar2(100) := 'export';
   blksz number := 0;
   SUCCESS_WITH_INFO exception;
begin
      utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
      if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
      end if;
      h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP', version => 'COMPATIBLE');
     dbms_datapump.set_parallel(handle => h1, degree => 2);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
     dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
     dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
      when SUCCESS_WITH_INFO THEN NULL;
      when others then
         h2 := sqlerrm;
         if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
         end if;
         dbms_output.put_line(h2);
end;





REM Import schema "HEMANT"
declare
   h1   NUMBER := 0;
   h2 varchar2(1000);
   ex boolean := TRUE;
   fl number := 0;
   schema_exp varchar2(1000) := 'in(''HEMANT'')';
   f_name varchar2(50) := 'My_DataPump';
   dp_mode varchar2(100) := 'import';
   blksz number := 0;
   SUCCESS_WITH_INFO exception;
begin
      utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
      if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
      end if;
      h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP');
     dbms_datapump.set_parallel(handle => h1, degree => 2);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
     dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value=>'SKIP');
     dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
     dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
      when SUCCESS_WITH_INFO THEN NULL;
      when others then
         h2 := sqlerrm;
         if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
         end if;
         dbms_output.put_line(h2);
end;

Again, I emphasise that this is only for small dumps.  

Configuring Transparent Data Encryption -- 2 : For Columns

The previous demo of TDE in 19c was for a full Tablespace (converting an existing, non-TDE, Tablespace to an Encrypted Tablespace).

Pre-creating a Table with an Encrypted column would be straightforward :

CREATE TABLE employees (
 emp_id number primary key,
 first_name varchar2(128),
 last_name varchar2(128),
 national_id_no  varchar2(18) encrypt,
 salary number(6) )
tablespace hr_data
/

This encrypts the column with the AES encryption algorithm with a 192-bit key length ("AES192").

But what if you want to encrypt an existing, non-encrypted column ? You can use the MODIFY clause.

ALTER TABLE employees (
 MODIFY (national_id_no encrypt)
/

A quick demo :

SQL> create tablespace hr_data datafile '/opt/oracle/oradata/HEMANT/HR_DATA.dbf' size 5M;

Tablespace created.

SQL> CREATE TABLE employees (
  2  emp_id number primary key,
  3  first_name varchar2(128),
  4  last_name varchar2(128),
  5  national_id_no  varchar2(18),
  6  salary number(6) )
  7  tablespace hr_data;

Table created.

SQL> ^C

SQL> insert into employees
  2  select rownum, 'Hemant', 'Hemant' || to_char(rownum), dbms_random.string('X',12), 1000
  3  from dual
  4  connect by level "less than" 21    --- "less than" symbol replaced by string to preserve HTML formatting
  5  /

20 rows created.

SQL> commit;

Commit complete.

SQL> alter system  checkpoint;

System altered.

SQL> !sync ; sync

SQL>
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
H4J?
AAAAAAAA
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL> alter table employees
  2  MODIFY (national_id_no encrypt)
  3  /

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>

SQL> select emp_id, national_id_no
  2  from employees
  3  order by 1
  4  /

    EMP_ID NATIONAL_ID_NO
---------- ------------------
         1 LH6RUZRISE11
         2 DFIN8FZ7B6J0
         3 PLJ1R2QYRG2C
         4 UT3HB9ALF3B5
         5 LQMDUTFB2PTM
         6 1IGKV4E78M5J
         7 P9TQAV5BC5EM
         8 V69U6VZWCK26
         9 EOTOQHOB0F45
        10 OKMEV89XOQE1
        11 0D4L77P3YNF0
        12 CTMCLJSKQW82
        13 49T0AG7E2Y9X
        14 ODEY2J51D8RH
        15 R1HFMN34MYLH
        16 OXI0LOX161BO
        17 2XL44ZJVABGW
        18 4BIPWVECBWYO
        19 732KA25TZ3KR
        20 NN0X92ES90PH

20 rows selected.

SQL>
SQL> select emp_id, dump(national_id_no) col_dump
  2  from employees
  3  order by emp_id
  4  /

    EMP_ID COL_DUMP
---------- ------------------------------------------------------
         1 Typ=1 Len=12: 76,72,54,82,85,90,82,73,83,69,49,49
         2 Typ=1 Len=12: 68,70,73,78,56,70,90,55,66,54,74,48
         3 Typ=1 Len=12: 80,76,74,49,82,50,81,89,82,71,50,67
         4 Typ=1 Len=12: 85,84,51,72,66,57,65,76,70,51,66,53
         5 Typ=1 Len=12: 76,81,77,68,85,84,70,66,50,80,84,77
         6 Typ=1 Len=12: 49,73,71,75,86,52,69,55,56,77,53,74
         7 Typ=1 Len=12: 80,57,84,81,65,86,53,66,67,53,69,77
         8 Typ=1 Len=12: 86,54,57,85,54,86,90,87,67,75,50,54
         9 Typ=1 Len=12: 69,79,84,79,81,72,79,66,48,70,52,53
        10 Typ=1 Len=12: 79,75,77,69,86,56,57,88,79,81,69,49
        11 Typ=1 Len=12: 48,68,52,76,55,55,80,51,89,78,70,48
        12 Typ=1 Len=12: 67,84,77,67,76,74,83,75,81,87,56,50
        13 Typ=1 Len=12: 52,57,84,48,65,71,55,69,50,89,57,88
        14 Typ=1 Len=12: 79,68,69,89,50,74,53,49,68,56,82,72
        15 Typ=1 Len=12: 82,49,72,70,77,78,51,52,77,89,76,72
        16 Typ=1 Len=12: 79,88,73,48,76,79,88,49,54,49,66,79
        17 Typ=1 Len=12: 50,88,76,52,52,90,74,86,65,66,71,87
        18 Typ=1 Len=12: 52,66,73,80,87,86,69,67,66,87,89,79
        19 Typ=1 Len=12: 55,51,50,75,65,50,53,84,90,51,75,82
        20 Typ=1 Len=12: 78,78,48,88,57,50,69,83,57,48,80,72

20 rows selected.

SQL>

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
AAAAAAAA
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
(       i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA


SQL> select version, version_full from v$instance;

VERSION           VERSION_FULL
----------------- -----------------
19.0.0.0.0        19.3.0.0.0

SQL>


When I insert a new row, the plain-text for this is not present.  But the old (20) rows plain-text is still present.

SQL> insert into employees
  2  values (21,'HemantNew','HemantNew21','ABCDEFGHIJ88',2000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync;sync

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf
}|{z
HEMANT
3J?5
SJ?
HR_DATA
UTJ?
AAAAAAAA
        HemantNew
HemantNew214S
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
(       i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL>

So, it seems that after I ran the MODIFY to encrypt a column, Oracle created new copies of the 20 rows with encrypted values.  However, the old plain-text (non-encrypted) values are still present in the datafile.

Apparently, those "still present" plain-text representations of the "NATIONAL_ID_NO" column in the datafile are explained in the documentation as :

"Column values encrypted using TDE are stored in the data files in encrypted form. However, these data files may still contain some plaintext fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file was deleted by the operating system."

You should remove old plaintext fragments that can appear over time.

Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace.

To minimize this risk:

1. Create a new tablespace in a new data file.

   You can use the CREATE TABLESPACE statement to create this tablespace.

2. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement.

   Repeat this step for all of the objects in the original tablespace.

3. Drop the original tablespace.

   You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities.

4. Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).