ORA-12528问题解决与静态注册

最新推荐文章于 2021-05-25 14:22:08 发布

一只小飞鱼

最新推荐文章于 2021-05-25 14:22:08 发布

阅读量4.2k

点赞数

文章标签： list oracle 数据库 database 远程连接 tcp

原文：http://blog.chinaunix.net/u/9800/showart_396710.html

startup nomount 之后

用sqlplus sys/orcl@orcl as sysdba 连接的时候报 ORA-12528 的错误

但是用 sqlplus "/as sysdba", conn sys/orcl as sysdba 就可以连接上。

搜了下 ORA-12528 这个错误。

将listener.ora的动态参数设置为静态参数之后，在 nomount 时，也可以用 sqlplus sys/orcl@orcl as sysdba登录了。

原文：http://blog.chinaunix.net/u/9800/showart_396710.html

lsnrctl status
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lypch)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROCipc)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
Instance "LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ammicly" has 1 instance(s).
Instance "ammicly", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

这个问题说明数据库没有Mount
连接数据库,发现:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
用sqlplus /nolog
sql>connect [email=sys/ammic@amicly]sys/ammic@amicly[/email] as sysdba
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

连接的问题可以通过修改listener.ora的参数,把动态的参数设置为静态的参数,红色标注部分,然后从新启动监听,就OK
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:/oracle/product/10.1.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ammicly)
      (ORACLE_HOME = c:/oracle/product/10.1.0/db_1)
      (SID_NAME = ammicly)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
)

通过修改tnsnames.ora的参数,红色表注部分

# tnsnames.ora Network Configuration File: C:/oracle/product/10.1.0/db_1
/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
AMMICLY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ammicly)
      (UR=A)
    )
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

然后 sqlplus /nolog
SQL>alter database mount;
SQL>alter database open;

这样就可以启动数据库了;

或者用Oradim -shutdown -sid ammicly -shuttype srvc,inst -shutmode immediate -syspwd ammic;关闭数据库
在用oradim -startup -sid ammicly -starttype srvc,inst -syspwd ammic;启动数据库解决;也可以在截面上从启动数据库服务来解决;

总结ORA-12528问题是因为监听中的服务使用了动态服务,系统启动后,数据库没有正常的MOUNT,因此在动态模式下,就会出现这个问题，用静态的就不会有这个问题,因此上面的方法就是把监听设置为静态,或者在tnsnames.ora中追加(UR=A)或者从启动服务,三种方法解决问题;

用静态注册的风险是，如果在instance运行中，lisener重新启动，就找不到instance了。换言之，静态注册需要先启动lisener，再启动instance。而且静态模式下，lisener status显示的是unknown

而且，用动态的话，instance nomount状态下，一般是dba需要进行操作（比如恢复），这个时候一般直接登陆到host进行，不使用tns远程连接。

--------------------------------------------------------
--------------------------------------------------------

listner.ora 修改之后：

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/10.2.0.1/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /oracle/app/oracle/product/10.2.0.1/db_1)
        (SID_NAME = orcl)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
     )
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
    )
)

修改之前：

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/10.2.0.1/db_1)
      (PROGRAM = extproc)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
)
注意：一个
LISTENER
(
......
)
对应一个
SID_LIST_LISTENER
(
....
)
如果修改有误，报错：
Oracle Error :: TNS-01155 Incorrectly specified SID_LIST_string parameter in LISTENER.ORA