Sunday, September 02, 2018

Upgrade Oracle 11.2.0.4 RAC To 12.1.0.2.0 RAC using DBUA and Convert Non - CDB To CDB (PDB) Database


Source Database   : 11.2.0.4
Target Database    : 12.1.0.2.0
Nodes                     : 3 Nodes
File system Type   : ASM
Platform                 : RHEL x86_64

Abstract: 

  • Install Oracle Database software 12.1.0.2.0
  • Take cold backup
  • Pre-upgrade steps
  • Upgrade Database
  • Post-upgrade steps
  • Migrate Listener from 11.2.0.4 to 12.1.0.2.0 (non-CDB)
  • Rename 12c database(non-CDB) after upgrade.
  • Create a new PDB database in 12c
  • Migrate non-CDB database to CDB (new PDB) database
  • Parameter Configuration in New PDB database.
  • Drop (non-CDB) renamed database
  • Listener Configuration


Prerequisites steps:


Download below software from OTN or My support Oracle #
P2141921_121020_platform_1of10.zip
P21419221_121020_platform_2of10.zipInstall Oracle Database software 12.1.0.2.0

  • Install Oracle Database software 12.1.0.2.0









uncheck Node 4 (z08s-temp0d)

Next >>















Oracle Base - /opt/<instance>/product/12.1.0.2
Oracle Home - /opt/<instance>/product/12.1.0.2/rac

Next>>








Next >>



 Ignore All>> As per Oracle, these can be ignored








Install >>





























Root.sh should be run sequentially one after another starting with Node-A

z03temp08a:/root# /opt/racdb1/product/12.1.0.2/rac/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= racdb1
    ORACLE_HOME=  /opt/racdb1/product/12.1.0.2/rac

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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.
z03temp08a:/root#

z03temp08b:/root# /opt/racdb1/product/12.1.0.2/rac/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= racdb1
    ORACLE_HOME=  /opt/racdb1/product/12.1.0.2/rac

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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.
z03temp08b:/root#

z03temp08c:/root# /opt/racdb1/product/12.1.0.2/rac/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= racdb1
    ORACLE_HOME=  /opt/racdb1/product/12.1.0.2/rac

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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.
z03temp08c:/root#

























Prerequisites upgrade steps 
#Manual Steps: Need to be executed prior to upgrade [Mandatory]
Enable Archive log
lsnrctl status listener_racdb1
Remove Listener Before Upgrade: [Node A only]

Pre-requisite Checks Prior to Upgrade: [all below steps are run from Node-A]

1.      Run the pre-upgrd.sql from 12.1.0.2 Oracle Home
z03temp08a:/home/racdb1/vvp# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 29 09:06:11 2018

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


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

SQL> @/opt/racdb1/product/12.1.0.2/rac/rdbms/admin/preupgrd.sql


ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /opt/racdb1/product/11.2.0.4/cfgtoollogs/racdb1/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /opt/racdb1/product/11.2.0.4/cfgtoollogs/racdb1/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /opt/racdb1/product/11.2.0.4/cfgtoollogs/racdb1/preupgrade/postupgrade_fixups.sql


Pre-Upgrade Checks in RACDB1 Completed.

SQL> exit

Take Backup of SPFILE

z03temp08a:/home/racdb1/vvp# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 29 10:08:55 2018

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


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

SQL> create pfile='/home/racdb1/vvp/12c/pfile_racdb1_11g.ora' from spfile;

File created.

SQL> exit


-----------------------------------------------------------------------------------------------------------------------------------------------------------------Remove Enterprise Manager Console


z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 29 10:09:55 2018

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


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

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


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

PL/SQL procedure successfully completed.


a   Execute DBMS_STATS

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

b   Unset Hidden Parameters [ take backup of spfile if not already done]

_always_semi_join = OFF
_b_tree_bitmap_plans = FALSE
_gc_defer_time = 0
_no_or_expansion = TRUE
_optimizer_max_permutations = 100
_partition_view_enabled = FALSE

SQL> ALTER SYSTEM RESET "_gc_defer_time" SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM RESET "_always_semi_join" SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM RESET "_b_tree_bitmap_plans" SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM RESET "_no_or_expansion" SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM RESET "_optimizer_max_permutations" SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM RESET "_partition_view_enabled" SCOPE=SPFILE;

