clone.pl is deprecated, substituted by runInstaller - how to use

1. create a "Golden Image" of the Oracle software home you want to clone

oracle@testserver1.oric.no:[testdb01]# /sw/oracle/product/19c_230117/runInstaller -silent -createGoldImage -destinationLocation /sw/oracle/product
Launching Oracle Database Setup Wizard...

Successfully Setup Software.
Gold Image location: /sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip

Note that I use the -silent flag. Without it, I received the following two errors:

ERROR: Could not read any NLS message catalogue!

and

Can't connect to X11 window server using 'localhost:10.0' as the value of the DISPLAY variable.

2. Transfer the zip file to your destination server using scp

ssh testserver2.oric.no
su - oracle
cd /sw/oracle/product
scp testserver1:/sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip .

3. Make a directory for the new ORACLE_HOME

mkdir 19c_230117

4. Unzip the image file

unzip -d 19c_230117 db_home_2023-01-24_04-05-28PM.zip

5. create a simple script for the installation process

vi exec_runInstaller.sh; chmod 755 exec_runInstaller.sh

Add the following to exec_runInstaller.sh:

#!/bin/bash
export GLOGFILE=/sw/oracle/product/exec_runInstaller.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "starting installer at " `date`
export CV_ASSUME_DISTID=OEL8.1
export NEW_ORACLE_HOME=/sw/oracle/product/19c_230117
cd $NEW_ORACLE_HOME
./runInstaller -silent -debug -force \
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=dba \
ORACLE_HOME=$NEW_ORACLE_HOME \
ORACLE_BASE=$ORACLE_BASE \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
DECLINE_SECURITY_UPDATES=true

exit

Note that I enter the newly unpacked ORACLE_HOME to execute runInstaller.

6. execute the script

./exec_runInstaller.sh

7. Run root scripts

Upon completion, check the logfile /sw/oracle/product/install_sw_${TS}.log and you are reminded to run two root scripts:

You can find the log of this install session at:
 /home/oracle/oraInventory/logs/InstallActions2023-01-25_10-04-01AM/installActions2023-01-25_10-04-01AM.log

As a root user, execute the following script(s):
        1. /sw/oracle/product/19c_230117/root.sh

Execute /sw/oracle/product/19c_230117/root.sh on the following nodes:
[testserver02.oric.no]

8. Verify that the new ORACLE_HOME is registered in the inventory

cat  /home/oracle/oraInventory/ContentsXML/inventory.xml

Output (abbreviated):

<HOME NAME="OraDB19Home1" LOC="/sw/oracle/product/19c_230117" TYPE="O" IDX="3"/>

How to work around error [INS-08101] Unexpected error while executing the action at state: ‘supportedOSCheck’

Thanks so much to Martin Berger for his blog post showing how to work around an error that shows up when you attempt to install Oracle 19c software on a RHEL8 distribution.

I wanted to install Oracle software on a RH8.5 Linux server:

cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)
  

This is the error I received:

    

In my case, the only thing I had to do was to add another environmental variable:

export CV_ASSUME_DISTID=OEL7.8

Execute the installer again and you will see a different screen:

./runInstaller

How to attach an ORACLE_HOME to an existing inventory

I wanted to check the patchlevel in one of my Oracle installations, and the following errow was returned:

oracle@tsl0map-dbteam-sandbox-db04:[vegdb01]# opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/12102
Central Inventory : /home/oracle/oraInventory
   from           : /u01/oracle/product/12102/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/oracle/product/12102/cfgtoollogs/opatch/opatch2017-09-12_15-30-47PM_1.log

List of Homes on this system:

  Home name= agent13c2, Location= "/u01/oracle/product/agent13c/agent_13.2.0.0.0"
  Home name= 11204, Location= "/u01/oracle/product/11204"
  Home name= OraHome3, Location= "/u01/oracle/product/gg121"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Turned out my inventory had not been updated with my new Oracle Home. When I looked in the inventory.xml file on my server, there was no entry for the installation there.

To fix this, add the new OH to your inventory. From your OH that is missing, do the following:

cd $ORACLE_HOME/oui/bin
./runInstaller -invPtrLoc /u01/oracle/product/12102/oraInst.loc -attachHome ORACLE_HOME=/u01/oracle/product/12102 ORACLE_HOME_NAME="Ora12cHome"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8191 MB    Passed
The inventory pointer is located at /u01/oracle/product/12102/oraInst.loc
'AttachHome' was successful.

Check the inventory file now, and you'll see a new entry for the OH (in my case the Ora12cHome):

<HOME NAME="agent13c2" LOC="/u01/oracle/product/agent13c/agent_13.2.0.0.0" TYPE="O" IDX="8"/>
<HOME NAME="11204" LOC="/u01/oracle/product/11204" TYPE="O" IDX="1"/>
<HOME NAME="Ora12cHome" LOC="/u01/oracle/product/12102" TYPE="O" IDX="9"/>

How to solve "INS-32035: The chosen installation conflicts with software already installed in the given oracle home" when installing into an old directory

Workaround for the error

INS-32035: The chosen installation conflicts with software already installed in the given oracle home

from the Oracle Universal Installer:

1. detatch the home from the ORACLE_HOME you install from:

cd $current_oracle_home/oui/bin
./runInstaller -detachHome ORACLE_HOME=/u01/oracle/product/11204 invPtrLoc=/u01/oracle/product/11201/oraInst.loc

2. edit the file /u01/oraInventory/ContentsXML/inventory.xml

In my case from:

<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home2" LOC="/u01/oracle/product/db/11204" TYPE="O" IDX="2"/>
</HOME_LIST>

to

<HOME_LIST>
<HOME NAME="OraDB11g_home1" LOC="/u01/oracle/product/db/11201" TYPE="O" IDX="1"/>
</HOME_LIST> 

3. Delete the files in /u01/oracle/product/db/11204 physically from disk, before you try again.

That's it.
Universal Installer should now recognize the selected directory, and not give you any complaints when reinstalling into the same directory.

Automatic startup and shutdown script for Oracle databases on the Linux Platform

On most installations of the Oracle database, it is desirable to add a script for automatic startup and shutdown.
This is how to do it.
The instructions holds true for both 11g and 12c.

1. Edit the /etc/oratab file, so that the entry for your database has a Y at the end:

testdb01:/u01/oracle/product/db/112:Y

2. cd /etc/init.d

3. create a file called dbora, and add the following lines to it. Note that you should enter the name of your ORACLE_HOME path and the oracle software installation owner as values for ORA_HOME and ORA_OWNER, respectively:

#! /bin/sh 
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORA_HOME=/u01/oracle/product/db/112
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

4. Set permissions:

# chgrp dba dbora
# chmod 750 dbora

5. Create symlinks to the different run level script directories:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Verify the symlinks. They should all point back to /etc/init.d/dbora

ls -la /etc/rc.d/rc0.d/K01dbora
ls -la /etc/rc.d/rc3.d/S99dbora
ls -la /etc/rc.d/rc5.d/S99dbora

What about the listener?
According to Oracle, as long as it has the default name of "LISTENER", you don't need to add it to your startup/shutdown script. If you use a different name, you must add the following line to the /etc/init.d/dbora script:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

The original instructions from Oracle can be found here

How to identifiy whether or not Oracle Configuration Manager is in use

Change directory to $ORACLE_HOME

# cd $ORACLE_HOME
# ls ccr
bin  config  doc  hosts  inventory  lib  README.pdf  state

If the directory ccr exist, OCM has been installed.

Change directory to ccr/bin:

# cd ccr/bin
# ls -la
drwxr-xr-x 2 oracle dba  4096 Feb 29 16:11 .
drwxr-xr-x 9 oracle dba  4096 Feb 29 16:11 ..
-rwx------ 1 oracle dba 19169 Feb 29 16:11 common
-rw------- 1 oracle dba 47285 Feb 29 16:11 deployPackages
-rwx------ 1 oracle dba  9063 Feb 29 16:11 emocmrsp
-rwx------ 1 oracle dba  5881 Feb 29 16:11 emSnapshotEnv
-rwx------ 1 oracle dba  6116 Feb 29 16:11 lockfile
-rwxr----- 1 oracle dba  1630 Feb 29 16:11 ocmJarUtil
-rw-r--r-- 1 oracle dba  3539 Feb 29 16:11 OCMJarUtil.class
-rwx------ 1 oracle dba 58374 Feb 29 16:11 setupCCR
-rw-r--r-- 1 oracle dba   903 Feb 29 16:11 strip_path.awk

If the file emCCR exists, OCM has been configured.

If not, like in my case above, OCM is installed but not configured for this server.

What is the package SYS.USER_LOCK?

Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code:

SQL> ALTER PACKAGE mypackage compile body; 

Warning: Package body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MVA3.MYPACKAGE:

PLS-00201: identifier 'USER_LOCK.SLEEP' must be declared

According to PSOUG, there are "four separate ways to induce a sleep into PL/SQL."

•dbms_backup_restore
•dbms_drs
•dbms_lock
•user_lock


PSOUG continues: "Note that USER_LOCK package is not part of the default Oracle installation"

Install as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba @userlock.sql

How to work around error from opatch "LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo"

I recently had to investigate the patch level for an Oracle 10g database prior to an upgrade of an OAS application database.

This is the error I ran into:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Fix this by running the attachHome.sh script in $ORACLE_HOME/oui/bin.
The Oracle Universal Installer allows you to set up an Inventory from scratch, or to register an existing ORACLE_HOME in an already existing Inventory.

myserver:asdb:ora10g>cd $ORACLE_HOME/oui/bin
/u01/oracle/product/ora10g/oui/bin
myserver:asdb:ora10g>./attachHome.sh
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/oracle/oraInventory11g
'AttachHome' was successful.

Result:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/ora10g
Central Inventory : /u01/oracle/oraInventory11g
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/oracle/product/ora10g/oui
Log file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/opatch2016-01-18_16-01-17PM.log

Lsinventory Output file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2016-01-18_16-01-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (4):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 4 products installed in this Oracle Home.


Interim patches (3) :

Patch  6979801      : applied on Fri Mar 06 10:47:02 CET 2009
   Created on 5 Feb 2009, 15:35:25 hrs PST8PDT
   Bugs fixed:
     6979801

Patch  6972843      : applied on Tue Oct 14 17:35:12 CEST 2008
   Created on 25 Jun 2008, 08:31:04 hrs UTC
   Bugs fixed:
     6972843

Patch  6823287      : applied on Tue Oct 14 17:26:32 CEST 2008
   Created on 30 Jun 2008, 10:27:42 hrs UTC
   Bugs fixed:
     6823287



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

OPatch succeeded.

How to unzip multiple files on a Unix-based operating systems

When extracting multiple zip files, remember to use single quotes to mask the zip-files:

unzip '*.zip'

For example, when unpacking the files needed to install Oracle 11.2.0.4:

[oracle@lx01 Downloads]$ ls -latr
total 5586768
-rwxr-x---.  1 root   root     1395582860 Sep  2 10:14 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-x---.  1 root   root     1151304589 Sep  2 10:14 p13390677_112040_Linux-x86-64_2of7.zip
-rwxr-x---.  1 root   root     1205251894 Sep  2 10:15 p13390677_112040_Linux-x86-64_3of7.zip
-rwxr-x---.  1 root   root      656026876 Sep  2 10:16 p13390677_112040_Linux-x86-64_4of7.zip
-rwxr-x---.  1 root   root      599170344 Sep  2 10:16 p13390677_112040_Linux-x86-64_5of7.zip
-rwxr-x---.  1 root   root      488372844 Sep  2 10:16 p13390677_112040_Linux-x86-64_6of7.zip
-rwxr-x---.  1 root   root      119521122 Sep  2 10:16 p13390677_112040_Linux-x86-64_7of7.zip
drwx------. 17 oracle oinstall       4096 Sep  2 10:27 ..
drwxr-xr-x.  2 oracle oinstall       4096 Sep  2 10:34 .
[oracle@lx01 Downloads]$ unzip 'p13390677_112040_Linux-x86-64_*.zip'

Without the quotes, unzip will look for "zip files within zip files".

Thanks to Chris Jean for the tip!

How to fix error thrown by OPatch: java.lang.NullPointerException

Problem:

Opatch throws the following error when you are querying the inventory:

[oracle@lx01 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.11
Copyright (c) 2015, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/oraInventory
   from           : /u01/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.11
OUI version       : 11.2.0.1.0
Log file location : /u01/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-09-02_08-52-28AM_1.log

Lsinventory Output file location : /u01/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-09-02_08-52-28AM.txt

--------------------------------------------------------------------------------
java.lang.NullPointerException
 at oracle.sysman.oii.oiin.OiinNetOps.addNICInfo(OiinNetOps.java:144)
 at oracle.sysman.oii.oiin.OiinNetOps.computeNICList(OiinNetOps.java:109)
 at oracle.sysman.oii.oiin.OiinNetOps.(OiinNetOps.java:76)
 at oracle.sysman.oii.oiin.OiinNetOps.getNetOps(OiinNetOps.java:90)
 at oracle.sysman.oii.oiix.OiixNetOps.getFullHostName(OiixNetOps.java:49)
 at oracle.opatch.LsInventoryPrinter.printLocalMachineInfo(LsInventoryPrinter.java:2336)
 at oracle.opatch.LsInventorySession.loadAndPrintInventory(LsInventorySession.java:480)
 at oracle.opatch.LsInventorySession.process(LsInventorySession.java:317)
 at oracle.opatch.OPatchSession.main(OPatchSession.java:2120)
 at oracle.opatch.OPatch.main(OPatch.java:626)
Local Machine Information::
Hostname: lx01.oric.no
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

Cause:
In my case, the file /etc/hosts was incorrectly configured.

Solution:
Configure the file /etc/hosts correctly.

[root@lx01 ~]# ifconfig 
enp0s3: flags=4163  mtu 1500
        inet 192.168.0.55  netmask 255.255.255.0  broadcast 192.168.0.255

Check the /etc/hosts:

[root@lx01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.55 lx01.oric.no lx01
[oracle@lx01 ~]$ vi /etc/hosts

Update the incorrect ip address from 192.168.1.55 to 192.168.0.55, and save the file.

Opatch will now display expected output:

[oracle@lx01 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.11
Copyright (c) 2015, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/oraInventory
   from           : /u01/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.11
OUI version       : 11.2.0.1.0
Log file location : /u01/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-09-02_09-07-43AM_1.log

Lsinventory Output file location : /u01/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-09-02_09-07-43AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: lx01.oric.no
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

How to solve: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

After a fresh install of an Oracle 32-bit client on a 64-bit Linux server, we received the following errors when we tried to use any of the binaries under the $ORACLE_CLIENT_HOME/bin directory:

 error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

It turned out that only the 64-bit version of libaio was installed:

yum list libaio
Loaded plugins: product-id, rhnplugin, security
This system is receiving updates from RHN Classic or RHN Satellite.
Installed Packages
libaio.x86_64  0.3.107-10.el6  @anaconda-RedHatEnterpriseLinux-201301301459.x86_64/6.4
Available Packages
libaio.i686  0.3.107-10.el6  prod-rhel-x86_64-server-6

Solution is to simply install the missing package (output abbreviated):

su -
yum install libaio.i686

downloading Packages:
libaio-0.3.107-10.el6.i686.rpm                                                                                                                                                                    |  21 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : libaio-0.3.107-10.el6.i686                                                                                                                                                                            1/1
  Verifying  : libaio-0.3.107-10.el6.i686                                                                                                                                                                            1/1

Installed:
  libaio.i686 0:0.3.107-10.el6

Complete!

What do do when Oracle Universal Installer reports missing package pdksh-5.2.14 oracle

On RHEL 6, if you encounter situations where the OUI is complaining about the absence of a package pdksh-5.2.14 oracle, do the following:

1. Change directory to /database/stage/cvu/cv/admin 

2. Backup cvu_config: % cp cvu_config backup_cvu_config

3. Edit cvu_config and change the following line:

CV_ASSUME_DISTID=OEL4

to:

CV_ASSUME_DISTID=OEL6

4. Save the updated cvu_config file
5. Install the 11.2.0.3 or 11.2.0.4 software using /database/runInstaller

% cd /database
% ./runInstaller

When installing a 32-bit client like in my case, the OUI will warn that "all requirements have not been met".
This message can be ignored and you can proceed with the installation.
Source: MOS document ID 1454982.1

How to install Oracle Text

Based on "Manual Installation, Deinstallation and Verification of Oracle Text 11gR2 (Doc ID 970473.1)" from My Oracle Support

1. Install the CTXSYS schema:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

2. Install the language-specific default preferences.
There is script which creates language-specific default preferences for every language Oracle Text supports in $O_H/ctx/admin/defaults Directory

Grep for your language, and you'll find your script easily:

myserver>grep Norwegian *
drdefn.sql:Rem      default preference for Norwegian

Execute it as follows:

SQL> connect "CTXSYS"/"change_on_install"
SQL> @?/ctx/admin/defaults/drdefn.sql "NORWEGIAN";
SQL> connect SYS/password as SYSDBA
SQL> alter user ctxsys account lock password expire;
SQL> spool off

3. Verify your installation by running:

connect SYS/password as SYSDBA

set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log

-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

spool off

Valid output depends on your Oracle version, but for 11.2.0.3 it is:

COMP_NAME                      STATUS                                       VERSION
------------------------------ -------------------------------------------- ----------------------------------------
Oracle Text                    VALID                                        11.2.0.3.0
 

VER_DICT                                 VER_CODE
---------------------------------------- ----------------------------------------
11.2.0.3.0                               11.2.0.3.0
 

VER_CODE
----------------------------------------
11.2.0.3.0


  COUNT(*)
----------
       388


OBJECT_TYPE            COUNT(*)
-------------------- ----------
INDEX                        63
TYPE BODY                     6
INDEXTYPE                     4
PROCEDURE                     2
TYPE                         35
TABLE                        50
VIEW                         77
FUNCTION                      2
LIBRARY                       1
PACKAGE BODY                 63
OPERATOR                      6
PACKAGE                      74
LOB                           2
SEQUENCE                      3

How to work around dbca error "The Oracle system identifier ... already exists, specify another SID"

If you have previously created a database through dbca and saved it as a template for future use, you may see the following error when trying to specify the same SID again:

The Oracle system identifier ... alerady exists, specify another SID

Solution to the problem is to remove the entry for the SID in /etc/oratab

Then click next in dbca, and the configuration wizard will proceed.

Thanks to Elena for pointing this out in her blog.

Potential cause of ORA-12547: TNS:lost contact

I was running a script against the database directly on the host, when the script timed out with:

ERROR:
 ORA-12547: TNS:lost contact

After a search on Oracle's support site I found doc 555565.1: Troubleshooting ORA-12547 TNS: Lost Contact

One potential cause was incorrect permissions on the oracle binary in the $ORACLE_HOME/bin directory. I checked my old OH and compared it with the new OH:

server1>cd $ORACLE_HOME
server1>pwd
 /u01/oracle/product/11204
server1>ls -latr bin/oracle
 -rwxr-x--x    1 ora11g   dba       309704130 Jun 11 11:00 bin/oracle
server1>ls -latr /u01/oracle/product/11.2.0.2/bin/oracle
 -rwsr-s--x    1 ora11g   dba       279291858 Mar 12 13:42 /u01/oracle/product/11.2.0.2/bin/oracle

As can be seen from the above statement, the oracle server executable was lacking the setuid bit

This bit is needed, since we need to make sure that any user who runs the executable file will inherit the user ID of the owner (or group) of the executable file. Note that LOCAL connections would work just fine, even if the permissions on the oracle executable is set incorrectly; only connections that are passed from the listener to the Oracle server are affected. These sessions must be able to spawn a new server process.

Oracle points out that this error is commonly seen "in environments where the listener is running in the GRID home and servicing connections to an instance in a different $ORACLE_HOME."

Connections via the listener are failing with ORA-12547. It is likely in this scenario that LOCAL or BEQ connections to the instance are successful.

Solution to my particular problem this time was to give $ORACLE_HOME/bin/oracle the proper permissions:

server1> sqlplus / as sysdba
SQL> shutdown immediate

server1>chmod 6751 oracle
server1>ls -latr oracle
 -rwsr-s--x    1 ora11g   dba       309704130 Jun 11 11:00 oracle

server1> sqlplus / as sysdba
SQL> startup

After this change the script ran through with no ORA-12547 errors.

Sources: Oracle System Administration Guide

How to install the instant client from Oracle on a Linux application server

This is how I quite quickly installed sqlplus on a Linux appserver on request from my customer:

1. Download the two files

instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

from the otn network.


2. Create Directory for the installation files

mkdir  /u01/oracle/product/admin/install

3. Ftp the files over to the server

4. Put the installation files in the install Directory:

mv /tmp/instantclient*.zip /u01/oracle/product/admin/install

5. Unzip the files:

unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip -d /u01/oracle/product

The files will be unzipped to the Directory /u01/oracle/Product/instantclient_11_2

6. Create a Directory for networking:

mkdir $ORACLE_HOME/network/admin

7. set up the .bash_profile:

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/instantclient_11_2
export PATH=$PATH:$HOME/bin:$ORACLE_HOME
export LD_LIBRARY_PATH=/u01/oracle/product/instantclient_11_2
export NLS_LANG=NORWEGIAN_NORWAY.UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin

8. Create file tnsnames.ora, add an entry for each of the databases you'd like to Access via sqlplus.

All done!


Note that you do need *both* the basic and the sqlplus instantclient zip files to be unpacked into the same directory. If not, you will be missing Libraries in your installation and you'll receive the following error when launching sqlplus:

sqlplus: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or Directory

How to work around SEVERE: OUI-10197:Unable to create a new Oracle Home when using clone.pl

When cloning a database home onto a new server, the clone.pl script threw the following error:

perl clone.pl ORACLE_HOME=/u01/oracle/product/11204 ORACLE_HOME_NAME=11204 

SEVERE: OUI-10197:Unable to create a new Oracle Home at /u01/oracle/product/11204. Oracle Home already exists at this location. Select another location.

The server was freshly installed, new volume Groups, logical Volume Groups and file systems newly created.

I then realized that since the software was tared and untared into a new directory, there would potentially be an existing Oracle Inventory present, and I would needed to "detach" the ORACLE_HOME before cloning it. The tarball did indeed contain an Inventory:

 myserver>cd $ORACLE_HOME
 myserver>cat oraInst.loc.org
 inventory_loc=/u01/oracle/oraInventory
 inst_group=dba

Since there was no such directory present, I created it:

myserver>mkdir /u01/oracle/oraInventory/ContentsXML/
myserver>cd /u01/oracle/oraInventory/ContentsXML/
myserver>vi inventory.xml

In the file inventory.xml, I added the following minimal information:

<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
 <!-- Do not modify the contents of this file by hand. -->
 <inventory>
 <version_info>
    <saved_with>11.2.0.4.0</saved_with>
    <minimum_ver>2.1.0.6.0</minimum_ver>
 </version_info>
 <home_list>
 <home idx="1" loc="/u01/oracle/product/11204" name="11204" type="O">
 </home></home_list>
 <compositehome_list>
 </compositehome_list>
 </inventory>

I then added

 -invPtrLoc /u01/oracle/product/11204/oraInst.loc

to file $ORACLE_HOME/clone/config/cs.properties

Next, I detached the ORACLE_HOME:

myserver>cd $ORACLE_HOME/oui/bin
 myserver>./runInstaller -detachHome ORACLE_HOME=/u01/oracle/product/11204 -invPtrLoc /u01/oracle/product/11204/oraInst.loc
 Starting Oracle Universal Installer...

 Checking swap space: must be greater than 500 MB.   Actual 8192 MB    Passed
 The inventory pointer is located at /u01/oracle/product/11204/oraInst.loc
 The inventory is located at /u01/oracle/oraInventory
 'DetachHome' was successful.

Finally I was able to clone the ORACLE_HOME:

perl clone.pl ORACLE_HOME=/u01/oracle/product/11204 ORACLE_HOME_NAME=11204 ORACLE_BASE=/u01/oracle OSDBA_GROUP=dba

 ********************************************************************************

 Your platform requires the root user to perform certain pre-clone
 OS preparation.  The root user should run the shell script 'rootpre.sh' before
 you proceed with cloning.  rootpre.sh can be found at
 /u01/oracle/product/11204/clone directory.
 Answer 'y' if the root user has run 'rootpre.sh' script.

 ********************************************************************************

 Has 'rootpre.sh' been run by the root user? [y/n] (n)
 y
 ./runInstaller -clone -waitForCompletion  "ORACLE_HOME=/u01/oracle/product/11204" "ORACLE_HOME_NAME=11204" "ORACLE_BASE=/u01/oracle" "oracle_install_OSDBA=dba" -silent -noConfig -nowait -invPtrLoc /u01/oracle/product/11204/oraInst.loc
 Starting Oracle Universal Installer...

 Checking swap space: must be greater than 500 MB.   Actual 8192 MB    Passed
 Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-06-11_03-12-31PM. Please wait ...Oracle Universal Installer, Version 11.2.0.4.0 Production
 Copyright (C) 1999, 2013, Oracle. All rights reserved.

 You can find the log of this install session at:
  /u01/oracle/oraInventory/logs/cloneActions2014-06-11_03-12-31PM.log
 .................................................................................................... 100% Done.



 Installation in progress (Wednesday, June 11, 2014 3:12:45 PM CEST)
 ................................................................................                                                80% Done.
 Install successful

 Linking in progress (Wednesday, June 11, 2014 3:12:54 PM CEST)
 Link successful

 Setup in progress (Wednesday, June 11, 2014 3:15:32 PM CEST)
 Setup successful

 End of install phases.(Wednesday, June 11, 2014 3:15:59 PM CEST)
 WARNING:
 The following configuration scripts need to be executed as the "root" user.
 /u01/oracle/product/11204/root.sh
 To execute the configuration scripts:
     1. Open a terminal window
     2. Log in as "root"
     3. Run the scripts

 The cloning of 11204 was successful.
 Please check '/u01/oracle/oraInventory/logs/cloneActions2014-06-11_03-12-31PM.log' for more details.

What is the significance of using the clone.pl script to clone an existing ORACLE_HOME to a new one?

In short, when using the clone method, Oracle will relink your binaries and update your Inventory.
Afterwards, your new ORACLE_HOME can be treated individually like any other ORACLE_HOME.

From Oracle Support note 565009.1:

Cloning re-plays all the actions that were performed during the installation of the Oracle Home like relinking, updating the inventory, etc., and hence the cloned home can be patched using OPatch, and patchsets can be installed to it.

Simply copying the Oracle Home is not supported.

By [simply] copying the Oracle Home from one server to another, all the above mentioned actions are not performed and also it is not possible to apply patches or patchsets to a copied Oracle Home.

If the Oracle Home has to be deployed to multiple destinations with the same configuration, cloning is the only supported method.


For a step-by-step instruction on how to perform such a clone, please see an excellent article by Eric Jenkinsson found here.

How to create the libobk.a link in $ORACLE_HOME/lib

su - oracle
cd $ORACLE_HOME/lib
ln -s /usr/lib/libobk64.a libobk.a

How to instruct OUI to ignore prerequisites

./runinstaller -ignoreSysPrereqs