How to Apply Database Component Of a Patch Onto ORMB Database

Always make a backup of CISADM schema or whole database before applying database component of a patch onto ORMB database.

Here is an example of applying an ORMB patch onto ORMB Oracle database.  There working environment is as below ;

Oracle Database 12c
Oracle Client 32bit 12c
ORMB 2.5.0.3
JDK 8

Before applying patching, make a backup of database in any way you prefer:

Set the environment variables:

C:\>set TNS_ADMIN=d:\oracle
C:\>set TOOLSBIN=d:\dbpatch_tools\bin
C:\>set JAVA_HOME=d:\java

Ensure to be able to connect to the database from the application server workstation:

C:\>tnsping ormb
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 13-JAN-2017 10:19:11
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
d:\oracle\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ractest-scan.ractest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVIC
E_NAME = ORMB)))
OK (10 msec)

Unzip the patch files, change to the CDXPatch directory and execute the ouafDatabasePatch.cmd utility. The utility will prompt you for the value of the following parameters:

if it prompts you for the target database type, enter O for Oracle.
Enter the username that owns the schema (e.g., CISADM)
The password for the user (in silent mode)(e.g., XXXXXXX)
Enter the name of the Oracle Database Connection String : //ractest-scan.ractest:1521/ORMB

Below is the output from applying patch 25054594 ( with three child patches -24586912, 24604908, 24963398  ).

D:\PATCHES\p25054594_25030\MultiPlatform\V2.5.0.3.0-25054594_MultiPlatform\database\ORACLE\CDXPatch>ouafDatabasePatch.cmd
"CMDLINE::: d:\java\bin\java.exe -cp d:\dbpatch_tools\lib\*;d:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "
Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXXX
Enter the name of the Oracle Database Connection String: //ractest-scan.ractest:1521/ORMB
Target Schema is a Production Schema
Ready to process patches, Do you want to continue? (Y/N): Y
Working Directory: ORMB001
***********************************
Setting up language file: ORMB001\CDXPatch.lang
exit value: 0

Applying 24586912 ...
Writing to log file: ORMB001\log24586912.log
-----------------------------------------------------------
--Applying patch 24586912 at 01-13-2017 14:20:49using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24586912 applied successfully at 01-13-2017 14:21:20
---------------------------------------------------------------

Applying 24604908 ...
Writing to log file: ORMB001\log24604908.log
-----------------------------------------------------------
--Applying patch 24604908 at 01-13-2017 14:21:20using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24604908 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------

Applying 24963398 ...
Writing to log file: ORMB001\log24963398.log
-----------------------------------------------------------
--Applying patch 24963398 at 01-13-2017 14:21:31using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24963398 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------
Patch applied successfully...

Check the successful installations of the patches as per How to Check the Successful Installation of a Database Patch Onto ORMB Database

We can see only the child patches ( 24586912, 24604908, 24963398 ) are stored in repository. The parent patch ID is NOT installed into database patch repository )

SQL>  select sr_no,run_status_flg,END_DTTM 
        from cisadm.ci_ut_instl 
       where sr_no in ( '25054594','24586912','24604908','24963398') 
         and run_status_flg = '02' 
         and END_DTTM IS Not NULL;

SR_NO                          RU END_DTTM
----------------               -- ---------
24586912                       02 13-JAN-17
24604908                       02 13-JAN-17
24963398                       02 13-JAN-17

Finally run Security utility as per How to Run Security Utility in ORMB Multiple Instances Environment

How to Run Security Utility in ORMB Multiple Instances Environment

In ORMB multiple instances environment, run security utility “OraGenSec.exe” by providing non-default information like schema, password, DB connection ,roles,etc.

There are situations  when there are multiple ORMB ( Oracle Revenue Management and Billing ) database instances in one physical database. Normally we run security utility in interactive  mode. It will by default grant permissions to CIS_USER and CIS_READ role.

So for non-default schemas in multiple ORMB database instances environment, we need execute Oragensec by providing command line options, for example ;

d:\..\RMB\Security>OraGenSec.exe -d CISADM_1,PASSWORD,ORMB -u CISUSER_1,CISREAD_1 -r CIS_READ_1
,CIS_USER_1 -a A -l OraGenSec_Security_Log.txt

Generating Security ...

Security privileges and Synonyms generated successfully, Now Exiting ...

d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>dir

05/04/2013 09:31 PM 86 oragensec.bat
05/04/2013 09:31 PM 2,355,284 OraGenSec.exe
11/11/2016 03:30 PM 362,138 security_log.txt

d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>type oragensec.bat
oragensec -d cisadm,cisadm,database-name -r cis_read,cis_user -a A -u cisuser,cisread

CISADM_1 :  Database user that owns the application schema .
PASSWORD:  Password for user CISADM_1.
ORMB: TNS entry in tnsnames.ora.
CISUSER_1:  Database user that has read-write (select, update, insert, and delete) privileges to the objects in the application schema ( CISADM_1). The application will access the database as this user.
CISREAD_1:  Database user with read-only privileges to the objects in the application schema( CISADM_1).
CIS_USER_1:  Database role that has read-write (select, update, insert, and delete) privileges to the objects in the application schema (CISADM_1).
CIS_READ_1:  Database role with read-only privileges to the objects in the application schema (CISADM_1).

capture

Oracle Revenue Management and Billing ( ORMB ) Security Utility OraGenSec

To generate security for ORMB by using “OraGenSec.exe

Here is an example of how to run ORMB 2.5.0.1 security utility — OraGenSec.exe.

1) Make sure database vault must be disabled before running. To check database vault is enabled or not.

SQL>SELECT * 
      FROM V$OPTION 
     WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER              VALUE    CON_ID
---------------------- -------- --------------
Oracle Database Vault  FALSE    0

2) Execute the OraGenSec.exe utility.

d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>set JAVA_HOME=d:\RMB\jdk1.8.0_112
d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>set TNS_ADMIN=D:\oracle


d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>where OraGenSec.exe
d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security\OraGenSec.exe

d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>.\OraGenSec.exe

Enter the application read-only user or Schema Owner in the database: CISADM

Enter the password for the CISADM user:

Enter the name of the Oracle database: ORMB


Enter a comma-separated list of Oracle users in which synonyms need to be created (e.g. cisuser,cisread): CISUSER,CISREAD


Select the following options:

(A/a): Generate security for All objects in the Database?

(O/o): Generate security for specific Objects inputted in this terminal?

(F/f): Generate security for specific objects generated from an input File?
A

You have chosen to generate security for all objects in the database.


Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB

Generating Security ...

Security privileges and Synonyms generated successfully, Now Exiting ...

Press Enter to Continue ...

d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Security>

ORA-39083: Object type TYPE failed to create with error

Using “transform=OID:n” option to avoid ORA-39083 in DataPump.

SYMPTOMS

While importing an ORMB schema from default installation to a new schema for another new ORMB environment, ORA-39083 error occurs:

$impdp directory=DUMP_DIR dumpfile=ORMB.dmp                     \ REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,      \
CISOPR,CISREAD REMAP_SCHEMA=CISADM:CISADM_1, CISUSER:CISUSER_1, \
CISOPR:CISOPR_1,CISREAD:CISREAD_1,CIS_USER:CIS_USER_1, \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log   ... .. . CREATE TYPE "CISADM"."TY_RMB_DELETE_BILL_TAB"   OID '40ACB20E52264074E053530F040AF48D' AS TABLE OF "CISADM"."TY_RMB_DELETE_BILL_DTL" ORA-39083: Object type TYPE failed to create with error: ORA-02304: invalid object identifier literal

SOLUTION

Add “transform=OID:n” as an impdp parameter.

$impdp directory=DUMP_DIR transform=OID:n dumpfile=ORMB.dmp       \
REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,CISOPR, \
CISREAD REMAP_SCHEMA=CISADM:CISADM_1,CISUSER:CISUSER_1, \
CISOPR:CISOPR_1, CISREAD:CISREAD_1,CIS_USER:CIS_USER_1, \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log