ORACLE10G DATAGUARD配置筆記

ORACLE10G DATAGUARD配置筆記

環境：

OS:RHL4+ORACLE10G10.2.0.1

IP:172.17.61.160(primary) 172.17.61.161(standby)

ORACLE_SID:orcl

ORACLE_HOME:/oracle/product/10.2.0

一、配置standby database為MAXIMIZE PERFORMANCE模式

二、轉換模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION

三、主庫和備庫的switchover.

1.設定主庫為force logging

SQL> alter database force logging;

2.設定主庫為歸檔模式：

SQL> archive log list

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

3.檢查主機是否有密碼檔案,如沒有需建立

orapwd file='/oracle/product/10.2.0/dbs/orawdorcl.ora' password=sys entries=5

4.為主資料庫添加"備用聯機日志檔案"

SQL> alter database add standby logfile group 4

('/oracle/oradata/orcl/redo04.log') size 50m;

SQL> alter database add standby logfile group 5

('/oracle/oradata/orcl/redo05.log') size 50m;

SQL> alter database add standby logfile group 6

('/oracle/oradata/orcl/redo06.log') size 50m;

SQL> alter database add standby logfile group 7

('/oracle/oradata/orcl/redo07.log') size 50m;

5.修改主庫參數檔案

SQL> create pfile='/oracle/admin/orcl/pfile/init.ora' from spfile;

orcl.__db_cache_size=100663296

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=54525952

orcl.__streams_pool_size=0

*.audit_file_dest='/oracle/admin/orcl/adump'

*.background_dump_dest='/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_format='%T%S%r.ARC'

*.log_archive_max_processes=3

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/admin/orcl/udump'

#add below parameter for standy database

*.DB_UNIQUE_NAME='10gpri'

*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'

*.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'

*.log_archive_dest_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby'

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='10gstandby'

*.FAL_CLIENT='10gpri'

6.主庫用PFILE建立SPFILE

[oracle@host160 pfile]$ sqlplus '/ as sysdba'

SQL> create spfile from pfile='/oracle/admin/orcl/pfile/init.ora';

7.建立備用庫的控制檔案

SQL> alter database create standby controlfile as '/tmp/standby_ctl01.ctl';

8.配置主資料庫listener及tnsnames

[oracle@host160 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /oracle/product/10.2.0)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

#加1522端口供以後做switchover

SID_LIST_LISTENER1 =

LISTENER1 =

(ADDRESS = (PROTOCOL = TCP)(HOST = host160)(PORT = 1522))

[oracle@host160 admin]$ cat tnsnames.ora

#1521和1522端口都能連上主機和備機，這樣在做switchover時就不需要改這裡的設定了

10gpri =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.161)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1522))

(CONNECT_DATA =

(SID = orcl)

10gstandby =

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

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

9.複制檔案到備機

[oracle@host161 ~]cd /oracle/oradata/orcl

[oracle@host161 orcl]$rcp host160:/oracle/oradata/orcl/*.dbf .

[oracle@host161 orcl]$rcp host160:/tmp/standby_ctl01.ctl .

[oracle@host161 orcl]$mv standby_ctl01.ctl control01.ctl

[oracle@host161 orcl]$cp control01.ctl control02.ctl

[oracle@host161 orcl]$cp control01.ctl control03.ctl

10.複制并修改備機的參數檔案

[oracle@host161 ~]cd /oracle/admin/orcl/pfile

[oracle@host161 pfile]$rcp host160:/oracle/admin/orcl/pfile/init.ora .

修改為如下：

*.DB_UNIQUE_NAME='10gstandby'

*.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'

*.log_archive_dest_2='SERVICE=10gpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri'

*.FAL_SERVER='10gpri'

*.FAL_CLIENT='10gstandby'

11.生成備用庫密碼檔案

12.修改備機的listener及tnsnames

[oracle@host161 admin]$ cat listener.ora

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

#加一個1522的端口供以後做switchover

(ADDRESS = (PROTOCOL = TCP)(HOST = host161)(PORT = 1522))

[oracle@host161 admin]$ cat tnsnames.ora

13.測試主備之間網絡連通

[oracle@host160 admin]$ lsnrctl start

[oracle@host160 admin]$ tnsping 10gstandby

[oracle@host161 admin]$ lsnrctl start

[oracle@host161 admin]$ tnsping 10gpri

14.打開備庫

SQL> startup mount pfile='/oracle/admin/orcl/pfile/init.ora';

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

15.打開主庫

SQL> startup

16.測試是否OK

主庫：

SQL> alter system switch logfile;

從庫：

SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;

FIRST_TIME NEXT_TIME APP SEQUENCE#

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

2007-10-16 11:39:33 2007-10-16 14:16:39 YES 5

2007-10-16 14:16:39 2007-10-16 14:21:06 YES 6

1.在備機上：

SQL> shutdown immediate;

SQL> startup mount；

2 ('/oracle/oradata/orcl/redo04.log') size 50m;

2 ('/oracle/oradata/orcl/redo05.log') size 50m;

2 ('/oracle/oradata/orcl/redo06.log') size 50m;

2 ('/oracle/oradata/orcl/redo07.log') size 50m;

2.在主機上

SQL> alter system set log_archive_dest_2='SERVICE=10gstandby LGWR SYNC AFFIRM 2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby';

SQL>alter database set standby database to maximize protection;

SQL>alter database open;

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PROTECTION

3.在備機上

SQL> recover managed standby database disconnect from session;

4.測試一下：

在主機上執行SQL> alte system switch logfile;

在備機上檢視v$standby_log視圖

SQL> select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;

GROUP# THREAD# SEQUENCE# USED ARC STATUS

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

4 1 36 59392 YES ACTIVE

5 1 0 512 NO UNASSIGNED

6 0 0 512 YES UNASSIGNED

7 0 0 512 YES UNASSIGNED

MAXIMIZE PROTECTION和MAXIMIZE AVAILABILITY模式下，備機不能先關閉，會出現如下錯誤

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

正确的開關機順序是：

關機：先關主機，後關備機

開機：先開備機，後開主機

注意：Swithover時隻能先從Primary切到Standby，再從Standby切到Primary.

1.準備原主庫是否有standby redo log，上面1.4已建好了。

2.準備主庫和備庫的參數檔案，最好就是将兩個資料庫的參數檔案互換，在兩台機器上同時保留主庫和備庫的參數檔案。

[oracle@host161 /]$ cd /oracle/admin/orcl/pfile

[oracle@host161 pfile]rcp host160:/oracle/admin/orcl/pfile/init.ora initprimary.ora

[oracle@host161 pfile]rcp init.ora host160:/oracle/admin/orcl/pfile/initstandby.ora

3.從primary 切換到standby

connect / as sysdba

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown

SQL> startup mount pfile=/oracle/admin/orcl/pfile/initstandby.ora;

SQL> recover managed standby database disconnect;

4.啟動新備庫端的Listener (port=1522)

[oracle@host160 admin]$ lsnrctl stop

[oracle@host160 admin]$ lsnrctl start LISTENER1

5.從standby 切換到primary

SQL> alter database commit to switchover to primary;

SQL> startup pfile=/oracle/admin/orcl/pfile/initprimary.ora

6.啟動新主庫端的Listener (port=1522)

[oracle@host161 admin]$ lsnrctl stop

[oracle@host161 admin]$ lsnrctl start LISTENER1

7.OK

按照你上的步驟，怎麼在mode轉換時出現：

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 2550136832 bytes

Fixed Size 1980808 bytes

Variable Size 587204216 bytes

Database Buffers 1946157056 bytes

Redo Buffers 14794752 bytes

Database mounted.

SQL> alter system set log_archive_dest_2='SERVICE=10gstandby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby';

System altered.

SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

本文轉自 jxwpx 51CTO部落格，原文連結：http://blog.51cto.com/jxwpx/205458，如需轉載請自行聯系原作者