fnd_nodes Clean up: AFCPClean.sh

Hi Guys,

Now no need to run exec fnd_conc_clone.setup_clean procedure. Oracle has introduced a new feature AFCPClean.sh script in R12 which do the same.

fnd_nodes Clean up: AFCPClean.sh :

Stop All Services

–

Execute $INST_TOP/admin/install/afcpclean.sh APPS APPS-Password

–

Perform Auto Config.


Example:

–

$ADMIN_SCRIPTS_HOME/adstpall.sh APPS/APPS-Password

–

Check the FND Nodes

SELECT node_name "Node Name", node_mode "Mode", support_cp "C",
support_web "W", support_admin "A", support_forms "F"
FROM FND_NODES;

–

$INST_TOP/admin/install/afcpclean.sh APPS APPS-Password

++++++++++++++++++++++++++++++++++++++++++++++++++++++

AFCPClean Performs the Following Tasks

======================================

#delete from fnd_profile_option_values

where (application_id,profile_option_id) in

(select application_id,profile_option_id

from fnd_profile_options

where profile_option_name='NODE_TRUST_LEVEL')

and level_id=10005;



#FND_CONC_CLONE.setup_clean();

Cleaning up the Database..

PL/SQL procedure successfully completed.

Commit complete.

–

Check the FND Nodes

–

DBTier AutoConfig: $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

–

Apps Tier Auto Confiog: $ADMIN_SCRIPTS_HOME/adautocfg.sh

Hello Guys,

I have implemented OBIEE with Informatica,DAC and BIAPPS and integrated with R2.

I will publish installation steps soon. Please mail me if you need any help on this.

AFPASSWD 12.1.2 utility

Hi Folks,
I found one new feature of R12 AFPASSWD. This utility introduced in 12.1.2.

AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:

• AFPASSWD only prompts for passwords required for the current operation,
allowing separation of duties between applications administrators and database
administrators. This also improves interoperability with Oracle Database Vault. In
contrast, the FNDCPASS utility currently requires specification of the APPS and the
SYSTEM usernames and corresponding passwords, preventing separation of duties
between applications administrators and database administrators.

• When changing a password with AFPASSWD, the user is prompted to enter the
new password twice to confirm.

• AFPASSWD can be run from the database tier as well as the application tier. In
contrast, FNDCPASS can only be run from the application tier.---  (MOST IMPORTANT FEATURE)

FNDCPASS will continue to be shipped with Oracle E-Business Suite, and customers
can migrate to the AFPASSWD utility at their discretio


AFPASSWD Usage

The AFPASSWD command is used with the relevant command line options to perform
the desired action.

AFPASSWD [-c [@]] [-f ]

AFPASSWD [-c [@]] [-o ]

AFPASSWD [-c [@]] [-a]

AFPASSWD [-c [@]] [-l [] |
[]]

AFPASSWD [-c [@]] [-L [] | []]

AFPASSWD [-c [@]] [-s]

These options have the following functions:

• -c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the
Applications user, and/or the value of TWO_TASK. This option can be use in
combination with others. If it is not specified, default values from the environment
will be used.
Note: The password will be prompted for, and is not to be
provided in the connection string.

• -f {FNDUSER} - Changes the password for an Applications user. A username that
contains spaces must be enclosed in double quotation marks; for example, "JOHN
SMITH".

• -o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.
Note:  This only applies to users listed in the
FND_ORACLE_USERID table, not database users in general.

• -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS,
APPLSYS, APPLSYSPUB) to the same password, in the same way as the
ALLORACLE mode does in FNDCPASS.

• -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} =
LOCK, {FALSE} = UNLOCK.

• -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} =
LOCK, {FALSE} = UNLOCK.

• -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user.
This requires the execution of AutoConfig on all tiers.

• -h - Displays help.

Recompile all invalid objects ADCOMPSC.pls

How can I recompile all my invalid objects using ADCOMPSC.pls?
=================================================================

Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls

Arguments for ADCOMPSC.pls:
1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3

NOTE:      The order in which to compile Invalid Objects in schemas is
    SYS, SYSTEM, APPS and then all others.  APPS_DDL and APPS_ARRAY_DDL
        should exist in all schema's.  If you encounter an ORA-1555 error
        while running adcompsc.pls, just restart the script.

The script can be run as follows:

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example:  SQL> @adcompsc.pls apps apps %


What if I still have invalid objects than can not be resolved by ADCOMPSC.pls?
==============================================================================

If there are any objects still left INVALID, you can verify them by using
aderrchk.sql to record the remaining INVALID objects.  Aderrchk.sql use the
same syntax as adcompsc.pls.  This script is also supplied with the
Applications. You can send the aderrchk.sql to a file using the
spool command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects will not compile, try the following:

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will give you the sql that creates the packages.  You can then
recreate the packages.  SQL>@packageheader     SQL>@packagebody
If recreating the package does not make the package valid you will have to
analyze the user_errors table to try to determine the cause of the invalid
package.

select text
from user_errors
where name = 'PACKAGENAME';

11.5.10.2 to 12.1.3 upgrade on IBM-AIX

Sorry folks for late reply. In busy schedule i am not able to get the time for update.

Upgrade steps---

Set Application R12.1.1 Enviornment file
Drop MRC schema (conditional)
Ensure that Maintenance Mode is enabled
R12 pre upgarde patch  9179588 (9179588:R12.AD.B), 7461070.R12 and 9477107.B
R12 Pre upgrade patches --Related to Functional---check on metalink


Configure and start Server Processes
Run Rapidwiz  for configure the Application services



Update database tier nodes with the Oracle E-Business Suite Release 12.1.1 code level.
Generate the appsutil file in the apps node and copy it to the DB node
unzip the appsutil file and run the autoconfig on the DB node
Run autoconfig on Database Tier
Run autoconfig on Application Tier



R12.1.1 Post Upgrade Task
Re-enable custom triggers and indexes (conditional)


OATM Tablespace migration
Execute the OATM Tool


Upgrade to 12.1.3 (Metalink Note---1080973.1)
Patch 9239089 R12.AD.B.Delta.3
Patch 9239090 12.1.3
Patch 9239095 12.1.3 online Help
Patch 9817770 R12.ATG_PF.B  (POST UPGRADE PATCH)
Patch 9966055 R12.FND.B  (POST UPGRADE PATCH)


Update database tier nodes with the Oracle E-Business Suite Release 12.1.3 code level.
Generate the appsutil file in the apps node and copy it to the DB node
unzip the appsutil file and run the autoconfig on the DB node
Run autoconfig on Database Tier
Run autoconfig on Application Tier


Run HRGLOBAL.DRV  (Metalink Note---145837.1)


Post Upgrade Task
Regenerate forms,reports,messages and product jar files
Compile invalid objects if any is there in APPS schema
Compile JSP
Disable Maintenance Mode and Start the Application Services

If anyone need any kind of help then do not hesitate to send me an email. (devesh288@gmail.com)

Sorry Folks. I was not able to update the blog. I was busy in an upgradation from 11.5.10.2 to 12.1.3 on IBM-AIX.

As I have successfully done the Go-Live. I will update the blog with upgradae details.

Some Useful Unix commands and sql queries

Killing stubborn Unix Processes



===============================


cat /dev/null > /tmp/ kill -9


+++++++++++++++++++++++++++



Get the Total number of files


=============================


select count(*) from dba_data_files union


select count(*) from dba_temp_files union


select count(*) from v$logfile union


select count(*) from v$controlfile;


+++++++++++++++++++++++++



Get Mountpoint hosting the DBFs


-------------------------------


select distinct substr(name,1,instr(NAME,'/',-1,1)-9) fsname from v$datafile;
 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

To delete files based on date and file extensions

----------------------------------------------

ls -l *.dat grep "Jul" tr -s " " cut -d " " -f9 > zzjul.sh


ps -ef|grep "" sort grep -v grep awk '{print $2'}


Generate Compression script for cold backup on source database


====================================================

sqlplus /nolog

conn /as sysdba

set lines 132

set head off

set pages 0

select '/usr/bin/compress < ' FILE_NAME 
' > (/DEST_BACKUP_DIR/)' 
substr(FILE_NAME,30)
'.Z'
from dba_data_files
order by substr(FILE_NAME,30)
spool ColdBackup..ksh
/
spool off
 
+++++++++++++++++++++++++++++++++++++++++++++
 
Generate Uncompression script for cold backup on source database


======================================================

select '/usr/bin/uncompress < /mnt2/oradata/backup/gpses117'
substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
'.Z > '
FILE_NAME
from dba_data_files
order by substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
spool RestoreBackup_gpser117.ksh
/
spool off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

All Scheduled Jobs


==================

set wrap on
set lines 140
set veri off
col PROGRAM for a20
col UnitCode for a8
col TypeCode for a8

select b.program, a.request_id, resubmit_interval "Interval", resubmit_interval_unit_code "UnitCode"

,resubmit_interval_type_code "TypeCode", resubmit_time "ResubTime", resubmit_end_date "EndDate"
,resubmitted, a.request_type, request_limit ,increment_dates ,resub_count,
exp_date, root_request_id "RootReqID", origin
from fnd_concurrent_requests a ,fnd_conc_req_summary_v b
where root_request_id is not null
and a.request_id = b.request_id;
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Get the SID for a given SPID


========================

col program for a30
col username for a10

col spid for a9

select A.PID, A.SPID, A.USERNAME, A.SERIAL#,
B.SID, B.COMMAND, B.PROGRAM
from v$process A, v$session B

where A.spid = &spid

and B.PADDR = A.ADDR

/
 
+++++++++++++++++++++++++++++++++++++++++
 
List of Users with SYSADMIN Responsibility


===============================

set pages 45
set lines 132
col RESPONSIBILITY_NAME for a30
col SECURITY_GROUP_NAME for a30
col APPLICATION_NAME for a30
col USER_NAME for a15

select user_name, application_name, responsibility_name, u.END_DATE, ur.END_DATE
from apps.fnd_user u, apps.fnd_user_resp_groups ur,
apps.fnd_responsibility_vl r, apps.fnd_application_vl a,
apps.fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and r.RESPONSIBILITY_NAME = 'System Administrator'
and ur.security_group_id = s.security_group_id
order by 1
/