Arrived in Manhatten

After 8 hours of flight,
reading Jonathan Lewis’ Cost Based Oracle,
I have arrived in New York.
I will deliver a special training
here next week.
The city is not very crowdy
because most New Yorkers
have left for a long weekend.

Contemporary art is all around here.
I will take a day off tomorrow
and get into it.
No forums, newsgroups, blogs
tomorrow…
we will see!

connect lutz hartmann as sysdba

Beautiful Bern

This week I was in Bern, the capital of Switzerland for two days. I have delivered a specially condensed New Features training there.

Bern is one of the most charming cities I have visited .

I was happy to see that not much damage
is left from the great flood last year.

connect lutz hartmann as sysdba

How to use Fast Incremental Backups with Block Change Tracking with Oracle 10g

This is an article I have pubished in SOUG Newletter in May 2007.

In Oracle 9i we could create incremental backups with level 0 to 4.
A level 0 backup is a full backup.
In Oracle 10g there are still these levels but we only use incremental level 0 and 1 backups with Oracle’s suggested backup strategy.

Starting with Oracle 10g RMAN can take incremental backups without having to read the entire datafiles in order to find out which blocks have changed since the last backup.
This new feature is called FAST INCREMENTAL BACKUP.
The new technology used for this feature is called BLOCK CHANGE TRACKING.

You enbale block change tracking by making an entry in the controlfile:
SQL> alter database enable block change tracking;
alter database enable block change tracking
*
ERROR at line 1:
ORA-19773: must specify change tracking file name

If you do not have DB_CREATE_FILE_DEST specified (used for OMF => oracle managed files)
you will encounter an error if you do not specify a name fot the change tracking file
SQL> show parameter create
NAME TYPE VALUE
———————————— ———– ——————————
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

So I try to alter this parameter:
SQL> alter system set db_create_file_dest=’/home/oracle/file_create’;
alter system set db_create_file_dest=’/home/oracle/file_create’
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be
translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory

Oracle checks if the location existes before it allows us to change this parameter!
SQL> ! mkdir -p /home/oracle/file_create
SQL> alter system set db_create_file_dest=’/home/oracle/file_create’;
System altered.

Now we can enable block change tracking
SQL> alter database enable block change tracking;
Database altered.
As an alternative you can specify a user managed filename if you do not want to use the parameter:
alter database enable block change tracking using file ‘/home/oracle/change.trc’;

Let’s take a look at the alert_log now:
alter database enable block change tracking
Thu May 18 06:39:14 2006
ORA-19773 signalled during: alter database enable block change tracking…
Thu May 18 06:39:46 2006
alter database enable block change tracking using file
‘/home/oracle/change.trc’
Thu May 18 06:39:46 2006
Block change tracking file is current.
Starting background process CTWR
CTWR started with pid=21, OS id=26871
Block change tracking service is active.
Thu May 18 06:39:47 2006
Completed: alter database enable block change tracking using file
‘/home/oracle/change.trc’

This is what oerr returns for this error:
[oracle@kr12-KL2 bdump]$ oerr ora 19773
19773, 00000, “must specify change tracking file name”
// *Cause: No file name was specified with the ALTER DATABASE ENABLE
// CHANGE TRACKING command, and the DB_CREATE_FILE_DEST parameter
// was not set.
// *Action: Either specify a file name, or set the DB_CREATE_FILE_DEST
// parameter.

Let’s take a look at the change tracking file:
[oracle@edchr3p8 oracle]$ cd file_create/
[oracle@edchr3p8 file_create]$ ll
total 4
drwxr-x— 3 oracle oinstall 4096 May 3 15:55 ORCL
[oracle@edchr3p8 file_create]$ cd ORCL/
[oracle@edchr3p8 ORCL]$ ll
total 4
drwxr-x— 2 oracle oinstall 4096 May 3 15:55 changetracking
[oracle@edchr3p8 ORCL]$ cd changetracking/
[oracle@edchr3p8 changetracking]$ ll
total 11348
-rw-r—– 1 oracle oinstall 11600384 May 3 15:55 o1_mf_25l66r5c_.chg
# this is an OMF change tracking file!

The change tracking file has an initial size of 10Mb.
How to monitor the change treacking file from inside Oracle:
SQL> desc v$block_change_tracking
Name Null? Type
—————————————– ——– —————————-
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
———- ————————————————– ———-
ENABLED /home/oracle/change.trc 11599872

Now that we have enabled block change tracking a new background process called CTWR (change track writer) is started and it will automatically be started for every new instance.
[oracle@kr12-KL2 ~]$ ps -ef grep orcl
oracle 9531 1 0 May16 ? 00:00:00 ora_pmon_orcl
oracle 9533 1 0 May16 ? 00:00:02 ora_psp0_orcl
oracle 9535 1 0 May16 ? 00:00:00 ora_mman_orcl
oracle 9537 1 0 May16 ? 00:00:06 ora_dbw0_orcl
oracle 9539 1 0 May16 ? 00:00:16 ora_lgwr_orcl
oracle 9541 1 0 May16 ? 00:00:17 ora_ckpt_orcl
oracle 9543 1 0 May16 ? 00:00:25 ora_smon_orcl
oracle 9545 1 0 May16 ? 00:00:00 ora_reco_orcl
oracle 9547 1 0 May16 ? 00:00:03 ora_cjq0_orcl
oracle 9549 1 0 May16 ? 00:00:18 ora_mmon_orcl
oracle 9551 1 0 May16 ? 00:00:02 ora_mmnl_orcl
oracle 9554 1 0 May16 ? 00:00:00 ora_d000_orcl
oracle 9556 1 0 May16 ? 00:00:00 ora_s000_orcl
oracle 9560 1 0 May16 ? 00:00:00 ora_qmnc_orcl
oracle 9574 1 0 May16 ? 00:00:06 ora_q000_orcl
oracle 10746 1 0 May16 ? 00:00:00 ora_q001_orcl
oracle 26854 26850 0 06:38 ? 00:00:00 oracleorcl (DESCRIPTION=
(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26871 1 0 06:39 ? 00:00:00 ora_ctwr_orcl
oracle 27778 1 0 06:50 ? 00:00:00 ora_j000_orcl
oracle 27780 27153 0 06:50 pts/0 00:00:00 grep orcl

CTWR will track addresses of blocks which have changed since the last backup in the change tracking file from now on.
RMAN can use this information for the next incremental backup. It will be able to find out which block must be written to the backupset by just reading the change tracking file.
RMAN will not have to read the entire datafiles into the SGA in order to find out which blocks must be backed up as it had to do before 10g.
This methode is much faster.

Now what happens if we lose the change tracking file or if it gets corrupted?
Let’s corrupt is:
[oracle@edchr3p8 changetracking]$ echo ‘hallo’ > o1_mf_25l66r5c_.chg
And now let us startup the database with a corrupt change tracking file!
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
‘/home/oracle/file_create/ORCL/changetracking/o1_mf_25l66r5c_.chg’
ORA-27038: created file already exists
Additional information: 1
ORA-27047: unable to read the header block of file
Linux Error: 2: No such file or directory
Additional information: 1
Wed May 3 16:03:30 2006
CHANGE TRACKING is enabled for this database, but the
change tracking file can not be found. Recreating the file.

Oracle tries to automaqtically recreate the missing file but it cannot overwrite an existing on!

Let us create some changed blocks now so Oracle cannot track them in the change tracking file!

SQL> update hr.employees set salary=salary*1.1;
107 rows updated.
SQL> commit;
Commit complete.

Now let us take a look at the ALERT_LOG:

CHANGE TRACKING ERROR 19756, disabling change tracking
Wed May 3 16:07:24 2006
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_ctwr_6628.trc:
ORA-19756: corrupt block number 18 found in change tracking file
ORA-19750: change tracking file: ‘/home/oracle/file_create/ORCL/changetracking/o1_mf_25l6qckh_.chg’
Block change tracking service stopping.
Deleted Oracle managed file /home/oracle/file_create/ORCL/
changetracking/o1_mf_25l6qckh_.chg

Oracle automatically deletes a currupted change tracking file and stops block change tracking.

Let us now take an incremental backup with RMAN:
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 03-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2006_05_03/ o1_mf_nnnd1_TAG20060503
T160745_25l6x2s1_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/
backupset/2006_05_03/o1_mf_ncsn1_TAG20060503
T160745_25l6z4mv_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-06

RMAN was able to take an incremental backup but now it had to use the old methode => read all the blocks in order to find out which ones have changed since the last backup;

Next I replace the change tracking file with an old version in order to see what happens when I try to take an incremental backup:

SYS @10gR2 SQL > ! mv /home/oracle/change.old /home/oracle/change.trc
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 19-MAY-06
using channel ORA_DISK_1
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_nnnd1_TAG20060519T123227_26v7owgo_.bk p tag=
TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_example_26v7oyjx_.dbf tag=TAG20060519T123227
recid=3 stamp=590848373
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/sales1.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_sales_26v7pr8j_.dbf tag=TAG20060519T123227 rec id=4 stamp=590848386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_users_26v7qb8t_.dbf tag=TAG20060519T123227 rec id=5 stamp=590848394
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_ncsn1_TAG20060519T123227_26v7qfb5_.bk p tag=TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
RMAN> list backup of tablespace sysaux;
There is no backup!!!
RMAN has found out that I tried to cheat and that it was not able to create an incremental backup for all files by using the change tracking file.
This is what oerr says about ORA-19694:
[oracle@lutzasm ~]$ oerr ora 19694
19694, 00000, “some changed blocks were not found in the change tracking file”
// *Cause: A backup or copy found that some changed blocks had not been
// recorded in the change tracking file. The details of which files
// and blocks are affected will be in an Oracle trace file.
// *Action: This indicates that there is a problem with the change tracking
// feature. Disable change tracking and re-start the backup.

connect lutz hartmann as sysdba

How to check if a tablespace is transportable

In order to find out if a tablespace or a tablespace set is transportable you can use the package DBMS_TTS.

This is the interface of the package:
sys@10gr2 SQL> desc dbms_tts
PROCEDURE DOWNGRADE
FUNCTION ISSELFCONTAINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN
FULL_CHECK BOOLEAN IN
PROCEDURE KCP_CKCMP
FUNCTION TRANSPORT_CHAR_SET_CHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
ERR_MSG VARCHAR2 OUT
PROCEDURE TRANSPORT_CHAR_SET_CHECK_MSG
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
PROCEDURE TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT

This is how you use the package:
sys@10gr2 SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK(‘LUTZ_TS’, TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK(‘SYSTEM‘, TRUE)
BEGIN dbms_tts.TRANSPORT_SET_CHECK(‘SYSTEM’, TRUE); END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 899
ORA-06512: at “SYS.DBMS_SQL”, line 19
ORA-06512: at “SYS.DBMS_TTS”, line 838
ORA-29351: can not transport system, sysaux, or temporary tablespace ‘SYSTEM’
ORA-06512: at “SYS.DBMS_TTS”, line 867
ORA-06512: at line 1

After you have checked the tablespace with the procedure TRANSPORT_SET_CHECK you can use a table call transport_set_violations which is owned by SYS to look at error messages from the procedure.
This table will contain the messages from the last run of the procedure and it will retained only for the duration of the session.
In other words, it will be refreshed with every new session.

sys@10gr2 SQL> desc transport_set_violations
Name Null? Type
—————————————– ——– —————————- VIOLATIONS VARCHAR2(2000)

Now SYS creates a table in the tablespace I want to check …
sys@10gr2 SQL> create table test_sys tablespace lutz_ts as select * from hr.test;
Table created.

And I check if the tablspace is transportable…

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK(‘LUTZ_TS’, TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
——————————————————————————–Sys owned object TEST_SYS in tablespace LUTZ_TS not allowed in pluggable set

This would be the error message if the tablespace is not self containing…

sys@10gr2 SQL> sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
——————————————————————————–
Constraint TESXT_FK between table SYS.TEST_SYS in tablespace USERS and table HR.
TEST in tablespace LUTZ_TS

Starting with Oracle 10gR2 we can create transportable tablespaces from backup.

connect lutz hartmann as sysdba

CS CRUISING ORACLE

Right after I had arrived from New Jersey I delivered a short training at a very special event which Credit-Suisse had aranged.
They had hired a ship on the Lake Zurich and invited their top DBAs to come and cruise Oracle for one day.

I had the first 30 minute slot and talked about New Features of RMAN in Orcale 10gR2.

All together there were five speakers on this event
amoung others
Christina Antonigni,

who talked about Automatic Segment Space Management

and Jonathan Lewis

who flew in by helicopter so that he could make it in time from his Indexing and Troubleshooting Seminar at the Zurich Development Center. He was picked up by the boat on the way.

Jonathan Lewis talked about the propper usage of V$ and X$-Views.

I got to know Lutz Fröhlich, who is an ORACLE 9i CERTIFIED MASTER and was recently hired by CS. He is the leader of the DWH-team now. Sorry, but I forgot to take a picture

connect lutz hartmann as sysdba

Jonathan Lewis Event in Zurich May 11.th-12th. 2006

I am so lucky that I made it to Zurich in time and can take part in this very special session at least.

Jonathan Lewis delivers a very highly condensed presentation in a two day event.
The first day, which I missed most of, what a pity!, was about INDEXING STRATEGIES.
Today he speakes about TROUBLE-SHOOTING and TUNING.

There are about 40 people attending.
Again Oracle Education chose this fantastic

ZURICH DEVELOPMENT CENTER as location for the event.

Jonathan made a very funny “demo of ORACLE 11g”
which he faked, and I believe most of us believed he already had access to the next release. Look at this, it’s Oracle 11g but it is faked , dont’t believe what you see here!

He “demonstrated” something he called HAL
(stands for Heuristic Algorythmic Linguistic:

=> sees what happened
=> worksout what to do next
=> tells you all about it)
and was “implemented” through a hint /*+go_faster*/

This was really fun!

One lucky attendee won a huge model of the Oracle yacht which was taking part the last Americas Cup.

Every attendee got a copy of Jonthan Lewis’ latest book on the CBO.

connect lutz hartmann as sysdba

Iselin NJ Event part 2

Yesterday I have finished my special Class in New Jersey education center.
These guyes again gave me a hard time , trying to even be harder than their collegues in Chicago last week.

It was a group of also very experienced DBAs
and we had a fun time.
These are Ying, Mark, Alex, Miguel, Mangerh,
Sridhar and me on the last day

In the New Jersey Oracle Education Center
I got to know a very nice collegue of mine from Dallas.
His name is Lester Wells.
He delivered a Grid Control class there.
Thanks for being a light in the dark for me Lester!

Right after the event I headed for Kennedy Airport

and left for Zurich.

On the plane was a large group of orthodox Jews
who all of a sudden assembled in the middle of the plane

for praying.

Having arrived in Zurich I headed directely to the
Zurich Development Centerin order not to miss
Jonathan Lewis.

I will report in my next post.

connect lutz hartmann as sysdba

Iselin NJ Event Part 1

Just arrived in Iselin New Jersey.
I will deliver a specially tailored
New Features for Administrators 10gR2 this week once again.

connect lutz hartmann as sysdba

My Chicago Training

This week I delivered a customized training for a bunch of very advanced DBAs in Chicago.

The location of the Training Center is so sophisticated (the walls in the building are all made of green marble from ground to top floor, well at least to 14th. floor, that’s where the Oracle Training Center is located)

These guyes have really given me a hard time.
They were not interested in the courseware at all
but only in the technical internals of the New Features.
Hardest course I had, ever!
But fun also.

Somashekar

Alan

Selvam

Bhavesh

Warren

Sorry, I forgot to take a picture of Ravi.

They are all very highly skilled DBAs, never trusting any new features, always trying everything out until they really have proved that it does what they expect it to do.
It was a pleasure meeting you guys.

connect lutz hartmann as sysdba

Active Session History in Oracle 10gR1+2

This article has been published in the Swiss Oracle User Group’s Newsletter 4/2006 (October 2006).

This article descibes the new on Oracle Database 10g Active Session History, a kind of data warehouse for session statistics.

A lot of the information in this post I have taken from the book
  Oracle Wait Interface by the
authors R.Shee, K.Deshpande and K.Gopalakrishnan
which I can strongly recommed to everybody
who has to deal with performance tuning for ORACLE of which release ever.
It covers the Oracle versions until 10gR1 and tells us everything we have ever wanted to know about latches, enqueues and all the wait events.

Starting with the version 10gR1 Oracle stores information about session activity on disk in the Automatic Workload Repository (AWR) which sits, amoung other things, in the new mandatory SYSAUX tablespace.
This information comes from statistics about active sessions which the Oracle Kernel collects automatically.
The active session history is enabled by default( influenced by the hidden parameter _ASH_ENABLE=true).

The main components of the ASH are
- two new background procresses, MMON and MMNL,
- a rolling buffer, which sits in the SGA fixed area and holds the historical statistical information of active sessions

This buffer has a minimum size of 1Mb in ORACLE 10gR1 and can grow to a maximum of either 30Mb, or 5% of SHARED_POOL_SIZE, or Numer of CPUs * 2Mb (whichever is smaller!).

This formula has been changed in 10gR2 to not linearly scale with
the number of CPUs.
All we get to know about the sizing is that “…the ASH will
auto-configure itself to try and hold at least one-hour worth of
instance activity without wasting too much value SGA space and will
never be bigger than 2% of SGA_TARGET or 5% of SHARED_POOL (if AutoSGA
is not used)“.

The contents of the rolling buffer get written down to disk on a regular basis by MMNL
( with every snapshot created and when the buffer gets full).
It gets flushed to the table WRH$_ACTIVE_SESSION_HISTORY in the AWR which is implemented like a facts table in a DWH with its columns as dimensions.
This is enbled with the hidden parameter _ASH_DISK_WRITE_ENABLE=true.

The information in the ASH is used, among others, by automatic tuning utilities like teh SQLTUNING Advisor (dbms_sqltune) for the Automatic Tuning Optimizer (ATO). This one will look for historical data about sql-statements and their exetutions in the ASH (see buttom) as well, anong other activities it can perform in order top find a potentially better execution plan.

There are a lot of DBA_ views we can use to read the historical information from the AWR:
SELECT table_name
FROM dictionary
WHERE table_name like ‘DBA/_HIST/_%’ ESCAPE ‘/’
ORDER BY table_name;

TABLE_NAME
—————————–
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY
DBA_HIST_BUFFERED_QUEUES
DBA_HIST_BUFFERED_SUBSCRIBERS
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_COMP_IOSTAT
DBA_HIST_CR_BLOCK_SERVER
DBA_HIST_CURRENT_BLOCK_SERVER
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_DATAFILE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_DLM_MISC
DBA_HIST_ENQUEUE_STAT
DBA_HIST_EVENT_NAME
DBA_HIST_FILEMETRIC_HISTORY
DBA_HIST_FILESTATXS
DBA_HIST_INSTANCE_RECOVERY
DBA_HIST_INST_CACHE_TRANSFER
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_MISSES_SUMMARY
DBA_HIST_LATCH_NAME
DBA_HIST_LATCH_PARENT
DBA_HIST_LIBRARYCACHE
DBA_HIST_LOG
DBA_HIST_METRIC_NAME
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_OPTIMIZER_ENV
DBA_HIST_OSSTAT
DBA_HIST_OSSTAT_NAME
DBA_HIST_PARAMETER
DBA_HIST_PARAMETER_NAME
DBA_HIST_PGASTAT
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_PROCESS_MEM_SUMMARY
DBA_HIST_RESOURCE_LIMIT
DBA_HIST_ROWCACHE_SUMMARY
DBA_HIST_RULE_SET
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_HIST_SERVICE_NAME
DBA_HIST_SERVICE_STAT
DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SESSMETRIC_HISTORY
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_SNAPSHOT
DBA_HIST_SNAP_ERROR
DBA_HIST_SQLBIND
DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_WORKAREA_HSTGRM
DBA_HIST_STAT_NAME
DBA_HIST_STREAMS_APPLY_SUM
DBA_HIST_STREAMS_CAPTURE
DBA_HIST_STREAMS_POOL_ADVICE
DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY
DBA_HIST_SYSSTAT
DBA_HIST_SYSTEM_EVENT
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_TABLESPACE_STAT
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_TEMPFILE
DBA_HIST_TEMPSTATXS
DBA_HIST_THREAD
DBA_HIST_UNDOSTAT
DBA_HIST_WAITCLASSMET_HISTORY
DBA_HIST_WAITSTAT
DBA_HIST_WR_CONTROL
78 rows selected.

The rolling buffer can be viewed by querying the relevant v$view:
SELECT table_name
FROM dictionary
WHERE table_name like ‘V$%SESSION%HISTORY’
ORDER BY table_name;

TABLE_NAME
——————————
V$ACTIVE_SESSION_HISTORY
V$SESSION_WAIT_HISTORY
2 rows selected.
You can use a query like this to look at the session activity in a certain peride of time with a query like this:
SELECT session_id, event, COUNT(*), SUM(time_waited)
FROM v$active_session_history
WHERE session_state= ‘WAITING’
AND time_waited > 0
AND sample_time >= (sysdate-&MinutesAgo/(24*60))
GROUP BY session_id, event;

Enter value for minutesago: 5
old 5: AND sample_time >= (sysdate-&MinutesAgo/(24*60))
new 5: AND sample_time >= (sysdate-5/(24*60))

SESSION_ID EVENT COUNT(*) SUM(TIME_WAITED)
—————- ——– ————- ——————————
149 db file sequential read 3 40857
149 db file scattered read 1 1530
164 db file sequential read 1 12208

The autors also describe how to dump the contents of the rolling buffer to a trace file in USER_DUMP_DEST in order to use sqlldr to load the information into another database.

ALTER SESSION SET events ‘immediate trace name ashdump, level 10′;

You can also use the utility oradebug to dump the rolling buffer:

SYS @10gR2 SQL > oradebug setmypid
Statement processed
SYS @10gR2 SQL > oradebug unlimit
Statement processed
SYS @10gR2 SQL > oradebug dump ashdump 10
Statement processed
SYS @10gR2 SQL > oradebug tracefile_name
Statement processed
SYS@10gR2 SQL > oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5851.trc

– first lines: column names for data
– trace data displayed as comma separated list of values
– can use sqlldr to load the data into another DB for further analysis
– can be helpfull in cases of hanging system

connect lutz hartmann as sysdba