APP
主庫192.168.2.191????數(shù)據(jù)庫實例名：orcl ????db_unique_name:primary?
從庫?192.168.2.192????數(shù)據(jù)庫實例名：orcl ????db_unique_name:standby
?
1.?主備庫判斷DG是否已經安裝,默認是安裝好的
?SQL> select * from v$option where parameter = 'Oracle Data Guard' ;
如果是true表示已經安裝可以配置，否則需要安裝相應組件。
?
2.?備份主備庫的pfile文件
sqlplus / as sysdba?再輸入?conn / as sysdba
create pfile='init_20210910.ora' from spfile;
?
?
3.?主備都要開啟archive log
先關閉shutdown immediate
SQL>?startup mount;
接著把數(shù)據(jù)庫改為歸檔模式：alter database archivelog
查看結果：archive log list
?
4.?主備庫開啟強制日志模式（避免sql使用nologging）
SQL>?alter database force logging;
Database altered.
檢查是否開啟成功：
SQL> ?select name,log_mode,force_logging from v$database;
NAME ??LOG_MODE ????FOR
--------- ?????------------ ?????????---
ORCL ??ARCHIVELOG ??YES
如果需要在主庫添加或者刪除數(shù)據(jù)文件時，這些文件也會在備庫添加或刪除，使用如下：??????
sql>alter system set standby_file_management=AUTO?;????
默認此參數(shù)是manual手工方式
?
查看結果：
sql>show parameter standby ?
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest ??????string ??/dbs/arch
standby_file_management ??????string ?AUTO
?
5.?主、備庫創(chuàng)建standby redolog日志組
從庫使用standby log files來保存從主庫接收到的重做日志。查看主庫當前線程與日志組的對應關系及日志組的大?。?/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
?
查看當前有哪些日志組及其成員：
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ù)，假設現(xiàn)在節(jié)點是1個，則=(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.?主備庫修改db_name和db_unique_name????
主從庫db_name必須一致，db_unique_name不一致,主庫為zzbspri，從庫為zzbsstd??
查看：sql>show parameter name主備庫的db_name都一致不需要設置
?
主庫db_unique_name設置：
sql>alter system set db_unique_name=primary?scope=spfile;
備庫db_unique_name設置：
sql>alter system set db_unique_name=standby?scope=spfile;
?
?
7.?主備庫設置log_archive_config，Data Guard 配置里的另外一個庫的名字，同步方式
主備一樣設置設置，該參數(shù)定義了DG配置中可用的DB_UNIQUE_NAME參數(shù)值列表
alter system set log_archive_config= 'DG_CONFIG=(primary,standby)';
?
主庫歸檔路徑，log_archive_dest_1 是寫入本地路徑，log_archive_dest_2是寫入對端的路徑，service對端的服務名稱：
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;
備庫歸檔路徑：
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.?主備庫配置FAL_SERVER??
?這個參數(shù)指定當日志傳輸出現(xiàn)問題時，備庫到哪里去找缺少的歸檔日志。它用在備庫接收到的重做日志間有缺口的時候。你是主庫,就填寫:fal_server=從庫,從庫上就反過來:fal_server=主庫?
主庫修改：
alter system set fal_server='standby'; ?
SQL> SHOW PARAMETER FAL_SERVER
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
fal_server ???????string ?zzbsstd
?
備庫修改:
alter system set fal_server='primary'; ?
SQL> SHOW PARAMETER FAL_SERVER
NAME ????????TYPE ?VALUE
------------------------------------ ----------- ------------------------------
fal_server ???????string ?zzbspri
?
?
9.?主庫密碼文件復制到備庫???
主庫執(zhí)行：?
scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.2.192:/software/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl
?
?
10.?主備庫配置靜態(tài)監(jiān)聽
主庫配置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))
????)
??)
?
備庫配置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)聽配好后 最好重啟一下監(jiān)聽，否則會在rman步驟報錯。
?
11.?主備重啟
shutdown?immediate
startup
?
?
12.?使用duplicate搭建備庫
備庫需啟動到nomount狀態(tài)；?啟動后同步。
?
在主庫同步數(shù)據(jù)到備庫
[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)下進行日志應用
備庫開啟數(shù)據(jù)庫：
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.?啟動關閉操作
啟動順序：
1、啟動備庫：?????startup
2、啟動備庫實時日志應用：
alter database recover managed standby database using current logfile disconnect from session;
3、啟動主庫：startup
?
關閉順序：
1、關閉主庫：shutdown immediate
2、暫停備庫實時應用:alter database recover managed standby database cancel;?
3、關閉備庫:shutdown immediate
?
?
?
15.?查看狀態(tài)
查看數(shù)據(jù)庫的保護模式：
primary?端查看，我們可以看到數(shù)據(jù)庫的保護模式為最大性能
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/
