Re: Export Tablespaces

Use RMAN and clone the database. Much easier and faster than exp/imp.

FlameDance wrote:
> Hello everyone,
>
> under Oracle 8.1.7 and redhat linux 8 I want to create a daily copy of
> the production database (or a part of it) on the same machine for use as
> a test database.
>
> So far we have used
> exp user/password_at_instance
> and imported the tables but that doesn't suffice anymore, as we need
> procedures, functions, triggers, packages, etc too.
>
> exp system/password_at_instance file=mydump full=y
>
> works (after giving the EXP_FULL_DATABASE role to system) but I've been
> warned that a full import will attempt to overwrite the production
> database instead of creating the test database.
>
> Another problem is that there is a huge table in the database that uses
> 1.7GB and that the Datafiles are only 2GB big. If the table get's
> fragmented over 2 datafiles, there's trouble ahead, I've been told (I
> haven't verified that yet).
>
> So my idea is to export tablespaces and reuse them in the test database.
> That would avoid both problems (I hope) and even be faster than writing
> and reading export files.
>
> However, I can't seem to get it right, in various wording I get error
> messages. Whatever I try the exp utility throws error messages at me.
>
> For any hint for solution I'd be thankful.
>
> For the record: sqlplus '/ as sysdba' works.
>
> Stephan
>
> ---------------------------------------------------
> First as documented in
> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#5697
>
> ---------------------------------------------------
> exp file=famtbl.dmp TRANSPORT_TABLESPACE=y TABLESPACES=(tbla,tblb)
> TRIGGERS=y CONSTRAINTS=y GRANTS=y
>
> Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:13:57 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> Username: sys as sysdba
> Password:
>
> EXP-00056: ORACLE error 1034 encountered
> ORA-01034: ORACLE not available
> ORA-27101: shared memory realm does not exist
> Linux Error: 2: No such file or directory
> EXP-00005: all allowable logon attempts failed
> EXP-00000: Export terminated unsuccessfully
> ---------------------------------------------------
> A couple of other attempts:
> ---------------------------------------------------
> -bash-2.05b$ exp "'/ as sysdba'" file=famtbl.dmp TRANSPORT_TABLESPACE=y
> TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y
>
> Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:20:46 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> EXP-00056: ORACLE error 1034 encountered
> ORA-01034: ORACLE not available
> ORA-27101: shared memory realm does not exist
> Linux Error: 2: No such file or directory
> EXP-00005: all allowable logon attempts failed
> EXP-00000: Export terminated unsuccessfully
> ---------------------------------------------------
> -bash-2.05b$ exp '/ as sysdba' file=famtbl.dmp TRANSPORT_TABLESPACE=y
> TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y
> LRM-00108: invalid positional parameter value 'as'
>
> EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
> EXP-00000: Export terminated unsuccessfully
>
> ---------------------------------------------------
> exp 'sys/password as sysdba' file=famtbl.dmp TRANSPORT_TABLESPACE=y
> TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y
>
>
> LRM-00108: invalid positional parameter value 'as'
>
> EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
> EXP-00000: Export terminated unsuccessfully
>
> ---------------------------------------------------
> exp "'sys/password as sysdba'" file=famtbl.dmp TRANSPORT_TABLESPACE=y
> TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y
>
> Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:10:31 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> EXP-00056: ORACLE error 1034 encountered
> ORA-01034: ORACLE not available
> ORA-27101: shared memory realm does not exist
> Linux Error: 2: No such file or directory
> EXP-00005: all allowable logon attempts failed
> EXP-00000: Export terminated unsuccessfully
>
> ---------------------------------------------------
> exp "'sys/password_at_instance as sysdba'" file=famtbl.dmp
> TRANSPORT_TABLESPACE=y TABLESPACES=(FAMDAT,FAMIDX) TRIGGERS=y
> CONSTRAINTS=y GRANTS=y
>
> Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:11:44 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> EXP-00056: ORACLE error 1017 encountered
> ORA-01017: invalid username/password; logon denied
> Username:
>
>
> ---------------------------------------------------
> -bash-2.05b$ exp "'system/password_at_instance as sysdba'" file=famtbl.dmp
> TRANSPORT_TABLESPACE=y TABLESPACES=(FAMDAT,FAMIDX) TRIGGERS=y
> CONSTRAINTS=y GRANTS=y
> Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:12:07 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> EXP-00056: ORACLE error 1031 encountered
> ORA-01031: insufficient privileges
>

-- 
Ron Reidy
Oracle DBA