Database fails to open after running a schedule SQL script (via database control) to perform a cold backup

Check this link too. May help in puttng more bells and whistles if you are interested.
http://www.informit.com/articles/article.asp?p=30348&seqNum=4&rl=1

I would test out the script from the first link before I look for additional functionality.

Use the windows command line utility oradim to shutdown and startup the database
http://www.oracleutilities.com/OSUtil/oradim.html
http://www.adp-gmbh.ch/ora/tools/oradim.html
http://www.psoug.org/reference/oradim.html

techji - We also have a RMAN backup job running through the dbconsole but we would also like to have a file copy backup.  The domain policy that this server is part of doesn't allow us to use the windows scheduler so I'm limited to using the scheduling features of the dbconsole.

MohanKNair - I don't understand how this would help.  Can you be more specific?

Did you try running a bat file using dbconsole scheduler ? then you can have more flexibility in scripting various commands.

----------------------------------
here is an example, you can do all this from dbconsole. But just to give you a clear idea of what is being done here.

begin
  dbms_scheduler.create_schedule(
       schedule_name   => 'daily_at_4am_except_monday',
       repeat_interval => 'FREQ=DAILY; INTERVAL=1; BYDAY=TUE,WED,THU,FRI,SAT,SUN; BYHOUR=4',
       comments        => 'schedule to run daily at 4am except on mondays');
 
  dbms_scheduler.create_program
    (  program_name   => 'backup_database',
       program_type   => 'EXECUTABLE',
       program_action => 'd:\oracle\product\admin\dw\scripts\backup_dw.bat > nul',
       enabled        => TRUE,
       comments       => 'Backup dw database via hot backup.'
    );

  dbms_scheduler.create_job (
    job_name=>'daily_backup',
    program_name =>'backup_database',
    schedule_name=> 'DAILY_AT_4AM_EXCEPT_MONDAY',
    enabled      => true,
    comments     => 'backs up the dw daily at 4am except on for mondays.'
  );
end;
/

It is easier to handle command line utilities when using .bat files to schedule jobs. Since oradim is a command line utility it can be used in  batch files.

The steps for taking a cold backup can be

1) Use oradim to shutdown the database
2) Copy datafiles and log files to destination folder
3) Use oradim to startup the database

techji:

Okay so I tried using your first solution that uses the .bat file for the backup.  I'm getting an error message in the output and I have attached it.  The script connects to the database and shuts it down.  It then copies the init and pwd file BUT it fails to copy any of the dbf files.  It then starts the database back up.  Here's the output from the log file.

----------BEGIN FULL COLD BACKUP OF DB----------
Mon 09/11/2006
04:28 PM
Generating cold backup script...
Mon 09/11/2006
04:28 PM
Shutting down...
Backing up...

E:\scripts>REM Script to take full cold database backup

E:\scripts>REM generated by make_cold_backup_scr.sql

E:\scripts>REM does not back up tempfiles

E:\scripts>REM

E:\scripts>set oracle_home=c:\oracle\product\10.2.0\db_1

E:\scripts>set backup_dir=e:\backuptemp

E:\scripts>SP2-0640: Not connected
'SP2-0640:' is not recognized as an internal or external command,
operable program or batch file.

E:\scripts>REM Copy init.ora and pwd files

E:\scripts>copy c:\oracle\product\10.2.0\db_1\database\initoracle.ora e:\backuptemp\initoracle.ora
        1 file(s) copied.

E:\scripts>copy c:\oracle\product\10.2.0\db_1\database\pwdoracle.ora e:\backuptemp\pwdoracle.ora
        1 file(s) copied.
Starting up...
Mon 09/11/2006
04:28 PM
Succesfully completed.
-----------END FULL COLD BACKUP OF DB-----------

Any ideas?

E:\scripts>SP2-0640: Not connected
'SP2-0640:' is not recognized as an internal or external command, operable program or batch file.

It didn't connect to the database. Can you check the user/pass you used ?

It was the account info.  I was running the script from the server so I didn't need the @orcl on the connect string.  

Are there any ramification for not having the temp01.dbf file?

I will try to schedule the execution of the .bat file in the morning.  I'll keep you posted.  Thanks for your help!

TEMP tablespace can be skipped from backups. You can always recreate a TEMP tablespace during recovery.

If you want, you can put the following sql in shutdown.sql

connect / as sysdba
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
shutdown immediate;
exit;

This will create a trace file in udump directory. If you open it, there you will find a sql to rebuild a control file and the following sql at the bottom. Just an easy way to find what all TEMP tablespace consisted of.

ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\CLT01\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.