How to resolve datafile location error when omitted "+" when adding datafile in a tablespace in +DG_DATA diskgroup and it mistakenly added it to the filesystem.

 Problem: Omitted "+" when adding datafile in a tablespace in +DG_DATA diskgroup and it mistakenly added it to the filesystem.

below is the command that created the problem:

alter tablespace SYSAUX add datafile 'DG_DATA' size 500m autoextend on next 5G maxsize 30G;

select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';

system@ORA01 > select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';


   FILE_ID FILE_NAME                                                                   BYTES ONLINE_STA

---------- ---------------------------------------------------------------------- ---------- ----------

         2 +DG_ORA/ora01/ora_sysaux                                               1.0737E+10 ONLINE

        33 +DG_ORA/ora01/ora_sysaux2.dbf                                          1.0737E+10 ONLINE

        34 +DG_ORA/ora01/ora_sysaux3.dbf                                          3221225472 ONLINE

        35 +DG_ORA/ora01/ora_sysaux4.dbf                                          3221225472 ONLINE

        36 +DG_ORA/ora01/ora_sysaux5.dbf                                          3221225472 ONLINE

        37 +DG_ORA/ora01/ora_sysaux6.dbf                                          3221225472 ONLINE

        38 +DG_ORA/ora01/ora_sysaux7.dbf                                          3221225472 ONLINE

        39 +DG_ORA/ora01/ora_sysaux1.dbf                                          5368709120 ONLINE

        40 /oracle/app/product/11.2.0.4/db/dbs/DG_DATA                               2097152 ONLINE

        41 +DG_ORA/ora01/ora_sysaux8.dbf                                          5368709120 ONLINE


10 rows selected.


1.  Find out the exact file name, file location, size and file number: 

select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';

   FILE_ID FILE_NAME                                                                   BYTES ONLINE_

---------- ---------------------------------------------------------------------- ---------- -------

         2 +DG_ORA/ora01/ora_sysaux                                               1.0737E+10 ONLINE

        33 +DG_ORA/ora01/ora_sysaux2.dbf                                          1.0737E+10 ONLINE

        34 +DG_ORA/ora01/ora_sysaux3.dbf                                          3221225472 ONLINE

        35 +DG_ORA/ora01/ora_sysaux4.dbf                                          3221225472 ONLINE

        36 +DG_ORA/ora01/ora_sysaux5.dbf                                          3221225472 ONLINE

        37 +DG_ORA/ora01/ora_sysaux6.dbf                                          3221225472 ONLINE

        38 +DG_ORA/ora01/ora_sysaux7.dbf                                          3221225472 ONLINE

        39 +DG_ORA/ora01/ora_sysaux1.dbf                                          5368709120 ONLINE

        40 /oracle/app/product/11.2.0.4/db/dbs/DG_DATA                               2097152 ONLINE

        41 +DG_ORA/ora01/ora_sysaux8.dbf                                          5368709120 ONLINE


10 rows selected.


select file_id, file_name, online_status from dba_data_files where file_id=40;


   FILE_ID FILE_NAME                                                              ONLINE_

---------- ---------------------------------------------------------------------- -------

        40 /oracle/app/product/11.2.0.4/db/dbs/DG_DATA                            ONLINE


2. Put the datafile offline


SQL> alter database datafile 40 offline;


Database altered.


SQL>


3. Recreate the datafile on the shared storage, please note that you need to do this on the node where the physical file resides 

and you need to specify the size retrieved in step 1


SQL> alter database create datafile '/oracle/app/product/11.2.0.4/db/dbs/DG_DATA' as '+DG_DATA' size 2097152;


encounter this error:


SQL> alter database create datafile '/oracle/app/product/11.2.0.4/db/dbs/DG_DATA' as '+DG_DATA' size 2097152;

alter database create datafile '/oracle/app/product/11.2.0.4/db/dbs/DG_DATA' as '+DG_DATA' size 2097152

*

ERROR at line 1:

ORA-01136: specified size of file 40 (256 blocks) is less than original size of 64000 blocks

ORA-01110: data file 40: '/oracle/app/product/11.2.0.4/db/dbs/DG_DATA'

SQL>

NB: if you encounter above error, just increase the size small, in this case, the size was 2mb and I increased it to 500m and it worked fine.

this work:

SQL> alter database create datafile '/oracle/app/product/11.2.0.4/db/dbs/DG_DATA' as '+DG_DATA' size 500m;

Database altered.

SQL>


4. Recover the datafile


SQL> recover datafile 40;

Media recovery complete.

SQL>


5. Place the datafile back online


SQL> alter database datafile 40 online;


Database altered.

SQL>


Note: In some specific or particular case the end-user might copy the physical file by mistake again to another physical location on the same or another node. When perfoming the solution there is no risk in data lost between the different versions of the physical files because the 'alter database create datafile ',  always recovers starting from the online redolog that was current at the moment the datafile was created originally, so it doesn't matter what happend with the datafile in between as long as we have all the archive logs since the datafile creation.

 

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = 'SYSAUX';


   FILE_ID FILE_NAME                                                                   BYTES ONLINE_

---------- ---------------------------------------------------------------------- ---------- -------

         2 +DG_ORA/ora01/ora_sysaux                                               1.0737E+10 ONLINE

        33 +DG_ORA/ora01/ora_sysaux2.dbf                                          1.0737E+10 ONLINE

        34 +DG_ORA/ora01/ora_sysaux3.dbf                                          3221225472 ONLINE

        35 +DG_ORA/ora01/ora_sysaux4.dbf                                          3221225472 ONLINE

        36 +DG_ORA/ora01/ora_sysaux5.dbf                                          3221225472 ONLINE

        37 +DG_ORA/ora01/ora_sysaux6.dbf                                          3221225472 ONLINE

        38 +DG_ORA/ora01/ora_sysaux7.dbf                                          3221225472 ONLINE

        39 +DG_ORA/ora01/ora_sysaux1.dbf                                          5368709120 ONLINE

        40 +DG_DATA/ora01/datafile/sysaux.277.1125845945                             2097152 ONLINE

        41 +DG_ORA/ora01/ora_sysaux8.dbf                                          5368709120 ONLINE


10 rows selected.


SQL>


Comments

Post a Comment

Popular posts from this blog

How to resolve - ORA-09925: Unable to create audit trail file

When audit directory adump get filled, it prevents application from further connection and throw below error : ERROR: ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 28: No space left on device Additional information: 9925 ORA-02002: error while writing to audit trail ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 28: No space left on device Additional information: 9925 At this stage, as a DBA, you need to urgently resolve the issue because everyone will be calling that they cannot access the database. The first thing to do is to check if the file syste is filled up which might not show you 100% because is only the size defined for audit location is reached when you run df -h. However, the inode of the filesystem has reached 100% and that is why it cannot create further login audit files and so could not allow anyone login to the database until the space is freed. To find out if the inode is filled, run df -ih command on the linux syste
Read more
How to resolve error ORA-15032,ORA-15031,ORA-15025,ORA-15056 when adding a disk to asm. SQL> ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51'; ALTER DISKGROUP ASM_FRA ADD DISK '/dev/dm-51' * ERROR at line 1: ORA-15032: not all alterations performed ORA-15031: disk specification '/dev/dm-51' matches no disks ORA-15025: could not open disk "/dev/dm-51" ORA-15056: additional error message Linux-x86_64 Error: 13: Permission denied Additional information: 42 Additional information: 1927546192 I wanted to add  the following disk: (/dev/dm-51,/dev/dm-52,/dev/dm-53) First check the permissions on this files after the Unix admin has provision it. $ls -lrt /dev/dm* brw-rw---- 1 root disk 253, 19 Jan 31 13:46 /dev/dm-19 brw-rw---- 1 root disk 253, 53 Mar 2 21:04 /dev/dm-53 brw-rw---- 1 root disk 253, 52 Mar 2 21:04 /dev/dm-52 brw-rw---- 1 root disk 253, 51 Mar 2 21:04 /dev/dm-51 brw-rw---- 1 root disk 253, 46 Mar 2 21:12 /dev/dm-46 brw-r
Read more

How to repair a disk in a diskgroup

SUMMARY ======== "DG01" diskgroup cannot be mounted due to one disk member is missing: ====================================================== SQL> ALTER DISKGROUP DG01 MOUNT /* asm agent *//* {1:16801:2} */ NOTE: cache registered group DG01 number=1 incarn=0x2929b317 NOTE: cache began mount (first) of group DG01 number=1 incarn=0x2929b317 NOTE: Assigning number (1,1) to disk (/dev/asm-disk5) Sat Feb 14 06:41:49 2015 ERROR: no read quorum in group: required 1, found 0 disks NOTE: cache dismounting (clean) group 1/0x2929B317 (DG01) NOTE: messaging CKPT to quiesce pins Unix process pid: 7194, image: oracle@SLSLMEDIDBOP01 (TNS V1-V3) NOTE: dbwr not being msg'd to dismount NOTE: lgwr not being msg'd to dismount NOTE: cache dismounted group 1/0x2929B317 (DG01) NOTE: cache ending mount (fail) of group DG01 number=1 incarn=0x2929b317 NOTE: cache deleting context for group DG01 1/0x2929b317 GMON dismounting group 1 at 18 for pid 29, osid 7194 NOTE: Dis
Read more