A definers rights stored procedure in the SYS schema (that performs the restore point stuff using NDS) + one execute grant to a dev-schema? On Tue, Dec 3, 2013 at 8:02 PM, Stephens, Chris <Chris.Stephens@xxxxxxx>wrote: > 11.2.0.4 RAC on Oracle Linux 6 > > > > Yesterday I was asked how to handle the following: > > > > The developers are frantically working to debug a number of issues with a > nightly batch process that frequently fails and leaves the database in an > inconsistent state that require hours of manual backing out of partially > loaded data. While they work to fix those issues and to create a process > that allows them to gracefully restart batch loads, they are asking for the > ability to restore the database to the point immediately prior to the batch > load that failed. This is the only application currently running in the > database and I think Guaranteed Restore points are the best fit. I would > like to create a procedure they can (synchronously) call as a pre-exec step > immediately prior to the batch load and once that load completes and is > successfully verified, allow another procedure call to drop the restore > point. > > > > The problem is that SYSDBA is required to create a restore point. I’m > trying to figure out the most secure way to implement this. My initial > though is to create a shell script on the server that performs the > create/drop of restore point. Implement that shell script as a > preprocessor script for an external table. Create a procedure that selects > from that table and then grant execute on that procedure to the developer > role. In the shell script I would implement a check to ensure it hasn’t > been executed in the last 30 minutes or something. > > > > That seems like a pretty round about way of doing it but I don’t really > see any other way without giving the developers the ability to log on as > SYSDBA. Am I missing a much easier way to do this? > > > > Thanks for any suggestions. > > > > I’d also like to automate the restore back to the restore point but I plan > to wait on that until we’ve had to do this a few times. I don’t want to > get called several times / week in the middle of the night for the > foreseeable future. Any suggestions on how to handle this would also be > greatly appreciated. > > > > Chris > > > > CONFIDENTIALITY NOTICE: > This message is intended for the use of the individual or entity to which > it is addressed and may contain information that is privileged, > confidential and exempt from disclosure under applicable law. If the reader > of this message is not the intended recipient or the employee or agent > responsible for delivering this message to the intended recipient, you are > hereby notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by email reply. > > > -- Toon Koppelaars RuleGen BV Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13