主庫db_unique_name: primary
備庫db_unique_name: standby

配置DATAGUARD:
##########主庫上操作：###########

alter system set log_archive_dest_1= 'location=/u01/app/dg/l_log/ valid_for=(all_logfiles,all_roles) db_unique_name=primary' scope=spfile;
alter system set log_archive_dest_2= 'service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby lgwr sync affirm' scope=spfile;
alter database archivelog;(mount下)
ALTER DATABASE FORCE LOGGING;

alter system set db_unique_name=primary scope=spfile;

alter system set log_archive_config='DG_CONFIG=(primary ,standby)' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

ALTER SYSTEM SET FAL_SERVER ='standby';

ALTER SYSTEM SET FAL_client ='primary';

alter system set standby_file_management=auto;

alter system set standby_archive_dest='/u01/app/dg/s_log/' scope=spfile;

--standby redolog的組數參考公式：(online redolog組數 + 1) * 資料庫執行緒數；單機執行緒數為1，RAC一般為2。
alter database add standby logfile '/u01/app/dg/y_log/s1.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s2.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s3.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s4.log' size 50m;

alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/','/u01/app/dg/oradata' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/','/u01/app/dg/oralog' scope=spfile;

alter database create standby controlfile as '/u01/app/dg/s_con/stancontrol.ctl';
create pfile from spfile;
scp initprimary.ora 192.168.75.62:/u01/oracle/db_1/dbs
scp /u01/app/dg/s_con/stancontrol.ctl 192.168.75.62:/u01/oracle

##########備庫上操作：##########

orapwd file=$ORACLE_HOME/dbs/orapwrac password=oracle entries=30;

mv initprimary.ora initstandby.ora
vi initstandby.ora
create spfile from pfile='/u01/app/dg/rman/stan.ora';

alter system set control_files ='/u01/app/dg/s_con/stancontrol.ctl' scope=spfile;

switch database to copy;

alter system set db_unique_name=standby scope=spfile;

--啟動db接受或傳送redo data，包括所有庫的db_unique_name
alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;

alter system set standby_file_management=auto;
alter system set service_names='standby' scope=both;

ALTER SYSTEM SET FAL_SERVER ='primary';
ALTER SYSTEM SET FAL_CLIENT ='standby';

--standby redolog的組數參考公式：(online redolog組數 + 1) * 資料庫執行緒數；單機執行緒數為1，RAC一般為2。
alter database add standby logfile '/u01/app/dg/y_log/s21.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s22.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s23.log' size 50m;
alter database add standby logfile '/u01/app/dg/y_log/s24.log' size 50m;

alter system set log_archive_dest_1= 'location=/u01/app/dg/l_log/ valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=spfile;
alter system set log_archive_dest_2= 'service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary lgwr sync affirm';

##########啟動ADG##########
SQL> alter database recover managed standby database disconnect from session;

ACTIVE DATA GURAD
開啟一般模式：
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;

如果開啟時日誌實時應用的話：
Real-time Apply
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

alter database set standby database to maximize performance;

提示：maximize後可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分別對應最大保護，最高可用性及最高效能。

驗證主庫
SQL> Select dest_name,status,error from v$archive_dest;

####################rman duplicate到備庫####################

rman target /

run
{
allocate channel d1 type disk format '/u01/backupset/%U';
allocate channel d2 type disk format '/u01/backupset/%U';
backup database;
release channel d1;
release channel d2;
}

exit
scp -r /u01/backupset 192.168.143.158:/u01/

在primary 上執行
rman target / auxiliary sys/oracle@standby

run
{
allocate auxiliary channel d1 type disk;
duplicate target database for standby nofilenamecheck;
release channel d1;

}

##########################檢查：######################################

檢視當前主機的執行狀態
select switchover_status,database_role,protection_mode from v$database;

顯示備庫相關程式的當前狀態資訊：
SQL> select process,client_process,sequence#,status from v$managed_standby;

檢視備庫接收、應用redo資料的過程
select message from v$dataguard_status;

檢視被日誌是否被應用
select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

檢視資料庫狀態
select open_mode,database_role,db_unique_name from v$database;

監控日誌傳送狀態，V$archive_gap記錄當前備庫mrp程式恢復需要的但是還沒有傳到備庫的日誌更簡單的是檢視主備庫的歸檔日誌的序列號相差多少
select * from v$archive_gap;

檢視資料庫角色(檢視是主庫還是備庫)
select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

1、在生產庫停止Data Guard操作：

SQL> show parameter log_archive_dest
SQL> alter system set log_archive_dest_state_2=defer;

2、在生產庫開啟Data Guard操作：

SQL> alter system set log_archive_dest_state_2=enable;

####################注意Data Guard 啟動順序：##############################

啟動順序：先standby ,後primary;
關閉順序：先primary 後standby;

一.啟用備用資料庫
1.將備庫將例項啟動到mount 狀態：

startup nomount;
2.啟動到備用庫模式
alter database mount standby database ; （也可以在此步後以只讀模式開啟資料庫）
3.開始redo apply
alter database recover managed standby database disconnect from session;
alter database recover managed standby database parallel 32 disconnect from session;

4.如果要開啟資料庫，Standby只讀模式開啟

--停止redo apply

alter database recover managed standby database cancel;
--只讀模式開啟
alter database open read only ;（在此可以繼續恢復備用庫應用日誌 Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;）
）

5.驗證Standby redo 是否應用

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

#################### 主備庫切換 ##############################
Step 1
主庫：
1. 檢視switchover 狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY

備庫：
1.檢視switchover狀態
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

附：若不是用此語句切換：ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

補充：若出現：ORA-16139: media recovery required
是因為沒有執行：alter database recover managed standby database disconnect from session;

2.　檢視是否有日誌GAP,沒有應用的日誌：
SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

如果有，則複製過來並且註冊
ALTER DATABASE REGISTER PHYSICAL LOGFILE '路徑';
重複檢視直到沒有應用的日誌：

Step 2 開始把物理主庫改變為物理備庫（原主庫執行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;

注：如果有活動的session可以使用此選項，否則轉換會遇到ORA-01093錯誤，也可以殺掉活動會話或等活動會話後進行轉換

Step 3 關閉並重啟主庫（原主庫執行）
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 驗證備庫是否能執行角色轉換到主庫（原備庫執行）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 5 開始把物理備庫轉換成物理主庫（原備庫執行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

--如果報ORA-16139: media recovery required，可能是由於未應用日誌引起，可先執行
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Step 6 開啟備庫，然後關閉重啟.（原備庫執行）

SQL> ALTER DATABASE OPEN;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Step 7 驗證是否轉換成功（原備庫執行）

SQL> ALTER SYSTEM SWITCH LOGFILE;

Setp 8 應用歸檔日誌（原主庫上執行）

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

########################################配置 dg_broker#####################

1、配置listener.ora檔案和tnsnames.ora檔案，新增GLOBAL_DBNAME引數，重啟監聽器，所有的節點都需要配置
GLOBAL_DBNAME=primary_DGMGRL格式為GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因為實際環境中db_domain為空，以下所有涉及到service_name的就用這個GLOBAL_DBNAME了。
for example：
------primary database：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = primary)
(GLOBAL_DBNAME= primary_DGMGRL.localdomain)
(ORACLE_HOME = /u01/oracle/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
)
)

-----standby database：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = standby)
(GLOBAL_DBNAME= standby_DGMGRL.localdomain)
(ORACLE_HOME = /u01/oracle/db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
)
)

--配置tnsnames.ora檔案

primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.157)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary_DGMGRL.localdomain)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.158)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby_DGMGRL.localdomain)
)
)

2、設定LOCAL_LISTENER

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.157)(PORT = 1521))' scope=spfile;
SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.158)(PORT = 1521))' scope=spfile;

3，修改dg_broker_config_file引數。如果是在RAC環境中，這個把這個檔案把到共享的儲存上面，如果有ASM可以放到ASM中。

主庫：
#這裡跟官方給的名字有一點不一樣htz1.dat這裡官方要求是drc.dat。
alter system set dg_broker_config_file1='/u01/oradata/primary/drcprimary1.dat';
alter system set dg_broker_config_file2='/u01/oradata/primary/drcprimary2.dat';

備庫：
alter system set dg_broker_config_file1='/u01/oradata/standby/drcstandby1.dat';
alter system set dg_broker_config_file2='/u01/oradata/standby/drcstandby2.dat';

4.啟用BROKER：
在兩個資料庫上面都執行：
alter system set dg_broker_start=true scope=both;

5.BROKER管理：
在主備庫其中任意一臺做下面的操作就可以了，另它安裝了client的客戶端也是可以的.
#進入管理
[oracle@11g admin]$ dgmgrl sys/oracle@primary

#help是幫忙命令
DGMGRL> help create
CREATE CONFIGURATION AS PRIMARY DATABASE IS CONNECT IDENTIFIER IS ;

#####開始配置管理dg######

#建立一個配置檔案，這裡一定要記得是連線到當前的主資料庫
DGMGRL> create configuration primary as primary database is primary connect identifier is primary;

#啟用配置檔案
DGMGRL> enable configuration;
Enabled.

#增加備庫到配置檔案中
DGMGRL> help add
ADD DATABASE [AS CONNECT IDENTIFIER IS ] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> add database standby as connect identifier is standby;
Database "standby" added
#啟用備庫
DGMGRL> enable database standby;
Enabled.

#檢視配置檔案
DGMGRL> show configuration
#檢視某個資料庫的配置訊息，其中很多引數我們都可以在sqlplus修改的。
DGMGRL> show database verbose primary;

#靜態監聽中的GLOBAL_NAME是DB_UNIQUE_NAME，所以這裡我們要修改
DGMGRL> show database verbose standby;

修改StaticConnectIdentifier引數，記得兩個資料庫都要修改。broker裡面的連線的service_name是_DGMGRL
DGMGRL> edit database primary set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.143.157)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL.localdomain)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))';
DGMGRL>edit database standby set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.143.158)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL.localdomain)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))';

#####SWITCHOVER######
#開始切換
DGMGRL> switchover to standby;

#檢視狀態
DGMGRL> show configuration;

查詢資料庫狀態：
set line 300
select DB_UNIQUE_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS,DATAGUARD_BROKER from v$database;

#####FAILOVER######

1.修改保護模式：

[oracle@test admin]$ dgmgrl sys/1111111

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL> show configuration;

2.修改日誌傳遞方式

DGMGRL> edit database primary set property logxptmode=sync;
DGMGRL> edit database standby set property logxptmode=sync;
DGMGRL> show database primary logxptmode
LogXptMode = 'sync'
DGMGRL> show database standby logxptmode
LogXptMode = 'sync
3. 配置資料庫的flashback

主庫上面
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

#配置flashback
startup mount;
alter database flashback on;
alter database open;
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON
-------------------- ---------------- ------------ ------------------
READ WRITE PRIMARY ARCHIVELOG YES
#flashback的配置目錄與大小
SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4032M

備庫上面
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON
-------------------- ---------------- ------------ ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG NO

SQL> recover managed standby database cancel;
Media recovery complete.
startup mount;
alter database flashback on;

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON
-------------------- ---------------- ------------ ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG YES

4 配置observer伺服器

5 配置tnsnames.ora檔案。

6 啟動observer程式

[oracle@test admin]$ dgmgrl sys/111111 "start observer"

7 配置fast-start failover

DGMGRL> edit database primary set property FastStartFailoverTarget=standby;
DGMGRL> edit database standby set property FastStartFailoverTarget=primary ;

8 啟用fast-start failover

DGMGRL> enable fast_start failover

測試FAST-START FAILOVER是否生效

#原主庫直接shutdown abort;
SQL> shutdown abort;

SQL> select open_mode ,database_role from v$database;

手動啟動原主庫
DGMGRL> show configuration;

至此broker配置操作完畢。

#######################################問題處理彙總##############################################

########處理ORA-01153:問題##########
Action: Complete or cancel the other media recovery session or RMAN backup
SQL> recover managed standby database cancel;
SQL> recover automatic standby database;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

##########處理ORA-16143:問題##########
ORA-16143: RFS connections not allowed during or after terminal recovery
Thu Dec 13 07:40:47 2012
原因：在備庫上曾經執行了：
alter database recover managed standby database finish ；
-------導致RFS不能再被啟動
Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法

1. 在主庫重建standby control file
先在備庫檢視一下控制檔名稱，等會建立完後直接覆蓋過去：

主庫建立standby controlfile：
SQL> alter database create standbycontrolfile as '/u01/control01.ctl';
Database altered.

copy到備庫的目錄，在覆蓋原來的控制檔案：
--先關閉備庫：
SQL> shutdown immediate
--copy並覆蓋：
mv control01.ctlcontrol01.ctl.bak
scp 192.168.1.20:/u01/control01.ctl 192.168.1.30:/u01/app/oracle/oradata/dave/
mv control02.ctlcontrol02.ctl.bak
cp control01.ctl/u01/app/oracle/fast_recovery_area/dave/control02.ctl

2.在正常拉起備庫
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managedstandby database disconnect from session;

##########處理ORA-16789:問題##########
broker ORA-16789: standby redo logs not configured
解決方法：在主庫新增standby logfile
alter database add standby logfile ('/u01/oradata/primary/standby01.log') size 50m;
alter database add standby logfile ('/u01/oradata/primary/standby02.log') size 50m;
alter database add standby logfile ('/u01/oradata/primary/standby03.log') size 50m;
alter database add standby logfile ('/u01/oradata/primary/standby04.log') size 50m;

#########failover會出現兩種情況：##########
第一種：（有可能將原來當機的主庫reinstated為備庫）
Error: ORA-16661: the standby database needs to be reinstated
Reinstate the database using the DGMGRL REINSTATE DATABASE command
步驟：
1. Restart the database to the mounted state
2. Connect to the new primary database
3. Use DGMGRL to reinstate the database
DGMGRL> REINSTATE DATABASE db_unique_name;
如果以上步驟失敗，則需要重新搭建dg。
第二種：
ORA-16795: the broker detects that database re-creation is required
沒有辦法只有重建dg。

DATAGUARD、broker安裝維護檔案（個人筆記比較齊全）

相關文章