Login to OAM (See metalink note 216816.1)
You can write your own scripts and can use in OAM.
Navigate to Site Map -> Monitoring -> SQL Extensions
1. Create directory in the $APPL_TOP/admin /custom/sql
2. Place your script in this directory.
3. Bounce Apache to see the scripts in OAM
Thanks
--Guru
Monday, January 28, 2008
Wednesday, January 23, 2008
Database Architecture PPT.
Click on the below link for PPT of Oracle Database Architecture.
http://docs.google.com/Presentation?id=d69347t_3dr3rxpdk
http://docs.google.com/Presentation?id=d69347t_3dr3rxpdk
Install of 11.5.10.2 on LINUX AS4
Use below link to know about the Installation of 11.5.10.2 on Linux
http://docs.google.com/Doc?id=d69347t_1fv4gj7gf
http://docs.google.com/Doc?id=d69347t_1fv4gj7gf
Issue with File-->Export in Front End
Hey Guys, Recently I faced one issue .
One of the user is not able to do export from front end Application. Let me explain ,Suppose you query to find the running requests. A request window will appear which shows currently running request. Now you want to export all the request with all the info of this request window to a text file instead of taking print screen.
You will use the navigation File-->Export. It prompts you for a location to save the output. But in my case it never ask me to save , it just ran and window will disapper.
What i did is.... I removed my pop up blockers restriction from Internet Explorer but problem was still there. Then I used the below solution which worked for me...
1. Internet Explorer Menu, Navigate to Tools > Internet Options
2. Security tab > click Local Intranet > Sites button > Advanced button
3. Add your domain suffix such as *.guru.org > Add button > OK button
4. Restart Internet Explorer browser
One of the user is not able to do export from front end Application. Let me explain ,Suppose you query to find the running requests. A request window will appear which shows currently running request. Now you want to export all the request with all the info of this request window to a text file instead of taking print screen.
You will use the navigation File-->Export. It prompts you for a location to save the output. But in my case it never ask me to save , it just ran and window will disapper.
What i did is.... I removed my pop up blockers restriction from Internet Explorer but problem was still there. Then I used the below solution which worked for me...
1. Internet Explorer Menu, Navigate to Tools > Internet Options
2. Security tab > click Local Intranet > Sites button > Advanced button
3. Add your domain suffix such as *.guru.org > Add button > OK button
4. Restart Internet Explorer browser
Details of Adpreclone and Adcfgclone
(Interview Ques: What happens internally when you run adpreclone and adcfgclone)
I am covering the Internal Cloning Process which you will not get eaily on the net. Generally you find the steps for cloning like run adpreclone on apps and db tier then copy the file sytem to Target then run adcfgclone on apps tier and db Tier.
What happens when you run adpreclone and adcfgclone. The below steps give you better understanding and enhance your troubleshooting skills.
When you run this commnad adpreclone.pl dbTier . This will run in two steps Techstack and database.
Techstack:
It will create following directories in the ORACLE_HOME/appsutil/clone
Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.
Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml
Prepare database for cloning:
This includes creating datbase control file script and datafile location information file at
$ORACLE_HOME/appsutil/template
adcrdbclone.sql, dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver
data.drv
Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui
When Running adpreclone appsTier
This will create stage directory at $COMMON_TOP/clone. This also run in two steps.
Techstack:
Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template
Creates Techstack driver files for
IAS_ORACLE_HOME/appsutil/driver/instconf.drv
806_ORACLE_HOME/appsutil/driver/instconf.drv
APPL_TOP preparation:
-It will create application top driver file$COMMON_TOP/clone/appl/driver/appl.drv
-Copy JDBC libraries$COMMON_TOP/clone/jlib/classes111.zip
Now Shutdown all the services of Application and database for Copy the file System to target location
Configuring the target system
Once it is done . Run as below adcfclone.pl for apps Tier and dbTier.
On database side:
cd $ORACLE_HOME/appsutils/clone/binperl adcfgclone.pl dbTier pwd=apps
This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.
Following scripts are run by adcfgclone.pl dbTier for configuring techstack
adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.
runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME
For data on database side, following scripts are run
Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
Create database adcrdb.zip
Autoconfig is run
Control file creation adcrdbclone.sql
On Application Side:
COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps
Following scripts are run by adcfgclone.pl
Creates context file for target adclonectx.pl
Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv
Relinking of Oracle Home$ORACLE_HOME/bin/adlnk806.sh$IAS_ORACLE_HOME/bin/adlnkiAS.sh
At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.
Enjoy!!!
I am covering the Internal Cloning Process which you will not get eaily on the net. Generally you find the steps for cloning like run adpreclone on apps and db tier then copy the file sytem to Target then run adcfgclone on apps tier and db Tier.
What happens when you run adpreclone and adcfgclone. The below steps give you better understanding and enhance your troubleshooting skills.
When you run this commnad adpreclone.pl dbTier . This will run in two steps Techstack and database.
Techstack:
It will create following directories in the ORACLE_HOME/appsutil/clone
Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.
Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml
Prepare database for cloning:
This includes creating datbase control file script and datafile location information file at
$ORACLE_HOME/appsutil/template
adcrdbclone.sql, dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver
data.drv
Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui
When Running adpreclone appsTier
This will create stage directory at $COMMON_TOP/clone. This also run in two steps.
Techstack:
Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template
Creates Techstack driver files for
IAS_ORACLE_HOME/appsutil/driver/instconf.drv
806_ORACLE_HOME/appsutil/driver/instconf.drv
APPL_TOP preparation:
-It will create application top driver file$COMMON_TOP/clone/appl/driver/appl.drv
-Copy JDBC libraries$COMMON_TOP/clone/jlib/classes111.zip
Now Shutdown all the services of Application and database for Copy the file System to target location
Configuring the target system
Once it is done . Run as below adcfclone.pl for apps Tier and dbTier.
On database side:
cd $ORACLE_HOME/appsutils/clone/binperl adcfgclone.pl dbTier pwd=apps
This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.
Following scripts are run by adcfgclone.pl dbTier for configuring techstack
adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.
runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME
For data on database side, following scripts are run
Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
Create database adcrdb.zip
Autoconfig is run
Control file creation adcrdbclone.sql
On Application Side:
COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps
Following scripts are run by adcfgclone.pl
Creates context file for target adclonectx.pl
Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv
Relinking of Oracle Home$ORACLE_HOME/bin/adlnk806.sh$IAS_ORACLE_HOME/bin/adlnkiAS.sh
At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.
Enjoy!!!
FInd Trace File using Request ID
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
select 'Request id: 'request_id , 'Trace id: 'oracle_Process_id, 'Trace Flag: 'req.enable_trace,'Trace Name: 'dest.value'/'lower(dbnm.value)'_ora_'oracle_process_id'.trc','Prog. Name: 'prog.user_concurrent_program_name,'File Name: 'execname.execution_file_name execname.subroutine_name , 'Status : 'decode(phase_code,'R','Running') '-'decode(status_code,'R','Normal'), 'SID Serial: 'ses.sid',' ses.serial#, 'Module : 'ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+) and dest.name='user_dump_dest' and dbnm.name='db_name' and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
select 'Request id: 'request_id , 'Trace id: 'oracle_Process_id, 'Trace Flag: 'req.enable_trace,'Trace Name: 'dest.value'/'lower(dbnm.value)'_ora_'oracle_process_id'.trc','Prog. Name: 'prog.user_concurrent_program_name,'File Name: 'execname.execution_file_name execname.subroutine_name , 'Status : 'decode(phase_code,'R','Running') '-'decode(status_code,'R','Normal'), 'SID Serial: 'ses.sid',' ses.serial#, 'Module : 'ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+) and dest.name='user_dump_dest' and dbnm.name='db_name' and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id
Tuesday, January 22, 2008
Installing Statspack
Create a tablespace which will hold the STATSPACK objects:
SQL> create tablespace SPTBS datafile '/u01/oradata/pavc2data/sptbs01.dbf' size 500M autoextend off
Create the STATSPACK user PERFSTAT:
SQL> create user perfstat identified by perfstat 2 default tablespace sptbs;
User created.
Run $ORACLE_HOME/rdbms/admin/spcheck.sql....spcpkg.sql from SYS user.
This script will prompt from PERFSTAT user password (enter perfstat), The default tablespace (enter SPTBS), And the default temporary tablespace (TEMP).
Check the $ORACLE_HOME/rdbms/admin/spcpkg.lis for errors.
Taking the SNAPSHOT:
connect to PERFSTAT user and run the statspack.snap package:
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
Run the following anonymous block to get the snap_id: (Begin Snap_id)
SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
2
Execute the statspack.snap package to get the second snap_id (end snap_id)
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
4
Generating the snapshot report:
Run the $ORACLE_HOME/rdbms/admin/spreport.sql as PERFSTAT user:
Enter the begin snap_id and the end snap_id followed by the report name.
The report will be created in $ORACLE_HOME/rdbms/admin/ directory
SQL> create tablespace SPTBS datafile '/u01/oradata/pavc2data/sptbs01.dbf' size 500M autoextend off
Create the STATSPACK user PERFSTAT:
SQL> create user perfstat identified by perfstat 2 default tablespace sptbs;
User created.
Run $ORACLE_HOME/rdbms/admin/spcheck.sql....spcpkg.sql from SYS user.
This script will prompt from PERFSTAT user password (enter perfstat), The default tablespace (enter SPTBS), And the default temporary tablespace (TEMP).
Check the $ORACLE_HOME/rdbms/admin/spcpkg.lis for errors.
Taking the SNAPSHOT:
connect to PERFSTAT user and run the statspack.snap package:
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
Run the following anonymous block to get the snap_id: (Begin Snap_id)
SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
2
Execute the statspack.snap package to get the second snap_id (end snap_id)
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
4
Generating the snapshot report:
Run the $ORACLE_HOME/rdbms/admin/spreport.sql as PERFSTAT user:
Enter the begin snap_id and the end snap_id followed by the report name.
The report will be created in $ORACLE_HOME/rdbms/admin/ directory
History of Oracle
Oracle Database, the relational database management system from Oracle Corporation, is arguably the most powerfull and feature rich database on the market. Larry Ellison founded Software Development Laboratories in 1977.
In 1979 SDL changed its company name to Relational Software, Inc. (RSI) and introduced its product Oracle V2 as an early commercially-available relational database system. The version did not support transactions, but implemented the basic SQL functionality of queries and joins. There was no version 1, instead the first version was called version 2 as a marketing strategy.
In 1983, RSI was renamed Oracle Corporation to more closely align itself with its flagship product. Oracle version 3 was released which had been re-written in the C Programming Language and supported commit and rollback transaction functionalities. Platform support was extended to UNIX with this version, which until then had run on Digital VAX/VMS systems.
In 1984, Oracle version 4 was released which supported read consistency.
Starting 1985, Oracle began supporting the Client-Server model, with networks becoming available in the mid 80s. Oracle version 5.0 supported distributed querying.
In 1988, Oracle entered the products market and developed its ERP product - Oracle Financials based on the Oracle Relational Database. Oracle version 6 was released with support for PL/SQL, row-level locking and hot backups.
In 1992, Oracle version 7 was released with support for integrity constraints, stored procedures and triggers.In 1997, Oracle version 8 was released with support for object-oriented development and multimedia applications.
In 1999, Oracle 8i was released which is more in tune with the needs of the Internet (The i in the name stands for "Internet"). The database has a native Java Virtual Machine.
In 2001, Oracle 9i was released with 400 new features including the facilty to read and write XML documents. 9i also provided an option for Oracle RAC, or Real Application Clusters, a computer cluster database, as replacement for the Oracle Parallel Server (OPS) option.
In 2003, Oracle 10g was released. The g stands for "Grid"; one of the sales points of 10g is that it's "grid computing ready".
Now 11g has released.
In 1979 SDL changed its company name to Relational Software, Inc. (RSI) and introduced its product Oracle V2 as an early commercially-available relational database system. The version did not support transactions, but implemented the basic SQL functionality of queries and joins. There was no version 1, instead the first version was called version 2 as a marketing strategy.
In 1983, RSI was renamed Oracle Corporation to more closely align itself with its flagship product. Oracle version 3 was released which had been re-written in the C Programming Language and supported commit and rollback transaction functionalities. Platform support was extended to UNIX with this version, which until then had run on Digital VAX/VMS systems.
In 1984, Oracle version 4 was released which supported read consistency.
Starting 1985, Oracle began supporting the Client-Server model, with networks becoming available in the mid 80s. Oracle version 5.0 supported distributed querying.
In 1988, Oracle entered the products market and developed its ERP product - Oracle Financials based on the Oracle Relational Database. Oracle version 6 was released with support for PL/SQL, row-level locking and hot backups.
In 1992, Oracle version 7 was released with support for integrity constraints, stored procedures and triggers.In 1997, Oracle version 8 was released with support for object-oriented development and multimedia applications.
In 1999, Oracle 8i was released which is more in tune with the needs of the Internet (The i in the name stands for "Internet"). The database has a native Java Virtual Machine.
In 2001, Oracle 9i was released with 400 new features including the facilty to read and write XML documents. 9i also provided an option for Oracle RAC, or Real Application Clusters, a computer cluster database, as replacement for the Oracle Parallel Server (OPS) option.
In 2003, Oracle 10g was released. The g stands for "Grid"; one of the sales points of 10g is that it's "grid computing ready".
Now 11g has released.
11i Technology Stack Components
Verify various components in 11i
Where oracle-->servername
apps.guru-->domain name
8000-->port number
AOL/J Diagnostic Tests
http://oracle.apps.guru:8000/OA_HTML/jsp/fnd/aoljtest.jsp
APPS_WEB_AGENT
http://oracle.apps.guru:8000/pls/vis/fnd_web.ping
APPS_FRAMEWORK_AGENT
http://oracle.apps.guru:8000/
ICX_FORMS_LAUNCHER
http://oracle.apps.guru:8000/dev60cgi/f60cgi
(Interview ques: How to bypass the home page)
ICX_REPORT_LAUNCHER
http://oracle.apps.guru:8000/dev60cgi/rwcgi60
ICX_DISCOVERER_LAUNCHER = http://oracle.apps.guru:8000/discwb4/html/discolaunch.htm?Connect=%5bAPPS_SECURE%5d
Pls post comments if this is helpful for you.
Thanks,
--Guru
apps.guru-->domain name
8000-->port number
AOL/J Diagnostic Tests
http://oracle.apps.guru:8000/OA_HTML/jsp/fnd/aoljtest.jsp
APPS_WEB_AGENT
http://oracle.apps.guru:8000/pls/vis/fnd_web.ping
APPS_FRAMEWORK_AGENT
http://oracle.apps.guru:8000/
ICX_FORMS_LAUNCHER
http://oracle.apps.guru:8000/dev60cgi/f60cgi
(Interview ques: How to bypass the home page)
ICX_REPORT_LAUNCHER
http://oracle.apps.guru:8000/dev60cgi/rwcgi60
ICX_DISCOVERER_LAUNCHER = http://oracle.apps.guru:8000/discwb4/html/discolaunch.htm?Connect=%5bAPPS_SECURE%5d
Pls post comments if this is helpful for you.
Thanks,
--Guru
Subscribe to:
Posts (Atom)