Oracle一步步搭建DataGuard

  • DataGuard环境:

      OS: SuSe 10
    
  Primary DB:
    
  	IP address:1.1.1.1
    
  	user:root
    
  	passwd:******
    
  	Oracle_SID:TEST
    
  	DB_UNIQUE_NAME:TEST_PD

  Standby DB:
    
  	IP address:2.2.2.2
    
  	user:root
    
  	passwd:******
    
  	Oracle_SID:TEST
    
  	DB_UNIQUE_NAME:TEST_ST

  • Primary端的配置

    1. Primary db设置force logging模式

      SQL> select force_logging from v$database;

FOR
      
---
      
YES

      如果非force Logging模式, 则如下设置

      SQL> alter database force logging;

    2. Primary db设置归档模式

       SQL> archive log list;		

 Database log mode              Archive Mode
      
 Automatic archival             Enabled
      
 Archive destination            /data/u/arch/
      
 Oldest online log sequence     64
      
 Next log sequence to archive   66
      
 Current log sequence           66

      如果非归档, 则如下设置

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

    3. 创建Standy口令文件

       oracle@test_mem_issue:~> orapwd file='/u/rman/orapwTEST' password=test123 entries=10

      scp口令文件到standby对应的位置:

       oracle@test_mem_issue:~> scp /u/rman/orapwdTEST oracle@2.2.2.2#36000:<$ORACLE_HOME>/dbs

      <$ORACLE_HOME>: oracle软件目录 /u/ora11g/product/11.2.0/db_1

    4. 修改初始化参数文件

      pfile默认的位置在$ORACLE_HOME/dbs目录下, 当然也可以自己执行:

       SQL> create pfile from spfile;

      在initTEST.ora中添加一下内容:

       *.DB_UNIQUE_NAME='TEST_PD'
      
 *.log_archive_dest_1='location=/u/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_PD'
      
 *.log_archive_dest_2='SERVICE=STANDBY lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_ST'
      
 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
      
 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
      
 *.standby_file_management='AUTO'
      
 *.FAL_SERVER='STANDBY'
      
 *.FAL_CLIENT='PRIMARY'
      
 *.log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)'
      
 *.log_archive_format='TEST_%t_%s_%r.arc'
      
 *.ARCHIVE_LAG_TARGET=900

      如果Primary和standby的数据文件位置不同, 还需要加如下2个参数:

       *.log_file_name_convert='/u/oradata/TEST/','/u/oradata1/TEST/'
      
 *.db_file_name_convert='/u/oradata/TEST/','/u/oradata1/TEST/'

      --注意:

       *.FAL_SERVER='STANDBY'
      
 *.FAL_CLIENT='PRIMARY'

      STANDBY,PRIMARY是tnsname.ora文件中配置的描述符

      用刚修改过的pfile启动primary db,并生成spfile.

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

 SQL> startup pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora';
      
 ORACLE instance started.

 Total System Global Area 1603411968 bytes
      
 Fixed Size                  2226912 bytes
      
 Variable Size             486540576 bytes
      
 Database Buffers         1107296256 bytes
      
 Redo Buffers                7348224 bytes
      
 Database mounted.
      
 Database opened.

 SQL> create spfile from pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora';

 File created.

    5. 修改listener.ora和tnsnams.ora文件

      Listener.ora文件

       LISTENER =
      
   (DESCRIPTION_LIST =
      
 	(DESCRIPTION =
      
 	  (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
      
 	)
      
   )

 SID_LIST_LISTENER =
      
   (SID_LIST =
      
 	(SID_DESC =
      
 	  (ORACLE_HOME = /u/ora11g/product/11.2.0/db_1)
      
 	  (SID_NAME = TEST)
      
 	)
      
   )

      Tnsnames.ora文件

       STANDBY =
      
   (DESCRIPTION =
      
 	(ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521))
      
 	(CONNECT_DATA =
      
 	  (SERVER = DEDICATED)
      
 	  (SERVICE_NAME = TEST)
      
 	)
      
   )

 PRIMARY =
      
   (DESCRIPTION =
      
 	(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
      
 	(CONNECT_DATA =
      
 	  (SERVER = DEDICATED)
      
 	  (SERVICE_NAME = TEST)
      
 	)
      
   )

      查看监听状态.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:16:34

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521)))
      
 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 Start Date                08-JUL-2013 17:20:22
      
 Uptime                    0 days 16 hr. 56 min. 12 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
   Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 Service "TESTXDB" has 1 instance(s).
      
   Instance "TEST", status READY, has 1 handler(s) for this service...

      如果没有启动, 则start.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs>lsnrctl start

      这里先stop, 然后start.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl stop

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:23:05

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521)))
      
 The command completed successfully

      再次查看监听状态.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:23:13

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521)))
      
 TNS-12541: TNS:no listener
      
  TNS-12560: TNS:protocol adapter error
      
   TNS-00511: No listener
      
    Linux Error: 111: Connection refused

      ok.启动监听.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl start

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:25:35

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

 Starting /u/ora11g/product/11.2.0/db_1/bin/tnslsnr: please wait...

 TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 System parameter file is /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Log messages written to /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml
      
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521)))

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521)))
      
 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 Start Date                09-JUL-2013 10:25:35
      
 Uptime                    0 days 0 hr. 0 min. 0 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
   Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 The command completed successfully

      此时登录session,动态注册。

       SQL> alter system register;

 System altered.

      查看监听状态.

       oracle@test_mem_issue:/u/ora11g/product/11.2.0/db_1/dbs> lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 10:25:57

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.132.24.177)(PORT=1521)))
      
 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 Start Date                09-JUL-2013 10:25:35
      
 Uptime                    0 days 0 hr. 0 min. 22 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/test_mem_issue/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.1)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
   Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 Service "TESTXDB" has 1 instance(s).
      
   Instance "TEST", status READY, has 1 handler(s) for this service...
      
 Service "TEST_PD" has 1 instance(s).
      
   Instance "TEST", status READY, has 1 handler(s) for this service...
      
 The command completed successfully

    6. RMAN备份主库

       rman target / nocatalog

 run{
      
 backup tag hot_db_bak_full filesperset 2 format '/u/rman/%d_%s_%p_%t_%T.bkd' (database);

 backup current controlfile format '/u/rman/%d_control_%s_%p_%t_%T.bkc';
      
 }

      如果出现ORA-28365: wallet is not open错误, 则需要配置rman取消加密:

       rman target / nocatalog
      
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
      
 CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
      
 CONFIGURE ENCRYPTION FOR DATABASE OFF;

      将/u/rman/目录下的*.bkd *.bkc scp到standby对应的目录

       rsync -a --progress --bwlimit=102400 *.bkd oracle@2.2.2.2:/u/rman/
      
 rsync -a --progress --bwlimit=102400 *.bkc oracle@2.2.2.2:/u/rman/

  • Standby端的配置

    1. 创建相关数据目录和归档目录

       cd /u
      
 mkdir arch
      
 mkdir rman

    2. 修改初始化参数文件

      从Primary端copy过来.

       scp /u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora oracle@2.2.2.2#36000:/u/ora11g/product/11.2.0/db_1/dbs

      修改如下:

       *.DB_UNIQUE_NAME='TEST_ST'
      
 *.log_archive_dest_1='location=/u/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_ST'
      
 *.log_archive_dest_2='SERVICE=PRIMARY lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_PD'
      
 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
      
 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
      
 *.standby_file_management='AUTO'
      
 *.FAL_SERVER='PRIMARY'
      
 *.FAL_CLIENT='STANDBY'
      
 *.log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)'
      
 *.log_archive_format='TEST_%t_%s_%r.arc'
      
 *.ARCHIVE_LAG_TARGET=900

    3. 用pfile将standby db启动到nomount状态

       SQL> startup  nomount pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora';
      
 ORACLE instance started.

 Total System Global Area 1603411968 bytes
      
 Fixed Size                  2226912 bytes
      
 Variable Size             486540576 bytes
      
 Database Buffers         1107296256 bytes
      
 Redo Buffers                7348224 bytes

    4. 创建spfile

       SQL> create spfile from pfile='/u/ora11g/product/11.2.0/db_1/dbs/initTEST.ora';

 File created.

    5. 设置standby的listener.ora和tnsnames.ora文件

      Listener.ora文件

       LISTENER =
      
   (DESCRIPTION_LIST =
      
     (DESCRIPTION =
      
       (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521))
      
     )
      
   )

 SID_LIST_LISTENER =
      
   (SID_LIST =
      
     (SID_DESC =
      
       (ORACLE_HOME = /u/ora11g/product/11.2.0/db_1)
      
       (SID_NAME = TEST)
      
     )
      
   )

      Tnsnames.ora文件

       STANDBY =
      
   (DESCRIPTION =
      
     (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521))
      
     (CONNECT_DATA =
      
       (SERVER = DEDICATED)
      
       (SERVICE_NAME = TEST)
      
     )
      
   )

 PRIMARY =
      
   (DESCRIPTION =
      
     (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
      
     (CONNECT_DATA =
      
       (SERVER = DEDICATED)
      
       (SERVICE_NAME = TEST)
      
     )
      
   )

      查看监听状态.

       [oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:13:49

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521)))
      
 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

 Start Date                09-JUL-2013 10:47:33
      
 Uptime                    0 days 0 hr. 26 min. 16 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
    Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 The command completed successfully

      如果没有启动,则start.

       [oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/dbs]$ lsnrctl start

      这里先stop,然后start.

       [oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl stop

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:14:20

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521)))
      
 The command completed successfully

      ok.启动监听.

       [oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl start

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 11:14:39

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

 Starting /u/ora11g/product/11.2.0/db_1/bin/tnslsnr: please wait...

 TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 System parameter file is /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Log messages written to /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml
      
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521)))

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521)))
      
 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 Start Date                09-JUL-2013 11:14:39
      
 Uptime                    0 days 0 hr. 0 min. 0 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
   Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 The command completed successfully

      登录session动态注册.

       SQL> alter system register;

 System altered.

      查看监听状态.

       [oracle@TENCENT64 /u/ora11g/product/11.2.0/db_1/network/admin]$ lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2013 14:42:06

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=2.2.2.2)(PORT=1521)))

 STATUS of the LISTENER
      
 ------------------------
      
 Alias                     LISTENER
      
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      
 Start Date                09-JUL-2013 13:30:07
      
 Uptime                    0 days 1 hr. 11 min. 58 sec
      
 Trace Level               off
      
 Security                  ON: Local OS Authentication
      
 SNMP                      OFF
      
 Listener Parameter File   /u/ora11g/product/11.2.0/db_1/network/admin/listener.ora
      
 Listener Log File         /u/ora11g/diag/tnslsnr/TENCENT64/listener/alert/log.xml
      
 Listening Endpoints Summary...
      
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2.2.2.2)(PORT=1521)))
      
 Services Summary...
      
 Service "TEST" has 1 instance(s).
      
   Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
      
 Service "TESTXDB" has 1 instance(s).
      
   Instance "TEST", status READY, has 1 handler(s) for this service...
      
 Service "TEST_ST" has 1 instance(s).
      
   Instance "TEST", status READY, has 1 handler(s) for this service...
      
 The command completed successfully

    6. 恢复standby控制文件

       [oracle@TENCENT64 /u/rman]$ rman target / nocatalog

 Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 3 16:50:04 2013

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

 connected to target database: TEST (not mounted)
      
 using target database control file instead of recovery catalog

 RMAN> restore standby controlfile from '/u/rman/TEST_control_20_1_819823252_20130703.bkc';

 Starting restore at 07/03/2013 16:50:32
      
 allocated channel: ORA_DISK_1
      
 channel ORA_DISK_1: SID=572 device type=DISK

 channel ORA_DISK_1: restoring control file
      
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
      
 output file name=/u/ora11g/oradata/TEST/control01.ctl
      
 output file name=/u/ora11g/oradata/TEST/control02.ctl
      
 Finished restore at 07/03/2013 16:50:34

 RMAN> sql 'alter database mount standby database';

 sql statement: alter database mount standby database
      
 released channel: ORA_DISK_1

    7. 恢复standby数据文件

       RMAN> restore database;

 Starting restore at 07/09/2013 14:48:25
      
 Starting implicit crosscheck backup at 07/09/2013 14:48:25
      
 allocated channel: ORA_DISK_1
      
 channel ORA_DISK_1: SID=572 device type=DISK
      
 allocated channel: ORA_DISK_2
      
 channel ORA_DISK_2: SID=1142 device type=DISK
      
 allocated channel: ORA_DISK_3
      
 channel ORA_DISK_3: SID=1711 device type=DISK
      
 allocated channel: ORA_DISK_4
      
 channel ORA_DISK_4: SID=8 device type=DISK
      
 Crosschecked 6 objects
      
 Crosschecked 8 objects
      
 Finished implicit crosscheck backup at 07/09/2013 14:48:26

 Starting implicit crosscheck copy at 07/09/2013 14:48:26
      
 using channel ORA_DISK_1
      
 using channel ORA_DISK_2
      
 using channel ORA_DISK_3
      
 using channel ORA_DISK_4
      
 Finished implicit crosscheck copy at 07/09/2013 14:48:26

 searching for all files in the recovery area
      
 cataloging files...
      
 no files cataloged

 using channel ORA_DISK_1
      
 using channel ORA_DISK_2
      
 using channel ORA_DISK_3
      
 using channel ORA_DISK_4

 channel ORA_DISK_1: starting datafile backup set restore

 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
      
 channel ORA_DISK_1: restoring datafile 00003 to /u/ora11g/oradata/TEST/undotbs01.dbf
      
 channel ORA_DISK_1: reading from backup piece /u/rman/TEST_16_1_820317132_20130709.bkd
      
 channel ORA_DISK_2: starting datafile backup set restore
      
 channel ORA_DISK_2: specifying datafile(s) to restore from backup set
      
 channel ORA_DISK_2: restoring datafile 00004 to /u/ora11g/oradata/TEST/users01.dbf
      
 channel ORA_DISK_2: reading from backup piece /u/rman/TEST_17_1_820317132_20130709.bkd
      
 channel ORA_DISK_3: starting datafile backup set restore
      
 channel ORA_DISK_3: specifying datafile(s) to restore from backup set
      
 channel ORA_DISK_3: restoring datafile 00002 to /u/ora11g/oradata/TEST/sysaux01.dbf
      
 channel ORA_DISK_3: reading from backup piece /u/rman/TEST_15_1_820317132_20130709.bkd
      
 channel ORA_DISK_4: starting datafile backup set restore
      
 channel ORA_DISK_4: specifying datafile(s) to restore from backup set
      
 channel ORA_DISK_4: restoring datafile 00001 to /u/ora11g/oradata/TEST/system01.dbf
      
 channel ORA_DISK_4: reading from backup piece /u/rman/TEST_14_1_820317132_20130709.bkd
      
 channel ORA_DISK_2: piece handle=/u/rman/TEST_17_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL
      
 channel ORA_DISK_2: restored backup piece 1
      
 channel ORA_DISK_2: restore complete, elapsed time: 00:00:00
      
 channel ORA_DISK_1: piece handle=/u/rman/TEST_16_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL
      
 channel ORA_DISK_1: restored backup piece 1
      
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
      
 channel ORA_DISK_3: piece handle=/u/rman/TEST_15_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL
      
 channel ORA_DISK_3: restored backup piece 1
      
 channel ORA_DISK_3: restore complete, elapsed time: 00:00:25
      
 channel ORA_DISK_4: piece handle=/u/rman/TEST_14_1_820317132_20130709.bkd tag=HOT_DB_BAK_FULL
      
 channel ORA_DISK_4: restored backup piece 1
      
 channel ORA_DISK_4: restore complete, elapsed time: 00:00:35
      
 Finished restore at 07/09/2013 14:49:02

 RMAN> recover database;

 Starting recover at 07/09/2013 14:58:18
      
 allocated channel: ORA_DISK_1
      
 channel ORA_DISK_1: SID=8 device type=DISK
      
 allocated channel: ORA_DISK_2
      
 channel ORA_DISK_2: SID=574 device type=DISK
      
 allocated channel: ORA_DISK_3
      
 channel ORA_DISK_3: SID=1144 device type=DISK
      
 allocated channel: ORA_DISK_4
      
 channel ORA_DISK_4: SID=1714 device type=DISK

 starting media recovery

 archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc
      
 ......
      
 archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc
      
 archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68
      
 RMAN-00571: ===========================================================
      
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      
 RMAN-00571: ===========================================================
      
 RMAN-03002: failure of recover command at 07/09/2013 14:58:20
      
 RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u/arch/TEST_1_68_730313063.arc'

 ORA-00328: archived log ends at change 1033153, need later change 1069832
      
 ORA-00334: archived log: '/u/arch/TEST_1_68_730313063.arc'

 RMAN-00571: ===========================================================
      
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      
 RMAN-00571: ===========================================================
      
 RMAN-00558: error encountered while parsing input commands
      
 RMAN-01006: error signaled during parse
      
 RMAN-02003: unrecognized character:

      这里recover时报错,提示68号归档日志找不到.

      需要将primary端的相应归档日志scp到执行目录下,再次执行recover.

       RMAN> recover database;

 Starting recover at 07/09/2013 14:59:26
      
 using channel ORA_DISK_1
      
 using channel ORA_DISK_2
      
 using channel ORA_DISK_3
      
 using channel ORA_DISK_4

 starting media recovery

 archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc
      
 archived log for thread 1 with sequence 70 is already on disk as file /u/arch/TEST_1_70_730313063.arc
      
 ......
      
 archived log for thread 1 with sequence 89 is already on disk as file /u/arch/TEST_1_89_730313063.arc
      
 archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc
      
 archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68
      
 archived log file name=/u/arch/TEST_1_69_730313063.arc thread=1 sequence=69
      
 archived log file name=/u/arch/TEST_1_70_730313063.arc thread=1 sequence=70
      
 ......
      
 archived log file name=/u/arch/TEST_1_89_730313063.arc thread=1 sequence=89
      
 archived log file name=/u/arch/TEST_1_90_730313063.arc thread=1 sequence=90
      
 unable to find archived log
      
 archived log thread=1 sequence=91
      
 RMAN-00571: ===========================================================
      
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      
 RMAN-00571: ===========================================================
      
 RMAN-03002: failure of recover command at 07/09/2013 14:59:30
      
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91 and starting SCN of 1091265

    8. 确认standby相关参数

       SQL> show parameter log_archive_config;

 NAME                                 TYPE        VALUE
      
 ------------------------------------ ----------- ------------------------------
      
 log_archive_config                   string      DG_CONFIG=(TEST_ST,TEST_PD
      
                                      )
      
 SQL> show parameter fal_server;

 NAME                                 TYPE        VALUE
      
 ------------------------------------ ----------- ------------------------------
      
 fal_server                           string      PRIMARY

 SQL> show parameter fal_client;

 NAME                                 TYPE        VALUE
      
 ------------------------------------ ----------- ------------------------------
      
 fal_client                           string      STANDBY

      若不正确, 需要修改:

       SQL> alter system set log_archive_config='DG_CONFIG=(TEST_PD,TEST_ST)';

 System altered.

 SQL> alter system set fal_server=PRIMARY;

 System altered.

 SQL> alter system set fal_client=STANDBY;

 System altered.

    9. 添加standby redolog files

       standby redofile count=(online redofile + 1)*2
      
 standby redofile size=online redofile size

 alter database add standby logfile group 34 ('/u/arch/TEST/stby/db_stb_redo04.log') size 50m;
      
 alter database add standby logfile group 33 ('/u/arch/TEST/stby/db_stb_redo03.log') size 50m;
      
 alter database add standby logfile group 32 ('/u/arch/TEST/stby/db_stb_redo02.log') size 50m;
      
 alter database add standby logfile group 31 ('/u/arch/TEST/stby/db_stb_redo01.log') size 50m;
      
 alter database add standby logfile group 30 ('/u/arch/TEST/stby/db_stb_redo10.log') size 50m;
      
 alter database add standby logfile group 39 ('/u/arch/TEST/stby/db_stb_redo09.log') size 50m;
      
 alter database add standby logfile group 38 ('/u/arch/TEST/stby/db_stb_redo08.log') size 50m;
      
 alter database add standby logfile group 37 ('/u/arch/TEST/stby/db_stb_redo07.log') size 50m;
      
 alter database add standby logfile group 36 ('/u/arch/TEST/stby/db_stb_redo06.log') size 50m;
      
 alter database add standby logfile group 35 ('/u/arch/TEST/stby/db_stb_redo05.log') size 50m;

    10. DataGuard启动

      --注意:DataGuard的启动顺序

      启动顺序: 先standby后primary

      关闭顺序: 先primary后standby

      (1). 在standby db上执行

      SQL> select open_mode from v$database;

OPEN_MODE
      
--------------------
      
MOUNTED

SQL> alter database open read only;

Database altered.

      --注意:

      如果没有处理前面recover的报错(找不到归档日志), 这里可能启动的时候报system表空间和控制文件记录的内容出现不一致, 这个时候最好的方法就是, 去主库拷贝那些丢失的归档.

      SQL> alter database open read only;
      
alter database open read only
      
*
      
ERROR at line 1:
      
ORA-10458: standby database requires recovery
      
ORA-01152: file 1 was not restored from a sufficiently old backup
      
ORA-01110: data file 1: '/u/ora11g/oradata/TEST/system01.dbf'

      之前recover的时候注意到,有ORA提示到归档日志有缺失.

      RMAN-00571: ===========================================================
      
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      
RMAN-00571: ===========================================================
      
RMAN-03002: failure of recover command at 07/09/2013 14:58:20
      
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u/arch/TEST_1_68_730313063.arc'
      
ORA-00328: archived log ends at change 1033153, need later change 1069832
      
ORA-00334: archived log: '/u/arch/TEST_1_68_730313063.arc'

      从primary上scp缺失的arc文件,然后再recover database一下.

      RMAN> recover database;

Starting recover at 07/09/2013 14:59:26
      
using channel ORA_DISK_1
      
