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[[email protected] root]# su - oracle[[email protected] 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[[email protected] oracle]$ lsadmin dictionary.ora jre oradata orainventory oui product soft[[email protected] 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[[email protected] oracle]$ ls -l *.tar-rw-r--r-- 1 oracle dba 576512000 aug 16 10:22 oradata.tar[[email protected] oracle]$ iduid=800(oracle) gid=800(dba) groups=800(dba)[[email protected] oracle]$ hostnamestandby[[email protected] 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[[email protected] oracle]$ lsadmin dictionary.ora jre oradata oradata.tar orainventory oui product soft stdcotrl.ctl
2.从主节点创建pfile文件
sql> create pfile from spfile;
file created.
sql> !
[[email protected] oracle]$ cd $oracle_home/dbs
[[email protected] 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[[email protected] root]# su - oracle[[email protected] oracle]$ lsadmin doc jre oradata orainventory oui product[[email protected] 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[[email protected] 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.[[email protected] oracle]$ lsadmin doc initprimary.ora jre oradata oradata.tar orainventory oui product stdcotrl.ctl[[email protected] oracle]$ mv initprimary.ora $oracle_home/dbs[[email protected] oracle]$ cd $oracle_home/dbs[[email protected] dbs]$ lsa.sql initdw.ora init.ora initprimary.ora initrac1.ora initrac2.ora initrac.ora orapw orapwrac1 orapwrac2 spfilerac.ora解包数据文件[[email protected] oracle]$ lsadmin doc jre oradata oradata.tar orainventory oui product stdcotrl.ctl[[email protected] 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文件修改控制文件名称及路径(如果和原配置不同)，增加几个参数,修改后如下:[[email protected] 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'创建必要的目录[[email protected] oracle]$ cd $oracle_base/admin[[email protected] admin]$ mkdir primary[[email protected] admin]$ lsprimary rac[[email protected] admin]$ cd primary/[[email protected] primary]$ ls[[email protected] primary]$ mkdir bdump cdump udump
4.配置主节点监听器及tnsnames.ora文件
配置后如下:
[[email protected] oracle]$ cd /opt/oracle/product/9.2.0/network/admin/[[email protected] 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) ) )[[email protected] 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) ) )[[email protected] 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文件
配置后文件如下:
[[email protected] admin]$ cd $oracle_home/network/admin[[email protected] 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) ) )[[email protected] 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) ) )[[email protected] admin]$
6.在主备节点用tnsping测试网络连通性
[[email protected] 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)[[email protected] 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)[[email protected] 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)[[email protected] 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.启动备用数据库
[[email protected] primary]$ hostname
eygle
[[email protected] 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>
在备用节点观察日志
[[email protected] 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.在主节点进行同样的配置，以便切换后继续日志传递
[[email protected] oracle]$ ls
admin dictionary.ora initprimary.ora jre oradata oradata.tar orainventory oui product soft stdcotrl.ctl
[[email protected] oracle]$ cd oradata
[[email protected] oradata]$ ls
primary
[[email protected] oradata]$ cd primary/
[[email protected] primary]$ ls
archive control02.ctl redo01.log redo03.log temp01.dbf users01.dbf
control01.ctl control03.ctl redo02.log system01.dbf undotbs01.dbf
[[email protected] primary]$ mkdir stdarch
[[email protected] 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.打开备用数据库[[email protected] 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.在主库上观察日志应用情况[[email protected] 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.
完成自由切换
