Oracle 9i Dataguard Max Performance Configuration for freshman in DBA Team

因为是给公司新人看的，所以比较直接简单。

[@more@]

一一、Primary Database上要确认的事情。

1． Enable force logging

SQL> alter database force logging;

Database altered.

2．SQL> show parameter log_archive_dest_1

NAME TYPE

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

VALUE

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

log_archive_dest_1 string

LOCATION=/u01/archive/test

log_archive_dest_10 string

二二、开始创建Physical Standby Database

1． Primary Database平时使用RMAN作全备，就使用RMAN作DB 复制。

I． RMAN备全库和Archived Log

Script:

run{

allocate channel C1 type disk maxpiecesize = 8G;

sql 'alter system archive log current';

backup database include current controlfile

filesperset = 8

format '/u03nfs/test01/%d_%U'

tag FULL_TEST01;

backup current controlfile for standby format'/u03nfs/test01/%d_%U';

release channel C1;

}

run{

allocate channel C2 type disk maxpiecesize = 8G;

sql 'alter system archive log current';

backup

filesperset 16

format '/u03nfs/test01/%d_%U'

tag ARCHIVED_LOG_TEST01

(archivelog all delete input);

release channel C2;

}

备到NFS Server上去后，因为Standby Server也是用同一Directory去mount，就省去Copy到Standby server。

II． 给Standby DB准备pfile.

SQL> create pfile='/u03nfs/test01/init_test01.ora' from spfile;

File created.

Cp /u03nfs/test01/init_test01.ora $ORACLE_HOME/dbs/inittest01st.ora

然后开始改Init parameters以适合standby database的需要。

可能需要修改的参数（与原Server位置不一致则需修改）：

Cdump, bdump, udump, controlfiles，log_archive_dest_1, instance_name

需增加的参数：

Standby_file_management=AUTO, standby_archive_dest.

如果准备在standby server上放置datafile and redo的位置与Primary Database不一致，那么需加上db_file_name_convert 和log_file_name_convert两个参数。

由于本次的情况是A,B server 是互作DG，且两机的原有db_name相同。因此standby的2个instance的parameter都需使用Lock_name_space = instance_name，且需设定service_names = instance_name

III． 准备Standby database的Password file.

orapwd file=orapwtest01st password=XXXXXX entries=2

IV． 准备listerner和tnsname.ora

V． 启用dead connect detection.

在sqlnet.ora加上sqlnet.expire_time=2

VI． 将standby database启动到nomount mode

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Nov 21 15:17:11 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 655851416 bytes

Fixed Size 741272 bytes

Variable Size 251658240 bytes

Database Buffers 402653184 bytes

Redo Buffers 798720 bytes

VII． RMAN duplication.

在standby server上

$ rman target sys/XXXXXX@test01

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TEST (DBID=3568843179)

RMAN> connect auxiliary /

connected to auxiliary database: test (not mounted)

RMAN> duplicate target database for standby dorecover nofilenamecheck;

.

.

.

media recovery complete

Finished recover at 22-NOV-07

Finished Duplicate Db at 22-NOV-07

VIII． 开始Archive Log to Standby Database

SQL> create spfile from pfile;

File created.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 655851416 bytes

Fixed Size 741272 bytes

Variable Size 251658240 bytes

Database Buffers 402653184 bytes

Redo Buffers 798720 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.---------------------初始化Log Apply Service。

Primary Database上：

SQL> alter system set log_archive_dest_2='SERVICE=test01st' scope=both;

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

SQL> alter system archive log current;

System altered.

IX． 检查Physical Standby Database

Standby database上：

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

658 21-NOV-07 21-NOV-07

659 21-NOV-07 21-NOV-07

660 21-NOV-07 22-NOV-07

661 22-NOV-07 22-NOV-07

662 22-NOV-07 22-NOV-07

663 22-NOV-07 22-NOV-07

6 rows selected.

Primary database上：

SQL> alter system archive log current;

System altered.

回到standby database上察看：

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

658 21-NOV-07 21-NOV-07

659 21-NOV-07 21-NOV-07

660 21-NOV-07 22-NOV-07

661 22-NOV-07 22-NOV-07

662 22-NOV-07 22-NOV-07

663 22-NOV-07 22-NOV-07

664 22-NOV-07 22-NOV-07

7 rows selected.

已经把log归档过去了。

查看是否apply redo：

SQL> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APPLIED

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

658 YES

659 YES

660 YES

661 YES

662 YES

663 YES

664 YES

7 rows selected.

已经apply。

Physical Standby Database的架设到现在完毕。

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

转载于:http://blog.itpub.net/10856805/viewspace-995612/