Restoring A Full Export
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Restoring A Full Export

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Restoring A Full Export

    We have a Oracle 8.1.5 database running on Solaris 2.8. Every night full exports are taken on this database. We would like to verify that they indeed have worked, by importing the export file into/onto a second Solaris 2.8 machine.
    This second machine has Oracle 8.1.5 installed but no Instance or database. Can someone help me with the next steps or point me to a source that can?

    Thank you
    Ken Hammer

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    If the file systems are same on both machines, then you can just create new database/instance on second machine and then do full import. Before you start import make sure you set up environment variables like ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH, etc. on second server.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hi

    1. create a blank database.
    2. create all the tablespaces existing in the old database with datafile sizes 50% bigger than the original one.
    3. create seperate tablespace for the rollback segments.
    4. create monster rollback segments in that tablespace.
    5. make all other rollback segments offline
    6. Do a full import.

    HTS
    Amar
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Originally posted by adewri
    Hi
    1. create a blank database.
    2. create all the tablespaces existing in the old database with datafile sizes 50% bigger than the original one.
    3. create seperate tablespace for the rollback segments.
    4. create monster rollback segments in that tablespace.
    5. make all other rollback segments offline
    6. Do a full import.
    HTS
    Amar

    2. create all the tablespaces existing in the old database with datafile sizes 50% bigger than the original one.

    WHAT THE POINT OF IT ???

    I always do that and it always works:
    1. Create a blank database.
    2. Create RBS tablespace, the same as you have in your a dump file.
    3. Create TEMP tablespace.
    4. Restart database.
    5. Import with options: full=y commit=y feedback=1000
    Best wishes!
    Dmitri

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by kgb
    2. create all the tablespaces existing in the old database with datafile sizes 50% bigger than the original one.

    WHAT THE POINT OF IT ???

    I always do that and it always works:
    1. Create a blank database.
    2. Create RBS tablespace, the same as you have in your a dump file.
    3. Create TEMP tablespace.
    4. Restart database.
    5. Import with options: full=y commit=y feedback=1000
    Thats because...

    if you donot create tablespaces, the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.

    Creating a tablespace before gives you better control on your datafile locations.

    And 50% bigger so that in case export was taken with compress=Y, while import it will try to import the data in one extent. This can give problem it there is lot of data.

    So to be on the safer side create tablespaces with 50% more space. Cos you wont want to get frustrated importing huge amount of data again and again for these simple failures.

    HTH
    Amar
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Originally posted by adewri
    if you donot create tablespaces, the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.

    And 50% bigger so that in case export was taken with compress=Y, while import it will try to import the data in one extent. This can give problem it there is lot of data.

    So to be on the safer side create tablespaces with 50% more space. Cos you wont want to get frustrated importing huge amount of data again and again for these simple failures.
    This part says me that you have no idea what are you talking about:

    if you donot create tablespaces, the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.

    This part says me that you do not know what you do:

    And 50% bigger so that in case export was taken with compress=Y, while import it will try to import the data in one extent. This can give problem it there is lot of data.
    Best wishes!
    Dmitri

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by adewri
    Thats because...

    if you donot create tablespaces, the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.

    Oh, really ? :-)

    I studied the lives of great men and famous women ...
    Good, good, how about studying some Oracle too :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Apr 2002
    Posts
    291
    the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.
    NOT AT ALL!!!!!! Now this says, definitely you need to take a small training on export/import.

    Import definitely fails if the same tablespaces are not found.
    PNRDBA

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pnrdba
    NOT AT ALL!!!!!! Now this says, definitely you need to take a small training on export/import.

    Import definitely fails if the same tablespaces are not found.
    It will not fail definitely. You will have problems with large objects but look at what I just did:

    SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jan 17 13:05:10 2003

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

    SQL> conn / as sysdba
    Connected.

    SQL> select tablespace_name from dba_tablespaces;

    TABLESPACE_NAME
    --------------------------------------------------------------------------------
    SYSTEM
    UNDOTBS
    TEMP
    SAPDW_DATA
    SAPDW_INDEX
    USERS

    6 rows selected.

    SQL> create user A1 identified by a1 default tablespace SAPDW_DATA;

    User created.

    SQL> create user A2 identified by a2 default tablespace USERS;

    User created.

    SQL> grant connect, resource to A1;

    Grant succeeded.

    SQL> grant connect, resource to A2;

    Grant succeeded.

    SQL> conn A1/a1
    Connected.

    SQL> create table t1 (abc timestamp);

    Table created.

    SQL> insert into t1 values (systimestamp);

    1 row created.

    SQL> commit work;

    Commit complete.

    SQL> exit

    Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
    JServer Release 9.2.0.2.0 - Production

    sapdw_v70:/opt/oracle9>cd /u04/arch1/sapdw_v70
    sapdw_v70:/u04/arch1/sapdw_v70>ls -lrt

    total 20496
    -rw-r----- 1 oracle92 dba 10484736 Jan 13 20:14 arc_1_48.arc

    sapdw_v70:/u04/arch1/sapdw_v70>exp userid=system/****** file=x.dmp owner=A1

    Export: Release 9.2.0.2.0 - Production on Fri Jan 17 13:13:25 2003

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

    Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
    JServer Release 9.2.0.2.0 - Production
    Export done in UTF8 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified users ...
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user A1
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user A1
    About to export A1's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export A1's tables via Conventional Path ...
    . . exporting table T1 1 rows exported
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully without warnings.
    sapdw_v70:/u04/arch1/sapdw_v70>

    SQL> conn / as ssydba
    SQL> drop tablespace sapdw_data including contents and datafiles;

    sapdw_v70:/u04/arch1/sapdw_v70>imp userid=system/***** file=x.dmp fromuser=A1 touser=A2

    Import: Release 9.2.0.2.0 - Production on Fri Jan 17 13:26:47 2003

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

    Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
    JServer Release 9.2.0.2.0 - Production

    Export file created by EXPORT:V09.02.00 via conventional path
    import done in UTF8 character set and AL16UTF16 NCHAR character set
    import server uses AL32UTF8 character set (possible charset conversion)
    . importing A1's objects into A2
    . . importing table "T1" 1 rows imported
    Import terminated successfully without warnings.

    SQL> select table_name, tablespace_name, owner from dba_tables where owner in ('A1','A2');

    TABLE_NAME
    --------------------------------------------------------------------------------
    TABLESPACE_NAME
    --------------------------------------------------------------------------------
    OWNER
    --------------------------------------------------------------------------------
    T1
    USERS
    A2
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Feb 2001
    Posts
    34
    Originally posted by kgb
    2. create all the tablespaces existing in the old database with datafile sizes 50% bigger than the original one.

    WHAT THE POINT OF IT ???

    I always do that and it always works:
    1. Create a blank database.
    2. Create RBS tablespace, the same as you have in your a dump file.
    3. Create TEMP tablespace.
    4. Restart database.
    5. Import with options: full=y commit=y feedback=1000
    Thanks to all who responded, as the above worked fine for me.
    Ken Hammer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width