DG的备库有2种类型，物理和逻辑。今天主要分享物理备库的搭建过程
一、环境准备
| dg1(主库) | dg2(备库) | |
| ip | 192.168.59.249 | 192.168.59.250 |
| db_name | orcl11g | orcl11g |
| db_unique_name | orcl11g | orcl11gdg |
| ORACLE_BASE | /u01/app/oracle | /u01/app/oracle |
| ORACLE_HOME | /u01/app/oracle/product/11.2.0/db_1 | /u01/app/oracle/product/11.2.0/db_1 |
| 主机名 | primarynode | standbynode |
| 数据库版本 | 11.2.0.4 | 11.2.0.4 |
| 操作系统 | centos7 | centos7 |
| instance_name | orcl11g | orcl11g |
| service_name | orcl11g | orcl11g |
| 数据库安装 | 安装数据库软件，创建监听和数据库实例 | 安装数据库软件 |
二、安装数据库
1、dg1：
安装数据库软件，创建监听和数据库实例
执行一键安装脚本
安装脚本可以参考我的这篇文章：https://mp.weixin.qq.com/s/2B_QyFGOAEhb_0xydog0zw
2、dg2：
只安装数据库软件
执行以下脚本
#!/bin/bash#一键安装oracle数据库start_time=$(date +%s)#修改主机名hostnamectl set-hostname myoracle#添加主机名与IP对应记录public_ip=$(hostname -I| grep -o -e '[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}' |head -n 1)node_name=$(hostname)echo -e "${public_ip} ${node_name}">> /etc/hostscat /etc/hosts#关闭Selinuxsed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/configsetenforce 0#关闭防火墙systemctl stop firewalldsystemctl disable firewalld# 获取当前所在目录位置current_dir=$(pwd)echo "当前所在目录位置: $current_dir"# 目标路径target_dir="/soft"# 检查目标路径是否存在，如果不存在则创建if [ ! -d "$target_dir" ]; thenmkdir -p "$target_dir"echo "已创建目录: $target_dir"fi# 移动当前目录下的所有文件到目标路径mv $current_dir/* $target_direcho "已将当前目录下所有文件移动至 $target_dir"#添加离线yum源cd /softtar -xvf oracle_repo.tar.gzcd /soft/my_oracle_reporpm -ivh *.rpm --nodeps --force#在线yum源cd /etc/yum.repos.d/rm -rf ./*sleep 20wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo#添加用户组及用户groupadd oinstallgroupadd dbauseradd -g oinstall -G dba oracleecho oracle | passwd --stdin oraclechmod 644 /etc/sysctl.conf#修改环境变量cat <<EOF >>/etc/sysctl.conffs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576EOFsysctl -pchmod 644 /etc/security/limits.confcat <<EOF >>/etc/security/limits.conforacle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536oracle soft stack 10240oracle hard stack 10240EOFchmod 644 /etc/pam.d/logincat <<EOF >>/etc/pam.d/loginsession required /lib64/security/pam_limits.sosession required pam_limits.soEOFchmod 644 /etc/profilecat <<EOF >>/etc/profileif [ $USER = "oracle" ]; thenif [ $SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fifiEOFsource /etc/profilemkdir -p /u01/app/chown -R oracle:oinstall /u01/app/chmod -R 775 /u01/app/mkdir -p /usr/local/srccd /usr/local/src#解压oracle安装包mv /soft/*.zip /usr/local/src/cd /usr/local/src/unzip p13390677_112040_Linux-x86-64_1of7.zipunzip p13390677_112040_Linux-x86-64_2of7.zipmkdir /home/oracle/etcchown -R oracle.oinstall /home/oracle/etccp /usr/local/src/database/response/* /home/oracle/etc/chmod 777 /home/oracle/etc/*.rspchown -R oracle:oinstall /home/oracle/etc/cat <<EOF >/home/oracle/etc/db_install.rsp###################################################################### Copyright(c) Oracle Corporation 1998,2013. All rights reserved.#### #### Specify values for the variables listed below to customize #### your installation. #### #### Each variable is associated with a comment. The comment #### can help to populate the variables with the appropriate #### values. #### #### IMPORTANT NOTE: This file contains plain text passwords and #### should be secured to have read permission only by oracle user #### or db administrator who owns this installation. #### #######################################################################------------------------------------------------------------------------------# Do not change the following system generated value.#------------------------------------------------------------------------------oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0#------------------------------------------------------------------------------# Specify the installation option.# It can be one of the following:# - INSTALL_DB_SWONLY# - INSTALL_DB_AND_CONFIG# - UPGRADE_DB#-------------------------------------------------------------------------------oracle.install.option=INSTALL_DB_SWONLY#-------------------------------------------------------------------------------# Specify the hostname of the system as set during the install. It can be used# to force the installation to use an alternative hostname rather than using the# first hostname found on the system. (e.g., for systems with multiple hostnames# and network interfaces)#-------------------------------------------------------------------------------ORACLE_HOSTNAME=myoracle#-------------------------------------------------------------------------------# Specify the Unix group to be set for the inventory directory.#-------------------------------------------------------------------------------UNIX_GROUP_NAME=oinstall#-------------------------------------------------------------------------------# Specify the location which holds the inventory files.# This is an optional parameter if installing on# Windows based Operating System.#-------------------------------------------------------------------------------INVENTORY_LOCATION=/u01/app/oraInventory#-------------------------------------------------------------------------------# Specify the languages in which the components will be installed.## en : English ja : Japanese# fr : French ko : Korean# ar : Arabic es : Latin American Spanish# bn : Bengali lv : Latvian# pt_BR: Brazilian Portuguese lt : Lithuanian# bg : Bulgarian ms : Malay# fr_CA: Canadian French es_MX: Mexican Spanish# ca : Catalan no : Norwegian# hr : Croatian pl : Polish# cs : Czech pt : Portuguese# da : Danish ro : Romanian# nl : Dutch ru : Russian# ar_EG: Egyptian zh_CN: Simplified Chinese# en_GB: English (Great Britain) sk : Slovak# et : Estonian sl : Slovenian# fi : Finnish es_ES: Spanish# de : German sv : Swedish# el : Greek th : Thai# iw : Hebrew zh_TW: Traditional Chinese# hu : Hungarian tr : Turkish# is : Icelandic uk : Ukrainian# in : Indonesian vi : Vietnamese# it : Italian## all_langs : All languages## Specify value as the following to select any of the languages.# Example : SELECTED_LANGUAGES=en,fr,ja## Specify value as the following to select all the languages.# Example : SELECTED_LANGUAGES=all_langs#------------------------------------------------------------------------------SELECTED_LANGUAGES=en#------------------------------------------------------------------------------# Specify the complete path of the Oracle Home.#------------------------------------------------------------------------------ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1#------------------------------------------------------------------------------# Specify the complete path of the Oracle Base.#------------------------------------------------------------------------------ORACLE_BASE=/u01/app/oracle#------------------------------------------------------------------------------# Specify the installation edition of the component.## The value should contain only one of these choices.# - EE : Enterprise Edition# - SE : Standard Edition# - SEONE : Standard Edition One# - PE : Personal Edition (WINDOWS ONLY)#------------------------------------------------------------------------------oracle.install.db.InstallEdition=EE#------------------------------------------------------------------------------# This variable is used to enable or disable custom install and is considered# only if InstallEdition is EE.## true : Components mentioned as part of 'optionalComponents' property# are considered for install.# false : Value for 'optionalComponents' is not considered.#------------------------------------------------------------------------------oracle.install.db.EEOptionsSelection=false#------------------------------------------------------------------------------# This variable is considered only if 'EEOptionsSelection' is set to true.## Description: List of Enterprise Edition Options you would like to enable.## The following choices are available. You may specify any# combination of these choices. The components you choose should# be specified in the form "internal-component-name:version"# Below is a list of components you may specify to enable.## oracle.oraolap:11.2.0.4.0 - Oracle OLAP# oracle.rdbms.dm:11.2.0.4.0 - Oracle Data Mining# oracle.rdbms.dv:11.2.0.4.0 - Oracle Database Vault# oracle.rdbms.lbac:11.2.0.4.0 - Oracle Label Security# oracle.rdbms.partitioning:11.2.0.4.0 - Oracle Partitioning# oracle.rdbms.rat:11.2.0.4.0 - Oracle Real Application Testing#------------------------------------------------------------------------------oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0################################################################################ ## PRIVILEGED OPERATING SYSTEM GROUPS ## ------------------------------------------ ## Provide values for the OS groups to which OSDBA and OSOPER privileges ## needs to be granted. If the install is being performed as a member of the ## group "dba", then that will be used unless specified otherwise below. ## ## The value to be specified for OSDBA and OSOPER group is only for UNIX based ## Operating System. ## #################################################################################------------------------------------------------------------------------------# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.#------------------------------------------------------------------------------oracle.install.db.DBA_GROUP=dba#------------------------------------------------------------------------------# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.# The value to be specified for OSOPER group is optional.#------------------------------------------------------------------------------oracle.install.db.OPER_GROUP=oinstall#------------------------------------------------------------------------------# Specify the cluster node names selected during the installation.# Example : oracle.install.db.CLUSTER_NODES=node1,node2#------------------------------------------------------------------------------oracle.install.db.CLUSTER_NODES=#------------------------------------------------------------------------------# This variable is used to enable or disable RAC One Node install.## - true : Value of RAC One Node service name is used.# - false : Value of RAC One Node service name is not used.## If left blank, it will be assumed to be false#------------------------------------------------------------------------------oracle.install.db.isRACOneInstall=#------------------------------------------------------------------------------# Specify the name for RAC One Node Service.#------------------------------------------------------------------------------oracle.install.db.racOneServiceName=#------------------------------------------------------------------------------# Specify the type of database to create.# It can be one of the following:# - GENERAL_PURPOSE/TRANSACTION_PROCESSING# - DATA_WAREHOUSE#------------------------------------------------------------------------------oracle.install.db.config.starterdb.type=GENERAL_PURPOSE#------------------------------------------------------------------------------# Specify the Starter Database Global Database Name.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.globalDBName=orcl#------------------------------------------------------------------------------# Specify the Starter Database SID.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.SID=dbsrv2#------------------------------------------------------------------------------# Specify the Starter Database character set.## It can be one of the following:# AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,# EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,# BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,# AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,# IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,# KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,# ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258#------------------------------------------------------------------------------oracle.install.db.config.starterdb.characterSet=AL32UTF8#------------------------------------------------------------------------------# This variable should be set to true if Automatic Memory Management# in Database is desired.# If Automatic Memory Management is not desired, and memory allocation# is to be done manually, then set it to false.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.memoryOption=true#------------------------------------------------------------------------------# Specify the total memory allocation for the database. Value(in MB) should be# at least 256 MB, and should not exceed the total physical memory available# on the system.# Example: oracle.install.db.config.starterdb.memoryLimit=512#------------------------------------------------------------------------------oracle.install.db.config.starterdb.memoryLimit=8192#------------------------------------------------------------------------------# This variable controls whether to load Example Schemas onto# the starter database or not.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.installExampleSchemas=false#------------------------------------------------------------------------------# This variable includes enabling audit settings, configuring password profiles# and revoking some grants to public. These settings are provided by default.# These settings may also be disabled.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.enableSecuritySettings=true################################################################################ ## Passwords can be supplied for the following four schemas in the ## starter database: ## SYS ## SYSTEM ## SYSMAN (used by Enterprise Manager) ## DBSNMP (used by Enterprise Manager) ## ## Same password can be used for all accounts (not recommended) ## or different passwords for each account can be provided (recommended) ## #################################################################################------------------------------------------------------------------------------# This variable holds the password that is to be used for all schemas in the# starter database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.password.ALL=oracle#-------------------------------------------------------------------------------# Specify the SYS password for the starter database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.password.SYS=#-------------------------------------------------------------------------------# Specify the SYSTEM password for the starter database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.password.SYSTEM=#-------------------------------------------------------------------------------# Specify the SYSMAN password for the starter database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.password.SYSMAN=#-------------------------------------------------------------------------------# Specify the DBSNMP password for the starter database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.password.DBSNMP=#-------------------------------------------------------------------------------# Specify the management option to be selected for the starter database.# It can be one of the following:# - GRID_CONTROL# - DB_CONTROL#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.control=DB_CONTROL#-------------------------------------------------------------------------------# Specify the Management Service to use if Grid Control is selected to manage# the database.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=################################################################################ ## SPECIFY BACKUP AND RECOVERY OPTIONS ## ------------------------------------ ## Out-of-box backup and recovery options for the database can be mentioned ## using the entries below. ## #################################################################################------------------------------------------------------------------------------# This variable is to be set to false if automated backup is not required. Else# this can be set to true.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.automatedBackup.enable=false#------------------------------------------------------------------------------# Regardless of the type of storage that is chosen for backup and recovery, if# automated backups are enabled, a job will be scheduled to run daily to backup# the database. This job will run as the operating system user that is# specified in this variable.#------------------------------------------------------------------------------oracle.install.db.config.starterdb.automatedBackup.osuid=#-------------------------------------------------------------------------------# Regardless of the type of storage that is chosen for backup and recovery, if# automated backups are enabled, a job will be scheduled to run daily to backup# the database. This job will run as the operating system user specified by the# above entry. The following entry stores the password for the above operating# system user.#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.automatedBackup.ospwd=#-------------------------------------------------------------------------------# Specify the type of storage to use for the database.# It can be one of the following:# - FILE_SYSTEM_STORAGE# - ASM_STORAGE#------------------------------------------------------------------------------oracle.install.db.config.starterdb.storageType=#-------------------------------------------------------------------------------# Specify the database file location which is a directory for datafiles, control# files, redo logs.## Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=#-------------------------------------------------------------------------------# Specify the backup and recovery location.## Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE#-------------------------------------------------------------------------------oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=#-------------------------------------------------------------------------------# Specify the existing ASM disk groups to be used for storage.## Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE#-------------------------------------------------------------------------------oracle.install.db.config.asm.diskGroup=#-------------------------------------------------------------------------------# Specify the password for ASMSNMP user of the ASM instance.## Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE#-------------------------------------------------------------------------------oracle.install.db.config.asm.ASMSNMPPassword=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username.## Example : MYORACLESUPPORT_USERNAME=abc@oracle.com#------------------------------------------------------------------------------MYORACLESUPPORT_USERNAME=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username password.## Example : MYORACLESUPPORT_PASSWORD=password#------------------------------------------------------------------------------MYORACLESUPPORT_PASSWORD=#------------------------------------------------------------------------------# Specify whether to enable the user to set the password for# My Oracle Support credentials. The value can be either true or false.# If left blank it will be assumed to be false.## Example : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true#------------------------------------------------------------------------------SECURITY_UPDATES_VIA_MYORACLESUPPORT=false#------------------------------------------------------------------------------# Specify whether user doesn't want to configure Security Updates.# The value for this variable should be true if you don't want to configure# Security Updates, false otherwise.## The value can be either true or false. If left blank it will be assumed# to be false.## Example : DECLINE_SECURITY_UPDATES=false#------------------------------------------------------------------------------DECLINE_SECURITY_UPDATES=true#------------------------------------------------------------------------------# Specify the Proxy server name. Length should be greater than zero.## Example : PROXY_HOST=proxy.domain.com#------------------------------------------------------------------------------PROXY_HOST=#------------------------------------------------------------------------------# Specify the proxy port number. Should be Numeric and at least 2 chars.## Example : PROXY_PORT=25#------------------------------------------------------------------------------PROXY_PORT=#------------------------------------------------------------------------------# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD# blank if your proxy server requires no authentication.## Example : PROXY_USER=username#------------------------------------------------------------------------------PROXY_USER=#------------------------------------------------------------------------------# Specify the proxy password. Leave PROXY_USER and PROXY_PWD# blank if your proxy server requires no authentication.## Example : PROXY_PWD=password#------------------------------------------------------------------------------PROXY_PWD=#------------------------------------------------------------------------------# Specify the proxy realm. This value is used if auto-updates option is selected.## Example : PROXY_REALM=metalink#------------------------------------------------------------------------------PROXY_REALM=#------------------------------------------------------------------------------# Specify the Oracle Support Hub URL.## Example : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/#------------------------------------------------------------------------------COLLECTOR_SUPPORTHUB_URL=#------------------------------------------------------------------------------# Specify the auto-updates option. It can be one of the following:# - MYORACLESUPPORT_DOWNLOAD# - OFFLINE_UPDATES# - SKIP_UPDATES#------------------------------------------------------------------------------oracle.installer.autoupdates.option=#------------------------------------------------------------------------------# In case MYORACLESUPPORT_DOWNLOAD option is chosen, specify the location where# the updates are to be downloaded.# In case OFFLINE_UPDATES option is chosen, specify the location where the updates# are present.#------------------------------------------------------------------------------oracle.installer.autoupdates.downloadUpdatesLoc=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username which has the patches download privileges# to be used for software updates.# Example : AUTOUPDATES_MYORACLESUPPORT_USERNAME=abc@oracle.com#------------------------------------------------------------------------------AUTOUPDATES_MYORACLESUPPORT_USERNAME=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username password which has the patches download privileges# to be used for software updates.## Example : AUTOUPDATES_MYORACLESUPPORT_PASSWORD=password#------------------------------------------------------------------------------AUTOUPDATES_MYORACLESUPPORT_PASSWORD=EOFchown -R oracle:oinstall /home/oracle/etc/db_install.rspchmod 660 /home/oracle/etc/db_install.rspcd /usr/local/src/database/su - oracle -c "/usr/local/src/database/runInstaller -silent -responseFile /home/oracle/etc/db_install.rsp"sleep 500/u01/app/oraInventory/orainstRoot.sh/u01/app/oracle/product/11.2.0/db_1/root.shcat <<EOF >>/home/oracle/.bash_profile#for oracleexport ORACLE_BASE=/u01/app/oracleexport ORACLE_SID=orcl11gexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1export LANG="zh_CN.UTF-8"export ORACLE_TERM=xtermexport NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'export PATH=/usr/sbin:\$PATHexport PATH=\$ORACLE_HOME/bin:\$PATHexport LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlibEOF####使配置文件生效###source /home/oracle/.bash_profileend_time=$(date +%s)execution_time=$((end_time - start_time))echo "脚本执行时间：${execution_time} 秒"
三、部署DG
（一）基础环境配置
两个节点都执行：
修改主备库的主机名并进行hosts文件配置
[root@myoracle ~]# hostnamectl set-hostname primarynode #主库[root@myoracle ~]# hostnamectl set-hostname standbynode #备库cat <<EOF >>/etc//etc/hosts192.168.59.249 primarynode192.168.59.250 standbynodeEOF
注意：使用一键脚本安装的需要注意修改listener.ora和tnsnames.ora两个文件对应的HOST。
路径为/u01/app/oracle/product/11.2.0/db_1/network/admin
（二）DG1配置:
1、开启归档
关闭数据库
SQL> shutdown immediate
启动数据库到 mount
SQL> startup mount;
开启归档
SQL> alter database archivelog;
打开数据库
SQL> alter database open;
查询是否启用归档
SQL>archive log list
2、主库启用强制记录日志功能
查询是否启用强制记录日志
SQL>select force_logging from v$database;
如果未启用，则使用下面语句来开启强制记录日志
SQL> alter database force logging;
3、主库参数配置
一共11个参数需要进行配置。这是关键，如果参数有误，那么搭建会失败。
1、db_unique_name
SQL> alter system set db_unique_name = 'orcl11g' scope=spfile;
2、log_archive_config
SQL> alter system set log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)' scope=spfile;
3、log_archive_dest_1
查看归档日志的目录位置
SQL> SELECT NAME, ARCHIVED, DEST_ID, COMPLETION_TIME FROM V$ARCHIVED_LOG ORDER BY COMPLETION_TIME DESC;
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11g' scope=both;
4、log_archive_dest_2
SQL> alter system set log_archive_dest_2='SERVICE=tnsorcl11gstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11gdg' scope=spfile;
5、log_archive_dest_state_1
SQL> alter system set log_archive_dest_state_1 = ENABLE;
6、log_archive_dest_state_2
SQL> alter system set log_archive_dest_state_2 = ENABLE;
7、db_file_name_convert
查找数据文件的位置
SQL> select name from v$datafile;SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g' scope=spfile;
8、log_file_name_convert
查看在线日志文件的位置：
SQL> select member from v$logfile;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g' scope=spfile;
9、standby_file_management
SQL> alter system set standby_file_management=auto scope=spfile;
10、fal_client
SQL> alter system set fal_client='tnsorcl11g' scope=both;
11、fal_server
SQL> alter system set fal_server='tnsorcl11gstandby' scope=both;
4、主库静态监听配置
[oracle@primarynode admin]$cd /u01/app/oracle/product/11.2.0/db_1/network/admin[oracle@primarynode admin]$ cat listener.ora# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME=orcl11g) #修改GLOBAL_DBNAME(SID_NAME = orcl11g) #修改SID_NAME(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)))ADR_BASE_LISTENER = /u01/app/oracle
[oracle@primarynode admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.tnsorcl11g =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl11g)))tnsorcl11gstandby =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl11g)))
查看监听的状态
[oracle@primarynode admin]$ lsnrctl status
（三）DG2配置
1、密码文件
在dataguard中，主库与备库sys密码需一致，需要将主库的密码文件拷贝到备库中。
[oracle@primarynode admin]$ cd $ORACLE_HOME/dbs[oracle@primarynode dbs]$ scp orapworcl11g oracle@192.168.59.250:/u01/app/oracle/product/11.2.0/db_1/dbs
如果不拷贝密码文件，直接使用orapwd生成一个新的密码文件，密码需与主库一致：
[oracle@primarynode dbs]$ orapwd file=orapworcl11g password=’password’;
2、初始化参数文件
1、在主库生成初始化参数文件
SQL> create pfile from spfile;
2、拷贝主库的参数文件到备库
[oracle@primarynode dbs]$ scp initorcl11g.ora oracle@192.168.59.250:/u01/app/oracle/product/11.2.0/db_1/dbs
3、修改备库的参数文件内容
[oracle@standbynode dbs]$ cat initorcl11g.oraorcl11g.__db_cache_size=654311424orcl11g.__java_pool_size=16777216orcl11g.__large_pool_size=33554432orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentorcl11g.__pga_aggregate_target=637534208orcl11g.__sga_target=956301312orcl11g.__shared_io_pool_size=0orcl11g.__shared_pool_size=234881024orcl11g.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/orcl11g/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/orcl11g/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g'*.log_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g'*.db_name='orcl11g'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=4385144832*.db_unique_name='orcl11gdg'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)'*.fal_client='tnsorcl11gstandby'*.fal_server='tnsorcl11g'*.log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)'*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11GDG/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11gdg'*.log_archive_dest_2='SERVICE=tnsorcl11g LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.memory_target=1580204032*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.standby_file_management='AUTO'*.undo_tablespace='UNDOTBS1'
注意：里面涉及到路径的需要手动创建
[oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/admin/orcl11g/adump[oracle@standbynode oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL11G/archivelog[oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/oradata/orcl11g[oracle@standbynode fast_recovery_area]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl11g/
使用pfile文件创建spfile文件
登陆到idle数据库
[oracle@standbynode dbs]$sqlplus / as sysdba；SQL> create spfile from pfile；
SQL> startup nomount
如有以下报错，需要进行以下配置:
vi /etc/fstab新增tmpfs /dev/shm tmpfs defaults,size=4096M 0 0
[root@standbynode ~]# mount -o remount /dev/shm
然后继续执行 startup nomount
SQL> startup nomount
4、配置静态监听
[oracle@standbynode dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/[oracle@standbynode admin]$ vi listener.ora新增以下内容# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521)))))SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME=orcl11g)(SID_NAME = orcl11g)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)))ADR_BASE_LISTENER = /u01/oracle
配置tnsnames.ora 文件
[oracle@standbynode admin]$ cat tnsnames.oratnsorcl11g =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl11g)))tnsorcl11gstandby =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl11g)))
6、测试网络连通性
主备之间互相测试连通性
[oracle@standbynode admin]$ tnsping tnsorcl11g[oracle@primarynode dbs]$ tnsping tnsorcl11gstandby
7、在主库进行登录测试
[oracle@primarynode dbs]$ sqlplus sys/password@tnsorcl11g as sysdba
[oracle@primarynode dbs]$ sqlplus sys/password@tnsorcl11gstandby as sysdba
8、使用duplicate创建物理standby（有很多种办法，今天只分享这一种）
（1）连接到主备库
[oracle@primarynode dbs]$ rman target sys/password@tnsorcl11g auxiliary sys/password@tnsorcl11gstandby nocatalog
（2）使用duplicate复制数据库
RMAN> duplicate target database for standby from active database nofilenamecheck;
9、添加主库和备库的standby日志组
先查看redo日志的位置
SQL> select member from v$logfile;
添加主库的standby日志组
编写4个日志组，根据上面查出来的编号进行排序。
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl11g/redo04.log' size 50M;SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl11g/redo05.log' size 50M;SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl11g/redo06.log' size 50M;SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl11g/redo07.log' size 50M;
添加备库的standby日志组
SQL>alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl11g/redo04.log' size 50M;SQL>alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl11g/redo05.log' size 50M;SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl11g/redo06.log' size 50M;SQL>alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl11g/redo07.log' size 50M;
10、开始同步数据库，在备库上执行
SQL>alter database open;
11、开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
至此DG搭建完成。
四、查看主备库状态
主库检查
SQL>set line 200set pagesize 200select name,remote_archive,database_role,guard_status,db_unique_name from v$database;
备库检查
SQL>set line 200set pagesize 200select name,remote_archive,database_role,guard_status,db_unique_name from v$database;
查询主备库日志是否同步（确保最大 SEQUENCE#相同）
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BYSEQUENCE#;
检查日志是否被备库使用（APPLIED 值为 yes）
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
五、DataGuard测试
在主库上创建测试表并插入数据
SQL> create table test(id number,name varchar2(255));insert into test values(1,'zhangsan');insert into test values(2,'lisi');commit;
在standby数据库上查询测试表，验证数据同步成功
SQL> select * from test;
六、日常运维必备命令
主备库角色状态查询
SQL> select switchover_status,database_role from v$database;
查看dataguard最新状态
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';set line 160set pagesize 1000col type for a20select process, status, thread#,sequence#, block#, blocks from v$managed_standby;select GROUP#,THREAD#,SEQUENCE#,STATUS,LAST_CHANGE#,LAST_TIME from v$standby_log;select START_TIME,TYPE, ITEM,UNITS,SOFAR,TIMESTAMP from v$recovery_progress where ITEM='Last Applied Redo';
查看redo_log
主库查看redo log
SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;
备库查看standby log
SQL> select group#,thread#,bytes/1024/1024M,status,last_change#,last_time from v$standby_log;
主、备库查看当前日志状况
SQL> select sequence#,status from v$log;
手动启动MRP0进程
SQL> alter database recover managed standby database disconnect from sessionSQL> select open_mode,current_scn from v$database;SQL> select * from v$standby_log;SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS,THREAD# from v$managed_standby;
开启数据库闪回
SQL> alter database flashback on;
重启监听并查看状态
[oracle@myoracle admin]$ lsnrctl stop[oracle@myoracle admin]$ lsnrctl start[oracle@myoracle admin]$ lsnrctl status
如看不到服务，需执行以下命令：
显示服务名：
SQL>show parameter service_names
强制注册服务：
SQL>alter system register;
主备库切换演练
1、主库切换到备库
SQL> alter database commit to switchover to physical standby;SQL> alter database commit to switchover to physical standby with session shutdown;--如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。SQL> shutdown immediateSQL> startup mount
2、备库切换到主库
SQL> alter database commit to switchover to primary;SQL> alter database open;原主库(新备库)执行APPLY LOG命令--启用备库mount状态下的APPLY LOGSQL> alter database recover managed standby database disconnect from session;--启用open状态（READ ONLY WITH APPLY）下的APPLY LOGSQL> alter database recover managed standby database cancel;SQL> alter database open;SQL> alter database recover managed standby database disconnect from session;SQL> select switchover_status,database_role,open_mode from v$database;
恢复主备GAP（适用于11g以后版本）
SQL> recover database from service orcl11g noredo using compressed backupset;
同步情况检查
SQL>archive log list; #检查当前log号 Current log sequence和主库一致
查看DG库归档是否有缺失
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;
开启归档应用
SQL>alter database recover managed standby database disconnect from session;
如果开启了实时归档，可以执行以下命令
SQL>alter database recover managed standby database cancel;SQL>alter database open;SQL>alter database recover managed standby database using current logfile disconnect;
开启和关闭 DG
开启 DG
启动备库（mount）——启动主库（open）——启动备库日志同步登录备库：$lsnrctl startSQL>startup mount;登录主库：$lsnrctl startSQL>startup登录备库：SQL> alter database recover managed standby database disconnect from session;
关闭 DG（请确保应用程序已经断开数据库连接或关闭）
关闭备库日志同步——关闭主库——关闭备库登录备库：SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;登录主库：SQL>shutdown immediate$lsnrctl stop登录备库SQL>shutdown immediate$lsnrctl stop
