Oracle11g Data Guard物理备用数据库搭建与配置(第2部分 配置物理备用数据库)

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle11g Data Guard物理备用数据库搭建与配置(第2部分 配置物理备用数据库)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

环境准备

接着上一篇文章 Oracle11g Data Guard物理备用数据库搭建与配置(第1部分 主数据库实例创建)


在我的测试环境中，我准备了两台CentOS7.4虚拟机，并同时都安装了Oracle11gR2的11.2.0.1.0企业版的数据库软件，其中只有主服务器创建一个数据库实例，备用服务器仅安装Oracle数据库软件。

主数据库:

OS: CentOS7.4

Hostname: hmdb11dg-db1

Oracle Version: 11.2.0.1.0

Oracle SID: HMDG(使用DBCA工具创建的一个数据库)

备数据库:

OS: CentOS7.4

Hostname: hmdb11dg-db2

Oracle Version: 11.2.0.1.0

Oracle SID: HMDG2

注意: 在开始之前，备用服务上还没有将要与主服务器同步备份的数据库实例。



一、主服务器前提配置


1. 启用归档日志

检查主数据是否处于归档日志模式

  1. sql>selectlog_modefromv$database;
  2.  
  3. LOG_MODE
  4. ------------
  5. ARCHIVELOG

如果是NOARCHIVELOG模式，则将其修改为ARCHIVELOG模式

  1. sql>SHUTDOWNIMMEDIATE;
  2. sql>STARTUPMOUNT;
  3. sql>ALTERDATABASEARCHIVELOG;
  4. sql>ALTERDATABASEOPEN;


2.启用强制日志

  1. sql>ALTERDATABASEFORCELOGGING;
  2.  
  3. Databasealtered.
  4.  
  5. sql>SELECTNAME,FORCE_LOGGINGFROMV$DATABASE;
  6.  
  7. NAMEFOR
  8. ------------
  9. HMDGYES


3.创建备用重做日志


备用重做日志文件的大小要与当前主数据库的在线重做日志文件大小完全匹配。

确定备用重做日志文件组的数量，建议的数量：(每个线程最大的日志数 + 1) * 最大线程数


查看联机重做日志

  1. sql>setpagesize100
  2. sql>setlinesize200
  3. sql>colGROUP#format99
  4. sql>colSTATUSformata10
  5. sql>colTYPEformata10
  6. sql>colMEMBERformata50
  7. sql>colIS_RECOVERY_DEST_FILEformata10
  8.  
  9. sql>select*fromv$logfile;
  10.  
  11. GROUP#STATUSTYPEMEMBERIS_RECOVER
  12. --------------------------------------------------------------------------------------
  13. 1ONLINE/u01/app/oracle/oradata/HMDG/redo01.logNO
  14. 2ONLINE/u01/app/oracle/oradata/HMDG/redo02.logNO
  15. 3ONLINE/u01/app/oracle/oradata/HMDG/redo03.logNO
  16. 4ONLINE/u01/app/oracle/oradata/HMDG/redo04.logNO


这里将创建10组备用重做日志

  1. ALTERDATABASEADDSTANDBYLOGFILEGROUP5'/u01/app/oracle/oradata/HMDG/redo05.log'SIZE500M;
  2. ALTERDATABASEADDSTANDBYLOGFILEGROUP6'/u01/app/oracle/oradata/HMDG/redo06.log'SIZE500M;
  3. ALTERDATABASEADDSTANDBYLOGFILEGROUP7'/u01/app/oracle/oradata/HMDG/redo07.log'SIZE500M;
  4. ALTERDATABASEADDSTANDBYLOGFILEGROUP8'/u01/app/oracle/oradata/HMDG/redo08.log'SIZE500M;
  5. ALTERDATABASEADDSTANDBYLOGFILEGROUP9'/u01/app/oracle/oradata/HMDG/redo09.log'SIZE500M;
  6. ALTERDATABASEADDSTANDBYLOGFILEGROUP10'/u01/app/oracle/oradata/HMDG/redo10.log'SIZE500M;
  7. ALTERDATABASEADDSTANDBYLOGFILEGROUP11'/u01/app/oracle/oradata/HMDG/redo11.log'SIZE500M;
  8. ALTERDATABASEADDSTANDBYLOGFILEGROUP12'/u01/app/oracle/oradata/HMDG/redo12.log'SIZE500M;
  9. ALTERDATABASEADDSTANDBYLOGFILEGROUP13'/u01/app/oracle/oradata/HMDG/redo13.log'SIZE500M;
  10. ALTERDATABASEADDSTANDBYLOGFILEGROUP14'/u01/app/oracle/oradata/HMDG/redo14.log'SIZE500M;


查看备用重做日志

  1. sql>SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUSFROMV$STANDBY_LOG;
  2.  
  3. GROUP#THREAD#SEQUENCE#ARCSTATUS
  4. ---------------------------------------
  5. 500YESUNASSIGNED
  6. 600YESUNASSIGNED
  7. 700YESUNASSIGNED
  8. 800YESUNASSIGNED
  9. 900YESUNASSIGNED
  10. 1000YESUNASSIGNED
  11. 1100YESUNASSIGNED
  12. 1200YESUNASSIGNED
  13. 1300YESUNASSIGNED
  14. 1400YESUNASSIGNED
  15.  
  16. 10rowsselected.



4. 开启闪回日志

  1. sql>alterdatabaseflashbackon;
  2. sql>selectflashback_onfromv$database;
  3.  
  4. FLASHBACK_ON
  5. ------------------
  6. YES



二、监听与TNS配置


1.监听配置

主备数据库必须注册静态监听服务(listener.ora)

  1. $cat/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  2.  
  3. LISTENER=
  4. (DESCRIPTION_LIST=
  5. (DESCRIPTION=
  6. (ADDRESS=(PROTOCOL=TCP)(HOST=hmdb11dg-db1)(PORT=1521))
  7. )
  8. )
  9.  
  10. SID_LIST_LISTENER=
  11. (SID_LIST=
  12. (SID_DESC=
  13. (GLOBAL_DBNAME=HMDG.DB)
  14. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
  15. (SID_NAME=HMDG)
  16. )
  17. )
  18.  
  19. ADR_BASE_LISTENER=/u01/app/oracle
  20.  
  21.  
  22. #备数据库
  23. LISTENER=
  24. (DESCRIPTION_LIST=
  25. (DESCRIPTION=
  26. (ADDRESS=(PROTOCOL=TCP)(HOST=hmdb11dg-db2)(PORT=1521))
  27. )
  28. )
  29.  
  30. SID_LIST_LISTENER=
  31. (SID_LIST=
  32. (SID_DESC=
  33. (GLOBAL_DBNAME=HMDG2.DB)
  34. (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
  35. (SID_NAME=HMDG2)
  36. )
  37. )
  38.  
  39. ADR_BASE_LISTENER=/u01/app/oracle



2. 主备数据库TNS别名连接信息配置

修改两台服务器上的$ORACLE_HOME/network/admin/tnsnames.ora配置文件，主备使用相同的配置

  1. HMDG=
  2. (DESCRIPTION=
  3. (ADDRESS=(PROTOCOL=TCP)(HOST=hmdb11dg-db1)(PORT=1521))
  4. (CONNECT_DATA=
  5. (SERVER=DEDICATED)
  6. (SERVICE_NAME=HMDG.DB)
  7. )
  8. )
  9.  
  10. HMDG2=
  11. (DESCRIPTION=
  12. (ADDRESS=(PROTOCOL=TCP)(HOST=hmdb11dg-db2)(PORT=1521))
  13. (CONNECT_DATA=
  14. (SERVER=DEDICATED)
  15. (SERVICE_NAME=HMDG2.DB)
  16. )
  17. )


3. 测试使用TNS别名连接数据库

  1. [oracle@hmdb11dg-db1~]$sqlplussystem/oracle@HMDG
  2.  
  3. sql*Plus:Release11.2.0.1.0ProductiononThuMar2220:47:282018
  4.  
  5. Copyright(c)1982,2009,Oracle.Allrightsreserved.
  6.  
  7.  
  8. Connectedto:
  9. OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bitProduction
  10. WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
  11.  
  12. sql>


三、主数据库初始化参数配置


1. 创建PFILE参数文件

使用如下语句创建PFILE配置文件，该文件自动生成在/u01/app/oracle/product/11.2.0/db_1/dbs目录下

  1. sql>CREATEPFILEFROMSPFILE;


2. 修改PFILE配置

使用刚刚生成的PFILE文件，修改如下配置

  1. HMDG.__db_cache_size=1023410176
  2. HMDG.__java_pool_size=16777216
  3. HMDG.__large_pool_size=16777216
  4. HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironment
  5. HMDG.__pga_aggregate_target=1325400064
  6. HMDG.__sga_target=1962934272
  7. HMDG.__shared_io_pool_size=0
  8. HMDG.__shared_pool_size=872415232
  9. HMDG.__streams_pool_size=0
  10. *.audit_file_dest='/u01/app/oracle/admin/HMDG/adump'
  11. *.audit_trail='db'
  12. *.compatible='11.2.0.0.0'
  13. *.control_files='/u01/app/oracle/oradata/HMDG/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG/control02.ctl'
  14. *.db_block_size=8192
  15. *.db_domain=''
  16. *.db_name='HMDG'
  17.  
  18. *.db_unique_name='HMDG'
  19. *.log_archive_config='DG_CONFIG=(HMDG,HMDG2)'
  20.  
  21. LOG_ARCHIVE_DEST_1=
  22. 'LOCATION=/u01/app/oracle/oradata/HMDG/
  23. VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  24. DB_UNIQUE_NAME=HMDG'
  25.  
  26. LOG_ARCHIVE_DEST_2=
  27. 'SERVICE=HMDG2ASYNC
  28. VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  29. DB_UNIQUE_NAME=HMDG2'
  30.  
  31. LOG_ARCHIVE_DEST_STATE_1=ENABLE
  32. LOG_ARCHIVE_DEST_STATE_2=ENABLE
  33. LOG_ARCHIVE_MAX_PROCESSES=30
  34.  
  35. FAL_SERVER=HMDG2
  36. FAL_CLIENT=HMDG
  37. DB_FILE_NAME_CONVERT='HMDG2','HMDG'
  38. LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG2/','/u01/app/oracle/oradata/HMDG/'
  39. STANDBY_FILE_MANAGEMENT=AUTO
  40.  
  41. *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
  42. *.db_recovery_file_dest_size=21474836480
  43. *.diagnostic_dest='/u01/app/oracle'
  44. *.dispatchers='(PROTOCOL=TCP)(SERVICE=HMDGXDB)'
  45. *.log_archive_format='%t_%s_%r.dbf'
  46. *.memory_target=3277848576
  47. *.open_cursors=300
  48. *.processes=5000
  49. *.remote_login_passwordfile='EXCLUSIVE'
  50. *.sessions=5505
  51. *.undo_tablespace='UNDOTBS1'


3. 使用刚刚创建的PFILE文件重新启动数据库，并且重新创建一个SPFILE文件

  1. sql>shutdownimmediate
  2. Databaseclosed.
  3. Databasedismounted.
  4. ORACLEinstanceshutdown.
  5. sql>startuppfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora';
  6. ORACLEinstancestarted.
  7.  
  8. TotalSystemGlobalArea3273641984bytes
  9. FixedSize2217792bytes
  10. VariableSize2315258048bytes
  11. DatabaseBuffers939524096bytes
  12. RedoBuffers16642048bytes
  13. Databasemounted.
  14. DatabaSEOpened.
  15. sql>createspfilefrompfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora';#创建SPFILE文件
  16.  
  17. Filecreated.
  18.  
  19. sql>shutdownimmediate
  20. Databaseclosed.
  21. Databasedismounted.
  22. ORACLEinstanceshutdown.
  23. sql>startup
  24. ORACLEinstancestarted.
  25.  
  26. TotalSystemGlobalArea3273641984bytes
  27. FixedSize2217792bytes
  28. VariableSize2315258048bytes
  29. DatabaseBuffers939524096bytes
  30. RedoBuffers16642048bytes
  31. Databasemounted.
  32. DatabaSEOpened.


注意：一旦由于PFILE参数配置信息错误或者使用PFILE文件启动报错需要修改PFILE文件参数的时候，都必须要重新创建SPFILE文件。(主备数据库都是一样)


4. 备份数据库(在这里是非必须的，因为在本次操作中我使用的是RMAN复制到备用数据库)

  1. [oracle@hmdb11dg-db1dbs]$rmantarget=/
  2.  
  3. RecoveryManager:Release11.2.0.1.0-ProductiononThuMar2221:12:062018
  4.  
  5. Copyright(c)1982,Oracleand/oritsaffiliates.Allrightsreserved.
  6.  
  7. connectedtotargetdatabase:HMDG(DBID=787192145)
  8.  
  9. RMAN>BACKUPDATABASEPLUSARCHIVELOG;


5. 创建备用数据库控制文件

  1. sql>ALTERDATABASECREATESTANDBYCONTROLFILEAS'/tmp/HMDG2.ctl';


6. 创建备用数据库的PFILE文件

  1. sql>CREATEPFILE='/tmp/initHMDG2.ora'FROMSPFILE;


7. 创建备用数据的密码文件

  1. [oracle@hmdb11dg-db1~]$cp/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG/tmp/orapwHMDG2



四、备用数据库服务器设置


1.备用数据库上创建必要的数据库文件目录

  1. $mkdir/u01/app/oracle/admin
  2. $mkdir/u01/app/oracle/admin/HMDG2
  3. $mkdir/u01/app/oracle/admin/HMDG2/{adump,dpdump,pfile,scripts}
  4. $mkdir-p/u01/app/oracle/oradata/HMDG2
  5. $mkdir-p/u01/app/oracle/flash_recovery_area/HMDG2


2. 从主数据库上拷贝配置文件到备用数据库(备用服务器上操作)


将控制文件、参数文件和密码文件从主服务器上复制到备用服务器上

  1. $scporacle@hmdb11dg-db1:/tmp/HMDG2.ctl/u01/app/oracle/oradata/HMDG2/control01.ctl
  2. $cp/u01/app/oracle/oradata/HMDG2/control01.ctl/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl
  3. $scporacle@hmdb11dg-db1:/tmp/orapwHMDG2/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2
  4. $scporacle@hmdb11dg-db1:/tmp/initHMDG2.ora/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora


3. 修改备用数据库初始化参数


修改备用服务器的PFILE文件$ORACLE_HOME/dbs/initHMDG2.ora

  1. HMDG.__db_cache_size=939524096
  2. HMDG.__java_pool_size=16777216
  3. HMDG.__large_pool_size=16777216
  4. HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironment
  5. HMDG.__pga_aggregate_target=1325400064
  6. HMDG.__sga_target=1962934272
  7. HMDG.__shared_io_pool_size=0
  8. HMDG.__shared_pool_size=956301312
  9. HMDG.__streams_pool_size=0
  10. *.audit_file_dest='/u01/app/oracle/admin/HMDG2/adump'
  11. *.audit_trail='db'
  12. *.compatible='11.2.0.0.0'
  13. *.control_files='/u01/app/oracle/oradata/HMDG2/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl'
  14. *.db_block_size=8192
  15. *.db_domain=''
  16. *.DB_FILE_NAME_CONVERT='HMDG','HMDG2'
  17. *.db_name='HMDG'
  18. *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
  19. *.db_recovery_file_dest_size=21474836480
  20. *.db_unique_name='HMDG2'
  21. *.diagnostic_dest='/u01/app/oracle'
  22. *.dispatchers='(PROTOCOL=TCP)(SERVICE=HMDGXDB)'
  23. *.FAL_CLIENT='HMDG2'
  24. *.FAL_SERVER='HMDG'
  25. *.log_archive_config='DG_CONFIG=(HMDG,HMDG2)'
  26. *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/HMDG2/
  27. VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  28. DB_UNIQUE_NAME=HMDG2'
  29. *.LOG_ARCHIVE_DEST_2='SERVICE=HMDGASYNC
  30. VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  31. DB_UNIQUE_NAME=HMDG'
  32. *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
  33. *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
  34. *.log_archive_format='%t_%s_%r.dbf'
  35. *.LOG_ARCHIVE_MAX_PROCESSES=30
  36. *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG/','/u01/app/oracle/oradata/HMDG2/'
  37. *.memory_target=3277848576
  38. *.open_cursors=300
  39. *.processes=5000
  40. *.remote_login_passwordfile='EXCLUSIVE'
  41. *.sessions=5505
  42. *.STANDBY_FILE_MANAGEMENT='AUTO'
  43. *.undo_tablespace='UNDOTBS1'



4. 使用刚刚创建的PFILE参数文件启动备用数据库

  1. [oracle@hmdb11dg-db2~]$sqlplus/assysdba
  2.  
  3. sql*Plus:Release11.2.0.1.0ProductiononThuMar2221:42:242018
  4.  
  5. Copyright(c)1982,Oracle.Allrightsreserved.
  6.  
  7. Connectedtoanidleinstance.
  8.  
  9. sql>STARTUPNOMOUNTPFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora';
  10. ORACLEinstancestarted.
  11.  
  12. TotalSystemGlobalArea3273641984bytes
  13. FixedSize2217792bytes
  14. VariableSize2197817536bytes
  15. DatabaseBuffers1056964608bytes
  16. RedoBuffers16642048bytes


5. 创建SPFILE文件

  1. sql>CREATESPFILEFROMPFILE;
  2.  
  3. Filecreated.

注意：一旦由于PFILE参数配置信息错误或者使用PFILE文件启动报错需要修改PFILE文件参数的时候，都必须要重新使用该语句创建SPFILE文件。(主备数据库都是一样)



五、在备用数据库上使用rman恢复数据到备用数据库(DUPLICATE)


1. 建立连接

  1. [oracle@hmdb11dg-db2~]$rmanTARGETsys/oracle@HMDGAUXILIARYsys/oracle@HMDG2
  2.  
  3. RecoveryManager:Release11.2.0.1.0-ProductiononThuMar2221:46:422018
  4.  
  5. Copyright(c)1982,Oracleand/oritsaffiliates.Allrightsreserved.
  6.  
  7. connectedtotargetdatabase:HMDG(DBID=787192145)
  8. connectedtoauxiliarydatabase:HMDG(notmounted)


2. 将主数据库复制到备用数据库


在RMAN模式下，发出以下语句将主数据库复制到备数据库

  1. DUPLICATETARGETDATABASEFORSTANDBYFROMACTIVEDATABASE;


[oracle@hmdb11dg-db2 dbs]$ rman TARGET sys/oracle@HMDG AUXILIARY sys/oracle@HMDG2

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 23 09:50:19 2018

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

connected to target database: HMDG (DBID=787214401)

connected to auxiliary database: HMDG (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

Starting Duplicate Db at 23-MAR-18

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=4708 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2' ;

}

executing Memory Script

Starting backup at 23-MAR-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3773 device type=DISK

Finished backup at 23-MAR-18

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/HMDG2/control01.ctl';

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl' from

'/u01/app/oracle/oradata/HMDG2/control01.ctl';

}

executing Memory Script

Starting backup at 23-MAR-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_HMDG.f tag=TAG20180323T095223 RECID=3 STAMP=971517146

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15

Finished backup at 23-MAR-18

Starting restore at 23-MAR-18

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 23-MAR-18

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/HMDG2/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/HMDG2/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/HMDG2/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/HMDG2/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/HMDG2/users01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/HMDG2/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/HMDG2/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/HMDG2/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/HMDG2/users01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HMDG2/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-MAR-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/HMDG/undotbs01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf tag=TAG20180323T095246

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:01:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/HMDG/system01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/system01.dbf tag=TAG20180323T095246

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/HMDG/sysaux01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf tag=TAG20180323T095246

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/HMDG/users01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/users01.dbf tag=TAG20180323T095246

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15

Finished backup at 23-MAR-18

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=3 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=971517319 file name=/u01/app/oracle/oradata/HMDG2/users01.dbf

Finished Duplicate Db at 23-MAR-18


如果复制过程中没有报错，就可以立即开启日志重做应用


3. 开启日志重做应用

发出如下命令指示备库开始使用备用日志进行在备库上同步数据(恢复数据)

  1. sql>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEDISCONNECTFROMSESSION;
  2. #或者
  3. sql>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION;
  4.  
  5. #取消申请重做(该命令用于停止恢复)
  6. sql>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;


4.测试备用数据的存档操作

默认情况下，当在线重做日志文件变满时，会发送日志切换。要强制进行日志切换以便立即传输重做数据，则在主数据库上使用如下语句强制日志切换

  1. sql>ALTERSYSTEMSWITCHLOGFILE;
  2.  
  3. Systemaltered.


5.在备用数据库上查询现有的归档重做日志文件

  1. sql>SELECTSEQUENCE#,FIRST_TIME,NEXT_TIMEFROMV$ARCHIVED_logoRDERBYSEQUENCE#;
  2.  
  3. SEQUENCE#FIRST_TIMNEXT_TIME
  4. ----------------------------
  5. 1923-MAR-1823-MAR-18
  6. 2023-MAR-1823-MAR-18
  7. 2123-MAR-1823-MAR-18
  8. 2223-MAR-1823-MAR-18


6.再次在数据库上强制日志切换

  1. sql>ALTERSYSTEMSWITCHLOGFILE;
  2.  
  3. Systemaltered.


7.验证在备用数据库中是否接收了新的重做日志

  1. sql>SELECTSEQUENCE#,NEXT_TIMEFROMV$ARCHIVED_logoRDERBYSEQUENCE#;
  2.  
  3. SEQUENCE#FIRST_TIMNEXT_TIME
  4. ----------------------------
  5. 1923-MAR-1823-MAR-18
  6. 2023-MAR-1823-MAR-18
  7. 2123-MAR-1823-MAR-18
  8. 2223-MAR-1823-MAR-18
  9. 2323-MAR-1823-MAR-18


8. 验证备用数据库是否应用了新的重做日志

  1. sql>SELECTSEQUENCE#,APPLIEDFROMV$ARCHIVED_logoRDERBYSEQUENCE#;
  2.  
  3. SEQUENCE#APPLIED
  4. -------------------
  5. 19YES
  6. 20YES
  7. 21YES
  8. 22YES
  9. 23YES


9.查询主备状态

  1. #在当前主数据库中查询
  2. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  3.  
  4. SWITCHOVER_STATUS
  5. --------------------
  6. TOSTANDBY
  7.  
  8. #在当前备用数据库中查询
  9. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  10.  
  11. SWITCHOVER_STATUS
  12. --------------------
  13. NOTALLOWED

此时，说明当前主备数据库状态都为正常。其中主数据状态TO STANDBY说明可以随时切换为备用数据库。


五、手动切换测试(主备切换)


在主数据库(DB1)上操作


在当前主数据库中查询主备状态

  1. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  2.  
  3. SWITCHOVER_STATUS
  4. --------------------
  5. TOSTANDBY

当主数据库的状态为TO STANDBY时，表示可以切换到备用数据库


在当前主数据库(DB1)上发出切换到备数据库角色申请

  1. sql>ALTERDATABASECOMMITTOSWITCHOVERTOPHYSICALSTANDBYWITHSESSIONSHUTDOWN;
  2.  
  3. Databasealtered.



在备数据库(DB2)上操作


此时查看备数据库(DB2)的状态

  1. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  2.  
  3. SWITCHOVER_STATUS
  4. --------------------
  5. TOPRIMARY

当此时备数据库的状态为TO PRIMARY时，表示可以切换到主数据库角色


在当前备数据库(DB2)上发出切换到主数据库角色申请

  1. sql>ALTERDATABASECOMMITTOSWITCHOVERTOPRIMARYWITHSESSIONSHUTDOWN;
  2.  
  3. Databasealtered.


继续在备数据(DB2)上完成以下指令

  1. sql>ALTERDATABASEOPEN;
  2. --或者
  3. sql>SHUTDOWNIMMEDIATE;
  4. sql>STARTUP;


在原来的主数据(DB1)上继续执行以下命令

  1. sql>SHUTDOWNIMMEDIATE;
  2. ORA-01507:databasenotmounted
  3.  
  4.  
  5. ORACLEinstanceshutdown.
  6. sql>STARTUPNOMOUNT;
  7. ORACLEinstancestarted.
  8.  
  9. TotalSystemGlobalArea3273641984bytes
  10. FixedSize2217792bytes
  11. VariableSize2264926400bytes
  12. DatabaseBuffers989855744bytes
  13. RedoBuffers16642048bytes
  14. sql>ALTERDATABASEMOUNTSTANDBYDATABASE;
  15.  
  16. Databasealtered.

发出如下命令让当前备库(DB1)开始使用备用日志进行同步数据(恢复数据)

  1. sql>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION;
  2.  
  3. Databasealtered.


最后查看切换以后的主备状态与主备角色


  1. --DB2上
  2. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  3.  
  4. SWITCHOVER_STATUS
  5. --------------------
  6. TOSTANDBY
  7.  
  8. sql>selectdatabase_rolefromv$database;
  9.  
  10. DATABASE_ROLE
  11. ----------------
  12. PHYSICALSTANDBY
  13.  
  14. --DB1上
  15. sql>SELECTSWITCHOVER_STATUSFROMV$DATABASE;
  16.  
  17. SWITCHOVER_STATUS
  18. --------------------
  19. NOTALLOWED
  20.  
  21. sql>selectdatabase_rolefromv$database;
  22.  
  23. DATABASE_ROLE
  24. ----------------
  25. PRIMARY

以上状态说明主备角色已经正常切换


注意：在正常切换后，主数据库状态可能需要等待几分钟时间才显示为TO STANDBY状态，这是因为主数据在执行日志切换，需要等待切换完成


在当前主数据库(DB2)上强制日志切换

  1. sql>ALTERSYSTEMSWITCHLOGFILE;
  2.  
  3. Systemaltered.

至此主备切换测试完成

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问