Move the datafiles (System, SYSAUX, UNDOTBS, TEMP, REDO LOGFILES) into the new location - Tested & Verified

-
Move the datafiles (System, SYSAUX, UNDOTBS, TEMP, REDO LOGFILES) into the new location:

USERS TBS

 Note:

It does not require the database to be bonce or in mount stage for USERS tablespace
Get the datafile location exist

SQL> select name from v$datafile;     (Old Location of al the datafiles)
NAME
------------------------------------------------
/u01/app/oracle/oradata/oggtest2/system01.dbf
/u01/app/oracle/oradata/oggtest2/sysaux01.dbf
/u01/app/oracle/oradata/oggtest2/undotbs01.dbf
/u01/app/oracle/oradata/oggtest2/users01.dbf
/u01/app/oracle/oradata/oggtest2/example01.dbf

 Moving Normal Data Files

To move the datafile associated with the USERS tablespace, fallow the below steps:
-         Make the users tablespace offline:

SQL> alter tablespace users offline;
Tablespace altered.

SQL> exit

Now move or copy  the tablespace users into the new location

$ mv /u01/app/oracle/oradata/oggtest2/users01.dbf  /oradata/datafiles/

$ !sq

SQL> alter tablespace USERS rename datafile ‘'/u01/app/oracle/oradata/oggtest2/users01.dbf ‘ to '/oradata/datafiles/users01.dbf';
Tablespace altered.

SQL>alter tablespace USERS online;
Tablespace altered

 Verify the new location:

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oggtest2/system01.dbf
/u01/app/oracle/oradata/oggtest2/sysaux01.dbf
/u01/app/oracle/oradata/oggtest2/undotbs01.dbf
/oradata/datafiles/users01.dbf                                    ----- New location transfer
/u01/app/oracle/oradata/oggtest2/example01.dbf

Others Moving System, SYSAUX, UNDOTBS, TEMP, REDO LOGFILES
 --- To move system, temporary or rollback tablespaces, the database should be shut down and brought back up into a mount state.  This allows the control file to be updated while the datafiles are not in use.

 SYSTEM

Shutdown the database & startup in mount mode

 SQL> shu immediate

 SQL> startup mount

 [oracle@ogg-test2 ~]$ cd /u01/app/oracle/oradata/oggtest2/

 [oracle@ogg-test2 oggtest2]$ ls

control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf

 [oracle@ogg-test2 oggtest2]$ mv system01.dbf /oradata/datafiles/

SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/system01.dbf' to '/oradata/datafiles/system01.dbf';
Database altered.

SQL> alter database open;
Database altered.

 SQL> select status from v$instance;
STATUS
------------
OPEN

 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/datafiles/system01.dbf
/u01/app/oracle/oradata/oggtest2/sysaux01.dbf
/u01/app/oracle/oradata/oggtest2/undotbs01.dbf
/oradata/datafiles/users01.dbf
/u01/app/oracle/oradata/oggtest2/example01.dbf

 SYSAUX

 SQL> startup mount

 [oracle@ogg-test2 oggtest2]$ mv sysaux01.dbf /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ !sq

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/sysaux01.dbf' to '/oradata/datafiles/sysaux01.dbf';
Database altered.

 SQL> alter database open;
Database altered.

 Verify

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/datafiles/system01.dbf
/oradata/datafiles/sysaux01.dbf
/u01/app/oracle/oradata/oggtest2/undotbs01.dbf
/oradata/datafiles/users01.dbf
/u01/app/oracle/oradata/oggtest2/example01.dbf

 UNDO

 SQL> startup mount

 [oracle@ogg-test2 oggtest2]$ mv undotbs01.dbf /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ !sq

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/undotbs01.dbf' to 
'/oradata/datafiles/undotbs01.dbf';
Database altered.

 SQL> alter database open;
Database altered.

 Verify

 SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/datafiles/system01.dbf
/oradata/datafiles/sysaux01.dbf
/oradata/datafiles/undotbs01.dbf
/oradata/datafiles/users01.dbf
/u01/app/oracle/oradata/oggtest2/example01.dbf

 EXAMPLE

 SQL> startup mount

 [oracle@ogg-test2 oggtest2]$ mv example01.dbf /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ !sq

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/example01.dbf' to '/oradata/datafiles/example01.dbf';
Database altered.

 SQL> alter database open;
Database altered.

 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/datafiles/system01.dbf
/oradata/datafiles/sysaux01.dbf
/oradata/datafiles/undotbs01.dbf
/oradata/datafiles/users01.dbf
/oradata/datafiles/example01.dbf

 TEMP

 SQL> startup mount

[oracle@ogg-test2 oggtest2]$ mv temp01.dbf /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ !sq

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/temp01.dbf' to '/oradata/datafiles/temp01.dbf';
Database altered.

 SQL> alter database open;
Database altered.

 Verify:
SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1           925785 20-JUN-17          3          1 ONLINE  READ WRITE
  30408704       3712     20971520       8192
/oradata/datafiles/temp01.dbf

 REDOLOG FILES

 Exiting:

 SQL> select member from v$logfile;

MEMBER
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/oggtest2/redo03.log
/u01/app/oracle/oradata/oggtest2/redo02.log
/u01/app/oracle/oradata/oggtest2/redo01.log

 Change to the new location

 [oracle@ogg-test2 oggtest2]$ mv redo01.log /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ mv redo02.log /oradata/datafiles/

 [oracle@ogg-test2 oggtest2]$ mv redo03.log /oradata/datafiles/

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/redo01.log' to '/oradata/datafiles/redo01.log';
Database altered.

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/redo02.log' to '/oradata/datafiles/redo02.log';
Database altered.

 SQL> alter database rename file '/u01/app/oracle/oradata/oggtest2/redo03.log' to '/oradata/datafiles/redo03.log';
Database altered.

 SQL> alter database open;
Database altered.

 Verify:

 SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/datafiles/redo03.log
/oradata/datafiles/redo02.log
/oradata/datafiles/redo01.log

Hence change the location of the existing datafile location into the new location



Comments

Post a Comment

Popular posts from this blog

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

-
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2   ...
Read more

Introduction of Mir - Oracle DBA - Oracle Certified Professional(OCP) - 9i,10g & 11g

-
Image
                                                                 Mir Sayeed Hassan                                          A self-motivated and result oriented Professional with around 7+ years of experience in Oracle DBA at IT Industry & Oracle Certified Professional(OCP)  9i,10g & 11g , which includes Oracle Database Administration, Oracle Golden Gate(OGG) & Corporate Trainer for Oracle DBA, Banking & Information Technology to align Bank’s IT strategy with business objective...
Read more

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

-
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' Error occur while restoring the database from the backup of RMAN RMAN> alter database open resetlogs; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/03/2017 16:05:14 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' To resolve this error fallow the below procedure – Hopefully you will succeed If you’re primary database is flashback & it has the standby – Fallow below procedure RMAN> run { 2> recover database; 3> } ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter datab...
Read more