orcle 数据库控制文件、数据文件、临时文件转移

orcle 数据库控制文件、数据文件、临时文件目录更改

1.检查所有文件的绝对路径
查看数据文件、临时文件、日志文件、控制文件当前目录

SQL> select name from v$datafile;
/u01/app/oracle/oragata/ora11g/system01.dbf
/u01/app/oracle/oragata/ora11g/sysaux01.dbf
/u01/app/oracle/oragata/ora11g/undotbs01.dbf
/u01/app/oracle/oragata/ora11g/users01.dbf

SQL> select name from v$tempfile;
/u01/app/oracle/oragata/ora11g/temp01.dbf

SQL> select member from v$logfile;

/u01/app/oracle/oragata/ora11g/redo01.log
/u01/app/oracle/oragata/ora11g/redo02.log
/u01/app/oracle/oragata/ora11g/redo03.log

SQL> select name from v$controlfile;

/u01/app/oracle/oragata/ora11g/control01.ctl
/u01/app/oracle/oragata/ora11g/control02.ctl

查看参数文件
[oracle@weblog dbs]$ ll
total 20
-rw-rw---- 1 oracle oinstall 1544 Dec 17 14:42 hc_ora11g.dat
-rw-r–r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Dec 17 14:24 lkORA11G
-rw-r----- 1 oracle oinstall 1536 Dec 17 14:24 orapwora11g
-rw-r----- 1 oracle oinstall 2560 Dec 17 14:42 spfileora11g.ora

创建spfile

SQL> create pfile from spfile;

File created.

[oracle@weblog dbs]$ ll
total 24
-rw-rw---- 1 oracle oinstall 1544 Dec 17 14:42 hc_ora11g.dat
-rw-r–r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r-- 1 oracle oinstall 876 Dec 17 14:49 initora11g.ora
-rw-r----- 1 oracle oinstall 24 Dec 17 14:24 lkORA11G
-rw-r----- 1 oracle oinstall 1536 Dec 17 14:24 orapwora11g
-rw-r----- 1 oracle oinstall 2560 Dec 17 14:42 spfileora11g.ora

2.关闭实例

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3.创建新目录

[oracle@weblog dbs]$ cd /u01/app/oracle/
[oracle@weblog oracle]$ ll
total 0
drwxr-x— 3 oracle oinstall 28 Dec 17 14:24 admin/
drwxr-x— 6 oracle oinstall 80 Dec 17 14:42 cfgtoollogs/
drwxr-xr-x 2 oracle oinstall 10 Dec 17 14:14 checkpoints/
drwxrwxr-x 11 oracle oinstall 168 Dec 17 14:09 diag/
drwxr-xr-x 2 oracle oinstall 10 Dec 17 14:21 oradata/
drwxr-x— 3 oracle oinstall 28 Dec 17 14:24 oragata/
drwxrwxr-x 3 oracle oinstall 28 Dec 17 13:48 product/

4.修改控制文件

在pfile中更改控制文件地址

[oracle@weblog oracle]$ cd $ORACL{E}_{H}OME/dbs[oracle@weblogdbs]$ more initora11g.ora
ora11g.__db_cache_size=3456106496
ora11g.__java_pool_size=50331648
ora11g.__large_pool_size=67108864
ora11g.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=212097564672
ora11g.__sga_target=4294967296
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=687865856
ora11g.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/ora11g/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/u01/app/oracle/oragata/ora11g/control01.ctl’,’/u01/app/oracle/oragata/ora11g/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘ora11g’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ora11gXDB)’
*.open_cursors=300
*.pga_aggregate_target=212095467520
*.processes=2000
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sessions=2205
*.sga_target=4293918720
*.undo_tablespace=‘UNDOTBS1’

[oracle@weblog dbs]$ vi initora11g.ora
[oracle@weblog dbs]$ more initora11g.ora
ora11g.__db_cache_size=3456106496
ora11g.__java_pool_size=50331648
ora11g.__large_pool_size=67108864
ora11g.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=212097564672
ora11g.__sga_target=4294967296
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=687865856
ora11g.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/ora11g/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/u01/app/oracle/oradata/ora11g/control01.ctl’,’/u01/app/oracle/oradata/ora11g/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘ora11g’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ora11gXDB)’
*.open_cursors=300
*.pga_aggregate_target=212095467520
*.processes=2000
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sessions=2205
*.sga_target=4293918720
*.undo_tablespace=‘UNDOTBS1’

将数据文件、日志文件等移动到目标目录下
[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/control01.ctl /u01/app/oracle/oradata/ora11g/control01.ctl

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/control02.ctl /u01/app/oracle/oradata/ora11g/control02.ctl

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/system01.dbf /u01/app/oracle/oradata/ora11g/system01.dbf

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/sysaux01.dbf /u01/app/oracle/oradata/ora11g/sysaux01.dbf

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/undotbs01.dbf /u01/app/oracle/oradata/ora11g/undotbs01.dbf

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/users01.dbf /u01/app/oracle/oradata/ora11g/users01.dbf

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/temp01.dbf /u01/app/oracle/oradata/ora11g/temp01.dbf

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo01.log /u01/app/oracle/oradata/ora11g/redo01.log

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo02.log /u01/app/oracle/oradata/ora11g/redo02.log

[oracle@weblog oracle]$ cp /u01/app/oracle/oragata/ora11g/redo03.log /u01/app/oracle/oradata/ora11g/redo03.log

用修改过控制文件目录的参数文件启动数据库至mount
[oracle@weblog oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 17 14:55:21 2019

Copyright © 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile =’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11g.ora’;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1526727560 bytes
Database Buffers 2734686208 bytes
Redo Buffers 12107776 bytes

SQL> alter database mount;
alter database mount

mount数据库

SQL> alter database mount;

Database altered.

5.修改数据文件、日志文件、临时文件
rename数据文件、日志文件等

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/system01.dbf’ to ‘/u01/app/oracle/oradata/ora11g/system01.dbf’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/sysaux01.dbf’ to ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/undotbs01.dbf’ to ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/users01.dbf’ to ‘/u01/app/oracle/oradata/ora11g/users01.dbf’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/temp01.dbf’ to ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/redo01.log’ to ‘/u01/app/oracle/oradata/ora11g/redo01.log’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/redo02.log’ to ‘/u01/app/oracle/oradata/ora11g/redo02.log’;

Database altered.

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/redo03.log’ to ‘/u01/app/oracle/oradata/ora11g/redo03.log’;

Database altered.

控制文件已经卸载参数文件里了，不需要rename

SQL> alter database rename file ‘/u01/app/oracle/oragata/ora11g/control01.ctl’ to ‘/u01/app/oracle/oradata/ora11g/control01.ctl’;
alter database rename file ‘/u01/app/oracle/oragata/ora11g/control01.ctl’ to ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
“/u01/app/oracle/oragata/ora11g/control01.ctl”

6、打开数据库

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf

SQL> select name from v$tempfile;
/u01/app/oracle/oradata/ora11g/temp01.dbf

SQL> select member from v$logfile;

/u01/app/oracle/oradata/ora11g/redo01.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo03.log

SQL> select name from v$controlfile;

/u01/app/oracle/oradata/ora11g/control01.ctl
/u01/app/oracle/oradata/ora11g/control02.ctl

SQL> show parameter pfile

NAME TYPE VALUE

---

spfile string

7.更新spfile

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1526727560 bytes
Database Buffers 2734686208 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.