代码语言：javascript

复制

[oracle@dg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 29 10:43:05 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO 3 SINGLE READ WRITE NO 5 GYLRS MOUNTED 6 JY MOUNTED 7 TA401 MOUNTED

主备端添加以下内容

代码语言：javascript

复制

[oracle@dg1 ~]$ cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 192.168.xx.175 dg1 192.168.xx.177 dg2 [oracle@dg1 ~]$

代码语言：javascript

复制

SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FORCE_LOGGING --------- ------------ --------------------------------------- NEWCDB ARCHIVELOG NO SQL> alter database force logging; Database altered. SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FORCE_LOGGING --------- ------------ --------------------------------------- NEWCDB ARCHIVELOG YES

代码语言：javascript

复制

SQL> col member format a50 SQL> select a.GROUP#,BYTES/1024/1024STATUS,TYPE,MEMBER from v$log a,v$logfile b where a.GROUP#=b.GROUP#; GROUP# STATUS TYPE MEMBER ---------- ---------- ------- ------------------------------------------------- 3 200 ONLINE /oracle/app/oracle/oradata/singledb/redo03.log 2 200 ONLINE /oracle/app/oracle/oradata/singledb/redo02.log 1 200 ONLINE /oracle/app/oracle/oradata/singledb/redo01.log ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/app/oracle/oradata/singledb/redo04.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/app/oracle/oradata/singledb/redo05.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oracle/app/oracle/oradata/singledb/redo06.log') size 200M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/oracle/app/oracle/oradata/singledb/redo07.log') size 200M;

配置listener.ora文件，欧博官网添加singledb的静态监听条目

代码语言：javascript

复制

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = singledb) (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1/) (SID_NAME = singledb) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521)) ) ADR_BASE_LISTENER = /oracle/app/oracle 配置tnsnames.ora文件，添加备端的监听连接串 # Generated by Oracle configuration tool LISTENER_SINGLEDB = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521)) SINGLEDB_PD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = SINGLEDB) ) ) SINGLEDB_ST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = singledb) ) )

配置listener.ora文件，添加singledb的静态监听条目

代码语言：javascript

复制

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1/) (SID_NAME = singledb) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521)) ) ADR_BASE_LISTENER = /oracle/app/oracle 配置tnsnames.ora文件，添加备端的监听连接串 LISTENER_SINGLEDB = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521)) SINGLEDB_PD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = SINGLEDB) ) ) SINGLEDB_ST= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521)) (CONNECT_DATA = (sid = singledb) ) )

备端tnsping主端

代码语言：javascript

复制

[oracle@oracle ~]$ tnsping singledb_pd

主端tnsping备端

代码语言：javascript

复制

[oracle@12cr2 ~]$ tnsping ingledb_st

代码语言：javascript

复制

SQL> create pfile='/home/oracle/pfile.ora' from spfile; scp /home/oracle/pfile.ora 192.168.xx.177:`pwd` scp orapwnewcdb 192.168.xx.177:`pwd`

添加db_unique_name，要不同于主库

代码语言：javascript

复制

[oracle@dg1 ~]$ cat pfile.ora singledb.__data_transfer_cache_size=0 singledb.__db_cache_size=1191182336 singledb.__inmemory_ext_roarea=0 singledb.__inmemory_ext_rwarea=0 singledb.__java_pool_size=16777216 singledb.__large_pool_size=33554432 singledb.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment singledb.__pga_aggregate_target=83886080 singledb.__sga_target=1593835520 singledb.__shared_io_pool_size=0 singledb.__shared_pool_size=335544320 singledb.__streams_pool_size=0 *.audit_file_dest='/oracle/app/oracle/admin/singledb/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/oracle/app/oracle/oradata/singledb/control01.ctl','/oracle/app/oracle/fast_recovery_area/singledb/control02.ctl' *.db_block_size=8192 *.db_create_file_dest='/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs' *.db_name='singledb' *.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/singledb' *.db_recovery_file_dest_size=12780m *.db_unique_name='singledb_pd' *.diagnostic_dest='/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=singledbXDB)' *.enable_pluggable_database=true *.memory_target=1600m *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1585446912 *.undo_tablespace='UNDOTBS1'

添加内容：

代码语言：javascript

复制

--DB_FILE_NAME_CONVERT=主库数据文件地址，备库数据文件地址。用于主、备库数据文件的路径不一致的情况下，欧博路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。 *.db_unique_name='singledb_st' *.LOG_FILE_NAME_CONVERT=('/oracle/app/oracle/oradata/singledb','/oracle/app/oracle/oradata/singledb_st')

如下是搭建ADG的常用参数，含义如下：

代码语言：javascript

复制

DB_UNIQUE_NAME=数据库的唯一名称。 LOG_ARCHIVE_CONFIG='DG_CONFIG=(主库的db_unique_name,备库的db_unique_name)' LOG_ARCHIVE_DEST_n=日志归档的地址，最少需要两个，LOG_ARCHIVE_DEST_1指向主库，欧博娱乐LOG_ARCHIVE_DEST_2指向备库。 FAL_SERVER=指向主库的网络服务名 FAL_CLIENT=指向备库的网络服务名 DB_FILE_NAME_CONVERT=主库数据文件地址，备库数据文件地址。用于主、备库数据文件的路径不一致的情况下，路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。 LOG_FILE_NAME_CONVERT=主库联机日志文件地址，备库联机日志文件地址。用于主、备库联机日志文件的路径不一致的情况下，欧博allbet路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。 STANDBY_FILE_MANAGEMENT=AUTO，当主库添加或减少数据文件时会自动同步到备库而不需要手动干预。

修改主库参数

代码语言：javascript

复制

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(singledb_pd, singledb_st)' scope=both; alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=singledb_pd' scope=both; alter system set LOG_ARCHIVE_DEST_2='SERVICE=singledb_st lgwr async affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=singledb_st' scope=both; alter system set FAL_SERVER=singledb_standby scope=both; alter system set FAL_CLIENT=singledb_primary scope=both; alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

修改备库参数

代码语言：javascript

复制

alter system set DB_UNIQUE_NAME='singledb_st' scope=both; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(singledb_pd,singledb_st)' scope=both; alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=singledb_st' scope=both; alter system set LOG_ARCHIVE_DEST_2='SERVICE=singledb_primary lgwr async affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=singledb_st scope=both; alter system set FAL_SERVER=singledb_primary scope=both; alter system set FAL_CLIENT=singledb_standby scope=both; alter system set DB_FILE_NAME_CONVERT='+MKMDB_DATA/mkmdb/datafile/ ','+DATA/mkmdbstd/datafile/' scope=both; alter system set LOG_FILE_NAME_CONVERT='+MKMDB_ARCH/MKMDB/ONLINELOG/','+DATA/MKMDBSTD/ONLINELOG/', '+MKMDB_DATA/MKMDB/ONLINELOG/', '+DATA/MKMDBSTD/ONLINELOG/' scope=both; alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

192.168.xx.175配置

代码语言：javascript

复制

SQL> show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_syslog_level string commit_logging string db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string enable_ddl_logging boolean FALSE log_archive_config string DG_CONFIG=( singledb_pd, singl edb_st) log_archive_dest string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_ DEST VALID_FOR=(ALL_LOGFILES,A LL_ROLES) DB_UNIQUE_NAME=singl edb_pd log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string SERVICE=singledb_pd lgwr async affirm VALID_FOR=(ONLINE_LOGF ILES,PRIMARY_ROLE) DB_UNIQUE_N AME=singledb_st log_archive_dest_20 string

192.168.xx.177配置

代码语言：javascript

复制

SQL> show parameter log_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_syslog_level string commit_logging string db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string enable_ddl_logging boolean FALSE log_archive_config string DG_CONFIG=(singledb_pd,singled b_st) log_archive_dest string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_ DEST VALID_FOR=(ALL_LOGFILES,A LL_ROLES) DB_UNIQUE_NAME=singl edb_st log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string SERVICE=singledb_st lgwr async affirm VALID_FOR=(ONLINE_LOGF ILES,PRIMARY_ROLE) DB_UNIQUE_N AME=singledb_pd log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string

备端创建参数文件所需目录

代码语言：javascript

复制

mkdir /archive chown oracle:oinstall /archive/ mkdir -p /oracle/app/oracle/admin/singledb/adump mkdir -p /oracle/app/oracle/oradata/singledb mkdir -p /oracle/app/oracle/oradata/singledb_st

启动备库到nomount状态

代码语言：javascript

复制

[oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 23:59:36 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/home/oracle/pfile.ora'; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 8798312 bytes Variable Size 322965400 bytes Database Buffers 503316480 bytes Redo Buffers 3780608 bytes SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string singledb_st

测试主备库之间的连通性

代码语言：javascript

复制

sqlplus sys/password@std as sysdba sqlplus sys/password@singledb_pd as sysdba sqlplus sys/password@std as sysdba sqlplus sys/password@singledb_pd as sysdba

备库执行RMAN duplicate

代码语言：javascript

复制

[oracle@oracle ~]$ rman target sys/password@singledb_pd auxiliary sys/password@std nocatalog Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 25 00:12:14 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: NEWCDB (DBID=36700136) using target database control file instead of recovery catalog connected to auxiliary database: NEWCDB (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck;

代码语言：javascript

复制

duplicate 完成之后，备库是mount的。 SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED -- 备库开启时实时日志应用 SQL> alter database recover managed standby database disconnect from session;

代码语言：javascript

复制

SQL> alter system switch logfile; //主库 SQL> select max(sequence#) from v$archived_log; SQL> select process, client_process, sequence#, status from v$managed_standby;

代码语言：javascript

复制

SQL> select flashback_on from v$database; --查看备库是否处于flashback状态 SQL> alter database recover managed standby database cancel; --停止日志应用 SQL> alter database flashback on; ---开启flashback状态（需要开启ADG模式） SQL> alter database recover managed standby database disconnect from session; --开启redo应用

如果要开启Active Data Guard，按以下步骤操作即可：

代码语言：javascript

复制

(1)取消管理备用数据库Redo Apply SQL> alter database recover managed standby database cancel; (2)然后以只读方式打开数据库 SQL> alter database open; (3)重新开始Redo Apply SQL> alter database recover managed standby database disconnect from session; (4)查看备库打开模式 SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY

参考文章：https://cloud.tencent.com/developer/article/1591253

停止standby（备库） 数据库redo日志的应用：

代码语言：javascript

复制

代码语言：javascript

复制

sql> alter database recover managed standby database cancel ; 停止主库数据库：sql> shutdown immediate; 停止备用数据库：sql> shutdown immediate;

代码语言：javascript

复制

代码语言：javascript

复制

注：一定要先启动备库 备库： [oracle@PD orcl]$ lsnrctl start 主备监听需在启动数据库前启动 [oracle@PD orcl]$ sqlplus / as sysdba SQL> startup nomount ORACLE instance started. Total System Global Area 893562880 bytes Fixed Size 2218512 bytes Variable Size 587204080 bytes Database Buffers 297795584 bytes Redo Buffers 6344704 bytes SQL> startup nomount ; ORA-01081: cannot start already-running ORACLE - shut it down first SQL> alter database mount standby database; ---您可以指定MOUNT STANDBY DATABASE来挂载物理备用数据库。关键字STANDBY DATABASE是可选的，因为Oracle数据库会自动确定要装载的数据库是主数据库还是备用数据库。一旦执行该语句，备用实例就可以从主实例接收重做数据。 Database altered. 启动备库数据库 SQL> alter database open read only; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; ---使用using current logfile参数，可以实时同步主库的更新。（在没有延时的情况下，实时传输redo日志。） SQL> alter database recover managed standby database disconnect from session; --开启redo应用, Database altered. 主库： [oracle@PD orcl]$ lsnrctl start --启动监听 [oracle@PD orcl]$ sqlplus / as sysdba SQL> startup --启动数据库服务 ## DG三种模式 最大保护模式（Maximum protection） --性能不佳 ``` alter database set standby database to maximize protection; Primary Database上的每个事务的Redo日志必须在本地和Standby Database上都写入日志文件后才能提交，如果不能写入到Standby Database，Primary Database就会自动关闭（挂起）以防止数据丢失。 ``` 最大可用性（Maximum Availability） ``` Primary Database每个事务的Redo日志要写到本地和Standby Database中才能提交。 这个和最大保护模式不同的是，如果写入到Standby Database失败，Primary Database不会自动关闭。这时Primary Database会自动转换为Maximum Performance模式，等待问题解决并且Standby Database再次和Primary Database同步之后，Primary Database会自动的转换为Maximum Availability。 这种模式要求Standby Database必须配置Standby Redo log，而Primary Database必须配置为LGWR、SYNC、AFFIRM方式归档。 ``` 最大性能（Maximum Performance） ``` 这个模式是缺省模式，他更加侧重对Primary Database的可用性不造成任何影响。 Primary Database上的事务的Redo日志只要写到本地日志文件就可以提交，不必等待到Standby Database的传递完成。 Primary Database的Redo流可以异步的发送到Standby Database。 这种模式通过LGWR ASYNC或者ARCH实现，Standby Database也不要求使用Standby Redo Log。 ```

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-10，如有侵权请联系 cloudcommunity@tencent.com 删除

网络安全

数据库

sql

本文分享自 数据和云 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。