How to Customize Autoconfig in Oracle Apps 11i on Unix

Step1: identify the configuration file you would like to customize

for example jserv.properties

Step2: Find out the template file related to jserv.properties

Use the following script to find out the template file.

$AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE target=$IAS_HOME/Apache/Jserv/etc/jserv.properties

For Unix the output of above command is $FND_TOP/admin/template/jserv_ux_ias1022.properties

Note: out put varies based on your OS

Step3: Create custom directory under $FND_TOP/admin/template

Ex: cd $FND_TOP/admin/template

Mkdir custom

Step 4: copy the template identified in step 2 to custom directory

Cp $FND_TOP/admin/template/jserv_ux_ias1022.properties $FND_TOP/admin/template/custom

Step 5: Adding Customization to template file.

Add customization to $FND_TOP/admin/template/custom/jserv_ux_ias1022.properties file. Save and exit.

Step6: Run autoconfig to reflect the changes.

Autoconfig file can be found in $COMMON_TOP/admin/scripts/SID_Servername/adautocfg.sh

Depends on your environment above steps may vary , please ensure to implement this solution in test before making any changes to production.

After long time I got some time and have plan to post my leanings. Over the past one year I have learnt oracle Apps administration. Maintaining Apps is quite challenging and very interesting.

In nutshell Oracle apps is a perfect environment to learn Database and Web Servers administration.

Oracle Apps DBA is a huge task Where to start ? I would say start with Apps concepts

ORA-12514 TNS: listener could not resolve SERVICE_NAME given in connect descriptor

This can occur when you directly connecting to database or when you try to access remote database using dblink.

When you encounter this error first thing we should collect the following information from init.ora file.

1.DB_NAME

2.DB_DOMAIN

3.SERVICE_NAME

assume that your DB_NAME value is “smplorcl” ,DB_DOMAIN is “world” and SERVICE_NAME value is “smplorcl.world”

above values also can be obtained using show parameter command from SQLPLUS

Ex: sql> show parameter DB_NAME

Next you need to find out the current services registered with listener.

Lsnrctl services

LSNRCTL> services listener

1. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1545))

2. Services Summary...

3 .Service "smplorcl" has 1 instance(s).

4. Instance "smplorcl", status READY, has 1 handler(s) for this service...

5. Handler(s):

6. "DEDICATED" established:0 refused:0 state:ready

7. LOCAL SERVER

8. Service "smplorcl.world" has 1 instance(s).

9. Instance "smplorcl", status READY, has 1 handler(s) for this service...

10. Handler(s):

11."DEDICATED" established:0 refused:0 state:ready

12.LOCAL SERVER

Note : I have given lines numbers for each lane in listener services output.

Now compare your service name value you have obtained from init.ora is matching with lane number 8 value . If not

you need to make changes to init.ora or tnsnames.ora (if your init.ora has right service_name then you change your tnsnames.ora SERVICE_NAME parameter value or Vice versa)

this error also can occur when yo accessing remote databases using dblibk. To verify tnsname alias first test the tnsname alias by using it to connect in SQLPLUS directly . If you still get this error , that means you have issues with your tnsnames, first correct tnsnames issue.

If your SQLPLUS connection is successful then you need to verify db_link connection parameter values

query dba_db_links database table.

Select db_link,host from dba_db_links;  from the above output verify your host value is identical to your remote 

TNSNAMES ALIAS parameter values, verify the domain reference also , this should include “world” part in 

the domain name

ex: smplorcl.world.   Also try restarting listener and give some time for listener to register service names .

Hope my notes help you resolving ORA-12514 error.

Reference document URL. 

http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm

Undo Management

My Understanding on undo Tablespace and Rollback segments.

Undo table space concept released with Oracle 9 Version , prior to 9 DBA used to manage undo management through rollback segments. It was a cumbersome process for DBAs to manage rollback segments especially sizing rollback segments.

From Oracle 9i onwards Oracle came up with a good solution called undo tablespace where DBA does not require to worry  Undo management.

But Oracle still have the rollback segment in 9i as well as in 10g and 11g  for backward compatibility. In 10g and 11g still oracle use rollback segment concept but this exclusively used by Oracle at the time of database creation. In 10g or 111g rollback segments are created and managed by oracle software and they are stored in SYSTEM tablespace, this is because at the time of database creation they may not be a undo tablespace.

Init.ORA Undo parameters .

The following undo parameter controls undo management .

UNDO_MANAGEMENT :  Possible values are 'AUTO' or 'MANUAL' , you basically speicy which undo management mode oracle database should use . If you specify AUTO database will be starting in automatic undo management mode.

UNDO_TABLESPACE  : You specify the tablespace name as a parameter value if your  undo management mode is automatic. It is not a mandatory parameter though, if you forget to specify undo tablespace name , database still start with out any startup errors but you still see errors/Warnings in the Alert log. If instance starts without undo tablespace user transaction will be using rollback segment created in system to manage transaction undo data. Oracle strongly recommends not use Rollback segments created in System tablespace. Undo tableapce parameter can be modified using Alter system table space command.

UNDO_RETENTION : Undo retention specified in seconds , Number of seonds to keep expired transaction data( undo data). default value is 900 seconds, this can be changed using alter system command.

Will write more undo in the next posting.

Init.ora Data Dictionary Views

he following Data Dictionary Views provides complete list of init.ora file parameters currently set /used in the databases. for all RAC environments query gv$parameter, gv$parameter2, gv$spparameter other environments query  v$parameter ,v$parameter2, v$spparameter

 

List of currently supported parameters can be vies using the following query

 

SELECT name, value
FROM gv$parameter
ORDER BY 1;

 

I strongly recommend to use GV$ views to view all data dictionary data in NON-RAC Environment also, because when you start working on RAC environment you do not have to learn or query a different views.

 

List of all parameters those you can modify using alter system command

 

SELECT name, value
FROM gv$parameter
WHERE isdefault = 'FALSE'
ORDER BY 1;

 

List of obsolete parameters in any environment can be found using

 

SELECT *  FROM gv$obsolete_parameter  ORDER BY 1;

 

You can also use v$spparameter data dictionary view for complete list of parameters set by SPFILE and PFILE

Know your INIT.ora

The "init.ora" file is a parameter file that is used to startup the database. based on the database setup we can modify these parameters. The "init.ora" file is a template file is supplied with oracle software.

This file can be found in

the "ORACLE_HOME/dbs" directory on unix platforms and on Windows this can be found in ORACLE_HOME/database.

 

Filename would be initORACLE_SID.ora file ( here SID means System Identification)

 

How do i find out if my database using IFILE or SPFILE

 

connect as  sqlplus "as sysdba"

at the SQL prompt

SQL> show parameter ifile;

 

SQL> show parameter spfile

 

 

 

Sample Init.ora File

#####################################################################
# Common parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8
#####################################################################
# Identification & Control Files
#####################################################################
db_name = orabase
control_files = (/app/oracle/product/orabase/control.ctl
/app/oracle/product/databases/orabase/control.ctl)
#####################################################################
# Version Specific
#####################################################################
compatible = 10.2.0
#####################################################################
# Platform Specific
#####################################################################
disk_asynch_io = false
filesystemio_options = directio
use_indirect_data_buffers = true
#####################################################################
# Security, Audit and Resource Limit
#####################################################################
audit_trail    = DB
resource_limit = true
#####################################################################
# NLS Settings
#####################################################################
nls_date_format = DD-MON-YYYY
#####################################################################
# Archive & Redo Logs
#####################################################################
#log_archive_dest = /app/oracle/product/flash_recovery_area/arch
log_buffer                = 4194304 
log_checkpoint_interval   = 1050624
log_archive_max_processes = 4 
#_log_simultaneous_copies = 48
archive_lag_target        = 1800
#####################################################################
# Dump & Output Directories
#####################################################################
audit_file_dest      = /app/oracle/product/admin/orabase/adump
background_dump_dest = /app/oracle/product/admin/orabase/bdump
core_dump_dest       = /app/oracle/product/admin/orabase/cdump
user_dump_dest       = /app/oracle/product/admin/orabase/udump
utl_file_dir         = /app/oracle/product/admin/orabase/output
#####################################################################
# DB & Instance Limits
#####################################################################
db_files     = 512
sessions     = 4000
processes    = 1500 
transactions = 200
#####################################################################
# Process & Session Specific
#####################################################################
open_cursors            = 4000
open_links              = 10
session_cached_cursors  = 40
session_max_open_files  = 30
sort_area_retained_size = 1048576
sort_area_size          = 4194304
#####################################################################
# Buffer Pool
#####################################################################
db_block_buffers       = 300000
db_block_checksum      = true
db_block_checking      = true_db_block_lru_latches  = 2048
_db_block_hash_latches = 65536

