Manual upgrade from 9i to 10g Release2


Manual upgrade from 9i to 10g Release 2

This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.
1. Compile any invalid objects
@?/rdbms/admin/utlrp.sql
2. Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '<file_name>' size 512M
extent management local
segment space management auto
/
3. Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.
4. Shut the database down with either normal or immediate
shutdown immediate
5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* <new_home>/dbs/
6. Edit oratab
Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.
7. Upgrade the database
sqlplus "/ as sysdba"
 
startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql
8. Recompile any invalid objects
@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.
9. Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql
10. Alter or remove initialisation parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;
 
shutdown immediate
 
vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup
 
create spfile from pfile;
 
shutdown immediate
 
startup

Comments

Post a Comment

Popular posts from this blog

Key points on Read-Only Instance and Read-Only Database

 In Oracle, both Read-Only Instance and Read-Only Database provide mechanisms to limit data modifications, but they are used in different contexts and for different purposes. Here's a comparison: 1. Read-Only Instance Definition : A read-only instance is a configuration in Oracle Real Application Clusters (RAC) where one or more instances of a multi-instance database are set up to operate in read-only mode. This is typically done in environments with high concurrency for both read and write operations. Key Features : Allows real-time query scaling by dedicating specific instances to read-only operations. Write operations are not allowed on the designated read-only instance, but other instances in the cluster can still handle write operations. Useful in RAC configurations for load balancing , where read workloads can be distributed across multiple read-only instances. Instance-specific: The database remains fully functional (read-write) when accessed through other instances. Us...
Read more

Steps to create database link between Oracle database and SQL Server

 Creating a database link between SQL Server and Oracle allows seamless data exchange between the two systems. Here’s a step-by-step guide: Step 1: Install the Required Drivers Install Oracle Database Gateway for SQL Server (ODG4MS). This acts as a bridge between Oracle and SQL Server. Ensure the Oracle Client is installed on the Oracle database server or the server acting as the gateway. Step 2: Configure the Oracle Database Gateway Locate Configuration Files: Navigate to the $ORACLE_HOME/network/admin directory on the Oracle Gateway server. Edit tnsnames.ora: Add an entry for the database link pointing to the SQL Server database: dg4msql =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = <SQL_SERVER_HOSTNAME>)(PORT = 1521))     (CONNECT_DATA = (SID = dg4msql))     (HS=OK)  -- Required for heterogeneous services   )   Edit listener.ora: Update the Oracle listener configuration to recognize the gateway: SID_LIST_LISTENE...
Read more

What's New with Oracle Cloud Guard in OCI?

 Oracle Cloud Guard is a game-changer for securing your cloud environment. Here are the top 10 features to know: Automated Threat Detection : Cloud Guard continuously scans for potential security risks across your OCI resources, providing automated detection and alerting. Built-in Remediation : With preconfigured policies, Cloud Guard offers automated remediation actions to address security threats instantly. Comprehensive Visibility : Gain end-to-end visibility into your OCI environment with dashboards and reports, helping you monitor and track risks in real-time. Customizable Policies : Cloud Guard allows you to tailor security policies based on your organization’s unique compliance needs. Risk Scoring : Each resource is assigned a risk score, enabling you to prioritize the most critical threats for immediate action. Incident Response Automation : Automate responses to incidents using predefined rules, reducing manual intervention and response time. Integrations with Oracle Secur...
Read more