关于oracle的物理dg,Oracle11g 创建物理DG/创建逻辑DG

1.配置DG为物理standby

主备配置LISTENER.ORA

[oracle@primary admin]$ cat

listener.ora

# listener.ora Network Configuration File:

/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION

=

(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =

1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC

=

(GLOBAL_DBNAME = tianbaobao)

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

(SID_NAME = tianbaobao)

)

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@primary dbs]$ lsnrctl

reload

[oracle@standby admin]$ cat

listener.ora

# listener.ora Network Configuration File:

/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION

=

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =

1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC

=

(GLOBAL_DBNAME = tianbaobao)

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

(SID_NAME = tianbaobao)

)

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@standby dbs]$ lsnrctl

reload

主备配置TNSNAMES.ORA

[oracle@primary admin]$ cat

tnsnames.ora

# tnsnames.ora Network Configuration File:

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =

1521))

)

(CONNECT_DATA =

(SERVICE_NAME = tianbaobao)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =

1521))

)

(CONNECT_DATA =

(SERVICE_NAME = tianbaobao)

)

)

[oracle@standby admin]$ cat

tnsnames.ora

# tnsnames.ora Network Configuration File:

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =

1521))

)

(CONNECT_DATA =

(SERVICE_NAME = tianbaobao)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =

1521))

)

(CONNECT_DATA =

(SERVICE_NAME = tianbaobao)

)

)

主库参数配置:

开启归档模式

SQL> archive log

list

Database log

mode Archive Mode

Automatic

archival Enabled

Archive

destination USE_DB_RECOVERY_FILE_DEST

Oldest online log

sequence 3

Next log sequence to archive 5

Current log

sequence 5

SQL>

FORCE

LOGGING设置为YES,避免一些操作不记录日志,从而无法保存两端数据一致。

SQL> alter database force

logging;

Database altered.

SQL> select force_logging from

v$database;

FOR

---

YES

设置配置DG相关参数

SQL> alter system set

db_unique_name='primary'

scope=spfile;

System altered.

SQL> alter system set

log_archive_config='DG_CONFIG=(primary,standby)';

System altered.

SQL> alter system set

log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/

valid_for=(all_logfiles,all_roles) db_unique_name=primary'

scope=spfile;

System altered.

SQL> alter system set

log_archive_dest_2='SERVICE=standby

valid_for=(online_logfiles,primary_role) db_unique_name=standby'

scope=spfile;

System altered.

SQL> alter system set

log_archive_format='arch_%r_%t_%s.arc'

scope=spfile;

System altered.

SQL> alter system set

fal_client='primary' scope=spfile;

System altered.

SQL> alter system set

fal_server='standby'

scope=spfile;

System altered.

SQL> alter system set

standby_file_management=AUTO;

System altered.

添加STANDBY REDO LOG,便与以后切换后使用。

SQL> alter database add standby

logfile group 4

'/u01/app/oracle/oradata/tianbaobao/standby_redo04.log' size

50M;

Database altered.

SQL> alter database add standby

logfile group 5

'/u01/app/oracle/oradata/tianbaobao/standby_redo05.log' size

50M;

Database altered.

SQL> alter database add standby

logfile group 6

'/u01/app/oracle/oradata/tianbaobao/standby_redo06.log' size

50M;

Database altered.

SQL> alter database add standby

logfile group 7

'/u01/app/oracle/oradata/tianbaobao/standby_redo07.log' size

50M;

Database altered.

SQL> shutdown

immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed

Size 2253664 bytes

Variable Size 1493175456

bytes

Database Buffers 100663296

bytes

Redo

Buffers 7319552 bytes

Database mounted.

Database opened.

SQL>

主库拷贝密码文件至备库

[oracle@primary tianbaobao]$ scp

/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtianbaobao

standby:/u01/app/oracle/product/11.2.0/db_1/dbs/

主备TNSPING,IT'S OK?

[oracle@primary admin]$ tnsping

primary

[oracle@primary admin]$ tnsping standby

[oracle@standby admin]$ tnsping primary

[oracle@standby admin]$ tnsping standby

备库上创建基本目录

[oracle@standby admin]$ mkdir -p

/u01/app/oracle/admin/tianbaobao/{adump,dpdump,pfile} [oracle@standby admin]$ mkdir -p

