Oracle data migration (from Oracle11G to Oracle10G) and oracle11goracle10g

Source: Internet
Author: User

Oracle data migration (from Oracle11G to Oracle10G) and oracle11goracle10g


1. Database status
The production environment is 11 GB, the linux system, and the testing environment is 10 Gb. For windows systems, you need to export all the data of a user from the production environment and import the data to the testing environment.
Because the data volume is small, we are going to use EXP and IMP tools.



2. Export user data in the production environment
Command:
Exp \ 'System/syspl as sysdba \ 'owner = PLAS buffer = 10240000 FILE =/tmp/PLAS_20141113.DMP

Execution Process:
[Oracle @ localhost ~] $ Exp \ 'System/syspl as sysdba \ 'owner = PLAS buffer = 10240000 FILE =/tmp/PLAS_20141113.DMP


Export: Release 11.2.0.1.0-Production on Thu Nov 13 19:32:52 2014


Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.




Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set


About to export specified users...
. Exporting pre-schema procedural objects and actions
. Exporting foreign function library names for user PLAS
. Exporting PUBLIC type synonyms
. Exporting private type synonyms
. Exporting object type definitions for user PLAS
............
. 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 without warnings .'



3. import data in the test environment

3.1. Back up data in the test environment first
Command:
Exp 'sys/tesyspd @ MP_EX as sysdba 'owner = PLAS buffer = 10240000 FILE = "F: \ TEST-PLAS_EX-1114.DMP"
Execution Process:
C: \ Documents ents and Settings \ Administrator> exp 'sys/tesyspd @ MP_EX as sysdba 'owner = PLAS buffer = 10240000 FILE = "F: \ TEST-PLAS_EX-1114.DMP"



Export: Release 10.2.0.1.0-Production on Thursday November 13 20:25:26 2014



Copyright (c) 1982,200 5, Oracle. All rights reserved.








Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
E options
The ZHS16GBK and AL16UTF16 NCHAR character sets have been exported.




Will export the specified user...
. Exporting objects and operations in the pre-schema Process
. Exporting the external function library name of the user PLAS
. Export PUBLIC type Synonyms
. Exporting the special type Synonym
. Exporting the object type definition of user PLAS
.........
.. Export table S_MIDORG export 738 rows
.. Export table S_MIDUSERS export 2746 rows
... Export table S_ROOM_COMP export 3 rows
... Export table TOAD_PLAN_TABLE export 0 rows
.. Export table T_COREMAIL export 82 rows
. Exporting Synonym
. Exporting view...
. Exporting the Stored Procedure
. Exporting Operator
. The reference integrity constraints are being exported.
. Exporting trigger...
. Exporting index type...
. Exporting bitmap, functional index, and scalable Index
. Exporting table activity later
Exporting the Materialized View
Exporting snapshot logs
. Exporting Job Queue...
. Exporting refresh group and sub-group
. Exporting dimension...
. Exporting objects and operations in the post-schema Process
. Exporting statistics
The export is successfully terminated without a warning.



3.2. Import Data
C: \ Documents ents and Settings \ Administrator> IMP 'sys/tesyspd @ MP_TEST as sysdba 'buffer = 10240000 FILE = F: \ ORACLE_EXP \ MP_EX-PLAS-20141114.DMP FROMUSER = plas touser = PLAS
......
Constraints to be enabled...
Import is terminated successfully, but a warning is displayed.


C: \ Documents ents and Settings \ Administrator>



4. Error records during execution

4.1. linux export Error
[Oracle @ localhost ~] $ Exp "system/syspl @ powerdes" owner = PLAS buffer = 10240000 FILE =/tmp/PLAS_20141113.DMP




Export: Release 11.2.0.1.0-Production on Thu Nov 13 19:25:47 2014




Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.


EXP-00056: ORACLE error 28009 encountered
ORA-28009: connection as SYS shocould be as SYSDBA or SYSOPER
Username:
Password:


EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully
[Oracle @ localhost ~] $

The import error is caused by the linux/unix escape characters. You need to add the escape characters, such as \ ', and modify them to the following command:
Exp \ 'System/syspl as sysdba \ 'owner = PLAS buffer = 10240000 FILE =/tmp/PLAS_20141113.DMP




4.2. Import error in Windows


C: \ Documents and Settings \ Administrator> imp 'sys/tesyspd @ MP_TEST as sysdba 'buffer = 10240000 FILE = D: \ oracle \ imp \ PLAS_20141113.DMP FROMUSER = plas touser = PLAS


Import: Release 10.2.0.1.0-Production on Thursday November 13 20:53:02 2014

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
E options

IMP-00010: the exported file is not valid and the header verification fails.
IMP-00000: Import failed


Solution: see article http://www.2cto.com/database/201401/272732.html
Use the tool to change "11.20.00" in the dmp file header to "10.20.10.


4.3. IMP table Problems
......
IMP-00015: The following statement fails because the object already exists:
"Create package" sss "sss is"
""
"-- Author: ADMINISTRATOR"
"-- Created: 16:14:58"
"-- Purpose: sssss"
......
IMP reports that an existing table cannot be created. On the PLSQL interface, you can delete the objects of a user before IMP. For example, you can delete a table: select Users-> PLAS-> Objects-> Tables, select all Tables, and right-click the Drop operation to delete all Tables under the current user PLAS. Then run the IMP command.

Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1333180/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.