How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

There are few methods to check this.

Method 1

Check Patches applied in Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:PORT/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch was returned.

Method 2

Use 'adphrept' utility

Patch History report usage:

adphrept query_depth \
bug_number or ALL \
bug_product or ALL \
end_date_from (mm/dd/rr or ALL) \
end_date_to (mm/dd/rr or ALL) \
patchname/ALL \
patchtype/ALL \
level/ALL \
language/ALL \
appltop/ALL \
limit to forms server? (Y/N) \
limit to web server? (Y/N) \
limit to node server? (Y/N) \
limit to admin server? (Y/N) \
only patches that change DB? (Y/N)

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions

Example: To get the complete patch details for patches applied in Dec 2000:

On UNIX:

$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N

Method 3

Use the following sql.

set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('','');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,
p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/


Note: Please enter Patch number in place of and , e.g '3240000'

Example Output

when p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943'):

..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was EXPLICIT
.
dcollierpc8:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED

Method 4

You might also use the following query, however the most reliable methods are the described above.

SQL> SELECT DISTINCT RPAD(a.bug_number,
11)|| RPAD(e.patch_name,
11)|| RPAD(TRUNC(c.end_date),
12)|| RPAD(b.applied_flag, 4) BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('','');
ORDER BY 1 DESC;

Note: Please enter Patch number in place of and , e.g '3453499'

Check Versions of Oracle Applications Components

Version of Oracle Applications

You may never need this but in case you are looking at a new environment then the simplest way to figure out your application version is by navigating to

Help-> About Oracle Applications from any of the forms sessions.

Or

Connect as apps user
select release_name from apps.fnd_product_Groups;

Version of Applications Forms (fmb,fmx) or reports (rdf)

To find the version of any oracle applications files
strings -a ICQTYED.fmx | grep Header

Alternatively you can also use the adident command
adident Header ICQTYED.fmx

Version of a Java class File
To find the version of a java class file
$ strings | grep '$Header'

Version of JDBC

In your middle tier, edit the jserv.properties file located in the iAS_ ORACLE_HOME/Apache/Jserv/etc directory
Locate the wrapper.classpath that is pointing to the jdbc zip file
/u01/oracle/viscomn/java/jdbc14.zip

Or
http://.:port/OA_HTML/jsp/fnd/aoljtest.jsp
Look for String ” JDBC driver version” under Connection String

Version of Apache
Go to the $iAS/Apache/Apache/bin directory and enter the following command:
$ httpd -version

Version Of The OA Framework
To find out the version of your Oracle appplication Framework
Option 1
http://.:port/OA_HTML/OAInfo.jsp

Option 2
adident Header %FND_TOP%/html/OA.jsp
adident Header %OA_HTML%/OA.jsp

Note: OA.jsp should be of the same version in both the places

Version of Application Product or Patch Set Level.

One of the most common things you will asked by your support engineer is the version or commonly known as the patch set level of your Oracle Applications product, while or after raising your service request. You can query this by logging on to your application database as the apps user.

select patch_level from fnd_product_installations where patch_level like '%AD%';

Version of Discoverer

cd $APPL_TOP/admin
grep -I s_disco_ver_comma **xml

Version of OJSP

Log in to the application server as the applmgr user
cd $OA_HTML
edit the jtflogin.jsp file to add the following line
OJSP Version: <%= application.getAttribute("oracle.jsp.versionNumber") %>
clear your cache and bounce your apache server
soruce the jtflogin.jsp from your browser

http://vis.oneapps.com:8000/OA_HTML/jtflogin.jsp
OJSP Version: 1.1.3.5.2

Version of JSP

Log on to your HTTP Server node, and change to the OA_HTML directory.
Using a text editor, create a file called test.jsp with only the following line:
<%= application.getAttribute("oracle.jsp.versionNumber") %>

Access this JSP from a web browser, using the URL:
http://[your web server]:[your port]/OA_HTML/test.jsp

Version of Portal
select fnd_oracle_schema.getouvalue(’PORTAL’) from dual;

FND_ORACLE_SCHEMA.GETOUVALUE(’PORTAL’)
——————————————————————————–
PORTAL30 <= to retrieve the portal user

select version from PORTAL30.wwc_version$;

VERSION
——————————————————————————–
3.0.9.8.1

or

begin
execute immediate 'select fnd_oracle_schema.getouvalue(''PORTAL'') from
dual' into portal_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||portal_user||''')'
into portal_user_name;
if portal_user is not null then
execute immediate 'select version from '||portal_user||'.wwc_version$'
into portal_ver;
check_message := ' [PASS] Your Applications database contains
Portal version: '||portal_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;

Version of XML Parser

You can find out the version of your XML Parser using the following query
SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION
2 from sys.dual;
XML_VERSION
--------------------------------------------------------------------------------
Oracle XDK Java 9.0.4.0.0 Production

Version of XML Publisher

To check weather XML publisher is installed or not you can query the FND_PRODUCT_INSTALLATIONS table or you can lookup the
reports in Oracle Applications Manager. You can find out the version for your XML publisher from the output of your report or from MetaInfo.class file.

$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.

Version of WorkFlow

You can find out the version of your workflow using the following query
SQL> select TEXT Version from WF_RESOURCES
2 where TYPE = 'WFTKN' and NAME = 'WF_VERSION';
VERSION
--------------------------------------------------------------------------------
2.6.0

Version of Oracle Login Server

begin
execute immediate 'select fnd_oracle_schema.getouvalue(''LOGINSERVER'') from
dual' into sso_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||sso_user||''')' into
sso_user_name;
if sso_user is not null then
execute immediate 'select version from '||sso_user||'.wwc_version$' into
sso_ver;
check_message := ' [PASS] Your Applications database contains Login
Server version: '||sso_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;

Developer Forms Patchset Level

For developer forms patch set level >> Click Here

Verify The Operation Of The Oracle Reports Server For eBusiness Suites 11i

On Oracle Applications 11i, the quickest method to check if the Oracle Reports server is functional is to "temporarily" enable the Oracle Reports Server diagnostic mode and bring up the "Environment" and "RWCGI Key Map" page via a web browser.

--> Enable the Oracle Reports Server diagnostic mode.

Comment out any reference to the REPORTS60_CGINODIAG and REPORTS60_OWSNODIAG parameters from the following files:

$APACHE_TOP/Apache/conf/apps.conf
$FND_TOP/resource/appsweb.cfg
$OAD_TOP/admin/scripts//adrepctl.sh

Bounce the Oracle Reports server and the Apache (Web) server.

--> Environment Check
http://://rwcgi60/showenv?server=

--> "RWCGI Key Map" Check
http://://rwcgi60/showmap?server=

An example is as follows: http://vis.oracle.com:8000/dev60cgi/rwcgi60/showenv?server=REP60_VIS
(The host and port is the same used for Apps login, the Web / Apache node)

If the Oracle Reports server installed with Oracle Applications 11i Succeeds to display the "Environment" and "RWCGI Key Map" page via a web browser, please check with the application product group who owns the troubled report for any required setup at the application level.

If the Oracle Reports server installed with Oracle Applications 11i Fails to display the "Environment" and "RWCGI Key Map" page via a web browser, please open a service request with the ATG group, upload a screen print of the error being encountered, and the following data:

1. Upload as a zip file with the following files.

a. The Apps 8.0.6 $TNS_ADMIN/tnsnames.ora file.
b. $APACHE_TOP/Apache/conf/apps.conf file
c. $ORACLE_HOME/reports60/server/CGIcmd.dat
d. $ORACLE_HOME/reports60/server/REP60_.ora
e. $FND_TOP/resource/appsweb.cfg
f. $OAD_TOP/admin/scripts//adrepctl.sh
(The exact adrepctl.sh file used to start the report server.)

2. Upload the report server's log files.

a. The Apps report server startup log file. The variable REPSRV_LOG within the adrepctl.sh points to the log file.

b. The Oracle Reports server log file.
$ORACLE_HOME/reports60/server/REP60_.log

3. Is the report server currently running; what is the output of ps -ef | grep rwmt ?

Java Version Used by JServ With Applications 11i

Perform the following steps to verify the version from the command prompt:

1. Set the Applications environment by running the APPSORA.env (if not already set)
2. Run the following command from the command prompt

MY_JAVA=`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' \
$IAS_ORACLE_HOME/Apache/Apache/bin/java.sh`; \
$MY_JAVA -version
The output will be similar to the following:

java version "1.4.2_04"Java(TM) 2 Runtime Environment, Standard Edition
(build 1.4.2_04-b05)Java HotSpot(TM) Client VM (build 1.4.2_04-b05, mixed mode)

How to Check Whether an AOL Table is Locked

The sql statements below will help to determine if there is
a lock on the AOL table.


Checking for locked files
=========================

To check whether an AOL table is locked or not:

Sign on to sqlplus as the APPS account.

The example that is being used here is an attempt to modify a user's password
in System Administrator Responsibility, Users Define Form
(Navigation path: Security -> User -> define).

After modifying the password of a user in the User Define Screen:

SQL> select * from v$lock;

  .... take note of the SID number (using '33' for this example)

SQL> select ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, REQUEST 
    from v$lock  where sid=33;

   Here's an example.

ADDR       KADDR      SID TY        ID1        ID2  LMODE  REQUEST
---------- ---------- --- -- ---------- ---------- ------ --------
010AB98C   010ABA58    33 TX     196618     107158      6        0
803CE1D8   803CE1EC    33 TM       4136          0      2        0

v$lock.type:
  TX: Transaction enqueue
  TM: DML enqueue

v$lock.lmode:  Lock mode in which the session holds the lock:
  0 means none
  1 means null
  2 means Row-S (SS)
  3 means Row-X (SX)
  4 means Share (S)
  5 means S/Row-X (SSX)
  6 means Exclusive (X)

v$lock.request: Lock mode in which the process requests the lock.

To find out which table is being locked by TM type lock:
(v$lock.id1 = dba_objects.object_id when v$lock.type = TM)

SQL> select object_name, owner from dba_objects where object_id=4136;

OBJECT_NAME                    OWNER
------------------------------ ---------------
FND_USER                       APPLSYS

 
To list the object which is being accessed by the session id 33:

SQL> select * from v$access  where sid =33;

SID OWNER           OBJECT                         TYPE
--- --------------- ------------------------------ ------------------------
33 APPLSYS         ALR_PROFILE_OPTIONS            TABLE
33 APPLSYS         FND_APPLICATION                TABLE
33 APPLSYS         FND_ATTACHED_DOCUMENTS         TABLE
33 APPLSYS         FND_ATTACHMENT_FUNCTIONS       TABLE
33 APPLSYS         FND_CONCURRENT_PROGRAMS        TABLE
33 APPLSYS         FND_CONCURRENT_REQUESTS        TABLE
33 APPLSYS         FND_CONCURRENT_REQUESTS_S      SEQUENCE
33 APPLSYS         FND_CONC_REQUEST_ARGUMENTS     TABLE
33 APPLSYS         FND_DATA_GROUP_UNITS           TABLE
33 APPLSYS         FND_DOCUMENTS                  TABLE
33 APPLSYS         FND_DOC_CATEGORY_USAGES        TABLE
33 APPLSYS         FND_FORM                       TABLE
33 APPLSYS         FND_FORM_FUNCTIONS             TABLE
33 APPLSYS         FND_LANGUAGES                  TABLE
33 APPLSYS         FND_LOGINS                     TABLE
33 APPLSYS         FND_LOGINS_S                   SEQUENCE
33 APPLSYS         FND_LOGIN_RESPONSIBILITIES     CURSOR
33 APPLSYS         FND_LOGIN_RESP_FORMS           TABLE
33 APPLSYS         FND_LOOKUP_VALUES              TABLE
33 APPLSYS         FND_ORACLE_USERID              TABLE
33 APPLSYS         FND_PRINTER                    TABLE
33 APPLSYS         FND_PRINTER_INFORMATION        TABLE
33 APPLSYS         FND_PRINTER_STYLES             TABLE
33 APPLSYS         FND_RESPONSIBILITY             TABLE
33 APPLSYS         FND_SESSIONS                   CURSOR
33 APPLSYS         FND_USER                       TABLE
33 APPLSYS         FND_USER_RESPONSIBILITY        TABLE
33 APPLSYS         FND_USER_S                     SEQUENCE
.....
111 rows selected

To find out what is the last SQL statement issued:

SQL> select a.sql_text
 2  from v$sqltext a, v$session b
 3  where a.address = b.sql_address
 4  and sid=33
 5* order by piece;

SQL_TEXT
----------------------------------------------------------------
SELECT USER_NAME,DESCRIPTION,EMAIL_ADDRESS,FAX,PASSWORD_LIFESPAN
_DAYS,PASSWORD_LIFESPAN_ACCESSES,START_DATE,END_DATE,EMPLOYEE_ID
,PERSON_TYPE,USER_ID,ENCRYPTED_FOUNDATION_PASSWORD,PASSWORD_DATE
,PASSWORD_ACCESSES_LEFT,ENCRYPTED_USER_PASSWORD,SESSION_NUMBER,L
AST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,CREATION_DATE,LAST_UP
DATE_LOGIN FROM FND_USER WHERE ROWID=:1 FOR UPDATE OF USER_NAME
NOWAIT

7 rows selected.

Note: If you reissue the above sql statement after saving the changes
     in the user define screen, you will see the following output:

SQL_TEXT
----------------------------------------------------------------
UPDATE FND_USER SET USER_NAME=:1,DESCRIPTION=:2,EMAIL_ADDRESS=:3
,FAX=:4,PASSWORD_LIFESPAN_DAYS=:5,PASSWORD_LIFESPAN_ACCESSES=:6,
START_DATE=:7,END_DATE=:8,EMPLOYEE_ID=:9,PERSON_TYPE=:10,USER_ID
=:11,ENCRYPTED_FOUNDATION_PASSWORD=:12,PASSWORD_DATE=:13,PASSWOR
D_ACCESSES_LEFT=:14,ENCRYPTED_USER_PASSWORD=:15,SESSION_NUMBER=:
16,LAST_UPDATE_DATE=:17,LAST_UPDATED_BY=:18,CREATED_BY=:19,CREAT
ION_DATE=:20,LAST_UPDATE_LOGIN=:21 WHERE ROWID=:22

7 rows selected.


To list the processes involved:

SQL> select distinct s.username, p.pid, osuser, p.spid, s.process, s.lockwait
 2  from v$process p, v$session s, v$access a
 3  where a.sid = s.sid
 4  and p.addr = s.paddr
 5  and s.username in ('APPS','APPLSYS')
 6* and s.sid = 33;

USERNAME        PID OSUSER          SPID      PROCESS   LOCKWAIT
-------- ---------- --------------- --------- --------- --------
APPS             11 OraUser         7228      5791:01

SQL> select addr, pid, spid, username, serial#, program, latchwait
 2* from v$process where pid = 11;

ADDR      PID SPID  USERNAME SERIAL# PROGRAM                   LATCHWAI
-------- ---- ----- -------- ------- ------------------------- --------
8018B174   11 7228  oracle7      100 oracle@suppue2(TNS V1-V3)

SQL> select distinct s.username, p.pid, osuser, p.spid, s.process, s.lockwait
 2  from v$process p, v$session s, v$access a
 3  where a.sid = s.sid
 4  and p.addr = s.paddr
 5  and s.username in ('APPS','APPLSYS')
 6* and s.sid = 33

USERNAME        PID OSUSER          SPID      PROCESS   LOCKWAIT
-------- ---------- --------------- --------- --------- --------
APPS             11 OraUser         7228      5791:01

In the above example the LATCHWAIT and LOCKWAIT columns are blank
indicating there is no lock waiting.


At times, it may be difficult to find out the root cause of the "hanging"
problem within the application. The best starting point for this type of
issue is the v$session_wait view.

Check Developer Forms Patch Set Level

1.Login though applmgr account

2.Create a file fpslevel.sh

cat > fpslevel.sh
copy the following contents in fpslevel.sh file.

#!/bin/sh
########################################
#fpslevel.sh - Used to find out the forms patchset level
########################################
clear
PatchLevel=0
f60gen > /dev/null 2>&1
if [ $? -eq 127 ]
then
echo "Environment is not Set"
else
PatchLevel=`f60gen | grep '(Form Compiler)' | grep -v Release | awk -F. '{print $5}' `
echo 'Forms Patchset Level :' ` expr $PatchLevel - 9 `
fi
echo 'For More Information '
echo 'Refer to Metalink Note:232313.1 for More Information'

3.Grant the execute permission to the script
chmod 777 fpslevel.sh

4.Execute the script.
./fpslevel.sh

Obtaining a Formatted Explain Plan

1. Create a Plan Table
----------------------------

Use the utlxplan.sql script to create the table as instructed below.

SQL> @?/rdbms/admin/utlxplan

Note that the plan table format can change between versions so ensure that you create it using the utlxplan script from the current version.

2. To Populate the Plan Table
--------------------------------------

SQL> explain plan for

Explained.

3. Displaying The Execution Plan
------------------------------------------

Version 9.2
--------------

With Oracle 9i version 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.

To generate a formatted explain plan of the query that has just been 'explained':

SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from
table(dbms_xplan.display(‘PLAN_TABLE’, Null,’ALL’));
SQL> spool off

Version 10.2
----------------

In addition to the standard explain plan option, you can pull execution plans from the library cache if the SQL has already been executed.

a) To get the plan of the last executed SQL issue the following:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from table(dbms_xplan.display_cursor(null,null,
'ALL'));

b) If you know the hash value of the SQL, you can use
dbms_xplan.display_cursor as follows:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD));

c) We can also get run time statistics with some additional
options and if we use the gather_plan_statistics hint.

e.g:

SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD,'ALL IOSTATS LAST'))

sql_id: specifies the sql_id value for a specific SQL statement, as shown in V$SQL.SQL_ID, V$SESSION.SQL_ID or V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last executed statement of the current session is shown.

cursor_child_no: specifies the child number for a specific sql cursor, as in V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER. If not specified, all child cursors for the specified sql_id are displayed.

Note that to use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate error message.

Oracle 11g : Alert Log file

Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases. Both these log files are stored inside the ADR home. The ADR root directory is known as ADR BASE. Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. This parameter is set by DIAGNOSTIC_DEST initialization parameter.

If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set
to the directory designated by ORACLE_BASE.

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is
set to ORACLE_HOME/log.

for e.g
SQL> show parameter diagno

NAME TYPE VALUE
-------------------- ------- -----------------------------------------
diagnostic_dest string /u01/oracle/product/ora11g/log

The location of an ADR home is given by the following path, which starts at the ADR base directory:

ADR_BASE/diag/product_type/product_id/instance_id

For example,
for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:

ADR_base/diag/rdbms/ora11g/ora11g/

Within the ADR home directory are subdirectories where the database
instance stores diagnostic data.

alert ( The XML-formatted alert log )

trace ( Background and server process trace files and SQL trace files and text alert.logfiles )

cdump ( Core files )

XML formatted alert.log
-------------------------------
The alert log is named log.xml and is stored in the alert subdirectory of ADR home.
To get the log.xml path
ADR_HOME/diag/product_type/product_id/instance_id/alert

from sqlplus
SQL> select value from v$diag_info where name ='Diag Alert';
ADRCI utility to view a text version of the alert log (with XML tags stripped)

Text formatted alert.log
--------------------------------

The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.

To view the text only alert.log file
ADR_HOME/diag/product_type/product_id/instance_id/trace

from sqlplus
SQL> select value from v$diag_info where name ='Diag Trace';
or
SQL>show parameter background_dump_dest

Open file alert_SID.log with a text editor

The alert log of a database is a chronological log of messages and errors, including the following:

All internal errors (ORA-600), block corruption errors (ORA-1578), and
deadlock errors (ORA-60) that occur

Administrative operations, such as the SQL statements CREATE ALTER DROP DATABASE TABLESPACE and Oracle Enterprise Manager or SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER

Several messages and errors relating to the functions of shared server and dispatcher processes

Errors during the automatic refresh of a materialized view

Oracle 10g : Flashback Table , Query

· Flashback Table :

Flashback Table happens in place by rolling back only the changes made to the table or tables and their dependent objects, such as indexes. Note that Flashback Table is different from Flashback Drop: Flashback Table undoes recent transactions to an existing table whereas Flashback Drop recovers a dropped table; Flashback Table uses data in the undo tablespace whereas Flashback Drop uses the recycle bin.
The FLASHBACK TABLE command brings one or more tables back to a point in time before any number of logical corruptions have occurred on the tables. To be able to flashback a table, you must enable row movement for the table; because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for applications that depend on the table’s ROWIDs to remain constant. Before performing the Flashback Table operation, you first enable row movement in the affected tables, as in the following syntax:

SQL> alter table  enable row movement;

Limitations and Restrictions on Flashback Tables :

· Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

· The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

· Flashback Query :

Flashback Query, available in Oracle 9i, has been enhanced to include two new types of queries:

Flashback Versions Query and Flashback Transaction Query.
Flashback Versions Query allows a user or the DBA to see all versions of a table’s row between two times, and with Flashback Transaction Query you can see all transactions that changed a row between two times.

Flashback Versions Query provides an easy way to show all versions of all rows in a table between two SCNs or time stamps, whether the rows were inserted, deleted, or updated. Even if a row was deleted and reinserted several times, all of these changes are available with Flashback Versions Query.

Flashback Transaction Query, in contrast, drills down into the history of table changes based on a transaction ID. Using Flashback Versions Query, you found out which transaction changed the salary information, but you don’t know who made the change. Flashback Transaction Query provides this additional level of detail.

In contrast to referencing the actual table in Flashback Versions Query, Flashback
Transaction Query uses the data dictionary view FLASHBACK_TRANSACTION_QUERY
to retrieve transaction information for all tables involved in a transaction. This view
provides the SQL statements that you can use to undo the changes made by a particular
transaction.

Flashback Query is Not available after restarting the database.

For Flashback Database >> Click Here

For Flashback Drop >> Click Here

Oracle 10g : Flash Back Drop

· Flashback Drop :

This Feature provides a way to restore accidentally dropped tables.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle places it in a recycle bin. Objects in the recycle bin remain there until the user decides to permanently remove them or until space limitations begin to occur on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users view the recycle bin and undrop the dropped table and its dependent objects.
Flashback Drop is available even after restarting the database.

Limitations and Restrictions on Flashback Drop :

· The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.

· There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.

· While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.

· You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.

· A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.

· Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

· Partitioned index-organized tables are not protected by the recycle bin.

· The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.

For Flash Back Database >> Click Here

For Flash Back Table and Query >> Click Here

What Do All 10g Flashback Features Rely on and their Limitations ?

The New Oracle 10g Flashback Technologies Consist Of The Following :

• Flashback Database
• Flashback Drop
• Flashback Table
  
• Flashback Query

· Flashback Database :

The Flashback Database allows you to flash the entire database back to a specific point-in time. It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is not based on undo data but on flashback logs.

If flashback database is enabled, its flashback logs are stored in the Flash Recovery area. Flashback logs are written sequentially During normal database operation, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.

The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.

DB_FLASHBACK_RETENTION_TARGET:

A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.

The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.

The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM      
       V$FLASHBACK_DATABASE_LOG;

Limitations of Flashback Database :

Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:

· Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.

· You cannot use Flashback Database to undo a shrink datafile operation.

· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

· When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.

For Flashback Drop >> Click Here

For Flashback Tables and Flashback Query >> Click Here

Oracle Apps Upgrade from 11.5.10.2 to Release 12

Upgradation Instructions for Oracle Applications 11.5.10.2 to Release 12
on Redhat Linux AS 4.0 ( Single Node )

Prerequisites

1) Installation of Maintenance Wizard on standalone server

Maintenance Wizard must be installed into an RDBMS 10g with a 10g iAS
ORACLE_HOME on a UNIX or Linux machine.

Here is an example of Maintenance Wizard Installation on a standalone server.

Instructions for Maintenance Wizard Installation

Required RPMs

kernel-2.6.9.-11.EL  ( uname -r )

binutils-2.15.92.0.2-13.i386.rpm
gcc-3.4.3-22.1.i386.rpm
gcc-c++-3.4.3-22.1.i386.rpm
glibc-common-2.3.4-2.9.i386.rpm
libstdc++-3_1.4.3-22.1.i386.rpm
libstdc++-devel-3.4.3-22.1.i386.rpm
openmotif21-2.30-11.RHEL4.4.i386.rpm

Users and Groups :

groupadd oinstall

groupadd dba

useradd -g oinstall -G dba oracle

Configure Kernel Parameter :

vi /etc/sysctl.conf

kernel.sysrq = 0
kernel.msgmnb = 65535
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 142
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

Save and Run sysctl -p

Set up a shell

You must set up either remote shell (rsh/remsh) or secured shell (ssh) between
the Maintenance Wizard node and every remote Applications node. The tool will
not work without having at least one of these shells successfully configured. Follow
the instructions below for OpenSSH shell you wish to use for remote access:

