文章目录
1. 前言
Oracle Data Guard可确保企业数据的高可用性,数据保护和灾难恢复。
Oracle Data Guard提供了一套全面的服务,可以创建,维护,管理和监视一个或多个备用数据库,以使生产Oracle数据库能够在灾难和数据损坏中幸免。Oracle Data Guard将这些备用数据库维护为生产数据库的副本,然后,如果生产数据库由于计划内或计划外的停机而变得不可用,则Oracle Data Guard可以将任何备用数据库切换为生产角色,从而最大程度地减少与停机相关的停机时间。Oracle Data Guard可以与传统的备份,还原和集群技术一起使用,以提供高水平的数据保护和数据可用性。
借助Oracle Data Guard,管理员可以选择通过将资源密集型备份卸载并向备用系统报告操作来提高生产数据库的性能。(来源网络)
本次测试环境的一主两备规划如下:根据dataguard的搭建方案,在Primary库上安装数据库软件,并建监听和实例,在Standby库上安装数据库软件,并建监听,但不建实例。
2. Master服务器Oracle数据库安装与配置说明
本节说明master服务器(192.183.3.112)Oracle 18c数据库服务器的安装与配置情况,以便日后开发与管理参考。
2.1 安装前准备
2.1.1 数据库下载
说明:目标服务器(192.183.3.112)安装的是非图形界面系统,需静默方式安装数据库,系统版本与内核情况查询如下:
[root@master ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
[root@master ~]# cat /proc/version
Linux version 3.10.0-1062.1.2.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Mon Sep 30 14:19:46 UTC 2019
2.1.2 操作系统软硬件检查
- 物理内存要求
[root@master ~]# grep MemTotal /proc/meminfo
MemTotal: 7966904 kB
- 虚拟内存要求
对应上表可知虚拟内存要求,查看虚拟内存命令(约62G):
[root@master ~]# grep SwapTotal /proc/meminfo
SwapTotal: 8257532 kB
- /tmp空间要求
/tmp磁盘空间至少1GB,如果不足1G需设置oracle用户的TMP和TMPDIR环境变量。
查询命令:# df -h /tmp
[root@master ~]# df -h /tmp
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 50G 6.7G 44G 14% /
- 安装目录要求
对数据库安装目录,需至少7.5G空间,本次安装目录为/home/app/oracle下。
[root@master ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 50G 6.7G 44G 14% /
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 3.8G 0 3.8G 0% /dev/shm
tmpfs 3.8G 8.6M 3.8G 1% /run
tmpfs 3.8G 0 3.8G 0% /sys/fs/cgroup
/dev/sda1 494M 125M 369M 26% /boot
/dev/mapper/centos-home 408G 21G 387G 6% /home
tmpfs 779M 0 779M 0% /run/user/0
- RPM包要求
binutils-2.23.52.0.1-12.el7.x86_64
compat-libcap1-1.10-3.el7.x86_64
compat-libstdc++-33-3.2.3-71.el7.i686
compat-libstdc++-33-3.2.3-71.el7.x86_64
gcc-4.8.2-3.el7.x86_64
gcc-c++-4.8.2-3.el7.x86_64
glibc-2.17-36.el7.i686
glibc-2.17-36.el7.x86_64
glibc-devel-2.17-36.el7.i686
glibc-devel-2.17-36.el7.x86_64
ksh
libaio-0.3.109-9.el7.i686
libaio-0.3.109-9.el7.x86_64
libaio-devel-0.3.109-9.el7.i686
libaio-devel-0.3.109-9.el7.x86_64
libgcc-4.8.2-3.el7.i686
libgcc-4.8.2-3.el7.x86_64
libstdc++-4.8.2-3.el7.i686
libstdc++-4.8.2-3.el7.x86_64
libstdc++-devel-4.8.2-3.el7.i686
libstdc++-devel-4.8.2-3.el7.x86_64
libXi-1.7.2-1.el7.i686
libXi-1.7.2-1.el7.x86_64
libXtst-1.2.2-1.el7.i686
libXtst-1.2.2-1.el7.x86_64
make-3.82-19.el7.x86_64
sysstat-10.1.5-1.el7.x86_64
unixODBC-2.3.1-6.el7.x86_64 or later
unixODBC-2.3.1-6.el7.i686 or later
unixODBC-devel-2.3.1-6.el7.x86_64 or later
unixODBC-devel-2.3.1-6.el7.i686 or later
安装前查看需要的RPM包安装情况,安装缺失的RPM包,这里就不赘述。
[root@master ~]# rpm -q binutils compat-libcap1 compat-libstdc++-33.i686 compat-libstdc++-33.x86_64 gcc gcc-c++ glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 ksh libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make sysstat unixODBC.i686 unixODBC.x86_64 unixODBC-devel.i686 unixODBC-devel.x86_64
[root@master ~]# yum -y install compat-libcap1 compat-libstdc++-33.i686 compat-libstdc++-33.x86_64 glibc.i686 glibc-devel.i686 libaio.i686 libaio-devel.i686 ksh libgcc.i686 libstdc++.i686 libstdc++-devel.i686 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 unixODBC.i686 unixODBC-devel.i686 sysstat unixODBC.x86_64 unixODBC-devel.x86_64
- 关闭SELinux
[root@master ~]# vim /etc/sysconfig/selinux
- 创建用户、用户组以及安装目录
[root@master ~]# groupadd -g 54321 oinstall
[root@master ~]# /usr/sbin/groupadd -g 54322 dba
[root@master ~]# groupadd -g 54323 oper
[root@master ~]# /usr/sbin/groupadd -g 54324 backupdba
[root@master ~]# /usr/sbin/groupadd -g 54325 dgdba
[root@master ~]# useradd -g oinstall -G dba oracle oper
[root@master ~]# usermod -a -G backupdba,dgdba,oper oracle
[root@master ~]# mkdir -p /home/app/oracle
[root@master ~]# mkdir -p /home/app/oraInventory
[root@master ~]# mkdir -p /home/app/oracle/product/18.3.0/dbhome_1
[root@master ~]# chown -R oracle:oinstall /home/app
[root@master ~]# chmod -R 775 /home/app
[oracle@master ~]$ cd /home/app/oracle/product/18.3.0/dbhome_1
注意:将安装文件解压到Oracle Home下,这里和Oracle 18c之前的版本不同,之前的版本解压的安装文件可以在任何位置,Oracle解压的安装文件必须在Oracle Home下,因为仅从Oracle home运行runInstaller命令,不要从任何其他位置运行runInstaller。
[oracle@master dbhome_1]$ unzip -q /home/softwares/oracle/LINUX.X64_180000_db_home.zip
- 配置Oracle用户shell limit
[root@master ~]# vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock 3145728
oracle hard memlock 3145728
- 修改系统内核参数
建议参考值:
参数详解如下:
1)fs.aio-max-nr = 1048576:同时可以拥有的的异步IO请求数目,1048576 即 1024*1024 也就是1024K个。
2)fs.file-max = 6553600:系统允许打开的文件数。
3)kernel.shmall = 2097152:指定任意时刻,系统中可以分配的所有共享内存段的总和的最大值,也可以保留默认值,这个值太小有可能导致数据库启动报错,这个是8G的值,如果大于8G需要调整,计算公式为:内存(G)10241024*1024/4096,4096为getconf PAGE_SIZE得到分页大小。
4)kernel.shmmax = 536870912:Linux进程可以分配的单独共享内存段的最大值,最小值536870912,最大值为比物理内存小1byte,一般推荐设置为内存总大小的一半,这个值的设置应该大于SGA_MAX_TARGET或MEMORY_MAX_TARGET的值,因此对于安装Oracle数据库的系统,shmmax的值应该比内存的二分之一大一些。
5)kernel.shmmni = 4096:设置系统级最大共享内存段数量,推荐最小值为4096。
6)kernel.sem = 2010 285420 100 142:从左到右分别为SEMMSL、SEMMNS、SEMOPM和SEMMNI。
SEMMSL:设置每个信号灯组中信号灯最大数量,推荐的最小值是250,对于系统中存在大量并发连接的系统,推荐将这个值设置为PROCESSES初始化参数加10。
SEMMNS:设置系统中信号灯的最大数量,操作系统在分配信号灯时不会超过LEAST(SEMMNS,SEMMSLSEMMNI),事实上,如果SEMMNS的值超过了SEMMSLSEMMNI是非法的,因此推荐SEMMNS的值就设置为SEMMSL*SEMMNI,Oracle推荐SEMMNS的设置不小于32000,假如数据库的PROCESSES参数设置为2000,则SEMMNS的设置应为:
SQL> select (2000+10)*142 from dual;
SEMOPM:设置每次系统调用可以同时执行的最大信号灯操作的数量,由于一个信号灯组最多拥有SEMMSL个信号灯,因此有推荐将SEMOPM设置为SEMMSL的值,Oracle验证的10.2和11.1的SEMOPM的配置为100。
SEMMNI:设置系统中信号灯组的最大数量,Oracle10g和11g的推荐值为142。
编辑内核参数:
[root@master ~]# vim /etc/sysctl.conf
# For oracle
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 2010 285420 100 142
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
[root@master ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 2010 285420 100 142
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
- 编辑登录配置文件
在文本最后添加:session required pam_limits.so或者session required /lib/security/pam_limits.so使shell limit生效。
[root@master ~]# vim /etc/pam.d/login
- 配置/etc/hosts文件
在/etc/hosts文件中添加IP地址和域名的映射关系,进入hosts文件,在文件末尾加上本机实际IP和主机用户名。
[root@master ~]# vim /etc/hosts
192.183.3.112 master
192.183.3.194 slave1
192.183.3.145 slave2
- 配置/etc/profile
[root@master ~]# vim /etc/profile
- 设置环境变量
[root@master ~]# su - oracle
[oracle@master ~]$ vim ~/.bash_profile
添加ORACLE_BASE和ORACLE_SID到bash_profile文件中,如下:
# For Oracle
export ORACLE_BASE=/home/app/oracle
export ORACLE_SID=primdb
export ORACLE_HOME=$ORACLE_BASE/product/18.3.0/dbhome_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
注意:除了ORACLE_BASE和ORACLE_SID,(/etc/profile和.bash_profile中)不设置任何oracle相关环境变量(ORACLE_HOME、PATH、LD_LIBRARY_PATH等)。
生效修改后的bash_profile:
[oracle@master ~]$ source /home/oracle/.bash_profile
2.2 安装Oracle 18c数据库
2.2.1 创建oraInst.loc
[root@master ~]# vim /etc/oraInst.loc
inventory_loc=/home/app/oraInventory
inst_group=oinstall
[root@master ~]# chown oracle:oinstall /etc/oraInst.loc
[root@master ~]# chmod 664 /etc/oraInst.loc
2.2.2 创建响应文件
- 创建Oracle数据库安装的响应文件
默认情况下,Oracle响应文件的模板在/home/app/oracle/product/18.3.0/dbhome_1/install/response/目录中,Oracle数据库安装的响应文件为db_install.rsp,这里只需根据实际的安装需求对该文件的参数进行相应的修改,即可用于安装数据库。
更改文件权限保护响应文件:[root@master ~]# chmod 600 /home/app/oracle/product/18.3.0/dbhome_1/install/response/db_install.rsp
本次安装修改的参数主要如下:
[root@master response]# egrep -v “#|$” db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/home/app/oraInventory
ORACLE_HOME=/home/app/oracle/product/18.3.0/dbhome_1
ORACLE_BASE=/home/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.CLUSTER_NODES=
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=primdb
oracle.install.db.config.starterdb.SID=primdb
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.password.ALL=123456
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/home/app/oracle/oradata
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/home/app/oracle/flash_recovery_area
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
- 创建数据库配置响应文件
Oracle数据库创建的响应文件为dbca.rsp,这里我们只需要根据实际的需求对该文件的参数进行相应的修改,即可用于创建数据库,这里直接修改/home/app/oracle/product/18.3.0/dbhome_1/assistants/dbca/dbca.rsp,如下:
[root@master ~]# egrep -v “#|$” /home/app/oracle/product/18.3.0/dbhome_1/assistants/dbca/dbca.rsp
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=primdb
sid=primdb
databaseConfigType=
RACOneNodeServiceName=
policyManaged=
createServerPool=
serverPoolName=
cardinality=
force=
pqPoolName=
pqCardinality=
createAsContainerDatabase=
numberOfPDBs=
pdbName=
useLocalUndoForPDBs=
pdbAdminPassword=
nodelist=
templateName=/home/app/oracle/product/18.3.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=123456
systemPassword= 123456
oracleHomeUserPassword=
emConfiguration=
emExpressPort=5500
runCVUChecks=
dbsnmpPassword=
omsHost=
omsPort=
emUser=
emPassword=
dvConfiguration=
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=
datafileJarLocation=/home/app/oracle/product/18.3.0/dbhome_1/assistants/dbca/templates/
datafileDestination=/home/app/oracle/oradata/primdb/
recoveryAreaDestination=/home/app/oracle/fast_recovery_area
storageType=FS
diskGroupName=
asmsnmpPassword=
recoveryGroupName=
characterSet=AL32UTF8
nationalCharacterSet=
registerWithDirService=
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=
variablesFile=
variables=
initParams=
sampleSchema=
memoryPercentage=
databaseType=
automaticMemoryManagement=
totalMemory=
- 监听响应文件配置
NET配置的响应文件使用Oracle默认即可,无需修改,该文件路径:/home/app/oracle/product/18.3.0/dbhome_1/assistants/netca/netca.rsp。
2.2.3 安装过程
- 安装Oracle数据库
[oracle@master ~]$ $ORACLE_HOME/runInstaller -silent -force -noconfig -ignorePrereq oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.3.0 oracle.install.option=INSTALL_DB_SWONLY UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/home/app/oraInventory ORACLE_BASE=/home/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.OSDBA_GROUP=dba oracle.install.db.OSOPER_GROUP=oper oracle.install.db.OSBACKUPDBA_GROUP=backupdba oracle.install.db.OSDGDBA_GROUP=dgdba oracle.install.db.OSKMDBA_GROUP=kmdba oracle.install.db.OSRACDBA_GROUP=dba oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
或者利用响应文件安装,本文利用响应文件安装:
[oracle@master ~]$ cd /home/app/oracle/product/18.3.0/dbhome_1/
[oracle@master dbhome_1]$ ./runInstaller -silent -responseFile /home/app/oracle/product/18.3.0/dbhome_1/install/response/db_install.rsp
root用户下执行:
[root@master ~]# /home/app/oracle/product/18.3.0/dbhome_1/root.sh
Check /home/app/oracle/product/18.3.0/dbhome_1/install/root_slave1_2019-11-04_17-07-18-568821410.log for the output of root script
- 查看安装日志
[root@master ~]# vim /home/app/oracle/product/18.3.0/dbhome_1/install/root_slave1_2019-11-04_17-07-18-568821410.log
- 建监听网络
[oracle@master dbhome_1]$ netca -silent -responsefile /home/app/oracle/product/18.3.0/dbhome_1/assistants/netca/netca.rsp
Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /home/app/oracle/product/18.3.0/dbhome_1/assistants/netca/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
/home/app/oracle/product/18.3.0/dbhome_1/bin/lsnrctl start LISTENER
Listener Control complete.
Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0
- 创建Oracle数据库
[oracle@master dbhome_1]$ dbca -silent -createDatabase -responseFile /home/app/oracle/product/18.3.0/dbhome_1/assistants/dbca/dbca.rsp
同样的,可以通过命令查看创建数据库的进度:
[oracle@master ~]$ tail -100f $ORACLE_BASE/cfgtoollogs/dbca/$ORACLE_SID/$ORACLE_SID.log
- 建库后实例与监听检查
[oracle@master ~]$ ps -ef | grep ora_ | grep -v grep | wc -l
[oracle@master ~]$ ps -ef | grep ora_ | grep -v grep
[oracle@master ~]$ lsnrctl status
[oracle@master ~]$ sqlplus / as sysdba
2.3 配置与测试数据库
2.3.1 监听配置
Oracle监听器运行于数据库服务器端一个进程,用于监听所有来自客户端的连接请求,并提供处理数据库服务方面的请求,监听分为2种:状态为UNKOWN的静态监听和状态READY动态监听。
虽然动态注册相对静态注册的监听结果更为准确,而且不需要额外的配置,但是它依赖于PMON进程,如果PMON进程比较忙或者是包括BUG在内的其他原因不能及时向监听更新服务状态,很容易导致监听认为连接数满而拒绝连接,就算重启监听也没有用,所以这里建议同时配置静态监听。
配置方法:可以通过Oracle图形界面工具Net Manager或Net Configuration Assistant来配置,这个方法比较简单不作介绍,这里只说明通过“$ORACLE_HOME/network/admin/listener.ora”文件来配置静态监听。
- 配置静态监听
[oracle@master ~]$ vim /home/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
添加如下内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = primdb)
(ORACLE_HOME = /home/app/oracle/product/18.3.0/dbhome_1)
(SID_NAME = primdb)
)
)
- 配置静态监听后状态
[oracle@master ~]$ lsnrctl stop
[oracle@master ~]$ lsnrctl start
[oracle@master ~]$ lsnrctl status
2.3.2 其他配置
2.3.2.1 归档设置
- 设置归档路径(暂时不设置,等搭完DG再设置)
开启archive log模式时,默认归档目录为db_recovery_file_dest指定,也可以pfile/spfile中可以自定义路径。为防止归档日志的误删,设置双路径归档,针对本服务器的分区情况我们增设的归档路径依然在home目录下,只是与flash recovery area路径区分开来,本服务器的分区大小情况如下:
[oracle@master ~]$ mkdir -p /home/app/oracle/archivelog
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = "LOCATION=/home/app/oracle/archivelog mandatory";
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/app/oracle/fast_recovery_area/PRIMARYDB/archivelog';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_;
SQL> archive log list;(可以看到最新设置的归档路径)
SQL> alter system switch logfile;
SQL> select thread#,sequence#,name,IS_RECOVERY_DEST_FILE from v$archived_log where name is not null;
可看到归档在两个目录都已生成。
归档日志命名格式:
SQL> show parameter archive_format;
NAME TYPE VALUE
------------------------------------ ----------- -----
log_archive_format string %t_%s_%r.dbf
其中,%t 代表线程号,thread#,%S 代表日志序列号,sequence#,%r代表重做日志的id(resetlog id)。
归档进程(默认4个,暂不做修改):
SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES;
NAME TYPE VALUE
------------------------------------ ----------- -----
log_archive_max_processes integer 4
查询保证成功的归档日志文件组数(默认是1):
SQL>show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST;
- 修改redo log组以及大小
修改redo log组以及大小,为防止日志频繁切换,引起数据库性能低下问题。最好每个实例有6个重做日志组,每个重做日志组有2个成员,这两个成员存放到不同的目录,大小根据业务需求,根据观察数据库切换重做日志组的频率而定,最好15~20分钟切换一次,这里我们根据旧机的生产情况,暂设3组,每组100M。
SQL> select * from v$logfile order by group#;
SQL> alter database add logfile group 4 '/home/app/oracle/oradata/primdb/redo04.log'size 100M;
Database altered.
SQL> alter database add logfile group 5 '/home/app/oracle/oradata/primdb/redo05.log'size 100M;
SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 200 INACTIVE
3 200 INACTIVE
2 200 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance primarydb (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/home/app/oracle/oradata/primarydb/redo03.log'
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
[oracle@master ~]$ cd /home/app/oracle/oradata/primarydb/
[oracle@master primarydb]$ rm -rf redo01.log redo02.log redo03.log
SQL> alter database add logfile group 1 '/home/app/oracle/oradata/primdb/redo01.log' size 100M;
Database altered.
SQL> alter database add logfile group 2'/home/app/oracle/oradata/primdb/redo02.log' size 100M;
Database altered.
SQL> alter database add logfile group 3'/home/app/oracle/oradata/primdb/redo03.log' size 100M;
Database altered.
多次切换删除日志组4,5:
SQL> alter system checkpoint;
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
[oracle@master primarydb]$ rm -rf redo04.log redo05.log
SQL> select group#,bytes/1024/1024,status from v$log;
GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 200 INACTIVE
3 200 INACTIVE
2 200 CURRENT
- 设置归档模式
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6.4137E+10 bytes
Fixed Size 2269072 bytes
Variable Size 1.8790E+10 bytes
Database Buffers 4.5231E+10 bytes
Redo Buffers 112762880 bytes
Database mounted.
SQL> alter database archivelog;--打开归档模式
Database altered.
SQL> alter database nalter database archivelog;oarchivelog;--关闭归档模式
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
2.3.2.2 闪回配置
Flashback对于Oracle Data Guard切换或故障转后的数据库恢复是一个非常好的工具,在开启Flashback功能之前,我们必须首先开启数据库归档,否则数据库会报错:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
另外先设置db_recovery_file_dest_size后设置db_recovery_file_dest。
- 配置闪回空间大小
DB_RECOVERY_FILE_DEST参数是默认的flash recovery area的路径,里面存放有归档日志、闪回日志以及rman的备份等文件,这里设置的空间大小主要用于闪回,归档和备份后面会设置自己的专属目录。
SQL> alter system set db_recovery_file_dest_size=100g scope=both;
SQL> show parameter db_recovery;
- 闪回配置
开启Flashback功能(必须先开归档才可以设置,否则ORA-38706、ORA-38707)
SQL> select flashback_on from v$database;
SQL> alter database flashback on;
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- -----
db_flashback_retention_target integer 1440
默认能闪回1440分钟,即一天以内的数据,所以闪回空间要设置的足够大以能够容纳该时间段的数据,否则数据库将宕掉。另外flashback日志的保存期限由参数db_flashback_ retention_target控制(单位为分钟),凡是超出保存期限的闪回日志将会在快速恢复区空间吃紧时被自动删除。
注意:Oracle目前并没有相关的flashback日志删除方案,手动物理清除日志很可能引起宕机等危险,要想彻底清除flashback日志只有将flashback置于off,然后再重新开启。对于主库可以直接alter database flashback on/off,但是对于备库需要先停止日志应用,然后开启闪回,最后再次开始日志应用:
ext log sequence to archive 15
Current log sequence 15
2.3.2.3 无密码登录、回收站、审计功能限制
- 无密码限制
安全起见,限制sys和系统用户的无密码登录。
[oracle@master ~]$ vi /home/app/oracle/product/network/admin/sqlnet.ora
添加:SQLNET.AUTHENTICATION_SERVICES=(NONE)
- 禁止回收站功能:
SQL> show parameter recyclebin;
SQL> alter system set recyclebin=off scope=spfile;
- 不开启审计功能
oracle 11g推出的审计功能,不建议在生产系统中使用,因为它会针对很多操作产生审计文件.aud,加大系统负荷,降低系统性能。默认情况下,系统为了节省资源,减少I/0操作,审计功能是关闭的。
SQL> show parameter audit;
当AUDIT_TRAIL设置为OS时,审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成,全部这些文件均可以随时被删除或复制。
none禁用数据库审计;
os启用数据库审计,并将数据库审计记录定向到操作系统审计记录;
db启用数据库审计,并将数据库所有审计记录定向到数据库的SYS.AUD$表。
如果已经开启,则关闭,SQL如下:
SQL> alter system set audit_trail=FALSE scope=spfile;
SQL> alter system set audit_trail=NONE scope=spfile;
System altered.
重启生效:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 4877066240 bytes
Fixed Size 2212856 bytes
Variable Size 3288337416 bytes
Database Buffers 1543503872 bytes
Redo Buffers 43012096 bytes
Database mounted.
Database opened.
2.3.2.4 概要文件配置
- 创建密码函数,要求密码必须有大小写字母、数字与指定字符组成,不能少于8位。
CREATE OR REPLACE FUNCTION verify_function_11GR2
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
lowerchararray varchar2(52);
upperchararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
lowerchararray:= 'abcdefghijklmnopqrstuvwxyz';
upperchararray:='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password contains at least one upperletter,one lowerletter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findlowerchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20008, 'Password must contain at least one \
digit, one upper character,one lower character and one punctuation');
END IF;
-- 2. Check for the lowercharacter
<<findlowerchar>>
ischar:=FALSE;
FOR i IN 1..length(lowerchararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(lowerchararray,i,1) THEN
ischar:=TRUE;
GOTO findupperchar;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, one upper character,one lower character and one punctuation');
END IF;
-- 3. Check for the uppercharacter
<<findupperchar>>
ischar:=FALSE;
FOR i IN 1..length(upperchararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(upperchararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, one upper character,one lower character and one punctuation');
END IF;
-- 4. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20010, 'Password must contain at least one \
digit, one upper character,one lower character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
- 创建指定的profile
-- Create profile
create profile PRIMARYDB_PROFILE limit
sessions_per_user unlimited
cpu_per_session unlimited
cpu_per_call unlimited
connect_time unlimited
idle_time unlimited
logical_reads_per_session unlimited
logical_reads_per_call unlimited
composite_limit unlimited
private_sga unlimited
failed_login_attempts 10
password_life_time 360
password_reuse_time unlimited
password_reuse_max unlimited
password_lock_time 1
password_grace_time 7
password_verify_function VERIFY_FUNCTION_11GR2;
2.3.2.5 修改控制文件里可重复使用的记录所能保存的最小天数
控制文件(Control File)是Oracle的物理文件之一,它记录了数据库的名字、数据文件与重做日志文件的名称与存储位置、数据库创建时间、当前log的SN检查点信息等信息,大小一般不超过100M。控制文件的重要性在于,一旦控制文件损坏,数据库将会宕机,控制文件是一个很小的二进制文件,用户不能编辑控制文件,控制文件的修改由Oracle自动完成。
可以通过转储文件来获得控制文件的详细信息:
SQL> alter database backup controlfile to trace;
查看转储文件位置:
SQL> show parameter user_dump_dest;
控制文件中记录的信息分成两类,一类是持续性信息(比如数据库名,数据文件名及位置等),另一类是可循环写入的(即reusable record)。对于后一类,就需要确定多久之前的信息是可以丢弃并被覆盖的,参数control_file_record_keep_time意为至少保留的时间,虽然默认值为7天,但在控制文件的空间还有富余时,超过7天的这类信息也是会被保留的,只有当控制文件的空间不足时,才会去覆盖已经在这个参数值之前记录的信息。这里我们对控制文件中存储备份记录的时间做一下配置,一般建议CONTROL_FILE_RECORD_KEEP_TIME参数值设置不小于选中数据库的RMAN备份保存周期的值。
SQL> show parameter control;
计划主库的备份时间为7天的窗口,那么设置15天的保留时间(重起生效):
SQL> alter system set control_file_record_keep_time=15 scope=spfile;
System altered.
3. Slave1/slave2服务器Oracle数据库安装与配置说明
slave1服务器(192.183.3.194)和slave2服务器(192.183.3.145)Oracle 18c数据库服务器不需要建库,其他安装与配置情况参详主库。
4. Dataguard之Primary库配置
4.1 Tns配置
[oracle@master ~]$ vim /home/app/oracle/product/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/app/oracle/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SER_STD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.194)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primdb)
)
)
SER_STD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primdb)
)
)
4.2 设置数据库强制归档与归档模式
- 将主库设置为FORCE LOGGING模式
SQL>alter database force logging;
- 开启归档模式,查看归档模式:SQL>archive log list;
- 开启Flashback功能
Flashback对于oracle Data Guard切换或故障转后的数据库恢复是一个非常好的工具,在开启Flashback功能之前,我们必须首先开启数据库归档,否则数据库会报错:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
另外先设置db_recovery_file_dest_size后设置db_recovery_file_dest。
SQL> select flashback_on from v$database;
SQL> show parameter db_recovery
SQL> alter system set db_recovery_file_dest_size=100g;
SQL> alter database flashback on;
SQL> show parameter db_flashback_retention_target;
默认能闪回1440分钟,即一天以内的数据,所以闪回空间要设置的足够大以能够容纳该时间段的数据,否则数据库将宕掉。另外flashback日志的保存期限由参数db_flashback_ retention_target控制(单位为分钟),凡是超出保存期限的闪回日志将会在快速恢复区空间吃紧时被自动删除。
注意:Oracle目前并没有相关的flashback日志删除方案,手动物理清除日志很可能引起宕机等危险,要想彻底清除flashback日志只有将flashback置于off,然后再重新开启。对于主库可以直接alter database flashback on/off,但是对于备库需要先停止日志应用,然后开启闪回,最后再次开始日志应用。
4.3 pfile相关参数修改与生成密码文件
pfile相关参数修改:
- 生成pfile
SQL>create pfile from spfile;
SQL>shutdown immediate;
- 修改pfile
[oracle@master ~]$ vim /home/app/oracle/product/18.3.0/dbhome_1/dbs/initprimdb.ora
ALTER SYSTEM SET DB_UNIQUE_NAME='primarydb' scope=both;
ALTER SYSTEM SET FAL_CLIENT = SER_PRI scope=both;
ALTER SYSTEM SET FAL_SERVER = SER_STD1,SER_STD2 scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primarydb,standbydb1,standbydb2)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primarydb' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=SER_STD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb1' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SER_STD2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb2' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=both;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area' scope=both;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb' scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area' scope=both;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb' scope=both;
- 生成pfile
SQL> create spfile from pfile;
- 生成密码文件
同一个Dataguard中所有数据库必须拥有独立的密码文件,并且保证sys用户拥有相同密码来保证redo的传输。默认Oracle自建的密码文件保存在$ORACLE_HOME/dbs/orapw$ORACLE_SID中,可以直接copy到standby库对应目录中(本案例采用该方法),如果没有该文件也可以通过如下方式重建。
[oracle@master ~]$ orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=123456 force=y ignorecase=y
4.4 STANDBY日志文件与控制文件创建
Standby redo log与主库接收到的重做日志相对应,也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile,在主库创建standby logfile是便于发生角色转换后备用。
Standby redo log创建原则:
1)确保standby redo log的大小与主库online redo log的大小一致
2)如主库为单实例数据库:standby redo log组数=主库日志组总数+1
3)如果主库是RAC数据库:standby redo log组数=(每线程的日志组数+1)*最大线程数
4)不建议复用standby redo log,避免增加额外的I/O以及延缓重做传输
- 添加standby日志文件
SQL> alter database add standby logfile group 4('/home/app/oracle/oradata/primdb/stdby01.log')size 200M;
Database altered.
SQL> alter database add standby logfile group 5('/home/app/oracle/oradata/primdb/stdby02.log')size 200M;
Database altered.
SQL> alter database add standby logfile group 6('/home/app/oracle/oradata/primdb/stdby03.log')size 200M;
Database altered.
SQL> alter database add standby logfile group 7 ('/home/app/oracle/oradata/primdb/stdby04.log')size 200M;
Database altered.
- 删除standby日志组
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
SQL>select group#,member from v$logfile order by 1;
- 创建standby控制文件
SQL> alter database create standby controlfile as '/home/app/oracle/oradata/primdb/standby01.ctl';
Database altered.
[oracle@master ~]$ cp /home/app/oracle/oradata/primdb/standby01.ctl /home/app/oracle/fast_recovery_area/PRIMDB/standby02.ctl
5. Dataguard之standby库配置
5.1 建立相应的文件目录
包括dump文件目录,闪回区,数据文件目录,可以通过show parameter dest命令查看
[root@slave1 ~]# su - oracle
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/admin/primdb/adump
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/admin/primdb/dpdump
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/admin/primdb/pfile
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/diag/rdbms
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/fast_recovery_area
[oracle@slave1 ~]$ mkdir -p /home/app/oracle/oradata/primdb
5.2 Slave1备库创建
[oracle@slave1 ~]$ dbca -silent -createDuplicateDB -primaryDBConnectionString 192.183.3.112:1521/primdb -gdbName primdb -sysPassword 123456 -sid primdb -dbUniqueName standbydb -initParams instance_name=primdb -createAsStandby
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
Look at the log file "/home/app/oracle/cfgtoollogs/dbca/standbydb/standbydb.log" for further details.
- 拷贝监听文件
[root@slave1 ~]# scp -r 192.183.3.112:/home/app/oracle/product/network/admin /home/app/oracle/product/network
修改监听配置文件:
[root@slave1 ~]# vim /home/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
LISTENER_STD1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = primdb)
(ORACLE_HOME = /home/app/oracle/product/18.3.0/dbhome_1)
(SID_NAME = primdb)
)
)
ADR_BASE_LISTENER = /home/app/oracle
- TNS配置文件
TNS文件目录$ORACLE_HOME/network/admin,测试tnsping 服务名。
[oracle@slave1 ~]$ vim/home/app/oracle/product/18.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/app/oracle/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SER_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primdb)
)
)
SER_STD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primdb)
)
)
- 修改参数文件
[oracle@slave1 ~]$ vim /home/app/oracle/product/dbs/initprimdb.ora
SQL> ALTER SYSTEM SET FAL_CLIENT = SER_STD1 scope=both;
SQL> ALTER SYSTEM SET FAL_SERVER = SER_PRI,SER_STD2 scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primarydb,standbydb1,standbydb2)' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standbydb1' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=SER_PRI ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primarydb' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SER_STD2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb2' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area' scope=spfile;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb' scope=spfile;
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> Startup;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> select open_mode from v$database;
SQL> select name,type,role,action,group# from V$DATAGUARD_PROCESS;
5.3 Slave2备库创建
[oracle@slave2 ~]$ dbca -silent -createDuplicateDB -primaryDBConnectionString 192.183.3.112:1521/primdb -gdbName primdb -sysPassword 123456 -sid primdb -dbUniqueName standbydb2 -initParams instance_name=primdb -createAsStandby
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
Look at the log file "/home/app/oracle/cfgtoollogs/dbca/standbydb2/standbydb2.log" for further details
- 拷贝监听文件
[root@slave2 ~]# scp -r 192.183.3.112:/home/app/oracle/product/network/admin /home/app/oracle/product/network
修改监听配置文件:
[root@slave2 ~]# vim /home/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
LISTENER_STD2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = primdb)
(ORACLE_HOME = /home/app/oracle/product/18.3.0/dbhome_1)
(SID_NAME = primdb)
)
)
ADR_BASE_LISTENER = /home/app/oracle
- TNS配置文件
TNS文件目录$ORACLE_HOME/network/admin,测试tnsping 服务名。
[oracle@slave2 ~]$ vim/home/app/oracle/product/18.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/app/oracle/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SER_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primdb)
)
)
SER_STD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.183.3.194)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primdb)
)
)
- 修改参数文件
[oracle@slave2 ~]$ vim /home/app/oracle/product/dbs/initprimdb.ora
修改如下内容:
SQL> ALTER SYSTEM SET FAL_CLIENT = SER_STD2 scope=both;
SQL> ALTER SYSTEM SET FAL_SERVER = SER_PRI,SER_STD1 scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primarydb,standbydb1,standbydb2)' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SER_STD1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb1' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standbydb2'scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=SER_PRI ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primarydb' scope=both;
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area' scope=spfile;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb','/home/app/oracle/oradata/primdb' scope=spfile;
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> Startup;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> select open_mode from v$database;
SQL> select name,type,role,action,group# from V$DATAGUARD_PROCESS;
6. 验证同步情况
物理备库之Active DataGuard模式:
- 查看数据库当前状态
15:31:27 SQL> select open_mode,database_role,db_unique_name from v$database;
-
取消备库的自动恢复
15:34:25 SQL> alter database recover managed standby database cancel;
Database altered. -
将备库置为只读模式
SQL> alter database open;
SQL> select open_mode,database_role,db_unique_name from v$database;
- 打开实时应用redo状态模式
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select open_mode,database_role,db_unique_name from v$database;
- 查询数据验证配置成功
Primary数据库:
15:37:37 SQL> create user nn identified by 123456;
User created.
15:37:46 SQL> grant dba to nn;
Grant succeeded.
15:37:57 SQL> conn nn/123456;
Connected.
15:38:22 SQL> create table t1(tid number,tname varchar2(20));
Table created.
- Standby开启flashback
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered
2020年07月16日
9盆花草两周养死了6盆,就因为手欠施肥,emm…
哥哥说:呢呢,补品是不能乱吃的,原本想着这9盆让你练个手养个把月,你厉害,2周就给我报销了…