COLD_BACKUP_windows

Database Version : 10.1.0.2.0
OS Platform : WinXP sp2
-------------------------------
Archive log : Disabled
Cold Backup
--------------------------------
I create three SQL scripts
1.shutdown.sql
2.startup.sql
3.copy.sql
----------------
Content of files.
1.shutown.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set headning off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\shutdown.sql
SQL>PROMPT shutdown immediate;
shutdown immediate
SQL>PROMPT exit;
exit
SQL>spool off;
-----------------------------------
2.startup.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set heading off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\startup.sql
SQL>PROMPT startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
SQL>exit;
exit
SQL>spool off;
--------------------------------------------
3.copy.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>spool d:\bkp_scripts\db01\copy.sql
SQL> select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$datafile
2 union all
3 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$controlfile
4 union all
5 select ' host COPY ' || member || ' d:\coldbkp_db01\ ' from v$logfile;
6 union all
7 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$tempfile;

host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSTEM01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\UNDOTBS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSAUX01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\USERS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL02.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL03.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO03.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO02.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO01.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\TEMP01.DBF d:\coldbkp_db01\

SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
SQL>PROMPT exit;
exit
SQL> spool off
----------------------------------------------------------------
I schedule my backup every friday 11 pm. i create COLDBKP.CMD file
4.coldbkp.cmd
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\shutdown.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\copy.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\startup.sql
-------------------------------------------------------
Schedule through SCHEDULE TASK windows utility.

Note : above scripts is my own workshop example. Test first any scripts before using.i am not responsible for any damage.


Note : If you don't include redolog files in COLD BACKUP. then you must open your database with RESETLOGS ( recover database through COLDBACKUP)

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORACLE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\oracle\product\10.1.0\oradata\oracle\*.LOG

SQL> host xcopy c:\oracle\product\10.1.0\oradata\oracle c:\ORACLEBKP
C:\oracle\product\10.1.0\oradata\oracle\CONTROL01.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL02.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL03.CTL
C:\oracle\product\10.1.0\oradata\oracle\SYSAUX01.DBF
C:\oracle\product\10.1.0\oradata\oracle\SYSTEM01.DBF
C:\oracle\product\10.1.0\oradata\oracle\TEMP01.DBF
C:\oracle\product\10.1.0\oradata\oracle\UNDOTBS01.DBF
C:\oracle\product\10.1.0\oradata\oracle\USERS01.DBF
8 File(s) copied

SQL> HOST del c:\oracle\product\10.1.0\oradata\oracle
c:\oracle\product\10.1.0\oradata\oracle\*, Are you sure (Y/N)? Y


SQL> host xcopy c:\ORACLEBKP c:\oracle\product\10.1.0\oradata\oracle
C:\ORACLEBKP\CONTROL01.CTL
C:\ORACLEBKP\CONTROL02.CTL
C:\ORACLEBKP\CONTROL03.CTL
C:\ORACLEBKP\SYSAUX01.DBF
C:\ORACLEBKP\SYSTEM01.DBF
C:\ORACLEBKP\TEMP01.DBF
C:\ORACLEBKP\UNDOTBS01.DBF
C:\ORACLEBKP\USERS01.DBF
8 File(s) copied


SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> recover until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
oracle OPEN

SQL>