Wednesday, August 18, 2010

Oracle Cursor_sharing parameter options

What is cursor_sharing parameters ?
CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

What is possible values for this parameter ?
1. EXACT (default)
Only allows statements with identical text to share the same cursor.

2. FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

3. SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

When we have to / should use this parameter ?
Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.

Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:

Are there statements in the shared pool that differ only in the values of literals?
Is the response time low due to a very high number of library cache misses?

Tom Kyte said "cursor_sharing=force" is not permanent solution for performance it is use for temporary basis during developer fix there bug in application... instead of this parameter use BIND VARIABLE.

Performance improvement when we set cursor_sharing=force ?
When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.

Side Effects on database when set cursor_sharing=FORCE/SIMILAR
Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.

Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".

BUG with cursor_sharing=FORCE/SIMILAR
In Oracle Version 8i there is bug when set cursor_sharing=force/similar.

We need to down our database to set this parameter
No, we can set this parameter when our database is open.

alter system set CURSOR_SHARING=force SCOPE=both;

Monday, August 16, 2010

Vi quick reference for DBAs

Here are a few useful commands for those who are new to vi.

esc :q!	Just quit - don't save
esc :e!	Revert to saved
esc :wq	Save and exit
esc shift zz	Save and exit
esc i	Enter insert mode (edit mode)
esc a	Enter append mode (edit mode)
esc	Exit edit mode
esc r	Replace a single character
esc x	Delete a single character
esc dd	Delete a single line
esc yy	Copy a single line
esc p	Paste a single line
.	Repeat the last command
esc /	String search
esc $	Jump to end of line
esc ^	Jump to begining of line
shift g	Jump to the end of the file
:1	Jump to the begining of the file
:.=	Display the current line number

Thursday, August 12, 2010

How to skip a tablespace for restore operation

Sometimes we want to restore full database except a certain tablespace. That is we need to skip a tablespace for restore operation. May be that the tablespace only contains tempoarary data and hence not useful for us. Or we are performing some database point in time recovery operation and we want to omit a big tablespace as the tablespace does not contain data needed for our requirement. In this way we can minimize our restore operation time.

With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.

Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;

Wednesday, August 11, 2010

Find ASM Home

[oracle@vmora01rh4 ~]$ srvctl config asm -n vmora01rh4

+ASM1 /u01/app/oracle/product/10.2.0/asm

[oracle@vmora01rh4 ~]$ srvctl config asm -n vmora02rh4

+ASM2 /u01/app/oracle/product/10.2.0/asm

$ cat /etc/oratab

+ASM1:/u01/app/oracle/product/10.2.0/db_1:N

ractest:/u01/app/oracle/product/10.2.0/db_1:N

Tuesday, August 10, 2010

CRS Version

"How can I verify what version CRS is?".

The version of CRS can be higher than the version of database/ASM software. The commands to see the same are

crsctl query crs softwareversion
crsctl query crs activeversion

So although the difference between the two commands might be obvious I am not sure if this is documented anywhere. Also after upgrading from 10.2.0.1 to 10.2.0.2/10.2.0.3 the active version does not seem to change. This maybe due to bug 5278777 which should be fixed in the 10.2.0.4 patchset.

I upgraded a couple of clusters yesterday from 10.2.0.1 to 10.2.0.3 (without any issues per se) and this is what I see after the CRS upgrade.

elephant-> crsctl query crs softwareversion
CRS software version on node [elephant] is [10.2.0.3.0]
elephant-> crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.1.0]

elephant-> cemutlo -w
2:1:

The cemutlo command is used by the EM agent to query CRS version.

Also a rolling upgrade of CRS causes the VIP to relocate to some other node and you have to manually relocate the same using crs_relocate. This is mentioned in the patchset readme though I am not sure how many customers actually read the section “Known Issues”.

Monday, August 09, 2010

Disk I/O, Events, Waits

Here are some scripts related to Disk I/O, Events, Waits .

Datafile I/O

DATAFILE I/O NOTES:

· File Name - Datafile name

· Physical Reads - Number of physical reads

· Reads % - Percentage of physical reads

· Physical Writes - Number of physical writes

· Writes % - Percentage of physical writes

· Total Block I/O's - Number of I/O blocks

· Use this report to identify any "hot spots" or I/O contention

select  NAME,

        PHYRDS "Physical Reads",

        round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",

        PHYWRTS "Physical Writes",

        round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",

        fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"

from (

        select  sum(PHYRDS) PHYS_READS,

               sum(PHYWRTS) PHYS_WRTS

        from    v$filestat

        ) pd,

        v$datafile df,

        v$filestat fs

where   df.FILE# = fs.FILE#

order   by fs.PHYBLKRD+fs.PHYBLKWRT desc

SGA Stats

SGA STAT NOTES:

· Statistic Name - Name of the statistic

· Bytes - Size

select  NAME,

        BYTES

from    v$sgastat

order   by NAME

Sort Stats

SORT NOTES:

· Sort Parameter - Name of the sort parameter

· Value - Number of sorts

· sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.

· sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.

· sorts (rows) - Number of sorted rows

· The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters.

select  NAME,

        VALUE

from    v$sysstat

where   NAME like 'sort%'

All Events

SYSTEM EVENT (ALL) NOTES:

· Event Name - Name of the event

· Total Waits - Total number of waits for the event

· Total Timeouts - Total number of timeouts for the event

· Time Waited - The total amount of time waited for this event, in hundredths of a second

· Average Wait - The average amount of time waited for this event, in hundredths of a second

select  EVENT,

        TOTAL_WAITS,

        TOTAL_TIMEOUTS,

        TIME_WAITED,

        round(AVERAGE_WAIT,2) "Average Wait"

from    v$system_event

order   by TOTAL_WAITS

All Statistics

SYSTEM STATISTICS (ALL) NOTES:

· Stat# - Number of the statistic

· Name - Name of the statistic

· Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)

· Value - Value of the statistic

select  STATISTIC#,

        NAME,

        CLASS,

        VALUE

from    v$sysstat

Wait Stats

WAIT STATISTIC NOTES:

· Class - Class of block subject to contention

· Count - Number of waits by this OPERATION for this CLASS of block

· Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block

· Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.

· Free List - May occur if multiple data loading programs run simultaneously.

· Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;

· Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.

· Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.

· Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.

select  CLASS,

        COUNT,

        TIME

from    v$waitstat

order   by CLASS

Friday, August 06, 2010

Querying the Alert Log using External Tables

Run this PL/SQL block as SYS. This will create the Directory, grant the required privileges on the Directory and then create the External Table in the SYSTEM schema.

