prm dul恢复oracle数据库数据表 中文最简易说明

 

 

 

软件下载地址: https://zcdn.parnassusdata.com/DUL5108.zip

本软件为JAVA编写,可用于WINDOWS LINUX(红帽,Centos,Ubuntu) AIX SOLARIS HPUX

 

但必须先安装 JDK 1.8 !!!

 

JDK 下载地址:

 

LINUX X86: http://zcdn.parnassusdata.com/jdk-8u201-linux-i586.rpm 32位LIUNX
LIUNX X86-64: http://zcdn.parnassusdata.com/jdk-8u91-linux-x64.rpm 64位LIUNX

Windows X86: http://zcdn.parnassusdata.com/jdk-8u201-windows-i586.exe 32位Windows
Windows X86-64: http://zcdn.parnassusdata.com/jdk-8u181-windows-x64.exe 64位Windows

目前的操作系统,基本都是64位的,推荐用64位JDK!!

确认JAVA 版本,打开命令行,在windows上是cmd

java -version

解压DUL5108.zip

Windows双击prm.bat
Linux 在满足X-Windows的条件下运行sh prm.sh,可以安装XSHELL Xmanager等远程图形化客户端:
xmanager 下载地址:
xmanager 4 https://zcdn.askmaclean.com/Xme4.exe
xmanager 6 https://zcdn.askmaclean.com/Xme6.exe

选择字典模式,
加入所有数据文件即可!! 必须加入所有你找的到的数据文件,所有的属于整个数据库的数据文件!!
不要加入其他数据库的数据文件!!

所有数据文件!!
所有数据文件!!
不要只加入SYSTEM01.DBF!!
不要只加入SYSTEM01.DBF!!
不要只加入你认为有问题的数据文件!!
不要只加入你认为有问题的数据文件!!
所有数据文件!!
所有数据文件!!
必须包含 SYSTEM01.DBF!!
必须包含 SYSTEM01.DBF!!
必须包含 SYSTEM01.DBF!!

如何找到所有数据文件???
在命令行中
sqlplus / as sysdba
conn / as sysdba
shutdown immediate;
startup mount;
select name from v$datafile;

最简单模式下不用选择任何参数,只要加入数据文件!!

ctrl+A 选择目录下所有dbf/ora文件!!

双击查看表中的样例数据,
这只是样例数据!!不是全部数据!!
这只是样例数据!!不是全部数据!!
这只是样例数据!!不是全部数据!!

找到你要的用户名,下面查看重要表是否有数据!!
你看到的只是样例数据!!不是全部数据!!
你看到的只是样例数据!!不是全部数据!!

社区版最多抽1万行,可能会略多于1万行!!!
社区版最多抽1万行,可能会略多于1万行!!!
社区版最多抽1万行,可能会略多于1万行!!!
社区版最多抽1万行,可能会略多于1万行!!!

右键unload 数据抽取到文件。
抽取完 就给你 这个文件的路径了!!!
抽取完 就给你 这个文件的路径了!!!
抽取完 就给你 这个文件的路径了!!!
按照路径去 就能看到这个文件!!!!

建议使用databridge数据搭桥模式!!
建议使用databridge数据搭桥模式!!
建议使用databridge数据搭桥模式!!
不推荐用 unload 抽取模式!!

数据搭桥模式,在新数据库/目标数据库新建一个用户,
create user pd1 identified by oracle;
grant dba to pd1;

注意给dba权限!!!!
注意给dba权限!!!!
注意给dba权限!!!!

没有新数据库,那就自己建一个!! 用dbca命令,新建一个!!
注意字符集要和原来数据库一样!!!!

注意目标数据库监听是否打开了???????
注意目标数据库监听是否打开了???????
确保 监听打开了, 服务注册了!!!!
确保 监听打开了, 服务注册了!!!!

选择你想要的表空间!!!

可以数据搭桥整个用户,整个用户,整个用户下的所有表!!!!
不是只能一张张表抽!!!
不是只能一张张表抽!!!
可以数据搭桥整个用户,整个用户,整个用户下的所有表!!!!

点用户名,右键,数据搭桥整个用户下所有表!!!

可以将用户的表结构,索引,主键约束,视图,存储过程,包,触发器,sequence序列导出成ddl文本!!
可以将用户的表结构,索引,主键约束,视图,存储过程,包,触发器,sequence序列导出成ddl文本!!
可以将用户的表结构,索引,主键约束,视图,存储过程,包,触发器,sequence序列导出成ddl文本!!

右键用户 ,选择一个可用的数据搭桥用户,最后给你一个文本文件!!!
右键用户 ,选择一个可用的数据搭桥用户,最后给你一个文本文件!!!
右键用户 ,选择一个可用的数据搭桥用户,最后给你一个文本文件!!!

