UNDO TABLESPACE

Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.

Undo tablespace check undo tablespace:

SQL> show parameter undo_tablespace;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

Check to undo retention:

SQL> show parameter undo_retention;

NAME                                    TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer            900

Check to undo management:

SQL> show parameter undo_management;

NAME                                     TYPE                   VALUE
------------------------------------ ----------- ------------------------------
undo_management                          string                  AUTO

create new undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORACLEAGENT/undo2.dbf' size 100m;

Tablespace created.

change undo tablespace

SQL> alter system set undo_tablespace='undotbs2' scope=spfile; 
System altered.

SQL> show parameter undo_tablespace;

NAME                                   TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes
 Database mounted.
Database opened.

SQL> show parameter undo_tablespace;

NAME                                   TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string           undotbs2

change undo_retention

SQL> show parameter undo_retention;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer             900

SQL> alter system set undo_retention=920;
System altered.

SQL> show parameter undo_retention; 

NAME                                    TYPE           VALUE
------------------------------------ ----------- ------------------------------
undo_retention                        integer           920 

check db block size

SQL> show parameter db_block_size;

NAME                                     TYPE              VALUE
------------------------------------ ----------- ------------------------------
db_block_size                            integer           8192

change undo management { auto | manual }

SQL> show parameter undo_management;
 NAME                                  TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_management                       string             MANUAL

SQL> alter system set undo_management='auto' scope=spfile; 
System altered.

SQL> shut immediate; 
Database closed.
Database dismounted. 
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes 
Database mounted.
Database opened.

SQL> show parameter undo_management;

NAME                                   TYPE            VALUE
------------------------------------ ----------- ------------------------------
undo_management                        string         AUTO

Undo stages:

SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, 
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;

TABLESPACE                      STATUS    SUM_IN_MB   COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1                        EXPIRED      1399.25     200
UNDOTBS1                        UNEXPIRED    168         21
UNDOTBS2                        EXPIRED      .375        6
UNDOTBS2                        UNEXPIRED     5.4375     42

Active, expired and unexpired transaction space usage in Undo Tablespace:-
ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.
UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.
Free blocks : At the time of creating new undo tablespace shows empty blocks

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to change Apps Password in R12.2

Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well as some manual steps after changing the password using FNDCPASS.

Whether you use FNDCPASS or AFPASSWD to change the APPLSYS/APPS password, you must also perform some additional steps. This is because in R12.2, the old AOL/J connection pooling is replaced with Weblogic Connection Pool ( JDBC Data source ). Currently this procedure is not yet automated. It would be good, if this can be automated using some WLS scripting.

Important: These steps must be carried out on the run file system.

Shut down the application tier services using the $INST_TOP/admin/scripts/adstpall.sh script.

Change the APPLSYS password, as described for the utility you are using.

Start Admin Server using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.

Change the “apps” password in WLS Data source as follows:
- Log in to WLS Administration Console.
- Click Lock & Edit in Change Center.
- In the Domain Structure tree, expand Services, then select Data Sources.
- On the “Summary of JDBC Data Sources” page, select EBSDataSource.
- On the “Settings for EBSDataSource” page, select the Connection Pool tab.
- Enter the new password in the “Password” field.
- Enter the new password in the “Confirm Password” field.
- Click Save.
- Click Activate Changes in Change Center.

Start all the application tier services using the $INST_TOP/admin/scripts/adstrtal.sh script.

Verify the WLS Datastore changes as follows:
Log in to WLS Administration Console.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select Monitoring > Testing.
Select “oacore_server1“.
Click Test Data Source
Look for the message “Test of EBSDataSource on server oacore_server1 was successful“.

Important: Steps 4, 5 and 6 are only applicable when changing the APPLSYS password. They are not applicable when changing passwords for product schemas or the SYSTEM schema.

In the next prepare phase after the password change, adop will invoke EBS Domain Configuration to ensure that the WLS data source on the patch file system will be synchronized with the new APPS password.

How To Change The Password For The SYSADMIN User
12.2 New Feature AFPASSWD utility to change password

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

How to change priority of concurrent request

Change the priority of the concurrent request

By changing priority of concurrent request you can run the important concurrent request before non-priority concurrent request.

Open “System Administrator” responsibility. Go to Concurrent > Requests
Select “Specific Requests“. In the field called “Requester” put the User ID for whom you want to change priority.
Select “View Details“. Change the Priority from default “50“. Change it to “10” for high priority. [1 for highest and 99 for lowest].

Change the User priority

You can set the priority of individual user priority. For high priority user [CFO or CEO :-)] the report will get completed the request will soon start to run than a normal priority user. Change the value of profile option “Concurrent:Request Priority” in User level to make him or her a high or low priority user.

Alternatively you can change the priority of a “Concurrent Request” in define concurrent request screen.

You can do the following two steps to increase the performance of concurrent manager.

Schedule the following concurrent request program “Purge Concurrent Requests and / or Manager Data“.
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

APPLSYSPUB user Concepts in Oracle Application

APPLSYSPUB

The default password is ‘PUB’ and in case you change it, the password should be always in Upper Case. It is a public schema. which helps in validating Application user credentials when we login to Oracle Application.

How Applsyspub user works?

When we login to applications, initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.

Once authentication is successfully completed, Oracle Application allows to connect to apps schema and allows to choose a responsibility. By using responsibility, we can use HTML or Oracle forms interface to access data that resides in product schema.

How to change password of APPLSYSPUB ?

FNDCPASS utility is used to change the applsyspub password.

$FNDCPASS APPS/[apps_password] 0 Y SYSTEM/[system_passwoord] ORACLE APPLSYSPUB [new_passsword].0 & Y are flags for FNDCPASS0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form)’Y’ indicates that this method is directly invoked from the command-line and not from the Submit Request Form.

Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.

In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.

How to fetch the APPLSYSPUB user password

1. echo $GWYUID

APPLSYSPUB/PUB

2. grep -i applsyspub $FND_SECURE/$TWO_TASK.dbc

GWYUID=APPLSYSPUB/PUB

3. grep -i s_gwyuid_pass $CONTEXT_FILE

password oa_var=”s_gwyuid_pass”– PUB — password

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

R12.2 adop phase=cleanup failed with ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old:

Below i face an error when i was running adop session.

When i was running adop phase=cleanup i was hit with the below error

[applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…

RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Wed May 24 2021 03:34:53
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 62)
Cleanup is not done in earlier session
[START 2021/05/24 03:35:27] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
[START 2021/05/24 03:35:48] cleanup phase
[EVENT] [START 2021/05/24 03:35:52] Performing Cleanup steps
[EVENT] [START 2021/05/24 03:35:57] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/log
[EVENT] [END 2021/05/24 03:39:43] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/05/24 03:39:54] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/adzdallddls_20210524_033957.out
[END 2021/05/24 03:39:58] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[ERROR] Failed to execute sql statement :
declare
result varchar2(10);
begin
ad_zd.cleanup(‘QUICK’);
exception
when others then
raise_application_error(-20001,’Error while calling ad_zd.cleanup’ || sqlerrm);
end;
[ERROR] SQLPLUS error: buffer=
SQL*Plus: Release 10.1.0.5.0 – Production on Wed May 24 03:40:01 2021
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> declare
ERROR at line 1:
ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old:
rollback segment number 2 with name “_SYSSMU2_735814084$” too small
ORA-06512: at line 7
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[UNEXPECTED]Error occurred while calling cleanup plsql API
[UNEXPECTED]Cleanup phase completed with errors/warnings. Please check logfiles
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
adop exiting with status = 1 (Fail)
[applmgr@r122 ~]$

To overcome this issue.I have extended the undotablespace using below:

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME like ‘APPS_UNDOTS1’;

TABLESPACE_NAME FILE_NAME

APPS_UNDOTS1 /u01/oracle/PROD/data/undo01.dbf

SQL> ALTER DATABASE DATAFILE ‘/u01/oracle/PROD/data/undo01.dbf’ RESIZE 10240M;
Database altered.
SQL>

Then again i executed the adop phase=cleanup

applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…

RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admi
n/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/ad
min/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Fri Feb 28 2021 04:11:18
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 59)
Cleanup is not done in earlier session
[START 2021/02/28 04:11:45] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
[START 2021/02/28 04:12:03] cleanup phase
[EVENT] [START 2021/02/28 04:12:06] Performing Cleanup steps
[EVENT] [START 2021/02/28 04:12:11] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/log
[EVENT] [END 2021/02/28 04:13:26] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/02/28 04:13:32] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdallddls_20210228_041335.out
[END 2021/02/28 04:13:36] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[EVENT] [END 2021/02/28 04:57:56] Performing Cleanup steps
[END 2021/02/28 04:58:05] cleanup phase
[START 2021/02/28 04:58:11] Generating AD_ZD_LOGS Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdshowlog.out
[END 2021/02/28 04:58:24] Generating AD_ZD_LOGS Report
[END 2021/02/28 04:58:25] adzdoptl.pl run
adop phase=cleanup – Completed Successfully
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
adop exiting with status = 0 (Success)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Adding a Host Target in EM 13C

