Home » RDBMS Server » Server Administration » Migrating Oracle 9i to 10g (From 9.2.0.1.0 on Win 2000 to 10.2.0.5.0 on Win 2008 R2)
| Migrating Oracle 9i to 10g [message #495958] |
Wed, 23 February 2011 08:43  |
 |
vik28
Messages: 2
Registered: February 2011
|
Junior Member |
|
|
Hi Gurus,
I'm trying to migrate an Oracle Enterprise Server database v9.2.0.1.0 deployed on Windows 2000 Server 32-bit to v10.2.0.5.0 on Windows 2008 R2 64-bit.
Basically, I'm following instructions provided by this document:
h**p://download.oracle.com/docs/html/B13831_01/ap_64bit.htm#CHDCDAGE
1) Updated v9.2.0.1.0 to v9.2.0.6.0 on the old server
2) Backed up the database as follow:
SQLPLUS /NOLOG
CONNECT / AS SYSDBA
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SHUTDOWN IMMEDIATE;
3) Installed Oracle Enterprise Server 10.2.0.4 on the new server and updated to v10.2.0.5
4) Copied trace files, data files, control files, archive logs and init.ora to the new server. Redo logs have been not copied since the ARCHIVELOGMODE is enabled on the old server.
5) Created an Oracle service on the new server as follow:
ORADIM -SID UNIDB2 -INTPWD mypass -STARTMODE AUTO -PFILE "C:\oracle\product\10.2.0\db_1\database\init.ora
6) Updated init.ora:
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
_SYSTEM_TRIG_ENABLED=false
###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\archive'
log_archive_format=%r_%t_%s.dbf
#log_archive_start=true
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=367001600
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=UNIDB2
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\bdump
core_dump_dest=C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\cdump
timed_statistics=TRUE
user_dump_dest=C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\udump
###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\control01.ctl", "C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\control02.ctl", "C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\control03.ctl")
###########################################
# Instance Identification
###########################################
instance_name=UNIDB2
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.0.0
###########################################
# Optimizer
###########################################
#hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Pools
###########################################
java_pool_size=0
large_pool_size=8388608
shared_pool_size=222321459
streams_pool_size=50331648
session_max_open_files=20
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Security and Auditing
###########################################
O7_DICTIONARY_ACCESSIBILITY=TRUE
#max_enabled_roles=148
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=326107136
sort_area_size=524288
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
7) Changed trace file as follow:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "UNIDB2" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO02.LOG' SIZE 10M,
GROUP 3 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\SYSTEM01.DBF',
'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\UNDOTBS01.DBF',
'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\TOOLS01.DBF',
'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\USERS01.DBF',
'C:\MY_DATA_FILE_DIR\CA_DATA01.DBF',
'C:\MY_INDEX_FILE_DIR\CA_INDEX01.DBF',
'C:\MY_DATA_FILE_DIR\APM_DATA01.DBF',
'C:\MY_DATA_FILE_DIR\APM_DATA02.DBF',
'C:\MY_DATA_FILE_DIR\APM_DATA03.DBF',
'C:\MY_INDEX_FILE_DIR\APM_INDEX01.DBF',
'C:\MY_DATA_FILE_DIR\RA_DATA01.DBF',
'C:\MY_INDEX_FILE_DIR\RA_INDEX01.DBF',
'C:\MY_DATA_FILE_DIR\APM_CLONE_DATA01.DBF',
'C:\MY_INDEX_FILE_DIR\APM_CLONE_INDEX01.DBF'
CHARACTER SET UTF8
;
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
8 ) Executed trace file on the new database instance with the following results:
SQL>
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 662700032 bytes
Fixed Size 2079864 bytes
Variable Size 285213576 bytes
Database Buffers 369098752 bytes
Redo Buffers 6307840 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "UNIDB2" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO01.LOG' SIZE 10M,
10 GROUP 2 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO02.LOG' SIZE 10M,
11 GROUP 3 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\REDO03.LOG' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
14 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\SYSTEM01.DBF',
15 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\UNDOTBS01.DBF',
16 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\TOOLS01.DBF',
17 'C:\oracle\product\10.2.0\db_1\oradata\UNIDB2\USERS01.DBF',
18 'C:\MY_DATA_FILE_DIR\CA_DATA01.DBF',
19 'C:\MY_INDEX_FILE_DIR\CA_INDEX01.DBF',
20 'C:\MY_DATA_FILE_DIR\APM_DATA01.DBF',
21 'C:\MY_DATA_FILE_DIR\APM_DATA02.DBF',
22 'C:\MY_DATA_FILE_DIR\APM_DATA03.DBF',
23 'C:\MY_INDEX_FILE_DIR\APM_INDEX01.DBF',
24 'C:\MY_DATA_FILE_DIR\RA_DATA01.DBF',
25 'C:\MY_INDEX_FILE_DIR\RA_INDEX01.DBF',
26 'C:\MY_DATA_FILE_DIR\APM_CLONE_DATA01.DBF',
27 'C:\MY_INDEX_FILE_DIR\APM_CLONE_INDEX01.DBF'
28 CHARACTER SET UTF8
29 ;
Control file created.
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 53951537 generated at 02/22/2011 13:28:08 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\DB_1\ORADATA\UNIDB2\ARCHIVE\558787328_1_552.DBF
ORA-00280: change 53951537 for thread 1 is in sequence #552
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL>
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 662700032 bytes
Fixed Size 2079864 bytes
Variable Size 285213576 bytes
Database Buffers 369098752 bytes
Redo Buffers 6307840 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> spool off
Here is the alert.log:
Tue Feb 22 08:25:03 Pacific Standard Time 2011
Setting recovery target incarnation to 2
Tue Feb 22 08:25:04 Pacific Standard Time 2011
Assigning activation ID 3508853714 (0xd124dbd2)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=13, OS id=588
ARC1 started with pid=14, OS id=1796
Tue Feb 22 08:25:04 Pacific Standard Time 2011
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: C:\ORACLE\PRODUCT\10.2.0\DB_1\ORADATA\UNIDB2\REDO01.LOG
Successful open of redo thread 1
Tue Feb 22 08:25:04 Pacific Standard Time 2011
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Tue Feb 22 08:25:04 Pacific Standard Time 2011
ARC1: Becoming the heartbeat ARCH
Tue Feb 22 08:25:04 Pacific Standard Time 2011
SMON: enabling cache recovery
Tue Feb 22 08:25:06 Pacific Standard Time 2011
Errors in file c:\oracle\product\10.2.0\db_1\oradata\unidb2\udump\unidb2_ora_196.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue Feb 22 08:25:06 Pacific Standard Time 2011
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 196
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...
I would kindly appreciate any help.
Be advised that I'm new to the Oracle world so, please, be patient and clear and as much as possible.
Regards,
David.
|
|
|
|
|
|
|
|
|
|
| Re: Migrating Oracle 9i to 10g [message #496079 is a reply to message #496078] |
Thu, 24 February 2011 05:10  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - well done, a 9i to 10g upgrade is usually considered to be a major project.
One point you must check is the CONNECT role. Many 9i databases rely on granting this role to many users, but the role is (by default) useless in 10.2. The quickest fix is to run this in your 10g database:
GRANT create session, create table, create view, create synonym,
create database link, create cluster, create sequence, alter session
TO CONNECT;
but you want might to investigate the use of the role further.
update: typo
[Updated on: Thu, 24 February 2011 05:12] Report message to a moderator |
|
|
|
| Re: Migrating Oracle 9i to 10g [message #496080 is a reply to message #496079] |
Thu, 24 February 2011 05:24 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:The quickest fix is to run this in your 10g database:
And in this case you lose your Oracle support.
I think the quickest fix is to create a CONNECT2 role with these privileges,
grant this role to accounts that have CONNECT and revoke the CONNECT one.
Of course, the correct way is to analyze the real need of the accounts and
grant them the privileges accordingly.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Jan 27 01:16:20 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
