How to move data files from one mount point to another mount point

How to move/rename data files

We can move or rename data files other than SYSTEM, SYSAUX and UNDO data files while the database is up and running. But we need to keep tablespaces offline while the data files move or name.  Database should be in mount stage while SYSTEM, SYSAUX and UNDO data files to move or rename.

Step1:  Find the tablespace names and associated data files.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME        FILE_NAME

---------------                       ---------------------------------------------

USERS                              /u01/app/oracle/oradata/TEST/users01.dbf

UNDOTBS1                     /u01/app/oracle/oradata/TEST/undotbs01.dbf

SYSAUX                          /u01/app/oracle/oradata/TEST/sysaux01.dbf

SYSTEM                          /u01/app/oracle/oradata/TEST/system01.dbf

EXAMPLE                       /u01/app/oracle/oradata/TEST/example01.dbf

Step 2: Move/rename USERS and EXAMPLE data files.

a) Create new directory structure where we want to move the files.

[oracle@server3 TEST]$ mkdir –p /u02/oradata/TEST

b) Move the data files related to USERS and EXAMPLE tablespaces. Put both USERS and EXAMPLE tablespaces offline. Both the tablespace will not be available when we put them offline.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace example offline;

Tablespace altered.

c) Move the USERS and EXAMPLE data files to new location

SQL> !

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/users01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/example01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ exit

d) Update the controlfile and data dictionary with data files new location.

SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/TEST/users01.dbf' to '/u02/oradata/TEST/users01.dbf';

Tablespace altered.

SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/TEST/example01.dbf' to '/u02/oradata/TEST/example01.dbf';

Tablespace altered.

SQL>

e) Bring tablespaces online.

SQL> alter tablespace users online;

Tablespace altered

SQL> alter tablespace example online;

Tablespace altered

f) Find the updated location details for USERS and EXAMPLE tablespaces.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

--------------- ---------------------------------------------

USERS                  /u02/oradata/TEST/users01.dbf

UNDOTBS1         /u01/app/oracle/oradata/TEST/undotbs01.dbf

SYSAUX              /u01/app/oracle/oradata/TEST/sysaux01.dbf

SYSTEM              /u01/app/oracle/oradata/TEST/system01.dbf

EXAMPLE           /u02/oradata/TEST/example01.dbf

Step 3: Move SYSTEM, SYSAUX, UNDO data files to new location. Database should be in mount stage to move these data files. Shutdown the database and start in mount stage.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                                2251856 bytes

Variable Size                        163578800 bytes

Database Buffers                50331648 bytes

Redo Buffers                         5169152 bytes

Database mounted.

SQL>

a) Move SYSTEM, SYSAUX and UNDO related data files to new location.

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/system01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/sysaux01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/undotbs01.dbf /u02/oradata/TEST/

b) Update controlfile and data dictionary with new data files locations.

SQL> alter database rename file '/u01/app/oracle/oradata/TEST/system01.dbf' to '/u02/oradata/TEST/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TEST/sysaux01.dbf' to '/u02/oradata/TEST/sysaux01.dbf';

Database altered.

SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/TEST/example01.dbf' to '/u02/oradata/TEST/example01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

--------------- ---------------------------------------------

USERS                    /u02/oradata/TEST/users01.dbf

UNDOTBS1           /u02/oradata/TEST/undotbs01.dbf

SYSAUX                /u02/oradata/TEST/sysaux01.dbf

SYSTEM                /u02/oradata/TEST/system01.dbf

EXAMPLE             /u02/oradata/TEST/example01.dbf

How to move/rename redo log files.

How to move/rename redo log files.

This document tells us how to move/rename redo log files from one location to other location.

Step 1: Find the location of redo log files by issuing below command.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/TEST/redo03.log

/u01/app/oracle/oradata/TEST/redo02.log

/u01/app/oracle/oradata/TEST/redo01.log

Step 2: Shutdown database.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Step 3: Create file structure where we want to move the redo logs.

[oracle@server3 TEST]$ mkdir -p /u02/oradata/TEST

Step 4: Move redo log files from current location to new location.

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/redo03.log /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/redo02.log /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/redo01.log /u02/oradata/TEST/

Step5: We need to update the new location information in data dictionary and control files. Start the database in mount stage and update them.

SQL> startup mount

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                                2251856 bytes

Variable Size                        163578800 bytes

Database Buffers                50331648 bytes

Redo Buffers                         5169152 bytes

Database mounted.

SQL>

SQL> alter database rename file '/u01/app/oracle/oradata/TEST/redo03.log' to '/u02/oradata/TEST/redo03.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TEST/redo02.log' to '/u02/oradata/TEST/redo02.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TEST/redo01.log' to '/u02/oradata/TEST/redo01.log';

Database altered.

Step 6: Check the redo log files location by issuing below command.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u02/oradata/TEST/redo03.log

/u02/oradata/TEST/redo02.log

/u02/oradata/TEST/redo01.log

SQL> 

Step 7: Open database.

SQL> alter database open;

Database altered.

SQL>

How to move control files from one mount point to other location.

How to move control file from one mount point to different mount point.

Step 1: Set the environment for specific SID

[oracle@server3 ~]$ . oraenv

ORACLE_SID = [TEST] ?

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

Step 2: Connect to database

[oracle@server3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 13 14:59:34 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Step 3: Find the name, version and control files location.

SQL> select name from v$database;

NAME

---------

TEST

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE     11.2.0.4.0             Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter control_files

NAME                                                        TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time                     integer            7

control_files                                            string                /u01/app/oracle/oradata/TEST/c

                                                                                                 ontrol01.ctl, /u01/app/oracle/

                                                                                                 fast_recovery_area/TEST/contro

                                                                                                 l02.ctl

Step 4: Create new mount point and directory structure. In my case /u02 is the new  mount point

[oracle@server3 TEST]$ mkdir -p /u02/oradata/TEST

Step 5: Take backup of pfile and spfile.

[oracle@server3 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@server3 dbs]$ mv initTEST.ora initTEST.ora_bkp

[oracle@server3 dbs]$ mv spfileTEST.ora spfileTEST.ora_bkp

Step 6: Shutdown the database

Step 7: Move the control files physically from existing location to new mount point.

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/control01.ctl /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/fast_recovery_area/TEST/control02.ctl /u02/oradata/TEST/

Step 8: Start the database in nomount stage

Step 9: update the new location in control file with below command.

SQL> alter system set control_files='/u02/oradata/TEST/control01.ctl','/u02/oradata/TEST/control02.ctl' scope=spfile;

System altered.

Step 10: Shutdown database.

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

Step 11: Start database in nomount point.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                                2251856 bytes

Variable Size                        163578800 bytes

Database Buffers                50331648 bytes

Redo Buffers                         5169152 bytes

SQL>

Step 12: Check the control file for new location

SQL> show parameter control_files

NAME                                                        TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files                                            string                /u02/oradata/TEST/control01.ct

                                                                                                 l, /u02/oradata/TEST/control02

                                                                                                 .ctl

Step 13: Start database in mount stage and then open it.

SQL> select name,open_mode from v$database;

NAME     OPEN_MODE

--------- --------------------

TEST         READ WRITE

Oracle Database 11.2.0.3 installation in Virtual Box

How to create oracle database 11.2.0.3 in virtual machine.

Copy software to staging area in server. In my case ‘/u02/softwares’ is the staging area.

[oracle@server1 database]$ pwd

/u02/softwares/

[oracle@server1 softwares]$ cd 11203/database/

Before start runInstaller we need to start xserver as root user in host. So that we can get GUI mode to start runInstaller.

[root@server1 softwares]# xhost +

access control disabled, clients can connect from any host

Run runInstaller as oracle user.

Uncheck “I wish to receive security updates via My Oracle Support” box and click Next

Click on “Yes”to go to next window

Select “Skip Software updates” and click Next.

Select “Create and Configure a database” and clict Next

Select “Server Class” and click Next

Select “Single instance database installation” and click Next

Select “Advanced Install” and click Next

Keep default language and click Next.

Select “Enterprise Edition” and click Next

Select location for Oracle Base and click Next. In my case I am using “/u01/app/oracle” is the base.

Keep default value and click Next

Prove “database.localhost” as Global database name and click Next

Provide memory size as per your requirement under “Memory” tab.

Select “AL32UTF8” as the character set under “Character Sets” tab.

Check “Create database with sample schemas” if required. We get SCOTT, HR schemas if you select this option. Click Next

Keep default value and click Next

Provide location for “C, R, D” files and click Next. I my case I have used “/u02/oradata”

Keep default value and click Next

Provide password for sys, system schemas and click Next

Keep default group and click Next

Click on “Fix & Check again. It will give script name to run as root user. Return to Installer and click OK.

Run “runfixup.sh” script as root user.

Click on Install

Click on OK.

It prompts us to run root.sh script as root user. It will update ORACLE_HOME location and database name in “/etc/oratab”

Return to Installer and click on close.

Set the environment and connect to database.

Check the listener status.