×
028-86922220

建站动态

根据您的个性需求进行定制 先人一步 抢占小程序红利时代

Oracle19cDataGuard物理备库搭建

  1. 部署目标

    成都创新互联公司是一家专注于成都网站制作、做网站与策划设计,松原网站建设哪家好?成都创新互联公司做网站,专注于网站建设10年,网设计领域的专业建站公司;建站业务涵盖:松原等地区。松原做网站价格咨询:18980820575

  1. 部署环境介绍

  1. 操作步骤

(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


标题名称：Oracle19cDataGuard物理备库搭建
文章链接：http://www.tsicrk.com/article/jcpgih.html

其他资讯

让你的专属顾问为你服务

1.7046s