Thursday, August 5, 2010

Do we really need tempfiles and online redo logfiles for cold backup?

Answer is "NO".

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01_nosparse.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ! rm /u01/app/oracle/oradata/orcl/redo03.log

SQL> ! rm /u01/app/oracle/oradata/orcl/redo02.log

SQL> ! rm /u01/app/oracle/oradata/orcl/redo01.log

SQL> ! rm /u01/app/oracle/oradata/orcl/temp01_nosparse.dbf

SQL> startup mount
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 197134048 bytes
Database Buffers 318767104 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.