logs archiveIRC Archive / Freenode / #oracle / 2010 / May / 16 / 1
sidh
greetings gentlemen
I need to on a 11gR1 instance to export a schema without data, so i did exp user/pwd file=shema_ddl.dmp rows=n
but on the new instance tablespace and datafile are not the same, so i decided to create small tablespace that have the same name as the original instance, and after importing with imp, i alter the tables to move the tablespace to the new one
but i get ORA-01659 error because , even if data are not imported, the datafiles are quite full, why ,
RJarett
sidh: why are you using exp?
i really wish exp was totally removed from 11
sidh: exp is deprecated. dont use it. dont expect any help on it in 10 or 11
use expdp and impdp
plus you can remap tablespaces on impdp
sidh
ok i'm looking for expdp options to see how to do that
RJarett
impdp help=y
sidh
yes that is what i did
CONTENT=METADATA_ONLY seems to do what i need
RJarett: but one thing i don't understand, before when i did the exp command with rows=n, i get a dumpfile of 800 kB
RJarett
and?
sidh
but when i import it , and list the occupation rate of my tablespace (several GB for each) they where nearly full, why ?
i import no data on it
RJarett
because the DDL of the table in question was specified with a size
sidh
i there a way to not specify a size for DDL ?
         

RJarett
if you are just migratting the objects with no data why use exp at all?
use dbms_metadata to extract the source of the objects and recreate them
sidh
RJarett: i don't understand sorry
RJarett
you want all objects
just objects
sidh
i google for dbms_metadata
RJarett
empty objects
with no data
empty tables
empty sequences
right?
thats the same as if you just freshly created the object
sidh
for my purpose i only need tables structure,
RJarett
well dbms_metadata will dump out the sql used to create objects
sidh
i 'll re create constraints and indexes later
RJarett
so you could just dump out a sql script from the other db
and run it in this db
sidh
ah ok i see, i'm googling for how doing that, it seems the best option
thank you very much RJarett for your advice
RJarett
but with expdp/impdp it would be just a few seconds to do it too
since you would need to just specify remap_tablespace=oldts:newts
crap like that
sidh
yes but with expdp , my tablespace would be filled again , isn't it ?
RJarett
depends how you do it
sidh
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm <- i'm looking there and i don't see EXCLUDE options that would help for not filling the tablespace
RJarett
is there a dblink between the 2 databases?
sidh
no
fir this purpose i need to use a dump file
RJarett
expdp content=metadata_only schemas=whatever DIRECTORY=foo
impdp user/pass remap_tablespace=oldts:newts schemas=whatever dumpfile=whatever.dmp directory=foo
sidh
RJarett: thank you , but that way my tablespace won't be filled because of the DDL of the tables ?
RJarett
yes
sidh
great thank you very much RJarett
oh sh*t here midnight has passed and the company vpn connection won't be available 'til 9 AM, i have to wait
http://psoug.org/reference/dbms_metadata.html <-- waoww how could i missed that ! this seems so handy, thank you RJarett
need to sleep
         