/u01/app/oracle/oradata/tianbaobao

[oracle@standby admin]$ mkdir -p

/u01/app/oracle/fast_recovery_area/tianbaobao

备库创建PFILE文件,这个位置注意DBNAME,11g限制最长8位。

[oracle@standby dbs]$ echo

'db_name=tianbaob' >

/u01/app/oracle/product/11.2.0/db_1/dbs/inittianbaobao.ora

启动备库到NOMOUNT

[oracle@standby dbs]$ sqlplus / as

sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 19:07:34

2015

Copyright (c) 1982, 2013, Oracle. All rights

reserved.

Connected to an idle instance.

SQL> 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

SQL>

DUPLICATE TO STANDBY开始

[oracle@primary dbs]$ rman

target sys/oracle@primary

auxiliary sys/oracle@standby

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 19

18:57:44 2015

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database: TIANBAOB (DBID=2088615787)

connected to auxiliary database (not started)

RMAN> run{

2> allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> allocate auxiliary channel c3 device type disk;

5> allocate auxiliary channel c4 device type disk;

6> duplicate target database for standby from active database

nofilenamecheck

7> dorecover

8> spfile

9> parameter_value_convert 'primary','standby'

10> set db_unique_name='standby'

11> set

log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/

valid_for=(all_logfiles,all_roles) db_unique_name=standby'

12> set log_archive_dest_2='SERVICE=primary valid_for=(online_logfile,primary_role)

db_unique_name=primary'

13> set log_archive_max_processes='5'

14> set standby_file_management='AUTO'

15> set fal_client='standby'

16> set fal_server='primary';

17> release channel c1;

18> release channel c2;

19> release channel c3;

20> release channel c4;

21> }

备库检查

[oracle@standby dbs]$ sqlplus / as

sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 19:12:46

2015

Copyright (c) 1982, 2013, Oracle. All rights

reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application

Testing options

SQL> select

instance_name,status from gv$instance;

INSTANCE_NAME STATUS

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

tianbaobao MOUNTED

SQL> alter database

open;

Database altered.

SQL> select

open_mode,name,database_role,db_unique_name from

v$database;

OPEN_MODE NAME DATABASE_ROLE DB_UNIQUE_NAME

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

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

READ

ONLY TIANBAOB PHYSICAL STANDBY standby

SQL>

应用归档日志方式进行数据同步

SQL> alter system set

log_archive_dest_2='SERVICE=standby arch noaffirm

valid_for=(online_logfiles,primary_role) db_unique_name=standby'

scope=spfile;

System altered.

SQL>

应用在线日志方式进行数据同步

SYNC:

SQL> alter system set

log_archive_dest_2='SERVICE=standby lgwr sync affirm

valid_for=(online_logfiles,primary_role) db_unique_name=standby'

scope=spfile;

System altered.

ASYNC:

SQL> alter system set

log_archive_dest_2='SERVICE=standby lgwr async noaffirm

valid_for=(online_logfiles,primary_role)

db_unique_name=standby';

System altered.

SQL>

配置Active Standby

SQL> select open_mode from

v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database recover

managed standby database cancel;

Database altered.

SQL> alter database

open;

Database altered.

SQL> select open_mode from

v$database;

OPEN_MODE

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

READ ONLY

SQL> alter database recover

managed standby database using current logfile

disconnect;

Database altered.

SQL> select open_mode from

v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL>

状态“READ ONLY WITH

APPLY”即表示此时备库处于Read

Only状态的同时可以接受主库传过来的日志进行恢复,以便达到备库可以即时查看到主库变化的目的。

2.配置Standby之最大性能,最大高可用和最大保护模式

STANDBY之最大保护模式

SQL> shutdown

immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed

Size 2253664 bytes

Variable Size 1493175456

bytes

Database Buffers 100663296

bytes

Redo

Buffers 7319552 bytes

Database mounted.

SQL> alter database set standby

database to maximize protection;

Database altered.

SQL> alter database open;

Database altered.

SQL> select

open_mode,name,database_role,protection_mode,protection_level,db_unique_name

from v$database;

OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME

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

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

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

READ

WRITE TIANBAOB PRIMARY MAXIMUM

PROTECTION MAXIMUM

PROTECTION primary

