Transcript BB_presentation - EduGarage (Blackboard Developers Network)

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.
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;
3.
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.
7.
Run the section backup in order to extract the course
backup.
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.
2.
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.
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.
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.
3.
Using the Recovery System
 Resynchronization Overview.
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.
3.
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?
[email protected]
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