Re: allowing developers to create guaranteed restore point
Date: Tue, 3 Dec 2013 20:08:53 +0100
Message-ID: <CAA9w=Es7_LveaLA-w72uAC6njCtmxWv8_2zzm9wVywQEHT_X1g_at_mail.gmail.com>
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_at_adm.com>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_at_RuleGen.com www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 03 2013 - 20:08:53 CET