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，如需转载请自行联系原作者