June 19, 2017

dbca - db creation stops with dbmssml.sql error



Abstract:


Trying to create a database using dbca on a Oracle Linux 7 running in a Virtualbox Container. DBCA throws an error during database creation: something like "dbmssml.sql. refer to dbmssml0.log error in process: perl". The mentioned log file is empty.


Solution:

Important!: It is NOT a database software or a dbca problem. Additionally, if You run a 'perl -v' command, You'll receive a 'segmentation error' - this is, too, not a database, nor a perl problem. Instead, it is a Virtualbox 5 issue.

Found the solution for that here: Oracle DB12c / OL7 / VBox 5 – Perl Segmentation Fault
Share:

June 16, 2017

OracleXADataSource cannot be cast to java.sql.Driver

Tried to start a Forms / Reports Server on an all new server. During startup of the AdminServer, I got an errormessage:

<BEA-000362> <Server failed. Reason: 

There are 1 nested errors:

oracle.security.jps.JpsException: oracle.security.jps.service.policystore.PolicyStoreException: javax.persistence.PersistenceException: java.lang.ClassCastException: oracle.jdbc.xa.client.OracleXADataSource cannot be cast to java.sql.Driver

Solution:

In my case it was quite simple: Start the Repository Database and the according listener process ... if not running. If running, check whether or not the Repository Database can be connected by using the EZConnect ConnectString for Your Repository DB. Pattern for EZConnect is like this: host.domain:port/servicename

Share:

June 13, 2017

ODA X6 - netca cannot add listener - displaying 'Use another port number' messages

Tried to create e new listener to run on a non-default portnumber on an ODA X6. Each time when adding a new port number (doesn't matter which number) an error screen pops up, stating that I shall choose another port number:




I checked more than once and using different tools - there is no port like this used. Finally I checked the logfile - to be found here: 
<GRID_HOME>/cfgtoollogs/netca/trace_OraGrid<something>.log 
it shows error messages like these:

[AWT-EventQueue-0] [ 2017-06-13 13:54:55.424 CEST ] [ConfigureListenerOPS.buildAddressString:787]  Building Address for TCP:
        (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<domain>)(PORT=1533)(IP=FIRST))
oracle.net.ca.IllegalEndpointException: The information provided for this listener is currently in use by another listener on this computer.

followed by

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.325 CEST ] [ConfigureListener.validateEndPoint:1059]  Validating end-point: TCP:1534
[AWT-EventQueue-0] [ 2017-06-13 13:55:00.326 CEST ] [ConfigureListenerOPS.buildTCPAddress:807]  Building TCP Address with HOST <hostname>.<domain>
[AWT-EventQueue-0] [ 2017-06-13 13:55:00.327 CEST ] [ConfigureListenerOPS.buildAddressString:787]  Building Address for TCP:
        (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>.<domain>)(PORT=1534)(IP=FIRST))
oracle.ops.mgmt.cluster.ClusterException: PRKC-1099 : The host names or IP addresses passed as an argument are null.

and finally

[AWT-EventQueue-0] [ 2017-06-13 13:55:00.331 CEST ] [ConfigureListenerOPS.isPortFree:1152]  PRKC-1099 : The host names or IP addresses passed as an argument are null.
[AWT-EventQueue-0] [ 2017-06-13 13:55:00.331 CEST ] [ConfigureListenerOPS.isPortFree:1153]  Failed to check if port 1534 is available on cluster nodes. !
[AWT-EventQueue-0] [ 2017-06-13 13:55:00.332 CEST ] [ConfigureListenerOPS.isPortFree:1161]  Is Port 1534 free on cluster nodes: false
oracle.net.ca.IllegalEndpointException: Port 1534 provided for this listener is already in use on node(s) of Oracle Clusterware.

RUBBISH!! The real reason is mentioned earlier in the logfile:
[Thread-8] [ 2017-06-13 13:54:38.899 CEST ] [StreamReader.run:65]  ERROR>No RSA host key is known for <hostname> and you have requested strict checking.
[Thread-8] [ 2017-06-13 13:54:38.899 CEST ] [StreamReader.run:65]  ERROR>Host key verification failed.
...
[main] [ 2017-06-13 13:54:38.901 CEST ] [UnixSystem.checkRemoteExecutionSetup:2417]  checkRemoteExecutionSetup:: Error checking user equivalence using Secured Shell '/usr/bin/ssh'
[main] [ 2017-06-13 13:54:38.908 CEST ] [HAUtils.getAccessibleNodes:3135]  <hostname> is not reachable PRKC-1044 : Failed to check remote command execution setup for node <hostname> using shells /usr/bin/ssh and /usr/bin/rsh
File "/usr/bin/rsh" does not exist on node "<hostname>"
No RSA host key is known for <hostname> and you have requested strict checking.Host key verification failed.

Solution

Long story told short: simply configure user equivalence:

# change to HOME directory:
cd

# create a directory called .ssh (if not existing)
mkdir .ssh

# change mod to 700
chmod 700 .ssh

# change directory to .ssh
cd .ssh

# create the RSA key file
ssh-keygen -t rsa

(accept the default location for the key file and confirm the passphrase (usually press enter twice to leave the passphrase empty))

