一. 环境描述
主节点Primary db server:
hostname: primarydb
ip: 168.0.3.92
sid: gridctl
备节点Standby db server:
hostname:standbydb
ip: 168.0.3.93
sid: gridctl
Two nodes:
#more /etc/hosts
168.10.13.192 primarydb
168.10.13.193 standbydb
ORACLE_HOME:/oracle/product/10.2.0/db_1
Archivelog_dest:/oradata/archivelog/primary_arc
/oradata/archivelog/standby_arc
datafile directory: /oradata/gridctl/
[@more@]二. 创建物理备用数据库(Create Physical Standby Database)
- 编辑主节点数据库环境
1.1 设置主节点归档日志目录，standby归档日志目录
#su - oracle
$cd /oradata/archivelog
$mkdir primary_arc standby_arc
$sqlplus / as sysdba
SQL>alter system set log_archive_dest_1='LOCATION=/oradata/archivelog/primary_arc' scope=both;
SQL>alter system set standby_archive_dest='/oradata/archivelog/standby_arc' scope=both;
1.2 如果主库不是归档模式,需要将主库修改为归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oradata/archivelog/primary_arc
Oldest online log sequence 204
Current log sequence 206
SQL> shutdown immediate;
Database closed.
Database dismounted.
SQL> startup mount;
ORACLE instance started.
。。。。。。
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL>!
[oracle@primarydb primary_arc]$ pwd
/oradata/archivelog/primary_arc
[oracle@primarydb primary_arc]$ ls -lt
total 376
-rw-r----- 1 oracle oinstall 379904 Sep 2 17:26 1_207_724504451.dbf
$exit
1.3 设置主节点为force logging 模式
SQL>alter database force logging;
Database altered.
- 主节点上,生成备用库所需的文件(数据文件,控制文件,PFILE)
2.1 查询主节点数据文件
SQL> select name from v$datafile;
/oradata/gridctl/system01.dbf
/oradata/gridctl/undotbs01.dbf
/oradata/gridctl/sysaux01.dbf
/oradata/gridctl/users01.dbf
/oradata/gridctl/mgmt.dbf
/oradata/gridctl/mgmt_ecm_depot1.dbf
SQL> select name from v$tempfile;
/oradata/gridctl/temp01.dbf
SQL> select member from v$logfile;
/oradata/gridctl/redo01.log
/oradata/gridctl/redo02.log
/oradata/gridctl/redo03.log
SQL> select name from v$controlfile;
/oradata/gridctl/ora_control1.dbf
/oradata/gridctl/ora_control2.dbf
/oradata/gridctl/ora_control3.dbf
SQL> !
[oracle@primarydb ~]$ cd /oradata/gridctl
[oracle@primarydb gridctl]$ ls -lt
total 2696988
-rw-r----- 1 oracle oinstall 104858112 Sep 3 09:28 redo02.log
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:28 ora_control1.dbf
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:28 ora_control2.dbf
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:28 ora_control3.dbf
-rw-r----- 1 oracle oinstall 822091776 Sep 3 09:28 system01.dbf
-rw-r----- 1 oracle oinstall 484450304 Sep 3 09:28 undotbs01.dbf
-rw-r----- 1 oracle oinstall 398467072 Sep 3 09:28 mgmt.dbf
-rw-r----- 1 oracle oinstall 340795392 Sep 3 09:26 sysaux01.dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 3 09:21 mgmt_ecm_depot1.dbf
-rw-r----- 1 oracle oinstall 268443648 Sep 3 07:02 users01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 3 06:57 redo01.log
-rw-r----- 1 oracle oinstall 209723392 Sep 3 05:05 temp01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 2 17:26 redo03.log
2.2 将主节点数据文件打包
SQL>shutdown immediate;
Database closed.
Database dismounted.
SQL>exit
[oracle@primarydb ~]$cd /oradata
[oracle@primarydb ~]$tar -cvf oradata_gridctl.tar gridctl
2.3 在主节点上创建备用库控制文件
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089472 bytes
Variable Size 578817536 bytes
Database Buffers 490733568 bytes
Redo Buffers 2101248 bytes
Database mounted.
Database opened.
SQL> alter database create standby controlfile as '/oradata/stdcontrol.ctl';
Database altered.
2.4 在主节点上创建pfile,并做相应的修改
SQL>create pfile='/oradata/initgridctl.ora' from spfile;
SQL>exit
[oracle@primarydb ~]cd /oradata
[oracle@primarydb oradata]$ more initgridctl.ora
。。。。。。
*.control_files='/oradata/gridctl/stdcontrol.ctl'
*.log_archive_dest_1='LOCATION=/oradata/archivelog/primary_arc'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.standby_archive_dest='/oradata/archivelog/standby_arc'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.standby_file_management='AUTO'
。。。。。。
本节主要是在主节点上生成三个文件，分别为：
/oradata/oradata_gridctl.tar
/oradata/stdcontrol.ctl
/oradata/initgridctl.ora
- 通过ftp方式将主节点的三个文件拷贝到备用库,并分别解压或者复制到合适的位置
通过ftp,将主节点/oradata/oradata_gridctl.tar;/oradata/stdcontrol.ctl两个文件以bin方式ftp到备库,将主节点/oradata/initgridctl.ora以asc方式ftp到备库.
备节点:
[oracle@standbydb oradata]$ ls -lt
-rw-r--r-- 1 oracle oinstall 1285 Sep 3 17:15 initgridctl.ora
-rw-r--r-- 1 oracle oinstall 6832128 Sep 3 17:15 stdcontrol.ctl
-rw-r--r-- 1 oracle oinstall 2963916800 Sep 3 17:14 oradata_gridctl.tar
drwxr-xr-x 2 oracle dba 4096 Sep 3 17:12 archivelog
drwxr-xr-x 2 oracle dba 16384 Aug 12 2009 lost+found
[oracle@standbydb oradata]$ tar -xvf oradata_gridctl.tar
gridctl/
gridctl/ora_control1.dbf
gridctl/ora_control2.dbf
gridctl/ora_control3.dbf
gridctl/redo01.log
gridctl/redo02.log
gridctl/redo03.log
gridctl/system01.dbf
gridctl/undotbs01.dbf
gridctl/sysaux01.dbf
gridctl/temp01.dbf
gridctl/users01.dbf
gridctl/mgmt.dbf
gridctl/mgmt_ecm_depot1.dbf
[oracle@standbydb oradata]$ mv stdcontrol.ctl ./gridctl
[oracle@standbydb oradata]$ cd gridctl
[oracle@standbydb gridctl]$ ls -lt
-rw-r--r-- 1 oracle oinstall 6832128 Sep 3 17:15 stdcontrol.ctl
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:35 ora_control1.dbf
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:35 ora_control2.dbf
-rw-r----- 1 oracle oinstall 6832128 Sep 3 09:35 ora_control3.dbf
-rw-r----- 1 oracle oinstall 398467072 Sep 3 09:35 mgmt.dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 3 09:35 mgmt_ecm_depot1.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 3 09:35 redo02.log
-rw-r----- 1 oracle oinstall 340795392 Sep 3 09:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 822091776 Sep 3 09:35 system01.dbf
-rw-r----- 1 oracle oinstall 484450304 Sep 3 09:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 268443648 Sep 3 09:35 users01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 3 06:57 redo01.log
-rw-r----- 1 oracle oinstall 209723392 Sep 3 05:05 temp01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 2 17:26 redo03.log
- 备机上创建一些必须的目录,目录的结构与主节点相同
4.1 建立归档日志的相关目录
[oracle@standbydb dbs]$ cd /oradata/archivelog
[oracle@standbydb archivelog]$ ls
[oracle@standbydb archivelog]$ mkdir primary_arc standby_arc
[oracle@standbydb archivelog]$ ls -lt
total 8
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:26 primary_arc
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:26 standby_arc
4.2 建立dump相关目录
[oracle@standbydb oracle]$ cd /oracle
[oracle@standbydb oracle]$ ls
OracleHomes oraInventory product
[oracle@standbydb oracle]$ mkdir admin
[oracle@standbydb oracle]$ cd admin
[oracle@standbydb admin]$ mkdir gridctl
[oracle@standbydb admin]$ cd gridctl
[oracle@standbydb gridctl]$ pwd
/oracle/admin/gridctl
[oracle@standbydb gridctl]$ mkdir adump bdump cdump udump
[oracle@standbydb gridctl]$ ls -lt
total 20
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:27 adump
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:27 bdump
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:27 cdump
drwxr-xr-x 2 oracle oinstall 4096 Sep 3 17:27 udump
- 配置主节点的监听器LISTENER.ORA和TNSNAMES.ORA 文件
[oracle@primarydb /]$ cd $ORACLE_HOME/network/admin
[oracle@primarydb admin]$ more listener.ora
。。。。。。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = gridctl)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = gridctl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521))
)
配置完主节点监听器listener.ora文件后,启动主节点监听器
[oracle@primarydb admin]$ lsnrctl
LSNRCTL> start
Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primarydb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 03-SEP-2010 17:48:33
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db_1/network/log/listener.log
Service "gridctl" has 1 instance(s).
Instance "gridctl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@primarydb admin]$ more tnsnames.ora
。。。。。。
PRIMARY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primarydb )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gridctl)
)
)
STANDBY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbydb )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gridctl)
)
)
- 配置备库监听器及tnsnames.ora文件
[oracle@standbydb ]$cd $ORACLE_HOME/network/admin
[oracle@standbydb admin]$ more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = gridctl)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = gridctl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbydb)(PORT = 1521))
)
配置完备库监听器listener.ora文件后,启动备库监听器
[oracle@standbydb admin]$ lsnrctl
LSNRCTL> start
。。。。。。
Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbydb)(PORT=1521)))
Service "gridctl" has 1 instance(s).
。。。。。。
[oracle@standbydb admin]$ more tnsnames.ora
PRIMARY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primarydb )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gridctl)
)
)
STANDBY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbydb )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gridctl)
)
)
- 在主备节点上分别用tnsping测试网络连通性
7.1 主节点测试网络连通性
[oracle@primarydb admin]$ tnsping primary
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gridctl)))
OK (0 msec)
[oracle@primarydb admin]$ tnsping standby
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gridctl)))
OK (10 msec)
主节点连接primary，standby都是通的。
7.2 备节点测试网络连通性
[oracle@standbydb admin]$ tnsping primary
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primarydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gridctl)))
OK (0 msec)
[oracle@standbydb admin]$ tnsping standby
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbydb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = gridctl)))
OK (10 msec)
备节点连接primary，standby都是通的。
7.3 在主、备机上检查$ORACLE_HOME/dbs/orapw
[oracle@standbydb]$cd $ORACLE_HOME/dbs
[oracle@standbydb]$orapwd file=$ORACLE_HOME/dbs/orapwgridctl2 password=password entries=5
- 在备库节点，创建spfile，并启动备数据库到standby role状态
[oracle@standbydb ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089472 bytes
Variable Size 578817536 bytes
Database Buffers 490733568 bytes
Redo Buffers 2101248 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
- 在主节点上设置归档路径，切换几次日志
[oracle@primarydb admin]$ sqlplus / as sysdba
SQL> alter system set log_archive_dest_2='service=standby mandatory reopen=60' scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
在备节点上观察日志：
[oracle@standbydb bdump]$ tail -f alert_gridctl.log
-- Connected User is Valid
RFS[2]: Assigned to RFS process 20551
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Archived Log: '/oradata/archivelog/standby_arc/1_209_724504451.dbf'
Fri Sep 3 18:31:19 2010
RFS[1]: Archived Log: '/oradata/archivelog/standby_arc/1_210_724504451.dbf'
Fri Sep 3 18:31:22 2010
Media Recovery Log /oradata/archivelog/standby_arc/1_209_724504451.dbf
Media Recovery Log /oradata/archivelog/standby_arc/1_210_724504451.dbf
Media Recovery Waiting for thread 1 sequence 211
Fri Sep 3 18:32:06 2010
RFS[2]: Archived Log: '/oradata/archivelog/standby_arc/1_211_724504451.dbf'
Fri Sep 3 18:32:06 2010
Media Recovery Log /oradata/archivelog/standby_arc/1_211_724504451.dbf
Media Recovery Waiting for thread 1 sequence 212