admin – 第93页 – Database SOS

hosts中缺少localhost.localdomain导致监听启动时间超长

0、基本信息

[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 ~]$ hostname
localhost.localdomain
[oracle@localhost ~]$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:14:22:10:96:AE
          inet addr:192.168.9.66  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96ae/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:6597875 errors:0 dropped:0 overruns:0 frame:0
          TX packets:9785915 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:2526369384 (2.3 GiB)  TX bytes:295978844 (282.2 MiB)
eth0:1    Link encap:Ethernet  HWaddr 00:14:22:10:96:AE
          inet addr:外网ip  Bcast:211.155.227.175  Mask:255.255.255.240
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
eth1      Link encap:Ethernet  HWaddr 00:14:22:10:96:AF
          inet addr:192.168.11.50  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96af/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:999903 errors:0 dropped:0 overruns:0 frame:0
          TX packets:283 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:104688608 (99.8 MiB)  TX bytes:22777 (22.2 KiB)
lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:8816738 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8816738 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:2726275798 (2.5 GiB)  TX bytes:2726275798 (2.5 GiB)
[oracle@localhost ~]$ more /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=localhost.localdomain

1、hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1                 localhost

2、监听文件

[oracle@localhost ~]$ more /opt/oracle/product/10g/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = gaxt)
      (ORACLE_HOME = /opt/oracle/product/10g)
      (SID_NAME = gaxt)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 外网IP)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

3、启动监听

[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-DEC-2011 23:27:32
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Starting /opt/oracle/product/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10g/network/admin/listener.ora
Log messages written to /opt/oracle/product/10g/network/log/listener.log
Trace information written to /tmp/listener_trc.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=外网IP)(PORT=1521)))
--这里卡了很久很久才显示成功
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                15-DEC-2011 23:27:32
Uptime                    0 days 0 hr. 3 min. 9 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10g/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10g/network/log/listener.log
Listener Trace File       /tmp/listener_trc.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=外网IP)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "gaxt" has 1 instance(s).
  Instance "gaxt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4、开启监听trace

--在/opt/oracle/product/10g/network/admin/listener.ora中加入下列参数
TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER= LISTENER_TRC
TRACE_DIRECTORY_LISTENER=/tmp

6、重启监听查看trace文件

[15-DEC-2011 23:33:32:938] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:33:32:938] New trace stream is /tmp/listener_trc.trc
[15-DEC-2011 23:33:32:938] New trace level is 16
…………………………
[15-DEC-2011 23:33:32:981] nsnainconn: no native services in use - returning
[15-DEC-2011 23:33:32:981] nsnainconn: signalling that calling function should not continue
[15-DEC-2011 23:33:32:982] nsnainconn: normal exit
[15-DEC-2011 23:33:32:982] nsnaconn: normal exit
[15-DEC-2011 23:33:32:982] nsaccept: exit (0)
[15-DEC-2011 23:36:41:965] nsglma: WARNING - SNMP master agent is not running OR snmp.ora file does not exist.
[15-DEC-2011 23:36:41:965] nsevwait: entry
[15-DEC-2011 23:36:41:965] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:36:41:965] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:36:41:965] nsevwait: waiting for transport event (1 thru 4)...
[15-DEC-2011 23:36:41:965] nsevwait: 1 newly-posted event(s)

这里可以看出监听启动的过程中，在这个地方等待了3分钟之久
MOS:Lsnrctl Start is Extremely Slow or Appears to Hang

7、修改hosts文件

[oracle@localhost ~]$ more /etc/hosts
127.0.0.1               localhost.localdomain localhost

8、重启监听查看trace文件

[15-DEC-2011 23:43:44:555] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[15-DEC-2011 23:43:44:555] New trace stream is /tmp/listener_trc.trc
……………………
[15-DEC-2011 23:43:44:627] nsevwait: entry
[15-DEC-2011 23:43:44:627] nsevwait: 3 registered connection(s)
[15-DEC-2011 23:43:44:627] nsevwait: 0 pre-posted event(s)
[15-DEC-2011 23:43:44:627] nsevwait: waiting for transport event (1 thru 4)...

