10G upgrade issue 1

[maswien]

We are planing 10g upgrade on one of our 9i database, the chanlleange is that it's 9i enterprise edition and we need to upgrade it to 10G standard edition.

I was told the only way I can do is export and import, my question is which system schema I need export and then import to 10G?

I don't know which option was intalled to the 9i database, so there maybe some oracle builtin schema is used. How can I check this?

Thanks!

 

[SantaMufasa]

...which system schema (do) I need (to) export and then import to 10G?

None...You should export and import only your application schemas. (Hopefully neither SYS nor SYSTEM schemas own any application objects.) The SYS and SYSTEM schemas should contain only Oracle-instance-related/data dictionary objects. Such objects would relate to the Oracle version. You should never export/import data dictionary objects.

If you have additional questions, please post.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[maswien]

Thank you Mufasa!

I mean the schemas that installed when some options were selected during the database was created.

For example ctxsys, xdb ...

I don't know if the application uses these options at all, is there a way to tell that? Or I just ignore those schemas that enterprise edition options specific, then I probably end with error if application uses these options.

 

[maswien]

Another question:

If there are alots of schema users in the databases, what is the easy way to export and import these schemas? Can I just exclude some schemas like 'sys' and export / import?

 

[SantaMufasa]

Good question, Maswien. The method that I use is a script I call, "exporter.sql":

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
col x format a200
accept userstr prompt "Enter (a portion) of the user(s) to export: "
spool TempExp.bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
   ||'file='||upper(instance_name)||'.'
   ||username||'.dump log='||username||'_Exp.log statistics=none '
   ||'owner='||username
   ||' userid='||username||'/'||username||'@'||instance_name x
from dba_users, v$instance
where username not in ('SYS','SYSTEM','DBSNMP')
  and username like upper('&userstr%')
order by username
/
spool off
prompt
prompt wrote 'TempExp.bat'
prompt
set feedback on
set linesize 180

The invocation and output of the "exporter.sql" script look like this:

SQL> @exporter
Enter (a portion) of the user(s) to export:
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.YADA.dump log=YADA_Exp.log statistics=none owner=YADA userid=YADA/YADA@dhunt

exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.YADA4.dump log=YADA4_Exp.log statistics=none owner=YADA4 userid=YADA4/YADA4@dhunt

wrote 'TempExp.bat'

(The script generates the output onto a single line per schema, but the Tek-Tips.com reply screen wraps the output, above, onto multiple lines.


The counterpart script, "importer.sql" looks like this:

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
accept userstr prompt "Enter (a portion) of the user(s) to import: "
spool TempImp.bat
select 'imp buffer=15000000 grants=y feedback=1000 fromuser='
   ||username||' touser='||username
   ||' file='||upper(instance_name)||'.'
   ||username||'.dump log='||username||'_Imp.log '
   ||' userid='||
   username||'/'||username||'@'||instance_name
from dba_users, v$instance
where username not in ('SYS','SYSTEM','DBSNMP')
  and username like upper('%&userstr%')
order by username
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp.bat"'
prompt

Notice that in both cases, 1) you can enter a portion of the schema name(s) for which you want export/import commands, and 2) the script generates a text file of the command(s) so that you can post-edit the export commands. In both scripts, the default passwords are the same as the schema names, but you can certainly edit those results, as well.

If you have any follow-on questions, please post here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[maswien]

Hi Musafa,

This is really helpful!

I have another question, using your script, seems I need to create all the tablespaces, roles and users on the new DB before I run the import.

Is there any better way to repliacte all the users and roles in the new database?

Thanks

 

[SantaMufasa]

Sure, you can use my scripts,

1) "tscreater.sql" to set up tablespaces, and
2) "usersetup.sql" to set up users.

You are certainly welcome to adjust the scripts to match your personal preferences. In each case, since the scripts contain "ACCEPT...PROMPT" combinations, you must run the following code as scripts and not as cut-and-paste executions.

The "tscreater.sql" script uses my "freespace.sql" script to provide helpful/useful information as you create your tablespaces, so I list "freespace.sql" first:

"freespace.sql":

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

"tscreater.sql":

set echo off
set verify off
set feedback on
@freespace
accept tsname prompt "Enter the name you want for the new tablespace: "
prompt
prompt Next, enter the fully qualified (case-sensitive in Unix) EXISTING path
prompt and non-existing filename for the '&tsname' tablespace.
prompt WARNING: if the filename you enter already exists, it will be OVERWRITTEN.
accept tsfile prompt "Enter path and filename: "
prompt
set echo on
create tablespace &tsname datafile '&tsfile' size 10m reuse
autoextend on next 10m maxsize 2000m
extent management local autoallocate
/
@freespace

"usersetup.sql":

set verify off
accept un prompt "Enter Username: "
accept ts prompt "Enter Default Tablespace name: "
accept tmp prompt "Enter Temporary Tablespace name: "
create user &un identified by &un
default tablespace &ts
temporary tablespace &tmp
quota unlimited on &ts
/
grant connect, resource, select any table to &un
/
grant CREATE VIEW to &un -- for Oracle 10g
/
grant plustrace to &un
/
revoke unlimited tablespace from &un

Let us know if you have questions or if you need clarifications on any of the above scripts.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[maswien]

Thank you Musafa!!

I used your script to generate the exp script, then when I ran it I got error like the one I posted long time ago:

http://tek-tips.com/viewthread.cfm?qid=1202176&page=6

This is not the problem of your script, but something wrong with my source DB, can I just ignore this message? I tried different ways to fix this problem but no luck.

 

[SantaMufasa]

can I just ignore this message? I tried different ways to fix this problem but no luck.

I, personally, would not ignore such problems...I would follow the methodologies that appear in your other thread that you mention, above.


If you follow the methodologies there, and if you still have errors in your objects, then I propose that you start another thread entitled, "Need help troubleshooting re-compile errors," then list the code that is throwing errors, along with the error messages you are encountering...Many of us will be able to help (although I am leaving in a few minutes on a 4-hour drive to my mother's house for US Mothers Day).

Let us know your findings, either here or in another new thread for solving your re-compile problems.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[maswien]

Hi Musafa,

Your script helped me. Someone else also suggested another way to do it is to exp the whole database with option full=y. Then imp it to the newly created 10g database.

Although there are some errors like 'object already exists ...' but doesn'r hurt anything.

Do you think this way is better?

Thanks