Tag Archives: upgrade database 11g to 12c

Upgrade DB using DBUA from 11.2.0.4 to 12.2.0.1

Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA

Table of Contents
___________________________________________________________________________________________________

1. Environment

PRE-UPGRADE TASKS

2. Run preupgrade script
3. View Preupgrade log
4. Remove the EM repository
5. Remove OLAP Catalog
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Create Flashback Guaranteed Restore Point

UPGRADE TASK

13. Run DBUA

POST-UPGRADE TASKS WHEN DBUA USING

14. Verify /etc/oratab
15. Verify Timezone version
16. Verify INVALID objects
17. Verify DBA_REGISTRY
18. Run postupgrade_fixups.sql
19. Drop Restore point
20. Update COMPATIBLE parameter
___________________________________________________________________________________________________


1. Environment

Hostname       		: RAC2.RAJASEKHAR.COM
Database Name  		: COKE
DB VERSION		: 11.2.0.4.0
DB Home Path 		: /u01/app/oracle/product/11.2.0.4
Datafile Location 	: /u02/oradata/coke

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

Upgrade Method		: Database Upgrade Assistant

PRE-UPGRADE TASKS


2. Run preupgrade script

. oraenv  (COKE)

/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar TERMINAL TEXT

--- OR ---

/u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade


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

[oracle@rac2 coke]$ . oraenv
ORACLE_SID = [oracle] ? COKE
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 coke]$ /u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
Preupgrade generated files:
    /home/oracle/preupgrade/preupgrade.log
    /home/oracle/preupgrade/preupgrade_fixups.sql
    /home/oracle/preupgrade/postupgrade_fixups.sql
[oracle@rac2 coke]$


3. View Preupgrade log

[oracle@rac2 coke]$ cat /home/oracle/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

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

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

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Expression Filter                      [to be upgraded]  VALID
  Rule Manager                           [to be upgraded]  VALID
  Oracle Application Express             [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

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

     SYSAUX                             500 MB  ENABLED      1401 MB  None
     SYSTEM                             750 MB  ENABLED      1253 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            50 MB  ENABLED       400 MB  None

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




   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
     processes                                        300




  RECOMMENDED ACTIONS
  ===================
   + Remove the EM repository.

     - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

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

       $> emctl stop dbconsole

     Step 2: Connect to the database using the SYS account AS SYSDBA

       SET ECHO ON;
       SET SERVEROUTPUT ON;
      @emremove.sql

     Without the set echo and serveroutput commands, you will not be able to
     follow the progress of the script.

     The database has an Enterprise Manager Database Control repository.

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

   + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
     $ORACLE_HOME/olap/admin/catnoamd.sql script.

     The OLAP Catalog component, AMD, exists in the database.

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

   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

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

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

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

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

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

  INFORMATION ONLY
  ================
   + Consider upgrading APEX manually, before the database upgrade.

     The database contains APEX version 3.2.1.00.12 and will need to be
     upgraded to at least version 5.0.4.00.12.

     To reduce database upgrade time, you can upgrade APEX manually before
     the database upgrade.  Refer to My Oracle Support Note 1088970.1 for
     information on APEX installation upgrades.

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

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

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

     The database has archiving enabled and LOG_ARCHIVE_DEST_ set.  The
     upgrade process will need free disk space in the archive log
     destination(s) to generate archived logs to.

=============
AFTER UPGRADE
=============

  Run /postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

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

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 14 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

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

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

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

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

       EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

     This recommendation is given for all preupgrade runs.

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

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

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

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

[oracle@rac2 coke]$


4. Remove the EM repository

[oracle@rac2 coke]$ emctl stop dbconsole

SQL> select name,open_mode,version from v$database,v$instance;

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

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

SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql
..
..
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files

PL/SQL procedure successfully completed.

SQL>


5. Remove OLAP Catalog

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> !ls -lrt /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13  2013 /u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql

SQL> @/u01/app/oracle/product/11.2.0.4/olap/admin/catnoamd.sql
..
..
..
 32  end;
 33  /

PL/SQL procedure successfully completed.

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

Role dropped.

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

PL/SQL procedure successfully completed.

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

1 row deleted.

SQL>
SQL>
SQL>
SQL> commit;  <--- I have gave commit here

Commit complete.

SQL>


6. Update INITIALIZATION PARAMETERS

SQL> show parameter PROCESSES

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

SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

System altered.

SQL>


7. Gather DICTIONARY STATS

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


8. Purge Recyclebin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


9. Refresh MVs

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

PL/SQL procedure successfully completed.

SQL>


10. Run preupgrade_fixups.sql

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

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

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

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
em_present                Passed  None
amd_exists                Passed  None
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Passed  None
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

SQL>


11. Verify archive log dest size

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch/coke
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL>
SQL> !df -h /u02/arch/coke
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G  5.4G   36G  14% /u02

SQL>


12. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onwards
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO  <----

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
COKE      READ WRITE           ARCHIVELOG  <----

SQL>

SQL> show parameter compatible

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

SQL> !mkdir -p /u02/fast_recovery_area/coke

SQL> alter system set db_recovery_file_dest='/u02/fast_recovery_area/coke';

System altered.

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> show parameter recovery

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

SQL> select * from V$restore_point;

no rows selected  <-----

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
SQL> SQL> SQL> SQL>
NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        17-SEP-18 06.54.55.000000000 AM

SQL>

UPGRADE TASK


13. Run DBUA

[oracle@rac2 ~]$ /u01/app/oracle/product/12.2.0.1/bin/dbua

Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM

Preupgrade generated files:
    
/u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/upgrade.xml
    /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/postupgrade_fixups.sql











Database upgrade has been completed successfully, and the database is ready to use.

[oracle@rac2 ~]$ 

POST UPGRADE TASKS WHEN DBUA USING


14. Verify /etc/oratab

After the upgrade, the home for database location was changed to new 12c location by DBUA

[oracle@rac2 ~]$ cat /etc/oratab | grep -i COKE
COKE:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac2 ~]$


15. Verify Timezone version

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26 <----

SQL>


16. Verify INVALID objects

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

  COUNT(1)
----------
         0 <-----

SQL> 


17. Verify DBA_REGISTRY

SQL> select name,open_mode,version from v$database,v$instance;

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
COKE      READ WRITE           12.2.0.1.0

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

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

13 rows selected.

SQL>


18. Run postupgrade_fixups.sql

SQL> @/u01/app/oracle/cfgtoollogs/dbua/upgrade2018-09-17_07-04-11-AM/COKE/postupgrade_fixups.sql

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.

No errors.


Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-17 07:05:49

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

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Passed  None
post_dictionary           Passed  None
fixed_objects             Passed  None
upg_by_std_upgrd          Passed  None

PL/SQL procedure successfully completed.


Session altered.

SQL>


19. Drop Restore point

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;SQL> SQL> SQL> SQL>

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        17-SEP-18 06.54.55.000000000 AM

SQL>

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

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

SQL>

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected  <----

SQL>

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


20. Update COMPATIBLE parameter

Warning: If the value of COMPATIBLE parameter is changed to 12.2.0.1 then if for some reasons database needs to be downgraded to 11.2.0.4 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.

SQL> show parameter COMPATIBLE

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


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

System altered.

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

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

SQL> show parameter COMPATIBLE

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

SQL> select name,open_mode,version from v$database,v$instance;

NAME                 OPEN_MODE            VERSION
-------------------- -------------------- -----------------
COKE                 READ WRITE           12.2.0.1.0

SQL>

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

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/