Oracle 18c RAC(cdb多租户)+ADG备库部署以及维护

Oracle 18c RAC+ADG备库部署以及维护

一、环境部署
二、DataGuard 数据库应用日志模式
三、维护使用
四、DataGuard主备切换
- 4.1 RAC数据库切换为Standby，单实例切换为Primary
- 4.2 RAC数据库切换为Primary，单实例数据库切换为Standby
五、取消DataGuard环境
- 5.1 Standby Database Alone
- 5.2 主库脱离DataGuard
六、其他特性

一、环境部署

1.1 主机配置

主库环境为Oracle 18.3 RAC 2 node，已开启cdb多租户，可通过RMAN的duplicate active database完成部署。

	Primary	Standby
DB Version	18.3.0	18.3.0
HOST IP	192.168.189.202 rac1/192.168.189.203 rac2	192.168.189.207 std
SCAN IP	192.168.189.206 rac-scan
DB_NAME	orcl	std
DB_UNIQUE_NAME	orcl	std
Instance_Name	orcl	std
ArchiveFile	+arch	/u01/arch/
DB Storage	ASM	FileSystem
ASM for DB files	+DATA/ORCL/DATAFILE/	/u01/app/oracle/oradata/std/datafile/
ASM for LOG files	+DATA/orcl/onlinelog	/u01/app/oracle/oradata/orcl/onlinelog
ASM for TEMP files	+DATA/orcl/tempfile/	/u01/app/oracle/oradata/std/tempfile/
ASM for pdbseed files	+DATA/ORCL/64A52F53AE053CDA9E80AED76/DATAFILE/	/u01/app/oracle/oradata/std/pdbseed/
ASM for pdb_temp files	+DATA/ORCL/80A758C64E9223BAE053CABDA8C09E9B/TEMPFILE/	/u01/app/oracle/oradata/std/pdbseed/
ORACLE_HOME	/u01/app/oracle/product/18.3.0/db_1/	/u01/app/oracle/product/18.3.0/db_1/
OS	CentOS 7.5	CentOS 7.5

1.2 实施步骤

部署之前可以修改备库的hostname以及hosts文件方便区分服务器

1、修改主库强制归档以及开启归档模式

[oracle@rac1:/home/oracle]$sqlplus / as sysdba SQL> startup mount; SQL> alter system set log_archive_dest_1='location=+arch' scope=spfile; System altered. SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. 开启数据库 SQL> alter database open;

2、修改主库初始化参数

当主库已开启force logging之后再进行如下操作 alter database force logging; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,std)' scope=both sid='*'; alter system set log_archive_dest_1='LOCATION=+arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile; alter system set LOG_ARCHIVE_DEST_2='SERVICE=std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std' scope=both sid='*'; alter system set fal_client='orcl' scope=both sid='*'; alter system set FAL_SERVER='std' scope=both sid='*'; alter system set standby_file_management=AUTO scope=both sid='*'; alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/datafile','+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/orcl/tempfile' scope=spfile sid='*'; alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*'; alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*'; alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile; 重启数据库 备注： log_archive_dest_1这里写主库arch路径 DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT参数需要已'备库路径','主库路径'一组为准则

参数解析：

LOG_ARCHIVE_CONFIG：列出主备库上的DB_UNIQUE_NAME 参数。默认情况下，定义该参数能确保主备库数据库能够互相识别对方 LOG_ARCHIVE_DEST_1：本地归档路径。Primary与Standby需要定义各自的归档路径 LOG_ARCHIVE_DEST_2：设置redo log的传输方式(sync or async)以及传输目标(即standby apply node)，当前节点设置的均为另一端数据库的db_unique_name，并且其中的service的值需要与tnsnames.ora别名相同。 FAL_CLIENT：（Fetch Archive Log）用来解决归档裂缝，定义的客户端（获取日志的客户端），参数取自Oracle Net Service Name FAL_SERVER：当主库转为备库的时候此参数会生效。通过网络向FAL_CLIENT发送缺失的日志，参数取自Oracle Net Service Name上述例子当rac转为备库时，会向std获取redo或者归档应用。 STANDBY_FILE_MANAGEMENT：当主库转为备库的时候此参数会生效。用来控制是否自动将Primary数据库增加表空间或数据文件的改动，传播到物理Standby数据库。AUTO：如果该参数值设置为AUTO，则Primary数据库执行的表空间创建操作也会被传播到物理Standby数据库上执行。 MANUAL：如果设置为MANUAL或未设置任何值（默认值是MANUAL），需要手工复制新创建的数据文件到物理Standby服务器。 DB_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一指明对映关系。注意: primary上的该参数仅在主备switch over后生效,格式应保持一致 LOG_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。定义主备log文件的存放路径转换

3、主库增加Standby redo log
如果2节点RAC主库每个thread redo log有N组，standby redo log则需要（N+1）*2组。
主库查询redo 日志组数，有2个thread，每个thread有2组日志，standby则需要6组

SQL> select group#,thread#,members,status from v$log; GROUP# THREAD# MEMBERS STATUS ---------- ---------- ---------- ---------------- 1 1 1 INACTIVE 2 1 1 CURRENT 3 2 1 INACTIVE 4 2 1 CURRENT

主库增加standby日志组： alter database add standby logfile thread 1 group 5 '+data' size 200m; alter database add standby logfile thread 1 group 6 '+data' size 200m; alter database add standby logfile thread 1 group 7 '+data' size 200m; alter database add standby logfile thread 2 group 8 '+data' size 200m; alter database add standby logfile thread 2 group 9 '+data' size 200m; alter database add standby logfile thread 2 group 10 '+data' size 200m;

确认主库加的standby日志是否已创建 SQL> select group#,thread# from v$standby_log; GROUP# THREAD# ---------- ---------- 5 1 6 1 7 1 8 2 9 2 10 2 6 rows selected.

4、在rac两个实例以及备库添加tnsnames字符串

[oracle@rac1:/u01/app/oracle/product/18.3.0/db_1/network/admin]$vi tnsnames.ora 主库tnsnamrs: orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) std = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = std) ) ) 备库tnsnames： orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) std = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = std) ) ) 备注：备库日志应用基于tnsnames连接

5、在主库上生成用于备库的参数文件

SQL> create pfile='initstd.ora' from spfile;

6、将主库的参数文件，密码文件拷贝到备库
注意：在18c rac数据库的密码文件已不在dbs目录，现存放于asm数据磁盘组内，可直接在asm使用cp命令拷贝到文件系统。
ASMCMD> pwd
+data/orcl/password

将/tmp目录的密码文件拷贝到备库dbs目录

对参数文件，密码文件重命名符合备库实例命名规范 [oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$mv orapworcl1 orapwstd [oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$mv orcl.ora initstd.ora

mkdir -p /u01/arch mkdir -p /u01/app/oracle/oradata/std mkdir -p /u01/app/oracle/oradata/std/datafile/ mkdir -p /u01/app/oracle/oradata/std/tempfile/ mkdir -p /u01/app/oracle/oradata/std/pdbseed/ mkdir -p /u01/app/oracle/admin/std/adump/ mkdir -p /u01/app/oracle/oradata/std/onlinelog/

备库pfile

[oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$vi initstd.ora *.audit_file_dest='/u01/app/oracle/admin/std/adump' *.audit_trail='NONE' *.compatible='18.0.0' *.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/oradata/std/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_unique_name='std' *.diagnostic_dest='/u01/app/oracle' *.log_archive_format='%t_%s_%r.arc' *.open_cursors=300 *.pga_aggregate_target=300m *.processes=1000 *.remote_login_passwordfile='exclusive' *.sga_target=8192m *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(std,orcl)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=std' *.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' *.log_archive_format='%t_%s_%r.arc' *.FAL_SERVER='orcl' *.fal_client='std' *.enable_pluggable_database=true *.FAL_SERVER='orcl' *.DB_FILE_NAME_CONVERT='+DATA/ORCL/DATAFILE/','/u01/app/oracle/oradata/std/datafile/','+DATA/orcl/tempfile/','/u01/app/oracle/oradata/std/tempfile/','+DATA/ORCL/64A52F53AE053CDA9E80AED76/DATAFILE/','/u01/app/oracle/oradata/std/pdbseed/','+DATA/ORCL/80A758C64E9223BAE053CABDA8C09E9B/TEMPFILE/','/u01/app/oracle/oradata/std/pdbseed/' *.LOG_FILE_NAME_CONVERT='+DATA/ORCL/ONLINELOG/','/u01/app/oracle/oradata/std/onlinelog/' *.standby_file_management=AUTO 备注： 1、log_archive_dest_1这里写备库arch路径 2、DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT参数中需要确保主库与备库的cdb目录以及pdbseed目录一一对应，并且数据文件目录尾部必须要以斜杠“/”结尾 3、因主库rac使用18c的单实例备库，enable_pluggable_database参数要改为true

8、在备库增加静态监听

[oracle@std:/u01/app/oracle/product/18.3.0/db_1/network/admin]$vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/18.3.0/db_1) (SID_NAME = std) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) ) ) lsnrctl start

9、启动备库数据库到nomount状态

修改环境变量，设置备库ORACLE_SID,本环境为std。然后启动到nomount

export ORACLE_SIS=std

SQL> startup nomount;

10、主库进入rman，准备使用rman duplicate特性完成备库的部署

在rac其中一个节点执行 主库tns：orcl 备库tns：std rman target sys/oracle@orcl auxiliary sys/oracle@std [oracle@rac1 ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@std

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Feb 1 15:25:25 2019 Version 18.3.0.0.0 Copyright © 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=) connected to auxiliary database: ORCL (not mounted) 执行脚本 run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate AUXILIARY channel c7 type disk; allocate AUXILIARY channel c8 type disk; allocate AUXILIARY channel c9 type disk; allocate AUXILIARY channel c10 type disk; allocate AUXILIARY channel c11 type disk; allocate AUXILIARY channel c12 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; }

执行过程输出日志：

using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=30 instance=orcl2 device type=DISK allocated channel: c2 channel c2: SID=405 instance=orcl1 device type=DISK allocated channel: c3 channel c3: SID=1171 instance=orcl2 device type=DISK allocated channel: c4 channel c4: SID=796 instance=orcl1 device type=DISK allocated channel: c5 channel c5: SID=409 instance=orcl2 device type=DISK allocated channel: c6 channel c6: SID=29 instance=orcl1 device type=DISK allocated channel: c7 channel c7: SID=1155 device type=DISK allocated channel: c8 channel c8: SID=12 device type=DISK allocated channel: c9 channel c9: SID=392 device type=DISK allocated channel: c10 channel c10: SID=773 device type=DISK allocated channel: c11 channel c11: SID=1156 device type=DISK allocated channel: c12 channel c12: SID=13 device type=DISK Starting Duplicate Db at 2019/02/01 16:09:37 current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/18.3.0/db_1/dbs/orapwstd' ; } executing Memory Script Starting backup at 2019/02/01 16:09:40 Finished backup at 2019/02/01 16:09:41 contents of Memory Script: { restore clone from service 'orcl' standby controlfile; } executing Memory Script Starting restore at 2019/02/01 16:09:42 channel c7: starting datafile backup set restore channel c7: using network backup set from service orcl channel c7: restoring control file channel c7: restore complete, elapsed time: 00:00:02 output file name=/u01/app/oracle/oradata/std/control01.ctl output file name=/u01/app/oracle/oradata/std/control02.ctl Finished restore at 2019/02/01 16:09:46 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/std/tempfile/temp.264."; set newname for tempfile 2 to "/u01/app/oracle/oradata/std/pdbseed/temp.268."; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/std/datafile/system.257."; set newname for datafile 3 to "/u01/app/oracle/oradata/std/datafile/sysaux.258."; set newname for datafile 4 to "/u01/app/oracle/oradata/std/datafile/undotbs1.259."; set newname for datafile 5 to "/u01/app/oracle/oradata/std/pdbseed/system.266."; set newname for datafile 6 to "/u01/app/oracle/oradata/std/pdbseed/sysaux.265."; set newname for datafile 7 to "/u01/app/oracle/oradata/std/datafile/users.260."; set newname for datafile 8 to "/u01/app/oracle/oradata/std/pdbseed/undotbs1.267."; set newname for datafile 9 to "/u01/app/oracle/oradata/std/datafile/undotbs2.269."; restore from nonsparse from service 'orcl' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/std/tempfile/temp.264. in control file renamed tempfile 2 to /u01/app/oracle/oradata/std/pdbseed/temp.268. in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 2019/02/01 16:09:54 channel c7: starting datafile backup set restore channel c7: using network backup set from service orcl channel c7: specifying datafile(s) to restore from backup set channel c7: restoring datafile 00001 to /u01/app/oracle/oradata/std/datafile/system.257. channel c8: starting datafile backup set restore channel c8: using network backup set from service orcl channel c8: specifying datafile(s) to restore from backup set channel c8: restoring datafile 00003 to /u01/app/oracle/oradata/std/datafile/sysaux.258. channel c9: starting datafile backup set restore channel c9: using network backup set from service orcl channel c9: specifying datafile(s) to restore from backup set channel c9: restoring datafile 00004 to /u01/app/oracle/oradata/std/datafile/undotbs1.259. channel c10: starting datafile backup set restore channel c10: using network backup set from service orcl channel c10: specifying datafile(s) to restore from backup set channel c10: restoring datafile 00005 to /u01/app/oracle/oradata/std/pdbseed/system.266. channel c11: starting datafile backup set restore channel c11: using network backup set from service orcl channel c11: specifying datafile(s) to restore from backup set channel c11: restoring datafile 00006 to /u01/app/oracle/oradata/std/pdbseed/sysaux.265. channel c12: starting datafile backup set restore channel c12: using network backup set from service orcl channel c12: specifying datafile(s) to restore from backup set channel c12: restoring datafile 00007 to /u01/app/oracle/oradata/std/datafile/users.260. channel c12: restore complete, elapsed time: 00:00:06 channel c12: starting datafile backup set restore channel c12: using network backup set from service orcl channel c12: specifying datafile(s) to restore from backup set channel c12: restoring datafile 00008 to /u01/app/oracle/oradata/std/pdbseed/undotbs1.267. channel c9: restore complete, elapsed time: 00:00:48 channel c9: starting datafile backup set restore channel c9: using network backup set from service orcl channel c9: specifying datafile(s) to restore from backup set channel c9: restoring datafile 00009 to /u01/app/oracle/oradata/std/datafile/undotbs2.269. channel c12: restore complete, elapsed time: 00:00:48 channel c9: restore complete, elapsed time: 00:00:26 channel c10: restore complete, elapsed time: 00:01:30 channel c11: restore complete, elapsed time: 00:01:38 channel c7: restore complete, elapsed time: 00:01:51 channel c8: restore complete, elapsed time: 00:01:51 Finished restore at 2019/02/01 16:11:45 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'orcl' archivelog from scn ; switch clone datafile all; } executing Memory Script Starting restore at 2019/02/01 16:11:55 channel c7: starting archived log restore to default destination channel c7: using network backup set from service orcl channel c7: restoring archived log archived log thread=1 sequence=23 channel c8: starting archived log restore to default destination channel c8: using network backup set from service orcl channel c8: restoring archived log archived log thread=1 sequence=24 channel c9: starting archived log restore to default destination channel c9: using network backup set from service orcl channel c9: restoring archived log archived log thread=1 sequence=25 channel c10: starting archived log restore to default destination channel c10: using network backup set from service orcl channel c10: restoring archived log archived log thread=2 sequence=20 channel c11: starting archived log restore to default destination channel c11: using network backup set from service orcl channel c11: restoring archived log archived log thread=2 sequence=21 channel c7: restore complete, elapsed time: 00:00:01 channel c8: restore complete, elapsed time: 00:00:01 channel c9: restore complete, elapsed time: 00:00:01 channel c10: restore complete, elapsed time: 00:00:00 channel c11: restore complete, elapsed time: 00:00:00 Finished restore at 2019/02/01 16:11:57 datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP= file name=/u01/app/oracle/oradata/std/datafile/system.257. datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP= file name=/u01/app/oracle/oradata/std/datafile/sysaux.258. datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP= file name=/u01/app/oracle/oradata/std/datafile/undotbs1.259. datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP= file name=/u01/app/oracle/oradata/std/pdbseed/system.266. datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP= file name=/u01/app/oracle/oradata/std/pdbseed/sysaux.265. datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP= file name=/u01/app/oracle/oradata/std/datafile/users.260. datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP= file name=/u01/app/oracle/oradata/std/pdbseed/undotbs1.267. datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP= file name=/u01/app/oracle/oradata/std/datafile/undotbs2.269. contents of Memory Script: { set until scn ; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 2019/02/01 16:11:57 starting media recovery archived log for thread 1 with sequence 24 is already on disk as file /u01/arch/1_24_.arc archived log for thread 1 with sequence 25 is already on disk as file /u01/arch/1_25_.arc archived log for thread 2 with sequence 20 is already on disk as file /u01/arch/2_20_.arc archived log for thread 2 with sequence 21 is already on disk as file /u01/arch/2_21_.arc archived log file name=/u01/arch/1_24_.arc thread=1 sequence=24 archived log file name=/u01/arch/2_20_.arc thread=2 sequence=20 archived log file name=/u01/arch/2_21_.arc thread=2 sequence=21 archived log file name=/u01/arch/1_25_.arc thread=1 sequence=25 media recovery complete, elapsed time: 00:00:01 Finished recover at 2019/02/01 16:12:01 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script deleted archived log archived log file name=/u01/arch/1_23_.arc RECID=1 STAMP= Deleted 1 objects deleted archived log archived log file name=/u01/arch/1_24_.arc RECID=2 STAMP= Deleted 1 objects deleted archived log archived log file name=/u01/arch/1_25_.arc RECID=3 STAMP= Deleted 1 objects deleted archived log archived log file name=/u01/arch/2_20_.arc RECID=4 STAMP= Deleted 1 objects deleted archived log archived log file name=/u01/arch/2_21_.arc RECID=5 STAMP= Deleted 1 objects Finished Duplicate Db at 2019/02/01 16:12:31 released channel: c1 released channel: c2 released channel: c3 released channel: c4 released channel: c5 released channel: c6 released channel: c7 released channel: c8 released channel: c9 released channel: c10 released channel: c11 released channel: c12 已完成备库的部署 duplicate之后数据库处于mount状态

