Upgrade Oracle from 10.2.0.1 To 10.2.0.4

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba
Enter password:
Connected.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK
The OracleDBConsoleGOLDLINK service is stopping................
The OracleDBConsoleGOLDLINK service was stopped successfully.


C:\>net stop OracleServiceGOLDLINK
The OracleServiceGOLDLINK service is stopping.
The OracleServiceGOLDLINK service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN
SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ORA-00600: internal error code, arguments: [kxspoac : EXL 1]

Today we are faced the below error in our one of production database.

ORA-00600: internal error code, arguments: [kxspoac : EXL 1]

Database Version: 10.2.0.1

OS Version: Windows

This is database bug in 10.2.0.1 to 10.2.0.3. Due to this error if a SQL statement fails with Ora-600 [kxspoac : EXL 1] when executed by a parallel execution slave and that SQL statement has numeric binds then you are probably seeing this bug.

Solution:

This bug fixed in oracle 10.2.0.4 & 11.1

Refer more details in : Metalink document: 389438.1

Character set Utilities

Database Character set utilities

The Database character set scanner access the feasibility of migrating an oracle database to new database character set. This database characterset scanner checks all character data in the database & tests for the effects & problem of changing the character set encoding.

The method of migrate the database's character sets.

1. Export & Import utilities.
2. CSALTER Script
3. CSALTER with Export & Import utilities on selected tables.

Scan modes in the Database character set scanner.

1. Full database scan

2. User Scan

3. Table Scan

4. Column Scan

How to Install Chractet set scanner:

1. Create a user named CSMIG

2. Assign the necessary privileges to CSMIG

3. Assign the Default ts to CSMIG

3. Create the character set scanner system tables under CSMIG

Use csminst.sql script to modify default tblespace for CSMIG.

% cd $ORACLE_HOME/rdbms/admin
% sqlplus sys/password as sysdba
SQL> @csminst.sql


C:\Users\Babu>csscan help=y


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:41:12 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:

Example: CSSCAN SYSTEM/MANAGER

Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:

Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes owner of tables to be scanned
TABLE yes list of tables to scan
COLUMN yes list of columns to scan
EXCLUDE list of tables to exclude from scan
TOCHAR yes new database character set name
FROMCHAR current database character set name
TONCHAR new national character set name
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

C:\Users\Babu>csscan system/sys tochar=utf8 user=scott log=D:\scott_scan.log


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:49:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 >

Enumerating tables to scan...

. process 1 scanning SCOTT.MASTER[AAAM7wAAEAAAAGhAAA]
. process 1 scanning SCOTT.DEPT[AAAMgxAAEAAAAAJAAA]
. process 1 scanning SCOTT.BONUS[AAAMg1AAEAAAAApAAA]
. process 1 scanning SCOTT.EMP[AAAMgzAAEAAAAAZAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.