Quick configuration of TSM Data Protection for Oracle on an AIX

1. Prerequests
1. TDP for  Oracle  must  be  installed before
2. Policy,domain,backup copy,storage pools  ...etc  must be defined  on TSM server  side
3. TDP for  Oracle  node must  be  registred  before on server  side ( e.x node name   =dbgenomt e.x domain name  = DM_TEST_ORACLE  )
   
   REG NODE dbgenomt  password_1234  dom=DM_TEST_ORACLE  backdel=yes
 
2. Link the Oracle target database instance with Data Protection for Oracle by performing the following steps: (with  oracle user  )
1. Change the LIBPATH environment variable to include $ORACLE_HOME/lib before /usr/lib. If you have a LD_LIBRARY_PATH, ensure that this has the $ORACLE_HOME/lib before /usr/lib.
2. Ensure the SBT_LIBRARY parameter is not set
3. Shut down all Oracle instances that use $ORACLE_HOME
4. Link  TDP library file directly to the Oracle directory
   cd /usr/tivoli/tsm/client/oracle/bin64/
   ln -s /usr/tivoli/tsm/client/oracle/bin64/libobk64.a $ORACLE_HOME/lib/libobk.a
5. Start the Oracle instances
3. Configure  tdpo.opt   file  under  /usr/tivoli/tsm/client/oracle/bin64
1. copy  tdpo.opt.smp64  as tdpo.opt
   
    
   cd /usr/tivoli/tsm/client/oracle/bin64
   cp  tdpo.opt.smp64   tdpo.opt
    
2. change  and  open * character t for   following lines 
   
   dsmi_orc_config  /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
   dsmi_log              
   
4.   create  dsm.opt  in the same  directory  that inclused  node name  (e.x  dbgenomt )
   

   cd /usr/tivoli/tsm/client/oracle/bin64
   echo   "SErvername dbgenomt"  >dsm.opt 
5. dsm.sys file 
1. create  a  symbolic link  in order  to  have only one copy  of  dsm.sys file
   ln -s /usr/tivoli/tsm/client/ba/bin64/dsm.sys /usr/tivoli/tsm/client/api/bin64/dsm.sys
2. Edit the dsm.sys file to include another server stanza with the following options (e.x node name  dbgenomt ) and  x.x.x.x is  the  IP  adress  of  IP address of the Tivoli Storage Manager
   
   
   SErvername dbgenomt    nodename dbgenomt    QUERYSCHEDPERIOD 1
       TCPNODELAY NO
       RETRYPERIOD 10
       ERRORLOGNAME "/tmp/dbtmp/dbgenomt_dsmerror.log"    SCHEDLOGNAME "/tmp/dbtmp/dbgenomt_dsmsched.log"
       SCHEDMODE POLLING
       SCHEDLOGRETENTION 4 D
       ERRORLOGRETENTION 4 D
       PASSWORDACCESS GENERATE
       passworddir  /genomtest/genomt    COMMmethod  TCPIP
       tcpserveraddress x.x.x.x    tcpport 1500
       TXNBYTELIMIT 2097152
       managedservices webclient
6. Make sure the Oracle user has the following permissions
1.  Read (r) permission to the /usr/tivoli/tsm/client/oracle/bin64 and /usr/tivoli/tsm/client/api/bin64 directories
1. Read permission (r-) to the tdpo.opt, dsm.opt, and dsm.sys files located in the /usr/tivoli/tsm/client/oracle/bin and /usr/tivoli/tsm/client/api/bin directories
7.  Change to the /usr/tivoli/tsm/client/oracle/bin64 directory and run the tdpoconf password command (as Oracle user) to generate the password file
   
   cd /usr/tivoli/tsm/client/oracle/bin64
   tdpoconf  password 
8. Run the tdpoconf showenvironment command to confirm proper configuration
   tdpoconf showenvironment
9. You can  take  backup (e.x script )
   run
   {
      allocate channel t1 type 'sbt_tape' parms
               'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
         backup
         filesperset 5
         format 'df_%t_%s_%p'
         (database);
      }

11g Release 2 RMAN Backup Compression

Oracle Compression

With Oracle 11g Oracle Advanced Compression provides comprehensive data compression capabilities to compress all types of data, backups, and network traffic in an application transparent manner.The Oracle Advanced Compression option contains the following features:

 

• Fast RMAN Compression
• Data Guard Network Compression
• Data Pump Compression
• OLTP Table Compression
• SecureFile Compression and Deduplication
• Flashback Data Archive (Total Recall)

Rman Compression
For RMAN backup compression evels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded. Unfortunately  use of LOW, MEDIUM and HIGH requires the "Advanced Compression license"

To configure RMAN to use compression at all you can use

RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed  by   one of the following

RMAN>CONFIGURE COMPRESSION ALGORITHM 'BASIC';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'LOW';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'HIGH'

Here are the results 

Method	Time(sec)	Backup Size(GB)
BASIC	266	1.258
LOW	66	1.637
MEDIUM	228	1.437
HIGH	668	1.166
NONE	74	3.241

 

 

Conclusion
Note  that  without compression backup time is  the best  but file size is  nearly  database  size

Also there is another factor that I did not take into account .The CPU load of these methods.we know that the complexity of compression level will generate CPU load on server.
Because of many factors (CPU source,disk speed ..)influencing backup speed and backup size you have to test backup and backup compression in your environment yourself.And you must consider your CPU nd disk performance .Also "Advanced Compression license" cost

Configure NTP (Network Time Protocol) clients on AIX

How to configure NTP on client with a NTP server .Our NTP servers  are   time_server01 and  time_server02

1. Verify that you have a server suitable for synchronization offset  must  be smaller  then 1000 ms 
   #ntpdate -d time_server01
2. Check if it is  running  or  not (configured  before)
   #lssrc -ls xntpd
3. Specify your ntp servers in /etc/ntp.conf  .Comment out the “broadcastclient” line (if applicable )
   #vi /etc/ntp.conf
   
   server    time_server01 prefer
   server    time_server02
   driftfile /etc/ntp.drift
   tracefile /etc/ntp.trace
   #broadcastclient
4. Be sure  that  you can rach time servers .if you define  a server  name  (not an IP be sure that you can  reach it ).You can add it  in /etc/hosts
   
   #vi /etc/hosts
   10.1.1.10       time_server01
   10.1.1.11       time_server02
   
   #ping  time_server01
   #nslookup time_server01
5. start  stop
   #stopsrc -s xntpd
   #startsrc -s xntpd
6. check  if it is  running  and  Sys peer should display the IP address or name of your xntp server. This process may take up to 15 minutes
   
   #lssrc -ls xntpd
   #lssrc -ls xntpd|grep "Sys peer"
   #ntpq -p
7. Uncomment xntpd from /etc/rc.tcpip so it will start on a reboot.
   
   # vi /etc/rc.tcpip
   Uncomment the following line:
   start /usr/sbin/xntpd "$src_running"
   
   

Disk performance test with IBM ndisk64 tool

ndisk64 is  an IBM free tool to measure  IO performance of   your  disk .
You can download  and  get information  from http://www.ibm.com/developerworks/wikis/display/WikiPtype/nstress

• I have  created  7   different 10g files on all mout points in order  to  spread   IO
  dd if=/dev/zero of=/datac1/bigfile1 bs=1m count=10240
  dd if=/dev/zero of=/datac2/bigfile2 bs=1m count=10240
  dd if=/dev/zero of=/datac3/bigfile3 bs=1m count=10240
  dd if=/dev/zero of=/datac4/bigfile4 bs=1m count=10240
  dd if=/dev/zero of=/datac5/bigfile5 bs=1m count=10240
  dd if=/dev/zero of=/datac6/bigfile6 bs=1m count=10240
  dd if=/dev/zero of=/datac7/bigfile7 bs=1m count=10240
