搭建ORACLE10G DATA GUARD--->Physical Standby

下面是我自己搭建的Physical Standby，按照我下面的步骤一步一步做，肯定能搭建成功的，下面的搭建步骤可以作为一个Data Guard的搭建操作手册。

HA和DG的区别：HA:可以做到IP自动切换  DG:做不到IP自动切换
主库和备库和RAC的接点个数没有关系,(生产库多少个节点,备库就是多少个节点),Oracle必须是企业版(Enterprise Edition)才可以做DG
DG级练 A->B->C(ABC可以相互切换)

主备库配置环境：
ORACLE VERSIOIN: Release 10.2.0.1.0
LINUX VERSION: Linux vmoel5u4.oracle.com 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux

192.168.92.100(primary) 
hostname:vmoel5u4.oracle.com
192.168.92.200(standby)
hostname:even.oracle.com

主库和备库和RAC的接点个数没有关系,(生产点接点,备库是多接点)什么情况下用:
Oracle必须是企业版(Enterprise Edition)才可以做DG

DG级练 A->B->C(ABC可以相互切换)

 

以下是primary database configuration:
主库上创建相应的目录为下面所用：
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/archivelog/orcl
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/archivelog/orclsby

把数据库启动到mount状态，开数据库归档
alter database archivvlog

alter system set DB_UNIQUE_NAME = ORCL SCOPE=SPFILE;

一定要确保/u01/app/oracle/archivelog/orcl路径存在
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_max_processes = 4;

打开闪回:一定要确保/u01/app/oracle/flash_recovery_area路径存在
alter system set db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area' scope=spfile;
alter system set db_recovery_file_dest_size = 4G;
alter system set db_flashback_retention_target = 1440;
shutdown immediate
startup mount
alter database flashback on;
alter database open;

**执行下下面(一定要加)
alter database force logging;

在主库添加standby log file(当然备库同样等会也要加standby log file)
---日志大小一定要大于等于online redo主库的大小
---standby redo log的组数一定要大于或等于online redo log
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redosby01.log') size 50M reuse;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redosby02.log') size 50M reuse;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redosby03.log') size 50M reuse;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redosby04.log') size 50M reuse;

SQL> select * from v$logfile order by group#;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         1 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo01.log                      NO
         2 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo02.log                      NO
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log                      NO
         4         STANDBY /u01/app/oracle/oradata/orcl/redosby01.log                   NO
         5         STANDBY /u01/app/oracle/oradata/orcl/redosby02.log                   NO
         6         STANDBY /u01/app/oracle/oradata/orcl/redosby03.log                   NO
         7         STANDBY /u01/app/oracle/oradata/orcl/redosby04.log                   NO

7 rows selected.

***primary listener.ora
[oracle@vmoel5u4 admin]$ vi listener.ora
LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=vmoel5u4.oracle.com)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = orcl.oracle.com)
    )
    (SID_DESC =
      (SID_NAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = orcldg.oracle.com)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

***primary tnsnames
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle.com)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmoel5u4.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle.com)
    )
  )

***standby listener.ora
LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = orcl.oracle.com)
    )
    (SID_DESC =
      (SID_NAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = orcldg.oracle.com)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

***standby tnsnames
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle.com)
    )
  )

ORCLPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmoel5u4.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oracle.com)
    )
  )

***Primary DB
alter system set dg_broker_start = false;(false表示不用broker)
alter system set remote_login_passwordfile = exclusive scope=spfile;
alter system set instance_name = orcl scope=spfile;
alter system set db_unique_name = ORCL scope=spfile;
alter system set service_names = 'orcl.oracle.com';
alter system set log_archive_config = 'DG_CONFIG=(orcl,orcldg)';(一定要配置)
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_2 = 'SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2
REOPEN=300 NOMAX_FAILURE';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = defer;
alter system set archive_lag_target = 1800;
alter system set log_archive_max_processes = 4;
alter system set standby_archive_dest = '/u01/app/oracle/archivelog/orclsby';   ---确保路径存在哦
alter system set standby_file_management = auto;
alter system reset db_create_file_dest scope=spfile sid='*';   --如果db_create_file_dest为空的话，回提示ORA-32010: cannot find entry to delete in SPFILE，可以ignore
alter system set fal_server = orcldg;(永远指向别人)
alter system set fal_client = orcl;(永远指向自己)
create pfile from spfile;

 

