Create BIG table space
sqlplus / as sysdba
sql> CREATE BIGFILE TABLESPACE "DPW04N4_DATA" DATAFILE '/home/beagw/dpwa/sparcsn4.dbf' SIZE 120G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTOSet up users, roles etc
sqlplus / as sysdba
create profile DPWORLD_NOEXPIRE limit password_life_time UNLIMITED;
create role webfocus;
create role report_prod;
create user billsys identified by Humvee;
create user sparcsn4 identified by Humvee;
alter user sparcsn4 profile DPWORLD_NOEXPIRE;
alter user sparcsn4 default tablespace DPW04N4_DATA;
Create impdp parameter file to exclude some huge files
# vi n4-imp.par
exclude=TABLE:"IN ('EDI_TRANSACTION','ROAD_DOCUMENTS','FRM_JOB_LOG','EDI_TRANSACTION_KEYWORD','EDI_BATCH_PROCESS','EDI_INTERCHANGE','EDI_EVENT','EDI_SEGMENT','EDI_BATCH','EDI_ERROR')"
Import database
# impdp dumpfile=sparcsn4_30042013.dmp parfile=n4-imp.par
[oracle@marauder ~]$ impdp system/Humvee dumpfile=sparcsn4_30042013.dmp parfile=n4-imp.par
Import: Release 11.2.0.1.0 - Production on Tue Oct 8 12:40:44 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=sparcsn4_30042013.dmp parfile=n4-imp.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SPARCSN4" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SPARCSN4"."SRV_EVENT" 11.21 GB 102943957 rows
. . imported "SPARCSN4"."ARGO_CHARGEABLE_UNIT_EVENTS" 6.960 GB 13282798 rows
. . imported "SPARCSN4"."SRV_EVENT_FIELD_CHANGES" 4.835 GB 123798109 rows
. . imported "SPARCSN4"."ARGO_PROPERTY_SOURCE" 3.030 GB 52470519 rows
..
..
. . imported "SPARCSN4"."CRG_PRODUCT_TYPE" 0 KB 0 rows
. . imported "SPARCSN4"."XPS_WHEELEDMT_FILT_WEIGHTS" 0 KB 0 rows
. . imported "SPARCSN4"."XPS_WHERENETALARM" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SPARCSN4"."ROAD_DOCUMENT_MESSAGES" ADD CONSTRAINT "FKE1FA50918010750D" FOREIGN KEY ("DOC_GKEY") REFERENCES "SPARCSN4"."ROAD_DOCUMENTS" ("GKEY") ENABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 1, column 3747:
PLS-00103: Encountered the symbol "~" when expecting one of the following:
..
..
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
Ignore the table statistics creation errors.
Create new database to contain the complete schema.
De tabellen die niet zijn geimporteerd in de eerste database kunnen in iedfer geval via de SQl create scripts vanuit deze tweede database worden gecreeerd.
Maak de tabelspace kleiner want er komt geen data in deze database terecht.
$ dbca
$ export ORACLE_SID=dpwaschema
$ sqlplus / as sysdba
CREATE BIGFILE TABLESPACE "DPW04N4_DATA" DATAFILE '/home/beagw/dpwaschema/sparcsn4.dbf' SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
create profile DPWORLD_NOEXPIRE limit password_life_time UNLIMITED;
create user sparcsn4 identified by Nephew default tablespace DPW04N4_DATA profile DPWORLD_NOEXPIRE;
create role webfocus;
create role report_prod;
create user billsys identified by Humvee;
$ find . -name "sparcsn4_30042013.dmp" -print
./app/oracle/admin/dpwa/dpdump/sparcsn4_30042013.dmp
[oracle@marauder ~]$ mv ./app/oracle/admin/dpwa/dpdump/sparcsn4_30042013.dmp ./app/oracle/admin/dpwaschema/dpdump/sparcsn4_30042013.dmp
$ impdp dumpfile=sparcsn4_30042013.dmp CONTENT=METADATA_ONLY
Create missing tables
Maak de gemiste tabellen aan in de eerste database.
EDI_INTERCHANGE
EDI_BATCH
EDI_BATCH_PROCESS
EDI_INTERCHANGE
EDI_TRANSACTION
EDI_TRANSACTION_KEYWORD
EDI_ERROR
EDI_EVENT
EDI_SEGMENT
FRM_JOB_LOG
ROAD_DOCUMENTS
sql> truncate table sparcsn4.road_document_messages;
sql> ALTER TABLE "SPARCSN4"."ROAD_DOCUMENT_MESSAGES" ADD CONSTRAINT "FKE1FA50918010750D" FOREIGN KEY ("DOC_GKEY") REFERENCES "SPARCSN4"."ROAD_DOCUMENTS" ("GKEY") ENABLE