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
O
R
A
C
L
E
H
O
M
E
/
d
b
s
[
o
r
a
c
l
e
@
w
e
b
l
o
g
d
b
s
]
ORACLE_HOME/dbs [oracle@weblog dbs]
ORACLEHOME/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.
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
