Batch migration of Oracle data files, log files and Control Files

In some cases, you need to relocate multiple Oracle data files and log files or migrate them to new partitions or locations, such as insufficient disk space or special requirements. In this case, you can migrate multiple data files or log files in batches. Of course, Backup recovery is also one of the methods. This document describes how to migrate data files and log files in batches. You can migrate the entire database to a new location or rename the database as needed.

1. Environment and requirements
Robin @ SZDB: ~> Cat/etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64)-Kernel \ r (\ l ).

Robin @ SZDB: ~> Sqlplus-v

SQL * Plus: Release 10.2.0.3.0-Production

The following migration mainly involves migrating all the files in the database/u02/database/SYBO2SZ to a new directory/u02/database/SY5221BK.
Source Path: All data files, log files, and control files of the database SYBO2SZ are located in the corresponding subdirectories under SYBO2SZ.
New Path: All data files, log files, and control files of the database SYBO2SZ are moved to the corresponding subdirectory of SY5221BK.

2. Current Database File Location (from data dictionary)
Sys @ SYBO2SZ> @ dba_files_all_2. SQL

Tablespace Name/File Class Filename File Size Auto
-------------------------------------------------------------------------------------------------------
GOEX_ACCOUNT_IDX/u02/database/SYBO2SZ/oradata/SYBO2SZ_account_idx.dbf 16,777,216 YES
GOEX_ACCOUNT_TBL/u02/database/SYBO2SZ/oradata/SYBO2SZ_account_tbl.dbf 25,165,824 YES
GOEX_ARCHIVE_IDX/u02/database/SYBO2SZ/oradata/SYBO2SZ_archive_idx.dbf 20,971,520 YES
--..................................
SOE/u02/database/SYBO2SZ/oradata/soe. dbf 934,043,648 YES
SOEINDEX/u02/database/SYBO2SZ/oradata/soeindex. dbf 713,031,680 YES
SYSAUX/u02/database/SYBO2SZ/oradata/sysauxsybo2sz. dbf 325,058,560 YES
SYSTEM/u02/database/SYBO2SZ/oradata/sysSYBO2SZ. dbf 524,288,000 YES
TBST/u02/database/SYBO2SZ/oradata/tbst. dbf 10,485,760 YES
TEMP/u02/database/SYBO2SZ/temp/tempSYBO2SZ. dbf 432,013,312 YES
UNDOTBS1/u02/database/SYBO2SZ/undo/undotbssybo2sz. dbf 429,916,160 YES
UNDOTBS2/u02/database/SYBO2SZ/undo/undotbsSYBO2SZ2. dbf 314,572,800 YES
[Online redo log]/u02/database/SYBO2SZ/redolog/log3aSYBO2SZ. log 20,971,520
[Online redo log]/u02/database/SYBO2SZ/redolog/log3bSYBO2SZ. log 20,971,520
[Online redo log]/u02/database/SYBO2SZ/redolog/log4aSYBO2SZ. log 20,971,520
[Online redo log]/u02/database/SYBO2SZ/redolog/log4bSYBO2SZ. log 20,971,520
---------------
Sum 5,107,376,128

41 rows selected.

3. Create a directory
Oracle @ SZDB:/u02/database/SYBO2SZ> more mkdir_SY5221BK.sh
#! /Bin/sh
Rm-rf/u02/database/SY5221BK/archive
Rm-rf/u02/database/SY5221BK/backup
Rm-rf/u02/database/SY5221BK/bdump
Rm-rf/u02/database/SY5221BK/cdump
Rm-rf/u02/database/SY5221BK/udump
Rm-rf/u02/database/SY5221BK/controlf
Rm-rf/u02/database/SY5221BK/oradata
Rm-rf/u02/database/SY5221BK/redolog
Rm-rf/u02/database/SY5221BK/undo
Rm-rf/u02/database/SY5221BK/temp
Rm-rf/u02/database/SY5221BK/ref_data
Rm-rf/u02/database/SY5221BK/BNR
Rm-rf/u02/database/SY5221BK/BNR/full
Rm-rf/u02/database/SY5221BK/BNR/dump
Rm-rf/u02/database/SY5221BK/dbcreatelogs

Mkdir-p/u02/database/SY5221BK/flash_recovery_area
Mkdir-p/u02/database/SY5221BK
Mkdir-p/u02/database/SY5221BK/archive
Mkdir-p/u02/database/SY5221BK/backup
Mkdir-p/u02/database/SY5221BK/bdump
Mkdir-p/u02/database/SY5221BK/cdump
Mkdir-p/u02/database/SY5221BK/udump
Mkdir-p/u02/database/SY5221BK/controlf
Mkdir-p/u02/database/SY5221BK/oradata
Mkdir-p/u02/database/SY5221BK/redolog
Mkdir-p/u02/database/SY5221BK/undo
Mkdir-p/u02/database/SY5221BK/temp
Mkdir-p/u02/database/SY5221BK/ref_data
Mkdir-p/u02/database/SY5221BK/BNR
Mkdir-p/u02/database/SY5221BK/BNR/full
Mkdir-p/u02/database/SY5221BK/BNR/dump
Mkdir-p/u02/database/SY5221BK/dbcreatelogs

Oracle @ SZDB:/u02/database/SYBO2SZ>./mkdir_SY5221BK.sh

1 2 3 Next Page