grid restart启动的数据库sqlplus 无法访问的问题

郑全

1.数据库已经启动:

[oracle@dbserver ~]$ ps -ef|grep ora_
oracle    9586     1  0 00:20 ?        00:00:00 ora_pmon_sztech
oracle    9588     1  0 00:20 ?        00:00:00 ora_psp0_sztech
oracle    9591     1  0 00:20 ?        00:00:00 ora_vktm_sztech
oracle    9595     1  0 00:20 ?        00:00:00 ora_gen0_sztech
oracle    9597     1  0 00:20 ?        00:00:00 ora_diag_sztech
oracle    9599     1  0 00:20 ?        00:00:00 ora_dbrm_sztech
oracle    9601     1  0 00:20 ?        00:00:00 ora_dia0_sztech
oracle    9603     1  0 00:20 ?        00:00:00 ora_mman_sztech
oracle    9605     1  0 00:20 ?        00:00:00 ora_dbw0_sztech
oracle    9607     1  0 00:20 ?        00:00:00 ora_lgwr_sztech
oracle    9609     1  0 00:20 ?        00:00:00 ora_ckpt_sztech
oracle    9611     1  0 00:20 ?        00:00:00 ora_smon_sztech
oracle    9613     1  0 00:20 ?        00:00:00 ora_reco_sztech
oracle    9615     1  0 00:20 ?        00:00:00 ora_rbal_sztech
oracle    9617     1  0 00:20 ?        00:00:00 ora_asmb_sztech
oracle    9619     1  0 00:20 ?        00:00:01 ora_mmon_sztech
oracle    9623     1  0 00:20 ?        00:00:00 ora_mmnl_sztech
oracle    9625     1  0 00:20 ?        00:00:00 ora_d000_sztech
oracle    9627     1  0 00:20 ?        00:00:00 ora_mark_sztech
oracle    9629     1  0 00:20 ?        00:00:00 ora_s000_sztech
oracle    9682     1  0 00:20 ?        00:00:00 ora_qmnc_sztech
oracle    9695     1  0 00:20 ?        00:00:00 ora_q000_sztech
oracle    9697     1  0 00:20 ?        00:00:00 ora_q001_sztech
oracle    9716     1  0 00:20 ?        00:00:00 ora_cjq0_sztech
oracle    9940     1  0 00:25 ?        00:00:00 ora_smco_sztech
oracle   10306     1  0 00:35 ?        00:00:00 ora_j000_sztech
oracle   10308     1  0 00:35 ?        00:00:00 ora_j001_sztech
oracle   10310     1  0 00:35 ?        00:00:00 ora_w000_sztech
oracle   10314  9213  0 00:35 pts/2    00:00:00 grep ora_

2.使用conn / as sysdba

[oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 00:35:30 2012

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

SQL> conn / as sysdba
Connected to an idle instance.

3.使用srvctl 查看 数据库的状态

[oracle@dbserver ~]$ srvctl status database -d sztech
Database is running.

4.使用srvctl 停止数据库

  srvctl stop database -d sztech

  [oracle@dbserver ~]$ ps -ef|grep ora_
oracle   10520  9213  0 00:39 pts/2    00:00:00 grep ora_
[oracle@dbserver ~]$ srvctl status database -d sztech
Database is not running.

   表明数据库已经停止

5.再用sqlplus 启动数据库
  sqlplus /nolog

  SQL> connect / as sysdba
  Connected to an idle instance.

  SQL> startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             230689652 bytes
Database Buffers           75497472 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.

[oracle@dbserver ~]$ srvctl status database -d sztech
Database is running.
[oracle@dbserver ~]$ ps -ef|grep ora_
oracle   10637     1  0 00:40 ?        00:00:00 ora_pmon_sztech
oracle   10639     1  0 00:40 ?        00:00:00 ora_psp0_sztech
oracle   10641     1  0 00:40 ?        00:00:00 ora_vktm_sztech
oracle   10645     1  0 00:40 ?        00:00:00 ora_gen0_sztech
oracle   10647     1  0 00:40 ?        00:00:00 ora_diag_sztech
oracle   10649     1  0 00:40 ?        00:00:00 ora_dbrm_sztech
oracle   10651     1  0 00:40 ?        00:00:00 ora_dia0_sztech
oracle   10653     1  0 00:40 ?        00:00:00 ora_mman_sztech
oracle   10655     1  0 00:40 ?        00:00:00 ora_dbw0_sztech
oracle   10657     1  0 00:40 ?        00:00:00 ora_lgwr_sztech
oracle   10659     1  0 00:40 ?        00:00:00 ora_ckpt_sztech
oracle   10661     1  0 00:40 ?        00:00:00 ora_smon_sztech
oracle   10663     1  0 00:40 ?        00:00:00 ora_reco_sztech
oracle   10665     1  0 00:40 ?        00:00:00 ora_rbal_sztech
oracle   10667     1  0 00:40 ?        00:00:00 ora_asmb_sztech
oracle   10669     1  1 00:40 ?        00:00:01 ora_mmon_sztech
oracle   10673     1  0 00:40 ?        00:00:00 ora_mark_sztech
oracle   10675     1  0 00:40 ?        00:00:00 ora_mmnl_sztech
oracle   10677     1  0 00:40 ?        00:00:00 ora_ocf0_sztech
oracle   10679     1  0 00:40 ?        00:00:00 ora_d000_sztech
oracle   10683     1  0 00:40 ?        00:00:00 ora_s000_sztech
oracle   10712     1  0 00:40 ?        00:00:00 ora_o000_sztech
oracle   10723     1  0 00:41 ?        00:00:00 ora_qmnc_sztech
oracle   10757     1  0 00:41 ?        00:00:00 ora_cjq0_sztech
oracle   10761     1  0 00:41 ?        00:00:00 ora_q000_sztech
oracle   10763     1  0 00:41 ?        00:00:00 ora_q001_sztech
oracle   10807     1  1 00:42 ?        00:00:00 ora_j000_sztech
oracle   10809     1  0 00:42 ?        00:00:00 ora_j001_sztech
oracle   10854  9213  0 00:42 pts/2    00:00:00 grep ora_
[oracle@dbserver ~]$

看来数据库确实已经启动

6.再次使用sqlplus 连接数据库

[oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 00:43:47 2012

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

SQL> connect / as sysdba
Connected.

表明使用sqlplus 启动后,可以连接到数据库

7.再次使用srvctl 停止数据库,却包错了

[oracle@dbserver ~]$ srvctl stop database -d sztech
PRCR-1133 : Failed to stop database sztech and its running services
PRCR-1132 : Failed to stop resources using a filter
CRS-5017: The resource action "ora.sztech.db stop" encountered the following error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00108:)" in "/oracle/app/grid/product/11.2.0/grid/log/dbserver/agent/ohasd/oraagent_grid/oraagent_grid.log".

CRS-2675: Stop of 'ora.sztech.db' on 'dbserver' failed
CRS-5017: The resource action "ora.sztech.db stop" encountered the following error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00108:)" in "/oracle/app/grid/product/11.2.0/grid/log/dbserver/agent/ohasd/oraagent_grid/oraagent_grid.log".

CRS-2675: Stop of 'ora.sztech.db' on 'dbserver' failed

8.使用sqlplus 停止数据库

[oracle@dbserver ~]$ sqlplus /nolog
c
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 01:04:25 2012

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

SQL> onnect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

9.再用srvctl 启动数据库
[oracle@dbserver ~]$ srvctl start database -d sztech
[oracle@dbserver ~]$

10.使用sqlplus 再次连接数据库
   依旧无法连接:
   [oracle@dbserver ~]$ sqlplus /nolog
c
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 01:06:17 2012

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

SQL> onnect / as sysdba
Connected to an idle instance.

连接到空实例

SQL> startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             230689652 bytes
Database Buffers           75497472 bytes
Redo Buffers                6328320 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

启动又包错
看报警日志,有如下信息:

ORACLE_BASE from environment = /oracle/app/oracle/
Mon Jun 25 01:06:40 2012
ALTER DATABASE   MOUNT
sculkget: failed to lock /oracle/app/oracle//product/11.2.0/db_1/dbs/lkSZTECH exclusive
sculkget: lock held by PID: 12132
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 12132
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

看看这个
/oracle/app/oracle//product/11.2.0/db_1/dbs/lkSZTECH

这里有两个//
 看来这个是环境变量有问题,

11.检查 数据库的配置信息

  [grid@dbserver oraagent_grid]$ srvctl config database -d sztech
Database unique name: sztech
Database name: sztech
Oracle home: /oracle/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/sztech/spfilesztech.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: sztech
Disk Groups: DATA
Services:

 

12.使用连接串连接,又可以正确连接

[oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 01:13:52 2012

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

SQL> connect sys/oracle@sztech as sysdba
Connected.

  再次说明,肯定是环境变量有问题
13.检查环境变量
  [oracle@dbserver ~]$ env |grep ORACLE
ORACLE_SID=sztech
ORACLE_BASE=/oracle/app/oracle/
ORACLE_HOME=/oracle/app/oracle//product/11.2.0/db_1

   看来确实是oracle_base最后,多了一个/
   去掉,再试连接

14.修改之后
   [oracle@dbserver ~]$ env|grep ORACLE
ORACLE_SID=sztech
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1

   再去验证
   [oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 25 01:17:45 2012

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

SQL> connect / as sysdba
Connected.
SQL>
   说明确实可以了。

15。再使用srvctl stop 停止
   [oracle@dbserver ~]$ srvctl stop database -d sztech
PRCC-1016 : sztech was already stopped

问题终于找到，原来是oracle_base环境变量设置有问题。

以上问题在：linux 5.5 +oracle 11g 单实例上测试，安装grid,再安装数据库，
grid使用用户grid安装，数据库使用用户oracle安装。