DG如何搭建

這篇文章主要為大家展示了“DG如何搭建”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“DG如何搭建”這篇文章吧。

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序制作、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了蘇尼特右免費(fèi)建站歡迎大家使用！

一、主庫(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ū)別，需要在配置過(guò)程細(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è)置

完成了以上步驟后，通過(guò)以下命令生成一個(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

如果不存在此文件，我們可以通過(guò)以下命令生成一個(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目錄下，如果沒(méi)有，可以自行創(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)聽(tīng)：

$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)聽(tīng)，使配置生效：

$lsnrctl stop

$lsnrctl start

做完以上配置后，在主備庫(kù)上執(zhí)行以下命令，確保兩個(gè)主機(jī)之間網(wǎng)絡(luò)相通：

$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

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

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

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;

MAX(SEQUENCE#)

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

以上是“DG如何搭建”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

網(wǎng)頁(yè)題目：DG如何搭建
本文URL：http://bm7419.com/article32/pscesc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營(yíng)銷、標(biāo)簽優(yōu)化、外貿(mào)網(wǎng)站建設(shè)、定制網(wǎng)站、虛擬主機(jī)、手機(jī)網(wǎng)站建設(shè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容