當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2025/4/16 数据库 38 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oralce数据库数据迁移到另一个数据小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

步驟一：

1，安裝操作系統，配置環境。

步驟二：

1，配置oralce安裝環境

2，圖形化安裝oralce

3，oralce用戶配置環境變量

# cat .bash_profile # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/binexport PATH export ORACLE_SID=petra2 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib:/usr/lib64:/usr/local/lib:/usr/local/lib64 export 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-2021 20:41:54Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 07-AUG-2020 03:18:31 Uptime 259 days 17 hr. 23 min. 23 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/grid/diag/tnslsnr/db1/listener/alert/log.xml Listening 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;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 undo_retention integer 21600 SQL> 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 PETRA TEMP

2，導入前庫中配置：

Import> kill_job Are 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 exists Additional 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

檢查監聽：

[oracle@test_oralce ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-APR-2021 12:10:09Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test_oralce)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 23-APR-2021 20:20:08 Uptime 0 days 15 hr. 50 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/test_oralce/listener/alert/log.xml Listening 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/hosts 127.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 ~]# getenforce Enforcing [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

總結

以上是生活随笔為你收集整理的Oralce数据库数据迁移到另一个数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： python脚本备份网络交换的命令
下一篇： oracle用户权限的基本查询