Category Archives: Oracle

ORACLE TUTORIALS

Oracle is most widely used Oracle database in the world. It has the capacity to handle large database and high transaction

Here I am presenting the Oracle tutorials on Various Oracle products

  1. Oracle database
  2. Oracle sql/plsql
  3. Oracle application
  4. Weblogic
  5. Performance tuning.

These articles will be  really help in understanding the oracle products and getting started on Oracle system

How to find RAC interconnect information

Last updated on November 28th, 2015 at 12:25 pmHere are a few ways to How to find RAC interconnect information 1. Using the dynamic view gv$cluster_interconnects:SQL> select * from gv$cluster_interconnects; INST_ID NAME IP_ADDRESS IS_ SOURCE ———- ————— —————- — ——————————- 1 en12 192.158.100.65 NO Oracle Cluster Repository 2 en12 192.158.100.66 NO Oracle Cluster Repository 2. Using the clusterware command oifcfg: $oifcfg getif en10 10.127.149.0 global … Continue reading How to find RAC interconnect information »

how to rebuild the wf_java_deferred queue

Last updated on December 21st, 2015 at 05:44 amFollowings steps are to be followed to rebuild the queue 1. Check to see what records will be backed up on the wf_queue_temp_jms_table backup table. select wfjd.corr_id corrid, msg_state state, count(*) COUNT from applsys.aq$wf_java_deferred wfjd where msg_state IN(‘READY’, ‘WAIT’) group by corr_id, wfjd.msg_state; 2). Shutdown the Workflow Agent Listener Service and backup the records on the WF_JAVA_DEFERRED … Continue reading how to rebuild the wf_java_deferred queue »

ADODFCMP Utility

Last updated on December 21st, 2015 at 05:40 amThe ODF Comparison utility is used to compare the data model of a customer’s data to a standard set of data model files from the current Oracle Application release. It can optionally modify the database to match the standard data model. Each application consists of certain building blocks. There is an object descriptor file (ODF) describing the … Continue reading ADODFCMP Utility »

Single Client Access Name (SCAN)

Last updated on August 27th, 2016 at 08:41 amWhat is Single Client Access Name (SCAN) It is a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes in the cluster. … Continue reading Single Client Access Name (SCAN) »

How to recover corrupt blocks using RMAN

Last updated on December 21st, 2015 at 05:34 am1. Recovering Data blocks By Using All Available Backups Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15; Recover multiple blocks in single command RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15 DATAFILE 2 BLOCK 10; 2. Recovering Data blocks Using Selected Backups Run the BLOCKRECOVER … Continue reading How to recover corrupt blocks using RMAN »

How to identify corrupts blocks in the database

Last updated on December 21st, 2015 at 05:35 am1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks. RMAN> backup validate check logical database; “CHECK LOGICAL” option is used to identify both Physical and Logical Block Corruptions. Select the view to identify the corrupted blocks detected by RMAN. SQL> select * from v$database_block_corruption; Please note, After a corrupt block is … Continue reading How to identify corrupts blocks in the database »

JDBC connection scripts

Last updated on November 28th, 2015 at 12:34 pmOracle  OC4J connect to database using JDBC connection. Here is the list of some good scripts on JDBC connection A) To find total number of open database connections for a given JVM PID SELECT s.process, Count(*) all_count FROM v$session s WHERE s.process IN () GROUP BY s.process B) To find number of database connections per JVM that were … Continue reading JDBC connection scripts »

How to create recovery catalog and register database in catalog

Last updated on December 21st, 2015 at 04:46 amRecovery catalog is a very important registry. Control file registry get rotated after some time so you lost many backup information. By storing backup information in recovery catalog ,you get rid of this problem Also it provides a central repository where you can find all the backup information. Here are the steps on How to create recovery catalog … Continue reading How to create recovery catalog and register database in catalog »

FNDCPASS and AFPASSWD

Last updated on November 28th, 2015 at 12:32 pmChange APPS/APPLSYS Passwords FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS Note: AutoConfig must be run and all application tier services restarted after changing the APPS password. Change Oracle EBS Schema Password (e.g., GL, FA, AR, etc.) FNDCPASS apps/apps 0 Y system/manager ORACLE Change All Oracle EBS Schema Passwords (e.g., GL, FA, AR, etc.) FNDCPASS apps/apps 0 Y … Continue reading FNDCPASS and AFPASSWD »

How Table Monitoring and STATISTICS_LEVEL parameter are connected from 10g onwards

Last updated on July 17th, 2015 at 06:05 pmBefore Oracle 10g -Automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on table. Depending on the MONITORING flag, the GATHER_STATS_JOB job collected “GATHER EMPTY” and “GATHER STALE” on the flagged objects. 10g onwards -The MONITORING and NOMONITORING keywords are deprecated and will be ignored.Table-monitoring feature is … Continue reading How Table Monitoring and STATISTICS_LEVEL parameter are connected from 10g onwards »

How to find how much space is actually used by the lobsegments

Last updated on November 28th, 2015 at 12:35 pmLet us take the example of FND_LOBS tables in Oracle Apps Database.FND_LOBS has LOB datatype column FILE_DATA How to  find how much space is actually used by the lobsegments Query to find the actual bytes used by the lob datatype select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS; SUM(DBMS_LOB.GETLENGTH(FILE_DATA)) ———————————- 13313 The total space allocated in the extents could be … Continue reading How to find how much space is actually used by the lobsegments »