How To Move Datafiles In Oragle 11gR2

How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g

How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g

When you accidentally create a datafile in the wrong folder, you can move it to the intended location. Unlike a regular file move, this process in Oracle 11g involves both operating system commands and an update to Oracle’s control file. Follow these steps carefully.

Important:
  • Backup: Before you begin, ensure you have a current backup of your database or at least the affected tablespace.
  • Privileges: You must have the appropriate administrative privileges to perform these operations.
  • Environment: The steps differ slightly based on whether you’re dealing with user tablespaces or system-critical tablespaces.

Step 1: Identify the Datafile

Start by confirming the file name and its current location. Connect to SQL*Plus or your preferred Oracle client and run:

SELECT file_name, tablespace_name 
FROM dba_data_files;

Review the output to locate the datafile you want to move and note its current path.

Step 2: Prepare the Database

Option A: For Non-Critical (User) Tablespaces

You can take just the affected tablespace offline:

ALTER TABLESPACE your_tablespace_name OFFLINE;

Option B: For System or Critical Tablespaces

For files in the SYSTEM, UNDO, or other critical tablespaces, you need to shut down the database and restart it in MOUNT mode:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Step 3: Move the Datafile at the OS Level

Using your operating system’s file commands, move the datafile from the current location to the new folder. For example, on a UNIX/Linux system:

mv /old_path/your_datafile.dbf /new_path/your_datafile.dbf
Note:
  • Ensure that the new folder has the proper permissions and enough disk space.
  • On Windows, you can use the File Explorer or the move command in Command Prompt.

Step 4: Update Oracle’s Control File

After physically moving the file, you must update Oracle’s control file so it knows where to find the datafile. Run the following command in SQL*Plus:

ALTER DATABASE RENAME FILE '/old_path/your_datafile.dbf' TO '/new_path/your_datafile.dbf';

This command tells Oracle that the file has been moved to the new location.

Step 5: Bring the Database or Tablespace Online

If You Took Only the Tablespace Offline:

ALTER TABLESPACE your_tablespace_name ONLINE;

If You Shut Down the Entire Database:

ALTER DATABASE OPEN;

Step 6: Verify the Change

Finally, verify that Oracle recognizes the new location by running:

SELECT file_name, tablespace_name 
FROM dba_data_files 
WHERE file_name LIKE '%your_datafile.dbf%';

The output should show the new path for the datafile.

Recap

  1. Identify the datafile: Check the current location and tablespace.
  2. Prepare the database:
    • For user tablespaces, take the tablespace offline.
    • For system-critical tablespaces, shut down the database and start it in MOUNT mode.
  3. Move the file at the OS level: Use OS commands to relocate the file.
  4. Update the control file: Use ALTER DATABASE RENAME FILE to inform Oracle of the new location.
  5. Bring the database or tablespace back online: Open the tablespace or the entire database.
  6. Verify the move: Confirm the new location with a query.

Comments

Post a Comment

Popular posts from this blog

Oracle Database Upgrade With OPatch Tool (RHEL/Centos/OEL)

Follow these steps to upgrade an Oracle Database using the OPatch tool: 1. Download and Prepare Files Download the patch you want to install along with the latest OPatch tool from Oracle's website. Transfer all downloaded files to the /tmp/ directory, and unzip them. Ensure the correct permissions and ownership are set: chown -R oracle:oinstall p26710464_122010_Linux-x86-64.zip chown -R oracle:oinstall p6880880_122010_Linux-x86-64.zip unzip p26710464_122010_Linux-x86-64.zip unzip p6880880_122010_Linux-x86-64.zip 2. Replace the OPatch Folder Replace the current OPatch folder inside the Oracle database files with the new one: cd $ORACLE_HOME cp -R OPatch OPatch_yedek # Backup current OPatch folder. rm -rf OPatch cp -R /tmp/Opatch . Verify the OPatch version and list applied patches: cd OPatch ./opatch lspatches 3. Check for Conflicts Navigate to the patch folder and run the following command to check for conflicts before starting the upgrade: cd /tmp/26710464/ $O...
Read more

POSTGRESQL UPGRADE WITH PG_UPGRADE UTILITY IN RHEL/CENTOS/OEL

Upgrading PostgreSQL can be a smooth process if you follow the necessary steps carefully. Below is a comprehensive guide to help you through the upgrade. Table of Contents Prerequisites Install the New PostgreSQL Version Set Up New Directories and Permissions Initialize the New Database Configure PostgreSQL Settings Stop and Disable Old PostgreSQL Services Run the Upgrade Process Finalize the Upgrade Post-Upgrade Tasks Prerequisites Before initiating the upgrade, ensure the following prerequisites are met: Internet Connection: Required for downloading packages. PostgreSQL RPM Packages: Necessary for installation. Free Disk Space: Ensure you have more free space than the current database size. Compatibility Checks: Verify PostgreSQL extension compatibility. Crontab Jobs: Identify any cron jobs related to PostgreSQL. Note: Skipping these checks may result in errors during the upgrade process. Install the New PostgreSQL Versio...
Read more

Creating Jobs With Different Users via pg_cron in Azure Postgresql Flexible Server

PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases In this tutorial, I’ll walk you through the process of scheduling a Materialized View refresh using the pg_cron extension in PostgreSQL. Specifically, we’ll cover how to execute a scheduled SQL command in a different database using cron.schedule_in_database . Step 1: Enable the pg_cron Extension in Azure Portal On Azure PostgreSQL Flexible Server, the pg_cron extension is managed directly through the Azure portal. Follow these steps: Go to the Azure portal and navigate to your PostgreSQL Flexible Server instance. Under the Server Parameters section, locate the azure.extensions parameter. Add PG_CRON to the list of allowed extensions (as shown in the screenshot). Once enabled, the extension is automatically installed in the default `postgres` database . If you need to schedule jobs in o...
Read more