安装 ORACLE 12C 单节点RAC

装过ORACLE 12C RAC 的朋友应该感觉到12C的RAC简直就是一个怪物,需要消耗太多的内存、IO、CPU资源,在没有物理机器的情况下,使用虚拟机装ORACLE 12C RAC那可能需要比较好的主机资源(8G的内存不运行主机,仅仅够2个节点的虚拟机运行,所以8G的内存主机基本上无法ORACLE 12C 2节点RAC),而没有好的资源情况下,又需要玩12C RAC功能的朋友,我这里展示了单节点RAC(一个节点的RAC的rac),这里主要显示的是单节点RAC和多节点RAC安装不同之处,同时这里的安装也仅仅是为了玩
内存要求
12c_rac_require


准备环境

[oracle@xifenfei ~]$ more /etc/oracle-release 
Oracle Linux Server release 5.8

[oracle@xifenfei ~]$ free -m
             total       used       free     shared    buffers     cached
Mem:          4350       4036        313          0          4       2805
-/+ buffers/cache:       1226       3124
Swap:         2047        853       1193

[oracle@xifenfei ~]$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:02:1B:A7  
          inet addr:192.168.30.22  Bcast:192.168.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:66152 errors:0 dropped:0 overruns:0 frame:0
          TX packets:83647 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:21143877 (20.1 MiB)  TX bytes:44756747 (42.6 MiB)

eth1      Link encap:Ethernet  HWaddr 00:0C:29:02:1B:B1  
          inet addr:10.10.30.22  Bcast:10.10.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:8064 errors:0 dropped:0 overruns:0 frame:0
          TX packets:366 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:645158 (630.0 KiB)  TX bytes:64125 (62.6 KiB)

[oracle@xifenfei ~]$ more /etc/hosts
127.0.0.1       localhost.localdomain localhost

10.10.30.22     xifenfei-priv

192.168.30.22   xifenfei
192.168.30.32   xifenfei-vip
192.168.30.42   scan-ip

[root@xifenfei ~]# yum install oracle-validated
[root@xifenfei rpm]# rpm -ivh cvuqdisk-1.0.9-1.rpm

安装GRID软件
single_rac_gi0.jpg
single_rac_gi1
single_rac_gi2.jpg


安装ORACE DB软件
single_rac_db1


创建数据库
single_rac_dbca


安装结果

[root@xifenfei ~]# crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.DATA.dg
               ONLINE  ONLINE       xifenfei                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.net1.network
               ONLINE  ONLINE       xifenfei                 STABLE
ora.ons
               ONLINE  ONLINE       xifenfei                 STABLE
ora.proxy_advm
               ONLINE  ONLINE       xifenfei                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       xifenfei                 169.254.243.16 10.10
                                                             .30.22,STABLE
ora.asm
      1        ONLINE  ONLINE       xifenfei                 STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cdb.db
      1        ONLINE  ONLINE       xifenfei                 Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       xifenfei                 Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.xifenfei.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
--------------------------------------------------------------------------------

[root@xifenfei ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:02:1B:A7  
          inet addr:192.168.30.22  Bcast:192.168.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:66419 errors:0 dropped:0 overruns:0 frame:0
          TX packets:83849 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:21168865 (20.1 MiB)  TX bytes:44798962 (42.7 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:02:1B:A7  
          inet addr:192.168.30.42  Bcast:192.168.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth0:2    Link encap:Ethernet  HWaddr 00:0C:29:02:1B:A7  
          inet addr:192.168.30.32  Bcast:192.168.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 00:0C:29:02:1B:B1  
          inet addr:10.10.30.22  Bcast:10.10.30.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:12244 errors:0 dropped:0 overruns:0 frame:0
          TX packets:368 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:994988 (971.6 KiB)  TX bytes:64536 (63.0 KiB)

eth1:1    Link encap:Ethernet  HWaddr 00:0C:29:02:1B:B1  
          inet addr:169.254.243.16  Bcast:169.254.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:91800 errors:0 dropped:0 overruns:0 frame:0
          TX packets:91800 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:68999524 (65.8 MiB)  TX bytes:68999524 (65.8 MiB)

dul支持ORACLE 12C CDB数据库恢复

熟悉dul的朋友都知道dul是通过file# 1 block 1的kcvfhrdb找到bootstarp$的segment header(其实kcvfhrdb就是bootstarp$ segment header的rdba地址),然后通过bootstarp$中存储的相关sql找对一些基础的基表对象(obj$,tab$,col$,seg$等),然后通过他们定位到具体的对象的segment记录,从而通过segment找到extent分布,然后按照extent恢复数据(如果丢失system的情况,是通过扫描来确定extent属于哪个segment,然后恢复,该情况不在本次讨论范围之类)。在ORACLE 12C之前,一个实例最多都只有一个数据库,也就是说,在一个完整的数据库中只会存在一个bootstarp$,只要通过file# 1 block 1 定位到kcvfhrdb就可以读取数据库中的所有内容.但是从12C开始数据库引入了CDB的概念,也就是在一个CDB数据库中有了多个PDB数据库,那这些PDB数据库如果要编写类似dul之类工具将如何恢复出来,这里根据自己对于CDB的理解,先普及一些在CDB数据库中和bootstarp$表有关知识
bootstarp$表在每个PDB中都存在,可以通过bbed证明

--查看pdb相关信息
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO
         5 ORA11G                         MOUNTED

SQL>  select con_id,header_file,header_block from cdb_segments where segment_name='BOOTSTRAP$';

    CON_ID HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
         4          11          520
         1           1          520
         2           5          520
----因为有部分库未read write,所以查询cdb_segments未显示

--file 1
RMAN> copy datafile 1 to '/tmp/system_01.dbf';

BBED> set block 1
        BLOCK#          1

BBED> map
 File: /tmp/system_01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 1112 bytes                   @0       

 ub4 tailchk                                @8188    

BBED> p kcvfhrdb
ub4 kcvfhrdb                                @96       0x00400208

SQL> select to_number('400208','xxxxxxxxxx') from dual;

TO_NUMBER('400208','XXXXXXXXXX')
--------------------------------
                         4194824

SQL> select dbms_utility.data_block_address_block(4194824) "block",
  2  dbms_utility.data_block_address_file(4194824) "file" from dual;

     block       file
---------- ----------
       520          1

----可以知道bootstarp$起点的rdba为4194824,在rfile# 1 block# 520上

--file 11
RMAN> copy datafile 11 to '/tmp/system_11.dbf';

BBED> set filename '/tmp/system_11.dbf'
        FILENAME        /tmp/system_11.dbf

BBED> set block 1
        BLOCK#          1

BBED> p kcvfhrdb
ub4 kcvfhrdb                                @96       0x00400208

---显示的rdba地址完全与file# 1中的kcvfhrdb相同,也就是表示rfile# 1 block# 520

--验证未mount pdb,并且从11.2.0.4升级到12.1.0.1
ASMCMD> cp system01.dbf /tmp/system_18.dbf
copying +data/ora11g/system01.dbf -> /tmp/system_18.dbf

BBED> set filename '/tmp/system_18.dbf'
        FILENAME        /tmp/system_18.dbf

BBED>  set block 1
        BLOCK#          1

BBED> p kcvfhrdb
ub4 kcvfhrdb                                @96       0x0041ad40

SQL> select to_number('41ad40','xxxxxxxxx') from dual;

TO_NUMBER('41AD40','XXXXXXXXX')
-------------------------------
                        4304192

SQL> select dbms_utility.data_block_address_block(4304192) "block",
  2  dbms_utility.data_block_address_file(4304192) "file" from dual;

     block       file
---------- ----------
    109888          1
----可以知道bootstarp$起点的rdba为4304192,在rfile# 1 block# 109888上

查询contrainer$视图确认bootstarp$

SQL> select a.con_id#, a.dbid, a.rdba, dbms_utility.data_block_address_file(a.rdba) "file",
2    dbms_utility.data_block_address_block(a.rdba) "block"from container$ a;

   CON_ID#       DBID       RDBA       file      block
---------- ---------- ---------- ---------- ----------
         1 1922813718    4194824          1        520
         5 4211303690    4304192          1     109888
         2 4048821679    4194824          1        520
         4 3872456618    4194824          1        520
         3 3313918585    4194824          1        520

通过上面的知识点,我们明确,在ORACLE 12C CDB设计理念中,为了和12C之前的版本兼用(12C之前的版本可以通过PDB插入到CDB中),也为了方便用户在操作PDB时候和传统数据库一样,没有任何区别,所以它把每个PDB的rdba的计算方法认为PDB内部的RELFILE#是从1开始(也就是说每个rdba都是相对于自己的pdb而言),所以这里的contrainer$查询出来的rdba的地址就比较好理解(并非是绝对文件号,而是相对文件号,即表示pdb的第一个数据文件[传统的system01.dbf])

rdba中的file#和cdb中的file#关系

SQL> show con_name;

CON_NAME
------------------------------
PDB2
SQL> select file#, RELFILE#   from file$;

     FILE#   RELFILE#
---------- ----------
        12          4
        11          1
        13         13

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> select file#, RELFILE#   from file$;

     FILE#   RELFILE#
---------- ----------
         1          1
         3          3
         5
         6          6
         2
         4          4

6 rows selected.

通过这里的分析,就可以清晰的知道当前的dul是完全可以处理ORACLE 12C的CDB数据库.

dul恢复CDB中PDB数据

--在pdb中创建测试表
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO
         5 ORA11G                         MOUNTED

SQL> alter session set container=pdb2;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB2

SQL> show con_id

CON_ID
------------------------------
3

SQL> create user xff identified by xifenfei;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> create table xff.t_xifenfei tablespace users 
  2  as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from xff.t_xifenfei;

  COUNT(*)
----------
     90756

--使用dul抽取数据
[oracle@xifenfei dul]$ ./dul


 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Disk group DATA, dul group_cid 0
Discovered disk /dev/sdb as diskgroup DATA, disk number 0 size 20480 Mb File1 starts at 10, dul_disk_cid 0

DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty
Probing for attributes in File9, the attribute directory, for disk group DATA
attribute name "_extent_sizes", value "1 4 16"
attribute name "_extent_counts", value "20000 20000 214748367"
Oracle data file size 283123712 bytes, block size 8192
Found db_id = 1922813718
Found db_name = CDB
Oracle data file size 713039872 bytes, block size 8192
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   90758 rows unloaded
. unloading table                      TAB$    2363 rows unloaded
. unloading table                      COL$  106731 rows unloaded
. unloading table                     USER$     124 rows unloaded
Reading USER.dat 124 entries loaded
Reading OBJ.dat 90758 entries loaded and sorted 90758 entries
Reading TAB.dat 2363 entries loaded
Reading COL.dat 106685 entries loaded and sorted 106685 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 692, file 1 block 4528
 INDPART$: segobjno 697, file 1 block 4568
 TABCOMPART$: segobjno 714, file 1 block 9880
 INDCOMPART$: segobjno 719, file 0 block 0
 TABSUBPART$: segobjno 704, file 1 block 9928
 INDSUBPART$: segobjno 709, file 0 block 0
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 619, tabno 1, file 1  block 1528
 COLLECTION$: segobjno 619, tabno 2, file 1  block 1528
 ATTRIBUTE$: segobjno 619, tabno 3, file 1  block 1528
 LOBFRAG$: segobjno 725, file 1 block 4616
 LOBCOMPPART$: segobjno 728, file 0 block 0
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 126, file 1 block 1096
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   90758 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2363 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  106731 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     124 rows unloaded
. unloading table                  TABPART$     234 rows unloaded
. unloading table                  INDPART$     155 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded

DUL: Error: dc_segment_header(dataobj#=719, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table INDCOMPART$
. unloading table               TABSUBPART$      32 rows unloaded

DUL: Error: dc_segment_header(dataobj#=709, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table INDSUBPART$
. unloading table                      IND$    4237 rows unloaded
. unloading table                     ICOL$    6290 rows unloaded
. unloading table                      LOB$     849 rows unloaded
. unloading table                  COLTYPE$    2567 rows unloaded
. unloading table                     TYPE$    3651 rows unloaded
. unloading table               COLLECTION$    1345 rows unloaded
. unloading table                ATTRIBUTE$   13755 rows unloaded
. unloading table                  LOBFRAG$       6 rows unloaded

DUL: Error: dc_segment_header(dataobj#=728, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table LOBCOMPPART$
. unloading table                     UNDO$       1 row  unloaded
. unloading table                       TS$       4 rows unloaded
. unloading table                    PROPS$      38 rows unloaded
Reading USER.dat 124 entries loaded
Reading OBJ.dat 90758 entries loaded and sorted 90758 entries
Reading TAB.dat 2363 entries loaded
Reading COL.dat 106685 entries loaded and sorted 106685 entries
Reading TABPART.dat 234 entries loaded and sorted 234 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 155 entries loaded and sorted 155 entries
Reading IND.dat 4237 entries loaded
Reading LOB.dat 849 entries loaded
Reading ICOL.dat 6290 entries loaded
Reading COLTYPE.dat 2567 entries loaded
Reading TYPE.dat 3651 entries loaded
Reading ATTRIBUTE.dat 13755 entries loaded
Reading COLLECTION.dat
DUL: Warning: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
 1345 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 6 entries loaded and sorted 6 entries
Reading UNDO.dat 1 entries loaded
Reading TS.dat 4 entries loaded
Reading PROPS.dat 38 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> unload table xff.t_xifenfei;
. unloading table                T_XIFENFEI   90756 rows unloaded

核对结果

SQL> create table xff.t_xifenfei_new as select  * from xff.t_xifenfei where 1=0;

Table created.

[oracle@xifenfei dul]$ sqlldr xff/xifenfei@pdb2 control=XFF_T_XIFENFEI.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 2 18:08:04 2013

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

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
…………
Commit point reached - logical record count 90589
Commit point reached - logical record count 90653
Commit point reached - logical record count 90717
Commit point reached - logical record count 90756

Table "XFF"."T_XIFENFEI_NEW":
  90756 Rows successfully loaded.

Check the log file:
  XFF_T_XIFENFEI.log
for more information about the load.

SQL> select count(*) from xff.t_xifenfei_new;

  COUNT(*)
----------
     90756

通过分析12C的bootstarp$表分布,和dul恢复数据库原理,通过变动实现dul完美恢复CDB中的pdb数据

11G RAC TO 11G RAC ADG配置

RDBMS tnsnames.ora配置
在主备库的数据库的tns中均增加如下配置

q9dbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

q9adgdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

主库修改参数配置

alter system set DB_UNIQUE_NAME=q9db scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9adg1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db_adg';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9dbdg';
alter system set fal_server='q9adgdg';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

备库修改参数配置

alter system set DB_UNIQUE_NAME=q9db_adg scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db_adg';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9db1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9adgdg';
alter system set fal_server='q9dbdg';
alter system set service_names='q9db';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

主库增加standby redo log

alter database add standby logfile THREAD 1 group 51 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 1 group 61 '+DATA' size 512m;

alter database add standby logfile THREAD 2 group 71 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 2 group 81 '+DATA' size 512m;

主库修改强制日志

SQL> ALTER DATABASE FROCE LOGGING;

RMAN备份主库

rman target /
run{
configure device type disk parallelism 16;
backup database filesperset = 10  format '/q9bak/df_%U';
backup current controlfile format '/q9bak/ctl_%U';
}

创建standby controlfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/q9bak/stb_ctl';

还原standby controlfile

ASMCMD> cp /q9bak/stb_ctl control01.ctl
copying /q9bak/stb_ctl -> +data/Q9DB/control01.ctl
ASMCMD> cp /q9bak/stb_ctl control02.ctl
copying /q9bak/stb_ctl -> +arch/q9db/control02.ctl

还原备库

SQL>startup mount;

rman target / nocatalog <<EOF
run{
configure default device type to disk;
configure device type disk parallelism 16;
restore database;
}
<<EOF

修改备库文件名

RMAN>catalog start with '+DATA/Q9DB/DATAFILE/';
RMAN> switch database to copy;

清理redo

--清理redo(所有的group#)
SQL> select group# from v$log; 
SQL> alter database clear logfile group 1; 
……

--清理standby redo(所有的group#)
SQL> select group# from v$standby_log; 
SQL> alter database clear logfile group 51; 
……

开启备库实时应用模式

SQL> alter database recover managed standby database using current logfile disconnect from session;

备库注册DB服务(oracle用户)

srvctl add database -d q9db_adg -o $ORACLE_HOME 
srvctl add instance -d q9db_adg -i q9db1 -n q9adg01
srvctl add instance -d q9db_adg -i q9db2 -n q9adg02
srvctl modify database -d q9db_adg -a DATA,ARCH

11G RAC TO 11G RAC ADG SWITCHOVER

11G RAC TO 11G RAC ADG切换过程
主库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PRIMARY          READ WRITE
         1 PRIMARY          READ WRITE

[oracle@q9db02 ~]$ srvctl stop instance -d q9db -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PRIMARY          READ WRITE

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

备库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PHYSICAL STANDBY READ ONLY WITH APPLY
         1 PHYSICAL STANDBY READ ONLY WITH APPL

[oracle@q9adg02 ~]$ srvctl stop instance -d q9db_adg -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PHYSICAL STANDBY READ ONLY WITH APPL


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

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

Database altered.

主库切换日志,观察备库

--主库
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

--备库
[oracle@q9adg01 trace]$ tail -f alert_q9db1.log 
Tue Jun 25 15:35:27 2013
RFS[10]: Selected log 52 for thread 1 sequence 4777 dbid 844605368 branch 817913807
Tue Jun 25 15:35:28 2013
Archived Log entry 4889 added for thread 1 sequence 4776 ID 0x3545ffea dest 1:
Tue Jun 25 15:35:28 2013
Media Recovery Waiting for thread 1 sequence 4777 (in transit)
Tue Jun 25 15:35:28 2013
RFS[11]: Selected log 72 for thread 2 sequence 1630 dbid 844605368 branch 817913807
Recovery of Online Redo Log: Thread 1 Group 52 Seq 4777 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_52.1564.818724635
Media Recovery Waiting for thread 2 sequence 1630 (in transit)
Recovery of Online Redo Log: Thread 2 Group 72 Seq 1630 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_72.1575.818724653
Tue Jun 25 15:35:30 2013
Archived Log entry 4890 added for thread 2 sequence 1629 ID 0x3545ffea dest 1:

几乎同步进行表示主备日志传输应用正常

主库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;


Database altered.

备库切换

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> ALTER DATABASE OPEN;

Database altered.

继续处理主库

SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Data Mining
and Real Application Testing options
[oracle@q9db01 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 25 14:13:58 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.5770E+10 bytes
Database Buffers         1.3422E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


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> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

--备库
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.7380E+10 bytes
Database Buffers         1.3261E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

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


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.

启动主备另外节点

--主库
[oracle@q9db01 ~]$ srvctl start instance -d q9db -i q9db2
--备库
[oracle@q9adg02 ~]$ srvctl start instance -d q9db_adg -i q9db2

补充说明:如果出现日志切换暂时不能传输

备库执行(因为重启动态监听没有马上别识别)
alter system register;
主库执行
alter system set log_archive_dest_state_2=enable;

在11GR2 GI上配置第二个监听

有客户因为归档日志量每天很大,为了不影响业务,需要配置一个单独的万兆网络来专门的传输归档日志到DG库,这里就涉及到在11G(11203 Linux) RAC中增加一个监听用来使用专门的网络.这里提供在主库配置第二个监听的整体操作过程,主要涉及配置解析,增加网络,增加vip,配置监听,配置listener_networks
网卡情况

[oracle@q9db01 admin]$ /sbin/ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 90:E2:BA:1E:14:34  
          inet addr:192.168.5.60  Bcast:192.168.5.255  Mask:255.255.255.0
          inet6 addr: fe80::92e2:baff:fe1e:1434/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3932094203 errors:0 dropped:0 overruns:0 frame:0
          TX packets:176073749 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:5752649063554 (5.2 TiB)  TX bytes:31298228144 (29.1 GiB)

[grid@q9db02 ~]$ /sbin/ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 90:E2:BA:1E:13:5C  
          inet addr:192.168.5.61  Bcast:192.168.5.255  Mask:255.255.255.0
          inet6 addr: fe80::92e2:baff:fe1e:135c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:22861938 errors:0 dropped:0 overruns:0 frame:0
          TX packets:187447459 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:2603356463 (2.4 GiB)  TX bytes:276672018723 (257.6 GiB)

配置hosts文件

#public dg ip
192.168.5.60     q9db01-dg
192.168.5.61     q9db02-dg

192.168.5.64    q9db01-dg-vip
192.168.5.65    q9db02-dg-vip

CRS中配置

--增加网络资源
[root@q9db01 ~]# srvctl add network -k 2 -S 192.168.5.0/255.255.255.0/eth2 -w static -v

--启动网络资源
[root@q9db01 ~]# crsctl start res ora.net2.network

--增加vip资源
[root@q9db01 ~]# srvctl add vip -n q9db01 -A 192.168.5.64/255.255.255.0 -k 2
[root@q9db01 ~]# srvctl add vip -n q9db02 -A 192.168.5.65/255.255.255.0 -k 2

--启动vip资源
[root@q9db01 ~]# srvctl start vip -i q9db01-dg-vip
[root@q9db01 ~]# srvctl start vip -i q9db02-dg-vip

--netca创建监听
[root@q9db01 ~]# su - grid
[grid@q9db01 ~]$ export DISPLAY=172.18.50.150:0.0
[grid@q9db01 ~]$ netca
------选择Subnet 2(选择网络192.168.5.60网段),选择非1521端口---------

--查看资源状态
[grid@q9db01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.DATA.dg
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.LISTENER_DG.lsnr
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.OCR_VOTE.dg
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.asm
               ONLINE  ONLINE       q9db01                   Started             
               ONLINE  ONLINE       q9db02                   Started             
ora.gsd
               OFFLINE OFFLINE      q9db01                                       
               OFFLINE OFFLINE      q9db02                                       
ora.net1.network
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.net2.network
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.ons
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
ora.registry.acfs
               ONLINE  ONLINE       q9db01                                       
               ONLINE  ONLINE       q9db02                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       q9db01                                       
ora.cvu
      1        ONLINE  ONLINE       q9db01                                       
ora.oc4j
      1        ONLINE  ONLINE       q9db01                                       
ora.q9db.db
      1        ONLINE  ONLINE       q9db01                   Open                
      2        ONLINE  ONLINE       q9db02                   Open                
ora.q9db01-dg-vip.vip
      1        ONLINE  ONLINE       q9db01                                       
ora.q9db01.vip
      1        ONLINE  ONLINE       q9db01                                       
ora.q9db02-dg-vip.vip
      1        ONLINE  ONLINE       q9db02                                       
ora.q9db02.vip
      1        ONLINE  ONLINE       q9db02                                       
ora.scan1.vip
      1        ONLINE  ONLINE       q9db01                        

--查看监听状态
[grid@q9db01 ~]$ lsnrctl status listener_dg

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-JUN-2013 14:40:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                19-JUN-2013 14:38:43
Uptime                    0 days 0 hr. 1 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/q9db01/listener_dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.64)(PORT=1522)))
The listener supports no services
The command completed successfully

配置listener_networks

--在RDBMS的tnsnames.ora中配置
Q9DB01_LOCAL_NET1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-vip )(PORT = 1521)))

Q9DB01_LOCAL_NET2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip )(PORT = 1522)))

