Category Archives: Oracle

19c 非第一个节点执行root.sh报Error 4 opening dom ASM/Self in 错误

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：19c 非第一个节点执行root.sh报Error 4 opening dom ASM/Self in 错误

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在linux 7.9的系统中安装oracle 19c的rac,在第二个节点执行root.sh报以下错误
Error 4 opening dom ASM/Self in 0x425a470
Domain name to open is ASM/Self
Error 4 opening dom ASM/Self in 0x425a470

[root@rac2 tmp]# /u01/app/19c/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/19c/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/19c/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/rac2/crsconfig/rootcrs_rac2_2021-08-27_10-54-04AM.log
2021/08/27 10:54:07 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2021/08/27 10:54:07 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2021/08/27 10:54:07 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2021/08/27 10:54:07 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2021/08/27 10:54:08 CLSRSC-594: Executing installation step 5 of 19: 'SetupOSD'.
Redirecting to /bin/systemctl restart rsyslog.service
2021/08/27 10:54:08 CLSRSC-594: Executing installation step 6 of 19: 'CheckCRSConfig'.
2021/08/27 10:54:08 CLSRSC-594: Executing installation step 7 of 19: 'SetupLocalGPNP'.
2021/08/27 10:54:09 CLSRSC-594: Executing installation step 8 of 19: 'CreateRootCert'.
2021/08/27 10:54:09 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2021/08/27 10:54:18 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2021/08/27 10:54:18 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2021/08/27 10:54:19 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2021/08/27 10:54:21 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2021/08/27 10:55:06 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2021/08/27 10:55:18 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2021/08/27 10:55:35 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
2021/08/27 10:56:07 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'.
2021/08/27 10:56:08 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'.
2021/08/27 10:56:15 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'.
2021/08/27 10:57:01 CLSRSC-343: Successfully started Oracle Clusterware stack
2021/08/27 10:57:01 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'.
2021/08/27 10:57:10 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'.
2021/08/27 10:57:15 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Error 4 opening dom ASM/Self in 0x425a470
Domain name to open is ASM/Self 
Error 4 opening dom ASM/Self in 0x425a470
[root@rac2 tmp]#

通过查询mos,发现相关文档:19C: While Executing Root.sh on Remote Nodes HIT UNEXPECTED “ERROR 4 OPENING DOM ASM/SELF IN 0x57f7d60″ (Doc ID 2571719.1)
20210903084403

确认是oracle一个bug,在20c版本中修复(20c没有对外发布,应该在21c中修复),这个bug不影响,可以忽略

重现sql执行计划SYS_OP_C2C隐式转换

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：重现sql执行计划SYS_OP_C2C隐式转换

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在一次的sql优化中,遇到SYS_OP_C2C函数,通过分析是由于一个表的varchar2和另外一个表的nvarchar2列进行关联导致,通过以下简单使用进行重现.对于这个问题,如果需要使用index,需要创建SYS_OP_C2C的函数index,或者把列类型修改一致.

SQL> create user xff identified by oracle;

用户已创建。

SQL> grant dba to xff;

授权成功。

SQL> create table xff.t1(id number,name varchar2(100));

表已创建。

SQL> insert into xff.t1 select  object_id,object_name from dba_objects;

已创建 89932 行。

SQL> commit;

提交完成。

SQL> create table xff.t2(id number,name nvarchar2(100));

表已创建。

SQL> insert into xff.t2 select  object_id,object_name from dba_objects;

已创建 89933 行。

SQL> commit;

提交完成。


SQL> exec dbms_stats.gather_table_stats('XFF','T1',cascade=>true);

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('XFF','T2',cascade=>true);

PL/SQL 过程已成功完成。


SQL> set autot on
SQL>  select count(1) from xff.t1, xff.t2 where t1.name=t2.name;

  COUNT(1)
----------
    160752


执行计划
----------------------------------------------------------
Plan hash value: 906334482

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    74 |       |   760   (1)| 00:00:10 |
|   1 |  SORT AGGREGATE     |      |     1 |    74 |       |            |          |
|*  2 |   HASH JOIN         |      |   146K|    10M|  3256K|   760   (1)| 00:00:10 |
|   3 |    TABLE ACCESS FULL| T1   | 89932 |  2195K|       |   137   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   | 89933 |  4303K|       |   205   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."NAME"=SYS_OP_C2C("T1"."NAME"))

还有一种情况也可能发生该转换,比如使用dblink的访问远程库,本地库和远程库字符集不一致.参考:SQL Statements Performed Across Database Links run Slowly. Explain Plan Shows Function SYS_OP_C2C has been Applied to Predicates, and Query uses a Full Table Scan. (Doc ID 2010872.1)

12.1.0.2扩展补丁信息-202107

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：12.1.0.2扩展补丁信息-202107

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

12.1.0.2版本早进入了扩展服务期,这里提供相关信息,便于大家查找

