Create database from Full export dump

[pwoneill]

I just wanted to make sure of the process of creating a new database from a full export. We're moving from 8.0.5 to 8.1.6, and since the sysadmins are slow to respond I figured it was easiest to have them just install the software without me, and I can do the rest of the configuring and database creation from the oracle account.

After the software is in it's new ORACLE_HOME, a quick reference of what I need to do is (I'll be laying out exact procedures later today)

1) take a full export of the current database (consistent=y, full=y). actually in my case consistent wouldn't really be required since all apps and users wouldn't be doing any work, but in general i would assume it's important.

2) sysadmins install the software

3) create the new database and all related steps (catalogs, default tablespaces, redo logs, rollbacks, verify init.ora parameters, etc)

4) import the database.

the overview seems simple enough, i'll start working on the exact details. does anyone have any gotchas to watch out for, helpful tips, etc?

our database has a lot of datafiles (from oracle applications). with the straight insert, i understand that they will be recreated with the same path and storage parameters as they were inititally? i suppose that means moving them from their current location, and letting import re-create them?

should i create a large rollback for the import?

thanks for any information.

[pando]

hi

you have to make sure the path where the datafiles of tablespaces will be created exist, same as the database you exported.

[pwoneill]

Thanks pando.

What about the COMPRESS option? I understand what it does, I'm just wondering if this is reccomended for a full export. What issues are associated? Is performance increased? Extent level fragmentation would be elimiated, but are there any problems associated with this?

[pando]

well these are not my words, it´s from an Oracle instructor who posted in newsgroups, he states as follows:




Imagine you have an EMP table that comes in extent sizes of 100K. When it was created, it acquired an initial extent of 100K. As you inserted new records, it acquired a second and then a third extent. You now have a 300K-big EMP table, made up of 3 100K extents.

Now say you start losing employees -so you start deleting records left, right and centre out of EMP. This doesn't make EMP get any smaller, of course, because it still has its three extents. What it *does* mean is that most of those extents are now empty of real data.

Now, if you do an export where COMPRESS=Y, then on subsequent import, what will happen is that a new EMP table will be created *with a 300K Initial extent*. In other words, the initial extents of all segments are set to be equal in size to the *total* size of all extents current acquired by a segment. Next extent sizes are not affected, by the way. You end up with a 300K EMP table, in one extent -so you've saved yourself precisely zero space. (If you'd used compress=n, by the way, you probably *would* have saved space: import would have created as many extents are needed to store the data about to be imported... and in our case, given all the deleted data, we might only have needed an initial 100K. You therefore have the slightly counter-intuitive situation where COMPRESS=Y will NOT save you any space, but COMPRESS=N actually will!)

Now this behaviour might be a good thing or a bad thing. It all depends. Remember we did all those deletes on the EMP table, so most of its 3 extents were sitting there empty? Well, now we've just created a single 300K extent, most of which is empty. If we were never going to do fresh inserts into EMP, this would be a waste of space. So, compression is bad. On the other hand, if we know we are about to do lots of fresh inserts into the table, having all that space pre-allocated to the segment will make the inserts go faster. So, compression is good. If EMP is sitting there with a 300K initial extent, and we do inserts such that a new NEXT extent is required, that new extent will revert to being 100K. So we have odd-sized extents in the tablespace, which will one day lead to fragmentation. So compression is bad. Except that this is an Oracle 8 or 8i database, with MINIMUM EXTENT or locally managed tablespaces, so the extent sizes that export wants to create are totally ignored. So compression is OK again.

You get the idea. It's a tool to be used when appropriate. The books will tell you to watch out for it when a table has been subject to lots of deletes, because of the '300K most of it empty space' issue. But that might be just what you want, so my advice is to understand what it does, and to use it accordingly.

Where it is extremely useful is in fixing up the problem of a segment having hundreds or thousands of extents because you didn't manage to size it correctly in the first place: what you would really like is 1000 10K extents to turn into a single 10M extent -and that's exactly what compress will do for you. And in that sense, it can fix up problems of past dynamic extension gone haywire.

You are correct in saying that using compress=y might mean the thing is totally unimportable -in that last example, if I need to create a single 10M extent, and I have only got 3M in one part of the tablespace, 4M in another, and a final 3M at the end, then I won't be able to create a single 10M extent, and the import will fail. That's what tablespace fragmentation is all about, and if you plan your tablespaces properly, and use consistent extent sizes, it won't happen. Failure to import is, in other words, a symptom of a much deeper problem, not an issue with using COMPRESS=Y per se.





Hope this helps

[pwoneill]

Yes very useful, thanks.

Is there a way to generate tablespace creation scripts for the currently used tablespaces in the database? This is an important upgrade, but there is room for me to play so I want to get practice that might help later. I know with an exp/imp migration we can keep the current database active during the steps, but unless I pre-create the datafiles in the new database import will try to use the ones currently in use by the old database.

I don't have any creation scripts except the ones we used for our own tablespace, all of oracle applications tablespaces were done elsewhere.

I can do the upgrade by moving the current tablespaces and letting import re-create them, but would like to practice pre-creating assuming I need to use it one day. I started writing a sql script to do it but if there is one already written I'm sure it would be better than what I can hack together.

Thanks

Edit
-----
Actually some more specific questions:

in dba_data_files, there are columns for initial and next extents. these are for the default storage clause correct?

all the tablespaces in question are on autoextend. their increment_by columns in dba_data_files are usually 128. the description of the view says this is the autoextension increment, but is that in KB? most numbers are usually in bytes, but 128 bytes increment seems small.

what field should i use for the maxsize of the autoextend clause? the maxbytes field in dba_data_files for each is 34359721984, which is 33554416K, or about 30gb. Do you think this is the number they entered or a result of maxsize unlimited?

Thanks again.

[Edited by pwoneill on 11-14-2000 at 01:13 PM]

[chobo]

Hello Pwoneill,

I am planning to upgrade production database from 8.0.6 to 8.1.6 using exp/imp.
How did your upgrade go?
I created SQL script that contains all production database structure.
I will pre-create all the database structure (datafile, tablesapce, rollback segment ) and then full import the data with ignore=y option.

Is there any issue that I need to consider before upgrading, except 8.1.6 catalog script has an error?

Thanks.

[johng]

If you have roles set up for the users, you will need to run a script to generae the sql to create them, and the last time i tried this with 8.0.5 i am pretty sure i had to recreate users also. i have the scripts that query the v$ tables to create the creation scripts. email me if you need them

john

johng@employmentgroup.com

[pwoneill]

Everything seemed to go fine, here are the hiccups I encountered though.

My 8.0.5 database character set was WEI but I created the new 8.1.6 database as US7ASCII. For some reason Import wouldn't convert the character sets, it was solved by unsetting my ORA_NLS33 environment variable. I tried to ask Oracle about the reasoning behind unsetting that variable, they gave me a terse answer about it only being used for 7 bit character sets. Not too helpful an answer, but they did solve the problem.

Otherwise the import seemed to go smoothly, just make sure to re-compile invalid objects, double check the roles/permissions, etc.

I'll mention that mine was a small development database that hasn't had a ton of use since the upgrade so I didn't put production level planning into it, but the database and applications are all in working order.

[chobo]

Thanks for the help.

Luckly, my production database creates very small size of dump file and does not have any procedure, trigger and functions (sounds strange?), so It would make my life easier.
I am a little bit worry about JAVA classes.

Anyway, after upgrading, I'll let you guys know if I face any interesting issues, but It won't happen soon.

Again, Thank you for all help.

[ocpdude]

Hi,
I am just about to do the same thing (8.0.5-->8.1.6).
Here is the steps I've done so far.
-- backed up the hardisk
-- exp80 full=y
spool network
-- select name from $datafile
-- select name from v$controlfile
-- select * from v$logfile
spool off
-- copy init.ora,tnsname.ora to network
-- reformat the hardisk
-- installed winNT server pack6
-- installed oracle8.1.6
-- now I need to create a database with different name
and different names for the datafiles and their path..also
can I change the tablespaces name as well ?? if so is IMPORT using full option would do enough to do the job???
did I forget anything ????
thanks,

[Edited by ocpdude on 12-28-2000 at 04:00 PM]