应用连接错误,初始化mysql数据库恢复

Posted on 2024 年 09 月 09 日 by 惜分飞

有人在部署一个新网站的时候,写错了配置信息,直接导致原有数据库被清掉,并创建了新库和写入了数据(其实本质就是drop table恢复)
mysql

登录操作系统查看,发现数据库文件在根分区,创建了新库,写入了数据之外,还有几个G的binlog.全部恢复不太可能,最后客户决定需要恢复的2个核心表数据,估计也就几十M的数据.通过os层面进行分析,发现操作系统的反删除恢复无法实现这类数据恢复.最后决定从mysql innodb的的碎片级别记性扫描恢复,通过扫描发现较多碎片
page

然后通过一些思路找出来需要恢复的表对应的page文件,然后对其进行解析恢复出来需要的数据

具体技术文章参考:
kettle导致MySQL数据丢失恢复
[MySQL异常恢复]恢复数据字典表讲解
[MySQL异常恢复]mysql drop table 数据恢复
[MySQL异常恢复]使用工具直接抽取MySQL数据字典
MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

RAC默认服务配置优先节点

Posted on 2024 年 09 月 07 日 by 惜分飞

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

标题：RAC默认服务配置优先节点

在某些rac情况下需要,需要对数据库默认的db_name对应的服务名进行修改,实现优先节点访问的效果.分析了下在默认值情况下,db_name影响到db_unique_name,然后决定了数据库的service_names.现有库的db_name无法修改,那就只能在db_unique_name上做手脚(只是修改service_names,对应的db_unique_name还是会创建默认服务,这样该服务依旧可以连接).但是在rac环境中db_unique_name记录到了crs资源之中,无法直接在数据库级别修改(修改会报ORA-32017 ORA-65500错误)

SQL> alter system set db_unique_name='nxifenfei' sid='*' scope=spfile;
alter system set db_unique_name='nxifenfei' sid='*' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

只能先删除crs中关于db的资源,然后再进行修改服务名,再增加db资源

[oracle@xffdb1 ~]$ srvctl remove database -d xifenfei  -f

SQL> alter system set db_unique_name='nxifenfei' sid='*' scope=spfile;

[oracle@xffdb1 ~]$ srvctl add database -d nxifenfei -o /u01/app/oracle/product/19c/db_1 -p \
  +DATADG/XIFENFEI/PARAMETERFILE/spfile.271.1174153165 -pwfile +DATADG/XIFENFEI/PASSWORD/pwdxifenfei.256.1174152463
[oracle@xffdb1 ~]$ srvctl add instance -d nxifenfei -i xifenfei1 -n xffdb1
[oracle@xffdb1 ~]$ srvctl add instance -d nxifenfei -i xifenfei2 -n xffdb2
[oracle@xffdb1 ~]$ srvctl add instance -d nxifenfei -i xifenfei3 -n xffdb3

创建新服务(和db_name同名,和现在的db_unique_name不同名)

[oracle@xffdb1 ~]$ srvctl add service -db nxifenfei -service xifenfei -r xifenfei2 -a xifenfei1,xifenfei3 \
  -failovertype SESSION -failovermethod BASIC -failoverdelay 10 -failoverretry 3 -failback YES
[oracle@xffdb1 ~]$ srvctl start service -db nxifenfei -service xifenfei

[oracle@xffdb1 ~]$ srvctl config service -d nxifenfei -service xifenfei
Service name: xifenfei
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: SESSION
Failover method: BASIC
Failover retries: 3
Failover delay: 10
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  yes
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: xifenfei2
Available instances: xifenfei1,xifenfei3
CSS critical: no
Service uses Java: false
[grid@xffdb1 ~]$

服务的其他操作

--调整服务的优先节点
srvctl modify service -db nxifenfei -service xifenfei -modifyconfig -preferred "xifenfei1" -available "xifenfei2,xifenfei3"
srvctl stop service -db nxifenfei -service xifenfei 
srvctl start service -db nxifenfei -service xifenfei 

--切换服务所在节点
srvctl relocate service -db nxifenfei -service xifenfei -oldinst xifenfei2 -newinst xifenfei1

--删除服务
srvctl stop service -db nxifenfei -service xifenfei
srvctl remove service -db nxifenfei -service xifenfei

Oracle 19c RAC 替换私网操作

Posted on 2024 年 09 月 06 日 by 惜分飞

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

标题：Oracle 19c RAC 替换私网操作

19c的三节点集群,需要替换一个私网网卡(如果有足够的停机窗口有一个更加简单的方法,直接通过修改网卡名称实现替换,不过需要主机重启一次,参考:Linux 8 修改网卡名称)
1. 先在主机层面确认新配置网络能够相互ping通,在hosts文件加入私网信息,并且确认ssh 可以相互访问

ssh xffdb1-priv3 date;ssh xffdb2-priv3 date;ssh xffdb3-priv3 date;

2. 删除掉需要删除的网络上的asm监听和该network信息

[grid@xffdb1 ~]$ srvctl config listener -asmlistener
Name: ASMNET1LSNR_ASM
Type: ASM Listener
Owner: grid
Subnet: 172.16.16.0
Home: <CRS home>
End points: TCP:1525
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
Name: ASMNET2LSNR_ASM
Type: ASM Listener
Owner: grid
Subnet: 172.17.17.0
Home: <CRS home>
End points: TCP:1526
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
[grid@xffdb1 ~]$ srvctl config asmnetwork
ASM network 1 exists
Subnet IPv4: 172.16.16.0//
Subnet IPv6:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:
ASM network 2 exists
Subnet IPv4: 172.17.17.0//
Subnet IPv6:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:
[grid@xffdb1 ~]$

[grid@xffdb3 ~]$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwASM
Backup of Password file: +DATA/orapwASM_backup
ASM listener: LISTENER
ASM instance count: 3
Cluster ASM listener: ASMNET1LSNR_ASM,ASMNET2LSNR_ASM
[grid@xffdb3 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.chad
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.net1.network
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.ons
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.proxy_advm
               OFFLINE OFFLINE      xffdb1                   STABLE
               OFFLINE OFFLINE      xffdb2                   STABLE
               OFFLINE OFFLINE      xffdb3                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.DATADG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.FRADG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.cvu
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.xffdb1.vip
      1        ONLINE  ONLINE       xffdb1                   STABLE
ora.xffdb2.vip
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.xffdb3.vip
      1        ONLINE  ONLINE       xffdb3                   STABLE
ora.xifenfei.db
      1        ONLINE  ONLINE       xffdb1                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       xffdb2                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      3        ONLINE  ONLINE       xffdb3                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xffdb2                   STABLE
--------------------------------------------------------------------------------

[grid@xffdb1 peer]$ srvctl update listener -listener ASMNET2LSNR_ASM -asm -remove -force
[grid@xffdb1 peer]$ srvctl remove asmnetwork -netnum 2 -force
PRCR-1028 : Failed to remove resource ora.asmnet2.asmnetwork
PRCR-1072 : Failed to unregister resource ora.asmnet2.asmnetwork
CRS-0245:  User doesn't have enough privilege to perform the operation
[root@xffdb1 ~]# source /home/grid/.bash_profile
[root@xffdb1 ~]# srvctl remove asmnetwork -netnum 2 -force
[root@xffdb1 ~]#
[root@xffdb1 ~]#
[root@xffdb1 ~]# crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.chad
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.net1.network
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.ons
               ONLINE  ONLINE       xffdb1                   STABLE
               ONLINE  ONLINE       xffdb2                   STABLE
               ONLINE  ONLINE       xffdb3                   STABLE
ora.proxy_advm
               OFFLINE OFFLINE      xffdb1                   STABLE
               OFFLINE OFFLINE      xffdb2                   STABLE
               OFFLINE OFFLINE      xffdb3                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.DATADG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.FRADG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       xffdb1                   STABLE
      2        ONLINE  ONLINE       xffdb2                   STABLE
      3        ONLINE  ONLINE       xffdb3                   STABLE
ora.cvu
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.xffdb1.vip
      1        ONLINE  ONLINE       xffdb1                   STABLE
ora.xffdb2.vip
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.xffdb3.vip
      1        ONLINE  ONLINE       xffdb3                   STABLE
ora.xifenfei.db
      1        ONLINE  ONLINE       xffdb1                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      2        ONLINE  ONLINE       xffdb2                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
      3        ONLINE  ONLINE       xffdb3                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       xffdb2                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xffdb2                   STABLE
--------------------------------------------------------------------------------
[grid@xffdb2 peer]$ srvctl config listener -asmlistener
Name: ASMNET1LSNR_ASM
Type: ASM Listener
Owner: grid
Subnet: 172.16.16.0
Home: <CRS home>
End points: TCP:1525
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
[grid@xffdb2 peer]$ srvctl config asmnetwork
ASM network 1 exists
Subnet IPv4: 172.16.16.0//
Subnet IPv6:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:

3. 替换集群私网操作

[grid@xffdb1 ~]$ oifcfg getif
bond0  192.168.20.0  global  public
ens9f0  172.16.16.0  global  cluster_interconnect,asm
ens9f1  172.17.17.0  global  cluster_interconnect,asm
[grid@xffdb1 ~]$ oifcfg setif -global ens6f0np0/172.18.18.0:cluster_interconnect,asm
[grid@xffdb1 ~]$ oifcfg getif
bond0  192.168.20.0  global  public
ens9f0  172.16.16.0  global  cluster_interconnect,asm
ens9f1  172.17.17.0  global  cluster_interconnect,asm
ens6f0np0  172.18.18.0  global  cluster_interconnect,asm
[grid@xffdb1 ~]$ oifcfg delif -global ens9f1/172.17.17.0
[grid@xffdb1 ~]$  oifcfg getif
bond0  192.168.20.0  global  public
ens9f0  172.16.16.0  global  cluster_interconnect,asm
ens6f0np0  172.18.18.0  global  cluster_interconnect,asm
[grid@xffdb1 ~]$ oifcfg delif -global ens9f1/172.17.17.0
[grid@xffdb1 ~]$  oifcfg getif
bond0  192.168.20.0  global  public
ens9f0  172.16.16.0  global  cluster_interconnect,asm
ens6f0np0  172.18.18.0  global  cluster_interconnect,asm

4. 依次重启集群三个节点(ASMNET2LSNR_ASM监听需要人工kill),集群网络替换完成(因为asm listener已经有一个,另外一个私网不准备给他们加上asm listener),如果要增加可以进行如下操作

# srvctl add asmnetwork -netnum 2 -subnet 172.18.18.0
% srvctl add listener -asmlistener -l ASMNET1LSNR_ASM -subnet 172.18.18.0

监听报TNS-12541 TNS-12560 TNS-00511错误

Posted on 2024 年 09 月 05 日 by 惜分飞

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

标题：监听报TNS-12541 TNS-12560 TNS-00511错误

有客户运行在win平台上的oracle 11.2.0.1,监听无法正常工作,使用status查看,卡很长时间然后出现如下TNS-12541 TNS-12560 TNS-00511 64-bit Windows Error: 2: No such file or directory错误

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

根据以往经验,监听服务本身启动成功,运行status hang很久,有可能和监听日志有关系,通过查看确认监听日志达到4G,关闭服务,重命名监听日志,再次启动监听,状态正常
listener

关于win平台监听超过4G,引起监听hang住的文章:windows平台listener.log超过4G导致监听异常

drop tablespace xxx including contents恢复

Posted on 2024 年 09 月 04 日 by 惜分飞

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

标题：drop tablespace xxx including contents恢复

最近接到一个客户恢复请求,对系统的核心业务表空间发起了drop tablespace xxx including contents 操作,导致该表空间被删除,客户在删除表空间操作之前使用expdp导出了一份元数据.
drop_tablespace

客户在咨询我的同时,也咨询了其他人,有人给客户答复是可以通过修改字典(以为有导出的元数据就可以逆向想改文件回去),然后把数据文件拷贝过去,实现恢复,成功概率65%[只能说是真牛]
QQ20240904-220806

对于这个客户的故障,这个思路不可能成功,原因有:
1)客户的系统中有部分字典信息已经彻底丢失,无法通过闪回找回来,因此无法对于字典逆向dml操作完成修改
2)drop tbs这个操作涉及的字典操作非常多,而且也非常复杂,在我的认知中,国内不一定有人完全在短时间内梳理清楚相互关系,完成逆向dml操作
3)他们咨询的人不是圈子中恢复大牛(因为圈子不大,大牛也不可能给他们出这种恢复方案)
4)数据文件复制到新库,完全不是同一个库的,要大量修改文件头信息,我估计他们在这一步都不能成功
果然不出所料,他们做了一个测试,结果库起不来
test

这个客户只是drop tablespace including contents 没有加上and datafiles,因此所有数据文件都还在
dbf

所以这个恢复相对比较简单,直接使用dul之类工具扫描数据文件获取到实际数据.结合客户导出的元数据和通过一些途径恢复出来的dataobj#信息,进行整合,实现数据接近完美恢复,可以业务直接启动成功,其中几个大表的lbo字段数据恢复情况
QQ20240904-222230

类似这样的drop tablespace恢复案例我们经历过很多,但是这个是恢复效果最好的(1.所有数据文件没有丢失;2.在删除表空间之前元数据导出了一份;3.通过找删除记录,awr中表,历史的dmp等方法找出来所有表的dataobj#）,以前的一些表空间删除恢复案例:
ASM删除表空间恢复
drop tablesapce 数据恢复
oracle drop tablespace 恢复最后一招
分享运气超级好的一次drop tablespace 数据恢复