When we were trying to connect to our upgraded 11.2.0.3 database, below error was thrown and we were not able to connect.
$ sqlplus as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
Done intial checking :
>> user oracle was in the correct database admin group (/etc/group)
>> No tnsnames issues as TNS_ADMIN variable is poiting to correct directories and all files( tnsnames.ora, sqlnet.ora, listener.ora) in it have valid entries.
>> $PATH variable was pointing to correct $ORACLE_HOME/bin
On checking the environment variable closely, it was found that one of the variable ORA_TZFILE was pointing to non-existent file.
echo $ORA_TZFILE
/u01/oracle/dev/db/tech_st/11.2.0/oracore/zoneinfo/timezlrg.dat
ls -ltr /u01/oracle/dev/db/tech_st/11.2.0/oracore/zoneinfo/timezlrg.dat
ls: /u01/oracle/dev/db/tech_st/11.2.0/oracore/zoneinfo/timezlrg.dat: No such file or directory
This file timezlrg.dat was not present at the location, though there were other files with names like
timezone_<N>.dat.
–
—
-rw-r–r– 1 oradev dba 507957 Aug 18 2011 timezlrg_2.dat
-rw-r–r– 1 oradev dba 493675 Aug 18 2011 timezlrg_1.dat
-rw-r–r– 1 oradev dba 791430 Aug 18 2011 timezlrg_14.dat
-rw-r–r– 1 oradev dba 782475 Aug 18 2011 timezlrg_13.dat
-rw-r–r– 1 oradev dba 785621 Aug 18 2011 timezlrg_12.dat
-rw-r–r– 1 oradev dba 787272 Aug 18 2011 timezlrg_11.dat
-rw-r–r– 1 oradev dba 792894 Aug 18 2011 timezlrg_10.dat
—
–
Unsetting this variable solved our issue.
$ unset ORA_TZFILE
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 16 11:45:08 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
We can also set and use the desired timezone file and if we do not set it then by default it will use maximum value of n.
export ORA_TZFILE=$ORACLE_HOME/oracore/zoneinfo/timezlrg_<N>.dat
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020