1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to take hot backup of oracle database

Discussion in 'Security, Backup and Recovery' started by nathanz, Mar 26, 2012.

  1. nathanz

    nathanz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    1: put the db in archive log mode

    2: set the db_sid to correct one

    3: login to sqlplus

    4: verify the name of the db that you are connected to

    select name from v$database;

    5: check if the db is in archive log made

    select log_mode from v$database;

    if not in archive log mode


    another command to check

    archive log list;

    6: find where on disk oracle writes archive log when it is in archive log mode

    sql> show parameter log_archive_dest_1;

    if the value is found to be 0, that means no values will be recorded, so we need to change it

    sql> alter system set log_archive_dest_1='LOCATION=c:\database\oradata\finance\archived_logs\'
    scope=spfile;

    7: shutdown immediate; < this is done just to prepare the db for hot backups >

    8: startup the db in mount mode

    startup mount;

    ( 3 startup types : nomount - just starts the instance, mount - locates the control files and open up according to the values, open - finds the datafiles from the control files and opens up the db )


    9: put the db in archive log mode

    alter database archivelog;

    10: open the database

    alter database open;

    11: check the status of the db

    select log_mode from v$database;

    SQL> archive log list;

    12: create a directory for archived log

    check if its empty, if empty we need to switch

    sql> alter system archive log current;

    run it 5 times < need to put / and enter > , then check the archive log dir , we will find files


    13: make a table in the database and insert data in it

    create table employees (fname varchar(2));

    check the table

    desc employees;

    insert values

    insert into employees values ('Mica');

    14: tablespace must be in hot backup mode

    check the status

    select * from v$backup;

    if found not active, then we need to change

    we cannot put the db in hot backup mode, unless it is archive log mode

    change to hot backup mode

    alter database begin backup;

    check the status

    select * from v$backup;

    15: now we can only COPY DBF FILES

    copy *dbf <distination location>

    16: need to take the db out to hot backup mode

    alter database end backup;

    17: need to make another archive log switch

    alter system archive log current;

    18: need to copy control files now, need to do a binary bckup

    alter database backup controlfile to '<location>\controlbackup';

    19: insert more values to the table

    insert into employess values ('NASH')

    COMMIT;

    make another archive log switch : alter system archive log current;

    do the same process for more values

    20 : backup all the archive logs to a new location

    21: shutdown the db and simulate a hw error, delete all the files from the database folder

    22: try to start the sqlplus and db ::: error

    23: copy all the backups to the db dir

    need to copy the control files, rename the binary backup of the control file and make the copies as needed

    24: try to mount the db, error < must use reset logs or noreset logs >

    25: need to do a recovering of the database

    shutdown

    restore the archive logs

    startup mount;

    recover database until cancel using backup controlfile;

    it will ask for a log file :

    yes for recovery

    cancel for cancelling recovery

    26: check status: open the database in readonly

    alter database open read only;

    check the tables to see the data


    shutdown immediate

    shartup mount;

    recover again : recover database until cancel using backup controlfile;

    if oracle is asking for a log that do nto exist , all we have to do is type cancel

    27: open the database

    alter database open;

    need to do reset logs

    alter database open resetlogs;

    28: check the db that you are connected, check the tables

    thanks and regards
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: How to take a Hot backup of Oracle database

    "24: try to mount the db, error < must use reset logs or noreset logs >"

    Incorrect; mounting a database won't throw that error. Opening the database after a restore and/or recovery will throw that error.

    "recover database until cancel using backup controlfile;

    it will ask for a log file :

    yes for recovery"

    YES is not a valid response for what Oracle is displaying. See below:

    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 775140 generated at 05/09/2007 07:00:38 needed for thread 1
    ORA-00289: suggestion :
    /u04/oracle/product/10.2.0/db_1/dbs/arch1_5_621627183.dbf
    ORA-00280: change 775140 for thread 1 is in sequence #5

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    Notice that one either hits the 'Return/Enter' key, supplies an alternate file name, instructs Oracle to auto generate the archivelog names to use for recover or cancels the recovery.

    "recover again : recover database until cancel using backup controlfile;

    if oracle is asking for a log that do nto exist , all we have to do is type cancel"

    Oracle asks for archivelogs; siimply because an archivelog does not exist doesn't mean you need to cancel recovery as you can still use the online redo logs. The following query will report the log file name and the sequence#:

    Code (SQL):
     
    SET linesize 150
    COLUMN member format a65

    SELECT  member, SEQUENCE#
    FROM v$log l JOIN v$logfile lf ON lf.GROUP# = l.GROUP#;
     
    From that list one should be able to supply the redo log name so that recovery can continue.

    For a task as important as this one the information presented should be correct and tested prior to being posted.
     
  3. Satyapal

    Satyapal Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    hello Users

    Just apply redo log in your case apply redo01.log(redo log location)
    i.e /u01/oradata/redo01.log

    With Regard's
    satyapal
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Whether or not Oracle is asking for that particular log or sequence? Oracle will know which sequence it needs for recovery and the query I provided will associate the online redo log with the sequence number so there will be no guesswork.