RMAN-06457 UNTIL SCN is ahead of last SCN in archived logs

#!/bin/ksh

export ORACLE_SID=OIDTEST

rman msglog restore_OIDTEST.log append<< EOF
connect target sys/@OID
connect auxiliary /
run
{
allocate auxiliary channel ch01 type disk;
allocate auxiliary channel ch02 type disk;
allocate auxiliary channel ch03 type disk;
allocate auxiliary channel ch04 type disk;
set until time "to_date('06-AUG-2012 10:00:00','DD-MON-YYYY HH24:MI:SS')";
duplicate target database to OIDTEST;
}
exit
EOF



RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/07/2012 11:12:13
RMAN-06457: UNTIL SCN (488979215) is ahead of last SCN in archived logs (486144524)



When I looked at the SCN's they all still reflecting the old chnages/date and my restore is trying to go beyond these SCN's.


SQL> select thread#, status, enabled, checkpoint_time, checkpoint_change# from v$thread;

   THREAD# STATUS ENABLED  CHECKPOIN CHECKPOINT_CHANGE#
---------- ------ -------- --------- ------------------
         1 OPEN   PUBLIC   03-AUG-12          486144524
         2 OPEN   PUBLIC   05-AUG-12          488215686
         3 OPEN   PUBLIC   04-AUG-12          486909043


Do a check point.

SQL> alter system checkpoint;

System altered.

SQL> select thread#, status, enabled, checkpoint_time, checkpoint_change# from v$thread;

   THREAD# STATUS ENABLED  CHECKPOIN CHECKPOINT_CHANGE#
---------- ------ -------- --------- ------------------
         1 OPEN   PUBLIC   07-AUG-12          490100101
         2 OPEN   PUBLIC   07-AUG-12          490100101
         3 OPEN   PUBLIC   07-AUG-12          490100100


Now check what you have in the v$log. If they still reflect the old Change#'s then switch logfiles until all the threads are changed.




SQL> alter system switch logfile;

System altered.



SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


SQL>  alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

System altered.


SQL>

Restart the duplication process..happy duplicating..

DG database recovery

1.Backup  ---
rman target /  

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset archivelog from time "to_date('21.04.2014 21:00:00','DD.MM.YYYY HH24:Mi:SS')"   until time "to_date('21.04.2014 22:13:14','DD.MM.YYYY HH24:Mi:SS')" format '/home/lingesan/arch_%U';
}

2.Copy the backup pieces and   new standby controlfile to DG  

3.Restore  ----  
run
{
catalog start with '/home/lingesan';
allocate channel c1 type disk ;
restore archivelog from time "to_date('21.04.2014 21:00:00','DD.MM.YYYY HH24:Mi:SS')"   until time "to_date('21.04.2014 22:13:14','DD.MM.YYYY HH24:Mi:SS')";
}

ORA-01586 ORA-39701 database must be mounted EXCLUSIVE and not open for this operation

Dropping the database :

 
Mount the database in restrict mode:
 SQL> startup mount restrict;
ORACLE instance started.


Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

What if you get the below error:


SQL> startup mount  restrict;
ORACLE instance started.


Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

Even the startup upgrade will fail:

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             704646848 bytes
Database Buffers         2348810240 bytes
Redo Buffers              151146496 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 46501
Session ID: 652 Serial number: 3


Then mostly likely you are destroying a RAC database and you need to change the cluster_database=FALSE.

SQL> alter system set cluster_database=FALSE scope=spfile;


System altered.

SQL> startup mount restrict;
ORACLE instance started.


Redo Buffers              151146496 bytes
Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

Direct NFS

You can get info from the following views:
– View servers accessed using Direct NFS
select * from v$dnfs_servers;
– View files currently open using Direct NFS
select * from v$dnfs_files;
– View open network paths or channels to servers for which Direct NFS is providing files
select * from v$dnfs_channels;
– View performance statistics for Direct NFS
select * from v$dnfs_stats;
Also you will see in the alert log on startup:
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 2.0
Disable DNFS
Shutdown DB
On all hosts:
cd $ORACLE_HOME/lib
mv libodm11.so_bak libodm11.so
Restart Instance
For Completeness, to enable:
Enable DFNS
Shutdown DB
On all hosts:
cd $ORACLE_HOME/lib
mv libodm11.so libodm11.so_bak
ln –s libnfsodm11.so libodm11.so
Restart Instance
Useful SQL
– View servers accessed using Direct NFS
select * from v$dnfs_servers;
– View files currently open using Direct NFS
select * from v$dnfs_files;
– View open network paths or channels to servers for which Direct NFS is providing files
select * from v$dnfs_channels;
– View performance statistics for Direct NFS
select * from v$dnfs_stats;

