During Oracle Upgrade cannot connect sys as sysdba

book

Article ID: 160775

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

When following the Oracle 11g Installation and Upgrade Guide steps on a Windows server, after creating the TNS Listener and editing the listener.ora, you are unable to log in to the Oracle database from SQL Lite with the command conn sys as sysdba as required in order to execute the ALTER commands, and instead receive a TNS error.

Resolution

One possible solution is to explicitly set the Windows environment variable ORACLE_SID = PROTECT (or the SID for the database if it differs from a default installation)
ORACLE_HOME should have been set by the user as part of the installation/upgrade process.

In Linux, Oracle is owned by the oracle Linux account and that has an associated .bash_profile that is applied to every Oracle user log in.  In that profile, the environment variables are set, ORACLE_HOME, ORACLE_BASE, ORACLE_SID, LD_LIBRARY_PATH, and PATH, typically.

Windows doesn’t explicitly do this.  You log in as Administrator, and if Oracle has been installed, it is implied that it is the default database.  Given no help at all, SQLPlus will manufacture a connection string based on defaults…the default for the host is localhost, the default for the port is 1521, and the default for Oracle SID is ORCL.  Usually Oracle will mine through the registry for the SID.  In the event that this is indeterminate, then it goes for the defaults.

ORACLE_HOME and ORACLE_SID among other settings should be created in the Windows registry by the installer.