联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
做一个oracle数据库被加密，所有数据文件名称全部被重命名,类似
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到一起sql server数据库被勒索病毒加密恢复请求
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接到一个客户的oracle数据文件被加密的恢复请求,文件被加密为扩展名为:.id[76B8C076-3009].[decrypt20@firemail.cc].eking,通过底层分析,确认该文件被加密破坏较少
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：19c sqlplus / as sysdba 登录慢分析
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
sqlplus / as sysdba登录非常慢
[oracle@xifenfei trace]$ date Fri Oct 29 00:04:27 CST 2021 [oracle@xifenfei trace]$ sqlplus / as sysdba<< EOF > exit; > EOF SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 29 00:04:28 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@xifenfei trace]$ date Fri Oct 29 00:04:49 CST 2021
sqlpus 一个简单的登录+退出使用了22秒,严重的慢,通过strace分析发现访问以下两个ip，但是10.11.0.41非常慢,10.13.0.41很快，并且通过一些信息53端口,判断可能是dns服务器的ip
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("10.13.0.41")}, 16) = 0
connect(12, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("10.11.0.41")}, 16) = 0
进一步确认,确认是该服务器配置的dns服务器
[root@xifenfei ~]# cat /etc/resolv.conf # Generated by NetworkManager nameserver 10.13.0.41 nameserver 10.11.0.41
对这两个ip的连通性进行测试
[root@xifenfei ~]# ping 10.13.0.41 PING 10.13.0.41 (10.13.0.41) 56(84) bytes of data. 64 bytes from 10.13.0.41: icmp_seq=1 ttl=126 time=0.616 ms ^C --- 10.13.0.41 ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.616/0.616/0.616/0.000 ms [root@xifenfei ~]# ping 10.11.0.41 PING 10.11.0.41 (10.11.0.41) 56(84) bytes of data. ^C --- 10.11.0.41 ping statistics --- 3 packets transmitted, 0 received, 100% packet loss, time 2063ms [root@xifenfei ~]#
确认10.11.0.41 ip不通,对于此类问题最快的解决方案就是除掉不可使用的dns服务器,然后测试登录和退出时间
[oracle@szd18dbts01 trace]$ date Fri Oct 29 00:21:12 CST 2021 [oracle@szd18dbts01 trace]$ sqlplus / as sysdba<< EOF > exit; > EOF SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 29 00:21:12 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. date Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@szd18dbts01 trace]$ date Fri Oct 29 00:21:13 CST 2021
通过除掉不可用的dns服务器ip之后,测试时间为1s,恢复正常
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近有一些朋友咨询了几种oracle数据库被加密的勒索病毒，我们都可以通过工具修复实现数据库直接open,数据使用exp/expdp导出,实现数据近似完美恢复,业务直接测试正常,远比各种工具直接导出数据效果要好很多.比如以下几种:
.id[A6B00388-2930].[Ransomwaree2020@cock.li].eking
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
|
||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| 19.0.0.0 | |||
| Description | Database Update | GI Update | Windows Bundle Patch |
| OCT2021(19.13.0.0.0) | 33192793 | 33182768 | 33155330 |
| JUL2021 (19.12.0.0.0) | 32904851 | 32895426 | 32832237 |
| APR2021 (19.11.0.0.0) | 32545013 | 32545008 | 32409154 |
| JAN2021 (19.10.0.0.0) | 32218454 | 32226239 | 32062765 |
| OCT2020 (19.9.0.0.0) | 31771877 | 31750108 | 31719903 |
| JUL2020 (19.8.0.0.0) | 31281355 | 31305339 | 31247621 |
| APR2020 (19.7.0.0.0) | 30869156 | 30899722 | 30901317 |
| JAN2020 (19.6.0.0.0) | 30557433 | 30501910 | 30445947 |
| OCT2019 (19.5.0.0.0) | 30125133 | 30116789 | 30151705 |
| JUL2019 (19.4.0.0.0) | 29834717 | 29708769 | NA |
| APR2019 (19.3.0.0.0) | 29517242 | 29517302 | NA |
| 18.0.0.0 | |||
| Description | OJVM Update | OJVM + DB Update | OJVM + GI Update |
| APR2021 (18.14.0.0.210420) | 32552752 | 32579022 | 32579024 |
| JAN2021 (18.13.0.0.210119) | 32119939 | 32126855 | 32126862 |
| OCT2020 (18.12.0.0.201020) | 31668892 | 31720435 | 31720457 |
| JUL2020 (18.11.0.0.200714) | 31219909 | 31326374 | 31326376 |
| APR2020 (18.10.0.0.200414) | 30805598 | 30783603 | 30783607 |
| JAN2020 (18.9.0.0.200114) | 30501926 | 30463620 | 30463635 |
| OCT2019 (18.8.0.0.191015) | 30133603 | 30133203 | 30133246 |
| JUL2091 (18.7.0.0.190716) | 29774410 | 29699112 | 29699160 |
| APR2019 (18.6.0.0.190416) | 29249584 | 29249695 | 29251992 |
| JAN2019 (18.5.0.0.190115) | 28790647 | 28980087 | 28980105 |
| OCT2018 (18.4.0.0.181016) | 28502229 | 28689117 | 28689122 |
| JUL2018 (18.3.0.0.180717) | 27923415 | 28317326 | 28317346 |
| APR2018 (18.2.0.0.180417) | 27636900 | 27726465 | 27726470 |
| 18.0.0.0 | |||
| Description | Database Update | GI Update | Windows Bundle Patch |
| APR2021 (18.14.0.0.0) | 32524155 | 32524152 | 32438481 |
| JAN2021 (18.13.0.0.0) | 32204699 | 32226219 | 32062760 |
| OCT2020 (18.12.0.0.0) | 31730250 | 31748523 | 31629682 |
| JUL2020 (18.11.0.0.0) | 31308624 | 31305362 | 31247612 |
| APR2020 (18.10.0.0.0) | 30872794 | 30899645 | 30901451 |
| JAN2020 (18.9.0.0.0) | 30480385 | 30480702 | 30445951 |
| OCT2019 (18.8.0.0.0) | 30112122 | 30116795 | 30150321 |
| JUL2019 (18.7.0.0.0) | 29757256 | 29708703 | 29859180 |
| APR2019 (18.6.0.0.0) | 29301631 | 29301682 | 29589622 |
| JAN2019 (18.5.0.0.0) | 28822489 | 28828717 | 29124511 |
| OCT2018 (18.4.0.0.0) | 28655784 | 28659165 | NA |
| JUL2018 (18.3.0.0.0) | 28090523 | 28096386 | NA |
| APR2018 (18.2.0.0.0) | 27676517 | 27681568 | NA |
| 12.2.0.1 | ||||
| Description | OJVM Update | OJVM Windows Bundle Patch | Combo OJVM + DB Update | Combo OJVM + GI Update |
| OCT2021 (12.2.0.1.211019) | 33192662 | 33248852 | 33248521 | 33248546 |
| JUL2021 (12.2.0.1.210720) | 32876409 | 32905896 | 32900144 | 32900156 |
| APR2021 (12.2.0.1.210420) | 32473172 | 32427674 | 32579049 | 32579057 |
| JAN2021 (12.2.0.1.210119) | 32119931 | 32142294 | 32126871 | 32226491 |
| OCT2020 (12.2.0.1.201020) | 31668898 | 31740064 | 31720473 | 31720486 |
| JUL2020 (12.2.0.1.200714) | 31219919 | 31465105 | 31326379 | 31326390 |
| APR2020 (12.2.0.1.200414) | 30805580 | 31035002 | 30783641 | 30783652 |
| JAN2020 (12.2.0.1.200114) | 30502018 | 30525838 | 30463660 | 30463673 |
| OCT2019 (12.2.0.1.191015) | 30133625 | 30268021 | 30133374 | 30133386 |
| JUL2019 (12.2.0.1.190716) | 29774415 | 29837425 | 29699168 | 29699173 |
| APR2019 (12.2.0.1.190416) | 29249637 | 29281550 | 29252035 | 29252072 |
| JAN2019 (12.2.0.1.190115) | 28790651 | 28994068 | 28980102 | 28980109 |
| NOV2018 (12.2.0.1.181130) | NA | 28412314 | NA | NA |
| OCT2018 (12.2.0.1.181016) | 28440725 | 28412312 | 28689128 | 28689130 |
| JUL2018 (12.2.0.1.180717) | 27923353 | 28135129 | 28317292 | 28317269 |
| APR2018 (12.2.0.1.180417) | 27475613 | 27650410 | 27726453 | 27726454 |
| JAN2018 (12.2.0.1.180116) | 27001739 | 27162975 | 27010695 | 27010711 |
| OCT2017 (12.2.0.1.171017) | 26635944 | 26792369 | 26636004 | 26636246 |
| AUG2017 (12.2.0.1.170814) | N/A | 26565082 | 26550033 | 26550314 |
| JUL2017 (12.2.0.1.170718) | 25811364 | 26182467 | 26146314 | 26146318 |
| 12.2.0.1 | |||
| Description | Database Update | GI Update | Windows Bundle Patch |
| OCT2021 (12.2.0.1.211019) | 33261817 | 33290750 | 33174380 |
| JUL2021 (12.2.0.1.210720) | 32916808 | 32928749 | 32775037 |
| APR2021 (12.2.0.1.210420) | 32507738 | 32540149 | 32392089 |
| JAN2021 (12.2.0.1.210119) | 32228578 | 32226491 | 31987852 |
| OCT2020 (12.2.0.1.201020) | 31741641 | 31750094 | 31654782 |
| JUL2020 (12.2.0.1.200714) | 31312468 | 31305382 | 31210848 |
| APR2020 (12.2.0.1.200414) | 30886680 | 30920127 | 30861472 |
| JAN2020 (12.2.0.1.200114) | 30593149 | 30501932 | 30446296 |
| OCT2019 (12.2.0.1.191015) | 30138470 | 30116802 | 30150416 |
| JUL2019 (12.2.0.1.190716) | 29757449 | 29708720 | 29832062 |
| APR2019 (12.2.0.1.190416) | 29314339 | 29301687 | 29394003 |
| JAN2019 (12.2.0.1.190115) | 28822515 | 28828733 | 28810696 |
| NOV2018 (12.2.0.1.181130) | NA | NA | 28810550 (64bit) |
| OCT2018 (12.2.0.1.181016) | 28662603 | 28714316 | 28574555 |
| JUL2018 (12.2.0.1.180717) | 28163133 | NA | 27937914 |
| APR2018 (12.2.0.1.180417) | 27674384 | 27468969 | 27426753 |
| JAN2018 (12.2.0.1.180116) | 27105253 | 27100009 | 27162931 |
| NOV2017 (12.2.0.1.171121) | NA | 27010638 | NA |
| OCT2017 (12.2.0.1.171017) | 26710464 | 26737266 | 26758841 |
| AUG2017 (12.2.0.1.170814) | 26609817 | 26610291 | 26204214 |
| JUL2017 (12.2.0.1.170718) | 26123830 | 26133434 | 26204212 |
参考:Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有oracle数据库被加密扩展名为:.[77C81F29].[Evilminded@privatemail.com].makop
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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)
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在一次的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)
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在oracle 未正式对外公布的20c版本中已经提及在该版本及其以后版本中不再支持no-cdb模式
