学步园

2. 设置primary库force logging.

PRIMARY>alter database force logging;

Database altered.

3. 创建密码文件

[oracle@PRIMARY]orapwd file=orapwprimary password=xxxxxxx;

4. 修改有关data guard的参数

PRIMARY> show parameter log_archive_config

PRIMARY> alter system set
log_archive_config='DG_CONFIG=(zxdb,zxdbdg1)';

System altered.

PRIMARY>alter system set
log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/zxdb/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zxdb';

System altered.

5. 添加standby log group

PRIMARY>alter database add standby logfile group
4('/u01/app/oracle/oradata/zxdb/standbylog/group_4_1.log','/u01/app/oracle/oradata/zxdb/standbylog/group_4_2.log')
size 30m;

Database altered.

PRIMARY>alter database add standby logfile group
5('/u01/app/oracle/oradata/zxdb/standbylog/group_5_1.log','/u01/app/oracle/oradata/zxdb/standbylog/group_5_2.log')
size 30m;

Database altered.

创建成功后，在v$standby_log可以看到新加入的两个standby logfile的group，
在v$log里查询可以看到新添加进去的4个standby logfile。

6. 修改参数

fal_server zxdbdg

fal_client zxdb

standby_file_management auto

log_archive_dest_state_2 enable

log_archive_max_processes

7. 创建standby控制文件

PRIMARY> alter database create standby controlfile as
'/u01/app/oracle/standby.ctl';

8. 导出spfile为pfile

PRIMARY> create pfile='/u01/app/oracle/init.ora' from spfile;

9. 复制数据库，可以物理拷贝，也可以用RMAN。

10. scp拷贝pfile和standby controlfile到备考机器

scp /u01/app/oracle/standby.ctl oracle:dg
/u01/app/oracle/oradata/zxdb/controlfile/control1.ctl

scp /u01/app/oracle/init.ora oracle:dg
/u01/app/oracle/product/10.2.0/db_1/dbs/initzxdb.ora

11. 修改pfile

db_unique_name zxdbdg

control_files /u01/app/oracle/oradata/zxdb/controlfile/standby.ctl

log_archive_dest_1 LOCATION=/u01/app/oracle/oradata/zxdb/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zxdbdg

log_archive_dest_2 SERVICE=zxdb LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zxdb

fal_server zxdb

fal_client zxdbdg

12. 在备库上pfile为spfile

STANDBY> create
spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfilezxdb.ora' from
pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initzxdb.ora'

13. 在主机上建立tns service zxdb和zxdbdg这里是上面fal指定的

zxdb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = zxdb)

)

)

zxdbdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = zxdb)

)

)

14 在备课上配置同上的tnsname

zxdb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = zxdb)

)

)

zxdbdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = zxdb)

)

)

15. 启动备库

STANDBY> start mount

16. 应用redo

STANDBY> recover managed standby database

这里注意，这个事handle的。所以关掉session的话，会停止住。

一般我们都是用disconnect来解决这个问题

STANDBY> recover managed standby database disconnect from session

测试dg

在主库上insert data并且commit

ID NAME

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

1 test1

2 test2

3 test3

4 test4

查看主库v$archived_log

PRIMARY> select RECID, STAMP, NAME, DEST_ID, SEQUENCE#,
STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log;

11 712946684
/u01/app/oracle/oradata/zxdb/archivelog/1_11_712892825.dbf
1 11 NO YES NO

12 712946692 zxdbdg1
2 11 YES
YES YES

13 712948030
/u01/app/oracle/oradata/zxdb/archivelog/1_12_712892825.dbf
1 12 NO YES NO

14 712948031 zxdbdg1
2 12 YES
YES YES

在备库上查看v$archived_log

STANDBY> create table test.testobject as select * from
dba_tables;

STANDBY> alter database open read only;

SQL> select RECID, STAMP, NAME, DEST_ID, SEQUENCE#, STANDBY_DEST,
ARCHIVED, APPLIED from v$archived_log;

RECID STAMP NAME
DEST_ID SEQUENCE# STANDBY_DEST
ARCHIVED APPLIED

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

1 712946690
/u01/app/oracle/oradata/zxdb/archivelog/1_11_712892825.dbf
1 11 NO YES YES

2 712948028
/u01/app/oracle/oradata/zxdb/archivelog/1_12_712892825.dbf
2 12 NO YES YES

STANDBY> select RECID, STAMP, NAME, DEST_ID, SEQUENCE#,
STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log;

ID NAME

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

1 test1

2 test2

3 test3

4 test4

这里已经成功了apply到备库上去了。

实验2

PRIMARY> create table test.testobject as select * from
dba_tables;

PRIMARY> commit

观察alert日志，没有归档的提示信息

redo日志没有满，所以没有切换

PRIMARY> insert into test.testobject select * from
test.testobject;

多执行了几次，在alert日志，出现归档的信息。

用上面的方法查询，和主库一致。

STANDBY>select process,client_process,sequence#,status from
v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

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

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

ARCH ARCH 17 CLOSING

ARCH ARCH 0 CONNECTED

RFS N/A 0 IDLE

MRP0 N/A 18 WAIT_FOR_LOG

RFS LGWR 19 IDLE

RFS UNKNOWN 0 IDLE