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

最新推荐文章于 2024-01-08 10:59:06 发布

小宝老豆

最新推荐文章于 2024-01-08 10:59:06 发布

阅读量924

点赞数

分类专栏： Oracle RAC Oracle 备份与恢复文章标签： oracle character database object 2010 schema

Oracle RAC 同时被 2 个专栏收录

101 篇文章 0 订阅

订阅专栏

Oracle 备份与恢复

33 篇文章 0 订阅

订阅专栏

昨天整理做了用导出导入的方法，将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.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

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

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

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;' 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