Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, January 21, 2016

HOW TO ENCRYPT RMAN BACKUPS USING PASSWORD

RMAN Backup Encryption Modes
RMAN offers three encryption modes: transparent mode, password mode, and dual mode.


Password Encryption of Backups
Password encryption requires that the DBA provide a password when creating and restoring encrypted backups. Restoring a password-encrypted backup requires the same password that was used to create the backup.

Password encryption is useful for backups that are restored at remote locations, but which must remain secure in transit. Password encryption cannot be persistently configured. You do not need to configure an Oracle wallet if password encryption is used exclusively.

Caution:
If you forget or lose the password that you used to encrypt a password-encrypted backup, then you cannot restore the backup.

To use password encryption, use the SET ENCRYPTION ON IDENTIFIED BY password ONLY command in your RMAN scripts.

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

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

RMAN> set encryption on identified by shaiksameer2 only;

executing command: SET encryption

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SHAIKDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/snapcf_SHAIKDB.f'; # default


RMAN> run{
2> allocate channel ch01 type disk format '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_%U.bkp';
3> backup incremental level=0 TAG='test password' database plus archivelog;
4> backup archivelog all;
5> }

released channel: ORA_DISK_1
allocated channel: ch01
channel ch01: SID=43 device type=DISK


Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=59 RECID=1 STAMP=901721811
input archived log thread=1 sequence=60 RECID=2 STAMP=901721937
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_09qrugg0_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=901726097
input archived log thread=1 sequence=2 RECID=6 STAMP=901726243
input archived log thread=1 sequence=3 RECID=7 STAMP=901726245
input archived log thread=1 sequence=4 RECID=8 STAMP=901726311
input archived log thread=1 sequence=5 RECID=9 STAMP=901726720
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0aqrugg1_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/shaikdb/lob01.dbf
input datafile file number=00001 name=/u01/app/oracle/shaikdb/SHAIKDB/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/shaikdb/SHAIKDB/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/shaikdb/SHAIKDB/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/shaikdb/SHAIKDB/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/shaikdb/tbs1
input datafile file number=00007 name=/u01/app/oracle/shaikdb/tbs1.dbf
input datafile file number=00008 name=/u01/app/oracle/shaikdb/tbs3.dbf
input datafile file number=00009 name=/u01/app/oracle/shaikdb/idx1.dbf
input datafile file number=00004 name=/u01/app/oracle/shaikdb/SHAIKDB/users01.dbf
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:25
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
including current control file in backup set
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=10 STAMP=901726750
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=901726097
input archived log thread=1 sequence=2 RECID=6 STAMP=901726243
input archived log thread=1 sequence=3 RECID=7 STAMP=901726245
input archived log thread=1 sequence=4 RECID=8 STAMP=901726311
input archived log thread=1 sequence=5 RECID=9 STAMP=901726720
input archived log thread=1 sequence=6 RECID=10 STAMP=901726750
input archived log thread=1 sequence=7 RECID=11 STAMP=901726751
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp tag=TAG20160121T153911 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16
released channel: ch01

RMAN> exit



RMAN> restore controlfile from '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp';

Starting restore at 21-JAN-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/21/2016 15:45:29
ORA-19870: error while restoring backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

[oracle@collabn1 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 21 16:17:59 2016

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

RMAN> connect target /

connected to target database: SHAIKDB (not mounted)

RMAN> set decryption identified by shaiksameer2;

executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp';

Starting restore at 21-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/shaikdb/SHAIKDB/control01.ctl
output file name=/u01/app/oracle/shaikdb/SHAIKDB/control02.ctl
Finished restore at 21-JAN-16

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.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: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp

RMAN> restore database;

Starting restore at 21-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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 /u01/app/oracle/shaikdb/SHAIKDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/shaikdb/SHAIKDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/shaikdb/SHAIKDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/shaikdb/SHAIKDB/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/shaikdb/SHAIKDB/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/shaikdb/tbs1
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/shaikdb/tbs1.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/shaikdb/tbs3.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/shaikdb/idx1.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/shaikdb/lob01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp tag=TEST PASSWORD
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-JAN-16

RMAN> recover database;

Starting recover at 21-JAN-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp tag=TAG20160121T153911
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/arch1_6_901723553.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/arch1_7_901723553.dbf thread=1 sequence=7
unable to find archived log
archived log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/21/2016 16:20:00
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 1863900

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@collabn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 21 16:20:34 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SHAIKDB>select open_mode,database_role from v$database;

OPEN_MODE         DATABASE_ROLE
-------------------- ----------------
READ WRITE         PRIMARY

1 comment:

Ketan Patel said...

Sameer, please reach out to me. I live near by and also in the Oracle ERP space for 15+ years.