注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

涅槃

文档收藏

 
 
 

日志

 
 
 
 

【转载】oracle11g部署Data Guard物理standby部署详细步骤  

2013-11-05 09:53:26|  分类: oracle_dataguard |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

运行环境: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>

 

 

  评论这张
 
阅读(571)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018