Improvements with 11.1 for the datatypes and table structures supported for asynchronous change data capture option

I was studying the Asynchronous Autolog CDC Cookbook, I am working on the S_AUDIT_READ Siebel source table, but the AUDIT_LOG column with CLOB datatype failed during the setup with ORA-31513: unsupported column type specified in change table error on 10.2.

As I found out that this limitation for LOBs is not a problem after 11.1 anymore, I wanted to share this information.

Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
Chapter 16 Change Data Capture: Datatypes and Table Structures Supported for Asynchronous Change Data Capture

Oracle® Database Data Warehousing Guide 11g Release 1 (11.1)
Chapter 16 Change Data Capture: Datatypes and Table Structures Supported for Asynchronous Change Data Capture

ps: if you are interested in CDC option of Oracle please check this previous post of mine for an introduction and a Hotlog option implementation example.

DBMS_COMPARISON supplied package after 11g Release 1

I discussed 10g’s ORA_HASH SQL function to determine if two Oracle tables’ data are equal or not with this post before, especially if you are working with huge tables and they are on different locations this method is very effective.

In the same post I also mentioned some favorite schema and data comparison methods used within the community, so this one is a following post to that post to mention the new 11g supplied package; DBMS_COMPARISON.

After a fast startup demonstration based on psoug’s library I saw that this new feature also uses ORA_HASH SQL function. :) Also for the schema comparison part which dictionary checks are made can be followed inside a 10046 SQL trace as demonstrated below;

conn hr/hr

DROP TABLE depts PURGE ;

CREATE TABLE depts NOLOGGING AS SELECT * FROM departments ;

CREATE UNIQUE INDEX ui_depts ON depts(department_id) NOLOGGING ;

DELETE FROM depts WHERE department_id = 10;

1 row deleted.

COMMIT;

ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level = ALL ;

ALTER SESSION SET tracefile_identifier = dbms_comparison ;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

exec dbms_comparison.create_comparison(comparison_name=>'compare_test', schema_name=>'HR', object_name=>'DEPTS', dblink_name=>NULL,  remote_schema_name=>'HR', remote_object_name=>'DEPARTMENTS',  scan_percent=>90);

set serveroutput on

DECLARE
  retval dbms_comparison.comparison_type;
BEGIN
  IF dbms_comparison.compare('compare_test', retval, perform_row_dif=>TRUE) THEN
    dbms_output.put_line('No Differences');
  ELSE
    dbms_output.put_line('Differences Found');
  END IF;
END;
/
Differences Found

PL/SQL procedure successfully completed.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

select value from v$diag_info where name = 'Default Trace File' ;

VALUE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc

CMD > tkprof /u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc tong_ora_4402_DBMS_COMPARISON.txt sys=yes waits=yes sort=execpu

set null @
set colsep |
set linesize 2500

select *  FROM  user_comparison_row_dif ;

COMPARISON_NAME               |   SCAN_ID|LOCAL_ROWID       |REMOTE_ROWID

INDEX_VALUE

STA|LAST_UPDATE_TIME

COMPARE_TEST                  |         4|@                 |AAARADAAFAAAAA3AAA
10
DIF|04-OCT-08 02.58.03.306182 PM

exec dbms_comparison.drop_comparison(comparison_name=>'compare_test') ;

There are several constraints to use this method like the local database that runs the subprograms in the DBMS_COMPARISON package must be at least 11.1 and the remote database must be 10.1 at least. Non-Oracle databases are not supported and the database character sets must be the same for the databases being compared. But I am pretty sure with feature releases this package will be enhanced and be used with other database features.

ps: During my unconference session at OOW08 – APEX Test Drive – I mentioned that Apex 3 on 11.1 under Utilities tab also has a GUI support to use this new supplied package.

11g’s Metadata Information for SQL Built-In Operators and Functions

11g introduced two new views: V$SQLFN_METADATA and V$SQLFN_ARG_METADATA, which provide metadata for all Oracle SQL built-in operations and functions.

SQL> set linesize 2500
SQL> SELECT * FROM v$sqlfn_metadata where name = 'REGEXP_SUBSTR' ;

   FUNC_ID NAME                              MINARGS    MAXARGS DATATYPE VERSION      ANA AGG DISP_TYPE     USAGE                          DESCR
---------- ------------------------------ ---------- ---------- -------- ------------ --- --- ------------- ------------------------------ ------------------------------
       526 REGEXP_SUBSTR                           2          5 STRING   V10 Oracle   NO  NO  NORMAL

