RAC 实例 迁移到 单实例 -- 使用导出导入
昨天整理做了用导出导入的方法，将Oracle 单实例 迁移到 RAC 实例的实验。 今天来做一个用导出导入的方法将RAC 实例迁移到 Oracle 单实例。 方法都差不多。 只不过顺序倒过来了。
Oracle 单实例 迁移到 RAC 实例 -- 使用导出导入方法
http://blog.csdn.net/tianlesoftware/archive/2010/09/28/5912602.aspx
昨天用的是数据泵（expdp/impdp）来导的，今天用逻辑导出导入(exp/imp) 来做这个实验。 数据泵它有很大的局限性，它只能在服务器端执行。
http://blog.csdn.net/tianlesoftware/archive/2009/10/24/4718366.aspx
接着昨天的那个实验做，用户，表空间就不在创建了，还用昨天的Dave用户和Tianlesoftware 表空间。
实验步骤：
1. 在RAC 实例上创建一下对象
2. 用exp 将实例导出
3. 导入前的准备工作
4. imp 导入单实例。
5. 检查无效对象
一． 在RAC 实例上创建一些对象
1.1 RAC 状态
[oracle@rac1 ~]$ cd /u01/app/oracle/product/crs/bin/
[oracle@rac1 bin]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.orcl.db application ONLINE ONLINE rac2
ora....oltp.cs application ONLINE ONLINE rac2
ora....cl1.srv application ONLINE ONLINE rac1
ora....cl2.srv application ONLINE ONLINE rac2
ora....l1.inst application ONLINE ONLINE rac1
ora....l2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
1.2 连接实例，创建对象
[oracle@rac2 ~]$ export ORACLE_SID=orcl2
[oracle@rac2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 28 22:27:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL> conn dave/dave;
Connected.
SQL> create table dba as select * from all_users;
Table created.
SQL> commit;
Commit complete.
SQL> select * from dba;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DAVE 55 28-SEP-10
SCOTT 54 30-JUN-05
MGMT_VIEW 53 30-JUN-05
MDDATA 50 30-JUN-05
SYSMAN 51 30-JUN-05
MDSYS 46 30-JUN-05
SI_INFORMTN_SCHEMA 45 30-JUN-05
ORDPLUGINS 44 30-JUN-05
ORDSYS 43 30-JUN-05
OLAPSYS 47 30-JUN-05
ANONYMOUS 39 30-JUN-05
XDB 38 30-JUN-05
CTXSYS 36 30-JUN-05
EXFSYS 34 30-JUN-05
WMSYS 25 30-JUN-05
DBSNMP 24 30-JUN-05
TSMSYS 21 30-JUN-05
DMSYS 35 30-JUN-05
DIP 19 30-JUN-05
OUTLN 11 30-JUN-05
SYSTEM 5 30-JUN-05
SYS 0 30-JUN-05
22 rows selected.
SQL>
二． Exp 导出数据
这里我们按照用户的模式来导。 导出dave用户的数据，然后导入dave用户的数据。
[oracle@rac1 bin]$ export ORACLE_SID=orcl1
[oracle@rac1 bin]$ exp dave/dave owner=dave file=/u01/dave_2010929.dmp log=/u01/dave.log;
Export: Release 10.2.0.1.0 - Production on Tue Sep 28 22:39:11 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DAVE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DAVE
About to export DAVE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DAVE's tables via Conventional Path ...
. . exporting table DBA 22 rows exported
. . exporting table USERINFO 2 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
导出成功，dump文件放在/u01目录下。
三． 导入前的主备工作
如果导入的用户和表空间已经在单实例上存在，那么我们最好清空用户的所有对象。
如果单实例上没有，那么就需要创建用户，及相关的表空间。
这里模拟用户和表空间不存在的情况。 我们会在单实例上创建用户Dave 和对应的表空间。
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:/APP/ADMINISTRATOR/ORADATA/ORCL/USERS01.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/UNDOTBS01.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/SYSAUX01.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/SYSTEM01.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/DAVE0.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/DBA1.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/CATALOG1.DBF
D:/APP/ADMINISTRATOR/ORADATA/ORCL/CATALOG_TS1.DBF
已选择8行。
SQL> create tablespace tianlesoftware datafile
'D:/APP/ADMINISTRATOR/ORADATA/ORCL/tianlesoftware.dbf' size 50m;
表空间已创建。
SQL> create user dave identified by dave default tablespace tianlesoftware temporary tablespace temp;
用户已创建。
SQL> grant dba to dave;
授权成功。
SQL> grant connect to dave;
授权成功。
SQL> grant resource to dave;
授权成功。
SQL>
四． 导入数据
4.1 将RAC 实例的dump 文件copy到单实例
[oracle@rac1 bin]$ cd /u01
[oracle@rac1 u01]$ ls
app dave.log RAC_hot_database_backup.sh
backup dave.ora RAC_hot_database_backup.sh.out
dave_2010929.dmp impdp.log tianlesoftware.dmp
[oracle@rac1 u01]$ scp dave_2010929.dmp 10.85.10.15://u01
The authenticity of host '10.85.10.15 (10.85.10.15)' can't be established.
RSA key fingerprint is 7b:f7:26:7e:6b:2a:1f:6b:67:f9:cc:4e:67:07:91:d1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.85.10.15' (RSA) to the list of known hosts.
oracle@10.85.10.15's password:
dave_2010929.dmp 100% 16KB 16.0KB/s 00:00
4.2 用imp导入数据
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ imp dave/dave fromuser=dave touser=dave file=/u01/dave_2010929.dmp log=/u01/dave.log;
Import: Release 10.2.0.1.0 - Production on Wed Sep 29 01:42:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . importing table "DBA" 22 rows imported
. . importing table "USERINFO" 2 rows imported
Import terminated successfully without warnings.
五． 检查无效对象
具体参考：
Oracle Compile 编译 无效对象
http://blog.csdn.net/tianlesoftware/archive/2009/11/21/4843600.aspx
SQL> select 'ALTER ' || OBJECT_TYPE || ' '||owner||'.' || OBJECT_NAME || ' COMPILE;' from all_objects where status = 'INVALID' and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'TABLE', 'VIEW', 'SEQUENCE', 'TRIGGER');
no rows selected
SQL> select 'ALTER PACKAGE ' || OWNER||'.'|| OBJECT_NAME || ' COMPILE body;' from ALL_objects where status = 'INVALID' and object_type in ('PACKAGE BODY');
no rows selected
没有无效对象， 验证下导入的数据：
SQL> conn dave/dave;
Connected.
SQL> select * from dba;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DAVE 55 28-SEP-10
SCOTT 54 30-JUN-05
MGMT_VIEW 53 30-JUN-05
MDDATA 50 30-JUN-05
SYSMAN 51 30-JUN-05
MDSYS 46 30-JUN-05
SI_INFORMTN_SCHEMA 45 30-JUN-05
ORDPLUGINS 44 30-JUN-05
ORDSYS 43 30-JUN-05
OLAPSYS 47 30-JUN-05
ANONYMOUS 39 30-JUN-05
XDB 38 30-JUN-05
CTXSYS 36 30-JUN-05
EXFSYS 34 30-JUN-05
WMSYS 25 30-JUN-05
DBSNMP 24 30-JUN-05
TSMSYS 21 30-JUN-05
DMSYS 35 30-JUN-05
DIP 19 30-JUN-05
OUTLN 11 30-JUN-05
SYSTEM 5 30-JUN-05
SYS 0 30-JUN-05
22 rows selected.
导入已经完成， 和普通的导出导入没有什么区别。
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：63306533; 聊天 群：40132017
--加群需要在备注说明SGA的组成部分，否则拒绝申请
