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

Go Back to Overview Page -

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

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

A whole database backup made by a user-managed backup should not be done while the database is open. You should back it up after the database has been shutdown cleanly to obtain consistent files including controlfile and all datafiles.

A clean shutdown should be with NORMAL, TRANSACTIONAL, IMMEDIATE mode, otherwise, it's a inconsistent shutdown. Since a shutdown with ABORT mode usually leaves the database unroll-backed data, which is not consistent after all.

Restoring from a whole database backup in NOARCHIVELOG mode is fully satisfied, no more recovery needed before open, but if your database is in ARCHIVELOG mode, you should recover the database to the most current status before open.

1. Prepare a backup destination.
[oracle@primary01 ~]$ mkdir /tmp/user_backup

2. Check the archive log mode and current log sequence.
[oracle@primary01 ~]$ export ORACLE_SID=smalldb
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     17
Current log sequence           19
Now, the current log sequence is 19.

3. Shutdown the database.
[oracle@primary01 ~]$ srvctl stop database -d smalldb

4. Copy the whole database to the designated backup destination.
[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
User-managed backup files are stored in /tmp/user_backup

5. Open database, make some log switches and then check the current log sequence.
[oracle@primary01 smalldb]$ srvctl start database -d smalldb
[oracle@primary01 smalldb]$ sqlplus / as sysdba
...
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.
...
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     23
Current log sequence           25
After some log switches, the current log sequence is now 25.

6. Copy the user-managed backup files back to the database.
[oracle@primary01 smalldb]$ cp /tmp/user_backup/* .

7. Open the database.
[oracle@primary01 smalldb]$ srvctl start database -d smalldb
[oracle@primary01 smalldb]$
Like I said, the backup of the database with NOARCHIVELOG mode is self-contained. It can open without question.

8. Check the current log sequence.
[oracle@primary01 smalldb]$ sqlplus / as sysdba
...
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     17
Current log sequence           19
The current log sequence is back to 19.

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