分类目录归档：数据库

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

发表于 2021 年 9 月 3 日由惜分飞

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

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

发表在 ORACLE 19C, Oracle安装升级 | 标签为 Error 4 opening dom ASM/Self in | 留下评论

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

发表于 2021 年 9 月 1 日由惜分飞

在一次的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)