Database SOS – 第3页 – Professional Database Recovery Support@+8617813235971

Oracle 发布计划—包含Oracle 23ai版本

根据oracle官方公布的Oracle 23ai的发布计划（当然free版本，exadata,cloud版本已经发布）,可以确定在2024年上半年即将发布Linux x86-64版本，Oracle Solaris SPARC/Linux on Arm/Microsoft Windows x64 (64-bit)版本将在下版本发布,Oracle Solaris x86-64平台不在发布Oracle 23ai版本数据库，IBM AIX on POWER Systems/IBM Linux on System z/HP-UX Itanium在发布计划中具体时间没有公布。具体参考：Release Schedule of Current Database Releases (Doc ID 742060.1)

Platform	23aiLong Term Release	21cInnovation Release	19cLong Term Release	18c	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Oracle Public Cloud Releases*
Exadata Express Cloud Service	Not Planned	Not Planned	Not Planned	Not Planned	18-Sep-2016	N/A
Base Database Service¹(Previously known as: Database Cloud Service)	19-Sep-2023	08-Dec-2020available on VM (RAC or single-instance) and Bare Metal (single-instance)	OCI DB System – VM: Jul-2019OCI DB System – Bare Metal: April 2020	1-Mar-2018	4-Nov-2016	Sep-2014	N/A	Sep-2014
Exadata Database Service on Dedicated Infrastructure¹(Previously known as: Exadata Cloud Service)	2-May-2024	N/A	19-Jun-2019	Jun-2018	4-Nov-2016	Oct-2015	N/A	Oct-2015
Gen 1 Exadata Cloud at Customer¹(Previously known as: Exadata Cloud at Customer)	N/A	N/A	17-Sep-2019	May-2018	2-May-2017	Dec-2016	N/A
Exadata Database Service on Cloud at Customer ¹	2-May-2024	N/A	17-Sep-2019	N/A	N/A	N/A	N/A
Autonomous Database on Dedicated Exadata Infrastructure ²	1H CY2024	N/A	26-Jun-2019	N/A	N/A	N/A	N/A
Autonomous Database on Exadata Cloud at Customer ²	1H CY2024	N/A	08-Jul-2020	N/A	N/A	N/A	N/A
Autonomous Database on Shared Exadata Infrastructure ²	1H CY2024	N/A	15-Mar-2020	18-Mar-2018	N/A	N/A	N/A
*On-Premises Engineered Systems*(Same software as released for other platforms but tested on Engineered Systems)
Oracle Database Appliance	CY2024	16-Sep-2021	16-Oct-2019	7-Mar-2018	17-Nov-2017	Apr 2014	N/A	Oct 2013
Exadata	1H CY2024	13-Aug-2021	13-Feb-2019	16-Feb-2018	10-Feb-2017	Oct 2014	Mar 2013	Nov 2013
Supercluster	TBA	N/A	26-Apr-2019	12-Mar-2018	10-Feb-2017	Nov 2014	Apr 2014	Dec 2013
**On-Premises Server Releases (includes client)(Download here)**
Linux x86	Not Planned	Not Planned	Not planned	Not planned	Not planned	Not planned	Not planned	28-Aug-2013
Linux x86-64	1H CY2024	13-Aug-2021	25-Apr-2019	23-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	27-Aug-2013
Linux on Arm	2H CY2024	Not Planned	28-June-2023	Not Planned	Not Planned	Not Planned	Not Planned	Not Planned
Oracle Solaris SPARC (64-bit)	2H CY2024	See DOC ID 2853097.1	26-Apr-2019	30-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Oracle Solaris x86-64 (64-bit)	Platform de-supported	Platform de-supported	07-Nov-2019Terminal Release	6-Aug-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Microsoft Windows x64 (64-bit)	2H CY2024	08-Oct-2021	08-Jun-2019	21-Aug-2018	16-Mar-2017	25-Sep-2014	9-Jul-2013	25-Oct-2013
OpenVMS Itanium	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Terminal Release
HP-UX Itanium	TBA	28-Sept-2021	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
HP-UX PA-RISC (64-bit)	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	2-Jan-2014
IBM AIX on POWER Systems	TBA	See DOC ID 2766930.1	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
IBM Linux on System z	TBA	See DOC ID 2766930.1	06-June-2019	20-Nov -2018	6-Jun-2017	14-Nov-2014	9-Jan-2014	9-Jan-2014
Microsoft Windows (32-bit)	Not Planned	Client Only – Oct 2021	Client only – June 2019	Not planned	Not planned	Not planned	Not planned	25-Oct-2013
Platform	23ai	21c	19	18	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Instant Client-Only Releases*
Apple macOS (Intel) download			10-Sept-2019	Mar-2018	15-Jan-2018	2016	Not planned	20-Apr-2014 Download
IBM Linux on POWER (Big Endian)						18-Dec-2015 (download)		4-Dec-2014
IBM Linux on POWER (Little Endian)download			Jul-2019download	31-Oct-2018 (download)	7-Jun-2017 (download)	18-Dec-2015 (download)
Linux on Arm		Not Planned	May-2021download
¹Oracle Base Database Service (formerly Oracle Database Cloud Service) and Exadata Database Service (formerly Exadata Cloud Service and Exadata Cloud@Customer) follow the same support life and error correction schedule as on-premises dates unless otherwise noted in Table 1 above (for example, 11.2.0.4 MDS). Also, Extended Support is bundled with both the license-included and BYOL versions of these services and does not require additional fees. These services will not be covered under Sustaining Support and Oracle makes no commitment that any cloud service instances will continue to run after the end of their support life (Premier, extended, error correction, or MDS). We will not disable any installed databases upon the expiration of support, but the underlying infrastructure will continue to be updated. The infrastructure updates may render the unpatched databases inoperable. We make no commitment as to how long any unpatched databases will continue to run.²Oracle’s current plan for Oracle Autonomous Database – Shared Exadata Infrastructure, Oracle Autonomous Database – Dedicated Exadata Infrastructure, Autonomous Database on Exadata Cloud@Customer services is to support Long Term Release versions for a similar period of time found for those major releases when deployed on-premises. The intent of this plan is to provide stability of service experience for the thousands of critical database deployments found in Oracle Cloud. Additionally, we will at times make available Innovation Releases as part of the Autonomous Database Cloud Services. Innovation Releases, when provided, will be supported for a shorter period of time as compared to a Long Term Release, similar to what is found for those releases when deployed on-premises. Oracle reserves the right to change this plan with changing business requirements. Any change will take into account that stability is of utmost importance to Oracle’s many thousands of mission critical deployments. *References for Client-Only:* https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-windows-5539283.html *32-bit Linux & Windows Instant Clients, https://www.oracle.com/database/technologies/instant-client/linux-x86-32-downloads.html* *https://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html*

Oracle 23ai 变化之—-默认数据文件变为bigfile

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai 变化之—-默认数据文件变为bigfile

oracle把23c变为23ai，引入了很多新特性,今天下载了free版本的虚拟机登录进去第一感觉就是system,sysaux,undo等文件以前版本默认的smallfile变为了bigfile

SQL> select BANNER_FULL from v$version;

BANNER_FULL
----------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

SQL> select con_id,ts#,name,bigfile from v$tablespace order by 1,2;

    CON_ID        TS# NAME                           BIG
---------- ---------- ------------------------------ ---
         1          0 SYSTEM                         YES
         1          1 SYSAUX                         YES
         1          3 TEMP                           NO
         1          4 USERS                          YES
         1          5 UNDOTBS2                       YES
         2          0 SYSTEM                         YES
         2          1 SYSAUX                         YES
         2          2 UNDOTBS1                       YES
         2          3 TEMP                           NO
         3          0 SYSTEM                         YES
         3          1 SYSAUX                         YES
         3          2 UNDOTBS1                       YES
         3          3 TEMP                           NO
         3          6 USERS                          NO

14 rows selected.

SQL> select con_id,file#,ts#,rfile#,name from v$datafile;

    CON_ID      FILE#        TS#     RFILE# NAME
---------- ---------- ---------- ---------- ------------------------------------------------------
         1          1          0       1024 /opt/oracle/oradata/FREE/system01.dbf
         2          2          0       1024 /opt/oracle/oradata/FREE/pdbseed/system01.dbf
         1          3          1       1024 /opt/oracle/oradata/FREE/sysaux01.dbf
         2          4          1       1024 /opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf
         1          7          4       1024 /opt/oracle/oradata/FREE/users01.dbf
         2          9          2       1024 /opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
         3         12          0       1024 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
         3         13          1       1024 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
         3         14          2       1024 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
         3         15          6         15 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
         1         16          5       1024 /opt/oracle/oradata/FREE/undotbs2.dbf

11 rows selected.

PostgreSQL解析wal日志之—walminer

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 19c/21c最新patch信息-202404

21.0.0.0
Description	Database Update	GI Update	Windows Bundle Patch
APR2024 (21.14.0.0.0)	36352352	36352207	36219877
JAN2024 (21.13.0.0.0)	36041222	36031790	35962857
OCT2023 (21.12.0.0.0)	35740258	35738010	35681617
JUL2023 (21.11.0.0.0)	35428978	35427907	35347974
APR2023 (21.10.0.0.0)	35134934	35132566	35046488
JAN2023 (21.9.0.0.0)	34839741	34838415	34750812
Oct2022 (21.8.0.0.0)	34527084	34526142	34468137
JUL2022 (21.7.0.0.0)	34160444	34155589	34110698
APR2022 (21.6.0.0.0)	33843745	33859395	33829143
JAN2022 (21.5.0.0.0)	33516412	33531909	33589769
OCT2021 (21.4.0.0.0)	33239276	33250101	NA

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批量处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

PostgreSQL部分主要字典信息

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL部分主要字典信息

一、pg_class:

该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。

名字	类型	引用	描述
relname	name	数据类型名字。
relnamespace	oid	pg_namespace.oid	包含这个对象的名字空间(模式)的OI。
reltype	oid	pg_type.oid	对应这个表的行类型的数据类型。
relowner	oid	pg_authid.oid	对象的所有者。
relam	oid	pg_am.oid	对于索引对象，表示该索引的类型(B-tree，hash)。
relfilenode	oid	对象存储在磁盘上的文件名，如果没有则为0。
reltablespace	oid	pg_tablespace.oid	对象所在的表空间。如果为零，则表示使用该数据库的缺省表空间。(如果对象在磁盘上没有文件，这个字段就没有什么意义)
relpages	int4	该数据表或索引所占用的磁盘页面数量，查询规划器会借助该值选择最优路径。
reltuples	float4	表中行的数量，该值只是被规划器使用的一个估计值。
reltoastrelid	oid	pg_class.oid	与此表关联的TOAST表的OID，如果没有为0。TOAST表在一个从属表里”离线”存储大字段。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，该字段为它索引的OID，如果不是TOAST表则为0。
relhasindex	bool	如果这是一个数据表而且至少有(或者最近有过)一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM发现一个表没有索引，那么它清理 relhasindex。
relisshared	bool	如果该表在整个集群中由所有数据库共享，则为真。
relkind	char	r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表
relnatts	int2	数据表中用户字段的数量(除了系统字段以外，如oid)。在pg_attribute里肯定有相同数目的数据行。见pg_attribute.attnum.
relchecks	int2	表中检查约束的数量，参阅pg_constraint表。
reltriggers	int2	表中触发器的数量；参阅pg_trigger表。
relhasoids	bool	如果我们为对象中的每行都生成一个OID，则为真。
relhaspkey	bool	如果该表存在主键，则为真。
relhasrules	bool	如表有规则就为真；参阅pg_rewrite表。
relhassubclass	bool	如果该表有子表，则为真。
relacl	aclitem[]	访问权限。

二、pg_attribute:

该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。

名字	类型	引用	描述
attrelid	oid	pg_class.oid	此字段所属的表。
attname	name	字段名。
atttypid	oid	pg_type.oid	字段的数据类型。
attstattarget	int4	attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。
attlen	int2	该字段所属类型的长度。(pg_type.typlen的拷贝)
attnum	int2	字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。
attndims	int4	如果该字段是数组，该值表示数组的维数，否则是0。
attcacheoff	int4	在磁盘上总是-1，但是如果装载入内存中的行描述器中，它可能会被更新为缓冲在行中字段的偏移量。
atttypmod	int4	表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。
attbyval	bool	pg_type.typbyval字段值的拷贝。
attstorage	char	pg_type.typstorage字段值的拷贝。
attalign	char	pg_type.typalign字段值的拷贝。
attnotnull	bool	如果该字段带有非空约束，则为真，否则为假。
atthasdef	bool	该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。
attisdropped	bool	该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过SQL访问。
attislocal	bool	该字段是否局部定义在对象中的。
attinhcount	int4	该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。

三、pg_attrdef:

该系统表主要存储字段默认值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。

名字	类型	引用	描述
adrelid	oid	pg_class.oid	这个字段所属的表
adnum	int2	pg_attribute.attnum	字段编号，其规则等同于pg_attribute.attnum
adbin	text	字段缺省值的内部表现形式。
adsrc	text	缺省值的人可读的表现形式。

四、pg_constraint:

该系统表存储PostgreSQL中表对象的检查约束、主键、唯一约束和外键约束。

名字	类型	引用	描述
conname	name	约束名字(不一定是唯一的)。
connamespace	oid	pg_namespace.oid	包含这个约束的名字空间(模式)的OID。
contype	char	c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束
condeferrable	bool	该约束是否可以推迟。
condeferred	bool	缺省时这个约束是否是推迟的？
conrelid	oid	pg_class.oid	该约束所在的表，如果不是表约束则为0。
contypid	oid	pg_type.oid	该约束所在的域，如果不是域约束则为0。
confrelid	oid	pg_class.oid	如果为外键，则指向参照的表，否则为0。
confupdtype	char	外键更新动作代码。
confdeltype	char	外键删除动作代码。
confmatchtype	char	外键匹配类型。
conkey	int2[]	pg_attribute.attnum	如果是表约束，则是约束控制的字段列表。
confkey	int2[]	pg_attribute.attnum	如果是外键，则是参照字段的列表。
conbin	text	如果是检查约束，则表示表达式的内部形式。
consrc	text	如果是检查约束，则是表达式的人可读的形式。

五、pg_tablespace:

该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决IO瓶颈。

名字	类型	引用	描述
spcname	name	表空间名称。
spcowner	oid	pg_authid.oid	表空间的所有者，通常是创建它的角色。
spclocation	text	表空间的位置(目录路径)。
spcacl	aclitem[]	访问权限。

六、pg_namespace:

该系统表存储名字空间(模式)。

名字	类型	引用	描述
nspname	name	名字空间(模式)的名称。
nspowner	oid	pg_authid.oid	名字空间(模式)的所有者
nspacl	aclitem[]	访问权限。

七、pg_database:

该系统表存储数据库的信息。和大多数系统表不同的是，在一个集群里该表是所有数据库共享的，即每个集群只有一份pg_database拷贝，而不是每个数据库一份。

名字	类型	引用	描述
datname	name	数据库名称。
datdba	oid	pg_authid.oid	数据库所有者，通常为创建该数据库的角色。
encoding	int4	数据库的字符编码方式。
datistemplate	bool	如果为真，此数据库可以用于CREATE DATABASE TEMPLATE子句，把新数据库创建为此数据库的克隆。
datallowconn	bool	如果为假，则没有人可以联接到这个数据库。
datlastsysoid	oid	数据库里最后一个系统OID，此值对pg_dump特别有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	该数据库的缺省表空间。在这个数据库里，所有pg_class.reltablespace为零的表都将保存在这个表空间里，特别要指出的是，所有非共享的系统表也都存放在这里。
datconfig	text[]	运行时配置变量的会话缺省值。
datacl	aclitem[]	访问权限。

八、pg_index:

该系统表存储关于索引的一部分信息。其它的信息大多数存储在pg_class。

名字	类型	引用	描述
indexrelid	oid	pg_class.oid	该索引在pg_class里的记录的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class里的记录的OID。
indnatts	int2	索引中的字段数量(拷贝的pg_class.relnatts)。
indisunique	bool	如果为真，该索引是唯一索引。
indisprimary	bool	如果为真，该索引为该表的主键。
indisclustered	bool	如果为真，那么该表在这个索引上建了簇。
indkey	int2vector	pg_attribute.attnum	该数组的元素数量为indnatts，数组元素值表示建立这个索引时所依赖的字段编号，如1 3，表示第一个字段和第三个字段构成这个索引的键值。如果为0，则表示是表达式索引，而不是基于简单字段的索引。
indclass	oidvector	pg_opclass.oid	对于构成索引键值的每个字段，这个字段都包含一个指向所使用的操作符表的OID。
indexprs	text	表达式树用于那些非简单字段引用的索引属性。它是一个列表，在indkey里面的每个零条目一个元素。如果所有索引属性都是简单的引用，则为空。
indpred	text	部分索引断言的表达式树。如果不是部分索引，则是空字串。

PostgreSQL恢复系列:pg_filedump恢复字典构造

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL恢复系列:pg_filedump恢复字典构造

pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息

--数据库查询结果
postgres=# select oid,datname,datdba,dattablespace from pg_database;
  oid  |   datname   | datdba | dattablespace 
-------+-------------+--------+---------------
 14187 | postgres    |     10 |          1663
 16403 | db_xff      |     10 |          1663
     1 | template1   |     10 |          1663
 14186 | template0   |     10 |          1663
 16407 | db_xifenfei |  16405 |         16406
(5 rows)

--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   3 -- Length:  260  Offset: 7320 (0x1c98)  Flags: NORMAL
COPY: 14187     postgres
 Item   4 -- Length:  260  Offset: 7056 (0x1b90)  Flags: NORMAL
COPY: 16403     db_xff
 Item   5 -- Length:  297  Offset: 7888 (0x1ed0)  Flags: NORMAL
COPY: 1 template1
 Item   6 -- Length:  297  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 14186     template0
 Item   7 -- Length:  260  Offset: 6792 (0x1a88)  Flags: NORMAL
COPY: 16407     db_xifenfei

分析PostgreSQL 表空间信息

--sql查询表空间信息
postgres=# select * from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 1663      pg_default
 Item   2 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 1664      pg_global
 Item   3 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 16406     tbs_xifenfei

分析PostgreSQL 对象id、name、path对应关系

--对象信息查询
postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode,
           reltablespace from pg_class where relname like 't_t%' or relname like 't_x%';
  oid  |  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace 
-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------
 16387 | t_xifenfei |         2200 |   16389 |         0 |       10 |     2 |       16390 |             0
 16391 | t_xff      |         2200 |   16393 |         0 |       10 |     2 |       16391 |             0
 16394 | t_xff2     |         2200 |   16396 |         0 |       10 |     2 |       16394 |             0
 16397 | t_xff3     |         2200 |   16399 |         0 |       10 |     2 |       16397 |             0
 16400 | t_xff4     |         2200 |   16402 |         0 |       10 |     2 |       16400 |             0
 16408 | t_tbs      |         2200 |   16410 |         0 |       10 |     2 |       16408 |         16406
(6 rows)

--通过dump 该文件解析数据（显示部分）
COPY: 16394     t_xff2  2200    16396   0       10      2       16394   0       0       0.000000000000  0       0       f       f       p       r
 Item  29 -- Length:    0  Offset:   31 (0x001f)  Flags: REDIRECT
 Item  30 -- Length:  172  Offset: 2592 (0x0a20)  Flags: NORMAL
COPY: 16397     t_xff3  2200    16399   0       10      2       16397   0       0       0.000000000000  0       0       f       f       p       r
 Item  31 -- Length:  205  Offset: 3376 (0x0d30)  Flags: NORMAL
COPY: 12093     pg_shadow       11      12094   0       10      0       0       0       0       0.000000000000  0       0       f       f       p       v
 Item  32 -- Length:  172  Offset: 2416 (0x0970)  Flags: NORMAL
COPY: 16400     t_xff4  2200    16402   0       10      2       16400   0       0       0.000000000000  0       0       f       f       p       r

这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径

-bash-4.2$ pwd
/var/lib/pgsql/12/data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff

结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息

--sql查询模式信息
postgres=# select * from pg_namespace;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13887 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16404 | u_xifenfei         |       10 | 
(7 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   2 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 99        pg_toast
 Item   3 -- Length:    0  Offset:    7 (0x0007)  Flags: REDIRECT
 Item   4 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 12314     pg_temp_1
 Item   5 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 12315     pg_toast_temp_1
 Item   6 -- Length:  141  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 11        pg_catalog
 Item   7 -- Length:  141  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 2200      public
 Item   8 -- Length:    0  Offset:    9 (0x0009)  Flags: REDIRECT
 Item   9 -- Length:  141  Offset: 7472 (0x1d30)  Flags: NORMAL
COPY: 13887     information_schema
 Item  10 -- Length:   96  Offset: 7376 (0x1cd0)  Flags: NORMAL
COPY: 16404     u_xifenfei

通过pg_namespace，pg_class信息，可以获取到对象所属的模式关系，基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。

获取PostgreSQL表的列名称和类型[编号]信息

--sql查询列信息
postgres=# \d t_tbs
               Table "public.t_tbs"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 oid      | oid  |           |          | 
 spcname  | name |           |          | 
 spcowner | oid  |           |          | 
Tablespace: "tbs_xifenfei"

postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum 
----------+----------+----------+---------------+--------+--------
    16408 | tableoid |       26 |             0 |      4 |     -6
    16408 | cmax     |       29 |             0 |      4 |     -5
    16408 | xmax     |       28 |             0 |      4 |     -4
    16408 | cmin     |       29 |             0 |      4 |     -3
    16408 | xmin     |       28 |             0 |      4 |     -2
    16408 | ctid     |       27 |             0 |      6 |     -1
    16408 | oid      |       26 |            -1 |      4 |      1
    16408 | spcname  |       19 |            -1 |     64 |      2
    16408 | spcowner |       26 |            -1 |      4 |      3
(9 rows)

--dump 内容（截取部分）
 Item  11 -- Length:  144  Offset: 1424 (0x0590)  Flags: NORMAL
COPY: 16408     oid     26      -1      4       1
 Item  12 -- Length:  144  Offset: 1280 (0x0500)  Flags: NORMAL
COPY: 16408     spcname 19      -1      64      2
 Item  13 -- Length:  144  Offset: 1136 (0x0470)  Flags: NORMAL
COPY: 16408     spcowner        26      -1      4       3
 Item  14 -- Length:  144  Offset:  992 (0x03e0)  Flags: NORMAL
COPY: 16408     ctid    27      0       6       -1
 Item  15 -- Length:  144  Offset:  848 (0x0350)  Flags: NORMAL
COPY: 16408     xmin    28      0       4       -2
 Item  16 -- Length:  144  Offset:  704 (0x02c0)  Flags: NORMAL
COPY: 16408     cmin    29      0       4       -3
 Item  17 -- Length:  144  Offset:  560 (0x0230)  Flags: NORMAL
COPY: 16408     xmax    28      0       4       -4
 Item  18 -- Length:  144  Offset:  416 (0x01a0)  Flags: NORMAL
COPY: 16408     cmax    29      0       4       -5

PostgreSQL获取类型编号和实际类型名称对应关系

--查询类型编号和实际类型关系
postgres=# select oid,typname from pg_type;
  oid  |                typname                
-------+---------------------------------------
    16 | bool
    17 | bytea
    18 | char
    19 | name
    20 | int8
    21 | int2
    22 | int2vector
    23 | int4
    24 | regproc
    25 | text
    26 | oid
    27 | tid
    28 | xid
    29 | cid
……

--dump 内容（截取部分）
 Item   1 -- Length:  176  Offset: 8016 (0x1f50)  Flags: NORMAL
COPY: 16        bool
 Item   2 -- Length:  176  Offset: 7840 (0x1ea0)  Flags: NORMAL
COPY: 17        bytea
 Item   3 -- Length:  176  Offset: 7664 (0x1df0)  Flags: NORMAL
COPY: 18        char
 Item   4 -- Length:  176  Offset: 7488 (0x1d40)  Flags: NORMAL
COPY: 19        name
 Item   5 -- Length:  176  Offset: 7312 (0x1c90)  Flags: NORMAL
COPY: 20        int8
 Item   6 -- Length:  176  Offset: 7136 (0x1be0)  Flags: NORMAL
COPY: 21        int2
 Item   7 -- Length:  176  Offset: 6960 (0x1b30)  Flags: NORMAL
COPY: 22        int2vector
 Item   8 -- Length:  176  Offset: 6784 (0x1a80)  Flags: NORMAL
COPY: 23        int4
 Item   9 -- Length:  176  Offset: 6608 (0x19d0)  Flags: NORMAL
COPY: 24        regproc
 Item  10 -- Length:  176  Offset: 6432 (0x1920)  Flags: NORMAL
COPY: 25        text
 Item  11 -- Length:  176  Offset: 6256 (0x1870)  Flags: NORMAL
COPY: 26        oid
 Item  12 -- Length:  176  Offset: 6080 (0x17c0)  Flags: NORMAL
COPY: 27        tid
 Item  13 -- Length:  176  Offset: 5904 (0x1710)  Flags: NORMAL
COPY: 28        xid
 Item  14 -- Length:  176  Offset: 5728 (0x1660)  Flags: NORMAL
COPY: 29        cid
 Item  15 -- Length:  176  Offset: 5552 (0x15b0)  Flags: NORMAL
COPY: 30        oidvector

通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称，数据类型等信息。通过以上几个对象，即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理

PostgreSQL 16 源码安装

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL 16 源码安装

空闲中尝试在linux 8的版本上,源码安装PostgreSQL 16，参考文章:PostgreSQL16.2源码部署教程

[root@xifenfei tmp]# cat /etc/redhat-release 
Red Hat Enterprise Linux release 8.7 (Ootpa)
[root@xifenfei tmp]#  uname -osr
Linux 5.15.0-3.60.5.1.el8uek.x86_64 GNU/Linux

下载pg源码程序:PostgreSQL 16源码下载
linux配置

--关闭selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
setenforce 0

--安装相应的包
yum -y install readline readline-devel zlib zlib-devel gettext \
 gettext-devel openssl openssl-devel pam pam-devel libxml2 \
libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel \
libuuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*  libicu  libicu-devel

--修改内核参数
cat>>/etc/sysctl.conf<<EOF
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
EOF
sysctl -p

--创建组和用户
groupadd postgres -g 3000
useradd postgres -g 3000 -u 3000

--用户 limits 配置
cat>>/etc/security/limits.conf<<EOF
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
EOF

--创建相关目录和权限
mkdir -p /pg/database/server
mkdir -p /pg/database/data
mkdir -p /pg/database/wal
mkdir -p /pg/database/archive
chown -R postgres:postgres /pg -R
chmod 0775 /pg
chmod 0700 /pg/database/data

--设置环境变量
vi /home/postgres/.bash_profile
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/pg/database/server
export PGDATA=/pg/database/data
export PATH=$PGHOME/bin:$PATH

--编译pg软件
su - postgres
tar xzvf postgresql-16.2.tar.gz
cd /tmp/postgresql-16.2
./configure --prefix=/pg/database/server --with-pgport=5432
make
make install

--确认软件安装成功（检查编译日志和测试如下命令）
[postgres@xifenfei ~]$ postgres --version
postgres (PostgreSQL) 16.2

创建数据库

[postgres@xifenfei ~]$  /pg/database/server/bin/initdb -D/pg/database/data\
 -X/pg/database/wal -EUTF8 -Upostgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /pg/database/data ... ok
fixing permissions on existing directory /pg/database/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A,
 or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start

启动数据库

[postgres@xifenfei ~]$ /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start
waiting for server to start.... done
server started
[postgres@xifenfei ~]$ psql
psql (16.2)
Type "help" for help.

postgres=# \d pg_class
                     Table "pg_catalog.pg_class"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           | not null | 
 relname             | name         |           | not null | 
 relnamespace        | oid          |           | not null | 
 reltype             | oid          |           | not null | 
 reloftype           | oid          |           | not null | 
 relowner            | oid          |           | not null | 
 relam               | oid          |           | not null | 
 relfilenode         | oid          |           | not null | 
 reltablespace       | oid          |           | not null | 
 relpages            | integer      |           | not null | 
 reltuples           | real         |           | not null | 
 relallvisible       | integer      |           | not null | 
 reltoastrelid       | oid          |           | not null | 
 relhasindex         | boolean      |           | not null | 
 relisshared         | boolean      |           | not null | 
 relpersistence      | "char"       |           | not null | 
 relkind             | "char"       |           | not null | 
 relnatts            | smallint     |           | not null | 
 relchecks           | smallint     |           | not null | 
 relhasrules         | boolean      |           | not null | 
 relhastriggers      | boolean      |           | not null | 
 relhassubclass      | boolean      |           | not null | 
 relrowsecurity      | boolean      |           | not null | 
 relforcerowsecurity | boolean      |           | not null | 
 relispopulated      | boolean      |           | not null | 
 relreplident        | "char"       |           | not null | 
 relispartition      | boolean      |           | not null | 
 relrewrite          | oid          |           | not null | 
 relfrozenxid        | xid          |           | not null | 
 relminmxid          | xid          |           | not null | 
 relacl              | aclitem[]    |           |          | 
 reloptions          | text[]       | C         |          | 
 relpartbound        | pg_node_tree | C         |          | 
Indexes:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

配置开机自动启动

[root@xifenfei tmp]# cp /tmp/postgresql-16.2/contrib/start-scripts/linux /etc/init.d/PostgreSQL
[root@xifenfei tmp]# vi /etc/init.d/PostgreSQL 
--修改如下值
prefix=/pg/database/server
PGDATA="/pg/database/data"
[root@xifenfei tmp]# chkconfig --add PostgreSQL
[root@xifenfei tmp]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

PostgreSQL      0:off   1:off   2:on    3:on    4:on    5:on    6:off

ORA-00742 ORA-00312 恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00742 ORA-00312 恢复

有客户反馈,断电之后数据库启动报ORA-00742和ORA-00312,无法正常open

我们远程上去尝试open库结果也报同样错误

[oracle@oldhis oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 10 09:40:03 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 3 thread 1: '/oradata/shrdh/redo03.log'


SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
         2 INACTIVE

因为recover已经成功,但是依旧报ORA-742错误,尝试查询scn相关信息

SQL> set pages 10000
set numw 16
SELECT status,
checkpoint_change#,
checkpoint_time,last_change#,
count(*) ROW_NUM
FROM v$datafile
GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
ORDER BY status, checkpoint_change#, checkpoint_time;


set numw 16
col CHECKPOINT_TIME for a40
set lines 150
set pages 1000
SELECT status,
to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
ORDER BY status, checkpoint_change#, checkpoint_time;

SQL> SQL>   2    3    4    5    6    7  
STATUS  CHECKPOINT_CHANGE# CHECKPOIN     LAST_CHANGE#          ROW_NUM
------- ------------------ --------- ---------------- ----------------
ONLINE          1279351848 26-MAR-24       1279351848               19
SYSTEM          1279351848 26-MAR-24       1279351848                1

SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  
STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2024-03-26 00:05:45                      NO          1279351848               20

基于这样的情况,我们判断数据库直接open成功

SQL> recover database using backup controlfile;
ORA-00279: change 1279351848 generated at 03/26/2024 00:05:45 needed for thread 1
ORA-00289: suggestion : /oradata/arch/shrdh/shrdh_1_12984_974767526.arc
ORA-00280: change 1279351848 for thread 1 is in sequence #12984


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/shrdh/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

后面比较不幸，数据库报ORA-600 4194错误导致数据库异常

Wed Apr 10 09:43:08 2024
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...
Wed Apr 10 09:43:24 2024
ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
Media Recovery Log /oradata/shrdh/redo03.log
Media Recovery Complete (shrdh)
Completed: ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  
alter database open resetlogs
RESETLOGS after complete recovery through change 1279351849
Clearing online redo logfile 1 /oradata/shrdh/redo01.log
Clearing online log 1 of thread 1 sequence number 12982
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradata/shrdh/redo02.log
Clearing online log 2 of thread 1 sequence number 12983
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradata/shrdh/redo03.log
Clearing online log 3 of thread 1 sequence number 12984
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 1820377766 (0x6c80c2a6)
Online log /oradata/shrdh/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/shrdh/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/shrdh/redo03.log: Thread 1 Group 3 was previously cleared
Wed Apr 10 09:43:34 2024
Setting recovery target incarnation to 2
Wed Apr 10 09:43:34 2024
Assigning activation ID 2011515185 (0x77e54931)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/shrdh/redo01.log
Successful open of redo thread 1
Wed Apr 10 09:43:34 2024
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Apr 10 09:43:34 2024
SMON: enabling cache recovery
[25089] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1273646224 end:1273646494 diff:270 (2 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc  (incident=84296):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84296/shrdh_smon_21704_i84296.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Starting background process QMNC
Wed Apr 10 09:43:35 2024
QMNC started with pid=24, OS id=25340 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery stopped at EOT rba 1.99.16
Block recovery completed at rba 1.99.16, scn 0.1279351933
Block recovery from logseq 1, block 61 to scn 1279351919
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.87.16, scn 0.1279351922
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc:
ORA-01595: error freeing extent (2) of rollback segment (7))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Completed: alter database open resetlogs
Wed Apr 10 09:43:37 2024
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_m000_25343.trc  (incident=84392):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84392/shrdh_m000_25343_i84392.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Apr 10 09:43:37 2024
Starting background process CJQ0
Wed Apr 10 09:43:37 2024
CJQ0 started with pid=29, OS id=25357 
Starting background process SMCO
Wed Apr 10 09:43:37 2024
SMCO started with pid=30, OS id=25360 
Wed Apr 10 09:43:38 2024
Flush retried for xcb 0x115b42d28, pmd 0x1148dea70
Block recovery from logseq 1, block 61 to scn 1279351933
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /oradata/shrdh/redo01.log
Block recovery completed at rba 1.99.16, scn 0.1279351934
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc  (incident=84208):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84208/shrdh_pmon_21679_i84208.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 21679): terminating the instance due to error 472
Wed Apr 10 09:43:47 2024
Instance terminated by PMON, pid = 21679

报错比较明显,对undo进行处理即可.

Oracle 23ai rm redo*.log恢复