Oracle Tips and Tricks — David Fitzjarrell

September 21, 2020

Take Control

Filed under: General — dfitzjarrell @ 16:42

"For years she ruled as a wise and beloved monarch, each morning at sunrise
releasing the day's new sounds, to be borne by the winds throughout the kingdom,
and each night at moonset gathering in the old sounds, to be catalogued and filed in
the vast storage vaults below."
-- Norton Juster,"The Phantom Tollbooth"

A fairly common task for many DBAs is to backup the controlfile, either as a binary copy or as a script intended to recreate said controlfile (or controfiles) should the need arise. The task is not complicated, but there may be some wondering why creating such a backup is necessary. Let’s look at what this task does and why it probably should be a regular occurrence.

The controlfile is the heart and soul of the Oracle database; without it the database cannot be mounted and subsequently opened. It contains the following sections:


DATABASE
CKPT PROGRESS
REDO THREAD
REDO LOG
DATAFILE
FILENAME
TABLESPACE
TEMPORARY FILENAME
RMAN CONFIGURATION
LOG HISTORY
OFFLINE RANGE
ARCHIVED LOG
BACKUP SET
BACKUP PIECE
BACKUP DATAFILE
BACKUP REDOLOG
DATAFILE COPY
BACKUP CORRUPTION
COPY CORRUPTION
DELETED OBJECT
PROXY COPY
BACKUP SPFILE
DATABASE INCARNATION
FLASHBACK LOG
RECOVERY DESTINATION
INSTANCE SPACE RESERVATION
REMOVABLE RECOVERY FILES
RMAN STATUS
THREAD INSTANCE NAME MAPPING
MTTR
DATAFILE HISTORY
STANDBY DATABASE MATRIX
GUARANTEED RESTORE POINT
RESTORE POINT

Since it contains a wealth of information the ability to replace it is of extreme importance for the DBA. The choice is not whether to backup the controlfile but in which form should the backup take. Binary copies are convenient, as they only need be copied to the correct locations to restore service. They can be large, however, and on systems where data files are exceptionally large space could be an issue. The second option, backing up to trace, provides a way to replace existing controlfile “in place” and such backups consume very little storage. Such backups also consume very little processing time, making this option very favorable for nightly O/S scheduler jobs to create a current backup.

Two options present themselves from within a controlfile backup script: a NORESETLOGS option, for when the controlfile becomes corrupted but the redo logs are still available to bring the database controlfile replacement current, and a RESETLOGS option, for when the redo is also a victim, rendering it unavailable. The contents of this script are to be found in the directory defined by the background_dump_dest parameter; of course since it’s a ‘standard’ trace file the naming will not make it obvious nor easy to find. Enter the tracefile_identifier parameter.

Oracle provides this parameter to allow the DBA to insert identifiable text into the tracefile name; it is exceptionally easy to set at the session level:


alter session set tracefile_identifier='CFILE';

Essentially any valid text can be used, however it might be prudent to set the text to the database name, in capital letters, or, as shown above, some text to mark the trace as one containing the controlfile script. Because the file initially resides in the background_dump_dest it’s a good idea to either move or copy the file to a different, “safe” destination:


