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

Đăng vào Tác giả Datalinks.vn 0
Share
Tweet
Share
Pin
0 Shares

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

create controlfile

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.

Xem thêm:

  1. Thay đổi đường dẫn database Postgresql PGDATA directory change trên Linux OS
  2. Moving and Renaming Online Data Files – Đổi tên datafile Oracle Database
  3. Managing Resumable Space Allocation – Quản trị cấp phát không gian lưu trữ liên tục trong Oracle Database
  4. Chuyển cơ sở dữ liệu Non-CDB sang PDB trong Oracle Multitenant – Migrate a Non-Container Database to a Pluggable Database (PDB)
  5. Bàn luận về việc tăng cường tính sẵn sàng availability cho Oracle database

Datalinks.vn

Hello các bạn, mình là Dương Nguyễn tác giả của blog này. Với gần 20 năm làm việc trong lĩnh vực CNTT, VT trong và ngoài nước, mong muốn được chia sẻ kiến thức và kinh nghiệm về database với các bạn đam mê và quan tâm đến lĩnh vực này. Đặc biệt là #OracleDatabase hoặc luyện thi #OCP database admin. Các bạn cần người đồng hành thì alo mình nhé. Call/Zalo: 0765 871 888. Thanks you !.....
0 0 đánh giá
Đánh giá bài viết
Theo dõi
Thông báo của
guest

0 Góp ý
Phản hồi nội tuyến
Xem tất cả bình luận