10g+dg+rman+windows(借鑑rman方式做dg)
server A: 10.85.10.44 SID=orcl
target server B: 10.85.10.43
oracle version:10.2.0.1
OS platform : windows XP
Primary設定：
1. 設定主資料庫為force logging 模式
SQL>sqlplus "/as sysdba"
SQL>alter database force logging;
2. 設定主資料庫為歸檔模式
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
3. 新增"備用聯機日誌檔案"
先檢視日誌檔案位置：
SQL>select * from v$logfile;
在新增：
SQL> alter database add standby logfile group 4 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo04.log') size 50m;
SQL> alter database add standby logfile group 5 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo05.log') size 50m;
SQL> alter database add standby logfile group 6 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo06.log') size 50m;
SQL> alter database add standby logfile group 7 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo07.log') size 50m;
4. 建立主庫的初始化引數給備庫用
SQL>Create pfile from spfile;
產生的檔名為initorcl.ora 存放目錄預設放在$ORACLE_HOME/database下
5. 在主庫建立備庫的控制檔案 和密碼檔案
SQL>Alter database create standby controlfile as 'D:oracleproduct10.2.0control01.ctl';
D:>orapwd file=D:PWDorcl.ora password=admin entries=5;
預設情況下，win下口令檔案的格式是pwdsid.ora，unix下的格式是orapwSID(大小寫敏感）
6. 在主庫建立監聽和配置tnsnams.ora
listener.ora配置如下:
# listener.ora Network Configuration File: D:oracleproduct10.2.0db_1networkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:oracleproduct10.2.0db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:oracleproduct10.2.0db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.44)(PORT = 1521))
)
)
tnsnames.ora配置如下:
# tnsnames.ora Network Configuration File: D:oracleproduct10.2.0db_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.44)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.43)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
7. 在initorcl.ora新增以下內容：
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:oracleproduct10.2.0oradataarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
關閉資料庫，在用initorcl.ora重啟，並建立spfile檔案
SQL>startup pfile='$ORACLE_HOME/database/initorcl.ora'
SQL>create spfile from pfile='$ORACLE_HOME/database/initorcl.ora'
8.
A. 複製資料檔案，引數檔案，控制檔案，密碼檔案到備庫上
initorcl.ora引數檔案,PWDorcl.ora密碼檔案考到$ORACLE_HOME/database下的，
controlbak.ctl 考到$ORACLE_base/oradata/orcl/下，並分別重新命名為control01.ctl,control01.ctl,control01.ctl
資料檔案考到$ORACLE_base/oradata/orcl/下
B. 用Rman複製,不用停機
$ rman target /
RMAN> backup full format 'D:/FULL_%d_%T_%s.bak' database include current controlfile for standby;
RMAN> sql 'alter system archive log current';
RMAN> Backup ArchiveLog all format='D:/arch_%d_%T_%s.bak';
備份完後將備份檔案拷到standby上同樣的目錄，強調：同樣的目錄，在standby進行rman 恢復即可
9. 啟動主資料庫
SQL>startup
---------------------------------------------------------------------
Standy操作：
1. 用oradim工具建立備庫orcl例項
oradim.exe -new -sid orcl -startmode m
oradim.exe -edit -sid orcl -startmode a
2. 建立備庫存放資料檔案和後臺跟蹤目錄
$ORACLE_BASEORADATAORCL
$ORACLE_BASEadminorcl
$ORACLE_BASEadminorcladump
$ORACLE_BASEadminorclbdump
$ORACLE_BASEadminorclcdump
$ORACLE_BASEadminorcldpdump
$ORACLE_BASEadminorclpfile
$ORACLE_BASEadminorcludump
$ORACLE_BASEadminorcl
3. 新增"備用聯機日誌檔案"
SQL>startup mount
先檢視日誌檔案位置：
SQL>select * from v$logfile;
在新增：
SQL> alter database add standby logfile group 4 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo04.log') size 50m;
SQL> alter database add standby logfile group 5 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo05.log') size 50m;
SQL> alter database add standby logfile group 6 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo06.log') size 50m;
SQL> alter database add standby logfile group 7 ('D:ORACLEPRODUCT10.2.0ORADATAORCLredo07.log') size 50m;
4. 在備庫建立監聽和配置tnsnams.ora
listener.ora配置如下:
# listener.ora Network Configuration File: D:oracleproduct10.2.0db_1networkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:oracleproduct10.2.0db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:oracleproduct10.2.0db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.44)(PORT = 1521))
)
)
tnsnames.ora配置如下:
# tnsnames.ora Network Configuration File: D:oracleproduct10.2.0db_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.44)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.3.43)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
5. 測試主備之間網路連通
primary:
C:>lsnrctl start
C:>tnsping standby
standby
C:>lsnrctl start
C:>tnsping primary
6. 配置備庫初始化引數
編輯$ORACLE_HOME/database目錄下的initorcl.ora新增以下內容
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:oracleproduct10.2.0oradataarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
7. 啟動備用資料庫
C:>set ORACLE_SID=orcl
SQL>sqlplus "/as sysdba"
SQL>startup nomount pfile='D:oracleproduct10.2.0db_1databaseinitorcl.ora';
SQL>create spfile from pfile='D:oracleproduct10.2.0db_1databaseinitorcl.ora';
若採用Rman備份的, 則在此standby 端進行 Rman還原資料庫：
$rman target auxiliary /
RMAN> duplicate target database for standby dorecover nofilenamecheck;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
---------------------------------------------------------------------------
測試
注意Data Guard 啟動順序：
啟動順序：先standby ,後primary;
關閉順序：先primary 後standby;
在備庫將例項啟動到mount 狀態：
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>alter database recover managed standby database disconnect from session;
在備庫啟動監聽：
$lsnrctl start
在主庫啟動例項：
SQL> startup;
在主庫啟動監聽：
$lsnrctl start
在主庫驗證歸檔目錄是否有效：
SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
如果有錯誤，要排查原因。
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
70
主備查詢結果一致，Data Guard 搭建結束。
1. 測試主庫產生的歸檔日誌是否能正常傳送到歸檔日誌
主庫進行日誌切換:
SQL>Alter system switch logfile;
然後分別檢視主庫和備庫的D:arch目錄下是否產生了同樣的歸檔日誌
檔案。
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
若不同步，
1. 看log日誌， archive是否有丟失
2. 可以在備庫坐如下操作：
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
-----------------------------------------------------------------------
主備庫切換
1. switchover
一般SWITCHOVER切換都是計劃中的切換，特點是在切換後，不會丟失任何的資料，而且這個過程是可逆的，整個DATA GUARD環境不會被破壞，原來DATA GUARD環境中的所有物理和邏輯STANDBY都可以繼續工作。 在進行DATA GUARD的物理STANDBY切換前需要注意：
確認主庫和從庫間網路連線通暢；
確認沒有活動的會話連線在資料庫中；
PRIMARY資料庫處於開啟的狀態，STANDBY資料庫處於MOUNT狀態；
確保STANDBY資料庫處於ARCHIVELOG模式；
如果設定了REDO應用的延遲，那麼將這個設定去掉；
確保配置了主庫和從庫的初始化引數，使得切換完成後，DATA GUARD機制可以順利的執行。
主庫：
1. 檢視switchover 狀態
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
to standby
附： A:switchover_status出現session active/not allowed
當出現session active的時候表示還有活動的session,則執行
Alter database commit to switchover to physical standby with session shutdown;
當出現not allowed時，在官方文件說轉換會不成功，但是我測試的時候成功了，如果大家在測試不成功的時候再和我說，讓我看看在什麼情況下會不成功。
B.ora- 01153: an incompatible media recovery is active
執行下面程式碼
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
2 切換成備庫
SQL>Alter database commit to switchover to physical standby with session shutdown;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
3 啟動到mount和應用日誌狀態
SQL> SHUTDOWN IMMEDIATE
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
4. 檢視資料庫模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
備庫：
1.檢視switchover狀態
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY
附：若不是用此語句切換：ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
2. 切換成主庫
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> shutdown immediate;
SQL> startup;
SQL> alter system switch logfile;
3. 檢視資料庫模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
注意地方:
如果做了switchover,主庫引數設定成以下方式，會觸發ora-16009錯誤
Alert system set log_archive_dest_2=’service=primary
DB_UNIQUE_NAME=orcl’ scope=spfile;
然後再alert_orcl.log 日誌中會出現以下錯誤內容
Thu Nov 27 10:19:12 2008
Redo Shipping Client Connect
-- Connected User is Valid
RFS[2]: Assigned to RFS process 1292
RFS[2]: Database mount ID mismatch [0x4781d95f:0x47823be1]
RFS[2]: Client instance is standby database i
RFS[2]: Not using real app
Thu Nov 27 10:19Errors in file
d:oracleproduct10.2.0adminorcludumporc
ORA-16009: 遠端歸檔日
從metalink上查到:
* fact: Oracle Server - Enterprise Edition 9
* symptom: Errors appears in alert.log on primary database
* symptom: RFS: client instance is standby database instead
* symptom: RFS: Not using real application clusters
* symptom: Errors appear in alert.log on standby database
* symptom:
database
standby database
primary database
* symptom: Standby redo log files are defined on the standby database
* cause: The standby redo log files are synchronously filled with redo
from the primary database. When a logswitch occur on the primary database,
those files are archived on the standby database before being applyed on
it. The archiving process on the standby database should only archive to
the local disks on tprimarfix:
Disable the remote archiving on the standby databasExample: alter system set log_archive_dest_2 = ''
是因為沒有把standby 上的log_archive_dest_2 清空導致的。
另外也有可
bug 4676659
Standby may not be recognised (ORA-16009)
When the log transport is LGWR ASYNC and logical standby has
LOG_ARCHIVE_DEST setting VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
ORA-16009 is reportedregular interWorkaround:
There is no workaround to prevent ORA-16009 from appearing in alert logs.
2. Failovers：
FAILOVER切換一般是PRIMARY資料庫發生故障後的切換，這種情況是STANDBY資料庫發揮其作用的情況。這種切換髮生後，可能會造成資料的丟失。而且這個過程不是可逆的，DATA GUARD環境會被破壞。
由於PRIMARY資料庫已經無法啟動，所以FAILOVER切換所需的條件並不多，只要檢查STANDBY是否執行在最大保護模式下，如果是的話，需要將其置為最大效能模式，否則切換到PRIMARY角色也無法啟動。
1.檢視是否有日誌GAP,沒有應用的日誌：
SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如果有，則複製過來並且註冊
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路徑';
重複檢視直到沒有應用的日誌：
2. 然後停止應用歸檔：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
3. 下面將STANDBY資料庫切換為PRIMARY資料庫：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
或 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN; 或者 shutdown immediate+startup
Database altered.
檢查資料庫是否已經切換成功：
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
至此，FAILOVER切換完成。這個時候應該馬上對新的PRIMARY資料庫進行備份。
資料庫模式檢視：
1. 首先檢視當前的保護模式 ---primary資料庫操作
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
2. 設定新的資料保護模式並重啟資料庫 --primary資料庫操作
當保護模式更改順序：
maximize protection ---> maximize availability ----> maximize performance
當在把dataguard的保護級別按這上面的順序減低的時候， 不需要primary庫在mount狀態，否則primary 必須在mount 狀態。
如：
SQL> alter database set standby database to maximize availability;
alter database set standby database to maximize availability
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
報錯了，這是因為最大可用性需要先修改日誌傳送方式為lgwr同步方式，否則，資料庫是無法open.
Maximum protection/AVAILABILITY模式必須滿足以下條件
Redo Archival Process: LGWR
Network Tranmission mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
standby database type: Physical Only
SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync AFFIRM';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database open;
Database altered.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
alter database set standby database to maximize performance;
提示：maximize後可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分別對應最大保護，最高可用性及最高效能。
在最大保護模式下，直接關閉備庫是不行的，如果在備庫上關閉資料庫，會有如下提示：
SQL> shutdown immediateORA-01154: database busy. Open, close, mount, and dismount not allowed nowSQL>在最大保護模式下，備庫是不允許關閉的，此時首先關閉主庫，然後備庫就可以順利關閉了。
注意： 主庫的保護模式修改之後，備庫的模式也會改變，和主庫保持一致。
本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/tianlesoftware/archive/2009/10/26/4730092.aspx
[@more@]來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14844492/viewspace-1044649/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DG】備庫RMAN還原方式搭建DG（不使用duplicate命令）
- Rami Ismail談3A遊戲和獨立開發者的互相借鑑方式AI遊戲
- element 學習借鑑 p1
- RMAN不停機搭建DG
- 借鑑）資料庫開發祕訣資料庫
- 從“消滅病毒”到“我的小家”，開發者需要借鑑這些爆款打造方式！
- 恐怖遊戲可以借鑑Roguelike遊戲哪些特色？遊戲
- 可以從CSS框架中借鑑到什麼CSS框架
- 我們需要向哈佛借鑑的學習精神
- 微軟軟體測試的可借鑑之處微軟
- rman duplicate搭建第二個 dg
- 借鑑redux，實現一個react狀態管理方案ReduxReact
- 51定時器/計數器齊上陣，借鑑定時器
- 開發者談恐怖遊戲可以借鑑的Roguelike遊戲特色遊戲
- 遊戲研發令人頭禿？借鑑廣告素材試試遊戲
- 一個很有借鑑價值的程式設計故事程式設計
- 採用 SOA 最佳實踐，借鑑經驗教訓
- oracle 11gR2 對CRS dg做映象dgOracle
- 主庫不停做物理dg
- 【DG】在Linux平臺上搭建單例項的dataguard--rman還原方式Linux單例
- DG rman duplicate 複製庫錯誤
- Oracle DG搭建1(duplicate方式)Oracle
- 「借鑑」弓箭傳說，這款遊戲拿下6000萬月流水遊戲
- 深入Weex系列（九）Weex SDK可借鑑細節總結
- 安裝QT我都借鑑了哪些有用的資料QT
- 心流理論對遊戲互動的借鑑意義遊戲
- 曝螞蟻金服將上市：借鑑eBay和PayPal模式模式
- AI應從神經科學中借鑑想法和思路AI
- 無需跑路，GitLab 刪庫事件的借鑑意義Gitlab事件
- ORACLE DG從庫 Rman備份恢復Oracle
- Oracle DG搭建2(冷備方式)Oracle
- 怎麼快速掌握原生JavaScript 有沒有小竅門借鑑JavaScript
- Wegame翼計劃或許能夠借鑑steam綠光系統GAM
- Tomcat 架構原理解析到架構設計借鑑Tomcat架構
- 資料庫定義規範(可以借鑑，不是硬性標準)資料庫
- 阿里巴巴入股Groupon 意在借鑑國際化經驗阿里
- 借鑑增加Twitter粉絲的經驗來提高微博粉絲
- 利用RMAN做TSPITR