SQL> SELECT * FROM v$sqlfn_arg_metadata WHERE func_id = 526 ;

   FUNC_ID     ARGNUM DATATYPE DESCR
---------- ---------- -------- ------------------------------
       526          1 STRING
       526          2 STRING
       526          3 NUMERIC
       526          4 NUMERIC
       526          5 STRING

This new feature may help especially to third party tools to maintain the function usage metadata redundantly in the application layer. For more informations you may check the documentation and this oracle-developer.net article.

11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality

Before Oracle 11g adding new columns with DEFAULT values and NOT NULL constraint required both an exclusive lock on the table and the default value to be stored in all existing records.

Now in Oracle 11g the database can optimize the resource usage and storage requirements for this operation, the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced and space is saved.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
* Add a NOT NULL column with a default value
* Add a nullable column without a default value
* Add a virtual column

release 1002000300 -

drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:30.43

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0

release 1101000600 –

drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:00.10

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0

On SAGE and Oracle’s new 11g SQL Tuning Workshop Education Content

Most probably you think SAGE (this was its project name before Larry’s announcement, much more compact naming so I prefer it :) -Oracle Exadata Storage Server and the HP Oracle Database Machine- will not make a change in your daily life and you maybe unsatisfied with this announcement but I promise to give you something cool whether you are a massive OLAP or an XE developer with this post. :)

But first let me share with you one of my friend’s, who is a warehouse developer, first response to SAGE: “Oh I knew Oracle should work much more faster for years, the fault was not ours you see!” .:) So does these new features promise to reduce the importance of the warehouse development best practices? I really don’t think so..

So on my 22 hours way back home I had a chance to look at this new Oracle University education and I strongly think that this is the best content you may get from a tuning education. First, with this post, let me briefly explain the table of contents and then I think I will be doing some series of blog posts parallel to this content.

If you had 10g’s SQL Tuning Workshop here is not only new features but new chapters in the 11g’s content now, like a whole chapter dedicated to Star Transformation and a very long chapter dedicated to the optimizer operations like access path options of the Oracle database. I tried to motivate developers around me for not doing any SQL Tuning, just do tuning as they are developing and I am very happy to see this chapter as it is a part I always dreamed of. :)

Let me mention another interesting observation of mine, if you attended Jonathan Lewis’s optimizer seminars I think you will also think that the content and even some pictures are very similar in this education to that content, to my understanding this is a kind of a kind approval for people like Mr.Lewis who are trying to improve the communities understanding of the complex technologies like optimizer. No pain no gain right, so I do not think this education now will be easily consumed by starters as it should be.

By the way, as we own one of the largest warehouses in the world and there is a very important possibility that we may be testing and using Exadata and so I may be blogging something more than “reading billions of rows in seconds” at the end of the day ETL(I mean lots of writes), sort operations parallel scans and hash joins are in total what will make the difference since lots of reports(I mean reads) are already tuned to be fast enough by logical design and access path options like partitioning, bitmap indexes and materialized views in today’s optimized warehouses.

Oracle 11gR1 enhancements to the initialization parameters management

Oracle Database 11.1 now upon startup writes values of initialization parameters to the alert log in such a way that when you loose your SPFILE or PFILE it is now easier to copy and paste them to create a new PFILE.

Also the SPFILE or PFILE that was used to open the instance is now mentioned with an additional line.

..
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletong.ora
System parameters with non-default values:
  processes                = 150
  shared_pool_size         = 144M
  streams_pool_size        = 16M
  control_files            = "/u01/app/oracle/oradata/tong/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/tong/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/tong/control03.ctl"
  db_block_size            = 8192
  db_cache_size            = 220M
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 8G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=tongXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/tong/adump"
  audit_trail              = "DB"
  db_name                  = "tong"
  open_cursors             = 300
  star_transformation_enabled= "TRUE"
  pga_aggregate_target     = 192M
  diagnostic_dest          = "/u01/app/oracle"
  _trace_files_public      = TRUE
Sat Sep 13 11:13:50 2008
PMON started with pid=2, OS id=3199
..

