cp資料檔案方式搭建 11g 物理standby

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

2. scp orapwbeijing ...

3. 
   

4. 備庫上重新命名一下

5. mv orapwbeijing orapwshanghai
   

點選( 此處 )摺疊或開啟

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;

點選( 此處 )摺疊或開啟

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.    )

點選( 此處 )摺疊或開啟

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.    )
   

10. 
    

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.    )

點選( 此處 )摺疊或開啟

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

2. 
   

3. Database altered .

點選( 此處 )摺疊或開啟

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'
    

28. 
    

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'

點選( 此處 )摺疊或開啟

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'
    

29. 
    

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'

點選( 此處 )摺疊或開啟

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

2. ORACLE instance started .
   

3. 
   

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 ;
    

12. 
    

13. OPEN_MODE
    

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

15. READ ONLY

點選( 此處 )摺疊或開啟

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

2. 
   

3. DEST_NAME              STATUS     PROCESS     ERROR    TRANSMIT_MODE
   

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

5. LOG_ARCHIVE_DEST_2           VALID      LGWR                 ASYNCHRONOUS

點選( 此處 )摺疊或開啟

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 ;
   

5. 
   

6. SQL > col member for a60
   

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

8. 
   

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
    

18. 
    

19. 7 rows selected .

點選( 此處 )摺疊或開啟

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

點選( 此處 )摺疊或開啟

1. SQL > select open_mode from v$database ;
   

2. 
   

3. OPEN_MODE
   

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

5. READ ONLY WITH APPLY

點選( 此處 )摺疊或開啟

1. 1 * select * from v$managed_standby
   

2. 
   

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