Wednesday, March 6, 2013

RMAN crash course

It has been a long time since I posted here! In my previous job (this is my second week in my new one) I didn't have so much chance to keep posting because my position didn't involve neither UNIX server administration nor database administration, but I hope this new job will give me plenty of material to write interesting posts.

So let's begin with something simple, backing up an Oracle database with RMAN. Let's say that you're learning database management or you have a small and hopefully non-critical database that you want to backup, and you don't mind using the Flash Recovery Area; the directory used as FRA is set by the db_recovery_file_dest parameter in the database. First of all, you have to set the Oracle environment variables needed to connect to your selected database:

oracle@olimpo:~/rman$ set|grep ORACLE
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/server
ORACLE_SID=orcl11g

Then you can run RMAN to connect to the current target database (orcl11g):

oracle@olimpo:~/rman$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 16:53:00 2013

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

connected to target database: ORCL11G (DBID=915822427, not open)

You might have noticed that my database is not opened, that's because it has no archive logs so in order to make a consistent backup I put it in mount state. If this is the first time you launch RMAN then it would be better to check its parameters:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL11G 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 OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
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 '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default

For example, you might want to check the compression algorithm and retention policy (how many backups you want to keep at the same time); if you want to change something you just have to issue the command the same way it's shown:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
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 OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
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 '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default

Then if you're ready to backup your database issue this simple backup command:

RMAN> backup check logical database;

Starting backup at 05-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/orcl11g/system01.dbf
input datafile file number=00002 name=/opt/oracle/oradata/orcl11g/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/orcl11g/example01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/orcl11g/undotbs01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/orcl11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-13

This way you can backup a database with no archive logs enabled; if you have a database with archive logs enabled then append the plus archivelog subclause at the end of the backup command. Also, the check logical subclause is for checking logical errors in the database while performing the backup, the command for backing up a database can be as simple as backup database.

Now, if you want to check that your backup is really there you can do it this way:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        05-MAR-13       1       1       NO         TAG20130305T165413
2       B  F  A DISK        05-MAR-13       1       1       NO         TAG20130305T165413

But wait, why there are two backups instead of one? Maybe a detailed list would show us why:

RMAN> list backup;

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.00G      DISK        00:01:15     05-MAR-13      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130305T165413
        Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/system01.dbf
  2       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/sysaux01.dbf
  3       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/undotbs01.dbf
  4       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/users01.dbf
  5       Full 854520     05-MAR-13 /opt/oracle/oradata/orcl11g/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:02     05-MAR-13      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20130305T165413
        Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp
  SPFILE Included: Modification time: 05-MAR-13
  SPFILE db_unique_name: ORCL11G
  Control File Included: Ckp SCN: 854520       Ckp time: 05-MAR-13

The first backup set is the full backup of the database, and the second one is the backup of the database's SPFILE, made by default. And before exiting from RMAN and in order to not fill up the FRA (its size set in the db_recovery_file_dest_size parameter), let's delete the obsolete backups, that is, backups no longer needed according to the retention policy:

RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
no obsolete backups found

RMAN> quit

Recovery Manager complete.

More information:

Getting Started with RMAN
Oracle Database Backup and Recovery Reference (Backup)

No comments:

Post a Comment