Pages

Monday, November 5, 2012

RMAN backup script [tested] Windows, Linux

*Works on Oracle 10g, 11g

 ###############################################################

 ## For Windows with separate log for every day of the week.

 ###############################################################

 -----------------------rman configuration-----------------------

 RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default PARALLELISM CAN BE > 1 ONLY FOR ENTERPRISE EDITION
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 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFBSCGH.ORA'; # default

 -----------------------rman script------------------------------

 file name: rman_backup.sql

 connect target 'system/password'
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag Full_database_open format 'E:\dbbackup_rman\db_%t_%s_p%p.dbf' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format 'E:\dbbackup_rman\al_%t_%s_p%p.dbf' delete all input;
delete noprompt obsolete redundancy = 1;
backup AS COMPRESSED BACKUPSET current controlfile tag =cf1 format 'E:\dbbackup_rman\cf_%t_%s_p%p.dbf';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

 -----------------------rman.bat---------------------------------
e:
cd E:\ora10g\BIN
rman @E:\dbbackup_rman\rman_backup.sql > E:\dbbackup_rman\log\rman_backup_%date:~0,3%.LOG

 ----------------------------------------------------------------

 How to create ".bat" file?
----------------------------------------------------------------



###############################################################

 ## For Linux with separate log for every day of the week.

 ###############################################################
#!/bin/bash
export ORACLE_SID=msats
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd /u01/app/oracle/product/10.2.0/db_1/bin
./rman  target 'system/password' log=/u03/rmanbackup/rmanlog.`date '+%a'` << EOF
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag whole_database_open format '/u03/rmanbackup/db_%t_%s_p%p' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/u03/rmanbackup/al_%t_%s_p%p' delete all input;
delete noprompt obsolete redundancy=1;
backup current controlfile tag =cf1 format '/u03/rmanbackup/cf_%t_%s_p%p';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;

 }
#exit;
EOF
----------------------- END -----------------------------------
Posted by at

4 comments:

  1. UnknownSeptember 24, 2015 at 1:29 AM

    First of all thanks. Your script is one of the simplest and to the point scripts I've ever found. Just one question cause am new to this things. What does %t ,%s and %p stands for in %t_%s_p%p.dbf?

    ReplyDelete
    Replies
    1. UnknownSeptember 24, 2015 at 1:35 AM

      Ok I found out the meaning of the variables. I didn't want to delete the comments for the thank you section.

      Delete
  2. UnknownSeptember 25, 2015 at 2:14 AM

    Sorry for bothering again but am hoping that you will be able to see this and answer me cause our dba is not answering any of my questions. If I want to restore all of these dbf files how I'm gonna do it. AM reading about RMAN the last 3-4 days but some things are way beyond me (am not an oracle specialist nor even a programmer) and I found that RECOVERY is one good option but I think I need the opinion of a specialist

    ReplyDelete
    Replies
    1. AzeSeptember 25, 2015 at 3:23 AM

      PF, Happy to see your comment.


      I could understand, how your DBA felt..! also how you felt..

      let me try answering you..

      How to restore the dbf files (database) from backup - is your question.

      **** First thing do not execute any of the restore command
      **** in the production servers, as this might bring
      **** disastrous effect, if not used properly.

      The steps are simple:

      For example, if you try it on your test machine, install same version of Oracle.

      cmd > dbca

      using DBCA,
      Create a dummy database with the same name as the backed-up database.
      (dont worry about data for now)
      make sure you place or choose the same location as in backed-up database for your dbf files.
      (while creating database using DBCA, you can edit the location)
      once the database is created, collect the results from below select statements from dummy database.

      SQL > select member from v$logfile;
      SQL > select name from v$datafile;
      SQL > select name from v$tempfile;
      SQL > select name from v$controfile;

      SQL > shutdown database.

      Delete/remove the datafiles, control files. (from dummy database)

      copy the init file (parameter file or pfile or check how to create pfile from spfile) to
      the same location as in prod (for easy first time practice)

      place the backup pieces into the same directory, as in production.
      Or you need to catalog those file: RMAN> catalog start with '/u03/rmanbackup/' noprompt;


      Windows : cmd> set ORACLE_SID=
      Linux : $ export ORACLE_SID=

      Windows : cmd > connect target /
      Linux : $ connect target /

      RMAN>

      RMAN> catalog start with '/u03/rmanbackup/' noprompt;

      RMAN> STARTUP NOMOUNT;

      RMAN> RESTORE CONTROLFILE FROM "";

      RMAN> ALTER DATABASE MOUNT;

      RMAN> RESTORE DATABASE;

      RMAN> RECOVER DATABASE;

      RMAN> ALTER DATABASE OPEN RESETLOGS;

      done.

      Delete

Subscribe to: Post Comments (Atom)