How to Make a Datafile Online in Oracle Database

How to Make a Datafile Online in Oracle Database

Introducing Oracle Datafiles

The Oracle tablespace is a large unit of logical storage consisting of structured data objects, such as tables and indexes. A database’s data is then collectively stored in the physical datafiles that constitute each tablespace of the database. These are physical structures that conform to the operating system in which Oracle is running.

Each Oracle server consists, therefore, of tablespaces and their datafiles, whether the database has one tablespace with one datafile or 5 tablespaces, each containing any amount of datafiles. The tablespaces that collectively store all of the database’s data consist of datafiles.

Making a new datafile online

There are several reasons why a DBA would want to make a new Oracle datafile. When you add another datafile to an existing tablespace you increase the amount of disk space allocated for the corresponding tablespace. Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. The third option for enlarging a database is to change a datafile’s size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties. Below are some statements you can use to create files.

• • CREATE TABLESPACE Creates a tablespace and the datafiles that comprise it
  • CREATE TEMPORARY TABLESPACE Creates a locally-managed temporary tablespace and the tempfiles (tempfiles are a special kind of datafile) that comprise it
  • ALTER TABLESPACE … ADD DATAFILE Creates and adds a datafile to a tablespace
  • ALTER TABLESPACE … ADD TEMPFILE Creates and adds a tempfile to a temporary tablespace
  • CREATE DATABASE Creates a database and associated datafiles
  • ALTER DATABASE … CREATE DATAFILE Creates a new empty datafile in place of an old one — useful to re-create a datafile that was lost with no backup.

If a statement that creates a datafile fails, the database removes any created operating system files. However, because of the large number of potential errors that can occur with file systems and storage subsystems, there can be situations where you must manually remove the files using operating system commands.

You must specify the new filenames If you add new datafiles to a tablespace, or else the database creates the datafiles in the current or default database directory, depending upon your operating system. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.

Moving datafiles online

You can use the ALTER DATABASE MOVE DATAFILE statement to move a datafile to a different location, disk or storage system. This provides a simple online method of migrating to a new storage platform. Follow these steps to rename and move your Oracle datafile:

1. 1. Shut down the database
   2. Rename the physical file on the OS
   3. Start the database in mount mode
   4. Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary
   5. Open the database

For example:

ALTER TABLESPACE solvaria

 ADD DATAFILE ‘/u02/oradata/tgs/solvaria01.dbf’ 

 SIZE 100M;

The above command will add a second datafile called solvaria01.dbf to the existing solvaria tablespace. The initial size of this tablespace will be 100M.

To create a datafile in a standby database, use this script:

SQL> alter database create datafile ‘/oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00005’ as new;

database altered.

Managing Oracle Tablespaces

Bringing Oracle Tablespaces Online

First, you must find the Tablespace that you want to bring online. To find the name of the tablespace, enter:

select * from dba_tablespaces

To bring a previously offline tablespace back online, use the following script:

SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space 

  2  DATAFILE ‘/temp/my_space.dbf’ SIZE 10M;

Tablespace created.

SQL> ALTER TABLESPACE my_space OFFLINE NORMAL;

Tablespace altered.

…

SQL> ALTER TABLESPACE my_space ONLINE;

Tablespace altered.

Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE … RENAME.

Altering Oracle Tablespaces

Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its datafiles or tempfiles.

If you have ALTER TABLESPACE system privilege, then you can perform any ALTER TABLESPACE operation. If you have MANAGE TABLESPACE system privilege, then you can only perform the following operations:

• Take the tablespace online or offline
• Begin or end a backup
• Make the tablespace read only or read write
• The tablespace must be online.Before you can make a tablespace read only, the following conditions must be met:
  • The tablespace must not contain any active rollback segments. For this reason, the SYSTEM tablespace can never be made read only, because it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, Oracle recommends that you drop the rollback segments before you make a tablespace read only.
  • The tablespace must not be involved in an open backup, because the end of a backup updates the header file of all datafiles in the tablespace.

Performing this function in restricted mode may help you meet these restrictions, because only users with RESTRICTED SESSION system privilege can be logged on.

Oracle 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). Oracle tablespace management commands are available from a pluggable database (PDB), given that you are pointing to the correct container. You can connect using a common user then switch to the correct container.

SQL> CONN / AS SYSDBA

Connected.

SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

——————————

PDB1

SQL>

If you have further questions or concerns about your Oracle database, visit our Oracle resources section on our website, or check out our Oracle 12c Backup and Recovery Best Practices blog article. If you still need answers, reach out to one of our expert DBAs via the form below.