Setting up OpenSSH: Follow this procedure if you will be using OpenSSH:

On the Maintenance Wizard Node:

Log in as the user who will own Maintenance Wizard.

Generate the ssh key by issuing the following command:

ssh-keygen -t rsa

Press the Enter key three times.

CD into the .ssh directory, which is located under the home directory. Note that
a file called id_rsa.pub is created.

Use FTP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.

On the Remote Node(s):

Login as the user who owns the product being upgraded or maintained. For Applications
tiers, use the applmgr account. For the database tiers, use the oracle account.

Remove specialized .login, .profile or similar files which cause extra verbiage to be
displayed to the screen.

Create a directory called .ssh in the home directory.

If an authorized_keys file does not exist, create a new, empty one.

Copy the content of /tmp/id_rsa.pub (from the Maintenance Wizard Node) into a new
file named authorized_keys. (If you already have a file called authorized_keys in this
directory, open it in vi, go to the bottom of the file, add one blank line and then copy
and paste the contents of the file /tmp/id_rsa.pub into the existing file and save it.)

Verify that the .ssh directory permission is set to 700.

Verify that the authorized_keys file permission is set to 600.

Verify that the $HOME directory does not have write permissions for group and others.

chmod go-w $HOME

Ensure that the ssh daemon is running.

Repeat the above steps for each Remote Node.

Test the installation:

Login to the Maintenance Wizard Node as Maintenance Wizard owner.

Execute the following command, replacing [user] with the username on the Remote
Node, and [SERVER] with the host name of the Remote Node:

ssh -l [user] [SERVER]

3. When prompted to confirm the identity, respond "Yes".
4. Disconnect from the server.
5. Execute the following command:

ssh -l [user] [SERVER] echo 'foo'

Only the word foo should appear, followed by the prompt, and you should not be
asked to enter and ID or password. If you are prompted for a password, verify the
following:

The content of the authorized_keys file contains the content of id_rsa.pub of client

The permissions of the authorized_keys file are set to 600.
The permissions of the .ssh directory are set to 700.
The permissions of the $HOME directory do not include execute for group and other.

If more than the word foo appears, then repeat the step above to remove all .login,
.profile or similar files until ONLY the word foo appears.

Maintenance Wizard version 2.x requires Oracle RDBMS 10g (10.2.0) with 10g iAS (10.1.3).

We recommend that you do not attempt to "re-use" an existing 10g database. Install a
new standalone Oracle 10g ORACLE_HOME for use by Maintenance Wizard.

10g Release 2 Database Installation

/etc/security/limits.conf

* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536

Create the directories in which the Oracle software will be installed:

mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01

Login as the oracle user and add the following lines at the end of the .bash_profile file:

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Install RDBMS 10g using the following command:

./runInstaller

-Specify the desired ORACLE_HOME location.

-Choose "Enterprise Edition".

-Uncheck the option called "Create Starter database". Click NEXT.

-Optionally, specify the oraInventory and operating system group, then click NEXT.

-After successful Product-Specific Prerequisite Checks, choose

"Install database Software only" and click NEXT

-Click INSTALL.

-When the installation has completed, exit the installer.

-As same user, Install Oracle 10g iAS ORACLE_HOME.

-Install a new standalone Oracle 10g iAS ORACLE_HOME for use by Maintenance Wizard.

-Set the DISPLAY variable appropriately and export it.

Run the installer.

./runInstaller

-Specify the installation directory.

-Choose "Advanced Installation Mode" and click NEXT.

-Choose "Web Server and Process Management" (or "Integrated Web Server,
J2EE Server and Process Management") and click NEXT.

-Select your desired method of port configuration and click NEXT.

-Optionally, select your desired Administration Instance settings and click NEXT.

-Specify your Cluster Topology Configuration and click NEXT.

-Click INSTALL.

-Exit the installer.

When the installation has completed, exit the installer.

-Create the database.

-Set the ORACLE_HOME variable appropriately.

-ORACLE_HOME=<path to the RDBMS HOME>; export ORACLE_HOME

-Change to the bin directory.

-cd $ORACLE_HOME/bin

Run the database creation utility.

./dbca

-Choose "Create a Database".

-Choose "General Purpose".

-Specify the Global Database Name.

-Specify whether to configure to database with Enterprise Manager.

-Set the default password for the SYS and SYSTEM accounts.

-Choose your storage mechanism. We recommend that you choose "File System"
and select a Common Location.

-Specify any desired recovery options.

-Specify NO sample schemas.

-In the Character Sets tab, do NOT modify the Default Language (American)
and Default Date Format (US).

-Specify the desired values for parameters SGA, PGA and processes.

-When the process has completed, exit the Database Creation Assistant. Set up
the listener for the Oracle RDBMS ORACLE_HOME. Create LISTENER.ora and
TNSNAMES.ora files in the Oracle RDBMS $ORACLE_HOME/network/admin directory.

LISTENER.ORA file

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ias.oneapps.com)(PORT = 1521))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oracle)
      (ORACLE_HOME = /u01/oracle/10.2.0)
      (SID_NAME = oneapps)
    )
  )

TNSNAMES.ora file

ORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ias.oneapps.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle)
    )
  )

Set up the listener for the Oracle iAS ORACLE_HOME. Either copy the
tnsnames.ora file or create a symbolic link.

To copy the file:

cp <ORACLE_HOME>/network/admin/tnsnames.ora <iAS _HOME>/network/admin/

Or, to create a symbolic link:

cd <iAS _HOME>/network/admin
ln -s <ORACLE_HOME>/network/admin/tnsnames.ora tnsnames.ora

Create the NEWEOF tablespace where the Maintenance Wizard schema objects
will be stored. Connect to sqlplus as the system user, and execute the following
command substituting in the full path to your datafile.

create tablespace NEWEOF datafile '/u01/oracle/oradata/eof01.dbf' size 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 75M;

Verify that Zip and UnZip executables are installed and that ping is in the
$PATH using the following commands:

which zip
which unzip
which ping

Installation Steps

Login to the system as the same UNIX user that you used to install the
RDBMS and iAS.

Ensure that the $ORACLE_HOME environment variable is set to point at RDBMS
10g before proceeding. At a minimum, make sure the following variables are set.

ORACLE_HOME=<RDBMS ORACLE_HOME>; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
TWO_TASK=<connect_string >;export TWO_TASK
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
IAS_HOME=<IAS ORACLE HOME >; export IAS_HOME

Create a directory to extract the tool into (this will become your $EOF_HOME).
Oracle recommends that you create the Maintenance Wizard directory in the
RDBMS $ORACLE_HOME directory.  Then set the $EOF_HOME environment variable
as follows:

cd $ORACLE_HOME
mkdir eof
EOF_HOME=$ORACLE_HOME/eof; export EOF_HOME

Download the latest Maintenance Wizard Full Install version ( Patch 6398371
as of now ) and unzip it into ${EOF_HOME}.

Change directory to the $EOF_HOME/config directory.

cd ${EOF_HOME}/config

Run the installation script.

./setup.sh

Respond to the prompts shown below. Default values are shown in brackets.

Prompt

Domain name for Maintenance Wizard Node [us.oracle.com]:
SYSTEM password for the Maintenance Wizard instance [manager]:
Schema name for storing Maintenance Wizard. [NEWEOF]:
Maintenance Wizard patch stage [$EOF_HOME/patches]:
Email for Anonymous FTP []
Metalink Username []:
Metalink Password []:

You have the option to review your entries and make changes before the install begins.

Domain =
Oracle Home =
Connect String =
System Password =
Database Schema =
EOF Patch Stage =
Email for Anonymous FTP =
OracleMetaLink Username =
OracleMetaLink Password =

Choose option 2 to review the todo.txt file for information about additional steps
that you must perform, then choose option 3 to exit the installer.

- Stop and restart iAS.

- opmnctl stopall
- opmnctl startall

Post Install Steps

� Secure the $EOF_HOME/EOF.env file.

• chmod 700 $EOF_HOME/EOF.env
• Setup a client machine with Microsoft Internet Explorer (IE) 6.0 or higher.
• In IE, under Tools -> Internet Options, click Settings. Select the radio button
  for "Every visit to the page."
• Click OK twice to exit IE Internet Options.
• Login to the tool using the following URL and the pre-assigned username and
  password: SYSADMIN/SYSADMIN:

http://<hostname>.<domain>:<ApachePort>/EOF/

 

Coming Soon the Pre-Upgrade, Upgrade and Post Upgrade of Release 12.