DG搭建

jelephant發表於2015-10-27
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章