1、oracle資料庫 DB的工作示意圖

2、oracle dg的三大工作模式及對資料庫的需求依賴

3、本次實驗的環境

4、oracle DG部署前的工作
在centdgpri機器上部署Oracle 11.2.0.4資料庫軟體並部署例項，在centdgstd機器上只安裝Oracle資料庫軟體，要求centdgpri和centdgstd機器上的oracle環境即資料庫相關的軟體部署路徑最好保持一致。

5、oracle DG的部署
主庫調整，開啟歸檔
archive log list;
shutdown immediate;
startup mount;
alter system set log_archive_dest_1='location=/opt/oracle/arch';
alter database archivelog;
archive log list;
alter database open;
alter system archive log current

主庫調整，開啟閃回

select force_logging, FLASHBACK_ON from v$database;
alter database force logging;
alter system set DB_RECOVERY_FILE_DEST_SIZE=10g;
alter system set db_recovery_file_dest='/opt/oracle/flash_recovery_area';
alter database FLASHBACK ON;
select force_logging, FLASHBACK_ON from v$database;

主庫調整，新增standby logfile日誌
set linesize 1000
col member for a50
select * from v$logfile order by 1;
select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
alter database add standby logfile group 6 ('/opt/oracle/oradata/redo06.log') size 50m;
alter database add standby logfile group 7 ('/opt/oracle/oradata/redo07.log') size 50m;
alter database add standby logfile group 8 ('/opt/oracle/oradata/redo08.log') size 50m;
alter database add standby logfile group 9 ('/opt/oracle/oradata/redo09.log') size 50m;
alter database add standby logfile group 10 ('/opt/oracle/oradata/redo10.log') size 50m;

主庫調整，修改資料庫啟動pfile檔案

orcl.__db_cache_size=327155712

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=469762048

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

orcl.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/opt/oracle/oradata/orcl/control01.ctl','/opt/oracle/oradata/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/opt/oracle'

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

*.log_archive_dest_1='location=/opt/arch'

*.memory_target=783286272

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME='orcl'

log_archive_config='DG_CONFIG=(orcl,orcls)'

log_archive_dest_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcls'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=dbstandby

FAL_CLIENT=dbprimary

STANDBY_FILE_MANAGEMENT=AUTO

*.log_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata'

*.db_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata'

主庫調整，重新生成spfile

startup pfile='$ORACLE_HOME/dbs/initorcl.ora';

create spfile from memory;

shutdown immediate;

startup;

show parameter spfile;

主庫調整，修改監聽配置檔案listener.ora

LISTENER =

(DESCRIPTION_LIST=

(DESCRIPTION =

(ADDRESS=(PROTOCOL=tcp)(HOST=centdgpri)(PORT=1521))

(ADDRESS=(PROTOCOL=ipc)(KEY= EXTPROC1521)))

)

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcl)

(SID_NAME=orcl)

(ORACLE_HOME=/opt/oracle/product/11.2.0.4/db)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl_DGMGRL)

(ORACLE_HOME = /opt/oracle/product/11.2.0.4/db)

(SID_NAME= orcl)

)

ADR_BASE_LISTENER = /opt/oracle

主庫調整，修改TNS服務配置檔案tnsnames.ora

DBPRIMARY=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

ORCL=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

DBSTANDBY=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

備庫調整，編輯資料庫啟動pfile

*.audit_file_dest='/opt/oracle/diag/rdbms/orcl/orcl/adump'

*.compatible='11.2.0.4.0'

*.control_files='/opt/oracle/oradata/control01.ctl','/opt/oracle/oradata/control02.ctl'

*.core_dump_dest='/opt/oracle/diag/rdbms/orcl/orcl/cdump'

*.db_block_size=8192

*.db_create_file_dest='/opt/oracle/oradata'

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=10G

*.diagnostic_dest='/opt/oracle/diag/rdbms/orcl/orcl/trace'

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

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/opt/oracle/arch'

*.log_buffer=7356416 # log buffer update

*.open_cursors=300

*.optimizer_dynamic_sampling=2

*.optimizer_mode='ALL_ROWS'

*.pga_aggregate_target=186M

*.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora

*.processes=150

*.query_rewrite_enabled='TRUE'

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=2880K

*.sga_target=560M

*.skip_unusable_indexes=TRUE

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME='orcls'

log_archive_config='DG_CONFIG=(orcls,orcl)'

log_archive_dest_1='LOCATION=/opt/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcls'

LOG_ARCHIVE_DEST_2='SERVICE=dbprimary LGWR ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=dbprimary

FAL_CLIENT=dbstandby

STANDBY_FILE_MANAGEMENT=AUTO

*.log_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata'

*.db_file_name_convert='/opt/oracle/oradata','/opt/oracle/oradata'

備庫調整，編輯監聽配置檔案listener.ora

LISTENER =

(DESCRIPTION_LIST=

(DESCRIPTION =

(ADDRESS=(PROTOCOL=tcp)(HOST=centdgstd)(PORT=1521))

(ADDRESS=(PROTOCOL=ipc)(KEY= EXTPROC1521)))

)

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcl)

(SID_NAME=orcl)

(ORACLE_HOME=/opt/oracle/product/11.2.0.4/db)

)

(SID_DESC =

(GLOBAL_DBNAME = orcls_DGMGRL)

(ORACLE_HOME = /opt/oracle/product/11.2.0.4/db)

(SID_NAME= orcl)

)

ADR_BASE_LISTENER = /opt/oracle

備庫調整，修改備庫TNS服務配置檔案tnsnames.ora

DBPRIMARY=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgpri)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

ORCLS=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

DBSTANDBY=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL =TCP)(HOST=centdgstd)(PORT=1521))

)

(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))

)

6、主庫克隆島備庫

主庫建立sys密碼檔案並傳送到備庫dbs目錄

orapwd file=$ORACLE_HOME/dbs/PWDorcl.ora password=WaterH2o entries=40 force=y

備庫建立和主庫一致的審計目錄
主庫：

cd $ORACLE_BASE

tar -cvf diag.tar diag/

scp diag.tar centdgstd:/opt/oracle

備庫：

mv diag $ORACLE_BASE/

cd $ORACLE_BASE

tar -xvf diag.tar

mkdir -p $ORACLE_BASE/flash_recovery_area

mkdir -p $ORACLE_BASE/oradata

克隆主庫前的TNS服務測試，一定要在主備庫均進行測試
tnsping dbprimary
tnsping dbstandby
sqlplus sys/WaterH2o@dbprimary as sysdba
sqlplus sys/WaterH2o@dbstndby as sysdba

主庫克隆到備機
rman target sys/WaterH2o@dbprimary auxiliary sys/WaterH2o@dbstandby

duplicate target database for standby from active database nofilenamecheck;

7、開啟備庫的日誌同步程式
alter database recover managed standby database disconnect from session;

8、檢視主備庫的角色
select db_unique_name,database_role,switchover_status,open_mode from v$database;

9、驗證物理DG的資料同步

select switchover_status from v$database; --檢視有沒有gap的歸檔日誌

主庫：

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

主庫：

SQL>select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

STATUS GAP_STATUS

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

VALID RESOLVABLE GAP

備庫：

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

STATUS GAP_STATUS

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

VALID NO GAP
檢視主備庫角色及狀態

select open_mode,database_role,db_unique_name from v$database;-

主庫：

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

READ WRITE PRIMARY orcl

SQL>

備庫：

SQL>select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

READ ONLY WITH APPLY PHYSICAL STANDBY orcl

SQL>

檢視主備庫的序列號

select max(sequence#) from v$archived_log;

archive log list;

主庫：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

25

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/arch

Oldest online log sequence 24

Next log sequence to archive 26

Current log sequence 26

SQL>

備庫：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

25

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/arch

Oldest online log sequence 17

Next log sequence to archive 0

Current log sequence 25

SQL>

10、DG Broker手工管理配置
DG部署前的主庫確認開啟閃回
select flashback_on from v$database;

主備庫開啟dg_broker_start

show parameter dg_broker_start

alter system set dg_broker_start=true;

show parameter dg_broker_start

主庫登入dgmrl客戶端
dgmgrl sys/WaterH2o@dbprimary

建立dgb控制檔案（注意一定要進行tnsping TNS服務名測試）
create configuration my_dgb as primary database is orcl connect identifier is dgb_p;

DGMGRL> create configuration my_dgb as primary database is orcl connect identifier is dbprimary;

Configuration "my_dgb" created with primary database "orcl"

新增備庫

DGMGRL> add database orcls as connect identifier is dbstandby maintained as physical;

Database "orcls" added

啟用配置檔案

DGMGRL> enable configuration;

Enabled.

DGMGRL>

驗證配置啟動狀態

DGMGRL> show configuration

Configuration - my_dgb

Protection Mode: MaxPerformance

Databases:

orcl - Primary database

orcls - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

開啟主備庫StandbyFileManagement並同步到DGB

SQL>alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

DGMGRL> edit database orcl set property StandbyFileManagement='AUTO';

DGMGRL> edit database orcls set property StandbyFileManagement='AUTO';

DGMGRL> EDIT DATABASE orcl SET PROPERTY LogXptMode='async';

DGMGRL> EDIT DATABASE orcls SET PROPERTY LogXptMode='async';

取消物理DG的兩個引數

alter system set fal_server='' scope=both sid='*';

alter system set fal_client='' scope=both sid='*';

11、物理DG 透過Broker手工切換主備庫的角色
主備庫切換前的角色檢查
主庫：

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY TO STANDBY READ WRITE

備庫：

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY NOT ALLOWED MOUNTED

DGMGRIL控制檯切換主備庫

DGMGRL> show configuration

Configuration - my_dgb

Protection Mode: MaxPerformance

Databases:

orcl - Primary database

orcls - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> switchover to orcls

Performing switchover NOW, please wait...

Operation requires a connection to instance "orcl" on database "orcls"

Connecting to instance "orcl"...

Connected.

New primary database "orcls" is opening...

Operation requires startup of instance "orcl" on database "orcl"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "orcls"

DGMGRL>

DGMGRL> show configuration

Configuration - my_dgb

Protection Mode: MaxPerformance

Databases:

orcls - Primary database

orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRIL控制檯手工切換主備庫的結果檢查

新主庫：

SQL> select db_unique_name,database_role,switchover_status,open_mode from v$database;

DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

orcls PRIMARY TO STANDBY READ WRITE

新備庫：

SQL> select db_unique_name,database_role,switchover_status,open_mode from v$database;

DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

orcl PHYSICAL STANDBY NOT ALLOWED MOUNTED

12、啟動DG FFS即啟動Failvoer Fast Start
啟用FFS必須滿足的條件
主備庫日誌同步模式是自動同步

LogXptMode='async'

EDIT DATABASE orcl SET PROPERTY LogXptMode='async';

EDIT DATABASE orcls SET PROPERTY LogXptMode='async';

主備庫均開啟閃回，目的是啟動快速自動恢復資料庫

select name,db_unique_name,flashback_on from v$database;

處理standby的備庫開啟閃回

alter database open read only;

alter database flashback on;

12. 啟用DG的FFS

啟用主備庫的FFS

edit database orcl set property FastStartFailoverTarget=orcls;

edit database orcls set property FastStartFailoverTarget=orcl;

enable fast_start failover

啟用結果

13、DG Broker FFS功能測試
主庫shutdown abort故障模擬
登入主庫發起shutdown abort

備庫告警日誌提示備庫成功接管主庫

observer提示角色自動切換

確認角色自動切換結果

主庫恢復後啟動

observer日誌提示主備角色分配

原主庫恢復後的主備庫角色檢視

原主庫端檢視

原備庫端檢視

ORACLE 11.2.0.4 DG(Broker) for linux 部署

主備庫開啟dg_broker_start

12. 啟用DG的FFS

相關文章