NARESH NADHAN 'S
Oracle E-Business Suite |
|
Oracle E-Business Suite supports today’s evolving business models, drives productivity, and meets the demands of the modern mobile user.
|
NARESH NADHAN 'S
Oracle E-Business Suite |
|
Oracle E-Business Suite supports today’s evolving business models, drives productivity, and meets the demands of the modern mobile user.
|
What is Password File ?
Password files are necessary because Oracle needs a way to authenticate you when you are connecting over a network to a database that has not been opened. If you are connecting to an instance over a network, then you have not logged into the database server, and Oracle has no way to verify that you are who you say you are. It can check your password against those stored in the database, but that only works if the database is open. DBAs often need to connect to an instance when a database is not open. A good example is when you need to connect in order to start an instance. To get around this problem, DBA passwords are stored in the password file. Password files are not database files. A password file is a small, simple file that just contains the encrypted passwords of the DBAs. Because it is not a database file, Oracle can read it when the database is closed. This gives Oracle a way to authenticate you, and this is what enables you to use tools such as Oracle Enterprise Manager in a secure manner. In other words, The password file stores a list of usernames and passwords that are allowed to remotely authenticate as SYSDBA over the network. Oracle must use this file to authenticate them, not the normal list of passwords stored in the database. And for this authentication to use password file,the value of REMOTE_LOGIN_PASSWORDFILE should be EXCLUSIVE or SHARED. orapwd tool is used to create and manage password files. DEFAULT LOCATION FOR PWD FILE – $ORACLE_HOME/dbs Create a passwordfile for standalone database . cd $ORACLE_HOME/dbs orapwd file=orapwORCL password=oracle force=y Argument Description FILEName: To assign to the password file. See your operating system documentation for name requirements. You must supply a complete path. If you supply only a file name, the file is written to the current directory. ENTRIES(Optional): Maximum number of entries (user accounts) to permit in the file. FORCE(Optional): If y, permits overwriting an existing password file. IGNORECASE(Optional): If y, passwords are treated as case-insensitive. NOSYSDBA(Optional): For Data Vault installations. See the Data Vault installation guide for your platform for more information. Create a password file in ASM diskgroup: orapwd file='+DATA/orapwORCL' ENTRIES=10 DBUNIQUENAME='ORCL' Create password file from asmcmd tool:(Oracle 12c onwards) ASMCMD> pwcreate --dbuniquename ORCL +DATA/PWDFILE/pwdORCL oracle We can view users authenticated through password file in v$pwfile_users table SQL> select username,sysdba from v$pwfile_users; USERNAME SYSDB --------- ----- SYS TRUE When we issue any DML statements i.e. insert,update,delete the changed blocks are stored in buffer cache and Server Processes keeps the before values for those changed blocks in UNDO segments.
What is the use of UNDO ? Rollback transactions: Whenever we issue a ROLLBACK command it uses the undo segment to rollback to previous value . Recover the database: During database recovery, undo records are used to undo any uncommitted changes applied from the redolog to the datafiles. Provide read consistency: Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it. Analyze data as of an earlier point in time by using Flashback Query. Recover from logical corruptions using Flashback features. Generally Undo is stored in Undo tablespace in the database. Another Question here … How long a undo data stored in the database? and Is it Permanent ? Undo data is not permanent and Oracle provides flexibility of how long should undo data be stored with the help of undo_retentions parameter. We can set undo_management parameter to automatic for oracle to manage undo retention(default), or even set this value manually and it’s value should be greater than the time taken by the longest running query in your database. IN 11g Onward Oracle tuned this parameter automatically according to the size of the undo tablespace Let's have a look about undo parameters .. UNDO_RETENTION: Committed undo information normally is lost when its undo space is overwritten by a newer transaction. However, for consistent read purposes, long-running queries sometimes require old undo information for undoing changes and producing older images of data blocks. The success of several Flashback features can also depend upon older undo information. The default value for the UNDO_RETENTION parameter is 900. Retention is specified in units of seconds. This value specifies the amount of time, undo is kept in the tablespace. The system retains undo for at least the time specified in this parameter. You can set the UNDO_RETENTION in the parameter file: UNDO_RETENTION = 1800 You can change the UNDO_RETENTION value at any time using: SQL> ALTER SYSTEM SET UNDO_RETENTION = 2400; The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space (if retention is not guaranteed). This action can potentially cause some queries to fail with the ORA-01555 "snapshot too old" error message. UNDO_RETENTION applies to both committed and uncommitted transactions since the introduction of flashback query feature in Oracle needs this information to create a read consistent copy of the data in the past. Retention Guarantee: Oracle Database 10g lets you guarantee undo retention. When you enable this option, the database never overwrites unexpired undo data i.e. undo data whose age is less than the undo retention period. This option is disabled by default, which means that the database can overwrite the unexpired undo data in order to avoid failure of DML operations if there is not enough free space left in the undo tablespace. You enable the guarantee option by specifying the RETENTION GUARANTEE clause for the undo tablespace when it is created by either the CREATE DATABASE or CREATE UNDO TABLESPACE statement or you can later specify this clause in an ALTER TABLESPACE statement. You do not guarantee that unexpired undo is preserved if you specify the RETENTION NOGUARANTEE clause. In order to guarantee the success of queries even at the price of compromising the success of DML operations, you can enable retention guarantee. This option must be used with caution, because it can cause DML operations to fail if the undo tablespace is not big enough. However, with proper settings, long-running queries can complete without risk of receiving the ORA-01555 "snapshot too old" error message, and you can guarantee a time window in which the execution of Flashback features will succeed. From 10g, you can use the DBA_TABLESPACES view to determine the RETENTION setting for the undo tablespace. A column named RETENTION will contain a value on GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace). A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data. Enabling and disabling undo retention guarantee SQL> ALTER TABLESPACE undotbs RETENTION GUARANTEE; SQL> ALTER TABLESPACE undotbs RETENTION NOGUARANTEE What is ORA-01555 Error ? Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. As we discussed earlier ,Undo also provides us the read consistency to a user as he can only see the previous values until a transaction is committed. To explain about ORA-01555 I would give one example, User A started the select cursor on the table at time 10:00 PM and it takes another 1 hour to fetch the data. And User B started updating the same table at the same time and it takes 2 hours to complete the update. Now user A should get the read consistent version of the table between 10:00 PM to 11:00 PM . But the blocks are getting updated by User B after some time. User A getting the read consistent from UNDO ,And User B occupied undo space. Now User C connected and trying to update some other table ,while User C required Undo segments to complete his update. As discussed earlier If Undo retention is in NOGURANTEE MODE and the User C doesn't have enough space in Undo(ExpiredBlocks) to complete the update then Undo data will be overwritten and throughs ORA-01555 to the User A . ORA-01555 relates to insufficient rollback segments or undo_retentions parameter values that are not large enough. The modified data by performed commits and rollbacks causes rollback data to be overwritten when the rollback segments are smaller in size and number of the changes being performed at the time. How Can we Avoid ORA-01555 Error ? To resolve this issue, either increase the parameter of UNDO_RETENTION if you are in AUM mode or use larger rollback segments. The latter solution will allow your rollback data for completed transactions to be kept longer. You may also run into this error when cursors are not being in programs after FETCH and UPDATE statements. Make sure you are closing cursors when you no longer need them. The error can also appear if a FETCH statement is run after a COMMIT statement is issued. If this occurs, you will begin to overwrite earlier records because the number of rollback records created since the last CLOSE will fill the rollback segments. In summary, follow these practices to avoid seeing error ORA-01555 in the future: 1. Do not run discrete queries and sensitive queries simultaneously unless the data is mutually exclusive. 2. If possible, schedule queries during off-peak hours to ensure consistent read blocks do not need to rollback changes. 3. Use large optimal values for rollback segments. 4. Use a large database block size to maximize rollback segment transaction table slots. 5. Reduce transaction slot reuse by performing less commits, especially in PL/SQL queries. 6. Avoid committing inside a cursor loop. 7. Do not fetch between commits, especially if the data queried by the cursor is being changed in the current session. 8. Optimize queries to read fewer data and take less time to reduce the risk of consistent get rollback f failure. 9. Increase the size of your UNDO tablespace, and set the UNDO tablespace in GUARANTEE mode. 10.When exporting tables, export with CONSISTENT = no parameter. When we execute an operations that needs to allocate undo space: 1. Allocate an extent in an undo segment which has no active transaction. Why i in other segments? Because Oracle tries to distribute transactions over all undo segments. 2. If no undo segment was found then oracle tries to online an off-line undo s Segment and uses it to assign the new extent.. 3. If no undo segment was possible to online, then Oracle creates a new undo segment and uses it. 4. If the free space doesn't permit creation of the undo segment, then Oracle tries to reuse an expired extent from the current undo segments. 5. If failed, Oracle tries to reuse an expired extent from another undo segment. 6. If failed, Oracle tries to autoextend a datafile (if autoextensible=yes) 7. If failed, Oracle tries to reuse an unexpired extent from the current undo segment. 8. If failed, Oracle tries to reuse an unexpired extent from another undo segment. 9. If failed, then only the operation will fail. The only difference with retention guarantee is enabled is that Unexpired Extents will not be reused. In other cases if there are no Expired extents that can be re-used then it's possible to encounter ORA-30036. If we see mostly Unexpired extents then it can be either a Undo spacing issue or caused by unreasonably high Undo_retentions. In other words, this means the Undo space is not enough for the specified Undo_retentions or the Tuned_undoretention value. If Expired extents are present and if ORA-30036 is encountered , it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting an ORA-30036 error. This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ). And this is the Oracle doc id to size undo tablespace for Automatic undo management Doc ID 262066.1 Size of Undo Tablespace: You can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by manually estimating the space. Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment. When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries will succeed by guaranteeing the undo required for such queries. After the system has stabilized and you are more familiar with undo space requirements, Oracle recommends that you set the maximum size of the tablespace to be slightly (10%) more than the current size of the undo tablespace. If you have decided on a fixed-size undo tablespace, the Undo Advisor can help us estimate needed capacity, and you can then calculate the amount of retention your system will need. You can access the Undo Advisor through Enterprise Manager or through the DBMS_ADVISOR package. The Undo Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces. Undo Advisor: Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is 'Undo Advisor'. The analysis is based on AWR snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is "1" and END_SNAPSHOT is "2". DECLARE tid NUMBER; tname VARCHAR2(30); oid NUMBER; BEGIN DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task'); DBMS_ADVISOR.CREATE_OBJECT(tname,'UNDO_TBS',null, null, null, 'null', oid); DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid); DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1); DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2); DBMS_ADVISOR.execute_task(tname); end; / Once you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor (ADDM) in Enterprise Manager. This information is also available in the DBA_ADVISOR_* data dictionary views. Calculating space requirements for Undo tablespace You can calculate space requirements manually using the following formula: Undo Space = UNDO_RETENTION in seconds * undo blocks for each second + overhead where: * Undo Space is the number of undo blocks * overhead is the small overhead for metadata and based on extent and file size (DB_BLOCK_SIZE) As an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows: (2 * 3600 * 200 * 4K) = 5.8GBs Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view. Monitoring Undo Tablespaces: Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace. The following dynamic performance views are useful for obtaining space information about the undo tablespace: Views: V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. Oracle uses this view information to tune undo usage in the system. V$ROLLSTAT For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace. V$TRANSACTION Contains undo segment information. DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace. WRH$_UNDOSTAT Contains statistical snapshots of V$UNDOSTAT information. WRH$_ROLLSTAT Contains statistical snapshots of V$ROLLSTAT information. To findout the undo segments in the database. SQL> select segment_name, tablespace_name from dba_rollback_segs; Undo Scripts : For Tablespace Space size : SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2) used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)*8/(1024) exp_space from dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace'); select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space", round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files where tablespace_name like '%&1%' group by tablespace_name) a, (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space where tablespace_name like '%&1%' group by tablespace_name) b Where a.tbl=b.tblsp; Undo Usage by Session: col name heading 'UNDO|Name' form a12 col module heading 'Module' form a18 trunc col username heading 'User' form a10 wrap col sql_hash_value heading 'Hash Value' form 9999999999 col totsize heading 'Undo Bytes Used' form 999,999,999,999 col log_io heading 'Redo Log I/O' form 999,999,999 col phy_io heading 'Physical I/O' form 999,999,999 col used_ublk heading 'Undo Blks' form 9,999,999 col spid heading 'Svr|PID' form 999999 col sid heading 'SID' form 99999 col serial# heading 'Serial#' form 9999999 selectr.name,p.spid,s.sid,s.serial#,s.username,s.module,s.sql_hash_value,t.used_ublk,used_ublk*8192 totsize,t.log_io,t.phy_io from v$transaction t, v$session s,v$process p, sys.v_$rollname r where t.ses_ADDR = s.saddr AND p.addr = s.paddr and t.xidusn = r.usn and used_ublk*8192 > 10000000 order by r.name,totsize desc / To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace select tablespace_name, status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks", sum(blocks)*8/(1024*1024) total_space from dba_undo_extents group by tablespace_name, status; To check for Active Transactions set head on select usn,extents,round(rssize/1048576) rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps from v$rollstat where xacts>0 order by rssize; Undo retention in hours col "Retention" for a30 col name for a30 col value for a50 select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%'; To show UndoRetention Value: Show parameter undo_retention; Definition:
AD admin is an Applications DBA utility that carries out different routine maintenance activities required for Oracle Applications System to run smoothly. Purpose: To perform maintenance tasks on Oracle Applications File System and Database, Types of tasks "adadmin" performs three types of maintenance tasks, 1. File Generation Tasks. 2. Database Maintenance Tasks. 3. File System Maintenance Tasks. Preliminary Steps: Steps you need to perform before running adadmin 1. Login as "applmgr" # su - applmgr 2. Go to APPL_TOP(/apps/applmgr/1211).- /apps/applmgr/1211 3. Run the environment file (To set the environment) $ . APPS<CONTEXT_NAME>.env 4. If any changes are made to the environment, check that they are correctly set. $ echo $<parameter> (e.g.: echo $APPL_TOP) 5.Ensure there is Sufficient Temporary Disk Space.(at least 50MB). 6. Shut down the concurrent managers (if you are relinking or updating files or database objects). 7. Enable Maintenance Mode (if the maintenance task requires downtime). 8. Run the utility by giving the executable of the utility at the command prompt. 9. Reply to the prompts (if prompted). Running "adadmin " [applmgr@nahans 1211] $ adadmin NOTE: Having set your environment you can run the" adadmin" from any location; otherwise you will have to run it only from its location i.e., "/apps/applmgr/1211/ad/12.0.0/bin It asks for prompts as given below, respond accordingly 1. Your default directory is '/apps/applmgr/1211' Is this the correct APPL_TOP (Yes] ? yes 2. AD Administration records your AD Administration session in a text file you specify. Enter your AD Administration log file name or press (Return] to accept the default file name shown in brackets. The default location of the log file apps/applmgr/1211/admin/<SID>/log' ($APPL_TOP/admin/<SID>/log) Filename [adadmin.log]: 3. You can be notified by email if a failure occurs. Do you wish to activate this feature [No]? no 4.Batch size specifies the number of rows after which the data is committed, Please enter the batch size [1000): enter 5. Please enter the name of the Oracle Applications System that this APPL_TOP belongs to. Applications System Name [PROD] : PROD NOTE: If you do not currently have certain types of files installed in this APPL_TOP, you may not be able to perform certain tasks. Example 1: If you don't have files used for installing or upgrading the database installed in this area, you cannot install or upgrade the database from this APPL_TOP. 6. Do you currently have files used for installing or upgrading the database installed in this APPL_TOP [YES] ? YES 7. Do you currently have Java and HTML files for HTML-based functionality installed in this APPL_TOP [YES] ? YES 8. Do you currently have Oracle Applications forms files installed in this APPL_TOP [YES] ? YES 9. Do you currently have concurrent program files installed in this APPL TOP [YES] ? YES 10. Please enter the name Oracle Applicatiohs will use to identify this APPL_TOP. APPL TOP Name (nadhans] : nadhans 11. You are about to use or modify Oracle Applications product tables in your ORACLE database 'PROD Using ORACLE executables in '/apps/product/806'. Is this the correct database [Yes] ? Yes 12. AD Administration needs the password for your 'SYSTEM' ORACLE schema in order to determine your installation configuration. Enter the password for your 'SYSTEM' ORACLE schema: manager 13. The ORACLE username specified below for Application Object Library uniquely identifies your existing product group: APPLSYS Enter the ORACLE password of Application Object Library [APPS) : apps AD Administration menu is displayed, you can perform various maintenance activities by selecting the appropriate menu options like creation and maintenance of Oracle Applications Files and database objects. AD Administration Main Menu --------------------------------------- 1. Generate Applications Files menu 2. Maintain Applications Files menu 3. Compile/Reload Applications Database Entities menu 4. Maintain Applications Database Entities menu 5. Change Maintenance Mode 6. Exit AD Administration Let's have a look at Generate Applications Files menu. Enter your choicé [6] : 1 1. Generate Applications Files menu: This option takes you to the "Generate Applications Files menu" where you can create Oracle Applications Files (like forms, reports, message ...files). The “Generate Applications Files Menu" is as follows: Generate Applications Files -------------------------------- 1. Generate message files 2. Generate form files 3. Generate report files 4. Generate product JAR files 5. Return to Main Menu Enter your choice [5] : 1 1.1 Generate message files Purpose: To generate message binary files used by Oracle Applications to display messages. Source: Oracle Applications object library (FND) tables that belong to APPLSYS schema. Destination: <PROD_TOP>/mesg Action: Upon selecting this option it prompts you for certain related information, respond to it accordingly 1. It prompts you to enter number of workers. (Managers run processes called workers to perform the given task concurrently so as to improve the performance by reducing the span of application being in the maintenance mode). Enter the number of workers [4] : 2. It displays the current character set and asks for confirmation OPSS: PKI-02002: Unable to open the wallet. Check password. Caused By: java.io.IOException4/11/2021 ERROR INFO:
When attempting to start ADMIN server the following error occurs. .Default (self-tuning)'> <<WLS Kernel>> <> <> <1635571086361> <BEA-090892> <The loading of OPSS java security policy provider failed due to exception, see the exception stack trace or the server log file for root cause. If still see no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. Error message: JPS-01050: Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. > ####<Oct 30, 2021 12:18:06 AM CDT> <Critical> <WebLogicServer> <extn-365-dap21.extn.itciss.com> <AdminServer> <main> <<WLS Kernel>> <> <> <1635571086362> <BEA-000386> <Server subsystem failed. Reason: weblogic.security.SecurityInitializationException: The loading of OPSS java security policy provider failed due to exception, see the exception stack trace or the server log file for root cause. If still see no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. Error message: JPS-01050: Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. weblogic.security.SecurityInitializationException: The loading of OPSS java security policy provider failed due to exception, see the exception stack trace or the server log file for root cause. If still see no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. Error message: JPS-01050: Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.loadOPSSPolicy(CommonSecurityServiceManagerDelegateImpl.java:1402) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDelegateImpl.java:1022) at weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:888) at weblogic.security.SecurityService.start(SecurityService.java:141) at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263) 60,1-8 12% weblogic.security.SecurityInitializationException: The loading of OPSS java security policy provider failed due to exception, see the exception stack trace or the server log file for root cause. If still see no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. Error message: JPS-01050: Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.loadOPSSPolicy(CommonSecurityServiceManagerDelegateImpl.java:1402) at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDelegateImpl.java:1022) at weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:888) at weblogic.security.SecurityService.start(SecurityService.java:141) at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263) at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) Caused By: oracle.security.jps.service.credstore.CredStoreException: JPS-01050: Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. at oracle.security.jps.internal.credstore.ssp.CsfWalletManager.openWallet(CsfWalletManager.java:189) at oracle.security.jps.internal.credstore.ssp.SspCredentialStore.doInit(SspCredentialStore.java:378) at oracle.security.jps.internal.credstore.ssp.SspCredentialStore.<init>(SspCredentialStore.java:193) at oracle.security.jps.internal.credstore.ssp.SspCredentialStore.<init>(SspCredentialStore.java:177) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263) at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) Caused By: java.lang.SecurityException: The jurisdiction policy files are not signed by the expected signer! (Policy files are specific per major JDK release.Ensure the correct version is installed.) at javax.crypto.JarVerifier.verifyPolicySigned(JarVerifier.java:336) at javax.crypto.JceSecurity.loadPolicies(JceSecurity.java:378) at javax.crypto.JceSecurity.setupJurisdictionPolicies(JceSecurity.java:323) at javax.crypto.JceSecurity.access$000(JceSecurity.java:50) at javax.crypto.JceSecurity$1.run(JceSecurity.java:82) Solution: Issue with the JDK. JDK in $FMW_HOME/webtier and $ORACLE_HOME locations should be compatible with the JDK exists in $COMMON_TOP/util/. If JDK is compatible in all the locations . Then 1.Removed the cwallet.sso in the /<MW_HOME>/user_projects/domains/<DOMAIN_NAME>/config/fmwconfig/ directory. 2.left the one in /<MW_HOME>/user_projects/domains/<DOMAIN_NAME>/config/fmwconfig/bootstrap and then the server started Once server started copy the same files to the other host Where managed servers are running and start the Managedserver on other hosts. Script to check user availability:
set lines 132 col USER_NAME format a50 select USER_ID,USER_NAME,START_DATE,END_DATE from FND_USER WHERE USER_NAME = upper(‘&user_name’); Script to find list of responsibility attached to a specific userid OR all userid: set lines 132 col user_name format a25 col responsibility format a40 col application format a40 SELECT UNIQUE fu.user_id, fu.user_name user_name, fr.responsibility_key responsibility, fa.application_name application FROM fnd_user fu, fnd_user_resp_groups fg, fnd_application_tl fa, fnd_responsibility fr WHERE fg.user_id(+) = fu.user_id AND fg.responsibility_application_id = fa.application_id AND fa.application_id = fr.application_id AND fg.responsibility_id = fr.responsibility_id AND fu.user_name like upper(‘%&user_name%’) AND fg.end_date is Null ORDER BY fu.user_id, fa.application_name, fr.responsibility_key; Find the application short name by Responsibility name: SELECT FA.APPLICATION_SHORT_NAME, FR.RESPONSIBILITY_KEY, FRG.SECURITY_GROUP_KEY, FRT.DESCRIPTION FROM FND_RESPONSIBILITY FR, FND_APPLICATION FA, FND_SECURITY_GROUPS FRG, FND_RESPONSIBILITY_TL FRT WHERE FR.APPLICATION_ID = FA.APPLICATION_ID AND FR.DATA_GROUP_ID = FRG.SECURITY_GROUP_ID AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID AND FRT.LANGUAGE = 'US' AND FRT.RESPONSIBILITY_NAME = '&RESPONSIBILITY_NAME'; Detail SQL Query to get list of responsibilities for a user in oracle apps: SELECT fu.user_name "&User Name", frt.responsibility_name "Responsibility Name", furg.start_date "Responsibility Start Date", furg.end_date "Responsibility End Date", fr.responsibility_key "Responsibility Key", fa.application_short_name "Application Short Name" FROM fnd_user_resp_groups_direct furg, applsys.fnd_user fu, applsys.fnd_responsibility_tl frt, applsys.fnd_responsibility fr, applsys.fnd_application_tl fat, applsys.fnd_application fa WHERE furg.user_id = fu.user_id AND furg.responsibility_id = frt.responsibility_id AND fr.responsibility_id = frt.responsibility_id AND fa.application_id = fat.application_id AND fr.application_id = fat.application_id ORDER BY fu.user_name,frt.responsibility_name; Query to find the last access date to a Responsibility in R12 SELECT frt.responsibility_name, Max(flr.start_time) FROM applsys.fnd_login_responsibilities flr, applsys.fnd_user fu, applsys.fnd_logins fl, applsys.fnd_responsibility_tl frt WHERE fl.login_id = flr.login_id AND fl.user_id = fu.user_id -- AND fu.user_name = '&Username' -- Comment for Complete List AND frt.responsibility_id = flr.responsibility_id GROUP BY frt.responsibility_name SELECT frt.responsibility_name,fu.user_name, Max(flr.start_time) "Last Connect" FROM applsys.fnd_login_responsibilities flr, applsys.fnd_user fu, applsys.fnd_logins fl, applsys.fnd_responsibility_tl frt WHERE fl.login_id = flr.login_id AND fl.user_id = fu.user_id AND fu.user_name = '&Username' -- Comment for Complete User List AND frt.responsibility_id = flr.responsibility_id and frt.responsibility_name = '&ResponsibilityName' GROUP BY frt.responsibility_name ,fu.user_name But the above query is only giving the results only if the users have used the standard oracle forms. For Ex,If the user only used a web based form(Like the Supplier Form,iProcurement),its not showing in the results. The Profile option Sign-On:Audit Level is set to Form at the site level. Ran Sign-on Audit Responsibilities,Sign-on Audit Forms concurrent requests from system Administrator.Both the reports are not including if a user used web based form. |
AuthorNARESH NADHAN Categories |