Oracle Notes for platform migration from EBS 11i to R12.2.5

1320300.1 - Oracle E-Business Suite Release Notes, Release 12.2
1349240.1 - Database Preparation Guidelines for an Oracle E-Business Suite Release 12.2 Upgrade (choose -> Path B)
1366265.1 - Using Transportable Tablespaces for EBS 11i Using Database 11gR2
1377213.1 - Oracle E-Business Suite - Upgrade & Platform Migration
1448102.2 - R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report
1531121.1 - Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2
1571256.1 - 11i: PRE-Upgrade GDF Diagnostic to Validate Data
1594274.1 - Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes
1617461.1 - Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2
1906873.1 - Can Not Validate CTXSYS.DRIACC Package
1926201.1 - Interoperability Notes Oracle EBS 12.2 with Oracle Database 12c Release 1
1983050.1 - Oracle E-Business Suite Release 12.2.5 Readme
2008451.1 - How To Run The 12.2 EBS Technology Code Level Checker (ETCC)
233044.1  - R11i : About Oracle Applications DBA Minipack 11i.AD.I
252422.1  - R11i / R12 : Requesting Translation Synchronization Patches
396009.1  - Database Initialization Parameters for Oracle E-Business Suite Release 12
874903.1  - R12: What is a Generic Datafix Patch (GDF) and what GDFs are available for Payables?
881505.1  - Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0)
977512.1  - Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST
1493677.1 - Cloning An Existing Oracle Database 12c Release 1 (12.1.0.x) RDBMS Installation Using OUI
1596433.1 - R12.2: How To Create the Stage In Preparation For Installation

Oracle Data Pump Performance Tuning

Commands used

expdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log full=y parallel=16

impdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log parallel=16

During the test we noticed that expdp took 13 minutes and impdp took 45 minutes

using export in parallel to multiple files has significantly reduced the time

expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog parallel=16

impdp username/password directory=dump_dir dumpfile=full%U.dmp logfile=full.log parallel=16

We noticed expdp took 2 minutes now and impdp took same 45 minutes.

After increasing pga_aggregate_target to 6 GB, performance of import came down to 14 minutes.

How to move SQL Profile from one database to another in EBS

1. Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'APPS');

2. Packing the SQL Profiles to the staging table

--Select SQL profiles names available in the source database.
SQL> SELECT name FROM dba_sql_profiles;
NAME
---------
SYS_SQLPROF_012f26136ae90001
SYS_SQLPROF_012f260d3e1a0000
SYS_SQLPROF_012ed90a425b0003
SYS_SQLPROF_012f2622a4620003

SQL>
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f26136ae90001');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f260d3e1a0000');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012ed90a425b0003');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_012f2622a4620003');

3. Export table using “exp” tool from the source database

$ exp APPS/password file=SQL_PROFILES.dmp tables=SQL_PROFILES_TT


4. Import table using “imp” tool into the target database

$ imp SCOTT/password file=SQL_PROFILES.dmp full=y

5. Unpack the SQL Profiles in the target database

SQL> GRANT ADMINISTER SQL MANAGEMENT OBJECT to USER;
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_TT');

SQL query to find file version in EBS

SQL> select * from ad_file_versions where file_id in
(select file_id from ad_files where upper(filename)=upper('appvndrb.pls')
) order by file_version_id desc;

Useful AIX Commands

Show CPU
# prtconf -s
# pmcycles -m
# lsdev -Cc processor
# bindprocessor -q

 Show RAM/Capacity
# prtconf -m
# bootinfo -r
# lsattr -El sys0 -a realmem
# lsattr -El mem0

# vmstat
# getconf REAL_MEMORY

 Show Hard disks/Capacity
# lsdev -Cc disk
# lspv
# bootinfo -s hdisk0

 Show IP Addresses
# ifconfig -a | grep inet
# lsattr -El en0 -a netaddr

Version
uname -a

How to enable database vault in 12.1 oracle RAC database

Verify Database Vault

Use the below query to check if DB Vault is already registered or not.

sqlplus / as sysdba

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> column parameter format a25

SQL> column value format a10

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

PARAMETER                 VALUE

------------------------- ----------

Oracle Label Security     FALSE

Oracle Database Vault     FALSE

Oracle Label Security     FALSE

Oracle Database Vault     FALSE

Note: If the returned value is FALSE, it means DB Vault is not registered and is in disable state.

Enable DB Vault at Container Level

Create DV Admin Users

First, you should create two users. One to administer DB vault and another to manage Oracle users at the database. These two users are required for the separation of duties.

sqlplus / as sysdba

SQL> create user c##dvowner identified by {Pw} CONTAINER=ALL;

User created.

SQL> create user c##dvacctmngr identified by {Pw} CONTAINER=ALL;

User created.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvowner;

Grant succeeded.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvacctmngr;

Grant succeeded.

Configure and Enable Database Vault

Now we can configure DB vault for registration. Then we will compile all the invalid objects.

SQL> BEGIN

DVSYS.CONFIGURE_DV (

dvowner_uname => 'c##dvowner',

dvacctmgr_uname => 'c##dvacctmngr');

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

Compile invalid objects

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

PL/SQL procedure successfully completed.

Enable DB Vault

SQL> connect c##dvowner

Enter password:

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Restart and Validate

Restart the Database. Otherwise, the value will be still FALSE when you query the table.

srvctl stop database -d

srvctl start database -d

DB Vault is ready now at container database. Use the below query for validation.

SQL> column parameter format a25

SQL> column value format a10

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

SQL> SQL>

PARAMETER                 VALUE

------------------------- ----------

Oracle Label Security     TRUE

Oracle Database Vault     TRUE

Oracle Label Security     TRUE

Oracle Database Vault     TRUE

Now proceed to install the DB Vault in pluggable database.

Enable DB Vault at Pluggable Level

Grant Permission for DV Admin User

Give grants to DV Admin users in pluggable database.

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL> show con_name

CON_NAME

------------------------------

PDB1

SQL> grant SET CONTAINER,CREATE SESSION to c##dvowner;

Grant succeeded.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvacctmngr;

Grant succeeded.

Configure and Enable Database Vault

Now we can configure DB vault for registration and compile all the invalid objects.

SQL> BEGIN

2 DVSYS.CONFIGURE_DV (

3 dvowner_uname => ‘c##dvowner’,

4 dvacctmgr_uname => ‘c##dvacctmngr’);

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Compile invalid objects

@?/rdbms/admin/utlrp.sql

PL/SQL procedure successfully completed.

Enable DB Vault for PDB

SQL> connect c##dvowner@PDB1

Enter password:

Connected.

SQL> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete

Restart and Validate

Restart the pluggable Database.

SQL> connect sys@PDB1 as sysdba

Enter password:

Connected.

SQL> alter pluggable database PDB1 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

Note: Bounce the PDB on Node2 as well.

DB Vault is ready now at pluggable database. Use the below query for validation.

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

SQL> SQL>

PARAMETER                 VALUE

------------------------- ----------

Oracle Label Security     TRUE

Oracle Database Vault     TRUE

Oracle Label Security     TRUE

Oracle Database Vault     TRUE

This completes DB Vault installation on pluggable database.

Disable DB Vault

Perform this step on both nodes

SQL> C##DVOWNER/pw

SQL> EXEC DBMS_MACADM.DISABLE_DV;
PL/SQL procedure successfully completed.

SQL> alter session set container=PDB1;
Session altered.

SQL> EXEC DBMS_MACADM.DISABLE_DV;
PL/SQL procedure successfully completed.

Restart the Database

srvctl stop database -d -o immediate

srvctl start database -d

How to use flashback in 12.2 PDB

Enable Flashback

SQL> Archive log list

SQL> SELECT flashback_on FROM v$database;

SQL> alter system set db_recovery_file_dest_size =200G scope=both sid='*';

SQL> show parameter db_recovery;

SQL> col name format a15

SQL> SELECT NAME, TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,

  TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,'999,999,999,999') AS SPACE_AVAILABLE,

  ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> SELECT flashback_on FROM v$database;

Create Restore Points

SQL> ALTER PLUGGABLE DATABASE PSBXPTL1 CLOSE;

SQL> CREATE CLEAN RESTORE POINT pdb_before_changes FOR PLUGGABLE DATABASE PSBXPTL1 GUARANTEE FLASHBACK DATABASE;

Flashback Pluggable Database

SQL> ALTER PLUGGABLE DATABASE PSBXPTL1 CLOSE;

SQL> FLASHBACK PLUGGABLE DATABASE PSBXPTL1 TO RESTORE POINT pdb_before_changes;

SQL> ALTER PLUGGABLE DATABASE PSBXPTL1 OPEN RESETLOGS;

SQL> select GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE, TIME, RESTORE_POINT_TIME, PRESERVED, NAME, PDB_RESTORE_POINT from V$RESTORE_POINT;

SQL> DROP RESTORE POINT pdb_before_changes FOR PLUGGABLE DATABASE PSBXPTL1;