Moving A datafile to ASM Disk

SQL> create tablespace t1 datafile ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’
2 ;
create tablespace t1 datafile ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’
*
ERROR at line 1:
ORA-01119: error in creating database file
‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’
ORA-17610: file ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ does not exist and
no size specified
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> create tablespace t1 datafile ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ size 10M;

Tablespace created.

SQL> alter tablespace t1 offline;

Tablespace altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

[oracle@kmc ~]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 – Production on Sat Dec 4 14:49:11 2010

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

connected to target database: TEST (DBID=2029749318)
using target database control file instead of recovery catalog

RMAN> copy datafile ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ to ‘+DATA/TEST/DATAFILE/’
2> ;

Starting backup at 04-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=138 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=130 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=137 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/oracle/product/10.2.0/db_1/oradata/t1.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/04/2010 14:51:43
ORA-19504: failed to create file “+DATA/test/datafile/”
ORA-17502: ksfdcre:3 Failed to create file +DATA/test/datafile/
ORA-15113: alias name ‘DATAFILE’ refers to a directory
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
channel ORA_DISK_2: starting datafile copy
input datafile fno=00006 name=/oracle/product/10.2.0/db_1/oradata/t1.dbf
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 12/04/2010 14:51:45
ORA-19504: failed to create file “+DATA/test/datafile/”
ORA-17502: ksfdcre:3 Failed to create file +DATA/test/datafile/
ORA-15113: alias name ‘DATAFILE’ refers to a directory
channel ORA_DISK_2 disabled, job failed on it will be run on another channel
channel ORA_DISK_3: starting datafile copy
input datafile fno=00006 name=/oracle/product/10.2.0/db_1/oradata/t1.dbf
RMAN-03009: failure of backup command on ORA_DISK_3 channel at 12/04/2010 14:51:47
ORA-19504: failed to create file “+DATA/test/datafile/”
ORA-17502: ksfdcre:3 Failed to create file +DATA/test/datafile/
ORA-15113: alias name ‘DATAFILE’ refers to a directory
channel ORA_DISK_3 disabled, job failed on it will be run on another channel
channel ORA_DISK_4: starting datafile copy
input datafile fno=00006 name=/oracle/product/10.2.0/db_1/oradata/t1.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 12/04/2010 14:51:48
ORA-19504: failed to create file “+DATA/test/datafile/”
ORA-17502: ksfdcre:3 Failed to create file +DATA/test/datafile/
ORA-15113: alias name ‘DATAFILE’ refers to a directory

RMAN>

[oracle@kmc ~]$ asmcmd
ASMCMD> ls
DATA/
FLASH/
ASMCMD> cd DATA/
ASMCMD> ls
MANGOBOY/
TEST/
ASMCMD> cd TEST/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd DATAFILE/
ASMCMD> ls
ISELF_DATA.268.736788471
SYSAUX.259.735570169
SYSTEM.258.735570169
UNDOTBS1.257.735570169
USERS.260.735570171
ASMCMD> pwd
+DATA/TEST/DATAFILE
ASMCMD>

ASMCMD> ls
ISELF_DATA.268.736788471
SYSAUX.259.735570169
SYSTEM.258.735570169
UNDOTBS1.257.735570169
USERS.260.735570171
ASMCMD> pwd
+DATA/TEST/DATAFILE
ASMCMD> ls
ISELF_DATA.268.736788471
SYSAUX.259.735570169
SYSTEM.258.735570169
T1.269.736874161
UNDOTBS1.257.735570169
USERS.260.735570171
t1.dbf
ASMCMD> pwd
+DATA/TEST/DATAFILE
ASMCMD>

copy datafile ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ to ‘+DATA/TEST/DATAFILE/t1.dbf’;

Starting backup at 04-DEC-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/oracle/product/10.2.0/db_1/oradata/t1.dbf
output filename=+DATA/test/datafile/t1.dbf tag=TAG20101204T151544 recid=13 stamp=736874168
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 04-DEC-10

Starting Control File and SPFILE Autobackup at 04-DEC-10
piece handle=+FLASH/test/autobackup/2010_12_04/s_736874186.280.736874197 comment=NONE
Finished Control File and SPFILE Autobackup at 04-DEC-10

RMAN> exit

Recovery Manager complete.

[oracle@kmc ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Dec 4 15:17:43 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace t1 online;

Tablespace altered.

SQL> desc dba_table_spaces;
ERROR:
ORA-04043: object dba_table_spaces does not exist

SQL> desc dba_table_space
ERROR:
ORA-04043: object dba_table_space does not exist

SQL> desc dba_tablespace
ERROR:
ORA-04043: object dba_tablespace does not exist

SQL> desc dba_tablespaces;
Name Null? Type
—————————————– ——– —————————-
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)

SQL> desc v$datafile;
Name Null? Type
—————————————– ——– —————————-
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE

SQL> select name from v$datafile;

NAME
——————————————————————————–
+DATA/test/datafile/system.258.735570169
+DATA/test/datafile/undotbs1.257.735570169
+DATA/test/datafile/sysaux.259.735570169
+DATA/test/datafile/users.260.735570171
+DATA/test/datafile/iself_data.268.736788471
/oracle/product/10.2.0/db_1/oradata/t1.dbf

6 rows selected.

SQL> alter database rename file ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ to ‘+DATA/TEST/DATAFILE/t1.dbf’;
alter database rename file ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ to ‘+DATA/TEST/DATAFILE/t1.dbf’
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 6 – file is in use or recovery
ORA-01110: data file 6: ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’

SQL> alter tablespace t1 offline;

Tablespace altered.

SQL> alter database rename file ‘/oracle/product/10.2.0/db_1/oradata/t1.dbf’ to ‘+DATA/TEST/DATAFILE/t1.dbf’;

Database altered.

SQL> alter tablespace t1 offline;
alter tablespace t1 offline
*
ERROR at line 1:
ORA-01539: tablespace ‘T1’ is not online

SQL> alter tablespace t1 online;
alter tablespace t1 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: ‘+DATA/test/datafile/t1.dbf’

SQL> restore datafile 6;
SP2-0734: unknown command beginning “restore da…” – rest of line ignored.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@kmc ~]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 – Production on Sat Dec 4 15:27:35 2010

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

connected to target database: TEST (DBID=2029749318)
using target database control file instead of recovery catalog

RMAN> restore datafile 6;

Starting restore at 04-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=147 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=159 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=134 devtype=DISK

datafile 6 is already restored to file +DATA/test/datafile/t1.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 04-DEC-10

RMAN>

Recovery Manager complete.
[oracle@kmc ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Dec 4 15:28:27 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> recover datafile 6
Media recovery complete.
SQL> alter tablespace t1 online;

Tablespace altered.

SQL> select name from v$datafile;

NAME
——————————————————————————–
+DATA/test/datafile/system.258.735570169
+DATA/test/datafile/undotbs1.257.735570169
+DATA/test/datafile/sysaux.259.735570169
+DATA/test/datafile/users.260.735570171
+DATA/test/datafile/iself_data.268.736788471
+DATA/test/datafile/t1.dbf

6 rows selected.

SQL>

Leave a comment
Comments 0

Sayantan Chakraborty'S Blog

Oracle DBA Daly work

Moving A datafile to ASM Disk

Leave a comment

Comments 0

Leave a comment Cancel reply

Categories

Meta

Sayantan Chakraborty'S Blog

Oracle DBA Daly work

Moving A datafile to ASM Disk

Share this:

Leave a comment

Comments 0

Leave a comment Cancel reply

Categories

Meta