Search This Blog

Thursday, January 18, 2007

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>

2 comments:

Anonymous said...

Hello!?
The copy command does not work in Ms Windows Server before Ms Windows Server 2003.
You must use the ocopy which Oracle are provided.

Mohammed Taj said...

Hi,
The copy command does not work in Ms Windows Server before Ms Windows Server 2003.

I don't think because above scripts tested in Windows XP sp2. and it is working correctly.

And if you take HOT BACKUP then MS Windows COPY command is not copy open database files that time you have to use OCOPY command.

Before WinXP i was not tested COPY command.

anyway thank for your suggestion.
i will edit my post with your comments.

regards
Taj