DBAToolZ

If you’ve extended your RAC cluster on a set of new nodes you already know how painful it can be to have to go through the list of your clients and make sure their SQL*Net configuration is up to date. 11gR2 solves this problem using Single Client Access Name (SCAN).

The single client access name (SCAN) is a hostname used to provide service access for clients to the cluster. Because the SCAN is associated with the cluster as a whole, rather than to a particular node, the SCAN makes it possible to add or remove nodes from the cluster without needing to reconfigure clients. It also adds location independence for the databases, so that client configuration does not have to depend on which nodes are running a particular database. Clients can continue to access the cluster in the same way as with previous releases, but Oracle recommends that clients accessing the cluster use the SCAN.

Reference: 1.3.2.2 IP Address Requirements

How is SCAN implemented?

For high availability purposes the SCAN name should be associated with at least three IP addresses using DNS round-robin resolution. If you opt to use Grid Naming Service then GNS can also be used to manage the SCAN name.

SCAN is configured at a cluster level not at the node level, that’s what makes it so flexible — no mater how many nodes your clusters consists of, your clients can continue to use SCAN to access the services of your cluster utilizing all nodes even if you add or delete them:

The SCAN is a virtual IP name, similar to the names used for virtual IP addresses, such as node1-vip. However, unlike a virtual IP, the SCAN is associated with the entire cluster, rather than an individual node, and associated with multiple IP addresses, not just one address.

SCAN works as an independent handler for the entire cluster — it acts on client’s behalf during connection request since it knows all cluster services and it’s available, least loaded nodes:

The SCAN works by being able to resolve to multiple IP addresses reflecting multiple listeners in the cluster handling public client connections. When a client submits a request, the SCAN listener listening on a SCAN IP address and the SCAN port is contracted on a client’s behalf. Because all services on the cluster are registered with the SCAN listener, the SCAN listener replies with the address of the local listener on the least-loaded node where the service is currently being offered. Finally, the client establishes connection to the service through the listener on the node where service is offered. All of these actions take place transparently to the client without any explicit configuration required in the client.

Bottom line – use SCAN – it simplifies cluster management:

Because the SCAN addresses resolve to the cluster, rather than to a node address in the cluster, nodes can be added to or removed from the cluster without affecting the SCAN address configuration.

Reference: D.1.3.5 About the SCAN

September 10th, 2009 | Tags: 11gR2, SCAN, single client access name, vip | Category: RAC | Comments (4)

I was just reading up on the 11gR2 documentation for Grid Infrastructure Installation and finally we have a closure on the topic of RAW and BLOCK devices for OCR and VOTING disks:

With this release, OUI no longer supports installation of Oracle Clusterware files on block or raw devices. Install Oracle Clusterware files either on Automatic Storage Management diskgroups, or in a supported shared file system.

For new installations, OCR and voting disk files can be placed either on ASM, or on a cluster file system or NFS system. Installing Oracle Clusterware files on raw or block devices is no longer supported, unless an existing system is being upgraded.

REFERENCE: What’s New in Oracle Grid Infrastructure Installation and Configuration?

Perfect timing! I was just mulling over what to do with OCR/VOTING on my upcoming SAN-based RAC install — now it’s clear — use 11gR2 and store them on ASM.

September 10th, 2009 | Tags: 11gR2, grid, raw or block devices | Category: RAC | Comments (3)

In case you didn’t get this via oracle.com news updates — 11gR2 is out and available for download from here ….

And as I have predicted, the first distro is Linux x86 and Linux x86-64. So if you are a die-hard Solaris fan it should now be obvious (if it wasn’t already) that Linux is now officially the preferred platform (as Solaris once was but no longer is).

I actually wasn’t expecting this in September — I thought it was going to get announced closer to Oracle Open World but I am not complaining here

September 10th, 2009 | Tags: 11gR2, Linux, solaris, SUN vs Linux | Category: Linux | Leave a comment

Sometimes basic things like installing the latest Oracle instantclient on the PCs of all of your developers can take considerable time. I typically setup a dedicated DBA Portal website wherever I work and then write up instructions for repetitive things like these. It’s all about — do it once and forget it. Here’s the copy of the writeup I did to install the latest 11g Oracle instantclient basic and instantclient sqlplus on win32:

Go to Instant Client Downloads for Microsoft Windows (32-bit) download page:

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

Download basic-win32 and sqlplus-win32 files to your PC:

instantclient-basic-win32-11.1.0.7.0.zip
instantclient-sqlplus-win32-11.1.0.7.0.zip

Create a C:\oracle directory on your C drive (if you don’t already have one)

move both files into C:\oracle

Unzip both files

1) first unzip instantclient-basic-win32-11.1.0.7.0.zip
2) then unzip instantclient-sqlplus-win32-11.1.0.7.0.zip

to unzip right click on file
point to WinZip
Click Extract to here …

End result should be a new folder called instantclient_11_1 in your C:\oracle:

Setup your system %PATH% and %TNS_ADMIN% variables

In Windows explorer LEFT pane, right click on “My Computer” icon and click “Properties”
Click “Advanced” tab
Click Environment variables button:

In the “System Variables” panel select “Path” variable and click “Edit” button:

In the “Variable Value” field paste the following:
C:\oracle\instantclient_11_1
BEFORE all other path values that point to your OLD Oracle clients, then click “Ok”

Now find a variable called TNS_ADMIN — it could be in either panel (System / User),
if you have no such variable, create it in the User panel by clicking “New”:
Variable Name: TNS_ADMIN
Variable Value: C:\oracle\instantclient_11_1
Click Ok

Now click “Ok” on the Environment Variables screen
and click “Ok” on the System Properties window to close it

Setup tnsnames.ora file

If you have an existing Oracle client copy tnsnames.ora file from there and place it to:

C:\oracle\instantclient_11_1

If you don’t have an existing Oracle client create a new tnsnames.ora file, for example:

- - - - - - - - - - - - - - - - - - - - - - - 
XRACQ_MYSERVICE_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac4)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac3)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XRACQ_MYSERVICE_TAF.domain.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Test 11g client by running sqlplus

Open a new CMD window by going to Start->Run
In the Run Window type in: cmd and press “Ok”:

in the CMD window prompt type in:

sqlplus user/pass@TNS_ALIAS

where:

user: is the database username given to you by the DBA
pass: is the database password given to you by the DBA
TNS_ALIAS: The alias you setup in the tnsnames.ora file

example:

   C:\>sqlplus xxx/xxxxxxxx@XRACQ_MYSERVICE_TAF
 
   SQL*Plus: Release 11.1.0.7.0 - Production on Wed Aug 26 16:24:23 2009
 
   Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
 
   Connected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
   With the Partitioning, Real Application Clusters, OLAP, Data Mining
   and Real Application Testing options
 
   SQL>

If you get the ORA-12705 error while running sqlplus test:

C:\>sqlplus xxxxxx/xxxxxxxx@xxxxxxxxxxx
 
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Aug 26 16:58:33 2009
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
 
Enter user-name:

Here’s the solution:

To fix the “ORA-12705: Cannot access NLS data files or invalid environment specified” error, go to your registry editor (run regedit from Start->Run) and then:

- Navigate to Windows Registry in \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
- Remove the NLS_LANG from there (select it and delete)

Re-run the sqlplus test to make sure it works (you will need to open a new CMD window for the registry changes to take place).

End.

August 26th, 2009 | Tags: instantclient basic, instantclient sqlplus, oracle instant client, win32 | Category: Operations | Comments (61)

MAA recommends to enable Flashback Database on the primary prior to FailOver:

Enable Flashback Database to reinstate failed production databases after a
failover operation has completed. Flashback Database provides a second
very significant function, enabling fast point in time recovery if needed.

See: MAA_WP_10gR2_SwitchoverFailoverBestPractices.pdf

Enabling Flashback Database involves setting up flash recovery area and setting a flashback retention target which specifies how far back you want to be able to restore your database using the Flashback Database feature.

Once Flashback Database is setup, the database starts to copy images of each altered block into the flashback logs — this works for all datafiles. When it’s time to Flashback the database, the copies of the blocks from flashback logs are used to reconstruct the datafiles to a state just prior to the desired flashback time, the redo/arch logs are then used to bring a datafiles to a consistent state.

WARNING::
Redo logs must be available for the entire time period spanned by the
flashback logs, whether on tape or on disk. (In practice, however, redo
logs are generally needed much longer than the flashback retention target
to support point-in-time recovery.)

There are also a number of operations you can perform on your database, such
as dropping a tablespace or shrinking a datafile, which cannot be reversed
with Flashback Database. After such an operation, the flashback database
window begins at the time immediately following that operation.

