Recreate XML file in DB node

1. Bring Down Instance.

2. Startup Database Services.

3.On DB goto $ORACLE_HOME/appsutil/bin

and do:- perl adbldxml.pl tier=db appsuser= appspasswd=

4. Run AutoConfig to populate the context into the database

5. As needed, execute the following in SQL*Plus to determine if the context is populated in the
database:

select name, path, CTX_TYPE from FND_OAM_CONTEXT_FILES
where status != 'H'
and name not in ('METADATA', 'TEMPLATE')
and CTX_TYPE = 'D' ;

6. Startup Application Services .


and if you found that appsutil directry is not there in $ORACLE_HOME

then you need to add following steps to create that


Goto $AD_TOP/bin and run

perl admkappsutil.pl

it will create appsutil.zip in $APPL_TOP/admin/out

copy that to $ORACLE_HOME (DB Tier) and

unzip -o appsutil.zip

and then proceed from step 3

Re-org of Database Objects

We will discuss the following segment_types

1) Table
- Normal
- IOT Tables starting with SYS_IOT_OVER
- Long Datatye column

2) Table Partition
3) Table Subpartition
4) Index
- Normal
- IOT Indexes starting with SYS_IOT_TOP
5) Index Partition
6) Index Subpartition
7) Lobsegment/LobIndex


1) Table

- Normal

Alter table move tablespace

- IOT Tables starting with SYS_IOT_OVER

If we move the above table with normal command, we get the following error

ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

To move the above table , use following method

a) Find the Iot_name

select OWNER,TABLE_NAME,TABLESPACE_NAME,IOT_NAME,IOT_TYPE
from dba_tables
where TABLE_NAME like ‘SYS_IOT_OVER_375778’;
OWNER TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
APPLSYS SYS_IOT_OVER_375778 APPS_TS_QUEUES AQ$_WF_CONTROL_G IOT_OVERFLOW


b) Find the queue table

Queue_table – remove the characters before the first underscore and last underscore of IOT_NAME
And then search for the queue table

So in our example IOT_NAME is AQ$_WF_CONTROL_G
So we are searching queue_table like ‘%WF_CONTROL%’

SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_CONTROL%';

QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_CONTROL APPLSYS


c) Install move_aqt package using Note 394713.1
d) exec move_aqt.move('APPLSYS',WF_CONTROL,'DUMMY');
e) Once this is done table SYS_IOT_OVER_375778 will be moved to DUMMY tablespace.




- Long Datatye column

While moving the tables with column of long datatype, we get the following error

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

These tables cannot be moved using normal move command.
We need to export, drop and import the tables.

2) Table Partiton

ALTER TABLE
MOVE PARTITION
TABLESPACE ;

Dba_tab_partitions -- to get the partition name

3) Table Subpartition

ALTER TABLE
MOVE SUBPARTITION
TABLESPACE ;

Dba_tab_subpartitions – to get the subpartition name

4) Index

- Normal

Alter index rebuild tablespace ;

- IOT Indexes starting with SYS_IOT_TOP

If we move the IOT indexes with normal commnad, we get the following error

ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

To move the IOT indexes , use the following method

a) Find the table name on which index is created

SQL> select table_name,table_owner from dba_indexes where index_name like 'SYS_IOT_TOP_375805';

TABLE_NAME TABLE_OWNER
------------------------------ ------------------------------
AQ$_WF_WS_SAMPLE_G APPLSYS


b) If the table name is starting with AQ$

1)Find the queue table, in the same way as was done above for tables.


SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_WS_SAMPLE%';

QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_WS_SAMPLE APPLSYS

We have removed the characters before first underscore and after last underscore
When searching for queue table.

2) exec move_aqt.move('APPLSYS',’ WF_WS_SAMPLE’ ,'DUMMY');

Package move_aqt needs to be installed as mentioned above.
3) Index SYS_IOT_TOP_375805 will be moved to dummy tablespace.

If the table name is not starting with AQ$

1) Move the table_name , from step (a), this will move the index.


5) Index Partition

Alter index rebuild partition
tablespace ;

dba_ind_partitions -- to get the partition name

6) Index Subpartition

Alter index rebuild subpartition
tablespace ;

Dba_ind_subpartitions – to get the subpartition name

7) Lobsegment/LobIndex

alter table move lob ()
store as (tablespace );

To find the table_name and lob_column

select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME like ‘lobsegment_name’

SYS_LOB0000195332C00032$$ LOBSEGMENT

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------
SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
IEO IEO_ICSM_QUEUE_TBL_1
USER_PROP
SYS_LOB0000195332C00032$$ SYS_IL0000195332C00032$$ APPS_TS_QUEUES


So from above output , following will be the command to move lobsegment


alter table IEO.IEO_ICSM_QUEUE_TBL_1 move lob (USER_PROP) store as (tablespace APPS_TS_QUEUES_NEW);

Note that , once the LOBSEGMENT is moved ,corresponding LOBINDEX will
Also move. You don’t need to run the command seperately for LOBINDEX.

Here is the dynamic query which will give the command to move LOBSEGMENT/INDEX

select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace APPS_TS_QUEUES_NEW);'
from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name like ‘’ and segment_type like '%LOB%');




Solution for following issue implemented in SR 7067291.992

Follow this only for lobsegment on AQ$_WF_CONTROL_D.

SQL> select segment_name, segment_Type,owner from dba_segments WHERE tablespace_name
='APPS_TS_QUEUES';

SEGMENT_NAME
SEGMENT_TYPE OWNER
---------------------------------------------------------------------------------
------------------ ------------------------------
SYS_IL0000583185C00006$$
LOBINDEX APPLSYS
SYS_LOB0000583185C00006$$
LOBSEGMENT APPLSYS


SQL> alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
2 lobsegment (tablespace APPS_TS_QUEUES_NEW);
alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online


Found Note 418238.1

1> First stop WF related services like WF Mailer Agent, WF listener, etc.

2> Then run following SQL :
sqlplus / @$FND_TOP/patch/115/sql/wfctqrec.sql

R12 New Techstack and Directory Structure Changes!!!!

The directory structure changes that happened between the old and new release.

JAVA_TOP changes
11i:

• $JAVA_TOP = $COMMON_TOP/java
  • Class
  • Archives

R12:

• $JAVA_TOP = $COMMON_TOP/java/classes
  • Classes
• $AF_JLIB = $COMMON_TOP/java/lib
  • Archives

In case of 11i release the JAVA_TOP was pointing to COMMON_TOP / java directory, which consist of class and archive directories. The class directory consist of all the .class files where as archive directory consist of all jar files.

In R12 this has been changed and new JAVA_TOP directly points to COMMON_TOP / java / classes directory, which contains all .class files. For jar files we have another directory called lib. This contains JAR files. For pointing to this directory, there is another environment variable that is defined called AF_JLIB.

Techstack Level Changes

For Oracle Apps 11i we have following components version.

In R12 the components and versions are as given below.

Component Level Comparison

In case of new techstack some of the components got changed. Following is the brief comparison.

In case of 11i, Jserv engine was part of HTTP server and was used for JSP and Java requests. This has been replaced by OACORE (for handling JSP). Instead of Application server 9i used in 11i, in R12 we have Application server 10g.

INST_TOP Structure

Instance top contains all the config files, log files, ssl certificates, document root etc. This is a new directory introduced in R12TS. Addition of this directory makes the middle-tier more organized, since data is kept separate from config/log files. Another advantage is that, multiple instances can easily share the same middle tier / DB. To create a new instance that shares an existing middle-tier, just create a new instance_top with proper config files and nfs mount the middle tier in the server.

In 11i release all the config/log files was in COMMON_TOP. This has been shifted to INST_TOP. This is just to organize the application middle tire and to make a distinction between the core files and config/log files. Before checking the contents of INST_TOP, lets have a brief comparison of what was the content of COMMON_TOP in 11i release and R12. COMMON_TOP contains following directories.

So if we see the above comparison for the COMMON_TOP for 11i and R12, we see that there is only one directory, which is missing. This directory is admin directory. This directory is been shifted to INST_TOP. Also the log directory, which is still shown in COMMON_TOP, is no more used. There is another log directory in INST_TOP. This represents the logs for all the components of middle tier. Lets now see the structure of INST_TOP.

Here we see that we have some of the directories, which are same as directories in COMMON_TOP. So lets clear the confusion here as to which directory in which TOP is used for what purpose.

Actually the LOG, PORTAL and RGF directory in COMMON_TOP is not used. Instead these directories in INST_TOP are actually used. They are just the copies stored in COMMON_TOP. Also the admin directory is clearly moved to INST_TOP.

LOG directory contains the log files for all the components of mid tier like apache, oacore, opmn, and concurrent manager and also the logs for the AD-scripts used to manager various services.

CERTS directory is used for SSL certificates. In case of secured environment where secure certificates are used, the certificates should be downloaded in this directory.

PID directory contains the PID (process ID) for middle tier processes like apache.

APPL directory contains the 2 directories admin and fnd. ADMIN directory contains ojspCompile.conf and ojspCompile.properties. These files are important for JSP compilation and settings. FND directory contains the DBC file, which is required by the middle tier to connect to database. Actually the DBC file which used to be in $FND_TOP/secure directory location in the 11i release has been shifted to this location. This location is directly referred by an environment variable FND_SECURE. This was pointing to $FND_TOP/secure directory in 11i release. In R12 its pointing to $INST_TOP/appl/fnd/secure directory.

ORA directory indicated the techstack components. But an ORA directory in INST_TOP contains the configuration information of the techstack components. Lets look into the details of this directory.

ADMIN directory contains all the AD-scripts used to manage the middle tier components. ADMIN directory contains the SCRIPT directory, which has all the script. Actually these are the wrapper scripts over the main binary. We will see the details of these scripts when we explain each component. Following are the different AD-scripts and there function.

New Environment Variables in R12

Changes to existing environment Variables in R12

Domain , Administration & Managed Server, Cluster in Oracle WebLogic

What is domain in WebLogic ?

WebLogic Domain

Domain is logical grouping of resources and services and consist of Administration Server, Managed Server and cluster. There can only be one administration Server in domain and zero to N Managed Server.

.

.
What is Administration Server ?

Administration Server is WebLogic Server instance that maintains configuration data for a domain. You can deploy your application on administration Server but it is recommended to create managed Server and deploy your application in managed server and leave Administration domain for configuration and maintenance.

—There will always be atleast one Administration Server in a domain.

.

.
What is Managed Server ?

Any WebLogic Server instance apart from Administration Server is called as Managed Servers. This is weblogic server where you deploy your application (Though you can deploy your application in Administration server as well but it is not recommended in production/UAT instance)

.

.

What is Cluster in WebLogic ?

Group of WebLogic Managed Server Instances that work together to provide high availability and scalability for applications is called cluster. WebLogic Servers with in cluster can run on same machine or different machines. These are also called as managed Server cluster.

.

.

Things you should know before creating domain in WebLogic Server ?

– You use configuration wizard to create or extend domain but this can be used only in offline mode (when weblogic server is not running)
– You can also use WLST (WebLogic Scripting Tool), command line tool to create and extend domains in WebLogic Server.
– You can also use unpack command to create new domain. This command can’t be used to extend domain.
– Extending a domain means, you already created domain and now wish to extend it (use more application, add managed server, create cluster…)
– Configuration wizard can be run in Graphical Mode (interactive GUI) or Console Mode (interactive text based)
– For silent mode use WLST (WebLogic Scripting Tool)
– While creating domain you specify Startup Mode (Development or Production). In development mode, you get autodeploy option and security is low. In production mode you need username/password to deploy applications.
– When you create domain, it creates following directories
i) autodeploy ii) bin iii) config (config.xml for domain sits here) iv) console-ext v) lib vi) security vii) servers

–You use config.cmd (windows) or config.sh (unix) from $BEA_HOME/ wlserver_/ common/ bin to start configuration manager to create domain

Below Flowchart (image from Oracle Documentation) displays steps to create domain.
Create WebLogic Domain

SSO : How to deal with “Your Account is Locked”

You have applications (E-Business Suite 11i/R12, Portal, Discoverer, BI Publisher) integrated with Oracle Single sign-On (SSO) and users complain that they receive message ”Your Account is Locked“. Here are few things which can help you in understanding this

-

First thing to do in case “User account is locked”
You should know how to unlock SSO account. These are various ways using which you can unlock account
1. Using OIDDAS : OIADDAS (Delegated Administrative Services) Login as superuser orcladmin (or Account with access to unlock/Change Password) >> select Directory Tab >> enter User Name >> Select Unlock Account

2. Using ODM : Oracle Directory Manager is java based tool to manage users . More can be found here . Go to User’s entry and on right pane reset user password. (Resetting password will unlock account)

3. Using ldapmodify : add attribute “orclpwdaccountunlock” to user account and set its value to 1 usign ldapmodify.

4. Using Portal: If you are using Oracle Portal with SSO, then reset password using Portal User Account portlet (Resetting password will unlock account)

-

Where are account locking rules defined
Next thing to identify is that where account lock rules defined
– Account lock rules are defined in Password Policy in OID (Did you know that you can use different password policy at leaf level in OID from 10.1.4.1 OID)
– To check your password policy, login to OIDADMINusing orcladmin and go to
Password Policy Management -> Password Policy for Realm .... >> click on Account Lockout tab on right pan

- User Account can be locked only if “Global Lockout” is enabled and user try wrong password for number of times defined in “Password Maximum Failure” within “Password Failure Count Internal”
To understand it, lets assume
A) If ”Password Maximum Failure” set to 10 and “Password Failure Count Internal” set to 0, then 10 consecutive wrong password will lock user account.

B) If “Password Maximum Failure” set to 10 and “Password Failure Count Internal” set to 20 then 10 consecutive wrong password within 20 seconds will lock user account.

- Another reason for account lock is “Password Expiry Time” defined in Password Policy. User Account can lock after password expiry time.

Note *

Account will remain locked for time specified by “Global Lockout Duration” (default is 86400 Sec i.e. 1 day) unless unlocked by Admin.

-

How to prevent Administrative Accounts getting locked

You can create another password policy (possible only from OID 10.1.4 and higher) with “No Global Lockout” and attach new password policy to administrative accounts.

-
Finally if your superuser “orcladmin” itself is locked
A) If super user account like “orcladmin” is locked you can unlock it like
oidpasswd [connect=] unlock_su_acct=true

P.S. There are two orcladmin account one cn=orcladmin and other cn=orcladmin,cn=users, dc= (oidpasswd is required to reset password for cn=orcladmin)
B) If ods schema password (this schema holds OID data) is locked then you can unlock this using
SQL> alter user ods account unlock;

C) If for some reason you forgot ods(default password is same as orcladmin or ias_admin) password then you can reset it using metalink note # 472752.1 How to Unlock/Reset Super User cn=orcladmin When the ODS’s Password Has Been Forgotten ?

-

How to track Failed Login Attempts

If you are are getting too many accounts locked and you would like to know if these are from users or some one else (hacker)
1. You can track failed login attempts from ias console

Login to iAS Console of SSO server like http://hostname:/emd (ias_admin/)
Click on “Single Sign-On:orasso”

Check failed logins under “Login Failures During The Last 24 Hours”
2. Use below query to list failed Login attempts in last 7 days including client IP address on your Single Sign-On Server

select USER_NAME||’ - ‘||MESSAGE||’ - ‘||to_char(LOG_DATE,’dd.mm.yyyy hh24:mi:ss’)||’ - ‘||IP_ADDRESS “Login Failures”
from ORASSO.WWSSO_AUDIT_LOG_TABLE$
where log_date > (sysdate - 7)
and MESSAGE = ‘Login failed’
order by log_date;

