this blog is a success!! thank you for using it!

Looking at the statistics of my blog I get very happy.
I seam to write about things which are interesting for people.

Thank you for coosing my blog!
And thank you for your feedback and comments!

connect lutz hartmann as sysdba

Chicago I

Just arrived in Chicago Downtown.
I will deliver a customized 10gR2 New Features for Administrators as a private event next week (May 1st.-3rd).

Haved been reading “Cost based Oracle” by Jonathan Lewis during the flight. This book is like a thriller for me – really exciting!

connect lutz hartmann as sysdba

ORACLE SECURE BACKUP now available

ORACLE has announced general availability of its new Media Management Server Oracle Secure Backup and Oracle Secure Backup Express.

This is Oracle’s new tape backup management software, delivering secure, high performance network tape backup for Oracle databases and file systems.
With Oracle Secure Backup (OSB) you can not only backup database files but also the entire ORACLE stack including
- application servers
- collaboration suites
as well as OS-file systems.

OSB is capable of discovering tape libraries which are available within a so called administrative doamin (which you define), it can handle barcode readers and change tapes and you can use it to backup any maschine on which either an OSB client is running or an NDMP-demon (network data management protocol) is started.
OSB is strongly integrated with RMAN and can be accessed
- through a web interface
- a command line tool
- enterprise manager

For more info pls see the following locations:
The official announcement is here.
The product description can be found here.
Technical information for DBAs and Developers can be found here.
A white paper about OSB on OTN is here.
OSB on OTn is here.

connect lutz hartmann as sysdba

How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINK

Hanging around in my hotel in Chicago because it is raining, I have been cruising around in the OTN forums which inspired me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.

The High Watermark is the maximum fill-grade a table has ever reached.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.

First let’s have a look at the question when space is allocated

- when you create a table at least one extent (contiguous blocks) is allocated to the table
- if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
- if you have not specified MINEXTENTS then exactely one extent
will be allocated (we will look at extent sizes later in another post).

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.

Now let us take a look at when space is released again from a segment like a table or index:

Let’s asume that we have filled a table with 100’0000 rows.
And let’s asume that we deleted 50’000 rows afterwards.
In this case the high watermark will have reached the level of 100’000 and will have stayed there. Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements …) .
Furthermore the physical addresses of the deleted row get recycled by new rows.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl … direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

So how can we release unused space from a table?

There are a number of possible options which are already available before Oracle 10g:
- What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
- Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.

With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
- the rows will have new physical addresses and
- the high watermark will be adjusted.
But for this:
- we need a full (exclusive) table lock
- the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;

This statement will procede in two steps:
- The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
- The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.

Table shrinking…
- will adjust the high watermark
- can be done online
- will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
- indexes will be maintained and remain usable
- can be made in one go
- can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:

- ALTER TABLE emp SHRINK SPACE; – only for the emp table
- ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)
)

How are the indexes maintained?
In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement. Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.
Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.

The following restrictions apply to table shrinking:

1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
- UNDO segments
- temporary segments
- clustered tables
- tables with a colmn of datatype LONG
- LOB indexes
- IOT mapping tables and IOT overflow segments
- tables with MVIEWS with ON COMMIT
- tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.

This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.

You can use the built in package DBMS_SPACE to run the advisor manually as well.
I will blog about this later on some time.

connect lutz hartmann as sysdba

Jonathan Lewis, the master of the cost based optimizer

I am reading the latest book by Jonathan Lewis, Cost-Based Oracle Fundamentals right now.
It is a compilation of all the in depth knowlege about the Cost-Based Optimizer we have been looking for since ages.
And it covers the history of the CBO from ORACLE 8i until ORACLE 10g.

It is really amazing how Jonathan Lewis explains, step by step,
what the CBO is all about and how it works.
And this in a way which can be understood even by non-insider-gurus,
although he really goes into details.
Jonathan Lewis encourages the reader to try the examples from the book practically in real life and find out what happens. The sample code is available for download from the apress site.

As Tom Kyte wrote in the foreward of the book:
“The insight that Jonathan provides into the working of the CBO will make a DBA a better designer and a developer a better SQL coder. Both groups will become better troubleshooters”.

I can only recommed this book to everybody who has to deal with ORACLE performance issues.
It will be worth the money.

I am really looking foreward to meeting Jonathan Lewis in Zurich on May 11th.-12th.
where he will deliver some very special trainings about troubleshooting and indexing.
For everybody who might be interested in taking part in this event, here is the info about bookings.

connect lutz hartmann as sysdba

Asynchronous Commit – a New Feature in ORACLE 10gR2

“In the old times” the LGWR had to write to the redo-logs with every COMMIT.

Starting with ORACLE 10gR2 we can use the initialization parameter
COMMIT_WRITE to change the commit behaviour
on SYSTEM- as well as on SESSION-LEVEL.

We can allow LGWR to consider itself
- when to write to disk and
- when control is returned to the client.

By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.
By specifiying IMMEDIATE or BATCH you can influence when LGWR will write to disk.

If you do not explicitly specify this advanced parameter:
- – the default is IMMEDIATE, WAIT
- the database by default will write commit records to disk for every commit
- control is returned to the client after the writing is completed.
- the client receives the massage COMMIT COMPLETE.

If you set COMMIT_WRITE to NOWAIT and you do not specify anything else:
- the default is IMMEDIATE, NOWAIT
- LGWR will write to the redologs directely after every commit
- it will not wait until the writing is completed before it sends the next writing process to the redologs
- the client will have control before the writing is completed.

If you set COMMIT_WRITE to BATCH and you do not specify anything else:
- the default is BATCH, WAIT
- LGWR will consider itself when to write to disk from the LOG_BUFFER
- LGWR will not write with every COMMIT
- the client must wait until the I/O of LGWR is completed

Changing this parameter has its drawbacks and advantages and it is you who has to descide which death to dye:
- the majour drawback is obviousely that you cannot be sure anymore that your changes are persistently saved even after commit which can mean that you might lose even committed data in the case of an instance crash.
- the advantage can be that concurrently committing sessions do not have to to wait for each other ‘s commit to complete => less wait events.
This parameter, again, is not a basic but an advanced parameter, which means that the default is o.k. in the first place and you would think about changing its value to whatever only after you encounter significant numbers of wait event with concurrent commits.
Because it is good to know that the committed changes are persistently changed and can be applied again in case of crash recovery.

•

You change the parameter by providing the values separated by a comma:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT;

POSSIBLE COMBINATIONS:
- IMMEDIATE,NOWAIT
- IMMEDIATE,WAIT
- BATCH,NOWAIT
- BATCH,WAIT

connect lutz hartmann as sysdba

Transportable Tablespaces from Backup with RMAN in Oracle 10g Release 2

Before Oracle 10g it was also possible to create transportable tablespaces
(they were introduced with ORACLE 8i):
we have used export-utility (exp) to create them.
- first we had to make the tablespace READ ONLY before exporting,
- then we had to used export utility to dump the tablespace metadata.
(In the exp-dumpfile only the metadata of the tablespace are dumped)
- and after we shipped the datafiles from the source system to the target system we could use the import utility(imp) to import the metadata from the dumpfile and
- finally we could take the tablespace in the target system read write.

This procedure had a number of significant limitations:
1. The source- and target-platforms had to be exactely the same(up to the last OS-package)
2. The source- and target-database had to exactely the same version (up to the last patchset)
3. The tablespace had to be made READ ONLY before exporting the metadata

Since ORACLE 10gR2 we can now use RMAN to create a transportable tablespace-set from Backup.
Now we do not need to make the tablespace(s) READ ONLY any more.

Here are the steps RMAN will take to do so:
1. RMAN connects to the source-database
2. RMAN creates an AUXILIARY instance and an AUXILIARY database which constists of a SYSTEM- and an UNDO-tablespace and the tablespace(s) we want to export. For this RMAN uses a backupset of the source database.
3. RMAN recovers the AUXILIARY database to the desired point in time
4. RMAN uses export-datapump (expdp) to create a dumpfile-set:
this consists of:
- the the datafiles which represent the transportable-tablespace-set,
- the dumpfile of the export con taining the metadata of the tablespace-set
- a sql-script which can be used to import the metadata into the target system
5. RMAN removes the AUXILIARY instance from the host.

And here is a demo:
I have written a little sql-script which contains an RMAN-run-block:

[oracle@lutz1 oracle]$ rman target / @tts.sql

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Feb 7 02:57:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1106297266)
RMAN> run {
2> TRANSPORT TABLESPACE ‘LUTZ_TEST’
3> AUXILIARY DESTINATION ‘/home/oracle/AUX/tts’
4> DUMP FILE ‘lutz_test.dmp’
5> EXPORT LOG ‘lutz_test.log’
6> IMPORT SCRIPT ‘lutz_test_im.sql’
7> TABLESPACE DESTINATION ‘/home/oracle/AUX/tts’
8> UNTIL TIME=”to_date(’07-02-2006:02:57:00′, ‘dd-mm-yyyy:hh24:mi:ss’)”;
9> }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies
to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID=’yigu’

initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_yigu
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/home/oracle/AUX/tts
control_files=/home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f


starting up automatic instance ORCL

Oracle instance started
Total System Global Area 201326592 bytes
Fixed Size 1218508 bytes
Variable Size 146802740 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time “to_date(’07-02-2006:02:57:00′, ‘dd-mm-yyyy:hh24:mi:ss’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log for tspitr to a resent until time
sql ‘alter system archive log current’;
# avoid unnecessary autobackups for structural changes during TSPITR
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 07-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=36 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/ORCL/
autobackup/2006_02_07/o1_mf_s_581741757_1yhzkgp5_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/opt/oracle/flash_recovery_area/ORCL/
autobackup/2006_02_07/o1_mf_s_581741757_1yhzkgp5_.bkp tag=TAG20060207T025557
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output filename=/home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f
Finished restore at 07-FEB-06

sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_SBT_TAPE_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time “to_date(’07-02-2006:02:57:00′, ‘dd-mm-yyyy:hh24:mi:ss’)”;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination filename for restore
set newname for clone datafile 3 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 6 to
“/home/oracle/AUX/tts/lutz_test.dbf”;
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 3, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone “alter database datafile 1 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile 2 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile 3 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile 6 online”;
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace “LUTZ_TEST”, “SYSTEM”,
“UNDOTBS1″, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone “create tablespace aux_tspitr_tmp
# datafile ”/tmp/aux_tspitr_tmp.dbf” size 500K”;
}
executing Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 07-FEB-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy recid=2 stamp=581741278 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_system_1yhyzg1w_.dbf
destination for restore of datafile 00001:
/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_1yhzoc4n_.dbf recid=8 stamp=581741970
channel ORA_AUX_DISK_1: restoring datafile 00002
input datafile copy recid=5 stamp=581741387 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_undotbs1_1yhz5np1_.dbf
destination for restore of datafile 00002: /home/oracle/AUX/tts/
TSPITR_ORCL_YIGU/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_undotbs1_1yhzrccl_.dbf
recid=9 stamp=581741983
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy recid=3 stamp=581741352 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_sysaux_1yhz2s7c_.dbf
destination for restore of
datafile 00003: /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003
output filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_1yhzrm6x_.dbf
recid=10 stamp=581742036
channel ORA_AUX_DISK_1: restoring datafile 00006
input datafile copy recid=6 stamp=581741397 filename=/opt/oracle/flash_recovery_area/ORCL/
datafile/o1_mf_lutz_tes_1yhz63wv_.dbf
destination for restore of datafile 00006:
/home/oracle/AUX/tts/lutz_test.dbf
channel ORA_AUX_DISK_1:
copied datafile copy of datafile 00006
output filename=/home/oracle/AUX/tts/lutz_test.dbf
recid=11 stamp=581742044
Finished restore at 07-FEB-06

datafile 1 switched to datafile copy
input datafile copy recid=12 stamp=581742047 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_system_1yhzoc4n_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=13 stamp=581742048 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_undotbs1_1yhzrccl_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=14 stamp=581742048 filename=/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/
datafile/o1_mf_sysaux_1yhzrm6x_.dbf
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=581742048 filename=/home/oracle/AUX/tts/lutz_test.dbf

sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 6 online

