Oracle 19c Data Guard物理备库搭建-关系型数据库-互联网-天达云

Oracle 19c Data Guard物理备库搭建
更新：HHH 时间：2023-1-7

部署目标

搭建
- 部署目标
- 部署环境介绍
操作步骤
(1). 查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志；

[oracle@sdedu ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 20:04:29 2019

Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

ARCHIVELOG NO

(2). 查看主库归档目的地，从下面结果中可以看到，归档目的地使用了快速恢复区；

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

(3). 开启主库附加日志，并验证开启的结果；

SQL> alter database force logging;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

ARCHIVELOG YES

从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。

STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。

SQL> alter database set standby nologging for data availability;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITY

STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。

SQL> alter database set standby nologging for load performance;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

NOARCHIVELOG STANDBY NOLOGGING FOR LOAD PERFORMANCE

(4). 在主库中添加附加日志；

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SS19P/stredo04.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SS19P/stredo05.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SS19P/stredo06.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SS19P/stredo07.log') size 100m;

Database altered.

(5). 修改主库参数；

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SS19P,SS19S)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19P';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SS19S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19S';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;

System altered.

SQL> alter system set FAL_SERVER=SS19S;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> quit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

(6). 编辑主库 listener.ora 网络配置文件，添加连接主库点静态注册；

[oracle@sdedu ~]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/

[oracle@sdedu admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

   (GLOBAL_DBNAME = SS19P.sandata.com.cn)

   (SID_NAME = SS19P)

   (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)

   )

   )

(7). 编辑主库 tnsnames.ora 网络配置文件，添加连接备库的本地服务名；

[oracle@sdedu admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_SS19P =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

SS19P =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19P.sandata.com.cn)

)

)

SS19S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19S.sandata.com.cn)

)

)

(8). 将主库中的网络文件和口令文件传输到备库；

[oracle@sdedu admin]$ scp listener.ora tnsnames.ora sdrep:`pwd`

oracle@sdrep's password:

listener.ora 100% 558 451.3KB/s 00:00

tnsnames.ora 100% 652 580.3KB/s 00:00

[oracle@sdedu admin]$ cd ../../dbs

[oracle@sdedu dbs]$ scp orapwSS19P sdrep:`pwd`/orapwSS19S

oracle@sdrep's password:

orapwSS19P

(9). 备库中创建对应路径：

[oracle@sdrep ~]$ cd /u01/app/oracle/

[oracle@sdrep oracle]$ mkdir -p admin/SS19S/adump

[oracle@sdrep oracle]$ mkdir -p oradata/SS19S

[oracle@sdrep oracle]$ mkdir -p fast_recovery_area/SS19S

(10). 创建备库的参数文件：

[oracle@sdrep oracle]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/dbs/

[oracle@sdrep dbs]$ vi initSS19S.ora

DB_NAME=SS19S

(11). 修改备库的 listener.ora 配置文件：

[oracle@sdrep dbs]$ vi ../network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SS19S.example.com )(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = SS19S.sandata.com.cn )

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = SS19S )

   )

   )

(12). 修改备库的 tnsnames.ora 配置文件：

[oracle@sdedu admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_SS19P =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn )(PORT = 1521))

SS19P =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19P.sandata.com.cn)

)

)

SS19S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19S.sandata.com.cn)

)

)

(13). 启动辅助实例；

[oracle@sdrep dbs]$ export ORACLE_SID=SS19S

[oracle@sdrep dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 16:41:03 2019

Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1543500824 bytes

Fixed Size 9135128 bytes

Variable Size 1006632960 bytes

Database Buffers 520093696 bytes

Redo Buffers 7639040 bytes

Database mounted.

SQL> quit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

(14). 使用复制数据库技术搭建备库，由于备库的文件路径包含SS19S，因此需要写明audit_file_dest,control_files参数（使用单引号也可以）；

[oracle@sdrep dbs]$ rman target sys/oracle@SS19P auxiliary sys/oracle@SS19S

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 20:51:58 2019

Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: SS19P (DBID=592912636)

connected to auxiliary database: SS19P (not mounted)

RMAN> DUPLICATE TARGET DATABASE

2>    FOR STANDBY

3>    FROM ACTIVE DATABASE

4>    DORECOVER

5>    SPFILE

6>    SET DB_UNIQUE_NAME="SS19S" COMMENT "Is a dbstyle duplicate"

7>    SET AUDIT_FILE_DEST="/u01/app/oracle/admin/SS19S/adump"

8>    SET CONTROL_FILES='/u01/app/oracle/oradata/SS19S/control01.ctl','/u01/app/oracle/fast_recovery_area/SS19S/control02.ctl'

9>    SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(SS19S,SS19P)"

10>    SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19S"

11>    SET LOG_ARCHIVE_DEST_2="SERVICE=SS19P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19P"

12>    SET DB_FILE_NAME_CONVERT="SS19P","SS19S"

13>    SET LOG_FILE_NAME_CONVERT="SS19P","SS19S"

14>    SET FAL_SERVER="SS19P" COMMENT "Is primary"

15>    SET STANDBY_FILE_MANAGEMENT="AUTO"

16>    NOFILENAMECHECK;

Starting Duplicate Db at 06-MAR-19

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=21 device type=DISK

current log archived

省略部分 ………

released channel: ORA_DISK_1

released channel: ORA_AUX_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=53 device type=DISK

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_10_g7zjp7gs_.arc RECID=1 STAMP=1002228807

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_11_g7zjp8po_.arc RECID=2 STAMP=1002228808

Deleted 2 objects

Finished Duplicate Db at 06-MAR-19

RMAN> quit

Recovery Manager complete.

(15). 进入备库验证角色信息；

[oracle@sdrep dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 20:54:39 2019

Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

SS19S

(16). 在备库启动recover 过程，应用主库传过来的日志（默认已经是real-time apply模式，因此省略using current logfile）；

SQL> recover managed standby database disconnect from session;

Media recovery complete.

(17). 查询v$dataguard_process 视图，验证来自主库传输过来的日志过程和备库应用日志的情况（v$dataguard_process视图在12.2版本出现，取代了v$managed_standby)；

SQL> select role,thread#,sequence#,action from v$dataguard_process;

ROLE THREAD# SEQUENCE# ACTION

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

recovery apply slave 0 0 IDLE

archive local       0 0 IDLE

redo transport timer    0 0 IDLE

gap manager       0 0 IDLE

recovery logmerger       1 12 WAIT_FOR_LOG

recovery apply slave          0 IDLE

managed recovery       0 IDLE

archive redo       0 0 IDLE

archive redo    0    0 IDLE

archive redo    0 0 IDLE

redo transport monitor 0    0 IDLE

log writer 0 0 IDLE

12 rows selected.

(18). 查询 v$archived_log 视图，验证来自主库传输过来的日志变化情况，下面输出可以看到主库传输过来的日志在增加；

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

12 YES

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

12 YES

13 YES

两节点 Oracle 19c Dataguard 环境

搭建两节点 Oracle 19c Dataguard 环境
主库不关闭
使用物理备库
不使用Broker
操作系统版本：OEL 7.6
数据库版本：Oracle Database 19c
两台主机名为：sdedu， sdrep
主库名称：SS19P
备库名称：SS19S

主库不关闭
使用物理备库
不使用Broker

部署环境介绍

操作系统版本：OEL 7.6
数据库版本：Oracle Database 19c
两台主机名为：sdedu， sdrep
主库名称：SS19P
备库名称：SS19S

操作步骤

(1). 查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志；

[oracle@sdedu ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 20:04:29 2019

Version 19.2.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

ARCHIVELOG NO

(2). 查看主库归档目的地，从下面结果中可以看到，归档目的地使用了快速恢复区；

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

(3). 开启主库附加日志，并验证开启的结果；

SQL> alter database force logging;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

ARCHIVELOG YES

从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。

STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。

SQL> alter database set standby nologging for data availability;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITY

STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。

SQL> alter database set standby nologging for load performance;

Database altered.

SQL> select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

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

NOARCHIVELOG STANDBY NOLOGGING FOR LOAD PERFORMANCE

(4). 在主库中添加附加日志；

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SS19P/stredo04.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SS19P/stredo05.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SS19P/stredo06.log') size 100m;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SS19P/stredo07.log') size 100m;

Database altered.

(5). 修改主库参数；

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SS19P,SS19S)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19P';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SS19S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19S';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;

System altered.

SQL> alter system set FAL_SERVER=SS19S;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> quit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

(6). 编辑主库 listener.ora 网络配置文件，添加连接主库点静态注册；

[oracle@sdedu ~]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/

[oracle@sdedu admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = SS19P.sandata.com.cn)

(SID_NAME = SS19P)

(ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)

)

(7). 编辑主库 tnsnames.ora 网络配置文件，添加连接备库的本地服务名；

[oracle@sdedu admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_SS19P =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

SS19P =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19P.sandata.com.cn)

)

SS19S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19S.sandata.com.cn)

)

(8). 将主库中的网络文件和口令文件传输到备库；

[oracle@sdedu admin]$ scp listener.ora tnsnames.ora sdrep:`pwd`

oracle@sdrep's password:

listener.ora 100% 558 451.3KB/s 00:00

tnsnames.ora 100% 652 580.3KB/s 00:00

[oracle@sdedu admin]$ cd ../../dbs

[oracle@sdedu dbs]$ scp orapwSS19P sdrep:`pwd`/orapwSS19S

oracle@sdrep's password:

orapwSS19P

(9). 备库中创建对应路径：

[oracle@sdrep ~]$ cd /u01/app/oracle/

[oracle@sdrep oracle]$ mkdir -p admin/SS19S/adump

[oracle@sdrep oracle]$ mkdir -p oradata/SS19S

[oracle@sdrep oracle]$ mkdir -p fast_recovery_area/SS19S

(10). 创建备库的参数文件：

[oracle@sdrep oracle]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/dbs/

[oracle@sdrep dbs]$ vi initSS19S.ora

DB_NAME=SS19S

(11). 修改备库的 listener.ora 配置文件：

[oracle@sdrep dbs]$ vi ../network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SS19S.example.com )(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = SS19S.sandata.com.cn )

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = SS19S )

)

(12). 修改备库的 tnsnames.ora 配置文件：

[oracle@sdedu admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_SS19P =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn )(PORT = 1521))

SS19P =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19P.sandata.com.cn)

)

SS19S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.sandata.com.cn)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SS19S.sandata.com.cn)

)

(13). 启动辅助实例；

[oracle@sdrep dbs]$ export ORACLE_SID=SS19S

[oracle@sdrep dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 16:41:03 2019

Version 19.2.0.0.0

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1543500824 bytes

Fixed Size 9135128 bytes

Variable Size 1006632960 bytes

Database Buffers 520093696 bytes

Redo Buffers 7639040 bytes

Database mounted.

SQL> quit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

(14). 使用复制数据库技术搭建备库，由于备库的文件路径包含SS19S，因此需要写明audit_file_dest,control_files参数（使用单引号也可以）；

[oracle@sdrep dbs]$ rman target sys/oracle@SS19P auxiliary sys/oracle@SS19S

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 20:51:58 2019

Version 19.2.0.0.0

connected to target database: SS19P (DBID=592912636)

connected to auxiliary database: SS19P (not mounted)

RMAN> DUPLICATE TARGET DATABASE

2> FOR STANDBY

3> FROM ACTIVE DATABASE

4> DORECOVER

5> SPFILE

6> SET DB_UNIQUE_NAME="SS19S" COMMENT "Is a dbstyle duplicate"

7> SET AUDIT_FILE_DEST="/u01/app/oracle/admin/SS19S/adump"

8> SET CONTROL_FILES='/u01/app/oracle/oradata/SS19S/control01.ctl','/u01/app/oracle/fast_recovery_area/SS19S/control02.ctl'

9> SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(SS19S,SS19P)"

10> SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19S"

11> SET LOG_ARCHIVE_DEST_2="SERVICE=SS19P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19P"

12> SET DB_FILE_NAME_CONVERT="SS19P","SS19S"

13> SET LOG_FILE_NAME_CONVERT="SS19P","SS19S"

14> SET FAL_SERVER="SS19P" COMMENT "Is primary"

15> SET STANDBY_FILE_MANAGEMENT="AUTO"

16> NOFILENAMECHECK;

Starting Duplicate Db at 06-MAR-19

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=21 device type=DISK

current log archived

省略部分 ………

released channel: ORA_DISK_1

released channel: ORA_AUX_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=53 device type=DISK

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_10_g7zjp7gs_.arc RECID=1 STAMP=1002228807

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_11_g7zjp8po_.arc RECID=2 STAMP=1002228808

Deleted 2 objects

Finished Duplicate Db at 06-MAR-19

RMAN> quit

Recovery Manager complete.

(15). 进入备库验证角色信息；

[oracle@sdrep dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 20:54:39 2019

Version 19.2.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

SS19S

(16). 在备库启动recover 过程，应用主库传过来的日志（默认已经是real-time apply模式，因此省略using current logfile）；

SQL> recover managed standby database disconnect from session;

Media recovery complete.

(17). 查询v$dataguard_process 视图，验证来自主库传输过来的日志过程和备库应用日志的情况（v$dataguard_process视图在12.2版本出现，取代了v$managed_standby)；

SQL> select role,thread#,sequence#,action from v$dataguard_process;

ROLE THREAD# SEQUENCE# ACTION

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

recovery apply slave 0 0 IDLE

archive local 0 0 IDLE

redo transport timer 0 0 IDLE

gap manager 0 0 IDLE

recovery logmerger 1 12 WAIT_FOR_LOG

recovery apply slave 0 IDLE

managed recovery 0 IDLE

archive redo 0 0 IDLE

redo transport monitor 0 0 IDLE

log writer 0 0 IDLE

12 rows selected.

(18). 查询 v$archived_log 视图，验证来自主库传输过来的日志变化情况，下面输出可以看到主库传输过来的日志在增加；

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

12 YES

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

10 YES

11 YES

12 YES

13 YES


返回关系型数据库教程...

新手上路

产品管理

支付方式

关于我们