jump to navigation

Move datafile to a different ASM diskgroup using RMAN 23 July 2010

Posted by David Alejo Marcos in ASM, Oracle 11.2, RMAN.
Tags: , ,
trackback

Yesterday we received an email from one of the developers with the following error:

ORA-00376: file 68 cannot be read at this time
ORA-01110: data file 68: ‘+DATA/xxx/datafile/yyy.366.724161769’

This error came to me for investigation and a fix as soon as possible as the database is critical for testing.

Earlier during the day, we did a SnapClone from a local standby running on the same server, but this should not have any impact on this other database.

As we are using SAN storage, I asked the SAN Manager and the SA to see if there was a chance something might have gone wrong.

They say no.

The problem:

ORA-00376: file 68 cannot be read at this time
ORA-01110: data file 68: ‘+DATA/xxx/datafile/yyy.366.724161769’

The status of the datafile on dba_data_files was AVAILABLE, but the online_status from v$recover_file was OFFLINE.

The solution:

The first thing I did was to check the alert.log file for any anomaly. I found the following entry:

Sat Jul 10 19:55:58 2010
ORA-1683: unable to extend index yyyy.FC_CDS_CNTR_CRV_ISCLCLTD_IDX partition FC_CDS_CNTR_CRV_FEB10 by 128 in tablespace yyy
......
ORA-1653: unable to extend table yyy.FC_JOB_STATUS by 128 in tablespace yyy
alter tablespace yyy add datafile '+DATA' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
Completed: alter tablespace yyy add datafile '+DATA' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
alter tablespace yyy add datafile size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
Completed: alter tablespace yyy add datafile size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
undo_tablespace = UNDOTBS1
Successfully onlined Undo Tablespace 1.
undo_tablespace = UNDOTBS1
Successfully onlined Undo Tablespace 1.
[oracle@ukwhqaorc004 bdump]$ vi alert_xxx.log
Mon Jul 12 11:55:37 2010
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
Mon Jul 12 12:02:47 2010
alter tablespace yyy add datafile '+DATA' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
Mon Jul 12 12:02:47 2010
SUCCESS: diskgroup DATA was mounted
Mon Jul 12 12:03:49 2010
SUCCESS: diskgroup DATA was dismounted
Mon Jul 12 12:03:49 2010
SUCCESS: diskgroup DATA was mounted
SUCCESS: diskgroup DATA was dismounted
SUCCESS: diskgroup DATA was mounted
Mon Jul 12 12:03:49 2010
Completed: alter tablespace yyy add datafile '+DATA' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited
Mon Jul 12 12:11:11 2010

