Oracle DataGuard创建实验（成功案例）

前段时间做了一次创建Oracle DataGuard的实验，但是并不成功，这段时间经过总结和整理，终于将这个实验圆满完成，特记录如下：

Oracle DataGuard实验

1、 注意修改Primary和Standby数据库服务器Oracle用户的配置文件中的ORACLE_SID。

2、 给Primary和Standby数据库服务器创建数据库（DBCA），注意在创建的过程中让Primary和Standby数据库的全局服务名相同，但SID各有不同。其实这样安装的目的仅仅是为了让Primary和Standby数据库都具有相同的目录结构。

Global Database Name SID DB_UNIQUE_NAME

Primary： oracleDG primDB primDB

Standby： oracleDG stanDB stanDB

3、 开启Primary和Standby数据库的归档及强制归档属性并查看确认：

SQL> alter database force logging;

Database altered.

SQL> SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

DBID NAME LOG_MODE FOR

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

3215112039 ORACLEDG ARCHIVELOG YES

4、 准备Primary和Standby的初始化参数文件

1) Primary初始化参数

SQL> create pfile='$ORACLE_HOME/dbs/initprimDB.ora' from spfile;

File created.

SQL> shutdown immediate;

SQL> exit

[oracle@OraLinux dbs]$ vi initprimDB.ora

//在其中添加如下内容：

DB_UNIQUE_NAME=primDB

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primDB,stanDB)'

LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/flash_recovery_area/ORACLEDG/archivelog/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primDB'

LOG_ARCHIVE_DEST_2='SERVICE=stanDB LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stanDB'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#--------for standby role switch

FAL_SERVER=primDB

FAL_CLIENT=stanDB

STANDBY_FILE_MANAGEMENT=AUTO

//注意两个问题：1、…/flash_recovery_area/ORACLEDG/archivelog目录可能需要手动创建；2、SERVICE=stanDB中的stanDB是在本机上配置的数据库服务名用于访问Standby数据库。

[oracle@OraLinux dbs]$ sqlplus / as sysdba

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initprimDB.ora';

File created.

SQL> startup //启动数据库的目的是为了验证初始化参数文件配置基本正确

……

Database mounted.

Database opened.

2) Standby初始化参数

SQL> create pfile='$ORACLE_HOME/dbs/initstanDB.ora' from spfile;

File created.

SQL> shutdown immediate;

SQL> exit

[oracle@OraLinuxAux dbs]$ vi initstanDB.ora

//在其中添加如下内容：

DB_UNIQUE_NAME=stanDB

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primDB,stanDB)'

LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/flash_recovery_area/ORACLEDG/archivelog/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stanDB'

LOG_ARCHIVE_DEST_2='SERVICE=primDB LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primDB'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#--------for standby role switch

FAL_SERVER=stanDB

FAL_CLIENT=primDB

STANDBY_FILE_MANAGEMENT=AUTO

//注意两个问题：1、…/flash_recovery_area/ORACLEDG/archivelog目录可能需要手动创建；2、SERVICE=primDB中的primDB是在本机上配置的数据库服务名用于访问Primary数据库。

[oracle@OraLinuxAux dbs]$ sqlplus / as sysdba

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initstanDB.ora';

File created.

SQL> startup //启动数据库的目的是为了验证初始化参数文件配置基本正确

……

Database mounted.

Database opened.

5、 关闭Standby数据库，并删除所有控制文件、数据文件以及在线日志文件。

6、 在Primary数据库上创建Standby数据库要用到的控制文件：

SQL> alter database create standby controlfile as '/oracle/app/oracle/bk/control01.ctl';

Database altered.

//并复制该文件为另外两份

SQL> host cp /oracle/app/oracle/bk/control01.ctl /oracle/app/oracle/bk/control02.ctl

SQL> host cp /oracle/app/oracle/bk/control01.ctl /oracle/app/oracle/bk/control03.ctl

7、 关闭Primary数据库，并复制/oracle/app/oracle/bk中的控制文件副本、Primary中的所有数据文件和在线日志文件到Standby数据库中相关位置。注意，复制完成后要修改这些文件Standby数据库上的所属权限。

8、 启动Primary和Standby数据库到mount状态，并创建standby redo log：

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/oracleDG/standby_redo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/oracleDG/standby_redo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/oracleDG/standby_redo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/oracle/app/oracle/oradata/oracleDG/standby_redo04.log' size 50m;

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS

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

4 0 0 YES UNASSIGNED

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

7 0 0 YES UNASSIGNED

//注意standby日志的大小应该和在线日志的大小相同，对于何时应该为主备数据库添加standby日志，在Oracle DataGuard的官方文档中有如下描述：

A standby redo log is required for the maximum protection and maximum availability

modes and the LGWR ASYNC transport mode is recommended for all databases. Data

Guard can recover and apply more redo data from a standby redo log than from

archived redo log files alone.

9、 在Primary和Standby数据库上配置Oracle Net：

[oracle@OraLinux oracleDG]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

……

stanDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stanDB)

)

)

……

[oracle@OraLinuxAux oracleDG]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

……

primDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primDB)

)

)

……

配置完成后重启主备数据库的监听程序，并分别在主备数据库上测试连接对方，注意：如果没有经过测试，而主备之间不能互通的话，则肯定无法实现日志的传递这样最基础的功能。其中SERVICE_NAME参数内容一定要分别登录到主备数据库上去查看，否则很可能出问题。

Primary：

[oracle@OraLinux oracleDG]$ tnsping stanDB

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-JUL-2011 13:38:19

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:

/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OraLinuxAux)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stanDB)))

OK (20 msec)

[oracle@OraLinux oracleDG]$ sqlplus sys/sys@stanDB as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 29 13:38:27 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>

Standby：

[oracle@OraLinuxAux oracleDG]$ tnsping primDB

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-JUL-2011 13:38:49

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:

/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OraLinux)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primDB)))

OK (20 msec)

[oracle@OraLinuxAux oracleDG]$ sqlplus sys/sys@primDB as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 29 13:39:00 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL>

10、 在Standby数据库上启动redo应用：

SQL> alter database recover managed standby database disconnect from session;

Database altered.

11、 启动primary数据库到Open状态并测试：

Primary:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP

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

1 NO

2 NO

Standby:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

no rows selected

Primary:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP

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

1 NO

1 NO

2 NO

2 YES

3 NO

3 NO

6 rows selected.

Standby:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

no rows selected

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP

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

1 NO

2 YES

3 YES

Primary:

SQL> select name from v$datafile;

NAME

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

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

Standby:

SQL> select name from v$datafile;

NAME

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

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

Primary:

SQL> create tablespace testdg datafile '/oracle/app/oracle/oradata/oracleDG/testdg.dbf' size 1M;

Tablespace created.

SQL> select name from v$datafile;

NAME

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

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdg.dbf

Standby:

SQL> select name from v$datafile;

NAME

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

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

//为什么这时Standby数据库没有同步呢？继续看

Primary:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

//手动进行归档，并触发LGWR，从而向Standby数据库传递redo日志

Standby:

SQL> select name from v$datafile;

NAME

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

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdg.dbf

//可见，此时Standby数据库得到同步

完成！

后续几个思考：

1、standby 日志一定需要配置吗？

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14338195/viewspace-1053187/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14338195/viewspace-1053187/