Known Issue
Getting the following message in alert log and instance not starting:
Direct NFS: please check that oradism is setuid
To correct, the following permissions are needed on:
ls -la $ORACLE_HOME/bin/oradism
-rwsr-x— 1 root oinstall 72416 Sep 11  2008 /u01/app/oracle/product/11.1/db/bin/oradism
If NOT this, shutdown DB and change permission as follows:
sudo su
cd /u01/app/oracle/product/11.1/db/bin
chmod 750 oradism
chmod u+s oradism
Restart instance

Changing Database Character Set (Oracle 11g)

February 14, 2013

1.      Install csscan Utility

The first step is to install the csscan utility in the database. Follow note 745809.1 for this. Please run the following script in database:

SQL> @?/rdbms/admin/csminst.sql

This script will create the CSMIG user account in the database and the script creates the user and assigns a default tablespace of SYSTEM. A number of CSM$ tables are created in the CSMIG schema.

2.      Running the csscan utility

The csscan will do a full table scan of all the tables in the database and this could cause a performance impact. So run the csscan at an appropriate time period.

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib $ csscan \”sys/xxxxx as sysdba\” FULL=Y FROMCHAR=AL32UTF8 TOCHAR=WE8MSWIN1252 LOG=win1252 CAPTURE=Y ARRAY=1000000 PROCESS=2

csscan will produce three files with the . err, .txt and .out extensions and the name of each will depend on the value used for the LOG parameter.

• The .out file is basically a log of all the scan activity performed .
• The .txt file is the most important file as it has the Database Scan Summary Report
• The .err file will list all the ‘exception’ rows – rows with lossy data or truncated data or convertible data.

The Conversion Summary section

The Conversion Summary section of the .txt file is the most important section of the Scan Summary Report and has 4 columns showing the possible status of the data

• Changeless: All the characters use the same codepoints in the new characterset as compared to the old characterset and no action needs to be taken. The csalter or ALTER DATABASE CHARACTERSET command will handle that for us.
• Convertible: The data is valid, but the characters will change to a different code point in the new characterset. In this case for any application data reported as convertible, we need to export the data, truncate the tables or delete the data, change the characterset using csalter or ALTER DATABASE and then import after characterset has been changed.
• Truncation: In case of going from a single byte characterset to a multiple byte characterset, the data will grow and we will need to increase the column size in this case. In this case, the .err file will tell us by how much the data is going to grow post conversion and we need to accordingly modify the column sizes of the affected tables.
• Lossy: If we see data in the Lossy column, then it means that the either the data is not a valid code point for the current characterset, or the proposed target characterset does not define that data. If we see data listed as lossy, we CANNOT use Export/Import to change characterset, but need to use either ALTER DATABASE CHARACTERSET command for 8i and 9i and csalter for 10g and upwards.

[Application Data Conversion Summary]
 
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                10,538,740,813                0                0                0
CHAR                       922,484,552                0                0                0
LONG                            34,823                0                0                0
CLOB                        76,562,599                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                   11,537,822,787                0                0                0

3.      Performing the actual characterset conversion.

Shutdown and startup the database in restricted mode. Run the csalter.plb which will prompt us to enter a confirmation.

Successful Execution:

SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old   6:     if (UPPER(‘&conf’) <> ‘Y’) then
new   6:     if (UPPER(‘Y’) <> ‘Y’) then
Checking data validity…
begin converting system objects
20 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
737 rows in table SYS.WRH$_SQLTEXT are converted
167 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
121 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
21134 rows in table SYS.WRH$_SQL_PLAN are converted
1 row in table SYS.RULE$ is converted
34 rows in table SYS.WRI$_ADV_OBJECTS are converted
178 rows in table SYS.METASTYLESHEET are converted
4971 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
PL/SQL procedure successfully completed.
Alter the database character set…
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

