This discussion is archived
7 Replies Latest reply: Nov 8, 2012 6:48 AM by mseberg RSS

Options for fast recovery - Reducing Downtime

user12028775 Newbie
Currently Being Moderated
OS: OEL 5.7
Database : 11.2.0.3-EE (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 Guru
    Currently Being Moderated
    You can have an option of using Dataguard to reduce downtime and have a failover in case when the production crashes.
  • 2. Re: Options for fast recovery - Reducing Downtime
    Iordan Iotzov Expert
    Currently Being Moderated
    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
    http://iiotzov.wordpress.com/
  • 3. Re: Options for fast recovery - Reducing Downtime
    mseberg Guru
    Currently Being Moderated
    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 :
    BEGIN
     
       DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
    
    END;
    /
    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 :
    CREATE TRIGGER CHECK_ERNIE_START AFTER STARTUP ON DATABASE
    DECLARE
     V_ROLE VARCHAR(30);
    BEGIN
     SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
     IF V_ROLE = 'PRIMARY' THEN
     DBMS_SERVICE.START_SERVICE('ernie');
     ELSE
     DBMS_SERVICE.STOP_SERVICE('ernie');
     END IF;
    END;
    /
    More details here

    http://uhesse.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/



    Best Regards

    mseberg
  • 4. Re: Options for fast recovery - Reducing Downtime
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,

    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 (DBA Community) Guru
    Currently Being Moderated
    Hi,

    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:
    http://www.oracle.com/technetwork/database/availability/index.html
    http://www.oracle.com/pls/db112/portal.portal_db?selected=14&frame=

    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:
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-plannedmaintwithcluste-129266.pdf
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-fastrecoveryoracleclus-130899.pdf

    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):
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

    But there are also other methods (Transportable Tablespace, or Golden Gate for example):
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-upgradetts-130980.pdf

    Golden Gate on the other side, has also a nice rollback plan...
    http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

    Regarding Application Upgrade/Changes Online there is a developer feature inside the Oracle 11gR2 database called edition based redefinition:
    http://www.oracle.com/technetwork/database/features/availability/ebr-455513.html
    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:
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf
  • 6. Re: Options for fast recovery - Reducing Downtime
    user12028775 Newbie
    Currently Being Moderated
    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
    mseberg Guru
    Currently Being Moderated
    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 :

    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrepai.htm#CHDCDHFD

    Best Regards

    mseberg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points