Sunday, October 25, 2009

MOVING DATAFILE IN ASM BY ASMCMD(cp)

After I wrote about Moving Datafile(system) from one ASM Diskgroup to Another, From example I used RMAN to move datafile. I read ocpdba's comment and curious Moving Datafile in ASM by asmcmd(cp). then start to test..
SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DISK01
DISK02
$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production
connected to target database: ORCL (DBID=1224745511)
RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK02/orcl/datafile/users.336.701196307
This test, I want to move datafile from +DISK02 to +DISK01
.. Bring datafile offline
RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

sql statement: ALTER DATABASE DATAFILE 4 OFFLINE
After bring DATAFILE offline... then copy datafile by asmcmd
$ asmcmd -p
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users.336.701196307
ASMCMD-08016: copy source->'+DISK02/orcl/datafile/users.336.701196307' and target->'+DISK01/orcl/datafile/users.336.701196307' failed
ORA-19505: failed to identify file "+DISK01/orcl/datafile/users.336.701196307"
ORA-17502: ksfdcre:4 Failed to create file +DISK01/orcl/datafile/users.336.701196307
ORA-15046: ASM file name '+DISK01/orcl/datafile/users.336.701196307' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
Can't copy, so copy to new file name
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf


ASMCMD [+] > ls -la +DISK02/orcl/datafile/users.336.701196307
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 25 17:00:00 Y none => users.336.701196307
ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411
After copied datafile... then rename file in database
SQL> ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf';

Database altered.
Check in alert log...
Sun Oct 25 17:24:55 2009
ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
Deleted Oracle managed file +DISK02/orcl/datafile/users.336.701196307
Completed: ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'

"ALTER DATABASE RENAME FILE" with ASM(OMF), that delete old datafile.

After renamed datafile in Database, then check + recover + bring datafile online.
RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf
RMAN> RECOVER DATAFILE 4;

Starting recover at 25-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 instance=orcl1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 25-OCT-09
RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

sql statement: ALTER DATABASE DATAFILE 4 ONLINE
RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbf
That's a good way... but I still interested about file (name, type) in ASM, after copied ;)

2 comments:

Ittichai Chammavanijakul said...

Khun Surachart,

I was a little frustrated with the way asmcmd cp working too when working with the TTS on the ASM (http://oraexplorer.com/2009/03/asmcmd-cp-command-on-the-test-with-transportable-tablespace/). Based on Oracle document, the regular target filename has to be specified when using asmcmd cp, and Oracle will automatically append the file/incarnation pair to ensure uniqueness.

So when

ASMCMD> cp /tmp/filename.314.680796267 +DEV_DG1/ora11dv/datafile/filename

will work - The new target file name will be like +DEV_DG1/ora11dv/datafile/filename.271.680796929.


But the following will "not" work

[specifying only the target directory]

ASMCMD> cp /tmp/filename.314.680796267 +DEV_DG1/ora11dv/datafile/

or

[specifying a filename with file/incarnation pair]

ASMCMD> cp /tmp/filename.314.680796267 +DEV_DG1/ora11dv/datafile/filename.314.680796267

This is very inconvenient because above is a common way of Unix OS cp. Hopefully it will be fixed in the later release. :-)

Ittichai

Surachart Opun said...

thank you so much. I feel frustrated with asmcmd too. But I curious after u copied...

ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf

I checked file ...

ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411

ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411