oracle 10g data guard 创建全过程

最新推荐文章于 2025-10-04 16:03:10 发布

原创最新推荐文章于 2025-10-04 16:03:10 发布 · 279 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #data guard #physical standby #logical standby

Oracle 专栏收录该内容

126 篇文章

订阅专栏

本文详细介绍了如何在两台运行 Linux Red Hat AS5 的服务器上安装 Oracle 10.2.0.4 并配置 DataGuard 环境，实现主备库之间的实时数据同步，以及如何将物理备库转换为逻辑备库以提高可用性。

环境
在192.168.1.89和90服务器，其操作系统为linux red hat as 5.2，分别安装Oracle 10.2.0.4, 再分别创建单实例数据库webdb。
以89上数据库webdb为主库，90上库为备库，配置Data Guard环境。

创建data guard physical standby的操作步骤步骤

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1718555 )

1、在89，90上新建操作系统用户oratt

useradd -m -u 1131 -g oinstall -G dba,oper,asmdba -d /home/oratt -s /bin/bash -c "Oracle Software Owner" oratt

2、设置.bash_profile文件

if [ -f ~/.bashrc ]; then
      . ~/.bashrc
fi
alias ls="ls -FA"
ORACLE_SID=webdb; export ORACLE_SID
ORACLE_UNQNAME=webdb; export ORACLE_UNQNAME
JAVA_HOME=/usr/local/java; export JAVA_HOME
ORACLE_BASE=/u01/app/oratt; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/dbhome_1; export ORACLE_HOME
ORACLE_OMS_HOME=$ORACLE_BASE/oms; export ORACLE_OMS_HOME
ORACLE_PATH=/u01/app/common/oracle/sql; export ORACLE_PATH
ASM_HOME=/u01/app/11.2.0.3/grid;export ASM_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
PATH=${PATH}:/u01/app/grid/bin
PATH=$ORACLE_OMS_HOME/agent11g/bin:${PATH}
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
export LC_ALL=en

PS1="\u@`hostname`:\${PWD}=>\${ORACLE_SID}\$"
umask 022

3、创建新建用户所用目录空间
mkdir -p /u01/app/oratt
chown -R oratt:oinstall /u01/app/oratt
chmod -R 755 /u01/app/oratt

目录/u01/app/oratt作为oracle base，安装oracle rdbms系统文件。

4、安装Oracle
在两个服务器上分别安装oracle rdbms，安装目录保持一致。
初始安装版本是10.2.0.1,然后升级到10.2.0.4。这是还没有数据库，所以直接安装升级包就可以。

5、创建数据库
在89服务器上创建数据库webdb，作为主库。如果已有数据库存在，并且需要将它做为data guard的主库，那么就不用再新建。

在90服务器上创建数据库webdb，作为备库。创建备库的目的是为了搭建一套数据库的环境，包括dump目录，密码文件，spfile文件的创建。备库的sys密码和主库的保持一致，这样新建的密码文件就直接和主库的密码文件一致。如果你不知道主库的sys密码，那么将主库的密码文件拷贝一份到备库上也可。

创建操作采用图形化工具dbca完成。

6、data guard 简述

Oracle 10g data guard就是将主库上新生成的日志采用arch或lgwr不断地传输到备库上，然后备库系统不停的应用传入的日志，使得两个库的数据近实时同步。
在主库出现物理故障如操作系统宕机或磁盘损坏等时，可以将备库切换成主库使用，最大程度上减少数据损失和数据库可用性，从而实现了一种远程灾备机制。

7、构建data guard环境

在主库上修改初始化参数文件的部分参数

*.log_archive_config='dg_config=(webdb,webdg)'
这个参数配置了主库和备库的初始化参数db_unique_name的值。

*.log_archive_dest_2='service=webdb_standby lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=webdg'
*.log_archive_dest_state_2='enable'
这个参数配置了主库采用oracle net 服务webdb_standby向备库传输日志。webdb_standby是主库的tnsnames.ora文件中的一个条目。

在主库上tnsnames.ora文件中添加两个条目webdb_standby和webdb_primary

WEBDB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.90)(PORT = 1521))
)
(CONNECT_DATA =
    (SID = webdb)
)
)

WEBDB_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.89)(PORT = 1521))
)
(CONNECT_DATA =
    (SID = webdb)
)
)

在备库上修改初始化参数

*.db_unique_name='webdg'
这个参数是10g中用于定义数据库唯一名称的一个参数，主要在data guard中使用到。以后也可以修改。

*.fal_client='webdb_standby'
*.fal_server='webdb_primary'
这两个参数用于解决主库和备库的日志传输缺口，分别设置为指向备库的oracle net服务名和指向主库的oracle net服务名。这两个服务名定义在备库的tnsnams.ora文件中。

一个FAL(Fetch Archive Log，获取归档日志)请求由MRP进程监测可用归档中的缺口来触发。用来连接回应这个请求的服务器由FAL_SERVER参数指定，你给这个参数设置一个合适的tnsnames别名。这使你的备库可以与主库连接并使用arch进程来传送所缺少的日志。

*.log_archive_config='dg_config=(webdb,webdg)'
这个参数配置了主库和备库的初始化参数db_unique_name的值，和主库保持一致。

*.log_archive_dest_1='location=/u01/app/oratt/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=webdg'
这个参数设置从主库传输过来的归档日志的保存目录。

在备库系统上tnsnames.ora文件中也添加两个条目webdb_standby和webdb_primary，它们的内容和主库上的完全一致。

在备库系统上listener.ora文件中，也必须将webdb实例静态注册进去。不能使用动态注册，因为备库实例不会处于open状态。否则在主库上传输日志时会出现ORA-12514错误。

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oratt/product/10.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = webdb)
      (ORACLE_HOME = /u01/app/oratt/product/10.2.0/dbhome_1)
    )
)

在主库上使用RMAN全备份数据库和standby用途的控制文件

oratt@serv1:/u01/app/oratt/dbbackup=>webdb$rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Nov 7 15:07:51 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: WEBDB (DBID=3348254801)

RMAN> backup database format '/u01/app/oratt/dbbackup/db%U.%T';

Starting backup at 2012-11-07 15:08:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oratt/webdb/system01.dbf
input datafile fno=00002 name=/u01/app/oratt/webdb/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oratt/webdb/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oratt/webdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2012-11-07 15:08:16
channel ORA_DISK_1: finished piece 1 at 2012-11-07 15:08:19
piece handle=/u01/app/oratt/dbbackup/db01npnfn0_1_1.20121107 tag=TAG20121107T150816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 2012-11-07 15:08:19
channel ORA_DISK_1: finished piece 1 at 2012-11-07 15:08:20
piece handle=/u01/app/oratt/dbbackup/db02npnfn3_1_1.20121107 tag=TAG20121107T150816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-11-07 15:08:20

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> backup current controlfile for standby format '/u01/app/oratt/dbbackup/ctl.standby';

Starting backup at 2012-11-07 15:09:09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 2012-11-07 15:09:09
channel ORA_DISK_1: finished piece 1 at 2012-11-07 15:09:10
piece handle=/u01/app/oratt/dbbackup/ctl.standby tag=TAG20121107T150909 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-11-07 15:09:10

备份结果集传到备库所在服务器上，密码文件也可以一块传一份。

在备库上恢复standby控制文件和数据文件

oratt@serv2:/u01/app/oratt/dbbackup=>webdb$rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Nov 7 15:18:27 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: webdb (not mounted)

RMAN> restore controlfile from '/u01/app/oratt/dbbackup/ctl.standby';

Starting restore at 2012-11-07 15:18:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oratt/webdb/control01.ctl
output filename=/u01/app/oratt/webdb/control02.ctl
output filename=/u01/app/oratt/webdb/control03.ctl
Finished restore at 2012-11-07 15:18:45

Starting restore at 2012-11-07 15:20:07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oratt/webdb/system01.dbf
restoring datafile 00002 to /u01/app/oratt/webdb/undotbs01.dbf
restoring datafile 00003 to /u01/app/oratt/webdb/sysaux01.dbf
restoring datafile 00004 to /u01/app/oratt/webdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oratt/dbbackup/db01npnfn0_1_1.20121107
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oratt/dbbackup/db01npnfn0_1_1.20121107 tag=TAG20121107T150816
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2012-11-07 15:20:14

RMAN> recover database;

Starting recover at 2012-11-07 15:20:21
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/07/2012 15:20:22
RMAN-06054: media recovery requesting unknown log: thread 1 seq 12 lowscn 216603

恢复到哪里算哪里。

在备库上创建standby logfile，满足最大可用模式的data guard配置的要求

ALTER DATABASE ADD STANDBY LOGFILE group 21 ('/u01/app/oratt/webdb/standby_redo21.log') SIZE 52428800 reuse;
ALTER DATABASE ADD STANDBY LOGFILE group 22 ('/u01/app/oratt/webdb/standby_redo22.log') SIZE 52428800 reuse;
ALTER DATABASE ADD STANDBY LOGFILE group 23 ('/u01/app/oratt/webdb/standby_redo23.log') SIZE 52428800 reuse;
ALTER DATABASE ADD STANDBY LOGFILE group 24 ('/u01/app/oratt/webdb/standby_redo24.log') SIZE 52428800 reuse;
ALTER DATABASE ADD STANDBY LOGFILE group 25 ('/u01/app/oratt/webdb/standby_redo25.log') SIZE 52428800 reuse;

新增standby logfile的大小和redo logfile的大小一致。

将备库设置为实时恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;

将主库设置为maximize availability模式

SQL> select name,protection_mode,protection_level from v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL
--------- -------------------- --------------------
WEBDB     MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2085320 bytes
Variable Size             486542904 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,protection_mode,protection_level from v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL
--------- -------------------- --------------------
WEBDB     MAXIMUM AVAILABILITY RESYNCHRONIZATION

调试日志传输和应用

在主库上，将log_archive_dest_state_2先defer再enable一下，最后检查 select protection_mode,protection_level from v$database;结果应该是一致的。

SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.

SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

也可以从主库上检查哪些归档日志文件没有在备库上被应用。

SQL> select max (SEQUENCE#),DEST_ID,APPLIED from v$archived_log group by DEST_ID,APPLIED ;

MAX(SEQUENCE#)    DEST_ID APP
-------------- ---------- ---
            33          1 NO
            33          2 YES

路径1和路径2的最大sequence#值是一致的，表示主库上写入本地和备库服务器上的归档日志文件是一致的，并且在备库上已经应用最新的日志文件。

将备库节点上的physical standby 转换为 logical standby的操作步骤

在physical standby的PROTECTION_MODE的PROTECTION_LEVEL的值都是"MAXIMUM AVAILABILITY"时，才能进行备库模式转换。

在主库节点的tnsnames.ora文件中增加下列条目webdb_reader

WEBDB_READER =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.90)(PORT = 1844))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = webread)
    )
)

在备库节点上创建初始化参数文件initwebread.ora和密码文件orapwwebread

将原来的initwebdb.ora和orapwwebdb拷贝一下，再修改参数文件中的这些条目。
*.db_name='webread'
*.db_unique_name='webdg'
*.fal_client='webdb_reader'
*.fal_server='webdb_primary'
*.log_archive_config='dg_config=(webdb,webdg)'
*.log_archive_dest_1='location=/u01/app/oratt/archivelog/reader/ valid_for=(all_logfiles,all_roles) db_unique_name=webdg'
*.log_archive_dest_2='location=/u01/app/oratt/archivelog/standby/ valid_for=(standby_logfiles,standby_roles) db_unique_name=webdg'

在备库节点上的tnsnames.ora中条目webdb_reader

WEBDB_READER =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.90)(PORT = 1844))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = webread)
    )
)

在备库节点上数据库实例取消恢复管理模式

alter database recover managed standby database cancel;

在主库节点上创建logical standby的数据字典
EXECUTE DBMS_LOGSTDBY.BUILD;

注意
这一步必须执行成功，且必须在其他操作之前执行，否则后面将执行的操作 alter database recover to logical standby webread; 会一直等待。
在执行这个操作过程中，该会话会处于等待状态，等待事件为 enq: TX - contention

在备库节点上将物理备库转换为逻辑备库，数据库名称从webdb修改为webread

alter database recover to logical standby webread;

SQL> alter database recover to logical standby webread;
alter database recover to logical standby webread
*
ERROR at line 1:
ORA-20000: File /u01/app/oratt/webdb/temp01.dbf has wrong dbid or dbname,
remove or restore the offending file.

提示临时文件错误。可以忽略，加alter database open resetlogs重启数据库

SQL> startup open

ORACLE instance started.

Total System Global Area 5.1540E+10 bytes

Fixed Size                  2179936 bytes

Variable Size            6425676960 bytes

Database Buffers         4.5097E+10 bytes

Redo Buffers               14594048 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

增加临时文件

alter tablespace temp add tempfile '/u01/app/oratt/webdb/temp02.dbf' size 1024M;

ALTER DATABASE TEMPFILE '/u01/app/oratt/webdb/temp01.dbf' DROP INCLUDING DATAFILES;

调整主库上log_archive_dest_2的参数值

在物理standby模式下，这个参数的service值是指向webdb_standby的，现在在逻辑standby模式下，它需要修改为指向webdb_reader。

主库的初始化参数文件initofdb.ora配置项也需要手工修改。
内存中修改操作为：

alter system set log_archive_dest_2='service=webdb_reader lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=webdg';

启动备库
alter database stop logical standby apply;
alter database start logical standby apply immediate;

--查看等待事件
select * from dba_logstdby_events t where t.event_time > sysdate - 1 / 2;

select event_time,
       xidusn,
       xidslt,
       xidsqn,
       status,
       status_code,
       event,
       'exec dbms_logstdby.skip_transaction(' || xidusn || ',' || xidslt || ',' ||
       xidsqn || ');' as exec_sql,
       a.*
from dba_logstdby_events a
where event_time = (select max(event_time) from dba_logstdby_events);

select * from dba_logstdby_skip;

select * from dba_logstdby_skip_transaction;

--查看进程
select * from v$logstdby_process;

--查看运行状态
select * from v$logstdby_state;

-- the end