Skip to content
Home » Oracle Database » User-Managed Backup (2/4) - Offline Backup The Whole Database With ARCHIVELOG Mode

User-Managed Backup (2/4) - Offline Backup The Whole Database With ARCHIVELOG Mode

User-Managed Backup (1/4) - Offline Backup The Whole Database With NOARCHIVELOG Mode

First of all, you have to make sure your database is in ARCHIVELOG or NOARCHIVELOG mode.

Let's switch to ARCHIVELOG mode and demonstrate the backup and restore process.
  1. Shutdown the database
  2. [oracle@primary01 ~]$ srvctl stop database -d smalldb
  3. Mount the database and switch to ARCHIVELOG mode.
  4. [oracle@primary01 ~]$ srvctl start database -d smalldb -o mount
    [oracle@primary01 ~]$ sqlplus / as sysdba
    ...
    SQL> alter database archivelog;

    Database altered.
  5. Open the database and check the log mode.
  6. 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     17
    Next log sequence to archive   19
    Current log sequence           19
  7. Shutdown the database and make a user-managed backup.
  8. [oracle@primary01 ~]$ srvctl stop database -d smalldb
    [oracle@primary01 ~]$ cd /u01/app/oracle/oradata/smalldb/
    [oracle@primary01 smalldb]$ ls
    control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
    redo01.log     redo03.log  system01.dbf  undotbs01.dbf
    [oracle@primary01 smalldb]$ cp * /tmp/user_backup/
    [oracle@primary01 smalldb]$ ls /tmp/user_backup/
    control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
    redo01.log     redo03.log  system01.dbf  undotbs01.dbf
  9. Open the database and do several switch logfile.
  10. [oracle@primary01 smalldb]$ srvctl start database -d smalldb
    [oracle@primary01 smalldb]$ sqlplus / as sysdba
    ...
    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     17
    Next log sequence to archive   19
    Current log sequence           19
    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     33
    Next log sequence to archive   35
    Current log sequence           35
  11. Shutdown the database and restore it from the user-managed backup.
  12. [oracle@primary01 smalldb]$ srvctl stop database -d smalldb
    [oracle@primary01 smalldb]$ cp /tmp/user_backup/* .
  13. Open the database and check the current log sequence.
  14. [oracle@primary01 smalldb]$ srvctl start database -d smalldb
    [oracle@primary01 smalldb]$ sqlplus / as sysdba
    ...
    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     17
    Next log sequence to archive   19
    Current log sequence           19

    The database startup without questions, and the current log sequence is back to 19. The archived logs generated after 19 will be abandoned.
One more thing, a database with ARCHIVELOG mode can be backed up inconsistently, but it's not a good practice in the user-managed backup strategies.

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

Leave a Reply

Your email address will not be published. Required fields are marked *