MOVING DATABASE FROM ONE LOCATION TO OTHER LOCATION

Normally, when we create database by using Database Creation agent (DBCA) tool  the default location of databasefiles,controlfiles and archivelog files is ORACLE_HOME.But it is not preferable to maintain  all the files at same location. If the disk containing ORACLE_HOME is filled up the database will go to hang state.for this ,It is a best idea maintain files at different destinations.The steps below illustrate moving database from old location to new location.

Step 1: Export environment variable ORACLE_SID

$export ORACLE_SID=<Instance_name>

Step 2:connect to the user sys with sysdba role

$sqlplus / as sysdba

Step 3:find UDUMP location

SQL>show parameter user_dump_dest

Step 4:take backup of control file in ASCII format

SQL>alter database backup controlfile to trace;

Step 5:Shutdown the database

SQL>shutdown immediate

Step 6:Go O/s level and identify latest trace file in UDUMP directory.

Step 7:Prepare require directory structure in new location by using o/s commands.

Step 8:edit the trace file and prepare script to recreate the controlfile.

Step 9: run the script at sql prompt to recreate the controlfile.

Step 10:Bring database from mount to state to open state.

Command:SQL>alter database open;

Step 11:After recreating the controlfile the database is running with out temporary tablespace. for this, create new temporary tablespace and make it default temporary tablespace to the database.

Sql>create temporary tablespace temp tempfile '<location>' size 30m;

Sql>alter database default temporary tablespace temp;