test

REAL WORLD EXAMPLE: ADDRESSING PERFORMANCE ISSUES

Some time ago, we were asked to help troubleshoot application performance for a web-based application that connects to an Oracle active-passive cluster based on Oracle Clusterware. Users were complaining about the application “hanging” and a slow response time.

Upon investigation, the following became apparent:

·         Bad transaction design severely limited scalability. ·         The application code accessing the database was built using an inflexible development framework. ·         The application was not stateless, as one would expect from a web-based tool. ·         The connection pool was oversized.

The worst part—and a problem that is nearly impossible to fix (because it originated from the application development framework)—was the transaction design. Whenever a user modified data in the frontend, the connection her application grabbed from the connection pool would turn every subsequent select into a select for update statementxe "for update statement". Even though the number of users was not huge, the select for update commandsxe "select for update commands" from multiple sessions effectively introduced massive enqueue problems that resulted in the hanging or slow performance situation. Oracle deadlock detection alleviated this problem a little from a database point of view by rolling back a few transactions, but otherwise this problem required a major application rewrite to fix. Lesson learned: Any application design should address scalability right from the xe "!!!scalability :and RAC :inhibitors to "xe "!!!scalability :and RAC "xe "!!!RAC (Real Application Clusters) architecture :and scalability :inhibitors to "xe "!!!RAC (Real Application Clusters) architecture :and scalability "outset.

Standby Databases

Real Application Clusters is primarily a high availability and scalability solution. Among its main benefits: It protects the system from loss of service in case of instance failure, which would otherwise cause an unplanned outage in a single-instance setup.

However, a catastrophic site failure is usually not covered by RAC, except for the case of a rare implementation of extended distance clusters. Also, common human errors such as the following can cause logical corruption in the database:

·         Dropping a table with the deactivated Flashback Table feature

·         Updating data with an incorrectly specified where clause

·         Confusing the production development with the development or test environment

Without standby databases, either a flashback databasexe "flashback database" call or a point-in-time restorexe "point-in-time restore" is needed to recover from such mishaps. With today’s large database systems, a point-in-time restore is usually not an option due to the large mean time to recovery associated with a full restore and recovery operation. Adding in the fact that RAC has been chosen in many cases for high availability highlights why a few hours of downtime cannot be tolerated. The Flashback Database feature introduced in Oracle 10g has greatly reduced the recovery time in many cases. In fact, the Flashback Database feature has become invaluable, not only for recovery of the production live database, but as we will see later in this chapter, for Data Guard scenarios as well.

Introduction to Oracle Standby Databases

To xe "!!!standby databases :overview "xe "!!!RAC (Real Application Clusters) architecture :standby databases :overview "protect from site failures or human error, an additional precaution needs to be taken in a RAC environment to provide disaster recoverability. Oracle Enterprise Edition has offered a feature called Oracle Data Guard since version 7.3 to address this requirement.

In Oracle 7 and 8/8i, this feature was referred to as a standby databasexe "standby database". The principle behind the technology is simple yet effective: an identical copy of the live (or primary) database (AKA the standby database) is instantiated in a remote data center. The standby database is constantly in the state of media recovery, unless opened for read-only access. Without the Active Data Guard option introduced in Oracle 11.1, there is an additional caveat you need to be aware of: while the database is opened in read-only mode, it doesn’t apply changes received from the primary database.

While not a problem per se, the fact that changes are not being applied can extend the time needed to transition to the standby database. This is because additional archived redo logs have to be applied unless you are willing to incur data loss. Without Active Data Guard, the database has to be in mount state for managed recovery to work. The mount state prevents users, except for those with sysdba privilege, from connecting to the database. Any attempt to do so will result in an ORA-1033 “Oracle initialization or shutdown in progress” messagexe "ORA-1033 ?DQ?Oracle initialization or shutdown in progress?DQ? message".

When the standby feature was introduced in Oracle 7.3, maintaining a standby database was a highly manual process: the database administrator was in charge of transferring archived redo logs generated on the primary database to the standby site using utilities such as rcp or ftp (rsync). Once the logs were on the standby site, the standby database had to be placed in recovery mode. The only possible action the administrator could take was to activate the standby database in order for it to assume the primary role. This process where the DBA copied logs was referred to as manual recoveryxe "manual recovery".

Beginning with Oracle 8i, the standby database uses managed recoveryxe "managed recovery" to stay in sync with the primary database. Using Oracle Net*8 communication, the primary database ships changes to the standby database, which are subsequently applied to the data files to keep the systems in sync. The application of changes can be delayed to protect the system from the aforementioned user errors. A standby database can also used for reporting or backing up data; this removes some of the load from the primary database.

A further milestone was reached with Oracle 9i, which introduced the logical standby database and graceful switchover operations. It was also in Oracle 9i that the standby database feature was renamed to Data Guard. Users of Data Guard were also given another choice for transmitting redo information to the standby. In addition to the archiver, which traditionally shipped information to the standby database after an online redo log was archived, the log writer process could be used to perform. the same task. Standby redo logs were introduced as the counterpart to the primary database’s online redo logs. Instead of having to wait for a complete archived redo log to arrive, the redo stream could be written into a standby redo log, thus reducing the risk of data loss. Oracle 9i Database also introduced the Data Guard brokerxe "Data Guard broker" with support for Enterprise Managerxe "Enterprise Manager", as well as a command-line tool to simplify the setup and management of standby databases.

Another noteworthy evolution came with Oracle 10g, when the Real Time Apply featurexe "Real Time Apply feature" was integrated into the database kernel. Using standby redo logs on the standby database server, the redo stream arriving on the destination could be applied to the standby database immediately, without having to wait for the standby redo log to be archived and applied. This further reduces the possibility of data loss.

Figure 3-1 illustrates the concepts for Oracle 11g, where redo generated by user activity on the primary database is transported via the Log Network Server (LNS0)xe "Log Network Server (LNS0)" process—not the log writer, as in previous versions—to the standby database’s Remote File Server (RFS)xe "Remote File Server (RFS)" process. In turn, the RFS process writes the redo stream into standby redo logs. The managed recovery process (MRP0)xe "managed recovery process (MRP0)" on the standby database applies the new information as soon as it arrives. Once filled, the standby redo log is archived by one of the standby database’s archiver processes.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10473097/viewspace-694192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10473097/viewspace-694192/