# create the DSA keyfile
ssh-keygen -t dsa

(accept the default location for the key file and confirm the passphrase (usually press enter twice to leave the passphrase empty))

# create the authorized keys files (one with the hostname for better visibility what node information is in - in case You want to copy that file to other hosts)
cat *.pub >> authorized_keys.<hostname>
cp authorized_keys.<hostname> authorized_keys

# now, add the host to the list of the known_hosts (I used this command - other commands are available as well):
/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no  -o StrictHostKeyChecking=no  -o NumberOfPasswordPrompts=0 <hostname> /bin/true

# Result of the last command
Warning: Permanently added '<hostname>,<host's IP>' (RSA) to the list of known hosts.

You should now have the following files in Your .ssh directory
authorized_keys
authorized_keys.<hostname>
id_dsa
id_dsa.pub
id_rsa
id_rsa.pub
known_hosts

When You now try to create a new listener, everything works fine :-)

It could be that easy! ;-)






Share:

May 31, 2017

How to install and configure Oracle Forms & Reports 12c fully unattended - Part 2

Important update! (June 14th, 2017): 

Please asure that all variables in setLocalEnv.sh are filled in correctly and adapted to Your environment. This especially for all variables regarding the Database Connection, like DB_PWD, DB_HOST, DB_PORT ... If not set correctly, the tool is not able to create the rcu repository which results in an error during runtime: 

Problem invoking WLST - traceback(innermost last):
(no code object) at line 0
File "/opt/wls/oracle/crFRExtension.py", line 143
changeDataSourceToXA('localSvcTblDataSource')

--------------------------------------------------------------------------------------------------------------------------


Part 2?? Yes! Part 1 - which covers the installation of FMW Infrastructure and Forms / Reports - can be found at Dirk Nachbars Blog: How to install and configure Oracle Forms & Reports 12c fully unattended - Part 1

The Task:

You have to create several new Forms 12c Domains / Environments

The Problem:

All oracle is providing to create Forms Domains is a Graphical User Interface. And there is no possibility of a 'silent' domain creation. In other words: Oracle does not support 'silent' domain creation.
Disadvantages:

  • each domain creation will take some time and needs manual input. 
  • possibility of typos during the create process is given,
  • standardization - which is essential imho - is not supported anymore 
  • no unattended installation, 
  • and so on ... name it.

The Solution:

Dirk Nachbar (http://dirknachbar.blogspot.ch/) and I created a toolset, based on a script written by Jan Peter Timmermann (https://jan-peter.me/), to do the whole stuff using scripts. But, You should know that oracle does not support silent installations officially. Nevertheless, it works very well, fast, reliable and repeatable.

The set consists of separate scripts to
  • set an appropriate environment to create the domain
  • install FMW Infrastructure software silently - if necessary (see Dirk Nachbars Blog for details)
  • install Forms / Reports software silently - if necessary (see Dirk Nachbars Blog for details)
  • create a domain
  • add necessary components for Forms / Reports to the domain and to finally form the forms / reports domain
  • create the webtier and - finally - 
  • configure the domain using forms config helper (see Dirk Nachbars Blog for details)

The scripts:

Lets start with the first script in the row - the crDomain.sh script which starts the whole process:
#!/bin/bash
#=====================================================================
# 
# $Id: crDomain.sh $
# $Name: basenv-16.05.final.c $ 
#
# PURPOSE: Script to create an Oracle Forms & Report WebLogic Domain
#
# PARAMETERS: none, all required parameters are taken from setLocalEnv.sh
#
# AUTHOR:  EDOREX AG, Robert Crames,  2017
#
# Modified : TRIVADIS AG, Dirk Nachbar,  2017 
#
#
#=====================================================================
# set -x

# Set Start Time
start_time=$(date +%s)

echo ${DOMAIN_NAME}

source $PWD/setLocalEnv.sh

echo $ORACLE_HOME


function wait4something
{
echo " "
echo "ENTER to proceed ..."
read
echo "The Installation Process is started ..."
}

echo "======================================================================================"
echo " Program  : crDomain.sh                                                ........"
echo "======================================================================================"

if [ -z "${WLST_HOME}" ]; then
    echo "Environment not correctly set - please verify"
    exit 1
fi

#--> install Domain 
if ! test -d "${DOMAIN_BASE}/${DOMAIN_NAME}"; then
   echo "=================================================="
   echo "Domain will be installed ..."
   echo "=================================================="
   if [  -z "${ADM_PWD}"  -o -z "${TEMPLATE}" -o -z "${ADMINPORT}" -o -z "${MW}" -o -z "${ADMINPORTSSL}" -o -z " ${DOMAIN_NAME}" ]; then
      echo "Environment not set - Exit"
      exit 1
   fi

   # In case we are facing problems with /dev/random
   export CONFIG_JVM_ARGS=-Djava.security.egd=file:/dev/./urandom:$CONFIG_JVM_ARGS

   ${WLST_HOME}/wlst.sh ${SCRIPT_HOME}/crDomain.py 

   mkdir -p  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${AS_NAME}/security
   echo "username=${ADM_USER}" >  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${AS_NAME}/security/boot.properties
   echo "password=${ADM_PWD}" >> ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${AS_NAME}/security/boot.properties

   if [ "${FORMS12C}" == "true" ]  || [ "${REPORTS12C}" == "true" ]; then
      echo "===========================================" 
      echo "Oracle Forms and Reports  will be configured"
      echo "==========================================="
      cd ${SCRIPT_HOME}
      ${SCRIPT_HOME}/crFRExtension.sh
      echo "DOMAIN with Forms and Reports is created"
   fi

   if [ "${WEBTIER12C}" == "true" ]; then
      echo "===========================================" 
      echo "Oracle Webtier will be configured"
      echo "==========================================="
      nohup ${DOMAIN_BASE}/${DOMAIN_NAME}/bin/startNodeManager.sh > /dev/null 2>&1 &
      echo "==========================================="
      echo " Node Manager starting, wait for 30 seconds"
      echo "==========================================="
      sleep 30
      nohup ${DOMAIN_BASE}/${DOMAIN_NAME}/startWebLogic.sh > /dev/null 2>&1 &
      echo "==========================================="
      echo "Admin Server ${AS_NAME} starting, wait for 2 Minutes"
      echo "==========================================="
      sleep 120
      cd ${SCRIPT_HOME}
      ${SCRIPT_HOME}/crWebtierDomain.sh
      echo "DOMAIN with OHS is created"
   fi

   # Set End Time
     finish_time=$(date +%s)
     echo "Finished"
     echo "Domain Build Time: $(( $((finish_time - start_time))/60))  minutes."
     echo "Start Domain manually by using startWeblogic.sh"
   else
     echo "Domain is already installed ..."
   fi


The crDomain.sh sources a file, which is responsible to set the correct environment for the following processes: setLocalEnv.sh:
# Location to the directory in which the create Domain scripts are residing
export SCRIPT_HOME=$PWD

# Directories
export ORACLE_BASE=/appl/oracle
export INT_ORACLE_HOME=$ORACLE_BASE/product/fmw-fr-12.2.1.2.0
export WL_HOME=$INT_ORACLE_HOME/wlserver
export WLST_HOME=$INT_ORACLE_HOME/oracle_common/common/bin
export MW=$INT_ORACLE_HOME
export DOMAIN_BASE=$ORACLE_BASE/user_projects/domains
export APPLICATION_BASE=$ORACLE_BASE/user_projects/applications
export APP_VZ=$APPLICATION_BASE

# install forms true / false
export FORMS12C=true
# install reports true / false
export REPORTS12C=true
# install OHS true / false
export WEBTIER12C=true
export OHS_COMPONENTNAME=ohs1
export OHS_LISTENPORT=7777
export OHS_SSLPORT=4443

# Domain specific
export TEMPLATE=$WL_HOME/common/templates/wls/wls.jar
export DOMAIN_NAME=FRTEST

# AdminServer
export AS_NAME=FRTESTAdminServer
export ADM_USER=weblogic
export ADM_PWD=welcome1
export ADMINPORT=7001 
export ADMINPORTSSL=7101
export AS_HOST=`hostname -f`

# Name and Port for the Forms Managed Server
export FORMS_MS_NAME=MS_FORMS
export FORMS12C_MS_PORT=9001

# Name and Port for the Reports Managed Server
export REPORTS_MS_NAME=MS_REPORTS
export REPORTS12C_MS_PORT=9002

# Move Reports Application into WLS_FORMS (true or false)
export REPORTS_IN_FORMS=false

# NodeManager
export NM_LISTENADDRESS=`hostname -f`
export NM_TYPE=SSL
export NM_PORT=5556
export NM_USERNAME=nodemanager
export NM_PWD=welcome1

# Repository Connect
export DBUSER=sys
export DBPWD=<Password of user sys>
export DBROLE=SYSDBA
export COMPONENTPWD=<Password to be used for component access>
export SCHEMA_PREFIX=$DOMAIN_NAME
export DB_HOST=<Hostname>
export DB_PORT=<Port, the database is listening on>
export DB_SERVICE=<Servicename of the repository database>
export DB_OMF=<true or false - set only to true if the database is prepared for that>
export DB_USER_PW=<Password to be used for repository schema owners>
export PWDFILE=$SCRIPT_HOME/passwords.txt

Set these parameters to values according Your needs. The variable names are selfexplaining ...


When all parameters are set, crDomain.py is the script which creates the basic domain:
#!/usr/bin/python

# Things to do:
# Logausgabe in File via redirect()

import os, sys
v_asName=os.environ['AS_NAME']
v_asUser=os.environ['ADM_USER']
v_webpw=os.environ['ADM_PWD']
v_adminport=os.environ['ADMINPORT']
v_adminportssl=os.environ['ADMINPORTSSL']
v_domainName=os.environ['DOMAIN_NAME']
v_template=os.environ['TEMPLATE']
v_javaHome=os.environ['JAVA_HOME']
v_setup_domain_base=os.environ['DOMAIN_BASE']
v_setup_application_base=os.environ['APPLICATION_BASE']
v_OracleHome=os.environ['INT_ORACLE_HOME']
v_dbhost=os.environ['DB_HOST']
v_dbport=os.environ['DB_PORT']
v_dbservice=os.environ['DB_SERVICE']
v_rcudbstr=v_dbhost+":"+v_dbport+":"+v_dbservice
v_SchemaPrefix=os.environ['SCHEMA_PREFIX']
v_pwdfile=os.environ['PWDFILE']
v_dbUser=os.environ['DBUSER']
v_dbPwd=os.environ['DBPWD']
v_dbRole=os.environ['DBROLE']
v_dbOmf=os.environ['DB_OMF']
v_componentPassword=os.environ['COMPONENTPWD']

def printHeader(headerText):
    print "\n======================================================================================"
    print "--> "+headerText
    print "======================================================================================\n"

def printInfo(infoText):
    print "-->: "+infoText

printHeader("Started: crDomain.py")

printHeader("Create password file")
os.system("echo "+v_dbPwd+">"+v_pwdfile)
os.system("echo " +v_componentPassword+ ">>"+v_pwdfile)
os.system("echo " +v_componentPassword+ ">>"+v_pwdfile)
os.system("echo " +v_componentPassword+ ">>"+v_pwdfile)
os.system("echo " +v_componentPassword+ ">>"+v_pwdfile)
os.system("echo " +v_componentPassword+ ">>"+v_pwdfile)

printHeader("Step: create repository - started")
printInfo("Drop repository "+v_SchemaPrefix)
os.system(v_OracleHome + "/oracle_common/bin/rcu -silent -dropRepository -databaseType ORACLE -connectString "+ v_rcudbstr +" -dbUser "+v_dbUser+" -dbRole "+v_dbRole+" -schemaPrefix "+v_SchemaPrefix+" -component STB -component IAU -component IAU_APPEND -component IAU_VIEWER -component OPSS -f <"+v_pwdfile)
printInfo("Repository "+v_SchemaPrefix+" dropped")

printInfo("Create repository "+v_SchemaPrefix+" - started")
os.system(v_OracleHome + "/oracle_common/bin/rcu -silent -createRepository -honorOMF "+ v_dbOmf +" -connectString "+ v_rcudbstr +" -dbUser "+v_dbUser+" -dbRole "+v_dbRole+" -useSamePasswordForAllSchemaUsers true -schemaPrefix "+v_SchemaPrefix+" -component STB -component IAU -component IAU_APPEND -component IAU_VIEWER -component OPSS -f < "+v_pwdfile)
printInfo("Repository "+v_SchemaPrefix+" created")

printHeader("Step: Read default template (always wls.jar!!)")
readTemplate(v_template)
printInfo("Template: "+v_template+" successfully read")   

printHeader("Step: Prepare Domain --> Set Domain Name to"+v_domainName)
cd('/')
cmo.setName(v_domainName)
printInfo("Step: Set Domain Name --> Successful")

printHeader("Step: Prepare Domain --> Set User Password")
cd('/')
cd('/Security/'+v_domainName+'/User/'+v_asUser)
cmo.setPassword(v_webpw)
printInfo("Step: Set User Password --> Successful")

printHeader('Step: Prepare Domain --> Set AdminServer Name ('+v_asName+') and Port ('+v_adminport+')')
cd('/')
cd('/Server/AdminServer')
cmo.setName(v_asName)
cd('/')
cd('/Server/'+v_asName)
cmo.setListenPort(int(v_adminport))
printInfo("Step: Set AdminServer Name --> Successful")

printHeader("Step: Prepare Domain --> Set Domain Properties")
setOption('OverwriteDomain', 'true')
setOption('ServerStartMode','prod')
printInfo("Step: Set Domain Properties --> Successful")

printHeader("Step: Prepare Domain --> Set JAVA_HOME in Domain")
setOption('JavaHome',v_javaHome)
printInfo("Step: Set JAVA_HOME in Domain --> Successful")

printHeader("Step: writeDomain and closeTemplate")
writeDomain(v_setup_domain_base+'/'+v_domainName)
closeTemplate()
printInfo("Step: writeDomain and closeTemplate --> Successful")

printHeader("Finished: crDomain.py")


Next in line is the script, to add forms / reports specific stuff to the domain. We called it: crFRExtension.sh (which calls crFRExtension.py):
#!/bin/bash
#=====================================================================
# 
# $Id: crDomain.sh $
# $Name: basenv-16.05.final.c $ 
#
# PURPOSE: Script to a WebLogic Domain with Forms & Reports
#          Script will be called by crDomain.sh
#
# PARAMETERS: none, all required parameters are taken from setLocalEnv.sh
#
# AUTHOR:  EDOREX AG, Robert Crames,  2017
#
# Modified : TRIVADIS AG, Dirk Nachbar,  2017 
#
#
#=====================================================================
echo "======================================================================================"
echo " Program  : crFRExtension.sh                                    ........"
echo "======================================================================================"

     # In case we are facing problems with /dev/random
     export CONFIG_JVM_ARGS=-Djava.security.egd=file:/dev/./urandom:$CONFIG_JVM_ARGS

     ${WLST_HOME}/wlst.sh ${SCRIPT_HOME}/crFRExtension.py 

     if [ "${FORMS12C}" == "true" ]; then
         mkdir -p  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${FORMS_MS_NAME}/security
         echo "username=${ADM_USER}" >  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${FORMS_MS_NAME}/security/boot.properties
         echo "password=${ADM_PWD}" >> ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${FORMS_MS_NAME}/security/boot.properties
     fi


     if [ "${REPORTS12C}" == "true" ]; then
        mkdir -p  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${REPORTS_MS_NAME}/security
        echo "username=${ADM_USER}" >  ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${REPORTS_MS_NAME}/security/boot.properties
        echo "password=${ADM_PWD}" >> ${DOMAIN_BASE}/${DOMAIN_NAME}/servers/${REPORTS_MS_NAME}/security/boot.properties
     fi


crFRExtension.py:
#!/usr/bin/python

# Things to do:
#

import os, sys
v_forms=os.environ['FORMS12C']
v_formsMSName=os.environ['FORMS_MS_NAME']
v_formsPort=os.environ['FORMS12C_MS_PORT']
v_reports=os.environ['REPORTS12C']
v_reportsMSName=os.environ['REPORTS_MS_NAME']
v_reportsPort=os.environ['REPORTS12C_MS_PORT']
v_NMListenAddress=os.environ['NM_LISTENADDRESS']
v_NMType=os.environ['NM_TYPE']
v_NMPort=os.environ['NM_PORT']
v_NMUsername=os.environ['NM_USERNAME']
v_NMPwd=os.environ['NM_PWD']
v_domain=os.environ['DOMAIN_NAME']
v_dbhost=os.environ['DB_HOST']
v_dbport=os.environ['DB_PORT']
v_dbservice=os.environ['DB_SERVICE']
v_url="jdbc:oracle:thin:@//"+v_dbhost+':'+v_dbport+'/'+v_dbservice
v_pwd=os.environ['DB_USER_PW']
v_SchemaPrefix=os.environ['SCHEMA_PREFIX']
v_setup_domain_base=os.environ['DOMAIN_BASE']
v_setup_application_base=os.environ['APPLICATION_BASE']
v_reports_in_forms=os.environ['REPORTS_IN_FORMS']

def changeDatasourceToXA(datasource):
  print 'Change datasource '+datasource
  cd('/')
  cd('/JDBCSystemResource/'+datasource+'/JdbcResource/'+datasource+'/JDBCDriverParams/NO_NAME_0')
  set('DriverName','oracle.jdbc.xa.client.OracleXADataSource')
  set('UseXADataSourceInterface','True')
  cd('/JDBCSystemResource/'+datasource+'/JdbcResource/'+datasource+'/JDBCDataSourceParams/NO_NAME_0')
  set('GlobalTransactionsProtocol','TwoPhaseCommit')
  cd('/')

def printHeader(headerText):
    print "\n======================================================================================"
    print "--> "+headerText
    print "======================================================================================\n"

def printInfo(infoText):
    print "-->: "+infoText

printHeader("Started: crFRExtension.py")

try:
    if not ( v_forms == "true" or v_reports == "true" ):
       printInfo( "Forms and Reports flag are set to false")
       printInfo( "We will stop processing, please activate Forms or Reports")
       exit()
    printHeader( "readDomain "+v_domain+" started")
    readDomain(v_setup_domain_base+'/'+v_domain)
    printInfo( "readDomain successful")
except:
    printInfo( "readDomain failed")
    exit()

try:
    printHeader( "select and load templates")
    selectTemplate('Oracle HTTP Server (Collocated)')
    if v_forms == "true":
       selectTemplate('Oracle Forms')
    if v_reports == "true":
       selectTemplate('Oracle Reports Application')
       selectTemplate('Oracle Reports Server')
    printInfo( "select templates successful")
    loadTemplates()
    printInfo( "load templates successful")
except:
    printInfo( "select and load templates failed")
    exit()

printHeader("JDBC configuration")
try:
    printInfo("Configure LocalSvcTblDataSource")
    cd('/')
    cd('/JDBCSystemResource/LocalSvcTblDataSource/JdbcResource/LocalSvcTblDataSource')
    cd('JDBCDriverParams/NO_NAME_0')
    set('DriverName','oracle.jdbc.OracleDriver')
    set('URL', v_url)
    set('PasswordEncrypted', v_pwd)
    cd('Properties/NO_NAME_0')
    cd('Property/user')
    cmo.setValue(v_SchemaPrefix+'_STB')
    printInfo("Configure LocalSvcTblDataSource successful")
except:
    printInfo("Configure LocalSvcTblDataSource failed")
    exit()

try:
    printInfo("Configure opss-data-source")
    cd('/')
    cd('JDBCSystemResource/opss-data-source/JdbcResource/opss-data-source')
    cd('JDBCDriverParams/NO_NAME_0')
    set('DriverName','oracle.jdbc.OracleDriver')
    set('URL', v_url)
    set('PasswordEncrypted', v_pwd)
    cd('Properties/NO_NAME_0')
    cd('Property/user')
    cmo.setValue(v_SchemaPrefix+'_OPSS')
    printInfo("Configure opss-data-source successful")
except:
    printInfo("Configure opss-data-source failed")
    exit()

try:
    printInfo("Configure opss-audit-viewDS")
    cd('/')
    cd('JDBCSystemResource/opss-audit-viewDS/JdbcResource/opss-audit-viewDS')
    cd('JDBCDriverParams/NO_NAME_0')
    set('DriverName','oracle.jdbc.OracleDriver')
    set('URL', v_url)
    set('PasswordEncrypted', v_pwd)
    cd('Properties/NO_NAME_0')
    cd('Property/user')
    cmo.setValue(v_SchemaPrefix+'_IAU_VIEWER')
    printInfo("Configure opss-audit-viewDS successful")
except:
    printInfo("Configure opss-audit-viewDS failed")
    exit()

try:
    printInfo("Configure opss-audit-DBDS")
    cd('/')
    cd('JDBCSystemResource/opss-audit-DBDS/JdbcResource/opss-audit-DBDS')
    cd('JDBCDriverParams/NO_NAME_0')
    set('DriverName','oracle.jdbc.OracleDriver')
    set('URL', v_url)
    set('PasswordEncrypted', v_pwd)
    cd('Properties/NO_NAME_0')
    cd('Property/user')
    cmo.setValue(v_SchemaPrefix+'_IAU_APPEND')
    printInfo("Configure opss-audit-DBDS successful")
except:
    printInfo("Configure opss-audit-DBDS failed")
    exit()

try:
   printInfo("Modify Datasources: LocalSvcTblDataSource , opss-audit-DBDS, opss-audit-viewDS , opss-data-source")
   changeDatasourceToXA('LocalSvcTblDataSource')
   changeDatasourceToXA('opss-audit-DBDS')
   changeDatasourceToXA('opss-audit-viewDS')
   changeDatasourceToXA('opss-data-source')
   printInfo("Modify Datasources successful")
except:
   printInfo("Modify Datasources failed")

printHeader('Customize Domain Settings')
try:
    printInfo("Name and Ports of the Managed Servers will be modified")
    if v_forms == "true":
       cd('/')
       cd('/Server/WLS_FORMS')
       cmo.setName(v_formsMSName)
       cd('/')
       cd('/Server/'+v_formsMSName)
       cmo.setListenPort(int(v_formsPort))
    if v_reports == "true":
       cd('/')
       cd('/Server/WLS_REPORTS')
       cmo.setName(v_reportsMSName)
       cd('/Server/'+v_reportsMSName)
       cmo.setListenPort(int(v_reportsPort))
    printInfo("Modification of Name and Ports are successful")
except:
    printInfo("ERROR: Modification of Name and Ports are failed")

try:
    if v_reports_in_forms == 'false':
        printInfo("Reports are remaining in own Managed Server")
except:
    printInfo("x")

try:
    if v_reports_in_forms == 'true':
        printInfo("Trying Move Reports into Forms Managed Server")
        cd('/')
        cd('/AppDeployments/reports#12.2.1')
        set('Target','cluster_forms')
        cd('/')
        cd('/Library/oracle.reports.applib#12.2.1@12.2.1')
        set('Target','cluster_forms')
        printInfo("Move Reports into Forms Managed Server are successful")
except:
    printInfo("ERROR: Move Reports into Forms Managed Server failed")

try:
    printHeader("Nodemanager Configuration")
    cd('/')
    cd('/Machines/AdminServerMachine/NodeManager/AdminServerMachine')
    cmo.setNMType(v_NMType)
    cmo.setListenAddress(v_NMListenAddress)
    cmo.setListenPort(int(v_NMPort))
    cd('/')
    cd('/SecurityConfiguration/'+v_domain)
    cmo.setNodeManagerUsername(v_NMUsername)
    cmo.setNodeManagerPasswordEncrypted(v_NMPwd)
    printInfo("Nodemanager Configuration successful")
except:
    printInfo("ERROR: Nodemanager Configuration failed")

try:
    printHeader("AppDir will be set to "+v_setup_application_base)
    try:
        setOption('AppDir',v_setup_application_base)
    except Exception, e:
        print "Error Message "+ str(e)

    printInfo("Domain will be updated and saved")
    printInfo("... this can take up to 5 minutes")
    updateDomain()
    closeDomain()
    printHeader("Program End: crFRExtension.py")
    print "======================================================================================"

    exit()
except:
   print "Domain could not be saved"



After all, it's time to create the Webtier ... using crWebtierDomain.sh / crWebtierDomain.py:
#!/bin/bash
#=====================================================================
#
# $Id: crWebtierDomain.sh $
# $Name: basenv-16.05.final.c $
#
# PURPOSE: Script to configure Oracle HTTP Server for Oracle Forms & Reports
#
# PARAMETERS: none, all required parameters are taken from setLocalEnv.sh
#
# AUTHOR:  TRIVADIS AG, Dirk Nachbar,  2017
#
# Modified : 
#
#
#=====================================================================


echo "======================================================================================"
echo " Program  : crWebtierDomain.sh                                                ........"
echo "======================================================================================"

# Check the required Environment Variables for OHS COnfiguration
if [  -z "${OHS_COMPONENTNAME}"  -o -z "${OHS_LISTENPORT}" -o -z "${OHS_SSLPORT}" ]; then
   echo "Environment not set - Exit"
   exit 1
fi

# In case we are facing problems with /dev/random
export CONFIG_JVM_ARGS=-Djava.security.egd=file:/dev/./urandom:$CONFIG_JVM_ARGS

${WLST_HOME}/wlst.sh ${SCRIPT_HOME}/crWebtierDomain.py


echo "======================================================================================"
echo "Starting newly create OHS Component ${OHS_COMPONENTNAME} "
echo "======================================================================================"

echo ${NM_PWD} | ${DOMAIN_BASE}/${DOMAIN_NAME}/bin/startComponent.sh ${OHS_COMPONENTNAME} storeUserConfig

crWebtierDomain.py:

#!/usr/bin/python

# Things to do:
#

import os, sys
v_asUser=os.environ['ADM_USER']
v_webpw=os.environ['ADM_PWD']
v_adminport=os.environ['ADMINPORT']
v_as_host=os.environ['AS_HOST']
v_ohs_componentname=os.environ['OHS_COMPONENTNAME']
v_ohs_listenport=os.environ['OHS_LISTENPORT']
v_ohs_sslport=os.environ['OHS_SSLPORT']

connect(v_asUser, v_webpw, v_as_host+':'+v_adminport)

ohs_createInstance(instanceName=v_ohs_componentname, machine='AdminServerMachine', listenPort=v_ohs_listenport, sslPort=v_ohs_sslport)

That's it - basically ... If You want to know, how to install the necessary software (FMW Infrastructure plus Forms Reports) and to configure the domain using forms config helper, please have a look at Dirk Nachbar's Blog --> How to install and configure Oracle Forms & Reports 12c fully unattended - Part 1

Please leave comments, requests for enhancements or hints (in case we could do something better or with less overhead). 

Special thanks to Jan Peter Timmermann who shared important information, and by doing that, gave the initial input for the script set.


Share:

May 18, 2017

Forms Application shows a 'FRM-40735: WHEN-CUSTOM-ITEM-EVENT trigger raised unhandled exception ORA-06508' after upgrade to 11.1.2.2.0

Situation:

After an upgrade from 11.1.2.1.0 to 11.1.2.2.0 and a patch apply of patch 19933795 (see post Forms Application does not run anymore after java upgrade to 1.8_131 on client), the application shows an 'FRM-40735: WHEN-CUSTOM-ITEM-EVENT trigger raised unhandled exception ORA-06508':



Solution:

I had to recreate the webutil repository (create_webutil_db.sql) and to recompile webutil.pll. After that, the application runs fine again ...

Important!!: Compile webutil.pll in your FORMS_HOME path and leave the webutil.plx there. In case of a different FORMS_PATH for an application, simply copy webutil.pll and webutil.plx to that path. Interestingly, the webutil.plx is expected in FORMS_HOME, even if webutil paths are set to an application specific path ...

Share:

May 17, 2017

Coming soon: How to create Forms / Reports Domains by using scripts (Non-GUI)

With Forms Reports 12c, Oracle does not support anymore the creation of Forms / Reports domains using a 'silent' mechanism. That isn't a problem when creating only a few domains - but when Your intention is standardization or You have to create several domains, a CLI based 'Forms Domain Config' is a must.

Based on information, I got from Jan-Peter Timmermann (Jan-Peter's Weblog), I developed a scriptset to create a complete Forms Reports Domain 'silently'. Finally, Dirk Nachbar (Dirk Nachbar: Oracle Fusion Middleware & Application Server) has added some more functionality. The result will be shown here and on Dirk's Blog in a few days.

stay tuned for the upcoming post :-)


Share:

May 9, 2017

ODA X4 - Migrating a database from ASM to ACFS

As promised ... Here's the post about migrating a database from ASM to ACFS on an ODA 4 - probably, this will work for newer ODA Servers as well

Given:

- Oracle Database Appliance machine, X4-<something>
- Software Release 12.1.2.7.0
- Prepared DB Software: 11.2.0.3, 11.2.0.4, 12.1.0.2

Story behind:

Task was, to create a new database of release 11.2.0.3 for testing purposes. Command used to create the database:

oakcli create database -db TDB -oh /u01/app/oracle/product/11.2.0.3/dbhome_1
After that, I started configuring the database, creating tablespaces, schemas, setting parameters, and so on ... lots of work, actually.

What I expected was a database using ACFS as storage option (as all the other databases on that system) - what I got was ASM ... this, because starting with software release 12.1.2.0 the default storage option is ACFS for all databases of release 11.2.0.4 an higher. In other words: creating a database 11.2.0.3, using oakcli, will result in a database using ASM. At that stage, I had two possibilities: Drop the database and recreate it - or migrate an ASM DB to ACFS. Decision was: ACFS :-)

Found a document which describes that in very detail: STEPS TO MIGRATE NON-CDB DATABASES TO ACFS ON ORACLE DATABASE APPLIANCE 12.1.2 . I used it, but found a few bugs in that doc and decided to write a blog post to present a shorter story.

Step 1 - create an empty snapshot (as user root)

To prepare the filesystem / directory structure for the new ACFS based database and to be able to use the snapshot feature, one have to create an empty snapshot
acfsutil snap create -w TDB /u02/app/oracle/oradata/datastore

Step 2 - ACFS: create directories to store the datafiles and other stuff (as user root)

mkdir /u01/app/oracle/oradata/datastore/TDB
mkdir /u01/app/oracle/fast_recovery_area/datastore/TDB
mkdir /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB
chown oracle:oinstall /u01/app/oracle/oradata/datastore/TDB
chown oracle:oinstall /u01/app/oracle/fast_recovery_area/datastore/TDB
chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB

Step 3 - File destinations: prepare the spfile (as user oracle - sql)

alter system set db_create_file_dest='/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_2='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/datastore/TDB' scope=both sid='*';

Step 4 - Migrate data files, control files and online redo logs (as user oracle)

First, copy the data files to the new destination by using rman and the 'copy database to destination' feature, and switch the database to the copy:
<Prompt>: rman target /