One thing to consider is that the only way to guarantee a database can be returned to a specific point in time is to use guaranteed restore points. In other words, don’t use “normal” restore points for this purpose and don’t rely on Flashback Database alone. The only constraint to how far you can go back, is the size of your disk space in the flash recovery area.

Again, WARNING:
Limitations that apply to Flashback Database also apply to guaranteed
restore points. For example, shrinking a datafile or dropping a tablespace
can prevent flashing back the affected datafiles to the guaranteed restore
point.

Creating a guaranteed restore point without having enough sufficient free space in the flash recovery area (FRA) will cause the FRA to fill completely, that’s because “No file in the flash recovery area is eligible for deletion if it is required to satisfy the guarantee [restore point]“. In many circumstances, this will cause your database to halt.

To save on space in FRA you can disable the Flashback Database and still create a guaranteed restore point. In this case, the first time a datafile block is modified, an image of this block before the modification is stored in the flashback logs. This saves on space because only one-time copy of every changed data block is stored there, but subsequent modifications to the same block do not cause the block contents to be logged again. This method works really well and it’s more efficient as long as your primary only needs to be able to return to the specific point in time at which the guaranteed restore point was created, such as to a before-state of a failed application upgrade that might have made changes to a database.

See: 5.1.1 About Flashback Database

That’s theory, how about some practice time? Follow along. First, we verify our setup (in this case no FRA [db_recovery_file_dest] is setup:

rac1.XRACP1-> sqlplus /nolog
 
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 12 19:25:07 2009
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
SQL> connect / as sysdba
Connected.
SQL>    select * from v$recovery_file_dest;
 
no rows selected
 
SQL> show parameter recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
SQL> select * from v$flash_recovery_area_usage;
 
no rows selected
 
SQL>

Lets create FRA (NOTE: since this is a RAC database, I am creating FRA on a clustered FS (OCFS2) /u02):

[root@rac1 log]# mkdir -p /u02/oradata/rcv_area
[root@rac1 log]# chown -R oracle:dba /u02/oradata
 
rac1.XRACP1-> df -k /u02
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/2000b080023002235p1
                      37899136   1971200  35927936   6% /u02
rac1.XRACP1->
 
 
SQL> alter system set db_recovery_file_dest_size=28g scope=both sid='*';
 
System altered.
 
SQL> alter system set db_recovery_file_dest='/u02/oradata/rcv_area' scope=both sid='*';
 
System altered.
 
SQL>
 
SQL> set lines 132
SQL> col name format a35
SQL> set trims on
SQL> select * from v$recovery_file_dest;
 
NAME                                SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------- ----------- ---------- ----------------- ---------------
/u02/oradata/rcv_area                3.0065E+10          0                 0               0
 
SQL> select * from v$flash_recovery_area_usage;
 
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0
 
6 rows selected.
 
SQL>

So far we setup the FRA, now lets try to create guaranteed restore point. One thing to remember here though is this — if flashback database is not enabled (to save on space), then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped). If you attempt to create a guaranteed restore point when the database is opened you get ORA-38787 error:

SQL> CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE;
CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'GR_01'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.

A normal restore point however, works, but it will not do anything since flashback database is off, here’s an example:

SQL> !ls -l /u02/oradata/rcv_area
total 0
 
SQL> CREATE RESTORE POINT nr_01;
 
Restore point created.
 
SQL> !ls -l /u02/oradata/rcv_area
total 0
 
SQL> create table xyz(t number) tablespace tools;
 
Table created.
 
SQL> !ls -l /u02/oradata/rcv_area
total 0
 
 
SQL> insert into xyz values (1000);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> !ls -l /u02/oradata/rcv_area
total 0
 
SQL>

As you can see nothing happened … even though a normal restore point is created. Lets drop it (this syntax works for both NORMAL and GUARANTEED RPs):

SQL> drop restore point nr_01;
 
Restore point dropped.
 
 
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
           GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
           FROM V$RESTORE_POINT;
  2    3
no rows selected

Now lets create GUARANTEED restore point WITHOUT enabling FLASHBACK DATABASE which requires database to be mounted only, and since this is RAC database all other instances must be shutdown first as well:

