實戰不停機搭建ORACLE DataGuard

一、基礎資訊

作業系統：Red Hat Enterprise Linux Server release 6.3 (Santiago)

資料庫：11.2.0.3

配置資訊：

二、準備工作

1、在主庫和備庫的host檔案中新增相應的解析，/etc/hosts

2、按照要求建立相應的主資料庫和備用資料庫，並刪除備用資料庫的資料檔案、控制檔案，兩個資料庫的所有路徑需要一致；

三、搭建資料庫

主庫的操作： 

1.確認主庫引數

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FOR

--------- -------------------- ---------------- ------------ ---

OREO      READ WRITE           PRIMARY          ARCHIVELOG   NO

SQL>show parameter name

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string

db_name                  string     orcl

db_unique_name                 string     orcldg1

global_names                 boolean     FALSE

instance_name                 string     orcl

lock_name_space              string

log_file_name_convert             string

processor_group_name             string

service_names                 string     orcldg1

2.設定資料庫的歸檔模式

SQL>archive log list;

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /u01/app/oracle/oradata/arch

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

歸檔模式的設定：

1、shutdown immediate；

2、alter database mount;

3、alter database archive log；

4、alter database open；

3.開啟force logging

SQL> alter database force logging;

SQL> select name,DB_UNIQUE_NAME,database_role,log_mode,force_logging from v$database;

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      LOG_MODE     FOR

--------- ------------------------------ ---------------- ------------ ----------------   ------

ORCL      orcldg1             PRIMARY      ARCHIVELOG   YES

4.配置監聽

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                20-FEB-2014 21:19:06

Uptime                    1 days 6 hr. 20 min. 13 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/OraDb11g_home1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))

Services Summary...

Service "dg_DGMGRL" has 1 instance(s).

  Instance "dg", status UNKNOWN, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orcldg1" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

5.配置主庫的TNSNAMES.ORA和密碼檔案到備庫

[oracle@dg1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/OraDb11g_home1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCLDG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg1)

    )

  )

ORCLDG2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg2)

       (UR=A)

    )

  )

傳輸監聽檔案到備庫：

scp tnsnames.ora oreostb:/u01/app/oracle/products/11.2.0/network/admin

oracle@oreostb's password: 

tnsnames.ora                                                                                      100%  517     0.5KB/s   00:00  

6.修改主庫引數

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldg1,orcldg2)';                                                            

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg1';  

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2';     

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';                                                                      

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';                                                                                                                                             

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';            

ALTER SYSTEM SET FAL_SERVER=orcldg1;      

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;

7.全備主庫,並傳輸備份集到備庫的相同位置

run

{

 allocate channel c0 device type disk;

 allocate channel c1 device type disk;

 CONFIGURE CONTROLFILE AUTOBACKUP ON;

 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rman/%F';

 backup  database format '/u01/rman/ora11g_full_db_%d_%T_%u.bak';

 BACKUP ARCHIVELOG ALL FORMAT '/u01/rman/ora11g_arc_%s_%p_%t.bak';

 }   

8.修改備庫引數

vi initoreo.ora

#DG CONFIG

*.log_archive_config='dg_config=(orcldg1,orcldg2)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg2'

*.log_archive_dest_2='SERVICE=orcldg1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'

*.standby_file_management='AUTO'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server=orcldg1

*.db_unique_name=orcldg2     

9.啟動備庫監聽

[oracle@dg2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 12-MAR-2014 19:34:22

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/OraDb11g_home1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/OraDb11g_home1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                12-MAR-2014 19:34:23

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/product/OraDb11g_home1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "dg_DGMGRL" has 1 instance(s).

  Instance "dg", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

10.進行備庫的恢復

[oracle@dg1 admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Feb 22 01:57:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1367831332)

RMAN>  connect auxiliary sys/oracle@orcldg2

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 22-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

   restore clone standby controlfile;

}

executing Memory Script

Starting restore at 22-FEB-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/c-1367831332-20140220-00

channel ORA_AUX_DISK_1: piece handle=/u01/rman/c-1367831332-20140220-00 tag=TAG20140220T223858

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Finished restore at 22-FEB-14

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to 

 "/u01/app/oracle/oradata/orcl/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/u01/app/oracle/oradata/orcl/system01.dbf";

   set newname for datafile  2 to 

 "/u01/app/oracle/oradata/orcl/sysaux01.dbf";

   set newname for datafile  3 to 

 "/u01/app/oracle/oradata/orcl/undotbs01.dbf";

   set newname for datafile  4 to 

 "/u01/app/oracle/oradata/orcl/users01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-FEB-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ora11g_full_db_ORCL_20140220_02p14lag.bak

channel ORA_AUX_DISK_1: piece handle=/u01/rman/ora11g_full_db_ORCL_20140220_02p14lag.bak tag=TAG20140220T223807

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ora11g_full_db_ORCL_20140220_01p14lag.bak

channel ORA_AUX_DISK_1: piece handle=/u01/rman/ora11g_full_db_ORCL_20140220_01p14lag.bak tag=TAG20140220T223807

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 22-FEB-14

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=841956993 file name=/u01/app/oracle/oradata/orcl/users01.dbf

Finished Duplicate Db at 22-FEB-14

12.將主庫的LOG_ARCHIVE_DEST_STATE_2設定為ENABLE

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENJABLE';

13.新增standby redo log

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/ORADATA/oreopdb/redo03.log

/ORADATA/oreopdb/redo02.log

/ORADATA/oreopdb/redo01.log

SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

no rows selected

#主庫增加 STANDBY LOGFILE

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;

#備庫增加STANDBY LOGFILE

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/ORADATA/oreostb/redo03.log

/ORADATA/oreostb/redo02.log

/ORADATA/oreostb/redo01.log

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE  GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;

14.開啟active dataguard

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

至此物理DATAGUARD已經建立完畢，後續將陸續介紹DATAGUARD的日常管理和操作，歡迎各位ORACLE愛好者關注。。。。。。

------------------------------------------------------------------------------------------------

IT技術員的理想發展曲線：技術員――技術管理――運營管理――企業戰略

個人理念：不做單純的技術，技術就做管理的技術

ORACLE技術管理QQ群：367875324

------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25954236/viewspace-1684176/，如需轉載，請註明出處，否則將追究法律責任。