##########################################################
--经分数据库迁移
redo --不需重启
undo --不需重启
temp tablespace --不需重启
control file --要重启
spfile --要重启
ocr --不需重启
voting disk --要重启
system tablespace --要重启
###########################################################
*****************************************************
一、迁移redo
*****************************************************
1、添加日志组成员
ALTER DATABASE ADD LOGFILE MEMBER '/u01/.../redo01.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/.../redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/.../redo03.log' TO GROUP 3;
2、删除旧的日志组成员
ALTER DATABASE DROP LOGFILE MEMBER '/u01/.../redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/.../redo02.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/.../redo03.log';
3、检查
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/.../redo01.log NO
2 ONLINE /u01/.../redo02.log NO
3 ONLINE /u01/.../redo03.log NO
注:redo 已迁移到新的路径下,我这里用的是文件系统,如果是裸设备也是一样的。
****************************************************************
二、迁移undo 表空间(如果是rac,则每个实例对应一下undo 表空间)
*****************************************************************
1、检查undo 表空间信息
SQL> select tablespace_name,sum(bytes)/1024/1024 size_m from dba_data_files where tablespace_name like 'UNDOTBS%' group by tablespace_name;
TABLESPACE_NAME SIZE_M
------------------------------ ----------
UNDOTBS2 32680
UNDOTBS1 67409.9609
2、检查每个实例对应的undo (dw1)
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ------------------------------ ------------------------------
undo_tablespace string UNDOTBS1
注:另一个undo 则对应UNDOTBS2
3、添加新的undo 表空间(在新的存储上,大小跟旧的一样)
--undotbs3
SQL> create undo tablespace undotbs3 datafile '/dev/..._32g_1417' size 32760M autoextend off;
Tablespace created.
SQL> alter tablespace undotbs3 add datafile '/dev/..._32g_1418' size 32760m autoextend off;
Tablespace altered.
--undotbs4
SQL> create undo tablespace undotbs4 datafile '/dev/..._32g_1419' size 32760M autoextend off;
Tablespace created.
4、检查undo 表空间信息
SQL> select file_name,tablespace_name,bytes/1024/1024 size_m from dba_data_files where tablespace_name like 'UNDOTBS%' order by tablespace_name
2 /
FILE_NAME TABLESPACE_NAME SIZE_M
-------------------------------------------------- ------------------------------ ----------
/dev/..._4g_047 UNDOTBS1 4095
/dev/..._16g_2136 UNDOTBS1 16300
.
.
.
/dev/..._32g_1419 UNDOTBS4 32760
SQL> select tablespace_name,sum(bytes)/1024/1024 size_m from dba_data_files where tablespace_name like 'UNDOTBS%' group by tablespace_name;
TABLESPACE_NAME SIZE_M
------------------------------ ----------
UNDOTBS4 32760
UNDOTBS3 65520
UNDOTBS2 32680
UNDOTBS1 67409.9609
5、切换undo 表空间(undo_tablespace 参数是动态参数,可以直接设置而不用重启数据库)
--dw1
SQL> alter system set undo_tablespace='UNDOTBS3' sid='dw1' ;
System altered.
--dw2
SQL> alter system set undo_tablespace='UNDOTBS4' sid='dw2';
System altered.
6、检查数据库默认undo 表空间
SQL> show parameter undo_tablespace
--dw1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS3
--dw2
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS4
注:undo 表空间间已切换到undotbs2,切换成功
7、删除旧的undo 表空间(切换undo 后,一般要等一段时间,再删除旧的undo 表空间)
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
注:删除旧的undo 这一步可以等系统稳定的运行一段时间后,再行删除
8、最后检查undo 表空间的情况
SQL> select file_name,tablespace_name,bytes/1024/1024 size_m from dba_data_files where tablespace_name like 'UNDOTBS%' order by 2;
FILE_NAME TABLESPACE_NAME SIZE_M
------------------------------ ------------------------------ ----------
/dev/..._4g_047 UNDOTBS1 4095
/dev/..._16g_2136 UNDOTBS1 16300
/dev/..._16g_2135 UNDOTBS1 16300
/dev/..._2g_015 UNDOTBS1 2047.99219
/dev/..._2g_014 UNDOTBS1 2047.99219
/dev/..._2g_013 UNDOTBS1 2047.99219
/dev/..._8g_001 UNDOTBS1 8190
/dev/..._8g_003 UNDOTBS1 8190
/dev/..._4g_046 UNDOTBS1 4095
/dev/..._2g_011 UNDOTBS1 2047.99219
/dev/..._2g_012 UNDOTBS1 2047.99219
FILE_NAME TABLESPACE_NAME SIZE_M
------------------------------ ------------------------------ ----------
/dev/..._16g_2137 UNDOTBS2 16300
/dev/..._8g_002 UNDOTBS2 8190
/dev/..._8g_004 UNDOTBS2 8190
/dev/..._32g_1418 UNDOTBS3 32760
/dev/..._32g_1417 UNDOTBS3 32760
/dev/..._32g_1419 UNDOTBS4 32760
17 rows selected.
注:现在undo 表空间的名字为undotbs2,为使其名字跟以前一样,需用同样的方法再作一次undo 表空间的迁移。这里不作说明
**************************************************
三、迁移临时表空间
有2种选择:
1、新建一个临时表空间,再切换到新的临时表空间,然后再删除旧的
2、给现在的临时表空间添加新的数据文件,再删除旧的数据库文件(如果数据库正在使用临时表空间,可能会对数据库造成影响)
我采用第1种
**************************************************
1、检查临时表空间信息
SQL> select file_name,tablespace_name,BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------------------------------------------------------------------- --------------- ---------------
/u01/.../temp01.dbf TEMP 20
2、创建新的临时表空间(大小跟旧的一样)
SQL> create temporary tablespace temp2 tempfile '/u01/.../temp02.dbf' size 20m autoextend off;
Tablespace created.
3、检查临时表空间信息
SQL> select file_name,tablespace_name,BYTES/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------------------------------------------------------------------- --------------- ---------------
/u01/.../temp02.dbf TEMP2 20
/u01/.../temp01.dbf TEMP 20
--检查数据库默认表空间
SQL> select property_name,property_value from database_properties
2 where property_name like 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
-------------------------------------------------- --------------------
DEFAULT_TEMP_TABLESPACE TEMP
--检查用户临时表空间
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
HR TEMP
MGMT_VIEW TEMP
OUTLN TEMP
MDSYS TEMP
ORDSYS TEMP
EXFSYS TEMP
DMSYS TEMP
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
WMSYS TEMP
CTXSYS TEMP
ANONYMOUS TEMP
XDB TEMP
ORDPLUGINS TEMP
SI_INFORMTN_SCHEMA TEMP
OLAPSYS TEMP
SCOTT TEMP
ORACLE_OCM TEMP
TSMSYS TEMP
BI TEMP
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
PM TEMP
MDDATA TEMP
IX TEMP
SH TEMP
DIP TEMP
OE TEMP
28 rows selected.
4、切换临时表空间
SQL> alter database default temporary tablespace temp2;
Database altered.
--检查临时表空间
SQL> select property_name,property_value from database_properties
2 where property_name like 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
-------------------------------------------------- --------------------
DEFAULT_TEMP_TABLESPACE TEMP2
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP2
SYSTEM TEMP2
DBSNMP TEMP2
SYSMAN TEMP2
HR TEMP2
MGMT_VIEW TEMP2
OUTLN TEMP2
MDSYS TEMP2
ORDSYS TEMP2
EXFSYS TEMP2
DMSYS TEMP2
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
WMSYS TEMP2
CTXSYS TEMP2
ANONYMOUS TEMP2
XDB TEMP2
ORDPLUGINS TEMP2
SI_INFORMTN_SCHEMA TEMP2
OLAPSYS TEMP2
SCOTT TEMP2
ORACLE_OCM TEMP2
TSMSYS TEMP2
BI TEMP2
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
PM TEMP2
MDDATA TEMP2
IX TEMP2
SH TEMP2
DIP TEMP2
OE TEMP2
28 rows selected.
4、删除旧的临时表空间
SQL> drop tablespace temp including contents and datafiles cascade constraints;
Tablespace dropped.
注:1、临时表空间已切换,但临时表空间名也原来的不致,为使其一致,则用上述方法,再做一次切换。这里不再细说。
2、这里用的是文件系统,裸设备也可用同样的方法作迁移。
******************************************************
四、迁移控制文件(需要重启数据库)
1、启动数据为到mount 状态
2、备份控制文件
3、修改control_files 参数,设置到新存储上
4、重启数据库到nomount 状态
5、用rman 还原控制文件
6、启动到mount 状态
7、用resetlogs 参数据打开数据库(之前可能需要:rman> recover database;)
*******************************************************
1、启动数据库到mount 状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 176163952 bytes
Database Buffers 415236096 bytes
Redo Buffers 6299648 bytes
Database mounted.
2、备份控制文件
[oracle@oracle_test oraback]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Oct 19 21:55:43 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCLTEST (DBID=2602092114, not open)
RMAN>
RMAN>
RMAN> backup current controlfile format '/u01/.../controlfile.bak';
Starting backup at 19-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 19-OCT-14
channel ORA_DISK_1: finished piece 1 at 19-OCT-14
piece handle=/u01/.../controlfile.bak tag=TAG20141019T215628 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 19-OCT-14
3、修改control_files 参数
SQL> alter system set control_files=
2 '/dev/raw/raw8',
3 '/dev/raw/raw9',
4 '/dev/raw/raw10'
5 scope=spfile;
System altered.
4、重启数据库到 nomount 状态
--关闭数据库
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--启动到 nomount 状态
SQL> startup nomount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 176163952 bytes
Database Buffers 415236096 bytes
Redo Buffers 6299648 bytes
5、用rman 还原控制文件
RMAN> restore controlfile from '/u01/.../controlfile04.ctl.bak';
Starting restore at 19-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
output filename=/u01/.../control01.ctl
output filename=/u01/.../control02.ctl
output filename=/u01/.../control03.ctl
Finished restore at 19-OCT-14
6、启动数据库到 mount 状态
SQL> alter database mount;
Database altered.
7、用rman recover 数据库
RMAN> recover database;
Starting recover at 19-OCT-14
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 19-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 19-OCT-14
Starting implicit crosscheck copy at 19-OCT-14
using channel ORA_DISK_1
Crosschecked 37 objects
Finished implicit crosscheck copy at 19-OCT-14
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 1
starting media recovery
archive log thread 1 sequence 1 is already on disk as file /u01/.../redo01.log
archive log filename=/u01/.../redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-OCT-14
8、用resetlogs 参数据打开数据库
SQL> alter database open resetlogs;
Database altered.
注:这里应做一个全备
******************************************************
六、迁称spfile
1、create pfile
2、用pfile 重启数据库
3、create spfile 到新存储的位置
4、修改所有节点$ORACLE_HOME/dbs/init
的spfile 路径参数
5、修改ocr 信息
6、用srvctl 重启数据库实例,看是否能正常启动
***********************************************************
1、创建pfile(要指定路径)
--查看spfile
sql>show parameter spfile
--创建pfile
sql>create pfile='/tmp/pfile.ora' from spfile;
2、用pfile 重启数据库
sql>shutdown immediate
sql>startup pfile='/tmp/pfile.ora';
3、用pfile 创建spfile
create spfile='+DBSYS/dwrac/spfiledwrac.ora' from pfile='/tmp/pfile.ora';
4、修改$ORACLE_HOME/dbs/init 里 spfile 的路径到新的位置
SPFILE='+DBSYS/dwrac/spfiledwrac.ora'
5、修改ocr
--查看ocr信息
srvctl config database -d dwrac -a
--修改ocr spfile 路径
srvctl modify database -d dwrac -p '+DBSYS/dwrac/spfiledwrac.ora'
6、用srvctl 重启数据库实例
srvctl start instance -d oracle_test -i oracle_test1,oracle_test2
*************************************************************************
七、迁移ocr
1、检查ocr 情况
2、根据情ocr 情况在新的存储上创建对应的ocr
3、增加镜像ocr (如果没有镜像)
4、迁移普通ocr 和 镜像ocr
*************************************************************************
1、检查ocr 情况
jfdb01:/home/oracle$ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048300
Used space (kbytes) : 4084
Available space (kbytes) : 1044216
ID : 2090936660
Device/File Name : /dev/..._ocr01
Device/File integrity check succeeded
Device/File Name : /dev/..._ocr02
Device/File integrity check succeeded
Cluster registry integrity check succeeded
注:有2个ocr 文件,第一个(/dev/..._ocr01)是普通,第二个(/dev/..._ocr02)是镜像
2、ocr 迁移
--迁移普通ocr
./ocrconfig -replace ocr /dev/..._ocr03
--迁移镜像ocr
./ocrconfig -replace ocrmirror /dev/..._ocr04
3、检查ocr 情况
jfdb02:/oracle/product/crs/10.2.0.4/crs_1/bin$ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048300
Used space (kbytes) : 4084
Available space (kbytes) : 1044216
ID : 2090936660
Device/File Name : /dev/..._ocr03
Device/File integrity check succeeded
Device/File Name : /dev/..._ocr04
Device/File integrity check succeeded
Cluster registry integrity check succeeded
注:ocr 已更改
4、检查crs 状态
jfdb02:/oracle/product/crs/10.2.0.4/crs_1/bin$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
注:状态正常,迁移成功
***************************************************
业务数据迁移
用move + parallel
*****************************************************
--表分区迁移 + 日志
select 'alter table ' || owner || '.' || segment_name ||' move partition '|| PARTITION_NAME || ' tablespace tbs_dw06 PARALLEL (DEGREE 6) update global indexes nologging;'|| chr(13) ||
'insert into dbmonitor.object_move_log values(' || '''' || owner || '''' || ',' || '''' || segment_name || '''' || ',' || '''' || partition_name || '''' || ',' || '''' || segment_type || '''' || ',' || '''' || tablespace_name || '''' || ',' || '''' || 'tbs_dw06' || '''' || ',' || bytes/1024/1024 || ', sysdate' || '); ' || chr(13) ||
'commit;'
from dba_segments
where 1=1
and segment_type = 'TABLE PARTITION'
and segment_name = 'T_DW_MEDIA_TASK_72H_LIST'
and tablespace_name = 'TBS_DW_06'
order by 1;
--子分区迁移
alter table NXTDW.TB_MS_MEDIA_TASK_RED1 move subpartition PART_201202_SUB_28 tablespace tbs_dw_04;
select 'alter table ' || owner || '.' || segment_name || ' move subpartition ' || partition_name || ' tablespace tbs_dw04 PARALLEL (DEGREE 2) update global indexes;'
from dba_segments
where 1=1
and tablespace_name = 'TBS_DW_04'
AND segment_type = 'TABLE SUBPARTITION'
--lob对像迁移,lob索引会自动迁移
select 'alter table ' || table_owner || '.' || table_name || ' move partition ' || partition_name || ' tablespace ' || tablespace_name || ' lob(' || column_name || ') store as (tablespace ' || 'new_tbs_name) nologging;'
from dba_lob_partitions
where 1=1
--and table_name = 'LOB_PAR_TEST'
--and table_owner = 'HR'
and tablespace_name = 'TBS_DW_04'
--索引组织表迁移,在segment里找到索引,再通过索引从dba_object里找到对应的表。move 表后,其索引自动移动。
select distinct owner,segment_name from dba_segments where segment_type in('INDEX PARTITION') and partition_name like 'PTAIL%'
and tablespace_name = 'TBS_DW_04'收起