Import Database from Windows

[Pinki Meggi]

Hi, thanks once again for your kind attention.

We are migrating from windows to hp-ux, and i need to copy all information from database running on windows system, i mean i need to have the same database running now on HP-UX.

so in your opinion i have to create first all datafiles, tablespaces, users, etc ... and after that import, just the data? It will be a tough job, but it is feasible. I prefered to do the import in one step, because the database is being used by the users of my company but if i have no option i can do it.


Thanks for help

Best Regards

Pinki

[Alexander M. Ermes]

Hi Pinki.
With the Oracle Enterprise Manager you can create many of the necessary scripts to create your new database ( DDL scripts ).
And give a thought to the item, whether you need locally managed tablespace, because you do not have that much influence on these.
For the export create a parameter file with all necessary options.
Rgds
Alexander M. Ermes

[Eric Antunes · Accepted solution]

Hi Pinki,

You may want to try to clone the database (this works fine from an HP-UX instance to create a new HP-UX instance but I never tried it with your situation).

Here are the steps...


In the source DB (windows):

shutdown immediate;

ftp the datafiles, redolog files, control files in BINARY mode and init.ora in ASCII mode to there new HP-UX destinations (init.ora is normally at $ORACLE_HOME/dbs)


In the destination DB (HP-UX):

Make sure init.ora is pointing to the correct control files location.

svrmgrl or sqlplus

connect / as sysdba;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
'/disc1/.../log01a.dbf',
'/disc2/.../log01b.dbf'
) SIZE 10M,
GROUP 2 (
'/disc1/.../log02a.dbf',
'/disc2/.../log02b.dbf'
) SIZE 10M
DATAFILE
'/.../system01.dbf',
'/.../rbs01.dbf',
'/.../temp01.dbf',
'/.../user1x01.dbf',
'/.../user1d01.dbf',
...
;

recover database; --it may error out but continue anyway...

alter database open resetlogs;


As I said, I never tried it but it may work...

Best Regards,

Eric Antunes

[Pinki Meggi]

Hi,

thanks a lot fo help


I will try it, hope it will work.

Best Regards

Pinki

[Yogeeraj_1]

hi pinki,

to generate the DDLs you can also use:

yd@MYDB.MU> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

Hope this helps too!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

for the generation of tablespace scripts you can also proceed as follows:


$ exp userid=/ full=y

Export: Release 8.1.7.4.0 - Production on Wed Jan 13 14:14:13 2006

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.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 context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions ^C <<<<<=== press CTRL-C
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully


$ grep 'CREATE TABLESPACE' expdat.dmp
CREATE TABLESPACE "RBS_TS_01" DATAFILE '/d01/oradata/ora817dev/rbs_ts_01.dbf'
SIZE 31563776 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_02" DATAFILE '/d02/oradata/ora817dev/rbs_ts_02.dbf'
SIZE 157392896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_03" DATAFILE '/d03/oradata/ora817dev/rbs_ts_03.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_04" DATAFILE '/d04/oradata/ora817dev/rbs_ts_04.dbf'
SIZE 48340992 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_05" DATAFILE '/d01/oradata/ora817dev/rbs_ts_05.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "USERS" DATAFILE '/d04/oradata/ora817dev/users.dbf' SIZE
661127168 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "DRSYS" DATAFILE '/d04/oradata/ora817dev/drsys.dbf' SIZE
36700160 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "XXX" DATAFILE '/tmp/xxx.dbf' SIZE 105373696 REUSE AUTOEXTEND
ON NEXT 104857600 MAXSIZE 32767M DEFAULT STORAGE(INITIAL 40960 NEXT 40960
MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ONLINE PERMANENT
CREATE TABLESPACE "TMP" DATAFILE '/tmp/tmp.dbf' SIZE 1048576 REUSE EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 65536
CREATE TABLESPACE "CLAMS" DATAFILE '/d03/oradata/ora817dev/clams.dbf' SIZE
78118912 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288

Alternatively, in Oracle 9i, you can again use the DBMS_METADATA package to get the tablespace scripts:

yd@MYDB.MU> select dbms_metadata.get_ddl( 'TABLESPACE', 'USERS' ) from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

CREATE TABLESPACE "USERS" DATAFILE
'/u01/oracle/p1/oradata/ora920/users01.dbf' SIZE 26214400 REUSE
AUTOEXTEND ON NEXT 1310720 MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO


hope this helps too!


kind regards
yogeeraj

[R. Allan Hicks]

You might find it helpful to use RMAN or sqlplus to back up the control file to trace on the source system. Then, on the target system take the control file from the source and modifiy the database file locations (since windows and HPUX won't agree on the locations). Build the database on the target prior to attempting to import the source. Since the datafiles are already built, the imp won't (or at least should not) try to build them. I believe that was the source of your problems early on where imp complained about trying to create a data file at E:/.....

Hope this helps. I've moved from HPUX to linux using imp so it takes care of the Big Endian/Little Endian compatibility problem.

-Hope this helps

[SteveKirby]

A database clone will not work. You must export/import.

The 'lazy' way to do the export/import is to:
- exp full=y to get everything on the Windows Box
- Create a new database on the HP-UX with the tablespaces you need and the same version or higher of the database (e.g. 9.2.0.7)
- Import the full Windows export into the HP.
- Re-run catalog/catproc to clean up orphan packages on HP-UX

A slightly better way is the user import rather than 'full', but I usually don't bother since the database I migrate often have a LOT of users.

[Simon Wickham_6]

Install Oracle on to the taget database and do a Export of your existing database then Import this will setup the structures. You will need to run catalog.sql and catproc.sql.

1. catalog.sql creates the data dictionary.
2. catproc.sql creates all structures required for PL/SQL.

Export
------
set ORACLE_SID=mysid
set ORACLE_HOME=c:oracleora9i
exp userid=system/manager file=c:exportsmysid.dmp
log=c:exportsmysid.log full=y

Import
------
imp userid=system/oracle file=c:exportsmysid.dmp log=c:\temp\uw.log

Monitor Import
--------------
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

Regards,
Simon