oracle数据库存储迁移参考

清疚 2022-06-15 10:55 213阅读 0赞

# 基本环境 #

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目录即可。