Oracle11g table + perfect data migration to 10g solution
Oracle 11g table + perfect data migration to 10g solution
1. Use imp/exp to perfectly migrate 10 GB (Table + data) to 11 GB. Everyone on Earth knows.
2, 11g (Table + Data) Perfect migration to 10g, the following solution:
1. Use the expdp command to back up data on the 11g Server
Expdp userid = 'szairpay/szairpay @ orcl 'schemas = szairpay directory = DATA_PUMP_DIR dumpfile = sz11g. dmp logfile = sz11g. log version = 10.2.0.3.0'
Szairpay/szairpay @ orcl
Account/password @ database instance
Schemas = szairpay can be omitted
Directory = data_pump_dir: directory of the generated file, which is usually generated in admin \ orcl \ dpdump.
Dumpfile = Name of the file generated by sz11g. dmp
Logfile = sz11g. log,
Version = 10.2.0.3.0 corresponds to the 10 Gb Oracle version to be placed
The values on the right of the preceding parameters can be changed according to actual conditions.
2. Use the impdp command to restore data on a 10 Gb Server
Step: 1. Create a database 2. Create a tablespace 3. Create a user and authorize 4. Copy dag. dmp to the 10 Gb dpdump directory 5. Import impdp to the database
1. Create a database: directly create a database (instance) in database configuration assistant ).
2. create a tablespace: create tablespace szairpay datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ szairpay. dbf 'size 400 m autoextend on next 20 m online; note that the tablespace name must be consistent with the tablespace name in 11g.
3. User Creation:
Create uszairpay oa identified by szairpay; note that this user name must be consistent with the user name in 11g
Authorization:
Alter user szairpay default tablespace szairpay quota unlimited on szairpay; // you can understand it at a glance.
Grant create session to szairpay;
Grant connect to szairpay;
Grant connect, resource, dba to szairpay;
Grant create table to szairpay;
Grant create view to szairpay;
Grant create trigger to szairpay;
Grant select any table to szairpay;
Grant create sequence to szairpay;
Grant create procedure to szairpay;
Grant create role to szairpay;
Grant create type to szairpay;
Grant grant any privilege to szairpay;
4. Import: first, 10 Gb of data is installed in the D:/root directory on the test machine. the dmp file is copied to the E: \ SETUP \ Oracle10g \ zhumulu \ admin \ orcl \ udump directory.
The command for creating a directory is as follows:
Sqlplus sys/sys @ daggis
SQL> create directory dump_dir as 'e: \ SETUP \ Oracle10g \ zhumulu \ admin \ orcl \ udump ';
5. Import Data
Impdp userid = 'szairpay/szairpay @ orcl as sysdba 'schemas = szairpay directory = DATA_PUMP_DIR dumpfile = sz11g. dmp logfile = sz11g. log version = 10.2.0.3.0
So far, all the operations have been completed, and the message for a long time has finally been displayed: The operation is completed!
Finally, we have a command to check the total number of users of the original object. You can perform verification as needed. Run the command once on the machine that exports the database and once on the machine that imports the database to see if the result is the same:
SQL> select count (*) from dba_objects where owner in ('hangou', 'up', 'qishun ');
Well, the above is all my operations. If you have a better way to complete it, please remember to share it! Everyone learns from each other and makes progress together!