How to check Concurrent Managers Status from the Back End?
select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));
Sunday, December 14, 2008
Tuesday, December 09, 2008
Find out Explain Plan of Session
How to get explain plan of Session?
set lines 120
select
lpad(' ', 2*(depth))||operation||' '||options
||' '||decode(object_owner,null,null,
object_owner||'.'||object_name)
||' '||decode(cost,null,null,'Cost = '||cost)
||' '||decode(partition_start,null,null,
'Partition='||partition_start||'..'||partition_stop)
||' '||decode(bytes,null,null,'Bytes= '||bytes)
-- ||' '||decode(access_predicates,null,null,'Access= '||access_predicates)
-- ||' '||decode(filter_predicates,null,null,'Filter= '||filter_predicates)
--, other,other_tag, temp_space
"Execution Plan"
from v$sql_plan
where (address, hash_value) =
(select sql_address, sql_hash_value
from v$session
where sid = &sid)
order by id
/
undefine 1
Note: Pass SID when it prompts
set lines 120
select
lpad(' ', 2*(depth))||operation||' '||options
||' '||decode(object_owner,null,null,
object_owner||'.'||object_name)
||' '||decode(cost,null,null,'Cost = '||cost)
||' '||decode(partition_start,null,null,
'Partition='||partition_start||'..'||partition_stop)
||' '||decode(bytes,null,null,'Bytes= '||bytes)
-- ||' '||decode(access_predicates,null,null,'Access= '||access_predicates)
-- ||' '||decode(filter_predicates,null,null,'Filter= '||filter_predicates)
--, other,other_tag, temp_space
"Execution Plan"
from v$sql_plan
where (address, hash_value) =
(select sql_address, sql_hash_value
from v$session
where sid = &sid)
order by id
/
undefine 1
Note: Pass SID when it prompts
Find out Session Waits in a Database
How to find out Session wait events in a Database?
column event format a41
set pagesize 100
select a.event,count(*),avg(b.wait_time)
from v$session a,v$session_wait b
where a.sid = b.sid
and a.type != 'BACKGROUND'
group by a.event
order by count(*) desc
/
column event format a41
set pagesize 100
select a.event,count(*),avg(b.wait_time)
from v$session a,v$session_wait b
where a.sid = b.sid
and a.type != 'BACKGROUND'
group by a.event
order by count(*) desc
/
How to find out sid of a query running?
How to find out session id of a sql that is running?
SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%ALTER INDEX%';
Note: Where sql_text is some unique part of the sql running
SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%ALTER INDEX%';
Note: Where sql_text is some unique part of the sql running
Check Redolog count hourly basis
How to find , how many redo switches happening every hour?
select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/
select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/
Wednesday, November 19, 2008
Enabling Flashback Database
To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.
1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:
SQL> SELECT STATUS FROM V$INSTANCE;
2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
3. Enable the Flashback Database feature for the whole database:
SQL> ALTER DATABASE FLASHBACK ON;
By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:
SQL> ALTER TABLESPACE test1 FLASHBACK OFF;
You can re-enable flashback logging for a tablespace later with this command:
SQL> ALTER TABLESPACE test1 FLASHBACK ON;
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.
You can disable flashback logging for the entire database with this command:
SQL> ALTER DATABASE FLASHBACK OFF;
You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.
1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:
SQL> SELECT STATUS FROM V$INSTANCE;
2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
3. Enable the Flashback Database feature for the whole database:
SQL> ALTER DATABASE FLASHBACK ON;
By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:
SQL> ALTER TABLESPACE test1 FLASHBACK OFF;
You can re-enable flashback logging for a tablespace later with this command:
SQL> ALTER TABLESPACE test1 FLASHBACK ON;
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.
You can disable flashback logging for the entire database with this command:
SQL> ALTER DATABASE FLASHBACK OFF;
You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.
Requirements for Flashback Database
The requirements for enabling Flashback Database are:
* Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
* You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
* For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
* Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
* You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
* For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
Oracle Flashback Table: Returning Individual Tables to Past States
Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.
Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.
Prerequisites for Using Flashback Table
The prerequisites for performing a FLASHBACK TABLE operation are as follows:
* You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
* You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
* Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:
ALTER TABLE table ENABLE ROW MOVEMENT;
Performing Flashback Table
The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:
FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');
The employee table is restored to its state when the database was at the time specified by the timestamp.
You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:
FLASHBACK TABLE employee TO SCN 123456;
The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:
FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;
The following scenario is typical of the kind of logical corruption where Flashback Table could be used:
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:
FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;
Source: http://www.stanford.edu
Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.
Prerequisites for Using Flashback Table
The prerequisites for performing a FLASHBACK TABLE operation are as follows:
* You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
* You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
* Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:
ALTER TABLE table ENABLE ROW MOVEMENT;
Performing Flashback Table
The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:
FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');
The employee table is restored to its state when the database was at the time specified by the timestamp.
You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:
FLASHBACK TABLE employee TO SCN 123456;
The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:
FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;
The following scenario is typical of the kind of logical corruption where Flashback Table could be used:
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:
FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;
Source: http://www.stanford.edu
Oracle Flashback Query: Recovering at the Row Level
In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.
Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:
SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
Restoring John's information to the table EMPLOYEE requires the following update:
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');
The missing row is re-created with its previous contents, with minimal impact to the running database.
Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:
SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
Restoring John's information to the table EMPLOYEE requires the following update:
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');
The missing row is re-created with its previous contents, with minimal impact to the running database.
Oracle Flashback Technology: Overview
Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.
* Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
* Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
* Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
* Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.
* Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
* Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.
Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.
* Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
* Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
* Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
* Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.
* Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
* Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.
Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.
Saturday, November 15, 2008
Flash Back Feature..
1. How to delete a table permanently?
Usage: drop table purge
eg: drop table emp purge;
2.How to delete a table from recycle bin?
Usage: purge < table name >
eg: purge emp;
3. How to recover a table from recycle bin?
Usage: flashback table "HBKLOEKIMO$#^^&777" to before drop;
Usage1:flashback table "HBKLOEKIMO$#^^&777" to before drop rename to emp1;
4.How to remove all objects in recyclebin?
Usage: purge recyclebin
5. How to show all objects in recyclebin?
Usage: show recyclebin
Usage: drop table
eg: drop table emp purge;
2.How to delete a table from recycle bin?
Usage: purge < table name >
eg: purge emp;
3. How to recover a table from recycle bin?
Usage: flashback table "HBKLOEKIMO$#^^&777" to before drop;
Usage1:flashback table "HBKLOEKIMO$#^^&777" to before drop rename to emp1;
4.How to remove all objects in recyclebin?
Usage: purge recyclebin
5. How to show all objects in recyclebin?
Usage: show recyclebin
Monday, November 10, 2008
Find files greater than 3 GB in HP unix
How do you find files that are greater than 3GB in HP unix?
find / -size +3000000000c -exec ls -l {} \;
find / -size +3000000000c -exec ls -l {} \;
Friday, November 07, 2008
Using srvctl to Manage your 10g RAC Database
Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article, we will look at how -- and why -- to manage your 10g databases with srvctl.
Interacting with CRS and the OCR: srvctl
srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.
Using srvctl
Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.
srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is
srvctl [options]
where command is one of
enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config
and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.
The srvctl commands are summarized in this table:
As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.
Examples
Example 1. Bring up the MYSID1 instance of the MYSID database.
[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1
Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.
[oracle@myserver oracle]$ srvctl stop database -d MYSID
Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.
[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver
Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.
[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver
Example 4. Add a new node, the mynewserver node, to a cluster.
[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A
149.181.201.1/255.255.255.0/eth1
(The -A flag precedes an address specification.)
Example 5. To change the VIP (virtual IP) on a RAC node, use the command
[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address
Example 6. Find out whether the nodeapps on mynewserver are up.
[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver
Example 7. Disable the ASM instance on myserver for maintenance.
[oracle@myserver oracle]$ srvctl disable asm -n myserver
Debugging srvctl
Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.
[oracle@myserver bin]$ export SRVM_TRACE=true
Let's repeat Example 6 with SRVM_TRACE set to true:
[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath
/u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:
/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:
/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:
/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar
-DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n
mynewserver
[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to
file null
[main] [19:53:31:825] [OPSCTLDriver.:94] Security manager is set
[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args
[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word
cmdline
[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...
[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16
[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19
...
[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is true
ONS daemon is running on node: mynewserver
[oracle@myserver oracle]$
Pitfalls
A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.
Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.
Error messages
srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.
Conclusion
srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.
Note: Info from Natalka Roshak's blog
Interacting with CRS and the OCR: srvctl
srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.
Using srvctl
Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.
srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is
srvctl
where command is one of
enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config
and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.
The srvctl commands are summarized in this table:
As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.
Examples
Example 1. Bring up the MYSID1 instance of the MYSID database.
[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1
Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.
[oracle@myserver oracle]$ srvctl stop database -d MYSID
Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.
[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver
Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.
[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver
Example 4. Add a new node, the mynewserver node, to a cluster.
[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A
149.181.201.1/255.255.255.0/eth1
(The -A flag precedes an address specification.)
Example 5. To change the VIP (virtual IP) on a RAC node, use the command
[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address
Example 6. Find out whether the nodeapps on mynewserver are up.
[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver
Example 7. Disable the ASM instance on myserver for maintenance.
[oracle@myserver oracle]$ srvctl disable asm -n myserver
Debugging srvctl
Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.
[oracle@myserver bin]$ export SRVM_TRACE=true
Let's repeat Example 6 with SRVM_TRACE set to true:
[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath
/u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:
/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:
/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:
/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar
-DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n
mynewserver
[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to
file null
[main] [19:53:31:825] [OPSCTLDriver.
[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args
[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word
cmdline
[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...
[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16
[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19
...
[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is true
ONS daemon is running on node: mynewserver
[oracle@myserver oracle]$
Pitfalls
A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.
Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.
Error messages
srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.
Conclusion
srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.
Note: Info from Natalka Roshak's blog
Cluster Ready Services and the OCR
Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.
CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.
CRS manages the following resources:
* The ASM instances on each node
* Databases
* The instances on each node
* Oracle Services on each node
* The cluster nodes themselves, including the following processes, or "nodeapps":
o VIP
o GSD
o The listener
o The ONS daemon
CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.
platforms.
CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.
CRS manages the following resources:
* The ASM instances on each node
* Databases
* The instances on each node
* Oracle Services on each node
* The cluster nodes themselves, including the following processes, or "nodeapps":
o VIP
o GSD
o The listener
o The ONS daemon
CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.
RAC Architecture in Brief
RAC Architecture Overview
1.A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.
2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.
3.Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.
Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.
1.A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.
2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.
3.Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.
Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.
Thursday, November 06, 2008
Concurrent Program Phases and status List
Concurrent Program Phases and status List
Phase_Code :
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
Status_Code :
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
Table: apps.fnd_concurrent_requests
Phase_Code :
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
Status_Code :
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
Table: apps.fnd_concurrent_requests
Friday, October 31, 2008
Check all logs for ORA- errors
Check all logs for ORA- errors
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u
Thursday, October 30, 2008
Get the 10046 trace for a Session?
How to take 10046 trace for a Session?
1. Get the SID from the user?
2. Get the OS Process ID (SPID) from v$session.
3. Use following commands as / as sysdba
oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
1. Get the SID from the user?
2. Get the OS Process ID (SPID) from v$session.
3. Use following commands as / as sysdba
oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
JInitiator 1.1.8.7 Crash when launch it from IE
JInitiator 1.1.8.7 Crash when launch it from IE
Fixes
1. Try Uninstall existing JInitiator 1.1.8.7 and install the same
2. If the above dont work.. Try the following
Rename symcjit.dll into somethnig else ex: symcjit_old.dll
path is C:\Program Files\Oracle\JInitiator 1.1.8.16\bin
Fixes
1. Try Uninstall existing JInitiator 1.1.8.7 and install the same
2. If the above dont work.. Try the following
Rename symcjit.dll into somethnig else ex: symcjit_old.dll
path is C:\Program Files\Oracle\JInitiator 1.1.8.16\bin
Wednesday, October 29, 2008
Get Concurrent Request ID from SID
Get Concurrent Request ID from SID:
select c.request_id, status_code, phase_code, USER_CONCURRENT_PROGRAM_NAME,d.use
r_name requestor, s.sid,p.spid,s.process,s.osuser
,s.username,s.program,s.status,logon_time,last_call_et where sid in ( select sid from gv$access where object like '%XXCCP_OE_ORDER_HEADERS_ALL_QTC%');
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurren
t_programs_tl ct, apps.fnd_user d
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id 11 00:14 show_rid.sql
and c.requested_by = d.user_id
and s.sid = '&enter_sid'
select c.request_id, status_code, phase_code, USER_CONCURRENT_PROGRAM_NAME,d.use
r_name requestor, s.sid,p.spid,s.process,s.osuser
,s.username,s.program,s.status,logon_time,last_call_et where sid in ( select sid from gv$access where object like '%XXCCP_OE_ORDER_HEADERS_ALL_QTC%');
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurren
t_programs_tl ct, apps.fnd_user d
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id 11 00:14 show_rid.sql
and c.requested_by = d.user_id
and s.sid = '&enter_sid'
Check package is used by any session in RAC DB?
select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%E_ORDER_HEADERS_ALL_%');
Important Apps Tables
Important Applications tables?
Users
fnd_user
ak_web_user_sec_attr_values (securing attributes)
fnd_user_resp_groups_direct ( direct responsibilities)
Define Responsibility
fnd_responsibility_vl
Menu Exclusions - fnd_resp_functions ( type, name, desc)
Request Groups
fnd_request_groups (header)
fnd_request_group_units (details)
Oracle Users
fnd_oracle_userid
Data Groups
fnd_data_groups (header)
fnd_data_group_units_v
Requests
fnd_conc_req_summary_v (main)
Request Set
fnd_request_sets_vl (main)
fnd_req_set_stages_form_v (stages)
fnd_request_set_programs (stage_requests)
fnd_descr_flex_col_usage_vl (request parameters)
fnd_req_set_stages_form_v (link stages)
Concurrent Managers
fnd_concurrent_queues_vl (main)
fnd_concurrent_queue_content (specialization rules)
fnd_concurrent_queue_size (work shifts)
work shifts
fnd_concurrent_time_periods
concurrent programs
fnd_concurrent_programs_vl (main)
fnd_descr_flex_col_usage_vl (parameters)
fnd_executables_form_v (executables)
fnd_conflict_domain ( concurrent conflicts domain)
profile
fnd_profile_options_vl
applications
fnd_application_vl
form functions
fnd_form_functions_vl (description)
menus
fnd_menus_vl (header)
fnd_menu_entries_vl (detail)
value sets
fnd_flex_value_sets, fnd_flex_values,
key flexfield segments
fnd_id_flexs
fnd_id_flex_segments_vl
fnd_segment_attribute_values (qualifiers)
Concurrent Managers
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES
FND_CONCURRENT_QUEUE_SIZE
Important FND
fnd_nodes
fnd_responsibility
fnd_databases
fnd_unsuccessful_logins
fnd_languages
fnd_application
fnd_profile_option_values
fnd_logins
fnd_user
fnd_appl_tops
fnd_dm_nodes
fnd_tns_aliases
Important AD
ad_install_processes
ad_sessions
ad_appl_tops
ad_applied_patches
ad_patch_drivers
ad_bugs
Users
fnd_user
ak_web_user_sec_attr_values (securing attributes)
fnd_user_resp_groups_direct ( direct responsibilities)
Define Responsibility
fnd_responsibility_vl
Menu Exclusions - fnd_resp_functions ( type, name, desc)
Request Groups
fnd_request_groups (header)
fnd_request_group_units (details)
Oracle Users
fnd_oracle_userid
Data Groups
fnd_data_groups (header)
fnd_data_group_units_v
Requests
fnd_conc_req_summary_v (main)
Request Set
fnd_request_sets_vl (main)
fnd_req_set_stages_form_v (stages)
fnd_request_set_programs (stage_requests)
fnd_descr_flex_col_usage_vl (request parameters)
fnd_req_set_stages_form_v (link stages)
Concurrent Managers
fnd_concurrent_queues_vl (main)
fnd_concurrent_queue_content (specialization rules)
fnd_concurrent_queue_size (work shifts)
work shifts
fnd_concurrent_time_periods
concurrent programs
fnd_concurrent_programs_vl (main)
fnd_descr_flex_col_usage_vl (parameters)
fnd_executables_form_v (executables)
fnd_conflict_domain ( concurrent conflicts domain)
profile
fnd_profile_options_vl
applications
fnd_application_vl
form functions
fnd_form_functions_vl (description)
menus
fnd_menus_vl (header)
fnd_menu_entries_vl (detail)
value sets
fnd_flex_value_sets, fnd_flex_values,
key flexfield segments
fnd_id_flexs
fnd_id_flex_segments_vl
fnd_segment_attribute_values (qualifiers)
Concurrent Managers
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES
FND_CONCURRENT_QUEUE_SIZE
Important FND
fnd_nodes
fnd_responsibility
fnd_databases
fnd_unsuccessful_logins
fnd_languages
fnd_application
fnd_profile_option_values
fnd_logins
fnd_user
fnd_appl_tops
fnd_dm_nodes
fnd_tns_aliases
Important AD
ad_install_processes
ad_sessions
ad_appl_tops
ad_applied_patches
ad_patch_drivers
ad_bugs
Friday, October 24, 2008
Pinning objects in the Database
How to ping required objects in the Database?
Solution:
Use following syntax
BEGIN
SYS.DBMS_SHARED_POOL.KEEP(''HR.QP_PRIC_RQUEST','P');
end;
HR - is the Owner of the object
QP_PRIC_RQUEST- Object to be pinned
P- Default parameter
Table to know pinning objects status
v$db_object_cache
Solution:
Use following syntax
BEGIN
SYS.DBMS_SHARED_POOL.KEEP(''HR.QP_PRIC_RQUEST','P');
end;
HR - is the Owner of the object
QP_PRIC_RQUEST- Object to be pinned
P- Default parameter
Table to know pinning objects status
v$db_object_cache
Thursday, October 23, 2008
Undo/Rollback Segment used
How do you know How much Undo/Roll back Segment each session consuming ?
COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
set linesize 100
set pagesize 15
Rem # rbs_used.sql
set feedback on
set linesize 132
set echo off
column RBS_NAME format a10
TTITLE 'Total Blocks used by a user in a rbs'
SELECT s.username,
s.sid,
t.used_ublk*8192/1024/1024 "RbsUsed(MB)",
t.used_urec,
t.log_io,
t.phy_io,
-- t.xidusn,
r.usn ,
r.name "RBS_NAME",
t.start_uext,
t.start_time,
t.status
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
order by s.sid
/
COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
set linesize 100
set pagesize 15
Rem # rbs_used.sql
set feedback on
set linesize 132
set echo off
column RBS_NAME format a10
TTITLE 'Total Blocks used by a user in a rbs'
SELECT s.username,
s.sid,
t.used_ublk*8192/1024/1024 "RbsUsed(MB)",
t.used_urec,
t.log_io,
t.phy_io,
-- t.xidusn,
r.usn ,
r.name "RBS_NAME",
t.start_uext,
t.start_time,
t.status
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
order by s.sid
/
Find out Top 10 Memory consuming processes?
How to Find out Top 10 Memory consuming processes?
UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle | tail -10
Pass Database Name in places of SID
UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle
Pass Database Name in places of SID
Find out Mount points Which exceeds 80%
Find out mount points starts with oracle which exceeds 80%?
bdf|grep -e oracle grep '[7-9].%'
bdf|grep -e oracle grep '[7-9].%'
Friday, September 26, 2008
Find out Package Locks
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
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
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
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 ...