RMAN配置DATAGUARD完整案例(主庫基於ASM儲存)
RAC，DATAGUARD，STREAM被認為是Oracle的高可用的三架馬車.三架馬車各有所側重:
(1)RAC主要解決了單節點故障，實現負載均衡，也實現了高效能。常用於24x7 的商業應用。
(2)DATAGUARD用冗餘的方式實現高可用的，解決容災。
(3)STREAM更加豐富，主要是應用層面的資料共享。
------->>本篇小編主要模擬在Vmware Server2.0下搭建基於oracle10gR2_32的dataguard(主庫基於ASM儲存，從庫基於普通檔案系統儲存)
======================================================================================================
ORACLE 10G 搭建DATAGUARD步驟
======================================================================================================
1 安裝環境(VMServer2.0)
在主機1上安裝資料庫軟體，並建監聽和例項，在主機2上安裝資料庫軟體，並建監聽，但不建例項。
|
|
主庫 |
dg備庫 |
|
作業系統 |
Redhat5 32位 |
Redhat5 32位 |
|
主機名 |
Rac01 |
Asm.dg |
|
IP |
192.168.63.100 |
192.168.63.132 |
|
資料庫軟體版本 |
oracle 10.2.0.1.0 |
oracle 10.2.0.1.0 |
|
ORACLE_BASE |
/opt/oracle10g |
/home/app/oracle/ |
|
ORACLE_HOME |
$ORACLE_BASE/product/10.2.0/db_1 |
$ORACLE_BASE/product/10.2.0/db_1 |
|
ORACLE_SID |
primary |
standby |
|
閃回區 |
2G |
2G |
|
歸檔 |
開啟 |
開啟 |
2 主資料庫配置
2.1 設定資料庫強制歸檔
sqlplus / as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;
FOR
---
YES
2.2 新增STANDBY日誌檔案
SQL>alter database add standby logfile group 4 ('+RAC_DISK/primary/onlinelog/standby41.log','+RAC_DISK/primary/onlinelog/standby42.log') size 50m;
SQL>alter database add standby logfile group 5 ('+RAC_DISK/primary/onlinelog/standby51.log','+RAC_DISK/primary/onlinelog/standby52.log') size 50m;
SQL>alter database add standby logfile group 6 ('+RAC_DISK/primary/onlinelog/standby61.log','+RAC_DISK/primary/onlinelog/standby62.log') size 50m;
SQL>alter database add standby logfile group 7 ('+RAC_DISK/primary/onlinelog/standby71.log','+RAC_DISK/primary/onlinelog/standby72.log') size 50m;
SQL> select * from v$logfile order by 1;
2.3 修改引數檔案
2.3.1 生成pfile
SQL>create pfile from spfile;
SQL>shutdown immediate;
2.3.2 修改pfile
vi $ORACLE_HOME/dbs/initprimary.ora
在最後新增如下內容：
primary.__db_cache_size=268435456
primary.__java_pool_size=4194304
primary.__large_pool_size=4194304
primary.__shared_pool_size=121634816
primary.__streams_pool_size=0
*.audit_file_dest='/opt/oracle10g/admin/primary/adump'
*.audit_trail='db'
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='+RAC_DISK'
*.db_recovery_file_dest_size=2097152
*.diagnostic_dest='+RAC_DISK'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.log_archive_dest_1='location=+RAC_DISK/primary'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=primary
*.fal_server='standby'
*.fal_client='primary'
*.standby_file_management=auto
*.db_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/datafile/'
*.log_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/onlinelog/'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_2='service=standby LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role)'
2.3.3 生成spfile
SQL> create spfile from pfile;
File created.
2.4 修改監聽配置檔案
2.5 修改TNS配置檔案
2.6 重啟監聽服務
lsnrctl stop
lsnrctl start
2.7 配置最大可用模式
SQL> startup
ORACLE instance started
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> exit
2.8 備份資料庫
---------->>rman備份:
RMAN> RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/home/oracle/ctl_%U';
BACKUP FORMAT '/home/oracle/db_%U_%T' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/home/oracle/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
release channel c2;
release channel c1;
}
---------->>通過scp命令命令將備份檔案傳輸到備庫相同目錄下:
3 備資料庫配置
3.1 建立相應的檔案目錄
包括dump檔案目錄，審計區,日誌區,閃回區，資料檔案目錄，可以通過 show parameter dest命令檢視(這裡都是通過oracle使用者建立)
mkdir -p /home/app/oracle/admin/standby/adump;
mkdir -p /home/app/oracle/admin/standby/udump;
mkdir -p /home/app/oracle/admin/standby/bdump;
mkdir -p /home/app/oracle/diag/rdbms/standby/standby/trace
mkdir -p /home/app/oracle/flash_recovery_area
mkdir -p /home/app/oracle/oradata/standby
3.2 修改監聽配置檔案
修改ip地址
3.3 修改TNS配置檔案
修改後的tnsnames.ora檔案內容:
3.4 重啟監聽服務
lsnrctl stop
lsnrctl start
3.5 主備庫互相tnsping
--------->>從庫tnsping主庫
----------------->>主- --------->>庫tnsping從庫
3.6 修改引數檔案
[oracle@dg2 admin]$ sqlplus / as sysdba
SQL> shutdown immediate;
vi $ORACLE_HOME/dbs/initstandby.ora
其中標紅的部分都是需要調整和備庫對應的:
SQL> create spfile from pfile;
3.7 啟動從庫到nomount,然後主庫遠端做RMAN恢復:
SQL> startup nomount pfile=/home/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora;
-->>主庫遠端連線從庫做recovery:
[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 17 08:58:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1690390844)
connected to auxiliary database: PRIMARY (not mounted)
RMAN> run
2> {
3> allocate channel c2 device type disk format '/home/oracle/ctl_%U' connect sys/sys@primary;
4> allocate channel c1 device type disk format '/home/oracle/db_%U_%T' connect sys/sys@primary;
5> allocate channel c3 device type disk format '/home/oracle/arch_%U_%T' connect sys/sys@primary;
6> allocate auxiliary channel ac2 device type disk format '/home/oracle/ctl_%U';
7> allocate auxiliary channel ac1 device type disk format '/home/oracle/db_%U_%T';
8> allocate auxiliary channel ac3 device type disk format '/home/oracle/arch_%U_%T';
9> duplicate target database for standby nofilenamecheck dorecover;
10> }
using target database control file instead of recovery catalog
allocated channel: c2
channel c2: sid=156 devtype=DISK
allocated channel: c1
channel c1: sid=128 devtype=DISK
allocated channel: c3
channel c3: sid=130 devtype=DISK
allocated channel: ac2
channel ac2: sid=36 devtype=DISK
allocated channel: ac1
channel ac1: sid=35 devtype=DISK
allocated channel: ac3
channel ac3: sid=34 devtype=DISK
Starting Duplicate Db at 19-JUL-14
contents of Memory Script:
{
set until scn 581814;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 19-JUL-14
channel ac2: starting datafile backupset restore
channel ac2: restoring control file
channel ac2: reading from backup piece /home/oracle/ctl_0epdpi2j_1_1
channel ac2: restored backup piece 1
piece handle=/home/oracle/ctl_0epdpi2j_1_1 tag=TAG20140719T120642
channel ac2: restore complete, elapsed time: 00:00:02
output filename=/home/app/oracle/oradata/standby/control01.ctl
Finished restore at 19-JUL-14
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 581814;
set newname for tempfile 1 to
"+RAC_DISK/primary/tempfile/temp.282.852908177";
switch clone tempfile all;
set newname for datafile 1 to
"/home/app/oracle/oradata/standby/system.270.852908093";
set newname for datafile 2 to
"/home/app/oracle/oradata/standby/undotbs1.272.852908095";
set newname for datafile 3 to
"/home/app/oracle/oradata/standby/sysaux.271.852908095";
set newname for datafile 4 to
"/home/app/oracle/oradata/standby/users.273.852908097";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to +RAC_DISK/primary/tempfile/temp.282.852908177 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 19-JUL-14
channel ac2: starting datafile backupset restore
channel ac2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /home/app/oracle/oradata/standby/undotbs1.272.852908095
restoring datafile 00003 to /home/app/oracle/oradata/standby/sysaux.271.852908095
channel ac2: reading from backup piece /home/oracle/db_0gpdpi2l_1_1_20140719
channel ac1: starting datafile backupset restore
channel ac1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/app/oracle/oradata/standby/system.270.852908093
restoring datafile 00004 to /home/app/oracle/oradata/standby/users.273.852908097
channel ac1: reading from backup piece /home/oracle/db_0fpdpi2l_1_1_20140719
channel ac2: restored backup piece 1
piece handle=/home/oracle/db_0gpdpi2l_1_1_20140719 tag=TAG20140719T120645
channel ac2: restore complete, elapsed time: 00:00:28
channel ac1: restored backup piece 1
piece handle=/home/oracle/db_0fpdpi2l_1_1_20140719 tag=TAG20140719T120645
channel ac1: restore complete, elapsed time: 00:00:28
Finished restore at 19-JUL-14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=853338716 filename=/home/app/oracle/oradata/standby/system.270.852908093
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=853338716 filename=/home/app/oracle/oradata/standby/undotbs1.272.852908095
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=853338716 filename=/home/app/oracle/oradata/standby/sysaux.271.852908095
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=853338716 filename=/home/app/oracle/oradata/standby/users.273.852908097
contents of Memory Script:
{
set until scn 581814;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 19-JUL-14
starting media recovery
channel ac2: starting archive log restore to default destination
channel ac2: restoring archive log
archive log thread=1 sequence=18
channel ac2: restoring archive log
archive log thread=1 sequence=19
channel ac2: reading from backup piece /home/oracle/arch_0lpdpi42_1_1_20140719
channel ac2: restored backup piece 1
piece handle=/home/oracle/arch_0lpdpi42_1_1_20140719 tag=TAG20140719T120705
channel ac2: restore complete, elapsed time: 00:00:02
archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc thread=1 sequence=18
channel clone_default: deleting archive log(s)
archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc recid=1 stamp=853338718
archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc thread=1 sequence=19
channel clone_default: deleting archive log(s)
archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc recid=2 stamp=853338718
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-JUL-14
Finished Duplicate Db at 19-JUL-14
released channel: c2
released channel: c1
released channel: c3
released channel: ac2
released channel: ac1
released channel: ac3
RMAN>
從日誌可以看到，oracle先根據引數檔案把控制檔案恢復到合適位置，然後再根據db_file_name_cover把資料檔案恢復到合適位置
3.8 恢復備庫並建立standby logfile
--->>主庫當前序列號
--->>恢復備庫:
3.9 主庫切換日誌，觀察從庫日誌:
SQL> alter system switch logfile;
System altered.
3.10 主庫進行資料修改,備庫以只讀方式查詢測試:
-->>主庫做修改
-->>從庫以只讀方式讀取主庫資料:
3.11 把主庫切換到備庫
----主庫
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary OPEN
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary STARTED
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 402653184 bytes
Fixed Size 1219664 bytes
Variable Size 125830064 bytes
Database Buffers 272629760 bytes
Redo Buffers 2973696 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RAC_DISK/primary/
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 31
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 19 13:05:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
primary MOUNTED
--備庫
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
standby OPEN
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 557844472 bytes
Database Buffers 276824064 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show user;
USER is "SYS"
SQL>create table lyl_dept as select * from scott.dept;
SQL> select * from lyl_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from lyl_dept where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
-----順利完成切換，並可在備庫進行正常更新操作.
3.12 為備庫建立tempfile
10G下面不需要給備庫建立臨時檔案。因為當它在備用狀態時候，用不到臨時檔案，當切換的時候，它會自動建立臨時檔案.如果是生產庫，個人感覺還是在備庫建立好臨時檔案.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
standby MOUNTED
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+RAC_DISK
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/home/app/oracle/oradata/standby/temp.dbf' SIZE 10M REUSE;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+RAC_DISK
/home/app/oracle/oradata/standby/temp.dbf
SQL>
3.13 遇到的問題及解決辦法
其實搭建dg不是很難，搭建過程中主要是由於沒注意細節，導致在一些環節浪費了不少時間，主要有:
(1)主庫遠端連線備庫不能,報錯資訊如下:
[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 16 15:09:50 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1690390844)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
-->>主要問題是此時備庫只是處於nomount狀態，註冊監聽需要是靜態的才可以.
(2)tempfile恢復報錯，大概如下示:
sql statement: alter database mount standby database
released channel: c2
released channel: c1
released channel: ac2
released channel: ac1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/17/2014 06:32:00
RMAN-05501: aborting duplication of target database
RMAN-05517: temporary file +RAC_DISK/primary/tempfile/temp.282.852908177 conflicts with file used by target database
-->>解決方法：
在主庫使用RMAN的Duplicate建立dg，如果資料檔案與重做日誌檔案目錄相同，要新增 nofilenamecheck ，否則不需要新增
(3)備庫記憶體不足報錯
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/19/2014 12:22:47
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-04031: unable to allocate 396 bytes of shared memory ("shared pool","select inst_id,alrid,alstm,a...","Typecheck","seg:kggfaAllocSeg")
-->>解決：增大sga。這種問題其實不應該有，細節問題.
(4)恢復中途中錯,大概資訊如下:
-->>分析解決：通過檢視後臺資料，發現日誌能歸檔成功，也姝應用日誌，alert中報說與從庫失去連線，導致恢復進行不下去。後來發現在主庫恢復過程中從庫開啟的連線還沒有exit。Shutdown從庫，清理完恢復過去的資料文 件和控制檔案，然後重新在主庫做遠端恢復就沒問題了.
4.參考資料:
-->>關於主從庫都是普通檔案系統且未用rman做備庫恢復請參見：
http://blog.itpub.net/29119536/viewspace-1166531/
-->>其他網友分享的搭建dg好貼子請參見:
http://blog.itpub.net/29119536/viewspace-1137574/
http://blog.itpub.net/29119536/viewspace-1223557/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1224061/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於catalog 建立RMAN儲存指令碼指令碼
- ASM儲存使用RMAN複製控制檔案ASM
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- [ 流雲]配置基於ASM儲存的STANDBY時日誌檔案的處理ASM
- ORACLE資料庫dataguard配置,rman移機,dx鎖處Oracle資料庫
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
- oracle asm 儲存限制OracleASM
- rman儲存指令碼指令碼
- 使用自動儲存管理 (ASM)建立資料庫ASM資料庫
- RMAN DUPLICATE建立DataGuard物理備庫
- 基於SOA 的儲存管理
- Mysql儲存過程基礎（案例+程式碼）MySql儲存過程
- 基於Hibernate的圖片資料庫儲存(mysql)資料庫MySql
- asm 儲存線上遷移ASM
- Oracle ASM儲存Spfile解析OracleASM
- 執行主備庫切換以解決主庫儲存不足
- 主備庫切換以解決主庫儲存空間不足
- 浪潮儲存基於智慧運維技術，加速儲存自治運維
- 基於Google雲端儲存的開放Maven映象中央倉庫GoMaven
- 給資料庫新增儲存空間的案例資料庫
- Esxi 6.7基於已有資料儲存配置虛擬機器（Linux類）間共享儲存（非RDM）虛擬機Linux
- rman 全庫恢復asm資料庫ASM資料庫
- dataguard 主備庫出現gap
- oracle11gR2_64之ASM到檔案系統的rman異機恢復完整案例OracleASM
- 資料庫設計：儲存過程主體資料庫儲存過程
- [20151022]dataguard備庫與修改RMAN的配置
- 基於RMAN 建立測試資料庫資料庫
- kunbernetes-基於NFS的儲存NFS
- 爬蟲資料儲存--基於MonogoDB爬蟲MonoGo
- catalog備份資料庫及RMAN儲存指令碼資料庫指令碼
- 儲存多路徑故障導致資料庫死掉案例資料庫
- 「完整案例」基於Socket開發TCP傳輸客戶端TCP客戶端
- oracle 11.2.0.3 rac資料庫線上新增ASM儲存空間Oracle資料庫ASM
- RAC+ASM+DATAGUARDASM
- 將FileSystem轉換為ASM儲存ASM
- windows 下 asm儲存啟動故障WindowsASM