Rename/Move datafile - Tested in our Test ENV

-
Tested in our Test ENV

 Example: Rename the datafile

(Here i have test by creating the new tablespace with datafile / We can use it for existing datafile to Rename/Move)

SQL> create tablespace newrename datafile '/u01/app/oracle/oradata/prim/newrename.dbf' size 10m;
Tablespace created.

SQL> select name from V$datafile;
NAME

--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/PAP_DATA.dbf
/u01/app/renametest/renametest.dbf
/u01/app/oracle/oradata/prim/newrename.dbf

 Make the Tablespace Offline:

SQL> alter tablespace newrename offline;
Tablespace altered.

 Rename the File by OS Level (Safer side copy the datafile then rename by using the mv command)

$ cp newrename.dbf newrename.dbf.bck

$ mv newrename.dbf to nrename.dbf

 Rename by Sql command:

SQL> alter tablespace newrename rename datafile '/u01/app/oracle/oradata/prim/newrename.dbf' to '/u01/app/oracle/oradata/prim/nrename.dbf';
Tablespace altered.

 Make the tablespace online

SQL> alter tablespace NEWRENAME online;
Tablespace altered.
Verify:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/PAP_DATA.dbf
/u01/app/renametest/renametest.dbf
/u01/app/oracle/oradata/prim/nrename.dbf

Hence it has been rename into the same location of the datafile

 Move the datafile into different location:

 Create on tablespace with datafile or you can use the existing datafile to perform the operation

Create tablespace renametest datafile ‘/u01/app/oracle/oradata/prim/renametest.dbf’ size 10m;

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

 Make the Tablespace Offline:

SQL> alter tablespace renametest offline;
Tablespace altered.

 Move/rename the File by OS Level (Safer side copy the datafile then rename by using the mv command)

$ cp renametest.dbf renametest.dbf.bck

$ mv renametest.dbf /u01/app/renametest/

$ cd /u01/app/renametest/

[oracle@oracledbtest renametest]$ ls

renametest.dbf

 Rename by Sql command:

SQL> alter tablespace renametest rename datafile '//u01/app/oracle/oradata/prim/renametest.dbf' to '/u01/app/renametest/renametest.dbf';
Tablespace altered.

 Make the tablespace online

SQL> alter tablespace renametest online;
Tablespace altered.

 Verify:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/PAP_DATA.dbf
/u01/app/renametest/renametest.dbf    - Move the datafile into the different location

Hence we move the datafile into the different location

Comments

Post a Comment

Popular posts from this blog

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

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                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All
Read more

Solutions: error while starting of Oracle Single instance ASM Database

-
1 st – Starting ASM [root@asmdb ~]#su - grid [grid@asmdb ~]$ asmcmd Connected to an idle instance ASMCMD> ls ASMCMD-08102: no connection to ASM; command requires ASM to run ASMCMD> [grid@asmdb ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:44:30 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 Automatic Storage Management option SQL> conn / as sysasm Connected to an idle instance SQL> startup ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service SQL> exit Disconnected 2nd : Starting DB [oracle@asmdb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:15:15 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Gl
Read more