Upgrade from 32-bit to 64 bit
Prior to the upgrade from the 32-bit ORACLE_HOME:
Prior to the upgrade from the 32-bit ORACLE_HOME:
- Current working directory: Oracle Home:
/d02/oramine/minedb/10.2.0/dbs
- Spool the invalid count before upgrade:
spool TEST_status_32bit_DB.log
select count(*) from dba_objects where status = 'INVALID';
COUNT(*)
----------
425
3. Execute the below script:
3. Execute the below script:
set linesize 200
column comp_name format a60
SELECT comp_name, version, status FROM
dba_registry;
set linesize 200
column owner format a12
column object_name format a30
column object_type format a15
column "Created" format a21
column "Last DDL Time" format a21
select owner, object_name, object_type,
status, to_char(created, 'DD-MON-YYYY HH:MI AM') "Created",
to_char(last_ddl_time, 'DD-MON-YYYY HH:MI AM') "Last DDL Time" ,
timestamp
from dba_objects
where status = 'INVALID' order by owner,
object_name;
spool off
- Use a non-rac spfile/init.ora for the
migration
sqlplus "/ as sysdba"
SHUTDOWN IMMEDIATE
6. Startup the database using startup upgrade
pfile=’<init.ora location>’
STARTUP UPGRADE
@$ORACLE_HOME/rdbms/admin/utlirp.sql
Took about 10 minutes complete.
- Remove the OLAP option
Remove the
OLAP option from an Oracle Database 10g:
sqlplus "/ as sysdba"
@?/olap/admin/catnoamd.sql
The following errors were ignored:
SP2-0310: unable to open file
"factvdrp.sql"
SP2-0310: unable to open file
"dimvwdrp.sql"
SP2-0310: unable to open file
"olapodrp.sql"
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
The following error was ignored:
Procedure created.
DECLARE
*
ERROR at line 1:
ORA-20003: User created AW objects exist,
unable to deinstall OLAP
ORA-06512: at line 8
Procedure dropped.
SQL> @?/olap/admin/catnoxoq.sql
- Execute the below script:
set linesize 200
column comp_name format a60
SELECT comp_name, version, status FROM
dba_registry;
COMP_NAME STATUS VERSION
-----------------------------------
--------------------------------- ------------------------------
Oracle XML Database VALID 10.2.0.3.0
Oracle Data Mining VALID 10.2.0.3.0
Oracle Enterprise Manager VALID 10.2.0.3.0
Oracle Text VALID 10.2.0.3.0
Spatial VALID 10.2.0.3.0
Oracle interMedia VALID 10.2.0.3.0
Oracle Database Catalog Views VALID 10.2.0.3.0
Oracle Database Packages and Types VALID 10.2.0.3.0
JServer JAVA Virtual Machine VALID 10.2.0.3.0
Oracle Database Java Packages VALID 10.2.0.3.0
Oracle XDK VALID 10.2.0.3.0
COMP_NAME STATUS VERSION
-----------------------------------
--------------------------------- ------------------------------
Oracle Real Application Clusters VALID 10.2.0.3.0
OLAP Analytic Workspace VALID 10.2.0.3.0
Oracle OLAP API REMOVED 10.2.0.3.0
14 rows selected.
- Add the OLAP option back to the
Enterprise Edition Database:
@?/olap/admin/olap.sql SYSAUX TEMP;
The status of OLAP will be Invalid until
the objects are recompiled:
set linesize 200
column comp_name format a60
SELECT comp_name, version, status FROM
dba_registry;
COMP_NAME
VERSION
STATUS
------------------------------------------------------------
------------------------------ ---------------------------------
OLAP Catalog
10.2.0.3.0
VALID
Oracle XML Database 10.2.0.3.0 VALID
Oracle Data Mining
10.2.0.3.0
VALID
Oracle Enterprise Manager
10.2.0.3.0
VALID
Oracle Text
10.2.0.3.0
VALID
Spatial
10.2.0.3.0
VALID
Oracle interMedia
10.2.0.3.0
VALID
Oracle Database Catalog Views 10.2.0.3.0 VALID
Oracle Database Packages and Types 10.2.0.3.0 VALID
JServer JAVA Virtual Machine 10.2.0.3.0 VALID
Oracle Database Java Packages 10.2.0.3.0 VALID
COMP_NAME
VERSION STATUS
------------------------------------------------------------
------------------------------ ---------------------------------
Oracle XDK
10.2.0.3.0
VALID
Oracle Real Application Clusters 10.2.0.3.0 VALID
OLAP Analytic Workspace
10.2.0.3.0
INVALID
Oracle OLAP API
10.2.0.3.0 VALID
15 rows selected.
Note: If the database is in "STARTUP
UPGRADE" or "STARTUP MIGRATE" mode, you will notice the parallel
recompile jobs don't really kick off in parallel mode "ps -ef | grep
ora_j". Ensure the DB is started up normally "startup" before
trying the parallel recompile:
- Perform:
shutdown
immediate
startup
select count(*) from dba_objects where status = 'INVALID';
COUNT(*)
----------
107241
SQL>
- exec utl_recomp.recomp_parallel(8);
On a separate session check parallel threads
started (you may initially see fewer threads such as 0..4):
ps -ef | grep ora_j
SQL> !ps -ef | grep ora_j
oratst
4118 1 25 12:34 ? 00:01:58 ora_j000_TEST1
oratst
4120 1 27 12:34 ? 00:02:06 ora_j001_TEST1
oratst
4122 1 26 12:34 ? 00:02:03 ora_j002_TEST1
oratst
4124 1 24 12:34 ? 00:01:53 ora_j003_TEST1
oratst
4126 1 23 12:34 ? 00:01:47 ora_j004_TEST1
oratst
11760 1 31 12:36 ? 00:01:48 ora_j005_TEST1
oratst
11762 1 32 12:36 ? 00:01:50 ora_j006_TEST1
oratst
19209 1 0 12:38 ? 00:00:00 ora_j007_TEST1
oratst
31090 15518 0 12:41 pts/0 00:00:00 /bin/ksh -c ps -ef | grep ora_j
oratst
31092 31090 0 12:41 pts/0 00:00:00 grep ora_j
SQL>
- @?/rdbms/admin/utlrp.sql
- cd $ORACLE_HOME/dbs
spool TEST_status_64bit_DB.log
select count(*) from dba_objects where
status = 'INVALID';
COUNT(*)
----------
471
- Execute:
set linesize 200
column owner format a12
column object_name format a30
column object_type format a15
column "Created" format a21
column "Last DDL Time" format a21
select owner, object_name, object_type,
status, to_char(created, 'DD-MON-YYYY HH:MI AM') "Created",
to_char(last_ddl_time, 'DD-MON-YYYY HH:MI AM') "Last DDL Time" ,
timestamp
from dba_objects
where status = 'INVALID' order by owner,
object_name;
- Check for any invalid OLAPSYS objects:
select owner, object_name, object_type,
status from dba_objects where status = 'INVALID' and OWNER = 'OLAPSYS';
spool off
- Run AutoConfig on 2 nodes
Start the listners
Run AutoConfig on all nodes
Exit from all sessions, re-source the
environment
Repeat AutoConfig on all the nodes a second
round for RAC environments
Exit from all sessions, re-source the
environment, verify tns, listener, etc.
Stop and then Restart the database and
listeners
-Recompile Invalids
select count(*) from dba_objects where
status = 'INVALID';
COUNT(*)
----------
471
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where
status = 'INVALID';
COUNT(*)
----------
428
SQL>
1*
select count(*) from dba_objects where status = 'INVALID'
SQL> /
COUNT(*)
----------
429
SQL>
set linesize 200
column owner format a12
column object_name format a30
column object_type format a15
column "Created" format a21
column "Last DDL Time" format a21
select owner, object_name, object_type,
status, to_char(created, 'DD-MON-YYYY HH:MI AM') "Created",
to_char(last_ddl_time, 'DD-MON-YYYY HH:MI AM') "Last DDL Time" ,
timestamp
from dba_objects
where status = 'INVALID'
and owner='APPS'
order by owner, object_name;
- Compare
the INVALIDS before upgrade and after upgrade, if INVALID count is high in
64 bit compare to 32 bit please compile them.
Hi!
ReplyDeleteJust want to ask. Does all of the commands above are executed on the new 64bit system with oracle 64bit already installed or they are run on the old 32bit server?
Hey Iam sorry , I dont know how i missed ur comment.
ReplyDeleteHere the whole effort is to upgrade from 32 bit to 64 bit. Hence mostly all pre steps are executed on 32 bit system.