原文地址: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>