DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, September 25, 2019

Useful Information Regarding Oracle Cloud Marketplace




Some of you might be aware of the Oracle Cloud Market place. But there are certain useful Applications services available for DBAs, APPS DBA or Cloud Admins that you might not be aware of.

I’m sharing some applications and services that I've handpicked for you to take a look at. You may also check out other useful apps. Based on my experience,  I have written my own analysis for few useful applications.

Though you might be aware of it,  I would still like to highlight a few basics. Using these applications requires a trial license except vision instance. All of them can be used for POC or for Demo purpose only with limited credits.

Oracle Cloud Marketplace is an online store dedicated to marketing cloud business apps and professional services offered by Oracle and its Cloud Partners.

https://cloudmarketplace.oracle.com/marketplace/en_US/homePage.jspx

Applications Link Usage
Oracle E-Business Suite 12.2.8 Demo Install Image



https://cloudmarketplace.oracle.com/marketplace/en_US/listing/50307373





We can use:
1.       To build any plugins (like interface, apex reports) or any customizations in 122 environment with vision data.
2.       To show POC to Customer, How Oracle 122 EBS works on OCI (IaaS). Setting up from market place is very easy, it takes less than an hour practically to setup 1228 environment.
3.       To Train or practice – Setting up Security , network , storage , Infra Backup, scale up features in OCI
4.       To Train or practice – Oracle EBS 122 Apps DBA Maintenance.

EBS 12.2.5 Fresh Install Image https://cloudmarketplace.oracle.com/marketplace/en_US/listing/5248397 Create a Compute Cloud Service instance (virtual machine) containing the Release 12.2.5 database and application tiers from the provided image. Use this single-node fresh install instance to implement setups and organization-specific business flows as part of your Oracle E-Business Suite rollout.
EBS 12.2.7 Fresh Install Image https://cloudmarketplace.oracle.com/marketplace/en_US/listing/38487279 Create a Compute Cloud Service instance (virtual machine) containing the Release 12.2.7 database and application tiers from the provided image. Use this single-node fresh install instance to implement setups and organization-specific business flows as part of your Oracle E-Business Suite rollout.
EBS OS-Only Image https://cloudmarketplace.oracle.com/marketplace/en_US/listing/5248662 The OS-Only Machine Image delivers a Compute Cloud Service instance (virtual machine) with the Oracle Linux 6.8 operating system plus the necessary packages, kernel parameter settings, and users required to run Oracle E-Business Suite. Once a virtual machine or machines are installed, if you are an existing Oracle E-Business Suite 12.1.3 or 12.2 customer you may be able to migrate your instances to the Oracle Compute Cloud Service

