dataguard之物理standby 日誌切換
物理standby 日常管理
對於最大效能模式（max performance）下，primary、standby庫的啟動順序無先後
1：dataguard啟動順序：先standby，後primary
2：dataguard關閉順序：先primary，後 standby
在standby庫上將例項啟動到mount狀態
[oracle@rac2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:09:27 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 71305172 bytes
Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
SQL>
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;//啟動redo apply
Database altered.
啟動standby庫的監聽
SQL> !lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2014 17:12:55
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /home/oracle/10.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/oracle/10.2.0/network/admin/listener.ora
Log messages written to /home/oracle/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 10-MAR-2014 17:12:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
The listener supports no services
The command completed successfully
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
在primary庫上啟動例項
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:15:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 75499476 bytes
Database Buffers 138412032 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
在primary 庫上啟動監聽
[oracle@rac1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2014 17:14:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /home/oracle/10.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/oracle/10.2.0/network/admin/listener.ora
Log messages written to /home/oracle/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 10-MAR-2014 17:14:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
The listener supports no services
The command completed successfully
驗證primary庫上的歸檔日誌是否正常傳輸到standby庫
在primary庫上執行
oracle@rac1 archivelog]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:25:55 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 05-MAR-14 05-MAR-14
2 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
5 05-MAR-14 06-MAR-14
5 05-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
7 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
7 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 06-MAR-14 06-MAR-14
13 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
18 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
18 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
21 06-MAR-14 10-MAR-14
21 06-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
24 10-MAR-14 10-MAR-14
24 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
48 rows selected.
SQL>
然後在primary庫上切換日誌
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
在standby庫上執行如下
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
5 05-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
7 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
18 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
21 06-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
24 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
26 10-MAR-14 10-MAR-14
27 10-MAR-14 10-MAR-14
28 10-MAR-14 10-MAR-14
27 rows selected.
可以看到primary庫上切換新的三組日誌已經傳到standby庫上了
[oracle@rac2 archivelog]$ ls -l
total 924
-rw-r----- 1 oracle dba 918528 Mar 10 17:32 log1_26_841430327.arc
-rw-r----- 1 oracle dba 1024 Mar 10 17:32 log1_27_841430327.arc
-rw-r----- 1 oracle dba 14848 Mar 10 17:32 log1_28_841430327.arc
[oracle@rac2 archivelog]$ pwd
/home/oracle/archivelog
[oracle@rac2 archivelog]$
如果碰到歸檔日誌無法傳輸到standby庫的情況，首先檢查alert.log
然後在 standby庫上先取消 redo apply，然後重新啟動redo apply
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
29
對於最大效能模式（max performance）下，primary、standby庫的啟動順序無先後
1：dataguard啟動順序：先standby，後primary
2：dataguard關閉順序：先primary，後 standby
在standby庫上將例項啟動到mount狀態
[oracle@rac2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:09:27 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 71305172 bytes
Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
SQL>
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;//啟動redo apply
Database altered.
啟動standby庫的監聽
SQL> !lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2014 17:12:55
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /home/oracle/10.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/oracle/10.2.0/network/admin/listener.ora
Log messages written to /home/oracle/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 10-MAR-2014 17:12:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
The listener supports no services
The command completed successfully
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
在primary庫上啟動例項
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:15:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 75499476 bytes
Database Buffers 138412032 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
在primary 庫上啟動監聽
[oracle@rac1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2014 17:14:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /home/oracle/10.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/oracle/10.2.0/network/admin/listener.ora
Log messages written to /home/oracle/10.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 10-MAR-2014 17:14:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
The listener supports no services
The command completed successfully
驗證primary庫上的歸檔日誌是否正常傳輸到standby庫
在primary庫上執行
oracle@rac1 archivelog]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 17:25:55 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 05-MAR-14 05-MAR-14
2 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
5 05-MAR-14 06-MAR-14
5 05-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
7 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
7 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 06-MAR-14 06-MAR-14
13 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
18 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
18 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
21 06-MAR-14 10-MAR-14
21 06-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
24 10-MAR-14 10-MAR-14
24 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
48 rows selected.
SQL>
然後在primary庫上切換日誌
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
在standby庫上執行如下
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 05-MAR-14 05-MAR-14
3 05-MAR-14 05-MAR-14
4 05-MAR-14 05-MAR-14
5 05-MAR-14 06-MAR-14
6 06-MAR-14 06-MAR-14
7 06-MAR-14 06-MAR-14
8 06-MAR-14 06-MAR-14
9 06-MAR-14 06-MAR-14
10 06-MAR-14 06-MAR-14
11 06-MAR-14 06-MAR-14
12 06-MAR-14 06-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 06-MAR-14 06-MAR-14
14 06-MAR-14 06-MAR-14
15 06-MAR-14 06-MAR-14
16 06-MAR-14 06-MAR-14
17 06-MAR-14 06-MAR-14
18 06-MAR-14 06-MAR-14
19 06-MAR-14 06-MAR-14
20 06-MAR-14 06-MAR-14
21 06-MAR-14 10-MAR-14
22 10-MAR-14 10-MAR-14
23 10-MAR-14 10-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
24 10-MAR-14 10-MAR-14
25 10-MAR-14 10-MAR-14
26 10-MAR-14 10-MAR-14
27 10-MAR-14 10-MAR-14
28 10-MAR-14 10-MAR-14
27 rows selected.
可以看到primary庫上切換新的三組日誌已經傳到standby庫上了
[oracle@rac2 archivelog]$ ls -l
total 924
-rw-r----- 1 oracle dba 918528 Mar 10 17:32 log1_26_841430327.arc
-rw-r----- 1 oracle dba 1024 Mar 10 17:32 log1_27_841430327.arc
-rw-r----- 1 oracle dba 14848 Mar 10 17:32 log1_28_841430327.arc
[oracle@rac2 archivelog]$ pwd
/home/oracle/archivelog
[oracle@rac2 archivelog]$
如果碰到歸檔日誌無法傳輸到standby庫的情況，首先檢查alert.log
然後在 standby庫上先取消 redo apply，然後重新啟動redo apply
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
29
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722274/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dataguard之物理standby庫failover 切換AI
- DataGuard---->物理StandBy的角色切換之switchover
- oracle 之dataguard standby 切換Oracle
- Dataguard物理Standby Switchover 角色轉換
- 【DataGuard】10g物理standby主備switchover方式切換詳述
- DATA GUARD物理STANDBY的 SWITCHOVER切換
- 【DATAGUARD】物理dg的switchover切換（五）
- 建立測試物理Standby日誌
- DataGuard搭建物理StandBy
- 物理Standby角色切換作業failoverAI
- DATA GUARD物理STANDBY的FAILOVER切換AI
- DATA GUARD物理STANDBY的 SWITCHOVER切換[zt]
- 【DATAGUARD】物理dg的failover切換(六)AI
- RAC環境的物理STANDBY的 SWITCHOVER切換
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- PostgreSQL切換日誌SQL
- 【DATAGUARD】Oracle Dataguard物理備庫切換最佳實踐(sqlplus)OracleSQL
- Oracle日誌模式切換Oracle模式
- 【DataGuard】物理Data Guard之Failover轉換AI
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- 物理dataguard 正常切換 角色轉換，switchover_status 狀態改變
- DATAGUARD_standby刪除歸檔日誌的指令碼指令碼
- REDO日誌切換頻率
- Oracle11g R2之Dataguard搭建物理standbyOracle
- oracle dataguard 切換Oracle
- 物理dataguard 正常切換 腳色轉換，switchover_status 狀態改變
- 盛哥學習 Data Guard 第三篇《物理standby之switchover 無損切換》
- oracle 9iDATA GUARD物理STANDBY的 SWITCHOVER切換步驟Oracle
- 物理DataGuard客戶端無縫切換--客戶端TAF 配置客戶端
- Oracle 10g DataGuard物理主備切換-switchover與failoverOracle 10gAI
- 一步一步學DataGuard(5)物理standby之建立示例
- 盛哥學習 Data Guard 第四篇《物理standby之failover 丟棄切換》AI
- oracle10g 物理standby dataguard 建立過程Oracle
- 【聽海日誌】之DATAGUARD新增redo log
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- GP standby切換成masterAST
- Oracle DataGuard切換步驟Oracle
- DataGuard切換保護模式模式