User Managed Oracle Database Backup Script for Windows platform

Script for Oracle Database User Managed Backup

Script is used to take the oracle backup with method User Managed Backup. In User Managed Backup is hot backup taken at Operating system level. You can freeze the changes with alter database begin backup command in oracle, Copy paste the oradata folder in other location, then change free scn with alter database end backup command.

General steps for manual:

1. Login with the sysdba privileges to the database

SQLPLUS sys as sysdba

2. Start the backup process with following command:

ALTER DATABASE BEGIN BACKUP;

3. Copy all the data files present in the database with OS command.

4. Stop the backup process with following command

ALTER DATABASE END BACKUP;

Script:

set feedback off pagesize 0 heading off verify off linesize 200 trimspool on
define dir ='F:\oracle\BACKUP\'
define fil='F:\oracle\BACKUP\open_backup_commands.sql'
define spo ='F:\oracle\BACKUP\open_backup_ouput.lst'
define archive_backup_dir='F:\oracle\BACKUP\Archive_BACKUP\'
prompt **** Spooling to &fil *****
set serveroutput on
spool &fil
prompt set feedback on pagesize 0 heading on verify on
prompt spool &spo
prompt prompt Creating directory
select 'host mkdir &dir'||to_char(sysdate,'YYYYMMDD') from dual;
--prompt prompt Switching logfile
alter system switch logfile;
prompt prompt database in backup mode
prompt alter database begin backup;;
prompt prompt Datafiles copy has been started
select 'host copy '|| file_name ||' &dir'||to_char(sysdate,'YYYYMMDD') from dba_data_files order by tablespace_name;
prompt prompt Datafiles copy has been completed
prompt alter database end backup;;
prompt prompt Database backup mode ended
prompt prompt Controlfile backup
select 'alter database backup controlfile to trace as ''&dir'||to_char(sysdate,'YYYYMMDD')||'\Ctl_Backup.ctl'';' from dual;
select  'host copy '||decode((select value from v$parameter where name='spfile'),null,(select value from v$parameter where name='pfile'),(select value from v$parameter where name='spfile'))||' &dir'||to_char(sysdate,'YYYYMMDD') from dual;

select unique 'host mkdir &archive_backup_dir'||to_char(ar.completion_time,'YYYY_MM_DD') from v$archived_log ar,(select max(actual_start_date) last_start_date from dba_scheduler_job_run_details where job_name='BACKUP_JOB' and status='SUCCEEDED') jb where  to_char(ar.completion_time,'YYYYMMDDHH24MISS')>to_char(jb.last_start_date,'YYYYMMDDHH24MISS') and to_char(ar.completion_time,'YYYYMMDDHH24MISS')to_char(jb.last_start_date,'YYYYMMDDHH24MISS') and to_char(ar.completion_time,'YYYYMMDDHH24MISS')<=to_char(sysdate,'YYYYMMDDHH24MISS') and ar.name is not null;

prompt prompt Database backup completed successfully
prompt spool off;;
spool off;
@&fil
exit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.