Database fails to reach the open state after Oracle 10g database control job (SQL script) to perform a cold backup runs. All files are copied but the database doesn't reach the open state. I have to manually log into the database via sqlplus and type "startup" to bring the database online. The problem is that I need this job to be fully automated to run in the middle of the night when support personnel aren't on site. So manually having to bring the database to the open state is an issue.
The database is Oracle 10g R2 running on Windows 2003 Server Standard Edition. What is really odd is that I have done a reinstall of the database a few times and on each occasion the backup script I'm using runs flawlessly for a month or two without any problems then all of a sudden the database stops reaching the open state when the script is run. I have submitted this problem to Oracle support on a few occasion and I have yet to get a solution to this problem.
The script I am using is as follows:
shutdown immediate
host xcopy c:\oracle\product\10.2.0\a
dmin e:\oracle_backups\cold_bac
kups\9 /S /Y /I /O;
host xcopy e:\oradata e:\oracle_backups\cold_bac
kups\9 /S /Y /I /O;
host xcopy c:\oracle\product\10.2.0\d
b_1\databa
se\PWDorac
le.ora e:\oracle_backups\cold_bac
kups\9 /S /Y /I /O;
startup;
I am using Oracle 10g database control to create a SQL script job that runs a SQL script file that is stored on the hard drive through the @backup.sql format. The file that is being executed has the code listed above. I've also tried embedding the full script in the command window rather than referencing the SQL script file and I get the same result. Each time this scheduled SQL script job runs via database control the database fails to open after completion. The most interesting point here is that if I launch sqlplus and run this script manually (@backup.sql), it runs and the database opens every time. So the problem only occurs when the SQL script is launch via the 10g database control.
The most recent feedback I had received from Oracle Support was to create a "host command" job rather than a "SQL script" job but no details were giving on how to accomplish this task. Does anyone know how to accomplish this via a host command job in the 10g Database Control? or any other solutions would be appreciated.
I'm getting ready to head out for the weekend so it will probably be the beginning of the week before I can test any solutions so please be patient. Thanks in advance for your help!
Regards,
Jim