搜索到
22
篇与
的结果
-
Oracle数据库自启动,DG配置和备份脚本 数据库配置自启动修改/etc/oratab文件# # This file is used by ORACLE utilities. It is created by root.sh # and updated by either Database Configuration Assistant while creating # a database or ASM Configuration Assistant while creating ASM instance. # A colon, ':', is used as the field terminator. A new line terminates # the entry. Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: # $ORACLE_SID:$ORACLE_HOME:<N|Y>: # # The first and second fields are the system identifier and home # directory of the database respectively. The third field indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # {ORACLE_SID}:{ORACLE_HOME}:Y #增加该行内容 sid 和 home替换为数据库的信息Oracle用户下修改"$ORACLE_HOME/bin/dbstart" 和 "$ORACLE_HOME/bin/dbshut".............. # Set path if path not set (if called from /etc/rc) SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATH SAVE_LLP=$LD_LIBRARY_PATH # First argument is used to bring up Oracle Net Listener #ORACLE_HOME_LISTNER=$1 #注释掉改行 ORACLE_HOME_LISTNER=$ORACLE_HOME #添加该内容 if [ ! $ORACLE_HOME_LISTNER ]; then echo "Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener." echo "Usage: $0 ORACLE_HOME" else ..............root用户下增加以下内容到/etc/rc.localsu - oracle -lc "/u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl start" #启动监听 su - oracle -lc /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart #启动数据库/etc/rc.local文件需要给可执行权限chmod +x /etc/rc.localDG库实现启动后自动应用归档日志Oracle用户下创建脚本文件/home/oracle/del_arch/autoapply.sh#!/bin/sh # 检查数据库是否启动的函数 check_db_is_up() { sqlplus -silent / as sysdba << EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT 1 FROM DUAL; EXIT; EOF } # 循环直到数据库启动 while ! check_db_is_up; do echo "Database is not up yet. Waiting for 10 seconds..." sleep 10 done # 数据库已启动，设置环境变量并执行恢复脚本 export ORACLE_SID=webemr sqlplus / as sysdba << EOF >> /home/oracle/del_arch/autoapply.log 2>&1 alter database recover managed standby database using current logfile disconnect from session; exit EOF然后root用户下增加以下内容到/etc/rc.localsu - oracle -lc /home/oracle/del_arch/autoapply.sh自动清理归档日志Oracle用户下创建脚本文件/home/oracle/del_arch/autoapply.sh#!/bin/sh export ORACLE_SID=webemr rman target / nocatalog log /home/oracle/del_arch/del_arch.sh << EOF run { crosscheck archivelog all; delete noprompt archivelog all completed before 'sysdate-3'; #删除3天前的归档日志 } exit EOF然后root用户下执行"crontab -e"来创建定时任务,添加以下内容0 2 * * * su - oracle -c /home/oracle/del_arch/del_arch.sh使用 "crontab -l" 检查定时任务[root@localhost ~]# crontab -l 0 2 * * * su - oracle -c /home/oracle/del_arch/del_arch.sh创建自动备份及清理备份的任务创建脚本文件/u01/rmanback/rmanbak.sh#!/bin/sh export ORACLE_SID=$ORACLE_SID rman target sys/aqa nocatalog log /u01/rmanback/rmanbak.log << EOF run { allocate channel t1 type disk; allocate channel t2 type disk; sql 'alter system archive log current'; backup as compressed backupset database tag 'dbfullbackup' format='/u01/rmanback/%T_db_%U_%d'; backup current controlfile format='/u01/rmanback/%T_ctl_%U_%d'; backup spfile format='/u01/rmanback/%T_sp_%U_%d'; sql 'alter system archive log current'; backup as compressed backupset archivelog from time "sysdate-1" until time "sysdate" format='/u01/rmanback/%T_arc_%U_%d'; crosscheck backupset; crosscheck archivelog all; delete noprompt archivelog all completed before 'sysdate-30'; delete noprompt backupset completed before 'sysdate-2'; release channel t1; release channel t2; } exit EOF然后root用户下执行"crontab -e"来创建定时任务,添加以下内容0 2 * * * su - oracle -c /u01/rmanback/rmanbak.sh使用 "crontab -l" 检查定时任务[root@localhost ~]# crontab -l 0 2 * * * su - oracle -c /u01/rmanback/rmanbak.sh
-
Oracle11g单节点库通过搭建DG RAC的方式迁移至集群环境 一、环境配置1.1、安装软件包1.1.1、配置本地源注:能通过公网安装软件包的情况下跳过该项mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/1.1.2、安装软件包yum -y install bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXtst libX11 libXau libxcb libXi libXrender make smartmontools sysstat kmod* sysstat unixODBC unixODBC-devel libnsl lm_sensors-libs ksh make glibc-devel libaio-devel libstdc++-devel nano需要单独安装的软件包yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/compat-libcap1-1.10-7.el7.x86_64.rpm yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/sysstat-10.1.5-20.0.3.el7_9.x86_64.rpm yum -y localinstall https://mirrors.aliyun.com/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm yum -y localinstall http://vault.centos.org/5.11/os/x86_64/CentOS/pdksh-5.2.14-37.el5_8.1.x86_64.rpm1.2、内核参数调整,执行以下代码块自动完成export shmall=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc) export shmax=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##') echo """kernel.shmall = $shmall kernel.shmmax = $shmax kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 104857 vm.swappiness = 0 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1""" >> /etc/sysctl.conf sysctl -p1.3、配置ssh、关闭Selinux及防火墙# 配置LoginGraceTime参数为0, 将timeout wait设置为无限制 sed -i '/#LoginGraceTime 2m/ s/#LoginGraceTime 2m/LoginGraceTime 0/' /etc/ssh/sshd_config && grep LoginGraceTime /etc/ssh/sshd_config # 加快SSH登录 禁用DNS sed -i '/#UseDNS yes/ s/#UseDNS yes/UseDNS no/' /etc/ssh/sshd_config && grep UseDNS /etc/ssh/sshd_config sed -i 's/enforcing/disabled/g' /etc/selinux/config setenforce 0 service firewalld stop chkconfig firewalld off1.4用户组及变量相关设置1.4.1单机环境groupadd -g 1003 oinstall groupadd -g 1001 dba groupadd -g 1002 oper useradd -u 1001 -g oinstall -G dba,oper oracle echo oracle |passwd --stdin oracle echo """export ORACLE_SID=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=\${ORACLE_BASE}/product/11.2.0.4/db_1 export NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS" export NLS_LANG=american_america.ZHS16GBK export TNS_ADMIN=\$ORACLE_HOME/network/admin export ORA_NLS11=\$ORACLE_HOME/nls/data export PATH=.:\${PATH}:\$ORACLE_HOME/bin:/usr/bin:/usr/bin/X11:/usr/local/bin: export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib # export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib:\$ORACLE_HOME/network/jlib export CV_ASSUME_DISTID=OL7 export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp""" >> /home/oracle/.bash_profile1.4.2集群环境节点1 执行groupadd -g 1003 oinstall groupadd -g 1001 dba groupadd -g 1002 oper groupadd -g 1004 backupdba groupadd -g 1005 dgdba groupadd -g 1006 kmdba groupadd -g 1007 asmdba groupadd -g 1008 asmoper groupadd -g 1009 asmadmin groupadd -g 1010 racdba useradd -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,racdba oracle useradd -g oinstall -G dba,asmdba,asmoper,asmadmin,racdba grid echo "oracle" | passwd --stdin oracle echo "grid" | passwd --stdin grid #节点1-Grid用户 cat >> /home/grid/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/11.2.0.4/grid export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=american_america.ZHS16GBK export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=+ASM1 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH #alias sas='sqlplus / as sysasm' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF # 节点1-Oracle用户 echo """export ORACLE_SID=orcl1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=\${ORACLE_BASE}/product/11.2.0.4/db_1 export NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS" export NLS_LANG=american_america.ZHS16GBK export TNS_ADMIN=\$ORACLE_HOME/network/admin export ORA_NLS11=\$ORACLE_HOME/nls/data export PATH=.:\${PATH}:\$ORACLE_HOME/bin:/usr/bin:/usr/bin/X11:/usr/local/bin: export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib # export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib:\$ORACLE_HOME/network/jlib export CV_ASSUME_DISTID=OL7 export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp""" >> /home/oracle/.bash_profile节点2执行groupadd -g 1003 oinstall groupadd -g 1001 dba groupadd -g 1002 oper groupadd -g 1004 backupdba groupadd -g 1005 dgdba groupadd -g 1006 kmdba groupadd -g 1007 asmdba groupadd -g 1008 asmoper groupadd -g 1009 asmadmin groupadd -g 1010 racdba useradd -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,racdba oracle useradd -g oinstall -G dba,asmdba,asmoper,asmadmin,racdba grid echo "oracle" | passwd --stdin oracle echo "grid" | passwd --stdin grid cat >> /home/grid/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/11.2.0.4/grid export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=american_america.ZHS16GBK export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=+ASM2 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH #alias sas='sqlplus / as sysasm' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF # 节点2-Oracle用户 echo """export ORACLE_SID=orcl2 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=\${ORACLE_BASE}/product/11.2.0.4/db_1 export NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS" export NLS_LANG=american_america.ZHS16GBK export TNS_ADMIN=\$ORACLE_HOME/network/admin export ORA_NLS11=\$ORACLE_HOME/nls/data export PATH=.:\${PATH}:\$ORACLE_HOME/bin:/usr/bin:/usr/bin/X11:/usr/local/bin: export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib # export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib:\$ORACLE_HOME/network/jlib export CV_ASSUME_DISTID=OL7 export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp""" >> /home/oracle/.bash_profile1.5、创建目录及赋予权限1.5.1、单机环境创建目录并赋予权限mkdir -p /u01/app/oracle/product/11.2.0.4/db_1 chown -R oracle:oinstall /u01/ chmod -R 775 /u01/1.5.2、集群环境创建目录并赋予权限mkdir -p /u01/app/11.2.0.4/grid mkdir -p /u01/app/grid mkdir -p /u01/app/oracle/product/11.2.0.4/db_1 chown -R grid:oinstall /u01 chown -R oracle:oinstall /u01/app/oracle chmod -R 775 /u01/二、存储配置2.1、ISCSI存储配置注:如未使用本地创建的磁盘，使用iscsi共享磁盘，需要先连接到共享磁盘yum install -y iscsi-initiator-utils systemctl start iscsi systemctl enable iscsi #发现target(discovery) iscsiadm -m discovery -t st -p 10.255.10.9:3260 #连接target(discovery)存储 iscsiadm -m node -T iqn.2005-10.org.freenas.ctl:orcl-rac -p 10.255.10.9 -l #查看登录的session 相当于iscsiadm -m session -P 0 iscsiadm -m session #扫描所有关联的target/session iscsiadm -m node -R iscsiadm -m session -R #自动登录 iscsiadm -m node -T iqn.2005-10.org.freenas.ctl:orcl-rac -p 10.255.10.9,3260 --op update -n node.startup -v automatic #扫描新添加的Iscsi存储设备 /usr/bin/scsi-rescan #查看已经建立的iscsi连接 iscsiadm -m session -P 3 #断开iscsi连接 iscsiadm --mode node --targetname <target_name> --portal <target_portal> --logout #断开所有iscsi连接 iscsiadm --mode node --logoutall=all #重启iscsi systemctl restart iscsid #取消掉自动登录 iscsiadm --mode node --targetname iqn.2005-10.org.freenas.ctl:orcl-rac --portal 10.255.10.9:3260 --op delete2.2、multipath+udev多路径2.2.1、multipath配置##安装multipath yum install -y device-mapper* mpathconf --enable --with_multipathd y ##查看共享盘的scsi_id /usr/lib/udev/scsi_id -g -u /dev/sdb /usr/lib/udev/scsi_id -g -u /dev/sdc /usr/lib/udev/scsi_id -g -u /dev/sdd /usr/lib/udev/scsi_id -g -u /dev/sde /usr/lib/udev/scsi_id -g -u /dev/sdf mv /etc/multipath.conf /etc/multipath.conf_bak # 将iscsi_id 替换到下边的代码块中 cat <<EOF>> /etc/multipath.conf defaults { user_friendly_names yes } blacklist { devnode "^sda" } multipaths { multipath { wwid "360002ac00000000000000018000278cf" alias asm_crs01 } multipath { wwid "360002ac00000000000000019000278cf" alias asm_crs02 } multipath { wwid "360002ac0000000000000001a000278cf" alias asm_crs03 } multipath { wwid "360002ac0000000000000001b000278cf" alias asm_fra } multipath { wwid "360002ac0000000000000001c000278cf" alias asm_data } } EOF #加载内核模块 modprobe dm_multipath #删除所有不使用的多路径设备 multipath -F #查看生成的多路径盘符 multipath -v2 #显示多路径配置状态 multipath -ll2.2.2、Udev配置for i in crs01 crs02 crs03 fra data; do echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\",SUBSYSTEM==\"block\", PROGRAM==\"/lib/udev/scsi_id -g -u -d \$devnode\",RESULT==\"`/usr/lib/udev/scsi_id -g -u /dev/mapper/asm_$i`\",RUN+=\"/bin/sh -c 'mknod /dev/asm-$i b \$major \$minor; chown grid:asmadmin /dev/asm-$i; chmod 0660 /dev/asm-$i'\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done ##重载udev udevadm control --reload-rules udevadm trigger --type=devices --action=change ll /dev/asm*2.2.3、Udev(非multipath)注:/dev/sd[b-f] 对应下边的 b c d e f 如有更多的盘添加到代码中for i in b c d e f; do echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\", SUBSYSTEM==\"block\", PROGRAM==\"/lib/udev/scsi_id -g -u -d \$devnode\", RESULT==\"`/lib/udev/scsi_id -g -u -d /dev/sd$i`\", SYMLINK+=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done # 加载rules文件,重新加载udev rule /sbin/udevadm control --reload # 检查新的设备名称 /sbin/udevadm trigger --type=devices --action=change三、集群环境下其他配置3.1、主机名及解析# 注：检查双网卡配置 #网卡1： # 配置public IP 需要网关 网卡2： # 配置priv IP 不要网关 # 网卡都关闭IPv6 # 节点1 hostnamectl set-hostname rac01 # 节点2 hostnamectl set-hostname rac02 echo "#public ip 10.255.10.11 rac01 10.255.10.12 rac02 #priv ip 10.10.10.11 rac01-pri 10.10.10.12 rac02-pri #vip ip 10.255.10.13 rac01-vip 10.255.10.14 rac02-vip #scan ip 10.255.10.10 rac-scan" >> /etc/hosts # 测试连通性所有节点 ping -c 1 rac01; ping -c 1 rac02; ping -c 1 rac01-pri; ping -c 1 rac02-pri;3.2、调整Network-双节点echo "NOZEROCONF=yes" >>/etc/sysconfig/network && cat /etc/sysconfig/network3.3、调整/dev/shm -双节点# 修改shm大小 echo "tmpfs /dev/shm tmpfs rw,exec,size=32G 0 0">>/etc/fstab # 重新挂载 mount -o remount /dev/shm # 检测shm大小是否修改 df -h3.4、关闭透明大页cp /etc/default/grub /etc/default/grub.bak sed -i 's/quiet/quiet\ transparent_hugepage=never/g' /etc/default/grub #执行命令 grub2-mkconfig -o /boot/grub2/grub.cfg #不重启生效 echo never > /sys/kernel/mm/transparent_hugepage/enabled注:Rac集群下为所有节点配置互信#生成密钥对 rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa注:节点1(rac01)的所有用户(oracle,grud,root)执行追加互信cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac02 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac02 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp ~/.ssh/authorized_keys rac02:~/.ssh/authorized_keys注:所有节点(rac01,rac02)的所有用户(oracle,grid,root)执行互信验证，都不需要密码则互信添加完成ssh rac01 date; ssh rac02 date; ssh rac01-pri date; ssh rac02-pri date;四、主库安装及DG配置安装步骤省略4.1、DG配置sqlplus / as sysdba shutdown immediate startup mount alter database archivelog; alter database force logging; alter database flashback on; set wrap off; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- --------------- 1 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG 2 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG 3 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG # 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组 alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/sredo04.log') size 50m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/sredo05.log') size 50m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/sredo06.log') size 50m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/sredo07.log') size 50m; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- ------- ------- ----------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log 4 STANDBY /u01/app/oracle/oradata/orcl/sredo04.log 5 STANDBY /u01/app/oracle/oradata/orcl/sredo05.log 6 STANDBY /u01/app/oracle/oradata/orcl/sredo06.log 7 STANDBY /u01/app/oracle/oradata/orcl/sredo07.log 7 rows selected. #设置db_unique_name alter system set db_unique_name='primary' scope=spfile; #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile; #设置日志归档路径1 alter system set log_archive_dest_1='location=/u01/app/oracle/orcl_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile; #设置日志归档路径2 alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby' scope=spfile; #启用归档路径1 alter system set log_archive_dest_state_1='enable' scope=spfile; #启用归档路径2 alter system set log_archive_dest_state_2='enable' scope=spfile; #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） alter system set fal_server='standby' scope=spfile; #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） alter system set fal_client='primary' scope=spfile; #设置归档日志强制切换时间（s）1800表示30分钟 alter system set archive_lag_target=1800 scope=spfile; #设置归档日志文件名命名方式 alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 alter system set standby_file_management=auto scope=spfile; #设置数据文件名称的映射关系（注意顺序不能变） alter system set db_file_name_convert='standby','primary' scope=spfile; #设置日志文件名称的映射关系（注意顺序不能变） alter system set log_file_name_convert='standby','primary' scope=spfile;4.2、主库监听配置# 修改监听文件/u01/app/oracle/product/19c/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) #host为当前主机名 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) #新增以下内容 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = orcl) #改为实例名 (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = orcl) #改为实例名 ) )# 修改主库客户端配置文件/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) # 新增内容 PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) #HOST填写主库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521)) #HOST填写备库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) )五、集群安装及DG配置5.1、安装前环境检查# 环境检查 ./runcluvfy.sh stage -pre crsinst -n rac01,rac02 -fixup -verbose ./runcluvfy.sh stage -pre crsinst -n rac01,rac02 -verbose ./runcluvfy.sh stage -post hwos -n rac01,rac02 -verbose安装过程省略，安装集群过程中出现问题按照下边方式处理# 执行过程中卡 Adding Clusterware entries to inittab # 新建一个窗口执行以下命令 # 方案1 dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1 # 方案2 chown root:oinstall/var/tmp/.oracle/npohasd # 重新执行root.sh之前删除旧配置 /u01/app/11.2.0.4/grid/crs/install/roothas.pl -deconfig -force -verbose5.2、监听配置GRID 监听# /u01/app/11.2.0.4/grid/network/admin/listener.ora 增加以下配置 SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=rac01) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) #此处为oracle的ORACLE_HOME (SID_NAME=orcl1) ) ) # 重载监听 lsnrctl reloadOracle Tnsnames# /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) ) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = orcl) ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac01)(PORT = 1521)) ) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = orcl) ) )5.3、数据复制# oracle文件权限（谨慎使用） chmod 6751 oracle # 用户加组 usermod -aG 组名 用户名 # 移除组下的用户 gpasswd -d 用户名 组名 # 连接主库和辅助数据库 rman target sys/Xx961024@primary auxiliary sys/Xx961024@standby # 执行此命令为不自动回复 duplicate target database for standby from active database nofilenamecheck; # 执行首次同步，带自动恢复 duplicate target database for standby from active database dorecover nofilenamecheck; # 应用日志同步 alter database recover managed standby database disconnect from session; # 开启实时同步 alter database recover managed standby database using current logfile disconnect from session; # 停止同步 alter database recover managed standby database cancel; # 检查日志同步情况 select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -3/24 order by sequence#; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1 order by sequence#; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where APPLIED='YES' order by sequence#; # 注册归档日志 rman catalog start with 'path'; # 创建spfile create spfile='+DATA/oradata/spfileorcl.ora' from pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora'; # 备份initorcl1.ora文件，重新创建 spfile='+RACDATA/spfileorcl.ora' # 重启数据库验证spfile # 注册CRS资源管理 srvctl add database -d orcl -n orcl -o /u01/app/oracle/product/11.2.0.4/db_1 -p +DATA/oradata/spfileorcl.ora -r physical_standby -a "DATA" # 注册节点 srvctl add instance -d orcl -i orcl1 -n rac01 srvctl add instance -d orcl -i orcl2 -n rac02 # 检查资源配置 srvctl config database -d orcl # 为rac02节点配置自己的UNDOTBS # 主库操作 set pagesize 1000 linesize 1000 col FILE_NAME for a65; select file_name,bytes/1024/1024 from dba_data_files; # 在主库添加UNDOTBS2，RAC库同步（加完可以切一下日志） create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m; # 备库（rac）操作 # 检查UNDOTBS2是否已经同步过来 select file_name from dba_data_files; # 将数据库转换为rac模式,注：千万不能漏，漏会报错只能把本地pfile恢复重新来 alter system set cluster_database=true scope=spfile; alter system set cluster_database_instances=2 scope=spfile; alter system set instance_number=1 scope=spfile sid='orcl1'; alter system set instance_number=2 scope=spfile sid='orcl2'; alter system set thread=1 scope=spfile sid='orcl1'; alter system set thread=2 scope=spfile sid='orcl2'; alter system set undo_tablespace=undotbs1 scope=spfile sid='orcl1'; alter system set undo_tablespace=undotbs2 scope=spfile sid='orcl2'; # 重启数据库（rac1节点） # 主库处理日志组问题 === 见下一个项目 # 备库查询实例状态 select INSTANCE_NAME,STATUS,INSTANCE_ROLE,DATABASE_STATUS from gv$instance; # 主库状态检查 select database_role,switchover_status from v$database; # 将主库切换为备库 # 切换之前做一次 alter system switch logfile; alter database commit to switchover to physical standby with session shutdown; # 关闭备库 srvctl stop database -d orcl # 将新备库启动到mount状态 grid 用户执行 srvctl start database -d orcl -o mount # 将备库（rac）切换为主库 alter database commit to switchover to primary; # 将新主库（rac）打开 grid 用户执行 srvctl start database -d orcl -o open; 主库为备库添加日志组show parameter standby_file_management # 先改为手动模式 alter system set standby_file_management=manual; # 查询日志组信息 select a.group#,member,thread#,bytes/1024/1024 MB from v$logfile a, v$log b where a.group#=b.group#; # 查询所有日志组 select * from v$logfile; # 查询日志组使用情况 select group#,thread#,sequence#,members,status from v$log; # 删除日志组 alter database disable thread 2; alter database clear logfile group xx; alter database drop logfile group xx; alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA/oradata/orcl/redo01.log'; alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '+DATA/oradata/orcl/redo02.log'; alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '+DATA/oradata/orcl/redo03.log'; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orcl/sredo01.log' size 50m; alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orcl/sredo02.log' size 50m; alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orcl/sredo03.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/orcl/redo04.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/orcl/redo05.log' size 50m; alter database add logfile thread 2 '/u01/app/oracle/oradata/orcl/redo06.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50m; alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50m; alter database enable thread 2; alter system set standby_file_management=auto; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # 备库rac添加standby日志 alter database add logfile thread 1 '+DATA/oradata/orcl/redo01.log' size 50m; alter database add logfile thread 1 '+DATA/oradata/orcl/redo02.log' size 50m; alter database add logfile thread 1 '+DATA/oradata/orcl/redo03.log' size 50m; alter database add standby logfile thread 1 '+DATA/oradata/orcl/sredo01.log' size 50m; alter database add standby logfile thread 1 '+DATA/oradata/orcl/sredo02.log' size 50m; alter database add standby logfile thread 1 '+DATA/oradata/orcl/sredo03.log' size 50m; alter database add logfile thread 2 '+DATA/oradata/orcl/redo04.log' size 50m; alter database add logfile thread 2 '+DATA/oradata/orcl/redo05.log' size 50m; alter database add logfile thread 2 '+DATA/oradata/orcl/redo06.log' size 50m; alter database add standby logfile thread 2 '+DATA/oradata/orcl/sredo04.log' size 50m; alter database add standby logfile thread 2 '+DATA/oradata/orcl/sredo05.log' size 50m; alter database add standby logfile thread 2 '+DATA/oradata/orcl/sredo06.log' size 50m; alter system set standby_file_management=auto;增加或删除新的scan IP在集群# 先编辑/etc/hosts 文件-双节点-root用户 #scan ip 10.255.10.10 rac-scan 10.255.10.15 rac-scan # 停止监听 srvctl stop scan_listener # 停止scan srvctl stop scan # 查看状态 srvctl status scan_listener # 修改scan信息,-n后面接 hosts中的san ip网络名 /u01/app/11.2.0.4/grid/bin/srvctl modify scan -n rac-scan # 添加scanIP的监听，若不执行该命令，将只有一个listener_scan1监听，执行后将根据scan ip数自动增加listener_scan2，listener_scan3. /u01/app/11.2.0.4/grid/bin/srvctl modify scan_listener -u # 启动scan srvctl start scan # 启动监听 srvctl start scan_listener # 查看状态 srvctl status scan闪回的使用# 查询闪回状态 select flashback_on from v$database; # 取消备库的自动恢复 alter database recover managed standby database cancel; # mount模式下打开闪回 alter system set db_recovery_dest_size=5g; alter system set db_recovery_file_dest='+DATA'; alter database flashback on; # 创建还原点 create restore point test_sw; # 在主库归档当前日志,确保前一步创建还原点的scn的归档日志已经传到备库 alter system archive log current; # 将主库到备库的归档目的地状态设置为defer alter system set log_archive_dest_state_2=defer; select dest_name,error, status from gv$archive_dest; # 查询闪回scn select current_scn from v$database; # 激活备库到read/write状态 alter database activate standby database; shutdown immediate; startup; select open_mode from v$database; # 将数据库flashback回原来保存的还原点 startup mount force; flashback database to restore point test_sw; # 转换成备库 alter database convert to physical standby; # 将备库至于自动恢复状态 startup mount force; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; # 将主库到备库的归档目的地状设置为enable alter system set log_archive_dest_state_2=enable; # 检查同步情况 set linesize 150; set pagesize 20; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');修改Ip# 备份hosts文件 cp /etc/hosts /etc/hosts_bak # 停止集群 srvctl stop database -d orcl # 停止CRS 所有节点root执行 /u01/app/11.2.0.4/grid/bin/crsctl stop crs -f # 修改hosts文件 # 启动CRS 所有节点root执行 /u01/app/11.2.0.4/grid/bin/crsctl start crs # 检查状态 /u01/app/11.2.0.4/grid/bin/crsctl check cluster -all
-
NBU 异机恢复 rman-06026 man-06023 异常处理 异常信息rman异机恢复时，报错RMAN-06026、RMAN-06023，如下所示：敏感信息做脱敏处理RMAN> run { ALLOCATE CHANNEL CH10 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH11 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH12 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH13 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH14 TYPE 'sbt_tape'; SEND 'NB_ORA_SERV=nbuServer, NB_ORA_CLIENT=xxxdb'; set newname for datafile 1 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 2 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 3 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 4 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 5 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 6 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 7 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 8 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 9 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 10 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 11 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 12 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 13 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 14 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 15 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 16 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 17 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 18 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 19 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 20 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 21 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 22 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 23 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 24 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 25 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 26 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 27 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 28 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 29 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 30 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 31 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 32 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 33 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 34 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 35 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 36 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 37 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 38 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 39 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 40 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 41 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 42 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 43 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 44 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 45 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 46 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 47 to '/u01/app/oracle/oradata/xxx.dbf'; restore database; switch datafile all; release channel ch10; release channel ch11; release channel ch12; release channel ch13; release channel ch14; 62> } allocated channel: CH10 channel CH10: SID=1702 device type=SBT_TAPE channel CH10: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH11 channel CH11: SID=2268 device type=SBT_TAPE channel CH11: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH12 channel CH12: SID=2834 device type=SBT_TAPE channel CH12: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH13 channel CH13: SID=3400 device type=SBT_TAPE channel CH13: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH14 channel CH14: SID=3967 device type=SBT_TAPE channel CH14: Veritas NetBackup for Oracle - Release 10.2 (2023032421) sent command to channel: ORA_MAINT_SBT_TAPE_3 sent command to channel: ORA_MAINT_DISK_4 sent command to channel: CH10 sent command to channel: CH11 sent command to channel: CH12 sent command to channel: CH13 sent command to channel: CH14 executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 10-APR-24 released channel: CH10 released channel: CH11 released channel: CH12 released channel: CH13 released channel: CH14 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 04/10/2024 17:06:56 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 4 found to restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore显示找不到数据文件的备份文件。在目标机器上使用rman查询incarnatioin记录:rman target / RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 JHEMR 1358074586 ORPHAN 1 24-AUG-13 2 2 JHEMR 1358074586 ORPHAN 925702 22-JUL-19 3 3 JHEMR 1358074586 ORPHAN 86506916788 17-DEC-21 4 4 JHEMR 1358074586 CURRENT 88670576572 25-FEB-22在原数据库上执行相同的查询:RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 JHEMR 1358074586 PARENT 1 2013:08:2411:37:30 2 2 JHEMR 1358074586 PARENT 925702 2019:07:2200:57:01 3 3 JHEMR 1358074586 CURRENT 86506916788 2021:12:1700:51:45结论及处理方案对比incarnatioin记录 发现目标库的CURRENT记录号和原库的CURRENT记录号不一致, 将目标库的当前记录修改为原库一致的记录号:RMAN> reset database to incarnation 3; database reset to incarnation 3然后再次执行NBU恢复命令, 无异常敏感信息做脱敏处理RMAN> run { ALLOCATE CHANNEL CH10 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH11 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH12 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH13 TYPE 'sbt_tape'; ALLOCATE CHANNEL CH14 TYPE 'sbt_tape'; SEND 'NB_ORA_SERV=nbuServer, NB_ORA_CLIENT=xxxdb'; set newname for datafile 1 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 2 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 3 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 4 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 5 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 6 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 7 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 8 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 9 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 10 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 11 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 12 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 13 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 14 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 15 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 16 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 17 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 18 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 19 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 20 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 21 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 22 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 23 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 24 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 25 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 26 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 27 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 28 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 29 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 30 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 31 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 32 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 33 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 34 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 35 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 36 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 37 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 38 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 39 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 40 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 41 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 42 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 43 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 44 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 45 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 46 to '/u01/app/oracle/oradata/xxx.dbf'; set newname for datafile 47 to '/u01/app/oracle/oradata/xxx.dbf'; restore database; switch datafile all; release channel ch10; release channel ch11; release channel ch12; release channel ch13; release channel ch14; 62> } allocated channel: CH10 channel CH10: SID=1702 device type=SBT_TAPE channel CH10: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH11 channel CH11: SID=2268 device type=SBT_TAPE channel CH11: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH12 channel CH12: SID=2834 device type=SBT_TAPE channel CH12: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH13 channel CH13: SID=3400 device type=SBT_TAPE channel CH13: Veritas NetBackup for Oracle - Release 10.2 (2023032421) allocated channel: CH14 channel CH14: SID=3967 device type=SBT_TAPE channel CH14: Veritas NetBackup for Oracle - Release 10.2 (2023032421) sent command to channel: ORA_MAINT_SBT_TAPE_3 sent command to channel: ORA_MAINT_DISK_4 sent command to channel: CH10 sent command to channel: CH11 sent command to channel: CH12 sent command to channel: CH13 sent command to channel: CH14 executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 10-APR-24 Starting restore at 10-APR-24 channel CH10: starting datafile backup set restore channel CH10: specifying datafile(s) to restore from backup set channel CH10: restoring datafile 00003 to /u01/app/oracle/oradata/xxx.dbf channel CH10: restoring datafile 00008 to /u01/app/oracle/oradata/xxx.dbf channel CH10: restoring datafile 00021 to /u01/app/oracle/oradata/xxx.dbf channel CH10: restoring datafile 00030 to /u01/app/oracle/oradata/xxx.dbf channel CH10: restoring datafile 00043 to /u01/app/oracle/oradata/xxx.dbf channel CH10: reading from backup piece bk_47370_1_1165622409 channel CH11: starting datafile backup set restore channel CH11: specifying datafile(s) to restore from backup set channel CH11: restoring datafile 00009 to /u01/app/oracle/oradata/xxx.dbf channel CH11: restoring datafile 00022 to /u01/app/oracle/oradata/xxx.dbf channel CH11: restoring datafile 00024 to /u01/app/oracle/oradata/xxx.dbf channel CH11: restoring datafile 00031 to /u01/app/oracle/oradata/xxx.dbf channel CH11: restoring datafile 00044 to /u01/app/oracle/oradata/xxx.dbf channel CH11: reading from backup piece bk_47371_1_1165622409 channel CH12: starting datafile backup set restore channel CH12: specifying datafile(s) to restore from backup set channel CH12: restoring datafile 00001 to /u01/app/oracle/oradata/xxx.dbf channel CH12: restoring datafile 00005 to /u01/app/oracle/oradata/xxx.dbf channel CH12: restoring datafile 00013 to /u01/app/oracle/oradata/xxx.dbf channel CH12: restoring datafile 00014 to /u01/app/oracle/oradata/xxx.dbf channel CH12: restoring datafile 00016 to /u01/app/oracle/oradata/xxx.dbf channel CH12: reading from backup piece bk_47372_1_1165629595 channel CH13: starting datafile backup set restore channel CH13: specifying datafile(s) to restore from backup set channel CH13: restoring datafile 00012 to /u01/app/oracle/oradata/xxx.dbf channel CH13: restoring datafile 00017 to /u01/app/oracle/oradata/xxx.dbf channel CH13: restoring datafile 00018 to /u01/app/oracle/oradata/xxx.dbf channel CH13: restoring datafile 00047 to /u01/app/oracle/oradata/xxx.dbf channel CH13: reading from backup piece bk_47373_1_1165638790 channel CH14: starting datafile backup set restore channel CH14: specifying datafile(s) to restore from backup set channel CH14: restoring datafile 00011 to /u01/app/oracle/oradata/xxx.dbf channel CH14: restoring datafile 00028 to /u01/app/oracle/oradata/xxx.dbf channel CH14: restoring datafile 00037 to /u01/app/oracle/oradata/xxx.dbf channel CH14: restoring datafile 00046 to /u01/app/oracle/oradata/xxx.dbf channel CH14: reading from backup piece bk_47374_1_1165641215
-
归档日志清理 归档日志及备份清理相关命令//检查所有归档日志 CROSSCHECK ARCHIVELOG ALL; //删除三天前的归档日志 DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3'; //验证所有归档日志的备份是否有效 BACKUP VALIDATE ARCHIVELOG ALL; //检查备份一致性 CROSSCHECK BACKUP; //删除无效的备份 DELETE NOPROMPT EXPIRED BACKUP; //退出 EXIT;Windows下自动清理归档日志创建脚本执行命令注意修改脚本中的位置rman target / nocatalog log D:\arch\clear_arch.log cmdfile=D:\arch\clear_arch.txt创建一个文件存放清理归档日志的命令D:\arch\chear_arch.txt 文件位置根据情况选择CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3'; delete expired archivelog all; EXIT;windows 下打开 任务计划程序创建基本任务填写任务名称选择触发时间选择启动程序程序选择bat脚本直至完成运行任务测试结果
-
龙析系统安装Oracle19c 龙析系统安装 Oracle19c1.安装背景本次教程使用VMware workstation Pro 17 创建虚拟机完成安装测试1.1 系统选择os版本信息:AnolisOS-8.8-x86_64-dvd.iso数据库版本信息:LINUX.X64_193000_db_home.zip1.2 硬件配置信息参数值核心1 H 2 G内存2048 M存储60 G虚拟机个数2 个1.3 分区配置分区大小/boot1024 M/boot/efi1024 Mswap2048 M/all2. 环境配置2.1 内核参数调整执行以下内容export shmall=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc) export shmax=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##') echo """kernel.shmall = $shmall kernel.shmmax = $shmax kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 104857 vm.swappiness = 0 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100""" >> /etc/sysctl.conf sysctl -p2.2 关闭 Selinux 及防火墙执行以下内容sed -i 's/enforcing/disabled/g' /etc/selinux/config setenforce 0 service firewalld stop chkconfig firewalld off # 龙析8.8 系统没有network服务 关闭NetworkManager后重启无法联网 # service NetworkManager stop # chkconfig NetworkManager off2.3 用户组相关设置执行以下内容groupadd -g 1003 oinstall groupadd -g 1001 dba groupadd -g 1002 oper useradd -u 1001 -g oinstall -G dba,oper oracle echo oracle |passwd --stdin oracle2.4 创建目录赋予权限mkdir -p /u01/app/oracle/product/19c/db_1 chown -R oracle:oinstall /u01/ chmod -R 775 /u01/2.5 修改Oracle用户下变量echo """export ORACLE_SID=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=\${ORACLE_BASE}/product/19c/db_1 export NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS" export NLS_LANG=american_america.ZHS16GBK export TNS_ADMIN=\$ORACLE_HOME/network/admin export ORA_NLS11=\$ORACLE_HOME/nls/data export PATH=.:\${PATH}:\$ORACLE_HOME/bin:/usr/bin:/usr/bin/X11:/usr/local/bin: export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib # export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib:\$ORACLE_HOME/network/jlib export CV_ASSUME_DISTID=OL7 export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp""" >> /home/oracle/.bash_profile2.6 安装软件支持包yum -y install bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXtst libX11 libXau libxcb libXi libXrender make smartmontools sysstat kmod* sysstat unixODBC unixODBC-devel libnsl lm_sensors-libs ksh make glibc-devel libaio-devel libstdc++-devel需要单独安装的软件包yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/compat-libcap1-1.10-7.el7.x86_64.rpm yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/sysstat-10.1.5-20.0.3.el7_9.x86_64.rpm3. 安装数据库软件3.1 软件准备`将Oracle 软件上传到服务器 修改用户组为oracle:oinstall` chown oracle:oinstall LINUX.X64_193000_db_home.zip `将Oracle软件移动到 $ORACLE_HOME 目录` mv LINUX.X64_193000_db_home.zip /u01/app/oracle/product/19c/db_1/ `oracle用户解压Oracle 软件` su - oracle cd $ORACLE_HOME unzip LINUX.X64_193000_db_home.zip3.2 软件安装3.2.1 运行安装命令./runInstaller3.2.2 Cunfiguration OptionSet Up Software Only3.2.3 Database Installation OptionSingle Instance database installation3.2.4 Database EditionEnterprise Edition3.2.5 Installation Location默认位置3.2.6 Create Inventory默认位置3.2.7 Operating System Groups默认用户组3.2.8 Root Script execution勾选Automatically run configuration scripts 并填写密码，自动执行root脚本。也可不勾选，手动执行root脚本3.2.9 Prerequisite Checks分析检查结果，根据情况判断是否需要处理3.2.10 Summary检查安装选项3.2.11 Install Product选yes 自动运行脚本3.2.12Finish完成4. 创建数据库4.1 数据库安装准备Oracle 用户登录服务器4.2 数据库创建4.2.1 运行创建命令dbca4.2.2 Database OperationCreate a database4.2.3 Creation ModeAdvanced configuration4.2.4 Deployment Type根据业务情况选择安装类型4.2.5 Database Identification输入数据库实例名根据情况选择是否创建cdb4.2.6 Storage Option根据情况选择,本此测试默认选项4.2.7 Fast Recovery Option指定fra参数，以及归档情况4.2.8 NetworkConfiguration勾选Create a new listenerListener name : LISTENERListener port: 15214.2.9 Data Vault Option默认4.2.10 Configuration Options根据情况配置内存参数和字符集信息4.2.11 Management Options根据情况选择是否开启EM4.2.12 User Credentials创建密码4.2.13 Creation Option根据业务情况选择，本次测试默认4.2.14 Summary检查安装信息4.2.15 Progress Page等待安装过程4.2.16 Finish完成5. 创建DG环境5.1 创建DG虚拟机环境参照本文章1，2，3，4章节创建服务器和安装数据库软件5.2 备库创建监听器数据库安装完成后创建监听器5.2.1 Oracle 用户创建监听器netca按照界面提示创建一个监听器5.3 主库配置DG环境5.3.1 主库配置检查检查archivelog是否开启SQL> archive log list; #执行命令检查archivelog状态 Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5检查flashbackSQL> SELECT flashback_on FROM v$database; #执行命令检查flashback状态 FLASHBACK_ON ------------------------------------ NO SQL> show parameter db_recovery; #执行命令检查闪回位置及大小 NAME TYPE VALUE ------------------------------------ ---------------------- ---------------------------------- db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 8256M检查主库日志文件SQL> select * from v$logfile order by group#; GROUP# STATUS TYPE MEMBER IS_REC CON_ID ---------- -------------- -------------- --------------------------------------------------------------------------- ------ ---------- 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_lt1c27xy_.log NO 0 1 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_lt1c28l0_.log YES 0 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_lt1c283q_.log NO 0 2 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_lt1c29cj_.log YES 0 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_lt1c2852_.log NO 0 3 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_lt1c2984_.log YES 0 6 rows selected.检查主库系统参数SQL> show parameter db_unique_name; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_unique_name string orcl SQL> show parameter log_archive_config; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_config string SQL> show parameter log_archive_dest_1; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> show parameter log_archive_dest_state_1; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable SQL> show parameter log_archive_dest_state_2; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable SQL> show parameter fal_server; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ fal_server string SQL> show parameter fal_client; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ fal_client string SQL> show parameter archive_lag_target; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ archive_lag_target integer 0 SQL> show parameter log_archive_format; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> show parameter standby_file_management; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ standby_file_management string MANUAL SQL> show parameter db_file_name_convert; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_file_name_convert string pdb_file_name_convert string SQL> show parameter log_file_name_convert; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_file_name_convert string5.3.2 主库重启到mount状态SQL> shutdown immediate; #关闭数据库 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; #启动到mount模式 ORACLE instance started. Total System Global Area 813692064 bytes Fixed Size 8901792 bytes Variable Size 218103808 bytes Database Buffers 578813952 bytes Redo Buffers 7872512 bytes Database mounted.5.3.3 修改归档信息和闪回信息SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch_orcl'; #设置归档路径 System altered. SQL> alter database archivelog; #设置启用归档 Database altered. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=spfile; #设置闪回位置 System altered. SQL> alter system set db_recovery_file_dest_size=8G; #设置闪回大小 System altered. SQL> alter database flashback on; #设置启用闪回 Database altered.5.3.4 配置日志组SQL> select * from v$logfile; #查询当前日志组信息 GROUP# STATUS TYPE MEMBER IS_REC CON_ID ---------- -------------- -------------- ----------------------------------------------------------------------------- ------ ---------- 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_lt1c2852_.log NO 0 3 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_lt1c2984_.log YES 0 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_lt1c283q_.log NO 0 2 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_lt1c29cj_.log YES 0 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_lt1c27xy_.log NO 0 1 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_lt1c28l0_.log YES 0 6 rows selected. SQL> alter database add standby logfile group 4 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo4.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo4.log') size 200M; #主库为备库添加日志组4 Database altered. SQL> alter database add standby logfile group 5 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo5.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo5.log') size 200M; #主库为备库添加日志组5 Database altered. SQL> alter database add standby logfile group 6 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo6.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo6.log') size 200M; #主库为备库添加日志组6 Database altered. SQL> alter database add standby logfile group 7 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo7.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo7.log') size 200M; #主库为备库添加日志组7 Database altered.5.3.5 修改spfile参数#设置db_unique_name数据库唯一标识名 SQL> alter system set db_unique_name='primary' scope=spfile; System altered. #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 SQL> alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile; System altered. #设置日志归档路径1 SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile; System altered. #设置日志归档路径2 SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby' scope=spfile; System altered. #启用归档路径1 SQL> alter system set log_archive_dest_state_1='enable' scope=spfile; System altered. #启用归档路径2 SQL> alter system set log_archive_dest_state_2='enable' scope=spfile; System altered. #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） SQL> alter system set fal_server='standby' scope=spfile; System altered. #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） SQL> alter system set fal_client='primary' scope=spfile; System altered. #设置归档日志强制切换时间（s）1800表示30分钟 SQL> alter system set archive_lag_target=1800 scope=spfile; System altered. #设置归档日志文件名命名方式 SQL> alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; System altered. #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 SQL> alter system set standby_file_management=auto scope=spfile; System altered. #设置数据文件名称的映射关系（注意顺序不能变） SQL> alter system set db_file_name_convert='standby','primary' scope=spfile; System altered. #设置日志文件名称的映射关系（注意顺序不能变） SQL> alter system set log_file_name_convert='standby','primary' scope=spfile; System altered.5.3.6 修改数据库状态SQL> alter database open; #启动数据库到OPEN状态 Database altered. SQL> shutdown immediate; #关闭数据库 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; #使用修改后的spfile启动数据库，检查是否有报错 ORACLE instance started. Total System Global Area 813692064 bytes Fixed Size 8901792 bytes Variable Size 218103808 bytes Database Buffers 578813952 bytes Redo Buffers 7872512 bytes Database mounted. Database opened.5.3.7 创建pfile文件SQL> create pfile from spfile; #从spfile创建pfile文件 File created.5.3.8 主库修改网络监听修改监听文件/u01/app/oracle/product/19c/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) #host为当前主机名 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) #新增以下内容 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = orcl) #改为实例名 (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = orcl) #改为实例名 ) )修改主库客户端配置文件/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) # 新增内容 PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) #HOST填写主库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521)) #HOST填写备库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) )5.3.9 主库重启和测试监听[oracle@localhost]$ lsnrctl reload #重加载监听文件 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:40:45 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) The command completed successfully [oracle@localhost]$ lsnrctl status #查看监听状态 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:40:50 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 12-JAN-2024 11:01:57 Uptime 0 days 4 hr. 38 min. 53 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "primary" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@localhost]$ tnsping primary #测试primary连接可用性 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:41:17 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (0 msec) [oracle@localhost]$ tnsping standby #测试standby连接可用性 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:41:20 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (40 msec)5.3.10 主库文件复制到备库[oracle@localhost]$ cd /u01/app/oracle/product/19c/db_1/dbs #定位到主库$ORACLE_HOME/dbs/ [oracle@localhost dbs]$ scp initorcl.ora orapworcl orcldg:/u01/app/oracle/product/19c/db_1/dbs/ # scp远程复制到备库 oracle@orcldg's password: initorcl.ora 100% 1805 292.5KB/s 00:00 orapworcl 100% 2048 410.2KB/s 00:005.4 备库配置5.4.1 备库监听文件配置修改监听文件/u01/app/oracle/product/19c/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) #host为当前主机名 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) #新增以下内容 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = orcl) #改为实例名 (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = orcl) #改为实例名 ) )修改主库客户端配置文件/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) # 新增内容 PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) #HOST填写主库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521)) #HOST填写备库IP或者主机名 ) (CONNECT_DATA = (SERVICE_NAME = orcl) #实例名 ) )5.4.2 备库重启和测试监听[oracle@localhost]$ lsnrctl reload #重加载监听配置文件 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:40 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) The command completed successfully [oracle@localhost]$ tnsping primary #测试primary连接可用性 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:44 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (10 msec) [oracle@localhost]$ tnsping standby #测试standby连接可用性 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:49 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (30 msec)5.4.3 备库修改pfile文件修改主库复制过来的pfile文件/u01/app/oracle/product/19c/db_1/dbs/initorcl.oraorcl.__data_transfer_cache_size=0 orcl.__db_cache_size=541065216 orcl.__inmemory_ext_roarea=0 orcl.__inmemory_ext_rwarea=0 orcl.__java_pool_size=0 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=272629760 orcl.__sga_target=813694976 orcl.__shared_io_pool_size=37748736 orcl.__shared_pool_size=213909504 orcl.__streams_pool_size=0 orcl.__unified_pga_pool_size=0 *.archive_lag_target=1800 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_lt1c25mt_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_lt1c25nt_.ctl' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' #修改点1 *.db_file_name_convert='primary','standby' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=8589934592 #修改点2 *.db_unique_name='standby' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' #修改点3 *.fal_client='standby' #修改点4 *.fal_server='primary' *.local_listener='LISTENER_ORCL' *.log_archive_config='DG_CONFIG=(primary,standby)' #修改点5 *.log_archive_dest_1='location=/u01/app/oracle/arch_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' #修改点6 *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' #修改点7 *.log_file_name_convert='primary','standby' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=259m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=775m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'除上述修改点外，如备库配置与主库不一致，还需注意sga，pga相关配置如主库为rac环境，还需修改控制文件路径，数据文件路径，归档日志路径5.4.4 位置创建根据pfile文件中的位置检查是否存在，不存在的则创建#根据pfile中的路径创建 mkdir -p /u01/app/oracle/{admin/orcl/adump,oradata/ORCL/controlfile,fast_recovery_area/ORCL,arch_orcl}5.4.5 测试pfile并创建spfileSQL> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora'; # 测试启动到nomount模式 ORACLE instance started. Total System Global Area 813692064 bytes Fixed Size 8901792 bytes Variable Size 218103808 bytes Database Buffers 578813952 bytes Redo Buffers 7872512 bytes SQL> create spfile from pfile='/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora'; # 创建spfile文件 File created. 5.4.6 备库RMAN连接主备库进行首次同步[oracle@localhost dbs]$ rman target sys/Xx961024@primary auxiliary sys/Xx961024@standby #RMAN连接主备库 Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 12 16:32:50 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1685839813) connected to auxiliary database: ORCL (not mounted) duplicate target database for standby from active database nofilenamecheck; #执行此命令为不自动回复 RMAN> duplicate target database for standby from active database dorecover nofilenamecheck; #执行首次同步，带自动恢复 ......5.4.7 首次同步完成后配置日志同步SQL> alter database recover managed standby database disconnect from session; #启动同步 Database altered.5.4.8 检查同步状态SQL> select process, status, GROUP#, SEQUENCE#, block#, blocks from v$managed_standby; #检查同步状态 PROCESS STATUS GROUP# SEQUENCE# BLOCK# BLOCKS ------------------ ------------------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ARCH CONNECTED N/A 0 0 0 DGRD ALLOCATED N/A 0 0 0 DGRD ALLOCATED N/A 0 0 0 ARCH CONNECTED N/A 0 0 0 ARCH CONNECTED N/A 0 0 0 ARCH CONNECTED N/A 0 0 0 RFS IDLE N/A 0 0 0 RFS IDLE 3 12 456 1 RFS IDLE N/A 0 0 0 MRP0 WAIT_FOR_LOG N/A 12 0 0 10 rows selected.5.4.9 检查日志应用状态SQL> select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #检查1小时内日志同步情况 NAME SEQUENCE# APPLIED COMPLETETIME ------------------------------------------------------- ---------- ------------------ -------------------------------------- /u01/app/oracle/arch_orcl/1_11_1158059079.arc 11 YES 2024-01-12 16:41:12 SQL>5.4.10 DG配置完成DG 配置完成6. DG其他命令6.1 备库启动数据同步#完成后关闭数据库，启动到mount模式 startup mount; alter database recover managed standby database disconnect from session; #如果要停止同步 SQL> alter database recover managed standby database cancel; #注：如果想让备库比主库延迟几分钟可以启动为如下方式 SQL> alter database recover managed standby database delay 5 disconnect from session;6.2 恢复后数据测试#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成） select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #主库上切换日志文件再观察有没有正确同步到备库 alter system switch logfile;6.3 验证数据库状态SQL> select switchover_status from v$database; #主库状态为 TO STANDBY表示可切换为备库 #备库状态为 NOT ALLOWED6.4 主备切换##执行切换命令，并将主库转换为备库角色 ##主库执行切换： SQL> alter database commit to switchover to physical standby with session shutdown; ##主库角色转换为备库： SQL> shutdown immediate; SQL> startup nomount; SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; ##备库执行切换： SQL> alter database commit to switchover to primary; SQL> shutdown immediate; SQL> startup ##切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。 ##注意： ##如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。 alter database commit to switchover to physical standby with session shutdown; ##在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY6.5 故障切换##当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。 SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database finish force; SQL> alter database commit to switchover to primary; SQL> alter database open; SQL> select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY6.6 通过flashback重建备库##当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建 ##A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号： SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ----------------------------------------------------------------------- 1400381 ##B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换 SQL> shutdown immediate; SQL> startup mount; SQL> flashback database to scn 1400381; 闪回完成。 SQL> alter database convert to physical standby; SQL> shutdown immediate; SQL> startup mount; ##C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库） SQL> alter system set log_archive_dest_state_2=enable; SQL> alter system switch logfile; ##D). 回到原主库（当前备库）开启日志应用进程 SQL> alter database recover managed standby database using current logfile disconnect; ##此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态。6.7 备注##Oracle Dtataguard有三种保护模式： Maximum Availability Maximum Performance Maximum Protection AFFIRM NOAFFIRM AFFIRM SYNC ASYNC SYNC ##Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。 ##通过select protection_mode from v$database查询保护模式 SQL> select protection_mode from v$database; PROTECTION_MODE ---------------------------------------- MAXIMUM PERFORMANCE ##备注2： ##当主备同步有问题时候可以通过查询错误输出来初步诊断 SQL> select error from v$archive_dest where target='STANDBY'; ##备注： ##当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。 ##查询某一个时间点的scn号： timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual; -
Centos7 安装Oracle19c环境准备 CentOS_7 Install Oracle 19c configuration1.安装操作系统系统磁盘分配：/boot 200M/boot/efi 300Mswap 16384M/ all系统版本：CentOS-7-x86_64-DVD-2009.isoOracle版本：LINUX.X64_193000_db_home.zip2.系统准备2.1内核参数调整vi /etc/sysctl.conf kernel.shmall = 264060061 #填入命令echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc 的运行结果 kernel.shmmax = 865272007884 #填入命令echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##' 的运行结果 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 104857 vm.swappiness = 0 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100 sysctl -p #使参数生效2.2.关闭selinux及防火墙sed -i 's/enforcing/disabled/g' /etc/selinux/config setenforce 0 service firewalld stop chkconfig firewalld off service NetworkManager stop chkconfig NetworkManager off2.3.用户组相关设置groupadd -g 1003 oinstall groupadd -g 1001 dba groupadd -g 1002 oper useradd -u 1001 -g oinstall -G dba,oper oracle echo oracle |passwd --stdin oracle2.4.创建目录赋予权限mkdir -p /u01/app/oracle/product/19c/db_1 chown -R oracle:oinstall /u01/ chmod -R 775 /u01/2.5.修改Oracle变量(oracle用户下执行)vi .bash_profile ORACLE_SID=; export ORACLE_SID ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=${ORACLE_BASE}/product/19c/db_1; export ORACLE_HOME NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS"; export NLS_DATE_FORMAT NLS_LANG=american_america.ZHS16GBK; export NLS_LANG TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11 PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin PATH=${PATH}:/usr/bin:/usr/bin/X11:/usr/local/bin export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export CLASSPATH THREADS_FLAG=native; export THREADS_FLAG export TEMP=/tmp export TMPDIR=/tmp umask 0002.6.安装软件包mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/yum -y install \ bc \ binutils \ compat-libcap1 \ compat-libstdc++-33 \ elfutils-libelf \ elfutils-libelf-devel \ fontconfig-devel \ gcc \ gcc-c++ \ glibc \ glibc-devel \ ksh \ libgcc \ libstdc++ \ libstdc++-devel \ libaio \ libaio-devel \ libXtst \ libX11 \ libXau \ libxcb \ libXi \ libXrender \ make \ smartmontools \ sysstat \ kmod* \ sysstat \ unixODBC \ unixODBC-devel
-
Oracle 11g集群Rac安装 Centos7 Oracle 11g RAC 双节点一、规划1.1、系统规划磁盘名称磁盘大小作用/dev/sda20GOS 操作系统1.2、网络规划主机名称rac1rac2public ip172.16.130.200172.16.130.210private ip10.0.80.20010.0.80.210vip172.16.130.201172.16.130.211scan ip172.16.130.220172.16.130.2201.3、磁盘规划采用Free NAS 的 iSCSI 块共享存储磁盘名称磁盘大小ASM磁盘名称作用/dev/sdb2Gasm-ocr1OCR/Voting File/dev/sdc2Gasm-ocr2OCR/Voting File/dev/sdd2Gasm-ocr3OCR/Voting File/dev/sde20Gasm-dataData Files二、系统环境及参数设置2.1、YUM仓库配置（双节点）mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/ yum -y install autoconf automake binutils binutils-devel bison cpp dos2unix ftp gcc gcc-c++ lrzsz python-devel compat-libcap1 compat-libstdc* libXpm-*.i686 libXext libXext.i686 libX11 libX11.i686 libxcb libxcb.i686 libaio* libaio-devel libaio-devel.i686 ksh libXp libaio-devel numactl numactl-devel make sysstat unixODBC unixODBC-devel elfutils-libelf-devel redhat-lsb-core unzip libXrender libXrender-devel nfs-utils smartmontools tigervnc* libvirt* e2fsprogs.x86_64 libXau* libXi* libXtst* libstdc* glibc* libgcc* ksh gcc-c++*2.2、编辑HOSTS文件（双节点）vim /etc/hosts # 添加内容 #scanip 172.16.130.220 racscan #rac1 172.16.130.200 rac1 10.0.80.200 rac1pri 172.16.130.201 rac1vip #rac2 172.16.130.210 rac2 10.0.80.210 rac2pri 172.16.130.211 rac2vip2.3、创建用户和组（双节点）/usr/sbin/groupadd -g 5001 oinstall /usr/sbin/groupadd -g 5002 dba /usr/sbin/groupadd -g 5003 oper /usr/sbin/groupadd -g 5004 asmadmin /usr/sbin/groupadd -g 5005 asmoper /usr/sbin/groupadd -g 5006 asmdba /usr/sbin/useradd -g oinstall -G dba,asmdba,oper -d /home/oracle -m oracle /usr/sbin/useradd -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid -m grid # 修改用户 oracle 及 grid 密码 echo "oracle" | passwd --stdin oracle echo "grid" | passwd --stdin grid2.4、创建文件夹及授权（双节点）mkdir -p /u01/app/{grid,11g/grid,oracle/product/11g/db_01,oraInventory} chown -R grid:oinstall /u01/app/grid chown -R grid:oinstall /u01/app/11g chown -R oracle:oinstall /u01/app/oracle chown -R grid:oinstall /u01/app/oraInventory chmod -R 775 /u012.5、用户资源限制配置（双节点）vim /etc/security/limits.conf # 添加内容 oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft stack 10240 oracle hard stack 32768 grid soft nproc 2047 grid hard nproc 16384 grid soft nofile 1024 grid hard nofile 65536 grid soft stack 10240 grid hard stack 32768 * soft memlock unlimited * hard memlock unlimited root soft stack 10240 root hard stack 327682.6 、将资源限制加入验证模块（双节点）echo "session required pam_limits.so" >> /etc/pam.d/login2.7、修改内核参数（双节点）vim /etc/sysctl.conf # 编辑文件添加以下内容 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 kernel.panic_on_oops = 1 kernel.shmmax = 7730941132 kernel.shmall = 1887436 kernel.shmmni = 4096 # 执行命令刷新生效 sysctl -p2.8、关闭透明大页（双节点）vim /etc/rc.local # 添加内容 if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defrag fi2.9、 关闭防火墙（双节点）systemctl stop firewalld.service systemctl disable firewalld.service2.10、关闭SELinux（双节点）sed -i '/^SELINUX=.*/ s//SELINUX=disabled/' /etc/selinux/configvim /etc/sysconfig/selinux # 修改内容 SELINUX=disabled2.11 关闭其他不需要的服务（双节点-可选）systemctl stop avahi-dnsconfd systemctl stop avahi-daemon systemctl stop cups systemctl stop postfix systemctl stop smartd systemctl disable avahi-dnsconfd systemctl disable avahi-daemon systemctl disable cups systemctl disable postfix systemctl disable smartd三、编辑用户环境变量3.1、GRID用户环境变量3.1.1、节点1su - grid vim .bash_profile # 添加内容 PS1="[`whoami`@`hostname`:"'$PWD]$' export PS1 umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=rac1 ORACLE_SID=+ASM1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM ORACLE_BASE=/u01/app/grid; export ORACLE_BASE ORACLE_HOME=/u01/app/11g/grid; export ORACLE_HOME NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMAT PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin; export PATH THREADS_FLAG=native; export THREADS_FLAG if [ $USER = "oracle" ] || [ $USER = "grid" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi3.1.2、节点2su - grid vim .bash_profile # 添加内容 PS1="[`whoami`@`hostname`:"'$PWD]$' export PS1 umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=rac2 ORACLE_SID=+ASM2; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM ORACLE_BASE=/u01/app/grid; export ORACLE_BASE ORACLE_HOME=/u01/app/11g/grid; export ORACLE_HOME NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMAT PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin; export PATH THREADS_FLAG=native; export THREADS_FLAG if [ $USER = "oracle" ] || [ $USER = "grid" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi3.2、ORACLE用户环境变量3.2.1、节点1su - oracle vim .bash_profile # 添加内容 PS1="[`whoami`@`hostname`:"'$PWD]$' export PS1 export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=rac1 export ORACLE_UNQNAME=SALESERP ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11g/db_01; export ORACLE_HOME ORACLE_SID=SALES1; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMAT NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG PATH=.:$PATH:$HOME/bin:$ORACLE_BASE/product/11g/db_01/bin:$ORACLE_HOME/bin; export PATH THREADS_FLAG=native; export THREADS_FLAG if [ $USER = "oracle" ] || [ $USER = "grid" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi3.2.2、节点2su - oracle vim .bash_profile # 添加内容 PS1="[`whoami`@`hostname`:"'$PWD]$' export PS1 export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=rac2 export ORACLE_UNQNAME=SALESERP ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11g/db_01; export ORACLE_HOME ORACLE_SID=SALES2; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"; export NLS_DATE_FORMAT NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;export NLS_LANG PATH=.:$PATH:$HOME/bin:$ORACLE_BASE/product/11g/db_01/bin:$ORACLE_HOME/bin; export PATH THREADS_FLAG=native; export THREADS_FLAG if [ $USER = "oracle" ] || [ $USER = "grid" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi四、SSH 互信4.1、GRID用户互信4.1.1、节点1su - grid rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa4.1.2 节点2su - grid rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa4.1.3 节点1追加互信并拷贝至节点2cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp /home/grid/.ssh/authorized_keys rac2:~/.ssh/authorized_keys4.1.4 节点1验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1pri date ssh rac2pri date4.1.5 节点2验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1pri date ssh rac2pri date4.2 ORACLE用户互信4.2.1 节点1su - oracle rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa4.2.2 节点2su - oracle rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa4.2.3 节点1追加互信并拷贝至节点2cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp /home/oracle/.ssh/authorized_keys rac2:~/.ssh/authorized_keys4.2.4 节点1验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1pri date ssh rac2pri date4.2.5 节点2验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1pri date ssh rac2pri date五、存储划分本次测试采用Free NAS 的iSCSI块共享服务5.1、挂载iSCSI配置好iscsi后，通过iscsi发现并挂载#发现iscsi target [root@rac1 ~]# iscsiadm -m discovery -t st -p 10.0.80.190 10.0.80.190:3260,-1 iqn.2015-10.org.freenas.ctl:freenas-iscsi #登录iscsi target [root@rac1 ~]# iscsiadm -m node -T iqn.2015-10.org.freenas.ctl:freenas-iscsi -l Logging in to [iface: default, target: iqn.2015-10.org.freenas.ctl:freenas-iscsi, portal: 10.0.80.190,3260] (multiple) Login to [iface: default, target: iqn.2015-10.org.freenas.ctl:freenas-iscsi, portal: 10.0.80.190,3260] successful.5.2、将盘添加到udev规则和用户组#将重命名的盘添加udev规则和所属用户、用户组 for i in b c d e; do echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\", SUBSYSTEM==\"block\", PROGRAM==\"/usr/lib/udev/scsi_id -g -u -d \$devnode\",RESULT==\"`/usr/lib/udev/scsi_id -g -u -d /dev/sd$i`\", RUN+=\"/bin/sh -c 'mknod /dev/asm-diskb b \$major \$minor; chown grid:asmadmin /dev/asm-diskb; chmod 0660 /dev/asm-diskb'\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done #使配置生效 [root@rac1 ~]# partprobe /dev/sd[b-e] [root@rac2 ~]# udevadm control --reload [root@rac2 ~]# udevadm trigger六、GRID集群软件安装1、上传集群软件包将p13390677_112040_Linux-x86-64_3of7.zip上传到rac1上边解压p13390677_112040_Linux-x86-64_3of7.zip到/u01/app/11g/grid文件夹中进入文件夹/u01/app/11g/grid2、安装新集群主节点安装/u01/app/11g/grid/cv/rpm/cvuqdisk-1.0.9-1.rpm，安装后将/u01/app/11g/grid/cv/rpm/cvuqdisk-1.0.9-1.rpm包传到其他节点安装运行命令：./runInstaller依次选择以下选项Skip software updates; #Next> Install and Configure Oracle Grid Infrastructure for a Cluster #Next> Advanced Installation #Next> 选择语言 #Next> Cluster Name:ora-rac #集群名称 SCAN Name:racscan #hosts中的SCAN IP 的解析名 SCAN Port:1521 取消 Configure GNS #Next> Public HostName Virtual HostName rac1 rac1vip #本机信息 rac2 rac2vip #在这里添加其他节点的信息 #Next> Interface Name Subnet Use for ens33 172.16.130.0 Public ens34 10.0.80.0 Private #Next> Oracle Automatic Storage Management（Oracle ASM） #Next> Disk Group Name DATA Redundancy Normal AU Size 4MB ADD Disks #点击change Discovery Path 更改位asm磁盘位置 /dev/asm* 选择创建磁盘组的磁盘，三个2G的磁盘 #Next> Use same passwords for there accounts; 输入密码 #Next> Do not use Intelligent Platform Management Interface (IPMI) #Next> 不做变动 #Next> 不做变动 #Next> 不做变动 #Next> 不做变动 #Next> I Gnore All打上勾 #Next> #Install 按要求在两个节点使用root用户执行脚本`/u01/app/oraInventory/orainstRoot.sh`和`/u01/app/11g/grid/root.sh`
-
WindowsServer配置Oracle11g DG Oracle 11g DG服务器环境操作系统 Server 2012 R2数据库版本 Oracle 11G 11.2.0.4.0配置准备主库安装数据库，备库只需要安装数据库软件安装路径主备库的路径要一致DG配置主库开启强制归档和闪回恢复sqlplus / as sysdba shutdown immediate startup mount alter database archivelog; alter database force logging; alter database flashback on;主库为备库创建日志文件set wrap off; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- --------------- 1 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG 2 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG 3 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG # 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组 alter database add standby logfile group 4 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo004.log') size 50m; alter database add standby logfile group 5 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo005.log') size 50m; alter database add standby logfile group 6 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo006.log') size 50m; alter database add standby logfile group 7 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo007.log') size 50m; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- ---------------------------------------------------- 1 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG 2 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG 3 ONLINE D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG 4 STANDBY D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO004.LOG 5 STANDBY D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO005.LOG 6 STANDBY D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO006.LOG 7 STANDBY D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO007.LOG主库修改系统参数#设置db_unique_name alter system set db_unique_name='primary' scope=spfile; #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile; #设置日志归档路径1 alter system set log_archive_dest_1='location=D:\app\Administrator\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile; #设置日志归档路径2 alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby' scope=spfile; #启用归档路径1 alter system set log_archive_dest_state_1='enable' scope=spfile; #启用归档路径2 alter system set log_archive_dest_state_2='enable' scope=spfile; #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） alter system set fal_server='standby' scope=spfile; #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） alter system set fal_client='primary' scope=spfile; #设置归档日志强制切换时间（s）1800表示30分钟 alter system set archive_lag_target=1800 scope=spfile; #设置归档日志文件名命名方式 alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 alter system set standby_file_management=auto scope=spfile; #设置数据文件名称的映射关系（注意顺序不能变） alter system set db_file_name_convert='standby','primary' scope=spfile; #设置日志文件名称的映射关系（注意顺序不能变） alter system set log_file_name_convert='standby','primary' scope=spfile;主库重新启动数据库alter database open; shutdown immediate; startup;主库创建pfile文件create pfile from spfile; 文件位置：D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA主库创建密码文件默认情况下已经生成了密码文件，位置在：D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA如果没有生成该文件，可以手工进行创建C:\Users\Administrator> orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=Flexsystem123 entries=5主库配置网络监听Oracle服务器端，配置listener配置文件，添加一个orcl的静态注册 # listener.ora Network Configuration File: D:\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll") ) #(SID_DESC = # (GLOBAL_DBNAME = orcl) # (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) # (SID_NAME = orcl) #) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1)(PORT = 1521)) //这里的主机名orcl1不要修改，windows的怪脾气，修改后监听会有问题 ) ) ADR_BASE_LISTENER = D:\app\Administrator Oracle客户端，配置tnsnames.ora # tnsnames.ora Network Configuration File: D:\tnsnames.ora # Generated by Oracle configuration tools. #PRIMARY = # (DESCRIPTION = # (ADDRESS_LIST = # (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.21)(PORT = 1521)) # ) # (CONNECT_DATA = # (SERVICE_NAME = orcl) # ) # ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) #STANDBY = # (DESCRIPTION = # (ADDRESS_LIST = # (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.22)(PORT = 1521)) # ) # (CONNECT_DATA = # (SERVICE_NAME = orcl) # ) # ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )主库测试监听在服务管理器里面重启listener服务和oracle数据库C:\Users\Administrator>tnsping primaryC:\Users\Administrator>tnsping orcl在主库上tnsping测试都能ping通C:\>sqlplus scott/Flexsystem123@192.168.68.21/primaryC:\>sqlplus scott/Flexsystem123@192.168.68.21/orcl在备库上sqlplus连接数据库都能连接主库复制配置及参数文件#关闭主数据库 SQL> shutdown immediate #将主库D:\app\Administrator文件夹下面的admin、cfgtoollogs、diag、flash_recover_area文件夹及PWDorcl.ora、 listener.ora和tnsnames.ora文件拷贝到备库相应目录下。 修改listener.ora中的监听地址（主机名HOST） # listener.ora Network Configuration File: D:\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll") ) (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = # (ADDRESS = (PROTOCOL = TCP)(HOST = orcl2)(PORT = 1521)) ) ) ADR_BASE_LISTENER = D:\app\Administrator备库新建实例C:\>oradim -new -sid orcl oradim -new -sid spectra -startmode auto oradim -new -sid spectra1 -startmode auto 实例已创建。备库启动监听C:\> lsnrctl start #初次启动会在services.msc服务列表中注册Listener服务 #备库参数文件调整 #将主库的pfile文件拷贝到备库D:\app\Administrator\product\11.2.0\dbhome_1\database相应目录下并修改红色部分如下 orcl.__db_cache_size=704643072 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=704643072 orcl.__sga_target=1023410176 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=268435456 orcl.__streams_pool_size=0 *.archive_lag_target=1800 *.audit_file_dest='D:\app\Administrator\admin\orcl\adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='D:\app\Administrator\oradata\orcl\control01.ctl','D:\app\Administrator\flash_recovery_area\orcl\control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='primary','standby' *.db_name='orcl' *.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area' *.db_recovery_file_dest_size=4102029312 *.db_unique_name='standby' *.diagnostic_dest='D:\app\Administrator' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_client='standby' *.fal_server='primary' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=D:\app\Administrator\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.log_file_name_convert='primary','standby' *.memory_target=1717567488 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' #在备库新建文件夹D:\app\Administrator\archivelog #在备库上用拷贝过来修改好的参数文件启动实例 SQL> startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA'复制主库数据建立一个完整备份到临时文件夹C:\Users\Administrator>rman target / 恢复管理器: Release 11.2.0.1.0 - Production on 星期日 7月 21 22:56:47 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到目标数据库: ORCL (DBID=1541935557) RMAN> backup full database format='D:\tmp\forstandby_%u%p%s.RMN' include current controlfile for standby; #归档当前二进制日志 RMAN> sql 'alter system archive log current';将主库的完整rman备份拷贝到备库相同的路径下在备库上创建与主库相同的数据目录然后在主库的rman终端连接备库进行数据恢复C:\Users\Administrator>rman target / RMAN> connect auxiliary sys/Flexsystem123@standby RMAN> duplicate target database for standby nofilenamecheck;备库启动数据同步C:\Users\Administrator>sqlplus / as sysdba SQL> startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA' SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; SQL> create spfile from pfile; #开启数据库flashback特性 SQL> alter database flashback on; #关闭数据同步 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; #注：如果想让备库比主库延迟几分钟可以启动为如下方式 alter database recover managed standby database delay 5 disconnect from session; 验证数据同步#在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成 SQL> select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc; SQL> select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #主库上切换日志文件再观察有没有正确同步到备库 SQL> alter system switch logfile;主备库切换#在主库上查询切换状态，只有为TO STANDBY的状态才可以进行无损切换，其他状态则需要修复。 SQL> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- TO STANDBY #执行切换命令，并将主库转换为备库角色 #主库执行切换： SQL> alter database commit to switchover to physical standby with session shutdown; #主库角色转换为备库： SQL> shutdown immediate; SQL> startup nomount; SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; #备库执行切换： SQL> alter database commit to switchover to primary; SQL> shutdown immediate; SQL> startup #切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。 #注意： #如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。 alter database commit to switchover to physical standby with session shutdown; #在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY故障切换#当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。 SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database finish force; SQL> alter database commit to switchover to primary; SQL> alter database open; SQL> select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY通过flashback重建备库#当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建 #A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号： SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ----------------------------------------------------------------------- 1400381 #B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换 SQL> shutdown immediate; SQL> startup mount; SQL> flashback database to scn 1400381; 闪回完成。 SQL> alter database convert to physical standby; SQL> shutdown immediate; SQL> startup mount; #C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库） SQL> alter system set log_archive_dest_state_2=enable; SQL> alter system switch logfile; #D). 回到原主库（当前备库）开启日志应用进程 SQL> alter database recover managed standby database using current logfile disconnect; #此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态备注 #备注1： #Oracle Dtataguard有三种保护模式： Maximum Availability Maximum Performance Maximum Protection AFFIRM NOAFFIRM AFFIRM SYNC ASYNC SYNC #Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。 #通过select protection_mode from v$database查询保护模式 SQL> select protection_mode from v$database; PROTECTION_MODE ---------------------------------------- MAXIMUM PERFORMANCE #备注2： #当主备同步有问题时候可以通过查询错误输出来初步诊断 SQL> select error from v$archive_dest where target='STANDBY'; #备注： #当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。 #查询某一个时间点的scn号： timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual;
-
Oracle 19c 集群环境安装 Centos7 Oracle 19c Rac Install测试环境，Vmware + CentOS 7x64 + Oracle 11g，本次测试安装系统采用最小安装一，规划1.1，IP规划主机名public IPPrivate IPVIPScanner IPrac_110.10.10.1011.11.11.1010.10.10.810.10.10.7rac_210.10.10.1111.11.11.1110.10.10.910.10.10.71.2，磁盘规划进入Windows CMD命令行窗口到VMware安装位置执行命令创建cd C:\Program Files (x86)\VMware\VMware Workstation vmware-vdiskmanager.exe -c -s 2Gb -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\shareDisk\OCR01.vmdk" vmware-vdiskmanager.exe -c -s 2Gb -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\shareDisk\OCR02.vmdk" vmware-vdiskmanager.exe -c -s 2Gb -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\shareDisk\OCR03.vmdk" vmware-vdiskmanager.exe -c -s 20Gb -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\shareDisk\DATA.vmdk" vmware-vdiskmanager.exe -c -s 5Gb -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\shareDisk\ARCH.vmdk"二，系统配置2.1，系统配置(所有节点)进入虚拟机配置界面，添加两个网卡，一个nat，一个仅主机，IP段按照规划来配置进入系统，挂载光盘，修改yum仓库为光驱mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/2.2，添加硬盘(所有节点)将上边的硬盘添加到虚拟机，然后点击高级-勾选独立-永久-确定，集群中虚拟机都需要添加创建的几个磁盘，模式都设置成独立永久然后关闭虚拟机，打开虚拟机保存目录修改所有虚拟机的文件Oracle_Rac_1.vmx#添加以下代码 disk.locking="FALSE" disk.EnableUUID = "TRUE"修改完成后开机2.3，网络(所有节点)rac中所有服务器按照规划中的IP划分做好，两张网卡，一张public IP，一张private IP2.4，检查系统依赖(所有节点)vi /etc/yum.repos.d/iso.repo [iso] name=iso baseurl=file:///mnt enabled=1 gpgcheck=0 mount /dev/cdrom /mnt yum -y install binutils compat-libstdc++-33 glibc ksh libaio libgcc libstdc++ make compat-libcap1 gcc gcc-c++ glibc-devel libaio-devel libstdc++-devel sysstat autoconf automake binutils binutils-devel bison cpp dos2unix ftp gcc gcc-c++ lrzsz python-devel compat-libcap1 compat-libstdc* libXpm-*.i686 libXext libXext.i686 libX11 libX11.i686 libxcb libxcb.i686 libaio* libaio-devel libaio-devel.i686 ksh libXp libaio-devel numactl numactl-devel make sysstat unixODBC unixODBC-devel elfutils-libelf-devel redhat-lsb-core unzip libXrender libXrender-devel nfs-utils smartmontools tigervnc* libvirt* e2fsprogs.x86_64 libXau* libXi* libXtst* libstdc* glibc* libgcc* ksh gcc-c++*2.5，操作系统内核参数调整(所有节点)vi /etc/sysctl.conf kernel.shmall = 965322 #填入命令echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc 的运行结果 kernel.shmmax = 3163167129 #填入命令echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##' 的运行结果 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 104857 vm.swappiness = 0 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100 sysctl -p 使参数生效2.6，关闭 SElinux、防火墙(所有节点)根据安装规范，此两项内容应该已经被关闭 使用如下命令关闭：sed -i 's/enforcing/disabled/g' /etc/selinux/config setenforce 0 service firewalld stop chkconfig firewalld off service NetworkManager stop chkconfig NetworkManager off2.7，修改主机hosts文件(所有节点)#PUBLIC 10.10.10.10 rac1 10.10.10.11 rac2 #PRIVATE 11.11.11.10 rac1-priv 11.11.11.11 rac2-priv #VIP 10.10.10.8 rac1-vip 10.10.10.9 rac2-vip 10.10.10.7 rac-scan2.8，用户组相关设置(所有节点)groupadd -g 1000 oinstall groupadd -g 1001 dba groupadd -g 1002 oper groupadd -g 1003 asmadmin groupadd -g 1004 asmdba groupadd -g 1005 asmoper useradd -u 1001 -g oinstall -G dba,asmadmin,asmdba,asmoper grid useradd -u 1002 -g oinstall -G dba,oper,asmadmin,asmdba oracle echo grid |passwd --stdin grid echo grid |passwd --stdin oracle2.9，创建目录(所有节点)mkdir -p /u01/app/oraInventory chown -R grid:oinstall /u01/app/oraInventory chmod -R 775 /u01/app/oraInventory mkdir -p /u01/app/grid chown -R grid:oinstall /u01/app/grid chmod -R 775 /u01/app/grid mkdir -p /u01/app/19c/grid chown -R grid:oinstall /u01/app/19c/grid chmod -R 775 /u01/app/19c/grid mkdir -p /u01/app/oracle chown -R oracle:oinstall /u01/app/oracle chmod -R 775 /u01/app/oracle mkdir -p /u01/app/oracle/product/19c/db_1 chown -R oracle:oinstall /u01/app/oracle/product/19c/db_1 chmod -R 775 /u01/app/oracle/product/19c/db_12.10，修改系统资源限制(所有节点)echo "grid soft nproc 2047 grid hard nproc 16384 grid soft nofile 1024 grid hard nofile 65536 oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536" > /etc/security/limits.conf 2.11，修改/etc/profile，添加如下配置(所有节点)vi /etc/profile echo "if [ \$USER = \"oracle\" ] || [ \$USER = \"grid\" ]; then if [ \$SHELL = \"/bin/ksh\" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022 fi" > /etc/profile2.12，修改/etc/csh.log，添加如下配置(所有节点)vi /etc/csh.login echo "if ( \$USER = \"oracle\" || \$USER = \"grid\" ) then limit maxproc 16384 limit descriptors 65536 endif" > /etc/csh.login2.13，修改grid用户环境变量(所有节点)su - grid vi .bash_profile echo "export ORACLE_BASE=/u01/app/grid export ORACLE_SID=+ASM1 #节点2 为+ASM2 export ORACLE_HOME=/u01/app/19c/grid export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH NAME=`hostname` PS1=\"[$NAME:$LOGNAME]:\${PWD}>\" umask 022" > .bash_profile2.14，修改oracle环境变量(所有节点)su - oracle vi .bash_profile echo "export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=orcl1 #节点2 为orcl2 export ORACLE_HOME=$ORACLE_BASE/product/19c/db_1 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH NAME=`hostname` PS1=\"[$NAME:$LOGNAME]:\${PWD}>\" umask 022" > .bash_profile2.15、grid节点互信节点1su - grid rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa节点2su - grid rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa节点1追加互信并拷贝至节点2cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp /home/grid/.ssh/authorized_keys rac2:~/.ssh/authorized_keys节点1验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1-priv date ssh rac2-priv date节点2验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1-priv date ssh rac2-priv date2.16， ORACLE用户互信节点1su - oracle rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa节点2su - oracle rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa节点1追加互信并拷贝至节点2cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp /home/oracle/.ssh/authorized_keys rac2:~/.ssh/authorized_keys节点1验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1-priv date ssh rac2-priv date节点2验证互信（无需密码）ssh rac1 date ssh rac2 date ssh rac1-priv date ssh rac2-priv date2.17，设置UDEV规则绑定前需要查看下虚机的配置文件的disk.EnableUUID参数需要是TRUE，如果不是需要关闭操作系统修改参数后再重启修改文件/etc/udev/rules.d/99-oracle-asmdevices.rules，运行一下脚本：for i in b c d e f; do echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\",SUBSYSTEM==\"block\", PROGRAM==\"/lib/udev/scsi_id -g -u -d \$devnode\",RESULT==\"`/usr/lib/udev/scsi_id -g -u /dev/sd$i`\",RUN+=\"/bin/sh -c 'mknod /dev/asm-disk$i b \$major \$minor; chown grid:asmadmin /dev/asm-disk$i; chmod 0660 /dev/asm-disk$i'\"">>/etc/udev/rules.d/99-oracle-asmdevices.rules done重启UDEV以使上述设置生效：/sbin/udevadm control --reload-rules udevadm trigger三，开始安装GRID3.1，上传GRID包将LINUX.X64_193000_grid_home.zip上传到主节点grid用户下,解压到/u01/app/使用x11工具连接到grid用户，在主节点3.2，开始安装到解压的路径执行./gridSetup.sh开始安装 configure Oracle Grid Inftastructure for a New Cluster next Configure an Oracle Standalone Cluster next Create Local Scan Cluster Name: ora-rac SCAN Name: rac-scan #hosts中配置的racscan名称 SCAN Port: 1521 next 点击add，将其他节点添加到上边的列表，按照已有的节点格式 next 更改Private的接口为ASM & Private next Use Oracle Flex ASM for storage next No next 点击 Change Discovery Path... 修改为/dev/asm* ok Disk Group Name : CRS 勾选上 /dev/asm-diskb,/dev/asm-diskc,/dev/asm-diskd 三个大小一样的磁盘 next Use Same passwords for these accounts specify Password: 设置密码 next Do not use Intelligent Platform Management Interface (IPMI) next Management Options 不修改 next Operating System Groups 不修改 next Installation Location 不修改 next Create Inventory 不修改 next Root Script execution 不修改 next 检查结果过呢根据实际情况处理，如需安装包，则所有节点都要安装上 完成后 勾选上Ignore All忽略其他的 next Install # 安装过程中根据提示，使用root账户执行脚本，一个节点执行完了再执行下一个下点 next finish四，开始安装Oracle4.1，上传db包将LINUX.X64_193000_db_home.zip上传到主节点，并解压使用x11工具连接到oracle用户，在主节点4.2，开始安装到解压的路径执行./runInstaller开始安装 Set Up Software Only next Oracle Real Application Clusters database installation next 确保所有节点都被勾选 next Enterprise Edition next 确保 Oracle base 路径正确 next Operating System Groups 不修改 next Root Script execution 不修改 next 根据检查结果自行决定是否需要调整系统 next summary 检查结果，确认无误点击Install Install 安装过程中根据提示在所有节点上执行脚本 finish安装完成
-
Oracle 12c升级 Oracle 19c Oracle 12c 升级 Oracle 19c本次升级是以原库备份，恢复到目标数据库修改配置后执行dbua升级备份脚本run { allocate channel ch1 device type disk; allocate channel ch2 device type disk; sql 'alter system archive log current'; backup as compressed backupset database format '/u03/backup/fullbackup_%U_%T_%D'; release channel ch1; release channel ch2; allocate channel ch1 device type disk; allocate channel ch2 device type disk; backup as compressed backupset archivelog all format '/u03/backup/archive_%U_%T_%D'; release channel ch1; release channel ch2; allocate channel ch1 device type disk; backup format '/u03/backup/control_%U_%T_%D' current controlfile; release channel ch1; allocate channel ch1 device type disk; backup spfile format '/u03/backup/spfile_%U_%T_%D'; release channel ch1; }恢复脚本## dafafile 和 数据文件编号查询 ## select file#,name from v$datafile; ## 查询出的原数据文件编号，恢复到指定的目标位置 SQL> shutdown immediate; SQL> startup nomount; SQL> set dbid XXXXXXXXXX; SQL> restore controlfile from "xxxxxxxxx"; SQL> alter database mount; RMAN> run { allocate channel c1 device type disk; allocate channel c2 device type disk; set newname for datafile x to "xxxxxxxx.dbf"; restore database; switch datafile all; release channel c1; release channel c2; }备份归档日志##创建指定归档日志目录 mkdir /u03/backup/archivelog ##创建一张测试表 create table testtable (id number(10)); insert into testtable values(1); insert into testtable values(2); insert into testtable values(3); ##执行检查点 alter system chekpoint; ##切换日志 alter system switch logfile; ##备份全部归档日志 backup archivelog all format '/u03/backup/archivelog/rman_arch_%T_%u';恢复新的归档日志##注册归档日志 RMAN> catalog start with '/u03/backup/archivelog/'; ##恢复归档日志 RMAN> restore archivelog all; RMAN> restore archivelog from logseq 78574; RMAN> recover database until scn 86327108973; ##设置输出格式 SQL> set pagesize 1000 linesize 1000; SQL> col member for a50; ##查看redolog SQL> select a.group#,a.member,b.status from v$logfile a left join v$log b on a.group#=b.group#; ##重命名redolog SQL> alter database rename file '/u02/oradata/xxx/redo04.log' to '/u01/app/oracle/oradata/xxx/redo04.log'; ##删除redolog alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; ##新建redolog alter database add logfile group 1 ('/u01/app/oracle/oradata/xxx/redo01.log') size 256m; alter database add logfile group 2 ('/u01/app/oracle/oradata/xxx/redo02.log') size 256m; alter database add logfile group 3 ('/u01/app/oracle/oradata/xxx/redo03.log') size 256m; ##清理redolog中的信息 alter database clear logfile group x; ##升级方式启动 SQL> alter database open resetlogs upgrade;执行DBUA进行升级SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/xxx/xxx/temp1.dbf' size 30G autoextend on; SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; SQL> alter database default temporary tablespace temp1; SQL> drop tablespace temp including contents and datafiles; SQL> exit [oracle@db ~]# dbua验证##查询数据库中最后添加进去的表是否存在，存在则所有数据都还原并升级成功 SQL> select * from testtables;