导出ddl功能,要购买了企业版授权,才能生效!!!!
导出ddl功能,要购买了企业版授权,才能生效!!!!
导出ddl功能,要购买了企业版授权,才能生效!!!!

数据搭桥默认将varchar字段,修改为varchar(4000),以保证一定可以插入成功!!!
如果不希望使用varchar(4000),可以用上一步(导出表结构ddl)生成的建表语句先建表!!
如果不希望使用varchar(4000),可以用上一步(导出表结构ddl)生成的建表语句先建表!!
数据搭桥会直接插入数据到目标表,而不自己创建表!!!!

如果还有问题可以找我们 电话 13764045638 QQ 47079569 !!!

使用PRM DUL软件快速恢复被勒索病毒 恶意软件加密的ORACLE数据库数据文件

为什么可以恢复这些被加密的ORACLE数据库数据文件?

因为这些文件一般比较大,超过300MB。导致恶意加密软件要加密它们时要花费大量时间和CPU,因此这些勒索软件一般仅部分加密其内容。

通过PRM-DUL的强大功能,可以恢复出其中绝大部分未被加密的内容。

PRM DUL download: https://zcdn.parnassusdata.com/DUL5108.zip

 

 

 

 

 

常用软件下载地址

putty                                        https://zcdn.askmaclean.com/putty.exe
mobaxterm                              https://zcdn.askmaclean.com/MobaXterm_Portable_v20.2.zip
teamviewer 14                        https://zcdn.askmaclean.com/tv14.exe
xmanager 4                              https://zcdn.askmaclean.com/Xme4.exe
xmanager 6                             https://zcdn.askmaclean.com/Xme6.exe
cmder                                         https://zcdn.askmaclean.com/cmder.zip
sqldeveloper                             https://zcdn.askmaclean.com/sqldeveloper-19.1.0.094.2042-no-jre.zip
chrome win                                https://zcdn.askmaclean.com/chrome-win.zip

bbed linux                              https://zcdn.askmaclean.com/linux%20bbed.zip

bbed windows                          https://zcdn.askmaclean.com/9ibbedfull.zip

everything                          https://zcdn.askmaclean.com/Everything.exe

notepad++                        https://zcdn.askmaclean.com/npp.7.3.1.Installer.exe

touch.exe                                https://zcdn.askmaclean.com/touch.exe

winscp                                  https://zcdn.askmaclean.com/winscp564setup.exe

vncviewer                         https://zcdn.askmaclean.com/vncviewer.exe

hxd                                        https://zcdn.askmaclean.com/HxD.exe

mysqlscan                            https://zcdn.askmaclean.com/mysqlscan

xshell                                    https://zcdn.askmaclean.com/Xshell-6.0.0197r.exe

 

PRM DUL  https://zcdn.parnassusdata.com/DUL5108.zip

PRM DUL latest: https://zcdn.parnassusdata.com/DUL5108rc12_java.zip

OpenJDK windows x86-64 : https://zcdn.parnassusdata.com/java-1.8.0-openjdk-1.8.0.232-1.b09.ojdkbuild.windows.x86_64.msi

Amazon JDK windows x86-64: https://zcdn.parnassusdata.com/amazon-corretto-8.232.09.1-windows-x64.msi

Windows X86-64: http://zcdn.parnassusdata.com/jdk-8u181-windows-x64.exe

LIUNX X86-64: http://zcdn.parnassusdata.com/jdk-8u91-linux-x64.rpm

 

 

 

JDK

 

JDK 1.8 DOWNLOAD URL:
AIX JAVA SDK: http://zcdn.parnassusdata.com/Java8_64.sdk.8.0.0.527.tar.gz
AIX JRE: http://zcdn.parnassusdata.com/Java8_64.jre.8.0.0.527.tar.gz
LINUX X86: http://zcdn.parnassusdata.com/jdk-8u201-linux-i586.rpm
LIUNX X86-64: http://zcdn.parnassusdata.com/jdk-8u91-linux-x64.rpm
Windows X86: http://zcdn.parnassusdata.com/jdk-8u201-windows-i586.exe
Windows X86-64: http://zcdn.parnassusdata.com/jdk-8u181-windows-x64.exe
HPUX Itanium: http://zcdn.parnassusdata.com/Itanium_JDK_8.0.16_December_2018_Z7550-63506_java8_18016_ia.depot


AIX JDK 6 https://zcdn.askmaclean.com/Java6_64.sdk.6.0.0.655.tar

AIX JDK 8

https://zcdn.askmaclean.com/Java8_64.jre.8.0.0.610.tar.gz
https://zcdn.askmaclean.com/Java8_64.sdk.8.0.0.610.tar.gz

squirrel-sql-3.9.0: http://zcdn.parnassusdata.com/squirrel-sql-3.9.0-standard.jar
db-derby-10.14.2.0: http://zcdn.parnassusdata.com/db-derby-10.14.2.0-bin.zip

Derby IJ Connection setting:

Windows:
C:\> set DERBY_INSTALL=C:\Apache\db-derby-10.14.2.0-bin
C:\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar;.
C:\> cd %DERBY_INSTALL%\bin
C:\Apache\db-derby-10.14.2.0-bin\bin> setEmbeddedCP.bat

LINUX/UNIX
$ export DERBY_INSTALL=/opt/Apache/db-derby-10.14.2.0-bin
$ export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar:.
$ cd $DERBY_INSTALL/bin
$ . setEmbeddedCP


java org.apache.derby.tools.sysinfo

java org.apache.derby.tools.ij

connect 'jdbc:derby:\dbinfo\parnassus_dbinfo_DB_20190127144050';
show schemas;
show tables;



clash for windows:   https://zcdn.askmaclean.com/Clash.for.Windows-0.6.0-win.zip
surge :              https://zcdn.askmaclean.com/Surge-latest.zip

prm dul恢复oracle数据库最简模式                                              http://zcdn.parnassusdata.com/prm%20dul%20recover%20oracle%20database%20easiest%20way.mp4
prm dul使用数据搭桥传输Oracle表数据                                          http://zcdn.parnassusdata.com/prm%20dul%20databridge%20transfer%20oracle%20table.mp4
prm dul在损坏数据库使用exportddl导出建表语句、索引、存储过程、函数等代码      http://zcdn.parnassusdata.com/prm%20dul%20export%20ddl%20from%20corrupted%20oracle%20database.mp4
prm dul恢复oracle中被delete的数据                                            http://zcdn.parnassusdata.com/prm%20dul%20recover%20oracle%20deleted%20rows.mp4
prm dul恢复oracle中被truncate的数据                                          http://zcdn.parnassusdata.com/prm%20dul%20recover%20oracle%20truncated%20table.mp4
prm dul用户级别的数据搭桥                                                    http://zcdn.parnassusdata.com/prm%20dul%20schema%20level%20databridge.mp4
prm dul在ASM存储情况下的最简模式                                             http://zcdn.parnassusdata.com/prm%20dul%20easiest%20way%20with%20ASM%20storage.mp4
prm dul恢复oracle中被drop掉的表                                              http://zcdn.parnassusdata.com/prm%20dul%20recover%20oracle%20dropped%20table.mp4
prm dul恢复oracle 12c以后可拔插数据库PDB/CDB中的数据                         http://zcdn.parnassusdata.com/prm%20dul%20work%20with%20oracle%2012c%20pdb%20pluggable%20database%20container%20database.mp4
prm dul恢复被恶意加密的Oracle数据文件                                        http://zcdn.parnassusdata.com/prm%20dul%20recover%20malware%20ransomware%20corrupted%20oracle%20datafile.mp4
prmscan oracle数据块重组恢复解决方案                                         http://zcdn.parnassusdata.com/prmscan.mp4




666和{Killserver@protonmail.com}KSR后缀加密病毒的Oracle数据文件DBF恢复

近期有大量勒索加密病毒造成ORACLE数据库损坏的案例,常见的有 Hephaestus666和Aphrodite666  以及 {Killserver@protonmail.com}KSR 和 完全无意义文件名的qxlozcf :

 

 

 
λ ls -ltr
-rw-r--r-- 1 st 197121 10737427200 7月  19 05:39 MAILBOX01.DBF.Hephaestus666
-rw-r--r-- 1 st 197121  5368718080 7月  19 05:39 SF_HISTORY01.DBF.Hephaestus666
-rw-r--r-- 1 st 197121  5368718080 7月  19 05:39 SF_MBP01.DBF.Hephaestus666
-rw-r--r-- 1 st 197121 32212263680 7月  19 05:39 SYSTEM01.DBF.Hephaestus666
-rw-r--r-- 1 st 197121  5368718080 7月  19 05:39 USERS01.DBF.Hephaestus666
-rw-r--r-- 1 st 197121 32212263680 7月  19 05:39 USR01.DBF.Hephaestus666


λ ls -ltr
total 27626584
-rw-r--r-- 1 st 197121  9017762736 7月  25 07:04 'MHSOFT.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121 15544099760 7月  25 07:04 'MHSOFT02.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121   104866736 7月  25 07:04 'MHTEMP.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121  1468015536 7月  25 07:04 'STRNEWEMR.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121  1310729136 7月  25 07:04 'SYSAUX01.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121   838869936 7月  25 07:04 'SYSTEM01.DBF.{Killserver@protonmail.com}KSR'
-rw-r--r-- 1 st 197121     5252016 7月  25 07:04 'USERS01.DBF.{Killserver@protonmail.com}KSR'


λ ls -l *666
-rw-r--r-- 1 st 197121  73409280 8月   1 05:32 GAA8220120228_T.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:33 GADATA0004_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:33 GADATA0004_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:34 GADATA0006_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:34 GADATA0006_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:35 GADATA0008_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:35 GADATA0008_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 352330496 8月   1 05:36 GADATA0010_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:37 GADATA0010_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:38 GADATA0012_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:39 GADATA0012_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:40 GADATA0014_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:41 GADATA0014_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:42 GADATA0016_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:42 GADATA0016_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:43 GADATA0018_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:43 GADATA0018_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:44 GADATA0020_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:45 GADATA0020_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:46 GADATA0025_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:46 GADATA0025_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:47 GADATA0027_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:48 GADATA0027_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:48 GADATA0028_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:49 GADATA0028_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:50 GADATA0030_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:51 GADATA0030_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:51 GADATA0032_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:52 GADATA0032_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:53 GADATA0034_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:54 GADATA0034_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:55 GADATA0036_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:55 GADATA0036_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 301998848 8月   1 05:56 GADATA0038_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:57 GADATA0038_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:58 GADATA0040_I.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 201335552 8月   1 05:58 GADATA0040_T.DAT.Aphrodite666
-rw-r--r-- 1 st 197121 251667200 8月   1 05:59 GADATA0044_I.DAT.Aphrodite666



λ ls -ltr
total 17428672
-rw-r--r-- 1 st 197121  1069556252 12月 29  2018 SYSTEM01.DBF.qxlozcf
-rw-r--r-- 1 st 197121 15728648732 12月 29  2018 APF_MES_YW_DATA.DBF.qxlozcf
-rw-r--r-- 1 st 197121  1048584732 12月 29  2018 APF_MES_YW_DATA_MM.DBF.qxlozcf

以上这些加密类病毒破坏的Oracle数据文件均可以通过PRM DUL 软件恢复其中的数据,

为什么可以恢复这些被加密的ORACLE数据库数据文件?

因为这些文件一般比较大,超过300MB。导致恶意加密软件要加密它们时要花费大量时间和CPU,因此这些勒索软件一般仅部分加密其内容。

通过PRM-DUL的强大功能,可以恢复出其中绝大部分未被加密的内容。

PRM DUL download: https://zcdn.parnassusdata.com/DUL5108.zip

恢复流程较为复杂,可以参考如下视频:

git npm brew yarn 配置proxy



all

export https_proxy=http://127.0.0.1:6152
export http_proxy=http://127.0.0.1:6152
export all_proxy=socks5://127.0.0.1:6153




git npm brew yarn 配置代理

//git列出
git config --global http.proxy

//git设置
git config --global http.proxy 'socks5://127.0.0.1:6153'

//git取消设置

git config --global --unset http.proxy



npm

//列出所有配置
npm config ls -l

//列出
npm config list

//设置

npm config set https-proxy http://127.0.0.1:6152
npm config set proxy http://127.0.0.1:6152



//npm 取消配置
npm config list delete https-proxy
npm config set proxy


//yarn 列出

yarn config list

//yarn 设置 

yarn config set proxy http://127.0.0.1:6152
yarn config set https-proxy http://127.0.0.1:6152


//yarn 取消配置

yarn config delete proxy
yarn config delete https-proxy



//brew 列出

all_proxy=socks5://127.0.0.1:6153 brew install mysql





2019年6月的冲绳之旅

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

关于MySQL的doublewrite与Oracle中类似的机制

大致看了下mysql 的double write buffer概念 :

So why doublewrite is needed ? It is needed to archive data safety in case of partial page writes. Innodb does not log full pages to the log files, but uses what is called “physiological” logging which means log records contain page number for the operation as well as operation data (ie update the row) and log sequence information. Such logging structure is geat as it require less data to be written to the log, however it requires pages to be internally consistent. It does not matter which page version it is – it could be “current” version in which case Innodb will skip page upate operation or “former” in which case Innodb will perform update. If page is inconsistent recovery can’t proceed.

Now lets talk a bit about partial page writes – what are they and why are they happening. Partial page writes is when page write request submited to OS completes only partially. For example out of 16K Innodb page only first 4KB are updated and other parts remain in their former state. Most typically partial page writes happen when power failure happens. It also can happen on OS crash – there is a chance operation system will split your 16K write into several writes and failure happens just between their execution. Reasons for splitting could be file fragmentation – most file systems use 4K block sizes by default so 16K could use more than one fragment. Also if software RAID is used page may come on the stripe border requiring multiple IO requests. Same happens with Hardware RAID on power failure if it does not have battery backed up cache. If there is single write issued to the disk itself it should be in theory completed even if power goes down as there should be enough power accomulated inside the drive to complete it. I honestly do not know if this is always the case – it is hard to check as it is not the only reason for partial page writes. I just know they tend to happen and before Innodb doublewirite was implemented I had couple of data corruptions due to it.

So how does double write works ? You can think about it as about one more short term log file allocated inside Innodb tablespace – it contains space for 100 pages. When Innodb flushes pages from Innodb buffer pool it does so by multiple pages. So several pages will be written to double write buffer (sequentially), fsync() called to ensure they make it to the disk, then pages written to their real location and fsync() called the second time. Now on recovery Innodb checks doublewrite buffer contents and pages in their original location. If page is inconsistent in double write buffer it is simply discarded, if it is inconsistent in the tablespace it is recovered from double write buffer.

 

我是这样认为的 其实oracle中也有写 partial page(block)的可能,只不过 o的redo和controlfile 检查点比较严谨, 不在需要这样一个区域了。

对于 partial page(block) 的前滚,o也有这样的情况,例如 一个已经写好的块, 增量检查点 未必更新了控制文件, o认为 这个块不是最新的, 那么o也会同样的再写一次 ,即便这个块已经是最新的。 这种机制在oracle中称为resilver write 镀银写。

 

 

Most data blocks are changed via redo and written by DBWR. These writes are coordinated by cache locks that insure there is only one current dirty buffer for any given block. If DBWR dies for any reason, its instance will also die. It will be necessary to do some form of recovery applying redo to reconstruct the blocks that were in the cache at the time of the failure. This recovery will have to either read or write any block that is both modified by redo, and might have been in the middle of a write when DBWR died. Thus, these blocks are a superset of the blocks that Oracle must resilver if it is responsible for resilvering. Hence, during recovery, Oracle will rewrite every block it examines in files it is responsible for resilvering. Since media recovery may be used to recover changes lost when an instance dies, Oracle must also resilver when doing media recovery.
Data blocks used for sorting are modified without generating any redo. They are still written by DBWR. These blocks are never read by any process other than the one doing the sort, and if its DBWR dies then it too will die. Thus it is not important that these blocks become resilvered.

 

 

mysql里叫double write, o 里叫 resilver write。 目的应该是一样的,机制不同,mysql 里知名度很高 可能是因为其可以关闭。 o里面就没人研究能不能关这个了。

 

 

补充:  double write的核心意义在于减少partial page writes的可能性,特别是对于instance crash导致的partially written;其原理是依赖于两次先后的fsync()来保证实际的数据落盘。

 

对于Oracle而言,确实是可能存在partially written block的现象的,这种现象o中体现为fractured block块断裂 : A fractured block is one that was partially written to disk, leaving a previous version on part of the block.

 

对于mysql中的double write,其也只能减少partial page writes的可能性,是并不能杜绝的,因为其还是依赖于fsync()。假设我们有一个调皮的存储子系统,对于第一次的fsync()尽职尽责地完成了,而对于第二次的fsync()只完成了一半就成功返回了。此时如果没有发生instance crash,那么也就没有对应的crash recovery发生。MySQL应当也不会去检查double write buffer和对应的page。那么还是会造成partially written。或者在MySQL Recovery情况下检测double write buffer和page内容是读取了文件系统或存储系统内被更新的缓存,那么还是会骗过这次检测。

double write更多的是为了保护crash recovery情况下可能发生的partially written;并不能完全杜绝存储子系统去“欺骗”它。

不管是Oracle还是MySQL,基本都基于现代存储子系统是基本可靠的这一点出来来设计。

对于Oracle而言,如果在crash recovery阶段发现了fractured block块断裂,那么确实它也是无法基于redo去恢复它的。用户也需要基于backup去恢复对应的数据块。

因为oracle的redo也是仅仅记录redo vector的,而不记录block的full image,仅仅在做hot backup是记录block full image。

oracle中引入了参数_db_writer_verify_writes :Enable lost write detection mechanism

 

Parameter Type:string
Allowable Values: TRUE, LIOCHK, SCNCHK and FALSE.
Typical Values: User would use a value of either TRUE or FALSE.
Default Value: FALSE.
Dangerous Values: NA.
Description: This parameter will performs lost write detection.
Dynamic: No.
Syntax: _db_writer_verify_writes = <string value>

Algorithm: This parameter defaults to FALSE at startup. If set to LIOCHK we read the bunch of blocks, written by this DBWR process recently, into a PGA array and check the complete contents of the block with a memcmp. If set to SCNCHK we log the SCN of a block change into the simulated buffer header and check it back on read if the simulated buffer is still in the cache. If set to TRUE both SCNCHK and LIOCHK are performed. The performance overhead fromboth these changes is minimal.

 

