oracle 11gdata guard,Oracle 11g Data Guard配置

操作环境说明：

两台服务器使用相同的Redhat 5.5

内核版本为：2.6.18-194.el5

在主库(primary database)中提前安装好了Oracle 11gR2软件，通过DBCA创建了数据库，实例名为PRIMARY

备库(standby database)中只安装了Oracle 11gR2软件，未创建数据库。

本文完整记录基于11g的DG配置

主库配置

1、确认主库开启了archivelog模式，并设置为force logging模式

SYS@PRIMARY>alter database force logging ;

alter database force logging

*

ERROR at line 1:

ORA-12920: database is already in force logging mode

复制代码

2、创建standby controlfile

SYS@PRIMARY>alter database create standby controlfile as '/u01/data/primary/primary/standby.ctl';

Database altered.

复制代码

3、登录到主库，可以了解一下数据文件的信息，之后安全关闭数据库，将全部数据文件拷贝到备库的相应目录下,直接复制目录最简单

SYS@PRIMARY>select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/data/primary/system01.dbf

/u01/data/primary/sysaux01.dbf

/u01/data/primary/undotbs01.dbf

/u01/data/primary/users.dbf

SYS@PRIMARY>show parameter control_files

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /u01/data/primary/primary/cont

rol01.ctl, /u01/data/primary/p

rimary/control02.ctl

SYS@PRIMARY>select group#,member from v$logfile;

GROUP# MEMBER

---------- ------------------------------

1 /u01/data/primary/redo01a.rdo

1 /u01/data/primary/redo01b.rdo

2 /u01/data/primary/redo02a.rdo

2 /u01/data/primary/redo02b.rdo

3 /u01/data/primary/redo03a.rdo

3 /u01/data/primary/redo03b.rdo

6 rows selected.

SYS@PRIMARY>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PRIMARY>quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ cd /u01/data/

[oracle@localhost data]$ scp -r primary/ 172.26.29.4:/u01/data

oracle@172.26.29.4's password:

system01.dbf                                                                               100%  172MB  34.4MB/s   00:05

redo01b.rdo                                                                                100%  100MB  33.3MB/s   00:03

sysaux01.dbf                                                                               100%  100MB  50.0MB/s   00:02

undotbs01.dbf                                                                              100%  133MB  44.3MB/s   00:03

.......

[oracle@localhost data]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 21 20:54:47 2012

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

Connected to an idle instance.

SYS@PRIMARY>startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2213776 bytes

Variable Size             402655344 bytes

Database Buffers         1191182336 bytes

Redo Buffers                7360512 bytes

Database mounted.

Database opened.

复制代码

4、主库修改初始化参数文件

SYS@PRIMARY>create pfile from spfile;

File created.

SYS@PRIMARY>!

复制代码

在pfile后面追加如下这些参数：

参数的含义请查看联机文档。

log_archive_config='dg_config=(primary,standby)'

log_archive_dest_1='location=/u01/data/primary/arc'

log_archive_dest_2='service=standby async valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY'

log_archive_dest_state_1='enable'

log_archive_dest_state_2='enable'

log_archive_format='%t_%s_%r.arc'

log_archive_max_processes=10

log_file_name_convert='/u01/data/primary/arc','/u01/data/primary/arc'

fal_client='PRIMARY'

fal_server='STANDBY'

standby_file_management='auto'

remote_login_passwordfile='exclusive'

这里要注意：

log_archive_dest_2这个参数中db_unique_name的值为备库的db_unique_name，最好完全与备库一致，包括大小写

log_file_name_convert的值中，第一个值为主库的归档日志目录，第二个为备库的归档日志目录

[oracle@localhost dbs]$ cat initPRIMARY.ora

PRIMARY.__db_cache_size=1191182336

PRIMARY.__java_pool_size=16777216

PRIMARY.__large_pool_size=16777216

PRIMARY.__oracle_base='/u01/app'#ORACLE_BASE set from environment

PRIMARY.__pga_aggregate_target=1677721600

PRIMARY.__sga_target=1610612736

PRIMARY.__shared_io_pool_size=0

PRIMARY.__shared_pool_size=369098752

PRIMARY.__streams_pool_size=0

*.compatible='11.2.0'

*.control_files='/u01/data/primary/primary/control01.ctl','/u01/data/primary/primary/control02.ctl'

*.core_dump_dest='/u01/app/oracle/admin/primary/cdump'

*.db_block_size=8192

*.db_name='PRIMARY'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=53687091200

*.db_unique_name='PRIMARY'

*.fal_client='PRIMARY'

*.fal_server='STANDBY'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/u01/data/primary/arc'

*.log_archive_dest_2='service=standby async valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=10

*.log_file_name_convert='/u01/data/primary/arc','/u01/data/primary/arc'

*.nls_date_format='YYYY-MM-DD hh24:mi:ss'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=1669332992

*.processes=1000

*.remote_login_passwordfile='exclusive'

*.sessions=1105

*.sga_target=1610612736

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS01'

复制代码

5、主库创建pfile和passwordfile后，拷贝到备库的相同路径下

注意，使用orapwd创建口令文件时，一定要使用ignorecase选项。否则启动备库后，可能会在alert文件中提示ORA-16191错误。这个问题有可能是Oracle 11g中的一个bug，我被他困扰折磨了老半天...

[oracle@localhost data]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ orapwd file=orapwPRIMARY password=sys force=y ignorecase=y

[oracle@localhost dbs]$ ls

hc_PRIMARY.dat  init.ora         lkPRAMARY  lkTDB1  orapwPRIMARY      orapwtdb1          spfiletdb1.ora

hc_tdb1.dat     initPRIMARY.ora  lkPRIMARY  lkTEST  orapwPRIMARY.bak  spfilePRIMARY.ora

[oracle@localhost dbs]$ scp -r orapwPRIMARY initPRIMARY.ora 172.26.29.4:/u01/app/oracle/product/11.2.0/db_1/dbs

oracle@172.26.29.4's password:

orapwPRIMARY                                                                               100% 1536     1.5KB/s   00:00

initPRIMARY.ora                                                                            100% 1496     1.5KB/s   00:00

复制代码

5、备库获取数据文件，初始化参数文件和口令文件后，对初始化参数文件进行相应修改

需要修改的参数如下：

db_unique_name=STANDBY

log_archive_dest_2='service=primary async valid_for=(all_logfiles,all_roles) db_unique_name=PRIMARY'

fal_server=primary

fal_client=standby

control_files='/u01/data/primary/primary/standby.ctl' 此处使用standby controlfile

简单来说，就是将这几个参数依据备库，与主库做一个映像般的调换

[oracle@tam dbs]$ cat initPRIMARY.ora

PRIMARY.__db_cache_size=1191182336

PRIMARY.__java_pool_size=16777216

PRIMARY.__large_pool_size=16777216

PRIMARY.__oracle_base='/u01/app'#ORACLE_BASE set from environment

PRIMARY.__pga_aggregate_target=1677721600

PRIMARY.__sga_target=1610612736

PRIMARY.__shared_io_pool_size=0

PRIMARY.__shared_pool_size=369098752

PRIMARY.__streams_pool_size=0

*.compatible='11.2.0'

*.control_files='/u01/data/primary/primary/standby.ctl'

*.core_dump_dest='/u01/app/oracle/admin/primary/cdump'

*.db_block_size=8192

*.db_name='PRIMARY'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=53687091200

*.db_unique_name='STANDBY'

*.fal_client='STANDBY'

*.fal_server='PRIMARY'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/u01/data/primary/arc'

*.log_archive_dest_2='service=primary async valid_for=(all_logfiles,all_roles) db_unique_name=PRIMARY'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=10

*.log_file_name_convert='/u01/data/primary/arc','/u01/data/primary/arc'

*.nls_date_format='YYYY-MM-DD hh24:mi:ss'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=1669332992

*.processes=1000

*.remote_login_passwordfile='exclusive'

*.sessions=1105

*.sga_target=1610612736

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS01'

复制代码

6、配置主库和备库中的监听服务和tnsnames.ora

主库：注意listener.ora文件中的SID_NAME是大小写敏感的，笔者因为忽略了这个问题，导致配置监听和启动监听成功后，一致不能远程登录...

[oracle@localhost admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0.5/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = PRIMARY)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(GLOBAL_DBNAME = primary)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.29.2)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

[oracle@localhost admin]$ cat tnsnames.ora

names.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STANDBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.29.4)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

复制代码

备库

[oracle@tam admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0.5/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = PRIMARY)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(GLOBAL_DBNAME = primary)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.29.4)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

[oracle@tam admin]$ cat tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.29.2)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

)

复制代码

配置完成后，尝试从两台服务器以sysdba身份互相访问

主库：

[oracle@localhost admin]$ sqlplus sys/sys@standby as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 22 10:16:26 2012

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

Connected to an idle instance.

复制代码

备库：

[oracle@tam admin]$ sqlplus sys/sys@primary as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 22 10:15:17 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

复制代码

7、启动备库

[oracle@tam dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 22 10:19:00 2012

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

Connected to an idle instance.

SYS@PRIMARY>startup nomount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2213776 bytes

Variable Size             402655344 bytes

Database Buffers         1191182336 bytes

Redo Buffers                7360512 bytes

SYS@PRIMARY>alter database mount standby database;

Database altered.

SYS@PRIMARY>alter database recover managed standby database disconnect from session;

Database altered.

复制代码

8、验证

主库中创建一个表，并插入若凡数据后，备库中能够同步出这些数据，则认为DG实施成功

主库：

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 22 10:28:14 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PRIMARY>create table test(id number);

Table created.

SYS@PRIMARY>for i in 1..10 loop

SP2-0734: unknown command beginning "for i in 1..." - rest of line ignored.

SYS@PRIMARY>begin

2  for i in 1..10 loop

3  insert into test values(i);

4  end loop;

5  commit;

6  end;

7  /

PL/SQL procedure successfully completed.

SYS@PRIMARY>select * from test;

ID

----------

1

2

3

4

5

6

7

8

9

10

10 rows selected.

SYS@PRIMARY>alter system switch logfile;

System altered.

SYS@PRIMARY>/

System altered.

复制代码

备库：

SYS@PRIMARY>select name,database_role from v$database;

NAME      DATABASE_ROLE

--------- ----------------

PRIMARY   PHYSICAL STANDBY

SYS@PRIMARY>select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME   NEXT_TIME

---------- ------------ ------------

15 20-FEB-12    20-FEB-12

8 20-FEB-12    20-FEB-12

10 20-FEB-12    20-FEB-12

9 20-FEB-12    20-FEB-12

11 20-FEB-12    20-FEB-12

13 20-FEB-12    20-FEB-12

7 20-FEB-12    20-FEB-12

14 20-FEB-12    20-FEB-12

12 20-FEB-12    20-FEB-12

18 21-FEB-12    21-FEB-12

20 21-FEB-12    21-FEB-12

......

SYS@PRIMARY>alter database recover managed standby database cancel;

Database altered.

SYS@PRIMARY>alter database open read only;

Database altered.

SYS@PRIMARY>select * from test;

ID

----------

1

2

3

4

5

6

7

8

9

10

10 rows selected.

复制代码

至此，配置完毕

总结

想要玩好oracle的Data Guard、RAC、Golden Gate等高端软件，主要在于细心与耐心。

所谓细心，就是认真读取文档，认真配置各项参数和文件；所谓耐心，就是出现错误后，要认真预读alert log好trace文件，找到错误原因，即使去网上求助，一项一项排除。笔者首次不是DG时，确实遇到不少问题，就是靠逐步啃日志文件解决的

摘自 所好者道也