Dataguard配置Step by Step

link:

http://www.eygle.com/ha/dataguard-step-by-step.htm

1.主节点备份并生成备用数据库控制文件

设置主节点为force Logging模式(为了双向切换,建议备用节点也设置为force logging模式)
ALTER DATABASE FORCE LOGGING;

设置主节点为归档模式

登陆主节点,进行数据库备份，并生成备用数据库控制文件

Last login: Mon Aug 9 16:46:47 2004 from 172.16.32.65 [root@standby root]# su - oracle [oracle@standby oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 10:16:18 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> select name from v$datafile; NAME ------------------------------------------------------------ /opt/oracle/oradata/primary/system01.dbf /opt/oracle/oradata/primary/undotbs01.dbf /opt/oracle/oradata/primary/users01.dbf SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production [oracle@standby oracle]$ ls admin dictionary.ora jre oradata oraInventory oui product soft [oracle@standby oracle]$ tar -cvf oradata.tar oradata oradata/ oradata/primary/ oradata/primary/archive/ oradata/primary/control01.ctl oradata/primary/control02.ctl oradata/primary/control03.ctl oradata/primary/redo01.log oradata/primary/redo02.log oradata/primary/redo03.log oradata/primary/system01.dbf oradata/primary/undotbs01.dbf oradata/primary/temp01.dbf oradata/primary/users01.dbf [oracle@standby oracle]$ ls -l *.tar -rw-r--r-- 1 oracle dba 576512000 Aug 16 10:22 oradata.tar [oracle@standby oracle]$ id uid=800(oracle) gid=800(dba) groups=800(dba) [oracle@standby oracle]$ hostname standby [oracle@standby oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 10:27:54 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/primary/archive Oldest online log sequence 88 Next log sequence to archive 90 Current log sequence 90 SQL> alter database create standby controlfile as '/opt/oracle/stdcotrl.ctl'; Database altered. SQL> ! ls[oracle@standby oracle]$ ls admin dictionary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl

2.从主节点创建pfile文件

SQL> create pfile from spfile; File created. SQL> ! [oracle@standby oracle]$ cd $ORACLE_HOME/dbs [oracle@standby dbs]$ ls initdw.ora init.ora initprimary.ora lkPRIMARY orapwprimary spfileprimary.ora sqlnet.log

3.登陆备用节点,ftp获得数据库文件、备用控制文件及参数文件

4.配置主节点监听器及tnsnames.ora文件
配置后如下:

[oracle@standby oracle]$ cd /opt/oracle/product/9.2.0/network/admin/ [oracle@standby admin]$ cat listener.ora # LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/product/9.2.0) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /opt/oracle/product/9.2.0) (SID_NAME = primary) ) ) [oracle@standby admin]$ cat tnsnames.ora # TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora # Generated by Oracle configuration tools. STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) [oracle@standby admin]$ lsnrctl start LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:31 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 9.2.0.4.0 - Production System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production Start Date 16-AUG-2004 10:46:31 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "primary" has 1 instance(s). Instance "primary", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

5.配置备用数据库监听器及tnsnames.ora文件

配置后文件如下:

6.在主备节点用tnsping测试网络连通性

7.启动备用数据库

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 11:09:40 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered.

8.在主节点设置归档路径

System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> 在备用节点观察日志 [oracle@eygle bdump]$ tail -f alert_primary.log MRP0: Background Managed Standby Recovery process started Starting datafile 1 recovery in thread 1 sequence 90 Datafile 1: '/opt/oracle/oradata/primary/system01.dbf' Starting datafile 2 recovery in thread 1 sequence 90 Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 90 Datafile 3: '/opt/oracle/oradata/primary/users01.dbf' Media Recovery Waiting for thread 1 seq# 90 Mon Aug 16 11:10:50 2004 Completed: alter database recover managed standby database di Mon Aug 16 11:13:34 2004 Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_90.dbf Media Recovery Waiting for thread 1 seq# 91 Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_91.dbf Media Recovery Waiting for thread 1 seq# 92 Mon Aug 16 12:09:38 2004 Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_92.dbf

9.在主节点进行同样的配置，以便切换后继续日志传递

SQL> alter system set standby_archive_dest='/opt/oracle/oradata/primary/stdarch'; System altered.

10.停止主数据库,启用备用数据库

11.进行数据修改

12.把数据库切换回到主节点

完成自由切换