oracle data guard搭建

DataGuard服務

日志傳輸

控制redo資料傳輸到一個或多個歸檔位置

日志應用

應用redo資料到standby 資料庫，以保持對primary資料庫事務一緻

角色轉換

primary standby

switchover

failover --有可能丢失資料 ------------------------DataGuard 搭建--------------------

primary standy

hostname： node1.oracle.com node1.oracle.com

instancename souid staid

db_unique_name souid staid

db_name souid souid file_nmae /oracle/oradata/souid /oracle/oradata/staid

archive /home/oracle/arch /home/oracle/arch

1. 準備工作

[[email protected] ~]# vi /etc/sysctl.conf

kernel.shmmax = 18294967295 //調大

[[email protected] ~]# sysctl -p

2. 更改參數（主庫）

SQL> alter system set db_unique_name=souid scope=spfile;

SQL> alter database force logging;

SQL> alter database flashback on; 前提開歸檔

SQL> alter system set log_archive_config='DG_CONFIG=(souid,staid)';

SQL> alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=souid';

SQL> alter system set log_archive_dest_2='service=staid async valid_for=(online_logfiles,primary_role) db_unique_name=staid';

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30;

SQL> alter system set fal_server=staid scope=spfile; //庫出現故障後，staid切換成主庫

SQL> alter system set fal_client=souid scope=spfile; //庫出現故障後，souid切換成備庫

SQL> alter system set db_file_name_convert='/oracle/oradata/staid','/oracle/oradata/souid' scope=spfile;

SQL> alter system set log_file_name_convert='/oracle/oradata/staid','/oracle/oradata/souid' scope=spfile;

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

SQL> alter system set local_listener=''; //不使用特殊監聽

SQL> alter database add standby logfile;

SQL> alter system set dg_broker_start=true

2. 更改參數（備庫）

SQL> create pfile='/home/oracle/initstaid.ora' from spfile;

更改後的内容如下

staid.__db_cache_size=12582912

staid.__java_pool_size=4194304

staid.__large_pool_size=4194304

staid.__oracle_base='/oracle'#ORACLE_BASE set from environment

staid.__pga_aggregate_target=104857600

staid.__sga_target=318767104

staid.__shared_io_pool_size=0

staid.__shared_pool_size=79691776

staid.__streams_pool_size=209715200

*.aq_tm_processes=2

*.audit_file_dest='/oracle/admin/staid/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oracle/oradata/staid/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/oracle/oradata/souid','/oracle/oradata/staid'

*.db_name='souid'

*.db_recovery_file_dest='/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.db_unique_name='STAID'

*.dg_broker_start=TRUE

*.diagnostic_dest='/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=staidXDB)'

*.fal_client='STAID'

*.fal_server='SOUID'

*.global_names=TRUE

*.job_queue_processes=10

*.local_listener=''

*.log_archive_config='DG_CONFIG=(staid,souid)'

*.log_archive_dest_1='location=/home/oracle/arch1 valid_for=(all_logfiles,all_roles) db_unique_name=staid'

*.log_archive_dest_2='service=souid async valid_for=(online_logfiles,primary_role) db_unique_name=souid'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='/oracle/oradata/souid','/oracle/oradata/staid'

*.open_cursors=300

*.parallel_max_servers=20

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=629145600

*.sga_target=0

*.shared_pool_reserved_size=0

*.standby_file_management='AUTO'

*.streams_pool_size=209715200

*.undo_retention=3600

*.undo_tablespace='UNDOTBS1'

3. 配置監聽和tnsnames.ora

靜态監聽

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=souid)

(SID_NAME=souid)

(ORACLE_HOME=/oracle/database)

)

(SID_DESC=

(GLOBAL_DBNAME=staid)

(SID_NAME=staid)

(ORACLE_HOME=/oracle/database)

)

) LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

) ADR_BASE_LISTENER = /oracle

獨占方式

[[email protected] admin]$ vi tnsnames.ora

SOUID =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = souid)

)

) STAID =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = staid)

)

4. 密碼檔案

[[email protected] dbs]$ orapwd file=orapwstaid password=oracle ignorecase=y

5. 啟動備庫到mount狀态

SQL> startup nomount pfile='/home/oracle/initstaid.ora';

SQL> create spfile from pfile='/home/oracle/initstaid.ora';

SQL> shutdown immediate;

SQL> startup nomount

6. 克隆

[[email protected] admin]$ rman target sys/[email protected] auxiliary sys/[email protected]

RMAN> duplicate target database for standby nofilenamecheck from active database;

SQL> select status from v$instance;------------------備庫到mount狀态

SQL> alter database open;

SQL> select open_mode from v$database;------------------隻讀模式

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect；

RMAN> configure ARCHIVELOG DELETION POLICY to applied on all standby; 日志同步

----------------定義日志傳輸模式----------------

SQL> alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=souid';

sync: 同步傳輸。把主庫的操作寫入主庫的線上日志之前就傳送到standby log file。如果備庫使用real-time apply的話，在寫入standby log redo後就直接引用備庫。

如果備庫沒有啟用read-time apply，則備庫的arcn程序會等待主庫切換日志時把standby redo 也做一次歸檔（把歸檔應用到備庫上） async : 異步傳輸。是主庫在寫入主庫的線上日志，由LNSn程序發送給備庫。備庫的RFS程序負責接收資料并寫入到standby logfile，後續動作同上。

affrim ：指定所有redo log檔案和 standby redo log 必須同步

noaffrim ：指定所有redo log檔案和 standby redo log 不必須同步 default

1. 添加監聽 (SID_DESC=

(GLOBAL_DBNAME=souid_DGMGRL) ------------------給DGMGRL 添加特定靜态監聽，服務名格式固定

(SID_NAME=souid)

(ORACLE_HOME=/oracle/database)

)

(SID_DESC=

(GLOBAL_DBNAME=staid_DGMGRL) ------------------給DGMGRL 添加特定靜态監聽，服務名格式固定

(SID_NAME=staid)

(ORACLE_HOME=/oracle/database)

)

[[email protected] admin]$ vi tnsnames.ora

根據監聽檔案添加相應的内容show parameter dg_broker_start檢視

確定兩個參數都是開啟

dg_broker_start boolean TRUE 使用DG BROKER 來配置dataguard

[[email protected] admin]$ dgmgrl sys/[email protected]

DGMGRL> create configuration dgc1 as primary database is souid connect identifier is souid

DGMGRL> add database staid as connect identifier is staid;

DGMGRL> enable configuration

DGMGRL> show configuration;

Configuration - dgc1 Protection Mode: MaxPerformance //最大性能

Databases:

souid - Primary database

staid - Physical standby database Fast-Start Failover: DISABLED Configuration Status:

SUCCESS

DGMGRL> show configuration verbose;

DGMGRL> show database verbose souid;

DGMGRL> show database verbose staid;

---------------------------将最大性能改為最大保護----------------

最大保護模式

sync lgwr affrim

async arcn noaffrim

[[email protected] admin]$ dgmgrl sys/[email protected]

DGMGRL> edit database souid SET PROPERTY LogXptMode = 'SYNC'

DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxProtection;

主庫和備庫都要修改

DGMGRL> edit database staid SET PROPERTY LogXptMode = 'SYNC'

DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxProtection; ------------------日常維護-------------

啟動順序關閉順序

1. 監聽 1.監聽

2. 備庫 2.主庫

3. 主庫 3.備庫

SQL> startup

SQL> alter database recover managed standby database disconnect from session;

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select open_mode from v$database; OPEN_MODE

--------------------

READ ONLY WITH APPLY 關閉

主庫shutdown immediate SQL> alter database recover managed standby database cancel;

備庫停止日志傳送

SQL> recover managed standby database cancel;

SQL> alter database open read only;

備份回到日志傳送模式

SQL> recover managed standby database disconnect from session;

備庫RFS（remote file service）接收日志清空和MRP應用日志同步主備庫情況

檢視備庫中已經歸檔的日志

SQL> select SEQUENCE#,status from v$archived_log;

檢視備庫，應用redo資料過程

SQL> select message from v$dataguard_status;

檢視備庫中語句應用的redo

SQL> select * from v$log_history ;

---------------------------swichover-----------------

SQL> select database_role,switchover_status from v$database; 檢視主備庫

開始将主庫切換成備庫

SQL> alter database commit to switchover to physical standby with session shutdown;

關閉主庫

再将主庫打開到mount狀态

SQL> alter database recover managed standby database disconnect from session;

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect; 修改為備庫

備庫切換成主庫

SQL> alter database commit to switchover to primary with session shutdown ;(如果有會話連接配接)

關閉重新開機即可

如果用dg broker切換

DGMGRL> switchover to staid;

快照庫

DGMGRL> convert database souid to snapshot standby; //如果是最大保護模式，不允許備庫轉成快照庫

DGMGRL> edit CONFIGURATION SET PROTECTION MODE AS MaxPerformance; //改成最大性能模式，即可操作

做完操作之後，再将快照庫還原成備庫

DGMGRL> convert database souid to PHYSICAL STANDBY;

将快照庫還原成實體備庫，那之前的測試都将被删除

-------------------failover------------------

備庫

SQL> alter database recover managed standby database cancel; //停止redo傳輸應用

重新開機

SQL> alter database recover managed standby database finish; //完結

SQL> alter database commit to switchover to primary with session shutdown ; //切成正常庫

關閉重新開機即可

變為可以讀寫的庫

---------------------FSSO-----------------

故障快速轉移

1. 打開flashback on （備庫）

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on

SQL> alter database recover managed standby database disconnect;

2. 開啟功能

DGMGRL> enable fast_start failover;

重新開機configuration

DGMGRL> DISABLE FAST_START FAILOVER

DGMGRL> disable configuration;

DGMGRL> enable configuration;

DGMGRL> enable Fast-Start Failover;

DGMGRL> enable FAST_START FAILOVER ;

開啟 FSSO

DGMGRL> enable FAST_START FAILOVER ;

開啟并檢視終端

DGMGRL> start observer

主庫（異常關機）

SQL> shutdown abort;

檢視

DGMGRL> start observer

檢視備庫狀态

SQL> select open_mode,database_role from v$database ;

原主庫修複

原主庫修複後，将其啟動到mount狀态

再次檢視（這個啟動庫自動關聯到我們的DG環境當中，但是作為備庫存在）

DGMGRL> start observer

手工切換

DGMGRL> switchover to souid;

oracle data guard搭建

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List