14 Replies Latest reply: Jan 8, 2013 9:35 AM by Daryl E. RSS

    Unable to resize asm datafile even though I resized the (logical) datafile

    Daryl E.
      I have a bigfile that went above 16tb - this is causing me grief in a restore to a netapp filer that has a 16tb limit.

      So we went thru the hassles of moving data around. I issued the

      Mon Dec 10 21:15:06 2012
      alter database datafile '+DATA/pcinf/datafile/users1.303.777062961' resize 15900000000000
      Completed: alter database datafile '+DATA/pcinf/datafile/users1.303.777062961' resize 15900000000000
      Mon Dec 10 21:40:10 2012

      The datafile itself from v$datafile shows 15tb - BUT the asm file is still 18tb in size.

      Should it not be the same - is this something others have faced where the asm file doesnt match?

      Name     USERS1.303.777062961
      Type     DATAFILE
      Redundancy     MIRROR
      Block Size (Bytes)     8192
      Blocks     2281701377
      Logical Size (KB)     18253611016

      Linux, Exadata, 11.2.0.2 + psu

      SR Created but not getting anywhere - Why such a large file, are you sure its really not 18tb, etc .etc

      Daryl
        • 1. Re: Unable to resize asm datafile even though I resized the (logical) datafile
          onedbguru
          What is your db_blocksize?

          at 8K, the max file size is 32TB. If you are using a 4K, I believe the max would be 16TB. Support should not be questioning why such a big file and asking relevant questions such as the one above.

          If using 4K, I would suggest stopping all processing and moving tables and/or indexes to another tablespace immediately.
          • 2. Re: Unable to resize asm datafile even though I resized the (logical) datafile
            Daryl E.
            Its 8k - so on the Exadata it can easily expand to 32TB (not that i want that per se) - the issue is I cant copy/recreate that file on the filer that has the hard limit of 16tb filesizes.

            The app dba's are trying to repoint data and such to a new tablespace but its "too late" do too much to this tablespace. We did manage to move about 3tb off the high water mark.
            • 3. Re: Unable to resize asm datafile even though I resized the (logical) datafile
              Daryl E.
              Oracle support is still running me in circles here ..

              This seems like a major flaw .. if you are tight on actual disk space in exadata - resizing a datafile will not give back the physical space. The datafile resizes fine but the physical datafile on asm retains its larger size.

              Are others finding this? 11.2.0.3
              • 4. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                Dude!
                The physical file size is typically larger than the logical file size due to something called file slack. Larger cluster sizes mean more file slack and also more waste of storage space. Slack space can exist when a file's size is not a multiple of the file system's cluster size. The Logical Size (KB) 18253611016 reported by ASM means 17.00 TB of logical data. The physical size should be larger. You may need to reduce the datafile further to see the affect on the physical size.
                • 5. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                  Daryl E.
                  6tb logical (as reportined to the db) 18tb physical (as seen on asm) .. seems way out of wack to me.
                  • 6. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                    Daryl E.
                    Here is the example from our db.
                    select file_id, bytes
                     from dba_data_files
                    where file_id = 12
                    
                       FILE_ID                BYTES
                    ---------- --------------------
                            12   15,900,000,002,048
                    1 row selected.
                    
                    
                    col bytes format 999,999,999,999,999 
                    select file#, bytes
                     from v$datafile where file# =12
                         FILE#                BYTES
                    ---------- --------------------
                            12   18,691,697,672,192
                    1 row selected.
                    • 7. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                      Dude!
                      If I'm not mistaken than dba_data_files reads from the data dictionary for which the database needs to be open, whereas v$datafile reads from the controlfile for which it needs to be mounted. I don't know why your data is not in sync, but why should this have anything to do with ASM? I think none of these values show you the physical file size though.
                      • 8. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                        Daryl E.
                        "why should this have anything to do with ASM? " -- only that the datafile is in asm. No idea if this is specific to ASM or not... Netapp has a limit of 16tb files - we found this out the hard way with the standby being created on a filesystem (not asm) .. and now we cant shrink the datafile back to under 16tb.
                        • 9. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                          Dude!
                          Sorry I'm not familiar with Netapp as such, but don't you use Netapp to manage the partitons or devices for ASM? Why should shrinking a database datafile in ASM reduce the the diskgroup or device managed by Netapp?
                          • 10. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                            Daryl E.
                            netapp is the flatfile storage for the standby .. The primary is an exadata with ASM. So the exadata can create files upto 32tb but the standby, being that it runs on netapp with a file system, it is limited to 16tb. Once we hit that limit, we didnt know about it, we tried to resize the asm primary datafile down. It was successfull according to the db but the physical file within asm didnt shrink at all.

                            So the bigger question is .. if a datafile grows and we resize it back - we never get that space back at the disk level? That doesnt seem right at all.
                            • 11. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                              Dude!
                              Does the following apply: https://communities.netapp.com/thread/17723

                              If yes, then Netapp apparently works with the physical data blocks of the volumes or disk groups of ASM. In other words, Netapp does not only copy your oracle datafile, but duplicates all the the data blocks that have changed in the logical volume (a file in Netapp). When you shrink a database datafile, the free space will become changed data blocks too and as such will need to be duplicated. Is this the way it works? If you might want to reconsider the concept, because a live database copied this way will need recovery.
                              • 12. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                                Daryl E.
                                Netapp shouldnt really be in the picture as the problem is on the Exadata/ASM side .. the datafile isnt shrinking.
                                This code appears to work with a small file ... so trying to see where it may break..
                                I am going to create larger and larger test files to see where it goes wrong..
                                set lines 255
                                drop tablespace bug_tbs including contents and datafiles;
                                CREATE TABLESPACE bug_tbs DATAFILE   '+DATA_EXA2/pexb/datafile/bug_tbs.dbf' SIZE 500000 AUTOEXTEND OFF BLOCKSIZE 8K;
                                select file_id, bytes from dba_data_files where tablespace_name = 'BUG_TBS';
                                select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                alter database datafile '+DATA_EXA2/pexb/datafile/bug_tbs.dbf' resize 400000;
                                select file_id, bytes from dba_data_files where tablespace_name = 'BUG_TBS';
                                select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                Correct Results seen with this size file..
                                Tablespace created.
                                
                                   FILE_ID      BYTES
                                ---------- ----------
                                       487     507904
                                1 row selected.
                                
                                     FILE#      BYTES
                                ---------- ----------
                                       487     507904
                                1 row selected.
                                Database altered.
                                
                                   FILE_ID      BYTES
                                ---------- ----------
                                       487     401408
                                1 row selected.
                                
                                     FILE#      BYTES
                                ---------- ----------
                                       487     401408
                                1 row selected.
                                • 13. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                                  Daryl E.
                                  Well seems I cant duplicate the problem with testfiles.. going to try this on the suspect system now..
                                  set lines 255
                                  column bytes format 999,999,999,999,999
                                  drop tablespace bug_tbs including contents and datafiles;
                                  CREATE BIGFILE TABLESPACE bug_tbs DATAFILE   '+DATA_EXA3/dexb/datafile/bug_tbs.dbf' SIZE 1000000000000 AUTOEXTEND OFF BLOCKSIZE 8K;
                                  select file_id, bytes from dba_data_files where tablespace_name = 'BUG_TBS';
                                  select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                  select file#, trunc(bytes/1024/1024/1024) G from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                  
                                  alter database datafile '+DATA_EXA3/dexb/datafile/bug_tbs.dbf' resize 400000000000;
                                  select file_id, bytes from dba_data_files where tablespace_name = 'BUG_TBS';
                                  select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                  select file#, trunc(bytes/1024/1024/1024) G from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'BUG_TBS');
                                  
                                  Tablespace dropped.
                                  Tablespace created.
                                  
                                     FILE_ID                BYTES
                                  ---------- --------------------
                                          72    1,000,000,004,096
                                  1 row selected.
                                  
                                       FILE#                BYTES
                                  ---------- --------------------
                                          72    1,000,000,004,096
                                  1 row selected.
                                  
                                       FILE#          G
                                  ---------- ----------
                                          72        931
                                  1 row selected.
                                  Database altered.
                                  
                                     FILE_ID                BYTES
                                  ---------- --------------------
                                          72      400,000,000,000
                                  1 row selected.
                                  
                                       FILE#                BYTES
                                  ---------- --------------------
                                          72      400,000,000,000
                                  1 row selected.
                                  
                                       FILE#          G
                                  ---------- ----------
                                          72        372
                                  1 row selected.
                                  • 14. Re: Unable to resize asm datafile even though I resized the (logical) datafile
                                    Daryl E.
                                    So I just ran another test of my real datafile issue and it appears to have corrected itself..

                                    OEM Shows this: (Correct)

                                    Block Size (Bytes)     8192
                                    Blocks     1934814455
                                    Logical Size (KB)     15478515640
                                    select file_id, bytes from dba_data_files where tablespace_name = 'USERS1';
                                    select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'USERS1');
                                    select file#, trunc(bytes/1024/1024/1024) G from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'USERS1');
                                    
                                    alter database datafile '+DATA/pcinf/datafile/users1.303.777062961' resize 15850000000000;
                                    select file_id, bytes from dba_data_files where tablespace_name = 'USERS1';
                                    select file#, bytes from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'USERS1');
                                    select file#, trunc(bytes/1024/1024/1024) G from v$datafile where file# = (select file_id from dba_data_files where tablespace_name = 'USERS1');
                                    
                                    
                                       FILE_ID                BYTES
                                    ---------- --------------------
                                            12   15,900,000,002,048
                                    1 row selected.
                                    
                                         FILE#                BYTES
                                    ---------- --------------------
                                            12   18,691,697,672,192    <<<< CAUSING ME MUCH MUCH GRIEF!!
                                    1 row selected.
                                    
                                         FILE#          G
                                    ---------- ----------
                                            12      17408
                                    1 row selected.
                                    Database altered.
                                    
                                       FILE_ID                BYTES
                                    ---------- --------------------
                                            12   15,850,000,007,168
                                    1 row selected.
                                    
                                         FILE#                BYTES
                                    ---------- --------------------
                                            12   15,850,000,007,168
                                    1 row selected.
                                    
                                         FILE#          G
                                    ---------- ----------
                                            12      14761
                                    1 row selected.