Oracle 19c Data Guard物理備庫搭建

• 搭建

  • 部署目標

• • 部署環境介紹

  操作步驟

• (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