Oracle 10G windows 平臺 DataGuard 例項

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:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo04.log') size 50m;

SQL> alter database add standby logfile group 5 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo05.log') size 50m;

SQL> alter database add standby logfile group 6 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo06.log') size 50m;

SQL> alter database add standby logfile group 7 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo07.log') size 50m;

4. 建立主庫的初始化引數給備庫用

SQL>Create pfile from spfile;

產生的檔名為initorcl.ora 存放目錄預設放在$ORACLE_HOME/database下

5. 在主庫建立備庫的控制檔案和密碼檔案

SQL>Alter database create standby controlfile as 'D:/oracle/product/10.2.0/control01.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:/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = D:/oracle/product/10.2.0/db_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:/oracle/product/10.2.0/db_1/network/admin/tnsnames.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:/oracle/product/10.2.0/oradata/arch 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_BASE/ORADATA/ORCL

$ORACLE_BASE/admin/orcl

$ORACLE_BASE/admin/orcl/adump

$ORACLE_BASE/admin/orcl/bdump

$ORACLE_BASE/admin/orcl/cdump

$ORACLE_BASE/admin/orcl/dpdump

$ORACLE_BASE/admin/orcl/pfile

$ORACLE_BASE/admin/orcl/udump

$ORACLE_BASE/admin/orcl/

3. 新增"備用聯機日誌檔案"

SQL>startup mount

先檢視日誌檔案位置：

SQL>select * from v$logfile;

在新增：

SQL> alter database add standby logfile group 4 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo04.log') size 50m;

SQL> alter database add standby logfile group 5 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo05.log') size 50m;

SQL> alter database add standby logfile group 6 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo06.log') size 50m;

SQL> alter database add standby logfile group 7 ('D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/redo07.log') size 50m;

4. 在備庫建立監聽和配置tnsnams.ora

listener.ora配置如下:

# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = D:/oracle/product/10.2.0/db_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:/oracle/product/10.2.0/db_1/network/admin/tnsnames.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:/oracle/product/10.2.0/oradata/arch 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:/oracle/product/10.2.0/db_1/database/initorcl.ora';

SQL>create spfile from pfile='D:/oracle/product/10.2.0/db_1/database/initorcl.ora';

若採用Rman備份的, 則在此standby 端進行 Rman還原資料庫：

$rman target sys/admin@primary 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:/oracle/product/10.2.0/admin/orcl/udump/orc

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 ---&gt maximize availability ----&gt 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 immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL>
在最大保護模式下，備庫是不允許關閉的，此時首先關閉主庫，然後備庫就可以順利關閉了。

注意：主庫的保護模式修改之後，備庫的模式也會改變，和主庫保持一致。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-720078/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 10G windows 平臺 DataGuard 例項

相關文章