【DATAGUARD 】DG 系列之11g 物理備庫的搭建

說明：由於虛擬機器，資源有限，搭建就在同一臺伺服器上進行了。

　　　資料庫版本：oracle11.2.0.4_x64 作業系統：redhat6.3_x64

主資料庫：firsoul 備庫：sbdb

一、網路配置

監聽檔案配置

[oracle@oradb1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = firsoul)

(ORACLE_HOME = /oracle/db_1)

(SID_NAME= firsoul)

)

(SID_DESC =

(GLOBAL_DBNAME = sbdb)

(ORACLE_HOME = /oracle/db_1)

(SID_NAME= sbdb)

)

TNS 配置資訊

[oracle@oradb1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

FIRSOUL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = firsoul)

)

SBDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sbdb)

)

二、主庫的基本配置
啟用強制日誌：

sys@FIRSOUL> alter database force logging;

Database altered

修改資料庫為歸檔模式，需在mount 模式下修改（這個不再詳細介紹）：

idle> alter database archivelog;

Database altered.

idle>

idle> alter system set log_archive_dest_1='location=/oracle/archivelog' scope=spfile;

System altered.

建立一個測試使用者，並插入資料

idle> create user firsoul identified by firsoul;

User created.

idle> grant connect,resource to firsoul;

Grant succeeded.

idle> create table firsoul.test (id int,name varchar2(30));

Table created.

idle> insert into firsoul.test values(1,'firsoul');

1 row created.

idle> commit;

新增備庫日誌組，這裡主庫有三個日誌組，新增了4 組備用重做日誌4-7

sys@FIRSOUL> alter database add standby logfile group 4 '/oracle/oradata/FIRSOUL/onlinelog/redo4.log' size 200m;

Database altered

配置主庫初始化引數

sys@FIRSOUL> alter system set standby_file_management=AUTO scope=both;

System altered.

sys@FIRSOUL> alter system set log_archive_config='DG_CONFIG=(firsoul,sbdb)' scope=both;

System altered.

sys@FIRSOUL> alter system set db_file_name_convert='SBDB','FIRSOUL' scope=spfile;

System altered.

sys@FIRSOUL> alter system set log_file_name_convert='SBDB','FIRSOUL' scope=spfile;

System altered.

sys@FIRSOUL> alter system set log_archive_dest_2='SERVICE=sbdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbdb' scope=spfile;

System altered.

sys@FIRSOUL> alter system set fal_server='sbdb' scope=spfile;

System altered.

sys@FIRSOUL> alter system set fal_client='firsoul' scope=spfile;

System altered.

三、備庫基本配置及搭建
建立密碼檔案

[oracle@oradb1 archivelog]$ orapwd file=$ORACLE_HOME/dbs/orapwsbdb password=oracle

四、

建立引數檔案

[oracle@oradb1 archivelog]$ echo db_name=firsoul >$ORACLE_HOME/dbs/initsbdb.ora

測試連線是否正常（sbdb ），並啟動到nomount 模式

[oracle@oradb1 archivelog]$ mkdir -p $ORACLE_BASE/admin/sbdb/adump

[oracle@oradb1 archivelog]$ export ORACLE_SID=sbdb

[oracle@oradb1 archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 15 07:30:42 2014

Connected to an idle instance.

idle> startup nomount

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2251816 bytes

Variable Size 159384536 bytes

Database Buffers 50331648 bytes

Redo Buffers 5189632 bytes

下面使用Duplicate 命令複製資料庫

rman target sys/oracle@firsoul auxiliary sys/oracle@sbdb

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 15 09:49:28 2014

connected to target database: FIRSOUL (DBID=3530501756)

connected to auxiliary database: FIRSOUL (not mounted)

RMAN> duplicate target database

2> for standby

3> from active database

4> DORECOVER

5> spfile

6> set db_unique_name='SBDB'

7> set log_archive_dest_1='location=/oracle/sbdb_log'

8> set log_archive_dest_2='service=firsoul async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=firsoul'

9> set standby_file_management='AUTO'

10> set fal_server='firsoul'

11> set fal_client='sbdb'

12> set control_files='/oracle/oradata/sbdb/crontal01.ctl','/oracle/oradata/sbdb/control02.ctl','/oracle/oradata/sbdb/crontal03.ctl'

13> set db_file_name_convert='FIRSOUL','SBDB'

14> set log_file_name_convert='FIRSOUL','SBDB'

15> set memory_target='0'

16> set sga_target='400M';
Starting Duplicate Db at 15-JUL-14

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:

{

set until scn 1032647;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 15-JUL-14

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /oracle/sbdb_log/1_4_852888831.dbf

archived log file name=/oracle/sbdb_log/1_4_852888831.dbf thread=1 sequence=4

media recovery complete, elapsed time: 00:00:01

Finished recover at 15-JUL-14

Finished Duplicate Db at 15-JUL-14

啟動備庫

idle> shutdown abort

ORACLE instance shut down.

idle> startup mount

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2253824 bytes

Variable Size 180358144 bytes

Database Buffers 230686720 bytes

Redo Buffers 4247552 bytes

Database mounted.

idle> alter system set dg_broker_start=true;

System altered.

以只讀方式開啟資料庫，oracle 知道我們在備用資料庫控制檔案中進行裝載，所以當開啟資料時，他將自動置於只讀模式。

idle>alter database open;

為了實時查詢，啟用管理恢復

idle>alter database recover managed standby database using current logfile disconnect;

至此，oracle11g 物理dg 搭建完成，並且我們以只讀方式開啟，可以對資料進行實時查詢。

【DATAGUARD】DG系列之11g物理備庫的搭建

相關文章