Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Script to take User Managed Hot Backup for an Oracle Database

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Script to take User Managed Hot Backup for an Oracle Database


    In this case the backup location is set to /u01/orabkp/
    BEFORE Creating the HotBkp procedure, set the backup location in the procedure.


    1 . CREATE THE HOTBKP PROCEDURE IN THE DATABASE FOR WHICH HOT BACKUP IS TO BE TAKEN
    -------------------------------------------------------------------------------------------------------

    create or replace procedure HotBkp
    is

    /* cursor to capture Tablespaces that it will be put in the Backup, accessing Temporary Tablespaces Managed Locally */
    cursor cTbs is
    select ts# tId, name tName
    from v$tablespace
    where ts# not in (select distinct ts# from v$tempfile);

    /* Cursor to capture Data Files that it will be put in the Backup */
    cursor cDtfl is
    select ts# tId, name dName, substr(name,
    instr(name,decode('\',NULL,'/','\'),-1)+1,
    instr(name,'.',-1)-instr(name,decode('\',NULL,'/','\'),-1)-1) DestName
    from v$datafile;

    /* Default backup directory. It should be compatible with your environment. */
    CBkpDir constant varchar2(100):='/u01/orabkp/';

    /* Default extension's backup files */
    CBkpExt constant varchar2(4):='.bkp';

    /* Default extension's control file logical backup */
    CBkpCtflExt constant varchar2(4):='.sql';

    /* Files name generated throught Logical Backup and Control Files's Backup Online */
    CBkpCtfl constant varchar2(7):='control';

    VSpid varchar2(100);

    VUdump varchar2(100);

    begin
    dbms_output.enable(50000);

    dbms_output.put_line('spool '||CBkpDir||'hotbkp.log');

    /* Capture the start hour of Backup Online execution */
    dbms_output.put_line('select to_char(sysdate, ''dd/mm/rrrr hh24:mi:ss'')
    Start_Time'||chr(10)||
    'from dual;');

    /* Starting the LOOP to take a Tablespaces's Backup Online */
    for rTbs in cTbs loop

    dbms_output.put_line('alter tablespace '||rTbs.tName||' begin backup;');
    for rDtfl in cDtfl loop

    if (rDtfl.tId=rTbs.tId) then
    dbms_output.put_line('HOST cp '||rDtfl.dName||' '||CBkpDir);
    end if;
    end loop;

    dbms_output.put_line('alter tablespace '||rTbs.tName||' end backup;');

    dbms_output.put_line('alter system checkpoint;');

    end loop;

    dbms_output.put_line('alter database backup controlfile to trace as '''||CBkpDir||'controlfile_bkp.trc'||''';');
    dbms_output.put_line('alter database backup controlfile to '''||CBkpDir||CBkpCtfl||CBkpExt||''';');


    dbms_output.put_line('alter system archive log current;');

    /* Capture the end hour of Backup Online execution */
    dbms_output.put_line('select to_char(sysdate, ''dd/mm/rrrr hh24:mi:ss'')
    End_Time'||chr(10)||
    'from dual;');

    /* Closing the log */
    dbms_output.put_line('spool off');

    end;
    /


    2. SPOOL OUTPUT TO A SQL FILE and EXEC THE HOTBKP PROCEDURE
    =================================================
    USAGE :

    conn / as sysdba
    set feed off
    set serveroutput on
    set linesize 300
    spool $HOME/hotbkp.sql
    exec HotBkp;
    spool off
    set feed on
    exit;

    3. Check the script $HOME/hotbkp.sql .
    This script will be now be used to take a hot backup, after creating the respective Directory to take a hot backup

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Create the Directory where Hot Backup is to be taken
    mkdir /u01/orabkp/

    Take the Backup

    SQL> spool hotbkp.log
    SQL> @hotbkp.sql
    SP2-0734: unknown command beginning "SQL> exec ..." - rest of line ignored.

    START_TIME
    -------------------
    01/01/2012 15:44:51


    Tablespace altered.



    Tablespace altered.


    System altered.


    Tablespace altered.



    Tablespace altered.


    System altered.


    Tablespace altered.



    Tablespace altered.


    System altered.


    Tablespace altered.



    Tablespace altered.


    System altered.


    Database altered.


    Database altered.


    System altered.


    END_TIME
    -------------------
    01/01/2012 15:46:28

    SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
    SQL>

+ Reply to Thread

Similar Threads

  1. Dynamic Script to take Hot Backup of Oracle Database
    By Hemant in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 10
    Last Post: 03-14-2012, 11:40 PM
  2. Replies: 0
    Last Post: 01-10-2012, 06:22 PM
  3. Loss of Non-System datafile in a Database (User Managed Recovery)
    By ajaysingh in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 1
    Last Post: 11-01-2011, 03:35 PM
  4. Loss of system datafile in Database (User Managed Recovery)
    By ajaysingh in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 11-01-2011, 11:54 AM
  5. Loss of all controlfiles of the Database (User Managed Recovery)
    By ajaysingh in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 11-01-2011, 11:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics