How to rename or move a datafile in oracle

-

How to rename or move a datafile in oracle

Renaming/Relocating a Data File or Tablespace

1. Take the tablespace offline:
ALTER TABLESPACE USER_DATA OFFLINE;

2. Copy the file to the new location by using OS commands on the disk.

3. Rename the files in the database by using one of the following two commands. The number of data files specified before the keyword TO should be equal to the number of files specified after the keyword.

ALTER DATABASE RENAME FILE
‘/disk1/oradata/DB01/user_data01.dbf‘,
‘/disk1/oradata/DB01/userdata2.dbf‘,
‘/disk1/oradata/DB01/user_data03.dbf‘
TO
‘/disk2/oradata/DB01/user_data01.dbf‘,
‘/disk2/oradata/DB01/user_data02.dbf‘,
‘/disk2/oradata/DB01/user_data03.dbf‘;

Or

ALTER TABLESPACE USER_DATA RENAME DATAFILE
‘/disk1/oradata/DB01/user_data01.dbf‘,
‘/disk1/oradata/DB01/userdata2.dbf‘,
‘/disk1/oradata/DB01/user_data03.dbf‘
TO
‘/disk2/oradata/DB01/user_data01.dbf‘,
‘/disk2/oradata/DB01/user_data02.dbf‘,
‘/disk2/oradata/DB01/user_data03.dbf‘;

4. Bring the tablespace online:
ALTER TABLESPACE USER_DATA ONLINE;


Renaming or Relocating Files Belonging to Multiple Tablespaces

If you need to rename or relocate files belonging to multiple tablespaces or if the file belongs to the SYSTEM tablespace, you must follow these steps:

1. Shut down the database. A complete backup is recommended before making any structural changes.

2. Copy or rename the files on the disk by using OS commands.

3. Start up and mount the database (STARTUP MOUNT).

4. Rename the files in the database by using the ALTER DATABASE RENAME FILE command.

5. Open the database by using ALTER DATABASE OPEN.

Moving Read-Only Tablespaces

If you need to move read-only tablespaces to a CD-ROM or any write-once read-many device, follow these steps:

1. Make the tablespace read-only.

2. Copy the data files belonging to the tablespace to the read-only device.

3. Rename the files in the database by using the ALTER DATABASE RENAME FILE command.

A Real World Scenario

Your operating-system administrator informed you that he is seeing lot of contention on the H drive and is seeking options to move some of the reads off the H drive and to G drive. As a DBA, you can move one of the hot files belonging to the receivables tablespace to the G drive.

You need to take a tablespace offline to perform some maintenance operations, such as recovering the tablespace or moving the data files to a new location. Use the OFFLINE clause with an ALTER TABLESPACE statement to take a tablespace offline. Follow these steps to rename or move a data file:

1. Take the receivables tablespace offline:
ALTER TABLESPACE receivables OFFLINE;

2. Use an operating-system program to physically move the file, such as Copy in Microsoft Windows or cp in Unix.

3. Tell the database about the new location:
ALTER TABLESPACE receivables RENAME DATAFILE
‘H:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF’
TO ‘G:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF’ ;

4. Bring the tablespace back online:
ALTER TABLESPACE receivables ONLINE;

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more