【DG】在Linux平臺上搭建單例項的dataguard--duplicate

lhrbest發表於2019-08-28

【DG】在Linux平臺上搭建單例項的dataguard--duplicate


實驗環境介紹

*** 鑑於生產環境均採用 64 位系統和資料庫軟體,本實驗也將採用這個標準 ***

 

採用vmware workstation 10 (版本隨意,我用的 10 )虛擬機器軟體作為安裝平臺

 

對於真機的要求:CPU INTEL CORE I3 級別或以上 推薦

  AMD 4 核心 CPU 或以上 推薦

記憶體: 建議4 G 以上

系統:WIN7 /8 64 /WINDOWS SERVER 2003/2008 64

硬碟:虛擬機器所在分割槽剩餘空間 5 0G 以上,固態硬碟最好

 

虛擬機器系統: Oracle Enterprise Linux 5 update 5 64 位)

資料庫版本: Oracle 11gR2 11.2.0. 3   64 位)

 

基礎要求:(1 )安裝兩臺虛擬機器 分別 作為 主庫和備庫 硬碟大小隨意。但要保證

根分割槽 2 0G 別給太小了就行。 實體記憶體 1 G SWAP  2 G

        

 

二:安裝資料庫軟體前的系統 配置   (主庫和備庫端都要做下列操作)

1 .基本配置:

一定要用新裝的系統(這裡以紅帽企業版5.5 為例),系統要求最低配置 1G 記憶體, 2Gswap 分割槽,根分割槽 20G 以上,裝好系統後先配置好 yum IP 地址, /etc/sysconfig/network 檔案中的主機名,以及 /etc/hosts 檔案中的 IP 地址和主機名的對應關係。

 

主庫IP 192.168.1.101

主庫主機名:pri.cty.com pri

備庫IP 192.168.1.102

備庫主機名:std.cty.com std

 

2 .裝包

yum install compat-libstdc++-33  ksh gcc gcc-c++ libgomp elfutils-libelf-devel glibc-devel glibc-headers libaio-devel libstdc++-devel sysstat unixODBC unixODBC-devel - y  

 

 

3 .檢視共享記憶體大小,要求最低為 1GB

# df -h /dev/shm

Filesystem            Size  Used   Avail    Use%   Mounted on

tmpfs                 506M     0   506M    0%    /dev/shm

發現不夠,修改

#   vim /etc/fstab

如圖,在tmpfs 那一行的 defaults 後面加上 ,size=1024m

 

儲存退出後重新掛載:

#   mount -o remount /dev/shm

再次檢視共享記憶體:

# df -h /dev/shm

Filesystem            Size  Used   Avail   Use%   Mounted on

tmpfs                1.0G     0  1.0G      0%   /dev/shm

修改成功

 

4 .建立相關的組與使用者:

groupadd oinstall

groupadd dba

g roupadd oper

useradd -g oinstall -G dba,oper oracle

passwd oracle

 

 

5 .建立所需的目錄並賦予特定的屬主和屬組

mkdir /u01/app/oracle - pv

chown oracle.oinstall /u01  -R

建議:最好為單獨分一個區,然後將u01 掛載到該分割槽上(可不做,看情況)

 

 

6 .編輯 limits.conf 檔案

vim /etc/security/limits.conf

新增下面5 行內容:

oracle          soft    nproc   2047

oracle          hard    nproc   16384

oracle          soft    nofile  1024

oracle          hard    nofile  65536

oracle          soft    stack   10240

 

7 .編輯 sysctl.conf 檔案,設定相關引數

vim /etc/sysctl.conf

新增/ 修改下列內容:(注意!下面的引數,若是已經存在,則直接修改數值,不要再新增同樣的引數,相同的引數只能有一個!如果需要修改的引數已經大於下面的數字,則不用修改,請仔細核對!)

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem =250 32000 100 128

fs.file-max = 6815744

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

儲存退出後,別忘了用 sysctl - p 命令使引數生效!

 

8 .編輯 vim /home/oracle/.bash_profile 檔案,配置相關環境變數

新增下列幾行:

export TMP=/tmp

export TMPDIR=/tmp

 

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export ORACLE_SID= pri ( 備庫端設定為 std)

export PATH=$ORACLE_HOME/bin:$PATH

至此,安裝環境相關配置結束。

 

開始 安裝oracle11g 資料庫軟體   (主庫和備庫端都要做下列操作)

1. 掛載 oracle11g 的映象到 /mnt/cdrom 目錄下,並把 3 個包解壓到 /usr/local/src 目錄下

cd /mnt/cdrom/

unzip p10404530_112030_LINUX_1of7.zip -d /usr/local/src

unzip p10404530_112030_LINUX_2of7.zip -d /usr/local/src

unzip p10404530_112030_LINUX_3of7.zip -d /usr/local/src

( 只裝資料庫的話,解壓前兩個包即可,第三個包是 grid)

 

2.   root 身份執行 xhost + ,然後切換到 oracle 使用者進入 /usr/local/src/database 目錄中,執行 runInstaller 開始安裝    (database 是預設解壓完的目錄名 )

# xhost +  

# su -  oracle

$ ./runInstaller           

注意:安裝oracle11g 要求解析度最低為 1024x768, 不然無法完整顯示安裝過程

 

安裝過程:略

注意:主庫在安裝完軟體後需要進行DBCA 建庫操作,推薦不要啟用歸檔模式,會節省時間,備庫端不要建庫!

四:開始搭建Dataguard

 

1 :將資料庫改為強制日誌模式   (此步驟只在主庫上做)

[oracle@ pri  ~]$ sqlplus / as sysdba

 

檢視 當前是否強制日誌模式:

SYS@pri> select name,log_mode,force_logging from v$database;

 

NAME      LOG_MODE     FOR

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

PRI        NOARCHIVELOG NO

 

SYS@pri> alter database force logging;

 

Database altered.

 

SYS@pri> select name,log_mode,force_logging from v$database;

 

NAME      LOG_MODE     FOR

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

PRI        NOARCHIVELOG YES

 

2 建立密碼檔案       (此步驟只在主庫上做)

注意:兩端 分別 建立自己的密碼檔案好像有問題,備庫的密碼檔案需要跟主庫一致,否則導致日誌傳輸不到備庫,有待驗證。我最後是將 主庫 密碼檔案 直接copy 到備庫,重新命名後使用。

[oracle@ pri  ~]$ cd $ORACLE_HOME/dbs

[oracle@ pri  dbs]$ ls

hc_racdb1.dat  init.ora  initracdb1.ora   orapwpri 已經有一個密碼檔案了

[oracle@ pri  dbs]$ orapwd file=orapw pri  password=oracle force=y

這條命令可以手動生成密碼檔案,force=y 的意思是強制覆蓋當前已有的密碼檔案

[oracle@ pri  dbs]$ ls

hc_pri.dat  init.ora  initpri.ora  lkPRI   orapwpri   spfilepri.ora

 

主庫 的密碼檔案copy 給備庫,並重新命名

[oracle@ pri  dbs]$ scp orapwpri   192.168.1.102 :$ORACLE_HOME/dbs/ orapwstd

orapwpri                                    100% 1536     1.5KB/s   00:00    

 

 

 

 

 

 

3 :建立 standby redolog 日誌組   (此步驟只在主庫上做)

原則:

1 standby redo log 的檔案大小與 primary 資料庫 online redo log 檔案大小相同

2 standby redo log 日誌檔案組的個數依照下面的原則進行計算:

Standby redo log 組數公式 >=( 每個 instance 日誌組個數 +1)*instance 個數

假如只有一個節點,這個節點有三組redolog

所以Standby redo log 組數 >=(3+1)*1 == 4

     所以至少需要建立4 Standby redo log

 

檢視當前執行緒與日誌組的對應關係及日誌組的大小:

SYS@pri> select thread#,group#,bytes/1024/1024 from v$log;

 

   THREAD#     GROUP#   BYTES/1024/1024

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

             1              1              50

             1              2              50

            1               3              50

如上,我現在的環境有 redolog ,每個日誌組的大小都是 50M

所以Standby redo log 組數 >=( 3 +1)* 1 == 4

所以至少需要建立 4 Standby redo log ,大小均為 50M

thread :執行緒,只有在多例項資料庫才有用的引數,例如 RAC 環境,單例項不考慮)

 

檢視當前有哪些日誌組及其成員:

SYS@pri>  col member for a50

SYS@pri>   select group#,member from v$logfile;

 

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/pri/redo03.log

         2 /u01/app/oracle/oradata/pri/redo02.log

         1 /u01/app/oracle/oradata/pri/redo01.log

 

手動 建立standby log 日誌組所需的目錄:

(建立新目錄只是為了便於區分,並非必須

[oracle@pri dbs]$ cd /u01/app/oracle/oradata/

[oracle@pri oradata]$ ls

pri

[oracle@pri oradata]$ mkdir standbylog

[oracle@pri oradata]$ ls

pri  standbylog

 

 

 

 

新建 4 個日誌組作為standby redolog 日誌組,大小與原來的日誌組一致:

由於已經存在group1- 3 , ,所以 group 號只能從 4 開始

SYS@pri>  alter database add standby logfile   group 4

  2  ' /u01/app/oracle/oradata/standbylog/ std_ redo0 4 .log '

  3  size 50m;

 

Database altered.

 

SYS@pri>  alter database add standby logfile   group 5

  2  ' /u01/app/oracle/oradata/standbylog/ std_ redo0 5 .log '

  3  size 50m;

 

Database altered.

 

SYS@pri>  alter database add standby logfile   group 6

  2  ' /u01/app/oracle/oradata/standbylog/ std_ redo0 6 .log '

  3  size 50m;

 

Database altered.

 

SYS@pri>  alter database add standby logfile   group 7

  2  ' /u01/app/oracle/oradata/standbylog/ std_ redo0 7 .log '

  3  size 50m;

 

Database altered.

 

檢視standby 日誌組的資訊:

SYS@pri>   select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

 

    GROUP#  SEQUENCE#    STATUS           BYTES/1024/1024

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

         4               0     UNASSIGNED                  50

         5               0     UNASSIGNED                  50

         6               0     UNASSIGNED                  50

         7               0     UNASSIGNED                  50

 

 

檢視當前有哪些日誌組及其成員:

SYS@pri>  set pagesize 100

SYS@pri> col member for a60

SYS@pri>  select group#,member from v$logfile  order by group# ;

 

      GROUP# MEMBER

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

              1 /u01/app/oracle/oradata/pri/redo01.log

              2 /u01/app/oracle/oradata/pri/redo02.log

              3 /u01/app/oracle/oradata/pri/redo03.log

              4 /u01/app/oracle/oradata/standbylog/std_redo04.log

              5 /u01/app/oracle/oradata/standbylog/std_redo05.log

              6 /u01/app/oracle/oradata/standbylog/std_redo06.log

              7 /u01/app/oracle/oradata/standbylog/std_redo07.log

 

 

4 :修改 主庫的 pfile 引數檔案   (此步驟只在主庫上做)

 

檢視spfile 的路徑:

SYS@pri>  show parameter spfile;

 

NAME    TYPE    VALUE

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

spfile     string       /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora

 

spfile 建立一個 pfile, 用於修改:

SYS@pri>  create pfile from spfile;  

 

修改主庫的pfile

[oracle@ pri  ~]$ cd $ORACLE_HOME/dbs

[oracle@ pri  dbs]$ vim init pri .ora

pri.__db_cache_size=125829120

pri.__java_pool_size=4194304

pri.__large_pool_size=4194304

pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

pri.__pga_aggregate_target=146800640

pri.__sga_target=272629760

pri.__shared_io_pool_size=0

pri.__shared_pool_size=125829120

pri.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='pri'   DG 主庫和備庫的 db_name 必須一致, db_unique_name 不一致

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4227858432

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

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

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

下面幾行是需要手動新增的:

*.db_unique_name=' pri '

*.log_archive_config='dg_config=( pri , std )'

*.log_archive_dest_1='location= /u01/app /arch/ pri  valid_for=(all_logfiles,all_roles) db_unique_name= pri '

*.log_archive_dest_2='service= std  valid_for=(online_logfiles,primary_role) db_unique_name= std '

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=4

 

*.fal_server=' std '

*.fal_client=' pri '

*.db_file_name_convert= ' /u01/app/oracle/oradata/std ' , ' /u01/app/oracle/oradata/pri '

*.log_file_name_convert= ' /u01/app/oracle/oradata/std ' , ' /u01/app/oracle/oradata/pri '

*.standby_file_management='auto'

修改完畢,儲存退出

 

手工建立 /u01/app /arch/ pri  

[oracle@ pri  dbs]$ mkdir /u01/app/arch/pri -pv

mkdir: created directory `/u01/app/arch'

mkdir: created directory `/u01/app/arch/pri'

 

5 :用修改過的 pfile 重新 建立一個spfile ,用於啟動資料庫 此步驟只在主庫上做

關閉資料庫:

SYS@pri>  shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SYS@pri>  create spfile from pfile;

 

此時把資料庫改為歸檔模式:   (如果當初建庫時選擇了啟用歸檔,則此步驟忽略)

由於當前資料庫已關閉,首先需要把資料庫啟動到mount 狀態

SYS@pri>   startup mount;  

ORACLE instance started.

Database mounted.

 

SYS@pri>   alter database archivelog; 啟用歸檔模式

Database altered.

 

SYS@pri>   alter database open;   OPEN 資料庫

Database altered.

 

SYS@pri>   archive log list;   檢視是否啟用歸檔模式

Database log mode              Archive Mode

Automatic archival                Enabled

Archive destination                /u01/app/arch/pri

Oldest online log sequence          6

Next log sequence to archive         8

Current log sequence               8

如上,歸檔路徑已經改為/u01/app/arch/pri ,證明對 pfile 的修改已生效

 

檢視當前資料庫是否使用spfile 啟動:

SYS@pri>   show parameter spfile;

 

NAME    TYPE    VALUE

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

spfile     string       /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepri.ora

 

如上,若能看到spfile 的路徑,則證明資料庫是使用 spfile 啟動的,若沒有值,則說明是用 pfile 啟動的

 

確認資料庫已經啟用歸檔模式和強制日誌模式:

SYS@pri>   select name,log_mode,force_logging from v$database;

 

NAME    LOG_MODE     FOR

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

PRI       ARCHIVELOG   YES

 

 

 

 

5 :對主庫進行 RMAN 全備

建立備份所需的目錄並賦予相應許可權:   主庫和備庫 端都 建立)

