8分钟完成Rac升级迁移+补丁操作

数据库技术加油站 2023-09-27

1、介绍

环境：双节点 linux7+11.2.0.4rac

数据量：900G

硬件环境：2台服务器、2台存储、2台光交，1台服务器和存储各放置一个机房，做的rac，2台存储聚合，私有网络走的单独的交换机。

容灾环境：现有2个dg，1个是三方容灾机上，1个是其他厂商做的.

存储空间：3个5G的lun，5个500G的lun。服务器本地磁盘空间500G可用

网卡说明：2个光口、2个电口，网卡做了team绑定，pubic、private都各两个网卡，做的负载均衡配置。

2、需求

用户等保漏洞扫描发现大量的漏洞，其它厂商的人员在打补丁将环境进行了损坏，无法恢复的情况下，我将asm的数据抽取到了单机文件系统来临时进行了恢复，事后用户又要求：恢复rac的环境同时完成数据库的升级。

3、具体方案

用户只给了30分钟，按照以往的经验可以使用如下方案：

3.1 单节点安装19c rac，adg迁移方式后，推掉11grac添加19c节点。

3.2 单节点安装19c rac，xtts迁移升级后，推掉11grac添加19c节点。

3.3 单节点安装19c rac，ogg迁移升级后，推掉11grac添加19c节点。

我这里为了稳妥，加上产品升级执行的脚本较多，我选择了3.1。上面的步骤都不难看出我的方案全部是单机搭建rac，再加节点的方式，因为用户只有两台服务器可用。

4、RAC配置和安装

安装配置跳过...


共享磁盘还是有很多人用裸设备、asmlib来绑定，asmlib这玩意我一直都不建议使用。
因为asm的原理无非就是固话磁盘设备名称、固话权限和用户组。bug很多，linux原生的udev可以更好地做到这一点还更灵活。
以下是我常用的多路径+udev配置
1)、安装
rpm -qa|grep multipath


2)、将多路径软件嵌入内核(两台机器均要执行)
modprobe dm-multipath
modprobe dm-round-robin
lsmod|grep dm_multipath


systemctl enable multipathd
systemctl start multipathd
multipath -v2 --聚合，如果没有cp后再运行


cp usr/share/doc/device-mapper-multipath-0.4.9/multipath.conf etc/multipath.conf
3)、查看多路径路径有多少条
multipath -ll |grep policy |wc -l


multipath -ll |grep "active ready running"
查看uuid
multipath -ll |grep mpath


[root@hisservera software]# multipath -ll |grep -A 1 mpath|grep -v "^-"




mpathc (360002ac0000000000000019e0001e94d) dm-5 3PARdata,VV              size=5.0G features='0' hwhandler='1 alua' wp=rw
mpathb (360002ac0000000000000019d0001e94d) dm-4 3PARdata,VV              size=5.0G features='0' hwhandler='1 alua' wp=rw
mpatha (360002ac0000000000000019c0001e94d) dm-3 3PARdata,VV              size=5.0G features='0' hwhandler='1 alua' wp=rw


mpathi (360002ac0000000000000000c0001e94d) dm-11 3PARdata,VV             size=500G features='0' hwhandler='1 alua' wp=rw
mpathg (360002ac000000000000001a50001e94d) dm-9 3PARdata,VV              size=500G features='0' hwhandler='1 alua' wp=rw
mpathf (360002ac000000000000001a10001e94d) dm-8 3PARdata,VV              size=500G features='0' hwhandler='1 alua' wp=rw
mpathe (360002ac000000000000001a00001e94d) dm-7 3PARdata,VV       size=500G features='0' hwhandler='1 alua' wp=rw
mpathd (360002ac0000000000000019f0001e94d) dm-6 3PARdata,VV           size=500G features='0' hwhandler='1 alua' wp=rw


mpathh (360002ac0000000000000000b0001e94d) dm-10 3PARdata,VV              size=2.0T features='0' hwhandler='1 alua' wp=rw




4) 两个节点修改多路径配置文件


vim etc/multipath.conf


defaults { 
    user_friendly_names yes
    find_multipaths yes
    path_grouping_policy group_by_prio
    path_selector  "round-robin 0"
    rr_min_io         100
    max_fds           8192
    rr_weight         uniform
    failback            immediate
}


blacklist {
   wwid "3600508b1001c75bd3203be98f718eda4" #rac1
}




multipaths {
        multipath {
                wwid "360002ac0000000000000019c0001e94d"
                alias mpathocr1
        }
        multipath {
                wwid "360002ac0000000000000019d0001e94d"
                alias mpathocr2
        }
        multipath {
                wwid "360002ac0000000000000019e0001e94d"
                alias mpathocr3
        }
  multipath {
                wwid "360002ac0000000000000019f0001e94d"
                alias mpathdata1
        }
multipath {
                wwid "360002ac000000000000001a00001e94d"
                alias mpathdata2
        }
        multipath {
                wwid "360002ac000000000000001a10001e94d"
                alias mpathdata3
        }
        multipath {
                wwid "360002ac000000000000001a50001e94d"
                alias mpathdata4
        }
        multipath {
                wwid "360002ac0000000000000000c0001e94d"
                alias mpathdata5
        }
}


5) 两个节点排除本地盘做多路径
/usr/lib/udev/scsi_id -g -u -d dev/sda


6) 重命名这个配置文件
mv etc/multipath/bindings etc/multipath/bindings_bak


multipath -r
systemctl restart multipathd.service
systemctl enable multipathd.service


multipath -F
multipath -v2
multipath -ll


7) udev 配置文件编辑


取得DM_UUID:
cd dev/mapper
for i in `ls mpath*`; 
do 
  printf "%s %s\n" "$i" "$(udevadm info --query=all --name=/dev/mapper/$i |grep -i dm_uuid)"; 
  
done






mpathdata1 E: DM_UUID=mpath-360002ac0000000000000019f0001e94d
mpathdata2 E: DM_UUID=mpath-360002ac000000000000001a00001e94d
mpathdata3 E: DM_UUID=mpath-360002ac000000000000001a10001e94d
mpathdata4 E: DM_UUID=mpath-360002ac000000000000001a50001e94d
mpathdata5 E: DM_UUID=mpath-360002ac0000000000000000c0001e94d




mpathocr1 E: DM_UUID=mpath-360002ac0000000000000019c0001e94d
mpathocr2 E: DM_UUID=mpath-360002ac0000000000000019d0001e94d
mpathocr3 E: DM_UUID=mpath-360002ac0000000000000019e0001e94d




vi etc/udev/rules.d/99-oracle-asmdevices.rules


KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac0000000000000019c0001e94d",OWNER="grid",SYMLINK+="asm_ocr1",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac0000000000000019d0001e94d",OWNER="grid",SYMLINK+="asm_ocr2",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac0000000000000019e0001e94d",OWNER="grid",SYMLINK+="asm_ocr3",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac0000000000000019f0001e94d",OWNER="grid",SYMLINK+="asm_data1",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a00001e94d",OWNER="grid",SYMLINK+="asm_data2",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a10001e94d",OWNER="grid",SYMLINK+="asm_data3",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a50001e94d",OWNER="grid",SYMLINK+="asm_data4",GROUP="asmadmin",MODE="0660"
KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac0000000000000000c0001e94d",OWNER="grid",SYMLINK+="asm_data5",GROUP="asmadmin",MODE="0660"


配置完成后重新加载和生成udev规则
# 加载rules文件,重新加载udev rule
1: usr/sbin/udevadm trigger --type=devices --action=change
2: usr/sbin/udevadm trigger --type=devices --action=add
3: usr/sbin/udevadm trigger --subsystem-match=block 这个命令就可以在线识别了，不会影响到数据库。
4: usr/sbin/udevadm trigger 不会影响到数据库。
优先选择1-4
/sbin/udevadm control --reload    执行这个之前，需要网卡先设置参数，否则集群有问题
# 验证磁盘
ls dev/asm*


--linux8
udevadm control -R
udevadm trigger -t devices -s block


[root@hisservera rules.d]# scp 99-oracle-asmdevices.rules hisservera:/etc/udev/rules.d/


linux很多的人我发现都没有删除虚拟网桥，这个会影响192.168.122.x网段访问服务器，我这里单独写下。
yum remove libvirt-libs.x86_64

1）安装grid
[root@hisservera software]# chown -R grid:oinstall LINUX.X64_193000_grid_home.zip
[root@hisservera software]# su - grid
[grid@his1 ~]$ cd software/
[grid@his1 software]$ unzip LINUX.X64_193000_grid_home.zip -d $ORACLE_HOME
[root@hisservera software]# rpm -ivh u01/app/19.0.0/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm
安装时候打补丁
su - root
cd u01/app/19.0.0/grid/
mv OPatch OPatch.bak
unzip software/p6880880_190000_Linux-x86-64.zip -d u01/app/19.0.0/grid/
chown -R grid:oinstall OPatch
chmod -R 755 OPatch
解压19.20补丁
[root@hisservera software]# unzip p35319490_190000_Linux-x86-64.zip -d tmp
[root@hisservera software]# unzip p35354406_190000_Linux-x86-64.zip -d tmp
[root@hisservera software]# chown -R grid:oinstall tmp/35*


安装软件的同时也安装上RU和OJVM补丁：
[grid@hisservera ~]$ cd $ORACLE_HOME
[grid@hisservera grid]$ ./gridSetup.sh -applyRU tmp/35319490/ -applyOneOffs tmp/35354406/
正在准备要打补丁的主目录...
正在应用补丁程序 tmp/35319490/...
已成功应用补丁程序。
正在应用补丁程序 tmp/35354406/...
已成功应用补丁程序。
可以在以下位置找到日志: /tmp/GridSetupActions2023-09-20_12-33-44PM/installerPatchActions_2023-09-20_12-33-44PM.log
正在启动 Oracle Grid Infrastructure 安装向导...


