精华内容
参与话题
问答
  • oracleADG搭建

    2020-03-31 23:02:27
    整体规划 主库单实例>>>>>>>>备库单实例 主库操作 1.主库开启归档 检查归档模式 archive log list 开启归档 shutdown immediate; startup mount; alter database ...select force_...

    整体规划
    在这里插入图片描述
    主库单实例>>>>>>>>备库单实例

    主库操作
    1.主库开启归档
    检查归档模式

    archive log list
    

    开启归档

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
    

    2.主库开启强日志

    select force_logging from v$database;
    

    开启强日志

    alter database force logging;
    

    3.主库增加standbylog
    查看当前日志组大小,位置。

    set linesize 200
    set pagesize 1000
    col group# format 99
    col (a.bytes)/1024/1024 format 99
    col a.thread# format 9 
    col member format a50 
    col a.status format a20
    select a.group#,(a.bytes)/1024/1024,a.thread#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
    

    整理standby

    alter database add standby logfile  group 4 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 5 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 6 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 7 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    

    4.主库修改参数

    alter system set standby_file_management=MANUAL scope=both sid='*';
    alter system set log_archive_config='dg_config=(orcl,dgiscdb)' scope=both ;alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog  valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both;alter system set log_archive_dest_2='service=dgiscdb LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dgiscdb' scope=both;
    alter system set log_archive_dest_state_2='enable' scope=both;
    alter system set log_archive_dest_state_1='enable' scope=both;alter system set fal_server='dgiscdb' scope=both ;
    alter system set log_archive_max_processes=10 scope=both;
    alter system set db_file_name_convert='/u01/app/oracle/oradata/dgiscdb','/u01/app/oracle/oradata/orcl'scope=spfile;
    alter system set log_file_name_convert='/u01/app/oracle/oradata/dgiscdb','/u01/app/oracle/oradata/orcl' scope=spfile;
    alter system set standby_file_management=AUTO scope=both sid='*';
    

    5.主库配置静态监听和tns
    配置静态监听

    SID_LIST_LISTENER_ORCL =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl)     (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)     (SID_NAME = orcl)    )  )LISTENER_ORCL =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    ) )
    

    配置tns

    orcl =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)	  (SID = orcl)    )  )dgiscdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.1113)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = dgiscdb)          (SID = dgiscdb)    )  )
    

    **

    备库操作

    **
    1.备库配置静态监听
    配置静态监听

    cd $ORACLE_HOME/network/admin
    
    SID_LIST_LISTENER_ORCL =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = dgiscdb)     (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)     (SID_NAME = dgiscdb)    )  )LISTENER_ORCL =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.113)(PORT = 1523))    ) )
    

    配置tns

    orcl =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.112)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)	  (SID = orcl1)    )  )dgiscdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.172.113)(PORT = 1523))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = dgiscdb)          (SID = dgiscdb)    )  )
    

    2.将主库密码文件传至备库

    cd $ORACLE_HOME/dbs
    scp -P 22 192.168.172.112:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl .
    

    3.测试主备库监听和tnsname可用性

    tnsping orcl
    tnsping dgiscdb
    sqlplus  sys/oracle@orcl as sysdba
    sqlplus  sys/oracle@dgiscdb as sysdba
    

    4创建审计目录

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

    5.备库准备脚本(复制数据库)

    1)touch /home/oracle/standby_init.oracat >>/home/oracle/standby_init.ora<<ADB_NAME=orclDB_UNIQUE_NAME=dgiscdbDB_BLOCK_SIZE=8192db_create_file_dest='/u01/app/oracle/oradata/dgiscdb'  sga_target = '1G'log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'control_files='/u01/app/oracle/oradata/dgiscdb/dgsicdbcontrol.ctl' A
    
    touch /home/oracle/init.shchmod +x  /home/oracle/init.shcat >>/home/oracle/init.sh<<Asqlplus "/ as sysdba" << !shutdown abortstartup nomount pfile='/home/oracle/standby_init.ora'connect  sys/oracle@orcl as sysdbaconnect  sys/oracle@dgiscdb as sysdba!rman target sys/oracle@orcl auxiliary sys/oracle@dgiscdb  << !run {allocate channel ch001 type disk;allocate channel ch002 type disk;allocate auxiliary channel ch003 type disk;duplicate target database for standby from active databasespfile  parameter_value_convert 'orcl','dgiscdb'  set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dgiscdb','/home/oracle/isc.dbf','/u01/app/oracle/oradata/dgiscdb'  set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dgiscdb'  set db_name='orcl'  set db_unique_name='dgiscdb'  set instance_name='dgiscdb'  set standby_file_management='AUTO'  set log_archive_dest_2='service=orcl LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=orcl'  set sga_target = '1024M'  set db_create_online_log_dest_1 = '/u01/app/oracle/oradata/dgiscdb'  set instance_number = '1'  set control_files='/u01/app/oracle/oradata/dgiscdb/dgiscdbcontrol.ctl'  set fal_server='orcl';release channel ch001;release channel ch002;release channel ch003;}!A
    

    6.备库增加standby log(如果主库创建了standbyredolog那么会自动传递到备库的)

    alter database add standby logfile  group 4 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 5 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 6 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    alter database add standby logfile  group 7 '/u01/app/oracle/oradata/orcl/standby.log' size 50M;
    

    7)此时备库在mount状态,等待追归档然后open

    alter database open;
    

    发现的问题,如果备库归档目录有文件时会有如下错误
    在这里插入图片描述

    ADG维护常用sql
    停止日志应用

    alter database recover managed standby database cancel;
    

    开启日志应用

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

    时刻监控alert日志
    1.查看是否为ADG模式

    select 'Using Active Data Guard' ADG from v$MANAGED_STANDBY M, v$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE like 'READ ONLY%';
    

    2.查看主备日志序列号

    select thread#,max(sequence#) from gv$archived_log group by thread#; 
    

    3.查看为什么归档没有同步到standby端

    set line 400
    col DEST_NAME for a20
    col DESTINATION for a10
    select dest_id,dest_name,status,fail_date ,ERROR from V$ARCHIVE_DEST where rownum <4;
    

    4.查看主备角色

    select database_role,open_mode from v$database;
    select sequence#,applied from v$archived_log order by 1;
    

    时时观察alert日志。
    5.断档处理(将主库归档拷贝至备库)

    alter database register physical logfile '日志文件';
    
    展开全文
  • Oracle ADG搭建

    2019-10-01 03:54:01
    Oracle Active Data Guard搭建 一:安装 1.基础环境配置 1.1.开启强制日志记录 DG日志发送方式中ARCH进程和LGWR进程的ASYNC模式都是基于日志同步的,所以我们必须强制将数据库的所有操作记录到日志中 RAC2:关闭库...

    Oracle Active Data Guard搭建

    一:安装

    1.基础环境配置

    1.1.开启强制日志记录

    • DG日志发送方式中ARCH进程和LGWR进程的ASYNC模式都是基于日志同步的,所以我们必须强制将数据库的所有操作记录到日志中

    RAC2:关闭库

      SQL> shutdown
      Database closed.
      Database dismounted.
      ORACLE instance shut down.

    RAC1:开启强制日志记录

      SQL> alter database force logging;   
    
      Database altered.

    ALL Node:开启rac2,并验证日志记录模式

      RAC1
      SQL> select log_mode,force_logging from v$database;
    
      LOG_MODE     FOR
      ------------ ---
      ARCHIVELOG   YES
    
      RAC2
      SQL> startup  
      ORACLE instance started.
    
      Total System Global Area 2020970496 bytes
      Fixed Size            2214776 bytes
      Variable Size      1224737928 bytes
      Database Buffers    788529152 bytes
      Redo Buffers          5488640 bytes
      Database mounted.
      Database opened.
      SQL> select log_mode,force_logging from v$database;
    
      LOG_MODE     FOR
      ------------ ---
      ARCHIVELOG   YES

    1.2.主库备份

    • 创建rman备份目录
      [root@racnode1 u01]# mkdir rmanbak
      [root@racnode1 u01]# ls
      app  rmanbak
      [root@racnode1 u01]# chown oracle:oinstall rmanbak
    • 使用rman对database和archive log进行备份
      [oracle@racnode1 ~]$ rman target /
    
      Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 9 10:26:21 2017
    
      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
      connected to target database: ORCL (DBID=1479188731)
    
      RMAN> run {
      allocate channel c1 type disk;
      allocate channel c2 type disk;
      allocate channel c3 type disk;
      allocate channel c4 type disk;
      backup database format '/u01/rmanbak/FULL_%U.bak';
      backup archivelog all format '/u01/rmanbak/ARC_%U.bak';
      release channel c1;
      release channel c2;
      release channel c3;
      release channel c4;
      }
    
      using target database control file instead of recovery catalog
      allocated channel: c1
      channel c1: SID=148 instance=orcl1 device type=DISK
    
      allocated channel: c2
      channel c2: SID=29 instance=orcl1 device type=DISK
    
      allocated channel: c3
      channel c3: SID=156 instance=orcl1 device type=DISK
    
      allocated channel: c4
      channel c4: SID=32 instance=orcl1 device type=DISK
    
      Starting backup at 09-AUG-2017 10:26:44
      channel c1: starting full datafile backup set
      channel c1: specifying datafile(s) in backup set
      input datafile file number=00001 name=+DATA/orcl/datafile/system.256.951407925
      channel c1: starting piece 1 at 09-AUG-2017 10:26:44
      channel c2: starting full datafile backup set
      channel c2: specifying datafile(s) in backup set
      input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.951407925
      input datafile file number=00004 name=+DATA/orcl/datafile/users.259.951407925
      channel c2: starting piece 1 at 09-AUG-2017 10:26:44
      channel c3: starting full datafile backup set
      channel c3: specifying datafile(s) in backup set
      input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.951407925
      input datafile file number=00005 name=+DATA/orcl/datafile/undotbs2.264.951408055
      channel c3: starting piece 1 at 09-AUG-2017 10:26:45
      channel c4: starting full datafile backup set
      channel c4: specifying datafile(s) in backup set
      including current control file in backup set
      channel c4: starting piece 1 at 09-AUG-2017 10:26:52
      channel c3: finished piece 1 at 09-AUG-2017 10:26:56
      piece handle=/u01/rmanbak/FULL_03sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
      channel c3: backup set complete, elapsed time: 00:00:11
      channel c3: starting full datafile backup set
      channel c3: specifying datafile(s) in backup set
      including current SPFILE in backup set
      channel c3: starting piece 1 at 09-AUG-2017 10:26:57
      channel c4: finished piece 1 at 09-AUG-2017 10:26:57
      piece handle=/u01/rmanbak/FULL_04sbfaj5_1_1.bak tag=TAG20170809T102644 comment=NONE
      channel c4: backup set complete, elapsed time: 00:00:05
      channel c1: finished piece 1 at 09-AUG-2017 10:26:57
      piece handle=/u01/rmanbak/FULL_01sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
      channel c1: backup set complete, elapsed time: 00:00:13
      channel c2: finished piece 1 at 09-AUG-2017 10:26:58
      piece handle=/u01/rmanbak/FULL_02sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
      channel c2: backup set complete, elapsed time: 00:00:14
      channel c3: finished piece 1 at 09-AUG-2017 10:26:58
      piece handle=/u01/rmanbak/FULL_05sbfajh_1_1.bak tag=TAG20170809T102644 comment=NONE
      channel c3: backup set complete, elapsed time: 00:00:01
      Finished backup at 09-AUG-2017 10:26:58
    
      Starting backup at 09-AUG-2017 10:26:59
      current log archived
      channel c1: starting archived log backup set
      channel c1: specifying archived log(s) in backup set
      input archived log thread=1 sequence=5 RECID=1 STAMP=951408120
      input archived log thread=2 sequence=1 RECID=2 STAMP=951408150
      input archived log thread=1 sequence=6 RECID=5 STAMP=951447623
      channel c1: starting piece 1 at 09-AUG-2017 10:26:59
      channel c2: starting archived log backup set
      channel c2: specifying archived log(s) in backup set
      input archived log thread=2 sequence=2 RECID=3 STAMP=951408156
      input archived log thread=2 sequence=3 RECID=4 STAMP=951447623
      input archived log thread=2 sequence=4 RECID=8 STAMP=951555111
      channel c2: starting piece 1 at 09-AUG-2017 10:26:59
      channel c3: starting archived log backup set
      channel c3: specifying archived log(s) in backup set
      input archived log thread=1 sequence=7 RECID=6 STAMP=951516208
      input archived log thread=1 sequence=8 RECID=7 STAMP=951555108
      channel c3: starting piece 1 at 09-AUG-2017 10:26:59
      channel c4: starting archived log backup set
      channel c4: specifying archived log(s) in backup set
      input archived log thread=1 sequence=9 RECID=11 STAMP=951560819
      input archived log thread=2 sequence=5 RECID=9 STAMP=951558975
      input archived log thread=2 sequence=6 RECID=10 STAMP=951560429
      channel c4: starting piece 1 at 09-AUG-2017 10:26:59
      channel c1: finished piece 1 at 09-AUG-2017 10:27:00
      piece handle=/u01/rmanbak/ARC_06sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
      channel c1: backup set complete, elapsed time: 00:00:01
      channel c2: finished piece 1 at 09-AUG-2017 10:27:00
      piece handle=/u01/rmanbak/ARC_07sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
      channel c2: backup set complete, elapsed time: 00:00:01
      channel c3: finished piece 1 at 09-AUG-2017 10:27:00
      piece handle=/u01/rmanbak/ARC_08sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
      channel c3: backup set complete, elapsed time: 00:00:01
      channel c4: finished piece 1 at 09-AUG-2017 10:27:00
      piece handle=/u01/rmanbak/ARC_09sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
      channel c4: backup set complete, elapsed time: 00:00:01
      Finished backup at 09-AUG-2017 10:27:00
    
      released channel: c1
    
      released channel: c2
    
      released channel: c3
    
      released channel: c4
    • 验证备份
      [oracle@racnode1 ~]$ ll -th /u01/rmanbak/
      总用量 1.3G
      -rw-r----- 1 oracle asmadmin  89M 8月   9 10:27 ARC_08sbfajj_1_1.bak
      -rw-r----- 1 oracle asmadmin  81M 8月   9 10:27 ARC_07sbfajj_1_1.bak
      -rw-r----- 1 oracle asmadmin 4.5M 8月   9 10:26 ARC_09sbfajj_1_1.bak
      -rw-r----- 1 oracle asmadmin  30M 8月   9 10:26 ARC_06sbfajj_1_1.bak
      -rw-r----- 1 oracle asmadmin 424M 8月   9 10:26 FULL_02sbfaj4_1_1.bak
      -rw-r----- 1 oracle asmadmin  96K 8月   9 10:26 FULL_05sbfajh_1_1.bak
      -rw-r----- 1 oracle asmadmin 588M 8月   9 10:26 FULL_01sbfaj4_1_1.bak
      -rw-r----- 1 oracle asmadmin  18M 8月   9 10:26 FULL_04sbfaj5_1_1.bak
      -rw-r----- 1 oracle asmadmin 3.6M 8月   9 10:26 FULL_03sbfaj4_1_1.bak

    1.3.在Primary上创建Standby Database的控制文件

    SQL> alter database create standby controlfile as'/u01/rmanbak/standby.ctl';
    
    Database altered.

    1.4.在Primary上创建Standby Database初始化参数文件

    SQL> create pfile='/u01/rmanbak/initphyracdb.ora' from spfile;
    
    File created.

    1.5.拷贝密码文件

    [oracle@racnode1 dbs]$ pwd
    /u01/app/oracle/product/11.2.0/db_1/dbs
    [oracle@racnode1 dbs]$ cp orapworcl1 /u01/rmanbak/

    1.6.拷贝/rmanbak下的所有备份文件到备库

    [oracle@racnode1 rmanbak]$ scp * oracle@192.168.30.134:/u01/rmanbak/
    The authenticity of host '192.168.30.134 (192.168.30.134)' can't be established.
    RSA key fingerprint is 43:e6:68:ce:37:01:1f:12:46:19:27:39:a5:ba:02:7d.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '192.168.30.134' (RSA) to the list of known hosts.
    oracle@192.168.30.134's password: 
    ARC_06sbfajj_1_1.bak                                     100%   29MB  29.4MB/s   00:00    
    ARC_07sbfajj_1_1.bak                                     100%   81MB  80.8MB/s   00:00    
    ARC_08sbfajj_1_1.bak                                     100%   88MB  88.0MB/s   00:01    
    ARC_09sbfajj_1_1.bak                                     100% 4598KB   4.5MB/s   00:00    
    FULL_01sbfaj4_1_1.bak                                    100%  587MB 117.4MB/s   00:05    
    FULL_02sbfaj4_1_1.bak                                    100%  424MB  84.8MB/s   00:05    
    FULL_03sbfaj4_1_1.bak                                    100% 3680KB   3.6MB/s   00:00    
    FULL_04sbfaj5_1_1.bak                                    100%   18MB  17.7MB/s   00:01    
    FULL_05sbfajh_1_1.bak                                    100%   96KB  96.0KB/s   00:00    
    initphyracdb.ora                                         100% 1390     1.4KB/s   00:00    
    orapworcl1                                               100% 1536     1.5KB/s   00:00    
    standby.ctl                                              100%   18MB  17.6MB/s   00:00

    1.7.将拷贝到备库上的初始化文件,并修改

    orcl.__db_cache_size=771751936
    orcl.__java_pool_size=16777216
    orcl.__large_pool_size=16777216
    orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=822083584
    orcl.__sga_target=1207959552
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=385875968
    orcl.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
    *.audit_trail='db'
    *.cluster_database=false
    *.compatible='11.2.0.0.0'
    # 指定控制文件路径
    *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
    *.db_block_size=8192
    *.db_create_file_dest='/u01/app/oracle/oradata/orcl'
    *.db_domain=''
    *.db_name='orcl'
    *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=10737418240
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    orcl.instance_number=1
    # 归档配置
    *.log_archive_config='dg_config=(orcl,rac)'
    # 本地归档
    *.log_archive_dest_1='LOCATION=/u01/archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
    # 远程归档
    *.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac'
    *.log_archive_dest_state_1='ENABLE'
    *.log_archive_dest_state_2='ENABLE'
    *.log_archive_format='%t_%s_%r.dbf'
    *.log_archive_max_processes=5
    *.memory_target=2030043136
    *.open_cursors=300
    *.processes=150
    # 启用本地密码,默认即可
    *.remote_login_passwordfile='exclusive'
    orcl.thread=1
    *.service_names='racdg'
    *.standby_file_management='auto'
    orcl.undo_tablespace='UNDOTBS1'
    # dbf文件路径转换
    *.db_file_name_convert='+DATA/ORCL/DATAFILE','/u01/app/oracle/oradata/orcl'
    *.db_unique_name='orcl'
    # 当前节点【对应监听文件】
    *.fal_client='orcl'
    # 主节点【对应监听文件】
    *.fal_server='rac1','rac2'
    # 日志文件路径转换
    *.log_file_name_convert='+REDO/ORCL','/u01/archive_log','+DATA/orcl','/u01/group_log'

    1.8.使用修改后的初始化文件启动备库到nomount状态

    SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initphyracdb.ora' nomount;
    ORACLE instance started.
    
    Total System Global Area 2020970496 bytes
    Fixed Size          2254664 bytes
    Variable Size        1224739000 bytes
    Database Buffers      788529152 bytes
    Redo Buffers            5447680 bytes

    1.9.备库恢复控制文件

    restore controlfile from '/u01/rmanbak/standby.ctl';

    1.10.切换备库到mount状态

    SQL> alter database mount;
    
    Database altered.
    
    SQL> select  status from v$instance;
    
    STATUS
    ------------------------------------
    MOUNTED

    1.20.使用rman将主库数据恢复到备库

    会自动识别当前目录下的备份文件

    RMAN> run {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate channel c4 type disk;
    restore database ;
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    } 
    
    released channel: ORA_DISK_1
    allocated channel: c1
    channel c1: SID=129 device type=DISK
    
    allocated channel: c2
    channel c2: SID=193 device type=DISK
    
    allocated channel: c3
    channel c3: SID=7 device type=DISK
    
    allocated channel: c4
    channel c4: SID=69 device type=DISK
    
    Starting restore at 09-AUG-17
    Starting implicit crosscheck backup at 09-AUG-17
    Crosschecked 9 objects
    Finished implicit crosscheck backup at 09-AUG-17
    
    Starting implicit crosscheck copy at 09-AUG-17
    Finished implicit crosscheck copy at 09-AUG-17
    
    searching for all files in the recovery area
    cataloging files...
    no files cataloged
    
    
    channel c1: starting datafile backup set restore
    channel c1: specifying datafile(s) to restore from backup set
    channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl//undotbs1.258.951407925
    channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl//undotbs2.264.951408055
    channel c1: reading from backup piece /u01/rmanbak/FULL_03sbfaj4_1_1.bak
    channel c2: starting datafile backup set restore
    channel c2: specifying datafile(s) to restore from backup set
    channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/orcl//system.256.951407925
    channel c2: reading from backup piece /u01/rmanbak/FULL_01sbfaj4_1_1.bak
    channel c3: starting datafile backup set restore
    channel c3: specifying datafile(s) to restore from backup set
    channel c3: restoring datafile 00002 to /u01/app/oracle/oradata/orcl//sysaux.257.951407925
    channel c3: restoring datafile 00004 to /u01/app/oracle/oradata/orcl//users.259.951407925
    channel c3: reading from backup piece /u01/rmanbak/FULL_02sbfaj4_1_1.bak
    channel c1: piece handle=/u01/rmanbak/FULL_03sbfaj4_1_1.bak tag=TAG20170809T102644
    channel c1: restored backup piece 1
    channel c1: restore complete, elapsed time: 00:00:01
    channel c3: piece handle=/u01/rmanbak/FULL_02sbfaj4_1_1.bak tag=TAG20170809T102644
    channel c3: restored backup piece 1
    channel c3: restore complete, elapsed time: 00:00:07
    channel c2: piece handle=/u01/rmanbak/FULL_01sbfaj4_1_1.bak tag=TAG20170809T102644
    channel c2: restored backup piece 1
    channel c2: restore complete, elapsed time: 00:00:15
    Finished restore at 09-AUG-17
    
    released channel: c1
    
    released channel: c2
    
    released channel: c3
    
    released channel: c4

    1.11.添加备库的日志组

    SQL> alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M;
    
    Database altered.
    
    SQL> alter database add standby logfile thread 2 group 8 size 50M,group 9 size 50M,group 10 size 50M;
    
    Database altered.

    1.12.主库参数调整

    # 设置本地归档路径
    SQL> alter system set log_archive_dest_1='LOCATION=+REDO VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
    
    System altered.
    
    # 设置远程归档路径
    SQL> alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';
    
    System altered.
    
    
    # 验证归档设置
    SQL> show parameter log_archive
    
    # 开启远程归档传输
    SQL> alter system set log_archive_dest_state_2=enable;
    
    System altered.

    1.13.开启备库应用日志

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

    ​ 执行上条命令后,Standby database应为是延迟传输,只有在归档时,才会发生应用,所以在查询日志应用情况时,会发现在当前日志会显示IN MEMORY。

    SQL> select thread#, sequence#, applied from v$archived_log;
       THREAD#  SEQUENCE# APPLIED
    ---------- ---------- ---------
         1    132 YES
         2    126 YES
         1    133 YES
         1    134 IN-MEMORY

    1.14.开启ADG

    ADG:11G的新特性,在备库上已open状态,打开日志应用进程,默认会 READ ONLY WITH APPLY状态模式,在应用日志的同时也可以读standby database进行查询。11G之间,应用日志时,只能以mount模式打开。

    Standby Database

    • 取消日志应用
      SQL> alter database recover managed standby database cancel;
    
      Database altered.
    • 以open模式打开数据库
      SQL> alter database open;
    
      Database altered.
    • 开启日志应用
      SQL> alter database recover managed standby database using current logfile disconnect from session;
    
      Database altered.
    • 查询数据库模式是否正常
      SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
    
            DBID NAME      OPEN_MODE          CURRENT_SCN PROTECTION_MODE
      ---------- --------- -------------------- ----------- --------------------
      DATABASE_ROLE  FOR OPEN_MODE        SWITCHOVER_STATUS
      ---------------- --- -------------------- --------------------
      1479188731 ORCL      READ ONLY WITH APPLY     3638179 MAXIMUM PERFORMANCE
      PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

    2.验证DG状态是否正常

    2.1.查看archive log序列是否一致

    Primary Database

    SQL> archive log list;
    Database log mode          Archive Mode
    Automatic archival         Enabled
    Archive destination        +REDO
    Oldest online log sequence     21
    Next log sequence to archive   22
    Current log sequence           22

    Standby Database

    SQL> archive log list;
    Database log mode          Archive Mode
    Automatic archival         Enabled
    Archive destination        /u01/app/oracle/arch
    Oldest online log sequence     21
    Next log sequence to archive   0
    Current log sequence           22

    2.2.查看DG状态

    Primary Database

    SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
    
          DBID NAME      OPEN_MODE        CURRENT_SCN PROTECTION_MODE
    ---------- --------- -------------------- ----------- --------------------
    DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS
    ---------------- --- -------------------- --------------------
    1479188731 ORCL      READ WRITE           1407507 MAXIMUM PERFORMANCE
    PRIMARY      YES READ WRITE       TO STANDBY
    • PROTECTION_MODE:MAXIMUM PERFORMANCE 默认最大性能模式
    • DATABASE_ROLE:PRIMARY 角色
    • SWITCHOVER_STATUS:切换状态(可以查看出当前DG状态)
      • NOT ALLOWED 当前库无备库
      • TO STANDBY 当前库可以切换成备库
      • RESOLVABLE GAP 主库和备库之间有GAP间隙

    Standby Database

    2.3.验证日志应用

    select thread#, sequence#, applied from v$archived_log;

    2.4.查看相应进程是否起来

    select process from v$managed_standby;

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

    展开全文
  • rac adg 搭建

    2018-10-20 19:39:54
      How to Setup Active DataGuard on Exadata (文档 ID 1580796.1) 转到底部 In this Document   Goal   Solution   References ... O...

     
    单击此项可添加到收藏夹 How to Setup Active DataGuard on Exadata (文档 ID 1580796.1) 转到底部转到底部

    In this Document

      Goal
      Solution
      References

     

    Applies to:

    Oracle Exadata Storage Server Software - Version 11.2.2.3.5 to 11.2.3.2.1 [Release 11.2]
    Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.4 [Release 11.2]
    Information in this document applies to any platform.

    Goal

    Purpose of this article is too provide step by step instructions for deploying Active DataGuard Configuration within an Exadata Environment.

    Transport methods within an Exadata Environment depending upon how the environment is architected can utilize one of 3 transport methods -->

    1). Standard Client Network which would be used by SCAN/VIP Network, typically 1gE
    2). IB/SDP Protocol Deployment,  this is only applicable if the Primary/Standby are connected via Spine  Switch's.   Located in the same data center physically connected to each other.

    3). High Speed Transport using 10gE Interface SFP Module over LAN or WAN


    Article assumes that Primary and Standby sites are both running on Exadata Hardware.
    This does not need to be exact Exadata Image release however following best practices ideally we would be running the exact same image.

    With respect too Oracle Enterprise Linux release this can differ again though not ideal.

    Pre-requisite is that Grid Insfrastructure is the same version and the Oracle RDBMS must be the same version and release at both primary/standby sites.

    Software is already installed at both Primary/Standby environments with the same Exadata Bundle Patch's applied to both GRID & RDBMS Oracle along with any required overlay patch's.

    Verify this at both ends with 'opatch lsinventory'

    Solution

     

    SETUP CONFIGURATION

    Primary is 2 Nodes --->
    Host Names # exadbmel01.au.oracle.com  exadbmel02.au.oracle.com
    SID Names  # AM1, AM2    
    DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1

    $ srvctl status database -d AM
    Instance AM1 is running on node exadbmel01
    Instance AM2 is running on node exadbmel02


    Standby is 2 Nodes --->
    Host Names # exadbmel03.au.oracle.com  exadbmel04.au.oracle.com
    SID Names  # AMADG1, AMADG2
    DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1

    Here no DB is configured yet, all we have is the s/ware installed.

     

    On the primary Database

    1). Enable force logging..

    SQL> ALTER DATABASE FORCE LOGGING;

    Database altered.


    2). Create standby redo logs

    You will need to create sufficient Standby Redo Logs to cater for redo apply.
    This needs to equal the same group and member amount of REDO logs you have configured on your primary.
     

    SQL> alter database add standby logfile thread 1
      2  group 9 ('+DATA_EXA','+DBFS_DG')  size 50M,
      3  group 10 ('+DATA_EXA','+DBFS_DG')  size 50M;

    Database altered.

    SQL> alter database add standby logfile thread 2
      2  group 11 ('+DATA_EXA','+DBFS_DG')  size 50M,
      3  group 12 ('+DATA_EXA','+DBFS_DG')  size 50M;

    Database altered.

     

    On the primary & standby Database

    3). Setup Entries in tnsnames.ora

    Pre-requisite here is that a listener exists on all nodes in Primary/Standby hosts
    Before defining the below sql*net entries use netca to create the require listeners.

    $ cat tnsnames.ora

    AM =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = AM)
        )
      )

    AMADG =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = AMADG)
             (UR=A)
        )
      )

     

    On the Primary Database Node#1

    4). Copy password file from Primary -> Standby

    $ cd $ORACLE_HOME/dbs
    $ cp orapwAM1 oracle@exadbmel03:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG1
    $ cp orapwAM1 oracle@exadbmel04:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG2

     

    On the standby Database

     5).   Setup a dummy pfile in $ORACLE_HOME/dbs on node #1 and node #2

    Name the file as initAMADG.ora
    You will later create a initAMADG1.ora & initAMADG2.ora accordingly with an spfile pointer.

    initAMADG.ora
    ~~~~~~~~~~~~~~
    *.db_cache_size=2684354560
    *.java_pool_size=16777216
    *.large_pool_size=16777216
    AMADG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    AMADG2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    *.pga_aggregate_target=1157627904
    *.sga_target=3472883712
    *.shared_io_pool_size=0
    *.shared_pool_size=721420288
    *.streams_pool_size=0
    *.archive_lag_target=0
    *.audit_file_dest='/u01/app/oracle/admin/AMADG/adump'
    *.audit_trail='db'
    *.cluster_database=true
    *.compatible='11.2.0.2.0'
    *.control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl'
    *.db_block_size=8192
    *.db_cache_size=2684354560
    *.db_create_file_dest='+DATA_EXA'
    *.db_domain=''
    *.db_file_name_convert='AM','AMADG'
    *.db_name='AM'
    *.db_recovery_file_dest='+DBFS_DG'
    *.db_recovery_file_dest_size=10737418240
    *.db_unique_name='AMADG'
    *.dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat'
    *.dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat'
    *.dg_broker_start=TRUE
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=AMADGXDB)'
    *.fal_server='am'
    AMADG1.instance_number=1
    AMADG2.instance_number=2
    *.java_pool_size=16777216
    *.large_pool_size=16777216
    *.log_archive_config='dg_config=(AMADG,am)'
    *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
    *.log_archive_format='%t_%s_%r.dbf'
    AMADG2.log_archive_format='%t_%s_%r.dbf'
    AMADG1.log_archive_format='%t_%s_%r.dbf'
    *.log_archive_max_processes=4
    *.log_archive_min_succeed_dest=1
    AMADG2.log_archive_trace=0
    AMADG1.log_archive_trace=0
    *.log_file_name_convert='AM','AMADG'
    *.open_cursors=300
    *.processes=150
    *.remote_listener='scan-exa:1521'
    *.remote_login_passwordfile='exclusive'
    *.shared_pool_size=721420288
    *.standby_file_management='AUTO'
    *.streams_pool_size=0
    AMADG3.thread=3
    AMADG1.thread=1
    AMADG2.thread=2
    AMADG1.undo_tablespace='UNDOTBS1'
    AMADG2.undo_tablespace='UNDOTBS2'

     

    On the standby DB

    6).  Using the dummy parameter file startup the instance's

    Standby Node1, placing the file as follows in $OH/dbs location

    SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initAMADG.ora

    ORACLE instance started.

    Total System Global Area 3457368064 bytes
    Fixed Size                  2233272 bytes
    Variable Size             754977864 bytes
    Database Buffers         2684354560 bytes
    Redo Buffers               15802368 bytes
    SQL>


    Repeat on node 2 and start the instance in nomount as well.

     

    On the primary & standby Database 

    7).  Test Connections

    On both Primary and Standby Test Sql*Net Connections

    $ sqlplus sys/welcome1@AMADG as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 29 15:10:47 2013

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

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options

    SQL>


    Repeat this connection across all nodes for the Primary AM connect string and the standby AMADG connect string.

     

    On the Primary Database

    8).  Perform an RMAN Duplicate

    From Primary -->  Connect to RMAN
    ------------------------------------

    [oracle@exadbmel01 dbhome_2]$ rman target sys@AM auxiliary sys@AMADG

    Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 1 09:46:27 2013

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

    target database Password:
    connected to target database: AM (DBID=939288056)
    auxiliary database Password:
    connected to auxiliary database: AM (not mounted)

    RMAN>


    RMAN> duplicate target database for standby from active database
    spfile
    parameter_value_convert 'AM','AMADG'
    set db_unique_name='AMADG'
    set db_file_name_convert='AM','AMADG'
    set log_file_name_convert='AM','AMADG'
    set control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl'
    set standby_file_management='AUTO'
    set db_recovery_file_dest='+DBFS_DG'
    set audit_file_dest='/u01/app/oracle/admin/AMADG/adump'

     

    On the Standby Database

    9).  Validate the Database Role on either AMADG1 or AMADG2

    SQL> select name, database_role from v$database;

    NAME      DATABASE_ROLE
    --------- ----------------
    AM        PHYSICAL STANDBY

     

    On the Standby Database

    10).  Create spfile from the test pfile created

    SQL>  create spfile='+DATA_EXA' from pfile='/u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/initAMADG1.ora';


    Then connected to 'ASMCMD'
    copied spfile location +DATA_EXA/AMADG/PARAMETERFILE  too  +DATA_EXA/AMADG/spfileamadg.ora

    Then made sure init.ora was updated to have correct SPFILE pointer entry..


    $ cat $ORACLE_HOME/dbs/initAMADG1.ora
    +DATA_EXA/amadg/spfileamadg.ora

    $ cat $ORACLE_HOME/dbs/initAMADG2.ora
    +DATA_EXA/amadg/spfileamadg.ora

     

    On the Standby Database

    11).  Verify both Standby Instances can start in mount mode.

    Its at this point we verify group services registration with the underlying CSSD daemon.
    Please note you'll need to do this with sqlplus as we are yet to register the standby resource with
    Oracle Clusterware.

    Once you have started them both make sure you see both instances

    SQL> select instance_number, instance_name from gv$instance;

    INSTANCE_NUMBER INSTANCE_NAME
    --------------- ----------------
                  1 AMADG1
                  2 AMADG2

     

    On the Primary & Standby Database

    12).  Define Log Transport Method to be used.

    In Step 3 we used the example of creating the DataGuard configuration on the SCAN Client Network.
    You now have the option to change this if architecturally intended to use the 10gE or IB network.

    Verify the current deployment is on the scan, which in my example would be bondeth0.

    E.g.

    HOST String used in tnsnames.ora is 'SCAN-EXA'

    $ srvctl config scan
    SCAN name: scan-exa, Network: 1/10.187.80.0/255.255.254.0/bondeth0
    SCAN VIP name: scan1, IP: /scan-exa/10.187.80.174
    SCAN VIP name: scan2, IP: /scan-exa/10.187.80.34
    SCAN VIP name: scan3, IP: /scan-exa/10.187.80.35

    <as root>

    % cat /proc/net/bonding/bondeth0 |grep eth
    Currently Active Slave: eth1
    Slave Interface: eth1
    Slave Interface: eth2

     

    Lets Proceed with transport method options -->

      a). Using the Scan Client Network  (default as above)
      b). Using 10gE Network Interface SFP Module over WAN or LAN
      c). Using TCPoIB on the Infiniband Network LAN


    If your choosing Option "A"  then please proceed to Step 13 as their is no need to change the transport method.
    If your choosing Option "B"  then proceed to step 12a
    If your choosing Option "C" then proceed to step 12b


    12.a)  Defining Log Transport on 10gE

    The 10GigE interfaces on an X2-2 or X3-2 are identified as eth4 or eth5, while on an X2-8 they are identified as eth8 through eth15. Use the ethtool command to probe each 10GigE interface.

    E.g.
    # ethtool eth8 |grep -i speed
            Speed: 10000Mb/s


    Once you've identified the correct interface and confirmed connectivity you can now setup additional
    network for TCP Support over 10gE.

    Please refer to article -->

      How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure (Doc ID 1063571.1)

    Once you have defined the 10gE net2 resource, setup connect strings as we did in step 3 but you'll need
    to change the HOST address to be the 10gE hostnames defined.

    Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba "


    12.b). Defining Log Transport on IB using TCPoIB Communication

    Please refer to article -->
      Setup Listener on Infiniband Network using both SDP and TCP Protocol (Doc ID 1580584.1)

    You will need to implement the requires steps outlined in 1580584.1 on both Primary and Standby Environments once this complete you can then add desired sql*Net connect strings to be used.

    E.g.

    AMIB =
      (DESCRIPTION =
            (LOAD_BALANCE=on)
            (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel01-ib)(PORT = 1522))
            (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel02-ib)(PORT = 1522))
            (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = AM)
            ))

    AMADGIB =
      (DESCRIPTION =
            (LOAD_BALANCE=on)
            (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel03-ib)(PORT = 1522))
            (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel04-ib)(PORT = 1522))
            (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = AMADG)
            ))



    Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba "



    Finally once you have defined either transport methods ensure you change 'log_archive_dest_2'
    parameter too point to the correct service name for either of the options in 12a or 12b.

    E.g.

    SQL>  alter system set LOG_ARCHIVE_DEST_2='SERVICE=AMADGIB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=AMADG' scope=both;

     

    On the Primary & Standby Database

    13>.  Enable Data Guard Broker


    On Primary, and Standby

    SQL> alter system set dg_broker_start=true scope=both sid='*';

    System altered.

     

    ON PRIMARY,   Node #1

    SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AM.dat' scope=spfile;

    System altered.

    SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AM.dat' scope=spfile;

    System altered.


    ON STANDBY,  Node #1


    SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat' scope=spfile;

    System altered.

    SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat' scope=spfile;

    System altered.


    Then Copy the Broker Files connecting as GRID s/ware owner using ASMCMD in GRID Home When first enabling the broker it creates config files locally (file system) we can place these on shared storage in this case +ASM diskgroup.


    Primary DB on Node 1

    $ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat +DATA_EXA/AM/BROKER/dr1AM.dat
    copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat -> +DATA_EXA/AM/BROKER/dr1AM.dat

    $ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat +DATA_EXA/AM/BROKER/dr2AM.dat
    copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat -> +DATA_EXA/AM/BROKER/dr2AM.dat


    Standby DB on Node 1

    ASMCMD> cd +DATA_EXA/AMADG/BROKER
    ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat .
    copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr1AMADG.dat

    ASMCMD> cd +DATA_EXA/AMADG/BROKER
    ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat .
    copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr2AMADG.dat



    Restart the broker for changes to take effect.

    On Primary, and Standby

    SQL> alter system set dg_broker_start=false scope=both sid='*';

    System altered.

    SQL> alter system set dg_broker_start=true scope=both sid='*';

    System altered.



    Primary DB on Node 1

    DGMGRL> create configuration AM_Active_DG as primary database is AM connect identifier is AM;
    Configuration "am_active_dg" created with primary database "am"

    DGMGRL> add database AMADG as connect identifier is AMADG maintained as physical;
    Database "amadg" added

    DGMGRL> enable configuration;
    Enabled.

    DGMGRL> show configuration;

    Configuration - am_active_dg

      Protection Mode: MaxPerformance
      Databases:
        am    - Primary database
        amadg - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS

     

    On the Standby Database

    14).   Register Standby Database Resources with Clusterware


    As the oracle software owner

    $ srvctl add database -d AMADG -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATA_EXA/amadg/spfileamadg.ora -r physical_standby
    $ srvctl add instance -d AMADG -i AMADG1 -n exadbmel03
    $ srvctl add instance -d AMADG -i AMADG2 -n exadbmel04

     

    Steps are complete!    Restart of the Standby Resource using srvctl will automatically start MRP with one of the active instance threads.

     

    15.  Additional Action to define static listener registration so that the Data Guard Broker can successfully restart the instances.

    Please refer to article --> Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

    展开全文
  • oracle - adg搭建

    2019-05-02 10:25:26
    oracle 11g active dataguard 搭建 1.修改参数 1>新添加dg备库 检查主库配置 SQL> select DBID,NAME,DATABASE_ROLE,PROTECTION_MODE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME ...

    oracle 11g active dataguard 搭建


    1.修改参数
    1>新添加dg备库
    检查主库配置

    SQL> select DBID,NAME,DATABASE_ROLE,PROTECTION_MODE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,DB_UNIQUE_NAME  from v$database;
    
          DBID NAME      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS    DATAGUAR GUARD_S DB_UNIQUE_NAME
    ---------- --------- ---------------- -------------------- -------------------- -------- ------- ------------------------------
     387098034 FSRENCAP  PRIMARY          MAXIMUM PERFORMANCE  NOT ALLOWED          DISABLED NONE    fsrenCAP


     
    检查源端参数 
    show parameter archive
    show parameter dg
    show parameter db_name
    show parameter db_unique_name

    2>修改参数
    archive log list;

    ALTER DATABASE FORCE LOGGING;
    --主备修改以下参数

    alter system set DB_NAME=FSRENCAP   scope=spfile; --数据库名要统一
    alter system set DB_UNIQUE_NAME=FSRENCAP   scope=spfile;  --数据库唯一名要不一致
    alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=FSRENCAP';--设置主库归档目录
    alter system set LOG_ARCHIVE_DEST_2='service="CAPDG"','LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FSRENCAP';  --service为备库(相对)的tnsname,指向备库
    alter system set FAL_SERVER=CAPDG;  ---备库tnsname
    alter system set FAL_CLIENT=CAP ; --主库tnsname(当前所在的库)
    alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile; 
    alter system set service_names=fsrenCAP;
    alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(fsren,fsrendg)' ; --主备库实例名相同不需要更改
    alter system set log_archive_dest_state_1='enable';
    alter system set log_archive_dest_state_2='enable';
    alter system set dg_broker_start=true;

     

    dg备库

    alter system set DB_NAME=FSRENCAP   scope=spfile; --数据库名要统一
    alter system set DB_UNIQUE_NAME=FSRENCAPDG   scope=spfile; --数据库唯一名要不一致
    alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=FSRENCAPDG';--设置备库归档目录
    alter system set LOG_ARCHIVE_DEST_2='service="CAP"','LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FSRENCAPDG';  --service为备库(相对)的tnsname,指向备库
    alter system set FAL_SERVER=CAP;  ---备库tnsname
    alter system set FAL_CLIENT=CAPDG ; --主库tnsname(当前所在的库)
    alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile; 
    alter system set service_names=FSRENCAPDG;
    alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(fsren,fsrendg)' ; --主备库实例名相同不需要更改
    alter system set log_archive_dest_state_1='enable';
    alter system set log_archive_dest_state_2='enable';
    alter system set dg_broker_start=true;

    **主备库文件位置不一致需更改以下参数:

    *.db_file_name_convert='/u01/oracle/oradata/KCARDPRD/datafile','/oradata01/kcardprd/kcardprd_data','/raid5/KCARDPRD/datafile','/oradata01/kcardprd/kcardprd_data','/raid10/KCARDPRD/datafile','/oradata01/kcardprd/kcardprd_data','/u01/oracle/oradata/SKCARDPRD/datafile','/oradata01/kcardprd/kcardprd_data'
    #db_file_name_convert中临时文件的路径也需要转换
    *.log_file_name_convert='/u01/oracle/oradata/KCARDPRD/onlinelog','/oradata01/kcardprd/kcardprd_data'


    ***
    --传输spfile 密码文件。创建相关目录。
    [oracle@renCAP ~]$ scp fsrenpfile.ora  oracle@10.140.100.50:/home/oracle/

    [oracle@renCAP dbs]$ scp orapwfsren  oracle@10.140.100.50:$ORACLE_HOME/dbs
    cp $ORACLE_HOME/dbs/orapwfsren   $ORACLE_HOME/dbs/orapwfsrendg


    [oracle@renCAPdg ~]$ mkdir -p /opt/app/oracle/admin/fsrenCAP/adump
    [oracle@renCAPdg ~]$ mkdir -p /opt/app/oracle/admin/fsrenCAP/bdump
    [oracle@renCAPdg ~]$ mkdir -p /opt/app/oracle/admin/fsrenCAP/cdump
    [oracle@renCAPdg ~]$ mkdir -p /opt/app/oracle/admin/fsrenCAP/ddump


    2.创建tns链路

    cap =
      (DESCRIPTION =
       (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =10.140.100.13)(PORT = 1521))
       )
      (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = fsrenCAP)
       )
    )

    capdg =
      (DESCRIPTION =
       (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =10.140.100.50)(PORT = 1521))
       )
      (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = fsrenCAP)
       )
    )

    --备库listener.ora

    SID_LIST_LISTENER=
      (SID_LIST=
         (SID_DESC=
           (SID_NAME=PLSExtProc)
           (ORACLE_HOME=/opt/app/oracle/product/11.2/db_2)
           (PROGRAM=extproc)
                    )
         (SID_DESC=
           (GLOBAL_DBNAME=fsrenCAP)
           (ORACLE_HOME=/opt/app/oracle/product/11.2/db_2)
           (SID_NAME=fsrendg)
       )
    )

    LISTENER=
        (DESCRIPTION_LIST=
           (DESCRIPTION=
              (ADDRESS=(PROTOCOL=TCP)(HOST=10.140.100.50)(PORT=1521))
              (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))
       )
    )

    tnsping  cap

    tnsping capdg

    3.启动实例

    [oracle@renCAPdg dbs]$ export ORACLE_SID=fsrendg
    [oracle@renCAPdg dbs]$ sqlplus / as sysdba
    SQL> startup pfile='/home/oracle/fsrenpfile.ora' nomount;

    主库执行复制数据


    rman target sys/oracle@cap auxiliary sys/oracle@capdg
    duplicate target database for standby from active database  nofilenamecheck dorecover;

     --若之前有不完全恢复,则此次回复是从上次恢复点进行恢复,需要指定从0级开始恢复

    list incarnation;
    incarnation 1;

    然后再执行duplicate


    ----基于scn同步数据
    参考文档:Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
    数据量大开启块跟踪
    alter database enable block change tracking using file '/raid5/KCARDPRD/rman_change_track.f';
    查看是否生效
    col status   format a8
    col filename format a60
    select status, filename from v$block_change_tracking;

    --查看scn号

    可以通过v$datafile_header查数据文件头部最小scn确认需要怎么恢复
    目标端找文件头最小scn号
    set pagesize 2000
    set linesize 200
    col min_changescn for 999999999999999
    select min(checkpoint_change#) min_changescn from v$datafile_header
    where file# not in (select file# from v$datafile where enabled = 'READ ONLY');


       MIN_CHANGESCN
    ----------------
            12702406
            
    rman target / log=/home/oracle/backup_20190501.log<<EOF
    run
    {configure controlfile autobackup on;
    sql 'alter system switch logfile'; 
    BACKUP INCREMENTAL FROM SCN  12702406  DATABASE FORMAT '/data/fsren_%d_%u.bak' tag 'FORSTANDBY';
    }

    备份完后复制到目标端应用
    注册备份信息,并且恢复
    rman target /
    CATALOG START WITH '/data/';
    RECOVER DATABASE NOREDO;

    从源端获取最新的控制文件
    BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/data/ForStandbyCTRL_20190501.bck';

    目标端恢复控制文件
    SHUTDOWN IMMEDIATE ;
    STARTUP NOMOUNT;
    RESTORE STANDBY CONTROLFILE FROM '/data/ForStandbyCTRL_20190501.bck';

    alter database mount;

    此时源端还留有目标端所需归档文件,可以开启dg同步追归档


    4.创建 standby redo(主备库)

     

    ALTER DATABASE ADD STANDBY LOGFILE ('/data/fsrenCAP/standredo01.log') SIZE 500M;
    ALTER DATABASE ADD STANDBY LOGFILE ('/data/fsrenCAP/standredo03.log') SIZE 500M;
    ALTER DATABASE ADD STANDBY LOGFILE ('/data/fsrenCAP/standredo02.log') SIZE 500M;

    5.激活备库--生成redolog

     alter database recover managed standby database disconnect from session;
     
     alter database open ;
    6.主备切换
    (主)
    select open_mode,database_role,switchover_status from v$database;
    OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
    -------------------- ---------------- --------------------
    READ WRITE           PRIMARY          RESOLVABLE GAP

    alter database commit to switchover to physical standby with session shutdown; 
     
    shutdown  immediate
    startup mount
    --应用日志
    alter database recover managed standby database disconnect from session;

    select open_mode,database_role,switchover_status from v$database;
     
    (备)
    recover managed standby database disconnect from session;
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;  
     
    alter database open;


    -------回切------
    (主)
    ALTER DATABASE COMMIT TO SWITCHOVER TOPHYSICAL STANDBY WITH SESSION SHUTDOWN;
    shutdown immediate;
    startup mount;
    --应用日志
    alter database recover managed standby database disconnect from session;

    (备)
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

    startup open;


    select open_mode,database_role,switchover_status from v$database;

    展开全文
  • ORACLE 12C RAC+ADG搭建

    2020-03-29 13:54:22
    记录自己搭建过程 1.系统环境 centos:7.7 oracle:12.2.0.1 2.IP规划 PUBLIC-IP PRIVATE-IP VIP SCAN-IP A-RAC1 192.168.0.1 10.10.0.1 192.168.0.10 192.168.0.50 A-RAC2 192....
  • rac+单实例adg搭建步骤

    2018-10-20 20:22:51
    在Linux环境下,如何实现RAC+单实例adg搭建步骤详情

空空如也

1 2 3 4 5 ... 15
收藏数 291
精华内容 116
关键字:

adg搭建