ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle dataguard oracle11g

oracle dataguard oracle11g

原创 Linux操作系统 作者:zhengbao_jun 时间:2013-08-21 11:55:05 0 删除 编辑
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》 | 关闭

esy666

无他,唯手熟尔

日志

oracle11g部署Data Guard物理standby部署详细步骤

2012-09-30 23:33:33| 分类: DataGuard | 标签: |字号 订阅

运行环境:vmware workstation 7;

操作系统版本:Linux centos 4.8(Linux oracle11g 2.6.9-89.ELsmp #1 SMP Mon Jun 22 12:32:43 EDT 2009 i686 athlon i386 GNU/Linux)

内核版本: 2.6.9-89.ELsmp

oracle 版本:racle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

测试环境参数:

主库(安装Oracle及数据库)

Primary Database

备库(只安装Oracle软件)

Standby Database

IP地址

192.168.9.88

192.168.9.77

SID:

prod

stby

DB_UNIQUE_NAME

prod

stby

数据文件路径

/u01/app/oracle/oradata/prod/

/u01/app/oracle/oradata/prod/

归档日志/standby日志路径

/u01/app/oracle/oradata/prod/

/u01/app/oracle/oradata/prod/

网络配置要求:

1.两台服务器之间网络畅通,双方都能够互相Ping通(另外用sqlplus sys/sys@stby as sysdba 和

sqlplus sys/sys@prod as sysdba能够登录到数据库,不然listener.ora里面的配置就有问题,后面启动主库,备库的时候,查询查询通过主库的v$archive_dest视图查看归档日志的发送状态的时候,会报备库不可用错误

)。

2.设置主库与备库服务器的时间同步,vmware workstation 7可以参考:

http://blog.163.com/yanenshun@126/blog/static/128388169201271111930807/,也可以date或者system-config-date命令设置。

3.设置主备库的listener.oratnsnames.ora,这两个文件在linux/unix环境下默认存放在$ORACLE_HOME/network/admin目录下;

**********************************************

主库的listener.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(PROGRAM = extproc)
) (SID_DESC =
(GLOBAL_DBNAME = prod.esy)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = prod)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.88)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

主库的tnsnames.ora:

prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.esy)
)
)
stby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby.esy)
)
)

**********************************************

备库的tnsnames.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.77)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(PROGRAM = extproc)
) (SID_DESC =
(GLOBAL_DBNAME = stby.esy)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = stby)
)
)

备库的tnsnames.ora:

stby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby.esy)
)
)
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod.esy)
)
)

4. 在主库上使用orapwd命令创建口令文件,并且复制到备库指定位置改名

orapwd file='$ORACLE_HOME/dbs/orapwprod' password=sys entries=20

主库的口令文件(-rw-r----- 1 oracle oinstall 3584 Sep 27 22:03 orapwprod)

备库的口令文件(-rw-r----- 1 oracle oinstall 3584 Sep 27 22:08 orapwstby)

5. Enable Forced Logging

确认将主库置于force logging模式,可用select name,FORCE_LOGGING from v$database;命令查看force logging模式,如果不是yes的话,使用这个命令修改:Alter database force logging;

6. Enable Archiving 主库处于归档模式

是否归档状态可用log archive list 命令或select name,log_mode from v$database查看,如果不是归档状态需要先设置归档日志存放位置并开启归档模式。

设置归档日志存放位置:

alter system set log_archive_dest_1=’//u01/app/oracle/oradata/prod/’;

开启归档模式:在mount状态下执行alter database archivelog;

7. 设置standby redologs;


alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prod/redo04.log') size 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/prod/redo05.log') size 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/prod/redo06.log') size 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/prod/redo07.log') size 50M;

oracle推荐将standby redologs日志组比主库online redologs日志组至少要多一个,这需要视你的工作量而定,standby redologs添加删除语法与online redologs一样,只是多了一个standby关键字;

我的库里面的日志组文件:

select group#,type,member from v$logfile;

3 ONLINE /u01/app/oracle/oradata/prod/redo03.log
2 ONLINE /u01/app/oracle/oradata/prod/redo02.log
1 ONLINE /u01/app/oracle/oradata/prod/redo01.log
4 STANDBY /u01/app/oracle/oradata/prod/redo04.log
5 STANDBY /u01/app/oracle/oradata/prod/redo05.log
6 STANDBY /u01/app/oracle/oradata/prod/redo06.log
7 STANDBY /u01/app/oracle/oradata/prod/redo07.log

4. Set Primary Database Initialization Parameters 设置主库初始化参数文件

