数据库实例到nomount 使用连接串无法连接到数据库 报ORA-12528错误

原文地址：http://blog.itpub.net/30310891/viewspace-1770208/

在用rman来duplicate active database时，发现数据库在nomount状态下，使用连接串无法连接到数据库，报ORA-12528错误
[oracle@zlj ~]$ rman target sys/oracle@orcl auxiliary sys/oracle

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 13 11:29:17 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1414669689)
connected to auxiliary database: TEST (not mounted)

RMAN> run
2> {
3> allocate auxiliary channel C1 device type disk;
4> DUPLICATE TARGET DATABASE 
5> TO 'TEST'
6> FROM ACTIVE DATABASE;
7> release channel C1;
8>}

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=18 device type=DISK

Starting Duplicate Db at 13-AUG-15
released channel: C1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/13/2015 11:29:22
RMAN-05501: aborting duplication of target database
RMAN-06217: not connected to auxiliary database with a net service name

报网络连接串错误

[oracle@zlj ~]$ tnsping test

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 13-AUG-2015 11:30:28

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.88)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test)))
OK (0 msec)

[oracle@zlj ~]$ lsnrctl  status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-AUG-2015 11:20:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.186.88)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                13-AUG-2015 10:15:02
Uptime                    0 days 1 hr. 5 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/base/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/base/diag/tnslsnr/zlj/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.186.88)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@zlj ~]$ 

[oracle@zlj admin]$ more tnsnames.ora 
      (SERVICE_NAME = test)
# tnsnames.ora Network Configuration File: /u01/app/base/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.88)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

以上查看了监听的状态以及连接串的配置，均没有问题，但使用连接串去连数据库却失败
[oracle@zlj admin]$ sqlplus sys/oracle@test as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 13 11:34:25 2015

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


Enter user-name: ^C

原因是ORACLE 11g数据库中，pmon进程将实例信息动态注册到监听，数据库在此nomount状态下监听无法识别连接者身份，会拒绝一切连接，所以报ORA-12528错误
解决此问题有两种方法：
第一种是在listener.ora中直接将实例信息静态注册到监听
[oracle@zlj admin]$ more listener.ora 
# listener.ora Network Configuration File: /u01/app/base/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/base/product/11.2.0/db_1)
      (SID_NAME = test)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.88)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/base

第二种是修改tnsname.ora中的内容，在连接串中添加(UR=A)来修复动态注册的监听在数据库nomount状态下拒绝的一切连接
[oracle@zlj admin]$ vi tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/base/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.88)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
     (UR=A)
    )
  )
~
~
"tnsnames.ora" 34L, 704C written                                                                                                                  
[oracle@zlj admin]$ sqlplus sys/oracle@test as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 13 11:35:14 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

[oracle@zlj ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@test

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 13 12:21:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1414669689)
connected to auxiliary database: TEST (not mounted)
RMAN> duplicate target database to 'TEST' from active database;

Starting Duplicate Db at 13-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/test/control01.ctl';
   restore clone controlfile to  '/oradata/test/control02.ctl' from 
 '/oradata/test/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     242208768 bytes

Fixed Size                     2227176 bytes
Variable Size                184550424 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5099520 bytes

Starting backup at 13-AUG-15
allocated channel: ORA_DISK_1
.
.
.
.
Executing: alter database force logging

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 13-AUG-15

RMAN>