/bin/cp /my/oracle/background/dest/*CFILE* /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql

This, of course, presumes only one tracefile exists with CFILE in its name; this can be remedied by writing a short script to perform the deed, and include any ‘housekeeping’ necessary to ensure success. An example of the relevant code is provided below:


/bin/cp /my/oracle/background/dest/*CFILE* /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql
status=$?
if [ $status -ne 0 ]
then
        /bin/echo "File copy, line 110, failed -- `/bin/date`" > $ERRFILE
        /bin/mailx -s "${NME} refresh error - `/bin/date`" $MAILLIST < $ERRFILE
        exit -3
else
        /bin/rm -f /my/oracle/background/dest/*CFILE*
fi

[The entire script is not provided as that is left as an exercise for the DBA.] So why do this on a daily basis? Databases are not static and changes such as datafile and tablespace additions will make prior controlfile backups obsolete. Also putting this on a schedule keeps the DBA from having to create a controlfile backup at the moment one is needed, reducing the stress the DBA is already experiencing from having an unexpected controlfile failure.

The DBA can go one step further and create two scripts, one for when the current redo is available and one for when it isn’t. The second case is the easiest to plan for as the RESETLOGS option is always option #2:


/bin/cp /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql /my/oracle/cfile/scripts/${ORACLE_SID}_resetlogs_controlfile.sql
LNE=`grep -n "#2" /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql | /bin/awk -F":" '{print $1}'`

/bin/vi /my/oracle/cfile/scripts/${ORACLE_SID}_resetlogs_controlfile.sql <<EOF
:1,${LNE}:d
:wq!
EOF

Creating the NORESETLOGS script is almost as easy::


/bin/cp /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql /my/oracle/cfile/scripts/${ORACLE_SID}_noresetlogs_controlfile.sql
LNE=`grep -n "#2" /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql | /bin/awk -F":" '{print $1}'`
END=`wc -l /my/oracle/cfile/scripts/${ORACLE_SID}_controlfile.sql`

/bin/vi /my/oracle/cfile/scripts/${ORACLE_SID}_noresetlogs_controlfile.sql <<EOF
:${LNE},${END}:d
:wq!
EOF

This will leave some commented lines at the end of the CREATE CONTROLFILE command but that should not be an issue. Now the DBA is positioned to replace the controlfile at a moments notice and all that will be necessary is to start the database in NOMOUNT mode and execute the script of choice (or necessity).

Although it should be a very rare occurrence controlfile corruption can happen (or, at the very worst, controlfiles can be deleted by determined hackers in an attempt to sabotage an installation). Just like the Boy Scouts it’s best for a DBA to ‘be prepared’, and having existing controlfile backups, in either form, is part of that preparation. It’s far better to be ready for a situation that never arrives than to be caught, possibly scrambling, to bring the database back to life by having to create a replacement controlfile ‘on the fly’.

And that is good information to file away for later use.

September 17, 2020

That Will Cost You

Filed under: General — dfitzjarrell @ 15:56

"Nothing can possibly go wrong now," cried the Humbug happily, and as soon
as he'd said it he leaped from the car, as if stuck by a pin, and sailed all the way to
the little island.
-- Norton Juster,"The Phantom Tollbooth"

Recently Oracle support posted the following at MOS:


Bug 30324542 : WRONG RESULT WITH "COST BASED OR EXPANSION"

An example was posted with the results, and, indeed, the results from the transformed query were wrong as they were ordered by the second column in descending order. This was interesting because a hidden parameter, _optimizer_cbqt_or_expansion, was using the default setting of ‘on’; setting this to ‘off’ essentially ‘cured’ the issue and returned correctly sorted results. The example provided was run in Oracle 19.8.0.0.0, with the output provided below. It’s a simple example, creating a table, inserting a handful of rows, creating an index and executing the suspect query when the parameter was on, then off. The results follow.

This begins by creating a two-column table and populating it with four rows:


SQL>
SQL> create table test(a char(1),b char(1));

Table created.

SQL> insert into test values('a','1');

1 row created.

SQL> insert into test values('a','2');

1 row created.

SQL> insert into test values('b','1');

1 row created.

SQL> insert into test values('b','2');

1 row created.

SQL> commit;

Commit complete.

An index is also created:


SQL> create index test_idx on test(a,b);

Index created.

SQL>

So far, so good. Now the interesting part — Oracle uses cost-based OR expansion to process the original query, with less than stellar results:


SQL> set autotrace on
SQL> select distinct a,b from test
  2  where (a=' ' and b>=' ') or (a>' ') order by a,b;


A B
- -
a 2
b 2
a 1
b 1


Execution Plan
----------------------------------------------------------
Plan hash value: 2529375370

---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     5 |    30 |     2  (50)| 00:00:01 |
|   1 |  HASH UNIQUE        |                 |     5 |    30 |     2  (50)| 00:00:01 |
|   2 |   VIEW              | VW_ORE_A06CF4B6 |     5 |    30 |     1   (0)| 00:00:01 |
|   3 |    UNION-ALL        |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| TEST_IDX        |     1 |     6 |     0   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| TEST_IDX        |     4 |    24 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"=' ' AND "B">=' ' AND "B" IS NOT NULL)
   5 - access("A">' ' AND "A" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        440  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

During the run event 10053 was set, to provide an optimizer trace. From that it was found that the original query had undergone an interesting transformation, edited to be more easily readable. This was executed, producing the same results as the original query when _optimizer_cbqt_or_expansion was set to ‘on’:


SQL> SELECT DISTINCT "VW_ORE_A06CF4B6"."ITEM_1" "A","VW_ORE_A06CF4B6"."ITEM_2" "B"
  2  FROM
  3  ( (SELECT "TEST"."A" "ITEM_1","TEST"."B" "ITEM_2" FROM "U1"."TEST" "TEST" WHERE "TEST"."A"=' ' AND "TEST"."B">=' ')
  4  UNION ALL
  5  (SELECT "TEST"."A" "ITEM_1","TEST"."B" "ITEM_2" FROM "U1"."TEST" "TEST" WHERE "TEST"."A">' ')) "VW_ORE_A06CF4B6";

A B
- -
a 2
b 2
a 1
b 1


Execution Plan
----------------------------------------------------------
Plan hash value: 4084848649

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     5 |    30 |     2  (50)| 00:00:01 |
|   1 |  HASH UNIQUE        |          |     5 |    30 |     2  (50)| 00:00:01 |
|   2 |   VIEW              |          |     5 |    30 |     1   (0)| 00:00:01 |
|   3 |    UNION-ALL        |          |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| TEST_IDX |     1 |     6 |     0   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| TEST_IDX |     4 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TEST"."A"=' ' AND "TEST"."B">=' ' AND "TEST"."B" IS NOT
              NULL)
   5 - access("TEST"."A">' ' AND "TEST"."A" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        691  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

What appears to be odd is the results are not wrong, per se, simply not in the desired order. By adding an order by clause to the transformed query all is again right with the world:


SQL> SELECT DISTINCT "VW_ORE_A06CF4B6"."ITEM_1" "A","VW_ORE_A06CF4B6"."ITEM_2" "B"
  2  FROM
  3  ( (SELECT "TEST"."A" "ITEM_1","TEST"."B" "ITEM_2" FROM "U1"."TEST" "TEST" WHERE "TEST"."A"=' ' AND "TEST"."B">=' ')
  4  UNION ALL
  5  (SELECT "TEST"."A" "ITEM_1","TEST"."B" "ITEM_2" FROM "U1"."TEST" "TEST" WHERE "TEST"."A">' ')) "VW_ORE_A06CF4B6"
  6  ORDER BY A,B;

A B
- -
a 1
a 2
b 1
b 2


Execution Plan
----------------------------------------------------------
Plan hash value: 1587190138

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     5 |    30 |     3  (67)| 00:00:01 |
|   1 |  SORT UNIQUE        |          |     5 |    30 |     2  (50)| 00:00:01 |
|   2 |   VIEW              |          |     5 |    30 |     1   (0)| 00:00:01 |
|   3 |    UNION-ALL        |          |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| TEST_IDX |     1 |     6 |     0   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| TEST_IDX |     4 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TEST"."A"=' ' AND "TEST"."B">=' ' AND "TEST"."B" IS NOT
              NULL)
   5 - access("TEST"."A">' ' AND "TEST"."A" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        704  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

It would appear to be a fairly simple case of an incomplete query transformation; the ORDER BY clause is missing from the transformed query, resulting in the default ordering Oracle would produce from a hash join (the hash key is the ordering column). Notice when the ORDER BY is included the HASH UNIQUE becomes a SORT UNIQUE; in the query not undergoing the transform the final step is a SORT UNIQUE NOSORT, due to the index access:


SQL> select distinct a,b from test
  2  where (a=' ' and b>=' ') or (a>' ') order by a,b;

A B
- -
a 1
a 2
b 1
b 2

Execution Plan
----------------------------------------------------------
Plan hash value: 4069179713

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     4 |    24 |     2  (50)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|          |     4 |    24 |     2  (50)| 00:00:01 |
|*  2 |   INDEX FULL SCAN  | TEST_IDX |     4 |    24 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A">' ' OR "A"=' ' AND "B">=' ')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        440  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Of course the ORDER BY exists in the original query, so it can’t be added to fix the errant output. Until Oracle gets around to correcting this the best option is to simply turn the feature off:


alter session set "_optimizer_cbqt_or_expansion"=off;

A database engine is a complicated piece of software, and, as with all complex programs, fixing bugs or providing new features in one section of the code can result in bugs surfacing in other parts. Even though valiant attempts are made to test such changes it’s highly likely that not every case will be discovered so errant results can be produced. It’s been reported to Oracle Support and it is reproducible so chances are good it will be fixed in an upcoming release.

Presuming, of course, nothing else goes wrong.

September 15, 2020

A Blooming Union

Filed under: General — dfitzjarrell @ 12:29

"What a silly system." The boy laughed. "Then your head keeps changing its
height and you always see things in a different way? Why, when you're fifteen
things won't look at all the way they did when you were ten, and at twenty
everything will change again."
-- Norton Juster,"The Phantom Tollbooth"

Oracle functionality and features can change with each release of the database. Introduced in 10.2 Bloom filters have evolved over time in their scope and can find themselves used in UNION ALL queries. This can make large rowsource operations more efficient by ‘weeding out’ unwanted rows before the final result set is constructed. Let’s take a closer look at that.

There may be some who wonder what Bloom filters are, so a bit of explanation may be helpful. Named after Burton Howard Bloom, who came up with the concept in the 1970s, it’s an efficient data structure used to quickly determine if an element has a high probability of being a member of a given set. It’s based on a bit array that allows for rapid searches and returns one of two results: either the element is probably in the set (which can produce false positives) or the element is definitely not in the set. The filter cannot produce false negatives, and the incidence of false positives is relatively rare. Another advantage to Bloom filters is their small size relative to other data structures used for similar purposes (self-balancing binary search trees, hash tables, or linked lists). The possibility of false positives necessitates the addition of another filter to eliminate them from the results, yet such a filter doesn’t add appreciably to the process time and, therefore, goes relatively unnoticed.

Let’s use an example modeled after one provided by Jonathan Lewis — slight changes in the text were made but the overall process is how Jonathan defined it. Run in a 19c database the optimizer_features_enable session parameter has been modified to reflect the release behavior of 12.2.0.1. A basic data set is created using DBA_OBJECTS:


SQL> 
SQL> create table tab_one as select * from all_objects where rownum <= 50000;

Table created.

SQL> create table tab_two as select tab_one.* from tab_one, (select rownum n1 from dual connect by level <= 4);

Table created.

SQL> create table tab_three as select tab_one.* from tab_one, (select rownum n1 from dual connect by level <= 4);

Table created.

SQL> 
SQL> begin
  2  	     dbms_stats.gather_table_stats(
  3  		     ownname	 => null,
  4  		     tabname	 => 'TAB_ONE',
  5  		     method_opt  => 'for all columns size 1 for columns object_type size 254'
  6  	     );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 

A simple join between TAB_ONE and TAB_TWO is executed, producing the expected Bloom filter:


SQL> 
SQL> doc
DOC>
DOC>Optimizer features 12.2.0.1
DOC>
DOC>Blooom filte created and used
DOC>
DOC>#
SQL> 
SQL> alter session set optimizer_features_enable='12.2.0.1';

Session altered.

SQL> 
SQL> select
  2  	     /*+
  3  		     parallel(2)
  4  		     gather_plan_statistics
  5  	     */
  6  	     tab_one.object_name, vtab_one.object_name
  7  from
  8  	     tab_one,
  9  	     tab_two vtab_one
 10  where
 11  	     tab_one.object_type = 'SCHEDULE'
 12  and     vtab_one.object_id = tab_one.object_id
 13  /

OBJECT_NAME                         OBJECT_NAME
----------------------------------- -----------------------------------
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED

16 rows selected.

SQL> 
SQL> select * From table(dbms_xplan.display_cursor( format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  663n8j3g0g63u, child number 0
-------------------------------------
select         /*+                 parallel(2)
gather_plan_statistics         */         tab1.object_name,
vtab1.object_name from         tab1,         tab2 vtab1 where
tab1.object_type = 'SCHEDULE' and     vtab1.object_id = tab1.object_id

Plan hash value: 1427754421

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     16 |00:00:00.07 |       5 |       |       |          |
|   1 |  PX COORDINATOR        |          |      1 |        |     16 |00:00:00.07 |       5 | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |      0 |     16 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN           |          |      0 |     16 |      0 |00:00:00.01 |       0 |  1250K|  1250K|  566K (0)|
|   4 |     JOIN FILTER CREATE | :BF0000  |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |      TABLE ACCESS FULL | TAB1     |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |     JOIN FILTER USE    | :BF0000  |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|   7 |      PX BLOCK ITERATOR |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |       TABLE ACCESS FULL| TAB2     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("VTAB1"."OBJECT_ID"="TAB1"."OBJECT_ID")
   5 - filter("TAB1"."OBJECT_TYPE"='SCHEDULE')
   8 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"VTAB1"."OBJECT_ID"))

Note
-----
   - Degree of Parallelism is 2 because of hint


35 rows selected.

SQL> 

Let’s now alter the query by adding a UNION ALL construct between TAB_TWO and TAB_THREE, still using the optimizer features set for 12.2.0.1; notice the Bloom filter is now missing with the result that all 400,000 rows end up being processed:</p)


SQL> 
SQL> doc
DOC>
DOC>Bloom filter not created -- 12.2.0.1 parallel
DOC>
DOC>#
SQL> select
  2  	     /*+
  3  		     parallel(2)
  4  		     gather_plan_statistics
  5  	     */
  6  	     tab_one.object_name, vtab_one.object_name
  7  from
  8  	     tab_one,
  9  	     (select * from tab_two union all select * from tab_three) vtab_one
 10  where
 11  	     tab_one.object_type = 'SCHEDULE'
 12  and     vtab_one.object_id = tab_one.object_id
 13  /

OBJECT_NAME                         OBJECT_NAME
----------------------------------- -----------------------------------
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED

32 rows selected.

SQL> 
SQL> select * From table(dbms_xplan.display_cursor( format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  08q5f2nq822uj, child number 0
-------------------------------------
select         /*+                 parallel(2)
gather_plan_statistics         */         tab1.object_name,
vtab1.object_name from         tab1,         (select * from tab2 union
all select * from tab3) vtab1 where         tab1.object_type =
'SCHEDULE' and     vtab1.object_id = tab1.object_id

Plan hash value: 4060012981

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |     32 |00:00:00.19 |      10 |       |       |          |
|   1 |  PX COORDINATOR         |          |      1 |        |     32 |00:00:00.19 |      10 | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |      0 |     32 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN            |          |      0 |     32 |      0 |00:00:00.01 |       0 |  1250K|  1250K|  693K (0)|
|*  4 |     TABLE ACCESS FULL   | TAB1     |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |     VIEW                |          |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|   6 |      UNION-ALL          |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |       PX BLOCK ITERATOR |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |        TABLE ACCESS FULL| TAB2     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|   9 |       PX BLOCK ITERATOR |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |        TABLE ACCESS FULL| TAB3     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("VTAB1"."OBJECT_ID"="TAB1"."OBJECT_ID")
   4 - filter("TAB1"."OBJECT_TYPE"='SCHEDULE')
   8 - access(:Z>=:Z AND :Z=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 2 because of hint


38 rows selected

SQL> 

Let’s take this one step further and try to get Oracle, by force, to generate a plan using a Bloom filter. The hints used will, indeed, generate a Bloom filter, but not quite the way we’d want it created:


SQL> 
SQL> doc
DOC>
DOC>Optimizer features 19.1.0
DOC>
DOC>Bloom filter created and used
DOC>
DOC>#
SQL> 
SQL> select
  2  	     /*+
  3  		     parallel(2)
  4  		     gather_plan_statistics
  5  		     leading(@sel$1 tab_one@sel$1 vtab_one@sel$1)
  6  		     use_hash(@sel$1 vtab_one@sel$1)
  7  		     pq_distribute(@sel$1 vtab_one@sel$1 hash hash)
  8  	     */
  9  	     tab_one.object_name, vtab_one.object_name
 10  from
 11  	     tab_one,
 12  	     (select * from tab_two union all select * from tab_three) vtab_one
 13  where
 14  	     tab_one.object_type = 'SCHEDULE'
 15  and     vtab_one.object_id = tab_one.object_id
 16  /

OBJECT_NAME                         OBJECT_NAME
----------------------------------- -----------------------------------
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED

32 rows selected.

SQL> 
SQL> select * From table(dbms_xplan.display_cursor( format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  71c28r1gch210, child number 0
-------------------------------------
select         /*+                 parallel(2)
gather_plan_statistics                 leading(@sel$1 tab_one@sel$1
vtab_one@sel$1)                 use_hash(@sel$1 vtab_one@sel$1)
  pq_distribute(@sel$1 vtab_one@sel$1 hash hash)         */
tab_one.object_name, vtab_one.object_name from         tab_one,         (select
* from tab_two union all select * from tab_three) vtab_one where
tab_one.object_type = 'SCHEDULE' and     vtab_one.object_id = tab_one.object_id

Plan hash value: 994466787

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |     32 |00:00:00.15 |      15 |       |       |          |
|   1 |  PX COORDINATOR             |          |      1 |        |     32 |00:00:00.15 |      15 | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)       | :TQ10002 |      0 |     32 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN BUFFERED       |          |      0 |     32 |      0 |00:00:00.01 |       0 |  2290K|  2082K|  911K (0)|
|   4 |     JOIN FILTER CREATE      | :BF0000  |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |      PX RECEIVE             |          |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       PX SEND HYBRID HASH   | :TQ10000 |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |        STATISTICS COLLECTOR |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |         PX BLOCK ITERATOR   |          |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |          TABLE ACCESS FULL  | TAB1     |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |     PX RECEIVE              |          |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|  11 |      PX SEND HYBRID HASH    | :TQ10001 |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|  12 |       JOIN FILTER USE       | :BF0000  |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|  13 |        VIEW                 |          |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|  14 |         UNION-ALL           |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  15 |          PX BLOCK ITERATOR  |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|* 16 |           TABLE ACCESS FULL | TAB2     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|  17 |          PX BLOCK ITERATOR  |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|* 18 |           TABLE ACCESS FULL | TAB3     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("VTAB1"."OBJECT_ID"="TAB1"."OBJECT_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
       filter("TAB1"."OBJECT_TYPE"='SCHEDULE')
  16 - access(:Z>=:Z AND :Z<=:Z)
  18 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 2 because of hint


49 rows selected.

SQL> 
>

Unfortunately the filter was not used until after the view was generated, resulting in all 400,000 rows being processed by the UNION ALL. Since this is 19c hints at lines 5, 6, and 7 have been removed and the query run again, this time using the UNION ALL enhancement implemented by Oracle, producing a different execution plan:


SQL> select
  2          /*+
  3                  parallel(2)
  4                  gather_plan_statistics
  5          */
  6          tab1.object_name, vtab1.object_name
  7  from
  8          tab1,
  9          (select * from tab2 union all select * from tab3) vtab1
 10  where
 11          tab1.object_type = 'SCHEDULE'
 12  and        vtab1.object_id = tab1.object_id
 13  /

OBJECT_NAME                         OBJECT_NAME
----------------------------------- -----------------------------------
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED
DAILY_PURGE_SCHEDULE                DAILY_PURGE_SCHEDULE
FILE_WATCHER_SCHEDULE               FILE_WATCHER_SCHEDULE
PMO_DEFERRED_GIDX_MAINT_SCHED       PMO_DEFERRED_GIDX_MAINT_SCHED
BSLN_MAINTAIN_STATS_SCHED           BSLN_MAINTAIN_STATS_SCHED

32 rows selected.

SQL>
SQL> select * From table(dbms_xplan.display_cursor( format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  08q5f2nq822uj, child number 1
-------------------------------------
select         /*+                 parallel(2)
gather_plan_statistics         */         tab1.object_name,
vtab1.object_name from         tab1,         (select * from tab2 union
all select * from tab3) vtab1 where         tab1.object_type =
'SCHEDULE' and     vtab1.object_id = tab1.object_id

Plan hash value: 2580303290

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |     32 |00:00:00.09 |      10 |       |       |          |
|   1 |  PX COORDINATOR          |          |      1 |        |     32 |00:00:00.09 |      10 | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |      0 |     32 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN             |          |      0 |     32 |      0 |00:00:00.01 |       0 |  1250K|  1250K|  553K (0)|
|   4 |     JOIN FILTER CREATE   | :BF0000  |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |      TABLE ACCESS FULL   | TAB1     |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |     VIEW                 |          |      0 |    400K|      0 |00:00:00.01 |       0 |       |       |          |
|   7 |      UNION-ALL           |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |       JOIN FILTER USE    | :BF0000  |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|   9 |        PX BLOCK ITERATOR |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |         TABLE ACCESS FULL| TAB2     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|  11 |       JOIN FILTER USE    | :BF0000  |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|  12 |        PX BLOCK ITERATOR |          |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
|* 13 |         TABLE ACCESS FULL| TAB3     |      0 |    200K|      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("VTAB1"."OBJECT_ID"="TAB1"."OBJECT_ID")
   5 - filter("TAB1"."OBJECT_TYPE"='SCHEDULE')
  10 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"TAB2"."OBJECT_ID"))
  13 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"TAB3"."OBJECT_ID"))

Note
-----
   - Degree of Parallelism is 2 because of hint


43 rows selected.

SQL>

Predicate information for steps 10 and 13 now show the Bloom filter being used to build the view and not filter it afterwards, which is the desired result.

New features are always exciting, and their evolution can provide performance improvements in how those features run or in how they can be applied. Even older technologies can receive an update, elevating their place in the pantheon of Oracle technology, though the benefit may not immediately be apparent.

Which isn’t such a silly system, if you think about it.

September 10, 2020

Going My Way?

Filed under: General — dfitzjarrell @ 17:03

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth 

Since version 12 Oracle has changed the way EXTPROC is configured, and it may be confusing to some. The documentation provides two methods to setup EXTPROC functionality, one including the listener (as older versions required) but that method isn’t like the original as it involves both the listener.ora and tnsnames.ora files. Compound that with the “new way” to configure the service and it may leave a DBA wondering what, exactly, needs to be done. Let’s clear that up.

In versions 12 and later a new configuration file, extproc.ora, located under $ORACLE_HOME/hs/admin, provides the location for the default configuration settings. At installation this file contains the following text:


# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS is not set, only the DLL(s) in $ORACLE_HOME/bin and 
#   ORACLE_HOME/lib can be loaded.
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
#
# To turn extproc tracing on, set TRACE_LEVEL=ON (default is OFF).
#
#   Syntax: TRACE_LEVEL=ON
#

The text is really self-explanatory, providing instructions and parameter setting examples to make it fairly simple to get extproc functioning. The most basic of configurations involves a single setting:


SET EXTPROC_DLLS=ANY

which allows Oracle to use any libraries created in the database. Specific locations can be set using the following syntax:


SET EXTPROC_DLLS=DLL:DLL

where DLL represents any DLL or shared library, including the full path, located on the database server. By default this also includes any unnamed libraries under $ORACLE_HOME/bin and $ORACLE_HOME/lib. An example would be:


SET EXTPROC_DLLS=/my/lib/shared1.so:/my/lib/shared2.so:/your/lib/shared3.so

Entries are separated with colons; no restrictions are listed outside of the overall line length but if there are a large number to configure it may be best to use the ANY configuration to avoid any potential issues. LD_LIBRARY_PATH can be set, if desired, but it isn’t necessary as the configuration file applies to the current $ORACLE_HOME. [In installations where the listener has a separate home then setting LD_LIBRARY_PATH may be required.] A configuration file for a working installation is shown below:


# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS is not set, only the DLL(s) in $ORACLE_HOME/bin and 
#   ORACLE_HOME/lib can be loaded.
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
#
# To turn extproc tracing on, set TRACE_LEVEL=ON (default is OFF).
#
#   Syntax: TRACE_LEVEL=ON
#
SET EXTPROC_DLLS=ANY

At this point nothing further needs to be done; EXTPROC is now configured.

For those who aren’t comfortable with the new configuration file the listener.ora can still be configured for EXTPROC, but now this configuration includes the tnsnames.ora file. To set the listener to spawn the extproc process the following entries need to be made to the listener.ora file:


EXTPLSNR=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=ipc)(KEY=extp))))

