谈下oracle11gR2 dataguard部署测试

搭建环境：
OS：Solaris10 x86-64
DB：Oracle 11.2.0
主库所在主机：sol01
备库所在主机：sol02

2 主库部署步骤
2.1 主库操作
创建归档目录，开启归档模式。
su – oracle
mkdir /orahome/oracle/archivelog
sqlplus /as sysdba
shutdown immediate
startup mount

alter database archivelog;

查看是否force_logging模式
SQL> select log_mode,force_logging from v$database;
开启force_logging模式
alter database force logging;

create pfile from spfile;

创建备库控制文件
alter database create standby controlfile as '/orahome/oracle/bak/controlfile01.ctl';

创建备库日志组路径
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;

cd $ORACLE_HOME/dbs
备份参数文件
cp inittest.ora  inittest.ora.bk
2.2 在inittest.ora参数文件中加入：
修改参数文件：
db_unique_name=test
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test'
log_archive_dest_2='service=test_dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test_dg
fal_client=test
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto

2.3 使更改参数生效：
sqlplus / as sysdba
startup nomount pfile= '$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
shutdown immediate
startup mount


2.4 把相应的文件SCP到standby主机：
控制文件：
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
scp /orahome/oracle/bak/controlfile01.ctl 192.168.0.22: $ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl
oracle口令文件：
scp orapwgisdb1 192.168.111.129:/home/oracle/u01/11.2.3/dbhome_1/dbs/orapwgisdb2
oracle数据文件：
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.dbf 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
oracle redo日志文件（不包括standyredo.log）
scp $ORACLE_BASE/oradata/$ORACLE_SID/*.log 192.168.0.22:$ORACLE_BASE/oradata/$ORACLE_SID/
参数文件
scp $ORACLE_HOME/dbs/inittest.ora 192.168.0.22:$ORACLE_HOME/dbs/
2.5 更改备库使用的pfile:inittest.ora
db_unique_name=test_dg
log_archive_config='dg_config=(test,test_dg)'
log_archive_dest_1='location=/orahome/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=test_dg'
log_archive_dest_2='service=test lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=test'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=test
fal_client=test_dg
db_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
log_file_name_convert='/orahome/oracle/app/oradata/test','/orahome/oracle/app/oradata/test'
standby_file_management=auto
2.6 配置监听：
 bash-3.00$ more tnsnames.ora
test =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = test)

    )

  )

test_dg =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = test_dg)

    )

  )
将tnsnames.ora文件拷贝到备库相同位置。
2.7 打开主库：

Alter database open;
日志如下：


3 从库上操作：
3.1 创建需要的目录
mkdir -p $ORACLE_BASE/oradata/test
mkdir -p $ORACLE_BASE/admin/test/{a,dp}dump
sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';
create spfile from pfile;
startup nomount


3.2 配置网络服务
启动监听(lsnrctl start),再tnsnames.ora中加入如下内容
bash-3.00$ more tnsnames.ora
test =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = test)

    )

  )

test_dg =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = test_dg)

    )

  )

查看tnsping是否互通

将备库启动到mount状态
alter database mount;

添加备库日志组
alter database add standby logfile group 4 '/orahome/oracle/app/oradata/test/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/orahome/oracle/app/oradata/test/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/orahome/oracle/app/oradata/test/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/orahome/oracle/app/oradata/test/standbyredo07.log' size 50m;
3.3 开启日志应用
alter database recover managed standby database disconnect from session;
相关LOG:

 alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test)
Thu Sep 24 09:01:44 2015
MRP0 started with pid=26, OS id=3901
MRP0: Background Managed Standby Recovery process started (test)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /orahome/oracle/archivelog/1_79_891106389.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 80 (in transit)
4 测试：
4.1 主库上：
Conn scott/tiger
SQL> create table a as select * from emp;
Table created.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL>
4.2 从库上操作：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

成功完成了同步

5 转换成ADG（active dataguard）
/备库 Database mounted.///
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;          
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

6 Swithover 测试：
6.1 检查主从库的信息：
主库上查询：
SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);
 
STATUS    GAP_STATUS
--------- ------------------------
VALID
VALID     NO GAP
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
此处的信息一定要显示是“to standby”
从库信息：
SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);
 
STATUS    GAP_STATUS
--------- ------------------------
VALID
VALID     NO GAP

6.2 开始switchover:
-- 主库的执行：
SQL> alter database commit to switchover to physical standby with session shutdown ;
 
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             339742040 bytes
Database Buffers           62914560 bytes
Redo Buffers                8486912 bytes
Database mounted.
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
 
SQL> alter database open;
 
Database altered.
 
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY

--- 从库上操行：
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
 
SQL> alter database commit to switchover to primary with session shutdown ;
 
Database altered.
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
MOUNTED
 
SQL> alter database open;
 
Database altered.


---- 在现在的从库上（之前的主库上执行）：
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ ONLY
 
SQL> alter database recover managed standby database using current logfile disconnect ;
 
Database altered.
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ ONLY WITH APPLY

6.3 切换后 再次测试：
--- 主上执行：
 SQL> conn scott/tiger
Connected.
SQL> create table b as select * from tab;

Table created.

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL>  conn scott/tiger
Connected.
SQL> select count(*) from a;

  COUNT(*)
----------
        14

SQL> insert into a select * from a;

14 rows created.

SQL> r
  1* insert into a select * from a

28 rows created.

SQL> r
  1* insert into a select * from a

56 rows created.

SQL> r
  1* insert into a select * from a

112 rows created.

SQL> select count(*) from a;

  COUNT(*)
----------
       224
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

查看主库角色
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY
再从库上查询：
 SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
B                              TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

6 rows selected.
SQL> r
  1* select count(*) from a

  COUNT(*)
----------
       224
查看备库角色
SQL> select database_role from v$database;

DATABASE_ROLE
----------------

PHYSICAL STANDBY

    DataGuard是一种数据库级别的HA方案,最主要功能是冗灾、数据保护、故障恢复等. 它是在主节点与备用节点间通过日志同步来保证数据的同步,可以实现数据库快速切换与灾难性恢复效率.Data Guard只是在软件上对数据库进行设置,并不需要额外购买任何组件.用户能够在对主数据库影响很小的情况下,实现主备数据库的同步.而主备机之间的数据差异只限于在线日志部分,因此被不少企业用作数据容灾解决方案 ，而RAC一般翻译为"真正应用集群",它一般有两台或者两台以上同构计算机及共享存储设备构成,可提供强大的数据库处理能力差异.简单说dataguard是为了安全而做的自动化备份,RAC是为了提高效率而做的多台电脑共同处理的集群,现在都把两者配合使用.