The final and concluding Part-V of the answer session begins now.
5. Troubleshoot ORA-12547: TNS:lost contact error.
5.1. Create Password File.
Now, create the password file being located in "$ORACLE_HOME/dbs"
whose name should be resolved as follows:
Password filename Formula: orapw
+ ORACLE_SID
= orapw
+ orcl
= orapworcl
.
Issue command orapwd
to create and add user SYS
into password file 'orapworcl'
along with password 'oracle18c'
as shown below:
$ /oracle18c/app/oracle/product/18.0.0/dbhome_1/bin/orapwd file=/oracle18c/app/oracle/product/18.0.0/dbhome_1/dbs/orapworcl password=oracle18c ENTRIES=30 FORCE=Y IGNORECASE=Y format=12
5.2. Set Oracle Environment.
To set the oracle environment, create a file ~/scripts/oenv.sh
and copy & paste contents given in this section:
$ vi ~/scripts/oenv.sh
#!/bin/sh
#--------------------------------------------------------------------
# Oracle Environment Variables Settings
#--------------------------------------------------------------------
export ORACLE_BASE=/oracle18c/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/18.0.0/dbhome_1
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export ORACLE_LIBPATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$CLASSPATH
export ORACLE_HOSTNAME=$HOSTNAME
export ORA_INVENTORY=/oracle18c/app/oraInventory
export DATA_DIR=$ORACLE_BASE/oradata
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_HOME=$ORACLE_BASE/diag
#--------------------------------------------------------------------
export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export PDB_NAME=pdb
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
#--------------------------------------------------------------------
export TMP=/tmp; export TMPDIR=$TMP; export TEMP=$TMP
#--------------------------------------------------------------------
Change the file permission:
$ chmod +x ~/scripts/oenv.sh
Add the following in ~/.bashrc
by this command:
$ echo '# Environment settings for Oracle ' | tee -a ~/.bashrc
$ echo '. ~/scripts/oenv.sh' | tee -a ~/.bashrc
Now, reboot the computer and return back here to proceed further.
5.3. Create directories.
Open vi
editor and copy & paste the following contents:
$ vi ~/scripts/ocdir.sh
#!/bin/sh
umask 0027
mkdir -p /oracle18c/app/oracle
mkdir -p /oracle18c/app/oracle/admin/orcl/adump
mkdir -p /oracle18c/app/oracle/admin/orcl/dpdump
mkdir -p /oracle18c/app/oracle/admin/orcl/pfile
mkdir -p /oracle18c/app/oracle/audit
mkdir -p /oracle18c/app/oracle/cfgtoollogs/dbca/orcl
mkdir -p /oracle18c/app/oracle/fast_recovery_area/ORCL
mkdir -p /oracle18c/app/oracle/oradata/ORCL
mkdir -p /oracle18c/app/oracle/oradata/ORCL/pdb
mkdir -p /oracle18c/app/oracle/oradata/ORCL/pdbseed
mkdir -p /oracle18c/app/oracle/product/18.0.0/dbhome_1/dbs
Change the file permission:
$ chmod +x ~/scripts/ocdir.sh
Execute the script file ocdir.sh
:
$ . ~/scripts/ocdir.sh
5.4. Check listener
status.
Ensure that listener is up and listening in the background with the following command:
$ ss -elpunt | grep -E "^Net|tnslsnr"
Figure-119: Oracle listener is up and running.
If listener is not running, then start it by the following command:
$ lsnrctl start LISTENER
5.5. Meaning of ORA-12547
error.
Let us first understand "What does ORA-12547
mean". Figure out the meaning of error ORA-12547
by running oracle binary oerr
which outputs a pre-recorded description.
$ cd $ORACLE_HOME/bin
$ ./oerr ORA 12547
Figure-120: The Meaning of error 'ORA-12547' displayed by oracle binary utility 'oerr'.
The description for error ORA-12547
by oracle utility oerr
, seems to be little strange. To put it simply, ORA-12547
indicates that the communication channel has been broken. It is most often thrown because the other end of the process went away unexpectedly.
This error ORA-12547
may result due to various causes. We will see one by one.
5.6. Privilege settings for oracle
binary
The binary oracle
should not only have 6751
privilege but also non-zero
file size, as shown in figure-121 below:
$ cd $ORACLE_HOME/bin
$ ls -l oracle
Figure-121: oracle binary has '6751' privileges set and 'non-zero' files size which are good.
If wrong privilege is set, then issue the following command:
$ chmod 6751 oracle
If oracle file shows zero file size
, then issue the following command to rebuild:
$ cd $ORACLE_HOME/bin
$ ./relink all
5.7. Non-zero file size of config.o
binary:
The object file config.o
should be having non-zero file size. To verify, issue the following commands:
$ cd $ORACLE_HOME/rdbms/lib
$ ls -l config.o
Figure-122: The screen displays that 'object.o' file has non-zero file size which is good.
If there is zero file size
, then issue the following command to rebuild:
$ mv config.o config.o.bad
$ cd $ORACLE_HOME/bin
$ ./relink all
5.8. Oracle Base and Home Settings in orabasetab
file:
Display the contents of the file for the verification of oracle Base
and Home
settings.
$ cd $ORACLE_HOME/install
$ cat orabasetab
Figure-123: Display of content of 'orabasetab' file showing the settings of oracle 'Base' and 'Home' which are good.
If there are incomplete or wrong settings found, then edit $ORACLE_HOME/install/orabasetab
file using vi
editor and correct Base
and Home
values.
5.9. Oracle Base settings:
Run the orabase
binary to make sure that Oracle base directory is properly set.
$ cd $ORACLE_HOME/bin
$ ./orabase
Figure-124: Oracle Base directory setting is shown by binary 'orabase' which is good.
5.10. Privilege settings for /proc
directory:
Make sure that correct privileges are set on /proc
directory. Issue the following command:
$ ls -l /
Figure-125: The correct privilege settings 555 for /proc directory.
If there is wrong privilege settings, then correct manually by following command:
$ cd /
$ sudo chmod 555 proc
5.11. Privilege settings for /dev/null
directory:
Make sure that correct privileges are set on /dev/null
directory which should be 0666
. Issue the following command:
$ ls -l /
Figure-126: The correct privilege settings '666' for '/dev/null' directory.
If there is wrong privilege settings, then correct manually by following command:
$ cd /
$ sudo chmod 666 /dev/null
5.12. Oracle binary related subsystem settings in /ect/fstab
file:
The structure of each entry in /etc/fstab
file consists of primarily the following 6 fields:
[Device] [Mount point] [Files System Type] [options] [Backup operations] [File System check order]
The oracle software has been installed in file system under /opt
mount point. Locate the record in /etc/fstab
file corresponding to /opt
mount point and inspect 4th field which is [options]
. If this field contains "nosuid"
then change this to "suid"
.
Issue the following command, to locate the entry corresponding /opt
mount point:
$ cat /etc/fstab | grep /opt
Figure-127: The 4th field '[options]' contains "defaults" corresponding to '/opt' file systems which is good.
The 4th field [options]
has value "defaults"
which is a shortcut
and whose expansion is:
"rw,suid,dev,exec,auto,nouser,async"
So, the presence of "defaults"
causes no trouble. In the absence of "defaults"
, if there is a presence of "nosuid"
explicitly then change it to "suid"
manually in /etc/fstab
file using vi
editor. After the change, just reboot your system.
5.13. Unset the EXTSHM
Environment variable.
As oracle does not support Extended Shared Memory
, the environment variable EXTSHM
should be unset. Issue the following command:
$ env | grep EXTSHM
Figure-128: The output shows that the Environment variable 'EXTSHM' is undefined which is good.
If the variable EXTSHM
is set then unset
it with the following command:
$ unset EXTSHM
5.14. Insufficient ulimit settings for Stack:
Refer [section 1-7] "Configure shell limits for user oracle" to increase stack limits.
5.15. Simulation of "ORA-12547: TNS:lost contact"
error using sqlplus
binary:
It was established that ORA-12547
error occurred while trying to create Oracle 18c Database by running scripts generated from DBCA [refer section-4.2 and figure-113]. We found out that the root cause of this error was CONNECT
statement. So, let us reproduce this error again by invoking CONNECT
statement directly from sqlplus
binary.
Issue sqlplus
command as shown below:
$ $ORACLE_HOME/bin/sqlplus /nolog
Issue CONNECT
statement at SQL
prompt as follows:
SQL> connect sys/oracle18c as sysdba;
Figure-129: Simulation of "ORA-12547: TNS:lost contact" error by directly invoking 'sqlplus' binary.
Now it is established that the error is re-creatable by executing sqlplus
binary. In the absence of source code, we should attack the problem with whatever weapons left in our arsenal. As a first step, let us try to generate trace information describing the sequence of events taking place within sqlplus
binary.
5.16. Run strace
command to generate trace information needed to dig further:
In order to generate trace file needed for further investigation into CONNECT
statement, let us use strace
command along with sqlplus
as shown below:
$ strace -f -o /tmp/strace.log $ORACLE_HOME/bin/sqlplus /nolog
Issue CONNECT
statement at SQL
prompt as follows:
SQL> connect sys/oracle18c as sysdba;
Figure-130: Use 'strace' command to investigate into 'CONNECT' statement invoked from 'sqlplus' binary.
Now, use gedit
and open up /tmp/strace.log
file. After tracing entire file, the problem area was identified as shown in the figure-131 below:
Figure-131: "core dump" is found recorded in the 'strace.log' file.
Before taking up analysis of "core dump"
further, let us check whether core
file has been generated in the default directory
with the following commands:
$ pwd
$ ls
Figure-132: The output confirms that no 'core' file was generated in '/home/oracle' directory.
Find out whether core is enabled or not with the following command:
$ ulimit -c
Figure-133: Output shows that 'core' is not enabled.
To enable core, issue the following command:
$ ulimit -c unlimited
Figure-134: Enable core file generation.
Ensure core is enabled by the following command:
$ ulimit -c
Figure-135: Ouput shows that 'core' file generation is successfully enabled.
Again, run strace
command as shown in the beginning of this section 5.16 and check that the core
file has been generated successfully:
$ pwd
$ ls
Figure-136: Output shows that 'core' file is generated successfully
Now, run debugger gdb
as follows:
$ gdb -c core
Figure-137: Undefined Symbol in 'libpython3.6m.so.1.0' while invoking debugger 'gdb'.
To resolve symbol lookup error
, issue the following command to inspect dependancies of gdb
:
$ ldd /usr/bin/gdb
Figure-138: Output shows that only shared object 'libexpat.so.1' has been loaded wrongly from '$ORACLE_HOME/lib'.
From the about output in above figure-138, one can easily deduce that the issue is connected with LD_LIBRARY_PATH
environment variable.
Firstly, issue the following command to locate the correct path of libexpat.so.1
:
$ locate libexpat.so.1
Figure-139: The correct library path for 'libexpat.so.1' is '/lib/x86_64-linux-gnu/'
Determine the current value of environment variable LD_LIBRARY_PATH
:
$ echo $LD_LIBRARY_PATH
Figure-140: '$ORACLE_HOME/lib' appears as the first member in the 'value' of environment variable 'LD_LIBRARY_PATH'
As $ORACLE_HOME/lib
appears to be the first member in LD_LIBRARY_PATH
, so dependency library libexpat.so.1
has been loaded from $ORACLE_HOME/lib
instead of from /lib/x86_64-linux-gnu
.
This LD_LIBRARY_PATH
must be unset until core dump analysis is over. Later, this environment variable LD_LIBRARY_PATH
should be restored back to its original state.
Edit ~/scripts/oenv.sh
file (refer section 5.2) and comment out the line containing LD_LIBRARY_PATH
, so that this variable will be automatically unset after rebooting.
Verify that the line is commented out properly, with the following command:
$ cat ~/scripts/oenv.sh | grep LD_LIBRARY_PATH
Figure-141: The comment symbol "#" appears in front of line containing 'LD_LIBRARY_PATH'.
Reboot the system.
Find out that the value of LD_LIBRARY_PATH
whether it is empty or not with the following command:
$ echo $LD_LIBRARY_PATH
Figure-142: Output shows that 'LD_LIBRARY_PATH' is empty, thus properly unset which is good.
Issue the following command once again to inspect dependency list of gdb
in order to make sure that libexpat.so.1
is loaded from right path:
$ ldd /usr/bin/gdb
Figure-143: Output shows that 'libexpat.so.1' is loaded correctly from '/lib/x86_64-linux-gnu'.
Now, run debugger gdb
and check it is working fine:
$ gdb -c core
Figure-144: Debugger 'gdb' is found to be working perfectly.
Issue bt
command at gdb
prompt, as shown below:
(gdb) bt
Figure-145: The screen shows the output of 'bt' command and observe that the result is too poor to analyze.
The result of bt
command was too poor and we need more information
for analysis. Let us attempt another method
with the following command:
$ gdb --core=core --exec=/oracle18c/app/oracle/product/18.0.0/dbhome_1/bin/oracle
Issue bt
command at gdb
prompt:
(gdb) bt
Figure-146: The result produced by the above command is more promising than in figure-145.
The tzset()
function sets time conversion information used by localtime()
and related functions.
Moreover, localtime()
is not thread-safe because it returns a pointer (struct tm *)
to a static data structure (refer <time.h>
header for more details) which holds the result.
Let us review the /tmp/strace.log
once again:
Figure-147: The file "/etc/localtime" was the last one accessed before crash (core dump).
The file "/etc/localtime"
was the last one accessed before crash (core dump). From the beginning until core dump, this file "/etc/localtime"
was accessed twice.
This shows a condition of deadlock because localtime()
is entered recursively (for a second time) due to some signal being handled and localtime()
is not reentrant or thread-safe, so it blocks on a lock.
We can conclude that Oracle kernel
is crashing in various libc
functions (Standard Library for C Programming Language
), e.g. localtime().
What is Oracle Kernel?
On Ubuntu linux, every oracle client process such as sqlplus or Toad
, connects to tnslsnr
, which fork()'s
and then exec()'s
a binary "$ORACLE_HOME/bin/oracle"
, called as oracle kernel program
. This binary is also known popularly as Oracle Server Process
. Refer figure-121
in [section-5.6].
Let us display the system call execve
invoking oracle
binary recorded by strace
command in /tmp/strace.log
.
Figure-148: The Oracle Kernel binary "oracle" invoked by "execve" system call.
So, it is the Oracle kernel program
who was causing core dump
and to over come this issue, it is required to remove the libc*
stubs and then rebuild oracle kernel binary $ORACLE_HOME/bin/oracle
afresh.
To accomplish this task, issue the following commands:
$ cd $ORACLE_HOME/lib/stubs
$ ls -l libc*
$ rm libc*
$ cd $ORACLE_HOME/bin
$ ./relink all
Figure-149: Rebuild oracle kernel program "$ORACLE_HOME/bin/oracle" afresh by issuing "./relink all" command and observe the resulting log file.
After the rebuilt, inspect its log file $ORCALE_HOME/install/relink_2019-02-26_08-27.log
to ensure that all rebuilds succeeded.
Run strace
command once again and let us check that our arch-villain "ORA-12547: TNS:lost contact"
still persists!
$ strace -f -o /tmp/strace.log $ORACLE_HOME/bin/sqlplus /nolog
Issue CONNECT
statement at SQL prompt as follows:
SQL> connect sys/oracle18c as sysdba;
Figure-150: Our arch-villain "ORA-12547: TNS:lost contact" has vanished at last and replaced by "an idle instance".
Bingo! Our arch-villain "ORA-12547: TNS:lost contact"
has disappeared at last!
The connect
statement was executed successfully and an idle instance
was also generated.
5.17. Re-run the Scripts generated from DBCA
to create Oracle 18c Database:
To display the list of scripts required to create database, issue these commands:
$ cd /oracle18c/app/oracle/admin/orcl/scripts
$ ls -l *.sql
Figure-151: List of scripts generated by 'DBCA' required to create oracle 18c database.
Ensure that listener
is already up and running with the following command:
$ ss -elpunt | grep -E "^Net|tnslsnr" | column -t
Figure-152: Oracle listener is running and listening on port 1521.
With reference to [refer section-4.2 and figure-113], let us attempt once again to create Oracle 18c Database by running scripts generated from DBCA
and let us hope that the database will be created successfully this time!
$ cd /oracle18c/app/oracle/admin/orcl/scripts
$ . orcl.sh | tee ~/Documents/create_database_scripts.log
Figure-153: Creation of oracle 18c database has successfully begun! Notice the appearance of 'idle instance' inside redbox.
Let us see the screenshot of end of database creation below:
Figure-154: Creation of oracle 18c database has come to end successfully!
Let us see the Services supported by the Listener
with the following command:
$ lsnrctl status
Figure-155: Services supported by 'listener'.
Let us verify Oracle Version
with the following command:
$ sqlplus / as sysdba
SQL> select instance_name, version from v$instance;
Figure-156: Display of Oracle Version.
Let us display Oracle Banner
with the following command:
$ sqlplus / as sysdba
SQL> select banner from v$version;
Figure-157: Display of Oracle Banner.
6. Post installation.
6.1. Configure /etc/oratab
file.
Append "/etc/oratab"
file setting the restart flag for each instance to 'Y'
.
$ echo 'orcl:/oracle18c/app/oracle/product/18.0.0/dbhome_1:Y' | sudo tee -a /etc/oratab
Figure-158: Append "/etc/oratab" file with restart flag.
Display the contents of "/etc/oratab"
file with the following command:
$ cat /etc/oratab
Figure-159: Content of "/etc/oratab" file.
6.2. Start PDB
along with database instance.
The environment variables DATA_DIR
and PDB_NAME
needed to configure PDB
are already exported through ~/scripts/oenv.sh
. Issue the following command to verify:
$ env | egrep 'DATA_DIR=|PDB_NAME='
Figure-160: Environnment variables 'PDB_NAME' and 'DATA_DIR' are already set.
Next, ensure that database instance is up and running.
If it is not, then invoke the following script file to run:
$ . ~/scripts/ora_start.sh
Note: to know about the script ora_start.sh
, refer [section-6.5]
Enable Oracle Managed Files (OMF) and make sure the PDB starts when the instance starts:
$ sqlplus / as sysdba <<EOF
alter system set db_create_file_dest='${DATA_DIR}';
alter pluggable database ${PDB_NAME} save state;
exit;
EOF
Figure-161: Pluggable database will start along with database instance.
6.3. Restore LD_LIBRARY_PATH
back to original value.
Recall that LD_LIBRARY_PATH
was unset (refer figures-137 and 138) in ~/scripts/oenv.sh
.
Now we should restore it back by removing the comment # symbol
in the line contains LD_LIBRARY_PATH
string using any editor. To verify the change is done, issue the following command:
$ cat ~/scripts/oenv.sh | grep LD_LIBRARY_PATH
Figure-162: Environnment variables 'LD_LIBRARY_PATH' is restored to its original value by removing comment '#' symbol (compare with figure-140).
6.4. Enable Firewall.
In section-1.12, firewall was disabled which was a pre-requisite. Now it can be restored.
To enable firewall, issue the following command:
$ sudo ufw enable
Figure-163: Firewall successfully enabled.
To check the status of firewall, issue the following command:
$ sudo ufw status
Figure-164: Firewall status is active
6.5. Start/Stop scripts for database.
It is important to recall that oracle environment variables have already been set using ~/scripts/oenv.sh
file and is appended in ~/.bashrc
file for execution upon login. Refer section-5.2.
Create two scripts ora_start.sh
and ora_stop.sh
respectively that can be called like startup/shutdown
service.
Now, create ora_start.sh
using vi
editor.
$ vi ~/scripts/ora_start.sh
Copy and paste the below contents into the file.
#!/bin/sh
lsnrctl start
dbstart $ORACLE_HOME
Save and exit from vi editor.
Now, create ora_stop.sh
using vi
editor.
$ vi ~/scripts/ora_stop.sh
Copy and paste the below contents into the file.
#!/bin/sh
lsnrctl stop
dbshut $ORACLE_HOME
Save and exit from vi
editor.
chown -R oracle:oinstall ~/scripts
chmod +x ~/scripts/ora_start.sh
chmod +x ~/scripts/ora_stop.sh
After edited the /etc/oratab
(refer section-6.1), you should be able to start/stop
the database with the following scripts run after logging into system as oracle user.
To start Oracle Database Service, run ora_start.sh
script:
$ . ~/scripts/ora_start.sh
Figure-165: Run script 'ora_start.sh' to start database instance
To shutdown Oracle Database Service, run ora_stop.sh
script:
$ . ~/scripts/ora_stop.sh
Figure-166: Run script ora_stop.sh to shut down database instance
6.6. Create a list of aliases for oracle.
Create a list of aliases suitable for Oracle in ~/scripts/oalias.sh
as follows:
$ vi ~/scripts/oalias.sh
Append the following into ~/scripts/oalias.sh
file using copy-paste technique:
#----------------------------------------
# alias list of useful commands for Oracle
#----------------------------------------
alias o='cd /oracle18c'
alias op='cd /opt'
alias oo='cd /opt/oracle'
alias ob='cd /oracle18c/app/oracle'
alias oi='cd /oracle18c/app/oraInventory'
alias od='cd /oracle18c/app/oracle/oraData'
alias oh='cd /oracle18c/app/oracle/product/18.0.0/dbhome_1'
alias obin='cd /oracle18c/app/oracle/product/18.0.0/dbhome_1/bin'
alias olib='cd /oracle18c/app/oracle/product/18.0.0/dbhome_1/lib'
alias odbs='cd /oracle18c/app/oracle/product/18.0.0/dbhome_1/dbs'
alias olog='cd /oracle18c/app/oracle/cfgtoollogs'
alias odlog='cd /oracle18c/app/oracle/cfgtoollogs/dbca'
alias otfa='cd /oracle18c/app/oracle/tfa'
alias otfab='cd /oracle18c/app/oracle/tfa/bin'
alias os='cd /oracle18c/app/oracle/admin/orcl/scripts'
alias oad='cd /oracle18c/app/oracle/admin'
alias osid='cd /oracle18c/app/oracle/admin/orcl'
alias otns='cd /oracle18c/app/oracle/product/18.0.0/dbhome_1/network/admin'
Save and exit from vi
editor.
Change file permission by chmod
command:
$ chmod +x ~/scripts/oalias.sh
Next, issue the following commands to add string ~/scripts/oalias.sh
into ~/.bashrc
file:
$ echo '#--------------------------------------------------------' | tee -a ~/.bashrc
$ echo '# alias list of useful commands for Oracle' | tee -a ~/.bashrc
$ echo '#--------------------------------------------------------' | tee -a ~/.bashrc
$ echo '. ~/scripts/oalias.sh' | tee -a ~/.bashrc
Now close your current terminal and open up a new terminal with Ctrl+Alt+T
and issue the following command:
$ alias
Figure-167: Very useful alias list for oracle.
You will see the list of aliases
for oracle displayed on the screen.
7. Conclusion.
The jubilation and joy were not only in the successful installation of Oracle-18c (Enterprise Edition) on Ubuntu 18.04 but also in the "taming" of Oracle Software
to co-exist along with all other installed software packages under /opt
partition (refer figure-57)
!
This completes Oracle 18c (Enterprise Edition)
installation on Ubuntu 18.04! Enjoy Oracle 18c (Enterprise Edition)
!!