简而言之,这个参数做的就是对DBWR写出的block,马上读取到PGA中做一个验证。这样能发现如下的情况:

  • fractured block 即写了一部分,partially written
  • Lost Write,写丢失

在Oracle中fractured block可以被算作physical corruption物理损坏,而Lost Write写丢失一般被认为是逻辑损坏。 写丢失是指这个该被写出更新的数据块一个字节都没更新,IO子系统欺骗上层DB引擎说已经写出了。

非instance crash情况下的partially written部分写块和lost write完全没写,其实是不多的;Mysql的double write机制保证了在crash recovery场景中存储子系统基本没问题的情况下,partially written和lost write不发生。

同时由于MySQL所处的存储子系统环境的复杂度往往没有oracle高,大部分采用本地磁盘或者没有复杂镜像基础的存储技术。而oracle中我们在采用大量存储镜像基础的用户哪里,是能明确遇到一些由于存储镜像技术问题造成的写丢失问题的:即Oracle写入到主镜像,存储成功返回;在后续的操作中Oracle读取到这些数据块,却没有被更新,因为读取到了没更新的存储镜像。这些故障的大量表象是ORA-08103,ORA-01410和一些ORA-00600报错。对于普通的存放ROW的block而言,lost write发生后甚至没有任何报错,也几乎无法让人察觉到,因为这个块本身内容是完全合法的。

在Oracle 11g中引入了db_lost_write_protect 参数来进一步防御写丢失,但该特性需要一般依赖于物理备库的存在。

 

 

19c Automatic Indexing 自动建索引 控制隐藏参数 _optimizer_auto_index_allow _optimizer_use_auto_indexes

目前 (2019年5月21日)似乎MOS上找不到 控制19c Automatic Indexing 自动建索引特性的Notes,大致可以通过 _optimizer_auto_index_allow 和 _optimizer_use_auto_indexes 2个参数关闭该新特性。

 col name format a40
 SELECT x.ksppinm NAME, y.ksppstvl VALUE
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  WHERE x.inst_id = USERENV ('Instance')
  AND y.inst_id = USERENV ('Instance')
  AND x.indx = y.indx
 AND x.ksppinm LIKE '%index%';
 
 NAME                                               VALUE
-------------------------------------------------- ------------------------------------------------------------
_optimizer_auto_index_allow                        AUTO
_gc_defer_ping_index_only                          TRUE
_kcl_index_split                                   TRUE
_gc_fast_index_split_wait                          0
_index_partition_large_extents                     FALSE
_index_partition_shrink_opt                        TRUE
_db_index_block_checking                           TRUE
_disable_index_block_prefetching                   FALSE
_index_scan_check_skip_corrupt                     FALSE
_index_scan_check_stopkey                          FALSE
_index_max_inc_trans_pct                           20
_index_split_chk_cancel                            5
_advanced_index_compression_trace                  0
db_index_compression_inheritance                   NONE
_index_alert_key_not_found                         FALSE
_reuse_index_loop                                  5
_reclaim_lob_index_scan_limit                      0
_ignore_desc_in_index                              FALSE
_index_load_buf_oltp_sacrifice_pct                 10
_index_load_buf_oltp_under_pct                     85
_index_load_buf_oltp_over_retry                    0
_index_load_last_leaf_comp                         85
_index_load_min_key_count                          10
_index_load_analysis_frequency                     4
_advanced_index_compression_umem_options           2147483647
_advanced_index_compression_options                0
_advanced_index_compression_cmp_options            0
_advanced_index_compression_tst_options            0
_advanced_index_compression_opt_options            0
_advanced_index_compression_options_value          0
_advanced_index_compression_recmp_cusz             90
_advanced_index_compression_recmp_crsz             10
_advanced_index_compression_recmp_nprg             10
_kdkv_index_lossy                                  TRUE
_kdkv_index_relocate                               FALSE
_kdkv_indexinvalid                                 FALSE
_domain_index_batch_size                           2000
_domain_index_dml_batch_size                       200
_odci_index_pmo_rebuild                            FALSE
_cell_index_scan_enabled                           TRUE
optimizer_index_cost_adj                           100
optimizer_index_caching                            0
_system_index_caching                              0
_index_prefetch_factor                             100
_index_join_enabled                                TRUE
_use_nosegment_indexes                             FALSE
_optimizer_compute_index_stats                     TRUE
skip_unusable_indexes                              TRUE
_delay_index_maintain                              TRUE
_disable_function_based_index                      FALSE
_globalindex_pnum_filter_enabled                   TRUE
_enable_online_index_without_s_locking             TRUE
_optimizer_fkr_index_cost_bias                     10
optimizer_use_invisible_indexes                    FALSE
_noseg_for_unusable_index_enabled                  TRUE
_px_index_sampling_objsize                         TRUE
_part_redef_global_index_update                    TRUE
_fast_index_maintenance                            TRUE
_modify_column_index_unusable                      FALSE
_indexable_con_id                                  TRUE
_optimizer_use_auto_indexes                        AUTO
_optimizer_gather_stats_on_load_index              TRUE

 
 
 
 
 
 _optimizer_auto_index_allow
 _optimizer_use_auto_indexes
 
 
 
 
 SQL> alter system set "_optimizer_auto_index_allow"=0 scope=spfile;
