Docstoc

Leveraging Oracle's Delayed Standby Database and Flashback

Document Sample
Leveraging Oracle's Delayed Standby Database and Flashback Powered By Docstoc
					Stephen D. Mund, OCP
Database Administrator
Nevada System of Higher Education
System Computing Services

OFFLOADING INPUT/OUTPUT ON
BLACKBOARD CAMPUS EDITION
USING ORACLE STANDBY
DATABASE TECHNOLOGY
Introduction

 Blackboard CE’s Section Backup.
Introduction

 Blackboard CE’s Section Backup.


 Using Oracle for backup of small datasets.
Introduction

 Blackboard CE’s Section Backup.


 Using Oracle for backup of small datasets.


 Offloading I/O from backups.
Case Study

 Great Basin College –
   Uses Blackboard CE as a primary tool in their
    Distance Learning program serving students in
    over 62,000 sq. mi. of rural Nevada.
Case Study

 Great Basin College –
   Uses Blackboard CE as a primary tool in their
    Distance Learning program serving students in
    over 62,000 sq. mi. of rural Nevada.
   Over 1200 courses are offered through the
    Blackboard CE solution.
Case Study

 Great Basin College –
   Uses Blackboard CE as a primary tool in their
    Distance Learning program serving students in
    over 62,000 sq. mi. of rural Nevada.
   Over 1200 courses are offered through the
    Blackboard CE solution.
   Section backup was performing nominally, issue
    was with the amount of coursework needed to be
    backed up.
Case Study (continued)

  Server degradation in performance towards the
  end of each semester.
Case Study (continued)

  Server degradation in performance towards the
   end of each semester.
  Massive slowdown causing users to log off and log
   on later.
Case Study (continued)

  Server degradation in performance towards the
   end of each semester.
  Massive slowdown causing users to log off and log
   on later.
  Hangs during both reading and writing.
Case Study (continued)

  Server degradation in performance towards the
   end of each semester.
  Massive slowdown causing users to log off and log
   on later.
  Hangs during both reading and writing.
  Caused by extensive I/O on the application server
   due to constant use of section backup.
Case Study (continued)

  Server degradation in performance towards the
     end of each semester.
    Massive slowdown causing users to log off and log
     on later.
    Hangs during both reading and writing.
    Caused by extensive I/O on the application server
     due to constant use of section backup.
    Section Backup required use of the application.
Options Considered

 Don’t use the section backup utility and use
  another database to perform Point-in-Time
  Recovery. Then use Section Backup to
  recovery the file.
Options Considered

 Don’t use the section backup utility and use
  another database to perform Point-in-Time
  Recovery. Then use Section Backup to
  recovery the file.
   PROS: Does not require ongoing use of storage.
    Relieves the I/O issue.
Options Considered

 Don’t use the section backup utility and use
  another database to perform Point-in-Time
  Recovery. Then use Section Backup to
  recovery the file.
   PROS: Does not require ongoing use of storage.
    Relieves the I/O issue.
   CONS: Cumbersome requires time consuming
    restoration to another server. More involved,
    difficult to script.
Options Considered

 Optimize Disk Performance. System currently
  uses RAID 5 could reconfigure to better RAID
  configuration like 1+0 or even RAID 1.
Options Considered

 Optimize Disk Performance. System currently
  uses RAID 5 could reconfigure to better RAID
  configuration like 1+0 or even RAID 1.
   PROS: I/O performance is increased at the
    storage level.
Options Considered

 Optimize Disk Performance. System currently
  uses RAID 5 could reconfigure to better RAID
  configuration like 1+0 or even RAID 1.
   PROS: I/O performance is increased at the
    storage level.
   CONS: It is doubtful that the performance
    increase would be substantial enough. Requires
    application downtime to configure and is labor
    intensive.
Options Considered

 Standby database with Flashback technology
  and roll forward.
Options Considered

 Standby database with Flashback technology
  and roll forward.
   PROS: Off loads most of the I/O leaving only a
    minor amount required for resynchronization.
Options Considered

 Standby database with Flashback technology
  and roll forward.
   PROS: Off loads most of the I/O leaving only a
    minor amount required for resynchronization.
   CONS: Requires extensive ongoing disk space for
    flashback logs, archive logs and database files.
Options Considered

 Delayed Standby database with Flashback
  technology
Options Considered

 Delayed Standby database with Flashback
  technology
   PROS: Has the advantages of previous option but
   uses about half the space.
Options Considered

 Delayed Standby database with Flashback
  technology.
   PROS: Has the advantages of previous option but
    uses about half the space.
   CONS: More involved setup. Requires special
    configurations to use extra long delay. Space
    requirement is more than first two options and
    recoveries are more involved than third option.
Our Solution

 Best solutions.
Our Solution

 Best solutions.
   Technical points option 3.
Our Solution

 Best solutions.
   Technical points option 3.
   Our specific situation option 4.
Our Solution

 Best solutions
   Technical points option 3.
   Our specific situation option 4.
 Option 3 is the easiest to implement and
  simplest to maintain.
Our Solution

 Best solutions
   Technical points option 3.
   Our specific situation option 4.
 Option 3 is the easiest to implement and
  simplest to maintain.
 Option 4 was the best solution for us.
  Because it uses least disk space.
Implementation

 Set up a Standby database.
Implementation

 Set up a Standby database.
   This is well documented in
    Oracle® Data Guard Concepts and
    Administration 10g Release 2 (10.2) Part Number
    B14239-05 ,Chapter 3, Chapter 12.7, Chapter 12.8
    Appendix F.
Implementation

 Remote Archivelog Destination.
Implementation

 Remote Archivelog Destination.
   Parameters ARCH.
Implementation

 Remote Archivelog Destination.
   Parameters ARCH.
   DELAY=20160.
Implementation

 Remote Archivelog Destination.
   Parameters ARCH.
   DELAY=20160.
   MAXIMUM PERFORMANCE.
Implementation

 Standby Redo Logs.
Implementation

 Standby Redo Logs.
   Not needed in this application.
Implementation

 Standby Redo Logs.
   Not needed in this application.
 Flashback Database.
Implementation

 Standby Redo Logs.
   Not needed in this application.
 Flashback Database.
   Only on the Standby Database.
Implementation

 Special Configurations.
Implementation

 Special Configurations.
   Control file retention period greater than the
    amount of the delay
    (control_file_record_keep_time).
Implementation

 Special Configurations.
   Control file retention period greater than the
    amount of the delay
    (control_file_record_keep_time).
   Archive logs will need to be maintained on both
    the primary and the standby for at least the delay
    time.
Implementation

 Errors.
Implementation

 Errors.
   If you .see something akin to this in the alert log:
    FAL[client]: Failed to request gap sequence
    GAP - thread 1 sequence 34810-34909
    DBID 3683442141 branch 624546717
    FAL[client]: All defined FAL servers have
    been attempted.
Implementation

 Errors.
Implementation

 Errors.
   Probably indicates that you are not
    keeping enough archive logs on the
    primary rather than keep time for the
    control file.
Implementation

 Errors.
   Probably indicates that you are not
    keeping enough archive logs on the
    primary rather that keep time for the
    control file.
   You will need to manually ship the
    archived logs on the standby to the
    primary.
Implementation

 Best Practice.
Implementation

 Best Practice.
   Place the standby database in it’s own
    home. During upgrades and patching
    you will need to patch the standby along
    with the primary.
Implementation

 Best Practice.
   Place the standby database in it’s own
    home. During upgrades and patching
    you will need to patch the standby along
    with the primary.
   If you use a development or test home
    then you won’t be able to test the patch
    without upgrading everything.
Using the Recovery System

 Recovery Overview.
Using the Recovery System

 Recovery Overview.
  1.   Ascertain the last “known good” time.
       This would be provided by the end user. The time
       which the restore was known to be good.
Using the Recovery System

 Recovery Overview.
  2.   Take the delayed standby out of the managed
       recovery mode.
       First you want to know the log that will be next
       applied to the standby. Use this query:
       SELECT NAME
       FROM V$ARCHIVED_LOG
       WHERE APPLIED = ‘NO’
       AND SEQUENCE# = (SELECT SEQUENCE#
                         FROM V$MANAGED_STANDBY
                          WHERE PROCESS LIKE ‘MRP%’);
       Cancel the recovery on the standby and defer the log
       shipping on the primary. Make a Guaranteed
       Recovery Point so you can resynchronize the system.
Using the Recovery System

 Recovery Overview.
  3.   Recover the standby to the correct point in time.
       RECOVER STANDBY DATABASE UNTIL TIME ‘
       YYYY-MM-DD:HH24:MI:SS’;