SQL> create spfile from pfile; 创建备库spfile，并重启备库 SQL> startup;

12、 DataGuard部署完毕之后，查询备库的状态

SQL> select SWITCHOVER_STATUS,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM v$database; SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------------- ---------------- -------------------- --------------- NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY 默认应该为MAXIMIZE PERFORMANCE，备库可启用ADG或者RealTime实时应用日志模式，具体可使用如下步骤。

1.3 部署FAQ

1、使用from atcive database时，必须使用密码连接源端和目标端 2、在默认情况duplicate命令并不会应用归档文件对创建的standby数据库数据文件进行恢复，除非执行duplicate时指定了DORECOVER参数，那么duplicate会传输以及应用所有可应用的redologs。 (active模式下会传输归档到目标端） 在duplicate期间如果不加DORECOVER参数，rman只会还原控制文件，数据文件，不会包含归档日志。等到standby 创建完毕之后，才会从primary传输归档到standby。 3、如果是基于backup-based，只需去掉参数FROM ACTIVE DATABASE以及dorecover,把备份文件以及standby控制文件传到目标库，需要保证目标库与源库相同的rman备份目录。 示例： * 主库RMAN全备 * 创建standby控制文件 主库生成备库standby 控制文件 SQL>alter database create standby controlfile as '/home/oracle/standby.ctl'; * 主库执行命令 run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate AUXILIARY channel c4 type disk; allocate AUXILIARY channel c5 type disk; allocate AUXILIARY channel c6 type disk; DUPLICATE TARGET DATABASE FOR STANDBY; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; } 4、在duplicate传输期间，会将两个节点当前日志序号为起点，之后的归档传输到备库（如rac1 redo当前序号为100，只会传输99之后的归档日志） 5、如果遇到如下报错，说明密码文件在当前节点不存在，可在其中一个节点执行 RMAN-03009: failure of backup command on c1 channel at 02/01/2019 17:15:03 ORA-19505: failed to identify file "/u01/app/oracle/product/18.3.0/db_1/dbs/orapworcl1" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory 6、standby停库之后，如果再启动还是会从primary端传输redo 7、不能在standby端连接rman You cannot CONNECT RMAN to the standby database and then use DUPLICATE ... FOR STANDBY to create an additional standby database. To create additional standby databases, connect RMAN to the original primary database and run DUPLICATE ... FOR STANDBY. Note: Although you can use the DUPLICATE command to create a standby database, you cannot use this command to activate a standby database. When you connect RMAN to the standby database and the recovery catalog in which the primary database is already registered, RMAN recognizes the standby database and implicitly registers it. Do not attempt to use the REGISTER command for the standby database. https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta020.htm#CHDEDFFH 8、已测试如果源端与目标端数据文件、日志文件目录结构不同，nofilenamecheck参数无区别

