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 Section 5.2.3 for more information. Also, see Chapter 14 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完成。

ITPub博客

ORACLE Data Guard--I

最新文章