Re: Q:Copy production database to Q/A or test environment

From: Yury Velikanov <j.velikanovs_at_gmail.com>
Date: Mon, 18 Apr 2005 22:45:40 +0300
Message-ID: <d6f0def5050418124545c5df49@mail.gmail.com>

Amihay,

It is easy if you need to copy whole database.

If the requirements are to copy just DB structure with empty (SMALL) tables + some data, then it can be bit complex. I would like to highlight requirement so have SMALL tables. Using suggested exp/imp (rows=3Dn) method, Oracle creates initial extend for each segment within export file. If objects sized properly in production, initial extend for BIG tables can be 10-100-500MB. Imp for such DB creates not really SMALL empty tables.

If you would like to get really small copy of production DB, you have to use method which allows you to set initial extend for segments. There are couple of methods you can use to achieve this goal:

1. You can try to hack DMP file and change value of INITIAL parameter for each segment. Use PERL/SED/VI/WORD :), whatever you want, but be careful, the size of whole DMP file has to be the same after editing. If INITIAL parameter for some table is 26571432, you have to change it 000 32768 or " 32768".
2. You can try to extract TABLE/INDEXES/CLUSTERS definitions. Change storage cause and create these manually.
3. full export
4. create target db.
5. full imp, for types creation. You can need types to create type based tables (prevent TBS creation, to not allow Oracle to create initial extends for segments, create users before full imp)
6. create segments manually (tables, indexes, clusters, iot whatever you ha= ve)
7. full imp for triggers creation.

Extracting segments definitions not trivial. You have tree options here:

1. Try to extract DDLs from binary DMP file
2. Try to extract DDLs from imp log files created by imp show=3Dy
3. Try to extract DDLs from imp log files created by imp indexfile=3D<file =

name>.

May be you have to don't care how much takes you target database, then will forget all I described above and use just exp rows=3Dn + imp full=3Dy

Jurijs
+371 9268222 (+2 GMT)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html

On 4/18/05, Amihay Gonen <AmihayG_at_ectel.com> wrote:
> Hi ,

>=20
> I've a requirement to develop a process to copy production database (3Tb =
and
> above) at customer site to QA or test environment (Vendor side).

>=20


> The requirements are :

> *       Database structures - This include tables,indexes , code

> (package,trigger ...) , views ...(select count(*) from DBA_objects =3D 15=
0,000
> )
> * Data of main configurations tables. (total size of all tables is
> less then 200M).

>=20
> This copy is done in order to investigate problem in customer sites and t=
o
> verify that patches which will be release , will run well.

>=20

> I'm thinking on two ways to developer such process :
>=20
> 1. Full exp without data + A list of tables to export with data. Zip=
ped
> those files , transfer them to vendor site , import those files into a cl=
ean
> database.
> 2. Take a hot backup of system and undo tablespaces and the
> configurations tablespace (let's assume that all the configurations tabl=
es
> are in the same tablespace).Zipped those files , transfer them to vendor
> site , create a new instance , recover and open the database

>=20

> What do you think it , it's the best way to go ? Please note that this
> process should be done with minimal DBA resource in the process. (DBA is =
a
> rare resource :) )

>=20

> Amihay Gonen
> DBA,
> 972-3-9268280

>=20

> Be there ... -> http://golan/Forum%20DBA/default.aspx
> <http://golan/Forum%20DBA/default.aspx>

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 18 2005 - 15:49:49 CDT