oracle 11g dataguard环境搭建
硬件和系统软件说明:
操作系统:Oracle Linux 5.8 64位
oracle软件:oracle 11.2.0.1
角色 主机名 IP地址 数据库 服务名
primary dg1 192.168.3.70 orcl tong
standby dg2 192.168.3.80 orcl cheng
一.primary主库操作
1.在primary服务器安装oracle软件,并创建数据库.在standby服务器只安装oracle软件,不创建数据库.
2.配置监听
[oracle@dg1 dbs]$ cd /u01/product/11.2.0.1/db_1/network/admin/
[oracle@dg1 admin]$ vim listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl) --primary库的名字
(ORACLE_HOME = /u01/product/11.2.0.1/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.70)(PORT = 1521)) --prmary库的IP地址
)
ADR_BASE_LISTENER = /u01
[oracle@dg1 admin]$ vim tnsnames.ora
tong =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.70)(PORT = 1521)) --primary库的IP地址
)
(CONNECT_DATA =
(SERVICE_NAME = tong) --服务名必须与上面的服务名相同
)
)
cheng =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.80)(PORT = 1521)) --standby库的IP地址
)
(CONNECT_DATA =
(SERVICE_NAME = cheng)
)
)
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
3.在primary库启用归档和日志强行写入redo文件
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 16 14:38:46 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 528484424 bytes
Database Buffers 297795584 bytes
Redo Buffers 2433024 bytes
Database mounted.
SQL> alter database archivelog; --启用归档
Database altered.
SQL> alter database flashback on; --启用闪回
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging; --日志强行归档
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
SQL>
4.在primary 库添加standbt日志文件(standby文件和redo文件大小一至,文件个数比redo多一个)
SQL> select group#,type,member,IS_RECOVERY_DEST_FILE from v$logfile;
GROUP# TYPE MEMBER IS_
---------- ------- ---------------------------------------- ---
3 ONLINE /u01/oradata/orcl/redo03.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
1 ONLINE /u01/oradata/orcl/redo01.log NO
SQL> alter database add standby logfile group 4 ('/u01/oradata/orcl/sredo04.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/oradata/orcl/sredo05.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/oradata/orcl/sredo06.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/oradata/orcl/sredo07.log') size 50m;
Database altered.
SQL> select * from v$logfile order by 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------------------------------------- ------- -------------------------------
1 ONLINE /u01/oradata/orcl/redo01.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
3 ONLINE /u01/oradata/orcl/redo03.log NO
4 STANDBY /u01/oradata/orcl/sredo04.log NO
5 STANDBY /u01/oradata/orcl/sredo05.log NO
6 STANDBY /u01/oradata/orcl/sredo06.log NO
7 STANDBY /u01/oradata/orcl/sredo07.log NO
7 rows selected.
SQL>
5.利用spfile文件内容生成pfile文件
SQL> create pfile='/tmp/2.txt' from spfile; --创建pfile文件,修改pfile文件的内容
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
6.修改pfile文件的内容
[oracle@dg1 ~]$ vim /tmp/2.txt
*.db_unique_name=tong --数据库节点的唯一名字
*.fal_server='cheng' --standby库的网络服务名
*.fal_client='tong' --primary库的网络服务名
*.standby_file_management=auto
*.log_archive_start=true
*.log_archive_config='dg_config=(tong,cheng)' --两个数据库节点的唯一名字
*.log_archive_dest_1='LOCATION=/u01/oradata/tong/archive valid_for=(all_logfiles,all_roles) db_unique_name=tong' --primary库的网络服务名
*.log_archive_dest_2='service=cheng LGWR SYNC AFFIRM valid_for=(online_logfiles,all_roles) db_unique_name=cheng' --standby库的网络服务名
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/u01/oradata/tong','/u01/oradata/tong'
*.db_file_name_convert='/u01/oradata/tong','/u01/oradata/tong'
[oracle@dg1 ~]$ mkdir -p /u01/oradata/tong/archive --存放归档文件
[oracle@dg1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 9 11:56:26 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/tmp/2.txt'; --利用pfile文件创建spfile文件
File created.
SQL> startup
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 536873032 bytes
Database Buffers 289406976 bytes
Redo Buffers 2433024 bytes
Database mounted.
Database opened.
SQL> alter database set standby database to maximize availability; --设置为最大性能模式
Database altered.
SQL>
8.备份数据库(备份文件在闪恢复区)
[oracle@dg1 dbs]$ rman target /
RMAN> backup database plus archivelog;
RMAN> backup current controlfile for standby;
镜像数据库：
rman target sys/oracle@orclpr auxiliary sys/oracle@orclst nocatalog
duplicate target database for standby from active database nofilenamecheck;
9.在standby服务器创建目录
[oracle@dg2 u01]$ cd /u01
[oracle@dg2 u01]$ mkdir flash_recovery_area oradata admin
[oracle@dg2 u01]$ mkdir -p /u01/admin/orcl/adump
[oracle@dg2 u01]$ mkdir -p /u01/admin/orcl/pfile
[oracle@dg2 u01]$ mkdir -p /u01/admin/orcl/dpdump
[oracle@dg2 u01]$ mkdir -p /u01/oradata/orcl
[oracle@dg2 u01]$ mkdir -p /u01/oradata/tong/archive --存放归档文件的目录
10.考贝文件到standby服务器
[oracle@dg1 dbs]$ cd /u01/flash_recovery_area/ --考贝闪回恢复区的备份文件
[oracle@dg1 flash_recovery_area]$ scp * oracle@dg2;/u01/flash_recovery_area/
[oracle@dg1 flash_recovery_area]$ cd /u01/product/11.2.0.1/db_1/dbs/
[oracle@dg1 dbs]$ scp *.ora orapwtong oracle@dg2:/u01/product/11.2.0.1/db_1/dbs/ --考贝pfile,spfile参数文件和密码文件(orapwtong)
[oracle@dg1 dbs]$ cd /u01/product/11.2.0.1/db_1/network/admin/
[oracle@dg1 admin]$ scp listener.ora tnsnames.ora oracle@dg2:/u01/product/11.2.0.1/db_1/network/admin/ --考贝监听文件
[oracle@dg1 admin]$
二.standby从库操作
11.修改监听的地址
[oracle@dg2 ~]$ cd /u01/product/11.2.0.1/db_1/network/admin/
[oracle@dg2 admin]$ vim listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cheng)
(ORACLE_HOME = /u01/product/11.2.0.1/db_1)
(SID_NAME = cheng)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.80)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01
[oracle@dg2 admin]$ vim tnsnames.ora
tong =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tong)
)
)
cheng =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.80)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cheng)
)
)
[oracle@dg2 admin]$ cd /u01/product/11.2.0.1/db_1/dbs/
[oracle@dg2 dbs]$ mv inittong.ora initcheng.ora --修改pfile文件名
[oracle@dg2 dbs]$ mv orapwtong orapwcheng --修改密码文件名
[oracle@dg2 dbs]$ lsnrctl stop
[oracle@dg2 dbs]$ lsnrctl start
12.恢复数据库
[oracle@dg2 ]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 16 17:06:55 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount --启动到nomount状态
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 490735688 bytes
Database Buffers 335544320 bytes
Redo Buffers 2433024 bytes
[oracle@dg2 ~]$ rman target sys/system@tong auxiliary / --恢复数据库
RMAN> duplicate target database for standby nofilenamecheck;
13.修改pfile参数文件
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@dg2 dbs]$ cd /u01/product/11.2.0.1/db_1/dbs/
[oracle@dg2 dbs]$ vim initcheng.ora
*.db_unique_name=cheng --数据库节点的唯一名字
*.fal_server='tong' --primary库节点的唯一名字
*.fal_client='cheng' --standby库节点的唯一名字
*.standby_file_management=auto
*.log_archive_start=true
*.log_archive_config='dg_config=(tong,cheng)'
*.log_archive_dest_1='LOCATION=/u01/oradata/tong/archive valid_for=(all_logfiles,all_roles) db_unique_name=cheng' --standby库网络服务名
*.log_archive_dest_2='service=tong LGWR SYNC AFFIRM valid_for=(online_logfiles,all_roles) db_unique_name=tong' --primary库网络服务名
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/u01/oradata/tong','/u01/oradata/tong'
*.db_file_name_convert='/u01/oradata/tong','/u01/oradata/tong'
[oracle@dg2 ]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 16 17:06:55 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL>create spfile from pfile;
File created.
SQL>
14.启动standby库
SQL> startup nomount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 490735688 bytes
Database Buffers 335544320 bytes
Redo Buffers 2433024 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database add standby logfile;
Database altered.
SQL> alter database add standby logfile;
Database altered.
SQL> alter database add standby logfile;
Database altered.
SQL> alter database add standby logfile;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
15.检查standby的日志
[root@dg2 ~]# tailf /u01/diag/rdbms/cheng/cheng/trace/alert_cheng.log
Thu Nov 17 13:08:20 2016
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (cheng)
Thu Nov 17 13:08:20 2016
MRP0 started with pid=30, OS id=5815
MRP0: Background Managed Standby Recovery process started (cheng)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Thu Nov 17 13:08:27 2016
Archiver process freed from errors. No longer stopped
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 11
Fetching gap sequence in thread 1, gap sequence 11-11
Thu Nov 17 13:08:27 2016
RFS[4]: Opened log for thread 1 sequence 11 dbid 1455843223 branch 928062493
Archived Log entry 9 added for thread 1 sequence 11 rlc 928062493 ID 0x56c6d297 dest 2:
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_11_928062493.dbf
Datafile 1 added to flashback set
Datafile 2 added to flashback set
Datafile 3 added to flashback set
Datafile 4 added to flashback set
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_12_928062493.dbf
Thu Nov 17 13:08:34 2016
Standby controlfile consistent with primary
RFS[2]: Selected log 6 for thread 1 sequence 22 dbid 1455843223 branch 928062493
Thu Nov 17 13:08:51 2016
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_13_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_14_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_15_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_16_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_17_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_18_928062493.dbf
Media Recovery Log /u01/product/11.2.0.1/db_1/dbs/arch2_19_928062493.dbf
Thu Nov 17 13:09:03 2016
Media Recovery Waiting for thread 1 sequence 20 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 20 Reading mem 0
Mem# 0: /u01/flash_recovery_area/TONG/onlinelog/o1_mf_4_d2r8rhpj_.log
Media Recovery Waiting for thread 1 sequence 21 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 21 Reading mem 0
Mem# 0: /u01/flash_recovery_area/TONG/onlinelog/o1_mf_5_d2r8rqpl_.log
16.验证dataguard是否成功
dg1节点:
[oracle@dg1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 13:50:58 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oradata/tong/archive
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26 --这个值与下面那个值相等就完成了
SQL>
dg2节点:
[oracle@dg2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 13:53:33 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 25
Next log sequence to archive 0
Current log sequence 26
SQL>
