oracle数据库存储迁移参考

来源：互联网发布：mac landesk 卸载编辑：程序博客网时间：2024/05/21 08:58

基本环境

os版本：centos5.5
db版本：oracle10.2.0.1

export PS1="`/bin/hostname -s`-> "
export EDITOR=vi
export ORACLE_SID=orcl
export ORACLE_BASE=/database
export ORACLE_HOME=$ORACLE_BASE/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
umask 022

测试信息

create tablespace wql datafile '/database/oradata/orcl/wql.dbf' size 100M;
create user wql identified by oracle default tablespace wql;
grant connect,resource to wql;
conn wql/oracle；
create table t(id int,name varchar(15));
insert into t values(1,'wql');
select * from t;
        ID NAME
---------- ---------------
         1 wql

替换规划

原有的：
数据文件：/dev/sda /database/oradata
归档：/dev/sda /arch

新存储：
/dev/sdb1、/dev/sdc1、/dev/sdd1对应asm的+DG1
存放数据文件

/dev/sde1 对应asm的+DG2 存放归档日志
/dev/sdf1 对应asm的+DG3 闪回区域

实施过程

安装配置asmlib

安装

[root@db2awse ~]# rpm -qa|grep oracle
oracleasm-2.6.18-398.el5-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5
oracleasm-support-2.1.8-1.el5

配置

[root@db2awse ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [  OK  ]
Scanning the system for Oracle ASMLib disks: [  OK  ]

使用fdisk /dev/sdx配置磁盘，过程略

创建asmdisk

[root@db2awse ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "VOL1" as an ASM disk: [  OK  ]
[root@db2awse ~]# /etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
Marking disk "VOL2" as an ASM disk: [  OK  ]
[root@db2awse ~]# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd1
Marking disk "VOL3" as an ASM disk: [  OK  ]
[root@db2awse ~]# /etc/init.d/oracleasm createdisk VOL4 /dev/sde1
Marking disk "VOL4" as an ASM disk: [  OK  ]
[root@db2awse ~]# /etc/init.d/oracleasm createdisk VOL5 /dev/sdf1
Marking disk "VOL5" as an ASM disk: [  OK  ]
[root@db2awse ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5

配置asm实例

启动css，以root执行

[root@db2awse ~]# whoami
root
[root@db2awse ~]# /database/oracle/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab 
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        db2awse
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)

创建ASM实例的pfile文件，内容如下：

$ cat $ORACLE_HOME/dbs/init+ASM.ora
instance_type=asm
large_pool_size=12M
remote_login_passwordfile=exclusive
asm_diskstring='/dev/oracleasm/disks/VOL*'
background_dump_dest=/database/admin/+ASM/bdump
core_dump_dest=/database/admin/+ASM/cdump
user_dump_dest=/database/admin/+ASM/udump

创建ASM实例的password文件

db2awse-> orapwd file=orapw+ASM entries=5 password=oracle

启动asm实例，还没创建diskgroup，报错忽略

db2awse-> export ORACLE_SID=+ASM
db2awse-> sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 5 13:40:48 2016
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area  130023424 bytes
Fixed Size                  2019032 bytes
Variable Size             102838568 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

创建diskgroup

SQL> create diskgroup dg1 external redundancy disk '/dev/oracleasm/disks/VOL1','/dev/oracleasm/disks/VOL2','/dev/oracleasm/disks/VOL3';
Diskgroup created.
SQL> create diskgroup dg2 external redundancy disk '/dev/oracleasm/disks/VOL4';
Diskgroup created.
SQL> create diskgroup recover external redundancy disk '/dev/oracleasm/disks/VOL5';
Diskgroup created.

创建数据库备份

db2awse-> rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 5 13:51:54 2016
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1457578197)
RMAN> run {
2> backup as copy database format '+DG1';
3> }
Starting backup at 05-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/database/oradata/orcl/system01.dbf
output filename=+DG1/orcl/datafile/system.256.929800377 tag=TAG20161205T135248 recid=1 stamp=929800380
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/database/oradata/orcl/sysaux01.dbf
output filename=+DG1/orcl/datafile/sysaux.257.929800385 tag=TAG20161205T135248 recid=2 stamp=929800385
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/database/oradata/orcl/wql.dbf
output filename=+DG1/orcl/datafile/wql.258.929800387 tag=TAG20161205T135248 recid=3 stamp=929800387
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/database/oradata/orcl/undotbs01.dbf
output filename=+DG1/orcl/datafile/undotbs1.259.929800389 tag=TAG20161205T135248 recid=4 stamp=929800388
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/database/oradata/orcl/users01.dbf
output filename=+DG1/orcl/datafile/users.260.929800389 tag=TAG20161205T135248 recid=5 stamp=929800389
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DG1/orcl/controlfile/backup.261.929800391 tag=TAG20161205T135248 recid=6 stamp=929800390
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 05-DEC-16
channel ORA_DISK_1: finished piece 1 at 05-DEC-16
piece handle=+DG1/orcl/backupset/2016_12_05/nnsnf0_tag20161205t135248_0.262.929800393 tag=TAG20161205T135248 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-DEC-16

修改数据库参数

修改闪回参数

SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /database/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='+RECOVERY' scope=both;
System altered.
SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECOVERY
db_recovery_file_dest_size           big integer 3G

将联机日志和自动创建的数据文件位置指定为磁盘组：

SQL> alter system set db_create_file_dest='+DG1' scope=both;
System altered.
SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DG1
SQL> alter system set db_create_online_log_dest_1='+DG1' scope=both;
System altered.
SQL> alter system set db_create_online_log_dest_2='+DG2' scope=both;
System altered.
SQL> show parameter db_create_online
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1          string      +DG1
db_create_online_log_dest_2          string      +DG2

迁移联机日志

当前日志配置

SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- ----------------------------------------
         3 /database/oradata/orcl/redo03.log
         2 /database/oradata/orcl/redo02.log
         1 /database/oradata/orcl/redo01.log

为每个联机日志组添加两个成员。然后删除旧成员来迁移联机日志。因为前面设置了db_create_online_log_dest_1/2，所以新建的会自动在dg中创建。

SQL> alter database add logfile member '+DG1','+DG2' to group 1;
Database altered.
SQL> alter database add logfile member '+DG1','+DG2' to group 2;
Database altered.
SQL> alter database add logfile member '+DG1','+DG2' to group 3;
Database altered.

通过执行alter system switch logfile；切换日志，然后删除旧的联机日志。

alter database drop logfile member '/database/oradata/orcl/redo01.log';
alter database drop logfile member '/database/oradata/orcl/redo02.log';
alter database drop logfile member '/database/oradata/orcl/redo03.log';
SQL> select group#,member,status from v$logfile order by 1;
    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         1 +DG1/orcl/onlinelog/group_1.263.929801375
         1 +DG2/orcl/onlinelog/group_1.256.929801375
         2 +DG1/orcl/onlinelog/group_2.264.929801379
         2 +DG2/orcl/onlinelog/group_2.257.929801379
         3 +DG1/orcl/onlinelog/group_3.265.929801383
         3 +DG2/orcl/onlinelog/group_3.258.929801383

迁移临时表空间到asm

当前的tmp表空间配置

SQL> select ts#,bytes/1024/1024,name from v$tempfile;
       TS# BYTES/1024/1024 NAME
---------- --------------- ----------------------------------------
         3              20 /database/oradata/orcl/temp01.dbf
SQL> select ts#,name from v$tablespace where ts#=3;
       TS# NAME
---------- ----------------------------------------
         3 TEMP

为临时表空间增加文件，自动创建到dg中，然后删除旧文件

SQL> alter tablespace temp add tempfile size 30M;
Tablespace altered.
SQL> select ts#,bytes/1024/1024,name from v$tempfile;
       TS# BYTES/1024/1024 NAME
---------- --------------- ----------------------------------------
         3              30 +DG1/orcl/tempfile/temp.266.929802143
         3              20 /database/oradata/orcl/temp01.dbf
SQL> alter database tempfile '/database/oradata/orcl/temp01.dbf' drop;
Database altered.
SQL> select ts#,bytes/1024/1024,name from v$tempfile;
       TS# BYTES/1024/1024 NAME
---------- --------------- ----------------------------------------
         3              30 +DG1/orcl/tempfile/temp.266.929802143

迁移控制文件和数据文件（需关停实例）

检查控制文件，并修改参数

SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /database/oradata/orcl/control
                                                 01.ctl, /database/oradata/orcl
                                                 /control02.ctl, /database/orad
                                                 ata/orcl/control03.ctl
SQL> alter system set control_files='+DG1/ORCL/CONTROLFILE/control01','+DG2/ORCL/CONTROLFILE/control02' scope=spfile;
System altered.

关闭数据库，并启动到nomount状态

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             318770144 bytes
Database Buffers          889192448 bytes
Redo Buffers               14753792 bytes
SQL>

在rman中进行完全恢复，恢复控制文件，可以看到，恢复到了指定位置

db2awse-> rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 5 14:31:22 2016
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/database/oradata/orcl/control01.ctl';
Starting restore at 05-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DG1/orcl/controlfile/control01
output filename=+DG2/orcl/controlfile/control02
Finished restore at 05-DEC-16

挂载数据库，并切换数据文件

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/orcl/datafile/system.256.929800377"
datafile 2 switched to datafile copy "+DG1/orcl/datafile/undotbs1.259.929800389"
datafile 3 switched to datafile copy "+DG1/orcl/datafile/sysaux.257.929800385"
datafile 4 switched to datafile copy "+DG1/orcl/datafile/users.260.929800389"
datafile 5 switched to datafile copy "+DG1/orcl/datafile/wql.258.929800387"

recover database并打开数据库

RMAN> recover database;
Starting recover at 05-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /arch/1_2_929797463.dbf
archive log thread 1 sequence 3 is already on disk as file /arch/1_3_929797463.dbf
archive log thread 1 sequence 4 is already on disk as file /arch/1_4_929797463.dbf
archive log thread 1 sequence 5 is already on disk as file /arch/1_5_929797463.dbf
archive log filename=/arch/1_2_929797463.dbf thread=1 sequence=2
archive log filename=/arch/1_3_929797463.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:05
Finished recover at 05-DEC-16
RMAN> alter database open;
database opened

确认迁移成功

SQL> select name from v$controlfile;
NAME
------------------------------------------
+DG1/orcl/controlfile/control01
+DG2/orcl/controlfile/control02
SQL> select name,status from v$datafile;
NAME                                       STATUS
------------------------------------------ -------
+DG1/orcl/datafile/system.256.929800377    SYSTEM
+DG1/orcl/datafile/undotbs1.259.929800389  ONLINE
+DG1/orcl/datafile/sysaux.257.929800385    ONLINE
+DG1/orcl/datafile/users.260.929800389     ONLINE
+DG1/orcl/datafile/wql.258.929800387       ONLINE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DG1/orcl/onlinelog/group_1.263.929801375
+DG2/orcl/onlinelog/group_1.256.929801375
+DG1/orcl/onlinelog/group_2.264.929801379
+DG2/orcl/onlinelog/group_2.257.929801379
+DG1/orcl/onlinelog/group_3.265.929801383
+DG2/orcl/onlinelog/group_3.258.929801383
6 rows selected.
SQL> select name,status from v$tempfile;
NAME                                       STATUS
------------------------------------------ -------
+DG1/orcl/tempfile/temp.266.929802143      ONLINE

迁移成功，删除原有的oradata目录即可。

阅读全文

0 0