Oralce数据库数据迁移到另一个数据

步骤一：

1，安装操作系统，配置环境。

步骤二：

1，配置oralce安装环境

2，图形化安装oralce

3，oralce用户配置环境变量

# cat .bash_profile # User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport ORACLE_SID=petra2export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib:/usr/lib64:/usr/local/lib:/usr/local/lib64export PATH=/sbin:/usr/sbin:/bin:/usr/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH

步骤三：

1，源目标库的查询

oracle@db1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-APR- 20:41:54Copyright (c) 1991, , Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date07-AUG- 03:18:31Uptime259 days 17 hr. 23 min. 23 secTrace LeveloffSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/app/11.2.0/grid/network/admin/listener.oraListener Log File /opt/app/grid/diag/tnslsnr/db1/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.101.101)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.101.111)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "petra" has 1 instance(s).Instance "petra1", status READY, has 1 handler(s) for this service...Service "petraXDB" has 1 instance(s).Instance "petra1", status READY, has 1 handler(s) for this service...Service "xa12345db" has 1 instance(s).Instance "petra1", status READY, has 1 handler(s) for this service...The command completed successfullySQL> show parameter retention;NAMETYPE VALUE------------------------------------ ----------- ------------------------------db_flashback_retention_targetinteger 1440undo_retentioninteger 21600SQL> alter system set undo_retention=102400;System altered.

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='PETRA';USERNAME DEFAULT_TABLESPACE------------------------------ ------------------------------TEMPORARY_TABLESPACE------------------------------PETRA PETRATEMP

2，导入前库中配置：

Import> kill_jobAre you sure you wish to stop this job ([yes]/no): yes[oracle@test_oralce ~]$ sqlplus / as sysdbaSQL> drop user petra cascade;User dropped.SQL> create user petra identified by Oracle123 default tablespace petra;User created.SQL> grant connect,resource to petra;Grant succeeded.SQL> alter user petra quota unlimited on petra;User altered.SQL> exit

SQL> select file_name from dba_data_files;FILE_NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/petra2/users01.dbf/u01/app/oracle/oradata/petra2/undotbs01.dbf/u01/app/oracle/oradata/petra2/sysaux01.dbf/u01/app/oracle/oradata/petra2/system01.dbf/home/oracle/oradata/petra01.dbf/home/oracle/oradata/petra02.dbf6 rows selected.SQL> alter tablespace petra add datafile '/home/oracle/oradata/petra03.dbf' size 30g,'/home/oracle/oradata/petra04.dbf' size 30g;alter tablespace petra add datafile '/home/oracle/oradata/petra03.dbf' size 30g,'/home/oracle/oradata/petra04.dbf' size 30g*ERROR at line 1:ORA-01119: error in creating database file '/home/oracle/oradata/petra03.dbf'ORA-27038: created file already existsAdditional information: 1SQL> alter tablespace petra add datafile '/home/oracle/oradata/petra04.dbf' size 30g;Tablespace altered.SQL> alter tablespace petra add datafile '/home/oracle/oradata/petra03.dbf' size 30g,'/home/oracle/oradata05.dbf' size 30g;Tablespace altered.SQL> alter tablespace petra add datafile '/home/oracle/oradata/petra06.dbf' size 30g,'/home/oracle/oradata/petra07.dbf' size 30g,'/home/oracle/oradata/petra07.dbf' size 30g,'/home/oracle/oradata/petra08.dbf' size 30g,'/home/oracle/oradata/petra09.dbf' size 30g;alter tablespace petra add datafile '/home/oracle/oradata/petra06.dbf' size 30g,'/home/oracle/oradata/petra07.dbf' size 30g,'/home/oracle/oradata/petra07.dbf' size 30g,'/home/oracle/oradata/petra08.dbf' size 30g,'/home/oracle/oradata/petra09.dbf' size 30g

3，数据导入的操作

SQL> startuo //启动数据库

]# impdp \'/ as sysdba \' directory=orahome network_link=lkdb101 schemas=xian12345 remap_schema=xian12345:petra remap_tablespace='(petra:petra,suntek:petra,new12345:petra,xian12345:petra)' parallel=6 logfile=impdp.log cluster=no

SQL> select * from dba_datapump_jobs;[oracle@test_oralce oradata]$ impdp \'/ as sysdba \' attach=SYS_IMPORT_schema_01 //导索引Import> status[oracle@test_oralce oradata]$ impdp \'/ as sysdba \' attach=SYS_IMPORT_schema_01

检查监听：

[oracle@test_oralce ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-APR- 12:10:09Copyright (c) 1991, , Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test_oralce)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date23-APR- 20:20:08Uptime0 days 15 hr. 50 min. 0 secTrace LeveloffSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/test_oralce/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test_oralce)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "petra2" has 1 instance(s).Instance "petra2", status READY, has 1 handler(s) for this service...Service "petra2XDB" has 1 instance(s).Instance "petra2", status READY, has 1 handler(s) for this service...The command completed successfully[oracle@test_oralce ~]$ cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

[root@test_oralce ~]# systemctl stop firewalld[root@test_oralce ~]# getenforceEnforcing[root@test_oralce ~]# setenforce 0

[oracle@test_oralce ~]$ cp oradata05.dbf ./oradata/[oracle@test_oralce ~]$ cd oradata/[oracle@test_oralce oradata]$ mv oradata05.dbf petra05.dbf[oracle@test_oralce oradata]$ llSQL> alter database rename file '/home/oracle/oradata05.dbf' to '/home/oracle/oradata/petra05.dbf';Database altered.SQL> alter database open;Database altered.[oracle@test_oralce ~]$ rm -f oradata05.dbf