alter system set "_optimizer_auto_index_allow"=0 scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value 0 for parameter _optimizer_auto_index_allow, must be from among ALWAYS, AUTO, NEVER



alter system set "_optimizer_auto_index_allow"=NEVER scope=spfile;


alter system set "_optimizer_use_auto_indexes"=NEVER scope=spfile;
ERROR at line 1:
ORA-00096: invalid value NEVER for parameter _optimizer_use_auto_indexes, must be from among ON, AUTO, OFF


alter system set "_optimizer_use_auto_indexes"=OFF scope=spfile;



19C has a new feature database 19c is automatic indexing. How does it work?

QUESTIONS AND ANSWERS

How does automatic indexing work?
This is fully automated process. Oracle will identify candidate indexes,
verify their effectiveness, perform online validations and then implement the indexes where appropriate.

DBA does not need to do anything. Oracle internally picks the candidate indexes and validates the index or indexes.

Does any parameters need to be set for the automatic indexing to work?
Per documentation:
21.7.3 Enabling Automatic Indexing
Automatic indexing is disabled by default in an Oracle database. To enable
automatic indexing, set the AUTO_IMPLEMENT_INDEXES initialization parameter
to the Oracle database release number, for example, 19.1. You can disable
automatic indexing by setting the AUTO_IMPLEMENT_INDEXES initialization
parameter to NONE.

Database Oracle
Oracle Database Release 19
Database Administrator’s Guide

https://docs-stage.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-6E31777C-3BE3-4510-90D5-C715644E00CB

However, the parameter does not seem to exist though and cannot be used in 19.1

The parameter to control/enable automatic indexing is available in the next
refresh of the RDBMS, 19.2. per following internal bug:

Bug 29001016 – 19CBETA CANNOT USE AUTOMATIC INDEXING FUNCTIONALITY – PARAMETER MISSING

快速部署ORACLE 19C

https://github.com/macleanliu/ora-easy-deploy

 

 

ora-easy-deploy  WORKS ON (CENTOS REDHAT ORACLE) LINUX 7


国外主机

su - root 
yum -y install git 
git clone https://github.com/macleanliu/ora-easy-deploy
wget https://www.dropbox.com/s/8097ds292gabpk9/oracle-database-ee-19c-1.0-1.x86_64.rpm
wget https://www.dropbox.com/s/x14jlgnj062nef2/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm
bash ora-easy-deploy/create_db.sh

国内主机


wget --no-check-certificate https://zcdn.askmaclean.com/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
wget --no-check-certificate https://zcdn.askmaclean.com/oracle-database-ee-19c-1.0-1.x86_64.rpm


su - root 
yum -y install git 
git clone https://github.com/macleanliu/ora-easy-deploy
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm
bash ora-easy-deploy/create_db.sh


安装完成后#设置ORACLE OS账户密码

su - root
passwd oracle                     
su - oracle

lsnrctl start 
sql
alter system register;


[oracle@vultr ~]$ sql

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 15 07:09:52 2019
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> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


[oracle@vultr ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-MAY-2019 07:28:31

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-MAY-2019 07:27:06
Uptime                    0 days 0 hr. 1 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /opt/oracle/diag/tnslsnr/vultr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vultr.guest)(PORT=1521)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
  Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully






关闭部分19c特性sql


