ITPub博客

首页 > 数据库 > Oracle > DG搭建

DG搭建

原创 Oracle 作者:jelephant 时间:2015-10-27 15:37:32 0 删除 编辑
1.主库中的操作:
1.1 查看数据库是否在归档与是否强制LOGGING模式。
     SQL> select log_mode,force_logging from v$database;
     
     LOG_MODE FOR
     ------------ ---
     ARCHIVELOG NO
     
     SQL> ALTER DATABASE FORCE LOGGING;
     
     Database altered.
1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。
     
    SQL> select group#,thread#,bytes/1024/1024 from v$log;
   
        GROUP# THREAD# BYTES/1024/1024
    ---------- ---------- ---------------
             1 1 50
             2 1 50
             3 2 50
             4 2 50
   
    SQL> select member from v$logfile;
   
    MEMBER
    --------------------------------------------------------------------------------
    +DATA/racdb/redo01.log
    +DATA/racdb/redo02.log
    +DATA/racdb/redo03.log
    +DATA/racdb/redo04.log
1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功
    SQL> alter database add standby logfile thread 1 group 5 ('+DATA/racdb/redo_st_05.log') size 50m;
    Database altered.
   
    SQL> alter database add standby logfile thread 1 group 6 ('+DATA/racdb/redo_st_06.log') size 50m;
   
    Database altered.
   
    SQL> alter database add standby logfile thread 1 group 7 ('+DATA/racdb/redo_st_07.log') size 50m;
   
    Database altered.
   
    SQL> alter database add standby logfile thread 2 group 8 ('+DATA/racdb/redo_st_08.log') size 50m;
   
    Database altered.
   
    SQL> alter database add standby logfile thread 2 group 9 ('+DATA/racdb/redo_st_09.log') size 50m;
   
    Database altered.
   
    SQL> alter database add standby logfile thread 2 group 10 ('+DATA/racdb/redo_st_10.log') size 50m;
   
    Database altered.
   
    SQL> select group#,status,type,member from v$logfile;
   
        GROUP# STATUS TYPE MEMBER
    ---------- ------- ------- ------------------------------
             1 ONLINE +DATA/racdb/redo01.log
             2 ONLINE +DATA/racdb/redo02.log
             3 ONLINE +DATA/racdb/redo03.log
             4 ONLINE +DATA/racdb/redo04.log
             5 STANDBY +DATA/racdb/redo_st_05.log
             6 STANDBY +DATA/racdb/redo_st_06.log
             7 STANDBY +DATA/racdb/redo_st_07.log
             8 STANDBY +DATA/racdb/redo_st_08.log
             9 STANDBY +DATA/racdb/redo_st_09.log
            10 STANDBY +DATA/racdb/redo_st_10.log
    10 rows selected.
   
1.3 修改日志组路径
    注:由于其中有日志为current,因此需要alter system switch logfile进行切换后,在修改路径
    SQL> alter database rename file '+DATA/racdb/redo01.log' to '+DATA/RACDB/ONLINELOG/group_1.258.832348287';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo04.log' to '+DATA/RACDB/ONLINELOG/group_4.267.832358755';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo03.log' to '+DATA/RACDB/ONLINELOG/group_3.266.832358749';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo02.log' to '+DATA/RACDB/ONLINELOG/group_2.259.832348289';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_09.log' to '+DATA/RACDB/ONLINELOG/group_9.298.832715363';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_08.log' to '+DATA/RACDB/ONLINELOG/group_8.297.832715327';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_07.log' to '+DATA/RACDB/ONLINELOG/group_7.296.832715287';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_06.log' to '+DATA/RACDB/ONLINELOG/group_6.295.832715229';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_05.log' to '+DATA/RACDB/ONLINELOG/group_5.269.832715165';
   
    Database altered.
   
    SQL> alter database rename file '+DATA/racdb/redo_st_10.log' to '+DATA/RACDB/ONLINELOG/group_10.342.832779459';
   
    Database altered.
   
    SQL> select group#,status,type,member from v$logfile;
   
        GROUP# STATUS TYPE MEMBER
    ---------- ------- ------- --------------------------------------------------
             1 ONLINE +DATA/racdb/onlinelog/group_1.258.832348287
             2 ONLINE +DATA/racdb/onlinelog/group_2.259.832348289
             3 ONLINE +DATA/racdb/onlinelog/group_3.266.832358749
             4 ONLINE +DATA/racdb/onlinelog/group_4.267.832358755
             5 STANDBY +DATA/racdb/onlinelog/group_5.269.832715165
             6 STANDBY +DATA/racdb/onlinelog/group_6.295.832715229
             7 STANDBY +DATA/racdb/onlinelog/group_7.296.832715287
             8 STANDBY +DATA/racdb/onlinelog/group_8.297.832715327
             9 STANDBY +DATA/racdb/onlinelog/group_9.298.832715363
            10 STANDBY +DATA/racdb/onlinelog/group_10.342.832779459
   
    10 rows selected.
1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是配置FAL_SERVER,FAL_CLIENT参数。
    SQL> create pfile='/home/oracle/initSBDB.ora' from spfile;
   
    File created.
   
    修改文件内容:
    RACDB2.__db_cache_size=117440512
    RACDB1.__db_cache_size=125829120
    RACDB1.__java_pool_size=4194304
    RACDB2.__java_pool_size=4194304
    RACDB1.__large_pool_size=4194304
    RACDB2.__large_pool_size=4194304
    RACDB1.__pga_aggregate_target=104857600
    RACDB2.__pga_aggregate_target=104857600
    RACDB1.__sga_target=318767104
    RACDB2.__sga_target=318767104
    RACDB1.__shared_io_pool_size=0
    RACDB2.__shared_io_pool_size=0
    RACDB2.__shared_pool_size=184549376
    RACDB1.__shared_pool_size=176160768
    RACDB1.__streams_pool_size=0
    RACDB2.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/RACDB/adump'
    *.audit_trail='db'
    *.cluster_database=true
    *.compatible='11.2.0.0.0'
    *.control_files='+DATA/RACDB/control01.ctl','+DATA/RACDB/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='RACDB'
    *.db_recovery_file_dest='+DATA'
    *.db_recovery_file_dest_size=6005194752
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'
    RACDB2.instance_number=2
    RACDB1.instance_number=1
    *.open_cursors=300
    *.pga_aggregate_target=104857600
    *.processes=150
    *.remote_listener='scan-ip:1521'
    *.remote_login_passwordfile='exclusive'
    *.service_names='RACDB,cuixu'
    *.sga_target=316669952
    RACDB2.thread=2
    RACDB1.thread=1
    RACDB2.undo_tablespace='UNDOTBS2'
    RACDB1.undo_tablespace='UNDOTBS1'
   
    添加如下内容:
    *.DB_UNIQUE_NAME=RACDB
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,SBDB)'
    *.LOG_ARCHIVE_DEST_1=
    'LOCATION=USE_DB_RECOVERY_FILE_DEST
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=RACDB'
    *.LOG_ARCHIVE_DEST_2=
    'SERVICE=SBDB ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=SBDB'
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.FAL_SERVER=SBDB
    RACDB1.FAL_CLIENT=RACDB1
    RACDB2.FAL_CLIENT=RACDB2
    *.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SBDB/','+DATA/racdb/datafile/','/u01/app/oracle/oradata/SBDB/','+DATA/racdb/tempfile/','/u01/app/oracle/oradata/SBDB/','+DATA/RACDB/'
    *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SBDB/','+DATA/RACDB/ONLINELOG/'
     
1.5 把initSBDB.ora和orapwRACDB1文件同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。
    [oracle@rac1 ~]$ scp /home/oracle/initSBDB.ora oracle@192.168.80.10:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
    The authenticity of host '192.168.80.10 (192.168.80.10)' can't be established.
    RSA key fingerprint is db:39:d8:4c:81:41:aa:a3:e5:be:bc:60:f5:b6:43:56.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '192.168.80.10' (RSA) to the list of known hosts.
    oracle@192.168.80.10's password:
    initSBDB.ora 100% 1940 1.9KB/s 00:00
    [oracle@rac1 ~]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwRACDB1 oracle@192.168.80.10:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSBDB
    oracle@192.168.80.10's password:
    orapwRACDB2 100% 1536 1.5KB/s 00:01
   
 
   
1.6 修改TNSNAMES文件
    [oracle@rac2 ~]$ cd $ORACLE_HOME/network/admin
    [oracle@rac2 admin]$ vi tnsnames.ora
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
   
    RACDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = RACDB)
        )
      )
   
    SBDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.10)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = SBDB)
        )
      )
     
2.库备的操作:
2.1 修改监听,添加静态注册
    [oracle@ENMOEDU admin]$ vi listener.ora
    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
   
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ENMOEDU)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
   
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
         (GLOBAL_DBNAME = SBDB)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
         (SID_NAME = SBDB)
        )
       )
    ADR_BASE_LISTENER = /u01/app/oracle
2.2 添加tnsname的内容
    [oracle@rac2 ~]$ cd $ORACLE_HOME/network/admin
    [oracle@rac2 admin]$ vi tnsnames.ora
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
   
    RACDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.111)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = RACDB)
        )
      )
    SBDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.10)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = SBDB)
        )
      )
2.3 创建相关文件与修改参数文件
 1. 创建相关目录
     [oracle@ENMOEDU ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/SBDB
     [oracle@ENMOEDU ~]$ mkdir -p /u01/app/oracle/oradata/SBDB
     [oracle@ENMOEDU ~]$ mkdir -p /u01/app/oracle/admin/SBDB/adump
 2. 修改参数文件
    SBDB.__db_cache_size=117440512
    SBDB.__db_cache_size=125829120
    SBDB.__java_pool_size=4194304
    SBDB.__java_pool_size=4194304
    SBDB.__large_pool_size=4194304
    SBDB.__large_pool_size=4194304
    SBDB.__pga_aggregate_target=104857600
    SBDB.__pga_aggregate_target=104857600
    SBDB.__sga_target=318767104
    SBDB.__sga_target=318767104
    SBDB.__shared_io_pool_size=0
    SBDB.__shared_io_pool_size=0
    SBDB.__shared_pool_size=184549376
    SBDB.__shared_pool_size=176160768
    SBDB.__streams_pool_size=0
    SBDB.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/SBDB/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/SBDB/control01.ctl','/u01/app/oracle/oradata/SBDB/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='RACDB'
    *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
    *.db_recovery_file_dest_size=6005194752
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=SDBDBXDB)'
    *.open_cursors=300
    *.pga_aggregate_target=104857600
    *.processes=150
    *.remote_login_passwordfile='exclusive'
    *.service_names='SBDB'
    *.sga_target=316669952
    *.undo_tablespace='UNDOTBS1'
   
    添加如下内容:
    *.DB_UNIQUE_NAME=SBDB
    *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACDB,SBDB)'
    *.LOG_ARCHIVE_DEST_1=
    'LOCATION=USE_DB_RECOVERY_FILE_DEST
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=SBDB'
    *.LOG_ARCHIVE_DEST_2=
    'SERVICE=RACDB ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=RACDB'
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.FAL_SERVER='RACDB1','RACDB2'
    *.FAL_CLIENT=SBDB
    *.DB_FILE_NAME_CONVERT='+DATA/racdb/datafile/','/u01/app/oracle/oradata/SBDB/','+DATA/racdb/tempfile/','/u01/app/oracle/oradata/SBDB/','+DATA/RACDB/','/u01/app/oracle/oradata/SBDB/'
    *.LOG_FILE_NAME_CONVERT='+DATA/RACDB/ONLINELOG','/u01/app/oracle/oradata/SBDB/'
     
2.4 创建spfile与启动数据库到mount
 1. 创建spfile,并启动数据库到NOMOUNT
     [oracle@ENMOEDU ~]$ export ORACLE_SID=SBDB
     [oracle@ENMOEDU ~]$ sqlplus / as sysdba
     
     SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 28 23:53:40 2013
     
     Copyright (c) 1982, 2011, Oracle. All rights reserved.
     
     Connected to an idle instance.
     
     SYS@SBDB> create spfile from pfile;
     
     File created.
     
     SYS@SBDB> startup nomount;
     ORACLE instance started.
     
     Total System Global Area 318046208 bytes
     Fixed Size 1344680 bytes
     Variable Size 184552280 bytes
     Database Buffers 125829120 bytes
     Redo Buffers 6320128 bytes
     SYS@SBDB>
  2. 检查实例向监听注册的情况
    [oracle@ENMOEDU admin]$ lsnrctl status
   
    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-NOV-2013 00:32:50
   
    Copyright (c) 1991, 2011, Oracle. All rights reserved.
   
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ENMOEDU)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date 29-NOV-2013 00:26:53
    Uptime 0 days 0 hr. 6 min. 6 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/ENMOEDU/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ENMOEDU)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "SBDB" has 2 instance(s).
      Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
      Instance "SBDB", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully
3.回到主库的操作:
   在前面我们修改了两个参数(db/log_file_name_convertt)到spfile文件中,还没有重启数据库,这里我们重启一下主数据库
3.1 重启rac
    节点一
    [oracle@rac1 ~]$ sqlplus / as sysdba
   
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 00:34:47 2013
   
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
   
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
   
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    节点二
    [oracle@rac2 dbs]$ sqlplus / as sysdba
   
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 00:33:23 2013
   
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
   
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
   
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
   
    生成参数文件
   
    SQL> create spfile='+DATA/RACDB/spfileRACDB.ora' from pfile='/home/oracle/initSBDB.ora';
   
    File created.
   
    注:可能需要在ASM中创建别名
    ASMCMD> mkalias +DATA/RACDB/PARAMETERFILE/spfile.305.832727335 +DATA/RACDB/spfileRACDB.ora
   
    启动数据库
    SQL> startup
    ORACLE instance started.
   
    Total System Global Area 318046208 bytes
    Fixed Size 1344680 bytes
    Variable Size 192940888 bytes
    Database Buffers 117440512 bytes
    Redo Buffers 6320128 bytes
    Database mounted.
    Database opened.
    SQL>
     
3.2 duplicate开始
   [oracle@rac1 ~]$ rman target sys/oracle@RACDB auxiliary sys/oracle@SBDB
   
   Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 29 15:45:08 2013
   
   Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
   connected to target database: RACDB (DBID=829965031)
   connected to auxiliary database: RACDB (not mounted)
   
   RMAN> run {
   2> allocate channel prmy1 type disk;
   3> allocate channel prmy2 type disk;
   4> allocate auxiliary channel stby type disk;
   5> duplicate target database for standby from active database nofilenamecheck ;
   6> }
   
   using target database control file instead of recovery catalog
   allocated channel: prmy1
   channel prmy1: SID=67 instance=RACDB1 device type=DISK
   
   allocated channel: prmy2
   channel prmy2: SID=63 instance=RACDB1 device type=DISK
   
   allocated channel: stby
   channel stby: SID=20 device type=DISK
   
   Starting Duplicate Db at 29-NOV-13
   
   contents of Memory Script:
   {
      backup as copy reuse
      targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwRACDB1' auxiliary format
    '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSBDB' ;
   }
   executing Memory Script
   
   Starting backup at 29-NOV-13
   Finished backup at 29-NOV-13
   
   contents of Memory Script:
   {
      backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB/control01.ctl';
      restore clone controlfile to '/u01/app/oracle/oradata/SBDB/control02.ctl' from
    '/u01/app/oracle/oradata/SBDB/control01.ctl';
   }
   executing Memory Script
   
   Starting backup at 29-NOV-13
   channel prmy1: starting datafile copy
   copying standby control file
   output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_RACDB1.f tag=TAG20131129T154737 RECID=5 STAMP=832780089
   channel prmy1: datafile copy complete, elapsed time: 00:00:36
   Finished backup at 29-NOV-13
   
   Starting restore at 29-NOV-13
   
   channel stby: copied control file copy
   Finished restore at 29-NOV-13
   
   contents of Memory Script:
   {
      sql clone 'alter database mount standby database';
   }
   executing Memory Script
   
   sql statement: alter database mount standby database
   
   contents of Memory Script:
   {
      set newname for tempfile 1 to
    "/u01/app/oracle/oradata/SBDB/temp.263.832348389";
      switch clone tempfile all;
      set newname for datafile 1 to
    "/u01/app/oracle/oradata/SBDB/system.260.832348297";
      set newname for datafile 2 to
    "/u01/app/oracle/oradata/SBDB/sysaux.261.832348339";
      set newname for datafile 3 to
    "/u01/app/oracle/oradata/SBDB/undotbs1.262.832348377";
      set newname for datafile 4 to
    "/u01/app/oracle/oradata/SBDB/undotbs2.264.832348459";
      set newname for datafile 5 to
    "/u01/app/oracle/oradata/SBDB/users.265.832348491";
      backup as copy reuse
      datafile 1 auxiliary format
    "/u01/app/oracle/oradata/SBDB/system.260.832348297" datafile
    2 auxiliary format
    "/u01/app/oracle/oradata/SBDB/sysaux.261.832348339" datafile
    3 auxiliary format
    "/u01/app/oracle/oradata/SBDB/undotbs1.262.832348377" datafile
    4 auxiliary format
    "/u01/app/oracle/oradata/SBDB/undotbs2.264.832348459" datafile
    5 auxiliary format
    "/u01/app/oracle/oradata/SBDB/users.265.832348491" ;
      sql 'alter system archive log current';
   }
   executing Memory Script
   
   executing command: SET NEWNAME
   
   renamed tempfile 1 to /u01/app/oracle/oradata/SBDB/temp.263.832348389 in control file
   
   executing command: SET NEWNAME
   
   executing command: SET NEWNAME
   
   executing command: SET NEWNAME
   
   executing command: SET NEWNAME
   
   executing command: SET NEWNAME
   
   Starting backup at 29-NOV-13
   channel prmy1: starting datafile copy
   input datafile file number=00001 name=+DATA/racdb/datafile/system.260.832348297
   channel prmy2: starting datafile copy
   input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.261.832348339
   output file name=/u01/app/oracle/oradata/SBDB/system.260.832348297 tag=TAG20131129T154953
   channel prmy1: datafile copy complete, elapsed time: 00:02:04
   channel prmy1: starting datafile copy
   input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.262.832348377
   output file name=/u01/app/oracle/oradata/SBDB/sysaux.261.832348339 tag=TAG20131129T154953
   channel prmy2: datafile copy complete, elapsed time: 00:02:35
   channel prmy2: starting datafile copy
   input datafile file number=00004 name=+DATA/racdb/datafile/undotbs2.264.832348459
   output file name=/u01/app/oracle/oradata/SBDB/undotbs1.262.832348377 tag=TAG20131129T154953
   channel prmy1: datafile copy complete, elapsed time: 00:01:42
   channel prmy1: starting datafile copy
   input datafile file number=00005 name=+DATA/racdb/datafile/users.265.832348491
   output file name=/u01/app/oracle/oradata/SBDB/undotbs2.264.832348459 tag=TAG20131129T154953
   channel prmy2: datafile copy complete, elapsed time: 00:01:03
   output file name=/u01/app/oracle/oradata/SBDB/users.265.832348491 tag=TAG20131129T154953
   channel prmy1: datafile copy complete, elapsed time: 00:00:25
   Finished backup at 29-NOV-13
   
   sql statement: alter system archive log current
   
   contents of Memory Script:
   {
      switch clone datafile all;
   }
   executing Memory Script
   
   datafile 1 switched to datafile copy
   input datafile copy RECID=5 STAMP=832780540 file name=/u01/app/oracle/oradata/SBDB/system.260.832348297
   datafile 2 switched to datafile copy
   input datafile copy RECID=6 STAMP=832780541 file name=/u01/app/oracle/oradata/SBDB/sysaux.261.832348339
   datafile 3 switched to datafile copy
   input datafile copy RECID=7 STAMP=832780542 file name=/u01/app/oracle/oradata/SBDB/undotbs1.262.832348377
   datafile 4 switched to datafile copy
   input datafile copy RECID=8 STAMP=832780542 file name=/u01/app/oracle/oradata/SBDB/undotbs2.264.832348459
   datafile 5 switched to datafile copy
   input datafile copy RECID=9 STAMP=832780543 file name=/u01/app/oracle/oradata/SBDB/users.265.832348491
   Finished Duplicate Db at 29-NOV-13
   released channel: prmy1
   released channel: prmy2
   released channel: stby
   
   RMAN>
   
   下面把备库启动到open only下面。并recover。
   
   [oracle@ENMOEDU dbs]$ sqlplus / as sysdba
   
   SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 14:06:42 2013
   
   Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SYS@SBDB> alter database open read only;
   
   Database altered.
   
   SYS@SBDB> alter database recover managed standby database using current logfile disconnect from session;
   
   Database altered.
   
   SYS@SBDB> select OPEN_MODE from V$database;
   
   OPEN_MODE
   --------------------
   READ ONLY WITH APPLY
   
4.下面就是开始测试了。这里我们是用的HR这个用户来做测试,在HR用户下面创建一个表,在备库去查询看是否创建成功,与在主库上面SWIRCH一次日志,看alert中的日志输出内容。
  在主库上面做测试
  [oracle@11g admin]$ sqlplus / as sysdba
   
  SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012
   
  Copyright (c) 1982, 2010, Oracle. All rights reserved.
   
   
  Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SQL> alter database set standby to maximize availability;
   
   Database altered.
   
   SQL> create table HR.test1 as select * from dba_objects;
   
   Table created.
   
   备库上面
   
   SQL> select count(*) from HR.test1;
   
     COUNT(*)
   ----------
        72391
   
   说明已经同步
   
   主库上面刷新日志。
   
   SQL> conn / as sysdba
   Connected.
   SQL> alter system archive log current;
   
   System altered.
   
 
 
   SYS@SBDB> select THREAD#,SEQUENCE#,ARCHIVED,APPLIED from v$archived_log;
   
      THREAD# SEQUENCE# ARC APPLIED
   ---------- ---------- --- ---------
            1 121 YES YES
            1 122 YES IN-MEMORY
   
 
 
5.执行切换
5.1 在主库上切换到备库
   
   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
   
   Database altered.
   
   SQL> shutdown immediate
   ORA-01092: ORACLE instance terminated. Disconnection forced
   SQL>
   SQL> quit
   Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
   With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
   Data Mining and Real Application Testing options
   [oracle@rac1 ~]$ sqlplus / as sysdba
   
   SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 16:25:05 2013
   
   Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
   Connected to an idle instance.
   
   SQL> startup mount
   ORACLE instance started.
   
   Total System Global Area 318046208 bytes
   Fixed Size 1344680 bytes
   Variable Size 184552280 bytes
   Database Buffers 125829120 bytes
   Redo Buffers 6320128 bytes
   Database mounted.
   SQL> select database_role from v$database;
   
   DATABASE_ROLE
   ----------------
   PHYSICAL STANDBY
   
   SQL> select THREAD#,SEQUENCE#,ARCHIVED,APPLIED from v$archived_log;
   
      THREAD# SEQUENCE# ARC APPLIED
   ---------- ---------- --- ---------
            1 103 YES YES
            1 103 YES YES
            2 30 YES YES
            2 29 YES NO
            2 30 YES NO
            2 31 YES YES
            2 32 YES YES
            1 104 YES YES
            1 105 YES YES
            1 106 YES YES
            1 107 YES YES
            1 108 YES YES
            1 109 YES YES
            1 110 YES YES
            1 111 YES YES
            1 112 YES YES
            1 112 YES YES
            1 113 YES YES
            1 113 YES YES
            1 114 YES YES
            1 114 YES YES
            1 115 YES YES
            1 115 YES YES
            1 116 YES YES
            1 116 YES YES
            1 117 YES YES
            1 117 YES YES
            1 118 YES YES
            1 119 YES YES
            1 118 YES YES
            1 119 YES YES
            1 120 YES YES
            1 120 YES YES
            1 121 YES YES
            1 121 YES YES
            1 122 YES YES
            1 122 YES YES
            1 123 YES NO
            1 123 YES YES
            1 124 YES NO
            1 124 YES NO
            1 125 YES NO
   
   92 rows selected.
   
   SQL>
   
5.2在备库上切换到主库
   
   SYS@SBDB> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
   
   Database altered.
   
   SYS@SBDB> shutdown immediate
   ORA-01109: database not open
   
   
   Database dismounted.
   ORACLE instance shut down.
   SYS@SBDB> startup
   ORACLE instance started.
   
   Total System Global Area 318046208 bytes
   Fixed Size 1344680 bytes
   Variable Size 184552280 bytes
   Database Buffers 125829120 bytes
   Redo Buffers 6320128 bytes
   Database mounted.
   Database opened.
   SYS@SBDB> select database_role from v$database;
   
   DATABASE_ROLE
   ----------------
   PRIMARY
   
   SYS@SBDB>
   
5.3在备库上应用日志
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   
   Database altered.
   
   SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1816613/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 诊断grid
下一篇: 整理表碎片
请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626608