STANDBY之最大可用模式

SQL> shutdown

immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed

Size 2253664 bytes

Variable Size 1493175456

bytes

Database Buffers 100663296

bytes

Redo

Buffers 7319552 bytes

Database mounted.

SQL> alter database set standby

database to maximize availability;

Database altered.

SQL> alter database

open;

Database altered.

SQL> select

open_mode,name,database_role,protection_mode,protection_level,db_unique_name

from v$database;

OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME

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

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

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

READ

WRITE TIANBAOB PRIMARY MAXIMUM AVAILABILITY

MAXIMUM AVAILABILITY primary

STANDBY之最大性能模式

SQL> alter system set

log_archive_dest_2='SERVICE=standby arch noaffirm

valid_for=(online_logfiles,primary_role)

db_unique_name=standby';

System altered.

SQL> shutdown

immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed

Size 2253664 bytes

Variable Size 1493175456

bytes

Database Buffers 100663296

bytes

Redo

Buffers 7319552 bytes

Database mounted.

SQL> alter database set standby

database to maximize performance;

Database altered.

SQL> alter database

open;

Database altered.

SQL> select

open_mode,name,database_role,protection_mode,protection_level,db_unique_name

from v$database;

OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME

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

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

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

READ

WRITE TIANBAOB PRIMARY MAXIMUM

PERFORMANCE MAXIMUM PERFORMANCE primary

3.配置DG为逻辑standby模式

1.创建physical standby

2.备库停止apply日志文件

SQL> alter database recover

managed standby database cancel;

Database altered.

SQL>

3.修改主库参数文件

设置归档路径用于存放由primary传过来后生成的standby归档文件,参数log_archive_dest_N

valid_for部分修改成STANDBY_LOGFILES,STANDBY_ROLE,如果你以后不想主库转换成逻辑standby

角色可以跳过此步。

alter system set log_archive_dest_3='location=/u01/app/oracle/fast_recovery_area/standbyarch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=standby';

4.生成LogMiner字典信息

避免有些特定操作备库无法接受sql语句,故而生成记录的元数据信息到redo

log,这里需要注意备库的,shared_pool_size,undo_retention大小是否满足需求。

SQL> exec

dbms_logstdby.build;

PL/SQL procedure successfully completed.

SQL>

5.物理standby转换逻辑standby。

这里要注意查看一下双向listener是否开启,否认报错如下,后一直处于等待状态,启动listener后正常转换成功。

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.4.0 -

Production

TCP/IP NT Protocol Adapter for Linux: Version

11.2.0.4.0 - Production

Time: 21-MAR-2015 11:12:13

Tracing not turned on.

Tns error struct:

ns main err

code: 12541

TNS-12541: TNS:no listener

ns secondary

err code: 12560

nt main err

code: 511

TNS-00511: No listener

nt secondary

err code: 111

nt OS err

code: 0

SQL> alter database recover to

logical standby standby;

Database altered.

SQL>

6.OPEN逻辑standby,开启redo实时应用。

用resetlogs方式打开逻辑standby,开启redo应用,为逻辑standby添加standby redologs日志组,以便实时应用redo数据

SQL> shutdown

immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

mount;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed

Size 2253664 bytes

Variable Size 1493175456

bytes

Database Buffers 100663296

bytes

Redo

Buffers 7319552 bytes

Database mounted.

SQL> alter database open

resetlogs;

Database altered.

SQL> alter database start

logical standby apply immediate;

Database altered.

关闭redo应用,开启redo应用

SQL> alter database stop logical

standby apply;

Database altered.

SQL> alter database start

logical standby apply;

Database altered.

7.检查状态

SQL> select

open_mode,name,database_role,protection_mode,protection_level,db_unique_name

from v$database;

OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME

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

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

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

READ

WRITE STANDBY LOGICAL

STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

standby

SQL>

[root@standby ~]# ps -ef|grep

mrp

root 2873 2852 0 11:41

pts/2 00:00:00 grep mrp

[root@standby ~]# ps -ef|grep

lsp

oracle 2823 1 0 11:38

? 00:00:00 ora_lsp0_tianbaobao

root 2875 2852 0 11:41

pts/2 00:00:00 grep lsp

IT'

OVER!!!!

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值