Migrate and Upgrade Oracle 11gR2 Windows Database to Oracle 12cR2 Linux Database using Datapump

 

Migrate and Upgrade Oracle 11gR2 Windows Database to Oracle 12cR2 Linux Database using Datapump

In this article, we are going to migrate the oracle database from windows to linux server with database upgrade from Oracle Database 11.2.0.1 to 12.2.0.1 using export/import.

DESCRIPTION:

1) Pre-checks in Source database
2) Export Source database
3) Pre-checks in Target database
4) Import into Target database
5) Post-checks in Target database

1) Pre-checks in Source database:

Step 1 :Check the Database Size in SOURCE:-

SQL> @db_size.sql

Step 2 :Execute the below script to check schema objects are placed in which tablespaces except the default schemas:-

SQL> @objects_in_tablespaces.sql

Step 3 :Execute the below script in Source Database to get the DDL of all the tablespaces except the default tablespaces:-

SQL> @tablespace_ddl.sql

Step 4 :Compile Invalid Objects if any in SOURCE:-

SQL> @?/rdbms/admin/utlrp.sql

Check the invalid count now in SOURCE:-

SQL> select count(*) from dba_objects where status=’INVALID’;

Step 5 :Execute the below script on Source and verify the output:-

SQL> @nls_characterset.sql

2) Export Source database:

Export Source Database using expdp:-
Step 1 :Check Estimate Size of Dumpfile:-

Make sure free space is more than  the estimated size in the export directory:

$ expdp directory=MOM_DIR full=Y nologfile=Y estimate_only=Y

Step 2 :Export Roles & Privileges:-

$ expdp directory=MOM_DIR dumpfile=WINDOWS_LINUX_ROLES.DMP logfile=WINDOWS_LINUX_ROLES.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step 3 :Export the database in full and exclude the default schema to reduce the export time with “EXCLUDE” parameter as below:-

$ expdp directory=MOM_DIR dumpfile=EXP_WINDOWS_LINUX_FULL.DMP logfile=EXP_WINDOWS_LINUX_FULL.log full=Y EXCLUDE=SCHEMA:\”IN \(\’ANONYMOUS\’, \’APEX_030200\’, \’APEX_PUBLIC_USER\’, \’APPQOSSYS\’, \’CTXSYS\’, \’DBSNMP\’, \’DIP\’, \’EXFSYS\’, \’FLOWS_FILES\’, \’MDDATA\’, \’MDSYS\’, \’MGMT_VIEW\’, \’OLAPSYS\’, \’ORACLE_OCM\’, \’ORDDATA\’, \’ORDPLUGINS\’, \’ORDSYS\’, \’OUTLN\’, \’OWBSYS\’, \’OWBSYS_AUDIT\’, \’SCOTT\’, \’SI_INFORMTN_SCHEMA\’, \’SPATIAL_CSW_ADMIN_USR\’, \’SPATIAL_WFS_ADMIN_USR\’, \’SYS\’, \’SYSMAN\’, \’SYSTEM\’, \’WMSYS\’, \’XDB\’, \’XS$NULL\’\)\”

cat EXP_WINDOWS_LINUX_FULL.txt

Once the export is completed check the log file and make sure no errors in it and then both the dumpfiles to the target server.

3) Pre-checks in Target database:

Step 1 :

Create a fresh database in the Target Server as described here with appropriate characterset, in order to avoid characterset conversion error.

Step 2 :

Create the required tablespaces as we already taken the tablespace DDL from SOURCE in Step 3 of Pre-checks in SOURCE.

Step 3 :

Create a directory for import on the target server in OS and database level:-

$ mkdir -p /u02/dpdump/LINUX_MIG

SQL> create directory DPDUMP as ‘/u02/dpdump/LINUX_MIG’;

SQL> @dba_directories.sql

4) Import into Target database:

Step 1 :

Import Roles & Privileges:-

$ impdp directory=DPDUMP dumpfile=WINDOWS_LINUX_ROLES.DMP logfile=WINDOWS_LINUX_ROLES.log full=Y

Step 2 :

Import the database:-

$ impdp directory=DPDUMP dumpfile=EXP_WINDOWS_LINUX_FULL.DMP logfile=IMP_WINDOWS_LINUX_FULL.log full=Y

Once the import is completed the review the log file for any errors.

cat IMP_WINDOWS_LINUX_FULL.txt

5) Post-checks in Target database:

Step 1 :

Compile Invalid Objects in TARGET:-

SQL> @?/rdbms/admin/utlrp.sql

Once done with above steps execute below command to verify target database:-
SQL> select count(*) from dba_objects where status=’INVALID’;

Step 2:

Query V$OPTION to get currently installed database options:-

SQL> select * from V$OPTION where value=’TRUE’ order by 1;

Step 3 :

Query DBA_REGISTRY to get currently installed database components:-

SQL> @dba_registry.sql

Step 4 :

Create database link to the source database to compare the schema object count in source & target :-

SQL> CREATE DATABASE LINK “SOURCE.DBLINK”
CONNECT TO “SYSTEM” IDENTIFIED BY <Password>
USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
oracledbwr101)(PORT = 1521)))
(CONNECT_DATA = (SID = WINORCL)))’;

Step 5 :

Execute the below scripts in target and verify the count of objects manually:-

Below script will provide the object count of schemas in SOURCE except the default schemas:
SQL> @object_count_source.sql

Below script will provide the object count of schemas in TARGET except the default schemas:
SQL> @object_count_local.sql

Step 6 :
Check the CONSTRAINTS count in both source & target:-

SQL> @dba_constraints_source.sql

SQL> @dba_constraints_local.sql

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Manual Upgrading Oracle Database From 11.2.0.4 to 12.2.0.1

Manual Upgrading Oracle Database From 11.2.0.4 to 12.2.0.1

Description:- 

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.

Current ORCALE_HOME=/u01/app/oracle/product/11.2.0.4/db_1

New ORACLE_HOME=/u03/app/oracle/product/12.2/db_1

Database Name: Test

1. Run the preupgrade tool:

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 12cR2.
This tool has reside in new oracle home.
/u03/app/oracle/product/12.2/db_1/rdbms/admin/preupgrade.jar

SYNTAX:
/u01/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u03/app/oracle/product/12.2/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/

After run the above tool we will get below sql files.

 Run the preupgrade_fixup.sql

A. To remove EM console.

B. Run the below block for refresh the materialized views.

C. We have to increase the number of processes and purge the recycle bin

2. Shutdown the database:

Take full database backup. we should  turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.

3. Copy the parameter and password file from the 11g home to the new 12c home.

4.  Set new ORACLE HOME of 12c location:

export ORACLE_HOME=/u01/app/oracle/product/12.2/db_1/

start the database in upgrade mode.

5. Run catctl.pl

catctl.pl is introduce in Oracle Database 12c, Parallel Upgrade Utility replaces the SQL Upgrade Utility catupgrd.sql. With Parallel Upgrade Utility, we can run upgrade scripts and processes in parallel. Using this script will increase our server CPU capacity and reduce the upgrade time.

-n is used for parallelism (new feature on oracle 12c)

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

6.  Run “postupgrade_fixups.sql” .

A. To fix the manual error related to timezone

For 12cR2, the new timezone is 26. So once the db upgrade is completed, we have to upgrade the dst timezone from 14 to 26

Download DBMS_DST_scriptsV1.9.zip file from metalink.

Check the timezone version:

Unzip DBMS_DST_scriptsV1.9.zip file we will get below sql files for upgrade the timezone.

Check the Current Timezone detail:

Apply the New Timezone:

Check the timezone version:

Now it’s changed from 14 to 26.

7. Execute utlu122s.sql:

This tool is supplied with Oracle Database and displays the version and elapsed upgrade time for each component in DBA_REGISTRY.

8. Execute catuppst.sql:

If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, this script will automatically applies those patch set update to the database.

9. Execute utlrp.sql script to compile  invalid objects.

10. Set COMPATIBALE parameter value for 12.2.0 as well as  start the listener with 12.2 home.

11. Finally to check the component and version using dba_registry view.

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba