Shulou(Shulou.com)06/01 Report--

[abstract]

Before version 12c, data files could not be renamed and moved online. In many cases, you need to move the data file or rename it, so you have to stop the operation.

But to 12c, this operation becomes very simple, can be operated online, and has no impact on the application business, which greatly improves the high availability of the database. In fact, many new features of 12c are designed to improve the high availability of the database. Oracle pays more and more attention to the high availability of the database.

The online renaming and moving data files of 12c are described below.

[text]

1 rename the data file online

First, create a new tablespace called TBS_FILE_TO_MOVE, which contains only one data file.

[oracle@Server ~] $sqlplus / as sysdba

SQL*Plus: Release12.1.0.1.0 Production on Fri Dec 20 03:03:50 2013

Copyright (c) 1982 Jing 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 12cEnterprise Edition Release 12.1.0.1.0-64bit Production

With thePartitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL > CREATESMALLFILE TABLESPACE TBS_FILE_TO_MOVE DATAFILE'/ u01According to oradataAccording to NONCDB

SIZE 100M AUTOEXTENDON NEXT 100M LOGGING DEFAULT NOCOMPRESS

ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACEMANAGEMENT AUTO

Tablespace created.

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move.dbf ONLINE

SQL >! ls/u01/app/oracle/oradata/NONCDB/file_to_move*

/ u01/app/oracle/oradata/NONCDB/file_to_move.dbf

The data file "/ u01/app/oracle/oradata/NONCDB/file_to_move.dbf" of the newly created tablespace TBS_FILE_TO_MOVE was renamed online:

SQL > alter databasemove datafile'/ u01 to'/u01/app/oracle/oradata/NONCDB/file_to_move2.dbf'

Database altered.

If you try to rename the data file using the "alter database rename file" command provided in the previous version in this step, you will get an ORA-01121 error indicating that the specified data file cannot be renamed while using or replying.

After the renaming operation is complete, we find that the data file has been displayed as the new name (file_to_move2.dbf) in the control file and operating system directory, and its status is still ONLINE.

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf ONLINE

SQL >! ls / u01According to oradataAccord

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf

Second, use the KEEP keyword to move data files online

Use "alter database move datafile..." with the KEEP keyword Statement, rename the data file file_to_move.dbf to file_to_move1.dbf (simulate the online movement of the data file). If the KEEP keyword is specified in the operation, the original data file is retained in the original location, and a copy of the new data file is created in the new location, in which case, when the alter statement completes successfully, the database will only use the new data file instead of the old data file in the original location. Therefore, after the operation is complete, you will only see the new file_to_move1.dbf data file in the control file, but you will see both the old (file_to_move2.dbf) and the new data file (file_to_move1.dbf) in the operating system directory.

SQL > alter databasemove datafile'/ u01 to'/u01/app/oracle/oradata/NONCDB/file_to_move1.dbf' KEEP

Database altered.

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move1.dbf ONLINE

SQL >! ls/u01/app/oracle/oradata/NONCDB/file_to_move*

/ u01/app/oracle/oradata/NONCDB/file_to_move1.dbf / u01/app/oracle/oradata/NONCDB/file_to_move2.dbf

Rename an online data file using the REUSE keyword

In "alter databasemove datafile..." The REUSE keyword is also specified in the statement, because in our previous renaming operation, we specified the KEEP keyword to keep the data file file_to_move2.dbf in the original directory location. If we do not specify the REUSE keyword at this time, our operation will report an error due to the existence of the target data piece. If we specify the REUSE keyword, it will directly overwrite the target data file with the same name.

SQL > alter databasemove datafile'/ u01 to'/u01/app/oracle/oradata/NONCDB/file_to_move2.dbf' REUSE

Database altered.

Data files from thecontrol file:

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf ONLINE

Data files from theOperating System Directory

SQL >! ls/u01/app/oracle/oradata/NONCDB/file_to_move*

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf

4 rename an online data file with a file number

After querying the data file file_to_move2.dbf, the corresponding data file number is 100. next, we directly use the file number corresponding to the data file file_to_move2.dbf (instead of specifying its data file name and location) to rename it to file_to_move1.dbf.

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf ONLINE

Data files from theOperating System Directory

SQL >! ls/u01/app/oracle/oradata/NONCDB/file_to_move*

/ u01/app/oracle/oradata/NONCDB/file_to_move2.dbf

SQL > select file#from v$datafile where name ='/ u01AccessAccord oradataAccord NoNCDBAccording to move 2.dbf'

FILE#

-

one hundred

SQL > alter databasemove datafile 100 to'/ u01 apprentices.oracle.oradataUniplic.NONCDB

SQL > select name,status from v$datafile where name like'% file_to_move%'

NAME STATUS

/ u01/app/oracle/oradata/NONCDB/file_to_move1.dbf ONLINE

SQL >! ls/u01/app/oracle/oradata/NONCDB/file_to_move*

/ u01/app/oracle/oradata/NONCDB/file_to_move1.dbf

After the operation is completed, through the v$datafile view, confirm that the control file has also been successfully modified.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.