ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle DG(Data Guard)创建

Oracle DG(Data Guard)创建

原创 Oracle 作者：jieyu119 时间：2013-04-19 14:30:57 0 删除 编辑



1.DG 进程名词解释.

RFS(reomte file services) write redo log or archive log to standby redo log or archive).

MRPn(managed recovery process) log apply to standby database.

 

2.DG平台环境.

主库与备库OS平台与Oracle版本相同.

OS: Linux RHEL5U5(32bit)

Oracle: 11.2.0.3.0(32bit)

 

Host hostname      ip          db_unique_name  Net service name

主库  TESTDB1     192.168.160.131    jiedb01         jiedb01

备库  TESTDB1-sty 192.168.160.132    jiedb01dg       jiedb01dg

 

Primary DB:


3.主库启用强制日志模式.

sys>alter database force logging.


4.启动归档模式.

sys>archive log list;

可以用以上命令查看归档模式是否开启,如果没有开启归档模式,DB需要启动到mount状态,用sql语句alter database archivelog开启.

 

5.主库创建密码文件,从而使OS验证的方式登陆.

orapwd file=/u01/product/oracle/dbs/orapwjiedb01 password=oracledba entries=5;

 

6.创建standby logfile.

  在maximize performance可以省略,但是在maximize protection或maximize availability必须建立,如果dg后续会从maximize performance转化为maximize protection或maximize availability,建议创建standby logfile. 创建standby logfile时,不要与online logfile日志组号相同.查看当前主库online logfile创建了4组日志, 故standby logfile日志组要添加5组,standby logfiles日志组数公式  (online日志组数+1)*线程数(thread#), standby logfiles日志成员大小要与online logfiles成员大小相同,且日志成员尽量冗余.

sys>alter database add standby logfile group 5 ('/u01/product/oradata/jiedb01/redo05a.log','/data/jiedb01/redo05b.log') size 50M;

sys>alter database add standby logfile group 6 ('/u01/product/oradata/jiedb01/redo06a.log','/data/jiedb01/redo06b.log') size 50M;

sys>alter database add standby logfile group 7 ('/u01/product/oradata/jiedb01/redo07a.log','/data/jiedb01/redo07b.log') size 50M;

sys>alter database add standby logfile group 8 ('/u01/product/oradata/jiedb01/redo08a.log','/data/jiedb01/redo08b.log') size 50M;

sys>alter database add standby logfile group 9 ('/u01/product/oradata/jiedb01/redo09a.log','/data/jiedb01/redo09b.log') size 50M;

 

7.修改主库初始化参数,创建pfile.

sys>create pfile from spfile;

    vim initjiedb01.ora(如下pfile中为添加的内容) 

*.db_unique_name=jiedb01
*.log_archive_config='dg_config=(jiedb01,jiedb01dg)'
*.log_archive_dest_1='location=/data/jiedb01/arch valid_for=(all_logfiles,all_roles) db_unique_name=jiedb01'
*.log_archive_dest_2='service=jiedb01dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=jiedb01dg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_client=jiedb01
*.fal_server=jiedb01dg
*.standby_file_management='AUTO'

sys>shutdown immediate

sys>startup nomount pfile='/u01/product/oracle/dbs/initjiedb01.ora';

sys>create spfile from pfile  --根据修改后的pfile文件,创建新的spfile.因为db启动会优先选择spfile,为后续db启动选择正确的spfile.

----注: 此处我的主库可以停库. 如果db主库为7*24小时模式,那只能在用sql命令 alter system set xxx(参数) scope=spfile来修改spfile; 我的主库与备库data file与log file路径相同,故参数db_file_name_convert与log_file_name_convert可以省略, 如果主/备库data file与log file路径不相同,一定要设置两个参数值.


8.主库进行备库(RMAN备份)

rman target /

rman>backup database format='/data/backup/rman_backup/%d_%s.bak';

 

9.主库创建备库controlfile快照文件.

alter database create standby controlfile as '/tmp/dgcontrol01.ctl';

 

Standby DB:

 

10.创建备库环境变量与相关目录.

如果备库只安装oracle软件,未安装oracle库,就要创建ORACLE_BASE,ORACLE_HOME,udump,cdump,adump等目录.此处我的备库已经安装了oracle空库,以上目录已经创建.

 

11.备库创建口令文件.

因为我的主/备库, 环境变量ORACLE_SID相同为jiedb01,故直接copy主库口令文件到备库上使用即可(oracle 口令文件命令格式orapwORACLE_SID). 如果主/备库用户创建的密码文件不相同,备库上,用户创建时, 一定要保护主库与备库的口令文件密码相同.

 

12.主库与备库上,创建listener与配置tnsname.

创建监听器listener可以用命令netca,netmgr或直接修改$ORACLE_HOME/network/admin/listener.ora文件创建.

创建tname.ora可以用命令netmgr或$ORACLE_HOME/network/admin/tnsnames.ora文件创建.

注意之处,主库与备库创建后的tnsname相同,如下.

jiedb01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.131)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jiedb01)
    )
  )

LISTENER_JIEDB01 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB1)(PORT = 1526))


jiedb01dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.132)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jiedb01dg)
    )
  )

启动主库与备库的监听器,并在主库与备库上相互tnsping jiedb01, tnsping jiedb01dg网络名,查看主/备库能否相互tnsping通.

 

13.copy主库上产生的rman备份与备库快照controlfile到备库.

主库上.

cd /data/backup/rman_backup/;

scp JIEDB01_* 192.168.160.132:/data/backup/rman_backup/

scp /tmp/dgcontrol01.ctl 192.168.160.132:/tmp/

 

14.创建备库pfile初始化参数文件.

因为我的主/备库, 环境变量ORACLE_SID相同为jiedb01,故主/备库的initjiedb01.ora初始化参数文件名称相同.

从主库scp initjiedb01.ora文件到备库并修改参数值. 参数中dg db_unique_name主/备库的不相同, log_archive_dest_2中service指定的值与fal_client,fal_server为网络名.

scp initjiedb01.ora 192.168.160.132:/u01/product/oracle/dbs/         --主库上

vim initjiedb01.ora    ---备库上, pfile添加如下参数值.

*.control_files='/u01/product/oradata/jiedb01/dgcontrol01.ctl','/u01/product/oradata/jiedb01/dgcontrol02.ctl','/data/jiedb01/dgcontrol03.ctl'

*.db_unique_name=jiedb01dg
*.log_archive_config='dg_config=(jiedb01,jiedb01dg)'
*.log_archive_dest_1='location=/data/jiedb01/arch valid_for=(all_logfiles,all_roles) db_unique_name=jiedb01dg'                                
*.log_archive_dest_2='service=jiedb01 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=jiedb01'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_client=jiedb01dg
*.fal_server=jiedb01
*.standby_file_management='AUTO'

 

copy 主库传过来的备库快照controlfile,到如上pfile初始化参数中,control_files设置的路径中.

cp /tmp/dgcontrol01.ctl  /u01/product/oradata/jiedb01/dgcontrol01.ctl

cp /tmp/dgcontrol01.ctl  /u01/product/oradata/jiedb01/dgcontrol02.ctl

cp /tmp/dgcontrol01.ctl  /data/jiedb01/dgcontrol03.ctl

 

15.启动备库,并创建spfile.

sys>startup nomount pfile='/u01/product/oracle/dbs/initjiedb01.ora';

sys>create spfile from pfile;

sys>alter database mount standby database;

 

16.备库rman进行恢复.

rman target /

rman>restore database;

 

17.备库上进行redo logfile日志应用.

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

sys> select process,pid,status from v$managed_standby; --如上sql备库产生mrp进程,从v$managed_standby视图中查询.                                                                                 

PROCESS       PID            STATUS
---------    ----------   ------------
ARCH          3896         CLOSING
ARCH          3898         CONNECTED
ARCH          3900         CONNECTED
ARCH          3902         CLOSING
RFS           3912         IDLE
RFS           3910         RECEIVING
RFS           3914         IDLE
RFS           3916         IDLE
MRP0         3918        WAIT_FOR_LOG

18. 主/备库日志传送及应用测试.

主库上.

sys>alter system switch logfile;   --日志切换,产生一个日志.

sys>select unique thread#,max(sequence#) over(partition by thread#) last_sequence#  from v$archived_log;    --查询主库每个线程最大日志序列号.

备库上.

sys>select unique thread#,max(sequence#) over(partition by thread#) last_sequence#  from v$archived_log;  --查看主库上新产生的日志,是否传送过来.

或备库上直接查看altert预警文件.


19. 主/备库 切换.

19.1 switchover切换(计划切换或无损数据切换);

主库上.

sys> set long 100000                                                                                                                              
sys> set line 150                                                                                                                                 
sys> col db_unqiue_name for a15                                                                                                                   
sys> select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;             

