Oracle11gR2 Dataguard搭建
資料庫版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
主庫ip：192.168.133.133 instance_name:orcl service_name:pdb 主機名：dg1
備庫ip：192.168.133.134 instance_name:orcl service_name:sdb 主機名：dg2
（注意，在虛擬機器上進行操作，網路連線方式最好選擇host only，這樣內部網路才會穩定，主庫備庫間的通訊才更加正常）
--修改主備庫hosts檔案：
主庫：
[root@dg1 ~]# cat /etc/hosts
127.0.0.1 dg1 localhost
: :1 localhost6.localdomain6 localhost6
192.168.133.133 dg1
192.168.133.134 dg2
備庫：
[root@dg2 ~]# cat /etc/hosts
127.0.0.1 dg2 localhost
: :1 localhost6.localdomain6 localhost6
192.168.133.133 dg1
192.168.133.134 dg2
--看看防火牆是否關閉：
[root@dg1 ~]# /etc/init.d/iptables status
Firewall is stopped.
Firewall is stopped.
--搭建DG前的準備工作：
開啟資料庫logging和archivelog mode
SQL>alter database force logging;
檢查：
SQL>select force_logging from v$database;(應為YES)
檢查資料庫是否處於歸檔模式:
SQL>select log_mode from v$database;
如果資料庫沒有處於歸檔模式，那麼將資料庫shutdow immediate;重啟到mount，執行
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
--配置主備庫的監聽和網路服務名：
主庫：
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora:
PDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.133)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb)
)
)
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdb)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
備庫：
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora:
PDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.133)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb)
)
)
SDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdb)
)
)
--啟動主備庫的監聽：
lsnrctl start
--建立備庫的口令檔案：
如果主庫中有就直接使用scp命令複製到備庫所在伺服器相應目錄中，如果沒有就使用orapwd命令建立：
orapwd file=xxx password=xxx entries=xx
orapwd file=xxx password=xxx entries=xx
--建立主備庫的pfile，新增相應的引數：
主庫：
SQL>create pfile from spfile;
[oracle@dg1 dbs]$ vi initorcl.ora
新增如下內容：
*.instance_name=orcl
*.db_unique_name=pdb
*.log_archive_config='dg_config=(pdb,sdb)'
*.fal_server=sdb
*.fal_client=pdb
*.log_archive_dest_2='service=sdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=sdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.STANDBY_FILE_MANAGEMENT=AUTO
如果主備庫的資料檔案及日誌檔案不在同一個目錄，則需要新增如下兩個引數檔案，路徑為先遠端後本地：
*.DB_FILE_NAME_CONVERT='/u01/oradata/sdb/','/u01/oradata/pdb/'
*.LOG_FILE_NAME_CONVERT='/u01/oradata/sdb/','/u01/oradata/pdb/'
備庫：
使用scp命令將主庫的pfile複製到備庫所在伺服器相同目錄下，並修改如下引數：
[oracle@dg2 dbs]$ vi initorcl.ora
*.instance_name=orcl
*.db_unique_name=sdb
*.log_archive_config='dg_config=(pdb,sdb)'
*.fal_server=pdb
*.fal_client=sdb
*.log_archive_dest_2='service=pdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.STANDBY_FILE_MANAGEMENT=AUTO
--建立備庫相應的目錄(以下是我的路徑)：
資料檔案和日誌檔案目錄/u01/app/oracle/oradata/orcl/
閃回區目錄：/u01/app/oracle/flash_recovery_area/orcl
警報日誌檔案目錄：/u01/app/oracle/diag/rdbms/sdb/orcl/trace
歸檔日誌檔案目錄：/ss/archivelog/orcl
--備庫：以pfile建立spfile並啟動資料庫到nomount：
SQL>startrup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup nomount
--檢測主備庫的網路連通性：
主庫：
備庫：
--使用rman備份資料庫：
備份當前控制檔案：
RMAN>backup format '/ss/backup/controlfile_%U' current controlfile for standby;
備份資料檔案和歸檔日誌檔案：
RMAN>backup format '/ss/backup/db_%U' database plus archivelog;
--把備份集複製到和主庫一樣的目錄/ss/backup/下：
scp /ss/backup/* oralce@192.168.133.134:/ss/backup/
--physical standby database的建立
--上面的步驟中我們已經將備庫啟動到nomount了，開啟rman，使用rman的rman duplicate建立備用資料庫：
[oracle@dg2 ~]$rman target sys/oracle@pdb auxiliary /
RMAN>duplicate target database for standby nofilenamecheck;
備用資料庫完成之後，資料庫會自動開啟到mount：
SQL>select status from v$instance;
(如果主備目錄不同：duplicate target database for standby;
如果主備目錄相同：duplicate target database for standby nofilenamecheck;)
--修改備庫為恢復管理模式，使備庫可以應用主庫的redo資料，實現同步：
SQL>alter database recover managed standby database disconnect from session;
或
SQL>alter database recover managed standby database using current logfile disconnect from session;
--檢查是否有錯誤資訊出現：
SQL>select error from v$archive_dest where target='STANDBY';
--檢查備庫日誌應用情況：
SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
30 01-NOV-14 01-NOV-14 YES
31 01-NOV-14 01-NOV-14 YES
32 01-NOV-14 02-NOV-14 YES
33 02-NOV-14 02-NOV-14 YES
34 02-NOV-14 02-NOV-14 YES
35 02-NOV-14 02-NOV-14 YES
36 02-NOV-14 02-NOV-14 YES
37 02-NOV-14 02-NOV-14 YES
--在主庫切換日誌，在備庫中檢視日誌應用情況：
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
30 01-NOV-14 01-NOV-14 YES
31 01-NOV-14 01-NOV-14 YES
32 01-NOV-14 02-NOV-14 YES
33 02-NOV-14 02-NOV-14 YES
34 02-NOV-14 02-NOV-14 YES
35 02-NOV-14 02-NOV-14 YES
36 02-NOV-14 02-NOV-14 YES
37 02-NOV-14 02-NOV-14 YES
38 02-NOV-14 03-NOV-14 IN-MEMORY
--為主備庫分別建立standby日誌檔案(至少比redo log多一組)：
取消備庫日誌應用：
SQL>alter database recover managed standby database cancel;
主庫：
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 50m;
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/orcl/redo03.log
2 /u01/app/oracle/oradata/orcl/redo02.log
1 /u01/app/oracle/oradata/orcl/redo01.log
4 /u01/app/oracle/oradata/orcl/stdredo01.log
5 /u01/app/oracle/oradata/orcl/stdredo02.log
6 /u01/app/oracle/oradata/orcl/stdredo03.log
7 /u01/app/oracle/oradata/orcl/stdredo04.log
7 rows selected.
備庫：
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 50m;
SQL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 50m;
SQL> select group#,status,member from v$logfile;
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------------------------------------
3 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_3_b5bt92v2_.log
2 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_2_b5bt90kf_.log
1 /u01/app/oracle/flash_recovery_area/SDB/onlinelog/o1_mf_1_b5bt8yfc_.log
4 /u01/app/oracle/oradata/orcl/stdredo01.log
5 /u01/app/oracle/oradata/orcl/stdredo02.log
6 /u01/app/oracle/oradata/orcl/stdredo03.log
7 /u01/app/oracle/oradata/orcl/stdredo04.log
7 rows selected.
--啟用備庫日誌應用：
SQL>alter database recover managed standby database disconnect from session;
--在備庫中確定相關程式是否全部啟動：
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG
RFS IDLE
9 rows selected.
--同步驗證：
--檢視備庫是否存在日誌斷點：
SQL> select * from v$archive_gap;
no rows selected
如果存在日誌斷點，就要到主庫中找到這些日誌：
SQL> select sequence#,name from v$archived_log t1,v$archive_gap t2 where t1.thread#=t2.thread#;
找到這些日誌檔案之後，將這些檔案複製到備庫，並註冊到備庫進行應用：
SQL>alter database register logfile 'xxx';
主庫：
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 10m autoextend off;
Tablespace altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/users02.dbf
/u01/app/oracle/oradata/orcl/test01.dbf
/u01/app/oracle/oradata/orcl/users03.dbf
/u01/app/oracle/oradata/orcl/users04.dbf
/u01/app/oracle/oradata/orcl/test02.dbf
10 rows selected.
備庫：
檢視備庫警報日誌檔案：
Datafile #10: '/u01/app/oracle/oradata/orcl/test02.dbf'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/users02.dbf
/u01/app/oracle/oradata/orcl/test01.dbf
/u01/app/oracle/oradata/orcl/users03.dbf
/u01/app/oracle/oradata/orcl/users04.dbf
/u01/app/oracle/oradata/orcl/test02.dbf
10 rows selected.
--修改主庫的保護模式和保護級別：
--檢視主庫保護模式和保護級別：
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
如果不是處於maximum availability高可用模式，就關閉主庫重啟到mount，修改為高可用模式：
SQL>shutdown immediate;
SQL>alter database mount
SQL>alter database set standby database to maximize availability;
(alter database set standby database to maximize {protection | availability | performance } )
SQL>select protection_mode,protection_level from v$database;
SQL>alter database open;
只需修改主庫，會自動應用同步到備庫。
至此，Datagurad已經搭建成功。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29800581/viewspace-1323560/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11gR2下搭建DataGuard主備同步詳解Oracle
- Oracle11gR2 Active DataGuardOracle
- DataGuard搭建
- ORACLE11GR2 RAC DATABASE+STANDLONE DATAGUARD配置摘要OracleDatabase
- DataGuard搭建物理StandBy
- DataGuard搭建邏輯StandBy
- 探索Oracle11gR2 之 DataGuard_03 三種保護模式Oracle模式
- 搭建 Oracle10g DataGuardOracle
- 搭建DataGuard碰到的小陷阱
- 在異構平臺配置Oracle11gR2 Streams同時再配置相同平臺的Oracle11gR2 DataguardOracle
- Oracle 11.2 DataGuard RAC To RAC搭建Oracle
- 實戰不停機搭建ORACLE DataGuardOracle
- 搭建dataguard時，錯誤處理
- DataGuard 搭建 uweb 分行資料庫Web資料庫
- RAC環境下dataguard的搭建
- 最大效能模式DATAGUARD 搭建 及SWITCH模式
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- Oracle11g 搭建DataGuard(筆記)Oracle筆記
- Oracle在一臺機器上搭建dataguardOracle
- oracle11g單節點DataGuard搭建Oracle
- 搭建rac+DataGuard的測試環境
- 搭建dataguard碰到的幾個小問題
- Oracle11g 搭建單例項DataGuardOracle單例
- 【DATAGUARD】DG系列之RACtoONE物理備庫的搭建
- Oracle11gR2搭建ADG一步一步操作Oracle
- 使用RMAN備份集搭建Oracle Dataguard Step by Step（一）Oracle
- 使用RMAN備份集搭建Oracle Dataguard Step by Step（二）Oracle
- 【問題解決】單機搭建dataguard的問題
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- 【DATAGUARD】DG系列之11g物理備庫的搭建
- dataguard 搭建 oracle_sid不同 2節點 primary+standbyOracle
- dataguard 搭建 oracle_sid相同 2節點 primary+standbyOracle
- ORACLE 12C DATAGUARD環境搭建和主從切換Oracle
- Oracle11g R2之Dataguard搭建物理standbyOracle
- 【DATAGUARD】DG系列之RACtoONE快照備用資料庫的搭建資料庫
- 虛擬機器下搭建Linux Oracle10g DataGuard虛擬機LinuxOracle
- 【DATAGUARD】DG系列之11g邏輯備庫的搭建
- oracle實驗記錄 (oracle 10G dataguard(1)手工搭建)Oracle