Migration of Oracle databases (from one server to another server, from Oracle 10g to Oracle 11g)

This process, it is quite difficult ....

First, I used the data transfer in the Navicat in the migration, although the entire database migration is not successful, but the transfer of the specified object is still transferred successfully. So let's just record it.

1. Connect the database to a good condition. Right-click on the specified database and select "Data Transfer".

2. In the pop-up box, select the source and destination libraries and the mode and the objects you want to transfer, as shown in

3. Click Start to transfer

Second, in the use of Navicat can only transfer objects, there is no way to transfer mode. But if you're migrating an entire database, it's definitely not just about migrating objects. So I chose EXPDP and IMPDP to import the exported method to migrate.

1. Export from source database (I am remote connection database exported, native Windows, remote Linux)

EXPDP userid= ' Sys/[email protected]:1521/orcl as Sysdba ' Directory=data_pump_dir dumpfile=234backup.dmp logfile= 235back.log version=11.2.0.1.0 full=y

Let me give you a detailed description of the parameters:

(1) The userid is mainly to get the connection of the database

sys=> User name;oraclepwd=> password;210.77.71.23=> remote server IP address;1521=> port number;orcl=> database name; as sysdba=> if the SYS user is connected, Need to write this, if it is ordinary users will not write. (Note: Under the DOS command under Windows, add as SYSDBA can operate normally, but if you use Xshell remote connection to the server to do the export, even if the SYS user does not add as SYSDBA, the runtime will error. If not added, the runtime will prompt you to enter your username and password.

(2) Directory=data_pump_dir is the directory where the exported files are stored, you do not have to dwell on where to store them, because after the export is successful, the system will tell you the path of the export.

(3) DumpFile is the name of the exported file, this needs your own designation.

(4) LogFile is the log that you run during the export process and is recorded in this file. If there is an error, you can find the corresponding bug in this file.

(5) Version: The version here is not the version of the source database, but the target database

(6) Full=y is the meaning of all exports. Includes users, table spaces, tables, and so on. If you want to export the object in the specified mode, you can use the schemas parameter to specify it. (Note: The schemas parameter and the full parameter cannot be used simultaneously)

If you export the specified table, view, etc., EXPDP also has the corresponding parameters, you can Baidu a bit, here do not do a detailed introduction.

2. Import the target database (relative export, import becomes very troublesome.) I am directly using Xshell telnet to the server to do the import)

Note:

(1) When doing import, it is recommended that you try to create a new instance to import. If you import in the original instance, if you have the same table name or schema, it will first be deleted in the re-creation. However, there are methods to prevent the import from being deleted, but if you migrate the entire database, it can be confusing, unless you know the schema and objects of the source and target databases.

(2) Before running the command below, you will now copy your exported DMP file to the directory directory of the target database. A bit of a tragedy is, it is possible that you do not know where this directory, you can do a small export in the target database, at the end of the directory to see this, the DMP file copy to this directory.

IMPDP userid= ' sys/[email protected]:1521/db11 ' Directory=data_pump_dir dumpfile=234backup.dmp logfile=235back.log

Remap_datafile= '/opt/oradata/db10/sdbp.dbf ': ' casnw/app/oradata/db11/sdbp.dbf ', '/opt/oradata/db10/ SDBPTEMP01.DBF ': ' casnw/app/oradata/db11/sdbptemp01.dbf ', '/opt/oradata/db10/casnw01.dbf ': ' casnw/app/oradata/ Db11/casnw01.dbf ', '/opt/oradata/db10/casnwtemp01.dbf ': ' casnw/app/oradata/db11/casnwtemp01.dbf ',

version=11.2.0.1.0 full=y Table_exists_action=replace

The preceding several parameters are not introduced, and the same meaning in EXPDP. Let me introduce a few new parameters:

Remap_datafile: Because of this parameter I was also very frustrating. This is the process of viewing the import through the log. The table space is established, the corresponding user is established through the table space, the table is built, and the data is transferred. Transport views, functions and stored procedures, sequences, and so on. However, the table space in the process of the error, saying that the path of the specified data file could not be found. Because the path to the space data file in the source database is different from the destination database. This is the use of this parameter, use this parameter to specify the target database to establish the path of the tablespace data file, to replace the source database path. This allows you to create a table space.

Table_exists_action: This parameter has a total of four values, which I introduce here. (1) Skip: Is if the object already exists, then skips and processes the next object, (2) Append is to add data for the table, (3) Truncate is truncating the table, and then add new data for it; (4) Replace: If you encounter a table that already exists, delete it, and then create one. and add data.

The above import method, in the import stored procedures will be error, I looked on the internet, said to be before the export or after the import recompile. The stored procedure did not import successfully, so I finally took advantage of Navicat to import the stored procedure successfully.

Note: (1) To ensure that two databases have the same character set, if the difference will be an error. I checked the next two database character sets are the same, so I'm not sure what is wrong with the specific report. To view the database character set:

SELECT * from V$nls_parameters WHERE PARAMETER in (' Nls_characterset ', ' nls_nchar_characterset ');

(2) because the entire database is migrated in the past, the SYS user password of your target database becomes the password of the source database sys user, and you can change it later

Sql>sqlplus/nolog

Sql>connect Sys/as SYSDBA

Sql>alter User sys identified by admin;

Migration of Oracle databases (from one server to another server, from Oracle 10g to Oracle 11g)