標籤：des io ar os 使用 sp for 檔案 資料
資料庫版本：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檔案：主庫：[[email protected] ~]# cat /etc/hosts127.0.0.1 dg1 localhost: :1 localhost6.localdomain6 localhost6192.168.133.133 dg1192.168.133.134 dg2 備庫：[[email protected] ~]# cat /etc/hosts127.0.0.1 dg2 localhost: :1 localhost6.localdomain6 localhost6192.168.133.133 dg1192.168.133.134 dg2 --看看防火牆是否關閉：[[email protected] ~]# /etc/init.d/iptables status
Firewall is stopped. --搭建DG前的準備工作：開啟資料庫logging和archivelog modeSQL>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;[[email protected] 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拷貝到備庫所在伺服器相同目錄下，並修改如下參數：[[email protected] 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/[email protected] as sysdba
Connected.
SQL> conn sys/[email protected] as sysdba
Connected.備庫：SQL> conn sys/[email protected] as sysdba
Connected.
SQL> conn sys/[email protected] 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建立備用資料庫：[[email protected] ~]$rman target sys/[email protected] 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.log7 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 CLOSINGARCH CLOSINGARCH CONNECTEDARCH CLOSINGRFS IDLERFS IDLERFS IDLEMRP0 WAIT_FOR_LOGRFS 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.dbf10 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 mountSQL>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已經搭建成功。
Oracle11gR2 Dataguard搭建
