Saturday, December 13, 2008

Renaming and Relocating Datafiles for a Single Tablespace

The post offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

Renaming Datafiles in a Single Tablespace

To rename datafiles from a single tablespace, complete the following steps:

  1. Take the non-SYSTEM tablespace that contains the datafiles offline.

    For example:

    ALTER TABLESPACE users OFFLINE NORMAL;
  2. Rename the datafiles using the operating system.
  3. 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:

    ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
    '/u02/oracle/rbdb1/user2.dbf'
    TO '/u02/oracle/rbdb1/users01.dbf',
    '/u02/oracle/rbdb1/users02.dbf';

    The new files must already exist; this statement does not create the files. Also, 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.

  4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved 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:

  1. Identify the datafile names of interest.

    The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

    SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'USERS';

    FILE_NAME BYTES
    ------------------------------------------ ----------------
    /U02/ORACLE/RBDB1/USERS01.DBF 102400000
    /U02/ORACLE/RBDB1/USERS02.DBF 102400000

  2. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
  3. Copy the datafiles to their new locations and rename them using the operating system.

    Note:

    You can execute an operating system command to copy a file by using the SQL*Plus HOST command.


  4. Rename the datafiles within Oracle.

    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.

    If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

    ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
    '/u02/oracle/rbdb1/users02.dbf'
    TO '/u03/oracle/rbdb1/users01.dbf',
    '/u04/oracle/rbdb1/users02.dbf';
  5. Bring the tablespace online, or open the database.

    If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

  6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.



Copying a File on a Local File System

This section includes an example that uses the COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file on a local file system. The following example copies a binary file named db1.dat from the /usr/admin/source directory to the /usr/admin/destination directory as db1_copy.dat on a local file system:

  1. In SQL*Plus, connect as an administrative user who can grant privileges and create directory objects using SQL.

  2. Use the SQL command CREATE DIRECTORY to create a directory object for the directory from which you want to copy the file. A directory object is similar to an alias for the directory. For example, to create a directory object called SOURCE_DIR for the /usr/admin/source directory on your computer system, execute the following statement:
  3. CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
  4. Use the SQL command CREATE DIRECTORY to create a directory object for the directory into which you want to copy the binary file. For example, to create a directory object called DEST_DIR for the /usr/admin/destination directory on your computer system, execute the following statement:
  5. CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
  6. Grant the required privileges to the user who will run the COPY_FILE procedure. In this example, the strmadmin user runs the procedure.
  7. GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;

    GRANT READ ON DIRECTORY source_dir TO strmadmin;

    GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
  8. Connect as strmadmin user:
  9. CONNECT strmadmin/strmadminpw
  10. Run the COPY_FILE procedure to copy the file:
     BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'db1.dat',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'db1_copy.dat');
END;
/

Oracle 10g New Feature

Oracle 10g New Feature

Thursday, September 18, 2008

Find a Lot of Real Oracle DBAs Stuff

Recently I found this one. Its a good blog giving lots of really useful tips to a DBA.

Frits Hoogland Weblog » Oracle EE

Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD) | Eric's Agile Answers

Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD) | Eric's Agile Answers: "Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD)

I suffered through quite a bit of trial and error during this installation because most of the posts on the issues encountered by those who blazed this trail before me failed to specify exactly which platform and versions they were tackling.

So, let me be clear. I started with the following:

* Oracle Database 10g Release 2 (10.2.0.1.0) for Linux x86-64
* CentOS 4.3 x86_64 (up2date sans kernel packages as of time of this post)
* jdk-1_5_0_07-linux-i586.rpm

An out of the box install on the platform above using these instructions from Oracle will fail during the linking stage of installation.
Here is the list of dependencies that got me over the hump:

compat-db-4.1.25.9 i386
compat-db-4.1.25.9 x86_64
compat-gcc-32-3.2.3.47.3 x86_64
compat-gcc-32-c++-3.2.3.47.3 x86_64
glibc-devel-2.3.4.2.19 i386
glibc-devel-2.3.4.2.19 x86_64
libaio-0.3.105.2 i386
libaio-0.3.105.2 x86_64
libaio-devel-0.3.105.2 x86_64
sysstat-5.0.5.7.rhel4 x86_64
xorg-x11-deprecated-libs-6.8.2.1.EL.13.25.1 i386
xorg-x11-deprecated-libs-6.8.2.1.EL.13.25.1 x86_64

(Note: Some of these are already installed on CentOS 4.3 but I wanted to show the x86_64 beside the i386 to drive home the point that you need both)
I installed all of the above using"