Additional Data File Operations- Tablespaces and Data Files

Occasionally you may need to move or rename a data file. For example, you may need to move data files because of changes in the storage devices or because the files were created in the wrong location or with a nonstandard name.

The ALTER DATBASE MOVE DATAFILE command allows you to rename or move data files without any downtime. Here is an example:

You can also specify the data file number from v$datafile when renaming or moving a data file:

If you are moving a data file and want to keep a copy of the original file, you can use the KEEP option:

You can specify the REUSE clause to overwrite an existing file. Oracle will not allow you to overwrite or reuse a data file that is currently being used by the database, which of course is a good thing.

Move SYSTEM and UNDO

To move the SYSTEM tablespace, you need to take the tablespace offline, and because it is SYSTEM, you can take it offline only while the database is closed and not open.

Because the database is in mount mode, the data files are not open for use, and therefore the data files do not need to be taken offline. The next step is to physically move the files via the Linux mv command:

You must move the files before you update the control file. The ALTER DATABASE RENAME FILE command expects the file to be in the renamed location. If the file is not there, an error is thrown: ORA-27037: unable to obtain file status.

Now you can update the control file to be aware of the new filename:

Next is to open the database:

Using ASM for Tablespaces

Using ASM to manage the physical disk and storage allocation simplifies the management of the tablespaces and data files. Adding storage means adding disks to a disk group and allows for additional space to be dynamically available to the tablespaces. With storage hardware advances, there are also ways to add disks to mount points. It

just depends how the databases and environments are being managed and configured if ASM is part of the environment.

There are plenty of advantages for using ASM, including shared storage, ease of disk management, data file repairs, and verifications specific to the database. As part of the grid infrastructure, the +ASM instance is created and provides a way to share storage for several databases, rebalances workloads, and provides higher availability for the database storage.

The parameters for using a default storage space have already been discussed, and instead of naming mount points that can change as databases grow or move, the database using +ASM can use a disk group without having to worry about names for mount points. A disk group called oradata is created to be used for the database storage. The parameters for file destinations are set using the following command:

This will create a tablespace named HRDATA on the oradata diskgroup. The filenames are generated by +ASM, and to create aliases by default, a template for the filenames in +AS is used. If a template is used, the DB_CREATE_FILE_DEST parameter will point to that template along with the disk group.

The data files and tablespace views are still available to see what tablespaces

are created and the data files that are part of the database. The view v$datafile and dba_data_files will show the files starting with the disk group +oradata. The dba_tablespaces view will still show the HRDATA tablespace as with non-ASM databases. There are also additional views that will show the files in the disk groups. To see the ASM disks in the disk group view, v$asm_disk should be queried. The files in the disk group are seen in the v$asm_file and v$asm_alias views.

From v$asm_file, the number, type, and space information are available, and v$asm_alias brings in the data filename:

The Oracle cloud uses the ASM and ASM Cluster File System (ACFS) to present the storage. It simplifies and automates storage management. There is no need for another volume or file manager tool. Again, there are advantages to using the storage management tools that come with the Oracle Database. There are plenty of reference materials to show how to create disk groups, add or drop disks, perform maintenance, and manage the ASM storage and file systems.

ASM is becoming a standard installation for the Oracle Base Database Service in the Oracle Cloud, and the overhead of managing an ASM instance simplifies adding storage and creating tablespaces. There are benefits to having the disk configured for Oracle databases, and throughout the rest of the book, you will see examples for both managing ASM and diskgroups and filesystems.

As a DBA, you must be proficient in managing tablespaces and data files. In any type of environment, you have to add, rename, relocate, and drop these storage containers. These are ideal tests that can be done when first creating a database or in a test environment to practice the commands and restore data files. The commands, errors, and issues can be logged for future reference to use in a high-pressure situation for data file corruption and recovery.

Oracle requires three types of files for a database to operate: data files, control files, and online redo log files. Data files are in both CDBs and PDBs. Control files and online redo files are part of the CDB and managed there, which we will discuss in the next chapter.

Leave a Reply

Your email address will not be published. Required fields are marked *