You can use
1.       If you want to test or practice any migration of Oracle EBS
2.       If you want to practice Installation of Oracle EBS
Oracle Identity Management https://cloudmarketplace.oracle.com/marketplace/en_US/listing/63376046 Oracle Identity Management provides a unified, integrated security platform designed to manage user lifecycle and provide secure access across the enterprise resources, both within and beyond the firewall and into the cloud
Oracle E-Business Suite Cloud Manager https://cloudmarketplace.oracle.com/marketplace/en_US/listing/49940494 Oracle E-Business Suite Cloud Manager is a web-based graphical user interface that automates the creation and management of Oracle E-Business Suite environments on Oracle Cloud Infrastructure and associated database services. Oracle E-Business Suite Cloud Manager was designed to simplify the diverse tasks Oracle E-Business Suite DBAs perform on a daily basis, with the goal of reducing the effort needed to perform them.
Oracle Data Integrator https://cloudmarketplace.oracle.com/marketplace/en_US/listing/62627436
This image provides a full instance of Oracle Data Integrator (ODI) based on 12.2.1.3
The image includes.:
Preconfigured ODI repository
ODI KMs for ADW 
ODI KMs for BI cloud connector
One standalone agent is preconfigured in the image to execute data integration flow.
Oracle Golden Gate 19c for Oracle https://cloudmarketplace.oracle.com/marketplace/en_US/listing/58489224
EBS Information Discovery 12.2 V7 https://cloudmarketplace.oracle.com/marketplace/en_US/listing/22406441 Oracle E-Business Suite Information Discovery brings enterprise data discovery platform to your EBS implementation.
Oracle ERP/SCM Cloud to Oracle WMS Cloud https://cloudmarketplace.oracle.com/marketplace/en_US/listing/39057623 This is a sample integration between Oracle Warehouse Management System (WMS) Cloud and Oracle Enterprise Resource Planning System (ERP)/Supply Chain Management (SCM) cloud via Integration Cloud Service (ICS).
ERP Cloud and FTP Server| Extract Bulk Data Async https://cloudmarketplace.oracle.com/marketplace/en_US/listing/61921647 The integration extracts BIP report data from ERP cloud in asynchronous manner. The scheduled orchestration pattern is used to give an integration user the ability to schedule data extract as required. Extracted data are stored in an FTP server. This way the actual report data could be available for the end user or application from the FTP location. If required, the extracted data could be enriched or altered within the integration.
Oracle E-Business Suite Provisioning Tools Image https://cloudmarketplace.oracle.com/marketplace/en_US/listing/5514423 Use the provided image to create a virtual machine containing a provisioning tool, the EBS Cloud Admin Tool. This tool allows you to provision environments in the Oracle Cloud, and perform additional tasks such as configuration of integrations, scaling, cloning using volume snapshots, etc. 
Oracle Enterprise Manager 13c https://cloudmarketplace.oracle.com/marketplace/en_US/listing/51275099
Oracle Enterprise Manager has built-in management capabilities of the Oracle stack for traditional and cloud environments that enables you to monitor and manage the complete Oracle IT infrastructure from a single console.
Oracle Autonomous Linux https://cloudmarketplace.oracle.com/marketplace/en_US/listing/63236232
You may test or practice provisioning Oracle Autonomous Linux in Oracle Cloud
Or showcase to customer on how it can be of use for their business.

Saturday, September 21, 2019

Steps to compile jsp

 Please follow the below action plan Steps to compile jsp:

1. We need to login to apps server and su to apps user .

2. Then we need to Shutdown the application services.

3. Now, compile the jsp's manually by using the command as shown below:

$cd $COMMON_TOP/
$rm _pages   [here, either you can remove or take backup]
$mkdir _pages
$chmod 755 _pages  [this may be automatically]
$perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

4. Then check whether all the jsp's are getting compiled successfully.

5. Restart the application services.

6. Open IE or your web browser and clear the browser cache.

7. Then again retest the issue. 

Query to find User Session Statistics


set linesize 132
rem
ttitle 'User Session Statistics'
rem
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a10 heading 'ORACLE|USERNAME'
col log_per_sec format 999999 heading 'LOG|PER|SEC'
col logical format b9999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a32 heading 'PROGRAM NAME'
col module format a32 heading 'MODULE'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 9999 heading 'LAST|CALL|MIN'
col status format a1 heading 'S'
rem
select s.process,
       p.spid,
/*
       p.pid,
*/
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       ( i.block_gets + i.consistent_gets ) /
       ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
       i.block_gets + i.consistent_gets logical,
       physical_reads /
       ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
       i.physical_reads,
/*
       s.audsid,
*/
       to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
       s.last_call_et/60 last_call_min,
       decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,
       decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module
  from v$process p, v$session s, v$sess_io i
 where i.sid = s.sid
   and s.paddr = p.addr
   and s.sid = &sid;
rem
set linesize 80

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-19909: datafile 1 belongs to an orphan incarnation

When I tried to Flashback on in standby database then suddenly below message found in alert log and standby database not able to apply archive-logs also. So, here are messages from standby database alert log:

-- Message from alert log ( in standby database)
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/PROD/PROD/xxxx.dbf'
Recovery Slave PR00 previously exited with exception 19909

RCA:
Primary and standby have different incarnations:

-- in standby db

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-MAY-2019 00:31:04
2       2       PROD  3680942556       PARENT  5982448719535 30-MAY-2019 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-JUN-2019 04:01:27
3       3       PROD  3680942556       CURRENT 5983354274164 10-JUN-2019 04:02:53

-- in primary db

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-MAY-2019 00:31:04
2       2       PROD  3680942556       CURRENT 5982448719535 30-MAY-2019 22:31:15

-- Solution:

You need to reset the standby database's incarnation to match the primary's:

-- in standby

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-MAY-2019 00:31:04
2       2       PROD  3680942556       PARENT  5982448719535 30-MAY-2019 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-JUN-2019 04:01:27
3       3       PROD  3680942556       CURRENT 5983354274164 10-JUN-2019 04:02:53

RMAN> reset database  to incarnation 2;
database reset to incarnation 2

RMAN> list incarnation of database;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-MAY-2019 00:31:04
2       2       PROD  3680942556       CURRENT 5982448719535 30-MAY-2019 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-JUN-2019 04:01:27
3       3       PROD  3680942556       ORPHAN  5983354274164 10-JUN-2019 04:02:53

-- Verify MRP is active or not

$ ps -ef|grep mrp
oracle   23358 21699  0 12:56 pts/1    00:00:00 grep mrp
-- Verify standby database status and role
SQL> select name,open_mode ,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PHYSICAL STANDBY

-- now start MRP in standby database

$ !sql
SQL> alter database recover managed standby database disconnect from session;
Database altered.

-- Verify the MRP process
$ ps -ef|grep mrp
oracle   23455     1  0 12:58 ?        00:00:00 ora_mrp0_PROD
oracle   24125 21699  0 13:11 pts/1    00:00:00 grep mrp

Now archive-logs are applying.

ORA-01666: control file is for a standby database - failover over standby as primary

ORA-01666: control file is for a standby database - failover over standby as primary

SQL> select name,open_mode ,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD     READ ONLY            PHYSICAL STANDBY

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

SQL> startup mount;
ORACLE instance started.
Total System Global Area 3.4206E+10 bytes
Fixed Size                  2238616 bytes
Variable Size            3422553960 bytes
Database Buffers         3.0736E+10 bytes
Redo Buffers               45682688 bytes
Database mounted.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database

SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD     MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

SOLUTION:

-- Fail-over
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.

-- Now verify the database status

SQL> select name,open_mode ,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PRIMARY

-- Now open the database

SQL> alter database open;
Database altered.

SQL> select name,open_mode ,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      READ WRITE           PRIMARY

-- If you have flashback on, make it off if you want.

SQL> alter database flashback off;
Database altered.

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED
ISSUE:
During cloning a database, I found below error:

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 07/20/2019 11:20:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
.....
 I tried to start, but not able to open the database and same error came.
SQL> startup;
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
Process ID: 85042
Session ID: 762 Serial number: 59546

SOLUTION

This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database. Either we can set it to STANDARD or.EXTENDED The default value is STANDARD
MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2
MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .
We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.
With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.
So here, I did below fixes to open the database:

SQL> startup mount;

SQL> alter system set MAX_STRING_SIZE='EXTENDED' scope=spfile;
System altered.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.
SQL>

SQL> @?/rdbms/admin/utl32k.sql

PLS-00201: identifier 'DBMS_CRYPTO' must be declared issue - Fix

PLS-00201: identifier 'DBMS_CRYPTO' must be declared issue - Fix

ISSUE:

SQL> show error
Errors for FUNCTION "SCOTT"."DECRYPT_CHAR":

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/19     PL/SQL: Item ignored
6/34     PLS-00201: identifier 'DBMS_CRYPTO' must be declared
17/1     PL/SQL: Statement ignored
17/24    PLS-00201: identifier 'DBMS_CRYPTO' must be declared
SQL>

SOLUTION:

