Monk的学习笔记

宠辱不惊,闲看庭前花开花落;去留无意,漫随天外云卷云舒。

  • 博客访问: 2070251
  • 博文数量: 164
  • 用 户 组: 普通用户
  • 注册时间: 2013-11-14 10:49
  • 认证徽章:
个人简介

每个人都有梦想,去实现吧!

文章分类

全部博文(164)

文章存档

2017年(11)

2016年(18)

2015年(36)

2014年(81)

2013年(18)

分类: Oracle

2017-01-13 11:21:44

 

Data Guard作为Oracle MAA(最大可用性框架)的重要组成部分,已经成为很多成熟运维系统的标准配置。借助Data Guard提供的物理standby和逻辑standby,我们可以保证系统数据库运行在一个较高的可用性环境里。并且,switchoverfailover策略,可以最大限度的保证日常运维和故障恢复场景。

从最早的9i11gOracle DG搭建过程经历了很多阶段。大致可以分为三种策略方法:

 

ü  Backup and Restore策略:借助RMAN备份集合(backupset),远程传递到standby服务器。再利用RMAN还原策略进行还原操作;

ü  RMAN Duplicate策略:同样是借助RMAN的机制,提供了duplicate语句可以实现直接的standby搭建,不需要备份集合传递过程;

ü  RMAN Duplicate from active database:这个是目前最新的搭建方法。在原有duplicate基础上,可以实现Primary不停机运行情况下搭建standby

 

在实际中,三种策略各有利弊和使用的场景。Backup and Restore策略和Duplicate方法需要单独停机过程。而from active database方法对带宽要求较高。根据实际的部署环境,设计不同的安装策略,进行多次的测试过程才是确保平稳的关键方法。

在之前的文章中,已经介绍过使用duplicate from active database方法搭建Data Guard的步骤。本篇介绍Backup and Restore方法,权当记录,留待需要朋友参考。

 

1、环境介绍

 

笔者选择Red Hat6.5版本进行实验,由于是虚拟机环境,策划在相同物理服务器上搭建PrimaryStandby

 

[root@SimpleLinux ~]# uname -r

2.6.32-431.el6.i686

 

内存和存储空间充足(相对而言)。

 

[root@SimpleLinux ~]# free -m

             total       used       free     shared    buffers     cached

Mem:          1893        129       1763          0         14         54

-/+ buffers/cache:         60       1832

Swap:         1999          0       1999

[root@SimpleLinux ~]# df -h

Filesystem      Size  Used Avail Use% Mounted on

/dev/sda2        48G   16G   31G  34% /

tmpfs           947M     0  947M   0% /dev/shm

 

Primary端已经安装完成,实例名为ora11g,当前处于非归档模式下。

 

 

[oracle@SimpleLinux ~]$ env | grep ORA

ORACLE_SID=ora11g

ORACLE_BASE=/u01/app

ORACLE_HOME=/u01/app/oracle

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4

Current log sequence           6

 

数据库版本为11.2.0.4,对应控制文件、数据文件、日志文件保持默认状态。

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

三大核心文件情况如下:

 

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oradata/ORA11G/controlfile/o1_mf_9mnjwpko_.ctl

/u01/app/fast_recovery_area/ORA11G/controlfile/o1_mf_9mnjwpw2_.ctl

 

 

SQL> select group#, member from v$logfile;

 

    GROUP# MEMBER

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

         3 /u01/app/oradata/ORA11G/onlinelog/o1_mf_3_9mnjx4n0_.log

         3 /u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_3_9mnjx54c_.log

         2 /u01/app/oradata/ORA11G/onlinelog/o1_mf_2_9mnjwzpq_.log

         2 /u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_2_9mnjx15f_.log

         1 /u01/app/oradata/ORA11G/onlinelog/o1_mf_1_9mnjwtj9_.log

         1 /u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_1_9mnjwvdm_.log

 

6 rows selected

 

 

SQL> select file_name, file_id from dba_data_files;

 

FILE_NAME                                                                           FILE_ID

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

/u01/app/oradata/ORA11G/datafile/o1_mf_users_9mnjs074_.dbf                                4

/u01/app/oradata/ORA11G/datafile/o1_mf_undotbs1_9mnjs068_.dbf                             3

/u01/app/oradata/ORA11G/datafile/o1_mf_sysaux_9mnjs04h_.dbf                               2

/u01/app/oradata/ORA11G/datafile/o1_mf_system_9mnjrzty_.dbf                               1

 

系统启用recovery area dest目录。

 

SQL> show parameter recovery

 

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/fast_recovery_area

db_recovery_file_dest_size           big integer 10000M

recovery_parallelism                 integer     0

 

2Primary端配置

 

首先调整Primary设置,先开启归档模式,选择默认的recovery area作为归档目录位置就可以了。

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  372449280 bytes

Fixed Size                  1364732 bytes

Variable Size             293604612 bytes

Database Buffers           71303168 bytes

Redo Buffers                6176768 bytes

Database mounted.

 

SQL> alter database archivelog;

Database altered.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence           6

 

开启强制日志动作:

 

SQL> alter database force logging;

Database altered.

 

3Primary备份和处理

 

此时保持mount状态,进行RMAN备份。

 

 

[oracle@SimpleLinux ~]$ rman nocatalog

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 24 20:41:50 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

RMAN> connect target /

connected to target database: ORA11G (DBID=4239941846, not open)

using target database control file instead of recovery catalog

 

--简单配置

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

 

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

 

备份数据库文件和日志。

 

RMAN> backup database plus archivelog;

 

 

Starting backup at 24-MAY-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

specification does not match any archived log in the repository

backup cancelled because there are no files to backup

Finished backup at 24-MAY-14

 

Starting backup at 24-MAY-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001

(篇幅原因,有省略……)

Finished Control File and SPFILE Autobackup at 24-MAY-14

 

备份standby controlfile

 

RMAN> backup current controlfile for standby;

 

Starting backup at 24-MAY-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 24-MAY-14

channel ORA_DISK_1: finished piece 1 at 24-MAY-14

piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2014_05_24/o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp tag=TAG20140524T204716 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-MAY-14

 

Starting Control File and SPFILE Autobackup at 24-MAY-14

piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2014_05_24/o1_mf_s_848435155_9r156vbz_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 24-MAY-14

 

备份集合情况:

 

RMAN> list backup;

 

List of Backup Sets

