DBA Hub

📋Steps in this guide1/21

Upgrade 12.2.0.1 to 19c Oracle Database Manually

========================

oracle configurationintermediate
by OracleDba
15 views
1

Overview

======================== UPGRADE TASK ___________________________________________________________________________________________________ - Hostname : prim.oracle.com - Database Name : PRIM - DB VERSION : 12.2.0.1 - PRIM : NON-PRIM, Single Instanc - DB Home Path : /u01/app/oracle/product/12.2.0/dbhome_1 - Datafile Location : /u01/app/oracle/oradata/PRIM - Target DB VERSION : 19c (19.4.0.0.0) - Target DB Path : /u01/app/oracle/product/19.0.0/dbhome_1 Upgrade Method : Manual Database Backup scripts – I have taken already
2

Section 2

Sample Backup Database Script

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
[oracle@rac1 PRIM]$
[oracle@rac1 PRIM]$ chmod 775 backup.sh
[oracle@rac1 PRIM]$
[oracle@rac1 PRIM]$ nohup ./backup.sh &

[oracle@rac1 ~]$ cd u01/app/oracle/product/12.2.0/dbhome_1/network/admin/

[oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/

[oracle@rac1 dbs]$ pwd 
/u01/app/oracle/product/12.2.0/dbhome_1/dbs 
[oracle@rac1 dbs]$ cp -p spfilePRIM.ora orapwPRIM /u01/app/backup/

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

  COUNT(*)
----------
         0 <---- SQL>

. oraenv (PRIM)

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
3

Section 3

================== PREUPGRADE SUMMARY ================== /home/oracle/PRIM/preupgrade/preupgrade.log /home/oracle/PRIM/preupgrade/preupgrade_fixups.sql /home/oracle/PRIM/preupgrade/postupgrade_fixups.sql PREUPGRADE SUMMARY Execute fixup scripts as indicated below: Log into the database and execute the preupgrade fixups @/home/oracle/PRIM/preupgrade/preupgrade_fixups.sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/PRIM/preupgrade

[oracle@rac1 ~]$ mkdir -p /home/oracle/PRIM/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? PRIM
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/PRIM/preupgrade
4

Section 4

Log into the database and execute the postupgrade fixups @/home/oracle/PRIM/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2020-01-28T20:45:05 [oracle@rac1 ~]$ Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2020-01-28T20:45:05 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: PRIM Container Name: PRIM Container ID: 0 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle OLAP API [to be upgraded] VALID
5

Section 5

============== BEFORE UPGRADE REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 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 12.2.0.1 Oracle Database SQL Tuning Guide. None of the fixed object tables have had stats collected.
6

Section 6

Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ———- ———- ———– SYSAUX 460 MB 500 MB SYSTEM 800 MB 912 MB TEMP 32 MB 150 MB UNDOTBS1 70 MB 439 MB Archiving cannot proceed if the archive log destination is full during upgrade.
7

Section 7

Archive Log Destination: Parameter : LOG_ARCHIVE_DEST_1 Destination : /u01/app/archive/PRIM The database has archiving enabled. The upgrade process will need free disk space in the archive log destination(s) to generate archived logs to. If you are using a version of the recovery catalog schema that is older than that required by the RMAN client version, then you must upgrade the catalog schema. It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database PRIM which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by executing the following

Code/Command (click line numbers to comment):

1
SQL>@/home/oracle/PRIM/preupgrade/preupgrade_fixups.sql
8

Section 8

============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS The database is using time zone file version 26 and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to “Upgrading the Time Zone File and Timestamp with Time Zone Data” in the 19 Oracle Database Globalization Support Guide.

Code/Command (click line numbers to comment):

1
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
9

Section 9

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 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. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.

Code/Command (click line numbers to comment):

1
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
10

Section 10

ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database PRIM which are identified above as AFTER UPGRADE “(AUTOFIXUP)” can be resolved by executing the following ** Tablespace Auto extend ON and max size also set, hence no action taken. http://learnomate.org/asm-2/#tspace TABLESPACE_NAME AUT FILE_NAME TOTAL_SPACE FREE_SPACE Free% MAX_SPACE —————– — —————————————— ———– ———- ———- ———- SYSAUX YES /u01/app/oracle/oradata/PRIM/sysaux01.dbf 460 23 5.08 31.9999847 SYSTEM YES /u01/app/oracle/oradata/PRIM/system01.dbf 800 4 .48 31.9999847 UNDOTBS1 YES /u01/app/oracle/oradata/PRIM/undotbs01.dbf 70 3 4.2 31.9999847 USERS YES /u01/app/oracle/oradata/PRIM/users01.dbf 5 4 80 31.9999847 ***************** ———– ———- ———- sum 1335 34 127.999939 SQL>

Code/Command (click line numbers to comment):

1
2
SQL>@/home/oracle/PRIM/preupgrade/postupgrade_fixups.sql
[oracle@rac1 ~]$
11

Section 11

In this test scenario, noting to update as per preupgrade.log. Hence no action taken.

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
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> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

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

PL/SQL procedure successfully completed.

SQL>

SQL> @/home/oracle/PRIM/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: 19.0.0.0.0 Build: 1
SQL> REM Generated on: 2020-01-28 20:45:02
SQL> REM
SQL> REM Source Database: PRIM
SQL> REM Source Database Version: 12.2.0.1.0
SQL> REM For Upgrade to Version: 19.0.0.0.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: 19.0.0.0.0 Build: 1
Generated on: 2020-01-28 20:45:02

For Source Database: PRIM
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. pre_fixed_objects YES None.
3. tablespaces_info NO Informational only.
Further action is optional.
4. min_archive_dest_size NO Informational only.
Further action is optional.
5. rman_recovery_version NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

SQL>
12

Section 12

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
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/PRIM
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>
 SQL> !df -h /u01/app/archive/PRIM
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   35G   33G  52% /u01 <---- SQL>

[oracle@rac1 ~]$ ps -ef | grep tns
root 15 2 0 20:01 ? 00:00:00 [netns]
oracle 3943 1 0 20:08 ? 00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_PRIM -inherit
oracle 16771 3093 0 21:03 pts/1 00:00:00 grep --color=auto tns

[oracle@rac1 ~]$ lsnrctl stop LISTENER_PRIM
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 21:03:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim.oracle.com)(PORT=1524)))
The command completed successfully
[oracle@rac1 ~]$
 [oracle@rac1 ~]$ ps -ef | grep tns 
root 15 2 0 20:01 ? 00:00:00 [netns]
oracle 16812 3093 0 21:03 pts/1 00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

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
--------- -------------------- ------------
PRIM       READ WRITE           ARCHIVELOG

SQL> show parameter compatible

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

SQL> show parameter recovery 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL>
13

Section 13

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
!mkdir -p /u01/app/oracle/fast_recovery_area 

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

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
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE YES 28-JAN-20 09.05.50.000000000 PM

SQL>

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
-------------------- --------------------
PRIM READ WRITE

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

15. Copy init and password files from 12c to 19c dbs home
[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *prim*
-rw-r-----. 1 oracle oinstall 24 Jan 28 20:08 lkprim
-rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwprim &lt;---
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileprim.ora &lt;---
-rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_prim.dat
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwprim spfileprim.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*prim*
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileprim.ora
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprim
[oracle@rac1 dbs]$

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *prim*
-rw-r-----. 1 oracle oinstall 24 Jan 28 20:08 lkprim
-rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwprim &lt;---
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileprim.ora &lt;---
-rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_prim.dat
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwprim spfileprim.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*prim*
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileprim.ora
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprim
[oracle@rac1 dbs]$
14

Section 14

[oracle@rac1 bin]$ [oracle@rac1 bin]$ nohup ./dbupgrade & <— Hit ENTER twice [1] 22584 [oracle@rac1 bin]$ nohup: ignoring input and appending output to ânohup.outâ

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
[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *prim*
-rw-r-----. 1 oracle oinstall 24 Jan 28 20:08 lkprim
-rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwprim &lt;---
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileprim.ora &lt;---
-rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_prim.dat
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwprim spfileprim.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*prim*
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileprim.ora
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprim
[oracle@rac1 dbs]$

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 
 [oracle@rac1 ~]$ export ORACLE_SID=PRIM 
[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH 
[oracle@rac1 ~]$ which sqlplus 
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 21:13:24 2020
Version 19.4.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 939524096 bytes
Database Buffers 603979776 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE PRIM VERSION STATUS
--------- -------------------- --- ----------------- ------------
PRIM READ WRITE NO 19.0.0.0.0 OPEN MIGRATE

[oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade
[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
[oracle@rac1 ~]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@rac1 bin]$ ls -ltr dbupgrade

-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade

[oracle@rac1 bin]$ jobs -l
[1]+ 22584 Running                nohup ./dbupgrade & 

[oracle@rac1 bin]$
[oracle@rac1 bin]$ disown
15

Section 15

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
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
[oracle@rac1 bin]$
 [oracle@rac1 bin]$ ps -ef | grep -i catctl.pl 
oracle   22589 22584  0 21:33 pts/1    00:00:03 /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl -I/u01/app/oracle/product/19.0.0/dbhome_1/perl/lib /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
oracle   24991  3093  0 21:40 pts/1    00:00:00 grep --color=auto -i catctl.pl
[oracle@rac1 bin]$

[oracle@rac1 upgrade20200128213345]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345
[oracle@rac1 upgrade20200128213345]$ ls -ltr *.log  
-rw-------. 1 oracle oinstall 1688451 Jan 28 21:37 catupgrd1.log
-rw-------. 1 oracle oinstall 1278535 Jan 28 21:37 catupgrd2.log
-rw-------. 1 oracle oinstall 1461585 Jan 28 21:37 catupgrd3.log
-rw-------. 1 oracle oinstall 7944713 Jan 28 21:38 catupgrd0.log
 [oracle@rac1 upgrade20200128213345]$

tail -f catupgrd0.log
tail -f catupgrd1.log
tail -f catupgrd2.log
tail -f catupgrd3.log

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@rac1 bin]$ more nohup.out

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
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 = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
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: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.4.0.0.0DBRU_LINUX.X64_190626]


/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20200128213334]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_catcon_22589.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 1
Database Name         = PRIM
DataBase Version      = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd_cat
con_22589.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd*.log] files for output generated by
scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345]

Parallel SQL Process Count            = 4
Components in [PRIM]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-107]         Start Time:[2020_01_28 21:33:56]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [PRIM] Files:1    Time: 32s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [PRIM] Files:5    Time: 56s
Restart  Phase #:2    [PRIM] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [PRIM] Files:19   Time: 32s
Restart  Phase #:4    [PRIM] Files:1    Time: 1s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [PRIM] Files:7    Time: 24s
*****************   Catproc Start   ****************
Serial   Phase #:6    [PRIM] Files:1    Time: 21s
*****************   Catproc Types   ****************
Serial   Phase #:7    [PRIM] Files:2    Time: 14s
Restart  Phase #:8    [PRIM] Files:1    Time: 1s
****************   Catproc Tables   ****************
Parallel Phase #:9    [PRIM] Files:67   Time: 47s
Restart  Phase #:10   [PRIM] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [PRIM] Files:1    Time: 94s
Restart  Phase #:12   [PRIM] Files:1    Time: 1s
**************   Catproc Procedures   **************
Parallel Phase #:13   [PRIM] Files:94   Time: 19s
Restart  Phase #:14   [PRIM] Files:1    Time: 1s
Parallel Phase #:15   [PRIM] Files:121  Time: 29s
Restart  Phase #:16   [PRIM] Files:1    Time: 1s
Serial   Phase #:17   [PRIM] Files:22   Time: 7s
Restart  Phase #:18   [PRIM] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [PRIM] Files:32   Time: 37s
Restart  Phase #:20   [PRIM] Files:1    Time: 1s
Serial   Phase #:21   [PRIM] Files:3    Time: 19s
Restart  Phase #:22   [PRIM] Files:1    Time: 1s
Parallel Phase #:23   [PRIM] Files:25   Time: 217s
Restart  Phase #:24   [PRIM] Files:1    Time: 2s
Parallel Phase #:25   [PRIM] Files:12   Time: 120s
Restart  Phase #:26   [PRIM] Files:1    Time: 1s
Serial   Phase #:27   [PRIM] Files:1    Time: 0s
Serial   Phase #:28   [PRIM] Files:3    Time: 6s
Serial   Phase #:29   [PRIM] Files:1    Time: 0s
Restart  Phase #:30   [PRIM] Files:1    Time: 0s
***************   Catproc PRIM Views   **************
Serial   Phase #:31   [PRIM] Files:1    Time: 3s
Restart  Phase #:32   [PRIM] Files:1    Time: 1s
Serial   Phase #:34   [PRIM] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [PRIM] Files:294  Time: 48s
Serial   Phase #:36   [PRIM] Files:1    Time: 0s
Restart  Phase #:37   [PRIM] Files:1    Time: 1s
Serial   Phase #:38   [PRIM] Files:6    Time: 8s
Restart  Phase #:39   [PRIM] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PRIM] Files:3    Time: 59s
Restart  Phase #:41   [PRIM] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PRIM] Files:13   Time: 131s
Restart  Phase #:43   [PRIM] Files:1    Time: 1s
Parallel Phase #:44   [PRIM] Files:11   Time: 20s
Restart  Phase #:45   [PRIM] Files:1    Time: 1s
Parallel Phase #:46   [PRIM] Files:3    Time: 4s
Restart  Phase #:47   [PRIM] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [PRIM] Files:1    Time: 10s
Restart  Phase #:49   [PRIM] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [PRIM] Files:1    Time: 5s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [PRIM] Files:1    Time: 3s
Restart  Phase #:52   [PRIM] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [PRIM] Files:2    Time: 382s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [PRIM] Files:1    Time: 2s
Serial   Phase #:56   [PRIM] Files:3    Time: 10s
Serial   Phase #:57   [PRIM] Files:3    Time: 7s
Parallel Phase #:58   [PRIM] Files:10   Time: 6s
Parallel Phase #:59   [PRIM] Files:25   Time: 10s
Serial   Phase #:60   [PRIM] Files:4    Time: 12s
Serial   Phase #:61   [PRIM] Files:1    Time: 0s
Serial   Phase #:62   [PRIM] Files:32   Time: 7s
Serial   Phase #:63   [PRIM] Files:1    Time: 0s
Parallel Phase #:64   [PRIM] Files:6    Time: 9s
Serial   Phase #:65   [PRIM] Files:2    Time: 22s
Serial   Phase #:66   [PRIM] Files:3    Time: 32s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [PRIM] Files:1    Time: 0s
Serial   Phase #:69   [PRIM] Files:1    Time: 5s
Parallel Phase #:70   [PRIM] Files:2    Time: 45s
Restart  Phase #:71   [PRIM] Files:1    Time: 1s
Parallel Phase #:72   [PRIM] Files:2    Time: 4s
Serial   Phase #:73   [PRIM] Files:2    Time: 5s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [PRIM] Files:1    Time: 1s
Serial   Phase #:76   [PRIM] Files:1    Time: 63s
Serial   Phase #:77   [PRIM] Files:2    Time: 6s
Restart  Phase #:78   [PRIM] Files:1    Time: 1s
Serial   Phase #:79   [PRIM] Files:1    Time: 55s
Restart  Phase #:80   [PRIM] Files:1    Time: 0s
Parallel Phase #:81   [PRIM] Files:3    Time: 115s
Restart  Phase #:82   [PRIM] Files:1    Time: 3s
Serial   Phase #:83   [PRIM] Files:1    Time: 13s
Restart  Phase #:84   [PRIM] Files:1    Time: 1s
Serial   Phase #:85   [PRIM] Files:1    Time: 13s
Restart  Phase #:86   [PRIM] Files:1    Time: 1s
Parallel Phase #:87   [PRIM] Files:4    Time: 155s
Restart  Phase #:88   [PRIM] Files:1    Time: 1s
Serial   Phase #:89   [PRIM] Files:1    Time: 4s
Restart  Phase #:90   [PRIM] Files:1    Time: 1s
Serial   Phase #:91   [PRIM] Files:2    Time: 12s
Restart  Phase #:92   [PRIM] Files:1    Time: 1s
Serial   Phase #:93   [PRIM] Files:1    Time: 2s
Restart  Phase #:94   [PRIM] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [PRIM] Files:1    Time: 17s
Restart  Phase #:96   [PRIM] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [PRIM] Files:1    Time: 5s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [PRIM] Files:1    Time: 520s
*******************   Migration   ******************
Serial   Phase #:99   [PRIM] Files:1    Time: 3s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [PRIM] Files:1    Time: 2s
Serial   Phase #:101  [PRIM] Files:1    Time: 0s
Serial   Phase #:102  [PRIM] Files:1    Time: 108s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [PRIM] Files:1    Time: 15s
****************   Summary report   ****************
Serial   Phase #:104  [PRIM] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [PRIM] Files:1    Time: 2s
Serial   Phase #:106  [PRIM] Files:1    Time: 0s
Serial   Phase #:107  [PRIM] Files:1     Time: 30s

------------------------------------------------------
Phases [0-107]         End Time:[2020_01_28 22:20:35]
------------------------------------------------------

Grand Total Time: 2800s

 LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:46m:40s]
[oracle@rac1 bin]$

[oracle@rac1 upgrade20200128213345]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345
[oracle@rac1 upgrade20200128213345]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    01-28-2020 22:20:0
Database Name: PRIM

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      19.4.0.0.0  00:18:01
JServer JAVA Virtual Machine           UPGRADED      19.4.0.0.0  00:02:29
Oracle XDK                             UPGRADED      19.4.0.0.0  00:01:12
Oracle Database Java Packages          UPGRADED      19.4.0.0.0  00:00:13
OLAP Analytic Workspace                UPGRADED      19.4.0.0.0  00:00:19
Oracle Label Security                  UPGRADED      19.4.0.0.0  00:00:11
Oracle Database Vault                  UPGRADED      19.4.0.0.0  00:00:32
Oracle Text                            UPGRADED      19.4.0.0.0  00:00:43
Oracle Workspace Manager               UPGRADED      19.4.0.0.0  00:00:37
Oracle Real Application Clusters       UPGRADED      19.4.0.0.0  00:00:01
Oracle XML Database                    UPGRADED      19.4.0.0.0  00:01:49
Oracle Multimedia                      UPGRADED      19.4.0.0.0  00:00:55
Spatial                                 LOADING      19.4.0.0.0  00:07:19
Oracle OLAP API                        UPGRADED      19.4.0.0.0  00:00:14
Datapatch                                                        00:08:34
Final Actions                                                    00:08:43
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:44:08

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:0h:46m:40s]
[oracle@rac1 upgrade20200128213345]$

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 ~]$ export ORACLE_SID=PRIM
[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH
[oracle@rac1 ~]$ which sqlplus
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 22:25:18 2020
Version 19.4.0.0.0

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 1174405120 bytes
Database Buffers 369098752 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME OPEN_MODE PRIM VERSION STATUS
--------- -------------------- --- ----------------- ------------
PRIM READ WRITE NO 19.0.0.0.0 OPEN <----- 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;

COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
CATPROC Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
XML Oracle XDK 19.0.0.0.0 UPGRADED
CATJAVA Oracle Database Java Packages 19.0.0.0.0 UPGRADED
APS OLAP Analytic Workspace 19.0.0.0.0 UPGRADED
RAC Oracle Real Application Clusters 19.0.0.0.0 UPGRADED
XDB Oracle XML Database 19.0.0.0.0 UPGRADED
OWM Oracle Workspace Manager 19.0.0.0.0 UPGRADED
CONTEXT Oracle Text 19.0.0.0.0 UPGRADED
ORDIM Oracle Multimedia 19.0.0.0.0 UPGRADED
SDO Spatial 19.0.0.0.0 LOADING
XOQ Oracle OLAP API 19.0.0.0.0 UPGRADED
OLS Oracle Label Security 19.0.0.0.0 UPGRADED
DV Oracle Database Vault 19.0.0.0.0 UPGRADED

15 rows selected.

SQL>

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
1413 <---- SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

COUNT(*)
----------
655 <---- SQL>

SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp.sql

Session altered.

TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-01-29 00:31:04

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 2020-01-29 00:36:03

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.

PL/SQL procedure successfully completed.

SQL>

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

COUNT(*)
----------
0

SQL>
16

Section 16

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
SQL>@/home/oracle/PRIM/preupgrade/postupgrade_fixups.sqlSession altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-01-28 20:45:05

For Source Database: PRIM
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
6. old_time_zones_exist NO Manual fixup recommended.
7. post_dictionary YES None.
8. post_fixed_objects NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

SQL>

$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

$ORACLE_HOME/rdbms/admin/utltz_countstar.sql

$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/
[oracle@rac1 admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql
-rw-r--r--. 1 oracle oinstall 8317 Feb 25 2017 utltz_countstats.sql
-rw-r--r--. 1 oracle oinstall 7423 Feb 25 2017 utltz_countstar.sql
-rw-r--r--. 1 oracle oinstall 33684 Sep 9 2017 utltz_upg_check.sql
-rw-r--r--. 1 oracle oinstall 21526 Sep 9 2017 utltz_upg_apply.sql

[oracle@rac1 admin]$

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
26 <----- SQL>

SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_check.sql

Session altered.

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 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
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 DSTv32 .
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 utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL>

SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
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 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 1191182336 bytes
Database Buffers 352321536 bytes
Redo Buffers 7876608 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 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 1191182336 bytes
Database Buffers 352321536 bytes
Redo Buffers 7876608 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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
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 DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.

SQL>
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
32 <---- 1 row selected. SQL>
17

Section 17

23. Run catuppst.sql /* In 12c: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/oracle-database-upgrade-utilities.html#GUID-408F22C3-2AD6-4DA4-8015-F5C6149508F0 You must run this script, either through DBUA or manually, if you perform a manual upgrade. DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades. Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database. Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time. */ *** Actually it will run as part of upgrade. We have reviewed catupgrd0.log and below is the output… found catuppst.sql ran and don’t see errors.

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
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 01-29-2020 01:41:3
Database Name: PRIM

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server VALID 19.4.0.0.0 00:18:01
JServer JAVA Virtual Machine VALID 19.4.0.0.0 00:02:29
Oracle XDK VALID 19.4.0.0.0 00:01:12
Oracle Database Java Packages VALID 19.4.0.0.0 00:00:13
OLAP Analytic Workspace VALID 19.4.0.0.0 00:00:19
Oracle Label Security VALID 19.4.0.0.0 00:00:11
Oracle Database Vault VALID 19.4.0.0.0 00:00:32
Oracle Text VALID 19.4.0.0.0 00:00:43
Oracle Workspace Manager VALID 19.4.0.0.0 00:00:37
Oracle Real Application Clusters OPTION OFF 19.4.0.0.0 00:00:01
Oracle XML Database VALID 19.4.0.0.0 00:01:49
Oracle Multimedia VALID 19.4.0.0.0 00:00:55
Spatial VALID 19.4.0.0.0 00:07:19
Oracle OLAP API VALID 19.4.0.0.0 00:00:14
Datapatch 00:08:34
Final Actions 00:08:43
Post Upgrade 00:00:12
Post Compile 00:04:58

Total Upgrade Time: 00:49:07

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

SQL>
18

Section 18

/* Rem catuppst.sql Rem Rem Copyright (c) 2006, 2018, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catuppst.sql – CATalog UPgrade PoST-upgrade actions Rem Rem DESCRIPTION Rem This post-upgrade script performs remaining upgrade actions that Rem do not require that the database be open in UPGRADE mode. Rem Automatically apply the latest PSU. Rem Rem NOTES Rem You must be connected AS SYSDBA to run this script. .. .. 22:19:59 SQL> — DBUA_TIMESTAMP: catuppst.sql finished 22:19:59 27. Set COMPATIBALE parameter value to 19.0.0 Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 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.

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
SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual; 

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2020-01-28 22:19:59
DBUA_TIMESTAMP CATUPPST FINISHED 2020-01-28 22:19:59
DBUA_TIMESTAMP CATUPPST NONE 2020-01-28 22:19:59
*/

*** If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. we need to run it manually if did have errors.
*** However can run one more time make sure no errors during execution.

SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2020-01-29 02:24:23
DBUA_TIMESTAMP DBRESTART FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP DBRESTART NONE 2020-01-29 02:24:23

TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2020-01-29 02:24:23

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2020-01-29 02:24:23
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP POSTUP_BGN NONE 2020-01-29 02:24:23

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_BGN NONE 2020-01-29 02:24:23

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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_END FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_END NONE 2020-01-29 02:24:23

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2020-01-29 02:24:24
DBUA_TIMESTAMP POSTUP_END FINISHED 2020-01-29 02:24:24
DBUA_TIMESTAMP POSTUP_END NONE 2020-01-29 02:24:24

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2020-01-29 02:24:24
DBUA_TIMESTAMP CATUPPST FINISHED 2020-01-29 02:24:24
DBUA_TIMESTAMP CATUPPST NONE 2020-01-29 02:24:24

SQL>


24. Re-Run postupgrade_fixups.sql

[php]
SQL> @/home/oracle/PRIM/preupgrade/postupgrade_fixups.sql
No errors.
No errors.No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-01-28 20:45:05

For Source Database: PRIM
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
6. old_time_zones_exist YES None.
7. post_dictionary YES None.
8. post_fixed_objects NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
SQL>

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

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;NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE YES 28-JAN-20 09.05.50.000000000 PM
SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/PRIM/flashback
total 1433680
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:39 o1_mf_h30dfg5q_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:52 o1_mf_h30dfkos_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:56 o1_mf_h30gdpcm_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:12 o1_mf_h30h4zjr_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30hdtcw_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30jhoxw_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 29 02:35 o1_mf_h30jc0ct_.flb

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>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/PRIM/flashback
total 0 <--- SQL>
19

Section 19

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.

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
SQL> show parameter COMPATIBLENAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL>
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

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

Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 1191182336 bytes
Database Buffers 352321536 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter COMPATIBLE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0 <---- noncdb_compatible boolean FALSE 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;COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID

15 rows selected.

SQL>

[oracle@rac1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.

LISTENER_PRIM =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1524))
)
)

SID_LIST_LISTENER_PRIM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prim.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = PRIM)
)
)
[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_PRIM =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1524))

PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prim.oracle.com)
)
)

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
20

Section 20

30. Password File – orapwPRIM *** REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE *** Password file orapwPRIM copied automatically during upgrade process. No action taken.

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
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[oracle@rac1 admin]$

[oracle@rac1 admin]$ lsnrctl start LISTENER_PRIM

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:28

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_prim/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim.oracle.com)(PORT=1524)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim.oracle.com)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias LISTENER_PRIM
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-JAN-2020 03:16:28
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_prim/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim.oracle.com)(PORT=1524)))
Services Summary...
Service "prim.oracle.com" has 1 instance(s).
Instance "PRIM", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping prim

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:50

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim.oracle.com)))
OK (0 msec)
[oracle@rac1 admin]$

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwPRIM
-rw-r-----. 1 oracle oinstall 3584 Jan 28 22:26 orapwPRIM <---- &#91;oracle@rac1 dbs&#93;$ SQL> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
SQL>

[oracle@rac1 ~]$ cat /etc/oratab | grep -i PRIM
#PRIM:/u01/app/oracle/product/12.2.0/dbhome_1:N
PRIM:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$
21

Section 21

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
cat rmanbackup_19c.sh

[oracle@rac1 AFTER_UPGRADE_19C]$ cat backup_db.log

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 29 03:41:08 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to target database: PRIM (DBID=3899729854)

RMAN> 2> 3> 4> 5> 6> 7> 8>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=79 device type=DISK

allocated channel: t2
channel t2: SID=82 device type=DISK

allocated channel: t3
channel t3: SID=80 device type=DISK

Starting backup at 29-JAN-20
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIM/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIM/sysaux01.dbf
channel t1: starting piece 1 at 29-JAN-20
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIM/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PRIM/users01.dbf
channel t2: starting piece 1 at 29-JAN-20
channel t2: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/database_PRIM_02un5nen_2 tag=TAG20200129T034111 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:15
channel t1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/database_PRIM_01un5nen_1 tag=TAG20200129T034111 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
Finished backup at 29-JAN-20

Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/PRIM/autobackup/2020_01_29/o1_mf_s_1030938097_h313ml67_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8>
allocated channel: a1
channel a1: SID=79 device type=DISK

allocated channel: a2
channel a2: SID=82 device type=DISK

allocated channel: a3
channel a3: SID=80 device type=DISK
Starting backup at 29-JAN-20
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 REPRIM=1 STAMP=1030914018
input archived log thread=1 sequence=2 REPRIM=2 STAMP=1030916097
input archived log thread=1 sequence=3 REPRIM=3 STAMP=1030916252
input archived log thread=1 sequence=4 REPRIM=4 STAMP=1030916367
input archived log thread=1 sequence=5 REPRIM=5 STAMP=1030916430
input archived log thread=1 sequence=6 REPRIM=6 STAMP=1030916704
input archived log thread=1 sequence=7 REPRIM=7 STAMP=1030916941
input archived log thread=1 sequence=8 REPRIM=8 STAMP=1030917153
input archived log thread=1 sequence=9 REPRIM=9 STAMP=1030917188
channel a1: starting piece 1 at 29-JAN-20
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 REPRIM=10 STAMP=1030917335
input archived log thread=1 sequence=11 REPRIM=11 STAMP=1030917430
input archived log thread=1 sequence=12 REPRIM=12 STAMP=1030917598
input archived log thread=1 sequence=13 REPRIM=13 STAMP=1030917716
input archived log thread=1 sequence=14 REPRIM=14 STAMP=1030917786
input archived log thread=1 sequence=15 REPRIM=15 STAMP=1030917852
input archived log thread=1 sequence=16 REPRIM=16 STAMP=1030917938
input archived log thread=1 sequence=17 REPRIM=17 STAMP=1030918034
input archived log thread=1 sequence=18 REPRIM=18 STAMP=1030918175
channel a2: starting piece 1 at 29-JAN-20
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 REPRIM=19 STAMP=1030918283
input archived log thread=1 sequence=20 REPRIM=20 STAMP=1030918326
input archived log thread=1 sequence=21 REPRIM=21 STAMP=1030918365
input archived log thread=1 sequence=22 REPRIM=22 STAMP=1030918454
input archived log thread=1 sequence=23 REPRIM=23 STAMP=1030918512
input archived log thread=1 sequence=24 REPRIM=24 STAMP=1030918594
input archived log thread=1 sequence=25 REPRIM=25 STAMP=1030919293
input archived log thread=1 sequence=26 REPRIM=26 STAMP=1030927180
input archived log thread=1 sequence=27 REPRIM=27 STAMP=1030935285
input archived log thread=1 sequence=28 REPRIM=28 STAMP=1030938102
channel a3: starting piece 1 at 29-JAN-20
channel a1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/arch_PRIM_04un5nfn_4 tag=TAG20200129T034143 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:16
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 REPRIM=29 STAMP=1030938103
channel a1: starting piece 1 at 29-JAN-20
channel a2: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/arch_PRIM_05un5nfn_5 tag=TAG20200129T034143 comment=NONE
channel a2: backup set complete, elapsed time: 00:02:18
channel a3: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/arch_PRIM_06un5nfo_6 tag=TAG20200129T034143 comment=NONE
channel a3: backup set complete, elapsed time: 00:02:17
channel a1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/arch_PRIM_07un5nk1_7 tag=TAG20200129T034143 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JAN-20

Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/PRIM/autobackup/2020_01_29/o1_mf_s_1030938246_h313rjrj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=79 device type=DISK

Starting backup at 29-JAN-20
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 29-JAN-20
channel c1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/PRIM/AFTER_UPGRADE_19C/Control_PRIM_09un5nkm_9 tag=TAG20200129T034422 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-20
[php]
Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/PRIM/autobackup/2020_01_29/o1_mf_s_1030938266_h313rv5m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: c1
RMAN>
Recovery Manager complete.
[oracle@rac1 AFTER_UPGRADE_19C]$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!