kissweety_个人页

oracle rman备份与ORA-19504&ORA-27040&RMAN-030009

今天，一哥们要对数据库做重要操作，操作之前要做rman全备份，但是因为粗心，写错了备份路径，导致rman备份报错如下图： 由报错提示rman备份程序要将备份文件写入：/Oracle/app/oracle/product/11.2.0/dbhome_1/dbs/Oracle/backup/rman/full_0tsmout.bak"， 但是因为/Oracle/app/oracle/product/11.2.0/dbhome_1/dbs/Oracle/backup/rman不存在而报错：ORA-19504&ORA-27040&RMAN-03009 从他的备份脚本看： run{ backup increnmental level 0 database format 'Oracle/backup/rman/full_%u.bak'; sql 'alter system archive log current'; backup archivelog all format '/Oracle/backup/rman/arch_%U.log'; delete noprompt archivelog until time '{sysdate -1}'; } 发现脚本路径写的路径少了开头的/，从RMAN>show all能发现： 数据库rman备份程序将备份放到$ORACLE_HOME/Oracle/backup/rman/下了，该路径不存在而报错。 处理方法很简单，将备份脚本改成： run{ backup increnmental level 0 database format '/Oracle/backup/rman/full_%u.bak'; sql 'alter system archive log current'; backup archivelog all format '/Oracle/backup/rman/arch_%U.log'; delete noprompt archivelog until time '{sysdate -1}'; } 然后备份可以顺利进行

oracle 11.2.0.1告警日志报错ORA-03137与绑定变量窥探BUG9703463

2017年12月份第二次oracle数据库巡检中，发现某一地市oracle数据库发现SQL语句触发特定版本BUG，详细信息如下： 操作系统版本：windows server 2008R2数据库版本：oracle 11.2.0.1问题描述：2017年12月份第二次巡检中，发现告警日志报错，报错信息如下：19/12/2017 08:27:35 Tue Dec 19 08:27:35 2017 ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] Errors in file d:appadministratordiagrdbmsorclorcltraceorcl_ora_5480.trc (incident=36699): Incident details in: d:appadministratordiagrdbmsorclorclincidentincdir_36699orcl_ora_5480_i36699.trc 18/12/2017 17:19:56 Mon Dec 18 17:19:56 2017 ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] Incident details in: d:appadministratordiagrdbmsorclorclincidentincdir_36554orcl_ora_4572_i36554.trc Errors in file d:appadministratordiagrdbmsorclorcltraceorcl_ora_4572.trc (incident=36554): 18/12/2017 16:18:58 ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] Errors in file d:appadministratordiagrdbmsorclorcltraceorcl_ora_3968.trc (incident=36547): Mon Dec 18 16:18:58 2017 Incident details in: d:appadministratordiagrdbmsorclorclincidentincdir_36547orcl_ora_3968_i36547.trc根据orcl_ora_5480_i36699.trc文件发现触发ORA-03137的应用SQL语句确实使用绑定变量：-----sql_id=cjx2sya2mu4zm select * from (select row_.*, rownum NumRow from (select * from (select sid, code, to_char(month, 'yyyyMM') as month, hisid, bill_no, state, billdate, hospital_id, patient_id, patient_name, admission_number, admission_disease_name, disease_name, claim_name, benefit_name, bmino, benefit_group_name, item_date, dept_id, dept_name, item_id, item_name, item_type, physician_name, bmi_convered_amount, bmi_nopay, reject_reson, remrk, version_no, hospital_backs, versionstate, rule_name, back_reson, reback_reason, processState, is_approval, nvl(version, 1) as version, nvl(trickProgress, 0) as trickProgress, nvl(is_retrick, 0) as is_retrick, PERIOD, billex.NUMBER01 as Number01, billex.NUMBER02 as Number02, billex.NUMBER03 as Number03, billex.NUMBER05 as Number05, billex.NUMBER06 as Number06, billex.NUMBER07 as Number07, HOSPITAL_REMARK_DETAIL, decode(bitand((select sum(distinct(nvl(g.rule_bit, 0))) from gz_list g where g.business_type = '0'), rule_bit), 0, 0, 1) as BUSINESS_TYPE, REFEEDBACK_REASON_DETAIL, (select sum(a.reject_money) from dw_opinion_details b join dw_billdetail a on a.id = b.detailid where b.code = dw_opinions.code and b.version_no = dw_opinions.version_no and b.month = dw_opinions.month) as sumrejectmoney from dw_opinions left join dw_bill_ex billex on dw_opinions.hisid = billex.billid where 1 = 1 and month = to_date(:ParamMonth0, 'yyyyMM') and hospital_id = :ParamHospitalId1 and version_no = :versionno2 and bill_no = :ParamBillNo3 order by month desc, sid)) row_ where rownum <= 10) where NumRow > 0 ； 针对ORA03137与oracle 11.2.0.1 for windows server 2008R2查看oracle metalink，ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] 与应用SQL语句使用绑定变量有关， 是非公共Bug:9703463（文档 ID 1615363.1）： 解决办法：1、解决Oracle 11.2.0.1 因绑定变量触发ora-03137错误的补丁已包含在PSU补丁集Patch:10245351中，需要对数据库应用补丁集Patch:10245351 风险：oracle数据库应用补丁集可能引入新的未知BUG 2、关闭oracle 11.2.0.1绑定变量功能：alter system set "_optim_peek_user_binds"=false; 风险：将导致数据库不稳定，引起应用sql语句执行计划不准确 3、将数据库版本升级到11.2.0.3以上版本可解决ORA-03137问题

oracle 11.2.0.1 for windows server2012R2应用p12429529_112010_MSWIN-x86-64补丁

>> >> > >> > >> >>>>> > > > > > > > >> >> >>

oracle 服务器断电重启导致数据库性能急剧下降之等待事件wait for a undo record

<> >       

jquery的ajax请求servlet与响应

JAVA 类声明中关键字public的作用

      JAVA类声明中关键字public起到什么作用呢？如下Hello类的声明，按着字面的意思理解是：Hello类是公共的， 要求Hello类与类文件名必须保持一致，并且一个java文件中只能有一个public关键字声明的类。 public class Hello{public static void main(String args[]){System.out.println("Hello JAVA!");} } 如果关键字public命名的类Hello与类文件名不一致，如下图： 程序编译时就会报错： 使用public关键字正确命名与类名保持一致： 使用public关键字正确命名与类名保持一致，Hello.java文件可以正常编译，可以正确执行： 如果，java文件中类命名不使用public关键字，那么类声明可以不与java文件保持一致，如下代码所示： HelloDemo.java可以正常编译，但是，会产生一个HelloModle.class文件： 那么，编译HelloDemo.java后，要执行的文件也变为HelloModle.class了： 如果HelloDemo.java中有多个非public关键字声明的类，就会产生多个与之相应的.class文件。 总结：使用public关键字声明的类必须与java文件名保持一致，并且一个java文件中只能有一个public关键字修饰的类名称， 编译后会产生一个与java文件名相应的.class文件，而一个java文件中可以同时又多个不使用public关键字声明的类，但是编 译后会产生多个与之对应的.class文件，并且要执行的文件是对应的多个.class的前缀名。

oracle 存储过程执行报错ORA-12828

ORACLE 安全访问策略VPD与ORA-28132

                                                                                                                                                                                                                     >                                                                                         >            <                                                        >                           >                           >                       >                       >     >                            >                            >     >                            >                            >

sql server 2008r2 for windows 2008r2异机远程备份

SQL Server 2008r2 for windows 2008r2安装详尽图解

oracle 11.2.0.4 ogg 12.3.0.1.0备库rep进程启动报错OGG-00446

            > >                                                   >                         >                                                                                                                                                               >>> >>                                                  >  >>>>>                                                  >  >                             >  >>>>>                                                  > 

putty 远程连接工具保留操作日志

   >

oracle 11.2.0.1 for windows server2008r2告警ORA-03137

2017.10.20贵州一地市oracle 11.2.0.1医保相关应用程序功能异常无法运行，查看数据库告警日志有如下报错： Fri Oct 20 16:03:04 2017 Trace dumping is performing id=[cdmp_20171020160304] Fri Oct 20 16:03:05 2017 Sweep [inc][140434]: completed Sweep [inc2][140434]: completed Fri Oct 20 16:12:40 2017 Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4756.trc (incident=140155): ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_140155\orcl_ora_4756_i140155.trc Fri Oct 20 16:12:41 2017 Trace dumping is performing id=[cdmp_20171020161241] 查看告警日志提示的trc文件： Dump file e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_140155\orcl_ora_4756_i140155.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Windows NT Version V6.1 Service Pack 1  CPU : 24 - type 8664, 12 Physical Cores Process Affinity : 0x0x0000000000000000 Memory (Avail/Total): Ph:45309M/65508M, Ph+PgF:110993M/131015M  Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 32 Windows thread id: 4756, image: ORACLE.EXE (SHAD) *** 2017-10-20 16:12:40.170 *** SESSION ID:(515.23643) 2017-10-20 16:12:40.170 *** CLIENT ID:() 2017-10-20 16:12:40.170 *** SERVICE NAME:(orcl) 2017-10-20 16:12:40.170 *** MODULE NAME:() 2017-10-20 16:12:40.170 *** ACTION NAME:() 2017-10-20 16:12:40.170 Dump continued from file: e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4756.trc ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] [] ========= Dump for incident 140155 (ORA 3137 [12333]) ======== *** 2017-10-20 16:12:40.170 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=905vd7vnpuh6n) ----- select * from (select row_.*, rownum NumRow from (select * from (select sid, code, to_char(month,'yyyyMM') as month , hisid, bill_no, state, billdate, hospital_id, patient_id, patient_name, admission_number, admission_disease_name, disease_name, claim_name, benefit_name, bmino, benefit_group_name, item_date, dept_id, dept_name, item_id, item_name, item_type, physician_name, bmi_convered_amount, bmi_nopay, reject_reson, remrk, version_no, hospital_backs, versionstate, rule_name, back_reson, reback_reason, processState, is_approval, nvl(version,1) as version, nvl(trickProgress,0) as trickProgress, nvl(is_retrick,0) as is_retrick, PERIOD, billex.NUMBER01 as Number01, billex.NUMBER02 as Number02, billex.NUMBER03 as Number03, billex.NUMBER05 as Number05, billex.NUMBER06 as Number06, billex.NUMBER07 as Number07, HOSPITAL_REMARK_DETAIL, decode(bitand((select sum(distinct(nvl(g.rule_bit, 0))) from gz_list g where g.business_type = '0'),rule_bit),0,0,1) as BUSINESS_TYPE, REFEEDBACK_REASON_DETAIL, (select sum(a.reject_money) from dw_opinion_details b join dw_billdetail a on a.id =b.detailid where b.code=dw_opinions.code and b.version_no=dw_opinions.version_no and b.month=dw_opinions.month ) as sumrejectmoney from dw_opinions left join dw_bill_ex billex on dw_opinions.hisid = billex.billid where 1=1 and month =to_date(:ParamMonth0,'yyyyMM') and hospital_id = :ParamHospitalId1 and version_no = :versionno2 order by month desc,sid)) row_ where rownum <= 10) where NumRow > 0 查询oracle metalink了解到这是oracle 11.2.0.1自身的一个Bug 9445675（文档 ID 1361107.1），与oracle数据库的绑定变量窥探有关   根据trc文件提示到的sql可知，sql语句确实使用了绑定变量，解决改问题的方法有3种： 1、取消数据库的绑定变量窥探：alter system set "_optim_peek_user_binds"=false; 注意取消绑定变量窥探对数据库有影响，会改变sql的执行计划 2、对数据库打补丁 PSU 11.1.0.7.8 Patch 12419384 includes Patch:9703463 For 11.1.0.7, Patch 9243912 Patch:9703463  can also be applied individually but requires PSU 11.1.0.7.6 Patch:8625762  may also be applicable to databases version 11.1.0.7 Patch:18841764 fixes the SQL Loader issue that may affect database version 12.1.0.2 3、升级数据库到11.2.0.3及以上版本 由于项目功能使用紧急，临时取消数据库绑定变量，应用功能能够正常使用，事后再做oracle 11.2.0.1 to 11.2.0.4的升级。

oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

                                                                                                                   >>>>> > >> >  >>>>          >                           >>>>>>>> >                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              >>>>>>>      >     >                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         >>>>>>>      >  >       > >  <        

oracle for linux安装报错 file /home/oracle/.Xauthority does not exist

      今天，一新手学oracle，安装完oracle数据库软件后，执行dbca时报错，核心错误是： file /home/oracle/.Xauthority does not exist 然后，使用oracle用户登录数据库后，执行ls -a发现报错，bash:command not found 估计是环境变量配置错了，然后使用root用户登录，root执行ls命令正常，然后cd /home/oracle查看.bash_profile 发现PATH环境变量设置是：export PATH=$ORACLE_HOME/bin:$path 而.bash_profile没有path的设置，这就是为什么执行数据库相关命令sqlplus正常，但是执行操作命令报错的原因了。 解决方法： 将export PATH=$ORACLE_HOME/bin:$path 改为：export PATH=$ORACLE_HOME/bin:$PATH 然后，oracle用户退出系统重新登录即可，注意这里oracle用户必须重新登录，因为oracle已经不能再执行操作系统相关的命令了。 oracle重新登录系统后，netca、dbca可以正常执行，问题解决。

oracle 11.2.0.4 rac for rhel 6.8 installation

                                                                                   >            >>         >>     >>                                             >><< >><<                                             >><<                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        >>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>                      > > > > > > >>                                                                                                                                                                                                                                                                                                                                                                                 > > >>> >> > >>          >>>>>>>>>>>>>>>>>>>>>>>      >>>>>>>>>>>>>>>>>>>                                                                                                                                                                                                                                                                                                                                       >   >   >   >   >   >   >                                                                                                                                                                >><<>>>>>>>>>>>>> >><<>>>>>>>>>>>>>>><<<<<                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            >> > >                                                                                                                                 >>  > >> > > > > > >> > >> >>  >>  > > > >                                                >                                                                                                                                                                               >>>>>>>>>>>>>>>>>>>>>>    

Linux fstab修改不当导致开机fsck失败而主机无法重启

heartbeat 3.0.4-drbd 8.9-Mysql 5.6--双机互备高可用部署

              > > > > > > > > > > > > >> >> > > > > > > > > > >< > > > > > > > > > > >>&> >>&> > > & > > > > > > > > > > > > > > >

linux-ha heartbeat高可用的安装配置（http服务高可用）

    heartbeat工作原理：本次实验，使用广播地址225.0.0.11 694作为heartbeat的网络心跳地址，heartbeat通过网络心跳监测对端服务是否正常， 通过/etc/ha.d/ha.cf 文件的deadtime参数判断对端服务中断，通过参数auto_failback参数控制资源失败自动接管，从而完成httpd服务的双机互备。 两台主机（一台是rhel6.4 arbiter，一台是centos6.8 mymongo1） 一、两台主机的基本信息，重点是hosts文件的IP地址主机名解析 [root@mymongo1 ~]# uname -a Linux mymongo1 2.6.32-642.el6.x86_64 #1 SMP Tue May 10 17:27:01 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux [root@mymongo1 ~]# hostname mymongo1 [root@mymongo1 ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost #::1 localhost6.localdomain6 localhost6 192.168.144.111 arbiter 192.168.144.132 mymongo1 [root@mymongo1 ~]# ifconfig -a eth0 Link encap:Ethernet HWaddr 00:0C:29:40:94:8E inet addr:192.168.199.131 Bcast:192.168.199.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fe40:948e/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:46 errors:0 dropped:0 overruns:0 frame:0 TX packets:30 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:4219 (4.1 KiB) TX bytes:3299 (3.2 KiB) eth1 Link encap:Ethernet HWaddr 00:0C:29:40:94:98 inet addr:192.168.144.132 Bcast:192.168.144.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fe40:9498/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:1890 errors:0 dropped:0 overruns:0 frame:0 TX packets:1800 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:253127 (247.1 KiB) TX bytes:252326 (246.4 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:65536 Metric:1 RX packets:8 errors:0 dropped:0 overruns:0 frame:0 TX packets:8 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:576 (576.0 b) TX bytes:576 (576.0 b) [root@mymongo1 ~]# [root@arbiter ~]# hostname arbiter [root@arbiter ~]# uname -a Linux arbiter 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux [root@arbiter ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost #::1 localhost6.localdomain6 localhost6 192.168.144.111 arbiter 192.168.144.132 mymongo1 [root@arbiter ~]# ifconfig -a eth0 Link encap:Ethernet HWaddr 00:50:56:3E:FE:CF inet addr:192.168.199.128 Bcast:192.168.199.255 Mask:255.255.255.0 inet6 addr: fe80::250:56ff:fe3e:fecf/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:85 errors:0 dropped:0 overruns:0 frame:0 TX packets:25 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:9437 (9.2 KiB) TX bytes:2719 (2.6 KiB) eth1 Link encap:Ethernet HWaddr 00:50:56:21:CF:3E inet addr:192.168.144.111 Bcast:192.168.144.255 Mask:255.255.255.0 inet6 addr: fe80::250:56ff:fe21:cf3e/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:2992 errors:0 dropped:0 overruns:0 frame:0 TX packets:3054 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:348131 (339.9 KiB) TX bytes:423727 (413.7 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:4 errors:0 dropped:0 overruns:0 frame:0 TX packets:4 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:240 (240.0 b) TX bytes:240 (240.0 b) [root@arbiter ~]# 二、heartbeat的安装，rhel和centos安装不一样，先安装centos机器的heartbeat 注意：最少保证2台主机中的centos主机能够上网，centos安装Heartbeats，通过yum方式网络安装 1、首先下载epel-release-6-8.noarch.rpm wget http://mirrors.sohu.com/fedora-epel/6/x86_64/epel-release-6-8.noarch.rpm [root@mymongo1 ~]# ls amoeba Documents install.log mysql-5.6.36.tar.gz Pictures Videos anaconda-ks.cfg Downloads install.log.syslog mysql-proxy-0.8.2 Public arbiter epel-release-6-8.noarch.rpm lua-5.1.4 mysql-proxy-0.8.2.tar.gz Templates cmake-2.8.10.2.tar.gz glib-2.22.5 lua-5.1.4.tar.gz mysql-proxy-0.8.5 test1.sh Desktop glib-2.22.5.tar Music mysql-proxy-0.8.5.tar.gz test.sh [root@mymongo1 ~]# --安装epel，其实是安装yum源 rpm -ivh epel-release-6-8.noarch.rpm [root@mymongo1 yum.repos.d]# pwd /etc/yum.repos.d [root@mymongo1 yum.repos.d]# ls CentOS-Base.repo CentOS-Debuginfo.repo CentOS-fasttrack.repo CentOS-Media.repo CentOS-Vault.repo epel.repo epel-testing.repo [root@mymongo1 yum.repos.d]# [root@mymongo1 yum.repos.d]# cat epel.repo [epel] name=Extra Packages for Enterprise Linux 6 - $basearch #baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch failovermethod=priority enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 [epel-debuginfo] name=Extra Packages for Enterprise Linux 6 - $basearch - Debug #baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch failovermethod=priority enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 gpgcheck=1 [epel-source] name=Extra Packages for Enterprise Linux 6 - $basearch - Source #baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch failovermethod=priority enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 gpgcheck=1 [root@mymongo1 yum.repos.d]# [root@mymongo1 yum.repos.d]# cat epel-testing.repo [epel-testing] name=Extra Packages for Enterprise Linux 6 - Testing - $basearch #baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-epel6&arch=$basearch failovermethod=priority enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 [epel-testing-debuginfo] name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Debug #baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch/debug mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-debug-epel6&arch=$basearch failovermethod=priority enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 gpgcheck=1 [epel-testing-source] name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Source #baseurl=http://download.fedoraproject.org/pub/epel/testing/6/SRPMS mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-source-epel6&arch=$basearch failovermethod=priority enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6 gpgcheck=1 [root@mymongo1 yum.repos.d]# --调整yum的配置文件，缓存本次heartbeat安装需要的依赖包 [root@mymongo1 etc]# cat yum.conf [main] cachedir=/var/cache/yum/$basearch/$releasever keepcache=1 debuglevel=2 logfile=/var/log/yum.log exactarch=1 obsoletes=1 gpgcheck=1 plugins=1 installonly_limit=5 bugtracker_url=http://bugs.centos.org/set_project.php?project_id=19&ref=http://bugs.centos.org/bug_report_page.php?category=yum distroverpkg=centos-release [root@mymongo1 etc]# --使用yum工具安装heartbeat yum -y install heartbeat 安装完成后heartbeat的缓存目录 [root@mymongo1 ~]# find / -name heartbeat*.rpm /var/cache/yum/x86_64/6/epel/packages/heartbeat-3.0.4-2.el6.x86_64.rpm /var/cache/yum/x86_64/6/epel/packages/heartbeat-libs-3.0.4-2.el6.x86_64.rpm [root@mymongo1 ~]# [root@mymongo1 ~]# cd /var/cache/yum/x86_64/6/base/packages/ [root@mymongo1 packages]# ll total 2060 -rw-r--r--. 1 root root 66344 Jul 24 2015 cifs-utils-4.8.1-20.el6.x86_64.rpm -rw-r--r--. 1 root root 72744 Jun 24 2012 cluster-glue-1.0.5-6.el6.x86_64.rpm -rw-r--r--. 1 root root 119096 Jun 24 2012 cluster-glue-libs-1.0.5-6.el6.x86_64.rpm -rw-r--r--. 1 root root 40152 Oct 17 2014 keyutils-1.4-5.el6.x86_64.rpm -rw-r--r--. 1 root root 23488 Sep 25 2011 libgssglue-0.1-11.el6.x86_64.rpm -rw-r--r--. 1 root root 343900 Mar 23 08:00 nfs-utils-1.2.3-75.el6.x86_64.rpm -rw-r--r--. 1 root root 72268 Mar 23 08:02 nfs-utils-lib-1.1.5-13.el6.x86_64.rpm -rw-r--r--. 1 root root 38264 Oct 17 2014 perl-TimeDate-1.16-13.el6.noarch.rpm -rw-r--r--. 1 root root 913840 Jul 2 2011 PyXML-0.8.4-19.el6.x86_64.rpm -rw-r--r--. 1 root root 398016 Mar 24 06:25 resource-agents-3.9.5-46.el6.x86_64.rpm [root@mymongo1 packages]# 2、在rhel机器上arbiter安装heartbeat，方法是将centos机器mymongo1上的heartbeat包及其依赖包scp到arbiter机器上， 使用rpm -ivh方式安装，安装过程（安装过程中需要其他依赖的rpm包，可以挂载centos cd1镜像到arbiter上找到安装即可）： [root@arbiter heartbeat]# ls cifs-utils-4.8.1-20.el6.x86_64.rpm keyutils-libs-1.4-5.el6.x86_64.rpm perl-TimeDate-1.16-13.el6.noarch.rpm cluster-glue-1.0.5-6.el6.x86_64.rpm libevent-1.4.13-4.el6.x86_64.rpm python-argparse-1.2.1-2.1.el6.noarch.rpm cluster-glue-libs-1.0.5-6.el6.x86_64.rpm libgssglue-0.1-11.el6.x86_64.rpm PyXML-0.8.4-19.el6.x86_64.rpm heartbeat-3.0.4-2.el6.x86_64.rpm libtirpc-0.2.1-11.el6.x86_64.rpm resource-agents-3.9.5-46.el6.x86_64.rpm heartbeat-libs-3.0.4-2.el6.x86_64.rpm nfs-utils-1.2.3-75.el6.x86_64.rpm rpcbind-0.2.0-12.el6.x86_64.rpm keyutils-1.4-5.el6.x86_64.rpm nfs-utils-lib-1.1.5-13.el6.x86_64.rpm [root@arbiter heartbeat]# rpm -ivh PyXML-0.8.4-19.el6.x86_64.rpm warning: PyXML-0.8.4-19.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:PyXML ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh perl-TimeDate-1.16-13.el6.noarch.rpm warning: perl-TimeDate-1.16-13.el6.noarch.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:perl-TimeDate ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh cifs-utils-4.8.1-20.el6.x86_64.rpm keyutils-1.4-5.el6.x86_64.rpm keyutils-libs-1.4-5.el6.x86_64.rpm warning: cifs-utils-4.8.1-20.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] file /lib64/libkeyutils.so.1.3 from install of keyutils-libs-1.4-5.el6.x86_64 conflicts with file from package keyutils-libs-1.4-4.el6.x86_64 [root@arbiter heartbeat]# rpm -qa|grep keyutils-libs keyutils-libs-1.4-4.el6.x86_64 [root@arbiter heartbeat]# rpm -e keyutils-libs error: Failed dependencies: libkeyutils.so.1()(64bit) is needed by (installed) krb5-libs-1.10.3-10.el6.x86_64 libkeyutils.so.1(KEYUTILS_0.3)(64bit) is needed by (installed) krb5-libs-1.10.3-10.el6.x86_64 [root@arbiter heartbeat]# rpm -e --nodeps keyutils-libs [root@arbiter heartbeat]# rpm -qa|grep keyutils-libs [root@arbiter heartbeat]# rpm -ivh cifs-utils-4.8.1-20.el6.x86_64.rpm keyutils-1.4-5.el6.x86_64.rpm keyutils-libs-1.4-5.el6.x86_64.rpm warning: cifs-utils-4.8.1-20.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:keyutils-libs ########################################### [ 33%] 2:keyutils ########################################### [ 67%] 3:cifs-utils ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh cluster-glue-* warning: cluster-glue-1.0.5-6.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:cluster-glue-libs ########################################### [ 50%] 2:cluster-glue ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh libgssglue-0.1-11.el6.x86_64.rpm warning: libgssglue-0.1-11.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:libgssglue ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh libgssglue-0.1-11.el6.x86_64.rpm warning: libgssglue-0.1-11.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] package libgssglue-0.1-11.el6.x86_64 is already installed [root@arbiter heartbeat]# rpm -ivh libevent-1.4.13-4.el6.x86_64.rpm warning: libevent-1.4.13-4.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:libevent ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh python-argparse-1.2.1-2.1.el6.noarch.rpm warning: python-argparse-1.2.1-2.1.el6.noarch.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:python-argparse ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh libtirpc-0.2.1-11.el6.x86_64.rpm warning: libtirpc-0.2.1-11.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:libtirpc ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh rpcbind-0.2.0-12.el6.x86_64.rpm warning: rpcbind-0.2.0-12.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:rpcbind ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh nfs-utils-* warning: nfs-utils-1.2.3-75.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:nfs-utils-lib ########################################### [ 50%] 2:nfs-utils ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh heartbeat-* warning: heartbeat-3.0.4-2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY error: Failed dependencies: resource-agents is needed by heartbeat-3.0.4-2.el6.x86_64 [root@arbiter heartbeat]# rpm -ivh resource-agents-3.9.5-46.el6.x86_64.rpm warning: resource-agents-3.9.5-46.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY Preparing... ########################################### [100%] 1:resource-agents ########################################### [100%] [root@arbiter heartbeat]# rpm -ivh heartbeat-* warning: heartbeat-3.0.4-2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY Preparing... ########################################### [100%] 1:heartbeat-libs ########################################### [ 50%] 2:heartbeat ########################################### [100%] [root@arbiter heartbeat]# 3、heartbeat安装完成后，配置以下三个配置文件 [root@mymongo1 ~]# ll /etc/ha.d/ total 32 -rw-------. 1 root root 37 Aug 16 23:53 authkeys -rw-r--r--. 1 root root 239 Aug 17 00:01 ha.cf -rwxr-xr-x. 1 root root 745 Dec 2 2013 harc -rw-r--r--. 1 root root 206 Aug 17 01:01 haresources drwxr-xr-x. 2 root root 4096 Aug 16 22:42 rc.d -rw-r--r--. 1 root root 692 Dec 2 2013 README.config drwxr-xr-x. 2 root root 4096 Aug 16 22:42 resource.d -rw-r--r--. 1 root root 2082 Mar 24 06:01 shellfuncs [root@mymongo1 ~]# heartbeat三个配置文件的所在目录 [root@mymongo1 ~]# rpm -q heartbeat -d /usr/share/doc/heartbeat-3.0.4/AUTHORS /usr/share/doc/heartbeat-3.0.4/COPYING /usr/share/doc/heartbeat-3.0.4/COPYING.LGPL /usr/share/doc/heartbeat-3.0.4/ChangeLog /usr/share/doc/heartbeat-3.0.4/README /usr/share/doc/heartbeat-3.0.4/apphbd.cf /usr/share/doc/heartbeat-3.0.4/authkeys /usr/share/doc/heartbeat-3.0.4/ha.cf /usr/share/doc/heartbeat-3.0.4/haresources /usr/share/man/man1/cl_status.1.gz /usr/share/man/man1/hb_addnode.1.gz /usr/share/man/man1/hb_delnode.1.gz /usr/share/man/man1/hb_standby.1.gz /usr/share/man/man1/hb_takeover.1.gz /usr/share/man/man5/authkeys.5.gz /usr/share/man/man5/ha.cf.5.gz /usr/share/man/man8/apphbd.8.gz /usr/share/man/man8/heartbeat.8.gz [root@mymongo1 ~]# heartbeat的ha.cf配置 [root@mymongo1 ~]# cat /etc/ha.d/ha.cf #heartbeat controlfile debugfile /var/log/ha-debug logfile /var/log/ha-log logfacility local1 keepalive 2 deadtime 30 warntime 10 initdead 60 #bcast eth1，使用广播方式做Heartbeat的心跳 mcast eth1 225.0.0.11 694 1 0 auto_failback on node arbiter node mymongo1 crm no [root@mymongo1 ~]# heartbeat的authkeys配置 sha1加密字符串随意，但是要求权限是600 [root@mymongo1 ~]# cat /etc/ha.d/authkeys auth 1 1 sha1 adf220fnasdfinvs02adsf [root@mymongo1 ~]# heartbeart的haresources配置 这里注意：配置的资源是浮动vip的地址，vip绑定的网络端口要保持一致，用eth1都用eth1，用eth0都用eth0 并且eht1和eth0要用不同的IP网段，网口后的服务httpd代表资源启动时会连带启动httpd，没有则不启动 [root@mymongo1 ~]# cat /etc/ha.d/haresources arbiter IPaddr::192.168.144.121/24/eth1 httpd mymongo1 IPaddr::192.168.144.122/24/eth1 #mysql master #arbiter IPaddr::192.168.144.11/24/eth1 mysqld #mysql salve #mymongo1 IPaddr::192.168.144.12/24/eth1 [root@mymongo1 ~]# 三、本次实验使用http做测试 2台主机分别编辑index.html [root@mymongo1 ~]# cat /var/www/html/index.html this is mymongo1,its ip is 192.168.144.132 [root@mymongo1 ~]# [root@arbiter ~]# cat /var/www/html/index.html this is arbiter ,its ip is 192.168.144.111! [root@arbiter ~]# 2台主机均启动http服务 [root@arbiter ~]# service httpd status httpd (pid 4574) is running... [root@arbiter ~]# [root@mymongo1 ~]# service httpd status httpd (pid 5714) is running... [root@mymongo1 ~]# 2台主机均启动heartbeat [root@mymongo1 ~]# /etc/init.d/heartbeat start Starting High-Availability services: INFO: Resource is stopped INFO: Resource is stopped Done. [root@mymongo1 ~]# 稍等1分钟左右观察浮动IP会启动 [root@mymongo1 ~]# ip add|grep 192.168.144 inet 192.168.144.132/24 brd 192.168.144.255 scope global eth1 inet 192.168.144.122/24 brd 192.168.144.255 scope global secondary eth1 [root@mymongo1 ~]# [root@arbiter ~]# /etc/init.d/heartbeat start Starting High-Availability services: INFO: Resource is stopped INFO: Resource is stopped Done. [root@arbiter ~]# ip add|grep 192.168.144 inet 192.168.144.111/24 brd 192.168.144.255 scope global eth1 inet 192.168.144.121/24 brd 192.168.144.255 scope global secondary eth1 [root@arbiter ~]# 网页查看

ORACLE SQL优化之ORA-03150&ORA-02055&ORA-02063

                                                                                                             >   

ORACLE rman备份报错之RMAN-03009&ORA-19587&ORA-27072

          >                                                                       

oracle 11.2.0.1 for windows installation

                                                                                                                                                                                                                                                    &                         

ORACLE exp/imp导入报错IMP-00009&IMP-00028&IMP-00015

    2017年8月14日，一实施同事使用imp导入exp导出的dmp文件时，遇到报错IMP-00009&IMP，具体报错如图所示： 出现报错的原因可能是dmp文件本身不完整，如果仍要导入数据，可以对imp命令加上commit=yes进行控制，可行的imp导入命令： imp zhul/zhulei file=/home/oracle/other/yy_basj_lyzlyy.dmp log=/home/oracle/other/yy_basj_lyzlyy.log full=y commit=yes 再次执行导入，遇到imp报错：IMP-00015，具体报错如图： 这个报错的原因很明确，就是第一次导入虽然没有导入数据，但是表结构已经创建，再次导入需要将表结构删除，删除后再次导入， 导入执行成功： 需要注意的是，imp加commit=yes虽然可以将有问题的dmp文件中的数据导入，但是可能会丢失数据，建议重新导出，然后再次导入。

ORACLE SQL调优之记录一次trim函数引发的大表全表扫描

ORACLE ORA-00600: 内部错误代码, 参数: [kokbcvb1]与merge into

                         >><>>                                                                        >                                                                                                                                                                                                  ><                             >                                                                                                      >                                                                                                                                                                                                                                                     

ORACLE startup报错之ORA-01261&&ORA-01263&&ORA-00202&&ORA-00205

    系统环境：windows server 2008R2    数据库环境：oracle 11.2.0.1    问题描述：一测试数据库oradb启动时报错ORA-01261&&ORA-00202，报错日志如下： C:\Users\localadmin>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期二 8月 8 10:12:29 2017 Copyright (c) 1982, 2010, Oracle.  All rights reserved. 已连接到空闲例程。 SQL> startup; ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01263: Name given for file destination directory is invalid OSD-04018: ?????????????????????????? O/S-Error: (OS 2) ?????????????????????? SQL>     startup报错ORA-01261&&ORA-01263的意思是：当前数据库开启了闪回区，但是当前闪回区参数db_recovery_file_dest设置的目录不存在了：ORA-01263: Name given for file destination directory is invalid    解决方法很简单，查看启动参数文件pfile（ORACLE_HOME/dbs/initoradb.ora），看db_recovery_file_dest的文件目录配置，查看存储并创建改目录 ： *.aq_tm_processes=0 *.audit_file_dest='C:\oracle\admin\oradb\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='C:\oracle\oradata\oradb\control01.ctl','C:\oracle\flash_recovery_area\oradb\control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='oradb' *.db_recovery_file_dest='C:\oracle\flash_recovery_area' *.db_recovery_file_dest_size=4294967296 *.diagnostic_dest='C:\oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.job_queue_processes=0 *.log_archive_format='ARC%S_%R.%T' *.memory_target=3294625792 *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.optimizer_use_sql_plan_baselines=TRUE *.processes=200 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' 需要注意的是，还需要在flash_recovery_area目录下创建oradb文件夹，然后从数据文件目录拷贝最新的控制文件到oradb下并重命名为control02.ctl 否则，启动数据库时会报错： SQL> startup; ORACLE 例程已经启动。 Total System Global Area 3290345472 bytes Fixed Size                  2180224 bytes Variable Size            1979714432 bytes Database Buffers         1291845632 bytes Redo Buffers               16605184 bytesORA-00205: ?????????, ??????, ??????? 告警日志内容： Tue Aug 08 10:14:41 2017 MMNL started with pid=16, OS id=11856  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = C:\oracle Tue Aug 08 10:14:41 2017 ALTER DATABASE   MOUNT ORA-00210: ???????????ORA-00202: ????: ''C:\ORACLE\FLASH_RECOVERY_AREA\ORADB\CONTROL02.CTL'' ORA-27041: ?????? OSD-04002: 无法打开文件 O/S-Error: (OS 3) 系统找不到指定的路径。 ORA-205 signalled during: ALTER DATABASE   MOUNT...    然后，再次启动数据库能够正常启动： SQL> startup; ORACLE 例程已经启动。 Total System Global Area 3290345472 bytes Fixed Size                  2180224 bytes Variable Size            1979714432 bytes Database Buffers         1291845632 bytes Redo Buffers               16605184 bytes 数据库装载完毕。 数据库已经打开。 SQL> 总结：可能是存储或者人为误删除导致数据库损失闪回区目录，数据库无法启动，需要按照参数文件配置修复存储目录及相应的控制文件即可。

ORACLE startup报错之ORA-01154&&ORA-01155&&ORA-01033&&ORA-03113

    今天，一实施同事求助，说一地市oracle数据库无法通过远程连接，连接报错如图： 操作系统：windows server2008 R2  数据库版本：oracle 11.2.0.1 初看报错貌似数据库正处在打开或关闭的过程中。查看告警日志，最近的一次数据库启动发生在上午10:50，部分告警日志如下： Wed Aug 02 10:51:48 2017 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 3 Wed Aug 02 10:52:01 2017 Autotune of undo retention is turned on.  IMODE=BR ILAT =86 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. 然后，实施同事说，他中午那会startup了一下数据库，但是数据库停在了：“数据库装载完毕。”好长一段时间，如图： 然后，启动就报了ORA-03113，如果所示： 然后，他又说，数据库启动失败后，他接着重启了数据库服务器主机，然后但是问题依旧。 接下来是我的处理过程： 登录数据库服务器， 查看主机状态，CPU、磁盘IO、内存等资源很空闲 查看数据库服务和监听服务均已启动 登录数据库sqlplus查看数据库实例当前状态是mounted Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 2 13:48:40 2017 Copyright (c) 1982, 2010, Oracle.  All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------ MOUNTED --尝试open数据库报ORA-01154，日志如下： SQL> alter database open; alter database open * 第 1 行出现错误: ORA-01154: 数据库忙。现在不允许打开, 关闭, 装载和卸装 接下来，重启了操作系统的Oracle实例服务，然后重新启动，启动依然停留在“数据库装载完毕”，日志如下： 从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options(情况复杂) 断开 C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 2 13:58:58 2017 Copyright (c) 1982, 2010, Oracle.  All rights reserved. 已连接到空闲例程。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 2.0577E+10 bytes Fixed Size                  2184672 bytes Variable Size            8589937184 bytes Database Buffers         1.1945E+10 bytes Redo Buffers               39743488 bytes 数据库装载完毕。 --观察数据库告警日志，12:59分数据库有ORA-00949，有实例进程超时等待 Wed Aug 02 12:59:29 2017 Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_arc1_3604.trc  (incident=164377): ORA-00494: 持有入队 [CF] 的时间过长 (超过 900 秒) (由 'inst 1, osid 3084') Incident details in: d:\oraclehome\oracle\diag\rdbms\bmi\bmi\incident\incdir_164377\bmi_arc1_3604_i164377.trc Killing enqueue blocker (pid=3084) on resource CF-00000000-00000000 by (pid=3604)  by killing session 638.1 Killing enqueue blocker (pid=3084) on resource CF-00000000-00000000 by (pid=3604)  by terminating the process ARC1 (ospid: 3604): terminating the instance due to error 2103 --13:20数据库被重启，日志如下（猜想是主机重启后的自动重启） Wed Aug 02 13:19:29 2017 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 3 Autotune of undo retention is turned on.  IMODE=BR ILAT =86 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. --观察到TNS连接报错：ORA-01155 Wed Aug 02 13:34:35 2017 Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_m000_4620.trc: ORA-01155: 正在打开, 关闭, 装载或卸装数据库 Wed Aug 02 13:48:39 2017 TNS-12535: TNS: 操作超时     ns secondary err code: 12606     nt main err code: 0     nt secondary err code: 0     nt OS err code: 0   Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=fe80::94b2:4372:a24c:4b6e%11)(PORT=49179)) WARNING: inbound connection timed out (ORA-3136) Wed Aug 02 13:49:38 2017 Errors in file d:\oraclehome\oracle\diag\rdbms\bmi\bmi\trace\bmi_m000_5596.trc: ORA-01155: 正在打开, 关闭, 装载或卸装数据库再看自己重启后的告警日志 Wed Aug 02 13:59:07 2017 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 3 Autotune of undo retention is turned on.  IMODE=BR ILAT =86 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. ...... Wed Aug 02 13:59:12 2017ALTER DATABASE   MOUNT Wed Aug 02 13:59:12 2017 MMNL started with pid=17, OS id=2012  Successful mount of redo thread 1, with mount id 271372528 Database mounted in Exclusive Mode Lost write protection disabledCompleted: ALTER DATABASE   MOUNTWed Aug 02 13:59:17 2017ALTER DATABASE OPENBeginning crash recovery of 1 threads  parallel recovery started with 15 processes Wed Aug 02 13:59:41 2017Started redo scan Wed Aug 02 14:01:55 2017 Completed redo scan  read 8951211 KB redo, 764700 data blocks need recovery Wed Aug 02 14:03:22 2017 Started redo application at  Thread 1: logseq 364892, block 508132 Recovery of Online Redo Log: Thread 1 Group 7 Seq 364892 Reading mem 0   Mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO07_01.LOG   Mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO07_02.LOG Recovery of Online Redo Log: Thread 1 Group 21 Seq 364893 Reading mem 0   Mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO21_01.LOG   Mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO21_02.LOG 从启动告警日志可以看到，数据库肯定被异常终止过，当前数据库增在从redo日志恢复，此时需要耐心等待；再次询问实施同事并让其关闭远程的所有oracle客户端程序。 然后等待20分钟，数据库完成了open操作 Wed Aug 02 14:19:19 2017 Completed crash recovery at  Thread 1: logseq 364905, block 820661, scn 14976863197959  764700 data blocks read, 474332 data blocks written, 8951211 redo k-bytes read Wed Aug 02 14:19:37 2017 LGWR: STARTING ARCH PROCESSES Wed Aug 02 14:19:37 2017 ARC0 started with pid=37, OS id=1072  ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Wed Aug 02 14:19:38 2017 ARC1 started with pid=38, OS id=5408  Wed Aug 02 14:19:38 2017 ARC2 started with pid=39, OS id=4412  ARC1: Archival started ARC2: Archival started ARC2: Becoming the 'no FAL' ARCH ARC2: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH Wed Aug 02 14:19:38 2017 ARC3 started with pid=40, OS id=5536  ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Thread 1 advanced to log sequence 364906 (thread open) Thread 1 opened at log sequence 364906   Current log# 6 seq# 364906 mem# 0: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO06_01.LOG   Current log# 6 seq# 364906 mem# 1: D:\ORACLEHOME\ORACLE\ORADATA\BMI\REDO06_02.LOG Successful open of redo thread 1 Wed Aug 02 14:19:45 2017 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Aug 02 14:19:45 2017 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Wed Aug 02 14:20:02 2017 Starting background process QMNCWed Aug 02 14:20:02 2017 QMNC started with pid=58, OS id=5700  Completed: ALTER DATABASE OPEN --在数据库open的过程中，本地登录sqlplus查看会话信息，数据库确实开启了并行进程恢复： SQL>  select sid,process,program from  v$session   2  where   3   type = 'USER'   4  and   5  SID not in (select DISTINCT SID from v$mystat);        SID PROCESS                  PROGRAM ---------- ------------------------ ----------------------------------------------------------------          3 3668                     ORACLE.EXE (P011)          4 828                      ORACLE.EXE (P022)         51 6024                     ORACLE.EXE (P012)         52 5824                     ORACLE.EXE (P023)        100 5880                     ORACLE.EXE (P013)        101 4624                     ORACLE.EXE (P024)        148 6048                     ORACLE.EXE (P014)        150 2452                     ORACLE.EXE (P025)        198 6028                     ORACLE.EXE (P000)        200 3060                     ORACLE.EXE (P026)        251 4844                     ORACLE.EXE (P027)        298 4556                     ORACLE.EXE (P001)        299 4356                     ORACLE.EXE (P028)        347 4828                     ORACLE.EXE (P002)        348 5956                     ORACLE.EXE (P029)        396 5716                     ORACLE.EXE (P003)        397 4288                     ORACLE.EXE (P030)        443 5992                     ORACLE.EXE (P004)        444 2828                     ORACLE.EXE (P015)        445 4140                     ORACLE.EXE (P031)        492 4704                     ORACLE.EXE (P005)        493 5840                     ORACLE.EXE (P016)        541 6004                     ORACLE.EXE (P006)        542 2216                     ORACLE.EXE (P017)        590 5048                     ORACLE.EXE (P018)        591 1808                     ORACLE.EXE (P007)        639 5632                     ORACLE.EXE (P019)        640 4936                     ORACLE.EXE (P008)        641 4436                     ORACLE.EXE (J000)        689 5576                     ORACLE.EXE (P020)        690 5884                     ORACLE.EXE (P009)        737 6064                     ORACLE.EXE (P021)        739 6012                     ORACLE.EXE (P010) 已选择33行。 数据库完成open后查看数据库状态，数据库恢复正常： SQL> startup ORACLE 例程已经启动。 Total System Global Area 2.0577E+10 bytes Fixed Size                  2184672 bytes Variable Size            8589937184 bytes Database Buffers         1.1945E+10 bytes Redo Buffers               39743488 bytes 数据库装载完毕。 数据库已经打开。 SQL> select instance_name,status from v$instance; INSTANCE_NAME    STATUS ---------------- ------------ bmi              OPEN SQL> SQL> select name,open_mode from v$database; NAME      OPEN_MODE --------- -------------------- BMI       READ WRITE SQL>  注意：本次故障中，需要实施同事明白Oracle数据库的启动和关闭过程；重启操作系统前，需要先关闭oracle监听、确定当前数据库中没有执行计划任务或存储过程、 关闭oracle数据库实例、关闭oracle数据库服务、重启操作系统，如果不是这个顺序，就可能导致数据文件损坏，数据库启动需要恢复而打开过程很慢，需要耐心等待。

MySQL 5.6 for REHL 6.4编译安装

                    >>>                                                                                                                                                    >> >>>                    >>&            > > > > > >> > > > > > > > > > >>       &&             &                >  >                   >          

ORACLE RAC 11.2.0.4 for RHEL6.8 集群CRS异常导致集群命令无法使用

                                    >   >                                                                     >   >                                                      >        >       >      >> 

