Oracle数据库数据迁移.docx

1、Oracle数据库数据迁移查看执行计划方法总结之一：explain plan命令2014-04-10 00:20:37分类：Oracle查看执行计划的方法有很多种，笔者今后将在博客里一一为大家介绍。本文介绍的是第一种方法，利用explain plan命令来查看执行计划。1.用hr用户登录数据库oracleENMOEDU $ sqlplushr/oracleENMOEDUSQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 10 00:11:00 2014Copyright (c) 1982, 2011, Oracle. All rights re

2、served.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsHRENMOEDU 2.使用explain plan命令HRENMOEDU explain plan for select salary from employees where first_name like Pat;Explained.3.查看执行计划HRENM

3、OEDU select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-Plan hash value: 3033625502-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |-| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)|00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 2 (0)|00:00:01 |* 2 | INDEX SKIP SCAN | EM

4、P_NAME_IX | 1 | | 1 (0)|00:00:01 |-Predicate Information (identified by operation id):- 2 - access(FIRST_NAME=Pat) filter(FIRST_NAME=Pat)15 rows selected.至此，执行计划查看成功。Oracle数据库迁移之一：RMAN分类：Oracle数据库的迁移方式千变万化，各有千秋。今天为大家呈现的是第一种方法，利用RMAN来完成数据库的迁移工作。1.打开RMANoracleENMOEDU $ rman target /Recovery Manager: R

5、elease 11.2.0.3.0 - Production on Sun Apr 6 17:52:06 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ENMOEDU (DBID=87396644)RMAN2.查看当前的配置信息并修改配置信息RMAN show all;RMAN configuration parameters for database with db_unique_name ENMOEDU are:CON

6、FIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO %F; # defaultCONFIGURE DEVICE TYPE DISK PARALLE

7、LISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGOR

8、ITHM AES128; # defaultCONFIGURE COMPRESSION ALGORITHM BASIC AS OF RELEASE DEFAULT OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ENMOEDU.f; # defaultRMAN CONFIGURE BACK

9、UP OPTIMIZATION ON;new RMAN configuration parameters:CONFIGURE BACKUP OPTIMIZATION ON;new RMAN configuration parameters are successfully storedRMAN CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters are successf

10、ully stored3.对数据库进行一次全备RMAN backup database include current controlfile plus archivelog;Starting backup at 06-APR-14current log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1 . . .channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Fini

11、shed backup at 06-APR-144.查看备份情况，删除冗余备份RMAN list backup;List of Backup Sets=BS Key Size Device Type Elapsed Time Completion Time- - - - -1 70.64M DISK 00:00:00 06-APR-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140406T181504 Piece Name: /u01/app/oracle/fast_recovery_area/ENMOEDU/backupse

12、t/2014_04_06/o1_mf_annnn_TAG20140406T181504_9n2b987h_.bkp.BS Key Type LV Size Device Type Elapsed Time Completion Time- - - - - - -11 Full 9.36M DISK 00:00:00 06-APR-14 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20140406T183542 Piece Name: /u01/app/oracle/fast_recovery_area/ENMOEDU/autoback

13、up/2014_04_06/o1_mf_s_844194942_9n2chyvw_.bkp SPFILE Included: Modification time: 04-APR-14 SPFILE db_unique_name: ENMOEDU Control File Included: Ckp SCN: 1302814 Ckp time: 06-APR-14RMAN delete obsolete;RMAN retention policy will be applied to the commandRMAN retention policy is set to redundancy 1u

14、sing channel ORA_DISK_1Deleting the following obsolete backups and copies:Type Key Completion Time Filename/Handle- - - -Archive Log 1 06-APR-14 /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_04_06/o1_mf_1_91_9n27s6o2_.arc.Do you really want to delete the above objects (enter YES or NO)?

15、 yes.deleted backup piecebackup piece handle=/u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2014_04_06/o1_mf_ncnnf_TAG20140406T183529_9n2chwnm_.bkp RECID=9 STAMP=844194940Deleted 11 objects5.将快速恢复区下的备份发送到要建立数据库的主机上oracleENMOEDU $ cd /u01/app/oracle/fast_recovery_area/oracleENMOEDUfast_recovery

16、_area$ scp -r ENMOEDU/ oracle192.168.80.11:/u01/app/oracle/fast_recovery_area/oracle192.168.80.11s password: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

17、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:006.在建立数据库的主机上查看传输过来的备份oracleFRANK $ ls /u01/app/oracle/fast_recovery_area/ENMOEDU FRANK此时，数据库的备份传输成功。7.利用RMAN恢复数据库oracleFRANK ENMOEDU$

18、export ORACLE_SID=ENMOEDUoracleFRANK ENMOEDU$ echo $ORACLE_SIDENMOEDUoracleFRANK ENMOEDU$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Sat Mar 1 15:10:16 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database (not started)RMA

19、N startup nomountOracle instance startedTotal System Global Area 171581440 bytesFixed Size 1343668 bytesVariable Size 117444428 bytesDatabase Buffers 50331648 bytesRedo Buffers 2461696 bytes1)恢复控制文件RMAN restore controlfile FROM AUTOBACKUP;Starting restore at 01-MAR-14using channel ORA_DISK_1recovery

20、 area destination: /u01/app/oracle/fast_recovery_areadatabase name (or database unique name) used for search: ENMOEDUchannel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp found in the recovery areaAUTOBACKUP search with format

21、 %F not attempted because DBID was not setchannel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkpRMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of restore com

22、mand at 03/01/2014 15:33:58ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkpORA-19504: failed to create file /u01/app/oracle/oradata/ENMOEDU/control01.ctlORA-27040: file create error, unable to create fileLi

23、nux Error: 2: No such file or directoryAdditional information: 1根据提示我们创建文件夹：oracleFRANK 2014_04_06$ mkdir -p /u01/app/oracle/oradata/ENMOEDU继续之前的恢复工作：RMAN restore controlfile FROM AUTOBACKUP;Starting restore at 01-MAR-14using channel ORA_DISK_1recovery area destination: /u01/app/oracle/fast_recovery

24、_areadatabase name (or database unique name) used for search: ENMOEDUchannel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp found in the recovery areaAUTOBACKUP search with format %F not attempted because DBID was not setchanne

25、l ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkpchannel ORA_DISK_1: control file restore from AUTOBACKUP completeoutput file name=/u01/app/oracle/oradata/ENMOEDU/control01.ctloutput file name=/u01/ap

26、p/oracle/fast_recovery_area/ENMOEDU/control02.ctlFinished restore at 01-MAR-14控制文件恢复成功。2)将数据库启动到mount模式RMAN alter database mount;database mountedreleased channel: ORA_DISK_1启动成功。3）恢复数据库文件RMAN restore database;Starting restore at 01-MAR-14Starting implicit crosscheck backup at 01-MAR-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKCrosschecked 9 objectsFinished implicit crosscheck backup at 01