共計(jì) 10795 個(gè)字符，預(yù)計(jì)需要花費(fèi) 27 分鐘才能閱讀完成。
這篇文章主要為大家展示了“DG 如何搭建”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“DG 如何搭建”這篇文章吧。
一、主庫(kù)
1 歸檔模式
SQL archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
2 強(qiáng)制日志
SQL alter database force logging;
Database altered.
SQL select force_logging from v$database;
FOR
—
YES
3 添加 standby 日志
standby logfile 的數(shù)量和大小均要與 redo logfile 相同
SQL select thread#,group#,members,bytes/1024/1024 from v$log;
THREAD# GROUP# MEMBERS BYTES/1024/1024
———- ———- ———- —————
1 1 1 50
1 2 1 50
1 3 1 50
SQL col MEMBER for a25
SQL select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————- —
3 ONLINE /oradata/orcl/redo03.log NO
2 ONLINE /oradata/orcl/redo02.log NO
1 ONLINE /oradata/orcl/redo01.log NO
從圖中可以看到我們主庫(kù)有三組大小為 50M 的 redo logfile，故我們也需要?jiǎng)?chuàng)建同樣數(shù)量和大小的 standby logfile：
SQL alter database add standby logfile group 11 (/oradata/orcl/stb01.log)size 50m;
Database altered.
SQL alter database add standby logfile group 12(/oradata/orcl/stb02.log)size 50m;
Database altered.
SQL alter database add standby logfile group 13(/oradata/orcl/stb03.log)size 50m;
Database altered.
SQL select group#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
11 0 0 YES UNASSIGNED
12 0 0 YES UNASSIGNED
13 0 0 YES UNASSIGNED
4 設(shè)置數(shù)據(jù)庫(kù)口令文件的使用模式
查看 remote_login_passwordfile 的值是否 EXCLUSIVE
SQL show parameter remote_login_passwordfile
NAME TYPE VALUE
———————————— ———– ——————————
remote_login_passwordfile string EXCLUSIVE
如果不是，執(zhí)行以下命令進(jìn)行設(shè)置，并且重啟數(shù)據(jù)庫(kù)，使其生效：
SQL alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
SQL shutdown immediate;
SQL startup;
5 參數(shù)（文件）設(shè)置
SQL show parameter db_unique_name;
DG 的搭建需要修改許多數(shù)據(jù)庫(kù)的參數(shù)，并且部分參數(shù)主備庫(kù)之間有點(diǎn)區(qū)別，需要在配置過程細(xì)心一點(diǎn)。
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string orcl
SQL alter system set log_archive_config= dg_config=(orcl,orcls) scope=spfile;
System altered.
– 其中 dg_config 填寫的是主備庫(kù)的 db_unique_name。
修改歸檔文件位置
SQL show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
設(shè)置本地歸檔位置，參數(shù)涉及切換
alter system set log_archive_dest_1= LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl scope=spfile;
alter system set log_archive_dest_2= SERVICE=orcls ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls scope=spfile;
啟用設(shè)置的日志路徑
SQL alter system set log_archive_dest_state_1=enable scope=spfile;
SQL alter system set log_archive_dest_state_2=enable scope=spfile;
設(shè)置歸檔日志進(jìn)程的最大數(shù)量（視實(shí)際情況調(diào)整）：
SQL alter system set log_archive_max_processes=30 scope=both;
設(shè)置 standby 庫(kù)從哪個(gè)數(shù)據(jù)庫(kù)獲取歸檔日志（只對(duì) standby 庫(kù)有效，在主庫(kù)上設(shè)置是為了在故障切換后，主庫(kù)可以成為備庫(kù)使用）：
SQL alter system set fal_server=orcls scope=both;
設(shè)置文件管理模式，此項(xiàng)設(shè)置為自動(dòng)，不然在主庫(kù)創(chuàng)建數(shù)據(jù)文件后，備庫(kù)不會(huì)自動(dòng)創(chuàng)建：
SQL alter system set standby_file_management=auto scope=spfile;
啟用 OMF 功能：
SQL alter system set db_create_file_dest= /oradata/orcl scope=spfile;
– 如果主備庫(kù)文件的存放路徑不同，還需要設(shè)置以下兩個(gè)參數(shù)（需要重啟數(shù)據(jù)庫(kù)生效）：
SQL alter system set db_file_name_convert= /data/oradata/orcls/datafile , /data/oradata/orcl/datafile , /data/oradata/orcls/tempfile , /data/oradata/orcl/tempfile scope=spfile;
SQL alter system set log_file_name_convert= /data/oradata/orcls/redo , /data/oradata/orcl/redo scope=spfile;
這步路徑的先后順序在主備庫(kù)上的設(shè)置是不一樣的，大家要注意！
二、備庫(kù)參數(shù)設(shè)置
完成了以上步驟后，通過以下命令生成一個(gè) pfile 文件給備庫(kù)使用：
SQL create pfile= /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora from spfile;
File created.
打開生成的文件, 修改部分參數(shù)，具體如下：
– 修改后
*.audit_file_dest= /u01/app/oracle/admin/orcls/adump
*.audit_trail= db
*.compatible= 11.2.0.4.0
*.control_files= /oradata/orcls/control01.ctl , /u01/app/oracle/fast_recovery_area/orcls/control02.ctl
*.db_block_size=8192
*.db_create_file_dest= /oradata/orcls
*.db_domain=
*.db_name= orcl
*.db_unique_name= ocrls
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest=
*.diagnostic_dest= /u01/app/oracle
*.dispatchers= (PROTOCOL=TCP) (SERVICE=orclsXDB)
*.log_archive_config= dg_config=(orcl,orcls)
*.log_archive_dest=
*.log_archive_dest_1= LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls
*.log_archive_dest_2= SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl
*.log_archive_dest_state_1= ENABLE
*.log_archive_dest_state_2= ENABLE
*.log_archive_format= %t_%s_%r.arch
*.memory_target=780140544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile= EXCLUSIVE
*.standby_file_management= AUTO
*.undo_tablespace= UNDOTBS1
c) 密碼文件配置
密碼文件是創(chuàng)建 DG 不可缺少的一部分，主庫(kù)的密碼文件一般在 $ORACLE_HOME/dbs，命名格式是：orapw+db_unique_name
如果不存在此文件，我們可以通過以下命令生成一個(gè)：
#su – oracle
$cd $ORACLE_HOME/dbs
$orapwdfile=orapwocrl password=oracle
我們將密碼文件和剛才修改好的 pfile 一起拷貝到備庫(kù)的 $ORACLE_HOME/dbs 目錄下，并重命名密碼文件的名字：
備庫(kù)上修改密碼文件名和參數(shù)文件
5.listener.ora 與 tnsnames.ora 配置
這兩個(gè)文件均在 $ORACLE_HOME/network/admin 目錄下，如果沒有，可以自行創(chuàng)建一下
a) 備庫(kù)配置
listener.ora 內(nèi)容如下：
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcls)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcls)
)
)
tnsnames.ora 內(nèi)容如下：
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcl)
)
)
orcls =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcls)
)
)
重啟一下監(jiān)聽：
$lsnrctl stop
$lsnrctl start
b) 主庫(kù)配置
listener.ora 內(nèi)容如下：
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ocrls)
(ORACLE_HOME =/u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = ocrls)
)
)
tnsnames.ora 內(nèi)容如下：
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcl)
)
)
orcls =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =orcls)
)
)
執(zhí)行以下命令重啟監(jiān)聽，使配置生效：
$lsnrctl stop
$lsnrctl start
做完以上配置后，在主備庫(kù)上執(zhí)行以下命令，確保兩個(gè)主機(jī)之間網(wǎng)絡(luò)相通：
$tnsping orcls
$tnsping orcls
6. 目錄創(chuàng)建
參數(shù)和網(wǎng)絡(luò)配置好后，我們需要為備庫(kù) dump 文件創(chuàng)建相應(yīng)的目錄（對(duì)照主庫(kù) $ORACLE_BASE/admin）：
[oracle@node2 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/adump
[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/dpdump
為數(shù)據(jù)庫(kù)文件創(chuàng)建目錄（就是之前 db_file_name_convert 和 log_file_name_convert 的目錄）–/oradata
ocrls:/data/oradata/orls@standby mkdir -p/data/oradata/ocrls/redo/
ocrls:/data/oradata/ocrls@standby mkdir -p/data/oradata/ocrls/datafile/
ocrls:/data/oradata/ocrls@standby mkdir -p /data/oradata/ocrls/control/
7.RMAN 復(fù)制創(chuàng)建 standby 庫(kù)
準(zhǔn)備工作都完成了，那我們可以開始 standby 庫(kù)的創(chuàng)建了。
注：以下操作在備庫(kù)完成
a) 文件復(fù)制
先，我們使用之前修改的 pfile 把備庫(kù)啟動(dòng)到 nomount 狀態(tài)，生成 spfile：
$echo $ORACLE_SID（確認(rèn) SID 是否我們?cè)O(shè)置的）
SQL startup nomount pfile= /u01/app/oracle/product/11.2.0/db_1/dbs/initorcls.ora
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
SQL create spfile from pfile;
File created.
SQL shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL exit
從 spfile 啟動(dòng)
SQL STARTUP NOMOUNT
SQL show parameter db_unique_name;
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string orcls
SQL show parameter name;
NAME TYPE VALUE
———————————— ———– ——————————
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcls
global_names boolean FALSE
instance_name string orcls
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcls
SQL
復(fù)制數(shù)據(jù)文件，在備庫(kù)上操作
[oracle@node2 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcls
Recovery Manager: Release 11.2.0.4.0 – Production on Fri Jun 15 00:33:22 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1506854844)
connected to auxiliary database: ORCL (not mounted)
RMAN
確認(rèn)我們已經(jīng)連接上主庫(kù)和備庫(kù)后，執(zhí)行以下命令：
如果在 RMAN 恢復(fù)時(shí)不指定 nofilenamecheck 參數(shù)
則在數(shù)據(jù)文件相同文件名恢復(fù)時(shí)會(huì)出現(xiàn) RMAN-05501 錯(cuò)誤
RMAN duplicate target database for standby from active database nofilenamecheck;
命令執(zhí)行完后，可以看到主庫(kù)在開始復(fù)制文件到備庫(kù)中
復(fù)制完成后，打開數(shù)據(jù)庫(kù)開啟實(shí)時(shí)同步：
SQL ALTER DATABASE ARCHIVELOG;
SQL ALTER DATABASE OPEN;
SQL ARCHIVE LOG LIST
SQL alter database recover managed standby database using current logfile disconnect from session;
查看數(shù)據(jù)庫(kù)狀態(tài)
登陸到主庫(kù)
$sqlplus / as sysdba
SQL select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
登錄到備庫(kù)：
$sqlplus / as sysdba
SQL select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
檢查歸檔日志是否能正常傳輸（日志的序號(hào)必須是一樣的）：
主庫(kù)
SQL select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
2 17-JUN-18 18-JUN-18 NO YES
3 18-JUN-18 18-JUN-18 NO YES
4 18-JUN-18 18-JUN-18 NO YES
5 18-JUN-18 18-JUN-18 NO YES
6 18-JUN-18 19-JUN-18 NO YES
7 19-JUN-18 19-JUN-18 NO YES
8 19-JUN-18 19-JUN-18 NO YES
9 19-JUN-18 19-JUN-18 NO YES
10 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 YES YES
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
12 19-JUN-18 19-JUN-18 NO YES
12 19-JUN-18 19-JUN-18 NO YES
13 rows selected.
備庫(kù)
SQL select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 IN-MEMORY YES
b) 切換日志測(cè)試
主庫(kù)
SQL alter system switch logfile;
System altered.
SQL select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
2 17-JUN-18 18-JUN-18 NO YES
3 18-JUN-18 18-JUN-18 NO YES
4 18-JUN-18 18-JUN-18 NO YES
5 18-JUN-18 18-JUN-18 NO YES
6 18-JUN-18 19-JUN-18 NO YES
7 19-JUN-18 19-JUN-18 NO YES
8 19-JUN-18 19-JUN-18 NO YES
9 19-JUN-18 19-JUN-18 NO YES
10 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 NO YES
11 19-JUN-18 19-JUN-18 YES YES
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
12 19-JUN-18 19-JUN-18 NO YES
12 19-JUN-18 19-JUN-18 NO YES
13 19-JUN-18 19-JUN-18 NO YES
13 19-JUN-18 19-JUN-18 NO YES
15 rows selected.
備庫(kù)
SQL select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 IN-MEMORY YES
SQL select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
11 19-JUN-18 19-JUN-18 YES YES
12 19-JUN-18 19-JUN-18 YES YES
13 19-JUN-18 19-JUN-18 IN-MEMORY YES
SQL select max(sequence#)from v$archived_log;
SQL select max(sequence#)from v$archived_log;
MAX(SEQUENCE#)
————–
13
以上是“DG 如何搭建”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