rac1.XRACP1-> srvctl stop database -d XRACP
rac1.XRACP1-> sqlplus /nolog
 
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 12 20:01:53 2009
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             822084152 bytes
Database Buffers          771751936 bytes
Redo Buffers               14692352 bytes
Database mounted.
SQL> CREATE RESTORE POINT gr_01 GUARANTEE FLASHBACK DATABASE;
 
Restore point created.
 
SQL> shutdown immediate;
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
rac1.XRACP1-> srvctl start database -d XRACP
rac1.XRACP1-> srvctl start service -d XRACP
rac1.XRACP1->

Now lets see what we can gather from V$RESTORE_POINT:

SQL> col name format a15
col time format a35
set lines 132
set trims on
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL>
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
           GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
           FROM V$RESTORE_POINT;
  2    3
NAME                   SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- ----------------------------------- --------------------- --- ------------
GR_01           9.0317E+12 12-JUN-09 08.02.19.000000000 PM                         1 YES     63766528
 
SQL>
 
 
rac1.XRACP1-> find /u02/oradata/rcv_area -ls
5161314    4 drwxr-xr-x   3 oracle   dba          4096 Jun 12 20:02 /u02/oradata/rcv_area
5161316    4 drwxr-x---   3 oracle   oinstall     4096 Jun 12 20:02 /u02/oradata/rcv_area/XRACP
5161317    4 drwxr-x---   2 oracle   oinstall     4096 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback
5161318 15576 -rw-r-----   1 oracle   oinstall 15949824 Jun 12 20:04 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365ov16_.flb
237846 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qm48_.flb
4128929 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qtxy_.flb
7227425 15576 -rw-rw----   1 oracle   oinstall 15949824 Jun 12 20:03 /u02/oradata/rcv_area/XRACP/flashback/o1_mf_5365qtyd_.flb
rac1.XRACP1->

Much better now — it is working, and a few days later:

SQL> !date;ls -l /u02/oradata/rcv_area/XRACP/flashback
Mon Jun 15 15:02:17 PDT 2009
total 358248
-rw-r-----  1 oracle oinstall 15949824 Jun 13 04:00 o1_mf_5365ov16_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qm48_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qtxy_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 20:03 o1_mf_5365qtyd_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 22:00 o1_mf_536dm0jm_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 22:01 o1_mf_536dmnxj_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 02:00 o1_mf_536dntxs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 23:03 o1_mf_536j9b8b_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 12 23:28 o1_mf_536j9jvj_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 06:00 o1_mf_536kqnmf_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 15:10 o1_mf_536tnpky_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 21:00 o1_mf_5371or32_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 13 23:03 o1_mf_5378qrx2_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 12:00 o1_mf_537m8tox_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 07:33 o1_mf_5388yvsd_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 19:00 o1_mf_538xgm1v_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 02:00 o1_mf_5394pdx7_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 14 23:04 o1_mf_539h0tcs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 04:00 o1_mf_53b2lorr_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53bl5v8y_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53cbs5hs_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53cs3y00_.flb
-rw-rw----  1 oracle oinstall 15949824 Jun 15 15:00 o1_mf_53dbfmx5_.flb
 
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
              GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
              FROM V$RESTORE_POINT;  2    3
 
NAME                   SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- ----------------------------------- --------------------- --- ------------
GR_01           9.0317E+12 12-JUN-09 08.02.19.000000000 PM                         1 YES    366657536
 
SQL> select * from v$flash_recovery_area_usage;
 
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG               1.22                         0              23
 
6 rows selected.
 
SQL>

And that’s how you create a guaranteed restore point. But what do you use it for, with regards to STANDBY FAILOVER? You really shouldn’t, because you don’t know when a FAILOVER occurs so you can’t create a point right before it. You should however, use a guaranteed restore point during a SWITCHOVER because during a switchover you are in full control of both primary and standby databases. In a FAILOVER scenario, enabling Flashback Database [ ALTER DATABASE FLASHBACK ON; ] is more effective than trying to create guaranteed restore points.

Still have questions? Here are pointers to additional reading with excerpts:

From Note:565535.1 titled “Flashback Database Best Practices & Performance”:

Manual primary database reinstate- if Data Guard is being used without the
fast-start failover feature and a Data Guard failover is necessary, then
flashback database can be used to manually reinstate the failed primary
database. This is documented in the Data Guard Administration and Concepts
guide – see “12.4 Using Flashback Database After a Failover” here:

And from 12.4 Using Flashback Database After a Failover:

After a failover occurs, the original primary database can no longer
participate in the Data Guard configuration until it is repaired and
established as a standby database in the new configuration. To do
this, you can use the Flashback Database feature to recover the
failed primary database to a point in time before the failover
occurred, and then convert it into a physical or logical standby
database in the new configuration.

August 22nd, 2009 | Tags: Failover, Flashback Database, FRA, guaranteed restore point, MAA, Switchover | Category: Data Guard, RAC | Comments (1)

“Data Guard — it’s real it’s Oracle, you know what you’ve got!” said Joe Meeks (Director of High Availability Product Management at Oracle) in his closing statement during the Live Webcast presentation titled “Maximize Availability with Oracle Database 11g” held today.

The focus of this presentation was on “Active Data Guard Option” of Oracle 11g. In one sentence — Active Data Guard Option lets you open your Physical Standby database READ-Only all while redo-apply is taking place from primary. In contrast, in 10g, redo-apply halted when physical standby was opened READ-Only, the redo transportation was still taking place but it was not being applied in 10g. 11g bridges this gap and it does so very efficiently — as Joe Meeks said “With 11gR2 Active Data Guard – we are confident that latency is very low”.

Data Guard != ZERO Downtime because rolling upgrade still requires downtime. For example, according to Joe’s recollection of a major upgrade at UPS — their downtime going from 10g to 11g took ~ 4 minutes. To get ZERO downtime you must design your application around Oracle Streams.

In contrast to Data Guard, which uses media recovery (block by block), Oracle Streams are building logical change records using SQL. Steams are providing full featured replication features at your disposal, for example you can:

• replicate a subset of database
• perform transformations
• replicate across platforms
• setup multimaster replications
• replicate to a database and have it replicate different subsets of data out

Data Guard is simpler, one way replication — focused on DR using media recovery process (time tested process). Physical Standby is very horizontal, transparent to the storage and application.

PS: Active Data Guard is only available for Physical Standby (block by block changes) and it requires an additional license.

August 19th, 2009 | Tags: 11gR2, Active Data Guard, Joe Meeks, Oracle Streams, Physical Standby, READ-Only, ZERO downtime | Category: Data Guard | Leave a comment

It’s widely believed that v$active_session_history.TIME_WAITED is presented in microseconds. It sure seems like it, but something is amiss as my small test shows. Using two sessions I created a test table in one, inserted a row and then from another session tried to select the same row for update:

| session A > SQL> create table ash_time_waited_test (t number);
| session A > 
| session A > Table created.
| session A > 
| session A > SQL> insert into ash_time_waited_test values(1);
| session A > 
| session A > 1 row created.
| session A > 
| session A > SQL> commit;
| session A > 
| session A > Commit complete.
| session A > 
| session A > SQL> update ash_time_waited_test set t=2 where t=1;
| session A > 
| session A > 1 row updated.
 
| session B > SQL> !date
| session B > Thu Jul 30 18:20:15 PDT 2009
| session B > 
| session B > SQL> select * from ash_time_waited_test for update;
 
| session A > 
| session A > SQL> commit;
| session A > 
| session A > Commit complete.
| session A > 
| session A > SQL>
| session A > 
 
| session B > 
| session B >          T
| session B > ----------
| session B >          2
| session B > 
| session B > SQL> !date
| session B > Thu Jul 30 18:20:38 PDT 2009
| session B >

What I found next was rather strange — the sum(TIME_WAITED) from ASH (v$active_session_history) did not add up to the total time session A kept the lock on the test table:

First we get the SQL_ID for session B (the waiter) so we can tie into ASH.SQL_ID later:

SQL> select sql_id from v$sql where sql_text = 'select * from ash_time_waited_test for update';
 
SQL_ID
-------------
fpm89fyrsf6gv

Now having the SQL_ID in hand we can dig into ASH to see what waits our session experienced:

SQL> col SAMPLE_TIME format a25
SQL> col event format a30
SQL> select sample_time,EVENT,WAIT_TIME,TIME_WAITED from v$active_session_history
  2  where sql_id = 'fpm89fyrsf6gv'
  3  order by sample_time;
 
SAMPLE_TIME               EVENT                           WAIT_TIME TIME_WAITED
------------------------- ------------------------------ ---------- -----------
30-JUL-09 06.20.25.751 PM enq: TX - row lock contention           0      489233
30-JUL-09 06.20.26.761 PM enq: TX - row lock contention           0      489244
30-JUL-09 06.20.27.761 PM enq: TX - row lock contention           0      489242
30-JUL-09 06.20.28.761 PM enq: TX - row lock contention           0      489238
30-JUL-09 06.20.29.761 PM enq: TX - row lock contention           0      489244
30-JUL-09 06.20.30.761 PM enq: TX - row lock contention           0      489242
30-JUL-09 06.20.31.761 PM enq: TX - row lock contention           0      489244
30-JUL-09 06.20.32.761 PM enq: TX - row lock contention           0      489253
30-JUL-09 06.20.33.761 PM enq: TX - row lock contention           0      489233
30-JUL-09 06.20.34.761 PM enq: TX - row lock contention           0      489245
30-JUL-09 06.20.35.761 PM enq: TX - row lock contention           0      489245
 
11 rows selected.
 
SQL>

Based on the output above we know that the first sample that trapped this wait occurred at “06.20.25.751 PM” and the last one at “06.20.35.761 PM”. The difference (delta) between these two timestamps is just over 10 seconds:

SQL> select e.SAMPLE_TIME - s.SAMPLE_TIME
  2  from v$active_session_history s,
  3       v$active_session_history e
  4  where s.sample_id = (select min(sample_id) from v$active_session_history where sql_id = 'fpm89fyrsf6gv')
  5    and e.sample_id = (select max(sample_id) from v$active_session_history where sql_id = 'fpm89fyrsf6gv')
  6  ;
 
E.SAMPLE_TIME-S.SAMPLE_TIME
---------------------------------------------------------------------------
+000000000 00:00:10.010
 
SQL>

So far so good. Now lets see what we get by summing up the TIME_WAITED:

SQL> select sum(TIME_WAITED) from v$active_session_history
  2  where sql_id = 'fpm89fyrsf6gv'
  3  ;
 
SUM(TIME_WAITED)
----------------
         5381663

Interesting? If the ASH.TIME_WAITED is measured in microseconds then the total wait time = 5381663/1000000 = 5.381663 seconds and as we know it’s not correct. In fact, we can prove this using another metric, the ELAPSED_TIME_* from DBA_HIST_SQLSTAT:

SQL> select ELAPSED_TIME_TOTAL, ELAPSED_TIME_DELTA,
  2  APWAIT_TOTAL, APWAIT_DELTA,
  3  CCWAIT_TOTAL, CCWAIT_DELTA
  4  from DBA_HIST_SQLSTAT
  5  where sql_id = 'fpm89fyrsf6gv'
  6  ;
 
ELAPSED_TIME_TOTAL ELAPSED_TIME_DELTA APWAIT_TOTAL APWAIT_DELTA CCWAIT_TOTAL CCWAIT_DELTA
------------------ ------------------ ------------ ------------ ------------ ------------
          11110172           11110172     11106973     11106973          254          254
 
SQL>

DBA_HIST_SQLSTAT is dead on — with Elapsed Time Total = 11110172/1000000 = 11.110172 seconds … So what’s happening with ASH then? Why such a large > 2 times discrepancy?

I have my theory — I think that because ASH sampling is taken every second half of that seconds is actually taken to process the data and for this reason the TIME_WAITED gets truncated. Whatever the reason is, one thing is for sure — ASH.TIME_WAITED, at least in this particular case, cannot be trusted.

The final question … how do you then use ASH? How do you arrive at a more or less accurate metrics, specifically the TIME_WAITED? One way is this — we know what ASH sampling takes place every second, so instead of relying on the buggy TIME_WAITED values we can rely on the actual number of times that a SQL_ID appeared in ASH … If a SQL_ID was trapped by ASH 10 consecutive times with WAIT_TIME=0 (Waiting) we then know for sure that it waited for at least 10 seconds …

UPDATE Aug 12, 2009

Graham Wood from Oracle explains the issue with ASH: TIME_WAITED in his paper Sifting through the ASHes (see Page 35). He also lists some other “ASH data gotcha’s” on page 27. What really caught my attention through, was page 36, which explains “ASH vs AWR/Statpack” and it specifically says that for “Counts/occurrence data” analysis you should go to AWR not ASH.

And it really makes sense, after all, ASH is sampled every second and while it sounds like “often enough” it really isn’t. All you have to do is run sql_trace on a busy session and you’ll see that there’s a lot that goes on between the 1 second intervals — all of it would be missed by ASH. ASH is great at putting spotlight on the “big issues”, not so great for subtle, sub second waits which could and do add up to a considerable wait time.

Bottom line for me is that there’s no substitute for the good, old sql_trace, but overall, ASH is a huge improvement over what we had or rather didn’t have before. It definitely replaces my own monitoring scripts which I’ve used for many years now, sampling v$session_wait where wait_time=0 every 5 seconds.

July 31st, 2009 | Tags: ASH, DBA_HIST_SQLSTAT, microseconds, SQL_ID, TIME_WAITED, v$active_session_history, WAIT_TIME | Category: Tuning | Comments (5)

I finally got out of the dungeon and made it to an Oracle hosted event titled “Oracle Technology Day – 11g Upgrade & Best Practices” which was hosted by Tom Kyte and Oracle Center of Excellence (COE) folks in Los Angeles on June 16 ‘09. And it was definitely worth it. I got a chance to finally meet Tom Kyte face to face and I even had my original, WROX “Expert One-One-One” signed by him. The place was packed and I even heard that the event organizers had to turn some people down because there was no room left.

The presentation was focused on 11g upgrade — I guess Oracle has had it with us “conservative” DBAs that stick to our guns and don’t upgrade until the R2 is out. Well, they sure got my attention with the “Revolutionary New Capabilities brought to you by the upcoming 11g R2” … But before I get to that I figured I’ll run through the notes of the stuff that was most interesting to me as a DBA, so instead of just stuffing the notepad someplace where I will never find it I figured it would be beneficial to both you and I if I wrote it up here:

Interesting stuff to research:
Quiesce
Incident Packaging
Total Recall
Data Masking

Capture & Replay Workload AKA Real Application Testing:
You can now achieve realistic testing with Database Capture and Replay. DB Capture is capable of recording bind variables, SCN and order of operations on your production instance and then using DB Replay you can reproduce the same exact load on a test system which will allow you to compare metrics (v$..), test upgrades and even test capacity plan.

Database Capture is available as of 9.2.0.8/up and it can be used across platforms, so you can easily use Captured workload from 9.2.0.8 and replay it on 11g test instance. Replay only works on 11g (11.1.0.7 see Note:738538.1).

The core of Real Application Testing is SPA (SQL Performance Analyzer) [pronounced like "heath SPA"] and SQL Tuning Advisor. SPA can be used in conjunction with Replay to look for regressed SQL, allowing you to build acceptable optimizer plans for the new release of the database before you upgrade. Replay commits changes — use Flashback DB to revert back and replay. 10.2.0.5 Grid Control can automate the Replay and restore of the database using GUI.

11g R2 will be able to UP the workload so it can be effectively used for future capacity planning.

Advise: when making comparisons using these tools rely on CPU TIME and BUFFER GETS rather than ELAPSED TIME.

Replay clients are called WRC. You can start multiple WRC processes to simulate concurrency. Filters can be used during Capture to limit what is being recorded. 11g R2 will also have selective replay capability.

Data Recovery Advisor:
If you want a second pair of “eyes” or have no idea what to do when your database requires recovery — Data Recovery Advisor might be able to help. Of course this requires you to use RMAN to backup your database in the first place, but you should be already doing that anyway right? But it also requires RMAN Catalog …

Flashback Data Archive:
Flashback is designed to “cure” the “right command / wrong database syndrome“, where you urgently want to UNDO a mistake.

9i – brought to us Flashback Query
10g – Flashback Tables and DB
11g – brings Flashback Data Archive & Transactions

Flash Data Archive is enabled on a table by table basis, where once a table is placed in the Data Archive a shadow table is created where all changes are recorded and kept. It works through a new background process and has very little (if any) overhead because it mines the UNDO. This is the best option for auditing and should replace all of your DIY audit triggers. While Audit triggers can be disabled or dropped, once a table is placed in the flash data archive it cannot be dropped even by the SYS DBA – perfect AUDIT.

Revolutionary New Capabilities brought to you by the upcoming 11g R2:
Expect to hear a lot of buzz about 11g R2 by October ‘09. 80% of the Open World “talk” will be about 11g R2. What can we expect? How about “Upgrade Database Application” Online using a new, “revolutionary” feature called “ADDITION“?

