[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

Oracle 9i export - can not full export a database

I have an Oracle 9i database, 150GB tablespace, 1000 users are using.
Maximum rows in bigest table about: 115milon record, keep data for 6 months.
It is running on Window 2000 Advance server.

But when I move to other server, I want to full export this database, but can not.
I'm looking for any solution how to fix it
-------------------------------------------------------------------
Error message is:



J:\BackupDebug201001>exp system/manager direct=y feedback=50000 file= J:\BackupDebug201001\fcvpcbaDe
bugHN_full.dmp full=y log=J:\BackupDebug201001\fcvpcbaDebugHN_full.log

Export: Release 9.2.0.1.0 - Production on Thu Jan 7 08:14:55 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
-------------------------------------------------------------------------

After run to:  exporting object type definitions
It was stop for long long time, exported file size not change.
If cause is object errors, how to identify which is error objects ?
Export script is:

exp system/manager direct=y feedback=50000 file= J:\BackupDebug201001\fcvpcbaDebugHN_full.dmp full=y log=J:\BackupDebug201001\fcvpcbaDebugHN_full.log


Thank you.





0
OneClickfcv
Asked:
OneClickfcv
  • 4
  • 3
  • 2
  • +1
1 Solution
 
ravindran_eeeCommented:
Why don't you think about this approach? Taking backup and restoring it on the new server?

http://www.experts-exchange.com/Database/Oracle/Q_20475581.html

I guess that would be faster and reliable. I assume you just need to move the entire database to another server. In that case backup and restore would be a better option.

I think you can also just copy the entire set of files (Control files, redolog files, etc) and place it in the new server. In this approach all your Oracle parameters should be the same. Just firing up the Oracle DB in new server should work after that.
0
 
raju1105Commented:
Hi,

why dont you test your backup without data i mean say rows=n statistics=none  and also try to avoid exporting statistics.

0
 
Shaju KumbalathCommented:
Select owner,object_name from dba_objects where status = 'INVALID' and owner in ('SYS','SYSTEM');
IF u find anyinvalid  objects
Run utlrp.sql
which compile all your objects,
You will find it under your $ORACLE_HOME\rdbms\admin  folder
 


0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
OneClickfcvAuthor Commented:
Dear Sharjukg
I followed your guide
But when I run this script, I can not fix invalid object,
Error message is below, please kindly guide me any more solution ?


SQL> @E:\oracle\ora92\rdbms\admin\utlrp.sql;

PL/SQL procedure successfully completed

Table created

Table created

Table created

Index created

Table created

Table created

CREATE OR REPLACE VIEW utl_recomp_all_inv AS
   SELECT o.obj#, o.type#, o.owner# FROM obj$ o
      WHERE o.remoteowner IS NULL AND o.status in (4, 5, 6) AND
            o.type# IN (1, 2, 4, 7, 8, 9, 11, 12, 13, 14, 22, 29, 32, 33)

ORA-00942: table or view does not exist

CREATE OR REPLACE view utl_recomp_schema_inv AS
   SELECT o.obj#, o.type#, u.name AS owner FROM utl_recomp_all_inv o, user$ u
      WHERE o.owner# = u.user#

ORA-00942: table or view does not exist

Package created
No errors for PACKAGE HN_DBF.UTL_RECOMP

Warning: Package body created with compilation errors
Errors for PACKAGE BODY HN_DBF.UTL_RECOMP:

LINE/COL ERROR
-------- -----------------------------------------------
60/29    PL/SQL: ORA-00942: table or view does not exist
59/10    PL/SQL: SQL Statement ignored
63/17    PL/SQL: ORA-00942: table or view does not exist
63/10    PL/SQL: SQL Statement ignored
76/44    PL/SQL: ORA-00942: table or view does not exist
76/7     PL/SQL: SQL Statement ignored
103/14   PL/SQL: ORA-00942: table or view does not exist
103/7    PL/SQL: SQL Statement ignored
182/35   PL/SQL: ORA-00942: table or view does not exist
181/13   PL/SQL: SQL Statement ignored
187/35   PL/SQL: ORA-00942: table or view does not exist
186/13   PL/SQL: SQL Statement ignored
195/35   PL/SQL: ORA-00942: table or view does not exist
194/13   PL/SQL: SQL Statement ignored
201/35   PL/SQL: ORA-00942: table or view does not exist
200/13   PL/SQL: SQL Statement ignored
265/42   PL/SQL: ORA-00942: table or view does not exist
264/10   PL/SQL: SQL Statement ignored
315/40   PL/SQL: ORA-00942: table or view does not exist
315/7    PL/SQL: SQL Statement ignored

begin utl_recomp.recomp_serial(); end;

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "HN_DBF.UTL_RECOMP" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1

begin dbms_registry.validate_components; end;

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_REGISTRY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>
0
 
OneClickfcvAuthor Commented:
raju1105 said
Hi,

why dont you test your backup without data i mean say rows=n statistics=none  and also try to avoid exporting statistics.
-------------------------------

I already test it, but bad situation still not change. please kindly guide me any more solution ?
0
 
raju1105Commented:
Hi,

Did u try exporting one schema at a time.... try without data i.e. rows=n and statistics=none

Also, did u check status of your database components... in dba_registry
0
 
Shaju KumbalathCommented:
are running the query as sys?
0
 
OneClickfcvAuthor Commented:
Yes, I run recombine all object as sys user.
0
 
raju1105Commented:
Hi,

Did u try exporting one schema at a time.... try without data i.e. rows=n and statistics=none

Also, did u check status of your database components... in dba_registry
0
 
OneClickfcvAuthor Commented:
At first, It have errors when I recombine objects, but after that, it is ok, tks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now