精华内容
参与话题
问答
  • ADG4612和ADG4613均内置四个独立的SPST(单刀单掷)开关。ADG4612每个开关的接通条件都是相关控制输入为逻辑1;ADG4613中两个开关的接通条件是逻辑1,另两个则是逻辑0。先开后合式开关动作适合多路复用器应用。  ...
  • adg

    千次阅读 2013-08-07 15:34:52
    在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,是不可以打开的,只可以mount。从11g开始,在应用redo的时候, 物理备库可以处于read-only模式,这就称为Active Data Guard 。...

    一、什么是adg

      adg 是11g的一个新特性 就是active dataguard ,在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,是不可以打开的,只可以mount。从11g开始,在应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard 。通过Active Data Guard,可以在物理备库进行查询或者导出数据,
    从而减少对主库的访问和压力。


     Active Data Guard适用于一些只读性的应用,比如,有的应用程序只是查询数据,进行一些报表业务,不会产生redo数据,
    这些应用可以转移到备库上,避免对主库资源的争用。
    如需启用Active Data Guard, 只需要将备库以 read-only 模式打开,而且执行 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE语句就可以。


    需要注意的是:主库和备库的COMPATIBLE 参数至少要设置为11.0.0。


    二、配置成adg需要准备

    主库:
    SQL> select status,instance_name,database_role from v$instance,v$database;

    STATUS     INSTANCE_NAME    DATABASE_ROLE
    ------------ ---------------- ----------------
    OPEN     rac      PRIMARY


    SQL>select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
      396

    备库:
    SQL> select status,instance_name,database_role from v$instance,v$database;

    STATUS      INSTANCE_NAME    DATABASE_ROLE
    ------------ ---------------- ----------------
    MOUNTED      orcl       PHYSICAL STANDBY


    SQL>select max(sequence#) from v$archived_log;
    MAX(SEQUENCE#)
    --------------
      396

    主库和备库的最大sequence#要一致

    备库:
    SQL> select process,status,sequence# from v$managed_standby;
    PROCESS   STATUS        SEQUENCE#
    --------- ------------ ----------
    ARCH      CONNECTED     0
    ARCH      CONNECTED     0
    ARCH      CONNECTED     0
    ARCH      CONNECTED     0
    RFS       IDLE          41
    RFS       IDLE          0
    RFS       IDLE          0
    RFS       IDLE          0
    MRP0      WAIT_FOR_LOG  41

    mrp的状态是wait 状态


    三、添加standby redo log

    alter database add standby logfile  '/u01/app/oracle/oradata/standredo01.log' size 50M;
    alter database add standby logfile  '/u01/app/oracle/oradata/standredo02.log' size 50M;
    alter database add standby logfile  '/u01/app/oracle/oradata/standredo03.log' size 50M;
    alter database add standby logfile  '/u01/app/oracle/oradata/standredo04.log' size 50M;

    四、备库执行如下命令
    如下:在备库执行:   
    SQL>startup mount;

    SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    SQL>alter database recover managed standby database cancel;

    SQL> alter database open;
    SQL> select open_mode from v$database;

    OPEN_MODE
    --------------------
    READ ONLY

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

    SQL>  select open_mode from v$database;

    OPEN_MODE
    --------------------
    READ ONLY WITH APPLY
      如果已经启用了Active Data Guard,备库的V$DATABASE会显示为"READ ONLY WITH APPLY':


    SQL> SELECT open_mode FROM V$DATABASE;
       OPEN_MODE
          --------------------
          READ ONLY WITH APPLY


    select   DB_UNIQUE_NAME,PROTECTION_MODE,database_role,open_mode from v$database
    DB_UNIQUE_NAME         PROTECTION_MODE     DATABASE_ROLE    OPEN_MODE
    ------------------------------ -------------------- ---------------- --------------------
    orcl          MAXIMUM PERFORMANCE  PHYSICAL STANDBY READ ONLY WITH APPLY

     注意:使用Active
    Data Guard要求主库和备库的COMPATIBLE 参数至少设置为11.0.0。


    ADG实际上就是Physical Standby的Real Time Apply方式应用的日志,在我们配置了standby redo log后,会直接通过standby redo log做recover,
    你提到了读一致性的问题,毕竟undo datafile也会被恢复过来,所以未commit的事务,还是会读取undo segment的,这点应该不会改变



    查看standby redo log


    主库查看:
    SQL> select * from v$standby_log;

        GROUP# DBID       THREAD#SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
    ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    7 UNASSIGNED     1 0   52428800   512        512 YES UNASSIGNED     0    0   0
    8 UNASSIGNED     1 0   52428800   512        512 YES UNASSIGNED     0    0   0
    9 UNASSIGNED     1 0   52428800   512        512 YES UNASSIGNED     0    0   0
    10 UNASSIGNED     2 0   52428800   512        512 YES UNASSIGNED     0    0   0
    11 UNASSIGNED     2 0   52428800   512        512 YES UNASSIGNED     0    0   0
    12 UNASSIGNED     2 0   52428800   512        512 YES UNASSIGNED     0    0   0


    6 rows selected.


    备库查看:
     SQL> select * from v$standby_log;

        GROUP# DBID       THREAD#SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
    ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
    7 2407816564     1      157   52428800   512   11044352 YES ACTIVE      12370225 02-AUG-13     12394343 02-AUG-13     12394343 02-AUG-13
    8 UNASSIGNED     1 0   52428800   512        512 NO  UNASSIGNED     0    0   0
    9 UNASSIGNED     1 0   52428800   512        512 NO  UNASSIGNED     0    0   0
    10 2407816564     2      194   52428800   512    9279488 YES ACTIVE      12370222 02-AUG-13     12394344 02-AUG-13     12394344 02-AUG-13
    11 UNASSIGNED     2 0   52428800   512        512 NO  UNASSIGNED     0    0   0
    12 UNASSIGNED     2 0   52428800   512        512 NO  UNASSIGNED     0    0   0


    6 rows selected.

    SQL> select process,status,sequence# from v$managed_standby;

    PROCESS   STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CLOSING       221
    ARCH   CLOSING       222
    ARCH   CLOSING       229
    ARCH   CLOSING       228
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    ARCH   CONNECTED  0
    RFS   IDLE   0
    RFS   IDLE        223
    RFS   IDLE   0
    RFS   IDLE   0
    RFS   IDLE   0
    RFS   IDLE        230
    RFS   IDLE   0
    RFS   IDLE   0
    MRP0   APPLYING_LOG       223
    39 rows selected.






      下面的功能是允许在read-only的数据库上执行的:
        • Issue SELECT statements, including queries that require multiple sorts that leverage TEMP 
    segments
        • Use ALTER SESSION and ALTER SYSTEM statements
        • Use SET ROLE
        • Call stored procedures
        • Use database links (dblinks) to write to remote databases
        • Use stored procedures to call remote procedures via dblinks
        • Use SET TRANSACTION READ ONLY for transaction level read consistency
        • Issue complex queries (such as grouping SET queries and WITH CLAUSE queries)


      下面的功能是不允许在read-only的数据库上执行的:
        • Any DMLs (excluding simple SELECT statements) or DDLs
        • Query accessing local sequences
        • DMLs to local temporary tables


        比较典型的Active Data Guard 分为:
      • 单实例的物理主库和单实例的物理备库
      • 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例
      • RAC主库和RAC备库


        Oracle Data Guard 的配置方法,,请参考下面的文档:

       * 单实例的物理主库和单实例的物理备库:
         http://docs.oracle.com/cd/B28359_01/server.111/b28294/create_ps.htm


      * 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例:
         http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimarysingleinstance-131970.pdf


     * RAC 主库和RAC 备库:
       10g: http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimaryracphysicalsta-131940.pdf
       11g: http://www.oracle.com/technetwork/database/features/availability/dataguard11g-rac-maa-1-134639.pdf


    * 关于Active Data Guard的最佳实践经验,请参考文档:
        http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf

    * 关于Oracle Maximum Availability Architecture Best Practices的更多文档,请参考:
       http://www.oracle.com/goto/maa


    展开全文
  • <p>This PR updates the database host names used by the ConfDB offline converter to access the ADG copy of the online database. <p>There are no changes to any CMSSW-specific code. <h4>PR validation: ...
  • <p>This PR updates the database host names used by the ConfDB offline converter to access the ADG copy of the online database. <p>There are no changes to any CMSSW-specific code. <h4>PR validation: ...
  • <p>This PR updates the database host names used by the ConfDB offline converter to access the ADG copy of the online database. <p>There are no changes to any CMSSW-specific code. <h4>PR validation: ...
  • <p>This PR updates the database host names used by the ConfDB offline converter to access the ADG copy of the online database. <p>There are no changes to any CMSSW-specific code. <h4>PR validation: ...
  • <p>This PR updates the database host names used by the ConfDB offline converter to access the ADG copy of the online database. <p>There are no changes to any CMSSW-specific code. <h4>PR validation: ...
  • ADG监控

    2019-10-01 03:54:10
    cx_Oracle环境配置 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib/ export TNS_ADMIN=$ORACLE_HOME/network/admin/ ...

    cx_Oracle环境配置

    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
    export TNS_ADMIN=$ORACLE_HOME/network/admin/

    脚本正本

    #!/usr/bin/python
    # coding:utf-8
    
    __author__ = 'Jipu FANG'
    
    import cx_Oracle
    import sys
    
    # 主库状态
    def pdb():
        conn = cx_Oracle.connect('user/passwd@rac01/orcl')  
        cursor = conn.cursor ()
        cursor.execute ("select switchover_status from v$database")
        row = cursor.fetchone ()
        status = row[0]
        if status == 'TO STANDBY' or status == 'SESSIONS ACTIVE':
            cursor.close ()
        conn.close ()
            return "0"
        else:
        return "200"
    
    # 备库状态
    def sdb():
        conn = cx_Oracle.connect('user/passwd@rdata01/phydb')  
        cursor = conn.cursor ()
        cursor.execute ("select switchover_status from v$database")
        row = cursor.fetchone ()
        status = row[0]
        if status == 'NOT ALLOWED':
            cursor.close ()
            conn.close ()
            return "0"
        else:
        return "200"
    
    # DG延迟,基于SCN
    class dg_delay():
        def rac1_scn(self):
            conn = cx_Oracle.connect('user/passwd@rac01/orcl')
            cursor = conn.cursor ()
            cursor.execute ("select current_scn from v$database")
            row = cursor.fetchone ()
        if row:
                scn = row[0]
        else:
            scn = 0
            cursor.close()
            conn.close ()
        return scn 
     
        def sdb_scn(self):
            conn = cx_Oracle.connect('user/passwd@rdata01/phydb')
            cursor = conn.cursor ()
            cursor.execute ("select current_scn from v$database")
            row = cursor.fetchone ()
        if row:
                scn = row[0]
        else:
            scn = 0
            cursor.close()
            conn.close ()
            return scn
    
        def run(self):
        scn = int(self.rac1_scn()) - int(self.sdb_scn())
        return scn
            
    item = sys.argv[1]
    
    if __name__ == '__main__':
        if item == 'pdb':
            print pdb()
        elif item == 'sdb':
            print sdb()
        elif item == 'dg':
            a = dg_delay()
            print a.run()
    

    脚本执行

    # ./check_dg_delay_status.py dg
    5

    转载于:https://www.cnblogs.com/GXLo/p/7562337.html

    展开全文
  • ADG指导手册.docx

    2019-08-20 14:27:35
    笔者在网上见过很多adg的搭建手册,但是终会遇到坑,经过实战经验测试总结,编写一个adg的指导手册,也是最近我在做机房搬迁中总结的经验
  • RAC+ADG(单节点ADG)

    2020-03-31 15:12:20
    RAC+ADG有两种存储数据文件的形式,一个是将DG的数据文件放在ASM上,另一个是将数据文件放在本地磁盘上 本实验做的是数据放在本地磁盘上。 在搭建RAC+DG之前,请确保RAC是没有问题的 1.检查集群状态 grid用户下执行...

    RAC+ADG有两种存储数据文件的形式,一个是将DG的数据文件放在ASM上,另一个是将数据文件放在本地磁盘上
    本实验做的是数据放在本地磁盘上。
    在搭建RAC+DG之前,请确保RAC是没有问题的
    1.检查集群状态 grid用户下执行:

    crsctl status res -t
    

    2.前期规划

    RAC Primary RACDB1(主机) RACDB2(主机) 备注
    Public IP 192.168.1.130 192.168.1.140 24位掩码
    Virtual IP 172.16.1.131 172.16.1.141 24位掩码
    Instance racdb1 racdb2
    DB name RACDB
    DATA file +DATA/racdb/datafile/
    Control file +DATA/racdb/controlfile/ +ARC/racdb/controlfile/
    Redo Log file +DATA/racdb/onlinelog/ +ARC/racdb/onlinelog/
    db_unique_name racdb
    service_names racdb
    Oracle_Version 11.2.0.4.0

    DATA file,Control file,Redo Log file根据自己的RAC来。
    Standby 端

    Single instance standby 主机名 备注
    IP 192.168.1.160 24位掩码
    Oracle_version 11.2.0.4.0
    Instance Racdb
    DB name RACDB
    DB_unique_name dg1
    service_names dg1
    DATA file /u01/app/oracle/oradata/dg1/
    Control file /u01/app/oracle/oradata/dg1/
    Redo Log file /u01/app/oracle/oradata/dg1/ /u01/app/oracle/oradata/arclog/ 此arclog不是存放归档日志,只是按照RAC中两个不同的日志组路径,对应设置的

    3.备库操作系统初始设置
    ①关闭防火墙

    /etc/init.d/iptables stop
    chkconfig iptables off
    

    ②关闭selinux

    vim /etc/selinux/config 
    SELINUX=disabled
    

    ③关闭networkmaanger

    service NetworkManager stop
    chkconfig NetworkManager off
    

    ④关闭NTP服务

    service ntpd stop
    chkconfig ntpd off
    

    ⑤配置yum源

    mkdir /source
    mount /dev/cdrom /source/
    
    vim /etc/yum.repos.d/x.repo
    ----------------------------
    [ok]
    name=ok
    baseurl=file:///source/
    gpgcheck=0
    enabled=1
    ---------------------------- 
    yum clean all
    yum list
    

    ⑥安装相关的软件包

    yum install -y  readline* binutils compat-libstdc++ compat-libstdc++ elfutils-libelf elfutils-libelf-devel  expat gcc gcc-c++ glibc glibc glibc-common glibc-devel glibc-headers libaio libaio libaio-devel libaio-devel  libgcc libgcc libstdc++ libstdc++ libstdc++-devel  make pdksh sysstat unixODBC unixODBC unixODBC-devel unixODBC-devel  binutils  libaio-devel libaio elfutils-libelf-devel compat-libstdc++-33 libgcc  gcc gcc-c++ glibc sysstat libstdc++ libstdc++-devel  unixODBC-devel unixODBC
    

    如果少包再试试下面的(通常是不用):

    yum install binutils compat-libcap1 compat-libstdc++-33 gcc  gcc-c++ glibc glibc-devel ksh  libgcc libstdc++ libstdc++-devel libaio libaio-devel make sysstat unixODBC-devel unixODBC
    

    ⑦建立oracle用户
    所有节点root执行:

    groupadd -g 1000 oinstall
    groupadd -g 1201 asmdba
    groupadd -g 1300 dba
    groupadd -g 1301 oper
    useradd -u 1101 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle
    echo "oracle" | passwd --stdin oracle
    

    配置oracle用户的环境变量(切换到oracle用户)

    [oracle@up1 ~]$ vim .bash_profile
    
    # .bash_profile
     
    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
           . ~/.bashrc
    fi
     
    # User specific environment and startup programs
     
    PATH=$PATH:$HOME/bin
     
    export PATH
    export TMP=/tmp
    export TMPDIR=$TMP
    export ORACLE_SID=racdb1
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
    export ORACLE_UNQNAME=racdb
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ORACLE_TERM=xterm
    export PATH=/usr/sbin:$PATH
    export PATH=$ORACLE_HOME/bin:/u01/app/11.2.0/grid/bin:$PATH
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
    export NLS_DATA_FORMAT='yyyy-mm-dd hh24:mi:ss'
    alias sqlplus='/usr/local/bin/rlwrap sqlplus'
    alias rman='/usr/local/bin/rlwrap rman'
    umask 022
    

    ⑧配置限制参数和内核参数

    限制参数
    echo "oracle soft nproc 2047" >>/etc/security/limits.conf
    echo "oracle hard nproc 16384" >>/etc/security/limits.conf 
    echo "oracle soft nofile 1024" >>/etc/security/limits.conf
    echo "oracle hard nofile 65536" >>/etc/security/limits.conf
    echo "grid soft nproc 2047" >>/etc/security/limits.conf 
    echo "grid hard nproc 16384" >>/etc/security/limits.conf
    echo "grid soft nofile 1024" >>/etc/security/limits.conf
    echo "grid hard nofile 65536" >>/etc/security/limits.conf
    echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login
    echo "session required pam_limits.so" >>/etc/pam.d/login
    

    内核参数

    echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
    echo "fs.file-max = 6815744" >> /etc/sysctl.conf
    echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
    echo "kernel.shmmax = 1054472192" >> /etc/sysctl.conf
    echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
    echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
    echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
    echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
    echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
    echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
    echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf
    echo "net.ipv4.tcp_wmem = 262144 262144 262144" >> /etc/sysctl.conf
    echo "net.ipv4.tcp_rmem = 4194304 4194304 4194304" >> /etc/sysctl.conf
    sysctl -p  
    

    ⑨上传安装介质,安装和指定lib包
    在备库上上传oracle安装包

    [root@up1 src]# ls
    p10404530_112030_Linux-x86-64_1of7.zip
    p10404530_112030_Linux-x86-64_2of7.zip
    p10404530_112030_Linux-x86-64_3of7.zip
    rlwrap-0.32.tar.gz
    
    tar zxvf rlwrap-0.32.tar.gz
    unzip  p10404530_112040_Linux-x86-64_1of7.zip  -d  /usr/local/src
    unzip  p10404530_112040_Linux-x86-64_2of7.zip  -d  /usr/local/src
    unzip  p10404530_112040_Linux-x86-64_3of7.zip  -d  /usr/local/src
    
    [root@up1 src]# ls
    database
    rlwrap-0.32
    rlwrap-0.32.tar.gz 
    
    chown -R oracle:oinstall database
    

    安装插件包 rlwrap-0.32.tar.gz

    cd rlwrap-0.32
    ./configure
    make ; make install
    

    4.建立相关路径
    备库:
    数据文件,日志文件,控制文件全部放在这里(为了方便,生产环境不要这样,io压力大)

    mkdir -p /u01/app/oracle/oradata/dg1/ 
    

    登陆时的审计文件放到这里

    mkdir -p /u01/app/oracle/admin/racdb/adump  
    

    记录核心dump的目录,如果oracle核心进程应为BUG等原因崩溃,会做内存的dump

    mkdir -p /u01/app/oracle/admin/racdb/cdump
    

    存放归档的路径

    mkdir -p /u01/archivelog
    

    rman备份存放在此

    mkdir -p /home/oracle/backup 
    

    由于RAC日志组有两个路径,所以对应建立另一个路径

    mkdir -p /u01/app/oracle/oradata/arclog/
    

    $ORACLE_BASE,根据 .bash_profile来的(根据自己的来设置)

    mkdir -p /u01/app/oracle 
    

    $ORACLE_HOME,也根据 .bash_profile 来的(根据自己的来设置)

    mkdir -p /u01/app/oracle/product/11.2.0/db_1
    chown -R oracle:oinstall /u01/
    chown -R oracle:oinstall /u01/app/oracle
    chmod -R 775 /u01
    

    RAC1,RAC2:

    su - oracle
    mkdir -p /u01/app/oracle/admin/racdb/adump
    mkdir -p /home/oracle/backup
    root用户
    mkdir -p /u01/archivelog
    chown -R oracle:oinstall /u01/archivelog
    

    5.备库安装ORACLE软件
    安装方式两种:
    1.DBCA
    2.静默安装
    注意:
    1.版本号要与主库一样
    2.环境变量,内核参数要一致,相关路径要建立全。
    6.RAC必须开启到归档模式(自行检查)

    SQL> archive log list;
    Database log mode	       Archive Mode
    Automatic archival	             Enabled
    Archive destination	       /u01/archivelog
    Oldest online log sequence      27
    Next log sequence to archive    28
    Current log sequence	         28
    

    7.配置监听(此步骤极为重要,配置错误会导致后续步骤报错)
    监听路径:$ORACLE_HOME/network/admin/
    RAC1和RAC2的listener.ora如下
    oracle_home和dbname,sid_name根据自己的来

    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (PROGRAM = extproc)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = racdb)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME = racdb)
        )
      )
    

    DG1的listener.ora如下

    LISTENER=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.13.30)(PORT=1521))
          (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=prod.neves.com)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
          (SID_NAME=prod))
        (SID_DESC=
          (SID_NAME=plsextproc)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
          (PROGRAM=extproc)))
    

    RAC1,RAC2和DG1的tnsnames.ora 如下

    racdb1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = racdb)
        )
      )
    racdb2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.140)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = racdb)
        )
      )
    dg1 =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = dg1)
        )
      )
    

    测试监听是否正常

    tnsping racdb1;tnsping racdb2;tnsping dg1 
    --- 全部看到OK就代表正常
    

    不正常的原因可能:
    防火墙没关;监听配置文件格式错误;监听配置文件内容错误;监听服务没有开启(lsnrctl status查看)
    8.将密码文件传到备库

    scp orapwracdb1  192.168.1.160:$ORACLE_HOME/dbs/    
    

    —注意密码文件和参数文件传过去后要记得改名字,改成和SID一样的 格式:orapw$ORACLE_SID
    在RAC1/2、DG1上测试监听
    rac1/2

    sqlplus sys/oracle@racdb1 as sysdba
    select instance_name from v$instance
    sqlplus sys/oracle@racdb2 as sysdba
    select instance_name from v$instance
    sqlplus sys/oracle@dg1 as sysdba
    

    DG1测试

    sqlplus sys/oracle@racdb1 as sysdba
    select instance_name from v$instance
    sqlplus sys/oracle@racdb2 as sysdba
    select instance_name from v$instance
    sqlplus sys/oracle@dg1 as sysdba
    

    9…RAC主节点开启开启Force logging

    SQL> ALTER DATABASE FORCE LOGGING;
    

    10.主库RAC1创建静态参数文件

    SQL>  show parameter pfile
    
    NAME			     TYPE	      VALUE
    --------------    ----------- ----------------------------
    spfile				string	   +DATA/racdb/spfileracdb.ora
    
    SQL> create  pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb1.ora' from spfile='+DATA/racdb/spfileracdb.ora';
    

    主库进到ORACLE_HOME/dbs/下编辑静态参数文件

    [oracle@up1 dbs]$ pwd
    /u01/app/oracle/product/11.2.0/db_1/dbs 
    

    原参数不变,添加以下参数

    *.db_unique_name=racdb
    *.service_names=racdb
    *.log_archive_config='DG_CONFIG=(racdb,dg1)'
    *.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' 
    *.log_archive_dest_2 = 'SERVICE=dg1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
    *.log_archive_dest_state_1=enable
    *.log_archive_dest_state_2=enable
    *.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'        ---由于数据文件放到备库本地,所以需要做映射
    *.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/'
    *.standby_file_management=AUTO
    *.FAL_SERVER=dg1
    ####以下三个参数主要针对日后的主备模式切换
    racdb1.fal_client=racdb1 
    ####注意:fal_srver,fal_client其实是net server name,根据tnsnames.ora中的字符串来的。
    racdb2.fal_client=racdb2  ####此参数写错,在备库启到open时候会报错(ora_10458)
    SPFILE='+DATA/racdb/spfileracdb.ora'		# line added by Agent
    

    完整参数如下

    racdb2.__db_cache_size=385875968
    racdb1.__db_cache_size=385875968
    racdb2.__java_pool_size=4194304
    racdb1.__java_pool_size=4194304
    racdb2.__large_pool_size=8388608
    racdb1.__large_pool_size=8388608
    racdb2.__pga_aggregate_target=209715200
    racdb1.__pga_aggregate_target=209715200
    racdb2.__sga_target=629145600
    racdb1.__sga_target=629145600
    racdb2.__shared_io_pool_size=0
    racdb1.__shared_io_pool_size=0
    racdb2.__shared_pool_size=222298112
    racdb1.__shared_pool_size=222298112
    racdb2.__streams_pool_size=0
    racdb1.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
    *.audit_trail='db'
    *.cluster_database=true
    *.compatible='11.2.0.4.0'
    *.control_files='+DATA/racdb/controlfile/current.260.951501039','+ARC/racdb/controlfile/current.256.951501039'
    *.db_block_size=8192
    *.db_create_file_dest='+DATA'
    *.db_domain=''
    *.db_name='racdb'
    *.db_recovery_file_dest='+ARC'
    *.db_recovery_file_dest_size=4621074432
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
    racdb1.instance_number=1
    racdb2.instance_number=2
    *.log_archive_format='%t_%s_%r.dbf'
    *.open_cursors=300
    *.pga_aggregate_target=209715200
    *.processes=150
    *.remote_listener='scanip:1521'
    *.remote_login_passwordfile='exclusive'
    *.sga_target=629145600
    racdb2.thread=2
    racdb1.thread=1
    racdb1.undo_tablespace='UNDOTBS1'
    racdb2.undo_tablespace='UNDOTBS2'
    *.db_unique_name=racdb
    *.service_names=racdb
    *.log_archive_config='DG_CONFIG=(racdb,dg1)'
    *.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' 
    *.log_archive_dest_2 = 'SERVICE=dg1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'
    *.log_archive_dest_state_1=enable
    *.log_archive_dest_state_2=enable
    *.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
    *.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/'
    *.standby_file_management=AUTO
    *.FAL_SERVER=dg1
    racdb1.fal_client=racdb1
    racdb2.fal_client=racdb2
    SPFILE='+DATA/racdb/spfileracdb.ora'		# line added by Agent
    

    将静态参数文件传给备库

    scp initracdb1.ora  192.168.1.160:$ORACLE_HOME/dbs/
    ---记得传过去后将参数文件改名
    

    11.关闭RAC1和RAC2数据库

    [grid@racdb1 ~]$ srvctl stop database -d racdb
    
    [oracle@racdb1 ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production onTue Mar 17 18:18:09 2015
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Connected to an idle instance.
    SQL> create  spfile='+DATA/racdb/spfileracdb.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb1.ora'
    File created.
    

    12.启动RAC1和RAC2数据库:

    [grid@racdb1 ~]$ srvctl start database -d racdb
    

    启动后登陆两个节点查看参数:

    SQL> show parameter fal
    NAME				     TYPE	 VALUE
    --------------       ----------- ----------------
    fal_client			   string	 racdb1
    fal_server			   string	 dg1
    
    SQL> show parameter archive
    NAME				       TYPE	     VALUE
    ------------------------------------ ----------- ------------------------------
    archive_lag_target		        integer	 0
    log_archive_config		         string	 DG_CONFIG=(racdb,dg1)
    log_archive_dest		         string
    log_archive_dest_1		     string	 LOCATION=/u01/archivelog VALID
    						             _FOR=(ALL_LOGFILES,ALL_ROLES)
    						             DB_UNIQUE_NAME=racdb
    log_archive_dest_10		     string
    log_archive_dest_11		     string
    log_archive_dest_12		     string
    log_archive_dest_13		     string
    log_archive_dest_14		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_15		     string
    log_archive_dest_16		     string
    log_archive_dest_17		     string
    log_archive_dest_18		     string
    log_archive_dest_19		     string
    log_archive_dest_2		     string	 SERVICE=dg1 LGWR SYNC AFFIRM V
    						             ALID_FOR=(ONLINE_LOGFILES,PRIM
    						             ARY_ROLE) DB_UNIQUE_NAME=dg1
    log_archive_dest_20		     string
    log_archive_dest_21		     string
    log_archive_dest_22		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_23		     string
    log_archive_dest_24		     string
    log_archive_dest_25		     string
    log_archive_dest_26		     string
    log_archive_dest_27		     string
    log_archive_dest_28		     string
    log_archive_dest_29		     string
    log_archive_dest_3		     string
    log_archive_dest_30		     string
    log_archive_dest_31		     string
    log_archive_dest_4		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_5		     string
    log_archive_dest_6		     string
    log_archive_dest_7		     string
    log_archive_dest_8		     string
    log_archive_dest_9		     string
    log_archive_dest_state_1	     string	 enable
    log_archive_dest_state_10	     string	 enable
    log_archive_dest_state_11	     string	 enable
    log_archive_dest_state_12	     string	 enable
    log_archive_dest_state_13	     string	 enable
    log_archive_dest_state_14	     string	 enable
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_15	     string	 enable
    log_archive_dest_state_16	     string	 enable
    log_archive_dest_state_17	     string	 enable
    log_archive_dest_state_18	     string	 enable
    log_archive_dest_state_19	     string	 enable
    log_archive_dest_state_2	     string	 enable
    log_archive_dest_state_20	     string	 enable
    log_archive_dest_state_21	     string	 enable
    log_archive_dest_state_22	     string	 enable
    log_archive_dest_state_23	     string	 enable
    log_archive_dest_state_24	     string	 enable
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_25	     string	 enable
    log_archive_dest_state_26	     string	 enable
    log_archive_dest_state_27	     string	 enable
    log_archive_dest_state_28	     string	 enable
    log_archive_dest_state_29	     string	 enable
    log_archive_dest_state_3	     string	 enable
    log_archive_dest_state_30	     string	 enable
    log_archive_dest_state_31	     string	 enable
    log_archive_dest_state_4	     string	 enable
    log_archive_dest_state_5	     string	 enable
    log_archive_dest_state_6	     string	 enable
    
    NAME				            TYPE	     VALUE
    -------------------------   ----------- ----------------
    log_archive_dest_state_7	     string	 enable
    log_archive_dest_state_8	     string	 enable
    log_archive_dest_state_9	     string	 enable
    log_archive_duplex_dest 	     string
    log_archive_format		     string	 %t_%s_%r.dbf
    log_archive_local_first 	        boolean	 TRUE
    log_archive_max_processes	     integer	 4
    log_archive_min_succeed_dest	 integer	 1
    log_archive_start		        boolean	 FALSE
    log_archive_trace		         integer	 0
    standby_archive_dest		     string	 ?/dbs/arch
    
    SQL> show parameter log_file_name_convert
    NAME				      TYPE	   VALUE
    ----------------------- ----------- ----------------
    log_file_name_convert	 string	 +DATA/racdb/onlinelog/, /u01/app/oracle/oradata/dg1/
    

    13.在RAC1上用rman进行备份

    [oracle@up1 backup]$ rman target /
    Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 18:58:43 2015
    Copyright (c) 1982,2009, Oracle and/or its affiliates.  Allrights reserved.
    connected to targetdatabase: RACDG (DBID=1109864007)
    RMAN> backup database format'/home/oracle/backup/cc_%u.bak ';
     
    Starting backup at17-MAR-15
    using targetdatabase control file instead of recovery catalog
    allocated channel:ORA_DISK_1
    channel ORA_DISK_1:SID=32 instance=racdg1 device type=DISK
    channel ORA_DISK_1:starting full datafile backup set
    channel ORA_DISK_1:specifying datafile(s) in backup set
    input datafile filenumber=00001 name=+DATA1/racdg/datafile/system.292.874603637
    input datafile filenumber=00002 name=+DATA1/racdg/datafile/sysaux.293.874603643
    input datafile filenumber=00003 name=+DATA1/racdg/datafile/undotbs1.294.874603645
    input datafile filenumber=00005 name=+DATA1/racdg/datafile/undotbs2.300.874604699
    input datafile filenumber=00004 name=+DATA1/racdg/datafile/users.295.874603647
    channel ORA_DISK_1:starting piece 1 at 17-MAR-15
    channel ORA_DISK_1:finished piece 1 at 17-MAR-15
    piecehandle=/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1tag=TAG20150317T185854 comment=NONE
    channel ORA_DISK_1:backup set complete, elapsed time: 00:02:45
    channel ORA_DISK_1:starting full datafile backup set
    channel ORA_DISK_1:specifying datafile(s) in backup set
    including currentcontrol file in backup set
    including currentSPFILE in backup set
    channel ORA_DISK_1:starting piece 1 at 17-MAR-15
    channel ORA_DISK_1:finished piece 1 at 17-MAR-15
    piece handle=/home/oracle/db_backup/racdbfull_20150317_874609302_02q22ukm_2_1tag=TAG20150317T185854 comment=NONE
    channel ORA_DISK_1:backup set complete, elapsed time: 00:00:16
    Finished backup at17-MAR-15
    RMAN>
    

    14.为standby db创建controlfile

    sqlplus / as sysdba
    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/backup/racdb.ctl';
    Database altered.
    SQL> alter system switch logfile;
    System altered.
    

    15.将备份考到备库的相应路径下

    scp  /home/oracle/backup/* 192.168.1.160:/home/oracle/backup
    

    16.修改备库的静态参数文件
    在备库上去掉primary parameter中的内容如下:

    *.cluster_database=TRUE
    racdg1.instance_number=1
    racdg2.instance_number=2
    *.remote_listener='scan.localdomain:1521'
    *.db_create_file_dest='+DATA1'
    *.cluster_database=true
    *.memory_target=842006528
    *.db_recovery_file_dest='+DATA3'
    *.db_recovery_file_dest_size=4070572032
    

    添加修改参数:

    *.pga_aggregate_target=339738624
    *.sga_target=503316480
    *.audit_file_dest='' /u01/app/oracle/admin/racdb/adump
    *.core_dump_dest='' /u01/app/oracle/admin/racdb/cdump
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files= ' /u01/app/oracle/oradata/dg1/control01.ctl ',' /u01/app/oracle/oradata/dg1/control02.ctl '
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='racdg'
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP)(SERVICE=racdgXDB)'
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='exclusive'
    *.thread=1
    *.undo_tablespace='UNDOTBS1'
    *.db_unique_name='racdb'
    *.service_names='racdb'
    *.log_archive_config='dg_config=(racdb,dg1)'
    *.log_archive_dest_1= 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
    *.log_archive_dest_2='SERVICE=racdb1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
    *.log_archive_dest_state_1='enable'
    *.log_archive_dest_state_2='enable'
    *.log_archive_format='%t_%s_%r.dbf'
    *.log_file_name_convert= '+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/','+ARC/racdb/onlinelog/','/u01/app/oracle/oradata/arclog/'
    *.db_file_name_convert= '+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
    *.log_archive_max_processes=30
    *.standby_file_management='auto'
    *.FAL_SERVER='racdb1','racdb2' ###写的是tnsname里字符串的名字 就是net server name
    *.fal_client='dg1'   #####同上
    

    完整参数如下
    注意:不要直接无脑复制,因为每个环境都不一样,以下仅作为参考

    *.__db_cache_size=385875968
    *.__java_pool_size=4194304
    *.__large_pool_size=8388608
    *.__pga_aggregate_target=209715200
    *.__sga_target=629145600
    *.__shared_io_pool_size=0
    *.__shared_pool_size=222298112
    *.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
    *.core_dump_dest='/u01/app/oracle/admin/racdb/cdump'
    *.audit_trail='db'
    *.compatible='11.2.0.4.0'
    *.control_files='/u01/app/oracle/oradata/dg1/control01.ctl','/u01/app/oracle/oradata/dg1/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='racdb'
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
    *.log_archive_format='%t_%s_%r.dbf'
    *.open_cursors=300
    *.pga_aggregate_target=209715200
    *.processes=150
    *.remote_login_passwordfile='exclusive'
    *.sga_target=629145600
    *.thread=1
    *.undo_tablespace='UNDOTBS1'
    *.db_unique_name='dg1'
    *.service_names='dg1'
    *.log_archive_config='DG_CONFIG=(racdb,dg1)'
    *.log_archive_dest_1 = 'LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
    *.log_archive_dest_2 = 'SERVICE=racdb1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
    *.log_archive_dest_state_1=enable
    *.log_archive_dest_state_2=enable
    *.db_file_name_convert='+DATA/racdb/datafile/','/u01/app/oracle/oradata/dg1/'
    *.log_file_name_convert='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/dg1/','+ARC/racdb/onlinelog/','/u01/app/oracle/oradata/arclog/'
    *.standby_file_management=AUTO
    *.FAL_SERVER='racdb1','racdb2'               
    *.fal_client='dg1'             
    

    17.备库开启到nomount

    SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdg1.ora' nomount;
    ORACLE instance started.
    Total System Global Area  217157632 bytes
    Fixed Size                  2211928 bytes
    Variable Size             159387560 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                5226496 bytes
    

    创建spfile

    SQL>
    create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledg1.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdg1.ora';
    

    18.查看相关参数是否生效

    SQL> show parameter fal
    NAME				     TYPE	 VALUE
    -------------       ----------- ------------
    fal_client			    string	 dg1
    fal_server			    string	 racdb1, racdb2
    
    SQL> show parameter archive
    NAME				        TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    archive_lag_target		         integer	 0
    log_archive_config		         string	 DG_CONFIG=(racdb,dg1)
    log_archive_dest		         string
    log_archive_dest_1		     string	 LOCATION=/u01/archivelog VALID
    						             _FOR=(ALL_LOGFILES,ALL_ROLES)
    						             DB_UNIQUE_NAME=dg1
    log_archive_dest_10		     string
    log_archive_dest_11		     string
    log_archive_dest_12		     string
    log_archive_dest_13		     string
    log_archive_dest_14		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    log_archive_dest_15		     string
    log_archive_dest_16		     string
    log_archive_dest_17		     string
    log_archive_dest_18		     string
    log_archive_dest_19		     string
    log_archive_dest_2		     string	 SERVICE=racdb1 LGWR SYNC AFFIR
    						             M VALID_FOR=(ONLINE_LOGFILES,P
    						             RIMARY_ROLE) DB_UNIQUE_NAME=racdb
    log_archive_dest_20		     string
    log_archive_dest_21		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    log_archive_dest_22		     string
    log_archive_dest_23		     string
    log_archive_dest_24		     string
    log_archive_dest_25		     string
    log_archive_dest_26		     string
    log_archive_dest_27		     string
    log_archive_dest_28		     string
    log_archive_dest_29		     string
    log_archive_dest_3		     string
    log_archive_dest_30		     string
    log_archive_dest_31		     string
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    log_archive_dest_4		     string
    log_archive_dest_5		     string
    log_archive_dest_6		     string
    log_archive_dest_7		     string
    log_archive_dest_8		     string
    log_archive_dest_9		     string
    log_archive_dest_state_1	     string	 enable
    log_archive_dest_state_10	     string	 enable
    log_archive_dest_state_11	     string	 enable
    log_archive_dest_state_12	     string	 enable
    log_archive_dest_state_13	     string	 enable
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    log_archive_dest_state_14	     string	 enable
    log_archive_dest_state_15	     string	 enable
    log_archive_dest_state_16	     string	 enable
    log_archive_dest_state_17	     string	 enable
    log_archive_dest_state_18	     string	 enable
    log_archive_dest_state_19	     string	 enable
    log_archive_dest_state_2	     string	 enable
    log_archive_dest_state_20	     string	 enable
    log_archive_dest_state_21	     string	 enable
    log_archive_dest_state_22	     string	 enable
    log_archive_dest_state_23	     string	 enable
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_24	     string	 enable
    log_archive_dest_state_25	     string	 enable
    log_archive_dest_state_26	     string	 enable
    log_archive_dest_state_27	     string	 enable
    log_archive_dest_state_28	     string	 enable
    log_archive_dest_state_29	     string	 enable
    log_archive_dest_state_3	     string	 enable
    log_archive_dest_state_30	     string	 enable
    log_archive_dest_state_31	     string	 enable
    log_archive_dest_state_4	     string	 enable
    log_archive_dest_state_5	     string	 enable
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    log_archive_dest_state_6	     string	 enable
    log_archive_dest_state_7	     string	 enable
    log_archive_dest_state_8	     string	 enable
    log_archive_dest_state_9	     string	 enable
    log_archive_duplex_dest 	     string
    log_archive_format		     string	 %t_%s_%r.dbf
    log_archive_local_first 	     boolean	 TRUE
    log_archive_max_processes	     integer	 4
    log_archive_min_succeed_dest	     integer	 1
    log_archive_start		     boolean	 FALSE
    log_archive_trace		     integer	 0
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ----------------
    standby_archive_dest		     string	 ?/dbs/arch
    

    19.在备库上用rman恢复数据

    [oracle@standydb~]$ rman target sys/密码@racdb1 auxiliary  sys/密码@dg1
    Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 20:22:42 2015
    Copyright (c) 1982,2009, Oracle and/or its affiliates.  Allrights reserved.
    connected to targetdatabase: RACDB (DBID=1109864007)
    connected toauxiliary database: RACDB (not mounted)
    RMAN>duplicate target database for standby from active database nofilenamecheck;  
    --如果遇到ora-17628,则是相关路径没有建立rman里查看report schema
    看到Finished DuplicateDb at 17-MAR-15代表成功。
    

    20.在主库和备库上分别添加备库重做日志组
    RAC有两个redo thread,每个thread有两个日志组,每个日志组有一个日志文件,文件大小为50M。
    主库查看

    SQL> select thread#,group#,members,bytes/1024/1024 from v$log;
       THREAD#     GROUP#    MEMBERS BYTES/1024/1024
    ---------- ---------- ---------- ---------------
             1          1          1              50
             1          2          1              50
             2          3          1              50
             2          4          1              50
    
    SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;
        GROUP# STATUS  TYPE    MEMBER
    ---------- ------- ------- --------------------------------------
             2         ONLINE  +DATA/paydb/onlinelog/group_2.262.927484759
             1         ONLINE  +DATA/paydb/onlinelog/group_1.261.927484751
             3         ONLINE  +DATA/paydb/onlinelog/group_3.266.927485173
             4         ONLINE  +DATA/paydb/onlinelog/group_4.267.927485181
    

    主库添加备库重做日志组

    alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M;
    alter database add standby logfile thread 2 group 7 size 50M,group 8 size 50M;
    

    备库查看状态

    SQL> select instance_name,status from v$instance;
     INSTANCE_NAME    STATUS
    ---------------- ------------
    racdg            MOUNTED
    
    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    SWITCHOVER_STATUS
    --------------------
    RECOVERY NEEDED
    

    备库添加备库重做日志组

    alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/dg1/styredo05.log','/u01/app/oracle/oradata/arclog/styarc05.log') size 50M;
    alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/dg1/styredo06.log','/u01/app/oracle/oradata/arclog/styarc06.log') size 50M;
    alter database add standby logfile thread 2 group 7('/u01/app/oracle/oradata/dg1/styredo07.log','/u01/app/oracle/oradata/arclog/styarc07.log') size 50M;
    alter database add standby logfile thread 2 group 8('/u01/app/oracle/oradata/dg1/styredo08.log','/u01/app/oracle/oradata/arclog/styarc08.log') size 50M;
    

    以下是关于备库重做日志组的要求:

    --a)、确保standbyredo log的大小与主库online redolog的大小一致
    --b)、如主库为单实例数据库:standbyredo log组数=主库日志组总数+1?
    --c)、如果主库是RAC数据库:standby redo log组数=(每线程的日志组数+1)*线程数?
    --d)、不建议复用standbyredo log,避免增加额外的I/O以及延缓重做传输
    

    为了确保安全,上述试验并没有按照B和C的要求来,有兴趣的同学可以试一试按照上述要求来会不会出错误。
    21.在standby端开启实时日志应用

    recover managed standby database using current logfile disconnect from session;  
    Media recovery complete. 
    

    22.测试ADG
    ①执行日志切换测试(在pri端切换归档,在节点二上检查是否也发生了切换)
    –primary执行日志切换

    archive log list; 
    Current log sequence          18
    
    alter system switch logfile;
    
    archive log list;
    Current log sequence          19
    

    –standby查看日志的sequence号也跟着变了

    archive log list;
    Current log sequence          19
    

    ②查看standby启动的DG进程

    select process,client_process,sequence#,status from v$managed_standby;  
    PROCESS  CLIENT_P  SEQUENCE# STATUS  
    --------- -------- ---------- ------------  
    ARCH      ARCH            23 CLOSING  
    ARCH      ARCH              0 CONNECTED            //归档进程  
    ARCH      ARCH            21 CLOSING  
    ARCH      ARCH              0 CONNECTED  
    RFS      ARCH              0 IDLE  
    RFS      UNKNOWN          0 IDLE  
    RFS      LGWR            24 IDLE                  //归档传输进程  
    RFS      UNKNOWN          0 IDLE  
    MRP0      N/A              24 APPLYING_LOG      //日志应用进程       ---这个地方要是appying log就对了
    

    ③查看数据库的保护模式
    #primary 端查看,我们可以看到数据库的保护模式为最大性能

    select database_role,protection_mode,protection_level,open_mode from v$database;  
      
    DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
    ---------------- -------------------- -------------------- --------------------  
    PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE  
    

    #standby 端查看,也是一样的。

    select database_role,protection_mode,protection_level,open_mode from v$database;  
      
    DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
    ---------------- -------------------- -------------------- --------------------  
    PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  MOUNTED
    

    ④Open Read Only standby数据库并且开启实时日志应用

    shutdown immediate  
    startup                 
    select OPEN_MODE from v$database ;            
    OPEN_MODE
    --------------------
    READ ONLY
    

    注意:如果无法启动—如果无法open,很有可能参数文件的fal_server和fal_client与tnsnames.ora的字符串不符合会报ora_10458

    select database_role,protection_mode,protection_level,open_mode from v$database;  
    DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE  
    ---------------- -------------------- -------------------- --------------------  
    PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ ONLY  
    
    select process,client_process,sequence#,status from v$managed_standby;  
    PROCESS  CLIENT_P  SEQUENCE# STATUS  
    --------- -------- ---------- ------------  
    ARCH      ARCH              0 CONNECTED  
    ARCH      ARCH              0 CONNECTED  
    ARCH      ARCH              0 CONNECTED  
    ARCH      ARCH            26 CLOSING  
    RFS      ARCH              0 IDLE  
    RFS      UNKNOWN          0 IDLE  
    RFS      LGWR            27 IDLE  
    

    ⑤开启到read only with apply模式

    recover managed standby database using current logfile disconnect from session;  
    select OPEN_MODE from v$database ;
    OPEN_MODE
    --------------------
    READ ONLY WITH APPLY
    
    1. 解锁scott用户,添加数据,验证数据是否能同步
      #在primary端创建解锁scott用户并创建测试表
      #primary 端操作如下内容
    alter user scott account unlock identified by tiger;  
    conn scott/tiger;  
    
    create table d1 as select * from dept;  
      
    update d1 set deptno=90;
      
    commit;  
    select * from d1;  
    

    #standby端查询scott用户是否解锁,以及d1表是否创建并且更新数据:

    conn scott/tiger;  
    select * from tab;    
      
    TNAME                          TABTYPE  CLUSTERID  
    ------------------------------ ------- ----------  
    BONUS                          TABLE  
    DEPT                          TABLE  
    EMP                            TABLE  
    SALGRADE                      TABLE  
    d1                        TABLE  
      
    select * from d1;  
    

    #至此Oracle 11g RAC+ADG配置完成

    以下是主备模式切换,本人并没有做过以下试验,有兴趣的同学可以自己试验一下。注意路径要正确。
    1、 因环境中primaryDB是双节点Rac集群,standbyDB为单节点。在切换时需要将rac节点中关闭racdb2节点实例。对于为什么关闭racdb2节点,原因是在standbyDB Initialization Parameters 中配置接收点为racdb1

    SQL>show parameter log_archive_dest_2
     
    NAME                            TYPE                          VALUE
    -------------------------------- ------------------------------
    log_archive_dest_2         string      service=racdb1 async valid_for=(online_logfiles,primary_role)
    db_unique_name=racdg
    [grid@racdg2 ~]$ srvctl stop instance -d racdg -i racdb2
    查看alter_racdb2.log
    

    在这里插入图片描述

    2、 检查主库switchover_status
    PrimaryDB

    SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
    
    SWITCHOVER#       SWITCHOVER_STATUS    DATABASE_ROLE
    ------------------   ------------- ----------------
     1110966030              TO STANDBY           PRIMARY
    StandbyDB
    SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
     
    SWITCHOVER#SWITCHOVER_STATUS    DATABASE_ROLE
    ------------------------------- ----------------
     1110966030    NOT ALLOWED           PHYSICAL STANDBY
    

    3、 开启式切换primaryDB
    ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;
    切换完成后,查看实例状态
    在这里插入图片描述
    4、 关闭实例racdg1并启动到mount状态

    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP MOUNT;
    

    5、 查看standbyDB 状态,并查看

    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
     
    SWITCHOVER_STATUS
    --------------------
    TOPRIMARY
    

    6、 切换physicalstandby DB to prmary role

    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
    

    7、 创建接收日志文件
    查看logfile
    在这里插入图片描述
    添加日志文件

    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5
    ('+DATA1/racdg/onlinelog/slog5_1.rdo','+DATA2/racdg/onlinelog/slog5_2.rdo') SIZE 50M;
     
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6
      ('+DATA1/racdg/onlinelog/slog6_1.rdo','+DATA2/racdg/onlinelog/slog6_2.rdo') SIZE 50M;
     
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 7
      ('+DATA1/racdg/onlinelog/slog7_1.rdo','+DATA2/racdg/onlinelog/slog7_2.rdo') SIZE 50M;
      
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8
      ('+DATA1/racdg/onlinelog/slog8_1.rdo','+DATA2/racdg/onlinelog/slog8_2.rdo') SIZE 50M;
    

    8、 打开新的primaryDB

    SQL> alter database open;
    

    9、 在新的physicalstandby DB上Star redo log

    SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
      2  DISCONNECT FROM SESSION;
     
    Database altered.
    

    10、查看状态
    新的physical standby DB
    在这里插入图片描述
    新的primary DB
    在这里插入图片描述
    到此为止,切换已经完成。
    查看并验证:
    第一种:
    在新的primary DB 节点上查看

    SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
    SQL> alter system switch logfile;
    

    在这里插入图片描述
    在新的standby DB 节点上查看

    SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
    

    在这里插入图片描述
    这说明包括primary 上做了一次alter system switch logfile之后的142,和140 、141 一并同步过来了。
    第二种:
    删除表空间DBF

    drop tablespace VAV1 including contents anddatafiles cascade constraints;
    

    1、 查看
    New primary 节点
    在这里插入图片描述
    New physical standby DB
    在这里插入图片描述
    2、 删除VAV1表空间

    drop tablespace VAV1 including contentsand datafiles cascade constraints;
    

    3、 在new physicalstandby DB 节点查看
    在这里插入图片描述
    到此说明switchover primary to standby 成功

    展开全文

空空如也

1 2 3 4 5 ... 20
收藏数 1,352
精华内容 540
关键字:

ADG