DBA Hub

📋Steps in this guide1/18

Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA

Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA

oracle upgradeintermediate
by OracleDba
13 views
1

Overview

Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA PRE-UPGRADE TASKS UPGRADE TASK POST-UPGRADE TASKS WHEN DBUA USING
2

Section 2

POST-UPGRADE TASKS WHEN DBUA USING 1. Environment PRE-UPGRADE TASKS

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Hostname       		:
RAC2.RAJASEKHAR.COM
Database Name  		:
COKE
DB VERSION		:
11.2.0.4.0
DB Home Path 		: /u01/app/oracle/product/11.2.0.4
Datafile Location 	: /u02/oradata/coke

Target DB VERSION 	:
12.2.0.1
Target DB Path		: /u01/app/oracle/product/12.2.0.1

Upgrade Method		:
Database Upgrade Assistant
3

Section 3

2. Run preupgrade script --- 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 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
. oraenv  (COKE)
/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@rac2 ~]$
mkdir -p /home/oracle/preupgrade
[oracle@rac2 ~]$

[oracle@rac2 coke]$ . oraenv
ORACLE_SID = [oracle] ? COKE
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 coke]$
/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@rac2 coke]$
4

Section 4

3. View Preupgrade log 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. 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. 4. Remove the EM repository

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
[oracle@rac2 coke]$
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:  COKE
     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
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             500 MB  ENABLED      1401 MB  None
     SYSTEM                             750 MB  ENABLED      1253 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            50 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.

  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/coke

     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
  ===================
   + 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@rac2 coke]$
5

Section 5

emctl stop dbconsole 5. Remove OLAP Catalog commit; <--- I have gave commit here

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
[oracle@rac2 coke]$
emctl stop dbconsole
SQL> select name,open_mode,version from v$database,v$instance;

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
COKE      READ WRITE           11.2.0.4.0

SQL>
SET ECHO ON;
SQL>
SET SERVEROUTPUT ON;
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
..
..
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files

PL/SQL procedure successfully completed.

SQL>

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
..
..
..
 32  end;
 33  /

PL/SQL procedure successfully completed.

SQL>
SQL> rem
SQL> rem drop OLAP_DBA role
SQL> rem
SQL> drop role OLAP_DBA
  2  /

Role dropped.

SQL>
SQL> execute sys.dbms_registry.removed('AMD');

PL/SQL procedure successfully completed.

SQL>
SQL> -- No longer show up in dba_registry
SQL>
delete from registry$ where cid='AMD' and status='99';
1 row deleted.

SQL>
SQL>
SQL>
SQL>
commit;
<--- I have gave commit here
Commit complete.

SQL>
6

Section 6

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
14
15
16
17
18
19
20
21
22
23
SQL>
show parameter PROCESSES
NAME        TYPE        VALUE  
----------- ----------- -------
processes   integer
150
SQL>

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

SQL>

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL>
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.

SQL>
7

Section 7

8. Purge Recyclebin PURGE DBA_RECYCLEBIN; 9. Refresh MVs

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL>
PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.

SQL>
8

Section 8

10. Run preupgrade_fixups.sql @/home/oracle/preupgrade/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
47
48
49
50
51
52
53
54
55
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>

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL>
@/home/oracle/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle PRE-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 12.2.0.1.0 Build: 1
SQL> REM    Generated on:            2018-09-17 06:27:19
SQL> REM
SQL> REM    Source Database:         COKE
SQL> REM    Source Database Version: 11.2.0.4.0
SQL> REM    For Upgrade to Version:     12.2.0.1.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-17 06:27:19

For Source Database:     COKE
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
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.
PL/SQL procedure successfully completed.

SQL>
9

Section 9

11. Verify archive log dest size *** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations 36G 12. 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
*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination
/u02/arch/coke
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL>
SQL>
!df -h /u02/arch/coke
Filesystem            Size  Used
Avail
Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G  5.4G
36G
14% /u02

SQL>
10

Section 10

*** NO need to enable Flashback Database from 11.2.0.1 onwards *** Database MUST be in Archive Log mode *** MUST NOT change the compatible parameter to higher version select * from V$restore_point; create restore point pre_upgrade guarantee flashback database; UPGRADE TASK

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
*** NO need to enable Flashback Database from 11.2.0.1 onwards
*** 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>
select name,open_mode,log_mode from v$database;
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
COKE      READ WRITE
ARCHIVELOG  <----
SQL>

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

SQL>
!mkdir -p /u02/fast_recovery_area/coke
SQL>
alter system set db_recovery_file_dest='/u02/fast_recovery_area/coke';
System altered.

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

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

SQL>
select * from V$restore_point;
no rows selected  <-----
SQL>
create restore point pre_upgrade guarantee flashback database;
Restore point created.

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        17-SEP-18 06.54.55.000000000 AM
SQL>
11

Section 11

13. Run DBUA /u01/app/oracle/product/12.2.0.1/bin/dbua Database upgrade has been completed successfully , and the database is ready to use. POST UPGRADE TASKS WHEN DBUA USING
Step 11

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@rac2 ~]$
/u01/app/oracle/product/12.2.0.1/bin/dbua
Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM

Preupgrade generated files:
    
/u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/upgrade.xml
    /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/postupgrade_fixups.sql
Database upgrade has been completed successfully
, and the database is ready to use.
[oracle@rac2 ~]$
12

Section 12

POST UPGRADE TASKS WHEN DBUA USING 14. Verify /etc/oratab After the upgrade, the home for database location was changed to new 12c location by DBUA 15. Verify Timezone version

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
After the upgrade, the home for database location was changed to new 12c location by DBUA
[oracle@rac2 ~]$
cat /etc/oratab | grep -i COKE
COKE:/u01/app/oracle/product/
12.2.0.1
:N
[oracle@rac2 ~]$
13

Section 13

15. Verify Timezone version 16. Verify INVALID objects 17. Verify DBA_REGISTRY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
SELECT version FROM v$timezone_file;
VERSION
----------
26 <----
SQL>

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

Section 14

17. Verify DBA_REGISTRY 18. Run postupgrade_fixups.sql @/u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/postupgrade_fixups.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
SQL>
select name,open_mode,version from v$database,v$instance;
NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
COKE      READ WRITE
12.2.0.1.0
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>

SQL>
@/u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/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-17 07:05:49

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

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
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>
15

Section 15

old_time_zones_exist Passed None post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None 19. Drop Restore point drop restore point PRE_UPGRADE; total 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
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        17-SEP-18 06.54.55.000000000 AM
SQL>

[oracle@rac2 flashback]$ pwd
/u02/fast_recovery_area/coke/COKE/flashback
[oracle@rac2 flashback]$
ls -ltrh
total 1.1G
-rw-r-----. 1 oracle dba 51M Sep 17 07:23 o1_mf_fsydwz9o_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:23 o1_mf_fsydx1oy_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:24 o1_mf_fsygm7lv_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:25 o1_mf_fsygmh0v_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:28 o1_mf_fsygntd0_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:29 o1_mf_fsygpxqp_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:30 o1_mf_fsygvl24_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:34 o1_mf_fsygxl9c_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:36 o1_mf_fsygzbd1_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:39 o1_mf_fsyh83jm_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 07:42 o1_mf_fsyhc7s6_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 07:43 o1_mf_fsyhkh13_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 07:46 o1_mf_fsyhpod8_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 07:51 o1_mf_fsyhqv23_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 08:00 o1_mf_fsyhwx5y_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 08:38 o1_mf_fsyj75lf_.flb
-rw-r-----. 1 oracle dba 51M Sep 17 08:38 o1_mf_fsylyty1_.flb
-rw-r-----. 1 oracle dba 81M Sep 17 10:19 o1_mf_fsyjqv0p_.flb
[oracle@rac2 flashback]$

SQL>
drop restore point PRE_UPGRADE;
Restore point dropped.

SQL>

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

[oracle@rac2 flashback]$ pwd
/u02/fast_recovery_area/coke/COKE/flashback
[oracle@rac2 flashback]$
ls -lrth
total 0   <------------
[oracle@rac2 flashback]$
16

Section 16

20. Update COMPATIBLE parameter 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.
17

Section 17

11.2.0.4.0 <--- 12.2.0 <---- select name,open_mode,version from v$database,v$instance; READ WRITE **** Copy the TNS entries from 11g TNS home to 12c TNS home

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
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            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>

SQL>
show parameter COMPATIBLE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string
12.2.0  <----
noncdb_compatible                    boolean     FALSE
SQL>

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

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

Section 18

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!