User Managed, Consistent Backup in Noarchivelog Mode

-You must have to take consistent backup if your database is in noarchivelog mode.

-Remember that database backup taken while the database is open or after an instance failure or SHUTDOWN ABORT is inconsistent.

-So in noarchivelog mode you can't take your backup while the database is open.

-In case of noarchivelog mode you can take your backup after the database has been shut down with the NORMAL, IMMEDIATE, or TRANSACTIONAL options. Backup taken in this way is called consistent backup.

-Consistent backup can be taken whether you are in archive or noarchivelog mode.

-Consistent backup can also called cold backup.

In the following sections I will see an example of how we can taken consistent used Managed Backup.

1)Determine the files that you need to take backup.

Whenever you decide to take your database user managed consistent backup then take backup of data files, control files spfiles and network files.

In order to decide which files you need to backup issue the following query while the database is in open state.

SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

File Need Backup
---------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora

11 rows selected.

2)Make clean shutdown of the database.
That is anyone of the following,

SQL>SHUTDOWN
SQL>SHUTDOWN IMMEDIATE
SQL>SHUTDOWN TRANSACTIONAL

3)Use an operating system utility to make backups of all datafiles as well as all control files as shown in section 1. Like,

$scp /oradata2/data1/dbase/system01.dbf /backup
.
.

scp /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora /backup

Also backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.

4)Startup the database.

$sqlplus / as sysdba
SQL>STARTUP