DG常用命令集
----標準DataGuard引數設定------------------------------
alter system set
log_archive_dest_2='SERVICE=ta_std REOPEN=300
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=ta_std';
alter system set fal_server='ta_std';
alter system set fal_client='ta';
alter system set fal_server='ta';
alter system set fal_client='ta_std';
----利用ssh tunnel的DataGuard引數設定------------------------------
alter
system set log_archive_dest_2 = 'SERVICE=ta_ssh NOREOPEN
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=ta_std
alternate=log_archive_dest_3';
alter system set log_archive_dest_state_2= enable;
--注意:log_archive_dest_2需要設定noreopen或者reopen=0屬性,否則無法迅速切換到備用路徑。
alter system set log_archive_dest_3 = 'SERVICE=ta_std REOPEN=300
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=ta_std';
alter system set log_archive_dest_state_3=alternate;
--注意:當歸檔目標log_archive_dest_2不能正常歸檔時,資料庫切換歸檔目標到log_archive_dest_3,即使log_archive_dest_2歸檔目標恢復正常,也不會再自動切換回去,需要手工重新修改路徑狀態引數:
--alter system set log_archive_dest_state_2= enable;
--alter system set log_archive_dest_state_3= alternate;
alter system set fal_server = 'ta_ssh';
alter system set fal_client = 'ta';
測試
tnsping ta tnsing ta_std tnsing ta_ssh
------------------------------------------------------
p170a: ssh -L 15210:p170b:1521 -C -N p170b
p170b: ssh -L 15210:p170a:1521 -C -N p170a
redhat1: ssh -L 15210:redhat2:1521 -C -N redhat2
redhat2: ssh -L 15210:redhat1:1521 -C -N redhat1
----DataGuard 中註冊日誌檔案------------------------------
alter database register OR replace logfile '/data/arch_ta/1_515_673519034.dbf';
註冊日誌檔案
catalog archivelog '....';
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
SELECT TIMESTAMP,MESSAGE FROM V$DATAGUARD_STATUS;
SELECT * FROM (SELECT THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY FIRST_CHANGE# DESC) WHERE ROWNUM<20;
一、PRIMARY DATABASE
1、設定DB FORCE_LOGGING為YES
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE NO FORCE LOGGING;
2、設定保護模式
SELECT OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
3、設定歸檔目標
ALTER SYSTEM SET log_archive_dest_1='location=/app/oracle/arch';
ALTER SYSTEM set LOG_ARCHIVE_DEST_2='SERVICE=ta_std REOPEN=300 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)';
ALTER SYSTEM SET log_archive_dest_state_1=enable;
ALTER SYSTEM SET log_archive_dest_state_2=defer;
ALTER SYSTEM SET fal_server=ta_std;
ALTER SYSTEM SET fal_client=fmp;
ALTER SYSTEM set DB_FILE_NAME_CONVERT='fmpdb','fmpdb' SCOPE=SPFILE;
ALTER SYSTEM set LOG_FILE_NAME_CONVERT='fmpdb','fmpdb' SCOPE=SPFILE;
ALTER SYSTEM SET standby_file_management=auto;
二、STANDBY DATABASE
1、建目錄結構
D:\oracle\product\10.2.0\admin
D:\oracle\product\10.2.0\flash_recovery_area
D:\oracle\product\10.2.0\oradata\fmpdb
D:\oracle\product\10.2.0\oradata\fmpdbarch
2、copy
D:\oracle\product\10.2.0\db_1\database\PWDfmpdb.ora
D:\oracle\product\10.2.0\db_1\database\SPFILEFMPDB.ORA
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora 需修改IP
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
C:\> ORADIM -NEW -sid fmpdb
C:\> orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDfmpdb.ora password=oracle force=y
--password必須與PRIMARY保持一致
starup nomount
ALTER SYSTEM SET log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\fmpdbarch';
ALTER SYSTEM set LOG_ARCHIVE_DEST_2='SERVICE= REOPEN=300 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)';
--ALTER SYSTEM reset LOG_ARCHIVE_DEST_2 scope=spfile sid='*';
ALTER SYSTEM SET log_archive_dest_state_1=enable;
ALTER SYSTEM SET log_archive_dest_state_1=defer;
ALTER SYSTEM SET fal_server=fmp;
ALTER SYSTEM SET fal_client=fmp_std;
三、PRIMARY DATABASE
RMAN 備份:
rman target /
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
BACKUP format 'D:\oracle\product\10.2.0\oradata\backup\%d_STBY_%T_%s_%p' current controlfile for standby;
BACKUP format 'D:\oracle\product\10.2.0\oradata\backup\%d_FULL_%T_%s_%p' database MAXSETSIZE 2G;
SQL 'alter system archive log current';
BACKUP format 'D:\oracle\product\10.2.0\oradata\backup\%d_ARCH_%T_%s_%p' archivelog all MAXSETSIZE 2G;
exit;
四、STANDBY DATABASE
1、copy 主D:\oracle\product\10.2.0\oradata\backup\*.*至備
2、
RMAN target
connect auxiliary
duplicate target database for standby nofilenamecheck;
[dorecover];
五、PRIMARY DATABASE
ALTER SYSTEM SET log_archive_dest_state_2=enable;
六、STANDBY DATABASE
startup mount
ALTER SYSTEM SET log_archive_dest_state_2=enable;
--啟動了MRP0程式(Managed Standby Recovery)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
七、檢查
--查詢尚未APPLIED的archived log(STANDBY端)
SELECT * FROM
(SELECT THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED FROM
V$ARCHIVED_LOG ORDER BY FIRST_CHANGE# DESC) WHERE ROWNUM<20;
--察看從資料庫已經歸檔的redo
SELECT REGISTRAR,CREATOR,THREAD#,SEQUENCE#,APPLIED,FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;
--察看從資料庫已經應用的redo
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
--DATAGUARD 狀態
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
SELECT TIMESTAMP,MESSAGE FROM V$DATAGUARD_STATUS;
--從資料庫端察看RFS(Remote File Service)接收日誌情況和MRP應用日誌同步主資料庫情況
COL STATUS FORMAT A15
SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;
--察看從資料庫是否和主資料庫同步
COL DEST_NAME FORMAT A20
SELECT DEST_NAME,ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
col DESTINATION format a50
SELECT DEST_NAME,DESTINATION, STATUS,
ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS
<> 'DEFERRED' AND STATUS <> 'INACTIVE';
--GAP
SELECT * FROM V$ARCHIVE_GAP;
第二部分 主資料庫正常切換
一 人工干預主資料庫正常切換
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 開始主資料庫正常切換 IP:172.16.75.35
如果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 在從資料庫端驗證可切換狀態 IP:172.16.75.30
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
5 將目標從資料庫轉換為主資料庫 IP:172.16.75.30
如果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;
============================================================================
--ALTER SYSTEM set log_archive_config='DG_CONFIG=(testdb,testdb2)';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\10.2.0\oradata\boston\CONTROL01.CTL';
--啟動了MRP0程式(Managed Standby Recovery)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SET PAGESIZE 200
SELECT * FROM V$PROCESS;
SELECT * FROM V$BGPROCESS WHERE PADDR<>'00';
SELECT USERNAME,TERMINAL,PROGRAM FROM V$PROCESS;
SELECT * FROM V$BGPROCESS WHERE PADDR<>'00';
COL USERNAME FORMAT A15
COL MACHINE FORMAT A30
SELECT USERNAME,MACHINE,TERMINAL,PROGRAM FROM V$SESSION;
-------
rman nocatalog target
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK CONNECT format '/app/oracle/backup/rac1_%U';
ALLOCATE CHANNEL c2 DEVICE TYPE DISK CONNECT format '/app/oracle/backup/rac2_%U';
sql 'alter system archive log current';
backup filesperset 3 archivelog all delete all input;
release channel c1;
release channel c2;
}
rman nocatalog target
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK CONNECT ;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK CONNECT ;
BACKUP FORMAT '/app/oracle/backup/%t_%U' DATABASE PLUS ARCHIVELOG;
BACKUP FORMAT '/app/oracle/backup/%t_%U' CURRENT CONTROLFILE FOR STANDBY;
release channel c1;
release channel c2;
}
-----RAC環境下,交叉驗證-----------------------------------------
allocate channel for maintenance device type DISK connect ;
allocate channel for maintenance device type DISK connect ;
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
release channel;
-------------------------------------------------------------------------
RMAN>
catalog archivelog '/arch/ora92/2_1.dbf';
list archivelog all;
scp -p -r oracle@192.168.192.71:/app/oracle/backup /app/oracle
scp -p -r oracle@192.168.192.72:/app/oracle/backup /app/oracle
orapwd file=/app/oracle/product/10.2.0/db_1/dbs/orapwta password=oracle force=y
ALTER SYSTEM set DB_FILE_NAME_CONVERT='ta','ta' SCOPE=SPFILE;
ALTER SYSTEM set LOG_FILE_NAME_CONVERT='ta','ta' SCOPE=SPFILE;
rman target
connect auxiliary
duplicate target database for standby nofilenamecheck;
--primary
ALTER SYSTEM SET log_archive_config='dg_config=(ta,ta_std)' sid='*';
ALTER SYSTEM SET log_archive_dest_1='location=/app/oracle/arch' sid='*';
ALTER
SYSTEM set LOG_ARCHIVE_DEST_2='SERVICE=ta_std REOPEN=300
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=ta_std'
sid='*';
ALTER SYSTEM SET log_archive_dest_state_1=enable sid='*';
ALTER SYSTEM SET log_archive_dest_state_2=defer sid='*';
ALTER SYSTEM SET standby_file_management=auto sid='*'
--standby
ALTER SYSTEM SET log_archive_config='dg_config=(ta,ta_std)';
ALTER SYSTEM SET standby_file_management=auto;
ALTER SYSTEM SET fal_server='ta1','ta2';
ALTER SYSTEM SET fal_client='ta_std';
ALTER SYSTEM SET log_archive_dest_state_2=enable sid='*';
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
recover managed standby database finish;
透過故障切換實現角色轉換
1、查詢備用資料中v$archive_gap檢視來定位備用資料庫中的日誌缺失。
2、在備用資料庫中透過下面的命令對歸檔日誌進行註冊
alter database register physical logfile '......';
3、alter database recover managed standby database finish;
alter database recover managed standby database finish skip standby logfile;
4、alter database commit to switchover to primary;
5、shutdown immediate;
startup;
fmpdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.2)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DAfmpdb =
(SERVICE_NAME = fmpdb)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
fmpdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.1)(PORT = 1521))
)
(CONNECT_DAfmpdb =
(SERVICE_NAME = fmpdb)
)
)
fmpdb2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.2)(PORT = 1521))
)
(CONNECT_DAfmpdb =
(SERVICE_NAME = fmpdb)
)
)
fmpdb_std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.0.11)(PORT = 1521))
(CONNECT_DAfmpdb =
(SERVER = DEDICATED)
(SERVICE_NAME = fmpdb)
)
)
-------------------------------------------------------------------------------
http://hi.baidu.com/dbaeyes/blog/item/740c3bfb3e0d5361034f568e.html
standby 管理和維護2008年07月13日 星期日 00:31管理:
查詢庫狀態:
SQL> select name,SWITCHOVER_STATUS,open_mode,PROTECTION_MODE,DATABASE_ROLE
from v$database;
驗證archive log是否接收和 applied.
SQL> alter session set nls_datE_format='YYYY-MM-DD HH24:MI:SS';
select SEQUENCE#,DEST_ID,ARCHIVED,APPLIED,DELETED,
STATUS,FIRST_TIME from v$archived_log order by SEQUENCE#;
啟動到管理模式:
SQL> shutdown immediate;
SQL> startup nomount
SQL> alter database mount standby database
SQL> recover managed standby database disconnect from session;
----------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
10g以後,採用聯機日誌的傳遞傳遞方式,
SQL> recover managed standby database using current logfile disconnect from session;
啟動到只讀模式:
SQL> shutdown immediate;
SQL> startup nomount
SQL> alter database mount standby database
SQL> alter databae open read only;
在管理模式恢復到只讀模式:
SQL> recover managed standby database cancel;
SQL> alter database open read only
在只讀模式可以給資料庫新增臨時資料檔案(在rman備份是沒有備份的),想讓standby提供只讀服務或者切換成主庫,最好先增加鏈式檔案
SQL> alter tablespace temp add tempfile 'C:\oracle\product\10.2.0\oradata\dgtest\temp02.dbf' size 100m;
只讀模式到管理模式
SQL> recover managed standby database disconnect from session;
在備庫進行備份
1.停止應用程式,跳轉到 read only模式,同坐backup database命令來備份資料庫,這樣資料庫處於一致性模式
2. 備份完成後,備份控制檔案
SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'FILE NAME';
主備切換
正常切換 (switchover)
切換準備: 準備引數檔案,平時應該備好,注意引數 fal_server,fal_client
先確認能否轉換:
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
主 -> 備
$lsnrclt stop
殺光程式或者重啟資料庫
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown immediate;
SQL> startup nomount pfile ='';
SQL> alter database mount standby database;
SQL> recover managed standby database disconnect;
備 -> 主
SQL> alter database commit to switchover to primary with session shutdown;
SQL> shutdown immediate
SQL> startup pfile =''
如果考慮在主備庫來回切換,要保證資料庫版本一致,引數compatible一致
失敗切換 (Failover)
主伺服器不能使用情況下
在備庫
SQL> recover managed standby database cancel;
--如果在備用庫上有備用庫日誌檔案,參考命令
SQL> alter database recover managed standby database finish; -- [force|wait|nowait] 10g or later
--沒有備庫日誌檔案[10gR2之前]
SQL> alter database recover managed standby database finish skip standby logfile;
--切換備庫到主庫
SQL> alter database commit to switchover to primary;
-- open
SQL> shutdown immediate;
SQL> startup pfile=''
如果還有為傳遞的歸檔日誌或者主庫的聯機日誌
SQL>alter database register logfile 'c:\...'
SQL>recover standby database;
強行切換(啟用)
啟用備用伺服器,在重啟資料庫時,備庫會resetlog。
SQL>alter system archive log current;
SQL>recover managed standby database cancel;
SQL>alter database activate standby database;
SQL>shutdown immediate;
SQL>startup pfile=''
-----
apply 主庫redolog並強制切換
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--選定redolog恢復
SQL> recover standby database until cancel;
Specify log: {
/u01/oradata/dgtest/redo02.log
Log applied.
Media recovery complete.
recovery 完成之後就要failover了。
SQL>alter database activate standby database;
SQL>shutdown immediate;
SQL>startup pfile=''
-- http://hi.baidu.com/wa0362/blog/item/33e52912a91eadcbc2fd78b9.html
-- http://zf_wu.itpub.net/
-- 參考來源: 《oracle高可用環境》
--http://www.oracleblog.cn/study-note/reduce-the-data-loss-of-data-guard-when-failover/
相關檢視
v$archive_dest
v$archive_dest_status
v$log_history
v$archvied_log
v$managed_standby
v$archive_gap
-- EOF --
啟用備庫到read/write狀態
SQL> alter database activate standby database;
資料庫已更改。
SQL> alter database open;
資料庫已更改。
轉換成備庫
SQL> alter database convert to physical standby;
資料庫已更改。
--------->>轉載於:http://www.cnitblog.com/lichanggu/articles/62976.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1285048/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 常用crs命令集
- Mac 常用命令集合Mac
- VI常用命令集合
- 常用指令碼命令集(轉)指令碼
- Linux 常用命令集合Linux
- AIX常用命令集錦AI
- CentOS7常用命令集合CentOS
- Oracle DG運維常用SQLOracle運維SQL
- ORACLE DG 日常維護常用SQLOracleSQL
- Aircrack-ng工具集常用命令集合AI
- redis常用資料型別操作命令集錦Redis資料型別
- 【Linux】CentOS7 常用命令集合LinuxCentOS
- 【附錄】 sqlplus 常用命令集SQL
- oracle dg 維護常用操作和調優Oracle
- Wifite工具常用命令集合大學霸IT達人WiFi
- 10. Oracle常用高可用方案——10.1. DGOracle
- hacmp命令集ACM
- 【Bat命令集】BAT
- Oracle命令集Oracle
- Git命令集合Git
- ARP監控工具ARPalert常用命令集合大學霸IT達人
- 批次探測工具fpingping常用命令集合大學霸IT達人
- Linux命令集合Linux
- Git命令集錦Git
- windows命令集(轉)Windows
- SSL_TLS快速掃描器SSLScan常用命令集合大學霸IT達人TLS
- 【DG】DG概念原理詳解
- 無線認證資訊抓包工具hcxdumptool常用命令集合大學霸IT達人
- Wifitap是一個WiFi注入工具集常用命令集合大學霸IT達人WiFi
- 無線密碼離線破解工具Pyrit常用命令集合大學霸IT達人密碼
- DB2資料庫常用命令集:部分資料庫維護命令DB2資料庫
- mysql 命令集(轉貼)MySql
- 物理DG、邏輯DG和快照DG的搭建(視訊講解)
- ARP探測目標工具arping常用命令集合大學霸IT達人
- 【DG】DG的3種保護模式模式
- 【DG】物理DG中LNSn、NSS、NSA程式
- DG搭建
- Oracle RAC維護命令集Oracle