溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要為大家展示了“DG如何搭建”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“DG如何搭建”這篇文章吧。
一、主庫
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強制日志
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
從圖中可以看到我們主庫有三組大小為50M的redo logfile，故我們也需要創(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ù)據(jù)庫口令文件的使用模式
查看remote_login_passwordfile的值是否EXCLUSIVE
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
如果不是，執(zhí)行以下命令進行設置，并且重啟數(shù)據(jù)庫，使其生效：
SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
SQL>shutdown immediate;
SQL>startup;
5參數(shù)（文件）設置
SQL> show parameter db_unique_name;
DG的搭建需要修改許多數(shù)據(jù)庫的參數(shù)，并且部分參數(shù)主備庫之間有點區(qū)別，需要在配置過程細心一點。
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填寫的是主備庫的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ù)涉及切換
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;
啟用設置的日志路徑
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ù)量（視實際情況調整）：
SQL>alter system set log_archive_max_processes=30 scope=both;
設置standby庫從哪個數(shù)據(jù)庫獲取歸檔日志（只對standby庫有效，在主庫上設置是為了在故障切換后，主庫可以成為備庫使用）：
SQL>alter system set fal_server=orcls scope=both;
設置文件管理模式，此項設置為自動，不然在主庫創(chuàng)建數(shù)據(jù)文件后，備庫不會自動創(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;
--如果主備庫文件的存放路徑不同，還需要設置以下兩個參數(shù)（需要重啟數(shù)據(jù)庫生效）：
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;
這步路徑的先后順序在主備庫上的設置是不一樣的，大家要注意！
二、備庫參數(shù)設置
完成了以上步驟后，通過以下命令生成一個pfile文件給備庫使用：
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不可缺少的一部分，主庫的密碼文件一般在$ORACLE_HOME/dbs，命名格式是：orapw+db_unique_name
如果不存在此文件，我們可以通過以下命令生成一個：
#su - oracle
$cd $ORACLE_HOME/dbs
$orapwdfile=orapwocrl password=oracle
我們將密碼文件和剛才修改好的pfile一起拷貝到備庫的$ORACLE_HOME/dbs目錄下，并重命名密碼文件的名字：
備庫上修改密碼文件名和參數(shù)文件
5.listener.ora與tnsnames.ora配置
這兩個文件均在$ORACLE_HOME/network/admin目錄下，如果沒有，可以自行創(chuàng)建一下
a)備庫配置
listener.ora內容如下：
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內容如下：
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)主庫配置
listener.ora內容如下：
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內容如下：
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
做完以上配置后，在主備庫上執(zhí)行以下命令，確保兩個主機之間網(wǎng)絡相通：
$tnsping orcls
$tnsping orcls
6.目錄創(chuàng)建
參數(shù)和網(wǎng)絡配置好后，我們需要為備庫dump文件創(chuàng)建相應的目錄（對照主庫$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ù)庫文件創(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復制創(chuàng)建standby庫
準備工作都完成了，那我們可以開始standby庫的創(chuàng)建了。
注：以下操作在備庫完成
a)文件復制
先，我們使用之前修改的pfile把備庫啟動到nomount狀態(tài)，生成spfile：
$echo $ORACLE_SID （確認SID是否我們設置的）
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啟動
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>
復制數(shù)據(jù)文件，在備庫上操作
[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>
確認我們已經連接上主庫和備庫后，執(zhí)行以下命令：
如果在RMAN恢復時不指定 nofilenamecheck 參數(shù)
則在數(shù)據(jù)文件相同文件名恢復時會出現(xiàn)RMAN-05501錯誤
RMAN> duplicate target database for standby from active database nofilenamecheck;
命令執(zhí)行完后，可以看到主庫在開始復制文件到備庫中
復制完成后，打開數(shù)據(jù)庫開啟實時同步：
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ù)庫狀態(tài)
登陸到主庫
$sqlplus / as sysdba
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
登錄到備庫：
$sqlplus / as sysdba
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
檢查歸檔日志是否能正常傳輸（日志的序號必須是一樣的）：
主庫
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.
備庫
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)切換日志測試
主庫
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.
備庫
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如何搭建”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業(yè)資訊頻道！
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經查實，將立刻刪除涉嫌侵權內容。
