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

Thread: Oracle Database Manual Backup and Recovery.

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Oracle Database Manual Backup and Recovery.

    COLD BACKUP AND RECOVERY SCENARIOS.

    Cold backup of Database in Noarchivelog Mode.

    1) analyze the space requirements.


    SQL> select sum(sum_bytes)/1024/11024 m_bytes
    2 from (
    3 select sum(bytes) sum_bytes from v$datafile
    4 union
    5 select sum(bytes) sum_bytes from v$tempfile
    6 union
    7 select (sum(bytes) * members) sum_bytes from v$log
    8 group by members);

    2) Determine location and names of files to be backed up.
    SQL> select name from v$datafile
    union
    select name from v$controlfile
    union
    select name from v$tempfile
    union
    select member from v$logfile;



    3) shutdown database

    SQL> shutdown immediate;

    4) Do OS level backup

    Note: I have also backed up the Online redo Log files.


    cp -ivr /u01/app/oracle/oradata/orcl/* /u02/app/oracle/backup/


    SQL> startup mount

    Do binary backup of controlfile.

    SQL> alter database backup controlfile to '/u02/app/oracle
    /controlbackup/control.bkp';

    Database altered.

    Or trace file backup

    SQL> alter database backup controlfile to trace as '/u02/app
    /oracle/controlback/controltrace';

    SQL> alter database open;



    Restoring from cold backup in Noarchivelog.

    a) when Online redo logs backup is there.


    Shutdown your database if its open.

    Remove the old files

    restore files from backup

    $ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


    startup up your database

    SQL> startup


    b) when Online redo logs backup is not there.


    Shutdown your database if its open.


    restore files from backup

    $ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


    startup up your database

    SQL> startup mount

    SQL> recover database using backup controlfile until cancel;

    when prompted

    cancel

    SQL> alter database open resetlogs;

    This command will clear and recreate the online redo log files
    and also will be a new incarnation of the database.

    That means old archive logs if any cannot
    be used for the purpose of recovery.

    So its importatnt that you make a FRESH BACKUP of your database.


    HOT BACKUP AND RECOVERY SCENARIOS.

    BACKUP

    1) Make sure that your database is in archivelog mode.

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled


    2) analyze space requirements.

    SQL> select sum(sum_bytes)/1024/11024 m_bytes
    from (
    select sum(bytes) sum_bytes from v$datafile
    union
    select sum(bytes) sum_bytes from v$tempfile
    union
    select (sum(bytes) * members) sum_bytes from v$log
    group by members);


    3) Determine the files that need backup;

    SQL> select name from v$datafile
    union
    select name from v$controlfile
    union
    select name from v$tempfile;


    4) check max sequence from online redo logfiles.
    SQL> select max(sequence#)
    from v$log;

    MAX(SEQUENCE#)
    --------------
    7

    5) Put the database or tablespace in begin backup mode.

    For database

    SQL> alter database begin backup;

    For Tablespace

    SQL> alter tablespace users begin backup;


    SQL> select file#,status from v$backup;

    FILE# STATUS
    ---------- ------------------
    1 ACTIVE
    2 ACTIVE
    3 ACTIVE
    4 ACTIVE
    5 ACTIVE


    6) Copy all the datafiles with OS utility.

    7) Take the database or tablespace out of backup mode.

    For database

    SQL> alter database end backup;

    For tablespace

    SQL> alter tablespace users end backup;


    SQL> select file#,status from v$backup;

    FILE# STATUS
    ---------- ------------------
    1 NOT ACTIVE
    2 NOT ACTIVE
    3 NOT ACTIVE
    4 NOT ACTIVE
    5 NOT ACTIVE



    8) archive current logfile and check latest max sequence
    number.

    SQL> alter system archive log current;

    System altered.

    SQL> select max(sequence#) from v$log;

    MAX(SEQUENCE#)
    --------------
    19


    9) backup the controlfile

    Binary backup

    SQL> alter database backup controlfile to '/u01/app/oracle
    /backup/control.bk';

    Database altered.

    Trace backup

    SQL> alter database backup controlfile to trace as '/u01/app
    /oracle/backup/trace';

    Database altered.

    10) backup all the archived logs generated during backup7
    identifying them by log sequence number.





    Recovery Scenarios when database is in archivelog with hot backup.


    Case 1: Lost all the controlfiles.

    shutdown the database if its open.

    SQL> shutdown abort

    Copy the controlfiles from backup.


    SQL>startup mount

    SQL> recover database using backup controlfile until cancel;

    ORA-00279: change 808116 generated at 01/23/2012 23:55:11
    needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_12_%u_.arc
    ORA-00280: change 808116 for thread 1 is in sequence #12





    Open another terminal and identify the online redo log file
    for the sequence number.

    SQL> select * from v$logfile;

    GROUP# STATUS TYPE
    ---------- ------- -------
    MEMBER
    -------------------------------------------------------------
    IS_
    ---
    3 ONLINE
    /u01/app/oracle/oradata/orcl/redo03.log
    NO

    2 ONLINE
    /u01/app/oracle/oradata/orcl/redo02.log
    NO

    GROUP# STATUS TYPE
    ---------- ------- -------
    MEMBER
    --------------------------------------------------------
    IS_
    ---

    1 ONLINE
    /u01/app/oracle/oradata/orcl/redo01.log
    NO


    SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
    ---------- ---------- ---------- ---------- ---------- ---------- ---
    STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
    ---------------- ------------- --------- ------------ ---------
    1 1 10 52428800 512 1 YES
    INACTIVE 807834 23-JAN-12 807837 23-JAN-12

    3 1 12 52428800 512 1 NO
    CURRENT 808116 23-JAN-12 2.8147E+14


    2 1 11 52428800 512 1 YES
    ACTIVE 807837 23-JAN-12 808116 23-JAN-12


    SQL>


    apply the relevant path.

    Specify log: {=suggested | filename | AUTO | CANCEL}
    /u01/app/oracle/oradata/orcl/redo03.log
    Log applied.
    Media recovery complete.

    In some cases querying v$log is not helpful as it does
    not show the correct sequence number, In that case apply
    the online logfiles one by one until media recovery is complete.

    SQL> alter database open resetlogs;

    Database altered.



    CASE 2: Lost System Datafile

    SQL> shutdown abort;
    ORACLE instance shut down.


    Copy the system datafile from backup.


    SQL> startup mount

    SQL> recover tablespace system;
    ORA-00279: change 807565 generated at 01/24/2012 01:57:07
    needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_11_7kvk24yl_.arc
    ORA-00280: change 807565 for thread 1 is in sequence #11


    Specify log: {=suggested | filename | AUTO | CANCEL}

    auto

    Log applied.
    Media recovery complete.

    SQL> alter database open;

    Database altered.

    CASE 3: Lost Non-System Datafiles.

    Example: Users tablespace lost
    due to media failure.


    Take the tablespace offline.

    SQL> alter tablespace users offline;
    alter tablespace users offline
    *
    ERROR at line 1:
    ORA-01191: file 4 is already offline - cannot do a normal offline
    ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


    SQL> alter tablespace users offline immediate;

    Tablespace altered.

    Copy apprpriate datafiles from backup for users tablespace.

    SQL> select file_name from dba_data_files where
    tablespace_name='USERS';

    FILE_NAME
    ---------------------------------------------------
    /u01/app/oracle/oradata/orcl/users01.dbf


    SQL> recover tablespace users;

    ORA-00279: change 807565 generated at 01/24/2012
    01:57:07 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_11_7kvk24yl_.arc
    ORA-00280: change 807565 for thread 1 is in sequence #11


    Specify log: {=suggested | filename | AUTO | CANCEL}

    auto

    Log applied.
    Media recovery complete.

    SQL> alter tablespace users online;

    Tablespace altered.

    SQL> select file_name,status from dba_data_files
    where tablespace_name='USERS';

    FILE_NAME
    ------------------------------------------------------
    STATUS
    ---------
    /u01/app/oracle/oradata/orcl/users01.dbf
    AVAILABLE

    CASE 4: A added data file was lost after hot backup.

    Example: Lost test tablespace


    SQL> alter tablespace test offline;

    Tablespace altered.

    SQL> alter database create datafile '/u01/app/oracle
    /oradata/orcl/test01.dbf';

    Database altered.

    SQL> alter database create datafile '/u01/app/oracle
    /oradata/orcl/test02.dbf';

    Database altered.

    SQL> recover tablespace test;
    Media recovery complete.
    SQL> alter tablespace test online;

    Tablespace altered.
    SQL> select file_name,status from dba_data_files where
    tablespace_name='TEST';

    FILE_NAME
    --------------------------------------------------------
    STATUS
    ---------
    /u01/app/oracle/oradata/orcl/test01.dbf
    AVAILABLE

    /u01/app/oracle/oradata/orcl/test02.dbf
    AVAILABLE




    CASE 5: Datafile lost and needs to be restored to
    different location.


    Example: lost my users tablespace.

    SQL> alter tablespace users offline;

    Tablespace altered.

    SQL> select file_name from dba_data_files where
    tablespace_name='USERS';

    FILE_NAME
    -------------------------------------------------------------
    /u01/app/oracle/oradata/orcl/users01.dbf


    Copy appropriate data files from backup to new location.

    Rename datafiles.

    1 alter database rename file '/u01/app/oracle/oradata
    /orcl/users01.dbf'
    2 to
    3* '/u01/app/oracle/oradata/users01.dbf'


    SQL> recover tablespace users;
    ORA-00279: change 807565 generated at 01/24/2012 01:57:07
    needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_11_7kvk24yl_.arc
    ORA-00280: change 807565 for thread 1 is in sequence #11


    Specify log: {=suggested | filename | AUTO | CANCEL}

    auto


    Log applied.
    Media recovery complete.

    SQL> alter tablespace users online;

    Tablespace altered.


    SQL> select file_name,status from dba_data_files where
    tablespace_name = 'USERS';

    FILE_NAME
    ------------------------------------------------------------
    STATUS
    ---------
    /u01/app/oracle/oradata/users01.dbf
    AVAILABLE



    CASE 6: Lost everything including datafiles and controlfiles
    except for online redo logs.


    Note: There was no backup of online redo log files.

    Restore all the files from backup except for online redo log files.

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 619360256 bytes
    Fixed Size 1338280 bytes
    Variable Size 398459992 bytes
    Database Buffers 213909504 bytes
    Redo Buffers 5652480 bytes
    Database mounted.

    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 807805 generated at 01/24/2012 14:50:13
    needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_10_7kwyqqnb_.arc
    ORA-00280: change 807805 for thread 1 is in sequence #10

    identify the correct online redo log file.

    SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
    ---------- ---------- ---------- ---------- ---------- ---------- ---
    STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
    ---------------- ------------- --------- ------------ ---------
    1 1 10 52428800 512 1 YES
    ACTIVE 807803 24-JAN-12 808486 24-JAN-12

    3 1 9 52428800 512 1 YES
    INACTIVE 807800 24-JAN-12 807803 24-JAN-12

    2 1 11 52428800 512 1 NO
    CURRENT 808486 24-JAN-12 2.8147E+14



    SQL> select * from v$logfile;

    GROUP# STATUS TYPE
    ---------- ------- -------
    MEMBER
    -------------------------------------------------------------
    IS_
    ---
    3 ONLINE
    /u01/app/oracle/oradata/orcl/redo03.log
    NO

    2 ONLINE
    /u01/app/oracle/oradata/orcl/redo02.log
    NO

    GROUP# STATUS TYPE
    ---------- ------- -------
    MEMBER
    ---------------------------------------------------------------
    IS_
    ---

    1 ONLINE
    /u01/app/oracle/oradata/orcl/redo01.log
    NO




    Apply logfiles sequence number wise.

    First give path for sequence 10 then 11.


    Specify log: {=suggested | filename | AUTO | CANCEL}
    /u01/app/oracle/oradata/orcl/redo01.log
    ORA-00279: change 808486 generated at 01/24/2012
    15:03:43 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/flash_recovery_area/ORCL/archivelog
    /2012_01_24/o1_mf_1_11_%u_.arc
    ORA-00280: change 808486 for thread 1 is in sequence #11
    ORA-00278: log file '/u01/app/oracle/oradata/orcl
    /redo01.log' no longer needed for this recovery


    Specify log: {=suggested | filename | AUTO | CANCEL}
    /u01/app/oracle/oradata/orcl/redo02.log
    Log applied.
    Media recovery complete.
    SQL> alter database open resetlogs;

    CASE 7: Lost a read only tablespace which was read only when backup
    was taken.


    Suppose I lost my read only users tablespace.

    SQL> alter tablespace users offline;

    Tablespace altered.

    Restore from Backup.

    SQL> alter tablespace users online;

    Tablespace altered.

    CASE 8:
    Creating new Control File After Losing All Current
    and Backup Control Files.


    SQL> shutdown abort
    ORACLE instance shut down.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 619360256 bytes
    Fixed Size 1338280 bytes
    Variable Size 398459992 bytes
    Database Buffers 213909504 bytes
    Redo Buffers 5652480 bytes
    ORA-00205: error in identifying control file, check alert log for more info

    SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
    DATAFILE
    '/u01/app/oracle/oradata/orcl/system01.dbf',
    '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
    '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
    '/u01/app/oracle/oradata/orcl/users01.dbf',
    '/u01/app/oracle/oradata/orcl/example01.dbf'
    CHARACTER SET WE8MSWIN1252;


    Control file created.

    SQL> recover database;


    Media recovery complete.


    -- All logs need archiving and a log switch is needed.

    SQL> alter system archive log all;

    System altered.


    SQL> alter database open;

    Database altered.

    SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
    SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

    Tablespace altered.
    Last edited by ajaychandi; 02-14-2012 at 07:25 PM.

+ Reply to Thread

Similar Threads

  1. Oracle 11g Manual Database Creation.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 03-13-2013, 06:24 PM
  2. Voting Disk Backup and Recovery
    By Hemant in forum RAC Concepts and Administration
    Replies: 1
    Last Post: 05-24-2012, 06:48 PM
  3. 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
  4. Learn to do manual installation of the database in just 6 steps
    By shoaibansari in forum Database Installation,Upgrades,Migration,Patching and Cloning
    Replies: 1
    Last Post: 02-14-2012, 04:50 PM
  5. Replies: 1
    Last Post: 01-01-2012, 03:50 PM

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