Q9DB02_LOCAL_NET1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-vip )(PORT = 1521)))

Q9DB02_LOCAL_NET2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip )(PORT = 1522)))

Q9DB_REMOTE_NET2 =(DESCRIPTION_LIST =(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip )
(PORT = 1522)))(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip )(PORT = 1522))))

--配置listener_networks
----节点1
SQL> ALTER SYSTEM SET listener_networks='((NAME=network1)(LOCAL_LISTENER=Q9DB01_LOCAL_NET1)
     (REMOTE_LISTENER=q9dbscan:1521))','((NAME=network2)(LOCAL_LISTENER=Q9DB01_LOCAL_NET2)
     (REMOTE_LISTENER=Q9DB_REMOTE_NET2))'SCOPE=BOTH SID='q9db1';

System altered.

----节点2
SQL> ALTER SYSTEM SET listener_networks='((NAME=network1)(LOCAL_LISTENER=Q9DB02_LOCAL_NET1)
     (REMOTE_LISTENER=q9db-scan:1521))','((NAME=network2)(LOCAL_LISTENER=Q9DB02_LOCAL_NET2)
     (REMOTE_LISTENER=Q9DB_REMOTE_NET2))'SCOPE=BOTH SID='q9db2';

System altered.

查看监听状态

--节点1
[grid@q9db01 ~]$ lsnrctl status listener_dg

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-JUN-2013 17:12:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                19-JUN-2013 16:47:03
Uptime                    0 days 0 hr. 25 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/q9db01/listener_dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.64)(PORT=1522)))
Services Summary...
Service "q9db" has 2 instance(s).
  Instance "q9db1", status READY, has 2 handler(s) for this service...
  Instance "q9db2", status READY, has 1 handler(s) for this service...
The command completed successfully

--节点2
[grid@q9db02 ~]$ lsnrctl status listener_dg

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-JUN-2013 17:12:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                19-JUN-2013 16:52:24
Uptime                    0 days 0 hr. 19 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/q9db02/listener_dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.65)(PORT=1522)))
Services Summary...
Service "q9db" has 2 instance(s).
  Instance "q9db1", status READY, has 1 handler(s) for this service...
  Instance "q9db2", status READY, has 2 handler(s) for this service...
The command completed successfully

测试新监听

--RDBMS目录中tns配置
q9db1dg=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = q9db1)
    )
 )

q9db2dg=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = q9db2)
    )
 )

--验证结果
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> conn test/test@q9db1dg
Connected.
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      q9db1
SQL> conn test/test@q9db2dg
Connected.
SQL>  show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      q9db2

到这里,可以正常的访问两个库,监听已经配置完成,数据库之间也可以使用特定网络