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:

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):

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):

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:

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.

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:

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.

All articles in this site are written by Jafar N.Aliyev. Reproducing of any article must be
followed by the author name and link to the site of origin(this site).
This site also keeps the same rules relative to the articles of other authors.