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> 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获得数据库文件、备用控制文件及参数文件
Last login: Mon Aug 16 08:47:58 2004 from 172.16.32.65
[root@eygle root]# su - oracle
[oracle@eygle oracle]$ ls
admin doc jre oradata oraInventory oui product
[oracle@eygle oracle]$ df -k
Filesystem 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% /opt
none 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.58
Connected to 172.16.33.58 (172.16.33.58).
220 (vsFTPd 1.2.0)
Name (172.16.33.58:root): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> ls
227 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.ora
drwxrwxr-x 4 800 800 4096 Jun 30 06:29 jre
drwxrwxr-x 12 800 800 4096 Jun 30 06:44 oraInventory
drwxr-xr-x 3 800 800 4096 Jul 01 06:15 oradata
-rw-r--r-- 1 800 800 576512000 Aug 16 02:22 oradata.tar
drwxrwxr-x 6 800 800 4096 Jun 30 06:29 oui
drwxr-xr-x 3 800 800 4096 Jun 30 05:18 product
drwxr-xr-x 6 800 800 4096 Jun 30 04:24 soft
-rw-r----- 1 800 800 1662976 Aug 16 02:37 stdcotrl.ctl
226 Directory send OK.
ftp> bin
200 Switching to Binary mode.
ftp> mget oradata.tar
mget oradata.tar? y
227 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 *.ctl
mget stdcotrl.ctl? y
227 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.ora
mget initprimary.ora? y
227 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> bye
221 Goodbye.[oracle@eygle oracle]$ ls
admin 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]$ ls
a.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.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
修改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]$ ls
primary 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 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文件
配置后文件如下:
[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:50
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 alias
Attempting 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: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 alias
Attempting 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 alias
Attempting 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: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 alias
Attempting 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.dbfcontrol01.ctl control03.ctl redo02.log system01.dbf
undotbs01.dbf
[oracle@standby primary]$ mkdir stdarch
[oracle@standby primary]$ exit
exitSQL> 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 immediate
ORA-01507: database not mounted
ORACLE instance shut down.
在备用模式启用主数据
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> 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 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - ProductionSQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
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> alter system switch logfile;System altered.
在主库上观察日志应用情况
[oracle@standby bdump]$ tail -f alert_primary.log11.进行数据修改
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# 96
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
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYSSQL> 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 immediate
ORA-01507: database not mounted
statORACLE instance shut down.
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 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 mounted
ORACLE instance shut down.
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.
完成自由切换
-The End-
链接: http://www.eygle.com/archives/2004/10/dataguard-step-by-step.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22750644/viewspace-676365/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22750644/viewspace-676365/