..
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
  processes                = 250
  sessions                 = 280
  __shared_pool_size       = 222298112
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  nls_language             = TURKISH
  nls_territory            = TURKEY
  sga_target               = 285212672
  control_files            = /oracle/oradata/tong/control01.ctl, /oracle/oradata/tong/control02.ctl, /oracle/oradata/tong/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 50331648
  compatible               = 10.2.0.3.0
  db_file_multiblock_read_count= 32
  db_recovery_file_dest    = /home/oracle/flash_recovery_area
  db_recovery_file_dest_size= 8589934592
  fast_start_mttr_target   = 180
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=tongXDB)
  job_queue_processes      = 10
  background_dump_dest     = /oracle/admin/tong/bdump
  user_dump_dest           = /oracle/admin/tong/udump
  core_dump_dest           = /oracle/admin/tong/cdump
  audit_file_dest          = /oracle/admin/tong/adump
  db_name                  = tong
  open_cursors             = 300
  star_transformation_enabled= TRUE
  pga_aggregate_target     = 94371840
PMON started with pid=2, OS id=5423
..

Also now you can create a pfile or spfile from the active instance’s current values of the initialization parameters.

CREATE PFILE FROM MEMORY;

CREATE SPFILE FROM MEMORY;

This feature gives an opportunity to compare the parameters used at that moment with the last startup ones.

Stored outlines are deprecated in Oracle Database 11g

I hope one day I write a similar post for HINTs, an optimizer world which always guarantees the *best* path for your each query without any additional interrupts..

[from metalink's 11g Upgrade Companion]
Oracle highly recommends the use of SQL plan baselines instead of the stored outlines after 11gR1. With Oracle Database 11g using the SQL Plan Management (SPM) the optimizer automatically manages plans and ensures that only verified or known plans are used.

SQL Plan Management allows controlled plan evolution by only using a new plan after it has been verified to be perform better than the current plan. You should also use SQL Plan Management as part of your upgrade strategy. Please see the best practices section for more information.
[/from metalink's 11g Upgrade Companion]

For more details on SQL Plan Management

Oracle Database Performance Tuning Guide – Using SQL Plan Management

SQL Plan Management in Oracle Database 11g

Some Useful Oracle Database Utilities and Errorlogging Feature of 11g’s SQL*Plus

Some Oracle utilities are well documented in the Oracle Database Utilities Guide but some are not. Some are very familiar to us; exp, imp, expdp, impdp, sqlldr, dbverify, csscan, tnsping, lsnrctl, emctl, tkprof etc. But some may be not; trcroute, oradebug, dbshut, dbstart, oerr, orakill etc. Here are some simple examples I use frequently on Linux.

[oracle@tonghost bin]$ oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause:  This is the generic internal error number for Oracle program
//         exceptions.  This indicated that a process encountered an
//         exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number.

[oracle@tonghost bin]$ dbhome
/u01/app/oracle/product/11.1.0/db_1

[oracle@tonghost bin]$ dbfsize /u01/app/oracle/oradata/tong/example01.dbf

Database file: /u01/app/oracle/oradata/tong/example01.dbf
Database file type: file system
Database file size: 12800 8192 byte blocks

[oracle@tonghost bin]$ dbshut /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/shutdown.log

[oracle@tonghost bin]$ dbstart /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/startup.log

With 11g sql*plus’s errorlogging feature we can store any SQL, PL/SQL or SQL*Plus errors by default in table SPERRORLOG.

conn / as sysdba
create user tong identified by tong;
grant create session to tong;
grant unlimited tablespace to tong;
create table tong.tong_sperrorlog(username   varchar(256),
				timestamp  TIMESTAMP,
				script     varchar(1024),
				identifier varchar(256),
				message    CLOB,
				statement  CLOB);                         

connect tong/tong
set linesize 1200
show errorlogging
set errorlogging on table tong_sperrorlog   

create table tong ( c1 number ) ;

set errorlogging off

select timestamp, statement, message from tong_sperrorlog;

TIMESTAMP       22-JUN-08 07.00.27.000000 PM
STATEMENT		create table tong ( c1 number )
MESSAGE			ORA-01031: insufficient privileges

This feature can be also used with 11g client against 9i and 10g servers. Check $ORACLE_HOME/sqlplus/doc/elgsetup.txt for details.

Oracle Database 11g Roadshow and Release 2 Sneak Peek

After seeing Pawel’s post I also registered for the event. This three hours briefing was mostly about Oracle Database 11g and provided an opportunity for me to meet with Oracle server technologies vice president Mark Townsend, identify how we can be involved in the 11g Release 2 beta program and outline how we can participate in the marketing of our products and services at Oracle OpenWorld as a partner.

In order to attend you should be in one of below categories;

- Independent Software Vendors
- Systems Integrators
- Consultants, and Pre-sales Consultants
- Resellers

And below are the scheduled next stops;

May 19, 2008: Czech Republic, Prague
May 20, 2008: Poland, Kraków
May 21, 2008: Sweden, Stockholm
May 22, 2008: Spain, Madrid

My impressions is that Pawel was right about his expectations since it was mostly just another colorful Oracle powerpoint day. I tried to ask several questions and took the advantage of speaking to Mark and these were the seconds where we got some technical. Since I believe it can be not appropriate to talk about 11g R2 in details I will skip details of those parts of the event and share my remaining notes as usual;

Oracle Database 11g Roadshow and Release 2 Seminar Notes

For 11g R2 let me briefly mention that other than impressive fusion middleware new features we the database people will be living the new Grid 2.0 era, ASMing everywhere, single RACing to rule them all and edition based redefinition for minimum change impacts on applications ;)

Visit the Oracle PartnerNetwork (OPN) portal

Oracle Database 11g

Chronicles of 11gR1

I am still on holiday, but Oracle is like drugs :) I was reading 11g now or 11g later forum topic on oraclecommunity.net and decided to install and play with 11gR1. I am an advocate for second release by experience for a production environment, to go with first releases there must be really strong motivations for new features. But on VMvare it is always fun to play with Oracle’s new features :)

So I was playing with 11gR1 on Oracle Enterprise Linux 5 at VMware Workstation 6 on my laptop, but first things first;
- Oracle 10.2.0.4 patchset is out for Linux x86, but yet another month I guess for plathforms like Solaris and Windows.
- On February 27th apex.oracle.com will be upgraded to 3.1, so if you have some important applications to be exported don’t be too late.

Since dizwell closed his resources to the community this time I couldn’t have the pleasure to consume his fantastic installation guides, strange but I never thought of loosing a resource like this before, now I have similar fears for some blogs I frequently read and asktom. But there are some really good alternatives for quick installation guides like
http://www.oracle-base.com/articles/linux/ArticlesLinux.php
http://www.puschitz.com/
http://ivan.kartik.sk/
and of course for details there is always official installation guide documentation.
http://download.oracle.com/docs/cd/B19306_01/install.102/b15660/toc.htm
By the way if you are also missing Howard Rogers’s fantastic articles on dizwell.com and his OTN forums replies maybe this helps, see this petition on oraclecommunity.net.

I want to comment on my first test results and share my first impressions of 11gR1 briefly here;

1- Our old USER_DUMP_DEST, BACKGROUND_DUMP_DEST and CORE_DUMP_DEST is replaced with DIAGNOSTIC_DEST parameter now and we now have also xml based log files. Hard to like immediately.
But it is really easy to get the trace file name now, V$DIAG_INFO view gives the trace file name with TRACEFILE_IDENTIFIER that is set. Lovely.
With each trace file also a small binary .trm extension, trace map file, is created now. Interesting.

2- Instead of SGA_TARGET and PGA_AGGREGATE_TARGET now we have MEMORY_TARGET by default to manage both sga and pga together. Cool.
Also my DB_FILE_MULTIBLOCK_READ_COUNT was set to 52 by default. Interesting.

3- Still after a FLASHBACK TABLE TO BEFORE DROP constraints and triggers related to the table are left with BIN$% names. Unhappy.

4- sqldeveloper is a part of the installation of the 11g database now. Lovely.

5- apex is also a part of the installation of the 11g database now. Lovely.

6- Lots of new parameters, supplied packages and background processes are introduced or changed. This study of Julian Dyke summarizes the differences between Oracle 10.2.0.1 and Oracle 11.1.0.6.

Listing 202a – Chronicles of 11gR1
Listing 202b – Some simple event 10046 and 10053 tests on 11g

11g may be a real cost with its new features, I am excited to see what will be included on the 11g Express Edition which is most probably be available with the second release. Until then if you also want to go out and play below references provide much much more;

Oracle Database 11g: The Top New Features for DBAs and Developers by by Arup Nanda
http://www.oracle-base.com/articles/11g/Articles11g.php
Oracle® Database New Features Guide 11g Release 1 (11.1)
http://wordpress.com/tag/oracle-11g-new-features/