RMAN> startup mount
...
RMAN> backup as copy database to destination '/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB'; ...
RMAN> switch database to copy;
...
RMAN> exit
Next, get the current control file name(s), using sqlplus, and reset the control files
<Prompt>:  sqlplus / as sysdba
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+REDO/TDB/controlfile/current.266.943174969
SQL> alter system reset control_files;
System altered.
SQL> exit
Restore the control file to ACFS (with rman)
<Prompt>:  rman target /
RMAN> shutdown immediate
...
RMAN> startup nomount
...
RMAN> restore controlfile from '+REDO/TDB/controlfile/current.266.943174969';
...
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl
Finished restore at 05-MAY-17

RMAN> exit
Change the control_files parameter in the database's spfile
<Prompt>:  sqlplus / as sysdba
SQL> alter system set control_files='/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl','/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl' scope=spfile sid='*';

System altered.
Last activity in this step: Migrate the online redo logs. Open sqlplus and execute the following:
declare
 cursor rlc is
   select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
     from v$log
   union
   select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
     from v$standby_log
     order by 1;
 stmt varchar2(2048);
 swtstmt varchar2(1024) := 'alter system switch logfile';
 archstmt varchar2(1024) := 'alter system archive log current';
 ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
 for rlcRec in rlc loop
   if (rlcRec.srl = 'YES') then
     stmt := 'alter database add standby logfile thread ' ||
       rlcRec.thr || ' size ' ||
       rlcRec.bytes_k || 'K';
     execute immediate stmt;
     stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
     execute immediate stmt;
   else
     stmt := 'alter database add logfile thread ' || rlcRec.thr || ' size ' || rlcRec.bytes_k || 'K';
      dbms_output.put_line(stmt);
     execute immediate stmt;
     begin
       stmt := 'alter database drop logfile group ' || rlcRec.grp;
       dbms_output.put_line(stmt);
       execute immediate stmt;
     exception
       when others then
         execute immediate swtstmt;
         execute immediate archstmt;
         execute immediate ckpstmt;
         execute immediate stmt;
     end;
   end if;
 end loop;