/*<------FAL_SERVER and FAL_CLIENT
FAL是Fetch Archive Log的简写，它是dataguard主备之间GAP的处理机制。
Primary上不会有GAP，所以fal_server和fal_client也是只在standby上生效的参数，当然为了switch over的需要同样会在primary端进行预设置。
FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name.
------->*/

SQL> shutdown immediate
***shutdown immediate 后拷贝如下几个文件(控制文件,归档,临时,闪回文件不拷贝)(考试的时候千万不要拷贝到别人的电脑上去了)
/*拷贝之前一定要到备库上去把相应的路径目录给创建好，否则在后面的操作中会提示目录不存在的*/
下面是在备库standby创建目录：
[oracle@even ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@even ~]$ mkdir -p /u01/app/oracle/admin/orcl/{a,b,c,u,dp}dump;
[oracle@even ~]$ mkdir -p /u01/app/oracle/archivelog/orcl
[oracle@even ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

下面是从主库上拷贝文件到备库上：
 scp /u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora   even.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
 scp /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl      even.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
 scp /u01/app/oracle/oradata/orcl/*.dbf                     even.oracle.com:/u01/app/oracle/oradata/orcl/  
 scp /u01/app/oracle/oradata/orcl/*.log                     even.oracle.com:/u01/app/oracle/oradata/orcl/

拷贝完成后千万不要立即启动数据库(因为控制文件没有拷贝,所以pfile,主备库的pfile会不一致)
startup mount(千万不要startup,否则重新拷贝上面的内容)

alter database create standby controlfile as '/u01/app/oracle/oradata/orcl/control_sby.ctl' reuse;(千万不要写错了control_sby.ctl reuse)

alter database open;

(从主库拷贝primary的控制文件到standby)
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control01.ctl
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control02.ctl
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control03.ctl

 

***备库(此时数据库是没有打开的)
cd $ORACLE_HOME/dbs
vi initorcl.ora(修改一些参数)
*.db_unique_name='ORCLDG'
*.fal_client='ORCL'
*.fal_server='ORCLPRI'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

create spfile from pfile;
startup mount;
(standby )
SQL> alter database recover managed standby database disconnect;   /*alter database recover managed standby database cancel; (这个是关掉)*/
Database altered.

在主备库上都打开跟踪文件：
tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log

(以下5条命令在primary)
alter system set log_archive_dest_state_2 = enable;
(切换的此时是redo的数目加1)
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
(此时在standby库的跟踪文件一定要看到Media Recovery Log 才算成功,类似如下：)
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_5_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_6_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_7_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_8_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_9_847844205.arc
Media Recovery Waiting for thread 1 sequence 10

(下面一条在primary和standby执行,查看)
select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

/*下面是在备库查看的信息*/
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED    PHYSICAL STANDBY NOT ALLOWED          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

/*下面是在主库查看的信息*/
SQL> set linesize 200
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY          TO STANDBY           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

/*下面主库测试dataguard数据库传输*/
drop table hr.t1 purge;
create table hr.t1 (id number);
insert into hr.t1 values (1);
commit;
alter system archive log current;

/*下面是在standby备库上操作的sql statement*/
alter database recover managed standby database cancel;  /*让备库置于不redo apply的状态*/
alter database open read only;    /*让备库置于read only 可以查询的状态*/
select * from hr.t1;(如果查询到数据就成功)
SQL> select * from hr.t1;

        ID
----------
         1

/*standby备库上，让备库处于redo apply的状态*/
alter database recover managed standby database disconnect from session parallel 4;
可提高media recover的速度，每次recover有4个并行度，这是10g的新特性

 

(primary)(修改成MAXIMUM PROTECTION)
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2

REOPEN=300 NOMAX_FAILURE';

(standby)
SQL> alter system set log_archive_dest_2='SERVICE=orclpri LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl NODELAY MAX_CONNECTIONS=2

REOPEN=300 NOMAX_FAILURE';
(以上两条千万不能写错了，否则主库会suspend)

 

/*下面是设置standby 在maximun protection 下*/
(primary)
shutdown immediate
startup mount
alter database set standby to maximize protection;
alter database open;

(primary & standby都看以下是否是maximun protection)
select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

/*下面是在主库查看的信息*/
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY          TO STANDBY           MAXIMUM PROTECTION   MAXIMUM PROTECTION

/*下面是在备库查看的信息*/
SQL> set linesize 200
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;

OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED    PHYSICAL STANDBY NOT ALLOWED          MAXIMUM PROTECTION   MAXIMUM PROTECTION

 

(把备库network disconnect)
在主库
insert into hr.t1 values (2);
commit;
此时主库被SUSPEND了.

把standby network connect后,主库就commit successfully.
所以这是maximun protection的缺点.

 

switchover between primary and standby
(primary:
shutdown immediate
startup restrict;(一定要是这种模式,不允许有连的,因为考试的时候grid control 的sysman是连着的)
select switchover_status from v$database;(switchover_status 是to standby 状态就可以切换了)
alter database commit to switchover to physical standby;
shutdown immediate
startup mount;(startup 是起不来的,因为是2个备库)
)

(standby:)
SQL> select switchover_status from v$database;   /*如果是to primary就正常了,否则就问题大了*/

SWITCHOVER_STATUS
--------------------
TO PRIMARY

alter database commit to switchover to primary;
shutdown immediate;(因为控制文件主备格式不一样,必须shutdown)

(在原主库上即vmoel5u4.oracle.com主机上,此时它是备库了)
sql>alter database recover managed standby database disconnect; )

(在原备库上即even.oracle.com主机上,此时它是主库了)
sql>startup

到目前位置切换成功
可以在测试下数据是否可以在主备之间顺利传输。

 

再切回去一样的步骤.注意主备库不用搞乱了.
switchover between primary and standby
(primary:在even.oracle.com
shutdown immediate
startup restrict;(一定要是这种模式,不允许有连的,因为考试的时候grid control 的sysman是连着的)
select switchover_status from v$database;(switchover_status 是to standby 状态就可以切换了)
alter database commit to switchover to physical standby;
shutdown immediate
startup mount;(startup 是起不来的,因为是2个备库)
)

(standby: 在vmoel5u4.oracle.com)
SQL> select switchover_status from v$database;   /*如果是to primary就正常了,否则就问题大了*/

SWITCHOVER_STATUS
--------------------
TO PRIMARY

alter database commit to switchover to primary;
shutdown immediate;(因为控制文件主备格式不一样,必须shutdown)

(在原主库上即even.oracle.com主机上,此时它是备库了)
sql>alter database recover managed standby database disconnect; )

(在原备库上即vmoel5u4.oracle.com主机上,此时它是主库了)
sql>startup
到此为止，我们有顺利的切回来了！

注意点：
先关主库,再关备库,备库先要cancel(alter database recover managed standby database cancel;)再shutdown.
开的时候是反的.备库先要cancel(alter database recover managed standby database disconnect;)

如何清掉DG：
primary:

alter database set standby database to maximize performance;
select protection_mode, protection_level from v$database;
alter system set log_archive_dest_state_2=defer;

shutdown immediate;
startup;

standby
rman: target /
rman:shutdown immediate
sql:startup mount restrict
rman:drop database;