Solved

Can we do parallel exports on the same database?

Posted on 2002-05-03
10
472 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 58
Oracle SQL Char delimited error 5 30
how to tune this sql query 61 102
select query - oracle 16 82
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now