这是我参与8月更文挑战的第28天,活动详情查看:8月更文挑战
Oracle 从 11g 开始支持在不关闭主库的情况下,搭建 dataguard 备库。
参考 mos: 452868.1
主备库环境
Database Name :- chicago
Primary db_unique_name :- chicago
standby db_unique_name :- Boston
实施步骤
1. 为生产数据库成为主数据库做准备
a. 确保数据库是 archivelog 模式
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
b. 启用 force logging
SQL> ALTER DATABASE FORCE LOGGING;
c. 创建备用 redo 日志
SQL> alter database add standby logfile '<name>' size <size>;
d. 修改主要初始化参数,使其适用于主数据库的 dataguard
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/chicago/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set FAL_SERVER=boston;
System altered.
SQL> alter system set FAL_CLIENT=chicago;
System altered.
SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/boston/data/','/u01/app/oracle/databases/chicago/data' scope=spfile;
System altered.
SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/boston/redo/','/u01/app/oracle/databases/chicago/redo' scope=spfile;
System altered.
2. 确保 sql*net 连接正常
在备库的 listener.ora 文件中插入 Boston 的静态条目。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boston.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/OraHome111)
(SID_NAME = boston)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <auxiliary host>)(PORT = 1521))
)
主库和备库的 TNSNAMES.ORA 应该有两个条目。
CHICAGO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <target host>)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = chicago.us.oracle.com))
)
BOSTON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <auxiliary host>)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = boston.us.oracle.com))
)
在主数据库和备用数据库中使用以下命令,检查 SQL*Net 配置。
% tnsping chicago
% tnsping boston
3. 创建备用数据库
a. 密码文件配置
从主数据库 $ORACLE_HOME/dbs 中复制密码文件,并将其重命名为备用数据库名称。
用户名必须为 SYS 且主数据库和备用数据库的密码需相同。
此处的最佳做法是按照建议复制密码文件。
密码文件名称必须和备用站点上使用的实例名称/SID 匹配,而非 DB_NAME。
b. 仅用一个参数 DB_NAME 创建初始化参数。
DB_NAME=chicago
DB_UNIQUE_NAME=boston
DB_BLOCK_SIZE=<same as primary>
c. 在备库创建需要的目录以放置 $ADR_HOME 中的数据文件和跟踪文件。
d. 将环境变量 ORACLE_SID 设置为备用服务并启动备用实例。
% export ORACLE_SID=boston
% sqlplus "/ as sysdba"
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcore1.ora
e. 验证“AS SYSDBA”连接正在运行
% sqlplus /nolog
SQL> connect sys/<passwd<@boston AS SYSDBA
connect sys/<passwd>@chicago AS SYSDBA
f. 在主系统上执行RMAN 将主数据库数据复制到备数据库
$ rman target sys/sys@chicago auxiliary sys/sys@boston
connected to target database: CHICAGO (DBID=761464750)
connected to auxiliary database: CHICAGO (not mounted)
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'chicago','boston'
set db_unique_name='boston'
set db_file_name_convert='/chicago/','/boston/'
set log_file_name_convert='/chicago/','/boston/'
set control_files='/u01/app/oracle/oradata/control01.ctl'
set log_archive_max_processes='5'
set fal_client='boston'
set fal_server='chicago'
set standby_file_management='MANUAL'
set log_archive_config='dg_config=(chicago,boston)'
set log_archive_dest_2='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'
;
}
- 现在连接备用数据库 sqlplus 并启动 MRP(管理恢复进程)。
SQL> alter database recover managed standby database disconnect from session;
- 用只读模式打开 DB 并启动恢复。
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;