数据容灾实施方案

1、方案说明

2、实施环境

3、系统架构

4、实施步骤

5、问题总结

6、方案总结和建议

1,方案说明

1-1，实施目的：实现数据的容灾

1-2，方案在企业中的应用：以最小的成本，最大范围的保护数据

2，实施的环境

2-1，操作系统版本

主库：Red Hat Enterprise Linux Server release 5.5

备库：Red Hat Enterprise Linux Server release 5.5

2-2，数据库版本

主库：Oracle Version 11.2.0.1.0

备库：Oracle Version 11.2.0.1.0

2-3，dg版本

3,系统架构

3-1，主机配置

	主库	备库
hostname	bjserv	shserv
IP	192.168.15.50	192.168.15.51
实例名	Bjdb	Shdb
数据库名	Prod	Prod
数据库别名	bjdb	shdb

3-2，data guard 的各个进程功能

LGWR:将redo buffer 里的redo log写到联机日志

ARCH:在一组联机日志写满后会发生日志切换，会触发arch进程将日志归档

FAL:

RFS: arch进程通过网络将日志发送到备库的rfs进程，rfs进程将日志写入到归档日志文件中

1，异步传输：rfs 将收到的日志写入到备库归档日志文件

2，同步传输：rfs 将收到的日志写入到备库联机日志文件

备库ARCH：

MRP/LSP：备库的mrp(managed recovery process)或者(sql apply)进程在备库上应用这些日志，并同步数据。

3-4，DG的保护模式

1，最大保护

目的：确保数据不会丢失

实现：主库的事务提交后，要求lgwr不仅将日志写到本地redo log 还要写到备库的redo log 并要求收到备库的确认信息

缺点：在网络中断的情况下，由于主库无法收到备库的确认信息，主库会自动shutdown 以保证数据无丢失。同时也造成数据库的不可用。

解决：1，确保网络的畅通可以准备备用网络 2，可以设置多个备库，主库只要收到一个备库的确认信息就可以正常工作

2，最高可用性

自动在最大保护和最大性能之间切换。

3，最大性能

目的：保护主数据库的运行

实现：主库的redo 日志异步传输到备库上，且不要求回复确认信息

缺点：绝有一定的风险，当灾难发生的时候会导致，备库上的数据和主库存在差别

4，实施步骤

4-1，配置操作系统环境主库，备库分别进行

导入两个虚拟机，修改mac地址

修改hostname解析文件主库备库都做

[root@node1 ~]# more /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost

192.168.15.50 bjserv

192.168.15.51 shserv

修改系统主机名主库备库都做

[root@node1 ~]# more /etc/sysconfig/network

NETWORKING=yes

NETWORKING_IPV6=no

HOSTNAME=bjserv

使主机名生效

hostname bjserv 生效

修改网卡ip 网关等

[root@node1 ~]# more /etc/sysconfig/network-scripts/ifcfg-eth0

# Intel Corporation 82540EM Gigabit Ethernet Controller

DEVICE=eth0

BOOTPROTO=static

IPADDR=192.168.15.50

NETMASK=255.255.255.0

ONBOOT=yes

网卡重启，使ip生效

[root@node1 ~]# service network restart

Shutting down interface eth0: [ OK ]

Shutting down loopback interface: [ OK ]

Bringing up loopback interface: [ OK ]

Bringing up interface eth0: [ OK ]

给添加的磁盘分区，设置挂载

查看那些磁盘未分区

[root@shserv ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 13 104391 83 Linux

/dev/sda2 14 2610 20860402+ 8e Linux LVM

Disk /dev/sdb: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

给sdb分区

[root@shserv ~]# fdisk /dev/sdb

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-2610, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610):

Using default value 2610

Command (m for help):

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

给sdb1分区设置文件系统

[root@shserv ~]# mkfs.ext3 /dev/sdb1

设置开机自动挂载

[root@shserv ~]# vi /etc/fstab

/dev/sdb1 /u01 ext3 defaults 0 0

~

"/etc/fstab" 8L, 611C written

将磁盘挂载到 /u01下

[root@shserv ~]# mount /u01

[root@shserv ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

18G 6.1G 11G 38% /

/dev/sda1 99M 12M 82M 13% /boot

tmpfs 781M 0 781M 0% /dev/shm

/dev/sdb1 20G 173M 19G 1% /u01

}

添加用户和用户组并创建目录

两个组一个用户

[root@bjserv ~]# groupadd -g 200 oinstall

[root@bjserv ~]# groupadd -g 201 dba

[root@bjserv ~]# useradd -u 200 -g oinstall -G dba oracle

[root@bjserv ~]# passwd oracle

修改目录权限

[root@bjserv ~]# mkdir -p /u01/app/oracle

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

修改oracle环境变量添加下面代码

[root@shserv ~]# su - oracle

[oracle@shserv ~]$ vi .bash_profile

export EDITOR=vi

export ORACLE_SID=prod

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin

umask 022

修改系统内核参数 /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

"/etc/sysctl.conf" 49L, 1305C written

使系统内核参数生效

[root@bjserv ~]# sysctl -p

限制资源 /etc/security/limits.conf 添加如下参数

[root@node1 ~]# more /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

使限制资源生效 /etc/pam.d/login 与 /etc/profile 添加如下参数

[root@node1 ~]# more /etc/pam.d/login

session required /lib/security/pam_limits.so

[root@node1 ~]# more /etc/profile

if [ $USER = "oracle" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

关闭不必要的服务

[root@node1 ~]# chkconfig sendmail off

[root@node1 ~]# chkconfig ntpd off

[root@node1 ~]# mv /etc/ntp.conf /etc/ntp.conf.bak

使用 yum 安装 missing 的包

配置 yum

[root@node1 ~]# cd /etc/yum.repos.d

[root@node1 yum.repos.d]# ls

rhel-debuginfo.repo

[root@node1 yum.repos.d]# cp rhel-debuginfo.repo yum.repo

[root@node1 yum.repos.d]# more yum.repo

[base]

name=Red Hat Enterprise Linux

baseurl=file:///media/Server

enabled=1

gpgcheck=0

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

将配置好的yum复制到备库

[root@node1 yum.repos.d]# scp yum.repo shserv:/etc/yum.repos.d/

插入光盘，安装 missing 包

[root@node1 ~]# mount /dev/cdrom /media

[root@node1 ~]# ls /media/Server

[root@node1 ~]# yum install -y libaio*

[root@node1 ~]# yum install -y sysstat*

[root@node1 ~]# yum install -y unix*

[root@node1 ~]# yum install -y vnc* (node1)

4-2，安装oracle软件主库和备库都要安装且主库需要建库

跑脚本

[root@bjserv ~]# /u01/app/oraInventory/orainstRoot.sh

[root@bjserv ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh

配置监听

netca

cd $ORACLE_HOME/sqlplus/admin

主库上需要建库

dbca

4-3，配置主库的参数

创建逻辑卷

查看新加的磁盘

[root@bjserv ~]# fdisk -l

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

新磁盘分区并转化成Linux LVM

[root@bjserv ~]# fdisk /dev/sdc

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-2610, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610):

Using default value 2610

Command (m for help): p

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdc1 1 2610 20964793+ 83 Linux

Command (m for help): t

Selected partition 1

Hex code (type L to list codes): L

Hex code (type L to list codes): 8e

Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdc1 1 2610 20964793+ 8e Linux LVM

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

创建物理卷

[root@bjserv ~]# pvcreate /dev/sdc1

Physical volume "/dev/sdc1" successfully created

创建逻辑卷组，因为只有一个物理卷

[root@bjserv ~]# vgcreate datavg /dev/sdc1

/dev/hdc: open failed: No medium found

Volume group "datavg" successfully created

创建三个5g的逻辑卷

[root@bjserv ~]# lvcreate -L 5g -n lv_dsk1 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk1" created

[root@bjserv ~]# lvcreate -L 5g -n lv_dsk2 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk2" created

[root@bjserv ~]# lvcreate -L 5g -n lv_dsk3 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk3" created

[root@bjserv ~]# ls -l /dev/datavg/lv*

lrwxrwxrwx 1 root root 26 Sep 23 19:33 /dev/datavg/lv_dsk1 -> /dev/mapper/datavg-lv_dsk1

lrwxrwxrwx 1 root root 26 Sep 23 19:33 /dev/datavg/lv_dsk2 -> /dev/mapper/datavg-lv_dsk2

lrwxrwxrwx 1 root root 26 Sep 23 19:33 /dev/datavg/lv_dsk3 -> /dev/mapper/datavg-lv_dsk3

[root@bjserv ~]# ls -l /dev/mapper

total 0

crw------- 1 root root 10, 63 Sep 23 19:09 control

brw-rw---- 1 root disk 253, 2 Sep 23 19:33 datavg-lv_dsk1

brw-rw---- 1 root disk 253, 3 Sep 23 19:33 datavg-lv_dsk2

brw-rw---- 1 root disk 253, 4 Sep 23 19:33 datavg-lv_dsk3

brw-rw---- 1 root disk 253, 0 Sep 23 19:11 VolGroup00-LogVol00

brw-rw---- 1 root disk 253, 1 Sep 23 19:09 VolGroup00-LogVol01

分别在逻辑卷上创建文件系统

[root@bjserv ~]# mkfs.ext3 /dev/datavg/lv_dsk1

[root@bjserv ~]# mkfs.ext3 /dev/datavg/lv_dsk2

[root@bjserv ~]# mkfs.ext3 /dev/datavg/lv_dsk3

设置开机自动挂载逻辑卷

[root@bjserv ~]# mkdir /dsk1 /dsk2 /dsk3

[root@bjserv ~]# vi /etc/fstab

/dev/datavg/lv_dsk1 /dsk1 ext3 defaults 0 0

/dev/datavg/lv_dsk2 /dsk2 ext3 defaults 0 0

/dev/datavg/lv_dsk3 /dsk3 ext3 defaults 0 0

"/etc/fstab" 11L, 842C written

挂载逻辑卷

[root@bjserv ~]# mount /dsk1

[root@bjserv ~]# mount /dsk2

[root@bjserv ~]# mount /dsk3

[root@bjserv ~]# df -h

/dev/mapper/datavg-lv_dsk1

5.0G 139M 4.6G 3% /dsk1

/dev/mapper/datavg-lv_dsk2

5.0G 139M 4.6G 3% /dsk2

/dev/mapper/datavg-lv_dsk3

5.0G 139M 4.6G 3% /dsk3

设置逻辑卷的所有者

[oracle@bjserv dbs]$ su -

Password:

[root@bjserv ~]# chown -R oracle:oinstall /dsk1

[root@bjserv ~]# chown -R oracle:oinstall /dsk2

[root@bjserv ~]# chown -R oracle:oinstall /dsk3

日志文件多元化

[oracle@bjserv ~]$ mkdir -p /dsk2/oradata/bj

SYS@ prod>alter database add logfile member

2 '/dsk2/oradata/bj/redo01b.log' to group 1,

3 '/dsk2/oradata/bj/redo02b.log' to group 2,

4 '/dsk2/oradata/bj/redo03b.log' to group 3;

Database altered.

[oracle@bjserv ~]$ cp /u01/app/oracle/oradata/prod/redo01.log /dsk1/oradata/bj/redo01a.log

[oracle@bjserv ~]$ cp /u01/app/oracle/oradata/prod/redo02.log /dsk1/oradata/bj/redo02a.log

[oracle@bjserv ~]$ cp /u01/app/oracle/oradata/prod/redo03.log /dsk1/oradata/bj/redo03a.log

SYS@ prod>alter database rename file '/u01/app/oracle/oradata/prod/redo01.log' to '/dsk1/oradata/bj/redo01a.log';

SYS@ prod>alter database rename file '/u01/app/oracle/oradata/prod/redo02.log' to '/dsk1/oradata/bj/redo02a.log';

SYS@ prod>alter database rename file '/u01/app/oracle/oradata/prod/redo03.log' to '/dsk1/oradata/bj/redo03a.log';

SYS@ prod>select member from v$logfile;

MEMBER

/dsk1/oradata/bj/redo03a.log

/dsk1/oradata/bj/redo02a.log

/dsk1/oradata/bj/redo01a.log

/dsk2/oradata/bj/redo01b.log

/dsk2/oradata/bj/redo02b.log

/dsk2/oradata/bj/redo03b.log

控制文件多元化

[oracle@bjserv ~]$ mkdir -p /dsk1/oradata/bj

SYS@ prod>alter system set control_files=

'/u01/app/oracle/oradata/prod/control01.ctl','/dsk1/oradata/bj/control02.ctl','/dsk2/oradata/bj/control02.ctl' scope=spfile;

设置归档

[oracle@bjserv ~]$ ls /dsk3/arch_prod

SYS@ prod>alter database archivelog;

SYS@ prod>alter system set log_archive_dest_1='location=/dsk3/arch_prod' scope=spfile;

SYS@ prod>alter system set log_archive_format='arch_%t_%s_%r.log' scope=spfile;

SYS@ prod>alter system switch logfile;

SYS@ prod>select name from v$archived_log;

NAME

/dsk3/arch_prod/arch_1_7_826915293.log

/dsk3/arch_prod/arch_1_8_826915293.log

备份主库rman下

run {

sql 'alter system switch logfile';

shutdown immediate;

startup mount;

backup database format '/dsk3/backup/%d_%s.bak';

alter database open;

sql 'alter system switch logfile';

}

4-4，设置备库的参数

备库建立逻辑卷

[root@shserv oracle]# fdisk -l

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

[root@shserv oracle]# fdisk /dev/sdc

Command (m for help): t

Selected partition 1

Hex code (type L to list codes): 8e

Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdc1 1 2610 20964793+ 8e Linux LVM

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@shserv oracle]# pvcreate /dev/sdc1

Physical volume "/dev/sdc1" successfully created

[root@shserv oracle]# vgcreate datavg /dev/sdc1

/dev/hdc: open failed: No medium found

Volume group "datavg" successfully created

[root@shserv oracle]# lvcreate -L 5g -n lv_dsk1 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk1" created

[root@shserv oracle]# lvcreate -L 5g -n lv_dsk2 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk2" created

[root@shserv oracle]# lvcreate -L 5g -n lv_dsk3 datavg

/dev/hdc: open failed: No medium found

Logical volume "lv_dsk3" created

[root@shserv oracle]# mkfs.ext3 /dev/datavg/lv_dsk1

[root@shserv oracle]# mkfs.ext3 /dev/datavg/lv_dsk2

[root@shserv oracle]# mkfs.ext3 /dev/datavg/lv_dsk3

设置逻辑卷的自动挂载

[root@shserv oracle]# mkdir /dsk1 /dsk2 /dsk3

[root@shserv ~]# vi /etc/fstab

/dev/datavg/lv_dsk1 /dsk1 ext3 defaults 0 0

/dev/datavg/lv_dsk2 /dsk2 ext3 defaults 0 0

/dev/datavg/lv_dsk3 /dsk3 ext3 defaults 0 0

挂载备库逻辑卷

[root@shserv ~]# mount /dsk1