Failed Execution

SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old   6:     if (UPPER(‘&conf’) <> ‘Y’) then
new   6:     if (UPPER(‘Y’) <> ‘Y’) then
Checking data validity…
Unrecognized convertible data found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>

This fails to perform the characterset Conversion.

4.      Alternative Method

Shutdown the database and start it in restricted mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area 5277921280 bytes
Fixed Size                  2213096 bytes
Variable Size            3556772632 bytes
Database Buffers         1677721600 bytes
Redo Buffers               41213952 bytes
Database mounted.
Database opened.

Perform the character set conversion:

SQL> select name from v$database;
NAME
———
DEV

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252 ;
Database altered.

SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;
VALUE
—————————————-
WE8MSWIN1252

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5277921280 bytes
Fixed Size                  2213096 bytes
Variable Size            3556772632 bytes
Database Buffers         1677721600 bytes
Redo Buffers               41213952 bytes
Database mounted.
Database opened.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

VALUE
—————————————-
WE8MSWIN1252

SQL> exit

5.      Run the following commands

SQL>@$ORACLE_HOME/rdbms/admin/catalog
SQL>@$ORACLE_HOME/rdbms/admin/catproc
Reload the datapump utility by executing the below scripts: SQL>@$ORACLE_HOME/rdbms/admin/catmet2.sql SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

adlnktools.sh. Fails with return code = .46 (/usr/lib/libXtst.so.6: undefined reference to `__stack_chk_fail@GLIBC_2.4)

Recently i got a issue when Application Relinking is going on. here is the issue description in detail.

Error Message: Error while running adlnktools.sh. return code = .46
bash-3.2$ sh adlnktools.sh
adlnktools.sh started
Log file located at /test/mtlog/test_redhat39/logs/ora/10.1.2/install/make_03231238.log

Error while running adlnktools.sh.
return code = .46
Please check logfile located at /test/mtlog/test_redhat39/logs/ora/10.1.2/install/make_03231238.log

Logfile Error :
==================
/usr/lib/libXtst.so.6: undefined reference to `__stack_chk_fail@GLIBC_2.4′
/usr/lib/libXtst.so.6: undefined reference to `__fprintf_chk@GLIBC_2.3.4′
/usr/lib/libXtst.so.6: undefined reference to `__sprintf_chk@GLIBC_2.3.4′
collect2: ld returned 1 exit status
make: *** [rwrun] Error 1
Failed linking target proxy_install runm_install server_install cgi_install cli_install conv_install qv_install on Tue Dec 23
Solution Offerd:
===================
Login as root
cd /usr/lib
rm libXtst.so.6
ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6
Prior
——–
lrwxrwxrwx 1 root root 16 Jan 15 17:59 libXtst.so.6 -> libXtst.so.6.1.0
Now
———
lrwxrwxrwx 1 root root 29 Jan 19:44 libXtst.so.6 -> /usr/X11R6/lib/libXtst.so.6.1
This Resolved the issue. Relink has been Done after this.
Ref Note: Oracle Forms Upgrade to 10.1.2.3 fails with error /usr/lib/libXtst.so.6: undefined reference (Doc ID 1120527.1)
Happy Troubleshooting.

Patchset and PSU and CPU

Based on my experiences with 11gR2, looks like PSU is the way to go.   For example  we  applied PSU3 to resolve mutex issues since there was no one-off fix  w/o  the PSU route.  I also thought that PSUs were more flexible than I initially assumed because  when we encountered another bug(10190759), the bug fix was available only for  11.2.0.2  (w/o PSU 3).  Oracle still recommended applying this patch to 11.2.0.2.3(with PSU3)  and it worked.

Patch sets and PSU

PSU’s(Patch set updates) are patch sets but with some major differences with respect to regular patch sets.

PSUs are generally low risk and do not contain fixes to bugs that require configuration changes or impact database components like optimizer. To achieve this low risk behavior, Each PSU is limited from 25 to 100 new bug fixes. PSU’s are also well tested by Oracle compared to one off patches.

PSUs are referenced by their 5th place in the Oracle version numbers which makes it easier to track ( (e.g. 10.2.0.3.1) and will not change the version of oracle binaries (like sqlplus, exp/imp etc.) As of now (10.2, 11,2) , the best way to  determine the PSU version installed  is to use opatch utility using the command below

opatch lsinv -bugs_fixed | grep -i  PSU

From the database , you can execute the following query(Assuming that catbundle.sql  was executed when PSU was installed). The last 2 columns will provide information about the PSU.

select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;

In summary PSU’s  contain fix for bugs that cause

• Instance crash
• Wrong results
• Data Corruption

PSU’s  do not contain fix for bugs  that may result in

• Dictionary changes
• Major Algorithm changes
• Architectural changes
• Optimizer plan changes

Regular patch sets on other hand include major bug fixes and most of the time requires re-certification. The importance of PSU is diminished once a regular patch set is released for a given version as they tend to be more security related fixes as CPU is part of PSU

PSU and CPU

PSUs  contain CPU’s and are released every quarter (like CPU’s) ; In other words  Critical Patch Update (CPU) is a subset of the Patch Set Update (PSU). CPU’s are built on the base Patch Set version (e.g. 10.2.0.3) whereas PSU are built on the base of the previous PSU (e.g. 10.2.0.3.1)

A PSU can always be applied over any CPU where as applying a CPU over a PSU will roll back the PSU (at least for now). Therefore it is easy to go from CPUs to PSUs and hard to go back to CPUs  from PSUs.

JVM Tuning (Garbage Collection) in Oracle Apps 11i

Important thing missing from all these notes (for some one like me who is new to Java) is basics of Garbage Collection, Generation and how to read GC output.
In this post I’ll start with basics of JVM GC (Garbage Collection) and then in next post apply this theory for real time performance issues w.r.t. JVM (11i Java Virtual Machine) .
Garbage - Java object is considered garbage when it can no longer be reached from any pointer in the running program.
Generations – Memory in JVM is managed in terms of generation i.e. Young generation and tenured generation. Memory pool holding object of different ages like young, tenured. If a particular generation fills up, garbage collection occurs.
A. Young generation – Objects are initially allocated in Young generation (most of objects die here). When Young generation fills up, it causes Minor Garbage Collection. Any objects survived after Minor GC (Garbage Collection) are moved to Tenured Generation.  Minor Garbage collection is quick as compared to Full/Major GC.
B. Tenured generation – Surviving objects (from young generation) after minor garbage collection are moved to area called tenured generation, When tenured generation fills up it causes major collection (aka Full GC or Full Garbage Collection). Major collection is slow as it involves all live objects.
Garbage Collection (GC) – is program which clears garbage(dead java objects). Garbage Collection work on fundamental principle that majority of java objects die young (quickly after arriving in JVM). There are two kind of Garbage Collection Minor Garbage Collection and Major Garbage Collection (aka Full GC)
Example of Minor GC -  3824.164: [GC 196725K->141181K(209864K), 0.3295949 secs]
Example of Minor GC -  3841.051: [Full GC 150466K->87061K(217032K), 3.2626248 secs]
Pauses: is the time when application becomes unresponsive because garbage collection is occurring.
.
Understanding JVM parameter for 11i
Sizing the generation is very important in tuning JVM GC. Before jumping to Sizing generation (Young and Tenured) lets look at default 11i JVM parameters
In context file($APPL_TOP/ admin/ $CONTEXT_NAME.xml) default entry for JVM is like
<jvm_options oa_var=”s_jvm_options” osd=”Solaris”>-verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2-XX:+PrintGCTimeStamps -XX:+UseTLAB </jvm_options>
1. Above line represents JVM (OACoreGroup) size in 11i
2. -Xms128M, means start with 128MB heap size
3. -Xmx512M, means grow JVM heap size upto max size of 512 MB
4. -XX:NewRatio=2 is to control young generation i.e. ratio between young and tenured generation is 1:2 (i.e. if size of young generation is 50 MB then size of tenured generation should be approx. 100MB)
5. -XX:MaxPermSize=128M limit the permanent generation to 128M (permanent generation is part/area in tenured generation)
6. -XX:+UseTLAB represents to use thread-local object allocation
7. There are two more parameters (11i JVM uses default values) -XX:MinHeapFreeRatio=<minimum> & -XX:MaxHeapFreeRatio=<maximum> with default value of 40 & 70 resp. (for Solaris)
If percentage of free space in generation falls below 40%, size of generation will expand and if percentage of free space exceeds 70%, the size of generation will shrunk.
.
Various type of Garbage CollectorFrom JDK 1.4.2 there are total 4 type of collectors (prior to 1.4.2 it was just one collector i.e. default collector)
1. Default Collector: JDK prior to 1.4.2 uses default collector. If you don’t specify any parameter with JVM default is default collector.
2. ThroughPut Collector : This collector uses parallel version of young generation collector but Tenrured generation is collected in normal way. To set throughput collector use -XX:+UseParallelGC  so change
<jvm_options oa_var=”s_jvm_options” osd=”Solaris”>-verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB </jvm_options>
to
<jvm_options oa_var=”s_jvm_options” osd=”Solaris”>-verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC</jvm_options>
3. Concurrent Low Pause Collector : Concurrent Collector is used to collect tenured generation collection concurrently with execution of application. Parallel version of collector is used for young generation. To set Concurrent Low Pause Collector use -XX:+UseConcMarkSweepGC
like
<jvm_options oa_var=”s_jvm_options” osd=”Solaris”>-verbose:gc -Xmx512M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseConcMarkSweepGC</jvm_options>
4. Incremental low pause collector : This collector collects just portion of tenured generation at each minor garbage collection. To use Incremental low pause collector use
-Xincgc
If you are on JDK 1.4.2 with multi CPU try setting Concurrent Low Pause Collectoras Garbage Collector.
Thumb rule for Grabage Collection/ JVM tuning w.r.t. 11i1.Stay on latest JVM/JDK version where ever possible (latest certified with 11i is JRE 6, you should be at-least 1.4.2 and higher)
2. For OACoreGroup consider no more than 100 active users per JVM
3. There should NOT be more than 1 active JVM per CPU
4. Try to reduce GC (Garbage Collection) frequency (specially Major/Full GC). Play with various JVM parameters like (-Xmx, -Xms, -XX:MaxPermSize, -XX:NewRatio, -XX:+UseParallelGC/ -XX:+UseConcMarkSweepGC)
5. If you are on JDK 1.4.2 with multiple CPU middle tier, use Concurrent Low Pause Garbage Collector  by setting -XX:+UseConcMarkSweepGC with JVM
6. If you are using Oracle Configurator, assign dedicated JVM for configurator requests
7. Try setting JVM max size NOTgreater than 1 GB, (use multiple JVM’s of 512MB or 1024 MB), this is to reduce GC time (more heap size means more time in GC)
8. Minor GC should be occurring at interval long enough to allow many objects to die young (i.e. lot of objects should die between two minor GC).
9. Throughput (which is time NOT spent on GC) is inversely proportion to amount of memory. Higher the memory for JVM, more time for GC meaning low throughput.
10. Unless you have problems with pauses (time when application becomes unresponsive because garbage collection is occurring), try granting as much memory as possible to VM (128 to 512 is good start and fine tune as per load testing results)
.
How to find JDK version used by Apache/Jserv (JVM) in 11i ?
In context file search for parameter like s_jdktop
<JDK_TOP oa_var=”s_jdktop”>/oracle/apps/11i/vis11icomn/util/java/1.4/j2sdk1.4.2_04</JDK_TOP>
Where is JVM log location in 11i ?
$IAS_ORACLE_HOME/ Apache/ Jserv/ logs/ jvm/ OACoreGroup.0.stdout  (GC output)
$IAS_ORACLE_HOME/ Apache/ Jserv/ logs/ jvm/ OACoreGroup.0.stderr  (JVM Error)
.
How to read GC (JVM stdout) file ?
Example of JVM out file to understand Garbage Collection in 11i
3824.164: [GC 196725K->141181K(209864K), 0.3295949 secs]
3840.734: [GC 207741K->150466K(217032K), 0.3168890 secs]
3841.051: [Full GC 150466K->87061K(217032K), 3.2626248 secs]
3854.717: [GC 155413K->97857K(215568K), 0.2732267 secs]
3874.714: [GC 166209K->109946K(215568K), 0.3498301 secs]
1. Line 1,2 4 and 5 are example of Minor Collection
2. Line 3 (Full GC) is example of Major Collection
3. First entry in each line is time in seconds since JVM started, To find out time between two GC (Garbage Collection) just subtract second entry from first i.e. (3840.734 – 3824.164 = 16.57 seconds)
4. 196725K->141181K in first line indicates combined size of live objects before and after Garbage Collection (GC)
5. (209864K) in first line in parenthesis, represents object after minor collection that aren’t necessarily alive but can’t be reclaimed, either because they are directly alive, or because they are referenced from objects in tenured generation.
6. 0.3295949 secs in first line represents time taken to run minor collection.
7. Full GC in line three represents Full Garbage Collection or Major Collection

CUSTOM_TOP (JPL_TOP) Creation Steps in Oracle Apps (R12)

CUSTOM_TOP (JPL_TOP) Creation Steps in Oracle Apps (R12)

STEP 1:

This document gives the clear steps to create the JPL_TOP (CUSTOM_TOP) in R12 Oracle Application.  The following need to be created in R12 Oracle Application for JPL_TOP (CUSTOM_TOP).

SCHEMA NAME:       JPL
TOP NAME:                 JPL_TOP
Application:                  JPL Custom Application
Data Group:                 JPL Group
Request Group:             JPL Request Group
Menu:                            JPL_CUSTOM_MENU
Responsibility:               JPL Custom

STEP 2:

Make the directory structure for your JPL_TOP (CUSTOM_TOP) custom application files.

i)                   Login as user applmgr in linux,

ii)                Write a shell script program with following lines for directory creation,

[applmgr@jpl shell_script]$ vi jpl_top_creation.sh

cd $APPL_TOP
mkdir jpl
mkdir jpl/12.0.0
mkdir jpl/12.0.0/admin
mkdir jpl/12.0.0/admin/sql
mkdir jpl/12.0.0/admin/odf
mkdir jpl/12.0.0/sql
mkdir jpl/12.0.0/bin
mkdir jpl/12.0.0/reports
mkdir jpl/12.0.0/reports/US
mkdir jpl/12.0.0/forms
mkdir jpl/12.0.0/forms/US
mkdir jpl/12.0.0/lib
mkdir jpl/12.0.0/out
mkdir jpl/12.0.0/log

Run the Shell script program as following,

[applmgr@jpl shell_script]$ jpl_top_creation.sh

Now the directory structure was created by the above shell script program.

Screenshot:

STEP 3:

Put the Entry for JPL_TOP (CUSTOM_TOP) in $CONTEXT_FILE as following,

[applmgr@jpl appl]$ echo $CONTEXT_FILE

/u01/oracle/VIS/inst/apps/VIS_jpl/appl/admin/VIS_jpl.xml

[applmgr@jpl appl]$ vi $CONTEXT_FILE

Put the Entry as following  and Save the $CONTEXT_FILE,

