Skip to content

User-Managed Backup (4/4) - Online Backup A Tablespace

  • by
User-Managed Backup (3/4) - Online Backup All Datafiles
The process of backing up a whole database online is same as datafiles, you must make the tablespace into the active backup mode for a consistent backup. The key steps are as following.
  1. Make the tablespace USERS into the active backup mode.
  2. SQL> column tb_name format a10;
    SQL> column df# format 99;
    SQL> column df_name format a50;
    SQL> column status format a10;
    SQL> SELECT t.name AS "TB_NAME",
      2    d.file#     AS "DF#",
      3    d.name      AS "DF_NAME",
      4    b.status
      5  FROM V$DATAFILE d,
      6    V$TABLESPACE t,
      7    V$BACKUP b
      8  WHERE d.TS#=t.TS#
      9  AND b.FILE#=d.FILE#;
     10  AND t.NAME='USERS';

    TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        NOT ACTIVE

    SQL> alter tablespace users begin backup;

    Tablespace altered.

    SQL> SELECT t.name AS "TB_NAME",
      2    d.file#     AS "DF#",
      3    d.name      AS "DF_NAME",
      4    b.status
      5  FROM V$DATAFILE d,
      6    V$TABLESPACE t,
      7    V$BACKUP b
      8  WHERE d.TS#=t.TS#
      9  AND b.FILE#=d.FILE#;
     10  AND t.NAME='USERS';

     TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        ACTIVE


  3. Copy the datafile /u01/app/oracle/oradata/smalldb/users01.dbf to the backup destination.
  4. End the active backup mode.
  5. SQL> alter tablespace users end backup;

    Tablespace altered.

    SQL> SELECT t.name AS "TB_NAME",
      2    d.file#     AS "DF#",
      3    d.name      AS "DF_NAME",
      4    b.status
      5  FROM V$DATAFILE d,
      6    V$TABLESPACE t,
      7    V$BACKUP b
      8  WHERE d.TS#=t.TS#
      9  AND b.FILE#=d.FILE#;
     10  AND t.NAME='USERS';

     TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        NOT ACTIVE

    SQL>

User-Managed Backup (0/4) - An Overview

Leave a Reply

Your email address will not be published.