[root@ pri  ~]# mkdir /rman_backup

[root@ pri  ~]# chown oracle.oinstall /rman_backup/

[root@ std  ~]# mkdir /rman_backup

[root@ std  ~]# chown oracle.oinstall /rman_backup/

 

 

 

oracle 使用者身份登入,進行備份操作:   (只在 主庫 上做)

[root@ pri  ~]# su - oracle

[oracle@ pri  ~]$ rman target /

 

RMAN> backup database format '/rman_backup/Full_ %T _ %U.bak';   備份資料庫

 

Starting backup at 14-JAN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=41 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/pri/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/pri/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/pri/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/pri/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/pri/users01.dbf

channel ORA_DISK_1: starting piece 1 at 14-JAN-14

channel ORA_DISK_1: finished piece 1 at 14-JAN-14

piece handle=/rman_backup/Full_20140114_01ou2791_1_1.bak tag=TAG20140114T143721 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 14-JAN-14

channel ORA_DISK_1: finished piece 1 at 14-JAN-14

piece handle=/rman_backup/Full_20140114_02ou27ae_1_1.bak tag=TAG20140114T143721 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-JAN-14

 

RMAN> list archivelog all;   檢視當前的歸檔日誌有哪些

 

List of Archived Log Copies for database with db_unique_name PRI

=====================================================================

 

Key     Thrd Seq     S Low Time

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

1       1    6       A 13-JAN-14

        Name: /u01/app/arch/pri/1_6_836760649.dbf

 

2       1    7       A 14-JAN-14

        Name: /u01/app/arch/pri/1_7_836760649.dbf

 

 

RMAN> backup archivelog all format'/rman_backup/ARC_ %T _ %U.bak';   備份歸檔日誌

 

Starting backup at 14-JAN-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=1 STAMP=836835599

input archived log thread=1 sequence=7 RECID=2 STAMP=836835600

input archived log thread=1 sequence=8 RECID=3 STAMP=836836802

channel ORA_DISK_1: starting piece 1 at 14-JAN-14

channel ORA_DISK_1: finished piece 1 at 14-JAN-14

piece handle=/rman_backup/ARC_20140114_03ou27e3_1_1.bak tag=TAG20140114T144002 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-JAN-14

 

RMAN> backup current controlfile for standby format'/rman_backup/std_con.ctl';

備份standby 控制檔案並命名為 std_con.ctl

 

Starting backup at 14-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 14-JAN-14

channel ORA_DISK_1: finished piece 1 at 14-JAN-14

piece handle=/rman_backup/std_con.ctl tag=TAG20140114T144036 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-JAN-14

 

RMAN> list backup;   檢視都有哪些備份

 

List of Backup Sets

===================

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Full    1.04G      DISK        00:00:35     14-JAN-14      

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20140114T143721

         Piece Name: /rman_backup/Full_20140114_01ou2791_1_1.bak

   List of Datafiles in backup set 1

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 1064545    14-JAN-14 /u01/app/oracle/oradata/pri/system01.dbf

  2       Full 1064545    14-JAN-14 /u01/app/oracle/oradata/pri/sysaux01.dbf

  3       Full 1064545    14-JAN-14 /u01/app/oracle/oradata/pri/undotbs01.dbf

  4       Full 1064545    14-JAN-14 /u01/app/oracle/oradata/pri/users01.dbf

  5       Full 1064545    14-JAN-14 /u01/app/oracle/oradata/pri/example01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

2       Full    9.36M      DISK        00:00:01     14-JAN-14      

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20140114T143721

         Piece Name: /rman_backup/Full_20140114_02ou27ae_1_1.bak

   SPFILE Included: Modification time: 14-JAN-14

  SPFILE db_unique_name: PRI

   Control File Included: Ckp SCN: 1064580      Ckp time: 14-JAN-14

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

3       16.06M     DISK        00:00:00     14-JAN-14      

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20140114T144002

        Piece Name: /rman_backup/ARC_20140114_03ou27e3_1_1.bak

 

   List of Archived Logs in backup set 3

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    6       1051084    13-JAN-14 1062515    14-JAN-14

  1    7       1062515    14-JAN-14 1062557    14-JAN-14

  1    8       1062557    14-JAN-14 1064725    14-JAN-14

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    9.33M      DISK        00:00:02     14-JAN-14      

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20140114T144036

        Piece Name: /rman_backup/std_con.ctl

  Standby Control File Included:   Ckp SCN: 1064792      Ckp time: 14-JAN-14

 

 

RMAN> quit

 

 

 

 

6 :修改監聽檔案,新增靜態監聽   主庫、 備庫都要 做)

主庫:

[oracle@ pri  ~]$ cd $ORACLE_HOME/network/admin

[oracle@ pri  admin]$ vim listener.ora 新增如下內容:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = pri )

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = pri )

    )

  )

 

庫修改後最終效果如下圖:

 

 

備庫:

[oracle@ pri  ~]$ cd $ORACLE_HOME/network/admin

[oracle@ pri  admin]$ vim listener.ora 新增如下內容:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = std )

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = std )

    )

  )

 

 

備庫修改後最終效果如下圖:

 

 

使新增加的監聽生效: (主庫和備庫端都要做)

[oracle@ pri  admin]$  lsnrctl stop

[oracle@ pri  admin]$  lsnrctl start  

 

確認新增加的靜態監聽有效:

主庫:

[oracle@pri ~]$ lsnrctl status

..........................................(N 行省略 )

Service "pri"  has 2 instance(s).

  Instance "pri" , status UNKNOWN , has 1 handler(s) for this service...

  Instance "pri", status READY, has 1 handler(s) for this service...

Service "priXDB" has 1 instance(s).

  Instance "pri", status READY, has 1 handler(s) for this service...

The command completed successfully

 

備庫:

[ oracle @ std  ~]$ lsnrctl status

..........................................(N 行省略 )

Service " std "  has 1 instance(s).

  Instance " std " , status UNKNOWN , has 1 handler(s) for this service...

The command completed successfully

 

如上,靜態監聽新增成功

 

 

7 :編輯網路服務名配置檔案 tnsnames.ora   (主庫和備庫端都要做)

[oracle@ pri  admin]$ cd $ORACLE_HOME/network/admin

[oracle@ pri  admin]$ pwd

/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@ pri  admin]$ ls

listener.ora  samples  shrept.lst  tnsnames.ora

[oracle@ pri  admin]$ vim tnsnames.ora  

編輯結果如下圖:

 

 

 

保證 主庫和 備庫的tnsnames.ora 檔案中的內容完全相同, 可以把修改後的檔案 直接傳 備庫。

[oracle@ pri  admin]$ scp tnsnames.ora 192.168.1.102 :$ORACLE_HOME/network/admin

tnsnames.ora                                  100%  925     0.9KB/s   00:00   

 

配置完後,確保在任意一 上都能tnsping 對方

[oracle@pri admin]$ tnsping std

[oracle@std admin]$ tnsping pri

 

 

8 :在備庫上恢復主庫之前的準備工作   (主庫和備庫端分別有各自的操作,仔細看)

首先把 主庫的 pfile 引數檔案拷貝到 /rman_backup/ 目錄中去,並重新命名:

[oracle@ pri  dbs]$ cd $ORACLE_HOME/dbs

[oracle@ pri  dbs]$ cp init pri .ora  /rman_backup/ init std .ora   

[oracle@ pri  dbs]$ cd /rman_backup/

[oracle@pri rman_backup]$ ll -h

total 1.1G

-rw-r----- 1 oracle oinstall  17M Jan 14 14:40 ARC_20140114_03ou27e3_1_1.bak

-rw-r----- 1 oracle oinstall 1.1G Jan 14 14:37 Full_20140114_01ou2791_1_1.bak

-rw-r----- 1 oracle oinstall 9.4M Jan 14 14:38 Full_20140114_02ou27ae_1_1.bak

-rw-r--r-- 1 oracle oinstall 1.5K Jan 14 15:22 initstd.ora

-rw-r----- 1 oracle oinstall 9.4M Jan 14 14:40 std_con.ctl

 

然後把 主庫上的 這些備份的檔案拷貝到備庫上的對應目錄中去:

[oracle@pri rman_backup]$ scp * 192.168.1.102:/rman_backup/

oracle@192.168.1.102's password:

ARC_20140114_03ou27e3_1_1.bak                 100%   16MB  16.1MB/s   00:00    

Full_20140114_01ou2791_1_1.bak                    100% 1070MB  41.1MB/s   00:26    

Full_20140114_02ou27ae_1_1.bak                    100% 9600KB   9.4MB/s   00:00    

initstd.ora                                         100% 1497     1.5KB/s      00:00    

std_con.ctl                                        100% 9568KB   9.3MB/s   00:00      

 

備庫端 pfile 引數檔案拷貝到 $ORACLE_HOME/dbs 目錄下:

[oracle@std admin]$ cd $ORACLE_HOME/dbs

[oracle@std dbs]$ ls

init.ora  orapwstd

[oracle@std dbs]$ cp /rman_backup/initstd.ora .

[oracle@std dbs]$ ls