<AU_TOP oa_var="s_autop" oa_type="PROD_TOP" oa_enabled="FALSE">/u01/oracle/VIS/apps/apps_st/appl/au/12.0.0</AU_TOP>

<JPL_TOP oa_var="s_jpltop" oa_type="PROD_TOP" oa_enabled="FALSE">/u01/oracle/VIS/apps/apps_st/appl/jpl/12.0.0</JPL_TOP>

Screenshot:

STEP4:

Stop all middle tier services in R12 Oracle Application.

[applmgr@jpl appl]$ cd $ADMIN_SCRIPTS_HOME

[applmgr@jpl scripts]$ adstpall.sh  apps/apps

Database and Listener should be in up status, then Run the Autoconfig as following,

[applmgr@jpl appl]$ cd $ADMIN_SCRIPTS_HOME

[applmgr@jpl scripts]$ adautocfg.sh  apps/apps

Screenshot:

Once this autoconfig completed successfully we need to check whether environment variable set correctly for $JPL_TOP.  Open the new terminal and check the $JPL_TOP (CUSTOM_TOP) as following,

[oracle@jpl ~]$ su - applmgr

Password:

[applmgr@jpl ~]$ echo $JPL_TOP

/u01/oracle/VIS/apps/apps_st/appl/jpl/12.0.0

After this, Start all middle tier services by executing following,

[applmgr@jpl appl]$ cd $ADMIN_SCRIPTS_HOME

[applmgr@jpl scripts]$ adstrtal.sh  apps/apps

STEP5:

Create Tablespace for JPL_TOP  through sqlplus as sys user  by following,

SQL> create tablespace JPL datafile '/u01/oracle/VIS/db/apps_st/data/JPL01.dbf' size 500M;

Tablespace created.

STEP6:

Create new Database user for JPL_TOP through sqlplus as sys user by following,

SQL> create user JPL identified by jpl default tablespace JPL temporary tablespace temp1 quota unlimited on JPL;

User created.

Grant the following privileges to JPL user,

SQL> grant connect, resource to JPL;

Grant succeeded.

Connect the JPL user and check the User as following,

SQL> connect jpl/jpl

Connected.

STEP7:

Oracle database user need to be registered in Oracle Application, do the following for Oracle database user Registration,

Naviate to Security-->Oracle-->Register

Database User Name = JPL
Password = JPL
Privilege = Enabled
Install Group = 0
Description = JPL Custom Application User

Screenshot:

STEP8:

Oracle Schema need to be registered in Oracle Application, do the following for Schema Registration,

Login to Applications with System Administrator responsibility
Navigate to Application-->Register

Application = JPL Custom Application
Short Name = JPL
Basepath = JPL_TOP
Description = JPL Custom Application

Screenshot:

STEP9:

Add Application to a Data Group as following,

Navigate to Security-->Oracle-->DataGroup

Data Group = JPL Group
Description = JPL Custom Data Group
Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
Application = JPL Custom Application
Oracle ID = APPS
Description = JPL Custom Application

Screenshot:

STEP10:

Create custom request group.
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility.

Navigate to Security-->responsbility-->Request

Group = JPL Request Group
Application = JPL Custom
Code = JPL
Description = JPL Custom Requests

We will not define any requests to add to the group at this stage, but you can add some now if required.

Screenshot:

STEP11:

Create custom menu.
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility. We will create two menus, one for Core Applications and one for Self Service.

Navigate to Application-->Menu

Menu = JPL_CUSTOM_MENU
User Menu Name = JPL Custom Application
Menu Type =
Description = JPL Custom Application Menu

Seq = 10
Prompt = Submit Concurrent
Submenu = Concurrent
Function =

Description = Submit Concurrent

Seq = 20
Prompt = Concurrent
Submenu = Concurrent Menu - Application Developer GUI
Function =
Description = Concurrent

Screenshot:

**********************************************************Menu = JPL_CUSTOM_MENU_SSWA
User Menu Name = JPL Custom Application SSWA
Menu Type =
Description = JPL Custom Application Menu for SSWA

Seq = 10
Prompt = Oracle Application Manager
Submenu =  OAM Workflow Manager
Function =
Description =

Screenshot:

STEP12:

Create new responsibility. One for Core Applications and One for Self Service (SSWA)

Navigate to Security-->Responsibility-->Define

Responsibility Name = JPL Custom
Application = JPL Custom
Responsibility Key = JPLCUSTOM
Description = JPL Custom Responsibility
Available From = Oracle Applications
Data Group Name = JPL Group
Data Group Application = JPL Custom
Menu = JPL Custom Application
Request Group Name = JPL Request Group

Screenshot:

Responsibility Name = JPL Custom SSWA
Application = JPL Custom
Responsibility Key = JPLCUSTOMSSWA
Description = JPL Custom Responsibility SSWA
Available From = Oracle Self Service Web Applications
Data Group Name = JPL Group
Data Group Application = JPL Custom
Menu = JPL Custom Application SSWA
Request Group Name = JPL Request Group

Screenshot:

STEP13:

Now you can add responsibility to user

Navigate to Security-->User-->Define

Screenshot:

Add JPL Custom responsibility to users as required.

STEP14:

Other considerations:

You are now ready to create your database Objects, custom Reports, Forms, Packages, etc.

Create the source code files in the JPL_TOP directory appropriate for the type of object. For example forms would be located in $JPL_TOP/forms/US or package source code in $JPL_TOP/admin/sql

For example,
Database Objects, such as tables, indexes and sequences should be created in the JPL schema, and then you need to

a) Grant all privilege from each custom data object to the APPS schema.
For example:

login as JPL user
grant all privileges on myTable to apps;

b) Create a synonym in APPS for each custom data object
For example:

login as APPS user
create synonym myTable for JPL.myTable;

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

Author

J. Lingesan

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