Oracle 11g dataguard 搭建(DUPLICATE)

1,040 阅读1分钟

这是我参与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'
;
}
  1. 现在连接备用数据库 sqlplus 并启动 MRP(管理恢复进程)。
SQL> alter database recover managed standby database disconnect from session;
  1. 用只读模式打开 DB 并启动恢复。
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;