One thought on “ORA-01075: you are currently logged on”

1. ORA-01075 Trying To Connect to Database [ID 1107224.1]

   Applies to:
   
   Oracle Server - Standard Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
   Information in this document applies to any platform.
   Oracle RDBMS EE 10.2.x - 11.2
   Symptoms
   
   Receive ORA-1075 when trying to connect to the database as SYS AS SYSDBA
   The database is down due to problems with datafiles and errors. 
   The following might be found in the alert.log file for the current, or previous startup attempts.
   
   ORA-00604: error occurred at recursive SQL level 1 
   ORA-00376: file 2 cannot be read at this time 
   ORA-01110: data file 2: 'E:\ORADATA\PROD\NDOTBS01.DBF' 
   
   
   Changes
   
   Some type of media problem has occurred to take files offline, or files have been manually offlined.
   Cause
   
   Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline, 
   this is preventing the database from allowing new connections.
   
   NOTE: At this point, you cannot connect to verify the status in V$DATAFILE, 
   but you may find an indication of the offline datafile(s) in the alert.log file.
   
   For example:
   
   In one case, a media problem occurred which made disks unavailable.
   This caused several files to be taken offline automatically including a SYSAUX datafile.
   
   In the alert.log file, among other errors (due to various offline files), we see:
   
     The alert.log file documents that Oracle processed the following for each file
     KCF: write/open error block=0x89ad online=1
     file=3 E:\ORADATA\PROD\SYSAUX01.DBF
     error=27070 txt: 'OSD-04016: Error queuing an asynchronous I/O request.
     O/S-Error: (OS 1167) The device is not connected.'
     Automatic datafile offline due to write error on
     file <#> <name>
   
   Solution
   
   
   1. Modify the spfile as per Note 137483.1 or edit the init.ora and add the following:
   
        _system_trig_enabled = FALSE
   
   2. Restart the database with the new parameter in place
   
    Since you cannot connect, this may require you to kill the process, 
   or on MS Windows stop and restart the service.
   
   On MS Windows the init_sid.ora file will be located in ORACLE_HOME/database
   - Shutdown the service
   - If using a spfile rename it so that when the service is restarted it will use the init.ora file.
   
    
   
       You should then be able to connect.
   
   
   3. If the db opens completely, shutdown and startup in mount mode, 
   then check the files in RECOVER status:
   
   SQL> shutdown immediate
              startup mount
               select name,status from v$datafile where status not in ('SYSTEM','ONLINE');
   
   4. For all files reported in step 3 output, issue an ONLINE command:
       This is necessary to perform recovery on the files.
   
       SQL> alter database datafile '<name>' online;
   
   5. Recover the database (or datafiles):
   
       SQL> recover database
                      When prompted, type "auto"
   
   6. If you receive "media recovery complete", open the database:
   
       SQL> alter database open;
   
   NOTE: If you receive an error during recovery, address the specific error. 
   If the database needs archived redo and it is not in archivelog mode, 
   you will not be able to perform full recovery and will need to restore a previously taken cold backup.
   
   7. Remove the parameter set in step 1 and restart the database.
   

Comments are closed.