SID_LIST_EXTPLSNR=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=ep_agt1)
      (ORACLE_HOME=/u01/app/oracbase/product/12.2.0/dbhome_1)
      (ENVS="EXTPROC_DLLS=ANY LD_LIBRARY_PATH=/home/oracle/lib")
      (ORACLE_SID=extp)
      (PROGRAM= extproc)))

The tnsnames.ora also needs changes, which deviates from the pre-12 configuration that only involved adding EXTPROC-specific settings; those are provided below:


extproc_agent=
  (DESCRIPTION=
    (ADDRESS=PROTOCOL=ipc)(KEY=extp))
    (CONNECT_DATA=
      (PRESENTATION=RO)
      (SID=ep_agt1)))

Yet another change requires that the new EXTPROC listener, EXTPLSNR, be started:</


$  lsnrctl start extplsnr

The usual listener output should be seen:


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extp)))
STATUS of the LISTENER
------------------------
Alias                     EXTPLSNR
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-SEP-2020 15:41:44
Uptime                    1 days 0 hr. 39 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /my/oracle/home/network/admin/listener.ora
Listener Log File         /my/oracle/diag/tnslsnr/myserver/extplsnr/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extp)))
Services Summary...
Service "ep_agt1" has 1 instance(s).
  Instance "ep_agt1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

At this point the listener will spawn the EXTPROC agent and external procedures will run as expected. [If processes using these external procedures access endpoints on remote servers it will be necessary to implement the proper firewall rules to permit connections between the servers.] Both CAN be configured, however the default configuration using the extproc.ora file will take precedence, making the listener configuration unnecessary; if the listener is preferred simply restore the extproc.ora file to its original state and the listener configuration will take over.

EXTPROC isn’t just the configuration discussed here, it’s also external shared libraries and procedures/functions written to use those libraries. The external libraries are operating system dependent, so shared libraries from AIX cannot be used on a Linux system, for example. Such libraries will need to be recreated from the source code; note that different operating systems can employ different compilers and not all compilers provide the same inherent functions. As an example HP-UX provides an ltoa() function native to the compiler, and Linux does not. When dealing with such inconsistencies it may be necessary to modify the original code to produce a working library. That discussion is beyond the scope of this post; it is mentioned to help troubleshoot EXTPROC installations migrated from one O/S to another. Ensure that all of the source code necessary for the shared libraries is available and is migrated to its new home or external procedure calls may fail.

EXTPROC in version 12 and later isn’t rocket science, but it could take a little time and patience to get up and running. The effort expended will most likely be worth the time it takes to get it properly configured, and it will open a new realm of possibilities in data processing by allowing other avenues of data entry and data manipulation, all from within an Oracle database. As an example an external library call can access a shell script to facilitate file transfers between two servers and provide feedback on the success or failure of that process. That’s, well, cool.

And that makes this another road to … anywhere.

September 8, 2020

Downhill Run

Filed under: General — dfitzjarrell @ 14:35
 
"For there's always something to do to keep you from what you really should be doing, ..." 
-- Norton Juster, The Phantom Tollbooth 

Database upgrades can be… interesting … at times but never more so than when an upgrade needs to be undone. Many times such tasks occur in development or test environments, however it is possible to successfully complete an upgrade in development and run across issues in production that will necessitate a downgrade. Smaller shops may be forced to upgrade in place due to physical space limitations or licensing restrictions preventing a second database from being created in a new software home. Oracle support reports that under such conditions a downgrade can generate ORA-12899 errors not experienced before the upgrade. Let’s look at why and how that can happen.

Under ideal conditions a database upgrade would involve creating a new software home for the desired release. A full backup of the database (using RMAN) would be taken to preserve the current state and version; the current database would be shut down cleanly and brought up under the new software home, at which time the proper upgrade scripts would be executed. Notice that this process preserves the original database unmolested; should a downgrade be required the current database would be shut down, the previous home would be used and an RMAN restore would ensue. The database should now be back at the previous version, no worse for wear, presuming, of course, the issues requiring the downgrade were discovered during the post-upgrade testing phase. A simple scenario, to be sure, but it can eliminate a lot of ‘headaches’ with upgrades.

As mentioned previously Oracle support reports that a downgrade to 11.2.0.3 from 12.2.0.1 throws “ORA-12899: value too large for column” errors; the AUD$ and FGA_LOG$ tables are reported in the complete error text. These tables were modified by the catdwgrd.sql script in an attempt to restore the original settings. In 11.2.0.x and earlier releases the CLIENT_ID column in AUD$ is declared as VARCHAR2(64); in 12.2.0.x that definition changed to VARCHAR2(128). The FGA_LOG$ table also defines CLIENT_ID as VARCHAR2(64). The catdwgrd.sql script performs several actions relative to the column definition change; the statements are shown below:


--File rdbms/admin/dsec.bsq

-- lrg 14680081
-- determine AUD$ schema before altering it.
DECLARE
  uname VARCHAR2(30);
BEGIN
  select usr.name into uname
  from sys.tab$ tab, sys.obj$ obj, sys.user$ usr
  where obj.name ='AUD$' AND obj.obj# =tab.obj# AND usr.user# = obj.owner#;

  execute immediate
    'alter table ' || dbms_assert.enquote_name(uname) ||
    '.aud$ modify clientid varchar2(30)';
END;
/
 


alter table fga_log$ modify clientid varchar2(30);

Notice that the script supplied by Oracle sets these two columns as VARCHAR2(30) when the data dictionary is downgraded; that these columns were never declared as VARCHAR2(30) in any recent release of Oracle makes these changes confusing to say the least. The conditions surrounding the need to run the catdwgrd.sql are not provided, only that the script was executed on downgrade and afterwards the ORA-12899 errors appeared. Upgrades and downgrades I have been involved with did not encounter such issues, mainly because there was sufficient space and resources to have both Oracle home installations on the same server and sufficient testing was completed to catch problems that would necessitate a downgrade without requiring data recovery (changes made in testing could be discarded making the downgrade as simple as shutting down the database, changing the Oracle home, restoring the backup created just prior to upgrade and opening the database). Be that as it may the script was run at the reporting client site producing the aforementioned errors, prompting the client to file a Service Request with Oracle Support.

Having this knowledge can only be beneficial as a situation may arise where a downgrade would require executing any Oracle-supplied downgrade scripts, and having this information would make short work of resolving the issue (an issue, IMHO, that should not exist in the first place). We cannot all be perfect.

As with any DBA activity that requires more effort than simply logging into the terminal planning is required and preparedness is a must; even the most bizarre and remote of possibilities must be on the DBA’s “radar” so that if the unthinkable does happen there are planned steps to address that occurrence so the DBA is not caught between the proverbial rock and a hard place. It’s always better to expect the worst and plan accordingly rather than be caught wanting should disaster strike.

Which is, of course, what the DBA should be doing.

September 4, 2020

“That’s Ancient History…”

Filed under: General — dfitzjarrell @ 13:00
 
"To be sure," said Canby; "you're on the Island of Conclusions. Make yourself
at home. You're apt to be here for some time."
"But how did we get here?" asked Milo, who was still a bit puzzled by being
there at all.
"You jumped, of course," explained Canby. "That's the way most everyone gets
here. It's really quite simple: every time you decide something without having a
good reason, you jump to Conclusions whether you like it or not. It's such an easy
trip to make that I've been here hundreds of times."
"But this is such an unpleasant-looking place," Milo remarked.
"Yes, that's true," admitted Canby; "it does look much better from a distance."
-- Norton Juster, The Phantom Tollbooth 

History can be a cruel mistress. Then it may also be an education for those who weren’t around to experience it but who are now affected by it. Take the deprecated and possibly long-forgotten parameter utl_File_dir: still functional up through 11.2 and that release still may be in use in the world. [Oracle introduced directories in version 10 and allowed either to be used. utl_file_dir was deprecated in 12.] If it wasn’t configured “correctly” a directory that was intended to be used will cause the call to, say, UTL_FILE, to fail. Let’s look at how that parameter was to be configured to keep the database server fairly secure.

utl_file_dir, used initially by the UTL_FILE package, provided access to directory locations on the database server where files could be written to and read from. It was designed to make the DBA carefully consider such locations so that a minimal number of destinations would be available. These locations could be shared directories (Windows) or NFS mounts (UNIX) so users could access the generated files without having full access to the database server.

During its lifetime the possible settings for utl_file_dir changed to include “*”, the ubiquitous “catch-all” character. As expected this value told UTL_FILE that any location provided to it was valid; this, of course, opened up a world of security issues and access nightmares for those who generated reports that were written on the database server. A typographical misstep could drop a report into a directory that outside users couldn’t access but that did exist on the server; if /my/files/not/yours and /my/file/not/yours both exist, and /my/files/not/yours contains sensitive information, a user could, through a keyboard mishap, drop a public report into that private location. Now the DBA has to move the report to the correct location simply because that DBA chose to make the utl_file_dir configuration “easier” to manage.

The proper way to manage that parameter started with deciding on which directories would be destined for user-generated reports and setting the appropriate permissions. The next step was to set the parameter for each directory to be made accessible to the general user population, ideally using remotely mounted file systems so end-users would not need database server access. utl_file_dir was a fairly unique parameter — multiple entries could be configured and the settings would be concatenated by Oracle to create a comma-separated list of available locations. A portion of a possible init.ora/spfile.ora file is shown below, with multiple directories configured for UTL_FILE access:


...
utl_file_dir=/my/shoes/are/red
utl_file_dir=/my/shoes/are/blue
utl_file_dir=/my/shoes/are/purple
utl_file_dir=/my/shoes/are/orange
utl_file_dir=/my/shoes/are/yellow
utl_file_dir=/my/shoes/are/green
utl_file_dir=/my/shoes/are/mauve
utl_file_dir=/my/pants/are/wrinkled
...

Eight locations are declared “safe” for UTL_FILE to access; no other directories will be valid given this configuration. Looking at the parameter setting in the database those eight lines produce reveals:


SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /my?shoes/are/red, /my/shoes/a
                                                 re/blue, /my/shoes/are/purple,
                                                 /my/shoes/are/orange, /my/shoe
                                                 s/are/yellow, /my/shoes/are/gr
                                                 een, /my/shoes/are/mauve, /my/
                                                 pants/are/wrinkled
SQL>

Files could read from and written to just those eight directories, no more. Should a user try to write to /my/dog/has/fleas UTL_FILE would throw an “ORA-29280: invalid directory path” error and no output would be written. The user either needed to choose one of the eight acceptable locations or petition the DBA to add /my/dog/has/fleas to the list of writable directories. Hopefully just asking nicely wouldn’t get that change effected since /my/dog/has/fleas may have been a restricted location, containing configuration files and libraries for the “Dog Pound” malware monitoring and sequestering application. Selective configuration of UTL_FILE locations prevented that directory from being used as a dumping ground for less-than-accurate typists or users who chose inappropriate file destinations.

Now let’s return to “easy street”, configuring utl_file_dir with the dreaded “*”. Looking at the resulting entry in the database shows:


SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      *
SQL>

Any available directory on the database server would now be a valid UTL_FILE destination, with “available” meaning sufficient access permissions to allow the “world” to read from and write to that location. Files and reports could now end up in some unlikely places because of, well, “fat fingers”; as noted the only requirements are that the directory exist on the server and that the directory permissions allow users to write to it. So, if /my/dog/has/fleas somehow ended up open to all users to read from and write to then the setting above would create issues by allowing users to dump anything UTL_FILE creates there. The content may not cause issues but the consumption of available space would, possibly causing the “Dog Pound” application to fail to start.

This is no longer an issue since Oracle requires that UTL_FILE locations be specified using the “CREATE OR REPLACE DIRECTORY” command; UTL_FILE will not recognize any entries in the utl_file_dir parameter which should eliminate its use (if it hasn’t done so already). But, as noted earlier, older versions of Oracle are still out there, in use, and this information may be useful to a new or mid-level DBA who is not familiar with this configuration. It may be history but having this information may make a younger DBA’s life a bit easier.

It’s better to arrive at a conclusion rather than jump to one.

August 25, 2020

Where’s My Wallet?

Filed under: General — dfitzjarrell @ 08:46

"Yes, indeed," they repeated together; "but if we'd told you then, you might not
have gone -- and, as you've discovered, so many things are possible just as long as
you don't know they're impossible."
-- Norton Juster, The Phantom Tollbooth

In this day and age security is a primary concern, especially with respect to database technology. In that regard Oracle provides functionality to secure data, passwords used by scripts and network traffic, all utilizing what Oracle refers to as wallets. These wallets “guard the gates”, so to speak, of the various areas of the database allowing (or disallowing) access to encrypted data and destinations as well as preventing “prying eyes” from seeing user account passwords. Unfortunately since two of tese mechanisms use the same configuration variables not all can be used togetner. Given that one limitation let’s explore Oracle’s wonderful world of wallets.

Probably the most common use of Oracle wallets is for Transparent Data Encryption, or TDE. This allows for data encryption at the table or tablespace level. As mentioned previously this configuration requires a wallet, created with native SQL*Plus functionality. A directory is required to store the wallet, which can have any name you might like; because multiple wallets may be in use a base directory name /ow seems appropriate. Following that convention it stands to reason that, for TDE, the location would be /ow/tde (well, at least to me). Because this wallet will be database-specific the $ORACLE_SID needs to be included in the directory path. Since this is a wallet directory that information is also included in the path; the final directory to be created will be /ow/tde/$ORACLE_SID/wallet. Oracle, of course, needs to be informed of this location and the sqlnet.ora file is where the configuration settings reside. The following entry will let Oracle know where to look for that wallet:


ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /ow/tde/${ORACLE_SID}/wallet)))

Once the directory has been created and the sqlnet.ora updated the wallet can be created. The commands are fairly simple, yet powerful. Let’s go through a typical wallet configuration for TDE. The following set of commands can be placed in a script to automate wallet creation:


administer key management create keystore '/ow/tde/${ORACLE_SID}/wallet ' identified by mypassword;
administer key management set keystore open identified by mypassword;
administer key management set key identified by mypassword with backup;
administer key management create auto_login keystore from keystore '/ow/tde//wallet' identified by mypassword;

The wallet password is essential for management tasks so it should be unique, secure and recorded in a password safe where only authorized personnel can access it. The wallet is created yet it’s not open. The second command opens the new wallet so that further configuration can be made, although the wallet is not yet ready for use. Encryption requires a key and the third command sets that for the newly created wallet. Once step three is completed the wallet is ready to use, although it will need to be opened manually each time the database is started. To automate this task the fourth command can be executed, to create an auto-login wallet that will open once the database is started. The V$ENCRYPTION_WALLET view provides information on the wallet:


SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE  WRL_PARAMETER               STATUS  WALLET_TYPE       WALLET_OR FULLY_BAC     CON_ID
--------- --------------------------- ------- ----------------- --------- --------- ----------
FILE      /ow/tde/test1/wallet        OPEN    AUTOLOGIN	        SINGLE    NO                 0

The wallet is open and it’s an autologin wallet, so when the database is shut down the wallet is closed and after database startup the wallet is ‘magically’ opened, eliminating the need for the DBA to open the wallet manually before users can access the encrypted data. (Creating encrypted tablespaces won’t be covered here.)` TDE is the one encryption/obfuscation technology that can be used with either of the other wallet configurations.

Network encryption is probably the next most used encryption, as it also affects access to databases and the data they contain. As with TDE a directory is required as the wallet location; because network encryption is user-specific there may be multiple wallets for a given database. The sqlnet.ora is again the location for the configuration settings and the following entry will provide Oracle with the necessary information:


WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /ow/tns/$LOGNAME)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

In most cases this will be configured for the ‘oracle’ operating system user, since the database is running as ‘oracle’. Both sides of the connection need to be configured so wallets need to be created for the server side and client side. The commands shown below will create such a wallet, and they can be used on both the server and the client:


mkdir -p /ow/tns/$LOGNAME
orapki wallet create -wallet "/ow/tns/$LOGNAME" -pwd [password] -auto_login_local
orapki wallet add -wallet "/ow/tns/$LOGNAME" -pwd [password] -dn "CN=$LOGNAME" keysize 1024 -self_signed -validity 3650
orapki wallet display -wallet "/ow/tns/$LOGNAME"
orapki wallet export -wallet "/ow/tns/$LOGNAME" -owd [password] -dn "CN=$LOGNAME" -cert /tmp/${LOGNAME}-certificate.crt
cat /tmp/${LOGNAME}-certificate.crt

[On Windows clients and serers $LOGNAME will need to be replaced with the user name using the configuration.] The listener.ora now needs to be configured:


SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /ow/tns/$LOGNAME)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bullwinkle)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = bullwinkle)(PORT = 20484))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

The TCPS configuration indicates the port number for encrypted TNS traffic. Now the tnsnames.ora file needs to be configured with an alias using the encrypted port:


prod_ssl=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=bullwinkle)
      (PORT=20484)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=prod)
    )
  )
>

`

Once both sides are configured and wallets exist the connectivity can be tested. Testing the connection with tnsping should show a successful result:


$ tnsping prod_ssl

TNS Ping Utility for HPUX: Version 12.2.0.1.0 - Production on 10-JUN-2020 13:59:10

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

Used parameter files:
/u02/home/oracle/product/10.2/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = world)(PROTOCOL = TCPS)(Host =bullwinkle)(Port = 20484))) (CONNECT_DATA = (SID = prod)))
OK (10 msec)

Network traffic to and from the database will now be encrypted for this port, preventing any intercepted packets from providing any useful data.

Client credentials can also be stored in a wallet, providing a way to login to the database without passing a password or, if the O/S user matches a database user, no credentials at all. Unfortunately the configuration for the sqlnet.ora file for credentials uses the same constructs as the TCPS configuration, so both TCPS and client credentials can’t be used at the same time in the same database. Such configuration begins here:


SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=
(SOURCE=
   (METHOD=FILE)
   (METHOD_DATA=
      (DIRECTORY=/u02/home/oracle/wallet)
   )
)

The mkstore command is used to create the client credential wallet:


mkstore -wrl /ow/client -create

The wallet password will be provided when prompted; as with the other wallets this password is critical for wallet maintenance. After creation user credentials can be added:


mkstore -wrl /ow/client -createCredential TEST1 BLEEBO FNORD

The wallet password will be prompted for, twice, as a security measure. The newly added credentials will be immediately available. The username need not match the O/S username, although if they do match connecting to the database is as easy as:


sqlplus /@test1

If O/S usernames don’t match database usernames (a more common occurrence) don’t despair as the connection is almost as easy:


sqlplus /@bleebo.test1

User credentials can be added, modified or deleted using mkstore commands. Know that the wallet is not synchronized with any password management processes which may be in use so when database passwords change the wallet entries need to be modified:


mkstore -wrl /ow/client -modifyCredential TEST1 BLEEBO FUDDBUCKET

Again the wallet password will be required, twice, to effect the change.

One significant issue is the fact that wallet files can be moved without notice. Such action will essentially deactivate the wallet making it impossible to use preconfigured credentials, TCPS or TDE. A good practice to follow is to copy the wallet files, or the entire wallet directory tree, to a “safe” location — flash drives are good if USB ports are available on the database server, or a “hidden” directory somewhere on the database server. This preserves the wallet files and should someone inadvertently or intentionally alter the contents it will be easy to replace missing or damaged files, restoring functionality. Another option is to create the directory tree as “hidden”; if the directory can’t be readily seen it can’t be readily damaged intentionally. Consult with your system administrator if creating hidden files and directories is not familiar to you.

This has not been an exhaustive treatise on Oracle wallets however the information provided should be a useful start to using the Oracle technologies described here. TDE, TCPS and credential wallets can be effective tools for the DBA to use in efforts to secure a database and its data. Hopefully what was appearing as a daunting task has become easier knowing there are tools available to help prevent data theft and interception.

Take that, impossible.

July 31, 2020

Flashback, Again

Filed under: flashback — dfitzjarrell @ 17:50

"Yes, indeed," they repeated together; "but if we'd told you then, you might not
have gone -- and, as you've discovered, so many things are possible just as long as
you don't know they're impossible."
-- Norton Juster, The Phantom Tollbooth

A recent tweet on Oracle’s flashback technology caused me to dig through some older posts to find the original post I made about the restorative powers of flashback query. Reading through the text inspired me to update the original post with more current information — it was published in 2008 so a lot of water has gone under the bridge, so to speak. The basic concepts have not changed appreciably but the implementation has become much easier, and the options to flashback an entire table, or an entire database, have been added (presuming sufficient space and/or the required undo is available). Let’s take a test drive.

The original implementation of flashback query was, as noted in a prior post, a bit cumbersome but better than the alternative of having an export handy to restore a table deleted by mistake. Flashback query allowed for recovering data up to the point of deletion (which an export can’t do) so the bit of extra PL/SQL coding necessary to restore missing data was worth the work. A script like the one below would be used to retrieve the missing data then replace it in the current table:


declare
  --
  -- Cursor to fetch the required data
  --
  cursor c1 is
  select flerg, snerm, anguplast
  from snangpiester;

  --
  -- A place to stash each row of the returned data
  --
  c1_rec c1%rowtype;

begin

  --
  -- We flashback the data image to before the problem began
  --
  dbms_flashback.enable_at_time(to_timestamp('16-SEP-2007 06:53:00 AM','DD-MON-RRRR HH:MI:SS AM'));

  --
  -- Open the cursor while in flashback mode
  --
  open c1;

  --
  -- Disable flashback mode so we can update the problem table
  --
  dbms_flashback.disable;

  --
  -- Go fetch the good data and update the problem table
  --
  loop
          fetch c1 into c1_rec;
          exit when c1%notfound;  -- say goodbye when there's no more data
          update snangpiester
          set anguplast = c1_rec.anguplast
          where flerg = c1_rec.flerg
          and snerm = c1_rec.snerm;
  end loop;

  --
  -- Commit the changes
  --

  commit;

end;
/

From 10g onward flashback query has been enabled by default and usable without the extra coding the dbms_flashback package required, making it much simpler to restore deleted data providing the undo retention has not been exceeded. Flashback query syntax remains the same and is as follows:


select [select list here]
from [table]
as of timestamp to_timestamp('date/time or timestamp string');

The latest releases also provide the ability to flashback the entire table:


flashback table [table]
to timestamp to_date([date string],[date format]);

making it a simpler task to revert all changes made after a given date/time value. For flashback table to work row movement must be enabled:


SQL> alter table emp enable row movement;

Table altered.

SQL>

Now the table can be flashed back to an hour ago, before the errant changes were made:


SQL> flashback table emp to timestamp to_timestamp(sysdate-1/24);

Flashback complete.

SQL>

Let’s take the original example, that used flashback query, and use flashback table to restore the original data:

Koffi Cupps, assistant HR manager and part-time ankle model, wanted to increase the salaries of the sales team by 17 percent and add $85 to each salesman’s commission; unfortunately she updated the entire employee table without first making a copy of the original data and committed the changes before checking the results:


SQL> update emp
   2 set sal = sal*1.17, comm = nvl(comm, 0) + 85;

14 rows updated.

SQL> commit;

Commit complete.

SQL> select 
   2 from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        936         85         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1872        385         30
      7521 WARD       SALESMAN        7698 22-FEB-81     1462.5        585         30
      7566 JONES      MANAGER         7839 02-APR-81    3480.75         85         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81     1462.5       1485         30
      7698 BLAKE      MANAGER         7839 01-MAY-81     3334.5         85         30
      7782 CLARK      MANAGER         7839 09-JUN-81     2866.5         85         10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3510         85         20
      7839 KING       PRESIDENT            17-NOV-81       5850         85         10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1755         85         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1287         85         20
      7900 JAMES      CLERK           7698 03-DEC-81     1111.5         85         30
      7902 FORD       ANALYST         7566 03-DEC-81       3510         85         20
      7934 MILLER     CLERK           7782 23-JAN-82       1521         85         10

14 rows selected.

SQL>

Fortunately her younger sister, Dixie, is the Oracle DBA for the company. Knowing the HR software was using Oracle 19.3.0.0 Dixie sprang into action to restore the data prior to the change using flashback table (it was also a good thing this was early on a Monday morning and that the undo_retention was set to a sufficient size else Dixie could have received an ORA-01555 because the necessary undo blocks had been overwritten):


