One instance primary to RAC standbyDataGuard Configuration:
Primary
standby
Clusterware
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
11g R2 Grid Infrastructure (11.2.0.4)
Cluster Nodes
Cltrac1
Srvrac1,srvrac2
DB_UNIQUE_NAME
Test1
Test2
DB_NAME
TEST1
Test1
DB_instance
Test1
Test11,test22
DB_listener
listener
Listener2
DB storage
ASM
Linux file sys
ASM diskgroup for DB files
DATA
ORACLE_HOME
/bee/app/oracle/product/11.2.0/db_1
/bee/app/oracle/product/11.2.0/db_1
OS
CentOS release 6.4 (Final)
CentOS release 6.4 (Final)
1. 主庫必須為歸檔模式，修改主庫為歸檔模式的方法：
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
2. 啟用主庫的強制日志功能
SQL> alter database force logging;
3. 當主庫添加或刪除數據文件時，這些文件也會在備庫添加或刪除。啟用此功能的方法如下：
SQL> alter system set standby_file_management = 'AUTO';
4. 給主庫添加standby日志：
alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby01.log' size50M;
alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby02.log' size50M;
alter database add standby logfile'/bee/app/oracle/oradata/test1/standby03.log' size 50M;
5. 建密碼文件，並且設置參數 REMOTE_LOGIN_PASSWORDFILE 為 EXCLUSIVE 或 SHARED。一般數據庫默認就有密碼文件，並且此參數默認為 EXECUSIVE。先檢查下這兩項，如果不是默認，設置方法如下：
SQL>alter system set remote_login_passwordfile=exclusive scope=spfile;
OS> orapwd password=<sys 用戶密碼>
Scp mvorapwSID
6. 檢查數據庫的 db_unique_name 參數是否設置。如果沒有，使用 alter system 進行設置：
SQL> show parameter db_unique_name;
SQL> alter system set db_unique_name=some_namescope=spfile;
7. 配置歸檔位置：
alter system set log_archive_dest_1 ='location=/bee/app/oracle/archivelog valid_for=(all_logfiles, all_roles)db_unique_name=test1';
alter system set log_archive_dest_2 = 'service=test21 asyncvalid_for=(online_logfile,primary_role) db_unique_name=test2';
8. SQL> alter system setfal_server = 'test2';
SQL> alter system set log_archive_config ='dg_config=(test1,test2)';
9. 設置文件轉換方式:後面為本地存放位置
altersystem set DB_FILE_NAME_CONVERT='+DATA/test2/datafile/','/bee/app/oracle/oradata/test1/'scope=spfile;
alter system setLOG_FILE_NAME_CONVERT='+DATA/test2/onlinelog/','/bee/app/oracle/oradata/test1/'scope=spfile;
10. createpfile='/tmp/test2_pfile.ora' from spfile;
11.
1. 我們要用 RMAN 的 duplicate from active database 命令創建備庫，需要配置靜態監聽和 TNS 名,黃色部分是我手動添加的靜態監聽，其余部分為數據庫原來的動態監聽，兩節點都要添加：
[grid@srvrac2admin]$ cat listener.ora
LISTENER_TT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TT)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_TT=ON # line added by Agent
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1522))
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME =/bee/app/oracle/product/11.2.0/db_1)
(SID_NAME = test22)
)
)
啟動時要帶上監聽名：
[grid@srvrac2 admin]$ lsnrctl start LISTENER2
2. 添加tnsnames.ora
從庫節點1：
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
從庫節點2：
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
主庫：
test1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
test21 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = test1)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES = 200)
(DELAY = 5)
)
)
)
紅色的網絡服務名對應配置log_archive_dest_2中的service
測試方法：
sqlplus sys/oracle@test21 as sysdba
3.
1. 建好了主庫的 pfile 後，將其復制到備庫服務器的相同位置，使用備庫的 SID 修改其名字。需要對 pfile 做如下修改：
根據你備庫的配置和文件位置，你可能需要修改AUDIT_FILE_DEST，CONTROL_FILES 和 DISPATCHERS 參數（也許還有其他需要修改的參數）。
LOG_ARCHIVE_DEST_1參數中的 db_unique_name 修改為備庫的相應唯一名（這裡是 JED2）。
LOG_ARCHIVE_DEST_2參數，修改為主庫對應的服務名和數據庫唯一名（這裡是 JED）。
FAL_SERVER參數修改指向主庫的服務名。
增加如下參數：
db_unique_name=JED2
altersystem set standby_file_management = 'AUTO';
db_file_name_convert和 log_file_name_convert。如果主備庫的數據文件、日志文件位置不同，需要設置這兩個參數。
然後在備庫服務器上創建所需目錄結構和修改相關文件
2. 如：
[oracle@srvrac1dbs]$ cat test2_pfile.ora
test1.__db_cache_size=883027968
test1.__java_pool_size=23554432
test1.__large_pool_size=30331648
test1.__pga_aggregate_target=673741824
test1.__sga_target=922122547
test1.__shared_io_pool_size=0
test1.__shared_pool_size=203979776
test1.__streams_pool_size=11777216
*.audit_file_dest='/bee/app/oracle/admin/test2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/test2/controlfile/control01.ctl','+DATA/test2/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'
*.log_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'
*.db_name='test1'
*.db_unique_name='test2'
*.diagnostic_dest='/bee/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=test2XDB)'
*.fal_server='test1'
*.log_archive_config='dg_config=(test1,test2)'
*.log_archive_dest_1='location=+DATA/test2/archivelogvalid_for=(all_logfiles, all_roles) db_unique_name=test2'
*.log_archive_dest_2='service=test1async valid_for=(online_logfile,primary_role) db_unique_name=test1'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1072693248
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3218079744
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
3. 啟動備庫export ORACLE_SID=test11
startup nomount pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/test2_pfile.ora'
4. 創建備庫,在從庫 RMAN 恢復：
[oracle@srvrac1~]$ export ORACLE_SID=test11
[oracle@srvrac1 ~]$ rman target sys/oracle@test1 auxiliary sys/oracle@test21
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASENOFILENAMECHECK;
5. 啟動重做應用：
sql> alter database recover managed standbydatabase disconnect from session
或實時同步ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT FROM SESSION;
6. 測試：
在主庫更新：
SQL>insert into test.test_table values (17,'jhpcc');
1 rowcreated.
SQL>commit;
Commitcomplete.
SQL>alter system archive log current;
System altered.
在備庫查看是否同步過來，分析問題看兩邊的alert.log
sql> alter database recover managed standbydatabase cancel;
sql>alter database open read only;
sql>SQL> select * from test.test_table;
ID NAME
---------- ----------
17 jhpcc
7. 11g已經支持活動備庫，可以讓數據庫在只讀狀態下打開，同時啟動日志應用:
alter database recover managed standby databasedisconnect
8. createspfile='+DATA/cltdbhz1/spfilecltdbhz1.ora' from pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/cltdbhz1_pfile.ora';
1. [oracle@srvrac2 dbs]$ catinittest22.ora
2. spfile='+DATA/test2/spfiletest2.ora
3. 從庫的第二個節點上， $ export ORACLE_SID=test22
$ sqlplus / as sysdba sql> startup mount;
4. srvctl add database -d test2 -ntest1 -o /bee/app/oracle/product/11.2.0/db_1 -p +DATA/test2/spfiletest2.ora -rphysical_standby -a DATA（oracle用戶執行）
5. srvctl add instance -d test2 -itest11 -n srvrac1
6. srvctl add instance -d test2 -itest22 -n srvrac2
7. srvctl start database -d test2
8. srvctl modify database -d test2-s mount
9. srvctl status database –d test2
在第三步startup mount時
由於參數文件是從單實例的主庫生成的，作為RAC第二個節點啟動時會先後報了幾個錯：
問題1：
ORA-00304: requested INSTANCE_NUMBER isbusy
解決：
alter system set instance_number=1scope=spfile sid='test11';
alter system set instance_number=2scope=spfile sid='test22';
然後重啟
問題2：
ORA-01102: cannot mount database inEXCLUSIVE mode
解決:
SQL> show parameter cluster_databas
NAME TYPE VALUE
----------------------------------------------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> alter system setcluster_database=true scope=spfile;
alter system setcluster_database_instances=2 scope=spfile;
System altered.
select instance_name,status from gv$instance;
問題3：
ORA-01620: no public threads are availablefor mounting
Cause: The value of the initializationparameter THREAD is zero, its default value. There are no threads which havebeen publicly enabled, and not mounted.
Action: Shut down the instance, change thevalue of the initialization parameter to a thread which is privately enabledand not mounted. If the database is open in another instance, then a thread maybe publicly enabled.
SQL> show parameter thread
NAME TYPE VALUE
----------------------------------------------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 0
alter system set thread=1 scope=spfilesid='test11';
alter system set thread=2 scope=spfilesid='test22';
檢查狀態：
