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

一一、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的架設到現在完畢。