Backup stanby and Restoration steps tested in Production Server

The below trace information is creating a standby in production environment with 220GB (9.2.0.7) database by without connecting to Production.

Metalink Reference : Step By Step Guide To Create Physical Standby Database Using RMAN [ID 469493.1]

The Below script is used for Backup

Run {
Allocate channel ch1 type disk format '\\t34270\e$\Full_backup_standby\%d_data_%U' maxpiecesize 30G;
Allocate channel ch2 type disk format '\\t34270\e$\Full_backup_standby\%d_data_%U' maxpiecesize 30G;
Allocate channel ch3 type disk format '\\t34270\e$\Full_backup_standby\%d_data_%U' maxpiecesize 30G;
Allocate channel ch4 type disk format '\\t34270\e$\Full_backup_standby\%d_data_%U' maxpiecesize 30G;
Allocate channel ch5 type disk format '\\t34270\e$\Full_backup_standby\%d_data_%U' maxpiecesize 30G;
Backup database plus archivelog;
backup format '\\t34270\e$\Full_backup_standby\%d_ctrl_%U' current controlfile for standby;
Release channel ch1;
Release channel ch2;
Release channel ch3;
Release channel ch4;
Release channel ch5;
}

Restoration steps performed in the standby server :

The standby server has the Oracle Service already created using Oradim utility, password file,spfile has already been copied with the necessary changes done in it. The below information is about restoration and recovery

C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 9.2.0.7.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 1609808860 bytes
Fixed Size 457692 bytes
Variable Size 1283457024 bytes
Database Buffers 318767104 bytes
Redo Buffers 7127040 bytes
RMAN> set dbid=2399844302
executing command: SET DBID
RMAN> restore controlfile from '\\T34270\e$\Full_backup_standby\sys8_CTRL_JVM18BD8_1_1';
Starting restore at 04-JAN-11
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 Dvtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\sys8\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\sys8\CONTROL02.CTL
output filename=D:\ORADATA\sys8\CONTROL03.CTL
Finished restore at 04-JAN-11
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
RMAN> restore database;
Starting restore at 04-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORADATA\sys8\INDX01.DBF
restoring datafile 00006 to D:\ORADATA\sys8\USERS01.DBF
restoring datafile 00007 to D:\ORADATA\sys8\XDB01.DBF
restoring datafile 00010 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_3.DBF
restoring datafile 00018 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_03.DBF
restoring datafile 00023 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_05.DBF
restoring datafile 00024 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_7.DBF
restoring datafile 00033 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_8.DBF
restoring datafile 00034 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_12.DBF
restoring datafile 00043 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_16.DBF
restoring datafile 00047 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_20.DBF
restoring datafile 00067 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_4.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JQM185RA_1_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JQM185RA_2_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\sys8\SYSTEM01.DBF
restoring datafile 00008 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_1.DBF
restoring datafile 00013 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_02.DBF
restoring datafile 00017 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_5.DBF
restoring datafile 00020 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_17.DBF
restoring datafile 00029 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_6.DBF
restoring datafile 00030 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_10.DBF
restoring datafile 00037 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_21.DBF
restoring datafile 00040 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_15.DBF
restoring datafile 00065 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_2.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JPM185RA_1_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JPM185RA_2_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORADATA\sys8\BSH_A\TS_BSH_A_INDX_1.DBF
restoring datafile 00011 to D:\ORADATA\sys8\BSH_A\TS_BSH_A_INDX_2.DBF
restoring datafile 00012 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_01.DBF
restoring datafile 00016 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_4.DBF
restoring datafile 00019 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_04.DBF
restoring datafile 00027 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_9.DBF
restoring datafile 00028 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_09.DBF
restoring datafile 00038 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_14.DBF
restoring datafile 00042 to D:\ORADATA\sys8\STATS_PACK.ORA
restoring datafile 00064 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_1.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JTM185RR_1_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JTM185RR_2_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORADATA\sys8\DRSYS01.DBF
restoring datafile 00014 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_5.DBF
restoring datafile 00025 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_8.DBF
restoring datafile 00026 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_08.DBF
restoring datafile 00035 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_9.DBF
restoring datafile 00036 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_13.DBF
restoring datafile 00045 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_19.DBF
restoring datafile 00046 to D:\ORADATA\sys8\UNDOTBS_01.DBF
restoring datafile 00068 to D:\ORADATA\sys8\BSH_A\TS_BSH_A_DATA_1.DBF
restoring datafile 00070 to D:\ORADATA\sys8\BSH_A\TS_BSH_A_DATA_3.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JSM185RG_1_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JSM185RG_2_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:\ORADATA\sys8\TOOLS01.DBF
restoring datafile 00009 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_2.DBF
restoring datafile 00015 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_06.DBF
restoring datafile 00021 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_07.DBF
restoring datafile 00022 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_6.DBF
restoring datafile 00031 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_7.DBF
restoring datafile 00032 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_11.DBF
restoring datafile 00039 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_10.DBF
restoring datafile 00041 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_INDX_18.DBF
restoring datafile 00044 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA2_10.DBF
restoring datafile 00066 to D:\ORADATA\sys8\BSH_D\TS_BSH_D_DATA1_3.DBF
restoring datafile 00069 to D:\ORADATA\sys8\BSH_A\TS_BSH_A_DATA_2.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JRM185RA_1_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JRM185RA_2_1 tag=TAG20110104T043657 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 04-JAN-11
RMAN> list backup of archivelog all;


RMAN> recover database until sequence 25224 thread 1;
Starting recover at 04-JAN-11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to Dfault Dstination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=25223
channel ORA_DISK_1: restored backup piece 1
piece handle=\\T34270\E$\FULL_BACKUP_STANDBY\sys8_DATA_JUM18BD6_1_1 tag=TAG20110104T061150 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=D:\ORADATA\sys8\ARCHIVE\LOG_1_25223.ARC thread=1 sequence=25223
media recovery complete
Finished recover at 04-JAN-11
RMAN>

after sucessfull restoration and recovery bring the database to recovery mode

sql> recover managed standby database disconnect from session;

************************** end ******************************

Techniques for Accessing more than 4GB of memory in 32 Bit windows 2003 Server.

Windows 2000 or Windows Server 2003 (32 bit) can suuprot upto 4 GB of memory. A user process running under these OS can access up to 2 GB of memory address space (assuming the /3GB switch was not used) with some of the memory being physical memory and some being virtual memory. The more programs (and, therefore, more processes) that run, the more memory you can go is upto 2 GB of user address space. When this situation occurs, the paging process increases dramatically and performance may be negatively impacted. So we need some technique to overcome the 4GB limit.

How to Access more than 4 GB of memory.

The Windows 2000 and Windows Server 2003 memory managers use PAE to provide more physical memory to a program. This reduces the need of swapping the memory of the page file and results in increased performance. The program itself is not aware of the actual memory size. All the memory management and allocation of the PAE memory is handled by the memory manager independently of the programs that run.

PAE : (Physical Address Extension)

It is a function of the Windows 2000 and Windows Server 2003 memory managers that provides more physical memory to a program that requests memory. The program is not aware that any of the memory that it uses resides in the range greater than 4 GB, just as a program is not aware that the memory it has requested is actually in the page file.

Example : (How to use /PAE switch in Boot.ini)

[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

AWE : Address Windowing Extensions

It is an API set that enables programs to reserve large chunks of memory. The reserved memory is non-pageable and is only accessible to that program.

What is /3GB switch ?

A 32 bit OS can access maximum 4 GB of the physical memory. Out of which 2 Gb is reserved for OS kernel programs and rest of the 2 GB is used for user applications. If the user application demands more than 2 GB of memory then the /3GB switch can be set in boot.ini to allow the user application to access 3 GB of memory. So the available memory for OS is now reduced to 1 GB.

Example : (How to use /3GB switch in Boot.ini)
[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3GB

The preceding information is valid for programs that run when the /3GB switch is used. A program that requests 3 GB of memory is more likely to be able to have more of its memory remain in physical memory rather than be paged out. This increases the performance of programs that are capable of using the /3GB switch. The exception is when the /3GB switch is used in conjunction with the /PAE switch. In this case, the operating system does not use any memory in excess of 16 GB. This behavior is caused by kernel virtual memory space considerations. Thus, if the system restarts with the /3GB entry in the Boot.ini file, and the system has more than 16 GB of physical memory, the additional physical random access memory (RAM) is not used by the operating system. Restarting the computer without the /3GB switch enables the use of all the physical memory.AWE is a set of application programming interfaces (APIs) to the memory manager functions that enables programs to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE enables programs to reserve physical memory as non-paged memory and then to dynamically map portions of the non-paged memory to the program's working set of memory. This process enables memory-intensive programs, such as large database systems, to reserve large amounts of physical memory for data without having to be paged in and out of a paging file for usage. Instead, the data is swapped in and out of the working set and reserved memory is in excess of the 4 GB range. Additionally, the range of memory in excess of 4 GB is exposed to the memory manager and the AWE functions by PAE. Without PAE, AWE cannot reserve memory in excess of 4 GB.

Note In Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot-add memory devices. In all other cases, you must use the /PAE switch in the Boot.ini file to take advantage of memory over 4GB.

The following operating systems can use PAE to take advantage of physical memory beyond 4GB
Microsoft Windows 2000 Advanced Server
Microsoft Windows 2000 Datacenter Server
Microsoft Windows Server 2003, Enterprise Edition
Microsoft Windows Server 2003, Datacenter Edition

Limitations and Requirments of Flashback

**************FLASHBACK DATABASE *************
Requirments:
-------------
>> You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
>> For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
New:
-----
>> Now in 10gR2 flashback database can be done beyond the Open resetlogs.

Limitations of Flashback database:
------------------------------------
>> Flashback database cannot be used for media failure or accidential deletion of datafiles or shrink datafiles.
>> Cannot go beyond the restoration of the controlfile, because all the flashback log information is discarded.
>> Flashback database can cause problem for datafiles if there is any database objects which is in nologging mode.
>> Be careful when creating a tablespace and reverting back the database.The created tablespace may get dropped.

********************* Flashback Table ******************
>> You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
>> Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
>> The database disables triggers on the affected table before performing a FLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled).
>> Constraints of the table will be checked. If the Operation violates any constraints, that it will be aborted.
>> Flashback database doesnt cause the Statistics of the table to be flashed back.


****************** Flashback Versions Query ****************

The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter.

Determine current SCN of The database using Time and Vice versa

A) Determine current SCN of The database.
-----------------------------------------------------------------------
You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.

1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
1053658

2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1053668


B) Determine current timestamp value.
-------------------------------------------------------------------
SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-MAY-08 11.00.08.374107 PM -04:00


C) Convert SCN to Timestamp.
-----------------------------------------------------
SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;

SCN_TO_TIMESTAMP(1053639)
---------------------------------------------------------------------------
14-MAY-08 10.52.15.000000000 PM


D) Convert Timestamp to SCN
-------------------------------------------------------------
SQL> SELECT TIMESTAMP_TO_SCN('14-MAY-08 11.00.08.374107 PM') FROM DUAL;

TIMESTAMP_TO_SCN('14-MAY-0811.00.08.374107PM')
----------------------------------------------
1054516

Some usefull information about Falshback and Undo

Flashback beyond resetlogs has been allowed in Oracle 10g R2

*>> Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints, not requiring the DBA to find and restore application specific properties

*>> When choosing whether to use a timestamp or an SCN in Flashback Query, remember that Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. If a possible 3-second error (maximum) is important to a Flashback Query in your application, then use an SCN instead of a timestamp. Refer to "Flashback Tips – General".

*>> In flashback tech we cant rollback DDL statments like alter table,add constraint etc..
except flashback database

*>> An System Change Number or SCN is an integer value associated with each change to the database. You might think of revision numbers in a source control system. Each time you do something, whether your adding or removing data, a unique number is associated with the change. Reverting to an earlier state is as easy as telling Flashback which SCN you want to revert to.

