ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > cp数据文件方式搭建 11g 物理standby

cp数据文件方式搭建 11g 物理standby

原创 Oracle 作者：哎呀我的天呐 时间：2015-06-03 12:04:31 0 删除 编辑



1.主库设置强制归档

点击( 此处 )折叠或打开

  1. SQL > alter database force logging ;


  3. SQL > select force_logging from v$database ;


  5. FOR

  6. - - -

  7. YES


2.创建口令文件并拷贝到备库

点击( 此处 )折叠或打开

  1. orapwd file = $ ORACLE_HOME/dbs/orapwbeijing password = oracle entries = 3

  2. scp orapwbeijing ...


  4. 备库上重命名一下

  5. mv orapwbeijing orapwshanghai


3.主库确定为归档模式

点击( 此处 )折叠或打开

  1. archive log list ;

  2. shutdown immediate ;

  3. startup mount ;

  4. alter database archivelog ;

  5. alter system set log_archvie_dest_1 =' location = / arch/' scope = spfile;

4.主备库上添加监听和Oracle Net Service(tnsnames.ora)

主库tnsnames.ora

点击( 此处 )折叠或打开

  1. sh =

  2.    ( DESCRIPTION =

  3.      ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192.168.6.31 ) ( PORT = 9521 ) )

  4.      ( CONNECT_DATA =

  5.        ( SERVER = DEDICATED )

  6.        ( SERVICE_NAME = shanghai )

  7.      )

  8.    )

备库tnsnames.ora

点击( 此处 )折叠或打开

  1. bj =

  2.    ( DESCRIPTION =

  3.      ( ADDRESS_LIST =

  4.        ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192 . 168 . 6 . 21 ) ( PORT = 9521 ) )

  5.      )

  6.      ( CONNECT_DATA =

  7.        ( SERVICE_NAME = beijing )

  8.      )

  9.    )


  11. sh =

  12.    ( DESCRIPTION =

  13.      ( ADDRESS_LIST =

  14.        ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192 . 168 . 6 . 31 ) ( PORT = 9521 ) )

  15.      )

  16.      ( CONNECT_DATA =

  17.        ( SERVICE_NAME = shanghai )

  18.      )

  19.    )

5.主库上创建standby Control file

点击( 此处 )折叠或打开

  1. alter database create standby controlfile as '/u01/control01.ctl' ;


  3. Database altered .

6.修改主备库参数文件
主库pfile

点击( 此处 )折叠或打开

  1. beijing . __db_cache_size = 314572800

  2. beijing . __java_pool_size = 4194304

  3. beijing . __large_pool_size = 4194304

  4. beijing . __oracle_base = '/u01/app/oracle' # ORACLE_BASE set from environment

  5. beijing . __pga_aggregate_target = 339738624

  6. beijing . __sga_target = 503316480

  7. beijing . __shared_io_pool_size =

  8. beijing . __shared_pool_size = 167772160

  9. beijing . __streams_pool_size = 4194304

  10. * . audit_file_dest = '/u01/app/oracle/admin/beijing/adump'

  11. * . audit_trail = 'db'

  12. * . compatible = '11.2.0.0.0'

  13. * . control_files = '/u01/app/oracle/oradata/beijing/control01.ctl' , '/u01/app/oracle/fast_recovery_area/beijing/control02.ctl'

  14. * . db_block_size = 8192

  15. * . db_domain = ''

  16. * . db_name = 'beijing'

  17. * . db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'

  18. * . db_recovery_file_dest_size = 4294967296

  19. * . diagnostic_dest = '/u01/app/oracle'

  20. * . dispatchers = '(PROTOCOL=TCP) (SERVICE=beijingXDB)'

  21. # * . local_listener = 'LISTENER_BEIJING'

  22. * . memory_target = 842006528

  23. * . open_cursors = 300

  24. * . processes = 200

  25. * . remote_login_passwordfile = 'EXCLUSIVE'

  26. * . sessions = 225

  27. * . undo_tablespace = 'UNDOTBS1'


  29. # add for primary_dg

  30. * . db_unique_name = 'beijing'

  31. * . log_archive_config = 'dg_config=(beijing,shanghai)'

  32. * . log_archive_dest_1 = 'location=/arch valid_for=(all_logfiles,all_roles) db_unique_name=beijing'

  33. * . log_archive_dest_2 = 'service=sh reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=shanghai'

  34. * . log_archive_dest_state_1 = enable

  35. * . log_archive_dest_state_2 = enable

  36. * . standby_file_management = 'auto'

  37. * . fal_server = 'shanghai'

  38. * . db_file_name_convert = '/u01/app/oracle/oradata/shanghai' , '/u01/app/oracle/oradata/bejing'

  39. * . log_file_name_convert = '/u01/app/oracle/oradata/shanghai' , '/u01/app/oracle/oradata/beijing'

备库pfile

点击( 此处 )折叠或打开

  1. [ oracle@standby dbs ] $ cat initshanghai . ora

  2. shanghai . __db_cache_size = 314572800

  3. shanghai . __java_pool_size = 4194304

  4. shanghai . __large_pool_size = 4194304

  5. shanghai . __oracle_base = '/u01/app/oracle\' # ORACLE_BASE set from environment

  6. shanghai . __pga_aggregate_target = 339738624

  7. shanghai . __sga_target = 503316480

  8. shanghai . __shared_io_pool_size =

  9. shanghai . __shared_pool_size = 167772160

  10. shanghai . __streams_pool_size = 4194304

  11. * . audit_file_dest = \ '/u01/app/oracle/admin/shanghai/adump\'

  12. * . audit_trail = \ 'db\'

  13. * . compatible = \ '11.2.0.0.0\'

  14. * . control_files = '/u01/app/oracle/oradata/shanghai/control01.ctl' , '/u01/app/oracle/fast_recovery_area/shanghai/control02.ctl\'

  15. * . db_block_size = 8192

  16. * . db_domain = ''

  17. * . db_name = 'beijing'

  18. * . db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'

  19. * . db_recovery_file_dest_size = 4294967296

  20. * . diagnostic_dest = '/u01/app/oracle\'

  21. * . dispatchers = '(PROTOCOL=TCP) (SERVICE=shanghaiXDB)'

  22. # * . local_listener = 'LISTENER_SHANGHAI'

  23. * . memory_target = 842006528

  24. * . open_cursors = 300

  25. * . processes = 200

  26. * . remote_login_passwordfile = 'EXCLUSIVE'

  27. * . sessions = 225

  28. * . undo_tablespace = 'UNDOTBS1'


  30. # add for primary_dg

  31. * . db_unique_name = 'shanghai'

  32. * . log_archive_config = 'dg_config=(shanghai,beijing)'

  33. * . log_archive_dest_1 = 'location=/arch valid_for=(all_logfiles,all_roles) db_unique_name=shanghai'

  34. * . log_archive_dest_2 = 'service=sh reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=shanghai'

  35. * . log_archive_dest_state_1 = enable

  36. * . log_archive_dest_state_2 = enable

  37. * . fal_server = 'beijing'

  38. * . db_file_name_convert = '/u01/app/oracle/oradata/beijing' , '/u01/app/oracle/oradata/shanghai'

  39. * . log_file_name_convert = '/u01/app/oracle/oradata/beijing' , '/u01/app/oracle/oradata/shanghai'

  40. * . standby_file_management = 'auto'

7.将主备库open并检查DG是否有错误

点击( 此处 )折叠或打开

  1. SQL > startup pfile = '/u01/app/oracle/product/11.2.0/db_1/dbs/initshanghai.ora' ;

  2. ORACLE instance started .


  4. Total System Global Area  839282688 bytes

  5. Fixed Size             2233000 bytes

  6. Variable Size           520097112 bytes

  7. Database Buffers      314572800 bytes

  8. Redo Buffers            2379776 bytes

  9. Database mounted .

  10. Database opened .

  11. SQL > select open_mode from v$database ;


  13. OPEN_MODE

  14. - - - - - - - - - - - - - - - - - - - -

  15. READ ONLY

检查DG是否有错误

点击( 此处 )折叠或打开

  1. select dest_name , status , process , error , transmit_mode from v$archive_dest where target = 'STANDBY'


  3. DEST_NAME              STATUS     PROCESS     ERROR    TRANSMIT_MODE

  4. - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---

  5. LOG_ARCHIVE_DEST_2           VALID      LGWR                 ASYNCHRONOUS

8.主备库上添加standby redo log

点击( 此处 )折叠或打开

  1. alter database add standby logfile group 4 ( '/u01/app/oracle/oradata/beijing/redo04.log' ) size 100M ;

  2. alter database add standby logfile group 5 ( '/u01/app/oracle/oradata/beijing/redo05.log' ) size 100M ;

  3. alter database add standby logfile group 6 ( '/u01/app/oracle/oradata/beijing/redo06.log' ) size 100M ;

  4. alter database add standby logfile group 7 ( '/u01/app/oracle/oradata/beijing/redo07.log' ) size 100M ;


  6. SQL > col member for a60

  7. SQL > select group# , type , member from v$logfile ;


  9. GROUP# TYPE     MEMBER

  10. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  11.      3 ONLINE  /u01/app/oracle/oradata/beijing/redo03 . log

  12.      2 ONLINE  /u01/app/oracle/oradata/beijing/redo02 . log

  13.      1 ONLINE  /u01/app/oracle/oradata/beijing/redo01 . log

  14.      4 STANDBY /u01/app/oracle/oradata/beijing/redo04 . log

  15.      5 STANDBY /u01/app/oracle/oradata/beijing/redo05 . log

  16.      6 STANDBY /u01/app/oracle/oradata/beijing/redo06 . log

  17.      7 STANDBY /u01/app/oracle/oradata/beijing/redo07 . log


  19. 7 rows selected .

备库同上

9.备库启动MRP进程

点击( 此处 )折叠或打开

  1. alter database recover managed standby database disconnect from session;

点击( 此处 )折叠或打开

  1. SQL > select open_mode from v$database ;


  3. OPEN_MODE

  4. - - - - - - - - - - - - - - - - - - - -

  5. READ ONLY WITH APPLY


点击( 此处 )折叠或打开

  1. 1 * select * from v$managed_standby


  3. PROCESS  PID STATUS CLIENT_PROCESS CLIENT_PID CLIENT_DBID     GROUP#   RESETLOG_ID    THREAD#  SEQUENCE#   BLOCK#     BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

  4. - - - - - - -   - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - -   - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  5. ARCH   11245 CLOSING       ARCH       11245       845781194        5           881314188      1          10        1       6        0           0         0

  6. ARCH   11247 CLOSING       ARCH       11247       845781194        5           881314188      1           8        1   926        0           0         0

  7. ARCH   11249 CONNECTED      ARCH       11249       845781194        N/A           0      0           0        0       0        0           0         0

  8. ARCH   11251 CLOSING       ARCH       11251       845781194        4           881314188      1           9        1       2        0           0         0

  9. RFS    12009 IDLE          ARCH       28837       845781194        N/A           0      0           0        0       0        0           0         0

  10. RFS    12003 IDLE       UNKNOWN     28835       845781194        N/A           0      0           0        0       0        0           0         0

  11. RFS    12005 IDLE       UNKNOWN     28839       845781194        N/A           0      0           0        0       0        0           0         0

  12. RFS    12007 IDLE          LGWR       28841       845781194        2           881314188      1          11       33       1        0           0         0

  13. MRP0   12058 WAIT_FOR_LOG   N/A       N/A             N/A     N/A   881314188      1          11        0       0        0           0         0






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

0
0
上一篇： ORA-01678:
下一篇： RMAN不停机搭建DG
请登录后发表评论 登录
全部评论
哎呀我的天呐
专家姓名：姚崇
https://cloud.tencent.com/developer/inventory/14362

注册时间：2014-10-30

  • 博文量
    403
  • 访问量
    2528746

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593