DATAGUARD详细配置

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 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startupORACLE instance started.
Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - Production[oracle@standby oracle]$ lsadmin dictionary.ora jre oradata oraInventory oui product soft
[oracle@standby oracle]$ tar -cvf oradata.tar oradataoradata/oradata/primary/oradata/primary/archive/oradata/primary/control01.ctloradata/primary/control02.ctloradata/primary/control03.ctloradata/primary/redo01.logoradata/primary/redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata/primary/undotbs01.dbforadata/primary/temp01.dbforadata/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]$ iduid=800(oracle) gid=800(dba) groups=800(dba)[oracle@standby oracle]$ hostnamestandby[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> startupORACLE instance started.
Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /opt/oracle/oradata/primary/archiveOldest online log sequence 88Next log sequence to archive 90Current log sequence 90SQL> alter database create standby controlfile as '/opt/oracle/stdcotrl.ctl';
Database altered.
SQL> !ls[oracle@standby oracle]$ lsadmin 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]$ lsinitdw.ora init.ora initprimary.ora lkPRIMARY orapwprimary spfileprimary.ora sqlnet.log

3. 登陆备用节点,ftp获得数据库文件、备用控制文件及参数文件Last login: Mon Aug 16 08:47:58 2004 from 172.16.32.65 [root@eygle root]# su - oracle[oracle@eygle oracle]$ lsadmin doc jre oradata oraInventory oui product[oracle@eygle oracle]$ df -kFilesystem 1K-blocks Used Available Use% Mounted on/dev/sda1 5154852 3360600 1532396 69% //dev/sda7 101089 25744 70126 27% /home/dev/sda5 4127076 2686152 1231280 69% /optnone 515296 0 515296 0% /dev/shm/dev/sda2 4127108 2218172 1699288 57% /usr/dev/sda6 2063504 107744 1850940 6% /var[oracle@eygle oracle]$ ftp 172.16.33.58Connected to 172.16.33.58 (172.16.33.58).220 (vsFTPd 1.2.0)Name (172.16.33.58:root): oracle331 Please specify the password.Password:230 Login successful.ftp> ls227 Entering Passive Mode (172,16,33,58,222,252)150 Here comes the directory listing.drwxr-xr-x 3 800 800 4096 Jun 30 07:02 admin-rw-r--r-- 1 800 800 5422222 Jul 13 11:58 dictionary.ora-rw-r--r-- 1 800 800 1165 Aug 16 02:51 initprimary.oradrwxrwxr-x 4 800 800 4096 Jun 30 06:29 jredrwxrwxr-x 12 800 800 4096 Jun 30 06:44 oraInventorydrwxr-xr-x 3 800 800 4096 Jul 01 06:15 oradata-rw-r--r-- 1 800 800 576512000 Aug 16 02:22 oradata.tardrwxrwxr-x 6 800 800 4096 Jun 30 06:29 ouidrwxr-xr-x 3 800 800 4096 Jun 30 05:18 productdrwxr-xr-x 6 800 800 4096 Jun 30 04:24 soft-rw-r----- 1 800 800 1662976 Aug 16 02:37 stdcotrl.ctl226 Directory send OK.ftp> bin200 Switching to Binary mode.ftp> mget oradata.tarmget oradata.tar? y227 Entering Passive Mode (172,16,33,58,238,132)150 Opening BINARY mode data connection for oradata.tar (576512000 bytes).226 File send OK.576512000 bytes received in 49.2 secs (1.1e+04 Kbytes/sec)ftp> mget *.ctlmget stdcotrl.ctl? y227 Entering Passive Mode (172,16,33,58,73,35)150 Opening BINARY mode data connection for stdcotrl.ctl (1662976 bytes).226 File send OK.1662976 bytes received in 0.14 secs (1.2e+04 Kbytes/sec)ftp> mget initprimary.oramget initprimary.ora? y227 Entering Passive Mode (172,16,33,58,194,239)150 Opening BINARY mode data connection for initprimary.ora (1165 bytes).226 File send OK.1165 bytes received in 0.000325 secs (3.5e+03 Kbytes/sec)ftp> bye221 Goodbye.[oracle@eygle oracle]$ lsadmin doc initprimary.ora jre oradata oradata.tar oraInventory oui product stdcotrl.ctl[oracle@eygle oracle]$ mv initprimary.ora $ORACLE_HOME/dbs[oracle@eygle oracle]$ cd $ORACLE_HOME/dbs[oracle@eygle dbs]$ lsa.sql initdw.ora init.ora initprimary.ora initrac1.ora initrac2.ora initrac.ora orapw orapwrac1 orapwrac2 spfilerac.ora

解包数据文件
[oracle@eygle oracle]$ ls admin doc jre oradata oradata.tar oraInventory oui product stdcotrl.ctl [oracle@eygle oracle]$ tar -xvf oradata.tar oradata/oradata/primary/oradata/primary/archive/oradata /primary/control01.ctloradata/primary/control02.ctloradata/primary /control03.ctloradata/primary/redo01.logoradata/primary /redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata /primary/undotbs01.dbforadata/primary/temp01.dbforadata/primary /users01.dbf修改initprimary.ora文件修改控制文件名称及路径(如果和原配置不同)，增加几个参数,修改后如下:
[oracle@eygle dbs]$ cat initprimary.ora *.aq_tm_processes=1 *.background_dump_dest='/opt/oracle/admin/primary/bdump' *.compatible='9.2.0.0.0' *.control_files='/opt/oracle/oradata/primary/stdcotrl.ctl' *.core_dump_dest='/opt/oracle/admin/primary/cdump' ... *.log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/archive' *.log_archive_dest_2='' *.log_archive_format='%t_ %s.dbf' *.log_archive_start=true ... *.user_dump_dest='/opt/oracle/admin /primary/udump' *.utl_file_dir='/opt/oracle' *.standby_archive_dest=' /opt/oracle/oradata/primary /stdarch' *.fal_server='PRIMARY' *.fal_client='STANDBY' *.standby_file_management='AUTO' 创建必要的目录
[oracle@eygle oracle]$ cd $ORACLE_BASE/admin[oracle@eygle admin]$ mkdir primary[oracle@eygle admin]$ lsprimary rac[oracle@eygle admin]$ cd primary/[oracle@eygle primary]$ ls[oracle@eygle primary]$ mkdir bdump cdump udump4.配置主节点监听器及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 - ProductionSystem parameter file is /opt/oracle/product/9.2.0/network/admin/listener.oraLog messages written to /opt/oracle/product/9.2.0/network/log/listener.logListening 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 LISTENERVersion TNSLSNR for Linux: Version 9.2.0.4.0 - ProductionStart Date 16-AUG-2004 10:46:31Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity OFFSNMP OFFListener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.oraListener Log File /opt/oracle/product/9.2.0/network/log/listener.logListening 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文件配置后文件如下:
[oracle@eygle admin]$ cd $ORACLE_HOME/network/admin [oracle@eygle 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 = TCP)(HOST = eygle)(PORT = 1521)))))
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = primary)(ORACLE_HOME = /opt/oracle/product/9.2.0)(SID_NAME = primary)))[oracle@eygle 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@eygle admin]$

6.在主备节点用tnsping测试网络连通性
[oracle@standby admin]$ tnsping standby TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:50Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))OK (10 msec)[oracle@standby admin]$ tnsping primary
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:55
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))OK (0 msec)
[oracle@eygle admin]$ tnsping primary
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:01
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.58)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))OK (50 msec)[oracle@eygle admin]$ tnsping standby
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:06
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.46)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))OK (10 msec)
7.启动备用数据库
[oracle@eygle primary]$ hostnameeygle[oracle@eygle primary]$ sqlplus "/ as sysdba" 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 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
8.在主节点设置归档路径
SQL> alter system set log_archive_dest_2='service=standby mandatory reopen=60'; 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.dbf9.在主节点进行同样的配置，以便切换后继续日志传递
[oracle@standby oracle]$ lsadmin dictionary.ora initprimary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl[oracle@standby oracle]$ cd oradata[oracle@standby oradata]$ lsprimary[oracle@standby oradata]$ cd primary/[oracle@standby primary]$ lsarchive control02.ctl redo01.log redo03.log temp01.dbf users01.dbfcontrol01.ctl control03.ctl redo02.log system01.dbf undotbs01.dbf[oracle@standby primary]$ mkdir stdarch[oracle@standby primary]$ exitexit
SQL> alter system set standby_archive_dest='/opt/oracle/oradata/primary/stdarch';
System altered.

10.停止主数据库,启用备用数据库
SQL> alter database commit to switchover to physical standby; Database altered.SQL> shutdown immediateORA-01507: database not mounted

ORACLE instance shut down.

在备用模式启用主数据
SQL> startup nomount; ORACLE instance started. Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database;Database altered.

SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE--------- ---------- -------------------- ----------------PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

打开备用数据库

[oracle@eygle oracle]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 12:11:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;ORA-01507: database not mounted

ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> alter system switch logfile;
System altered.
在主库上观察日志应用情况
[oracle@standby bdump]$ tail -f alert_primary.log Starting datafile 2 recovery in thread 1 sequence 93 Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 93 Datafile 3: '/opt/oracle/oradata/primary/users01.dbf' Media Recovery Log /opt/oracle/product/9.2.0/dbs/arch1_93.dbf Mon Aug 16 15:08:43 2004 Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_94.dbf Media Recovery Waiting for thread 1 seq# 95 Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_95.dbf Media Recovery Waiting for thread 1 seq# 9611.进行数据修改SQL> create table t as select * from dba_users; Table created.SQL> alter system switch logfile;
System altered.

在从库上以read only打开数据库，执行查询

SQL> select username from t;select username from t*ERROR at line 1:ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> select username from t;
USERNAME------------------------------SYSSYSTEMDBSNMPOUTLNWMSYS
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.
SQL>
12.把数据库切换回到主节点
在主节点
SQL> alter database commit to switchover to physical standby;Database altered.
SQL> shutdown immediateORA-01507: database not mounted

statORACLE instance shut down.SQL> startup nomount;ORACLE instance started.
Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
在备用节点
SQL> alter database commit to switchover to primary;Database altered.
SQL> shutdown immediate;ORA-01507: database not mounted

ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened