RedHat搭建物理Data Guard
我的搭建环境：
两台机器均为：Red Hat Enterprise Linux Server release 5.4
数据库版本为：Oracle10g10.2.0
primary机上装oracle软件并创建数据库orcl
standby机上只装oralce软件，无需装数据库
基本配置：
源数据库：
IP：10.37.1.1
数据库SID：orcl_p
db_unique_name：orcl1
standby数据库：
IP：10.37.1.2
数据库SID：orcl_s
db_unique_name：orcl2
配置步骤：
1、配置primary数据库归档，并设置本地归档路径
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/arch';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
2、将primary数据库置于force logging模式
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
3、创建并修改primary数据库的初始化参数文件
SQL> create pfile='/u01/p_pfile.ora' from spfile;
File created.
[oracle@localhost ~]$ vi /u01/p_pfile.ora
修改如下内容：
*.db_unique_name=orcl1
*.log_archive_config='dg_config=(orcl1,orcl2)'
*.log_archive_dest_2='service=orcl_s.2_tns arch valid_for=(online_logfiles,primary_role)db_unique_name=orcl2'
*.log_archive_dest_state_2=defer
*.fal_server=orcl_s.2_tns
*.fal_client=orcl_p.1_tns
*.db_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'
*.standby_file_management=auto
关闭数据库利用修改后的pfile创建spfile
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !export ORACLE_SID=orcl_p
SQL> create spfile from pfile='/u01/p_pfile.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
4、创建备份：
创建standby的控制文件：
SQL> alter database create standby controlfile as '/u01/orcl2control01.ctl';
Database altered.
创建所有的数据文件备份（此处仅以users表空间下的数据文件为例，其他数据文件均要备份）
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> !cp /u01/app/oracle/oradata/orcl/users01.dbf /u01
SQL> alter tablespace users end backup;
Tablespace altered.
5、配置primary监听和tns服务
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ vi listener.ora
配置内容如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl_p)
(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
(GLOBAL_DBNAME = orcl_p)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))
)
)
配置tns
[oracle@localhost admin]$ vi tnsnames.ora
配置内容如下：
orcl_p.1_tns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl_p)
(SERVER = DEDICATED)
)
)
orcl_s.2_tns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl_s)
(SERVER = DEDICATED)
)
)
此时重启监听后，测试源数据库tns配置
[oracle@localhost admin]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.37.1.1)(PORT=1521)))
The command completed successfully
[oracle@localhost admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:53
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/app/oracle/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.37.1.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.37.1.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-FEB-2014 19:11:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.37.1.1)(PORT=1521)))
Services Summary...
Service "orcl_p" has 1 instance(s).
Instance "orcl_P", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost admin]$ tnsping orcl_p.1_tns
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:12:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = orcl_p) (SERVER = DEDICATED)))
OK (0 msec)
6、standby数据库创建目录结构，并将源数据库的参数文件、备份的控制文件、创建的口令文件copy到相应的位置
7、
[oracle@localhost ~]$ mkdir -p /u01/arch2
[oracle@localhost u01]$ mkdir -p /u01/app/oracle/flash_recovery_area
[oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/orcl_s
[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/orcl_s/{a,b,c,u}dump
[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.dbf /u01/app/oracle/oradata/orcl_s/
[email protected]'s password:
example01.dbf 100% 100MB 20.0MB/s 00:05
sysaux01.dbf 100% 240MB 16.0MB/s 00:15
system01.dbf 100% 480MB 14.6MB/s 00:33
undotbs01.dbf 100% 30MB 15.0MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:01
[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.ctl /u01/app/oracle/oradata/orcl_s/
[email protected]'s password:
orcl2control01.ctl 100% 6896KB 6.7MB/s 00:00
[oracle@localhost ~]$ scp 10.37.1.1:/u01/p_pfile.ora /u01s_pfile.ora
[email protected]'s password:
p_pfile.ora 100% 1508 1.5KB/s 00:00
[oracle@localhost ~]$ scp 10.37.1.1:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_p /u01/app/oracle/10.2.0/db_1/dbs/orapworcl_s
[email protected]'s password:
orapworcl_p 100% 5120 5.0KB/s 00:00
7、��改standby数据库的sid为orcl_s并配置copy来的参数文件
[root@localhost ~]# vi /u01/s_pfile.ora
需要修改的内容如下：（没必要照搬，可根据自己的实际情况自行修改，注意红色部分是重点修改的地方）
*.audit_file_dest='/u01/app/oracle/admin/orcl_s/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl_s/bdump'
*.control_files='/u01/app/oracle/oradata/orcl_s/orcl2control01.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control02.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl_s/cdump'
*.user_dump_dest='/u01/app/oracle/admin/orcl_s/udump'
*.db_unique_name=orcl2
*.log_archive_dest_1='location=/u01/arch2'
*.log_archive_dest_2='service=orcl_p.1_tns arch valid_for=(online_logfiles, primary_ro
le) db_unique_name=orcl1'
*.log_archive_dest_state_2=enable
*.fal_server=orcl_p.1_tns
*.fal_client=orcl_s.2_tns
8、配置standby数据库的监听和服务名
[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl_s)
(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
(GLOBAL_DBNAME = orcl_s)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.2)(PORT = 1521))
)
)
[oracle@localhost admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl_s.2_tns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl_s)
(SERVER = DEDICATED)
)
)
orcl_p.1_tns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl_p)
(SERVER = DEDICATED)
"tnsnames.ora" 36L, 764C
配置完成后重启监听服务
[oracle@localhost admin]$ lsnrctl stop
[oracle@localhost admin]$ lsnrctl start
至此监听和服务配置完成，在primary和standby端用tnsping命令应该能ping都通两个服务，能远程登入两数据库视为配置成功
SQL> conn sys/oracle@orcl_p.1_tns as sysdba
Connected.
SQL> conn sys/oracle@orcl_s.2_tns as sysdba
Connected to an idle instance.
9、配置stanby数据库并启动到mount状态，并接受归档文件
任意终端连接到standby数据库
[oracle@localhost ~]$ sqlplus sys/oracle@orcl_s.2_tns as sysdba
利用s_pfile.ora常见standby的spfile
SQL> create spfile from pfile='/u01/s_pfile.ora';
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
连接到primary数据库并设置远程归档路径开启
SQL> conn sys/oracle@orcl_p.1_tns as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
查看归档接受情况
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
4
SQL> conn sys/oracle@orcl_s.2_tns as sysdba
Connected.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
4
查看standby的归档路径下是否有源数据库传来的归档日志
SQL> !ls /u01/arch2
1_4_840520047.dbf
10、primary数据插入，测试standby数据库能否正常接受
primary端创建表并插入数据
SQL> conn scott/tiger
Connected.
SQL> create table DG_TEST(ID VARCHAR2(10));
Table created.
SQL> insert into DG_TEST
2 values ('DG_TEST')
3 /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from DG_TEST;
ID
----------
DG_TEST
DG_TEST
DG_TEST
DG_TEST
SQL> commit;
Commit complete.
切换归档日志，使当前日志归档
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
standby启动redo应用（这也正是物理standby的工作方法，等于是从获取的归档中执行恢复操作，来保持与源数据库一致）
SQL> alter database recover managed standby database disconnect from session;
Database altered.
打开数据库前必须停止redo应用
SQL> alter database recover managed standby database cancel;
Database altered.
打开standby数据，查看是否有源数据库新插入的数据
SQL> select * from scott.DG_TEST;
ID
----------
DG_TEST
DG_TEST
DG_TEST
DG_TEST
数据成功传入standby数据库，物理的DG搭建成功！
--------------------------------------分割线 --------------------------------------
--------------------------------------分割线 --------------------------------------