可以先获取pfile文件,方便编辑修改参数(create pfile from spfile;然后将spfile改名,让数据库启动用pfile,红色部分是要修改的部分


[oracle@oracle11g dbs]$ cat initprod.ora
prod.__db_cache_size=83886080
prod.__java_pool_size=12582912
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=209715200
prod.__sga_target=314572800
prod.__shared_io_pool_size=0
prod.__shared_pool_size=205520896
prod.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl','/u01/app/oracle/oradata/prod/control03.ctl'
*.db_block_size=8192
*.db_domain='esy'
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_client='prod'
*.fal_server='stby'
*.log_archive_config='dg_config=(prod,stby)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/prod valid_for=(all_logfiles,all_roles) db_unique_name=prod'
*.log_archive_dest_2='SERVICE=stby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=stby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=523239424
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

9. Set Primary Standby Database Initialization Parameters 设置物理备库的参数文件

[oracle@oracle11g dbs]$ cat initstby.ora
prod.__db_cache_size=125829120
prod.__java_pool_size=12582912
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=180355072
prod.__sga_target=343932928
prod.__shared_io_pool_size=0
prod.__shared_pool_size=192937984
prod.__streams_pool_size=0
#*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl','/u01/app/oracle/oradata/prod/control03.ctl'
*.db_block_size=8192
*.db_domain='esy'
*.db_name='prod'
*.db_unique_name='stby'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_client='stby'
*.fal_server='prod'
*.log_archive_config='dg_config=(prod,stby)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/prod valid_for=(all_logfiles,all_roles) db_unique_name=stby'
*.log_archive_dest_2='SERVICE=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=523239424
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

10. 在主库中创建备库的控制文件,注意备库的控制文件要在主库的MOUNT状态下创建。

Sql>startup mount

Sql>alter database create standby controlfile as ‘/home/oracle/control01.ctl’;

Sql>alter database open;

11 复制相关文件到备库,包括所有数据文件、日志文件、新建的控制文件和已归档的数据文件(如果有归档文件,需要已归档的数据文件,不然备库打开至read only状态,会报错,缺少归档文件)。

可以使用scp命令:

[oracle@oracle11g dbs]$ scp prod/ oracle@192.168.1.77:/u01/app/oracle/oradata/prod/

12 保证主库,备库的lisnrctl都是开启的,不然打开主库的时候,alert会报连不上备库(Fatal NI connect error 12514)

13 下面就开始正式的启动Data Guard

SYSDBA身份连到备库,

然后启动备库到mount状态

Sql>alter database recover managed standby database disconnect from session;

Oracle 11g可以在一边接收的同时还支持read only查询,这点是10g 做不到的,启到实时接收用这条命令:alter database recover managed standby database using current logfile disconnect from session; disconnect from session子句是可选项,意思是命令执行完毕后自动断开连接,否则会一直处在执行的状态,想继续操作的话只能另开窗口。

为了测试部署是否成功,可以在主库上进行一些操作,然后进行日志切换,比如:

Sql>Create table xxx as Select * from dba_objects;

Sql>alter system switch logfile;

可以通过主库的v$archive_dest视图查看归档日志的发送状态

Select status,dest_name,error from v$archive_dest where rownum<5;

如果error列中有内容,说明主库的归档日志发送不成功,请依据提示解决错误。

现在看看备库的是否同步主库的操作了,首先停止备库的接收状态,然后启动为read only查询状态:

Standby Sql>alter database recover managed standby database cancel;

Standby Sql>alter database open read only;

可以查看v$archive_log视图与主库对比,如果最大sequence#号都是一致的话说明备库成功接收了主库发送过来的日志。

Standby sql>select count(1) from xxx

********************************一般常见错误*************************************

一 如果在登录备库是出现如下错误
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Sep 27 22:08:46 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> startup nomount
ORA-01031: insufficient privileges
!

需用sys用户的密码登陆:

[oracle@oracle11g dbs]$ sqlplus sys/sys as sysdba;

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Sep 27 22:10:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1301024 bytes
Variable Size 306185696 bytes
Database Buffers 209715200 bytes
Redo Buffers 5906432 bytes
SQL>

评论这张
转发至微博
转发至微博
0 | 分享到:
阅读(463)| 评论(0)| 转载 (0) |举报

历史上的今天

评论

点击登录|昵称:
取消

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/235507/viewspace-768956/,如需转载,请注明出处,否则将追究法律责任。

上一篇: rman 的备份方案
请登录后发表评论 登录
全部评论

注册时间:2008-08-08

  • 博文量
    209
  • 访问量
    866039