Oracle 19c(19.3) 单机数据库静默安装
一、环境介绍
操作系统：CentOS 7.4
数据库版本：Oracle database 19.3
主机名：oradb31
IP地址：192.168.1.31
安装目录：/opt/oracle/product/19.3/db_1
数据库名称：orcl
字符集：AL32UTF8
二、环境准备
2.1：关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
2.2：禁用NetworkManager服务
systemctl stop NetworkManager
systemctl disable NetworkManager
2.3：禁用SELINUX
[root@adg19c ~]# setenforce 0
setenforce: SELinux is disabled
cat /etc/selinux/config
SELINUX=disabled
2.4：配置HOSTS解析
cat /etc/hosts
192.168.1.31 oradb31
2.5：修改主机名
cat /etc/sysconfig/network
[root@adg19c ~]# sed -i 's#^NETWORKING_IPV6=.*$#NETWORKING_IPV6=no#' /etc/sysconfig/network
2.6：配置NOZEROCONFIG
cat >> /etc/sysconfig/network
NOZEROCONF=yes
2.7：创建组和用户
# 创建dba和onstall两个用户组
groupadd -g 501 dba
groupadd -g 502 oinstall
groupadd -g 503 backupdba
groupadd -g 504 dgdba
groupadd -g 505 kmdba
groupadd -g 506 racdba
# 创建oracle用户
useradd -u 503 -g oinstall -G dba,backupdba,dgdba,kmdba,racdba oracle
# 配置oracle用户密码
passwd oracle
2.8：创建安装目录并赋权
# 创建目录
mkdir -p /opt/oraInventory
mkdir -p /opt/oracle/product/19.3/db_1
# 赋权
chown -R oracle:oinstall /opt
chmod -R 775 /opt/oraInventory
2.9：配置用户环境变量
cat /home/oracle/.bash_profile
export TMP=/tmp
export ORACLE_HOSTNAME=oradb31
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/19.3/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/bin:/usr/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA AL32UTF16
umask 022
[root@adg19c ~]# source /home/oracle/.bash_profile
2.10：配置系统环境变量
cat /etc/profile
if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
source /etc/profile
2.11. 修改系统内核参数
[root@adg19c ~]# cat >> /etc/sysctl.conf <<EOF
kernel.shmall = 4294967296
kernel.sem = 5130 65280 510 256
kernel.shmmni = 4096
kernel.shmmax = 549755813888 #support max TotalMemory 512GB
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744
fs.aio-max-nr = 1048576
vm.swappiness = 10
vm.dirty_background_ratio = 20
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
net.ipv4.tcp_sack = 0
net.ipv4.tcp_timestamps = 0
net.ipv4.conf.default.rp_filter = 0
net.ipv4.tcp_wmem = 262144
net.ipv4.tcp_rmem = 4194304
EOF
[root@adg19c ~]# /sbin/sysctl -p
kernel.shmall = 4294967296
kernel.sem = 5130 65280 510 256
kernel.shmmni = 4096
kernel.shmmax = 549755813888 #support max TotalMemory 512GB
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744
fs.aio-max-nr = 1048576
vm.swappiness = 10
vm.dirty_background_ratio = 20
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
net.ipv4.tcp_sack = 0
net.ipv4.tcp_timestamps = 0
net.ipv4.conf.default.rp_filter = 0
net.ipv4.tcp_wmem = 262144
net.ipv4.tcp_rmem = 4194304
2.12. 配置LIMITS限制参数
cat >> /etc/security/limits.conf <<EOF
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft memlock 3145728
oracle hard memlock 3145728
2.13. 配置PAM验证
vi /etc/pam.d/login
session required /lib64/security/pam_limits.so
2.14：安装依赖包:
yum install -y bc binutils compat-libcap1 compat-libstdc++ elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make net-tools nfs-utils python python-configshell python-rtslib python-six targetcli smartmontools sysstat unzip vim
2.15：安装compat-libstdc++:
wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
2.16：检查已安装依赖包
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' bc binutils compat-libcap1 compat-libstdc++ elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make net-tools nfs-utils python python-configshell python-rtslib python-six targetcli smartmontools sysstat unzip vim
三、安装软件
3.1：解压数据库软件
su - oracle
unzip -q LINUX.X64_193000_db_home.zip -d $ORACLE_HOME
du -sh $ORACLE_HOME
6.5G /opt/oracle/product/19.3/dbhome_1
3.2：开始安装数据库 软件
# 编写相应文件
[oracle@adg19c ~]$ cat /home/oracle/19c_db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/opt/oracle/oraInventory
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/19.3/db_1
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=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT
# 运行安装命令
[oracle@adg19c ~]$ $ORACLE_HOME/runInstaller -silent -force -noconfig -ignorePrereq -responseFile /home/oracle/19c_db_install.rsp
Launching Oracle Database Setup Wizard...
[FATAL] [INS-35954] The installer has detected that the Oracle home location provided in the response file is not correct.
CAUSE: The Database Oracle home is the location from where the installer is executed. It has been detected that the value set in the response file is different (/opt/oracle/product/19.3/dbhome_1). Value expected is: /opt/oracle/product/19.3/db_1
ACTION: It is not required to specify ORACLE_HOME in the response file for Database installation. Alternatively, set it to the location of the installer (/opt/oracle/product/19.3/db_1).
Moved the install session logs to:
/opt/oracle/oraInventory/logs/InstallActions2021-10-30_07-23-54PM
四、 创建监听
# 编写监听配置文件
[oracle@adg19c ~]$ cat /home/oracle/19c_netca.rsp
[GENERAL]
RESPONSEFILE_VERSION="19.3"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
# 静默配置监听
[oracle@adg19c ~]$ netca /silent /responsefile /home/oracle/19c_netca.rsp
五、创建数据库
# 编写数据库创建相应文件(原博客这里配置项不准确，
我在此步骤停滞了很长时间（报错：dbca -slient [FATAL] java.lang.NullPointerException），
配置项可参考：：cat $ORACLE_HOME/assistants/dbca/dbca.rsp)
[oracle@adg19c ~]$ cat 19c_dbca.rsp
responseFileVersion=/home/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
templateName=General_Purpose.dbc
gdbName=orcl
sid=orcl
createAsContainerDatabase=FALSE (true则会安装pdb新特性，false则不安装)
sysPassword=oracle
systemPassword=oracle
datafileDestination=/opt/oracle/oradata
recoveryAreaDestination=/opt/oracle/flash_recovery_area
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
sampleSchema=true
totalMemory=2048
databaseType=OLTP
emConfiguration=NONE
# 静默创建数据库
dbca -silent -createDatabase -responseFile /home/oracle/19c_dbca.rsp
#############################################################################
Oracle 19c 单实例 19.3.0 升级到19.11.0
1：查看当前DB环境
cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
uname -r
3.10.0-693.el7.x86_64
lsnrctl status
2：从MOS下载19.6 的RU 补丁
19.11 的DB补丁是：p32545013_190000_Linux-x86-64.zip， 可以直接从MOS上下载。解压之后查阅readme文档。
3：开始安装RU补丁
3.1 检查OPatch工具版本
要安装19c的RU，OPatch 工具的版本必须大于 12.2.0.1.23。 在Oracle 19.3的版本中，OPatch版本是12.2.0.1.17的。 所以需要单独的更新OPatch。
[oracle@oradb31 ~]$ /opt/oracle/product/19.3/db_1/OPatch/opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
3.2查看OPatch是否冲突
[oracle@oradb31 soft]$ unzip p32545013_190000_Linux-x86-64.zip
[oracle@oradb31 soft]$ cd 32545013/
[oracle@oradb31 32545013]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /opt/oracle/product/19.3/db_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19.3/db_1/oraInst.loc
OPatch version : 12.2.0.1.17
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19.3/db_1/cfgtoollogs/opatch/opatch2021-10-30_23-59-23PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
3.3 应用patch
这里有几点注意事项：
1.如果是物理DG，那么主备库都需要安装Patch，可以先在备库安装，再安装主库，操作步骤可以参考：Document 278641.1。
2.对于RAC 环境，可以使用OPatch rolling 方式来安装，这样没有停机时间，具体操作可以参考：Document 244241.1。
3.对于单实例，必须关闭待升级ORACLE HOME关联的所有实例和监听，并且包括退出所有的sqlplus窗口。
1：关闭监听
[oracle@oradb31 32545013]$ lsnrctl stop
2：关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@oradb31 32545013]$ pwd
/soft/32545013
[oracle@oradb31 32545013]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /opt/oracle/product/19.3/db_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19.3/db_1/oraInst.loc
OPatch version : 12.2.0.1.17
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19.3/db_1/cfgtoollogs/opatch/opatch2021-10-31_00-12-57AM_1.log
Verifying environment and performing prerequisite checks...
Prerequisite check "CheckMinimumOPatchVersion" failed.
The details are:
The OPatch being used has version 12.2.0.1.17 while the following patch(es) require higher versions:
Patch 32545013 requires OPatch version 12.2.0.1.23.
Please download latest OPatch from My Oracle Support.
UtilSession failed: Prerequisite check "CheckMinimumOPatchVersion" failed.
Log file location: /opt/oracle/product/19.3/db_1/cfgtoollogs/opatch/opatch2021-10-31_00-12-57AM_1.log
OPatch failed with error code 73
如果报错，需要安装psmisc
yum install -y psmisc
去下载opatch，并升级：
https://updates.oracle.com/download/6880880.html
https://www.jianshu.com/p/69279268b068
[oracle@oradb31 32545013]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /opt/oracle/product/19.3/db_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19.3/db_1/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19.3/db_1/cfgtoollogs/opatch/opatch2021-10-31_00-35-23AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 32545013
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/19.3/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32545013' to OH '/opt/oracle/product/19.3/db_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.util, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
...
Patching component oracle.precomp.lang, 19.0.0.0.0...
Patching component oracle.jdk, 1.8.0.201.0...
Patch 32545013 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32545013].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /opt/oracle/product/19.3/db_1/cfgtoollogs/opatch/opatch2021-10-31_00-35-23AM_1.log
OPatch succeeded.
3.4 加载变化的SQL到数据库
安装补丁之后，还需要将有变化的SQL加载到数据库中，这里可以直接运行Datapatch工具将这些修改的SQL重新加载到数据库中，如果是RAC环境，只需要在一个节点执行就可以了。
根据readme的说明，操作步骤如下：
1：启动数据库
[oracle@oradb31 32545013]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 31 00:52:33 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1728050768 bytes
Fixed Size 9135696 bytes
Variable Size 402653184 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
exit
[oracle@oradb31 ~]$ /opt/oracle/product/19.3/db_1/OPatch/datapatch -verbos
SQL Patching tool version 19.11.0.0.0 Production on Sun Oct 31 01:02:27 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_32849_2021_10_31_01_02_27/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.11.0.0.0 Release_Update 210413004009: Installed
PDB CDB$ROOT:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 30-OCT-21 08.20.24.613193 PM
PDB PDB$SEED:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 30-OCT-21 08.37.34.192471 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
No interim patches need to be rolled back
Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.11.0.0.0 Release_Update 210413004009
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 2
Validating logfiles...done
Patch 32545013 apply (pdb CDB$ROOT): SUCCESS
logfile: /opt/oracle/cfgtoollogs/sqlpatch/32545013/24175065/32545013_apply_ORCL_CDBROOT_2021Oct31_01_04_56.log (no errors)
Patch 32545013 apply (pdb PDB$SEED): SUCCESS
logfile: /opt/oracle/cfgtoollogs/sqlpatch/32545013/24175065/32545013_apply_ORCL_PDBSEED_2021Oct31_02_15_57.log (no errors)
SQL Patching tool complete on Sun Oct 31 02:56:09 2021
七、检查监听注册状态
lsnrctl status
八、编译失效对象
@$ORACLE_HOME/rdbms/admin/utlrp.sql
############################################
Oracle 19c DG物理备库搭建（RMAN方式）
环境：
OS： CentOS7.4
Oracle：19.11
IP：192.168.1.51(主库) 192.168.1.31(备库)
oracle_sid：zhang19
1.设置主库
开启归档模式：
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
alter system set log_archive_dest_1='location=/opt/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=zhang19';
alter system set log_archive_dest_2='service=zhang19s valid_for=(online_logfiles,primary_role) db_unique_name=zhang19s';
alter system set log_archive_config='dg_config=(zhang19,zhang19S)';
alter system set fal_client='zhang19'; --调换位置与主库相反
alter system set fal_server='zhang19s';
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
shutdown immediate
startup mount;
alter database archivelog;
alter database open;
确认归档模式
SYS@orcl>archive log list;
查询实例的归档模式和附加日志
select log_mode,force_logging from v$database;
开启附加日志模式
alter database set standby nologging for data availability; --高可用模式
alter database set standby nologging for load performance； --性能模式
查询redo日志数量
select group#,status,type,member from v$logfile;
查询redo日志容量
select group# ,sequence#, bytes/(1024*1024),status from v$log;
给主库创建stand by的redo日志组，方便角色切换为备库时使用,容量要和redo相同，数量是redo当前数量+1
alter database add standby logfile group 4 ('/opt/oracle/oradata/ZHANG19/stredo04.log') size 200m;
alter database add standby logfile group 5 ('/opt/oracle/oradata/ZHANG19/stredo05.log') size 200m;
alter database add standby logfile group 6 ('/opt/oracle/oradata/ZHANG19/stredo06.log') size 200m;
alter database add standby logfile group 7 ('/opt/oracle/oradata/ZHANG19/stredo07.log') size 200m;
确认standby redo logfile创建成功
select group#,status,used from v$standby_log;
create pfile from spfile;
2.设置备库
复制主库密码文件和参数文件到备库改名为
initstddb.ora（create spfile的时候要把原来那个挪走，然后把这个改成initorcl.ora才能成功）
orapwstddb
备库生成spfile并启动实例到nomount
SYS@stddb> shutdown immediate;
SYS@stddb> create spfile from pfile;
SYS@stddb> startup nomount;
3.修改备库的pfile
*.audit_file_dest='/opt/oracle/admin/zhang19/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/ZHANG19/control01.ctl','/opt/oracle/flash_recovery_area/ZHANG19/control02.ctl'
*.db_block_size=8192
*.db_name='zhang19'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zhang19XDB)'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1638m
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='zhang19S'
*.log_archive_config='dg_config=(zhang19,zhang19S)'
*.fal_client='zhang19S' --调换位置与主库相反
*.fal_server='zhang19'
*.log_archive_dest_1='location=/opt/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=zhang19S'
远程归档文件传输给的服务名和唯一库ID-这里要填写主库的
*.log_archive_dest_2='service=zhang19 valid_for=(online_logfiles,primary_role) db_unique_name=zhang19'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
STANDBY_FILE_MANAGEMENT=AUTO
这里前面要填主库的本机绝对路径，后面为备库的转换路径，格式要一致，要么都加'/'，要么都不加
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
STANDBY_FILE_MANAGEMENT=AUTO
4.创建备库需要的文件夹
mkdir -p /opt/oracle/admin/zhang19/adump
mkdir -p /opt/oracle/oradata/ZHANG19
mkdir -p /opt/oracle/flash_recovery_area/ZHANG19
mkdir -p /opt/oracle/arch
5.修改主库和备库的监听
主库listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = zhang19)
(ORACLE_HOME = /opt/oracle/product/19.3/db_1)
(SID_NAME = zhang19)
)
)
主库tnsnames.ora：
ZHANG19S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhang19)
)
)
ZHANG19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhang19)
)
)
备库listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = zhang19)
(ORACLE_HOME = /opt/oracle/product/19.3/db_1)
(SID_NAME = zhang)
)
)
备库的tnsnames.ora：
ZHANG19S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhang19)
)
)
ZHANG19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhang19)
)
)
6.测试网络互通性
tnsping zhang19
tnsping zhang19s
测试登录
sqlplus sys/oracle@zhang19 as sysdba
sqlplus sys/oracle@zhang19s as sysdba
7.使用RMAN备份主库
mkdir /home/oracle/backup --建立备份目录
rman target /
关闭主库的控制文件自动备份功能（19c默认开启）
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP off;
指定备份目录
configure channel device type disk format '/home/oracle/backup/%d_%I_%s_%p.bak';
执行备份
backup as compressed backupset database include current controlfile for standby plus archivelog;
8.restore备库
拷贝数据文件到备库
[oracle@oracle19c ~/dgback]$ scp * 172.16.0.201:${PWD}
执行备库恢复
备库上需要把主库拷贝来的pfile改名为当前实例initorcl.ora
然后在nomount模式下执行
create spfile from pfile;
启动RMAN 连接目标库与辅助数据库
rman target sys/oracle@zhang19 auxiliary sys/oracle@zhang19s
执行duplicate过程
RMAN> duplicate target database for standby from active database nofilenamecheck;
9.recovery备库
开启备库
SYS@orcl>alter database open;
查看数据库角色
SYS@orcl>select database_role from v$database;
查看归档状态
SYS@orcl>select sequence#,applied from v$archived_log order by sequence#;
开启日志应用服务
alter database recover managed standby database disconnect from session;
在主库切换几次日志
SYS@pridb>alter system switch logfile;
然后回到备库确认主库的归档文件已经在备库引用
SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;
查看数据库状态
SYS@stddb>select name,open_mode from v$database;
停止日志应用服务
alter database recover managed standby database cancel;
启动备库到OPEN状态
SYS@stddb>alter database open;
查看备库状态，目前为只读状态
SYS@stddb>select name,open_mode from v$database;
备库在OPEN状态下开启日志应用服务
SYS@orcl> alter database recover managed standby database using current logfile disconnect;
此时备库可变为读写分离状态下的只读库
---------------------------------------
1.DG切换测试
1.1.DG switchover 切换测试
DB1：192.168.1.51 zhang19
DB2：192.168.1.31 zhang19
db1-zhang19：主库------>备库
db2-zhang19：备库------>主库
主备库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
--db1-zhang19显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭会话再检查
--db2-zhang19显示：NOT ALLOWED/PHYSICAL STANDBY
db1-zhang19切换到备库
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 immediate
SQL> startup mount
db2-zhang19切换到主库
SQL> alter database commit to switchover to primary;
SQL> alter database open;
db1-zhang19执行APPLY LOG命令
--启用mount状态下的APPLY LOG
SQL> alter database recover managed standby database disconnect from session;
--启用open状态（READ ONLY WITH APPLY）下的APPLY LOG
SQL> 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;
-----------------------------------------
1.2.DG failover 切换测试
经过上面正常切换后，db2现在是主库，db1是备库
db2-zhang19：主库------>崩溃
db1-zhang19：备库------>主库
主备库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
db2-zhang19：通过shutdown abort方式人工模拟主库崩溃，直接关闭
SQL> select open_mode from v$database;
SQL> shutdown abort
SQL> startup mount
SQL> alter system flush redo to 'zhang19';
db1-zhang19：执行如下操作切换为主库
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
--如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在备库，要进行关闭apply和结束应用动作。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> select open_mode, switchover_status from v$database;
---------------------------------------------------
1.3.DG failover后重建DG
db1-zhang19：主库------>主库（保持主库状态不变）
db2-zhang19：崩溃------>备库
db1-zhang19：主库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
-------------------- ---------------- --------------------
TO STANDBY PRIMARY READ WRITE
db1-zhang19主库创建备库控制文件
mkdir -p /home/oracle/backup
SQL> alter database create standby controlfile as '/home/oracle/backup/control01.ctl';
将备库控制文件拷贝至db2
scp /home/oracle/backup/control01.ctl 192.168.1.31:/opt/oracle/oradata/ZHANG19/control01.ctl
db2下：
cp /opt/oracle/oradata/ZHANG19/control01.ctl /opt/oracle/flash_recovery_area/ZHANG19/control02.ctl
恢复对应数据文件至db2，并启动db2-zhang19到mount状态，应用APPLY模式
shutdown immediate
startup mount;
alter database recover managed standby database disconnect from session;
--alter database recover managed standby database disconnect;
启动db2-zhang19至OPEN状态，并应用APPLY REDO模式
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
--alter database recover managed standby database disconnect;
select switchover_status,database_role,open_mode from v$database;
2929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
