Use Following Script:
CLEAR COL BREAK COMPUTE
SET PAGES 100 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF
COL object_name FORMAT A35
COL type FORMAT A20
COL sid FORMAT 9999
COL held FORMAT A5
COL request FORMAT A7
SELECT session_id sid,
owner||'.'||name object_name,
type,
mode_held held,
mode_requested request
FROM dba_ddl_locks
WHERE name LIKE UPPER('%&object_name%')
/
SET PAGES 32 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF
PROMPT
When prompts give Package name
Friday, September 26, 2008
Thursday, September 18, 2008
Tablespace Usage Info (Free Space, Used Space, Percentage)
Solution:
select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
Note: Pass Tablespace whenever you are prompted
select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
Note: Pass Tablespace whenever you are prompted
Wednesday, September 17, 2008
Tuning the Concurrent Manager
All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:
* Tuning the Concurrent Manager
- Tuning the Internal Concurrent Manager
- Purging Concurrent Requests
- Troubleshooting Oracle Apps performance problems
- Adjusting the Concurrent Manager Cache Size
- Analyzing the Oracle Apps Dictionary Tables
* Monitoring Pending Requests in the Concurrent Manager
* Changing the dispatching priority within the Concurrent Manager
Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.
* PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
* Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
* Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.
All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment.
An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.
Purging Concurrent Requests
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications.
When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.
Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.
Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
* FND_CONCURRENT_PROCESSES
* FND_CONCURRENT_PROGRAMS
* FND_CONCURRENT_REQUESTS
* FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace.
A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications.
Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace.
Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.
Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requestsset status_code='X', phase_code='C'where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority. Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request.
If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql : Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql : Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql : Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql : Displays the requests that are pending, held, and scheduled.
afrqstat.sql : Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql : Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql : Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
* Tuning the Concurrent Manager
- Tuning the Internal Concurrent Manager
- Purging Concurrent Requests
- Troubleshooting Oracle Apps performance problems
- Adjusting the Concurrent Manager Cache Size
- Analyzing the Oracle Apps Dictionary Tables
* Monitoring Pending Requests in the Concurrent Manager
* Changing the dispatching priority within the Concurrent Manager
Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.
* PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
* Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
* Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.
All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment.
An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.
Purging Concurrent Requests
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications.
When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.
Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.
Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
* FND_CONCURRENT_PROCESSES
* FND_CONCURRENT_PROGRAMS
* FND_CONCURRENT_REQUESTS
* FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace.
A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications.
Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace.
Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.
Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requestsset status_code='X', phase_code='C'where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority. Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request.
If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql : Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql : Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql : Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql : Displays the requests that are pending, held, and scheduled.
afrqstat.sql : Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql : Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql : Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
Something about Concurrent Managers
Concurrent Managers
The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.
This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.
The Master Concurrent Managers
There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:
* Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
* Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
* Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.
Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.
The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.
This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.
The Master Concurrent Managers
There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:
* Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
* Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
* Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.
Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.
Recreate Database Link
Solution:
col OWNER for a15
col DB_LINK for a25
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK in ('DB_LINK_NAME')
1.Once, the password has been given by client, then use the following statement to recreate the DBLINK,
create database link "DB_LINK_NAME" connect to TARGET_USER IDENTIFIED BY TARGET_PASS using 'TARGET_SID';
2. Verify whether DB Link is working by using the following statement,
select * from dual@DB_LINK_NAME;
3.It should return 1 row. "
col OWNER for a15
col DB_LINK for a25
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK in ('DB_LINK_NAME')
1.Once, the password has been given by client, then use the following statement to recreate the DBLINK,
create database link "DB_LINK_NAME" connect to TARGET_USER IDENTIFIED BY TARGET_PASS using 'TARGET_SID';
2. Verify whether DB Link is working by using the following statement,
select * from dual@DB_LINK_NAME;
3.It should return 1 row. "
Enable FRD ( Forms Runtime Diagnostic ) Trace??
Solution:
1. Login into SYSADMIN --> System Administrator --> Profile --> System
Search the profile option for "ICX Forms Launcher" and Make sure Site and User leave has been checked.
2. Set the value following values at user level only according to the version
3. For R12, http://url:/OA_HTML/frmservlet?record=collect
4. For 11i, http://url:/dev60cgi/f60cgi?&record=collect&log=<>
5. Get the FRD trace from $FORMS_TRACE_DIR path at OS Level
1. Login into SYSADMIN --> System Administrator --> Profile --> System
Search the profile option for "ICX Forms Launcher" and Make sure Site and User leave has been checked.
2. Set the value following values at user level only according to the version
3. For R12, http://url:/OA_HTML/frmservlet?record=collect
4. For 11i, http://url:/dev60cgi/f60cgi?&record=collect&log=<>
5. Get the FRD trace from $FORMS_TRACE_DIR path at OS Level
Create Orignal Package Specification and body if it is corrupted?
Solution:
1. select text from dba_source where name='AC_PACKAGE_IC' and rownum < 10
2. You will get .pls file
3. Take a backup of current package body and specification by spooling
4. Goto That top/patch/115/sql/
5. Execute .pls files for both body spec
1. select text from dba_source where name='AC_PACKAGE_IC' and rownum < 10
2. You will get .pls file
3. Take a backup of current package body and specification by spooling
4. Goto That top/patch/115/sql/
5. Execute .pls files for both body spec
How to Analyze a table partition ?
Solution:
exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)
exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)
Concurrent requests show Inactive No Manager? Then?
Solution:
1. Get the request id from the user,
2. Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.
3. If the Manager screen doesn't show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers.
1. Get the request id from the user,
2. Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.
3. If the Manager screen doesn't show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers.
How to register Jar File in Class Path?
Solution:
1. Make an entry in jserv.properties under Classpath section.
2. Add a new liine as "wrapper.classpath=".
3. Bounce the Apache.
1. Make an entry in jserv.properties under Classpath section.
2. Add a new liine as "wrapper.classpath=".
3. Bounce the Apache.
How to rebuild Index Online with parallel?
Solution:
1. alter index APPS.CSS_SUMMARY_ST rebuild parallel 32; (After completion use following)
2. alter index APPS.CSS_SUMMARY_ST no parallel
1. alter index APPS.CSS_SUMMARY_ST rebuild parallel 32; (After completion use following)
2. alter index APPS.CSS_SUMMARY_ST no parallel
How do you check whether Trace is enabled to particular concurrent program from the back end?
Solution:
1. select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Program Name%'; (You will get Id)
2. select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id ='concurrent program id';
1. select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Program Name%'; (You will get Id)
2. select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id ='concurrent program id';
Get the Temporary Table Space information?
Solution:
select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;
or
SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;
(pass block_size(get it from show parameter block_size))
select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;
or
SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;
(pass block_size(get it from show parameter block_size))
How do you compile invalid objects?
Solution:
@ORACLE_HOME/rdbms/admin/utlrp.sql or
exec sys.utl_recomp.recomp_parallel(32);
@ORACLE_HOME/rdbms/admin/utlrp.sql or
exec sys.utl_recomp.recomp_parallel(32);
How do you collect Schema Level Statistics?
Solution:
set time on;
set timing on;
exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','EST_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
set time on;
set timing on;
exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','EST_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
How do you collect Table level Statistics?
Solution:
exec dbms_stats.gather_table_stats(OWNNAME=>'MP',TABNAME=>'MP_FORAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_table_stats(OWNNAME=>'MP',TABNAME=>'MP_FORAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
How do you know which concurrent manager is taking care of your request?
Solution:
sqlplus apps/appspwd @$FND_TOP/sql/@afcmcreq.sql
(Give Request number when prompts)
sqlplus apps/appspwd @$FND_TOP/sql/@afcmcreq.sql
(Give Request number when prompts)
How do you get Oracle Process id from the concurrent request id?
Solution:
select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;
select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;
How do you know complete Concurrent program details ?
Solution:
1. Get the concurrent program Name.. Give it after running following script
col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;
select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';
1. Get the concurrent program Name.. Give it after running following script
col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;
select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';
Tkprof Usage?
Solution:
tkprof coestg1_trc coestg1_.trc_tkprof sys=no explain=apps/appspwd sort='(fchela,exeela,prsela)'
tkprof coestg1_trc coestg1_.trc_tkprof sys=no explain=apps/appspwd sort='(fchela,exeela,prsela)'
How do you know, Your node details in your Oracle applications?
Solution:
SELECT node_name,support_cp,support_forms,support_web,support_admin,
status FROM apps.fnd_nodes ORDER BY 2,3,4;
SELECT node_name,support_cp,support_forms,support_web,support_admin,
status FROM apps.fnd_nodes ORDER BY 2,3,4;
How do you know your apps URL?
Solution:
select home_url from apps.icx_parameters;
or
grep -i login $APPL_TOP/admin/Context_name.xml
select home_url from apps.icx_parameters;
or
grep -i login $APPL_TOP/admin/Context_name.xml
How do you kill Bulk number of sessions at a single Time?
Solution:
set head off
set pagesize 1000
spool kill.sql
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';
spool off
@kill.sql
set head off
set pagesize 1000
spool kill.sql
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';
spool off
@kill.sql
How do you assign priviliges to a user, same it has priviliges in another user?
Solution:
SELECT 'GRANT ' PRIVILEGE ' ON ' OWNER '.' TABLE_NAME ' TO newusername;' FROM DBA_TAB_PRIVS WHERE GRANTEE = 'firstusername'
SELECT 'GRANT ' PRIVILEGE ' ON ' OWNER '.' TABLE_NAME ' TO newusername;' FROM DBA_TAB_PRIVS WHERE GRANTEE = 'firstusername'
How do you view source code of view?
Solution:
desc dba_views;
Set long 20000
Set pagsize 10000
Spool viewsource.txt
select text from dba_views where view_name=’VIE_SOURCE’;
Spool off
desc dba_views;
Set long 20000
Set pagsize 10000
Spool viewsource.txt
select text from dba_views where view_name=’VIE_SOURCE’;
Spool off
FNDCPASS Usage
How to user FNDCPASS to change the passwords of a Oracle Apps Front End Passwords/Database Product Passwords/Apps Passwords?
Solution:
1. To Change Front End application password for a user?
FNDCPASS apps/appspwd 0 Y system/systempwd USER username passwd
Example: FNDCPASS apps/apps0 Y system/manager USER test test123
2. To Change Database Product Password?
FNDCPASS apps/appspwd 0 Y system/systempwd ORACLE prodschema passwd
Example: FNDCPASS apps/apps 0 Y system/manager ORACLE GL glnew
Note: After Execution of FNDCPASS it will generate two files one is 282990.log and 32123.out files
Open the log file and see whether concurrent program successfully executed or not. If so, Everything ran successfully
Solution:
1. To Change Front End application password for a user?
FNDCPASS apps/appspwd 0 Y system/systempwd USER username passwd
Example: FNDCPASS apps/apps0 Y system/manager USER test test123
2. To Change Database Product Password?
FNDCPASS apps/appspwd 0 Y system/systempwd ORACLE prodschema passwd
Example: FNDCPASS apps/apps 0 Y system/manager ORACLE GL glnew
Note: After Execution of FNDCPASS it will generate two files one is 282990.log and 32123.out files
Open the log file and see whether concurrent program successfully executed or not. If so, Everything ran successfully
Form/Reprot Compilation/Generation
1. How do you compile a form command line?
appltop/apps/ora/8.0.6/bin/f60gen module=/appltop/apps/au/11.5.0/forms/US/FNDRSRUN.fmb userid=APPS/apps output_file=/appltop/apps/fnd/11.5.0/forms/US/FNDRSRUN.fmx module_type=form batch=yes compile_all=special
2. how do you generate a report command line?
/appltop/apps/ora/8.0.6/bin/rwcon60 userid=APPS/apps source=/tappltop/apps/pa/11.5.0/reports/US/PAXPCEGS.rdf dest=/tappltop/apps/admin/INTGBL/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/tappltop/apps/admin/INTGBL/out/adrep001.txt overwrite=yes batch=yes compile_all=yes
3. How do you load data to db using FNDLOAD?
/appltop/apps/fnd/11.5.0/bin/FNDLOAD &ui_apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct @PJI:patch/115/import/US/pji115fn.ldt
appltop/apps/ora/8.0.6/bin/f60gen module=/appltop/apps/au/11.5.0/forms/US/FNDRSRUN.fmb userid=APPS/apps output_file=/appltop/apps/fnd/11.5.0/forms/US/FNDRSRUN.fmx module_type=form batch=yes compile_all=special
2. how do you generate a report command line?
/appltop/apps/ora/8.0.6/bin/rwcon60 userid=APPS/apps source=/tappltop/apps/pa/11.5.0/reports/US/PAXPCEGS.rdf dest=/tappltop/apps/admin/INTGBL/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/tappltop/apps/admin/INTGBL/out/adrep001.txt overwrite=yes batch=yes compile_all=yes
3. How do you load data to db using FNDLOAD?
/appltop/apps/fnd/11.5.0/bin/FNDLOAD &ui_apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct @PJI:patch/115/import/US/pji115fn.ldt
what Sqls/scripts/modules are running in the databases by what users?
Solution: Use the following script
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username'('sid','serial#')' username, module, action, client_infofrom v$sessionwhere moduleactionclient_info is not null;
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username'('sid','serial#')' username, module, action, client_infofrom v$sessionwhere moduleactionclient_info is not null;
Know Currnet SQL Running
1. How do you know what is the current SQL Running in the Database?
Solution: use the folowing script
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in ( select username'('sid','serial# ') ospid = ' process ' program = ' program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET
from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) max(decode(piece,1,sql_text,null)) max(decode(piece,2,sql_text,null)) max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4)
loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time ' ' x.current_time ' last et = ' x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;/
Solution: use the folowing script
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in ( select username'('sid','serial# ') ospid = ' process ' program = ' program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET
from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) max(decode(piece,1,sql_text,null)) max(decode(piece,2,sql_text,null)) max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4)
loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time ' ' x.current_time ' last et = ' x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;/
Table Locks and Remove Table locks
1. How do you know What are the objects have locks??
Solution:
select * from v$lock where block<>0;
if block=0 then no locks if block=1 then there is locks
2. How do you know locked tables? and how do you remove locks?
Solution:
1. select a.object_id, a.session_id, substr(b.object_name, 1, 40)from v$locked_object a,dba_objects bwhere a.object_id = b.object_idorder by b.object_name ;
2. select sid, serial#, command, taddr from v$session where sid=
3. alter system kill session 'sid,serial#';
Solution:
select * from v$lock where block<>0;
if block=0 then no locks if block=1 then there is locks
2. How do you know locked tables? and how do you remove locks?
Solution:
1. select a.object_id, a.session_id, substr(b.object_name, 1, 40)from v$locked_object a,dba_objects bwhere a.object_id = b.object_idorder by b.object_name ;
2. select sid, serial#, command, taddr from v$session where sid=
3. alter system kill session 'sid,serial#';
Rollback Segments Issue/Fix/WorkAround
1. If you get an error like unable to extend rollback segment like below error?
ERROR at line 1: ORA-01562: failed to extend rollback segment number 10 ORA-01628: max # extents (150) reached for rollback segment R09 ORA-06512: at line 139
Solutioin:
1. SELECT segment_name, max_extents FROM dba_rollback_segs;
2. alter rollback segment r01 storage (maxextents unlimited)
ERROR at line 1: ORA-01562: failed to extend rollback segment number 10 ORA-01628: max # extents (150) reached for rollback segment R09 ORA-06512: at line 139
Solutioin:
1. SELECT segment_name, max_extents FROM dba_rollback_segs;
2. alter rollback segment r01 storage (maxextents unlimited)
Enabling Auditing for a user
How to enable audit for a particular user?
audit insert table,delete table,update table by newene;
audit insert table,delete table,update table by newene;
ORA-1555 failures ("snapshot too old") Issue/Fixes/WorkAround
If you get ORA-1555 Failures or SnapShot Too Old errors
Solution:
1. Increase the size of undo tablespace or
2. Increase the undo_retentoin time
Steps to Fix the issue
1. First you find out what is the undo tablespace is needed for your database
2. Second you find out what is the undo retentino time is needed for your database
---Using following Scripts -----
Script #1
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION (Secs)"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size'
/
Script #2
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "
NEEDED UNDO SIZE (MEGS)"
from (select sum(a.bytes) undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention' and f.name = 'db_block_size'
/
You will get Ideal Undo_tablespace size and Undo_retention time. According that outputs, Set your values as follows
If it is just problem with undo_retention, then do as follows
1. Update init.ora with the following undo_retention=36600
2. Bounce the database
If it is just problem with undo_tablespace size, then do as follows
Solution in this situation is to create a NEW UNDO tablespace with smaller size and switch over to your current UNDO tablespace to NEW UNDO tablespace.
Example:
/* Create new undo tablespace with smaller size
*/SQL> create undo tablespace undotbs2 datafile ‘/u01/oradata/decipher/undotbs2_01.dbf’ size 1024m autoextend on next 256m maxsize 10240m;
/* Set new tablespace as undo_tablespace */SQL> alter system set undo_tablespace= UNDOTBS2 scope=both;
If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This is normally to un-assign an undo tablespace in the event that you want to revert to manual undo management mode.
/* Drop the old tablespace */SQL> drop tablespace UNDOTBS1 including contents;
Solution:
1. Increase the size of undo tablespace or
2. Increase the undo_retentoin time
Steps to Fix the issue
1. First you find out what is the undo tablespace is needed for your database
2. Second you find out what is the undo retentino time is needed for your database
---Using following Scripts -----
Script #1
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION (Secs)"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size'
/
Script #2
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "
NEEDED UNDO SIZE (MEGS)"
from (select sum(a.bytes) undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention' and f.name = 'db_block_size'
/
You will get Ideal Undo_tablespace size and Undo_retention time. According that outputs, Set your values as follows
If it is just problem with undo_retention, then do as follows
1. Update init.ora with the following undo_retention=36600
2. Bounce the database
If it is just problem with undo_tablespace size, then do as follows
Solution in this situation is to create a NEW UNDO tablespace with smaller size and switch over to your current UNDO tablespace to NEW UNDO tablespace.
Example:
/* Create new undo tablespace with smaller size
*/SQL> create undo tablespace undotbs2 datafile ‘/u01/oradata/decipher/undotbs2_01.dbf’ size 1024m autoextend on next 256m maxsize 10240m;
/* Set new tablespace as undo_tablespace */SQL> alter system set undo_tablespace= UNDOTBS2 scope=both;
If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This is normally to un-assign an undo tablespace in the event that you want to revert to manual undo management mode.
/* Drop the old tablespace */SQL> drop tablespace UNDOTBS1 including contents;
Temporary Tablespace Issue
Recreation of Temporary Tablespace
Solution:
Drop and Re Create the Temporary Tablespace
Steps to implement
1. alter database backup controlfile to trace;
2. select file_name,tablespace_name from dba_temp_files;
3. create temporary tablespace tempbkp tempfile '/y01/orcl/dat/tempbkp.dbf' size 10m;
4. alter database default temporary tablespace tempbkp;Database altered.
5. select temporary_tablespace from dba_users;
6. drop tablespace temp including contents and datafiles;
If above statement doesn't work. Delete each datafile of the particular temporary tablespace as follows
alter database tempfile '/temp/orcle/dat/temp05.dbf' drop including datafiles;
7. alter tablespace temp add tempfile'/temp/orcl/dat/temp01.dbf' SIZE 118489088 reuse autoextend on 1048576 maxsize 8500m;
8. alter database default temporary tablespace temp;
9. drop tablespace tempbkp including contenets and datafiles
Solution:
Drop and Re Create the Temporary Tablespace
Steps to implement
1. alter database backup controlfile to trace;
2. select file_name,tablespace_name from dba_temp_files;
3. create temporary tablespace tempbkp tempfile '/y01/orcl/dat/tempbkp.dbf' size 10m;
4. alter database default temporary tablespace tempbkp;Database altered.
5. select temporary_tablespace from dba_users;
6. drop tablespace temp including contents and datafiles;
If above statement doesn't work. Delete each datafile of the particular temporary tablespace as follows
alter database tempfile '/temp/orcle/dat/temp05.dbf' drop including datafiles;
7. alter tablespace temp add tempfile'/temp/orcl/dat/temp01.dbf' SIZE 118489088 reuse autoextend on 1048576 maxsize 8500m;
8. alter database default temporary tablespace temp;
9. drop tablespace tempbkp including contenets and datafiles
Monday, September 08, 2008
Oracle Apps DBA Interview Questions
Oracle Apps DBA - Interview Questions
1. what is the utility to change the password of a dba schema in oracle apps? Ans: FNDCPASS
2. what are mandatory users in oracle apps?
Ans: applsys,applsyspub,apps
3. What simplay a oracle Architechture?
Ans: Desktop Tier, Application Tier, Database Tier 5. What are the components in the Application Tier?
Ans: Apache(http)
Jserver(jre)
Forms Server(f60srv)
Metric Server(d2ls)
Metric Client(d2lc)
Report Server(rwm60)
Concurrent Server(FNDLIBR)
Discoverer 6.What are main file systems in Oracle Apps?
Ans: APPL_TOP, COMMON_TOP,
DB_TOP,ORA_TOP 7. What are there in Desktop Tier?
Ans: Internet Browser, JInitiator 8. What is the location of JInitiator in the Desktop Tier?
Ans: c:\program files\oracle\Jinitiator 9. What is the location of client cache?
Ans: c:\documents and settngs\user\oracle jar Cache 10. What is the location of Server cache?
Ans: $COMMON_TOP/_pages
11. Which package will be used for the user validation by plsql agent?
Ans: oraclemypage
12. What are adadmin utilities? and Its location?
$AD_TOP/bin
Ans: 1.adadmin
2.adpatch
3.adsplice
4.adident
5.adrelink
6.adlicmgr
13.What are the location of JaVA Files?
Ans: JAVA_TOP and all PRODUCT_TOP/Java/Jar
14. What is the name of the xml file of Apps and its location?
Ans: Context Name.xml and $APPL_TOP/admin
15. what is the location of Apps environment file? and its name?
Ans: contextname.env and $APPL_TOP
16. In how many way Jar files are generated?
Ans: Normal and Force
17. Once Jar files are generated what files get effected?
Ans: All Product_top/java/jar files and Two files in JAVA_TOP they are appsborg.zip
appsborg2.zip
18. How do you see the files in zip file?
Ans: unzip -v
19.How do you generate jar files?
Ans: Using adadmin and option 5
20. How do you start the apps services?
Ans: $COMMON_TOP\admin\scripts\Contextname\adstrtal.sh apps/appspwd
21. What is the executable to generate jar files?
Ans: adjava
22. How do you relink a executable of a product
Ans: by relinking option in adadmin or adrelink
23. How do you relink AD product executable? and usage?
Ans: adrelink.sh and adrelink.sh force=y "ad adsplice"
24.When do you relinking?
Ans: 1. when you miss a executable file
2. When there is a problem with any executable file
3. When any product executable get currupted
25. What is DAD?
Ans: It is a file which stores apps passwords in hard coded format. i.e wdbsvr
26.How do you relink OS files and libraries?
Ans: using make command
27.What is compile scheman option in adadmin?
Ans: This option is used to compile/resolve the invalid objects
28. Where do you get the info about invalid objects?
Ans: from dba_objects where status=invalid
29.How do you compile an obect ?
Ans: alter object_ type objet _name compile. Eg: alter table fnd_nodes compile
30.How do you see the errors of a table or view?
Ans: select text from dba_errors where name='emp_view'
31. How do you see the errors in the db?
Ans: show error
32. How do you compile a schema?
Ans: using utlrp.sql (location is ?/rdbms/admin/) or
going adadmin, compile schema option
33. How do you know how many invalid objects are in specific schema?
Ans: select count(*) from dba_objects where status='INVALID' group by owner;
34. How do you know the package version?
Ans: select text from dba_source where name='package name' and type='PACKAGE BODY' and rownum<10>/rdbms/admin)
41. How do you load java class to databae?
Ans: loadjava
42. What are restart files? and its location?
Ans: These files contains the previouse session info about adadmin.. location is $APPL_TOP\admin\sid\restart\*.rf9
43.How do you validate apps schema?
Ans: To validate synonyms, missing sysnonyms and all grant. You can do it in adadmin. after validating it iwll produce
a report in the location $APPL_TOP\admin\sid\out\*.out
44. How do you enable maintainance mode?
Ans: using adadmin or running a script called "adsetmmd.sql ENABLE/DISABLE" (AD_TOP/patch/115/sql)
45.What is APPS_MRC Schema?
Ans: It is used for multi language support. To synchronize APPs schema and APPS_MRC
46. How to see the version of a script or form or report or etc?
Ans: grep Header adsetmmd.sql or adident Header adsetmmd.sql
strings -a GLXSTEA.fmx grep Header or adident Header GLXSTEA.fmx
47.What is the location of adadmin log?
Ans: $APPL_TOP\admin\sid\log
48. What are the oracle homes in Apps?
Ans: 8.0.6ORACLE_HOME(Dev 6i products) and IAS_ORACLE_HOME (Apache)
49. How do you configure you ipaddress at client side? and server side?
Ans: c:\windows\system32\drivers\etc\hosts and \etc\host
50. What is the location of Datbase server related scripts?
Ans: $ORACLE_HOME\appsutil\scripts\contextname
51. what is the utility to clean the concurrent manager?
Ans: @cmclean.sql ( You have download from metalink)
52. How do you stage the 11.5.10 Apps software?
Ans: using adautostg.pl
53. What is the location of the source files of forms?
Ans: AU_TOP/forms/US/
54. What is the executable to generate forms?
Ans: f60gen
1. what is the utility to change the password of a dba schema in oracle apps? Ans: FNDCPASS
2. what are mandatory users in oracle apps?
Ans: applsys,applsyspub,apps
3. What simplay a oracle Architechture?
Ans: Desktop Tier, Application Tier, Database Tier 5. What are the components in the Application Tier?
Ans: Apache(http)
Jserver(jre)
Forms Server(f60srv)
Metric Server(d2ls)
Metric Client(d2lc)
Report Server(rwm60)
Concurrent Server(FNDLIBR)
Discoverer 6.What are main file systems in Oracle Apps?
Ans: APPL_TOP, COMMON_TOP,
DB_TOP,ORA_TOP 7. What are there in Desktop Tier?
Ans: Internet Browser, JInitiator 8. What is the location of JInitiator in the Desktop Tier?
Ans: c:\program files\oracle\Jinitiator 9. What is the location of client cache?
Ans: c:\documents and settngs\user\oracle jar Cache 10. What is the location of Server cache?
Ans: $COMMON_TOP/_pages
11. Which package will be used for the user validation by plsql agent?
Ans: oraclemypage
12. What are adadmin utilities? and Its location?
$AD_TOP/bin
Ans: 1.adadmin
2.adpatch
3.adsplice
4.adident
5.adrelink
6.adlicmgr
13.What are the location of JaVA Files?
Ans: JAVA_TOP and all PRODUCT_TOP/Java/Jar
14. What is the name of the xml file of Apps and its location?
Ans: Context Name.xml and $APPL_TOP/admin
15. what is the location of Apps environment file? and its name?
Ans: contextname.env and $APPL_TOP
16. In how many way Jar files are generated?
Ans: Normal and Force
17. Once Jar files are generated what files get effected?
Ans: All Product_top/java/jar files and Two files in JAVA_TOP they are appsborg.zip
appsborg2.zip
18. How do you see the files in zip file?
Ans: unzip -v
19.How do you generate jar files?
Ans: Using adadmin and option 5
20. How do you start the apps services?
Ans: $COMMON_TOP\admin\scripts\Contextname\adstrtal.sh apps/appspwd
21. What is the executable to generate jar files?
Ans: adjava
22. How do you relink a executable of a product
Ans: by relinking option in adadmin or adrelink
23. How do you relink AD product executable? and usage?
Ans: adrelink.sh and adrelink.sh force=y "ad adsplice"
24.When do you relinking?
Ans: 1. when you miss a executable file
2. When there is a problem with any executable file
3. When any product executable get currupted
25. What is DAD?
Ans: It is a file which stores apps passwords in hard coded format. i.e wdbsvr
26.How do you relink OS files and libraries?
Ans: using make command
27.What is compile scheman option in adadmin?
Ans: This option is used to compile/resolve the invalid objects
28. Where do you get the info about invalid objects?
Ans: from dba_objects where status=invalid
29.How do you compile an obect ?
Ans: alter object_ type objet _name compile. Eg: alter table fnd_nodes compile
30.How do you see the errors of a table or view?
Ans: select text from dba_errors where name='emp_view'
31. How do you see the errors in the db?
Ans: show error
32. How do you compile a schema?
Ans: using utlrp.sql (location is ?/rdbms/admin/) or
going adadmin, compile schema option
33. How do you know how many invalid objects are in specific schema?
Ans: select count(*) from dba_objects where status='INVALID' group by owner;
34. How do you know the package version?
Ans: select text from dba_source where name='package name' and type='PACKAGE BODY' and rownum<10>/rdbms/admin)
41. How do you load java class to databae?
Ans: loadjava
42. What are restart files? and its location?
Ans: These files contains the previouse session info about adadmin.. location is $APPL_TOP\admin\sid\restart\*.rf9
43.How do you validate apps schema?
Ans: To validate synonyms, missing sysnonyms and all grant. You can do it in adadmin. after validating it iwll produce
a report in the location $APPL_TOP\admin\sid\out\*.out
44. How do you enable maintainance mode?
Ans: using adadmin or running a script called "adsetmmd.sql ENABLE/DISABLE" (AD_TOP/patch/115/sql)
45.What is APPS_MRC Schema?
Ans: It is used for multi language support. To synchronize APPs schema and APPS_MRC
46. How to see the version of a script or form or report or etc?
Ans: grep Header adsetmmd.sql or adident Header adsetmmd.sql
strings -a GLXSTEA.fmx grep Header or adident Header GLXSTEA.fmx
47.What is the location of adadmin log?
Ans: $APPL_TOP\admin\sid\log
48. What are the oracle homes in Apps?
Ans: 8.0.6ORACLE_HOME(Dev 6i products) and IAS_ORACLE_HOME (Apache)
49. How do you configure you ipaddress at client side? and server side?
Ans: c:\windows\system32\drivers\etc\hosts and \etc\host
50. What is the location of Datbase server related scripts?
Ans: $ORACLE_HOME\appsutil\scripts\contextname
51. what is the utility to clean the concurrent manager?
Ans: @cmclean.sql ( You have download from metalink)
52. How do you stage the 11.5.10 Apps software?
Ans: using adautostg.pl
53. What is the location of the source files of forms?
Ans: AU_TOP/forms/US/
54. What is the executable to generate forms?
Ans: f60gen
Friday, September 05, 2008
Metalink Important Note IDs
1. How to run OATM migration utility ---- Note:404954.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
Thursday, September 04, 2008
Oracle Apps DBA Important Tables
Oracle Apps DBA Important Tables
fnd_user
fnd_oracle_user_id
fnd_concurrent_requests
fnd_concurrent_programs_tr
fnd_concurrent_programs
fnd_responsibility
fnd_profile_option_values
fnd_menus
fnd_nodes
fnd_product_installations
fnd_application
fnd_product_groups
fnd_install_processes
ad_deferred_jobs
ad_applied_patches
ad_bugs
fnd_user
fnd_oracle_user_id
fnd_concurrent_requests
fnd_concurrent_programs_tr
fnd_concurrent_programs
fnd_responsibility
fnd_profile_option_values
fnd_menus
fnd_nodes
fnd_product_installations
fnd_application
fnd_product_groups
fnd_install_processes
ad_deferred_jobs
ad_applied_patches
ad_bugs
Oracle Database Important Tables
Oracle DBA Important Tables
v$database
v$controlfile
dba_data_files
v$datafile
dba_temp_files
v$tempfile
v$tempspace_header
dba_users
dba_db_links
v$link
dba_objects
dba_tables
dba_indexees
dba_ind_columns
dba_tab_columns
dba_sequences
dba_synonyms
dba_source
dba_views
dba_errors
dba_triggers
dba_roles
dba_role_privs
session_roles
dba_tablespaces
dba_tab_privs
role_tab_privs
session_privs
dba_ts_quotas
dba_registry
dba_rollback_segs
dba_properties
v$database
v$controlfile
dba_data_files
v$datafile
dba_temp_files
v$tempfile
v$tempspace_header
dba_users
dba_db_links
v$link
dba_objects
dba_tables
dba_indexees
dba_ind_columns
dba_tab_columns
dba_sequences
dba_synonyms
dba_source
dba_views
dba_errors
dba_triggers
dba_roles
dba_role_privs
session_roles
dba_tablespaces
dba_tab_privs
role_tab_privs
session_privs
dba_ts_quotas
dba_registry
dba_rollback_segs
dba_properties
How to apply India Localization Patches
How to apply India Localization Patches
1. How to apply india Localizatin Patches?
1. We have to install India localization Patch Application Tool by downloading patch 6491231
2. Copy the downloaded patch to $JA_TOP and unzip the same there
3. A directory inpatch will be created afer unzipping. In which india localization patch tool avaialable
4. Go to india localization patch directory
5. use following command
perl $JA_TOP/inpatch/indpatch.pl drvr_file=6355941.drv fnd_patchset=H appspwd=apps japwd=ja logfile=6355941.log systempwd=manager
fnd_patchset= FND PATCHSET LEVEL
japwd = ja_top password
drv_file=patchnumber.drv file
2. How do you know what are india localization patches applied?
Solution : using JAI_APPLIED_PATCHES
1. How to apply india Localizatin Patches?
1. We have to install India localization Patch Application Tool by downloading patch 6491231
2. Copy the downloaded patch to $JA_TOP and unzip the same there
3. A directory inpatch will be created afer unzipping. In which india localization patch tool avaialable
4. Go to india localization patch directory
5. use following command
perl $JA_TOP/inpatch/indpatch.pl drvr_file=6355941.drv fnd_patchset=H appspwd=apps japwd=ja logfile=6355941.log systempwd=manager
fnd_patchset= FND PATCHSET LEVEL
japwd = ja_top password
drv_file=patchnumber.drv file
2. How do you know what are india localization patches applied?
Solution : using JAI_APPLIED_PATCHES
Wednesday, September 03, 2008
Unix Important
1. How do you delete 3 days old log files?
Usage: find /location -name "*.log" -mtime +3 -exec rm -rf{} \;
Example : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;
2. Display latest 20 largest files/directories in current directory?
Solution: du -ka sort -n tail -20
3. How do you display/remove Specifice Month files in Unix?
Solution: rm `ls -l grep Jun awk '{print $9}'`
4. How do you find the files which contains a specific Word?
Solution : find /home/ganesh \( -type f \) -exec grep -l test {} \;
Usage: find /location -name "*.log" -mtime +3 -exec rm -rf{} \;
Example : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;
2. Display latest 20 largest files/directories in current directory?
Solution: du -ka sort -n tail -20
3. How do you display/remove Specifice Month files in Unix?
Solution: rm `ls -l grep Jun awk '{print $9}'`
4. How do you find the files which contains a specific Word?
Solution : find /home/ganesh \( -type f \) -exec grep -l test {} \;
Patching Issues
Patching Issues/Sollutions/WorkArounds
During Patching, If you get different AD Worker Errors:
1. AD Worker error:
The following ORACLE error:
occurred while executing the SQL statement:
GRANT select on GV$LOGFILE to em_oam_monitor_role
Error occurred in file
/appltop/apps/ad/11.5.0/patch/115/sql/ademusr.sql
Work Around:
->connect DB / as sysdba
->grant select on GV_$LOGFILE to system with grant option.
->connect system/systempwd.
->grant select on GV$LOGFILE to em_oam_monitor_role.
-> Restart the failed worker using adctrl.
2.AD Worker error:
The following ORACLE error:ORA-12801: error signaled in parallel query server P000ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundoccurred while executing the SQL statement:CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXXAD Worker error:Unable to compare or correct tables or indexes or keysbecause of the error above
Work Around
->Execute the following SQL to prevent errors during Patch Application through adpatch:
->SELECT TRANSACTION_ID, count(*)FROM ICX.ICX_TRANSACTIONSGROUP BY TRANSACTION_IDHAVING count(*)>1
->If the Above query returns any Row then Please execute the following SQL :
->$ICX_TOP/sql (named ICXDLTMP.sql).
During Patching, If you get different AD Worker Errors:
1. AD Worker error:
The following ORACLE error:
occurred while executing the SQL statement:
GRANT select on GV$LOGFILE to em_oam_monitor_role
Error occurred in file
/appltop/apps/ad/11.5.0/patch/115/sql/ademusr.sql
Work Around:
->connect DB / as sysdba
->grant select on GV_$LOGFILE to system with grant option.
->connect system/systempwd.
->grant select on GV$LOGFILE to em_oam_monitor_role.
-> Restart the failed worker using adctrl.
2.AD Worker error:
The following ORACLE error:ORA-12801: error signaled in parallel query server P000ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundoccurred while executing the SQL statement:CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXXAD Worker error:Unable to compare or correct tables or indexes or keysbecause of the error above
Work Around
->Execute the following SQL to prevent errors during Patch Application through adpatch:
->SELECT TRANSACTION_ID, count(*)FROM ICX.ICX_TRANSACTIONSGROUP BY TRANSACTION_IDHAVING count(*)>1
->If the Above query returns any Row then Please execute the following SQL :
->$ICX_TOP/sql (named ICXDLTMP.sql).
Apps DBA - Tracing
Oracle Apps DBA - Enable Trace Application level
1. How do you enable/disable a trace to a Oracle Application Forms Session?
solution:
1. Connection to Oracle Applications
2. Navigate to the particular form, which you want trace to be enabled
3. Goto Menu Help->Diagnostics->Trace->Regular Trace and select it
4. It will ask you for Apps Password. Provide it
5. Then it will show the file path where trace is going to be generated
6. Ask developer to perform their transactions, once they are done disable the trace
7. Goto to that location to get the trace file
8. Get the trace out put file using tkprof with different options
To disable Trace Session
Goto Menu Help-> Diagnostics->Trace->No Trace
2. How do you enable/disable a trace to a Oracle Application Forms Session? (Other Way)
Solution:
1. Get the serial #, sid of particular form session by navigating Help->about Oracle applicatins
2. Connect to database using sqlplus with relavant user
3. execute dbms_system.set_sql_trace_in_session(2122,332,TRUE);
4. Select spid from v$process where addr=(select paddr from v$session where sid=2122);
5. You will get spid like 4515 for above statement
6. Goto udump location and type ls -ltr *4515* you will get trace file
To disable Trace Session
1. execute dbms_system.set_sql_trace_in_session(2122,332,FALSE);
3. How do you enable/disable a trace to a Concurrent Program?
Solution:
1. Connect to Oracle Applications
2. Navigate to System Administrator->Concurrent->Program->Define
3. Query the concurrent program on which you want to enable trace.
4. Check the enable trace check box bottom of the screen, Save it.
5. Ask the developer to submit the request, Once the request got submitted and completed normal.
6. Get the spid as select oracle_process_id from apps.fnd_concurrent_requests where request_id=456624.
7. You will get a spid like 12340.
8. Goto Udump and ls -ltr *12344*.
9 . You will get trace file.
1. How do you enable/disable a trace to a Oracle Application Forms Session?
solution:
1. Connection to Oracle Applications
2. Navigate to the particular form, which you want trace to be enabled
3. Goto Menu Help->Diagnostics->Trace->Regular Trace and select it
4. It will ask you for Apps Password. Provide it
5. Then it will show the file path where trace is going to be generated
6. Ask developer to perform their transactions, once they are done disable the trace
7. Goto to that location to get the trace file
8. Get the trace out put file using tkprof with different options
To disable Trace Session
Goto Menu Help-> Diagnostics->Trace->No Trace
2. How do you enable/disable a trace to a Oracle Application Forms Session? (Other Way)
Solution:
1. Get the serial #, sid of particular form session by navigating Help->about Oracle applicatins
2. Connect to database using sqlplus with relavant user
3. execute dbms_system.set_sql_trace_in_session(2122,332,TRUE);
4. Select spid from v$process where addr=(select paddr from v$session where sid=2122);
5. You will get spid like 4515 for above statement
6. Goto udump location and type ls -ltr *4515* you will get trace file
To disable Trace Session
1. execute dbms_system.set_sql_trace_in_session(2122,332,FALSE);
3. How do you enable/disable a trace to a Concurrent Program?
Solution:
1. Connect to Oracle Applications
2. Navigate to System Administrator->Concurrent->Program->Define
3. Query the concurrent program on which you want to enable trace.
4. Check the enable trace check box bottom of the screen, Save it.
5. Ask the developer to submit the request, Once the request got submitted and completed normal.
6. Get the spid as select oracle_process_id from apps.fnd_concurrent_requests where request_id=456624.
7. You will get a spid like 12340.
8. Goto Udump and ls -ltr *12344*.
9 . You will get trace file.
Oracle Apps DBA - Concurrent Managers
Oracle Apps DBA - Concurrent Managers
1. How do you start/stop/check Concurrent Managers?
Solution:
cd $COMMON_TOP/admin/scripts/context_name/
-> adcmctl.sh start apps/appspwd
-> adcmctl.sh stop apps/appspwd
-> adcmctl.sh status apps/appspwd
-> ps -ef grep FNDLIBR grep applmgr
2. How do you create custom concurrent manager?
Solution:
1. Login to System Administrator Responsibility
2. Navigate to Concurrent > Manager > Define
Manager Field: Custom Manager-
Short Name: CUSTOMCM-
Type: Concurrent Manager-
Program Library: FNDLIBR-
Enter desired Cache-
Work Shifts: Standard-
Enter number of Processes-
Provide Specialization Rules- Save
3. Navigate to Concurrent > Manager > Administer- Activate the Custom Manager
3. How to Start the Concurrent Manager from the Operating system?
solution:
startmgr [parameters]
Example: startmgr sysmgr="applsys/fnd" mgrname="std" printer="hqseq1"mailto="jsmith" restart="N" logfile="mgrlog" sleep="90" pmon="5" quesiz="10"
Parameters:
[sysmgr="fnd_usernamd/fnd_password"] [mgrname="mgrname"]
[printer=printer]
[mailto="userid1 userid2...]
[restart="Nminutes"]
[logfile="log_file_name"]
[sleep="new_check"]
[pmon="manager_check"]
[quesiz="number_check"]
[diag="YN"]
4. EachConcurrent Request Phase and Status Meaning?
Solution:
Phase Status Description
PENDING Normal Request is waiting for the next available manager.
PENDING Standby Program to run request is incompatible with other program(s) currently running.
PENDING Scheduled Request is scheduled to start at a future time or date.
PENDING Waiting A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete.
RUNNING Normal Request is running normally.
RUNNING Paused Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.
RUNNING Resuming All requests submitted by the same parent request have completed running. The Parent request resumes running.
RUNNING Terminating Request is terminated by choosing the Cancel Request button in Requests window.
COMPLETED Normal Request completed successfully.
COMPLETED Error Request failed to complete successfully.
COMPLETED Warning Request completed with warnings. For example, a request is generated successfully but fails to print.
COMPLETED Cancelled Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.
COMPLETED Terminated Request is terminated by choosing the Cancel Request button in the Requests window.
INACTIVE Disabled Program to run request is not enabled. Contact your system administrator.
INACTIVE On Hold Pending request is placed on hold by choosing the Hold Request button in the Requests window.
INACTIVE No Manager No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.
1. How do you start/stop/check Concurrent Managers?
Solution:
cd $COMMON_TOP/admin/scripts/context_name/
-> adcmctl.sh start apps/appspwd
-> adcmctl.sh stop apps/appspwd
-> adcmctl.sh status apps/appspwd
-> ps -ef grep FNDLIBR grep applmgr
2. How do you create custom concurrent manager?
Solution:
1. Login to System Administrator Responsibility
2. Navigate to Concurrent > Manager > Define
Manager Field: Custom Manager-
Short Name: CUSTOMCM-
Type: Concurrent Manager-
Program Library: FNDLIBR-
Enter desired Cache-
Work Shifts: Standard-
Enter number of Processes-
Provide Specialization Rules- Save
3. Navigate to Concurrent > Manager > Administer- Activate the Custom Manager
3. How to Start the Concurrent Manager from the Operating system?
solution:
startmgr [parameters]
Example: startmgr sysmgr="applsys/fnd" mgrname="std" printer="hqseq1"mailto="jsmith" restart="N" logfile="mgrlog" sleep="90" pmon="5" quesiz="10"
Parameters:
[sysmgr="fnd_usernamd/fnd_password"] [mgrname="mgrname"]
[printer=printer]
[mailto="userid1 userid2...]
[restart="Nminutes"]
[logfile="log_file_name"]
[sleep="new_check"]
[pmon="manager_check"]
[quesiz="number_check"]
[diag="YN"]
4. EachConcurrent Request Phase and Status Meaning?
Solution:
Phase Status Description
PENDING Normal Request is waiting for the next available manager.
PENDING Standby Program to run request is incompatible with other program(s) currently running.
PENDING Scheduled Request is scheduled to start at a future time or date.
PENDING Waiting A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete.
RUNNING Normal Request is running normally.
RUNNING Paused Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.
RUNNING Resuming All requests submitted by the same parent request have completed running. The Parent request resumes running.
RUNNING Terminating Request is terminated by choosing the Cancel Request button in Requests window.
COMPLETED Normal Request completed successfully.
COMPLETED Error Request failed to complete successfully.
COMPLETED Warning Request completed with warnings. For example, a request is generated successfully but fails to print.
COMPLETED Cancelled Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.
COMPLETED Terminated Request is terminated by choosing the Cancel Request button in the Requests window.
INACTIVE Disabled Program to run request is not enabled. Contact your system administrator.
INACTIVE On Hold Pending request is placed on hold by choosing the Hold Request button in the Requests window.
INACTIVE No Manager No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.
Oracle DBA -Performance Tuning Questions
Oracle DBA - Performance Tuning Interview Questions
1. What is Performance Tuning?
Ans: Making optimal use of system using existing resources called performace tuning.
2. Types of Tunings?
Ans: 1. CPU Tuning 2. Memory Tuning 3. IO Tuning 4. Application Tuning 5. Databse Tuning
3. What Mailny Database Tuning contains?
Ans: 1. Hit Ratios 2. Wait Events
3. What is an optimizer?
Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement
4. Types of Optimizers?
Ans: 1. RBO(Rule Based Optimizer) 2. CBO(Cost Based Optimzer)
5. Which init parameter is used to make use of Optimizer?
Ans: optimizer_mode= rule----RBO cost---CBO choose--------First CBO otherwiser RBO
6. Which optimizer is the best one?
Ans: CBO
7. What are the pre requsited to make use of Optimizer?
Ans: 1. Set the optimizer mode 2. Collect the statistics of an object
8. How do you collect statistics of a table?
Ans: analyze table emp compute statistics or analyze table emp estimate statistics
9. What is the diff between compute and estimate?
Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read
10. What wll happen if you set the optimizer_mode=choose?Ans: If the statistics of an object is available then CBO used. if not RBO will be used
11. Data Dictionay follows which optimzer mode?
Ans: RBO
12. How do you delete statistics of an object?
Ans: analyze table emp delete statistics
13. How do you collect statistics of a user/schema?
Ans: exec dbms_stats.gather_schema_stats(scott)
14. How do you see the statistics of a table?
Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'
15. What are chained rows?
Ans: These are rows, it spans in multiple blocks
16. How do you collect statistics of a user in Oracle Apps?
Ans: fnd_stats package
17. How do you create a execution plan and how do you see?Ans: 1. @?/rdbms/admin/utlxplan.sql --------- it creates a plan_table 2. explain set statement_id='1' for select * from emp; 3. @?/rdbms/admin/utlxpls.sql -------------it display the plan
18. How do you know what sql is currently being used by the session?
Ans: by goind v$sql and v$sql_area
19. What is a execution plan?
Ans: Its a road map how sql is being executed by oracle db?
20. How do you get the index of a table and on which column the index is?
Ans: dba_indexes and dba_ind_columns
21. Which init paramter you have to set to by pass parsing?
Ans: cursor_sharing=force
22. How do you know which session is running long jobs?
Ans: by going v$session_longops
23. How do you flush the shared pool?
Ans: alter system flush shared_pool
24. How do you get the info about FTS?
Ans: using v$sysstat
25. How do you increase the db cache?
Ans: alter table emp cache
26. Where do you get the info of library cache?
Ans: v$librarycache
27. How do you get the information of specific session?
Ans: v$mystat
28. How do you see the trace files?
Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt
29. Types of hits?
Ans: Buffer hit and library hit
30. Types of wait events?
Ans: cpu time and direct path read
1. What is Performance Tuning?
Ans: Making optimal use of system using existing resources called performace tuning.
2. Types of Tunings?
Ans: 1. CPU Tuning 2. Memory Tuning 3. IO Tuning 4. Application Tuning 5. Databse Tuning
3. What Mailny Database Tuning contains?
Ans: 1. Hit Ratios 2. Wait Events
3. What is an optimizer?
Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement
4. Types of Optimizers?
Ans: 1. RBO(Rule Based Optimizer) 2. CBO(Cost Based Optimzer)
5. Which init parameter is used to make use of Optimizer?
Ans: optimizer_mode= rule----RBO cost---CBO choose--------First CBO otherwiser RBO
6. Which optimizer is the best one?
Ans: CBO
7. What are the pre requsited to make use of Optimizer?
Ans: 1. Set the optimizer mode 2. Collect the statistics of an object
8. How do you collect statistics of a table?
Ans: analyze table emp compute statistics or analyze table emp estimate statistics
9. What is the diff between compute and estimate?
Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read
10. What wll happen if you set the optimizer_mode=choose?Ans: If the statistics of an object is available then CBO used. if not RBO will be used
11. Data Dictionay follows which optimzer mode?
Ans: RBO
12. How do you delete statistics of an object?
Ans: analyze table emp delete statistics
13. How do you collect statistics of a user/schema?
Ans: exec dbms_stats.gather_schema_stats(scott)
14. How do you see the statistics of a table?
Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'
15. What are chained rows?
Ans: These are rows, it spans in multiple blocks
16. How do you collect statistics of a user in Oracle Apps?
Ans: fnd_stats package
17. How do you create a execution plan and how do you see?Ans: 1. @?/rdbms/admin/utlxplan.sql --------- it creates a plan_table 2. explain set statement_id='1' for select * from emp; 3. @?/rdbms/admin/utlxpls.sql -------------it display the plan
18. How do you know what sql is currently being used by the session?
Ans: by goind v$sql and v$sql_area
19. What is a execution plan?
Ans: Its a road map how sql is being executed by oracle db?
20. How do you get the index of a table and on which column the index is?
Ans: dba_indexes and dba_ind_columns
21. Which init paramter you have to set to by pass parsing?
Ans: cursor_sharing=force
22. How do you know which session is running long jobs?
Ans: by going v$session_longops
23. How do you flush the shared pool?
Ans: alter system flush shared_pool
24. How do you get the info about FTS?
Ans: using v$sysstat
25. How do you increase the db cache?
Ans: alter table emp cache
26. Where do you get the info of library cache?
Ans: v$librarycache
27. How do you get the information of specific session?
Ans: v$mystat
28. How do you see the trace files?
Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt
29. Types of hits?
Ans: Buffer hit and library hit
30. Types of wait events?
Ans: cpu time and direct path read
Oracle DBA - Interview Questions
Oracle DBA - Interview Questions
1. How do you kill a session from the database?
Ans: alter system kill 'sid,serial#'
Usage : alter system kill '9,8' (Get the info from v$session
2. How do you know whether the process is Server Side Process?
Ans. By seeing the process in ps-ef as oracle+sid
eg: suppose the sid is prod, then the server process is refered as server side process and local=no
3. Daily Activities of a Oracle DBA?
Ans: 1. Check the Databse availability
2. Check the Listerner availability
3. check the alert log filie for errors
4. monitoring space availablilty in tablespaces
5. monitoring mount point (see capacity planning document)
6. Validate Database backup or Archive backup
7. Find objects which is going to reach max extents
8. Database Health check
9. CPU, Processor, Memory usage
4. Where do you get all hidden parameters ?
Ans: In the table x$ksppi
5. How do you see the names from that table?
Ans:select ksppinm,ksppdesc from x$ksppi where substr(ksppinm,1,1)='_'
6. How do increase the count of datafiles?
Ans: Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size
Procedure:
1. open the database
2. Generate the control file change the maxdatafiles
3. open the db in nomount
4. execute the syntax with noresetlogs
5. alter databse open
7. What is the init parameter to make use of profile?
Ans: resource_limits=true
8. How do you know whether the parameter is dynamic or static?
Ans: By going v$parameter and check the fields isses_modifiable and issys_modifible
9. What is the package and procedure name to conver dmt to lmt and vice versa?
Ans: exec dbms_space_admin.tablespace_migrate_from_local("gtb")
exec dbms_space_admin.tablespace_migrate_to_local("gtb1")
10. What is the use of nohup?
Ans: The execution of a specific task is performed in the server side with out any interupting
Usage : nohup cp -r * /tmp/. &
11. Where alert log is stored? What is the parameter?
Ans: in bdump. parameter is background_dump_dest
11. Where trace file are stored? What is the parameter?
Ans: in udump. parameter is user_dump_dest
12. Common Oracle Errors ……
1) ORA-01555 : Snapshot Too Old
2) ORA-01109 : Database Not Mounted
3) ORA-01507 : Database Not Open
4) ORA-01801 : Database already In startup mode.
5) ORA-600 : Internal error code for oracle program
Usage : oerr ORA 600
13. How do you enable traceing while you are in the database?
Ans : alter session set sql_trace=true;
14. If you want to enable tracing in remote system? what will u do?
Ans: exec dbms_system.SET_SQL_TRACE_IN_SESSION(9,3,TRUE);
15. Which role you grant to rman user while configuring rman user
Ans: recover_catalog_owner
16. Where do you get to know the version of your oracle software and what is your version?
Ans: from v$version(field is banner) and the version is 9.2.0.1.0
17. What is the parameters to set in the init.ora if you create db using OMF(Oracle Managed Files)?
Ans: db_create_file_dest=
db_create_online_log_dest_1=
18. What is a runaway session?
Ans: you killed a session in the database but it still remains in the os level and vice versa. Its called runaway session. If runaway session is there cpu consumes more usage.
19. How do you know whether the specific tablespace is in begin backup mode?
Ans: select status from v$backup. if it is active it means it is in begin backup mode
20. If you want to maintain one more archive destination which parameter you have to set ?
Ans:Its a dynamic parameter you have to set log_archive_duplex_dest=
Usage : alter system set log_archive_duplex_dest=
21. How do you know the create syntax of your function/procedure/index/synonym ?
Ans: using function dbms_metadata.get_ddl(object type,object name,owner)
22. What is the password you have to set in the init.ora to enable remote login ?
Ans: remote_login_password_file=exclusive
23.How do u set crontab to delete 5 days old trace files at daily 10'0 clock?
Ans: crontab -e
0 10 * * * /usr/bin/find /u001/admin/udump -name "*.trc" -mtime +5 -exec rm -rf {} \;
save and exit (wq!)
24.How do u know when system was last booted?
Ans: 3 ways 1.uptime cmd
2.top
3.who -b
4.w
24.How do u know load on system?
Ans: 1.w
2.top
25. How do you know when the process is started
Ans : Using ps -ef grep process name
26. Tell me the location of Unix/Solaris log messages stored?
Ans:/var/log/messages(Unix)
/var/adm/messages (solaris)
27.How do you take backup of a controlfile?
Ans: alter database backup controlfille to destination (Database should be open)
file will be save in the your destination
28. What is the parameter to set the user trace enabiling?
Ans: sql_trace = true
29. How do you know whether archive log mode is enable or not?
Ans: issue command 'archive log list' at sqlplus prompt
30. Where do you get the information of quotas?
Ans: dba_ts_quotas view
31. How do you know how much archives are generated ?
Ans: using the view v$log_history
32. What is a stale?
Ans: The redolog file which has not been used yet
33. How do you read the binary file?
Ans: using strings -a
usage : strings -a filename
34.How do you read control file?
Ans: using command tkprof
Usage: tkprof contrl.ctl ctrol2.txt
35. How do you send the data to tape?
Ans: using 1. tar -cvf or 2. cpio
36. How do you connect to db and startup and shutdown the db without having dba group?
Ans: using remote_login_passwordfile
37. When will you take Cold back up especially?
Ans: during upgradation and migration
38. How do you enable/disable debugging mode in unix?
Ans: set -x and set +x
39. How do you get the create syntax of a table or index or function or procedure?
Ans: select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
40. How do you replace a string in vi editor?
Ans: %s/name/newname
41. When ckpt occurs?
Ans: 1. for every 3 seconds
2. when 1/3rd of DB buffer fills
3. when log swtich occurs
4. when databse shuts down
42. In which file oracle inventory information is available?
Ans : /etc/oraInst.loc
42. Where all oracle homes and oracle sid information available?
Ans: /etc/oratab
43. What is the environment variable to set the location of the listener.ora
Ans: TNS_ADMIN
44. How do you know whether listener is running or not
Ans: ps -ef grep tns
45. What are the two steps involved in instace recovery?
Ans: 1. Roll forward (redofiles data to datafiles)
2. Roll backward (undo files to datafiles).
46. If you delete the alert log fle what will happen?
Ans: New alert log will be created automatically
57. How do you create a table in another tablespace name?
Ans: create table xyz (a number) tablespace system
58. What are the modes/options in incomplete recovery?
Ans: cancel based, change based, time based
59. How do you create an alias?
Ans: alias bdump='cd $ORACLE_HOME/rdbms/admin'
60. Types of trace files?
Ans: 1. trace files generated by database (bdump)
2. trace files generated by user(udump)
61. How do you find the files whose are more than 500k?
Ans : fnd . -name "*" -size +500k
62. Where do you configure your hostname in linux?
Ans: vi /etc/hosts
63. What are the types of segments?
Ans: Data Segment,Undo Segment,Temporary segment,Index Segment
64. What is a synonym and different types of synonyms?
Ans: A synonym is an alias for a table,view, sequence or program unit.
1. Public synonym
2. private synonym
65. What are mandatory background processes in Oracle Database?
Ans: smon,pmon,ckpt,dbwr,lgwr
66. How do you make your redolog group inactive?
Ans: alter system switch logfile;
67. How do you drop a tablespace?
Ans: drop tablespace ts1 including contents and datafiles;
68. What is the parameter allows you to create max no. of groups?
Ans: maxlogfiles=
69. What is the paramter allows to create max no. of members in a group?
Ans: maxlognumbers=
70. What Controlfile contains?
Ans: Database name
Database creation time stamp
Address of datafiles and redolog files
Check point information
SCN information
71. What are the init parameters you have to set to make use of undo management?
Ans: undo_tablespace= undotbs1
undo_management= auto
undo_retention= time in minutes
comment rollback_segment
73. How do you enable monitoring of a table?
Ans: alter table tablename monitoring
74. How do you disable monitoring of a table?
Ans: alter table tablename no monitoring
75. How do you check the status of the table whether monitoring or not?
Ans: select table_name, monitoring from dba_table where owner='scott;
76. In which table records of monitoirg will be stored?
Ans: dba_tab_modificaitons
77. How do you get you demo files get created in your user?
Ans: ?/sqlplus/demo/demobld.sql execute this script on which user you want
78. What is netstat? and its Usage?
Ans: it is a utility to know the port numbers availability. Usage: netstat -na grep port number
79.How do you make use of stats pack?
Ans: 1. spcreate.sql (?/rdbms/admin/) -- to create statspack
2. execute statspack.snap -- to collect datbase snaps
3. spreport.sql-- to generate reports(in your current directory)
4. spauto.sql----to schedule a job
5. sppurge.sql---to delete statistics
6. spdrop.sql----to drop the statistics
1. How do you kill a session from the database?
Ans: alter system kill 'sid,serial#'
Usage : alter system kill '9,8' (Get the info from v$session
2. How do you know whether the process is Server Side Process?
Ans. By seeing the process in ps-ef as oracle+sid
eg: suppose the sid is prod, then the server process is refered as server side process and local=no
3. Daily Activities of a Oracle DBA?
Ans: 1. Check the Databse availability
2. Check the Listerner availability
3. check the alert log filie for errors
4. monitoring space availablilty in tablespaces
5. monitoring mount point (see capacity planning document)
6. Validate Database backup or Archive backup
7. Find objects which is going to reach max extents
8. Database Health check
9. CPU, Processor, Memory usage
4. Where do you get all hidden parameters ?
Ans: In the table x$ksppi
5. How do you see the names from that table?
Ans:select ksppinm,ksppdesc from x$ksppi where substr(ksppinm,1,1)='_'
6. How do increase the count of datafiles?
Ans: Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size
Procedure:
1. open the database
2. Generate the control file change the maxdatafiles
3. open the db in nomount
4. execute the syntax with noresetlogs
5. alter databse open
7. What is the init parameter to make use of profile?
Ans: resource_limits=true
8. How do you know whether the parameter is dynamic or static?
Ans: By going v$parameter and check the fields isses_modifiable and issys_modifible
9. What is the package and procedure name to conver dmt to lmt and vice versa?
Ans: exec dbms_space_admin.tablespace_migrate_from_local("gtb")
exec dbms_space_admin.tablespace_migrate_to_local("gtb1")
10. What is the use of nohup?
Ans: The execution of a specific task is performed in the server side with out any interupting
Usage : nohup cp -r * /tmp/. &
11. Where alert log is stored? What is the parameter?
Ans: in bdump. parameter is background_dump_dest
11. Where trace file are stored? What is the parameter?
Ans: in udump. parameter is user_dump_dest
12. Common Oracle Errors ……
1) ORA-01555 : Snapshot Too Old
2) ORA-01109 : Database Not Mounted
3) ORA-01507 : Database Not Open
4) ORA-01801 : Database already In startup mode.
5) ORA-600 : Internal error code for oracle program
Usage : oerr ORA 600
13. How do you enable traceing while you are in the database?
Ans : alter session set sql_trace=true;
14. If you want to enable tracing in remote system? what will u do?
Ans: exec dbms_system.SET_SQL_TRACE_IN_SESSION(9,3,TRUE);
15. Which role you grant to rman user while configuring rman user
Ans: recover_catalog_owner
16. Where do you get to know the version of your oracle software and what is your version?
Ans: from v$version(field is banner) and the version is 9.2.0.1.0
17. What is the parameters to set in the init.ora if you create db using OMF(Oracle Managed Files)?
Ans: db_create_file_dest=
db_create_online_log_dest_1=
18. What is a runaway session?
Ans: you killed a session in the database but it still remains in the os level and vice versa. Its called runaway session. If runaway session is there cpu consumes more usage.
19. How do you know whether the specific tablespace is in begin backup mode?
Ans: select status from v$backup. if it is active it means it is in begin backup mode
20. If you want to maintain one more archive destination which parameter you have to set ?
Ans:Its a dynamic parameter you have to set log_archive_duplex_dest=
Usage : alter system set log_archive_duplex_dest=
21. How do you know the create syntax of your function/procedure/index/synonym ?
Ans: using function dbms_metadata.get_ddl(object type,object name,owner)
22. What is the password you have to set in the init.ora to enable remote login ?
Ans: remote_login_password_file=exclusive
23.How do u set crontab to delete 5 days old trace files at daily 10'0 clock?
Ans: crontab -e
0 10 * * * /usr/bin/find /u001/admin/udump -name "*.trc" -mtime +5 -exec rm -rf {} \;
save and exit (wq!)
24.How do u know when system was last booted?
Ans: 3 ways 1.uptime cmd
2.top
3.who -b
4.w
24.How do u know load on system?
Ans: 1.w
2.top
25. How do you know when the process is started
Ans : Using ps -ef grep process name
26. Tell me the location of Unix/Solaris log messages stored?
Ans:/var/log/messages(Unix)
/var/adm/messages (solaris)
27.How do you take backup of a controlfile?
Ans: alter database backup controlfille to destination (Database should be open)
file will be save in the your destination
28. What is the parameter to set the user trace enabiling?
Ans: sql_trace = true
29. How do you know whether archive log mode is enable or not?
Ans: issue command 'archive log list' at sqlplus prompt
30. Where do you get the information of quotas?
Ans: dba_ts_quotas view
31. How do you know how much archives are generated ?
Ans: using the view v$log_history
32. What is a stale?
Ans: The redolog file which has not been used yet
33. How do you read the binary file?
Ans: using strings -a
usage : strings -a filename
34.How do you read control file?
Ans: using command tkprof
Usage: tkprof contrl.ctl ctrol2.txt
35. How do you send the data to tape?
Ans: using 1. tar -cvf or 2. cpio
36. How do you connect to db and startup and shutdown the db without having dba group?
Ans: using remote_login_passwordfile
37. When will you take Cold back up especially?
Ans: during upgradation and migration
38. How do you enable/disable debugging mode in unix?
Ans: set -x and set +x
39. How do you get the create syntax of a table or index or function or procedure?
Ans: select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
40. How do you replace a string in vi editor?
Ans: %s/name/newname
41. When ckpt occurs?
Ans: 1. for every 3 seconds
2. when 1/3rd of DB buffer fills
3. when log swtich occurs
4. when databse shuts down
42. In which file oracle inventory information is available?
Ans : /etc/oraInst.loc
42. Where all oracle homes and oracle sid information available?
Ans: /etc/oratab
43. What is the environment variable to set the location of the listener.ora
Ans: TNS_ADMIN
44. How do you know whether listener is running or not
Ans: ps -ef grep tns
45. What are the two steps involved in instace recovery?
Ans: 1. Roll forward (redofiles data to datafiles)
2. Roll backward (undo files to datafiles).
46. If you delete the alert log fle what will happen?
Ans: New alert log will be created automatically
57. How do you create a table in another tablespace name?
Ans: create table xyz (a number) tablespace system
58. What are the modes/options in incomplete recovery?
Ans: cancel based, change based, time based
59. How do you create an alias?
Ans: alias bdump='cd $ORACLE_HOME/rdbms/admin'
60. Types of trace files?
Ans: 1. trace files generated by database (bdump)
2. trace files generated by user(udump)
61. How do you find the files whose are more than 500k?
Ans : fnd . -name "*" -size +500k
62. Where do you configure your hostname in linux?
Ans: vi /etc/hosts
63. What are the types of segments?
Ans: Data Segment,Undo Segment,Temporary segment,Index Segment
64. What is a synonym and different types of synonyms?
Ans: A synonym is an alias for a table,view, sequence or program unit.
1. Public synonym
2. private synonym
65. What are mandatory background processes in Oracle Database?
Ans: smon,pmon,ckpt,dbwr,lgwr
66. How do you make your redolog group inactive?
Ans: alter system switch logfile;
67. How do you drop a tablespace?
Ans: drop tablespace ts1 including contents and datafiles;
68. What is the parameter allows you to create max no. of groups?
Ans: maxlogfiles=
69. What is the paramter allows to create max no. of members in a group?
Ans: maxlognumbers=
70. What Controlfile contains?
Ans: Database name
Database creation time stamp
Address of datafiles and redolog files
Check point information
SCN information
71. What are the init parameters you have to set to make use of undo management?
Ans: undo_tablespace= undotbs1
undo_management= auto
undo_retention= time in minutes
comment rollback_segment
73. How do you enable monitoring of a table?
Ans: alter table tablename monitoring
74. How do you disable monitoring of a table?
Ans: alter table tablename no monitoring
75. How do you check the status of the table whether monitoring or not?
Ans: select table_name, monitoring from dba_table where owner='scott;
76. In which table records of monitoirg will be stored?
Ans: dba_tab_modificaitons
77. How do you get you demo files get created in your user?
Ans: ?/sqlplus/demo/demobld.sql execute this script on which user you want
78. What is netstat? and its Usage?
Ans: it is a utility to know the port numbers availability. Usage: netstat -na grep port number
79.How do you make use of stats pack?
Ans: 1. spcreate.sql (?/rdbms/admin/) -- to create statspack
2. execute statspack.snap -- to collect datbase snaps
3. spreport.sql-- to generate reports(in your current directory)
4. spauto.sql----to schedule a job
5. sppurge.sql---to delete statistics
6. spdrop.sql----to drop the statistics
Oracle Apps DBA - Complete Patching
1. How do you Apply a application patch?
-> Using adpatch
2. Complete Usage of adpatch?
1. download the patch in three ways.
a) Using OAM-Open Internet Exploreer->Select Oracle Application Manager-> Navigate to Patch Wizard -> Select Download Patches -> Give the patch number(more than one patch give patch numbers separated by comma-> Select option download only->Select langauge and Platform-> Give date and time-> submit ok
Note: Before doing this Your oracle apps should be configured with metalink credentials and proxy settings
b) If your unix system is configured with metalink then goto your applmgr account and issue following command
1.ftp updates.oracle.com
2.Give metalink username and password
3.After connecting, cd patch number
4. ls -ltr
5. get patchnumber.zip(select compatiable to OS)
c) Third way is connect to metalink.oracle.com.
1. After logging into metalink with your username and password
2. Goto Quickfind->Select patch numer-> Give patch number->Patch will be displayed->Select os type->Select download
3. ftp this patch to your unix environment
2. Apply the patch?
1. unzip downloaded patch using unzip
eg: unzip p6241811_11i_GENERIC.zip
2. Patch directory will be unzip with patch number.
3. Goto that directory read readme.txt completely.
4. Make sure that Middle tier should be down, Oracle apps is in maintainance mode and database and listener is UP
5. Note down invalid objects count before patching
6. Goto patch Directory and type adpatch
7. It will ask you some inputs from you like, is this your appl_top,common_top, logfile name,sytem pwd, apps pwd, patch directory location, u driver name etc. Provide everything
2. During Patch What needs to be done?
1. Goto $APPL_TOP/admin/SID/log
2. tail -f patchnumber.log(Monitor this file in another session)
3. tail -f patchnumber.lgi(Monitor this file in another session)
4. TOP comand in another session for CPU Usage
3. Adcontroller during patching?
1. During patching if worker fails, restart failed worker using adctrl(You wil find the option when u enter into adctrl)
2. If again worker fails, Goto $APPL_TOP/admin/SID/log/workernumber.log
3. Check for the error, fix it restart the worker using adctrl
4. If you the issue was not fixed, If oracle recommends if it can e ignorable, skip the worker using adctrl with hidden option 8 and give the worker number
4. Log files during patching?
1. patchnumber.log ($APPL_TOP/admin/SID/LOG/patchnumber.log)
2. patchnumber.lgi($APPL_TOP/admin/SID/LOG/patchnumber.lgi)
3. adworker.log($APPL_TOP/admin/SID/LOG/adworker001.log)
4. l.req($APPL_TOP/admin/SID/LOG/l1248097.req)
5. adrelink.log($APPL_TOP/admin/SID/LOG/adrelink.log)
6. adrelink.lsv($APPL_TOP/admin/SID/LOG/adrelink.lsv)
7.autoconfig.log($APPL_TOP/admin/SID/LOG/autoconfig_3307.log)
5. useful tables for patching?
1. ad_applied_patches->T know patches applied
2. ad_bugs->ugs info
3. fnd_installed_processes
4. ad_deferred_jobs
5. fnd_product_installations(patch level)
6. To know patch Info?
1. You can know whether particular patch is applied or not using ad_applied_patches or ad_bugs
2. Using OAM->Patch wizard-> Give patch numer
3. To know mini pack patchest level, family pack patchest level and patch numbers by executing script called patchsets.sh(It has to be downloaded from metalink
7. Reduce patch time?
1. using defaults file
2. Different adpatch options you can get these options by typing adpatch help=y(noautoconfig,nocompiledb,hotpatch,novalidate,nocompilejsp,nocopyportion,nodatabaseportion,nogenerateportion etc)
3. By merging patches into single file
4. Distributed AD if your appl_top is shared
5. Staged APPL_TOP while in production env
8. Usage of Admerging?
1. You can merge number of patches into single patch
2. create two directories like eg: merge_source and merged_dest
3. Copy all patches directories to merge_source
4. admrgpch -s merge_source -d merged_dest -logfile logfile.log
5. merged patch will be generated into merged_dest directory and driver name wil be u_merged.drv
8. Usage of Adsplice?
1. Download splice patch, and unzip it
2. Read the readme.txt perfect
3. As per read me, copy following three files to $APPL_TOP/admin
izuprod.txt
izuterr.txt
newprods.txt
4. open newprods.txt using vi and modify the file by giving correct tablespace names available in your environment
5. run adsplice in appl_top/admin directory
-> Using adpatch
2. Complete Usage of adpatch?
1. download the patch in three ways.
a) Using OAM-Open Internet Exploreer->Select Oracle Application Manager-> Navigate to Patch Wizard -> Select Download Patches -> Give the patch number(more than one patch give patch numbers separated by comma-> Select option download only->Select langauge and Platform-> Give date and time-> submit ok
Note: Before doing this Your oracle apps should be configured with metalink credentials and proxy settings
b) If your unix system is configured with metalink then goto your applmgr account and issue following command
1.ftp updates.oracle.com
2.Give metalink username and password
3.After connecting, cd patch number
4. ls -ltr
5. get patchnumber.zip(select compatiable to OS)
c) Third way is connect to metalink.oracle.com.
1. After logging into metalink with your username and password
2. Goto Quickfind->Select patch numer-> Give patch number->Patch will be displayed->Select os type->Select download
3. ftp this patch to your unix environment
2. Apply the patch?
1. unzip downloaded patch using unzip
eg: unzip p6241811_11i_GENERIC.zip
2. Patch directory will be unzip with patch number.
3. Goto that directory read readme.txt completely.
4. Make sure that Middle tier should be down, Oracle apps is in maintainance mode and database and listener is UP
5. Note down invalid objects count before patching
6. Goto patch Directory and type adpatch
7. It will ask you some inputs from you like, is this your appl_top,common_top, logfile name,sytem pwd, apps pwd, patch directory location, u driver name etc. Provide everything
2. During Patch What needs to be done?
1. Goto $APPL_TOP/admin/SID/log
2. tail -f patchnumber.log(Monitor this file in another session)
3. tail -f patchnumber.lgi(Monitor this file in another session)
4. TOP comand in another session for CPU Usage
3. Adcontroller during patching?
1. During patching if worker fails, restart failed worker using adctrl(You wil find the option when u enter into adctrl)
2. If again worker fails, Goto $APPL_TOP/admin/SID/log/workernumber.log
3. Check for the error, fix it restart the worker using adctrl
4. If you the issue was not fixed, If oracle recommends if it can e ignorable, skip the worker using adctrl with hidden option 8 and give the worker number
4. Log files during patching?
1. patchnumber.log ($APPL_TOP/admin/SID/LOG/patchnumber.log)
2. patchnumber.lgi($APPL_TOP/admin/SID/LOG/patchnumber.lgi)
3. adworker.log($APPL_TOP/admin/SID/LOG/adworker001.log)
4. l.req($APPL_TOP/admin/SID/LOG/l1248097.req)
5. adrelink.log($APPL_TOP/admin/SID/LOG/adrelink.log)
6. adrelink.lsv($APPL_TOP/admin/SID/LOG/adrelink.lsv)
7.autoconfig.log($APPL_TOP/admin/SID/LOG/autoconfig_3307.log)
5. useful tables for patching?
1. ad_applied_patches->T know patches applied
2. ad_bugs->ugs info
3. fnd_installed_processes
4. ad_deferred_jobs
5. fnd_product_installations(patch level)
6. To know patch Info?
1. You can know whether particular patch is applied or not using ad_applied_patches or ad_bugs
2. Using OAM->Patch wizard-> Give patch numer
3. To know mini pack patchest level, family pack patchest level and patch numbers by executing script called patchsets.sh(It has to be downloaded from metalink
7. Reduce patch time?
1. using defaults file
2. Different adpatch options you can get these options by typing adpatch help=y(noautoconfig,nocompiledb,hotpatch,novalidate,nocompilejsp,nocopyportion,nodatabaseportion,nogenerateportion etc)
3. By merging patches into single file
4. Distributed AD if your appl_top is shared
5. Staged APPL_TOP while in production env
8. Usage of Admerging?
1. You can merge number of patches into single patch
2. create two directories like eg: merge_source and merged_dest
3. Copy all patches directories to merge_source
4. admrgpch -s merge_source -d merged_dest -logfile logfile.log
5. merged patch will be generated into merged_dest directory and driver name wil be u_merged.drv
8. Usage of Adsplice?
1. Download splice patch, and unzip it
2. Read the readme.txt perfect
3. As per read me, copy following three files to $APPL_TOP/admin
izuprod.txt
izuterr.txt
newprods.txt
4. open newprods.txt using vi and modify the file by giving correct tablespace names available in your environment
5. run adsplice in appl_top/admin directory
Oracle Apps DBA - Cloning
Oracle Apps DBA - Simple Clonig Steps
How do you Clone your Oracle Apps System with Rapid Clone?
Let us say:
source is Prod and destination is Test
1. Make sure Test Server is ready
a. Remove existing apps systems and file systems in Test
b. That is look for all mount points sames as Prod
c. Look for the same file structures of the prod.
d. Make sure that same users should be there in test such as oracle and applmgr.
e. make sure that all the mount points and required file systems in test has required permissions.
2. Make Sure everything is ready in Prod.
a. Apply Rapid clone patch.
b. login oracle user, Goto $ORACLE_HOME/appsutil/clone/bin.
c. run perl ./adpreclone.pl dbTier.
d. login applmgr user, Goto $COMMON_TOP/clone/bin.
e. run perl ./adpreclone.pl appsTier.
3. Copy everything(Restore everthing) from Prod to test using some tool (Data protector) or manually, including database
4. After successfull restore from Prod backup/Prod source to Test, Go to Test system, rename all root directories and change ownership to respective users to test directories
Example : mv /y23/data/PRD /y23/data/TEST
mv /z24/data/PRD /z24/data/TEST
chown soracle:dba /y23/data/TEST
chown sapplmgr:dba /y23/data/TEST
5. Check correct oraInst.loc is there in your Test envirnoment
6. Statrt DBTier cloning program , go to soracle user, goto $ORACLE_HOME/appsutil/clone/bin. run the command
perl ./adcfgclone.pl dbTier
7. After running the script, it will ask inputs like
Target System Name, Target System Domain name, Target system Oracle Home, Number of Data Tops and its locations, Target System Display. Give all appropriate values
8. Then actual database cloning part starts. Mean while open other session of putty/telnet/x-manager. tail the logfile of cloning ex: tail -f $ORACLE_HOME/appsutil/clone/bin/CloneContext_032832939.log
9 . Open another session and user TOP command for CPU usage
10 .After completioin of script check log files for any errors
11. Important thing.. Update fnd_concurrent_requests.. Means cancel all existing requests. (change thes status of concurrent requests to completed)
12. Goto sapplmgr user, goto $COMMON_TOP/clone/bin execute the script as perl ./adcfgclone.pl appsTier
13. After executing script it will ask different inputs to enter such as apps password(prod password),Target System Database SID, Target Databse Server node, Target system domain name, Target systems APPL_TOP,COMMON_TOP, ORACLE_HOME, IAS_ORACLE_HOME, Target system Display, Locatino of JDK, UTL_FILE_DIR location. Give all appropriate values. Then scripst starts actual cloning process
14. Open another session of putty/telnet/X-Managers. Tail the logfile as tail -f $COMMON_TOP/clone/bin/CloneContext_06666724.log
15. Open another session of putty/telnet/X-Manager. See the CPU usage using top command
16. Hope appscloning completed successfully. Stop all apps services going $COMMON_TOP/admin/scripts/ContextName/adstpall apps/appspwd
17. Remove temporary files located in $COMMON_TOP/temp/*.t and *.tmp
18. If you have any customizations update init.ora as per your needs. You better to get initprod.ora from the PROD and change it as per your needs
19. Goto database drop production database links and create the database links needed in the test env
20. change the passwords of some users which can be used in test environment
21. Goto $APPL_TOP. update APPSORA.env with test database sid, test server name bothe in small letters and capital letters
22. Update profile options ICX Session Time out for site value
23. Change apps password in wdbsvr.app file located in $IAS_ORACLE/Apache/modplsql/cfg
24. Update schema passwords
25. delete all production logfiles came while refresh
How do you Clone your Oracle Apps System with Rapid Clone?
Let us say:
source is Prod and destination is Test
1. Make sure Test Server is ready
a. Remove existing apps systems and file systems in Test
b. That is look for all mount points sames as Prod
c. Look for the same file structures of the prod.
d. Make sure that same users should be there in test such as oracle and applmgr.
e. make sure that all the mount points and required file systems in test has required permissions.
2. Make Sure everything is ready in Prod.
a. Apply Rapid clone patch.
b. login oracle user, Goto $ORACLE_HOME/appsutil/clone/bin.
c. run perl ./adpreclone.pl dbTier.
d. login applmgr user, Goto $COMMON_TOP/clone/bin.
e. run perl ./adpreclone.pl appsTier.
3. Copy everything(Restore everthing) from Prod to test using some tool (Data protector) or manually, including database
4. After successfull restore from Prod backup/Prod source to Test, Go to Test system, rename all root directories and change ownership to respective users to test directories
Example : mv /y23/data/PRD /y23/data/TEST
mv /z24/data/PRD /z24/data/TEST
chown soracle:dba /y23/data/TEST
chown sapplmgr:dba /y23/data/TEST
5. Check correct oraInst.loc is there in your Test envirnoment
6. Statrt DBTier cloning program , go to soracle user, goto $ORACLE_HOME/appsutil/clone/bin. run the command
perl ./adcfgclone.pl dbTier
7. After running the script, it will ask inputs like
Target System Name, Target System Domain name, Target system Oracle Home, Number of Data Tops and its locations, Target System Display. Give all appropriate values
8. Then actual database cloning part starts. Mean while open other session of putty/telnet/x-manager. tail the logfile of cloning ex: tail -f $ORACLE_HOME/appsutil/clone/bin/CloneContext_032832939.log
9 . Open another session and user TOP command for CPU usage
10 .After completioin of script check log files for any errors
11. Important thing.. Update fnd_concurrent_requests.. Means cancel all existing requests. (change thes status of concurrent requests to completed)
12. Goto sapplmgr user, goto $COMMON_TOP/clone/bin execute the script as perl ./adcfgclone.pl appsTier
13. After executing script it will ask different inputs to enter such as apps password(prod password),Target System Database SID, Target Databse Server node, Target system domain name, Target systems APPL_TOP,COMMON_TOP, ORACLE_HOME, IAS_ORACLE_HOME, Target system Display, Locatino of JDK, UTL_FILE_DIR location. Give all appropriate values. Then scripst starts actual cloning process
14. Open another session of putty/telnet/X-Managers. Tail the logfile as tail -f $COMMON_TOP/clone/bin/CloneContext_06666724.log
15. Open another session of putty/telnet/X-Manager. See the CPU usage using top command
16. Hope appscloning completed successfully. Stop all apps services going $COMMON_TOP/admin/scripts/ContextName/adstpall apps/appspwd
17. Remove temporary files located in $COMMON_TOP/temp/*.t and *.tmp
18. If you have any customizations update init.ora as per your needs. You better to get initprod.ora from the PROD and change it as per your needs
19. Goto database drop production database links and create the database links needed in the test env
20. change the passwords of some users which can be used in test environment
21. Goto $APPL_TOP. update APPSORA.env with test database sid, test server name bothe in small letters and capital letters
22. Update profile options ICX Session Time out for site value
23. Change apps password in wdbsvr.app file located in $IAS_ORACLE/Apache/modplsql/cfg
24. Update schema passwords
25. delete all production logfiles came while refresh
Oracle DBA - 9.2.0.8 Database Upgradation from 9.2.0.5
How do you know whether you Current Oracle Database is 64-Bit or 34-Bit?
solution: cd $ORACLE_HOME\binfile oracle or file ora*
How do you know your HP Unix Version?
Solution: uname -a
Procedure to upgrade database
:
1. Download upgrade patch for your OS and DB compatible. For Example if your current database is 64-bit and your OS is HP unix.Then download 4547809Hp1164bit.zip from Metalink download
2. unzip the patch file. Disk1 directory will get extracted.
3. set display of your unix environment to enable GUI.
4. Open X-Manager or Reflection X, and note down port number and frame number
5. export DISPALY=192.168.0.1:1.0
6. Test GUI by typing xclock.
7. Shut down database and listener.
8. Goto Disk1 directory you will find, runinstaller executable.
9. Invoke OUI(Oracle Universal Installer), by executing ./runInstaller
10. Choose Oracle Home/Name and click next
11. In the middle of the installation, it will ask you to execute root.sh by root user. Do the same
12. After installation completes, close the OUI
13. start the database in migration mode i.e startup migrate
14. Execute catpatch.sql located in ORACLE_HOME/rdmbs/admin. i.e in sql prompt: sql> ?/rdbms/admin/catpatch.sql
15. It will take some time, after that execute utlrp.sql to compile invalid objects i .e sql>?/rdbms/admin/utlrp.sql
16. Shutdown the database and startup in normal mode. i.e shut immediate and startup
17. Start the listener and release the instance
solution: cd $ORACLE_HOME\binfile oracle or file ora*
How do you know your HP Unix Version?
Solution: uname -a
Procedure to upgrade database
:
1. Download upgrade patch for your OS and DB compatible. For Example if your current database is 64-bit and your OS is HP unix.Then download 4547809Hp1164bit.zip from Metalink download
2. unzip the patch file. Disk1 directory will get extracted.
3. set display of your unix environment to enable GUI.
4. Open X-Manager or Reflection X, and note down port number and frame number
5. export DISPALY=192.168.0.1:1.0
6. Test GUI by typing xclock.
7. Shut down database and listener.
8. Goto Disk1 directory you will find, runinstaller executable.
9. Invoke OUI(Oracle Universal Installer), by executing ./runInstaller
10. Choose Oracle Home/Name and click next
11. In the middle of the installation, it will ask you to execute root.sh by root user. Do the same
12. After installation completes, close the OUI
13. start the database in migration mode i.e startup migrate
14. Execute catpatch.sql located in ORACLE_HOME/rdmbs/admin. i.e in sql prompt: sql> ?/rdbms/admin/catpatch.sql
15. It will take some time, after that execute utlrp.sql to compile invalid objects i .e sql>?/rdbms/admin/utlrp.sql
16. Shutdown the database and startup in normal mode. i.e shut immediate and startup
17. Start the listener and release the instance
Subscribe to:
Posts (Atom)
Oracle EBS integration with Oracle IDCS for SSO
Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...
-
Enabling TLS in Oracle Apps R12.2 Here we would be looking at the detailed steps for Enabling TLS in Oracle Apps R12.2 Introduction: ... -
R12.2. Services Start and Stop Procedure All components - Application (Middle Tier) START $INST_TOP/admin/scripts adstrtal.s...
-
Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well ...