欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-17 10:06:08
...

一、实验环境 平台:VMware Server 1.0.6 Linux 2.6.18-164 el5 DB Version: Oracle 10g 10.2.0.1 Oracle SID: orcl 数据库运行在

一、实验环境

平台:VMware Server 1.0.6 Linux 2.6.18-164 el5

DB Version: Oracle 10g 10.2.0.1

Oracle 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

二、操作步骤:

源数据库数据文件位置:

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.dbf

sys@ORCL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf

sys@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.dbf

3、修改控制文件位置

[oracle@ora10gserv /]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011

Copyright (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.ora

orcl.__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数据库数据文件迁移到其它目录