****************** Undo retention *****************

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#ADMIN013

Undo Retention
------------------
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.
Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention:

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low.

For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.
You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

Automatic Tuning of Undo Retention
>> Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.
>> If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.
>> If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.

Note:

Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace. For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten.

You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.) TUNED_UNDORETENTION is given in seconds.


************************************************

v$flashback_database_log:
-------------------------------

OLDEST_FLASHBACK_SCN --> Lowest system change number (SCN) in the flashback data
OLDEST_FLASHBACK_TIME --> Time of the lowest SCN in the flashback data
RETENTION_TARGET --> Target retention time (in minutes)
FLASHBACK_SIZE --> Current size (in bytes) of the flashback data
ESTIMATED_FLASHBACK_SIZE --> Estimated size of flashback data needed for the current target retention

v$flashback_database_stat:
-----------------------------

BEGIN_TIME --> Beginning of the time interval
END_TIME --> End of the time interval
FLASHBACK_DATA --> Number of bytes of flashback data written during the interval
DB_DATA --> Number of bytes of database data read and written during the interval
REDO_DATA --> Number of bytes of redo data written during the interval
ESTIMATED_FLASHBACK_SIZE --> Value of ESTIMATED_FLASHBACK_SIZE in V$FLASHBACK_DATABASE_LOG at the end of the time interval

Flashback Query tested in Oracle 10g

******************** Flashback Query *****************

11:01:21 SQL> select * from a;

NO
----------
0
1
2
33
3423
44444
123123

11:01:28 SQL> select * from a as of timestamp to_timestamp('16/07/2009:10:59:36','dd/mm/yyyy:hh:mi:ss');

NO
----------
0
33
3423
44444


D)Convert Timestamp to SCN
-------------------------------------------------------------

11:22:39 SQL> SELECT TIMESTAMP_TO_SCN('16-JUL-09:10:59:36') from dual;

TIMESTAMP_TO_SCN('16-JUL-09:10:59:36')
--------------------------------------
676308

11:23:30 SQL> flashback table a to scn 676308;
Flashback complete.

11:24:00 SQL> select * from a;

NO
----------
0
33
3423
44444
123123

Flashback database tested in Oracle 10g

************** Flashback database *********************

Scenario 1:

14:45:16 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

14:45:38 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.

14:50:40 SQL> alter database flashback on;
Database altered.

14:50:48 SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
14:51:02 SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

14:51:14 SQL> select * from v$flashback_database_logfile;

NAME
-------------------------------------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\FLASHBACK\O1_MF_55XW7J6N_.FLB
SQL> set time on

13:34:09 SQL> create user xx identified by xx;
User created.
13:35:16 SQL> grant connect,resource to xx;
Grant succeeded.

13:35:23 SQL> select username from dba_users;
USERNAME
------------------------------
DBSNMP
SYSMAN
NV
XX
OUTLN
MDSYS
IX
.
.
HR
29 rows selected.

13:35:35 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

13:36:26 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 113246936 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
Database mounted.

13:36:50 SQL> flashback database to timestamp to_timestamp('17/07/09:11:36:09','dd/mm/yy:hh:mi:ss');
Flashback complete.

13:38:10 SQL> alter database open resetlogs;
Database altered.

13:38:46 SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
NV
OUTLN
MDSYS
ORDSYS
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
TSMSYS
BI
PM
MDDATA
IX
SH
DIP
OE
HR
28 rows selected.

Scenario 2:

******************************* Adding tablespace and creating tables ******************

16:15:26 SQL> alter table c add (name varchar(10));
Table altered.

16:15:47 SQL> desc c
Name
-------------------------------------------------------------------------------------------------------------NO
NAME

16:15:52 SQL> select * from c;
NO NAME
---------- ----------
12
23
44
45

16:15:58 SQL> commit;
Commit complete.

16:16:02 SQL> select * from c as of timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
NO NAME
---------- ----------
12
23
44

16:17:41 SQL> alter table c enable row movement;
Table altered.

16:17:53 SQL> flashback table c to timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

16:17:56 SQL> select * from c;
NO NAME
---------- ----------
12
23
44

16:21:43 SQL> create tablespace flashback datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\flashback.dbf' size 2M;
Tablespace created.

16:22:43 SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 FLASHBACK YES NO YES
7 rows selected.

16:22:48 SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------------------------------------D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FLASHBACK.DBF
6 rows selected.

16:24:49 SQL> conn nv/database
Connected.

16:24:57 SQL> create table test_flash(no number);
Table created.

16:25:05 SQL> conn / as sysdba
Connected.

16:25:09 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

16:25:45 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 104858328 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.

16:25:54 SQL> flashback database to timestamp to_timestamp('20/07/2009:04:00:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

16:27:17 SQL> alter database open resetlogs;
Database altered.

16:27:39 SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
6 rows selected.

16:27:45 SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

16:28:00 SQL> conn nv/database
Connected.

16:28:31 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
T TABLE
B TABLE
SYS_TEMP_FBT TABLE
C TABLE

16:28:34 SQL> desc c
Name
-------------------------------------------------------------------------------------------------------------
NO

16:28:37 SQL> desc a
Name
-------------------------------------------------------------------------------------------------------------NO

16:28:41 SQL> select * from c;
NO
----------
12
23
44

Scenario 3:
***************************** Droping tablespace *********************

15:54:52 SQL> create tablespace n datafile 'D:\oracle\product\10.2.0\oradata\orcl\n.dbf' size 5M;
Tablespace created.

15:55:18 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
899730

15:57:14 SQL>
15:57:16 SQL> drop tablespace n including contents and datafiles;
Tablespace dropped.

15:57:34 SQL> conn / as sysdba
Connected.

15:58:36 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

15:59:20 SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1248552 bytes
Variable Size 79692504 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.

15:59:26 SQL> flashback database to timestamp to_timestamp('22/07/2009:15:55:39','dd/mm/yyyy:hh24:mi:ss');
flashback database to timestamp to_timestamp('22/07/2009:15:55:39','dd/mm/yyyy:hh24:mi:ss')
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00006'


16:05:41 SQL> flashback database to timestamp to_timestamp('22/07/2009:15:57:36','dd/mm/yyyy:hh24:mi:ss');
Flashback complete.

16:06:04 SQL> alter database open resetlogs;
Database altered.

16:06:31 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 899831 22-JUL-09
3 1 0 52428800 1 YES UNUSED 0

16:06:50 SQL> show user
USER is "SYS"

16:28:03 SQL> conn nv/database
Connected.

16:28:08 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
T TABLE
B TABLE
SYS_TEMP_FBT TABLE
C TABLE

16:28:12 SQL> select * from c;
NO
----------
1
23
44
2
23333333

******************************* end ***********************

Flashback table tested with Reference tables(ie constraints)

***************** Flashback table ****************

Scenario 1:
A).

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
SYS_TEMP_FBT TABLE

SQL> set time on
10:16:59 SQL>

10:16:59 SQL> select * from t;
NUM
----------
112
3
5

10:29:20 SQL> insert into t values(333);
1 row created.
10:29:37 SQL> insert into t values(4444);
1 row created.
10:29:45 SQL> commit;
Commit complete.

10:29:49 SQL> select * from t;
NUM
----------
112
3
5
333
4444

10:29:53 SQL> select * from t as of timestamp to_timestamp('16/07/2009:10:28:53','dd/mm/yyyy:hh:mi:ss');
NUM
----------
112
3
5

10:31:06 SQL> flashback table t to timestamp to_timestamp('16/07/2009:10:28:53','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

10:31:33 SQL> select * from t;
NUM
----------
112
3
5

Scenario 2:


B).
*********** with constraint reference **************

create table b (no number,ano number constraint fk_ano references a(no));
alter table a add constraint pk_no primary key (no);

10:59:36 SQL> select * from user_constraints;
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
------------------------------ ------------------------------ - ------------------------------ ---------------NV FK_ANO R B
NV PK_NO P A

SQL> select * from a;
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

SQL> select * from b;
no rows selected

SQL> desc b
Name
-------------------------------------------------------------------------------------------------------------NO
ANO

SQL> insert into a values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into b values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> set time on
14:52:16 SQL>

14:52:17 SQL> select * from a as of timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

14:53:01 SQL> select * from a;
NO
----------
0
1
33
3423
44444
123123
4.2322E+12
7 rows selected.

14:53:07 SQL> flashback table a to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
flashback table a to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (NV.FK_ANO) violated - child record found

14:53:48 SQL> alter table b enable row movement;
Table altered.

14:54:05 SQL> flashback table a,b to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

14:54:09 SQL> select * from a;
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

14:54:14 SQL> select * from b;
no rows selected

Scenario 3:
C).
******************************** DDL **************************

14:58:08 SQL> desc c
Name
---------------------------------------------------------------------------------------------------------
NO

14:58:10 SQL>
14:58:12 SQL>
14:58:21 SQL> alter table c add name varchar2(10);
Table altered.

14:58:59 SQL> insert into c values(4,'asdasdasda');
1 row created.
14:59:08 SQL> commit;
Commit complete.

14:59:10 SQL> select * from c;
NO NAME
---------- -----------------------------------------------------------------------------------------------
1
23
44
2
23333333
4 asdasdasda
6 rows selected.

14:59:33 SQL>
15:01:37 SQL> alter table c drop column name;
Table altered.

15:01:50 SQL> flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss');
flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

15:02:41 SQL> alter table c enable row movement;
Table altered.

15:02:56 SQL> flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss');
flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


******************** End *******************

Flashback version query Oracle 10g

********************** Flashback version query ********************

VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or
VERSIONS BETWEEN SCN [lower bound] AND [lower bound].

The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. For our first flashback version query, we will attempt to retrieve all available data.

********* CODE *********

sql>col versions_starttime format a25
sql>col versions_endtime format a25
sql>insert into c values(11);
sql>commit;
sql> select versions_starttime, versions_endtime, versions_xid,versions_operation,no from c versions between timestamp minvalue and maxvalue;

select VERSIONS_STARTSCN,VERSIONS_ENDSCN,VERSIONS_XID,VERSIONS_OPERATION,no from c versions between scn minvalue and maxvalue;

>>>> Oracle inbuilt function HEXTORAW converts char containing hexadecimal digits in the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 character set to a raw value

select * from FLASHBACK_TRANSACTION_QUERY WHERE XID =hextoraw('03001800BC010000');

Export into multiple Dump files

It creates 4 dumpfiles each the size of 5 GB.Please check the below script.

exp "'sys/passwd as sysdba'" buffer=314572800 file='\\Serv103\db1_backup\backup_%date:~4,2%_%date:~7,2%_%date:~10%_first.dmp' '\\Serv103\db1_backup\backup_%date:~4,2%_%date:~7,2%_%date:~10%_second.dmp' '\\Serv103\db1_backup\backup_%date:~4,2%_%date:~7,2%_%date:~10%_third.dmp' '\\Serv103\db1_backup\backup_%date:~4,2%_%date:~7,2%_%date:~10%_fourth.dmp'

log=C:\Backup\backup_%date:~4,2%_%date:~7,2%_%date:~10%.log FILESIZE=5GB Full=yes statistics=none

Using Variable in SQLPLUSW

Let’s see the behavior when using a bind variable. Here is the demonstrated behavior in Oracle Database 10g.

SQL> var state_code varchar2(2)
SQL> exec :state_code := ‘CT’
PL/SQL procedure successfully completed.
SQL> select max(times_purchased) from customers where state_code = :state_code
2 /

Loop Back Adapter Configuration (General information :-) )

If there is a loopback adapter installed, you would see a section that lists the values for the loopback adapter. For example:

Ethernet adapter Local Area Connection 2:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Microsoft Loopback Adapter
Physical Address. . . . . . . . . : 02-00-4C-4F-4F-50
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Autoconfiguration IP Address. . . : 169.254.25.129
Subnet Mask . . . . . . . . . . . : 255.255.0.0

Installing a Loopback Adapter on Windows 2003 or Windows XP

To install a loopback adapter on Windows 2003 or Windows XP:

>Open the Windows Control Panel.
>Double-click Add Hardware to start the Add Hardware wizard.
>In the Welcome window, click Next.
>In the Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
>In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
>In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
>From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
>In the Select Network Adapter window, make the following selections:
Manufacturer: Select Microsoft.
Network Adapter: Select Microsoft Loopback Adapter.
Click Next.
>In the The wizard is ready to install your hardware window, click Next.
>In the Completing the Add Hardware Wizard window, click Finish.

If you are using Windows 2003, restart your computer.

>Right-click My Network Places on the desktop and choose Properties. This displays the Network Connections Control Panel.
>Right-click the connection that was just created. This is usually named "Local Area Connection 2". Choose Properties.
>On the General tab, select Internet Protocol (TCP/IP), and click Properties.
>In the Properties dialog box, click Use the following IP address and do the following:
>IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:

192.168.x.x (x is any value between 0 and 255)
10.10.10.10
Subnet mask: Enter 255.255.255.0.
>Record the values you entered, which you will need later in this procedure.
>Leave all other fields empty.
>Click OK.
>Click OK.
>Close Network Connections.
>Restart the computer.

Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts file with the following format, after the localhost line:
IP_address hostname.domainname hostname

where:
IP_address is the non-routable IP address you entered in step 16.
hostname is the name of the computer.
domainname is the name of the domain.

For example:
10.10.10.10 mycomputer.mydomain.com mycomputer

>Check the network configuration:
>Open System in the Control Panel, and select the Computer Name tab. In Full computer name, make sure you see the host name and the domain name, for example, sales.us.mycompany.com.
>Click Change. In Computer name, you should see the hostname, and in Full computer name, you should see the host name and domain name. Using the previous example, the host name would be sales and the domain would be us.mycompany.com.
>Click More. In Primary DNS suffix of this computer, you should see the domain name, for example, us.mycompany.com.

Removing a Loopback Adapter

To remove a loopback adapter:

>Display System in the Windows Control Panel.
>In the Hardware tab, click Device Manager.
>In the Device Manager window, expand Network adapters. You should see Microsoft Loopback Adapter.
>Right-click Microsoft Loopback Adapter and select Uninstall.
>Click OK.

Some basic commands for DBA's

**************** queries ****************
-----------
The below query lists the records for which the employees have salary greater than 5000 and
the departments which has the number of employees greater than 2.

select * from emp where sal > 5000 group by detpno having count(empno) >2;
-----------

The below query lists the first two employee records filtered using rownum.

select a.* from
(select rownum as rowcnt,empno,ename from emp) a
where rowcnt > 2

-----------

select * from v$session
select * from v$database;
select * from v$archived_log;
select * from v$instance;
select * from v$tablespace;
select * from v$locks;
select * from v$bg;
select * from v$process;
select * from v$datafile;
select * from v$log
select * from v$logfile;
select * from v$controlfile;
select * from v$process
select * from v$system_events

--------- RMAN -----------

Show all;
report schema;
List backup summary;
restore database preview;
List backup;
List incarnation;
List archivelog all completed before 'sysdate-6';
backup archivelog all completed before 'sysdate-1';
backup archivelog all not backedup 1 time;
List backup of datafile 1 ;
restore database except datafile 1;
restore database exclude tablespace 1;
delete archivelog sequence between 1 and 100;
backup datafile 1;

run
{
allocate channel c1 device type disk format='e:\backup\navan.dbf';
backup database;
}

Restore datafile to another location:
----------------------------------------
run
{
allocate channel c1 device type disk;
set newname for datafile 1 to 'e:\backup\system01.dbf';
restore datafile 1;
recover datafile 1;
sql "alter database datafile 1 online";
switch datafile 1;
}

Alter database create datafile 'new datafile path' using 'old datafile path';

************* Parameter related options ***********

> set linesize 100
> col name format a20
> show parameter pfile
> show parameter spfile
> show parameter archivelog
> show parameter controlfile_record_keep_time
> show parameter datafile
> show parameter db_recovery_file_dest
> show parameter db_create_online_log_dest
> show parameter db_create_file_destw
> show parameter logfile
> show parameter undo_retention
> show parameter process
> show parameter listener

*******************

------------------ Flask back technology --------------

Flash back table to before drop;
select * from emp as of timestamp to_timestamp('14/04/2009 12:22:11 am','dd/mm/yyyy hh:mi:ss am');
select sysdate from dual;
select * from flashback_query
----------------------------------------------------
alter tablespace 1 offline;
alter database datafile 1 move 'new location'
------------------------------------------------------

---------- Linux commands ------------

ps -ef grep oracle
kill -9 processid
top
tail -f filename
[ -- df -g -> only in IBM AIX ]
fdisk -l
mount
df -h
pwd
----
service nfs restart
service network restart
----
[ ext3 -> Linux partition file system ]
**
mount -t /mnt/cdrom /dev/cdrom
cd /dev/cdrom
**
ls -l
ls -a

ls -l -t ->  << list files in last modified order >>.
find *.* -atime -2 exec ls -l {} \; -->  << list files that are accessed 2 times >>
find *.* -atime -2 -exec ls -al {} \;
find . -iname "*.*" -mtime -2 -print

Transport Tablespace Reference

For Oracle transport tablespace refer this:

1. Make the tablespace read only.
2.export tablespace using TRANSPORT_TABLESPACE option
3. copy the datafile to the destination database server.
4.transfer the dumpfile to the destination server.
5. import tablespace using TRANSPORT_TABLESPACE option.
6. Make the tablespace read write

Eg:
Transport Tablespace:
exp TRANSPORT_TABLESPACE=y TABLESPACES=(history) FILE=/disk2/backup/hs.dmp

Table Level Import by replacing statistics

table import
-------------------

[nvar@insiscdev01 Navan]$ impdp system/nvar@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TMP_22Apr10.dmp tables=nvartxn.REPORT_GROUP remap_schema=nvartxn:nvartxn TABLE_EXISTS_ACTION=replace

Import: Release 11.1.0.6.0 - Production on Friday, 23 April, 2010 0:42:22
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
nvarting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TMP_22Apr10.dmp tables=nvartxn.REPORT_GROUP remap_schema=nvartxn:nvartxn TABLE_EXISTS_ACTION=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "nvarTXN"."REPORT_GROUP" 14.16 KB 46 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 00:42:27

import schemas from dump
------------------------------

[nvar@insiscdev01 Navan]$ impdp system/nvar@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TST_22Apr10.dmp remap_schema=nvartxn:nvartxn,nvarrep:nvarrep,nvartxnapp:nvartxnapp,nvarrepapp:nvarrepapp,nvarmig:nvarmig logfile=impdp_nvar4_TMP_22Apr10.log

Oracle 10g Complete Architecture

Oracle 10g Architecture

Oracle 9i complete Architecture

Oracle 9i complete Architecture

Using Database Verify command on the Datafiles for corruption

Below is the script file for using DBverify command against the datafiles.

Save the below file as Shell file using extension .sh in unix or Batch file as .bat in windows and execute

dbv file=/NVAR/oracle/oradata/NAVADEMO/system01.dbf blocksize=8192 logfile=system01.dbf.1.log dbv file=/NVAR/oracle/oradata/NAVADEMO/sysaux01.dbf blocksize=8192 logfile=sysaux01.dbf.2.log dbv file=/NVAR/oracle/oradata/NAVADEMO/undotbs01.dbf blocksize=8192 logfile=undotbs01.dbf.3.log dbv file=/NVAR/oracle/oradata/NAVADEMO/users01.dbf blocksize=8192 logfile=users01.dbf.4.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data01.dbf blocksize=8192 logfile=qliveprod_data01.dbf.5.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_indx.dbf blocksize=8192 logfile=qliveprod_indx.dbf.6.log dbv file=/NVAR/oracle/oradata/NAVADEMO/tiger_data01.dbf blocksize=8192 logfile=tiger_data01.dbf.7.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data02.dbf blocksize=8192 logfile=qliveprod_data02.dbf.8.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data03.dbf blocksize=8192 logfile=qliveprod_data03.dbf.9.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data01.dbf blocksize=8192 logfile=NVAR_data01.dbf.10.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_idx01.dbf blocksize=8192 logfile=NVAR_idx01.dbf.11.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data02.dbf blocksize=8192 logfile=NVAR_data02.dbf.12.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data03.dbf blocksize=8192 logfile=NVAR_data03.dbf.13.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02d02.dbf blocksize=8192 logfile=NVAR02d02.dbf.14.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR01d01.dbf blocksize=8192 logfile=NVAR01d01.dbf.15.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR01i01.dbf blocksize=8192 logfile=NVAR01i01.dbf.16.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02d01.dbf blocksize=8192 logfile=NVAR02d01.dbf.17.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02i01.dbf blocksize=8192 logfile=NVAR02i01.dbf.18.log

Below is the logfile created for a file which has no corruption ,by the above script file.
DBVERIFY: Release 11.1.0.6.0 - Production on Thu Apr 29 13:07:27 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY-Verification starting : FILE = /nvar/oracle/oradata/NAVADEMO/nvar_data01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 513280
Total Pages Processed (Data) : 372277
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 103009
Total Pages Failing (Index): 0
Total Pages Processed (Other): 35162
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2832
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3358059738 (1685.3358059738)

Below is the logfile created for a file which has corruptions ,by the above script file.

DBVERIFY: Release 11.1.0.6.0 - Production on Thu Apr 29 13:08:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY-Verification starting : FILE = /nvar/oracle/oradata/NAVADEMO/nvar_data03.dbf
Page 38576 is marked corrupt
Corrupt block relative dba: 0x034096b0 (file 13, block 38576)
Bad header found during dbv:
Data in bad block:
type: 97 format: 0 rdba: 0x74616369
last change scn: 0x6e76.2f6e6f69 seq: 0x64 flg: 0x2e
spare1: 0x70 spare2: 0x6c spare3: 0x652d
consistency value in tail: 0x794d0602
check value in block header: 0x736d
computed block checksum: 0x60a
Page 48062 is marked corrupt
Corrupt block relative dba: 0x0340bbbe (file 13, block 48062)
Bad header found during dbv:
Data in bad block:
type: 83 format: 5 rdba: 0x5b203230
last change scn: 0x7270.412f3432 seq: 0x2f flg: 0x32
spare1: 0x52 spare2: 0x30 spare3: 0x3a30
consistency value in tail: 0x80c30601
check value in block header: 0x3130
block checksum disabled
Page 49482 is marked corrupt
Corrupt block relative dba: 0x0340c14a (file 13, block 49482)
Bad header found during dbv:
Data in bad block:
type: 58 format: 4 rdba: 0x2b203732
last change scn: 0x205d.30333530 seq: 0x22 flg: 0x47
spare1: 0x33 spare2: 0x3a spare3: 0x2f20
consistency value in tail: 0x80c40601
check value in block header: 0x5445
computed block checksum: 0x3bbd
Page 70259 is influx - most likely media corrupt
Corrupt block relative dba: 0x03411273 (file 13, block 70259)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03411273
last change scn: 0x0695.a7c6e680 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x2563
computed block checksum: 0xe719
Page 81382 is marked corrupt
Corrupt block relative dba: 0x03413de6 (file 13, block 81382)
Bad header found during dbv:
Data in bad block:
type: 49 format: 0 rdba: 0x32303a38
last change scn: 0x3033.35302b20 seq: 0x5d flg: 0x20
spare1: 0x3a spare2: 0x35 spare3: 0x5445
consistency value in tail: 0x47e1060d
check value in block header: 0x4722
block checksum disabled
Page 101964 is influx - most likely media corrupt
Corrupt block relative dba: 0x03418e4c (file 13, block 101964)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03418e4c
last change scn: 0x0695.a78be66c seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x6f18
computed block checksum: 0xafef
Page 109891 is marked corrupt
Corrupt block relative dba: 0x0341ad43 (file 13, block 109891)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x000a.000a6b00 seq: 0x5f flg: 0x09
spare1: 0xba spare2: 0xbe spare3: 0x6000
consistency value in tail: 0xcac70624
check value in block header: 0x900
block checksum disabled
Page 111680 is marked corrupt
Corrupt block relative dba: 0x0341b440 (file 13, block 111680)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x0720.00072200 seq: 0x0 flg: 0x21
spare1: 0xba spare2: 0xbe spare3: 0x7513
consistency value in tail: 0x410e0602
check value in block header: 0x1
block checksum disabled
Page 133669 is influx - most likely media corrupt
Corrupt block relative dba: 0x03420a25 (file 13, block 133669)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03420a25
last change scn: 0x0695.a7cf9b37 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x9232
computed block checksum: 0xfa1e
Page 134914 is marked corrupt
Corrupt block relative dba: 0x03420f02 (file 13, block 134914)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x0715.00071800 seq: 0x0 flg: 0x16
spare1: 0xba spare2: 0xbe spare3: 0x730e
consistency value in tail: 0xa8750601
check value in block header: 0x1
computed block checksum: 0x1167
Page 169859 is marked corrupt
Corrupt block relative dba: 0x03429783 (file 13, block 169859)
Bad header found during dbv:
Data in bad block:
type: 2 format: 0 rdba: 0x00140000
last change scn: 0x0000.03e80000 seq: 0x0 flg: 0x00
spare1: 0x1 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf9f82802
check value in block header: 0x0
block checksum disabled
Page 238087 is influx - most likely media corrupt
Corrupt block relative dba: 0x0343a207 (file 13, block 238087)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0343a207
last change scn: 0x0695.a7dc8aa6 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0xa4f7
computed block checksum: 0x6dd4

DBVERIFY - Verification complete
Total Pages Examined : 256000
Total Pages Processed (Data) : 192131
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 46117
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14782
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2958
Total Pages Marked Corrupt : 12
Total Pages Influx : 4
Total Pages Encrypted : 0
Highest block SCN : 3358059738 (1685.3358059738)

avoiding pls-00436 with forall

FORALL: Bulk update. - Check the above link for more information.

Workaround Queries(11g) While Monitoring

------------------------ check session information -----------------------

select * form v$fixed_table;
select * from v$session where osuser='501200I127';
select * from v$sysstat
select * from v$session_longops where sid in (130,138)
select * from v$session where osuser='50120C1008';
select * from v$session where type!= 'BACKGROUND';
select 'alter system kill session ' '''' sid ',' serial#'''' ' immediate;' as script from v$session where osuser='501200I127';
select * from v$session where sid in (147);
alter system kill session '138,8217' immediate;

-------------find which session is using a particular object --

select * from v$access where object like 'RPT_TREASURY_REPORT_FILTERS%';

---------- enable trace for a particular session ------

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(53 ,5083,true);

---------- find the trace files for a session -------

select b.username, c.value '\' lower(d.value) '_ora_'
to_char(a.spid, 'fm00000') '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr and c.name = 'user_dump_dest'
and d.name = 'db_name' and b.username is not null and b.sid=53;
alter system kill session '191,19465' immediate;

----------------- find the undo space usage in UNDO tablespace for a database ---------------------------

select sum(decode(status,'EXPIRED',bytes,0))/sum(bytes) EXPIRED_PERCENT,
sum(decode(status,'UNEXPIRED',bytes,0))/sum(bytes) UNEXPIRED_PERCENT,
sum(decode(status,'ACTIVE',bytes,0))/sum(bytes) ACTIVE_PERCENT
from dba_undo_extents;

--------------------- find the Database components features usage ----------------------------------

select * from DBA_FEATURE_USAGE_STATISTICS;

------------------------ find current running query using OS process id -----------------

SELECT s.sid, s.serial#, q.SQL_TEXT ,s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p,v$sqltext q
WHERE s.paddr = p.addr and q.address = s.sql_address
AND q.hash_value = s.sql_hash_value AND p.spid = 22737;
---------- 1 row

SELECT s.sid,s.osuser,q.SQL_FULLTEXT
FROM v$session s, v$process p,v$sqlarea q
WHERE s.paddr = p.addr and s.sql_address= q.address AND p.spid = 24510;

exec dbms_stats.GATHER_TABLE_STATS('STARREP','DISCOUNT_FACTOR_DATA');

--------------------- find the OS process id -----------------------

SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (488);

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24);

--------- cpu usage information ---------

select a.sid, a.value cpu_usage, a.value/b.value cpu_bycall from v$sesstat a, v$sesstat b
where a.sid=b.sid and a.statistic#=12 and b.statistic#=6
and a.value>0 and b.value>0 order by a.value desc;

-------------------------- find the top session ------------------

Select sid,username,round(100 * total_user_io/total_io,2) tot_io_pct
from (select
b.sid sid,nvl(b.username,p.name) username,sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by b.sid, nvl(b.username,p.name)),
(select sum(value) total_io from
sys.v_$statname c,
sys.v_$sesstat a
where a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by 3 desc;

-------------------- find the blocking sessions --------------------------

select s1.username '@' s1.machine
' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session WHERE blocking_session IS NOT NULL;

------------- identify locked object ----------------------------

select object_name from dba_objects where object_id = 88519;
select * from v$lock;

--------------- SQL run time monitor -----------------------------

select * from v$sql_monitor where sid = 344;

---------------- find top SQL queries ------------------------------

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/01/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('09/09/2008','MM/DD/YYYY'))
GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

------------------------ resources used by a session --------------

select s.osuser osuser,s.serial# serial,se.sid,n.name,max(se.value) maxmem
from v$sesstat se,v$statname n,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
group by s.osuser, s.serial#, se.sid, n.name
order by 2;

--- check local session

select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.statistic# and b.sid in (474,475,477,478,481,494,499) ;

-------------------check number of cursors opened -----------------------------------------------------

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.username, s.machine
from v$sesstat a,v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;

How to recover the deleted mails in outlook

>> Click Start,
>> Click Run, type regedit, and then click OK.
>> Locate and then click the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Client\Options
>> Right-click the Options subkey, point to New, and then click DWORD Value.
>> Type DumpsterAlwaysOn for the DWORD name, and then press ENTER.
>> Double-click DumpsterAlwaysOn. In the Value data box, type 1, and then click OK.
>> Quit Registry Editor.

To enable hard-deleted items recovery in Outlook follow these steps:

>> Open Registry Editor.
>> In Registry Editor, navigate to the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Client\Options
>> Create the following value (DWORD):
DumpsterAlwaysOn
and give it a value of 1.

Note: As always, before making changes to your registry you should always make sure you have a valid backup. In cases where you're supposed to delete or modify keys or values from the registry it is possible to first export that key or value(s) to a .REG file before performing the changes.

>> Close Registry Editor, re-open Outlook for settings to take place.
>> After you add this value, the Recover Deleted Items option becomes available for the Sent Items folder, the Drafts folder, the Outbox folder, and the Inbox folder.
>> You can recover deleted items from these folders.
/images/ol_recover_del6.gif/images/ol_recover_del6.gif
>> As you can clearly see, the Inbox has the Recover Deleted Items option enabled, and so does each and every folder in the user's mailbox.

Statspack Report scheduling based on the Requirment timings

The below steps are to schedule the statspack collection to run on the specific timings. ie it runs in the weeksdays apart from monday by the interval 3 hours, and from sunday 12 pm to monday 12 pm it runs every 1 hour (ie during the peak time).


STEP 1: Remove the existing statspack job schedule:
EXECUTE DBMS_JOB.REMOVE(1);

STEP 2 : Function that will return the date to the job:

create or replace function statspack_schedule
return date
as
N_date date;
begin
select case
when ( to_char(dt,'dy') = 'sun' and to_char(dt,'hh24:mi')>'12:00')
or ( to_char(dt,'dy') = 'mon' and to_char(dt,'hh24:mi')<'12:00') then d1 else d2 end into N_date from ( select sysdate dt , trunc(sysdate, 'hh24') +1/24 d1 , trunc(sysdate, 'hh24') +3/24 d2 from dual ); return N_date; end; /

STEP 3: statspack Job scheduling :

BEGIN
DBMS_JOB.isubmit (
job => 1,
what => 'statspack.snap;',
next_date => sysdate,
interval => '( select statspack_schedule from dual )');
COMMIT;
END;
/

STEP 4: Purge the old data with the retention of 1 month (Manually do this activity every month):

CONNECT perfstat/perfstat
@?\rdbms\admin\sppurge.sql
(specified the snap id with retention of 1 month)
to identified the begining and end snap id:
select snap_id, snap_level "level", to_char(snap_time,' dd/mm/YYYY HH24:mi:ss') snap_date from stats$snapshot
where snap_timeorder by 1;

Components still show 9.2.0.1 version after Applying 9.2.0.7 patchset also

There is an issue while applying patchset 9.2.0.7 on 9.2.0.1. Please see the below steps and explanation for more information.

Step 1 : I installed 9.2.0.1 software on a windows xp professional machine for testing purpose. But no database was created at the time.

Step 2 : And later i applied 9.2.0.7 patch on that 9.2.0.1 software, the patch installation went fine without any issues or errors.

Step 3 : And after the sucessfull patch insallation i created a database using DBCA. it was sucessfull.

Step 4 : I connected the database as sys user and checked the version using V$VERSION view. it displayed

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - ProductionPL/SQL Release 9.2.0.7.0 - ProductionCORE 9.2.0.7.0 ProductionTNS for 32-bit Windows: Version 9.2.0.7.0 - ProductionNLSRTL Version 9.2.0.7.0 - Production

Step 5: As a precautionary measure i thought of checking the components version from
dba_registry and it showed the old version that is 9.2.0.1. Please see below

SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ---------------------------
Oracle9i Catalog Views 9.2.0.1.0
Oracle9i Packages and Types 9.2.0.1.0
Oracle Workspace Manager 9.2.0.1.0
JServer JAVA Virtual Machine 9.2.0.1.0
Oracle XDK for Java 9.2.0.1.0
Oracle9i Java Packages 9.2.0.1.0
Oracle interMedia 9.2.0.1.0
Spatial 9.2.0.1.0
Oracle Text 9.2.0.1.0
Oracle XML Database 9.2.0.1.0
Oracle Ultra Search 9.2.0.1.0
Oracle Data Mining 9.2.0.1.0
OLAP Analytic Workspace 9.2.0.1.0
Oracle OLAP API 9.2.0.1.0
OLAP Catalog 9.2.0.1.0
15 rows selected.

So its a problem. So we have to manualy upgrade the components by running scripts which is mentioned in the README file which came with the patch.

So i ran the scripts by connecting as a SYS user to the database as below

sqlplus sys/******* as sysdba
SQL> Shutdown immediate
SQL> startup migrate
SQL> @C:\oracle\rdbms\admin\catpatch.sql

after the sucessfull execution of the above script executed the second script as mentioned in the README file.

SQL>Shutdown immediate
SQL> startup
SQL>@C:\oracle\rdbms\admin\utlrp.sql

after the sucessfull execution of both the scripts again i checked the components version and found that everything is fine, Please see below.


SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ---------------------------
Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
Oracle Workspace Manager 9.2.0.1.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0
Oracle interMedia 9.2.0.7.0
Spatial 9.2.0.7.0
Oracle Text 9.2.0.7.0
Oracle XML Database 9.2.0.7.0
Oracle Ultra Search 9.2.0.7.0
Oracle Data Mining 9.2.0.7.0
OLAP Analytic Workspace 9.2.0.7.0
Oracle OLAP API 9.2.0.7.0
OLAP Catalog 9.2.0.7.0
15 rows selected.

Thanks ..
Navaneeth