搜索到
12
篇与
的结果
-
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脚本直至完成运行任务测试结果
-
zabbix通过ODBC驱动监控Oracle zabbix ODBC 驱动监控Oracle本文档参考位置Zabbix官网odbc文档 Oracle monitoring and integration with ZabbixOracle官网odbc文档 Oracle Instant Client ODBC 安装说明 | Oracle 中国目标数据库创建用于监视Oracle DB的用户以及赋权CREATE USER zabbix IDENTIFIED BY password; -- Grant access to the zabbix user. GRANT CONNECT, CREATE SESSION TO zabbix; GRANT SELECT ON v$instance TO zabbix; GRANT SELECT ON v$database TO zabbix; GRANT SELECT ON v$sysmetric TO zabbix; GRANT SELECT ON v$system_parameter TO zabbix; GRANT SELECT ON v$session TO zabbix; GRANT SELECT ON v$recovery_file_dest TO zabbix; GRANT SELECT ON v$active_session_history TO zabbix; GRANT SELECT ON v$osstat TO zabbix; GRANT SELECT ON v$restore_point TO zabbix; GRANT SELECT ON v$process TO zabbix; GRANT SELECT ON v$datafile TO zabbix; GRANT SELECT ON v$pgastat TO zabbix; GRANT SELECT ON v$sgastat TO zabbix; GRANT SELECT ON v$log TO zabbix; GRANT SELECT ON v$archive_dest TO zabbix; GRANT SELECT ON v$asm_diskgroup TO zabbix; GRANT SELECT ON sys.dba_data_files TO zabbix; GRANT SELECT ON DBA_TABLESPACES TO zabbix; GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix; GRANT SELECT ON DBA_USERS TO zabbix;lynx监控系统安装odbc包软件包对应选择数据库版本的yum -y install unixODBC unixODBC-devel rpm -ivh oracle-instantclient19.14-basic-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-devel-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-odbc-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-sqlplus-19.14.0.0.0-1.x86_64.rpm修改配置文件## 修改Oracle tnsname配置文件 ## 编辑配置文件，在安装的版本路径下修改以下文件 ## vi /usr/lib/oracle/<version>/client64/network/admin/tnsnames.ora orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) oracle = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle) ) )修改系统环境变量增加以下内容 ## vi /etc/profile export ORACLE_HOME=/usr/lib/oracle/<version>/client64 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH export TNS_ADMIN=$ORACLE_HOME/network/admin export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin ## 修改 libsqora.so.11.1文件权限（若安装的版本存在，本测试使用的 Oralce 11g 版本） chmod +x /usr/lib/oracle/<version>/client64/lib/libsqora.so.11.1 ln -s /usr/lib/oracle/<version>/client64/lib/libsqora.so.11.1 libodbcinst.so.1创建文件并写入内容## vi /etc/odbcinst.ini [Oracle] Description = [描述自填] Driver = /usr/lib/oracle/[version]/client64/lib/libsqora.so.11.1创建文件并写入内容##vi /etc/odbc.ini [orcl] Description = [描述自填] Driver = Oracle ##上边odbcinst.ini创建的驱动名称Oracle ServerName = [Address]:[Port]/[SID] UserID = zabbix Password = <password> [oracle] Description = [描述自填] Driver = Oracle ##上边odbcinst.ini创建的驱动名称Oracle ServerName = [Address]:[Port]/[SID] UserID = zabbix Password = <password>新建文件/etc/sysconfig/zabbix-serverORACLE_HOME=/usr/lib/oracle/[version]/client64 LD_LIBRARY_PATH=/usr/lib/oracle/[version]/client64/lib:/usr/lib64 TNS_ADMIN=/usr/lib/oracle/[version]/client64/network/admin PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/[version]/client64/lib测试## 检测驱动是否正常 ldd /usr/lib/oracle/[version]/client64/lib/libsqora.so.11.1 ## 未出现not found 字样为正常 ##测试配置是否成功 isql -v orcl ## 此处的ORCL为odbc.ini中配置的名称 ## 返回以下内容为成功 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
-
Oracle 备份脚本 完整备份run { #### 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup as compressed backupset database format 'D:\rmanbackup\db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }增量备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 as compressed backupset database format 'D:\rmanbackup\inc1_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }差异备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 differential as compressed backupset database format 'D:\rmanbackup\diff_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }
-
RMAN详解 RMAN详解一、备份与RMAN备份的概念数据库完全备份：按归档模式分为归档和非归档归档模式 打开状态，属于非一致性备份 关闭状态，可以分为一致性和非一致性非归档模式打开状态，非一致性备份无效 关闭状态，一致性备份，非一致性备份不被推荐RMAN备份RMAN使用会话来完成备份操作，从RMAN客户端连接到服务器将产生一个服务器会话RMAN备份内容包括：整个数据库,表空间,数据文件,指定的数据文件,控制文件,归档日志文件,参数文件等RMAN备份的类型完整备份(full)或增量备份(incremental)一致性备份(consistent)或不一致性备份(inconsistent)热备(open)或冷备(closed)，冷备时数据库必须处于mount状态，冷备可以为一致性备份或非一致性备份完整备份一个或多个数据文件的一个完整副本,包含从备份开始处所有的数据块.完整备份不能作为增量的基础增量备份包含从最近一次备份以来被修改或添加的数据块.可以分为差异增量备份和累计增量备份差异增量备份仅仅包含n级或n级以下被修改过的数据块。备份数据量小，时间长。累计增量备份仅仅包含n-1级或n-1级以下被修改过的数据块。备份数据量大，恢复时间短。0级增量备份相当于一个完整备份,该备份包含所有已用的数据块文件,与完整备份的差异是完整备份不能用作级增量备份的基础一致性备份备份所包含的各个文件中的所有修改都具备相同的系统变化编号(system change number，SCN)。也就是说，备份所包含的各个文件中的所有数据均来自同一时间点。一致性数据库完全备份(consis-tent whole backup)进行还原(restore)后，不需要执行恢复操作(recovery)非一致性备份在数据库处于打开(open)状态时，或数据库异常关闭(shut down abnormally)后，对一个或多个数据库文件进行的备份。非一致性备份需要在还原之后进行恢复操作备份集与镜像副本备份集是包含一个或多个数据文件,归档日志文件的二进制文件的集合.备份集由备份片组成,一个备份集中可以包含一个或多个备份片可以通过filesperset参数来设置备份集中可包含的备份片数，也可以设定参数maxpiecesize来制定每个备份片的大小。备份集中空闲的数据块将不会被备份，因此备份集可以支持压缩。备份集支持增量备份，可以备份到磁盘或磁带。镜像副本是数据文件或归档日志文件等的完整拷贝,未经过任何压缩等处理,不能备份到磁带,也不支持增量备份。恢复时可以立即使用实现快速恢复。等同于操作系统的复制命令。可以作为级增量备份。备份路径可以备份到磁盘目录可以备份到磁带闪回区备份限制数据库必须处于mount或open状态不能备份联机日志在非归档模式下仅仅能作干净备份，即在干净关闭且启动到mount状态下备份在归档模式下，current状态下数据文件可以备份二、使用RMAN进行备份1、备份数据库查看数据库当前工作模式,非ARCHIVELOG模式更改为ARCHIVELOG模式SQL> select dbid,name,log_mode from v$database;------查看当前数据库是不是archivelog模式 DBID NAME LOG_MODE ---------- --------------------------- ------------------------------------ 2816063020 ORADB NOARCHIVELOG SQL> shutdown immediate------关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount------启动到nomount模式 ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes SQL> alter database mount;------挂载数据库 Database altered. SQL> archive log list------归档日志列表 Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19c/dbs/arch Oldest online log sequence 16 Current log sequence 18 SQL> alter database archivelog;------更改数据库为归档日志模式 Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/19c/dbs/arch Oldest online log sequence 16 Next log sequence to archive 18 Current log sequence 18 SQL>备份数据库创建RMAN路径[oracle@oradb ~]$ mkdir /u01/app/oracle/rmanbak/ [oracle@oradb ~]$开始备份[oracle@oradb ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 11 22:48:36 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADB (DBID=2816063020, not open) RMAN> backup database format '/u01/app/oracle/rmanbak/whole_%d_%U';------备份整个数据库 Starting backup at 11-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=273 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/whole_ORADB_02vs2eb4_1_1 tag=TAG20210411T224908 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-00 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup as compressed backupset database format '/u01/app/oracle/rmanbak/whole_%d_%U';------备份整个数据库并压缩备份集 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup s·et channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/whole_ORADB_04vs2efb_1_1 tag=TAG20210411T225123 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-01 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>2、备份数据文件RMAN> backup as copy datafile 4 format '/u01/app/oracle/rmanbak/df_%d_%U';------备份类型为镜像备份 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf output file name=/u01/app/oracle/rmanbak/df_ORADB_data_D-ORADB_I-2816063020_TS-UNDOTBS1_FNO-4_0avs2fem tag=TAG20210411T230806 RECID=1 STAMP=1069628896 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-03 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> list copy; specification does not match any control file copy in the repository specification does not match any archived log in the repository List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 1 4 A 11-APR-21 2942179 11-APR-21 NO Name: /u01/app/oracle/rmanbak/df_ORADB_data_D-ORADB_I-2816063020_TS-UNDOTBS1_FNO-4_0avs2fem Tag: TAG20210411T230806 RMAN> backup datafile 4,5 format '/u01/app/oracle/rmanbak/df_%d_%U';------备份类型为备份集 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/df_ORADB_0cvs2fj3_1_1 tag=TAG20210411T231027 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-04 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>3、备份表空间RMAN> backup tablespace users format '/u01/app/oracle/rmanbak/tb_%d_%U'; Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/tb_ORADB_08vs2fbf_1_1 tag=TAG20210411T230623 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-02 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup tablespace temp;------零时表空间不需要备份 Starting backup at 11-APR-21 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 04/11/2021 23:11:22 RMAN-20202: Tablespace not found in the recovery catalog RMAN-06019: could not translate tablespace name "TEMP" RMAN>4、备份控制文件RMAN> configure controlfile autobackup on;------自动备份控制文件置为on状态,将自动备份控制文件和参数文件 new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> backup current controlfile;------单独备份控制文件及参数文件 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0evs2fqn_1_1 tag=TAG20210411T231431 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-05 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup datafile 4 include current controlfile;------备份数据文件时包含控制文件 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0gvs2frf_1_1 tag=TAG20210411T231455 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0hvs2fri_1_1 tag=TAG20210411T231455 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-06 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup spfile format '/u01/app/oracle/rmanbak/sp_%d_%U';------单独备份SPFile Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/sp_ORADB_0jvs2fv6_1_1 tag=TAG20210411T231654 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-07 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup copies 2 device type disk spfile;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 with 2 copies and tag TAG20210411T231716 piece handle=/u01/app/oracle/product/19c/dbs/0lvs2fvs_1_1 comment=NONE piece handle=/u01/app/oracle/product/19c/dbs/0lvs2fvs_1_2 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-08 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>注：在备份system01.dbf或system表空间时将会自动备份控制文件和参数文件，即使自动备份控制文件参数为off5、备份归档日志文件备份归档日志时仅仅备份归档过的数据文件(不备份联机重做日志文件)备份归档日志时总是对归档日志做完整备份RMAN对归档日志备份前会自动做一次日志切换，且从一组归档日志中备份未损坏的归档日志RMAN会自动判断哪些归档日志需要进行备份归档日志的备份集不能包含其它类型的文件RMAN> backup 2> format '/u01/app/oracle/rmanbak/lf_%d_%U' 3> archivelog all delete input;------delete input删除所有已经备份过的归档日志 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog all delete input format '/u01/app/oracle/rmanbak/lf_%d_%U';------此种写法实现了上述相同的功能 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog sequence between 50 and 120 thread 1 delete input;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog from time "sysdate-15" until time "sysdate-7";------- Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup format'/u01/app/oracle/rmanbak/lf_%d_%U' archivelog from sequence =80 delete input;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 ------执行下面的命令,并观察备份列出的信息,可以看到使用plus archivelog时使用了上面描述的步骤来进行备份 RMAN> backup database plus archivelog format'/u01/app/oracle/rmanbak/lg_%d_%U'delete input; Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0nvs2goa_1_1 tag=TAG20210411T233018 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 Finished backup at 11-APR-21 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-09 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>使用plus archivelog时备份数据库完成的动作(backup database plus archivelog)1.首先执行alter system archive log current命令(对当前日志归档)2.执行backup archivelog all命令(对所有归档日志进行备份)3.执行backup database命令中指定的数据文件、表空间等4.再次执行alter system archive log current5.备份在备份操作期间产生的新的归档日志三、总结数据文件的备份集对于未使用的块可以执行增量备份，可以跳过未使用过的数据块来进行压缩备份对于控制文件、归档日志文件、spfile文件则是简单的拷贝，并对其进行打包压缩而已四、RMAN异机恢复恢复前准备恢复首先配置跟源库相同的Oracle环境测试环境待恢复数据库的ORACLE_SID为orcl查询源库ORCL的DBID并记录select name,dbid from v$database;[oracle@localhost ~]$ sqlplus / as sysdba------以sysdba身份进入sqlplus SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:29:22 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name,dbid from v$database;------执行查询DBID语句 NAME DBID --------------------------- ---------- ORCL 1597556564------记录这个需要恢复的库的DBID SQL>开始恢复[oracle@localhost ~]$ export ORACLE_SID=orcl------设置ORACLE_SID为即将恢复的数据库的SID [oracle@localhost ~]$ rman target /------启动rman从当前ORACLE_SID Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 14 15:54:58 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073737800 bytes Fixed Size 8904776 bytes Variable Size 276824064 bytes Database Buffers 780140544 bytes Redo Buffers 7868416 bytes RMAN> set dbid=1597556564;------设置DBID为源库的DBID executing command: SET DBID RMAN> restore spfile to pfile '/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora' from '/rmanbak/orcl/spfile_bak/spf_4_1';------恢复pfile文件 从备份的spfile文件 Starting restore at 14-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /rmanbak/orcl/spfile_bak/spf_4_1 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 14-APR-21 RMAN> shutdown immediate;------正常关闭immediate Oracle instance shut down RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; ------启动数据库到nomount状态 使用刚刚恢复出来的pfile文件 '此处启动命令可能会报错，最下边给出解决方案' Oracle instance started Total System Global Area 2466249672 bytes Fixed Size 8899528 bytes Variable Size 536870912 bytes Database Buffers 1912602624 bytes Redo Buffers 7876608 bytes RMAN> restore controlfile from '/rmanbak/orcl/controlfile_bak/cntrl_3_1'; ------执行恢复控制文件 从备份的控制文件 Starting restore at 14-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=261 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_j7bmpnh4_.ctl output file name=/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_j7bmpnjz_.ctl Finished restore at 14-APR-21 RMAN> alter database mount;------修改数据库为mount模式 released channel: ORA_DISK_1 Statement processed RMAN> crosscheck backup;------检查数据库有效性 Starting implicit crosscheck backup at 14-APR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=261 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 14-APR-21 Starting implicit crosscheck copy at 14-APR-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 14-APR-21 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 RECID=1 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 RECID=2 STAMP=1069844495 Crosschecked 2 objects RMAN> catalog start with '/rmanbak';------将备份的目录导入到控制文件中 searching for all files that match the pattern /rmanbak List of Files Unknown to the Database ===================================== File Name: /rmanbak/orcl.tar.gz File Name: /rmanbak/orcl/controlfile_bak/cntrl_3_1 File Name: /rmanbak/orcl/log/backupall_20210414103821.log File Name: /rmanbak/orcl/log/backupall_20210414104042.log File Name: /rmanbak/orcl/log/backupall_20210414110132.log File Name: /rmanbak/orcl/spfile_bak/spf_4_1 Do you really want to catalog the above files (enter YES or NO)? yes------输入yes确认 cataloging files... cataloging done List of Cataloged Files ======================= File Name: /rmanbak/orcl/controlfile_bak/cntrl_3_1 File Name: /rmanbak/orcl/spfile_bak/spf_4_1 List of Files Which Were Not Cataloged ======================================= File Name: /rmanbak/orcl.tar.gz RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414103821.log RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414104042.log RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414110132.log RMAN-07517: Reason: The file header is corrupted RMAN> crosscheck backup;------再次检查数据库有效性 using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 RECID=1 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 RECID=2 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/controlfile_bak/cntrl_3_1 RECID=3 STAMP=1069862926 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/spfile_bak/spf_4_1 RECID=4 STAMP=1069862926 Crosschecked 4 objects RMAN> restore database preview summary;------预览数据库恢复详细信息 Starting restore at 14-APR-21 using channel ORA_DISK_1 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 2 B F A DISK 14-APR-21 1 1 YES TAG20210414T110135 1 B F A DISK 14-APR-21 1 1 YES TAG20210414T110135 archived logs generated after SCN 2243398 not found in repository recovery will be done up to SCN 2243398 Media recovery start SCN is 2243398 Recovery must be done beyond SCN 2243398 to clear datafile fuzziness Finished restore at 14-APR-21 RMAN> restore database;------执行数据库恢复 重新存放数据库 Starting restore at 14-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j7bmnbjn_.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j7bmo4rx_.dbf channel ORA_DISK_1: reading from backup piece /rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 channel ORA_DISK_1: piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 tag=TAG20210414T110135 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_j7bmlmbb_.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j7bmo3o8_.dbf channel ORA_DISK_1: reading from backup piece /rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 channel ORA_DISK_1: piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 tag=TAG20210414T110135 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 14-APR-21 RMAN> recover database;------执行数据库恢复 恢复数据库、 '该条恢复命令也许会报错。就跳过执行下一条' Starting recover at 14-APR-21 using channel ORA_DISK_1 starting media recovery unable to find archived log archived log thread=1 sequence=7 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 04/14/2021 16:12:13 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 2243398 RMAN> recover database until scn 2243398;------执行数据库恢复 上条不成功执行该条恢复语句 Starting recover at 14-APR-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 14-APR-21 RMAN> exit------恢复完成 退出 Recovery Manager complete. [oracle@localhost ~]$ sqlplus / as sysdba------进入sqlplus 以sysdba身份 SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:14:56 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter database open resetlogs;------用open resetlogs 打开数据库 Database altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@localhost ~]$ sqlplus------执行sqlplus命令 SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:17:06 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter user-name: owner------使用源库中创建的用户登录 Enter password: Last Successful login time: Wed Apr 14 2021 10:35:23 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select table_name from user_tables;------查询当前用户下的所有表 TABLE_NAME -------------------------------------------------------------------------------- USERSINFO USERSID SQL> select * from usersinfo;------验证表中的数据 USER_ID SEX AGE ---------- --------- ---------- 10001 男 21 10002 女 18 SQL> select * from usersid;------验证表中的数据 USER_ID NAME ---------- ------------------------------ 10002 李四 10001 张三 SQL>恢复过程中出现过的问题执行startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora';命令的时候也许会报错RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 04/14/2021 16:02:17 RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 04/14/2021 16:03:20 RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory报错中告知找不到参数（db_create_file_dest、db_recovery_file_dest）所指定的文件或目录，检查恢复的pfile文件中该参数指定的位置应该是不存在的，创建上目录再执行startup nomount pfile="" 就没问题了
-
Oracle检查死锁 检查死锁检查死锁用sysdba用户执行以下语句select username,lockwait,status,machine,client_info,program from gv$session where sid in (select session_id from gv$locked_object);## 如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明： ## ## Username：死锁语句所用的数据库用户； ## ## Lockwait：死锁的状态，如果有内容表示被死锁。 ## ## Status： 状态，active表示被死锁 ## ## Machine： 死锁语句所在的机器。 ## ## Program： 产生死锁的语句主要来自哪个应用程序查看被死锁的语句select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));死锁的解决办法查死锁进程SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;kill死锁的进程alter system kill session ‘sid,serial#’; (其中sid=l.session_id) ## 举例 sql > alter system kill session '71,1';生成Kill Session语句SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);开启session记录IP地址create or replace trigger login_on_record_ip after logon on database begin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); end; / select username,lockwait,status,machine,client_info,program from v$session where sid = 1155; select trigger_name from all_triggers where trigger_name like '%RECORD%';杀掉所有客户端名称包含doctor.exe的sessionSELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "killcmd" FROM gv$session WHERE program like '%doctor.exe%'; set pagesize 1000 linesize 1000; spool conninfo select count(*), machine, program, username, osuser from gv$session group by machine, program, username, osuser order by count(*) desc; spool off exit
-
Oracle 部分报错处理 Oracle报错处理处理Oracle数据库启动报错 ORA-27104报错内容：system-defined limits for shared memory was misconfigured查看报错日志（我的日志位置，根据自身环境确定）cat /u01/app/oracle/diag/rdbms/oradb/oradb/alert/log.xml错误内容如下：<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>System cannot support SGA size of 2352 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Total system memory configured is 1838 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Instance maximum shared memory size should be less than 1654 MB. </txt>我的环境中，根据报错可以看出配置的SGA相关配置的内存太大了，要求小于1654MB解决办法：数据库不能启动，需要调整编辑参数文件来修改相关的SGA值我的spfile文件位置是：/u01/app/oracle/product/19c/dbs[oracle@oracle dbs]$ pwd /u01/app/oracle/product/19c/dbs [oracle@oracle dbs]$ ls hc_oradb.dat init20210220.ora init.ora lkORADB orapworadb spfileoradb.ora [oracle@oracle dbs]$先生成一个临时spfile文件来修改配置SQL> create pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora' from spfile; File created. SQL> !vi /u01/app/oracle/product/19c/dbs/init20210220.ora打开后配置如下：oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=1778384896 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=16777216 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=822083584 oradb.__sga_target=2466250752 oradb.__shared_io_pool_size=134217728 oradb.__shared_pool_size=520093696 oradb.__streams_pool_size=0 oradb.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORADB/control01.ctl','/u01/app/oracle/oradata/ORADB/control02.ctl' *.db_block_size=8192 *.db_name='oradb' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.local_listener='LISTENER_ORADB' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=782m *.processes=320 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2346m *.undo_tablespace='UNDOTBS1'将 oradb.__sga_target= 和 .sga_target= 这两个参数修改到推荐值范围，然后保存。保存之后使用心得spfile启动数据库SQL> startup pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>现在数据库能启动成功了，还需要将修改后的pfile文件覆盖原spfile文件SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; File created. SQL> startup ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>完成修改archivelog报错 ORA-00265执行过强制关闭数据库的操作就会出现的错误解决步骤：将数据库起到open状态，正常关闭shutdown immediate，再起到mount状态SQL> alter database open ;------启动数据库到open状态 Database altered. SQL> shutdown immediate------正常关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount;------启动到mount状态 ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> alter database archivelog;------执行更改archivelog Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7
-
Oracle 部分命令介绍 Oracle学习记录表空间和数据文件表空间分类系统表空间system表空间system表空间用来存储整个数据库的数据字典表（data dictionary table），该表空间不能被损坏，一旦损坏，数据库将无法打开。SQL> select ts#,name from v$tablespace where name='SYSTEM'; TS# NAME ---------- -------------------------------------------------------------------------------- 0 SYSTEM SQL>sysaux表空间从oracle 10g开始，oracle将工具放到SYSAUX,减轻system的压力。SYSAUX表空间不影响系统，但是会影响性能。SQL> select ts#,name from v$tablespace where name='SYSAUX'; TS# NAME ---------- -------------------------------------------------------------------------------- 1 SYSAUX SQL>非系统表空间undoundo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。undo数据用来：1）事务的回滚；2）实例恢复(回滚)；3）一致性读时需要构造CR块；SQL> select ts#,name from v$tablespace where name='UNDOTBS1'; TS# NAME ---------- -------------------------------------------------------------------------------- 2 UNDOTBS1 SQL> show parameter undo_tablespace; NAME TYPE VALUE ------------------------------------ -------------------- ------------------------------ undo_tablespace string UNDOTBS1 SQL>查看回滚段信息：SQL> select * from v$rollname; USN NAME CON_ID ---------- -------------------- ---------- 0 SYSTEM 0 1 _SYSSMU1_1261223759$ 0 2 _SYSSMU2_27624015$ 0 3 _SYSSMU3_2421748942$ 0 4 _SYSSMU4_625702278$ 0 5 _SYSSMU5_2101348960$ 0 6 _SYSSMU6_813816332$ 0 7 _SYSSMU7_2329891355$ 0 8 _SYSSMU8_399776867$ 0 9 _SYSSMU9_1692468413$ 0 10 _SYSSMU10_930580995$ 0 11 rows selected. SQL>temptemp表空间，即临时表空间，用来存放用户排序，分组等操作时的数据信息。SQL> select ts#,name from v$tablespace where name='TEMP'; TS# NAME ---------- -------------------- 3 TEMP SQL>users从oracle 10g开始，oracle将用户数据信息单独存放到users表空间中。SQL> select ts#,name from v$tablespace where name='USERS'; TS# NAME ---------- -------------------- 4 USERS SQL>用户表空间SQL> select ts#,name from v$tablespace where name not in('SYSTEM','SYSAUX','TEMP','USERS','UNDOTBS1'); TS# NAME ---------- -------------------- 6 HR SQL>表空间操作创建表空间SQL> create tablespace ORA_TEST_DAT datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' size 1G autoextend on next 200M maxsize 2G; Tablespace created. SQL>删除表空间不能删除的表空间为系统表空间，正在用的undo表空间，默认临时/永久表空间SQL> drop tablespace ORA_TEST_DAT including contents and datafiles; Tablespace dropped. SQL>修改表空间权限SQL> alter tablespace ORA_TEST_DAT read only; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT read write; Tablespace altered. SQL>脱机表空间SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>表空间改名sysaux system不能改名用户表空间users，undo表空间，temp表空间最好不要改名，因为系统中部分参数定义名还是以原来的表空间名，下次数据库启动会报错。SQL> alter tablespace ORA_TEST_DAT rename to ORA_TEST_DAT01; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT01 rename to ORA_TEST_DAT; Tablespace altered. SQL>表空间说明system 表空间必须online 必须是read writesysaux 表空间可以offline 不能read onlyundo 表空间不能offline 不能read only只读表空间的对象可以删除，但是不能被update和insert数据文件操作修改数据文件自动扩展SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend off; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend on; Database altered. SQL>修改数据文件大小SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 100M; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 1G; Database altered. SQL>添加数据文件SQL> alter tablespace ORA_TEST_DAT add datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' size 200M autoextend off; Tablespace altered. SQL>删除数据文件SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' offline drop; Database altered. SQL>数据文件重命名(必须要在归档模式下)如果数据库只在mont状态，那么只要在操作系统级别移动数据文件，执行数据库文件改名，启动数据库即可SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> ho cp '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' SQL> alter database rename file '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' to '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF'; Database altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>注意事项修改数据文件ALTER DATABASE 语句修改单独的 DataFileALTER TABLESPACE 语句修改所有的 DataFileARCHIVRLOG 模式下的更改 DataFile只有在 ARCHIVELOG 模式下才可使用 ALTER DATABASE 来更改DataFileALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;由于在 NOARCHIVELOG 模式下，数据文件脱机后会造成数据的遗失，所以只能使用 ALTER DATABASE 语句下带有 DATAFILE 和 OFFLINE DROP 子句的选项将该 DataFile 直接取消，例如该 DataFile 只包含临时段数据，并没有备份时：alter database datafile '/u02/oracle/rbdb1/users3.dbf' offline drop;alter tablespace ....offline,会对数据文件进行检查点，并冻结数据文件SCN表空间 online 时，Oracle 会取得当前 SCN，解冻 offline 文件 SCN，和当前 SCN同步tablespace offline 有几种选项可供选择: normal, temporary,immediate, for recovery,而在 datafile 中则没有这些选项。数据文件reuse参数如果 file 已经存在，并且在创建时指定了 file size，那么就重用原文件，并应用新的 size，如果没有指定 file size，则保留原有的大小如果 file 不存在，oracle 将忽略该参数如果 Oracle 使用了已经存在的 file，那么之前 file 里的数据将全部丢失offline dropoffline drop 并不会 drop datafile， 仅仅是将 datafile 标记为 offline， 我们 online之后还可以 recover 回来。alter database datafile '/usr/ORA_TEST_DAT01.dbf' offline drop;该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中归档模式下，alter database datafile 'file_name' offline 和 offline drop 没有什么区别。 因为offline 之后多需要进行recover 才可以online。alter database datafile '/usr/ORA_TEST_DAT01.dbf' online;recover datafile '/usr/ORA_TEST_DAT01.dbf';alter tablespace ORA_TEST_DAT drop datafile '/usr/ORA_TEST_DAT01.dbf';该语句会删除控制文件和磁盘上的文件，删除之后的原数据文件序列号可以重用。该语句只能是datafile online的时候才可以使用。如果说对应的数据文件已经是offline for drop，那么仅针对 dictionary managed tablespaces 可用。控制文件控制文件概述二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)一个控制文件只能属于一个数据库控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像控制文件中包含的内容数据库的名字、ID、创建的时间戳表空间的名字联机日志文件、数据文件的位置、个数、名字联机日志的Sequence号码检查点的信息撤销段的开始或结束归档信息备份信息查看控制文件信息列出实例中所有控制文件的名字及状态信息SQL> select * from v$controlfile; STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- /u01/app/oracle/oradata/ORADB/control01.ctl NO 16384 646 0 /u01/app/oracle/oradata/ORADB/control02.ctl NO 16384 646 0 STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- SQL>列出所有参数的位置及状态信息SQL> select name,value from v$parameter where name='control_files'; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- control_files /u01/app/oracle/oradata/ORADB/control01.ctl, /u01/app/oracle/oradata/ORADB/contr ol02.ctl SQL>列出控制文件中记录的部分信息SQL> select * from v$CONTROLFILE_RECORD_SECTION; TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE 316 1 1 0 0 0 0 CKPT PROGRESS 8180 11 0 0 0 0 0 REDO THREAD 256 8 1 0 0 0 0 REDO LOG 72 16 3 0 0 3 0 DATAFILE 520 100 8 0 0 18 0 FILENAME 524 2298 11 0 0 0 0 TABLESPACE 180 100 7 0 0 7 0 TEMPORARY FILENAME 56 100 1 0 0 1 0 RMAN CONFIGURATION 1108 50 0 0 0 0 0 LOG HISTORY 56 292 9 1 9 9 0 OFFLINE RANGE 200 163 2 1 2 2 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- ARCHIVED LOG 584 28 0 0 0 0 0 BACKUP SET 96 170 0 0 0 0 0 BACKUP PIECE 780 209 0 0 0 0 0 BACKUP DATAFILE 200 245 0 0 0 0 0 BACKUP REDOLOG 76 215 0 0 0 0 0 DATAFILE COPY 736 200 0 0 0 0 0 BACKUP CORRUPTION 44 371 0 0 0 0 0 COPY CORRUPTION 40 409 0 0 0 0 0 DELETED OBJECT 20 818 1 1 1 1 0 PROXY COPY 928 246 0 0 0 0 0 BACKUP SPFILE 124 131 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE INCARNATION 56 292 2 1 2 2 0 FLASHBACK LOG 84 2048 0 0 0 0 0 RECOVERY DESTINATION 180 1 0 0 0 0 0 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0 RMAN STATUS 116 141 0 0 0 0 0 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0 MTTR 100 8 1 0 0 0 0 DATAFILE HISTORY 568 57 0 0 0 0 0 STANDBY DATABASE MATRIX 400 128 128 0 0 0 0 GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- RESTORE POINT 256 2108 0 0 0 0 0 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0 ACM OPERATION 104 64 11 0 0 0 0 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0 PDB RECORD 780 10 0 0 0 0 0 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0 PDBINC RECORD 144 113 0 0 0 0 0 TABLESPACE KEY HISTORY 108 151 0 0 0 0 0 42 rows selected. SQL>列出控制文件的名字、状态、位置等SQL> SHOW PARAMETER CONTROL_FILES NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/ORADB/ control01.ctl, /u01/app/oracle /oradata/ORADB/control02.ctl SQL>使用STRINGS命令来查看控制文件中的具体内容[oracle@oracle ~]$ strings /u01/app/oracle/oradata/ORADB/control01.ctl备份控制文件到平面文件(然后查看控制文件中的具体内容)SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt'; Database altered. SQL>转储控制文件内容(查看控制文件中的具体内容)