System altered.

SQL>
SQL>



Check component status


SQL> set pagesize500
SQL> set linesize 100
SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

COMP_NAME
----------------------------------------------------------------------------------------------------
STATUS                                       VERSION
-------------------------------------------- ----------------------------------------
JServer JAVA Virtual Machine
VALID                                        11.2.0.4.0

OLAP Analytic Workspace
VALID                                        11.2.0.4.0

OLAP Catalog
VALID                                        11.2.0.4.0

OWB
VALID                                        11.2.0.3.0

Oracle Application Express
VALID                                        3.2.1.00.1

Oracle Database Catalog Views
VALID                                        11.2.0.4.0

Oracle Database Java Packages
VALID                                        11.2.0.4.0

Oracle Database Packages and Types
VALID                                        11.2.0.4.0

Oracle Expression Filter
VALID                                        11.2.0.4.0

Oracle Multimedia
VALID                                        11.2.0.4.0

Oracle OLAP API
VALID                                        11.2.0.4.0

Oracle Real Application Clusters
VALID                                        11.2.0.4.0

Oracle Rules Manager
VALID                                        11.2.0.4.0

Oracle Text
VALID                                        11.2.0.4.0

Oracle Workspace Manager
VALID                                        11.2.0.4.0

Oracle XDK
VALID                                        11.2.0.4.0

Oracle XML Database
VALID                                        11.2.0.4.0

Spatial
VALID                                        11.2.0.4.0


18 rows selected.

SQL>


     Compile Invalid Objects

SQL> @utlrp.sql


Total counts:

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

  COUNT(*)
----------
        24

SQL>

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# sqlplus / as sysdba

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            6106909344 bytes
Database Buffers         4160749568 bytes
Redo Buffers              185720832 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     48207
Next log sequence to archive   48208
Current log sequence           48208
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl start database -d racdb1

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl config database -d racdb1
Database unique name: racdb1
Database name: racdb1
Oracle home: /opt/racdb1/product/11.2.0.4/rac
Oracle user: racdb1
Spfile: +RACDB1_DB/racdb1/spfileracdb1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racdb1
Database instances: racdb11,racdb12,racdb13
Disk Groups: RACDB1_DB,RACDB1_FLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#


LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JAN-2018 10:49:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RACDB1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RACDB1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-JAN-2018 21:39:49
Uptime                    10 days 13 hr. 9 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/racdb1/product/11.2.0.4/rac/network/admin/listener.ora
Listener Log File         /opt/racdb1/product/11.2.0.4/diag/tnslsnr/z03temp08a/listener_racdb1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RACDB1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1001)))
Services Summary...
Service "racdb1" has 1 instance(s).
  Instance "racdb11", status READY, has 1 handler(s) for this service...
Service "racdb1XDB" has 1 instance(s).
  Instance "racdb11", status READY, has 1 handler(s) for this service...
The command completed successfully
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# sqlplus / as sysdba

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

SQL> show parameter listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER_RACDB11
remote_listener                      string      RACDB1, TEMP08-DB:1500
SQL>


z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# ps -fu `whoami` | grep -v grep | grep lsnr
racdb1   31616      1  0 Jan18 ?        00:01:40 /opt/racdb1/product/11.2.0.4/rac/bin/tnslsnr LISTENER_RACDB1 -inherit
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl status listener -l LISTENER_`whoami`
Listener LISTENER_RACDB1 is enabled
Listener LISTENER_RACDB1 is running on node(s): z03temp08a,z03temp08b,z03temp08c
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#


z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl config listener -l LISTENER_`whoami` -a
Name: LISTENER_RACDB1
Network: 1, Owner: racdb1
Home: /opt/racdb1/product/11.2.0.4/rac
End points: TCP:1001
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#


z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl getenv listener -l LISTENER_`whoami`
LISTENER_RACDB1:
ORACLE_BASE=/opt/racdb1/product/11.2.0.4

Check status for listener from ALL nodes


z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# lsnrctl status LISTENER_`whoami`

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1001)))
Services Summary...
Service "racdb1" has 1 instance(s).
  Instance "racdb11", status READY, has 1 handler(s) for this service...
Service "racdb1XDB" has 1 instance(s).
  Instance "racdb11", status READY, has 1 handler(s) for this service...
The command completed successfully
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#

z03temp08b:/home/racdb1# lsnrctl status LISTENER_`whoami`

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JAN-2018 10:55:44

Listener Parameter File   /opt/racdb1/product/11.2.0.4/rac/network/admin/listener.ora
Listener Log File         /opt/racdb1/product/11.2.0.4/diag/tnslsnr/z03temp08b/listener_racdb1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RACDB1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1001)))
Services Summary...
Service "racdb1" has 1 instance(s).
  Instance "racdb12", status READY, has 1 handler(s) for this service...
Service "racdb1XDB" has 1 instance(s).
  Instance "racdb12", status READY, has 1 handler(s) for this service...
The command completed successfully
z03temp08b:/home/racdb1#

z03temp08c:/home/racdb1# lsnrctl status LISTENER_`whoami`

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RACDB1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.250)(PORT=1001)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.250)(PORT=1001)))
Services Summary...
Service "racdb1" has 1 instance(s).
  Instance "racdb13", status READY, has 1 handler(s) for this service...
Service "racdb1XDB" has 1 instance(s).
  Instance "racdb13", status READY, has 1 handler(s) for this service...
The command completed successfully
z03temp08c:/home/racdb1#

Remove Listener [Node-A]

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl remove listener -l LISTENER_`whoami` -f
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# ps -fu `whoami` | grep -v grep | grep lsnr
racdb1   31616      1  0 Jan18 ?        00:01:40 /opt/racdb1/product/11.2.0.4/rac/bin/tnslsnr LISTENER_RACDB1 -inherit
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl status listener -l LISTENER_`whoami`
PRCR-1001 : Resource ora.LISTENER_RACDB1.lsnr does not exist

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# lsnrctl status LISTENER_`whoami`


TNS-01101: Could not find service name LISTENER_racdb1

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# lsnrctl status LISTENER_RACDB1

TNS-01101: Could not find service name LISTENER_RACDB1
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin#  ps -fu `whoami` | grep -v grep | grep lsnr
racdb1   31616      1  0 Jan18 ?        00:01:40 /opt/racdb1/product/11.2.0.4/rac/bin/tnslsnr LISTENER_RACDB1 -inherit

Make Sure RACDB1 is not in the list

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/rdbms/admin# srvctl status listener
Listener LISTENER is enabled


Database Upgrade :[ Initiate from Node-A]


1
SQL> EXECUTE dbms_preup.purge_recyclebin_fixup;

PL/SQL procedure successfully completed.

SQL>

3.      Initiate the upgrade

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/bin# export DISPLAY=z03temp08a.zebra.lan:3
z03temp08a:/opt/racdb1/product/12.1.0.2/rac/bin# ./dbua





























Next >>
























Next >>























Next >>




---
Yes
Next >>


Next >>


Next >>

Next>>









































































































Post Upgrade Steps:



z03temp08a:/opt/racdb1/product/11.2.0.4/cfgtoollogs/racdb1/preupgrade# sqlplus / as sysdba


SQL> @postupgrade_fixups.sql

**********************************************************************
Check Tag:     INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary:   Invalid objects are displayed and must be reviewed.
**********************************************************************
Fixup Returned Information:
WARNING: --> Database contains INVALID objects prior to upgrade

     The list of invalid SYS/SYSTEM objects was written to
     registry$sys_inv_objs.
     The list of non-SYS/SYSTEM objects was written to
     registry$nonsys_inv_objs unless there were over 5000.
     Use utluiobj.sql after the upgrade to identify any new invalid
     objects due to the upgrade.
**********************************************************************


**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.2.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************


**********************************************************************
Check Tag:     NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary:   This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action. 
If you want to upgrade those other components, you must do so manually.
**********************************************************************


**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 3 fixup routines generated INFORMATIONAL messages that should be reviewed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.

Re-adding listener back to Grid

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# ls -lrt srvctl
-rwxr-xr-x. 1 racdb1 oinstall 9055 Jan 18  2017 srvctl
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# ./srvctl add listener -l LISTENER_`whoami` -o /opt/racdb1/product/11.2.0.4/rac/ -p TCP:1001 -s
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl setenv listener -l LISTENER_`whoami` -T "ORACLE_BASE=/opt/racdb1/product/11.2.0.4"
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl status listener -l LISTENER_`whoami`
Listener LISTENER_RACDB1 is enabled
Listener LISTENER_RACDB1 is not running
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl config listener -l LISTENER_`whoami` -a
Name: LISTENER_RACDB1
Type: Database Listener
Network: 1, Owner: racdb1
Home: /opt/racdb1/product/11.2.0.4/rac/
End points: TCP:1001
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl getenv listener -l LISTENER_`whoami`  
LISTENER_RACDB1:
ORACLE_BASE=/opt/racdb1/product/11.2.0.4
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl config listener -l LISTENER_`whoami` -a
Name: LISTENER_RACDB1
Type: Database Listener
Network: 1, Owner: racdb1
Home: /opt/racdb1/product/11.2.0.4/rac/
End points: TCP:1001
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl start listener -l LISTENER_`whoami`
experienced an unrecoverable failure
CRS-0267: Human intervention required to resume its availability.
CRS-5020: Not all endpoints are registered for listener LISTENER_RACDB1
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# srvctl status listener -l LISTENER_`whoami`
Listener LISTENER_RACDB1 is enabled
Listener LISTENER_RACDB1 is running on node(s): z03temp08a,z03temp08b,z03temp08c
z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin#

Migrate Listener from 11.2.0.4 Oracle Home to 12.1.0.2 Oracle Home

z03temp08a:/opt/racdb1/product/11.2.0.4/rac/bin# netca
Oracle Net Services Configuration:








































































Create 12c Listener from 12.1.0.2

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/bin# netca










































Database was shutdown 





























Rename Database name racdb1 to rac1db

Create New Database RACDB1

1.      Modify the ASM Compatibility Parameter
z03temp08a:/home/grid# . ./grid.env
z03temp08a:/home/grid# export DISPLAY=z03temp08a.zebra.lan:3
z03temp08a:/home/grid# asmca

Diskgroups-> RACDB1_DB-> Edit Attributes-> 12.1.0.0.0-> OK

z03temp08a:/home/racdb1# srvctl config database -d racdb1


z03temp08a:/home/racdb1# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 30 16:13:44 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +RACDB1_DB/spfilerac1db.ora

SQL> create pfile='/home/racdb1/ROK/12c/rac1db_pfile.ora' from spfile;


File created.







z03temp08a:/home/racdb1# srvctl config database -d racdb1
Database unique name: racdb1
Database name: racdb1
Oracle home: /opt/racdb1/product/12.1.0.2/rac
Oracle user: racdb1
Spfile: +RACDB1_DB/spfilecrp1crm.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RACDB1_DB,RACDB1_FLASH
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group:
Database instances: racdb11,racdb12,racdb13
Configured nodes: 
Database is administrator managed
z03temp08a:/home/racdb1# srvctl remove database -d racdb1
Remove the database racdb1? (y/[n]) y

z03temp08a:/home/racdb1#














































Uncheck Run CVU and Configure Enterprise Manager



Modify the database + flash diskgroup








 Update All-> Apply





















Reference : How to migrate an existing pre-12c database (non-CDB) to 12c CDB database? (Doc ID 1564657.1)


1.     Shutdown the rac1db database

z03temp08a:/home/racdb1# . ./rac1db_12102.env
z03temp08a:/home/racdb1/ROK/12c# srvctl stop database -d rac1db

z03temp08a:/home/racdb1/ROK/12c# srvctl stop database -d rac1db
z03temp08a:/home/racdb1/ROK/12c# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 30 17:05:07 2018

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

Connected to an idle instance.

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 1.0503E+10 bytes
Fixed Size                  7653960 bytes
Variable Size            8254391736 bytes
Database Buffers         2046820352 bytes
Redo Buffers              193671168 bytes
Database mounted.
SQL> alter database open read only;\
  2 
SQL> alter database open read only;

Database altered.

SQL> BEGIN
  2  DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/racdb1/ROK/12c/pdb_racdb1_1.xml');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
z03temp08a:/home/racdb1/ROK/12c# exit
logout
z03temp08a:/home/d2marini# sudo su - racdb1
z03temp08a:/home/racdb1# ls
123.sql     INC0402895.log  create_QA_users.sql  racdb1.env_old  racdb11_z03temp08a.env  racdb1_12102.env  db.rsp        jac    ROK              pfile_racdb1_beforeupgrade.ora  vn.log
INC0367628  afiedt.buf      racdb1.env          racdb11.env     racdb1_11204.env          rac1db_12102.env  filename.pkb  kbace  oradiag_racdb1  rjc
z03temp08a:/home/racdb1# . ./racdb11.env
z03temp08a:/home/racdb1# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 30 17:07:53 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create pfile='/home/racdb1/ROK/12c/racdb1_12c_pfile.ora' from spfile;

File created.

SQL>


SQL> set serveroutput on
SQL> DECLARE
  compatible BOOLEAN := FALSE;
  2    3    BEGIN  
  4    compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5    pdb_descr_file => '/home/racdb1/ROK/12c/pdb_racdb1_1.xml');
  6   if compatible then
  7   DBMS_OUTPUT.PUT_LINE('Is pluggable pdb_racdb1 compatible? YES');
  8   else DBMS_OUTPUT.PUT_LINE('Is pluggable pdb_racdb1 compatible? NO');
  9   end if;
 10   END;
 11   /
Is pluggable pdb_racdb1 compatible? YES

PL/SQL procedure successfully completed.

SQL>



SQL> create pluggable database pdb_racdb1 using '/home/racdb1/ROK/12c/pdb_racdb1_1.xml' nocopy;

Pluggable database created.

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         2 2426174475 2426174475 640702AA75F23EC1E05327004D0ADF92
PDB$SEED                       READ ONLY  NO
30-JAN-18 04.49.09.924 PM -06:00
   1594423  859832320       8192 ENABLED                       0


    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         3 2186757575  161696482 63EFBDB05753BA0EE05327004D0A4FEE
PDB_RACDB1                    MOUNTED
30-JAN-18 05.12.30.752 PM -06:00
1.4755E+13          0       8192 ENABLED                       0


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
z03temp08a:/home/racdb1# cd /opt/racdb1/product/12.1.0.2/rac/rdbms/admin
z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin#

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 30 17:13:22 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> alter session set container=pdb_racdb1;  

Session altered.

SQL> set serveroutput on;                      
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql [this step will take 1.5 HRS]

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 30 18:22:44 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         2 2426174475 2426174475 640702AA75F23EC1E05327004D0ADF92
PDB$SEED                       READ ONLY  NO
30-JAN-18 04.49.09.924 PM -06:00
   1594423  859832320       8192 ENABLED                       0


    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         3 2186757575  161696482 63EFBDB05753BA0EE05327004D0A4FEE
PDB_RACDB1                    MOUNTED
30-JAN-18 06.19.33.263 PM -06:00
1.4755E+13 1.3183E+10       8192 ENABLED                       0


SQL> alter system set open_cursors=5000 scope=spfile;
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=FALSE SCOPE=SPFILE;

System altered.

SQL>
System altered.

SQL> alter system set SESSION_CACHED_CURSORS=200 scope=spfile;
alter system set "_always_semi_join"='OFF' SCOPE=SPFILE;

System altered.

SQL> alter system set "_b_tree_bitmap_plans"=FALSE SCOPE=SPFILE;

System altered.

SQL> alter system set "_partition_view_enabled"=FALSE scope=spfile;
alter system set "_gc_defer_time"=0 SCOPE=SPFILE;

System altered.

SQL> alter system set "_no_or_expansion"=TRUE SCOPE=SPFILE;

System altered.

SQL> alter system set "_optimizer_max_permutations"=100 scope=spfile;

System altered.

SQL> alter system set "_LIKE_WITH_BIND_AS_EQUALITY"=TRUE SCOPE=SPFILE;

System altered.

SQL> alter system set processes =4000 scope=spfile;
alter system set session= 6000 scope=spfile;
alter system set nls_sort='BINARY' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET optimizer_features_enable ='12.1.0.2' SCOPE=SPFILE;
alter system set optimizer_adaptive_features =false scope=spfile;
System altered.

SQL>
System altered.

SQL> alter system set session= 6000 scope=spfile
                 *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>
System altered.

SQL>
System altered.

SQL>

System altered.


SQL> alter system set sessions= 6000 scope=spfile;

System altered.

SQL> show parameter listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 10.77.1.39)(PORT=1521))
remote_listener                      string       TFAB02-DB:1521

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PDB_RACDB1';

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter processes mismatch: Previous 1000 Current 300
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter sessions mismatch: Previous 1584 Current 576
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter nls_sort mismatch: Previous 'BINARY' Current ''
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter memory_target mismatch: Previous 10016M Current 12G
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter compatible mismatch: Previous '11.2.0.0.0' Current '12.1.0.2.0'
RESOLVED

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter cluster_database_instances mismatch: Previous 3 Current 4
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter parallel_max_servers mismatch: Previous 16 Current 160
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter sort_area_size mismatch: Previous 3048576 Current 65536
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter open_cursors mismatch: Previous 5000 Current 300
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter optimizer_index_cost_adj mismatch: Previous 1 Current 100
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter query_rewrite_enabled mismatch: Previous 'FALSE' Current 'TRUE'
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Parameter                                                        WARNING
CDB parameter optimizer_dynamic_sampling mismatch: Previous 1 Current 2
RESOLVED


NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB_RACDB1
Service Name Conflict                                            WARNING
Service name or network name of service racdb1XDB in the PDB is invalid or conf
licts with an existing service name or network name in the CDB.
RESOLVED

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------

PDB_RACDB1
Service Name Conflict                                            WARNING
Service name or network name of service racdb1 in the PDB is invalid or conflic
ts with an existing service name or network name in the CDB.

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
RESOLVED

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

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PENDING

PDB_RACDB1
OPTION                                                           WARNING
Database option DV mismatch: PDB installed version NULL. CDB installed version 1

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
2.1.0.2.0.
PENDING

PDB_RACDB1
OPTION                                                           WARNING

NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
Database option OLS mismatch: PDB installed version NULL. CDB installed version
12.1.0.2.0.
PENDING







Drop RAC1DB database

03s-tfab02a:/home/racdb1# . ./rac1db_12102.env
z03temp08a:/home/racdb1# srvctl config database -d rac1db
Database unique name: rac1db
Database name: rac1db
Oracle home: /opt/racdb1/product/12.1.0.2/rac
Oracle user: racdb1
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RACDB1_DB,RACDB1_FLASH
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group:
Database instances: rac1db1,rac1db2,rac1db3
Configured nodes: z03temp08a,z03temp08b,z03temp08c
Database is administrator managed
z03temp08a:/home/racdb1#

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin# export DISPLAY=z03temp08a.zebra.lan:3
z03temp08a:/opt/racdb1/product/12.1.0.2/rac/rdbms/admin# dbca






























SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB_RACDB1                    READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> alter pluggable database pdb_racdb1 save state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME
--------------------------------------------------------------------------------
INSTANCE_NAME
--------------------------------------------------------------------------------
STATE
--------------
PDB_RACDB1
racdb11
OPEN


SQL>

SQL> alter system set parallel_min_servers=0 scope=spfile;

System altered.

SQL> alter system set parallel_max_servers=16 scope=spfile;

System altered.

SQL> exit

Listener configuration

alter system set local_listener='RACDB11_LOCAL' scope=spfile sid='racdb11'; [node-a]
alter system set local_listener='RACDB12_LOCAL' scope=spfile sid='racdb12'; [node-b]
alter system set local_listener='RACDB13_LOCAL' scope=spfile sid='racdb13'; [node-c]
alter system set remote_listener='racdb1_REMOTE','temp02:1530' scope=spfile sid='*';

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/network/admin# srvctl stop database -d racdb1
z03temp08a:/opt/racdb1/product/12.1.0.2/rac/network/admin# srvctl start database -d racdb1

z03temp08a:/opt/racdb1/product/12.1.0.2/rac/network/admin# lsnrctl status listener_racdb1


The command completed successfully
z03temp08a:/opt/racdb1/product/12.1.0.2/rac/network/admin#

update tnsnames.ora file and then configure scan_listener from grid user.




-
Labels:

Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

Scope - ·        Infrastructure planning and requirement for installation of the multi-node cluster database ·        Prerequisites Software...