2014-06-06 – OracleBlog

12c的网络设置

2014-06-06 没有评论

12c开始，对于pdb一般都是需要tnsname登录了，在这里记录一下主要的3个网络文件配置。 listener.ora

# listener.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora12c)
      (ORACLE_HOME = /u01/ora12c/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ora12c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pdb1)
      (ORACLE_HOME = /u01/ora12c/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ora12c)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))
  )

ADR_BASE_LISTENER = /u01/ora12c/app/oracle

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

# listener.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora12c)

(ORACLE_HOME = /u01/ora12c/app/oracle/product/12.1.0/db_1)

(SID_NAME = ora12c)

)

(SID_DESC =

(GLOBAL_DBNAME = pdb1)

(ORACLE_HOME = /u01/ora12c/app/oracle/product/12.1.0/db_1)

(SID_NAME = ora12c)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))

)

ADR_BASE_LISTENER = /u01/ora12c/app/oracle

tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora12c)
    )
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb1)
    )
  )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

# tnsnames.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORA12C =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ora12c)

)

PDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.132)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = pdb1)

)

sqlnet.ora […]

..experience, Working case

Job中报错ora-1493，no data found

2014-06-06 没有评论

客户这边遇到个问题，他们有个package，在job中定期运行，但是会出现时不时的报错ora-1493，no data found。定位引发ora-1493，no data found的语句为：

SELECT sid, serial#
  INTO v_sid, v_serial#
  FROM v$session
WHERE sid =
       (SELECT MAX(sid) FROM v$session WHERE audsid = userenv('sessionid'));

1

2

3

4

5

SELECT sid, serial#

INTO v_sid, v_serial#

FROM v$session

WHERE sid =

(SELECT MAX(sid) FROM v$session WHERE audsid = userenv('sessionid'));

这样的语句，放在客户端中发起，是没有 […]

..experience, Working case

11g启动报错ORA-00371: not enough shared pool memory

2014-06-06 没有评论

问题： 11g的一个库，启库时，报错shared pool不够。由于我是在一个测试的机器上，我不需要那么多的share pool，我只需800M的shared pool就够了。但指定shared pool的大小为800M在初始化文件中，起库就报错： SQL> startup ORA-00371: […]

..experience, Working case

data file init write等待

2014-06-06 没有评论

发生data file init write的等待是数据文件正在发生扩展，在11g中，这往往和SMCO和Wnnn进程的自动预扩展有关。在生产环境中，如果在生产高峰期出现预扩展，可能会造成短暂的hang住，或者CPU突然的升高，或者查询dba_free_space的hang住。但是，预扩展这个功能是 […]

..experience, Working case

删除分区缓慢的一次分析

2014-06-06 没有评论

在客户处遇到个问题，客户昨晚清理数据，删除一些表的分区，第一个表跑了20多分钟，但是差不多数据量的另外一个表，跑了好几个小时才完成。为何类似的数据在drop 分区的时候，有那么大的差异？检查了改删除分区的语句，发现是update了global索引，并且，还用到了parallel 8的并行。 [cr […]