DBA Hub

📋Steps in this guide1/31

Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place

Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place

oracle upgradeintermediate
by OracleDba
17 views
1

Overview

Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place PRE-UPGRADE TASKS UPGRADE TASK POST-UPGRADE TASKS WHEN DBUA USING
2

Section 2

POST-UPGRADE TASKS WHEN DBUA USING 0. Environment On Primary

Code/Command (click line numbers to comment):

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

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

STANDBY 1. Disable DG Broker

Code/Command (click line numbers to comment):

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

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

Upgrade Method		: Database Upgrade Assistant

http://www.br8dba.com/disable-dg-broker/
4

Section 4

2. Apply latest PSU on 11.2.0.4 on both primary and standby (click below for example) 3. Install 12.2 database software on standby

Code/Command (click line numbers to comment):

1
2
3
http://www.br8dba.com/how-to-apply-database-psu-patch-where-standby-in-place/

http://www.br8dba.com/install-12-2-database-binaries-in-silent-mode/
5

Section 5

4. Install 12.2 database software on primary 5. Apply latest Release Update on 12.2.0.1 on standby

Code/Command (click line numbers to comment):

1
2
3
http://www.br8dba.com/install-12-2-database-binaries-in-silent-mode/

http://www.br8dba.com/apply-ru-on-database-12-2/
6

Section 6

6. Apply latest Release Update on 12.2.0.1 on primary 7. Run preupgrade script

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
http://www.br8dba.com/apply-ru-on-database-12-2/

[oracle@rac1 preupgrade]$
. oraenv
ORACLE_SID = [ARC] ? TEST
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
/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/PRE_UPGRADE
Preupgrade generated files:
    /home/oracle/PRE_UPGRADE/preupgrade.log
    /home/oracle/PRE_UPGRADE/preupgrade_fixups.sql
    /home/oracle/PRE_UPGRADE/postupgrade_fixups.sql
[oracle@rac1 ~]$
7

Section 7

/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/PRE_UPGRADE 8. View Preupgrade log 9. Backup 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
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
[oracle@rac1 ~]$
cat /home/oracle/PRE_UPGRADE/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:  TEST
     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                             520 MB  ENABLED      1425 MB  None
     SYSTEM                             750 MB  ENABLED      1256 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
     ---------                         ------------------
memory_target*                            1535115264
     processes                                        300
* These minimum memory/pool sizes are recommended for the upgrade process




  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/oracle/archive

     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@rac1 ~]$
8

Section 8

10. Adjust TABLESPACE SIZES as needed 11. Run preupgrade_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
Make sure you should have valid Backup. Better to take COLD backup on both primary and standby if you complete downtime. Hence no need to rebuild entire standby in case of rollback.

SQL>
select file_name from dba_data_files where tablespace_name='SYSAUX';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/sysaux01.dbf

SQL>
alter database datafile '/u02/oracle/oradata/TEST/sysaux01.dbf' autoextend on maxsize 30g;
Database altered.

SQL>

SQL>
select file_name from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/system01.dbf

SQL>
alter database datafile '/u02/oracle/oradata/TEST/system01.dbf' autoextend on maxsize 30g;
Database altered.

SQL>

SQL>
select file_name from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/undotbs01.dbf

SQL>
alter database datafile '/u02/oracle/oradata/TEST/undotbs01.dbf' autoextend on maxsize 30g;
Database altered.

SQL>

SQL>
select file_name from dba_temp_files where tablespace_name='TEMP';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf

SQL>
alter database tempfile '/u02/oracle/oradata/TEST/temp01.dbf' autoextend on maxsize 30g;
Database altered.

SQL>
9

Section 9

12. Remove the EM repository 13. Remove OLAP Catalog

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
[oracle@rac1 PRE_UPGRADE]$ pwd
/home/oracle/PRE_UPGRADE
[oracle@rac1 PRE_UPGRADE]$

SQL>
@preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-11-13 09:10:06

For Source Database:     TEST
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                Failed  Manual fixup recommended.
amd_exists                Failed  Manual fixup recommended.
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Failed  Manual fixup recommended.
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.
PL/SQL procedure successfully completed.

SQL>

[oracle@rac1 admin]$ cp
/u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0.4/rdbms/admin
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/rdbms/admin
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 09:42:20 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>
@emremove.sql
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>
10

Section 10

14. Update Processes init parameter 15. Rerun Pre-upgrade 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
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
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/olap/admin
[oracle@rac1 admin]$
ls -ltr catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13  2013 catnoamd.sql
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 09:48:15 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>
@catnoamd.sql
Synonym dropped.
..
..
..
Type dropped.


PL/SQL procedure successfully completed.


Role dropped.


PL/SQL procedure successfully completed.


1 row deleted.

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

SQL>
show parameter processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150
SQL>

SQL>
alter system set processes=300 scope=both;
alter system set processes=300 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
alter system set processes=300 scope=spfile;
System altered.

SQL>
11

Section 11

