Skip to content

User-Managed Backup (3/4) - Online Backup All Datafiles

  • by
User-Managed Backup (2/4) - Offline Backup The Whole Database With ARCHIVELOG Mode
To backup all datafiles online with user-managed mode, you can force the all datafiles to be consistent and stop writing changes into data blocks by having all datafiles into the active backup mode.
  1. Check backup status of all datafiles before entering 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#;

    TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    SYSTEM       1 /u01/app/oracle/oradata/smalldb/system01.dbf       NOT ACTIVE
    SYSAUX       2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf       NOT ACTIVE
    UNDOTBS1     3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf      NOT ACTIVE
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        NOT ACTIVE
  3. Alter database with BEGIN BACKUP to enter the active backup mode.
  4. SQL> alter database begin backup;

    Database 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#;

    TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    SYSTEM       1 /u01/app/oracle/oradata/smalldb/system01.dbf       ACTIVE
    SYSAUX       2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf       ACTIVE
    UNDOTBS1     3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf      ACTIVE
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        ACTIVE

    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
    Oldest online log sequence     21
    Next log sequence to archive   23
    Current log sequence           23
  5. Make a user-managed backup by copying all datafiles.
  6. [oracle@primary01 smalldb]$ rm /tmp/user_backup/*
    [oracle@primary01 smalldb]$ cp *.dbf /tmp/user_backup/
    [oracle@primary01 smalldb]$ ls /tmp/user_backup/
    sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
  7. Alter database with END BACKUP.
  8. [oracle@primary01 smalldb]$ sqlplus / as sysdba
    ...
    SQL> alter database end backup;

    Database altered.

    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#;

    TB_NAME    DF# DF_NAME                                            STATUS
    ---------- --- -------------------------------------------------- ----------
    SYSTEM       1 /u01/app/oracle/oradata/smalldb/system01.dbf       NOT ACTIVE
    SYSAUX       2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf       NOT ACTIVE
    UNDOTBS1     3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf      NOT ACTIVE
    USERS        4 /u01/app/oracle/oradata/smalldb/users01.dbf        NOT ACTIVE

    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
    Oldest online log sequence     21
    Next log sequence to archive   23
    Current log sequence           23
  9. Do some switch logfile to force the log sequence number incremented.
  10. SQL> alter system switch logfile;

    System altered.

    SQL> /

    System altered.
    ...
    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
    Oldest online log sequence     36
    Next log sequence to archive   38
    Current log sequence           38
  11. Restore all datafiles from the user-managed backup by copying all the datafiles back to the database.
  12. [oracle@primary01 smalldb]$ srvctl stop database -d smalldb
    [oracle@primary01 smalldb]$ cp /tmp/user_backup/*.dbf .
  13. Open the database, but it failed as expected, because all the datafiles are not consistent with the controlfile.
  14. [oracle@primary01 smalldb]$ srvctl start database -d smalldb
    PRCR-1079 : Failed to start resource ora.smalldb.db
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: '/u01/app/oracle/oradata/smalldb/system01.dbf'

    CRS-2674: Start of 'ora.smalldb.db' on 'primary01' failed
    CRS-2632: There are no more servers to try to place resource 'ora.smalldb.db' on that would satisfy its placement policy
  15. Recover the database
  16. [oracle@primary01 smalldb]$ srvctl start database -d smalldb -o mount
    [oracle@primary01 smalldb]$ sqlplus / as sysdba
    ....
    SQL> recover database;
    ORA-00279: change 1092395 generated at 02/05/2013 15:21:36 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_23_799174213.dbf
    ORA-00280: change 1092395 for thread 1 is in sequence #23


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    auto
    ORA-00279: change 1092820 generated at 02/05/2013 15:29:13 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_24_799174213.dbf
    ORA-00280: change 1092820 for thread 1 is in sequence #24


    ORA-00279: change 1092823 generated at 02/05/2013 15:29:14 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_25_799174213.dbf
    ORA-00280: change 1092823 for thread 1 is in sequence #25


    ORA-00279: change 1092826 generated at 02/05/2013 15:29:17 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_26_799174213.dbf
    ORA-00280: change 1092826 for thread 1 is in sequence #26


    ORA-00279: change 1092829 generated at 02/05/2013 15:29:17 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_27_799174213.dbf
    ORA-00280: change 1092829 for thread 1 is in sequence #27


    ORA-00279: change 1092833 generated at 02/05/2013 15:29:22 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_28_799174213.dbf
    ORA-00280: change 1092833 for thread 1 is in sequence #28


    ORA-00279: change 1092836 generated at 02/05/2013 15:29:22 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_29_799174213.dbf
    ORA-00280: change 1092836 for thread 1 is in sequence #29


    ORA-00279: change 1092839 generated at 02/05/2013 15:29:25 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_30_799174213.dbf
    ORA-00280: change 1092839 for thread 1 is in sequence #30


    ORA-00279: change 1092842 generated at 02/05/2013 15:29:25 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_31_799174213.dbf
    ORA-00280: change 1092842 for thread 1 is in sequence #31


    ORA-00279: change 1092853 generated at 02/05/2013 15:29:35 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_32_799174213.dbf
    ORA-00280: change 1092853 for thread 1 is in sequence #32


    ORA-00279: change 1092856 generated at 02/05/2013 15:29:35 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_33_799174213.dbf
    ORA-00280: change 1092856 for thread 1 is in sequence #33


    ORA-00279: change 1092859 generated at 02/05/2013 15:29:38 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_34_799174213.dbf
    ORA-00280: change 1092859 for thread 1 is in sequence #34


    ORA-00279: change 1092862 generated at 02/05/2013 15:29:38 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_35_799174213.dbf
    ORA-00280: change 1092862 for thread 1 is in sequence #35


    Log applied.
    Media recovery complete.
  17. Open the database.
  18. SQL> alter database open;

    Database altered.

    SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
    Oldest online log sequence     36
    Next log sequence to archive   38
    Current log sequence           38

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

Leave a Reply

Your email address will not be published.