APP
主庫(kù)192.168.2.191????數(shù)據(jù)庫(kù)實(shí)例名：orcl ????db_unique_name:primary?
從庫(kù)?192.168.2.192????數(shù)據(jù)庫(kù)實(shí)例名：orcl ????db_unique_name:standby
?
1.?主備庫(kù)判斷DG是否已經(jīng)安裝,默認(rèn)是安裝好的
?SQL> select * from v$option where parameter = 'Oracle Data Guard' ;
如果是true表示已經(jīng)安裝可以配置，否則需要安裝相應(yīng)組件。
?
2.?備份主備庫(kù)的pfile文件
sqlplus / as sysdba?再輸入?conn / as sysdba
create pfile='init_20210910.ora' from spfile;
?
?
3.?主備都要開(kāi)啟archive log
先關(guān)閉shutdown immediate
SQL>?startup mount;
接著把數(shù)據(jù)庫(kù)改為歸檔模式：alter database archivelog
查看結(jié)果：archive log list
?
4.?主備庫(kù)開(kāi)啟強(qiáng)制日志模式（避免sql使用nologging）
SQL>?alter database force logging;
Database altered.
檢查是否開(kāi)啟成功：
SQL> ?select name,log_mode,force_logging from v$database;
NAME ??LOG_MODE ????FOR
--------- ?????------------ ?????????---
ORCL ??ARCHIVELOG ??YES
如果需要在主庫(kù)添加或者刪除數(shù)據(jù)文件時(shí)，這些文件也會(huì)在備庫(kù)添加或刪除，使用如下：??????
sql>alter system set standby_file_management=AUTO?;????
默認(rèn)此參數(shù)是manual手工方式
?
查看結(jié)果：
sql>show parameter standby ?
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest ??????string ??/dbs/arch
standby_file_management ??????string ?AUTO
?
5.?主、備庫(kù)創(chuàng)建standby redolog日志組
從庫(kù)使用standby log files來(lái)保存從主庫(kù)接收到的重做日志。查看主庫(kù)當(dāng)前線程與日志組的對(duì)應(yīng)關(guān)系及日志組的大?。?/span>
SQL>?select thread#,group#,bytes/1024/1024 from v$log;???
???THREAD# ????GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 ????1 ????50
1 ????2 ????50
2 ????3 ????50
2 ????4 ????50
?
查看當(dāng)前有哪些日志組及其成員：
SQL> select group#,member from v$logfile;
????GROUP# ????MEMBER
----------- ??------------------------------------------
?????3 ????????/u01/app/oracle/oradata/zzbs/redo03.log
?????2 ????????/u01/app/oracle/oradata/zzbs/redo02.log
1?/u01/app/oracle/oradata/zzbs/redo01.log
?
公式可以做參考：(每線程的日志組數(shù)+1)*最大線程數(shù)，假設(shè)現(xiàn)在節(jié)點(diǎn)是1個(gè)，則=(3+1)*1=4 ?，這里我們從建立從11到14的standby logfile
/software/app/oracle/oradata/orcl/redo01.log
?
alter database add standby logfile group ?11 '/software/app/oracle/oradata/orcl/standby11.log' size 50M; ?
alter database add standby logfile group ?12 '/software/app/oracle/oradata/orcl/standby12.log' size 50M;
alter database add standby logfile group ?13 '/software/app/oracle/oradata/orcl/standby13.log' size 50M;
alter database add standby logfile group ?14 '/software/app/oracle/oradata/orcl/standby14.log' size 50M;
?
查看standby 日志組的信息：
SQL> select group#,type,member from v$logfile;
????GROUP# TYPE ???MEMBER
---------- ------- -------------------------------------------------------------------------
?1 ONLINE ?/u01/app/oracle/oradata/zzbs/redo01.log
?2 ONLINE ?/u01/app/oracle/oradata/zzbs/redo02.log
?3 ONLINE ?/u01/app/oracle/oradata/zzbs/redo03.log
11 STANDBY /u01/app/oracle/oradata/standbylog/standby11.log
12 STANDBY /u01/app/oracle/oradata/standbylog/standby12.log
13 STANDBY /u01/app/oracle/oradata/standbylog/standby13.log
14 STANDBY /u01/app/oracle/oradata/standbylog/standby14.log
?
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
????GROUP# ?SEQUENCE# STATUS?BYTES/1024/1024
---------- ---------- ---------- ---------------
11????0 UNASSIGNED??????50
12????0 UNASSIGNED??????50
13????0 UNASSIGNED??????50
14????0 UNASSIGNED??????50
?
6.?主備庫(kù)修改db_name和db_unique_name????
主從庫(kù)db_name必須一致，db_unique_name不一致,主庫(kù)為zzbspri，從庫(kù)為zzbsstd??
查看：sql>show parameter name主備庫(kù)的db_name都一致不需要設(shè)置
?
主庫(kù)db_unique_name設(shè)置：
sql>alter system set db_unique_name=primary?scope=spfile;
備庫(kù)db_unique_name設(shè)置：
sql>alter system set db_unique_name=standby?scope=spfile;
?
?
7.?主備庫(kù)設(shè)置log_archive_config，Data Guard 配置里的另外一個(gè)庫(kù)的名字，同步方式
主備一樣設(shè)置設(shè)置，該參數(shù)定義了DG配置中可用的DB_UNIQUE_NAME參數(shù)值列表
alter system set log_archive_config= 'DG_CONFIG=(primary,standby)';
?
主庫(kù)歸檔路徑，log_archive_dest_1 是寫(xiě)入本地路徑，log_archive_dest_2是寫(xiě)入對(duì)端的路徑，service對(duì)端的服務(wù)名稱：
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' ?scope=spfile;
alter system set log_archive_dest_2=' SERVICE=standby?LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby' scope=spfile;
備庫(kù)歸檔路徑：
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' ?scope=spfile;
alter system set log_archive_dest_2=' SERVICE=primary?LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary' scope=spfile;
?
檢查：
show parameter ?log_archive_dest
select * from v$archive_dest_status
?
?
8.?主備庫(kù)配置FAL_SERVER??
?這個(gè)參數(shù)指定當(dāng)日志傳輸出現(xiàn)問(wèn)題時(shí)，備庫(kù)到哪里去找缺少的歸檔日志。它用在備庫(kù)接收到的重做日志間有缺口的時(shí)候。你是主庫(kù),就填寫(xiě):fal_server=從庫(kù),從庫(kù)上就反過(guò)來(lái):fal_server=主庫(kù)?
主庫(kù)修改：
alter system set fal_server='standby'; ?
SQL> SHOW PARAMETER FAL_SERVER
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
fal_server ???????string ?zzbsstd
?
備庫(kù)修改:
alter system set fal_server='primary'; ?
SQL> SHOW PARAMETER FAL_SERVER
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
fal_server ???????string ?zzbspri
?
?
9.?主庫(kù)密碼文件復(fù)制到備庫(kù)???
主庫(kù)執(zhí)行：?
scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.2.192:/software/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl
?
?
10.?主備庫(kù)配置靜態(tài)監(jiān)聽(tīng)
主庫(kù)配置listener.ora
cd /software/app/oracle/product/11.2.0/dbhome_1/network/admin
SID_LIST_LISTENER =
????(SID_LIST =
????????(SID_DESC =
??????????(GLOBAL_DBNAME = primary)
??????????(ORACLE_HOME = /software/app/oracle/product/11.2.0/dbhome_1)
??????????(SID_NAME = orcl)
????????)
)
LISTENER =
??(DESCRIPTION_LIST =
????(DESCRIPTION =
??????(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
??????(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
????)
??)
?
備庫(kù)配置listener.ora
SID_LIST_LISTENER =
????(SID_LIST =
????????(SID_DESC =
??????????(GLOBAL_DBNAME = standby)
??????????(ORACLE_HOME = /software/app/oracle/product/11.2.0/dbhome_1)
??????????(SID_NAME = orcl)
????????)
)
LISTENER =
??(DESCRIPTION_LIST =
????(DESCRIPTION =
??????(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
??????(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
????)
??)
?
主備配置tnsnames.ora配置：
standby?=
??(DESCRIPTION =
????(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.192)(PORT = 1521))
????(CONNECT_DATA =
??????(SERVER = DEDICATED)
??????(SERVICE_NAME = standby)
????)
??)
?
primary?=
??(DESCRIPTION =
????(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.191)(PORT = 1521))
????(CONNECT_DATA =
??????(SERVER = DEDICATED)
??????(SERVICE_NAME = primary)
????)
??)
?
注意：監(jiān)聽(tīng)配好后 最好重啟一下監(jiān)聽(tīng)，否則會(huì)在rman步驟報(bào)錯(cuò)。
?
11.?主備重啟
shutdown?immediate
startup
?
?
12.?使用duplicate搭建備庫(kù)
備庫(kù)需啟動(dòng)到nomount狀態(tài)；?啟動(dòng)后同步。
?
在主庫(kù)同步數(shù)據(jù)到備庫(kù)
[oracle@rac1 ~]$rman target sys/LW_SYS_2017@primary?auxiliary 'sys/LW_SYS_2017'@standby
RMAN> duplicate target database for standby from active database nofilenamecheck;
?
?
?
13.?在OPEN狀態(tài)下進(jìn)行日志應(yīng)用
備庫(kù)開(kāi)啟數(shù)據(jù)庫(kù)：
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
?
查看archive_dest_status是否正確：
select * from v$archive_dest_status;
?
?
14.?啟動(dòng)關(guān)閉操作
啟動(dòng)順序：
1、啟動(dòng)備庫(kù)：?????startup
2、啟動(dòng)備庫(kù)實(shí)時(shí)日志應(yīng)用：
alter database recover managed standby database using current logfile disconnect from session;
3、啟動(dòng)主庫(kù)：startup
?
關(guān)閉順序：
1、關(guān)閉主庫(kù)：shutdown immediate
2、暫停備庫(kù)實(shí)時(shí)應(yīng)用:alter database recover managed standby database cancel;?
3、關(guān)閉備庫(kù):shutdown immediate
?
?
?
15.?查看狀態(tài)
查看數(shù)據(jù)庫(kù)的保護(hù)模式：
primary?端查看，我們可以看到數(shù)據(jù)庫(kù)的保護(hù)模式為最大性能
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE ?PROTECTION_MODE ?????PROTECTION_LEVEL ???OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY ??MAXIMUM PERFORMANCE ?MAXIMUM PERFORMANCE ?READ WRITE
?
#standby?端查看，也是一樣的。
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE ?PROTECTION_MODE ?????PROTECTION_LEVEL ???OPEN_MODE
---------------- -------------------- -------------------- --------------------
?
PHYSICAL STANDBY MAXIMUM PERFORMANCE ?MAXIMUM PERFORMANCE ?READ ONLY
?
?
本文摘自 ：https://www.cnblogs.com/
