记一次| oracle12c 搭建 ADG

环境准备：两台服务器(系统centos7.9）
主库：192.168.1.42 主机名：cent7z 预装了oracle12c-db软件 监听和库都是正常的
备库：192.168.1.33 主机名：cent7 预装了oracle12c-db软件 （无监听，无数据库）

1、修改/etc/hosts文件，将主从的ip和主机名添加进去（主备两边都要做）

vim /etc/hosts

192.168.1.42 cent7z
192.168.1.33 cent7
[oracle@cent7 ~]$ ping cent7z
PING cent7z (192.168.1.42) 56(84) bytes of data.
64 bytes from cent7z (192.168.1.42): icmp_seq=1 ttl=64 time=0.355 ms
[root@cent7z ~]# ping cent7
PING cent7 (192.168.1.33) 56(84) bytes of data.
64 bytes from cent7 (192.168.1.33): icmp_seq=1 ttl=64 time=0.283 ms


2、主库启动到FORCE LOGGING（强制日志）

SQL> alter database force logging;

3、检查主库是否在归档模式下

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 22
Current log sequence 24

4、给主库添加stand by备用日志组 ，要比现有的日志组多一组。

SQL> select group#, members, bytes from v$log;

Database altered


5、修改内核参数

为让ADG库起到更好的作用，需要修改LINUX内核参数。

[root@cent7z ~]# vim /etc/sysctl.conf
net.ipv4.tcp_rmem = 4096 87380 6291456
net.ipv4.tcp_wmem = 4096 16384 4194304
[root@cent7z ~]# sysctl -p


net.ipv4.tcp_r(w)mem含义为：自动优化所使用的接收缓冲区

6、将监听修改为静态监听

vi listener.ora（创建静态监听）这是主库的
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1/)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU=32767)
(SID_NAME = cwai)
(GLOBAL_DBNAME = cwai)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS = (PROTOCOL = TCP)(HOST = cent7z)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle

vi listener.ora（创建静态监听）这是备库的
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1/)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU=32767)
(SID_NAME = cwai)
(GLOBAL_DBNAME = cwai)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS = (PROTOCOL = TCP)(HOST = cent7)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle


修改完了以后重新加载监听 lsnrctl reload
然后检查监听状态 lsnrctl status

7、修改tnsname.ora文件 主备库都添加下面的内容

这里添加主备库的别名，其中MD代表MASETDATABASE 主库别名；SD代表SLAVEDATABASE 备库别名

vim tnsnames.ora
MD =
(DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=1406250)
(RECV_BUF_SIZE=1406250)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cent7z)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cwai)
)
)

[oracle@cent7z admin]$ tnsping sd
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2024 03:44:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=1406250) (RECV_BUF_SIZE=1406250) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cent7)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cwai)))
OK (10 msec)


8、复制密码文件（从主库到备库）

[oracle@cent7z dbs]$ scp orapwcwai 192.168.1.33:$ORACLE_HOME/dbs
# 连通测试
[oracle@cent7 admin]$ sqlplus sys/oracle@MD as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 18 03:46:35 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> exit


9、主库添加下列参数

alter system set job_queue_processes=10 scope=spfile; --作业进程限制 可加可不加看具体需求
alter system set db_unique_name=‘cwaimd’ scope=spfile; （由于使用高可用集群，所以集群下每个节点都需要有一个唯一名） orclmd=cwai（实例名）md（masterdatabase）
alter system set local_listener=‘MD’ scope=spfile; --本地监听注册别名
alter system set global_names=true scope=both; --开启全局名称
ALTER DATABASE RENAME GLOBAL_NAME TO cwai; --设置全局名称 要和非DG集群时你的实例名保持一致
alter system set db_file_name_convert=’/u01/app/oradata/oracle/’,’/u01/app/oradata/oracle/’ scope=spfile; --db_file_name_convert ：在使用数据复制时，指定主备库的数据复制路径
alter system set log_file_name_convert=’/u01/app/oradata/oracle/’,’/u01/app/oradata/oracle/’ scope=spfile; --log_file_name_convert：在使用数据复制时，指定主备库的日志复制路径（online REDO和standby redo）
–FAL_CLIENT 设定FAL的客户端名称，一般为本地在tnsnames.ora中的别名，该参数只在备库角色时有效，但是ORACLE建议还是在两边都是要设置，为了方便角色切换（主备切换时用）。
alter system set fal_client=‘MD’ scope=spfile;
–这个参数设定备库从哪里获取到归档日志，一般设定为对方（如果是主库的就相对于是备库，如果备库的话就相对于是主库）在tnsnames.ora文件里的别名。
alter system set fal_server=‘SD’ scope=spfile;

SQL> conn /as sysdba
Connected.
SQL> alter system set job_queue_processes=10 scope=spfile;

System altered.
SQL> alter system set fal_client='MD' scope=spfile;
System altered.
SQL> alter system set fal_server='SD' scope=spfile;
System altered.


alter system set log_archive_config = ‘DG_CONFIG=( cwaimd,cwaisd)’ scope=spfile;–启动db接收或发送redo data，包括所有库的db_unique_name
–重新指定归档目录
alter system set log_archive_dest_1=‘location=/u01/app/oradata/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=cwaimd’ scope=spfile;
alter system set log_archive_dest_2=‘service=SD LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=cwaisd’ scope=spfile;
–归档目录的状态
alter system set LOG_ARCHIVE_DEST_STATE_1=‘ENABLE’;
alter system set LOG_ARCHIVE_DEST_STATE_2=‘ENABLE’;
–该参数控制是否自动在备库中建立主库新建的数据文件
注意以下两点：1 不会自动创建日志文件;2 如果数据文件重名会覆盖原有的数据文件。
alter system set standby_file_management=‘AUTO’ scope=spfile;
alter system set service_names=‘cwai’ scope=spfile; --设置一个叫做cwai的服务
alter system set log_archive_max_processes=4 scope=spfile; --设置归档的进程数据量

SQL> alter system set log_archive_config = 'DG_CONFIG=( cwaimd,cwaisd)' scope=spfile;

System altered.


10、修改备库参数

在主库中把spfile备份出一个pfile出来，并修改格式，把cwai._开头的都删除。*.开头的部分格式调整正确（纠正部分错行的、丢失单引号的）

cd $ORACLE_HOME/dbs
strings spfileorcl.ora > cwai_init.ora
scp cwai_init.ora oracle@cent7:$ORACLE_HOME/dbs


进入到备库的$ORACLE_HOME/dbs下,修改部分参数

cd $ORACLE_HOME/dbs
vim orcl_init.ora
*.db_unique_name='cwaisd'
*.global_names=TRUE
*.fal_client='SD'
*.fal_server='MD'
*.local_listener='SD'
*.log_archive_config='DG_CONFIG=( cwaimd,cwaisd)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/cwai/arch valid_for=(all_logfiles,all_roles) db_unique_name=cwaisd'
*.log_archive_dest_2='service=MD LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=cwaimd'

创建归档目录()
mkdir -p /u01/app/oradata/oracle/arch
创建adump目录
mkdir -p /u01/app/oracle/admin/cwai/adump
创建redo目录
mkdir -p /u01/app/oradata/oracle/redo

[oracle@cent7 dbs]$ mkdir -p /u01/app/oradata/oracle/arch
[oracle@cent7 dbs]$ mkdir -p /u01/app/oracle/admin/cwai/adump
[oracle@cent7 dbs]$ mkdir -p /u01/app/oradata/oracle/redo


11、重启主备库监听

[oracle@cent7 admin]$ lsnrctl reload

Connecting to (DESCRIPTION=(SEND_BUF_SIZE=1406250)(RECV_BUF_SIZE=1406250)(ADDRESS=(PROTOCOL=TCP)(HOST=cent7)(PORT=1521)))
The command completed successfully


12、启动备库到nomount状态，根据pile创建spfile后重启数据库到nomount状态。

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/db_1/dbs/cwai_init.ora';
ORACLE instance started.

防止redo复制时出错
用该命令检查一下参数:SHOW PARAMETER STANDBY_FILE_MANAGEMENT

13、在主库通过Rman Duplicate创建备库

[oracle@cent7z dbs]$ rman target sys/oracle@MD auxiliary sys/oracle@SD nocatalog

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1161467233 file name=/u01/app/oradata/oracle/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1161467233 file name=/u01/app/oradata/oracle/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1161467233 file name=/u01/app/oradata/oracle/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1161467233 file name=/u01/app/oradata/oracle/hr.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1161467233 file name=/u01/app/oradata/oracle/users01.dbf
Finished Duplicate Db at 2024-02-20 21:47:47


#备库中查看

[oracle@cent7 oracle]$ ls -lh
total 2.9G
drwxr-xr-x 2 oracle dba 4.0K Feb 20 21:51 arch
-rw-r----- 1 oracle dba 11M Feb 20 21:54 control01.ctl
-rw-r----- 1 oracle dba 11M Feb 20 21:54 control02.ctl
-rw-r----- 1 oracle dba 101M Feb 20 21:47 hr.dbf
drwxr-xr-x 2 oracle dba 4.0K Feb 20 01:53 redo
-rw-r----- 1 oracle dba 201M Feb 20 21:47 redo01.log
-rw-r----- 1 oracle dba 201M Feb 20 21:47 redo02.log
-rw-r----- 1 oracle dba 201M Feb 20 21:47 redo03.log
-rw-r----- 1 oracle dba 201M Feb 20 21:51 stdredo01.log
-rw-r----- 1 oracle dba 201M Feb 20 21:54 stdredo02.log
-rw-r----- 1 oracle dba 201M Feb 20 21:47 stdredo03.log
-rw-r----- 1 oracle dba 201M Feb 20 21:47 stdredo04.log
-rw-r----- 1 oracle dba 501M Feb 20 21:46 sysaux01.dbf
-rw-r----- 1 oracle dba 801M Feb 20 21:46 system01.dbf
-rw-r----- 1 oracle dba 71M Feb 20 21:47 undotbs01.dbf
-rw-r----- 1 oracle dba 5.1M Feb 20 21:47 users01.dbf
SQL> select member from v$logfile;

14、到备库开启实时日志应用

SQL> alter database open;

standby_file_management --备库文件管理“AUTO-自动模式，MANUAL-手动模式”

15、重启数据库，恢复介质

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

DATABASE_ROLE
------------------------------------------------
PROTECTION_MODE
------------------------------------------------------------
OPEN_MODE
------------------------------------------------------------
PHYSICAL STANDBY
MAXIMUM PERFORMANCE
READ ONLY WITH APPLY


至此实验12c搭建ADG配置完成

16、主库建表，备库验证存在

# 备库查看主库原有的表
SQL> select table_name from all_tables t where t.OWNER='HR';

实验过程中遇到的问题需注意：
实验过程中备库未关防火墙导致rman复制失败

连接备库状态为not started 实际未连接上数据库

[root@cent7 ~]# systemctl stop firewalld
[root@cent7 ~]# systemctl disable firewalld


关闭防火墙后连接备库状态为not mounted 后方可正常rman复制。