Starting recover at 07-FEB-06
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/
archivelog/2006_02_07/o1_mf_1_10_1yhzo2j8_.arc
archive log filename=/opt/oracle/flash_recovery_area/ORCL/
archivelog/2006_02_07/o1_mf_1_10_1yhzo2j8_.arc
thread=1 sequence=10media recovery complete, elapsed time: 00:00:01
Finished recover at 07-FEB-06
database opened

contents of Memory Script:
{
#mark read only the tablespace that will be exported
sql clone “alter tablespace LUTZ_TEST read only”;
# create directory for datapump export
sql clone “create or replace directory STREAMS_DIROBJ_DPDIR as ”
/home/oracle/AUX/tts””;
# export the tablespaces in the recovery set
host ‘expdp userid=\”/@\(DESCRIPTION=\(ADDRESS=(PROTOCOL=beq\)(PROGRAM=/opt/oracle/product/db10_2/bin/oracle\)
\(ARGV0=oracleyigu\)\(ARGS=^’\(DESCRIPTION=\(LOCAL=YES\)
(ADDRESS=\(PROTOCOL=beq\)\)\)^’\)\(ENVS=^’ORACLE_SID=yigu^’\)\)
\(CONNECT_DATA=\(SID=yigu\)\)\) as sysdba\” transport_tablespaces=
LUTZ_TEST dumpfile=
lutz_test.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
lutz_test.log’;
}
executing Memory Script

sql statement: alter tablespace LUTZ_TEST read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ”/home/oracle/AUX/tts”

Export: Release 10.2.0.1.0 - Production on Tuesday, 07 February, 2006 3:01:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″: userid=”/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)
(PROGRAM=/opt/oracle/product/db10_2/bin/oracle)(ARGV0=oracleyigu)
(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))
(ENVS=ORACLE_SID=yigu))(CONNECT_DATA=(SID=yigu))) AS SYSDBA” transport_tablespaces= LUTZ_TEST dumpfile=lutz_test.dmp directory=STREAMS_DIROBJ_DPDIR logfile=lutz_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/AUX/tts/lutz_test.dmp
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 03:02:44

host command complete
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= ‘lutz_test.dmp’ transport_datafiles= /home/oracle/AUX/tts/lutz_test.dbf
*/
————————————————————–
– Start of sample PL/SQL script for importing the tablespaces
————————————————————–
– creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS ‘/home/oracle/AUX/tts/’;
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS ‘/home/oracle/AUX/tts’;
/* PL/SQL Script to import the exported tablespaces */
DECLARE
— the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
— the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
— names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
— dump file name and location
dump_file.file_name := ‘lutz_test.dmp’;
dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
— forming list of datafiles for import
tbs_files( 1).file_name := ‘lutz_test.dbf’;
tbs_files( 1).directory_object := ‘STREAMS$DIROBJ$1′;
— import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
— output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
END LOOP;
END IF;
END;
/
– dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
————————————————————–
– End of sample PL/SQL script
————————————————————–

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /home/oracle/AUX/tts/cntrl_tspitr_ORCL_yigu.f
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_system_1yhzoc4n_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_undotbs1_1yhzrccl_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_sysaux_1yhzrm6x_.dbf
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/datafile/
o1_mf_temp_1yhzvhkk_.tmp
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_1_1yhztxdl_.log
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_2_1yhzv0p5_.log
deleted
auxiliary instance file /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog/
o1_mf_3_1yhzv4gp_.log
deleted

Recovery Manager complete.

[oracle@lutz1 oracle]$ ll /home/oracle/AUX/tts/
total 10344
-rw-r—– 1 oracle oinstall 10493952 Feb 7 03:01 lutz_test.dbf
-rw-r—– 1 oracle oinstall 65536 Feb 7 03:02 lutz_test.dmp
-rw-r–r– 1 oracle oinstall 2075 Feb 7 03:03 lutz_test_im.sql
-rw-r–r– 1 oracle oinstall 1130 Feb 7 03:02 lutz_test.log
drwxr-x— 4 oracle oinstall 4096 Feb 7 02:58 TSPITR_ORCL_YIGU

[oracle@lutz1 oracle]$ cat /home/oracle/AUX/tts/lutz_test_im.sql
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= ‘lutz_test.dmp’ transport_datafiles= /home/oracle/AUX/tts/lutz_test.dbf
*/
————————————————————–
– Start of sample PL/SQL script for importing the tablespaces
————————————————————–
– creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS
‘/home/oracle/AUX/tts/’;
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS
‘/home/oracle/AUX/tts’;
/* PL/SQL Script to import the exported tablespaces */
DECLARE
— the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
— the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
— names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
— dump file name and location
dump_file.file_name := ‘lutz_test.dmp’;
dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
— forming list of datafiles for import
tbs_files( 1).file_name := ‘lutz_test.dbf’;
tbs_files( 1).directory_object := ‘STREAMS$DIROBJ$1′;
— import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
— output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first
IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
END LOOP;
END IF;
END;
/
– dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
————————————————————–
– End of sample PL/SQL script
————————————————————–

[oracle@lutz1 oracle]$ cat /home/oracle/AUX/tts/lutz_test.log

Export: Release 10.2.0.1.0 – Production on Tuesday, 07 February, 2006 3:01:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Production
With the Partitioning, OLAP and Data Mining options
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″: userid=”/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)
(PROGRAM=/opt/oracle/product/db10_2/bin/oracle)(ARGV0=oracleyigu)
(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=yigu))
(CONNECT_DATA=(SID=yigu))) AS SYSDBA” transport_tablespaces= LUTZ_TEST dumpfile=lutz_test.dmp directory=STREAMS_DIROBJ_DPDIR logfile=lutz_test.log
Processing object type
TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type
TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table
“SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/AUX/tts/lutz_test.dmp
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″
successfully completed at 03:02:44

