Oracle 10g 11g DataGuard 配置

1.方式 physic standby configuration 最大性能模式
2.目的 生产库比较大，如果用rman做备份如果哪一天坏掉恢复起来太慢。
  决定采用dataguard的容灾方案。
3.优点：在备机上做恢复，从主库上通过rman同步过来作，恢复速度快。Oracle11g一个重大的功能改进
  就是DATA GUARD可以在打开的情况下应用归档。这个功能使得STANDBY数据库可以轻松的替PRIMARY来分担
  查询的压力。
4.缺点：不能还原到某一时刻。
测试环境准备
1.主库也备库oracle版本一致，运行在相同平台如linux,服务器硬件可以不一样。
2.Primary 数据库必须运行于归档模式，并且务必确保在primary 数据库上打开FORCE LOGGING，
  以避免用户通过nologging 等方式避免写redo 造成对应的操作无法传输到standby 数据库。
3.Primary 和standby 数据库均可应用于单实例或RAC 架构下，并且同一个data guard 配置可以混合
  使用逻辑standby 和物理standby.
4.建议数据库必须采用相同的存储架构。比如存储采用ASM/OMF 的话，那不分primarty 或是standby
  也都需要采用ASM/OMF。
5.standby库的环境和primary一致
物理standby创建步
1.先安装primary库(oracle11g)
以sysdba登录primary库,开启归档,open状态。
设置primary库为force Logging模式(为了便于切换，建议standby库也设置为force logging),这样所有的改变都会放入重做日志中，确保了可靠的恢复。
SQL> ALTER DATABASE FORCE LOGGING;
在主库上创建备用日志 （为切换而用）
alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/u01/app/oracle/oradata/oracle11/stdy_redo05.log','/u01/arch/onlinelog/stdy_redo05.log') size 50m;
alter database add standby LOGFILE GROUP 6 ('/u01/app/oracle/oradata/oracle11/stdy_redo06.log','/u01/arch/onlinelog/stdy_redo06.log') size 50m;
alter database add standby LOGFILE GROUP 7 ('/u01/app/oracle/oradata/oracle11/stdy_redo07.log','/u01/arch/onlinelog/onlinelog/stdy_redo07.log') size 50m;
alter database recover managed standby database disconnect from session;
更改主库的pfile文件
*.db_name='oracle11'
*.DB_UNIQUE_NAME='oracle11'
*.log_archive_config='DG_CONFIG=(oracle11,guard2)' #oracle11,guard2是tns里配置名字.
*.log_archive_dest_1='location=/u01/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='oracle11'
*.LOG_ARCHIVE_DEST_2='SERVICE=guard2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle11'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
/*以下部分为主机切换为备库使用*/
*.FAL_SERVER=guard2
*.FAL_CLIENT=oracle11
*.DB_FILE_NAME_CONVERT='guard2','oracle11'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oracle11','/u01/app/oracle/oradata/oracle11'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.remote_login_passwordfile='EXCLUSIVE'

配置primary库的tnsname.ora
[oracle@modem admin]$ more tnsnames.ora
oracle11 =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.239)(PORT = 1521))
  )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = oracle11)
  )
)

guard2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle11)
    )
  )
配置primary库的listener.ora
[oracle@modem admin]$ more listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = oracle11)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.239)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

再次启动primary到mount状态。
SQL> shutdown immediate;
SQL> startup  mount;
用RMAN作一次全库备份，采用默认目录
创建备库的控制文件
SQL> alter database create standby controlfile as '/home/oracle/controlbak.ctl';
Database altered.
这样以上的主库已经配置完毕。

下面是建备库
1.把主库shutdown immediate
在备库上把主库的/u01/app /u01/arch  /etc/oraInst.loc  /etc/oratab /usr/local/bin/oraenv 拷贝过来。
更改备库相关配置
1.更改initoracle11.ora
*.control_files='/u01/app/oracle/oradata/oracle11/controlguard1.ctl','/u01/app/oracle/oradata/oracle11/
controlguard2.ctl','/u01/app/oracle/oradata/oracle11/controlguard3.ctl'
*.db_name='oracle11'
*.DB_UNIQUE_NAME='oracle11'
*.log_archive_config='DG_CONFIG=(oracle11,guard2)'
*.log_archive_dest_1='location=/u01/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='oracle11'
*.LOG_ARCHIVE_DEST_2='SERVICE=oracle11 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle11'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=oracle11  #注意与主库刚好相反
*.FAL_CLIENT=guard2    #注意与主库刚好相反
*.DB_FILE_NAME_CONVERT='guard2','oracle11'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oracle11','/u01/app/oracle/oradata/oracle11'
*.STANDBY_FILE_MANAGEMENT=AUTO

把主库上备份的控制文件/home/oracle/controlbak.ctl 对应复制三制保存为controlguard1.ctl,controlguard2.ctl,controlguard3.ctl
这样备库也做完了。

测试
先开启主备数据库的监听
sqlplus sys/****@oracle11 as sysdba
sqlplus sys/****@guard2   as sysdba
在主库和备库分别登录，如果能登录说明监听没问题。

在主库上 startup启动数据库到open
在备库上 startup mount 然后
启动redo 应用
SQL> alter database recover managed standby database disconnect from session;
查看同步情况
首先连接到primary 数据库
SQL> show parameter instance_name;
SQL> select sequence#, first_time, next_time, applied, fal from v$archived_log order by sequence#;


数据验证
最后，我们在主库上创建一个表并插入一些数据，然后模拟日志切换，最后在备库上验证数据的传输情况。
 主库上建测试表
SQL> create table randy (id number);
Table created.

SQL> insert into randy values(1);
1 row created.

SQL> /
1 row created.

SQL> /
1 row created.

SQL> commit;
Commit complete.

SQL> select * from randy;

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

 主库上模拟日志切换
SQL> alter system archive log current;

System altered.

 查看备库告警日志文件
Fri Mar 20 10:04:29 2009
MRP0 started with pid=24, OS id=7410
MRP0: Background Managed Standby Recovery process started (oracle11)
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Waiting for all non-current ORLs to be archived...
Media Recovery Log /u01/arch/1_483_673887649.dbf
Media Recovery Log /u01/arch/1_484_673887649.dbf
Media Recovery Waiting for thread 1 sequence 485 (in transit)
--可以发现主库的数据已经传送过来。

 以只读模式打开备库，验证数据
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from randy;

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

 恢复备库的standby状态
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Oracle11g一个重大的功能改进就是DATA GUARD可以在打开的情况下应用归档。这个功能使得STANDBY数据库可以轻松的替PRIMARY来分担查询的压力。
-----------------------------------------------------------------------------
主备库切换

 将主库切换到备用状态
SQL> alter database commit to switchover to physical standby;
Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes

SQL> alter database mount standby database;
Database altered.

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

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
GUARD1    MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

 将备库转换成主库模式
SQL> alter database commit to switchover to primary;
Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

 查看主备库告警日志文件