R12.2 FlexField Value Set Security

 R12.2 FlexField Value Set Security

FlexField value set security is a new feature in R12.2.2 that allows you to determine which roles can access which value set values. This allows you delegate GL Value Set Value maintenance to one set of functional users, while allowing Item Category Value Set Values to be maintained by another. Often people just take the slam dunk approach of assigning the role ‘FlexField Value Set Security: All privileges’, allowing a user access to all value sets. 

We will also discuss the use of the wizard ‘FlexField Value Sets: Security Administration Setup’

 1) Need to login as SYSADMIN 

2) Select the "User Management" responsibility 

3) Navigate to "User” 

4) Find the particular user then click on update icon 

5) Click on "Assign Role”

 6) Select "Role" as search by and enter value as "Flexfield Value Set Security: All privileges” Or Select "Code" as search by and enter value as"UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE" 

7) Select the role from result 

8) Enter a justification text. 

9) Click on "Apply”

 10) Clear the cache from functional administrator responsibility if required.

Script to Provide the access from Backend(with apps user)

Begin

  WF_LOCAL_SYNCH.PROPAGATEUSERROLE(p_user_name => 'XYZ',

      p_role_name => 'UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE');

  commit;

end;

References -

Doc id 1612727.1 "Flexfield Value Sets Do Not Appear in List of Values (LOV) in Oracle E-Business Suite Release 12.2 on Segment Values Form FNDFFMSV

http://www.jrpjr.com/paper_archive/R12.2%20Flexfield%20Value%20Set%20Security.pdf

Benefits of Oracle EBS 12.2.9

 Top 5 Benefits of Oracle EBS 12.2.9

1. Functional Efficiency: 

Upgrading to R12.2.9 will allow you to leverage these functional enhancements, in addition to Online Patching, and the mobile application capabilities introduced in R12.2.

2. Reduce maintenance and support costs:

EBS 12.2.9 reduces the total cost of ownership and provides open standards to reduce maintenance and support costs for your EBS environment.

3. Regulatory Standards & Compliances: 

Receive the latest tax and regulatory updates from Oracle, and stay compliant with SOX (Sarbanes Oxley), ITAR (International Traffic in Arms), etc.

4. Stable platform for users: 

Several new functionalities and User Interface enhancements help modernize your business processes for greater operational efficiency. You can also utilize the Online Patching feature, which provides higher availability and reduces downtime for patching and maintenance.

5. Multiple instances: 

An upgrade to R12.2.9 can help simplify application complexity by consolidating your business units into a single global instance.

Oracle E-Business Suite and the Oracle Multitenant Architecture

 

EBS R12 -Administration for 19c Version

·         Oracle 19c is certified with 12.2 version

·         Minimum Certified version for 19c Database is 19.3

·         New concept of Multitenant introduced with 19c

   

CDB (Container Database) Architecture

 

Database 19c  are now certified with CDB (Container Database)

1. ·         A CDB with one PDB (single tenant) is currently the only certified deployment for Oracle E-Business Suite with Database 19c.
2. ·         A CDB with multiple PDBs (multitenant) is not currently certified for Oracle E-Business Suite.
3. ·         A non-CDB architecture is not planned to be certified or supported for EBS with Database 19c.
4. ·         Upgrade to Database 19c, you will convert your EBS database to the CDB architecture with a single pluggable database (PDB) i.e EBS database.
5. ·         In EBS 19c database, it contains two environment files one for CDB and the other one for PDB and one Context file. The CONTEXT_FILE points to PDB database. when we source the PDB environment then only we can find the database CONTEXT_FILE.
6. ·         All EBS specific tasks on the database side should be performed only on EBS instance, which is Pluggable database (PDB) in this case. Also, EBS specific database initialization parameters must be updated only in the pluggable database (EBS_PDB).

 

Database Tier Environment Sourcing:

·         CDB -  <EBS CDB>_<hostname>.env

·         PDB -   <EBS PDB>_<hostname>.env

1) How to source and connect the Oracle E-Business Suite CDB environment as SYSDBA?

Oracle E-Business Suite database CDB environment files are located in the database ORACLE_HOME. 

Run the following commands to connect the CDB database

$ cd $ORACLE_HOME

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

2) How to start the CDB that hosts the Oracle E-Business suite PDB?

Run the following commands to start the CDB

$ source <CDB_NAME>.<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> startup; 

3) How to shutdown the CDB that hosts the Oracle E-Business suite PDB?

Run the following commands to shutdown CDB database

 $ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

Two scripts introduced in EBS Database 19c to start and stop the CDB database and listener.

·                     adcdbctl.sh

·                     adcdblnctl.sh

4) How to source and connect the Oracle E-Business Suite PDB environment as SYSDBA?

Run the following commands to connect the EBS PDB database

$ source <CDB_NAME>_<NODE_NAME>.env

$ export ORACLE_PDB_SID=<PDB NAME>

$ sqlplus "/ as sysdba"

5) How to open the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, 

then execute the SQL command to start the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> alter pluggable database open read write services=all;

6) How to close the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, 

then execute the SQL command to stop the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> alter pluggable database <EBS PDB Name> close immediate; 

7) How to find Oracle E-Business Suite PDB information and status?

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> show pdbs;

Returns the values for the con_id, con_name, open mode, restricted values of all your PDBs.

