Oracle19c ADG搭建

一、环境配置

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/archive

2、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;
exit

cd $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.ora

4、配置TNS文件

cd $ORACLE_HOME/network/admin/

netmgr,配置tns,监听

vim tnsnames.ora

LISTENER_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/adump

2、配置参数文件

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 sysdba

2、主库登入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、测试同步

随便创建一个表，在插入一些数据，然后用备库查询看是否能查询到
注意：主库数据插入之后需要提交之后备库才能查看到

备库进行查看：

同步成功