Database upgrade from 10g to 11g - Praposal document

2. Requirement Analysis:

DETAILED SYSTEM STUDY ON THE EXISTING ENVIRONMENT


Existing System Environment
Operating System Windows Server 2003 Enterprise Edition - 64 bit
Release Service pack 2
CPU Intel Xeon® CPU X5365
Processor speed 3 GHz
RAM Size 11.7 GB
PF Size Min 30 GB
PF Size Max 42 GB
Computer Name MELEXHTRAX01
IP Address 172.23.201.84
Oracle Software Oracle 10g Enterprise Edition x64 Bit


Drive Details
Drive Name Total Size in GB Free Size in GB
Local Disk (C Drive) 39.9 14.0
Swap Volume (D Drive) 39.9 9.22
Data Volume (E Drive) 249 82.9



















3. FEASIBILITY STUDY ON UPGRADE:
A feasibility study’s main goal is to assess the economic viability of the proposed approach. The outcome of the feasibility study will indicate whether or not to proceed with the proposed venture.
From technical view, following points are to be considered.
1. Cost involved for DB Migration/Upgrade
2. Time and effort involved
3. Technical Benefits of DB Migration/Upgrade

Why Upgrade to Oracle Database 11g?
The innovation continues with Oracle Database 11g Release 2 to lower IT costs and deliver a higher quality of service by:
 Consolidating business applications onto fast, reliable and scalable.
 Maximizing availability and eliminating idle data center redundancy
 Compressing data onto low cost storage partitions for faster performance
 Securely protecting information and enabling compliance
 Doubling DBA productivity and reducing the risk of change
The key value propositions of Oracle Database 11g and the faster and smooth upgrade process make it compelling for businesses to consider upgrading older Oracle databases. Upgrading provides businesses with access to new functionality and ensures that an Oracle database is fully supported by Oracle.



Oracle Database version 11.1g new features
Overall, Oracle Database 11g makes database infrastructure far more efficient, resilient, and manageable. For example, very compelling new features in the realm of partitioning ease the
design and management of partitioned tables immensely.

1) SQL Performance Analyzer (SPA)
2) Database Replay
3) Automatic Memory Tuning
4) Case sensitive password
5) Virtual columns and indexes
6) Interval Partition and System Partition
7) Feature based patching
8) RMAN Undo bypass
9) New default Audit setting
10) Faster DML triggers
11) Parallel backup and restore for very large files
12) Improved block media recovery performance
13) Enhanced Index rebuild online: Online index build with NO pause to DML.
14) No recompilation of dependent objects
15) Fast incremental backups on physical standby database

Upgrade Methods

1. Database Upgrade Assistant
The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database 11g release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. The DBUA provides support for Real Application Clusters (RAC) and Automatic Storage Management (ASM).
2. Manual Upgrade
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 11g release. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
3. Export/Import Method
Unlike the DBUA or a manual upgrade, the Export/Import utilities physically copy data from current database to a new database. We can use either the Oracle Data Pump Export and Import utilities (available as of Oracle Database 10g) or the original Export and Import utilities to perform a full or partial export from your database, followed by a full or partial import into a new Oracle Database 11g database. Export/Import can copy a subset of the data in a database, leaving the database unchanged.




4. PROPOSED APPROACH
IMPLEMENTATION APPROACH (Manual/DBUA Upgrade)
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to Oracle Database 11g Release 1 (11.1).While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.



*RAT – REAL APPLICATION TESTING
Before the Upgrade
When manually upgrading a database, perform the following pre-upgrade steps:
• Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with Oracle Database 11g Release 1 (11.1), and must be run in the environment of the database being upgraded.
The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for Oracle Database 11g Release 1 (11.1).
• Prepare the new Oracle home.
• Perform a backup of the database.
Depending on the release of the database being upgraded, you might be required to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).
After the Upgrade
Review the upgrade spool log file and use the Post-Upgrade Status Tool. The Post-Upgrade Status Tool is a SQL script that ships with Oracle Database 11g Release 1 (11.1), and should be run in the environment of the new release.
APPROACH FLOWCHART




5. IMPLEMENTATION PLAN

Prerequisites for Implementation

Hardware requirements for upgradation

Hardware requirement for Upgrade (recommended)
CPU Intel Extended memory (EM64T) or AMD64
Processor speed 3 GHz
RAM Size 15 GB
PF Size Min 30 GB
PF Size Max 45 GB

Software requirements for up gradation

Software requirement (recommended)
Operating System Windows Server 2008 Enterprise Edition - 64 bit
Oracle Oracle server software 10.2.0.2.0
Oracle Patch 10.2.0.4.0 Patch set
Oracle Oracle server software 11.1.0.7.0


Drive Details
Drive Name Total Size in GB Required (in GB)
Local Disk (C Drive) 40 40
Swap Volume (D Drive) 40 40
Data Volume (E Drive) 250 250

Network Requirements

The computer on which you want to install Oracle Database should be connected to the network.





Tools / Resources Required
This section deals with the tools and the resources that are required to do the migration or upgrade of the Database that has been identified. This may contain the following:

1. The way in which the Original data can be transferred to the new server.
E.g. like Export dump or a Cold back of the database, etc
2. Tools that needs to be used. E.g. Oracle client, Import Utility, DBUA, etc
3. Two new servers are required (Primary and Standby).
4. Servers should be connected to network.
5. IGATE IMS Team should be able to login to new servers through CITRIX.
6. Administrator rights should be provided for IGATE IMS team login ids.
7. Oracle server software and patch set specified in the above section should be downloaded, and the location of the software should be shared.
Pre upgrade and Post upgrades activities

This includes following activities:
1. Timing tests
2. Data dictionary growth observations after upgradation.
3. Database resource usage observations, such as undo and temporary segment usage

Collecting this information will help to compare the current database (10.2.0.4.0) with the new Oracle Database (11.1.0.7.0).

Installation steps

1 What the database name is?

SQL> Select name from v$database;

2 What version is running?

SQL> Select * from v$version;

3 What option is installed?

SQL>Select VERSION, COMP_NAME, STATUS from dba_registry;


4 What is the count of the objects in that database?

SQL> Select count(*) from dba_objects;

5 How many invalid objects in database?

SQL> Select object_name, object_type from dba_objects where status='INVALID';

6 Install oracle 10.2.0.2.0 server software on the new test server.

7 Download 10.2.0.4.0 patch and upgrade Oracle to 10.2.0.4.0

8 Copy parameter file (init file), control files, Redo log files and Data files to the corresponding location of new server.

9 Make required modifications in init file (change location of control, datafile, directory location etc...

10 Create new TRAXMP instance and password file in new server.

SQL>ORADIM NEW –SID TRAXMP
SQL> ORAPWD FILE=C:\ORACLE\PRODUCT\10.2.0.\DB_1\DATABASE\PWDTRAXP.ORA PASSWORD=******** ENTRIES=100

11 Startup oracle instance in mount mode using initfile.

SQL> STARTUP NOMOUNT PFILE=’C:\ORACLE\PRODUCT\10.2.0.\DB_1\DATABASE\INITTRAXP.ORA’;

SQL>ALTER DATABASE MOUNT;

12 In mount mode, change the location of Data file, Redo log file in the control file.

SQL> ALTER DATABASE RENAME FILE ‘LOCATION\OLD_FILE_NAME’ TO
‘NEW_LOATION\NEW_FILE_NAME’;

13 Open the database.

SQL>ALTER DATABASE OPEN;

14 Perform health checkup (confirm that DB is running in 10.2.0.4.0).

15 Install Oracle 11.1.0.7.0 Server software in new server.

16 Run pre-upgrade information script(utl111i.sql – present in ORACLE_HOME\db_1\RDBMS\admin directory) in database running under 10.2.0.4.0.

SQL>SPOOL C:\PREUPGRADE.TXT
SQL>@utl111i.sql;
SQL>SPOOL OFF;

17 Make required modifications to database as recommended by pre-upgrade script.

18 Change oracle_home path pointing to 11.1.0.7.0

C:\>SET ORACLE_HOME=’C:\ORACLE\PRODUCT\11.1.0\DB_1’

19 Create directory structure in 11.1.0.7 .0 and copy the data file, log file and control files.

Create adump, bdump, cdump, pfile, dump directory under C:\ORACLE\PRODUCT\11.1.0\admin

20 Delete old instance and recreate new instance.

C:\>ORADIM –DELSID SID_NAME – To delete instance
C:\>ORADIM –NEW –SID SID_NAME

21 Edit pfile (compatibility and directory locations).

Set compatibility = 11.1.0.7.0

22 Startup nomount database using pfile.

SQL> STARTUP NOMOUNT PFILE=’C:\ORACLE\PRODUCT\11.1.0.\DB_1\DATABASE\INITTRAXP.ORA’;

23 Keep database in mount mode.

SQL>ALTER DATABASE MOUNT;

24 Change the location of data files and Redolog files in controlfiles.

SQL> ALTER DATABASE RENAME FILE ‘LOCATION\OLD_FILE_NAME’ TO
‘NEW_LOATION\NEW_FILE_NAME’;

25 Shutdown database.

SQL>SHUTDOWN IMMEDIATE;

26 Startup database in upgrade mode.

SQL>STARTUP UPGRADE;

27 Run catupgrd.sql script which is in ORACLE_HOME/RDBMS/ADMIN directory for upgrade.

SQL>@C:\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ADMIN\CATUPGRD.SQL;

28 Shutdown database.

SQL>SHUTDOWN IMMEDIATE;

29 Startup database normaly.

SQL>SET ORACLE_SID=SID_NAME;
SQL>STARTUP;

30 Run utlrp.sql script to find invalid objects (script which is in ORACLE_HOME/RDBMS/ADMIN directory)

SQL>@C:\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ADMIN\UTLRP.SQL;

31 If any invalid objects observed, recompile such objects.

SQL>SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS=’INVALID’;

SQL> ALTER OBJECT_TYPE OBJECT_NAME COMPILE;

32 Perform complete health checkup of database.

Check the database hit ratios.
Check table space usage details.
Check application level compatibility for 11g
Check database performance metrics.

Once the actual migration or the Upgrade is completed, the following things needs to be done:

1. Compare the Number of objects in both the old one and the new database (in case if a migration has been done to a different server.
2. Check for the INVALID state of the objects and compiling the same.
3. Check for the disabled constraints and then enabling the same. Also, the triggers need to be checked and enabled.
4. Reorganization of the tables or indexes if it is required.
5. Do an Analyze of the schemas in order to gain good performance.
6. Check for the application access and also the Performance of the database.


6. EFFORT ESTIMATION


S.No. Activity Resource/Effort Requirement Responsibility Comments/Status
1 Taking a cold Backup of the old Database
2 Setting up and installation of the new Oracle Server
3 Transferring of the original Production Data
4 Creating an Empty Database(optional)
5 Imports/Upgrade Activity
6 Checking of Invalid Objects and compiling those objects
7 User testing of the application

7. Rollback Strategy
Any Migration or an upgrade that is done needs a rollback strategy which needs to be done in case if the new system implemented is not fulfilling the expectations by the users or the project team. In order for this, a rollback strategy needs to be identified and should be kept in place. Should there be an upgrade failure it will be necessary to revert back to the previous versions. It will be more difficult and time consuming to roll back from an in-place upgrade. A side-by-side upgrade will work better is simpler to rollback from.

The roll back strategy should be in such a way that either the database needs to be restored to the old status in the same server or we should have the correct copy of the database before starting the migration so that the old system can be made available to the users in a short span of time reducing the outage.

Procedure:

1. Take a Complete backup of the Oracle Home, Oracle Inventory and Database before the activity.
2. If the up gradation activity fails, it should be rolled back to 10.2.0.4.0 using the backup.

8. Risks and Risk Mitigation Process

1. Risk is low; if the up gradation fails or Application compatibility fails then it can
be rolled Back to 10.2.0.4.0 using backup.

No activity or migration is risk free as there is always a risk associated with any kind of migration that is being done. The following are some of the risks that might be faced during the migration:

1. Getting the correct time window or downtime of the Production databases for doing the migration.
2. Identifying the Proper tools or methodology to do the migration
3. Availability of the Proper disk space for migrating the database
4. Suitable hardware identification and sizing
5. Risks involved in getting the original data because of network connectivity.

9. Testing
Application tests and performance tests should be planned and implemented to confirm in advance that no major compatibility or performance problems will arise in the new system. This reduces the risk of migration failure and other risks following migration.

Minimum tests

All or parts of applications are transferred from the current system to the new system (or test environment). Tests are performed without enabling new functions. In
minimum tests, all possible potential problems cannot be discovered , but at least
problems related to application launching and invocation can be checked.

Functional tests

Functional tests include testing of all databases, network, and application components. These are conducted after new and existing functions of the system are upgraded. Functional tests confirm that individual system components and new functionality work properly as they did before the upgrade.

Integration tests

Integration tests check interactions among system components. Specifically, these tests assess the following points:
 Confirms that applications (Pro*C/C++, JDBC, ODBC) and other software causes no problems in the new software.
 Performs analysis to determine if changes in data type and data dictionary data
affect front-end applications.
 Checks connectivity when applications are connected through SQL*Net, Net8, or Oracle Net services.

Performance tests

To check performance, plan and carry out the following performance tests. Before performing the tests, you must obtain performance information on the current system based on the performance requirements of the new system. This will help determine whether the performance requirements of the new system are adequate and help identify factors leading to performance degradation, based on comparisons.

Confirming concurrent performance

To confirm concurrent performance, multiple factors comprising actual business
Processes are used to generate loads similar to those in the actual system and the
Performance of the overall system is analyzed. If the system fails to meet the required
Unit performance, confirming concurrent performance will not help improve
Performance. Additionally, the following problems not generated during confirmation
Of unit performance can also emerge during confirmation of concurrent performance.

• High CPU usage, CPU wait
• High disk usage, disk wait
• Exclusive control (lock) for files and databases
• Process wait due to inter-process communication

As described above, migration to a new system requires planning and confirmation of compatibility of the current system (applicability, extent of results of any incompatibilities, countermeasures), application tests, and performance tests. This increases required man-hours, entails higher costs, and results in system failures and other problems in various work processes. In our verification testing, we attempted to verify whether use of application tests, Database Replay to confirm concurrent performance, and SQL Performance Analyzer to confirm unit performance would reduce risks and facilitate the process of migration.

Once the migration or the upgrade of the database is completed, we need to rigorous testing on the new environment that has been developed. This involves active participation from the end users as well. During the testing phase, it is important that we check on the following things:

1. Performance of the new database
2. Validity of the objects in the database
3. Correctness and matching of the data in the tables, number of objects in the old and the new database
4. Access and the working of the application when compared to the old environment.