logical standby是在physical standby基礎上搭建
Oracle10g Physical DataGuard 詳細搭建過程
--停止standby上的日志應用
SQL> alter database recover managed standby database cancel;
Database altered.
--修改主庫log_archive_dest_3參數,當切換為備庫時在線日志歸檔路徑
SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/dbserver/arch valid_for=(standby_logfiles,standby_role) db_unique_name=dbserver';
System altered.
SQL> alter system set log_archive_dest_state_3=enable;
System altered.
--主庫執行,alert.log顯示Logminer Bld: Done
SQL> execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
--修改備庫log_archive_dest_3參數,備庫在線日志歸檔路徑
SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/standby/arch valid_for=(standby_logfiles,standby_role) db_unique_name=standby';
System altered.
SQL> alter system set log_archive_dest_state_3=enable;
System altered.
--如果備庫沒有臨時表空間需要創建
--在standby端將物理備庫轉為邏輯備庫,最後一個standby是邏輯備庫名稱
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> startup mount force
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 2085872 bytes
Variable Size 167775248 bytes
Database Buffers 436207616 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database recover to logical standby standby;
Database altered.
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> startup mount force
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 2085872 bytes
Variable Size 167775248 bytes
Database Buffers 436207616 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
STANDBY
--在主庫switch logfile
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
--standby alert.log
RFS[3]: Assigned to RFS process 10806
RFS[3]: Identified database type as 'logical standby'
Mon Mar 3 13:10:28 2014
RFS LogMiner: Client enabled and ready for notification
RFS[3]: Successfully opened standby log 6: '/u01/app/oracle/oradata/standby/standby03.log'
Mon Mar 3 13:10:28 2014
RFS LogMiner: Client enabled and ready for notification
Mon Mar 3 13:11:21 2014
RFS LogMiner: Client enabled and ready for notification
Mon Mar 3 13:11:22 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/standby/standby02.log'
Mon Mar 3 13:11:33 2014
RFS LogMiner: Client enabled and ready for notification
Mon Mar 3 13:11:34 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/standby/standby02.log'
--在備庫啟用實時應用
SQL> alter database start logical standby apply immediate;
Database altered.
--接下來就是測試了，在主庫插入數據在備庫查看是否同步，以下是備庫的alert.log
RFS LogMiner: Registered logfile [/u01/app/oracle/oradata/standby/arch/ARC0000000136_0839199018.0001] to LogMiner session id [1]
LOGMINER: session# = 1, reader process P000 started with pid=40 OS id=10844
LOGMINER: session# = 1, builder process P001 started with pid=41 OS id=10846
LOGMINER: session# = 1, preparer process P002 started with pid=42 OS id=10848
Mon Mar 3 13:17:08 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 136, /u01/app/oracle/oradata/standby/arch/ARC0000000136_0839199018.0001
Mon Mar 3 13:17:08 2014
LOGMINER: Turning ON Log Auto Delete
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000136_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 137, /u01/app/oracle/oradata/standby/arch/ARC0000000137_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000137_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 138, /u01/app/oracle/oradata/standby/arch/ARC0000000138_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000138_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 139, /u01/app/oracle/oradata/standby/arch/ARC0000000139_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000139_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 140, /u01/app/oracle/oradata/standby/arch/ARC0000000140_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000140_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 141, /u01/app/oracle/oradata/standby/arch/ARC0000000141_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000141_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 142, /u01/app/oracle/oradata/standby/arch/ARC0000000142_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000142_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 143, /u01/app/oracle/oradata/standby/arch/ARC0000000143_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000143_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 144, /u01/app/oracle/oradata/standby/arch/ARC0000000144_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000144_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 145, /u01/app/oracle/oradata/standby/arch/ARC0000000145_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000145_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 146, /u01/app/oracle/oradata/standby/arch/ARC0000000146_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000146_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 147, /u01/app/oracle/oradata/standby/arch/ARC0000000147_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000147_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 148, /u01/app/oracle/oradata/standby/arch/ARC0000000148_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000148_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 149, /u01/app/oracle/oradata/standby/arch/ARC0000000149_0839199018.0001
Mon Mar 3 13:17:14 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000149_0839199018.0001
LNS1 started with pid=43, OS id=10850
Mon Mar 3 13:17:17 2014
Thread 1 advanced to log sequence 2 (LGWR switch)
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/standby/redo02.log
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable
Mon Mar 3 13:17:19 2014
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable
Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
LSP2 started with pid=44, OS id=10853
Mon Mar 3 13:17:26 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 136, /u01/app/oracle/oradata/standby/arch/ARC0000000136_0839199018.0001
Mon Mar 3 13:17:26 2014
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P003 started with pid=45 OS id=10855
LOGSTDBY Apply process P006 started with pid=48 OS id=10861
LOGSTDBY Apply process P008 started with pid=50 OS id=10865
LOGSTDBY Apply process P007 started with pid=49 OS id=10863
LOGSTDBY Apply process P004 started with pid=46 OS id=10857
LOGSTDBY Apply process P005 started with pid=47 OS id=10859
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000136_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 137, /u01/app/oracle/oradata/standby/arch/ARC0000000137_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000137_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 138, /u01/app/oracle/oradata/standby/arch/ARC0000000138_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000138_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 139, /u01/app/oracle/oradata/standby/arch/ARC0000000139_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000139_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 140, /u01/app/oracle/oradata/standby/arch/ARC0000000140_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000140_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 141, /u01/app/oracle/oradata/standby/arch/ARC0000000141_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000141_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 142, /u01/app/oracle/oradata/standby/arch/ARC0000000142_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000142_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 143, /u01/app/oracle/oradata/standby/arch/ARC0000000143_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000143_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 144, /u01/app/oracle/oradata/standby/arch/ARC0000000144_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000144_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 145, /u01/app/oracle/oradata/standby/arch/ARC0000000145_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000145_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 146, /u01/app/oracle/oradata/standby/arch/ARC0000000146_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000146_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 147, /u01/app/oracle/oradata/standby/arch/ARC0000000147_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000147_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 148, /u01/app/oracle/oradata/standby/arch/ARC0000000148_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000148_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 149, /u01/app/oracle/oradata/standby/arch/ARC0000000149_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/standby/arch/ARC0000000149_0839199018.0001
Mon Mar 3 13:17:27 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 150, /u01/app/oracle/oradata/standby/standby03.log
Mon Mar 3 13:18:05 2014
RFS LogMiner: RFS id [10797] assigned as thread [1] PING handler
