Hakki Al Saidi حقي الساعدي

RESTORE TABLE FROM RMAN BACKUP ORACLE12c database

2014-06-09T15:13:00.001+03:00

The RECOVER TABLE command is a new feature in 12C that allows point in time recovery of a table or a table partition.
The table is recovered into an auxiliary instance and there is the option to:

import the recovered table into a new table or partition using REMAP option
create the expdp dump of the recovered table only, for import at a later time of your choosing

Pre-requisites:

The target database must be in read-write mode.
The target database must be in ARCHIVELOG mode.
You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.
To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

Setting the Point-in-time to Which Tables and Table Partitions Must be Recovered

Options are:

SCN
Time
Sequence number

The ROOT container SYSTEM and UNDO tablespaces are restored to the auxiliary therefore RECOVER TABLE has to be run after a direct connection to ROOT CDB. If a service_name is not used, RMAN will fail during export:

SELECT SEQUENCE#, THREAD#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG

run
{
RECOVER TABLE "SCOTT"."EMP"
UNTIL SEQUENCE 10 thread 1
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'EMP':'EMP_PREV';
}

OR

run
{
RECOVER TABLE "SCOTT"."SALGRADE"
UNTIL time "TO_DATE('09-JUN-14 14:00:00','DD-MON-YY HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'SALGRADE':'SALGRADE_PREV';
}

the out put is

RMAN> run
2> {
RECOVER TABLE "SCOTT"."EMP"
UNTIL SEQUENCE 10 thread 1
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'EMP':'EMP_PREV';
}3> 4> 5> 6> 7>

Starting recover at 09-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=346 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=207 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=253 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=347 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

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

Creating automatic instance, with SID='zmww'

initialization parameters used for automatic instance:
db_name=HLRMAIN2
db_unique_name=zmww_pitr_HLRMAIN2
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/app/oracle/oradata/HLRMAIN3
log_archive_dest_1='location=/u01/app/oracle/oradata/HLRMAIN3'
#No auxiliary parameter file used

starting up automatic instance HLRMAIN2

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes
Variable Size 293602560 bytes
Database Buffers 767557632 bytes
Redo Buffers 5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until logseq 10 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=74 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=64 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=83 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=92 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN/c-1520971765-20140609-02
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/c-1520971765-20140609-02 tag=TAG20140609T141102
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl
Finished restore at 09-JUN-14

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until logseq 10 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN/HLRMAIN2_S_67_P_1_T_849795008
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u01/RMAN/HLRMAIN2_S_66_P_1_T_849795008
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/HLRMAIN2_S_67_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/u01/RMAN/HLRMAIN2_S_66_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_3: piece handle=/u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:55
Finished restore at 09-JUN-14

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_9sc7k9pc_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_9sc7k9ox_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_9sc7k9pk_.dbf

contents of Memory Script:
{
# set requested point in time
set until logseq 10 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 3 online

Starting recover at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JUN-14

sql statement: alter database open read only

contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes
Variable Size 297796864 bytes
Database Buffers 763363328 bytes
Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2296576 bytes
Variable Size 297796864 bytes
Database Buffers 763363328 bytes
Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until logseq 10 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 09-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=64 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=74 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=83 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=92 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-JUN-14

datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=849797166 file name=/u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_9sc7mx3h_.dbf

contents of Memory Script:
{
# set requested point in time
set until logseq 10 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 6 online

Starting recover at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-JUN-14

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/HLRMAIN3''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/HLRMAIN3''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/HLRMAIN3''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/HLRMAIN3''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_zmww_uaxg":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_ADMINMSG_BUS_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_EVENT_BUS_TABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_NOTIFY_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_NOTIFY_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_HOST_PING_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_CNTR_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_LOADER_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
EXPDP> . . exported "SCOTT"."EMP" 8.757 KB 14 rows
EXPDP> Master table "SYS"."TSPITR_EXP_zmww_uaxg" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_zmww_uaxg is:
EXPDP> /u01/app/oracle/oradata/HLRMAIN3/tspitr_zmww_74786.dmp
EXPDP> Job "SYS"."TSPITR_EXP_zmww_uaxg" completed with 7 error(s) at Mon Jun 9 14:47:07 2014 elapsed 0 00:00:45
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_zmww_eCcE" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_zmww_eCcE":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "SCOTT"."EMP_PREV" 8.757 KB 14 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_zmww_eCcE" successfully completed at Mon Jun 9 14:47:30 2014 elapsed 0 00:00:15
Import completed

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_temp_9sc7m2pz_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_3_9sc7mzvf_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_2_9sc7mznf_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_1_9sc7mzfb_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_9sc7mx3h_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_9sc7k9pk_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_9sc7k9ox_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_9sc7k9pc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl deleted
auxiliary instance file tspitr_zmww_74786.dmp deleted
Finished recover at 09-JUN-14

http://www.pythian.com/blog/table-recovery-with-rman-in-database-12c/

ORA-24248 XMLDB extensible security not installed

2013-01-10T12:14:00.004+03:00

ORA-24248 XMLDB extensible security not installed

Oracle 11g introduces more secure and fine grained access on network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR, i.e. an execute privilege on these packages is not enough to access an external network resource using these packages. You have to configure ACL (Access Control List), assign the network host and port to it and grant connect privilege to the users through this ACL .

Please see: Oracle 11g Access Control List for External Network Services

These ACLs are XML files which are stored in the XML Database repository inside the database itself, which means that XML Database must be installed in the database to use the network PL/SQL packages. XML Database is installed automatically when you create a database using DBCA with default options. However, if you unselect/uncheck the XML Database option in DBCA while creating the database, XML Database won't installed.

A few days ago I ran into this problem. We upgraded one of our development database from oracle 10gR2 to 11gR1 recently. After the upgrade the application was broken very badly. Any user trying to login to the application was getting the error below:

ORA-24248: XMLDB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

After I investigated, I found that that in one of Java classes in the application, where a connection was being established to the databases, a query was being executed like this.

SELECT global_name,utl_inaddr.get_host_address FROM global_name;

When I saw UTL_INADDR being called, I realized instantly that we need an ACL for this call and ACL needs XML Database. The error above is quite self explanatory. I went ahead and verified the existence of Oracle XML Database and found the following:

SQL> select comp_name from dba_registry;

COMP_NAME
----------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
OLAP Catalog
Spatial
Oracle Multimedia
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API

17 rows selected.

As can be seen from the output of the above query, we were missing Oracle XML Database. I was able to reproduce the problem in sqlplus.

SQL> conn scott/tiger
Connected.

SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

SQL>

Install XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool install_xml_db.log
SQL> @catqm xdb sysaux temp YES
-- xdb is the password for XML Database repository schema
-- sysaux is the default tablespace for XML Database repository schema.
-- temp is the default temporary tablespace for XML Database repository schema.
-- YES is for using secure files for xdb$resource
.
.
.
[output trimmed]
.
.
.
SQL> declare
  2    suf  varchar2(26);
  3    stmt varchar2(2000);
  4  begin
  5    select toksuf into suf from xdb.xdb$ttset where flags = 0;
  6    stmt := 'grant all on XDB.X$PT' || suf || ' to DBA';
  7    execute immediate stmt;
  8    stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION';
  9    execute immediate stmt;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>spool off

The XML Database installation is complete. I opened the install_xml_db.log and didn't find any significant errors in it. Then I verified from dba_registry again to see the status of XML Database installed.

SQL> select comp_name , status from dba_registry;

COMP_NAME                                STATUS
---------------------------------------- --------------------------------------------
Oracle XML Database                      VALID
OWB                                      VALID
Oracle Application Express               VALID
Oracle Enterprise Manager                VALID
OLAP Catalog                             VALID
Spatial                                  VALID
Oracle Multimedia                        VALID
Oracle Text                              VALID
Oracle Expression Filter                 VALID
Oracle Rules Manager                     VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  VALID
Oracle OLAP API                          VALID

18 rows selected.

SQL>

The status should be VALID to indicate that the installation was successfull. Then I tested the UTL_INADDR again.

SQL> conn scott/tiger
Connected.
SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

As you see now the ORA-24248 is gone because we have XML Database installed. Now we are seeing ORA-24247, which means we can't access any network host using UTL_INADDR unless we allow access to this host via ACL.

SQL> conn / as sysdba
Connected.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'my_application_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
 );
END;
/

PL/SQL procedure successfully completed.

/*
 All that UTL_INADDR.get_host_address does is resolve the host and return the IP Address.
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL creates an ACL and grant privilege to a user or role 
 to that ACL.
 For the sake of this article I am using SCOTT as the grantee of the privilege resolve but 
 in my application I granted this privilege to a role which was assigned to all the 
 application users. So through that role all the users were granted resole privilege to 
 this ACL.
*/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         =>'my_application_acl.xml',
     host        => 'localhost',
     lower_port  => null,
     upper_port  => null
     );
END;
/

PL/SQL procedure successfully completed.

/*
 When UTL_INADDR.get_host_address is invoked without a host name specified with it,
 it sends the request to localhost. So we need to assign our ACL to the host "localhost".
*/

commit;

SQL> conn scott/tiger
Connected.
SQL> set lines 10000
SQL> column global_name format a20
SQL> column get_host_address format a15
SQL> select global_name,utl_inaddr.get_host_address from global_name;

GLOBAL_NAME          GET_HOST_ADDRES
-------------------- ---------------
ORA11G.HOME.COM      127.0.0.1

SQL>

As you can see above, SCOTT is now able to get server's IP Adress using utl_inaddr.get_host_address. And this solves our problem with the application.

Uninstall XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @catnoqm.sql
-- This will uninstall the Oracle XML Database.

......Zahid

DB_ULTRA_SAFE a new GEM for High Availability

2012-11-05T16:17:00.003+03:00

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

When you set DB_ULTRA_SAFE to

Then the following parameters…

DATA_AND_INDEX (recommended by Oracle)

DB_BLOCK_CHECKING is set to FULL.
DB_LOST_WRITE_PROTECT is set to TYPICAL.
DB_BLOCK_CHECKSUM is set to FULL.

DATA_ONLY

DB_BLOCK_CHECKING is set to MEDIUM.
DB_LOST_WRITE_PROTECT is set to TYPICAL.
DB_BLOCK_CHECKSUM is set to FULL.

Lets’ Check all the parameters affected by DB_ULTRA_SAFE:

· DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.

· DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.

· DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3. SQL> shutdown immediate (Shutdown your Database)

4. SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database:

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3. SQL> startup nomount

4. SQL> alter database mount standby database;

5. SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB:

1. SQL> alter database recover managed standby database cancel;

2. SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command:

1. SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

by Francisco Munoz Alvarez

Connect Time Failover & Transparent Application Failover for Data Guard

2012-10-24T16:07:00.001+03:00

Connect Time Failover & Transparent Application Failover for Data Guard

I was giving a 10g Data Guard course this week in Düsseldorf, demonstrating amongst others the possibility to configure Transparent Application Failover (TAF) for Data Guard. I always try to keep things as simple as seriously possible, in order to achieve an easy and good understanding of what I like to explain. Later on, things are getting complex by themselves soon enough

In my simple scenario, I have one Primary Database (prima) and one Physical Standby Database (physt). On my downloads page, you may find an installation guide for that scenario for 10g and for 11g. After a switchover or after a failover, the primary is going to be physt. The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = myapp)

)

HostA is the host on which prima runs, HostB has physt running.

Second, we take care that the service myapp is offered only at the right database – only on the primary.Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin

dbms_service.create_service('myapp','myapp');

end;

begin

DBMS_SERVICE.START_SERVICE('myapp');

end;

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database

declare

v_role varchar(30);

begin

select database_role into v_role from v$database;

if v_role = 'PRIMARY' then

DBMS_SERVICE.START_SERVICE('myapp');

else

DBMS_SERVICE.STOP_SERVICE('myapp');

end if;

end;

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at physt without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to prima are disconnected if a switchover or failover to physt takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin

dbms_service.modify_service

('myapp',

FAILOVER_METHOD => 'BASIC',

FAILOVER_TYPE => 'SELECT',

FAILOVER_RETRIES => 200,

FAILOVER_DELAY => 1);

end;

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption (about 20 seconds, in a typical case). Only if sessions have open transactions during the failover/switchover, they will receive error messages (“transaction must roll back”) after they try commit then.

I use to demonstrate that with a select on a table with 100000 rows that starts on the primary. Then I kill the SMON of that primary and the select stops at row 30000 something, waits a couple of seconds (maximal 200, with the above settings) and then continues on the new primary after the failover, fetching exactly the 100000 rows! That is always quite impressive and shows how robust Oracle Databases – especially combined with Data Guard – are

by Uwe Hesse,

How to change database character set

2012-07-18T14:54:00.001+03:00

This article gives a overview of methods to change the database character set .

Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;

Transportable Tablespaces

2012-07-18T14:34:00.001+03:00

Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.

Oracle Data Pump in Oracle Database 10g (expdp and impdp)
Data Pump Enhancements in Oracle Database 11g Release 1
SQL Developer 3.1 Data Pump Wizards (expdp, impdp)

For this example I'm going to create a new tablespace, user and table to work with in the source database.

CONN / AS SYSDBA

CREATE TABLESPACE test_data
DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test_user IDENTIFIED BY test_user
DEFAULT TABLESPACE test_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON test_data;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
'Description for ' || level
FROM dual
CONNECT BY level <= 10000;

COMMIT;
Source Database

For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check.

CONN / AS SYSDBA
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL>
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.

SELECT * FROM transport_set_violations;

no rows selected

SQL>
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.

SQL> ALTER TABLESPACE test_data READ ONLY;

Tablespace altered.

SQL>
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.

The source tablespace can now be switched back to read/write mode.

ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log
Switch the new tablespace into read write mode.

SQL> ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
The tablespace is now available in the destination database.

SELECT tablespace_name, plugged_in, status
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_DATA';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TEST_DATA YES ONLINE

1 row selected.

SQL>

By Tim.....

Performance Monitoring: Active Session History at work

2012-06-27T10:45:00.002+03:00

New Feature Active Session History (ASH) in oracle 10g. That was one major improvement – together with the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) – of the 10g version. Way better than STATSPACK was before!

Imagine you are a DBA on a production system and get an emergency call like “The Database is dead slow!”. You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. with little effort from the command line like this :

-----------------------------------------
--
-- Top 10 CPU consumers in last 5 minutes
--
-----------------------------------------
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
--------------------------------------------
--
-- Top 10 waiting sessions in last 5 minutes
--
--------------------------------------------
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING' and sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
--------------------
--
-- Who is that SID?
--
--------------------
select serial#,username, osuser, machine, program, resource_consumer_group, client_info
from v$session where sid=SID;
-------------------------
--
-- What did that SID do?
--
-------------------------
select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=SID;
----------------------------------------------
--
-- Retrieve the SQL from the Library Cache:
--
----------------------------------------------
select sql_text from v$sql where sql_id='SQL_ID';

By Uwe Hesse ....

adrci: A survival guide for the DBA

2012-06-27T10:16:00.000+03:00

Starting with 11gR1, we have a new way to deal with Oracle Errors & Tracefiles: There is now a special command line utility dedicated for that purpose called adrci (Automatic Diagnostic Repository Command Interpreter). This posting is intended to show you the (in my view) essential commands, a DBA ought to know in order to use it. We will look at
1.Viewing the alert.log
2.The relation between incident & problem
3.Creation of Packages & ZIP files to send to Oracle Support
4.Managing, especially purging tracefiles

I will at first create a problem. Don’t do that with your Production Database! Especially: Never do DML on dictionary tables!
[oracle@uhesse ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:25:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter diagnostic
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest              string     /u01/app/oracle
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table t (n number);
Table created.
SQL> select object_id from user_objects;
OBJECT_ID
----------
     75719
SQL> connect / as sysdba
Connected.
SQL> update tab$ set cols=2 where obj#=75719;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> connect adam/adam
Connected.
SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2236
Session ID: 29 Serial number: 9
I flushed the Shared Pool to get the Data Dictionary Cache empty. Else the select may not crash the session as it did. Imagine the user calls me now on the phone. Our first idea as an experienced DBA: We look at the alert.log! Right so. Please notice that we now have two different kinds of the alert.log.

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

1. Viewing the alert.log

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert This version of the alert.log is accessed by adrci:
[oracle@uhesse ~]$ adrci
ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 10:20:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/tnslsnr/uhesse/listener
diag/rdbms/orcl/orcl
Please notice that we have different ADR Homes. In my case only two, because I am not using Grid Infrastructure on this Demo System, else there would be another one. I specify my Database Home first. Then I look at the alert.log. Good news if you are on Windows: Finally, you can tail -f your alert.log also
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail -f
2011-06-01 10:16:35.337000 +02:00
db_recovery_file_dest_size of 4032 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=21, OS id=2204
2011-06-01 10:18:42.668000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc (incident=6153):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:18:47.518000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601101847], requested by (instance=1, osid=2236), summary=[incident=6153].
2011-06-01 10:18:48.727000 +02:00
Sweep [inc][6153]: completed
Sweep [inc2][6153]: completed
2. The relation between Incident & Problem

You see the incident was recorded in the alert.log. And it tells you “Use ADRCI or Support Workbench to package the incident.” We will soon see how to do that. First I’d like to explain the relation between incident and problem: An incident is the concrete occurrence of a problem. In other words: The same problem may have multiple incidents. To show that, I will open another terminal and do again a select against the table t, while still tailing the alert log from the first session.

Second terminal:
[oracle@uhesse ~]$ sqlplus adam/adam
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:21:52 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t where n=42;
select * from t where n=42
                         *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2299
Session ID: 36 Serial number: 11
First terminal:
2011-06-01 10:21:31.367000 +02:00
Starting background process SMCO
SMCO started with pid=19, OS id=2268
2011-06-01 10:22:08.781000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc (incident=6201):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:22:11.135000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601102211], requested by (instance=1, osid=2299), summary=[incident=6201].
2011-06-01 10:22:13.370000 +02:00
Sweep [inc][6201]: completed
Sweep [inc2][6201]: completed
I have seen the second incident recorded. I exit out of the tail -f with CTRL+C and continue:
adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 7445 [qcstda()+702]                                     6201                 2011-06-01 10:22:08.924000 +02:00
1 rows fetched
So I have one problem with the ID 1 and the last incident occurred at 10:22. Are there more?
adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
6153                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:18:42.995000 +02:00
6201                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:22:08.924000 +02:00
2 rows fetched
I want to see some more detail about the incidents:
adrci> show incident -mode detail -p "incident_id=6201"
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   6201
   STATUS                        ready
   CREATE_TIME                   2011-06-01 10:22:08.924000 +02:00
   PROBLEM_ID                    1
   CLOSE_TIME
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    qcstda()+702
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x0
   ERROR_ARG4                    PC:0x90D891A
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6
   ERROR_ARG7
   ERROR_ARG8
   ERROR_ARG9
   ERROR_ARG10
   ERROR_ARG11
   ERROR_ARG12
   SIGNALLING_COMPONENT          SQL_Parser
   SIGNALLING_SUBCOMPONENT
   SUSPECT_COMPONENT
   SUSPECT_SUBCOMPONENT
   ECID
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [qcstda()+702]
   FIRST_INCIDENT                6153
   FIRSTINC_TIME                 2011-06-01 10:18:42.995000 +02:00
   LAST_INCIDENT                 6201
   LASTINC_TIME                  2011-06-01 10:22:08.924000 +02:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     25.3
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@uhesse (TNS V1-V3).2299_140262306875136
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1306916528)
   KEY_NAME                      SID
   KEY_VALUE                     36.11
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
1 rows fetched
I want to look at the incident tracefile mentioned above:
adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
----------------------------------------------------------
LEVEL PAYLOAD
----- ------------------------------------------------------------------------------------------------------------------------------------------------
Dump file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:      Linux
Node name:        uhesse
Release: 2.6.32-100.28.5.el6.x86_64
Version: #1 SMP Wed Feb 2 18:40:23 EST 2011
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 2299, image: oracle@uhesse (TNS V1-V3)
*** 2011-06-01 10:22:08.929
*** SESSION ID:(36.11) 2011-06-01 10:22:08.929
*** CLIENT ID:() 2011-06-01 10:22:08.929
*** SERVICE NAME:(SYS$USERS) 2011-06-01 10:22:08.929
*** MODULE NAME:(SQL*Plus) 2011-06-01 10:22:08.929
*** ACTION NAME:() 2011-06-01 10:22:08.929
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
1>     ***** Error Stack *****
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
1<     ***** Error Stack *****
1>     ***** Dump for incident 6201 (ORA 7445 [qcstda()+702]) *****
2>      ***** Beginning of Customized Incident Dump(s) *****
2>      ***** Beginning of Customized Incident Dump(s) *****
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000000 %rbx: 0x00007f915c77f0e0 %rcx: 0x0000000000000007
%rdx: 0x0000000000000000 %rdi: 0x00007f915c77be98 %rsi: 0x0000000000000000
%rsp: 0x00007fffc65178e0 %rbp: 0x00007fffc6517960 %r8: 0x0000000000000028
%r9: 0x0000000000002000 %r10: 0x00000000093849c0 %r11: 0x0000000000000168
%r12: 0x00007f915c77ade8 %r13: 0x000000008edbb178 %r14: 0x00007f915c777da0
%r15: 0x00007f915c77ae28 %rip: 0x00000000090d891a %efl: 0x0000000000010246
qcstda()+686 (0x90d890a) mov -0x40(%rbp),%rdi
qcstda()+690 (0x90d890e) mov %rdx,0x18(%rbx)
qcstda()+694 (0x90d8912) mov 0x60(%r15),%rsi
qcstda()+698 (0x90d8916) mov %ecx,0x8(%r15)
> qcstda()+702 (0x90d891a) mov %ecx,(%rsi)
qcstda()+704 (0x90d891c) mov 0x78(%rdi),%rdx
qcstda()+708 (0x90d8920) test %rdx,%rdx
qcstda()+711 (0x90d8923) jnz 0x90d8d03
qcstda()+717 (0x90d8929) mov -0x70(%rbp),%rdi
*** 2011-06-01 10:22:08.963
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
3>       ***** Current SQL Statement for this session (sql_id=8r222qucmawdt) *****
          select * from t where n=42
3<       ***** current_sql_statement ***** 3
3. Creation of Packages & ZIP files to send to Oracle Support

I may not be able to solve the problem myself. Oracle Support will help me with that one. I gather all the required information with a method called “Incident Packaging Service” (IPS):
adrci> ips create package problem 1 correlate all
Created package 2 based on problem id 1, correlation level all
This did not yet create a ZIP file and is therefore referred to as “Logical Package”. The ZIP file is generated from the Logical Package that was created:
adrci> ips generate package 2 in "/home/oracle"
Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, mode complete
4. Managing, especially purging of tracefiles

Now to the management of tracefiles. You may notice that 11g creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:
adrci> show tracefile -rt
01-JUN-11 10:31:48 diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
01-JUN-11 09:43:43 diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
01-JUN-11 09:22:13 diag/rdbms/orcl/orcl/trace/alert_orcl.log
01-JUN-11 09:22:11 diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
01-JUN-11 09:22:10 diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
01-JUN-11 09:22:10 diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
01-JUN-11 09:18:47 diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
01-JUN-11 09:18:47 diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
01-JUN-11 09:17:19 diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
01-JUN-11 09:16:44 diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
01-JUN-11 09:16:30 diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
01-JUN-11 09:16:19 diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
01-JUN-11 09:16:16 diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
01-JUN-11 09:16:14 diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
30-MAY-11 14:07:02 diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
30-MAY-11 11:15:30 diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
30-MAY-11 11:00:01 diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
30-MAY-11 10:56:58 diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
30-MAY-11 10:56:20 diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
30-MAY-11 10:56:06 diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
30-MAY-11 10:55:58 diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
30-MAY-11 10:55:55 diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
30-MAY-11 10:55:53 diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc
27-MAY-11 10:53:25 diag/rdbms/orcl/orcl/trace/orcl_mmon_8589.trc
27-MAY-11 10:17:05 diag/rdbms/orcl/orcl/trace/orcl_ora_11390.trc
27-MAY-11 09:26:41 diag/rdbms/orcl/orcl/trace/orcl_ora_10739.trc
27-MAY-11 09:23:53 diag/rdbms/orcl/orcl/trace/orcl_dbrm_8573.trc
27-MAY-11 09:22:58 diag/rdbms/orcl/orcl/trace/orcl_ora_8687.trc
27-MAY-11 09:22:54 diag/rdbms/orcl/orcl/trace/orcl_mman_8577.trc
27-MAY-11 09:22:50 diag/rdbms/orcl/orcl/trace/orcl_vktm_8565.trc
27-MAY-11 09:22:48 diag/rdbms/orcl/orcl/trace/orcl_ora_8516.trc
27-MAY-11 09:22:44 diag/rdbms/orcl/orcl/trace/orcl_ora_8515.trc
27-MAY-11 09:22:44 diag/rdbms/orcl/orcl/trace/orcl_vktm_8347.trc
27-MAY-11 09:21:24 diag/rdbms/orcl/orcl/trace/orcl_dbrm_8355.trc
27-MAY-11 09:20:29 diag/rdbms/orcl/orcl/trace/orcl_ora_8470.trc
27-MAY-11 09:20:28 diag/rdbms/orcl/orcl/trace/orcl_mmon_8371.trc
27-MAY-11 09:20:28 diag/rdbms/orcl/orcl/trace/orcl_ora_8381.trc
27-MAY-11 09:20:26 diag/rdbms/orcl/orcl/trace/orcl_mman_8359.trc
27-MAY-11 09:20:20 diag/rdbms/orcl/orcl/trace/orcl_ora_8299.trc
27-MAY-11 09:20:15 diag/rdbms/orcl/orcl/trace/orcl_ora_8297.trc
27-MAY-11 09:20:15 diag/rdbms/orcl/orcl/trace/orcl_vktm_8096.trc
27-MAY-11 09:20:07 diag/rdbms/orcl/orcl/trace/orcl_ora_8296.trc
27-MAY-11 09:19:42 diag/rdbms/orcl/orcl/trace/orcl_ora_8285.trc
27-MAY-11 09:19:33 diag/rdbms/orcl/orcl/trace/orcl_dm00_8271.trc
27-MAY-11 09:19:33 diag/rdbms/orcl/orcl/trace/orcl_dw00_8273.trc
27-MAY-11 09:19:11 diag/rdbms/orcl/orcl/trace/orcl_dbrm_8104.trc
27-MAY-11 09:18:53 diag/rdbms/orcl/orcl/trace/orcl_ora_8267.trc
27-MAY-11 09:18:33 diag/rdbms/orcl/orcl/trace/orcl_j001_8237.trc
27-MAY-11 09:18:26 diag/rdbms/orcl/orcl/trace/orcl_mmon_8219.trc
27-MAY-11 09:18:23 diag/rdbms/orcl/orcl/trace/orcl_ora_8231.trc
27-MAY-11 09:18:22 diag/rdbms/orcl/orcl/trace/orcl_cjq0_8229.trc
27-MAY-11 09:18:16 diag/rdbms/orcl/orcl/trace/orcl_ora_8131.trc
27-MAY-11 09:18:14 diag/rdbms/orcl/orcl/trace/orcl_m000_8223.trc
27-MAY-11 09:18:13 diag/rdbms/orcl/orcl/trace/orcl_mman_8108.trc
27-MAY-11 09:18:05 diag/rdbms/orcl/orcl/trace/orcl_ora_8048.trc
27-MAY-11 09:17:59 diag/rdbms/orcl/orcl/trace/orcl_vktm_7920.trc
27-MAY-11 09:17:59 diag/rdbms/orcl/orcl/trace/orcl_ora_8046.trc
27-MAY-11 09:17:00 diag/rdbms/orcl/orcl/trace/orcl_mman_7932.trc
27-MAY-11 09:16:56 diag/rdbms/orcl/orcl/trace/orcl_ora_7954.trc
27-MAY-11 09:16:51 diag/rdbms/orcl/orcl/trace/orcl_ora_7871.trc
I have already got some tracefiles. How long are they going to stay?
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           720                  8760                 2011-05-27 10:16:46.997118 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00
1 rows fetched
The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy with for example:
adrci> set control (SHORTP_POLICY = 360)
adrci> set control (LONGP_POLICY = 2190)
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           360                  2190                 2011-06-01 11:42:17.208064 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00
1 rows fetched
Also, we may want to purge tracefiles manually. Following command will manually purge all tracefiles older than 2 days (2880 minutes):
adrci> purge -age 2880 -type trace
adrci> show tracefile -rt
   01-JUN-11 10:46:54 diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
   01-JUN-11 09:43:43 diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
   01-JUN-11 09:22:13 diag/rdbms/orcl/orcl/trace/alert_orcl.log
   01-JUN-11 09:22:11 diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
   01-JUN-11 09:22:10 diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
   01-JUN-11 09:22:10 diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   01-JUN-11 09:18:47 diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
   01-JUN-11 09:18:47 diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
   01-JUN-11 09:17:19 diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
   01-JUN-11 09:16:44 diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
   01-JUN-11 09:16:30 diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
   01-JUN-11 09:16:19 diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
   01-JUN-11 09:16:16 diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
   01-JUN-11 09:16:14 diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
   30-MAY-11 14:07:02 diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
   30-MAY-11 11:15:30 diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
   30-MAY-11 11:00:01 diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
   30-MAY-11 10:56:58 diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
   30-MAY-11 10:56:20 diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
   30-MAY-11 10:56:06 diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
   30-MAY-11 10:55:58 diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
   30-MAY-11 10:55:55 diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
   30-MAY-11 10:55:53 diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc
Conclusion: With adrci, we have a new and efficient utility to deal with Oracle Errors – especially for collecting information to send them to Oracle Support. This functionality is called Incident Packaging Service. 11g is generating lots of tracefiles. We can control the purging policy of them with adrci. Finally, we can now tail -f our alert.log from any OS.

By Uwe Hesse

Listing invalid indexes in Oracle

2012-06-21T15:33:00.000+03:00

SELECTindex_name
FROM all_indexes

WHERE owner NOT IN ('SYS', 'SYSTEM')

AND status != 'VALID'

AND

(

status != 'N/A'

OR index_name IN

(

SELECT index_name

FROM all_ind_partitions

WHERE status != 'USABLE'

AND

(

status != 'N/A'

OR index_name IN

(

SELECT index_name

FROM all_ind_subpartitions

WHERE status != 'USABLE'

)

)

)

);

CHR() fuction in oracle

2012-06-21T10:23:00.001+03:00

Ascii Table

The following is a listing of Ascii values.

Dec	Hex	Oct	Char	Description
0	0	000		null
1	1	001		start of heading
2	2	002		start of text
3	3	003		end of text
4	4	004		end of transmission
5	5	005		enquiry
6	6	006		acknowledge
7	7	007		bell
8	8	010		backspace
9	9	011		horizontal tab
10	A	012		new line
11	B	013		vertical tab
12	C	014		new page
13	D	015		carriage return
14	E	016		shift out
15	F	017		shift in
16	10	020		data link escape
17	11	021		device control 1
18	12	022		device control 2
19	13	023		device control 3
20	14	024		device control 4
21	15	025		negative acknowledge
22	16	026		synchronous idle
23	17	027		end of trans. block
24	18	030		cancel
25	19	031		end of medium
26	1A	032		substitute
27	1B	033		escape
28	1C	034		file separator
29	1D	035		group separator
30	1E	036		record separator
31	1F	037		unit separator
32	20	040		space
33	21	041	!
34	22	042	"
35	23	043	#
36	24	044	$
37	25	045	%
38	26	046	&
39	27	047	'
40	28	050	(
41	29	051	)
42	2A	052	*
43	2B	053	+
44	2C	054	,
45	2D	055	-
46	2E	056	.
47	2F	057	/
48	30	060	0
49	31	061	1
50	32	062	2
51	33	063	3
52	34	064	4
53	35	065	5
54	36	066	6
55	37	067	7
56	38	070	8
57	39	071	9
58	3A	072	:
59	3B	073	;
60	3C	074	<
61	3D	075	=
62	3E	076	>
63	3F	077	?

100

101

102

103

104

105

106

107

110

111

112

113

114

115

116

117

120

121

122

123

124

125

126

127

130

131

132

133

[

134

135

]

136

137

140

141

142

143

100

144

101

145

102

146

103

147

104

150

105

151

106

152

107

153

108

154

109

155

110

156

111

157

112

160

113

161

114

162

115

163

116

164

117

165

118

166

119

167

120

170

121

171

122

172

123

173

{

124

174

125

175

}

126

176

127

177

DEL

ORA-01591: lock held by in-doubt distributed transaction string

2012-06-03T10:25:00.004+03:00

The first thing to do is to check the result of these queries:
Check the following table:

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

From the above queries keep all the value of LOCAL_TRAN_ID in each table and try to force commit or rollback.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
EXEC DBMS_TRANSACTION.rollback_force('LOCAL_TRAN_ID')
--this changed state prepared to forced_rollback after that
COMMIT ;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');
COMMIT;

Repeat each steps for each LOCAL_TRAN_ID, turn on recovery for distributed transaction
crosscheck these tables (must be empty):

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

How to count number of files in a directory in Linux

2012-05-21T16:57:00.004+03:00

$ ls -1 targetdir | wc -l

Listing invalid indexes in Oracle

2012-05-19T02:26:00.001+03:00

SELECT TABLE_OWNER,TABLE_NAME,OWNER,INDEX_NAME,INDEX_TYPE,STATUS,
                TABLESPACE_NAME
FROM all_indexes WHERE owner NOT IN ('SYS', 'SYSTEM')
AND status != 'VALID'
AND (status != 'N/A'
              OR index_name IN(SELECT index_name
                                              FROM all_ind_partitions
                                              WHERE status != 'USABLE'
                                              AND(status != 'N/A' OR index_name IN ( SELECT index_name
                                                                                                          FROM all_ind_subpartitions
                                                                                                          WHERE status != 'USABLE'   ))));

To see the structure of table and index in oracle

2012-05-19T01:42:00.000+03:00

select
dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA_NAME')
from dual;

select
dbms_metadata.get_ddl(INDEX,'INDEX_NAME','SCHEMA_NAME')
from dual;

WORKLOAD REPOSITORY report for database

2012-05-16T15:35:00.001+03:00

use below query:

select output from table( dbms_workload_repository.awr_report_html
((select dbid from V$DATABASE),
(select INSTANCE_NUMBER from V$INSTANCE),
(select max(snap_id)-10 from dba_hist_snapshot),
(select max(snap_id) from dba_hist_snapshot),
0))

and after get result save as file.htm

Automatic Workload Repository (AWR) in Oracle Database 10g

2012-05-13T14:14:00.000+03:00

Automatic Workload Repository (AWR) in Oracle Database 10g
Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
" AWR Features
" Snapshots
" Baselines
" Workload Repository Views
" Workload Repository Reports
" Enterprise Manager
Related articles.
" AWR Baseline Enhancements in Oracle Database 11g Release 1
AWR Features
The AWR is used to collect performance statistics including:
" Wait events used to identify performance problems.
" Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
" Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
" Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
" Object usage statistics.
" Resource intensive SQL statements.
The repository is a source of information for several other Oracle 10g features including:
" Automatic Database Diagnostic Monitor
" SQL Tuning Advisor
" Undo Advisor
" Segment Advisor
Snapshots
By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval => 30);          -- Minutes. Current value retained if NULL.
END;
/
The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.
Extra snapshots can be taken and existing snapshots can be removed, as shown below.
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id => 22,
    high_snap_id => 32);
END;
/
Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.
Baselines
A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210,
    end_snap_id   => 220,
    baseline_name => 'batch baseline');
END;
/
The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/
Baseline information can be queried from the DBA_HIST_BASELINE view.
Workload Repository Views
The following workload repository views are available:
" V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
" V$METRIC - Displays metric information.
" V$METRICNAME - Displays the metrics associated with each metric group.
" V$METRIC_HISTORY - Displays historical metrics.
" V$METRICGROUP - Displays all metrics groups.
" DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
" DBA_HIST_BASELINE - Displays baseline information.
" DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
" DBA_HIST_SNAPSHOT - Displays snapshot information.
" DBA_HIST_SQL_PLAN - Displays SQL execution plans.
" DBA_HIST_WR_CONTROL - Displays AWR settings.
Workload Repository Reports
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.
An example of a HTML AWR report can be seen here.
Enterprise Manager
The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Crontab Format

2012-01-30T09:58:00.001+03:00

* * * * * *
|   | |  | | |
|   | | | | +-- Year                      (range: 1900-3000)
|    | | | +---- Day of the Week   (range: 1-7, 1 standing for Monday)
|   | | +------ Month of the Year (range: 1-12)
|   | +-------- Day of the Month (range: 1-31)
| +----------- Hour                    (range: 0-23)
+-------------- Minute                 (range: 0-59)

Find duplicate rows in a table

2012-01-10T10:29:00.002+03:00

select city_name
from areas
group by city_name
having count(*) > 1

Troubleshoot invalid objects in Oracle

2011-12-23T18:05:00.003+03:00

Most of the procedural objects like procedures, functions, triggers, objects refer to data objects, such as table.

-If the code refers to a column, the column must exist or the code will not compile.
-If any of the data objects to which a procedural object refers change after the procedural object has been compiled, then the procedure will be marked INVALID.
-Procedural objects may also be invalid for more mundane reasons: perhaps the
programmer made a simple syntactical mistake.

The same situation can occur with views. When created they may be fine, but they will be invalidated if the tables on which they are based have their definitions changed.

Identifying Invalid Objects
--------------------------------
To identify all of the invalid objects , run the following query,

SQL> select owner,object_name,object_type from dba_objects where
status='INVALID';

How to Handle Invalid Objects:
-------------------------------
1)Compile the invalid objects:
-------------------------------
The first steps is to compile the invalid objects. Though, the first time an invalid object is accessed, Oracle will attempt to compile it automatically, but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, he can try to fix it before a user notices.

2)If compilation is ok then there is no problem. But if it fails then check the object and object type. Suppose if the object type is procedure then you can use SHOW ERRORS to identify the the errors.

3)If the object type is view then SHOW ERRORS will not work then try to see the creation script of the view.

SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='YOUR VIEW';

Generally you can use DBA_DEPENDENCIES object and query column REFERENCED_OWNER and REFERENCED_NAME.

4)There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,
SQL> @?/rdbms/admin/utlrp

Workaround Example:
---------------------
1)Create one table, one invalid view and one valid view:
----------------------------------------------------------

SQL> create table first_table ( col1 number, col2 number);
Table created.

SQL> create force view first_view as select col3 from first_table;
Warning: View created with compilation errors.

SQL> create view second_view as select col2 from first_table;
View created.

2)Check Status:
----------------
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID

3)Drop Column col2 from Table and Check Status:
-----------------------------------------------
SQL> alter table first_table drop column col2;
Table altered.

SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID

4)Add column col3 and Check Status:
--------------------------------------
SQL> alter table FIRST_TABLE add (col3 number);
Table altered.

SQL> alter view FIRST_VIEW compile;
View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW INVALID

5)Troubleshoot Second View:
-----------------------------
SQL> select text from user_views where view_name='SECOND_VIEW';
TEXT
--------------------------------------------------------------------------------
select col2 from first_table

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
COL2 NON-EXISTENT
COL2 NON-EXISTENT
FIRST_TABLE TABLE

So col2 is non-existent or missing.

SQL> alter table FIRST_TABLE add col2 number;
Table altered.
SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID

Interestingly when I add col2 FIRST_VIEW becomes invalid.

And also when deleting a referenced column status of dependent objects become invalid. But when we add the status does not changes.

6)Now compile on second_view or any access will marked the dictionary ok.

SQL> select * from second_view;
no rows selected

SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID

But First_view become INVALID.

7)Fix Error:
---------------
SQL> alter view FIRST_VIEW compile;

View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW VALID

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
FIRST_TABLE TABLE

Interval partitioning

2011-12-17T12:32:00.001+03:00

Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
The following restrictions apply:

You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is NOT supported for index-organized tables.
You can NOT create a domain index on an interval-partitioned table.

Test case

Let start it from here:

SQL> conn hr/hr
Connected.

Here we create a table with range partition on the SALARY column.

SQL> create table test
 2      (sno number(6),
 3      last_name varchar2(30),
 4      salary number(6))
 5      partition by range(salary)
 6     (
 7  partition p1 values less than (5000),
 8  partition p2 values less than (10000),
 9  partition p3 values less than (15000),
10  partition p4 values less than (20000));

Table created.

Let insert some records into test table.

SQL> insert into test
    select employee_id,last_name,salary from employees
    where last_name not like '%K%';