alter system set "_optimizer_aggr_groupby_elim"=false;
alter system set "_drop_stat_segment"=1;
alter system set "_common_data_view_enabled"=false;
alter system set optimizer_adaptive_features=false;
alter system set "_optimizer_dsdir_usage_control"=0; 
alter system set "_enable_automatic_sqltune"=false scope=both; 
alter system set "_serial_direct_read"=false;
alter system set "_nlj_batching_enabled" = 0; 
alter system set "_optimizer_undo_cost_change" = '10.2.0.4';
alter system set "_optimizer_null_aware_antijoin" = false;
alter system set "_optimizer_extend_jppd_view_types" = false;
alter system set "_replace_virtual_columns" = false;
alter system set "_first_k_rows_dynamic_proration" = false;
alter system set "_bloom_pruning_enabled" = false;
alter system set "_optimizer_multi_level_push_pred" = false;
alter system set "_optim_peek_user_binds"=false; 
alter system set client_result_cache_size=0 scope=spfile;
alter system set result_cache_mode=MANUAL;
alter system set "_diag_hm_rc_enabled"=false; 
alter system set audit_trail=none scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=both; 
alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set deferred_segment_creation=false; 
alter system set "_datafile_write_errors_crash_instance"=false ;
alter system set "_fairness_threshold"=6 scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_defer_time"=3  scope=spfile;
alter system set "parallel_force_local"=false;
alter system set "_gc_bypass_readers"=false;
alter system set "_row_cr"=false;
alter system set ddl_lock_timeout=0;
alter system set "_gby_hash_aggregation_enabled"=False scope=spfile;
alter system set "_cleanup_rollback_entries"=400 scope=spfile;
alter system set "_dbms_sql_security_level"=0                          scope=spfile;
alter system set "_bloom_pruning_enabled"=False                        scope=spfile;
alter system set "_simple_view_merging"=True                           scope=spfile;
alter system set "_enable_NUMA_optimization"=FALSE                     scope=spfile;
alter system set "_fix_control"='9344709:OFF'                           scope=spfile;
alter system set "_px_use_large_pool"=True                              scope=spfile;
alter system set "_mv_refresh_use_hash_sj"=FALSE                      scope=spfile;
alter system set "_mv_refresh_use_stats"=True                          scope=spfile;
alter system set "_like_with_bind_as_equality"=TRUE                    scope=spfile;
alter system set optimizer_secure_view_merging=false                   scope=spfile;
alter system set optimizer_capture_sql_plan_baselines=False            scope=spfile;
alter system set event="10949 TRACE NAME CONTEXT FOREVER:28401 trace name context forever, level 1"  scope=spfile;
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto optimizer stats collection', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto space advisor', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL,window_name => NULL);
commit;
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );
commit;



shutdown immediate;
startup ;

11g ocp ocm考证信息

 

11g ocm 相关博文:

 

数据库恢复的配置 https://www.askmaclean.com/archives/11g-ocm-student-guide-backup-restore.html
配置备份的规范 https://www.askmaclean.com/archives/dbdao-11g-oracle-ocm-2.html
使用RMAN创建备份 https://www.askmaclean.com/archives/oracle-11g-ocm-rman.html
使用RMAN执行恢复 https://www.askmaclean.com/archives/oracle-11g-ocm-master-rman-restore.html
SPA https://www.askmaclean.com/archives/oracle-11g-ocm-spa.html
SQL执行计划管理 https://www.askmaclean.com/archives/oracle-11g-ocm-spm.html
grid control 架构 https://www.askmaclean.com/archives/oracle-11g-ocm-grid-control-architecture.html
grid control 安装 https://www.askmaclean.com/archives/oracle-11g-ocm-grid-control-install.html
配置EMGC https://www.askmaclean.com/archives/oracle-11g-ocm-setemgc.html
Oracle Data Guard 介绍 https://www.askmaclean.com/archives/oracle-11g-ocm-dg.html
使用SQL和RMAN命令来创建一个物理备库 https://www.askmaclean.com/archives/oracle-11g-ocm-create-dg.html
oracle Data Guard Broker:概述 https://www.askmaclean.com/archives/oracle-data-guard-broker.html
配置 DataGuard 保护模式 https://www.askmaclean.com/archives/oracle-11g-ocm-data-mode.html
grid 安装 https://www.askmaclean.com/archives/oracle-11g-install-grid.html
管理Oracle 集群 https://www.askmaclean.com/archives/oracle-11g-ocm-manage-clusterware.html
RAC数据库安装 https://www.askmaclean.com/archives/oracle-11g-ocm-rac-install.html
数据库安全与审计 https://www.askmaclean.com/archives/oracle-11g-ocm-audit.html
物化视图和分区表 https://www.askmaclean.com/archives/oracle-11g-ocm-materializedview.html
并行和传输表空间 https://www.askmaclean.com/archives/oracle-11g-ocm-parallel.html
SQL*Loader和外部表 https://www.askmaclean.com/archives/oracle-11g-ocm-sqlloader.html
手动建库和基本数据库管理 https://www.askmaclean.com/archives/oracle-11g-ocm-createdatabase.html

 

11g OCP 考试题库中英分析:

【诗檀学院】Oracle 11g OCP-051考试题库中英文对照详解

【诗檀学院】Oracle 11g OCP-052考试题库中英文对照详解

【诗檀学院】Oracle 11g OCP-053考试题库中英文对照详解

 

沪ICP备14014813号

沪公网安备 31010802001379号