and earlier on the day:
Thu Jul 22 09:05:08 2010
Errors in file /u01/app/oracle/admin/whqaint/bdump/xxx_ckpt_522.trc:
ORA-01110: data file 68: '+DATA/xxx/datafile/yyy.366.724161769'
ORA-01115: IO error reading block from file 68 (block # 1)
ORA-15078: ASM diskgroup was forcibly dismounted
Thu Jul 22 09:05:23 2010
Errors in file /u01/app/oracle/admin/xxx/bdump/xxx_ckpt_522.trc:
ORA-01110: data file 68: '+DATA/xxx/datafile/yyy.366.724161769'
ORA-01115: IO error reading block from file 68 (block # 1)
ORA-15078: ASM diskgroup was forcibly dismounted
Thu Jul 22 09:05:38 2010
Errors in file /u01/app/oracle/admin/xxx/bdump/xxx_ckpt_522.trc:
ORA-01171: datafile 68 going offline due to error advancing checkpoint
ORA-01110: data file 68: '+DATA/xxx/datafile/xxx.366.724161769'
ORA-01115: IO error reading block from file 68 (block # 1)
ORA-15078: ASM diskgroup was forcibly dismounted

The key line is ORA-15078: ASM diskgroup was forcibly dismounted

As we are using ASM, the first step on our SnapClone procedure is to dismount several diskgroups, but technically, this operation should not impact the other database as both databases should use different diskgroups.

When the development DBA created the datafile to extend the tablespace, she did it on the wrong diskgroup (DATA) instead of (DATB). It is an easy mistake as the name is similar.

Now that we know the reason, it is fairly simple to fix it. The steps I followed are:
1.- Check the assumption is correct (datafile was created on a different diskgroup):
SQL> select file_name, status
from dba_data_files
where tablespace_name = 'xxx';
FILE_NAME STATUS
------------------------------------------------ ---------
+DATB/xxx/datafile/yyy.339.718314941 AVAILABLE
+DATB/xxx/datafile/yyy.358.718303181 AVAILABLE
+DATB/xxx/datafile/yyy.338.718314943 AVAILABLE
+DATB/xxx/datafile/yyy.309.718325181 AVAILABLE
+DATA/xxx/datafile/yyy.366.724161769 AVAILABLE
+DATB/xxx/datafile/yyy.281.724238729 AVAILABLE

2.- Change the status of the datafile to offline:
SQL> alter database datafile '+DATA/xxx/datafile/yyy.366.724161769' offline;

3.- Copy the datafile using RMAN:
[oracle@ssssssssssssss bdump]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jul 22 15:52:52 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: xxx (DBID=4255406167)
RMAN> copy datafile '+DATA/xxx/datafile/yyy.366.724161769' to '+DATB';

during the output of the command you can see the source and the destination of the datafile:
Starting backup at 22-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=403 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00068 name=+DATA/xxx/datafile/yyy.366.724161769
output filename=+DATB/xxx/datafile/yyy.280.725039609
tag=TAG20100722T155329 recid=1 stamp=725039763
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
Finished backup at 22-JUL-10

4.- Update the Oracle data dictionary with the new information:
SQL> alter database rename
file '+DATA/xxx/datafile/yyy.366.724161769'
to '+DATB/xxx/datafile/yyy.280.725039609';

Once the datafile has been renamed in the data dictionary, Oracle removes the old copy of the file in ASM

5.- Rename the ASM datafile copy using RMAN.
[oracle@ssssssssssssss bdump]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jul 22 15:58:31 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: xxx (DBID=4255406167)
RMAN> switch datafile '+DATB/xxxx/datafile/yyy.280.725039609' to copy;

The output should be similar to:

using target database control file instead of recovery catalog
datafile 68 switched to datafile copy "+DATB/xxx/datafile/yyy.280.725039609"

6.- Perform media recovery of the datafile:
RMAN> recover datafile '+DATB/xxx/datafile/yyy.280.725039609';

7.- Make the datafile available by changing the status to online:
SQL> alter database datafile '+DATB/xxx/datafile/yyy.280.725039609' online;

8.- Check the status and location of the datafile:
SQL> select file_name, status from dba_data_files where tablespace_name = 'yyy';
FILE_NAME STATUS
------------------------------------------------ ---------
+DATB/xxx/datafile/yyy.339.718314941 AVAILABLE
+DATB/xxx/datafile/yyy.358.718303181 AVAILABLE
+DATB/xxx/datafile/yyy.338.718314943 AVAILABLE
+DATB/xxx/datafile/yyy.309.718325181 AVAILABLE
+DATB/xxx/datafile/yyy.280.725039609 AVAILABLE
+DATB/xxx/datafile/yyy.281.724238729 AVAILABLE

and job done.

As always, comments are welcome.

Advertisements

Comments»

1. www.rmanbackup.com - 4 October 2010

Thank you so much for this precious informations.

2. G Moreno - 11 November 2010

Would you avoid the the need for media recovery if you put the tablespace into read-only mode first? I’m thinking of the case where the database is in non-archivelog mode.

David Alejo Marcos - 22 November 2010

Morning,

I guess it is possible, but we were moving a single datafile.

This particular tablespace has around 15 datafiles. Modifying the status of the tablespace to read only will have an impact on the application, while changing the status of a single datafile to offline for the duration of the operation was transparent to users.

thank you,

David.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: