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;

  

Control file multiplexing

This document tells us how to multiplex control file in ORACLE databases.Controlfile contains crucial information about the database. It contains information about
1) Database creation time.
2) Time stamp of database.
3) Data files and redo log file information.
4)check point information.
5)log sequence number.
6) backup information.
7)Time stamp of database.
By based on this information control file is crucial for database. But the size of control file is always small. Multiplexing control is an important task in database admistration.It is always better to maintain multiple copies of control file in different disk location's. If one control file will have corrupted we will demultiplex the control file and up the database otherwise we will copy the control file(which is not corrupted) to the  corrupted location. I am giving steps for control file multiplexing below.
step 1: export ORACLE_SID=<Instance Name>
step 2: connect to sql prompt with sysdba privilege.
step 3:Then check control file location of database.
           command: sql>show parameter control_files
step 4:show parameter spfile.
step 5:If database is running by using spfile then go to step 6
step 6:update the information of new control file location in spfile.
command:sql>altersystem set control_files='/disk1/oradata/db1/control.ctl','/disk2/oradata/db1/control.ctl'  scope=spfile;
step 7:down the database.
      command:sql>shutdown immediate.
step 8:copy the control file to the new location by using operating system commands.
        $cp <old location> <new location>
step 9:startup the database
        command:sql>startup
step 10: If database had started with pfile,then manually update the value of the parameter in init.ora .execute the steps form step 7