DECLARE
  BDumploc  VARCHAR2(200);
  ORASID       VARCHAR2(16);
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
   SELECT value
  INTO BDumploc
  FROM v$parameter
  WHERE name='background_dump_dest';
  -- create the directory for the bdump dir
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
    BDumploc||'''';
  -- grant the necessary privileges
  EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
  -- get the SID
  SELECT instance_name INTO ORASID FROM v$instance;
  -- create the external table
  EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXT
    (TEXT VARCHAR2(255)
    ) ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY BDUMP_DIR
     ACCESS PARAMETERS
     (records delimited by newline
      nobadfile
      nologfile
     )
     LOCATION (''alert_'||ORASID||'.log'')
    )
    REJECT LIMIT UNLIMITED'
  ;
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/

Create the function which will handle dates in the alert log

CREATE OR REPLACE FUNCTION system.get_alert_log_date( text IN VARCHAR2 )
  RETURN DATE
IS
  InvalidDate  EXCEPTION;
  PRAGMA EXCEPTION_INIT(InvalidDate, -1846);
BEGIN
  RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY'
    ,'NLS_DATE_LANGUAGE=AMERICAN');
EXCEPTION
  WHEN InvalidDate THEN RETURN NULL;
END;
/

Create the View to query the External Table

CREATE OR REPLACE VIEW system.read_alert_log as
SELECT
      LAST_VALUE(low_row_num IGNORE NULLS)
         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW) start_row
      ,LAST_VALUE(alert_date  IGNORE NULLS)
         OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW) alert_date
      ,alert_text
FROM (SELECT ROWNUM row_num
            ,NVL2(system.get_alert_log_date(text),ROWNUM,NULL) low_row_num
            ,system.get_alert_log_date(text) alert_date
            ,text alert_text
      FROM system.alert_log_ext
     )
;

Query the External Table – this will list all alert log entries for the past day which have the string ‘ORA-’

SELECT alert_text
FROM system.read_alert_log
WHERE start_row IN (SELECT start_row
FROM system.read_alert_log
WHERE REGEXP_LIKE(alert_text,'ORA-')
)
AND alert_date > TRUNC(SYSDATE)-1
/

How to monitor the progress of an UNDO operation

We can monitor the progress of an undo operation by running the query shown

below:

select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
, from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr;

From the first session connected as SH we issue a DELETE statement

SQL> conn sh/sh
Connected.

SQL> delete sales;

While the delete operation is in progress, we can monitor the usage of undo blocks from
another session

As user SYS we issue the SQL statement shown above and we see that the USED_UBLK
column value keeps increasing as the delete statement progresses and more undo blocks
are generated.

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       11070

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       11212

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       15996

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       20246

After the delete operation is completed, we now run a ROLLBACK command to undo the
delete operation.

While the rollback is in operation, running the same query shows that the USED_UBLK
column now instead keeps decreasing in value until the query returns 'no rows selected'.

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3389

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3376

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        2409

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        1344

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         775

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         399

SQL> /

no rows selected

At this point we can confirm that the user SH would have completed the rollback operation.

SQL> rollback;

Rollback complete.

How to change Database name

DBNEWID Utility

Prior to the introduction of the DBNEWID utility alteration of the internal DBID of an instance was impossible and alteration of the DBNAME required the creation of a new controlfile. The DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler. Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. Alteration of the BID in the cloned instance removes this restriction.

DBID And DBNAME

Backup the database.
Mount the database after a clean shutdown:

·         SHUTDOWN IMMEDIATE

STARTUP MOUNT

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

nid TARGET=sys/password@TSH1 DBNAME=TSH2

Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:

C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2

DBNEWID: Release 9.2.0.3.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TSH1 (DBID=1024166118)

Control Files in database:

    C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL

    C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL

    C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL

Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1024166118 to 1317278975

Changing database name from TSH1 to TSH2

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified

    Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\DRSYS01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\EXAMPLE01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\INDX01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\ODM01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\USERS01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\XDB01.DBF - dbid changed, wrote new name

    Datafile C:\ORACLE\ORADATA\TSH1\TEMP01.DBF - dbid changed, wrote new name

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name

    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to TSH2.

Modify parameter file and generate a new password file before restarting.

Database ID for database TSH2 changed to 1317278975.

All previous backups and archived redo logs for this database are unusable.

Shut down database and open with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

Shutdown the database:

SHUTDOWN IMMEDIATE

Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

STARTUP MOUNT

ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;

SHUTDOWN IMMEDIATE

Create a new password file:

orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

Rename the SPFILE to match the new DBNAME.
If you are using Windows you must recreate the service so the correct name and parameter file are used:

·         oradim -delete -sid TSH1

oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora

If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:

ORACLE_SID=TSH2; export ORACLE_SID

Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

lsnrctl reload

Open the database with RESETLOGS:

·         STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

Backup the database.

Delete duplicate rows from a table

Following query would identify the duplicate rows on a table using self join using rowid among duplicates. For faster execution, this is suggested to use unique/primary key values in select and in joinings. If there is no PK/Unique key then its safe to specify all the columns associated with table in joining.

First way:

1. Identify Duplicates

select EMPNO,DEPT_ID, GRADE

from SCOTT.EMP a

where

rowid >

(select min(rowid) from SCOTT.EMP b

where

b.EMPNO = a.EMPNO

and

b.DEPT_ID = a.DEPT_ID

and b.GRADE = a.GRADE

);

2. Delete Duplicates

See the output, do some manual verification and go for deletion with following query.

delete from

SCOTT.EMP a

and

a.rowid >

any (select b.rowid

from

SCOTT.EMP b

where

b.EMPNO = a.EMPNO

and

b.DEPT_ID = a.DEPT_ID

and b.GRADE = a.GRADE

);

Second way:

1. Identify duplicates.

select count(*) from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
2528

2. 2258 duplicates exist – these can be deleted with command below -

delete from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

Thursday, August 05, 2010

Restore RMAN from Lower to Upper Version

Is it possible to restore Oracle 9.2 RMAN backup with 10.2.0.4 version?

Actually, this is possible because RMAN can read from backupset files written by older versions. The same is true for patchset differences, 10.2.0.2 backupset can be used for a restore with RMAN 10.2.0.4. We can also restore 32-bit database backup set with 64-bit version. Of course, keep in mind that simply restoring older database with newer version doesn't give you "instant" upgraded database -

All upgrade steps must be performed after a restore (yes, you'll have to read that manual, after all)

Refer to Metalink Note:369644.1 "Answers To FAQ For Restoring Or Duplicating Between Different Versions And Platforms".

I tested 9.2->10.2 scenario by restoring 9.2.0.8 backupset with 10.2.0.4 RMAN.

-- Connecting to Oracle 9.2.0.8 (noarchivelog) database

Recovery Manager: Release 9.2.0.8.0 - Production

connected to target database: ORA9 (DBID=422058228)

using target database controlfile instead of recovery catalog

D:\Oracle>rman target / nocatalog

-- -------------------------------------

-- Clean shutdown

-- -------------------------------------

RMAN> shutdown

database closed

database dismounted

Oracle instance shut down

-- ------------------------------------

Mount the database.for the sake of simplicity I left 9i db in noarchivelog mode

-- ------------------------------------

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 705765992 bytes

Fixed Size 455272 bytes

Variable Size 301989888 bytes

Database Buffers 402653184 bytes

Redo Buffers 667648 bytes

-- --------------------------------------

-- Backup 9i database with 9i RMAN

-- --------------------------------------

RMAN> run {

2> allocate channel d1 type disk;

3> backup format 'D:\ORADATA\ORA9\BACKUP\%d_%t_%s_%p.bak' database;

4> backup current controlfile format 'D:\ORADATA\ORA9\BACKUP\ctrl_%d_%u_%s.bak';

5> release channel d1;

6> }

allocated channel: d1

channel d1: sid=11 devtype=DISK

Starting backup at 05.06.08

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00001 name=D:\ORADATA\ORA9\SYSTEM01.DBF

input datafile fno=00002 name=D:\ORADATA\ORA9\UNDOTBS01.DBF

input datafile fno=00007 name=D:\ORADATA\ORA9\XDB01.DBF

input datafile fno=00006 name=D:\ORADATA\ORA9\USERS01.DBF

input datafile fno=00004 name=D:\ORADATA\ORA9\INDX01.DBF

input datafile fno=00003 name=D:\ORADATA\ORA9\DRSYS01.DBF

input datafile fno=00005 name=D:\ORADATA\ORA9\TOOLS01.DBF

channel d1: starting piece 1 at 05.06.08

channel d1: finished piece 1 at 05.06.08

piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK comment=NONE

channel d1: backup set complete, elapsed time: 00:01:15

Finished backup at 05.06.08

Starting backup at 05.06.08

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

including current controlfile in backupset

channel d1: starting piece 1 at 05.06.08

channel d1: finished piece 1 at 05.06.08

piece handle=D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 05.06.08

Starting Control File and SPFILE Autobackup at 05.06.08

piece handle=D:\ORACLE\ORA9\DATABASE\C-422058228-20080605-02 comment=NONE

Finished Control File and SPFILE Autobackup at 05.06.08

released channel: d1

-- ---------------------

-- Shutdown 9i instance

-- ---------------------

RMAN> shutdown

database dismounted

Oracle instance shut down

-- -----------------------------------------

-- Prepare 10g instance with which you'll

-- restore 9i backup (this step is out of

-- the scope of this note!)

-- -----------------------------------------

Prior to excecuting restore of 9i RMAN backup with 10g RMAN I took backup of 9i files and deleted them from original location. As shown below, there are no datafile in directory D:\ORADATA\ORA9 directory.

D:\ORADATA\ORA9>dir

Volume in drive D is SW

Volume Serial Number is 408C-7E40

Directory of D:\ORADATA\ORA9

05.06.2008 23:24

05.06.2008 23:08

BACKUP

05.06.2008 23:23

BACKUP9_ORIGINAL

0 File(s) 0 bytes

And the RMAN backup directory contains:

D:\ORADATA\ORA9\BACKUP>dir

Volume in drive D is SW

Volume Serial Number is 408C-7E40

Directory of D:\ORADATA\ORA9\BACKUP

05.06.2008 23:08

05.06.2008 23:08 1.638.400 CTRL_ORA9_08JI6V06_8.BAK

05.06.2008 23:08 517.414.912 ORA9_656636859_7_1.BAK

2 File(s) 519.053.312 bytes

2 Dir(s) 4.094.455.808 bytes free

-- -----------------------------------------

Restore 9i backupset with 10g (Note: I used 9i pfile to start 10g instance that's what caused some warnings about obsolete parameters.)

-- -----------------------------------------

D:\Oracle>rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Cet Jun 5 23:29:08 2008

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 683671552 bytes

Fixed Size 1298592 bytes

Variable Size 276828000 bytes

Database Buffers 402653184 bytes

Redo Buffers 2891776 bytes

-- -----------------------

-- Restore control file...

-- -----------------------

RMAN> run {

2> allocate channel d1 type disk;

3> restore controlfile from 'D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK';

4> release channel d1;

5> }

allocated channel: d1

channel d1: sid=157 devtype=DISK

Starting restore at 05.06.08

channel d1: restoring control file

channel d1: restore complete, elapsed time: 00:00:03

output filename=D:\ORADATA\ORA9\CONTROL01.CTL

output filename=D:\ORADATA\ORA9\CONTROL02.CTL

output filename=D:\ORADATA\ORA9\CONTROL03.CTL

Finished restore at 05.06.08

released channel: d1

-- ------------------------

-- Mount database...

-- ------------------------

RMAN> alter database mount;

database mounted

-- ------------------------

-- Restore database...

-- ------------------------

RMAN> run {

2> allocate channel d1 type disk;

3> restore database;

4> }

allocated channel: d1

channel d1: sid=157 devtype=DISK

Starting restore at 05.06.08

channel d1: starting datafile backupset restore

channel d1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\ORADATA\ORA9\SYSTEM01.DBF

restoring datafile 00002 to D:\ORADATA\ORA9\UNDOTBS01.DBF

restoring datafile 00003 to D:\ORADATA\ORA9\DRSYS01.DBF

restoring datafile 00004 to D:\ORADATA\ORA9\INDX01.DBF

restoring datafile 00005 to D:\ORADATA\ORA9\TOOLS01.DBF

restoring datafile 00006 to D:\ORADATA\ORA9\USERS01.DBF

restoring datafile 00007 to D:\ORADATA\ORA9\XDB01.DBF

channel d1: reading from backup piece D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK

channel d1: restored backup piece 1

piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK tag=TAG20080605T230738

channel d1: restore complete, elapsed time: 00:01:25

Finished restore at 05.06.08

released channel: d1

-- ----------------------------

-- Open database with resetlogs

-- ----------------------------

RMAN> alter database open resetlogs;