Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place
Table of Contents
___________________________________________________________________________________________________
PRE-UPGRADE TASKS
1. Disable DG Broker
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
4. Install 12.2 database software on primary
5. Apply latest Release Update on 12.2.0.1 on standby
6. Apply latest Release Update on 12.2.0.1 on primary
7. Run preupgrade script
8. View Preupgrade log
9. Backup Database
10. Adjust TABLESPACE SIZES as needed
11. Run preupgrade_fixups.sql
12. Remove the EM repository
13. Remove OLAP Catalog
14. Update Processes init parameter
15. Rerun Pre-upgrade script
16. Verify archive dest size
17. Verify GAP
18. Cancel MRP (On Standby)
19. DEFER log_archive_dest_2
20. Purge DBA_RECYCLEBIN
21. Verify INVALID objects
UPGRADE TASK
22. Run DBUA from 12.2 ORACLE HOME
POST-UPGRADE TASKS WHEN DBUA USING
23. Verify /etc/oratab
24. Verify Timezone version
25. Verify INVALID objects
26. Verify DBA_REGISTRY
27. Run postupgrade_fixups.sql
27.1 Start Standby Instance from 12c home – On Standby
28. Configure TNS entries
29. Verify TNS connectivity
30. Enable LOG_ARCHIVE_DEST_2
31. Enable MRP on Standby
32. Verify GAP
33. Verify DBA_REGISTRY
34. Update COMPATIBLE parameter
35. Configure DG Broker
___________________________________________________________________________________________________
On Primary
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
STANDBY
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
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
4. Install 12.2 database software on primary
5. Apply latest Release Update on 12.2.0.1 on standby
6. Apply latest Release Update on 12.2.0.1 on primary
[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 ~]$
[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 ~]$
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.
10. Adjust TABLESPACE SIZES as needed
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>
[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>
[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>
14. Update Processes init parameter
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>
[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>
Run on Primary
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>
Run on Standby
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>
18. Cancel MRP (Run on Standby )
[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>
19. DEFER log_archive_dest_2 (On Primary)
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>
SQL> select count(1) from dba_objects where status='INVALID';
COUNT(1)
----------
0
SQL>
UPGRADE TASK
22. Run DBUA from 12.2 ORACLE HOME On Primary
POST-UPGRADE TASKS WHEN DBUA USING
[oracle@rac1 admin]$ cat /etc/oratab | grep -i TEST
TEST:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac1 admin]$
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 26 <----- SQL>
SQL> select count(1) from dba_objects where status='INVALID'; COUNT(1) ---------- 0 <---- SQL>
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL> COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- ----------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID XML Oracle XDK 12.2.0.1.0 VALID CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID APS OLAP Analytic Workspace 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID CONTEXT Oracle Text 12.2.0.1.0 VALID XDB Oracle XML Database 12.2.0.1.0 VALID ORDIM Oracle Multimedia 12.2.0.1.0 VALID SDO Spatial 12.2.0.1.0 VALID XOQ Oracle OLAP API 12.2.0.1.0 VALID APEX Oracle Application Express 5.0.4.00.12 VALID 13 rows selected. SQL>
27. Run postupgrade_fixups.sql
SQL> @postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2018-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>
27.1 Start Standby Instance from 12c home – On Standby
[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>
On Primary
[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]$
On Standby
[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]$
On Primary
[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>
On Standby
[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>
30. Enable LOG_ARCHIVE_DEST_2 – On Primary
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>
31. Enable MRP on Standby – On Standby
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>
On Primary
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>
On Standby
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>
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
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>
On STANDBY
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>
35. Configure DG Broker (Primary)
[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>
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.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Very informative. detailed steps