Hi,
I have a full export of a database which i need to import on a new server. I dont have the database, just Oracle software installed.
What are the steps I need to follow to create a skeleton database and import the full export.
Thanks
Anurag
Printable View
Hi,
I have a full export of a database which i need to import on a new server. I dont have the database, just Oracle software installed.
What are the steps I need to follow to create a skeleton database and import the full export.
Thanks
Anurag
1. Create the target db (if it's not created yet)
2. Configure the ts/dbf at target db to be that of the source db
3. Configure rbs in target db
4. Perform import.
Hi ,
Thanks for the reply.
You mean to say that I need to create a database with the datafiles/tablespaces and segment names exactly the same as the source database. Wont this create a system table space and also create objects in it.
Does this mean that you do not import the system user from the export.
Thanks
Anurag
Well, you can just let the full export file create the db for you.
How do I let the full export create the database for me.
Thanks
Anurag
Any inputs on how will the export create a database for me.
Thanks
Anurag
The database can not be created by exp/imp, no way. You have to create the instance and the database manualy, only then can you execute the import. However you don't need to create the complete database, ie don't need to create all the tablespaces, users etc... Creating the database with a SYSTEM tablespace, an additional RB segment in sytem TS and users SYS and SYSTEM is sufficient, provided that imp will be able to create the datafiles exactly es they were in the original database (ie it will be able to create exactly the same directory structure for database files).
If we have to create a system table space manually then whats the use of it being exported in a full export. In any case export of the system tablespace is useless then.
Thanks
Anurag
I'm affraid you'll have to dig into manuals a bit and try to find out what are the basic concepts of export and why is it used for.Quote:
Originally posted by anuragmin
If we have to create a system table space manually then whats the use of it being exported in a full export. In any case export of the system tablespace is useless then.
Thanks
Anurag
Unless your database is a total mess (meaning that any user appart from SYS has any object created in the system tablespace), nothing from the system tablespace is exported ever, no matter what kind of export you take. Remember that objects owned by SYS are never exported!
Hi,
Below is a sample of the log file generated with a full export.
According to jmodic system tablespace is not exported. I am not sure whether all of these are system tables but the ones with $ signs definitly should be.
Also if these are not system tables then can i just go ahead and delete them without affecting the day to day database operation.
Please confirm.
Thanks
Anurag
Connected to: Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Objects option
PL/SQL Release 8.0.5.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting foreign function library names
. exporting object type definitions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table CHAINED_ROWS 1620 rows exported
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALL 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 0 rows exported
. . exporting table DEF$_ERROR 0 rows exported
. . exporting table DEF$_LOB 0 rows exported
. . exporting table DEF$_ORIGIN 0 rows exported
. . exporting table DEF$_PROPAGATOR 0 rows exported
. . exporting table DEF$_TEMP$LOB 0 rows exported
. . exporting table EVT_CARRIER_CONFIGURATION 0 rows exported
. . exporting table EVT_DEST_PROFILE 0 rows exported
. . exporting table EVT_HISTORY 0 rows exported
. . exporting table EVT_INSTANCE 0 rows exported
. . exporting table EVT_MAIL_CONFIGURATION 0 rows exported
. . exporting table EVT_MONITOR_NODE 0 rows exported
. . exporting table EVT_NOTIFY_STATUS 0 rows exported
. . exporting table EVT_OPERATORS 0 rows exported
. . exporting table EVT_OPERATORS_ADDITIONAL 0 rows exported
. . exporting table EVT_OPERATORS_SYSTEMS 0 rows exported
. . exporting table EVT_OUTSTANDING 0 rows exported
. . exporting table EVT_PROFILE 5 rows exported
. . exporting table EVT_PROFILE_EVENTS 11 rows exported
. . exporting table EVT_REGISTRY 2 rows exported
. . exporting table EVT_REGISTRY_BACKLOG 0 rows exported
. . exporting table PLAN_TABLE 0 rows exported
. . exporting table SMACTUALPARAMETER_S 0 rows exported
. . exporting table SMAGENTJOB_S 0 rows exported
. . exporting table SMARCHIVE_S 0 rows exported
. . exporting table SMBREAKABLELINKS 0 rows exported
. . exporting table SMCLIQUE 0 rows exported
. . exporting table SMCONFIGURATION 1 rows exported
. . exporting table SMCONSOLESOSETTING_S 0 rows exported
. . exporting table SMDATABASE_S 0 rows exported
. . exporting table SMDBAUTH_S 0 rows exported
. . exporting table SMDEFAUTH_S 0 rows exported
. . exporting table SMDEPENDENTLINKS 0 rows exported
. . exporting table SMDISTRIBUTIONSET_S 0 rows exported
. . exporting table SMFOLDER_S 0 rows exported
. . exporting table SMFORMALPARAMETER_S 17 rows exported
. . exporting table SMGLOBALCONFIGURATION_S 0 rows exported
. . exporting table SMHOSTAUTH_S 0 rows exported
. . exporting table SMHOST_S 0 rows exported
. . exporting table SMINSTALLATION_S 0 rows exported
. . exporting table SMLOGMESSAGE_S 0 rows exported
. . exporting table SMMONTHLYENTRY_S 0 rows exported
. . exporting table SMMONTHWEEKENTRY_S 0 rows exported
. . exporting table SMMOWNERLINKS 0 rows exported
. . exporting table SMOMSTRING_S 2 rows exported
. . exporting table SMOSNAMES_X 0 rows exported
. . exporting table SMOWNERLINKS 19 rows exported
. . exporting table SMPACKAGE_S 0 rows exported
. . exporting table SMPARALLELJOB_S 0 rows exported
. . exporting table SMPARALLELOPERATION_S 9 rows exported
. . exporting table SMPARALLELSTATEMENT_S 0 rows exported
. . exporting table SMPRODUCTATTRIBUTE_S 0 rows exported
. . exporting table SMPRODUCT_S 0 rows exported
. . exporting table SMP_AD_ADDRESSES_ 0 rows exported
. . exporting table SMP_AD_DISCOVERED_NODES_ 0 rows exported
. . exporting table SMP_AD_NODES_ 0 rows exported
. . exporting table SMP_AD_PARMS_ 0 rows exported
. . exporting table SMP_AUTO_DISCOVERY_ITEM_ 0 rows exported
. . exporting table SMP_AUTO_DISCOVERY_PARMS_ 0 rows exported
. . exporting table SMP_BLOB_ 9 rows exported
. . exporting table SMP_BRM_ACTIVE_JOB_ 0 rows exported
. . exporting table SMP_BRM_CHANNEL_DEVICE_ 0 rows exported
. . exporting table SMP_BRM_DEFAULT_CHANNEL_ 0 rows exported
. . exporting table SMP_BRM_RC_CONNECT_STRING_ 0 rows exported
. . exporting table SMP_BRM_SAVED_JOB_ 0 rows exported
. . exporting table SMP_BRM_TEMP_SCRIPTS_ 0 rows exported
. . exporting table SMP_CREDENTIALS$ 0 rows exported
. . exporting table SMP_EBU_ACTIVE_JOB_ 0 rows exported
. . exporting table SMP_EBU_SAVED_JOB_ 0 rows exported
. . exporting table SMP_JOB_ 0 rows exported
. . exporting table SMP_JOB_EVENTLIST_ 0 rows exported
. . exporting table SMP_JOB_HISTORY_ 0 rows exported
. . exporting table SMP_JOB_INSTANCE_ 0 rows exported
. . exporting table SMP_JOB_LIBRARY_ 0 rows exported
. . exporting table SMP_JOB_TASK_INSTANCE_ 0 rows exported
. . exporting table SMP_LONG_TEXT_ 0 rows exported
. . exporting table SMP_REP_EXEC_STAT 0 rows exported
. . exporting table SMP_REP_VERSION 2 rows exported
. . exporting table SMP_SERVICES 1 rows exported
. . exporting table SMP_SERVICE_DATA_ 0 rows exported
. . exporting table SMP_SERVICE_GROUP_DEFN_ 0 rows exported
. . exporting table SMP_SERVICE_GROUP_ITEM_ 0 rows exported
. . exporting table SMP_SERVICE_ITEM_ 0 rows exported
. . exporting table SMP_UPDATESERVICES_CALLED_ 0 rows exported
. . exporting table SMP_USER_DETAILS 0 rows exported
. . exporting table SMP_VAB_ACTIVE_JOB_ 0 rows exported
. . exporting table SMP_VAB_SAVED_JOB_ 0 rows exported
. . exporting table SMP_VAR_LIST_DATABASES_ 0 rows exported
. . exporting table SMRELEASE_S 0 rows exported
. . exporting table SMRUN_S 0 rows exported
. . exporting table SMSCHEDULE_S 0 rows exported
. . exporting table SMSHAREDORACLECLIENT_S 0 rows exported
. . exporting table SMSHAREDORACLECONFIGURATION_S 0 rows exported
. . exporting table SMTABLESPACE_S 0 rows exported
. . exporting table SMVCENDPOINT_S 0 rows exported
. . exporting table SMWEEKLYENTRY_S 0 rows exported
. . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported
. . exporting table USER_PROFILE 0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. about to export MANDA's tables via Conventional Path ...
. . exporting table ADVISOR 906 rows exported
. . exporting table ASSET_DETAIL 5740 rows exported
. . exporting table ASSET_PROPERTY 248 rows exported
. . exporting table ASSET_TYPE 21 rows exported
. . exporting table ATTRIBUTE 7 rows exported
. . exporting table BUSINESS_FOCUS 8 rows exported
. . exporting table COMPANY 7513 rows exported
. . exporting table COMPANY_TYPE 42 rows exported
. . exporting table COMPARABLE_TRANSACTIONS 781 rows exported
. . exporting table CORPORATE_LINE_ITEM 2426 rows expo
I think you are messing things up when talking about "system".
About the "system" terminology:
1. SYSTEM is a name of tablespace, the tablespace that is created first during database creation and that contains database dictionary objects, which are owned by the user SYS. This is also the only tablespace that *must* be created in the database, all other tablespaces are optional (but of course, there is no production database on this planet with only this tablespace created ;)).
2. SYSTEM is also a name of the user that is usually created during database creation. This user is in fact not a particullarly "special" user, it is totaly ordinal user with DBA role. It is equivalent to any other user with simmilar privileges. In contrast, the user that realy is special is user SYS - this user is the owner of the database dictionary, the "heart" of the database.
3. "system" is also a word that is often used when refering to a database dictioanry or the core of the database
About the export in conjunction with "system":
Not that export never exports SYSTEM tablespace, it never exports *any* tablespace. Full export does export tablespace definitions (except for the deffinition of SYSTEM tablespace), but it doesn't export tablespaces in a sence "it will export all the segments from a particular tablespace". You can instruct export to export a particular tables or a particular users, but not to expoert a particular tablespace. It wouldn't be logical and it can't be done. When you do a full export, you will actually export all the users and their objects, no matter in which tablespace their objects reside, except for one user: SYS. None of SYS's objects will be exported, no matter if they are located in tablespace SYSTEM or any other tablespace.
About your $ objects in the export log:
This are realy not "system" tables, I mean they are not really a part of a datbase catalog. They are owned by user SYSTEM, not user SYS, that is why they are exported. Those tables are needed for some database options, in your case for advanced queuing and replication. As such they can be refered as a "kind of system" tables, so the question is why they are created in schema SYSTEM and not in schema SYS? There is only one, simple reason: becuse this way they can be exported! For those tables it is sometimes usefull if they can be exported and imported elsewhere, but for true dictionary tables owned by SYS Oracle does not (and must not) allow to be exported, es their import would corrupt the database totally.
As for deleting those SYSTEM.$ tables - yes, you can delete them without cousing database corruption, but the options they are required for will no longer work. So I wouldn't recomend to do so if you realy are using AQ or ttransaction replication.
I hope I haven't confused you even further ;)....
Thank you very much jurij, I really appreciate your help. This was indeed useful.
Now I need to refer to the manuals and first understand this concept thoroughly.
Regards
Anurag