Upgrade Oracle 11g R2 (11.2.0.4) Database to Oracle 12c (12.2.0.1)

Step 1:

Upgrade Path for Oracle Database to Oracle 12c Release 1 (12.1)

For example:

If you are upgrading from 11.2.0.1 or 11.1.0.6, then you must first upgrade to Oracle Database 11g Release 2 (11.2.0.2).
If you are upgrading from 10.2.0.2, 10.2.0.3, 10.2.0.4, or 10.1.0.5, then you must first upgrade to 10.2.0.5 or later.
For release 9.2.0.8, you must first upgrade to an intermediate Oracle Database release, as follows: 9.2.0.8 -> 11.2.0.2 or 11.2.0.3 -> 12.1

Upgrading 11.2.0.4 to 12.2.0.1, on Linux (x64_86) platform

Step 2 :

Requirements and recommendations for Source  database 

Either take a cold or hot backup of the source database (advisable to have cold backup).

a) Take a backup using RMAN
(or)
b) Perform Cold Backup  ( if  your database is in NOARCHIVELOG mode)


Step.3:

check source database before upgrade

-Ensure That No Files Need Media Recovery Before Upgrading
-Ensure That No Files Are in Backup Mode Before Upgrading
-Purge the Database Recycle Bin Before Upgrading
-Disable all batch and cron jobs
-location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME

Step.4:

Run the new preupgrade check script preupgrd.sql, in  11.2.0.4 environment – it will generate 3 files in $ORACLE_HOME/cfgtoollogs/$SID/preupgrade:

SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql

preupgrade.log
preupgrade_fixups.sql
postupgrade_fixups.sql

Step.5:

Verify the preupgrade.log and make necessary changes

Example of preupgrade.log:

-WARNING: --> Process Count may be too low
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE (bydefault 150)
-WARNING: --> Inactive DBIDs found in AWR
AWR contains inactive DBIDs which may need additional updating after upgrading.
-WARNING: --> Enterprise Manager Database Control repository found in the database In Oracle Database 12c, Database Control is removed during the upgrade. To save time during the Upgrade, this action can be done prior to upgrading using the following steps after copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql Without the set echo and serveroutput commands you will not be able to follow the progress of the script.

-WARNING: --> Existing DBMS_LDAP dependent objects Database contains schemas with objects dependent on DBMS_LDAP package. Refer to the Upgrade Guide for instructions to configure Network ACLs. USER APEX_030200 has dependent objects.

-INFORMATION: -->
OLAP Catalog(AMD) exists in database Starting with Oracle Database 12c, OLAP is desupported. If you are not using the OLAP Catalog component and want to remove it, then execute the ORACLE_HOME/oraolap/admin/catnoamd.sql script before or after the upgrade.

-INFORMATION: --> Older Timezone in use

Please gather dictionary statistics 24 hours prior to upgrading the database.
To gather dictionary statistics execute the following command while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;

Step.6:

Prepare spfile for the 12c upgrade according to the output from preupgrade.log:
(Please note: Best Practice would be to edit the init.ora for the upgrade manually. You could do so – the way i propose here is just a shortcut avoiding manual edit steps)
SQL> create pfile from spfile;


Step.7:

Execute the preupgrade_fixups.sql – it was created by preupgrd.sql in directory ORACLE_HOME/cfgtoollogs/$SID/preupgrade

SQL>@ORACLE_HOME/cfgtoollogs/$SID/preupgrade/preupgrade_fixups.sql

Please note that the preupgrade_fixups.sql script will still complain about PROCESSES being set too low. This is because I have used the ALTER SYSTEM command to adjust the parameter, but that adjustment will not take effect until the database is shutdown and restarted. Because I specified SCOPE=SPFILE, this parameter will be set correctly for the upgrade.

Step.8:

Shutdown the UPGRADE database:
SQL >shutdown immediate
SQL >exit

Step.9:

Copy the passwordfile and pfile/spfile from the 11g home to the 12c home.

$ cp /u01/app/oracle/product/11.2.0/dbs/spfileUPGR.ora /u01/app/oracle/product/12.1.0.2/dbs/
$ cp /u01/app/oracle/product/11.2.0/dbs/orapwUPGR /u01/app/oracle/product/12.1.0.2/dbs/

Step.10:

Set new ORACLE HOME of 12c location
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1/

Step.11:

SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open upgrade;
SQL> exit

Step.12:

Run catctl.pl

Go to $ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started catctl.pl, $ORACLE_HOME/rdbms/admin Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.

logfile and upgrade summary reports are created in @ORACLE_HOME/cfgtoollogs/$SID

Note: It will create logfiles based on process in $ORACLE_HOME/rdbms/admin

example:

rw-r--r-- 1 oracle oinstall 5.4M Dec 18 10:41 catupgrd3.log
-rw-r--r-- 1 oracle oinstall 4.9M Dec 18 10:41 catupgrd2.log
-rw-r--r-- 1 oracle oinstall 4.2M Dec 18 10:41 catupgrd1.log
-rw-r--r-- 1 oracle oinstall 228M Dec 18 11:01 catupgrd0.log

Step.13:

Run postupgrade_fixups.sql which was created earlier.
@ORACLE_HOME/cfgtoollogs/$SID/preupgrade/postupgrade_fixups.sql

The database is using timezone datafile version 14 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data.  For further information, refer to My Oracle Support Note 1585343.1

Step.14

Run below scripts:

SQL> @?/rdbms/admin/utlu122s.sql
SQL> @?/rdbms/admin/catuppst.sql
Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @catuppst.sql
This script can be run concurrently with utlrp.sql.
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

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

Step.15:

Set COMPATIBALE parameter value to 12.2.0, start the listener with new home and count the invalid objects

Step.16:

Restart the database and check the version of a database

What Happened When You Opened The Database In Startup Upgrade Mode

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

Find And Replace In Vi editor

Syntax for Text Substitution inside the vi editor is,

:[range]s[ubstitute]/{pattern}/{string}/[Flags] [count]

Following are the possible Flags

[g] -> replace all occurances in the line.
[c] -> Confirm each substitution
[i] -> make case insensitive

Case 1: Replace a text with another in the whole file

:%s/oldText/newText/g

where %s indicates all lines in the file.

Case 2: Replace a text within a single line

:s/oldText/newText/g

No range was specified therefore it replaces text in the single line.

:s/I/oldText/newText/g

where I indicates case insensitive

Case 3: Replace a text within a range of lines

:1,10s/oldText/newText/g

replaces text between line 1 and 10

Case 4: Replace text in only the first N lines

:s/oldText/newText/g 5

replaces the text in 5 lines from the current cursor position.

Case 5: Replace the whole word and not the partial word

:s/\<his\>/her/

The standard replace will make the word history with herstory. Enclose the word with < and > to ensure the search is for the whole word

and not for partial one.

Case 6: Interactive Find and Replace

:%s/oldText/newText/gc

This will prompt for confirmation.

replace with newText (y/n/a/q/l/^E/^Y)?

y -> replace the highlighted word. After replace moves to the next word.
n -> does not replace the highlighted word. Moves to the next word.
a -> replace all the highlighted words.
l -> replaces the current highlighted word and terminates the find and replace.

pwd

/export/home/oratest/CLONE_24Jul2022

vi Db_config_bkp.sql

Add below contents in your file.

SQL>@Db_config_bkp.sql

Prompt

Prompt  === copying init.ora and spfiles  ===

Prompt  ========================================

Prompt

host cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/oraInst.loc /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/root.sh  /export/home/oratest/CLONE_24Jul2022

host cp $ORACLE_HOME/*.env  /export/home/oratest/CLONE_24Jul2022

host cp -R $ORACLE_HOME/network/admin/* /export/home/oratest/CLONE_24Jul2022

spool /export/home/oratest/CLONE_24Jul2022/Precloneinfo.log

alter database backup controlfile to trace as '/export/home/oratest/CLONE_24Jul2022/ctrl_$ORACLE_SID.sql';

create pfile='/export/home/oratest/CLONE_24Jul2022/initpfile.ora' from spfile;

Prompt

Prompt  === dblinks  ===

Prompt  ========================================

Prompt

begin

   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);

end;

/

set lines 200

set pages 200

col DB_LINK for a50

col USERNAME for a20

col HOST for a50

select * from dba_db_links;

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

 ||'connect to ' || L.USERID || ' identified by '

 ||L.PASSWORD||' using ''' || L.host || ''''

 ||chr(10)||';' TEXT

 FROM sys.link$ L, sys.user$ U

 WHERE L.OWNER# = U.USER#;

Prompt

Prompt  === dba_directories  ===

Prompt  ========================================

Prompt

set pages 800

set lines 300

col DIRECTORY_NAME for a40

col DIRECTORY_PATH for a90

select * from dba_directories;

set pages 800

select 'create or replace directory '||DIRECTORY_NAME||' as '''||DIRECTORY_PATH||''';' from dba_directories;

archive log list

show parameter backgr

Prompt

Prompt  === temp_files  ===

Prompt  ========================================

Prompt

set lines 200

col FILE_NAME for a80;

select file_name,tablespace_name,bytes/1024/1024,status from dba_temp_files;

Prompt

Prompt  === data_files  ===

Prompt  ========================================

Prompt

select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files;

Prompt

Prompt  === control_files  ===

Prompt  ========================================

Prompt

select name from v$controlfile;

Prompt

Prompt  === redolog_files  ===

Prompt  ========================================

Prompt

col MEMBER for a60

set pages 800

set lines 200

select * from v$logfile;

select * from v$log;

Prompt

Prompt  === all directories of db CRD files  ===

Prompt  ========================================

Prompt

select substr(name,1,instr(name,'/',-1)) from v$datafile

union

select substr(name,1,instr(name,'/',-1)) from v$tempfile

union

select substr(member,1,instr(member,'/',-1)) from v$logfile

union

select substr(name,1,instr(name,'/',-1)) from v$controlfile;

Prompt

Prompt  === status of CRD files  ===

Prompt  ========================================

Prompt

select distinct status from v$datafile

union

select distinct status from v$tempfile

union

select distinct status from v$logfile

union

select distinct status from v$controlfile;

Prompt

Prompt  === size and count of datafiles on each mount  ===

Prompt  ========================================

Prompt

set pages 200

set lines 300

col DATAFILES_DIR for a50

select substr(name, 1, instr(name, '/', -1)) datafiles_DIR, count(1) datafiles_count,sum(bytes/1024/1024/1024) size_in_GB from v$datafile group by rollup (substr(name, 1, instr(name, '/', -1))) order by 1;

Prompt

Prompt  === size and count of tempfiles on each mount  ===

Prompt  ========================================

Prompt

set pages 200

set lines 300

col TEMPFILES_DIR for a50

select substr(name, 1, instr(name, '/', -1)) tempfiles_DIR, count(1) tempfiles_count,sum(bytes/1024/1024/1024) size_in_GB from v$tempfile group by rollup (substr(name, 1, instr(name, '/', -1))) order by 1;

Prompt

Prompt  ===all file locations of db ===

Prompt  ========================================

Prompt

select distinct substr(name, 1, instr(name, '/', -1)) || '  -- DATAfiles' from v$datafile union

select distinct substr(name, 1, instr(name, '/', -1)) || '  -- TEMPfiles' from v$tempfile union

select distinct substr(member, 1, instr(member, '/', -1)) || '  -- LOGfiles' from v$logfile union

select distinct substr(name, 1, instr(name, '/', -1)) || '  -- Controlfiles' from v$controlfile;

select node_name from apps.fnd_nodes;

show parameter pfile

show parameter backgr

show parameter utl

Prompt *****Public synonym backup before import***********

set long 900000

select dbms_metadata.get_ddl(object_type=>'SYNONYM',name=>synonym_name,schema=>'PUBLIC') from all_synonymswhere owner='PUBLIC' and table_owner not in ('SYS')

Prompt

Prompt  === tablespace creation script  ===

Prompt  ========================================

Prompt

set long 900000

select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Prompt

Prompt  === user creation script  ===

Prompt  ========================================

Prompt

set long 9999999

select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users;

Prompt

Prompt  === username -- password script ===

Prompt  ========================================

Prompt

select 'alter user '|| name||' identified by values  '|| ''''|| password||''''||';' from SYS.USER$ WHERE password is not null;

Prompt

Prompt  === username -- its  Default tablespace  ===

Prompt  ========================================

Prompt

set lines 200 pages 9999

SELECT username, default_tablespace,temporary_tablespace,profile FROM dba_users order by 1;

select name,open_mode,log_mode,database_role,dbid from v$database;

col host_name fOR a36

col created fOR a16

col start_time fOR a16

col INSTANCE_NAME fOR a13

col OPEN_MODE fOR a10

set lines 280

select d.name db_name,i.INSTANCE_NAME,i.HOST_NAME,d.open_mode,d.database_role,to_char(d.created,'DD-MON-YY HH24:MI') created,to_char(i.startup_time,'DD-MON-YY HH24:MI') start_time from v$database d, gv$instance i;

spool off

How To Drop An UNDO Tablespace

Following error while dropping the undo tablespace:

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS                               D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1                       D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX                             D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM                            D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE                          D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

As the error indicate that the undo tablespace is in use so i issue the following command.

SQL> alter tablespace undotbs1  offline;
alter tablespace undotbs1  offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace.

Therefore, to drop undo  tablespace, we have to perform following steps:

1.) Create new undo tablespace
2.) Make it defalut tablepsace and undo management manual by editting parameter file and restart it.
3.) Check the all segment of old undo tablespace to be offline.
4.) Drop the old tablespace.
5.) Change undo management to auto by editting parameter file and restart the database

Step 1 : Create Tablespace   :  Create undo tablespace undotbs2 

SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             360711792 bytes
Database Buffers           58720256 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

Step 3: Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------ ------------------------------ ------------------------------ ----------------
SYS                 SYSTEM                                     SYSTEM                            ONLINE
PUBLIC       _SYSSMU10_1192467665$          UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU1_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU2_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU3_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU4_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU5_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU6_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU7_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU8_1192467665$           UNDOTBS1                       OFFLINE
PUBLIC        _SYSSMU9_1192467665$           UNDOTBS1                       ONLINE
PUBLIC      _SYSSMU12_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU13_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU14_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU15_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU11_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU17_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU18_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU19_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU20_1304934663$          UNDOTBS2                        OFFLINE
PUBLIC      _SYSSMU16_1304934663$          UNDOTBS2                        OFFLINE

21 rows selected.

If any one the above segment is online then change it status to offline by using below command .
SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

Step  5 : Change undo management to auto and restart the database

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             364906096 bytes
Database Buffers           54525952 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace
NAME                                       TYPE        VALUE
------------------------------------   ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2