Printable View
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
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.
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
Quote:
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
Thats because...Quote:
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
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
This part says me that you have no idea what are you talking about:Quote:
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.
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.
Oh, really ? :-)Quote:
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.
Good, good, how about studying some Oracle too :-)Quote:
I studied the lives of great men and famous women ...
NOT AT ALL!!!!!! Now this says, definitely you need to take a small training on export/import.Quote:
the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.
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:Quote:
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.
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
Thanks to all who responded, as the above worked fine for me.Quote:
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