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 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.dbfSQL> 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 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> 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]$ ls
initdw.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]$ lsadmin doc jre oradata oradata.tar oraInventory oui product stdcotrl.ctl[oracle@eygle oracle]$ tar -xvf oradata.taroradata/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 udump
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 startLSNRCTL for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:31Copyright (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 standbyTNS 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.oraUsed 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 primaryTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:55Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsed 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 primaryTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:01Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsed 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 standbyTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:06Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsed 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]$ hostname
eygle
[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 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.在主节点设置归档路径
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.dbf
9.在主节点进行同样的配置，以便切换后继续日志传递
[oracle@standby oracle]$ ls
admin dictionary.ora initprimary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl
[oracle@standby oracle]$ cd oradata
[oracle@standby oradata]$ ls
primary
[oracle@standby oradata]$ cd primary/
[oracle@standby primary]$ ls
archive control02.ctl redo01.log redo03.log temp01.dbf users01.dbf
control01.ctl control03.ctl redo02.log system01.dbf undotbs01.dbf
[oracle@standby primary]$ mkdir stdarch
[oracle@standby primary]$ exit
exit
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 mountedORACLE 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 STANDBYSQL> 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 2004Copyright (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 - ProductionSQL> alter database commit to switchover to primary;Database altered.SQL> shutdown immediate;ORA-01507: database not mountedORACLE 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 93Datafile 2: '/opt/oracle/oradata/primary/undotbs01.dbf'Starting datafile 3 recovery in thread 1 sequence 93Datafile 3: '/opt/oracle/oradata/primary/users01.dbf'Media Recovery Log /opt/oracle/product/9.2.0/dbs/arch1_93.dbfMon Aug 16 15:08:43 2004Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_94.dbfMedia Recovery Waiting for thread 1 seq# 95Media Recovery Log /opt/oracle/oradata/primary/stdarch/1_95.dbfMedia Recovery Waiting for thread 1 seq# 96
11.进行数据修改
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 onlySQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.SQL> ALTER DATABASE OPEN READ ONLY;Database altered.SQL> select username from t;USERNAME------------------------------SYSSYSTEMDBSNMPOUTLNWMSYSSQL> 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 mountedstatORACLE 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> 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 mountedORACLE 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.
完成自由切换