I am supporting a system that uses export for backup. I want to test the import command to create a new database on a different machine. The system is running on Windows. I am having problems with it.
The export command is as below:
EXP SYSTEM/password COMPRESS=N CONSISTENT=N CONSTRAINTS=Y FILE=CBA3LIVE.DMP FULL=Y GRANTS=Y INDEXES=Y LOG=CBA3LIVE.LOG
I run ORADIM to create a new instance and then create database to create the new database, and @catalog.sql and @catproc.sql. I then run the import command:
imp system/password full=y file=cba3live.dmp log=cba3live_new.log.
The system returns a lot of errors, "IMP-00058: ORACLE error 1552 encountered" and "ORA-01552: cannot use system rollback segment for non-system tablespace."
Any help would be appreciated.
When you created the new database, you did not create any rollback segments. Oracle includes the SYSTEM rollback segment, located in the SYSTEM tablespace. But this rollback segment cannot be used for non-SYSTEM tablespace operations, as the error indicates.
I am going to assume that you are using Oracle 9i or 10g since these versions are the most recent. You'll want to create an UNDO tablespace for the undo segments. Undo segments are the replacement for the older rollback segments. To get your database set up correctly, peform the following:
CREATE UNDO TABLESPACE undo_tbs DATAFILE '/directory/undo_tbs01.dbf' SIZE 2000M AUTOEXTEND ON NEXT 500M MAXSIZE 10000M; ALTER SYSETM SET undo_tablespace = undo_tbs SCOPE=spfile; ALTER SYSEM SET undo_management = auto SCOPE=spfile;
Once you have completed the above in your new database, then stop and restart the instance for the settings to take affect. After that, try your import operation again.
This was first published in April 2006