1. Linux下卸载Oracle1.1 方式一：使用自带的deinstall # 卸载GRID，grid用户执行 [grid@orclasm ~]$ ORACLE_HOME/deinstall/deinstall # 卸载oracle，oracle用户执行 [oracle@orclasm ~]$ ORACLE_HOME/deinstall/deinstall # 过程较慢，也可能出现不成功的现象。1.2 方式二：手动删除手动卸载是删除一系列软件及配置信息1.2.1 停止数据库和监听 # oracle 用户 [oracle@orclasm ~]$ lsnrctl stop [oracle@orclasm ~]$ sqlplus / as sysdba SQL> shutdown immediate; # grid 用户 \$ lsnrctl stop \$ crsctl stop resource -all1.2.2 删除相关文件 # 删除oracl的ORACLE_BASE目录 $ $ORACLE_BASE # Oracle下查看 bash: /u01/app/oracle: Is a directory rm -rf /u01/app/oracle/* # root下删除 # 删除grid的ORACLE_BASE目录 $ $ORACLE_BASE # grid下查看 bash: /u01/app/grid: Is a directory rm -rf /u01/app/grid/* # root下删除 # 删除oraInventory目录 cat /etc/oraInst.loc # 查看oraInventory存放位置 inventory_loc=/u01/app/oracle/oraInventory inst_group=oinstall rm -rf /u01/app/oracle/oraInventory # 删除/usr/local/bin目录下的dbhome、oraenv、coraenv文件 rm -rf /usr/local/bin/oraenv rm -rf /usr/local/bin/coraenv rm -rf /usr/local/bin/dbhome # 删除etc下oracle相关的文件 rm -rf /etc/ora* # 删除Oracle的临时文件 rm -rf /var/tmp/.oracle rm -rf /tmp/*oracle* rm -rf /tmp/Oracle* rm -rf /tmp/.oracle rm -rf /tmp/CUV* rm -rf /tmp/OraInst*1.2.3 删除用户 # 删除oracle用户 userdel -r oracle userdel -r grid # 删除oracle用户组 groupdel oinstall groupdel oinstall groupdel dba groupdel oper groupdel asmadmin groupdel asmdba groupdel asmoper1.2.4 清除OCR、voting相关磁盘数据 dd if=/dev/zero of=/dev/xxxxx bs=1024 count=5122. Windows下卸载Oracle 停止oracle所有服务 开始菜单运行Oracle Universal Installer进行卸载 删除注册表(全文搜索oracle进行删除) 删除开始菜单的oracle目录 删除Oracle和grid的安装目录 删除C:\Program Files\Oracle目录 删除C:\Users\Administrator\Oracle目录 删除环境变量 重启检查
方式一：DBCA删除注意：1)、数据库必须处于open状态下，否则无法删除1.1)、open状态下，删除数据库和安装了grid的主机是一样的。2)、如果数据库处于非open状态，那么：2.1)、在装有grid的主机上，DBCA图形界面和静默方式不会删除任何文件(/etcoratab和参数文件)；2.2)、在没装grid的主机上，DBCA图形界面会删除参数文件(pfile和spfile)，但是不会删除数据文件；而静默方式依然会报错，不会删除和修改任何文件。1.1 DBCA图形界面删除(略)1.2 DBCA静默删除在OS级别使用dbca命令静默的方式删除，如 [oracle@orclasm ~]$ dbca -silent -deleteDatabase -sourceDB mynewdb # 通过警告日志查看，删除的过程是： 先将数据库关闭 启动到mount状态下 执行'alter system enable restricted session' # 让数据库处于受限模式 drop database 清理/etc/oratab,删除spfile和pfile文件方式二：SQL窗口 SQL> alter database close; SQL> alter system enable restricted session; SQL> drop database; --或者 SQL> startup force mount restricted； SQL> drop database；推荐第一种方式对于第二种方式，若是RAC环境，数据库需要设置参数cluster_database为false后才可以执行dropSQL> alter system set cluster_database=false sid='*' scope=spfile; SQL> drop database
1. 静默建库概述Silent模式采用命令行方式一次将所有信息提供给DBCA完成数据库的建立，在建立过程中不存在交互作用，所有的信息、错误和告警都写到日志文件中，只在结束时屏幕上打印出日志文件的位置，除此以外，没有其他的消息。使用dbca安装oracle数据库实例也有两种方式：2. 方式一：响应文件模板一种就是根据[font color="green"]响应文件模板[/font]进行安装，Oracle安装程序的安装文件夹下的response目录的dbca.rsp文件就是dbca的模板文件。当然我们也可以通过复制这个模板文件来修改其中的内容来建立定制的数据库，在这个模板文件中对于各个参数的说明非常详细，对这个文件的内容在此不做说明。 # 创建命令：根据模版文件创建数据库： # 安装的路径：$ORACLE_HOME/assistants/dbca/dbca.rsp # 或者解压的路径：/xxx/database/response/dbca.rsp # 复制一份响应文件到指定路径下修改： $ cp dbca.rsp /u01/app/dbca.rsp # 创建命令 [oracle@orclasm ~]$ dbca -silent -cloneTemplate -gdbName orcl -sid orcl -datafileDestination /u01/app/oradata -responseFile /u01/app/dbca.rsp # 一些参数的说明： -silent # 指定dbca的静默安装方式 -cloneTemplate # 指定自模版文件创建 -gdbName # Global Database Name -sid # 创建数据库的sid -datafileDestination # 数据文件存放的目标路径 -responseFIle # 模版文件的路径3. 方式二：建库模板另外一种就是根据oracle自身的[font color="green"]数据库模板[/font]来建立数据库实例 # 数据库模板文件位置：$ORACLE_HOME/assistants/dbca/templates/*.dbc [oracle@orclasm templates]$ ls $ORACLE_HOME/assistants/dbca/templates Data_Warehouse.dbc General_Purpose.dbc pdbseed.xml Seed_Database.ctl example01.dfb New_Database.dbt sampleschema.dfb Seed_Database.dfb example.dmp pdbseed.dfb sampleschema.xml # ORACLE为我们提供了几个dbc模板： Data Warehouse 数据仓库 Transaction Processing 事务处理 General Purpose 一般用途 # 这几个模板是比较易用的模板，oracle针对不同的数据库用途做了专门的优化 # 当然我们也可以创建我们自己的模板（也是使用dbca命令） # 创建命令：根据Oracle提供的数据库模版创建数据库： # 可以通过dbca -help查看创建命令 # 12c静默建库命令命令一: [oracle@orclasm ~]$ dbca -silent -createDatabase -templateName $ORACLE_HOME/assistants/dbca/template/General_Purpose.dbc -gdbName orcl -sid orcl -responseFile NO_VALUE -characterSet ZHS16GBK # 输入密码： Enter SYS user password: Enter SYSTEM user password: Copying database files # 12c静默建库命令命令二: [oracle@orclasm ~]$ dbca -silent -createDatabase -templateName $ORACLE_HOME/assistants/dbca/template/General_Purpose.dbc -gdbName orcl -sid orcl -responseFile NO_VALUE \ -sysPassword xxxxx -systemPassword xxxx \ -datafileDestination 'DATA/' -recoveryAreaDestination 'FRA' \ -redoLogFileSize 50 \ -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \ -characterSet AL32UTF8 -nationalcharacterSet AL16UTF16 \ -databaseType OLTP \ -emConfiguration NONE \ -nodeinfo NODE1，NODE2 # 11g静默建库命令: [oracle@orclasm ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -sysPassword xxxxx -systemPassword xxxxx -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata/ -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE # 10g静默建库命令: dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora10g -sid ora10g -sysPassword xxxxx -systemPassword xxxxx -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata/ -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -databaseType OLTP -emConfiguration NONE
1. OEM分类 OEM全称：Oracle Enterprise Manager # 12c之前 EMDC：Enterprise Manager Database Control EMGC：EMGC_Enterprise Manager Grid Control # 12c之后 EMDE：Enterprise Manager Database Express EMCC：Enterprise Manager Cloud Control2. 介绍2.1 维护管理单个数据库EMDC： Enterprise Manager Database Control使用emca命令创建，针对单实例的数据库。12c中取消了ECDC，而是被EMDE取代了EMDE： Enterprise Manager Database Express不需要额外安装，只需要配置一个端口即可使用，pdb和cdb都可以配置EMDE。从12c开始，用于维护管理单个数据库2.2 管理多台数据库服务器EMGC： EMGC_Enterprise Manager Grid Control可以监控多台数据库服务器。用于12c之前EMCC： Enterprise Manager Cloud Control可以监控多台数据库服务器，EMCC比EMGC功能更加强大，从Oracle 12c开始使用。3. OEM的图形界面3.1 EMDC3.2 EMGC3.3 EMDE3.4 EMCC
1. 前置条件1.1 修改oratab [oracle@orclasm ~]$ cat /etc/oratab orcl:/u01/app/oracle/product/12.1.0/dbhome_1:Y# 将N改成Y # oratab由root.sh脚本创建，在DBCA创建实例时也会更新这个文件 # 这里的参数(Y/N)是个开关作用，不会真正启动或关闭，具体开关由&ORACLE_HOME/bin/dbstart和dbshut实现,执行时会先检查oratab的参数，为Y时才会继续执行.1.2 修改启动和关闭脚本 [oracle@orclasm ~]$ cat $ORACLE_HOME/bin/dbstart ORACLE_HOME_LISTNER=$ORACLE_HOME# 将$1改成$ORACLE_HOME [oracle@orclasm ~]$ cat $ORACLE_HOME/bin/dbshut ORACLE_HOME_LISTNER=$ORACLE_HOME# 将$1改成$ORACLE_HOME2. 方式一：配置Oracle服务2.1 编辑启动脚本 [root@orclasm ~]\# cat /etc/rc.d/init.d/oracle #!/bin/bash # chkconfig: 345 99 10 # description: Startup Script for Oracle Databases # /etc/rc.d/init.d/oracle export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin ORA_OWNR="oracle" # if the executables do not exist -- display error if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ] then echo "Oracle startup: cannot start" exit 1 fi # depending on parameter -- startup, shutdown, restart # of the instance and listener or usage display case "$1" in start) # Oracle listener and instance startup echo -n "Starting Oracle: " su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbstart" su - $ORA_OWNR -c "$ORACLE_HOME/bin/lsnrctl start" touch /var/lock/Oracle su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl start dbconsole" #su - $ORA_OWNR -c "$ORACLE_HOME/bin/isqlplusctrl start" echo "OK" ;; stop) # Oracle listener and instance shutdown echo -n "Shutdown Oracle: " su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl stop dbconsole" #su - $ORA_OWNR -c "$ORACLE_HOME/bin/isqlplusctrl stop" su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbshut" su - $ORA_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop" rm -f /var/lock/Oracle echo "OK" ;; reload|restart) $0 stop $0 start ;; *) echo "Usage: `basename $0` start|stop|restart|reload" exit 1 esac exit 0 # 2.2 脚本授权 chown oracle /etc/init.d/oracle chmod 775 /etc/init.d/oracle chkconfig --add /etc/init.d/oracle2.3 创建服务 chkconfig --add oracle chkconfig oracle on# 或 chkconfig --level 2345 oracle on # 检查服务状态 chkconfig --list oracle Oracle0:off1:off2:on3:on4:on5:off6:off # 测试正常service oracle stop/start/restart # 重启OS查看是否正常启动3. 方式二：配置rc.local文件(推荐)3.1 编辑rc.local文件 # 增加一行 [root@orclasm ~]\# Vi /etc/rc.d/rc.local su - oracle -c 'dbstart $ORACLE_HOME' # 也可以写成 su - oracle -c $ORACLE_HOME/bin/dbstart # 或者 export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 su - oracle -c $ORACLE_HOME/bin/dbstart export ORACLE_UNQNAME=orcl su - oracle -c "$ORACLE_HOME/bin/emctl start dbconsole" # 说明: #1)、/etc/rc.local是/etc/rc.d/rc.local的软连接 #2)、由于dbstart脚本中已包含了启动listener监听服务,所以此处不需要添加lsnrctl start命令行: #su - oracle -lc "/u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start" #3)、由于dbstart($ORACLE_HOME/bin/dbstart)脚本中将ORACLE_HOME_LISTNER=$1,所以dbstart后要增加$ORACLE_HOME,即将$ORACLE_HOME作为参数传递给$1； #4)、如果将dbstart中的$1修改为$ORACLE_HOME,那么此行的$ORACLE_HOME就不再需要,即新增加的一行为 #su - oracle -c 'dbstart'即可 #5)、若环境中没有EM，可以不用配置UNQNAME和启动emctl这两行3.2 脚本授权 [root@orclasm ~]\# chmod +x /etc/rc.d/rc.local # 监测是否生效 [root@orclasm ~]\# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 [root@orclasm ~]\# su - oracle -c $ORACLE_HOME/bin/dbstart # 再重启OS验证4. 小结 如果多个实例，oratab的参数都是Y，那么都会开机自启 监听也会开机自启(dbstart脚本包含监听自启) 启动和关闭日志：$ORALCE_HOME下的startup.log和shutdown.log
1. 概述此文说明的是数据库在文件系统中的手动创建有时候因为环境的缘故不能使用图形界面或者不能使用DBCA的静默方式来创建一个新库，那么这个时候可以考虑使用CREATE DATABASE SQL命令行来创建数据库，该方式是一种手动建库方式，使用此种命令行手动创建数据库的优点是：可以用脚本来创建数据库。 另外OCM的考试中要求我们用CREATE DATABASE来创建数据库。当然在使用脚本创建数据库时，在建立数据字典视图和安装标准的PL/SQL程序包时，必须先建立一个可以操作的数据库。11GR2Creating a Database with the CREATE DATABASE Statement2. 创建过程2.1 确保环境变量正确 [oracle@orclasm ~]$ env | grep ORACLE ORACLE_SID=orcl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_12.2 创建密码文件 orapwd file=$ORACLE_HOME/dbs/orapworcl password=xxxx force=y 2.3 初始化参数文件 # 初始化参数文件有个模板例子 init.ora # 可以根据这个模板创建initmynewdb.ora，如orcl的一般是initorcl.ora [oracle@orclasm dbs]$ ls hc_orcl.dat init.ora lkORCL orapworcl # 创建参数文件 [oracle@orclasm dbs]$ cat initmynewdb.ora db_name=mynewdb memory_target=1G # 原模板参数 [oracle@orclasm dbs]$ cat init.ora db_name='ORCL' memory_target=1G processes = 150 audit_file_dest='<ORACLE_BASE>/admin/orcl/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='<ORACLE_BASE>' dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' # You may want to ensure that control files are created on separate physical devices control_files = (ora_control1, ora_control2) compatible ='11.2.0'2.4 创建数据库文件目录 [oracle@orclasm dbs]$ mkdir -p /u01/app/oracle/oradata/mynewdb # Oracle建议日志文件等放在不同目录,以作备份 [oracle@orclasm dbs]$ mkdir -p /u01/logs/my [oracle@orclasm dbs]$ mkdir -p /u02/logs/my2.5 启动数据库到nomount状态在该环境下，我们的控制文件尚未生成，实例至多也只能加载到NOMOUNT状态 [oracle@orclasm dbs]$ sqlplus / as sysdba SQL> startup nomount # 如果参数文件没有在默认路径下，可以在后面指定 # 如 startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initmynewdb.ora'2.6 创建数据库2.6.1 方式一 CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512, GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512, GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;2.6.2 方式二-- 可以在参数文件指定DB_CREATE_FILE_DEST后，用更简短的语句创建 [oracle@orclasm dbs]$ cat initmynewdb.ora db_name=mynewdb memory_target=1G DB_CREATE_FILE_DEST='/u01/app/oracle/oradata' [oracle@orclasm dbs]$ sqlplus / as sysdba SQL> startup nomount SQL> create database mynewdb charactor set al32utf8 extent management local; -- 也可以附加临时、undo、user等表空间 SQL> create database mynewdb charactor set al32utf8 extent management local DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE undotbs1 DEFAULT TABLESPACE users;2.7 创建数据字典视图-- 使用sysdba权限的用户 SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql SQL> @?/rdbms/admin/utlrp.sql # 使用system权限的用户 SQL> @?/sqlplus/admin/pupbld.sql -- 当然也可以编译这个目录下其他的视图3. 静默创建监听 # 创建完成后，此时并没有监听，可以UI或编辑监听文件或静默创建 [oracle@orclasm ~]$ netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp4. 数据库的删除 # 将数据库处于MOUNT RESTRICT状态 SQL> alter system enable restricted session; System altered. SQL> drop database;
1. 前提1.1 环境配置建立Oracle用户、安装必须的依赖、home路径、环境变量等设置.(略)1.2 创建oraInst.loc文件 # 新建oraInst.loc文件，没有此文件会报错：找不到这个文件 # 图形化安装时，oraInst.loc文件通常会自动生成在etc目录下 [root@orclasm ~]# vim /etc/oraInst.loc # 内容如下： inventory_loc=/u01/app/oraInventory inst_group=oinstall # 开放权限: [root@orclasm ~]# chown oracle:oinstall /etc/oraInst.loc [root@orclasm ~]# chmod 664 /etc/oraInst.loc2. 方式一：使用响应文件安装2.1 修改响应文件参数修改解压后的安装文件夹下response下面的db_install.rsp文件，直接产生orcl数据库实例将相关的参数设置为如下: # /xxx/database/response/db_install.rsp ORACLE_HOSTNAME=orclasm# 主机名 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory# SELECTED_LANGUAGES=en,zh_CN ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1# 安装目录 ORACLE_BASE=/u01/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.EEOptionsSelection=false oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=oper oracle.install.db.isRACOneInstall=false oracle.install.db.config.starterdb.type=GENERAL_PURPOSE SECURITY_UPDATES_VIA_MYORACLESUPPORT=false oracle.installer.autoupdates.option=SKIP_UPDATES2.2 执行安装 # 执行db_install.rsp [oracle@orclasm ~]$ cd /soft/database [oracle@orclasm database]$./runInstaller -silent -noconfig -responseFile /soft/database/response/db_install.rsp -ignoreSysPrereqs -ignorePrereq . .. Successfully Setup Software. # 安装完后执行root脚本 [oracle@orclasm ~]$ su -root [root@orclasm ~]# /u01/app/oracle/product/12.1.0/dbhome_1/root.sh3. 方式二：命令行安装 # 开始安装 [oracle@orclasm ~]$ # 命令行 起始... # /soft/database/runInstaller -force -noconfig -ignoreSysPrereqs -ignorePrereq -showProgress \ oracle.install.option=INSTALL_DB_ONLY \ DECLINE_SECURITY_UPDATES=true \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ SELECTED_LANGUAGES=en,zh_CN \ ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 \ ORACLE_BASE=/u01/app/oracle \ oracle.install.db.InstallEdition=EE \ oracle.install.db.EEOptionsSelection=false \ oracle.install.db.DBA_GROUP=dba \ oracle.install.db.OPER_GROUP=dba \ oracle.install.db.isRACOneInstall=false \ oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ oracle.installer.autoupdates.option=SKIP_UPDATES \ # 命令行 结束... # . .. Starting Oralce Universal Installer... # 安装日志 [oracle@orclasm ~]$ You can find the log of this install session at: /u01/app/oraInventory/logs/installAction2020-07-30_20-11-20.PM.log . .. ... Setup files succesful. . .. Execute Root Scripts in progress. As a root user, excute the follwing script(s): 1. /u01/app/oraInventory/orainstRoot.sh 2. /u01/app/oracle/product/12.1.0/dbhome_1/root.sh # 执行root脚本 [root@orclasm ~]\# /u01/app/oraInventory/orainstRoot.sh [root@orclasm ~]\# /u01/app/oracle/product/12.1.0/dbhome_1/root.sh4. 卸载重装 # 如果安装错误，可以删除目录后重新创建目录再执行命令创建。 rm -rf /u01 mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1 chown -R oracle:oinstall /u01 chmod -R 775 /u01
一、升级说明--记录时间:2021.03.03具体升级方法方法参考补丁中的[font color="green"]README.HTML[/font],里面介绍的很详细;安装的19c版本是19.3,需要升级到更高版本,当前最新补丁版本是19.10;升级补丁的顺序(19.10的包,包含GI和DB,可以自动安装GI和DB):GIDBOJVMOne off下载补丁: p32226239_190000_Linux-x86-64,Patch 32226239 - GI Release Update 19.10.0.0.210119二、Opatch补丁工具版本[grid@rac01 grid]$ cd $ORACLE_HOME/OPatch [grid@rac01 OPatch]$ ./opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded.README.HTML要求补丁工具版本是12.2.0.1.23,不符合要求,下载Opatch工具Patch 6880880,选择对应的版本上传下载的p6880880_190000_Linux-x86-64.zip工具到所有节点,解压覆盖原Opatch,此处上传的是/u01目录# Grid用户 [grid@rac01 OPatch]$ unzip /u01/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME [grid@rac02 OPatch]$ unzip /u01/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME [grid@rac01 OPatch]$ ./opatch version OPatch Version: 12.2.0.1.24 OPatch succeeded. [grid@rac02 OPatch]$ ./opatch version OPatch Version: 12.2.0.1.24 OPatch succeeded. # Oracle用户 [oracle@rac01 ~]$ unzip /u01/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME [oracle@rac02 ~]$ unzip /u01/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME三、校验Oracle Inventory[grid@rac02 OPatch]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME OPatch succeeded. [grid@rac01 OPatch]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME OPatch succeeded. [oracle@rac02 OPatch]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME OPatch succeeded. [oracle@rac01 OPatch]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME OPatch succeeded.四、检查Opatch冲突# Grid用户 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32218454 OPatch succeeded. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32222571 OPatch succeeded. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32218663 OPatch succeeded. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/29340594 OPatch succeeded. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32240590 OPatch succeeded. # Oracle用户 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32218454 OPatch succeeded. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32226239/32222571 OPatch succeeded.五、检查OPatch空间大小5.1 For Grid Infrastructure Home, as home user# 1.Create file /tmp/patch_list_gihome.txt with the following content: cat /tmp/patch_list_gihome.txt /tmp/32226239/32218454 /tmp/32226239/32222571 /tmp/32226239/32218663 /tmp/32226239/29340594 /tmp/32226239/32240590 # 2.Run the opatch command to check if enough free space is available in the Grid Infrastructure Home: $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt OPatch succeeded.5.2 For Database home, as home user:# 1.Create file /tmp/patch_list_dbhome.txt with the following content: cat /tmp/patch_list_dbhome.txt /tmp/32226239/32218454 /tmp/32226239/32222571 # 2.Run opatch command to check if enough free space is available in the Database Home: $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt OPatch succeeded.六、解压# 进入安装包所在目录解压,给与grid对补丁的权限 # To apply the patch, it must be accessible from all nodes in the Oracle cluster. Download the patch and unzip it to a shared location, this is called the <UNZIPPED_PATCH_LOCATION>. This directory must be empty and not be /tmp. Additionally, the directory should have read permission for the ORA_INSTALL group. $ cd <UNZIPPED_PATCH_LOCATION> Check that the directory is empty. $ ls Unzip the patch as grid home owner except for installations that do not have any grid homes. For installations where this patch will be applied to the Database home only, the patch needs to be unzipped as the database home owner. $ unzip p32226239_190000_Linux-x86-64.zip # 此处直接解压到 /tmp目录 # 如果没有权限可以对文件授权 # chown grid.asmadmin p32226239_190000_Linux-x86-64.zip [root@rac01 u01]# su - grid Last login: Wed Mar 3 18:07:29 CST 2021 on pts/1 [grid@rac01 ~]$ cd /tmp [grid@rac01 tmp]$ unzip /u01/p32226239_190000_Linux-x86-64.zip [root@rac02 u01]# su - grid Last login: Wed Mar 3 18:07:29 CST 2021 on pts/1 [grid@rac02 ~]$ cd /tmp [grid@rac02 tmp]$ unzip /u01/p32226239_190000_Linux-x86-64.zip 七、节点1安装补丁The most common configurations are listed as follows:Case 1: Oracle RAC, where the GI Home and the Database Homes are not shared and ACFS file system is not configuredCase 2: Oracle RAC, where the GI Home is not shared, Database Home is shared, ACFS may be usedCase 3: Non-Oracle RAC Database homesCase 1: Oracle RAC, where the GI Home and the Database Homes are not shared and ACFS file system is not configured.[font color="green"]As root user[/font],execute the following command on each node of the cluster:# 节点执行升级 /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/32226239 # 安装日志: [root@rac01 ~]# /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/32226239 OPatchauto session is initiated at Wed Mar 3 22:12:46 2021 System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2021-03-03_10-13-15PM.log. Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2021-03-03_10-14-45PM.log The id for this session is IHI4 Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/dbhome_1 Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Patch applicability verified successfully on home /u01/app/19.3.0/grid Executing patch validation checks on home /u01/app/19.3.0/grid Patch validation checks successfully completed on home /u01/app/19.3.0/grid Executing patch validation checks on home /u01/app/oracle/product/19.3.0/dbhome_1 Patch validation checks successfully completed on home /u01/app/oracle/product/19.3.0/dbhome_1 Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/dbhome_1 SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/dbhome_1 Successfully prepared home /u01/app/oracle/product/19.3.0/dbhome_1 to bring down database service Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.3.0/grid Prepatch operation log file location: /u01/app/grid/crsdata/rac01/crsconfig/crs_prepatch_apply_inplace_rac01_2021-03-03_10-18-16PM.log CRS service brought down successfully on home /u01/app/19.3.0/grid Performing prepatch operation on home /u01/app/oracle/product/19.3.0/dbhome_1 Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Start applying binary patch on home /u01/app/oracle/product/19.3.0/dbhome_1 Binary patch applied successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Performing postpatch operation on home /u01/app/oracle/product/19.3.0/dbhome_1 Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Start applying binary patch on home /u01/app/19.3.0/grid Binary patch applied successfully on home /u01/app/19.3.0/grid Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.3.0/grid Postpatch operation log file location: /u01/app/grid/crsdata/rac01/crsconfig/crs_postpatch_apply_inplace_rac01_2021-03-03_11-20-50PM.log CRS service started successfully on home /u01/app/19.3.0/grid Preparing home /u01/app/oracle/product/19.3.0/dbhome_1 after database service restarted No step execution required......... Trying to apply SQL patch on home /u01/app/oracle/product/19.3.0/dbhome_1 SQL patch applied successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 OPatchAuto successful. --------------------------------Summary-------------------------------- Patching is completed successfully. Please find the summary as follows: Host:rac01 RAC Home:/u01/app/oracle/product/19.3.0/dbhome_1 Version:19.0.0.0.0 Summary: ==Following patches were SKIPPED: Patch: /tmp/32226239/32218663 Reason: This patch is not applicable to this specified target type - "rac_database" Patch: /tmp/32226239/29340594 Reason: This patch is not applicable to this specified target type - "rac_database" Patch: /tmp/32226239/32240590 Reason: This patch is not applicable to this specified target type - "rac_database" ==Following patches were SUCCESSFULLY applied: Patch: /tmp/32226239/32218454 Log: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-33-59PM_1.log Patch: /tmp/32226239/32222571 Log: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-33-59PM_1.log Host:rac01 CRS Home:/u01/app/19.3.0/grid Version:19.0.0.0.0 Summary: ==Following patches were SUCCESSFULLY applied: Patch: /tmp/32226239/29340594 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-48-02PM_1.log Patch: /tmp/32226239/32218454 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-48-02PM_1.log Patch: /tmp/32226239/32218663 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-48-02PM_1.log Patch: /tmp/32226239/32222571 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-48-02PM_1.log Patch: /tmp/32226239/32240590 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-04_17-48-02PM_1.log OPatchauto session completed at Thu Mar 4 18:22:00 2021 Time taken to complete the session 55 minutes, 48 seconds八、传输文件#错误一命令: [grid@rac01 ~]$ scp /u01/app/oraInventory/ContentsXML/oui-patch.xml grid@rac02:/u01/app/oraInventory/ContentsXML/ [root@rac02 ~]# chmod 660 /u01/app/oraInventory/ContentsXML/oui-patch.xml #错误二命令: [oracle@rac01 ~]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/inventory/oneoffs/ [oracle@rac01 oneoffs]$ scp -r 32218454 oracle@rac02:/u01/app/oracle/product/19.3.0/dbhome_1/inventory/oneoffs #错误三命令: [root@rac02 ~]# chmod 755 /u01/app/19.3.0/grid/crs/install/cmdllroot.sh [root@rac02 ~]# chown grid:oinstall /u01/app/19.3.0/grid/crs/install/cmdllroot.sh九、节点2遇到的错误通过提前设置以上三条命令后才安装成功;1.硬件条件不够导致安装制终止:2.虚拟机中,因为CPU和内存等限制,有时打补丁中途会出现错误,因为CPU超出或不足物理CPU核数3.另外初始8G内存增加到16G,也会导致安装失败,查询得知应对应增加shm设定的内存,但最终在8G2核2线程下安装,过程太久...[root@rac02 ~]# /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/32226239 OPatchauto session is initiated at Fri Mar 5 22:31:23 2021 System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2021-03-05_10-31-38PM.log. Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2021-03-05_10-32-38PM.log The id for this session is BYLB Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/dbhome_1 Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid Patch applicability verified successfully on home /u01/app/19.3.0/grid Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Executing patch validation checks on home /u01/app/19.3.0/grid Patch validation checks successfully completed on home /u01/app/19.3.0/grid Executing patch validation checks on home /u01/app/oracle/product/19.3.0/dbhome_1 Patch validation checks successfully completed on home /u01/app/oracle/product/19.3.0/dbhome_1 Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/dbhome_1 SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/dbhome_1 Successfully prepared home /u01/app/oracle/product/19.3.0/dbhome_1 to bring down database service Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.3.0/grid Prepatch operation log file location: /u01/app/grid/crsdata/rac02/crsconfig/crs_prepatch_apply_inplace_rac02_2021-03-05_10-35-05PM.log CRS service brought down successfully on home /u01/app/19.3.0/grid Performing prepatch operation on home /u01/app/oracle/product/19.3.0/dbhome_1 Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Start applying binary patch on home /u01/app/oracle/product/19.3.0/dbhome_1 Binary patch applied successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Performing postpatch operation on home /u01/app/oracle/product/19.3.0/dbhome_1 Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 Start applying binary patch on home /u01/app/19.3.0/grid Binary patch applied successfully on home /u01/app/19.3.0/grid Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.3.0/grid Postpatch operation log file location: /u01/app/grid/crsdata/rac02/crsconfig/crs_postpatch_apply_inplace_rac02_2021-03-05_11-32-07PM.log CRS service started successfully on home /u01/app/19.3.0/grid Preparing home /u01/app/oracle/product/19.3.0/dbhome_1 after database service restarted No step execution required......... Trying to apply SQL patch on home /u01/app/oracle/product/19.3.0/dbhome_1 SQL patch applied successfully on home /u01/app/oracle/product/19.3.0/dbhome_1 OPatchAuto successful. --------------------------------Summary-------------------------------- Patching is completed successfully. Please find the summary as follows: Host:rac02 RAC Home:/u01/app/oracle/product/19.3.0/dbhome_1 Version:19.0.0.0.0 Summary: ==Following patches were SKIPPED: Patch: /tmp/32226239/32218663 Reason: This patch is not applicable to this specified target type - "rac_database" Patch: /tmp/32226239/29340594 Reason: This patch is not applicable to this specified target type - "rac_database" Patch: /tmp/32226239/32240590 Reason: This patch is not applicable to this specified target type - "rac_database" ==Following patches were SUCCESSFULLY applied: Patch: /tmp/32226239/32218454 Log: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_22-38-10PM_1.log Patch: /tmp/32226239/32222571 Log: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_22-38-10PM_1.log Host:rac02 CRS Home:/u01/app/19.3.0/grid Version:19.0.0.0.0 Summary: ==Following patches were SUCCESSFULLY applied: Patch: /tmp/32226239/29340594 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_23-00-15PM_1.log Patch: /tmp/32226239/32218454 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_23-00-15PM_1.log Patch: /tmp/32226239/32218663 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_23-00-15PM_1.log Patch: /tmp/32226239/32222571 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_23-00-15PM_1.log Patch: /tmp/32226239/32240590 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-05_23-00-15PM_1.log OPatchauto session completed at Sat Mar 6 00:35:41 2021 Time taken to complete the session 124 minutes, 22 seconds9.1错误一权限不足,授权即可………/u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)'………[root@rac02 ~]# chmod 660 /u01/app/oraInventory/ContentsXML/oui-patch.xml /u01/app/19.3.0/grid/OPatch/opatchauto resume9.2错误二节点2的补丁没有相应的文件夹和文件32218454;MOS建议从节点1安装成功复制过去即可.[oracle@rac01 32218454]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/inventory/oneoffs/ [oracle@rac01 oneoffs]$ scp -r 32218454 oracle@rac02:/u01/app/oracle/product/19.3.0/dbhome_1/inventory/oneoffs inventory.xml 100% 425KB 3.2MB/s 00:00 actions.xml 100% 2748KB 6.0MB/s 00:00 /u01/app/19.3.0/grid/OPatch/opatchauto resume9.3 错误三节点2的权限提示不对,修改即可---------------------------Patching Failed--------------------------------- ….. PRVH-0116 : Path "/u01/app/19.3.0/grid/crs/install/cmdllroot.sh" with permissions "rw-r--r--" does not have execute permissions for the owner, file's group, and others on node "rac02". PRVG-2031 : Owner of file "/u01/app/19.3.0/grid/crs/install/cmdllroot.sh" did not match the expected value on node "rac02". [Expected = "grid(1001)" ; Found = "root(0)"] PRVG-2032 : Group of file "/u01/app/19.3.0/grid/crs/install/cmdllroot.sh" did not match the expected value on node "rac02". [Expected = "oinstall(54321)" ; Found = "root(0)"] CRS-4000: Command Start failed, or completed with errors. 2021/03/05 02:13:02 CLSRSC-117: Failed to start Oracle Clusterware stack from the Grid Infrastructure home /u01/app/19.3.0/grid After fixing the cause of failure Run opatchauto resume ] OPATCHAUTO-68061: The orchestration engine failed. OPATCHAUTO-68061: The orchestration engine failed with return code 1 OPATCHAUTO-68061: Check the log for more details. OPatchAuto failed. OPatchauto session completed at Fri Mar 5 02:13:04 2021 Time taken to complete the session 36 minutes, 25 seconds[root@rac02 ~]# chmod 755 /u01/app/19.3.0/grid/crs/install/cmdllroot.sh [root@rac02 ~]# chown grid:oinstall /u01/app/19.3.0/grid/crs/install/cmdllroot.sh /u01/app/19.3.0/grid/OPatch/opatchauto resume十、验证# rac01 [grid@rac01 ~]$ $ORACLE_HOME/bin/kfod op=patches --------------- List of Patches =============== 29340594 32218454 32218663 32222571 32240590 [grid@rac01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.24 Copyright (c) 2021, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/19.3.0/grid Central Inventory : /u01/app/oraInventory from : /u01/app/19.3.0/grid/oraInst.loc OPatch version : 12.2.0.1.24 OUI version : 12.2.0.7.0 Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2021-03-06_01-28-05AM_1.log Lsinventory Output file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2021-03-06_01-28-05AM.txt -------------------------------------------------------------------------------- …….. 31796277, 31796882, 31798742, 31800757, 31807516, 31815099, 31816158 31816631, 31820632, 31820859, 31823051, 31827605, 31827912, 31829617 31829639, 31833172, 31833948, 31835854, 31836454, 31839779, 31842545 31843462, 31847489, 31849859, 31851383, 31852574, 31854692, 31855526 31860193, 31862359, 31863118, 31866141, 31867037, 31869601, 31871692 …….. Patch 29340594 : applied on Fri Mar 05 23:00:29 CST 2021 Unique Patch ID: 24005319 Patch description: "DBWLM RELEASE UPDATE 19.0.0.0.0 (29340594)" Created on 8 Jan 2021, 01:44:40 hrs PST8PDT Bugs fixed: 31862202, 31554751, 31547458 [oracle@rac01 ~]$ sqlplus -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.10.0.0.0 [grid@rac01 OPatch]$ sqlplus -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.10.0.0.0# rac02 [grid@rac02 ~]$ $ORACLE_HOME/bin/kfod op=patches --------------- List of Patches =============== 29340594 32218454 32218663 32222571 32240590 [grid@rac02 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.24 Copyright (c) 2021, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/19.3.0/grid Central Inventory : /u01/app/oraInventory from : /u01/app/19.3.0/grid/oraInst.loc OPatch version : 12.2.0.1.24 OUI version : 12.2.0.7.0 Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2021-03-06_01-28-05AM_1.log Lsinventory Output file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2021-03-06_01-28-05AM.txt -------------------------------------------------------------------------------- …….. 31796277, 31796882, 31798742, 31800757, 31807516, 31815099, 31816158 31816631, 31820632, 31820859, 31823051, 31827605, 31827912, 31829617 31829639, 31833172, 31833948, 31835854, 31836454, 31839779, 31842545 31843462, 31847489, 31849859, 31851383, 31852574, 31854692, 31855526 31860193, 31862359, 31863118, 31866141, 31867037, 31869601, 31871692 …….. Patch 29340594 : applied on Thu Mar 04 17:48:17 CST 2021 Unique Patch ID: 24005319 Patch description: "DBWLM RELEASE UPDATE 19.0.0.0.0 (29340594)" Created on 8 Jan 2021, 01:44:40 hrs PST8PDT Bugs fixed: 31862202, 31554751, 31547458 [oracle@rac02 ~]$ sqlplus -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.10.0.0.0 [grid@rac02 ~]$ sqlplus -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.10.0.0.0十一、升级OJVM11.1 停服[oracle@rac01 ~]$ srvctl stop instance -d orcl -i orcl1 [oracle@rac02 ~]$ srvctl stop instance -d orcl -i orcl211.2 解压[root@rac01 tmp]# su - oracle [oracle@rac01 tmp]$ unzip -d /tmp/ /u01/p32067171_190000_Linux-x86-64.zip [oracle@rac02 tmp]$ unzip -d /tmp/ /u01/p32067171_190000_Linux-x86-64.zip11.3 校验[oracle@rac01 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /tmp/32067171/ [oracle@rac02 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /tmp/32067171/11.4 升级一个节点一个节点的升级[oracle@rac01 ~]$ $ORACLE_HOME/OPatch/opatch apply /tmp/32067171/ [oracle@rac01 ~]$ $ORACLE_HOME/OPatch/opatch apply /tmp/32067171/11.5 Postinstallation# 1.Starting the Database for Post Installation Steps in an Oracle RAC Environment # If following the Oracle RAC Rolling approach as described in Note 2217053.1, STARTUP UPGRADE is not required. # On only one node, execute the following steps to start an Oracle RAC database in startup upgrade mode: sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> alter system set cluster_database=false scope=spfile; SQL> exit $ORACLE_HOME/bin/srvctl stop database -d orcl sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP UPGRADE #2.Restarting the Database after Running the Post Installation Steps in an Oracle RAC Environment # To start the database back in normal mode sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> alter system set cluster_database=true scope=spfile; SQL> SHUTDOWN $ORACLE_HOME/bin/srvctl start database -d orcl #3.After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sql to get them back into a VALID state. # 如果有无效记录,运行脚本即可 SQL> SELECT owner, object_name FROM dba_objects WHERE status = 'INVALID'; no rows selected cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> @utlrp.sql SQL> select patch_id,action,status from dba_registry_sqlpatch; [oracle@rac01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.24 Copyright (c) 2021, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.24 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-03-07_23-04-57PM_1.log Lsinventory Output file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2021-03-07_23-04-57PM.txt --------------------------------------------------------------------------------
Part 1:RMAN备份计划 : Windows (单机)一、备份条件1.1 归档日志--目标库设置在归档日志下: SQL> archive log list SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list SQL> alter system switch logfile;1.2 日期格式Batch批处理根据此格式为备份日志文件命名.设置windows日期格式为:YYYY-MM-DD时间格式为:HH:MM:SS24小时制.二、备份路径备份数据库 D:\RMAN_Backup\Data 备份控制文件 D:\RMAN_Backup\Controlfile 备份归档日志 D:\RMAN_Backup\Archivelog 备份日志 D:\RMAN_Backup\Log 备份脚本 D:\RMAN_Backup\RMAN_Backup_Scripts三、备份计划3.1 备份时间Windows添加任务计划, 执行备份脚本(02:00:00)3.2 一周备份周日：0级增量备份 周一：1级增量备份 周二：1级增量备份 周三：1级增量备份 周四：0级增量备份 周五：1级增量备份 周六：1级增量备份3.3 备份策略a、保留策略-- 保留策略默认7改为14天, 可以恢复到14天内任何时间点 configure retention policy to recovery window of 14 days;b、控制文件自动备份c、备份优化d、控制文件保留备份集信息的时间-- 控制文件keep time值应为19(retention+ level 0 interval+1=14+4+1=19) -- 即极限情况下,keep time=保留策略+全备时间间隔+1天 alter system set control_file_record_keep_time=19 scope=both;四、备份脚本4.1 Level 0 批处理设置系统时间格式YYYY-MM-DD, HH:SS设置ORACLE_SID,或设置在变量环境中删除15天以上的备份日志[font color="green"]Win_RMAN_Backup_Level_0.bat[/font] 内容@echo off echo ================================================ echo Name :RMAN_Backup_Level_0 Batchfile echo Platform:Windows echo Version :Oracle 12cR1 echo Date :YYYY-MM-DD HH:MM:SS echo ================================================ echo ===!Excute RMAN Level 0 BACKUP!================= echo ===!!Do Not Kill This Windows!!================= echo ===!Running...... !================= echo ================================================ set ORACLE_SID=mcrspos if not exist "D:\RMAN_Backup\Data" md "D:\RMAN_Backup\Data" if not exist "D:\RMAN_Backup\Controlfile" md "D:\RMAN_Backup\Controlfile" if not exist "D:\RMAN_Backup\Archivelog" md "D:\RMAN_Backup\Archivelog" if not exist "D:\RMAN_Backup\Log" md "D:\RMAN_Backup\Log" forfiles /p D:\RMAN_Backup\Log /m *.*log /d -15 /c "cmd /c del @path" IF "%Time:~0,1%"==" " Set "Time=0%Time:~1%" set date=%Date:~0,4%%Date:~5,2%%Date:~8,2%_%Time:~0,2%%Time:~3,2%%Time:~6,2% 2>nul rman target / cmdfile RMAN_Backup_Level_0.rcv log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_0__%date%.log'4.2 Level 0 备份脚本[font color="green"]Win_RMAN_Backup_Level_0.rcv[/font] 内容sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter system set control_file_record_keep_time=19 scope=both'; run{ configure backup optimization on; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to 'D:\RMAN_Backup\Controlfile\%F'; configure retention policy to recovery window of 14 days; backup as compressed backupset incremental level 0 database format 'D:\RMAN_Backup\Data\RMAN_Backup_Level_0_%d_%T_%s_%p.bak' tag='Level_0' plus archivelog format 'D:\RMAN_Backup\Archivelog\Arch_Level_0_%d_%T_%s_%p.log' delete all input skip inaccessible; } list backup; report obsolete; delete noprompt obsolete; crosscheck backup; delete noprompt expired backup; list backup summary; exit;4.3 Level 1 批处理[font color="green"]Win_RMAN_Backup_Level_1.bat[/font] 内容@echo off echo ================================================ echo Name :RMAN_Backup_Level_1 Batchfile echo Platform:Windows echo Version :Oracle 12cR1 echo Date :YYYY-MM-DD HH:MM:SS echo ================================================ echo ===!Excute RMAN Level 1 BACKUP!================= echo ===!!Do Not Kill This Windows!!================= echo ===!Running...... !================= echo ================================================ set ORACLE_SID=mcrspos if not exist "D:\RMAN_Backup\Data" md "D:\RMAN_Backup\Data" if not exist "D:\RMAN_Backup\Controlfile" md "D:\RMAN_Backup\Controlfile" if not exist "D:\RMAN_Backup\Archivelog" md "D:\RMAN_Backup\Archivelog" if not exist "D:\RMAN_Backup\Log" md "D:\RMAN_Backup\Log" forfiles /p D:\RMAN_Backup\Log /m *.*log /d -15 /c "cmd /c del @path" IF "%Time:~0,1%"==" " Set "Time=0%Time:~1%" set date=%Date:~0,4%%Date:~5,2%%Date:~8,2%_%Time:~0,2%%Time:~3,2%%Time:~6,2% 2>nul rman target / cmdfile RMAN_Backup_Level_1.rcv log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_1__%date%.log'4.4 Level 1 备份脚本[font color="green"]Win_RMAN_Backup_Level_1.rcv[/font] 内容sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter system set control_file_record_keep_time=19 scope=both'; run{ configure backup optimization on; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to 'D:\RMAN_Backup\Controlfile\%F'; configure retention policy to recovery window of 14 days; backup as compressed backupset incremental level 1 database format 'D:\RMAN_Backup\Data\RMAN_Backup_Level_1_%d_%T_%s_%p.bak' tag='Level_1' plus archivelog format 'D:\RMAN_Backup\Archivelog\Arch_Level_1_%d_%T_%s_%p.log' delete all input skip inaccessible; } list backup; report obsolete; delete noprompt obsolete; crosscheck backup; delete noprompt expired backup; list backup summary; exit;五、Windows任务计划windows添加每日02:00备份计划任务Part 2:RMAN备份计划: Windows (Catalog)一、环境1.1 连接目标库和辅助库target database :windows -oracle- mcrspos catalog database :windows -oracle- orcl12c1.2 确保tnsping# 确保目录库辅助库都能tnsping两个SID C:\Users\Administrator>tnsping mcrspos C:\Users\Administrator>tnsping orcl12c二、目录库配置2.1 创建目录库所用表空间C:\Users\Administrator>sqlplus sys/Password#1@orcl12c as sysdba SQL> create tablespace recovery_catalog datafile 'D:\app\orcl\oradata\orcl12c\recovery_catalog.dbf'size 100m autoextend on; Tablespace created.2.2 创建RMAN用户并授权SQL> create user rman identified by rman default tablespace recovery_catalog; SQL> grant resource,unlimited tablespace,recovery_catalog_owner to rman; Grant succeeded.2.3 连接目标库和目录库C:\Users\Administrator> rman target / catalog rman/rman@orcl12c Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 16 23:11:45 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: MCRSPOS (DBID=1576583420) connected to recovery catalog database2.4 创建恢复目录RMAN> create catalog; recovery catalog created2.5 注册目标库RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog三、备份脚本[font color="green"]Win_RMAN_Backup_Level_0.bat[/font]rman target / cmdfile RMAN_Backup_Level_0.rvc log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_0__%date%.log' 只需修改为增加target连接catalog rman target / catalog rman/rman@orcl12c cmdfile RMAN_Backup_Level_0.rvc log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_0__%date%.log'[font color="green"]Win_RMAN_Backup_Level_1.bat[/font]rman target / cmdfile RMAN_Backup_Level_1.rvc log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_1__%date%.log' 只需修改为增加target连接catalog rman target / catalog rman/rman@orcl12c cmdfile RMAN_Backup_Level_1.rvc log 'D:\RMAN_Backup\Log\RMAN_Backup_Level_1__%date%.log'四、备份测试Target上list backupRMAN>list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 611 1.11M DISK 00:00:00 20-DEC-20 BP Key: 611 Status: AVAILABLE Compressed: YES Tag: TAG20201220T010459 Piece Name: D:\RMAN_BACKUP\ARCHIVELOG\ARCH_LEVEL_0_MCRSPOS_20201220_726_1.LOG List of Archived Logs in backup set 611 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 652 18419149 20-DEC-20 18421555 20-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 612 Incr 0 2.45G DISK 00:07:06 20-DEC-20 BP Key: 612 Status: AVAILABLE Compressed: YES Tag: LEVEL_0 Piece Name: D:\RMAN_BACKUP\DATA\RMAN_BACKUP_LEVEL_0_MCRSPOS_20201220_727_1.BAK List of Datafiles in backup set 612 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- …… …… BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 613 1.11M DISK 00:00:00 20-DEC-20 BP Key: 613 Status: AVAILABLE Compressed: YES Tag: TAG20201220T011216 Piece Name: D:\RMAN_BACKUP\ARCHIVELOG\ARCH_LEVEL_0_MCRSPOS_20201220_728_1.LOG List of Archived Logs in backup set 613 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 653 18421555 20-DEC-20 18423566 20-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 614 Full 14.73M DISK 00:00:00 20-DEC-20 BP Key: 614 Status: AVAILABLE Compressed: NO Tag: TAG20201220T011217 Piece Name: D:\RMAN_BACKUP\CONTROLFILE\C-3772471526-20201220-01 SPFILE Included: Modification time: 20-DEC-20 SPFILE db_unique_name: MCRSPOS Control File Included: Ckp SCN: 18423577 Ckp time: 20-DEC-20 Catalog上list backupRMAN>list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 3389 1.11M DISK 00:00:00 20-DEC-20 BP Key: 3390 Status: AVAILABLE Compressed: YES Tag: TAG20201220T010459 Piece Name: D:\RMAN_BACKUP\ARCHIVELOG\ARCH_LEVEL_0_MCRSPOS_20201220_726_1.LOG List of Archived Logs in backup set 3389 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 652 18419149 20-DEC-20 18421555 20-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3438 Incr 0 2.45G DISK 00:07:06 20-DEC-20 BP Key: 3442 Status: AVAILABLE Compressed: YES Tag: LEVEL_0 Piece Name: D:\RMAN_BACKUP\DATA\RMAN_BACKUP_LEVEL_0_MCRSPOS_20201220_727_1.BAK List of Datafiles in backup set 3438 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- …… …… BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 3439 1.11M DISK 00:00:00 20-DEC-20 BP Key: 3443 Status: AVAILABLE Compressed: YES Tag: TAG20201220T011216 Piece Name: D:\RMAN_BACKUP\ARCHIVELOG\ARCH_LEVEL_0_MCRSPOS_20201220_728_1.LOG List of Archived Logs in backup set 3439 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 653 18421555 20-DEC-20 18423566 20-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3440 Full 14.73M DISK 00:00:00 20-DEC-20 BP Key: 3444 Status: AVAILABLE Compressed: NO Tag: TAG20201220T011217 Piece Name: D:\RMAN_BACKUP\CONTROLFILE\C-3772471526-20201220-01 SPFILE Included: Modification time: 20-DEC-20 SPFILE db_unique_name: MCRSPOS Control File Included: Ckp SCN: 18423577 Ckp time: 20-DEC-20 Part 3:RMAN备份计划: Linux (单机)一、变量环境[oracle@klaus ~]$ more /home/oracle/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH export EDITOR=vi export ORACLE_SID=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin export PATH=$ORACLE_HOME/bin:$PATH export NLS_LANG=AMERICAN export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'二、日志归档三、目录及授权# 创建目录 # /data 备份数据库文件 # /log 备份日志 # /archivelog 备份归档日志 # /controlfile 备份控制文件 # /rman_scripts 备份脚本 [oracle@klaus ~]$ cd /u01/app/oracle [oracle@klaus oracle]$ mkdir -p rman_backup [oracle@klaus oracle]$ mkdir -p rman_backup/data [oracle@klaus oracle]$ mkdir -p rman_backup/log [oracle@klaus oracle]$ mkdir -p rman_backup/archivelog [oracle@klaus oracle]$ mkdir -p rman_backup/controlfile [oracle@klaus oracle]$ mkdir -p rman_backup/rman_scripts #目录授权 [oracle@klaus oracle]$ chmod 771 -R /u01/app/oracle/rman_backup [oracle@klaus oracle]$ chown -R oracle:oinstall /u01/app/oracle/rman_backup四、备份脚本注意不能将windows编辑的shell脚本直接放入linux中;因为在dos/window 下按一次回车键实际上输入的是” 回车(CR)” 和“ 换行(LF)”, 而Linux/unix 下按一次回车键只输入“ 换行(LF)”, 所以修改的sh 文件在每行都会多了一个CR, 在Linux下运行时就会报错找不到命令Lunix中直接vi创建shell脚本Windows中通过notpad++等工具转换格式**4.1 Level 0 shell 脚本[oracle@klaus rman_scripts]$ vi /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_0.sh export ORACLE_SID=orcl source /home/oracle/.bash_profile find /u01/app/oracle/rman_backup/log/ -mtime +0 -name "*.log*" -exec rm -rf {} \; rman target / log=/u01/app/oracle/rman_backup/log/level_0_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF run{ sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter system set control_file_record_keep_time=19 scope=both'; configure backup optimization on; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to '/u01/app/oracle/rman_backup/controlfile/%F'; configure retention policy to recovery window of 14 days; backup as compressed backupset incremental level 0 database format '/u01/app/oracle/rman_backup/data/rman_backup_level_0_%d_%T_%s_%p.bak' tag='Level_0' plus archivelog format '/u01/app/oracle/rman_backup/archivelog/arch_level_0_%d_%T_%s_%p.log' delete all input skip inaccessible; } list backup; report obsolete; delete noprompt obsolete; crosscheck backup; delete noprompt expired backup; list backup summary; exit; EOF4.2 Level 1 shell 脚本[oracle@klaus rman_scripts]$ vi /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_1.sh export ORACLE_SID=orcl source /home/oracle/.bash_profile find /u01/app/oracle/rman_backup/log/ -mtime +0 -name "*.log*" -exec rm -rf {} \; rman target / log=/u01/app/oracle/rman_backup/log/level_1_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF run{ sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter system set control_file_record_keep_time=19 scope=both'; configure backup optimization on; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to '/u01/app/oracle/rman_backup/controlfile/%F'; configure retention policy to recovery window of 14 days; backup as compressed backupset incremental level 1 database format '/u01/app/oracle/rman_backup/data/rman_backup_level_1_%d_%T_%s_%p.bak' tag='Level_0' plus archivelog format '/u01/app/oracle/rman_backup/archivelog/arch_level_1_%d_%T_%s_%p.log' delete all input skip inaccessible; } list backup; report obsolete; delete noprompt obsolete; crosscheck backup; delete noprompt expired backup; list backup summary; exit; EOF五、定时任务计划5.1 一周备份周日： 0级增量备份 周一： 1级增量备份 周二： 1级增量备份 周三： 1级增量备份 周四： 0级增量备份 周五： 1级增量备份 周六： 1级增量备份5.2 任务计划指令[oracle@klaus ~]$ cat /etc/crontab # m h dom mon dow command # 分钟 小时 某天 某月 星期几 命令5.3 任务计划服务[oracle@klaus ~]$ service crond status Redirecting to /bin/systemctl status crond.service . crond.service - Command Scheduler Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2020-12-20 01:49:23 CST; 21h ago Main PID: 1147 (crond) Tasks: 1 CGroup: /system.slice/crond.service └─1147 /usr/sbin/crond -n5.4 添加任务计划[oracle@klaus ~]$ crontab -e 0 2 * * 0,4 /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_0.sh 0 2 * * 1,2,3,5,6 /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_1.sh5.4 重启服务[oracle@klaus ~]$ service crond restart5.5 查看计划[oracle@klaus ~]$ crontab -l 0 2 * * 0,4 /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_0.sh 0 2 * * 1,2,3,5,6 /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_1.shPart 4:RMAN备份计划: Linux (Catalog)相同方法设置连接catalog database一、备份脚本[oracle@klaus rman_scripts]$ vi /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_0.sh rman target / log=/u01/app/oracle/rman_backup/log/level_0_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF # 只需修改为增加target连接catalog rman target / catalog rman/rman@orcl12c log=/u01/app/oracle/rman_backup/log/level_0_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF [oracle@klaus rman_scripts]$ vi /u01/app/oracle/rman_backup/rman_scripts/linux_rman_backup_level_1.sh rman target / log=/u01/app/oracle/rman_backup/log/level_1_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF # 只需修改为增加target连接catalog rman target / catalog rman/rman@orcl12c log=/u01/app/oracle/rman_backup/log/level_1_$(date +%Y-%m-%d-%H:%M:%S).log <<EOF二、测试Target上list backupRMAN>list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 208 25.00K DISK 00:00:00 2020-12-21 01:14:21 BP Key: 208 Status: AVAILABLE Compressed: YES Tag: TAG20201221T011421 Piece Name: /u01/app/oracle/rman_backup/archivelog/arch_level_0_ORCL_20201221_211_1.log List of Archived Logs in backup set 208 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 105 15818195 2020-12-21 01:07:18 15818569 2020-12-21 01:14:20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 209 Incr 0 354.95M DISK 00:00:36 2020-12-21 01:14:58 BP Key: 209 Status: AVAILABLE Compressed: YES Tag: LEVEL_0 Piece Name: /u01/app/oracle/rman_backup/data/rman_backup_level_0_ORCL_20201221_212_1.bak List of Datafiles in backup set 209 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- …… …… BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 210 18.00K DISK 00:00:00 2020-12-21 01:15:07 BP Key: 210 Status: AVAILABLE Compressed: YES Tag: TAG20201221T011507 Piece Name: /u01/app/oracle/rman_backup/archivelog/arch_level_0_ORCL_20201221_213_1.log List of Archived Logs in backup set 210 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 106 15818569 2020-12-21 01:14:20 15818619 2020-12-21 01:15:07 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 211 Full 10.14M DISK 00:00:00 2020-12-21 01:15:08 BP Key: 211 Status: AVAILABLE Compressed: NO Tag: TAG20201221T011508 Piece Name: /u01/app/oracle/rman_backup/controlfile/c-1576583420-20201221-05 SPFILE Included: Modification time: 2020-12-21 01:14:20 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 15818630 Ckp time: 2020-12-21 01:15:08Catalog上list backupRMAN>list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4348 25.00K DISK 00:00:00 21-DEC-20 BP Key: 4352 Status: AVAILABLE Compressed: YES Tag: TAG20201221T011421 Piece Name: /u01/app/oracle/rman_backup/archivelog/arch_level_0_ORCL_20201221_211_1.log List of Archived Logs in backup set 4348 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 105 15818195 21-DEC-20 15818569 21-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4349 Incr 0 354.95M DISK 00:00:36 21-DEC-20 BP Key: 4353 Status: AVAILABLE Compressed: YES Tag: LEVEL_0 Piece Name: /u01/app/oracle/rman_backup/data/rman_backup_level_0_ORCL_20201221_212_1.bak List of Datafiles in backup set 4349 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- …… …… BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4350 18.00K DISK 00:00:00 21-DEC-20 BP Key: 4354 Status: AVAILABLE Compressed: YES Tag: TAG20201221T011507 Piece Name: /u01/app/oracle/rman_backup/archivelog/arch_level_0_ORCL_20201221_213_1.log List of Archived Logs in backup set 4350 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 106 15818569 21-DEC-20 15818619 21-DEC-20 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4351 Full 10.14M DISK 00:00:00 21-DEC-20 BP Key: 4355 Status: AVAILABLE Compressed: NO Tag: TAG20201221T011508 Piece Name: /u01/app/oracle/rman_backup/controlfile/c-1576583420-20201221-05 SPFILE Included: Modification time: 21-DEC-20 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 15818630 Ckp time: 21-DEC-20Part 5:RMAN备份计划: Linux (RAC)
一、Dataguard介绍1.1 概念Data Guard是保证企业数据的高可用性（high availability）、数据保护（data protection）以及灾难恢复（disaster recovery）的集成化灾难恢复解决方案，该技术可以维护生产数据库一个或多个同步备份，有一个生产数据库和若干个备用数据库组成，并形成一个独立的、易于管理的数据保护方案。Data Guard备用数据库可以与生产系统位于相同的数据中心，也可以是在地理位置上分布较远的的远程灾难备份中心。1.2 原理Data Guard的基本原理是，当某次事务处理对生产数据库中的数据作出更改时，Oracle将在联机重做日志文件中记录此次更改。在Data Guard中，除了把日志记录到本地的联机日志文件和归档日志文件中外，还通过网络，把日志信息发送到远程的备用数据库服务器上。这个备用日志文件写入过程可以是实时、同步的，以实现零数据丢失（最大保护模式）；也可以是异步的，以减少对网络带宽的压力（最大可用性模式）；或者是通过归档日志文件的批量传输模式，以减少对生产系统的性能影响（最大性能模式）。当备份数据库接收到日志信息时，Data Guard可以自动利用日志信息实现数据的同步。当主数据库打开并处于活动状态时，备份数据库可以执行恢复操作；如果主数据出现了故障，备用数据库即可以被激活并接管生产数据库的工作。1.3 特点1、需要冗余的服务器设备 该模式需要有冗余的服务器硬件，硬件成本较高。2、需要冗余的存储设备 主机和备机都需要同样的存储空间，成本较高。3、安装配置比较复杂 该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。4、管理维护成本高 该模式对维护人员的要求较高，维护成本高。5、具备一定的容灾特性 当主机数据库不可用并短期内无法恢复时，可以把数据库系统切换到备机上，具备容灾的功能。6、备机可以用作只读查询 备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。1.4 搭建DG方式Data Guard备库按照从生产数据库传输过来的日志处理方法的不同分为物理备库、逻辑备库和快照备库。物理备库 (三种方法：1、RMAN的Duplicate 2、RMAN的备份恢复 3、DBCA )备份数据库处于mount状态下，直接利用数据恢复技术，把日志文件中记录的数据变更应用在备份数据库中，从而实现与生产数据库的数据同步，在进行数据同步的时候，物理备份数据库是不能打开的、也无法提供数据查询等服务；物理备用数据库也可以通过只读的方式打开，此时就只能接收日志文件，而无法进行数据的同步。逻辑备库数据库是处于正常打开状态，当它接收到新的日志信息后，利用日志挖掘器的功能，把日志中记录的变更信息转换成具体的SQL语句，并在逻辑备用数据库上执行这些SQL语句，从而实现与生产数据库的数据同步。逻辑备份数据支持在数据同步时，进行数据的查询、报表等操作。Oracle从9i R2开始支持逻辑备份数据库。快照备库可以暂时将物理备份数据库转换为可更新的数据库，快照备份数据不会立马应用接收到的日志文件，设一个时间点，一次性转换。1.5 备库数据保护级别Oracle Data Guard 支持多种级别的数据保护模式：最大性能模式，最大可用性模式，最大保护模式。分别对应于“重要信息系统灾难恢复指南”中的5级，5级6级自适应，6级的数据保护级别。其中对应6级的最大保护模式可以实现实时数据实时同步和0数据丢失。另外，Oracle Data Guard 可以设置延时应用时间窗口，从而防范错误操作、黑客攻击等人为错误导致的数据损坏。1.5.1 保护模式和重做传输要确定适当的保护模式，企业需要根据用户对系统响应时间的要求来估量它们对数据保护的业务要求。下表从数据丢失风险的角度概述了各种模式的适用性。保护模式在灾难出现时数据丢失的风险重做传输机制最大保护零数据丢失LGWR SYNC最大可用零数据丢失LGWR SYNC最高性能最小数据丢失LGWR SYNC或ARCH1.5.2 最大保护模式最大保护模式为主数据库提供了最高水平的数据保护，从而确保一个全面的零数据丢失的灾难恢复解决方案。当在最大保护模式下运行时，重做记录由日志写入器 (LGWR) 进程从主数据库同步地传输到备用数据库，并且直到确认事务数据在至少一个备用服务器上的磁盘上可用时，才在主数据库上提交事务。强烈建议，这种模式应至少配置两个备用数据库。当最后参与的备用数据库不可用时，主数据库上的处理将停止。这就确保了当主数据库与其所有备用数据库失去联系时，不会丢失事务。由于重做传输的同步特性，这种最大保护模式可能潜在地影响主数据库响应时间。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。需要这种最大保护模式的企业有股票交易所、货币交易所、金融机构等。1.5.3 最大可用性模式最高可用性模式拥有仅次于最高水平的主数据库数据可用性。如同最大保护模式一样，重做数据由 LGWR 从主数据库同步地传输到备用数据库，直到确认事务数据在备用服务器的磁盘上可用时，事务才在主数据库上完成。不过，在这种模式下（与最大保护模式不同），如果最后参与的备用数据库变为不可用，例如由于网络连接问题，处理将在主数据库上继续进行。备用数据库与主数据库相比，可能暂时落在后面，但当它再次变为可用时，备用数据库将使用主数据库上累积的归档日志自动同步，而不会丢失数据。由于同步重做传输，这种保护模式可潜在地影响响应时间和吞吐量。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。最高可用性模式适用于想要确保获得零数据丢失保护，但不想让生产数据库受网络/备用服务器故障影响的企业。如果又一个故障随后影响了生产数据库，然后最初的网络/备用服务器故障得到解决，那么这些企业将接受数据丢失的可能性。1.5.4 最高性能模式最高性能模式是默认的保护模式。它与最高可用性模式相比，提供了稍微少一些的主数据库数据保护，但提供了更高的性能。在这种模式下，当主数据库处理事务时，重做数据由 LGWR 进程异步传输到备用数据库上。另外，也可以将主数据库上的归档器进程 (ARCH) 配置为在这种模式下传输重做数据。在任何情况下，均先完成主数据库上的写操作，主数据库的提交操作不等待备用数据库确认接收。如果任意备用目标数据库变为不可用，则处理将在主数据库上继续进行，这对性能只有很小的影响或没有影响。在主数据库出现故障的情况下，尚未被发送到备用数据库的重做数据会丢失。但是，如果网络有足够的吞吐量来跟上重做流量高峰，并且使用了 LGWR 进程来将重做流量传输到备用服务器，则丢失的事务将非常少或者为零。当主数据库上的可用性和性能比丢失少量数据的风险更重要时，应该使用最高性能模式。这种模式还适合于 WAN 上的 Data Guard 部署，在 WAN 中，网络的内在延迟可能限制同步重做传输的适用性。二、环境准备2.1 环境规划实验基于单机DB+单实例DG注：备库中的实例名是参数设置的 主库(主机1)备库(主机2)DB 类型单机单机OSCentos 7.8Centos 7.8HostnameKlausKlausdgIP192.168.2.2192.168.2.3DB_Version12.1.0.212.1.0.2ORACLE_BASE/u01/app/oracle/u01/app/oracleORACLE_HOME/u01/app/oracle/product/12.1.0/dbhome_1/u01/app/oracle/product/12.1.0/dbhome_1DB_NAMEorclorclORACLE_SIDorclorcldgDB_Unique_NameorclorcldgInstance_Nameorclorcldgservice_namesorclorcldgTNS_NameORCLORCLDG闪回区开启开启归档开启开启2.2 数据库安装(略)在主库上安装数据库软件，并建监听和实例在备库上安装数据库软件，并建监听，但不创建实例，安装时选择只安装软件即可三、主库配置3.1 CDB和Non CDB环境下如果实例处于多租户架构中，设置操作和Non-CDB方法相同，都在CDB下完成；实验中有两个PDB1和PDB2，在创建备库后，默认两个PDB都会同步到备库，也可以通过参数指定只同步某个PDB；也可以设置完同步的备库后，主库中再添加的PDB3也会同步到备库中。-- CDB环境下的实验配置中的主库实例也是orcl,备库是orcldg，需要设置主备不同的 DB_UNIQUE_NAME -- 同样可以通过name参数查看 SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string service_names string orcl涉及的CDB简单操作-- 查看当前处于的那个容器 SQL> show con_name CON_NAME ------------------ CDB$ROOT -- 如果处于CDB，可以查看所有PDB SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO -- 切换到PDB1 SQL> alter session set container=PDB1; Session altered. -- 再次查看处于PDB1 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE NO -- 再次查看处于PDB1 SQL> show con_name CON_NAME ------------------------------ PDB1 -- 切回CDB SQL> alter session set container=CDB$ROOT; Session altered.3.2 开启归档-- 如果是CDB环境，先检查处于CDB根容器中，PDB下是不允许的 SQL> show con_name CON_NAME ------------------ CDB$ROOT -- 查看归档是否Enable SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 129 Next log sequence to archive 131 Current log sequence 131 -- 这里的归档路径是默认的 USE_DB_RECOVERY_FILE_DEST -- 如果没有开启，开启的步骤 shutdown immediate startup mount alter database archivelog; alter database open; SQL> alter system switch logfile;3.3 开启闪回闪回区的管理，及闪回日志管理，数据库能闪回到过去的多久时间点，这个由闪回区大小以db_flashback_retention_target 参数控制，在闪回区大小足够的情况，下默认能闪回1440秒也就是一天的数据。SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440-- 1、查看闪回未开启 SQL> select flashback_on from v$database; FLASHBACK_ON ------------ NO -- 2、查看 db_recovery_file_dest 为空 SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 recovery_parallelism integer 0 remote_recovery_file_dest string -- 3、指定闪回区大小，指定闪回目录路径(顺序必须先设置闪回区大小，才能指定闪回目录，否则报错) SQL> alter system set db_recovery_file_dest_size=4560m scope=spfile; System altered. SQL> shutdown immediate SQL> startup -- 需要重启数据库才能生效，再继续修改目录位置，修改完目录也需要重启实例 -- 4、先创建目录，在指定该闪回区域目录 mkdir -p /u01/app/oracle/fast_recovery_area SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; System altered. SQL> shutdown immediate SQL> startup -- 5 开启闪回 SQL> alter database flashback on; Database altered.3.4 开启所有PDB如果是CDB环境，开启所有PDBcol name for a10 SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ------------------------------ PDB$SEED READ ONLY PDB1 MOUNTED PDB2 MOUNTED -- 开启 PDB SQL> alter pluggable database all open; Pluggable database altered. -- 查看 SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ---------- ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB2 READ WRITE3.5 设置数据库强制归档有一些DDL语句可以通过指定NOLOGGING子句的方式避免写REDO(目的是提高速度，某些时候确实有效)。指定数据库为Force Logging模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作，而忽略类似NOLOGGING之类的指定参数。如果在执行Force Logging时有NOLOGGING之类的语句在执行，那么Force Logging会等待，直到这类语句全部执行。Force Logging是作为固定参数保存在控制文件中，因此其不受重启之类操作的影响(只执行一次即可)，如果想取消，可以通过ALTER DATABASE NO FORCE LOGGING语句关闭强制记录。-- 如果是CDB环境，先检查处于CDB根容器中，PDB下是不允许的 -- 查看 SQL> SELECT NAME, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE; NAME OPEN_MODE FORCE_LOGGING ---------- -------------------- --------------------------------------- ORCL READ WRITE NO -- **开启强制归档** SQL> alter database force logging;3.6 添加Standby redo log3.6.1 说明：为主库添加standby redo log后，备库自动同步，所以备库不用再创建standby redo logData Guard在最大保护和最高可用性模式下，Standby数据库必须配置standby redo log3.6.2 作用：实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将主库接收到的重做日志按顺序导入到standby logfile在主库创建Standby logfile是便于发生角色转换后备用3.6.3 创建原则：确保Standby redo log的大小与主库online redo log的大小一致如果主库为单实例数据库：Standby redo log组数=主库日志总数+1如果主库是RAC数据库：Standby redo log组数=(每线程的日志数+1)*最大线程数不建议复用Standby redo log，避免增加额外的I/O以及延缓重做传输-- 如果是CDB环境，先检查处于CDB根容器中，PDB下是不允许的； -- 在Oracle 12c的架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件； -- 所以如果是CDB下，就在CDB中加 Standby redo log。 -- 1、查看组数=3 SQL> select count(group#),thread# from v$log group by thread#; COUNT(GROUP#) THREAD# ------------ -------- 3 1 -- 2、大小=50M SQL> select group#,bytes/1024/1024 from v$log; GROUP# BYTES/1024/1024 ------ --------------- 1 50 2 50 3 50 -- 3、创建standby logfile(3+1组、每组50M) -- 注意路径大小写，本文部署CDB环境时，SID是大写的ORCL SQL> select * from v$standby_log; alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/stantby_redo04.log') size 50m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/stantby_redo05.log') size 50m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/stantby_redo06.log') size 50m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/stantby_redo07.log') size 50m; -- 4、验证查看 SQL> select * from v$standby_log; SQL> select group#,bytes/1024/1024 from v$standby_log; GROUP# BYTES/1024/1024 ---------- --------------- 4 50 5 50 6 50 7 50 SQL> select group#,status,type,member from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- ---------------------------------- 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log 4 STANDBY /u01/app/oracle/oradata/orcl/stantby_redo04.log 5 STANDBY /u01/app/oracle/oradata/orcl/stantby_redo05.log 6 STANDBY /u01/app/oracle/oradata/orcl/stantby_redo06.log 7 STANDBY /u01/app/oracle/oradata/orcl/stantby_redo07.log3.7 修改参数文件3.7.1 设置DB唯一名称-- 通常主库的DB名和唯一名相同,show参数查看 alter system set db_unique_name='orcl' scope=spfile; -- 其中dg_config填写的是主备库的db_unique_name alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=spfile;3.7.2 设置归档日志的路径-- =前后不能有空格，本地的archive路径没有修改，使用默认 alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile; alter system set log_archive_dest_2='SERVICE=ORCLDG ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcldg' scope=spfile; -- 第一个ORCLDG是备库tnsname.ora的连接名(最开头名称) -- 第二个orcldg是DB_UNIQUE_NAME3.7.3 启用设置的日志路径alter system set log_archive_dest_state_1=enable scope=spfile; alter system set log_archive_dest_state_2=enable scope=spfile;3.7.4 设置归档日志进程的最大数量--（视实际情况调整） alter system set log_archive_max_processes=30 scope=both;3.7.5 设置备库从哪个数据库获取归档日志-- 只对standby库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用，值就是TNSNAME -- fal表示fetch archive log -- fal_client用于发送日志，fal_server用于接受日志。也即无论是主库或备库，fal_server=对方，fal_client=自己 alter system set fal_server=orcldg; alter system set fal_client=orcl;3.7.6 设置文件管理模式-- 表示如果Primary数据库数据文件发生修改（如新建、重命名等）则按照本参数的设置在Standby数据库中作相应修改。 -- 设为AUTO表示自动管理。设为MANUAL表示需要手工管理 -- 此项设置为自动，不然在主库创建数据文件后，备库不会自动创建 alter system set standby_file_management=auto scope=spfile;3.7.7 主备文件路径如果主备库文件的存放路径不同，还需要设置以下两个参数（需要重启数据库生效）-- 小写的orcl alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg','/u01/app/oracle/oradata/orcl' scope=spfile; alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg','/u01/app/oracle/oradata/orcl' scope=spfile; -- 大写的ORCL alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCLDG','/u01/app/oracle/oradata/ORCL' scope=spfile; alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCLDG','/u01/app/oracle/oradata/ORCL' scope=spfile;3.7.8 设置数据库口令文件的使用模式SQL> show parameter remote_login_passwordfile NAME TYPE VALUE ------------------------- ------ --------- remote_login_passwordfile string EXCLUSIVE -- 默认也是EXCLUSIVE alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;3.7.9 设置默认监听此处直接让监听为空即可保持后面创建的默认静态监听，否则备库无法从参数文件启动或者如果想要设置监听值，也可以alter system set local_listener='(DESCRIPTION =(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.2)(PORT=1521)))';SQL> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_ORCL alter system set local_listener=''; SQL> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string四、备库配置4.1 变量环境[oracle@klausdg ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH export EDITOR=vi export ORACLE_SID=orcldg export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin export PATH=$ORACLE_HOME/bin:$PATH # 使变量生效 [oracle@klausdg ~]$ source ~/.bash_profile4.2 主备库密码文件# Data Guard环境中，数据库的sys用户名密码要相同，可直接将主库复制密码文件复制到备库 # 拷贝后备库的密码文件格式：ora+sid，Windows下格式为：PWD[sid].ora scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.2.3:$ORACLE_HOME/dbs/orapworcldg4.3 修改参数文件4.3.1 复制主库参数文件备库的参数文件根据主库参数进行修改主库上创建pfile，然后拷贝给备库SQL> create pfile from spfile; # 拷贝后的静态参数文件格式：init+sid.ora $ scp $ORACLE_HOME/dbs/initorcl.ora oracle@192.168.2.3:$ORACLE_HOME/dbs/initorcldg.ora4.3.2 修改initorcldg.ora内容orcl.__data_transfer_cache_size=0 orcl.__db_cache_size=1761607680 orcl.__java_pool_size=16777216 orcl.__large_pool_size=33554432 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=822083584 orcl.__sga_target=2466250752 orcl.__shared_io_pool_size=117440512 orcl.__shared_pool_size=520093696 orcl.__streams_pool_size=0 # 修改为备库的orcldg *.audit_file_dest='/u01/app/oracle/admin/orcldg/adump' *.audit_trail='db' *.compatible='12.1.0.2.0' *.control_file_record_keep_time=19 # 修改为备库的orcldg *.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl' #Restore Controlfile *.db_block_size=8192 *.db_domain='' # 修改顺序主备路径和主库的相反 *.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' # 实例名相同 *.db_name='orcl' # 修改实例唯一名 *.db_unique_name='orcldg' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4560m *.diagnostic_dest='/u01/app/oracle' # 修改orcldgXDB *.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)' # 和主库配置顺序相反 *.fal_client='ORCLDG' *.fal_server='ORCL' # 使用默认监听 *.local_listener='' # 和主库配置相同 *.log_archive_config='DG_CONFIG=(orcl,orcldg)' # 修改为本库(备库)的唯一名 *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ocrldg' # 服务名修改为连接主库TNS的ORCL，DB唯一名修改为主库的orcl *.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_max_processes=30 # 和主库配置相反 *.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' *.nls_date_format='yyyy-mm-dd hh24:mi:ss' *.open_cursors=300 *.pga_aggregate_target=780m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2341m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' *.utl_file_dir='/home/oracle/logmnr'4.4 备库创建spfileSQL> create spfile from pfile; File created. SQL> 4.5 创建备库所需路径Oracle建议一般控制文件至少三个，放在不同的地方，本文主库没有设置如果主库修改相关文件目录，拷贝过来的参数文件修改后也需要在备库中创建相关目录# 每个人的环境不同，根据备库参数文件创建所需目录 mkdir -p /u01/app/oracle/admin/orcldg/adump mkdir -p /u01/app/oracle/oradata/orcldg #根据情况是否大写 mkdir -p /u01/app/oracle/oradata/ORCLDG mkdir -p /u01/app/oracle/fast_recovery_area五、主备库监听5.1 开机自启动监听设置主备库自动开启监听不建议设置自动开启数据库,因为DG开关有先后顺序，要手动开启#修改/etc/rc.d/rc.local文件 su - root vi /etc/rc.d/rc.local #增加一行(/etc/rc.local是/etc/rc.d/rc.local的软连接) su - oracle -c 'lsnrctl start' #授权rc.local文件可执行权限 chmod +x /etc/rc.d/rc.local5.2 主库注册静态监听# 静态注册的参数概念： GLOBAL_DBNAME :数据库服务名，默认和SID_NAME保持一致。在本例中这里需要填写PDB的服务名。 ORACLE_HOME :实例运行的ORACLE_HOME目录，如果是集群环境这里也填写ORACLE的ORACLE_HOME目录。 SID_NAME :数据库实例名。和数据库参数INSTANCE_NAME一致。使用Net Manager创建监听不容易出错，添加 Database Services，客户端如果没有添加hosts信息或DNS建议写IP如果是CDB环境，监听也是相同静态配置即可，无需配置PDB监听或TNS[oracle@klaus admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle5.3 备库注册静态监听[oracle@klausdg admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldg) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = orcldg) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.3)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle5.4 主备库TNS监听相同[oracle@klausdg admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCLDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.3)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )5.5 主备连接测试# 主备库监听重启 lsnrctl stop lsnrctl start # 主备测试 tnsping orcl tnsping orcldg # 测试登录 # # 主库登陆 在密码文件拷贝后才可以登陆 [oracle@klaus ~]$ sqlplus sys/Oracle#2020@orcl as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 16 01:45:02 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> [oracle@klaus ~]$ sqlplus sys/Oracle#2020@orcldg as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 16 01:45:56 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> # 备库登录 [oracle@klausdg ~]$ sqlplus sys/Oracle#2020@orcl as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 16 01:47:24 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> [oracle@klausdg ~]$ sqlplus sys/Oracle#2020@orcldg as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 16 01:47:58 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL>六、【方式一：物理备库之RMAN Duplicate】Duplicate 方式创建物理备库；通过这种方式直接在线从主库搭建物理备库。本次实验：Non-CDB环境6.1 备库启动到nomount状态-- 使用pfile启动到nomount [oracle@klausdg ~]$ sqlplus / as sysdba SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initorcldg.ora'; ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes -- 创建Spfile SQL> create spfile from pfile; SQL> shutdown immediate; -- 启动到nomount SQL> startup nomount6.2 登陆RMAN连接主备库-- 主库target/orcl 备库auxiliary/orcldg -- 主备库DB_NAME必须一致，主库是open状态，备库是nomount状态 [oracle@klausdg ~]$ rman target sys/Oracle#2020@orcl auxiliary sys/Oracle#2020@orcldg Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 16 16:54:46 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1576583420) connected to auxiliary database: ORCL (not mounted) RMAN> 6.3 开始 DuplicateRMAN> duplicate target database for standby from active database dorecover nofilenamecheck; -- 执行过程（略） Starting Duplicate Db at 16-AUG-21 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=243 device type=DISK current log archived ... .... ..... media recovery complete, elapsed time: 00:00:01 Finished recover at 16-AUG-21 Finished Duplicate Db at 16-AUG-21七、【方式二：物理备库之RMAN备份还原】使用 RMAN 备份恢复方法，将备份的文件和控制文件传到备库恢复本次实验：Non-CDB环境7.1 主库RMAN全备-- 创建RMAN备份路径 mkdir -p /u01/rmanbackup -- RMAN备份 $ rman target / RMAN> backup database format='/u01/rmanbackup/FULL_%d_%T_%s.dbf'; Starting backup at 18-AUG-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 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=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/test.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: starting piece 1 at 18-AUG-21 channel ORA_DISK_1: finished piece 1 at 18-AUG-21 piece handle=/u01/rmanbackup/FULL_ORCL_20210818_1.dbf tag=TAG20210818T220447 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 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 including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 18-AUG-21 channel ORA_DISK_1: finished piece 1 at 18-AUG-21 piece handle=/u01/rmanbackup/FULL_ORCL_20210818_2.dbf tag=TAG20210818T220447 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-AUG-217.2 主库备份控制文件RMAN> backup current controlfile for standby format '/u01/rmanbackup/control.bak';7.3 拷贝备份文件到备库-- 创建和主库相同的路径 mkdir -p /u01/rmanbackup [oracle@klaus ~]$ cd /u01/rmanbackup/ [oracle@klaus rmanbackup]$ ls control.bak FULL_ORCL_20210818_1 FULL_ORCL_20210818_2 -- 传输文件到备库改路径下 [oracle@klaus rmanbackup]$ scp /u01/rmanbackup/* oracle@192.168.2.3:/u01/rmanbackup oracle@192.168.2.3's password: control.bak 100% 9888KB 36.6MB/s 00:00 FULL_ORCL_20210818_1.dbf 100% 1299MB 51.9MB/s 00:25 FULL_ORCL_20210818_2.dbf 100% 9920KB 18.0MB/s 00:00 7.4 备库启动到nomount状态-- 使用pfile启动到nomount -- 如果已经创建spfile可以直接启动到 nomount状态 [oracle@klausdg ~]$ echo $ORACLE_SID orcldg [oracle@klausdg ~]$ sqlplus / as sysdba -- 启动 SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initorcldg.ora'; ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes -- 创建Spfile SQL> create spfile from pfile; SQL> shutdown immediate; -- 启动到nomount SQL> startup nomount7.5 备库恢复控制文件[oracle@klausdg ~]$ rman target / RMAN> restore standby controlfile from '/u01/rmanbackup/control.bak'; .. ... Finished restore at 18-AUG-217.6 备库mountSQL> alter database mount; Database altered.7.7 还原数据库RMAN> restore database; Starting restore at 19-AUG-21 released channel: ORA_DISK_1 Starting implicit crosscheck backup at 19-AUG-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=355 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 19-AUG-21 Starting implicit crosscheck copy at 19-AUG-21 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 19-AUG-21 searching for all files in the recovery area cataloging files... no files cataloged 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 00001 to /u01/app/oracle/oradata/orcldg/system01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcldg/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcldg/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcldg/test.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcldg/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/rmanbackup/FULL_ORCL_20210818_1.dbf channel ORA_DISK_1: piece handle=/u01/rmanbackup/FULL_ORCL_20210818_1.dbf tag=TAG20210818T235901 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 19-AUG-21 八、【方式三：物理备库之CDB下的DBCA】8.1 DBCA备库说明前两个物理备库方式都是基于Non-CDB创建DG的，也可以在CDB下通过Duplicate或RMAN备份还原的方法创建DG，配置主备库的条件相同，处于CDB状态下设置主库的配置即可；到 12cR2 (12.2.0.1)后，新增 DBCA 方式直接建立物理备库的方法。但是对版本和实例环境有一定限制条件：12cR2中:-- 1、12cR2 主库必须是单机环境，非 RAC 数据库； -- 若主库是 RAC 数据库，错误如下： [FATAL] [DBT-16056] Specified primary database is not a Single Instance (SI) database. CAUSE: Duplicate database operation is supported only for SI databases.-- 2、12cR2 主库必须是非 CDB 环境； -- 若主库是 CDB 环境，错误如下： [FATAL] [DBT-16057] Specified primary database is a container database (CDB). CAUSE: Duplicate database operation is supported only for non container databases.18c 及以后:18c(12.2.0.2)开始，这些限制条件已经取消，即主库是 CDB 或 RAC环境都可以通过 dbca 来创建物理备库本节DBCA备库环境： 19c的CDB环境，主/备库的环境和前面的配置方法相同8.2 配置Hosts在没有DNS的配置下，需要配置Hosts，否则DBCA过程通讯报错：ORA-17627：TNS：could not resolve connect identifier specifiedcat /etc/hosts192.168.2.2 klaus192.168.2.3 klausdg8.3 DBCA过程8.3.1 主库开启-- 如果是CDB环境，先开启数据库也要开启PDB SQL> startup SQL> alter pluggable database all open; Pluggable database altered.8.3.2 DBCA命令不需要备库数据库处于nomount状态，监听开启后备库上直接DBCA即可如果是CDB环境，完成后CDB会直接开启，再开启PDB即可。-- 19c 官网展示的模板 dbca -createDuplicateDB -gdbName global_database_name -primaryDBConnectionString easy_connect_string_to_primary -sid database_system_identifier [-createAsStandby [-dbUniqueName db_unique_name_for_standby]] [-customScripts scripts_list] -- 19c 官网DBCA案例 # Database DB_UNIQUE_NAME Oracle Net Service Name # Primary chicago chicago # Physical standby boston boston -- dbca –silent -createDuplicateDB -primaryDBConnectionString myprimary.domain:1523/chicago.domain -gdbName chicago.domain -sid boston -initParams instance_name=boston –createAsStandby -- 本实验如下设置 dbca -silent -createDuplicateDB \ -gdbName orcl \ -sid orcldg \ -sysPassword Oracle#2020 \ -primaryDBConnectionString 192.168.2.2:1521/ORCL \ -nodelist klausdg \ -databaseConfigType SINGLE \ -createAsStandby -dbUniqueName orcldg-- 执行过程 [oracle@klausdg ~]$ dbca -silent -createDuplicateDB \ > -gdbName orcl \ > -sid orcldg \ > -sysPassword Oracle#2020 \ > -primaryDBConnectionString 192.168.2.2:1521/ORCL \ > -nodelist klausdg \ > -databaseConfigType SINGLE \ > -createAsStandby -dbUniqueName orcldg Prepare for db operation 22% complete Listener config step 44% complete Auxiliary instance creation 67% complete RMAN duplicate 89% complete Post duplicate database operations 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcldg/orcldg.log" for further details.九、开启并验证DG同步9.1 开启实时同步alter database open; -- 如果是CDB环境，需要开启pdb SQL> alter pluggable database all open; alter database recover managed standby database using current logfile disconnect from session; -- 从12C开始，RECOVER语句，不需要再指定using current logfile，Oracle会自动判断日志应用是否是实时的。9.2 备库开启闪回-- 查看闪回 SQL> select flashback_on from v$database; FLASHBACK_ON ------------ NO -- 取消实时同步 SQL> alter database recover managed standby database cancel; -- 关闭数据库 SQL> shutdown immediate SQL> startup mount SQL> alter database flashback on; -- 开启数据库 SQL> alter database open; -- 再次开启同步 SQL> alter database recover managed standby database using current logfile disconnect from session; -- 查看 SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY -- 查看主库 PRIMARY SQL> select log_mode,open_mode ,database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE ------------ -------------------- ---------------- ARCHIVELOG READ WRITE PRIMARY -- 查看备库 PHYSICAL STANDBY SQL> select log_mode,open_mode ,database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE ------------ -------------------- ---------------- ARCHIVELOG READ ONLY PHYSICAL STANDBY9.3 归档同步验证# 主库切换归档, 查询主库备库最大归档序号，一致即归档同步成功 SQL> alter system archive log current; SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 72 # 查看主备归档日志是否能正常传输（APPLIED=yes） SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;9.4 查看主备库状态-- 主库 SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO STANDBY PRIMARY -- 备库 SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED PHYSICAL STANDBY9.5 主库查看备库归档路径报错-- 主库 查看archive_log_dest_2列是否有error SQL> select status,error from v$archive_dest where dest_id=2; STATUS ERROR --------- -------------------- VALID9.6 主库Dataguard的状态信息-- 查看Dataguard的状态信息 col message format a150 SQL> select message_num,message from v$dataguard_status;9.7 建表测试-- 主库 create table test(id number); insert into test values(1); commit; select * from test; -- 备库 select * from test; 十、DG切换和恢复配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。DG的故障切换分为switchover和failover两种10.1 Switchover10.1.1 主库上操作-- 1、主库查询状态 -- 注意：下面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换 SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO STANDBY PRIMARY -- 2、主库开始切换 SQL> alter database commit to switchover to physical standby; Database altered. -- 主库有会话连接的时候使用如下命令 -- alter database commit to switchover to physical standby with session shutdown; -- 3、主库变备库，开启到mount状态 SQL> startup mount ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes Database mounted. SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY10.1.2 备库上操作-- 1、备库查询状态 -- 注意：下面查询结果为TO PRIMARY 或 SESSIONS ACTIVE表明可以进行切换 SQL> select switchover_status,database_role from v$database; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO PRIMARY PHYSICAL STANDBY -- 2、备库开始切换 SQL> alter database commit to switchover to primary with session shutdown; Database altered. -- 3、开启数据库 SQL> alter database open; Database altered. -- 4、查询状态，已经变成主库 SQL> select switchover_status,database_role,open_mode from v$database; SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE -------------------- ---------------- -------------------- TO STANDBY PRIMARY READ WRITE10.1.3 新备库(原主库)上的操作-- 1、开机数据库open SQL> alter database open; Database altered. -- 2、开启同步 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. -- 3、主备检查切换状态 SQL> select open_mode,database_role,db_unique_name from v$database;10.1.4 验证-- 新主库切换归档，主备查询 alter system archive log current; SQL> select max(sequence#) from v$archived_log; -- 新主库表插入数据测试 insert into test values(2); commit; select * from test;10.2 FailoverFailover是当主库真正出现严重系统故障，如数据库宕机，软硬件故障导致主库不能支持服务，从而进行的切换动作。注意：为了能够在failover后能够恢复DG,需要在主库上开启flashback，否则DG就可能需要重新搭建。先把主库关机，模拟Failover由于主库已经不可访问，下面所有的操作都在备库完成：10.2.1 备库上操作SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY10.2.2 停止实时同步SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database finish force; Database altered.10.2.3 状态改为主库SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY -- 更改状态 SQL> alter database commit to switchover to primary; Database altered. -- 再次查看 SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY10.2.4 开启数据库open-- 开启数据库open SQL> alter database open; Database altered. -- 查看状态 SQL> select switchover_status,database_role,open_mode from v$database; SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE -------------------- ---------------- -------------------- FAILED DESTINATION PRIMARY READ WRITE -- 当主库的SWITCHOVER_STATUS状态为FAILED DESTINATION时，是因为备库不在mount状态下,这里已经没有备库10.3 Failover恢复上面提到了failover，这种情形是当主库真正出现异常之后，才会执行的操作，那么执行过failover 之后，如何在重新构建DG，这里我们利用flashback database来重构，具体方法如下：10.3.1 在新主库上执行-- 查询变成新主库的scn SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 1251423410.3.2 在老主库上执行-- 要将之前出问题的老主库变成备库 -- 1、启动 mount 状态下 SQL> startup mount ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes Database mounted. -- 2、闪回到新的主库查询的scn SQL> flashback database to scn 12514234; Flashback complete. -- 3、转为备库 SQL> alter database convert to physical standby; Database altered. -- 4、关闭 SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. -- 5、开启 SQL> startup ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. -- 6、开启同步 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. -- 7、同步验证(略)十一、【方式四：逻辑备库Logical Standby】11.1 说明Oracle Data Guard逻辑备库是利用主库的一个备份首先建立一个物理备库，然后再将其转换为逻辑备库。 这之后主库将日志传递到备库，备库利用logminer从主库的日志中解析出主库所执行过的SQL，在备库上重新执行一遍，从而保证与主库的数据在逻辑上保持一致。 与物理备库相对应的是，物理备库使用的是redo apply，逻辑备库使用的是sql apply。 因此逻辑备库仅仅保证数据与主库是在逻辑上是一致的，从而逻辑备库可以处于open状态下并进行相应的DML操作。11.2 备库停止MGR(Redo apply应用)-- 对于将物理备库切换到逻辑备库，需要在主库构建LogMiner字典及启用补充日志 -- 因此应先停用备库的MRP进程，避免产生额外的Redo Apply SQL> alter database recover managed standby database cancel;11.3 主库构建LogMiner字典-- 1、创建字典表空间 SQL> create tablespace logmnrtbs datafile '/u01/app/oracle/oradata/orcl/logmnrtbs.dbf' size 100m autoextend on next 5m maxsize 2000m; -- 2、关联字典表空间 SQL> execute dbms_logmnr_d.set_tablespace('logmnrtbs'); PL/SQL procedure successfully completed. -- 3、构建LogMiner字典 SQL> exec dbms_logstdby.build; PL/SQL procedure successfully completed.11.4 物理备库恢复为逻辑备库SQL> select name,open_mode,database_role,protection_mode from v$database; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- ORCL MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE ======================================================================== -- 1、关闭,必须一致性性关闭数据库 SQL> shutdown immediate -- 2、以exclusive方式启动到mount SQL> startup mount exclusive -- 3、转换为逻辑备库并设置新的db_name SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ORCLLG; -- 4、关闭,必须一致性性关闭数据库 SQL> shutdown immediate -- 5、正常启动到mount SQL> startup mount ======================================================================== -- 可以看到name自动改变，为读写模式，日志序列也从1开始 SQL> select name,open_mode,database_role,protection_mode from v$database; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- ORCLLG MOUNTED LOGICAL STANDBY MAXIMUM PERFORMANCE SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 111.5 开启逻辑备库并同步-- 由于逻辑Standby与Primary数据库事务并不一致，因此第一次打开时必须指定RESETLOGS子句 SQL> ALTER DATABASE OPEN RESETLOGS; -- 执行启动REDO应用的命令，附加APPLY IMMEDIATE子句，以打开实时应用 SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; 11.6 测试+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 测试逻辑DG部分 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 在测试数据是否正常同步时，不要使用数据库的内部账号，逻辑dg在默认情况下不传输系统用户下，用户自行创建的数据 -- 比如在sys用户下创建一个test表，这个表不会同步到逻辑备库 -- 可以使用下列语句查看哪些用户作为内部用户 SQL> SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA'; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 以下在方式四的逻辑DG环境中，主库解锁scott，备库已也会同步经解锁该用户，并创建表等操作 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL> alter user scott account unlock; User altered. SQL> conn scott/tiger ERROR: ORA-28002: the password will expire within 7 days create table test(id number); insert into test values(1); commit; -- 主备查询一致，至此逻辑Standby创建完成. select * from test;11.7 Swichover11.7.1 状态检查-- 1、查看当前Primary数据库状态： #如果该查询返回TO STANDBY 或SESSIONS ACTIVE则表示状态正常，可以执行转换操作 -- 主库 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO STANDBY PRIMARY -- 备库 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED LOGICAL STANDBY11.7.2 [准备]转换Primary为逻辑Standby-- 2、执行下列语句，将Primary置为准备转换的状态： SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY; Database altered. -- 执行完上述操作后，Primary数据库就开始为角色的转换打好基础，时刻准备着接收来自逻辑Standby数据库，也就是未来的新Primary数据库发来的REDO数据。 -- 查看一下SWITCHOVER_STATUS的状态： SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- PREPARING SWITCHOVER PRIMARY11.7.3 [准备]转换逻辑Standby为Primary-- 转换为Primary的PREPARE状态 -- 语句执行时响应会稍微有点慢 SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; Database altered. -- 执行完后查看当前逻辑Standby数据库的转换状态： SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- PREPARING SWITCHOVER LOGICAL STANDBY 11.7.4 再次检查Primary状态SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO LOGICAL STANDBY PRIMARY -- 检查结果却非常重要，它直接关系到switchover转换是否能够成功。 -- 逻辑Standby执行完PREPARE命令之后，就会生成相应的LogMiner字典数据（就像我们前面创建逻辑Standby时，Primary数据库会生成LogMiner字典数据一样），只有它正常生成并发送至当前的Primary数据库，转换操作才能够继续下去。不然当前的Primary数据库在转换完之后，可能就失去了从新的Primary数据库接收REDO数据的能力了。 -- 因此，如果上述查询的返回结果不是TO LOGICAL STANDBY的话，DBA就需要考虑是否取消此次转换，检查原因，然后再重新操作了 -- ++取消转换可以通过下列语句进行++++++++++++++++++++++++++++++++++++++++++++++++ ALTER DATABASE PREPARE TO SWITCHOVER CANCEL; -- ++需要分别在Primary端和逻辑Standby端执行+++++++++++++++++++++++++++++++++++++11.7.5 正式转换Primary为逻辑StandbySQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; Database altered. -- 该语句需要等待当前Primary数据库所有事务全部结束才开始执行 -- 该语句执行过程中会自动拒绝用户提交新事务或修改需求，为了确保该操作尽可能快的执行，最好自开始切换操作起就禁止所有用户的操作 -- 该命令执行完之后，这个Primary数据库就已经成为新的逻辑Standby了。不过在新Primary执行完转换之前，不要关闭当前这个数据库11.7.6 再次检查逻辑Standby状态-- SWITCHOVER_STATUS的状态，应该为TO PRIMARY： SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- TO PRIMARY LOGICAL STANDBY -- 或者 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED LOGICAL STANDBY11.7.7 正式转换逻辑Standby为新PrimarySQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered.11.7.8 开始新的主备同步-- 新逻辑备库（即原主库上） SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered.11.7.9 测试-- 主库添加数据 insert into scott.test values(10); commit; -- 主备查询 select * from scott.test; -- ==发现并没有同步============================= -- 查看新主库状态,存在GAP SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- LOG SWITCH GAP PRIMARY -- ==解决===================================== -- 新主库关闭启动到mount shutdown immediate startup mount -- 把新主库REDO FLUSH到新备库， -- 此处是orcldg是新主库，在新主库上将日志flush到新备库上 -- ALTER SYSTEM FLUSH REDO TO target_db_name;其中target_db_name使用DB_UNIQUE_NAME ALTER SYSTEM FLUSH REDO TO orcl; -- 再次查看新主库状态，再测试数据已经同步 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED PRIMARY -- 开启数据库 alter database open; -- 测试查询结果 SQL> select * from scott.test; ID ---------- 2 10 111.8 Failover11.8.1 检查丢失的归档-- 如果此时Primary数据库仍可被访问，首先应当检查当前的归档日志序号与逻辑Standby是否相同 -- 主库 SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) -------------- 11 -- 备库 SQL> SELECT SEQUENCE#,APPLIED FROM DBA_LOGSTDBY_LOG; SEQUENCE# APPLIED ---------- -------- 8 YES 9 YES 10 YES 11 CURRENT -- Failover三种情况：(上面查询的是正常运行的还没有Failover的情况) -- 第一种：如果逻辑Standby与Primary的归档序号相同，但某些序号的APPLIED状态为NO，建议检查当前逻辑Standby数据库是否启动了SQL应用 -- 第二种：如果逻辑Standby归档序号小于Primary的归档序号，说明Primary存在尚未发送至逻辑Standby数据库的归档文件，手工复制这些文件到待转换角色的逻辑Standby端，然后在该逻辑Standby数据库的SQL*Plus命令窗口，通过执行ALTER DATABASE REGISTER LOGICAL LOGFILE 'filepath'; 命令，将复制过来的归档文件手工注册 -- 第三种：如果Primary数据库已经无法打开，就只好直接到磁盘上查看归档目录中的序号，来与逻辑Standby数据库做比较。如果Primary数据库已经完全无法访问，那直接按照后面步骤进行操作11.8.2 模拟Failover将Primary关机或关闭数据库，模拟Failover11.8.3 检查Standby日志情况-- 通过V$LOGSTDBY_PROGRESS视图，查询重做日志的应用情况 SQL> SELECT APPLIED_SCN,LATEST_SCN FROM V$LOGSTDBY_PROGRESS; APPLIED_SCN LATEST_SCN ----------- ---------- 12778829 12778829 -- 如果返回的结果中显示，两列的列值一致，则表示所有接收到的重做日志都已经应用。如果不一致的话，启动逻辑Standby端的SQL应用。11.8.4 激活前检查-- 确认待转换的逻辑Standby配置了正确的归档路径和远端服务器的归档配置(前面设置部分都配置过) SQL> SHOW PARAMETER LOG_ARCHIVE_DEST -- 查看当前操作的角色： SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE,FORCE_LOGGING FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE FORCE_LOGGING -------------------- ---------------- --------------------------------------- NOT ALLOWED LOGICAL STANDBY YES -- 如果当前FORCE_LOGGING为NO，务必执行ALTER DATABASE FORCE LOGGING;命令。11.8.5 激活新的Primary数据库-- 转换Standby数据库角色为Primary SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY; Database altered. -- 该语句主要是停止待转换的逻辑Standby中RFS进程，并应用完当前所有已接收但并未应用的REDO数据，然后停止SQL应用，将数据库转换成Primary角色 -- 再次查看当前数据库的角色 SQL> SELECT DATABASE_ROLE,FORCE_LOGGING FROM V$DATABASE; DATABASE_ROLE FORCE_LOGGING ---------------- --------------------------------------- PRIMARY YES -- 至此完成Failover后角色的转换了11.9 Failover恢复上面这种情形是当主库真正出现异常之后，才会执行的Failover操作；执行过failover之后，如何在重新构建之前老的逻辑主库DG11.9.1 DB_LINK连接主备-- 在需要重构的库中执行 -- ALTER SESSION ENABLE|DISABLE GUARD的作用 -- 该语句用于允许或禁止用户修改逻辑Standby中的结构 -- 直接修改会报错： SQL> ALTER SESSION DISABLE GUARD; -- 创建DB_LINK SQL> CREATE DATABASE LINK link_orcllg CONNECT TO system IDENTIFIED BY Oracle#2020 USING '192.168.2.2:1521/orcl'; SQL> ALTER SESSION ENABLE GUARD;11.9.2 启动同步-- 使用link同步新主库 SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY link_orcllg; Database altered. -- 后面开关机可以正常使用下面SQL应用redo -- SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;11.9.3 查看状态并验证-- 主库 -- 主库存在GAP SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- RESOLVABLE GAP PRIMARY -- 备库 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED LOGICAL STANDBY -- ==解决===================================== -- 新主库关闭启动到mount shutdown immediate startup mount -- 把新主库REDO FLUSH到新备库 -- 此处是orcl是新主库，在新主库上将日志flush到新备库orcldg上 -- ALTER SYSTEM FLUSH REDO TO target_db_name;其中target_db_name使用DB_UNIQUE_NAME ALTER SYSTEM FLUSH REDO TO orcldg; -- 再次查看新主库状态，再测试数据已经同步 SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE; SWITCHOVER_STATUS DATABASE_ROLE -------------------- ---------------- NOT ALLOWED PRIMARY -- 开启数据库 alter database open; -- 数据验证 insert into scott.test values(900); commit; select * from scott.test;十二、【方式五：快照备库Snapshot】继 方式一：物理备库之RMAN Duplicate 后的操作，快照备库是11g开始有的特性在Dataguard中，可以将standby备库切换为snapshot快照数据库，在切换为snapshot数据库后，备库将置于可读写的模式。 运用的场景：可用于模拟业务功能测试，备库处于快照备库模式后，可将备库置于可读写的状态，用于不方便在生产环境主库的测试内容,比如模拟线上测试等内容。快照模式的备库可以反复测试，在使用完成之后，可以将快照数据库切换为物理备库，则之前所有在快照备库上的操作都丢失，一般快照备库操作的数据越大，恢复回物理备库时间越长。 在快照备库期间，备库可以接受主库传输过来的日志，但是不能应用日志，需要处于物理备库的时候才可以应用。 当快照备库转回为物理备库时，所有在快照备库的操作被丢弃后，物理快照才会应用主库的redo数据。12.1 备库停止Redo apply应用-- 确保备库指定闪回区域 SQL> show parameter db_recov NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------- db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4560M -- 停止Redo应用 SQL> alter database recover managed standby database cancel; Database altered.12.2 备库到mount状态-- 确保数据库是否处于mount状态 SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE12.3 转换为快照备库SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. -- 查看 SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- MOUNTED SNAPSHOT STANDBY MAXIMUM PERFORMANCE UNPROTECTED12.4 快照备库到open状态-- 备库到open可读可写状态 SQL> alter database open; -- 查看 OPEN_MODE SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE12.5 快照备库对主库的日志接收当主库切换日志时，备库依然可以接受日志，只是不应用-- 主库切换日志 SQL> alter system archive log current; -- 主备都是相同的序号 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 88-- 查看日志alert日志位置 SQL> select name,value from V$diag_info where name in('Diag Trace','Diag Alert'); -- 主库alert信息 [oracle@klaus trace]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log Wed Aug 25 21:52:16 2021 ALTER SYSTEM ARCHIVE LOG Wed Aug 25 21:52:16 2021 Thread 1 advanced to log sequence 89 (LGWR switch) Current log# 2 seq# 89 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log Wed Aug 25 21:52:16 2021 Archived Log entry 67 added for thread 1 sequence 88 ID 0x5df896fc dest 1: Wed Aug 25 21:52:16 2021 TT00: Standby redo logfile selected for thread 1 sequence 89 for destination LOG_ARCHIVE_DEST_2 -- 备库alert信息 RFS[3]: Selected log 5 for thread 1 sequence 89 dbid 1576583420 branch 1048802686 Wed Aug 25 21:52:15 2021 Archived Log entry 16 added for thread 1 sequence 88 ID 0x5df896fc dest 1:12.6 快照备库可读写此时快照备库是可读写的 READ WRITE 状态SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE12.7 恢复为物理备库-- 备库到mount状态 shutdown immediate startup mount -- 恢复到物理备库 SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. -- 重新开启数据库 shutdown immediate startup -- 开启同步 SQL> alter database recover managed standby database using current logfile disconnect from session; -- 验证 SQL> select open_mode,database_role,protection_mode,protection_level from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE -- 切换日志 SQL> alter system archive log current; SQL> select max(sequence#) from v$archived_log;十三、DG开关机顺序--------------------------------------------------------------------------------- -- 启动顺序：先启动备库，后启动主库 -- 关闭顺序：先关闭主库，后关闭备库 --------------------------------------------------------------------------------- -- 1、正确开启顺序 -- 备库: SQL> STARTUP MOUNT SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 逻辑备库: SQL> STARTUP SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 主库: SQL> STARTUP --------------------------------------------------------------------------------- -- 2、正确关闭顺序 -- 主库: SQL> SHUTDOWN IMMEDIATE -- 备库: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- 逻辑备库: SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL> SHUTDOWN IMMEDIATE十四、DG维护相关