init.ora  initstd.ora  orapwstd

 

9 :備庫修改 pfile 引數檔案   (只在備庫端做)

[oracle@std dbs]$ pwd

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

[oracle@std dbs]$ vim initstd.ora  

 

std.__db_cache_size=125829120

std.__java_pool_size=4194304

std.__large_pool_size=4194304

std.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

std.__pga_aggregate_target=146800640

std.__sga_target=272629760

std.__shared_io_pool_size=0

std.__shared_pool_size=125829120

std.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/ std / std_con .ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='pri'   DG 主庫和備庫的 db_name 必須一致, db_unique_name 不一致

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4227858432

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

*.dispatchers='(PROTOCOL=TCP) (SERVICE= std XDB )'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

下面幾行是需要手動新增的:

*.db_unique_name=' std '

*.log_archive_config='dg_config=( pri , std )'

*.log_archive_dest_1='location= /u01/app /arch/ std  valid_for=(all_logfiles,all_roles) db_unique_name= std '

*.log_archive_dest_2='service= pri  valid_for=(online_logfiles,primary_role) db_unique_name= pri '

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=4

 

*.fal_server=' pri '

*.fal_client=' std '

*.db_file_name_convert= ' /u01/app/oracle/oradata/pri ' , ' /u01/app/oracle/oradata/std '

*.log_file_name_convert= ' /u01/app/oracle/oradata/pri ' , ' /u01/app/oracle/oradata/std '

*.standby_file_management='auto'

 

修改完畢,儲存退出

 

注意:整個搭建過程最需要留意的就是主庫和備庫的PFILE 配置,建議修改完後仔細對照主備庫 PFILE 的區別

 

 

10 :在備庫端手工建立所需的目錄 (備庫端做,不提前建立的話恢復時會報錯!)

/u01/app/oracle/admin/ std /adump

/u01/app/oracle/diag/rdbms/ std / std/ trace

/u01/app /arch/ std

/u01/app/oracle/oradata/std

/u01/app/oracle/oradata/standbylog

建立 /u01/app/oracle/fast_recovery_area

 

建立 /u01/app/oracle/admin/ std /adump

[oracle@std dbs]$ cd /u01/app/oracle/

[oracle@std oracle]$ ls

cfgtoollogs  checkpoints  diag  product (由於沒建庫,此時沒有admin 目錄)

[oracle@std oracle]$ mkdir -pv admin/std/adump

mkdir: created directory `admin'

mkdir: created directory `admin/std'

mkdir: created directory `admin/std/adump'

 

建立 /u01/app/oracle/diag/rdbms/ std / std/ trace

[oracle@std oracle]$ cd /u01/app/oracle/diag/rdbms/

[oracle@std rdbms]$ ls     (由於沒建庫,該目錄下為空)

[oracle@std rdbms]$ mkdir -pv std/std/trace

mkdir: created directory `std'

mkdir: created directory `std/std'

mkdir: created directory `std/std/trace'

 

建立 /u01/app /arch/ std

[oracle@std rdbms]$ cd /u01/app/

[oracle@std app]$ ls

oracle  oraInventory

[oracle@std app]$ mkdir -pv arch/std

mkdir: created directory `arch'

mkdir: created directory `arch/std'

 

建立 /u01/app/oracle/oradata/std

[oracle@std app]$ cd /u01/app/oracle/

[oracle@std oracle]$ ls

admin  cfgtoollogs  checkpoints  diag  product

[oracle@std oracle]$ mkdir -pv oradata/std

mkdir: created directory `oradata'

mkdir: created directory `oradata/std'

 

建立 /u01/app/oracle/oradata/standbylog

[oracle@std oracle]$ cd /u01/app/oracle/oradata/

[oracle@std oradata]$ ls

std

[oracle@std oradata]$ mkdir -pv standbylog

mkdir: created directory `standbylog'

 

建立 /u01/app/oracle/fast_recovery_area

[oracle@std dbs]$ cd /u01/app/oracle/

[oracle@std oracle]$ ls

admin  cfgtoollogs  checkpoints  diag  oradata  product

[oracle@std oracle]$ mkdir -pv fast_recovery_area

mkdir: created directory `fast_recovery_area'

 

11 :用修改後的 pfile 建立一個 spfile ,用於啟動資料庫   (備庫端做)

[oracle@std ~]$ sqlplus / as sysdba

 

Connected to an idle instance.

 

SYS@std> create spfile from pfile;

 

File created.

 

將資料庫啟動到nomount 狀態:

SYS@pri>   startup nomount;

ORACLE instance started.

 

Total System Global Area  839282688 bytes

Fixed Size                  2233000 bytes

Variable Size             482348376 bytes

Database Buffers          352321536 bytes

Redo Buffers                2379776 bytes

SYS@pri>  

 

12 :利用 RMAN 在備庫上恢復主庫   (備庫端做)

[oracle@ std  ~]$ rman target sys/oracle@ pri  auxiliary /

 

Recovery Manager: Release 11.2.0.3 .0 - Production on Tue Jan 14 16:39:26 2014

 

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

 

connected to target database: PRI (DBID=767417863)

connected to auxiliary database: PRI (not mounted)

 

RMAN> duplicate target database for standby nofilenamecheck;

這條命令可以直接恢復資料檔案,standby 控制檔案, standby 日誌組,非常霸道

Starting Duplicate Db at 14-JAN-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

 

contents of Memory Script:

{

   restore clone standby controlfile;

}

executing Memory Script

 

Starting restore at 14-JAN-14

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /rman_backup/std_con.ctl

channel ORA_AUX_DISK_1: piece handle=/rman_backup/std_con.ctl tag=TAG20140114T144036

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/std/std_con.ctl

Finished restore at 14-JAN-14

 

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

 

sql statement: alter database mount standby database

 

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/std/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/std/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/std/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/std/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/std/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/std/example01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/std/temp01.dbf in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 14-JAN-14

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/std/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/std/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/std/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/std/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/std/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /rman_backup/Full_20140114_01ou2791_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/rman_backup/Full_20140114_01ou2791_1_1.bak tag=TAG20140114T143721

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 14-JAN-14

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=836844042 file name=/u01/app/oracle/oradata/std/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=836844042 file name=/u01/app/oracle/oradata/std/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=836844042 file name=/u01/app/oracle/oradata/std/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=836844042 file name=/u01/app/oracle/oradata/std/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=836844042 file name=/u01/app/oracle/oradata/std/example01.dbf

Finished Duplicate Db at 14-JAN-14

 

 

RMAN>

恢復資料庫結束

 

13 :嘗試開啟備庫

 

登陸並 檢視資料庫當前狀態:

 

[oracle@std ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 14 16:41:50 2014

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@std> select status from v$instance;

 

STATUS

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

MOUNTED   RMAN 恢復完直接就是 mount 狀態)

 

 

 

 

 

 

 

 

 

 

1 4 :備庫啟動日誌應用

SYS@ std >   alter database recover managed standby database disconnect from session;

Database altered.

停止日誌應用的命令是:alter database recover managed standby database cancel;

 

檢視日誌應用情況:

SYS@ std >  set pagesize 100

SYS@ std >   select sequence#,applied from v$archived_log order by 1;

 

   SEQUENCE# APPLIED

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

         8 YES

         9 YES

        10 YES

 

如上, 如果 發現有個NO 的, 是正常的,說明該日誌在主庫上還沒有歸檔,可以在主庫上執行 alter system  switch logfile ; 命令來進行日誌切換,再到備庫檢視日誌應用情況

 

 

15 :分別檢視主庫和備庫的歸檔序列號是否一致:

先在主庫手動切換一下日誌:

SYS@pri> alter system switch logfile;

 

System altered.

然後檢視主庫:

SYS@pri> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/arch/ pri

Oldest online log sequence     10

Next log sequence to archive   12

Current log sequence           12

 

備庫:

SYS@std> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/arch/ std

Oldest online log sequence     10

Next log sequence to archive   0

Current log sequence           12

 

結果完全一致, 至此,DataGuard 的搭建成功!

五:相關知識補充:

1 DataGuard 的三種資料保護模式:

1 MAXIMIZE PROTECTION (最大保護模式):

最大資料保護與無資料分歧,LGWR 將同時傳送到備用節點, 在主節點事務確認之前,備用節點也必須完全收到日誌資料 。如果網路不好,引起LGWR 不能傳送資料,將引起嚴重的效能問題,導致主節點 DOWN 機。

 

2 MAXIMIZE AVAILABILITY (最大可用模式)

無資料丟失模式,允許資料分歧,允許非同步傳送。 正常情況下執行在最大保護模式,在主節點與備用節點的網路斷開或連線不正常時,自動切換到最大效能模式,主節點的操作還是可以繼續的。在網路不好的情況下有較大的效能影響。

 

3 MAXIMIZE PERFORMANCE (最大效能模式)

這種模式應當可以說是從8i 繼承過來的備用伺服器模式, 非同步傳送,無資料同步檢查,可能丟失資料 ,但是能獲得主節點的最大效能。

 

2 DataGuard   保護模式 切換   (重點,注意升級切換和降級切換的區別)

1 檢視 當前的 保護模式:

select database_role, protection_mode, protection_level from v$database;

 

2 三種保護模式的級別排名:

最大效能 --> 最大可用 --> 最大保護 從左至右,保護模式級別提升

 

3 升級切換步驟:

1. 備庫停止 redo 應用:

alter database recover managed standby database cancel;

 

2. 主庫重啟 mount 狀態 升級切換,需要重啟主庫

 

shutdown immediate;

startup mount

 

3. 主庫升級切換保護模式 OPEN 資料庫   (升級切換是重點,仔細看)

重點:當要進行升級切換時,一定要先檢視一下主庫和備庫的 LOG_ARCHIVE_DEST_ 2   引數, 若配置不正確,那麼在OPEN 資料庫時會報錯 ORA-16072 !(尤其是要修改成最大保護模式時)下 表中描述了不同保護模式下LOG_ARCHIVE_DEST_ 2   引數應該設定的屬性:

 

最大保護

最高可用

最高效能

REDO 寫程式

LGWR  

LGWR  

LGWR ARCH

網路傳輸模式

SYNC  

SYNC  

LGWR 程式時 SYNC ASYNC  , ARCH 程式時 SYNC

磁碟寫操作

AFFIRM

AFFIRM

AFFIRM NOAFFIRM

是否需要 standby redologs  

YES  

YES  

可沒有但推薦有

 

檢視: show parameter log_archive_dest_2

NAME              TYPE        VALUE

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

log_archive_dest_2     string  service=std valid_for=(online_logfiles,primary_role)            db_unique_name=std

如上,並沒有包含升級切換所需的引數,可以用下列兩條命令修改:

 

主庫修改: SYS@ pri > alter system set log_archive_dest_2='service= std   lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name= std ';

 

備庫修改: SYS@ std > alter system set log_archive_dest_2='service= pri   lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name= pri ';

 

然後修改保護模式:

alter database set standby database to maximize availability;