Release Date Version Download link Included in Windows Bundle
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32768233 12.1.0.2.210720 WIN DB BP Patch 32774982
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328635 12.1.0.2.210420 WIN DB BP Patch 32396181
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Database Patch Set Update (DB PSU) Patch 31985579 12.1.0.2.210119 WIN DB BP Patch 32000405
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Database Patch Set Update (DB PSU) Patch 31550110 12.1.0.2.201020 WIN DB BP Patch 31658987
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Database Patch Set Update (DB PSU) Patch 31113348 12.1.0.2.200714 WIN DB BP Patch 31211574
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Database Patch Set Update (DB PSU) Patch 30700212 12.1.0.2.200414 WIN DB BP Patch 30861721
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Database Patch Set Update (DB PSU) Patch 30340202 12.1.0.2.200114 WIN DB BP Patch 30455401
Release Date Version Download link
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32917447
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32495126
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32131261
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31718737
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31305174
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30805421
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30464119
Release Date Version Includes GI PSU Download link
20-Jul-2021 12.1.0.2.210720 Database Proactive Bundle Patch (Jul 2021) 12.1.0.2.210720 Patch 32917362
20-Apr-2021 12.1.0.2.210420 Database Proactive Bundle Patch (Apr 2021) 12.1.0.2.210420 Patch 32518631
19-Jan-2021 12.1.0.2.210119 Database Proactive Bundle Patch (Jan 2021) 12.1.0.2.210119 Patch 32131231
20-Oct-2020 12.1.0.2.201020 Database Proactive Bundle Patch (Oct 2020) 12.1.0.2.201020 Patch 31718813
14-Jul-2020 12.1.0.2.200714 Database Proactive Bundle Patch (Jul 2020) 12.1.0.2.200714 Patch 31307682
14-Apr-2020 12.1.0.2.200414 Database Proactive Bundle Patch (Apr 2020) 12.1.0.2.200414 Recommend Patch 31307682 or later
14-Jan-2020 12.1.0.2.200114 Database Proactive Bundle Patch (Jan 2020) 12.1.0.2.200114 Recommend Patch 31307682 or later
Release Date Version Unix PSU Patch Windows Bundle Patch
20-Jul-2021 12.1.0.2.210720 (Jul 2021) OJVM Component Patch Set Update Patch 32876425 Patch 32905878
20-Apr-2021 12.1.0.2.210420 (Apr 2021) OJVM Component Patch Set Update Patch 32473164 Patch 32427683
19-Jan-2021 12.1.0.2.210119 (Jan 2021) OJVM Component Patch Set Update Patch 32119956 Patch 32142066
20-Oct-2020 12.1.0.2.201020 (Oct 2020) OJVM Component Patch Set Update Patch 31668915 Patch 31740134
14-Jul-2020 12.1.0.2.200714 (Jul 2020) OJVM Component Patch Set Update Patch 31219939 Patch 31465095
14-Apr-2020 12.1.0.2.200414 (Apr 2020) OJVM Component Patch Set Update Patch 30805558 Patch 31037459
14-Jan-2020 12.1.0.2.200114 (Jan 2020) OJVM Component Patch Set Update Patch 30502041 Patch 30671054
Release Date Version Download Link
20-Jul-2021 Combo OJVM PSU 12.1.0.2.210720 and DB PSU 12.1.0.2.210720 Patch 32900172
20-Apr-2021 Combo OJVM PSU 12.1.0.2.210420 and DB PSU 12.1.0.2.210420 Patch 32579074
19-Jan-2021 Combo OJVM PSU 12.1.0.2.210119 and DB PSU 12.1.0.2.210119 Patch 32126886
20-Oct-2020 Combo OJVM PSU 12.1.0.2.201020 and DB PSU 12.1.0.2.201020 Patch 31720729
14-Jul-2020 Combo OJVM PSU 12.1.0.2.200714 and DB PSU 12.1.0.2.200714 Patch 31326396
14-Apr-2020 Combo OJVM PSU 12.1.0.2.200414 and DB PSU 12.1.0.2.200414 Patch 30783658
14-Jan-2020 Combo OJVM PSU 12.1.0.2.200114 and DB PSU 12.1.0.2.200114 Patch 30463684
Release Date Version Download Link
20-Jul-2021 Combo OJVM PSU 12.1.0.2.210720 and DBBP 12.1.0.2.210720 Patch 32900201
20-Apr-2021 Combo OJVM PSU 12.1.0.2.210420 and DBBP 12.1.0.2.210420 Patch 32579100
19-Jan-2021 Combo OJVM PSU 12.1.0.2.210119 and DBBP 12.1.0.2.210119 Patch 32126908
20-Oct-2020 Combo OJVM PSU 12.1.0.2.201020 and DBBP 12.1.0.2.201020 Patch 31720769
14-Jul-2020 Combo OJVM PSU 12.1.0.2.200714 and DBBP 12.1.0.2.200714 Patch 31326402
14-Apr-2020 Combo OJVM PSU 12.1.0.2.200414 and DBBP 12.1.0.2.200414 Recommend Patch 31326402 or later
14-Jan-2020 Combo OJVM PSU 12.1.0.2.200114 and DBBP 12.1.0.2.200114 Recommend Patch 31326402 or later
Release Date Version Download Link
20-Jul-2021 Combo OJVM PSU 12.1.0.2.210720 and GI PSU 12.1.0.2.210720 Patch 32900185
20-Apr-2021 Combo OJVM PSU 12.1.0.2.210420 and GI PSU 12.1.0.2.210420 Patch 32579077
19-Jan-2021 Combo OJVM PSU 12.1.0.2.210119 and GI PSU 12.1.0.2.210119 Patch 32126899
20-Oct-2020 Combo OJVM PSU 12.1.0.2.201020 and GI PSU 12.1.0.2.201020 Patch 31720761
14-Jul-2020 Combo OJVM PSU 12.1.0.2.200714 and GI PSU 12.1.0.2.200714 Patch 31326400
14-Apr-2020 Combo OJVM PSU 12.1.0.2.200414 and GI PSU 12.1.0.2.200414 Patch 30783882
14-Jan-2020 Combo OJVM PSU 12.1.0.2.200114 and GI PSU 12.1.0.2.200114 Patch 30463691

11.2.0.4扩展补丁信息-202107

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：11.2.0.4扩展补丁信息-202107

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

11.2.0.4版本早进入了扩展服务期,这里提供相关信息,便于大家查找

Release Date Version Download link Included in Windows Bundle Comments
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32758711 Patch 32775108 Linux x86-64 is Available. Bundles for other Platforms ETA is 13-Aug-2021.
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328626 Patch 32392141
19-Jan-2021 11.2.0.4.210119 (Jan 2021) Database Patch Set Update (DB PSU) Patch 31983472 Patch 32003403
20-Oct-2020 11.2.0.4.201020 (Oct 2020) Database Patch Set Update (DB PSU) Patch 31537677 Patch 31659823
Release Date Version Download link
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32917428
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32495145
19-Jan-2021 11.2.0.4.210119 (Jan 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32131250
20-Oct-2020 11.2.0.4.201020 (Oct 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31718723
Release Date Version Unix PSU Patch Windows Bundle Patch Comments
20-Jul-2021 11.2.0.4.210720 (Jul2021) OJVM Component Patch Set Update Patch 32876451 Patch 32905855 Patches are delayed. ETA is 13-Aug-2021.
20-Apr-2021 11.2.0.4.210420 (Apr2021) OJVM Component Patch Set Update Patch 32671980 Patch 32428494
19-Jan-2021 11.2.0.4.210119 (Jan2021) OJVM Component Patch Set Update ** Patch 32145687
20-Oct-2020 11.2.0.4.201020 (Oct 2020) OJVM Component Patch Set Update Patch 31668908 Patch 31740195
Release Date Version Download Link Comments
20-Jul-2021 Combo OJVM PSU 11.2.0.4.210720 and DB PSU 11.2.0.4.210720 Patch 32900228 Patch is delayed. ETA is 13-Aug-2021.
20-Apr-2021 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.210420 Patch 32579111
19-Jan-2021 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.210119 Patch 32126939 
20-Oct-2020 Combo OJVM PSU 11.2.0.4.201020 and DB PSU 11.2.0.4.201020 Patch 31720776
Release Date Version Download Link Comments
20-Jul-2021 Combo OJVM PSU 11.2.0.4.210720 and GI PSU 11.2.0.4.210720 Patch 32900245 Patch is delayed. ETA is 13-Aug-2021.
20-Apr-2021 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.210420 Patch 32579106
19-Jan-2021 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.210119 Patch 32126942
20-Oct-2020 Combo OJVM PSU 11.2.0.4.201020 and GI PSU 11.2.0.4.201020 Patch 31720783

安全漏洞扫描结果让人无语

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：安全漏洞扫描结果让人无语

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对于安全公司对oracle漏洞扫描的结果的准确性一直不太认可,前几天有客户linux 平台11.2.0.4的数据库打上了210420的patch,结果xx安全厂商扫描之后,依旧有大量安全漏洞,我随机对其中的两个高危漏洞进行分析,结果发现主要有:已经修复的漏洞继续报,漏洞对应数据库版本和实际数据库不符
数据库库的补丁信息
20210811190805

已经修复的漏洞依旧报
20210811190838
20210811190844
这里比较明显CVE-2018-3110漏洞oracle在18年7月份的补丁中已经修复,而我们这个是21年04月的补丁包含了该patch,已经修复了该问题
直接报漏洞和数据库版本不匹配
20210811190828
CVE-2016-1000031这个安全漏洞根本只是在12.2+版本中才有,他直接给客户的11204版本提示高危漏洞