Hi i use Oracle 10G database(10.2.0.3.0) in Windows 2003 or Windows 2008 server.
We have practice to take a data backup after day end and then shutdown the server before leaving office.
But stuffs do not follow that practice regularly.
So i made a database trigger which automatically start DBMS_DATAPUMP SCHEMA EXPORT JOB when the last
client software logout from the database.
But the problem is that as the backup job runs in background, people simple shutdown the server and the export failed.
Is it possible by any way pause the Windows Shutdown process till the oracle DMP file creation finish at background.
Hoping for a innovative solutions.
First of all version you are using is not supported anymore. Second, You can make a windows bat file and automate the datapump and the restart of the server. You can use 'if' condition in a bat file to confirm datapump export success and reboot the server.
And you shouldn't be using trigger to kick the datapump backup(what will happen when your last client logs out and your backup kicks in and 2 minute later another client logs in and logs out, your trigger will execute another datapump backup which would be wrong and unnecessary)
You need to define the business rules for when the batch script should run. Again you can use Windows batch script or perl or any other scripting tool to automate this. This is just my view.
The first thing i would ask is why do you have to shutdown the machine every night? Is there some problem that means you have to do that? Do the cleaners need to use that power socket to plug in the vacuum cleaner? :-)
The seocnd thing is that an export is just a logical backup at that point in time, it can;t be used for proper recovery to any point in time, I'd be looking at using rman to do things properly.
If you do still want to export/reboot every night you can do it as TSharma says above with those caveats. You could maybe restart in resticted mode and then do the export to stop people connecting or stop the listener and do a local connection with expdp to do the backup to prevent any remote connections?