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
[oracle@primary01 ~]$ srvctl stop database -d smalldb

2. Mount the database and switch to ARCHIVELOG mode.
[oracle@primary01 ~]$ srvctl start database -d smalldb -o mount
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> alter database archivelog;

Database altered.

3. Open the database and check the log mode.
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

4. Shutdown the database and make a user-managed backup.
[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

5. Open the database and do several switch logfile.
[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

6. Shutdown the database and restore it from the user-managed backup.
[oracle@primary01 smalldb]$ srvctl stop database -d smalldb
[oracle@primary01 smalldb]$ cp /tmp/user_backup/* .

7. Open the database and check the current log sequence.
[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