RAC 实例迁移到单实例 -- 使用导出导入

最新推荐文章于 2024-06-20 14:31:02 发布

iteye_3158

最新推荐文章于 2024-06-20 14:31:02 发布

阅读量131

点赞数

文章标签：数据库

昨天整理做了用导出导入的方法，将Oracle单实例迁移到RAC实例的实验。今天来做一个用导出导入的方法将RAC实例迁移到Oracle单实例。方法都差不多。只不过顺序倒过来了。

Oracle单实例迁移到RAC实例--使用导出导入方法

http://blog.csdn.net/xujinyang/article/details/6837071

昨天用的是数据泵（expdp/impdp）来导的，今天用逻辑导出导入(exp/imp)来做这个实验。数据泵它有很大的局限性，它只能在服务器端执行。

ORACLE数据库逻辑备份简单EXP/IMP

http://blog.csdn.net/xujinyang/article/details/6830199

接着昨天的那个实验做，用户，表空间就不在创建了，还用昨天的Dave用户和Tianlesoftware表空间。

实验步骤：

1.在RAC实例上创建一下对象

2.用exp将实例导出

3.导入前的准备工作

4.imp导入单实例。

5.检查无效对象

一．在RAC实例上创建一些对象

1.1RAC状态

[oracle@rac1 ~]$cd /u01/app/oracle/product/crs/bin/

[oracle@rac1 bin]$crs_stat -t

NameTypeTargetStateHost

------------------------------------------------------------

ora.orcl.dbapplicationONLINEONLINErac2

ora....oltp.cs applicationONLINEONLINErac2

ora....cl1.srv applicationONLINEONLINErac1

ora....cl2.srv applicationONLINEONLINErac2

ora....l1.inst applicationONLINEONLINErac1

ora....l2.inst applicationONLINEONLINErac2

ora....SM1.asm applicationONLINEONLINErac1

ora....C1.lsnr applicationONLINEONLINErac1

ora.rac1.gsdapplicationONLINEONLINErac1

ora.rac1.onsapplicationONLINEONLINErac1

ora.rac1.vipapplicationONLINEONLINErac1

ora....SM2.asm applicationONLINEONLINErac2

ora....C2.lsnr applicationONLINEONLINErac2

ora.rac2.gsdapplicationONLINEONLINErac2

ora.rac2.onsapplicationONLINEONLINErac2

ora.rac2.vipapplicationONLINEONLINErac2

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

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;

USERNAMEUSER_ID CREATED

------------------------------ ---------- ---------

DAVE55 28-SEP-10

SCOTT54 30-JUN-05

MGMT_VIEW53 30-JUN-05

MDDATA50 30-JUN-05

SYSMAN51 30-JUN-05

MDSYS46 30-JUN-05

SI_INFORMTN_SCHEMA45 30-JUN-05

ORDPLUGINS44 30-JUN-05

ORDSYS43 30-JUN-05

OLAPSYS47 30-JUN-05

ANONYMOUS39 30-JUN-05

XDB38 30-JUN-05

CTXSYS36 30-JUN-05

EXFSYS34 30-JUN-05

WMSYS25 30-JUN-05

DBSNMP24 30-JUN-05

TSMSYS21 30-JUN-05

DMSYS35 30-JUN-05

DIP19 30-JUN-05

OUTLN11 30-JUN-05

SYSTEM5 30-JUN-05

SYS0 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

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 tableDBA22 rows exported

. . exporting tableUSERINFO2 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

appdave.logRAC_hot_database_backup.sh

backupdave.oraRAC_hot_database_backup.sh.out

dave_2010929.dmpimpdp.logtianlesoftware.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.dmp100%16KB16.0KB/s00: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

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/xujinyang/article/details/6830032

SQL>select 'ALTER ' || OBJECT_TYPE || ' '||owner||'.' || OBJECT_NAME || ' COMPILE;' fromall_objects wherestatus = 'INVALID' andobject_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;

USERNAMEUSER_ID CREATED

------------------------------ ---------- ---------

DAVE55 28-SEP-10

SCOTT54 30-JUN-05

MGMT_VIEW53 30-JUN-05

MDDATA50 30-JUN-05

SYSMAN51 30-JUN-05