Oracle перенос datafile на другой диск
In many situations, a datafile or logfile must be renamed inside Oracle. Whereas the contents of the file remain valid, you need to define a new physical name or location for it. For example:
- You want to move a database file to a different disk for performance or maintenance reasons.
- You have restored a datafile from backup, but the disk where it should normally be placed has crashed and you need to relocate it to a different disk.
- You have moved or renamed a datafile at the operating system level but forgot to rename it within Oracle. At startup, you get ORA-01157 and ORA-01110. If the database is up and you try to shut it down normal or immediate, you get ORA-01116 ad ORA-01110.
- You have multiple databases on the same machine and you need to rename certain database files to prevent collision and confusion.
This post gives instructions to:
- Rename or move datafile(s) with the database open.
- Rename and/or move datafile(s) with the database shut down.
- Rename and/or move a logfile.
NOTE: To rename or relocate datafiles in the SYSTEM tablespace you must use option 2 shown above – ‘Renaming or Moving a Datafile with the Database Shut Down’, because you cannot take the SYSTEM tablespace offline.
Controlfiles
The current location of the controlfiles can be queried from the V$CONTROLFILE view, as shown below.
In order to rename or move these files we must alter the value of the control_files instance parameter.
To move or rename a controlfile do the following.
- Alter the control_files parameter using the ALTER SYSTEM comamnd.
- Shutdown the database.
- Rename the physical file on the OS.
- Start the database.
The following SQL*Plus output shows how this is done for an instance using an spfile. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file.
Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.
Basic Syntax
The text description of the syntax is shown below, but the syntax diagrams and a full description of the ALTER DATABASE command is available in the documentation here.
The source file can be specified using the file number or name, while the destination file must be specified by the file name. The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.
When the source file is an OMF file the KEEP option can not be used. If the destination file is an OMF file, the TO clause can be omitted and the file will be created with an OMF name in the DB_CREATE_FILE_DEST location.
The file number can be queried from the V$DATAFILE and DBA_DATA_FILES views.
4. Relocate the online redo logs
As online redo logs are not backed up by RMAN, you will need to relocate them outside of RMAN:
a. identify the list of online redo logs:
b. make an o/s copy of the line redo logs to the new location:
c. now rename the log files, do this for each of the redo log files:
Procedures for Renaming and Relocating Datafiles in a Single Tablespace
The section suggests some procedures for renaming and relocating datafiles that can be used for a single tablespace. You must have ALTER TABLESPACE system privileges.
"Taking Tablespaces Offline" for more information about taking tablespaces offline in preparation for renaming or relocating datafiles
Manual (Offline)
The process for manually renaming a datafile is the same as renaming a logfile, but for the sake of clarity it is repeated below. The current location of the datafiles can be queried from the V$DATAFILE view, as shown below.
To move or rename a datafile do the following.
- Shutdown the database.
- Rename the physical file on the OS.
- Start the database in mount mode.
- Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
- Open the database.
The following SQL*Plus output shows how this is done.
Repeating the initial query shows that the the datafile has been renamed in the data dictionary.
Pluggable Database (PDB)
The container database (CDB) can not move files that belong to a pluggable database. The following query displays all the datafiles for the CDB and the PDBs.
If we try to move a datafile belonging to a PDB an error is returned.
If we switch to the PDB container, the datafile can be moved as normal.
Rename or move datafile(s) with the database open
Datafiles can be renamed or moved while the database is open. However, the tablespace must be made READ-ONLY. This will allow users to select from the tablespace but prevents them from doing inserts, updates, and deletes. The amount of time the tablespace is required to be read-only will depend on how large the datafile(s) are and how long it takes to copy the datafile(s) to the new location.
Making the tablespace read only freezes the file header, preventing updates from being made to the file header. Since this datafile is then at a read only state, it is possible to copy the file while the database is open. To do this you must follow these steps:
1. Determine how many datafiles are associated with the tablespace.
2. Make sure that all datafiles returned have the status AVAILABLE.
3. Make the tablespace is read only.
4. Make sure that the tablespace is defined as read only in the data dictionary.
5. Copy the datafile(s) to the new location using the operating system copy command. Once the datafile(s) have been copied to the new location compare the sizes of the datafiles. Make sure that the sizes match.
NOTE: The same method could be used to rename a datafile as a kind of in-place copy. However, on Windows that fails with a message like:
This is not a real limitation because the procedure is meant for actually moving the file to a new location for intended storage management. Only renaming the file has no function here and this can be done during downtime.
However, note that you could remove the file lock at your own risk with available(freeware) utilities.
6. Once the datafiles have been copied to the new location alter the tablespace offline.
At this point, the tablespace is not accessible to users.
7. Once the tablespace is offline you will need to rename the datafile(s) to the new location. This updates the entry for the datafile(s) in the controlfile.
You will need to do this for all datafiles associated with this tablespace. You can use the ALTER TABLESPACE … RENAME DATAFILE syntax as well.
8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.
9. After you bring the tablespace back online you can make the tablespace read/write again.
10. You can check the status of the tablespace to make sure it is read/write. You can also verify that the controlfile has been updated by doing the following:
This will produce a readable copy of the contents of your controlfile which will be placed in your user_dump_dest directory. Optionally, you can query V$DATAFILE, which gets information from the controlfile as well.
11. Remove the datafile(s) from the old location at the O/S level.
Rename and/or move a logfile
1. Shutdown the database.
2. Copy the logfile to the new name/location at the operating system level.
3. Mount the database.
4. Rename the file.
5. Open the database.
6. Remove the logfile(s) from the old location at the operating system level.
You can rename datafiles to either change their names or relocate them. Some possible procedures for doing this are described in the following sections:
When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed. The procedures do not physically rename any operating system files, nor do they copy files at the operating system level. Renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.
Examples
The following example shows a basic file move, specifying both source and destination by name. Notice the original file is no longer present.
The next example uses the file number for the source file and keeps the original file.
The next example shows the use of OMF.
The final example attempts to use the KEEP option, where the source file in an OMF file. Notice how the KEEP option is ignored.
Relocating a few non-system datafiles
The below mentioned steps can be done when the database is open. You can only do this for non-system datafiles which are less in number. It only needs a minimal outage during the rename. For the purpose of this post example, we will relocate the datafile 5 to new location.
7. Relocating TEMP files
If you need to relocate temp then simply drop and recreate it in SQL*Plus:
Tempfiles
Not surprisingly, the ALTER DATABASE MOVE DATAFILE syntax does not work for temporary files.
That is not major problem as temporary files can be created and removed quite simply.
Procedure for Renaming Datafiles in a Single Tablespace
To rename datafiles in a single tablespace, complete the following steps:
Take the tablespace that contains the datafiles offline. The database must be open.
Rename the datafiles using the operating system.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to /u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf , respectively:
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
Recreating the Controlfile
For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.
The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.
This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.
Relocating all the database files
This solution requires the database to be in mounted mode. We will relocate all database files, controlfiles and redo logs as per steps given below.
Datafiles
3. Switch to the datafile copies
Switch the datafile copies to be able to read them from new location.
6. Start the database
Once you have completed all the above steps, you may proceed to start the database.
2. Copy all datafiles to the new location
Next step is to start copying the datafiles to new location. There are 2 cases here.
a. Copying datafiles with different names (database residing on OS file system)
When you specify the variable %U, RMAN auto generates new filenames while copying.
b. Copying datafiles with same name
To keep the same names you can use db_file_name_convert option as follows:
Logfiles
The current location of the logfiles can be queried from the V$LOGFILE view, as shown below.
To move or rename a logfile do the following.
- Shutdown the database.
- Rename the physical file on the OS.
- Start the database in mount mode.
- Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
- Open the database.
The following SQL*Plus output shows how this is done.
Repeating the initial query shows that the the logfile has been renamed in the data dictionary.
5. Relocate the controlfiles
Follwo the steps below to relocate the controlfiles.
a. backup current controlfile to new location:
b. duplicate the controlfile copy:
c. change the controlfile locations:
Manual (Almost Online)
For tablespaces other than the SYSTEM tablespace, you can move the datafiles while the database is online, provided you take the relevant tablespace offline during the rename operation.
An example of this is shown below.
The downtime associated with the tablespace rename is dependent on the length of time the physical rename/move takes. For a simple rename in place, it should happen immediately. If the file has to be moved to a new location, it will take as long as the file move takes to complete.
Thanks Noons for pointing out this glaring omission from the article.
Procedure for Relocating Datafiles in a Single Tablespace
Here is a sample procedure for relocating a datafile.
Assume the following conditions:
An open database has a tablespace named users that is made up of datafiles all located on the same disk.
The datafiles of the users tablespace are to be relocated to different and separate disk drives.
You are currently connected with administrator privileges to the open database.
You have a current backup of the database.
Complete the following steps:
If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES :
Take the tablespace containing the datafiles offline:
Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".
You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.
Rename the datafiles within the database.
The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.
Use the ALTER TABLESPACE. RENAME DATAFILE statement.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
Data Guard Environments
In Data Guard environments you have to be careful about renaming and moving files. The STANDBY_FILE_MANAGEMENT parameter determines how file changes on the primary server are applied to the standby server. When set to AUTO, files added or deleted under normal database use will be automatically created or deleted on the standby server. When set to MANUAL, this automatic maintenance will not happen.
If you are manually moving or renaming files in a Data Guard environment where STANDBY_FILE_MANAGEMENT=AUTO , you should first set STANDBY_FILE_MANAGEMENT=MANUAL , make your changes in the primary and standby environment, then set STANDBY_FILE_MANAGEMENT=AUTO again.
The requirement here is to move or relocate the database files to some other location. So, there are 2 scenarios here,
1. Relocating all the database files – datafiles, online redo logs, and controlfiles ( This requires the database to be in mounted mode )
2. Relocating Non-system datafiles – This can be relocated with minimal outage.
The examples below use /data02/oradata as the new destination. You can use any destination you like, including new ASM disk groups. So For this example, we have :
Data Guard
We connect to the primary database (cdb1), check the datafile location, move it and check again.
We connect to the standby database (cdb1_stby) and check the location of file 1. We can see it hasn't moved.
We can move it on the standby database, but we have to turn off the apply process.
This article presents a brief explanation of how assorted Oracle files can be renamed or moved to a new location. The examples are based on a default Oracle 10g installation on Windows, but the method is the same for different versions of Oracle on any platform, with the exception of the host command used to rename the file.
Rename and/or move datafile(s) with the database shutdown
1. If the database is up, shut it down.
2. Copy the datafile to the new name/location at the operating system level.
3. Mount the database.
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
Do this for all the datafiles that were renamed or moved at the operating system level.
5. Open the database.
6. Query v$dbfile to confirm that the changes made were correct.
7. Remove the datafile(s) from the old location at the operating system level.
1. backup the datafile to the new location
To relocate the datafile 5, we would take a backup of the datafile first.
Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.
To rename datafiles in multiple tablespaces, follow these steps.
Ensure that the database is mounted but closed.
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.
Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".
Use ALTER DATABASE to rename the file pointers in the database control file.
For example, the following statement renames the datafiles /u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf , respectively:
Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
Prior to Oracle 12c, moving datafiles has always been an offline task. There were certain techniques you could employ to minimize that downtime, but you couldn't remove it completely. Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online.
1. Restart database in mount mode.
First thing first, we need to start the database in mount mode to be able to perform the relocation.
Online Move (12c)
Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.
RMAN can be used to move files with less downtime by copying them in advance of the move, then recovering them as part of the move itself. First, log in to RMAN and list the current files.
Copy the file(s) to the new location.
Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.
Switch to the new datafile copy(s) and recover the tablespace.
Turn the tablespace online again.
Remove the old datafile(s).
Listing the current files shows the move is complete.
Moving the SYSTEM tablespace is possible using a similar method, but the database must be shutdown and mounted before the switch and recover can be done.
Читайте также: