一、环境配置
1、主机环境
| 类型 | 主机名 | IP |
|---|---|---|
| 主库 | p19c | 192.168.229.150 |
| 备库 | p19cstd | 192.168.229.151 |
这里选择做两个19c单机环境
tip：数据库服务名与主机名一致
- 19c的安装可以参考以下教程，教程是以p19c为例，在安装p19cstd时，需要将所有的p19c替换成p19cstd
Oracle 19c静默安装教程
2、host文件配置
配置hostname：
# 主库 hostnamectl set-hostname p19c # 备库 hostnamectl set-hostname p19cstd配置hosts文件：
cat <<EOF>>/etc/hosts #Public IP 192.168.229.150 p19c 192.168.229.151 p19cstd EOF二、主库配置
1、 开启归档
- 查看oracle归档状态
archive log list- 关闭数据库，并启动到mount状态
shutdown immediate; startup mount;- 修改归档位置
mkdir -p /u01/app/archive chown -R oracle:oinstall /u01/app/ # 在sql中执行，修改归档位置 alter system set log_archive_dest_1='location=/u01/app/archive';- 启用归档模式和强制归档
alter database archivelog; alter database force logging; alter database open;- 查看修改后的归档状态
archive log list;- 切换在线日志，验证归档是否正常
alter system switch logfile; !ls /u01/app/archive2、redo日志
- 查询现有redo日志的数量
select group#,type,member from v$logfile;- 查询现有redo日志的容量
select group#,bytes/1024/1024 as MB,status from v$log;- 创建standby日志组，容量要和redo相同，数量是redo当前数量+1
mkdir -p /u01/app/oradata/P19C chown -R oracle:oinstall /u01/app/alter database add standby logfile group 6 ('/u01/app/oradata/P19C/standby_redo04.log') size 200m reuse, group 7 ('/u01/app/oradata/P19C/standby_redo05.log') size 200m reuse, group 8 ('/u01/app/oradata/P19C/standby_redo06.log') size 200m reuse, group 9 ('/u01/app/oradata/P19C/standby_redo07.log') size 200m reuse;- 确认状态
select group#,status,used from v$standby_log;3、传输口令文件和参数文件
root用户执行：
chown oracle:oinstall /opt口令文件：
cd $ORACLE_HOME/dbs cp orapwp19c /opt/orapwp19cstd参数文件:
sqlplus / as sysdba create pfile from spfile; exitcd $ORACLE_HOME/dbs vim initp19c.ora *.db_unique_name='p19c' *.log_archive_config='dg_config=(p19c,p19cstd)' *.fal_client='p19c' *.fal_server='p19cstd' *.log_archive_dest_1='LOCATION=/u01/app/archive valid_for=(all_logfiles,all_roles) db_unique_name=p19c' *.log_archive_dest_2='service=p19cstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=p19cstd' *.log_archive_format='%t_%s_%r.arc' *.db_file_name_convert='/u01/app/oradata/P19CSTD/','/u01/app/oradata/P19C/' *.log_file_name_convert='/u01/app/oradata/P19CSTD/','/u01/app/oradata/P19C/' STANDBY_FILE_MANAGEMENT=AUTO
启用新的参数文件:
备份之前的spfile
# 如果报错可能是没有opt下的创建文件的权限，给一下权限就好 create pfile='/opt/spfile.bak' from spfile;
启用新的
shutdown immediate; create spfile from pfile; startup;传输文件到备库:
备库执行：
chown oracle:oinstall /opt# 这一步报错一般也是目标服务器中ORACLE用户没有在opt下创建文件的权限 scp /opt/orapwp19cstd oracle@p19cstd:/opt scp $ORACLE_HOME/dbs/initp19c.ora oracle@p19cstd:/opt/initp19cstd.ora4、配置TNS文件
cd $ORACLE_HOME/network/admin/netmgr,配置tns,监听
vim tnsnames.oraLISTENER_P19C = (ADDRESS = (PROTOCOL = TCP)(HOST = p19c)(PORT = 1521)) P19C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = p19c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = p19c) ) ) P19CSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = p19cstd)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = p19cstd) ) )5、静态监听
cd $ORACLE_HOME/network/admin vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = p19c)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = p19c) (ORACLE_HOME = /u01/app/oracle/product/19.3.0) (SID_NAME = p19c) ) )监听重载
lsnrctl reload- 测试监听是否配资成功
tnsping p19c tnsping p19cstd三、备库配置
1、创建必要文件夹
mkdir -p /u01/app/archive chown -R oracle:oinstall /u01/app/ mkdir -p /u01/app/oradata/P19CSTD chown -R oracle:oinstall /u01/app/ mkdir -p /u01/app/oracle/admin/p19cstd/adump2、配置参数文件
cd /opt cp orapwp19cstd initp19cstd.ora $ORACLE_HOME/dbs cd $ORACLE_HOME/dbs vim initp19cstd.ora *.audit_file_dest='/u01/app/oracle/admin/p19cstd/adump' *.control_files='/u01/app/oradata/P19CSTD/control01.ctl','/u01/app/oradata/P19CSTD/control02.ctl' *.db_unique_name='p19cstd' *.log_archive_config='dg_config=(p19cstd,p19c)' *.fal_client='p19cstd' *.fal_server='p19c' *.log_archive_dest_1='LOCATION=/u01/app/archive valid_for=(all_logfiles,all_roles) db_unique_name=p19cstd' *.log_archive_dest_2='service=p19c lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=p19c' *.log_archive_format='%t_%s_%r.arc' *.db_file_name_convert='/u01/app/oradata/P19C/','/u01/app/oradata/P19CSTD/' *.log_file_name_convert='/u01/app/oradata/P19C/','/u01/app/oradata/P19CSTD/'3、启动备库到nomount
export ORACLE_SID=p19cstd sqlplus / as sysdba shutdown immediate create spfile from pfile; startup nomount; # 修改参数standby_file_management alter system set standby_file_management=MANUAL;4、配置TNS文件
cd $ORACLE_HOME/network/admin/tnetmgr,配置tns,监听
vim tnsnames.ora# 文件内容如下 LISTENER_P19CSTD = (ADDRESS = (PROTOCOL = TCP)(HOST = p19cstd)(PORT = 1521)) P19CSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = p19cstd)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = p19cstd) ) ) P19C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = p19c)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = p19c) ) )5、配置静态监听
cd $ORACLE_HOME/network/admin vi listener.ora # 文件内容如下 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = p19cstd)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = p19cstd) (ORACLE_HOME = /u01/app/oracle/product/19.3.0) (SID_NAME = p19cstd) ) )监听重载
lsnrctl reload- 测试监听是否配资成功
tnsping p19c tnsping p19cstd四、构建DG
1、验证监听
主库：
sqlplus sys/123@p19c as sysdba sqlplus sys/123@p19cstd as sysdba备库：
sqlplus sys/123@p19c as sysdba sqlplus sys/123@p19cstd as sysdba2、主库登入rman，复制数据库至备库
rman target sys/123@p19c auxiliary sys/123@p19cstd构建备库
run { allocate channel ch001 type disk; allocate channel ch002 type disk; allocate channel ch003 type disk; allocate channel ch004 type disk; allocate auxiliary channel ch005 type disk; allocate auxiliary channel ch006 type disk; duplicate target database for standby from active database nofilenamecheck using compressed backupset; release channel ch001; release channel ch002; release channel ch003; release channel ch004; release channel ch005; release channel ch006; }3、检验并开启实时同步
- 备库执行
# 查询备库状态是否已经mount： select open_mode from v$database;# 打开备库 alter database open;检查文件：
select name from v$datafile; select name from v$tempfile; select group#,type,member from v$logfile order by 2,1;
开启日志应用（即开启实时同步）:
alter database recover managed standby database using current logfile disconnect;检查数据同步状态:
通过判断transport lag和apply lag两个参数的DATUM_TIME值来判断备库是否在应用主库的日志文件
如果多次查询DATUM_TIME值保持不变，则说明备库已经停止从主库接收数据了
select name,value,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;检查MRP进程状态:
select process,status from v$managed_standby;查看数据库打开状态:
READ ONLY WITH APPLY代表ADG启动成功
select open_mode from v$database;
到这里已经完成了ADG的搭建了
4、测试同步
随便创建一个表，在插入一些数据，然后用备库查询看是否能查询到
注意：主库数据插入之后需要提交之后备库才能查看到
备库进行查看：
同步成功
