環境：
主庫：192.168.160.2
物理備庫：192.168.160.128
邏輯備庫：192.168.160.129
實驗思路：在160.2上配置db，隨後在128和129上建立2個一樣的物理備庫，隨後將129上的物理備份轉換成邏輯備庫。
操作步驟：
0. 在3個虛擬機器裡安裝redhat 6.2，並分別建立資料庫。
1. 配置監聽，以達到主庫和2個備庫都可以通訊。
2. 主庫改force logging，add SUPPLEMENTAL LOG
SQL> alter database force logging;
SQL> alter database add SUPPLEMENTAL LOG data;
3. 配置兩邊引數檔案
不同的地方（主庫）：
*.db_unique_name='dong_pri'
*.service_names='dong_pri'
*.log_archive_config='DG_CONFIG=(dong_pri,mm_stb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dong_pri'
*.LOG_ARCHIVE_DEST_2='SERVICE=mm_stb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_3='SERVICE=mm_stb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_DEST_STATE_3=ENABLE
*.FAL_SERVER=mm_stb
*.FAL_CLIENT=dong_pri
*.STANDBY_FILE_MANAGEMENT=AUTO
備註：主庫是需要寫LOG_ARCHIVE_DEST3個的，而2個備庫只需要寫自己的就行。
不同的地方（物理備庫128）：
*.db_unique_name='mm_stb'
*.service_names='mm_stb'
*.log_archive_config='DG_CONFIG=(mm_stb,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb
*.STANDBY_FILE_MANAGEMENT=AUTO
不同的地方（物理備庫129（一會要改為邏輯備庫））：
*.db_unique_name='mm_stb2'
*.service_names='mm_stb2'
*.log_archive_config='DG_CONFIG=(mm_stb2,dong_pri)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/diag/rdbms/dong/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mm_stb2'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.FAL_SERVER=dong_pri
*.FAL_CLIENT=mm_stb2
*.STANDBY_FILE_MANAGEMENT=AUTO
4. 主庫建立備庫控制檔案
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/mmstb.ctl';
5. 主庫增加備庫日誌檔案(此處主要是為了以後切換到備庫時使用)
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dong/std_redo04.log') SIZE 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dong/std_redo05.log') SIZE 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dong/std_redo06.log') SIZE 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dong/std_redo07.log') SIZE 50M;
6. 將主庫shutdown，並將檔案copy到物理備庫和邏輯備庫的伺服器上
此處為尋求方便，我將所有資料檔案、主備的控制檔案、主備的日誌檔案和密碼檔案、主的引數檔案都 copy到從伺服器上了
7. 啟動主庫和備庫
主startup open pfile='$ORACLE_HOME/dbs/initdong.ora';
物理備startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';
邏輯備startup mount pfile='$ORACLE_HOME/dbs/initdong.ora';---這裡我們先要將它搭建成物理備庫
8. 將2個物理備庫啟動到應用日誌狀態
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9. 測試物理備庫是否可以用
10.將mm_stb2（129，第二個物理備庫）轉成邏輯備庫
10.1 129停止物理備庫的應用redo
SQL> alter database recover managed standby database cancel;
Database altered.
10.2 主庫生成LogMiner字典資訊
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 81
Current log sequence 81
備註：做完DBMS_LOGSTDBY.BUILD不用切換日誌。
10.3 轉換物理standby為邏輯standby
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 79
Next log sequence to archive 0
Current log sequence 81
SQL> select name,dest_id,STATUS,APPLIED,ARCHIVED from v$archived_log;
NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_76_833209595.dbf
1 A YES YES

/u01/app/oracle/diag/rdbms/dong/1_77_833209595.dbf
1 A YES YES

/u01/app/oracle/diag/rdbms/dong/1_78_833209595.dbf
1 A NO YES

NAME
--------------------------------------------------------------------------------
DEST_ID S APPLIED ARC
---------- - --------- ---
/u01/app/oracle/diag/rdbms/dong/1_79_833209595.dbf
1 A NO YES

/u01/app/oracle/diag/rdbms/dong/1_80_833209595.dbf
1 A NO YES
備註：78-80這幾個日誌已經傳到備庫129這邊來，但並沒有應用，因為這裡我們已經取消了日誌的應用了。
SQL> alter database recover to logical standby donglog; --這裡donglog是129這個備庫的新的DB_NAME
Database altered.
SQL> archive log list
ORA-01507: database not mounted
SQL> !ps -ef |grep smon
oracle 3530 1 0 01:30 ? 00:00:00 ora_smon_dong
oracle 3905 3875 1 02:01 pts/6 00:00:00 /bin/bash -c ps -ef |grep smon
oracle 3907 3905 0 02:01 pts/6 00:00:00 grep smon

SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 213911964 bytes
Database Buffers 197132288 bytes
Redo Buffers 6103040 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string DONGLOG
db_unique_name string mm_stb2
global_names boolean FALSE
instance_name string dong
lock_name_space string
log_file_name_convert string
service_names string mm_stb2
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
10.4 修改引數檔案
邏輯備庫：
SQL> alter system set LOG_ARCHIVE_DEST_4='LOCATION=/u01/app/oracle/diag/rdbms/dong/lgc_dong/ VALID_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=mm_stb2';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_4='ENABLE';
備註：這裡只需要修改邏輯備庫的引數檔案即可。
10.5 開啟備庫到open
SQL> alter database open resetlogs;
Database altered.
10.6 備庫應用redo
SQL> alter database start logical standby apply immediate;
Database altered.
10.7 驗證
主庫：
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PRIMARY READ WRITE MAXIMUM PERFORMANCE NONE
128物理備庫：
SQL> SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;

NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONG PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE ALL
129邏輯備庫：
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
--------- ---------------- -------------------- -------------------- -------
DONGLOG LOGICAL STANDBY READ WRITE MAXIMUM PERFORMANCE ALL
主庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 103
Next log sequence to archive 105
Current log sequence 105
物理備庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 58
Next log sequence to archive 0
Current log sequence 105
邏輯備庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/lgc_dong/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
備註:注意這裡邏輯備庫的目錄lgc_dong是邏輯備庫自己的歸檔目錄，下面的current log sequence也是自己的。而邏輯備庫接受主庫的歸檔是放在/u01/app/oracle/diag/rdbms/dong下的。
11. 測試邏輯備庫
對於物理備庫，我們可以在主庫上建議一個表並切換日誌，隨後將備庫拉起到readonly狀態，檢視是否可以發現。而邏輯備庫來說就簡簡單單多了，由於邏輯備庫一直都是open狀態的，所以備庫切換完日誌後，備庫直接查詢即可，以下為邏輯備庫的測試。
主庫：
SQL> insert into mao.t_mao select * from mao.t_mao;
2 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from mao.t_mao;
ID
----------
1
1
1
1
邏輯備庫：
SQL> select * from mao.t_mao;

ID
----------
1
1
1
1
12. 小知識點（查詢）：
邏輯備庫：
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;---檢視日誌應用情況
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
79 1541405 1542804 06-JAN-14 YES
80 1542804 1542820 06-JAN-14 YES
81 1542820 1543693 06-JAN-14 YES
82 1543693 1544457 06-JAN-14 YES
83 1544457 1544492 06-JAN-14 YES
84 1544492 1544539 06-JAN-14 YES
85 1544539 1545119 06-JAN-14 YES
86 1545119 1548402 06-JAN-14 YES
87 1548402 1551318 06-JAN-14 YES
88 1551318 1554336 06-JAN-14 YES
89 1554336 1557214 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
90 1557214 1560147 06-JAN-14 YES
91 1560147 1563171 06-JAN-14 YES
92 1563171 1565585 06-JAN-14 YES
93 1565585 1585835 06-JAN-14 YES
94 1585835 1585844 06-JAN-14 YES
95 1585844 1586161 06-JAN-14 YES
96 1586161 1586631 06-JAN-14 YES
97 1586631 1586983 06-JAN-14 YES
98 1586983 1587322 06-JAN-14 YES
99 1587322 1587665 06-JAN-14 YES
100 1587665 1588188 06-JAN-14 YES
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
101 1588188 1589321 06-JAN-14 YES
102 1589321 1593152 06-JAN-14 YES
103 1593152 1593219 06-JAN-14 YES
104 1593219 1594249 06-JAN-14 YES
26 rows selected.
備註：這裡需要為YES才可以，如果為NO的話，需檢視是否開啟日誌應用。
SQL> select session_id,state from v$logstdby_state; --檢視日誌應用狀態
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 IDLE
備註：這裡的狀態有很多種，如果是APPLYING代表正在應用日誌，如果是IDLE的話，表示已經開啟應用，但目前沒有新的日誌需要應用。
SQL> select event_time,status,event from dba_logstdby_events order by event_timestamp;-----檢視報錯
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 Apply LWM 1545390, HWM 1545390
, SCN 1565584
06-JAN-14 ORA-16226: DDL skipped due to ALTER DATABASE OPEN
lack of support
06-JAN-14 ORA-16226: DDL skipped due to alter database guard none
lack of support
EVENT_TIME STATUS EVENT
------------------------------ ------------------------------ ------------------------------
06-JAN-14 ORA-16226: DDL skipped due to alter database add SUPPLEMENTA
lack of support L LOG data
SQL> select THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS,FIRST_TIME,LAST_TIME from v$standby_log;--檢視邏輯standbylog
THREAD# SEQUENCE# USED ARC STATUS FIRST_TIM LAST_TIME
---------- ---------- ---------- --- ------------------------------ --------- ---------
1 0 512 NO UNASSIGNED
1 105 341504 YES ACTIVE 06-JAN-14 06-JAN-14
1 0 512 NO UNASSIGNED
0 0 512 YES UNASSIGNED
SQL> select * from v$archive_gap;-------檢視是否有沒有應用的日誌
no rows selected
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;-----檢視各程式狀態
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 68 CLOSING
ARCH ARCH 60 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 69 WAIT_FOR_LOG
RFS LGWR 69 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
9 rows selected.

實驗中所遇到的問題：
1. 關於alter database guard none和alter database guard all
答：等同於alter system disable|enable guard，用於允許或禁止使用者修改邏輯standby 中的結構。對於主庫來說，那肯定要設定為none的，對於邏輯備庫來說，如果你不想讓備庫的結果被修改，那就設定為all，我的實驗中，邏輯備庫是ALL的。在ALL下如果執行dml或者ddl操作則會丟擲ORA-16224: Database Guard is enabled報錯。解決辦法只需要改為disable guard即可。
2. 在執行物理備庫像邏輯備庫切換時命令hang住
答：在執行alter database recover to logical standby donglog時hang住，半天沒有反應，最後發現正確的順序是備庫先取消應用redo，然後主庫生成LogMiner字典資訊，主庫不需要執行切換日誌，最後備庫執行 alter database recover to logical standby donglog。
3. 密碼檔案問題
答：由於3個庫的密碼一樣，都是oracle，所以我就在3個庫上分別執行了orapwd命令來生成密碼檔案，而且也可以透過網路連結對方庫成功。但再傳輸日誌時卻發現警告日誌裡報錯說Heartbeat失敗，於是我將主庫的密碼檔案分別scp到2個備庫，最後問題解決。

配置物理備庫+邏輯備庫

相關文章