实验：搭建dataguard

1、打开主库zwb强制写日志模式

SQL> alter database force logging; SQL> select force_logging from v$database;

FOR

--- YES

2、建立备库数据文件放置目录 [oracle@testsvr oracle]$ mkdir stdby

3、做首次全同步数据库

SQL> alter database begin backup;

SQL> select name from v$datafile;

NAME

-------------------------------------------------------------------------------- /oracle/oradata/zwb/system01.dbf /oracle/oradata/zwb/undotbs01.dbf /oracle/oradata/zwb/sysaux01.dbf /oracle/oradata/zwb/users01.dbf /oracle/oradata/zwb/example01.dbf /oracle/oradata/zwb/rmantbs01.dbf

6 rows selected.

[oracle@testsvr zwb]$ cp *.dbf /oracle/stdby

SQL> alter database end backup;   

4、配置网络 [oracle@testsvr ~]$ cd $ORACLE_HOME/network/admin

配置listener.ora:

SID_LIST_LISTENER =

  (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /oracle/app/product/10.2.0/db_1)       (PROGRAM = extproc)     )       (SID_DESC =       (SID_NAME = zwb)       (ORACLE_HOME = /oracle/app/product/10.2.0/db_1)      (global_dbname=zwb)       )      (SID_DESC =       (SID_NAME = stdby)       (ORACLE_HOME = /oracle/app/product/10.2.0/db_1)      (global_dbname=stdby)     )   )

LISTENER =

  (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = testsvr)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )   )

配完后重启一下监听

[oracle@testsvr admin]$ lsnrctl stop [oracle@testsvr admin]$ lsnrctl start

配置tnsnames.ora:

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   )

ZWB =

  (DESCRIPTION =     (ADDRESS = (PROTOCOL = tcp)(HOST = 10.10.10.88)(PORT = 1521))     (CONNECT_DATA =       (SERVICE_NAME = zwb)     )   )

bxtest =

  (DESCRIPTION =     (ADDRESS = (PROTOCOL = tcp)(HOST = 10.10.10.88)(PORT = 1521))     (CONNECT_DATA =       (SERVICE_NAME = bxtest)     )   )

stdby =

  (DESCRIPTION =     (ADDRESS = (PROTOCOL = tcp)(HOST = 10.10.10.88)(PORT = 1521))     (CONNECT_DATA =       (SERVICE_NAME = stdby)     )   )

5、测试下能否ping通stdby [oracle@testsvr admin]$ tnsping stdby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 10-JUN-2012 20:07:20

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 10.10.10.88)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = stdby))) OK (10 msec)

6、创建备库目录 [oracle@testsvr admin]$ cd $ORACLE_BASE/admin [oracle@testsvr admin]$ mkdir -p stdby/bdump [oracle@testsvr admin]$ mkdir -p stdby/cdump [oracle@testsvr admin]$ mkdir -p stdby/adump [oracle@testsvr admin]$ mkdir -p stdby/udump [oracle@testsvr stdby]$ mkdir -p /oracle/stdby/archlog  /*创建备库的归档路径*/

7、配置备库密码文件

[oracle@testsvr admin]$ cd $ORACLE_HOME/dbs [oracle@testsvr dbs]$ cp orapwzwb orapwstdby

8、测试连到备库 [oracle@testsvr dbs]$ sqlplus as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 10 20:15:29 2012

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

Connected to an idle instance.

9、创建备库参数文件模板

[oracle@testsvr dbs]$ cd $ORACLE_HOME/dbs [oracle@testsvr dbs]$ cp initzwb.ora initstdby.ora

10、主库zwb参数文件添加如下参数

[oracle@testsvr dbs]$ vi initzwb.ora

standby_archive_dest='/oracle/archlog'

standby_file_management=auto db_file_name_convert='/oracle/stdby','/oracle/oradata/zwb'                log_file_name_convert='/oracle/stdby','/oracle/oradata/zwb' log_archive_config='DG_CONFIG=(zwb,stdby)'

注意：log_file_name_convert=('AAA','BBB' )

AAA是日志文件的源目录，BBB是日志文件目标目录

10、主库重新创建spfile，重启生效

SQL> create spfile from pfile; SQL> startup

11、测试一下参数是否生效

SQL> show parameter db_file_name_convert;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------ db_file_name_convert                 string      /oracle/stdby, /oracle/zwb

12、编辑备库stdby参数文件 [oracle@testsvr dbs]$ cd $ORACLE_HOME/dbs

$vi initstdby.ora

将zwb替换成stdby，注意db_name参数不需要替换，并添加或修改如下参数

standby_archive_dest='/oracle/stdby/archlog'

standby_file_management=auto db_file_name_convert='/oracle/oradata/zwb','/oracle/stdby' log_file_name_convert='/oracle/oradata/zwb','/oracle/stdby' db_unique_name='stdby' log_archive_config='DG_CONFIG=(zwb,stdby)'

13、生成备库spfile SQL> create spfile from pfile;

14、启动备库 [oracle@testsvr dbs]$ export ORACLE_SID=stdby [oracle@testsvr dbs]$ sqlplus '/as sysdba' SQL> startup nomount ORACLE instance started.

15、连到主库，从主库zwb创建standby controlfile [oracle@testsvr dbs]$ export ORACLE_SID=zwb [oracle@testsvr dbs]$ sqlplus '/as sysdba' SQL> alter database create standby controlfile as '/oracle/stdby/control01.ctl'；

16、切换备库并mount

[oracle@testsvr dbs]$ export ORACLE_SID=stdby [oracle@testsvr dbs]$ sqlplus '/as sysdba' SQL> alter database mount；

17、备库设置传输参数

SQL> alter system set log_archive_dest_1='location=/oracle/stdby/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby'; SQL> alter system set log_archive_dest_2='SERVICE=zwb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zwb';

18、主库设置传输参数

[oracle@testsvr dbs]$ export ORACLE_SID=zwb [oracle@testsvr dbs]$ sqlplus '/as sysdba'

SQL> alter system set log_archive_dest_2='SERVICE=stdby  VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/oracle/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zwb';

System altered.

19、测试主库切个归档看备库是否传到 主库： SQL> alter system switch logfile;

System altered.

备库收到日志：

[oracle@testsvr archlog]$ pwd /oracle/stdby/archlog [oracle@testsvr archlog]$ ls -rtl total 792 -rw-r-----  1 oracle oinstall 803328 Jun 10 21:33 1_24_784249064.dbf

20、备库启动之前手动应用归档

SQL> recover standby database;      ---缺少的话直接考一下 如果正常，启动后台应用进程mrp SQL> alter database recover managed standby database disconnect from session; Database altered.

实验成功：搭建dataguard成功