Pages

Friday, 3 August 2012

Applications Database

For more information on Applications Database visit :
http://asteriskora.blogspot.in/


---------------------------------------------------------------------------------------------
Related links :

HOME
Posted by at No comments:
Labels:

Tuesday, 24 July 2012

11g R2 RAC : PROBLEMS DISCOVERED AND SOLUTIONS



                11G R2 RAC PROBLEMS AND SOLUTIONS

I discovered some issues while working with 11g R2 RAC. I would like to share them with u all.

- During c/w installation, If kernel parameters are not set to
  appropriate values and runfixup.sh is run, kernel.sem parameter
  is set for running machine only. It's value is reset on next
  reboot and will have to be set manually to

  kernel.sem = 250 32000  100 128

-------------------------------------------------------------

- Whenver there is any problemwith ASM, it could be due to
  following reasons

 - parameter kernel.sem not set to appropriate value
 - Inappropriate permissions on /u01/app/11.2.0/grid/bin/oracle
 - Inappropriate permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin


------------------------------------------------------
  - parameter kernel.sem not set to appropriate value
-------------------------------------------------------

  soln:

  - add following line to /etc/sysctl.conf

    kernel.sem = 250 32000  100 128

  - #sysctl -p

-------------------------------------------------------
 - Inappropriate permissions on /u01/app/11.2.0/grid/bin/oracle
-------------------------------------------------------

- Check  that Permission on file /u01/app/11.2.0/grid/bin/oracle
  shd be

# ls -lr /u01/app/11.2.0/grid/bin/oracle

-rwsr-s--x 1 grid oinstall 152462728 Apr  4 08:45 oracle

  soln:

  - if permissions are not appropriate, change them

    #chmod 6751 /u01/app/11.2.0/grid/bin/oracle

     chown grid:oinstall /u01/app/11.2.0/grid/bin/oracle

-------------------------------------------------------
 - Inappropriate permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin
-------------------------------------------------------

- Check  that Permission on file
  /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle   shd be

#ls -lr oracle

-r-sr-s--x 1 oracle asmadmin 173515905 Apr 10 23:29 oracle

  soln:

  - if permissions are not appropriate, change them

    #chmod 6751  /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

     chown oracle:asmadmin  /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle


------------------------------------------------------------------------

IF CRS SERVICE IS NOT STARTING ON A NODE AND CRSD.LOG HAS AN ENTRY
LIKE

   hash-id does not match

It implies that OCR info on that node is not compatible with
other nodes. This could be the case when OCR was reconfigured
(add/delete/replace a location) and the affected node was not
online so that it's ocr.loc was not updated.

Soln: Copy /etc/oracle/ocr.loc from a runnning node to the
      affected node and try to restart crs

-------------------------------------------------------------------
RCONFIG

IF WE USE RCONFIG TO ADD AN INSTANCE, ENTRIES ARE NOT AUTOMATICALLY
ADDED TO THE TNSNAMES.ORA ON THE NEWLY ADDED NODE. THEY HAVE TO BE
ADDED MANUALLY.

RCONFIG WILL AUTOMATICALLY ADD REDO THREADS FOR THE ALL THE RUNNING NODES IN THE CLUSTER. FOR EXAMPLE, IF WE HAVE
- A SINGLE INSTANCE DATABASE (NODE1)
- 5 NODES IN THE CLUSTER AND
- 3 RUNNING NODES (NODE1, NODE2, NODE3)

IF WE USE RCONFIG TO ADD ANOTHER INSTANCE ON ONE OF THE REMAINING RUNNING NODES (NODE2), IT WILL AUTOMATICALLY ADD 2 SETS OF REDO THREADS, ONE FOR NODE2 AND ANOTHER FOR NODE3.

--------------------------------------------------------------------

IF A NODE IS DELETED USING ROOTCRS.PL etc. procedure (steps 3,4 and 7)
GRID HOME IS NOT DELETED FROM THE DELETED NODE.

TO ADD BACK SUCH NODE,

  - RUN ADDNODE.SH WITH NOCOPY OPTION

    It will update the inventories on all existing nodes and
    instantiates scripts on local node

    #cd grid_home/oui/bin

     addNode.sh -silent -noCopy ORACLE_HOME=$ORACLE_HOME "CLUSTER_NEW_NODES={host03}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={host03-vip}"


   - RUN ROOT.SH

     In case it is not available in grid home, copy root.sh
     from any of the existing nodes

     #cd /u01/app/11.2.0/grid

      ./root.sh

---------------------------------------------------------------------------------------------

WHEN AN INSTANCE IS ADDED TO A RAC DATABASE USING DBCA, TNSNAMES.ORA
ON NEWLY ADDED INSTANCE IS NOT UPDATED.

Soln: Copy entry for the database from tnsnames.ora from existing node to the newly added node.

---------------------------------------------------------------------------------------------
 Problem:

After adding a node to the cluster, sometimes the node is visible to the cluster i.e. it appears when
crsctl stat res -t is issued but it is not listed in DBCA. The reason is that its information is not updated
in the inventory of existing nodes. 

Solution:

Add the entry of the newly added node in the inventory of the existing nodes for database home.

$cd /u01/app/oraInventory/ContentsXML
  vi inventory.xml
i.e.

<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="host01"/>
      <NODE NAME="host02"/>
      <NODE NAME="host03"/>

--------------------------------------------------------------------------------------------------------

If a serverpool is created using srvctl e.g. test and we want to see its status, we can see it using srvctl or crsctl.
If we query using srvctl status serverpool -g test, it will display its status
If we query using crsctl status serverpool test, it will not recognize test serverpool but
if we query using crsctk status serverpool ora.test, it displays the status.

Hence, the name of the serverpool created using srvctl should be prefixed by ora. for crsctl to recognize it. 

Another issue related to the same problem is that DBCA recognizes only the pools prefixed with ora. Hence if we want to create a policy managed database ,
  - create the corresponding serverpool using srvctl
   - if we create serverpool using crsctl and prefix  the name with .ora.   i.e crsctl create serverpool ora.test , DBCA will recognize the pool but the database created using this pool will be listed as ADMIN MANAGED when its configuration is checked using srvctl although it runs in the sreverpool.

   Hence we should use srvctl to create a serverpool if we want a database to run in that pool.

-------------------------------------------------------------------------------------------------------------

In case of an admin managed database , names of the instances have the format db1, db2 etc.
In case of an policy managed database , names of the instances have the format db_1, db_2 etc.
/etc/oratab should have entries for all the instances so that we can invoke EM.
To invoke EM:
$export ORACLE_UNQNAME=db
 -- check which is instance is running on current node
#srvctl status database -d db
-- set ORACLE_SID to the instance running on current node
#export ORACLE_SID=db_n
$emctl start dbconsole


------------------------------------------------------------------------------------------------------

 Related links :

HOME

11.2 RAC GOODIES AND SERVERPOOLS
11G R2 RAC PROBLEMS AND SOLUTIONS
CLONE DATABASE HOME IN 11GR2 RAC
NIC BONDING IN 11G R2 RAC
PREREQUISITE TO CONFIGURE GNS AND GPNP IN 11G R2 RAC
SERVICES CONFUSIONS CLEARED
SETUP 10G RAC ON YOUR LAPTOP
SETUP AND PLAY WITH 11G R2 RAC AT YOUR HOME PC
http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20-%20ADD%20INSTANCE%20MANUALLY

11g R2 RAC - LOCAL ARCHIVE WITH NFS
  http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20-%20LOCAL%20ARCHIVE%20WITH%20NFS
 
 11g R2 RAC : CACHE FUSION DEMONSTRATED
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20CACHE%20FUSION%20DEMONSTRATED


11G R2 RAC : DYNAMIC REMASTERING DEMONSTRATED

11gR2 RAC : USE RCONFIG TO CONVERT NON RAC DATABASE TO RAC DATABASE
http://koenigocm.blogspot.in/search/label/11gR2%20RAC%20%3A%20USE%20RCONFIG%20TO%20CONVERT%20NON%20RAC%20DATABASE%20TO%20%20RAC%20DATABASE

11g R2 RAC : RECOVER VOTING DISK - A SCENARIO
http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20RECOVER%20VOTING%20DISK%20-%20A%20SCENARIO

11g R2 RAC : TRACING SERVICES IN A RAC DATABASE
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20TRACING%20SERVICES%20%20IN%20A%20RAC%20DATABASE

11G R2 RAC: INSTANCE RECOVERY
 http://koenigocm.blogspot.in/search/label/11G%20R2%20RAC%3A%20INSTANCE%20RECOVERY

11g R2 RAC: MANAGING THIRD PARTY APPLICATIONS USING APPLICATION VIP
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%3A%20MANAGING%20THIRD%20PARTY%20APPLICATIONS%20USING%20APPLICATION%20VIP

Posted by at 6 comments:
Labels: ,

Monday, 2 July 2012

ORACLE DATABASE BUFFER CACHE




                      BUFEFR CACHE ARCHITECTURE



Database buffer cache is a memory structure in SGA. Buffers in DB cache are used to hold blocks of data read from the data files.Each buffer is sized to hold one database block. Oracle allows for a total of 8 different caches :

- 5 caches to allow different block sizes
- 3 caches to allow different behaviour

BUFFERS TO ALLOW DIFFRENT BLOCK SIZES :

- DEFAULT CACHE : To hold blocks of size DB_BLOCK_SIZE. Its size is determined by the parameter DB_CACHE_SIZE

- DB NK CACHE : To hold blocks of size = nK where n = 2,4,8,16,32 where n is not equal the size of the default block (DB_BLOCK_SIZE).  Its size is determined by the parameter DB_NK_CACHE_SIZE

BUFFERS TO ALLOW DIFFRENT BEHAVIOUR :

- DEFAULT CACHE : Cache where blocks of those objects are stored which are not assigned to keep or recycle cache or assigned default cache.

- KEEP CACHE : Cache to hold blocks of small sized objects for a longer time.Its size is determined by the parameter DB_KEEP_CACHE_SIZE.

- RECYCLE CACHE : Cache designed to quickly age out blocks of rarely accessed large sized objects. Its size is determined by the parameter DB_RECYCLE_CACHE_SIZE.

Each buffer in any of the above caches can be in any of the following states :

- FREE : Free buffers can be further of two kinds:
         - Empty or unused
         - Buffers which are identical to the blocks on disk because
           . they have not been dirtied or
           . they were dirtied and have been written to disk

- PINNED : A buffer being accessed (read/written) by a process.

- DIRTY : A buffer which holds a block that has been changed by a server process and hence needs to be writted to the disk before it can be reused.

Buffer headers store metadata about contents of the buffers and are used to manage the cache.The metadata includes information like Data block address (DBA) of the block, type of block (data,undo), touch count, pointer to the buffer, pointers to previous/next buffer in cache bufferchain etc. There is one row in V$BH for every buffer. Buffer headers keep track of various attributes and state of the buffers in buffer cache. The blocks are placed in the buffer cache in the form of linked lists (cache buffer chains) in different hash buckets. While searching for blocks in buffer cache oracle does not look at them buffer by buffer as it will bring down the performance drastically. Let us take the example of a library where we are looking for a blook. If we search the whole library book by book it will take very long but if the books are placed subject wise, we first need to go the rack containing books of the desired subject and then we search for the book we want. Our search will be over rather quickly. In the same manner, buffers are placed in different hash buckets depending upon the hash value of the data block address (DBA). While searching for a particular block, Oracle first hashes the DBA of the desired block, locates the hash bucket containing the block and then searches the blocks in that hash bucket. Different DBAs can hash to the same value (similar to modulus function i.e mod (5,4) = mod (9,4) = mod(13,4)....). Hence, one hash bucket can contain blocks with different DBA's. Each hash chain is protected by a latch. Processes need to get the relevant latch to allow them to scan a hash chain for a buffer so that while searching, the linked list underneath does not change. These latches are called cache buffer chain latches CBC latches). One CBC latch protects multiple hash buckets.

   To implement read consistency, a hash chain can also contain multiple versions of the  same block (DBA). For each DBA in a bucket, there is at least one current block which contains all the committed/uncommitted changes in different rows in that block made by different users. When a user requests a row from that block, he should receive a read consistent copy of the block i.e. the copy should have all the changes (committed / uncommitted)  made by him and only the committed changes made by other users. If the desired read consistent clone is already there, it is read and the user gets to see the results. If the read consistent clone is not there, a copy of the current block is made and marked as clone. Changes are rolled back on the clone after reading undo and then the user os displayed the results. In this case, No. of consistent gets are higher. If a block is rapidly changed and is rapidly queried, multiple CR copies can exist in buffer cache. Such a  block is called a hot block. All CR copies have the same DBA and hence go to the same hash bucket (CBC). The CBC gets longer and hence takes longer to scan. Multiple users trying to scan the CBC chain may have to wait to obtain the latch on the hash bucket. This can result in contention on CBC latch.

          CBC latch    Hash bucket          Buffer hash chains of           Buffers
                                                           buffer headers

            L1 +------- B1 ----------------- A <-> C                                     A
               |                                                                                         B
               +------- B2 ----------------- B <-> F <-> E <-> B                    C
                                                        |_____________|                     D
                                                                         |                               E
                                                                CR CLONES                     F

            L2 +------- B3
               |
               +------- B4

            L3 +------- B5
               |
               +------- B6

No. of buckets protected by each latch = 32 - is high so that

1. Hash chains linked to a bucket will be smaller.

2. No. of latches will be less since latch is a fairly large structure with along code path.

No. of hash buckets is decided by an uncodumented parameter _db_block_hash_buckets


  Whenever any block is modified, changes are always made on the current copy of the block and the statistic db block gets is incremented. Th information in hash buckets is actually the linked list of buffer headers pointing to blocks in DB buffercache. The latch on the hash chain can be obtained in

- SHARED MODE: to scan the buffers

- EXCLUSIVE MODE: To link/delink blocks to/from  the chain

To know more about consistent reads in oracle click the following link: http://koenigocm.blogspot.in/search/label/CONSISTENT%20READS%20IN%20ORACLE

                ALGORITHM FOR HASH LOOKUP
         ---------------------------------------

1. Hash the DBA (Tablespace-id + File-id + Block-id)

2. Find the corresponding hash bucket .

3. Try to get the latch protecting the hash bucket

   If not success

      - Spin for spin count times

        . If latch was not got while spinnning,
              then sleep, wakeup and go to step 3(try to get latch)

          else (latch obtained)
              go to step 4(walk the chain)

   else ( latch obtained)
       Go to step 4 (walk the chain)

4. Walk the chain, reading buffer headers to see if specific version of the buffer is already in the chain.

   If found (logical I/O)

      Access the buffer in buffer cache with protection of buffer pin/unpin action.

   else (not found) (Physical I/O)

      - find a free buffer in buffer cache (obtain LRU latch)
      - unlink the buffer header for that buffer from its current chain (LRU chain)
      - Link that buffer header with this buffer chain
      - Release the latch and read the block into that free buffer with buffer header unpinned.
   
                        WORKING SETS

Buffers in the buffer cache are divided into working sets to improve scalability of common buffer cache operation. Each working set has its own LRU list and two check point queues.

- One DB writer may be responsible to write buffers in multiple working sets. Therefore, write I/O workload can be distributed among various DB writers.

- Since each working set has  its own LRU list, competition between processes trying to look for free buffers is reduced as differebt readers pick buffers from different working sets.

     No. of working sets in 11g R2 = CPU_COUNT
     No. of DB writers in 11g R2 = CPU_COUNT/8

Buffers in DB buffer cache are managed using various doubly linked lists:

- LRU list : contains list of free / dirty / pinned buffers. It has a

    . Least Recently used (LRU) end which has cold buffers which have not been accessed for a long time.

    . Most recently used (MRU) end which have hot buffers which have been recently accessed.

  When a block is first read into the buffer, it is placed in the middle of the LRU list and moved to the LRU/MRU end depeneding upon how frequently and how recently it has been accessed which is further decided by a buffer's touch count. The touch count of a buffer is stored in its header .When a server process scans the LRU list to find free buffers, it moves the buffers to the MRU/LRU end depending upon its touch count. Hence, it is possible that some buffers on the LRU end might not be really cold. They may be there because they have not been searched by a server process yet.

  As instance activity increases, dirty buffers on LRU need to be flushed to the disk so that they can be reused. Since blocks need to be written to disk in the order in which they were dirtied, anothet list is maintained i.e.e checkpoint queue which contains dirty blocks in the order of SCN#.

 - Checkpoint queue : contains dirty buffers in the order in which they were dirtied (SCN#). Whenever a block is dirtied, it is attached here. A buffer can be simlultaneously in both LRU list and checkpoint queue.

- Write list - Main : contains dirty buffers which have to be written by DB writer to free some buffers in the buffer cache. When a Server process is scanning LRU list for a free buffer, dirty buffers in LRU list are  delinked from LRU list before linking them to this list.

- Write list - Aux : contains dirty buffers  to which have already been written to the online redo log files and are currently being written by DB writer to data files.

In the next article I will discuss about checkpointing.
----------------------------------------------------------------------------------------------------------

Related links:


HOME

BUFFER CACHE WAIT EVENTS
CASE STUDY ON BUFFER BUSY WAIT EVENT
CHECKPOINTS CONFUSIONS CLEARED
ORACLE CHECKPOINTS
ORACLE DATABASE BUFFER CACHE
http://koenigocm.blogspot.in/search/label/ORACLE%20DATABASE%20BUFFER%20CACHE

SIMULATE BUFFER BUSY WAIT AND IDENTIFY HOT OBJECT
http://koenigocm.blogspot.in/search/label/SIMULATE%20BUFFER%20BUSY%20WAIT%20AND%20IDENTIFY%20HOT%20OBJECT

 CONSISTENT READS IN ORACLE PART-I
http://koenigocm.blogspot.in/search/label/CONSISTENT%20READS%20IN%20ORACLE



CONSISTENT READS IN ORACLE PART-II 

FLUSH BUFFER CACHE

Posted by at 4 comments:
Labels: ,

Friday, 1 June 2012

11.2 RAC GOODIES AND SERVERPOOLS

Oracle RAC is designed to cater for Oracle specific applications and also non-oracle applications. These applications, both Oracle and non-Oracle, when deployed on RAC, are termed as resources and Oracle RAC can manage both kinds of resources on the clusterware stack. Examples of both oracle and non-oracle resources are: database instance, database services, xclock, Apache etc.

The traditional way to manage these resources was manually. The DBA was supposed to decide on which instance (or instances) these resources would run and would set them up accordingly. An example of this can be taken when you are configuring a service which has to be present on some nodes as Preferred and on another node(s) as Available. Although this kind of management works well, it’s not something that can scale for a cluster which has got a large number of nodes in it. Think about sitting and managing the instances over a 200 nodes cluster, it wouldn’t be very exciting would it?

Enter Server Pools

Grid computing is a concept within Oracle database which has been there since 10g. The basic meaning of grid computing is to divide the hard coupling of the availability of a resource over the machines thus letting the resources be available on a ‘wherever and whenever’ kind of basis. This means that there shouldn’t be a restriction on a said resource which must be present on a specific machine itself or can only be accessed from a specific machine. The very same concept is enhanced in 11.2 RAC with the introduction of Server Pools.

Server Pools allow the same functionality of logically dividing the cluster into small segments that can be used for varying workloads. But unlike the traditional mechanism available up to 11.1, which only allows this task by choosing the instances to run on nodes as Preferred & Available and running services using the same architecture, server pools offer a much larger list of attributes which help in the management of resources in a rather simple and transparent way. In server pools, the underlying hosts of the cluster are added (and removed) from the pools much more on-the-fly, and take the burden off the DBA’s shoulders for this task. With just a few parameters to take care of, the whole stack becomes much easier to govern and administer, while still being completely transparent yet powerful to manage all the types of different resources in the RAC clusterware environment, especially  when the number of the nodes go beyond two digits.

The built-in pools, Free and Generic

In 11.2, with the installation of the cluster, two pools get created by default-Free & Generic.

Generic Pool

When you upgrade your existing version of the clusterware to 11.2, all the nodes get mapped to the in-built Generic pool. This is an internally managed server pool and the modification of the attributes of this pool is not allowed. Besides the addition of the nodes of the previous cluster, in 11.2, when you create a database which is Admin-managed, that also becomes a part of the Generic Pool as a child pool of it.

Free Pool

This is another pool which gets created by default with the installation of the 11.2 clusterware. If it’s going to be a fresh installation of the clusterware environment, all the nodes would be attached to this pool first of all. From this pool only, the nodes would move to the manually created server pools. As like the Generic pool, this is also an internally managed pool but still some attributes are available to be modified by the dba like IMPORTANCE.

Child Pools

Server pools can have the child pools under them as well. The child pools are going to have their own properties. The order in which the host allocated to them is going to be based on the IMPORTANCE parameter assigned to them. To understand the child pools, we can imagine a parent pool named as OLTP which represents the working of OLTP database. This parent pool now can have two further child pools, HR (human resource) and AP (account payable) . Now, we can have different attributes set up for the child pools depending on the importance that we want to give to each domain of the work. The hosts assigned are mutually exclusive within the parent pools. For the child pools, whether the same is possible for the child pools or not, would be based on the fact that what’s the value of the attribute EXCLUSIVE_POOL for them. If there is a string value assigned to this parameter and this is shared by two child pools, host assigned to one child pool can’t be shared by its other sibling.

How the hosts are added to the server pools

The allocation of the hosts is going to be happening in the following order:

    Generic Pool
    Explicitly created pool
    Free Pool

Oracle would keep on adding the hosts to the server pools as long as they have not been equal to the values of the MIN_SIZE and MAX_SIZE on the basis of the IMPORTANCE that’s assigned to the server pools. The hosts are going to be added exclusively to the pools i.e. the hosts added to one server pool won’t be shared with the other pools. The hosts can be shared by the child pools provided that there is no value set for the parameter EXCLUSIVE_POOLS which would restrict the host sharing within the child pools as well.

Database management changes in 11.2 -to use server pools

To incorporate the usage of server pools, there is a change introduced in the database itself. From 11.2 onwards, there would be two types of databases available, Admin Managed and Policy Managed. Choosing either one would make you use the server pools though it would vary whether those server pools would be explicitly created by you or would be the default one available already.

Admin managed database

This is sort of the same as the traditional database type i.e. where a DBA is responsible to manage the allocation of the resources and instances explicitly on the available instances, and also the instances on the respective nodes. This database type would assign the instances to a child pool of the default pool available Generic pool. Having the Generic pool as the parent, no modification of the child pools of the Admin managed database’s instances would be allowed for the DBA to be done by their self. Depending on the number of nodes being chosen initially for the database, the instances would be started on each one of them. By default, with a fresh installation of 11.2 RAC, all the nodes would get added to the Generic pool and in the upgrade, all the nodes of the previous version would also be added to the Generic pool. Although this type seems to be much simpler and like the older versions and techniques of managing the instances, this can’t be used when the number of nodes pass a number which is difficult to manage by a DBA, for example 100. In that case, the next type of the 11.2 database would be helpful-Policy Managed database.

Policy Managed Database

This type of database administration is meant to make DBAs’ life simpler by not letting them worry about managing each of their instances on each node individually, but to use a server pool to manage the instances. By using server pools, the administrative tasks to manage the database becomes much simpler.

-----------------------------------------------------------------------------------------------------------

Related links:


11G R2 RAC PROBLEMS AND SOLUTIONS

CLONE DATABASE HOME IN 11GR2 RAC

NIC BONDING IN 11G R2 RAC

PREREQUISITE TO CONFIGURE GNS AND GPNP IN 11G R2 RAC

SERVICES CONFUSIONS CLEARED

SETUP 10G RAC ON YOUR LAPTOP

SETUP AND PLAY WITH 11G R2 RAC AT YOUR HOME PC

11g R2 RAC - ADD INSTANCE MANUALLY
http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20-%20ADD%

11g R2 RAC - LOCAL ARCHIVE WITH NFS
  http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20-%20LOCAL%20ARCHIVE%20WITH%20NFS

  11g R2 RAC : CACHE FUSION DEMONSTRATED
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20CACHE%20FUSION%20DEMONSTRATED
11G R2 RAC : DYNAMIC REMASTERING DEMONSTRATED

 11gR2 RAC : USE RCONFIG TO CONVERT NON RAC DATABASE TO RAC DATABASE
http://koenigocm.blogspot.in/search/label/11gR2%20RAC%20%3A%20USE%20RCONFIG%20TO%20CONVERT%20NON%20RAC%20DATABASE%20TO%20%20RAC%20DATABASE

11g R2 RAC : RECOVER VOTING DISK - A SCENARIO
http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20RECOVER%20VOTING%20DISK%20-%20A%20SCENARIO

11g R2 RAC : TRACING SERVICES IN A RAC DATABASE
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%20%3A%20TRACING%20SERVICES%20%20IN%20A%20RAC%20DATABASE

11G R2 RAC: INSTANCE RECOVERY
 http://koenigocm.blogspot.in/search/label/11G%20R2%20RAC%3A%20INSTANCE%20RECOVERY

11g R2 RAC: MANAGING THIRD PARTY APPLICATIONS USING APPLICATION VIP
 http://koenigocm.blogspot.in/search/label/11g%20R2%20RAC%3A%20MANAGING%20THIRD%20PARTY%20APPLICATIONS%20USING%20APPLICATION%20VIP

Posted by at No comments:
Labels: ,

Thursday, 5 April 2012

11g DATA GUARD ADMINISTRATION AND SETUP TIPS


Assume we have a production database i.e "PC01PRMY" which is keep up and running in 24x7 environment and their master DBA's has decided to setup physical standby i.e. "PC01SBY1" at remote location for disaster recover solutions as well as to make production available all the time while routine maintenance operations on production.



I have already posted on our blog, how to setup physical standby database for production but this time I would like to share few tips while setup and while administrating DR site.



Tips 1> Better to use Active database duplication to setup standby database in spite of using backup copy. As you know about Active database duplication where duplicate database is created using online copy of production data files and in fact it doesn't require any staging area as well and we also don't need to apply any redo logs. When you use active database duplication better to perform some log switching manually on primary side cause if there is no log switching happened so far on primary side, in this case your duplicate database failed and you have to restart it after cleanup everything.



Tips 2> Configure Oracle Net properly between Primary and Standby because what I found students normally does mistakes there and later on we face lot of difficulties to resolve them, especially when your database managed by Oracle restart where we have separate grid and database home and default listener is running from grid home.



Oracle Net configuration includes three main steps-



Configure Listener (Keep in mind Listener home and port number)

Register database service with Listener (Use correct service name and Oracle database home)

Configure TNS alias for database service (Use correct hostname, Listener port number and database service name)



Tips 3> Though LOG_ARCHIVE_DEST_STATE_N parameter default value is ENABLE but still I recommend you to set this parameter manually.



Tips 4> Better to setup Standby Redo log groups in advance on primary side. (You know better why?)



Tips 5> Better to configure LOCAL_LISTENER parameter manually and set the address of those Listener which we are using for Oracle Net connectivity so that PMON will register a service with the name "db_unique_name_DGB.db_domain" automatically at run time which is use by broker internally for inter-communication.



Tips 6> If you are managing Data Guard using Broker then please consider followings points-



=> when you remove database or entire broker configuration using REMOVE DATABASE or REMOVE CONFIGURATION better to use PRESERVE DESTINATIONS options else when you remove the database from configuration broker will reset their corresponding parameters like LOG_ARCHIVE_DEST_N, LOG_ARCHIVE_CONFIG etc.



=> Use SPFILE if you are managing Data Guard using broker and modify archive dest attributes through broker only else there would be inconsistency in broker configuration and spfile settings if we modify using ALTER SYSTEM command.



=> Identify inconsistent attribute settings between broker and spfile using SHOW DATABASE <DB_NAME> INCONSISTENTPROPERTIES. If you want to remove inconsistency with broker values then just disable and enable the database using broker and if you want to remove inconsistency with spfile values then update the values using EDIT DATABASE broker command.



=> Sometimes we need to recreate the broker configuration but even after recreating broker reads previous broker configuration from memory. To overcome from this problem you have to change broker configuration files location just to enforce broker to read new configuration settings.

--------------------------------------------------------------------------------------------------------

Related links:


HOME


11G DATA GUARD NEW FEATURES
DATA GUARD SETUP

RECOVERY USING STANDBY DATABASE
http://koenigocm.blogspot.in/search/label/RMAN

Posted by at 1 comment:
Labels: ,

Friday, 23 March 2012

11G DATA GUARD NEW FEATURES


Real-Time Query. In Oracle Database 8i it was possible to bring a standby database into READ ONLY mode so that it could be used for reporting purposes, but it was necessary to switch it back to standby mode for reapplication of pending change vectors from the archived redo logs transported from the primary database. Oracle Database 11g now lets me run queries in real time against any physical standby database without any disturbance to receipt and application of redo.



Snapshot Standby Databases. Oracle Database 11g offers another intriguing prospect: the ability to open a physicalstandby database for testing or QA purposes while simultaneously collecting production changes for immediate reapplication in case disaster recovery is required. This snapshot standbydatabase still accepts redo information from its primary, but unlike the first two standby types, it does not apply the redo to the database immediately; instead, the redo is only applied when the snapshot standby database is reconverted back into a physical standby. This offers significant leverage because in theory, a QA environment that requires specifically dedicated, identical hardware is no longer required.



Improved Handling of Role Transitions. The addition of standby snapshot databases brings the total of different Data Guard standby database types to three (physical, logical, and snapshot), so Oracle Database 11g also makes it much easier to transition between these different roles via either Data Guard Broker (DGB) command line execution or Enterprise Manager Grid Control. Role transitions are simpler to execute and complete more quickly than in earlier releases.



Improvements to Rolling Database Upgrades.Oracle Database 11g supports rolling database upgrades to be performed against a physical standby database by first transforming it into a logical standby database with a few simple commands before the upgrade begins. Once the upgrade is done, the logical standby database is reverted to its original physical standby state. Oracle 11gleverages this capability as well as the improved speed and simplicity of role transitions to perform system and database patching in a fraction of the time it would’ve taken in earlier releases, and it’s especially powerful in a Real Application Clusters (RAC) database environment.



SQL Apply Enhancements. Logical standby databases are obviously central to these new role transition features, but they use SQL Apply technology to apply change vectors to data. It therefore makes sense that Oracle Database 11g provides significant improvements to this crucial part of Data Guard architecture. SQL Apply now supports parallel DDL execution, Fine-Grained Auditing (FGA), Virtual Private Database (VPD), and Transparent Data Encryption (TDE), as well as simpler real-time SQL Apply reconfiguration and tuning.



Enhanced Redo Logs Transport. Physical standby databases have always used archived redo logs for application of change vectors to data. Oracle Database 11g augments redo transport with some long-overdue features, including compressionand SSL authentication of redo logs while they’re being transmitted between the primary and standby sites.



Heterogeneous DataGuard., Oracle Database 11g allows the primary and standby databases to use different operating systems (for example, Windows 2003 Server and Oracle Enterprise Linux) as long as both operating systems support the same endianness.



Fast Start Failover Improvements. Oracle introduced this feature set in Release 10gR2, but it’s been enhanced significantly in Oracle 11g to permit much finer-grained control over the conditions under which a fast-start failover would be initiated.



“Live Cloning” of Standby Databases. Finally, Oracle 11g has made it extremely simple to set up a standby database environment because Recovery Manager(RMAN) now supports the ability to clone the existing primary database directly to the intended standby database site over the network via the DUPLICATE DATABASE command set while the target database is active. This means it’s no longer necessary to first generate, then transmit, and finally restore and recover RMAN backups of the primary database on the standby site via tedious (and possibly error-prone!) manual methods; instead, RMAN automatically generates a conversion script in memory on the primary site and uses that script to manage the cloning operation on the standby site with virtually no DBA intervention required.
------------------------------------------------------------------------------------------------------------

Related links:


HOME


RECOVERY USING STANDBY DATABASE
http://koenigocm.blogspot.in/search/label/RMAN

Posted by at No comments:
Labels: ,

Friday, 24 February 2012

ORACLE 11GR2 DATABASE FLASH CACHE

Hi Dba's I was working on Database flash cache and found it very interesting feature of 11G so thought to share with you as well.  well database flash cache works as l2 level of cache for database and is very useful when memory become a bottleneck  in oltp-kinda database  by these feature we can configure a flash drive(either cheap as pen drive or may be expensive high quality flash drive) to  store clean buffer  that aged out from db_buffer cache
NOTE:-Database flash cache feature can only be configured in ORALCE ENTERPRISES LINUX or SOLARIS.
TO  be able to use database flash cache you should follow these steps.
1.login to database and shutdown the system in normal mode.
sql>shutdown immediate
2. Install Patch 9352237 by using opatch utility
[oracle@node1  9352237]$/u01/app/oracle/product/11.2.0/db_home/Opatch/opatch apply.
3.Install patch 8974084 which allows you to use the database flash cache on Oracle Enterprise Linux even if you don't have exadata storage.  The patch is named:
§     8974084:META BUG FOR FLASH CACHE 11.2PL BUGS TO BACKPORT TO 11.2.0.1 OEL


Once you install the patch you can use any old flash device as a database flash cache if  you just want to experiment using an USB flash device, then you first need to get that device mounted. On my test machine I created a directory "/mnt/usbflash" then created an /etc/fstab entry like this:
   /dev/sda1               /mnt/usbflash           vfat    noauto,users,rw,umask=0 0 0
On your system you might need to change "/dev/sda1" to another device depending on how your fixed disks are configured.  You should then be able to mount the flashdrive by typiing "mount /dev/sda1".  Make sure that the mount point is writable by oracle (chmod 777 /mnt/usbflash). 
Once mounted, you configure the flash cache by setting the parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE 
alter system set db_flash_cache_size='/mnt/usbflash/oraflashcache.dat;'
alter system set db_flash_cache_size=1000M;
Note that the value of DB_FLASH_CACHE_FILE needs to be a file on the flash drive, not the flash drive mount point itself.
Once these parameters are set, the flash cache will be enabled and will act as a secondary cache to the buffer cache.  When a block is removed from the primary cache, it will still exist in the flash cache, and can be read back without a physical read to the spinning disk.
Monitoring

There's a few ways to examine how the flash cache is being used.  Firstly,  V$SYSSTAT contains some new statistics showing the number of blocks added to the cache and the number of "hits" that were satisfied from cache:

SELECT   name,value   FROM   v$sysstat  WHERE   name IN ('physical read flash cache hits',
                  'physical reads',  'consistent gets',  'db block gets',  'flash cache inserts')
Although the flash reads save time when compared to db file sequential reads, the overhead of maintaining the cache can be high because flash based SSD has a relatively severe write penalty.
All flash-based Solid State Disk have issues with write performance.  However, cheap Multi Level Cell (MLC) flash take about 3 times as long to write as the more expensive Single Level Cell (SLC).  When flash drives are new, the empty space can be written to in single page increments (usually 4KB).  However, when the flash drive is older, writes typically require erasing a complete 128 page block which is very much slower.  My cheap USB drive was old and MLC, so it had very poor write performance.   But even the best flash based SSD is going to be much slower for writes than for reads, and in some cases using a  flash cache might slow a database down as a result.  So monitoring is important.
There's a couple of other V$SYSSTAT statistics of interest:

select name,value from v$sysstat where name like '%flash cache%'
To examine the contents of the cache, we can examine the V$BH view.  Buffers in the flash cache have STATUS values such as 'flashcur', allowing us to count the buffers from each object in the main buffer cache and in the flash cache
   SELECT   owner || '.' || object_name object, SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks,SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,count(*) total_blocks  FROM   v$bh b  JOIN   dba_objects   ON (objd = object_id)  GROUP BY   owner,  object_name order by 4 desc
Conclusion
  We should be cautious however, because the flash write penalty may result in performance problems.

 Related links:

 HOME
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)