一、 本文說明

針對於11g 版本以上的資料庫，在建立備庫時，通常都會在主庫執行以下命令：

RMAN> duplicate target database for standby from active database;

這裡使用的是 from active database 。在這種情況下，複製整個主庫到備庫，會佔用大量業務網路頻寬。尤其是當生產主庫很大的時候，，可能在網路層就會影響業務的進行。並且在這種方式下，需要給備庫新增靜態監聽，避免不了要重啟監聽。如果備庫伺服器上同時跑其他的資料庫，那該庫也會暫停一下業務。

在10g 版本以後，可以透過以下方式進行復制主庫到備庫

第一步：主庫備份‘所有資料庫檔案’

第二步：主庫備份‘備庫控制檔案’

第三步：將上面兩個備份傳輸到備庫主機，並保證放置的目錄與備份的目錄一致。如果條件允許，可以將主庫的備份寫到備份伺服器上，然後將備份掛載到備庫伺服器上即可。

第四步：使用rman 執行以下命令：

RMAN> duplicate target database for standby ;

此時，不會佔用業務網路頻寬，備庫會自動將備份進行還原，以此方式來建立備庫。

本文可以理解為是第二種方式的“衍生版”，主庫不需要執行 RMAN 的 duplicate 命令，以備庫還原的方式來實現物理 DG 的搭建。

二、 規劃

三、 準備工作

1.  關閉 防火牆 （主備庫）

# systemctl stop firewalld

# systemctl disable firewalld

2.  禁用selinux 防火牆 （主備庫）

# vi /etc/selinux/config

selinux= disabled

3.  檢查主庫 歸檔設定

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination             /archivelog/ORCL

Oldest online log sequence     282

Next log sequence to archive   28 4

Current log sequence          28 4

4.  配置/etc/hosts 檔案（主備庫）

# vi /etc/hosts

# Primary IP

172.16.70.178  primary

#Standby IP

172.16.70.179  standby

四、 搭建DG

1.  開啟強制日誌模式（主庫）

SQL> alter database force logging;

2.  修改tnsname s .ora 檔案（主備庫 ）

（主備庫一致）

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL   =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary )(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL )     

    )

  )

SBDB  =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby )(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = SBDB )

    )

  )

3.  修改 spfile 引數檔案

（ 主庫修改 引數 ）

1）  生成引數檔案

SQL> create pfile from spfile;

2）  修改引數檔案

$ cd /u01/app/oracle/prod uct/11.2.0/db_1/dbs

$ vi init ORCL .ora

新增 以下內容 ：

db_unique_name= ORCL

log_archive_config='dg_config=( ORCL , SBDB )'

log_archive_dest_1='location=/archivelog /ORCL  valid_for=(all_logfiles,all_roles) db_unique_name= ORCL '

log_archive_dest_2='service= SBDB  lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name= SBDB '

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

db_ file_name_convert='/u01/app/oracle/oradata/ SBDB ','/u01/app/oracle/oradata/ ORCL '

log_file_name_convert='/u01/app/oracle/oradata/ SBDB ','/u01/app/oracle/oradata/ ORCL '

fal_server= SBDB

fal_client= ORCL

standby_file_management=auto    

3）  生成spfile ，重啟庫使引數生效

SQL> shutdown immediate;

SQL> create spfile from pfile;

SQL> st artup ;

（備庫修改 引數 ）

1）  將主庫pfile 傳到備庫

$ scp initORCL.ora standby:$ORACLE_HOME/dbs/initSBDB.ora

2）  修改引數檔案

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

$ vi init SBDB .ora

執行以下命令

:%s/ORCL/AAAA/g

:%s/SBDB/ORCL/g

:%s/AAAA/SBDB/g

最後將db_name 修改回 ORCL

最後結果如下：

*.audit_file_dest='/u01/app/oracle/admin/SBDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/SBDB/control01.ctl','/u01/app/oracle/oradata/SBDB/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORCL'

*.diagnostic_dest='/u01/app/oracle'

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

*.log_archive_dest_1='LOCATION=/archivelog/SBDB'

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

*.memory_target=769654784

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

db_unique_name= SBDB

log_archive_config='dg_config=( SBDB,ORCL )'

log_archive_dest_1='location= /archivelog/SBDB valid_for=(all_logfiles,all_roles) db_unique_name= SBDB '

log_archive_dest_2='service= ORCL  lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name= ORCL '

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

db_file_name_convert='/u01/app/oracle/oradata/ ORCL ','/u01/app/oracle/oradata/ SBDB '

log_file_name_convert='/u01/app/oracle/oradata/ ORCL ','/u01/app/oracle/oradata/ SBDB '

fal_server= ORCL

fal_client= SBDB

standby_file_management=auto

3）  備庫建立上述目錄

$ mkdir -p /u01/app/oracle/admin/SBDB/adump

$ mkdir -p /u01/app/oracle/oradata/SBDB

$ mkdir -p /archivelog/SBDB

4）  備庫建立密碼檔案

$ cd $ORACLE_HOME/dbs/

$ orapwd file=orapwSBDB password=oracle

4.  備份主庫

1）  主庫備份資料庫檔案

RMAN> backup database format '/home/oracle/ backup/ backup_orcl_%T_% U .bak';

2）  主庫備份備庫控制檔案

RMAN> backup current controlfile for standby format '/home/oracle/backup/control_std.ctl';

3）  將上面的備份傳輸至備庫相同位置

$ scp /home/oracle/backup/* standby:/home/oracle/backup/

5.  還原備庫

1） 備庫啟動至nomount 狀態

$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

2） 備庫還原控制檔案

$ rman target /

RMAN> restore controlfile from '/home/oracle/backup/control_std.ctl';

3） 備庫開啟到mount 狀態

SQL> alter database mount;

4） 執行還原資料庫檔案

RMAN> restore database;

注意不要執行recover database 命令

5） 主備庫建立standby logfile;

（主庫）

SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;

SQL> select member ,type  from v$logfile;

 

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ ORCL /redo04.log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ ORCL /redo0 5 .log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ ORCL /redo0 6 .log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ ORCL /redo0 7 .log' size 50m;

（備庫）

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ SBDB /redo04.log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ SBDB /redo0 5 .log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ SBDB /redo0 6 .log' size 50m;

SQL> alter database add stan d by logfile '/u01/app/oracle/oradata/ SBDB /redo0 7 .log' size 50m;

6） 備庫執行應用日誌

（此時備庫應處於mount 狀態）

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

此時alert 日誌如下：

Tue Nov 10 10:56:04 2020

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

Attempt to start background Managed Standby Recovery process (SBDB)

Tue Nov 10 10:56:04 2020

MRP0 started with pid=31, OS id=7958

MRP0: Background Managed Standby Recovery process started (SBDB)

 started logmerger process

Tue Nov 10 10:56:09 2020

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 2 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Additional information: 3

Clearing online redo logfile 1 /u01/app/oracle/oradata/SBDB/redo01.log

Clearing online log 1 of thread 1 sequence number 280

Additional information: 3

Clearing online redo logfile 1 complete

Additional information: 3

Clearing online redo logfile 2 /u01/app/oracle/oradata/SBDB/redo02.log

Clearing online log 2 of thread 1 sequence number 281

Additional information: 3

Completed: alter database recover managed standby database using current logfile disconnect from session

Clearing online redo logfile 2 complete

Additional information: 3

Clearing online redo logfile 3 /u01/app/oracle/oradata/SBDB/redo03.log

Clearing online log 3 of thread 1 sequence number 282

Additional information: 3

Clearing online redo logfile 3 complete

Media Recovery Log /archivelog/SBDB/1_275_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_276_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_277_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_278_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_279_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_280_1047466707.dbf

Media Recovery Log /archivelog/SBDB/1_281_1047466707.dbf

Media Recovery Waiting for thread 1 sequence 282 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 282 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/SBDB/redo04.log

7） 備庫取消實時應用

SQL>  alter database recover managed standby database cancel;

8 ）開啟備庫，開啟實時應用

SQL> alter database open;

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

此時已經完成DG 搭建

6.  檢查DG 狀態

1 ） 檢查 備 庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME     OPEN_MODE                DATABASE_ROLE       SWITCHOVER_STATUS

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

SBDB                  READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

2） 檢視主庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME     OPEN_MODE                DATABASE_ROLE       SWITCHOVER_STATUS

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

ORCL                   READ WRITE               PRIMARY TO STANDBY

3 ） 備庫程式狀態

SQL> select process, pid, status, client_process from v$managed_standby;

 

PROCESS          PID STATUS       CLIENT_P

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

ARCH            7007 CLOSING      ARCH

ARCH            7009 CONNECTED    ARCH

ARCH            7011 CLOSING      ARCH

ARCH            7013 CLOSING      ARCH

RFS             7040 IDLE         ARCH

RFS             7031 IDLE         UNKNOWN

RFS             7021 IDLE         UNKNOWN

RFS             7033 IDLE         LGWR

MRP0            8265 APPLYING_LOG N/A

 

此時，備庫已經是實時應用狀態（ Active Data Guard ）

 

 

 

---- end ----