資料庫例項到nomount 使用連線串無法連線到資料庫 報ORA-12528錯誤
在用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>
[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>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30310891/viewspace-1770208/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OEM:Enterprise Manager 無法連線到資料庫例項 錯誤解決資料庫
- ORACLE 11G 無法連線到資料庫例項故障排除Oracle資料庫
- MMMNL absent錯誤 ，資料庫無法連線資料庫
- oracle 10g enterprise manager無法連線到資料庫例項Oracle 10g資料庫
- 資料庫的連線串資料庫
- 資料庫–如何連線RDS例項，使用雲資料庫？資料庫
- 連線到資料庫（JSP）資料庫JS
- 資料庫連線學到不少資料庫
- qt使用mysql,開啟資料庫，丟擲無法連線錯誤。QTMySql資料庫
- cacti登陸出現報錯:資料庫無法連線資料庫
- sql server資料庫錯誤資料恢復（資料庫連線失效，無法附加查詢）SQLServer資料庫資料恢復
- 連線到 ASP.NET 資料庫ASP.NET資料庫
- WAMP無法連線mysql資料庫MySql資料庫
- MMNL absent ,資料庫無法連線資料庫
- 【LISTENER】資料庫連線串的幾種寫法資料庫
- 連線資料庫出錯???資料庫
- Oracle10g出現Enterprise Manager 無法連線到資料庫例項解決辦法Oracle資料庫
- 手工配置OEM和Enterprise Manager無法連線資料庫例項資料庫
- Oracle 資料庫連線錯誤解決方法Oracle資料庫
- 用Navicat連線資料庫-資料庫連線(MySQL演示)資料庫MySql
- 我的postgresql資料庫報埠錯誤，連線失敗SQL資料庫
- 資料庫連線分析(1)-從JDBC到MyBatis資料庫JDBCMyBatis
- 配置ORACLE 客戶端連線到資料庫Oracle客戶端資料庫
- 使用Sequelize連線資料庫資料庫
- 使用JPA連線資料庫資料庫
- 連線資料庫資料庫
- 資料庫連線資料庫
- WMB 使用Compute節點連線Oracle資料庫例項Oracle資料庫
- JDBC連線MySQL資料庫的方法和例項JDBCMySql資料庫
- 連線RAC資料庫中單個例項（一）資料庫
- 連線RAC資料庫中單個例項（二）資料庫
- Java連線各種資料庫的例項 (轉)Java資料庫
- 使用 localhost 無法連線 MySQL 資料庫的解決方案localhostMySql資料庫
- 從連線到資料（一）
- 從連線到資料（二）
- 從連線到資料（三）
- 常用資料庫JDBC連線寫法資料庫JDBC
- 使用C#連線資料庫C#資料庫