19c单机ADG

1. ADG部署步骤
   主库IP：192.168.1.9 北京
   备库IP：192.168.1.200 上海
   1.1. 主库相关配置操作
   1.1.1. 主库归档模式开启
   sqlplus / as sysdba
   select open_mode from gv$database;
   OPEN_MODE

---

READ WRITE

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /Archivelog
Oldest online log sequence 312
Next log sequence to archive 314
Current log sequence 314

1.1.2. 主库打开force logging

SQL> select force_logging,flashback_on from gv$database;

FORCE_LOG FLASHBACK_ON

---

NO NO

SQL> alter database force logging;

Database altered.

SQL> Col force_logging for a20
Col FLASHBACK_ON for a20
Set lines 200 pages 80
select force_logging,flashback_on from gv$database;

FORCE_LOGGING FLASHBACK_ON

---

YES NO

1.1.3. 拷贝主库的密码文件到备库服务器
主库拷贝密码文件到备库服务器,这里注意一下确认主库当前使用的密码文件，如果有多个，确定当前使用的是哪一个，如果没有使用需要指定：

cd $ORACLE_HOME/dbs
scp orapwORCL 192.168.1.200:/oracle/app/oracle/product/19.3.0/db_1/dbs

1.1.4. 主库配置监听程序

编辑listener配置文件：

su - grid
vi /oracle/app/19.3.0/grid/network/admin/listener.ora

添加如下内容：

LISTENER_ADG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1525))
)
)

SID_LIST_LISTENER_ADG =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
(GLOBAL_DBNAME=ORCL)
)

完成以后启动listener：

lsnrctl start LISTENER_ADG
lsnrctl status LISTENER_ADG

1.1.5. 主库配置tnsname
编辑tns配置文件：

cd /oracle/app/oracle/product/19.3.0/db_1/network/admin
vi tnsnames.ora

添加如下内容：

ORCL_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

ORCL_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLp)
)
)

1.1.6. 在主库使用oracle用户创建pfile并传递到备库

Sqlplus / as sysdba

create pfile=’/tmp/pfile.ora’ from spfile;

exit;

scp /tmp/pfile.ora 192.168.1.200:/tmp/

1.1.7. 后期主库的部分参数修改

修改如下参数（需要个性化配置的参数标红）：

alter system set db_create_file_dest=’/oradata/ORCL’ scope=both;
alter system set archive_lag_target=0 scope=both;
alter system set log_archive_max_processes=8;
alter system set log_archive_config=‘dg_config=(ORCL,ORCLp)’ scope=both;
alter system set log_archive_dest_state_2=DEFER scope=both;
alter system set log_archive_dest_2=’’ scope=both;
alter system set log_archive_dest_2=‘SERVICE=ORCL_PRI LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLp’ scope=both;
alter system set fal_client=‘ORCL_STD’ scope=both;
alter system set fal_server=‘ORCL_PRI’ scope=both;
alter system set standby_file_management=‘AUTO’ scope=both;
alter system set db_recovery_file_dest_size=100G scope=both;
alter system set db_recovery_file_dest=’/Archivelog’ scope=both;
alter system set local_listener= ‘(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.9)(PORT=1521))’,’(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.9)(PORT=1525))’;
alter system register;

修改以下两个参数需要重启两个节点的数据库。
alter system set db_file_name_convert=’/oradata/ORCLp’,‘oradata/ORCL’ scope=spfile;
alter system set log_file_name_convert=’/oradata/ORCLp’,‘oradata/ORCL’ scope=spfile;

1.2. 物理备库相关配置操作

1.2.1. 备库配置listener
编辑listener配置文件：

su - grid
vi /oracle/app/19.3.0/grid/network/admin/listener.ora
添加如下内容：

LISTENER_ADG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1525))
)
)

SID_LIST_LISTENER_ADG =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
(GLOBAL_DBNAME=ORCLp)
)

1.2.2. 备库配置tnsnames

编辑tns配置文件：

su - oracle

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

添加如下内容：

ORCL_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

ORCL_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLp)
)
)

1.2.3. 备库添加数据库及listener资源(选项)

/oracle/app/19.3.0/grid/bin/crsctl modify resource “ora.cssd” -attr “AUTO_START=1” -unsupported

/oracle/app/19.3.0/grid/bin/crsctl start resource -all

su - grid

srvctl add database -d ORCLp -o /oracle/app/oracle/product/19.3.0/db_1

srvctl add listener -l LISTENER_ADG -p 1525

srvctl start listener

lsnrctl status

1.2.4. 创建audit文件目录
备库节点创建审计目录

mkdir -p /oracle/app/oracle/admin/ORCL/adump

1.2.5. 根据主库的pfile修改成备库的参数文件
在备库上编辑pfile文件：

$vi /tmp/pfile.ora

根据文件内容修改成备库的信息，修改之后的如下，标红部分需要根据备库的情况进行修改：

注释 control_files一行
即

#*.control_files=’/oradata/ORCL/control01.ctl’,’/oradata/ORCL/control02.ctl’

然后添加如下内容：

*.db_recovery_file_dest_size=100G
*.db_recovery_file_dest=’/Archivelog’
*.db_create_file_dest=’/oradata/ORCLp’
*.db_unique_name=‘ORCLp’
*.fal_client=‘ORCL_PRI’
*.fal_server=‘ORCL_STD’
*.log_archive_config=‘dg_config=(ORCL,ORCLp)’
*.log_archive_dest_2=‘SERVICE=ORCL_STD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME