H.Tonguç Yılmaz - Oracle Blog

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.

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/

Recently I started a discussion on oraclecommunity.net database forum with subject “Why Oracle?”. Hans Forbrich and Billy Verreynne gave important feedbacks. As a group we are preparing a free half day seminar to the computer science students in Istanbul.

When I started to study 10g I tried to blog my experiences on new pl/sql packages with a series called Expanded Supplied Packages with 10g. If you do not want to waste your time and money on developing some features which are already available in your database, it is better to be aware of them. As Tom Kyte’s saying; no need to re-invent a dumper wheel :) For 11g here is a start point for the new and updated ones;

11g Release 1 New Features in the SQL Language
11g Release 1 New and Updated PL/SQL Packages
11g Release 1 All New Features Grouped by Guides

Recently I was playing with DBMS_ADVANCED_REWRITE package which was also a new feature on 10g. You may want to use this package for rewriting a bad query on your production database until it is tuned or even to make your friends go mad with changed results to their queries on your development database :)

Code Listing 198 : DBMS_ADVANCED_REWRITE demo

Testing Information : the scripts mentioned are tested on Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

References Used :
http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php
http://technology.amis.nl/blog/?p=447
http://psoug.org/reference/dbms_adv_rewrite.html

This one maybe one of the best RAC setup articles of all times - Oracle 11g RAC on Oracle Enterprise Linux 4(Update 5) and VMware Server 1.0.3 by Frederik Visser

On Oracle Database 10g Express Edition (you will need an extra registration with your OTN account for this forum - please check http://otn.oracle.com/xe for details) forum I started some threads recently, each is related to an extension for the coming 11g XE.

So you may support the ones already opened or you may create new ones similar to these. Of course there is no guarantee that these will have an affect on Oracle’s decisions but why not trying :)

11g Express Edition Wishlist - Compression
11g Express Edition Wishlist - JVM as a separate download and install
11g Express Edition Wishlist - maximum storage to 20GB
11g Express Edition Wishlist - Point-in-time tablespace recovery
11g Express Edition Wishlist - More globalization support
11g Express Edition Wishlist - Maximum memory 2GB
11g Express Edition Wishlist - Database Control(Oracle Enterprise Manager)
11g Express Edition Wishlist - Flashback Table

References used :
Express Edition Licensing Information Guide

I will be upgrading my experiment mouse 10g Release 2 database(an emotional period for me to get apart :) to 11g Release 1 soon, so I thought remembering, consolidating and sharing some of my past experiences on upgrading an Oracle database can be useful. You may comment on these depending to your own experiences.

If an upgrade is again at my door step(usually within 3 years at most :) -

a. I first read the new features, concepts and upgrade guides of the new release. Also I check to my platform specific release notes for especially special cases.

b. I test the upgrade process carefully, load and test my applications on upgraded environment and of course test the restore/recovery plan for an unexpected situations. Here do not skip to test the third party tools(if you are using) like data extraction(unloader) or performance monitoring tools you purchased extra. Also test your backup and disaster recovery strategies, cronjobs, if there is an active-passive clustering your switchover scripts.

c. If you are also needing and believing in control and performance of the upgrade steps, you may also plan to do it manually like me. For example I plan to;
- take the database temporarily to NOARCHIVELOG mode,
- close the other instances and increase sga and pga for the database to be upgraded,
- create large redo log files,
- spooling each step to a log file during migration and
- carefully monitor os cpu and memory usage for example with top utulity, tail -f alert_SID.log, plan and guarantee sufficient space in your SYSTEM, UNDO and TEMP tablespaces.
Also there is always a well detailed metalink note for this manual upgrade path :)

Of course when I have the tolerance for downtime, prior to migration and after a successful migration a cold(clean closed) backup will always feel good. During migration restricting database connection to users, stopping listener, unix cron and oracle scheduler(jobs) for unwanted change or interrupts is very important.

Since I believe and trust in Cost based optimizer-CBO(hints are always last resorts as Mr.Lewis advices) I re-collect both object and system statistics as a post migration step(still staying in NOARCHIVELOG may be useful at this point). Also setting COMPATIBLE and OPTIMIZER_FEATURES_ENABLE init.ora parameters to enable new optimizer features is important for the quality of your tests. Always one of the last steps is checking and compiling the invalid objects after upgrading, here a report of the invalid objects prior to the migration will be useful for comparison. In fact I create lots of temporary tables with create table as select for the copies of important dictionary views like dba_tables/indexes/tab_partitions/constraints/triggers etc.

