本文主要是介绍云贝教育 |【技术文章】Oracle 19c 单实例dg+rman+copy方案，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
软件规划
| 软件名称 | 版本 |
|---|---|
| vmware | v15 |
| 操作系统 | oracle linux 8.8 |
| 数据库 | 19.3.0.0.0 |
| secureCRT | 9 |
主备规划
| 类型 | IP | 主机名 | db_name | db_unique_name | instance_name | ORACLE_SID |
|---|---|---|---|---|---|---|
| 主库 | 192.168.2.115 | db1 | ORCLCDB | ORCLCDB1 | ORCLCDB1 | ORCLCDB1 |
| 备库 | 192.168.254.38 | db2 | ORCLCDB | ORCLCDB2 | ORCLCDB2 | ORCLCDB1 |
说明：以下配置无特殊说明，均在CDB下，用SYS用户操作。
一、主库配置
1.1 主库开启归档
1）oracle创建归档目录
mkdir -p /opt/oracle/arch2）cdb下修改归档参数
alter system set log_archive_dest_1='LOCATION=/opt/oracle/arch'; scope=spfile;
show parameter log_archive_dest_state_1;
-->enable3) 重启数据库到mount阶段
startup mount4) 开启归档
alter database archivelog;5）打开数据库并检查
SQL> alter database open;Database altered.SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/arch
Oldest online log sequence 252
Next log sequence to archive 254
Current log sequence 2541.2 主库设置force logging模式
1）设置主备force logging
alter database force logging;2）检查
select FORCE_LOGGING from v$database;1.3 配置standby redolog
1) 查看日志组路径及大小SQL> col MEMBER for a40
SQL> select a.group#,a.MEMBER,b.bytes/1024/1024 from v$logfile a ,v$log b where a.group#=b.group# order by 1;GROUP# MEMBER B.BYTES/1024/1024
---------- ---------------------------------------- -----------------1 /opt/oracle/oradata/ORCLCDB/redo01.log 2002 /opt/oracle/oradata/ORCLCDB/redo02.log 2003 /opt/oracle/oradata/ORCLCDB/redo03.log 2002）创建standby logfilealter database add standby logfile group 4('/opt/oracle/oradata/ORCLCDB/redo04.log') size 200M;
alter database add standby logfile group 5('/opt/oracle/oradata/ORCLCDB/redo05.log') size 200M;
alter database add standby logfile group 6('/opt/oracle/oradata/ORCLCDB/redo06.log') size 200M;
alter database add standby logfile group 7('/opt/oracle/oradata/ORCLCDB/redo07.log') size 200M;3) 重启
shutdown immediate
startup1.4 配置密码文件
orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=oracle entries=10 force=y format=12;将主库密码文件拷贝到备库
scp orapwORCLCDB1 db2:$ORACLE_HOME/dbs1.5 配置监听
查看数据库name参数
SQL> show parameter nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string ORCLCDB
db_unique_name string ORCLCDB1
global_names boolean FALSE
instance_name string ORCLCDB1
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string ORCLCDB1查看ORACLE_SID
[oracle@db1 ~]$ ps -ef|grep smon
oracle 25927 1 0 17:23 ? 00:00:00 ora_smon_ORCLCDB1
oracle 27127 24278 0 17:33 pts/1 00:00:00 grep --color=auto smon
[oracle@db1 ~]$ echo $ORACLE_SID
ORCLCDB1这里要配置静态监听
SID_LIST_LISTENER = (SID_LIST =(SID_DESC =(GLOBAL_DBNAME = ORCLCDB1) #service_name(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB1) #oracle_sid))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521)))1.6 配置tns
ORCLCDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLCDB1)))ORCLCDB2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLCDB2)))验证网络
#t重启监听
lsnrctl stop
lsnrctl start测试服务名连通性：
tnsping ORCLCDB1
tnsping ORCLCDB2测试远程登录
sqlplus sys/oracle@ORCLCDB1 as sysdba
--此时备库未配置监听，所以无法连接
sqlplus sys/oracle@ORCLCDB2 as sysdba1.7 配置主库参数
主库参数（重启生效）：
alter system set db_unique_name='ORCLCDB1' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLCDB1,ORCLCDB2)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLCDB1' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCLCDB2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB2' scope=both;
alter system set fal_client='ORCLCDB1' scope=both;
alter system set FAL_SERVER='ORCLCDB2' scope=both;
alter system set standby_file_management=AUTO scope=both;重启数据库生效
shutdown immediate;
startup ;注：
如果主备两边目录结构不一样，DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数需要修改
alter system set DB_FILE_NAME_CONVERT='ORCLCDB2','ORCLCDB1' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='ORCLCDB2','ORCLCDB1' scope=spfile;1.8 关闭应用和监听，做RMAN全备
1）关闭监听
lsnrctl stop2）rman全库备份
backup full database format '/opt/oracle/backup/backup_%T_%s_%p.bak';
sql 'alter system archive log current';
backup archivelog all format ='/opt/oracle/backup/arch_%T_%s_%p.bak';run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format '/opt/oracle/backup/backdata_%T_%s_%p.bak';
sql 'alter system archive log current';
backup archivelog all format '/opt/oracle/backup/archlog_%T_%s_%p.bak';
backup current controlfile format '/opt/oracle/backup/cntrl_%s_%p_%s.bak';
release channel c1;
release channel c2;
} 3）启动监听
lsnrctl start4）将备份传输到备库
scp -r /opt/oracle/backup/ db2:/opt/oracle二、备库配置
2.1 配置备库oracle环境变量
umask 022export TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/opt/oracleexport ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1export ORACLE_SID=ORCLCDB2export PATH=/usr/sbin:$PATHexport PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH2.2 配置备库参数
主要参照主库，微调即可
*.audit_file_dest='/opt/oracle/admin/ORCLCDB/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl','/opt/oracle/oradata/ORCLCDB/control02.ctl'
*.db_block_size=8192
*.db_name='ORCLCDB'
*.db_unique_name='ORCLCDB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLCDBXDB)'
*.enable_pluggable_database=true
*.fal_client='ORCLCDB2'
*.fal_server='ORCLCDB1'
*.instance_name='ORCLCDB2'
*.local_listener='LISTENER_ORCLCDB'
*.log_archive_config='DG_CONFIG=(ORCLCDB2,ORCLCDB1)'
*.log_archive_dest_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLCDB2'
*.log_archive_dest_2='SERVICE=ORCLCDB1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB1'
*.memory_target=1444m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'将参数文件中必要的目录创建
mkdir -p /opt/oracle/oradata/ORCLCDB
mkdir -p /opt/oracle/arch
mkdir -p /opt/oracle/admin/ORCLCDB/adump2.3 配置备库网络
1）静态监听
SID_LIST_LISTENER = (SID_LIST =(SID_DESC =(GLOBAL_DBNAME = ORCLCDB2) (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB2) ))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521)))2）tns解析
ORCLCDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLCDB1)))ORCLCDB2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLCDB2)))验证网络
重启备库监听
lsnrctl stop
lsnrctl start测试服务名连通性：
tnsping ORCLCDB1
tnsping ORCLCDB2测试远程登录
sqlplus sys/oracle@ORCLCDB1 as sysdba
sqlplus sys/oracle@ORCLCDB2 as sysdba2.4 启动备库到nomount阶段
startup nomount2.5 备库通过rman恢复控制文件
注意这里的控制文件是 standby controlfile
restore standby controlfile from '/home/oracle/backup/cntrl_6_1_6.bak';2.6 备库通过rman恢复数据文件
注册恢复目录
catalog start with '/opt/oracle/backup';
crosscheck backup;rman恢复数据文件
restore database;
restore archivelog all;查看备库日志
select * from v$standby_log;查看数据库状态
SQL> select open_mode from v$database;OPEN_MODE
--------------------
MOUNTED三、配置主备同步
3.1 启动备库日志应用
alter database recover managed standby database using current logfile disconnect;3.2 查看主备日志应用情况
select process,status,thread#,sequence# from v$managed_standby;PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 1 0
RFS IDLE 1 267
MRP0 APPLYING_LOG 1 2679 rows selected.3.3 关闭日志应用
alter database recover managed standby database cancel;3.4 将备库打开
alter database open;到此为上，主备搭建完成。
这篇关于云贝教育 |【技术文章】Oracle 19c 单实例dg+rman+copy方案的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
