Oracle 10g 创 建 物 理 备 用 数 据 库 (Broken) - Linux, Unix
参考文档:
http://download.oracle.com/docs/cd/B12037_01/server.101/b10822/dgmgrl.htm
Oracle版本: 10.2.0.1.0, 10.2.0.2.0, 10.2.0.4.0
OS版本: RedhatAS4u8, AIX 5300-05, HPUnix 11iv3
环境
DatabaseType IP HOSTANME ORACLE_SID instance_name db_name db_unique_name global_name service_name TNS Alias ORACLE_BASE ORACLE_HOME
=====================================================================================================================================================================================================
主 192.168.253.10 edrsr10p1.us.oracle.com orcl orcl orcl orcl orcl.oracle.com orcl.oracle.com orcl /u01/app/oracle /u01/app/oracle/product/10.2.0/db_1
物理备用 192.168.253.11 edrsr10p2.us.oracle.com orcl orcl orcl orcldg orcl.oracle.com orcl.oracle.com orcldg /u01/app/oracle /u01/app/oracle/product/10.2.0/db_1
主数据库数据文件结构
/u01/app/oracle/
oradata/
orcl/
数据文件
archivelog/
orcl/
归档文件
orclsby/
切换状态的归档文件
orcl/
物理备用数据库角色切换的归档文件
flash_recovery_area/
闪回数据库的文件
客户端配置
=========================================================================================
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
=========================================================================================
1. 准备工作
# cat /etc/hosts
=========================================================================================
127.0.0.1 localhost.localdomain localhost
192.168.253.10 edrsr10p1.us.oracle.com edrsr10p1
192.168.253.11 edrsr10p2.us.oracle.com edrsr10p2
=========================================================================================
2. 对主数据库启用归档模式、强制日志模式
a. 对主数据库启用归档模式
$ mkdir /u01/app/oracle/archivelog/orcl
SQL>
alter system set db_unique_name = orcl scope=spfile;
alter system set service_names = 'orcl.oracle.com';
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_max_processes = 4;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list
alter system switch logfile;
create pfile from spfile;
说明: 若主数据库是 RAC 数据库，LOG_ARCHIVE_FORMAT 参数中必须设置 %t 或 %T ，指明是哪一个 thread。
b. 对主数据库启用flashback(可选，不是必须)
alter system set db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area' scope=spfile;
alter system set db_recovery_file_dest_size = 4G;
alter system set db_flashback_retention_target = 1440;
shutdown immediate
startup mount
alter database flashback on;
alter database open;
select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
YES
c. 在主数据库上启用强制日志记录
SQL>
alter database force logging;
select force_logging from v$database;
FORCE_
------
YES
3. 创建主数据库的密码文件(如果主数据库密码文件不存在的话)
$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y
$ ls -ltr $ORACLE_HOME/dbs/orapworcl
-rw-r----- 1 oracle oinstall 1536 Aug 8 16:21 /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl
注意: 主备数据库的SYS密码必须一致
4. 在主数据库上，创建物理备用数据库的standby重做日志（可选，但强烈建议最好创建）
说明: 如果物理备用数据库要配置成"Real-Time"或"Maximum Availability"或"Maximum Protection"模式，就必须要创建物理备用数据库的重做日志
SQL> set lines 10000 pages 10000
SQL> select group#, status, type, member from v$logfile order by group#, member;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------------------------------ ---------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redosby01.log') size 50M reuse;
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redosby02.log') size 50M reuse;
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redosby03.log') size 50M reuse;
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redosby04.log') size 50M reuse;
SQL> select group#, status, type, member from v$logfile where type = 'STANDBY' order by group#, member;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------------------------------ ---------------------
4 STANDBY /u01/app/oracle/oradata/orcl/redosby01.log NO
5 STANDBY /u01/app/oracle/oradata/orcl/redosby02.log NO
6 STANDBY /u01/app/oracle/oradata/orcl/redosby03.log NO
7 STANDBY /u01/app/oracle/oradata/orcl/redosby04.log NO
其它操作说明:
添加成员:
SQL> alter database add standby logfile member '/u01/app/oracle/oradata/orcl/redosby01b.log' to group 4;
删除:
SQL> alter database drop logfile '/u01/app/oracle/oradata/orcl/redosby01b.log';
5. 在物理备用数据库上创建目录结构
/u01/app/oracle/
admin/
orcl/
adump/
bdump/
cdump/
dpdump/
pfile/
scripts/
udump/
oradata/
orcl/
数据文件
archivelog/
orcl/
归档文件
orclsby/
物理备用数据库角色切换的归档文件
flash_recovery_area/
闪回数据库的文件
$ mkdir /u01/app/oracle/admin/orcl
$ cd /u01/app/oracle/admin/orcl
$ mkdir adump bdump cdump udump
$ mkdir /u01/app/oracle/archivelog/orclsby
6. 为主备数据库配置网络服务
主数据库：
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
=========================================================================================
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
=========================================================================================
物理备用数据库:
$ vi $ORACLE_HOME/network/admin/listener.ora
=========================================================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
=========================================================================================
$ vi $ORACLE_HOME/network/admin/sqlnet.ora (此文件也可不存在)
=========================================================================================
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
=========================================================================================
$ lsnrctl stop; lsnrctl start
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
=========================================================================================
ORCLPRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
=========================================================================================
确保主备数据库能相互tnsping到
主数据库:
$ tnsping orcldg
备用数据库:
$ tnsping orclpri
7. 修改主数据库参数(全部)
$ mkdir /u01/app/oracle/archivelog/orclsby
SQL>
alter system set db_domain = 'oracle.com' scope=spfile;
alter database rename global_name to orcl.oracle.com;
alter system set dg_broker_start = false;
alter system set remote_login_passwordfile = exclusive scope=spfile;
alter system set instance_name = orcl scope=spfile;
alter system set db_unique_name = ORCL scope=spfile;
alter system set service_names = 'orcl.oracle.com';
alter system set log_archive_config = 'DG_CONFIG=(orcl,orcldg)';
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_2 = 'SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = defer;
alter system set archive_lag_target = 1800;
alter system set log_archive_max_processes = 4;
alter system set standby_archive_dest = '/u01/app/oracle/archivelog/orclsby';
alter system set standby_file_management = auto;
alter system reset db_create_file_dest scope=spfile sid='*';
alter system set fal_server = orcldg;
alter system set fal_client = orcl;
create pfile from spfile;
8. 搭建物理备用数据库
本文采用冷拷贝方法，复制主数据库的相关文件至物理备用数据库的对应目录
a. 关闭数据库
$ sqlplus "/ as sysdba"
SQL> shutdown immediate
b. 复制主数据库文件至物理备用数据库
复制主数据库的参数文件(pfile)、密码文件、数据文件、Redo Log文件（控制文件、归档文件、闪回日志文件除外）至物理备用数据库服务器的物理文件对应目录中
$ scp /u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora oracle@edrsr10p2.us.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
$ scp /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl oracle@edrsr10p2.us.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
$ scp /u01/app/oracle/oradata/orcl/*.dbf oracle@edrsr10p2.us.oracle.com:/u01/app/oracle/oradata/orcl/
$ scp /u01/app/oracle/oradata/orcl/*.log oracle@edrsr10p2.us.oracle.com:/u01/app/oracle/oradata/orcl/
c. 创建物理备用数据库的控制文件
SQL> startup mount
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/orcl/control_sby.ctl' reuse;
SQL> alter database open;
SQL> alter system switch logfile;
传递刚刚建立的物理备用数据库的控制文件
$ scp /u01/app/oracle/oradata/orcl/control_sby.ctl oracle@edrsr10p2.us.oracle.com:/u01/app/oracle/oradata/orcl/
在物理备用数据库上，复制新创建的控制文件:
$ cp /u01/app/oracle/oradata/orcl/control_sby.ctl /u01/app/oracle/oradata/orcl/control01.ctl
$ cp /u01/app/oracle/oradata/orcl/control_sby.ctl /u01/app/oracle/oradata/orcl/control02.ctl
$ cp /u01/app/oracle/oradata/orcl/control_sby.ctl /u01/app/oracle/oradata/orcl/control03.ctl
9. 根据主数据库的pfile来创建物理备用数据库的pfile
$ vi $ORACLE_HOME/dbs/initorcl.ora
修改/核对以下项：
=========================================================================================
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl', '/u01/app/oracle/oradata/orcl/control03.ctl'
*.instance_name='ORCL'
*.service_names='orcl.oracle.com'
*.db_unique_name='ORCLDG'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_2='SERVICE=orclpri VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.standby_archive_dest='/u01/app/oracle/archivelog/orclsby'
*.fal_server='ORCL'
*.fal_client='ORCLPRI'
=========================================================================================
10. 启动物理备用数据库
$ mkdir /u01/app/oracle/admin/orcl
$ cd /u01/app/oracle/admin/orcl
$ mkdir adump bdump cdump udump
$ sqlplus "/ as sysdba"
SQL> create spfile from pfile;
SQL> startup mount
SQL> alter database recover managed standby database disconnect [from session];
查看物理备用数据库的临时表空间是否包含数据文件，若无，则添加之(9i)
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select tablespace_name from dba_tablespaces where contents = 'TEMPORARY';
TABLESPACE_NAME
------------------------------------------------------------
TEMP
SQL> select tablespace_name, file_name from dba_temp_files order by tablespace_name;
SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 20M reuse autoextend on next 640K maxsize 20480M;
SQL> alter database recover managed standby database disconnect [from session];
11. 配置主数据库
$ sqlplus "/ as sysdba"
SQL>
alter system set log_archive_dest_state_2=enable;
alter system archive log current;
检查日志文件是否被正常传输
12. 主备数据库状态观察
主数据库信息:
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY SESSIONS ACTIVE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog/orcl
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
......
物理备用数据库信息:
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE UNPROTECTED UNPROTECTED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog/orcl
Oldest online log sequence 8
Next log sequence to archive 0
Current log sequence 10
13. 将物理备用数据库切换到只读模式，进行测试
备用数据库:
SQL> select process, status, sequence#, block#, blocks, delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
------------------ ------------------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 10 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
在主数据库上创建表及插入测试数据:
SQL>
create table scott.t1 (id number);
insert into scott.t1 select object_id from all_objects;
commit
alter system archive log current;
备用数据库:
SQL> select process, status, sequence#, block#, blocks, delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
------------------ ------------------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 11 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
确认 MRP0 的 SEQUENCE#, BLOCK# 的值在增加
将物理备用数据库切换到只读模式，查看数据有无同步过来：
alter database recover managed standby database cancel;
alter database open read only;
select * from scott.t1;
将备用数据库还原至日志接收状态：
alter database recover managed standby database disconnect [from session];
14. 配置物理备用数据库的闪回
SQL> alter database recover managed standby database cancel;
SQL> alter database flashback on;
SQL> alter database recover managed standby database disconnect [from session];
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
YES
★ 至此，主备数据库配置完毕！
15. 创建物理备用数据库的standby重做日志
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/stby_redo01.log') size 50M reuse;
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/stby_redo02.log') size 50M reuse;
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/stby_redo03.log') size 50M reuse;
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/stby_redo04.log') size 50M reuse;
SQL> select * from v$logfile where type = 'STANDBY' order by group#;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
4 STANDBY /u01/oracle/oradata/orcl/stby_redo01.log NO
5 STANDBY /u01/oracle/oradata/orcl/stby_redo02.log NO
6 STANDBY /u01/oracle/oradata/orcl/stby_redo03.log NO
7 STANDBY /u01/oracle/oradata/orcl/stby_redo04.log NO
16. 配置 Dataguard 代理配置
i. 配置 listener.ora, tnsnames.ora
主数据库：
$ vi $ORACLE_HOME/network/admin/listener.ora
====================================================================================================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
====================================================================================================================================
$ lsnrctl stop; lsnrctl start
说明:
● GLOBAL_DBNAME 设置格式: <db_unique_name>_DGMGRL.<db_domain>
● GLOBAL_DBNAME, SID_NAME 必须严格区分大小写。
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
====================================================================================================================================
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
====================================================================================================================================
物理备用数据库：
$ vi $ORACLE_HOME/network/admin/listener.ora
====================================================================================================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
====================================================================================================================================
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
====================================================================================================================================
ORCLPRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
====================================================================================================================================
ii. 设置主、备数据库的 dg_broker_start 参数为 TRUE
alter system set dg_broker_start = true;
select dataguard_broker from v$database;
DATAGUARD_BROKER
----------------
ENABLED
iii. 创建 Broker 配置
a. 将主数据库添加到 broker 配置中
$ dgmgrl sys/oracle@orcl
DGMGRL> create configuration DGCONFIG1 as primary database is orcl connect identifier is orcl;
b. 将物理备用数据库添加到 broker 配置中
DGMGRL> add database orcldg as connect identifier is orcldg maintained as physical;
iv. 启用 Broker 配置
a. 启用整个配置
DGMGRL> enable configuration
b. 验证配置已成功启用
DGMGRL> show configuration
Configuration
Name: DGConfig1
Enabled: YES
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
orcldg - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DGConfig1":
SUCCESS
c. 查看数据库参数
DGMGRL>
edit database orcl set property LogArchiveFormat='%t_%s_%r.arc';
edit database orcldg set property LogArchiveFormat='%t_%s_%r.arc';
show database orcl
show database orcldg
show database verbose orcl
show database verbose orcldg
生成数据库状态报告（如果遇到 ORA-16810 时可以使用）
show database orcl statusreport
修改物理备用数据库的 tnsnames.ora 中的 ORCL 配置，使之指向主数据库:
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
====================================================================================================================================
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
====================================================================================================================================
◆ 配置"Real-Time Apply"的备用数据库
a. 修改主备数据库参数
DGMGRL>
edit database orcl set property DELAYMINS='0';
edit database orcldg set property DELAYMINS='0';
b. 在主数据库上修改配置
SQL> alter system archive log current;
注意: 执行日志切换操作的次数要和主数据库的redolog组的数目一致
在alert日志文件中将看到如下提示信息：
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
备用数据库状态观察
SQL> select dest_id, recovery_mode from v$archive_dest_status;
DEST_ID RECOVERY_MODE
---------- ----------------------------------------------
1 MANAGED REAL TIME APPLY
2 MANAGED REAL TIME APPLY
3 MANAGED REAL TIME APPLY
4 MANAGED REAL TIME APPLY
5 MANAGED REAL TIME APPLY
6 MANAGED REAL TIME APPLY
7 MANAGED REAL TIME APPLY
8 MANAGED REAL TIME APPLY
9 MANAGED REAL TIME APPLY
10 MANAGED REAL TIME APPLY
11 MANAGED REAL TIME APPLY
c. 测试
在备用数据库上执行:
SQL> set lines 10000 pages 10000
SQL> select process, status, sequence#, block#, blocks, delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
------------------ ------------------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 APPLYING_LOG 12 434 102400 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 12 434 2 0
在主数据库上执行:
SQL>
truncate table scott.t1;
insert into scott.t1 select object_id from all_objects;
commit;
此时，无须进行切换日志！！！
在备用数据库上执行:
SQL> select process, status, sequence#, block#, blocks, delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
------------------ ------------------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 APPLYING_LOG 12 434 102400 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 12 434 2 0
观察 MRP0 和 RFS 的 block# 值是否在增加
在物理备用数据库:
$ dgmgrl sys/oracle@orcl
DGMGRL> edit database orcldg set state='READ-ONLY';
SQL> select * from scott.t1;
将看到提交的数据，哪怕日志没有发生切换
DGMGRL> edit database orcldg set state='ONLINE';
◆ 配置物理备用数据库为数据最大保护(Maximum Protection)模式
1. 修改主数据库配置：
$ dgmgrl sys/oracle@orcl
DGMGRL>
edit database orcl set property LogXptMode='SYNC';
edit database orcldg set property LogXptMode='SYNC';
show database orcl LogXptMode
show database orcldg LogXptMode
2. 修改主数据库保护模式
DGMGRL> edit configuration set protection mode as MaxProtection;
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
主物理数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM PROTECTION PRIMARY
备用物理数据库上查询：
SQL>
set lines 10000 pages 10000
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG MOUNTED MAXIMUM PROTECTION PHYSICAL STANDBY
3. 测试
在这种情况下，如果物理备用数据库宕机或者网络不通，则主库也宕机，保证数据主备库完全一致
强制关闭物理备用数据库
$ dgmgrl sys/oracle@orcl
DGMGRL> shutdown abort
在主库上作一个产生事务的操作
SQL> create table scott.t2 (id number);
此时，主数据库的所有事务操作都会 hang 住
检查主数据库 alert 日志
=========================================================================================
Sat Sep 24 00:11:48 2011
ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC3: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC3]: Error 3113 when pinging standby (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=edrsr10p2.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcldg_XPT.oracle.com)(INSTANCE_NAME=ORCL)(SERVER=dedicated))).
LNSb started with pid=30, OS id=29794
Error 12514 received logging on to the standby
Sat Sep 24 00:12:13 2011
LGWR: Error 12514 attaching to RFS for reconnect
=========================================================================================
说明配置运行完全正常。
4. 将备用数据库切换到read only状态，并应用redo日志
$ dgmgrl sys/oracle@orcl
DGMGRL> startup mount
一旦数据库启动，主数据库的所有事务操作都会立刻执行
◆ 配置物理备用数据库为数据最大可用(Maximum Available)模式
1. 修改主数据库配置：
说明：如果数据库已经是最大保护模式，则无须修改！
$ dgmgrl sys/oracle@orcl
DGMGRL>
shutdown immediate
startup
edit database orcl set property LogXptMode='SYNC';
edit database orcldg set property LogXptMode='SYNC';
show database orcl LogXptMode
show database orcldg LogXptMode
2. 修改主数据库保护模式
DGMGRL> edit configuration set protection mode as MaxAvailability;
主物理数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM AVAILABILITY PRIMARY
备用物理数据库上查询：
SQL>
set lines 10000 pages 10000
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG MOUNTED MAXIMUM AVAILABILITY PHYSICAL STANDBY
◆ 配置物理备用数据库为数据最大性能(Maximum Performance)模式
1. 修改主数据库保护模式
DGMGRL> edit configuration set protection mode as MaxPerformance;
主物理数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
备用物理数据库上查询：
SQL>
set lines 10000 pages 10000
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
2. 修改主数据库配置：
说明：如果数据库已经是最大保护模式，则无须修改！
$ dgmgrl sys/oracle@orcl
DGMGRL>
edit database orcl set property LogXptMode='ASYNC';
edit database orcldg set property LogXptMode='ASYNC';
show database orcl LogXptMode
show database orcldg LogXptMode
◆ Standby数据库模式切换
● 启动Standby数据库
DGMGRL> startup mount
DGMGRL> edit database orcldg set state='ONLINE';
● 关闭Standby数据库
DGMGRL> edit database orcldg set state='OFFLINE';
● 更改备用库到只读状态
DGMGRL> edit database orcldg set state='READ-ONLY';
● 更改备用库只读状态到日志接收状态
DGMGRL> edit database orcldg set state='ONLINE';
● 停止主库到备库的日志传送
DGMGRL> edit database orcl set state='LOG-TRANSPORT-OFF';
● 恢复主库到备库的日志传送
DGMGRL> edit database orcl set state='ONLINE';
◆ 主备数据库switchover操作(主备数据库角色切换，即：将主数据库切换到物理备用数据库，将物理备用数据库切换到主数据库)
1. 主数据库切换至备用数据库
主数据库:
$ dgmgrl sys/oracle@orcl
DGMGRL> show configuration
Configuration
Name: dgconfig1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcldg - Physical standby database
Current status for "dgconfig1":
SUCCESS
DGMGRL> edit configuration set protection mode as MaxPerformance;
DGMGRL> edit database orcl set property LogXptMode='SYNC';
DGMGRL> edit database orcldg set property LogXptMode='SYNC';
DGMGRL> switchover to orcldg;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcldg"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orcl" on database "orcldg"
Starting instance "orcl"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "orcl"
You must start instance "orcl" manually
Switchover succeeded, new primary is "orcldg"
操作成功后，会自动启动原主数据库至 mount 状态。
启动新的主数据库:
$ dgmgrl sys/oracle@orcldg
DGMGRL> startup
DGMGRL> show configuration
Configuration
Name: dgconfig1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Physical standby database
orcldg - Primary database
Current status for "dgconfig1":
SUCCESS
2. 检查数据库状态
原主物理数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
原物理备用数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
★ 至此，主备数据库角色切换完毕
3. 主、备用数据库角色回切
备用数据库：
$ dgmgrl sys/oracle@orcldg
DGMGRL> show configuration
Configuration
Name: dgconfig1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Physical standby database
orcldg - Primary database
Current status for "dgconfig1":
SUCCESS
DGMGRL> switchover to orcl;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "orcl" on database "orcldg"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcldg"
Starting instance "orcl"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "orcl"
You must start instance "orcl" manually
Operation requires startup of instance "orcl" on database "orcl"
You must start instance "orcl" manually
Switchover succeeded, new primary is "orcl"
操作成功后，会自动启动物理备用数据库至 mount 状态。
启动主数据库:
$ dgmgrl sys/oracle@orcl
DGMGRL> startup
4. 检查数据库状态
主物理数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
原物理备用数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
★ 至此，主备数据库角色切换完毕
◆ 物理备用数据库failover操作(当主数据库发生不可恢复的故障，激活物理备用数据库)
1. 在物理备用数据库上执行：
$ dgmgrl sys/oracle@orcldg
DGMGRL> show configuration
Configuration
Name: dgconfig1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
orcldg - Physical standby database
Current status for "dgconfig1":
Error: ORA-16625: cannot reach the database
DGMGRL> failover to orcldg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcldg"
2. 检查数据库状态
新主数据库上查询：
SQL>
set lines 10000 pages 10000
select name, log_mode, open_mode, protection_mode, database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------ ------------------------ -------------------- ---------------------------------------- --------------------------------
ORCL ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
3. 修改 db_unique_name 参数(可选)
SQL>
alter system set db_unique_name = orcl scope=spfile;
◆ 删除 Broken 配置
禁用某个备用库
DGMGRL> disable database orcldg;
禁用 Broken 配置
DGMGRL> disable configuration;
从配置中删除指定备用数据库库
DGMGRL> remove database orcldg;
删除配置：
DGMGRL> remove configuration;
SQL>
alter system set dg_broker_start = false;
$ rm $ORACLE_HOME/dbs/dr*
参考文档:
http://download.oracle.com/docs/cd/B12037_01/server.101/b10822/dgmgrl.htm
89
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
