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>