IMP/EXP accross platforms: What will be missing?

Hello

Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
to do exp/imp. One instance, about 230 GB (size of Database files). My plan:

1) Do an export from the source with rows=no and freeze Database structure
2) Convert the exported dump file into executable sql scripts
3) Edit the scripts (removing storage params, etc.)
4) Generate a Database creation script with Version 9 (target environment)
5) Run the Database creation script from step 4, so creating a standard DB
6) Delete spfile, tune new Database init parameters
7) Test rman+Veritas setup (backup/restore) in new environment
8) Precreate tablespaces manually
9) Run the scripts from step 3 to precreate objects in Database from step 5
10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
11)Immediately start an import from pipe (fromuser= touser=) on target
12)Gather statistics of new Database
13)Shutdown source Database
14)Edit central tnsnames.ora for all clients, announce new platform
15)Drop example users (HR, SH, etc) cascade
16)Repeat step 7

Done.

In praxis: Not done (being realistic). I will test this of course, the
goal being a reliable procedure and a minimal downtime. But what
really concerns me is the kind of unexpected problems that I won't be
prepared to cope with due to my lack of experience (well, I have
already transferred a number of DBs this way, at times something fails
and has to be fixed manually).

These are the things that seem prone to failure. Please comment or put
your ideas:

a) Database Links. We have some.
b) Synonyms (no full import done...)
c) Roles
d) Object and system privileges
e) Sequences (?)
f) Constraints, triggers (?)
g) Resource Plans (yeah, they save admin's life during night jobs)
h) rdbms/admin things that were run for years in the old Database (views,
packages etc.). How to find which should be rerun? One I know:
Statspack :-)
i) Scheduled jobs (!!)
j) Client software. When to update? About 40 PCs.
k) ..(what else?)..

Many questions here. How to freeze a Database structure? (I am not a
developer, I don't trust the developers). How do I do a quick "sanity"
check of the new Database? (Something could go wrong and remain unnoticed).
How can I transfer jobs? Resource plans? Are sequences an issue?
Should I install the new version of the clients beforehand? (I think
so, but will there be any limitation until the new Database version is up
and running?). Since the Database will have a new name, will DB links still
be valid? Where can I find an exact list of example users? (I don't
mean OUTLN, PERFSTAT, etc.)-

Init parameters to adapt, some specific to V 9 or with changed
default:

DB_CHECKSUM (false)
DB_FILE_MULTIBLOCK_READ_COUNT
PGA_AGGREGATE_TARGET
OPTIMIZER_INDEX_COST_ADJ (about 30)
OPTIMIZER_INDEX_CACHING (perhaps 50)
PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
START_ARCHIVING (or similar; true, of course).
DISK_ASYNCH_IO (true)
QUERY_REWRITE (true)
CURSOR_CACHING (force)
NUMBER_OF_PROCESSES
JOB_QUEUE_PROCESSES (1 or 2)
...
and some other memory parameters. I am afraid that something will
break. Until now, I have always done imp/exp in the same platform,
same version.

You could help me minimize Murphy's chances!

Thanks a lot
Rick Denoire
(who will spend Christmas/New Year on duty).

Rick Denoire <100.17706@germanynet.de> wrote in message news:...
> Hello
>
> Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
> to do exp/imp. One instance, about 230 GB (size of Database files). My plan:
>
> 1) Do an export from the source with rows=no and freeze Database structure
> 2) Convert the exported dump file into executable sql scripts
> 3) Edit the scripts (removing storage params, etc.)
> 4) Generate a Database creation script with Version 9 (target environment)
> 5) Run the Database creation script from step 4, so creating a standard DB
> 6) Delete spfile, tune new Database init parameters
> 7) Test rman+Veritas setup (backup/restore) in new environment
> 8) Precreate tablespaces manually
> 9) Run the scripts from step 3 to precreate objects in Database from step 5
> 10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
> 11)Immediately start an import from pipe (fromuser= touser=) on target
> 12)Gather statistics of new Database
> 13)Shutdown source Database
> 14)Edit central tnsnames.ora for all clients, announce new platform
> 15)Drop example users (HR, SH, etc) cascade
> 16)Repeat step 7
>
> Done.
>
> In praxis: Not done (being realistic). I will test this of course, the
> goal being a reliable procedure and a minimal downtime. But what
> really concerns me is the kind of unexpected problems that I won't be
> prepared to cope with due to my lack of experience (well, I have
> already transferred a number of DBs this way, at times something fails
> and has to be fixed manually).
>
> These are the things that seem prone to failure. Please comment or put
> your ideas:
>
> a) Database Links. We have some.
> b) Synonyms (no full import done...)
> c) Roles
> d) Object and system privileges
> e) Sequences (?)
> f) Constraints, triggers (?)
> g) Resource Plans (yeah, they save admin's life during night jobs)
> h) rdbms/admin things that were run for years in the old Database (views,
> packages etc.). How to find which should be rerun? One I know:
> Statspack :-)
> i) Scheduled jobs (!!)
> j) Client software. When to update? About 40 PCs.
> k) ..(what else?)..
>
> Many questions here. How to freeze a Database structure? (I am not a
> developer, I don't trust the developers). How do I do a quick "sanity"
> check of the new Database? (Something could go wrong and remain unnoticed).
> How can I transfer jobs? Resource plans? Are sequences an issue?
> Should I install the new version of the clients beforehand? (I think
> so, but will there be any limitation until the new Database version is up
> and running?). Since the Database will have a new name, will DB links still
> be valid? Where can I find an exact list of example users? (I don't
> mean OUTLN, PERFSTAT, etc.)-
>
> Init parameters to adapt, some specific to V 9 or with changed
> default:
>
> DB_CHECKSUM (false)
> DB_FILE_MULTIBLOCK_READ_COUNT
> PGA_AGGREGATE_TARGET
> OPTIMIZER_INDEX_COST_ADJ (about 30)
> OPTIMIZER_INDEX_CACHING (perhaps 50)
> PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
> START_ARCHIVING (or similar; true, of course).
> DISK_ASYNCH_IO (true)
> QUERY_REWRITE (true)
> CURSOR_CACHING (force)
> NUMBER_OF_PROCESSES
> JOB_QUEUE_PROCESSES (1 or 2)
> ..
> and some other memory parameters. I am afraid that something will
> break. Until now, I have always done imp/exp in the same platform,
> same version.
>
> You could help me minimize Murphy's chances!
>
> Thanks a lot
> Rick Denoire
> (who will spend Christmas/New Year on duty).

Rick, I am only going to add a couple of notes:
1- Build your new Database using locally managed uniform extent tablespaces
for all user data tablespaces
2- Perform the exports with compress=n so each object takes only as
much room as it needs when recreated and initially loaded
3- you can use an export with full=y rows=n to capture public synonyms
etc... that an imp with ignore=y will re-establish for you should
something go wrong on the first import
4- I would build my new Database from scratch well in advance room
permitting and precreate the owners with the necessary object grants.
This would prevent errors due to the owners having objects of a type
for which they do not currently have create privilege plus you can
re-establish any grants on SYS owned objects that the imp will not
contain and which are needed to support stored
procedure/functions/packaged code that references v$ or dictionary
views.

HTH -- Mark D Powell --

Rick Denoire <100.17706@germanynet.de> wrote in message news:...
> Hello
>
> Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
> to do exp/imp. One instance, about 230 GB (size of Database files). My plan:
>
> 1) Do an export from the source with rows=no and freeze Database structure
> 2) Convert the exported dump file into executable sql scripts
> 3) Edit the scripts (removing storage params, etc.)
> 4) Generate a Database creation script with Version 9 (target environment)
> 5) Run the Database creation script from step 4, so creating a standard DB
> 6) Delete spfile, tune new Database init parameters
> 7) Test rman+Veritas setup (backup/restore) in new environment
> 8) Precreate tablespaces manually
> 9) Run the scripts from step 3 to precreate objects in Database from step 5
> 10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
> 11)Immediately start an import from pipe (fromuser= touser=) on target
> 12)Gather statistics of new Database
> 13)Shutdown source Database
> 14)Edit central tnsnames.ora for all clients, announce new platform
> 15)Drop example users (HR, SH, etc) cascade
> 16)Repeat step 7
>
> Done.
>
> In praxis: Not done (being realistic). I will test this of course, the
> goal being a reliable procedure and a minimal downtime. But what
> really concerns me is the kind of unexpected problems that I won't be
> prepared to cope with due to my lack of experience (well, I have
> already transferred a number of DBs this way, at times something fails
> and has to be fixed manually).
>
> These are the things that seem prone to failure. Please comment or put
> your ideas:
>
> a) Database Links. We have some.
> b) Synonyms (no full import done...)
> c) Roles
> d) Object and system privileges
> e) Sequences (?)
> f) Constraints, triggers (?)
> g) Resource Plans (yeah, they save admin's life during night jobs)
> h) rdbms/admin things that were run for years in the old Database (views,
> packages etc.). How to find which should be rerun? One I know:
> Statspack :-)
> i) Scheduled jobs (!!)
> j) Client software. When to update? About 40 PCs.
> k) ..(what else?)..
>
> Many questions here. How to freeze a Database structure? (I am not a
> developer, I don't trust the developers). How do I do a quick "sanity"
> check of the new Database? (Something could go wrong and remain unnoticed).
> How can I transfer jobs? Resource plans? Are sequences an issue?
> Should I install the new version of the clients beforehand? (I think
> so, but will there be any limitation until the new Database version is up
> and running?). Since the Database will have a new name, will DB links still
> be valid? Where can I find an exact list of example users? (I don't
> mean OUTLN, PERFSTAT, etc.)-
>
> Init parameters to adapt, some specific to V 9 or with changed
> default:
>
> DB_CHECKSUM (false)
> DB_FILE_MULTIBLOCK_READ_COUNT
> PGA_AGGREGATE_TARGET
> OPTIMIZER_INDEX_COST_ADJ (about 30)
> OPTIMIZER_INDEX_CACHING (perhaps 50)
> PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
> START_ARCHIVING (or similar; true, of course).
> DISK_ASYNCH_IO (true)
> QUERY_REWRITE (true)
> CURSOR_CACHING (force)
> NUMBER_OF_PROCESSES
> JOB_QUEUE_PROCESSES (1 or 2)
> ..
> and some other memory parameters. I am afraid that something will
> break. Until now, I have always done imp/exp in the same platform,
> same version.
>
> You could help me minimize Murphy's chances!
>
> Thanks a lot
> Rick Denoire
> (who will spend Christmas/New Year on duty).

Rick, I am only going to add a couple of notes:
1- Build your new Database using locally managed uniform extent tablespaces
for all user data tablespaces
2- Perform the exports with compress=n so each object takes only as
much room as it needs when recreated and initially loaded
3- you can use an export with full=y rows=n to capture public synonyms
etc... that an imp with ignore=y will re-establish for you should
something go wrong on the first import
4- I would build my new Database from scratch well in advance room
permitting and precreate the owners with the necessary object grants.
This would prevent errors due to the owners having objects of a type
for which they do not currently have create privilege plus you can
re-establish any grants on SYS owned objects that the imp will not
contain and which are needed to support stored
procedure/functions/packaged code that references v$ or dictionary
views.

HTH -- Mark D Powell --

Hi,

Your plan seems good, but it could take a long time to export and
import 230 GB.
You could try to parallelize this operation by exporting and importing
users rather than doing just one full export and import.

Dias

Rick Denoire <100.17706@germanynet.de> wrote in message news:...
> Hello
>
> Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
> to do exp/imp. One instance, about 230 GB (size of Database files). My plan:
>
> 1) Do an export from the source with rows=no and freeze Database structure
> 2) Convert the exported dump file into executable sql scripts
> 3) Edit the scripts (removing storage params, etc.)
> 4) Generate a Database creation script with Version 9 (target environment)
> 5) Run the Database creation script from step 4, so creating a standard DB
> 6) Delete spfile, tune new Database init parameters
> 7) Test rman+Veritas setup (backup/restore) in new environment
> 8) Precreate tablespaces manually
> 9) Run the scripts from step 3 to precreate objects in Database from step 5
> 10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
> 11)Immediately start an import from pipe (fromuser= touser=) on target
> 12)Gather statistics of new Database
> 13)Shutdown source Database
> 14)Edit central tnsnames.ora for all clients, announce new platform
> 15)Drop example users (HR, SH, etc) cascade
> 16)Repeat step 7
>
> Done.
>
> In praxis: Not done (being realistic). I will test this of course, the
> goal being a reliable procedure and a minimal downtime. But what
> really concerns me is the kind of unexpected problems that I won't be
> prepared to cope with due to my lack of experience (well, I have
> already transferred a number of DBs this way, at times something fails
> and has to be fixed manually).
>
> These are the things that seem prone to failure. Please comment or put
> your ideas:
>
> a) Database Links. We have some.
> b) Synonyms (no full import done...)
> c) Roles
> d) Object and system privileges
> e) Sequences (?)
> f) Constraints, triggers (?)
> g) Resource Plans (yeah, they save admin's life during night jobs)
> h) rdbms/admin things that were run for years in the old Database (views,
> packages etc.). How to find which should be rerun? One I know:
> Statspack :-)
> i) Scheduled jobs (!!)
> j) Client software. When to update? About 40 PCs.
> k) ..(what else?)..
>
> Many questions here. How to freeze a Database structure? (I am not a
> developer, I don't trust the developers). How do I do a quick "sanity"
> check of the new Database? (Something could go wrong and remain unnoticed).
> How can I transfer jobs? Resource plans? Are sequences an issue?
> Should I install the new version of the clients beforehand? (I think
> so, but will there be any limitation until the new Database version is up
> and running?). Since the Database will have a new name, will DB links still
> be valid? Where can I find an exact list of example users? (I don't
> mean OUTLN, PERFSTAT, etc.)-
>
> Init parameters to adapt, some specific to V 9 or with changed
> default:
>
> DB_CHECKSUM (false)
> DB_FILE_MULTIBLOCK_READ_COUNT
> PGA_AGGREGATE_TARGET
> OPTIMIZER_INDEX_COST_ADJ (about 30)
> OPTIMIZER_INDEX_CACHING (perhaps 50)
> PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
> START_ARCHIVING (or similar; true, of course).
> DISK_ASYNCH_IO (true)
> QUERY_REWRITE (true)
> CURSOR_CACHING (force)
> NUMBER_OF_PROCESSES
> JOB_QUEUE_PROCESSES (1 or 2)
> ..
> and some other memory parameters. I am afraid that something will
> break. Until now, I have always done imp/exp in the same platform,
> same version.
>
> You could help me minimize Murphy's chances!
>
> Thanks a lot
> Rick Denoire
> (who will spend Christmas/New Year on duty).

Hi,

Your plan seems good, but it could take a long time to export and
import 230 GB.
You could try to parallelize this operation by exporting and importing
users rather than doing just one full export and import.

Dias

Rick Denoire <100.17706@germanynet.de> wrote in message news:...
> Hello
>
> Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
> to do exp/imp. One instance, about 230 GB (size of Database files). My plan:
>
> 1) Do an export from the source with rows=no and freeze Database structure
> 2) Convert the exported dump file into executable sql scripts
> 3) Edit the scripts (removing storage params, etc.)
> 4) Generate a Database creation script with Version 9 (target environment)
> 5) Run the Database creation script from step 4, so creating a standard DB
> 6) Delete spfile, tune new Database init parameters
> 7) Test rman+Veritas setup (backup/restore) in new environment
> 8) Precreate tablespaces manually
> 9) Run the scripts from step 3 to precreate objects in Database from step 5
> 10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
> 11)Immediately start an import from pipe (fromuser= touser=) on target
> 12)Gather statistics of new Database
> 13)Shutdown source Database
> 14)Edit central tnsnames.ora for all clients, announce new platform
> 15)Drop example users (HR, SH, etc) cascade
> 16)Repeat step 7
>
> Done.
>
> In praxis: Not done (being realistic). I will test this of course, the
> goal being a reliable procedure and a minimal downtime. But what
> really concerns me is the kind of unexpected problems that I won't be
> prepared to cope with due to my lack of experience (well, I have
> already transferred a number of DBs this way, at times something fails
> and has to be fixed manually).
>
> These are the things that seem prone to failure. Please comment or put
> your ideas:
>
> a) Database Links. We have some.
> b) Synonyms (no full import done...)
> c) Roles
> d) Object and system privileges
> e) Sequences (?)
> f) Constraints, triggers (?)
> g) Resource Plans (yeah, they save admin's life during night jobs)
> h) rdbms/admin things that were run for years in the old Database (views,
> packages etc.). How to find which should be rerun? One I know:
> Statspack :-)
> i) Scheduled jobs (!!)
> j) Client software. When to update? About 40 PCs.
> k) ..(what else?)..
>
> Many questions here. How to freeze a Database structure? (I am not a
> developer, I don't trust the developers). How do I do a quick "sanity"
> check of the new Database? (Something could go wrong and remain unnoticed).
> How can I transfer jobs? Resource plans? Are sequences an issue?
> Should I install the new version of the clients beforehand? (I think
> so, but will there be any limitation until the new Database version is up
> and running?). Since the Database will have a new name, will DB links still
> be valid? Where can I find an exact list of example users? (I don't
> mean OUTLN, PERFSTAT, etc.)-
>
> Init parameters to adapt, some specific to V 9 or with changed
> default:
>
> DB_CHECKSUM (false)
> DB_FILE_MULTIBLOCK_READ_COUNT
> PGA_AGGREGATE_TARGET
> OPTIMIZER_INDEX_COST_ADJ (about 30)
> OPTIMIZER_INDEX_CACHING (perhaps 50)
> PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
> START_ARCHIVING (or similar; true, of course).
> DISK_ASYNCH_IO (true)
> QUERY_REWRITE (true)
> CURSOR_CACHING (force)
> NUMBER_OF_PROCESSES
> JOB_QUEUE_PROCESSES (1 or 2)
> ..
> and some other memory parameters. I am afraid that something will
> break. Until now, I have always done imp/exp in the same platform,
> same version.
>
> You could help me minimize Murphy's chances!
>
> Thanks a lot
> Rick Denoire
> (who will spend Christmas/New Year on duty).

Mark.Powell-at-eds.com (Mark D Powell) wrote:


>Rick, I am only going to add a couple of notes:
>1- Build your new Database using locally managed uniform extent tablespaces
>for all user data tablespaces
And for the system tablaspace too, I guess.

>2- Perform the exports with compress=n so each object takes only as
>much room as it needs when recreated and initially loaded
Agree.
>3- you can use an export with full=y rows=n to capture public synonyms
>etc... that an imp with ignore=y will re-establish for you should
>something go wrong on the first import
I hope it will work that way.

>4- I would build my new Database from scratch well in advance room
>permitting and precreate the owners with the necessary object grants.

I have had the problem that at import time, some specific tables would
import into a wrong tablespace (it should go into the target user's
default tablespace); and the import of this particular segment will
fail if the schema owner at the target Database has no quota on this
tablespace. Strange. It happened with tables containing records of
type LONG.

>This would prevent errors due to the owners having objects of a type
>for which they do not currently have create privilege plus you can
>re-establish any grants on SYS owned objects that the imp will not
>contain and which are needed to support stored
>procedure/functions/packaged code that references v$ or dictionary
>views.

This is the best point: grants on sys objects won't come over.

And I was right. I am afraid that I forgot something. Shouldn't I set
the COMPATIBLE parameter in the target Database so something based on the
source Database? Remember, I am going from 8.1.7.0 to 9.2.0.4.

Thanks
Rick Denoire
>HTH -- Mark D Powell --

Mark.Powell-at-eds.com (Mark D Powell) wrote:


>Rick, I am only going to add a couple of notes:
>1- Build your new Database using locally managed uniform extent tablespaces
>for all user data tablespaces
And for the system tablaspace too, I guess.

>2- Perform the exports with compress=n so each object takes only as
>much room as it needs when recreated and initially loaded
Agree.
>3- you can use an export with full=y rows=n to capture public synonyms
>etc... that an imp with ignore=y will re-establish for you should
>something go wrong on the first import
I hope it will work that way.

>4- I would build my new Database from scratch well in advance room
>permitting and precreate the owners with the necessary object grants.

I have had the problem that at import time, some specific tables would
import into a wrong tablespace (it should go into the target user's
default tablespace); and the import of this particular segment will
fail if the schema owner at the target Database has no quota on this
tablespace. Strange. It happened with tables containing records of
type LONG.

>This would prevent errors due to the owners having objects of a type
>for which they do not currently have create privilege plus you can
>re-establish any grants on SYS owned objects that the imp will not
>contain and which are needed to support stored
>procedure/functions/packaged code that references v$ or dictionary
>views.

This is the best point: grants on sys objects won't come over.

And I was right. I am afraid that I forgot something. Shouldn't I set
the COMPATIBLE parameter in the target Database so something based on the
source Database? Remember, I am going from 8.1.7.0 to 9.2.0.4.

Thanks
Rick Denoire
>HTH -- Mark D Powell --

ydias-at-hotmail.com (dias) wrote:

>Hi,
>
>Your plan seems good, but it could take a long time to export and
>import 230 GB.
>You could try to parallelize this operation by exporting and importing
>users rather than doing just one full export and import.

Yes, good hint. On the other side, some degree of fuss may be
introduced here. What about a user's grants on other users' objects?
Schemas are not completely independent.

I am afraid that the limiting factor will be the network transfer rate
anyway. And I am wondering if this parallel input into the Database won't
physically shuffle data extents from different segments randomly,
which would be bad for full table scans later (assuming the schemas
being loaded in parallel reside in the same tablespace).

I can't wait to begin testing!

Bye
Rick Denoire

ydias-at-hotmail.com (dias) wrote:

>Hi,
>
>Your plan seems good, but it could take a long time to export and
>import 230 GB.
>You could try to parallelize this operation by exporting and importing
>users rather than doing just one full export and import.

Yes, good hint. On the other side, some degree of fuss may be
introduced here. What about a user's grants on other users' objects?
Schemas are not completely independent.

I am afraid that the limiting factor will be the network transfer rate
anyway. And I am wondering if this parallel input into the Database won't
physically shuffle data extents from different segments randomly,
which would be bad for full table scans later (assuming the schemas
being loaded in parallel reside in the same tablespace).

I can't wait to begin testing!

Bye
Rick Denoire

Have you ever done a full export to disk ?
- Export to a named pipe and route output of pipe to compress and route
output of compress to disk. You might be surprised at how much it gets
compressed to. There are probably examples in Google search and in Metalink,
if you haven't done this yet.

If you cannot do a cleaner process because of size/time , then it still
seems you could do an export full=y and rows=n (and then a rows =y on
upgrade date). This would at least get things like public synonyms and users
created.

Of course, you want to pre-create the tablespaces as LMT (if not already
LMT).

Database links are always fun and Plsql and views . It seems there is always at
least 1 or 2 that are invalid or become invalid.

Doing a test execution is of course required and you will see the types of
errors and be better prepared for them on the day(s) when this is running.


--
"Rick Denoire" <100.17706@germanynet.de> wrote in message
news5oaqvkme2ogp0aichlj38mpsp46teesr4-at-4ax.com...
> Hello
>
> Going from 8.1.7 (Solaris) to 9.2.0.4 (Linux Redhat AS2.1) I will have
> to do exp/imp. One instance, about 230 GB (size of Database files). My plan:
>
> 1) Do an export from the source with rows=no and freeze Database structure
> 2) Convert the exported dump file into executable sql scripts
> 3) Edit the scripts (removing storage params, etc.)
> 4) Generate a Database creation script with Version 9 (target environment)
> 5) Run the Database creation script from step 4, so creating a standard DB
> 6) Delete spfile, tune new Database init parameters
> 7) Test rman+Veritas setup (backup/restore) in new environment
> 8) Precreate tablespaces manually
> 9) Run the scripts from step 3 to precreate objects in Database from step 5
> 10)Do a 2nd export (full, rows=y, compress=n) into a pipe, set Database RO
> 11)Immediately start an import from pipe (fromuser= touser=) on target
> 12)Gather statistics of new Database
> 13)Shutdown source Database
> 14)Edit central tnsnames.ora for all clients, announce new platform
> 15)Drop example users (HR, SH, etc) cascade
> 16)Repeat step 7
>
> Done.
>
> In praxis: Not done (being realistic). I will test this of course, the
> goal being a reliable procedure and a minimal downtime. But what
> really concerns me is the kind of unexpected problems that I won't be
> prepared to cope with due to my lack of experience (well, I have
> already transferred a number of DBs this way, at times something fails
> and has to be fixed manually).
>
> These are the things that seem prone to failure. Please comment or put
> your ideas:
>
> a) Database Links. We have some.
> b) Synonyms (no full import done...)
> c) Roles
> d) Object and system privileges
> e) Sequences (?)
> f) Constraints, triggers (?)
> g) Resource Plans (yeah, they save admin's life during night jobs)
> h) rdbms/admin things that were run for years in the old Database (views,
> packages etc.). How to find which should be rerun? One I know:
> Statspack :-)
> i) Scheduled jobs (!!)
> j) Client software. When to update? About 40 PCs.
> k) ..(what else?)..
>
> Many questions here. How to freeze a Database structure? (I am not a
> developer, I don't trust the developers). How do I do a quick "sanity"
> check of the new Database? (Something could go wrong and remain unnoticed).
> How can I transfer jobs? Resource plans? Are sequences an issue?
> Should I install the new version of the clients beforehand? (I think
> so, but will there be any limitation until the new Database version is up
> and running?). Since the Database will have a new name, will DB links still
> be valid? Where can I find an exact list of example users? (I don't
> mean OUTLN, PERFSTAT, etc.)-
>
> Init parameters to adapt, some specific to V 9 or with changed
> default:
>
> DB_CHECKSUM (false)
> DB_FILE_MULTIBLOCK_READ_COUNT
> PGA_AGGREGATE_TARGET
> OPTIMIZER_INDEX_COST_ADJ (about 30)
> OPTIMIZER_INDEX_CACHING (perhaps 50)
> PARALLELITY (don't remember exactly). Four CPUs, lots of disks.
> START_ARCHIVING (or similar; true, of course).
> DISK_ASYNCH_IO (true)
> QUERY_REWRITE (true)
> CURSOR_CACHING (force)
> NUMBER_OF_PROCESSES
> JOB_QUEUE_PROCESSES (1 or 2)
> ..
> and some other memory parameters. I am afraid that something will
> break. Until now, I have always done imp/exp in the same platform,
> same version.
>
> You could help me minimize Murphy's chances!
>
> Thanks a lot
> Rick Denoire
> (who will spend Christmas/New Year on duty).