#buffer_pool_keep    = (buffers:120000, lru_latches:150)
#buffer_pool_recycle = (buffers:55296, lru_latches:48)
#####################################################################
# Shared Pool & Other "Pools"
# Sort, Hash Joins, Bitmap Indexes
#####################################################################
java_pool_size = 0
large_pool_size = 500M
pga_aggregate_target = 1024M
shared_pool_size = 750M
shared_pool_reserved_size = 96M
streams_pool_size = 0

_shared_pool_reserved_min_alloc = 4000
#####################################################################
# UNDO
#####################################################################
# use automatic undo
undo_management = 'auto'
# which tablespace
undo_tablespace = 'undo_t1'
# keep 8 hours (8*3600)
undo_retention  = 28000
_undo_autotune  = false
#####################################################################
# Parallelism
#####################################################################
parallel_max_servers            = 32
parallel_min_servers            = 0
parallel_threads_per_cpu        = 8
parallel_execution_message_size = 65535
recovery_parallelism            = 16

#parallel_automatic_tuning      = true (deprecated in 10g)
#####################################################################
# Shared Server
#####################################################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orabaseXDB)"
#max_dispatchers    = 40
#max_shared_servers = 150
#mts_service        = orabase
#shared_servers     = 8
#####################################################################
# Job Processing
#####################################################################
job_queue_processes = 8
aq_tm_processes = 1
#####################################################################
# Miscellaneous
#####################################################################
background_core_dump          = partial
db_file_multiblock_read_count = 16
fast_start_parallel_rollback  = FALSE
optimizer_index_caching       = 80
optimizer_index_cost_adj      = 10
recyclebin                    = off

_disable_selftune_checkpointing = true
#####################################################################
# Undocumented Parameters & Temporary Fixes
#####################################################################
_b_tree_bitmap_plans            = false
_shared_pool_reserved_min_alloc = 4000
_small_table_threshold          = 2560
_optim_peek_user_binds          = false
#####################################################################
# Events
#####################################################################
#event = "600 trace name library_cache level 10"
# tracing PMON actions
#event = "10500 trace name context forever"
#event = "10196 trace name context forever"
#event = "10246 trace name context forever"
##event = "4031 trace name errorstack level 10"
#event = "10511 trace name context forever, level 2"
#event = "32333 trace name context forever, level 8"

 

 

Opatch Howto

How to specify different inventory location?

OPatch expects the oraInst.loc file in the standard location namely  /var/opt/oracle/oraInst.loc  however there are some systems where this file is located in a different location other than default location

In order to specify a different inventory location  when you apply a patch to oracle database you can use  inventory API flag invPtrLoc (note the case)

opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc .

 

Errors related Ora inventory locations are

“OPatch would error out saying "Unable to access inventory"  OR "Couldn't find required file liboraInstaller”

 

What is the Default Storage location of OPATCH

Opatch is usually located in the $ORACLE_HOME/OPatch directory.

 

 Opatch logs are placed as follows:

For opatch version 1.0.0.0.xx, logs are written into the patch storage area under$ORACLE_HOME/.patch_storage/ and the log in this case starts with

"_Apply_.log"

For the newer opatch versions for products installed with OUI release 10.2 and after, logs are written into the cfgtoollogs/opatch area under $ORACLE_HOME/ and the log in this case starts with

"opatch.log"