Oracle Analytics Server 5.9 - Silent Install

Oracle Analytics Server 5.9 is now out and is available via the software delivery site of Oracle. I just want to quickly take you through the installation steps using silent mode.

Before we proceed, there are some pre-requisites which needs to be met. 

• Database for hosting metadata schemas 
• RPM packages 
• Software Zip files
• OS user with required permissions
• JDK version

Let's get to it. I will outline high level steps here a more detailed steps were documented in my GitHub repo.

Step 1: Directories 

We need to host the Oracle Fusion Middleware and Oracle Analytics Server domain, Ideal way to this is in two different folders 

• OAS_HOME
• oracle_home - for FMW and OAS software 
• domain_home - for bi domain configuration

Step 2 : FMW installation

Below is the simple command to install the fusion middleware infrastructure using a response file. You can find the sample response files in the Git repository.

java -jar /u01/app/oas/softwares/fmw_12.2.1.4.0_infrastructure.jar -silent -responseFile /u01/app/oas/resposefiles/fmw.response

Step 3 : FMW Patch Installation

Move the patch zip file to [ORACLE_HOME]/OPatch and unzip it there. change into the newly created path folder for ex cd 30657796. To apply patch use the below command.

[ORACLE_HOME]\OPatch\opatch apply -silent

Step 4 : OAS Installation

Unzip the Oracle Analytics Server installer zip file (~3.5GB). Modify the oas.response file to the oracle_home path as per previous step.

java -jar /u01/app/oas/softwares/Oracle_Analytics_Server_Linux_5.9.0.jar -silent -responseFile /u01/app/oas/resposefiles/oas.response

Step 5 : Domain Configuration

Update the config.respose to include details of the domain_home, Database details (hostname,port, servicename and SYS details) and port information etc.

[ORACLE_HOME]/bi/bin/config.sh -silent -responseFile /u01/app/oas/resposefiles/config.response

Install VNC Server on Oracle Cloud Infrastructure - Compute VM's

 This will be a simple and straight guide to install vnc server on Oracle Linux on Oracle Cloud Infrastructure Compute VM's.

This post outlines the steps to install and enable tiger vnc on Oracle Linux 7.

Step 1 : Install the necessary System Group Lists 

yum group install -y "Server with GUI"

Step 2 : Install tigervnc Server

yum install -y tigervnc-server

Step 3 : Create a service file and edit for a User

cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@\:1.service
vi /etc/systemd/system/vncserver@\:1.service

Once the file is open replace the <USER> with a OS linux user like opc/root or oracle and save the file

Step 4 : Enable and Start Vnc Server

systemctl daemon-reload
systemctl enable vncserver@\:1.service
systemctl start vncserver@\:1.service

Step 5 : Set VNC Password 

Login as the user with which we will use vnc server and use vncpasswd to set a new password.

vncpasswd

Step 6 : Open ports on Machine and Security Lists

firewall-cmd --permanent --zone=public --add-port=5901-5905/tcp
firewall-cmd --reload
firewall-cmd --permanent --zone=public --list-ports

In the Security List of your VCN, make sure to open ports 5901-5905 to access the vnc server from a vnc client like vncviewer etc.

Now from your client try the vnc server using <IP_ADDRESS>:<PORT> and Password as set in Step 5.

If you dont know on which port the vncserver is running, type vncserver it will display :1,:2 etc. For :1 the port is 5901, for :2 the port is 5902 and so on.

Add SWAP size while installing a Oracle Database 19c on Oracle Linux

 Below is the error message I received while I was trying to install Oracle Database 19c on Oracle Linux 7.

PRVF-7573 : Sufficient swap size is not available on node

As per my error on the installer screen I needed 16GB of SWAP space where as my system has just 4GB. So I need to add a swap file for the installation to continue. 

Below steps will outline the steps for the same.

dd if=/dev/sdb of=/tmp/swap01 bs=1K count=16M
chmod 600 /tmp/swap01
mkswap /tmp/swap01
swapon /tmp/swap01

Make sure the bs (*) count is equal to your required SWAP space. Also, the /dev/sdb has enough storage as needed

Oracle Autonomous Database : Delete files from DATA_PUMP_DIR

To delete a single file from DATA_PUMP_DIR, we need to first know the name of the file.

The below statement can be used to list down all the files avialable in the DATA_PUMP_DIR

select * from DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

Now, copy the filename which you want to delete and use below command to delete it

BEGIN
UTL_FILE.FREMOVE('DATA_PUMP_DIR','FILE_NAME');
COMMIT;
END:
/

There can be a case where you want to delete all the files from the DATA_PUMP_DIR or any other directory defined in Oracle Database / ADB. Use the below to delete all files in one shot.

BEGIN
FOR r IN (SELECT object_name FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'))
LOOP
UTL_FILE.FREMOVE ('DATA_PUMP_DIR',r.object_name );
END LOOP;
COMMIT;
END;
/

We can modify the select statement to filter for only the files to be deleted for example to meet a specific criteria like .log or .dmp etc.

Oracle Big Data SQL aka Cloud SQL

The post is intended to outline the Oracle Big Data SQL offering, more of an introduction to what it is and where can we find further information.

Introduction and terminology

Oracle Big Data SQL leverages Oracle cell smart-scan technology to fetch a large amount of data defined as an Oracle external table using Oracle Big Data SQL drivers (oracle_hdfs, oracle_hive, and for Big Data SQL (aka. BDSQL) 4.0, oracle_bigdata)

An Oracle query that involves such an external table triggers a describe call from an Oracle database foreground process to access metadata (from hadoop Hive Metastore / namenode) which includes granules (i.e., data blocks ) and location information. The granules are then batched and assigned to eligible multiple Parallel Queries Slaves, PQs (or a foreground process if a serialized query) which interface with Big Data SQL (or BDSQL) cells running in data nodes. There can be hundreds or thousands of granules involved in a single query. A query involving granules in all data nodes will be able to leverage all Big Data SQL cells running in data nodes, thus scale (i.e., parallelize) the query the best, and get the most benefits. After the data is fetched to the cell, the data can be filtered before sending back to the database, thus shortening the overall query elapsed time significantly.

Retries/Fallback

There are three replicas of each granule by default, each in different datanode. Each granule has three datanodes assigned, thus there will be three attempts to fetch each granule. If all of the three attempts (aka. retries) fail, the granule will have to fetched directly from the database (aka fallback mode), in which case, data will not be able to be filtered (impacting performance).

A small number of 'retries count' might not represent a problem. In any event, if the number keeps increasing, it indicates either the Big Data SQL cell memory is not large enough to handle the workload, or there are code issues / configuration issues.

Quarantines

With any internal error (i.e., ORA-600) occurs on any offload server thread, a quarantine is created in the Big Data SQL cell. The quarantine has the SQLID stored along with other attributes. Once a quarantine is created in a Big Data SQL cell, the same SQL (with the same SQLID) will not be allowed to be processed in that cell. The basic idea is that the same SQL will very likely cause the same internal error. Once there are three quarantines (within a certain time range), the Big Data SQL cell will not accept any further SQL from the database.

Each quarantine will cause an incident trace created in:/opt/oracle/bd_cell/log/diag/bdsql/cell/SYS*/incident/

Below is the working Architecture of Big Data SQL , which I will try to explain in later post.

Documentation 

          User Guide 

          Installation Guide

Installing unrar on Oracle Enterprise Linux, Red Hat etc.

unrar is not generally available in yum repositories of all the Linux platforms. Even though most people don't use or recommend RAR as a popular format for compression, there are still times where u might be in a situation where you need to extract a .RAR file.

I was quite in that situation today when one of colleague asked me to help extract a RAR file on Oracle Enterprise Linux.

I had to so quite some research to find out the steps by going through some blogs, community forums and some official repo sites. So recording them for others and my self.


1) Login to you Linux machine through SSH, make sure your'e root or has sudo access.

2) We have to download the rar libraries, use below command. Check the www.rarlab.com for latest releases.

wget https://www.rarlab.com/rar/rarlinux-x64-5.6.1.tar.gz

3) Extract the tar.gz file using below command.

tar -zxvf rarlinux-x64-5.6.1.tar.gz

4) Navigate into the extracted folder (rar)

cd rar

5) Check if the extracted files are working. Run the unrar just to make sure.

./unrar

You should an output similar to below.

6)  Now copy the files to /bin so that you can use them as default programs from anywhere.

cp rar unrar /bin

Once this is done you're good to use unrar command. Example command below.

unrar e filename.rar

Hope this helps. 

Creating connection from Oracle Analytics Cloud(OAC) to Autonomous DataWarehouse Cloud(ADWC)

Since the introduction of Autonomous services in Oracle cloud, there has been a lot of buzz around the Self-Driving, Self-Securing and Self-Repairing Database called Oracle Autonomous Data Warehouse Cloud service. I will try posting the features and other stuff about it at a later point, for now my concentration is on how to use the Data from this ADWC service and use it in my Business Intelligence tools, specifically Oracle Analytics Cloud.

Like any other database we would need certain details like Host name, Port, service name and user/password. For ADWC we would need an additional security measure in the format of a wallet file, so if you wish to connect to a ADWC this is must and should.

I have detailed the process on how to get these connection parameters along with creating a connection in OAC as a video. Hope you guys like it.

OAC : Fixing NULL value in prompts

We have been seeing this over the years in many different cases, NULL values appearing in prompts. There are certain workarounds like making the column not nullable etc, but this might impact the actual reporting in many ways.

So, the easy way would be to restrict the workaround to prompts.

We just have that, Just like in OBIEE 12c we have some tags for prompts in instanceconfig.xml

The instanceconfig.xml file is located in below location

[BI_DOMAIN]/config/fmwconfig/biconfig/OBIPS

Add the below tag in the prompts section of ServerInstance

<ServerInstance>
  <Prompts>
    <MaxDropDownValues>256</MaxDropDownValues>
    <ResultRowLimit>65000</ResultRowLimit>
    <AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
    <AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
    <AutoCompletePromptDropDowns>
      <SupportAutoComplete>true</SupportAutoComplete>
      <CaseInsensitive>true</CaseInsensitive>
      <MatchingLevel>MatchAll</MatchingLevel>
      <ResultsLimit>50</ResultsLimit>
    </AutoCompletePromptDropDowns>
    <ShowNullValueWhenColumnIsNullable>never</ShowNullValueWhenColumnIsNullable>
  </Prompts>
</ServerInstance>

Save the file and restart the services. Ideally, a restart of the Presentation Services component should be enough, if the changes don't come to effect, do a complete restart.

Provisioning Oracle Database Cloud Service (DBCS)

In the series for Oracle Analytics Cloud introduction, this post will cover one of its prerequisites Oracle Database Cloud Service. As directed in my previous post
Introduction to Oracle Analytics Cloud Series and Pre-requisites one should have valid Cloud account credentials for provisioning services.

Below will be step by step procedure in creating an Oracle Database Cloud Service instance.

• Navigate to webpage https://www.cloud.oracle.com
• Click on Sign In as shown in the below image.

• Enter the Identity Domain as choose during the registration or as received and click on My Services.

• Enter your credentials in the login page and click Sign In

• 
  
•  We will be taken to our Services Dashboard as shown below.

• Click on the symbol on the left top corner of my services dashboard.
• Select the Drop down next to Services and Click Database

• Click Create Instance as shown in the image.

• Give a name to the Database something like DBCS, Software release should be 12c Release 1 as OAC is not certified with 12c release 2. A single instance would suffice

• Give values as shown in the below screenshot. Password chosen here is Admin123#. These will be sufficient for use with an Oracle Analytics Cloud, for using as a Database for tables etc.. you might have to change based on usage. Click Edit next to SSH Public Key. 

• Select 'Create New Key' if you do not have a public key already and click 'Enter'

• Click Download to get the key pair, which will be used for SSH access to the DBCS instance.

• Click Next as shown below

• Click Create.

• It takes around 30 minutes for the instance to provision and once it is done you will see this screen.

Introduction to Oracle Analytics Cloud Series and Pre-requisites

It has been a while since I wrote a post, I have been working on Oracle cloud platform in the areas of Business Analytics and Big Data. As a starting point, I want to write a series of posts around provisioning and usage of Oracle PaaS services targetting areas around Business Analytics.

Oracle Database Cloud Service: This is the first and most popular Oracle Cloud service for GA. As leaders in the Database engine for over 4 decades the cloud service is also efficient like the on-prem. We would require an Oracle Database Cloud Service as a pre-requisite to the Oracle Analytics Cloud service for the purpose of storing metadata, commonly know as RCU schemas. Learn more about DBCS from here

Oracle Storage Cloud Service: This is one of the most common cloud services which you might be already aware of. Storage cloud service is meant for object storage. Everything in the cloud would need storage for the purpose of backup, logs etc. Oracle Analytics Cloud would also require storage of the same purpose of Logs & backups. Learn more about Storage Cloud from here

Oracle Analytics Cloud Service: Analytics cloud service is a combination of Oracle Business Intelligence Cloud Service, Oracle Data Visualization Cloud Service, and Oracle Essbase Cloud Service. So an Oracle Analytics Cloud service would essentially give all these packaged together under one license. Learn more on Analytics Cloud from here

In order to provision an Oracle Analytics Cloud service, below are the pre-requisites.

1. Cloud Account - Identity Domain, Username/Email, Password 
2. Oracle Database Cloud Service - Provisioned and Ready to use
3. Oracle Storage Cloud Service - Either a Container Created or Storage Admin Credentials for creating while provisioning.

In case you do not have a cloud account, you can try the Cloud for free with a promotional account having 400SGD credit for 30 days. Register for a cloud account at https://cloud.oracle.com.

R Studio : Warning: unable to move temporary installation

Was trying to install some packages using the install.packages() command and was facing some strange issue with below error.


Warning: unable to move temporary installation 'C:\Oracle\R-3.4.2\library\file6cae3bcf\MASS' to 'C:\Oracle\R-3.4.2\library\MASS'

Warning: unable to move temporary installation 'C:\Oracle\R-3.4.2\library\file6cae3bcf\MASS' to 'C:\Oracle\R-3.4.2\library\MASS'

Initially I was considering the permissions problem so i tried running the install.packages() command from cmd line as an administrator. Also gave full control on R installation directory to all users. Nothing worked.

Finally I was able to find solution from some random function mentioned R documentation. What actually is happening is that R is trying to install the mentioned package but is also interrupting it, interesting right.

Lets see in detail, when an install.packages() command is executed R will download the package to temp space, extract it and then place it in library path. But the extracting and copying are performed very closely that they are practically happening at the same time. All I had to do was insert some lag between them, otherwise put R to sleep for sometime.

Which can be performed using  Sys.sleep(time) command, the default value used in "unpackPkgZip" utility which is used to extract the downloaded packages is 0.5 sec, we will increase it to may be 2.5 or 3 etc. as follows.

The below command will open the unpackPkgZip utility code in edit mode

trace(utils:::unpackPkgZip, edit=TRUE)

Go to Sys.sleep(0.5) and replace it with 2.5 and save.

Now run the install.packages("MASS") which should run with out any errors.

OBIEE 12c : Environment Variables on Linux

This is just a quick one for all those who want to what environment variables should be set to use the OBIEE 12c environment on Linux environment. Make sure to include this list in the .bash_profile file of the Linux user used to run the OBIEE Application.

For those who are not aware of the .bash_profile location, the file will be in hidden mode in user home directory ( generally the directory you will be landed as soon as you login ). Can also find from environment variable HOME as per below.

[oracle@MyLinux oracle]$ pwd
/user/oracle
[oracle@MyLinux oracle]$echo $HOME
/user/oracle
[oracle@MyLinux oracle]$ l.

You can see the same using WinSCP. The assumptions here are OBIEE is installed as per below

/oracle/applications/OBIEE12c - Software Installtion
/oracle/applications/OBIEE12c/Domain_Home - Domain is installed here
/oracle/applications/OBIEE12c/Oracle_Home - FMW is installation directory

Open the .bash_profile file and add the below as needed

PATH=$PATH:$HOME/.local/bin:$HOME/bin
export JAVA_HOME=/oracle/applications/Java/jdk1.8.0_144
export LANG=en_US.UTF-8
export DOMAIN_HOME=/oracle/applications/OBIEE12c/Domain_Home/bi
export ORACLE_HOME=/oracle/applications/OBIEE12c/Oracle_Home
export BI_PRODUCT_HOME=/oracle/applications/OBIEE12c/Oracle_Home/bi
export BI_ORACLE_HOME=/oracle/applications/OBIEE12c/Oracle_Home/bi
export COMMON_COMPONENTS_HOME=$ORACLE_HOME/oracle_common
export ORACLE_DOMAIN_CONFIG_DIR=$DOMAIN_HOME/config/fmwconfig
export ORACLE_BI_ENVIRONMENT_DIR=$ORACLE_DOMAIN_CONFIG_DIR/bienv
export WL_HOME=$ORACLE_HOME/wlserver
export ORACLE_BI_CONFIG_DIR=$DOMAIN_HOME/config/fmwconfig/biconfig
export ODBCINI=/oracle/applications/OBIEE12c/Domain_Home/bi/config/fmwconfig/bienv/core/odbc.ini
export AMAZONREDSHIFTODBC=/userhome/oraclebi/.amazon.redshiftodbc.ini 
export ODBCHOME=/opt/amazon/redshiftodbc
export LIBPATH=$ODBCHOME/lib/64
export LD_LIBRARY_PATH=/opt/amazon/redshiftodbc/lib/64:/usr/local/lib:$ODBCHOME/lib/64:/usr/lib64:/usr/lib
export PATH=/oracle/applications/Java/jdk1.8.0_144/bin:$PATH:$ODBCHOME:/usr/lib64:/usr/lib:$JAVA_HOME/bin

Linux ODBC : GLIBCXX_3.4.20 not found, [RESOLVED]

I have done a setup of ODBC for Amazon Redshift on RedHat Linux 7.2 recently as per my earlier post OBIEE 12c: Amazon Redshift ODBC setup which was done a year ago.

All went well except i was not able to use the ODBC driver as it throws errors.

First i tried to see if my configuration was correct, the first step is to see if my DSN shows up or not.

To test this i use the odbcinst command 

[oracle@My-Linux ~]$ odbcinst -q -s
[ODBC]
[AnalyticsWeb]
[opss_datasource]
[biplatform_datasource]
[Redshift]

Next thing is to check if the DSN listed is working or not, which can be done using i sql

[oracle@My-Linux ~]$ isql -v Redshift
[01000][unixODBC][Driver Manager]Can't open lib '/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so' : file not found
[ISQL]ERROR: Could not SQLConnect

As the error says the file libamazonredshiftodbc64.so is not accessible for the ODBC manager, we will try and access the same to see if it has any issues.

[oracle@My-Linux ~]$ ldd /opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so: /usr/lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by /opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so)
        linux-vdso.so.1 =>  (0x00007fff88ffe000)
        libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007faf36d23000)
        libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007faf36b06000)
        librt.so.1 => /usr/lib64/librt.so.1 (0x00007faf368fe000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007faf365f5000)
        libm.so.6 => /usr/lib64/libm.so.6 (0x00007faf362f2000)
        libgcc_s.so.1 => /usr/lib64/libgcc_s.so.1 (0x00007faf360dc000)
        libc.so.6 => /usr/lib64/libc.so.6 (0x00007faf35d1b000)
        /lib64/ld-linux-x86-64.so.2 (0x00007faf39937000)

You can now see that the GLIBCXX_3.4.20 was not found in libstdc++.so.6 library file. Let us see what the file has to confirm if its available or not.

[oracle@My-Linux ~]$ strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX               
GLIBCXX_3.4
GLIBCXX_3.4.1
GLIBCXX_3.4.2
GLIBCXX_3.4.3
GLIBCXX_3.4.4
GLIBCXX_3.4.5
GLIBCXX_3.4.6
GLIBCXX_3.4.7
GLIBCXX_3.4.8
GLIBCXX_3.4.9
GLIBCXX_3.4.10
GLIBCXX_3.4.11
GLIBCXX_3.4.12
GLIBCXX_3.4.13
GLIBCXX_3.4.14
GLIBCXX_3.4.15
GLIBCXX_3.4.16
GLIBCXX_3.4.17
GLIBCXX_3.4.18
GLIBCXX_3.4.19
GLIBCXX_DEBUG_MESSAGE_LENGTH

So it is true that the GLIBCXX_3.4.20  is missing which is required by the Amazon driver, which is the root cause for the issue. Now lets see what can be done to resolve this.

We have to basically update the libstdc++ to a higher version which includes the GLIBCXX_3.4.20. For me the below command worked.

[oracle@My-Linux ~] yum update libstdc++

Depending on your OS you can update using different commands like

sudo apt-get install libstdc++6Above command will install it if not already donesudo add-apt-repository ppa:ubuntu-toolchain-r/test sudo apt-get updatesudo apt-get upgradesudo apt-get dist-upgrade

Now try and run the isql command with username and password to verify the connection.

[oracle@My-Linux ~]$ isql Redshift username password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

Oracle DB : Get the Oracle Database version using a SQL Query

To find the Oracle DB Version to which you are connected now use the below queries.

select * from v$version;

The Results will be as below, The database version i used is 11.2.0.3

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Sometimes the above command may result in Table or view doesn't exist error, reason can be you are not a DBA.

The below can be used as an alternative.

select * from product_component_version;

The Results for the above command will be like below.

 
 
 
 


  
PRODUCT
  
VERSION
  
STATUS
 

  
NLSRTL
  
11.2.0.3.0
  
