程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle11gR2Dataguard搭建

oracle11gR2Dataguard搭建

編輯：Oracle教程

oracle11gR2Dataguard搭建

數據庫版本：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.

--搭建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

--創建主備庫的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

--檢測主備庫的網絡連通性：主庫： SQL> conn sys/oracle@pdb as sysdba
Connected.
SQL> conn sys/oracle@sdb as sysdba
Connected. 備庫： SQL> conn sys/oracle@sdb as sysdba
Connected.
SQL> conn sys/oracle@pdb as sysdba
Connected.

--使用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/* [email protected]:/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已經搭建成功。