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
Quote:
Originally posted by kgb
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.
woooow, i need a place to hide and brush up again.......:DQuote:
Originally posted by julian
[B]Oh, really ? :-)
Good, good, how about studying some Oracle too :-)
i did the home work and this is what i found...
I did full export of one database on node 1
the location of the datafiles was
SQL> select name from v$datafile;
NAME
---------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\ORADATA\ORCL\INDX01.DBF
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
E:\ORACLE\ORADATA\ORCL\USERS01.DBF
E:\ORADATA\PUBS.DBF
E:\ORADATA\TEST01.DBF
Now i created a fresh database on another machine which did not have the last two tablespaces and e:\oradata path.
i did the full import.
and i got
-----------------------------------------------------------------
Import: Release 9.2.0.2.1 - Production on Mon Jan 20 15:23:35 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "PUBS" BLOCKSIZE 16384 DATAFILE 'E:\ORADATA\PUBS.DBF' SI"
"ZE 52428800 AUTOEXTEND ON NEXT 2097152 MAXSIZE 65535M EXTENT MANAGEM"
"ENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORADATA\PUBS.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "TEST" BLOCKSIZE 16384 DATAFILE 'E:\ORADATA\TEST01.DBF' "
"SIZE 14680064 AUTOEXTEND ON NEXT 16384 MAXSIZE 65535M EXTENT MANAGEM"
"ENT LOCAL UNIFORM SIZE 4194304 ONLINE PERMANENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORADATA\TEST01.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
-------------------------------------------------------------------
So what i would like to say is that i was right on the part that yes you need to create tablespaces if the new machine does not have the same filesystem structure as on the previous machine
or
create the file systems on which the datafiles were present.
And yes i was wrong on the part that the "datafiles will be created in $ORACLE_BASE/oradata"
and about the part
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.
Well KGB,
It does happen that another DBA takes the backup and you just get the dump files for import, in such cases you are not sure if the export was taken with compress=Y or Compress=N. and Compress=Y being the default parameter, most export dumps come with that option enabled.
No hard feelings, just an explanation to what i wrote and why i wrote.
Any comments??
Cheers
Amar
Adewri,
Do you know difference between "Oracle 8.1.5" and "Oracle 9.2.0.2.1". ?
About, lessons:
If I have a chance, I am always glad to take lessons :-)
From question:
... We have a Oracle 8.1.5 database running on Solaris 2.8. Every night full exports are taken on this database. ...
I don't know how is this remark relevant to adewri's answer (or to the whole discused isue, for that matter). I do not know of any important changes in exp/imp utilities between the two releases regarding the isues that adewri has pointed out in his reply.Quote:
Originally posted by kgb
Do you know difference between "Oracle 8.1.5" and "Oracle 9.2.0.2.1". ?