Upgrade interview questions oracle database

Upgrade interview questions oracle database

1. Oracle database upgrade from version to version.
* Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
* Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

2. What is rolling upgrade?
It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.Rolling upgrade can be used only for Oracle database 11g releases(from 11.1).

3.Steps to Upgrade in Oracle ?
Manual upgrade which involves the following steps:
* Backup the database.
* In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new    11g Oracle home.
* Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.
* Start the original database using the STARTUP UPGRADE command and proceed with the     upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
* Recompile invalid objects.
* Restart the database.
* Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
* Troubleshoot any issues or abort the upgrade.

4. What happens when you give "STARTUP UPGRADE"?
$sqlplus "/as sysdba"
SQL> STARTUP UPGRADE

Note: The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBAsessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.
You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.

5.What is the difference between startup Upgrade and Migrate ?
STARTUP MIGRATE:
Used to upgrade a database till 9i.

STARTUP UPGRADE:
From 10G  we are using startup upgrade to upgrade database.

6. What happens internally when you use startup upgrade/migrate?
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothly.
In other way,it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.

What is the difference between normal startup and startup upgrade?

[Applicable for oracle 10g.]

The answer is same for both the questions. 

Basically startup upgrade will open the database by setting the below parameters in memory.(not for spfile)

ALTER SYSTEM enable restricted session;

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;

ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;

ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;

ALTER SYSTEM SET resource_manager_plan=” SCOPE=MEMORY;

It will be just a normal database startup, but it will make an environment for migrating the version.

Alert log will show all these details:

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Autotune of undo retention is turned on. 

IMODE=BR

ILAT =18

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.3.0.

—————————————————

—————————————————

Mon Mar 25 19:54:01 2013

Database mounted in Exclusive Mode

Completed: ALTER DATABASE   MOUNT

Mon Mar 25 19:54:02 2013

ALTER DATABASE OPEN MIGRATE

Mon Mar 25 19:54:02 2013

————————————————-

————————————————-

Starting background process MMNL

MMNL started with pid=12, OS id=2536

Mon Mar 25 19:54:14 2013

ALTER SYSTEM enable restricted session;

Mon Mar 25 19:54:14 2013

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;

Autotune of undo retention is turned off. 

Mon Mar 25 19:54:14 2013

ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;

Mon Mar 25 19:54:14 2013

ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;

Mon Mar 25 19:54:14 2013

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;

Mon Mar 25 19:54:14 2013

ALTER SYSTEM SET resource_manager_plan=” SCOPE=MEMORY;

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: ALTER DATABASE OPEN MIGRATE