3 Replies Latest reply: Mar 28, 2012 10:41 AM by 912595 RSS

    11gR2 upgrade via Import - a couple of queries

    Jim Thompson
      Hi,

      I am planning to move a 10g Database from a Windows 2003 server to another Windows 2008 server and also roll in a database upgrade to 11gR2. Hence I will use the export / import method of upgrade.

      This will also allow me to rearrange some of the storage settings as part of the migration. However I want to check a couple of things

      Q1 ) Does datapump not export SYSTEM and SYS owned objects or anything that is in the SYSTEM tablespace ( I have found a couple of objects in the SYSTEM tablespace space that someone naughty has placed in there - I am wondering will the export fail to export these because they are in the SYSTEM tablespace or am I mistaking this for SYSTEM / SYS owned objects ? )

      Q2 ) The current database has some tablespaces that have multiple data files. Assuming that the file system / SAN on the new target environment is efficiently created, would I be better consolidating these data files into 1 single data file per tablespace.

      Furthermore would it be better to create such tablespaces as type BIGFILE ? what are the pro's / con's of BIGFILE ( I know you can only have 1 such datafile per tablespace )

      Q3) Traditionally when checking the density of my database in the past for Dictionary Managed tablespaces I would have simply sumed the bytes in dba_segments and compared that to the sum of bytes in dba_data_files. However I guess for locally managed tablespaces, this comparison is meaningless as these dictionary views do not contain the storage info for locally managed tablespaces. How would you calculate this for locally managed tablespaces ?

      Thanks,
      Jim
        • 1. Re: 11gR2 upgrade via Import - a couple of queries
          912595
          Jim Thompson wrote:
          Hi,

          I am planning to move a 10g Database from a Windows 2003 server to another Windows 2008 server and also roll in a database upgrade to 11gR2. Hence I will use the export / import method of upgrade.

          This will also allow me to rearrange some of the storage settings as part of the migration. However I want to check a couple of things

          Q1 ) Does datapump not export SYSTEM and SYS owned objects or anything that is in the SYSTEM tablespace ( I have found a couple of objects in the SYSTEM tablespace space that someone naughty has placed in there - I am wondering will the export fail to export these because they are in the SYSTEM tablespace or am I mistaking this for SYSTEM / SYS owned objects ? )
          There are two things
          If you own table under SYS users then these wont be exported but this case is not seen with SYSTEM user. You may able to export SYSTEM objects.
          So before upgrade/export, move these objects to other schema and then do the export. There are couple of ways achiving this i.e move objects to other user a) use exp b) CTAS
          >
          Q2 ) The current database has some tablespaces that have multiple data files. Assuming that the file system / SAN on the new target environment is efficiently created, would I be better consolidating these data files into 1 single data file per tablespace.
          If there are multiple disk controllers then assign these controllers to disk equally. For e.g 2 diskcontroller for 10 disk which leaves 1 for each 5. Now on those 5 disk you can create n numbers of datafile or just one, result would be same because its the controller which actually do stripping/load balancing.
          Best is to use ASM which do stripping/mirroring for you.
          Furthermore would it be better to create such tablespaces as type BIGFILE ? what are the pro's / con's of BIGFILE ( I know you can only have 1 such datafile per tablespace )
          BIGFILE are generlly used for dataware house such as loading unloading lots of data. There are no such pro's/con's for bigdatafile. But its the requirement which suits you best.
          >
          Q3) Traditionally when checking the density of my database in the past for Dictionary Managed tablespaces I would have simply sumed the bytes in dba_segments and compared that to the sum of bytes in dba_data_files. However I guess for locally managed tablespaces, this comparison is meaningless as these dictionary views do not contain the storage info for locally managed tablespaces. How would you calculate this for locally managed tablespaces ?
          To check size of database used:
          SQL>select sum(bytes/1024/1024/1024) from dba_segments;
          To check size of complete database( Including Free + Used)
          SQL>select sum(bytes/1024/1024/1024) from dba_data_files;
          To check free space in database
          SQl>select sum(bytes/1024/1024/1024) from dba_free_space;
          Thanks,
          Jim
          Thanks
          • 2. Re: 11gR2 upgrade via Import - a couple of queries
            Jim Thompson
            Is the bytes column in dba_segments accurate when you are using locally managed tablespaces ?
            • 3. Re: 11gR2 upgrade via Import - a couple of queries
              912595
              Jim Thompson wrote:
              Is the bytes column in dba_segments accurate when you are using locally managed tablespaces ?
              Yes

              And dont forget to close this thread as anwered and marking our suggestion as correct o helpful if you think they wer.
              https://forums.oracle.com/forums/ann.jspa?annID=718