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

Oracle Recover a lost datafile:

Recover a lost datafile:

Create a test table to verify the data is not lost from the datafile:

SQL>  select df.name "Datafile_Name",ts.name "Tablespace_Name" from v$datafile df,v$tablespace ts where df.ts#=ts.ts# and ts.name='USERS';

Datafile_Name                                       Tablespace_Name
-----------------------------------------------------------------------------------------------------------------------
+DATA2/orcl/users01.dbf                          USERS

SQL> create table hr.regions2 tablespace  users as select * from hr.regions;

Table created.

SQL> select * from hr.regions2 ;

REGION_ID REGION_NAME

---------- -------------------------

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

SQL> alter database datafile '+DATA2/orcl/users01.dbf' offline;

Database altered.

Delete the datafile:

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

alter database datafile '+DATA2/orcl/users01.dbf' online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '+DATA2/orcl/users01.dbf'

SQL> alter database datafile '+DATA2/orcl/users01.dbf' offline;

Database altered.

ASMCMD> mkalias '+DATA2/orcl/datafile/USERS.263.886783809' users01.dbf

ASMCMD> ls -l users01.dbf

Type      Redund  Striped  Time             Sys  Name

DATAFILE  UNPROT  COARSE   AUG 04 16:00:00  N    users01.dbf => +DATA2/ORCL/DATAFILE/USERS.263.886783809

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

alter database datafile '+DATA2/orcl/users01.dbf' online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '+DATA2/orcl/users01.dbf'

SQL> recover datafile 4;

Media recovery complete.

SQL> alter database datafile '+DATA2/orcl/users01.dbf' online;

Database altered.

SQL> select * from hr.regions2 ;

REGION_ID REGION_NAME

---------- -------------------------

    1 Europe

    2 Americas

    3 Asia

    4 Middle East and Africa

ASMCMD> ls -l users01.dbf

Type           Redund  Striped  Time             Sys  Name

DATAFILE       UNPROT  COARSE   AUG 04 15:00:00  N    users01.dbf => +DATA2/ORCL/DATAFILE/USERS.263.886783809

ASMCMD>

ORA-15028: ASM file not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Issue:

ORA-15028: ASM file not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

 Trying to drop a asm file which is actively used by ORACLE Database.

ASMCMD> rm -rf users01.dbf

ORA-15032: not all alterations performed

ORA-15028: ASM file '+DATA2/ORCL/users01.dbf' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Fix:

Take the file offline(or shut the DB down ) and then try again:

SQL> alter database datafile '+DATA2/orcl/users01.dbf' offline;

Database altered.

ASMCMD> rm -rf users01.dbf

ASMCMD> ls -l users01.dbf

ASMCMD-8002: entry 'users01.dbf' does not exist in directory '+DATA2/ORCL/'

rman take full database backup

Here we will take the whole database backup and keep it forever.

[oracle@collabn1 config]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 4 14:47:27 2015

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

RMAN> connect catalog rcuser/rcuser@prod1;

connected to recovery catalog database

RMAN> connect target /

connected to target database: ORCL (DBID=1414564590)

RMAN> run {

2> allocate channel ch01 type disk format "/home/oracle/sshaik/backup/orcl-backup-%U.bak";

3> backup database plus archivelog keep forever tag "long01";

4> }

allocated channel: ch01

channel ch01: SID=30 device type=DISK

Starting backup at 04-AUG-15

current log archived

backup will never be obsolete

archived logs required to recover from this backup will be backed up

channel ch01: starting archived log backup set

channel ch01: specifying archived log(s) in backup set

input archived log thread=1 sequence=7 RECID=1 STAMP=886865277

input archived log thread=1 sequence=8 RECID=2 STAMP=886865398

channel ch01: starting piece 1 at 04-AUG-15

channel ch01: finished piece 1 at 04-AUG-15

piece handle=/home/oracle/sshaik/backup/orcl-backup-04qdovfm_1_1.bak tag=LONG01 comment=NONE

channel ch01: backup set complete, elapsed time: 00:00:01

Finished backup at 04-AUG-15

Starting backup at 04-AUG-15

channel ch01: starting full datafile backup set

channel ch01: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA2/orcl/system01.dbf

input datafile file number=00002 name=+DATA2/orcl/sysaux01.dbf

input datafile file number=00003 name=+DATA2/orcl/undotbs01.dbf

input datafile file number=00005 name=+DATA2/orcl/example01.dbf

input datafile file number=00004 name=+DATA2/orcl/users01.dbf

channel ch01: starting piece 1 at 04-AUG-15

channel ch01: finished piece 1 at 04-AUG-15

piece handle=/home/oracle/sshaik/backup/orcl-backup-05qdovfo_1_1.bak tag=TAG20150804T153000 comment=NONE

channel ch01: backup set complete, elapsed time: 00:00:07

Finished backup at 04-AUG-15

Starting backup at 04-AUG-15

current log archived

backup will never be obsolete

archived logs required to recover from this backup will be backed up

channel ch01: starting archived log backup set

channel ch01: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=3 STAMP=886865407

channel ch01: starting piece 1 at 04-AUG-15

channel ch01: finished piece 1 at 04-AUG-15

piece handle=/home/oracle/sshaik/backup/orcl-backup-06qdovg0_1_1.bak tag=LONG01 comment=NONE

channel ch01: backup set complete, elapsed time: 00:00:01

Finished backup at 04-AUG-15

Starting Control File and SPFILE Autobackup at 04-AUG-15

piece handle=+DATA2/orcl/autobackup/2015_08_04/s_886865409.287.886865409 comment=NONE

Finished Control File and SPFILE Autobackup at 04-AUG-15

released channel: ch01

RMAN>

RMAN> list backup summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

69      B  F  A DISK        04-AUG-15       1       1       NO         TAG20150804T152113

70      B  F  A DISK        04-AUG-15       1       1       NO         TAG20150804T152121

104     B  A  A DISK        04-AUG-15       1       1       NO         LONG01

105     B  F  A DISK        04-AUG-15       1       1       NO         TAG20150804T153000

125     B  A  A DISK        04-AUG-15       1       1       NO         LONG01

140     B  F  A DISK        04-AUG-15       1       1       NO         TAG20150804T153009

RMAN CONFIGURE CONTROLFILE AUTOBAACKUP

Configuring Control File and Server Parameter File Autobackups

you can configure RMAN to automatically back up the control file and server parameter file. The autobackup occurs whenever a backup record is added. If the database runs in ARCHIVELOG mode, then an autobackup is also taken whenever the database structure metadata in the control file changes. A control file autobackup enables RMAN to recover the database even if the current control file, recovery catalog, and server parameter file are lost.

You can enable the autobackup feature by running the following command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

You can disable the feature by running the following command:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

Create RMAN backup script.

Connect to catalog & Target database:

[oracle@collabn1 config]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 4 14:22:45 2015

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

RMAN> connect catalog rcuser/rcuser@prod1

RMAN> create script whole_backup{

allocate channel ch01 type disk format '/u10/backup';

backup database plus archivelog;

}

created script whole_backup

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

    Scripts of Target Database ORCL

      Script Name

      Description

      -----------------------------------------------------------------------

      whole_backup

RMAN> print script whole_backup;

printing stored script: whole_backup

{

allocate channel ch01 type disk format '/u10/backup';

backup database plus archivelog;

}

RMAN>