jay321
oracle net listener, is that the same as the tsn listener?
RJarett
yes
jay321
ok ty
during oracle install, if i choose not to create a database, can the imp tool do that for me?
RJarett
no
imp puts data to an existing db
you can later use dbca to create one
jay321
ok, it asks what the global database name should be, based on another install is there a way to know what was set? e.g. by looking in the .ora file?
RJarett
why does it need to be the same as another install?
shouldnt ever have 2 dbs with the same name on the network
jay321
ah .. i dont know. i was guess it ought to be the same
RJarett
do you have multiple computers with the same ip on your network?
or names?
mod_cure
RJarett, hi, how goes ?
RJarett
hi
jay321
no. our domain name has a '-' dash, seemed the installer complained not to use dash
mod_cure
RJarett, i know 2% of oracle now :)
RJarett
ya sure?
mod_cure
RJarett, maybe 1% :)
RJarett, thanks for recommending that book
RJarett
jay321: the name you give to the DB, even like ORCL.domain.com domain.com really doesnt matter at all
mod_cure
RJarett, read about 80 pages so far
RJarett
good
so considering theres hundreds of books with hundreds of pages each
and you have 80 pages...
;) still think 1%?
mod_cure
RJarett, 0.1 ?
:)
RJarett, i also purchased the rman book. read about 50 pages. i can now backup the database
the flashback technology is sweet. i played with that yesterday. really cool
RJarett, i installed it on my centos box
RJarett
good
mod_cure
wonder how long it will take me to be efficient at it ?
RJarett
think of a very large project
make a big website or order and inventory system out of oracle
get it working
pull your hard drive out of the system
hit it with a hammer
and try to recover what you had
or ask a friend to randomly destroy files or the data
mod_cure
RJarett, how long have u been using oracle ?
RJarett
about 13 years
mod_cure
been using mysql for 10 years. i work for a small company
Trengo
i thought it was longer than me
RJarett
mysql means nothing in the industry
plus with 3 different fragmented codelines its a sure sign it wont live more than 5 years
death by sale and fork
mod_cure
its very popular
for small companies its great as they probably cant afford a lot for software
RJarett
popular by inexperienced people with little influence in an insignificant niche
same companies cant afford to pay employees alot
so those people who use it rarely grow into larger positions
mod_cure
the same could be said about php but its very popular for the web
and apache etc
RJarett
no
apache isnt on the same league
mod_cure
agree
RJarett
out of everything out there apache does it better than anyone else because of what it does
and its not the heart of an app
you can easily switch apache out with no code changes
mod_cure
agree
RJarett
and no one would know
mod_cure
real exicited about learning oracle
i must be sick ? :)
RJarett
after using mysql for 10 years, do you feel "senior level" in your job or approaching 6 figures?
mod_cure
yes
RJarett
pretty good for a small company
mod_cure
ive been with the company from day 1.
my case is a little different
but i see what u mean
RJarett
are you keeping offsited and vaulted backups?
mod_cure
yes
RJarett
better off than most mysql people
mod_cure
agree
i want to learn oracle to increase my knowledge but to learn how it works and why its great etc
RJarett
its great for some of the reasons i listed in the test
if you lost a whole san, how quickly could you recover your data?
right now
mod_cure
oracle is great for that
:)
RJarett
i have terabytes of data
mod_cure
wow
i have small database
RJarett
even reading from another san or tape to move it back to the original place would take up to a day
mod_cure
flashback would be super fast for that
RJarett
nope
mod_cure
nevermind, i see now
RJarett
if you only had data 1 place on the system and you lost that san
where would it flash back too?
plus flashback in very heavy on a large system
mod_cure
i see what u mean, i misunderstood at first
RJarett
if im pushing 70,000 transactions a second all that flashback needs to be kept around
mod_cure
im assuming u are using RAC ?
RJarett
nope
mod_cure
how do u scale ?
RJarett
more disks, more cache, more throughput
im io bound
mod_cure
u dont scale horizontally ?
RJarett
extra frontend instances like rac provides would actually slow me down and increase my costs 5x in licensing
mod_cure
but gives u five 9's
RJarett
no it doesnt
mod_cure
u have 1 machine for database ?
RJarett
it slows a system down
system as a whole i mean
theres cases its goof
good
and many that its a really poor choice
if you lose your disks in RAC your are still sh*t out of luck
packets
I think RAC is overused in a lot of infrastructures...
RJarett
rac is multiple systems hitting the same shared storage
mod_cure
oic
RJarett
still the same limitation
mod_cure
mysql cluster is shared nothing, i assume rac was like that but then again i have no clue what rac is :)
u have 1 machine for database ?
RJarett
no
shared nothing is a sh*tty ass option
i process hundreds of gb/night of data
mod_cure
lost, how do u scale then ?
RJarett
i wouldnt want to have to replicate that across many many many nodes
i buy bigger SANs, or think about my data smarter
if i was cpu bound, i would use rac.
mod_cure: never trust oracle marketing material
mod_cure
RJarett, kinda lost on how u scale, meaning do u have 2 database and replicate etc ?
RJarett
no
1 database
mod_cure
RJarett, im new to oracle, so im not sure how it scales, so maybe i should ask u later when i know more :)
RJarett
i use image copy for my backups
its not really "scaling"
mod_cure
what if that 1 database goes down ?
RJarett
i switch to my image copy
packets
No dataguard?
mod_cure
auto fail-over ?
RJarett
datafile copy which is 2 days old
backup set 1 day old
archive logs from the last 24 hours
if sh*t hits the fan and i lose my whole san, rman> alter database switch to copy; recover database;
and it rolls the backupset and archive logs into the backed up datafiles
takes a few moments
and im back up and running
mod_cure
oic
packets
Curious why you didn't implement dataguard in that situation...
RJarett
$
jay321
bah, install hangs on 'naeet.o'
mod_cure
im assuming dataguard is a standby server
packets
Ahh, not running enterprise edition?
« prev 1 2 next »