这里可以看出监听启动一共使用时间为:几十毫秒

9、结论
在主机名为localhost.localdomain的hosts文件中缺少127.0.0.1 localhost.localdomain,哪怕监听中配置的是ip地址,也将导致监听启动时间非常长。如果主机名不是localhost.localdomain，那hosts中缺少localhost.localdomain没有影响监听启动。

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

一、监听异常现象

[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)

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

开发那边有台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.

修改clob字段并插入新表

最近开发有个需求，需要替换一张表的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

今天去省公安厅部署系统，那边没有网络，不用使用自己的电脑，突然发现原来网络是那么的美好，原来自己电脑上的资料是那么的珍贵；也发现一个问题，做技术的要坦实，是实实在在的懂，而不是说我有个了解，然后google下就可以了。今天就遇到有Linux服务器root密码忘记了，因为sa没有过去，开发不懂，只能我去重设，因为以前这些活都是sa做的，自己有所了解但是记忆不深，今天在自己的大脑中拼命的搜索，不断的尝试，终于修改成功了。晚上回家在vm中做了linux 4/5/6三个版本的修改密码方法截，给自己留个记录，也供大家参考，免得临时手忙脚乱
Linux重设root密码

模拟跨resetlogs恢复

一、模拟跨越resetlog恢复环境

[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:09:29 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA11G (DBID=4162194039)
RMAN>  list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       CURRENT 995548     2011-10-31 16:05:14
RMAN> backup database format '/tmp/ora11g_%U.rman';
Starting backup at 2011-12-12 13:10:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:10:50
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:17
piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:13:20
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:22
piece handle=/tmp/ora11g_02mu1avd_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2011-12-12 13:13:22
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area    2137886720 bytes
Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes
RMAN> recover database;
Starting recover at 2011-12-12 13:14:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2011-12-12 13:14:58
RMAN>  alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 13:15:14
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:16:02 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:17:47 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA11G (DBID=4162194039)
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:18:34 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:18:53 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA11G (DBID=4162194039)
RMAN> backup database format '/tmp/ora11g_new_%U';
Starting backup at 2011-12-12 13:19:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:19:31
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:56
piece handle=/tmp/ora11g—_new_03mu1bb3_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:20:57
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:58
piece handle=/tmp/ora11g—_new_04mu1bdo_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-12-12 13:20:58
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
--备份成功后还切换日志，为了证明可以利用这些日志恢复
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ cd /opt/oracle/oradata/ora11g/
[oracle@node1 ora11g]$ ll
总计 2568524
-rw-r----- 1 oracle oinstall   9846784 12-12 13:27 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 12-12 13:22 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo01.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo02.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:26 redo03.log
-rw-r----- 1 oracle oinstall 828383232 12-12 13:22 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 12-12 13:22 system01.dbf
-rw-r----- 1 oracle oinstall 235937792 12-11 18:05 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 12-12 13:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 169091072 12-12 13:22 users01.dbf
[oracle@node1 ora11g]$ rm redo0*
[oracle@node1 ora11g]$ rm *.dbf
[oracle@node1 ora11g]$ ll
总计 9636
-rw-r----- 1 oracle oinstall 9846784 12-12 13:31 control01.ctl
--删除除控制文件外的所有文件
--今天产生的归档日志，从这个里面也可以看到resetlogs操作的界限
[oracle@node1 ora11g]$ ll /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/
总计 152120
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_104_7gc3cogp_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_105_7gc3co97_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_106_7gc3cv1w_.arc
-rw-r----- 1 oracle oinstall 11425792 12-12 13:17 o1_mf_1_107_7gc3mbpr_.arc
-rw-r----- 1 oracle oinstall   984576 12-12 13:18 o1_mf_1_1_7gc3ojqw_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:18 o1_mf_1_2_7gc3okx8_.arc
-rw-r----- 1 oracle oinstall     4608 12-12 13:18 o1_mf_1_3_7gc3onnq_.arc
-rw-r----- 1 oracle oinstall     1536 12-12 13:22 o1_mf_1_4_7gc3wnvf_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:22 o1_mf_1_5_7gc3wt48_.arc

二、跨越resetlogs恢复

[oracle@node1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:22:50 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area    2137886720 bytes
Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30
--指定incarnation恢复
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN> restore database;
Starting restore at 2011-12-12 13:33:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ora11g_01mu1aqq_1_1.rman
channel ORA_DISK_1: piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 2011-12-12 13:34:51
--证明恢复会使用incarnation 2对应的备份集
RMAN> recover database;
Starting recover at 2011-12-12 13:35:13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 106 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc
archived log for thread 1 with sequence 107 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc thread=1 sequence=106
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc thread=1 sequence=107
media recovery complete, elapsed time: 00:00:03
Finished recover at 2011-12-12 13:35:18
--incarnation 2数据还原恢复完成
--指定恢复incarnation 3归档日志
RMAN>  reset database to incarnation 3;
database reset to incarnation 3
RMAN> recover database;
Starting recover at 2011-12-12 13:49:36
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc thread=1 sequence=1
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc thread=1 sequence=2
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc thread=1 sequence=3
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc thread=1 sequence=4
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/12/2011 13:49:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 12882851
--缺少归档日志，恢复完seq=5的归档日志，属于正常情况
--因为没有redo，进行的是不完全恢复，使用resetlogs开打数据库
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
--发现意外出现ORA-00600[2898]错误，打开数据库终止

三、查看alert日志

Mon Dec 12 14:05:59 2011
SMON: enabling cache recovery
[26406] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1208679594 end:1208679684 diff:90 (0 seconds)
Dictionary check beginning
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc  (incident=4953):
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_4953/ora11g_ora_26406_i4953.trc
Mon Dec 12 14:06:02 2011
Dumping diagnostic data in directory=[cdmp_20111212140602], requested by (instance=1, osid=26406), summary=[incident=4953].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 26406): terminating the instance due to error 600
Mon Dec 12 14:06:03 2011
Instance terminated by USER, pid = 26406
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (26406) as a result of ORA-1092
Mon Dec 12 14:06:04 2011
ORA-1092 : opitsk aborting process

发现ORA-00600[2898]错误，导致数据库abort，因为这个错误暂时未找到权威说明。初步怀疑是因为在resetlogs时候，遇到File #5 is offline, but is part of an online tablespace导致

四、重新开启数据库

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:08:28 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         5 OFFLINE      12881970
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5             0
SQL> select FILE#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5      12881970

意外的发现数据库竟然open成功，从这里可以看到datafile 5处于offline状态，而且其数据文件头部scn比其他文件小，怀疑没有恢复到一致状态

五、查看重启数据库后alert日志

Archived Log entry 109 added for thread 1 sequence 1 ID 0xf84e7829 dest 1:
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Mon Dec 12 14:09:01 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01186: ?? 201 ??????
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /opt/oracle/oradata/ora11g/temp01.dbf
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28225.trc  (incident=6153):
ORA-25319: Queue table repartitioning aborted
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6153/ora11g_ora_28225_i6153.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Mon Dec 12 14:09:03 2011
QMNC started with pid=31, OS id=28288
LOGSTDBY: Validating controlfile with logical metadata
Mon Dec 12 14:09:04 2011
Dumping diagnostic data in directory=[cdmp_20111212140904], requested by (instance=1, osid=28225), summary=[incident=6153].
LOGSTDBY: Validation complete
Completed: alter database open
Mon Dec 12 14:09:04 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6121):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6121/ora11g_mmon_28190_i6121.trc
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6122):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6122/ora11g_mmon_28190_i6122.trc
Dumping diagnostic data in directory=[cdmp_20111212140906], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6121].
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6123):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6123/ora11g_mmon_28190_i6123.trc
Dumping diagnostic data in directory=[cdmp_20111212140907], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6122].
error 25319 happened during Queue table repartitioning
Dumping diagnostic data in directory=[cdmp_20111212140908], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6123].
Mon Dec 12 14:09:08 2011
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Dec 12 14:09:08 2011
Starting background process CJQ0
Mon Dec 12 14:09:08 2011
CJQ0 started with pid=32, OS id=28308
Mon Dec 12 14:09:39 2011
Sweep [inc][6153]: completed
Sweep [inc][6123]: completed
Sweep [inc][6122]: completed

