暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 11g DataGuard搭建保姆级教程

Oracle 11g DataGuard搭建保姆级教程

小周的数据库进阶之路 2024-08-21
431


DG的备库有2种类型，物理和逻辑。今天主要分享物理备库的搭建过程

一、环境准备


dg1(主库)dg2(备库)
ip192.168.59.249192.168.59.250
db_nameorcl11gorcl11g
db_unique_nameorcl11gorcl11gdg
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
主机名primarynodestandbynode
数据库版本11.2.0.411.2.0.4
操作系统centos7centos7
instance_nameorcl11gorcl11g
service_nameorcl11gorcl11g
数据库安装安装数据库软件，创建监听和数据库实例安装数据库软件

二、安装数据库

1、dg1：

安装数据库软件，创建监听和数据库实例

执行一键安装脚本

  1. 安装脚本可以参考我的这篇文章：https://mp.weixin.qq.com/s/2B_QyFGOAEhb_0xydog0zw


2、dg2：

只安装数据库软件

执行以下脚本

  1. #!/bin/bash
  2. #一键安装oracle数据库
  3. start_time=$(date +%s)
  4. #修改主机名
  5. hostnamectl set-hostname myoracle
  6. #添加主机名与IP对应记录
  7. 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)
  8. node_name=$(hostname)
  9. echo -e "${public_ip} ${node_name}">> /etc/hosts
  10. cat /etc/hosts
  11. #关闭Selinux
  12. sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
  13. setenforce 0
  14. #关闭防火墙
  15. systemctl stop firewalld
  16. systemctl disable firewalld
  17. # 获取当前所在目录位置
  18. current_dir=$(pwd)
  19. echo "当前所在目录位置: $current_dir"
  20. # 目标路径
  21. target_dir="/soft"
  22. # 检查目标路径是否存在，如果不存在则创建
  23. if [ ! -d "$target_dir" ]; then
  24.     mkdir -p "$target_dir"
  25.     echo "已创建目录: $target_dir"
  26. fi
  27. # 移动当前目录下的所有文件到目标路径
  28. mv $current_dir/* $target_dir
  29. echo "已将当前目录下所有文件移动至 $target_dir"
  30. #添加离线yum源
  31. cd soft
  32. tar -xvf oracle_repo.tar.gz
  33. cd soft/my_oracle_repo
  34. rpm -ivh *.rpm --nodeps --force
  35. #在线yum源
  36. cd etc/yum.repos.d/
  37. rm -rf ./*
  38. sleep 20
  39. wget -O etc/yum.repos.d/CentOS-Base.repo  http://mirrors.aliyun.com/repo/Centos-7.repo
  40. #添加用户组及用户
  41. groupadd oinstall
  42. groupadd dba
  43. useradd -g oinstall -G dba oracle
  44. echo oracle | passwd --stdin oracle
  45. chmod 644 etc/sysctl.conf
  46. #修改环境变量
  47. cat <<EOF >>/etc/sysctl.conf
  48. fs.aio-max-nr = 1048576
  49. fs.file-max = 6815744
  50. kernel.shmall = 2097152
  51. kernel.shmmax = 536870912
  52. kernel.shmmni = 4096
  53. kernel.sem = 250 32000 100 128
  54. net.ipv4.ip_local_port_range = 9000 65500
  55. net.core.rmem_default = 262144
  56. net.core.rmem_max = 4194304
  57. net.core.wmem_default = 262144
  58. net.core.wmem_max = 1048576
  59. EOF
  60. sysctl -p
  61. chmod 644 etc/security/limits.conf
  62. cat <<EOF >>/etc/security/limits.conf
  63. oracle soft nproc 2047
  64. oracle hard nproc 16384
  65. oracle soft nofile 1024
  66. oracle hard nofile 65536
  67. oracle soft stack 10240
  68. oracle hard stack 10240
  69. EOF
  70. chmod 644 etc/pam.d/login
  71. cat <<EOF >>/etc/pam.d/login
  72. session required lib64/security/pam_limits.so
  73. session required pam_limits.so
  74. EOF
  75. chmod 644 etc/profile
  76. cat <<EOF >>/etc/profile
  77. if [ $USER = "oracle" ]; then
  78.    if [ $SHELL = "/bin/ksh" ]; then
  79.        ulimit -p 16384
  80.        ulimit -n 65536
  81.     else
  82.        ulimit -u 16384 -n 65536
  83.    fi
  84. fi
  85. EOF
  86. source etc/profile
  87. mkdir -p u01/app/
  88. chown -R oracle:oinstall u01/app/
  89. chmod -R 775 u01/app/
  90. mkdir -p usr/local/src
  91. cd  usr/local/src
  92. #解压oracle安装包
  93. mv soft/*.zip  usr/local/src/
  94. cd usr/local/src/
  95. unzip p13390677_112040_Linux-x86-64_1of7.zip
  96. unzip p13390677_112040_Linux-x86-64_2of7.zip
  97. mkdir home/oracle/etc
  98. chown -R oracle.oinstall home/oracle/etc
  99. cp  usr/local/src/database/response/* home/oracle/etc/
  100. chmod 777 home/oracle/etc/*.rsp
  101. chown -R  oracle:oinstall home/oracle/etc/
  102. cat <<EOF >/home/oracle/etc/db_install.rsp
  103. ####################################################################
  104. ## Copyright(c) Oracle Corporation 1998,2013. All rights reserved.##
  105. ##                                                                ##
  106. ## Specify values for the variables listed below to customize     ##
  107. ## your installation.                                             ##
  108. ##                                                                ##
  109. ## Each variable is associated with a comment. The comment        ##
  110. ## can help to populate the variables with the appropriate        ##
  111. ## values.                              ##
  112. ##                                                                ##
  113. ## IMPORTANT NOTE: This file contains plain text passwords and    ##
  114. ## should be secured to have read permission only by oracle user  ##
  115. ## or db administrator who owns this installation.                ##
  116. ##                                                                ##
  117. ####################################################################
  118. #------------------------------------------------------------------------------
  119. # Do not change the following system generated value.
  120. #------------------------------------------------------------------------------
  121. oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
  122. #------------------------------------------------------------------------------
  123. # Specify the installation option.
  124. # It can be one of the following:
  125. #   - INSTALL_DB_SWONLY
  126. #   - INSTALL_DB_AND_CONFIG
  127. #   - UPGRADE_DB
  128. #-------------------------------------------------------------------------------
  129. oracle.install.option=INSTALL_DB_SWONLY
  130. #-------------------------------------------------------------------------------
  131. # Specify the hostname of the system as set during the install. It can be used
  132. # to force the installation to use an alternative hostname rather than using the
  133. # first hostname found on the system. (e.g., for systems with multiple hostnames
  134. # and network interfaces)
  135. #-------------------------------------------------------------------------------
  136. ORACLE_HOSTNAME=myoracle
  137. #-------------------------------------------------------------------------------
  138. # Specify the Unix group to be set for the inventory directory.
  139. #-------------------------------------------------------------------------------
  140. UNIX_GROUP_NAME=oinstall
  141. #-------------------------------------------------------------------------------
  142. # Specify the location which holds the inventory files.
  143. # This is an optional parameter if installing on
  144. # Windows based Operating System.
  145. #-------------------------------------------------------------------------------
  146. INVENTORY_LOCATION=/u01/app/oraInventory
  147. #-------------------------------------------------------------------------------
  148. # Specify the languages in which the components will be installed.
  149. #
  150. # en   : English                  ja   : Japanese
  151. # fr   : French                   ko   : Korean
  152. # ar   : Arabic                   es   : Latin American Spanish
  153. # bn   : Bengali                  lv   : Latvian
  154. # pt_BR: Brazilian Portuguese     lt   : Lithuanian
  155. # bg   : Bulgarian                ms   : Malay
  156. # fr_CA: Canadian French          es_MX: Mexican Spanish
  157. # ca   : Catalan                  no   : Norwegian
  158. # hr   : Croatian                 pl   : Polish
  159. # cs   : Czech                    pt   : Portuguese
  160. # da   : Danish                   ro   : Romanian
  161. # nl   : Dutch                    ru   : Russian
  162. # ar_EG: Egyptian                 zh_CN: Simplified Chinese
  163. # en_GB: English (Great Britain)  sk   : Slovak
  164. # et   : Estonian                 sl   : Slovenian
  165. # fi   : Finnish                  es_ES: Spanish
  166. # de   : German                   sv   : Swedish
  167. # el   : Greek                    th   : Thai
  168. # iw   : Hebrew                   zh_TW: Traditional Chinese
  169. # hu   : Hungarian                tr   : Turkish
  170. # is   : Icelandic                uk   : Ukrainian
  171. # in   : Indonesian               vi   : Vietnamese
  172. # it   : Italian
  173. #
  174. # all_langs   : All languages
  175. #
  176. # Specify value as the following to select any of the languages.
  177. # Example : SELECTED_LANGUAGES=en,fr,ja
  178. #
  179. # Specify value as the following to select all the languages.
  180. # Example : SELECTED_LANGUAGES=all_langs
  181. #------------------------------------------------------------------------------
  182. SELECTED_LANGUAGES=en
  183. #------------------------------------------------------------------------------
  184. # Specify the complete path of the Oracle Home.
  185. #------------------------------------------------------------------------------
  186. ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
  187. #------------------------------------------------------------------------------
  188. # Specify the complete path of the Oracle Base.
  189. #------------------------------------------------------------------------------
  190. ORACLE_BASE=/u01/app/oracle
  191. #------------------------------------------------------------------------------
  192. # Specify the installation edition of the component.
  193. #
  194. # The value should contain only one of these choices.
  195. #   - EE     : Enterprise Edition
  196. #   - SE     : Standard Edition
  197. #   - SEONE  : Standard Edition One
  198. #   - PE     : Personal Edition (WINDOWS ONLY)
  199. #------------------------------------------------------------------------------
  200. oracle.install.db.InstallEdition=EE
  201. #------------------------------------------------------------------------------
  202. # This variable is used to enable or disable custom install and is considered
  203. # only if InstallEdition is EE.
  204. #
  205. # true  : Components mentioned as part of 'optionalComponents' property
  206. #         are considered for install.
  207. # false : Value for 'optionalComponents' is not considered.
  208. #------------------------------------------------------------------------------
  209. oracle.install.db.EEOptionsSelection=false
  210. #------------------------------------------------------------------------------
  211. # This variable is considered only if 'EEOptionsSelection' is set to true.
  212. #
  213. # Description: List of Enterprise Edition Options you would like to enable.
  214. #
  215. #              The following choices are available. You may specify any
  216. #              combination of these choices.  The components you choose should
  217. #              be specified in the form "internal-component-name:version"
  218. #              Below is a list of components you may specify to enable.
  219. #
  220. #              oracle.oraolap:11.2.0.4.0 - Oracle OLAP
  221. #              oracle.rdbms.dm:11.2.0.4.0 - Oracle Data Mining
  222. #              oracle.rdbms.dv:11.2.0.4.0 - Oracle Database Vault
  223. #              oracle.rdbms.lbac:11.2.0.4.0 - Oracle Label Security
  224. #              oracle.rdbms.partitioning:11.2.0.4.0 - Oracle Partitioning
  225. #              oracle.rdbms.rat:11.2.0.4.0 - Oracle Real Application Testing
  226. #------------------------------------------------------------------------------
  227. 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
  228. ###############################################################################
  229. #                                                                             #
  230. # PRIVILEGED OPERATING SYSTEM GROUPS                                            #
  231. # ------------------------------------------                                  #
  232. # Provide values for the OS groups to which OSDBA and OSOPER privileges       #
  233. # needs to be granted. If the install is being performed as a member of the   #
  234. # group "dba", then that will be used unless specified otherwise below.          #
  235. #                                                                             #
  236. # The value to be specified for OSDBA and OSOPER group is only for UNIX based #
  237. # Operating System.                                                           #
  238. #                                                                             #
  239. ###############################################################################
  240. #------------------------------------------------------------------------------
  241. # The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
  242. #------------------------------------------------------------------------------
  243. oracle.install.db.DBA_GROUP=dba
  244. #------------------------------------------------------------------------------
  245. # The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
  246. # The value to be specified for OSOPER group is optional.
  247. #------------------------------------------------------------------------------
  248. oracle.install.db.OPER_GROUP=oinstall
  249. #------------------------------------------------------------------------------
  250. # Specify the cluster node names selected during the installation.
  251. # Example : oracle.install.db.CLUSTER_NODES=node1,node2
  252. #------------------------------------------------------------------------------
  253. oracle.install.db.CLUSTER_NODES=
  254. #------------------------------------------------------------------------------
  255. # This variable is used to enable or disable RAC One Node install.
  256. #
  257. #   - true  : Value of RAC One Node service name is used.
  258. #   - false : Value of RAC One Node service name is not used.
  259. #
  260. # If left blank, it will be assumed to be false
  261. #------------------------------------------------------------------------------
  262. oracle.install.db.isRACOneInstall=
  263. #------------------------------------------------------------------------------
  264. # Specify the name for RAC One Node Service.
  265. #------------------------------------------------------------------------------
  266. oracle.install.db.racOneServiceName=
  267. #------------------------------------------------------------------------------
  268. # Specify the type of database to create.
  269. # It can be one of the following:
  270. #   - GENERAL_PURPOSE/TRANSACTION_PROCESSING
  271. #   - DATA_WAREHOUSE
  272. #------------------------------------------------------------------------------
  273. oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
  274. #------------------------------------------------------------------------------
  275. # Specify the Starter Database Global Database Name.
  276. #------------------------------------------------------------------------------
  277. oracle.install.db.config.starterdb.globalDBName=orcl
  278. #------------------------------------------------------------------------------
  279. # Specify the Starter Database SID.
  280. #------------------------------------------------------------------------------
  281. oracle.install.db.config.starterdb.SID=dbsrv2
  282. #------------------------------------------------------------------------------
  283. # Specify the Starter Database character set.
  284. #
  285. # It can be one of the following:
  286. # AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
  287. # EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
  288. # BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
  289. # AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
  290. # IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
  291. # KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
  292. # ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
  293. #------------------------------------------------------------------------------
  294. oracle.install.db.config.starterdb.characterSet=AL32UTF8
  295. #------------------------------------------------------------------------------
  296. # This variable should be set to true if Automatic Memory Management
  297. # in Database is desired.
  298. # If Automatic Memory Management is not desired, and memory allocation
  299. # is to be done manually, then set it to false.
  300. #------------------------------------------------------------------------------
  301. oracle.install.db.config.starterdb.memoryOption=true
  302. #------------------------------------------------------------------------------
  303. # Specify the total memory allocation for the database. Value(in MB) should be
  304. # at least 256 MB, and should not exceed the total physical memory available
  305. # on the system.
  306. # Example: oracle.install.db.config.starterdb.memoryLimit=512
  307. #------------------------------------------------------------------------------
  308. oracle.install.db.config.starterdb.memoryLimit=8192
  309. #------------------------------------------------------------------------------
  310. # This variable controls whether to load Example Schemas onto
  311. # the starter database or not.
  312. #------------------------------------------------------------------------------
  313. oracle.install.db.config.starterdb.installExampleSchemas=false
  314. #------------------------------------------------------------------------------
  315. # This variable includes enabling audit settings, configuring password profiles
  316. # and revoking some grants to public. These settings are provided by default.
  317. # These settings may also be disabled.
  318. #------------------------------------------------------------------------------
  319. oracle.install.db.config.starterdb.enableSecuritySettings=true
  320. ###############################################################################
  321. #                                                                             #
  322. # Passwords can be supplied for the following four schemas in the          #
  323. # starter database:                                    #
  324. #   SYS                                                                       #
  325. #   SYSTEM                                                                    #
  326. #   SYSMAN (used by Enterprise Manager)                                       #
  327. #   DBSNMP (used by Enterprise Manager)                                       #
  328. #                                                                             #
  329. # Same password can be used for all accounts (not recommended)               #
  330. # or different passwords for each account can be provided (recommended)       #
  331. #                                                                             #
  332. ###############################################################################
  333. #------------------------------------------------------------------------------
  334. # This variable holds the password that is to be used for all schemas in the
  335. # starter database.
  336. #-------------------------------------------------------------------------------
  337. oracle.install.db.config.starterdb.password.ALL=oracle
  338. #-------------------------------------------------------------------------------
  339. # Specify the SYS password for the starter database.
  340. #-------------------------------------------------------------------------------
  341. oracle.install.db.config.starterdb.password.SYS=
  342. #-------------------------------------------------------------------------------
  343. # Specify the SYSTEM password for the starter database.
  344. #-------------------------------------------------------------------------------
  345. oracle.install.db.config.starterdb.password.SYSTEM=
  346. #-------------------------------------------------------------------------------
  347. # Specify the SYSMAN password for the starter database.
  348. #-------------------------------------------------------------------------------
  349. oracle.install.db.config.starterdb.password.SYSMAN=
  350. #-------------------------------------------------------------------------------
  351. # Specify the DBSNMP password for the starter database.
  352. #-------------------------------------------------------------------------------
  353. oracle.install.db.config.starterdb.password.DBSNMP=
  354. #-------------------------------------------------------------------------------
  355. # Specify the management option to be selected for the starter database.
  356. # It can be one of the following:
  357. #   - GRID_CONTROL
  358. #   - DB_CONTROL
  359. #-------------------------------------------------------------------------------
  360. oracle.install.db.config.starterdb.control=DB_CONTROL
  361. #-------------------------------------------------------------------------------
  362. # Specify the Management Service to use if Grid Control is selected to manage
  363. # the database.
  364. #-------------------------------------------------------------------------------
  365. oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
  366. ###############################################################################
  367. #                                                                             #
  368. # SPECIFY BACKUP AND RECOVERY OPTIONS                                           #
  369. # ------------------------------------                                      #
  370. # Out-of-box backup and recovery options for the database can be mentioned    #
  371. # using the entries below.                              #
  372. #                                                                             #
  373. ###############################################################################
  374. #------------------------------------------------------------------------------
  375. # This variable is to be set to false if automated backup is not required. Else
  376. # this can be set to true.
  377. #------------------------------------------------------------------------------
  378. oracle.install.db.config.starterdb.automatedBackup.enable=false
  379. #------------------------------------------------------------------------------
  380. # Regardless of the type of storage that is chosen for backup and recovery, if
  381. # automated backups are enabled, a job will be scheduled to run daily to backup
  382. # the database. This job will run as the operating system user that is
  383. # specified in this variable.
  384. #------------------------------------------------------------------------------
  385. oracle.install.db.config.starterdb.automatedBackup.osuid=
  386. #-------------------------------------------------------------------------------
  387. # Regardless of the type of storage that is chosen for backup and recovery, if
  388. # automated backups are enabled, a job will be scheduled to run daily to backup
  389. # the database. This job will run as the operating system user specified by the
  390. # above entry. The following entry stores the password for the above operating
  391. # system user.
  392. #-------------------------------------------------------------------------------
  393. oracle.install.db.config.starterdb.automatedBackup.ospwd=
  394. #-------------------------------------------------------------------------------
  395. # Specify the type of storage to use for the database.
  396. # It can be one of the following:
  397. #   - FILE_SYSTEM_STORAGE
  398. #   - ASM_STORAGE
  399. #------------------------------------------------------------------------------
  400. oracle.install.db.config.starterdb.storageType=
  401. #-------------------------------------------------------------------------------
  402. # Specify the database file location which is a directory for datafiles, control
  403. # files, redo logs.
  404. #
  405. # Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
  406. #-------------------------------------------------------------------------------
  407. oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
  408. #-------------------------------------------------------------------------------
  409. # Specify the backup and recovery location.
  410. #
  411. # Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
  412. #-------------------------------------------------------------------------------
  413. oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
  414. #-------------------------------------------------------------------------------
  415. # Specify the existing ASM disk groups to be used for storage.
  416. #
  417. # Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
  418. #-------------------------------------------------------------------------------
  419. oracle.install.db.config.asm.diskGroup=
  420. #-------------------------------------------------------------------------------
  421. # Specify the password for ASMSNMP user of the ASM instance.
  422. #
  423. # Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
  424. #-------------------------------------------------------------------------------
  425. oracle.install.db.config.asm.ASMSNMPPassword=
  426. #------------------------------------------------------------------------------
  427. # Specify the My Oracle Support Account Username.
  428. #
  429. #  Example   : MYORACLESUPPORT_USERNAME=abc@oracle.com
  430. #------------------------------------------------------------------------------
  431. MYORACLESUPPORT_USERNAME=
  432. #------------------------------------------------------------------------------
  433. # Specify the My Oracle Support Account Username password.
  434. #
  435. # Example    : MYORACLESUPPORT_PASSWORD=password
  436. #------------------------------------------------------------------------------
  437. MYORACLESUPPORT_PASSWORD=
  438. #------------------------------------------------------------------------------
  439. # Specify whether to enable the user to set the password for
  440. # My Oracle Support credentials. The value can be either true or false.
  441. # If left blank it will be assumed to be false.
  442. #
  443. # Example    : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
  444. #------------------------------------------------------------------------------
  445. SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
  446. #------------------------------------------------------------------------------
  447. # Specify whether user doesn't want to configure Security Updates.
  448. # The value for this variable should be true if you don't want to configure
  449. # Security Updates, false otherwise.
  450. #
  451. # The value can be either true or false. If left blank it will be assumed
  452. # to be false.
  453. #
  454. # Example    : DECLINE_SECURITY_UPDATES=false
  455. #------------------------------------------------------------------------------
  456. DECLINE_SECURITY_UPDATES=true
  457. #------------------------------------------------------------------------------
  458. # Specify the Proxy server name. Length should be greater than zero.
  459. #
  460. # Example    : PROXY_HOST=proxy.domain.com
  461. #------------------------------------------------------------------------------
  462. PROXY_HOST=
  463. #------------------------------------------------------------------------------
  464. # Specify the proxy port number. Should be Numeric and at least 2 chars.
  465. #
  466. # Example    : PROXY_PORT=25
  467. #------------------------------------------------------------------------------
  468. PROXY_PORT=
  469. #------------------------------------------------------------------------------
  470. # Specify the proxy user name. Leave PROXY_USER and PROXY_PWD
  471. # blank if your proxy server requires no authentication.
  472. #
  473. # Example    : PROXY_USER=username
  474. #------------------------------------------------------------------------------
  475. PROXY_USER=
  476. #------------------------------------------------------------------------------
  477. # Specify the proxy password. Leave PROXY_USER and PROXY_PWD
  478. # blank if your proxy server requires no authentication.
  479. #
  480. # Example    : PROXY_PWD=password
  481. #------------------------------------------------------------------------------
  482. PROXY_PWD=
  483. #------------------------------------------------------------------------------
  484. # Specify the proxy realm. This value is used if auto-updates option is selected.
  485. #
  486. # Example    : PROXY_REALM=metalink
  487. #------------------------------------------------------------------------------
  488. PROXY_REALM=
  489. #------------------------------------------------------------------------------
  490. # Specify the Oracle Support Hub URL.
  491. #
  492. # Example    : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/
  493. #------------------------------------------------------------------------------
  494. COLLECTOR_SUPPORTHUB_URL=
  495. #------------------------------------------------------------------------------
  496. # Specify the auto-updates option. It can be one of the following:
  497. #   - MYORACLESUPPORT_DOWNLOAD
  498. #   - OFFLINE_UPDATES
  499. #   - SKIP_UPDATES
  500. #------------------------------------------------------------------------------
  501. oracle.installer.autoupdates.option=
  502. #------------------------------------------------------------------------------
  503. # In case MYORACLESUPPORT_DOWNLOAD option is chosen, specify the location where
  504. # the updates are to be downloaded.
  505. # In case OFFLINE_UPDATES option is chosen, specify the location where the updates
  506. # are present.
  507. #------------------------------------------------------------------------------
  508. oracle.installer.autoupdates.downloadUpdatesLoc=
  509. #------------------------------------------------------------------------------
  510. # Specify the My Oracle Support Account Username which has the patches download privileges
  511. # to be used for software updates.
  512. #  Example   : AUTOUPDATES_MYORACLESUPPORT_USERNAME=abc@oracle.com
  513. #------------------------------------------------------------------------------
  514. AUTOUPDATES_MYORACLESUPPORT_USERNAME=
  515. #------------------------------------------------------------------------------
  516. # Specify the My Oracle Support Account Username password which has the patches download privileges
  517. # to be used for software updates.
  518. #
  519. # Example    : AUTOUPDATES_MYORACLESUPPORT_PASSWORD=password
  520. #------------------------------------------------------------------------------
  521. AUTOUPDATES_MYORACLESUPPORT_PASSWORD=
  522. EOF
  523. chown -R  oracle:oinstall home/oracle/etc/db_install.rsp
  524. chmod 660 home/oracle/etc/db_install.rsp
  525. cd usr/local/src/database/
  526. su - oracle -c "/usr/local/src/database/runInstaller -silent -responseFile home/oracle/etc/db_install.rsp"
  527. sleep 500
  528. /u01/app/oraInventory/orainstRoot.sh
  529. /u01/app/oracle/product/11.2.0/db_1/root.sh
  530. cat <<EOF >>/home/oracle/.bash_profile
  531. #for oracle
  532. export  ORACLE_BASE=/u01/app/oracle
  533. export  ORACLE_SID=orcl11g
  534. export  ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
  535. export  LANG="zh_CN.UTF-8"
  536. export  ORACLE_TERM=xterm
  537. export  NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
  538. export  NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
  539. export  PATH=/usr/sbin:\$PATH
  540. export  PATH=\$ORACLE_HOME/bin:\$PATH
  541. export  LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
  542. export  CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
  543. EOF
  544. ####使配置文件生效###
  545. source home/oracle/.bash_profile
  546. end_time=$(date +%s)
  547. execution_time=$((end_time - start_time))
  548. echo "脚本执行时间：${execution_time} 秒"

三、部署DG

（一）基础环境配置

两个节点都执行：
修改主备库的主机名并进行hosts文件配置

  1. [root@myoracle ~]# hostnamectl set-hostname primarynode  #主库
  2. [root@myoracle ~]# hostnamectl set-hostname standbynode  #备库
  1. cat <<EOF >>/etc//etc/hosts
  2. 192.168.59.249 primarynode
  3. 192.168.59.250 standbynode
  4. EOF

注意：使用一键脚本安装的需要注意修改listener.ora和tnsnames.ora两个文件对应的HOST。
路径为/u01/app/oracle/product/11.2.0/db_1/network/admin

（二）DG1配置:

1、开启归档

关闭数据库

  1. SQL> shutdown immediate

启动数据库到 mount

  1. SQL> startup mount;

开启归档

  1. SQL> alter database archivelog;

打开数据库

  1. SQL> alter database open;

查询是否启用归档

  1. SQL>archive log list


2、主库启用强制记录日志功能

查询是否启用强制记录日志

  1. SQL>select force_logging from v$database;

如果未启用，则使用下面语句来开启强制记录日志

  1. SQL> alter database force logging;

3、主库参数配置

一共11个参数需要进行配置。这是关键，如果参数有误，那么搭建会失败。
1、db_unique_name

  1. SQL> alter system set db_unique_name = 'orcl11g' scope=spfile;

2、log_archive_config

  1. SQL> alter system set log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)' scope=spfile;

3、log_archive_dest_1
查看归档日志的目录位置

  1. SQL> SELECT NAME, ARCHIVED, DEST_ID, COMPLETION_TIME FROM V$ARCHIVED_LOG ORDER BY COMPLETION_TIME DESC;

  1. 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

  1. 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

  1. SQL> alter system set log_archive_dest_state_1 = ENABLE;

6、log_archive_dest_state_2

  1. SQL> alter system set log_archive_dest_state_2 = ENABLE;

7、db_file_name_convert
查找数据文件的位置

  1. SQL> select name from v$datafile;
  1. 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
查看在线日志文件的位置：

  1. SQL> select member from v$logfile;

  1. SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g' scope=spfile;

9、standby_file_management

  1. SQL> alter system set standby_file_management=auto scope=spfile;

10、fal_client

  1. SQL> alter system set fal_client='tnsorcl11g' scope=both;

11、fal_server

  1. SQL> alter system set fal_server='tnsorcl11gstandby' scope=both;

4、主库静态监听配置

  1. [oracle@primarynode admin]$cd /u01/app/oracle/product/11.2.0/db_1/network/admin
  1. [oracle@primarynode admin]$ cat listener.ora
  2. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.

  5. LISTENER =
  6.   (DESCRIPTION_LIST =
  7.     (DESCRIPTION =
  8.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  9.       (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
  10.     )
  11.   )
  12. SID_LIST_LISTENER = 
  13.   (SID_LIST = 
  14.     (SID_DESC = 
  15.       (GLOBAL_DBNAME=orcl11g)  #修改GLOBAL_DBNAME
  16.       (SID_NAME = orcl11g)      #修改SID_NAME
  17.       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
  18.     ) 
  19.   ) 
  20. ADR_BASE_LISTENER = /u01/app/oracle

  1. [oracle@primarynode admin]$ cat tnsnames.ora
  2. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  3. # Generated by Oracle configuration tools.
  4. tnsorcl11g =
  5.   (DESCRIPTION =
  6.     (ADDRESS_LIST =
  7.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
  8.     )
  9.     (CONNECT_DATA =
  10.       (SERVICE_NAME = orcl11g)
  11.     )
  12.   )

  14. tnsorcl11gstandby =
  15.   (DESCRIPTION =
  16.     (ADDRESS_LIST =
  17.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
  18.     )
  19.     (CONNECT_DATA =
  20.       (SERVICE_NAME = orcl11g)
  21.     )
  22.   )


查看监听的状态

  1. [oracle@primarynode admin]$ lsnrctl status

（三）DG2配置

1、密码文件

在dataguard中，主库与备库sys密码需一致，需要将主库的密码文件拷贝到备库中。

  1. [oracle@primarynode admin]$ cd $ORACLE_HOME/dbs
  2. [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、在主库生成初始化参数文件

  1. SQL> create pfile from spfile;

2、拷贝主库的参数文件到备库

  1. [oracle@primarynode dbs]$ scp initorcl11g.ora oracle@192.168.59.250:/u01/app/oracle/product/11.2.0/db_1/dbs

3、修改备库的参数文件内容

  1. [oracle@standbynode dbs]$ cat initorcl11g.ora
  2. orcl11g.__db_cache_size=654311424
  3. orcl11g.__java_pool_size=16777216
  4. orcl11g.__large_pool_size=33554432
  5. orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  6. orcl11g.__pga_aggregate_target=637534208
  7. orcl11g.__sga_target=956301312
  8. orcl11g.__shared_io_pool_size=0
  9. orcl11g.__shared_pool_size=234881024
  10. orcl11g.__streams_pool_size=0
  11. *.audit_file_dest='/u01/app/oracle/admin/orcl11g/adump'
  12. *.audit_trail='db'
  13. *.compatible='11.2.0.4.0'
  14. *.control_files='/u01/app/oracle/oradata/orcl11g/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl'
  15. *.db_block_size=8192
  16. *.db_domain=''
  17. *.db_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g'
  18. *.log_file_name_convert='/u01/app/oracle/oradata/orcl11g','/u01/app/oracle/oradata/orcl11g'
  19. *.db_name='orcl11g'
  20. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  21. *.db_recovery_file_dest_size=4385144832
  22. *.db_unique_name='orcl11gdg'
  23. *.diagnostic_dest='/u01/app/oracle'
  24. *.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)'
  25. *.fal_client='tnsorcl11gstandby'
  26. *.fal_server='tnsorcl11g'
  27. *.log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)'
  28. *.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11GDG/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11gdg'
  29. *.log_archive_dest_2='SERVICE=tnsorcl11g LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g'
  30. *.log_archive_dest_state_1='ENABLE'
  31. *.log_archive_dest_state_2='ENABLE'
  32. *.memory_target=1580204032
  33. *.open_cursors=300
  34. *.processes=150
  35. *.remote_login_passwordfile='EXCLUSIVE'
  36. *.standby_file_management='AUTO'
  37. *.undo_tablespace='UNDOTBS1'


注意：里面涉及到路径的需要手动创建

  1. [oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/admin/orcl11g/adump
  2. [oracle@standbynode oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL11G/archivelog
  3. [oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/oradata/orcl11g
  4. [oracle@standbynode fast_recovery_area]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl11g/

使用pfile文件创建spfile文件
登陆到idle数据库

  1. [oracle@standbynode dbs]$sqlplus / as sysdba；
  2. SQL> create spfile from pfile；

  1. SQL> startup nomount

如有以下报错，需要进行以下配置:

  1. vi /etc/fstab
  2. 新增
  3. tmpfs                   /dev/shm                tmpfs   defaults,size=4096M        0 0

  1. [root@standbynode ~]# mount -o remount /dev/shm

然后继续执行 startup nomount

  1. SQL> startup nomount


4、配置静态监听

  1. [oracle@standbynode dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
  1. [oracle@standbynode admin]$ vi listener.ora
  2. 新增以下内容
  3. # listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
  4. # Generated by Oracle configuration tools.

  6. LISTENER =
  7.   (DESCRIPTION_LIST =
  8.     (DESCRIPTION =
  9.       (ADDRESS_LIST = 
  10.         (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
  11.       )
  12.     )
  13.   )

  15. SID_LIST_LISTENER = 
  16.   (SID_LIST = 
  17.     (SID_DESC = 
  18.       (GLOBAL_DBNAME=orcl11g)
  19.       (SID_NAME = orcl11g)
  20.       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
  21.     ) 
  22.   ) 

  24. ADR_BASE_LISTENER = /u01/oracle

配置tnsnames.ora 文件

  1. [oracle@standbynode admin]$ cat tnsnames.ora
  2. tnsorcl11g =
  3.   (DESCRIPTION =
  4.     (ADDRESS_LIST =
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
  6.     )
  7.     (CONNECT_DATA =
  8.       (SERVICE_NAME = orcl11g)
  9.     )
  10.   )

  12. tnsorcl11gstandby =
  13.   (DESCRIPTION =
  14.     (ADDRESS_LIST =
  15.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
  16.     )
  17.     (CONNECT_DATA =
  18.       (SERVICE_NAME = orcl11g)
  19.     )
  20.   )

6、测试网络连通性
主备之间互相测试连通性

  1. [oracle@standbynode admin]$ tnsping tnsorcl11g
  2. [oracle@primarynode dbs]$ tnsping tnsorcl11gstandby



7、在主库进行登录测试

  1. [oracle@primarynode dbs]$ sqlplus sys/password@tnsorcl11g as sysdba

  1. [oracle@primarynode dbs]$ sqlplus sys/password@tnsorcl11gstandby as sysdba


8、使用duplicate创建物理standby（有很多种办法，今天只分享这一种）
（1）连接到主备库

  1. [oracle@primarynode dbs]$ rman target sys/password@tnsorcl11g auxiliary sys/password@tnsorcl11gstandby nocatalog

（2）使用duplicate复制数据库

  1. RMAN> duplicate target database for standby from active database nofilenamecheck;


9、添加主库和备库的standby日志组
先查看redo日志的位置

  1. SQL> select member from v$logfile;


添加主库的standby日志组
编写4个日志组，根据上面查出来的编号进行排序。

  1. SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl11g/redo04.log' size 50M;
  2. SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl11g/redo05.log' size 50M;
  3. SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl11g/redo06.log' size 50M;
  4. SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl11g/redo07.log' size 50M;


添加备库的standby日志组

  1. SQL>alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl11g/redo04.log' size 50M;
  2. SQL>alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl11g/redo05.log' size 50M;
  3. SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl11g/redo06.log' size 50M;
  4. SQL>alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl11g/redo07.log' size 50M;


10、开始同步数据库，在备库上执行

  1. SQL>alter database open;


11、开启实时同步

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


至此DG搭建完成。

四、查看主备库状态

主库检查

  1. SQL>set line 200
  2. set pagesize 200
  3. select name,remote_archive,database_role,guard_status,db_unique_name from v$database;

备库检查

  1. SQL>set line 200
  2. set pagesize 200
  3. select name,remote_archive,database_role,guard_status,db_unique_name from v$database;

查询主备库日志是否同步（确保最大 SEQUENCE#相同）

  1. SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY 
    SEQUENCE#;


检查日志是否被备库使用（APPLIED 值为 yes）

  1. SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

五、DataGuard测试

在主库上创建测试表并插入数据

  1. SQL> create table test(id  number,name  varchar2(255));
  2. insert into test values(1,'zhangsan');
  3. insert into test values(2,'lisi');
  4. commit;

在standby数据库上查询测试表，验证数据同步成功

  1. SQL> select * from test;

六、日常运维必备命令

主备库角色状态查询

  1. SQL> select switchover_status,database_role from v$database;


查看dataguard最新状态

  1. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
  2. set line 160
  3. set pagesize 1000
  4. col type for a20
  5. select process, status, thread#,sequence#, block#, blocks from v$managed_standby;
  6. select GROUP#,THREAD#,SEQUENCE#,STATUS,LAST_CHANGE#,LAST_TIME from v$standby_log;
  7. select START_TIME,TYPE, ITEM,UNITS,SOFAR,TIMESTAMP from v$recovery_progress where ITEM='Last Applied Redo';

查看redo_log

主库查看redo log

  1. SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;

备库查看standby log

  1. SQL> select group#,thread#,bytes/1024/1024M,status,last_change#,last_time from v$standby_log;


主、备库查看当前日志状况

  1. SQL> select sequence#,status from v$log;

手动启动MRP0进程

  1. SQL> alter database recover managed standby database disconnect from session
  2. SQL> select open_mode,current_scn from v$database;
  3. SQL> select * from v$standby_log;
  4. SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS,THREAD# from v$managed_standby;

开启数据库闪回

  1. SQL> alter database flashback on;

重启监听并查看状态

  1. [oracle@myoracle admin]$ lsnrctl stop
  2. [oracle@myoracle admin]$ lsnrctl start
  3. [oracle@myoracle admin]$ lsnrctl status

如看不到服务，需执行以下命令：
显示服务名：

  1. SQL>show parameter service_names

强制注册服务：

  1. SQL>alter system register;


主备库切换演练

1、主库切换到备库

  1. SQL> alter database commit to switchover to physical standby;
  2. SQL> alter database commit to switchover to physical standby with session shutdown;
  3. --如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。
  4. SQL> shutdown immediate
  5. SQL> startup mount

2、备库切换到主库

  1. SQL> alter database commit to switchover to primary; 
  2. SQL> alter database open;
  3. 原主库(新备库)执行APPLY LOG命令
  4. --启用备库mount状态下的APPLY LOG
  5. SQL> alter database recover managed standby database disconnect from session;
  6. --启用open状态（READ ONLY WITH APPLY）下的APPLY LOG
  7. SQL> alter database recover managed standby database cancel;
  8. SQL> alter database open;
  9. SQL> alter database recover managed standby database disconnect from session;
  10. SQL> select switchover_status,database_role,open_mode from v$database;

恢复主备GAP（适用于11g以后版本）

  1. SQL> recover database from service orcl11g noredo using compressed backupset;

同步情况检查

  1. SQL>archive log list; #检查当前log号  Current log sequence和主库一致

查看DG库归档是否有缺失

  1. SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

开启归档应用

  1. SQL>alter database recover managed standby database  disconnect from session;

如果开启了实时归档，可以执行以下命令

  1. SQL>alter database recover managed standby database cancel;
  2. SQL>alter database open;
  3. SQL>alter database recover managed standby database using current logfile disconnect;

开启和关闭 DG

开启 DG

  1. 启动备库（mount）——启动主库（open）——启动备库日志同步
  2. 登录备库：$lsnrctl start
  3. SQL>startup mount;
  4. 登录主库：$lsnrctl start
  5. SQL>startup
  6. 登录备库：
  7. SQL> alter database recover managed standby database disconnect from session;

关闭 DG（请确保应用程序已经断开数据库连接或关闭）

  1. 关闭备库日志同步——关闭主库——关闭备库
  2. 登录备库：
  3. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  4. 登录主库：
  5. SQL>shutdown  immediate
  6. $lsnrctl  stop
  7. 登录备库
  8. SQL>shutdown  immediate
  9. $lsnrctl  stop


参考链接：https://zhuanlan.zhihu.com/p/669889767

文中的概念来源于互联网，如有侵权，请联系我删除。

欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。欢迎觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。


数据库sql数据库oracle数据库installoracle
文章转载自小周的数据库进阶之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论