Oracle 11g搭建DataGuard詳細步驟(物理standby所有操作總結)
序言:
DataGuard是通過建立一個PRIMARY和STANDBY組來確立其參照關係;STANDBY一旦建立,DataGuard就會通過將主要資料庫(PRIMARY)的REDO傳遞給STANDBY資料庫,然後在STANDBY中應用REDO實現資料庫的同步。
有兩種類型的STANDBY:物理STANDBY和邏輯STANDBY
物理STANDBY提供與主要資料庫完全一樣的拷貝(塊到塊),資料庫SCHEMA,包括索引都是一樣的。它是直接應用REDO實現同步的。
邏輯STANDBY則不是這樣,在邏輯STANDBY中,邏輯資訊是相同的,但物理組織和資料結構可以不同,它和主庫保持同步的方法是將接收的REDO轉換成SQL語句,然後在STANDBY上執行SQL語句。邏輯STANDBY除災難恢複外還有其它用途,比如用於使用者進行查詢和報表。
1、安裝環境
在primary搭建資料庫軟體,建立lsnrctl監聽,採用dbca搭建執行個體,在standby上搭建資料庫軟體,建立監聽,但是不需要採用dbca建立執行個體。
如何在Linux上搭建Oracle資料庫,請參考前面的文章:
在CentOS 6.4下安裝Oracle 11gR2(x64)
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 處理方法
項目列表 |
primary庫 |
standby庫 |
作業系統: |
都是centos6.4 |
都是centos6.4 |
oracle軟體版本: |
oracle 11.2.0.1.0 |
oracle 11.2.0.1.0 |
IP地址: |
192.168.121.217 |
192.168.121.218 |
db_unique_name: |
pdunq |
pdunq_dg |
2、準備工作 在primary上操作
2.1、開啟Forced Logging 模式
先確認primary庫處于歸檔模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 362
Next log sequence to archive 364
Current log sequence 364
SQL>
強制歸檔
SQL> alter database force logging;
Database altered.
SQL>
確認primary庫是歸檔模式
添加standby檔案
alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20M;
alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20M;
alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
查看所有redo檔案清單,select * from v$logfile order by 1;
SQL> select * from v$logfile order by 1;
1 ONLINE /home/oradata/powerdes/redo03.log NO
2 ONLINE /home/oradata/powerdes/redo02.log NO
3 ONLINE /home/oradata/powerdes/redo01.log NO
4 STANDBY /home/oradata/powerdes/redo_dg_021.log NO
5 STANDBY /home/oradata/powerdes/redo_dg_022.log NO
6 STANDBY /home/oradata/powerdes/redo_dg_023.log NO
6 rows selected.
SQL>
2.3 準備參數檔案
2.3.1 產生pfile
create pfile from spfile;
shutdown immediate
2.3.2 修改pfile
cp $ORACLE_HOME/dbs/initpowerdes.ora $ORACLE_HOME/dbs/initpowerdes.ora.bak
vim $ORACLE_HOME/dbs/initpowerdes.ora
*.db_unique_name=pdunq
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdesXDB)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/pwerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
2.3.3 產生spfile
create spfile from pfile;
startup #這裡可以啟動也可以不啟動,這裡不啟動,後面就要記得startup;讓新的參數檔案生效
2.4 修改監聽檔案
[oracle@powerlong4 admin]$ vim listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener=10
2.5,修改tns設定檔
[oracle@powerlong4 admin]$ vim tnsnames.ora
PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)
SC_SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
)
(CONNECT_DATA =
(SID = powerdes)
(SERVER = DEDICATED)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2.6 監聽服務重啟
lsnrctl stop
lsnrctl start
2.7 primary上配置最大可用模式:
SQL>startup
SQL>alter database set standby database to maximize availability;
2.8 備份資料庫
backup database plus archivelog;
backup current controlfile for standby;
exit;
備份結束後會在閃回區產生備份檔案
3,資料庫配置 standby上
3.1 建立相應的檔案目錄
包括dump檔案目錄,資料檔案目錄,通過show parameter dest;查看,保持和primary一樣的路徑地址
3.2 從primary上copy資料檔案到standby上
在主庫上執行:
ps:在primary上執行
copy閃回區內容
copy閃迴文件
cd /oracle/app/oracle/flash_recovery_area/
scp -r ./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/
copy參數檔案
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
copy監聽檔案
cd /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r ./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
3.3 在standby庫 修改設定檔 在standby上修改
[oracle@powerlong5 admin]$ vim listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.218)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
在standby修改tns檔案
3.4,修改參數檔案
*.db_unique_name='pdunq_dg'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerdes)'
*.fal_client='pdunq'
*.fal_server='pdunq_dg'
*.standby_file_management='AUTO'
*.db_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_file_name_convert='/home/oradata/powerdes','/home/oradata/powerdes'
*.log_archive_config='DG_CONFIG=(pdunq,pdunq_dg)'
*.log_archive_dest_2='SERVICE=pdunq_dg lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'
*.log_archive_dest_state_2='ENABLE'
PS:將*.log_archive_dest_2=後面的DB_UNIQUE_NAME改成primary的DB_UNIQUE_NAME值改為pdunq,這樣在做switchover的時候,新的primary能通過這個將redo日誌傳到新的standby上面去。
log_archive_dest_N 目的是告訴資料庫,把歸檔放到那裡去可選項,首先是本地,然後考慮遠端從庫,所以,假設A是主庫,B是從庫,切換之後B是主庫,A是從庫,所以,log_archive_dest_N需要設定為對方
更多詳情見請繼續閱讀下一頁的精彩內容: