How to Backup and Restore Under Encryption and Compression

  • by
If your database is running on Enterprise Edition, then you can have encryption and compression option. Let's see the version we are running first.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

  1. Configure RMAN settings of ENCRYPTION and COMPRESSION.
  2. RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

    new RMAN configuration parameters:
    CONFIGURE ENCRYPTION FOR DATABASE ON;
    new RMAN configuration parameters are successfully stored

    RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

    new RMAN configuration parameters:
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
    new RMAN configuration parameters are successfully stored

    If the backup space is in contention, you can also turn OPTIMIZATION on.
    RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

    new RMAN configuration parameters:
    CONFIGURE BACKUP OPTIMIZATION ON;
    new RMAN configuration parameters are successfully stored

  3. Set the key to encrypt the backup in this session.
  4. RMAN> set encryption on identified by password only;

    executing command: SET encryption

  5. Begin to backup.
  6. RMAN> backup as compressed backupset tag '20151028' database plus archivelog;


    Starting backup at 28-OCT-15
    current log archived
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=55 device type=DISK
    skipping archived logs of thread 1 from sequence 90 to 99; already backed up
    skipping archived logs of thread 1 from sequence 1 to 14; already backed up
    channel ORA_DISK_1: starting compressed archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=15 RECID=94 STAMP=894242019
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_annnn_20151028_c30ylmj9_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 28-OCT-15

    Starting backup at 28-OCT-15
    using channel ORA_DISK_1
    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=/u01/app/oracle/oradata/orcl/system01.dbf
    input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
    input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
    input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ylnw7_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/FinanPDB/sysaux01.dbf
    input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/FinanPDB/system01.dbf
    input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/FinanPDB/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/133A1CEF9E1F6209E0530100007F2D8A/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yoy4g_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00041 name=/u01/app/oracle/oradata/orcl/Finance_PDB/sysaux01.dbf
    input datafile file number=00040 name=/u01/app/oracle/oradata/orcl/Finance_PDB/system01.dbf
    input datafile file number=00042 name=/u01/app/oracle/oradata/orcl/Finance_PDB/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/22C2104BFC1C39AFE0530100007F803C/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yqoyf_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
    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=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
    input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1339CA7290D05D9FE0530100007F3AAC/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ysf35_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
    Finished backup at 28-OCT-15

    Starting backup at 28-OCT-15
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=16 RECID=95 STAMP=894242292
    channel ORA_DISK_1: starting piece 1 at 28-OCT-15
    channel ORA_DISK_1: finished piece 1 at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_annnn_20151028_c30yv4hr_.bkp tag=20151028 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 28-OCT-15

    Starting Control File and SPFILE Autobackup at 28-OCT-15
    piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_28/o1_mf_s_894242293_c30yv86x_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 28-OCT-15

    Let's review the backup.
    RMAN> list backup tag '20151028';


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


    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    19      96.21M     DISK        00:00:11     28-OCT-15    
            BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/DB_RECOVERY_FILE_DEST

      List of Archived Logs in backup set 19
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    90      2346481    27-AUG-15 2462800    21-OCT-15
      1    91      2462800    21-OCT-15 2481788    21-OCT-15
      1    92      2481788    21-OCT-15 2489666    21-OCT-15
      1    93      2489666    21-OCT-15 2498065    21-OCT-15
      1    94      2498065    21-OCT-15 2504491    21-OCT-15
      1    95      2504491    21-OCT-15 2513513    21-OCT-15
      1    96      2513513    21-OCT-15 2521827    21-OCT-15
      1    97      2521827    21-OCT-15 2969776    21-OCT-15
      1    98      2969776    21-OCT-15 2972024    22-OCT-15
      1    99      2972024    22-OCT-15 2973668    22-OCT-15

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    20      65.31M     DISK        00:00:10     28-OCT-15    
            BP Key: 20   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST

      List of Archived Logs in backup set 20
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    1       2972684    22-OCT-15 3074442    22-OCT-15
      1    2       3074442    22-OCT-15 3096702    22-OCT-15
      1    3       3096702    22-OCT-15 3111142    23-OCT-15
      1    4       3111142    23-OCT-15 3213079    25-OCT-15
      1    5       3213079    25-OCT-15 3223955    25-OCT-15
      1    6       3223955    25-OCT-15 3325139    27-OCT-15
      1    7       3325139    27-OCT-15 3333773    27-OCT-15
      1    8       3333773    27-OCT-15 3348634    27-OCT-15
      1    9       3348634    27-OCT-15 3350896    27-OCT-15
      1    10      3350896    27-OCT-15 3358609    28-OCT-15
      1    11      3358609    28-OCT-15 3358898    28-OCT-15
      1    12      3358898    28-OCT-15 3358934    28-OCT-15
      1    13      3358934    28-OCT-15 3359114    28-OCT-15
      1    14      3359114    28-OCT-15 3359245    28-OCT-15

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    21      60.00K     DISK        00:00:00     28-OCT-15    
            BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_annnn_20151028_c30ylmj9_.bkp

      List of Archived Logs in backup set 21
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    15      3359245    28-OCT-15 3359550    28-OCT-15

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    22      Full    341.79M    DISK        00:01:42     28-OCT-15    
            BP Key: 22   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ylnw7_.bkp
      List of Datafiles in backup set 22
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 3359559    28-OCT-15 /u01/app/oracle/oradata/orcl/system01.dbf
      3       Full 3359559    28-OCT-15 /u01/app/oracle/oradata/orcl/sysaux01.dbf
      4       Full 3359559    28-OCT-15 /u01/app/oracle/oradata/orcl/undotbs01.dbf
      6       Full 3359559    28-OCT-15 /u01/app/oracle/oradata/orcl/users01.dbf

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    23      Full    168.88M    DISK        00:00:52     28-OCT-15    
            BP Key: 23   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/133A1CEF9E1F6209E0530100007F2D8A/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yoy4g_.bkp
      List of Datafiles in backup set 23
      Container ID: 3, PDB Name: FINANPDB
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      8       Full 3359610    28-OCT-15 /u01/app/oracle/oradata/orcl/FinanPDB/system01.dbf
      9       Full 3359610    28-OCT-15 /u01/app/oracle/oradata/orcl/FinanPDB/sysaux01.dbf
      10      Full 3359610    28-OCT-15 /u01/app/oracle/oradata/orcl/FinanPDB/users01.dbf

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    24      Full    168.88M    DISK        00:00:47     28-OCT-15    
            BP Key: 24   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/22C2104BFC1C39AFE0530100007F803C/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yqoyf_.bkp
      List of Datafiles in backup set 24
      Container ID: 4, PDB Name: FINANCE_PDB
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      40      Full 3112429    23-OCT-15 /u01/app/oracle/oradata/orcl/Finance_PDB/system01.dbf
      41      Full 3112429    23-OCT-15 /u01/app/oracle/oradata/orcl/Finance_PDB/sysaux01.dbf
      42      Full 3112429    23-OCT-15 /u01/app/oracle/oradata/orcl/Finance_PDB/users01.dbf

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    25      Full    165.32M    DISK        00:00:47     28-OCT-15    
            BP Key: 25   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/1339CA7290D05D9FE0530100007F3AAC/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ysf35_.bkp
      List of Datafiles in backup set 25
      Container ID: 2, PDB Name: PDB$SEED
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      5       Full 2352447    27-AUG-15 /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
      7       Full 2352447    27-AUG-15 /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    26      142.00K    DISK        00:00:00     28-OCT-15    
            BP Key: 26   Status: AVAILABLE  Compressed: YES  Tag: 20151028
            Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_annnn_20151028_c30yv4hr_.bkp

      List of Archived Logs in backup set 26
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    16      3359550    28-OCT-15 3359747    28-OCT-15

    You can see nothing about encryption in the above. There's no way to know which algorithm was being used.

  7. Create a restore point either on SQL prompt or RMAN prompt.
  8. RMAN> create restore point before_converting preserve;

    Statement processed

    RMAN> list restore point all;

    SCN              RSP Time  Type       Time      Name
    ---------------- --------- ---------- --------- ----
    3570103                    PRESERVED  28-OCT-15 BEFORE_CONVERTING

  9. Set the key to decrypt the backup in this session.
  10. Before running restore and recover, you have to set key. The statement below is wrong.RMAN> set encryption on identified by password only;
    If you set the wrong statement incautiously, you will get the following errors during restore / recover:
    ORA-19913: unable to decrypt backup
    ORA-28365: wallet is not open

    You should set decryption instead.
    RMAN> set decryption on identified by password only;
  11. Restore and recover the database to said restore point BEFORE_CONVERTING.
  12. RMAN> run {
        shutdown immediate;
        startup mount;
        restore database to restore point before_converting;
        recover database to restore point before_converting;
        alter database open resetlogs;
    }
    2> 3> 4> 5> 6> 7>
    database closed
    database dismounted
    Oracle instance shut down

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

    Total System Global Area     838860800 bytes

    Fixed Size                     2929936 bytes
    Variable Size                562039536 bytes
    Database Buffers             268435456 bytes
    Redo Buffers                   5455872 bytes

    Starting restore at 28-OCT-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=21 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=22 device type=DISK

    skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
    skipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
    skipping datafile 40; already restored to file /u01/app/oracle/oradata/orcl/Finance_PDB/system01.dbf
    skipping datafile 41; already restored to file /u01/app/oracle/oradata/orcl/Finance_PDB/sysaux01.dbf
    skipping datafile 42; already restored to file /u01/app/oracle/oradata/orcl/Finance_PDB/users01.dbf
    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 /u01/app/oracle/oradata/orcl/system01.dbf
    channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
    channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
    channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/users01.dbf
    channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ylnw7_.bkp
    channel ORA_DISK_2: starting datafile backup set restore
    channel ORA_DISK_2: specifying datafile(s) to restore from backup set
    channel ORA_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/FinanPDB/system01.dbf
    channel ORA_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/FinanPDB/sysaux01.dbf
    channel ORA_DISK_2: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/FinanPDB/users01.dbf
    channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/133A1CEF9E1F6209E0530100007F2D8A/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yoy4g_.bkp
    lchannel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/133A1CEF9E1F6209E0530100007F2D8A/backupset/2015_10_28/o1_mf_nnndf_20151028_c30yoy4g_.bkp tag=20151028
    channel ORA_DISK_2: restored backup piece 1
    channel ORA_DISK_2: restore complete, elapsed time: 00:05:25
    channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_28/o1_mf_nnndf_20151028_c30ylnw7_.bkp tag=20151028
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:06:37
    Finished restore at 28-OCT-15

    Starting recover at 28-OCT-15
    using channel ORA_DISK_1
    using channel ORA_DISK_2

    starting media recovery

    archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_16_c30yv494_.arc
    archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_17_c32rqtjo_.arc
    archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_18_c32s9chy_.arc
    archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_19_c32sbh1f_.arc
    archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_20_c32sn8g9_.arc
    archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_21_c32tdknd_.arc
    archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_16_c30yv494_.arc thread=1 sequence=16
    archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_17_c32rqtjo_.arc thread=1 sequence=17
    archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_18_c32s9chy_.arc thread=1 sequence=18
    archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_10_28/o1_mf_1_19_c32sbh1f_.arc thread=1 sequence=19
    media recovery complete, elapsed time: 00:00:36
    Finished recover at 28-OCT-15

    Statement processed

Leave a Reply

Your email address will not be published. Required fields are marked *