d. For a meaningful(best test strategy let me say), I clone my production database onto a machine with similar resources, this means budget of course(I linked a reference guide for cloning a database) . I run my applications under a load similar to its production and monitor the database with OEM, V$ views, events like 10046-10053 and AWR reports. Here CBO enhancements need the primary attention and you will be needing some reports, prior execution plans and reference metrics prior to upgrade for detail comparisons in order to catch a problematic change in CBO behavior.

e. Of course, no matter what the deadline is, I do not accept to upgrade the real production database until I successfully upgrade and finish my planned tests on the cloned test environment..

I will also share some notes from the 11g new features guide I saw which I think you may also be interested -

i. 11g Release 1 deprecated features;
- Oracle Ultra Search,
- Java Development Kit(JDK) 1.4,
- CTXXPATH index

ii. Important initialization parameter changes;
- USER_DUMP_DEST/BACKGROUND_DUMP_DEST/CORE_DUMP_DEST is replaced with DIAGNOSTIC_DEST parameter which defaults to $ORACLE_BASE/diag.
- If there is a null value for the UNDO_MANAGEMENT initialization parameter which means UNDO_MANAGEMENT is not set, after 11g this implies to AUTO mode.
- So to migrate to automatic undo management if you are not already some how :)
1. Set UNDO_MANAGEMENT=MANUAL
2. create a reasonable load compared to your production
3. and execute DBMS_UNDO_ADV.RBU_MIGRATION function to get the advised size for undo tablespace;

DECLARE
   undo_tablespace_MB NUMBER;
BEGIN
   undo_tablespace_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
END;
/

4. create new undo tablespace as adviced sized
5. last step will be setting UNDO_MANAGEMENT=AUTO

And some extra related readings -

Some migration experiences to share
Deffensive upgrade methods, but still no pain no gain
Take the risk and migrate to 10gR2
Cloning a Database by Howard Rogers

In my opinion history will show that, life of an Oracle DBA is divided into two stages; before and after 10g Enterprise Manager Grid Control :) I still remember some old days after paying to Oracle every possible extra licensing you may pay, everybody were paying lots of extra money to some bunch of other companies’ tools just to manage Oracle effectively. But somehow, someday Oracle decided to end its customers’ pain; Grid Control, Automatic Storage Management(ASM) and SQL Developer are examples of this strategy, so those were now the “good” old days just for some companies I guess.

Everybody even presidents needs, loves advisors. It is the same with Oracle, after 10g and with 11g Oracle is continuing to invest on its advisories. Memory, SQL Access and Tuning, Segment advisors have important parts in our lives now. But with this post I planned to discuss on some less popular advisors(I believe) like Undo, Redo and Automatic Checkpoint Tuning Advisors with 10g and also some 11g delights like Partitioning and Data Recovery advisors.

The Undo Advisor assists in correctly sizing the undo tablespace. The Undo Advisor can also be used to set the low threshold value of the undo retention period for any Oracle Flashback requirements. And to access the Undo Advisor in the Database Control you may follow; Administration > Undo Management > Undo Advisor

Also after 10g Rollback Monitoring improved; when a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, if the process takes more than six seconds. This view enables you to estimate when the monitored rollback process will finish.

SELECT TIME_REMAINING, SOFAR/TOTALWORK*100 PCT
FROM V$SESSION_LONGOPS
WHERE SID = 9
AND OPNAME =’Transaction Rollback’

Redo Log Tuning Advisory and Automatic Checkpoint Tuning are also new features introduced with 10G. The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance, however it must balanced out with the expected recovery time, as rule of thumb switching logs at most once every fifteen-twenty minutes. Undersized log files increase checkpoint activity and increase CPU usage.

Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary. Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files.

The redo logfile sizing advisory is specified by column optimal_logfile_size of v$instance_recovery. This feature require setting the parameter “fast_start_mttr_target” for the advisory to take effect and populate the column optimal_logfile_size.

You can try this with different settings of “FAST_START_MTTR_TARGET” but -
* If FAST_START_MTTR_TARGET is set to such a small value that it is impossible to do a recovery within its time frame then target_mttr field of v$instance_recovery contains the effective MTTR target which is larger than FAST_START_MTTR_TARGET.
* If FAST_START_MTTR_TARGET is set to such a high value that even in worst case entire buffer cache is dirty) recovery would not take that long, then target_mrrt field contains the estimated mttr in worst-case scnerios.

Code Listing 117 - Redo Log Tuning Advisory Demo

And to access the Redo Logfile Size Advisor you may follow; Administration > Storage > Redo Log Groups > Sizing Advice

10g also supports automatic checkpoint tuning. But by default, this feature is not enabled, because FAST_START_MTTR_TARGET has a default value of 0. It is an advancement over the MTTR related parameter introduced in earlier versions. The idea is to use the periods of low I/O usage to advance checkpoints and therefore improve availability.

To enable automatic checkpoint tuning, unset FAST_START_MTTR_TARGET or set it to a nonzero value(This is measured in seconds). If you set this parameter to zero this feature will be disabled. When you enable fast-start checkpointing, remove or disable(set to 0) the following initialization parameters:
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- FAST_START_IO_TARGET
Enabling fast-start checkpointing can be done statically using the initialization files or dynamically using -

SQL> alter system set FAST_START_MTTR_TARGET=10;

Enabling fast-start checkpointing increases the average number of writes per transaction that DBWn issues for a given workload. However, if the system is not already near or at its maximum I/O capacity, then fast-start checkpointing has a negligible impact on performance. View V$MTTR_TARGET_ADVICE will give information on the additional I/O’s on various values of FAST_START_MTTR_TARGET. But if -
- FAST_START_MTTR_TARGET is set to a low value - Fast-start checkpointing is more aggressive. The average number of writes per transaction that DBWn issues is higher in order to keep the thread checkpoint sufficiently advanced to meet the requested MTTR.
- FAST_START_MTTR_TARGET is set to a high value - Fast-start checkpointing in less aggressive, and the average number of writes per transaction that DBWn issues is lower.
- FAST_START_MTTR_TARGET is unset - automatic checkpoint tuning is in effect. Average number of writes per transaction is reduced but at the same time MTTR is highest.

So what about future releases and Oracle’s advisory strategy, I advise :) you to check 11g Manageability presentation’s fifth slide for some hints. After 11g a new advisor called Partition Advisor which analyzes the data access patterns and suggests the decision to choose the partitioning scheme and the partitioning column(s) will be assisting us especially during development and testing stages. By the way before 10g if you drop a partitioned table Oracle removed all the partitions at once, so time and resource consuming process. After 10g Release 2 when you drop a partitioned table, partitions are dropped one by one, maybe you already felt the change :)

Data Recovery Advisor will be another friend which automatically diagnose data failures and recommend repairs. You can repair failures manually or request that they be repaired automatically. Of course Enterprise Manager includes interfaces for these new advisors with 11g.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
Note:265831.1 - Automatic Checkpoint Tuning in 10g
Note:274264.1 - REDO LOGS SIZING ADVISORY
Note 180894.1 - “V$INSTANCE_RECOVERY”
Note 151062.1 - Init.ora Parameter “FAST_START_MTTR_TARGET” Reference Note
Note 30754.1 - Init.ora Parameter “LOG_CHECKPOINT_INTERVAL” Reference Note
Note 30755.1 - Init.ora Parameter “LOG_CHECKPOINT_TIMEOUT” Reference Note
Note 68931.1 - Init.ora Parameter “FAST_START_IO_TARGET” Reference Note
http://psoug.org/reference/dbms_advisor.html

I mentioned Julian Dyke’s studies before on my blog, especially his presentations on slide show mode, they are important stuff if you want to understand Oracle internals deeper.

So no additional words needed for his new study -
http://www.juliandyke.com/Internals/Oracle11_1/Oracle11_1.html

Thank you Julian for sharing :)

Okey, it is getting hot :)

1. Download Oracle Database 11g Release 1 (11.1.0.6.0)
Standard Edition, Standard Edition One, and Enterprise Edition - Linux x86 (1.7 GB)

2. Oracle Database 11g Release 1 Documentation Library
Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Upgrade Guide 11g Release 1 (11.1)
Oracle® Database New Features Guide 11g Release 1 (11.1)
“What’s New in 11g” search on documentation

3. Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda Oracle ACE Director
Database Replay
Partitioning to Perfection
Schema Management
Adaptive Cursors and SQL Plan Management
Transaction Management

4. Installing 11g Release 1 on Xubuntu 7.04 by Howard Rogers

Yesterday Oracle Türkiye was our guest and we talked on the new features of the new release. Below you will find some presentations which summaries why you might want to migrate to 11g.

Creating test environments was one of our chronic problems and with 11g Standby Snapshots and Real Application Testing(RAT) features, managing testing quality will be much more easier for us. Also real time physical standby database feature for reporting purposes is another great feature.

11g on Linux will be download able from OTN this month and I also hope we will be enjoying a new free Express Edition(XE) version soon.

10g Release 2 is a great release for our needs and each day I continue to learn new things about it, but this is for today of course.

Also I still believe in second releases :) I hope you also enjoy the 11g new features -

11g General
11g Application Development
11g Manageability
11g Very Large Database
11g High Availability
11g Grid and OLTP
11g Real Application Testing
Why 11g? (In Turkish)

You may also want to visit these 11g references -

Oracle 11G database on OTN
Mr.Arup Nanda is again preparing one of his great top features series for 11g
Amis Blogs on 11g new features
psoug.org started publishing 11gR1 demos