• Put the names  of the files  to   a  input file
  
  echo "/datac1/bigfile1" >filelist
  echo "/datac2/bigfile2">>filelist
  echo "/datac3/bigfile3">>filelist
  echo "/datac4/bigfile4">>filelist
  echo "/datac5/bigfile5">>filelist
  echo "/datac6/bigfile6">>filelist
  echo "/datac7/bigfile7">>filelist
• Identify  some  system values and  also  define some assumptions   about  your  system my assumptions and system   values  are
  
  Block size=8k
  Read-WriteRatio: 70:30 = read mostly(OLTP)
  Timed duration of the test in seconds =120
   Mutliple processes used to generate =3
• Start  test  for different multiple  process
     /home/sturgut/ndisk64 -F filelist  -S -r70 -b 8k -t 120  -M3
  Command: /home/sturgut/ndisk64 -F filelist -S -r70 -b 8k -t 120 -M3
          Synchronous Disk test (regular read/write)
          No. of processes = 3
          I/O type         = Sequential
          Block size       = 8192
          Read-WriteRatio: 70:30 = read mostly
          Sync type: none  = just close the file
          Number of files  = 7
          File size        = 33554432 bytes = 32768 KB = 32 MB
          Run time         = 120 seconds
          Snooze %         = 0 percent
  ----> Running test with block Size=8192 (8KB) ...
  Proc - <-----disk io----=""> | <-----throughput------> RunTime
   Num -     TOTAL   IO/sec |    MB/sec       KB/sec  Seconds
     1 -   2523632  21030.3 |    164.30    168242.34 120.00
     2 -   2442193  20351.6 |    159.00    162813.09 120.00
     3 -   2097104  17475.9 |    136.53    139807.03 120.00
  TOTALS   7062929  58857.8 |    459.83 Seq procs=  3 read= 70% bs=  8KB
• In another session Monitor  IO service times using # iostat -RDTl
• Increase  the different multiple process  and monitor  the  service time .Create a IOPS vs. IO service time chart
• Increase the number of threads to get a peak IOPS
• Be sure your queue_depth is >= number of threads
• More than queue_depth x 2 threads won’t increase thruput

Disable or Enable all Oracle jobs

There  are two  kinds  of  jobs  dbms_job and  dbms_scheduler  jobs

1. Disable
• dbms_job
  alter system set  job_queue_processes=0;
• dbms_scheduler
  exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','true');
2. Enable
• dbms_job
  alter system set job_queue_processes=10;
• dbms_scheduler
  exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','true');

ORA-08104: this index object 136450 is being online built or rebuilt

Problem:While running an online index rebuild your session was killed  so  second attemp will give  following error.

alter index MARDATA.COR_VERGI_NO_HAR_X2  rebuild online
tablespace MARBASINDEX_1M storage(initial 1m next 1m) compress 2;

ERROR at line 1:

ORA-08104: this index object 136450 is being online built or rebuilt
Cause:
you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.So smon is   very slow   in cleaning up 

Solution:Use  dbms_repair.online_index_clean to  clean  up .

declare
  result boolean ;
begin
result:=DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
end ;
/

After  then you can   rebuild index.

How to flush a single SQL from shared pool

Use  DBMS_SHARED_POOL.purge
exec  DBMS_SHARED_POOL.purge('ADRESS,HASH_VALUE','C',1);
Example :
 select address,hash_value, executions, loads, version_count,
 invalidations, parse_calls
 from v$sqlarea
 where sql_text like 'UPDATE OTPL_BASVURU_KULLANICI SET ORDER_ID%';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
070000012EC6FB98 3630826165 1 1 1 0 1
alter session set events '5614566 trace name context forever';    --for  10.2.0.4 or 10.2.0.5
 EXEC SYS.DBMS_SHARED_POOL.purge('070000012EC6FB98,3630826165','C',1);


or another   flush  sql 

alter session set events '5614566 trace name context forever';


select  ' EXEC SYS.DBMS_SHARED_POOL.purge('''||address||','||to_char(hash_value)||''',''C'',1);'
from  v$sqlarea where hash_value=&hash_value;


Note1:Be careful usage  of   'ADRESS,HASH_VALUE'      not  'ADRESS','HASH_VALUE'

Note2:The create statement for this package can be found in the $ORACLE_HOME/rdbms/admin/dbmspool.sql script

Note3:DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available through the fix for Bug 5614566. However, the fix is event protected. You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.


event="5614566 trace name context forever"   #in init.ora for 10.2.0.4 or 10.2.0.5
or 
alter session set events '5614566 trace name context forever';

How to compile Invalid Object?

Operations such as upgrades, patches and DDL changes can invalidate schema objects.Here are  some  methods  to compile   objects .But I prefer  UTL_RECOMP (from sql  or PL/SQL ) or  $ORACLE_HOME/rdbms/admin/utlrp.sql (From Operting system)

1.  we  can  compile it manually like 
    alter  package serdar.xxx compile  body  ;
2. Manuel by the help  of a  script:we can  spool  the output  and  run it.Note  that  this script is not written for all types  like java classes
   
   Set heading off;
   set feedback off;
   set echo off;
   Set lines 999;
   spool compile.sql
   
   select  'alter '||
   decode(object_type,'SYNONYM',decode(owner,'PUBLIC','PUBLIC SYNONYM '||object_name,
   'SYNONYM '||OWNER||'.'||OBJECT_NAME)||' compile;',
   decode(OBJECT_TYPE ,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||
   ' '||owner||'.'||object_name||' compile '||
   decode(OBJECT_TYPE ,'PACKAGE BODY','BODY;',' ;'))
   from dba_objects where status<>'VALID'
   order by owner,OBJECT_NAME;
   spool off
   @compile.sql
   
   
   
3. DBMS_DDL.ALTER_COMPILE: The same with "alter   procudere serdar.test compile"
   
   exec dbms_ddl.alter_compile ('PROCEDURE','SERDAR','TEST');
4. DBMS_UTILITY.compile_schema:The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema
   
   EXEC DBMS_UTILITY.compile_schema(schema => 'SERDAR');
5. UTL_RECOMP :it will  recompile all invalids.
   
   --Compile  all invalids  in the database
   exec  SYS.UTL_RECOMP.RECOMP_SERIAL ();
   --Compile schema  SERDAR
   exec SYS.UTL_RECOMP.RECOMP_SERIAL ('SERDAR');   
   
   --Compile all invalids in the database parallel (If we  have eneogh  CPU )
   
   exec  SYS.UTL_RECOMP.recomp_parallel(4);
6. UTLRP.SQL  : From  the  operating  system as sysdba.Note that this is an example  for UNIX systems
   
   SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
   
    

How to Recreate the OraInventory on UNIX Systems

How can I recreate the OraInventory  if it gets  removed

1. Locate the oraInst.loc file, which may be in different locations, depending on your system
   /var/opt/oracle/oraInst.loc file
   or
   /etc/oraInst.loc
2. Modify the file oraInst.loc file
   cp /etc/oraInst.loc /etc/oraInst.loc.bak     #e.x for AIX run with rot
   
   mkdir /data1/oracle2/oraInventory          #path is examp.Change it .run with oracle owner user
3. Change oraInst.loc
   inventory_loc=/data1/oracle2/oraInventory
   
   inst_group=oinstall #it can be also dba
4. Change the permissions
   chmod 644 /etc/oraInst.loc
5. For consistency, copy the file to Oracle home directory, (using your directory location):
   
   cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak
   
   cp /etc/oraInst.loc                  $ORACLE_HOME/oraInst.loc
6. Run Oracle Universal Installer from your Oracle home as below:
   
   cd $ORACLE_HOME/oui/bin
   
   ./runInstaller -silent -attachHome ORACLE_HOME="/data1/oracle2/orahome10gr2" ORACLE_HOME_NAME="PARTEST"
   
   Note: The -attachHome is only officially supported in 10.2 and higher. But, we found it works on our testing with 10.1.2
7. Check the inventory output is correct for your Oracle home:
   $ORACLE_HOME/OPatch/opatch lsinventory -detail
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

How to grant on v$ views

When we  need to  grant on v$views  to a  user  I faced  with  ORA-02030 error

SQL> grant select on v$sqlarea to serdar;

grant select on v$sqlarea to serdar
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The  problem  is caused   because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym

Try this 

SQL> grant select  on v_$sqlarea to serdar;
Grant succeeded.

Where to find MAXxxxxxx control file parameters in Data Dictionary

PURPOSE
How to find  information about the following  controlfile parameters
- MAXLOGFILES

- MAXDATAFILES
- MAXLOGHISTORY
- MAXLOGMEMBERS
- MAXINSTANCES

Explanation

• The values of MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXINSTANCES and MAXLOGHISTORY are set either during CREATE DATABASE, or CREATE CONTROLFILE

• In all Oracle versions, the CREATE CONTROLFILE syntax can be regenerated from the data dictionary using 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;'  and can be checked from trace
• For  parameters (exepct  MAXLOGMEMBERS) can be seen  from  v$controlfile_record_section
  select decode(TYPE,
    'REDO LOG',    'MAXLOGFILES',
    'DATAFILE',     'MAXDATAFILES',
    'CKPT PROGRESS','MAXINSTANCES',
    'REDO THREAD','MAXINSTANCES',
    'LOG HISTORY','MAXLOGHISTORY') TYPE,RECORDS_TOTAL
  from v$controlfile_record_section
  where type in
  ('REDO LOG','DATAFILE','CKPT PROGRESS','REDO THREAD','LOG HISTORY');
  
  
  TYPE RECORDS_TOTAL
  
  --------------- -------------
  MAXINSTANCES 11
  MAXINSTANCES 8
  MAXLOGFILES 128
  MAXDATAFILES 10000
  MAXLOGHISTORY 10225
• MAXLOGMEMBERS is available via "x$kccdi.dimlm".
  

Database Crashes With ORA-00494

Symptoms

Database may crashed with the following error in the alert file

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 5488'

Incident details in: d:\oracle\admin\ecore\diag\rdbms\ecore\ecore\incident\incdir_12529\ecore_lgwr_5484_i12529.trc
Killing enqueue blocker (pid=5488) on resource CF-00000000-00000000 by (pid=5484)
by killing session 3.1
Killing enqueue blocker (pid=5488) on resource CF-00000000-00000000 by (pid=5484)
by terminating the process
LGWR (ospid: 5484): terminating the instance due to error 2103
Fri Dec 17 03:05:09 2010
Instance terminated by LGWR, pid = 5484

Cause


The lgwr has killed the ckpt process, causing the instance to crash.
From the alert.log we can see:
That the database has waited too long for a CF enqueue, so the following error has been reported.
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by by 'inst 1, osid 5488'

Then the LGWR has killed the blocker, which was in this case the CKPT process which cause the instance to crash.
Checking the alert.log we can see that the frequency of redo log files switch is very high(almost every 1 min).

Solution


1-We usually suggest to configure the redo log switches to be done every 20~30 min to reduce the contention on the control files.

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to
determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online
logs to be at least this size.

References

BUG:7448854 - ORA-00494 CAUSE THE INSTANCE TO CRASH
Metalink Note: 753290.1

Oracle enqueue wait

List of Oracle 10g enqueue waits  are  following .The aggregated statistics for each of these enqueue types is displayed by the view V$ENQUEUE_STAT


enq: AD - allocate AU:Synchronizes accesses to a specific OSM (Oracle Software Manager) disk AU


enq: AD - deallocate AU:Synchronizes accesses to a specific OSM disk AU

enq: AF - task serialization:Serializes access to an advisor task

enq: AG - contention:Synchronizes generation use of a particular workspace

enq: AO - contention:Synchronizes access to objects and scalar variables

enq: AS - contention:Synchronizes new service activation

enq: AT - contention:Serializes alter tablespace operations

enq: AW - AW$ table lock:Allows global access synchronization to the AW$ table (analytical workplace tables used in OLAP option)

enq: AW - AW generation lock:Gives in-use generation state for a particular workspace

enq: AW - user access for AW:Synchronizes user accesses to a particular workspace

enq: AW - AW state lock:Row lock synchronization for the AW$ table

enq: BR - file shrink:Lock held to prevent file from decreasing in physical size during RMAN backup

enq: BR - proxy-copy:Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup

enq: CF - contention:The CF enqueue is a Control File enqueue   and happens during parallel access to the control files.  The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands

enq: CI - contention:The CI enqueue is the Cross Instance enqueue  and happens when a session executes a cross instance call such as a query over a database link

enq: CL - drop label:Synchronizes accesses to label cache when dropping a label

enq: CL - compare labels:Synchronizes accesses to label cache for label comparison

enq: CM - gate:Serializes access to instance enqueue

enq: CM - instance:Indicates OSM disk group is mounted

enq: CT - global space management:Lock held during change tracking space management operations that affect the entire change tracking file

enq: CT - state:Lock held while enabling or disabling change tracking to ensure that it is enabled or disabled by only one user at a time

enq: CT - state change gate 2:Lock held while enabling or disabling change tracking in RAC

enq: CT - reading:Lock held to ensure that change tracking data remains in existence until a reader is done with it

enq: CT - CTWR process start/stop:Lock held to ensure that only one CTWR (Change Tracking Writer, which tracks block changes and is initiated by the alter database enable block change tracking command) process is started in a single instance

enq: CT - state change gate 1:Lock held while enabling or disabling change tracking in RAC

enq: CT - change stream ownership:Lock held by one instance while change tracking is enabled to guarantee access to thread-specific resources

enq: CT - local space management:Lock held during change tracking space management operations that affect just the data for one thread

enq: CU - contention:Recovers cursors in case of death while compiling

enq: DB - contention:Synchronizes modification of database wide supplemental logging attributes

enq: DD - contention:Synchronizes local accesses to ASM (Automatic Storage Management) disk groups

enq: DF - contention:Enqueue held by foreground or DBWR when a datafile is brought online in RAC

enq: DG - contention:Synchronizes accesses to ASM disk groups

enq: DL - contention:Lock to prevent index DDL during direct load

enq: DM - contention:Enqueue held by foreground or DBWR to synchronize database mount/open with other operations

enq: DN - contention:Serializes group number generations

enq: DP - contention:Synchronizes access to LDAP parameters

enq: DR - contention:Serializes the active distributed recovery operation

enq: DS - contention:Prevents a database suspend during LMON reconfiguration

enq: DT - contention:Serializes changing the default temporary table space and user creation

enq: DV - contention:Synchronizes access to lower-version Diana (PL/SQL intermediate representation)

enq: DX - contention:Serializes tightly coupled distributed transaction branches

enq: FA - access file:Synchronizes accesses to open ASM files

enq: FB - contention:This is the Format Block enqueue, used only when data blocks are using ASSM (Automatic Segment Space Management or bitmapped freelists).  As we might expect, common FB enqueue relate to buffer busy conditions, especially since ASSM tends to cause performance problems under heavily DML loads

enq: FC - open an ACD thread:LGWR opens an ACD thread

enq: FC - recover an ACD thread:SMON recovers an ACD thread

enq: FD - Marker generation:Synchronization

enq: FD - Flashback coordinator:Synchronization

enq: FD - Tablespace flashback on/off:Synchronization

enq: FD - Flashback on/off:Synchronization

enq: FG - serialize ACD relocate:Only 1 process in the cluster may do ACD relocation in a disk group

enq: FG - LGWR redo generation enq race:Resolves race condition to acquire Disk Group Redo Generation Enqueue

enq: FG - FG redo generation enq race:Resolves race condition to acquire Disk Group Redo Generation Enqueue

enq: FL - Flashback database log:Synchronizes access to Flashback database log

enq: FL - Flashback db command:Synchronizes Flashback Database and deletion of flashback logs

enq: FM - contention:Synchronizes access to global file mapping state

enq: FR - contention:Begins recovery of disk group

enq: FS - contention:Synchronizes recovery and file operations or synchronizes dictionary check

enq: FT - allow LGWR writes:Allows LGWR to generate redo in this thread

enq: FT - disable LGWR writes:Prevents LGWR from generating redo in this thread

enq: FU - contention:Serializes the capture of the DB feature, usage, and high watermark statistics

enq: HD - contention:Serializes accesses to ASM SGA data structures

enq: HP - contention:Synchronizes accesses to queue pages

enq: HQ - contention:Synchronizes the creation of new queue IDs

enq: HV - contention:The HV enqueue is similar to the HW enqueue but for parallel direct path INSERTs

enq: HW - contention:The HW High Water enqueue  occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM

enq: IA - contention:Information not available

enq: ID - contention:Lock held to prevent other processes from performing controlfile transaction while NID is running

enq: IL - contention:Synchronizes accesses to internal label data structures

enq: IM - contention for blr:Serializes block recovery for IMU txn

enq: IR - contention:Synchronizes instance recovery

enq: IR - contention2:Synchronizes parallel instance recovery and shutdown immediate

enq: IS - contention:Synchronizes instance state changes

enq: IT - contention:Synchronizes accesses to a temp object’s metadata

enq: JD - contention:Synchronizes dates between job queue coordinator and slave processes

enq: JI - contention:Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view

enq: JQ - contention:Lock to prevent multiple instances from running a single job

enq: JS - contention:Synchronizes accesses to the job cache

enq: JS - coord post lock:Lock for coordinator posting

enq: JS - global wdw lock:Lock acquired when doing wdw ddl

enq: JS - job chain evaluate lock:Lock when job chain evaluated for steps to create

enq: JS - q mem clnup lck:Lock obtained when cleaning up q memory

enq: JS - slave enq get lock2:Gets run info locks before slv objget

enq: JS - slave enq get lock1:Slave locks exec pre to sess strt

enq: JS - running job cnt lock3:Lock to set running job count epost

enq: JS - running job cnt lock2:Lock to set running job count epre

enq: JS - running job cnt lock:Lock to get running job count

enq: JS - coord rcv lock:Lock when coord receives msg

enq: JS - queue lock:Lock on internal scheduler queue

enq: JS - job run lock - synchronize:Lock to prevent job from running elsewhere

enq: JS - job recov lock:Lock to recover jobs running on crashed RAC inst

enq: KK - context:Lock held by open redo thread, used by other instances to force a log switch

enq: KM - contention:Synchronizes various Resource Manager operations

enq: KO - fast object checkpoint:The KO enqueue (a.k.a. enq: KO - fast object checkpoint) is seem in Oracle STAR transformations and high enqueue waits can indicate a sub-optimal DBWR background process


enq: KP - contention:Synchronizes kupp process startup

enq: KT - contention:Synchronizes accesses to the current Resource Manager plan

enq: MD - contention:Lock held during materialized view log DDL statements

enq: MH - contention:Lock used for recovery when setting Mail Host for AQ e-mail notifications

enq: ML - contention:Lock used for recovery when setting Mail Port for AQ e-mail notifications

enq: MN - contention:Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session

enq: MR - contention:Lock used to coordinate media recovery with other uses of datafiles

enq: MS - contention:Lock held during materialized view refresh to set up MV log

enq: MW - contention:Serializes the calibration of the manageability schedules with the Maintenance Window

enq: OC - contention:Synchronizes write accesses to the outline cache

enq: OL - contention:Synchronizes accesses to a particular outline name

enq: OQ - xsoqhiAlloc:Synchronizes access to olapi history allocation

enq: OQ - xsoqhiClose:Synchronizes access to olapi history closing

enq: OQ - xsoqhistrecb:Synchronizes access to olapi history globals

enq: OQ - xsoqhiFlush:Synchronizes access to olapi history flushing

enq: OQ - xsoq*histrecb:Synchronizes access to olapi history parameter CB

enq: PD - contention:Prevents others from updating the same property

enq: PE - contention:The PE enqueue is the Parameter Enqueue, which happens after “alter system” or “alter session” statements

enq: PF - contention:Synchronizes accesses to the password file

enq: PG - contention:Synchronizes global system parameter updates

enq: PH - contention:Lock used for recovery when setting proxy for AQ HTTP notifications

enq: PI - contention:Communicates remote Parallel Execution Server Process creation status

enq: PL - contention:Coordinates plug-in operation of transportable tablespaces

enq: PR - contention:Synchronizes process startup

enq: PS - contention:The PS enqueue is the Parallel Slave synchronization enqueue which is only seen with Oracle parallel query.  The PS enqueue happens when pre-processing problems occur when allocating the factotum (slave) processes for OPQ

enq: PT - contention:Synchronizes access to ASM PST metadata

enq: PV - syncstart:Synchronizes slave start_shutdown

enq: PV - syncshut:Synchronizes instance shutdown_slvstart

enq: PW - prewarm status in dbw0:DBWR0 holds this enqueue indicating pre-warmed buffers present in cache

enq: PW - flush prewarm buffers:Direct Load needs to flush prewarmed buffers if DBWR0 holds this enqueue

enq: RB - contention:Serializes OSM rollback recovery operations

enq: RF - synch: per-SGA Broker metadata:Ensures r/w atomicity of DG configuration metadata per unique SGA

enq: RF - synchronization: critical ai:Synchronizes critical apply instance among primary instances

enq: RF - new AI:Synchronizes selection of the new apply instance

enq: RF - synchronization: chief:Anoints 1 instance's DMON (Data Guard Broker Monitor) as chief to other instance’s DMONs

enq: RF - synchronization: HC master:Anoints 1 instance's DMON as health check master

enq: RF - synchronization: aifo master:Synchronizes critical apply instance failure detection and failover operation

enq: RF - atomicity:Ensures atomicity of log transport setup

enq: RN - contention:Coordinates nab computations of online logs during recovery

enq: RO - contention:Coordinates flushing of multiple objects.The RO enqueue is the Reuse Object enqueue and is a cross-instance enqueue related to truncate table and drop table DDL operations

enq: RO - fast object reuse:Coordinates fast object reuse

enq: RP - contention:Enqueue held when resilvering is needed or when data block is repaired from mirror

enq: RS - file delete:Lock held to prevent file from accessing during space reclamation

enq: RS - persist alert level:Lock held to make alert level persistent

enq: RS - write alert level:Lock held to write alert level

enq: RS - read alert level:Lock held to read alert level

enq: RS - prevent aging list update:Lock held to prevent aging list update

enq: RS - record reuse:Lock held to prevent file from accessing while reusing circular record

enq: RS - prevent file delete:Lock held to prevent deleting file to reclaim space

enq: RT - contention:Thread locks held by LGWR, DBW0, and RVWR (Recovery Writer, used in Flashback Database operations) to indicate mounted or open status

enq: SB - contention:Synchronizes logical standby metadata operations

enq: SF - contention:Lock held for recovery when setting sender for AQ e-mail notifications

enq: SH - contention:Enqueue always acquired in no-wait mode; should seldom see this contention

enq: SI - contention:Prevents multiple streams table instantiations

enq: SK - contention:Serialize shrink of a segment

enq: SQ - contention:The SQ enqueue is the Sequence Cache enqueue  is used to serialize access to Oracle sequences

enq: SR - contention:Coordinates replication / streams operations

enq: SS - contention:Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up.
enq: ST - contention:Synchronizes space management activities in dictionary-managed tablespaces

enq: SU - contention:Serializes access to SaveUndo Segment

enq: SW - contention:Coordinates the ‘alter system suspend’ operation

enq: TA - contention:Serializes operations on undo segments and undo tablespaces

enq: TB - SQL Tuning Base Cache Update:Synchronizes writes to the SQL Tuning Base Existence Cache

enq: TB - SQL Tuning Base Cache Load:Synchronizes writes to the SQL Tuning Base Existence Cache

enq: TC - contention:The TC enqueue is related to the DBWR background process and occur when “alter tablespace” commands are issued.  You will also see the TC enqueue when doing parallel full-table scans where rows are accessed directly, without being loaded into the data buffer cache

enq: TC - contention2:Lock during setup of a unique tablespace checkpoint in null mode

enq: TD - KTF dump entries:KTF dumping time/scn mappings in SMON_SCN_TIME table

enq: TE - KTF broadcast:KTF broadcasting

enq: TF - contention:Serializes dropping of a temporary file

enq: TL - contention:Serializes threshold log table read and update

enq: TM - contention:The TM enqueue related to Transaction Management  and can be seen when tables are explicitly locked with reorganization activities that require locking of a table

enq: TO - contention:Synchronizes DDL and DML operations on a temp object

enq: TQ - TM contention:TM access to the queue table

enq: TQ - DDL contention:DDL access to the queue table

enq: TQ - INI contention:TM access to the queue table

enq: TS - contention:Serializes accesses to temp segments.these enqueues happen during disk sort operations

enq: TT - contention:The TT enqueue  is used to avoid deadlocks in parallel tablespace operations.  The TT enqueue can be seen with parallel create tablespace and parallel point in time recovery (PITR)

enq: TW - contention:Lock held by one instance to wait for transactions on all instances to finish

enq: TX - contention:Lock held by a transaction to allow other transactions to wait for it

enq: TX - row lock contention:Lock held on a particular row by a transaction to prevent other transactions from modifying it

enq: TX - allocate ITL entry:Allocating an ITL entry in order to begin a transaction

enq: TX - index contention:Lock held on an index during a split to prevent other operations on it

enq: UL - contention:The UL enqueue is a User Lock enqueue   and happens when a lock is requested in dbms_lock.request.  The UL enqueue can be seen in Oracle Data Pump

enq: US - contention:The US enqueue happens with Oracle automatic UNDO management was undo segments are moved online and offline

enq: WA - contention:Lock used for recovery when setting watermark for memory usage in AQ notifications

enq: WF - contention:Enqueue used to serialize the flushing of snapshots

enq: WL - contention:Coordinates access to redo log files and archive logs

enq: WP - contention:Enqueue to handle concurrency between purging and baselines

enq: XH - contention:Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications

enq: XR - quiesce database:Lock held during database quiesce

enq: XR - database force logging:Lock held during database force logging mode

enq: XY - contention:Lock used by Oracle Corporation for internal testing

 
See also:
Reference: http://oracle-dox.net/McGraw.Hill-Oracle.Wait.Interf/8174final/LiB0063.html

enq: TX - index contention

It’s possible that we see high Index leaf block contention  on index associated with  tables, which are having high concurrency from the application.  This usually happens when the application performs lot of INSERTs and DELETEs  .

The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait  for  TX lock in mode 4, until the session that is doing the block splits completes the operations

Causes

• Indexes on the tables which are being accessed heavily from the application.
• Indexes on table columns which are having values inserted by a monotonically increasing.
• Heavily deleted tables

Detecting index leaf block contention
There are many ways to find hot indexes

• Check high  "enq: TX – index contention" system waits   from AWR report
• At the same  time  you will see  high split events  in the  Instance  avtivity stats  in the AWR  report
• If yor system is RAC also   you will see  the  following  wait events in the AWR report
  gc buffer busy waits on Index Branch Blocks
  gc buffer busy waits on Index Leaf Blocks
  gc current block busy on Remote Undo Headers
  gc current split
  gcs ast xid
  gcs refuse xid
• we  can find sql_id ' s  from v$active_session_history
  
  select sql_id,count(*) from v$active_session_history a
  
  where a.sample_time between sysdate - 5/24 and sysdate
  and trim(a.event) like 'enq: TX - index contention%'
  group by sql_id
  order by 2;
• Or we  can  query the segments  from  V$SEGMENT_STATISTICS or from the 'Segments by Row Lock Waits' of the AWR reports.
  
  select * from v$segment_statistics
  where statistic_name ='row lock waits'
  
  and value>0 order by value desc;
• we can  query   dba_hist_enqueue_stat and the stats$enqueuestat  or  v$enqueue_stat       
  select * from  v$enqueue_stat        order  by cum_wait_time;

Solutions 

• Reverse Key  Indexes :Rebuild the as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports.These indexes are excellent for insert performance.  But the downside of it is that, it may affect the performance of index range scans
• Hash partitioned global indexes :When an index is monotonically growing because of a sequence or date key, global hash-partitioned indexes improve performance by spreading out the contention. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments
• 
  CACHE size of the sequences:When we use monotonically increasing sequences for populating column values, the leaf block which is having high sequence key will be changing with every  insert, which makes it a hot block and potential candidate for a block split. With CACHE SIZE (and probably with NOORDER option), each instance would use start using the sequence keys with a different range reduces the index keys getting insert same set of leaf blocks
• Index  block size:Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace. Adjusting the index block size may only produce a small effect and changing the index block size should never be your first choice for reducing index block contention, and it should only be done after the other approaches have been examined.
• Rebuild indexes:If  many rows are  deleted  or  it is an  skewed indes   rebuilding will help  for a  while 

See Also:
All Oracle enqueue waits
Solving Waits on "enq: TM - contention"









Index leaf block contention is very common in busy databases and it’s especially common on tables that have monotonically increasing key values.

Oracle data loading performance

Oracle  choices for data loading

• SQL insert and merge statements
• PL/SQL bulk loads for the forall PL/SQL operator
• SQL*Loader
• Oracle10 Data Pump
• Oracle import utility

TIPS FOR DATA LOADING

1. Use a large blocksize:Data loads onto large  blocksize  (e.x 32k ) will run faster becuse more rows will be written in  an empty block
2. Use a small db_cache_size:If loading with DML a small data cache will minimize DBWR work during async buffer cleanouts.You can reduce  it  with alter  system  temporarily
3. Size your log_buffer properly:If you have waits associated to log_buffer size “db log sync wait”, try increasing to to 10m.
4. Watch your commit frequency:At each commit, Oracle releases locks and undo segments.Benchmarks suggest that you should commit as infrequently as possible
5. Use large undo segments:In order to avoid a ORA-1555 (snapshot too old) error use  large undo segments.
6. Use append hint:If you must use SQL inserts By using the append hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an append hint.  Note:  Prior to 11g r2, the append hint supports only the subquery syntax of the INSERT statement, not the VALUES clause, where you can use the append_values hint
   
   insert /*+ append */ into customer (select  ....);
7. Use  nologging if possible:NOLOGGING mode (for sql level,table level,database level)  will allow Oracle to avoid almost all redo logging. but the operaions will be unrecoverable
8. Disable archiving if possible:
9. Use PL/SQL bulking:PL/SQL often out-performs standard SQL inserts because of the array processing and bulking in the "forall" statement.   (see also  : PL/SQL forall operator speeds for table inserts )
10. Partition :Load the data in a separate partition, using transportable tablespaces.
11. Use multiple freelists or freelist groups for target tables:Avoid using bitmap freelists ASS management (automatic segment space management) for super high-volume loads.
12. Preallocate  space  for target  tables:Pre allocate  space for  tables and index  with "allocate extent"  clause  in order  to  gain performance on  allocation of segments.
13. Pre-sort the data in index key order:This will make subsequent SQL run far faster for index range scans. 
14. Use parallel DML:Parallelize the data loads according to the number of processors and disk layout.Try to saturate your processors with parallel processes.
15. Disable constraints :Disable during load and re-enable in parallel following the load
16. Disable/drop indexes - It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size. If you choose to keep the indexes during inserts, consider creating a reverse key index to minimize insert contention.
17. Disable/drop  triggers if possible:
18. Use RAM Disk:Place undo tablespace and online redo logs on Solid-state disk (RAM SAN),
19. Use SSD RAM Disk:Especially for the insert partition, undo and redo.  You can move the partition to standard disk later.
20. Use SAME RAID:Avoid RAID5 and use Oracle Stripe and Mirror Everywhere approach (RAID 1+0, RAID10).
    
    
     

PL/SQL forall operator speeds for table inserts

Loading an Oracle table from a PL/SQL array involves expensive context switches, and the PL/SQL FORALL operator speed is amazing here is an example


create  table  t_dba_objects as  select * from dba_objects  where  1=2 ;

DECLARE

  TYPE prod_tab IS TABLE OF dba_objects%ROWTYPE;
  dba_objects_tab prod_tab := prod_tab();
  start_time number; end_time number;
BEGIN
  SELECT * BULK COLLECT INTO dba_objects_tab FROM dba_objects;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FOR i in dba_objects_tab.first .. dba_objects_tab.last LOOP
    INSERT INTO t_dba_objects VALUES (
        dba_objects_tab(i).OWNER ,dba_objects_tab(i).OBJECT_NAME,
        dba_objects_tab(i).SUBOBJECT_NAME,dba_objects_tab(i).OBJECT_ID,
        dba_objects_tab(i).DATA_OBJECT_ID,dba_objects_tab(i).OBJECT_TYPE ,
       dba_objects_tab(i).CREATED ,dba_objects_tab(i).LAST_DDL_TIME,
       dba_objects_tab(i).TIMESTAMP ,dba_objects_tab(i).STATUS ,
       dba_objects_tab(i).TEMPORARY,dba_objects_tab(i).GENERATED ,
      dba_objects_tab(i).SECONDARY  );
  END LOOP;
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FORALL i in dba_objects_tab.first .. dba_objects_tab.last
   INSERT INTO t_dba_objects VALUES dba_objects_tab(i);
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Bulk Insert: ' ||to_char(end_time-start_time));
  COMMIT;
END;
/
Conventional Insert: 23689

Bulk Insert: 128

Note:Forall   is very fast then the convential for loop but it uses much  more  UGA memory depend on row count and column count.(e.x for 100m table I  examine that it used  5g UGA memory)

Oracle Cross-Platform Migration Using Rman (Convert Database )

1. Check Oracle version on  both side if   the patch level  of new  platform is  different   than old one try  to create a  new database  and  use  Transportable  tablespace  feature of Oracle (In this soluiton   be careful sys objects and so on  ).In my example  I will   migrate  from 10.2.0.4 Solaris  64  to AIX 64 bit
2. Check  the endian format  of   source and  destination platforms If endian formats are the same converting only system and  undo tablespaces  (datafiles that contain undo ) are enough
   
   select platform_name, endian_format from V$TRANSPORTABLE_PLATFORM;
3. Open database  read only;
   
   startup mount;
   alter database open read only;
4. Use DBMS_TDB.CHECK_DB to check whether the database can be transported to a desired destination platform
   
   set serveroutput on
   
   declare
   db_ready boolean;
   begin
     db_ready := sys.dbms_tdb.check_db('AIX-Based Systems (64-bit)');
   end;
   /
   
    
5. Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files as mentioned above.
   
   
   set serveroutput on
   
   declare
   external boolean;
   begin
     external := dbms_tdb.check_external;
   end;
   /
6.  Identify datafiles that contain undo data by running the following query.Because  my endian format is  the same I will only convert system and undo tablespaces.
   
   select distinct(file_name)
   
   from dba_data_files a, dba_rollback_segs b
   where a.tablespace_name=b.tablespace_name;
   
7. Run RMAN command to create conversion scripts and init.ora file:
   FORMAT defines location of init.ora file
   DB_FILE_NAME_CONVERT defines final data files location on destination server.
   
   rman
   connect target /
   
   CONVERT DATABASE ON TARGET PLATFORM
   CONVERT SCRIPT '/switchthome/switcht/tts/convertscript.rman'
   TRANSPORT SCRIPT '/switchthome/switcht/tts/transportscript.sql'
   new database 'SWITCHT'
   FORMAT '/switchthome/switcht/tts/%U'
   DB_FILE_NAME_CONVERT =('/switchtdata01/data','/wftesthome/sw/data');
8. Copy the  files
1. Transport.sql
2. Convertscript.rman
3. Pfile generated by the convert database command.
4. Copy system  and undo databafiles to stage area
5. Copy rest datafiles to final location on destination server (if endian formats are diffrent  also  we must copy all  the datafiles  to stage  and  convert  them)
9. Check the envorminmets
1. ORACLE_SID,ORACLE_HOME,PATH,LD_LIBRARY_PATH,LIBPATH ..
2. Copy and edit the init.ora
3. check the  directories  like dump..
10. Create a dummy Controlfile  in order  to convert
    sqlplus '/ as sysdba'
    STARTUP NOMOUNT ;
    
    create controlfile reuse set database "SWITCHT" RESETLOGS ARCHIVELOG
    
     MAXLOGFILES 128
     MAXLOGMEMBERS 4
     MAXDATAFILES 10000
     MAXINSTANCES 8
     MAXLOGHISTORY 10210
    LOGFILE
    
     '/wftesthome/sw/log/redo01.log' size 10m,
     '/wftesthome/sw/log/redo02.log' size 10m,
     '/wftesthome/sw/log/redo03.log' size 10m,
     '/wftesthome/sw/log/redo04.log' size 10m,
     '/wftesthome/sw/log/redo05.log' size 10m,
     '/wftesthome/sw/log/redo06.log' size 10m
    DATAFILE
    
     '/wftesthome/sw/stage/system01.dbf',
     '/wftesthome/sw/stage/undorbs01.dbf',
     '/wftesthome/sw/data/sysaux01.dbf',
     '/wftesthome/sw/data/tools01.dbf',
     '/wftesthome/sw/data/users01.dbf',
     '/wftesthome/sw/data/oasis_index01.dbf',
     '/wftesthome/sw/data/oasisdata_100m01.dbf',
     '/wftesthome/sw/data/oasisindex_10m01.dbf',
     '/wftesthome/sw/data/oasisindex_1m01.dbf',
     '/wftesthome/sw/data/index_1m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_100m01.dbf',
     '/wftesthome/sw/data/data_128k_ts01.dbf',
     '/wftesthome/sw/data/data_1m_ts01.dbf',
     '/wftesthome/sw/data/data_10m_ts01.dbf',
     '/wftesthome/sw/data/data_100m_ts01.dbf',
     '/wftesthome/sw/data/index_128k_ts01.dbf',
     '/wftesthome/sw/data/index_10m_ts01.dbf',
     '/wftesthome/sw/data/index_100m_ts02.dbf',
     '/wftesthome/sw/data/index_100m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_64k01.dbf',
     '/wftesthome/sw/data/oasisdata_10m01.dbf',
     '/wftesthome/sw/data/oasisdata_1m01.dbf',
     '/wftesthome/sw/data/oasisdata_64k01.dbf',
     '/wftesthome/sw/data/oasis_01.dbf'
    CHARACTER SET WE8ISO8859P9;
11.  edit the file Convertscript.rman  only for system and undo tablespaces  (if endian formats are diffrent  also  we must copy all  the datafiles  to stage  and  convert  them)
    
    rman target / nocatalog @convert.rman
    
    
    
    ---convert.rman
    RUN {
     CONVERT DATAFILE '/wftesthome/sw/stage/undorbs01.dbf'
     FROM PLATFORM 'Solaris[tm] OE (64-bit)'
     FORMAT '/wftesthome/sw/data/undorbs01.dbf';
    
     CONVERT DATAFILE '/wftesthome/sw/stage/system01.dbf'
     FROM PLATFORM 'Solaris[tm] OE (64-bit)'
     FORMAT '/wftesthome/sw/data/system01.dbf';
    }
12. shutdown the database and delete the dummy controlfile
13. edit the TRANSPORT sql script to reflect the new path for datafiles and redolog files in the CREATE CONTROLFILE section of the script
    
    sqlplus '/ as sysdba'
    
    STARTUP NOMOUNT ;
    
    
    
    create controlfile reuse set database "SWITCHT" RESETLOGS ARCHIVELOG
    
     ARCHIVELOG
     MAXLOGFILES 128
     MAXLOGMEMBERS 4
     MAXDATAFILES 10000
     MAXINSTANCES 8
     MAXLOGHISTORY 10210
    LOGFILE
     '/wftesthome/sw/log/redo01.log' size 10m,
     '/wftesthome/sw/log/redo02.log' size 10m,
     '/wftesthome/sw/log/redo03.log' size 10m,
     '/wftesthome/sw/log/redo04.log' size 10m,
     '/wftesthome/sw/log/redo05.log' size 10m,
     '/wftesthome/sw/log/redo06.log' size 10m
    DATAFILE
     '/wftesthome/sw/data/system01.dbf',
     '/wftesthome/sw/data/undorbs01.dbf',
     '/wftesthome/sw/data/sysaux01.dbf',
     '/wftesthome/sw/data/tools01.dbf',
     '/wftesthome/sw/data/users01.dbf',
     '/wftesthome/sw/data/oasis_index01.dbf',
     '/wftesthome/sw/data/oasisdata_100m01.dbf',
     '/wftesthome/sw/data/oasisindex_10m01.dbf',
     '/wftesthome/sw/data/oasisindex_1m01.dbf',
     '/wftesthome/sw/data/index_1m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_100m01.dbf',
     '/wftesthome/sw/data/data_128k_ts01.dbf',
     '/wftesthome/sw/data/data_1m_ts01.dbf',
     '/wftesthome/sw/data/data_10m_ts01.dbf',
     '/wftesthome/sw/data/data_100m_ts01.dbf',
     '/wftesthome/sw/data/index_128k_ts01.dbf',
     '/wftesthome/sw/data/index_10m_ts01.dbf',
     '/wftesthome/sw/data/index_100m_ts02.dbf',
     '/wftesthome/sw/data/index_100m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_64k01.dbf',
     '/wftesthome/sw/data/oasisdata_10m01.dbf',
     '/wftesthome/sw/data/oasisdata_1m01.dbf',
     '/wftesthome/sw/data/oasisdata_64k01.dbf',
     '/wftesthome/sw/data/oasis_01.dbf'
    CHARACTER SET WE8ISO8859P9;
    
    alter database open resetlogs;
    alter tablespace temp add tempfile '/wftesthome/sw/data/temp01.dbf'  SIZE 100m  autoextend off;
    shutdown immediate;
    
    startup upgrade;
    @@ ?/rdbms/admin/utlirp.sql
    shutdown immediate;
    
    startup;
    @@?/rdbms/admin/utlrp.sql
    set feedback 6;

See also:
Transportable Tablespaces (TTS)
References:
Metalink 415884.1 Cross Platform Database Conversion with same Endian
Metalink 414878.1 Cross-Platform Migration on Destination Host Using Rman Convert Database
Metalink 732053.1 Avoid Datafile Conversion during Transportable Database
Metalink 417455.1 Datafiles are not converted in parallel for transportable database
Metalink 100693.1 Getting Started with Transportable Tablespaces
Metalink 413586.1 How To Use RMAN CONVERT DATABASE for Cross Platform Migration:
Metalink 371556.1 How move tablespaces across platforms using Transportable Tablespaces with RMAN
Metalink 579136.1 IMPDP TRANSPORTABLE TABLESPACE FAILS for SPATIAL INDEX)
Metalink 77523.1 Transportable Tablespaces -- An Example to setup and use
Metalink 243304.1 10g : Transportable Tablespaces Across Different Platforms
Metalink 733824.1 HowTo Recreate a database using TTS (TransportableTableSpace)

ORA-08102: index key not found

I have  taken  folllowing error while  updating  the table
ORA-08102: index key not found, obj# 1456599, file 1133, block 63620 (2)

This can be a possible inconsistency in index because of a bug.

Example  :Bug 7329252  ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE

Fix:Minimize updates during online index rebuild and rebuild the index.

Transportable Tablespaces (TTS)

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another

1. The evalutaion of TTS
• Oracle8i:Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size
• Oracle9i:TTS technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes
• Oracle10g:TTS technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms which has same ENDIAN formats. If ENDIAN formats are different you have to use RMAN  to convert them .
  
   You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering).
   
  SQL> COLUMN PLATFORM_NAME FORMAT A32
  SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
  
  PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
  ----------- -------------------------------- ------------
  
  1 Solaris[tm] OE (32-bit)           Big
  2 Solaris[tm] OE (64-bit)           Big7 Microsoft Windows IA (32-bit)     Little
  10 Linux IA (32-bit)                Little
  6 AIX-Based Systems (64-bit)        Big
  3 HP-UX (64-bit)                    Big
  5 HP Tru64 UNIX                     Little
  4 HP-UX IA (64-bit)                 Big
  11 Linux IA (64-bit)                Little
  15 HP Open VMS                      Little
  8 Microsoft Windows IA (64-bit)     Little
  9 IBM zSeries Based  Linux          Big
  13 Linux 64-bit for AMD             Little
  16 Apple Mac OS                     Big
  12 Microsoft Windows 64-bit for AMD Little
  17 Solaris Operating System (x86)   Little
2. Usage of TTS
• Exporting and importing partitions in data warehousing tables
• Publishing structured data on CDs
• Copying multiple read-only versions of a tablespace on multiple databases
• Archiving historical data
• Performing tablespace point-in-time-recovery (TSPITR) 
3. Limitations on Transportable Tablespace Use
• Character set:The source and target database must use the same character set and national character set.
• Tablespace_name:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set
• Encrypted tablespaces:Encrypted tablespaces have the following the limitations
• Before transporting an encrypted tablespace, you must copy the Oracle wallet manually to the destination database, unless the master encryption key is stored in a Hardware Security Module (HSM) device instead of an Oracle wallet. When copying the wallet, the wallet password remains the same in the destination database. However, it is recommended that you change the password on the destination database so that each database has its own wallet password
• You cannot transport an encrypted tablespace to a database that already has an Oracle wallet for transparent data encryption. In this case, you must use Oracle Data Pump to export the tablespace's schema objects and then import them to the destination database. You can optionally take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported
• You cannot transport an encrypted tablespace to a platform with different endianness
• Encrypted columns:Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported
• XML Types:Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes. Beginning with Oracle Database 11g Release 1, you must use only Data Pump to export and import the tablespace metadata for tablespaces that contain XMLTypes
  
  The following query returns a list of tablespaces that contain XMLTypes
  
  select distinct p.tablespace_name from dba_tablespaces p,
    dba_xml_tables x, dba_users u, all_all_tables t where
    t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
    and x.owner=u.username;
  Transporting tablespaces with XMLTypes has the following limitations:
• The target database must have XML DB installed.
• Schemas referenced by XMLType tables cannot be the XML DB standard schemas.
• Schemas referenced by XMLType tables cannot have cyclic dependencies.
• XMLType tables with row level security are not supported, because they cannot be exported or imported.
• If the schema for a transported XMLType table is not present in the target database, it is imported and registered. If the schema already exists in the target database, an error is returned unless the ignore=y option is set
• If an XMLType table uses a schema that is dependent on another schema, the schema that is depended on is not exported. The import succeeds only if that schema is already in the target database.
• Advanced Queues:Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
• SYSTEM Tablespace Objects:You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences
• Opaque Types:Types whose interpretation is application-specific and opaque to the database (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation
• Floating-Point Numbers:BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump
4.  Compatibility Considerations for Transportable Tablespaces:
   When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 11g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database
5. Example
1. Determine if Platforms are Supported and Determine Endianness on both source and target
   
   SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
   FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
   WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
2. Pick a Self-Contained Set of Tablespaces
   TTS requires all the tablespaces, which we are moving, must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.E.x for tablespaces  sales_1,sales_2
   
   
   SQL >EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
   
   If it were not self contained you should either remove the dependencies by dropping/moving them or include the tablespaces of segments into TTS set to which migration set is depended
3. Generate a Transportable Tablespace Set
   SQL> ALTER TABLESPACE sales_1 READ ONLY;
   SQL> ALTER TABLESPACE sales_2 READ ONLY;
   
   expdp   system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = sales_1,sales_2
   
   or we  can  export  it with exp (old version )
   exp system/password  file=expdat.dmp  transport_tablespace=y tablespaces=sales_1,sales_2
   
   If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter
   expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir     TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y
   
   If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1 and sales_2 tablespaces
   
   RMAN TARGET /
   
   RMAN> CONVERT TABLESPACE sales_1,sales_2
   
   2> TO PLATFORM 'Microsoft Windows NT'
   3> FORMAT '/temp/%U';
   
   
4. Transport the tablespace
• If both the source and destination are files systems, you can use:
  Any facility for copying flat files (for example, an operating system copy utility or ftp)
  The DBMS_FILE_TRANSFER package
  RMAN
  Any facility for publishing on CDs
• If either the source or destination is an Automatic Storage Management (ASM) disk group, you can use:
  ftp to or from the /sys/asm virtual folder in the XML DB repository
  
  The DBMS_FILE_TRANSFER package
  RMAN
• If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, you must do so now. This example assumes that you have completed the following steps before the transport:
1. Set the source tablespaces to be transported to be read-only
2. Use the export utility to create an export file (in our example, expdat.dmp).
3. Datafiles that are to be converted on the target platform can be moved to a temporary location on the target platform. However, all datafiles, whether already converted or not, must be moved to a designated location on the target database.Now use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs, as shown in this hypothetical example:
   
   RMAN> CONVERT DATAFILE
   2> '/hq/finance/work/tru/tbs_31.f',
   3> '/hq/finance/work/tru/tbs_32.f',
   4> '/hq/finance/work/tru/tbs_41.f'
   5> TO PLATFORM="Solaris[tm] OE (32-bit)"
   6> FROM PLATFORM="HP TRu64 UNIX"
   7> DB_FILE_NAME_CONVERT=
   8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
   9> PARALLELISM=5; 
5. Import the Tablespace Set
   
   IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
   TRANSPORT_DATAFILES=
   /salesdb/sales_101.dbf,
   /salesdb/sales_201.dbf
   REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
   
   If required, put the tablespaces into read/write mode
   ALTER TABLESPACE sales_1 READ WRITE;
   ALTER TABLESPACE sales_2 READ WRITE;

See also:

Oracle Cross-Platform Migration Using Rman (Convert Database )

Oracle Background processes

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes

You can see the Oracle background processes with  following queries
select  * from V$BGPROCESS ;
or
select *  from   v$session where  type ='BACKGROUND';

An Oracle instance can have many background processes; not all are always present

SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.


DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

ARCH - Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.

CJQ - Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main program (the coordinator) and slave programs that the coordinator executes. The parameter job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.
CKPT - Checkpoint process writes checkpoint information to control files and data file headers.

CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.

FMON - The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.

LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

LMON - Lock Manager process

MMON - The Manageability Monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis

MMNL - The Memory Monitor Light  process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

MMAN - The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize  operations

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel query operations.

RBAL - This is the ASM related process that performs rebalancing of disk resources controlled by ASM. 

ARBx - These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter

ASMB - The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

CTWR -  Change Tracking Writer (CTWR) which works with the new block changed tracking features  for fast RMAN incremental backups

WMON - The "wakeup" monitor process

RVWR - Recovery Writer ( RVWR) introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks

Data Guard/Streams/replication Background processes

DMON - The Data Guard Broker process.


SNP - The snapshot process.

MRP - Managed recovery process - For Data Guard, the background process that applies archived redo log to the standby database.

ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB - is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS - Remote File Server process - In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN - Queue Monitor Process (QMNn) - Used to manage Oracle Streams Advanced Queuing.


Oracle Real Application Clusters (RAC) Background Processes


DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for instance process failures.

LCKx - This process manages the global enqueue requests and the cross-instance broadcast. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).

LMON - The Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage the global enqueues and the resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)

LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance.

LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.
The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.



The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache Service resources by:
Managing the resource requests and cross-instance call operations for the shared resources.
Building a list of invalid lock elements and validating the lock elements during recovery.
Handling the global lock deadlock detection and Monitoring for the lock conversion timeouts