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...
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
Here are the practical steps of installing Oracle Revenue Management and Billing ( ORMB ) 2.5.0.3.0 Database in RAC environment.
Mandatory Software Binaries List
Oracle Database Enterprise Edition 12.1.0.2
Oracle Database Client 32bit - 12.1.0.2
JDK 1.7 or JDK 1.8
7-zip file manager or any other zip tools
Oracle Revenue Management and Billing 2.5.0.3.0 ( p24445559_25030_Generic.zip )
STEP 1: Install Java jdk-8u112-windows-x64
Download and install ( jdk-8u112-windows-x64.zip ). Do not install in default C:\Program Files. It will cause issues later when installing ORMB due to space in path “Program Files”
Otherwise, we can copy “C:\Program Files\Java\jdk1.8.0_112” to “D:\RMB\jdk1.8.0_112” as JAVA_HOME.
Run below sql query to verify the above mandatory software options are enable.
SQL>SELECT COMP_NAME, STATUS FROM DBA_REGISTRY WHERE COMP_NAME IN ('Spatial','Oracle Text');
COMP_NAME STATUS
---------------- --------------------------------------------
Oracle Text VALID
Spatial VALID
Step 5: Create tablesapce
SQL>CREATE BIGFILE TABLESPACE CISTS_01 DATAFILE '+DG1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 1024G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS FOR ADVANCED;
Step 6: Create Users and Roles
SQL> CREATE ROLE CIS_USER;
Role created.
SQL> CREATE ROLE CIS_READ;
Role created.
SQL> CREATE USER CISADM IDENTIFIED BY CISADM DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
User created.
SQL> GRANT UNLIMITED TABLESPACE TO CISADM WITH ADMIN OPTION;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO CISADM;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO CISADM;
Grant succeeded.
SQL> GRANT CONNECT TO CISADM;
Grant succeeded.
SQL> GRANT RESOURCE TO CISADM;
Grant succeeded.
SQL> GRANT DBA TO CISADM WITH ADMIN OPTION;
Grant succeeded.
SQL> GRANT CREATE ANY SYNONYM TO CISADM;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO CISADM;
Grant succeeded.
SQL> CREATE USER CISUSER PROFILE DEFAULT IDENTIFIED BY CISUSER DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT SELECT ANY TABLE TO CISUSER;
Grant succeeded.
SQL> GRANT CIS_USER TO CISUSER;
Grant succeeded.
SQL> GRANT CIS_READ TO CISUSER;
Grant succeeded.
SQL> GRANT CONNECT TO CISUSER;
Grant succeeded.
SQL> CREATE USER CISOPR PROFILE DEFAULT IDENTIFIED BY OPRPLUS DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT CONNECT,RESOURCE,EXP_FULL_DATABASE TO CISOPR;
Grant succeeded.
SQL> CREATE USER CISREAD IDENTIFIED BY CISREAD DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT SELECT ANY TABLE TO CISREAD;
Grant succeeded.
SQL> GRANT CIS_READ TO CISREAD;
Grant succeeded.
SQL> GRANT CONNECT TO CISREAD;
Grant succeeded.
Step 7: Create a Dedicated Service
$ srvctl add service -db RACTEST -service ORMB -preferred "RACTEST1,RACTEST2" -available "RACTEST3,RACTEST4" -tafpolicy BASIC -role primary -failovertype SELECT -failovermethod BASIC -pdb RACTESTPDB -clbgoal LONG -rlbgoal THROUGHPUT
$ srvctl status service -db RACTEST -service ORMB
Service ORMB is not running.
$ srvctl start service -db RACTEST -service ORMB
$ srvctl status service -db RACTEST -service ORMB
Service ORMB is running on instance(s) RACTEST1,RACTEST2
Step 8: Copy and Unzip ORMB 2.5.0.3.0 package.
Copy ORMB 2.5.0.3.0 patch files ( p24445559_25030_Generic.zip ) onto Windows, and unzip it with 7-zip or other tools, the whole the unzipped files will be under “D:\RMB\” as below:
Step 9: Install Oracle Utilities Application Framework Version 4.3.0.1.0
1) Edit Storage.xml file under “D:\RMB\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade” by giving right tablespace name and compression option.
3)Execute the OraDBI.exe utility from the “d:\RMB\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade”.
d:\RMB_PDEV\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade>OraDBI
Enter the name of the target database: ORMB
Enter your database username: CISADM
Enter your password username:
Enter the location for Java Home(e.g. C:\Java\jdk1.6.0_18): D:\java
Enter the TUGBU jarfiles location (e.g.C:\Database-Install\Jarfiles): d:\RMB_PDEV\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\FW\FW43010\jarfiles
Enter the Oracle user with read-write privileges to Database Schema: CISUSER
Enter the Oracle user with read-only privileges to Database Schema: CISREAD
Enter the database role with read-write privileges to Database Schema: CIS_USER
Enter the database role with read-only privileges to Database Schema: CIS_READ
Enter the name of the target Schema where you want to install or upgrade: CISADM
Enter the password for CISADM_PDEV schema (or hit ENTER to quit):
Re-enter the value:
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
WARNING: 2 sessions are connected to the target database, Please make sure you close them before pressing a key continue !!!
Ready to perform initial install of Database Rel.V4.3.0.1.0, do you want to continue (Y/N)? Y
Executing Database Setup Scripts...
Processing file FW_CDX_BP_CRTLOG.sql ...
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
Executing Initialization Scripts...
Processing file FW400_bpschema3.sql ...
Processing file FW400_bpschema4.sql ...
Processing file FW400_bpschema5.sql ...
Processing file FW410_bpschema7.sql ...
Processing file FW410_bpschema8.sql ...
Processing file FW410_bpschema9.sql ...
Processing file FW410_bpschema10.sql ...
Processing file FW410_bpschema11.sql ...
Processing file FW410_bpschema12.sql ...
Processing file FW410_bpschema13.sql ...
Processing file FW420_bpschema1.sql ...
Processing file FW420_bpschema2.sql ...
Processing file FW420_bpschema3.sql ...
Processing file FW420_bpschema4.sql ...
Processing file FW42020_bpschema1.sql ...
Performing Schema Upgrade...
Running the Schema Upgrade process in the Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Loading Blueprint
Loading the schema information from the target database
Creating the missing tables
Upgrading the columns
..
.
Setting Environment ID...
******************************************
Executing the Environment ID Setup Process
******************************************
Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Environment ID set to 405852
Environment ID Setup Process completed successfully, Now Exiting ...
Auto Delete Upgrade...
Running Auto System Data Deletion process in Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Ready to upgrade the target database, Do you want to continue? (Y/N) Y
Reading Blueprint
Loading CI_ALG_TYPE from the blueprint
Loading CI_ALG_TYPE from the target database
......
.....
....
...
..
.
Processing file FW43010_postData1.sql ...
Processing file FW43010_postData2.sql ...
Processing file FW_cleanup.sql ...
Populating Log for Single-fixes...
******************************************
Executing the Single-Fix logging process
******************************************
Connecting to the ORMB as a CISADM user ...
Setting Current Schema to CISADM
Single-fix logging process completed successfully, Now Exiting ...
Configuring Security...
Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB
Generating security ...
Security privileges and Synonyms generated successfully, Now Exiting ...
Storing Database Credential with ecncrypted password...
Install/Upgrade of Database Rel.V4.3.0.1.0 Completed Sucessfully, Now Exiting ...
Press Enter to Continue ...
4) Check the objects and their status, which matches the installation guide
7. Change to the Database directory using the following command:
d:\dbpatch_tools\bin>tnsping ormb
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 07-NOV-2016 09:40:47
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.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVIC
E_NAME = ORMB)))
OK (20 msec)
d:\dbpatch_tools\bin>cd D:\RMB_PDEV\RMB-V2.5.0.3.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\databas
Before executing ouafDatabasePatch utility, we need add “USE_SID_AS_SERVICE_listener=on” into GI listener.ora and reload listener to make it effective. Because ouafDatabasePatch utility requires ORACLE_SID as input parameter. For PDB, “USE_SID_AS_SERVICE_listener=on” parameter will take ORACLE_SID as a service.
D:\RMB_PDEV\RMB-V2.5.0.3.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>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:
Enter the name of the Oracle Database Connection String: //RACTEST-SCAN.ractest.local: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 21908287 ...
Writing to log file: ORMB001\log21908287.log
-----------------------------------------------------------
--Applying patch 21908287 at 11-25-2016 09:20:26using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21908287 applied successfully at 11-25-2016 09:20:27
---------------------------------------------------------------
...
..
.
Applying 22619484 ...
Writing to log file: ORMB001\log22619484.log
-----------------------------------------------------------
--Applying patch 22619484 at 11-25-2016 09:20:47using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 22619484 applied successfully at 11-25-2016 09:20:54
---------------------------------------------------------------
Patch applied successfully..
D:\RMB_PDEV\RMB-V2.5.0.3.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>
Check the objects and their status, which matches the installation guide.
SQL> select owner,object_type, status,count(*) from dba_objects where owner like 'CIS%' group by owner,object_type, status order by 1,2;
OWNER OBJECT_TYPE STATUS COUNT(*)
-----------------------------------
CISADM INDEX VALID 700
CISADM LOB VALID 47
CISADM SEQUENCE VALID 4
CISADM TABLE VALID 502
CISADM TRIGGER VALID 1
CISADM VIEW VALID 8
CISREAD SYNONYM VALID 514
CISUSER SYNONYM VALID 514
8 rows selected.
Step 11: install Oracle Revenue Management and Billing Version 2.5.0.3.0
1. Execute the CdxDBI utility from “D:\RMB\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade” directory.
Note: The TEMPDIR folder is the location where you have extracted the contents of the RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform.zip file. Ensure that you execute the CdxDBI utility from the Window 32-bit or 64-bit desktop that has Oracle Database Client 12.1.0.2 (32-bit) and Java Development Kit Version 7.0 installed. The database must be listed in the tnsnames.ora file on your local machine.
D:\RMB_PDEV\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade>oradbi
Enter the name of the target database: ORMB
Enter the name of the owner of Database Schema: CISADM
Enter the location for Java Home(e.g. C:\Java\jdk1.6.0_18): D:\RMB\jdk1.8.0_112
Enter the TUGBU jarfiles location (e.g.C:\Database-Install\Jarfiles): D:\RMB\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\RMB\jarfiles
Enter the password for CISADM schema (or hit ENTER to quit):
Re-enter the password:
Enter the Oracle user with read-write privileges to Database Schema: CISUSER
Enter the Oracle user with read-only privileges to Database Schema: CISREAD
Enter the database role with read-write privileges to Database Schema: CIS_USER
Enter the database role with read-only privileges to Database Schema: CIS_READ
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
WARNING: A session is connected to the target database, Please make sure you close it before pressing a key continue !!!
Ready to perform initial install of Database Rel.V2.5.0.3.0, do you want to continue (Y/N)? Y
Processing file FN_GET_UNPAID_BILL_PRD_AMT.sql ...
Processing file DROP_COLUMN_PRICELIST_ELIG_ID.sql ...
Processing file alter_primary_key_CI_BILL_ACH.sql ...
Processing file C1_AlgorithmSoftParameter.sql ...
Configuring Security...
Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB
Generating security ...
Security privileges and Synonyms generated successfully, Now Exiting ...
Storing Database Credential with ecncrypted password...
Install/Upgrade of Database Rel.V2.5.0.3.0 Completed Sucessfully, Now Exiting ...
Press Enter to Continue ...
D:\RMB_PDEV\RMB-V2.5.0.3.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade>
Check the objects and their status. There is a trigger and a function are INVALID.
SQL> select owner, object_type, status,count(*) from dba_objects where owner like 'CIS%' group by owner, object_type, status order by 1,2,3;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------ ----------------------- ------- ----------
CISADM FUNCTION INVALID 1
CISADM FUNCTION VALID 22
CISADM INDEX VALID 2755
CISADM LOB VALID 161
CISADM PACKAGE VALID 4
CISADM PACKAGE BODY VALID 4
CISADM PROCEDURE VALID 1
CISADM SEQUENCE VALID 27
CISADM TABLE VALID 1831
CISADM TRIGGER INVALID 1
CISADM TYPE VALID 8
CISADM TYPE BODY VALID 1
CISADM VIEW VALID 155
STEP 12: Move Four Indexes onto Right Tablespace
If none default tablespace name “CISADM” is used instead of default “CIST_01” tablespace name, there are four indexes are created onto “CIST_01” for some reason.
SQL> select distinct owner, tablespace_name from dba_segments where owner='CISADM';
OWNER TABLESPACE_NAME
------------------ ------------------------------
CISADM CISTS_01
CISADM CISADM
SQL> select segment_type, segment_Name from dba_segments where owner='CISADM_PDEV' and TABLESPACE_NAME='CISTS_01';
SEGMENT_TYPE SEGMENT_NAME
------------------ --------------------------------------------------------------------------------------------------------------------------------
INDEX XM172S2
INDEX XF028S1
INDEX XF013S1
INDEX XF165S1
SQL> alter index CISADM.XM172S2 rebuild tablespace CISADM;
Index altered.
SQL> alter index CISADM.XF028S1 rebuild tablespace CISADM;
Index altered.
SQL> alter index CISADM.XF013S1 rebuild tablespace CISADM;
Index altered.
SQL> alter index CISADM.XF165S1 rebuild tablespace CISADM;
Index altered.
SQL> select distinct owner, tablespace_name from dba_segments where owner='CISADM';
OWNER TABLESPACE_NAME
------------------ ------------------------------
CISADM CISADM
STEP 13: Post Installation Tasks ( Transaction Feed Management Feature Not Used )
Apply the 24516656 patch.
d:\PATCHES\p24516656_25030\MultiPlatform\V2.5.0.3.0-24516656_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:
Enter the name of the Oracle Database Connection String://RACTEST-SCAN.ractest.local: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 24516656 ...
Enter a comma-separated list of Oracle users in which synonyms need to be created (e.g. CISUSER,CISREAD): CISUSER,CISREAD
Writing to log file: ORMB001\log24516656.log
-----------------------------------------------------------
--Applying patch 24516656 at 11-25-2016 13:38:40using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24516656 applied successfully at 11-25-2016 13:39:47
---------------------------------------------------------------
Patch applied successfully..
Enter a database role which has a read-write privilege (e.g. CIS_USER): CIS_USER
Enter a database role which has a read only privilege (e.g. CIS_READ): CIS_READ
Generating Security ...
-----------------------------------------------------------
--Security is successfully applied at 11-25-2016 13:44:18
--------------------------------------------------------------
d:\PATCHES\p24516656_25030_PDEV\MultiPlatform\V2.5.0.3.0-24516656_MultiPlatform\database\ORACLE\CDXPatch>
2. Enable USER_LOCK Package
SQL> show con_name
CON_NAME
------------------------------
RACTESTPDB
SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/userlock.sql
drop package user_lock
*
ERROR at line 1:
ORA-04043: object USER_LOCK does not exist
Package created.
Package body created.
Synonym created.
Grant succeeded.
SQL> grant execute on USER_LOCK to public;
Grant succeeded.
3. Grant Permissions to the DBMS_LOCK Package and Recompile Database Objects
SQL> grant EXECUTE, DEBUG on DBMS_LOCK to cisadm;
Grant succeeded.
SQL> @?/rdbms/admin/utlrp.sql;
TIMESTAMP
-----------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-11-07 12:30:23
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-11-07 12:30:28
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
ouafDatabasePatch.cmd requires Instance Name ( SID ) instead of Service Name.
SYMPTOMS
When installing Rollup Pack for Oracle Utilities Application Framework Version 4.3.0.1.0, ran ouafDatabasePatch.cmd command , and got the following errors:
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set TOOLSBIN=D:\dbpatch_tools\bin
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=D:\java
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: D:\java\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\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: XXXXXX
Enter the name of the Oracle Database Connection String: ractest-scan.ractest.local:1521:ORMB
Couldn't connect to database ORACLE ractest-scan.ractest.local:1521:ORMB CISADM : java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
In connection string “ractest-scan.ractest.local:1521:ORMB”, ORMB is a service name instead of instance name.
SOLUTION
1)Apply patch 22505470: PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DBSERVICE CONNECTION SUPPORT.
2) Replace D:\dbpatch_tools\lib with new ones.
OR
2) Add below parameter into LISTENER.ora , and bounce/reload the listener.
step by step installing Oracle Revenue Management and Billing ( ORMB ) 2.5.0.1.0 in RAC database
This post demonstrates the steps of installing Oracle Revenue Management and Billing ( ORMB ) 2.5.0.1.0 Database in RAC environment.
Mandatory Software Binaries List
Oracle Database Enterprise Edition 12.1.0.2
Oracle Database Client 32bit - 12.1.0.2
JDK 1.7 or JDK 1.8
7-zip file manager or any other zip tools
Oracle Revenue Management and Billing 2.5.0.1.0 ( p22480614_25010_Generic.zip )
STEP 1: Install Java jdk-8u112-windows-x64
Download and install ( jdk-8u112-windows-x64.zip ). Do not install in default C:\Program Files. It will cause issues later when installing ORMB due to space in path “Program Files”
Otherwise, we can copy “C:\Program Files\Java\jdk1.8.0_112” to “D:\RMB\jdk1.8.0_112” as JAVA_HOME.
Run below sql query to verify the above mandatory software options are enable.
SQL>SELECT COMP_NAME, STATUS
FROM DBA_REGISTRY
WHERE COMP_NAME IN ('Spatial','Oracle Text');
COMP_NAME STATUS
------------ -----------
Oracle Text VALID
Spatial VALID
Step 5: Create Tablesapce
SQL>CREATE BIGFILE TABLESPACE CISTS_01 DATAFILE '+DG1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 1024G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS FOR ADVANCED;
Step 6: Create Users and Roles
SQL> CREATE ROLE CIS_USER;
Role created.
SQL> CREATE ROLE CIS_READ;
Role created.
SQL> CREATE USER CISADM IDENTIFIED BY CISADM DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
User created.
SQL> GRANT UNLIMITED TABLESPACE TO CISADM WITH ADMIN OPTION;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO CISADM;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO CISADM;
Grant succeeded.
SQL> GRANT CONNECT TO CISADM;
Grant succeeded.
SQL> GRANT RESOURCE TO CISADM;
Grant succeeded.
SQL> GRANT DBA TO CISADM WITH ADMIN OPTION;
Grant succeeded.
SQL> GRANT CREATE ANY SYNONYM TO CISADM;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO CISADM;
Grant succeeded.
SQL> CREATE USER CISUSER PROFILE DEFAULT IDENTIFIED BY CISUSER DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT SELECT ANY TABLE TO CISUSER;
Grant succeeded.
SQL> GRANT CIS_USER TO CISUSER;
Grant succeeded.
SQL> GRANT CIS_READ TO CISUSER;
Grant succeeded.
SQL> GRANT CONNECT TO CISUSER;
Grant succeeded.
SQL> CREATE USER CISOPR PROFILE DEFAULT IDENTIFIED BY OPRPLUS DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT CONNECT,RESOURCE,EXP_FULL_DATABASE TO CISOPR;
Grant succeeded.
SQL> CREATE USER CISREAD IDENTIFIED BY CISREAD DEFAULT TABLESPACE CISTS_01 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT SELECT ANY TABLE TO CISREAD;
Grant succeeded.
SQL> GRANT CIS_READ TO CISREAD;
Grant succeeded.
SQL> GRANT CONNECT TO CISREAD;
Grant succeeded.
Step 7: Create a Dedicated Service
$ srvctl add service -db RACTEST -service ORMB -preferred "RACTEST1,RACTEST2" -available "RACTEST3,RACTEST4" -tafpolicy BASIC -role primary -failovertype SELECT -failovermethod BASIC -pdb RACTESTPDB -clbgoal LONG -rlbgoal THROUGHPUT
$ srvctl status service -db RACTEST -service ORMB
Service ORMB is not running.
$ srvctl start service -db RACTEST -service ORMB
$ srvctl status service -db RACTEST -service ORMB
Service ORMB is running on instance(s) RACTEST1,RACTEST2
Step 8: Copy and Unzip ORMB 2.5.0.1.0 package.
Copy ORMB ) 2.5.0.1.0 patch files ( p22480614_25010_Generic.zip ) onto Windows, and unzip it with 7-zip or other tools, the whole the unzipped files will be under “D:\RMB\” as below:
Step 9: Install Oracle Utilities Application Framework Version 4.3.0.1.0
1) Edit Storage.xml file under “D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade” by giving right tablespace name and compression option.
3)Execute the OraDBI.exe utility from the “d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade”.
d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade>.\oradbi
Enter the name of the target database: ORMB
Enter your database username: CISADM
Enter your password username:
Enter the location for Java Home(e.g. C:\Java\jdk1.6.0_18): D:\RMB\jdk1.8.0_112
Enter the TUGBU jarfiles location (e.g.C:\Database-Install\Jarfiles): D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\FW\FW43010\jarfiles
Enter the Oracle user with read-write privileges to Database Schema: CISUSER
Enter the Oracle user with read-only privileges to Database Schema: CISREAD
Enter the database role with read-write privileges to Database Schema: CIS_USER
Enter the database role with read-only privileges to Database Schema: CIS_READ
Enter the name of the target Schema where you want to install or upgrade: CISADM
Enter the password for CISADM schema (or hit ENTER to quit):
Re-enter the value:
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
WARNING: 2 sessions are connected to the target database, Please make sure you close them before pressing a key continue !!!
Ready to perform initial install of Database Rel.V4.3.0.1.0, do you want to continue (Y/N)? Y
Executing Database Setup Scripts...
Processing file FW_CDX_BP_CRTLOG.sql ...
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
Executing Initialization Scripts...
Processing file FW400_bpschema3.sql ...
Processing file FW400_bpschema4.sql ...
Processing file FW400_bpschema5.sql ...
Processing file FW410_bpschema7.sql ...
Processing file FW410_bpschema8.sql ...
Processing file FW410_bpschema9.sql ...
Processing file FW410_bpschema10.sql ...
Processing file FW410_bpschema11.sql ...
Processing file FW410_bpschema12.sql ...
Processing file FW410_bpschema13.sql ...
Processing file FW420_bpschema1.sql ...
Processing file FW420_bpschema2.sql ...
Processing file FW420_bpschema3.sql ...
Processing file FW420_bpschema4.sql ...
Processing file FW42020_bpschema1.sql ...
Performing Schema Upgrade...
Running the Schema Upgrade process in the Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Loading Blueprint
Loading the schema information from the target database
Creating the missing tables
Upgrading the columns
Upgrading indexes
Upgrading Primary Keys
Upgrading the sequences
Upgrading the views
Upgrading the triggers
CREATE OR REPLACE TRIGGER "CIFKVALID"
BEFORE INSERT ON CI_VAL_FK_ERR
FOR EACH ROW
BEGIN
SELECT CIFKVALSEQ.NextVal INTO :new.VAL_FK_ERR_ID FROM DUAL;
END CIFKVALID;
Schema Upgraded Successfully, Now Exiting
Executing Intermediate Scripts...
Processing file FW400_bpschema1.sql ...
Processing file FW400_bpschema2.sql ...
..
.
Processing file FW420_apData8.sql ...
Processing file FW4301_apData1.sql ...
Processing file FW_AP_INIDATA.SQL ...
Setting Environment ID...
******************************************
Executing the Environment ID Setup Process
******************************************
Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Environment ID set to 787230
Environment ID Setup Process completed successfully, Now Exiting ...
Auto Delete Upgrade...
Running Auto System Data Deletion process in Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Ready to upgrade the target database, Do you want to continue? (Y/N) Y
Reading Blueprint
Loading CI_ALG_TYPE from the blueprint
Loading CI_ALG_TYPE from the target database
Comparing CI_ALG_TYPE
...
..
.
Comparing CI_XAI_EXECUTER_L
Upgrading Database
Pass:1
Auto System Data Deletion Process Executed Successfully, Now Exiting
Performing English System Data Upgrade...
Running System Data Upgrade process in Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Reading Blueprint
Loading CI_CURRENCY_CD from the blueprint
Loading CI_CURRENCY_CD from the target database
Comparing CI_CURRENCY_CD
...
..
.
Comparing CI_XAI_EXECUTER_L
Upgrading Database
Pass:1
System Data Upgraded Successfully, Now Exiting
Executing Finalization Scripts...
Processing file FW400_postData1.sql ...
Processing file FW400_postData2.sql ...
...
..
.
Processing file FW43010_postData2.sql ...
Processing file FW_cleanup.sql ...
Populating Log for Single-fixes...
******************************************
Executing the Single-Fix logging process
******************************************
Connecting to the ORMB as a CISADM user ...
Setting Current Schema to CISADM
Single-fix logging process completed successfully, Now Exiting ...
Configuring Security...
Connecting to the Target Database ...
User Name : CISADM
Database Name : ORMB
Generating security ...
Security privileges and Synonyms generated successfully, Now Exiting ...
Storing Database Credential with ecncrypted password...
Install/Upgrade of Database Rel.V4.3.0.1.0 Completed Sucessfully, Now Exiting ...
Press Enter to Continue ...
d:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\FW\FW43010\Install-Upgrade>
4) Check the objects and their status, which matches the installation guide
7. Change to the Database directory using the following command:
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\bin>tnsping ormb
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 07-NOV-2016 09:40:47
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.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVIC
E_NAME = ORMB)))
OK (20 msec)
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\bin>cd ../.
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools>cd ../
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform>cd FW-V4.3.0.1.0-Rollup\Database
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>
8. Execute the ouafDatabasePatch utility.
Before executing ouafDatabasePatch utility, we need add “USE_SID_AS_SERVICE_listener=on” into GI listener.ora and reload listener to make it effective. Because ouafDatabasePatch utility requires ORACLE_SID as input parameter. For PDB, “USE_SID_AS_SERVICE_listener=on” parameter will take ORACLE_SID as a service.
$grep -i use listener.ora
USE_SID_AS_SERVICE_listener=on
$lsnrctl reload
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
JAVA_HOME is not set. You need to set JAVA_HOME
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_112
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
The system cannot find the path specified.
"CMDLINE::: C:\Program Files\Java\jdk1.8.0_112\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PRERE
Q-MultiPlatform\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME="C:\Program Files\Java\jdk1.8.0_112"
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
Files\Java\jdk1.8.0_112"" was unexpected at this time.
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=C:\"Program Files"\Java\jdk1.8.0_112
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
Files"\Java\jdk1.8.0_112" was unexpected at this time.
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=d:\RMB\jdk1.8.0_112
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: d:\RMB\jdk1.8.0_112\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform
\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:
Enter the name of the Oracle Database Connection String: ORMBConnection string is invalid format should be dbhost:dbport:dbsid
usage: java -cp OUAFPatch.jar [-c] [-d <arg>] [-h] [-i] [-l <arg>] [-n] [-o
<arg>] [-p <arg>] [-q] [-r] [-s] [-t <arg>] [-u <arg>] [-v]
DB Patch Help
-c Consolidated Rollup/Service Pack
-d <arg> Database connect information for the target database.
-h help
-i Ignore all the error generated while executing upgrade scripts.
-l <arg> Name of the output log file. Default name is logcdxpatch.txt
-n Do not Generate Security. Default always generate security (e.g
synonyms and grants).
-o <arg> Optional. Names of database roles with read and read-write
privs. Default roles are CIS_READ,CIS_USER.
-p <arg> Name of the file containing the list of patches. Default name is
cdxpatch.ini.
-q Silent mode.
-r Reapply the patch.
-s Applying patch on Development database only.
-t <arg> Target database type: O - Oracle, D - DB2 and M - MSSQL.
-u <arg> A comma-separated list of database users where synonyms need to
be created
-v Print version information.
End of DB Patch Help
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: d:\RMB\jdk1.8.0_112\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform
\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "
Enter the target database type (O/M/D) [O]:
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user:
Enter the name of the Oracle Database Connection String: RACTEST-SCAN.ractest.local:1521:RACTESTPDB
Target Schema is a Production Schema
Ready to process patches, Do you want to continue? (Y/N): Y
Working Directory: RACTESTPDB001
***********************************
Setting up language file: RACTESTPDB001\CDXPatch.lang
exit value: 0
Applying 21908287 ...
Writing to log file: RACTESTPDB001\log21908287.log
-----------------------------------------------------------
--Applying patch 21908287 at 11-07-2016 10:32:30
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21908287 applied successfully at 11-07-2016 10:32:30
---------------------------------------------------------------
Applying 21196697 ...
Writing to log file: RACTESTPDB001\log21196697.log
-----------------------------------------------------------
--Applying patch 21196697 at 11-07-2016 10:32:30
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21196697 applied successfully at 11-07-2016 10:32:30
---------------------------------------------------------------
Applying 21792146 ...
Writing to log file: RACTESTPDB001\log21792146.log
-----------------------------------------------------------
--Applying patch 21792146 at 11-07-2016 10:32:30
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21792146 applied successfully at 11-07-2016 10:32:32
---------------------------------------------------------------
Applying 22070400 ...
Writing to log file: RACTESTPDB001\log22070400.log
-----------------------------------------------------------
--Applying patch 22070400 at 11-07-2016 10:32:32
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 22070400 applied successfully at 11-07-2016 10:32:32
---------------------------------------------------------------
Applying 22084304 ...
Writing to log file: RACTESTPDB001\log22084304.log
-----------------------------------------------------------
--Applying patch 22084304 at 11-07-2016 10:32:32
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 22084304 applied successfully at 11-07-2016 10:32:32
---------------------------------------------------------------
Applying 21647369 ...
Writing to log file: RACTESTPDB001\log21647369.log
-----------------------------------------------------------
--Applying patch 21647369 at 11-07-2016 10:32:32
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21647369 applied successfully at 11-07-2016 10:32:32
---------------------------------------------------------------
Applying 19075673 ...
Writing to log file: RACTESTPDB001\log19075673.log
-----------------------------------------------------------
--Applying patch 19075673 at 11-07-2016 10:32:32
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 19075673 applied successfully at 11-07-2016 10:32:33
---------------------------------------------------------------
Applying 21748264 ...
Writing to log file: RACTESTPDB001\log21748264.log
-----------------------------------------------------------
--Applying patch 21748264 at 11-07-2016 10:32:33
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21748264 applied successfully at 11-07-2016 10:32:34
---------------------------------------------------------------
Applying 22194261 ...
Writing to log file: RACTESTPDB001\log22194261.log
-----------------------------------------------------------
--Applying patch 22194261 at 11-07-2016 10:32:34
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 22194261 applied successfully at 11-07-2016 10:32:34
---------------------------------------------------------------
Applying 21779770 ...
Writing to log file: RACTESTPDB001\log21779770.log
-----------------------------------------------------------
--Applying patch 21779770 at 11-07-2016 10:32:34
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 21779770 applied successfully at 11-07-2016 10:32:35
---------------------------------------------------------------
Applying 22013062 ...
Writing to log file: RACTESTPDB001\log22013062.log
-----------------------------------------------------------
--Applying patch 22013062 at 11-07-2016 10:32:35
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 22013062 applied successfully at 11-07-2016 10:32:37
---------------------------------------------------------------
Patch applied successfully..
Check the objects and their status, which matches the installation guide.
SQL> select owner,object_type, status,count(*)
from dba_objects
where owner like 'CIS%'
group by owner,object_type, status
order by 1,2;
OWNER OBJECT_TYPE STATUS COUNT(*)
-----------------------------------
CISADM INDEX VALID 700
CISADM LOB VALID 47
CISADM SEQUENCE VALID 4
CISADM TABLE VALID 502
CISADM TRIGGER VALID 1
CISADM VIEW VALID 8
CISREAD SYNONYM VALID 514
CISUSER SYNONYM VALID 514
8 rows selected.
Step 11: install Oracle Revenue Management and Billing Version 2.5.0.1.0
1. Execute the CdxDBI utility from “D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade” directory.
Note: The TEMPDIR folder is the location where you have extracted the contents of the RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform.zip file.
Ensure that you execute the CdxDBI utility from the Window 32-bit or 64-bit desktop that has Oracle Database Client 12.1.0.2 (32-bit) and Java Development Kit Version 7.0 installed. The database must be listed in the tnsnames.ora file on your local machine.
D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade>.\CdxDBI
Enter the name of the target database: ORMB
Enter the name of the owner of Database Schema: CISADM
Enter the location for Java Home(e.g. C:\Java\jdk1.6.0_18): D:\RMB\jdk1.8.0_112
Enter the TUGBU jarfiles location (e.g.C:\Database-Install\Jarfiles): D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\jarfiles
Enter the password for CISADM schema (or hit ENTER to quit):
Re-enter the password:
Enter the Oracle user with read-write privileges to Database Schema: CISUSER
Enter the Oracle user with read-only privileges to Database Schema: CISREAD
Enter the database role with read-write privileges to Database Schema: CIS_USER
Enter the database role with read-only privileges to Database Schema: CIS_READ
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
WARNING: A session is connected to the target database, Please make sure you close it before pressing a key continue !!!
Ready to perform initial install of Database Rel.V2.5.0.1.0, do you want to continue (Y/N)? Y
Executing Database Setup Scripts...
Processing file cdx_bp_crtlog.sql ...
Connecting to the Target Database as user CISADM
User Name : CISADM
Database Name : ORMB
Executing Initialization Scripts...
Processing file txn_table_schema_change_V4SP2.sql ...
Processing file drop_indexes_ci_txn_dtl_v4sp2_hotfix.sql ...
Processing file drop_column_priceitem_cd.sql ...
Processing file drop_CI_FTTEMP.sql ...
Processing file PreAllServicesUpdate.sql ...
Performing Schema Upgrade for RMB...
Running the Schema Upgrade process in the Modification Mode
Connecting to the Target Database
User Name : CISADM
Database Name : ORMB
Schema Name : CISADM
Setting Current Schema to CISADM
Loading Blueprint
Loading the schema information from the target database
Creating the missing tables
Upgrading the columns
Upgrading indexes
Upgrading Primary Keys
Upgrading the sequences
DBD::Oracle::db do warning: ORA-24344: success with compilation error (DBD SUCCESS_WITH_INFO: error possibly near <*> indicator at char 406 in 'CREATE OR REPLAC
E FORCE VIEW CI_CC_ACCT_HST_VW (ACCT_ID,LANGUAGE_CD,ACTIVITY_ID,ACTIVITY_ID2,ACTIVITY_DTTM,ACT_TYPE_FLG,ACT_DESCR,SORT_DTTM) AS SELECT
AP.ACCT_ID
,LU.LANGUAGE_CD
,CC.CC_ID
,AP.PER_ID
,CC.CC_DTTM
,LU.FIELD_VALUE
,LU.DESCR
,TO_CHAR(CC.CC_DTTM,'YYYY-MM-DD-HH24.MI.SS') ||'.000000'
FROM CI_CC CC
, CI_ACCT_PER AP
, <*>CI_PER_PRIM_VW PN
, CI_LOOKUP LU
WHERE PN.PER_ID = AP.PER_ID
AND AP.MAIN_CUST_SW = 'Y'
AND AP.PER_ID = CC.PER_ID
AND LU.FIELD_NAME = 'ACT_TYPE_FLG'
AND LU.FIELD_VALUE = 'CC'
WITH READ ONLY
') [for Statement "CREATE OR REPLACE FORCE VIEW CI_CC_ACCT_HST_VW (ACCT_ID,LANGUAGE_CD,ACTIVITY_ID,ACTIVITY_ID2,ACTIVITY_DTTM,ACT_TYPE_FLG,ACT_DESCR,SORT_DTTM)
AS SELECT
AP.ACCT_ID
,LU.LANGUAGE_CD
,CC.CC_ID
,AP.PER_ID
,CC.CC_DTTM
,LU.FIELD_VALUE
,LU.DESCR
,TO_CHAR(CC.CC_DTTM,'YYYY-MM-DD-HH24.MI.SS') ||'.000000'
FROM CI_CC CC
, CI_ACCT_PER AP
, CI_PER_PRIM_VW PN
, CI_LOOKUP LU
WHERE PN.PER_ID = AP.PER_ID
AND AP.MAIN_CUST_SW = 'Y'
AND AP.PER_ID = CC.PER_ID
AND LU.FIELD_NAME = 'ACT_TYPE_FLG'
AND LU.FIELD_VALUE = 'CC'
WITH READ ONLY
"] at OraSchUpg.plx line 1933.
DBD::Oracle::db do warning: ORA-24344: success with compilation error (DBD SUCCESS_WITH_INFO: error possibly near <*> indicator at char 332 in 'CREATE OR REPLAC
E FORCE VIEW CI_PRC_AC_PER_VW (ACCT_ID,PER_ID2,PER_ID1,PER_LEVEL,REL_PATH,START_DT,END_DT,PARTY_UID,PARTY_TYPE_FLG) AS SELECT ap.acct_id,
cpp.per_id2,
cpp.per_id1,
cpp.per_level,
cpp.rel_path,
cpp.start_dt,
cpp.end_dt,
pty.party_uid,
pty.party_type_flg
FROM <*>ci_prc_per_per_vw cpp,
ci_acct_per ap,
ci_party pty
WHERE cpp.per_id2 = ap.per_id
AND (cpp.start_dt IS NULL OR
cpp.start_dt <= nvl(cpp.end_dt,
cpp.start_dt))
,,,,,,
,,,,,
,,,,
,,,
,,
,
END IF;
EXCEPTION
WHEN OTHERS THEN
If errorMsg IS NULL THEN
errorMsg := 'Indentify/Insert failed in prc_insertAffectedEntities at ' ||
l_Status || ' with error : ' || SQLERRM;
END IF;
PO_EXEC_STATUS := errorMsg;
end;
--begin
-- Initialization
--< Statement >;
end PKG_RMB_TFM_DISAGG;
"] at CdxDBI.plx line 1499, <SQLFILE> line 1433.
Populating Log for Single-fixes...
******************************************
Executing the Single-Fix logging process
******************************************
Connecting to the ORMB as a CISADM user ...
Single-fix logging process completed successfully, Now Exiting ...
Configuring Security...
Generating Security ...
Security privileges and Synonyms generated successfully, Now Exiting ...
Storing Database Credential with ecncrypted password...
Install/Upgrade of Database Rel.V2.5.0.1.0 Completed Sucessfully, Now Exiting ...
Press Enter to Continue ...
D:\RMB\RMB-V2.5.0.1.0-Oracle-Database-MultiPlatform\RMB\Upgrade\Oracle\Install-Upgrade>
Check the objects and their status. There are two sequences missing. One of them causes only one trigger INVALID.
OWNER OBJECT_TYPE STATUS COUNT(*)
--------------- ----------------------- ------- ----------
CISADM FUNCTION VALID 16
CISADM INDEX VALID 2604
CISADM LOB VALID 146
CISADM PACKAGE VALID 4
CISADM PACKAGE BODY VALID 4
CISADM PROCEDURE VALID 1
CISADM SEQUENCE VALID 24
CISADM TABLE VALID 1755
CISADM TRIGGER INVALID 1
CISADM TYPE VALID 8
CISADM TYPE BODY VALID 1
CISADM VIEW VALID 154
There are 2 sequences missing. It is supposed to have 26 sequences according to installation guide.
The only one trigger is INVALID. Checked the definition, it is related to the sequences missing :
SQL> alter trigger CISADM.CIFKVALID compile;
Warning: Trigger altered with compilation errors.
SQL> show errors
Errors for TRIGGER CISADM.CIFKVALID:
LINE/COL ERROR
———————————————————- 2/7 PL/SQL: SQL Statement ignored 2/14 PL/SQL: ORA-02289: sequence does not exist
TRIGGER_BODY ———————————————————- BEGIN SELECT CIFKVALSEQ.NextVal INTO :new.VAL_FK_ERR_ID FROM DUAL; END CIFKVALID;
STEP 12: Post Installation Tasks
1.Enable USER_LOCK Package
SQL> show con_name
CON_NAME
------------------------------
RACTESTPDB
SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/userlock.sql
drop package user_lock
*
ERROR at line 1:
ORA-04043: object USER_LOCK does not exist
Package created.
Package body created.
Synonym created.
Grant succeeded.
SQL> grant execute on USER_LOCK to public;
Grant succeeded.
2. Increase INITRANS Values
SQL> ALTER TABLE CISADM.CI_BCHG_SQ INITRANS 20;
Table altered.
SQL> ALTER INDEX CISADM.XT081P0 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.IDX_SQ INITRANS 40;
ALTER INDEX CISADM.IDX_SQ INITRANS 40
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> ALTER TABLE CISADM.CI_BILL_CHG INITRANS 20;
Table altered.
SQL> ALTER INDEX CISADM.XT035P0 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.XT035S1 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.XT035S2 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.XT035S3 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.XT035S3 INITRANS 40;
Index altered.
SQL> ALTER INDEX CISADM.XT035S4 INITRANS 40;
Index altered.
3. Grant Permissions to the DBMS_LOCK Package and Recompile Database Objects.
SQL> grant EXECUTE, DEBUG on DBMS_LOCK to cisadm;
Grant succeeded.
SQL> @?/rdbms/admin/utlrp.sql;
TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-11-07 12:30:23
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-11-07 12:30:28
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
After the installtion, there are three issues identified as below. Will investigate with the help of Oracle support. Most likely it is because of bugs of the original scripts.
there are total 24 sequences compared 26 in installation guide.
Trigger CISADM.CIFKVALID is INVALID, due to sequence CIFKVALSEQ does not exist.
Make DBA Life Easy 