龙析系统安装 Oracle19c
1.安装背景
本次教程使用VMware workstation Pro 17 创建虚拟机完成安装测试
1.1 系统选择
os版本信息:AnolisOS-8.8-x86_64-dvd.iso
数据库版本信息:LINUX.X64_193000_db_home.zip
1.2 硬件配置信息
| 参数 | 值 |
|---|---|
| 核心 | 1 H 2 G |
| 内存 | 2048 M |
| 存储 | 60 G |
| 虚拟机个数 | 2 个 |
1.3 分区配置
| 分区 | 大小 |
|---|---|
| /boot | 1024 M |
| /boot/efi | 1024 M |
| swap | 2048 M |
| / | all |
2. 环境配置
2.1 内核参数调整
执行以下内容
export shmall=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` / (`getconf PAGESIZE` / 1024)" | bc)
export shmax=$(echo "`cat /proc/meminfo | grep "MemTotal" | awk '{print $2}'` * 1024 * 0.8" | bc | sed 's#\..*$##')
echo """kernel.shmall = $shmall
kernel.shmmax = $shmax
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
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 = 104857
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100""" >> /etc/sysctl.conf
sysctl -p2.2 关闭 Selinux 及防火墙
执行以下内容
sed -i 's/enforcing/disabled/g' /etc/selinux/config
setenforce 0
service firewalld stop
chkconfig firewalld off
# 龙析8.8 系统没有network服务 关闭NetworkManager后重启无法联网
# service NetworkManager stop
# chkconfig NetworkManager off2.3 用户组相关设置
执行以下内容
groupadd -g 1003 oinstall
groupadd -g 1001 dba
groupadd -g 1002 oper
useradd -u 1001 -g oinstall -G dba,oper oracle
echo oracle |passwd --stdin oracle2.4 创建目录赋予权限
mkdir -p /u01/app/oracle/product/19c/db_1
chown -R oracle:oinstall /u01/
chmod -R 775 /u01/2.5 修改Oracle用户下变量
echo """export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\${ORACLE_BASE}/product/19c/db_1
export NLS_DATE_FORMAT="YYYY:MM:DDHH24:MI:SS"
export NLS_LANG=american_america.ZHS16GBK
export TNS_ADMIN=\$ORACLE_HOME/network/admin
export ORA_NLS11=\$ORACLE_HOME/nls/data
export PATH=.:\${PATH}:\$ORACLE_HOME/bin:/usr/bin:/usr/bin/X11:/usr/local/bin:
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
# export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib:\$ORACLE_HOME/network/jlib
export CV_ASSUME_DISTID=OL7
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp""" >> /home/oracle/.bash_profile2.6 安装软件支持包
yum -y install bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXtst libX11 libXau libxcb libXi libXrender make smartmontools sysstat kmod* sysstat unixODBC unixODBC-devel libnsl lm_sensors-libs ksh make glibc-devel libaio-devel libstdc++-devel需要单独安装的软件包
yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/compat-libcap1-1.10-7.el7.x86_64.rpm
yum -y localinstall https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/sysstat-10.1.5-20.0.3.el7_9.x86_64.rpm3. 安装数据库软件
3.1 软件准备
`将Oracle 软件上传到服务器 修改用户组为oracle:oinstall`
chown oracle:oinstall LINUX.X64_193000_db_home.zip
`将Oracle软件移动到 $ORACLE_HOME 目录`
mv LINUX.X64_193000_db_home.zip /u01/app/oracle/product/19c/db_1/
`oracle用户解压Oracle 软件`
su - oracle
cd $ORACLE_HOME
unzip LINUX.X64_193000_db_home.zip3.2 软件安装
3.2.1 运行安装命令
./runInstaller
3.2.2 Cunfiguration Option
Set Up Software Only
3.2.3 Database Installation Option
Single Instance database installation
3.2.4 Database Edition
Enterprise Edition
3.2.5 Installation Location
默认位置
3.2.6 Create Inventory
默认位置
3.2.7 Operating System Groups
默认用户组
3.2.8 Root Script execution
勾选Automatically run configuration scripts 并填写密码，自动执行root脚本。也可不勾选，手动执行root脚本
3.2.9 Prerequisite Checks
分析检查结果，根据情况判断是否需要处理
3.2.10 Summary
检查安装选项
3.2.11 Install Product
选yes 自动运行脚本
3.2.12Finish
完成
4. 创建数据库
4.1 数据库安装准备
Oracle 用户登录服务器
4.2 数据库创建
4.2.1 运行创建命令
dbca
4.2.2 Database Operation
Create a database
4.2.3 Creation Mode
Advanced configuration
4.2.4 Deployment Type
根据业务情况选择安装类型
4.2.5 Database Identification
输入数据库实例名
根据情况选择是否创建cdb
4.2.6 Storage Option
根据情况选择,本此测试默认选项
4.2.7 Fast Recovery Option
指定fra参数，以及归档情况
4.2.8 NetworkConfiguration
勾选Create a new listener
Listener name : LISTENER
Listener port: 1521
4.2.9 Data Vault Option
默认
4.2.10 Configuration Options
根据情况配置内存参数和字符集信息
4.2.11 Management Options
根据情况选择是否开启EM
4.2.12 User Credentials
创建密码
4.2.13 Creation Option
根据业务情况选择，本次测试默认
4.2.14 Summary
检查安装信息
4.2.15 Progress Page
等待安装过程
4.2.16 Finish
完成
5. 创建DG环境
5.1 创建DG虚拟机环境
参照本文章1，2，3，4章节创建服务器和安装数据库软件
5.2 备库创建监听器
数据库安装完成后创建监听器
5.2.1 Oracle 用户创建监听器
netca
按照界面提示创建一个监听器
5.3 主库配置DG环境
5.3.1 主库配置检查
检查archivelog是否开启
SQL> archive log list; #执行命令检查archivelog状态
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5检查flashback
SQL> SELECT flashback_on FROM v$database; #执行命令检查flashback状态
FLASHBACK_ON
------------------------------------
NO
SQL> show parameter db_recovery; #执行命令检查闪回位置及大小
NAME TYPE VALUE
------------------------------------ ---------------------- ----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 8256M检查主库日志文件
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- --------------------------------------------------------------------------- ------ ----------
1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_lt1c27xy_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_lt1c28l0_.log YES 0
2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_lt1c283q_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_lt1c29cj_.log YES 0
3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_lt1c2852_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_lt1c2984_.log YES 0
6 rows selected.检查主库系统参数
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name string orcl
SQL> show parameter log_archive_config;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_config string
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter log_archive_dest_state_1;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL> show parameter fal_server;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fal_server string
SQL> show parameter fal_client;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fal_client string
SQL> show parameter archive_lag_target;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
archive_lag_target integer 0
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
standby_file_management string MANUAL
SQL> show parameter db_file_name_convert;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_name_convert string
pdb_file_name_convert string
SQL> show parameter log_file_name_convert;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_file_name_convert string5.3.2 主库重启到mount状态
SQL> shutdown immediate; #关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; #启动到mount模式
ORACLE instance started.
Total System Global Area 813692064 bytes
Fixed Size 8901792 bytes
Variable Size 218103808 bytes
Database Buffers 578813952 bytes
Redo Buffers 7872512 bytes
Database mounted.5.3.3 修改归档信息和闪回信息
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch_orcl'; #设置归档路径
System altered.
SQL> alter database archivelog; #设置启用归档
Database altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=spfile; #设置闪回位置
System altered.
SQL> alter system set db_recovery_file_dest_size=8G; #设置闪回大小
System altered.
SQL> alter database flashback on; #设置启用闪回
Database altered.5.3.4 配置日志组
SQL> select * from v$logfile; #查询当前日志组信息
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- ----------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_lt1c2852_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_lt1c2984_.log YES 0
2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_lt1c283q_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_lt1c29cj_.log YES 0
1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_lt1c27xy_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_lt1c28l0_.log YES 0
6 rows selected.
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo4.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo4.log') size 200M; #主库为备库添加日志组4
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo5.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo5.log') size 200M; #主库为备库添加日志组5
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo6.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo6.log') size 200M; #主库为备库添加日志组6
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/sredo7.log','/u01/app/oracle/oradata/ORCL/onlinelog/sredo7.log') size 200M; #主库为备库添加日志组7
Database altered.5.3.5 修改spfile参数
#设置db_unique_name数据库唯一标识名
SQL> alter system set db_unique_name='primary' scope=spfile;
System altered.
#设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换
SQL> alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
System altered.
#设置日志归档路径1
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
System altered.
#设置日志归档路径2
SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby' scope=spfile;
System altered.
#启用归档路径1
SQL> alter system set log_archive_dest_state_1='enable' scope=spfile;
System altered.
#启用归档路径2
SQL> alter system set log_archive_dest_state_2='enable' scope=spfile;
System altered.
#设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换）
SQL> alter system set fal_server='standby' scope=spfile;
System altered.
#设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换）
SQL> alter system set fal_client='primary' scope=spfile;
System altered.
#设置归档日志强制切换时间（s）1800表示30分钟
SQL> alter system set archive_lag_target=1800 scope=spfile;
System altered.
#设置归档日志文件名命名方式
SQL> alter system set log_archive_format='%t_%s_%r.arc'scope=spfile;
System altered.
#设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。
SQL> alter system set standby_file_management=auto scope=spfile;
System altered.
#设置数据文件名称的映射关系（注意顺序不能变）
SQL> alter system set db_file_name_convert='standby','primary' scope=spfile;
System altered.
#设置日志文件名称的映射关系（注意顺序不能变）
SQL> alter system set log_file_name_convert='standby','primary' scope=spfile;
System altered.5.3.6 修改数据库状态
SQL> alter database open; #启动数据库到OPEN状态
Database altered.
SQL> shutdown immediate; #关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup; #使用修改后的spfile启动数据库，检查是否有报错
ORACLE instance started.
Total System Global Area 813692064 bytes
Fixed Size 8901792 bytes
Variable Size 218103808 bytes
Database Buffers 578813952 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.5.3.7 创建pfile文件
SQL> create pfile from spfile; #从spfile创建pfile文件
File created.5.3.8 主库修改网络监听
修改监听文件/u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) #host为当前主机名
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
#新增以下内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl) #改为实例名
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl) #改为实例名
)
)修改主库客户端配置文件/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
# 新增内容
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) #HOST填写主库IP或者主机名
)
(CONNECT_DATA =
(SERVICE_NAME = orcl) #实例名
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521)) #HOST填写备库IP或者主机名
)
(CONNECT_DATA =
(SERVICE_NAME = orcl) #实例名
)
)5.3.9 主库重启和测试监听
[oracle@localhost]$ lsnrctl reload #重加载监听文件
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:40:45
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost]$ lsnrctl status #查看监听状态
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:40:50
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 12-JAN-2024 11:01:57
Uptime 0 days 4 hr. 38 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "primary" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost]$ tnsping primary #测试primary连接可用性
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:41:17
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@localhost]$ tnsping standby #测试standby连接可用性
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:41:20
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (40 msec)5.3.10 主库文件复制到备库
[oracle@localhost]$ cd /u01/app/oracle/product/19c/db_1/dbs #定位到主库$ORACLE_HOME/dbs/
[oracle@localhost dbs]$ scp initorcl.ora orapworcl orcldg:/u01/app/oracle/product/19c/db_1/dbs/ # scp远程复制到备库
oracle@orcldg's password:
initorcl.ora 100% 1805 292.5KB/s 00:00
orapworcl 100% 2048 410.2KB/s 00:005.4 备库配置
5.4.1 备库监听文件配置
修改监听文件/u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) #host为当前主机名
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
#新增以下内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl) #改为实例名
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
(SID_NAME = orcl) #改为实例名
)
)修改主库客户端配置文件/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
# 新增内容
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521)) #HOST填写主库IP或者主机名
)
(CONNECT_DATA =
(SERVICE_NAME = orcl) #实例名
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521)) #HOST填写备库IP或者主机名
)
(CONNECT_DATA =
(SERVICE_NAME = orcl) #实例名
)
)5.4.2 备库重启和测试监听
[oracle@localhost]$ lsnrctl reload #重加载监听配置文件
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:40
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost]$ tnsping primary #测试primary连接可用性
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:44
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@localhost]$ tnsping standby #测试standby连接可用性
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-JAN-2024 15:58:49
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (30 msec)5.4.3 备库修改pfile文件
修改主库复制过来的pfile文件/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=541065216
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=272629760
orcl.__sga_target=813694976
orcl.__shared_io_pool_size=37748736
orcl.__shared_pool_size=213909504
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_lt1c25mt_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_lt1c25nt_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
#修改点1
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8589934592
#修改点2
*.db_unique_name='standby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
#修改点3
*.fal_client='standby'
#修改点4
*.fal_server='primary'
*.local_listener='LISTENER_ORCL'
*.log_archive_config='DG_CONFIG=(primary,standby)'
#修改点5
*.log_archive_dest_1='location=/u01/app/oracle/arch_orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
#修改点6
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
#修改点7
*.log_file_name_convert='primary','standby'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=259m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=775m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'除上述修改点外，如备库配置与主库不一致，还需注意sga，pga相关配置
如主库为rac环境，还需修改控制文件路径，数据文件路径，归档日志路径
5.4.4 位置创建
根据pfile文件中的位置检查是否存在，不存在的则创建
#根据pfile中的路径创建
mkdir -p /u01/app/oracle/{admin/orcl/adump,oradata/ORCL/controlfile,fast_recovery_area/ORCL,arch_orcl}5.4.5 测试pfile并创建spfile
SQL> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora'; # 测试启动到nomount模式
ORACLE instance started.
Total System Global Area 813692064 bytes
Fixed Size 8901792 bytes
Variable Size 218103808 bytes
Database Buffers 578813952 bytes
Redo Buffers 7872512 bytes
SQL> create spfile from pfile='/u01/app/oracle/product/19c/db_1/dbs/initorcl.ora'; # 创建spfile文件
File created.
5.4.6 备库RMAN连接主备库进行首次同步
[oracle@localhost dbs]$ rman target sys/Xx961024@primary auxiliary sys/Xx961024@standby #RMAN连接主备库
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 12 16:32:50 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1685839813)
connected to auxiliary database: ORCL (not mounted)
duplicate target database for standby from active database nofilenamecheck; #执行此命令为不自动回复
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck; #执行首次同步，带自动恢复
......5.4.7 首次同步完成后配置日志同步
SQL> alter database recover managed standby database disconnect from session; #启动同步
Database altered.5.4.8 检查同步状态
SQL> select process, status, GROUP#, SEQUENCE#, block#, blocks from v$managed_standby; #检查同步状态
PROCESS STATUS GROUP# SEQUENCE# BLOCK# BLOCKS
------------------ ------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
ARCH CONNECTED N/A 0 0 0
DGRD ALLOCATED N/A 0 0 0
DGRD ALLOCATED N/A 0 0 0
ARCH CONNECTED N/A 0 0 0
ARCH CONNECTED N/A 0 0 0
ARCH CONNECTED N/A 0 0 0
RFS IDLE N/A 0 0 0
RFS IDLE 3 12 456 1
RFS IDLE N/A 0 0 0
MRP0 WAIT_FOR_LOG N/A 12 0 0
10 rows selected.5.4.9 检查日志应用状态
SQL> select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #检查1小时内日志同步情况
NAME SEQUENCE# APPLIED COMPLETETIME
------------------------------------------------------- ---------- ------------------ --------------------------------------
/u01/app/oracle/arch_orcl/1_11_1158059079.arc 11 YES 2024-01-12 16:41:12
SQL>5.4.10 DG配置完成
DG 配置完成
6. DG其他命令
6.1 备库启动数据同步
#完成后关闭数据库，启动到mount模式
startup mount;
alter database recover managed standby database disconnect from session;
#如果要停止同步
SQL> alter database recover managed standby database cancel;
#注：如果想让备库比主库延迟几分钟可以启动为如下方式
SQL> alter database recover managed standby database delay 5 disconnect from session;6.2 恢复后数据测试
#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成）
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;
select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#;
#主库上切换日志文件再观察有没有正确同步到备库
alter system switch logfile;6.3 验证数据库状态
SQL> select switchover_status from v$database;
#主库状态为 TO STANDBY表示可切换为备库
#备库状态为 NOT ALLOWED6.4 主备切换
##执行切换命令，并将主库转换为备库角色
##主库执行切换：
SQL> alter database commit to switchover to physical standby with session shutdown;
##主库角色转换为备库：
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
##备库执行切换：
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup
##切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。
##注意：
##如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。
alter database commit to switchover to physical standby with session shutdown;
##在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY6.5 故障切换
##当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
PRIMARY6.6 通过flashback重建备库
##当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建
##A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号：
SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
-----------------------------------------------------------------------
1400381
##B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to scn 1400381;
闪回完成。
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup mount;
##C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库）
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;
##D). 回到原主库（当前备库）开启日志应用进程
SQL> alter database recover managed standby database using current logfile disconnect;
##此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态。6.7 备注
##Oracle Dtataguard有三种保护模式：
Maximum Availability Maximum Performance Maximum Protection
AFFIRM NOAFFIRM AFFIRM
SYNC ASYNC SYNC
##Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。
##通过select protection_mode from v$database查询保护模式
SQL> select protection_mode from v$database;
PROTECTION_MODE
----------------------------------------
MAXIMUM PERFORMANCE
##备注2：
##当主备同步有问题时候可以通过查询错误输出来初步诊断
SQL> select error from v$archive_dest where target='STANDBY';
##备注：
##当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。
##查询某一个时间点的scn号：
timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual;
川公网安备 51130202000392号
评论