如何将Oracle数据库数据文件迁移到其它目录

一、实验环境平台：VMware Server 1.0.6 Linux 2.6.18-164 el5DB Version: Oracle 10g 10.2.0.1Oracle SID: orcl数据库运行在归档模式sys@ORCL> select log_mode from v$database;LOG_MODE
------------
ARCHIVELOG或者：sys@ORCL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch_orcl
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence           30 二、操作步骤：源数据库数据文件位置：/u01/oradata/orcl
实验中想把数据文件迁移到的位置：/u01/oradata/orcl_test 源数据库数据文件位置：sys@ORCL> select file_name from dba_data_files;FILE_NAME
----------------------------------------
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbfsys@ORCL> select file_name from dba_temp_files;FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL> col member for a30
sys@ORCL> select member from v$logfile ;MEMBER
------------------------------
/u01/oradata/orcl/redo01.log
/u01/oradata/orcl/redo02.log
/u01/oradata/orcl/redo03.log
需要移动所有的数据文件，采用alter database 方法1、关闭数据库 sys@ORCL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.2、移动数据文件到/u01/oradata/orcl_test目录[oracle@ora10gserv orcl]$ pwd
/u01/oradata/orcl
[oracle@ora10gserv orcl]$ mv * /u01/oradata/orcl_test/
[oracle@ora10gserv orcl]$ ls
[oracle@ora10gserv orcl]$ cd /u01/oradata/orcl_test/
[oracle@ora10gserv orcl_test]$ ls
control01.ctl control03.ctl redo02.log sysaux01.dbf temp01.dbf     users01.dbf
control02.ctl redo01.log     redo03.log system01.dbf undotbs01.dbf3、修改控制文件位置[oracle@ora10gserv /]$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011Copyright （c） 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.Total System Global Area 465567744 bytes
Fixed Size                  1220024 bytes
Variable Size             146801224 bytes
Database Buffers          314572800 bytes
Redo Buffers                2973696 bytes
SQL> create pfile="/u01/initorcl.ora" from spfile;File created.SQL> host vi /u01/initorcl.oraorcl.__db_cache_size=314572800
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=0
*.audit_file_dest="/dba/admin/orcl/adump"
*.background_dump_dest="/dba/admin/orcl/bdump"
*.compatible="10.2.0.1.0"
*.control_file_record_keep_time=14
*.control_files="/u01/oradata/orcl_test/control01.ctl","/u01/oradata/orcl_test/control02.ctl","/u01/oradata/orcl_test/control03.ctl"
*.core_dump_dest="/dba/admin/orcl/cdump"
*.db_block_size=8192
*.db_domain="lsf.com.cn"
*.db_file_multiblock_read_count=16
*.db_name="orcl"
*.db_recovery_file_dest="/dba/flash_recovery_area"
*.db_recovery_file_dest_size=2147483648
*.dispatchers="（PROTOCOL=TCP）（SERVICE=orclXDB）"
*.job_queue_processes=10
*.log_archive_dest_1="location=/u01/arch_orcl"
*.open_cursors=300
*.pga_aggregate_target=154140672
*.processes=1000
*.remote_login_passwordfile="EXCLUSIVE"
*.sessions=2000
*.sga_target=463470592
*.undo_management="AUTO"
*.undo_retention=10800
*.undo_tablespace="UNDOTBS1"
*.user_dump_dest="/dba/admin/orcl/udump"SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile="/u01/initorcl.ora";File created.

Oracle 创建索引的基本规则总结Oracle all_parameters view 创建sql相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）