...中途只选择安装hisservera单机rac...
...在asm磁盘组安装的时候选择不到磁盘，需要dd清空处理...
dd if=/dev/zero of=/dev/asm_ocr1 bs=1M count=100
dd if=/dev/zero of=/dev/asm_ocr2 bs=1M count=100
dd if=/dev/zero of=/dev/asm_ocr3 bs=1M count=100
dd if=/dev/zero of=/dev/asm_data1 bs=1M count=100
dd if=/dev/zero of=/dev/asm_data2 bs=1M count=100
dd if=/dev/zero of=/dev/asm_data3 bs=1M count=100
dd if=/dev/zero of=/dev/asm_data4 bs=1M count=100
dd if=/dev/zero of=/dev/asm_data5 bs=1M count=100
安装完成后
[grid@hisservera grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       hisservera               STABLE
ora.chad
               ONLINE  ONLINE       hisservera               STABLE
ora.net1.network
               ONLINE  ONLINE       hisservera               STABLE
ora.ons
               ONLINE  ONLINE       hisservera               STABLE
ora.proxy_advm
               OFFLINE OFFLINE      hisservera               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       hisservera               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       hisservera               STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       hisservera               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       hisservera               Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       hisservera               STABLE
ora.cvu
      1        ONLINE  ONLINE       hisservera               STABLE
ora.hisservera.vip
      1        ONLINE  ONLINE       hisservera               STABLE
ora.qosmserver
      1        ONLINE  ONLINE       hisservera               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       hisservera               STABLE
--------------------------------------------------------------------------------
SQL> create diskgroup DATA external redundancy disk '/dev/asm_data1' disk '/dev/asm_data2' disk '/dev/asm_data3' disk '/dev/asm_data4' disk '/dev/asm_data5' attribute 'au_size'='4m','compatible.asm'='19.0.0.0.0';


SQL> create diskgroup ARCH external redundancy disk '/dev/asm_data5' attribute 'au_size'='4m','compatible.asm'='19.0.0.0.0';


[grid@hisservera grid]$ opatch lspatches
35354406;OJVM RELEASE UPDATE: 19.20.0.0.230718 (35354406)
35553096;TOMCAT RELEASE UPDATE 19.0.0.0.0 (35553096)
35332537;ACFS RELEASE UPDATE 19.20.0.0.0 (35332537)
35320149;OCW RELEASE UPDATE 19.20.0.0.0 (35320149)
35320081;Database Release Update : 19.20.0.0.230718 (35320081)
33575402;DBWLM RELEASE UPDATE 19.0.0.0.0 (33575402)


OPatch succeeded.


2）安装db
[root@hisservera software]# chown -R oracle:oinstall software/
[oracle@hisservera software]$ unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME
su - root
cd u01/app/oracle/product/19.0.0/db_1
mv OPatch OPatch.bak
unzip software/p6880880_190000_Linux-x86-64.zip -d u01/app/oracle/product/19.0.0/db_1
chown -R oracle:oinstall OPatch
chmod -R 755 OPatch
[oracle@hisservera db_1]$ ./runInstaller -applyRU tmp/35319490/ -applyOneOffs tmp/35354406/
正在准备要打补丁的主目录...
正在应用补丁程序 tmp/35319490/...
已成功应用补丁程序。
正在应用补丁程序 tmp/35354406/...
已成功应用补丁程序。
可以在以下位置找到日志: /u01/app/oraInventory/logs/InstallActions2023-09-20_01-22-46PM/installerPatchActions_2023-09-20_01-22-46PM.log
正在启动 Oracle 数据库安装向导...


...安装完成后，不dbca建库.

5、RAC单机向另一台单机搭建第4个dg

搭建过程中我遇到一个问题，就是本地的磁盘空间不够，我想到一个办法就是先把asm磁盘组的磁盘划1t出来做lvm，整个业务完成后再恢复成asm磁盘。

第一步asm磁盘转lvm
[root@hisservera software]# df -Th
文件系统              类型      容量  已用  可用 已用% 挂载点
devtmpfs              devtmpfs  126G     0  126G    0% dev
tmpfs                 tmpfs     225G     0  225G    0% dev/shm
tmpfs                 tmpfs     126G   21M  126G    1% run
tmpfs                 tmpfs     126G     0  126G    0% sys/fs/cgroup
/dev/mapper/rhel-root xfs       543G   54G  489G   10%   <<空间不能存放
/dev/sda2             xfs       292M  154M  138M   53% boot
tmpfs                 tmpfs      26G   12K   26G    1% run/user/42
tmpfs                 tmpfs      26G     0   26G    0% run/user/0
tmpfs                 tmpfs      26G     0   26G    0% run/user/54322




crsctl start crs
su - grid


sqlplus / as sysasm


set line 220
col name for a10
col state for a10 
col COMPATIBILITY for a15
col database_compatibility for a15
select group_number,name,state,type,total_mb/1024 totol_gb,free_mb/1024 as free_gb,usable_file_mb/1024 usable_file_gb,allocation_unit_size/1024/1024 unit_mb,offline_disks,compatibility,database_compatibility,voting_files from v$asm_diskgroup order by 1;


set line 250 pages 222
col dno for a5
col name for a13
col state for a10
col path for a40
col FAILGROUP for a13
col header_status for a15
col rpt for 99999
select group_number||'_'||disk_number as dno,name,failgroup,failgroup_type,path,create_date,mount_date,mount_status,header_status,state,os_mb,total_mb,free_mb,voting_file,repair_timer rpt from v$asm_disk order by 1;


alter diskgroup data drop disk DATA_0002,DATA_0003  rebalance power 1024;
exit;
exit;
crsctl stop crs




vi /etc/udev/rules.d/99-oracle-asmdevices.rules
#KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a10001e94d",OWNER="grid",SYMLINK+="asm_data3",GROUP="asmadmin",MODE="0660"
#KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a50001e94d",OWNER="grid",SYMLINK+="asm_data4",GROUP="asmadmin",MODE="0660"


/usr/sbin/udevadm trigger --type=devices --action=change
/usr/sbin/udevadm trigger --type=devices --action=add


dd if=/dev/zero of=/dev/mapper/mpathdata3 bs=1M count=100
dd if=/dev/zero of=/dev/mapper/mpathdata4 bs=1M count=100


vgcreate oraclevg dev/mapper/mpathdata3
vgextend oraclevg dev/mapper/mpathdata4
lvcreate -l 100%FREE oraclevg -n oraclelv 
mkfs.xfs /dev/oraclevg/oraclelv


mkdir /hisdata


vi /etc/fstab                                  #### 修改fstab 开机自动挂载
/dev/oraclevg/oraclelv /hisdata xfs     defaults        0 0


chown -R oracle:oinstall hisdata

下一步则是搭建物理dg

[root@hisservera software]# tar -zxvf 11204linux.tar.gz
[root@hisservera software]# chown -R oracle:oinstall u02
[root@hisservera software]# mv u02 
3.3 关闭两个节点rac crs服务
[grid@hisservera ~]$ which crsctl
/u01/app/19.0.0/grid/bin/crsctl


[root@hisservera software]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hisservera'
CRS-2673: Attempting to stop 'ora.crsd' on 'hisservera'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'hisservera'
CRS-33673: 尝试在服务器 'hisservera' 上停止资源组 'ora.asmgroup'
CRS-2673: Attempting to stop 'ora.ARCH.dg' on 'hisservera'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'hisservera'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'hisservera'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'hisservera'
CRS-2673: Attempting to stop 'ora.chad' on 'hisservera'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hisservera'
CRS-2673: Attempting to stop 'ora.cvu' on 'hisservera'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'hisservera'
CRS-2677: Stop of 'ora.ARCH.dg' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hisservera'
CRS-2677: Stop of 'ora.cvu' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.hisservera.vip' on 'hisservera'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'hisservera'
CRS-2677: Stop of 'ora.hisservera.vip' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.asm' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'hisservera'
CRS-2677: Stop of 'ora.scan1.vip' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.chad' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'hisservera'
CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'hisservera' succeeded
CRS-33677: 在服务器 'hisservera' 上停止资源组 'ora.asmgroup' 成功。
CRS-2673: Attempting to stop 'ora.ons' on 'hisservera'
CRS-2677: Stop of 'ora.ons' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'hisservera'
CRS-2677: Stop of 'ora.net1.network' on 'hisservera' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'hisservera' has completed
CRS-2677: Stop of 'ora.crsd' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.storage' on 'hisservera'
CRS-2673: Attempting to stop 'ora.crf' on 'hisservera'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'hisservera'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'hisservera'
CRS-2677: Stop of 'ora.drivers.acfs' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.crf' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.storage' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hisservera'
CRS-2677: Stop of 'ora.mdnsd' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.asm' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'hisservera'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'hisservera'
CRS-2673: Attempting to stop 'ora.evmd' on 'hisservera'
CRS-2677: Stop of 'ora.ctssd' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.evmd' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'hisservera'
CRS-2677: Stop of 'ora.cssd' on 'hisservera' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'hisservera'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'hisservera'
CRS-2677: Stop of 'ora.gipcd' on 'hisservera' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'hisservera' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hisservera' has completed
CRS-4133: Oracle High Availability Services has been stopped.






ps -ef | grep d.bin | awk '{print $2}' | xargs kill -9
ps -ef |grep u01| awk '{print $2}' | xargs kill -9
[root@hisservera software]# ps -ef|grep d.bin --检查rac是否还有进程运行
root      27025  12165  0 04:54 pts/0    00:00:00 grep --color=auto d.bin




3.4 临时修改rac1 oracle 环境变量
[root@hisservera bin]# vi home/oracle/.bash_profile


export ORACLE_SID=hisdb;
export ORACLE_BASE=/u02/app/oracle;
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1;


[root@hisservera software]# source home/oracle/.bash_profile


一、环境
主库0.100 数据库是单机，路径在/rmanbak/hisdbdata,现有2个dg，


-------------------------节点1--------------节点2--------
OS系统                 redhat7.6             redhat7.9      
数据库版本             11.2.0.4 rac         11.2.0.4   
IP                     10.10.0.100          10.10.0.202             
软件                   装grid、db、netca、dbca  装db           
instance_name          hisdb1                 hisdb             
db_name                hisdb                  hisdb             
db_unique_name         hisdb,hisdg,hisdb1       hisdbdg              
tnsname.ora            hisdb、hisdg、hisdb1、hisdbdg                       
service_names          hisdb                  hisdb              
-------------------------存放路径规划--------------------
Primary                              Standby
/rmanbak/hisdbdata          hisdata/hisdb
/rmanbak/hisdbdata/log


/archivelog           hisdata/hisdb_arch


二、备库环境准备


2、1 拷贝主库的密码文件到备库


cd $ORACLE_HOME/dbs/
scp orapwhisdb1 oracle@10.10.0.202:/u02/app/oracle/product/11.2.0/db_1/dbs/
mv orapwhisdb1 orapwhisdb


2、2 准备备库linux的tnsnames.ora文件


vim u02/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
HISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)(UR=A)
    )
  )


HISDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.68)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdg)(UR=A)
    )
  )


hisdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.74)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)(UR=A)
    )
  )
#以下是增加的
hisdbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb) (UR=A)
    )
  )


2、3 准备备库的静态监听listener.ora


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hisdb)
      (ORACLE_HOME = u02/app/oracle/product/11.2.0/db_1)
      (SID_NAME = hisdb)
    )
  )


ADR_BASE_LISTENER = u02/app/oracle




2、4 准备备库的参数文件，然后启动到nomount状态
-------------------------存放路径规划--------------------
Primary                              Standby
/rmanbak/hisdbdata          hisdata/hisdb/
/archivelog           hisdata/hisdb_arch/


*.audit_file_dest='/u02/app/oracle/admin/hisdbdg/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/hisdata/hisdb/control01.ctl','/hisdata/hisdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/rmanbak/hisdbdata','/hisdata/hisdb'
*.db_name='hisdb'
*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='hisdbdg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisdbXDB)'
*.fal_server='hisdb'
*.log_archive_config='DG_CONFIG=(hisdb,hisdg,hisdb1,hisdbdg)'
*.log_archive_dest_1='LOCATION=/hisdata/hisdb_arch/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hisdbdg'
*.log_archive_dest_2='SERVICE=hisdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/rmanbak/hisdbdata/log','/hisdata/hisdb'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=6g
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.service_names='hisdb'
*.sga_max_size=30g
*.sga_target=30g
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*._optimizer_skip_scan_enabled=FALSE


备库oracle执行
mkdir -p hisdata/hisdb/
mkdir -p hisdata/hisdb_arch/
mkdir -p u02/app/oracle/admin/hisdbdg/{adump,dpdump,hdump,pfile}
mkdir -p u02/app/oracle/fast_recovery_area


vim u02/app/oracle/product/11.2.0/db_1/dbs/inithisdb.ora
create  spfile  from pfile='/u02/app/oracle/product/11.2.0/db_1/dbs/inithisdb.ora';
startup nomount;


lsnrctl stop
lsnrctl start




三、主库环境准备


3、1 主库设置强制日志
SQL> alter database force logging;
SQL> select force_logging,flashback_on from v$database;


FOR FLASHBACK_ON
--- ------------------
YES YES


3、2 主库创建standby日志


select * from v$Log;
select * from v$Logfile;
select * from v$standby_log;


----------------------------方法1------------------------------------------------------
alter database add standby logfile thread 1('+data','+fra') size 100M;
alter database add standby logfile thread 1('+data','+fra') size 100M;
alter database add standby logfile thread 1('+data','+fra') size 100M;
alter database add standby logfile thread 1('+data','+fra') size 100M;
alter database add standby logfile thread 1('+data','+fra') size 100M;
alter database add standby logfile thread 1('+data','+fra') size 100M;


alter database add standby logfile thread 2('+data','+fra') size 100M;
alter database add standby logfile thread 2('+data','+fra') size 100M;
alter database add standby logfile thread 2('+data','+fra') size 100M;
alter database add standby logfile thread 2('+data','+fra') size 100M;
alter database add standby logfile thread 2('+data','+fra') size 100M;
alter database add standby logfile thread 2('+data','+fra') size 100M;
-----------------------------方法2-------------------------------------------------------
alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m;
alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;


3、3 主库配置tnsnames.ora文件（rac两个节点oracle下修改）


和备库一样，复制上面的内容


3、4 主库RMAN duplicate数据库 到 linux备库


--前台运行方法
rman target sys/oracle@hisdb auxiliary sys/oracle@hisdbdg


duplicate target database for standby from active database;
--duplicate target database for standby from active database nofilenamecheck;


--后台运行方法
vi tmp/rmancopy.sh
#!/bin/bash
rman target sys/oracle@hisdb auxiliary sys/oracle@hisdbdg log=/tmp/rmancopy.log <<EOF
run{
duplicate target database for standby from active database;
}


chmod u+x tmp/rmancopy.sh
[oracle@xnlhisdb ~]$ nohup sh tmp/rmancopy.sh & >/tmp/1.txt




3、5 修改主库参数文件(先克隆数据库到备机，再来改主库参数)
select * from v$parameter where  lower(name) 
in ('log_archive_format','log_archive_dest_1','db_unique_name','log_archive_config','log_archive_dest_2','db_file_name_convert','log_file_name_convert'
,'hisdbdg_file_management','fal_client','fal_server','log_archive_dest_state_1','log_archive_dest_state_2','redo_transport_user','service_names');




--静态参数
alter system set db_unique_name='hisdb' scope=spfile sid='*'; --主库不改，用默认的
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';--主库不改，用默认的
alter system set db_file_name_convert='/hisdata/hisdbdg','+data/hisdb/datafile','/hisdata/hisdbdg','+data/hisdb/tempfile' scope=spfile sid='*'; --（不设置，因为目的不是搭建dg，而是升级备库）
alter system set log_file_name_convert='/hisdata/hisdbdg','+data/hisdb/onlinelog','/hisdata/hisdbdg','+fra/hisdb/onlinelog' scope=spfile sid='*';--（不设置，因为目的不是搭建dg，而是升级备库）


----主库参数调整到dumplicate后修改
--动态参数
--hisdb代表主库db_unique_name，standby代表备库的db_unique_name，在DataGuard中，主备库拥有相同的DB_NAME，为了区别，就必须有不同的DB_UNIQUE_NAME
alter system set log_archive_config='DG_CONFIG=(hisdb,hisdg,hisdb1,hisdbdg)' scope=both sid='*';
--主库,备库
--alter system set service_names='hisdb'  sid='*';
--alter system set log_archive_dest_state_1='enable'  sid='*';
--alter system set log_archive_dest_state_2='enable'  sid='*';
--alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hisdb'  sid='*';
--alter system set log_archive_dest_2='SERVICE=hisdbdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdbdg'  sid='*';
alter system set log_archive_dest_3='SERVICE=hisdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdbdg'  sid='*';


#以下参数不需要设置
alter system set standby_file_management=auto sid='*';
alter system set fal_client='hisdb1'  sid='hisdb1'; 
alter system set fal_client='hisdb2'  sid='hisdb2';
alter system set fal_server='hisdbdg'  sid='*';


备库参数：
*.fal_client='hisdbdg'
*.fal_server='hisdb1','hisdb2'
alter system set fal_server='hisdb1','hisdb2'  sid='*';




四、dg状态检查
说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。


alter database open;
alter database recover managed standby database using current logfile disconnect;


4、1 主备看状态
select open_mode,database_role,db_unique_name from v$database;


4、2 验证物理备库日志应用
1）主库上操作


create table cs (i int);
/
begin
 for i in 1..10000 loop
   insert into cs values (i);
   commit;
   dbms_lock.sleep(2);
  end loop;
end;
/
alter system switch logfile;
archive log list;
2）备库上查看


select * from cs;
archive log list;

下一步则是物理dg转逻辑dg



一、检查逻辑备库在主库不支持的相关表
+++++1、   检查逻辑备用数据库不支持的对象
-- 检查逻辑备库不支持的表
select distinct owner,table_name from dba_logstdby_unsupported  order by owner,table_name;
-- 精确查表表的哪一列
select  owner,table_name,column_name,data_type from dba_logstdby_unsupported where  table_name='';
确定如何处理不受支持的表。
如果要在主数据库上修改不支持的对象，则可以通过在执行升级过程所需的时间段内临时挂起对不支持的表的更改来执行升级。
如果可以防止更改不受支持的数据，则使用SQL Apply可能仍然是执行升级过程的可行方法。此方法要求从创建逻辑备用控制文件到完成升级之间，防止用户修改任何不受支持的表。例如，假设薪资部门更新对象表，但是该部门仅星期一至星期五更新数据库。但是，客户服务部门要求每周7天，每天24小时访问数据库，但仅使用受支持的数据类型和表。在这种情况下，您可以在一个周末内执行升级。您可以在DBA_LOGSTDBY_EVENTS视图中监视事务活动，并在执行首次切换之前中止升级（如有必要）。
如果无法防止在升级过程中更改不受支持的表，则发生的任何不受支持的事务都将记录在DBA_LOGSTDBY_EVENTS逻辑备用数据库上的表中。升级完成后，您也许可以使用Oracle Data Pump导出/导入实用程序将已更改的表导入到升级的数据库中。




+++++2、   在oracle11g 中，逻辑备用数据库本身不支持一下的数据类型：
   BFILE  VARRAYS  ROWID   嵌套表  UROWID   存储为对象关系的XML 类型、用户定义类型   二进制XML多媒体数据类型（例如空间数据、图像数据、oracle 文本）


+++++3、   查看主数据库中不含有主键或唯一键索引的表：
select owner,table_name,bad_column from  dba_logstdby_not_unique where table_name not in (select table_name from  dba_logstdby_unsupported) and bad_column='Y';
   注意BAD_COLUMN列值，该列有两个值：


   Y ：表示该表中有采用大数据类型的字段，比如LONG啦，CLOB啦之类。如果表中除log列某些行记录完全匹配，则该表无法成功应用于逻辑standby。
       standby会尝试维护这些表，不过你必须保证应用不允许。
   N ：表示该表拥有足够的信息，能够支持在逻辑standby的更新，不过仍然建议你为该表创建一个主键或者唯一索引/约束以提高log应用效率
假设在某张表中你可以确认数据是唯一的，但是基于效率方面的考虑，不想为其创建主键或唯一约束，怎么办呢？没关系，Oracle早想到了这一点，你可以创建一个DISABLE的Primary-Key Rely约束。


Add a disabled primary-key RELY constraint.


ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;


--备库停掉
alter database stop logical standby apply;


--主库执行
ALTER TABLE zlhis.医保就诊登记 ADD PRIMARY KEY (记录id,病人id,就诊编号,结算编号,序号) RELY DISABLE;


--备库打开
alter database start logical standby apply immediate;


二、将物理备库转换为逻辑备库
11g新特性:Rolling Upgrade With Physical Standby
从Oracle 10.1.0.3开始引入了利用逻辑备库(logical standby)实施滚动升级(rolling upgrade)的特性；在滚动升级期间(rolling upgrade)，
允许主库(primary database)与逻辑备库(logical standby)间运行不同的数据库版本，以最小化宕机时间。
到了11g中增加了可以将物理备库(physical standby)临时性转换成逻辑备库(logical standby)以完成滚动升级，之后将该临时逻辑备库反转为物理备库的功能。
使用以上临时转换功能只需要在转换语句"ALTER DATABASE RECOVER TO LOGICAL STANDBY"后加上"KEEP IDENTITY"选项。除去转换细节的区别外，
使用物理备库进行滚动升级的过程与10g中的逻辑备库滚动升级没有太大的区别。


当前的需求是将生产数据库升级到12.2.0.3，但要求最小化应用downtime；这里我们就可以充分利用Data Guard的环境借势升级，同时也不会破坏原有的HA可用性：
备库操作：
alter database recover managed standby database cancel;
shutdown immediate;
startup nomount;
alter database mount standby database;




主库操作：
之后需要在Primary Database中创建Logminer dictionary日志挖掘字典：
SQL> drop user zldhc cascade;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
--如果一直卡住处理长事物
with transaction_details as
( select inst_id
  , ses_addr
  , sysdate - start_date as diff
  from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
             || ' days, '
             || to_char(trunc(mod(t.diff * 24,24)))
             || ' hours, '
             || to_char(trunc(mod(t.diff * 24 * 60,24)))
             || ' minutes, '
             || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
             || ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc




alter system kill session '247,62496' immediate;
alter system kill session '81,27857' immediate;






备库操作：
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
SQL> select database_role from v$database;


DATABASE_ROLE
----------------
LOGICAL STANDBY


SQL> alter database open;


我们还需要完成一系列逻辑备库的配置:


a.在逻辑备库中禁用自动删除外部存档日志
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'FALSE' ); #BUG 12353543 version 11.2


SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
如果报错：
/* 缺少DBMS_LOGSTDBY包，可以从以下脚本创建 */


SQL> @?/rdbms/admin/dbmslsby.sql


b.执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中


SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS);
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');


c.启动在逻辑备库上的SQL APPLY:（因为windows打了补丁，所以不需要马上验证同步情况，因为linux没有windows那些补丁，开启下面的同步，数据也不会过来。会报找不到xx补丁的错误。所以我们直接跳过。开始升级，升级到12c后再打开同步即可。）----（不执行该步骤）
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;




三、备库表空间文件添加处理（防止升级前数据文件路径问题）--升级前先检查表空间情况


3.1 对于逻辑Standby数据库，你甚至可以通过编写自定义的PROCEDURE，来重新定义SQL应用时执行的操作。
如逻辑Standby数据库的文件路径与Primary数据库路径不同，如果是物理Standby，可以通过*_FILE_NAME_CONVERT之类的参数处理，在逻辑Standby环境中这几个参数无效，应该如何处理呢？


答案就是通过编写自定义的过程，修改SQL应用时执行的操作。
首先当然是创建一个过程，建议创建在SYS下，因为在这个用户下的操作肯定不会有同步的问题，如下所示：


SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (  
OLD_STMT  IN  VARCHAR2,  
STMT_TYP  IN  VARCHAR2,  
SCHEMA    IN  VARCHAR2,  
NAME      IN  VARCHAR2,  
XIDUSN    IN  NUMBER,  
XIDSLT    IN  NUMBER,  
XIDSQN    IN  NUMBER,  
ACTION    OUT NUMBER,  
NEW_STMT  OUT VARCHAR2  
) AS 


BEGIN 


--NEW_STMT := REPLACE(OLD_STMT, 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\','/oradata/orcldg/');  
NEW_STMT := REPLACE(OLD_STMT, '/rmanbak/hisdbdata','/hisdata/hisdb');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;  


EXCEPTION  


    WHEN OTHERS THEN 
      ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;  
      NEW_STMT := NULL;  
END HANDLE_TBS_DDL;  


/  


逻辑非常简单，基本上就是一个REPLACE，不过PROCEDURE中声明的变量看起来很多，这个是固定格式，不建议修改。
停止逻辑Standby的SQL应用：
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;  
如果不停，PROCEDURE不能生效。


执行DBMS_LOGSTDBY.SKIP过程，将编写的过程注册到表空间处理的SQL应用中：


SQL> EXEC DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');  


这里也要借助DBMS_LOGSTDBY.SKIP过程实现。该过程功能非常强大，而且操作非常灵活。
重启SQL应用：


SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


3.2 查看备库应用


--1、检查归档日志应用
set line 200 pages 2222
col file_name for a50
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,NEXT_CHANGE# AS N_SCN#, TIMESTAMP, DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR#, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
--2、检查业务表同步情况
SELECT  max(登记时间) from ZLHIS.病人挂号记录 where 登记时间  >sysdate-0.5;
SELECT max(开嘱时间) from ZLHIS.病人医嘱记录 where 开嘱时间  >sysdate-0.5;
SELECT  max(登记时间) from ZLHIS.门诊费用记录 where 登记时间  >sysdate-0.5;
SELECT  max(登记时间) from ZLHIS.住院费用记录 where 登记时间  >sysdate-0.5;
--3、检查实时应用的语句
select ls.serial# "Apply Process"
         , sas.state "State"
         , sas.sid SID
         , s.sql_address "SQL Address"
         , s.sql_hash_value "SQL Hash Value"
         ,s.SQL_ID
         , sa.sql_text "SQL Text"
     from v$logstdby ls
       , v$streams_apply_server sas
       , v$session s
      , v$sqlarea sa
    where ls.type = 'APPLIER'
    and sas.state != 'IDLE'
    and sas.serial# = ls.serial#
    and s.sid = sas.sid
    and sa.address (+) = s.sql_address
    and sa.hash_value (+) = s.sql_hash_value;
--4、检查目前应用到哪个时段
SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;


--5、 检查和处理未应用或者报错的表
SELECT XIDUSN, XIDSLT, XIDSQN , status , EVENT_TIME,to_char(EVENT), STATUS FROM DBA_LOGSTDBY_EVENTS 
where EVENT not like '%SYSMAN%' and EVENT not like '%SYSLS%' and to_char(EVENT) is not null  ORDER BY EVENT_TIMESTAMP;


SELECT distinct trunc(event_time),to_char(EVENT) FROM DBA_LOGSTDBY_EVENTS where EVENT not like '%SYSMAN%' and EVENT not like '%SYSLS%' and to_char(EVENT) is not null; 


select STATEMENT_OPT,OWNER,NAME from DBA_LOGSTDBY_SKIP;


--跳过表的应用


alter database stop logical standby apply;
begin DBMS_LOGSTDBY.SKIP('DML', 'SYS', 'aud$');  end;
begin DBMS_LOGSTDBY.SKIP('DML', 'ZLHIS', '检验复查明细');  end;
select STATEMENT_OPT,OWNER,NAME from DBA_LOGSTDBY_SKIP;
alter database start logical standby apply immediate;


--跳过某个事物
alter database stop logical standby apply;
begin dbms_logstdby.skip_transaction (19,10,306300); end;
alter database start logical standby apply immediate;

6、升级操作

1、 主库停止传输
SQL> alter system set log_archive_dest_state_3 = DEFER scope=both;


2、 备库停止日志应用
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; 


3、备库执行升级前工具（11204数据库打开的情况下操作）
--为了防止出现bug，用打补丁前的19.3的preupgrade.jar
[root@hisservera hisdata]# cd software/
[root@hisservera software]# ll
总用量 15800452
-rw-r--r--  1 root   root     2460911159 9月  20 15:54 11204linux.tar.gz
-rw-r--r--. 1 oracle oinstall     195388 9月  19 22:40 compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
-rw-r--r--. 1 oracle oinstall 3059705302 9月  19 22:34 LINUX.X64_193000_db_home.zip
-rw-r--r--. 1 oracle oinstall 2889184573 9月  19 22:35 LINUX.X64_193000_grid_home.zip
-rw-r--r--. 1 oracle oinstall 2990370786 9月  19 22:36 p35319490_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall  127314614 9月  19 22:39 p35354406_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall  125167420 9月  19 22:36 p6880880_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall 4526702592 9月  19 22:37 rhel-server-7.9-x86_64-dvd.iso
-rw-r--r--. 1 oracle oinstall      97664 9月  19 22:40 rlwrap-0.42-1.el7.x86_64.rpm
[root@hisservera software]# mkdir oracletmp
[root@hisservera software]# chown -R oracle:oinstall oracletmp/
[root@hisservera software]# unzip LINUX.X64_193000_db_home.zip -d ./oracletmp


[oracle@hisservera ~]$ u02/app/oracle/product/11.2.0/db_1/jdk/bin/java -jar software/oracletmp/rdbms/admin/preupgrade.jar
==================
PREUPGRADE SUMMARY
==================
  u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/preupgrade.log
  u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/preupgrade_fixups.sql
  u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/postupgrade_fixups.sql


Execute fixup scripts as indicated below:


Before upgrade:


Log into the database and execute the preupgrade fixups
@/u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/preupgrade_fixups.sql


After the upgrade:


Log into the database and execute the postupgrade fixups
@/u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/postupgrade_fixups.sql


Preupgrade complete: 2023-09-20T21:43:17




3.1、备库预升级检查
Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
     19c ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
[oracle@his1 admin]$ cp u01/app/oracle/product/19.0.0/db_1/rdbms/admin/emremove.sql u02/app/oracle/product/11.2.0/db_1/rdbms/admin/


sqlplus  as sysdba
SQL> alter database guard none; --不执行
SQL> alter session disable guard;
@/u02/app/oracle/product/11.2.0/db_1/rdbms/admin/emremove.sql


--11g home执行
@$ORACLE_HOME/olap/admin/catnoamd.sql
SQL> alter database guard all; --不执行


3.2、旧库执行
SQL> @/u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/preupgrade_fixups.sql --直接执行
Executing Oracle PRE-Upgrade Fixup Script


Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2023-09-20 21:43:10


For Source Database:     HISDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0


Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  two_pc_txn_exist          NO          Manual fixup required.
    2.  parameter_obsolete        NO          Manual fixup recommended.
    3.  em_present                YES         None.
    4.  invalid_objects_exist     NO          Manual fixup recommended.
    5.  amd_exists                YES         None.
    6.  network_acl_priv          NO          Manual fixup recommended.
    7.  exclusive_mode_auth       NO          Manual fixup recommended.
    8.  apex_manual_upgrade       NO          Manual fixup recommended.
    9.  case_insensitive_auth     NO          Manual fixup recommended.
   10.  streams_setup             NO          Manual fixup recommended.
   11.  mv_refresh                NO          Informational only.
                                              Further action is optional.
   12.  hidden_params             NO          Informational only.
                                              Further action is optional.
   13.  dictionary_stats          YES         None.
   14.  trgowner_no_admndbtrg     YES         None.
   15.  mv_refresh                NO          Informational only.
                                              Further action is optional.
   16.  pre_fixed_objects         YES         None.
   17.  parameter_deprecated      NO          Informational only.
                                              Further action is optional.
   18.  exf_rul_exists            NO          Informational only.
                                              Further action is optional.
   19.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
   20.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.


The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.


PL/SQL procedure successfully completed.


4、备库关监听和库
SQL> shutdown immediate
[oracle@hisservera ~]$ lsnrctl stop


5、备库切换环境变量到rac
[oracle@hisservera ~]$ vi ~/.bash_profile 


export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=hisdb1  #(节点2改为hisdb2)
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1
#export ORACLE_SID=hisdb;
#export ORACLE_BASE=/u02/app/oracle;
#export ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1;


[oracle@hisservera ~]$ source ~/.bash_profile 


6、 备库单节点打开rac crs服务
[root@hisservera ~]# u01/app/19.0.0/grid/bin/crsctl start crs


7、备库rac1 在oracle_home/dbs下新建参数文件


[oracle@hisservera ~]$ cd $ORACLE_HOME/dbs
[oracle@hisservera dbs]# vi inithisdb1.ora


*._cleanup_rollback_entries=10000
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._optimizer_skip_scan_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/hisdbdg/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA','+ARCH'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_name_convert='/rmanbak/hisdbdata','+DATA'
*.db_files=8192
*.db_name='hisdb'
*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest_size=100G
*.db_unique_name='hisdbdg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisdbXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.event='10503 trace name context forever, level 4000'
family:dw_helper.instance_mode='read-only'
*.fal_server='hisdb'
hisdb1.instance_number=1
*.log_archive_config='DG_CONFIG=(hisdb,hisdbdg)'
*.log_archive_dest_2='SERVICE=hisdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdb'
*.log_file_name_convert='/rmanbak/hisdbdata/log','+DATA'
*.local_listener='-oraagent-dummy-'
*.log_buffer=104857600
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=5000
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_limit=0
*.pga_aggregate_target=40G
*.processes=5000
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=300
*.service_names='hisdb'
*.sga_max_size=159G
*.sga_target=159G
*.standby_file_management='AUTO'
hisdb1.thread=1
*.undo_retention=10800
hisdb1.undo_tablespace='UNDOTBS1'


[oracle@hisservera dbs]$ mkdir -p u01/app/oracle/admin/hisdbdg/adump


8、备库rac1启动到nomount并创建spfile到asm
startup nomount;
SQL> create spfile='+data' from pfile;


9、备库rac使用asm的spfile启动到nomount
[oracle@hisservera dbs]$ mv inithisdb1.ora inithisdb1.ora_old
[oracle@hisservera dbs]# vi inithisdb1.ora
spfile='+data/hisdbdg/parameterfile/spfile.256.1148094475'


[oracle@his1 dbs]$ sqlplus  as sysdba
startup nomount;
exit;


10、备库rac1恢复控制文件并启动到mount
[root@his1 ~]# cd hisdata/
[root@his1 oradata]# chown -R oracle:asmadmin hisdb*


注意control01.ctl的权限必须是oracle:asmadmin,不然要报错，找不到备份


[oracle@hisservera hisdbdg]$ rman target 
RMAN> restore controlfile from '/hisdata/hisdb/control01.ctl';


##备份日志文件，等会就不用重新搭建dg，再从这里开始做
SQL> select 'cp -p '|| member|| ' tmp/logbak/' from v$logfile;


'CP-P'||MEMBER||'/TMP/LOGBAK/'
--------------------------------------------------------------------------------
cp -p hisdata/hisdb/redo22.log.285 tmp/logbak/
cp -p hisdata/hisdb/group_22.3315 tmp/logbak/
cp -p hisdata/hisdb/redo21.log.279 tmp/logbak/
cp -p hisdata/hisdb/redo21.log.3272 tmp/logbak/
cp -p hisdata/hisdb/redo24.log.293 tmp/logbak/
cp -p hisdata/hisdb/group_24.3321 tmp/logbak/
cp -p hisdata/hisdb/redo23.log.309 tmp/logbak/
cp -p hisdata/hisdb/group_23.3320 tmp/logbak/
cp -p hisdata/hisdb/group_14.341 tmp/logbak/
cp -p hisdata/hisdb/group_14.440 tmp/logbak/
cp -p hisdata/hisdb/group_13.340 tmp/logbak/
cp -p hisdata/hisdb/group_13.446 tmp/logbak/
cp -p hisdata/hisdb/group_12.339 tmp/logbak/
cp -p hisdata/hisdb/group_12.453 tmp/logbak/
cp -p hisdata/hisdb/group_11.338 tmp/logbak/
cp -p hisdata/hisdb/group_11.437 tmp/logbak/
cp -p hisdata/hisdb/stdredo01.log tmp/logbak/
cp -p hisdata/hisdb/stdredo02.log tmp/logbak/
cp -p hisdata/hisdb/stdredo03.log tmp/logbak/
cp -p hisdata/hisdb/stdredo04.log tmp/logbak/


[oracle@hisservera hisdb]$ mkdir tmp/logbak
...执行上面的cp命令...


11、备库rac1 恢复fs文件到asm
rman target 
backup as copy database format '+data';
switch database to copy;


临时文件未转换到asm
使用sqlplus 改变路径
SQL> select name from v$tempfile;


NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/hisdbdg/temp.339.1024168759
/u02/app/oracle/oradata/hisdbdg/bhtemp.dbf
/u02/app/oracle/oradata/hisdbdg/hoatemp.dbf
/u02/app/oracle/oradata/hisdbdg/temp02.dbf


set line 200
select 'alter database rename file  ''' || name || ''' to ''' || '+data'|| ''';' from v$tempfile;


'ALTERDATABASERENAMEFILE'''||NAME||'''TO'''||'+DATA'||''';'
-------------------------------------------------------------------------------------------------------------------------------
alter database rename file  '/u02/app/oracle/oradata/hisdbdg/temp.339.1024168759' to '+data';
alter database rename file  '/u02/app/oracle/oradata/hisdbdg/bhtemp.dbf' to '+data';
alter database rename file  '/u02/app/oracle/oradata/hisdbdg/hoatemp.dbf' to '+data';
alter database rename file  '/u02/app/oracle/oradata/hisdbdg/temp02.dbf' to '+data';




12、备库rac1 密码文件处理
将/u02下的dbs/orapwhisdb 拷贝到 u01目录下并修改,rac2暂时不处理，因为后面注册数据库到ocr需要将密码文件放到asm，同时删除本地的密码文件。


[oracle@hisservera hisdata]$ cd u02/app/oracle/product/11.2.0/db_1/dbs/
[oracle@hisservera dbs]$ cp orapwhisdb u01/app/oracle/product/19.0.0/db_1/dbs/
mv orapwhisdb orapwhisdb1


13、备库rac1 添加tnsnames.ora sqlnet.ora处理
oracle用户添加：


[oracle@hisservera admin]$ pwd
/u01/app/oracle/product/19.0.0/db_1/network/admin
[oracle@hisservera admin]$ vi tnsnames.ora
HISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)(UR=A)
    )
  )


HISDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.68)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdg)(UR=A)
    )
  )


hisdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.74)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb)(UR=A)
    )
  )
#以下是增加的
hisdbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hisdb) (UR=A)
    )
  )


[oracle@his1 admin]$ vi sqlnet.ora 
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8


14、备库rac1 添加静态监听
grid用户添加：
[grid@hisservera admin]$ pwd
/u01/app/19.0.0/grid/network/admin
[grid@hisservera admin]$ vi listener.ora


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hisdb)
      (ORACLE_HOME = u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = hisdb1)
    )
  )
  
  15、备库rac1升级到19c
  
SQL> startup nomount;
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE scope=spfile; --集群参数关闭
SQL> SHUTDOWN IMMEDIATE


SQL> startup upgrade;


alter database add logfile thread 1 group 15  ('+data') size 4G;
alter database add logfile thread 1 group 16  ('+data') size 4G;


--19c oracle目录
[oracle@his1 dbs]$ cd $ORACLE_HOME/rdbms/admin
[oracle@his1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
+++++++升级脚本介绍
4 (默认) – 最大: 8个并行进程
中断可继续运行:
$  dbupgrade -R -l tmp/logs
封装脚本:
catctl.pl
$ perl catctl.pl -n 4 -l tmp/logs catupgrd.sql
+++++++
[oracle@hisservera admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql


Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 8
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0


catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.20.0.0.0DBRU_LINUX.X64_230621]




/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/db_1]
/u01/app/oracle/product/19.0.0/db_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/db_1]


Analyzing file u01/app/oracle/product/19.0.0/db_1/rdbms/admin/catupgrd.sql


Log file directory = [/tmp/cfgtoollogs/upgrade20230921001521]


catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20230921001521/catupgrd_catcon_95011.lst]


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20230921001521/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20230921001521/catupgrd_*.lst] files for spool files, if any




Number of Cpus        = 128
Database Name         = hisdbdg
DataBase Version      = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523/cat


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523/catupgrd*.log] files for output gene


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523/catupgrd_*.lst] files for spool file




Log file directory = [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523]


Parallel SQL Process Count            = 8
Components in [hisdbdg]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
DataBase Version      = 11.2.0.4.0


------------------------------------------------------
Phases [0-107]         Start Time:[2023_09_21 00:15:24]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [hisdbdg] Files:1    Time: 235s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [hisdbdg] Files:5    Time: 72s
Restart  Phase #:2    [hisdbdg] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [hisdbdg] Files:19   Time: 28s
Restart  Phase #:4    [hisdbdg] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [hisdbdg] Files:7    Time: 29s
*****************   Catproc Start   ****************
Serial   Phase #:6    [hisdbdg] Files:1    Time: 16s
*****************   Catproc Types   ****************
Serial   Phase #:7    [hisdbdg] Files:2    Time: 15s
Restart  Phase #:8    [hisdbdg] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [hisdbdg] Files:71   Time: 17s
Restart  Phase #:10   [hisdbdg] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [hisdbdg] Files:1    Time: 79s
Restart  Phase #:12   [hisdbdg] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [hisdbdg] Files:95   Time: 3s
Restart  Phase #:14   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:15   [hisdbdg] Files:123  Time: 7s
Restart  Phase #:16   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:17   [hisdbdg] Files:26   Time: 3s
Restart  Phase #:18   [hisdbdg] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [hisdbdg] Files:32   Time: 9s
Restart  Phase #:20   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:21   [hisdbdg] Files:3    Time: 13s
Restart  Phase #:22   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:23   [hisdbdg] Files:25   Time: 102s
Restart  Phase #:24   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:25   [hisdbdg] Files:12   Time: 66s
Restart  Phase #:26   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:27   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:28   [hisdbdg] Files:7    Time: 5s
Serial   Phase #:29   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:30   [hisdbdg] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:32   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:34   [hisdbdg] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [hisdbdg] Files:298  Time: 26s
Serial   Phase #:36   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:37   [hisdbdg] Files:1    Time: 1s
Serial   Phase #:38   [hisdbdg] Files:10   Time: 5s
Restart  Phase #:39   [hisdbdg] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [hisdbdg] Files:3    Time: 43s
Restart  Phase #:41   [hisdbdg] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [hisdbdg] Files:13   Time: 62s
Restart  Phase #:43   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:44   [hisdbdg] Files:11   Time: 6s
Restart  Phase #:45   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:46   [hisdbdg] Files:3    Time: 1s
Restart  Phase #:47   [hisdbdg] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [hisdbdg] Files:1    Time: 24s
Restart  Phase #:49   [hisdbdg] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [hisdbdg] Files:1    Time: 34s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:52   [hisdbdg] Files:1    Time: 0s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [hisdbdg] Files:2    Time: 368s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:56   [hisdbdg] Files:3    Time: 23s
Serial   Phase #:57   [hisdbdg] Files:3    Time: 2s
Parallel Phase #:58   [hisdbdg] Files:10   Time: 2s
Parallel Phase #:59   [hisdbdg] Files:25   Time: 3s
Serial   Phase #:60   [hisdbdg] Files:4    Time: 8s
Serial   Phase #:61   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:62   [hisdbdg] Files:32   Time: 3s
Serial   Phase #:63   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:64   [hisdbdg] Files:6    Time: 6s
Serial   Phase #:65   [hisdbdg] Files:2    Time: 15s
Serial   Phase #:66   [hisdbdg] Files:3    Time: 66s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:69   [hisdbdg] Files:1    Time: 1s
Parallel Phase #:70   [hisdbdg] Files:2    Time: 31s
Restart  Phase #:71   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:72   [hisdbdg] Files:2    Time: 1s
Serial   Phase #:73   [hisdbdg] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:76   [hisdbdg] Files:1    Time: 31s
Serial   Phase #:77   [hisdbdg] Files:2    Time: 2s
Restart  Phase #:78   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:79   [hisdbdg] Files:1    Time: 19s
Restart  Phase #:80   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:81   [hisdbdg] Files:3    Time: 24s
Restart  Phase #:82   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:83   [hisdbdg] Files:1    Time: 5s
Restart  Phase #:84   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:85   [hisdbdg] Files:1    Time: 8s
Restart  Phase #:86   [hisdbdg] Files:1    Time: 0s
Parallel Phase #:87   [hisdbdg] Files:4    Time: 54s
Restart  Phase #:88   [hisdbdg] Files:1    Time: 1s
Serial   Phase #:89   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:90   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:91   [hisdbdg] Files:2    Time: 7s
Restart  Phase #:92   [hisdbdg] Files:1    Time: 0s
Serial   Phase #:93   [hisdbdg] Files:1    Time: 0s
Restart  Phase #:94   [hisdbdg] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [hisdbdg] Files:1    Time: 18s
Restart  Phase #:96   [hisdbdg] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [hisdbdg] Files:1    Time: 3s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [hisdbdg] Files:1    Time: 101s
*******************   Migration   ******************
Serial   Phase #:99   [hisdbdg] Files:1    Time: 37s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [hisdbdg] Files:1    Time: 1s
Serial   Phase #:101  [hisdbdg] Files:1    Time: 0s
Serial   Phase #:102  [hisdbdg] Files:1    Time: 65s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [hisdbdg] Files:1    Time: 56s
****************   Summary report   ****************
Serial   Phase #:104  [hisdbdg] Files:1    Time: 0s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [hisdbdg] Files:1    Time: 1s
Serial   Phase #:106  [hisdbdg] Files:1    Time: 0s
Serial   Phase #:107  [hisdbdg] Files:1     Time: 132s


------------------------------------------------------
Phases [0-107]         End Time:[2023_09_21 00:48:42]
------------------------------------------------------


Grand Total Time: 1998s 


 LOG FILES: (/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523/catupgrd*.log)


Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/hisdbdg/upgrade20230921001523/upg_summary.log


Grand Total Upgrade Time:    [0d:0h:33m:18s]


16、备库rac1 升级后处理
sqlplus  as sysdba
startup
SQL> alter trigger SYS.GGS_DDL_TRIGGER_BEFORE disable;
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Enter value for 1: 


Oracle Database Release 19 Post-Upgrade Status Tool    09-21-2023 00:51:1
Database Name: HISDB


Component        Current   Full   Elapsed Time
Name          Status    Version  HH:MM:SS


Oracle Server             UPGRADED     19.20.0.0.0  00:14:59
JServer JAVA Virtual Machine         UPGRADED     19.20.0.0.0  00:03:27
Oracle XDK             UPGRADED     19.20.0.0.0  00:00:29
Oracle Database Java Packages         UPGRADED     19.20.0.0.0  00:00:11
OLAP Analytic Workspace          UPGRADED     19.20.0.0.0  00:00:15
Oracle Text             UPGRADED     19.20.0.0.0  00:00:51
Oracle Workspace Manager         UPGRADED     19.20.0.0.0  00:00:51
Oracle Real Application Clusters       UPGRADED     19.20.0.0.0  00:00:00
Oracle XML Database           UPGRADED     19.20.0.0.0  00:02:06
Oracle Multimedia           UPGRADED     19.20.0.0.0  00:00:32
Spatial              UPGRADED     19.20.0.0.0  00:02:29
Oracle OLAP API            UPGRADED     19.20.0.0.0  00:00:11
Datapatch               00:01:32
Final Actions               00:02:18
Post Upgrade               00:00:53


Total Upgrade Time: 00:29:53


Database time zone version is 14. It is older than current release time
zone version 42. Time zone upgrade is needed using the DBMS_DST package.




时区升级：
-------升级后和时区更改--------------
SQL> @?/rdbms/admin/utltz_upg_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv42 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.


Session altered.


SQL> @?/rdbms/admin/utltz_upg_apply.sql


Session altered.


INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv42 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.


Total System Global Area 3.4360E+11 bytes
Fixed Size       30156920 bytes
Variable Size     4.4023E+10 bytes
Database Buffers   2.9904E+11 bytes
Redo Buffers      506712064 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.


Total System Global Area 3.4360E+11 bytes
Fixed Size       30156920 bytes
Variable Size     4.4023E+10 bytes
Database Buffers   2.9904E+11 bytes
Redo Buffers      506712064 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv42 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.


Session altered.


set line 200
col PROPERTY_NAME format a30
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  FROM DATABASE_PROPERTIES  WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;


select * from v$timezone_file;


--升级后脚本执行
SQL> @/u02/app/oracle/cfgtoollogs/hisdbdg/preupgrade/postupgrade_fixups.sql


Session altered.




PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.




Package created.


No errors.


Package body created.




PL/SQL procedure successfully completed.


No errors.








1 row selected.




Package created.


No errors.


Package body created.


No errors.
Executing Oracle POST-Upgrade Fixup Script


Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2023-09-20 21:43:16


For Source Database:     HISDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0


Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
   21.  depend_usr_tables         YES         None.
   22.  old_time_zones_exist      YES         None.
   23.  dir_symlinks              YES         None.
   24.  post_dictionary           YES         None.
   25.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
   26.  upg_by_std_upgrd          YES         None.


The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.


PL/SQL procedure successfully completed.




Session altered.


SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlusts.sql


Oracle Database Release 19 Post-Upgrade Status Tool    09-21-2023 01:03:3
Database Name: HISDB


Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS


Oracle Server                             VALID     19.20.0.0.0  00:14:59
JServer JAVA Virtual Machine              VALID     19.20.0.0.0  00:03:27
Oracle XDK                                VALID     19.20.0.0.0  00:00:29
Oracle Database Java Packages             VALID     19.20.0.0.0  00:00:11
OLAP Analytic Workspace                   VALID     19.20.0.0.0  00:00:15
Oracle Text                               VALID     19.20.0.0.0  00:00:51
Oracle Workspace Manager                  VALID     19.20.0.0.0  00:00:51
Oracle Real Application Clusters          VALID     19.20.0.0.0  00:00:00
Oracle XML Database                       VALID     19.20.0.0.0  00:02:06
Oracle Multimedia                         VALID     19.20.0.0.0  00:00:32
Spatial                                   VALID     19.20.0.0.0  00:02:29
Oracle OLAP API                           VALID     19.20.0.0.0  00:00:11
Datapatch                                                        00:01:32
Final Actions                                                    00:02:18
Post Upgrade                                                     00:00:53
Post Compile                                                     00:02:01


Total Upgrade Time: 00:31:54


Database time zone version is 42. It meets current release needs.




升级后查询确认：
select * from DBA_REGISTRY_HISTORY;
select comp_name,status,version from dba_server_registry;
select comp_id,version,status from dba_registry;  
select * from v$version;
show parameter compatible


升级后的检查：
select * from utl_recomp_errors;
如果出现未选定行，说明升级没有错误

6、升级后操作

1、redo处理
1) 重建在fs上的redo
select * from v$log;
select * from v$logfile;
select * from v$standby_log;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;


alter database disable thread 2;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;


alter database add logfile thread 1 group 11  ('+data') size 300m;
alter database add logfile thread 1 group 12  ('+data') size 300m;
alter database add logfile thread 1 group 13  ('+data') size 300m;
alter database add logfile thread 1 group 14  ('+data') size 300m;
alter database add logfile thread 1 group 15  ('+data') size 300m;


alter database add logfile thread 2 group 21  ('+data') size 300m;
alter database add logfile thread 2 group 22  ('+data') size 300m;
alter database add logfile thread 2 group 23  ('+data') size 300m;
alter database add logfile thread 2 group 24  ('+data') size 300m;
alter database add logfile thread 2 group 25  ('+data') size 300m;


alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 15;
alter database drop logfile group 16;


alter database disable thread 2;
alter database drop logfile group 3;


2) 重建在fs上的standby（每个线程添加6组）




alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;


alter database add standby logfile thread 1 group 31 ('+data') size 300m;
alter database add standby logfile thread 1 group 32 ('+data') size 300m;
alter database add standby logfile thread 1 group 33 ('+data') size 300m;
alter database add standby logfile thread 1 group 34 ('+data') size 300m;
alter database add standby logfile thread 1 group 35 ('+data') size 300m;
alter database add standby logfile thread 1 group 36 ('+data') size 300m;


alter database add standby logfile thread 2 group 41 ('+data') size 300m;
alter database add standby logfile thread 2 group 42 ('+data') size 300m;
alter database add standby logfile thread 2 group 43 ('+data') size 300m;
alter database add standby logfile thread 2 group 44 ('+data') size 300m;
alter database add standby logfile thread 2 group 45 ('+data') size 300m;
alter database add standby logfile thread 2 group 46 ('+data') size 300m;


3) 打开线程2，并为各实例设置使用的线程
alter database enable thread 2;
alter system set thread=1 scope=spfile sid='hisdb1';
alter system set thread=2 scope=spfile sid='hisdb2';
4) RAC参数调整
--alter system set instance_number=1 scope=spfile sid='hisdb1';
--alter system set instance_number=2 scope=spfile sid='hisdb2';
--alter system set cluster_database_instances=2 scope=spfile;
alter system set cluster_database=true scope=spfile;




2、 注册db到OCR
第一节点关闭，然后重启
SQL> shutdown immediate
SQL> startup


当前的crs里面没有数据库的资源，需要将资源添加到crs里
用oracle用户添加数据库服务及实例服务并设置成自动启动crs去启动这些服务。
[grid@hisservera ~]$ srvctl config database -d hisdb   --之前正常的配置
Database unique name: hisdb
Database name: hisdb
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/hisdb/PARAMETERFILE/spfile.268.1101872219
Password file: +DATA/hisdb/PASSWORD/pwdhisdb.256.1101871767
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: FRA,DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: hisdb1,hisdb2
Configured nodes: his1,his2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed


a ) grid用户先处理密码文件到asm –节点一处理
ASMCMD [+data/hisdbdg] > mkdir password --不新建，直接拷贝
ASMCMD [+data/hisdbdg] > pwcopy --dbuniquename hisdbdg /u01/app/oracle/product/19.0.0/db_1/dbs/orapwhisdb1 +data/hisdbdg/password/pwdhisdb


--如果加错路径或文件了，可以通过sqlplus进去删除
SQL> alter diskgroup data drop file '+DATA/hisdbDG/PASSWORD/pwdhisdbdg.362.1101920821';


b ) 旧密码文件处理
[oracle@hisservera ~]$ cd $ORACLE_HOME/dbs
[oracle@hisservera dbs]$ mv orapwhisdb1 orapwhisdb1_old
c ) oracle用户添加db服务到ocr
srvctl add database -db hisdbdg -oraclehome /u01/app/oracle/product/19.0.0/db_1 -spfile +DATA/hisdbdg/parameterfile/spfile.256.1148094475 -pwfile +data/hisdbdg/password/pwdhisdb  -role LOGICAL_STANDBY -dbname hisdb
d ) oracle用户修改db服务到ocr
srvctl modify database -d hisdbdg -diskgroup 'DATA,ARCH'


[oracle@hisservera dbs]$ srvctl add instance -d hisdbdg -i hisdb1 -n hisservera


[oracle@hisservera dbs]$ srvctl enable database -d hisdbdg
[oracle@hisservera dbs]$ srvctl enable instance -d hisdbdg -i hisdb1
[oracle@hisservera dbs]$ srvctl config database -d hisdbdg
[grid@hisservera admin]$ crsctl stat res -t


e）集群启停测试
[grid@hisservera admin]$ srvctl start database -d hisdbdg
[grid@hisservera admin]$ srvctl stop database -d hisdbdg


3、统计信息收集


4、wm_concat函数增加
5、sys授权\产品脚本执行调整...\his参数调整

7、重新开启同步

11g向19c同步

1、重新同步
在逻辑备库上启用SQL APPLY（rac1开启即可）
SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


主库开启日志传输
SQL> alter system set log_archive_dest_state_3 = ENABLE scope=both;

8、正式切割

执行failover动作，主库释放ip，新19c rac scan修改为原生产库ip

#######################################
#######################################
1、第一步操作,停掉生产业务(zsk)
su - oracle
/u01/app/oracle/product/11.2.4/db_1/network/admin/sqlnet.ora
tcp.validnode_checking=yes
tcp.invited_nodes=(127.0.0.1,10.10.0.68,10.10.0.74,10.10.0.202,10.10.0.201,10.10.0.203,10.10.0.204,10.10.0.100,10.10.0.205,192.168.100.100,192.168.100.101,10.10.14.11)


rac重启监听
lsnrctl stop
lsnrctl start


ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|wc -l
ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs kill -9


2、对比新库和旧库数据


select '门诊费用记录' 表,count(1) 记录数 from 门诊费用记录
union all
select '住院费用记录',count(1) from 住院费用记录
union all
select '药品收发记录',count(1) from 药品收发记录
union all
select '病人医嘱记录',count(1) from 病人医嘱记录
union all
select '电子病历记录',count(1) from 电子病历记录
union all
select '电子病历内容',count(1) from 电子病历内容
union all
select '病人信息',count(1) from 病人信息
union all
select '病人变动记录',count(1) from 病人变动记录
union all
select '病人诊断记录',count(1) from 病人诊断记录;


3、主库摘掉第4个dg
--主库先执行
alter system set log_archive_config='DG_CONFIG=(hisdb,hisdg,hisdb1)' scope=both sid='*';
alter system set log_archive_dest_3='';
alter system reset log_archive_dest_3 scope=spfile;


--备库(先备份现有spfile，然后再删除dg参数)
create pfile from spfile;


--alter system set db_unique_name='hisdb' scope=spfile;
alter system set log_archive_config='';  
alter system reset log_archive_config scope=spfile;
--alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system reset log_archive_dest_2 scope=spfile;
alter system set log_archive_dest_3='' scope=both;
alter system reset log_archive_dest_3 scope=spfile;


alter system reset standby_file_management scope=spfile;  
alter system reset db_file_name_convert scope=spfile; 
alter system reset log_file_name_convert scope=spfile;


alter system set fal_client='' scope=both; 
alter system reset fal_client scope=spfile;
alter system set fal_server='' scope=both; 
alter system reset fal_server scope=spfile;
exit;


su - root
srvctl stop database -d hisdbdg
srvctl start database -d hisdbdg


4、备库执行激活
alter database stop logical standby apply;
alter database activate logical standby database ;
select db_unique_name,open_mode,switchover_status,dataguard_broker,guard_status,database_role,force_logging,
       log_mode,protection_mode,protection_level,name,controlfile_type,fs_failover_status from v$database;


5、改掉集群的属性为主库
srvctl config database -d hisdbdg
srvctl modify database -d hisdbdg -role PRIMARY
srvctl config database -d hisdbdg


6、新202 用root 停掉scan和scan_listener，因为scan用的 10.10.0.201


srvctl stop scan_listener
srvctl stop scan


7、修改老服务器ip变为201 （zsk）


vi /etc/sysconfig/network-scripts/ifcfg-bond0
10.10.0.201
systemctl restart network


8、新HIS库修改scan ip为原HIS ip 10.10.0.100


A．查看当前 scanip 情况
srvctl config scan


B. 停止 scanip
srvctl stop scan_listener
srvctl stop scan


C. 修改命名解析(两个节点都要修改)
vim /etc/hosts


10.10.0.100 hisdb-scan


------修改后如下------
#PUBLIC
10.10.0.201 hisserverb
10.10.0.202 hisservera


#PRIVATE
192.168.100.100 hisserverb-priv
192.168.100.101 hisservera-priv
#VIP
10.10.0.203 hisserverb-vip
10.10.0.204 hisservera-vip
#SCAN
10.10.0.100 hisdb-scan


D. 刷新 scan
srvctl modify scan -n hisdb-scan
srvctl modify scan_listener -u


E. 启动 scan 及 scan_listener
srvctl start scan
srvctl start scan_listener


F. 确认一下 ip 可用
srvctl config scan
ip a


9、业务测试


10、重装原正式库环境，然后搭建

9、重装原来的生产库环境，然后19c的单节点做节点添加操作

一、网卡绑定
public和private网卡均按照主库进行配置，team0为public网卡，team1参考team0配置，步骤一样。
1.创建team
nmcli connection add type team con-name team0 ifname team0 config '{"runner":{"name":"activebackup"}}'
2.配置网络参数
nmcli con modify team0 ipv4.address "10.10.0.201/24" ipv4.gateway 10.10.0.1 ipv4.method manual
3.添加网卡信息
 nmcli con add type team-slave con-name team0-port1 ifname ens4f0 master team0 
 nmcli con add type team-slave con-name team0-port2 ifname ens4f1 master team0 
4.查看网卡信息
 nmcli connection show


5.激活team
 nmcli connection up team0
 nmcli connection up team0-port1
 nmcli connection up team0-port2
6.验证
teamdctl team0 stat
 
二、环境准备
添加gi


-- 如添加节点hisservera(节点1 grid执行)
su - grid
cd $ORACLE_HOME/addnode
./addnode.sh -silent -ignoreSysPrereqs -ignorePrereqFailure "CLUSTER_NEW_NODES={hisserverb}" "CLUSTER_NEW_PRIVATE_NODE_NAMES={hisserverb-priv}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={hisserverb-vip}"


-- 运行root脚本(新节点2 root执行日志)
[root@hisserverb software]# /u01/app/oraInventory/orainstRoot.sh
[root@hisserverb software]# /u01/app/19.0.0/grid/root.sh


添加db
[oracle@hisservera ~]$ cd $ORACLE_HOME/addnode
[oracle@hisservera addnode]$ ls
addnode_oraparam.ini  addnode_oraparam.ini.sbs  addnode.pl  addnode.sh
[oracle@hisservera addnode]$ ./addnode.sh -silent -ignoreSysPrereqs -ignorePrereqFailure "CLUSTER_NEW_NODES={hisserverb}"
--执行root脚本
[root@hisserverb software]# /u01/app/oracle/product/19.0.0/db_1/root.sh




添加实例
--添加实例之前确认密码
[oracle@hisserverb ~]$ sqlplus sys/oracle@10.10.0.204/hisdb as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 21 21:06:15 2023
Version 19.20.0.0.0


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




Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0


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


--添加实例


[oracle@hisservera ~]$ dbca -ignorePreReqs -ignorePrereqFailure -silent -addInstance -nodeName hisserverb -gdbName hisdbdg -instanceName hisdb2 -sysDBAUserName sys -sysDBAPassword oracle
注意：添加实例的时候这里一定要写对-gdbName hisdbdg

11、lvm磁盘删除，再转成asm磁盘组，同时用来做备份

第二步lvm磁盘转asm
[root@hisservera hisdb]# df -Th
文件系统                      类型      容量  已用  可用 已用% 挂载点
devtmpfs                      devtmpfs  126G     0  126G    0% /dev
tmpfs                         tmpfs     225G  3.2G  222G    2% /dev/shm
tmpfs                         tmpfs     126G   21M  126G    1% /run
tmpfs                         tmpfs     126G     0  126G    0% /sys/fs/cgroup
/dev/mapper/rhel-root         xfs       543G   66G  477G   13% /
/dev/sda2                     xfs       292M  154M  138M   53% /boot
/dev/mapper/oraclevg-oraclelv xfs      1000G  885G  116G   89% /hisdata


[root@hisservera hisdata]# pwd
/hisdata
[root@hisservera hisdata]# rm hisdb* -fr


[root@hisservera ~]# umount /hisdata
[root@hisservera ~]# 
[root@hisservera ~]# df -Th
文件系统              类型      容量  已用  可用 已用% 挂载点
devtmpfs              devtmpfs  126G     0  126G    0% /dev
tmpfs                 tmpfs     225G  3.2G  222G    2% /dev/shm
tmpfs                 tmpfs     126G   21M  126G    1% /run
tmpfs                 tmpfs     126G     0  126G    0% /sys/fs/cgroup
/dev/mapper/rhel-root xfs       543G   56G  487G   11% /
/dev/sda2             xfs       292M  154M  138M   53% /boot
tmpfs                 tmpfs      26G   24K   26G    1% /run/user/42
tmpfs                 tmpfs      26G     0   26G    0% /run/user/54322
tmpfs                 tmpfs      26G     0   26G    0% /run/user/0
[root@hisservera ~]# 
[root@hisservera ~]# lvremove /dev/oraclevg/oraclelv 
Do you really want to remove active logical volume oraclevg/oraclelv? [y/n]: y
  Logical volume "oraclelv" successfully removed
[root@hisservera ~]# vgremove oraclevg
  Volume group "oraclevg" successfully removed


[root@hisservera ~]# pvremove /dev/mapper/mpathdata3
  Labels on physical volume "/dev/mapper/mpathdata3" successfully wiped.
[root@hisservera ~]# pvremove /dev/mapper/mpathdata4
  Labels on physical volume "/dev/mapper/mpathdata4" successfully wiped.
[root@hisservera ~]# pvs
  PV                 VG     Fmt  Attr PSize   PFree
  /dev/mapper/mpathh vgdata lvm2 a--    1.95t    0 
  /dev/sda3          rhel   lvm2 a--  558.43g    0 


vi /etc/fstab                                  #### 修改fstab 开机自动挂载
#/dev/oraclevg/oraclelv /hisdata xfs     defaults        0 0


vi /etc/udev/rules.d/99-oracle-asmdevices.rules
#KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a10001e94d",OWNER="grid",SYMLINK+="asm_data3",GROUP="asmadmin",MODE="0660"
#KERNEL=="dm-*",ENV{DM_UUID}=="mpath-360002ac000000000000001a50001e94d",OWNER="grid",SYMLINK+="asm_data4",GROUP="asmadmin",MODE="0660"


/usr/sbin/udevadm trigger --type=devices --action=change
/usr/sbin/udevadm trigger --type=devices --action=add




set line 220
col name for a10
col state for a10 
col COMPATIBILITY for a15
col database_compatibility for a15
select group_number,name,state,type,total_mb/1024 totol_gb,free_mb/1024 as free_gb,usable_file_mb/1024 usable_file_gb,allocation_unit_size/1024/1024 unit_mb,offline_disks,compatibility,database_compatibility,voting_files from v$asm_diskgroup order by 1;


set line 250 pages 222
col dno for a5
col name for a13
col state for a10
col path for a40
col FAILGROUP for a13
col header_status for a15
col rpt for 99999
select group_number||'_'||disk_number as dno,name,failgroup,failgroup_type,path,create_date,mount_date,mount_status,header_status,state,os_mb,total_mb,free_mb,voting_file,repair_timer rpt from v$asm_disk order by 1;


alter diskgroup data add disk '/dev/asm_data3','/dev/asm_data4'  rebalance power 1024;
select * from v$asm_operation;

mkdir /backup/Auto_Ctl_Backup
cd /backup/
mkdir logs
mkdir level0
mkdir levelc1
mkdir leveld1
mkdir /backup/archbak
chown -R oracle:asmadmin /backup


打开和关闭块跟踪


RAC:
#查看是否开启了块跟踪
select * from v$block_change_tracking;
#打开块跟踪
alter database enable block change tracking using file '+DATA';
#关闭块跟踪
alter database disable block change tracking;


非RAC:
#打开块跟踪
alter database enable block change tracking using file '/backup/block.trc';
#关闭块跟踪
alter database disable block change tracking;


rman target /
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
#CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/Auto_Ctl_Backup/ctl_%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ARCH/snapcf_orcl.f';






[oracle@hisservera ~]$ env|grep ORACLE|awk '{print "export "$1}'


[oracle@wsrmyydbb backup]$ vi /backup/level0.sh 
#!/bin/bash
export NLS_LANG=american_america.zhs16gbk
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / nocatalog  log=/backup/logs/db0_`date +%Y%m%d%H%M`.log <<EOF
run{
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate-15';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
backup as compressed backupset incremental level 0 database format '/backup/level0/db0-%d-%T-%s' plus archivelog format '/backup/level0/arc0-%d-%T-%s';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
delete noprompt obsolete  device type disk;
}
EOF


[oracle@wsrmyydbb backup]$ vi levelc1.sh 
#!/bin/bash
export NLS_LANG=american_america.zhs16gbk
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / nocatalog  log=/backup/logs/db_c1_`date +%Y%m%d%H%M`.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset incremental level 1 cumulative database format '/backup/levelc1/db_c1-%d-%T-%s' plus archivelog format '/backup/levelc1/arc_c1-%d-%T-%s';
release channel c1;
release channel c2;
delete noprompt obsolete  device type disk;
}
EOF


[oracle@wsrmyydbb backup]$ vi leveld1.sh 
#!/bin/bash
export NLS_LANG=american_america.zhs16gbk
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / nocatalog  log=/backup/logs/db_d1_`date +%Y%m%d%H%M`.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset incremental level 1 database format '/backup/leveld1/db_d1-%d-%T-%s' plus archivelog format '/backup/leveld1/arc_d1-%d-%T-%s';
release channel c1;
release channel c2;
delete noprompt obsolete  device type disk;
}
EOF


[oracle@wsrmyydbb backup]$ vi arch.sh 
#!/bin/bash
export NLS_LANG=american_america.zhs16gbk
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / nocatalog  log=/backup/logs/arch_`date +%Y%m%d%H%M`.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup as compressed backupset archivelog all not backed up  tag 'Arc'  format '/backup/archbak/arc_%d_%u_%p_%s';
release channel c1;
release channel c2;
delete noprompt obsolete  device type disk;
}
EOF


[root@hisservera backup]# chown -R oracle:asmadmin /backup
[root@hisservera backup]# chmod u+x *.sh
[root@hisservera backup]# ll
总用量 20
drwxr-xr-x 2 oracle asmadmin    6 9月  22 09:33 archbak
-rwxr--r-- 1 oracle asmadmin  593 9月  22 09:32 arch.sh
drwxr-xr-x 2 oracle asmadmin    6 9月  22 08:47 Auto_Ctl_Backup
drwxr-xr-x 2 oracle asmadmin 4096 9月  22 09:09 level0
-rwxr--r-- 1 oracle asmadmin 1078 9月  22 08:55 level0.sh
drwxr-xr-x 2 oracle asmadmin    6 9月  22 08:51 levelc1
-rwxr--r-- 1 oracle asmadmin  569 9月  22 08:56 levelc1.sh
drwxr-xr-x 2 oracle asmadmin    6 9月  22 08:51 leveld1
-rwxr--r-- 1 oracle asmadmin  558 9月  22 08:56 leveld1.sh
drwxr-xr-x 2 oracle asmadmin    6 9月  22 08:50 logs


[oracle@wsrmyydbb level0]$ crontab -l
00 01  * * 0 /backup/level0.sh
00 01  * * 1,2,4,6 /backup/leveld1.sh
00 01  * * 3,5 /backup/levelc1.sh
0 */1 * * * /backup/arch.sh   > /dev/null 2>&1
00 03 * * * find /backup/logs/ -mtime +30 -name "*" -exec rm -rf {} \;

文章转载自数据库技术加油站，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

8分钟完成Rac升级迁移+补丁操作

评论