标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
作者归档:惜分飞
PostgreSQL解析wal日志之—walminer
在oracle数据库中可以通过logminer实现对归档日志的解析,从而分析执行sql语句和undo sql,可以实现某些情况下数据库一些操作的定位(比如日志突然增加,数据突然丢失)以及一些故障的恢复(比如需要把update/delete执行的数据找回)等。在PostgreSQL数据库中walminer可以实现该需求,对pg的预写式日志(wal)的解析,具体见官网:https://gitee.com/movead/XLogMiner/
walminer安装
[postgres@localhost tmp]$ ls -l walminer_x86_64_centos_v4.6.0.tar.gz -rw-r--r--. 1 root root 3866437 Apr 18 10:08 walminer_x86_64_centos_v4.6.0.tar.gz [postgres@localhost tmp]$ tar xzvf walminer_x86_64_centos_v4.6.0.tar.gz walminer_x86_64_centos_v4.6.0/ walminer_x86_64_centos_v4.6.0/bin/ walminer_x86_64_centos_v4.6.0/bin/walminer walminer_x86_64_centos_v4.6.0/lib/ walminer_x86_64_centos_v4.6.0/lib/libpq.so.5.15 walminer_x86_64_centos_v4.6.0/lib/libpq.so.5 walminer_x86_64_centos_v4.6.0/lib/libpq.so walminer_x86_64_centos_v4.6.0/share/ ………… [root@localhost ~]# mkdir -p /usr/local/walminer/ [root@localhost ~]# chown postgres:postgres /usr/local/walminer/ [root@localhost ~]# cp /tmp/walminer/walminer.license /usr/local/walminer/ [postgres@localhost bin]$ cd /tmp/walminer [postgres@localhost walminer]$ cp -rp * /usr/local/walminer/ [postgres@localhost bin]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/walminer/ [postgres@localhost bin]$ cd /usr/local/walminer/bin [postgres@localhost bin]$ ./walminer help walminer [command] [options] COMMANDS --------- #wal2sql options -D dic file for miner -a out detail info for catalog change -w wal file path to miner -t dest of miner result(1 stdout, 2 file, 3 db)(stdout default) -k boundary kind(1 all, 2 lsn, 3 time, 4 xid)(all default) -m miner mode(0 nomal miner, 1 accurate miner)(nomal default) if k=2 -r the relname for single table miner -s start location if k=2 or k=3, or xid if k = 4 if k=2 default the min lsn of input wals if k=3 or k=4 you need input this -e end wal location if k=2 or k=3 if k=2 default the max lsn of input wals if k=3 you need input this -f file to store miner result if t = 2 -d target database name if t=3(default postgres) -h target database host if t=3(default localhost) -p target database port if t=3(default 5432) -u target database user if t=3(default postgres) -W target user password if t=3 --------- #builtdic options -d target database name for connect(default postgres) -h target database host(default localhost) -p target database port(default 5432) -u target database user(default postgres) -W target user password -D dic produce path -f rewrite walminer dic if exists -s only database pointed by -d --------- #showdic options -D dic file to show --------- #avatardic options -r avatar rel that new created -n avatared relfilenode -D avatared walminer dic path -b target database name which contain rel pointed by -r --------- #regress(not support for user) options -w test database wal path(default postgres) -d test database name(default postgres) -h test database host(default localhost) -p test database port(default 5432) -u test database user(default postgres) -P apply database port -W test user password --------- #fosync options -D dic file for miner -w wal file path to miner -t dest of miner result(1 stdout, 2 file, 3 db, 4 apply)(stdout default) -f file to store miner result if t = 2 -l lsn it start fync -d target database name if t=3 or 4(default postgres) -h target database host if t=3 or 4(default localhost) -p target database port if t=3 or 4(default 5432) -u target database user if t=3 or 4(default postgres) -W target user password if t=3 or 4 --------- #pgto options -c configure path -i to init a CDC configure -r to run a CDC configure Below is needed when -i -d source database name(default postgres) -h source database host(default localhost) -p source database port(default 5432) -u source database user(default postgres) -w source user password -D target database name -H target database host -P target database port -U target database user -W target user password -K target database type(1 postgres) (support postgres only currently) -s slot name need for CDC --------- #waldump options -D dic file for miner -w wal file path to dump -t dest of miner result(1 stdout, 2 file)(stdout default) -s start lsn to dump -e end lsn to dump -f file to store miner result if t = 2 -v verbose --------- ################################################# [postgres@localhost bin]$
postgresql创建测试表和插入数据
[postgres@localhost ~]$ psql psql (16.2) Type "help" for help. postgres=# select now(); now ------------------------------- 2024-04-25 10:48:00.602067-04 (1 row) postgres=# postgres=# create table t_walminer(id int,name varchar(100)); CREATE TABLE postgres=# insert into t_walminer values(1,'www.xifenfei.com'); INSERT 0 1 postgres=# insert into t_walminer values(2,'www.orasos.com'); INSERT 0 1 postgres=# insert into t_walminer values(3,'xifenfei'); INSERT 0 1 postgres=# select * from t_walminer; id | name ----+------------------ 1 | www.xifenfei.com 2 | www.orasos.com 3 | xifenfei (3 rows) postgres=# select now(); now ------------------------------- 2024-04-25 10:49:47.036881-04 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/D5023E8 (1 row)
walminer 生成字典
[postgres@localhost bin]$ ./walminer builtdic -D /usr/local/walminer/xifenfei.dic ################################################# Walminer for PostgreSQL wal Contact Author by mail 'lchch1990@sina.cn' Persional License for posgress ################################################# DIC INFO# sysid:7357852038421105818 timeline:1 dbversion:160002 walminer:4.6
walminer解析这个时间段wal操作
[postgres@localhost bin]$ ./walminer wal2sql -D /usr/local/walminer/xifenfei.dic -w /pg/database/data/pg_arch \ -k 3 -s 2024-04-24 -e 2024-04-26 ################################################# Walminer for PostgreSQL wal Contact Author by mail 'lchch1990@sina.cn' Vip License for posgress ################################################# Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.42721+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000002 on time 2024-04-25 23:27:07.45369+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000003 on time 2024-04-25 23:27:07.453891+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000004 on time 2024-04-25 23:27:07.486403+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000005 on time 2024-04-25 23:27:07.513144+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000006 on time 2024-04-25 23:27:07.538212+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000007 on time 2024-04-25 23:27:07.561455+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000008 on time 2024-04-25 23:27:07.584488+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000009 on time 2024-04-25 23:27:07.606598+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000A on time 2024-04-25 23:27:07.609195+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000B on time 2024-04-25 23:27:07.609344+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000C on time 2024-04-25 23:27:07.609364+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.66233+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.684666+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.684877+08 Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.684899+08 Get start lsn 0/d4eb380 for time range Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.694947+08 [XID]=425507, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(1 ,'www.xifenfei.com') [UNDO]=DELETE FROM public.t_walminer WHERE id=1 AND name='www.xifenfei.com' [database]=postgres [COMPLETE]=true [LSN]=0/d5021c8 [COMMITLSN]=0/d502218 [COMMITTIME]=2024-04-25 22:48:55.775279+08 ------------------------------------------------------ [XID]=425508, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(2 ,'www.orasos.com') [UNDO]=DELETE FROM public.t_walminer WHERE id=2 AND name='www.orasos.com' [database]=postgres [COMPLETE]=true [LSN]=0/d502278 [COMMITLSN]=0/d5022c8 [COMMITTIME]=2024-04-25 22:49:10.769752+08 ------------------------------------------------------ [XID]=425509, [TOPXID]=0 [SQLNO]=1 [SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(3 ,'xifenfei') [UNDO]=DELETE FROM public.t_walminer WHERE id=3 AND name='xifenfei' [database]=postgres [COMPLETE]=true [LSN]=0/d502328 [COMMITLSN]=0/d502370 [COMMITTIME]=2024-04-25 22:49:23.382642+08 ------------------------------------------------------ Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.696041+08 Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.696062+08 [postgres@localhost bin]$
通过上述测试证明walminer可以非常好的解析pg的wal日志
Oracle 19c/21c最新patch信息-202404
|
19.0.0.0 | |||
Description | Database Update | GI Update | Windows Bundle Patch |
APR2024 (19.23.0.0.0) | 36233263 | 36233126 | 36219938 |
JAN2024 (19.22.0.0.0) | 35943157 | 35940989 | 35962832 |
OCT2023 (19.21.0.0.0) | 35643107 | 35642822 | 35681552 |
JUL2023 (19.20.0.0.0) | 35320081 | 35319490 | 35348034 |
APR2023 (19.19.0.0.0) | 35042068 | 35037840 | 35046439 |
JAN2023 (19.18.0.0.0) | 34765931 | 34762026 | 34750795 |
Oct2022 (19.17.0.0.0) | 34419443 | 34416665 | 34468114 |
JUL2022 (19.16.0.0.0) | 34133642 | 34130714 | 34110685 |
APR2022 (19.15.0.0.0) | 33806152 | 33803476 | 33829175 |
JAN2022 (19.14.0.0.0) | 33515361 | 33509923 | 33575656 |
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 |
19.0.0.0 | |||
Description | OJVM Update | OJVM + DB Update | OJVM + GI Update |
APR2024 (19.23.0.0.240416) | 36199232 | 36209492 | 36209493 |
JAN2024 (19.22.0.0.240116) | 35926646 | 36031426 | 36031453 |
OCT2023 (19.21.0.0.231017) | 35648110 | 35742413 | 35742441 |
JUL2023 (19.20.0.0.230718) | 35354406 | 35370174 | 35370167 |
APR2023 (19.19.0.0.230418) | 35050341 | 35058163 | 35058172 |
JAN2023 (19.18.0.0.230117) | 34786990 | 34773489 | 34773504 |
OCT2022 (19.17.0.0.221018) | 34411846 | 34449114 | 34449117 |
JUL2022 (19.16.0.0.220719) | 34086870 | 34160831 | 34160854 |
APR2022 (19.15.0.0.220419) | 33808367 | 33859194 | 33859214 |
JAN2022 (19.14.0.0.220118) | 33561310 | 33567270 | 33567274 |
OCT2021 (19.13.0.0.211019) | 33192694 | 33248420 | 33248471 |
JUL2021 (19.12.0.0.210720) | 32876380 | 32900021 | 32900083 |
APR2021 (19.11.0.0.210420) | 32399816 | 32578972 | 32578973 |
JAN2021 (19.10.0.0.210119) | 32067171 | 32126828 | 32126842 |
OCT2020 (19.9.0.0.201020) | 31668882 | 31720396 | 31720429 |
JUL2020 (19.8.0.0.200714) | 31219897 | 31326362 | 31326369 |
APR2020 (19.7.0.0.200414) | 30805684 | 30783543 | 30783556 |
JAN2020 (19.6.0.0.200114) | 30484981 | 30463595 | 30463609 |
OCT2019 (19.5.0.0.191015) | 30128191 | 30133124 | 30133178 |
JUL2019 (19.4.0.0.190716) | 29774421 | 29699079 | 29699097 |
APR2019 (19.3.0.0.190416) | 29548437 | 29621253 | 29621299 |
参考:Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
PostgreSQL恢复系列:pg_filedump批量处理
pg_filedump工具使用起来比较麻烦,主要存在问题:
1. 需要人工一个个枚举各个列类型无法实现批量恢复,参考以前写的PostgreSQL恢复系列:pg_filedump基本使用
2. 特别是在pg库无法正常运行的情况下,如果没有业务提供表创建语句,恢复基本上无法正常进行.
基于这两个问题,在以前的文章中写过PostgreSQL恢复系列:pg_filedump恢复字典构造,为了解决上述的两个,弄了一个pg_filedump_batch脚本实现批量恢复需求
在测试的pg库中创建了一些测试表,并查看部分表数据,便于对比后续恢复效果
postgres=# \d List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | t_tbs | table | postgres public | t_xff | table | postgres public | t_xff2 | table | postgres public | t_xff3 | table | postgres public | t_xff4 | table | postgres public | t_xifenfei | table | postgres public | tab_attribute | table | postgres public | tab_class | table | postgres public | tab_database | table | postgres public | tab_namespace | table | postgres public | tab_tablespace | table | postgres public | tab_type | table | postgres (12 rows) postgres=# select * from tab_database; oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoi d | datfrozenxid | datminmxid | dattablespace -------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+------------- --+--------------+------------+--------------- 14187 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 1418 6 | 479 | 1 | 1663 16403 | db_xff | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 1418 6 | 479 | 1 | 1663 1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 1418 6 | 479 | 1 | 1663 14186 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 1418 6 | 479 | 1 | 1663 16407 | db_xifenfei | 16405 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 1418 6 | 479 | 1 | 16406 (5 rows) postgres=# select count(1) from tab_class; count ------- 407 (1 row) postgres=# select *from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions -------+--------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 16406 | tbs_xifenfei | 16405 | | (3 rows)
使用pg_filedump_bath脚本来实现批量恢复
[root@xifenfei tmp]# ./pg_filedump_batch recover --database-oid=14187 \ --output-directory=/data/recovery --pgdata=/var/lib/pgsql/12/data Recover tables in database with oid: 14187 LOG: starting to process table tab_attribute LOG: starting to process table tab_class LOG: starting to process table tab_database LOG: starting to process table tab_namespace LOG: starting to process table tab_tablespace LOG: starting to process table tab_type LOG: starting to process table t_tbs LOG: starting to process table t_xff LOG: starting to process table t_xff2 LOG: starting to process table t_xff3 LOG: starting to process table t_xff4 LOG: starting to process table t_xifenfei Check dumps in /data/recovery
参考数据恢复
[root@xifenfei tmp]# cd /data/recovery/ [root@xifenfei recovery]# ls -ltr total 156 -rw-r--r-- 1 root root 82797 Apr 18 20:35 recovered-14187-tab_attribute.csv -rw-r--r-- 1 root root 31129 Apr 18 20:35 recovered-14187-tab_class.csv -rw-r--r-- 1 root root 343 Apr 18 20:35 recovered-14187-tab_database.csv -rw-r--r-- 1 root root 118 Apr 18 20:35 recovered-14187-tab_namespace.csv -rw-r--r-- 1 root root 50 Apr 18 20:35 recovered-14187-tab_tablespace.csv -rw-r--r-- 1 root root 7907 Apr 18 20:35 recovered-14187-tab_type.csv -rw-r--r-- 1 root root 0 Apr 18 20:35 recovered-14187-t_tbs.csv -rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff.csv -rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff2.csv -rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff3.csv -rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff4.csv -rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xifenfei.csv [root@xifenfei recovery]# cat recovered-14187-tab_database.csv 14187 postgres 10 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 1663 16403 db_xff 10 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 1663 1 template1 10 6 en_US.UTF-8 en_US.UTF-8 t t -1 14186 479 1 1663 14186 template0 10 6 en_US.UTF-8 en_US.UTF-8 t f -1 14186 479 1 1663 16407 db_xifenfei 16405 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 16406 [root@xifenfei recovery]# cat recovered-14187-tab_class.csv|wc -l 407 [root@xifenfei recovery]# cat recovered-14187-tab_tablespace.csv 1663 pg_default 1664 pg_global 16406 tbs_xifenfei
把pg_class恢复数据导入库中进行对比,证明恢复的数据完全正确
postgres=# COPY tab_class_new FROM '/data/recovery/recovered-14187-tab_class.csv'; COPY 407 postgres=# select count(1) from tab_class; count ------- 407 (1 row) count ------- 407 (1 row) postgres=# select count(1) from tab_class_new; count ------- 407 (1 row) postgres=# select * from tab_class_new postgres-# EXCEPT postgres-# select * from tab_class; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind -----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---- -----------+---------------+-------------+-------------+----------------+--------- (0 rows) postgres=# select * from tab_class postgres-# EXCEPT postgres-# select * from tab_class_new; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind -----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---- -----------+---------------+-------------+-------------+----------------+--------- (0 rows)
通过上述操作证明:
1. 在没有人工列出列类型的情况下实现批量pg_filedump恢复功能
2. 在pg库没有启动的情况下直接解析字典实现恢复功能
3. 实现pg数据库的批量恢复
如果有PostgreSQL的数据库故障,自行无法解决,请联系我们提供专业数据库恢复技术支持:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com