Production
 

  
Oracle
  Database 11g Enterprise Edition
  
11.2.0.3.0
  
64bit Production
 

  
PL/SQL
  
11.2.0.3.0
  
Production
 

  
TNS for
  Solaris:
  
11.2.0.3.0
  
Production

Linux : Find all files containing specific text on Linux

The most common requirement these days is to find something that you have saved sometime ago in some file in a discrete folder, most importantly he one which you dont remember currently.Windows is pretty staright forward that everyone knows how to serach a file and I will not bore you with details.

However Linux is a different game when it comes to searching files and its contents. We will see a command that will let us do that work in simple and easy to remember manner.

            grep -rnw '/path/to/folder/' -e 'string'               

-r or -R is recursive,
-n is line number, and
-w stands for match the whole word.
-l (lower-case L) can be added to just give the file name of matching files.

Along with above options a few others like , --exclude, --include, --exclude-dir or --include-dir flags could be used for more efficient searching:


This below command will only search through the files which have .properties or .sh extensions:

           grep --include=\*.{properties,sh} -rnw '/path/to/folder/' -e "string"       

This below command will exclude searching all the files ending with .txt extension:

            grep --exclude=*.txt -rnw '/path/to/folder/' -e "string"                           

Just like exclude files, it's possible to exclude/include directories through --exclude-dir and --include-dir parameter. For example, this will exclude the dirs dir1/, dir2/ and all of them matching *.ext/:

            grep --exclude-dir={dir1,dir2,*.ext} -rnw '/path/to/folder/' -e "string"     

Hope you got what you came for. 

OBIEE 12c : Single script to export the service instance

As we are all aware, in OBIEE 12c all the code movements are recommended to be performed using the service instance export files called BAR ( BI Application Archive) files.

In General we will have to open the Weblogic command in offline mode and use the export service instance command with parameters as described in my previous post Export and Import Service Instances. But we can also make all this steps into a single script and the just run that so everything happens on it own. This helps you save time as each time you have to navigate to different locations and remember directories and write export script which might have typo errors etc.

Lets see what we can do, if we have the Work and Export Directories fixed then we can decide on  the export instance command, which generally will not change. Mine is as below.

exportServiceInstance('/oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi','ssi','/userhome/oraclebi/Desktop/BARWorkDir','/userhome/oraclebi/Desktop/BARExpDir','','','',true,'Admin123')

now save this command in a file with extension .py and make sure to give full permissions to user who runs the script. My case the file name is  SIExport.py located in below location

/home/oracle/scripts/SIExport.py 

Now all you need is to run this against the WLST  opened in offline mode. to do this we can actually use the below syntax.

java weblogic.WLST /home/oracle/scripts/SIExport.py 

But if you run this directly you will not be able to initialize the weblogic scripting tool, we need to set the environment before this using the below command.

source setWLSEnv.sh

Now we know the order and what needs to be done, include all the commands in a .sh file in required sequence. my file is ServiceInstanceExport.sh which is as below.

#!/bin/bash
# Purpose : Export the Service Instance
# Author : Rampradeep Pakalapati


ORACLE_HOME=/oracle/systems/apps/Middleware/Oracle_Home
WL_PATH=$ORACLE_HOME/wlserver/server/bin
source $WL_PATH/setWLSEnv.sh
sleep 5
java weblogic.WLST /home/oracle/scripts/SIExport.py


#End of File

Now if you just run the file as ./ ServiceInstanceExport.sh , your service instance will be exported to the mentioned export directoty.

OBIEE 11g : All services are running but unable to Login to analytics

During a recent installation of OBIEE 11.1.1.7.0 on one of my personal machines i have noticed something strange which has not happened to be before. The installation went on to complete successfully and the analytics page popped up as usual, but surprisingly i was not able to login to analytics. Admin Console and Enterprise Manager allowed the weblogic login with out any issues.


Verified all the deployments and was able to see no issues, restarted to see if that fixed the issue. Even the status of all opmn components is Alive.SO then i decided to go on to have a look at the Server log files. As this is something related to Analytics login only usually my first preference is to look at bi_server1.log, bi_server1-diagnostic.log, NQserver.log and sawlog.log files.


I found these below errors in bi_server1-diagnostic.log and NQserver.log

bi_server1-diagnostic.log

 WSM-02557 oracle.wsm.policymanager.accessor.BeanAccessor The documents required to configure the Oracle Web Services Manager runtime have not been retrieved from the Policy Manager application (wsm-pm), possibly because the application is not running or has not been deployed in the environment. The query "/policies/oracle/wss_username_token_service_policy" is queued for later retrieval.

[WSM-02353] [oracle.wsm.resources.policymanager] [tid: [ACTIVE].ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: ] [ecid: f2c8df7ec64d12f7:1c25f84e:14402c30fe1:-8000-0000000000001049,0] [APP: wsm-pm] Unable to get the Action Executor.

NQServer.log:

[WSM-02557] oracle.wsm.policymanager.accessor.BeanAccessor The documents required to configure the Oracle Web Services Manager runtime have not been retrieved from the Policy Manager application (wsm-pm), possibly because the application is not running or has not been deployed in the environment. The query "/policies/oracle/no_authentication_service_policy" is queued for later retrieval

 [nQSError: 43126] Authentication failed: invalid user/password.

When i saw thw Authntication failed message, I directly went into Administration Console to see if have the BISystemUser available as this is quite a famous issue. To my surprise its there however OracleSystemUser is missing from the list. I contacted a friend of mine in Oracle and found that this can be created directly as below.

• Create a User with the name OracleSystemUser
• Assign OracleSystemGroup to the user created, if group is missing then create one.
• Make sure the OracleSystemRole under "Roles and Policies" --> "Global Roles" has the OracleSystemGroup added to it.
• If OracleSystemRole role is missing create one with "Manage Global Roles" link in "How do I" section on the left.
• Add the OracleSystemGroup manually to Role using "Add Conditions" option.
• Restart the OBIEE services.

This fixed my issue, for some only role is missing and for others user is missing, so this process will fit for both the cases. Hope this helped your case.

OBIEE 12c: Uploadrpd and Downloadrpd Commands

Its more than a year 12c is into market and people are Testing it, Trying it out and also some using it. However, there are still questions around RPD deployments and offline access to RPD.

As per Oracle documentation Uploadrpd and Downloadrpd are meant for offline repository diagnostic and development purposes such as testing, only. And in all other cases of repository development and maintenance situations, you should use BAR to utilize BAR's repository upgrade and patching capabilities and benefits.

Now we will look at how we can use the uploadrpd and downloadrpd commands. For us, to either upload or download rpd, we will have to look at the launcher script datamodel.sh (Linux) or datamodel.cmd(Window) from version 12.2.1.1.
This same functionality is available as data-model-cmd.sh in OBIEE 12.2.1 Version, we find many online posts with this command.

Downloadrpd command

Syntax

downloadrpd -O RPDname [-W RPDpwd] -SI service_instance -U cred_username [-P cred_password] [-S hostname] [-N port_number] [-SSL] [-H]

O - Repository Name you want to Download.
W - Password for RPD download mandatory, if not specified in command will ask when we run it.
SI - Service Instance Name
U - OBIEE username
P  - Password for the OBIEE Username
All the above are Mandatory, where as the below are needed if you are using the command from a client installation
S - OBIEE server name
N - Port number
SSL - If SSL is enabled use this
H - displays the usage information and exits the command.

Example:- 

./datamodel.sh downloadrpd -O liverpd.rpd -W Admin123 -SI ssi -U weblogic -P Admin123 

Uploadrpd command

Syntax

uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

I - RPD name you are uploading
W - Password for RPD download mandatory, if not specified in command will ask when we run it.
SI - Service Instance Name
U - OBIEE username
P  - Password for the OBIEE Username
All the above are Mandatory, where as the below are needed if you are using the command from a client installation
S - OBIEE server name
N - Port number
SSL - If SSL is enabled use this
H - displays the usage information and exits the command.

Example:- 

./datamodel.sh uploadrpd -I liverpd.rpd -W Admin123 -SI ssi -U weblogic -P Admin123

OBIEE 12c : Export and Import Service Instance (BAR) files

This is once of the best features in OBIEE 12c, FYI my system is on 12.2.1.1. The way you can export your RPD, catalog, security model all at once without you having to worry about going to places and backing up files is just AWESOME.

I will keep it as simple as possible, BI Application Archive Files (BAR) are just like zip files but in BI application readable format. The basic idea behind this concept is to enable us to migrate the RPD, Catalog and Security Model all at once, to keep it simple. Its a command line utility and there is no other way around it.

First thing is to understand that it is a WebLogic Scripting Tool (WLST) utility, and can be executed only against it. To open a WLST command line utility open the below location on command prompt.

[ORACLE_HOME]/oracle_common/common/bin/

and run the file wlst.sh using the ./wlst.sh

Now before we start we need to know few details like DOMAIN_HOME, Service Instance Name, Administrator Username/Password which are already available as part of installation. Now we need two directories (for export) one for TempWorkSpace and other for saving the Exported BAR file.

Giving mine for an idea

DOMAIN_HOME = /oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi

Service Instance Name = ssi (definitely this one if dont know what it is)

you can find the same using the listBIServiceInstances('[DOMAIN_HOME') command where it will display the key.

Work Dir = /userhome/oracle/Desktop/BARWorkDir 

Export Dir = /userhome/oracle/Desktop/BARExpDir


We have prepared enough and now its time for action, Invoke the wlst.sh file and it will start in offline mode, dont worry about offline lets take it for another day. Today only concentrate on import and export Service instances.

The magical commands at last exportServiceInstance()    and  importServiceInstance() , definitely not expected them to be so simple when they are doing so much.

All the wlst commands are case sensitive so you must remember them as is. Let s see how to use.


exportServiceInstance()

Obviously this will export the service instance with RPD, Webcatalog and security info without any question, but we will need to specify some parameters to it, there are 9 of them, 4 mandatory.

1st -- DOMAIN _HOME
2nd -- Service Insatnce Name
3rd -- Work Dir
4th -- Export Dir
5th -- No Use as of now, Future Use
6th -- no Use as of now, Future Use
7th -- no Use as of now, Future Use
8th -- true or false ( will decide on exporting user specific info like personal folders etc)
9th -- Password for exporting DB credentials, if not given then DB details are not exported

Now we can either go for 1-4 and leave rest as below

exportServiceInstance('/oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi','ssi','/userhome/oraclebi/Desktop/BARWorkDir','/userhome/oraclebi/Desktop/BARExpDir')

or leave the 6,7 and 8 as blanks and give 8th and 9th as below

exportServiceInstance('/oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi','ssi','/userhome/oraclebi/Desktop/BARWorkDir','/userhome/oraclebi/Desktop/BARExpDir','','','',true,'Admin123')

Both the cases a Folder is created in Export Dir, with data and time as folder name with a file generally as ssi.bar , You are done by the way.

importServiceInstance()

So no more explaining on what it does, we look at how to get it done directly. Even import command needs some parameters 7 on the whole and 3 mandatory

1st -- DOMAIN_HOME
2nd -- Service Instance Name
3rd -- BAR file location with name
4th - Boolean to import RPD
5th -- Boolean to import Catalog
6th -- Boolean for User info
7th -- Password for DB credentials import

Now lets just use 3 , to get the RPD and catalog imported as below

importServiceInstance('/oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi','ssi','/userhome/oracle/Desktop/BARExpDir/20161130_151519/ssi.bar')

To get everything imported from BAR file use below

importServiceInstance('/oracle/systems/apps/Middleware/Oracle_Home/user_projects/domains/bi','ssi','/userhome/oraclebi/Desktop/BARExpDir/20161130_151519/ssi.bar','true','true','true','Admin123')

You can change the 4,5 & 6 Boolean parameters as per your need. Oracle doesn't say anything about restarts after the Import but i did notice some inconsistencies it can be anything may not be even related at all, but i did a restart of services. You can too if had a chance.

OBIEE 11g - Find where the RCU schemas are hosted

Many of the times i hear people asking for details of the database where the RCU schemas are hosted. Its hard when the system is there for sometime and people who have installed BI would have left and responsibilities have shifted to others and so on you tend to forget the details.

But we always have the details within OBIEE, the only problem is many of us are not aware where to look. I my self suffered for sometime to find this out.

Login to Enterprise Manager, using the URL   http://hostname:portno/em

Now as soon as you Login you will see two icons Farm and Topology right Below the Oracle logo. Adding image to save your time.

Clicking on Topology will lead you to an architectural diagram where you will see a database with the JDBC connection details as below

Now from the URL, jdbc:oracle:thin:@Pied-PC:1521/ORCL we can get the below details.

Hostname of Database : Pied-PC
Port of Database : 1521
Service Name : ORCL

Now all it takes is login details , which ofcourse you cannot find in OBIEE. We can find only the user name of BIPLATFORM schema from the below location

Business Intelligence --> Corepplication --> Deployment --> Scheduler 


I'm sure it comes in handy especially if you are new to Administration.