因为第一个resetlogs没有成功，所以temp文件没有创建，这里先创建了temp文件.还有个ORA-25319的错误，和datafile 5 offline有关系

六、trace文件中内容

*** 2011-12-12 14:17:46.627
Started Serial Media Recovery
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Media Recovery apply resetlogs offline range for datafile 5, incarnation : 1
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000.00c493a4 12/12/2011 14:05:53
Resetlogs 1 scn and time: 0x0000.00c49033 12/12/2011 13:17:30
Recovery target incarnation = 4, activation ID = 0
Influx buffer limit = 37449 min(50% x 74898, 100000)
Start recovery at thread 1 ckpt scn 12881971 logseq 1 block 2
Initial buffer sizes: read 1024K, overflow 832K, change 805K
*** 2011-12-12 14:17:46.725
Media Recovery add redo thread 1
*** 2011-12-12 14:18:47.348
Media Recovery Log 2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
*** 2011-12-12 14:19:00.198
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
*** 2011-12-12 14:19:15.911
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
*** 2011-12-12 14:19:22.638
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
*** 2011-12-12 14:19:31.007
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
*** 2011-12-12 14:19:37.116
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Thread 1 initialized for new incarnation 1 at scn 12882852
Media Recovery current incarnation depth : 0
File 5 (stop scn 12882852) completed recovery at checkpoint scn 12882852

从这里可以知道，datafile 5，没有应用o1_mf_1_5_7gc3wt48_.arc日志恢复，那么恢复datafile 5 需要o1_mf_1_5_7gc3wt48_.arc日志文件

七、恢复数据文件5，打开数据库

SQL> recover datafile 5 ;
ORA-00279: change 12881971 generated at 12/12/2011 13:14:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.
arc
ORA-00280: change 12881971 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL>

kkjcre1p: unable to spawn jobq slave process, error 1089

闲着无聊，看历史的alert文件，发现kkjcre1p: unable to spawn jobq slave process, error 1089警告

Thu Jul  8 21:23:39 2010
Starting background process CJQ0
CJQ0 started with pid=18, OS id=9019
Thu Jul  8 21:23:39 2010
Shutting down instance: further logons disabled
Thu Jul  8 21:23:42 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
………………
Thu Jul  8 21:23:57 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
Thu Jul  8 21:23:59 2010
Stopping background process QMNC
Thu Jul  8 21:23:59 2010
Stopping background process CJQ0
Thu Jul  8 21:24:01 2010
Stopping background process MMNL
Thu Jul  8 21:24:02 2010
Stopping background process MMON
Thu Jul  8 21:24:03 2010
Shutting down instance (immediate)
License high water mark = 1
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Thu Jul  8 21:24:05 2010
ALTER DATABASE CLOSE NORMAL
Thu Jul  8 21:24:05 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Jul  8 21:24:05 2010
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 2
Successful close of redo thread 1
Thu Jul  8 21:24:05 2010
Completed: ALTER DATABASE CLOSE NORMAL
Thu Jul  8 21:24:05 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active

警告原因
If a job is about to be spawned when shutdown of database is in progress, you will see these errors in the alert log file and this is perfectly valid.

解决方法
1、这个警告可以安全的忽略
There is no harm at all because of this warning being logged to the alert.log The Error can be safely ignored as the job coordinator process tried to spawn a job slave when the Shutdown was in progress.

2、设置_JOB_QUEUE_INTERVAL更大值，减少出现该警告概率
One workaround that we can suggest is to set an underscore parameter
_JOB_QUEUE_INTERVAL=120 or greater value
The default value is 60 but when we change to 120 there are less chances of getting the above warnings in the alert log file.
Kkjcre1p: Unable To Spawn Jobq Slave Process, Error 1089 [ID 344275.1]

ORA-00600[4194]故障解决

朋友数据库因为断电，导致数据库正常启动片刻之后，自动down掉
一、alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 58720256
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 4194304
  nls_date_format          = yyyy-mm-dd hh24:mi:ss
  sga_target               = 335544320
  control_files            = /u02/ezhou/control01.ctl
  db_block_size            = 8192
  compatible               = 10.2.0.1.0
  log_archive_dest         = /u02/arch
  log_archive_max_processes= 10
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=ezhouXDB)
  job_queue_processes      = 10
  background_dump_dest     = /u01/pp/oracle/admin/ezhou/bdump
  user_dump_dest           = /u01/pp/oracle/admin/ezhou/udump
  core_dump_dest           = /u01/pp/oracle/admin/ezhou/cdump
  audit_file_dest          = /u01/pp/oracle/admin/ezhou/adump
  db_name                  = ezhou
  open_cursors             = 400
  sql_trace                = TRUE
  pga_aggregate_target     = 94371840
MMAN started with pid=4, OS id=5539
PMON started with pid=2, OS id=5535
DBW0 started with pid=5, OS id=5541
LGWR started with pid=6, OS id=5543
SMON started with pid=8, OS id=5547
CJQ0 started with pid=10, OS id=5577
RECO started with pid=9, OS id=5575
Sat Dec 10 17:15:40 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'
MMNL started with pid=12, OS id=5581
MMON started with pid=11, OS id=5579
Sat Dec 10 17:15:40 2011
starting up 1 shared server(s) ...
PSP0 started with pid=3, OS id=5537
CKPT started with pid=7, OS id=5545
Sat Dec 10 17:15:42 2011
ALTER DATABASE   MOUNT
Sat Dec 10 17:15:46 2011
Setting recovery target incarnation to 3
Sat Dec 10 17:15:47 2011
Successful mount of redo thread 1, with mount id 4055654398
Sat Dec 10 17:15:47 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sat Dec 10 17:15:47 2011
ALTER DATABASE OPEN
Sat Dec 10 17:15:47 2011
Beginning crash recovery of 1 threads
Sat Dec 10 17:15:47 2011
Started redo scan
Sat Dec 10 17:15:48 2011
Completed redo scan
 319 redo blocks read, 98 data blocks need recovery
Sat Dec 10 17:15:50 2011
Started redo application at
 Thread 1: logseq 24, block 3
Sat Dec 10 17:15:50 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo03.log
Sat Dec 10 17:15:50 2011
Completed redo application
Sat Dec 10 17:15:51 2011
Completed crash recovery at
 Thread 1: logseq 24, block 322, scn 6168722
 98 data blocks read, 98 data blocks written, 319 redo blocks read
Sat Dec 10 17:15:51 2011
LGWR: STARTING ARCH PROCESSES
ARC1 started with pid=17, OS id=5645
ARC0 started with pid=16, OS id=5643
ARC3 started with pid=19, OS id=5649
ARC4 started with pid=20, OS id=5651
ARC2 started with pid=18, OS id=5647
ARC6 started with pid=22, OS id=5655
ARC7 started with pid=23, OS id=5657
ARC5 started with pid=21, OS id=5653
ARC8 started with pid=24, OS id=5659
Sat Dec 10 17:15:52 2011
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC9: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC9 started with pid=25, OS id=5661
Sat Dec 10 17:15:52 2011
Thread 1 advanced to log sequence 25
Sat Dec 10 17:15:53 2011
ARC2: STARTING ARCH PROCESSES
Sat Dec 10 17:15:53 2011
ARC6: Becoming the 'no FAL' ARCH
ARC6: Becoming the 'no SRL' ARCH
Sat Dec 10 17:15:53 2011
ARC3: Becoming the heartbeat ARCH
Sat Dec 10 17:15:53 2011
Thread 1 opened at log sequence 25
  Current log# 1 seq# 25 mem# 0: /u02/ezhou/redo01.log
  Current log# 1 seq# 25 mem# 1: /u02/ezhou/redo01a.rdo
Successful open of redo thread 1
Sat Dec 10 17:15:53 2011
SMON: enabling cache recovery
Sat Dec 10 17:15:54 2011
ARCa: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
ARCa started with pid=26, OS id=5663
Sat Dec 10 17:15:57 2011
Successfully onlined Undo Tablespace 1.
Sat Dec 10 17:15:57 2011
SMON: enabling tx recovery
Sat Dec 10 17:15:57 2011
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=27, OS id=5666
Sat Dec 10 17:16:13 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:16:17 2011
Completed: ALTER DATABASE OPEN
Sat Dec 10 17:16:27 2011
Doing block recovery for file 2 block 4124
Block recovery from logseq 25, block 68 to scn 6168829
Sat Dec 10 17:16:27 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery stopped at EOT rba 25.126.16
Block recovery completed at rba 25.126.16, scn 0.6168829
Doing block recovery for file 2 block 73
Block recovery from logseq 25, block 68 to scn 6168786
Sat Dec 10 17:16:28 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery completed at rba 25.69.16, scn 0.6168789
Sat Dec 10 17:16:28 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc:
ORA-01595: error freeing extent (2) of rollback segment (5))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:16:30 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:16:37 2011
Doing block recovery for file 2 block 4124
Block recovery from logseq 25, block 68 to scn 6168829
Sat Dec 10 17:16:37 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery completed at rba 25.126.16, scn 0.6168830
Doing block recovery for file 2 block 73
Block recovery from logseq 25, block 68 to scn 6168841
Sat Dec 10 17:16:37 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery completed at rba 25.149.16, scn 0.6168843
Sat Dec 10 17:16:37 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc:
ORA-12012: error on auto execute of job 8886
ORA-00607: Internal error occurred while making a change to a data block
Sat Dec 10 17:16:41 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:16:42 2011
DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8
Doing block recovery for file 2 block 4124
Block recovery from logseq 25, block 68 to scn 6168829
Sat Dec 10 17:16:42 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery completed at rba 25.126.16, scn 0.6168830
Sat Dec 10 17:16:43 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:16:46 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:17:46 2011
DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8
Doing block recovery for file 2 block 4124
Block recovery from logseq 25, block 68 to scn 6168829
Sat Dec 10 17:17:46 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0 errs 0: /u02/ezhou/redo01.log
  Mem# 1 errs 0: /u02/ezhou/redo01a.rdo
Block recovery completed at rba 25.126.16, scn 0.6168830
Sat Dec 10 17:17:48 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
Sat Dec 10 17:17:49 2011
Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc:
ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], []
PMON: terminating instance due to error 472
Instance terminated by PMON, pid = 5535

二、MOS记录

ERROR:
  ORA-600 [4194] [a] [b]
VERSIONS:
  versions 6.0 to 10.1
DESCRIPTION:
  A mismatch has been detected between Redo records and rollback (Undo)
  records.
  We are validating the Undo record number relating to the change being
  applied against the maximum undo record number recorded in the undo block.
  This error is reported when the validation fails.
ARGUMENTS:
  Arg [a] Maximum Undo record number in Undo block
  Arg [b] Undo record number from Redo block

三、解决办法
1、修改参数
undo_management= MANUAL
undo_tablespace= SYSTEM
2、打开数据库，删除当前undo空间，重建新undo空间
3、修改参数
undo_management= AUTO
undo_tablespace= UNDOTBSNEW
4、重新启动数据库

in/exists和not in/not exists语意探讨

本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境

C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t1 (c1 number,c2 number);
表已创建。
SQL> create table t2 (c1 number,c2 number);
表已创建。
SQL> insert into t1 values (1,2);
已创建 1 行。
SQL> insert into t1 values (1,3);
已创建 1 行。
SQL> insert into t2 values (1,2);
已创建 1 行。
SQL> insert into t2 values (1,null);
已创建 1 行。
SQL> insert into t1 values (1,null);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from t1;
        C1         C2
---------- ----------
         1          2
         1          3
         1
SQL> select * from t2;
        C1         C2
---------- ----------
         1          2
         1

Note:t1和t2表都有null，且t1比t2多一条记录

2、t2做内部表

SQL> select * from t1 where c2 in (select c2 from t2 );
        C1         C2
---------- ----------
         1          2
SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);
        C1         C2
---------- ----------
         1          2
SQL> select * from t1 where c2 not in (select c2 from t2 );
未选定行
SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
        C1         C2
---------- ----------
         1          3
         1

3、t1为内部表

SQL> select * from t2 where c2 in (select c2 from t1 );
        C1         C2
---------- ----------
         1          2
SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);
        C1         C2
---------- ----------
         1          2
SQL> select * from t2 where c2 not in (select c2 from t1 );
未选定行
SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);
        C1         C2
---------- ----------
         1

3、结论
in和exists结果相同(都会排除掉null,无论内部表中有无null)
not in会过滤掉外部表中的null(即使内部表中无null)
not exists不会过滤掉外部表的null(即使内部表有null)
由于篇幅关系,括号中的部分实验过程未展现出来

回收dba中alter system处理方法

今天在pub上看到一个问题，一个朋友想回收dba的alter system权限，直接回收这个系统权限从dba的做法是不推荐使用，因为修改了系统默认的dba角色所具有的系统权限，可能会导致未知的后果。好的做法是创建新的角色，使其有dba中除alter system之外的所有权限。
1、数据库版本

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

2、创建角色并授权

SQL> create role xifenfei;
角色已创建。
SQL> declare
  2  cursor cur is select privilege from role_sys_privs where role='DBA'
  3  AND PRIVILEGE NOT IN ('ALTER SYSTEM','ADMINISTER RESOURCE MANAGER');
  4  BEGIN
  5    FOR C in cur loop
  6    EXECUTE IMMEDIATE  'grant '||c.privilege||' to xifenfei';
  7    END loop;
  8  END;
  9  /
PL/SQL 过程已成功完成。
SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  2  WHERE ROLE ='DBA' AND PRIVILEGE NOT IN(
  3  SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  4  WHERE ROLE ='XIFENFEI'
  5  );
PRIVILEGE
----------------------------------------
ALTER SYSTEM
ADMINISTER RESOURCE MANAGER

说明：授予创建角色出ALTER SYSTEM和ADMINISTER RESOURCE MANAGER系统权限之外的所有权限

3、创建用户并授权角色

SQL> create user chf_xff identified by xifenfei;
用户已创建。
SQL> grant xifenfei to chf_xff;
授权成功。

4、单独授予ADMINISTER RESOURCE MANAGER权限

SQL> exec dbms_resource_manager_privs.grant_system_privilege(
   2 grantee_name => 'CHF_XFF',admin_option => false);
PL/SQL 过程已成功完成。
SQL> CONN chf_xff/xifenfei
已连接。
SQL> SELECT * FROM SESSION_PRIVS WHERE
  2  PRIVILEGE ='ADMINISTER RESOURCE MANAGER';
PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER

说明:
1)通过授权xifenfei角色和ADMINISTER RESOURCE MANAGER权限，完成回收dba中的alter system权限要求。
2)如果只有个别用户有这样的需求，那么可以直接生成批量授权语句实现，而不用建立类此xifenfei这样的角色。

5、为何单独授予ADMINISTER RESOURCE MANAGER权限

SQL> CONN / AS SYSDBA
已连接。
SQL> GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF;
GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效

说明：ADMINISTER RESOURCE MANAGER这个系统权限在10g及其以后版本中，就不能直接使用GRANT/REVOKE直接授权/回收权限,而必面使用dbms_resource_manager_privs.grant_system_privilege和revoke_system_privilege过程进行处理。

作者：admin