16. Verify archive dest size 15% 17. Verify GAP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
[oracle@rac1 PRE_UPGRADE]$ pwd
/home/oracle/PRE_UPGRADE
[oracle@rac1 PRE_UPGRADE]$
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 16:48:32 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>
@preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-11-13 09:10:06

For Source Database:     TEST
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>

SQL>
archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive
Oldest online log sequence     107
Next log sequence to archive   109
Current log sequence           109
SQL>

SQL>
!df -h /u02/oracle/archive
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G  6.1G   35G
15%
/u02

SQL>
12

Section 12

17. Verify GAP Run on Primary Run on Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                    108                   108          0
         1                    108                   108          0
SQL>
13

Section 13

18. Cancel MRP (Run on Standby ) alter database recover managed standby database cancel; 19. DEFER log_archive_dest_2 (On Primary)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                    108                   108          0
SQL>

[oracle@rac2 ~]$
ps -ef | grep mrp
oracle    3909     1  0 16:05 ?        00:00:00
ora_mrp0_TEST_DG
oracle    6692  3750  0 17:04 pts/0    00:00:00 grep mrp
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL>
alter database recover managed standby database cancel;
Database altered.

SQL>
14

Section 14

select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; alter system set log_archive_dest_2='' scope=both; 20. Purge DBA_RECYCLEBIN

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
SQL>
select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3;
DEST_ID    DEST_NAME             STATUS    ERROR 
---------- -------------------- --------- ---------- 
1          LOG_ARCHIVE_DEST_1    VALID 
2          LOG_ARCHIVE_DEST_2    VALID 
SQL>
SQL>
ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=BOTH;
System altered.

SQL>
SQL>
select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3;
DEST_ID     DEST_NAME           STATUS     ERROR 
---------- -------------------- --------- ---------- 
1           LOG_ARCHIVE_DEST_1   VALID 
2           LOG_ARCHIVE_DEST_2   DEFERRED 

SQL>

SQL>
show parameter log_archive_dest_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2
string      service="test_dg", LGWR ASYNC
                                                 NOAFFIRM delay=0 optional comp
                                                 ression=disable max_failure=0
                                                 max_connections=1 reopen=300 d
                                                 b_unique_name="TEST_DG" net_ti
                                                 meout=30, valid_for=(all_logfi
                                                 les,primary_role)
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>
SQL>
alter system set log_archive_dest_2='' scope=both;
<---- removed entry to avoid errors while running dbua, why because log_archive_dest_2 having many attributes System altered.
SQL>

SQL>
purge dba_recyclebin;
DBA Recyclebin purged.

SQL>
15

Section 15

21. Verify INVALID objects UPGRADE TASK 22. Run DBUA from 12.2 ORACLE HOME On Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL>
select count(1) from dba_objects where status='INVALID';
COUNT(1)
----------
         0

SQL>
16

Section 16

22. Run DBUA from 12.2 ORACLE HOME On Primary POST-UPGRADE TASKS WHEN DBUA USING 23. Verify /etc/oratab

Code/Command (click line numbers to comment):

1
2
3
4
[oracle@rac1 admin]$
cat /etc/oratab | grep -i TEST
TEST:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac1 admin]$
17

Section 17

24. Verify Timezone version 25. Verify INVALID objects 26. Verify DBA_REGISTRY

Code/Command (click line numbers to comment):

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

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

Section 18

26. Verify DBA_REGISTRY 27. Run postupgrade_fixups.sql 27.1 Start Standby Instance from 12c home – On Standby

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
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>
@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-11-13 09:10:06

For Source Database:     TEST
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>
19

Section 19

27.1 Start Standby Instance from 12c home – On Standby /u01/app/oracle/product/ 12.2.0.1 /bin/sqlplus startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbs/initTEST_DG.ora'; create spfile from pfile='/u01/app/oracle/product/12.2.0.1/dbs/initTEST_DG.ora'; shut immediate;

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
[oracle@rac2 ~]$
which sqlplus
/u01/app/oracle/product/
12.2.0.1
/bin/sqlplus
[oracle@rac2 ~]$


SQL>
startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbs/initTEST_DG.ora';
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  8620992 bytes
Variable Size             939525184 bytes
Database Buffers          570425344 bytes
Redo Buffers                8155136 bytes
SQL> 

SQL>
create spfile from pfile='/u01/app/oracle/product/12.2.0.1/dbs/initTEST_DG.ora';
File created.

SQL>
shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

SQL>
startup nomount;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  8620992 bytes
Variable Size             939525184 bytes
Database Buffers          570425344 bytes
Redo Buffers                8155136 bytes
SQL>
SQL>
alter database mount standby database;
Database altered.

SQL>
20

Section 20

alter database mount standby database; 28. Configure TNS entries On Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
[oracle@rac1 ~]$
cd /u01/app/oracle/product/12.2.0.1/network/admin/
[oracle@rac1 admin]$
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac1 admin]$

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

LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

TEST_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST_DG)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )

TEST_DG_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG_DGMGRL)
    )
  )

[oracle@rac1 admin]$
21

Section 21

On Standby 29. Verify TNS connectivity On Primary

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
[oracle@rac2 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/network/admin
[oracle@rac2 admin]$
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST_DG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DG_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST_DG)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac2 admin]$

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

LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

TEST_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )

TEST_DG_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG_DGMGRL)
    )
  )

[oracle@rac2 admin]$
22

Section 22

On Primary sqlplus sys/SYS@TEST AS SYSDBA sqlplus sys/SYS@TEST_DG AS SYSDBA On Standby

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
[oracle@rac1 ~]$
sqlplus sys/SYS@TEST AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:46:13 2018

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


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

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$
sqlplus sys/SYS@TEST_DG AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:46:47 2018

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


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

SQL>
23

Section 23

sqlplus sys/SYS@TEST AS SYSDBA sqlplus sys/SYS@TEST_DG AS SYSDBA 30. Enable LOG_ARCHIVE_DEST_2 – On Primary

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
[oracle@rac2 ~]$
sqlplus sys/SYS@TEST AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:47:17 2018

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


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

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac2 ~]$
[oracle@rac2 ~]$
sqlplus sys/SYS@TEST_DG AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:47:26 2018

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


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

SQL>

SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TEST_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_DG' scope=both;
System altered.

SQL>

SQL>
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;
System altered.

SQL>

SQL> SET LINES 180
SQL> COL DEST_NAME FOR A30
SQL> COL ERROR FOR A10
SQL>
select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3;
DEST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- ---------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 VALID SQL>
24

Section 24

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TEST_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_DG' scope=both; select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 31. Enable MRP on Standby – On Standby 32. Verify GAP

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
SQL>
alter database open;
Database altered.

SQL>
alter database recover managed standby database disconnect from session;
Database altered.

SQL>
select name,db_unique_name,open_mode,database_role from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST      TEST_DG
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL>

SQL>
select process,status,sequence#,thread# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
DGRD      ALLOCATED             0          0
ARCH      CLOSING             156          1
ARCH      CLOSING             157          1
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
DGRD      ALLOCATED             0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                158          1
MRP0      APPLYING_LOG        158          1
35 rows selected.

SQL>
25

Section 25

32. Verify GAP On Primary On Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                    157                   157          0
         1                    157                   157          0
SQL>
26

Section 26

33. Verify DBA_REGISTRY select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; 34. Update COMPATIBLE parameter

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
SQL>
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                    157                   157          0
SQL>

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

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

13 rows selected.

SQL>
27

Section 27

34. 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. On Primary

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
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
-------------------- -------------------- -----------------
TEST                 READ WRITE
12.2.0.1.0 <----
SQL>
28

Section 28

On STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY 35. Configure DG Broker (Primary)

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
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;
SQL> 
SQL>
startup;
SQL>
show parameter COMPATIBLE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string
12.2.0 <-----
noncdb_compatible                    boolean     FALSE
SQL>

SQL>
select name,db_unique_name,open_mode,database_role from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST      TEST_DG
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL>
29

Section 29

create configuration 'TEST' as primary database is 'TEST' connect identifier is TEST; add database 'TEST_DG' as connect identifier is TEST_DG maintained as physical; enable configuration; SUCCESS (status updated 45 seconds ago) 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.

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
[oracle@rac1 ~]$
dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Dec 3 05:09:08 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>
connect sys/SYS@TEST
Connected to "TEST"
Connected as SYSDBA.
DGMGRL>
show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>
create configuration 'TEST' as primary database is 'TEST' connect identifier is TEST;
Configuration "TEST" created with primary database "TEST"
DGMGRL>
show configuration;
Configuration - TEST

  Protection Mode:
MaxPerformance
Members:
TEST - Primary database
Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>
add database 'TEST_DG' as connect identifier is TEST_DG maintained as physical;
Database "TEST_DG" added
DGMGRL> show configuration;

Configuration - TEST

  Protection Mode: MaxPerformance
  Members:
TEST    - Primary database
TEST_DG - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
DGMGRL>
enable configuration;
Enabled.
DGMGRL>

DGMGRL>
show configuration;
Configuration - TEST

  Protection Mode: MaxPerformance
  Members:
  TEST    - Primary database
    TEST_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 45 seconds ago)
DGMGRL>
30

Section 30

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. Reference: https://mikedietrichde.com/2017/09/12/upgrade-disable-data-guard-broker/ Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1) Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1) Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1) https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/upgrading-downgrading-in-data-guard-broker-configuration.html#GUID-9B424C49-A005-4106-9EF8-CB7479F9ACB8 http://docs.oracle.com/database/122/SBYDB/upgrading-patching-downgrading-oracle-data-guard-configuration.htm#SBYDB-GUID-A5226768-DB6B-4714-BB9A-0A3EF17A01C8 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/upgrading-downgrading-in-data-guard-broker-configuration.html#GUID-9B424C49-A005-4106-9EF8-CB7479F9ACB8 http://docs.oracle.com/database/122/SBYDB/upgrading-patching-downgrading-oracle-data-guard-configuration.htm#GUID-6431EFBF-6B00-4E7D-A106-63574225BDD9 Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2) Master Note for Database Proactive Patch Program (Doc ID 756671.1) 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.
31

Section 31

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!