Wednesday, 13 January 2016

How do I find out the “redo log file max size” in Oracle 11R2

set linesize 300 column

REDOLOG_FILE_NAME format a50

SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP# ASC;



Output : 


GROUP #THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB ---------- ---------- ---------- --- ---------------- --------------------- 1 1 4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log 50 2 1 5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log 50 3 1 6 NO CURRENT /ORACLE/oradata/orcl1/redo03.log 50

Friday, 8 January 2016

HOW TO FIND APPS PASSWORD – R12

 

Simple way to find out apps password in R12, Please review below for the same
1) Connect to SYSTEM or SYS USER
2) Create Function for to decrypt the encrypted password
3) Query for PASSWORD
4) Query for decrypt the password
5) Test the apps user connection
STEP 1:
[oracle@localhost]$sqlplus system/system_password
[oracle@localhost]$sqlplus / as sysdba
STEP 2:
$SQL>create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
/
Function created.
STEP 3:
$SQL>select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;
ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4BB250407DF1DB9EEE786F895B78FB6F260E0101
85B830B8F49C6ECC2FAF
STEP 4:
$SQL>SELECT apps.decrypt_get_pwd(‘GUEST/ORACLE’,’ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4BB250407DF1DB9EEE786F895B78FB6F260E010185B830B8F49C6ECC2FAF
‘) from dual;
APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4B
——————————————————————————–
welcome
STEP 5:
$SQL> conn apps/welcome
Connected.

Thanks
Srini

12.2 Upgrade Driver Fails with ORA-01017: invalid username/password; logon denied


Symptoms

During the initial upgrade to EBS 12.2.0 following Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1) , or while applying the 12.2.x CUP, an upgrade driver worker job fails with the following errors:

ERROR
-----------------------
ORA-01017: invalid username/password; logon denied
occurred while executing the SQL statement:
CONNECT AR/*****

- or -

Creating APPS_NE schema APPS_NE ...
Create user APPS_NE identified by *****
ORA-28003: password verification for the specified password failed;
ORA-20002: Password length less than 15




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Applying one of the 12.2 upgrade drivers
2. database connection failing because of security

Solution

1) if a password was changed without using FNDCPASS or AFPASSWD on the previous EBS release, this is not fixable as adpatch starts by saving all passwords in memory and cache them. Also, when a connection error occurs in the middle of the upgrade, since the system is at that time in-between 2 releases, the AD utilities such as FNDCPASS and AFPASSWD will not work.  The only solution is to restore from the previous EBS release (11i, 12.1.3, etc) and redo the upgrade from the start.
2) if a database password management policy that is more restrictive had been implemented, the DBA can attempt to restore the APPS or other accounts to use the DEFAULT policy which should not be restricted.  Then adctrl can be used to attempt to restart the job.  Please note that if the DBA decidd to cancel the upgrade driver by exiting completely as a failure, the patching cannot be restarted.  The following will be found in the adpatch logs if the 12.2 upgrade driver is restarted from scratch with adpatch:
Restarting from scratch a patch will NOT execute the correct upgrade steps so it will make fail later the rest of the upgrade
Do you wish to continue with your previous AutoPatch session [Yes] ?     <<< do NOT answer "No"  -- you MUST continue with the previous upgrade session in order to potentially get a successful upgrade
------------------------------------------------------------------------
If you have partially applied a patch, you must continue with your           <<< this is a critical warning -- a patch/upgrade cannot be re-applied from scratch.. the workers should be restarted using adctrl so the same session is used
previous AutoPatch session until it completes successfuly.
If you try to re-apply a patch that you have only applied partially,
AutoPatch may apply the patch incorrectly.
------------------------------------------------------------------------
You can try to "fail" the worker job and "restart" it in adctrl after having make sure there are no more policy limitations;
If restarting the worker job still is failing, there will be no other choice but to:
1) recover from before upgrading/patching
2) remove the custom security that was attached to the database default password management profiles
3) restart the upgrade from scratch

All EBS customers who have implemented custom security solutions during the running of their Production system are strongly recommended to remove these customizations before starting an upgrade.  If an error occurs during an upgrade which is related to a security customization, the system must usually be restored from a backup and the upgrade restarted from scratch as the effect of failures are unknown and generally impossible to fix because of the nature of an upgrade and of it's complexity in data dependencies.

Thanks
Srini

EM 12c : Unable Login To Target Database " ORA-01017: invalid username/password; logon denied " Error Returned (Doc ID 1521695.1)


Symptoms

From EM 12c Console, unable to login to target Database when performing any administration task.
When entering SYS credentials (SYS username and password), the following error is returned :
 ORA-01017: invalid username/password; logon denied

Solution

Check the following :
1. The following database parameter should be set either to SHARED or EXCLUSIVE and not NONE :
remote_login_passwordfile=EXCLUSIVE
2. On Windows, Set the following Sqlnet parameter in <ORACLE_HOME>/network/admin/sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
On Unix, ensure that this parameter has the correct value by referring the two documents that are mentionned below in REFERENCES section.
3. Test the Remote Client/Server database connection using Sqlplus installed within the OMS Oracle_Home.
3.1 Using TNS alias defined under <OMS_ORACLE_HOME>/network/admin/tnsnames.ora file
This Remote Client/Server Connection database Test should successfully work.

cd <OMS_Oracle_Home>/bin
sqlplus /nolog

connect sys/<password>@<tns_alias> as sysdba
=> replace <tns_alias> with the alias tns defined under <OMS_ORACLE_HOME>/bin and that points to the target database
3.2 Using the complete NET connection string.
This Remote Client/Server database Connection Test should successfully work.
sqlplus /nolog

connect sys/<password>@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>)))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))' as sysdba
=> Replace <Hostname>, <port>, <service_name>, with the appropriate values that correspond to the impacted target database.
Contact the database's administrator to investigate any error returned when testing it.
Refer as well the two references notes mentionned in the "REFERENCES" section.
3.3 The error returned by either of the two previous tests described in 3.1 and 3.2, will help to troubleshoot and fix the remote client/server connection
between the OMS and the target database.
A classical error that could happen by testing the sqlplus connection is :
ORA-01031: insufficient privileges
That means, that :
either
The password file of the target database would be corrupted or incorrect.
Or
The password file has the different password setting.
Contact the database's administrator to investigate this error, and to re-create the password file only if required/needed.
For this error, and all other errors, Refer the two references notes mentionned below to help to diagnose, and fix the remote client/server database connection error.
REFERENCES :
Two notes can help to troubleshoot the Client/Server Database connections
Document 730067.1 Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA
Document 233223.1 Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues

4. As soon as the remote client/server connection is working fine in either of the two tests described in 3.1 and 3.2, go back in the EM Console to try again to login to the target database from
the database home page for instance, or when performaing any administrative task, or when creating/testing database credentials

Thanks
Srini