7 Replies Latest reply on Nov 8, 2012 2:48 PM by mseberg

    Options for fast recovery - Reducing Downtime

      OS: OEL 5.7
      Database : (non-RAC)

      I'm looking for Options using ONLY Oracle Features to reduce downtime on scheduled outages, due application changes and upgrades.

      In this particular case I have only one application installed on this database (ERP).

      Default Backup (full) and Restore operation are activities that we already know, but I'm looking for others options that reduce downtime.

      I need a rollback plan in short time.

      Any help is welcome.
        • 1. Re: Options for fast recovery - Reducing Downtime
          Shivananda Rao
          You can have an option of using Dataguard to reduce downtime and have a failover in case when the production crashes.
          1 person found this helpful
          • 2. Re: Options for fast recovery - Reducing Downtime
            Iordan Iotzov
            Flashback database could be useful, particularly if you want to rollback DB changes after failed application change or upgrade.

            The feature does consume resources though.

            Iordan Iotzov
            1 person found this helpful
            • 3. Re: Options for fast recovery - Reducing Downtime
              Data Guard is your best option. With it you can define net services that allow Transparent Application Failover.

              You can also perform a swithover for planned downtime.

              Transparent Application Failover uses DBMS_SERVICE.CREATE_SERVICE

              Example :
              and then you can do this so the end users don't even see it
              ernie =
               (DESCRIPTION =
                  (ADDRESS_LIST =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
                     (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
                     (CONNECT_DATA =
                     (SERVICE_NAME = ernie)
              A trigger can control this :
               V_ROLE VARCHAR(30);
               IF V_ROLE = 'PRIMARY' THEN
               END IF;
              More details here


              Best Regards

              1 person found this helpful
              • 4. Re: Options for fast recovery - Reducing Downtime
                Levi Pereira

                Dataguard is the best option in case of short downtime, but you will need double of storage space.

                Two thing you must consider:
                * What is database size?
                * What is amount of data that will be updated/deleted/added during this application change?

                Choose one of this option:

                * Dataguard
                The best option and is really fast, near zero downtime. (As mentioned by mseberg using a nice example)

                * Flashback Database with RESTORE POINT
                Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window. These features provide a more efficient alternative to point-in-time recovery and does not require a backup of the database to be restored first.

                Restore points provide capabilities related to Flashback Database and other media recovery operations. In particular, a guaranteed restore point created at an system change number (SCN) ensures that you can use Flashback Database to rewind the database to this SCN. You can use restore points and Flashback Database independently or together.

                You will need to open the database with RESETLOGS after FLASHBACK Database.

                * Guarantee Restore Point (with flashback database disabled)
                Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped. In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point.
                A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled.

                You don't need RESETLOGS after rollback.

                * Edition-Based Redefinition for Online Application Maintenance and Upgrades
                Edition-based redefinition enables you to upgrade a database component of an application while it is in use, thereby minimizing or eliminating down time. This is accomplished by changing (redefining) database objects in a private environment known as an edition.
                To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.
                • 5. Re: Options for fast recovery - Reducing Downtime
                  Sebastian Solbach -Database Community-Oracle

                  there are some aspects which were not mentioned yet. Most answers you recieved were about cases to reduce unplanned downtime, for which Data Guard is really useful.
                  A nice overview you find in the HA Overview here:

                  However you were asking about Scheduled Outages, Application Changes and Upgrade (but did not specify application upgrade, DB upgrade or O/S upgrade).

                  So in regards of OS upgrades and minor PSU Patches of the Oracle database Oracle RAC (Real Application Cluster) already has a lot of functionality:

                  To extend unplanned/planned downtime Data Guard can also be of value in this area.
                  Especially if looking at Transient Logical Standby for minimal downtime DB Upgrade (Patch Set/Release Level):

                  But there are also other methods (Transportable Tablespace, or Golden Gate for example):

                  Golden Gate on the other side, has also a nice rollback plan...

                  Regarding Application Upgrade/Changes Online there is a developer feature inside the Oracle 11gR2 database called edition based redefinition:
                  Which allows you to update applications on the fly, without downtime. But it needs implementation (on the plus side it is included in the database)

                  Last but not least, you should look on the application side, to fully enable transparent failover capabilities:
                  1 person found this helpful
                  • 6. Re: Options for fast recovery - Reducing Downtime
                    Thanks all for the replies that are really valuable.

                    I was looking for a database feature that not need additional license or additional cost.

                    I will upgrade my application ERP (not database). The upgrade has been tested and homologated, I just need a rollback plan in case something go wrong during the upgrade (need a rollback plan in a short time).

                    My Database size is 9TB and the changes during application upgrade are small only metadata of ERP will be updated.

                    DataGuard is an option, but is discarded since I have no problems with downtime on this planned outage. I will need additional storage space (I don't have it) and a lot work to do.

                    Edition Based Redefinition: My application does not support this feature, so I cannot use it. Is a nice feature.

                    Flashback database: I have not enabled this feature, but is a good option. Don't need restore full backup. It's helps a lot.

                    Guarantee Restore Point: Seems the most obvious feature to use since I don't need to change anything in my database.
                    Create a Restore Point before the upgrade and create additional restore point until validate application, if something goes wrong only discard one or more restore point.
                    • 7. Re: Options for fast recovery - Reducing Downtime
                      Given that information your options appear to be a combination of RMAN and Flashback.

                      Please post this information :

                      Size(s) of database(s)

                      Recovery Time Objective.

                      Once you move to Oracle 11 you can use this :


                      Best Regards