using channel ORA_DISK_2
      
using channel ORA_DISK_3
      
using channel ORA_DISK_4

starting media recovery

archived log for thread 1 with sequence 69 is already on disk as file /u/arch/TEST_1_69_730313063.arc

archived log for thread 1 with sequence 70 is already on disk as file /u/arch/TEST_1_70_730313063.arc
      
......
      
archived log for thread 1 with sequence 89 is already on disk as file /u/arch/TEST_1_89_730313063.arc
      
archived log for thread 1 with sequence 90 is already on disk as file /u/arch/TEST_1_90_730313063.arc
      
archived log file name=/u/arch/TEST_1_68_730313063.arc thread=1 sequence=68
      
archived log file name=/u/arch/TEST_1_69_730313063.arc thread=1 sequence=69
      
archived log file name=/u/arch/TEST_1_70_730313063.arc thread=1 sequence=70
      
......
      
archived log file name=/u/arch/TEST_1_89_730313063.arc thread=1 sequence=89
      
archived log file name=/u/arch/TEST_1_90_730313063.arc thread=1 sequence=90
      
unable to find archived log
      
archived log thread=1 sequence=91
      
RMAN-00571: ===========================================================
      
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      
RMAN-00571: ===========================================================
      
RMAN-03002: failure of recover command at 07/09/2013 14:59:30
      
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91 and starting SCN of 1091265

      再次以read only方式启动standby db就可以了.

      SQL> alter database open read only;

