Perform the below in sequence to complete the upgrade tasks by executing SQL scripts and utilities from command line to upgrade a database to the new Oracle Database release 12c 12.2.0.1.
Current ORCALE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
New ORACLE_HOME=/u02/app/oracle/product/12.2.0.1/db_1
Run the preupgrade tool:
[oracle@ed-oldblin] # /u01/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u02/app/oracle/product/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/
Run the preupgrade_fixup.sql from 11g home
SQL> @preupgrade_fixup.sql
Run the /u02/app/oracle/product/12.2.0.1/db_1/rdbms/admin/emremove.sql to remove the EM console from 12c home
Run this block to refresh the materialized views.
Increase the number of processes and purge the recycle bin
SQL> alter system set processes=300 scope=spfile;
SQL> PURGE DBA_RECYCLEBIN;
Now SHUTDOWN the database
Copy the PFILE and PASSWORD file from 11g home to 12c home.
SET NEW ORACLE HOME
[oracle@ed-oldblin] # export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1/
Start the database in upgrade mode from 12c home
[oracle@ed-oldblin] # sqlplus / as sysdba
Connected to idle instance
SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open upgrade;
Run catctl.pl
Run postupgrade_fixups.sql
To fix the time zone from 14 to 26 we need to download dbms_dst scriptV1.9.zip from metalink and apply it to the new oracle home.
SQL> select version from v$timezone_file;
Execute utlu122s.sql
Execute catuppst.sql
Execute utlrp.sql script to compile invalid objects.
Set COMPATIBALE parameter value for 12.2.0 as well as start the listener with 12.2 home.
Migrating a Database across platforms.
The export utility is the only method to migrate database running from one platform to another.
1. Query in source database to find the name of tablespace and make full export of source database.
SQL> select tablespace_name from dba_tablespaces;
$ exp system/manager FULL=y FILE=myfullexp.dmp
2. Move the dump file to the target database and before import the dumpfile create required tablespace.
$ imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp
