One is All You Need

Step by Step Guide for Installing a Data Guard/ Data Guard Broker Environment

This focuses on setting up a test environment on a single node. The DBA can adapt this to set up a production environment.

By Roger Lenihan

Step 1:

Install an Oracle database and validate it is in Archive log Mode.

If Archive log is disabled enable it before continuing.

SQL> archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           C:\oracle\product\10.2.0\archive

Oldest online log sequence    66

Next log sequence to archive  68

Current log sequence          68

Step 2:

Enable force logging mode.

SQL> alter database force logging;

Database altered.

Step 3:

Identify all the datafiles to copy for a cold back up or do an RMAN backup.

Since this is test environment I will shutdown the database and do a cold backup.

SQL> select name from v$datafile;

NAME

———————————————————————-

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF

SQL>

Step 4:

In a windows environment create the service for the standby database. You will also have to create the folders and environment for the standby database.

C:\>oradim -new -sid stby -intpwd teststby -startmode manual

C:\oracle\product\10.2.0\oradata\teststby – Create this folder

C:\oracle\product\10.2.0\admin\teststby – Create this folder and all the folders underneath. Like bdump, udump, etc. Or just copy the production one.

Note: Since this is a dataguard environment on a single test node I do not have to install all the Oracle software again.

Step 5:

Shutdown Primary database and move the datafiles

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Move the datafiles from step 3 into the oradata standby environment.

C:\oracle\product\10.2.0\oradata\test

C:\oracle\product\10.2.0\oradata\teststby

Step 6:

Create a standby control file and start the database

SQL> startup mount;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

    ‘ C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl’;

Database altered.

SQL> alter database open;

Step 7:

Create the Primary and Standby init.ora/spfile to facilitate Dataguard.

SQL> CREATE PFILE= ‘C:\oracle\product\10.2.0\testinit.ora’

from spfile;

File created.

Primary Database:

Make the corresponding changes: 

*.aq_tm_processes=1

*.background_dump_dest='C:\oracle\product\10.2.0/admin/test/bdump'

*.compatible='10.2.0.1.0'

*.control_files='C:\oracle\product\10.2.0/oradata/test/\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.2.0/admin/test/cdump'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

*.instance_name='test'

*.db_unique_name=’test’

*.job_queue_processes=10

*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(test,teststb)’

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive arch mandatory
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.LOG_ARCHIVE_DEST_2=’service=teststby lgwr sync affirm net_timeout=30
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.2.0/admin/test/udump'

*.sga_target=611319808

*.fal_server=teststby

*.fal_client=test

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

Standby Database:

Create a standby init.ora from a copy of the primary one and make the necessary changes below.

*.aq_tm_processes=1

*.background_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’C:\oracle\product\10.2.0/oradata/teststby/\control01.ctl’

*.core_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/cdump’

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’test’

*.DB_UNIQUE_NAME=’teststby’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’

*.fast_start_mttr_target=300

*.instance_name=’teststby’

*.job_queue_processes=10

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive2 arch mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.LOG_ARCHIVE_DEST_2=’service=test lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.remote_login_passwordfile=’EXCLUSIVE’

*.processes=150

*.query_rewrite_enabled=’FALSE’

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled=’FALSE’

*.timed_statistics=TRUE

*.undo_management=’AUTO’

*.undo_retention=10800

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/udump’

*.sga_target=611319808

*.standby_archive_dest=’location=c:\oracle\product\10.2.0\archive’

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.standby_file_management=AUTO

*.log_archive_config=’DG_CONFIG=(test,teststby)’

*.service_names=teststby

*.fal_server=test

*.fal_client=teststby

Step 8:

Convert the control file created from step 6 to the name and location of control file parameter in the new standby init.ora.

C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl

to

C:\oracle\product\10.2.0\oradata\teststby\control01.ctl

Step 9:

Configure the Listener.ora file. Since the example is on the same node I have both the standby and primary in the listener.ora. If this was a production environment you would create the listener for primary and standby separate.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = test_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

)

Note: The DGMGRL entries are for the Data Guard Broker to be able to start and stop the databases on switchovers.

Step 10:

Stop and restart the listener

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:40

:55

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:41

:32

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or

a

Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

ction

Start Date 30-MAR-2008 10:41:34

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o

ra

Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Services Summary…

Service “PLSExtProc” has 1 instance(s).

Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

Service “test” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “test_DGMGRL” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby_DGMGRL” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Step 11:

Add the standby database to the tnsnames.ora file.

teststby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = teststby)

)

)

Step 12:

Shutdown the primary database and start again with the new init.ora.

Convert it back to a spfile.

C:\> set oracle_sid=test

C:\> sqlplus

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=C:\oracle\product\10.2.0\testinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\testinit.ora;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

Note: You have to use a spfile to use Data Guard Broker.

Step 13:

Create standby redo logs if you want to run the configuration in MaxAvailibility or MaxProtection. Basically you have to create them if you want to run in SYNC mode.

SQL> Select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME

———- ———- ———- ———- ———- ——– —————- ————- ———–

1 1 108 52428800 1 NO CURRENT 658611 4/9/2008 10

2 1 106 52428800 1 YES ACTIVE 658530 4/9/2008 10

3 1 107 52428800 1 YES ACTIVE 658552 4/9/2008 10

SQL> Select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

———- ——- ——- ——————————————————————————– ———————

3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG NO

2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG NO

1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG NO

Now we create the standby redologs

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB01.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB02.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB03.LOG’
size 52428800;

Once you mount standby you will have to open it in read only and issue the above commands
in the standby database location. 

i.e –

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTSTBY\REDOSTB01.LOG’ size 52428800;

--- Do this for all 3

Step 14:

Start up the standby database using its init.ora file that you created in step 7.

Convert the init.ora file to a spfile.

Shut down the database and restart it using the spfile.

C:\> set oracle_sid=teststby

C:\> sqlplus

SQL> startup nomount pfile=C:\oracle\product\10.2.0\teststbyinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

SQL> alter database mount standby database;

Database altered.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\teststbyinit.ora;

Database altered.

SQL> Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup nomount;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

Now the standby database is started under the spfile.

Step 15:

Mount the standby database and recover it.

This will bring the standby database into standby mode.

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL>

—- Go back to step 13 and add standby redo logs on standby database.

Step 16:

Now that Data Guard is all configured, we set up the Broker component.

First, we create the configuration.

C:\>dgmgrl

DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys@test

Password:

Connected.

DGMGRL> CREATE CONFIGURATION ‘DGBTEST’ AS

PRIMARY DATABASE IS ‘TEST’

CONNECT IDENTIFIER IS TEST;

Configuration “DGBTEST’ created with primary database “TEST”

Step 17:

We add the standby database to the broker configuration.

DGMGRL> ADD DATABASE ‘TESTSTBY’ AS

CONNECT IDENTIFIER IS TESTSTBY

MAINTAINED AS PHYSICAL;

Database “TESTSTBY” added.

Step 18:

Enable the configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL>

Step 19:

Verify the broker configuration is set up correctly and running.

DGMGRL> show configuration

Configuration

Name: dgbtest

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

test - Primary database

teststby - Physical standby database

Current status for “dgbtest”:

SUCCESS

DGMGRL>

Now we have a fully functional Data Guard and Broker configuration. If you have any questions or any ideas to make this document better please contact me at rlenihan@piocon.com

I just recently authored a PL/SQL Server Page designed to funnel large amounts of data via the htp.p function to a web application. Because the volume of data could be very large, I wanted to minimize the individual calls to the htp.p function (which can only take a maximum of 32767 characters per call).

The answer was obviously CLOBS! I proceeded to accumulate the full multi-megabyte response in a CLOB by concatenating the individual VARCHAR2 results line by line as they came out of the database.

Now I’ll be the first to admit that my experience with LOBs in Oracle has been very limited. And with the onset of SecureFiles in 11g, maybe I will never really become very proficient with LOBs. But in my LOB naivety, I assumed that if it COULD be done, it would probably work fine. You would think that after 15 years of Oracle development, I would know better…nope.

DECLARE
    bigLob CLOB;
    CURSOR getVarchars2 IS
         SELECT singleColumn FROM table;
BEGIN
FOR v_rec IN getVarchars2 LOOP
    bigClob := bigClob||v_rec.singleRow||linebreak;
END LOOP;
    doStuffWithTheCLOB(bigCLOB);
END;

It looks so innocent. Append the VARCHAR2 to the end of the CLOB. Let the database do the data type conversion from VARCHAR2 to CLOB. No problema! 10 rows, 100 rows, 1000 rows, all working just fine. Then came the request for 44K rows of data. The database became very angry and would not cooperate (“Stop asking for 44k rows!”). The user community became very angry and would not cooperate (“We need our 44k rows!”).

The solution is of course found in an obscure part of the Oracle documentation.

Here are the relevant entries:
• When Possible, Read/Write Large Data Chunks at a Time: Because LOBs are big, you can obtain the best performance by reading and writing large pieces of a LOB value at a time. This helps in several respects:
    o Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, then the chunk is also stored in the redo log.

• If you use the newly provided enhanced SQL semantics functionality in your applications, then there will be many more temporary LOBs created silently in SQL and PL/SQL than before. Ensure that temporary tablespace for storing these temporary LOBs is large enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
     o SQL functions on LOBs
     o PL/SQL built-in character functions on LOBs
     o Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.
     o Perform a LONG-to-LOB migration

I highly recommend looking at this chapter in the LOB documentation prior to working with LOBs in SQL and PL/SQL.

Greg Pike

Have you ever been in a situation that required you to generate a set of multiple “artificial rows”? Perhaps you needed it for a report. Typically, you would need to do this in a PL/SQL loop of some kind. How else does one generate a >1 set of rows dynamically, from nothing? With a single query of course!

Our Scenario:

For a report of some kind, or perhaps for use in a much larger query, you need to create a rowset that consists of the dates of the 365 days previous to today, each along with a random number between 1 and 20 (pointless I agree, but useful as an example).

There are a couple ways to tackle this.

Method 1 - Using DUAL and a CONNECT BY
This technique actually came to us late in the drafting of this topic by way of one of our readers, Rose. But it is so simple, elegant and ingenious that it immediately became our chosen solution for most situations. Our hat tip to Rose.

Many people I’m sure are already familiar with Oracle’s CONNECT BY clause, which allows one to construct queries that select rows from a table in a hierarchical ordering that may be present in the data, by specifying the conditions that identify a parent-child relationship between rows. But for those that aren’t, a brief example is in order, though greater detail will be omitted.

Consider the following Employee table:

emp_id	emp_name	mgr_id
1	M. Vranicar	null
2	J. Simmons	1
3	J. Weicher	2
4	R. Rolek	2

The CONNECT BY clause (and its associated optional keywords) allow us to cleverly query the table, ordering the rows hierarchically, using a relationship we know is defined through columns in the table itself, in this case, an employee to his manager:

> SELECT  LEVEL  – oracle keyword available in CONNECT BY queries
          LPAD(’ ‘, LEVEL*2) || emp_name name
  FROM employee
  START WITH mgr_id is null
  CONNECT BY PRIOR emp_id = mgr_id

Without going into greater detail, Oracle retrieves the records using the CONNECT BY PRIOR condition to in essence “filter” the records to determine the next appropriate “child” of the record last retrieved, when determining the retrieval order.

Returning to our example scenario, we can use this functionality to generate the 365 row result set that we are looking for by omitting the PRIOR keyword, and using only a condition based on the LEVEL. This is doable as the CONNECT BY PRIOR clause really just acts as a filter, determining which record is the next child (or in this case, simply when to stop selecting more “child” rows!):

> SELECT  sysdate - LEVEL thedate,
          mod(abs(dbms_random.random), 20) + 1 thevalue
  FROM dual
  CONNECT BY LEVEL <= 365;

THEDATE     THEVALUE
--------- ----------
23-APR-07         20
22-APR-07         19
21-APR-07         10
20-APR-07          5
19-APR-07         13
18-APR-07          9
17-APR-07         12
16-APR-07         14
15-APR-07         19
14-APR-07         14
...
28-APR-06          9
27-APR-06          7
26-APR-06          4
25-APR-06         10
24-APR-06          8

365 rows selected.

What is happening?
This technique works because the CONNECT BY clause causes Oracle to execute the query by taking the following steps:

1) Select the first date and random number using the single record from dual.

2) Next, “connect back” to select the next appropriate “child” record, again from dual, using the CONNECT BY condition as a “filter”. Dual only has one record, and the only “filter” is that LEVEL be <= 365. The result is the single record being selected from dual again, though this time LEVEL is automatically incremented by 1.

3) Step 2 will be continually repeated, as the CONNECT BY condition/filter will continue to find the single eligible record in dual with every "connect back", until one is retrieved where LEVEL has been incremented beyond 365!

Kudos again to Rose for this one.

Method 2 - Using DUAL and a WITH Clause
Another technique is available, which also uses DUAL, but relies on a WITH clause instead of CONNECT BY. It should be considered more of a “brute force” method. The goal is to build an “empty” rowset of size 2ⁿ that is equal or greater than our target rowset size.

We begin by selecting anything from DUAL twice, and using a UNION ALL to preserve the duplication:

> SELECT 1 FROM DUAL UNION ALL SELECT 1 FROM DUAL;

         1
----------
         1
         1

Next, let’s use this query in a WITH clause for efficiency, and select from it multiple times without a join condition- a Cartesian Product!

> WITH tworows AS
( SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual)
SELECT	*
FROM	tworows	t1, /* produces 2 rows */
	tworows t2, /* produces 4 rows */
	tworows t3, /* produces 8 rows */
	tworows t4, /* produces 16 rows */
	tworows t5, /* produces 32 rows */
	tworows t6, /* produces 64 rows */
	tworows t7, /* produces 128 rows */
	tworows t8, /* produces 256 rows */
	tworows t9  /* produces 512 rows */

What we see produced is a result set of 512 rows and 9 columns:

         1          1          1          1          1          1
---------- ---------- ---------- ---------- ---------- ---------- ...
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1
...
         1          1          1          1          1          1
         1          1          1          1          1          1
         1          1          1          1          1          1

512 rows selected.

Next, we can extend our query by actually selecting the data we are interested in by utilizing ROWNUM: the past 365 days and a random number:

> WITH tworows AS
( SELECT 1 FROM dual union all SELECT 1 FROM dual)
SELECT	sysdate - ROWNUM thedate,
	mod(abs(dbms_random.random), 20) + 1 thevalue
FROM	tworows	t1, /* produces 2 rows */
	tworows t2, /* produces 4 rows */
	tworows t3, /* produces 8 rows */
	tworows t4, /* produces 16 rows */
	tworows t5, /* produces 32 rows */
	tworows t6, /* produces 64 rows */
	tworows t7, /* produces 128 rows */
	tworows t8, /* produces 256 rows */
	tworows t9  /* produces 512 rows */

And again, we see that this produces a rowset of 512 rows, this time with only values we are interested in:

THEDATE     THEVALUE
--------- ----------
12-APR-07          2
11-APR-07         18
10-APR-07         12
09-APR-07         18
08-APR-07         19
07-APR-07          2
...
22-NOV-05          9
21-NOV-05         17
20-NOV-05          5
19-NOV-05          1
18-NOV-05         19
17-NOV-05          8

512 rows selected.

Finally, we can limit the number of rows down to our target size with a filter on “rownum”:

> WITH tworows AS
( SELECT 1 FROM dual union all SELECT 1 FROM dual)
SELECT	sysdate - rownum thedate,
	mod(abs(dbms_random.random), 20) + 1 thevalue
FROM	tworows	t1, /* produces 2 rows */
	tworows t2, /* produces 4 rows */
	tworows t3, /* produces 8 rows */
	tworows t4, /* produces 16 rows */
	tworows t5, /* produces 32 rows */
	tworows t6, /* produces 64 rows */
	tworows t7, /* produces 128 rows */
	tworows t8, /* produces 256 rows */
	tworows t9  /* produces 512 rows */
WHERE	rownum <= 365

And we now have the result set we were looking for:

THEDATE     THEVALUE
--------- ----------
12-APR-07         16
11-APR-07         20
10-APR-07         12
09-APR-07         12
08-APR-07         11
07-APR-07         17
06-APR-07          5
...
17-APR-06         13
16-APR-06          7
15-APR-06          7
14-APR-06          2
13-APR-06         18

365 rows selected.

So Which is Better?
You guessed it: it depends. In our testing, we’ve found that while Method 2 is a bit less efficient in terms of database activities (gets, sorts, etc.), the timing differences are negligible at worst when dealing with smaller result set sizes. However, this does not remain the case should you be generating significantly larger numbers of records. With large rowsets, Method 2 wins out in the end.

For purposes of illustration, consider a rowset of ~1.7 million records (2²⁴).

If we run our query using Method 1, we see that the execution time is a little over 10 seconds (~10.3):

> SELECT count(*) from (
    SELECT  sysdate - rownum thedate,
	    mod(abs(dbms_random.random), 20) + 1 thevalue
    FROM dual
    CONNECT BY LEVEL <= 16777216
  )

  COUNT(*)
----------
  16777216

Elapsed: 00:00:10.29

Running this query using Method 2 however, takes about 7 seconds (~6.9), a savings of about 3 seconds:

> WITH tworows AS
( SELECT 1 FROM dual union all SELECT 1 FROM dual)
select count(*) from (
SELECT	sysdate - rownum thedate,
	mod(abs(dbms_random.random), 20) + 1 thevalue
FROM	tworows	t1,  /* produces 2 rows */
	tworows t2,  /* produces 4 rows */
	tworows t3,  /* produces 8 rows */
	tworows t4,  /* produces 16 rows */
	tworows t5,  /* produces 32 rows */
	tworows t6,  /* produces 64 rows */
	tworows t7,  /* produces 128 rows */
	tworows t8,  /* produces 256 rows */
	tworows t9,  /* produces 512 rows */
	tworows t10,  /* produces 1024 rows */
	tworows t11,  /* produces 2048 rows */
	tworows t12,  /* produces 4096 rows */
	tworows t13,  /* produces 8192 rows */
	tworows t14,  /* ... */
	tworows t15,
	tworows t16,
	tworows t17,
	tworows t18,
	tworows t19,
	tworows t20,
	tworows t21,
	tworows t22,
	tworows t23,
	tworows t24   /* produces 16777216 rows */
)

  COUNT(*)
----------
  16777216

Elapsed: 00:00:06.90

The real savings for large results found with Method 2 however, comes from the ability to “tune” it. Specifically, there is nothing stopping us from increasing the the number of records initially produced in the WITH clause subquery from 2, to say 4. Due to Oracle’s efficiency in MERGE JOIN CARTESIANS, the dominant database operation resulting from Method 2, the savings can be significant:

> WITH fourrows AS
( SELECT 1 FROM dual
  union all
  SELECT 1 FROM dual
  union all
  SELECT 1 FROM dual
  union all
  SELECT 1 FROM dual
)
select count(*) from (
SELECT	sysdate - rownum thedate,
	mod(abs(dbms_random.random), 20) + 1 thevalue
FROM	fourrows t1,  /* produces 4 rows */
	fourrows t2,  /* produces 16 rows */
	fourrows t3,  /* produces 64 rows */
	fourrows t4,  /* produces 256 rows */
	fourrows t5,  /* produces 1024 rows */
	fourrows t6,  /* produces 4096 rows */
	fourrows t7,  /* produces 16384 rows */
	fourrows t8,  /* produces 65536 rows */
	fourrows t9,  /* produces 262144 rows */
	fourrows t10, /* produces 1048576 rows */
	fourrows t11, /* produces 4194304 rows */
	fourrows t12  /* produces 16777216 rows */
)

  COUNT(*)
----------
  16777216

Elapsed: 00:00:03.54

Conclusion
For all but the largest record sets, Method 1 is ideally suited for dynamically generating artificial rowsets using a query.

	John Weicher

While being an incredibly powerful and efficient way of indexing certain types of data, it is now relatively well accepted that Oracle’s Bitmap Indexes still have some kinks and sometimes cause erratic behavior in queries, particularly when dealing with parallel queries.

Unfortunately, this erratic behavior can even crop up when parallelism has been set to zero both on a given table, and in a query itself, if you are trying to execute even simple queries that contain PARTITION BY clauses. Unfortunately, there often appears to be no rhyme or reason as to when queries will exhibit this behavior when querying one bitmap-indexed table versus another.

Nonetheless, you may have found yourself faced with the following situation, and simply need a workaround.

Consider:

A fact table, ORDER_ITEM_F of the following general structure:

ORDER_ITEM_F
record_pk	NUMBER NOT NULL
loc_id	NUMBER NOT NULL
prod_id	NUMBER NOT NULL
quantity	NUMBER NOT NULL
cost	NUMBER NOT NULL

This table has numerous records already in it:

> SELECT count(*) FROM order_item_f;

  COUNT(*)
----------
    657802

Additionally, among others, a bitmap index has been created on the location ID field (loc_id).

> CREATE BITMAP INDEX order_item_f_b_idx_11 ON order_item_f (loc_id)


Next, for some internal reporting you would like to query this table to find counts of order items per sales location, while attaching a total for the set to each row (perhaps you are using this query in an Oracle Report and doing some nice formatting).

A simple query could be as follows:

> SELECT  distinct loc_id,
          count(*) over (partition by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

What you expect to receive from such a query is a row for each LOC_ID value, which includes a count of records having that LOC_ID, and an extra column containing the total number of records in the table (this value will be the same for each row returned).

Unfortunately, what you receive instead falls into one of the two scenarios below:

Scenario 1: Inaccurate counts based on full table count query (example above):

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17         12         89
        18         12         89
        19          8         89
        20          9         89
        21          1         89
        22          3         89
        23          4         89
        24          6         89
        25          6         89
        26          1         89
        27         11         89
        28          6         89
        29          4         89
        30          3         89
        31          1         89
        44          1         89
        45          1         89

Scenario 2: ORA-0600 - The Dread Internal Error

ERROR at line 1:
ORA-00600: internal error code, arguments: [kxfqupp_bad_cvl], [7215], [4],
 [0], [], [], [], []

Enabling autotrace (>SET AUTOTRACE ON) and re-running the query reveals that the database attempted to retrieve your results using the bitmap index:

Execution Plan
----------------------------------------------------------
Plan hash value: 2270429775

-------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |    16
|   1 |  SORT UNIQUE                   |                       |    16
|   2 |   WINDOW SORT                  |                       |    16
|   3 |    WINDOW BUFFER               |                       |    16
|   4 |     BITMAP INDEX FAST FULL SCAN| ORDER_ITEM_F_B_IDX_11 |   657K
-------------------------------------------------------------------------

An inspection of the plan shows that ~657K records are in fact accounted for by the fast full scan of the bitmap index. Yet in the case of Scenario 1 above, the counts coming back clearly do not reflect this total (if they come back at all!).

The PARALLEL/BITMAP bug strikes!

While a much more in-depth discussion of the cause for this bug could follow, sometimes you just need a practical fix:

The Fix: Full Table Scan Hint

To quickly resolve your problem, a simple hint can be added to your SELECT query, to force a full table scan instead in place of the bitmap index scan:

> SELECT  /*+ FULL(order_item_f) */
          distinct loc_id,
          count(*) over (PARTITION by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17     127674     657802
        18     136815     657802
        19      49540     657802
        20      51782     657802
        21       3908     657802
        22      11463     657802
        23      33415     657802
        24      29190     657802
        25      34141     657802
        26       1901     657802
        27      97483     657802
        28      31405     657802
        29      32619     657802
        30      15248     657802
        31          1     657802
        44       1090     657802
        45        127     657802

17 rows selected.

	John Weicher

There are often times when you want to know what has been recorded in the database’s alert log, but may not want to or be able to log into the database server and view the file. An SQL interface to the alert log can offer several advantages – you don’t need an account on the database server and you can read the alert log from SQL and PLSQL programs (which can be incorporated into other programs). This posting will show you how to create a usable SQL interface to the alert log. Then using this interface you can write simple SQL to show the alert log entries for the past hour or all the alert log entries that resulted from errors so far this month.

External tables, introduced in database release 9.0 give us the ability to access the alert log using SQL. The IGNORE NULLS option to the LAST analytic function, introduced in database release 10g, allows us to really make use of the dates we find in the alert log.

In this example, we'll create all the schema objects in the SYSTEM schema and run the commands as user SYS (we can’t grant ourselves privileges, so we can’t run all of the statements as SYSTEM)

The first block of code below does three things, first is to create a directory for the bdump_dest – the directory location of the alert log. We call it appropriately BDumpDir. Next, we need to grant the read privilege to SYSTEM, so this directory can be used for the external table. Finally, we create the external table on the file alert_sid.log:

DECLARE

  BDumpDir  VARCHAR2(200);

  SID       VARCHAR2(16);

  ObjectExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ObjectExists,-955);

BEGIN

  -- get the bdump dir

  SELECT value

  INTO BDumpDir

  FROM v$parameter

  WHERE name='background_dump_dest';

  -- create the directory for the bdump dir

  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||

    BDumpDir||'''';

  -- grant the necessary privileges

  EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';

  -- get the SID

  SELECT instance_name INTO SID FROM v$instance;

  -- create the external table

  EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL

    (TEXT VARCHAR2(255)

    ) ORGANIZATION EXTERNAL

    (TYPE ORACLE_LOADER

     DEFAULT DIRECTORY BDUMP_DIR

     ACCESS PARAMETERS

     (records delimited by newline

      nobadfile

      nologfile

     )

     LOCATION (''alert_'||SID||'.log'')

    )

    REJECT LIMIT UNLIMITED'

  ;

-- ignore ORA-955 errors (object already exists)

EXCEPTION WHEN ObjectExists THEN NULL;

END;

/

Now we can query the external table to read the alert log.

SELECT * FROM system.alert_log_external

WHERE ROWNUM < 20;

TEXT

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

Thu Jan 25 14:58:36 2007

Thread 1 advanced to log sequence 227

  Current log# 1 seq# 227 mem# 0: C:\INFO\ORACLE\ORADATA\ORA102\REDO01.LOG

Dump file c:\info\oracle\product\10.2.0\admin\ora102\bdump\alert_ora102.log

Fri Jan 26 09:18:36 2007

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Windows XP Version V5.1 Service Pack 2

CPU                 : 2 - type 586

Process Affinity    : 0×00000000

Memory (Avail/Total): Ph:671M/2046M

Fri Jan 26 09:18:36 2007

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR
19 rows selected.

This is a first step, but the log file isn’t really very usable - the entries are all timestamped, but the timestamp appears inline with the entries. We need to associate the timestamp with each line in the file, so we can query the alert log based on timestamp. We know the timestamp appears in a consistent format, so we can look for these timestamps and convert them to a date datatype. We create a function to do this for us, so we can trap and ignore any errors related to invalid dates. The alert_log_date function looks for strings in the expected timestamp format and converts them to a date. If the line in the alert log does not appear in a timestamp format (ORA-1846 is raised), we ignore it.

CREATE OR REPLACE FUNCTION system.alert_log_date( text IN VARCHAR2 )

  RETURN DATE

IS

  InvalidDate  EXCEPTION;

  PRAGMA EXCEPTION_INIT(InvalidDate, -1846);

BEGIN

  RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY'

    ,'NLS_DATE_LANGUAGE=AMERICAN');

EXCEPTION

  WHEN InvalidDate THEN RETURN NULL;

END;

/

So now, we can get the alert log entry and the timestamp, if present:

SELECT ROWNUM row_num ,system.alert_log_date(text) alert_date, text

FROM system.alert_log_external

WHERE ROWNUM < 20

/

This is progress, but we really want that datestamp carried down so each line of text is marked with the datestamp. This is where the IGNORE NULLS option to the LAST function comes into play. We use our working query, but then wrap another query around it.

SELECT row_num

      ,LAST_VALUE(alert_date IGNORE NULLS) OVER(ORDER BY row_num

           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) alert_date

      ,alert_text

FROM (SELECT ROWNUM row_num

            ,system.alert_log_date(text) alert_date

            ,text alert_text

      FROM system.alert_log_external

     )

WHERE ROWNUM < 20

/

So, now we get the line number, date stamp and text for each alert log entry. Hm.. It’s starting to look more like a good old table. Let’s add the starting line number for each alert log entry, so we can extract these entries easier. Again, we’ll build on the query we have by wrapping it with another. We get the starting line number based on the presence of a timestamp in the base table.

SELECT row_num

      ,LAST_VALUE(low_row_num IGNORE NULLS)

         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

         AND CURRENT ROW) start_row

      ,LAST_VALUE(alert_date  IGNORE NULLS)

         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

         AND CURRENT ROW) alert_date

      ,alert_text

FROM (SELECT ROWNUM row_num

            ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num

            ,system.alert_log_date(text) alert_date

            ,text alert_text

      FROM system.alert_log_external

     )

WHERE ROWNUM < 20

/

Finally, we put this nested query into a view to hide its complexity and create a public synonym on it. You can decide who you allow to read the alert log by granting them SELECT on the alert_log view.

CREATE OR REPLACE FORCE VIEW system.alert_log as

SELECT row_num

      ,LAST_VALUE(low_row_num IGNORE NULLS)

         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

         AND CURRENT ROW) start_row

      ,LAST_VALUE(alert_date  IGNORE NULLS)

         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

         AND CURRENT ROW) alert_date

      ,alert_text

FROM (SELECT ROWNUM row_num

            ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num

            ,system.alert_log_date(text) alert_date

            ,text alert_text

      FROM system.alert_log_external

     )

;

DECLARE

  ObjectExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ObjectExists,-955);

BEGIN

  EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';

-- If the synonym exists, drop and recreate it

EXCEPTION WHEN ObjectExists THEN

  EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM alert_log';

  EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';

END;

/

Now with our usable view built, we can look for alert log entries with SQL. For example; let’s see the alert log entries for the past hour:

SELECT row_num, alert_text

FROM alert_log

WHERE alert_date > SYSDATE - 1/24

/

Let’s see all the alert log entries for the past month that resulted from errors (contain the text ORA-).

SELECT row_num, alert_text

FROM alert_log

WHERE start_row IN (SELECT start_row

                    FROM alert_log

                    WHERE REGEXP_LIKE(alert_text,'ORA-')

                   )

AND alert_date > TRUNC(SYSDATE,'MON')

/

The next step, that I’ll leave to you, is to write the SQL interface to read these trace files…
Happy coding!