Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 22 April 2012

How to move datafiles with RMAN

The new way of moving files with RMAN is a safer move and having minimal downtime. This method requires that database is running in archive log mode. The archive logs are necessary to perform recovery part of database. This method is also useful to take the backup of datafiles.
1.      Make the RMAN backup image copy in the newly desired location [note: this is done while the database is online]
2.      New step: Ensure the datafile copied as expected (by grepping through the RMAN log, or utilizing a file comparison utility such as diff or HP-UX’s cmp to compare the copy to the original)
3.      Take the tablespace [that the datafile belongs to] offline [normal]. Note: this will quiesce the datafiles associated with the tablespace. Also, the Oracle RDBMS will not let you do an offline normal on tablespaces that are currently in use [outstanding transactions].
4.      New step: Ensure the tablespace is really offline (query the dba_tablespaces view for status)
5.      Use RMAN to “switch” to the newly created backup datafile. RMAN has a single command that will tell the database to use the backup copy for the datafile – the original file will no longer be in play.
6.      New step: Ensure you really have switched – check the RMAN log
7.      Use RMAN to do a “recover datafile” on the datafile being moved. This will use the necessary archive logs to sync the backup copy datafile with the original datafile.
8.      New step: Check the RMAN log for a success message to indicate a successful recovery
9.      Put the tablespace back online
10.  New step: Ensure the tablespace is successfully back online (check the dba_tablespaces view for status)
11.  Remove the original datafile from the filesystem. If you are paranoid, rename the original file to {original name}.orig, then over the next couple days check the datetime stamp on the original files. If they have not been touched by the OS/filesystem, they can safely be removed.
Select file_id, file_name from dba_data_files where file_name = '{filename in question}'‎
This gets the file id, name and path of the original file - all are important.‎
Now - set up your environment variables (ORACLE_HOME, PATH, ORACLE_SID, etc.)‎
Now, get into RMAN:‎
rman> connect target /‎
rman> BACKUP AS COPY DATAFILE {file id} FORMAT '{new file name/new location}';‎
rman> sql 'ALTER TABLESPACE {tablespace of datafile} OFFLINE';‎
rman> SWITCH DATAFILE {file id} TO COPY;‎
rman> RECOVER DATAFILE {file id};‎
rman> sql 'ALTER TABLESPACE {tablespace of datafile] ONLINE';‎
rman> host 'rm -f {filename of ORIGINAL file}';‎
Note: The above steps I have taken from Mr. Gary blog and tested on oracle 10g, you can follow this ‎link for details:


Post a Comment