Migracion de 8i a 10GR1

Via METALINK... completito

This is a step by step guide that explains how we can migrate a database to a new server and database release manually using a Cold backup.

How to do a cold backup of oracle database and restore it on the new server and then upgrade it.
Solution

1) Prerequisites
----------------

- The copy of the datafiles must be done with the database closed.

- Source Operating System and destination Operating System need to be the same

- Destination sever need to have the 10g installed with the same word size
as the 8.1.7.4. Both need to be 32 bit or both need to be 64 bit.

- If your database contains user tables with NCHAR columns, you must upgrade the
NCHAR columns before they can be used .
In the database documentation you have all the steps is detail:
Oracle® Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-02





2) Init.ora or spfile and controlfile
----------------------------------
- You need to copy the init.ora to the target host and locate it in
ORACLE_HOME\dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems

- Copy the controlfile and the datafiles the redologs and all the archivelogs
generated, to the target host,

- You need to stop database with shutdown immediate or shutdown normal before copying the
files to target host,


3) Set the oracle environment on Target 10g host
-----------------------------------------------

C:\> set ORACLE_SID=
C:\> sqlplus "sys/ as sysdba"

- Check the init.ora parameters that reference location

control_files =
background_dump_dest =
core_dump_dest =
user_dump_dest =
log_archive_dest_1 =

- Make sure the COMPATIBLE initialization parameter is properly
set for the new Oracle Database 10g release.
compatible = 10.2.0.X

- We need to check which parameters need to be changed in the init.ora for the new 10g
release. We can use Pre-upgrade utility

* Copy the file utlu102i.sql from the ORACLE_HOME/rdbms/admin directory of the 10g
release to a directory on the old 8.1.7 server

* In the 8.1.7 environment, start SQL*Plus. Connect to the database instance as SYS
SQL> SPOOL info.log
SQL> @utlu102i.sql
SQL> SPOOL OFF

* Check the spool file and examine the output of the upgrade information tool, you
will see the minimum parameter setting for the 10g


4) Set up a password file for the target database
-----------------------------------------------
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<...>

The name and location of the password file on UNIX platforms, is ORACLE_HOME/dbs/orapw.
On Windows operating systems, the default password file is ORACLE_HOME\database\pwd.ora.

5) Start up the database and re mane files
-------------------------------------
Assuming that all the files are copied to target host and that controlfile has been located
at location specified by init.ora parameter control_files

- Start up the database in mount status

SQL> startup nomount pfile=\init.ora;

You can create at this point an spfile for the 10g instance, then you won't need to specify
the pfile location each startup.

SQL> create spfile from pfile=\init.ora;
SQL> shutdown immediate;
SQL> startup mount;

- If the datafile location is different on target server, we need to rename the files
Rename any of the datafiles to the new location, if necessary:

SQL>ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_HOST_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_HOST_LOCATION/AND_DATAFILE_NAME.DBF';

Rename the logfiles to the new location if necessary

SQL> alter database rename file '' to '';

6) Check that all the datafiles are in the right location:
-------------------------------------------------------
select file#, name from v$datafile;
Then shutdown the instance
sql>shutdown immediate;

7) Create SYSAUX tablespace
-------------------------
Once you have changed the datafile names you need to start up the instance
by issuing the following command:

SQL> STARTUP UPGRADE

And create sysaux tablespace

The following SQL statement would create a 500 MB SYSAUX tablespace for the
database:

SQL> CREATE TABLESPACE sysaux DATAFILE '/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

8) Run catupgrd.sql to upgrade the database
----------------------------------------

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

9) Run utlu102s.sql to display the results of the upgrade:
-------------------------------------------------------
SQL> @utlu102s.sql
SQL> SPOOL OFF;

Verify the upgrade.log file to check for any errors during the upgrade processes

10) Bounce Database
---------------
Shut down and restart the instance to reinitialize the system parameters for normal operation.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11) Create UNDO tablespace to use AUTOMATIC UNDO MANAGEMENT
-------------------------------------------------------

SQL> create undo tablespace UNDOTS1
datafile '/undots101.dbf' size 500M;
SQL> alter system set UNDO_MANAGEMENT=AUTO scope=spfile;
SQL> alter system set UNDO_TABLESPACE=UNDOTS1 scope=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP


12) Run olstrig.sql
----------------
Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
SQL> @olstrig.sql

13) Recompile packages
------------------
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql


14) Check for invalid objects
-------------------------
Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';