create ADDITION v2;
alter session set ADDITION = v2;
– from now on everything takes place in this new virtual schema called v2 –
[compile new PKGs]
[add indexes and tables ???] Tom mentioned later that ADDITIONs only work for things that “live” in SYSTEM, i.e. compiled and virtual stuff, so this might not work for tables and indexes …
[v1/original is still fully operational and running]
[test v2]
[pause DB]
alter database set schema XYZ to V2 addition; [not real syntax ...]
DONE.

Obviously, this will only work if you use PL/SQL. V2 becomes the current ADDITION once it’s enabled. V1 can be kept around if you desire so. This is NOT a source code control — it’s only designed to easily deploy database applications without disrupting your online users.

11g Upgrade Best Practices:
Upgrading to 10g? Review Note:466181.1
Upgrading to 11g? Review Note:601807.1

Oracle support really, really wants you to use OCM (Oracle Configuration Manager). They also want you to use the new Metalink (I don’t use it just in case you wanted to know …).

USE DBUA (I agree). DBUA == easy. Manual Upgrade == hard.

Backup your optimizer stats — see Note:465787.1

Don’t set any optimizer_* or db_multiblock_* params that you found on google. This is BAD practice. Most of the default values are good for 99% of installations — don’t touch them.

Gather dictionary statistics before you upgrade, it can dramatically improve the time it takes to upgrade your database.

Post upgrade run:
execute DBMS_STATS.GATHER_SYSTEM_STATS(start); [not exact command]
execute DBMS_STATS.GATHER_SYSTEM_STATS(stop); [not exact command]

On 11g use AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT — this is 5-6 times faster and close to 100% accurate.

Export AWR repository BEFORE the upgrade.

Plan Stability:
On 11g use Plan Stability feature — see DBMS_SPM. SPM allows you to create SQL Baseline where only known and accepted plans are used for specific SQL. This can be either automated or setup manually by DBA. SPM works in three phases:

1. Capture (stored in SYSAUX)
2. Selection (DBA marks what’s accepted)
3. Evolution (EVAL all unverified plans to see if they are worth considering)

Capture: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true
Selection: OPTIMIZER_USE_SQL_PLAN_BASELINES = true + The Plan must be ENABLED=YES AND ACCEPTED=YES to be used by SPM.
Evolution: Scheduled by DBA via EVAL procedure.

See Note:456518.1.

You can also pack the stored SQL Plan Baselines on DEV and then unpack them into PROD. Great thing to ask of your vendors — provide the SQL Plan Baselines with all your code.

If you are going to 11g from 10g you can utilize STS — create STS (SQL Tuning Set) on 10g, unload them to staging table and load them into 11g’s plan baseline.

Some Random Bits and funny Pieces:
Evolution of APEX:
Browse (good) -> WebDB (good) -> Portal (messy) -> dead end
few years later …
HTML DB (good) -> APEX (better)

The guy who wrote APEX (Mike Hichwa) is also largely responsible for existence of SQLPLUS and SQL Developer.

Bryn Llewellyn owns PLSQL.

DBMS_OUTPUT.put_line (danger)
sys.DBMS_OUTPUT.put_line (safe)

Tom Kyte:

This is a really big table. Really? How big? It’s really big — imagine the largest table and insert one more record into it — that’s how big.

Rexx is my favorite language …

June 17th, 2009 | Tags: 11g R2, APEX, Capture, Data Masking, DBMS_SPM, DBMS_STATS, DBUA, Flashback Data Archive, Incident Packaging, Oracle ADDITION, Plan Stability, PLSQL, Quiesce, Real Application Testing, Replay Workload, Tom Kyte, Total Recall | Category: Events | Leave a comment

Google’s share nothing approach to application development has lead to the #1 search engine solution both in performance and functionality. Notice that I said “application development” because for the share nothing approach to work it needs to be built into the application from day one not as an afterthought.

On the other end of the spectrum we have ERP APPS where design with thousands of tables per module are the norm and the UNION ALL joins span a multi-page printout. In these types of applications Oracle RAC’s “share everything” approach is clearly superior, scratch that, it’s the only solution, period.

For an interesting read on this specific issue take a look at Kevin Closson’s post titled “Nearly Free or Not, GridSQL for EnterpriseDB is Simply Better Than Real Application Clusters. It is Shared-Nothing Architecture After All!” and a sort of reply to it by a blogger called “bonglonglong” titled “All in the assumptions“.

June 15th, 2009 | Tags: ERP, google, share everything, share nothing | Category: RAC | Leave a comment