]ORA-12528: TNS: 监听程序: 所有适用的例程都不允许建

来源：互联网发布：怎么在mac上玩dota2 编辑：程序博客网时间：2024/06/06 09:48

D:Script>set day=-12-17

D:Script>cd d:backup

D:backup>md -12-17

D:backup>sqlplus /nolog @D:scriptcold_backup.sql -12-17

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 12月 17 10:50:55 2009

已连接。
*** spooling to D:scriptcold_backup_command.sql
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLEDUREX.ORA D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLEPERFSTAT.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLESYSAUX01.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLESYSTEM01.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEST.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEST01.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEST02.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEST02_COPY.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLEUNDOTBS01.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLEUSERS01.DBF D:backup
host copy D:TESTJERRY_UNDO.DBF D:backup
host copy D:DISK3REDO01A.LOG D:backup
host copy D:DISK3REDO02A.LOG D:backup
host copy D:DISK3REDO03A.LOG D:backup
host copy D:DISK3REDO04A.LOG D:backup
host copy D:DISK3REDO05A.LOG D:backup
host copy D:DISK6REDO01B.LOG D:backup
host copy D:DISK6REDO02B.LOG D:backup
host copy D:DISK6REDO03B.LOG D:backup
host copy D:DISK6REDO04B.LOG D:backup
host copy D:DISK6REDO05B.LOG D:backup
host copy D:DISK9REDO01C.LOG D:backup
host copy D:DISK9REDO02C.LOG D:backup
host copy D:DISK9REDO03C.LOG D:backup
host copy D:DISK9REDO04C.LOG D:backup
host copy D:DISK9REDO05C.LOG D:backup
host copy D:DISK3CONTROL01.CTL D:backup
host copy D:DISK6CONTROL02.CTL D:backup
host copy D:DISK9CONTROL03.CTL D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEMP01.DBF D:backup
host copy D:ORACLEPRODUCT10.1.0ORADATAMYORACLETEMP1129.DBF D:backup
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
已复制 1 个文件。

已复制 1 个文件。

ORA-12528: TNS: 监听程序: 所有适用的例程都不允许建立新的连接

SQL> show sga
ERROR:
ORA-03114: 未连接到 ORALCE

SQL> connect sys/test;
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

SQL> startup
ORA-01031: insufficient privileges
SQL> show user;
USER 为 ""

ALERTLOG:

Archive process shutdown avoided: 0 active
Thu Dec 17 10:56:31 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
KCCDEBUG_LEVEL = 0
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
Dynamic strands is set to TRUE
Running with 2 shared and 18 private strand(s). Zero-copy redo is FALSE
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 10.1.0.2.0.
System parameters with non-default values:
processes                = 150
__shared_pool_size       = 83886080
shared_pool_size         = 83886080
large_pool_size          = 8388608
java_pool_size           = 50331648
control_files            = D:DISK3CONTROL01.CTL, D:DISK6CONTROL02.CTL, D:DISK9CONTROL03.CTL
db_block_size            = 8192
db_cache_size            = 25165824
db_cache_advice          = ON
compatible               = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest    = d:oracleproduct10.1.0flash_recovery_area
db_recovery_file_dest_size= 21474836480
undo_management          = AUTO
undo_tablespace          = JERRY_UNDO
remote_login_passwordfile= EXCLUSIVE
db_domain                =
dispatchers              = (PROTOCOL=TCP) (SERVICE=myoracleXDB)
shared_servers           = 3
job_queue_processes      = 10
background_dump_dest     = D:ORACLEPRODUCT10.1.0ADMINMYORACLEBDUMP
user_dump_dest           = D:ORACLEPRODUCT10.1.0ADMINMYORACLEUDUMP
core_dump_dest           = D:ORACLEPRODUCT10.1.0ADMINMYORACLECDUMP
sort_area_size           = 65536
db_name                  = myoracle
open_cursors             = 300
pga_aggregate_target     = 25165824
PMON started with pid=2, OS id=772
MMAN started with pid=3, OS id=3120
DBW0 started with pid=4, OS id=2264
LGWR started with pid=5, OS id=3520
CKPT started with pid=6, OS id=252
SMON started with pid=7, OS id=2608
RECO started with pid=8, OS id=3628
CJQ0 started with pid=9, OS id=1320
Thu Dec 17 10:56:32 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Dec 17 10:56:32 2009
starting up 3 shared server(s) ...
Thu Dec 17 11:03:29 2009
Starting ORACLE instance (normal)
Thu Dec 17 11:04:43 2009
Starting ORACLE instance (normal)
Thu Dec 17 11:04:45 2009
Starting ORACLE instance (normal)
Thu Dec 17 11:04:46 2009
Starting ORACLE instance (normal)

客户端无法建立连接

SQL> connect sys/test@myoracle as sysdba;
ERROR:
ORA-12528: TNS: 监听程序: 所有适用例程都无法建立新连接

服务器端：

SQL> connect / as sysdba;
已连接。
SQL> startup;
ORA-01081: ????????? ORACLE - ??????
USER 为 "SYS"
SQL> select status
2 from v$instance;

STATUS
------------------------
STARTED

已选择 1 行。

SQL> startup;
ORA-01081: ????????? ORACLE - ??????
SQL> alter session set nls_date_formate=american;
alter session set nls_date_formate=american
*
第 1 行出现错误:
ORA-00922: ???????

SQL> alter session set nls_language=american;

Session altered.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
数据库装载完毕。
数据库已经打开。
SQL> select status
2 from v$instance;

STATUS
------------
OPEN

已选择 1 行。

SQL>

执行冷备份脚本后数据库未能正常启动！

脚本较简单，执行到shutdown immediate再startup后报上述错误！无法正常启动，但在SQLPLUS中单独执行

shutdown immediate 再startup却没有问题！

再次执行脚本，故障依然，测试发现脚本没有问题，查了下资料是监听服务的问题，修改Tnsname.ora的配置在脚本中执行shutdown immediate再startup正常启动。记录一下

参考文章如下：

ORA-12528: TNS: 监听程序: 所有适用例程都无法建立新连接

lsnrctl status
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hp1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=/.pipeEXTPROCipc)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
修改listener.ora的参数,把动态的参数设置为静态的参数,红色标注部分,然后从新启动监听,就OK
SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = C:oracleproduct10.1.0db_1)
   (PROGRAM = extproc)
)
(SID_DESC =
   (GLOBAL_DBNAME = ammicly)
   (ORACLE_HOME = c:oracleproduct10.1.0db_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:oracleproduct10.1.0db_1
networkadmintnsnames.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)或者从启动服务,三种方法解决问题