Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 15 May 2012

RMAN backup on a Windows server thruogh DBMS_SCHEDULING

This is single channel configuration on oracle 9i you can configure multi channel by simulating the below
rman setting. so if one fail default will work.
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:\oraback\%F'
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'H:\oraback\%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\oraback\SNCFSADHAN.ORA';

MY Consideration: There is two option either you set your default configuration thruogh command line
or in RCV file along with the backup script. Here in my case i already setup a default configuration and additionally configuring a setup thruogh rcv. It will give the priority to the additional setup and if this setup (rcv file) fails by any reason then your default setup will work. So any how your backup script will work
Weekly_BACKUP.RCV:
# Backup  Full database and Archivelog all (with Current Redolog) and delete all archive input from drive.
allocate channel for delete type disk;
# Crosscheck backups older than 15 days. If they are no longer on disk then delete that backup.
crosscheck backup completed before 'SYSDATE-15';
delete expired backup;
# lists backups that not needed to recover the database to within 15 days and delete those backup.
REPORT OBSOLETE RECOVERY WINDOW OF 15 DAYS;
Delete obsolete;
run {
allocate channel d1 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
backup
incremental level 0
tag Sadhan_Full_DBbackup
filesperset 8
FORMAT "H:\oraback\Weekly_%T_FULL_%d-%s_%p.db"
DATABASE
;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
archivelog all delete all input
tag Sadhan_Full_Archiveback
filesperset 5
format "H:\oraback\Weekly_%T_FULL_%d-%s_%p.arch"
;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}
# Reports which database files require backup because they have been affected by some NOLOGGING operation
REPORT UNRECOVERABLE;
# Reports which database files need to be backed up to meet a configured or specified retention policy
REPORT NEED BACKUP RECOVERY WINDOW OF 14 DAYS;
Then create a short OS script to launch the RMAN backup procces. This script will define the required variables which are already present in the OS session – just to make sure – and start the backup.
set ORACLE_HOME=D:\oracle
set ORACLE_SID=sadhan
RMAN TARGET / @D:\DBserver_Rman_Backup\Weekly_BACKUP.RCV
LOG=D:\DBserver_Rman_Backup\Logs\Weekly_BACKUP.LOG
exit 0
Now, create the Scheduler job. For this example, I made a job that launches Weekly on Friday 2 AM
exec DBMS_SCHEDULER.create_job ( -
job_name    => 'BACKUP_Name',    -
job_type    => 'EXECUTABLE',     -
job_action =>'D:\DBserver_Rman_Backup\Weekly_BACKUP.CMD \c',-
start_date    => trunc(SYSTIMESTAMP)+2/24, -
repeat_interval => 'Freq=Weekly;ByDay=FRI;ByHour=2;ByMinute=00',-
end_date    => NULL,   -
enabled    => TRUE,    -
comments    => 'Weekly Full backup script on Friday 2 AM');
NOTE: Please try or run the script on test server before scheduling.

8 comments:

  1. Wow, superb blog layout! How long have you been blogging for?

    you made blogging look easy. The overall look of your site is fantastic,
    as well as the content!
    Also see my web site > exchange server 2003 Recovery database

    ReplyDelete
  2. Thanks dear, thanks for appreciation. I maintain this blog for helping myself while in work. Once i faces problem while working on different location then i decide to upload all of my notes and related theory at one place so i can use easily use.
    I gone through your blog the contents is useful for my admin or other windows user..so i am attaching it into my blog,,

    ReplyDelete
  3. Hi Shahin, great blog, pls let me know if you tested this on Oracle 11.2g? Is it working on this version database?

    ReplyDelete
    Replies
    1. All most every thing in this blog is tried or tested on 9i/10g database. You can try on higher version. I think it will work.

      Delete
  4. I am trying, with Yr solution too but without positive result.
    I don't know where is problem.
    RCV file is it necessary or can be txt or sql file

    ReplyDelete
    Replies
    1. Create a cmd file: Weekly_Backup.cmd by putting target details as:
      set ORACLE_HOME=D:\oracle; set ORACLE_SID=?
      RMAN TARGET / @D:\DBserver_Rman_Backup\Weekly_BACKUP.RCV
      LOG=D:\DBserver_Rman_Backup\Logs\Weekly_BACKUP.LOG

      and use this cmd file in Job action as:
      job_action =>'D:\DBserver_Rman_Backup\Weekly_BACKUP.CMD

      Delete
  5. bhai your blog is very good

    ReplyDelete
  6. if you dont mind can send the all detail about cron tab how to schedule

    ReplyDelete