end;
/

What about the Tempfiles? This (as user oracle):

<Prompt>:  sqlplus / as sysdba
-- get ddl of current TEMP
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

-- rename the tablespace

alter tablespace temp rename to temp_old;

-- create a new TEMP by using the information from the GET_DDL

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 1468006400 AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

-- make the new tablespace to the new default TEMP

alter database default temporary tablespace TEMP;

-- drop old temp tablespace

drop tablespace temp_old;

Move spfile and password file to the new location and switch off ASM usage (as user root)

First, move spfile and pwdfile from ASM to ACFS. Use asmcmd to get current spfile and password file names
<Prompt>: su - grid -c "asmcmd"
ASMCMD> ls -l DATA/TDB

copy the spfile to the new location spfile (and the password file)
su - grid -c "asmcmd cp '+DATA/TDB/PARAMETERFILE/spfile.285.943175449' /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora"

do not forget to change the owner:group for the new files:
chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora

spfile has been copied to the new directory - but the database is not aware of that ...:
srvctl config database -d TDB
Database unique name: TDB
...

Change spfile location in the database config (the -p option) and tell the DB that it does not need Disk Groups anymore (the -z option):
srvctl modify database -d TDB -p /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora -z

Check config:
Database unique name: TDB
...
Spfile: /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora
...
Disk Groups: 
...
Database is administrator managed

Stop the database and start again
srvctl stop database -d TDB
srvctl start database -d TDB

Final steps - clean up (as user oracle)

If the DB is running properly and the alert log does not show any problems, than it's time to clean up and remove the old datafile copies
<Prompt>: rman target /
RMAN> list datafilecopy all;
...
List of Datafile Copies
=======================
...
For each datafile copy
RMAN> delete noprompt datafilecopy '<name as shown in list datafilecopy>';

Conclusion

Several steps to execute - but it's working fine. Can save a lot of time and makes a export / import scenario obsolete.



Share:

Copyright © Robert Crames' Oracle Database and Middleware Blog | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com