Oracle backups in Azure





This post is all about how you can back up Oracle in Azure, well 'all about' is an exaggeration - this is what i have set up for our current Oracle backups in Azure. There is of course more than one way to do this - the solution below works for us but may not work for you.

Feedback on this would be great but please make it constructive ("this is shit" is not constructive whereas "this is shit because ..." is)

First up to make some statements about restrictions/availability of stuff in Azure.

1) a media management library (mml) to allow backups directly from azure to azure file/backup storage is not available. This library is available for both oracle cloud and Amazon AWS. There are some good notes on the Amazon one here and stuff to get you started for oracle cloud here
2) our policy is to encrypt any data at rest in Azure - for this we require the advanced security option to enable TDE of database tablespaces and encryption of rman backups.
3) Oracle (yesterday actually) pulled all licence included VM's from Azure (this will piss off quite a few people i think) so you have to provide your own licences to use oracle at all on the Azure cloud. There is a special licence case for Amazon/Azure so you thankfully don't have to licence every possible physical server in the cloud providers datacentre... :-). That special case is described here

OK so now onto the setup we rolled out.

The database servers are set up with the following mount points (all provided from different 'disks')

/oracle (part of root vg) contains oracle software
/oracle/oradata contains 'DB_CREATE_FILE_DEST'
/fra contains 'DB_RECOVER_FILE_DEST'
/oraclebackupfileshare is a 'pseudo' backup tape device - i think of it as a virtual tape library (VTL) but it's actually a CIFS file share (i talked about that here ) so i won't repeat myself

With this setup we separate online files from archive logs and backups but also have redo mirrored across 2 devices. This seems to be the de facto standard most shops are using and is the default if you go the OMF route with all the defaults.

Prior to going on to the backups I'll describe briefly how we set up TDE to encrypt the application data (side note here is that system/sysaux etc cannot currently be encrypted). We also used this to encrypt the rman catalog so I'll show the example from there.

First up make sure you have an entry in the sqlnet.ora that looks something like this:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/oracle/wallet/$ORACLE_SID/)))

Note that i have $ORACLE_SID in the path - this is essential if you have more than one database on the same server that wants to use encryption - wallets cannot be shared - this allows individual databases to all have a separate wallet

Once that is in place use the following steps to initialize the wallet and create an encrypted tablespace (note this syntax is only valid in 12c - in 11g you use something different). The syntax can be a little picky but these steps worked for me

SQL> administer key management create keystore '/oracle/wallet/AZRCAT' identified by "secret!";

keystore altered.

SQL> administer key management set keystore open identified by "secret!";

keystore altered.

SQL> administer key management set key identified by "secret!"  with backup;

keystore altered.

SQL> create tablespace rcat encryption default storage (encrypt);

Tablespace created.

At this point anything you write to the rcat tablespace is encrypted and data is only readable when the wallet is open. The wallet and knowing it's password are vital and you must make sure these are backed up and the password stored somewhere safe. The password should not be stored anywhere on the server - this is what protects you from data theft. Note however that every time you open the database you will have to manually open the wallet with this password - this is an important procedural step that people need to know and can affect automation.

OK that's the background covered on TDE, i touched on the fact we use an rman catalog - i won't describe how i created that - i just used the standard create catalog process described here.

Now we have our application database with encrypted data, we have an rman catalog with encrypted data and we now need to create rman scripts that will do our backups.

I decided to take the following approach (it's more common for very large databases but works just as well here) - the general steps are:

1) take an encrypted incremental backup to apply to an image copy in the fra
2) backup encrypted the fra including all files to the CIFS fileshare
3) delete anything obsolete from everywhere (i have obsolescence set to 28 days)

The incremental apply business is described well here

So now to the script - the following is planned as a once per day task

CONFIGURE RETENTION POLICY TO recovery window of 28 days;
 CONFIGURE BACKUP OPTIMIZATION ON;
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE ENCRYPTION FOR DATABASE ON;
 RECOVER COPY OF DATABASE WITH TAG "daily_backup";
 BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG "daily_backup" DATABASE include current controlfile spfile plus archivelog;
 BACKUP RECOVERY AREA to destination '/oraclebackupfileshare'; 
delete noprompt obsolete;

Hopefully its reasonable self explanatory - the incremental apply is a little confusing until you get your head round it. Some bits to highlight though.

1) The configure command are all hardcoded to ensure these are the values used and are not changed elsewhere - particularly the encryption line
2) The wallet must be open for this to work otherwise it just throws errors
3) Controlfile autobackup is off by default and should always be on
4) very important to point out is that in the backup incremental line i specifically include controlfile/spfile in the command - otherwise backups of these don't get written to the FRA and then not to the backup location - this could be a disaster if this step is missed!

Example output is shown below -it's a little long but i think useful to read through to aid in understanding

RMAN> CONFIGURE RETENTION POLICY TO recovery window of 28 days; 
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE ENCRYPTION FOR DATABASE ON;
RECOVER COPY OF DATABASE WITH TAG "daily_backup";
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG "daily_backup" DATABASE include current controlfile spfile plus archivelog;
BACKUP RECOVERY AREA to destination '/oraclebackupfileshare';
delete noprompt obsolete;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
Starting recover at 03-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/fra/DEMO/datafile/o1_mf_system_ckn8o4cv_.dbf
recovering datafile copy file number=00002 name=/fra/DEMO/datafile/o1_mf_sysaux_ckn8ocjf_.dbf
recovering datafile copy file number=00003 name=/fra/DEMO/datafile/o1_mf_sys_undo_ckn8ots0_.dbf
recovering datafile copy file number=00004 name=/fra/DEMO/datafile/o1_mf_secret_d_cl468gc1_.dbf
channel ORA_DISK_1: reading from backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_nnnd1_DAILY_BACKUP_clkgdtq0_.bkp
channel ORA_DISK_1: piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_nnnd1_DAILY_BACKUP_clkgdtq0_.bkp tag=DAILY_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished recover at 03-MAY-16

Starting Control File and SPFILE Autobackup at 03-MAY-16
piece handle=/fra/DEMO/autobackup/2016_05_03/o1_mf_s_910886357_cll0rppb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAY-16

RMAN> 

Starting backup at 03-MAY-16
current log archived
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 12 to 34; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=24 STAMP=910886364
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0rygx_.bkp tag=DAILY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-MAY-16

Starting backup at 03-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/DEMO/datafile/o1_mf_system_ckn0ntfx_.dbf
input datafile file number=00002 name=/oracle/oradata/DEMO/datafile/o1_mf_sysaux_ckn0nybs_.dbf
input datafile file number=00003 name=/oracle/oradata/DEMO/datafile/o1_mf_sys_undo_ckn0o0bp_.dbf
input datafile file number=00004 name=/oracle/oradata/DEMO/datafile/o1_mf_secret_d_cl461109_.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_nnnd1_DAILY_BACKUP_cll0s3jx_.bkp tag=DAILY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_ncnn1_DAILY_BACKUP_cll0s8hx_.bkp tag=DAILY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_nnsn1_DAILY_BACKUP_cll0sbwz_.bkp tag=DAILY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAY-16

Starting backup at 03-MAY-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=36 RECID=25 STAMP=910886380
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0sfp7_.bkp tag=DAILY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAY-16

Starting Control File and SPFILE Autobackup at 03-MAY-16
piece handle=/fra/DEMO/autobackup/2016_05_03/o1_mf_s_910886383_cll0sjh2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAY-16

RMAN> 
Starting backup at 03-MAY-16
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 12 to 34; already backed up
skipping backup set key 1; already backed up 1 time(s)
skipping backup set key 4; already backed up 1 time(s)
skipping backup set key 6; already backed up 1 time(s)
skipping backup set key 9; already backed up 1 time(s)
skipping backup set key 18; already backed up 1 time(s)
skipping backup set key 19; already backed up 1 time(s)
skipping backup set key 21; already backed up 1 time(s)
skipping backup set key 22; already backed up 1 time(s)
skipping backup set key 23; already backed up 1 time(s)
skipping backup set key 25; already backed up 1 time(s)
skipping backup set key 26; already backed up 1 time(s)
skipping backup set key 27; already backed up 1 time(s)
skipping backup set key 28; already backed up 1 time(s)
skipping backup set key 30; already backed up 1 time(s)
skipping backup set key 31; already backed up 1 time(s)
skipping backup set key 32; already backed up 1 time(s)
skipping backup set key 33; already backed up 1 time(s)
skipping backup set key 36; already backed up 1 time(s)
skipping backup set key 37; already backed up 1 time(s)
skipping backup set key 38; already backed up 1 time(s)
skipping backup set key 41; already backed up 1 time(s)
skipping backup set key 42; already backed up 1 time(s)
skipping backup set key 43; already backed up 1 time(s)
skipping backup set key 44; already backed up 1 time(s)
skipping backup set key 46; already backed up 1 time(s)
skipping backup set key 47; already backed up 1 time(s)
skipping backup set key 48; already backed up 1 time(s)
skipping backup set key 49; already backed up 1 time(s)
skipping backup set key 51; already backed up 1 time(s)
skipping backup set key 52; already backed up 1 time(s)
skipping backup set key 53; already backed up 1 time(s)
skipping backup set key 54; already backed up 1 time(s)
skipping backup set key 57; already backed up 1 time(s)
skipping backup set key 58; already backed up 1 time(s)
skipping backup set key 59; already backed up 1 time(s)
skipping backup set key 60; already backed up 1 time(s)
skipping backup set key 61; already backed up 1 time(s)
skipping backup set key 62; already backed up 1 time(s)
skipping backup set key 63; already backed up 1 time(s)
skipping backup set key 65; already backed up 1 time(s)
skipping backup set key 66; already backed up 1 time(s)
skipping backup set key 67; already backed up 1 time(s)
skipping backup set key 68; already backed up 1 time(s)
skipping backup set key 69; already backed up 1 time(s)
skipping backup set key 70; already backed up 1 time(s)
skipping backup set key 71; already backed up 1 time(s)
skipping backup set key 72; already backed up 1 time(s)
skipping backup set key 73; already backed up 1 time(s)
skipping backup set key 74; already backed up 1 time(s)
skipping backup set key 75; already backed up 1 time(s)
skipping backup set key 76; already backed up 1 time(s)
skipping backup set key 77; already backed up 1 time(s)
skipping backup set key 78; already backed up 1 time(s)
skipping backup set key 80; already backed up 1 time(s)
skipping backup set key 81; already backed up 1 time(s)
skipping backup set key 82; already backed up 1 time(s)
skipping backup set key 83; already backed up 1 time(s)
skipping backup set key 84; already backed up 1 time(s)
skipping backup set key 85; already backed up 1 time(s)
skipping backup set key 86; already backed up 1 time(s)
skipping backup set key 87; already backed up 1 time(s)
skipping backup set key 88; already backed up 1 time(s)
skipping backup set key 89; already backed up 1 time(s)
skipping backup set key 90; already backed up 1 time(s)
skipping backup set key 91; already backed up 1 time(s)
skipping backup set key 92; already backed up 1 time(s)
skipping backup set key 93; already backed up 1 time(s)
skipping backup set key 97; already backed up 1 time(s)
skipping backup set key 107; already backed up 1 time(s)
skipping backup set key 108; already backed up 1 time(s)
skipping backup set key 109; already backed up 1 time(s)
skipping backup set key 110; already backed up 1 time(s)
skipping backup set key 111; already backed up 1 time(s)
skipping backup set key 112; already backed up 1 time(s)
skipping backup set key 113; already backed up 1 time(s)
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: including datafile copy of datafile 00001 in backup set
input file name=/fra/DEMO/datafile/o1_mf_system_ckn8o4cv_.dbf
channel ORA_DISK_1: including datafile copy of datafile 00002 in backup set
input file name=/fra/DEMO/datafile/o1_mf_sysaux_ckn8ocjf_.dbf
channel ORA_DISK_1: including datafile copy of datafile 00003 in backup set
input file name=/fra/DEMO/datafile/o1_mf_sys_undo_ckn8ots0_.dbf
channel ORA_DISK_1: including datafile copy of datafile 00004 in backup set
input file name=/fra/DEMO/datafile/o1_mf_secret_d_cl468gc1_.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_nnndf_TAG20160503T155950_cll0sph9_.bkp tag=TAG20160503T155950 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=24 STAMP=910886364
input archived log thread=1 sequence=36 RECID=25 STAMP=910886380
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_annnn_TAG20160503T155950_cll0thq7_.bkp tag=TAG20160503T155950 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: input backup set: count=128, stamp=910867602, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/autobackup/2016_05_03/o1_mf_s_910867602_clkggmbh_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_ncsnf_TAG20160503T104642_cll0tlqg_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=129, stamp=910886358, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/autobackup/2016_05_03/o1_mf_s_910886357_cll0rppb_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_ncsnf_TAG20160503T155917_cll0tmst_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=130, stamp=910886366, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0rygx_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0tnvl_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=131, stamp=910886371, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_nnnd1_DAILY_BACKUP_cll0s3jx_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_nnnd1_DAILY_BACKUP_cll0toyl_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=132, stamp=910886374, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_ncnn1_DAILY_BACKUP_cll0s8hx_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_ncnn1_DAILY_BACKUP_cll0tq09_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=133, stamp=910886378, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_nnsn1_DAILY_BACKUP_cll0sbwz_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_nnsn1_DAILY_BACKUP_cll0tr3q_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=134, stamp=910886381, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0sfp7_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_annnn_DAILY_BACKUP_cll0ts5x_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=135, stamp=910886383, piece=1
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece /fra/DEMO/autobackup/2016_05_03/o1_mf_s_910886383_cll0sjh2_.bkp
piece handle=/oraclebackupfileshare/DEMO/backupset/2016_05_03/o1_mf_ncsnf_TAG20160503T155943_cll0tt8c_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
Finished backup at 03-MAY-16

Starting Control File and SPFILE Autobackup at 03-MAY-16
piece handle=/fra/DEMO/autobackup/2016_05_03/o1_mf_s_910886428_cll0twwy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAY-16

RMAN> 

The more regular archivelog backup (scheduled on whatever frequency is desired is shown here)

CONFIGURE RETENTION POLICY TO recovery window of 28 days; 
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE ENCRYPTION FOR DATABASE ON;
BACKUP archivelog all;
delete noprompt obsolete;

Again lots of output but included for understanding

RMAN> CONFIGURE RETENTION POLICY TO recovery window of 28 days; 
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE ENCRYPTION FOR DATABASE ON;
BACKUP archivelog all;
delete noprompt obsolete;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS;
new RMAN configuration parameters are successfully stored


starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
old RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> 
Starting backup at 03-MAY-16
current log archived
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 12 to 36; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=37 RECID=26 STAMP=910887112
channel ORA_DISK_1: starting piece 1 at 03-MAY-16
channel ORA_DISK_1: finished piece 1 at 03-MAY-16
piece handle=/fra/DEMO/backupset/2016_05_03/o1_mf_annnn_TAG20160503T161153_cll1jb1r_.bkp tag=TAG20160503T161153 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-16

Starting Control File and SPFILE Autobackup at 03-MAY-16
piece handle=/fra/DEMO/autobackup/2016_05_03/o1_mf_s_910887116_cll1jdxm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAY-16

RMAN> 
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 28 days
using channel ORA_DISK_1
no obsolete backups found

RMAN> 
RMAN> 

So that's now all in place, i have local image copies in the fra no more than one day old plus backupsets and incremental backups in the CIFS fileshare.

Note also that the CIFS fileshare is georeplicated and available from any server i choose to mount it to so It's very well protected. The performance and reliabilty of the CIFS share so far seems good - that was my only real concern here - however there is not really an obvious alternative to pick from.

Note also that the backup script works fine even in the absence of an rman catalog (and just using the controlfile) - this is what we will use to back up the rman catalog itself.

Now that's all well and good you say but the real test is does the damn thing restore if shit hits the fan?

Well here is a test case to show that - i took a kind of worse case example where somehow the rman catalog (and its backups) are somehow gone and all we have is the backup files on the CIFS fileshare.

Here is the worked example through that:

First up we have to have an instance started on whatever server we are going to recreate the database on - this can be very basic - little more than the db name specified and then startup nomount.

Once that is there we attempt to restore the controlfile from the most recent backupset copy

RMAN> restore controlfile from '/oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031345_cl0spmcb_.bkp';

Starting restore at 27-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/27/2016 07:24:12
ORA-19870: error while restoring backup piece /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031345_cl0spmcb_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

OK - hope you got that wallet backed up somewhere as you are royally screwed without it......

So assuming you have the wallet and set sqlnet.ora up correctly you can now do this

SQL> alter system set encryption wallet open authenticated by "secret!";

System altered.

Now we try again

RMAN> restore controlfile from '/oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031345_cl0spmcb_.bkp';

Starting restore at 27-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oracle/DEMO/oradata/DEMO/controlfile/o1_mf_cl0yzr4r_.ctl
output file name=/oracle_recovery/DEMO/controlfile/o1_mf_cl0yzrl8_.ctl
Finished restore at 27-APR-16

Looking good - lets now mount the db

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

OK at this point we know about most backups as the controlfile contains them all - lets just make sure we have everything known though and catalog the backup area - output in small font again
 

RMAN> catalog start with '/oraclebackupfileshare/DEMO/';

searching for all files that match the pattern /oraclebackupfileshare/DEMO/

List of Files Unknown to the Database
=====================================
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0sp03v_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0sp8nm_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0spcv7_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0spk73_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnnf_TAG20160427T024454_cl0sp27w_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T023543_cl0soy1z_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T024316_cl0sp15o_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T024456_cl0sp39w_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T025207_cl0sp4d5_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030039_cl0sp5gm_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030220_cl0sp6jl_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030257_cl0sp9qk_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031134_cl0spg0q_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031336_cl0sph2y_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031345_cl0spmcb_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0soz1b_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0sp7lw_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spbsf_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spj51_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnndf_TAG20160427T031350_cl0sogwv_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnsn1_DAILY_BACKUP_cl0spdy1_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnsn1_DAILY_BACKUP_cl0splb3_.bkp
File Name: /oraclebackupfileshare/DEMO/ewallet.p12
File Name: /oraclebackupfileshare/DEMO/ewallet_2016042510265464_premasterkey.p12

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0sp03v_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0sp8nm_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0spcv7_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnn1_DAILY_BACKUP_cl0spk73_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncnnf_TAG20160427T024454_cl0sp27w_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T023543_cl0soy1z_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T024316_cl0sp15o_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T024456_cl0sp39w_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T025207_cl0sp4d5_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030039_cl0sp5gm_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030220_cl0sp6jl_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T030257_cl0sp9qk_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031134_cl0spg0q_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031336_cl0sph2y_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_ncsnf_TAG20160427T031345_cl0spmcb_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0soz1b_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0sp7lw_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spbsf_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spj51_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnndf_TAG20160427T031350_cl0sogwv_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnsn1_DAILY_BACKUP_cl0spdy1_.bkp
File Name: /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnsn1_DAILY_BACKUP_cl0splb3_.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /oraclebackupfileshare/DEMO/ewallet.p12
  RMAN-07517: Reason: The file header is corrupted
File Name: /oraclebackupfileshare/DEMO/ewallet_2016042510265464_premasterkey.p12
  RMAN-07517: Reason: The file header is corrupted

OK - we're now good to go - lets restore db

RMAN> restore database;

Starting restore at 27-APR-16
using channel ORA_DISK_1

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 /oracle/oradata/DEMO/datafile/o1_mf_system_ckn0ntfx_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/DEMO/datafile/o1_mf_sysaux_ckn0nybs_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/DEMO/datafile/o1_mf_sys_undo_ckn0o0bp_.dbf
channel ORA_DISK_1: reading from backup piece /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnndf_TAG20160427T031350_cl0sogwv_.bkp
channel ORA_DISK_1: piece handle=/oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnndf_TAG20160427T031350_cl0sogwv_.bkp tag=TAG20160427T031350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 27-APR-16

 OK great - now recover it

RMAN> recover database;

Starting recover at 27-APR-16
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: /oracle/DEMO/oradata/DEMO/datafile/o1_mf_system_cl0zjj2j_.dbf
destination for restore of datafile 00002: /oracle/DEMO/oradata/DEMO/datafile/o1_mf_sysaux_cl0zjj3l_.dbf
destination for restore of datafile 00003: /oracle/DEMO/oradata/DEMO/datafile/o1_mf_sys_undo_cl0zjj69_.dbf
channel ORA_DISK_1: reading from backup piece /oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spj51_.bkp
channel ORA_DISK_1: piece handle=/oraclebackupfileshare/DEMO/backupset/2016_04_27/o1_mf_nnnd1_DAILY_BACKUP_cl0spj51_.bkp tag=DAILY_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

unable to find archived log
archived log thread=1 sequence=24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/27/2016 08:55:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 24 and starting SCN of 374481

OK that's as far as we can get - the online redo was trashed from the original server in my worst case example so we have to do incomplete recovery and miss the last couple of logs. So in sqlplus:

SQL>  recover database until cancel using backup controlfile;
ORA-00279: change 374481 generated at 04/27/2016 03:13:40 needed for thread 1
ORA-00289: suggestion :
/fra/DEMO/archivelog/2016_04_27/o1_mf_1_24_%u_.arc
ORA-00280: change 374481 for thread 1 is in sequence #24


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open  resetlogs;

Database altered.

SQL>

And there we have it database restored (almost completely up to date from worst case scenario).

So some summary comments

1) Backup (and more importantly restore) seems to work well with this approach
2) ASO option is needed to encrypt backups/tablespaces in Azure (at least for our policies)
3) If you cant afford to lose any data you need dataguard or some other replication in place - for live systems we plan to do that
4) The wallet is really really important - do not forget about it or lose it and make sure it too is backed up somewhere
5) whatever strategy you do use make sure you test the damn thing - and test the worst case to make sure that works.

And if you got this far feedback would be great......



2 comments:

  1. Thank you Harrison, this is a very useful post. I'm consolidating 4 Stand-alone DBs into a Multi-tenant with DataGuard onto Azure.

    ReplyDelete