手把手教你搭建Oracle 11g DataGuard

手把手教你搭建Oracle 11g DataGuard

 

一、前期準備
    在準備搭建DataGuard的時候，看過許多篇教程，按著教程去走，出現過問題，然後就不知道該如何進行下一步。
    後來去看了官方文檔的，整理一下思路後事情就簡單多了。
    以一主主庫一備庫為例，搭建DG的思路如下：
    關閉防火牆。
    配置ssh互信（非必須，減少SCP時輸入密碼的次數）
    主庫上的操作：
    1）開啟歸檔模式，開啟forced logging；
    2）添加standby logfile，standby logfile組比logfile組多一個；
    3）修改參數檔案
    4）配置listener.ora 和tnsnames.ora
    5）備份資料庫和控制檔案
    備庫準備：
    1）將主庫上的參數檔案、密碼檔案拷貝到備庫上，修改參數檔案；
    2）將主庫上的listener.ora 和tnsnames.ora複製到備庫上，修改listener.ora檔案
    3）將備份複製到備庫上
    4）在備庫上建立對應的檔案夾
    5）啟動備庫到nomount模式
    6）在備庫上使用RMAN恢複資料庫
    7）備庫上添加standby logfile
    8）應用歸檔
    9）查看狀態
二、基本配置準備
2.1 primary伺服器安裝資料庫軟體並安裝ORCL資料庫，standby伺服器上安裝
2.2 關閉防火牆
primary伺服器關閉防火牆：

 

standby伺服器關閉防火牆

 

2.3 配置ssh互信
百度下“linux scp免密碼”，隨便找篇文章看下。
[Oracle@primary dbs]$ ssh-keygen -t rsa
[oracle@primary dbs]$  scp ~/.ssh/id_rsa.pub oracle@192.168.62.202:/home/oracle/.ssh/authorized_keys

 

三、主庫配置
1）enable forced logging:
    SQL> ALTER DATABASE FORCE LOGGING;
    查看是否已經是歸檔模式,如果是未歸檔模式，則將其修改成歸檔模式。
    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP MOUNT;
    SQL> ALTER DATABASE ARCHIVELOG;
    SQL> ALTER DATABASE OPEN;
3）配置standby logfile檔案，當主庫變成備庫時，可以接收來自備庫的日誌。
    alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo_dg_021.log') size 50M;
    alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo_dg_022.log') size 50M;
    alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo_dg_023.log') size 50M;
    alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo_dg_024.log') size 50M;
3）配置主庫的參數：這裡有兩種方式，一種是使用alter system 來修改參數，另外一種是產生靜態參數檔案之後修改參數；我採用的是後一種配置方式。
參數的配置參考Oracle Data Guard Concepts and Administration 11g Release 2 (11.2) E41134-03中3-3和3-4頁
initorcl.ora已經有的參數，可以不用配置。

 

 

 

SQL> create pfile from spfile;
[oracle@primary ~]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ vim initorcl.ora
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
SQL> shutdown immediate
SQL> create spfile from pfile;
4）主庫上配置listener.ora 和tnsnames.ora
主庫上的listener.ora ：
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.201)(PORT = 1521))
  )
ADR_BASE_LISTENER = /u01/app/oracle
主庫上的tnsnames.ora：
[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )
STY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.202)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )
修改完成之後要重啟監聽。
5）備份資料庫和控制檔案
[oracle@primary admin]$ rman target sys/asd@pri
RMAN> backup database format '/u01/app/oracle/backup/full_db_%U';
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/sty.ctl';
四、備庫上的操作
 1）將主庫上的參數檔案、密碼檔案拷貝到備庫上，修改參數檔案；
[oracle@primary admin]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ scp initorcl.ora orapworcl oracle@192.168.62.202:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
修改參數檔案：
vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
*.db_name='orcl'
*.DB_UNIQUE_NAME=sty
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=pri
標紅的是修改的部分
密碼檔案說明：主庫上有授權或者收回sysdba時，要更新備庫上的密碼檔案。
2）將主庫上的listener.ora 和tnsnames.ora複製到備庫上，修改listener.ora檔案
主庫上執行：
[oracle@primary admin]$ scp listener.ora tnsnames.ora oracle@192.168.62.202:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
備庫上執行：
[oracle@standby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@standby admin]$ vim listener.ora
將host中的201 改成202，也就是將host改成備庫機器上的IP，tnsnames.ora不用修改。
修改完成之後要重啟監聽

 

3）將備份複製到備庫上
[oracle@primary backup]$ scp /u01/app/oracle/backup/* oracle@192.168.62.202:/u01/app/oracle/backup
4）在備庫上建立對應的檔案夾
[oracle@standby oracle]$ cd $ORACLE_BASE
[oracle@standby oracle]$ ls
arch  backup  checkpoints  database  product
[oracle@standby oracle]$ mkdir -p oradata/orcl
[oracle@standby oracle]$ mkdir -p admin/orcl/adump
[oracle@standby oracle]$ mkdir -p admin/orcl/dpdump
[oracle@standby oracle]$ mkdir -p admin/orcl/pfile
5）啟動備庫到nomount模式
SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';
SQL> startup nomount;
6）在備庫上使用RMAN恢複資料庫
[oracle@standby ~]$ rman target sys/asd@pri
RMAN> connect auxiliary /
run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel c1;
release channel c2;
}
雖然最後出現了這個問題，但備庫還是可以開啟的。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/08/2016 17:38:36
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 1010403 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1009794 found to restore
7）備庫上添加standby logfile
備庫上添加standby logfile，我做測試時，v$logfile視圖裡有standby logfile group4 至group 7，而/u01/app/oracle/oradata/orcl並沒有這些檔案，所以要先把這幾組日誌刪除再重建：
SQL>alter database open;#未試過在mount狀態建立standby日誌
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;
SQL> alter database drop logfile group 7;
SQL>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo_dg_021.log') size 50M;
SQL>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo_dg_022.log') size 50M;
SQL>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo_dg_023.log') size 50M;
SQL>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo_dg_024.log') size 50M;
  8）應用歸檔
--應用歸檔
SQL>shutdown immediate;

 

SQL>startup mount;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--退出歸檔
alter database recover managed standby database cancel;

 

    9）查看狀態
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 SEQUENCE# FIRST_TIME          NEXT_TIME      APPLIED
---------- ------------------ ------------------ ---------
    15 08-JUN-16          08-JUN-16      YES
    16 08-JUN-16          08-JUN-16      YES
    17 08-JUN-16          08-JUN-16      YES
    18 08-JUN-16          08-JUN-16      YES
    19 08-JUN-16          08-JUN-16      YES
    20 08-JUN-16          08-JUN-16      YES
    21 08-JUN-16          08-JUN-16      IN-MEMORY
說明DG已經搭建成功

 

註：如果備庫處於open狀態，警示日誌中會出現：ORA-16058: 未裝載備用資料庫執行個體的警示資訊。

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法