分类目录归档：Oracle

hostname配置不切当导致TNS-12542错误

发表于 2011 年 12 月 15 日由惜分飞

一、监听异常现象

[oracle@gongantest ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-DEC-2011 14:15:06

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

Starting /opt/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gongantest)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.60)(PORT=1521)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

Listener failed to start. See the error message(s) above...


[oracle@gongantest ~]$ more /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gongantest)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.60)(PORT = 1521))
    )
  )

二、相关网络配置

[oracle@gongantest ~]$ ping gongantest -c 2
PING gongantest (127.0.0.1) 56(84) bytes of data.
64 bytes from gongantest (127.0.0.1): icmp_seq=1 ttl=64 time=0.040 ms
64 bytes from gongantest (127.0.0.1): icmp_seq=2 ttl=64 time=0.016 ms

--- gongantest ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.016/0.028/0.040/0.012 ms


[oracle@gongantest ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               gongantest localhost.localdomain localhost

[oracle@gongantest ~]$ cd /etc/sysconfig/network-scripts
[oracle@gongantest network-scripts]$ more ifcfg-eth0 
# Intel Corporation 82541GI Gigabit Ethernet Controller
DEVICE=eth0
BOOTPROTO=static
BROADCAST=192.168.11.255
HWADDR=00:14:22:10:96:C9
IPADDR=192.168.11.60
NETMASK=255.255.252.0
NETWORK=192.168.8.0
ONBOOT=yes
[oracle@gongantest network-scripts]$ more ifcfg-eth1
# Intel Corporation 82541GI Gigabit Ethernet Controller
DEVICE=eth1
BOOTPROTO=dhcp
HWADDR=00:14:22:10:96:CA
ONBOOT=no
HOTPLUG=no
DHCP_HOSTNAME=gongantest
[oracle@gongantest network-scripts]$ hostname
gongantest
[oracle@gongantest network-scripts]$ more /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=gongantest
GATEWAY=192.168.8.1

三、解决问题

[oracle@gongantest ~]$ more /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.60)(PORT = 1521))
    )
  )



[oracle@gongantest etc]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-DEC-2011 14:17:54

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

Starting /opt/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.60)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                15-DEC-2011 14:17:54
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.60)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

四、补充说明
如果把hostname配置在hosts中的127.0.0.1项,那么如果在监听中使用了hostname,再配置一个host监听该机器的ip地址(非127.0.0.1),那么会报文章开头的错误,解决方法有两种:
1、监听中全部采用ip地址
2、在hosts中配置hostname对应ip地址(非127.0.0.1)

发表在 Oracle 监听 | 一条评论

ORA-06553: PLS-801: internal error [56319]

发表于 2011 年 12 月 15 日由惜分飞

开发那边有台Linux 32位服务器因为网卡问题，准备把数据库迁移到一台新服务器(Linux 64)上。因为是开发环境(对稳定性要求不是非常高，停机时间几乎没有要求)，还有我比较懒，准备两边安装相同版本，打上相同的补丁，然后直接拷贝datafile/redo/controlfile/spfile的方法完成数据文件迁移
一、源端信息

--系统信息
[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-194.el5PAE #1 SMP Tue Mar 16 22:00:21 EDT 2010 i686 i686 i386 GNU/Linux

--数据库信息
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 13:38:51 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

二、目标端信息

--操作系统信息
[oracle@gongantest ~]$ uname -a
Linux gongantest 2.6.18-194.el5 #1 SMP Fri Apr 2 14:58:14 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

--数据库信息
[oracle@gongantest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 13:43:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

三、迁移数据库
1.修改目标库ORACLE_SID变量等环境变量
2.目标库建立相关目录(参考源库)
3.使用shutdown immediate关闭源库
4.拷贝spfile和oradata中文件

四、启动目标端数据库

[oracle@gongantest gaxt]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 12:46:19 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2084136 bytes
Variable Size             369099480 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

五、收集系统统计信息

SQL> exec dbms_stats.delete_system_stats();
BEGIN dbms_stats.delete_system_stats(); END;

*
ERROR at line 1:
ORA-06553: PLS-801: internal error [56319]

六、重新编译对象

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @?/rdbms/admin/utlirp.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> shutdown immediate;
SQL> startup;

七、出现ORA-07445[_intel_fast_memcpy.J()+250]错误

--alert日志
Thu Dec 15 13:07:22 2011
Errors in file /opt/oracle/admin/gaxt/udump/gaxt_ora_13898.trc:
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()+250] [SIGSEGV] [Address not mapped to object] [0x2BA0E731928F] [] []

--trace文件
*** 2011-12-15 12:58:49.883
SERVER COMPONENT id=UTLRP_BGN: timestamp=2011-12-15 12:58:49
*** 2011-12-15 13:07:22.063
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x2ba0e731928f, PC: [0x2b9fdf587cd8, _intel_fast_memcpy.J()+250]
*** 2011-12-15 13:07:22.063
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()+250] [SIGSEGV] [Address not mapped to object] [0x2BA0E731928F] [] []
Current SQL statement for this session:
 declare 
     rc sys_refcursor; 
   begin 
     :1 := "SYS"."OLAPIMPL_T"."ODCITABLEDESCRIBE"(:2 ,'SYS.AWMD duration query','olapsys.ALL_OLAP2_AW_METADATA_T','ACTIVE_CATALOG ''ALL_CATALOGS'' ''ALL''','
MEASURE AWOWNER FROM sys.awmd!CAT_AWOWNER
                        MEASURE AWNAME FROM sys.awmd!CAT_AWNAME
                        MEASURE COL5 FROM sys.awmd!CAT_CATALOG_ID
                        MEASURE COL1 FROM sys.awmd!CAT_MEASFOLDERNAME
                        MEASURE COL2 FROM sys.awmd!CAT_MEASFOLDERDESC
                        MEASURE COL4 FROM sys.awmd!CAT_PARENTFOLDERNAME
                        DIMENSION AWMDKEY FROM sys.awmd!AWMDKEY_CAT'); 
   end;

查看MOS，发现 Oracle OLAP AWs(Analytical Workspace)在迁移过程中没有正确处理导致，不过该功能该库中没有使用到，直接忽略

八、查询组件是否都正常

SQL> col comp_name for a40
SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

COMP_NAME                                STATUS
---------------------------------------- ----------------------
Spatial                                  VALID
Oracle interMedia                        VALID
OLAP Catalog                             VALID
Oracle Enterprise Manager                VALID
Oracle XML Database                      VALID
Oracle Text                              VALID
Oracle Expression Filter                 VALID
Oracle Rules Manager                     VALID
Oracle Workspace Manager                 VALID
Oracle Data Mining                       VALID
Oracle Database Catalog Views            VALID

COMP_NAME                                STATUS
---------------------------------------- ----------------------
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  VALID
Oracle OLAP API                          VALID

九、收集系统信息

SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'START');