[oracle@lutz1 oracle]$ ls -rtl /home/oracle/AUX/tts/TSPITR_ORCL_YIGU/*
/home/oracle/AUX/tts/TSPITR_ORCL_YIGU/onlinelog:
total 0

connect lutz hartmann as sysdba

Resumable space allocation in ORACLE 10g

Since Oracle 9i we can cause a session which runs into a space allocation problem
to halt adn wait for the resolution of the space problem
instead of rolling back the staement
by using the resumable space allocation option.

In 9i this could be done only on session level.
Since Oracle 10gR1 the parameter RESUMABLE_TIMEOUT
can also be set on system level for all sessions;

Parameter type

Integer

Here is a little demo on session level:

SYS @ orcl AS SYSDBA SQL > CREATE USER lutz IDENTIFIED BY lutz
DEFAULT TABLESPACE lutz_klein
QUOTA 1m ON lutz_klein;
User created.

SYS @ orcl AS SYSDBA SQL > GRANT CREATE SESSION , CREATE TABLE TO lutz;
Grant succeeded.

LUTZ @ orcl SQL > BEGIN FOR i IN 1..10000 LOOP
2 INSERT INTO lutz_small VALUES(i);
3 END LOOP;
4 COMMIT;
5 END;
6 /
PL/SQL procedure successfully completed.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘LUTZ_KLEIN’

LUTZ @ orcl SQL > SELECT COUNT(*) FROM lutz_small;
COUNT(*)
———-
40000
As we can see, the server made a transaction level rollback.

LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
ERROR:
ORA-01031: insufficient privileges

SYS @ orcl AS SYSDBA SQL > GRANT RESUMABLE TO lutz;
Grant succeeded.
LUTZ @ orcl SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
Session altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;

alert_orcl.log ===>>
Thu Apr 6 10:12:48 2006pr 6 10:59:03 2006
statement in resumable session ‘User LUTZ(62), Session 142, Instance 1′ was suspended due to
ORA-01536: space quota exceeded for tablespace ‘LUTZ_KLEIN’

SYS @ orcl AS SYSDBA SQL > DESC dba_resumable
Name Null? Type
—————————————– ——– —————————-
USER_ID NUMBER
SESSION_ID NUMBER
INSTANCE_ID NUMBER
COORD_INSTANCE_ID NUMBER
COORD_SESSION_ID NUMBER
STATUS VARCHAR2(9)
TIMEOUT NUMBER
START_TIME VARCHAR2(20)
SUSPEND_TIME VARCHAR2(20)
RESUME_TIME VARCHAR2(20)
NAME VARCHAR2(4000)
SQL_TEXT VARCHAR2(1000)
ERROR_NUMBER NUMBER
ERROR_PARAMETER1 VARCHAR2(80)
ERROR_PARAMETER2 VARCHAR2(80)
ERROR_PARAMETER3 VARCHAR2(80)
ERROR_PARAMETER4 VARCHAR2(80)
ERROR_PARAMETER5 VARCHAR2(80)
ERROR_MSG VARCHAR2(4000)
SYS @ orcl AS SYSDBA SQL > SELECT name, SQL_TEXT, STATUS, TIMEOUT FROM dba_resumable;
NAME SQL_TEXT STATUS TIMEOUT
———- ————————————————- ——— ———-
User LUTZ( insert into lutz_small select * from lutz_small SUSPENDED 600
62), Session 142, Instance 1

SYS @ orcl AS SYSDBA SQL > ALTER USER lutz QUOTA UNLIMITED ON lutz_klein;
User altered.

LUTZ @ orcl SQL > INSERT INTO lutz_small SELECT * FROM lutz_small;
40000 rows created.

alert_orcl.log ===>>
Thu Apr 6 11:05:29 2006
statement in resumable session ‘User LUTZ(62), Session 142, Instance 1′ was resumed

connect lutz hartmann as sysdba

How to check for and repair block corruption with RMAN in Oracle 9i and Oracle 10g

Problem: the application encounters an ORA-01578 runtime error because there are one or more corrupt blocks in a table it is reading.

How can corrupt blocks be caused?
First of all we have two diffent kinds of block corruption:
- physical corruption (media corrupt)
- logical corruption (soft corrupt)
Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;
Logical corrution can amoung other reasons be caused by an attempt to recover through a NOLOGGING action.
There are two initialization parameters for dealing with block corruption:
- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)
causes 1-2% performance overhead
- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)
causes 1-10% performance overhead
If performance is not a big issue then you should use these!

Normally RMAN checks only for physically corrupt blocks
with every backup it takes and every image copy it makes.
This is a common misunderstanding amoung a lot of DBAs.
RMAN doesn not automatically detect logical corruption by default!
We have to tell it to do so by using CHECK LOGICAL!
The info about corruptions can be found in the following views:

SYS @ orcl AS SYSDBA SQL > select * from v$backup_corruption;
RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#
———- ———- ———- ———- ———- ———- ———-
BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
———- —————— — ———
1 586945441 586945402 3 1 5 81
4 0 YES CORRUPT
– SYS @ orcl AS SYSDBA SQL > select * from v$copy_corruption;

Here is a case study:

HR @ orcl SQL > select last_name, salary
2 from employees;

ERROR at line 2:
ORA-01578: ORACLE data block corrupted (file # 5, block # 83)
# this could be an ORA-26040 in Oracle 8i! and before
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/orcl/
example01.dbf’

This is what you find in the alert_.log:
Wed Apr 5 08:17:40 2006
Hex dump of (file 5, block 83) in trace file
/u01/app/oracle/admin/orcl/udump/orcl_ora_14669.trc
Corrupt block relative dba: 0×01400053 (file 5, block 83)
Bad header found during buffer read
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0×0000.0006d162 seq: 0×1 flg: 0×04
spare1: 0×52 spare2: 0×52 spare3: 0×0
consistency value in tail: 0xd1622301
check value in block header: 0x63be
computed block checksum: 0xe420
Reread of rdba: 0×01400053 (file 5, block 83)
found same corrupted data
Wed Apr 5 08:17:41 2006
Corrupt Block Found
TSN = 6, TSNAME = EXAMPLE
RFN = 5, BLK = 83, RDBA = 20971603
OBJN = 51857, OBJD = 51255, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =

Starting with Oracle 9i we can use RMAN
to check a database for both physically and logically corrupt blocks.
Here is the syntax:
RMAN> backup validate check logical database;
Starting backup at 05-04-2006:08:23:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full 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: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full 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: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full 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: backup set complete, elapsed time: 00:00:03
Finished backup at 05-04-2006:08:24:10

RMAN does not physically backup the database with this command
but it reads all blocks and checks for corruptions.
If it finds corrupted blocks it will place the information about the corruption into a view:

SYS @ orcl AS SYSDBA SQL > select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 81 4 0 CORRUPT

this is what we find in the alert_.log:
Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad header found during backing up datafile
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04
spare1: 0×52 spare2: 0×52 spare3: 0×0
consistency value in tail: 0xbc772003
check value in block header: 0xb32
computed block checksum: 0xe4c1
Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/
example01.dbf.
found same corrupt data

Now we can tell RMAN to recover all the blocks
which it has found as being corrupt:
RMAN> blockrecover corruption list;
# (all blocks from v$database_block_corruption)
Starting blockrecover at 05-04-2006:10:09:15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/
oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/
flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc
archive log thread 1 sequence 1 is already on disk as file
/u01/app/oracle/oradata/
orcl/redo01.log
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 05-04-2006:10:09:24

this is in the alert_.log:
Starting block media recovery
Wed Apr 5 10:09:22 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_%u_.arc
Wed Apr 5 10:09:23 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc ( restored)
Wed Apr 5 10:09:23 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Wed Apr 5 10:09:23 2006
Completed block media recovery

I recommend you to check your database for corrupt blocks
with RMAN on a regular basis, proactively.
If you do so you RMAN finds out about block corruptions
before your application runs into an ORA-01578 and
before you find out that you have backed up the corrupt blocks again and again.

There have been incidents when DBAs found out
that they did not have a backup with the un-corruted block any more,
because you have deleted the last one with a not corrupted version.
They could not recover the block any more!

For more detailed info about recovering corrupt blocks
(without and with RMAN, releases 7-10g)
pls see metalink also:
Subject: Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Doc ID: Note:28814.1 Type: BULLETIN
Last Revision Date: 26-MAR-2006 Status: PUBLISHED

connect lutz hartmann as sysdba

DROP DATABASE INCLUDING BACKUPS with RMAN in Oracle 10g Release 2

“In the old times” it could be a problem to get rid of all the Backup information in the RMAN CATALOG after you had deleted a database with dbca for instance.
You had to manually remove all the records from the catalog.

Starting with Oracle 10gR1 we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG.

Here is a demo how it works:
$ rman target / catalog rman_user/oracle@hugo
RMAN> startup mount
RMAN> drop database including backups;

database name is “ORCL” and DBID is 1114494726
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 04/05/2006 07:00:59
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

RMAN> sql ‘alter system enable restricted session’;
RMAN> drop database including backups;

database name is “ORCL” and DBID is 1114494726
Do you really want to drop all backups and the database (enter YES or NO)? yes
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
1402 1399 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp
1872 1858 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset
/2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp
1873 1859 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp
1874 1860 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp
1875 1865 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp recid=38 stamp=586867060
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp recid=48 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp recid=49 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
Deleted 5 objects
…
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
——- —- – ——————- ———- ——————- —-
1228 5 A 04-04-2006:10:34:35 950407 04-04-2006:10:32:56
/u01/app/oracle/oradata/orcl/example01.dbf

List of Archived Log Copies
Key Thrd Seq S Low Time Name
——- —- ——- – ——————- —-
1557 1 6 A 04-04-2006:11:02:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc
1558 1 7 A 04-04-2006:11:19:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc
1559 1 8 A 04-04-2006:11:23:51 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc
1560 1 1 A 04-04-2006:11:34:05 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_1_234hpx9w_.arc
1561 1 2 A 04-04-2006:11:39:40 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_2_234hqjto_.arc
…
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc recid=46 stamp=586869540
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc recid=47 stamp=586869831
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc recid=48 stamp=586870445
deleted archive log
…
datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf
recid=11 stamp=586866875
Deleted 1 objects
…
database name is “ORCL” and DBID is 1114494726
database dropped
database name is “ORCL” and DBID is 1114494726
database unregistered from the recovery catalog
RMAN> exit

Rman will not delete the entry in the /etc/oratab, so we have to do this either by editing /etc/oratab or by using dbca to delete the service

For further information pls also see Metalink Note: 251412.1

connect lutz hartmann as sysdba