Secure Oracle RMAN backup to Amazon cloud S3


Configure secure backup to Amazon cloud S3. Use database webfly01.

Create table EMP_TEST in tablespace user:

CREATE TABLE emp_test (
     emp_id NUMBER(6) NOT NULL,
     first_name VARCHAR2(30) NOT NULL,
     last_name VARCHAR2(30) NOT NULL,
     email VARCHAR2(30) NOT NULL,
     salary NUMBER(8,2),
     comm_pct NUMBER(2,2),
     mgr_id NUMBER(6))
tablespace USERS;
INSERT INTO emp_test VALUES (1,'Peter','Bucek','oracle@webfly.sk',11500,0.05,3);
INSERT INTO emp_test VALUES (2,'Yves','Smith','yves@webfly.sk',16500,0.02,3);
INSERT INTO emp_test VALUES (3,'Jo','Ma','jo@webfly.sk',15000,0.04,NULL);
INSERT INTO emp_test VALUES (4,'Emma','Decole','emma@webfly.sk',5000,0.1,5);
INSERT INTO emp_test VALUES (5,'Susan','Island','susan@webfly.sk',90000,0,2);
commit;

RMAN

Connect RMAN:

[oracle@centos-oracle-fra1-01 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 16 15:28:16 2016

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

connected to target database: WEBFLY01 (DBID=3271592580)

List all backups

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A SBT_TAPE    11-OCT-16       1       1       NO         TAG20161011T110042
2       B  F  A SBT_TAPE    11-OCT-16       1       1       NO         TAG20161011T110923
3       B  F  A SBT_TAPE    11-OCT-16       1       1       NO         TAG20161011T110923
4       B  F  A DISK        16-OCT-16       1       1       NO         TAG20161016T152833
5       B  F  A DISK        16-OCT-16       1       1       NO         TAG20161016T152833

Configure default device to STB_TAPE (Amazon cloud S3)

RMAN> configure default device type to 'sbt_tape';

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

Setup RMAN encryption:

Setup password for all tablespace

RMAN> set encryption on for all tablespaces algorithm 'AES256' identified by Head22 only;
RMAN> show encryption algorithm;

RMAN configuration parameters for database with db_unique_name WEBFLY01 are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

Test backup and restore

Test backup and restore tablespace "USERS" with encryption backup:

RMAN> report schema;

Report of database schema for database with db_unique_name WEBFLY01
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u01/app/oracle/oradata/webfly01/system01.dbf
3    860      SYSAUX               NO      /u01/app/oracle/oradata/webfly01/sysaux01.dbf
4    135      UNDOTBS1             YES     /u01/app/oracle/oradata/webfly01/undotbs01.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/webfly01/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    197      TEMP                 32767       /u01/app/oracle/oradata/webfly01/temp01.dbf

Backup tablespace USERS, file 6 (datafile users01.dbf):

RMAN> backup datafile 6;

Starting backup at 16-OCT-16
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/webfly01/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 16-OCT-16
channel ORA_SBT_TAPE_1: finished piece 1 at 16-OCT-16
piece handle=0arih7ia_1_1 tag=TAG20161016T180042 comment=API Version 2.0,MMS Version 3.16.9.21
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
Finished backup at 16-OCT-16

Delete tablespace "USERS" for test restore backup:

RMAN> host 'rm /u01/app/oracle/oradata/webfly01/users01.dbf';
host command complete

Test select from table emp_test:

RMAN> select count (*) from emp_test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/16/2016 19:31:14
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/webfly01/users01.dbf'

Change tablespace from online to offline mode:

RMAN>  sql 'alter database datafile 6 offline';
sql statement: alter database datafile 6 offline

Restore tablespace (datafile 6), but not set encryption (read error message):

RMAN> restore datafile 6;

Starting restore at 16-OCT-16
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /u01/app/oracle/oradata/webfly01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0arih7ia_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/16/2016 18:06:31
ORA-19870: error while restoring backup piece 0arih7ia_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

Set encryption and restore tablespace (password Head22):

RMAN> set decryption identified by Head22;
executing command: SET decryption
RMAN> restore datafile 6;

Starting restore at 16-OCT-16
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /u01/app/oracle/oradata/webfly01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0brih8hh_1_1
channel ORA_SBT_TAPE_1: piece handle=0brih8hh_1_1 tag=TAG20161016T181720
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-OCT-16

Recover datafile:

RMAN> recover datafile 6;

Starting recover at 16-OCT-16
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

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

Finished recover at 16-OCT-16

Change tablespace from offline to online mode:

RMAN> sql 'alter database datafile 6 online';
sql statement: alter database datafile 6 online
RMAN> select TABLESPACE_NAME, STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

Select from table, where is in tablespace USERS:

RMAN> select count (*) from emp_test;

using target database control file instead of recovery catalog
  COUNT(*)
----------
         5

Next: RMAN cloud Amazon

Posted in Databázy, RMAN on okt 16, 2016