分类目录归档：数据库

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

发表于 2024 年 9 月 9 日 由 惜分飞

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

登录操作系统查看,发现数据库文件在根分区,创建了新库,写入了数据之外,还有几个G的binlog.全部恢复不太可能,最后客户决定需要恢复的2个核心表数据,估计也就几十M的数据.通过os层面进行分析,发现操作系统的反删除恢复无法实现这类数据恢复.最后决定从mysql innodb的的碎片级别记性扫描恢复,通过扫描发现较多碎片
page
然后通过一些思路找出来需要恢复的表对应的page文件,然后对其进行解析恢复出来需要的数据
1
具体技术文章参考:
kettle导致MySQL数据丢失恢复
[MySQL异常恢复]恢复数据字典表讲解
[MySQL异常恢复]mysql drop table 数据恢复
[MySQL异常恢复]使用工具直接抽取MySQL数据字典
MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

发表在 MySQL恢复 | 标签为 , | 评论关闭

RAC默认服务配置优先节点

发表于 2024 年 9 月 7 日 由 惜分飞

在某些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 RAC | 标签为 , | 评论关闭

Oracle 19c RAC 替换私网操作

发表于 2024 年 9 月 6 日 由 惜分飞

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
发表在 ORACLE 19C, Oracle RAC | 标签为 , | 评论关闭