ORA-01075: you are currently logged on
xixuefeng
ERR
2014-03-01 23:47:37
3,596 次浏览
ORA-01075
ORA-01075: you are currently logged on已关闭评论
SQLPLUS登陆失败,效果如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@prod dbs]$ echo $ORACLE_SID PROD [oracle@prod dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 17:07:27 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 2: No such file or directory Additional information: 9925 ORA-01075: you are currently logged on Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus [oracle@prod dbs]$ |
问题描述:数据库在通过spfile启动到nomount的时候,有个参数的路径指向有误,当再次登陆sqlplus的时候,就出现了如下错误。原因是,数据库在刚才startup nomount时已经启动了后台进程,并且已经分配了内存,只不过由于参数中某个路径的问题,导致启动失败,但,内存已经分配和进程也已经启动。
解决办法:
第一步:kill掉oracle进程
1:查看oracle进程,并将其全部kill,这么多,如果一个一个的kill,显然很麻烦
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[root@prod ~]# ps -ef|grep ora_ oracle 14095 1 0 16:54 ? 00:00:00 ora_pmon_PROD oracle 14099 1 0 16:54 ? 00:00:00 ora_psp0_PROD oracle 14103 1 0 16:54 ? 00:00:02 ora_vktm_PROD oracle 14109 1 0 16:54 ? 00:00:00 ora_gen0_PROD oracle 14113 1 0 16:54 ? 00:00:00 ora_diag_PROD oracle 14117 1 0 16:54 ? 00:00:00 ora_dbrm_PROD oracle 14121 1 0 16:54 ? 00:00:00 ora_dia0_PROD oracle 14125 1 0 16:54 ? 00:00:00 ora_mman_PROD oracle 14129 1 0 16:54 ? 00:00:00 ora_dbw0_PROD oracle 14133 1 0 16:54 ? 00:00:00 ora_lgwr_PROD oracle 14137 1 0 16:54 ? 00:00:00 ora_ckpt_PROD oracle 14141 1 0 16:54 ? 00:00:00 ora_smon_PROD oracle 14145 1 0 16:54 ? 00:00:00 ora_reco_PROD oracle 14149 1 0 16:54 ? 00:00:00 ora_mmon_PROD oracle 14153 1 0 16:54 ? 00:00:00 ora_mmnl_PROD oracle 14157 1 0 16:54 ? 00:00:00 ora_d000_PROD oracle 14161 1 0 16:54 ? 00:00:00 ora_s000_PROD root 14212 14058 0 16:59 pts/4 00:00:00 grep ora_ [root@prod ~]# |
2:如果想省事儿,一定有办法解决,步骤如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
--只打印出oracle进程 [root@prod ~]# ps -ef|grep ora_|grep -v grep oracle 14095 1 0 16:54 ? 00:00:00 ora_pmon_PROD oracle 14099 1 0 16:54 ? 00:00:00 ora_psp0_PROD oracle 14103 1 0 16:54 ? 00:00:03 ora_vktm_PROD oracle 14109 1 0 16:54 ? 00:00:00 ora_gen0_PROD oracle 14113 1 0 16:54 ? 00:00:00 ora_diag_PROD oracle 14117 1 0 16:54 ? 00:00:00 ora_dbrm_PROD oracle 14121 1 0 16:54 ? 00:00:00 ora_dia0_PROD oracle 14125 1 0 16:54 ? 00:00:00 ora_mman_PROD oracle 14129 1 0 16:54 ? 00:00:00 ora_dbw0_PROD oracle 14133 1 0 16:54 ? 00:00:00 ora_lgwr_PROD oracle 14137 1 0 16:54 ? 00:00:00 ora_ckpt_PROD oracle 14141 1 0 16:54 ? 00:00:00 ora_smon_PROD oracle 14145 1 0 16:54 ? 00:00:00 ora_reco_PROD oracle 14149 1 0 16:54 ? 00:00:00 ora_mmon_PROD oracle 14153 1 0 16:54 ? 00:00:00 ora_mmnl_PROD oracle 14157 1 0 16:54 ? 00:00:00 ora_d000_PROD oracle 14161 1 0 16:54 ? 00:00:00 ora_s000_PROD [root@prod ~]# |
3: 通过AWK命令打印出进程ID
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@prod ~]# ps -ef|grep ora_|grep -v grep|awk '{print $2}' 14095 14099 14103 14109 14113 14117 14121 14125 14129 14133 14137 14141 14145 14149 14153 14157 14161 [root@prod ~]# |
4:通过xargs命令批量执行
注:xargs是一条Unix和类Unix操作系统的常用命令。它的作用是将参数列表转换成小块分段传递给其他命令,以避免参数列表过长的问题
1 2 3 4 5 6 |
[root@prod ~]# ps -ef|grep ora_|grep -v grep|awk '{print $2}' |xargs kill -9 [root@prod ~]# [root@prod ~]# ps -ef|grep ora_ root 14249 14058 0 17:02 pts/4 00:00:00 grep ora_ [root@prod ~]# --此时我们会看到,oracle的进程已经全部kill掉,虽然进程已经全部kill了,但是,sqlplus仍然无法启动,还需要把已分配的内存释放 |
第二步:释放分配给oracle的进程
1:显示内存共享内存信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@prod ~]# ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 3670016 root 644 80 2 0x00000000 3702785 root 644 16384 2 0x00000000 3735554 root 644 280 2 0x00000000 3801091 root 600 393216 2 dest 0x00000000 3833860 root 600 393216 2 dest 0x00000000 3866629 root 600 393216 2 dest 0x00000000 3899398 root 600 393216 2 dest 0x00000000 3932167 root 600 393216 2 dest 0x00000000 3964936 root 600 393216 2 dest 0x00000000 3997705 root 600 393216 2 dest 0x00000000 4063242 oracle 640 4096 0 0x00000000 4096011 oracle 640 4096 0 0x9513f1c8 4128780 oracle 640 4096 0 [root@prod ~]# |
2:打印出跟oracle相关的信息
1 2 3 4 5 |
[root@prod ~]# ipcs -m|grep oracle 0x00000000 4063242 oracle 640 4096 0 0x00000000 4096011 oracle 640 4096 0 0x9513f1c8 4128780 oracle 640 4096 0 [root@prod ~]# |
3:与第一步相同,获取到我们 要的shmid信息
1 2 3 4 5 |
[root@prod ~]# ipcs -m|grep oracle|awk '{print $2}' 4063242 4096011 4128780 [root@prod ~]# |
4:释放共享内存
1 2 3 |
[root@prod ~]# ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm resource(s) deleted [root@prod ~]# |
哦了,可以正常登陆SQLPLUS了
1 2 3 4 5 6 7 8 9 |
[oracle@prod ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 16:38:22 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> |