[root@shserv ~]# mount /dsk2

[root@shserv ~]# mount /dsk3

[root@shserv ~]# chown -R oracle:oinstall /dsk[1-3]

备库上创建目录

[oracle@shserv ~]$ mkdir -p /dsk1/oradata/shdb

[oracle@shserv ~]$ mkdir -p /dsk2/oradata/shdb

[oracle@shserv ~]$ mkdir -p /dsk3/arch_sh

[oracle@shserv ~]$ mkdir -p /dsk3/backup

[oracle@shserv ~]$ ls /dsk3

arch_sh backup lost+found

4-5，修改主库和备库的pfile

创建一个pfile

create pfile from spfile；

修改主库的pfile

DB_UNIQUE_NAME=bjdb

LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/dsk3/arch_prod

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=bjdb'

LOG_ARCHIVE_DEST_2=

'SERVICE=shdb ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=shdb'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=3

FAL_SERVER=shdb

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/shdb','/u01/app/oracle/oradata/prod'

LOG_FILE_NAME_CONVERT= '/dsk1/oradata/shdb','/dsk1/oradata/bj','/dsk2/oradata/shdb','/dsk2/oradata/bj'

STANDBY_FILE_MANAGEMENT=AUTO

用pfile启动数据库

SYS@ prod>startup force pfile='$ORACLE_HOME/dbs/initprod.ora' nomount;

ORACLE instance started.

SYS@ prod>show parameter name

NAME TYPE VALUE

db_file_name_convert string /u01/app/oracle/oradata/shdb, /u01/app/oracle/oradata/prod

db_name string prod

db_unique_name string bjdb

global_names boolean FALSE

instance_name string prod

lock_name_space string

log_file_name_convert string /dsk1/oradata/shdb, /dsk1/orad

ata/bj, /dsk2/oradata/shdb, /d

sk2/oradata/bj

service_names string bjdb

在主库mount的情况下把主库的数据文件拷贝到备库里

SYS@ prod>alter database mount;

[oracle@shserv ~]$ mkdir -p /u01/app/oracle/oradata/shdb

[oracle@bjserv dbs]$ cd /u01/app/oracle/oradata/prod

[oracle@bjserv prod]$ scp *.dbf shserv:/u01/app/oracle/oradata/shdb

example01.dbf 100% 100MB 11.1MB/s 00:09

sysaux01.dbf 100% 510MB 10.2MB/s 00:50

system01.dbf 100% 690MB 10.8MB/s 01:04

temp01.dbf 100% 29MB 9.7MB/s 00:03

undotbs01.dbf 100% 100MB 12.5MB/s 00:08

users01.dbf 100% 5128KB 5.0MB/s 00:01

把pfile和口令文件拷贝到备库注意修改文件名

[oracle@bjserv dbs]$ scp initprod.ora shserv:$ORACLE_HOME/dbs/initshdb.ora

