Migration and specification of oracle data files in windows

 

In the R & D center, a database running oracle and sqlserver on Windows is an old machine with three hard disks attached, however, I recently received a request from my colleague saying that the disk where the oracle database data file is located is almost full and asked how to handle the change. Previously, my colleague has consulted another colleague from this department, my colleagues in this department actually replied that they couldn't handle the problem. After processing, the database will become unusable! I had no choice but to ask my R & D colleague, but I replied to her that I could handle the problem. So I ran to check the machine disk information and found that each disk was almost exhausted. I had to purchase a new hard disk to handle the problem, in desperation, R & D colleagues had to delete one large historical document file, migrate one of the data files first, and request to purchase a new hard disk.

Today, the new hard disk arrived, mounted the hard disk and partitioned it, And then prepared to change the file storage location. I learned that there are three application users in the database, and the data file storage is chaotic, the naming rules are also not standardized, and the two points are processed in a unified manner, the data files are stored in the partition where the new hard disk is located, the user name is installed for standard storage, and the serial number is added after the file.

Here is the processing process!

Run the following script:

 

Spool f: \ mv_datafiles.txt

-- Set linesize 200;

Set pagesize 100;

Column username format a8;

Column dtbspace format a8;

Column dtpspace format a8;

Column command format a75;

Select * From v $ version;

Select. username,. default_tablespace dtbspace,. temporary_tablespace dtpspace from dba_users a where. username in ('user1', 'user2', 'user3') order by. username;

Break on username skip 1;

Break on offtbspace skip 1;

With Tbs

(Select a. Username,

A. Default_Tablespace,

B. File_Name,

B. File_Id,

B. Bytes/1024/1024 "SIZE-Mb"

From Dba_Users a, Dba_Data_Files B

Where a. Username In ('user1', 'user2', 'user3 ')

And a. Default_Tablespace = B. Tablespace_Name)

Select 'datafile total size: '| To_Char (Sum ("SIZE-Mb") | 'mb' command

From Tbs

Union All

Select '***** Run follow script use sqlplus ****'

From Dual

Union All

Select 'alter tablespace' | Default_Tablespace | 'offline ;'

From Tbs

Group By Default_Tablespace

Union All

Select '*** Run follow script use MS-DOS ****'

From Dual

Union All

Select 'echo % date :~ 0, 4%-% date :~ 5, 2%-% date :~ 8, 2% % time :~ 0, 2%: % time :~ 3,2%: % time :~ 6, 2%'

From Dual

Union All

Select 'opcopy' | File_Name | 'f: \ oradata \ '| Username |' \ '|

Username | Row_Number () Over (Partition By Username Order By File_Id) | '. dbf' cptbdatafile

From Tbs

Union All

Select '***** Run follow script use sqlplus ****'

From Dual

Union all

Select 'alter tablespace' | Default_Tablespace | 'rename datafile' | File_Name | ''' to ''f: \ oradata \ '| Username |' \ '|

Username | Row_Number () Over (Partition By Username Order By File_Id) | '. dbf'; 'renametbdatafile

From Tbs

Union All

Select 'echo % date :~ 0, 4%-% date :~ 5, 2%-% date :~ 8, 2% % time :~ 0, 2%: % time :~ 3,2%: % time :~ 6, 2%'

From Dual

Union All

Select '***** Run follow script use sqlplus ****'

From Dual

Union All

Select 'alter tablespace' | Default_Tablespace | 'Online ;'

From Tbs

Group By Default_Tablespace

Union All

Select '*** Run follow script use MS-DOS ****'

From Dual

Union All

Select 'Before you delete datafiles job recommend you check the tbs and datafiles is Work' From dual

Union All

Select 'del '| File_Name Deldatafile From Tbs;

Spool Off;

 

 

View offline files, execute scripts in the installation sequence, and check the table space and Data File status before the last OS delete operation:

 

Col defaultt_tablespace For a20;

Col tablespacestatus For a17;

Col defaultt_tablespace For a20;

Col file_name For a50;

Col datafilestatus For a15;

Break On username Skip 1;

Break On Default_Tablespace Skip 1;

Select a. Username,

A. Default_Tablespace, c. status tablespacestatus, B. file_name, B. status datafilestatus

From Dba_Users a, Dba_Data_Files B, Dba_Tablespaces c

Where a. Username In ('user1', 'user2', 'user3 ')

And a. Default_Tablespace = B. Tablespace_Name And B. tablespace_name = c. tablespace_name Order By username;

 

 

By the way, the speed of copying objects is more than GB, and the copy time is one and a half hours.

-The End-

 

By Gtlions