PL/SQL procedure successfully completed.

--一段时间后

SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');

PL/SQL procedure successfully completed.

发表在 Oracle备份恢复 | 标签为 ORA-06553 | 评论关闭

修改clob字段并插入新表

发表于 2011 年 12 月 14 日由惜分飞

最近开发有个需求，需要替换一张表的clob字段中的某些字符串，然后插入的一张新表中，我查询了一些资料，利用function结合匿名块模拟实现
0、数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1、创建替换clob中字符串函数

create or replace function replaceClob_new (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2
--newClob OUT CLOB
) RETURN CLOB
IS

vBuffer    VARCHAR2 (32767);
l_amount   BINARY_INTEGER := 32767;
l_pos      PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob  CLOB;

BEGIN
   newClob  := EMPTY_CLOB;
  -- initalize the new clob
 dbms_lob.createtemporary(newClob,TRUE);
 l_clob_len := dbms_lob.getlength(srcClob);
 WHILE l_pos < l_clob_len
  LOOP
    dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);
    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount;
  END LOOP;
  RETURN newclob;
  
 EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

2、创建原表，并测试该函数

SQL> CREATE TABLE t_clob(ID NUMBER,clob_xff CLOB);
 
Table created

--手工通过plsql dev插入一些数据（大于4000）

SQL> select dbms_lob.getlength(clob_xff) from t_clob;
 
DBMS_LOB.GETLENGTH(CLOB_XFF)
----------------------------
                        4856

SQL> select dbms_lob.getlength(replaceClob_new(clob_xff,'function','1')) from t_clob;
 
DBMS_LOB.GETLENGTH(REPLACECLOB
------------------------------
                          4814

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select replaceClob_new(clob_xff,'function','1')
  2  from t_clob where id=1;

REPLACECLOB_NEW(CLOB_XFF,'FUNCTION','1')
--------------------------------------------------
create or replace 1 replaceClob_new (
srcClob IN C

3、编写匿名块实现插入功能

DECLARE
  A_CLOB CLOB;
  R_CLOB CLOB;
  VSTRT  NUMBER(4);
BEGIN
--否则会在新表的clob字段头部会有空格
  VSTRT := 1;
  SELECT REPLACECLOB_NEW(CLOB_XFF, 'function', 'xifenfei')
    INTO R_CLOB
    FROM T_CLOB
   WHERE ID = 1;
  INSERT INTO T_CLOB_NEW
    (ID, CLOB_XFF)
  VALUES
    (1, EMPTY_CLOB())
  RETURNING CLOB_XFF INTO A_CLOB;
  DBMS_LOB.WRITE(A_CLOB, DBMS_LOB.GETLENGTH(R_CLOB), VSTRT, R_CLOB);
  COMMIT;
END;

4、测试匿名块功能

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select clob_xff from t_clob_new where id=1;

CLOB_XFF
--------------------------------------------------
create or replace xifenfei replaceClob_new (
srcCl

参考:http://space.itpub.net/111631/viewspace-605827

发表在 Oracle 开发 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

hostname配置不切当导致TNS-12542错误

ORA-06553: PLS-801: internal error [56319]

修改clob字段并插入新表

文章分类

最近发表

分类目录归档：Oracle

hostname配置不切当导致TNS-12542错误

ORA-06553: PLS-801: internal error [56319]

修改clob字段并插入新表

标签云

文章分类

最近发表