Archive

Archive for the ‘Data Migration’ Category

Oracle – Datapump example

August 4, 2011 1 comment

To run Oracle Datapump you will need to create a datapump directory where your dump files will be located.

SQL> CREATE DIRECTORY datadir AS '/oaslive/dpdump';

The datapump command for export is as follows:

expdp user/password@orasid tables=tmp_interface_definitions, tmp_interface_registrations directory=datadir dumpfile=OASLIVE_InterfaceExport.dmp logfile=OASLIVE_InterfaceExport.log

The datapump command for import is as follows:

impdp user/password@orasid tables=tmp_interface_definitions, tmp_interface_registrations directory=datadir dumpfile=OASLIVE_InterfaceExport.dmp logfile=OASLIVE_InterfaceImport.log

Further reading

http://download.oracle.com/docs/cd/B12037_01/server.101/b10825/dp_export.htm

http://download.oracle.com/docs/cd/B13789_01/server.101/b10825/dp_import.htm

Oracle – SQL Loader example


The followin script is an example of how to use sql*loader with a control file and external table. It is based on importing vmstat information into a database table. The information from vmstat is stored in a ‘,’ delimited file called ext_vmstat.txt :

Create table for the external data to go into:

CREATE TABLE oasis.ext_vmstat
(
ProcessRunTime     Number(10,0)
,ProcBlocked       Number(10,0)
,UsedVirtualMemory Number(10,0)
,IdleMemory        Number(10,0)
,BufferMemory      Number(10,0)
,CacheMemory       Number(10,0)
,SwappedIn         Number(10,0)
,SwappedOut        Number(10,0)
,BlocksIn          Number(10,0)
,BlocksOut         Number(10,0)
,Interrupts        Number(10,0)
,ContextSwitches   Number(10,0)
,UserTime          Number(10,0)
,SystemTime        Number(10,0)
,IdleTime          Number(10,0)
,WaitingTime       Number(10,0)
,SnapshotTime      Varchar2(8)
,RowInsertDate     Date
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY ext_vmstat_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
FIELDS TERMINATED BY ','
)
LOCATION ('ext_vmstat.txt')
)
REJECT LIMIT unlimited
/

Create control file for SQL*Loader vmstat.ctrl:

LOAD DATA
INFILE 'ext_vmstat.txt'
BADFILE 'ext_vmstat.bad'
DISCARDFILE 'ext_vmstat.desc'
APPEND
INTO TABLE sys.ext_vmstat
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ProcessRunTime
,ProcBlocked
,UsedVirtualMemory
,IdleMemory
,BufferMemory
,CacheMemory
,SwappedIn
,SwappedOut
,BlocksIn
,BlocksOut
,Interrupts
,ContextSwitches
,UserTime
,SystemTime
,IdleTime
,WaitingTime
,SnapshotTime
,RowInsertDate SYSDATE)

To run SQL*Loader you issue the following:

sqlldr username@server/password control=vmstat.ctl

To download data from the database to a flat file you can use the following:

set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool spool.txt
select col1, col2, col3
from tab1
where col2 = ;
spool off

The set colsep ‘,’ separates each column by a comma. This saves having to type ||’,’|| after every column.

Oracle – Migrate database from Oracle 9i to Oracle 10g

October 18, 2010 1 comment

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

  1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
  2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
  3. So as not to run out of undo tablespace create the following script and store it on the 9i system:

    create table tmp_users as select * from dba_users;
    delete tmp_users;

    Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

  4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:

    userid=<user>/<password>@<oracle_sid>
    log=exp.log
    full=y
    consistent=y
    filesize=5G
    file=/u02/oracle/oraback/<oracle_sid>/export/exp1.dmp

  5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.

    nohup exp parfile=export.par &

  6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

  1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:

    nohup imp parfile=import.par &

  2. Create the export parameter file on the 10g system:

    userid=<user>/<password>@<oracle_sid>
    log=import.log
    full=y
    commit=y
    feedback=50000
    buffer=10000000
    file=exp1.dmp

  3. Create a transactional database using DBCA in a VNC session
  4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

  1. Check backup to tape has been successful
  2. Cleardown any old backups if space is required before export
  3. Run the sql script for undo tablespace retention
  4. In a separate session start the export using the previously created export shell script
  5. Tail nohup.out to see when the export has completed

    $  tail -f nohup.out

FTP the files to the 10g server

  1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

  1. In a VNC session run the import shell script created previously