Oracle10g RAC環境下DataGuard備庫搭建例項-eygle
為一個RAC搭建standby和單節點搭建方法基本一致,我們可以把RAC看成是一個單節點的資料庫,只需要保證所有節點的日誌能傳送到備庫即可。
一、在備庫伺服器安裝ORACLE軟體
只安裝軟體,不要建立資料庫。ORACLE軟體版本和主庫保持一致。
二、修改主庫引數
節點1執行:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /dev/raw/raw14
節點2執行:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /dev/raw/raw14
可見,在本例中,RAC各節點共用一個spfile,所以,我們修改引數時,可以只需在一個節點下修改就可以了。
--2.1 強制資料庫LOGGING
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
--2.2 修改DATAGUARD相關引數
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME=primary scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)' scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/soft/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile;
SQL> ALTER SYSTEM SET COMPATIBLE = '10.2.0.3' scope=spfile;
--以下幾個引數是為了SWITCH OVER用的,是可選引數。
--但是為了以後可能發生的SWITCH OVER更方便,應該養成設定這些引數的習慣
--在本文測試環境下,由於主庫和備庫路徑不一致,所以要設定路徑轉換引數。(可選,switch over用)
SQL> ALTER SYSTEM SET FAL_CLIENT = PRIMARY SCOPE=SPFILE;
SQL> ALTER SYSTEM SET FAL_SERVER = STANDBY SCOPE=SPFILE;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO SCOPE=SPFILE;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/soft/oradata/rac/','/dev/raw/' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/soft/oradata/rac/','/dev/raw/' SCOPE=SPFILE;
2.3、在主庫新增指向備庫的tnsname
在主庫的tnsnames.ora新增如下內容:
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =200.200.200.123)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
三、修改主庫為歸檔模式
3.1、--關閉所有例項
--注意:修改以上引數後,必須把所有例項都關閉。否則在啟動例項的時候可能會導致錯誤:
--ORA-00600: internal error code, arguments: [kccsbck_first], [2], [2241198041],
[], [], [], [], []
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3.2、修改為歸檔模式
--關閉所有節點
SQL> shutdown immediate
--在其中一個節點啟用歸檔
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
10GR2以前,在RAC環境下修改歸檔必須先把設定引數cluster_database=false,把資料庫設定為歸檔後再把該引數設定為true,但這個步驟在10GR2可以省略。
四、備份資料庫
4.1、備份資料庫
備份操作在節點1(rac1)上執行。
由於歸檔在不同的節點下,故要連線所有節點進行備份:
[oracle@rac1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Apr 30 14:48:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RAC (DBID=2232067446)
RMAN> run
2> {
3> allocate channel c1 device type disk format '/soft/backup/%U' connect sys/test@rac1;
4> allocate channel c2 device type disk format '/soft/backup/%U' connect sys/test@rac2;
5> backup database plus archivelog delete all input;
6> }
4.2、生成備庫控制檔案
RMAN> run
2> {
3> allocate channel c1 device type disk format '/soft/backup/CON_%U';
4> backup current controlfile for standby;
5> }
為一個RAC搭建standby和單節點搭建方法基本一致,我們可以把RAC看成是一個單節點的資料庫,
只需要保證所有節點的日誌能傳送到備庫即可。
五、備庫環境準備
5.1、在備庫新增指向主庫的tnsnames
在備庫的tnsnames.ora新增如下內容:
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.200.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.200.22)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
5.2、在備庫建立相關目錄
包括adump,bdump,cdump,udump及資料檔案目錄等。
5.3、複製主庫的密碼檔案到備庫上
--複製rac1的密碼檔案到備庫的$ORACLE_HOME/dbs下,並把該密碼檔案修改為orapwd
[oracle@rac1 dbs]$ scp orapwrac1 172.25.0.35:`pwd`
5.4、配置備庫的監聽
[oracle@standby admin]$ more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /opt/oracle/product/10.2/database)
(SID_NAME = rac1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
啟動備庫監聽:lsnrctl start
5.5、設定備庫引數檔案
從主庫rac1上根據spfile建立一個pfile檔案,並傳到備庫上
SQL> create pfile from spfile;
SQL> exit
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ scp initrac1.ora 172.25.0.35:`pwd` ==複製到stancby資料庫==
在備庫上修改引數檔案:
? 刪除所有非"*"打頭的引數設定及rac相關引數
? 設定dataguard引數
修改後引數如下:
[oracle@standby dbs]$ more initrac1.ora
*.audit_file_dest='/opt/oracle/admin/rac/adump'
*.background_dump_dest='/opt/oracle/admin/rac/bdump'
*.compatible='10.2.0.3'
*.control_files='/soft/oradata/rac/control01.ctl','/soft/oradata/rac/control02.ctl','/soft/oradata/rac/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/rac/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/DEV/RAW/','/SOFT/ORADATA/RAC/'
*.db_name='rac'
*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.standby_file_management='AUTO'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION= /soft/oradata/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='/DEV/RAW/','/SOFT/ORADATA/RAC/'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=157286400
*.sga_target=157286400
*.undo_management='AUTO'
undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/rac/udump'
以上 有些引數非必須設定的,但是為了switchover更方便,建議都修改上。
Undo表空間保留其中一個就可以。
路徑轉換相關引數要設定對,否則會報錯。
5.6、把備庫啟動到nomount狀態
[oracle@standby ~]$ sqlplus "/as sysdba"
SQL> startup nomount
5.7、從主庫複製備份到備庫上
之前備份的所有檔案都複製到備庫上,路徑要和主庫備份路徑保持一致。如果不一致,linux下可以用ln的方式解決。
注意:兩個節點都有歸檔的備份,要把這些備份都複製到備庫上。==重要==
RAC1:
[oracle@rac1 backup]$ scp * 172.25.0.35:`pwd`
RAC2:
[oracle@rac2 backup]$ scp * 172.25.0.35:`pwd`
六、恢復備庫
6.2、在主庫執行,利用auxiliary來恢復備庫
[oracle@rac1 ~]$ $ORACLE_HOME/bin/rman target / auxiliary
RMAN> run
{
allocate channel c1 device type disk format '/soft/backup/%U' connect sys/6212327@rac1;
allocate channel c2 device type disk format '/soft/backup/%U' connect sys/6212327@rac2;
allocate auxiliary channel ac1 device type disk format '/soft/backup/%U';
allocate auxiliary channel ac2 device type disk format '/soft/backup/%U';
duplicate target database for standby;
}
從日誌可以看到,oracle先根據引數檔案把控制檔案恢復到合適位置,然後再根據db_file_name_cover把資料檔案恢復到合適位置。
七、後續工作
7.1、把備庫至於恢復狀態:
[oracle@standby admin]$ sqlplus "/as sysdba"
SQL> alter database recover managed standby database disconnect from session;
Database altered
此時觀察備庫的alert檔案,可以發現有很多類似的資訊:
Errors in file /opt/oracle/admin/rac/bdump/rac1_mrp0_10825.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/soft/oradata/rac/raw11'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 4 /soft/oradata/rac/raw11
Clearing online log 4 of thread 2 sequence number 50
這是正常的,在第一次置於recover狀態的時候,備庫會生成對應的online redo log。
7.2、在備庫新增standby redo log
Standby redo log比archivelog方式有更大的優勢,且在最大保護、最大可用、實時恢復的情況下必須有standby redo log。
Standby redo log的組數一般為(N +1)* thread# (N分別為每個thread#的聯機日誌組數)。在本例,每一個thread的聯機日誌都是2組,所以,需要新增6組standby redo log:
SQL> alter database recover managed standby database cancel;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('/soft/oradata/rac/slog4.ora') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('/soft/oradata/rac/slog5.ora') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('/soft/oradata/rac/slog6.ora') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('/soft/oradata/rac/slog7.ora') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('/soft/oradata/rac/slog8.ora') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('/soft/oradata/rac/slog9.ora') SIZE 52428800;
注意:standby redo log的大小必須和聯機日誌一樣。
八、遇到的問題
8.1、FAL[client, MRP0]: Error 12545 connecting to PRIMARY for fetching gap sequence
Wed Apr 30 20:21:23 2008
Errors in file /opt/oracle/admin/rac/bdump/rac1_mrp0_10965.trc:
ORA-12545: Connect failed because target host or object does not exist
Wed Apr 30 20:22:23 2008
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 88-88
DBID 2232067446 branch 644085430
FAL[client]: All defined FAL servers have been attempted.
這個問題和gap有關,備庫嘗試拿gap的時候,發現不能連線主庫。這個問題是RAC的監聽造成的,參考yangtingkun的解決方法,修改兩個引數即可:
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.200.11)(PORT = 1521))' SID = 'rac1';
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.200.22)(PORT = 1521))' SID = 'rac2';
8.2
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 88-88
DBID 2232067446 branch 644085430
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
這個問題還是和gap有關。備庫現在需要從主庫上拿sequence 88這個歸檔,但這個歸檔在前面做備份的時候刪除了。解決方法是從備份還原這個歸檔:
RMAN> restore archivelog sequence 88;
補充說明:==oracle官方配置===lvlisong==
(2006年釋出的,應該是9版本的===)
1. tnsname.ora配置
CHICAGO1_SERV = (DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP)
(HOST =chicago_host1vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME = CHICAGO)
(INSTANCE_NAME =CHICAGO1)
)
)
CHICAGO2_SERV =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = chicago_host2vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CHICAGO)
(INSTANCE_NAME = CHICAGO2)
)
BOSTON =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST =boston_host1)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BOSTON)
)
)
Primary Database======配置==
*.db_unique_name=CHICAGO
*.service_names=CHICAGO
*.log_archive_config='dg_config=(CHICAGO,BOSTON)'
*.log_archive_dest_2='service=BOSTON valid_for=(online_logfiles,primary_role) db_unique_name=BOSTON'
*.db_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
*.log_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
=說明=下面引數是switch over用的,切換成standy時,fal_server='bosten'=
*.standby_file_management=auto
*.fal_server='BOSTON'
CHICAGO1.fal_client='CHICAGO1_SERV'
CHICAGO2.fal_client='CHICAGO2_SERV'
Standby database==================配置============
*.service_names='BOSTON'
*.db_unique_name='BOSTON'
*.log_archive_config='dg_config= (CHICAGO,BOSTON)'
*.log_archive_dest_2='service=CHICAGO1_SERV valid_for=(online_logfiles,primary_role)
db_unique_name=CHICAGO'
*.db_file_name_convert='+DATA/CHICAGO/', '+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.log_file_name_convert='+DATA/CHICAGO/','+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.standby_file_management=auto
*.fal_server='CHICAGO1_SERV','CHICAGO2_SERV'
*.fal_client='BOSTON'
primary database create standby redo log========
2. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;
physical standby database-------------------------------
(maximum # of logfiles +1) * maximum # of threads
This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.
Thread 1, group 1 2
Thread 2,group 3, 4
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;
SQL> ALTER DATABASE ADD STANDBY LOGFILETHREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;
查詢:
SQL> SELECT * FROM V$LOG;
SQL> SELECT * FROM V$STANDBY_LOG;
SQL> SELECT * FROM V$LOGFILE;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1220973/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle10g RAC環境下 DataGuard備庫搭建例項-3-eygleOracle
- Oracle10g RAC環境下 DataGuard備庫搭建例項-2-eygleOracle
- Oracle10g RAC環境下 DataGuard備庫搭建例項-1-eygleOracle
- Oracle10g RAC環境下DataGuard備庫搭建例項Oracle
- Oracle10g RAC環境下 DataGuard備庫搭建例項-4-自己補Oracle
- RAC環境下dataguard的搭建
- RAC和Dataguard環境下主備庫切換演練模板
- 搭建rac+DataGuard的測試環境
- (轉)Oracle rac環境下清除asm例項OracleASM
- 單例項環境利用備份恢復RAC資料庫(四)單例資料庫
- 單例項環境利用備份恢復RAC資料庫(三)單例資料庫
- 單例項環境利用備份恢復RAC資料庫(二)單例資料庫
- 單例項環境利用備份恢復RAC資料庫(一)單例資料庫
- RAC環境只啟動單例項資料庫單例資料庫
- ORACLE RAC資料庫配置Dataguard環境(3)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(2)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(1)Oracle資料庫
- 【RAC】rac環境下的資料庫備份與還原資料庫
- RAC環境下的RMAN 備份指令碼一例指令碼
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- 刪除Linux非rac環境下的ASM例項LinuxASM
- RAC環境下單例項啟動Oracle資料庫重建控制檔案案例單例Oracle資料庫
- Linux環境下oracle數庫庫改名,例項改名LinuxOracle
- 刪除Linux非rac環境下的ASM例項(轉)LinuxASM
- Rest Webservice 環境搭建流程和例項RESTWeb
- jafka環境搭建步驟--例項可用
- RAC環境下刪庫後重新建立相同例項名的問題總結
- 在容器環境搭建mysql備庫MySql
- 從單例項資料庫轉換到RAC環境——RAC的建立和配置單例資料庫
- Oracle10g RAC環境OCR的新增、刪除、備份Oracle
- Oracle10g RAC環境VoteDisk的新增、刪除、備份Oracle
- RAC和Dataguard環境下修改sys使用者密碼密碼
- 【RAC】在RAC環境中SQL*Plus命令對資料庫及例項的影響SQL資料庫
- aix下rac環境rman備份策略部署AI
- 利用STANDBY將單例項資料庫升級為RAC環境(四)單例資料庫
- 利用STANDBY將單例項資料庫升級為RAC環境(三)單例資料庫
- 利用STANDBY將單例項資料庫升級為RAC環境(二)單例資料庫
- 利用STANDBY將單例項資料庫升級為RAC環境(一)單例資料庫