Thủ tục đổi tên CONTROLFILE, DATAFILES sang đường dẫn mới

Thủ tục đổi tên CONTROLFILE, DATAFILES sang đường dẫn mới

Các bước thực hiện:
1. Tạo đường dẫn mới và sao chép file cũ sang đường dẫn mới
2. Cập nhật thông tin trong parameter để hệ thống hiểu

Chi tiết thủ tục như sau (các bạn nên copy đoạn code sau và thực hiện theo hướng dẫn) lưu ý thay đổi thông tin theo tham số thiết bị, ứng ụng hiện tại của bạn hiện có:

-- Kiểm tra thông tin datafile hệ thống hiện tại
SQL> select file_id, name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytcrx4_.dbf
/u01/app/oracle/oradata/ORADB/datafile/o1_mf_sysaux_llytdw3z_.dbf
/u01/app/oracle/oradata/ORADB/datafile/o1_mf_undotbs1_llytfobw_.dbf
/u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytssq5_.dbf
.....

-- Tạo đương dẫn mới chứa datafile:
[oracle@sv1 ~]$ mkdir -p /u01/app/oracle/oradata/DEV/datafile

-- Copy tất cả datafile sang đường dẫn mới
-- Tạo script copy cho nhanh, vì DATABASE của chúng ta có cả pdb nên có thể phải xoá mấy thưc mục con và đưa tất cả về đường dẫn gốc
set lines 999 pages 999
select 'cp ' || name || ' ' || REPLACE(name,'ORADB','DEV') from v$datafile;

-- Kết quả:
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytcrx4_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llytcrx4_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_sysaux_llytdw3z_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llytdw3z_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_undotbs1_llytfobw_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llytfobw_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytssq5_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llytssq5_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_sysaux_llytssq8_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llytssq8_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_users_llytfpj1_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llytfpj1_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_undotbs1_llytssq9_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llytssq9_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_system_llyvk1fg_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvk1fg_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_sysaux_llyvk1fr_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvk1fr_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_undotbs1_llyvk1fs_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvk1fs_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_users_llyvkl6w_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvkl6w_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_system_llyvkmh7_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvkmh7_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_sysaux_llyvkmhd_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvkmhd_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_undotbs1_llyvkmhf_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvkmhf_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_users_llyvkx8j_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvkx8j_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_system_llyvky04_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvky04_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_sysaux_llyvky05_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvky05_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_undotbs1_llyvky05_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvky05_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_users_llyvl7kf_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvl7kf_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_system_llyvl8v4_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvl8v4_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_sysaux_llyvl8v9_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvl8v9_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_undotbs1_llyvl8v9_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvl8v9_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_users_llyvlm19_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvlm19_.dbf
cp /u01/app/oracle/oradata/ORADB/datafile/TBS_DUONG_DATAFILE_1000 /u01/app/oracle/oradata/DEV/datafile/TBS_DUONG_DATAFILE_1000
cp /u01/app/oracle/oradata/ORADB/TBS_DUONG_PDB1_0001.DBF /u01/app/oracle/oradata/DEV/datafile/TBS_DUONG_PDB1_0001.DBF

-- Tiếp theo là copy tempfile:
cp /u01/app/oracle/oradata/ORADB/datafile/o1_mf_temp_llyth97c_.tmp /u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyth97c_.tmp
cp /u01/app/oracle/oradata/ORADB/datafile/temp012023-10-18_12-21-17-309-PM.dbf /u01/app/oracle/oradata/DEV/datafile/temp012023-10-18_12-21-17-309-PM.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_temp_llyvk1fs_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvk1fs_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_temp_llyvkmhh_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvkmhh_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_temp_llyvky06_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvky06_.dbf
cp /u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_temp_llyvl8vb_.dbf /u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvl8vb_.dbf

-- Khởi động DB trong chế độ MOUNT
shut IMMEDIATE;
startup MOUNT;

-- Đổi tên file trên DB:
select 'ALTER DATABASE RENAME FILE ''' || name || ''' TO ''' || REPLACE(name,'ORADB','DEV') ||''';' from v$datafile;

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytcrx4_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llytcrx4_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_sysaux_llytdw3z_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llytdw3z_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_undotbs1_llytfobw_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llytfobw_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_system_llytssq5_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llytssq5_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_sysaux_llytssq8_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llytssq8_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_users_llytfpj1_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llytfpj1_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_undotbs1_llytssq9_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llytssq9_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_system_llyvk1fg_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvk1fg_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_sysaux_llyvk1fr_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvk1fr_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_undotbs1_llyvk1fs_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvk1fs_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_users_llyvkl6w_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvkl6w_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_system_llyvkmh7_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvkmh7_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_sysaux_llyvkmhd_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvkmhd_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_undotbs1_llyvkmhf_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvkmhf_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_users_llyvkx8j_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvkx8j_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_system_llyvky04_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvky04_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_sysaux_llyvky05_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvky05_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_undotbs1_llyvky05_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvky05_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_users_llyvl7kf_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvl7kf_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_system_llyvl8v4_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_llyvl8v4_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_sysaux_llyvl8v9_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_llyvl8v9_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_undotbs1_llyvl8v9_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_llyvl8v9_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_users_llyvlm19_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_llyvlm19_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/TBS_DUONG_DATAFILE_1000' TO '/u01/app/oracle/oradata/DEV/datafile/TBS_DUONG_DATAFILE_1000';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/TBS_DUONG_PDB1_0001.DBF' TO '/u01/app/oracle/oradata/DEV/datafile/TBS_DUONG_PDB1_0001.DBF';

-- Đổi tên tempfile
select 'ALTER DATABASE RENAME FILE ''' || name || ''' TO ''' || REPLACE(name,'ORADB','DEV') ||''';' from v$tempfile;

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/o1_mf_temp_llyth97c_.tmp' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyth97c_.tmp';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/datafile/temp012023-10-18_12-21-17-309-PM.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/temp012023-10-18_12-21-17-309-PM.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FA30B7A92BD1E065000000000001/datafile/o1_mf_temp_llyvk1fs_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvk1fs_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FB44EEFF2CB7E065000000000001/datafile/o1_mf_temp_llyvkmhh_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvkmhh_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FBE642612CCFE065000000000001/datafile/o1_mf_temp_llyvky06_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvky06_.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ORADB/07F7FC8BD3362CE0E065000000000001/datafile/o1_mf_temp_llyvl8vb_.dbf' TO '/u01/app/oracle/oradata/DEV/datafile/o1_mf_temp_llyvl8vb_.dbf';

-- Sau khi đổi thành công => open DATABASE
alter database open;

-- Nếu hệ thống yêu cầu recover thì tiến hành recover datafile theo hướng dẫn là xong
RMAN> recover datafile 1;

-- Done !!!

Chúc các bạn thành công !

Đừng quên để lại câu hỏi nếu chưa thực hiện thành công (mục liên hệ), chúng tôi sẽ hỗ trợ bạn.