ORACLE 11G DATAGUARD 搭建（RMAN duplicate方式相同目錄結(jié)構(gòu)）

1.準(zhǔn)備ORACLE主、備庫(kù)環(huán)境

本次使用

ORACLE 11.2.0.4 數(shù)據(jù)庫(kù)軟件

RHEL rhel6.2 操作系統(tǒng)

1.1修改主、備庫(kù)hosts文件

vi /etc/hosts

172.20.0.7 liu
172.20.0.8 liudg

1.2環(huán)境變量

主庫(kù)環(huán)境變量

vi .bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LANG=en_US; export LANG
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

備庫(kù)環(huán)境變量

vi .bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_SID=orcl; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LANG=en_US; export LANG
ORACLE_UNQNAME=orcldg; export ORACLE_UNQNAME
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"

1.3主備庫(kù)環(huán)境準(zhǔn)備

主庫(kù)安裝數(shù)據(jù)庫(kù)軟件、建庫(kù)、建監(jiān)聽
./runInstaller ****安裝數(shù)據(jù)庫(kù)軟件****
NETCA ****建監(jiān)聽****
DBCA ****建庫(kù)****

備庫(kù)安裝數(shù)據(jù)庫(kù)軟件、建監(jiān)聽
./runInstaller ****安裝數(shù)據(jù)庫(kù)軟件****
NETCA ****建監(jiān)聽****

2.進(jìn)行dataguard配置

2.1主庫(kù)開啟歸檔

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;

2.2主庫(kù)開啟強(qiáng)制日志

alter database force logging；

（在mount模式下執(zhí)行，效率更快，可以在開啟歸檔時(shí)執(zhí)行）

2.3主庫(kù)開啟flashback日志

select flashback_on from v$database;
Alter database flashback on;
alter database open;

2.4修改tnsnames.ora 文件

cd $ORACLE_HOME/network/admin

***主備同步需要***

ORCL =

　　(DESCRIPTION =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.7)(PORT = 1521))
　　　　(CONNECT_DATA =
　　　　　　　　(SERVER = DEDICATED)
　　　　　　　　(SERVICE_NAME = orcl)
　　　　)
)
ORCLDG =
    (DESCRIPTION =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.8)(PORT = 1521))
　　　　(CONNECT_DATA =
　　　　　　　　(SERVER = DEDICATED)
　　　　　　　　(SERVICE_NAME = orcl)
　　　　)
)

2.5修改監(jiān)聽為靜態(tài)注冊(cè)

主庫(kù)：

SID_LIST_LISTENER =
　　　　(SID_LIST =
　　　　(SID_DESC =
　　　　　　　　(GLOBAL_DBNAME = orcl)
　　　　　　　　(SID_NAME = orcl)
　　　　　　　　(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
　　　　)
)
LISTENER =
(DESCRIPTION_LIST =
　　　　(DESCRIPTION =
　　　　　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.7)(PORT = 1521))
　　　　　　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
　　　　)
)
ADR_BASE_LISTENER = /u01/app/oracle

備庫(kù)：
SID_LIST_LISTENER =
　　　　(SID_LIST =
　　　　　　　　(SID_DESC =
　　　　　　　　(GLOBAL_DBNAME = orcl)
　　　　　　　　(SID_NAME = orcl)
　　　　　　　　(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
　　　　　　　　)
)
LISTENER =
(DESCRIPTION_LIST =
　　　　(DESCRIPTION =
　　　　　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.0.8)(PORT = 1521))
　　　　　　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
　　　　)
)
ADR_BASE_LISTENER = /u01/app/oracle

重啟主備監(jiān)聽:

lsnrctl stop
lsnrctl start
主備檢測(cè)tnsnames
tnsping orcldg
tnsping orcl

2.6.修改主庫(kù)參數(shù)，增加standby 聯(lián)機(jī)日志

sqlplus / as sysdba

增加以下內(nèi)容

alter system set DB_UNIQUE_NAME='orcl' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;
alter system set log_archive_dest_2='SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=spfile;
alter system set log_archive_dest_state_1='enable' scope=spfile;
alter system set log_archive_dest_state_2='enable' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCL/datafile','/u01/app/oracle/oradata/ORCLDG/datafile';
alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCL/onlinelog/','/u01/app/oracle/oradata/ORCLDG/onlinelog/';
alter system set fal_server='orcldg';
alter system set fal_client='orcl';
alter system set standby_file_management='AUTO' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo01.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo03.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/ORCL/onlinelog/stredo04.log' size 50M;
重啟主庫(kù)
shutdown immediate;
startup;

2.7生成主庫(kù)pfile文件

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

2.8傳輸相應(yīng)文件到備庫(kù)

1) 主庫(kù)密碼文件

scp -r $ORACLE_HOME/dbs/orapworcl liudg:$ORACLE_HOME/dbs/ 

2) 主庫(kù)pfile文件

scp -r /home/oracle/pfile.ora liudg:/home/oracle/

2.9修改備庫(kù)pfile文件

(注:orcl和orcldg互換即可/log_file_name_convert、db_file_name_convert無(wú)需變化)

3.以rman duplication方式恢復(fù)備庫(kù)

3.1創(chuàng)建備庫(kù)相應(yīng)目錄結(jié)構(gòu)

mkdir -p /u01/app/oracle/admin/orcldg/adump
mkdir -p /u01/app/oracle/oradata/ORCLDG/controlfile/
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDG/controlfile/
mkdir -p /u01/app/oracle/diag/rdbms/orcldg/orcl/cdump
mkdir -p /u01/app/oracle/oradata/ORCLDG/datafile
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/oradata/ORCLDG/onlinelog/standby
(注:據(jù)當(dāng)前環(huán)境目錄修改)

3.2啟動(dòng)備庫(kù)到nomount狀態(tài)

[oracle@orcldg ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
SQL>

3.3主庫(kù)連接輔助庫(kù)

[oracle@liu ~]$ rman target sys/ln202902@orcl auxiliary sys/ln202902@orcldg
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 10 14:32:33 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1615274987)
connected to auxiliary database: ORCL (not mounted)
RMAN>

3.4duplicate復(fù)制數(shù)據(jù)庫(kù)

RMAN> duplicate target database for standby nofilenamecheck from active database;
nofilenamecheck： 相同目錄結(jié)構(gòu)使用此參數(shù),結(jié)構(gòu)不同使用該參數(shù)避免目錄檢查。
RMAN> duplicate target database for standby from active database;

4.備庫(kù)開啟standby實(shí)時(shí)恢復(fù)

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

6.primary庫(kù)歸檔,查看dataguard數(shù)據(jù)同步是否成功

SQL> select name,archived,applied,sequence# from v$archived_log;
SQL> alter system archive log current;
SQL> select name,archived,applied,sequence# from v$archived_log;
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;

7.啟停

7.1關(guān)閉

先主庫(kù)，后備庫(kù)。

主庫(kù)執(zhí)行:
shutdown immediate;
lsnrctl stop

備庫(kù)執(zhí)行:
alter database recover managed standby database cancel; 關(guān)閉實(shí)時(shí)同步
shutdown immediate;
lsnrctl stop

7.2啟動(dòng)

先備庫(kù)，后主庫(kù)。

備庫(kù)執(zhí)行:
lsnrctl start
Startup nomount；
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;

主庫(kù)執(zhí)行:
lsnrctl start
startup 

8. 保護(hù)性切換

1.主庫(kù)啟動(dòng)到mount狀態(tài)
2.主庫(kù)中執(zhí)行如下SQL語(yǔ)句
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
3.查詢保護(hù)模式語(yǔ)句
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

9. 主備切換

主庫(kù)：
SQL> select switchover_status from v$database;
1. 如果switchover_status為TO_STANDBY說(shuō)明可以轉(zhuǎn)換
直接轉(zhuǎn)換：
alter database commit to switchover to physical standby;
2. 如果switchover_status為SESSIONS ACTIVE 則關(guān)閉會(huì)話
SQL>alter database commit to switchover to physical standby with session shutdown;
3. startup #啟動(dòng)
4. alter database recover managed standby database using current logfile disconnect from session; #啟同步
5.select name,open_mode,switchover_status,database_role from v$database; #查看狀態(tài)

備庫(kù)：
SQL> select switchover_status from v$database;
1. 如果switchover_status為TO_PRIMARY 說(shuō)明標(biāo)記恢復(fù)可以直接轉(zhuǎn)換為primary庫(kù)
SQL>alter database commit to switchover to primary
2. 如果switchover_status為SESSION ACTIVE 就應(yīng)該斷開活動(dòng)會(huì)話
SQL>alter database commit to switchover to primary with session shutdown;
3. 如果switchover_status為NOT ALLOWED 說(shuō)明切換標(biāo)記還沒收到，此時(shí)不能，檢查主庫(kù)
4.alter database open;
5.select name,open_mode,switchover_status,database_role from v$database; 

10. 災(zāi)難恢復(fù)

手動(dòng)

舊備庫(kù)切新主庫(kù)：
alter database recover managed standby database finish force;
alter database commit to switchover to primary with session shutdown;
alter database open;
select name,log_mode ,open_mode ,database_role ,protection_mode ,switchover_status from v$database;
SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; #查詢進(jìn)主庫(kù)的SCN

新主庫(kù)切舊備庫(kù)：
shutdown immediate
startup mount
flashback database to scn 新主庫(kù)SCN;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY; #轉(zhuǎn)換physical standby database 
shutdown immediate;
startup;
select name,open_mode,switchover_status,database_role from v$database; #查看當(dāng)前狀態(tài)

alter database recovr managed standby database using current logfile disconnect from session; #開啟MRP
select name,open_mode,switchover_status,database_role from v$database;

檢查：
archive log list;
此刻是主備正常的
舊主庫(kù)恢復(fù)后，就可以將角色轉(zhuǎn)回
新主庫(kù)狀態(tài)：

alter database commit to switchover to pysical standby; #轉(zhuǎn)回備庫(kù)
startup;
select name,open_mode,switchover_status,database_role from v$database;
新備庫(kù)狀態(tài)：

此刻
alter database commit to switchover to primary with session shutdown ;
alter database open;

OK

broker

1.主備庫(kù)listener.ora靜態(tài)注冊(cè)中添加如下：

SID_LIST_LISTENER =
　　　　(SID_LIST =
　　　　(SID_DESC =
　　　　　　　　(GLOBAL_DBNAME = orcl)
　　　　　　　　(SID_NAME = orcl)
　　　　　　　　(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
)
　　　　(SID_DESC =
　　　　(GLOBAL_DBNAME = orcl_DGMGRL) #DB_UNIQUE_NAME.DGMGRL
　　　　　　　　(SID_NAME = orcl)
　　　　　　　　(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1/)
　　　　)
)

2.開啟broker

show parameter dg_broker_start;

alter system set dg_broker_start=true scope=spfile;

3. dgmgrl

到這兒就算是成功了，然后現(xiàn)在試試一條命令的轉(zhuǎn)換主備

接下來(lái)要配置成功fast_start failover 需要滿足以下5項(xiàng)條件.

1. dataguard 的配置要么是maxAvailability模式要么是maxPerformance模式.

2.當(dāng)dataguard的配置為maxAvailability模式時(shí),fast-start failover的目標(biāo)standby數(shù)據(jù)庫(kù)的log傳送模式必須設(shè)置為Sync.

3. 當(dāng)dataguard的配置為maxPerformance模式時(shí),fast-start failover的目標(biāo)standby數(shù)據(jù)庫(kù)的log傳送模式必須設(shè)置為Async.

4. 主庫(kù)與fast-start failover的目標(biāo)standby數(shù)據(jù)庫(kù)都必須激活flashback功能.

5. 當(dāng)配置了多個(gè)standby數(shù)據(jù)庫(kù)時(shí),要在主庫(kù)的配置屬性FastStartFailoverTarget指定目標(biāo)standby 數(shù)據(jù)庫(kù).

操作：略

配置完畢后

開啟observer

DGMGRL> start observer(nohup dgmgrl -silent sys/oracle@orcl "start observer" &)

DGMGRL> enable fast_start failover

ok成功