二、DataGuard 数据库应用日志模式

2.1 Active DataGuard模式

SQL>startup; SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database disconnect from session; SQL> !ps -ef|grep mrp oracle 22470 1 0 21:51 ? 00:00:00 ora_mrp0_std MRP进程已启动

查询ADG是否已开启

SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE ----- -------------------- -------------------------- ORCL PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY 状态“READ ONLY WITH APPLY”即表示此时备库处于Read Only状态的同时可以接受主库传过来的日志进行恢复，以便达到备库可以即时查看到主库变化的目的。

2.2 Real-Time Apply实时应用模式

SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. 基于此模式备库会实时与主库数据同步。

2.3 备库停止日志应用

SQL> alter database recover managed standby database cancel; 执行完之后MRP进程会停止 SQL> !ps -ef|grep mrp oracle 22403 21996 0 21:50 pts/1 00:00:00 /bin/bash -c ps -ef|grep mrp oracle 22405 22403 0 21:50 pts/1 00:00:00 grep mrp

三、维护使用

3.1 打开与关闭RAC+DG数据库

打开数据库

先开备库，再开主库 SQL>STARTUP MOUNT; SQL>ALTER DATABASE open; $ lsnrctl start ---启用ADG SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; ---启用REAL TIME APPLY alter database recover managed standby database using current logfile disconnect; 启动主库 srvctl start database -d orcl

关闭数据库

先关主库，再关备库 关闭主库 srvctl stop database -d orcl 关闭备库 SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL>SHUTDOWN IMMEDIATE; $ lsnrctl stop

3.2 检查主备库的归档日志号

在主库检查日志号： SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +ARCH Oldest online log sequence 159 Next log sequence to archive 160 Current log sequence 160

在备库检查日志号 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/arch Oldest online log sequence 159 Next log sequence to archive 0 Current log sequence 160 检查主库、备库当前的Current log sequence是否都是一样的

3.3 检查备库的归档日志同步情况以及应用指标参数

在备库执行：

查询日志应用情况 SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='YES'; SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;  PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ---------- ARCH CLOSING 1 168 1 1 0 ARCH CLOSING 1 169 1 77 0 ARCH CONNECTED 0 0 0 0 0 ARCH CLOSING 2 96 1 31 0 RFS IDLE 0 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 2 97 4557 1 0 RFS IDLE 0 0 0 0 0 RFS IDLE 0 0 0 0 0 RFS IDLE 1 170 3917 1 0 MRP0 APPLYING_LOG 2 97 4555  0

查询备库日志应用的速率

select * from V$RECOVERY_PROGRESS;

也可通过动态性能视图，如下查询 SQL> select message from V$DATAGUARD_STATUS order by TIMESTAMP; 该动态性能视图显示出在主库上产生了多少重做日志数据,但是还没有被备库所应用 MESSAGE ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC2: Becoming the heartbeat ARCH ARC2: Becoming the active heartbeat ARCH ARC3: Archival started RFS[1]: Assigned to RFS process 1723 RFS[2]: Assigned to RFS process 1725 RFS[3]: Assigned to RFS process 1720 ARC3: Beginning to archive thread 1 sequence 100 (-) ARC3: Completed archiving thread 1 sequence 100 (0-0) Primary database is in MAXIMUM PERFORMANCE mode ARC0: Completed archiving thread 1 sequence 105 (0-0) ARC0: Beginning to archive thread 1 sequence 105 (-) RFS[5]: Assigned to RFS process 1742 RFS[4]: Assigned to RFS process 1740 Attempt to start background Managed Standby Recovery process MRP0: Background Managed Standby Recovery process started Managed Standby Recovery starting Real Time Apply Media Recovery Waiting for thread 2 sequence 18 观察可以发现是在等待thread2的日志应用

备注： 1、检查主备库之间的日志同步情况，如是是YES表示同步成功的，如果是NO表示没同步同成的日志,根据主备库与具体时间来确定。 2、实时应用MRP通常是APPLYING_LOG，ADG下通常是WAIT_FOR_LOG

3.4 查看主备库的模式

查看主备库性能模式

主库 SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------- ---------------- -------------------- -------------------- ORCL PRIMARY MAXIMUM PERFORMANCE READ WRITE 默认为最大性能模式

备库 SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------- ---------------- -------------------- -------------------- ORCL PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY 备库最大性能模式，已开启日志应用

检查应用模式(是否启用了实时应用)

查询v$archive_dest_status 视图，如果打开了实时应用，则recovery_mode为：MANAGED REAL TIME APPLY，例如： 备库： SQL> select recovery_mode from v$archive_dest_status where DEST_NAME='LOG_ARCHIVE_DEST_1'; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY 主库： SQL> select recovery_mode from v$archive_dest_status where DEST_NAME='LOG_ARCHIVE_DEST_2'; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY

3.5 主备库归档日志维护

vi /home/oracle/clean_arch.sh

#!/bin/bash export ORACLE_SID=orcl export ORACLE_HOME=/u01/app/oracle/product/18.3.0/db_1 export ORACLE_BASE=/u01/app/oracle time=`date +"20%y%m%d%H%M%S"` logdir="/home/oracle/arch_clean_log" find $logdir -type f -name "*.log" -mtime +7 -exec rm -rf { 
   } \; $ORACLE_HOME/bin/rman target / log=$logdir/archClean_$time.log <<EOF run{ crosscheck archivelog all; delete noprompt expired archivelog all; DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; } exit EOF

crontab 0 22 * * * /bin/bash /home/oracle/clean_arch.sh >/dev/null 2>&1

3.6 客户端连接数据库

在tnsnames.ora中增加以下配置连接RAC： orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.125)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) 在tnsnames.ora中增加以下配置连接单机备库： std = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = std) ) ) jdbc字符串 jdbcUrl=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))(FAILOVER=on)(LOAD_BALANCE=on))(CONNECT_DATA= (SERVICE_NAME=orcl)))

3.7 备库磁盘空间不足处理

当主库创建新的数据文件，然而备库没有空间时，应当先扩容备库磁盘，然后将备库的文件管理改成手动管理，使用命令在备库创建对应的数据文件。

1、在主库创建新的表空间test，并且增加数据文件7，8

SQL> select file#,name from v$datafile;

2、备库查询数据文件

SQL> select file#,name from v$datafile; FILE# NAME ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/orcl/datafile/system.273. 2 /u01/app/oracle/oradata/orcl/datafile/sysaux.272. 3 /u01/app/oracle/oradata/orcl/datafile/undotbs1.271. 4 /u01/app/oracle/oradata/orcl/datafile/undotbs2.269. 5 /u01/app/oracle/oradata/orcl/datafile/users.268. 6 /u01/app/oracle/oradata/orcl/datafile/admin.260. 7 /u01/app/oracle/product/18.3.0/db_1/dbs/UNNAMED00007 8 /u01/app/oracle/product/18.3.0/db_1/dbs/UNNAMED00008 可以看到备库的数据文件，并没有真正的创建，只是创建了临时文件。

3、等备库磁盘空间充足时（新挂1块盘），手动创建数据文件对应主库

--默认为自动管理，改为手动管理 SQL> alter system set standby_file_management='MANUAL';

--由于u01空间已不够，可将数据文件创建到u02 SQL> alter database create datafile '/u01/app/oracle/product/18.3.0/db_1/dbs/UNNAMED00007' as '/u02/oradata/test_1.dbf'; SQL> alter database create datafile '/u01/app/oracle/product/18.3.0/db_1/dbs/UNNAMED00008' as '/u02/oradata/test_2.dbf';

4、将备库数据文件管理改回自动

SQL> alter system set standby_file_management='AUTO';

SQL> alter database recover managed standby database using current logfile disconnect;

6、检查主备库新的表空间数据是否同步

备注：系统做同步时，会先建临时文件，名称如下： /u01/app/oracle/product/18.3.0/dbhome_1/dbs/UNNAMED000XX 如果空间足够，就会成功同步，并将文件更名为正确的名称和路径。

3.8 重建备库

如果备库损坏，可以将其删除之后再次重建。
1、删除备库
此方法只删除备库的数据文件，日志文件，控制文件，临时文件。

SQL> shutdown immediate; SQL> startup restrict mount; （只有sysdba用户可以连） SQL> select name from v$database; NAME --------- ORCL SQL> drop database;

2、重新用备库的pfile文件将备库启动到nomount

3、主库重新用rman duplicate创建备库

四、DataGuard主备切换

4.1 RAC数据库切换为Standby，单实例切换为Primary

主库切换为备库
1 查询主库状态

SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY TO STANDBY 备注：当出现session active的时候表示还有活动的session

2 查询备库状态

SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY TO PRIMARY 正常为 TO PRIMARY或session active

3 关闭主库RAC另一节点

[root@orcl1 ~]$ srvctl stop instance -d orcl -n orcld2

4 查询备库是否启用最大性能模式

SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

5 查询备库日志是否全部应用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO'; no rows selected

6 主库切换为备库（在RAC节点1执行）

SQL>alter database commit to switchover to physical standby; Database altered; 或session active状态执行： SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. 此时数据库可能会停止，可手动mount

7 查询新备库状态（RAC 节点1）

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY RECOVERY NEEDED

备库切换为主库

1 查询备库状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY TO PRIMARY 说明：SWITCHOVER_STATUS正常应该是to_primary或session active

2 查询备库日志应用情况

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO'; no rows selected 说明：如果日志没有完全应用。开启日志应用 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. 再次查询确保没有应用日志记录 SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO'; no rows selected

3 备库切换为主库

SQL> alter database commit to switchover to primary; Database altered.

4 查询新主库状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY NOT ALLOWED

5 打开新的主库（std）

SQL> alter database open;

6、再次查看新主库状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY TO STANDBY

在新的备库RAC环境（原来的主库）开启日志应用

1 在其中一个节点运行，开启实时应用

SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 MRP0 APPLYING_LOG 1 217 4614  0 RFS IDLE 1 217 4615 1 0 RFS IDLE 0 0 0 0 0 7 rows selected. SQL> !ps -ef|grep mrp oracle 29977 1 0 20:19 ? 00:00:02 ora_mrp0_orcl1 oracle 33642 32487 0 21:06 pts/1 00:00:00 /bin/bash -c ps -ef|grep mrp oracle 33644 33642 0 21:06 pts/1 00:00:00 grep mrp

节点2

SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ---------- ARCH CLOSING 1 206 10240 1861 0 ARCH CLOSING 1 215 1 696 0 ARCH CONNECTED 0 0 0 0 0 ARCH CLOSING 1 216 2048 1053 0 SQL> !ps -ef|grep mrp oracle 53581 52552 0 21:06 pts/0 00:00:00 /bin/bash -c ps -ef|grep mrp oracle 53583 53581 0 21:06 pts/0 00:00:00 grep mrp

在切换完之后，不一定日志会马上应用，可等待日志应用，查看告警日志
新的备库（RAC环境）只会在其中一个实例上运行MRP进程，如果运行另外一个节点，当前节点MRP会中断

4.2 RAC数据库切换为Primary，单实例数据库切换为Standby

主库（std）切换为备库
1 查询当前主库状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PRIMARY TO STANDBY READ WRITE

2 查询备库（rac）日志是否全部应用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

3 主库（std）切换成备库并启动到mount状态

SQL> alter database commit to switchover to physical standby; SQL> startup mount;

4 查询新备库（std）状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY RECOVERY NEEDED MOUNTED

备库（rac）切换为主库
1 关闭备库（rac）另一个实例

[root@rac2 ~]# srvctl stop instance -d orcl -n rac2

2 查询备库状态

SQL> select database_role,switchover_status,open_mode from gv$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY

3 将备库切换为主库

SQL> alter database commit to switchover to primary; Database altered.

4 查询新主库状态

SQL> select database_role,switchover_status,open_mode from gv$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PRIMARY NOT ALLOWED MOUNTED

5 启动新主库RAC

SQL> alter database open; 同时开启另一节点

新备库（std）开启日志应用

1 开启实时应用

SQL> alter database recover managed standby database using current logfile disconnect; Database altered.

2 在备库查询日志是否在应用

SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;  PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 ARCH CONNECTED 0 0 0 0 0 MRP0 WAIT_FOR_LOG 1 221 0 0 0 SQL> !ps -ef|grep mrp oracle 3371 1 0 20:35 ? 00:00:00 ora_mrp0_std oracle 3544 1549 0 20:38 pts/1 00:00:00 /bin/bash -c ps -ef|grep mrp oracle 3546 3544 0 20:38 pts/1 00:00:00 grep mrp 确保mrp 在applying

3 查询下主库状态

SQL> select database_role,switchover_status,open_mode from gv$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PRIMARY FAILED DESTINATION READ WRITE PRIMARY FAILED DESTINATION READ WRITE 此时目标参数异常 SQL> select message from V$DATAGUARD_STATUS order by TIMESTAMP; ARCH shutting down ARC4: Archival stopped Check whether the listener is up and running. Error 12541 received logging on to the standby PING[ARC2]: Heartbeat failed to connect to standby 'std'. Error is 12541. 此时心跳异常

4 检查备库监听是否开启

备库监听未开启，启动监听 lsnrctl start

5 再次查询主库状态

SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------- ---------------- -------------------- -------------------- ORCL PRIMARY MAXIMUM PERFORMANCE READ WRITE 状态正常

6 测试主备库数据是否同步

五、取消DataGuard环境

5.1 Standby Database Alone

如果主库已完全不能使用，可将备库脱离dataguard环境，成为独立数据库。
1、修改备库的pfile
去掉dg环境参数

LOG_ARCHIVE_CONFIG DB_FILE_NAME_CONVERT LOG_FILE_NAME_CONVERT LOG_ARCHIVE_DEST_STATE_2 STANDBY_FILE_MANAGEMENT FAL_SERVER FAL_CLIENT

2、启动数据库脱离dg环境

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP

3、查看数据库状态

SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE ---------------- -------------------- -------------------- PRIMARY NOT ALLOWED READ WRITE 已脱离dg

4、测试备库读写

参考MOS ID .1

5.2 主库脱离DataGuard

当主库不需要与备库保持数据同步，可以将主库有关dg的初始化参数移除，使主库完全脱离DataGuard环境。
1、查询确认主库是否是最大性能模式
如果不是最大性能模式，需要使用命令修改

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

2、移除(S)PFILE 参数文件dg参数

LOG_ARCHIVE_CONFIG DB_FILE_NAME_CONVERT LOG_FILE_NAME_CONVERT LOG_ARCHIVE_DEST_n pointing to the Standby Database and valid for STANDBY_LOGFILES LOG_ARCHIVE_DEST_STATE_n DG_BROKER_START DG_BROKER_CONFIG_FILE1 DG_BROKER_CONFIG_FILE2 STANDBY_ARCHIVE_DEST STANDBY_FILE_MANAGEMENT FAL_SERVER FAL_CLIENT

3、删除主库所有的Standby Redolog Groups

SQL> SELECT GROUP# FROM V$STANDBY_LOG; SQL> alter database drop standby logfile group 3;

4、删除 Data Guard Broker Configuration 配置文件

SQL> show parameter DG_BROKER_CONFIG; $ORACLE_HOME/dbs/dr1<ORACLE_SID>.dat and $ORACLE_HOME/dbs/dr2<ORACLE_SID>.dat

参考MOS ID .1

六、其他特性

6.1 日志延时应用

在log_archive_dest_2中加入参数delay后，备库会延时应用日志，如下：

alter system set LOG_ARCHIVE_DEST_2='SERVICE=std DELAY=5 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std';

备注：如果启用了实时应用需要更改为其他模式，延时才会生效。

案例：如果主库某张表删除可以从备库逻辑导出，然后再导入到主库

[oracle@orcl:/u01/arch_std]$expdp admin/oracle ORA-31626: job does not exist ORA-31633: unable to create master table "ADMIN.SYS_EXPORT_SCHEMA_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1038 ORA-16000: database open for read-only access 无法使用数据泵导出

可以使用exp进行逻辑导出

[oracle@orcl:/u01/arch]$exp admin/oracle file=/u01/arch/admin.dmp owner=admin log=/u01/arch/admin.log About to export specified users ... . . exporting table TEST 1 rows exported Export terminated successfully without warnings.

6.2 Snapshot Standby Database

使用该特性可以将备库转为snapshot database，并且可以读写，此时备库不再接受主库的日志应用，但是主库仍然会传输归档日志到备库。拥有此特性可以在备库中进行功能测试，当测试完之后，备库可以恢复到dataguard standby环境。

6.2.1 开启Snapshot Standby Database

1、设置standby database的闪回区

SQL> alter system set db_recovery_file_dest_size=5g; SQL> alter system set db_recovery_file_dest='/u01/flashrecovery'; SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/flashrecovery db_recovery_file_dest_size big integer 5G

2、启动数据库到mount

SQL> shutdown immediate; SQL> startup mount;

3、开启snapshot

SQL> alter database convert to snapshot standby;

4、打开数据库

SQL> alter database open;

5、查看备库状态

SQL> select PROTECTION_MODE,SWITCHOVER#,open_mode,FLASHBACK_ON from v$database; PROTECTION_MODE SWITCHOVER# OPEN_MODE FLASHBACK_ON -------------------- ----------- -------------------- ------------------ MAXIMUM PERFORMANCE  READ WRITE RESTORE POINT ONLY

SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------- ---------------- -------------------- -------------------- ORCL SNAPSHOT STANDBY MAXIMUM PERFORMANCE READ WRITE

SQL> select process,status from v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CONNECTED ARCH CONNECTED ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE

6.2.2 取消Snapshot Standby Database

1、数据库启动到mount状态

SQL> shutdown immediate; SQL> startup mount;

2、关闭snapshot特性

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

3、重启数据库

SQL> shutdown immediate; SQL> startup;

4、开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

5、查看数据库状态

SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE; NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE --------- ---------------- -------------------- -------------------- ORCL PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY