7 Replies Latest reply: Oct 15, 2012 9:11 AM by DBA2011 RSS

    refresh database through exp/imp

    DBA2011
      version 10203 on windows (old db on solaris 8170)

      i Have to refresh database data, No of users/schemas are 400+. fastest way to do that would be to do full exp/imp.
      but 1st drop current users cascade. (any command to drop all users in with one command?)
      then validate all all tables/schemas are same & up-to-date( any suggestion to validate this efficiently?) i am thinking to check full exp logs on old & new db.(what that can take forever manually going through thousands of tables etc)
        • 1. Re: refresh database through exp/imp
          vlethakula
          sql > select ' drop user ' || username || ' cascade ;' from dba_users where username not in (default user list like sys,system,dbsnmp,etc)

          did not understand other requirement? what do you mean by validating all tables/schemas?

          Edited by: vreddy on Oct 3, 2012 8:03 AM
          • 2. Re: refresh database through exp/imp
            Iordan Iotzov
            fastest way to do that would be to do full exp/imp.
            Copying the 8i DB to the new server using RMAN DUPLCIATE or RESTORE and them upgrading it to 10g might be faster in some cases.

            In general, a good way to test that everything was moved is to create the db link between the old and the new DB and using SQLs like the one below to check:
            select owner, table_name from dba_tables@old_db
            where owner not in (‘SYS’,’SYSTEM’,…)
            minus
            select owner, table_name from dba_tables
            where owner not in (‘SYS’,’SYSTEM’,…)
            
            
            
            select owner, table_name from dba_tables
            where owner not in (‘SYS’,’SYSTEM’,…)
            minus
            select owner, table_name from dba_tables@old_db
            where owner not in (‘SYS’,’SYSTEM’,…)
            Iordan Iotzov
            http://iiotzov.wordpress.com/
            • 3. Re: refresh database through exp/imp
              DBA2011
              & how to verify all tables has similar # of rows also?
              • 4. Re: refresh database through exp/imp
                Iordan Iotzov
                First, make sure that statistics are properly gathered in both the old and the new DB.

                Next, use num_rows column in dba_tables. Since statistics are typically gathered by sampling, they would not necessarily match. They need to be quite close though.

                After you are sure that all objects were transferred, you can issue a query to find all tables with “invalid” number of records.

                The query can look something like this:
                select s.owner, d.table_name , d.num_rows 
                from dba_tables d
                where d.owner not in (‘SYS’,’SYSTEM’,…)
                and not exists
                           (select * from dba_tables@old_db s
                          Where d.owner = s.owner
                          And d.table_name = s.table_name
                          And s.num_rows between 0.9*d.num_rows and 1.1*d.num_rows
                          )
                You need to take care of some special cases, such as num_rows being NULL, partitioned tables, etc.

                Iordan Iotzov
                http://iiotzov.wordpress.com/
                • 5. Re: refresh database through exp/imp
                  DBA2011
                  any quick tip if full imp does't work and have to do touser/fromuser imp...? i mean how to manager/collect 400+ users imp through this method?


                  what i am doing wrong?
                  SQL> select owner, table_name from dba_tables
                  2 where owner not in ('SYS','SYSTEM')
                  3 minus
                  4 select owner, table_name from dba_tables@ORA8UNX-TCP
                  5 where owner not in ('SYS','SYSTEM')
                  6 /
                  select owner, table_name from dba_tables@ORA8UNX-TCP
                  *
                  ERROR at line 4:
                  ORA-00933: SQL command not properly ended
                  • 6. Re: refresh database through exp/imp
                    Iordan Iotzov
                    The scripts are fine. SQL*Plus may have issues with the “–“ symbol in the db link name.

                    You can modify the query to accommodate handling touser/fromuser.

                    Let assume that from go from use SCOTT (old DB) to use PETER(new DB). A modification like this one
                    select case when owner  = ‘PETER’ THEN ‘SCOTT’ else owner end , table_name from dba_tables
                    where owner not in (‘SYS’,’SYSTEM’,…)
                    minus
                    select owner, table_name from dba_tables@old_db
                    where owner not in (‘SYS’,’SYSTEM’,…)
                    should be able to handle the name change.

                    Iordan Iotzov
                    http://iiotzov.wordpress.com/
                    • 7. Re: refresh database through exp/imp
                      DBA2011
                      do i need to create dblink for this?

                      SQL> select owner, table_name from dba_tables@ORA8UNX-TCP;
                      select owner, table_name from dba_tables@ORA8UNX-TCP
                      *
                      ERROR at line 1:
                      ORA-00933: SQL command not properly ended


                      SQL> connect system/********@ORA8UNX-TCP;
                      Connected.
                      SQL>