Oracle cold backup

If your Database in "NOARCHIVELOG" mode, you can do cold backup only.

List of files for the backup:

                   Select name from v$datafile 
                                         union all
                   select name from v$tempfile
                                         union all
                   select member from v$logfile
                                         union all
                   select name from v$controlfile

Example:

D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF
D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_BGQ5ZKBM_.LOG
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF

Cmd -> sqlplus " / as sysdba"
SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Copy files manually
SQL>startup

Backup.bat:

mkdir %1
copy D:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM02.DAT d:\backup\%1\
copy D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_BGQ5ZKNZ_.LOG d:\backup\%1\
copy D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_BGQ5ZKBM_.LOG d:\backup\%1\

Restore.bat

copy d:\backup\%1\SYSTEM.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\SYSAUX.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\UNDOTBS1.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\USERS.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\TEMP.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\CONTROL.DBF D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
copy d:\backup\%1\SYSTEM02.DAT D:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM02.DAT
copy d:\backup\%1\O1_MF_2_BGQ5ZKNZ_.LOG D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_BGQ5ZKNZ_.LOG
copy d:\backup\%1\O1_MF_1_BGQ5ZKBM_.LOG D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_BGQ5ZKBM_.LOG

All files for backup should be (recommendation) in the same catalog. Files *.Log usually has name REDO: redo01.log,redo02.log… How can we change names of files?

Do cold backup
Copy and rename files:

SQL> shutdown immediate
Cmd> copy D:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM02.DAT D:\ORACLEXE\APP\ORACLE\ORADATA\XE\
Cmd> copy D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_BGQ5ZKNZ_.LOG D:\ORACLEXE\APP\ORACLE\ORADATA\XE\ REDO02.LOG
Cmd> copy D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\ O1_MF_1_BGQ5ZKBM_.LOG D:\ORACLEXE\APP\ORACLE\ORADATA\XE\ REDO01.LOG
SQL> STARTUP MOUNT
SQL>ALTER DATABASE RENAME FILE ‘D:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM02.DAT’ to ‘D:\ORACLEXE\APP\ORACLE\ORADATA\XE\ SYSTEM02.DAT’
SQL>ALTER DATABASE RENAME FILE ‘D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_BGQ5ZKNZ_.LOG’ to ‘D:\ORACLEXE\APP\ORACLE\ORADATA\XE\ REDO02.LOG’
SQL>ALTER DATABASE RENAME FILE ‘D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\ O1_MF_1_BGQ5ZKBM_.LOG’ to ‘D:\ORACLEXE\APP\ORACLE\ORADATA\XE\ REDO01.LOG’

2.9. SQL>ALTER DATABASE OPEN;

If OK =>

Cmd> remove D:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM02.DAT;

Cmd>remove D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_BGQ5ZKNZ_.LOG

Cmd> remove D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\ O1_MF_1_BGQ5ZKBM_.LOG

New Backup file:

----

BACKUP.bat

Mkdir D:\backup

Cd D:\backup\

mkdir 20151116

cd D:\ORACLEXE\APP\ORACLE\ORADATA\XE
copy *.* D:\backup\20151116\

Blog. Databases

Oracle backup and restore (cold backup)

Oracle cold backup