INSTALLATION DOCUMENTS BY RAVI

Sunday, April 19, 2020

Installation and Configuration of Oracle Analytics server



JDK Installation

1.       Download and extract the recommended JDK version.











Fusion Middleware Installation

1.       Start the Fusion middle ware installation
$JAVA_HOME/java -jar fmw_12.2.1.4.0_infrastructure.jar








In welcome screen, click on Next






















Select “Skip Auto Updates”, click on Next





















Provide “Oracle Home” path and click on Next






















Select “Fusion Middleware Infrastructure” and click on Next.





















If Prerequisite Checks completed successfully, click on Next






















Click on Install






















Click on Next





















Click on Finish






















Apply Patch set

After installing, we need to apply the 30657796 patch which was downloaded from e-delivery
Navigate to downloaded patch directory, set ORACLE_HOME and PATH environmental variables and run opatch apply command






















OAS Installation

Start Oracle Analytics Server Installation as shown below







On welcome screen, click on Next






















Select “Skip Auto Updates”, click on Next






















Provide “Oracle Home” and click on Next





















If Prerequisite Checks completed, click on Next






















Click on Install






















Click on Next






















Click on Finish






















RCU Configuration

Navigate to oracle_common/bin directory, run RCU as shown below





On Welcome screen, click on Next





















Select “Create Repository”, click on Next





















Enter the Database details and click on Next





















Click on OK





















Select Create new prefix as per “Best Practices” and select Oracle Business Intelligence. Click on Next





















Click on OK





















Enter password for newly created Schema’s, click on Next





















Click on Next





















Click on OK





















Click on OK





















Click on Create





















Click on Close





















OAS Configuration

1.       Next step is to configure OAS, for this we need to run
cd $ORACLE_HOME/bi/bin
./config.sh






On Welcome screen, click on Next






















Select below options and click on Next






















If Prerequisite checks completed, click on Next






















Provide Domain Directory details and click on Next






















Enter database schema details, click on Next






















Click on Next






















Select Clean State, click on Next






















Click on Configure






















Click on Next






















Click on Finish





















Accessing Oracle Analytics services


Weblogic console









































Fusion Middleware Control







































BI Publisher









































DV

























































Oracle Analytics Server Data Visualization Machine Learning Fails With 'Something Went Wrong' Using Insight Explain Feature or Data Flow


Oracle Analytics Server Data Visualization Machine Learning Fails With 'Something Went Wrong' Using Insight Explain Feature or Data Flow



Show Details 
Step |j| Execution failed. Status: FAILED. Message: [nQSError: 46240] Python Process exited with non 0 exit Code
[nQSError: 43224] The Dataflow "Address State Data Flow" failed during the execution.
[nQSError: 43204] Asynchronous Job Manager failed to execute the asynchronous job.

















Solution:
Login to the server
Using 'sudo' or as 'root' user, install the libgfortran package




































Restart OAS:
  • stop.sh
  • start.sh

SQL query to find top 50 tables by size

select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;


Note: Change 50 to any other number of tables you want to find

Sunday, February 16, 2020

Loading xml file data to oracle table using python


Sample xml file (test.xml):

<?xml version="1.0"?>
<data>
    <customer name="Ravi" >
        <email>lravikumarvsp@gmail.com</email>
        <phone>9999999999</phone>
    </customer>
    <customer name="kumar" >
        <email>kumar@gmail.com</email>
    </customer>
    <customer name="lanke" >
        <email>lanke@gmail.com</email>
        <phone>8888888888</phone>
    </customer>
    <customer name="ravikumar" >
        <phone>7777777777</phone>
        <address>
            <street>kukatpally</street>
        </address>
    </customer>
</data>




Python script for parsing xml file data to data frame:

import pandas as pd
import xml.etree.cElementTree as et
#Reading Data from the xml file
parsedXML = et.parse( "test.xml" )                                                                         
dfcols = ['name','email','phone','street']                                                                                
df = pd.DataFrame(columns=dfcols)
def getvalueofnode( node ):
                return node.text if node is not None else None
for node in parsedXML.getroot():                                                                              
                name = node.attrib.get('name')
                email = node.find('email')
                phone = node.find('phone')
                street = node.find('address/street')
                df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)
print (df)                                                                                                                                  










Run the program as below:















Now create a table with column names in the data frame.

CREATE TABLE TEST 
   (                          "NAME" VARCHAR2(200 BYTE),
                               "EMAIL" VARCHAR2(200 BYTE),
                               "PHONE" VARCHAR2(200 BYTE),
                               "STREET" VARCHAR2(200 BYTE)
   )





















Python script for loading xml file to oracle table.

import pandas as pd
import cx_Oracle
import xml.etree.cElementTree as et
#Reading Data from the xml file
parsedXML = et.parse( "test.xml" )                                                                         
dfcols = ['name','email','phone','street']                                
df = pd.DataFrame(columns=dfcols)
def getvalueofnode( node ):
                               return node.text if node is not None else None
for node in parsedXML.getroot():                                                                               
                               name = node.attrib.get('name')
                               email = node.find('email')
                               phone = node.find('phone')
                               street = node.find('address/street')
                               df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)
print (df)                                                                                                                             
#Loading Data into the Database Table
host1 = '172.16.11.32'
port1 = '1521'
sid1 = 'srcdb'
schema1 = 'system'
pwd1 = 'Admin123'
sdsn = cx_Oracle.makedsn(host1, port1, service_name=sid1)    
connect=cx_Oracle.connect(schema1, pwd1, sdsn)                                                        
cursor=connect.cursor()
dataframe_list=df.values.tolist()                                                                                                              # convert the dataframe to list
sql='INSERT INTO TEST values (:1,:2,:3,:4)'
for index,elem in enumerate(dataframe_list):                                                   
                               cursor.execute(sql,dataframe_list[index]) 
                               connect.commit()                                                             













Run the script to load the data to oracle table now















Check the table for data






















  Opatch reports 'Cyclic Dependency Detected' error when patching ODI   Issue: When applying a Patch Set Update (PSU) to WebLogic Se...