Solved

Can we do parallel exports on the same database?

Posted on 2002-05-03
10
498 Views
Last Modified: 2007-11-27
Hi,
When I did a full export of my test db it took 10 hours. There is a 30 mil row table which took major chunk of the time.

Is it possible to run a full export and a table export at the same time? This table comes in the end of the sequence of full export. So it should not conflict with the full export. Once the table export is done, I want to drop the table.

One last question, when I did the full export I got this msg for a few tables but it exported the table rows though - "EXP-00079: Data in table "table_name" is protected. Conventional path may only be exporting partial table." Do I need to worry about it?

Your feedback would be greatly appreciated.

Thx,
masaimara
0
Comment
Question by:masaimara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 7

Expert Comment

by:waynezhu
ID: 6988967
Th signle important thing you need to do is to export
using direct path, such as,
exp ... direct=y

[You may also specfiy a large buffer, for example,
         buffer=5000000]

0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 6989574
masaimara,

Yes, you can export a big table while exporting an entire schema or database. Once you got the big table dump with you, you can drop it. The main export is smooth except for this error:
EXP-00007: dictionary shows no columns for MYSCHEMA.BIG_TABLE

But this is not a problem. You can import it back whenever you may so please and it will be smooth without warnings. I actually checked it in my test database.

The second part of your question is partly answered by waynezhu. Keep direct=y and buffer=5000000 and your problem will mostly be solved. The error you are getting is just a warning which also come if privileges not there, if table already exists and ignore=y not given, duplicate constraints in schema.

The warnings also come if other objects like indexes, jobs etc. exist during export/import and PL/SQL cursors, triggers, subprograms and packages exist. The warnings mostly are not important to pay attention to and in your case also it is the same.

Just check the data, structure, indexes, constraints etc. for the tables for which this warning was thrown.

--- k_murli_krishna
0
 

Author Comment

by:masaimara
ID: 6991471
Waynezhu and Murali Krishna,
Thankyou for your comments.

I am trying to do a full export and drop the database, recreate it with original create scripts, and import it back.

1. Can you tell me how to drop the database - is it deleting the datafiles and stuff OR can you use some tool to drop it before you recreate the database?

2. If no database create scripts exist, can we create the scripts somehow using the existing the database?

masaimara
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 7

Expert Comment

by:waynezhu
ID: 6991675
1. Depending upon OS and Oracle versions, there are various
ways to do that, for example, using Oracle Enterpise Manager, manual method, ...

2. Yes, you can. Again there are many ways to do it. You
can use various tools, export utility, SQL scripts, ...

0
 

Author Comment

by:masaimara
ID: 6997165
Waynezhu and Murali Krishna,
I used direct=y and recordlength=db block size instead of buffer=5000000 and it ran like a horse.

Do you guys know about

'alter database backup controlfile to trace'

by any chance.

If you know then, can you please tell me if these statements make sense

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DATABASE_1" NORESETLOGS ARCHIVELOG

These are from the trace file created. I am going to delete all the database files (datafiles,control files, and redo files) before full import. Do I need to modify the statements? I know for sure that i have to make ARCHIVELOG to NOARCHIVELOG as there is no archiving for this test database.

Thx,
masaimara
0
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 50 total points
ID: 7000764
masaimara:
1) If you are going to do backup and recovery, then do offline/online backup and corresponding recovery.
2) If you want to create database creation scripts from existing database, then third party tools from toadsoft etc. can help you.
3) If you send me your mail ID, I can give a HTML page full of Oracle links.
4) You can on the other hand drop database and then recreate using same options or exactly same using database configuration assistant.
5) What you can do is in a custom db creation note down all parameters that come and their values. Then see full database syntax from any standard book for create database command and fill the gaps. One book is Oracle8i DBA Bible by Jonathan Gennick etc.
6) You can do a create from OEM as waynezhu suggested but remember database is not deleted just by removing control files, data files and redo log files. There is much more to it and you will not be sometimes allowed to delete some of them. Either you delete all or keep all.
7) ALTER DATABASE BACKUP CONTROLFILE TO 'filename' specifies that the control file backed up to the specified filename in specified path.
8) ALTER DATABASE BACKUP CONTROLFILE TO TRACE: Specifies that SQL statements will be written to the trace file(user_dump_dest='trace file') and that no control file will be created.
9) STARTUP NOMOUNT: You can start an instance without mounting a database. Typically, you do so only during
database creation.  Again, only some background processes are started.
10) CREATE CONTROLFILE REUSE DATABASE "DATABASE_1" NORESETLOGS ARCHIVELOG;
REUSE: Specifies that existing control files are reused overwriting the existing data in them.
DATABASE "DATABASE_1": Specifies the database for which to create a new control file.
NORESETLOGS: Specifies that the content of the files specified in the LOGFILE clause are expected to be exactly the same as the configuration at the moment that the database was shut down.
ARCHIVELOG: Specifies that the mode of archiving isn't established. This is the default.

Masaimara: Having all the info in hand, if you have just a test DB and not important go ahead. Else take export, offline and online backups before doing anything for sake of recovery. If things spoil up, just unintall and install oracle once again with a new home and DB SID.

--- k_murli_krishna

0
 

Author Comment

by:masaimara
ID: 7001791
As you said, we cannot delete a database by just deleting the datafiles,controlfiles, and redo files, what else should we do to completely erase a database. This database is running on a unix server and I cannot use db assistant from unix.

I have to completely erase the database; recreate it exactly as it was with the same init parameters (except for db_block_size), same datafiles, and modify the extent management for tablespaces (make them locally managed); do a full import. I just need to know the sequence of steps to carry out this task on a unix server without the help of db assist. I will do the full cold backup before the erase.

my id: geauxafrica@yahoo.com

thx,
masaimara


0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 7042184
masaimara:

1) Take export dumps of all schemas
2) Take cold/offline backup
3) Take hot/online backup

The above 3 steps to make sure that you can get it back at any cost. Something really goes wrong reinstall oracle in a new home and with new database SID.

A) One way is if you have windows m/c install oracle, custom create database and while deleting look at the messages and most importantly notice the files in directories which get deleted.

B) The other way is to use ALTER DATABASE to get the database state you want and also by editing init.ora.

C) A third way is for deleting database, delete all that you take backup for both hot/cold i.e. online/offline.

All the best:)

--- k_murli_krishna


0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7096834
Please update and finalize this question.  If you need Moderator assistance to split points or otherwise help with special handling, let us know via comment here or by posting a Zero point question with this question link in the Community Support topic area.
Thank you,
Moondancer - EE Moderator
0
 

Author Comment

by:masaimara
ID: 7149804
Thankyou very much for all your comments.

Masaimara
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question