DataGuard 日常操作-02
Dataguard採用最大效能模式.
第一部分 日常維護
一 正確開啟主庫和備庫
1
主庫:
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
2
備庫:
SQL> startup mount
SQL>
alter database recover managed standby database disconnect from
session;
OR =>> alter database recover managed standby database using current logfile disconnect from session;
二 正確關閉順序
1
備庫:
SQL> alter database recover managed standby database cancel;
SQL>
shutdown immediate;
2
主庫
SQL>shutdown immediate;
三 備庫Read-Only模式開啟
當前主庫正常OPEN狀態
備庫處於日誌傳送狀態.
1
在備庫停止日誌傳送
SQL>
recover managed standby database cancel;
2
備庫Read-only模式開啟
SQL>
alter database open read only;
3
備庫回到日誌傳送模式
SQL>
recover managed standby database disconnect from session;
Media recovery
complete.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
四 日誌傳送狀態監控
1
主庫察看當前日誌狀況
SQL>
select sequence#,status from v$log;
SEQUENCE#
STATUS
---------- ----------------
51 ACTIVE
52 CURRENT
50
INACTIVE
2
備庫察看RFS(Remote
File Service)接收日誌情況和MRP應用日誌同步主庫情況
SQL>
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM
V$MANAGED_STANDBY;
PROCESS
STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ----------
---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0
0 0
RFS RECEIVING 0 0 0 0
MRP0 WAIT_FOR_LOG 1 52 0 0
RFS RECEIVING 0 0
0 0
可以看到備庫MPR0正等待SEQUENCE#為52的redo.
3
察看備庫是否和主庫同步
SQL>
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM
V$ARCHIVE_DEST_STATUS;
ARCHIVED_THREAD#
ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- -------------
--------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0
0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 51 1
50
可以看到備庫已經將SEQUENCE#51的日誌歸檔,已經將SEQUENCE#50的redo應用到備庫.
由於已經將SEQUENCE#51的日誌歸檔,所以SEQUENCE#51以前的資料不會丟失.
4
察看備庫已經歸檔的redo
SQL>
select registrar, creator, thread#, sequence#, first_change#, next_change# from
v$archived_log;
REGISTR
CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------- -------
---------- ---------- ------------- ------------
SRMN SRMN 1 37 572907
573346
RFS ARCH 1 38 573346 573538
RFS ARCH 1 39 573538 573623
RFS ARCH
1 40 573623 573627
RFS ARCH 1 41 573627 574326
RFS ARCH 1 42 574326
574480
RFS
ARCH 1 49 601476 601532
RFS ARCH 1 50 601532 606932
RFS ARCH 1 51 606932
607256
5
察看備庫已經應用的redo
SQL>
select thread#, sequence#, first_change#, next_change# from
v$log_history;
THREAD#
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------
------------
1 1 366852 368222
1 2 368222 369590
1 3 369590 371071
1
4 371071 372388
1 5 372388 376781
1 6 376781 397744
1 7 397744
407738
1 8 407738 413035
1 9 413035 413037
1
50 601532 606932
1 51 606932 607256
可以看到備庫已經將SEQUENCE#為51的歸檔檔案應用到備庫.
6
察看備庫接收,應用redo資料過程.
SQL>
select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------
ARC0:
Archival started
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no
SRL' ARCH
ARC1: Archival started
ARC1: Becoming the heartbeat ARCH
Redo
Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]:
Assigned to RFS process 19740
RFS[1]: Identified database type as 'physical
standby'
Primary database is in MAXIMUM PERFORMANCE mode
Attempt to start
background Managed Standby Recovery process
MESSAGE
--------------------------------------------------------------------------------
MRP0:
Background Managed Standby Recovery process started
Managed Standby Recovery
not using Real Time Apply
Clearing online redo logfile 7
/oraguard/redo1/redo_7_1.log
Clearing online redo logfile 7 complete
Media
Recovery Waiting for thread 1 sequence 47
RFS[1]: No standby redo logfiles
created
Redo Shipping Client Connected as PUBLIC
-- Connected User is
Valid
RFS[2]: Assigned to RFS process 19746
RFS[2]: Identified database
type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE
mode
MESSAGE
--------------------------------------------------------------------------------
Committing
creation of archivelog '/arch/1_47_552308270.arc'
Media Recovery Log
/arch/1_47_552308270.arc
Media Recovery Waiting for thread 1 sequence
48
MRP0: Background Media Recovery cancelled with status 16037
MRP0:
Background Media Recovery process shutdown
Managed Standby Recovery
Canceled
Attempt to start background Managed Standby Recovery
process
MRP0: Background Managed Standby Recovery process started
Managed
Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread
1 sequence 48
RFS[1]: No standby redo logfiles created
MESSAGE
--------------------------------------------------------------------------------
Committing
creation of archivelog '/arch/1_48_552308270.arc'
Media Recovery Log
/arch/1_48_552308270.arc
Media Recovery Waiting for thread 1 sequence
49
RFS[1]: No standby redo logfiles created
Committing creation of
archivelog '/arch/1_49_552308270.arc'
Media Recovery Log
/arch/1_49_552308270.arc
Media Recovery Waiting for thread 1 sequence
50
RFS[1]: No standby redo logfiles created
Committing creation of
archivelog '/arch/1_50_552308270.arc'
Media Recovery Log
/arch/1_50_552308270.arc
Media Recovery Waiting for thread 1 sequence
51
MESSAGE
--------------------------------------------------------------------------------
RFS[1]:
No standby redo logfiles created
Committing creation of archivelog
'/arch/1_51_552308270.arc'
Media Recovery Log
/arch/1_51_552308270.arc
Media Recovery Waiting for thread 1 sequence
52
可以看到RFS接收到sequence#為51的歸檔檔案並存至備庫歸檔目錄/arch/1_51_552308270.arc.
Oracle自動應用檔案/arch/1_51_552308270.arc進行備庫與主庫同步
Oracle繼續等待主庫sequence
52的歸檔檔案
五 備庫歸檔目錄維護
1 找到備庫歸檔目錄
SQL>
show parameter log_archive_dest_1
NAME
TYPE
------------------------------------
--------------------------------
VALUE
------------------------------
log_archive_dest_1
string
LOCATION=/arch
VALID_FOR=(ALL_LOGFILES,ALL_RO
LES)
DB_UNIQUE_NAME=ora2
log_archive_dest_10
string
2
維護策略
每週2,4,7刪除已經應用的歸檔檔案
具體參見附錄二
第二部分 主庫正常切換
一 人工干預主庫正常切換
1
在主庫端檢驗資料庫可切換狀態
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
SWITCHOVER_STATUS:TO STANDBY表示可以正常切換.
如果SWITCHOVER_STATUS的值為SESSIONS
ACTIVE,表示當前有會話處於ACTIVE狀態
2
開始主庫正常切換
如果SWITCHOVER_STATUS的值為TO
STANDBY 則:
SQL>
alter database commit to switchover to physical
standby;
如果SWITCHOVER_STATUS的值為SESSIONS
ACTIVE 則:
SQL>
alter database commit to switchover to physical standby with session
shutdown;
成功執行這個命令後，主庫被修改為備庫
3
重啟先前的主庫
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
4
在備庫驗證可切換狀態
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
5
將目標備庫轉換為主庫
如果SWITCHOVER_STATUS的值為TO
STANDBY 則:
SQL>
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
如果SWITCHOVER_STATUS的值為SESSIONS
ACTIVE 則:
SQL>
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION
SHUTDOWN;
成功執行這個命令後，備庫被修改為主庫
6
重啟目標備庫
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
7
先前主庫啟動日誌傳送程式
SQL> alter database recover managed standby database
disconnect;
總結: 這樣主庫的一次正常切換完成.切換後的狀態,原先的主庫變為備庫,原先的備庫變為主庫.
二 透過執行指令碼實現主庫正常切換
1
主庫切換為備庫
在主庫上執行指令碼
/admin/dataGuard/switchover/primary_to_standby.sh
2
備庫切換為主庫
在備庫上執行指令碼
/admin/dataGuard/switchover/standby_to_primary.sh
指令碼1成功執行後,再執行指令碼2,不能同時執行兩個指令碼.
經過這次切換後原來的主庫變為備庫,原先的備庫變為主資料並且OPEN對應用提供服務.
3
復原最初狀態
在原備庫上執行指令碼
/admin/dataGuard/switchover/primary_to_standby.sh
成功完成後
在原主庫上執行指令碼
/admin/dataGuard/switchover/standby_to_primary.sh
第三部分 主庫災難切換
一 人工干預主庫災難切換
二 透過執行指令碼實現主庫災難切換
SQL>alter
database recover managed standby database cancel;
SQL>shutdown
immediate
SQL>startup mount
SQL>alter database set standby database
to maximize performance;
SQL>alter database recover managed standby
database finish;
-- switch
SQL>alter database commit to switchover to
primary with session shutdown;
-- open
SQL>shutdown
immediate
SQL>startup
附:
一 有選擇察看redo傳送與應用情況
select
message from v$dataguard_status
where
message_num>&message_num;
二 備庫歸檔目錄維護指令碼
在crontab
中定製每日執行removeCommand.sh即可。
流程:每日11:50PM執行removeCommand.sh
假設今日2005-04-05
則刪除04-04和04-03兩日已應用歸檔日誌.保留今日已應用歸檔日誌
[oracle@db_gurid
admin]$ crontab -l
50 23 * * * sh
/oraguard/admin/removeCommand.sh>>removeArch.log
##################
[oracle@db_gurid
admin]$ cat removeCommand.sh
#!/bin/sh
export
ORACLE_BASE=/ora10g/app
export
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export
ORACLE_SID=ora2
cd
/oraguard/admin
$ORACLE_HOME/bin/sqlplus /nolog<
@/oraguard/admin/removeArch.sql
EOF
chmod
+x
/oraguard/admin/removeArch.sh
/oraguard/admin/removeArch.sh>>removeArch2.log
##################
[oracle@db_gurid
admin]$ cat removeArch.sql
set feed off
set heading off
set echo
off
spool removeArch.sh
select 'rm '||name from v$archived_log where
applied='YES' and completion_time>trunc(sysdate-3) and
completion_time
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22578826/viewspace-711526/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DataGuard 日常維護命令整理
- 日常操作
- Dataguard日常維護及故障解決
- Mac 日常操作Mac
- Git 日常操作Git
- Vagrant (二) - 日常操作
- MongoBD 日常操作小節Go
- Redis Cluster日常操作命令梳理Redis
- Redis日常操作命令小結Redis
- dataguard 常規運維操作運維
- Oracle Dataguard基本操作語句Oracle
- Linux 日常操作命令集合 -1程式操作Linux
- php5.3日常操作PHP
- mongodb 的一些日常操作MongoDB
- oracle表空間日常操作管理Oracle
- MongoDB日常運維操作命令小結MongoDB運維
- oracle日常操作彙總:日誌篇Oracle
- Oracle Dataguard故障轉移(failover)操作OracleAI
- Docker容器學習梳理 - 日常操作總結Docker
- kvm虛擬機器日常操作命令梳理虛擬機
- Dataguard 維護操作-01-- MRP process
- MongoDB日常運維-02安裝MongoDB運維
- VERITA備份日常監控及相關命令操作
- Postgresql日常運維-安裝(Windows)02SQL運維Windows
- 日常電腦操作小技能篇（生活無處不精彩）
- Linux運維筆記-日常操作命令總結（2）Linux運維筆記
- Linux運維筆記-日常操作命令總結（3）Linux運維筆記
- Linux運維筆記-日常操作命令總結（1）Linux運維筆記
- [Sqlite] Sqlite的基本日常SQL操作語句彙總SQLite
- Redis日常運維-02主從複製Redis運維
- Oracle12c日常pdb建立、開關和刪除操作Oracle
- 資料庫日常運維中的幾個操作建議資料庫運維
- ORACLE 11G 搭建dataguard詳細步驟（所有操作總結）Oracle
- dataguard中需要注意的一些資料檔案操作
- 【Dataguard】DataGuard運維注意事項運維
- 全網最全，講解最細的ps日常操作小技巧，Photoshop教程
- 10g dataguard ORA-00313 rename備用日誌操作
- DataGuard broker之一：DataGuard broker簡介