1、環境:
primary:activity01.cyou.com 已經安裝數據庫
netname:actvdbbak db_unique_name:actvdb01
standby:actvnew.cyou.com 只安裝數據庫軟件,沒有創建數據庫
netname:actvdb db_unique_name:actvdb02
2、在primary\\standby中的/etc/hosts中做好兩個主機名的解析。
3、配置listener.ora 和 tnsnames.ora
listener.ora:
LISTENER1532 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = activity01.cyou.com)(PORT = 1532))
)
)
)
SID_LIST_LISTENER1532 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = actvdb)
(ORACLE_HOME = /data/U01/app/oracle/product/11.2.0.4)
(SID_NAME = actvdb)
))
)
tnsnames.ora:
actvdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = actvnew.cyou.com)(PORT = 1532))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = actvdb)
)
)
actvdbbak =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = activity01.cyou.com)(PORT = 1532))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = actvdb)
))
)
4、primary參數設置
alter database force logging;
alter system set db_unique_name='actvdb01' scope=spfile;#這個名字primary與standby不能一樣
alter system set db_recovery_file_dest='/data/U01/app/oracle/flash_recovery_area' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(actvdb01,actvdb02)';#這個填寫的是db_unique_name
alter system set log_archive_dest_1='location=/data/U01/app/oracle/oradata/actvdb/archive valid_for=(ONLINE_LOGFILE,ALL_ROLES) db_unique_name=actvdb01' scope=spfile;
alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST valid_for=(STANDBY_LOGFILE,STANDBY_ROLE) db_unique_name=actvdb01' scope=spfile;
alter system set log_archive_dest_3='service=actvdb LGWR ASYNC db_unique_name=actvdb02 valid_for=(all_logfiles,primary_role)' scope=spfile;
#service配置的是發送日誌的standby的net名稱,後面的db_unique_name也是standby的
alter system set fal_client='actvdbbak';
alter system set fal_server='actvdb';
alter system set standby_file_management=auto;#這裡最好設置成auto
alter database add standby logfile group 7 ('/data/U01/app/oracle/oradata/actvdb/redo07.log') size 50M;
alter database add standby logfile group 8 ('/data/U01/app/oracle/oradata/actvdb/redo08.log') size 50M;
alter database add standby logfile group 9 ('/data/U01/app/oracle/oradata/actvdb/redo09.log') size 50M;
alter database add standby logfile group 10 ('/data/U01/app/oracle/oradata/actvdb/redo10.log') size 50M;
alter database add standby logfile group 11 ('/data/U01/app/oracle/oradata/actvdb/redo11.log') size 50M;
alter database add standby logfile group 12 ('/data/U01/app/oracle/oradata/actvdb/redo12.log') size 50M;
alter database add standby logfile group 13 ('/data/U01/app/oracle/oradata/actvdb/redo13.log') size 50M;
alter database add standby logfile group 14 ('/data/U01/app/oracle/oradata/actvdb/redo14.log') size 50M;alter system set local_listener='actvdbbak';
alter system set local_listener='actvdbbak';
特別注意log_archive_dest_n的配置內容的空格和逗號問題。
SQL> alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST,valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' scope=spfile;
alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST,valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE這個錯誤是valid_for前沒有空格的原因
這個錯誤是valid_for前沒有空格的原因
配置好了後,可以通過如下語句查詢是否正常
select * from v$archive_dest_status;
如出現如下圖的bad param就能確定這log_archive_dest_3配置有問題。
5、設置好參數後,需要重啟shutdown immediate --->startup
6、在primary中生成文本的參數文件
create pfile='/home/oracle/1.pfile' from spfile;
這裡需要修改1.pfile的內容:
*.db_unique_name='actvdb02'
*.fal_client='actvdb'
*.fal_server='actvdbbak'
*.log_archive_dest_1='location=/data/U01/app/oracle/oradata/actvdb/archive valid_for=(ONLINE_LOGFILE,ALL_ROLES) db_unique_name=actvdb02'
*.log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST valid_for=(STANDBY_LOGFILE,STANDBY_ROLE) db_unique_name=actvdb02'
*.log_archive_dest_3='service=actvdbbak LGWR ASYNC db_unique_name=actvdb01 valid_for=(all_logfiles,primary_role)'*.local_listener='ACTVDB'
*.local_listener='ACTVDB'
主要修改這幾個參數,將actvdb與actvdbbak進行調換
7、再將生成的pfile文件及密碼文件拷貝到standby中
[[email protected] ~]$scp 1.pfile actvnew.cyou.com:~/
[[email protected] ~]$scp /data/U01/app/oracle/product/11.2.0.4/dbs/orapwactvdb actvnew.cyou.com:/data/U01/app/oracle/product/11.2.0.4/dbs/
SQL> create spfile from pfile='/home/oracle/1.pfile';
File created.
然後通過pfile生成spfile,啟動到nomount狀態
SQL> startup nomountORACLE instance started.
ORACLE instance started.
8、然後進行第一次數據同步
[[email protected] admin]$rman target sys/123456@actvdbbak auxiliary sys/123456@actvdb
Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 5 17:33:35 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ACTVDB (DBID=2845198203)
connected to auxiliary database: ACTVDB (not mounted)RMAN> duplicate target database for standby from active database nofilenamecheck;
RMAN> duplicate target database for standby from active database nofilenamecheck;
查看這兩個庫的狀態,一個是open狀態,一個是nomount狀態,這是正常的。然後執行duplicate進行復制數據。
注意後面的nofilenamecheck,如果不加這個參數的話,會報錯:
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/05/2014 17:24:08
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/audit_tab01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/users01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/undotbs01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/sysaux01.dbf conflicts with a file used by the target databaseRMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/system01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/U01/app/oracle/oradata/actvdb/system01.dbf conflicts with a file used by the target database
9、然後將standby打開,這個時候打開是read only狀態的
[[email protected] admin]$sqlplus / as sysdba
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> !ps -ef | grep mrp
oracle 22754 1 0 17:40 ? 00:00:00 ora_mrp0_actvdb
oracle 22789 22143 0 17:40 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp
oracle 22791 22789 0 17:40 pts/0 00:00:00 grep mrp
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
312 YES
在primary中查看中最大的是:
SEQUENCE# APPLIED
---------- ---------312 NO
312 NO
通過 alter database recover managed standby database using current logfile disconnect from session;語句啟動mrp進程,開啟日誌實時應用。
可以查看下兩個庫的狀態:
primary:
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------
actvdb01 PRIMARY READ WRITE
standby:
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------actvdb02 PHYSICAL STANDBY READ ONLY WITH APPLY
actvdb02 PHYSICAL STANDBY READ ONLY WITH APPLY
standby顯示為:read only with aplly表示正處理日誌應用狀態
10、如下就可以進行測試了,在primary創建用戶、表空間、表,然後到standby中查看是否也存在。
SQL> create user test identified by test_test;
User created.
SQL> create tablespace test
2 datafile '/data/U01/app/oracle/oradata/actvdb/test01.dbf' size 10M;
Tablespace created.
SQL> alter user test quota unlimited on test;
User altered.
SQL> create table test_table tablespace test as select * from dba_objects;Table created.
Table created.
在standby中能夠看到相應的用戶、表空間、表了,這表示oracle 11G的主從已經搭建好了。
最後就是要設置下數據保護模式:
Data Guard 允許定義3鍾數據保護模式,分別是最大保護(Maximum Protection),最大可用(Maximum Availability)和 最大性能(Maximum Performance)。
1. 最大保護(Maximum Protection)
這種模式能夠確保絕無數據丟失。要實現這一步當然是有代價的,它要求所有的事務在提交前其REDO不僅被寫入到本地的Online Redologs,還要同時寫入到Standby數據庫的Standby Redologs,並確認REDO數據至少在一個Standby數據庫中可用(如果有多個的話),然後才會在Primary數據庫上提交。如果出現了什麼故障導致Standby數據庫不可用的話(比如網絡中斷),Primary數據庫會被Shutdown,以防止數據丟失。
使用這種方式要求Standby Database 必須配置Standby Redo Log,而Primary Database必須使用LGWR,SYNC,AFFIRM 方式歸檔到Standby Database.
2. 最高可用性(Maximum availability)
這種模式在不影響Primary數據庫可用前提下,提供最高級別的數據保護策略。其實現方式與最大保護模式類似,也是要求本地事務在提交前必須至少寫入一臺Standby數據庫的Standby Redologs中,不過與最大保護模式不同的是,如果出現故障導致Standby數據庫無法訪問,Primary數據庫並不會被Shutdown,而是自動轉為最高性能模式,等Standby數據庫恢復正常之後,Primary數據庫又會自動轉換成最高可用性模式。
這種方式雖然會盡量避免數據丟失,但不能絕對保證數據完全一致。這種方式要求Standby Database 必須配置Standby Redo Log,而Primary Database必須使用LGWR,SYNC,AFFIRM 方式歸檔到Standby Database.
3. 最高性能(Maximum performance)
缺省模式。 這種模式在不影響Primary數據庫性能前提下,提供最高級別的數據保護策略。事務可以隨時提交,當前Primary數據庫的REDO數據至少需要寫入一個Standby數據庫,不過這種寫入可以是不同步的。如果網絡條件理想的話,這種模式能夠提供類似最高可用性的數據保護,而僅對Primary數據庫的性能有輕微影響。這也是創建Standby數據庫時,系統的默認保護模式。
這種方式可以使用LGWR ASYNC 或者 ARCH 進程實現,Standby Database也不要求使用Standby Redo Log。
4. 修改數據保護模式步驟
1)關閉數據庫,重啟到Mount 狀態,如果是RAC,需要關閉所有實例,然後只啟動一個實例到mount狀態。
2)修改模式:
語法:ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};
如:SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
3) 打開數據庫: alter database open;
4) 確認修改數據保護模式:
SQL>select protection_mode,protection_level from v$database;
閱讀更多 IT維京 的文章