-

Related

Password Policy in OID/SSO
275104.1 - How To Unlock User Accounts in Portal
251354.1 -OID Superuser Password Locked: How to Unlock the orcladmin Password
204900.1 -What oidpasswd Utility Does and the Objects it Modifies (ODS and ODSCOMMON)
472752.1 -How to Unlock/Reset Super User cn=orcladmin When the ODS’s Password Has Been Forgotten ?

Good Metalink Notes or Documentation on Apps 11i/R12/12i Patching

Here are few good metalink notes to understand Oracle Apps Patching (adpatch), You need Metalink Account to access these notes.

181665.1 - Release 11i Adpatch Basics

457566.1 - Oracle Applications Patching FAQ for Release 11i

459156.1 - Oracle Applications Patching FAQ for Release 12

60766.1 - 11.0.x : Patch Installation Frequently Asked Questions

225165.1 - Patching Best Practices and Reducing Downtime

314540.1 - What Log Files are Generated with Adpatch, and Where are They Located?

175485.1 - How to Apply an 11i Patch When adpatch is Already Running

136342.1 - How To Apply a Patch in a Multi-Server Environment

374915.1 - HRMS PATCH INSTALL FAQ

342035.1 - How to Show the Patch History for Merged Patches in OAM

228779.1 - How to Merge Patches Using admrgpch

432597.1 - Reducing Down Time When Applying Patch for Oracle E-Business Applications 11i

231701.1 - How to Find Patching History (10.7, 11.0, 11i)

235447.1 - Application Install AutoPatch Setup Test

R12 Instance Home Overview

Oracle Applications/E-Business Suite Release 12 introduces a new concept of a top-level directory for an Applications Instance which is known as Instance Home and is denoted the environment variable $INST_TOP.
Instance Home contains all the config files, log files, SSL certificates etc.

Advantages of new INSTANCE HOME

The additional Instance Home makes the middle tier more easy to manage and organised since the data is kept separate from the config files. The Instance Home also has the ability to share the Applications and Technology stack code across multiple instances.

Another advantage of the Instance Home is that the Autoconfig no longer writes anything to the APPL_TOP and ORACLE_HOME directories, everything is now written in the INST_TOP as a result APPL_TOP and ORACLE_HOME can also be made read only file system if required . Earlier say the adpatch used to write the log file in APPL_TOP/admin directory but with the new model the APPL_CONFIG_HOME/admin is used.

To create a new instance that shares an existing middle-tier, just create a new instance_top with proper config files and NFS Mount the middle tier in the server.The basic structure of the Instance Home is:$APPS_BASE/inst/apps/$CONTEXT_NAME or also defined by environment variable $INST_TOP, where APPS_BASE (which does not have or need a corresponding environment variable) is the top level of the Applications installation and $CONTEXT_NAME is the highest level at which the Applications context exists (format $SID_$hostname).

Instance Home Directory Structure

$INST_TOP/admin/scripts ($ADMIN_SCRIPTS_HOME): All AD scripts are located here

$INST_TOP/appl ($APPL_CONFIG_HOME) : For standalone envs, this is set to $APPL_TOP
$INST_TOP/appl/fnd/12.0.0/secure FND_SECURE: The dbc file is located here
$INST_TOP/appl/admin All the env config files are located here

$INST_TOP/certs SSL Certificates go here

$INST_TOP/logs LOG_HOME: Central log file location. All log files are placed here (except adconfig)
$INST_TOP/logs/ora ($ORA_CONFIG_HOME)
$INST_TOP/logs/ora/10.1.2 ‘C’ Oracle home config, Contains tnsnames and forms listener servlet config files
$INST_TOP/logs/ora/10.1.3 Apache & OC4J config home, Apache, OC4J and opmn. This is the ‘Java’ oracle home configuration for OPMN, Apache and OC4J

$INST_TOP/pids Apache/Forms server PID files are located here

$INST_TOP/portal Apache’s DocumentRoot folder