How to change sys password in Oracle 10g

How to change sys password in Oracle 10g

To Change the ""SYS"  Password

SQL> conn sys as sysdba
Enter password: (null) no need to give anything
Connected.
 
SQL> passw system
Changing password for system
New password: (new password)
Retype new password: (new password)
Password changed
 
SQL> conn system/(new password)
Connected.
 
SQL> passw sys
Changing password for sys
New password: (new sys password )
Retype new password: (new sys password )
Password changed
 
SQL>

Package using which session

Package using which session

Trying to compile package but its not compiling and going in infinite loop.

Run this query to now which session is using package. It will give you SIDs

SELECT * FROM v$access WHERE object = 'XXXX_HR_COMMON_PKG';

Now run this command to get SERIAL# of that SID

select SERIAL# from v$session where sid='<SID>';

Now kill sessions

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Its easy :)

Huge generation of archive logs

Huge generation of archive logs

Doc ID 167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
Doc ID 1507157.1 - Master Note: Troubleshooting Redo Logs and Archiving
Doc ID 781999.1 - General Guideline For Sizing The Online Redo Log Files
Doc ID 782935.1 - Troubleshooting High Redo Generation Issues
Doc ID 199298.1 - Diagnosing Excessive Redo Generation - Limit with Nologging

Components version in R12

Components version in R12

Apache Version
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
Report Version
string -a APXAPRVL.rdf|grep Header

Perl Version
$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode
cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12
Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
 
Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

Oracle Jinitiator in 11i/R12/12i 
Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )

Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check

adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
 
Identity Management component Version/Release Number

Oracle Single Sign On
select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)

To find software/binary version
$ORACLE_HOME/bin/oidldapd -version

To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion
select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
 
Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0 

For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
select * from ias_versions;
or
select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version

Oracle Portal
select version from portal.wwc_version$;

Database Component
 
To find database version
select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

Adding responsibility to SYSADMIN user resulted ORA-20001: FND_CANT_INSERT_USER_ROLE

Adding responsibility to SYSADMIN user resulted the below error.
Issue : Adding responsibility to SYSADMIN user resulted the below error in R12

Oracle error - 20001: ORA-20001: FND_CANT_INSERT_USER_ROLE
(USERNAME=SYSADMIN)
(ROLENAME=FND_RESP|FND|FND_FUNC_ADMINI|STANDARD)
(ROUTINE=FND_USER_RESP_GROUPS_API.Insert_Assignment) has
been detected in FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT

Resolution:

Issue exists in default R12 installation

Refer Oracle Note id : 454988.1

1. Run the concurrent program "Workflow Directory Services User/Role Validation" with parameters : 100000, Yes, Yes, No

2. Bounce all services under oacore or bounce opmnctl