DBA Hub

📋Steps in this guide1/21

Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1

Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1 Contents

oracle upgradeintermediate
by OracleDba
11 views
1

Overview

Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1 Contents 1. Environment
2

Section 2

2. Run preupgrade script 3. View Preupgrade log

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: DELL
DB VERSION		:
11.2.0.4.0
DB Home Path 		:
/u01/app/oracle/product/11.2.0.4
Datafile Location 	: /u02/oradata/DELL

Target DB VERSION 	:
12.2.0.1
Target DB Path		:
/u01/app/oracle/product/12.2.0.1
Upgrade Method		:
Manual

/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar TERMINAL TEXT
--- OR ---
/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
[oracle@rac1 ~]$
mkdir -p /home/oracle/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 preupgrade]$
/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
Preupgrade generated files:
    /home/oracle/preupgrade/preupgrade.log
    /home/oracle/preupgrade/preupgrade_fixups.sql
    /home/oracle/preupgrade/postupgrade_fixups.sql
[oracle@rac1 preupgrade]$
3

Section 3

4. Remove the EM repository 5. Remove OLAP Catalog

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
[oracle@rac1 preupgrade]$ cat
/home/oracle/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  DELL
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Expression Filter                      [to be upgraded]  VALID
  Rule Manager                           [to be upgraded]  VALID
  Oracle Application Express             [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(
AUTOFIXUP
)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     EXAMPLE                            346 MB  DISABLED      309 MB  None
     SYSAUX                             500 MB  ENABLED      1411 MB  None
     SYSTEM                             750 MB  ENABLED      1260 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            65 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.




   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
processes                                        300
RECOMMENDED ACTIONS
  ===================
+ Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
     12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

     Step 1: If database control is configured, stop EM Database Control,
     using the following command

       $> emctl stop dbconsole

     Step 2: 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.

     The database has an Enterprise Manager Database Control repository.

     Starting with Oracle Database 12c, the local Enterprise Manager Database
     Control does not exist anymore. The repository will be removed from your
     database during the upgrade.  This step can be manually performed before
     the upgrade to reduce downtime.

   +
Remove OLAP Catalog
by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.

     Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
     desupported and will be automatically marked as OPTION OFF during the
     database upgrade if present. Oracle recommends removing OLAP Catalog
     (OLAP AMD) before database upgrade.

   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 11.2.0.4
     Oracle Database Performance Tuning Guide.

   + Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
     trigger or drop and re-create the trigger with a user that was granted
     directly with such. You can list those triggers using "SELECT OWNER,
     TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
     OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
     PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

     There is one or more database triggers whose owner does not have the
     right privilege on the database.

     The creation of database triggers must be done by users granted with
     ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
     directly.

   +
Please make sure that all the MVs are refreshed and sys.sumdelta$
becomes empty before doing upgrade, unless you have strong business
     reasons not to do so. You can use dbms_mview.refresh() to refresh the
     MVs except those stale ones  to be kept due to business need. If there
     are any stale MVs depending on changes in sys.sumdelta$, do not truncate
     it, because doing so will cause wrong results after refresh.

     There is one or more non-fresh MV in the database or sumdelta$ is not
     empty.

     Oracle recommends that all materialized views (MV's) are refreshed
     before upgrading the database because this will clear the MV logs and
     the sumdelta$ table, and make the UPGRADE process faster. If you choose
     to not refresh some MVs, the change data for those MV's will be carried
     through the UPGRADE process. After UPGRADE, you can refresh the MV's and
      MV incremental refresh should work in normal cases.

  INFORMATION ONLY
  ================
   + Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 3.2.1.00.12 and will need to be
     upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before
     the database upgrade.  Refer to My Oracle Support Note 1088970.1 for
     information on APEX installation upgrades.

   + Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
     5315 MB of archived logs.  Check alert log during the upgrade that there
     is no write error to the destination due to lack of disk space.  Execute
     'archive log list' and query v$archive_dest for more
     LOG_ARCHIVE_DEST_ destinations to check.

     Archiving cannot proceed if the archive log destination is full during
     upgrade.

     Archive Log Destination:
      Parameter    :  LOG_ARCHIVE_DEST_1
      Destination  :  /u02/arch/DELL/

     The database has archiving enabled and LOG_ARCHIVE_DEST_ set.  The
     upgrade process will need free disk space in the archive log
     destination(s) to generate archived logs to.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + If you use the -T option for the database upgrade, then run
     $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
     to VALIDATE and UPGRADE any user tables affected by changes to
     Oracle-Maintained types.

     There are user tables dependent on Oracle-Maintained object types.

     If the -T option is used to set user tablespaces to READ ONLY during the
     upgrade, user tables in those tablespaces, that are dependent on
     Oracle-Maintained types, will not be automatically upgraded. If a type
     is evolved during the upgrade, any dependent tables need to be
     re-validated and upgraded to the latest type version AFTER the database
     upgrade completes.

   + Upgrade the database time zone version using the DBMS_DST package.

     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.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.

   + Gather statistics on fixed objects two weeks after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

     This recommendation is given for all preupgrade runs.

     Fixed object statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans.  Those
     statistics are specific to the Oracle Database release that generates
     them, and can be stale upon database upgrade.

  INFORMATION ONLY
  ================
   + Check the Oracle documentation for the identified components for their
     specific upgrade procedure.

     The database upgrade script will not upgrade the following Oracle
     components:  OLAP Catalog,OWB

     The Oracle database upgrade script upgrades most, but not all Oracle
     Database components that may be installed.  Some components that are not
     upgraded may have their own upgrade scripts, or they may be deprecated
     or obsolete.

[oracle@rac1 preupgrade]$

[oracle@rac1 preupgrade]$ emctl stop dbconsole

SQL>
select name,open_mode,version from v$database,v$instance;
NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
DELL      READ WRITE           11.2.0.4.0

SQL>
!ls -ltr /u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql
-rw-r--r--. 1 oracle dba 20740 Aug  8  2016 /u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql

SQL>
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>
4

Section 4

6. Update INITIALIZATION PARAMETERS 7. Gather DICTIONARY STATS

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> !ls -lrt /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13  2013 /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql

SQL>
@/u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql

SQL>
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
System altered.

SQL>
5

Section 5

8. Purge Recyclebin 9. Refresh MVs

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL>
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.

SQL>

SQL>
PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.

SQL>

SQL>
declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/
2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>
6

Section 6

10. Run preupgrade_fixups.sql 11. Verify archive log dest size

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL>
@/home/oracle/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-15 20:14:51

For Source Database:     DELL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
em_present                Passed  None
amd_exists                Passed  None
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Passed  None
mv_refresh                Passed  None
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.
PL/SQL procedure successfully completed.

SQL>

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations, including standby side
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination
/u02/arch/DELL/
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL>
!df -h /u02/arch/DELL/
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G   24G
18G
57% /u02

SQL>
7

Section 7

18G 12. Stop LISTENER 13. Create Flashback Guaranteed Restore Point

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
[oracle@rac1 ~]$
ps -ef | grep tns
[oracle@rac1 ~]$
lsnrctl stop LISTENER

*** NO need to enable Flashback Database from 11.2.0.1
onward
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version
SQL>
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO   <------
SQL>

SQL>
select name,open_mode,log_mode from v$database;
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
DELL      READ WRITE
ARCHIVELOG <------
SQL>


SQL>
show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string
11.2.0.4.0 <----
SQL>


SQL>
alter system set db_recovery_file_dest='/u02/fast_recovery_area/DELL';
System altered.

SQL>
alter system set db_recovery_file_dest_size=10G;
System altered.

SQL>

SQL>
show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/fast_recovery_area/DELL
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
SQL>

[oracle@rac1 ~]$ df -h /u02/fast_recovery_area/DELL
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G   24G   18G  57% /u02
[oracle@rac1 ~]$

SQL>
select * from V$restore_point;
no rows selected

SQL>

SQL>
create restore point pre_upgrade guarantee flashback database;
Restore point created.

SQL>

SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 190
SQL>
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME                 GUARANTEE_ TIME
-------------------- ---------- ---------------------------------
PRE_UPGRADE          YES        15-SEP-18 09.53.59.000000000 PM
SQL>
8

Section 8

create restore point pre_upgrade guarantee flashback database; PRE_UPGRADE YES 15-SEP-18 09.53.59.000000000 PM 14. Shutdown Database 15. Copy init and password files from 11g to 12c dbs home

Code/Command (click line numbers to comment):

1
2
3
4
5
6
SQL>
shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
9

Section 9

15. Copy init and password files from 11g to 12c dbs home 16. Startup DB in Upgrade mode startup upgrade;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
[oracle@rac1 ~]$
cd /u01/app/oracle/product/11.2.0.4/dbs
[oracle@rac1 dbs]$
ls -ltr *DELL*
-rw-r-----. 1 oracle dba   24 Aug 14 19:48 lkDELL
-rw-r-----. 1 oracle dba 1536 Aug 14 19:50 orapwDELL
-rw-r-----. 1 oracle dba 2560 Sep 15 20:35 spfileDELL.ora
-rw-rw----. 1 oracle dba 1544 Sep 15 21:03 hc_DELL.dat
[oracle@rac1 dbs]$
cp
orapwDELL
spfileDELL.ora
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$
ls -ltr /u01/app/oracle/product/12.2.0.1/dbs/*DELL*
-rw-r-----. 1 oracle dba 2560 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/spfileDELL.ora
-rw-r-----. 1 oracle dba 1536 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/orapwDELL
[oracle@rac1 dbs]$

[oracle@rac1 ~]$
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
[oracle@rac1 ~]$
export ORACLE_SID=DELL
[oracle@rac1 ~]$
PATH=/u01/app/oracle/product/12.2.0.1/bin:$PATH; export PATH
[oracle@rac1 ~]$
which sqlplus
/u01/app/oracle/product/12.2.0.1/bin/sqlplus
[oracle@rac1 ~]$
[oracle@rac1 ~]$
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 15 22:05:13 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
startup upgrade;
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>

SQL>
select instance_name,version,status from v$instance;
INSTANCE_NAME    VERSION           STATUS
---------------- ----------------- ------------
DELL             12.2.0.1.0
OPEN MIGRATE  <----
SQL>
10

Section 10

17. Run catctl.pl with catupgrd.sql nohup /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql & Grand Total Upgrade Time: [0d:1h:24m:24s] select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
[oracle@rac1 ~]$
mkdir -p /home/oracle/whileupgrade
[oracle@rac1 ~]$
cd /u01/app/oracle/product/12.2.0.1/rdbms/admin
[oracle@rac1 admin]$
nohup /u01/app/oracle/product/12.2.0.1/perl/bin/perl
catctl.pl
-l /home/oracle/whileupgrade -n 4
catupgrd.sql
&
[1] 16004
[oracle@rac1 admin]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac1 admin]$
disown
[oracle@rac1 admin]$
ps -ef | grep -i catctl.pl
oracle   16004  6189  1 22:22 pts/0    00:00:01 /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql
oracle   16324  6189  0 22:23 pts/0    00:00:00 grep -i catctl.pl
[oracle@rac1 admin]$

[oracle@rac1 admin]$
more nohup.out
Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/whileupgrade
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170107]
..
..  
..
..
LOG FILES: (/home/oracle/whileupgrade/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/whileupgrade/upg_summary.log  <----
Grand Total Upgrade Time:    [0d:1h:24m:24s]
[oracle@rac1 admin]$


SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
SQL> SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- -----------
CATALOG    Oracle Database Catalog Views
12.2.0.1.0
UPGRADED
CATPROC    Oracle Database Packages and Types
12.2.0.1.0
UPGRADED
JAVAVM     JServer JAVA Virtual Machine
12.2.0.1.0
UPGRADED
XML        Oracle XDK
12.2.0.1.0
UPGRADED
CATJAVA    Oracle Database Java Packages
12.2.0.1.0
UPGRADED
APS        OLAP Analytic Workspace
12.2.0.1.0
UPGRADED
OWM        Oracle Workspace Manager
12.2.0.1.0
UPGRADED
CONTEXT    Oracle Text
12.2.0.1.0
UPGRADED
XDB        Oracle XML Database
12.2.0.1.0
UPGRADED
ORDIM      Oracle Multimedia
12.2.0.1.0
UPGRADED
SDO        Spatial
12.2.0.1.0
UPGRADED
XOQ        Oracle OLAP API
12.2.0.1.0
UPGRADED
APEX       Oracle Application Express
5.0.4.00.12
UPGRADED
13 rows selected.

SQL>
11

Section 11

18. Run postupgrade_fixups.sql depend_usr_tables Failed Manual fixup recommended. old_time_zones_exist Failed Manual fixup recommended. 19. Update Timezone

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
[oracle@rac1 preupgrade]$ pwd
/home/oracle/preupgrade
[oracle@rac1 preupgrade]$ which sqlplus
/u01/app/oracle/product/12.2.0.1/bin/sqlplus
[oracle@rac1 preupgrade]$
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 16 00:32:19 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
!ls -ltr postupgrade_fixups.sql
-rw-r--r--. 1 oracle dba 8129 Sep 15 20:14 postupgrade_fixups.sql

SQL>
@postupgrade_fixups.sql
Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.

No errors.





Package created.

No errors.

Package body created.

No errors.

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-15 20:14:52

For Source Database:     DELL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
depend_usr_tables         Failed  Manual fixup recommended.
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None
fixed_objects             Passed  None
upg_by_std_upgrd          Passed  None

PL/SQL procedure successfully completed.


Session altered.

SQL> SQL>
12

Section 12

Download DBMS_DST_scriptsV1.9.zip from Oracle Support 1585343.1 @upg_tzv_check.sql 14 <---- @upg_tzv_apply.sql SELECT version FROM v$timezone_file;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
Download DBMS_DST_scriptsV1.9.zip from Oracle Support 1585343.1
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

[oracle@rac1 DBMS_DST_scriptsV1.9]$ pwd
/home/oracle/TZ/DBMS_DST_scriptsV1.9
[oracle@rac1 DBMS_DST_scriptsV1.9]$
ls -ltr
total 68
-rw-r--r--. 1 oracle dba 31010 Aug 22  2014
upg_tzv_check.sql
-rw-r--r--. 1 oracle dba 19502 Aug 22  2014
upg_tzv_apply.sql
-rw-r--r--. 1 oracle dba  6294 Jan  8  2015 countstarTSTZ.sql
-rw-r--r--. 1 oracle dba  7213 Mar 17 18:30 countstatsTSTZ.sql
[oracle@rac1 DBMS_DST_scriptsV1.9]$
[oracle@rac1 DBMS_DST_scriptsV1.9]$ which sqlplus
/u01/app/oracle/product/12.2.0.1/bin/sqlplus
[oracle@rac1 DBMS_DST_scriptsV1.9]$

[oracle@rac1 DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba

SQL*Plus: Release
12.2.0.1.0
Production on Sun Sep 16 00:54:44 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
@upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.2.0.1 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv26 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL>
SQL>
SELECT version FROM v$timezone_file;
VERSION
----------
14  <----
1 row selected.

SQL>
@upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv26 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1157628608 bytes
Database Buffers          486539264 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv26 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL>
SQL>
SELECT version FROM v$timezone_file;
VERSION
----------
26 <----
1 row selected.

SQL>
13

Section 13

26 <---- 20. Re-Run postupgrade_fixups.sql depend_usr_tables Failed Manual fixup recommended. RO User Tablespaces T = 0

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
SQL>
@/home/oracle/preupgrade/postupgrade_fixups.sql
Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-15 20:14:52

For Source Database:     DELL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
depend_usr_tables         Failed  Manual fixup recommended.
<-- Ignore this.
old_time_zones_exist Passed None post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered. SQL>
12.2 DB Post Upgrade:postupgrade_fixups.sql shows "depend_usr_tables Failed Manual fixup recommended" (Doc ID 2266026.1)
Solution:
1)
If Database Upgraded without using -T option,then FAILED status to be ignored.
or

2) If Database Upgraded with using -T option .

Then execute $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete.

And then ignore FAILED status .


[oracle@rac1 admin]$
more nohup.out
Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/whileupgrade
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
<--------
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
14

Section 14

21. Run utlu122s.sql 22. Run catuppst.sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
SQL>
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlu122s.sql
Oracle Database 12.2 Post-Upgrade Status Tool           09-16-2018 01:10:10

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
..
..
..
Oracle Server                          UPGRADED      12.2.0.1.0  00:19:59
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:10:28
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:01:36
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:49
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:23
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:16
Oracle Text                            UPGRADED      12.2.0.1.0  00:01:37
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:04:08
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:23
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:03:25
Spatial                                UPGRADED      12.2.0.1.0  00:09:48
Oracle Application Express             UPGRADED     5.0.4.00.12  00:21:35
Final Actions                                                    00:03:25
Post Upgrade                                                     00:02:26

Total Upgrade Time: 01:21:57

Database time zone version is 26. It meets current release needs.

Summary Report File = /home/oracle/whileupgrade/upg_summary.log

01:10:10 SQL>
01:10:10 SQL>

SQL>
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/catuppst.sql
Session altered.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART              2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART     FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART         NONE 2018-09-16 01:12:55



TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST       STARTED 2018-09-16 01:12:55


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN             2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN        NONE 2018-09-16 01:12:55



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN             2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN        NONE 2018-09-16 01:12:55



PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END             2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END        NONE 2018-09-16 01:12:55


catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.






PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END             2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END    FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END        NONE 2018-09-16 01:12:59



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST               2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST      FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST          NONE 2018-09-16 01:12:59



Session altered.

SQL>
15

Section 15

23. Run utlrp.sql Run in background ---- OR ---- 0 <----

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
Run in background
cd /u01/app/oracle/product/12.2.0.1/rdbms/admin
nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &
---- OR ----
SQL>
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2018-09-16 01:14:35

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2018-09-16 01:21:18

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

...Starting validation 01:21:36
...Database user "SYS", database schema "APEX_050000", user# "128" 01:21:36
...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 01:21:38
...272 packages
...265 package bodies
...465 tables
...8 functions
...16 procedures
...4 sequences
...497 triggers
...1582 indexes
...255 views
...0 libraries
...14 types
...5 type bodies
...0 operators
...0 index types
...Begin key object existence check 01:21:43
...Completed key object existence check 01:21:43
...Setting DBMS Registry 01:21:43
...Setting DBMS Registry Complete 01:21:43
...Exiting validate 01:21:43

PL/SQL procedure successfully completed.

SQL>

SQL>
select count(1) from dba_objects where status='INVALID';
COUNT(1)
----------
0 <----
SQL>
16

Section 16

24. Drop Restore point select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; PRE_UPGRADE YES 15-SEP-18 09.53.59.000000000 PM drop restore point PRE_UPGRADE;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
SQL> SQL> SQL> SQL>
NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        15-SEP-18 09.53.59.000000000 PM
SQL>

SQL>
drop restore point PRE_UPGRADE;
Restore point dropped.

SQL>
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
no rows selected  <-----
SQL>
17

Section 17

25. Set COMPATIBALE parameter value to 12.2.0 Warning : If the value of COMPATIBLE parameter is changed to 12.2.0.1 then if for some reasons database needs to be downgraded to 11.2.0.4 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded. If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
18

Section 18

ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE; 26. Restart the database startup;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL>
show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string
11.2.0.4.0
noncdb_compatible                    boolean     FALSE
SQL>

SQL>
ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;
System altered.

SQL>

SQL>
shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
startup;
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1090519744 bytes
Database Buffers          553648128 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>
19

Section 19

27. Start Listener from 12c home 28. Verify DBA_REGISTRY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[oracle@rac1 admin]$
lsnrctl start LISTENER
[oracle@rac1 admin]$
lsnrctl status LISTENER
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-SEP-2018 01:43:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-SEP-2018 01:42:15
Uptime                    0 days 0 hr. 1 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "
DELL
" has 1 instance(s).
  Instance "
DELL
", status READY, has 1 handler(s) for this service...
Service "DELLXDB" has 1 instance(s).
  Instance "
DELL
", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$

SQL>
select name,open_mode,version from v$database,v$instance;
NAME                 OPEN_MODE            VERSION
-------------------- -------------------- -----------------
DELL                 READ WRITE           12.2.0.1.0

SQL>

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
SQL> SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- -----------
CATALOG    Oracle Database Catalog Views
12.2.0.1.0      VALID
CATPROC    Oracle Database Packages and Types
12.2.0.1.0      VALID
JAVAVM     JServer JAVA Virtual Machine
12.2.0.1.0      VALID
XML        Oracle XDK
12.2.0.1.0      VALID
CATJAVA    Oracle Database Java Packages
12.2.0.1.0      VALID
APS        OLAP Analytic Workspace
12.2.0.1.0      VALID
OWM        Oracle Workspace Manager
12.2.0.1.0      VALID
CONTEXT    Oracle Text
12.2.0.1.0      VALID
XDB        Oracle XML Database
12.2.0.1.0      VALID
ORDIM      Oracle Multimedia
12.2.0.1.0      VALID
SDO        Spatial
12.2.0.1.0      VALID
XOQ        Oracle OLAP API
12.2.0.1.0      VALID
APEX       Oracle Application Express
5.0.4.00.12     VALID
13 rows selected.

SQL>
**** Copy the TNS entries from 11g TNS home to 12c TNS home
20

Section 20

select name,open_mode,version from v$database,v$instance; **** Copy the TNS entries from 11g TNS home to 12c TNS home 29. Edit oratab DELL:/u01/app/oracle/product/12.2.0.1:N

Code/Command (click line numbers to comment):

1
2
#DELL:/u01/app/oracle/product/11.2.0.4:N  <--
DELL:/u01/app/oracle/product/12.2.0.1:N
21

Section 21

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!