Oracle11gDataGuard使用duplicatefromactivedatabase創建standby

用這種方式來搭建 DG ，主庫的停機時間很少，只需要重啟一下，使參數生效。也可以用這種方法進行 DB 遷移。 DG 搭建好，然后把備庫激活就可以了。這樣整個遷移中宕機時間也比較短。 Oracle 11g 的 pyhsical standby 支持 open read only 下的 apply 和 Real-

用這種方式來搭建DG ，主庫的停機時間很少，只需要重啟一下，使參數生效。也可以用這種方法進行DB遷移。DG搭建好，然后把備庫激活就可以了。 這樣整個遷移中宕機時間也比較短。

Oracle 11g的pyhsical standby 支持open read only 下的apply和Real-time query。 因此就有了physical standby 穩定和logical standby 的報表查詢功能。

Oracle： 11.2.0.1

OS： redhat 5.5

Primary IP: 192.168.2.42

DB_NAME=sanfu

Standby IP: 192.168.2.43

DB_NAME=sanfu

一. Primary 端操作：

1. 設置歸檔模式

這個生產庫都是這種模式。

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

2. Primary 設置force logging

SQL> alter database force logging;

SQL> select force_logging from v$database;

FORCE_LOG

---------

YES

3. 配置Oracle Net

在Primary 庫和Standby 都要修改。也可以使用netca 和netmgr命令配置。

注意：修改完后記得重啟listener。

Listener.ora

[oracle@qs-dmm-rh1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost6.localdomain6)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcl)

)

)

-- 配置靜態注冊

tnsname.ora

[oracle@qs-dmm-rh1 admin]$ cat tnsnames.ora

sanfus =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.43)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

sanfup =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.42)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

4. 添加data guard 參數

創建pfile, 添加如下文件：

SQL> create pfile from spfile;

*.db_name='orcl'

*.db_unique_name='sanfup'

*.log_archive_config='dg_config=(sanfup,sanfus)'

*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sanfup'

*.log_archive_dest_2='service=sanfus reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sanfus'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='sanfus'

*.fal_client='sanfup'

注意：

在Oracle 11g的Data Guard中，standby_archive_dest參數已經被取消了。

Standby歸檔文件的存放位置按如下規則來進行：

（1）當LOG_ARCHIVE_DEST_n設置了valid_for=(all_logfiles,all_roles)，那么在不定義standby_archive_dest參數時，Oracle就會選擇LOG_ARCHIVE_DEST_n參數作為歸檔目標。

（2）如果在第一步設置的同時，又獨立設置LOG_ARCHIVE_DEST_n參數為 valid_for=(standby_logfile,*) 屬性，那么當compatible參數大于10.0的時候，會自動的選擇任意一個LOG_ARCHIVE_DEST_n的值。

（3）如果LOG_ARCHIVE_DEST_n 沒有設置的話，默認位置是：

$ORACLE_HOME/dbs.

不過valid_for參數的默認值就是all_logfiles和all_roles. 所以只要設置了本地的歸檔位置，遠程的歸檔文件也會放到這個目錄下面。

5. 用新pfile重啟主庫

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1336960 bytes

Variable Size 310380928 bytes

Database Buffers 104857600 bytes

Redo Buffers 6094848 bytes

Database mounted.

Database opened.

SQL>

二. Standby 端設置：

1. 創建相關目錄結構

[oracle@qs-dmm-rh2 trace]$ mkdir -p /u01/app/oracle/oradata/dave

--這里我們創建的目錄和Target 庫不同，我們在參數文件里需要轉換一下。

2. 創建standby的口令文件

[oracle@qs-dmm-rh2 trace]$ orapwd file=?/dbs/orapwdave password=oracle

3. 創建standby的初始化參數：

*.compatible='11.2.0.4.0'

*db_name=orcl

*.control_files='/u01/app/oracle/oradata/dave/control01.ctl', '/u01/app/oracle/oradata/dave/control02.ctl',

'/u01/app/oracle/oradata/dave/control03.ctl'

*.db_unique_name='sanfus'

*.log_archive_config='dg_config=(sanfup,sanfus)'

*.log_archive_dest_1='location=/u01/archivelog valid_for=(standby_logfile, standby_role) db_unique_name=sanfus'

*.log_archive_dest_2='service=sanfup reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sanfup'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='sanfup'

*.fal_client='sanfus'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

4. 用pfile 將standby 啟動到nomount狀態：

SQL> startup nomount pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

5. 開始duplicate

[oracle@qs-dmm-rh2 dbs]$ rman target sys/oracle@sanfup auxiliary sys/oracle@sanfus

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 16:10:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1272955137)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 08-MAR-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' ;

--用duplicate 創建standby 時會復制口令文件

}

executing Memory Script

Starting backup at 08-MAR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=45 device type=DISK

Finished backup at 08-MAR-11

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/dave/control01.ctl';

restore clone controlfile to '/u01/app/oracle/oradata/dave/control02.ctl' from

'/u01/app/oracle/oradata/dave/control01.ctl';

restore clone controlfile to '/u01

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com