Database altered.

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

Database altered.

      (2).在primay db上检查如下参数,如果没有则设置.

      SQL> alter system set log_archive_dest_2='SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_ST';

System altered.

SQL> alter system set log_archive_dest_state_2=ENABLE;

System altered.

SQL> alter system set archive_lag_target=900;

System altered.

      (3).查询是否有出错信息(primary和standby上均执行下)

      primary上执行:

      SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5;

   DEST_ID STATUS    DESTINATIO ERROR
      
---------- --------- ---------- ----------
      
         1 VALID     /u/arch
      
         2 VALID     STANDBY
      
         3 INACTIVE
      
         4 INACTIVE
      
         5 INACTIVE

      standby上执行:

      SQL> select dest_id,status,destination,error from v$archive_dest where dest_id<=5;

   DEST_ID STATUS    DESTINATIO ERROR
      
---------- --------- ---------- ----------
      
         1 VALID     /u/arch
      
         2 VALID     PRIMARY
      
         3 INACTIVE
      
         4 INACTIVE
      
         5 INACTIVE

    11. 验证DataGuard：

      (1).在standby查看

      SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIME          NEXT_TIME
      
---------- ------------------- -------------------
      
        68 07/09/2013 11:54:31 07/09/2013 12:09:31
      
        68 07/09/2013 09:49:35 07/09/2013 10:04:35
      
        68 07/09/2013 11:54:31 07/09/2013 12:09:31
      
		......
      
        91 07/09/2013 14:56:01 07/09/2013 15:11:00
      
        92 07/09/2013 15:11:00 07/09/2013 15:19:42
      
        93 07/09/2013 15:19:42 07/09/2013 15:34:42

28 rows selected.

      (2).在primary上执行归档命令并查看已归档的日志

      SQL> alter system archive log current;

System altered.

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIME          NEXT_TIME
      
---------- ------------------- -------------------
      
        62 09/25/2010 03:20:17 07/08/2013 21:10:42
      
        63 07/08/2013 21:10:42 07/08/2013 21:10:45
      
        64 07/08/2013 21:10:45 07/08/2013 21:10:47
      
		......
      
        93 07/09/2013 15:19:42 07/09/2013 15:34:42
      
        94 07/09/2013 15:34:42 07/09/2013 15:40:44
      
        94 07/09/2013 15:34:42 07/09/2013 15:40:44

61 rows selected.

      (3).再次在standby上看当前已归档的日志

      SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIME          NEXT_TIME
      
---------- ------------------- -------------------
      
        68 07/09/2013 11:54:31 07/09/2013 12:09:31
      
        68 07/09/2013 09:49:35 07/09/2013 10:04:35
      
        68 07/09/2013 11:54:31 07/09/2013 12:09:31
      
        ......
      
        91 07/09/2013 14:56:01 07/09/2013 15:11:00
      
        92 07/09/2013 15:11:00 07/09/2013 15:19:42
      
        93 07/09/2013 15:19:42 07/09/2013 15:34:42
      
        94 07/09/2013 15:34:42 07/09/2013 15:40:44

29 rows selected.

      另外, 同样可以从alert文件里也看到了94号日志应用.

      $ tail -f alert_TEST.log
      
Tue Jul 09 15:40:44 2013
      
Expanded controlfile section 11 from 28 to 280 records
      
Requested to grow by 252 records; added 9 blocks of records
      
Archived Log entry 29 added for thread 1 sequence 94 ID 0xc8c4a8e7 dest 1:
      
Tue Jul 09 15:40:44 2013
      
RFS[3]: Selected log 30 for thread 1 sequence 95 dbid -926624281 branch 730313063
      
Tue Jul 09 15:40:44 2013
      
Media Recovery Waiting for thread 1 sequence 95 (in transit)
      
Recovery of Online Redo Log: Thread 1 Group 30 Seq 95 Reading mem 0
      
  Mem# 0: /u/arch/TEST/stby/db_stb_redo10.log

--END--

最新文章

  1. console.dir() 与 console.log() 区别
  2. OpenCASCADE Application Framework Data Framework Services
  3. SNF开发平台WinForm之三-开发-单表选择控件创建-SNF快速开发平台3.3-Spring.Net.Framework
  4. 运用BigDecimal精确计算
  5. sqlserver2005唯一性约束
  6. virtualbox 复制多个虚拟机 （宿主机redhat）
  7. JDBC操作数据库的学习(1)
  8. caffe训练超参数
  9. Entity Framework Core 2.0 数据库迁移
  10. Mybatis第一篇【介绍、快速入门、工作流程】
  11. 使用boost::multi_index高速构建排行榜
  12. ltp-ddt emmc_dd_rw
  13. 如何使用java代码启动tomcat和打开浏览器
  14. django+uwsgi+nginx数据表过大引起&quot;out of memory for query result&quot;
  15. CentOS 7使用通过二进制包安装MySQL 5.7.18
  16. tomcat，很多时候，可以在服务server.xml中可以实现一些效果
  17. 【C#】C#创建Windows Service服务
  18. 精力管理 | 迅速恢复精力的N个技巧，四个关键词以及自我管理的方法和工具列表
  19. 【Visual Studio】控制台程序运行时一闪而过
  20. 什么是ORM，以及在php上的使用？

热门文章

  1. 笔记-python操作mysql
  2. Spring---单例模式（Singleton）的6种实现
  3. phpStorm9.0 +xampp+chrome php调试环境配置！
  4. 能ping通网络，也正常连接，就是打不开网页，无法访问网络
  5. flask利用session身份伪造
  6. ASP.NET Core 2.1 源码学习之 Options[3]:IOptionsMonitor 【转】
  7. C++ Primer 第3章 字符串、向量和数组
  8. 带外键Mysql
  9. hdu 1574 RP问题 01背包的变形
  10. fragment中的WebView返回上一页