Creation date: 28 January, 2017

RMAN as 1-2-3

This instruction is divided into two parts. The first part gives a ready to run instruction for backup procedure. The second part is for the person, who wants to penetrate into RMAN deeper, than an ordinary user. Note, that this instruction and examples targeted to Oracle 10g and 11g in Windows platform.

Backup database in easy way

If you want to back up your database without drilling into RMAN difficulties here is an easy way to do it.  To backup database with RMAN without catalog:

C:\>rman target system/manager@store

Here target means your backing up database. It’ll get you into RMAN console. Then run the following command:

RMAN> backup database;

That’s all. You did it. It’ll run without any problem if you have a small database. But for a bit larger than tiny databases most probably, that you’ll get the following exception:

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/13/2017 12:32:06
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 1739587584 limit

The numbers about disk space and limit may differ. This means that the space available in DB_RECOVERY_FILE_DEST_SIZE is not enough to complete the backup procedure. RMAN writes all backups into flash_recovery_area (for Oracle 10g) or fast_recovery_area (Oracle 11g). In my notebook it is inside C:\oracle\product\10.2.0\ folder. For now I just give you a solution, with small explanation.

Log into SQL Plus as SYSTEM or SYS user and issue the following command to increase DB_RECOVERY_FILE_DEST_SIZE up to 20GB (or 30GB if you have enough space on your disk):

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=30G SCOPE=BOTH;

U can see the actual size and used size with this query:

SQL> SELECT name, ceil(space_limit/1024/1024/1024) SIZE_GB, ceil(space_used/1024/1024/1024) USED_G FROM v$recovery_file_dest;

Now go to RMAN and run the backup database command again.

If you run database in ARCHIVELOG mode, and want to get all archive logs cleaned after the successful backup, issue the following command:

RMAN> backup database include current controlfile archivelog all delete input;

This command backs up your database, Control file, Server Parameter file (SPFILE) and archived redo logs. After backup complete RMAN deletes all ARCHIVELOGS disregarding ARCHIVELOG DELETION POLICY, if you have the one (will be explained later), because of ALL DELETE INPUT option.

Now you can delete all previous backup sets. If you don’t delete archived redo logs, this command will delete them too. Because of NOPROMPT parameter it’ll not ask you whether to delete every entry.

RMAN> delete noprompt obsolete;

But it has caveats. As I said It deletes all archived logs too. If you lose your backup set or it gets corrupted, you will lose everything, including your archived logs. Therefore never use this method in production. Read the detailed instruction.

 Detailed backup procedure with RMAN

This part also explains backing up a database with RMAN without using a catalog database, but in more detail. So run RMAN (my database service iname is STORE):

C:\>rman target system/manager@store

First of all check RMAN current parameters.

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFJEFF.ORA'; # default

As U see, that by default CONTROLFILE AUTOBACKUP is OFF. Change it to ON. Even without this parameter Oracle backs up the Control file too. But RMAN backs it up during the main backup process. After backup completes RMAN writes information about latest backup into the Config file. So the latest changes will not be backed up. But this parameter forces to launch the new RMAN process and creates the new file in AUTOBACKUP catalog especially for the last Control File.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Then configure how many backup datasets you want to keep in RETENTION POLICY TO REDUNDANCY. By default there is only 1 backup sets. Change it at least to 2. U may get your latest backup corrupted. In this case you can restore your database from previous backup set applying all archived redo logs, generated  after that set.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

You see that ARCHIVELOG DELETION POLICY is NONE. Because I set retention policy to REDUNDANCY 2, I also make corresponding changes in ARCHIVELOG deletion policy. I set it exactly the same number as RETENTION POLICY TO REDUNDANCY (in my case 2). Even if you set it a larger value RMAN will not keep them. Because you have only last two backup sets the older archived logs won’t help you. You only need archived logs made after the time of the backup. Note, this option is added from Oracle 11g. In 10 you can’t set this kind of deletion policy for archived redo logs.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

Now during backup process on DELETE INPUT, it deletes 2 times backed up archived logs. The DELETE OBSOLETE also deletes backup sets, remaining only the last two and obsolete archived logs. Be careful if you run the command, given in the first part of this article it’ll delete all archived logs:

RMAN> backup database include current controlfile archivelog all delete input; # don’t do it

Because you write ALL DELETE INPUT, it’ll delete all the archived logs, disregarding your policy. Therefore in our case this command is not acceptable. Instead run the following command:

RMAN> backup database include current controlfile plus archivelog delete input;

This following command does what I need. It runs in following manner:

1. Before starting the backup RMAN takes the snapshot of Control file. Because Control file does not use UNDO tablespace this snapshot provides read consistency of this file during backup process.
2. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
3. Runs BACKUP ARCHIVELOG ALL. Note that if backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.
4. Backs up the rest of the files specified in BACKUP command.
5. Backs up current control file (because of INCLUDE CURRENT CONTROLFILE)
6. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
7. Backs up any remaining archived logs generated during the backup.
8. Makes corresponding changes into Control File about the latest backup (because CONFIGURE CONTROLFILE AUTOBACKUP ON)
9. Backs up Control File and SPFILE

Now we can delete obsolete backup sets too, remaining the last two, as set in retention policy. Note, that if we omit DELETE INPUT during BACKUP DATABASE the following command will delete obsolete archived logs too.

RMAN> DELETE NOPROMPT OBSOLETE;

So it deletes backup sets, archive logs and backed up archive logs according to REDUNDANCY. Without NOPROMPT it'll ask you whether to delete. For batch job we use NOPROMPT option.

If U manually deleted some bacup files with operating system command, RMAN will give a warning message. Then  you must crosscheck to inform RMAN about it:

RMAN> crosscheck backup;

Now on the next DELETE OBSOLETE, It'll delete manually deleted files from RMAN repository.

Restore the database with RMAN

RMAN> SQL 'startup mount';
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

If you use incomplete restore, or restore in past time, then you have to open database in RESETLOGS mode:

RMAN> SQL 'alter database open resetlogs';

RMAN cheat sheet

There are some useful commands in this cheat sheet. Note, that all RMAN commands start with RMAN> and SQL*Plus console commands start with SQL> prompts.
Show all RMAN parameters:

RMAN> show all;

Show obsolete BACKUP sets, ARCHIVELOGS, etc. that was not deleted yet:

RMAN> report obsolete;

All backed up Control files:

RMAN> list backup of controlfile;

All backed up archived logs:

RMAN> list backup of archivelog all;

To see expired archive logs according to the ARCHIVELOG DELETION POLICY:

RMAN> list expired archivelog all;

Switches logs and archives the current log. Note that we use SQL command because we run it inside of RMAN. In Oracle 12c you can run some SQL Plus commands directly from RMAN.

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

Deletes archived logs:

RMAN> delete archivelogs;

To change FRA (flash_recovery_area in 10g/fast_recovery_area in 11g) location:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'G:/Oracle/flash_recovery_area' SCOPE=BOTH;

Some backup information

Here are the commands, to get some backup information.
To see what needs to be backed up:

RMAN> report need backup;

List backups:

RMAN> list backup;
RMAN> list backup summary;
RMAN> list backup of database;
RMAN> list backupset;
RMAN> list copy of database;

List archived logs:

RMAN> list archivelog all;

RMAN checking commands

RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

RMAN deletion commands

Delete all obsolete backups after confirmation one by one:

RMAN> delete obsolete;

Delete all obsolete backups without confirmation (silent mode):

RMAN> delete noprompt obsolete;

Delete archived logs:

RMAN> delete archivelog all;

Delete copy and backup:

RMAN> delete copy;
RMAN> delete backup;

Delete expired backups, run after CROSSCHECK:

RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE EXPIRED COPY;

Delete backup set with the specified id:

RMAN> delete backupset 20;

Enable flashback

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database flashback on;
SQL> alter database open;

Enable ARCHIVELOG mode

If your database is not in ARCHIVELOG mode do the following:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> shutdown immediate;
SQL> startup;

Manage Archived logs

To create the second place to duplicate archive logs:

SQL> alter system set LOG_ARCHIVE_DEST_2='location=D:\oracle\Jeff\fast_recovery_area\STORE\archive' SCOPE=BOTH;

To create the second place for archivelog in FRA:

SQL> alter system set LOG_ARCHIVE_DEST_10='location=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

To make it mandatory write MANDATORY, by default all destinations are OPTIONAL.

SQL> alter system set LOG_ARCHIVE_DEST_10='location=USE_DB_RECOVERY_FILE_DEST MANDATORY' SCOPE=BOTH;

To see all archive log destinations with MANDATORY/OPTIONAL binding types:

SQL> select dest_name, status, binding from v$archive_dest;

To see logfile states:

SQL> select * from v$log;

You can set minimum necessary quantity of archive log destinations. Without archiving into all of these destinations Oracle will not switch the logfile.

SQL> alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=2;

To read this parameter:

SQL> select name, value from v$parameter where name='log_archive_min_succeed_dest';

If the value of this parameter is less than the number of enabled, valid MANDATORY destinations, this parameter is ignored in favor of the MANDATORY destination count. If this parameter is greater than count of valid MANDATORY and OPTIONAL destinations together, all destinations are treated mandatory.

Duplicate online redo logs

To create the second member for redo log groups:

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\Jeff\fast_recovery_area\STORE\REDO01B.LOG' to GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\Jeff\fast_recovery_area\STORE\REDO02B.LOG' to GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\Jeff\fast_recovery_area\STORE\REDO03B.LOG' to GROUP 3;

Now look newly created log group members:

SQL> select group#, status, type, member from v$logfile;

You’ll see, that statuses of all new members are INVALID. This is normal and it will change to active (blank) when it is first used.

Manage Control files

To duplicate or move control files into different locations first run in SQL Plus:

SQL> alter system set control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENT\CONTROL01.CTL', 'F:\FullDumpAgent\CONTROL02.CTL', 'G:\Oracle\flash_recovery_area\AGENT\CONTROL03.CTL' scope=spfile;

Then shutdown database and move other control files into corresponding places. Note, that we only set scope in spfile. Because , when we change the location database is open. Startup database and check the location.

SQL> select name from v$controlfile;

Some essential views

All below views run from SQL*Plus. Therefore I don’t write SQL> in front of them.

select * from v$database;

Get location of FRA:

select * from v$recovery_file_dest;

Get used volume of FRA:

select * from v$flash_recovery_area_usage;

Read backup information:

select * from v$rman_backup_job_details;
select * from v$rman_backup_subjob_details;
select * from v$backup_files;
select * from v$backup_device;
select * from v$backup_set;
select * from v$backup_piece;
select * from v$backup_redolog;
select * from v$backup_spfile;
select * from v$backup_corruption;
select * from v$copy_corruption;
select * from v$rman_configuration;
select * from v$rman_backup_type;
select * from v$rman_status;
select * from v$archived_log;