Export All Schema And User Permission - Then Import To New Server?
Nov 2, 2010
I have database in 9.2.0.3 on windows 2003 R2 one server and i have serve with 10.2.0.4 64 bit on windows 2008 R2 64 bits. I want to move database from 9.2.0.3 to new server on 10.2.0.4.
!)should i do the cold backup of 9.2.0.3 and then create db instance on new server and then use dimutility to create new instance and then run the patch upgrade.
!!) I would like to do export all schema and user permision (is if possible?)export and then import to new server?
I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.
1)Want to perform an Export from a Production Schema and Import the results into Test Schema. BUT, do not want to export ALL objects from Production (only a subset of tables). Is this possible?doco on how to do this? (rather than a complete Export and then a complete Import).
2)I have 2 test instances of Oracle on the same development server, UNIT and SIT. Am using Oracle SQL Developer tool. While in the UNIT instance, is there a way to select data from the SIT instance? An example of syntax to use?
3) Can tables in the UNIT instance be compared to tables in the SIT instance, through any existing Oracle utilities
Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?
I am using Oracle 10g Data Pump Export utility expdp. What I am trying to do is to export a single schema, except for a certain partition P in table T.
Imagine you have 100 schemas backed up (expdp) in a dumpfile and you want to import just one schema from that dumpfile in a DB. You can specify just that one schema you want using SCHEMAS parameter in the impdp. But things are not straightforward when you want use REMAP_SCHEMA.
Here is my scenario: ===================
I took the expdp dump of schemas A and B in one go. So, dumpfile has objects from both A and B.The dumpfile name is : schemas_AandB.dmpNow , I want to create schema C from A using REMAP_SCHEMA parameter
-- Putting each parameter in a separate line for readability impdp PSTREF/PSTREF_123 DIRECTORY=ADET_EFX_DIR DUMPFILE=schemas_AandB.dmp LOGFILE=CreatingCfromA-Impdp.log REMAP_SCHEMA=A:CEverything goes fine. Schema C is created from Schema A in the dumpfile.
But impdp is trying to create schema B as well because schema B was present in the dumpfile. Since the schema B and its objects are already in the DB , I get the following errors.
ORA-31684: Object type USER:"B" already exists ORA-31684: Object type PROCEDURE:"B"."SP_CLEAREXPIREDSESSIONDATA" already exists ORA-31684: Object type PROCEDURE:"B"."SP_DELETESESSIONDATA" already exists ORA-31684: Object type PROCEDURE:"B"."SP_DELETESTATECONTEXTINFO" already exists
[code]...
Trying to avoid schema B in the dumpfile from being imported by specifying SCHEMASBut I got the following error ORA-39065: unexpected master process exception in MAIN ORA-12801: error signaled in parallel query server PZ99, instance oracth214:HEWRAC1 (1) ORA-01460: unimplemented or unreasonable conversion requestedMaybe REMAP_SCHEMA and SCHEMAS parameters won't work together.
Is there any way to prevent the impdp from importing user B and its objects ?
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/******** schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
I have a process to export a schema using expdp and import using impdp. Everything creates successfully except for a trigger. The trigger gives and error that the table or view does not exist. The account that I use to import the schema is different than the schema user but is a highly privileged account. I notice that the schema in the create or replace trigger line of code is remapped (I am using remapping in the impdp syntax) and the rest of the syntax of the trigger (which is just a sequence trigger for a primary key column) does not have the schema. In order to fix the issue, I have my bash script log into oracle as the schema user after the import of the schema and execute the trigger code. why do I have to do this for trigger code but not for other objects like views that create just fine.
I've got a schema that I've truncated all tables. I have a full schema export I took awhile back, and I'm wanting to import this into the schema to basically 'reset' it.
First time run, I got the :
ORA-39151: Table "xyz.tablename" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
I've been reading through, and see suggestions to add to the par file:
CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND
And I've seen others use the option for:
table_exists_action=replace
I basically want to put the data back into the tables, and have the indexes rebuilt.....
i have a .dmp file and i want to use the data in this file for my further practices. so, i need to dump the data in the .dmp file to the any schema exists in data base.
I am importing a dump into 11g Database, the dump taken from Old Financial release 6 [EBIZ]
i given this command:
IMP FILE=EXPDUMP.DMP BUFFER=524288000 FULL=Y IGNORE=Y Import user system and password given...
Once its started and getting error
+"ALTER USER "PERFUSER" QUOTA UNLIMITED ON "OTHERD""+ IMP-00003: ORACLE error 1918 encountered ORA-01918: user 'PERFUSER' does not exist
[Code]....
what is the wrong,,
I have created all the data files same like Old Database. so while importing All the USERs will created, if its correct then why this "*ORA-01435 : User does not exist*" coming...
We have troubles with import of transportable tablespace, when i try to import it with system the import is done currectly, when i try to use another user we receive this message :
ORA-31626: il job non esiste ORA-31633: impossibile creare la tabella principale "BMCESE.SYS_IMPORT_TRANSPORTABLE_05" ORA-06512: a "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: a "SYS.KUPV$FT", line 1020 ORA-00959: tablespace 'RMCCO_RMC_UTZ_PP080531' inesistente
this happens only on development server and it work into test server, so i don't think that the problem is in grants(i controlled that are the same and i have tried with DBA grant too and we received the error).Another strange thing is that tablespace 'RMCCO_RMC_UTZ_PP080531' is not included into the dump that i try to import and it doesn't exist in the database.
I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?
Why do export-import require temporary tablespace? Since export-import do behave like DMLs, when does temporary tablespace be needed by datapump utility?
I have two schema named ODI_MASTER and ODI_WORK.Under ODI_WORK there are some tables like TEMP1, TEMP2 Further more when any new tables will create under ODI_WORK, then i need automatically grant select permission to ODI _MASTER schema.
for this purpose i choose trigger, and a Stored procedure.
CREATE OR REPLACE TRIGGER ODI_WORK.TRIG1 AFTER CREATE ON ODI_WORK.SCHEMA ENABLE call sp1 (ora_login_user)
[code]...
I searched a lot over blogs, if EXECUTE IMMEDIATE commands exist under Trigger it gets problem. Insert/update/delete statement on trigger seems no problem.
when i try to export schema using expdp i got error Like
Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 6.25 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
