ORACLE Data Guard--I

ORACLE Data Guard

概念和構建物理standby和物理standby 的switchover.

[@more@]

Data Guard Protection Mode.

1. Maximum Protection

the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.

2. Maximum availability

Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike
maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log.

3. Maximum performance

This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database’s redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.

=====================================

======================================

DataGuard 配置手冊:

備庫引數檔案：

[oracle@guard dbs]$ cat initbarguard.ora
barguard.__db_cache_size=88080384
barguard.__java_pool_size=4194304
barguard.__large_pool_size=4194304
barguard.__shared_pool_size=67108864
barguard.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/product/10.2.0/admin/barguard/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2.0/admin/barguard/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u02/oradata/orcl/barguard/control01.ctl','/u02/oradata/orcl/barguard/control02.ctl','/u02/oradata/orcl/barguard/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10.2.0/admin/barguard/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name=KES1BAR
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10.2.0/admin/barguard/udump'

DB_UNIQUE_NAME=barguard
LOG_ARCHIVE_CONFIG='DG_CONFIG=(KES1BAR,barguard)'
LOG_ARCHIVE_DEST_2='SERVICE=KES1BAR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=KES1BAR'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=KES1BAR
FAL_CLIENT=barguard
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/kes2bar','/u02/oradata/orcl/barguard'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/kes2bar','/u02/oradata/orcl/barguard'
STANDBY_FILE_MANAGEMENT=AUTO

主庫引數檔案：

[oracle@db1 dbs]$ cat initKES1BAR.ora
kes2bar.__db_cache_size=88080384
kes2bar.__java_pool_size=4194304
kes2bar.__large_pool_size=4194304
kes2bar.__shared_pool_size=67108864
kes2bar.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/kes2bar/adump'
*.background_dump_dest='/u01/app/oracle/admin/kes2bar/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/kes2bar/control01.ctl','/u01/app/oracle/oradata/kes2bar/control02.ctl','/u01/app/oracle/oradata/kes2bar/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/kes2bar/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='KES1BAR'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=kes2barXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/kes2bar/udump'

DB_UNIQUE_NAME=KES1BAR
LOG_ARCHIVE_CONFIG='DG_CONFIG=(KES1BAR,barguard)'
LOG_ARCHIVE_DEST_2='SERVICE=barguard LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=barguard'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=barguard
FAL_CLIENT=KES1BAR
DB_FILE_NAME_CONVERT='/u02/oradata/orcl/barguard','/u01/app/oracle/oradata/kes2bar'
LOG_FILE_NAME_CONVERT='/u02/oradata/orcl/barguard','/u01/app/oracle/oradata/kes2bar'
STANDBY_FILE_MANAGEMENT=AUTO

有幾個點要注意：

1. 沒有設定

LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'

以下是原因：

Note: If a flash recovery area was configured (with the DB_RECOVERY_FILE_DEST initialization parameter) and you have not explicitly configured a local archiving destination with the LOCATION attribute, Data Guard automatically uses the LOG_ARCHIVE_DEST_10 initialization parameter as the default destination for local archiving. See for more information. Also, see for complete LOG_ARCHIVE_DEST_n information.

2. DB_FILE_NAME_CONVERT和
LOG_FILE_NAME_CONVERT的引數內容：

2.1 是指檔案路徑。

2.2 在主庫上是設定，”備庫檔案路徑， 主庫檔案路徑“

在備庫上是設定，”主庫檔案路徑，備庫檔案路徑”

DB_FILE_NAME_CONVERT='/u02/oradata/orcl/barguard','/u01/app/oracle/oradata/kes2bar'
LOG_FILE_NAME_CONVERT

============================================================

透過修改兩個庫的 tnsnames.ora，保證兩個庫能用tnsping相互ping通。以下是參考配置：

[oracle@db1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA10G1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora10g1)
)
)

KES1BAR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KES1BAR)
)
)

BARGUARD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = guard)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = barguard)
)
)



EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

=================================================

操作步驟：

1. 為DataGuard修改主庫的pfile。

2. alter database force logging。

3. alter database create standby controlfile as '/dld/ddl.ctl'， 建立spfile.

4. shutdown主庫。

5. 複製所有檔案到備庫。

6. 修改備庫的引數檔案。建立備庫的spfile.

7. startup 備庫到mount狀態。

8. 在備庫中啟動redo應用。 alter database recover managed standby database disconnect from session;

物理standby配置完成。

=====================================

開始切換物理standby資料庫。

1.在主庫上

1.1 alter database commit to switchover to physical standby;

1.2 shutdown immediate;

1.3 startup mount;

2. 在備庫上

2.1 select switchover_status from v$database;

此時待轉換 standby 資料庫 switchover_status 列值應該是 "TO_PRIMARY" ，如否則檢查其初始化引數 文中的設定，提示一下，比著原 primary 資料庫的初始化引數改改。

如果是第一次切換，上面的狀態是沒有問題的。
NOT ALLOWED - Either this is a standby database and the primary database has
not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to
the primary or standby database that need to be disconnected before the
switchover operation is permitted.

SWITCHOVER PENDING - This is a standby database and the primary database
switchover request has been received but not processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not complete
and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is
allowed to switch over to a primary database.

TO STANDBY - This is a primary database, with no active sessions, that is
allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the
switchover request.

During normal operations it is acceptable to see the following values for
SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY.
During normal operations on the standby it is acceptable to see the values
of NOT ALLOWED or SESSIONS ACTIVE.

2.2 轉換角色到primary.

alter database commit to switchover to primary;

2.3 開啟資料庫

alter database open;

注意：

切換過後，要在備庫上（原來的主庫）執行一下語句：

alter database recover managed standby database disconnect from session;
用來啟動REDO APPLY.

否則在以下語句的applied欄位裡顯示都是NO.

select sequence# , applied from v$archived_log;
同時狀態顯示”RECOVERY NEEDED"
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

物理standby switchover完成。