ORACLE10G DATAGUARD配置笔记

精选转载

jxwpx 2009-09-25 14:51:33

文章标签 oracle 职场休闲 文章分类 Oracle 数据库

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.一、配置standby database为MAXIMIZE PERFORMANCE模式 1.设置主库为force logging

SQL> alter database force logging;2.设置主库为归档模式：

SQL> archive log list

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list3.检查主机是否有口令文件,如没有需建立

orapwd file='/oracle/product/10.2.0/dbs/orawdorcl.ora' password=sys entries=54.为主数据库添加"备用联机日志文件"

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.oraSID_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 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /oracle/product/10.2.0)

(SID_NAME = orcl)

)

)LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)[oracle@host160 admin]$ cat tnsnames.ora

#1521和1522端口都能连上主机和备机，这样在做switchover时就不需要改这里的设置了

10gpri =

(DESCRIPTION =

(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 =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(CONNECT_DATA =

(SID = orcl)

)

)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.ctl10.复制并修改备机的参数文件

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

[oracle@host161 pfile]$rcp host160:/oracle/admin/orcl/pfile/init.ora .修改为如下：

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'*.DB_UNIQUE_NAME='10gstandby'

*.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=10gstandby'

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

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='10gpri'

*.FAL_CLIENT='10gstandby'11.生成备用库密码文件

orapwd file='/oracle/product/10.2.0/dbs/orawdorcl.ora' password=sys entries=512.修改备机的listener及tnsnames

[oracle@host161 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 = host161)(PORT = 1521))

)

)#加一个1522的端口供以后做switchover

SID_LIST_LISTENER1 =

(SID_LIST =

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /oracle/product/10.2.0)

(SID_NAME = orcl)

)

)LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

[oracle@host161 admin]$ cat tnsnames.ora

10gpri =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(CONNECT_DATA =

(SID = orcl)

)

10gstandby =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(CONNECT_DATA =

(SID = orcl)

)

)13.测试主备之间网络连通

[oracle@host160 admin]$ lsnrctl start

[oracle@host160 admin]$ tnsping 10gstandby

[oracle@host161 admin]$ lsnrctl start

[oracle@host161 admin]$ tnsping 10gpri14.打开备库

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

SQL> create spfile from 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

二、转换模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION1.在备机上：

SQL> shutdown immediate;

SQL> startup mount；

SQL> alter database add standby logfile group 4

2 ('/oracle/oradata/orcl/redo04.log') size 50m;SQL> alter database add standby logfile group 5

2 ('/oracle/oradata/orcl/redo05.log') size 50m;SQL> alter database add standby logfile group 6

2 ('/oracle/oradata/orcl/redo06.log') size 50m;SQL> alter database add standby logfile group 7

2 ('/oracle/oradata/orcl/redo07.log') size 50m;2.在主机上

SQL> shutdown immediate;SQL> startup mount； 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 UNASSIGNEDMAXIMIZE PROTECTION和MAXIMIZE AVAILABILITY模式下，备机不能先关闭，会出现如下错误

SQL> shutdown immediate;

ORA-01154: database busy. Open, close, mount, and dismount not allowed now正确的开关机顺序是：

关机：先关主机，后关备机

开机：先开备机，后开主机三、主库和备库的switchover. 注意：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.ora3.从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 LISTENER15.从standby 切换到primary

connect / as sysdba

SQL> alter database commit to switchover to primary;

SQL> shutdown

SQL> startup pfile=/oracle/admin/orcl/pfile/initprimary.ora6.启动新主库端的Listener (port=1522)

[oracle@host161 admin]$ lsnrctl stop

[oracle@host161 admin]$ lsnrctl start LISTENER17.OK jolly10 发表于:2007.11.21 14:57 ::分类: ( oracle 10g release 2 学习笔记 ) ::阅读:(1308次) :: 评论 (3) :: 引用 (0) oracle10g dataguard problem [回复] 按照你上的步驟，怎麼在mode轉換時出現：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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 channelkylix 评论于：2008.03.06 15:46