August 25, 2008 at 3:10 pm
😛
These days we do need a brain of 3 elephants to remember all the stuff we need for everyday use with all the massive amount of tools and enhancements Oracle is giving us - sometimes delivering bugs as well. I may not remember the exact patch I applied then, but as you see there are things you will not find in nice books that deal about administration. That's what you call experience. And as for memory, I guess it's a common thing to remember things from the past that gave you a lot of pain, like unexpected disasters.
I forgot to mention - for those who would like some experience with and export/import - normally no one should install or create anything in the SYS schema. However I do have outsourcing companies that do that. In that case keep all the installation scripts, because once you make a decision to rebuild your database a full import will not import your objects and privileges that anyone installed in SYS schema.
Today in Oracle 9 and 10 I believe this would be hardly probably possible, Oracle changes the way you use the SYS account.
Regards,
Richard.
August 26, 2008 at 5:11 am
Well, to get back to the OP's original request for sybtax (where I think he meant syntax), here are some simple examples for a Windows based system. Each line is placed into a BAT file which is then invoked.
EXP SYSTEM/SYSTEM@ORCL FILE=c:\wherever\my.dmp LOG=%temp%\exp.log GRANTS=N OWNER=THEOWNER
IMP SYSTEM/SYSTEM@ORCL FILE=C:\wherever\my.dmp LOG=%temp%\imp.log ANALYZE=N IGNORE=Y GRANTS=N FROMUSER=THEUSER1 TOUSER=THEUSER2
I deal with a product which sometimes requires the data from the customer (after having signed a non-discloure agreement) and I have been using the above syntax for years on Oracle 8, 9 and 10 systems (EXP on the client's side and IMP on my side).
Just do an EXP -? or IMP -? to get the full syntax.
August 27, 2008 at 7:03 am
Do a google search for RMAN. Follow that you'll be fine. I struggled with it at first but I got the hang of it. If you want me to send a script that does a full online backup plus takes a copy of the controlfile let me know. I use it every night for my production backups.
August 27, 2008 at 7:32 am
Yeahhh I'm not true DBA, more developer but even I know that RMAN is the way to go today. It BTW can check also for corrupted blocks and restore just one corrupted block if necessary. It can do also many other things. So no need to stick in stone age with experience gathered 15 years ago. There are of course some very basic concepts that don't change over time, but the tools that can be used aren't among them. If you are afraid don't go to the very bleeding edge of technologies or versions of software, but there is absolutely no need to stick just with experience gathered many years ago. IT and particularly DBMSes as part of IT is area where everyone should look into new features and adapt them to make life easier. Each few years there are new versions with new features, as soon as one starts to use new version, one should read new features guide and understand what new possibilities it offers. Of course they sometimes are buggy, of course if one is very cautious he can use not the very last version of software, but anyway no need to stick with Oracle7 or SQL Server 4.2. And BTW RMAN is quite old product and has approved itself many times.
Gints Plivna
http://www.gplivna.eu
August 27, 2008 at 8:37 am
Michael Meierruth (8/26/2008)
EXP SYSTEM/SYSTEM@ORCL FILE=c:\wherever\my.dmp LOG=%temp%\exp.log GRANTS=N OWNER=THEOWNERIMP SYSTEM/SYSTEM@ORCL FILE=C:\wherever\my.dmp LOG=%temp%\imp.log ANALYZE=N IGNORE=Y GRANTS=N FROMUSER=THEUSER1 TOUSER=THEUSER2
1- This doesn't work; you are importing nothing in your example.
2- You do not want to exp/imp using SYSTEM account, very bad mojo.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 27, 2008 at 9:42 am
PaulB (8/27/2008)
Michael Meierruth (8/26/2008)
EXP SYSTEM/SYSTEM@ORCL FILE=c:\wherever\my.dmp LOG=%temp%\exp.log GRANTS=N OWNER=THEOWNERIMP SYSTEM/SYSTEM@ORCL FILE=C:\wherever\my.dmp LOG=%temp%\imp.log ANALYZE=N IGNORE=Y GRANTS=N FROMUSER=THEUSER1 TOUSER=THEUSER2
1- This doesn't work; you are importing nothing in your example.
2- You do not want to exp/imp using SYSTEM account, very bad mojo.
Can you be more specific:
1 - why am I not importing anything; just ran this syntax today and it worked fine
2 - why is using the SYSTEM account bad mojo for doing an IMP? is it because the password is in the BAT file? what do you do with the SYSTEM account that's good mojo?
August 27, 2008 at 5:36 pm
Yeahhh I'm not true DBA, more developer but even I know that RMAN is the way to go today.
And BTW RMAN is quite old product and has approved itself many times.
1. No one is disputing the relevance RMAN as a backup tool
or the viability of a HOT or COLD database backup .
2. As a developer I find EXP and IMP a very handy 'backup'
tool as it allows to take backup of selected objects in the
schema with or without data. Just imagine a scenario
where you have to restore just one table from database A
to database B which are not connected by any network.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 28, 2008 at 12:35 am
Michael Meierruth (8/27/2008)
1- This doesn't work; you are importing nothing in your example.
Either you did not or you are a liar.
2- You do not want to exp/imp using SYSTEM account, very bad mojo.
Think of SYS and SYSTEM accounts as "sa" account.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 28, 2008 at 12:47 am
I know SYS and SYSTEM are like sa.
Your crytpic response is creating suspense that's awesome.
Can you be just a bit more detailed in your explanations?
August 28, 2008 at 12:57 am
Mazharuddin Ehsan (8/27/2008)
1. No one is disputing the relevance RMAN as a backup tool
or the viability of a HOT or COLD database backup .
Not so sure about it 🙂 OK but let's be so.
2. As a developer I find EXP and IMP a very handy 'backup'
tool as it allows to take backup of selected objects in the
schema with or without data. Just imagine a scenario
where you have to restore just one table from database A
to database B which are not connected by any network.
Yeahhh, then probably the problem is in terms. I won't call it backup and I won't call it restore. I'd call it export files (or data or whatever) exported either by original export or data pump export (which BTW is important and in versions >= 10g data pump is much more powerful than original exp/imp) and importing tables/data in database B. Just to avoid confusion. Just to differentiate it from "the backup" 🙂
But yes I agree it is handy in above mentioned cases.
Gints Plivna
http://www.gplivna.eu
August 28, 2008 at 1:36 am
Mazharuddin Ehsan (8/27/2008)[
2. As a developer I find EXP and IMP a very handy 'backup'
tool as it allows to take backup of selected objects in the
schema with or without data. Just imagine a scenario
where you have to restore just one table from database A
to database B which are not connected by any network.
I see your point but the problem is what you call a backup is not a backup.
We define Oracle backup as a set of files you can carry to a desert island where all you have is a powered-up server with operating system in working condition and Oracle binaries installed and you can restore/recover your entire database.
Can you do that with an export dump? No, you cannot.
What you are doing with exp/imp is data transfer which is exactly what exp/imp was designed to do.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 28, 2008 at 1:41 am
Michael Meierruth (8/28/2008)
I know SYS and SYSTEM are like sa.Your crytpic response is creating suspense that's awesome.
Can you be just a bit more detailed in your explanations?
Cryptic? :w00t:
In regards to SYS/SYSTEM/sa account utilization you do not use these accounts to do minor stuff. Best practices tells you to use the account with lower privileges yet powerfull enough to do what you have to do. You don't go around dropping a nuke each time you have to deal with a little incident, don't you? 😎
In regards to your exp/imp... you cannot "export owner=theonwer" and then "import fromuser=theuser1"... there are not "theuser1" owned objects in your dump file 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 28, 2008 at 1:54 am
Hi,
using the SYSTEM acount is not a good idea to use in scripts. However, you can create a seperate DBA account like OPS$ORACLE, create an operating system account "oracle", keep the passwords restricted to the DBA and grant the EXP_FULL_DATABASE privilege to that account. Then you don't need to put in passwords into your scripts, just run :
"exp userid=/ ...".
You can also create a user with limited privileges to run full exports:
create user exp
identified by exp
default tablespace users
temporary tablespace temp;
grant create session to exp;
grant restricted session to exp;
grant EXP_FULL_DATABASE to exp;
grant select any table to exp;
Yet any database owner of the tables can run an export to dump his own tables.
Regards,
Richard.
August 29, 2008 at 3:50 am
gints.plivna (8/28/2008)
Yeahhh, then probably the problem is in terms. I won't call it backup and I won't call it restore.
PaulB (8/28/2008)
What you are doing with exp/imp is data transfer which is exactly what exp/imp was designed to do.
Dears,
exp/imp is not just data transfer. If you just have to transfer a table from DatabaseA to DatabaseB you can do as follows:
login to DatabaseB
sql> COPY FROM SchemaName/Password@DatabaseA INSERT TableName USING -
SELECT * FROM TableName
But in the case of exp/imp, the DatabaseA need not be alive. This is the essence of backup and restore.
Most importantly, Oracle itself is defining this as a valid backup process as I described earlier in the discussion.
Thanks,
Mazharuddin
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 29, 2008 at 4:22 am
Mazharuddin Ehsan (8/29/2008)But in the case of exp/imp, the DatabaseA need not be alive. This is the essence of backup and restore.
Pure BS 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply