Category Archives: 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

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

Upgrade DB Manually 11.2.0.4 to 12.2.0.1

Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1

Contents
___________________________________________________________________________________________________

1. Environment
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. Stop LISTENER
13. Create Flashback Guaranteed Restore Point
14. Shutdown Database
15. Copy init and password files from 11g to 12c dbs home
16. Startup DB in Upgrade mode
17. Run catctl.pl with catupgrd.sql
18. Run postupgrade_fixups.sql
19. Update Timezone
20. Re-Run postupgrade_fixups.sql
21. Run utlu122s.sql
22. Run catuppst.sql
23. Run utlrp.sql
24. Drop Restore point
25. Set COMPATIBALE parameter value to 12.2.0
26. Restart the database
27. Start Listener from 12c home
28. Verify
29. Edit oratab
___________________________________________________________________________________________________


1. Environment

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

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

Upgrade Method		: Manual


2. Run preupgrade script

/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@rac1 ~]$ mkdir -p /home/oracle/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 preupgrade]$ /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@rac1 preupgrade]$


3. View Preupgrade log

[oracle@rac1 preupgrade]$ 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:  DELL
     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
     ----------                     ----------  --------  ----------  ------

     EXAMPLE                            346 MB  DISABLED      309 MB  None
     SYSAUX                             500 MB  ENABLED      1411 MB  None
     SYSTEM                             750 MB  ENABLED      1260 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            65 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.

   + Please make sure that all the MVs are refreshed and sys.sumdelta$
     becomes empty before doing upgrade, unless you have strong business
     reasons not to do so. You can use dbms_mview.refresh() to refresh the
     MVs except those stale ones  to be kept due to business need. If there
     are any stale MVs depending on changes in sys.sumdelta$, do not truncate
     it, because doing so will cause wrong results after refresh.

     There is one or more non-fresh MV in the database or sumdelta$ is not
     empty.

     Oracle recommends that all materialized views (MV's) are refreshed
     before upgrading the database because this will clear the MV logs and
     the sumdelta$ table, and make the UPGRADE process faster. If you choose
     to not refresh some MVs, the change data for those MV's will be carried
     through the UPGRADE process. After UPGRADE, you can refresh the MV's and
      MV incremental refresh should work in normal cases.

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

     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
  ===================
   + If you use the -T option for the database upgrade, then run
     $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
     to VALIDATE and UPGRADE any user tables affected by changes to
     Oracle-Maintained types.

     There are user tables dependent on Oracle-Maintained object types.

     If the -T option is used to set user tablespaces to READ ONLY during the
     upgrade, user tables in those tablespaces, that are dependent on
     Oracle-Maintained types, will not be automatically upgraded. If a type
     is evolved during the upgrade, any dependent tables need to be
     re-validated and upgraded to the latest type version AFTER the database
     upgrade completes.

   + 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 preupgrade]$


4. Remove the EM repository

[oracle@rac1 preupgrade]$ emctl stop dbconsole

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

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
DELL      READ WRITE           11.2.0.4.0

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
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

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


6. Update INITIALIZATION PARAMETERS

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
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-09-15 20:14:51

For Source Database:     DELL
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
mv_refresh                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 standby side

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch/DELL/
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
SQL> !df -h /u02/arch/DELL/
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G   24G   18G  57% /u02

SQL>


12. Stop LISTENER

[oracle@rac1 ~]$ ps -ef | grep tns

[oracle@rac1 ~]$ lsnrctl stop LISTENER


13. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onward
*** 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>

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

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

SQL>


SQL> show parameter compatible

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


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

System altered.

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL>

SQL> show parameter recovery

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

[oracle@rac1 ~]$ df -h /u02/fast_recovery_area/DELL
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G   24G   18G  57% /u02
[oracle@rac1 ~]$

SQL> select * from V$restore_point;

no rows selected

SQL>

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL>

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

NAME                 GUARANTEE_ TIME
-------------------- ---------- ---------------------------------
PRE_UPGRADE          YES        15-SEP-18 09.53.59.000000000 PM

SQL>


14. Shutdown Database

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


15. Copy init and password files from 11g to 12c dbs home

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs
[oracle@rac1 dbs]$ ls -ltr *DELL*
-rw-r-----. 1 oracle dba   24 Aug 14 19:48 lkDELL
-rw-r-----. 1 oracle dba 1536 Aug 14 19:50 orapwDELL
-rw-r-----. 1 oracle dba 2560 Sep 15 20:35 spfileDELL.ora
-rw-rw----. 1 oracle dba 1544 Sep 15 21:03 hc_DELL.dat
[oracle@rac1 dbs]$ cp orapwDELL spfileDELL.ora /u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/12.2.0.1/dbs/*DELL*
-rw-r-----. 1 oracle dba 2560 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/spfileDELL.ora
-rw-r-----. 1 oracle dba 1536 Sep 15 21:10 /u01/app/oracle/product/12.2.0.1/dbs/orapwDELL
[oracle@rac1 dbs]$


16. Startup DB in Upgrade mode

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

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 15 22:05:13 2018

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

Connected to an idle instance.

SQL> startup upgrade;
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> select instance_name,version,status from v$instance;

INSTANCE_NAME    VERSION           STATUS
---------------- ----------------- ------------
DELL             12.2.0.1.0        OPEN MIGRATE  <----

SQL>


17. Run catctl.pl with catupgrd.sql

[oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade
[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin

[oracle@rac1 admin]$ nohup /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &
[1] 16004
[oracle@rac1 admin]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac1 admin]$ disown


[oracle@rac1 admin]$ ps -ef | grep -i catctl.pl
oracle   16004  6189  1 22:22 pts/0    00:00:01 /u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql
oracle   16324  6189  0 22:23 pts/0    00:00:00 grep -i catctl.pl
[oracle@rac1 admin]$

[oracle@rac1 admin]$  more nohup.out

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/whileupgrade
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170107]
..
..  
..
..
 LOG FILES: (/home/oracle/whileupgrade/catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle/whileupgrade/upg_summary.log  <----

Grand Total Upgrade Time:    [0d:1h:24m:24s]
[oracle@rac1 admin]$


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      UPGRADED
CATPROC    Oracle Database Packages and Types       12.2.0.1.0      UPGRADED
JAVAVM     JServer JAVA Virtual Machine             12.2.0.1.0      UPGRADED
XML        Oracle XDK                               12.2.0.1.0      UPGRADED
CATJAVA    Oracle Database Java Packages            12.2.0.1.0      UPGRADED
APS        OLAP Analytic Workspace                  12.2.0.1.0      UPGRADED
OWM        Oracle Workspace Manager                 12.2.0.1.0      UPGRADED
CONTEXT    Oracle Text                              12.2.0.1.0      UPGRADED
XDB        Oracle XML Database                      12.2.0.1.0      UPGRADED
ORDIM      Oracle Multimedia                        12.2.0.1.0      UPGRADED
SDO        Spatial                                  12.2.0.1.0      UPGRADED
XOQ        Oracle OLAP API                          12.2.0.1.0      UPGRADED
APEX       Oracle Application Express               5.0.4.00.12     UPGRADED

13 rows selected.

SQL>


18. Run postupgrade_fixups.sql

[oracle@rac1 preupgrade]$ pwd
/home/oracle/preupgrade
[oracle@rac1 preupgrade]$ which sqlplus
/u01/app/oracle/product/12.2.0.1/bin/sqlplus
[oracle@rac1 preupgrade]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 16 00:32:19 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> !ls -ltr postupgrade_fixups.sql
-rw-r--r--. 1 oracle dba 8129 Sep 15 20:14 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-09-15 20:14:52

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

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
depend_usr_tables         Failed  Manual fixup recommended.
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None
fixed_objects             Passed  None
upg_by_std_upgrd          Passed  None

PL/SQL procedure successfully completed.


Session altered.

SQL> SQL>


19. Update Timezone

Download DBMS_DST_scriptsV1.9.zip from Oracle Support 1585343.1

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

[oracle@rac1 DBMS_DST_scriptsV1.9]$ pwd
/home/oracle/TZ/DBMS_DST_scriptsV1.9
[oracle@rac1 DBMS_DST_scriptsV1.9]$ ls -ltr
total 68
-rw-r--r--. 1 oracle dba 31010 Aug 22  2014 upg_tzv_check.sql
-rw-r--r--. 1 oracle dba 19502 Aug 22  2014 upg_tzv_apply.sql
-rw-r--r--. 1 oracle dba  6294 Jan  8  2015 countstarTSTZ.sql
-rw-r--r--. 1 oracle dba  7213 Mar 17 18:30 countstatsTSTZ.sql
[oracle@rac1 DBMS_DST_scriptsV1.9]$
[oracle@rac1 DBMS_DST_scriptsV1.9]$ which sqlplus
/u01/app/oracle/product/12.2.0.1/bin/sqlplus
[oracle@rac1 DBMS_DST_scriptsV1.9]$

[oracle@rac1 DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 16 00:54:44 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> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.2.0.1 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv26 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL>
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14  <----

1 row selected.

SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv26 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1157628608 bytes
Database Buffers          486539264 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv26 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL>
SQL> SELECT version FROM v$timezone_file;

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

1 row selected.

SQL>


20. Re-Run postupgrade_fixups.sql

SQL> @/home/oracle/preupgrade/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-15 20:14:52

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

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
depend_usr_tables         Failed  Manual fixup recommended. <-- Ignore this. 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>

12.2 DB Post Upgrade:postupgrade_fixups.sql shows "depend_usr_tables Failed Manual fixup recommended" (Doc ID 2266026.1)

Solution:

1) If Database Upgraded without using -T option,then FAILED status to be ignored.

or

2) If Database Upgraded with using -T option .

Then execute $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete.

And then ignore FAILED status .


[oracle@rac1 admin]$  more nohup.out

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/whileupgrade
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0  <--------
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0


21. Run utlu122s.sql

SQL> @/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlu122s.sql


Oracle Database 12.2 Post-Upgrade Status Tool           09-16-2018 01:10:10

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
..
..
..
Oracle Server                          UPGRADED      12.2.0.1.0  00:19:59
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:10:28
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:01:36
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:49
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:23
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:16
Oracle Text                            UPGRADED      12.2.0.1.0  00:01:37
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:04:08
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:23
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:03:25
Spatial                                UPGRADED      12.2.0.1.0  00:09:48
Oracle Application Express             UPGRADED     5.0.4.00.12  00:21:35
Final Actions                                                    00:03:25
Post Upgrade                                                     00:02:26

Total Upgrade Time: 01:21:57

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

Summary Report File = /home/oracle/whileupgrade/upg_summary.log

01:10:10 SQL>
01:10:10 SQL>


22. Run catuppst.sql

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

Session altered.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART              2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART     FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP DBRESTART         NONE 2018-09-16 01:12:55



TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST       STARTED 2018-09-16 01:12:55


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN             2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP POSTUP_BGN        NONE 2018-09-16 01:12:55



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN             2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_BGN        NONE 2018-09-16 01:12:55



PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END             2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END    FINISHED 2018-09-16 01:12:55
DBUA_TIMESTAMP CATREQ_END        NONE 2018-09-16 01:12:55


catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.






PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END             2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END    FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP POSTUP_END        NONE 2018-09-16 01:12:59



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST               2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST      FINISHED 2018-09-16 01:12:59
DBUA_TIMESTAMP CATUPPST          NONE 2018-09-16 01:12:59



Session altered.

SQL>


23. Run utlrp.sql

Run in background 

cd /u01/app/oracle/product/12.2.0.1/rdbms/admin
nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &


---- OR ----


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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2018-09-16 01:14:35

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2018-09-16 01:21:18

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

...Starting validation 01:21:36
...Database user "SYS", database schema "APEX_050000", user# "128" 01:21:36
...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 01:21:38
...272 packages
...265 package bodies
...465 tables
...8 functions
...16 procedures
...4 sequences
...497 triggers
...1582 indexes
...255 views
...0 libraries
...14 types
...5 type bodies
...0 operators
...0 index types
...Begin key object existence check 01:21:43
...Completed key object existence check 01:21:43
...Setting DBMS Registry 01:21:43
...Setting DBMS Registry Complete 01:21:43
...Exiting validate 01:21:43

PL/SQL procedure successfully completed.

SQL>

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

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

SQL>


24. 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        15-SEP-18 09.53.59.000000000 PM

SQL>

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

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

no rows selected  <-----

SQL>


25. Set COMPATIBALE parameter value to 12.2.0

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>


26. Restart the database

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


27. Start Listener from 12c home

[oracle@rac1 admin]$ lsnrctl start LISTENER
[oracle@rac1 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-SEP-2018 01:43:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-SEP-2018 01:42:15
Uptime                    0 days 0 hr. 1 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DELL" has 1 instance(s).
  Instance "DELL", status READY, has 1 handler(s) for this service...
Service "DELLXDB" has 1 instance(s).
  Instance "DELL", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$ 


28. Verify DBA_REGISTRY

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

NAME                 OPEN_MODE            VERSION
-------------------- -------------------- -----------------
DELL                 READ WRITE           12.2.0.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>

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


29. Edit oratab

#DELL:/u01/app/oracle/product/11.2.0.4:N  <--
DELL:/u01/app/oracle/product/12.2.0.1:N

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

Install 18c

Install Oracle 18c Release 3 on RHEL 6.8

Pre-requisites already been taken care like memory, swap, kernel parameters, add groups, profile and users…etc

Contents
___________________________________________________________________________________________________

1. Hardware Requirements
2. Verify OS version
3. Unzip 18c Binaries
4. Set the DISPLAY Environment
5. Invoke ./runInstaller
6. Verify
___________________________________________________________________________________________________

1. Hardware Requirements

The first thing we need to verify the hardware requirements for an Oracle 18c Release 3

— Check Physical RAM.
# grep MemTotal /proc/meminfo
We need at least 8192 MB of physical RAM. <----

— Check Swap Space.
# grep SwapTotal /proc/meminfo/*
RAM up to 1024MB then swap = 2 times the size of RAM
RAM between 2049MB and 8192MB then swap = equal to the size of RAM
RAM more than 8192MB then swap size = 0.75 times the size of RAM

We need at least 8192 MB of swap <----


-– Check space available in /tmp
# df -h /tmp/*
You need to have at least 2048 MB of space in the /tmp directory. <---

-– Check space for Oracle Software and pre-configured database.
# df -h

Space requirement for Oracle 18c Software:
Enterprise Edition 14G  <----

—- To determine whether the system architecture can run the software, enter the following command:

# grep "model name" /proc/cpuinfo

This command displays the processor type. Verify that the processor architecture matches the Oracle software release that you want to install. If you do not see the expected output, then you cannot install the software on this system.


2. Verify OS version

[oracle@rac1 product]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
[oracle@rac1 product]$


3. Unzip 18c Binaries

NOTE: You can't edit oracle home location while installation using OUI. It will pickup automatically ORACLE HOME location, where you have unzipped database binaries. Hence directly unzip in ORACLE HOME location and then start ./runInstaller

After unzip, it will NOT keep all files in single directory.

[oracle@rac1 ]cd /u02/app/oracle/product/18c
[oracle@rac1 18c]$ ls -ltr
total 4457672
-rw-r--r--. 1 oracle dba 4564649047 Jul 25 20:26 LINUX.X64_180000_db_home.zip
[oracle@rac1 18c]$

[oracle@rac1 18c]$ unzip LINUX.X64_180000_db_home.zip

[oracle@rac1 18c]$ ls -ltr
total 4457972
-rw-r--r--.  1 oracle dba        852 Aug 18  2015 env.ora
-rw-r--r--.  1 oracle dba       2927 Oct 14  2016 schagent.conf
-rwxr-x---.  1 oracle dba       1783 Mar  8  2017 runInstaller <----
drwxr-x---.  7 oracle dba       4096 Feb  7 23:11 xdk
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 wwg
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 usm
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 suptools
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:11 srvm
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 sqlj
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 slax
-rw-r-----.  1 oracle dba         10 Feb  7 23:11 root.sh.old.1
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 relnotes
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 racg
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 R
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 owm
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 oss
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 ordim
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 oracore
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:11 olap
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:11 mgw
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:11 log
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 has
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 dv
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 diagnostics
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 dbjava
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 data
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:11 css
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:12 plsql
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 dbs
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 utl
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 instantclient
drwxr-xr-x. 13 oracle dba       4096 Feb  7 23:12 dmu
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:12 ucp
drwxr-xr-x.  3 oracle dba       4096 Feb  7 23:12 jdbc
drwxr-xr-x.  2 oracle dba       4096 Feb  7 23:12 QOpatch
drwxr-xr-x.  4 oracle dba       4096 Feb  7 23:12 ords
drwxr-xr-x.  5 oracle dba       4096 Feb  7 23:13 sdk
drwxr-xr-x.  6 oracle dba       4096 Feb  7 23:13 apex
drwxr-xr-x. 22 oracle dba       4096 Feb  7 23:13 sqldeveloper
drwxr-xr-x.  8 oracle dba       4096 Feb  7 23:13 odbc
drwxr-xr-x. 11 oracle dba       4096 Feb  7 23:14 ctx
-rwx------.  1 oracle dba        786 Feb  7 23:14 root.sh.old
drwxr-x---. 14 oracle dba       4096 Jun  4 03:20 OPatch
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:17 sqlplus
-rwx------.  1 oracle dba        638 Jul 18 19:18 root.sh
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:39 jlib
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:41 sqlpatch
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 jdk
drwxr-xr-x. 13 oracle dba       4096 Jul 18 19:44 rdbms
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:44 precomp
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 perl
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 ord
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 opmn
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 nls
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:44 network
drwxr-xr-x.  9 oracle dba       4096 Jul 18 19:44 md
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:44 ldap
drwxr-xr-x.  8 oracle dba       4096 Jul 18 19:44 javavm
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 hs
drwxr-xr-x.  4 oracle dba       4096 Jul 18 19:44 drdaas
drwxr-xr-x.  3 oracle dba       4096 Jul 18 19:44 demo
drwxr-xr-x.  7 oracle dba       4096 Jul 18 19:44 cv
drwxr-xr-x.  6 oracle dba       4096 Jul 18 19:44 crs
drwxr-xr-x.  9 oracle dba       4096 Jul 18 19:44 assistants
drwxr-xr-x.  8 oracle dba       4096 Jul 18 19:44 oui
drwxr-xr-x.  3 oracle dba      16384 Jul 18 19:44 lib
drwxr-x---. 13 oracle dba       4096 Jul 18 19:44 inventory
drwxr-xr-x.  5 oracle dba       4096 Jul 18 19:44 deinstall
drwxr-xr-x.  4 oracle dba       4096 Jul 18 19:44 clone
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:44 bin
drwxr-xr-x.  2 oracle dba       4096 Jul 18 19:44 addnode
drwxr-xr-x. 10 oracle dba       4096 Jul 18 19:53 install
-rw-r--r--.  1 oracle dba 4564649047 Jul 25 20:26 LINUX.X64_180000_db_home.zip
[oracle@rac1 18c]$


4. Set the DISPLAY Environment

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

export DISPLAY=192.168.2.3:0.0;


5. Invoke ./runInstaller

Start the Oracle Universal Installer (OUI) by issuing the following command.

[oracle@rac1 ~]$ cd /u02/app/oracle/product/18c/
[oracle@rac1 18c]$ ./runInstaller         <-----
Launching Oracle Database Setup Wizard...

















6. Verify

[oracle@rac1 bin]$ export ORACLE_HOME=/u02/app/oracle/product/18c
[oracle@rac1 bin]$ ./sqlplus

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jul 27 13:16:55 2018
Version 18.3.0.0.0

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

Enter user-name:

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

Colored SQL_ID

How to mark SQL_ID as Colored

Contents
___________________________________________________________________________________________________

1. Overview
2. Mark SQL_ID as colored
3. Unmark Colored SQL_ID
___________________________________________________________________________________________________


1. Overview

By default, only top sql_ids are captured in database snapshots.

If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities  (so that it does not have to be a TOP SQL).

Information will be captured in each snapshot for sqls marked using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL. 

All the information related to the specific sql (sql plan, executions statistics, etc) will be stored in the AWR tables. 

However the SQLs will not neccesarily appear in the AWR Report sections related to SQLS unless they are indeed amongst the top SQLs for that sections as determined by the setting for DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS


2. Mark SQL_ID as colored

SQL> select * from DBA_HIST_COLORED_SQL;

no rows selected

SQL> execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>'1gyx4vasq9yv0');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TI
---------- ------------- ---------
3554487085 1gyx4vasq9yv0 25-JUL-18  <-----

SQL> 


3. Unmark Colored SQL_ID

SQL> execute DBMS_WORKLOAD_REPOSITORY.remove_COLORED_SQL(sql_id=>'1gyx4vasq9yv0');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

no rows selected   <------

SQL>

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

Convert Non-CDB to PDB with DBMS_PDB

Convert 12.2 Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB (CDB1)

Contents
___________________________________________________________________________________________________

1. Overview
2. Environment
3. Shutdown NON-CDB (NONCDB12C)
4. Open Non-CDB in read only (this is for consistency)
5. Run DBMS_PDB.DESCRIBE to create an XML file
6. Shutdown the Non-CDB
7. Connect to Target
8. Check whether Non-CDB (NONCDB12C) can be plugged into CDB(CDB1)
9. Create target directory for datafiles
10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1
11. Verify newly created PDB NONCDB12C on CDB1
12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)
13. Verify table PDB_PLUG_IN_VIOLATIONS
___________________________________________________________________________________________________


1. Overview

There are 4 methods to move Non-CDB into a Pluggable Database (PDB) in a Container Database

- Create PDB by cloning a Non-CDB
- Use DBMS_PDB package
- Use Oracle Data Pump export/import
- Use GoldenGate Replication

Note: If status is UNUSABLE state it must be dropped before a PDB with the same name as the unusable PDB can be created. YOu can view the status from CBD_PDBS OR DBA_PDBS views


2. Environment

Platform		: Linux x86_64
Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version		: Oracle 12.2.0.1, File system: Normal
Source NON-CDB   	: NONCDB12C
Target CDB 		: CDB1
Target PDB  	        : NONCDB12C
Oracle Home Path        : /u01/app/oracle/product/12.2.0.1


3. Shutdown NON-CDB (NONCDB12C)

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ WRITE           NO <-----

SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to raj;

Grant succeeded.

SQL> alter user raj quota unlimited on users;

User altered.

SQL> conn raj/raj;
Connected.
SQL> show user
USER is "RAJ"
SQL>
SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL> insert into sugi values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('TEJU','DBA')

1 row created.

SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('VIJAY','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from sugi; <-- please check output once task completed.

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL> 
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/NONCDB12C/system01.dbf
/u02/oradata/NONCDB12C/sysaux01.dbf
/u02/oradata/NONCDB12C/undotbs01.dbf
/u02/oradata/NONCDB12C/users01.dbf

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


4. Open Non-CDB in read only (this is for consistency)

SQL> STARTUP MOUNT EXCLUSIVE;
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.
SQL>
SQL> alter database open read only;

Database altered.

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ ONLY            NO

SQL>


5. Run DBMS_PDB.DESCRIBE to create an XML file

The DBMS_PDB. It describe procedure creates the XML file that contains all necessary information regarding the nonCDB Database

SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml');
END;
/ 

PL/SQL procedure successfully completed.

SQL>


[oracle@rac1 ~]$ cat /u02/oradata/NONCDB12C/noncdb12c.xml

Please click noncdb12c.xml    <------

[oracle@rac1 ~]$


6. Shutdown the Non-CDB

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ ONLY            NO

SQL>
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$

On Target
===========


7. Connect to Target

Connect to the target Container Database (CDB1)

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES  <-----

SQL>


8. Check whether non-cdb (NONCDB12C) can be plugged into CDB(CDB1)

SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml',
pdb_name => 'NONCDB12')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

YES  

PL/SQL procedure successfully completed.

SQL>

--  Incase output is NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Non-CDB to WARNING   PDB plugged in is a non-CDB,        PENDING
            PDB                 requires noncdb_to_pdb.sql be run.

NONCDB12C  Parameter  WARNING   CDB parameter memory_target         PENDING
                                mismatch: Previous 1584M Current 0


SQL>


9. Create target directory for datafiles

SQL> !mkdir -p /u01/app/oracle/oradata/CDB1/NONCDB12C

SQL>


10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1

The NOCOPY option keeps the original location of the datafiles as the database is Plugged-In
The SOURCE_FILE_NAME_CONVERT option allows us to specify a new location of the datafiles

I am plugging the database in to a CDB on the same server with COPY clause and hence using  FILE_NAME_CONVERT.

SQL> CREATE PLUGGABLE DATABASE NONCDB12C USING '/u02/oradata/NONCDB12C/noncdb12c.xml'
COPY
file_name_convert=('/u02/oradata/NONCDB12C','/u01/app/oracle/oradata/CDB1/NONCDB12C'); 

Pluggable database created.

SQL>


11. Verify newly created PDB NONCDB12C on CDB1

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL>
SQL> col pdb_name for a30
SQL> SELECT pdb_name , status from cdb_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NEW <--------

SQL>

--- OR ---

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NEW

SQL>
SQL> col name for a30
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------------------------------------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
NONCDB12C                      MOUNTED <-----

SQL>

--check that datafiles copied to new location 

SQL> !ls -ltr /u01/app/oracle/oradata/CDB1/NONCDB12C*
total 1397856
-rw-r-----. 1 oracle dba  33562624 Jul 22 21:39 temp01.dbf
-rw-r-----. 1 oracle dba   5251072 Jul 22 21:39 users01.dbf
-rw-r-----. 1 oracle dba  73408512 Jul 22 21:39 undotbs01.dbf
-rw-r-----. 1 oracle dba 503324672 Jul 22 21:39 sysaux01.dbf
-rw-r-----. 1 oracle dba 849354752 Jul 22 21:39 system01.dbf

SQL>


12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)

You have to convert the plugged non-CDB to a proper PDB by deleting unnecessary metadata from PDB SYSTEM tablespace.
For this purpose, you execute the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
script when connected to the PDB. 

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> ALTER SESSION SET CONTAINER=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C
SQL>
SQL> show con_id

CON_ID
------------------------------
4
SQL>


SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
21:58:13 SQL> SET TIMING ON
21:58:13 SQL>
21:58:13 SQL> WHENEVER SQLERROR EXIT;
21:58:13 SQL>
...
...
...
22:22:53 SQL> -- restore old settings
22:22:53 SQL> START ncdb2pdb.settings.sql
22:22:53 SQL> set appinfo OFF
22:22:54 SQL> set appinfo "SQL*Plus"
22:22:54 SQL> set arraysize 15
22:22:54 SQL> set autocommit OFF
22:22:54 SQL> set autoprint OFF
22:22:54 SQL> set autorecovery OFF
22:22:54 SQL> set autotrace OFF
22:22:54 SQL> set blockterminator "."
22:22:54 SQL> set cmdsep OFF
22:22:54 SQL> set colinvisible OFF
22:22:54 SQL> set colsep " "
22:22:54 SQL> set compatibility NATIVE
22:22:54 SQL> set concat "."
22:22:54 SQL> set copycommit 0
22:22:54 SQL> set copytypecheck ON
22:22:54 SQL> set define "&"
22:22:54 SQL> set describe DEPTH 1 LINENUM OFF INDENT ON
22:22:54 SQL> set echo ON
22:22:54 SQL> set editfile "afiedt.buf"
22:22:54 SQL> set embedded OFF
22:22:54 SQL> set escape OFF
22:22:54 SQL> set escchar OFF
22:22:54 SQL> set exitcommit ON
22:22:54 SQL> set feedback ON
22:22:54 SQL> set flagger OFF
22:22:54 SQL> set flush ON
22:22:54 SQL> set fullcolname OFF
22:22:54 SQL> set heading ON
22:22:54 SQL> set headsep "|"
22:22:54 SQL> set linesize 80
22:22:54 SQL> set lobprefetch 0
22:22:54 SQL> set logsource ""
22:22:54 SQL> set long 80
22:22:54 SQL> set longchunksize 80
22:22:54 SQL> set markup HTML OFF HEAD "
.......................................
..... Intentionally left black here.. trimmed output
.......................................
SQL> set termout ON 22:22:54 SQL> set time OFF SQL> set timing OFF SQL> set trimout ON SQL> set trimspool ON SQL> set underline "-" SQL> set verify OFF SQL> set wrap ON SQL> set xmloptimizationcheck OFF SQL> SQL>

Click here for complete output noncdb_to_pdb_output  <------


13. Verify table PDB_PLUG_IN_VIOLATIONS

Check out the table PDB_PLUG_IN_VIOLATIONS in order to see if there are error related to the plug in process, but read first the note "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS (Doc ID 2020172.1)"

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

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:41:00 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> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Parameter  WARNING   CDB parameter memory_target         RESOLVED
                                mismatch: Previous 1584M Current 0

NONCDB12C  Non-CDB to ERROR     PDB plugged in is a non-CDB,        PENDING
            PDB                 requires noncdb_to_pdb.sql be run.


SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 NONCDB12C                      MOUNTED
SQL> 
SQL> alter session set container=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C

SQL>
SQL> alter database open;

Database altered.

SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 NONCDB12C                      READ WRITE NO
SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:58:51 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> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> col pdb_name for a30
SQL> SELECT pdb_name , status from cdb_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NORMAL <----

SQL>

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Parameter  WARNING   CDB parameter memory_target         RESOLVED
                                mismatch: Previous 1584M Current 0

NONCDB12C  Non-CDB to ERROR     PDB plugged in is a non-CDB,        RESOLVED
            PDB                 requires noncdb_to_pdb.sql be run.


SQL>
SQL>  alter session set container=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C
SQL>
SQL> select * from raj.sugi;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL>

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

Create Non-CDB Oracle Database 12c on Linux using DBCA (GUI)

Create Non-CDB Oracle Database 12c on Linux using DBCA (GUI)

Contents
___________________________________________________________________________________________________

1. Environment
2. Invoke dbca
3. Verify
___________________________________________________________________________________________________


1. Environment

Platform		: Linuxx86_64
Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version		: Oracle 12.2.0.1, File system: Normal
NON-CDB Name	        : NONCDB12C
Oracle Home Path        : /u01/app/oracle/product/12.2.0.1


2. Invoke dbca

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
[oracle@rac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@rac1 ~]$ which dbca
/u01/app/oracle/product/12.2.0.1/bin/dbca
[oracle@rac1 ~]$ dbca  <----
              












3. Verify

[oracle@rac1 ~]$ cat /etc/oratab | grep -i NONCDB12C
NONCDB12C:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep pmon
oracle    9750     1  0 21:10 ?        00:00:00 ora_pmon_NONCDB12C  <---
oracle   10228  3318  0 21:24 pts/0    00:00:00 grep pmon
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? NONCDB12C
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 21 21:25:53 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> select name,open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ WRITE           NO  <-----

SQL>
SQL> show con_name

CON_NAME
------------------------------
NONCDB12
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive/NONCDB12C
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL>
SQL> select name from v$datafile
UNION
select name from v$tempfile;  2    3

NAME
--------------------------------------------------------------------------------
/u02/oradata/NONCDB12C/sysaux01.dbf
/u02/oradata/NONCDB12C/system01.dbf
/u02/oradata/NONCDB12C/temp01.dbf
/u02/oradata/NONCDB12C/undotbs01.dbf
/u02/oradata/NONCDB12C/users01.dbf

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        22     2  0 19:30 ?        00:00:00 [netns]
oracle    2978     1  0 19:31 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   10732  3318  0 21:27 pts/0    00:00:00 grep tns
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl status LISTENER | grep -i NONCDB12C
Service "NONCDB12C.rajasekhar.com" has 1 instance(s).
  Instance "NONCDB12C", status READY, has 1 handler(s) for this service...
Service "NONCDB12CXDB.rajasekhar.com" has 1 instance(s).
  Instance "NONCDB12C", status READY, has 1 handler(s) for this service...
[oracle@rac1 ~]$

[oracle@rac1 ~]$ tnsping NONCDB12C

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-JUL-2018 21:28:02

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

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

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NONCDB12C.rajasekhar.com)))
OK (0 msec) <----
[oracle@rac1 ~]$

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

[oracle@rac1 ~]$ sqlplus sys@NONCDB12C AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 21 21:31:17 2018

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

Enter password:

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

SQL>
SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" FROM DUAL;

SYSDATE
-----------------------------
21-JUL-2018 21:31:37

SQL>

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

Install 12.2 Database Binaries in silent mode

Install Oracle 12.2 database software in silent mode (12.2.0.1)

Please note pre-requisites already been taken care like kernel parameters, add groups, profile and etc


1. Overview

Usually to install 12.2.0.1 database software we will use ./runInstaller graphical user interface.

Some times may not have access to a graphical user interface. 

Silent mode installation allows to configure necessary Oracle components without using graphical interface

In this response file can be used to provide all the required information for the installation, so no additional user input is required.


2. Unzip software

[oracle@localhost]$ unzip linuxx64_12201_database.zip


3. Backup response file

[oracle@localhost ~]$ cd database
[oracle@localhost database]$ ls -ltr
total 36
-rwxr-xr-x.  1 oracle dba  500 Feb  6  2013 welcome.html
drwxr-xr-x.  4 oracle dba 4096 Jan 26  2017 install
-rwxr-xr-x.  1 oracle dba 8771 Jan 26  2017 runInstaller
drwxr-xr-x.  2 oracle dba 4096 Jan 26  2017 rpm
drwxrwxr-x.  2 oracle dba 4096 Jan 26  2017 sshsetup
drwxrwxr-x.  2 oracle dba 4096 Jan 26  2017 response
drwxr-xr-x. 14 oracle dba 4096 Jan 26  2017 stage
[oracle@localhost database]$ cd response
[oracle@localhost response]$ ls -ltr
total 60
-rwxrwxr-x. 1 oracle dba  6209 Jun 20  2016 netca.rsp
-rwxrwxr-x. 1 oracle dba 25502 Jan  5  2017 dbca.rsp
-rw-rw-r--. 1 oracle dba 22924 Jan 26  2017 db_install.rsp
[oracle@localhost response]$ cp db_install.rsp db_install.rsp.bkp
[oracle@localhost response]$ ls -ltr
total 84
-rwxrwxr-x. 1 oracle dba  6209 Jun 20  2016 netca.rsp
-rwxrwxr-x. 1 oracle dba 25502 Jan  5  2017 dbca.rsp
-rw-rw-r--. 1 oracle dba 22924 Jan 26  2017 db_install.rsp
-rw-r--r--. 1 oracle dba 22924 Jan  2 08:25 db_install.rsp.bkp
[oracle@localhost response]$


4. Modify the response file

File name: db_install.rsp

[oracle@localhost response]$ cat db_install.rsp
####################################################################
## Copyright(c) Oracle Corporation 1998,2017. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################


#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0

#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
#   - INSTALL_DB_SWONLY
#   - INSTALL_DB_AND_CONFIG
#   - UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY

#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#-------------------------------------------------------------------------------
ORACLE_HOME=/home/oracle/product/12.2/db_1

#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/home/oracle/product

#-------------------------------------------------------------------------------
# Specify the installation edition of the component.
#
# The value should contain only one of these choices.
#   - EE     : Enterprise Edition
#   - SE2     : Standard Edition 2


#-------------------------------------------------------------------------------

oracle.install.db.InstallEdition=EE
###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
# Provide values for the OS groups to which SYSDBA and SYSOPER privileges     #
# needs to be granted. If the install is being performed as a member of the   #
# group "dba", then that will be used unless specified otherwise below.       #
#                                                                             #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System.                                                           #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# The OSDBA_GROUP is the OS group which is to be granted SYSDBA privileges.
#-------------------------------------------------------------------------------
oracle.install.db.OSDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSOPER_GROUP is the OS group which is to be granted SYSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OSOPER_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSBACKUPDBA_GROUP is the OS group which is to be granted SYSBACKUP privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSBACKUPDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSDGDBA_GROUP is the OS group which is to be granted SYSDG privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSDGDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSKMDBA_GROUP is the OS group which is to be granted SYSKM privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSKMDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSRACDBA_GROUP is the OS group which is to be granted SYSRAC privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSRACDBA_GROUP=oinstall

###############################################################################
#                                                                             #
#                               Grid Options                                  #
#                                                                             #
###############################################################################
#------------------------------------------------------------------------------
# Specify the type of Real Application Cluster Database
#
#   - ADMIN_MANAGED: Admin-Managed
#   - POLICY_MANAGED: Policy-Managed
#
# If left unspecified, default will be ADMIN_MANAGED
#------------------------------------------------------------------------------
oracle.install.db.rac.configurationType=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is ADMIN_MANAGED
#
# Specify the cluster node names selected during the installation.
# Leaving it blank will result in install on local server only (Single Instance)
#
# Example : oracle.install.db.CLUSTER_NODES=node1,node2
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=

#------------------------------------------------------------------------------
# This variable is used to enable or disable RAC One Node install.
#
#   - true  : Value of RAC One Node service name is used.
#   - false : Value of RAC One Node service name is not used.
#
# If left blank, it will be assumed to be false.
#------------------------------------------------------------------------------
oracle.install.db.isRACOneInstall=

#------------------------------------------------------------------------------
# Value is required only if oracle.install.db.isRACOneInstall is true.
#
# Specify the name for RAC One Node Service
#------------------------------------------------------------------------------
oracle.install.db.racOneServiceName=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
#
# Specify a name for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolName=pool1
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolName=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
#
# Specify a number as cardinality for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolCardinality=2
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolCardinality=

###############################################################################
#                                                                             #
#                        Database Configuration Options                       #
#                                                                             #
###############################################################################

#-------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
#   - GENERAL_PURPOSE
#   - DATA_WAREHOUSE
# GENERAL_PURPOSE: A starter database designed for general purpose use or transaction-heavy applications.
# DATA_WAREHOUSE : A starter database optimized for data warehousing applications.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=

#-------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database SID.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=

#-------------------------------------------------------------------------------
# Specify whether the database should be configured as a Container database.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.ConfigureAsContainerDB=

#-------------------------------------------------------------------------------
# Specify the  Pluggable Database name for the pluggable database in Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.PDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database character set.
#
#  One of the following
#  AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
#  EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
#  BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
#  AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
#  IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
#  KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
#  ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=

#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=

#-------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=

#-------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto
# the starter database or not.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=

###############################################################################
#                                                                             #
# Passwords can be supplied for the following four schemas in the             #
# starter database:                                                           #
#   SYS                                                                       #
#   SYSTEM                                                                    #
#   DBSNMP (used by Enterprise Manager)                                       #
#                                                                             #
# Same password can be used for all accounts (not recommended)                #
# or different passwords for each account can be provided (recommended)       #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=

#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=

#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=

#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Specify the PDBADMIN password required for creation of Pluggable Database in the Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.PDBADMIN=

#-------------------------------------------------------------------------------
# Specify the management option to use for managing the database.
# Options are:
# 1. CLOUD_CONTROL - If you want to manage your database with Enterprise Manager Cloud Control along with Database Express.
# 2. DEFAULT   -If you want to manage your database using the default Database Express option.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.managementOption=

#-------------------------------------------------------------------------------
# Specify the OMS host to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsHost=

#-------------------------------------------------------------------------------
# Specify the OMS port to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsPort=

#-------------------------------------------------------------------------------
# Specify the EM Admin user name to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminUser=

#-------------------------------------------------------------------------------
# Specify the EM Admin password to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminPassword=

###############################################################################
#                                                                             #
# SPECIFY RECOVERY OPTIONS                                                    #
# ------------------------------------                                        #
# Recovery options for the database can be mentioned using the entries below  #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable is to be set to false if database recovery is not required. Else
# this can be set to true.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableRecovery=

#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
#   - FILE_SYSTEM_STORAGE
#   - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=

#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

#-------------------------------------------------------------------------------
# Specify the recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
# Applicable only when oracle.install.db.config.starterdb.storageType=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=

#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username.
#
#  Example   : MYORACLESUPPORT_USERNAME=br8dba@oracle.com
#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password.
#
# Example    : MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=

#------------------------------------------------------------------------------
# Specify whether to enable the user to set the password for
# My Oracle Support credentials. The value can be either true or false.
# If left blank it will be assumed to be false.
#
# Example    : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

#------------------------------------------------------------------------------
# Specify whether user doesn't want to configure Security Updates.
# The value for this variable should be true if you don't want to configure
# Security Updates, false otherwise.
#
# The value can be either true or false. If left blank it will be assumed
# to be true.
#
# Example    : DECLINE_SECURITY_UPDATES=false
#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true

#------------------------------------------------------------------------------
# Specify the Proxy server name. Length should be greater than zero.
#
# Example    : PROXY_HOST=proxy.domain.com
#------------------------------------------------------------------------------
PROXY_HOST=

#------------------------------------------------------------------------------
# Specify the proxy port number. Should be Numeric and at least 2 chars.
#
# Example    : PROXY_PORT=25
#------------------------------------------------------------------------------
PROXY_PORT=

#------------------------------------------------------------------------------
# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_USER=username
#------------------------------------------------------------------------------
PROXY_USER=

#------------------------------------------------------------------------------
# Specify the proxy password. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_PWD=password
#------------------------------------------------------------------------------
PROXY_PWD=

#------------------------------------------------------------------------------
# Specify the Oracle Support Hub URL.
#
# Example    : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/
#------------------------------------------------------------------------------
COLLECTOR_SUPPORTHUB_URL=
[oracle@localhost response]$


5. Install oracle software in Silent mode

[oracle@localhost database]$ ./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /home/oracle/database/response/db_install.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 5706 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 5837 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-01-02_08-53-55AM. Please wait ...[oracle@localhost database]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2018-01-02_08-53-55AM.log

Prepare in progress.
..................................................   8% Done.

Prepare successful.

Copy files in progress.
..................................................   17% Done.
..................................................   22% Done.
..................................................   27% Done.
..................................................   32% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..............................
Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The installation of Oracle Database 12c was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2018-01-02_08-53-55AM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /home/oracle/product/12.2/db_1/root.sh



..................................................   100% Done.
Successfully Setup Software.



open another terminal as root and then execute
# /home/oracle/product/12.2/db_1/root.sh

[root@localhost ~]# /home/oracle/product/12.2/db_1/root.sh  <-----
Check /home/oracle/product/12.2/db_1/install/root_localhost.localdomain_2018-01-02_09-08-52-116627703.log for the output of root script
[root@localhost ~]# more /home/oracle/product/12.2/db_1/install/root_localhost.localdomain_2018-01-02_09-08-52-116627703.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /home/oracle/product/12.2/db_1
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA) is available at : /home/oracle/product/12.2/db_1/suptools/tfa/release/tfa_home/bin/tfactl
[root@localhost ~]#


Once the root scripts are run as instructed, the installation is complete.

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

12.2 Active Dataguard in CDB on Non-ASM

Creating Physical Standby Active DataGuard Configuration in 12.2.0.1 Container Database

AIM: Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command on 12.2 Database in CDB/PDB environment (No need to take backup of primary database)

Contents

___________________________________________________________________________________________________

0. Introduction
1. Environment
2. Enable Forced Logging on Primary
3. Check Password File on Primary
4. Configure a Standby Redo Log on Primary
5. Verify Archive Mode Enabled on Primary
6. Set Primary Database Initialization Parameters
7. Transfer PFILE AND PASSWORD FILE TO STANDBY SIDE
8. Prepare an Initialization Parameter File for the Standby Database on Standby
9. Crete required directories on Standby
10. Add below entry in ORATAB on Standby
11. Create spfile from pfile on Standby
12. Configure TNS Entries on Primary
13. Configure TNS Entries on Standby
14. Verify connection ‘AS SYSDBA’ from Primary
15. Create Standby Database using duplicate command
16. Enable MRP on Standby
17. Open PDB in READ-ONLY Mode
18. Verify the log shipping Is Performing Properly
19. Verify the Physical Standby Database Is Performing Properly
________________________________________________________________________________________________


0. Introduction

PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files.

Definition of Active Dataguard:

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.


1. Environment

Source:

		Platform		: Linuxx86_64
		Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version		: Oracle 12.2.0.1, File system: Normal
		CDB Name		: CDB1
		PDB Name		: PDB1
                DB_UNIQUE_NAME          : CDB1
		Flashback		: Disabled
		Oracle Home Path        : /u01/app/oracle/product/12.2.0.1
		
Target:
		
		Platform		: Linuxx86_64
		Server Name		: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version		: Oracle 12.2.0.1, File system: Normal
		CDB Name		: CDB1
		PDB Name		: PDB1
		DB_UNIQUE_NAME          : CDB1_DG
                Flashback		: Disabled
		Oracle Home Path        : /u01/app/oracle/product/12.2.0.1


2. Enable Forced Logging on Primary

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES <----

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>

SQL> col name for a30
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ WRITE          1  931007404
PDB$SEED                       READ ONLY           2  302211347
PDB1                           READ WRITE          3   19275046

SQL>

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES  <----

SQL>


3. Check Password File on Primary

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwCDB1
-rw-r-----. 1 oracle dba 3584 Oct 13  2017 orapwCDB1
[oracle@rac1 dbs]$


4. Configure a Standby Redo Log on Primary

Note : 

-- Since we have 3 online redo log file groups, we need to create 4(3+1) Standby redo log file groups
-- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection.


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 /u01/app/oracle/oradata/CDB1/redo03.log                       209715200
         1          2 /u01/app/oracle/oradata/CDB1/redo02.log                       209715200
         1          1 /u01/app/oracle/oradata/CDB1/redo01.log                       209715200

SQL>


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/CDB1/redo04.log') SIZE 200M; <---

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/CDB1/redo05.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/CDB1/redo06.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/CDB1/redo07.log') SIZE 200M;

Database altered.

SQL>


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         3         ONLINE  /u01/app/oracle/oradata/CDB1/redo03.log                      NO           0
         2         ONLINE  /u01/app/oracle/oradata/CDB1/redo02.log                      NO           0
         1         ONLINE  /u01/app/oracle/oradata/CDB1/redo01.log                      NO           0
         4         STANDBY /u01/app/oracle/oradata/CDB1/redo04.log                      NO           0
         5         STANDBY /u01/app/oracle/oradata/CDB1/redo05.log                      NO           0
         6         STANDBY /u01/app/oracle/oradata/CDB1/redo06.log                      NO           0
         7         STANDBY /u01/app/oracle/oradata/CDB1/redo07.log                      NO           0

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                            BYTES
---------- ------------------------------------------------------------ ----------
         4 /u01/app/oracle/oradata/CDB1/redo04.log                        209715200
         5 /u01/app/oracle/oradata/CDB1/redo05.log                        209715200
         6 /u01/app/oracle/oradata/CDB1/redo06.log                        209715200
         7 /u01/app/oracle/oradata/CDB1/redo07.log                        209715200

SQL>


5. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch/CDB1
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL>


6. Set Primary Database Initialization Parameters

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileapac.ora
SQL> create pfile='/home/oracle/initCDB1_before.ora' from spfile;

File created.

SQL> alter system set db_unique_name='CDB1' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1,CDB1_DG)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/arch/CDB1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1' scope=both;

System altered.

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

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=CDB1 scope=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=CDB1_DG scope=both;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CDB1_DG','/u01/app/oracle/oradata/CDB1','/u01/app/oracle/oradata/CDB1_DG/PDB1','/u01/app/oracle/oradata/CDB1/PDB1' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CDB1_DG','/u01/app/oracle/oradata/CDB1' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> create pfile='/home/oracle/initCDB1.ora' from spfile;

File created.

SQL>

[oracle@rac1 ~]$ cat initCDB1.ora
CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=1291845632
CDB1.__inmemory_ext_roarea=0
CDB1.__inmemory_ext_rwarea=0
CDB1.__java_pool_size=16777216
CDB1.__large_pool_size=33554432
CDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=620756992
CDB1.__sga_target=1845493760
CDB1.__shared_io_pool_size=100663296
CDB1.__shared_pool_size=385875968
CDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/CDB1/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/CDB1/control01.ctl','/u01/app/oracle/oradata/CDB1/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/CDB1_DG','/u01/app/oracle/oradata/CDB1','/u01/app/oracle/oradata/CDB1_DG/PDB1','/u01/app/oracle/oradata/CDB1/PDB1'
*.db_name='CDB1'
*.db_unique_name='CDB1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1XDB)'
*.enable_pluggable_database=true
*.fal_client='CDB1'
*.fal_server='CDB1_DG'
*.local_listener='LISTENER_CDB1'
*.log_archive_config='DG_CONFIG=(CDB1,CDB1_DG)'
*.log_archive_dest_1='LOCATION=/u02/arch/CDB1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1'
*.log_archive_dest_2='SERVICE=CDB1_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/CDB1_DG','/u01/app/oracle/oradata/CDB1'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=583m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1746m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$


7. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE

Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 ~]$ scp initCDB1.ora oracle@rac2:/home/oracle/initCDB1_DG.ora
The authenticity of host 'rac2 (192.168.2.102)' can't be established.
RSA key fingerprint is f3:d9:d8:f4:f0:bb:8b:15:34:86:cf:b5:85:fe:06:48.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac2,192.168.2.102' (RSA) to the list of known hosts.
oracle@rac2's password:
initCDB1.ora                                                                               100% 1769     1.7KB/s   00:00
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ scp orapwCDB1 oracle@rac2:/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1_DG
oracle@rac2's password:
orapwCDB1                                                                                  100% 3584     3.5KB/s   00:00
[oracle@rac1 dbs]$


8. Prepare an Initialization Parameter File for the Standby Database on Standby

Edit shown highlighted to set it up for the standby role

[oracle@rac2 ~]$ cat initCDB1_DG_DG.ora
CDB1_DG.__data_transfer_cache_size=0
CDB1_DG.__db_cache_size=1291845632
CDB1_DG.__inmemory_ext_roarea=0
CDB1_DG.__inmemory_ext_rwarea=0
CDB1_DG.__java_pool_size=16777216
CDB1_DG.__large_pool_size=33554432
CDB1_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
CDB1_DG.__pga_aggregate_target=620756992
CDB1_DG.__sga_target=1845493760
CDB1_DG.__shared_io_pool_size=100663296
CDB1_DG.__shared_pool_size=385875968
CDB1_DG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/CDB1_DG/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/CDB1_DG/control01.ctl','/u01/app/oracle/oradata/CDB1_DG/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/CDB1','/u01/app/oracle/oradata/CDB1_DG','/u01/app/oracle/oradata/CDB1/PDB1','/u01/app/oracle/oradata/CDB1_DG/PDB1'
*.db_name='CDB1'
*.db_unique_name='CDB1_DG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1_DGXDB)'
*.enable_pluggable_database=true
*.fal_client='CDB1_DG'
*.fal_server='CDB1'
*.local_listener='LISTENER_CDB1_DG'
*.log_archive_config='DG_CONFIG=(CDB1,CDB1_DG)'
*.log_archive_dest_1='LOCATION=/u02/arch/CDB1_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1_DG'
*.log_archive_dest_2='SERVICE=CDB1_DG_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DG_DG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/CDB1','/u01/app/oracle/oradata/CDB1_DG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=583m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1746m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$


9. Crete required directories on Standby

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/CDB1_DG/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/CDB1_DG
[oracle@rac2 ~]$ mkdir -p /u02/arch/CDB1_DG
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/CDB1_DG/PDB1


10. Add below entry in ORATAB on Standby

CDB1_DG:/u01/app/oracle/product/12.2.0.1:N


11. Create spfile from pfile on Standby

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [CDB1] ? CDB1_DG
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 20:00:53 2018

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initCDB1_DG.ora';
ORACLE instance started.

Total System Global Area 1845493760 bytes
Fixed Size                  8793976 bytes
Variable Size             553648264 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7983104 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initCDB1_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 1845493760 bytes
Fixed Size                  8793976 bytes
Variable Size             553648264 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7983104 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.2.0
                                                 .1/dbs/spfileCDB1_DG.ora
SQL>


12. Configure TNS Entries on Primary

[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.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[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_CDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

CDB1_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1_DG)(UR = A)
    )
  )

[oracle@rac1 admin]$


[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:16:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-JUL-2018 20:43:58
Uptime                    0 days 0 hr. 32 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1.rajasekhar.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "5b70bb17b6741d68e055000000000001" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$

[oracle@rac1 admin]$ tnsping CDB1

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:22:20

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1)))
OK (0 msec)
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping CDB1_DG

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:28:23

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
OK (0 msec)
[oracle@rac1 admin]$


13. Configure TNS Entries on Standby

[oracle@rac2 ~]$ cd /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.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@rac2 admin]$


[oracle@rac2 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_CDB1_DG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

CDB1_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1_DG)(UR = A)
    )
  )
  
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )

[oracle@rac2 admin]$

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


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1845493760 bytes
Fixed Size                  8793976 bytes
Variable Size             553648264 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7983104 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:37:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-JUL-2018 21:36:02
Uptime                    0 days 0 hr. 1 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "CDB1_DG" has 1 instance(s).
  Instance "CDB1_DG", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$

[oracle@rac2 ~]$ tnsping CDB1

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:38:14

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping CDB1_DG

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 21:38:22

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB1_DG)(UR = A)))
OK (0 msec)
[oracle@rac2 ~]$


14. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus sys/sys@CDB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:38:59 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 ~]$
[oracle@rac1 ~]$ sqlplus sys/sys@CDB1_DG as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:39:21 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 ~]$


15. Create Standby Database using duplicate command

On Primary:

[oracle@rac1 ~]$ rman target sys/sys@CDB1 auxiliary sys/sys@CDB1_DG

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jul 17 21:49:31 2018

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

connected to target database: CDB1 (DBID=931007404)
connected to auxiliary database: CDB1 (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 17-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1' auxiliary format
 '/u01/app/oracle/product/12.2.0.1/dbs/orapwCDB1_DG'   ;
}
executing Memory Script

Starting backup at 17-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=297 device type=DISK
Finished backup at 17-JUL-18

contents of Memory Script:
{
   restore clone from service  'CDB1' standby controlfile;
}
executing Memory Script

Starting restore at 17-JUL-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/CDB1_DG/control01.ctl
output file name=/u01/app/oracle/oradata/CDB1_DG/control02.ctl
Finished restore at 17-JUL-18

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/CDB1_DG/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/CDB1_DG/pdbseed/temp012017-10-13_17-53-30-404-PM.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/CDB1_DG/PDB1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/CDB1_DG/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/CDB1_DG/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/CDB1_DG/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/CDB1_DG/PDB1/system01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf";
   restore
   from  nonsparse   from service
 'CDB1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/CDB1_DG/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/temp012017-10-13_17-53-30-404-PM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/CDB1_DG/PDB1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-JUL-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CDB1_DG/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CDB1_DG/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1_DG/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-JUL-18

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=981755498 file name=/u01/app/oracle/oradata/CDB1_DG/PDB1/users01.dbf
Finished Duplicate Db at 17-JUL-18

RMAN> exit

Recovery Manager complete.
[oracle@rac1 ~]$


16. Enable MRP on Standby

[oracle@rac2 CDB1_DG]$ . oraenv
ORACLE_SID = [CDB1_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 CDB1_DG]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:53:31 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> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
CDB1      MOUNTED              PHYSICAL STANDBY YES

SQL>


SQL> alter database open;

Database altered.

SQL>
SQL> !ps -ef | grep mrp
oracle   15074 14602  0 21:56 pts/0    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle   15076 15074  0 21:56 pts/0    00:00:00 grep mrp

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
CDB1      READ ONLY WITH APPLY PHYSICAL STANDBY YES

SQL> col name for a30
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ ONLY           1  931007404
PDB$SEED                       READ ONLY           2  302211347
PDB1                           MOUNTED             3   19275046

SQL>

SQL> !ps -ef | grep mrp
oracle   15080     1  0 21:56 ?        00:00:00 ora_mrp0_CDB1_DG <----
oracle   15091 14602  0 21:57 pts/0    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle   15093 15091  0 21:57 pts/0    00:00:00 grep mrp

SQL>


17. Open PDB in READ-ONLY Mode

SQL> alter pluggable database all open read only;

Pluggable database altered.

SQL>
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ ONLY           1  931007404
PDB$SEED                       READ ONLY           2  302211347
PDB1                           READ ONLY           3   19275046

SQL>


18. Verify the log shipping Is Performing Properly

On Primary

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show parameters db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      CDB1
SQL>
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         9 12-JUL-18 17-JUL-18
        10 17-JUL-18 17-JUL-18
        11 17-JUL-18 17-JUL-18
        12 17-JUL-18 17-JUL-18
        12 17-JUL-18 17-JUL-18
        13 17-JUL-18 17-JUL-18
        13 17-JUL-18 17-JUL-18
        14 17-JUL-18 17-JUL-18
        14 17-JUL-18 17-JUL-18
        15 17-JUL-18 17-JUL-18
        15 17-JUL-18 17-JUL-18

11 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            18  <-----

SQL>

On Standby

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         3         ONLINE  /u01/app/oracle/oradata/CDB1_DG/redo03.log                   NO           0
         2         ONLINE  /u01/app/oracle/oradata/CDB1_DG/redo02.log                   NO           0
         1         ONLINE  /u01/app/oracle/oradata/CDB1_DG/redo01.log                   NO           0
         4         STANDBY /u01/app/oracle/oradata/CDB1_DG/redo04.log                   NO           0
         5         STANDBY /u01/app/oracle/oradata/CDB1_DG/redo05.log                   NO           0
         6         STANDBY /u01/app/oracle/oradata/CDB1_DG/redo06.log                   NO           0
         7         STANDBY /u01/app/oracle/oradata/CDB1_DG/redo07.log                   NO           0

7 rows selected.

SQL>

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
        12 17-JUL-18 17-JUL-18
        13 17-JUL-18 17-JUL-18
        14 17-JUL-18 17-JUL-18
        15 17-JUL-18 17-JUL-18
        16 17-JUL-18 17-JUL-18
        17 17-JUL-18 17-JUL-18
        18 17-JUL-18 17-JUL-18

7 rows selected.

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            18 <----

SQL>


19. Verify the Physical Standby Database Is Performing Properly

The Pluggable database PDB1 has been opened in READ ONLY mode, but the Container Database is running as an Active Standby database and applying changes as they are received from the primary even when the Standby Container database and all the associated pluggable databases have been opened in read only mode. This means Active Dataguard is working..

On Primary

SQL> alter session set container=pdb1;

Session altered.

SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to raj;

Grant succeeded.

SQL> alter user raj quota unlimited on users;

User altered.

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

[oracle@rac1 ~]$ sqlplus raj/raj@pdb1  <----

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 22:19:10 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> show user
USER is "RAJ"
SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL> insert into sugi values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('TEJU','DBA')

1 row created.

SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('VIJAY','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn sys/sys@pdb1 as sysdba
Connected.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> conn sys/sys@cdb1 as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            21 <----

SQL>

SQL> select dest_id,destination,status from v$archive_dest_status where dest_id < 3; DEST_ID DESTINATION STATUS ---------- ------------------------- --------- 1 /u02/arch/CDB1 VALID 2 CDB1_DG VALID SQL>

On Standby

[oracle@rac2 admin]$ tnsping pdb1

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-JUL-2018 10:10:06

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1)))
OK (10 msec)
[oracle@rac2 admin]$

[oracle@rac2 ~]$ sqlplus raj/raj@pdb1 <----

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 22:27:42 2018

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

Last Successful login time: Tue Jul 17 2018 22:19:10 +02:00

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

SQL> select * from sugi;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL>

SQL> conn sys/sys@cdb1_dg as sysdba
Connected.
SQL> select process,status,thread#,sequence#,blocks from v$managed_standby where process like '%MRP%';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCKS
--------- ------------ ---------- ---------- ----------
MRP0      WAIT_FOR_LOG          1         22          0

SQL>

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            21 <---

SQL>

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

ROLLBACK RU from DATABASE 12.2

ROLLBACK DATABASE RELEASE UPDATE from Oracle Database 12.2.0.1

Goal: We are going to ROLLBACK DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830) from 12.2 Oracle Database Home

Contents
___________________________________________________________________________________________________

1. Environment
2. Check existing patches
3. Shutdown ALL PDBs and CDB
4. Stop LISTENER which is running from Oracle Database Home
5. Rollback patch
6. List Patches
7. Startup CDB and ALL PDBs
8. Startup LISTENER
9. Rollback SQL changes (datapatch)
10. Check Registry Status (dba_registry_sqlpatch)
____________________________________________________________________________________________________


1. Environment

Platform   : Linuxx86_64
Server IP  : 192.168.2.101
DB Version : Oracle 12.2.0.1.0, File system: Normal, RAC: Non-RAC
CDB Name   : CDB2
PDB Name   : PDB1,PDB2,PDB3,PDB4
Oracle Home: /u01/app/oracle/product/12.2.0.1


2. Check existing patches

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1 | grep -i "Database Release Update"
Patch description:  "DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)"
[oracle@localhost ~]$

[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh  /u01/app/oracle/product/12.2.0.1
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_16-16-43PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_16-16-43PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  26123830     : applied on Mon Jul 16 16:13:29 CEST 2018
Unique Patch ID:  21405588
Patch description:  "DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)"
   Created on 7 Jul 2017, 00:33:59 hrs PST8PDT
   Bugs fixed:
     23026585, 24336249, 24929210, 24942749, 25036474, 25110233, 25410877
     25417050, 25427662, 25459958, 25547901, 25569149, 25600342, 25600421
     25606091, 25655390, 25662088, 24385983, 24923215, 25099758, 25429959
     25662101, 25728085, 25823754, 22594071, 23665623, 23749454, 24326846
     24334708, 24560906, 24573817, 24578797, 24609996, 24624166, 24668398
     24674955, 24744686, 24811725, 24827228, 24831514, 24908321, 24976007
     25184555, 25210499, 25211628, 25223839, 25262869, 25316758, 25337332
     25455795, 25457409, 25539063, 25546608, 25612095, 25643931, 25410017
     22729345, 24485174, 24509056, 24714096, 25329664, 25410180, 25607726
     25957038, 25973152, 26024732, 24376878, 24589590, 24676172, 23548817
     24796092, 24907917, 25044977, 25736747, 25766822, 25856821, 25051628
     24534401, 24835919, 25050160, 25395696, 25430120, 25616359, 25715167
     25967985



--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@localhost 26123830]$


3. Shutdown ALL PDBs and CDB

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [CDB2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 20:19:28 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> show con_name

CON_NAME
------------------------------
CDB$ROOT <-----
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;

Pluggable database altered.

SQL>
SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep pmon
oracle   26328  3363  0 20:20 pts/0    00:00:00 grep pmon
[oracle@localhost ~]$


4. Stop LISTENER which is running from Oracle Database Home

[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   21278     1  0 18:47 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   26361  3363  0 20:22 pts/0    00:00:00 grep tns
[oracle@localhost ~]$
[oracle@localhost ~]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:22:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   26365  3363  0 20:22 pts/0    00:00:00 grep tns
[oracle@localhost ~]$


5. Rollback patch

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch rollback -id 26123830 -oh /u01/app/oracle/product/12.2.0.1 -local
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-26-34PM_1.log


Patches will be rolled back in the following order:
   26123830
The following patch(es) will be rolled back: 26123830

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0.1')


Is the local system ready for patching? [y|n]
y <----
User Responded with: Y

Rolling back patch 26123830...

RollbackSession rolling back interim patch '26123830' from OH '/u01/app/oracle/product/12.2.0.1'

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...
Deleting "kgcs.o" from archive "/u01/app/oracle/product/12.2.0.1/lib/libgeneric12.a"

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...
RollbackSession removing interim patch '26123830' from inventory
Log file location: /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-26-34PM_1.log

OPatch succeeded.
[oracle@localhost ~]$


6. List Patches

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1 | grep -i "Database Release Update"
[oracle@localhost ~]$

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-34-33PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_20-34-33PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@localhost ~]$


7. Startup CDB and ALL PDBs

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [CDB2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 20:37:27 2018

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  8793208 bytes
Variable Size             503317384 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <------
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL>
SQL> col name for a30
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ WRITE          1  680230459
PDB$SEED                       READ ONLY           2 2993936271
PDB1                           READ WRITE          3  627484885
PDB2                           READ WRITE          4  891811039
PDB3                           READ WRITE          5  424568091
PDB4                           READ WRITE          6 2306285303

6 rows selected.

SQL>


8. Startup LISTENER

[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   28991  3363  0 20:42 pts/0    00:00:00 grep tns
[oracle@localhost ~]$
[oracle@localhost ~]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:42:49

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

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

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 20:42:50
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   29004     1  0 20:42 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   29007  3363  0 20:43 pts/0    00:00:00 grep tns
[oracle@localhost ~]$

[oracle@localhost ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:44:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 20:42:50
Uptime                    0 days 0 hr. 1 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5511))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5510))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "6f205af172040e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172050e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172060e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f2c146104472ecee055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2XDB" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb4" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

9. Rollback SQL changes (datapatch)

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Mon Jul 16 20:48:13 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29106_2018_07_16_20_48_13/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  Not installed in the binary registry and ID 170718 in PDB CDB$ROOT, ID 170718 in PDB PDB$SEED, ID 170718 in PDB PDB1, ID 170718 in PDB PDB2, ID 170718 in PDB PDB3, ID 170718 in PDB PDB4

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2 PDB3 PDB4
    The following patches will be rolled back:
      26123830 (DATABASE RELEASE UPDATE 12.2.0.1.170718)
    Nothing to apply

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...
Patch 26123830 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_CDBROOT_2018Jul16_20_50_01.log (no errors)
Patch 26123830 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDBSEED_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB1): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB1_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB2_2018Jul16_20_52_38.log (no errors)
Patch 26123830 rollback (pdb PDB3): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB3_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB4): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB4_2018Jul16_20_57_05.log (no errors)
SQL Patching tool complete on Mon Jul 16 20:58:33 2018
[oracle@localhost ~]$


10. Check Registry Status

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 21:00:20 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> show con_name

CON_NAME
------------------------------
CDB$ROOT <------
SQL>

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000

COLUMN action_time FORMAT A25
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4 
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;
 
Output
 
 ACTION_TIME               ACTION     STATUS     DESCRIPTION                              VERSION      PATCH_ID BUND
------------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----
16-JUL-2018 17:11:09      APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718  12.2.0.1     26123830 DBRU
16-JUL-2018 20:58:16      ROLLBACK   SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718  12.2.0.1     26123830 DBRU

SQL>

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

Apply RU on DATABASE 12.2

Applying RU (Release Update) for Oracle Database 12.2.0.1

Goal: We are going to apply 26123830 (DATABASE RELEASE UPDATE 12.2.0.1.170718) on 12.2 Oracle Database Home

Contents
___________________________________________________________________________________________________

1. Environment
2. Introduction
3. Download the patch from MOS (My Oracle Support)
4. Unzip patch
5. List patches which were applied
6. CheckConflict
7. Shutdown ALL PDBs and CDB
7.1 Backup Oracle Home / Inventory
8. Stop LISTENER which is running from Oracle Database Home
9. Apply Patch
10. List Patches
11. Startup CDB and ALL PDBs
12. Apply SQL changes (datapatch)
13. Verify from dba_registry_sqlpatch
14. Start LISTENER
_________________________________________________________________________________________________


1. Environment

Platform   : Linuxx86_64
Server IP  : 192.168.2.101
DB Version : Oracle 12.2.0.1.0, File system: Normal, RAC: Non-RAC
CDB Name   : CDB2
PDB Name   : PDB1,PDB2,PDB3,PDB4
Oracle Home: /u01/app/oracle/product/12.2.0.1


2. Introduction

What is an RU? Release Update (formerly known as Proactive Bundle Patches)

What is RUR? Release Update Revision (formerly known as PSU)


3. Download the patch from MOS


4. Unzip patch

-- Copy the patch to server from local machine using WinSCP or FTP

[oracle@localhost RU]$ pwd
/home/oracle/RU
[oracle@localhost RU]$ unzip p26123830_122010_Linux-x86-64.zip

[oracle@localhost RU]$ ls -lrth
total 40M
drwxrwxr-x. 4 oracle dba 4.0K Jul  7  2017 26123830
-rw-rw-r--. 1 oracle dba  17K Aug  7  2017 PatchSearch.xml
-rw-r--r--. 1 oracle dba  40M Jul 16 14:55 p26123830_122010_Linux-x86-64.zip
[oracle@localhost RU]$


5. List patches which were applied

[oracle@localhost ~]$ cd /u01/app/oracle/product/12.2.0.1/OPatch/
[oracle@localhost OPatch]$ ./opatch lsinv
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_15-05-18PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_15-05-18PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@localhost OPatch]$


6. CheckConflict

[oracle@localhost RU]$ pwd
/home/oracle/RU
[oracle@localhost RU]$ cd 26123830
[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /home/oracle/RU/26123830
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_22-14-05PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed. <-----

OPatch succeeded.
[oracle@localhost 26123830]$


7. Shutdown ALL PDBs and CDB

[oracle@localhost 26123830]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 16:08:35 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> show con_name

CON_NAME
------------------------------
CDB$ROOT <----
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost 26123830]$
[oracle@localhost 26123830]$ ps -ef | grep pmon
oracle   11763  3363  0 16:10 pts/0    00:00:00 grep pmon
[oracle@localhost 26123830]$


7.1 Backup Oracle Home / Inventory

-- Take Backup of below for safer side

Oracle Inventory

[oracle@localhost 26123830]$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=dba
[oracle@localhost 26123830]$

[oracle@localhost ~]$ cd /u01/app/
[oracle@localhost app]$ ls -ltrh
total 8.0K
drwxr-xr-x. 10 oracle dba 4.0K Jan 13  2017 oracle
drwxrwx---.  6 oracle dba 4.0K Jul 16 16:15 oraInventory
[oracle@localhost app]$

tar -czvf /u01/stage/Backup_OraInv.tar oraInventory | gzip > /u01/stage/Backup_OraInv.tar.gz


Oracle Database Home

[oracle@localhost app]$ cd /u01/app/oracle/product
[oracle@localhost product]$ ls -lrth

drwxr-xr-x. 72 oracle dba 4.0K Oct 21  2016 12.1.0.2
[oracle@localhost product]$

tar -czvf /u01/stage/Backup_Soft_12.2.tar 12.2.0.1 | gzip > /u01/stage/Backup_Soft_12.2.tar.gz


8. Stop LISTENER which is running from Oracle Database Home

[oracle@localhost 26123830]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle    2958     1  0 13:46 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   11767  3363  0 16:10 pts/0    00:00:00 grep tns
[oracle@localhost 26123830]$
[oracle@localhost 26123830]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 16:10:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost 26123830]$
[oracle@localhost 26123830]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   11775  3363  0 16:11 pts/0    00:00:00 grep tns
[oracle@localhost 26123830]$


9. Apply Patch

[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0.1 -local
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_16-12-25PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   26123830

Do you want to proceed? [y|n]
y <-----
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0.1')


Is the local system ready for patching? [y|n]
y  <------
User Responded with: Y
Backing up files...
Applying interim patch '26123830' to OH '/u01/app/oracle/product/12.2.0.1'

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...
Patch 26123830 successfully applied.
Log file location: /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_16-12-25PM_1.log

OPatch succeeded.
[oracle@localhost 26123830]$


10. List Patches

[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh  /u01/app/oracle/product/12.2.0.1
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_16-16-43PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_16-16-43PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  26123830     : applied on Mon Jul 16 16:13:29 CEST 2018
Unique Patch ID:  21405588
Patch description:  "DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)"
   Created on 7 Jul 2017, 00:33:59 hrs PST8PDT
   Bugs fixed:
     23026585, 24336249, 24929210, 24942749, 25036474, 25110233, 25410877
     25417050, 25427662, 25459958, 25547901, 25569149, 25600342, 25600421
     25606091, 25655390, 25662088, 24385983, 24923215, 25099758, 25429959
     25662101, 25728085, 25823754, 22594071, 23665623, 23749454, 24326846
     24334708, 24560906, 24573817, 24578797, 24609996, 24624166, 24668398
     24674955, 24744686, 24811725, 24827228, 24831514, 24908321, 24976007
     25184555, 25210499, 25211628, 25223839, 25262869, 25316758, 25337332
     25455795, 25457409, 25539063, 25546608, 25612095, 25643931, 25410017
     22729345, 24485174, 24509056, 24714096, 25329664, 25410180, 25607726
     25957038, 25973152, 26024732, 24376878, 24589590, 24676172, 23548817
     24796092, 24907917, 25044977, 25736747, 25766822, 25856821, 25051628
     24534401, 24835919, 25050160, 25395696, 25430120, 25616359, 25715167
     25967985



--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@localhost 26123830]$


11. Startup CDB and ALL PDBs

[oracle@localhost 26123830]$ . oraenv
ORACLE_SID = [CDB2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost 26123830]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 16:59:14 2018

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  8793208 bytes
Variable Size             503317384 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <-----
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL>
SQL> col name for a30
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ WRITE          1  680230459
PDB$SEED                       READ ONLY           2 2993936271
PDB1                           READ WRITE          3  627484885
PDB2                           READ WRITE          4  891811039
PDB3                           READ WRITE          5  424568091
PDB4                           READ WRITE          6 2306285303

6 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost 26123830]$


12. Apply SQL changes (datapatch)

[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Mon Jul 16 17:03:21 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15649_2018_07_16_17_03_21/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 170718 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2 PDB3 PDB4
    Nothing to roll back
    The following patches will be applied:
      26123830 (DATABASE RELEASE UPDATE 12.2.0.1.170718)

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...
Patch 26123830 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_CDBROOT_2018Jul16_17_04_18.log (no errors)
Patch 26123830 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_PDBSEED_2018Jul16_17_06_59.log (no errors)
Patch 26123830 apply (pdb PDB1): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_PDB1_2018Jul16_17_06_59.log (no errors)
Patch 26123830 apply (pdb PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_PDB2_2018Jul16_17_07_00.log (no errors)
Patch 26123830 apply (pdb PDB3): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_PDB3_2018Jul16_17_06_59.log (no errors)
Patch 26123830 apply (pdb PDB4): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_apply_CDB2_PDB4_2018Jul16_17_09_43.log (no errors)
SQL Patching tool complete on Mon Jul 16 17:11:28 2018
[oracle@localhost 26123830]$


13. Verify from dba_registry_sqlpatch

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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <-----
SQL>

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000

COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4 
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;


ACTION_TIME  ACTION     STATUS     DESCRIPTION                              VERSION      PATCH_ID BUND
------------ ---------- ---------- ---------------------------------------- ---------- ---------- ----
2018-07-16   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718  12.2.0.1     26123830 DBRU

SQL>


14. Start LISTENER

[oracle@localhost 26123830]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   21274  3363  0 18:47 pts/0    00:00:00 grep tns
[oracle@localhost 26123830]$
[oracle@localhost 26123830]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 18:47:50

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

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

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 18:47:50
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@localhost 26123830]$
[oracle@localhost 26123830]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 18:47:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 18:47:50
Uptime                    0 days 0 hr. 0 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5511))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5510))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "6f205af172040e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172050e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172060e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f2c146104472ecee055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2XDB" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb4" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost 26123830]$

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