Oracle 19c Data Guard 配置

1. Oracle基础环境

Oracle Data Guard 环境描述

1.1 开启FRA

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string

db_recovery_file_dest_size big integer 0

-- 配置FRA路径

SQL> alter system set db_recovery_file_dest = '/ora/oracle/fast_recovery_area';

-- 配置FRA大小

SQL> alter system set db_recovery_file_dest_size = '50G';

1.2 开启数据库闪回

SQL> alter database flashback on;

1.3 开启归档

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /ora/oracle/archive

Oldest online log sequence 153

Next log sequence to archive 155

Current log sequence 155

-- 由于数据库在安装时开启了归档，所以本步骤可以省略。开启归档方法如下：

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

1.4 开启force logging

SQL> select force_logging from v$database;

FORCE_LOGGING

---------------------------------------

NO

SQL> alter database force logging;

SQL> select force_logging from v$database;

FORCE_LOGGING

---------------------------------------

YES

1.5. 添加standby redo logfile 日志文件，standby redo logfile为redo logfile + 1

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 MB,MEMBERS,STATUS from v$log;

GROUP# THREAD# SEQUENCE# MB MEMBERS STATUS

---------- ---------- ---------- ---------- ---------- ----------------

1 1 154 512 1 INACTIVE

2 1 155 512 1 CURRENT

3 1 153 512 1 INACTIVE

SQL> alter database add standby logfile '/ora/oracle/oradata/ORCL/standby_redo01.log' size 512M;

SQL> alter database add standby logfile '/ora/oracle/oradata/ORCL/standby_redo02.log' size 512M;

SQL> alter database add standby logfile '/ora/oracle/oradata/ORCL/standby_redo03.log' size 512M;

SQL> alter database add standby logfile '/ora/oracle/oradata/ORCL/standby_redo04.log' size 512M;

SQL> set linesize 999;

SQL> col member format a70;

SQL> select GROUP#,STATUS,TYPE,MEMBER from V$LOGFILE;

GROUP# STATUS TYPE MEMBER

---------- ------- ------- ----------------------------------------------------------------------

3 ONLINE /ora/oracle/oradata/ORCL/redo03.log

2 ONLINE /ora/oracle/oradata/ORCL/redo02.log

1 ONLINE /ora/oracle/oradata/ORCL/redo01.log

4 STANDBY /ora/oracle/oradata/ORCL/standby_redo02.log

5 STANDBY /ora/oracle/oradata/ORCL/standby_redo03.log

6 STANDBY /ora/oracle/oradata/ORCL/standby_redo04.log

7 STANDBY /ora/oracle/oradata/ORCL/standby_redo01.log

7 rows selected.

1.6 配置DB_NAME DB_UNIQUE_NAME INSTANCE_NAME SERVICE_NAME，DB_UNIQUE_NAME

需要唯一

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string orcl

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string mdb

SQL> show parameter instance_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

instance_name string orcl

2. 配置主备数据库listener和tnsname

2.1 主库监听配置

[oracle@mdb ~]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /ora/oracle/product/19c/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = mdb) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

(SID_DESC = (GLOBAL_DBNAME = mdb_dgmgrl) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

(SID_DESC = (GLOBAL_DBNAME = hkgl) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

)

LISTENER = (DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))))

2.2 主库tnsname

[oracle@mdb ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /ora/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mdb)))

MDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mdb)))

SDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sdb)))

HKGL = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=hkgl)))

2.3 备库监听

[oracle@sdb ~]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /ora/oracle/product/19c/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = sdb) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

(SID_DESC = (GLOBAL_DBNAME = sdb_dgmgrl) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

(SID_DESC = (GLOBAL_DBNAME = hkgl) (ORACLE_HOME = /ora/oracle/product/19c/dbhome_1) (SID_NAME = orcl))

)

LISTENER = (DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))))

2.4 备库tnsname

[oracle@sdb ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /ora/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sdb)))

MDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mdb)))

SDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sdb)))

HKGL = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=hkgl)))

2.5 测试监听

[oracle@sdb ~]$ tnsping MDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-SEP-2022 09:54:02

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:

/ora/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.157.0)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mdb)))

OK (10 msec)

[oracle@sdb ~]$ tnsping SDB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-SEP-2022 09:54:07

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:

/ora/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.82.142)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sdb)))

OK (0 msec)

3. 配置Data Guard

3.1 主库配置

SQL> alter system set log_archive_config='dg_config=(mdb,sdb)';

SQL> alter system set db_unique_name='mdb' scope=spfile;

SQL> alter system set log_archive_dest_2='service=sdb lgwr async valid_for=(online_logfile,primary_role) db_unique_name=sdb';

SQL> alter system set standby_file_management=auto scope=both sid='*';

SQL> alter system set log_archive_dest_1='LOCATION=/ora/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=mdb' scope=spfile;

SQL> alter system set fal_client='mdb' scope=both sid='*';

SQL> alter system set fal_server='sdb' scope=both sid='*';

SQL> alter system set db_file_name_convert='/ora/oracle/oradata/ORCL','/ora/oracle/oradata/ORCL' scope=spfile sid='*';

SQL> alter system set log_file_name_convert='/ora/oracle/oradata/ORCL','/ora/oracle/oradata/ORCL' scope=spfile sid='*';

4. 拷贝主库orapworcl和initorcl.ora文件到备库

[oracle@mdb dbs]$ scp initorcl.ora orapworcl oracle@sdb:$ORACLE_HOME/dbs

5. 修改备库参数文件，并将备库启动为nomount

*.db_file_name_convert='/ora/oracle/oradata/ORCL','/ora/oracle/oradata/ORCL'

*.db_name='orcl'

*.db_recovery_file_dest_size=64424509440

*.db_recovery_file_dest='/ora/oracle/fast_recovery_area'

*.db_unique_name='mdb'

*.dg_broker_start=TRUE

*.diagnostic_dest='/ora/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.enable_pluggable_database=true

*.fal_client='sdb'

*.fal_server='mdb'

*.local_listener='LISTENER'

*.log_archive_config='dg_config=(mdb,sdb)'

*.log_archive_dest_1='LOCATION=/ora/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=mdb'

*.log_archive_dest_2='service="sdb"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="sdb" net_timeout=30','valid_for=(online_logfile,all_roles)'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='/ora/oracle/oradata/ORCL','/ora/oracle/oradata/ORCL'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=1200

*.pga_aggregate_target=1245m

*.processes=1200

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=300

*.sessions=1822

*.sga_target=4979m

SQL> startup nomount pfile='/ora/oracle/product/19c/dbhome_1/dbs/initorcl.ora';

6. rman duplicate 拷贝数据到备库

[oracle@sdb ~]$ rman target sys/Oracle19C@MDB auxiliary sys/Oracle19C@SDB

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

7. 开启实时日志

alter database recover managed standby database using current logfile disconnect from session;