You need to give access on dbms_crypto to your schema. Here your schema is "scott".
SQL>  grant execute on sys.dbms_crypto to SCOTT;

Grant succeeded.

Moving Audit trail objects to different table-space to gain performance

Moving Audit trail objects to different table-space to gain performance:

Use the DBMS_AUDIT_MGMT package to move them to the SYSAUX tablespace:

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
--this moves table AUD$
audit_trail_location_value => 'SYSAUX');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
--this moves table FGA_LOG$
audit_trail_location_value => 'SYSAUX');
END;
/

Query to shows Day wise,User wise,Process id of server wise- CPU and I/O consumption

shows Day wise,User wise,Process id of server wise- CPU and I/O consumption 

set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;


FLUSH_DATABASE_MONITORING_INFO Procedure

FLUSH_DATABASE_MONITORING_INFO Procedure

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

after executing above package, then below query will give you updated data.

select table_owner,table_name,inserts,updates,deletes from DBA_TAB_MODIFICATIONS;

query to find segment name

Run below query to find segment name :

1) Query-1: Find segment name with owner
To find segment name from Corrupt Block
Find segment name from Corrupt Block:
SQL> select * from dba_extents where file_id = 80 and 1288987 between block_id and block_id + blocks -1 ;


2) Query-2: Find object details

SQL> select * from dba_objects where object_name='MLOG$_PATIENT' and object_type='TABLE'; 

Query to obtain the amount of redo generation over time by hour and MB:


Query to obtain the amount of redo generation over time by hour and MB:

set pages 999 lines 400
select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB
from v$archived_log
group by trunc(first_time, 'HH24')
order by 1
/

ORA-20005: object statistics are locked (stattype = ALL)

ORA-20005: object statistics are locked (stattype = ALL) - Solution
ERROR

During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table:

SQL> execute dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

BEGIN dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); END;

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1
SQL>

Analysis:
Verify whether statistics is locked for that table or not.
SQL> col OWNER for a12
SQL> col table_name for a30
SQL> col STATTYPE_LOCKED for a15
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='EMPLOYEE' and owner='HR';

OWNER        TABLE_NAME                     STATTYPE_LOCKED
------------ ------------------------------ ---------------
HR           EMPLOYEE                           ALL
SQL>

So, the column "stattype_locked" value is showing "ALL", that means statistics is locked for that table.

Solution :

SQL> EXEC DBMS_STATS.unlock_table_stats('HR','EMPLOYEE');
PL/SQL procedure successfully completed.
SQL>

Now try to run stats again:

SQL> execute dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>

Similarly we can unlock stats for a schema also.

SQL> EXEC DBMS_STATS.unlock_schema_stats('HR');
PL/SQL procedure successfully completed.

Thursday, September 19, 2019

Oracle E-Business Suite Learning Subscription Free Access Videos



Congratulations! You acknowledge the potential of the Oracle platform. Time to dive into the pool of knowledge and learn how to work with Oracle. Having a hard time? Don’t worry, our extensive collection of videos will help anyone set their foot firmly into the vast world of Oracle E-Business Suite Learning. Feel free to explore.

You need Oracle.com login account to access those videos.

This is a useful information for everyone, Hence please do share this post with your friends and colleagues.


Module
Link
Operational efficiency => Monitoring , Maintenance , Upgrade , TLS Configuration, Online patching, cloning, Managing customization, 12.2 Upgrade best practices, Performance management
Technology Stack and Architecture
Information Discovery / Endeca
User interface
India Localization
Financial control and reporting
Credit to Cash
Financial Overview
HCM Overview
HCM Compensation and Benefits
HCM Payroll
Logistics
Optimize Fulfilment with Oracle EBS Logistics
Advanced Catch Weight
Manufacturing
Order Management
OM Solution Selling
Credit Checking
Procurement –
Advanced Procurement
Employee Driven Procurement
Operations Management
Service Management
Projects
Value Chain Planning
Advanced Supply Chain
Demantra / Demand Management
Inventory Optimization