101 rows created.

SQL> select table_name,partition_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
TEST                           P1
TEST                           P2
TEST                           P4
TEST                           P3

SQL> exec dbms_stats.gather_table_stats('HR','TEST');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
    from user_tab_partitions
    where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                            NUM_ROWS
------------------------------ ------------------------------      ----------
TEST                P1                                     48
TEST                P2                                     37
TEST                           P4                                      1
TEST                           P3                                     15

As we know the data which we inserted obeyed all rules defined for partitions. Let see what happens:

SQL> insert into test
         values
         (1,'Michel',25000);
insert into test *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Note the 14400 error.
ORA-14400:
inserted partition key does not map to any partition
Cause:
An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action:
Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification.
Now in 11g, Oracle introduced new partition type called INTERVAL PARTITIONING. So now I am going to check what the benefits of interval partitioning are.
First I will drop mine existing TEST table.

SQL> drop table test purge;
Table dropped.

SQL> create table test
 2          (sno number(6),
 3          last_name varchar2(30),
 4          salary number(6))
 5          partition by range(salary)
 6          Interval  (5000)
 7         (
 8      partition p1 values less than (5000),
 9      partition p2 values less than (10000),
10     partition p3 values less than (15000),
11     partition p4 values less than (20000));

Table created.

Let check the partition names.

SQL> select table_name,partition_name
   From user_tab_partitions
   Where table_name='TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                             P4
TEST                             P1
TEST                             P2
TEST                             P3

Here we can see we created four partition during table creation now how oracle will use this INTERVAL. Let's try to insert records into test table.

SQL> insert into test
 2       select employee_id,last_name,salary from employees
 3       where last_name not like '%K%';
101 rows created.

I inserted the data which already obeyed the existing partitions limit.

SQL> exec dbms_stats.gather_table_stats('HR','TEST');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
 2       from user_tab_partitions
 3       where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ -----------------                ----------
TEST                             P4                               1
TEST                                P1                              48
TEST                                P2                              37
TEST                                P3                              15

Now I’m going to insert the data which is not mentioned for any partition.

SQL> insert into test
 2  values
 3  (1,'Michel',25000);
1 row created.

SQL> insert into test
 2  values
 3  (2,'Michel',30000);
1 row created.

SQL> insert into test
 2  values
 3  (3,'Michel',35000);
1 row created.

SQL> insert into test
 2  values
 3  (4,'Michel',40000);
1 row created.

You can see that this time it didn’t generate the ORA_14400 errors. Let see what oracle did to insert the data over the partitions limit.
Here we go.

SQL> select table_name,partition_name
 2  from user_tab_partitions
 3  where table_name='TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                              P4
TEST                                 SYS_P41
TEST                               SYS_P42
TEST                             P1
TEST                             P2
TEST                             P3
TEST                                    SYS_P43
TEST                               SYS_P44
8 rows selected.

I created 4 partitions but now we can see there are total 8 partitions ,four extra partitions with system generated names. Now its clear like water what INTERVAL did, as I specify the INTERVAL limit of (5000) and I inserted 4 records with the interval of 5000 each so oracle created new system generated partition for each that partition which was in the interval of 5000.

SQL> select table_name,partition_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST' order by partition_name;

TABLE_NAME                     PARTITION_NAME                            NUM_ROWS
------------------------------ ------------------------------           ----------
TEST                             P1                                  48
TEST                             P2                                  37
TEST                       P3                                  15
TEST                            P4                                   1
TEST                            SYS_P41                              2
TEST                             SYS_P42                              1
TEST                                    SYS_P43                              1
TEST                                   SYS_P44                              1

8 rows selected.

SQL> select salary from test where salary>20000;

   SALARY
----------
    25000
    25000
    30000
    35000
    40000

We can easily check that salary column have 2 values for 25000 that’s why we have num_rows 2 for SYS_P41

BY FAQ

How to check how much disk space is allocated/ in-use by an ASM instance?

2011-10-31T11:13:00.002+03:00

SQL> COL % FORMAT 99.0
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;
NAME                              FREE_MB   TOTAL_MB     %
------------------------------ ---------- ---------- -----
DATA                               917104    1482145  61.9
RECOVER                             17387      17437  99.7

From Oracle 10g Release 2, one can also use the asmcmd command line utility:

ASMCMD> du
Used_MB      Mirror_used_MB
   1523                1523

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576     11264     9885                0            9885              0  DISKGROUP1/
MOUNTED  EXTERN  N      N         512   4096  1048576     10240     9906                0            9906              0  FLASH/

Backup - DataGuard

2011-10-30T16:50:00.000+03:00

Backup - DataGuard
•Startup commands
•To remove a delay from a standby
•Cancel managed recovery
•Register a missing log file
•If FAL doesn't work and it says the log is already registered
•Check which logs are missing
•Disable/Enable archive log destinations
•Turn on fal tracing on the primary db
•Stop the Data Guard broker
•Show the current instance role
•Logical standby apply stop/start
•See how up to date a physical standby is
•Display info about all log destinations
•Display log destinations options
•List any standby redo logs

Startup commands
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;

To remove a delay from a standby
alter database recover managed standby database cancel;
alter database recover managed standby database nodelay disconnect;

Cancel managed recovery
alter database recover managed standby database cancel;

Register a missing log file
alter database register physical logfile '<fullpath/filename>';

If FAL doesn't work and it says the log is already registered
alter database register or replace physical logfile '<fullpath/filename>';

If that doesn't work, try this...
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;

wait for the recovery to finish - then cancel
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;

Check which logs are missing
Run this on the standby...
select local.thread#
,      local.sequence# from
       (select thread#
       ,       sequence#
       from    v$archived_log
       where dest_id=1) local
where local.sequence# not in
       (select sequence#
       from v$archived_log
       where dest_id=2 and
       thread# = local.thread#)
/

Disable/Enable archive log destinations
alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';

Turn on fal tracing on the primary db
alter system set LOG_ARCHIVE_TRACE = 128;

Stop the Data Guard broker
alter system set dg_broker_start=false
/

Show the current instance role
select database_role
from v$database
/

Logical standby apply stop/start
Stop...
alter database stop logical standby apply;

Start...
alter database start logical standby apply;

See how up to date a physical standby is
Run this on the primary
set numwidth 15
select max(sequence#) current_seq
from v$log
/

Then run this on the standby
set numwidth 15
select max(applied_seq#) last_seq
from v$archive_dest_status
/

Display info about all log destinations
To be run on the primary
set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest  ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by
ds.dest_id
/

Display log destinations options
To be run on the primary
set numwidth 8 lines 100
column id format 99
select dest_id id
, archiver
, transmit_mode
, affirm
, async_blocks async
, net_timeout net_time
, delay_mins delay
, reopen_secs reopen
, register,binding
from v$archive_dest
order by
dest_id
/

List any standby redo logs
set lines 100 pages 999
col member format a70
select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st
, v$logfile lf
where st.group# = lf.group#
/

Andrew Barry

The stapes to restart oracle database RAC with ASM

2011-10-22T18:16:00.000+03:00

====Stop database, asm, listener, nodeapps finally crs===
1. STOP DATABASE
srvctl stop database -d DB_NAME
2. STOP ASM
srvctl stop asm -n NODE_NAME1
srvctl stop asm -n NODE_NAME2

3. STOP LISTENER
srvctl stop listener -n NODE_NAME1
srvctl stop listener -n NODE_NAME2

4. STOP NODEAPPS
srvctl stop nodeapps -n NODE_NAME1
srvctl stop nodeapps -n NODE_NAME2
5. STOP CRS
crsctl stop crs -- (on NODE_NAME1)
crsctl stop crs -- (on NODE_NAME2)

====Start crs, nodeapps, listner, asm and finally database===
1. START CRS
crsctl start crs -- (on NODE_NAME1)
crsctl start crs -- (on NODE_NAME2)
RESET to trace level 0
./crsctl debug log crs allcomp:0
2. START NODEAPPS
srvctl start nodeapps -n NODE_NAME1
srvctl start nodeapps -n NODE_NAME2

3. START LISTENER
srvctl start listener -n NODE_NAME1
srvctl start listener -n NODE_NAME2
4. STOP ASM
srvctl start asm -n NODE_NAME1
srvctl start asm -n NODE_NAME2
date
export SRVM_TRACE=y
env
srvctl start instance -d DB_NAME -i DB_NAME1

5. START DATABASE
srvctl start database -d DB_NAME

kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
Status of all instances and services

[p570db3][grid]/home/grid> srvctl status database -d bossmain
Instance bossmain1 is running on node p570db3
Instance bossmain2 is running on node p570db4
kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
srvctl status nodeapps -n p570db3
-n <node_name> option has been deprecated.
VIP p570db3-vip is enabled
VIP p570db3-vip is running on node: p570db3
Network is enabled
Network is running on node: p570db3
GSD is disabled
GSD is not running on node: p570db3
ONS is enabled
ONS daemon is running on node: p570db3
eONS is enabled
eONS daemon is not running on node: p570db3
kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
srvctl status asm -n p570db3
ASM is running on p570db3

Patch 9654983 - 11.2.0.1.2 Patch Set Update

2011-10-19T14:08:00.001+03:00

Oracle® Database

Patch 9654983 - 11.2.0.1.2 Patch Set Update

Released: July 13, 2010

This document is accurate at the time of release. For any changes and additional information regarding PSU 11.2.0.1.2, see these related documents that are available at My Oracle Support (http://support.oracle.com/):

Note 854428.1 Patch Set Updates for Oracle Products
Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues

This document includes the following sections:

1 Patch Information

Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.
PSU 11.2.0.1.2 includes the fixes listed in Section 5, "Bugs Fixed by This Patch".
Table 1 describes installation types and security content. For each installation type, it indicates the most recent PSU patch to include new security fixes that are pertinent to that installation type. If there are no security fixes to be applied to an installation type, then "None" is indicated. If a specific PSU is listed, then apply that or any later PSU patch to be current with security fixes.

Table 1 Installation Types and Security Content

Installation Type	Latest PSU with Security Fixes
Server homes	PSU 11.2.0.1.2
Client-Only Installations	None
Instant Client Installations	None (The Instant Client installation is not the same as the client-only Installation. For additional information about Instant Client installations, see Oracle Database Concepts.)

2 Patch Installation and Deinstallation

This section includes the following sections:

2.1 Platforms for PSU 11.2.0.1.2

For a list of platforms that are supported in this Patch Set Update, see My Oracle Support Note 1060989.1 Critical Patch Update July 2010 Patch Availability Document for Oracle Products.

2.2 OPatch Utility Information

You must use the OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.
For information about OPatch documentation, including any known issues, see My Oracle Support Note 293369.1 OPatch documentation list.

2.3 Patch Installation

These instructions are for all Oracle Database installations.

2.3.1 Patch Pre-Installation Instructions

Before you install PSU 11.2.0.1.2, perform the following actions to check the environment and to detect and resolve any one-off patch conflicts.

2.3.1.1 Environments with ASM

If you are installing the PSU to an environment that has Automatic Storage Management (ASM), note the following:

For Linux x86 and Linux x86-64 platforms, install either (A) the bug fix for 8898852 and the Database PSU patch 9654983, or (B) the Grid Infrastructure PSU patch 9343627.
For all other platforms, no action is required. The fix for 8898852 was included in the base 11.2.0.1.0 release.

2.3.1.2 Environment Checks

Ensure that the $PATH definition has the following executables: make, ar, ld, and nm.
The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
```
export PATH=$PATH:/usr/ccs/bin
```

2.3.1.3 One-off Patch Conflict Detection and Resolution

For an introduction to the PSU one-off patch concepts, see "Patch Set Updates Patch Conflict Resolution" in My Oracle Support Note 854428.1 Patch Set Updates for Oracle Products.
The fastest and easiest way to determine whether you have one-off patches in the Oracle home that conflict with the PSU, and to get the necessary conflict resolution patches, is to use the Patch Recommendations and Patch Plans features on the Patches & Updates tab in My Oracle Support. These features work in conjunction with the My Oracle Support Configuration Manager. Recorded training sessions on these features can be found in Note 603505.1.
However, if you are not using My Oracle Support Patch Plans, follow these steps:

Determine whether any currently installed one-off patches conflict with the PSU patch as follows:

unzip p9654983_11201_<platform>.zip
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9654983

The report will indicate the patches that conflict with PSU 9654983 and the patches for which PSU 9654983 is a superset.
Note that Oracle proactively provides PSU 11.2.0.1.2 one-off patches for common conflicts.
Use My Oracle Support Note 1061295.1 Patch Set Updates - One-off Patch Conflict Resolution to determine, for each conflicting patch, whether a conflict resolution patch is already available, and if you need to request a new conflict resolution patch or if the conflict may be ignored.
When all the one-off patches that you have requested are available at My Oracle Support, proceed with Section 2.3.2, "Patch Installation Instructions".

2.3.2 Patch Installation Instructions

Follow these steps:

If you are using a Data Guard Physical Standby database, you must first install this patch on the primary database before installing the patch on the physical standby database. It is not supported to install this patch on the physical standby database before installing the patch on the primary database. For more information, see My Oracle Support Note 278641.1.
Do one of the following, depending on whether this is a RAC environment:
- If this is a RAC environment, choose one of the patch installation methods provided by OPatch (rolling, all node, or minimum downtime), and shutdown instances and listeners as appropriate for the installation method selected.
  This PSU patch is rolling RAC installable. Refer to My Oracle Support Note 244241.1 Rolling Patch - OPatch Support for RAC.
- If this is not a RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide.
Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
```
unzip p9654983_11201_<platform>.zip
cd 9654983
opatch apply
```
If there are errors, refer to Section 3, "Known Issues".

2.3.3 Patch Post-Installation Instructions

After installing the patch, perform the following actions:

Apply conflict resolution patches as explained in Section 2.3.3.1.
Load modified SQL files into the database, as explained in Section 2.3.3.2.

2.3.3.1 Applying Conflict Resolution Patches

Apply the patch conflict resolution one-off patches that were determined to be needed when you performed the steps in Section 2.3.1.3, "One-off Patch Conflict Detection and Resolution".

2.3.3.2 Loading Modified SQL Files into the Database

The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
```
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT
```
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors:
```
catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
```
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, "Known Issues".

2.3.4 Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of PSU 11.2.0.1.2 in the Oracle Home

These instructions are for a database that is created or upgraded after the installation of PSU 11.2.0.1.2.
You must execute the steps in Section 2.3.3.2, "Loading Modified SQL Files into the Database" for any new database only if it was created by any of the following methods:

Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)
Using a script that was created by DBCA that creates a database from a sample database

2.4 Patch Deinstallation

These instructions apply if you need to deinstall the patch.

2.4.1 Patch Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 2.3.3.2, "Loading Modified SQL Files into the Database" against the database before deinstalling the PSU.
Shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide.
Run the OPatch utility specifying the rollback argument as follows.
```
opatch rollback -id 9654983
```
If there are errors, refer to Section 3, "Known Issues".

2.4.2 Patch Post-Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

Start all database instances running from the Oracle home. (For more information, see Oracle Database Administrator's Guide.)
For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
```
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
SQL> QUIT
```
In a RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.
Check the log file for any errors. The log file is found in $ORACLE_HOME/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, "Known Issues".

2.4.3 Patch Deinstallation Instructions for a RAC Environment

Follow these steps for each node in the cluster, one node at a time:

Shut down the instance on the node.
Run the OPatch utility specifying the rollback argument as follows.
```
opatch rollback -id 9654983
```
If there are errors, refer to Section 3, "Known Issues".
Start the instance on the node as follows:
```
srvctl start instance
```

2.4.4 Patch Post-Deinstallation Instructions for a RAC Environment

Follow the instructions listed in Section Section 2.4.2, "Patch Post-Deinstallation Instructions for a Non-RAC Environment" only on the node for which the steps in Section 2.3.3.2, "Loading Modified SQL Files into the Database" were executed during the patch application.
All other instances can be started and accessed as usual while you are executing the deinstallation steps.

3 Known Issues

For information about OPatch issues, see My Oracle Support Note 293369.1 OPatch documentation list.
For issues documented after the release of this PSU, see My Oracle Support Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues.
Other known issues are as follows.

Issue 1

The following ignorable errors may be encountered while running the catbundle.sql script or its rollback script:

ORA-29809: cannot drop an operator with dependent objects
ORA-29931: specified association does not exist
ORA-29830: operator does not exist
ORA-00942: table or view does not exist
ORA-00955: name is already used by an existing object
ORA-01430: column being added already exists in table
ORA-01432: public synonym to be dropped does not exist
ORA-01434: private synonym to be dropped does not exist
ORA-01435: user does not exist
ORA-01917: user or role 'XDB' does not exist
ORA-01920: user name '<user-name>' conflicts with another user or role name
ORA-01921: role name '<role name>' conflicts with another user or role name
ORA-01952: system privileges not granted to 'WKSYS'
ORA-02303: cannot drop or replace a type with type or table dependents
ORA-02443: Cannot drop constraint - nonexistent constraint
ORA-04043: object <object-name> does not exist
ORA-29832: cannot drop or replace an indextype with dependent indexes
ORA-29844: duplicate operator name specified 
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored.
ORA-01927: cannot REVOKE privileges you did not grant

4 References

The following documents are references for this patch.
Note 293369.1 OPatch documentation list
Note 360870.1 Impact of Java Security Vulnerabilities on Oracle Products
Note 468959.1 Enterprise Manager Grid Control Known Issues
Note 9352237.8 Bug 9352237 - 11.2.0.1.1 Patch Set Update (PSU)

5 Bugs Fixed by This Patch

This patch includes the following bug fixes.

5.1 CPU Molecules

CPU molecules in PSU 11.2.0.1.2:
PSU 11.2.0.1.2 contains the following new CPU molecules:
9676419 - DB-11.2.0.1-MOLECULE-004-CPUJUL2010
9676420 - DB-11.2.0.1-MOLECULE-005-CPUJUL2010

5.2 Bug Fixes

PSU 11.2.0.1.2 contains the following new fixes:

Automatic Storage Management

8755082 - ORA-00600: [KCFIS_TRANSLATE4:VOLUME LOOKUP], [2], [WRONG DEVICE NAME], [], [], [
8890026 - ASM PARTNERING CREATES IMBALANCED PARTNERSHIPS
9170608 - STBH:DD BLOCKS PINNED FOR QUERIES THAT DO NOT REQUEST USED SPACE
9363145 - STBH:DB INSTANCES TERMINATED BY ASMB DUE TO ORA-00600 [KFDSKALLOC0]

Buffer Cache

8330783 - HANGING DB WITH "CACHE BUFFER CHAINS" AND "BUFFER DEADLOCK" WAITS DURING INSERT
8822531 - TAKING AWR SNAP HANGS

Data Guard Broker

8918433 - UNPERSISTED FSFO STATE BITS CAN GET PERSISTED
9363384 - PHYSICAL STANDBY SERVICES NOT STARTED AFTER CONVERT FROM SNAPSHOT
9467635 - BROKER'S METADATA FILE UPGRADE TO 11.2 IS BROKEN
9467727 - GETSTATUS DOC YIELDS INCORRECT RESULT IF DBRESOURCE_ID PROP VALUE IS USED

Data Guard Logical

8774868 - LGSBFSFO: ORA-600 [3020], [3], [138] RAISED IN RECOVERY SLAVE
8822832 - V$ARCHIVE_DEST_STATUS HAS INCORRECT VALUE FOR APPLIED_SEQ#

DataGuard Redo Transport

8872096 - ARCHIVING FORCED DURING CLOSE WHEN NO STANDBY IS PRESENT
9399090 - STBH: CONSTANT/HIGH FREQUENT LOG SWITCHES ON BEEHIVE DATABASE IN THE LAST 3 DAYS

Shared Cursors

8865718 - RECURSIVE CURSORS CONTAINING "AS OF SNAPSHOT" CLAUSE ARE NOT SHARED
8981059 - HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH
9010222 - APPS ST 11G ORA-00600 [KKSFBC-REPARSE-INFINITE-LOOP]
9067282 - TB:SH:ORA-00600:[KKSFBC-WRONG-KKSCSFLGS] WHILE RUNNING TPC-H

DML Drivers

9255542 - ARRAY INSERT TO PARTITIONED TABLE LOOSES ROWS DUE TO CONCURRENT DDL (ORA-14403)
9488887 - FORIEGN KEY VIOLATION WITH ARRAY-INSERT AND ONLINE IDX REBUILD AFTER BUG-9255542

Flashback Database

8834425 - ORA-240 IN RVWR PROCESS CAUSING 5MIN TRANSACTIONAL HANG

PLSQL

9210925 - AFTER MANUAL UPGRADE TO 11.1.0.7 PL/SQL CALLS INCORRECT FUNCTION

Automatic Memory Management

8505803 - PRE_PAGE_SGA RESULTS IN EXCESSIVE PAGE TABLE SIZE WHEN USING MEMORY_TARGET [AMM]

Partitioning

9165206 - PARTITIONING ORA-600 [KKPOLLS1] / [KKDOILSF1] - DURING PARTITION MAINTANANCE

Real Application Cluster

8875671 - LX64: ORA-600 ARGS [KJPNP_CHK:!MASTER_READY],
9093300 - LOTS OF REPEATED KJXOCDR: DROP DUPLICATE OPEN MESSAGE IN LMD TRACE

Row Access Method

8544696 - TABLE GROWTH - BLOCKS ARE NOT REUSED

Streams

8650719 - DOWNSTREAM CAPTURE ABORTS WITH ORA-26766

Secure Files

8856478 - RAM SECUREFILE PERF DEGRADATION WITH SF COMPRESSION ON SMALL LOBS DURING ATB MOVE
9272086 - STBH: DATA PUMP WRITER SEEMS TO BE WAITING ON WAIT FOR UNREAD MESSAGE ON BROADCA

DB Recovery

8909984 - APPSST GSI 11G: GAPS IN AWR SNAPSHOTS
9068088 - MEDIA RECOVERY WAS HUNG ON STANDBY
9145541 - ORA-600 [25027] / ORA-600 [4097] FOR ACTIVE TX IN A PLUGGED TABLESPACE
9167285 - PKT-BUGOLTP: ORA-07445: [KCRALC()+87]

Space Management

7519406 - 'J000' TRACE FILE REGARDING GATHER_STATS_JOB INTERMITTENTLY SINCE 10.2.0.4
8815639 - [11GR2-LNX-090813] MULTIPLE INSERT CAUSE DATA ALLOCATION ABOVE HHWM
9216806 - HIGH "ENQ: TS - CONTENTION" FOR TEMPORARY SEGMENT WHILE SQLLDR DIRECT PATH LOAD
9242411 - STRESS-BIGBH: LOTS OF OR-3113S IN BIGBH STRESS TEST
9461782 - ORA-7445 [KTSLF_SUMFSG()+54] [SIGSEGV] AND KTSLFSUM_CFS ON CALL STACK

Compression

9011088 - [11GR2]ADDING COLUMN TO COMPRESSED TABLE, DATA LOSS OCCURED.
9275072 - APPSST GSI 11G : BUFFER BUSY WAITS INSERTING INTO TABLES
9341448 - APPSST GSI 11G : BUFFER BUSY WAITS AND LATCH: CACHE BUFFERS WAITS WHEN INSERTING
9637033 - ORA-07445[KDR9IR2RST0] INSERT AS SELECT IN A COMPRESSED TABLE WITH > 255 COLUMNS

SQL Execution

8664189 - ORA-00600 [KDISS_UNCOMPRESS: BUFFER LENGTH]
9119194 - PSRC: DISTRIBUTED QUERY SLOWER IN 10.2.0.4 COMPARED TO 10.2.0.3

Transaction Management

8268775 - PERF: HIGH US ENQUEUE CONTENTION DURING A LOGIN STORM OR SESSION FAILOVER
8803762 - ORA-00600 [KDSGRP1] BLOCK CORRUPTION ON 11G DATABASE UPGRADE

Memory Management

8431487 - INSTANCE CRASH ORA-07445 [KGGHSTFEL()+192] ORA-07445[KGGHSTMAP()+241]

Message

9713537 - ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-00600
9714832 - ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-07445

6 Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/support/contact.html or visit http://www.oracle.com/accessibility/support.html if you are hearing impaired.

Patch 9209238 - 11.2.0.1.1 Patch Set Update Release 11.2.0.1.1 for UNIX
Copyright © 2006, 2010, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Al Saidi

2011-10-18T09:11:00.000+03:00