(或者alter database set standby database to maximize protection;

開啟資料庫:

alter database open;

 

4. 備庫啟動 redo 應用:

alter database recover managed standby database disconnect from session;

 

 

 

4 降級切換步驟:   (相對於升級切換,降級切換的步驟就簡單的多了)

1. 備庫停止 redo 應用:

alter database recover managed standby database cancel;

 

2. 主庫調整保護模式:

alter database set standby database to maximize performance;

 

3. 備庫啟動 redo 應用:

alter database recover managed standby database disconnect from session;

 

 

3 舉例: 將主庫和備庫的保護模式設定為最大 保護 模式(預設是最大效能模式)

 

1 . 檢視當前的保護模式   (在主庫或者備庫端檢視都可以)

SYS@ pri > select database_role, protection_mode, protection_level from v$database;

 

DATABASE_ROLE        PROTECTION_MODE          PROTECTION_LEVEL

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

PHYSICAL STANDBY    MAXIMUM PERFORMANCE   MAXIMUM PERFORMANCE

 

2 . 備庫停止 redo 應用:

S YS@std> alter database recover managed standby database cancel;

Database altered.

 

2. 主庫重啟 mount 狀態 由於是 升級切換, 所以 需要重啟主庫

SYS@pri> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SYS@pri> startup mount;

ORACLE instance started.

 

Total System Global Area  417546240 bytes

Fixed Size                  2227072 bytes

Variable Size             331351168 bytes

Database Buffers           79691776 bytes

Redo Buffers                4276224 bytes

Database mounted.

 

3. 檢視主庫和備庫的parameter log_archive_dest_2 引數設定是否正確

主庫檢視:

SYS@ pri > show parameter log_archive_dest_2

NAME              TYPE        VALUE

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

log_archive_dest_2     string  service= std  valid_for=(online_logfiles,primary_role)            db_unique_name= std

備庫檢視:

SYS@ std > show parameter log_archive_dest_2

NAME              TYPE        VALUE

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

log_archive_dest_2     string  service= pri  valid_for=(online_logfiles,primary_role)            db_unique_name= pri

 

如上,並沒有包含升級切換所需的引數,可以用下列兩條命令修改:

 

主庫修改:

SYS@ pri > alter system set log_archive_dest_2='service= std   lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name= std ';

備庫修改:

SYS@ std > alter system set log_archive_dest_2='service= pri   lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name= pri ';

 

當然,也可以在一開始搭建的時候,PFILE 裡就這麼寫,具體情況看實際要求

主庫再次檢視:

SYS@ pri > show parameter log_archive_dest_2

NAME              TYPE        VALUE

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

log_archive_dest_2     string  service= std   lgwr sync affirm  valid_for=(online_logfiles,prim

                           ary_role) db_unique_name= std

 

備庫再次檢視:

SYS@ std > show parameter log_archive_dest_2

NAME              TYPE        VALUE

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

log_archive_dest_2     string  service= pri   lgwr sync affirm  valid_for=(online_logfiles,prim

                           ary_role) db_unique_name= pri

 

4 . 主庫升級切換 到最大 保護模式 OPEN 資料庫

SYS@pri> alter database set standby database to maximize protection;

Database altered.

 

SYS@pri> alter database open;

Database altered.

 

 

5 . 備庫啟動 redo 應用:

SYS@std> alter database recover managed standby database disconnect from session;

Database altered.

 

檢視主庫的相關資訊:

SYS@pri>   select name,database_role,open_mode,protection_mode from v$database;

 

NAME    DATABASE_ROLE    OPEN_MODE     PROTECTION_MODE

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

PRI      PRIMARY              READ WRITE      MAXIMUM PROTECTION

 

檢視備庫的相關資訊:

SYS@ std >   select name,database_role,open_mode,protection_mode from v$database;

 

NAME    DATABASE_ROLE    OPEN_MODE     PROTECTION_MODE

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

PRI      PHYSICAL STANDBY      MOUNTED      MAXIMUM PROTECTION

 

可以看到,主庫修改完保護模式,備庫是自動跟著修改的,至於降級操作,參考上面的步驟即可,太簡單就不做演示了。

 

 

5 Oracle Data Guard 的開啟關閉:

 

1 Data Guard 主備庫開關 順序

 

啟動:

1 MOUNT 備庫

2 OPEN 主庫

3 :備庫開啟日誌應用 alter database recover managed standby database disconnect from session;

1 :備庫關閉日誌應用 alter database recover managed standby database cancel;

2 :關閉主庫

3 :關閉備庫

 

2 在最大保護模式下,直接關閉備庫是不行的,如果 直接關閉 ,會有如下提示:

SQL> shutdown immediate

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL>

在最大保護模式下,備庫是不允許關閉的,此時首先關閉主庫,然後備庫就可以順利關閉了。

 

6 Oracle Data Guard 主備切換

 

1  Switchover

 

一般SWITCHOVER 切換都是計劃中的切換,特點是在切換後,不會丟失任何的資料,而且這個過程是可逆的,整個 DATA GUARD 環境不會被破壞,原來 DATA GUARD 環境中的所有物理和邏輯 STANDBY 都可以繼續工作。

 

在進行DATA GUARD 的物理 STANDBY 切換前需要注意:

1 )確認主庫和從庫間網路連線通暢;

2 )確認沒有活動的會話連線在資料庫中;

3 PRIMARY 資料庫處於開啟的狀態, STANDBY 資料庫處於 MOUNT 狀態;

4 )確保 STANDBY 資料庫處於 ARCHIVELOG 模式;

5 )如果設定了 REDO 應用的延遲,那麼將這個設定去掉;

6 )確保配置了主庫和從庫的初始化引數,使得切換完成後, DATA GUARD 機制可以順利的執行。

 

主庫:

1. 檢視 switchover 狀態  

SYS@pri> select name,database_role,switchover_status from v$database;

NAME  DATABASE_ROLE  SWITCHOVER_STATUS

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

PRI      PRIMARY           TO STANDBY

 

附:A switchover_status 出現 session active/not allowed

      當出現session active 的時候表示還有活動的 session, 則執行

    Alter database commit to switchover to physical standby with session shutdown;

當出現not allowed 時, 說明切換標記還沒收到,此時不能執行轉換。  

當主庫備庫狀態都正常,並且沒有進行主備切換的操作時,備庫的 switchover_status 就是 not allowed 。當主庫執行了切換成備庫的操作後,備庫的 switchover_status 才是 to primary  

 

     B ora- 01153: an incompatible media recovery is active

     執行下面程式碼

    Alter database recover managed standby database finish;

     或者Alter database recover managed standby database finish force;

    Alter database recover managed standby database disconnect from session;

 

2 切換成備庫  

SYS@pri>   a lter database commit to switchover to physical standby with session shutdown;