===================

 

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Full    1.05G      DISK        00:02:18     24-MAY-14     

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20140524T204320

        Piece Name: /u01/app/fast_recovery_area/ORA11G/backupset/2014_05_24/o1_mf_nnndf_TAG20140524T204320_9r14z9hn_.bkp

 (篇幅原因,有省略……

        Piece Name: /u01/app/fast_recovery_area/ORA11G/backupset/2014_05_24/o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp

  Standby Control File Included: Ckp SCN: 696037       Ckp time: 24-MAY-14

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    9.36M      DISK        00:00:03     24-MAY-14     

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20140524T204721

        Piece Name: /u01/app/fast_recovery_area/ORA11G/autobackup/2014_05_24/o1_mf_s_848435155_9r156vbz_.bkp

  SPFILE Included: Modification time: 24-MAY-14

  SPFILE db_unique_name: ORA11G

  Control File Included: Ckp SCN: 696037       Ckp time: 24-MAY-14

 

将备份集合文件拷贝到standby端服务器,可以使用sftpscp等方法。这个过程传输往往是最快的方法。

 

--standby

[root@SimpleLinux ~]# cd /

[root@SimpleLinux /]# mkdir /standbybackup

[root@SimpleLinux /]# chown oracle:oinstall /standbybackup/

 

[oracle@SimpleLinux 2014_05_24]$ pwd

/u01/app/fast_recovery_area/ORA11G/backupset/2014_05_24

[oracle@SimpleLinux 2014_05_24]$ ls -l

total 1105740

-rw-r-----. 1 oracle oinstall    9797632 May 24 20:47 o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp

-rw-r-----. 1 oracle oinstall 1122476032 May 24 20:45 o1_mf_nnndf_TAG20140524T204320_9r14z9hn_.bkp

[oracle@SimpleLinux 2014_05_24]$ cp * /standbybackup/

 

 

[oracle@SimpleLinux 2014_05_24]$ cd /standbybackup/

[oracle@SimpleLinux standbybackup]$ ls -l

total 1105740

-rw-r-----. 1 oracle oinstall    9797632 May 24 20:50 o1_mf_ncnnf_TAG20140524T204716_9r156r7j_.bkp

-rw-r-----. 1 oracle oinstall 1122476032 May 24 20:52 o1_mf_nnndf_TAG20140524T204320_9r14z9hn_.bkp

 

4Primary端参数修改

 

修改primary端参数,加入归档日志传输、文件自动管理和命名转换参数。

 

 

SQL> alter system set log_archive_dest_2='SERVICE=ora11gsy valid_for=(online_logfiles,primary_role) db_unique_name=ora11gsy' scope=spfile;

System altered.

 

SQL> alter system set standby_file_management=auto;

System altered.

 

SQL> alter system set db_file_name_convert='ORA11G','ORA11GSY' scope=spfile;

System altered.

 

SQL> alter system set log_file_name_convert='ORA11G','ORA11GSY' scope=spfile;

System altered.

 

创建文本参数文件,加以修改。

 

--生成文本备份

SQL> create pfile from spfile;

File created.

 

 

SQL> shutdown immediate;

ORA-01109: database not open

 

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  372449280 bytes

Fixed Size                  1364732 bytes

Variable Size             293604612 bytes

Database Buffers           71303168 bytes

Redo Buffers                6176768 bytes

Database mounted.

 

参数文件默认在$ORACLE_HOME/dbs目录中。

 

[oracle@SimpleLinux standbybackup]$ cd /u01/app/oracle/dbs/

[oracle@SimpleLinux dbs]$ ls -l

total 9544

-rw-rw----. 1 oracle oinstall    1544 May 24 21:12 hc_ora11g.dat

-rw-r--r--. 1 oracle oinstall    2851 May 15  2009 init.ora

-rw-r--r--. 1 oracle oinstall    1190 May 24 21:12 initora11g.ora

-rw-r-----. 1 oracle oinstall      24 Apr  1 12:39 lkORA11G

-rw-r-----. 1 oracle oinstall    1536 Apr  1 12:45 orapwora11g

-rw-r-----. 1 oracle oinstall 9748480 May 24 20:47 snapcf_ora11g.f

-rw-r-----. 1 oracle oinstall    3584 May 24 21:13 spfileora11g.ora

 

对参数文件进行修改,并且改名,作为新实例ora11gsypfile启动文件。

 

[oracle@SimpleLinux dbs]$ cat initora11g.ora

*.audit_file_dest='/u01/app/admin/ora11gsy/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oradata'

*.db_domain=''

*.db_file_name_convert='ORA11G','ORA11GSY'

*.db_name='ora11g'

*.db_recovery_file_dest='/u01/app/fast_recovery_area'

*.db_recovery_file_dest_size=10485760000

*.diagnostic_dest='/u01/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gsyXDB)'

*.log_archive_dest_2='SERVICE=ora11gsy valid_for=(online_logfiles,primary_role) db_unique_name=ora11gsy'

*.log_file_name_convert='ORA11G','ORA11GSY'

*.memory_target=373293056

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

db_unique_name='ora11gsy'

instance_name='ora11gsy'

 

[oracle@SimpleLinux dbs]$ cp initora11g.ora initora11gsy.ora

 

创建dump目录。

 

--必须创建,否则会在启动实例中报错。

[root@SimpleLinux ~]# mkdir -p /u01/app/admin/ora11gsy/adump

[root@SimpleLinux ~]# chown -R oracle:oinstall /u01


阅读(772) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册