oracle@shserv's password:

initprod.ora 100% 1516 1.5KB/s 00:00

[oracle@bjserv dbs]$ scp orapwprod shserv:$ORACLE_HOME/dbs/orapwshdb

oracle@shserv's password:

orapwprod 100% 1536 1.5KB/s 00:00

在主库mount的情况下生成一个备库的控制文件，并拷贝到备库

SYS@ prod>alter database create standby controlfile as '/dsk3/backup/sh_control01.ctl';

Database altered.

[oracle@bjserv dbs]$ scp /dsk3/backup/sh_control01.ctl shserv:/u01/app/oracle/oradata/shdb

oracle@shserv s password:

sh_control01.ctl 100% 9520KB 9.3MB/s 00:01

修改备库的pfile

[oracle@shserv dbs]$ more initshdb.ora

*.audit_file_dest='/u01/app/oracle/admin/sh/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/shdb/sh_control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='prod'

*.diagnostic_dest='/u01/app/oracle'

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

*.log_archive_dest_1='location=/dsk3/arch_prod'

*.log_archive_format='arch_%t_%s_%r.log'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=shdb

LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/dsk3/arch_sh

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=shdb'

LOG_ARCHIVE_DEST_2=

'SERVICE=bjdb ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=bjdb'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=3

FAL_SERVER=bjdb

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/shdb'

LOG_FILE_NAME_CONVERT=

'/dsk1/oradata/bj','/dsk1/oradata/shdb','/dsk2/oradata/bj','/dsk2/oradata/shdb'

STANDBY_FILE_MANAGEMENT=AUTO

创建一个审计目录

[oracle@shserv dbs]$ mkdir -p /u01/app/oracle/admin/sh/adump

4-6，修改主库和备库的tnsname.ora

主库配置tnsname.ora

cd $ORACLE_HOME/network/admin/tnsname.ora

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bjserv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bjdb)

)

SHDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = shserv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = shdb)

)

在备库上创建tnsname.ora

BJDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bjserv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bjdb)

)

SHDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = shserv)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = shdb)

)

在备库上链接主库

在创建数据库的时候设置了一个密码 beijing

[oracle@shserv admin]$ sqlplus sys/beijing@bjdb as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 24 01:35:25 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

备库上启动数据库到nomount

[oracle@shserv ~]$ sqlplus / as sysdba

SYS@ shdb>startup nomount

SYS@ shdb>show parameter name

NAME TYPE VALUE

db_file_name_convert string /u01/app/oracle/oradata/prod, /u01/app/oracle/oradata/shdb

db_name string prod

db_unique_name string shdb

global_names boolean FALSE

instance_name string shdb

lock_name_space string

log_file_name_convert string /dsk1/oradata/bj, /dsk1/oradat

a/shdb, /dsk2/oradata/bj, /dsk

2/oradata/shdb

service_names string shdb

主库open

SYS@ prod>alter database open;

备库mount

SYS@ shdb>alter database mount;

查看备库告警日志

[oracle@shserv trace]$ pwd

/u01/app/oracle/diag/rdbms/shdb/shdb/trace

[oracle@shserv trace]$ tail -f alert_shdb.log

查看主库告警日志

[oracle@bjserv trace]$ pwd

/u01/app/oracle/diag/rdbms/bjdb/prod/trace

[oracle@bjserv trace]$ tail -f alert_prod.log

在备库上打开介质恢复

SYS@ shdb>alter database recover managed standby database disconnect from session;

查看主库和备库的日志是否状态一致

SYS@ shdb>select group#,thread#,sequence#,status from v$log;

GROUP# THREAD# SEQUENCE# STATUS

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

1 1 19 CLEARING

3 1 21 CLEARING_CURRENT

2 1 20 CLEARING

SYS@ prod>select group#,thread#,sequence#,status from v$log;

GROUP# THREAD# SEQUENCE# STATUS

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

1 1 19 ACTIVE

2 1 20 ACTIVE

3 1 21 CURRENT

主库备库的状态

SYS@ prod>select name,dbid, PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME DBID PROTECTION_MODE DATABASE_ROLE

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

PROD 238753626 MAXIMUM PERFORMANCE PRIMARY

SYS@ shdb>select name,dbid, PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME DBID PROTECTION_MODE DATABASE_ROLE

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

PROD 238753626 MAXIMUM PERFORMANCE PHYSICAL STANDBY

5，问题总结

5-1，在修改pfile的时候要特别注意，主库和备库pfile的区别

5-2，

6，方案总结