ORA-01075: you are currently logged on

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-01075: you are currently logged on

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys
Enter password: 
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

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.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件

此条目发表在 ORA-xxxxx 分类目录,贴了 标签。将固定链接加入收藏夹。

ORA-01075: you are currently logged on》有 1 条评论

  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.