Cold Backup in Windows 2000 Server

Cold Backup in Windows 2000 Server

I have implemented cold backup in windows 2000 server. Here with i attached my script. Please try in testing environment and let me know your comments…

Coldbackup.bat

sqlplus -s system/manager@sngdb @C:\Dbbackup\CreateBackup.sql

Shutdown.bat

D:\oracle\ora92\bin\sqlplus -s "/@sngdb as sysdba" @C:\dbbackup\shutdown.sql

Startup.bat

D:\oracle\ora92\bin\sqlplus -s "/@sngdb as sysdba" @C:\dbbackup\startup.sql

TakeColdBackup.bat

/*** Script Generated On September 18, 2007 17:10:18 ***/

Spool E:\Dbbackup\HotBackup.log

SELECT TO_CHAR(SYSDATE, 'fmMONTH fmDD, YYYY HH24:MI:SS') "Started On" FROM DUAL;

PROMPT -- Starting Backup...

PROMPT -- Making Directory E:\Dbbackup\20070918171018

MkDir E:\Dbbackup\20070918171018

MkDir E:\Dbbackup\20070918171018\Oradata

Copy D:\ORACLE\ORADATA\SNGDB\SYSTEM01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\UNDOTBS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CWMLITE01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\DRSYS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SYSTEM02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\INDX01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\ODM01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\TOOLS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\USERS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\XDB01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\PERSONNEL01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\ANJUMIS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPURCHASE01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPURCHASE02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\INVENTORY01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY03.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\PRODUCTION01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPRODUCTION01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPRODUCTION02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIEXPORTS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIQC01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIRD01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMISALES01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\QC01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CONTROL01A.CTL E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CONTROL02A.CTL E:\Dbbackup\20070918171018\Oradata\

Copy C:\ORABAK\SNGDB\CONTROL\CONTROL01B.CTL E:\Dbbackup\20070918171018\Oradata\

Copy C:\ORABAK\SNGDB\CONTROL\CONTROL02B.CTL E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO03.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO02.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO01.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\TEMP01.DBF E:\Dbbackup\20070918171018\Oradata\

PROMPT -- Making Directory E:\Dbbackup\20070918171018\NETWORK\ADMIN

MkDir E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying Listener.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\listener.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying TnsNames.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying SqlNet.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Making Directory E:\Dbbackup\20070918171018\PFILE

MkDir E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying Initialization File From D:\oracle\admin\sngdb\pfile To E:\Dbbackup\20070918171018\PFILE

XCopy D:\oracle\admin\sngdb\pfile\init.ora E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying SPFile D:\oracle\ora92\database\SPFILEsngdb.ORA To E:\Dbbackup\20070918171018\PFILE

Copy D:\oracle\ora92\database\SPFILEsngdb.ORA E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying Password File D:\oracle\ora92\database\PWDsngdb.ora To E:\Dbbackup\20070918171018\PFILE

XCopy D:\oracle\ora92\database\PWDsngdb.ora E:\Dbbackup\20070918171018\PFILE

PROMPT -- ... Ending Backup

SELECT TO_CHAR(SYSDATE, 'fmMONTH fmDD, YYYY HH24:MI:SS') "Ended On" FROM DUAL;

Spool Off

Exit

/*** End of Script ***/

CreateBackup.sql

SET SERVEROUTPUT ON

SET LINESIZE 120

SET HEADING OFF

SET FEEDBACK OFF

SET TERMOUT OFF

SPOOL C:\DBBackup\TakeColdBackup.bat

DECLARE

v_backupfolder VARCHAR2(500) DEFAULT 'E:\Dbbackup';

v_backupnetworkfolder VARCHAR2(500);

v_backuppfilefolder VARCHAR2(500);

v_networkfolder VARCHAR2(500) DEFAULT 'D:\oracle\ora92\NETWORK\ADMIN';

v_pfilefolder VARCHAR2(500) DEFAULT 'D:\oracle\admin\sngdb\pfile';

v_spfile VARCHAR2(500) DEFAULT 'D:\oracle\ora92\database\SPFILEsngdb.ORA';

v_pwdfile VARCHAR2(500) DEFAULT 'D:\oracle\ora92\database\PWDsngdb.ora';

v_currentdate VARCHAR2(100) DEFAULT TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:ss');

v_oradatafolder varchar2(500) DEFAULT '\Oradata' ;

CURSOR c_dfile

IS

SELECT NAME from v$datafile

union all

SELECT name from v$controlfile

union all

SELECT MEMBER from v$logfile

union all

SELECT name from v$tempfile;

BEGIN

dbms_output.enable(20000);

dbms_output.put_line('/*** Script Generated On ' || v_currentdate || ' ***/');

dbms_output.put_line('Spool ' || v_backupfolder || '\HotBackup.log');

v_backupfolder := v_backupfolder || '\' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');

v_backupnetworkfolder := v_backupfolder || '\NETWORK\ADMIN';

v_backuppfilefolder := v_backupfolder || '\PFILE';

dbms_output.put_line('SELECT TO_CHAR(SYSDATE, ''fmMONTH fmDD, YYYY HH24:MI:SS'') "Started On" FROM DUAL;');

dbms_output.put_line('PROMPT -- Starting Backup...');

dbms_output.put_line('PROMPT -- Making Directory ' || v_backupfolder);

dbms_output.put_line(' MkDir ' || v_backupfolder );

dbms_output.put_line('MkDir ' || v_backupfolder || v_oradatafolder );

FOR r_tbsp IN c_dfile LOOP

dbms_output.put_line(' Copy' || ' ' || r_tbsp.name || ' ' || v_backupfolder || v_oradatafolder ||'\' );

END LOOP;

dbms_output.put_line('PROMPT -- Making Directory ' || v_backupnetworkfolder);

dbms_output.put_line(' MkDir ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying Listener.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\listener.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying TnsNames.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\tnsnames.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying SqlNet.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\sqlnet.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Making Directory ' || v_backuppfilefolder);

dbms_output.put_line(' MkDir ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying Initialization File From ' || v_pfilefolder || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' XCopy ' || v_pfilefolder || '\init.ora ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying SPFile ' || v_spfile || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' Copy ' || v_spfile || ' ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying Password File ' || v_pwdfile || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' XCopy ' || v_pwdfile || ' ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- ... Ending Backup');

dbms_output.put_line('SELECT TO_CHAR(SYSDATE, ''fmMONTH fmDD, YYYY HH24:MI:SS'') "Ended On" FROM DUAL;');

dbms_output.put_line('Spool Off');

dbms_output.put_line('Exit');

dbms_output.put_line('/*** End of Script ***/');

END;

/

SPOOL OFF

SET TERMOUT ON

SET FEEDBACK ON

SET HEADING ON

SET LINESIZE 80

SET SERVEROUTPUT OFF

EXIT

Shutdown.sql

Shutdown immediate;

Exit;

Startup.sql

startup;

exit;