資料容災實施方案

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，方案總結

資料容災實施方案

相關文章