ORACLE RMAN备份失败之ORA-19599

    近期的oracle数据库巡检中，一地市oracle数据库的rman备份出现失败如下： 查看数据库rman备份日志，日志提示归档中有坏块： 输入归档日志线程=1 序列=43530 RECID=35316 STAMP=949830743 通道 c4: 正在启动段 1 于 20-7月 -17 RMAN-03009: backup 命令 (c1 通道上, 在 07/20/2017 09:52:28 上) 失败ORA-19599: 块编号 8 已在 archived log E:\BACKUP\ARCH\ARCH_737083740_1_43087.ARC 中损坏 通道 c1 已禁用, 将在另一个通道上运行该通道上失败的作业     与地市负责人沟通得知，该oracle数据库服务器的存储盘E盘是经存储服务器映射而来，现在已损坏。     登录oracle数据库执行archive log list发现数据库的归档目录就在e:\backup\arch，与rman备份失败提示的路径一致且在主机上该目录已经无法打开。 因此，可以确定是：oracle数据库的归档路径因存储损坏而无法完成正在归档的文件，导致rman备份失败 处理方法很简单，使用rman登录删除以前的所有备份并删除所有的归档文件，然后使用rman立即执行全备即可 然后，再次发起的rman备份成功完成：

MongoDB的排除查询$ne缺陷

    最近学习MongoDB数据库基本开发知识，发现MongoDB的排除查$ne类似于Oracle的not in 或者not exists， 但是$ne只能对字符串键值对起作用而对数字类型不行，举例如下： 示例数据： > dvd=({"Type":"DVD","Title":"Matrix,The","Released":1999,"Cast":["Keanu Reeeves","Carrie-Anne Moss","Laurence Fishburne","Hugo Weaving","Gloria Foster","Joe Pantoliano"]}) { "Type" : "DVD", "Title" : "Matrix,The", "Released" : 1999, "Cast" : [ "Keanu Reeeves", "Carrie-Anne Moss", "Laurence Fishburne", "Hugo Weaving", "Gloria Foster", "Joe Pantoliano" ] } > db.media.insert(dvd) WriteResult({ "nInserted" : 1 }) > dvd=({"Type":"DVD","Title":"Blade Runner","Released":1982}) { "Type" : "DVD", "Title" : "Blade Runner", "Released" : 1982 } > db.media.insert(dvd) WriteResult({ "nInserted" : 1 }) > dvd=({"Type":"DVD","Title":"Toy Story 3","Released":2010}) { "Type" : "DVD", "Title" : "Toy Story 3", "Released" : 2010 } > db.media.insert(dvd) WriteResult({ "nInserted" : 1 }) > 正常的字符串排除查询，以示例数据中Title键进行测试： > db.media.find({"Type":"DVD","Title":{$ne:"Blade Runner"}}) { "_id" : ObjectId("595af5ce39297105e4322fbd"), "Type" : "DVD", "Title" : "Matrix,The", "Released" : 1999, "Cast" : [ "Keanu Reeeves", "Carrie-Anne Moss", "Laurence Fishburne", "Hugo Weaving", "Gloria Foster", "Joe Pantoliano" ] } { "_id" : ObjectId("595af63239297105e4322fbf"), "Type" : "DVD", "Title" : "Toy Story 3", "Released" : 2010 } > 对数字排除无效，以示例数据中Released键进行测试： > db.media.find({"Type":"DVD","Release":{$ne:"1999"}}) { "_id" : ObjectId("595af5ce39297105e4322fbd"), "Type" : "DVD", "Title" : "Matrix,The", "Released" : 1999, "Cast" : [ "Keanu Reeeves", "Carrie-Anne Moss", "Laurence Fishburne", "Hugo Weaving", "Gloria Foster", "Joe Pantoliano" ] } { "_id" : ObjectId("595af61939297105e4322fbe"), "Type" : "DVD", "Title" : "Blade Runner", "Released" : 1982 } { "_id" : ObjectId("595af63239297105e4322fbf"), "Type" : "DVD", "Title" : "Toy Story 3", "Released" : 2010 } > > db.media.find({"Type":"DVD","Release":{$ne:1999}}) { "_id" : ObjectId("595af5ce39297105e4322fbd"), "Type" : "DVD", "Title" : "Matrix,The", "Released" : 1999, "Cast" : [ "Keanu Reeeves", "Carrie-Anne Moss", "Laurence Fishburne", "Hugo Weaving", "Gloria Foster", "Joe Pantoliano" ] } { "_id" : ObjectId("595af61939297105e4322fbe"), "Type" : "DVD", "Title" : "Blade Runner", "Released" : 1982 } { "_id" : ObjectId("595af63239297105e4322fbf"), "Type" : "DVD", "Title" : "Toy Story 3", "Released" : 2010 } > 实验结果：mongodb的$ne操作只能对字符串键值对发挥作用。 本次实验的MongoDB数据库版本是：3.2.7 操作系统是：Red Hat Enterprise Linux Server release 6.4 (Santiago)

ORACLE 数据库表字段查询报错之ORA-00904：invalid identifier

oracle 用户权限之执行存储过程权限不足

    >>   >> > > > 

ORACLE .net应用程序通过ODP.NET连接oracle12c之ORA-03137

    数据库服务器操作系统：   [ora12c@ora29 trace]$ uname -a   Linux ora29 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux   [ora12c@ora29 trace]$    数据库版本： [ora12c@ora29 trace]$ sqlplus -v SQL*Plus: Release 12.2.0.1.0 Production [ora12c@ora29 trace]$     今天，测试同事反应，他们.net应用程序连接的12c数据库出问题了，客户端应用程序报错： 登录数据库服务器，查看告警日志也发现一大堆的类似报错： 2017-06-14T13:16:40.873489+08:00 Errors in file /u03/app/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12761.trc  (incident=26305): ORA-03137: 来自客户机的格式错误的 TTC 包被拒绝: [kpoal8Check-3] [49152] [0] [0x000000000] [34856] [] [] [] 2017-06-14T13:16:40.876466+08:00 Session (7,27633): Bad TTC Packet Detected: Inbound connection from client Session (7,27633): Bad TTC Packet Detected: DB Logon User: BMITEST, Remote Machine: IIS APPPOOL\SE28, Program: w3wp.exe, OS User: WJW Session (7,27633): Bad TTC Packet Detected: Client IP Address: 10.117.130.28 2017-06-14T13:22:23.986783+08:00 Errors in file /u03/app/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_14136.trc  (incident=26260): ORA-03137: 来自客户机的格式错误的 TTC 包被拒绝: [kpoal8Check-3] [49152] [0] [0x000000000] [34856] [] [] [] 2017-06-14T13:22:23.988291+08:00 Session (254,48328): Bad TTC Packet Detected: Inbound connection from client Session (254,48328): Bad TTC Packet Detected: DB Logon User: BMITEST, Remote Machine: IIS APPPOOL\SE28, Program: w3wp.exe, OS User: WJW Session (254,48328): Bad TTC Packet Detected: Client IP Address: 10.117.130.28 查看trc文件内容，没有有价值的东西： [ora12c@ora29 trace]$ cat /u03/app/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_14136.trc   Trace file /u03/app/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_14136.trc Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125 ORACLE_HOME:    /u03/app/oracle/product/12.2.0/db_1 System name: Linux Node name: ora29 Release: 2.6.39-400.17.1.el6uek.x86_64 Version: #1 SMP Fri Feb 22 18:16:18 PST 2013 Machine: x86_64 Instance name: ora12c Redo thread mounted by this instance: 1 Oracle process number: 82 Unix process pid: 14136, image: oracle@ora29 *** 2017-06-14T13:22:23.987987+08:00 *** SESSION ID:(254.48328) 2017-06-14T13:22:23.988026+08:00 *** CLIENT ID:() 2017-06-14T13:22:23.988035+08:00 *** SERVICE NAME:(ora12c) 2017-06-14T13:22:23.988044+08:00 *** MODULE NAME:(w3wp.exe) 2017-06-14T13:22:23.988053+08:00 *** ACTION NAME:() 2017-06-14T13:22:23.988061+08:00 *** CLIENT DRIVER:(ODPM.NET) 2017-06-14T13:22:23.988069+08:00 DDE: Problem Key 'ORA 3137 [kpoal8Check-3]' was flood controlled (0x6) (incident: 26260) ORA-03137: 来自客户机的格式错误的 TTC 包被拒绝: [kpoal8Check-3] [49152] [0] [0x000000000] [34856] [] [] [] [ora12c@ora29 trace]$     查询oracle metalink网站，发现一个类似的报错文章：       

ORACLE 低版本客户端登录Oracle 12c监听报错之ORA-28040

        > > > >                                                                                   > >    > >>   

ORACLE SQL开发where子句之case-when

    湖南长沙一地市医保数据抽取要求从结果集中排除掉城市职工（61）就医方式住院（61），城市居民31就医方式住院（21）的数据。其实，这一业务逻辑实现，可以 那scott方案下的数据表emp做实验。    我要取的全部结果集：    那我要排除掉部门30中的WARD和部门20中的ADAMS，该怎么实现呢？其实很简单，可以使用结果集做差，如下所示：     其实，使用where子句的case-when有更优雅的实现方式：    对比结果集的minus和where子句的case-when，前者的实现逻辑好理解就是从总的结果集中排除不要的结果， 后者实现是取符合要求的数据，就性能而言个人觉得第二种更好、代码也更优雅。

ORACLE SQL调优之执行计划与隐藏参数_complex_view_merging

    最近，赤峰windows 版的11.2.0.3的oracle数据库出现一条sql语句执行非常慢，需要1天的时间还出不来，但是观察服务器的IO和CPU都是很空闲，并且将 该sql语句涉及的对象全部导出，然后分别导入其他机器oracle数据库与出现性能问题的数据库，发现其他机器数据库执行非常快而问题数据库依旧很慢。尝试 收集方案及表的统计信息后，再次执行sql语句还是没有改善，在问题处理的过程中，注意到一个很明显的问题，就是不管收集统计信息还是给sql加并行、使 用强制sql走hash，其执行计划都是原来的嵌套循环执行计划并且出现view字样。最终定位到数据库启动初始化参数_complex_view_merging。     问题sql： SELECT SUBSTR(F.PJ_CANTONCODE_CH, 0, 6) BMI_CODE,          A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC KEY_1,          A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC HISID,          B.HCM_SETTLEMENTDATE_DT BILLDATE,          C.HR_INSTITUTIONCODE_CH HOSPITAL_ID,          D.ME_INSTITUTINNAME_VC HOSPITAL_NAME,          C.HR_INSTITUTIONCODE_CH HOSPITAL_FEE_ID,          D.ME_INSTITUTINNAME_VC HOSPITAL_FEE_NAME,          C.HR_PERSONALCODE_VC PATIENT_ID,          C.HR_NAME PATIENT_NAME,          C.HR_COMPENSATIONTYPE_VC CLAIM_TYPE,          DECODE(NVL(C.HR_SEX, '-1'), '男', '1', '女', '0', '-1') PATIENT_SEX,          TO_DATE(NVL(E.PR_BRITHDAY_VC,                       '1900-01-01'),                   'yyyy-MM-dd') PATIENT_BIRTH,          M.MD_ICDCODE_VC ADMISSION_DISEASE_ID,          DECODE(C.HR_OUTHOSSTATUS_VC,                  '1',                  '治愈',                  '2',                  '好转',                  '3',                  '未愈',                  '4',                  '死亡',                  '9',                  '其他',                  '其他') DISCHARGE_REASON,          C.HR_INHOSDATE_DT ADMISSION_DATE,          C.HR_OUTHOSDATE_DT DISCHARGE_DATE,          C.HR_INHOSDATE_DT FIRST_DATE,          A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') || TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC BILL_NO,          SUM(NVL(A.HP_PRESCRIPTIONFEE_DEC, 0)) TOTAL_AMOUNT,          SUM(NVL(A.HP_ALLOWEDCOMP_DEC, 0)) BMI_CONVERED_AMOUNT,          A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||           TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') || TO_CHAR(C.HR_OUTHOSDATE_DT,'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC TRADENO     FROM COMP_HOSPRESCRIPTION A     LEFT JOIN COMP_HOSCOSTMAIN B       ON A.HP_HOSREGISTERCODE_VC = B.HCM_HOSREGISTERCODE_VC     LEFT JOIN COMP_HOSREGISTER C       ON A.HP_HOSREGISTERCODE_VC = C.HR_HOSREGISTERCODE_VC     LEFT JOIN CFG_MEDICALESTABLISHMENT D       ON C.HR_INSTITUTIONCODE_CH = D.ME_INSTITUTIONCODE_VC     LEFT JOIN JOIN_PERSONALRECORD E       ON C.HR_PERSONALCODE_VC = E.PR_PERSONALCODE_VC     LEFT JOIN JOIN_PERSONAJOIN F       ON F.PJ_PERSONALCODE_VC = E.PR_PERSONALCODE_VC     LEFT JOIN CFG_MAINTAINDISEASE M       ON M.MD_DISEASEID_VC = C.HR_DISEASECODE_VC    WHERE TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') > '2017-03-31'      AND TO_CHAR(B.HCM_SETTLEMENTDATE_DT, 'yyyy-MM-dd') < '2017-05-31'      AND A.DELETEFLAG_CH = 'N'      AND B.DELETEFLAG_CH = 'N'      AND C.DELETEFLAG_CH = 'N'      AND D.DELETEFLAG_CH = 'N'      AND M.DELETEFLAG_CH = 'N'    GROUP BY A.HP_HOSREGISTERCODE_VC || REPLACE(M.MD_ICDCODE_VC, '.', '') ||             TO_CHAR(C.HR_INHOSDATE_DT, 'yyyyMMdd') ||             TO_CHAR(C.HR_OUTHOSDATE_DT, 'yyyyMMdd') || C.HR_COMPENSATIONTYPE_VC,             B.HCM_SETTLEMENTDATE_DT,             F.PJ_CANTONCODE_CH,             C.HR_INSTITUTIONCODE_CH,             D.ME_INSTITUTINNAME_VC,             C.HR_INSTITUTIONCODE_CH,             D.ME_INSTITUTINNAME_VC,             C.HR_PERSONALCODE_VC,             C.HR_COMPENSATIONTYPE_VC,             C.HR_NAME,             C.HR_SEX,             E.PR_BRITHDAY_VC,             M.MD_ICDCODE_VC，C.HR_OUTHOSSTATUS_VC,             C.HR_INHOSDATE_DT,             C.HR_OUTHOSDATE_DT,             C.HR_INHOSDATE_DT; 其执行计划： Plan hash value: 1942484934 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                |                          |       |       |       |    18E(100)|          | |   1 |  HASH GROUP BY                  |                          |    25G|  6024G|    15E|    18E  (0)|999:59:59 | |   2 |   VIEW                          |                          |    18E|    15E|       |    18E  (0)|999:59:59 | |   3 |    NESTED LOOPS                 |                          |    18E|    15E|       |    18E  (0)|999:59:59 | |   4 |     VIEW                        |                          |   441P|    15E|       |    18E  (0)|999:59:59 | |   5 |      NESTED LOOPS OUTER         |                          |   441P|    15E|       |    18E  (0)|999:59:59 | |   6 |       VIEW                      |                          |   441P|    15E|       |   112P  (1)|999:59:59 | |   7 |        NESTED LOOPS OUTER       |                          |   441P|    15E|       |   112P  (1)|999:59:59 | |   8 |         VIEW                    |                          |    12T|  2601T|       |  8133G  (1)|999:59:59 | |   9 |          NESTED LOOPS           |                          |    12T|  2613T|       |  8133G  (1)|999:59:59 | |  10 |           VIEW                  |                          |   440G|    77T|       |   624G  (1)|999:59:59 | |  11 |            NESTED LOOPS         |                          |   440G|    77T|       |   624G  (1)|999:59:59 | |  12 |             VIEW                |                          |   889M|    43G|       |   909M  (1)|999:59:59 | |  13 |              NESTED LOOPS       |                          |   889M|    43G|       |   909M  (1)|999:59:59 | |* 14 |               TABLE ACCESS FULL | COMP_HOSPRESCRIPTION     |  1258K|    49M|       | 11529   (1)| 00:02:19 | |* 15 |               VIEW              |                          |   707 |  8484 |       |   723   (1)| 00:00:09 | |* 16 |                TABLE ACCESS FULL| COMP_HOSCOSTMAIN         |   707 | 16968 |       |   723   (1)| 00:00:09 | |* 17 |             VIEW                |                          |   495 | 69795 |       |   700   (1)| 00:00:09 | |* 18 |              TABLE ACCESS FULL  | COMP_HOSREGISTER         |   495 | 75735 |       |   700   (1)| 00:00:09 | |* 19 |           VIEW                  |                          |    29 |   899 |       |    17   (0)| 00:00:01 | |* 20 |            TABLE ACCESS FULL    | CFG_MEDICALESTABLISHMENT |    29 |  1102 |       |    17   (0)| 00:00:01 | |  21 |         VIEW                    |                          | 34547 |  1551K|       |  8824   (1)| 00:01:46 | |* 22 |          TABLE ACCESS FULL      | JOIN_PERSONALRECORD      | 34547 |  1012K|       |  8824   (1)| 00:01:46 | |  23 |       VIEW                      |                          |     1 |     8 |       | 15777   (1)| 00:03:10 | |* 24 |        TABLE ACCESS FULL        | JOIN_PERSONAJOIN         |     1 |    32 |       | 15777   (1)| 00:03:10 | |* 25 |     VIEW                        |                          |   317 |  3170 |       |   137   (1)| 00:00:02 | |* 26 |      TABLE ACCESS FULL          | CFG_MAINTAINDISEASE      |   317 |  5706 |       |   137   (1)| 00:00:02 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$13    2 - SEL$27C75F45 / from$_subquery$_013@SEL$13    3 - SEL$27C75F45    4 - SEL$10       / from$_subquery$_011@SEL$12    5 - SEL$10    6 - SEL$8        / from$_subquery$_009@SEL$10    7 - SEL$8    8 - SEL$D28406B9 / from$_subquery$_007@SEL$8    9 - SEL$D28406B9   10 - SEL$E875D2C0 / from$_subquery$_005@SEL$6   11 - SEL$E875D2C0   12 - SEL$D186B18B / from$_subquery$_003@SEL$4   13 - SEL$D186B18B   14 - SEL$D186B18B / A@SEL$2   15 - SEL$1        / from$_subquery$_014@SEL$2   16 - SEL$1        / B@SEL$1   17 - SEL$3        / from$_subquery$_015@SEL$4   18 - SEL$3        / C@SEL$3   19 - SEL$5        / from$_subquery$_016@SEL$6   20 - SEL$5        / D@SEL$5   21 - SEL$7        / from$_subquery$_017@SEL$8   22 - SEL$7        / E@SEL$7   23 - SEL$9        / from$_subquery$_018@SEL$10   24 - SEL$9        / F@SEL$9   25 - SEL$11       / from$_subquery$_019@SEL$12   26 - SEL$11       / M@SEL$11 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')       DB_VERSION('11.2.0.3')       OPT_PARAM('_complex_view_merging' 'false')       OPT_PARAM('_optimizer_use_feedback' 'false')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       OUTLINE_LEAF(@"SEL$D186B18B")       OUTER_JOIN_TO_INNER(@"SEL$2" "from$_subquery$_014"@"SEL$2")       OUTLINE_LEAF(@"SEL$3")       OUTLINE_LEAF(@"SEL$E875D2C0")       OUTER_JOIN_TO_INNER(@"SEL$4" "from$_subquery$_015"@"SEL$4")       OUTLINE_LEAF(@"SEL$5")       OUTLINE_LEAF(@"SEL$D28406B9")       OUTER_JOIN_TO_INNER(@"SEL$6" "from$_subquery$_016"@"SEL$6")       OUTLINE_LEAF(@"SEL$7")       OUTLINE_LEAF(@"SEL$8")       OUTLINE_LEAF(@"SEL$9")       OUTLINE_LEAF(@"SEL$10")       OUTLINE_LEAF(@"SEL$11")       OUTLINE_LEAF(@"SEL$27C75F45")       OUTER_JOIN_TO_INNER(@"SEL$12" "from$_subquery$_019"@"SEL$12")       OUTLINE_LEAF(@"SEL$13")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$4")       OUTLINE(@"SEL$6")       OUTLINE(@"SEL$12")       NO_ACCESS(@"SEL$13" "from$_subquery$_013"@"SEL$13")       USE_HASH_AGGREGATION(@"SEL$13")       NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12")       NO_ACCESS(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")       LEADING(@"SEL$27C75F45" "from$_subquery$_011"@"SEL$12" "from$_subquery$_019"@"SEL$12")       USE_NL(@"SEL$27C75F45" "from$_subquery$_019"@"SEL$12")       NO_ACCESS(@"SEL$10" "from$_subquery$_009"@"SEL$10")       NO_ACCESS(@"SEL$10" "from$_subquery$_018"@"SEL$10")       LEADING(@"SEL$10" "from$_subquery$_009"@"SEL$10" "from$_subquery$_018"@"SEL$10")       USE_NL(@"SEL$10" "from$_subquery$_018"@"SEL$10")       FULL(@"SEL$11" "M"@"SEL$11")       NO_ACCESS(@"SEL$8" "from$_subquery$_007"@"SEL$8")       NO_ACCESS(@"SEL$8" "from$_subquery$_017"@"SEL$8")       LEADING(@"SEL$8" "from$_subquery$_007"@"SEL$8" "from$_subquery$_017"@"SEL$8")       USE_NL(@"SEL$8" "from$_subquery$_017"@"SEL$8")       FULL(@"SEL$9" "F"@"SEL$9")       NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6")       NO_ACCESS(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")       LEADING(@"SEL$D28406B9" "from$_subquery$_005"@"SEL$6" "from$_subquery$_016"@"SEL$6")       USE_NL(@"SEL$D28406B9" "from$_subquery$_016"@"SEL$6")       FULL(@"SEL$7" "E"@"SEL$7")       NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4")       NO_ACCESS(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")       LEADING(@"SEL$E875D2C0" "from$_subquery$_003"@"SEL$4" "from$_subquery$_015"@"SEL$4")       USE_NL(@"SEL$E875D2C0" "from$_subquery$_015"@"SEL$4")       FULL(@"SEL$5" "D"@"SEL$5")       FULL(@"SEL$D186B18B" "A"@"SEL$2")       NO_ACCESS(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")       LEADING(@"SEL$D186B18B" "A"@"SEL$2" "from$_subquery$_014"@"SEL$2")       USE_NL(@"SEL$D186B18B" "from$_subquery$_014"@"SEL$2")       FULL(@"SEL$3" "C"@"SEL$3")       FULL(@"SEL$1" "B"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------   14 - filter("A"."DELETEFLAG_CH"='N')   15 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND               TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')<'2017-05-31' AND               "B"."DELETEFLAG_CH"='N'))   16 - filter("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")   17 - filter("C"."DELETEFLAG_CH"='N')   18 - filter("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")   19 - filter("D"."DELETEFLAG_CH"='N')   20 - filter("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")   22 - filter("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")   24 - filter("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")   25 - filter("M"."DELETEFLAG_CH"='N')   26 - filter("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC") Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_I        NHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C"."HR_COMPENSATI        ONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],        "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],        "M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7], SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22],        SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]    2 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7],        "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],        "F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]    3 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],         "from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50],        "from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12], "M"."MD_ICDCODE_VC"[VARCHAR2,10]    4 - "from$_subquery$_011"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_011"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_011"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_011"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_011"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_011"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_011"."HR_SEX"[VARCHAR2,5], "from$_subquery$_011"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_011"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "from$_subquery$_011"."HR_INHOSDATE_DT"[DATE,7], "from$_subquery$_011"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "from$_subquery$_011"."HR_OUTHOSDATE_DT"[DATE,7], "from$_subquery$_011"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],         "from$_subquery$_011"."PR_BRITHDAY_VC"[VARCHAR2,50], "from$_subquery$_011"."PJ_CANTONCODE_CH"[VARCHAR2,12]    5 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],        "from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],        "from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]    6 - "from$_subquery$_009"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_009"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_009"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_009"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_009"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_009"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_009"."HR_SEX"[VARCHAR2,5], "from$_subquery$_009"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_009"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_009"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_009"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_009"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_009"."HR_OUTHOSDATE_DT"[DATE,7],        "from$_subquery$_009"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],        "from$_subquery$_009"."PR_BRITHDAY_VC"[VARCHAR2,50]    7 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],        "from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],        "from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],        "E"."PR_PERSONALCODE_VC"[VARCHAR2,18]    8 - "from$_subquery$_007"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_007"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_007"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_007"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "from$_subquery$_007"."HR_NAME"[VARCHAR2,50], "from$_subquery$_007"."HR_SEX"[VARCHAR2,5],        "from$_subquery$_007"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_007"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_007"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_007"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_007"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_007"."HR_OUTHOSDATE_DT"[DATE,7],        "from$_subquery$_007"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]    9 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7],        "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]   10 - "from$_subquery$_005"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "from$_subquery$_005"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_005"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_005"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "from$_subquery$_005"."HR_PERSONALCODE_VC"[VARCHAR2,30], "from$_subquery$_005"."HR_NAME"[VARCHAR2,50],        "from$_subquery$_005"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "from$_subquery$_005"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "from$_subquery$_005"."HR_DISEASECODE_VC"[VARCHAR2,10], "from$_subquery$_005"."HR_INHOSDATE_DT"[DATE,7],        "from$_subquery$_005"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "from$_subquery$_005"."HR_OUTHOSDATE_DT"[DATE,7]   11 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7], "C"."HR_OUTHOSDATE_DT"[DATE,7],        "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],        "C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],        "C"."HR_PERSONALCODE_VC"[VARCHAR2,30]   12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "from$_subquery$_003"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "from$_subquery$_003"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "from$_subquery$_003"."HCM_SETTLEMENTDATE_DT"[DATE,7]   13 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]   14 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]   15 - "B"."DELETEFLAG_CH"[CHARACTER,1], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]   16 - "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "B"."DELETEFLAG_CH"[CHARACTER,1]   17 - "C"."DELETEFLAG_CH"[CHARACTER,1], "C"."HR_OUTHOSDATE_DT"[DATE,7],        "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],        "C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50],        "C"."HR_PERSONALCODE_VC"[VARCHAR2,30]   18 - "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],        "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_DISEASECODE_VC"[VARCHAR2,10], "C"."HR_INHOSDATE_DT"[DATE,7],        "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."DELETEFLAG_CH"[CHARACTER,1]   19 - "D"."DELETEFLAG_CH"[VARCHAR2,5], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]   20 - "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "D"."DELETEFLAG_CH"[VARCHAR2,5]   21 - "E"."PR_BRITHDAY_VC"[VARCHAR2,50], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18]   22 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]   23 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]   24 - "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]   25 - "M"."DELETEFLAG_CH"[CHARACTER,1], "M"."MD_ICDCODE_VC"[VARCHAR2,10]   26 - "M"."MD_ICDCODE_VC"[VARCHAR2,10], "M"."DELETEFLAG_CH"[CHARACTER,1] Note -----    - dynamic sampling used for this statement (level=2) 该sql正常执行时的执行计划（其他机器oracle数据库的该sql的执行计划） Plan hash value: 1364454912 ------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                          |       |       |       |   109K(100)|       | |   1 |  HASH GROUP BY           |                          |   330K|    86M|    92M|   109K  (1)| 00:21:59 | |*  2 |   HASH JOIN              |                          |   330K|    86M|       | 90325   (1)| 00:18:04 | |*  3 |    TABLE ACCESS FULL     | CFG_MAINTAINDISEASE      | 31653 |   556K|       |   138   (2)| 00:00:02 | |*  4 |    HASH JOIN             |                          |   328K|    80M|       | 90184   (1)| 00:18:03 | |*  5 |     TABLE ACCESS FULL    | CFG_MEDICALESTABLISHMENT |  2926 |   108K|       |    17   (0)| 00:00:01 | |*  6 |     HASH JOIN OUTER      |                          |   328K|    68M|    61M| 90164   (1)| 00:18:02 | |*  7 |      HASH JOIN OUTER     |                          |   322K|    57M|    50M| 64090   (2)| 00:12:50 | |*  8 |       HASH JOIN          |                          |   316K|    47M|    21M| 45795   (2)| 00:09:10 | |*  9 |        TABLE ACCESS FULL | COMP_HOSREGISTER         |   210K|    19M|       |  3314   (1)| 00:00:40 | |* 10 |        HASH JOIN         |                          |   310K|    18M|       | 40322   (2)| 00:08:04 | |* 11 |         TABLE ACCESS FULL| COMP_HOSCOSTMAIN         | 10467 |   316K|       |  2208   (2)| 00:00:27 | |* 12 |         TABLE ACCESS FULL| COMP_HOSPRESCRIPTION     |  6129K|   181M|       | 38070   (2)| 00:07:37 | |  13 |       TABLE ACCESS FULL  | JOIN_PERSONALRECORD      |  3454K|    98M|       |  8851   (1)| 00:01:47 | |  14 |      TABLE ACCESS FULL   | JOIN_PERSONAJOIN         |  3456K|   105M|       | 15795   (1)| 00:03:10 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9A5C6B1E    3 - SEL$9A5C6B1E / M@SEL$11    5 - SEL$9A5C6B1E / D@SEL$5    9 - SEL$9A5C6B1E / C@SEL$3   11 - SEL$9A5C6B1E / B@SEL$2   12 - SEL$9A5C6B1E / A@SEL$1   13 - SEL$9A5C6B1E / E@SEL$7   14 - SEL$9A5C6B1E / F@SEL$9 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')       DB_VERSION('11.2.0.1')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9A5C6B1E")       MERGE(@"SEL$72AEFE3E")       OUTLINE(@"SEL$F0958867")       OUTER_JOIN_TO_INNER(@"SEL$13")       OUTLINE(@"SEL$72AEFE3E")       MERGE(@"SEL$11")       MERGE(@"SEL$B97648DD")       OUTLINE(@"SEL$13")       OUTLINE(@"SEL$12")       OUTLINE(@"SEL$11")       OUTLINE(@"SEL$B97648DD")       MERGE(@"SEL$096E5AED")       MERGE(@"SEL$9")       OUTLINE(@"SEL$10")       OUTLINE(@"SEL$096E5AED")       MERGE(@"SEL$15E987C1")       MERGE(@"SEL$7")       OUTLINE(@"SEL$9")       OUTLINE(@"SEL$8")       OUTLINE(@"SEL$15E987C1")       MERGE(@"SEL$5")       MERGE(@"SEL$7237DA6D")       OUTLINE(@"SEL$7")       OUTLINE(@"SEL$6")       OUTLINE(@"SEL$5")       OUTLINE(@"SEL$7237DA6D")       MERGE(@"SEL$3")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$4")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       FULL(@"SEL$9A5C6B1E" "B"@"SEL$2")       FULL(@"SEL$9A5C6B1E" "A"@"SEL$1")       FULL(@"SEL$9A5C6B1E" "C"@"SEL$3")       FULL(@"SEL$9A5C6B1E" "E"@"SEL$7")       FULL(@"SEL$9A5C6B1E" "F"@"SEL$9")       FULL(@"SEL$9A5C6B1E" "D"@"SEL$5")       FULL(@"SEL$9A5C6B1E" "M"@"SEL$11")       LEADING(@"SEL$9A5C6B1E" "B"@"SEL$2" "A"@"SEL$1" "C"@"SEL$3" "E"@"SEL$7" "F"@"SEL$9" "D"@"SEL$5"               "M"@"SEL$11")       USE_HASH(@"SEL$9A5C6B1E" "A"@"SEL$1")       USE_HASH(@"SEL$9A5C6B1E" "C"@"SEL$3")       USE_HASH(@"SEL$9A5C6B1E" "E"@"SEL$7")       USE_HASH(@"SEL$9A5C6B1E" "F"@"SEL$9")       USE_HASH(@"SEL$9A5C6B1E" "D"@"SEL$5")       USE_HASH(@"SEL$9A5C6B1E" "M"@"SEL$11")       PX_JOIN_FILTER(@"SEL$9A5C6B1E" "E"@"SEL$7")       SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "C"@"SEL$3")       SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "D"@"SEL$5")       SWAP_JOIN_INPUTS(@"SEL$9A5C6B1E" "M"@"SEL$11")       USE_HASH_AGGREGATION(@"SEL$9A5C6B1E")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("M"."MD_DISEASEID_VC"="C"."HR_DISEASECODE_VC")    3 - filter("M"."DELETEFLAG_CH"='N')    4 - access("C"."HR_INSTITUTIONCODE_CH"="D"."ME_INSTITUTIONCODE_VC")    5 - filter("D"."DELETEFLAG_CH"='N')    6 - access("F"."PJ_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")    7 - access("C"."HR_PERSONALCODE_VC"="E"."PR_PERSONALCODE_VC")    8 - access("A"."HP_HOSREGISTERCODE_VC"="C"."HR_HOSREGISTERCODE_VC")    9 - filter("C"."DELETEFLAG_CH"='N')   10 - access("A"."HP_HOSREGISTERCODE_VC"="B"."HCM_HOSREGISTERCODE_VC")   11 - filter((TO_CHAR(INTERNAL_FUNCTION("B"."HCM_SETTLEMENTDATE_DT"),'yyyy-MM-dd')>'2017-03-31' AND               "B"."DELETEFLAG_CH"='N'))   12 - filter("A"."DELETEFLAG_CH"='N') Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "A"."HP_HOSREGISTERCODE_VC"||REPLACE("M"."MD_ICDCODE_VC",'.','')||TO_CHAR(INTERNAL_FUNCTION("C        "."HR_INHOSDATE_DT"),'yyyyMMdd')||TO_CHAR(INTERNAL_FUNCTION("C"."HR_OUTHOSDATE_DT"),'yyyyMMdd')||"C".        "HR_COMPENSATIONTYPE_VC"[51], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],        "E"."PR_BRITHDAY_VC"[VARCHAR2,50], "M"."MD_ICDCODE_VC"[VARCHAR2,10],        "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_INHOSDATE_DT"[DATE,7],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "C"."HR_INHOSDATE_DT"[DATE,7],        SUM(NVL("A"."HP_ALLOWEDCOMP_DEC",0))[22], SUM(NVL("A"."HP_PRESCRIPTIONFEE_DEC",0))[22]    2 - (#keys=1) "M"."MD_ICDCODE_VC"[VARCHAR2,10], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "E"."PR_BRITHDAY_VC"[VARCHAR2,50], "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20],        "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5],        "F"."PJ_CANTONCODE_CH"[VARCHAR2,12], "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]    3 - "M"."MD_DISEASEID_VC"[VARCHAR2,20], "M"."MD_ICDCODE_VC"[VARCHAR2,10]    4 - (#keys=1) "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50],        "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],        "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]    5 - "D"."ME_INSTITUTIONCODE_VC"[VARCHAR2,15], "D"."ME_INSTITUTINNAME_VC"[VARCHAR2,50]    6 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_BRITHDAY_VC"[VARCHAR2,50],        "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12]    7 - (#keys=1) "C"."HR_PERSONALCODE_VC"[VARCHAR2,30], "E"."PR_PERSONALCODE_VC"[VARCHAR2,18],        "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]    8 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100],        "C"."HR_OUTHOSDATE_DT"[DATE,7], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22],        "B"."HCM_SETTLEMENTDATE_DT"[DATE,7], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22]    9 - "C"."HR_HOSREGISTERCODE_VC"[VARCHAR2,20], "C"."HR_PERSONALCODE_VC"[VARCHAR2,30],        "C"."HR_NAME"[VARCHAR2,50], "C"."HR_SEX"[VARCHAR2,5], "C"."HR_INSTITUTIONCODE_CH"[VARCHAR2,14],        "C"."HR_COMPENSATIONTYPE_VC"[VARCHAR2,5], "C"."HR_DISEASECODE_VC"[VARCHAR2,10],        "C"."HR_INHOSDATE_DT"[DATE,7], "C"."HR_OUTHOSSTATUS_VC"[VARCHAR2,100], "C"."HR_OUTHOSDATE_DT"[DATE,7]   10 - (#keys=1) "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7],        "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22], "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]   11 - "B"."HCM_HOSREGISTERCODE_VC"[VARCHAR2,20], "B"."HCM_SETTLEMENTDATE_DT"[DATE,7]   12 - "A"."HP_HOSREGISTERCODE_VC"[VARCHAR2,20], "A"."HP_PRESCRIPTIONFEE_DEC"[NUMBER,22],        "A"."HP_ALLOWEDCOMP_DEC"[NUMBER,22]   13 - "E"."PR_PERSONALCODE_VC"[VARCHAR2,18], "E"."PR_BRITHDAY_VC"[VARCHAR2,50]   14 - "F"."PJ_PERSONALCODE_VC"[VARCHAR2,30], "F"."PJ_CANTONCODE_CH"[VARCHAR2,12] 已选择198行。     同一条sql语句不通实例执行下的执行计划做对比，除了性能问题实例中该sql执行计划出现view、nestloop嵌套循环外、执行时间特别长外，就是 出现性能问题的实例中的sql语句执行计划里还出现了： OPT_PARAM('_complex_view_merging' 'false')，于是生成pfile查看该实例的参数设置 pfile: orclnew.__db_cache_size=36775657472 orclnew.__java_pool_size=134217728 orclnew.__large_pool_size=134217728 orclnew.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment orclnew.__pga_aggregate_target=13824425984 orclnew.__sga_target=41339060224 orclnew.__shared_io_pool_size=0 orclnew.__shared_pool_size=3892314112 orclnew.__streams_pool_size=134217728*._complex_view_merging=FALSE*._optimizer_use_feedback=FALSE*._simple_view_merging=FALSE *.audit_file_dest='E:\app\Administrator\admin\orclnew\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='E:\APP\ADMINISTRATOR\ORADATA\ORCLNEW\CONTROL01.CTL','E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCLNEW\CONTROL02.CTL'#Restore Controlfile *.db_block_size=8192 *.db_domain='' *.db_name='orclnew' *.db_recovery_file_dest='E:\app\Administrator\fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.deferred_segment_creation=FALSE *.diagnostic_dest='E:\app\Administrator' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclnewXDB)' *.log_archive_dest_1='location=D:\orclnew\archivelog' *.log_archive_format='arch_%r_%t_%s.arc' *.open_cursors=300 *.pga_aggregate_target=13740539904 *.processes=500 *.remote_login_passwordfile='EXCLUSIVE' *.session_cached_cursors=3000 *.sga_target=41221619712 *.undo_tablespace='UNDOTBS1'     于是尝试修改隐藏参数，执行 alter system set "_complex_view_merging"=TRUE scope=both;alter system set "_simple_view_merging"=TRUE scope=both; 修改完成重启数据库服务后，问题sql执行计划恢复正常，sql正常执行8s就出结果。

ORACLE RAC 一节点出现Suspending MMON slave action kewrmrfsa_ for 82800 seconds

       ><> ><>                                                                                        >                                                                                                            >>                >&  &                                                  >                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      >

Linux 的dhcp启动异常之No subnet declaration for eth1 (192.168.48.134)

Oracle数据库服务器CPU持续100%之等待事件asynch descriptor resize

2017年5月11日下午13点，一地市技术反应服务器响应慢，CPU长时间100%如图： 经观察数据库服务器资源监视器，发现是oracle进程导致的，登录数据库查询数据库等待事件，发现asynch descriptor resize居高不下 按照等待事件类型查询对应的会话信息如下，有30多个会话同时执行同一条sql语句：b7rng1bdrzzkq 查询sql语句b7rng1bdrzzkq对应的sql文本如下： b7rng1bdrzzkq的执行计划如下： PLAN_TABLE_OUTPUT  -------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID b7rng1bdrzzkq, child number 0  -------------------------------------  select zdb.HOSPITAL_ID,  zdh.HOSPITAL_NAME,  zdb.hisid,  zdb.HS_PATIENT_NAME as  PATIENT_NAME,  hs_area_code as  DEPTNAME,  admission_disease_name as  RULE_NAME,  round(zdb.TOTAL_COST/10000,4) as Total_Costs  from zk_dw_bill zdb, zk_dim_hospital zdh  where zdb.HOSPITAL_ID = zdh.HOSPITAL_ID_SZ  and exists  (select 1 from  zk_dw_billdetail zdbd where zdb.HISID = zdbd.pid  AND zdbd.item_date >=  to_date('2017/5/11 00:00:00', 'yyyy/mm/dd HH24:mi:ss')  AND zdbd.item_date <=  to_date('2017/5/11 23:59:59', 'yyyy/mm/dd HH24:  Plan hash value: 32811706  -------------------------------------------------------------------------------------------------------------------------------  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |  -------------------------------------------------------------------------------------------------------------------------------  | 0 | SELECT STATEMENT | | | | 13 (100)| | | |  | 1 | SORT ORDER BY | | 1 | 424 | 13 (24)| 00:00:01 | | |  | 2 | NESTED LOOPS | | | | | | | |  | 3 | NESTED LOOPS | | 1 | 424 | 12 (17)| 00:00:01 | | |  | 4 | MERGE JOIN CARTESIAN | | 1 | 362 | 12 (17)| 00:00:01 | | |  | 5 | MERGE JOIN CARTESIAN | | 1 | 296 | 6 (17)| 00:00:01 | | |  | 6 | SORT UNIQUE | | 1 | 261 | 2 (0)| 00:00:01 | | |  | 7 | PARTITION RANGE SINGLE | | 1 | 261 | 2 (0)| 00:00:01 | 862 | 862 |  |* 8 | TABLE ACCESS FULL | CLAIMDETAILHOSPITAL | 1 | 261 | 2 (0)| 00:00:01 | 862 | 862 |  | 9 | BUFFER SORT | | 31 | 1085 | 4 (25)| 00:00:01 | | |  | 10 | TABLE ACCESS FULL | DW_ZD_HOSPITAL_YB | 31 | 1085 | 3 (0)| 00:00:01 | | |  | 11 | BUFFER SORT | | 1 | 66 | 9 (23)| 00:00:01 | | |  | 12 | VIEW | VW_SQ_1 | 1 | 66 | 6 (17)| 00:00:01 | | |  | 13 | HASH UNIQUE | | 1 | 2083 | | | | |  |* 14 | HASH JOIN | | 1 | 2083 | 6 (17)| 00:00:01 | | |  | 15 | PARTITION RANGE SINGLE | | 1 | 2077 | 2 (0)| 00:00:01 | 862 | 862 |  |* 16 | TABLE ACCESS FULL | AUDITRESULT4HOSPITAL | 1 | 2077 | 2 (0)| 00:00:01 | 862 | 862 |  | 17 | TABLE ACCESS FULL | GZ_LIST | 29 | 174 | 3 (0)| 00:00:01 | | |  |* 18 | INDEX UNIQUE SCAN | PK_CLAIMHOSPITAL_HISID | 1 | | 0 (0)| | | |  |* 19 | TABLE ACCESS BY GLOBAL INDEX ROWID| CLAIMHOSPITAL | 1 | 62 | 0 (0)| | ROWID | ROWID |  -------------------------------------------------------------------------------------------------------------------------------  Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------  1 - SEL$817CBF02  8 - SEL$817CBF02 / CLAIMDETAILHOSPITAL@SEL$5  10 - SEL$817CBF02 / DW_ZD_HOSPITAL_YB@SEL$3  12 - SEL$A7D54A5B / VW_SQ_1@SEL$E4B10583  13 - SEL$A7D54A5B  16 - SEL$A7D54A5B / AA@SEL$7  17 - SEL$A7D54A5B / GL@SEL$7  18 - SEL$817CBF02 / CLAIMHOSPITAL@SEL$2  19 - SEL$817CBF02 / CLAIMHOSPITAL@SEL$2  Outline Data  -------------  /*+  BEGIN_OUTLINE_DATA  IGNORE_OPTIM_EMBEDDED_HINTS  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')  DB_VERSION('11.2.0.1')  OPT_PARAM('_optimizer_use_feedback' 'false')  ALL_ROWS  OUTLINE_LEAF(@"SEL$A7D54A5B")  OUTLINE_LEAF(@"SEL$817CBF02")  UNNEST(@"SEL$68B588A0")  UNNEST(@"SEL$7286615E")  OUTLINE(@"SEL$68B588A0")  MERGE(@"SEL$7")  OUTLINE(@"SEL$E4B10583")  OUTLINE(@"SEL$7286615E")  MERGE(@"SEL$5")  OUTLINE(@"SEL$6")  OUTLINE(@"SEL$7")  OUTLINE(@"SEL$5428C7F1")  MERGE(@"SEL$2")  MERGE(@"SEL$3")  OUTLINE(@"SEL$4")  OUTLINE(@"SEL$5")  OUTLINE(@"SEL$1")  OUTLINE(@"SEL$2")  OUTLINE(@"SEL$3")  FULL(@"SEL$817CBF02" "CLAIMDETAILHOSPITAL"@"SEL$5")  FULL(@"SEL$817CBF02" "DW_ZD_HOSPITAL_YB"@"SEL$3")  NO_ACCESS(@"SEL$817CBF02" "VW_SQ_1"@"SEL$E4B10583")  INDEX(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2" ("CLAIMHOSPITAL"."HISID"))  LEADING(@"SEL$817CBF02" "CLAIMDETAILHOSPITAL"@"SEL$5" "DW_ZD_HOSPITAL_YB"@"SEL$3" "VW_SQ_1"@"SEL$E4B10583"  "CLAIMHOSPITAL"@"SEL$2")  USE_MERGE_CARTESIAN(@"SEL$817CBF02" "DW_ZD_HOSPITAL_YB"@"SEL$3")  USE_MERGE(@"SEL$817CBF02" "VW_SQ_1"@"SEL$E4B10583")  USE_NL(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2")  NLJ_BATCHING(@"SEL$817CBF02" "CLAIMHOSPITAL"@"SEL$2")  FULL(@"SEL$A7D54A5B" "AA"@"SEL$7")  FULL(@"SEL$A7D54A5B" "GL"@"SEL$7")  LEADING(@"SEL$A7D54A5B" "AA"@"SEL$7" "GL"@"SEL$7")  USE_HASH(@"SEL$A7D54A5B" "GL"@"SEL$7")  USE_HASH_AGGREGATION(@"SEL$A7D54A5B")  END_OUTLINE_DATA  */  Predicate Information (identified by operation id):  ---------------------------------------------------  8 - filter("ITEM_DATE"<=TO_DATE(' 2017-05-11 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))  14 - access("GL"."ID"=TO_NUMBER("AA"."RULECODE"))  16 - filter("AA"."ITEM_DATE"<=TO_DATE(' 2017-05-11 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))  18 - access("HISID"="PID")  filter("ITEM_1"="HISID")  19 - filter("HOSPITAL_ID"="HOSPITAL_ID_SZ")  Column Projection Information (identified by operation id):  -----------------------------------------------------------  1 - (#keys=1) INTERNAL_FUNCTION("SETTLE_DATE")[7], "HOSPITAL_ID"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128],  "HISID"[VARCHAR2,128], "HS_PATIENT_NAME"[VARCHAR2,200], "HS_AREA_CODE"[VARCHAR2,100],  "ADMISSION_DISEASE_NAME"[VARCHAR2,128], ROUND("TOTAL_COST"/10000,4)[22]  2 - "HOSPITAL_NAME"[VARCHAR2,128], "HISID"[VARCHAR2,128], "HOSPITAL_ID"[VARCHAR2,128],  "ADMISSION_DISEASE_NAME"[VARCHAR2,128], "HS_AREA_CODE"[VARCHAR2,100], "TOTAL_COST"[NUMBER,22],  "HS_PATIENT_NAME"[VARCHAR2,200], "SETTLE_DATE"[DATE,7]  3 - "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128], "CLAIMHOSPITAL".ROWID[ROWID,10],  "HISID"[VARCHAR2,128]  4 - "PID"[VARCHAR2,500], "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128], "ITEM_1"[VARCHAR2,128]  5 - "PID"[VARCHAR2,500], "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128]  6 - (#keys=1) "PID"[VARCHAR2,500]  7 - "PID"[VARCHAR2,500]  8 - "PID"[VARCHAR2,500]  9 - (#keys=0) "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128]  10 - "HOSPITAL_ID_SZ"[VARCHAR2,128], "HOSPITAL_NAME"[VARCHAR2,128]  11 - (#keys=0) "ITEM_1"[VARCHAR2,128]  12 - "ITEM_1"[VARCHAR2,128]  13 - "AA"."CLAIM_ID"[VARCHAR2,128]  14 - (#keys=1) "AA"."CLAIM_ID"[VARCHAR2,128]  15 - "AA"."CLAIM_ID"[VARCHAR2,128], "AA"."RULECODE"[VARCHAR2,4000]  16 - "AA"."CLAIM_ID"[VARCHAR2,128], "AA"."RULECODE"[VARCHAR2,4000]  17 - "GL"."ID"[NUMBER,22]  18 - "CLAIMHOSPITAL".ROWID[ROWID,10], "HISID"[VARCHAR2,128]  19 - "HOSPITAL_ID"[VARCHAR2,128], "ADMISSION_DISEASE_NAME"[VARCHAR2,128], "HS_AREA_CODE"[VARCHAR2,100],  "TOTAL_COST"[NUMBER,22], "HS_PATIENT_NAME"[VARCHAR2,200], "SETTLE_DATE"[DATE,7]  已选择144行。 由于服务器CPU100%，响应极慢，由于是select查询语句，与地市技术人员沟通后，决定查杀等待事件asynch descriptor resize对应的会话进程： 如图所示，会话查杀后，服务器CPU恢复正常水平。

cdh5.3.10_hadoop环境迁移之主机IP修改

cdh5.3.10_hadoop环境迁移 主机迁移后Hadoop集群调整： 1、修改/etc/hosts（三台主机均需要修改） 修改前： [root@hadoop-master ~]# cat /etc/hosts 127.0.0.1 localhost.localdomain localhost.localdomain localhost4 localhost4.localdomain4 localhost ::1 localhost.localdomain localhost.localdomain localhost6 localhost6.localdomain6 localhost 10.117.130.207 hadoop-master 10.117.130.208 hadoop-slave1 10.117.130.209 hadoop-slave2 [root@hadoop-master ~]#  修改后： [root@hadoop-master ~]# cat /etc/hosts 127.0.0.1 localhost.localdomain localhost.localdomain localhost4 localhost4.localdomain4 localhost ::1 localhost.localdomain localhost.localdomain localhost6 localhost6.localdomain6 localhost 10.117.130.110 hadoop-master 10.117.130.111 hadoop-slave1 10.117.130.112 hadoop-slave2 [root@hadoop-master ~]# 2、修改ntp时钟同步（三台主机均需要修改） 修改前： [root@hadoop-master ~]# cat /etc/ntp.conf  server 10.117.130.207 prefer 修改后： [root@hadoop-master ~]# cat /etc/ntp.conf  server 10.117.130.110 prefer 3、修改MySQL中hadoop相关配置（仅master主机修改） 修改前： [root@hadoop-master data]# mysql -u root -p  Enter password:  Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 58 Server version: 5.6.29 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. >                                                         >>                                                                                                                                                                                                                                                                                                                                                                       >>                                                                                                                                                                                                                                           >>> >                                                                                                                                                                                                                                           > >>       

oracle exp&imp之IMP-00037: Character set marker unknown

    最近，为地市技术人员做技术支持，碰到一个很奇怪的问题，别人给他提供一个exp的dmp文件，他在本地进行imp的时候报错：IMP-00037:Character set marker unknown 报错信息如下： C:\Users\localadmin>imp test/oracle file=C:\Users\localadmin\Desktop\jzdjxx.dmp log=C:\Users\localadmin\Deskto p\jzdjxx.log Import: Release 11.2.0.1.0 - Production on 星期四 4月 27 11:34:16 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsIMP-00037: 未知字符集标记 IMP-00000: 未成功终止导入 C:\Users\localadmin> 经测试，不是expdp导出的dmp文件，测试信息如下： C:\Users\localadmin>impdp test/oracle directory=MYEXPDP dumpfile=jzdjxx.dmp logfile=jzdjxx.log Import: Release 11.2.0.1.0 - Production on 星期四 4月 27 11:31:58 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: 参数值无效 ORA-39000: 转储文件说明错误 ORA-31619: 转储文件 "c:\jzdjxx.dmp" 无效 在linux操作系统上，查看该dmp文件的字符集，提示信息如下： [root@docmaster ~]#  cat jzdjxx.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6 5353 正确的字符集标识应该是 0354 数据库中的字符集对照码验证： SQL> select nls_charset_name(to_number('5353','xxxx')) from dual; NLS_CHARSET_NAME(TO_NUMBER('5353','XXXX' ---------------------------------------- SQL> select nls_charset_name(to_number('0354','xxxx')) from dual; NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX' ----------------------------------------ZHS16GBK    查询oracle官方metalink，提示exp的dmp文件损坏：

Linux 权限管理之目录权限限制

   最近，项目对Linux文件目录有要求：业务用户及其启动进程能对指定目录drwx，其中有一个用户对该业务用户的目录中的一个子目录能够rwx， 但是对该子目录以上的其他目录不能进行读写操作。其实实现很简单，假设root是业务用户，test是受限用户，/test/t1是root的业务目录，/test/t1/t2 是test用户能够访问并进行操作的受限目录，那么只需要给/test一个701，给t1一个701，给/test/t1/t2一个700并将t2的属组及宿主改为test:test就能 保证root能对/test下任何文件及目录的绝对操作权限又能把test用户限制在/test/t1/t2中，test用户不能对/test或/test/t1进行读写操作。     示例如下： [root@oratest /]# ls -l total 192 ... drwx-----x   3 root   root      4096 Apr 26 17:21 test ... [root@oratest /]#  [root@oratest test]# ls -l total 4 drwx-----x 3 root root 4096 Apr 26 17:21 t1 [root@oratest test]#  [root@oratest t1]# ls -l total 4 -rw-r--r-- 1 root root    0 Apr 26 17:21 t drwx------ 2 test test 4096 Apr 27 09:30 t2 [root@oratest t1]#  [test@oratest ~]$ cd /test [test@oratest test]$ ls ls: .: Permission denied [test@oratest test]$ cd t1 [test@oratest t1]$ ls ls: .: Permission denied [test@oratest t1]$ cd t2 [test@oratest t2]$ ls 1  b [test@oratest t2]$ touch a [test@oratest t2]$ ls 1  a  b [test@oratest t2]$ 

oracle for windows cmd登录sqlplus之SP2-0750

    又是奇葩的windows惹的祸，有测试同事在windows上安装了plsql，竟然还修改了操作系统环境变量oracle_home的值，导致数据库服务器本地执行oracle命令报错SP2-0750 报错信息如下：    处理次问题很简单，要么是设置临时的oracle_home环境变量，要么修改操作系统环境变量oracle_home的值，地市技术人员决定修改操作系统环境变量oracle_home的值，原因是数据库服务器本地操作频繁。 处理方法很简单，就是将数据库安装目录oracle_home的路径写入ORACLE_HOME的环境变量值保存即可。 修改环境变量后cmd执行oracle命令恢复正常：

oracle ORA-01200&ORA-01110&ORA-01122

    由于自己本地使用的virtual-box虚拟机，在虚拟机上安装Oracle，物理机偶尔会因为内存问题无响应，就强制断电重启，没想到虚拟机非常规关机导致oracle ORA-01200&ORA-01110&ORA-01122     启动数据库时报错： [oracle@oratest ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 11:32:03 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area  839282688 bytes Fixed Size    2257880 bytes Variable Size  545262632 bytes Database Buffers  289406976 bytes Redo Buffers    2355200 bytes Database mounted. ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/oradata/oracle/oradata/oradb/sysaux01.dbf' ORA-01200: actual file size of 62720 is smaller than correct size of 66560 blocks 报错提示数据库记录/oradata/oracle/oradata/oradb/sysaux01.dbf的大小是66560而实际大小是62720，此种报错有个简单的解决方法，就是通过dd修改报错相关的数据文件，改成数据库期望的大小，瞒过oracle：SQL> select 66560 - 62720 from dual; 66560-62720 -----------        3840 SQL> !dd if=/dev/zero of=/oradata/oracle/oradata/oradb/sysaux01.dbf bs=8192 count=3840 seek=62721 --bs是数据库块大小，count是差值，seek是从哪开始填充0 3840+0 records in 3840+0 records out 31457280 bytes (31 MB) copied, 0.02607 seconds, 1.2 GB/s SQL> alter database open; Database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area  839282688 bytes Fixed Size    2257880 bytes Variable Size  545262632 bytes Database Buffers  289406976 bytes Redo Buffers    2355200 bytes Database mounted. Database opened. SQL> select count(*) from dba_users;   COUNT(*) ---------- 30 SQL> select count(*) from dba_data_files;   COUNT(*) ---------- 4 SQL>               处理后数据库能正常打开、关闭、重启了。

oracle 执行存储过程 ora00131

    今天一开发同事反应新建的测试用户无法执行存储过程，使用plsq远程连接执行存储过程报错如下：     报错信息是：ORA-0131：Insufficient privileges，就是用户没有存储过程的调试执行权限：DEBUG CONNECT SESSION     需要具有DBA权限的用户登录授权即可： Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0  Connected as test29@Audit11g31Server SQL> conn zhul/zhulei Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0  Connected as zhul@Audit11g31Server SQL> grant DEBUG CONNECT SESSION to test29; Grant succeeded SQL> grant DEBUG CONNECT SESSION to bmi_xbsh; Grant succeeded SQL> grant DEBUG CONNECT SESSION to bmi_cs01; Grant succeeded SQL> grant DEBUG CONNECT SESSION to bmi_cs02; Grant succeeded SQL>      授权完成后，测试用户再次执行存储过程可以正常执行了。

kettle工具从windows移植到linux上启动报错:\karaf/deploy does not exist

    kettle工具data-integration从windows上用rar压缩.zip后上传到linux后解压，然后运行spoon.sh，启动报错如下： [root@oratest data-integration]# ./spoon.sh  16:27:44,226 INFO  [KarafInstance]  ******************************************************************************* *** Karaf Instance Number: 1 at /opt/data-integration/./system/karaf/caches *** ***   /spoon/data-1                                                         *** *** Karaf Port:8802                                                         *** *** OSGI Service Port:9051                                                  *** ******************************************************************************* 16:27:44,227 INFO  [KarafBoot] Checking to see if org.pentaho.clean.karaf.cache is enabled Mar 27, 2017 4:27:45 PM org.apache.karaf.main.Main$KarafLockCallback lockAquired INFO: Lock acquired. Setting startlevel to 100C:\ETL\data-integration\system\karaf/deploy does not exist, please create it.Root path does not exist: /opt/data-integration/C:\ETL\data-integration\system\karaf/deploy 2017/03/27 16:27:48 - cfgbuilder - Warning: The configuration parameter [org] is not supported by the default configuration builder for scheme: sftp *ERROR* [org.osgi.service.cm.ManagedService, id=247, bundle=48/mvn:org.apache.aries.transaction/org.apache.aries.transaction.manager/1.1.1]: Unexpected problem updating configuration org.apache.aries.transaction java.lang.ExceptionInInitializerError at org.apache.aries.transaction.internal.TransactionManagerService.<init>(TransactionManagerService.java:114) at org.apache.aries.transaction.internal.Activator.updated(Activator.java:63) at org.apache.felix.cm.impl.helper.ManagedServiceTracker.updateService(ManagedServiceTracker.java:148) at org.apache.felix.cm.impl.helper.ManagedServiceTracker.provideConfiguration(ManagedServiceTracker.java:81) at org.apache.felix.cm.impl.ConfigurationManager$ManagedServiceUpdate.provide(ConfigurationManager.java:1448) at org.apache.felix.cm.impl.ConfigurationManager$ManagedServiceUpdate.run(ConfigurationManager.java:1404) at org.apache.felix.cm.impl.UpdateThread.run(UpdateThread.java:103) at java.lang.Thread.run(Thread.java:724) Caused by: java.util.MissingResourceException: Can't find bundle for base name org.apache.aries.transaction.txManager, locale en_US at java.util.ResourceBundle.throwMissingResourceException(ResourceBundle.java:1499) at java.util.ResourceBundle.getBundleImpl(ResourceBundle.java:1322) at java.util.ResourceBundle.getBundle(ResourceBundle.java:1028) at org.apache.aries.util.nls.MessageUtil.createMessageUtil(MessageUtil.java:152) at org.apache.aries.util.nls.MessageUtil.createMessageUtil(MessageUtil.java:107) at org.apache.aries.transaction.internal.NLS.<clinit>(NLS.java:25) ... 8 more 2017-03-27 16:27:53.777:INFO:oejs.Server:jetty-8.1.15.v20140411 2017-03-27 16:27:53.818:INFO:oejs.AbstractConnector:Started NIOSocketConnectorWrapper@0.0.0.0:9051 Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/blueprint/core Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/beans Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/parameterized-types Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/security Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://schemas.xmlsoap.org/wsdl/ Mar 27, 2017 4:27:55 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://www.w3.org/2005/08/addressing     按照启动提示kettle在windows上启动运行后有缓存，缓存目录在： [root@oratest data-1]# pwd/opt/data-integration/system/karaf/caches/spoon/data-1 [root@oratest data-1]# ls cache  generated-bundles  kar  log  txlog [root@oratest data-1]#  [root@oratest data]# pwd/opt/data-integration/system/karaf/data[root@oratest data]#     将以上两个目录清空后，重新启动没有报错： [root@oratest data-integration]# ./spoon.sh  16:31:45,649 INFO  [KarafInstance]  ******************************************************************************* *** Karaf Instance Number: 1 at /opt/data-integration/./system/karaf/caches *** ***   /spoon/data-1                                                         *** *** Karaf Port:8802                                                         *** *** OSGI Service Port:9051                                                  *** ******************************************************************************* 16:31:45,649 INFO  [KarafBoot] Checking to see if org.pentaho.clean.karaf.cache is enabled Mar 27, 2017 4:31:46 PM org.apache.karaf.main.Main launch INFO: Installing and starting initial bundles Mar 27, 2017 4:31:46 PM org.apache.karaf.main.Main launch INFO: All initial bundles installed and set to start Mar 27, 2017 4:31:46 PM org.apache.karaf.main.Main$KarafLockCallback lockAquired INFO: Lock acquired. Setting startlevel to 100 Creating configuration from org.apache.karaf.command.acl.kar.cfg Creating configuration from jmx.acl.java.lang.Memory.cfg Creating configuration from jmx.acl.org.apache.karaf.bundle.cfg Creating configuration from jmx.acl.org.apache.karaf.security.jmx.cfg Creating configuration from org.ops4j.pax.web.cfg Creating configuration from org.ops4j.pax.url.mvn.cfg Creating configuration from org.apache.karaf.command.acl.bundle.cfg Creating configuration from org.apache.karaf.management.cfg Creating configuration from org.apache.karaf.command.acl.shell.cfg Creating configuration from org.apache.karaf.kar.cfg Creating configuration from org.apache.karaf.features.repos.cfg Creating configuration from org.apache.karaf.command.acl.config.cfg Creating configuration from org.apache.felix.fileinstall-deploy.cfg Creating configuration from org.apache.karaf.shell.cfg Creating configuration from org.apache.karaf.command.acl.jaas.cfg Creating configuration from org.apache.karaf.features.obr.cfg Creating configuration from org.apache.activemq.webconsole.cfg Creating configuration from org.apache.karaf.webconsole.cfg Creating configuration from org.pentaho.caching-default.cfg Creating configuration from org.apache.karaf.features.cfg Creating configuration from mondrian.cfg Creating configuration from org.apache.karaf.command.acl.scope_bundle.cfg Creating configuration from jmx.acl.osgi.compendium.cm.cfg Creating configuration from org.apache.karaf.command.acl.feature.cfg Creating configuration from pentaho.geo.roles.cfg Creating configuration from pentaho.marketplace.di.cfg Creating configuration from org.apache.activemq.server-default.cfg Creating configuration from pentaho.metaverse.cfg Creating configuration from jmx.acl.org.apache.karaf.config.cfg Creating configuration from org.apache.karaf.jaas.cfg Creating configuration from org.apache.karaf.command.acl.system.cfg Creating configuration from jmx.acl.cfg Creating configuration from org.apache.karaf.log.cfg Creating configuration from org.ops4j.pax.logging.cfg Creating configuration from org.pentaho.features.cfg 2017/03/27 16:31:49 - cfgbuilder - Warning: The configuration parameter [org] is not supported by the default configuration builder for scheme: sftp Mar 27, 2017 4:31:54 PM org.pentaho.caching.impl.PentahoCacheManagerFactory$RegistrationHandler$1 onSuccess INFO: New Caching Service registered 2017-03-27 16:31:55.514:INFO:oejs.Server:jetty-8.1.15.v20140411 2017-03-27 16:31:55.541:INFO:oejs.AbstractConnector:Started NIOSocketConnectorWrapper@0.0.0.0:9051 SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/opt/data-integration/launcher/../lib/slf4j-log4j12-1.7.7.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/opt/data-integration/plugins/pentaho-big-data-plugin/lib/slf4j-log4j12-1.7.3.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/blueprint/core Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/beans Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/parameterized-types Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://cxf.apache.org/configuration/security Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://schemas.xmlsoap.org/wsdl/ Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://www.w3.org/2005/08/addressing Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.blueprint.NamespaceHandlerRegisterer register INFO: Registered blueprint namespace handler for http://schemas.xmlsoap.org/ws/2004/08/addressing Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener addExtensions INFO: Adding the extensions from bundle org.apache.cxf.cxf-rt-management (173) [org.apache.cxf.management.InstrumentationManager] Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener addExtensions INFO: Adding the extensions from bundle org.apache.cxf.cxf-rt-wsdl (176) [org.apache.cxf.wsdl.WSDLManager] Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener addExtensions INFO: Adding the extensions from bundle org.apache.cxf.cxf-rt-bindings-xml (178) [org.apache.cxf.binding.xml.XMLBindingFactory, org.apache.cxf.binding.xml.wsdl11.XMLWSDLExtensionLoader] Mar 27, 2017 4:32:02 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener addExtensions INFO: Adding the extensions from bundle org.apache.cxf.cxf-rt-bindings-soap (179) [org.apache.cxf.binding.soap.SoapBindingFactory, org.apache.cxf.binding.soap.SoapTransportFactory]

MYSQL : "Can't get hostname for your address"

<><>

ELK 5.0.1+Filebeat5.0.1实时监控MongoDB日志并使用正则解析mongodb日志

    关于ELK5.0.1的安装部署，请参考博文（ ELK 5.0.1+Filebeat5.0.1 for LINUX RHEL6.6 监控MongoDB日志）， 本文重点说明如何适用filebeat实时监控mongodb数据库日志及在logstash正则解析mongodb日志。     部署完ELK5.0.1后，在需要监控mongodb日志的数据库服务器上部署filebeat来抓取日志， 首先需要修改filebeat配置文件： [root@se122 filebeat-5.0.1]# pwd /opt/filebeat-5.0.1 [root@se122 filebeat-5.0.1]#  [root@se122 filebeat-5.0.1]# ls data  filebeat  filebeat.full.yml  filebeat.template-es2x.json  filebeat.template.json  filebeat.yml  scripts [root@se122 filebeat-5.0.1]# cat filebeat.yml  filebeat :  prospectors :   -    paths :        - /root/rs0-0.log   #filebeat负责实时监控的mongodb日志    document_type : mongodblog  #指定filebeat发送到logstash的mongodb日志的文档类型为document_type，一定要指定（logstash接收解析匹配要使用）    input_type : log  registry_file :     /opt/filebeat-5.0.1/data/registry output.logstash:   hosts: ["10.117.194.228:5044"] #logstash服务部署的机器IP地址及运行的服务端口号 [root@se122 filebeat-5.0.1]#  其次修改logstash配置文件： [root@rhel6 config]# pwd /opt/logstash-5.0.1/config [root@rhel6 config]# cat logstash_mongodb.conf  #input { # stdin {} #} input{   beats {     host => "0.0.0.0"     port => 5044     type => mongodblog  #指定filebeat输入的日志类型是mongodblog   } } filter {   if [type] == "mongodblog" { #过滤器，只处理filebeat发送过来的mogodblog日志数据     grok {  #解析发送过来的mognodblog日志        match => ["message","%{TIMESTAMP_ISO8601:timestamp}\s+%{MONGO3_SEVERITY:severity}\s+%{MONGO3_COMPONENT:component}\s+(?:\[%{DATA:context}\])?\s+%{GREEDYDATA:body}"]     }     if [component] =~ "WRITE" {       grok { #第二层解析body部分，提取mongodblog中的command_type、db_name、command、spend_time字段         match => ["body","%{WORD:command_type}\s+%{DATA:db_name}\s+\w+\:\s+%{GREEDYDATA:command}%{INT:spend_time}ms$"]       }     } else {         grok {           match => ["body","\s+%{DATA:db_name}\s+\w+\:\s+%{WORD:command_type}\s+%{GREEDYDATA:command}protocol.*%{INT:spend_time}ms$"]         }     }     date {       match => [ "timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss", "ISO8601"]       remove_field => [ "timestamp" ]     }   } } output{ elasticsearch { hosts => ["192.168.144.230:9200"] index => "mongod_log-%{+YYYY.MM}" } stdout { codec => rubydebug } } [root@rhel6 config]#      然后，确保ELK服务端的服务进程都已经开启，启动命令： [elasticsearch@rhel6 ]$ /home/elasticsearch/elasticsearch-5.0.1/bin/elasticsearch [root@rhel6 ~]# /opt/logstash-5.0.1/bin/logstash -f /opt/logstash-5.0.1/config/logstash_mongodb.conf  [root@rhel6 ~]# /opt/kibana-5.0.1/bin/kibana 在远程端启动filebeat，开始监控mongodb日志： [root@se122 filebeat-5.0.1]# /opt/filebeat-5.0.1/filebeat -e -c /opt/filebeat-5.0.1/filebeat.yml -d "Publish" 2017/02/16 05:50:40.931969 beat.go:264: INFO Home path: [/opt/filebeat-5.0.1] Config path: [/opt/filebeat-5.0.1] Data path: [/opt/filebeat-5.0.1/data] Logs path: [/opt/filebeat-5.0.1/logs] 2017/02/16 05:50:40.932036 beat.go:174: INFO Setup Beat: filebeat; Version: 5.0.1 2017/02/16 05:50:40.932167 logp.go:219: INFO Metrics logging every 30s 2017/02/16 05:50:40.932227 logstash.go:90: INFO Max Retries set to: 3 2017/02/16 05:50:40.932444 outputs.go:106: INFO Activated logstash as output plugin. 2017/02/16 05:50:40.932594 publish.go:291: INFO Publisher name: se122 2017/02/16 05:50:40.935437 async.go:63: INFO Flush Interval set to: 1s 2017/02/16 05:50:40.935473 async.go:64: INFO Max Bulk Size set to: 2048 2017/02/16 05:50:40.935745 beat.go:204: INFO filebeat start running. 2017/02/16 05:50:40.935836 registrar.go:66: INFO Registry file set to: /opt/filebeat-5.0.1/data/registry 2017/02/16 05:50:40.935905 registrar.go:99: INFO Loading registrar data from /opt/filebeat-5.0.1/data/registry 2017/02/16 05:50:40.936717 registrar.go:122: INFO States Loaded from registrar: 1 2017/02/16 05:50:40.936771 crawler.go:34: INFO Loading Prospectors: 1 2017/02/16 05:50:40.936860 prospector_log.go:40: INFO Load previous states from registry into memory 2017/02/16 05:50:40.936923 registrar.go:211: INFO Starting Registrar 2017/02/16 05:50:40.936939 sync.go:41: INFO Start sending events to output 2017/02/16 05:50:40.937148 spooler.go:64: INFO Starting spooler: spool_size: 2048; idle_timeout: 5s 2017/02/16 05:50:40.937286 prospector_log.go:67: INFO Previous states loaded: 1 2017/02/16 05:50:40.937404 crawler.go:46: INFO Loading Prospectors completed. Number of prospectors: 1 2017/02/16 05:50:40.937440 crawler.go:61: INFO All prospectors are initialised and running with 1 states to persist 2017/02/16 05:50:40.937478 prospector.go:106: INFO Starting prospector of type: log 2017/02/16 05:50:40.937745 log.go:84: INFO Harvester started for file: /root/rs0-0.log     我们看到，这里已经开始实时监控mongodb日志是/root/rs0-0.log；然后，我们去logstash开启的前台窗口，可以看到有如下信息： {         "severity" => "I",           "offset" => 243843239,       "spend_time" => "0",       "input_type" => "log",           "source" => "/root/rs0-0.log",          "message" => "2017-02-04T14:03:30.025+0800 I COMMAND  [conn272] command admin.$cmd command: replSetGetStatus { replSetGetStatus: 1 } keyUpdates:0 writeConflicts:0 numYields:0 reslen:364 locks:{} protocol:op_query 0ms",             "type" => "mongodblog",             "body" => "command admin.$cmd command: replSetGetStatus { replSetGetStatus: 1 } keyUpdates:0 writeConflicts:0 numYields:0 reslen:364 locks:{} protocol:op_query 0ms",          "command" => "{ replSetGetStatus: 1 } keyUpdates:0 writeConflicts:0 numYields:0 reslen:364 locks:{} ",             "tags" => [         [0] "beats_input_codec_plain_applied"     ],        "component" => "COMMAND",       "@timestamp" => 2017-02-04T06:03:30.025Z,          "db_name" => "admin.$cmd",     "command_type" => "replSetGetStatus",         "@version" => "1",             "beat" => {         "hostname" => "se122",             "name" => "se122",          "version" => "5.0.1"     },             "host" => "se122",          "context" => "conn272" } 这说明logstash按照配置文件正常过滤并按照指定的正则解析了mongodblog日志，再到kibana创建索引： 然后，就能在kibana自定义视图查看到监控到的Mongodb日志了：

ORACLE impdp或expdp与ORA-31693&ORA-31640&ORA-19505&ORA-27037

    今天，安装完了衡阳ORACLE 11.2.0.4 rac for RHEL6.8集群后，做数据迁移的时候，碰到了expdp报错： ORA-31693&ORA-31640&ORA-19505&ORA-27037。具体的报错信息如下： Import: Release 11.2.0.4.0 - Production on Wed Feb 15 11:24:55 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ;;;  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "ZHUL"."SYS_IMPORT_SCHEMA_01":  zhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log parallel=4  Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201610" failed to load/unload and is being skipped due to error: ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory     由于dmp文件比较大，34G之多，里边有大表，加上客户方对时间要求比较紧，所以使用如下命令并行导入：impdp zhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log parallel=4  然而，执行过程却碰到上述错误，更为蹊跷的是，虽然impdp报错说找不到指定的dmp文件，但是主机上确实存在这个dmp文件，而且报错imp还有数据导入到数据库中，信息如下： ORA-31693: Table data object "BMI"."DW_OPINION_DETAILS":"P_DW_OPINION_DETAILS_201605" failed to load/unload and is being skipped due to error: ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3. . imported "BMI"."DW_BILL":"P_DW_BILL_201604"          10.28 MB   26019 rows. . imported "BMI"."DW_BILL":"P_DW_BILL_201612"          10.27 MB   25808 rows. . imported "BMI"."DW_BILL":"P_DW_BILL_201701"          10.36 MB   25917 rows. . imported "BMI"."DW_OPINION_DETAILS":"P_DW_OPINION_DETAILS_201608"  9.801 MB   81796 rows. . imported "BMI"."DW_OPINIONS":"P_DW_OPINIONS_201603"  9.829 MB   22372 rows. . imported "BMI"."DW_BILL":"P_DW_BILL_201511"          9.302 MB   23493 rows. . imported "BMI"."DW_BILL":"P_DW_BILL_201512"          9.404 MB   23770 rows ORA-31693: Table data object "BMI"."DW_BILL":"P_DW_BILL_201606" failed to load/unload and is being skipped due to error: ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" ORA-27037: unable to obtain file status 本次最终导入结果是失败的，在数据同事做数据核对时，表的数目是对的，但是很多表的数据没有导入或导入的数据条目不正确。     实在没有更好的办法，我只能尝试放弃并行，删除bmi用户后，使用原先的dmp文件，做重新impdp导入，意想不到的是竟然成功了，仅仅有几个视图、存储过程编译警告： Import: Release 11.2.0.4.0 - Production on Wed Feb 15 12:41:55 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ;;;  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "ZHUL"."SYS_IMPORT_SCHEMA_01":  zhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log  Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "BMI"."DRUG_INSTRUCTION"                    4.409 GB   76521 rows . . imported "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201604"  665.6 MB 2872334 rows . . imported "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201701"  540.3 MB 2229265 rows ...... . . imported "BMI"."TMP_STA_BILL_PROBLEM_DETAIL"             0 KB       0 rows . . imported "BMI"."TMP_STA_CLASSIFY_ILL_DETAIL"             0 KB       0 rows ORA-39082: Object type PACKAGE_BODY:"BMI"."PKG_SUSPICIOUS_AMOUNT" created with compilation warnings ORA-39082: Object type PACKAGE_BODY:"BMI"."PKG_SYNCDATA_PERMONTH" created with compilation warnings Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "ZHUL"."SYS_IMPORT_SCHEMA_01" completed with 14 error(s) at Wed Feb 15 13:51:00 2017 elapsed 0 01:09:04     总结：可能是底层存储比较差，集群光纤卡性能差，导致oracle的dmp文件在expdp或impdp时（适用dblink远程expdp时，该集群也是报ORA-31693&ORA-31640&ORA-19505&ORA-27037） 出现无法正确写或读取到dmp文件。以后再做类似工作的时候，要考虑并行是否适用。