“This is the 15th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

11G and before

It is divided into two ways of non-stop and shutdown:

One, non-stop movement of data files

Complete steps:

1. Confirm that the archive mode is enabled

2. Make the data file offline

3, physical layer move data file (rename)

Logical layer rename data file path and name

5. Recover Recover data files

6. Online data files

-- Enable the archive mode
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /archivelog
Oldest online log sequence     1
Current log sequence           2
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

-- Make the data file offline
SQL> /

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /oradata/orcl11g/system01.dbf                                SYSTEM
         2 /oradata/orcl11g/sysaux01.dbf                                ONLINE
         3 /oradata/orcl11g/undotbs01.dbf                               ONLINE
         4 /oradata/orcl11g/users01.dbf                                 ONLINE
         5 /oradata/orcl11g/example01.dbf                               ONLINE
         6 /oradata/orcl11g/test01.dbf                                  ONLINE
         7 /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf           ONLINE

7 rows selected.

SQL> alter database datafile 7 offline;

Database altered.

Physical layer to move data files
SQL> !mv /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf /oradata/orcl11g/test02.dbf

SQL> !ls /oradata/orcl11g/test02.dbf
/oradata/orcl11g/test02.dbf

Logical layer rename data files
SQL> alter database rename file '/oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf' to '/oradata/orcl11g/test02.dbf';

Database altered.

Restore data files
SQL> recover datafile 7;
Media recovery complete.

--online Data files
SQL> alter database datafile 7 online;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /oradata/orcl11g/system01.dbf                                SYSTEM
         2 /oradata/orcl11g/sysaux01.dbf                                ONLINE
         3 /oradata/orcl11g/undotbs01.dbf                               ONLINE
         4 /oradata/orcl11g/users01.dbf                                 ONLINE
         5 /oradata/orcl11g/example01.dbf                               ONLINE
         6 /oradata/orcl11g/test01.dbf                                  ONLINE
         7 /oradata/orcl11g/test02.dbf                                  ONLINE

7 rows selected.
Copy the code

Two, stop the movement of data files

Complete steps:

1. Close the database

2, physical layer move data file (rename)

3. Start the database to mount

Logical layer rename data file path and name

5. Start the database

Create a TEST tablespace under /oradata/ORCL11G/ and move it to /oradata/ORCL11G/
SQL> create tablespace TEST;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf

Try moving data files online
SQL> alter database rename file '/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf' to '/oradata/orcl11g/test01.dbf';
alter database rename file '/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf' to '/oradata/orcl11g/test01.dbf'
*
ERROR at line 1:
ORA- 01511.: error in renaming log/data files
ORA- 01121.: cannot rename database file 6 - file is in use or recovery
ORA- 01110.: data file 6: '/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf'

- an error ORA - 01121
[oracle@orcl11g:/home/oracle]$ oerr ORA 01121
01121.00000, "cannot rename database file %s - file is in use or recovery"
// *Cause:  Attempted to use ALTER DATABASE RENAME to rename a
//          datafile that is online in an open instance or is being recovered.
// *Action: Close database in all instances and end all recovery sessions.
Copy the code

You cannot move data files online and need to close the database.

Move and rename data files at the operating system level
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll
total 102408
-rw-r----- 1 oracle oinstall 104865792 Apr 15 20:55 o1_mf_test_j7jfm30c_.dbf
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/
control01.ctl  example01.dbf  redo01.log     redo02.log     redo03.log     sysaux01.dbf   system01.dbf   temp01.dbf     undotbs01.dbf  users01.dbf    
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/test01.dbf
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll /oradata/orcl11g/test01.dbf 

-- Start database to mount
SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
Database mounted.

--rename Specifies the name of a data file
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf

6 rows selected.

SQL> alter database rename file '/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf' to '/oradata/orcl11g/test01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/orcl11g/test01.dbf

6 rows selected.

-- Open database
SQL> alter database open;

Database altered.
Copy the code

12C and later

Support online movement of data files:

Online Move Datafile in Oracle Database 12C Release 1

The syntax is as follows:

ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]
Copy the code

Blog.csdn.net/m0_50546016…