1.查询数据文件、redo文件、控制文件路径:

col name for a55
SQL> select name,file#,status from v$datafile;
NAME                                 FILE# STATUS
------------------------------------------------------- ---------- -------
/u02/oradata/XXXXXX/system01.dbf                 1 SYSTEM
/u02/oradata/XXXXXX/sysaux01.dbf                 2 ONLINE
/u02/oradata/XXXXXX/undotbs01.dbf                 3 ONLINE
/u02/oradata/XXXXXX/users01.dbf                 4 ONLINE
/u02/oradata/XXXXXX/bidwd01.dbf                 5 ONLINE
/u02/oradata/XXXXXX/bidwd02.dbf                 6 ONLINE
/u02/oradata/XXXXXX/bidwd03.dbf                 7 ONLINE
/u02/oradata/XXXXXX/bidwd04.dbf                 8 ONLINE
/u02/oradata/XXXXXX/bidwd05.dbf                 9 ONLINE
/u02/oradata/XXXXXX/bidwx01.dbf                10 ONLINE
/u02/oradata/XXXXXX/bi_odsd_01.dbf                11 ONLINE

NAME                                 FILE# STATUS
------------------------------------------------------- ---------- -------
/u02/oradata/XXXXXX/bi_odsd_02.dbf                12 ONLINE
/u02/oradata/XXXXXX/bi_odsd_03.dbf                13 ONLINE
/u02/oradata/XXXXXX/bi_odsd_04.dbf                14 ONLINE
/u02/oradata/XXXXXX/bi_odsd_05.dbf                15 ONLINE
/u02/oradata/XXXXXX/bi_odsx_01.dbf                16 ONLINE
/u02/oradata/XXXXXX/system02.dbf                17 SYSTEM
/u02/oradata/XXXXXX/undotbs102.dbf                18 ONLINE
/u02/oradata/XXXXXX/undotbs202.dbf                19 ONLINE
/u02/oradata/XXXXXX/sysaux02.dbf                20 ONLINE
/u02/oradata/XXXXXX/bi_DICTD_01.dbf                21 ONLINE
/u02/oradata/XXXXXX/bi_DICTX_01.dbf                22 ONLINE

NAME                                 FILE# STATUS
------------------------------------------------------- ---------- -------
/u02/oradata/XXXXXX/BIETLD01.dbf                23 ONLINE
/u02/oradata/XXXXXX/BIETLD02.dbf                24 ONLINE
/u02/oradata/XXXXXX/BIETLX01.dbf                25 ONLINE


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/XXXXXX/temp01.dbf
/u02/oradata/XXXXXX/temp02.dbf


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/XXXXXX/redo03.log
/u01/app/oracle/oradata/XXXXXX/redo02.log
/u01/app/oracle/oradata/XXXXXX/redo01.log



SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
/u01/app/oracle/oradata/XXXXXX/control01.ctl
/u01/app/oracle/fast_recovery_area/XXXXXX/control02.c
tl



SQL> show parameter control

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     /u01/app/oracle/oradata/RongLi
                         an/control01.ctl, /u01/app/ora
                         cle/fast_recovery_area/RongLia
                         n/control02.ctl
control_management_pack_access         string     DIAGNOSTIC+TUNING

2.变更控制文件路径:

alter system set control_files='/u170/data/control01.ctl','/u170/data/control02.ctl' scope=spfile;

3.变更控制文件、redo和数据文件路径:

shu immediate

cp /u01/app/oracle/oradata/XXXXXX/control01.ctl /u170/data/control01.ctl
cp /u01/app/oracle/oradata/XXXXXX/control01.ctl /u170/data/control02.ctl


cp /u02/oradata/XXXXXX/system01.dbf             /u170/data/system01.dbf            
cp /u02/oradata/XXXXXX/sysaux01.dbf             /u170/data/sysaux01.dbf            
cp /u02/oradata/XXXXXX/undotbs01.dbf            /u170/data/undotbs01.dbf            
cp /u02/oradata/XXXXXX/users01.dbf                /u170/data/users01.dbf                
cp /u02/oradata/XXXXXX/bidwd01.dbf                /u170/data/bidwd01.dbf                
cp /u02/oradata/XXXXXX/bidwd02.dbf                /u170/data/bidwd02.dbf                
cp /u02/oradata/XXXXXX/bidwd03.dbf                /u170/data/bidwd03.dbf                
cp /u02/oradata/XXXXXX/bidwd04.dbf                /u170/data/bidwd04.dbf                
cp /u02/oradata/XXXXXX/bidwd05.dbf                /u170/data/bidwd05.dbf                
cp /u02/oradata/XXXXXX/bidwx01.dbf                /u170/data/bidwx01.dbf                
cp /u02/oradata/XXXXXX/bi_odsd_01.dbf         /u170/data/bi_odsd_01.dbf        
cp /u02/oradata/XXXXXX/bi_odsd_02.dbf         /u170/data/bi_odsd_02.dbf        
cp /u02/oradata/XXXXXX/bi_odsd_03.dbf         /u170/data/bi_odsd_03.dbf        
cp /u02/oradata/XXXXXX/bi_odsd_04.dbf         /u170/data/bi_odsd_04.dbf        
cp /u02/oradata/XXXXXX/bi_odsd_05.dbf         /u170/data/bi_odsd_05.dbf        
cp /u02/oradata/XXXXXX/bi_odsx_01.dbf         /u170/data/bi_odsx_01.dbf        
cp /u02/oradata/XXXXXX/system02.dbf             /u170/data/system02.dbf            
cp /u02/oradata/XXXXXX/undotbs102.dbf         /u170/data/undotbs102.dbf        
cp /u02/oradata/XXXXXX/undotbs202.dbf         /u170/data/undotbs202.dbf        
cp /u02/oradata/XXXXXX/sysaux02.dbf             /u170/data/sysaux02.dbf            
cp /u02/oradata/XXXXXX/bi_DICTD_01.dbf        /u170/data/bi_DICTD_01.dbf        
cp /u02/oradata/XXXXXX/bi_DICTX_01.dbf        /u170/data/bi_DICTX_01.dbf        
cp /u02/oradata/XXXXXX/BIETLD01.dbf             /u170/data/BIETLD01.dbf            
cp /u02/oradata/XXXXXX/BIETLD02.dbf             /u170/data/BIETLD02.dbf            
cp /u02/oradata/XXXXXX/BIETLX01.dbf             /u170/data/BIETLX01.dbf    

cp /u02/oradata/XXXXXX/temp01.dbf       /u170/data/temp01.dbf  
cp /u02/oradata/XXXXXX/temp02.dbf       /u170/data/temp02.dbf  

cp /u01/app/oracle/oradata/XXXXXX/redo03.log    /u170/data/redo03.log
cp /u01/app/oracle/oradata/XXXXXX/redo02.log    /u170/data/redo02.log
cp /u01/app/oracle/oradata/XXXXXX/redo01.log    /u170/data/redo01.log





startup mount;


alter database rename file '/u02/oradata/XXXXXX/system01.dbf'             to '/u170/data/system01.dbf'    ;
alter database rename file '/u02/oradata/XXXXXX/sysaux01.dbf'             to '/u170/data/sysaux01.dbf';
alter database rename file '/u02/oradata/XXXXXX/undotbs01.dbf'            to '/u170/data/undotbs01.dbf'    ;
alter database rename file '/u02/oradata/XXXXXX/users01.dbf'                to '/u170/data/users01.dbf'    ;
alter database rename file '/u02/oradata/XXXXXX/bidwd01.dbf'                to '/u170/data/bidwd01.dbf'    ;
alter database rename file '/u02/oradata/XXXXXX/bidwd02.dbf'                to '/u170/data/bidwd02.dbf';
alter database rename file '/u02/oradata/XXXXXX/bidwd03.dbf'                to '/u170/data/bidwd03.dbf'    ;
alter database rename file '/u02/oradata/XXXXXX/bidwd04.dbf'                to '/u170/data/bidwd04.dbf';                
alter database rename file '/u02/oradata/XXXXXX/bidwd05.dbf'                to '/u170/data/bidwd05.dbf'    ;            
alter database rename file '/u02/oradata/XXXXXX/bidwx01.dbf'                to '/u170/data/bidwx01.dbf'        ;        
alter database rename file '/u02/oradata/XXXXXX/bi_odsd_01.dbf'         to '/u170/data/bi_odsd_01.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_odsd_02.dbf'         to '/u170/data/bi_odsd_02.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_odsd_03.dbf'         to '/u170/data/bi_odsd_03.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_odsd_04.dbf'         to '/u170/data/bi_odsd_04.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_odsd_05.dbf'         to '/u170/data/bi_odsd_05.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_odsx_01.dbf'         to '/u170/data/bi_odsx_01.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/system02.dbf'             to '/u170/data/system02.dbf'            ;
alter database rename file '/u02/oradata/XXXXXX/undotbs102.dbf'         to '/u170/data/undotbs102.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/undotbs202.dbf'         to '/u170/data/undotbs202.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/sysaux02.dbf'             to '/u170/data/sysaux02.dbf'            ;
alter database rename file '/u02/oradata/XXXXXX/bi_DICTD_01.dbf'        to '/u170/data/bi_DICTD_01.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/bi_DICTX_01.dbf'        to '/u170/data/bi_DICTX_01.dbf'        ;
alter database rename file '/u02/oradata/XXXXXX/BIETLD01.dbf'             to '/u170/data/BIETLD01.dbf'            ;
alter database rename file '/u02/oradata/XXXXXX/BIETLD02.dbf'             to '/u170/data/BIETLD02.dbf'            ;
alter database rename file '/u02/oradata/XXXXXX/BIETLX01.dbf'             to '/u170/data/BIETLX01.dbf'          ;

alter database rename file '/u02/oradata/XXXXXX/temp01.dbf'             to '/u170/data/temp01.dbf'            ;
alter database rename file '/u02/oradata/XXXXXX/temp02.dbf'             to '/u170/data/temp02.dbf'          ;

alter database rename file '/u01/app/oracle/oradata/XXXXXX/redo03.log'    to '/u170/data/redo03.log';
alter database rename file '/u01/app/oracle/oradata/XXXXXX/redo02.log'    to '/u170/data/redo02.log';
alter database rename file '/u01/app/oracle/oradata/XXXXXX/redo01.log'    to '/u170/data/redo01.log';

4.查询修改后路径

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u170/data/system01.dbf
/u170/data/sysaux01.dbf
/u170/data/undotbs01.dbf
/u170/data/users01.dbf
/u170/data/bidwd01.dbf
/u170/data/bidwd02.dbf
/u170/data/bidwd03.dbf
/u170/data/bidwd04.dbf
/u170/data/bidwd05.dbf
/u170/data/bidwx01.dbf
/u170/data/bi_odsd_01.dbf

NAME
--------------------------------------------------------------------------------
/u170/data/bi_odsd_02.dbf
/u170/data/bi_odsd_03.dbf
/u170/data/bi_odsd_04.dbf
/u170/data/bi_odsd_05.dbf
/u170/data/bi_odsx_01.dbf
/u170/data/system02.dbf
/u170/data/undotbs102.dbf
/u170/data/undotbs202.dbf
/u170/data/sysaux02.dbf
/u170/data/bi_DICTD_01.dbf
/u170/data/bi_DICTX_01.dbf

NAME
--------------------------------------------------------------------------------
/u170/data/BIETLD01.dbf
/u170/data/BIETLD02.dbf
/u170/data/BIETLX01.dbf

25 rows selected.

select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u170/data/redo03.log
/u170/data/redo02.log
/u170/data/redo01.log

select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u170/data/control01.ctl
/u170/data/control02.ctl

show parameter control

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     /u170/data/control01.ctl, /u17
                         0/data/control02.ctl
control_management_pack_access         string     DIAGNOSTIC+TUNING

select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u170/data/temp01.dbf
/u170/data/temp02.dbf
最后修改:2021 年 10 月 02 日
如果觉得我的文章对你有用,请随意赞赏