11g R2? too late to post anything for 11g? Wait, there are thousands of Businesses still using Oracle database 10g. So let us see how RMAN could be simplified on Windows. I hardly see many articles explaining such for Windows platform in general.

Considering you have 11g database already installed & setup for Archive logging, we’ll start by setting up RMAN backup first, then try a restore routine on a different sever.

Like cron jobs on Linux environment, for Windows we will use Task Scheduler for RMAN backups. Here are couple of batch files we will be using for making RMAN backup on Windows.

run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\RMAN_BACKUP\PROD_comp_%d_ lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT 'D:\RMAN_BACKUP\archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before 'sysdate-7';
backup current controlfile format 'D:\RMAN_BACKUP\bkpcontrol_file.ctl_%d_%T'               ;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

Save this script as “rman_backup.cmd”. You can save it with any other name ending with .cmd or .bat extension. Make sure you change the name in the next script incase if you choose a name other than “rman_backup.cmd”. Please note, I am taking backup on “D:\RMAN_BACKUP” folder, you should change “D:\RMAN_BACKUP” with the exact location that is used for the backup!

Now, we will create another cmd file that will call the above script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
:: echo %mydate%_%mytime%

set filename="D:\RMAN_BACKUP\logs\%mydate%_%mytime%_rman_backup.log"
:: echo %filename%

rman target / nocatalog cmdfile='D:\scripts\rman_backup.cmd' log='%filename%'

Once again you can save this file with any name, provided the extension is either .cmd or .bat. I saved this cmd file as “callrman.cmd”

Make sure the path ” D:\RMAN_BACKUP\logs” or equivalent exists prior running the scripts.

Now fire up Windows Task scheduler and create a basic job

That’s all. Everyday, at a said time, RMAN backups will be created and kept in the destination folder. We are using 7 days retention policy with the script file, so on the 8th day obsolete backups will be purged. Depending upon the disk space available, you should adjust the retention policy for your backups. Don’t forget to copy those backup pieces to an external medium to insure maximum availability during a crisis.

That completes the simplest form of Oracle RMAN backups on Windows platform.

RMAN Restore

Currently we are discussing about restoring the RMAN backup to same version of Oracle database. If you want to restore RMAN backup to a higher version of Oracle database, it will be an entirely different exercise.

RMAN backups are the fastest, easiest incase if you are doing it right. No hassles like creating tablespaces, schemas and then waiting hours for the import process to complete! The biggest size of the database I ever dealt with is 550GB and a server with 2×4 core processors, 16GB total memory restores the 140GB backup in less than 1.5 hours. I suggest you to start using RMAN backups the soonest possible regardless whether your database is just few megabytes in size.

Today we will see how to restore RMAN backup from Server A to Server B, ie, to a different physical server with a different database name.

It might sound bit unethical to ask you to create a new database using Oracle database creation wizard for this purpose, however this is what we will do for this exercise. This will save us some time figuring out setting up many other parameters for the new database at later stages.

So let us start.

Start DBCA and create a database with a database name that you prefer for the purpose, for eg: “ORCL”

Once the database created, shut it down & delete all the datafiles from the data store. For example, I had all my data files in the folder C:\oracle\oradata\orcl, where orcl is the database name!

Now copy the RMAN backups from Server A to Server B. I’ve copied only one day backups from Server A to Server B and make a note of the exact location where you copied the backup files. In my case, I copied the files to “C:\RMAN”

From an elevated command prompt start SQLPLUS as sysdba & start the database in non mounted mode.

Now, start another elevated command prompt and connect to RMAN in Auxiliary mode, for duplication of the database.

We are all set to restore the RMAN backup to Server B now. However, there are few things we have to insure to make a successful restoration. For example renaming the datafiles. Based on the new datastore location where you are restoring the database files, each data file should be renamed. Please refer the below sample script for the same.

run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "ORCL" backup location 'C:\RMAN' nofilenamecheck
db_file_name_convert=('D:\Oracle\oradata\PROD','C:\Oracle\oradata\orcl')
LOGFILE
GROUP 1 (
'C:\Oracle\oradata\orcl\redo01a.log',
'C:\Oracle\oradata\orcl\redo01b.log'
) SIZE 1000M ,
GROUP 2 (
'C:\Oracle\oradata\orcl\redo02a.log',
'C:\Oracle\oradata\orcl\redo02b.log'
) SIZE 1000M ,
GROUP 3 (
'C:\Oracle\oradata\orcl\redo03a.log',
'C:\Oracle\oradata\orcl\redo03b.log'
) SIZE 1000M ,
GROUP 4 (
'C:\Oracle\oradata\orcl\redo04a.log',
'C:\Oracle\oradata\orcl\redo04b.log'
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

As I am duplicating the database to existing one, I don’t have to go through the tedious process of changing the dbid or database name etc. Once the restore is done, all I need is to disable the archive logging and go online! We will see those in next few steps.

Please give attention to “db_file_name_convert“, Server A had the datafiles in the path “D:\oracle\oradata\PROD” and the Server B, we will be restoring the datafile to C:\oracle\ordata\orcl folder. So we must mention those changes here in the script. The same way we need to rename the logfiles and locations also. I hope the above script is self explanatory on that regard.

You may not need all those channel allocation for a small database, please adjust the number of cannels allocated as per your requirements. Give a try with less or more number of channels incase if you want to learn what happens :)

Execute the above script from the RMAN prompt.

If there are no errors, based on the size of the database & hardware resources RMAN will return to the prompt once the activities are finished.

Before doing anything else, we need to insure that the Archive logging is disabled for the newly restored database. Rush to the previous SQLPlus session that you started with nomount. This session is already disconnected, hence reconnect and shutdown immediate.

Now start the database in mount state and disable the archive logging.

Shutdown the database and restart normally.

That’s all, your Server B “orcl” has all the data from Server A “PROD” database now. If this is a production recovery, you can change the database name using “NID” utility, that is supported from Oracle database 9i onwards. Hope this helps few Oracle database beginners out there.

As we have restored full database with archive logs, there is no need to restore logs. Cross verify the database details

Cheers guys!