NAME      LOG_MODE     OPEN_MODE        PROTECTION_MODE    PROTECTION_LEVEL     DATABASE_ROLE   SWITCHOVER_STATUS     DB_UNIQUE_NAME
--------- ------------ -------------    ----------------    -------------       ---------------- -------------------- ---------------
JIEDB01   ARCHIVELOG   READ WRITE      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE    PRIMARY          TO STANDBY          jiedb01

如上为切换前主库的状态. 如switchover_status为session_active时,需要用alter database commit to switchover to physical standby with session shutdown;

当switchover_status为not allowed时,官方文档说,主/备库切换会失败,我的备库switchover_status有一次实验为not allowed,但主/备库切换OK, 有一次实验主/备库switchover_status都为not allowed时,主/备库切换失败.此处要注意.

sys> alter database commit to switchover to physical standby;                                                                                     
sys> shutdown immediate;

sys>exit    ---此处要退出,当前session处,重新用sqlplus / as sysdba登陆,如果不退出当前环境,直接startup nomount会报错,原因查待.

sqlplus / as sysdba

sys>startup nomount;                                                                                                                        
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1348244 bytes
Variable Size             641731948 bytes
Database Buffers          201326592 bytes
Redo Buffers                5124096 bytes
sys> alter database mount standby database;                                                                                                      

Database altered.

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

sys>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;             

NAME      LOG_MODE      OPEN_MODE    PROTECTION_MODE        PROTECTION_LEVEL      DATABASE_ROLE    SWITCHOVER_STATUS   DB_UNIQUE_NAME
--------- ------------  ----------   --------------------   -------------------- ----------------  ------------------ ---------------
JIEDB01   ARCHIVELOG     MOUNTED     MAXIMUM PERFORMANCE    MAXIMUM PERFORMANCE   PHYSICAL STANDBY   RECOVERY NEEDED      jiedb01

 

备库上.

sys>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;             

NAME      LOG_MODE     OPEN_MODE     PROTECTION_MODE     PROTECTION_LEVEL    DATABASE_ROLE     SWITCHOVER_STATUS   DB_UNIQUE_NAME
--------  ----------  -----------  ------------      --------------------    ----------------   -----------------  ---------------
JIEDB01   ARCHIVELOG   MOUNTED      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY  TO PRIMARY          jiedb01dg

sys> alter database commit to switchover to primary;

sys>alter database open;

sys>shutdown immediate;

sys>startup;

sys> select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;             ---原先备库做switchover切为主库后v$database参数.

NAME      LOG_MODE   OPEN_MODE      PROTECTION_MODE       PROTECTION_LEVEL   DATABASE_ROLE   SWITCHOVER_STATUS     DB_UNIQUE_NAME

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

JIEDB01   ARCHIVELOG   READ WRITE  MAXIMUM PERFORMANCE   MAXIMUM PERFORMANCE      PRIMARY         TO STANDBY         jiedb01dg

19.2. failover(非正常切换);

为主库宕机情况:

备库(192.168.160.132)

首先检查主库(假如可以查询)与备库的日志间隙

select * from v$archive_gap;

如上sql如主/备库产生间隙,备库上可以用如下sql语句,应用归档信息到数据字典中.

alter database register physical logfile ‘/data/jiedb01/arch/xxx.arc’;

检查主库与备库的sequence#

主库宕机不能查询时,可以查询alter预警文件或归档路径下的归档文件序列号,备库可以用如下sql查询.

select unique(thread#),max(sequence#) over(partition by thread#) from v$archived_log;

如下为备库运行的sql语句.

sys>alter database recover managed standby database cancel;或

sys >alter database recover managed standby database finish;

sys >alter database commit to switchover to primary;

sys >shutdown immediate;

sys >startup;

 PS.主库与备库 初始化参数文件中db_name相同(jiedb01).

 小结: 1.主库与备库orapwjiedb01密码文件一定统一.主库与备库,如sys密码或orapwjiedb01密码文件不统一,alert会报Check that the primary and standby are using a password file......
      2.备库最好建空库,datafile与logfile与主库相同,这样db_file_name_convert与log_file_name_convert两个参数就不需要设置.
      3.备库建立空库后,备库建立的redo logfile与standby redo logfile要与主库建立的组数统一.
      4.主库与备库中,tnsname.ora中service_name要设置正常.

 

 



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

0
0
上一篇： Oracle DB日常项目点检
下一篇： Informix DB创建
请登录后发表评论 登录
全部评论
jieyu119

注册时间：2013-04-12

  • 博文量
    30
  • 访问量
    188896

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593