Insert Picture Here OBIEE 11 g to 12
<Insert Picture Here> OBIEE 11 g to 12 c Upgrade Tips Customer Engineering & Advocacy Lab September 2015
Simple installation steps • JDK: • • WLS - This will lay down the WLS shell: • • • If necessary, drop previous install database schemas using 'Oracle_Homeoracle_commonbinrcu. bat‘ or 'Oracle_Home/oracle_common/bin/. /rcu’ You can either create the required schemas via RCU, or let the BI Config process create them. BI Configuration - This will deploy the BIEE application to the WLS shell. • • Run the downloaded biee executable 'bi_platform-12. 2. 1. 0. 0 -*-win 64. exe'. Use the Oracle Home that was specified in the WLS install. RCU Schema: • • Open a command window and cd (change directory) to the bin directory of the installed JDK 8 (1. 8) Execute the following command - java -jar C: path_to_jar_filewls_jrf_generic-jarfile-*. jar - substituting the directory path and downloaded jar name as necessary. For example: c: Javajdk 1. 8. 0_45bin>java -jar C: BuildsBIEE12 cwls_jrf_generic-12. 2. 1. 0. 0 -150921. 1702. jar BIEE Platform - This will lay down the BIEE binaries only into the WLS shell: • • Install JDK 8 (1. 8) from http: //www. oracle. com/technetwork/javase/downloads/index. html Execute c: OracleMiddlewareOracle_Homebibinconfig. cmd • Optionally select the create schemas option if the biee schemas were not created before hand via RCU. • Optionally select a migrated 11 g jar file or a 12 c bar file. BIEE Client Tools: • • Run the downloaded biclient executable 'setup_bi_client-12. 2. 1. 0. 0 -*-win 64. exe. Specify an empty directory for 'Oracle Home'. Default port for Nqs. ODBC driver is 9514 (OBIS port). Note – The executable names listed above may change with the official release with the exception of the JDK.
Post Install Utilities and Configuration • Start/Stop/Status shell scripts - '. . . 12 c_homeuser_projectsdomainsbibitoolsbin'. Options are 'status. cmd', 'stop. cmd' and 'start. cmd‘: Usage: [-h domain. Home] [-i start. Servers. List] -h: Specify domain home (including final domain. Name dir). Default is DOMAIN_HOME if set, otherwise '$default_bi. Domain. Home' -i: Comma separated list (with no spaces) of servers (Name) and system components to start (default is all) • Example to start OBIS and OBIPS: • • . /start. sh -i obis 1, obips 1 start. cmd -i obis 1, obips 1 • Set-up tnsnames. ora at '. . . 12 c_homeuser_projectsdomainsbiconfigfmwconfigbienvcore' (also contains odbc. ini)
Main directories Directory structure has completely changed in 12 c. Here are the most commonly used paths: • OBIEE Configuration files: <MW_HOME>/user_projects/domains/bi/config/fmwconfig/biconfig • Tnsnames. ora: <MW_HOME>/user_projects/domains/bi/config/fmwconfig/bienv/core • Stop/Start scripts: <MW_HOME>/user_projects/domains/bi/bitools/bin • Logs: <MW_HOME>/user_projects/domains/bi/servers • Custom messages (previously Analytics. Res): <MW_HOME>/user_projects/domains/bi/bidata/components/OBIPS/cust om. Messages
Default port numbers Port numbers of BI components are assigned automatically during installation based on port range specified. They can be found easily in EM.
Catalog Upgrade Pre-Requisite • Upgrading catalog to 12 c is supported from 11. 1. 1. 7 and 11. 1. 1. 9. • Sometimes when customers upgraded to 11. 1. 1. 7 from previous 11 g version, they did not run the full catalog upgrade. The consequence is that the version stored in the catalog is 11. 1. 1. 3, 11. 1. 1. 5, or 11. 1. 1. 6, even though the catalog is being used on 11. 1. 1. 7 environment. • Before upgrading to 12 c, make sure that your catalog is in the right version by running catalog upgrade in your current 11 g environment (set <Upgrade. And. Exit> tag to true in instanceconfig. xml).
Using the 12 c Migration Tool bi-migration-tool. jar • Used to migrate 11 g OBIS and OBIPS metadata and WLS defined application roles to a self-extracting and self-executing 12 c migration jar file (bundle) • 11 g JDK requirements: • You can use the same JDK as the 11 g instance, or you may install jdk 1. 8 in your 11 g environment. In either case, the jdk used must be the first in the path if multiple java instances are installed. • 12 c JDK requirements: • 12 c requires JDK 1. 8. This is available at http: //www. oracle. com/technetwork/javase/downloads/index. html
Example for Windows installations • Copy the Oracle 12 c Migration tool from the Install home to your 11 g environment: Default install location – Oracle_Middleware_Homebimigration-tooljlibbi-migration-tool. jar Example - C: OracleMiddlewareOracle_Homebimigration-tooljlibbi-migration-tool. jar • - bi-migration-tool. jar parameters: • Expected arguments: out <oracle 11 g home> <domain home> <output export bundle path> (Export from an existing system) in <oraclehome> <domainhome> <export bundle> <service instance name> (Import into an existing system) get <pluginname> <export bundle> (Retrieve data produced by a plugin) put <pluginname> <new data file> <new export bundle> <existing export bundle> (Repack jar, replacing data from a plugin)
• Migrate export bundle from 11 g system: java -jar pathto12 cbi-migration-tool. jar out pathto11 gmwhomeOracle_BI 1 pathto11 gmwhomeuser_projectsdomainsbifoundation_domain pathtomy-export-bundle. jar • Example (bi-migration-tool. jar copied to directory ‘c: aaa11 g_to_12 c_upgrade’): c: aaa11 g_to_12 c_upgrade>java -jar bi-migration-tool. jar out C: oraclebi_11119Oracle_BI 1 C: oraclebi_11119user_projectsdomainsbifoundation_domain C: OBIEE_11 g_bar11 g_sampleapplite. jar • • Copy / Move the 11 g exported jar file to the machine where the 12 c instance resides if it differs from the 11 g instance machine Import bundle into 12 c system: java -jar pathto12 coraclehomebi-migration-tooljlibbi-migration-tool. jar in pathto12 coraclehomeuser_projectsdomainsbi pathtomy-export-bundle. jar service 1 or ssi • Example: c: Javajdk 1. 8. 0_45bin>java -jar C: OracleMiddlewareOracle_Homebimigration-tooljlibbi-migration-tool. jar in C: OracleMiddlewareOracle_Home C: OracleMddlewareOracle_Homeuser_projectsdomainsbi C: 2. BuildsBIEE12 c11 g_bars11. 1. 1. 9. 0_Sample. App. Lite. jar service 1 or ssi NOTE: Examples have return lines between parameters to highlight them only. Do Not use return lines in actual calls.
Example for Linux installations Copy the Oracle 12 c Migration tool from the Install home to your 11 g environment: • Default install location: • Oracle_Middleware_Home/bi/migration-tool/jlib/bi-migration-tool. jar Example - /scratch/aime/Oracle_Home/bi/migration-tool/jlib/bi-migration-tool. jar • Migrate / export bundle from 11 g system: java -jar /path/to/12 c/bi-migration-tool. jar out /path/to/11 g/mwhome/Oracle_BI 1 /path/to/11 g/mwhome/user_projects/domains/bifoundation_domain /path/to/my-export-bundle. jar
• Example (bi-migration-tool. jar copied to directory ‘/scratch/aime/11 g_to_12 c_upgrade’): /scratch/aime/11 g_to_12 c_upgrade>java -jar bi-migration-tool. jar out /scratch/aime/Oracle_Home/Oracle_BI 1 /scratch/aime/Oracle_Home/user_projects/domains/bifoundation_domain /scratch/aime/OBIEE_11 g_bar/11 g_sampleapplite. jar • • Copy / Move the 11 g exported jar file to the machine where the 12 c instance resides if it differs from the 11 g instance machine Import bundle into 12 c system: java -jar /path/to/12 c/oraclehome/bi-migration-tool/jlib/bi-migration-tool. jar in /path/to/12 c/oraclehome/user_projects/domains/bi /path/to/my-export-bundle. jar service 1 or ssi • Example: /scratch/aime/Java/jdk 1. 8. 0_45/bin>java -jar C: /Oracle/Middleware/Oracle_Home/bi/migration-tool/jlib/bi-migration-tool. jar in /scratch/aime/Oracle_Home/user_projects/domains/bi /scratch/aime/Builds/BIEE/12 c/11 g_bars/11. 1. 1. 9. 0_Sample. App. Lite. jar service 1 or ssi NOTE: Examples have return lines between parameters to highlight them only. Do Not use return lines in actual calls.
Example using the Migration Script • 12 c also ships with a migration-tool script (migration-tool. sh or migration-tool. cmd) that can be used for import. It determines some parameters automatically, so the user does not have to supply them. • The location of this script is: • …/user_projects/domains/bi/bitools/bin • The required parameters are: in <export bundle> <service instance name> • Note that oracle home and domain home are not required. • Here is an example: …/user_projects/domains/bi/bitools/bin/migration-tool. sh in /scratch/aime/Builds/BIEE/12 c/11 g_bars/11. 1. 1. 9. 0_Sample. App. Lite. jar ssi or service 1
Example using the migrated 11 g jar file during the OBI Configuration install step • You may use a migrated 11 g jar file when using the BI config script ‘…mwhomebibinconfig. cmd’ or ‘…/mwhome/bi/bin/config. sh’ • Create the 11 g migration jar file using the appropriate steps above. • Run 'mwhomebibinconfig. cmd' or '/mwhome/bi/bin/config. sh', select the required settings, and provide the necessary parameters. • Choose 'Single Instance' and select the required components (Essbase, BIEE and/or BI Publisher). • Choose to create the Schemas, or use existing schemas if previously created via RCU. • Either use the Default Port assignments 9500 - 9999, or change to another range. • Choose to use export bundle and browse to the location of the migrated 11 g jar file:
NOTE: Password is the password for the migrated 11 g RPD • Optionally save Response file • Optionally save the Configuration info to a file.
Manual create or drop the required schemas for an OBIEE install using RCU • To manually create or drop any RCU defined schemas, run the ‘rcu. bat’ or ‘rcu. sh’ executable located at ‘…/oracle_common/bin’ • Follow the appropriate ‘Create Repository’ or ‘Drop Repository’ option steps Note: A fresh schema is required for a OBIEE 12 c install. You cannot reuse a pre-existing OBIEE 12 c schema.
Usage Tracking Usage tracking settings are no longer managed by Enterprise Manager. To enable usage tracking, it is necessary to modify the corresponding settings in NQSConfig. ini manually.
Usage Tracking There are 9 new columns in usage tracking tables in OBIEE 12 c compared to 11. 1. 1. 7. But most of them already existed in 11. 1. 1. 9. In S_NQ_ACCT: • • ECID varchar 2(1024) added in 11. 1. 1. 9 corresponds to ECID in biserver-diagnostic. log TENANT_ID varchar 2(128) added in 11. 1. 1. 9 tenant id, used in multitenancy SERVICE_NAME varchar 2(128) added in 11. 1. 1. 9 service name, used in multitenancy SESSION_ID number(10, 0) added in 11. 1. 1. 9 corresponds to biserver session for use in analyzing user behavior by session HASH_ID varchar 2(128) added in 11. 1. 1. 9 logical query hash id, joins to s_nq_db_acct. hash_id TOTAL_TEMP_KB number(20, 0) added in 12 c total temp space used by a query during execution RESP_TIME_SEC number(10, 0) added in 12 c how much time server took before it started fetching records. This is the response time that the end user would experience with few early records being displayed on dashboard, while server continues fetching more In S_NQ_DB_ACCT: • • HASH_ID varchar 2(128) added in 11. 1. 1. 9 logical query hash id, joins to s_nq_acct. hash_id PHYSICAL_HASH_ID varchar 2(128) added in 11. 1. 1. 9 used for tracing physical queries to the backend database
Usage Tracking Direct Database Inserts In NQSConfig. INI, modify the following parameters: • ENABLE – turns usage tracking on or off, off by default • PHYSICAL_TABLE_NAME – usage tracking table as defined in the. rpd file • CONNECTION_POOL – connection pool for usage tracking table as defined in the. rpd file For example: ######################################## # # Usage Tracking Section # # Collect usage statistics on each logical query submitted to the # server. # ######################################## [USAGE_TRACKING] ENABLE = YES; #======================================= # Parameters used for writing data to a flat file (i. e. DIRECT_INSERT = NO). # # Note that the directory should be relative to the instance directory. # In general, we prefer directo insert to flat files. If you are working in a cluster, it is strongly recommended you use direct insert. If there is only one Oracle BI Server instance, then you may use flat file data. # The directory is then assumed relative to the process instance. For example, "UTData" is resolved to "$(ORACLE_INSTANCE)/bifoundation/Oracle. BIServer. Component/<instance_name>/UTData STORAGE_DIRECTORY = "<directory path>"; CHECKPOINT_INTERVAL_MINUTES = 5; FILE_ROLLOVER_INTERVAL_MINUTES = 30; CODE_PAGE = "ANSI"; # ANSI, UTF 8, 1252, etc.
Usage Tracking Direct Database Inserts #======================================= DIRECT_INSERT = YES; #======================================= # Parameters used for inserting data into a table (i. e. DIRECT_INSERT = YES). # # Init-Block Tracking Options are commented out and as a result disabled. # To enable Init-Block Tracking Feature, define the two parameters for # Init-Block, INIT_BLOCK_TABLE_NAME and INIT_BLOCK_CONNECTION_POOL. # PHYSICAL_TABLE_NAME = "Usage. Tracking". "server 1_biplatform". "S_NQ_ACCT"; CONNECTION_POOL = "Usage. Tracking". "Connection Pool"; # INIT_BLOCK_TABLE_NAME = "<Database>". "<Catalog>". "<Schema>". "<Table>" ; # INIT_BLOCK_CONNECTION_POOL = "<Database>". "<Connection Pool>" ; BUFFER_SIZE = 250 MB; BUFFER_TIME_LIMIT_SECONDS = 5; NUM_INSERT_THREADS = 5; MAX_INSERTS_PER_TRANSACTION = 5 ; JOBQUEUE_SIZE_PER_INSERT_THREADPOOL_THREAD = 100; #default is 100 while 0 means unlimited. THROW_INSERT_WHEN_JOBQUEUE_FULL = NO; # Default is no. # #======================================= • Usage tracking tables and columns need to be defined in the. rpd file. If just the tables are defined, an error will be recorded in obisn-diagnostic. log (formerly nqsserver. log) similar to: [2015 -07 -31 T 16: 50: 20. 91 -07: 00] [OBIS] [ERROR: 1] [] [] [ecid: 993594 d 8 -80 bf-43 a 7 -a 83 c-11584 bbfffd 300001677, 0: 1: 6] [tid: 81 d 7 a 700] [59053] Usage Tracking stopped because the specified Usage Tracking table contained the wrong number of columns or a column with an inappropriate data type. • If usage tracking has previously been defined in the. rpd, since there a limited number of new columns the easiest modification would be to add the new columns manually in the. rpd.
Hot deployment of the OBIS RPD metadata file • There is a command line tool to upload and download an OBIS RPD in 12 c: • ‘data-model-cmd. cmd’ at ‘…user_projectsdomainsbibitoolsbin’. • You run this in a cmd window at ‘…user_projectsdomainsbibitoolsbin’. • This tool has several other options as well. Type ‘-H’ for a listing. • Currently, there is no equivalent utility to upload/download the OBIPS Webcat.
Hosting the BIEE metadata on a shared network location for clustering • The process is to: • Move/copy the entire ‘bidata’ in the ‘/user_projects/domains/bi/’ directory to shared storage. • Update the config file bi-environment. xml (DOMAIN_HOME/config/fmwconfig/bienv/core/) with the location of this new ‘singleton data directory’. • Example on Windows: • Copy the host ‘bidata’ folder to the target share folder on the network share machine. • • • On the network windows box, share the target share folder to the appropriate user(s). On the BIEE Windows server, map a network drive to the share location created in step 2. Update the bi-environment. xml <bi: singleton-data-directory> tag with the share created in step 3 – e. g. : <bi: singleton-data-directory>Z: bidata</bi: singleton-data-directory>. Stop/start the BIEE stack. • • Everything under the bidata directory would be on the shared directory/NAS. One of the changes in 12 c is to cleanly separate the metadata and config in the deployments hence just copying the bidata directory.
Legend in Stacked Bar Graph 11 g 12 c The sort order in Stack Bar graph has been reversed to improve consistency.
Catalog Size In 12 c, the size of the catalog cannot exceed 4. 5 GB by default. If the catalog reaches this size then some limitations appears (impossibility to save new projects in Visual Analyzer, …). To remove this limitation or increase it, the following tags can be added in instanceconfig. xml: <catalog> <Service. Instance. Quota> <Enabled>true/false</Enabled> <Size. Limit. MB>5120</Size. Limit. MB> </Service. Instance. Quota> <catalog>
Concatenation of numeric data types In 12 c, it is not allowed anymore to concatenate columns in numeric data types in logical SQL. The column must be converted to a text data type first. Note that it is possible revert to 11 g behavior by modifying the following setting (which is global) in NQSConfig. ini [Compatibility] COMPATIBLE_RELEASE = 11. 1. 1. 9 Note that instead of using a global parameter, it is also possible to change the behavior locally in a report by using a session or request variable, or by setting the value in prefix: set variable COMPATIBLE_RELEASE = '11. 1. 1. 9'
Web Logic Services starting slowly For Linux/Unix installs, if the Admin. Server, Managed. Server or Node. Manager services are taking a long time to start, it could be a sign of not enough entropy allocated on the server. To check the point in time entropy available on a server, run this command: • cat /proc/sys/kernel/random/entropy_avail • Anything below 500 is at risk of running out of entropy. Increasing the entropy has been seen to dramatically decrease service start up times. The following resources explain how this may be accomplished: • Fusion Middleware Release Notes section “ 13. 28. 7 Random Number Generator May Be Slow on Machines With Inadequate Entropy” • The following blogs describe this topic in detail: • Web. Logic Server and entropy • Why does my Weblogic Server takes a Long Time to Start?
Install and configure R Engine for Advanced Analytics Need to execute all commands as root or administrator user Linux 64 bit • Explode '. . . /Oracle_Home/bi/bifoundation/advanced_analytics/r-installer. tar. gz': tar -xvzf. /r-installer. tar. gz • Change Directory to 'RInstaller' • Modify proxy. txt to use the appropriate proxy: Default is proxy=http: //[proxy-host: proxy-port]. Modify this to the site specific proxy • Install R: . /RInstaller. sh install (default install path is '/usr/lib 64/R') • Install required R packages: . /RInstaller. sh installpackages • Start R and execute the Sys. Blas. Lapack function: > Sys. Blas. Lapack() $vendor [1] "R internal BLAS and LAPACK" $nthreads [1] 1
• Modify NQSConfig. ini section 'Advance Analytics Script Section' parameters - e. g. : [ ADVANCE_ANALYTICS_SCRIPT ] # R EXECUTABLE PATH # Specify the script executable binary path. # R_EXECUTABLE_PATH = "/usr/bin/R"; R_EXECUTABLE_PATH = "/usr/lib 64/R/bin/R"; # Include the actual executable 'R' # R COMMAND ARGS # Specify the script executable command line arguments. R_COMMAND_ARGS = "--no-restore --no-save --no-timing"; # Max Time to wait for R Process to complete gracefully. # The R Process would be terminated beyond this max time R_PROCESS_TIMEOUT = 3600; # in seconds. # Max Number of R Process that can be active at any given point in time R_MAX_PROCESS = 20; # EXECUTION TARGET WHERE SCRIPT GETS EXECUTED # Defaults to Mid Tier R. The other targets are ORE, etc TARGET = "R"; # THE CONNECTION POOL HAS TO BE SET IF Advanced Analytics NEED TO RUN ON THE DATABASE (eg: ORE) # CONNECTION_POOL = "<Database>". "<Connection Pool>"; • Stop/start BIEE stack
For Linux 7 only • RPMs ‘texinfo-tex-5. 1 -4. el 7. x 86_64. rpm’ and ‘texlive-epsf-svn 21461. 2. 7. 432. el 7. noarch. rpm’ need to be installed first. They may be downloaded from the following locations: • http: //rpmfind. net/linux/RPM/centos/7. 1. 1503/x 86_64/Packages/texinfo-tex-5. 14. el 7. x 86_64. html • http : //rpmfind. net/linux/RPM/centos/7. 1. 1503/x 86_64/Packages/texlive-epsfsvn 21461. 2. 7. 4 -32. el 7. noarch. html • After downloading the rpm from the above links, perform a 'rpm -ivh <rpm_name>' as the root user for each rpm: • rpm -ivh texlive-epsf-svn 21461. 2. 7. 4 -32. el 7. noarch. rpm • rpm -ivh texinfo-tex-5. 1 -4. el 7. x 86_64. rpm Note: Tex. Live has to be installed before texinfo-tex
Windows • • Download wget and the unzip commands – ‘http: //gnuwin 32. sourceforge. net/packages/wget. htm’ and ‘http: //gnuwin 32. sourceforge. net/packages/unzip. htm’ respectively - and install both. Add Gnu. Win 32bin directory to the PATH - e. g. : 'C: Program Files (x 86)Gnu. Win 32bin' Explode '. . . Oracle_Homebibifoundationadvanced_analyticsr-installer. tar. gz': • tar -xvzf. /r-installer. tar. gz Change Directory to 'RInstaller' Modify proxy. txt to use the appropriate proxy: • Default is proxy=http: //[proxy-host: proxy-port]. Modify this to the site specific proxy. Install R: • RInstaller. bat install (default install path is 'C: Program FilesRR-3. 1. 1') Install required R packages: • RInstaller. bat installpackages Start R and execute the Sys. Blas. Lapack function: R> Sys. Blas. Lapack() $vendor [1] "Intel Math Kernel Library (Intel MKL)" $nthreads [1] -1 • • The returned value of $vendor indicates that MKL has replaced the BLAS and LAPACK that are native to R. The returned value of nthreads indicates the number of threads to be used by MKL. By default all available threads are used ($nthreads= -1).
• • Optional - You can change the number of threads to be used by MKL by editing the system environment variable MKL_NUM_THREADS If MKL_NUM_THREADS does not exist, then you must create it at: • • Control Panel > System and Security > System > Advanced system settings > Environment Variables > System variables After setting MKL_NUM_THREADS to 3, the output of Sys. Blas. Lapack shows a value of 3 for $nthreads. R> Sys. Blas. Lapack() $vendor [1] "Intel Math Kernel Library (Intel MKL)" $nthreads [1] 3 • Modify NQSConfig. ini section 'Advance Analytics Script Section' parameters - e. g. : [ ADVANCE_ANALYTICS_SCRIPT ] # R EXECUTABLE PATH # Specify the script executable binary path. # R_EXECUTABLE_PATH = "/usr/bin/R"; R_EXECUTABLE_PATH = "C: /Program Files/R/R-3. 1. 1/bin/x 64/R"; # Include the actual executable 'R‘ # R COMMAND ARGS # Specify the script executable command line arguments. R_COMMAND_ARGS = "--no-restore --no-save --no-timing"; # Max Time to wait for R Process to complete gracefully. # The R Process would be terminated beyond this max time R_PROCESS_TIMEOUT = 3600; # in seconds. # Max Number of R Process that can be active at any given point in time R_MAX_PROCESS = 20; # EXECUTION TARGET WHERE SCRIPT GETS EXECUTED # Defaults to Mid Tier R. The other targets are ORE, etc TARGET = "R“; • Stop/start BIEE stack Uninstallation • • Windows: Use Windows ‘Programs’ or ‘Programs and Features’ Linux: . /RInstaller uninstall
Configuring JDBC datasources • • • In 12 c, all configuration is done out of the box. The Data Source Server is set up on the managed server. Deploying the Data Source Server war file and editing NQSConfig. ini are artifacts of 11 g and are no longer needed. From Admin tool select ‘File > ‘Load Java Datasources. . . ’ - This will bring up the Connect to Java Datasource Server interface. For Hostname, enter the Admin Server name. The default port is 9500. Successful connection:
• Now when importing a new data source, JDBC (Direct Driver) and JDBC (JNDI) Connection Types are available:
Oracle BITech Demo You. Tube channel • https: //www. youtube. com/user/Oracle. BITech. Demos
- Slides: 33
