Client-failover for dataguard switchover and failover

Introduction

The following is nothing more than some extension of the oracle metalink document:

[ID 740029.1] Step By Step Guide On How To Configure And Test Client-Failover For Dataguard Switchover And Failover

That is, the article does not only present the cookbook how-to of its anchestor but also examines what happens in the database and what the clients experience will be concerning a so called transparent application failover (TAF). The scenario has been tested on an oracle 10.2.0.3 on windows, please note that the metalink document points to a some more applicable technique available with oracle 11gR2 (Client Failover Best Practices for Highly Available Oracle Databases: Oracle Database 11g Release 2).

Client-failover for dataguard, away from other failover scenarios, essentially aims at using a general tnsnames entry against some server-side database endpoint, no matter what server-side database instance is currently running in a dataguard primary role. This is actually the transparency in a transparent application failover where the application, client here, does not have to care to switch its network configuration in any way (some simple implementation would be, not uncommonly, to provide some tnsnames.ora, tnsnames.ora.prm and tnsnames.ora.stb and rename the files) during a failover or switchover.

Implementing CANCEL_SQL with the resource manager

introduction

this article is more a recipe on how to get CANCEL_SQL working for a dedicated use case than a discussion of oracle resource manager concepts and technology. but, however, as long as CANCEL_SQL can not be implemented without having at least a basic understanding of resource manager, be adviced to study the relevant oracle documentation (from 10g onwards) before running or even reintegrating the code below. It is important to learn that activating a resource manager plan is a system wide effect because there can only be one resource manager plan active at one defined point in time. additionally, others may also be granted to activate or switch resource manager plans. sounds like the big wheel and side effects en masse? no, not really, you just need to know where to plug in your resource requirements and how to adapt your application code accordingly.

Your eye into the buffer cache

ever asked yourself what is going on in the buffer cache? e.g., does oracle honour my settings for the different buffer cache types, that is, the default, keep and recycle cache? or, does oracle tune the buffer cache nicely, such that the application important database objects are in fact cached?

the latter is, however, not only oracle’s duty. imagine an application where there is an index missing on a large table. on every unindexed select, oracle will have to full scan the table, flooding the buffer cache with unwanted blocks. but how to trap this scenario?

the solution key is v$bh or, internally, x$bh, a system view that allows a blockwise inspection of the buffer cache. some pretend that v$bh has been introduced with oracle parallel server (ops), others relate it to the real application cluster (rac) environment.

Running indirect data buffers on windows server 2008

introduction

i’ve been running oracle databases with around 5 gb sga since the days of oracle release 9iR2 and windows server 2003 (enterprise edition) on regular midrange 8 gb machines. although i initially had to pass a long and winding road to come up with a really working configuration, between oracle and windows server 2003, the effort really did pay off in having that extended, past 2 gb ram access in spite of the limitations of the 32 bit addressing range. to scetch the windows 2003 recipe in short, one may follow this approach:

• add the /3gb and the /PAE to the boot.ini file (and reboot)
• have the account running the oracle service be a part of the administrators group
• set several local user rights for the account running the oracle service, for example lock_pages_in_memory
• do not use the oracle asm-parameters with the spfile, instead apply use_indirect_data_buffers and the pre-asm stuff just as db_block_buffers and buffer_pool_keep
• optimize the setting of _db_block_lru_latches according to the cpu-count of your machine
• reexecute oracle.exe, a shutdown/startup-bounce is not sufficient for the change to take effect

although just scetching, i want to emphasize the role of the /3gb-switch mentioned above. using a past 2 gb ram access on 32 bit windows must be a rope trick of some sort in overcoming the limitations of the addressing range. the trick is, here, to define a mapping window between the regular and the extended ram address space. that mapping window can also actually be seen by the applications. some secondary process however, maps memory pages into that window, either from the extended address space, which is a fast ram access, or just from disc, the traditional way. that is, the larger the mapping window within the regular address space is, the higher the hit ratio will grow (see the AWE_WINDOW_MEMORY-parameter to be set in the registry for more information). such a way, it always really pays off to employ /3gb alongside with /PAE.

the windows 2008 recipe

to anticipate the result of the 2008 trial, nothing conceptually changes compared to the 2003 recipe. the exception is the application of bcdedit, with is the new boot configuration command, replacing the need to directly hack the boot.ini file (it is just ignored if done) and the naming and default values of /3gb and /PAE. but let’s follow the trial.