SQL> select name, open_mode from v$pdbs; 

8) How to connect the Oracle E-Business Suite PDB as APPS user?

Run the following commands to connect PDB as APPS user

$ source <PDB_NAME>_<NODE_NAME>.env

$ sqlplus apps/apps@<PDB_NAME>

9) How to connect the Oracle E-Business Suite PDB as SYSTEM user?

Run the following commands to connect PDB as SYSTEM user

$ source <PDB_NAME>_<NODE_NAME>.env

$ sqlplus system/manager@<PDB_NAME>

10) How to access the Oracle E-Business Suite PDB while logged into the CDB?

Use the following command to set the environment, show the PDB name, 

and then connect to that PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> show pdbs;

SQL> alter session set container="PDBNAME";

11) Where to look for PDB errors if encounter a problem?

Source the environment and then review any plugin violations, 

as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;

12) How to run AutoConfig on the database tier 

You should always source the Oracle E-Business Suite PDB environment 

before running AutoConfig. You can do so by running the following commands:

$ cd $ORACLE_HOME

$ source <PDB_NAME>_<NODE_NAME>.env 

Then run AutoConfig script

13) How to set up the environment to run an adgrants.sql?

Execute following steps before running Oracle E-Business Suite programs 

as adgrants.sql.

$ source <CDB_NAME>_<NODE_NAME>.env

$ export ORACLE_PDB_SID=<PDB_NAME>

$ sqlplus "/ as sysdba"

14) How to run the EBS Technology Codelevel Checker (ETCC) against Oracle E-Business Suite PDB?

-The EBS Technology Codelevel Checker (ETCC) utility provides two scripts to 

help ensure you have the necessary database and application tier patches installed

 on your Oracle E-Business Suite Release 12.2 instance.

- ETCC extracts environment-related information from the context file 

(using the location defined in $CONTEXT_FILE), so you need to source the

 Oracle E-Business Suite PDB environment before you run the database checker script.

 $ source <EBS PDB Name>_<NODE_NAME>.env

 $ ./checkDBpatch.sh

15) How to list the OPatch inventory for a multitenant?

You can list the OPatch inventory of a multitenant database in the same

 way as for non-CDB. 

$ source <CDB_NAME>_<NODE_NAME>.env

$ export PATH=$PATH:$ORACLE_HOME/OPatch

$ opatch lsinventory –detail

16) How to set up the environment to install a database patch in an Oracle E-Business Suite multitenant database?

Source the CDB environment and add the OPatch directory to the path, 

as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env

$ export PATH=$PATH:$ORACLE_HOME/OPatch

Ref - (Doc ID 2567105.1)

Oracle database tablespace report

 

Oracle database tablespace report – SQL script

Amazing script written by Kirill Loifman

SQL will show Oracle database tablespace information.. The SQL script will list Oracle database tablespaces including tablespace status and type, counts of files and segments per tablespace and the most important – display proper tablespace 

– New [2.0]: Tablespace (TS) type is extended to display UNIFORM / System Extend Management and ASSM
– New [2.0]: Display actual used space in UNDO and TEMP tablespaces (not HWM as before)
– New [2.0]: Runtime is considerably reduced
– New [2.0]: Compatible with OEM CC 12c/13c output
– Tested on Oracle database 10g, 11g, 12c
– Column: “Max Size” – maximum possible size of a tablespace as a result of Autoextention of database files
– Column: “TS Type” (Tablespace type):
-> LM/DM – Local/Dictionary Managed
-> SYS/UNI – SYStem/UNIform Extent Management (LM only)
-> ASSM/MSSM – Automatic/Manual Segment Space Management (ASSM -> LM only)

set pagesize 10000 linesize 300 tab off   col tablespace_name format A22              heading "Tablespace" col ts_type         format A13              heading "TS Type" col segments        format 999999           heading "Segments" col files           format 9999 col allocated_mb    format 9,999,990.000    heading "Allocated Size|(Mb)" col used_mb         format 9,999,990.000    heading "Used Space|(Mb)" col Free_mb         format 999,990.000      heading "Free Space|(Mb)" col used_pct        format 999              heading "Used|%" col max_ext_mb      format 99,999,990.000   heading "Max Size|(Mb)" col max_free_mb     format 9,999,990.000    heading "Max Free|(Mb)" col max_used_pct    format 999              heading "Max Used|(%)"   BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT   WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),      tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),      tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),      ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt files,         NVL(s.segcnt,0) segments,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,        ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,        ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_ext_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct   FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = f.tablespace_name(+)    AND d.tablespace_name = m.tablespace_name(+)    AND d.tablespace_name = s.tablespace_name(+)    AND NOT d.contents = 'UNDO'    AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt,        0,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,        ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,        ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct   FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = t.tablespace_name(+)    AND d.tablespace_name = m.tablespace_name(+)    AND d.extent_management = 'LOCAL'    AND d.contents = 'TEMPORARY'   UNION ALL -- UNDO TS SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt,        NVL(s.segcnt,0) segments,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,        ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,        ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 1 /   prompt * Tablespace (TS) types: prompt .  - LM/DM     - Local/Dictionary Managed prompt .  - SYS/UNI   - SYStem/UNIform Extent Management (LM only) prompt .  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only) Reference - Script Ref