Before adding any Software Targets such as Databases, EBS, Middleware it is mandatory to add the Host Targets first in EM. In this post, we will see the simple process of adding a Host Target in EM. I am going to add a host “apps.example.com” which actually has an Oracle EBS Vision Instance running on it. Before you add the Host you need ensure below are done already:

No firewall between OMS (EM) Host and Target Host. If you cannot open all ports, at least open the needed ports for e.g. 3872 on Target Host and 4889,4903 ports on EM. Always check ports needed to be opened using “emctl status oms -details”
Ensure both hosts are reachable from one another
Create directory for emagent in Target Host for e.g. /u01/install/emagent

Adding Host Target

Adding a Host Target is very easy and self explanatory. Please find the screenshots below.

Using emctl command

Using the emctl status agent -details, you can find details of the Agent and its status as shown in the screenshot above.

Known Issues and Suggestions

You might be facing issues related to firewall, OS permissions on agent, unavailable of OS packages such as ZIP, UNZIP. These you should be able to fix easily ideally.

How to resolve issue :

Clearing an Enterprise manager agent that fails to upload

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

OPMN fails to start after latest EBS 12.2 ETCC patches

Sometimes applying Tech Stack patches lead to bugs or issues which we know. This is one such instance where the OPMN was failing to start after applying latest ETCC patches. So let us see in detail:

I have applied latest ETCC patches which includes patches for WebTier, 10.1.2.3, WLS etc. Once they are applied, opmn was failing to start and when I checked the OPMN log file, I have found the below error in it:

[opmn] [ERROR:1] [] [ons-secure] Connection server SSL set credentials failed (43084)
[opmn] [ERROR:1] [222] [ons-secure] SSL initialization failed

Oh! But wait.. I don’t have SSL or TLS configured in my environment but why is it complaining about SSL here? So apparently! after applying latest security patches of Oracle Process Manager and Notification Service (OPMN) and Oracle HTTPS Server (OHS) 11.1.1.9 components for Oracle E-Business Suite, this is expected.

Why does it happen?

The FMW no longer accepts the usage of certificates signed by MD5withRSA and requires using SHA256withRSA or better. So even though you don’t use SSL or TLS in EBS, you still have to create self-signed certificates and import them to wallet.

The detailed steps are present below:

Prerequisite Steps to Configure Oracle Fusion Middleware 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 Before Applying the July 2019 and Later FMW OSS Security Patch (Doc ID 2555355.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

AD/TXK 12 are now available for EBS 12.2

Oracle released update to the AD/TXK on 7th Aug, 2020. The previous version for R12.AD.C.Delta.11 was one of the major updates and there has not been a update to AD/TXK since a long time.

Oracle now released for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12. There are no enhancements for any of the AD utilities and mostly its the bug fixes.

Check the documents below for more details:

Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 (Doc ID 2649885.1)

Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Oracle Statspack Installation / Re-Creation

Why we need statspack? However for some non critical database where we don’t have license of tuning pack. Hence we need to survive on statspack.

1) Take export backup of exiting perftstat user

2) Drop statspack

 @?/rdbms/admin/spdrop.sql;

3) Create stastpack

 @?/rdbms/admin/spcreate.sql;

4) Change stastpack snap level to level 6

BEGIN
statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
END;
/

select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

5) Take manual snap

 exec PERFSTAT.statspack.snap;

6) Schedule statspack auto jobs for statspack snap ( Please note job id)

 @?/rdbms/admin/spauto.sql

7) Change snap interval to 30 min

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=826;

exec dbms_job.interval(826,'sysdate+(1/48)');

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT , 
to_char(next_date ,'DD-MON-YYYY:HH24:MI:SS') "next date" , 
failures from dba_jobs where JOB=826;

select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
from stats$snapshot,v$database;

8) After 30 minutes verify snap interval working fine with 30 min and level 6

@?/rdbms/admin/spreport.sql

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us: