oracle11gdataguard环境搭建

硬件和系统软件说明:

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：域名注册、虚拟主机、营销软件、网站建设、凉州网站维护、网站推广。

操作系统: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

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

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

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

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

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

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>

本文名称：oracle11gdataguard环境搭建
本文来源：http://scyanting.com/article/jjgoii.html

oracle11gdataguard环境搭建

其他资讯