或者

SYS@pri> a lter database commit to switchover to physical standby ;  

Database altered.

 

3 啟動到 mount 和應用日誌狀態

SYS@pri> shutdown immediate;  

若此時提示ORA-01092: ORACLE instance terminated. Disconnection forced ,則 quit 退出,重新 sqlplus 登陸資料庫即可。

SYS@pri> startup nomount;

SYS@pri> alter database mount standby database;

SYS@pri> alter database recover managed standby database disconnect from session;  

 

4. 檢視資料庫模式

SYS@pri> select name,database_role,switchover_status from v$database;

NAME  DATABASE_ROLE        SWITCHOVER_STATUS

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

PRI      PHYSICAL STANDBY      TO PRIMARY

 

如上,此時主庫已經切換為物理備庫

 

備庫:  

1. 檢視 switchover 狀態

SYS@std> select name,database_role,switchover_status from v$database;

NAME  DATABASE_ROLE        SWITCHOVER_STATUS

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

PRI      PHYSICAL STANDBY      TO PRIMARY

 

補充:若出現:ORA-16139: media recovery required

是因為沒有執行:alter database recover managed standby database disconnect from session;

 

2. 切換成主庫

SYS@std> alter database commit to switchover to primary;

Database altered.

SYS@std> shutdown immediate;

SYS@std> startup;

SYS@std> alter system switch logfile;

 

3. 檢視資料庫模式

SYS@std> select name,database_role,switchover_status from v$database;

NAME  DATABASE_ROLE  SWITCHOVER_STATUS

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

PRI      PRIMARY           TO STANDBY

 

如上,物理備庫已成功切換為主庫

 

驗證同步:

SYS@ pri > select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

             40

 

SYS@ std > select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

             40

 

 

  2 Failover

 

FAILOVER 切換一般是 PRIMARY 資料庫發生故障後 的切換,這種情況是 STANDBY 資料庫發揮其作用 的情況。這種切換髮生後, 可能會造成資料的丟失 。而且這個過程不是可逆的, DATA GUARD 環境會被破壞

 

由於PRIMARY 資料庫已經無法啟動,所以 FAILOVER 切換所需的條件並不多,只要 檢查STANDBY 是否執行在最大保護模式下,如果是的話,需要將其置為最大效能模式,否則切換到 PRIMARY 角色也無法啟動。  

 

1.  檢視 當前保護模式,確保當前保護模式為最大效能模式

SYS@std> select name,database_role,open_mode,protection_mode from v$database;

 

NAME    DATABASE_ROLE    OPEN_MODE     PROTECTION_MODE

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

PRI      PHYSICAL STANDBY      MOUNTED      MAXIMUM PROTECTION

如上,當前是最大保護模式,修改為最大效能模式:

SYS@std> alter database set standby database to maximize performance;

 

Database altered.

 

再次檢視,OK

SYS@std> select name,database_role,open_mode,protection_mode from v$database;

 

NAME    DATABASE_ROLE    OPEN_MODE     PROTECTION_MODE

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

PRI      PHYSICAL STANDBY      MOUNTED      MAXIMUM PERFORMANCE

 

2 .  檢視是否有日誌 GAP, 沒有應用的日誌:  

SYS@std> select unique thread#,max(sequence#) over (partition by thread#) last from v$archived_log;

 

   THREAD#    LAST

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

         1         75

SYS@std> select thread#,low_sequence#,high_sequence# from v$archive_gap;

 

no rows selected

如上,no rows selected ,表示沒有 GAP  

 

如果有,則拷貝過來並且註冊

SYS@ std > alter   database   register physical logfile ' 路徑 ';  

重複檢視直到沒有 應用的日誌  

 

3 . 然後停止應用歸檔:  

SYS@std> alter database recover managed standby database cancel;

Database altered.

 

4 . 下面將 STANDBY 資料庫切換為 PRIMARY 資料庫: ­

取消日誌應用:

SYS@std> alter database recover managed standby database finish;

這個finish Failover 專用的,功能類似 cancel 區別是 finish 破壞了主從結構 用了finish 就得重建 dg 所以模擬這個實驗的時候,最好提前給虛擬機器做個快照!

SYS@std> alter database recover managed standby database finish force;

 

切換為主庫:  

SYS@std> alter database commit to switchover to primary;

Database altered.

 

OPEN 主庫:

SYS@std> alter database open;

Database altered.

或者 shutdown immediate+startup

 

5 檢查資料庫是否已經切換成功:  

 

NAME    DATABASE_ROLE    OPEN_MODE     PROTECTION_MODE

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

PRI      PRIMARY             MOUNTED      MAXIMUM PERFORMANCE

 

 

至此,FAILOVER 切換完成。這個時候應該馬上對新的 PRIMARY 資料庫進行備份。




About Me

........................................................................................................................

● 本文作者:小麥苗,部分內容整理自網路,若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號( xiaomaimiaolhr)上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162

● 本文部落格園地址: http://www.cnblogs.com/lhrbest

● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

● 本文pdf版、個人簡介及小麥苗雲盤地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群號: 230161599 、618766405

● 微 信群:可加我微 信,我拉大家進群,非誠勿擾

● 聯絡我請加QQ好友 646634621 ,註明新增緣由

● 於 2019-08-01 06:00 ~ 2019-08-31 24:00 在西安完成

● 最新修改時間:2019-08-01 06:00 ~ 2019-08-31 24:00

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

........................................................................................................................

小麥苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麥苗出版的資料庫類叢書http://blog.itpub.net/26736162/viewspace-2142121/

小麥苗OCP、OCM、高可用網路班http://blog.itpub.net/26736162/viewspace-2148098/

小麥苗騰訊課堂主頁https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號( xiaomaimiaolhr)及QQ群(DBA寶典)、新增小麥苗微 信, 學習最實用的資料庫技術。

........................................................................................................................

歡迎與我聯絡

 

 



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2655304/,如需轉載,請註明出處,否則將追究法律責任。

相關文章