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!

User managed backups in Oracle Database

Discussion in 'Server Administration and Options' started by Anne, Sep 9, 2008.

  1. Anne

    Anne Forum Advisor

    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    90
    User managed backups in Oracle Database

    Loss of database data has always been a threat for individuals and organizations. Developers have been working on ways to keep the data secure in extreme conditions also but many a times it is not possible to save data stored in the database. In such situations we have to use the data we have backed up earlier for such circumstances. There are various methods by which you can backup your data in Oracle database. Recovery Manager (RMAN) tool of Oracle is one such example using which you can backup your data in the database.

    User managed backups are those backups in which you do not use Recovery Manager or RMAN.

    It is very important that you identify all the directories, folders or files that need to be backed up before we start backup process. It is suggested that you list all the database files, control files and online redo files before selecting the backup files. Then you need to confirm whether the datafile you considered for backup is onilne currently or not. You should use V$BACKUP view command to check if any new tablespaces have been introduced in the table or not. This can better be ascertained when database is in open state. A new control file or restored backup does not comprise of the vital information required for V$BACKUP command.

    You can also take user managed backup of whole database at once but for this you will have to shut down database. In case you do not shut down database before database backup then it might result in inconsistency. It is possible to backup whole database only if it is running in NOARCHIVELOG or ARCHIVELOG mode.

    However, in NOARCHIVELOG mode it is necessary to shut down database before backup task is initiated. Backing up data in ARCHIVELOG mode would result in inconsistent data backup. It is very easy to restore the consistent whole database backup as all the backup files use the same SCN. If the database was in ARCHIVELOG mode then you need to recover the data to more recent operations.

    It is possible to backup tablespaces that are not in use currently or are offline and do not have any rollback segments. Moreover, this process does not effect the running or active tablespaces. You may also backup online and open datafiles or tablespaces. If the online datafile is read/write type then you need to put tablespace in backup mode so that the file is stored till the current position. You would require redo log files that record all changes to update backup file.

    It is also possible to make multiple user managed backups of table spaces with online read/write option by backing up online tablespace parallely or serially. Backing up tablespaces that are online and with read-only attributes is very easy as system is not permitting you the changes to datafiles. You may also transfer or backup the metadata associated with the database with transportable tablespace functionality. This way you provide security to your data in the Oracle database.
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    This is somewhat misleading, Actually In order to see any added tablespace you have to use DBA_TABLESPACES or V$TABLESPACE view.
    In order to see existing datafiles and their status query from DBA_DATA_FILES or V$DATAFILE view.

    And V$BACKUP is important to display the backup status of all online datafiles. To make it clear I just include one example.
    Code (Text):
    SQL> select * from v$backup;

         FILE# STATUS                CHANGE# TIME
    ---------- ------------------ ---------- ---------
             1 NOT ACTIVE                  0
             2 NOT ACTIVE                  0
             3 NOT ACTIVE                  0
             4 NOT ACTIVE                  0
             5 NOT ACTIVE                  0
             6 NOT ACTIVE                  0

    6 rows selected.
    Now make tablespace users in backup mode.
    Code (Text):
    SQL> alter tablespace users begin backup;

    Tablespace altered.
    Check the view.
    Code (Text):
    SQL> select * from v$backup;

         FILE# STATUS                CHANGE# TIME
    ---------- ------------------ ---------- ---------
             1 NOT ACTIVE                  0
             2 NOT ACTIVE                  0
             3 NOT ACTIVE                  0
             4 ACTIVE                1949372 10-OCT-08
             5 NOT ACTIVE                  0
             6 NOT ACTIVE                  0

    6 rows selected.
    Check the tablespace containing ACTIVE backup mode.
    Code (Text):
    SQL> select tablespace_name from dba_data_files where file_id IN(select file# from v$backup where status='ACTIVE');

    TABLESPACE_NAME
    ------------------------------
    USERS
    Make the end backup and see.
    Code (Text):
    SQL> alter tablespace users end backup;

    Tablespace altered.

    SQL> select * from v$backup;

         FILE# STATUS                CHANGE# TIME
    ---------- ------------------ ---------- ---------
             1 NOT ACTIVE                  0
             2 NOT ACTIVE                  0
             3 NOT ACTIVE                  0
             4 NOT ACTIVE            1949372 10-OCT-08
             5 NOT ACTIVE                  0
             6 NOT ACTIVE                  0

    6 rows selected.
     
  3. Anne

    Anne Forum Advisor

    Messages:
    24
    Likes Received:
    2
    Trophy Points:
    90
    Hi.. thanks for correcting me! actually i confused V$BACKUP with V$TABLESPACE

    i have to admit i am no expert in database. i am more of a middleware person :)
     
  4. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    No problem. I appreciate that you have written one.