Oracle数据库数据迁移.docx
《Oracle数据库数据迁移.docx》由会员分享，可在线阅读，更多相关《Oracle数据库数据迁移.docx（28页珍藏版）》请在冰豆网上搜索。
Oracle数据库数据迁移
查看执行计划方法总结之一：
explainplan命令
2014-04-1000:
20:
37
分类：
Oracle
查看执行计划的方法有很多种，笔者今后将在博客里一一为大家介绍。
本文介绍的是第一种方法，利用explainplan命令来查看执行计划。
1.用hr用户登录数据库
[oracle@ENMOEDU~]$sqlplushr/oracle@ENMOEDU
SQL*Plus:
Release11.2.0.3.0ProductiononThuApr1000:
11:
002014
Copyright（c）1982,2011,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
HR@ENMOEDU>
2.使用explainplan命令
HR@ENMOEDU>explainplanforselectsalaryfromemployeeswherefirst_namelike'Pat';
Explained.
3.查看执行计划
HR@ENMOEDU>select*fromtable（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Planhashvalue:
3033625502
-------------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost（%CPU）| Time |
-------------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT | | 1| 11| 2 （0）| 00:
00:
01|
| 1| TABLEACCESSBYINDEXROWID|EMPLOYEES | 1| 11| 2 （0）| 00:
00:
01|
|* 2| INDEXSKIPSCAN |EMP_NAME_IX| 1| | 1 （0）| 00:
00:
01|
-------------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-access（"FIRST_NAME"='Pat'）
filter（"FIRST_NAME"='Pat'）
15rowsselected.
至此，执行计划查看成功。
Oracle数据库迁移之一：
RMAN
分类：
Oracle
数据库的迁移方式千变万化，各有千秋。
今天为大家呈现的是第一种方法，利用RMAN来完成数据库的迁移工作。
1.打开RMAN
[oracle@ENMOEDU~]$rmantarget/
RecoveryManager:
Release11.2.0.3.0-ProductiononSunApr617:
52:
062014
Copyright（c）1982,2011,Oracleand/oritsaffiliates. Allrightsreserved.
connectedtotargetdatabase:
ENMOEDU（DBID=87396644）
RMAN>
2.查看当前的配置信息并修改配置信息
RMAN>showall;
RMANconfigurationparametersfordatabasewithdb_unique_nameENMOEDUare:
CONFIGURERETENTIONPOLICYTOREDUNDANCY1;#default
CONFIGUREBACKUPOPTIMIZATIONOFF;#default
CONFIGUREDEFAULTDEVICETYPETODISK;#default
CONFIGURECONTROLFILEAUTOBACKUPOFF;#default
CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';#default
CONFIGUREDEVICETYPEDISKPARALLELISM1BACKUPTYPETOBACKUPSET;#default
CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;#default
CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;#default
CONFIGUREMAXSETSIZETOUNLIMITED;#default
CONFIGUREENCRYPTIONFORDATABASEOFF;#default
CONFIGUREENCRYPTIONALGORITHM'AES128';#default
CONFIGURECOMPRESSIONALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;#default
CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;#default
CONFIGURESNAPSHOTCONTROLFILENAMETO'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ENMOEDU.f';#default
RMAN>CONFIGUREBACKUPOPTIMIZATIONON;
newRMANconfigurationparameters:
CONFIGUREBACKUPOPTIMIZATIONON;
newRMANconfigurationparametersaresuccessfullystored
RMAN>CONFIGURECONTROLFILEAUTOBACKUPON;
newRMANconfigurationparameters:
CONFIGURECONTROLFILEAUTOBACKUPON;
newRMANconfigurationparametersaresuccessfullystored
3.对数据库进行一次全备
RMAN>backupdatabaseincludecurrentcontrolfileplusarchivelog;
Startingbackupat06-APR-14
currentlogarchived
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:
ORA_DISK_1
.
.
.
channelORA_DISK_1:
backupsetcomplete,elapsedtime:
00:
00:
01
Finishedbackupat06-APR-14
4.查看备份情况，删除冗余备份
RMAN>listbackup;
ListofBackupSets
===================
BSKey Size DeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------
1 70.64M DISK 00:
00:
00 06-APR-14
BPKey:
1 Status:
AVAILABLE Compressed:
NO Tag:
TAG20140406T181504
PieceName:
/u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2014_04_06/o1_mf_annnn_TAG20140406T181504_9n2b987h_.bkp
.
.
.
BSKey TypeLVSize DeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------------
11 Full 9.36M DISK 00:
00:
00 06-APR-14
BPKey:
11 Status:
AVAILABLE Compressed:
NO Tag:
TAG20140406T183542
PieceName:
/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_04_06/o1_mf_s_844194942_9n2chyvw_.bkp
SPFILEIncluded:
Modificationtime:
04-APR-14
SPFILEdb_unique_name:
ENMOEDU
ControlFileIncluded:
CkpSCN:
1302814 Ckptime:
06-APR-14
RMAN>deleteobsolete;
RMANretentionpolicywillbeappliedtothecommand
RMANretentionpolicyissettoredundancy1
usingchannelORA_DISK_1
Deletingthefollowingobsoletebackupsandcopies:
Type Key CompletionTime Filename/Handle
----------------------------------------------------------------
ArchiveLog 1 06-APR-14 /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_04_06/o1_mf_1_91_9n27s6o2_.arc
.
.
.
Doyoureallywanttodeletetheaboveobjects（enterYESorNO）?
yes
.
.
.
deletedbackuppiece
backuppiecehandle=/u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2014_04_06/o1_mf_ncnnf_TAG20140406T183529_9n2chwnm_.bkpRECID=9STAMP=844194940
Deleted11objects
5.将快速恢复区下的备份发送到要建立数据库的主机上
[oracle@ENMOEDU~]$cd/u01/app/oracle/fast_recovery_area/
[oracle@ENMOEDUfast_recovery_area]$scp-rENMOEDU/oracle@192.168.80.11:
/u01/app/oracle/fast_recovery_area/
oracle@192.168.80.11'spassword:
o1_mf_1_95_9n2chxov_.arc 100%3072 3.0KB/s 00:
00
o1_mf_nnndf_TAG20140406T183529_9n2chkgc_.bkp 100%1059MB 53.0MB/s 00:
20
o1_mf_nnndf_TAG20140406T183529_9n2chrl2_.bkp 100% 224MB 55.9MB/s 00:
04
o1_mf_annnn_TAG20140406T183541_9n2chxs6_.bkp 100%4608 4.5KB/s 00:
00
o1_mf_s_844194942_9n2chyvw_.bkp 100%9600KB 9.4MB/s 00:
00
control02.ctl 100%9520KB 9.3MB/s 00:
00
6.在建立数据库的主机上查看传输过来的备份
[oracle@FRANK~]$ls/u01/app/oracle/fast_recovery_area/
ENMOEDU FRANK
此时，数据库的备份传输成功。
7.利用RMAN恢复数据库
[oracle@FRANKENMOEDU]$exportORACLE_SID=ENMOEDU
[oracle@FRANKENMOEDU]$echo$ORACLE_SID
ENMOEDU
[oracle@FRANKENMOEDU]$rmantarget/
RecoveryManager:
Release11.2.0.3.0-ProductiononSatMar115:
10:
162014
Copyright（c）1982,2011,Oracleand/oritsaffiliates. Allrightsreserved.
connectedtotargetdatabase（notstarted）
RMAN>startupnomount
Oracleinstancestarted
TotalSystemGlobalArea 171581440bytes
FixedSize 1343668bytes
VariableSize 117444428bytes
DatabaseBuffers 50331648bytes
RedoBuffers 2461696bytes
1）恢复控制文件
RMAN>restorecontrolfileFROMAUTOBACKUP;
Startingrestoreat01-MAR-14
usingchannelORA_DISK_1
recoveryareadestination:
/u01/app/oracle/fast_recovery_area
databasename（ordatabaseuniquename）usedforsearch:
ENMOEDU
channelORA_DISK_1:
AUTOBACKUP/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkpfoundintherecoveryarea
AUTOBACKUPsearchwithformat"%F"notattemptedbecauseDBIDwasnotset
channelORA_DISK_1:
restoringcontrolfilefromAUTOBACKUP/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
RMAN-00571:
===========================================================
RMAN-00569:
===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:
===========================================================
RMAN-03002:
failureofrestorecommandat03/01/201415:
33:
58
ORA-19870:
errorwhilerestoringbackuppiece/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
ORA-19504:
failedtocreatefile"/u01/app/oracle/oradata/ENMOEDU/control01.ctl"
ORA-27040:
filecreateerror,unabletocreatefile
LinuxError:
2:
Nosuchfileordirectory
Additionalinformation:
1
根据提示我们创建文件夹：
[oracle@FRANK2014_04_06]$mkdir-p/u01/app/oracle/oradata/ENMOEDU
继续之前的恢复工作：
RMAN>restorecontrolfileFROMAUTOBACKUP;
Startingrestoreat01-MAR-14
usingchannelORA_DISK_1
recoveryareadestination:
/u01/app/oracle/fast_recovery_area
databasename（ordatabaseuniquename）usedforsearch:
ENMOEDU
channelORA_DISK_1:
AUTOBACKUP/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkpfoundintherecoveryarea
AUTOBACKUPsearchwithformat"%F"notattemptedbecauseDBIDwasnotset
channelORA_DISK_1:
restoringcontrolfilefromAUTOBACKUP/u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
channelORA_DISK_1:
controlfilerestorefromAUTOBACKUPcomplete
outputfilename=/u01/app/oracle/oradata/ENMOEDU/control01.ctl
outputfilename=/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
Finishedrestoreat01-MAR-14
控制文件恢复成功。
2）将数据库启动到mount模式
RMAN>alterdatabasemount;
databasemounted
releasedchannel:
ORA_DISK_1
启动成功。
3）恢复数据库文件
RMAN>restoredatabase;
Startingrestoreat01-MAR-14
Startingimplicitcrosscheckbackupat01-MAR-14
allocatedchannel:
ORA_DISK_1
channelORA_DISK_1:
SID=19devicetype=DISK
Crosschecked9objects
Finishedimplicitcrosscheckbackupat01
升级会员 