Using the Recovery System

 Recovery Overview.
  3. Recover the standby to the correct point in time.
     RECOVER STANDBY DATABASE UNTIL TIME ‘
     YYYY-MM-DD:HH24:MI:SS’;
  4. Activate and open the standby database
     ALTER DATABASE ACTIVATE STANDBY DATABASE;
     ALTER DATABASE OPEN;
Using the Recovery System

 Recovery Overview.
  5.   Point the application (development) to standby
       database.
       The ORACLE_SID of the development database
       needs to be change to the DB_UNIQUE_NAME of
       the standby database in the following files:

       CP-WebCTConnectionPool$PF-jdbc.xml
       CP-WebCTConnectionPool-jdbc.xml
       CP-WebCTJMSConnectionPool-jdbc.xml

       Then restart the applications server.
Using the Recovery System

 Recovery Overview.
  6.   Run the section backup in order to extract the course
       backup.
Using the Recovery System

 Recovery Overview.
  6.   Run the section backup in order to extract the course
       backup.
  7.   Restore the section backup on the production
       system.
Using the Recovery System

 Resynchronization Overview.
Using the Recovery System

 Resynchronization Overview.
  1.   Point the application (standby) to original database
       Change the following files back to the original sid:
       CP-WebCTConnectionPool$PF-jdbc.xml
       CP-WebCTConnectionPool-jdbc.xml
       CP-WebCTJMSConnectionPool-jdbc.xml.
Using the Recovery System

 Resynchronization Overview.
  1.   Point the application (standby) to original database
       Change the following files back to the original sid:
       CP-WebCTConnectionPool$PF-jdbc.xml
       CP-WebCTConnectionPool-jdbc.xml
       CP-WebCTJMSConnectionPool-jdbc.xml.
  2.   Remount the database and flashback to original delay
       STARTUP FORCE MOUNT;
       FLASHBACK DATABASE TO RESTORE POINT <your
       restore point>;
       ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
       STARTUP FORCE MOUNT;
       Drop the restore point to avoid having too many
       flashback logs.
Using the Recovery System

 Resynchronization Overview.
  3.   Manually register the next archivelog to pick up the
       delay information
       ALTER DATABASE REGISTER OR REPLACE
       LOGFILE ‘<full path to archived log from step 2 of
       recovery overview>’;
Using the Recovery System

 Resynchronization Overview.
  3. Manually register the next archivelog to pick up the
     delay information.
     ALTER DATABASE REGISTER OR REPLACE
     LOGFILE ‘<full path to archived log from step 2 of
     recovery overview>’;
  4. Enable log shipping on the primary and restart the
     managed recovery.
Using the Recovery System

 Resynchronization Overview.
  3. Manually register the next archivelog to pick up the
     delay information.
     ALTER DATABASE REGISTER OR REPLACE
     LOGFILE ‘<full path to archived log from step 2 of
     recovery overview>’;
  4. Enable log shipping on the primary and restart the
     managed recovery.
  5. Verify with log switch on the primary.
Using the Recovery System

 Resynchronization Overview.
  6.   Check the standby alert log for appropriate
       response. You should see logs shipping virtually
       immediately and eventually you should see “Media
       Recovery Log” which means a log has applied.
Final Results
Final Results

 Most notably was that GBC received NO
  complaints concerning access during the end
  of semesters.
Final Results

 Most notably was that GBC received NO
  complaints concerning access during the end
  of semesters.
 The change was so marked that it was
  commented on several times by the Web
  Campus administration team.
Final Results

 Lisa Frasier, Director of Online Education and
  Curriculum Development at Great Basin
  stated:
  “Logon was really not the problem but the
  extreme lag time and problems with
  assignment errors, due to resource
  competition. Assignment errors has stopped
  almost completely. We haven’t had a help
  desk call complaining about slow server
  speed since the upgrade was completed.”
Final Results

 Additional benefits are that GBC was able to
  extend testing of the latest major upgrade
  because of the reduction of performance
  problems made upgrading less crucial.
  Allowing added time for training of faculty
  and pilots for students.
Stephen D. Mund, OCP
Database Administrator
Nevada System of Higher Education
System Computing Services



      QUESTIONS?
                Stephen_Mund@nshe.nevada.edu
Stephen D. Mund, OCP
Database Administrator
Nevada System of Higher Education
System Computing Services

OFFLOADING INPUT/OUTPUT ON
BLACKBOARD CAMPUS EDITION
USING ORACLE STANDBY
DATABASE TECHNOLOGY

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:12/26/2012
language:Unknown
pages:72