Solved

Database BACKUP

Posted on 2003-10-22
6
686 Views
Last Modified: 2007-12-19
Hello-

I want to backup my database, including tables, constraints, procedures,packages, primary keys, indexes etc.

I have found DBMS_METADATA.GET_DEPENDENT_DDL() function helpful

I also found this link helpful
http://www.dba-oracle.com/art_builder_get_schema_syntax.htm

In the section "Issues with dbms_metadata"


When you have foreign keys (“references” constraints), it would be nice to punch the table DDL in their proper order for re-loading into another database.  If you do not order the table, a table may be added that references another table that has not yet been added.  Making this change would involve adding a WHERE clause that queries dba_constraints and dba_cons_columns to determine the proper table order.

How do I use dba_constraints and dba_cons_columns to retrieve the table in the proper order?
Any example would be highly appreciated.

thank you very much for all your help!

0
Comment
Question by:iit
6 Comments
 
LVL 8

Accepted Solution

by:
Danielzt earned 25 total points
Comment Utility

if you really want to backup my database, including tables, constraints, procedures,packages, primary keys, indexes etc.

The export/import is easy way for you. the export file, usually called dump file, includes all the DDLs and data for all the database objects.

export/import are two utility shipped with oracle. In oracle 8i,they are exp.exe and imp.exe.

in the command prompt:
assume you work on windows(same as on unix)
C>exp help=y

usually, you have 3 ways to export data from the database.

FULL,OWNER and Table

examples:

to export the whole database:
(You need to have EXP_FULL_DATABASE privilege. )
C:>exp system/manager@service_name  full=y file=ABCD.dmp log=ABCD_exp.log

to export a schema(all the database objects belong to the user):

C:>exp scott/tiger@service_name  file=ABCD.dmp owner=SCOTT log=ABCD_exp.log

to export tables:

C:>exp scott/tiger@service_name  file=ABCD.dmp tables=(exp,dept) log=ABCD_exp.log

if you do not want the records in the tables, you can use rows=N to do that.

For more details, see :
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/toc.htm
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Export, Import is a good choice.
see
http://oldlook.experts-exchange.com/Databases/Oracle/Q_20774214.html
for other possibility.
0
 

Author Comment

by:iit
Comment Utility
thank you for the comments.

I agree exp/imp are great tools to backup. I should have posted my clearly like this,

I want to *extract ddl* that creates my whole database with package, procedures, tables etc. For this dbms_metadata.get_ddl is a great tool. But, as discussed at this link
http://www.dba-oracle.com/art_builder_get_schema_syntax.htm

there might be problems creating the db when tables have referential constraints, unless the parent tables in the relationship are created first.  It is discussed in the section "Issues with dbms_metadata"
My question is
How do I use dba_constraints and dba_cons_columns to retrieve the tables in the proper order?

sorry for the confusion.

thank you very much for your comments!
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 2

Expert Comment

by:Kong
Comment Utility
You could write a convoluted query/sub-program to do this but the problem will be with circular referencing tables.

Make your life easier, create all tables first or run the resultant scripts from get_ddl a few times ignoring the ORA-00955 errors. Oracle does it all the time - ever seen the output of running catalog.sql and catproc.sql? :-)
0
 

Author Comment

by:iit
Comment Utility
Kong: Thank you for the comments.

Do you have any such sample scripts?
0
 
LVL 2

Assisted Solution

by:Kong
Kong earned 25 total points
Comment Utility
iit, use dbms_metadata.get_ddl function to get the ddl, you can generate the scripts to call the function eg:

SET PAGES 0 HEAD OFF LINES 200 LONG 30000
SELECT 'SELECT dbms_metadata.get_ddl(''' || object_type
       || ''', ''' || object_name || ''') FROM DUAL;'
  FROM user_objects
 WHERE object_type NOT LIKE '%PARTITION';

Then run the results. RE: referencial constraints - don't bother writing a script that creates tables in the right order, no script will handle circular dependencies.

HTH

K
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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