SQL> flashback tabld emp as of timestamp to_timestamp(trunc(sysdate));

Flashback complete.

SQL> select * From emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

Now Dixie can update the table properly, only processing the sales employees:


SQL> update emp
  2  set sal = sal*1.17, comm = nvl(comm, 0) + 85
  3  where job = 'SALESMAN'
  4  /

4 rows updated.

SQL>
SQL> select * From emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                 
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1872        385         30                                                                 
      7521 WARD       SALESMAN        7698 22-FEB-81     1462.5        585         30                                                                 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                 
      7654 MARTIN     SALESMAN        7698 28-SEP-81     1462.5       1485         30                                                                 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20                                                                 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1755         85         30                                                                 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20                                                                 
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                 

14 rows selected.

SQL> commit;

Xommit complete.

SQL>

Flashback table restored the data to it’s ‘original’ state as of the date/time specified in the flashback statement so the correct update could be applied. Of course the data could also have been restored using flashback query; I will refer you to the original post to find that example.

Another example from that post seems worthy to re-post here, involving flashback query to generate a salary increase report to verify that no employee has exceeded the maximum percentage increase on their salary (which is why this example generates the report after the increase has been processed but before the payroll data has been released):


SQL> --
SQL> -- Get reference timestamp before changes
SQL> --
SQL> select to_timestamp(to_char(sysdate, 'DD-MON-YYYY HH:MI:SS AM')) curr_timestamp from dual;

CURR_TIMESTAMP
---------------------------------------------------------------------------
09-JAN-08 11.54.40.000000000 AM

SQL>
SQL> --
SQL> -- Change the data in EMP
SQL> --
SQL> update emp
  2  set sal = sal+120, comm = nvl(comm, 0) + 85
  3  where job = 'SALESMAN';

4 rows updated.

SQL>
SQL> --
SQL> -- Commit and make the changes "permanent"
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Show the current state of the data
SQL> --
SQL> select *
  2  from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1720        385         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1370        585         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1370       1485         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1620         85         30 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL>
SQL> --
SQL> -- Generate a report of employee number,
SQL> -- employee name, job, salary, percent increase,
SQL> -- commission, increase
SQL> --
SQL> with get_old as(
  2       select empno, ename, job, sal, nvl(comm,0) comm
  3       from emp
  4       as of timestamp to_timestamp('&curr_ts')
  5  ),
  6  get_curr as (
  7       select empno, ename, job, sal, comm
  8       from emp
  9  )
 10  select c.empno, c.ename, c.job, c.sal,
 11  (c.sal - o.sal)/o.sal*100 sal_incr,
 12  c.comm,
 13  (c.comm - o.comm) comm_incr
 14  from get_old o, get_curr c
 15  where c.empno = o.empno;

     EMPNO ENAME      JOB              SAL   SAL_INCR       COMM  COMM_INCR
---------- ---------- --------- ---------- ---------- ---------- ---------- 
      7369 SMITH      CLERK            800                     0 
      7499 ALLEN      SALESMAN        1720        7.5        385         85 
      7521 WARD       SALESMAN        1370        9.6        585         85 
      7566 JONES      MANAGER         2975                     0 
      7654 MARTIN     SALESMAN        1370        9.6       1485         85 
      7698 BLAKE      MANAGER         2850                     0 
      7782 CLARK      MANAGER         2450                     0 
      7788 SCOTT      ANALYST         3000                     0 
      7839 KING       PRESIDENT       5000                     0 
      7844 TURNER     SALESMAN        1620          8         85         85 
      7876 ADAMS      CLERK           1100                     0
      7900 JAMES      CLERK            950                     0 
      7902 FORD       ANALYST         3000                     0 
      7934 MILLER     CLERK           1300                     0

14 rows selected.

SQL>

Flashback query, and flashback table, are two very useful tools for the DBA to have access to. Fast action and a sufficieintly sized undo retention can go a long way in restoring data changed in error, or verifying that changes are within the scope of company policy. And that can keep (almost) everyone happy.

Sort of makes the “impossible” … possible.

July 30, 2020

Get WITH It

Filed under: General — dfitzjarrell @ 08:09

"Is this the right road for Dictionopolis?" asked Milo, a little bowled over by
the effusive greeting.
"Well now, well now, well now," he began again, "I don't know of any wrong
road to Dictionopolis, so if this road goes to Dictionopolis at all it must be the right
road, and if it doesn't it must be the right road to somewhere else, because there are
no wrong roads to anywhere. Do you think it will rain?"
-- Norton Juster, The Phantom Tollbooth

Views can be both convenient and inconvenient – convenient to query but inconvenient to create if the user attempting to create a view does not have direct grants on the tables involved. Yes, the user can ask the DBA to fix that deficiency, over and over or the user can utilize subquery factoring (the WITH clause) to create a pseudo-view within the script and use it just like a table. Doing so would use the existing privileges granted through roles, eliminating the need for additional grants on tables and the additional CREATE VIEW privilege. Let’s explore that option.

For years Oracle has allowed users to ‘sprinkle’ queries with other queries:


select a.a, a.b, b.c, c.d, e.f
from
tbla a, tblb b. (select d, x, z from tblz where y=9) c,
(select f, x from tblq where p=77 and o=4) e
where a.x = b.x
and a.x = c.x
and c.x = e.x;

Of course this can become cumbersome when complex queries are used. Enter the WITH clause, a way to specify one or more independent queries to be used in the main query. Taking the example shown above and re-writing it using WITH produces:


with c as (
	select d, x, z from tblz where y=9
),
e as (
	select f, x from tblq where p=77 and o=4
)
select a.a, a.b, b.c, c.d, e.f
from
tbla a, tblb b, c, e
where a.x = b.x
and a.x = c.x
and c.x = e.x;

It may look more complicated at first glance but the WITH clause will allow a user to define various ‘views’ to be utilized in the main query. Using WITH allows the user to simply write queries and test them before adding them to the WITH list, making the end result easier to obtain — earlier in-line views could be confusing to those reading the query text and it is possible for the author to get lost in all of the embedded queries, losing focus and possibly resulting in incorrect results or a query that simply won’t run. Assuming that’s a simple example let’s look at a more involved set of queries returning database information DBAs could use:


set feedback on pagesize 60 linesize 150 trimspool on verify off
 column "TOTAL ALLOC (MB)" format 999,999,990.00
 column "TOTAL PHYS ALLOC (MB)" format 999,999,990.00
 column "USED (MB)" format  999,999,990.00
 column "FREE (MB)" format 999,999,990.00
 column "% USED" format 990.00

with tbl as ( select tablespace_name,
       count(*) db_files,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name
       union
       select tablespace_name,
       count(*) db_files,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_temp_files
       group by tablespace_name ),
seg as ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name
       union all
       select tablespace, sum(blocks)*8192
       from v$tempseg_usage
       group by tablespace )
select a.tablespace_name,
       db_files,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
       a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from tbl a full outer join seg b on b.tablespace_name = a.tablespace_name
order by 6
/

Notice that more complex queries are easier to code using WITH and that the results from those views can more easily be manipulated. All without additional grants or privileges. Breaking the overall query down and returning the various parts reveals the building blocks of the final output. First we get regular and temp tablespace sizes, taking into account autoextend settings:


SQL> select tablespace_name,
  2  count(*) db_files,
  3  sum(bytes) physical_bytes,
  4  sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  5  from dba_data_files
  6  group by tablespace_name
  7  union
  8  select tablespace_name,
  9  count(*) db_files,
 10  sum(bytes) physical_bytes,
 11  sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
 12  from dba_temp_files
 13  group by tablespace_name;

TABLESPACE_NAME                  DB_FILES PHYSICAL_BYTES BYTES_ALLOC
------------------------------ ---------- -------------- -----------
STATSPACK                               1       10485760  3.5184E+13
REPORT_TEMP                             1     1073741824  1073741824
SYSAUX                                  1     6480199680  3.4360E+10
SYSTEM                                  7     1.7606E+11  2.4052E+11
TEMP                                    1      524288000   524288000
TOOLS                                   1       62914560  3.5184E+13
UNDOTBS2                                8     8.0422E+10  9.6427E+10
USERS                                   1     1694760960  3.4360E+10

8 rows selected.

SQL>

Next we find the total used space based on information from dba_segments and v$tempseg_usage:


SQL> select tablespace_name, sum(bytes) tot_used
  2  from dba_segments
  3  group by tablespace_name
  4  union all
  5  select tablespace, sum(blocks)*8192
  6  from v$tempseg_usage
  7  group by tablespace;

TABLESPACE_NAME                   TOT_USED
------------------------------- ----------
SYSAUX                          5382471680
SYSTEM                          1.3311E+10
TOOLS                             57868288
UNDOTBS2                        3.4475E+10
USERS                           1657012224

5 rows selected.

SQL>o

Finally the finished report surfaces:


TABLESPACE_NAME                  DB_FILES TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)       USED (MB)  % USED
------------------------------ ---------- ---------------- --------------------- --------------- -------
TEMP                                    1           500.00                500.00            0.00    0.00
STATSPACK                               1    33,554,431.98                 10.00            0.00    0.00
REPORT_TEMP                             1         1,024.00              1,024.00            0.00    0.00
TOOLS                                   1    33,554,431.98                 60.00           55.19    0.00
USERS                                   1        32,767.98              1,616.25        1,580.25    4.82
SYSTEM                                  7       229,375.89            167,906.05       12,694.44    5.53
SYSAUX                                  1        32,767.98              6,180.00        5,133.13   15.67
UNDOTBS2                                8        91,960.00             76,696.00       32,878.00   35.75

8 rows selected.

SQL>

Using WITH allowed the user to write and test the individual queries to be used as views before putting all of the pieces together, and it provided an easier way to write the final query including manipulating the data returned from the subqueries to paint a more complete usage picture. Could this have been written the ‘old fashioned’ way? Yes, but it certainly seems easier and simpler to use the WITH clause.

But (I hear you say) I can create a view once and use it hundreds, no, thousands, of times in multiple situations and multiple queries without having to write the same code over and over again. This is true but copying an existing script, using the WITH statement it provides and modifying the main query is also an option. WITH queries can always return more data than the main query uses so one WITH definition can span several different queries. For example:


SQL> with e as (
  2  select empno, ename, job, hiredate, sal, deptno, comm
  3  from emp
  4  ),
  5  d as (
  6  select deptno, dname, loc
  7  from dept
  8  )
  9  select e.empno, e.ename, d.deptno, d.dname, d.loc
 10  from e, d
 11  where e.deptno = d.deptno;

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7369 SMITH              20 RESEARCH       DALLAS
      7499 ALLEN              30 SALES          CHICAGO
      7521 WARD               30 SALES          CHICAGO
      7566 JONES              20 RESEARCH       DALLAS
      7654 MARTIN             30 SALES          CHICAGO
      7698 BLAKE              30 SALES          CHICAGO
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7788 SCOTT              20 RESEARCH       DALLAS
      7839 KING               10 ACCOUNTING     NEW YORK
      7844 TURNER             30 SALES          CHICAGO
      7876 ADAMS              20 RESEARCH       DALLAS
      7900 JAMES              30 SALES          CHICAGO
      7902 FORD               20 RESEARCH       DALLAS
      7934 MILLER             10 ACCOUNTING     NEW YORK

14 rows selected.

SQL>
SQL> with e as (
  2  select empno, ename, job, hiredate, sal, deptno, comm
  3  from emp
  4  ),
  5  d as (
  6  select deptno, dname, loc
  7  from dept
  8  )
  9  select e.empno, e.ename, e.job, e.deptno, d.dname
 10  from e, d
 11  where e.deptno = d.deptno;

     EMPNO ENAME      JOB           DEPTNO DNAME
---------- ---------- --------- ---------- --------------
      7369 SMITH      CLERK             20 RESEARCH
      7499 ALLEN      SALESMAN          30 SALES
      7521 WARD       SALESMAN          30 SALES
      7566 JONES      MANAGER           20 RESEARCH
      7654 MARTIN     SALESMAN          30 SALES
      7698 BLAKE      MANAGER           30 SALES
      7782 CLARK      MANAGER           10 ACCOUNTING
      7788 SCOTT      ANALYST           20 RESEARCH
      7839 KING       PRESIDENT         10 ACCOUNTING
      7844 TURNER     SALESMAN          30 SALES
      7876 ADAMS      CLERK             20 RESEARCH
      7900 JAMES      CLERK             30 SALES
      7902 FORD       ANALYST           20 RESEARCH
      7934 MILLER     CLERK             10 ACCOUNTING

14 rows selected.

SQL>
SQL> with e as (
  2  select empno, ename, job, hiredate, sal, deptno, comm
  3  from emp
  4  ),
  5  d as (
  6  select deptno, dname, loc
  7  from dept
  8  )
  9  select e.empno, e.job, e.deptno, d.dname, e.hiredate
 10  from e, d
 11  where e.deptno = d.deptno;

     EMPNO JOB           DEPTNO DNAME          HIREDATE
---------- --------- ---------- -------------- ---------
      7369 CLERK             20 RESEARCH       17-DEC-80
      7499 SALESMAN          30 SALES          20-FEB-81
      7521 SALESMAN          30 SALES          22-FEB-81
      7566 MANAGER           20 RESEARCH       02-APR-81
      7654 SALESMAN          30 SALES          28-SEP-81
      7698 MANAGER           30 SALES          01-MAY-81
      7782 MANAGER           10 ACCOUNTING     09-JUN-81
      7788 ANALYST           20 RESEARCH       09-DEC-82
      7839 PRESIDENT         10 ACCOUNTING     17-NOV-81
      7844 SALESMAN          30 SALES          08-SEP-81
      7876 CLERK             20 RESEARCH       12-JAN-83
      7900 CLERK             30 SALES          03-DEC-81
      7902 ANALYST           20 RESEARCH       03-DEC-81
      7934 CLERK             10 ACCOUNTING     23-JAN-82

14 rows selected.

SQL>

The WITH clause is exactly the same in all three queries — the first query was copied in its entirety twice and the second and third main queries were edited to provide the desired results. Such edits take little time to complete and save the user, and the DBA, work and time, and the less time a user spends waiting on the DBA to get to a request for additional privileges the more work that user can produce. Unless the view to be created is being used by other accounts using the WITH clause may be the better way to go.

There may not be a wrong road, but some roads are better than others.

July 27, 2020

It Doesn’t Fit

Filed under: General — dfitzjarrell @ 12:51
 
"For, while it was not quite square, it was definitely not round, and for its size it
was larger than almost any other big package of smaller dimension that he'd ever
seen."
-- Norton Juster, The Phantom Tollbooth 

The database character set can be a ‘forgotten’ setting, that is until it causes issues when importing data. Depending upon what is stored in the source database using a different character set in the destination database can wreak havoc the DBA might not be expecting. Normally using a superset of the source character set isn’t a problem; when text fields contain ‘cut and paste’ “test” from PDFs, word processor documents and on-line pages the characters can be converted to extended characters rather than the common ‘?’ indicative of non-translatable characters, thus expanding the text length and possibly exceeding the column definition. Such is the case when a database using the WE8ISO8859P1 character set is imported into one using AL32UTF8.

Visible characters are not all the same as extended characters in a character set will be coded differently. A directional quote has a different code value than a plain vanilla quote and Windows can render both without the user noticing the difference. Copy and paste those values into a database and they can now become problems for the database to handle. For single-byte character sets, like WE8ISO8859P1, ‘unknown’ characters will be displayed as a question mark, which is still a single-byte character not affecting the overall length of the text. Move that to a UTF-8 character set (AL32UTF8) without configuring extended string length and an import could generate a long list of unexpected ORA-12899 errors. This, of course, causes the row in question to fail insertion into the destination table, reducing the row count (sometimes considerably) or, if every row generates such an error, preventing the table from being populated at all.

Data population issues can affect more than the table the data is destined for as missing rows can throw “parent key not found” errors when attempting to enable foreign keys. Data integrity is now suspect as no referential integrity is in place.

The ORA-12899 errors occur because some characters copied from various electronic documents can translate into multi-byte values, increasing the length of the character string. With VARCHAR2() strings shorter than 4000 bytes or characters it’s possible to increase the defined length to allow values to be inserted. VARCHAR2(4000) declarations won’t be adjustable when extended string length is not configured (and configuring extended string length can open a whole other can of worms with indexes). Absent extended string length the only way to allow data to be inserted is to redefine the column or columns in question as CLOBs, eliminating the size restriction the original definition created and the UTF-8 character set revealed. Note that such issues are not a likely experience if text fields are not populated by “copy and paste” operations from a wide array of online documents as “normal” characters are will not be subject to translation in the super-sized UTF-8 character set.

How did this occur? Many times it’s a result of having to move a database from one platform to another, when the endian models don’t match, Moving from HP-UX to Linux is one such migration, resulting in an export/import type of move (of course if there is sufficient disk space RMAN can be used to perform such endian conversions, but such operations can consume almost twice the allocate space for the source database because the RMAN backup and the translated data files will exist on one server at the same time. Normally an export will consume less disk space and make it easier to populate the destination database. (This can bring up other issues, like data types Data Pump doesn’t support,but that is a subject for a different post.) To eliminate tne ORA-12899 errors the destination database should be using the same character set as the source database; check this value in the source BEFORE setting out to create the destination database and verify that DBCA or the scripts written to create the database use the same character set. In situations like this the default value can, well, “bite you”.

Size can, most definitely, matter.

Next Page »

Create a free website or blog at WordPress.com.