【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 搭建完成，并且我们以只读方式打开，可以对数据进行实时查询。

ITPub博客

【DATAGUARD】DG系列之11g物理备库的搭建

最新文章