12c: Upgrading OPatch Version

In order to install any patch (psu or cpu), first we need to check compatibility of "opatch version". If the opatch version is not compatible, we need to upgrade the opatch version

Steps to Upgrade "opatch version"
step1: Backup the OPatch folder under Oracle_Home as follows 

[admind@tnc63 dbhome_1]$ cp -r OPatch OPatch_backup

Step2: Remove the files in the OPatch directory

[admind@tnc63 dbhome_1]$ cd OPatch

[admind@tnc63 OPatch]$ ls -ltr 

total 164

-rw-r----- 1 admind oinstall    27 Nov 13 15:15 version.txt

drwxr-x--- 2 admind oinstall  4096 Nov 13 15:15 scripts

-rw-r----- 1 admind oinstall  2915 Nov 13 15:15 README.txt

-rw-r----- 1 admind oinstall  3177 Nov 13 15:15 operr_readme.txt

-rwxr-x--- 1 admind oinstall  4220 Nov 13 15:15 operr.bat

-rwxr-x--- 1 admind oinstall  3161 Nov 13 15:15 operr

drwxr-x--- 4 admind oinstall  4096 Nov 13 15:15 opatchprereqs

-rwxr-x--- 1 admind oinstall  2652 Nov 13 15:15 opatch.pl

-rwxr-x--- 1 admind oinstall  9445 Nov 13 15:15 opatchdiag.bat

-rwxr-x--- 1 admind oinstall 10125 Nov 13 15:15 opatchdiag

-rwxr-x--- 1 admind oinstall 15277 Nov 13 15:15 opatch.bat

-rwxr-x--- 1 admind oinstall 27214 Nov 13 15:15 opatch

drwxr-x--- 2 admind oinstall  4096 Nov 13 15:15 jlib

-rwxr-x--- 1 admind oinstall 23764 Nov 13 15:15 emdpatch.pl

-rwxr-x--- 1 admind oinstall   645 Nov 13 15:15 datapatch.bat

-rwxr-x--- 1 admind oinstall   607 Nov 13 15:15 datapatch

drwxr-x--- 5 admind oinstall  4096 Nov 13 15:15 ocm

drwxrwxr-x 3 admind oinstall  4096 Nov 13 15:16 oracle_common

drwxr-x--- 3 admind oinstall  4096 Nov 13 15:16 oplan

drwxr-x--- 4 admind oinstall  4096 Nov 13 15:16 opatchauto-dir

-rwxr-x--- 1 admind oinstall   309 Nov 13 15:16 opatchauto

drwxr-x--- 2 admind oinstall  4096 Nov 13 15:16 docs

[admind@tnc63 OPatch]$ rm -rf *

[admind@tnc63 OPatch]$ pwd

/u01/app/admind/product/12.1.0/dbhome_1/OPatch

Step3: Copy the latest released OPatch version for 12.1, which is available for download from My Oracle Support patch 6880880 by selecting the 12.1.0.1.1 release and copy to the ORACLE_HOME

unzip the downloaded patch in oracle_home, when we unzip patch automatically new files will be loaded in OPatch directory 

[admind@tnc63 dbhome_1]$ unzip p6880880_121010_Linux-x86-64.zip

Archive:  p6880880_121010_Linux-x86-64.zip

  inflating: OPatch/opatch           

   creating: OPatch/oracle_common/

   creating: OPatch/oracle_common/modules/

  inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.2.0.0.jar  

   creating: OPatch/jlib/

  inflating: OPatch/jlib/opatchsdk.jar  

  inflating: OPatch/jlib/oracle.opatch.classpath.unix.jar  

  inflating: OPatch/jlib/oracle.opatch.classpath.windows.jar  

  inflating: OPatch/jlib/oracle.opatch.classpath.jar  

  inflating: OPatch/jlib/opatch.jar  

  inflating: OPatch/jlib/oracle.opatchcore.classpath.windows.jar  

  inflating: OPatch/jlib/oracle.opatchcore.classpath.jar  

  inflating: OPatch/jlib/oracle.opatchcore.classpath.unix.jar  

  inflating: OPatch/README.txt       

   creating: OPatch/ocm/

  inflating: OPatch/ocm/ocm_platforms.txt  

   creating: OPatch/ocm/doc/

   creating: OPatch/ocm/bin/

  inflating: OPatch/ocm/bin/emocmrsp  

   creating: OPatch/ocm/lib/

  inflating: OPatch/ocm/lib/http_client.jar  

  inflating: OPatch/ocm/lib/jsse.jar  

  inflating: OPatch/ocm/lib/osdt_core3.jar  

  inflating: OPatch/ocm/lib/emocmclnt.jar  

  inflating: OPatch/ocm/lib/log4j-core.jar  

  inflating: OPatch/ocm/lib/xmlparserv2.jar  

  inflating: OPatch/ocm/lib/regexp.jar  

  inflating: OPatch/ocm/lib/jcert.jar  

  inflating: OPatch/ocm/lib/osdt_jce.jar  

  inflating: OPatch/ocm/lib/jnet.jar  

  inflating: OPatch/ocm/lib/emocmclnt-14.jar  

  inflating: OPatch/ocm/lib/emocmcommon.jar  

 extracting: OPatch/ocm/ocm.zip      

  inflating: OPatch/operr.bat        

  inflating: OPatch/datapatch.bat    

  inflating: OPatch/opatch.bat       

   creating: OPatch/docs/

  inflating: OPatch/docs/Prereq_Users_Guide.txt  

  inflating: OPatch/docs/Users_Guide.txt  

  inflating: OPatch/docs/FAQ         

  inflating: OPatch/docs/cversion.txt  

  inflating: OPatch/opatchdiag       

  inflating: OPatch/opatch.pl        

   creating: OPatch/oplan/

  inflating: OPatch/oplan/oplan      

   creating: OPatch/oplan/jlib/

  inflating: OPatch/oplan/jlib/ValidationRules.jar  

  inflating: OPatch/oplan/jlib/automation.jar  

  inflating: OPatch/oplan/jlib/osysmodel-utils.jar  

  inflating: OPatch/oplan/jlib/oracle.oplan.classpath.jar  

  inflating: OPatch/oplan/jlib/Validation.jar  

   creating: OPatch/oplan/jlib/jaxb/

  inflating: OPatch/oplan/jlib/jaxb/activation.jar  

  inflating: OPatch/oplan/jlib/jaxb/jaxb-impl.jar  

  inflating: OPatch/oplan/jlib/jaxb/jsr173_1.0_api.jar  

  inflating: OPatch/oplan/jlib/jaxb/jaxb-api.jar  

   creating: OPatch/oplan/jlib/apache-commons/

  inflating: OPatch/oplan/jlib/apache-commons/commons-cli-1.0.jar  

  inflating: OPatch/oplan/jlib/apache-commons/commons-compress-1.4.jar  

  inflating: OPatch/oplan/jlib/oplan_core.jar  

  inflating: OPatch/oplan/jlib/bundle.jar  

  inflating: OPatch/oplan/jlib/ProductDriver.jar  

 extracting: OPatch/version.txt      

   creating: OPatch/opatchauto-dir/

   creating: OPatch/opatchauto-dir/opatchautocore/

  inflating: OPatch/opatchauto-dir/opatchautocore/oplan.bat  

  inflating: OPatch/opatchauto-dir/opatchautocore/opatchautobinary  

   creating: OPatch/opatchauto-dir/opatchautocore/jlib/

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/osysmodel-utils.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/ValidationRules.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oplan_sample.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/bundle.jar  

   creating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/commons-cli-1.0.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/commons-compress-1.4.jar  

   creating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jaxb-api.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jaxb-impl.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jsr173_1.0_api.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/activation.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oplan_core.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oracle.oplan.classpath.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/patchsdk.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/Validation.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/OsysModel.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/ProductDriver.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/jlib/automation.jar  

  inflating: OPatch/opatchauto-dir/opatchautocore/oplan  

  inflating: OPatch/opatchauto-dir/opatchautocore/README.html  

  inflating: OPatch/opatchauto-dir/opatchautocore/README.txt  

   creating: OPatch/opatchauto-dir/opatchautodb/

   creating: OPatch/opatchauto-dir/opatchautodb/jlib/

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oracle.opatchautodb.classpath.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oracle.opatchautodb.classpath.windows.jar  

 extracting: OPatch/opatchauto-dir/opatchautodb/jlib/opatchauto-core.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oracle.opatchautodb.classpath.unix.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/opatchauto-db.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oplan_db.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oracle.oplan.db.classpath.jar  

  inflating: OPatch/opatchauto-dir/opatchautodb/opatchautodbscr  

   creating: OPatch/opatchprereqs/

   creating: OPatch/opatchprereqs/oui/

  inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml  

   creating: OPatch/opatchprereqs/opatch/

  inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml  

  inflating: OPatch/opatchprereqs/opatch/rulemap.xml  

  inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml  

  inflating: OPatch/opatchprereqs/prerequisite.properties  

  inflating: OPatch/datapatch        

  inflating: OPatch/operr_readme.txt  

   creating: OPatch/scripts/

  inflating: OPatch/scripts/opatch_wls  

  inflating: OPatch/scripts/opatch_jvm_discovery  

  inflating: OPatch/scripts/opatch_jvm_discovery.bat  

  inflating: OPatch/scripts/opatch_wls.bat  

  inflating: OPatch/operr            

  inflating: OPatch/opatchdiag.bat   

  inflating: OPatch/emdpatch.pl      

  inflating: OPatch/opatchauto       

Step4: Check OPatch version 

[admind@tnc63 dbhome_1]$ opatch version

OPatch Version: 12.1.0.1.10

OPatch succeeded. 

Opatch is upgraded successfully 

-- Nikhil Tatineni--
--12c: Pluggable databases--

12c: Upgrading Oracle Multitenant database

Steps to Upgrade
pre-req checks before applying patch to the Oracle databases 

1) Backup existing Oracle_Home 
[oracle@tnc61 12.1.0]$ ls -ltr
[oracle@tnc61 12.1.0]$ cp -r dbhome_1 dbhome_1_backup
drwxr-xr-x 70 oracle dba 4096 Feb 10 21:46 dbhome_1
drwxr-xr-x  2 oracle dba 4096 Feb 10 21:56 dbhome_1_backup

2) Make sure Opatch version is compactible to the patch we are applying
[oracle@tnc61 catbundle]$ opatch version
OPatch Version: 12.1.0.1.10
OPatch succeeded.

3) Add following line in bash_profile
PATH=$PATH:$ORACLE_HOME/OPatch

4) checks for conflicts before applying a patch
[oracle@tnc61 19877342]$ pwd
/u01/patch/20132482/19877342
[oracle@tnc61 19877342]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.1.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-02-10_21-44-34PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

5) run opatch apply when it passes all pre-req's
[oracle@tnc61 19877342]$ opatch apply

[oracle@tnc61 19877342]$ opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.1.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-02-10_22-01-23PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2016-02-10_22-01-23PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: tnc61.ffdc.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c | 12.1.0.1.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch  19877342     : applied on Wed Feb 10 22:00:23 EST 2016
Unique Patch ID:  18407371
Patch description:  "Oracle JAVAVM Component 12.1.0.1.2 Database PSU (JAN2015)"
   Created on 22 Dec 2014, 06:38:01 hrs PST8PDT
   Bugs fixed:
     19007266, 19153980, 17070459, 19554117, 19909862, 17201047, 19058059
     19245191, 16746190, 19699946, 19852357, 19282024, 19006757, 19877342
     19895326, 19231857, 17615204, 17907157, 17285560, 17056813, 19895362
     14774730, 19223010
--------------------------------------------------------------------------------
OPatch succeeded.

Patch is successfully installed on oracle home & now we need to upgrade the databases 


upgrading CDB & PDB DATABASES 

[oracle@tnc61 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 10 22:10:05 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area  730714112 bytes
Fixed Size            2292672 bytes
Variable Size          532677696 bytes
Database Buffers      192937984 bytes
Redo Buffers            2805760 bytes
Database mounted.
Database opened.

SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

SQL> select name,open_mode from v$pdbs;
NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PINK                   READ WRITE
GREEN               READ WRITE

SQL> exit

[oracle@tnc61 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/OPatch
[oracle@tnc61 OPatch]$ ls -ltr
total 164
-rw-r----- 1 oracle dba    27 Nov 13 15:15 version.txt
drwxr-x--- 2 oracle dba  4096 Nov 13 15:15 scripts
-rw-r----- 1 oracle dba  2915 Nov 13 15:15 README.txt
-rw-r----- 1 oracle dba  3177 Nov 13 15:15 operr_readme.txt
-rwxr-x--- 1 oracle dba  4220 Nov 13 15:15 operr.bat
-rwxr-x--- 1 oracle dba  3161 Nov 13 15:15 operr
drwxr-x--- 4 oracle dba  4096 Nov 13 15:15 opatchprereqs
-rwxr-x--- 1 oracle dba  2652 Nov 13 15:15 opatch.pl
-rwxr-x--- 1 oracle dba  9445 Nov 13 15:15 opatchdiag.bat
-rwxr-x--- 1 oracle dba 10125 Nov 13 15:15 opatchdiag
-rwxr-x--- 1 oracle dba 15277 Nov 13 15:15 opatch.bat
-rwxr-x--- 1 oracle dba 27214 Nov 13 15:15 opatch
drwxr-x--- 2 oracle dba  4096 Nov 13 15:15 jlib
-rwxr-x--- 1 oracle dba 23764 Nov 13 15:15 emdpatch.pl
-rwxr-x--- 1 oracle dba   645 Nov 13 15:15 datapatch.bat
-rwxr-x--- 1 oracle dba   607 Nov 13 15:15 datapatch
drwxr-x--- 5 oracle dba  4096 Nov 13 15:15 ocm
drwxrwxr-x 3 oracle dba  4096 Nov 13 15:16 oracle_common
drwxr-x--- 3 oracle dba  4096 Nov 13 15:16 oplan
drwxr-x--- 4 oracle dba  4096 Nov 13 15:16 opatchauto-dir
-rwxr-x--- 1 oracle dba   309 Nov 13 15:16 opatchauto
drwxr-x--- 2 oracle dba  4096 Nov 13 15:16 docs

[oracle@tnc61 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Wed Feb 10 22:13:15 2016
Copyright (c) 2012, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches:
  PDB CDB$ROOT:
  PDB PDB$SEED:
  PDB PINK:
  PDB GREEN:
Currently installed C Patches: 19877342
For the following PDBs: CDB$ROOT
  Nothing to roll back
  The following patches will be applied: 19877342
For the following PDBs: PDB$SEED
  Nothing to roll back
  The following patches will be applied: 19877342
For the following PDBs: PINK
  Nothing to roll back
  The following patches will be applied: 19877342
For the following PDBs: GREEN
  Nothing to roll back
  The following patches will be applied: 19877342
Adding patches to installation queue...
Installing patches...
Validating logfiles...
Patch 19877342 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/product/12.1.0/dbhome_1/sqlpatch/19877342/19877342_apply_COLOUR_CDBROOT_2016Feb10_22_13_56.log
Patch 19877342 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/product/12.1.0/dbhome_1/sqlpatch/19877342/19877342_apply_COLOUR_PDBSEED_2016Feb10_22_14_23.log
Patch 19877342 apply (pdb PINK): SUCCESS
  logfile: /u01/app/oracle/product/12.1.0/dbhome_1/sqlpatch/19877342/19877342_apply_COLOUR_PINK_2016Feb10_22_14_24.log
Patch 19877342 apply (pdb GREEN): SUCCESS
  logfile: /u01/app/oracle/product/12.1.0/dbhome_1/sqlpatch/19877342/19877342_apply_COLOUR_GREEN_2016Feb10_22_15_06.log
SQL Patching tool complete on Wed Feb 10 22:15:55 2016

upgraded container database successfully :)

Upgrading NON CDB database

[oracle@tnc61 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Wed Feb 10 22:42:30 2016
Copyright (c) 2012, Oracle.  All rights reserved.
Connecting to database...OK
Determining current state...
Currently installed SQL Patches:
Currently installed C Patches: 19877342
Nothing to roll back
The following patches will be applied: 19877342
Adding patches to installation queue...
Installing patches...
Validating logfiles...
Patch 19877342 apply: SUCCESS
  logfile: /u01/app/oracle/product/12.1.0/dbhome_1/sqlpatch/19877342/19877342_apply_OVAL007P_oval007p_2016Feb10_22_43_19.log
SQL Patching tool complete on Wed Feb 10 22:43:26 2016
[oracle@tnc61 OPatch]$

we can find error in log files at following location
[oracle@tnc61 catbundle]$ pwd
/u01/app/oracle/cfgtoollogs/catbundle


--12c Oracle: Pluggable databases--
--Nikhil Tatineni--

12c: Rename PDB’S from CDB Root

After the data refresh, we are going to rename PDB in lower environment (test | dev). Steps as follows

Step1: close the pdb which you are going to rename it on CDB Root 

SQL> alter pluggable database red close immediate;

Pluggable database altered.

step2: Open PDB in restricted mode as follows 

SQL> alter pluggable database red open restricted;

Pluggable database altered.

SQL> select name, restricted from v$pdbs;

NAME              RES

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

PDB$SEED         NO

RED                  YES

Step3: Now connect to pluggable database & change global_name of pluggable database 

SQL> connect sys/oracle12@red as sysdba

Connected.

SQL> 

SQL> show con_name;

CON_NAME

———————————————

RED

SQL> alter pluggable database red rename global_name to pink;

Pluggable database altered.

SQL> show con_name;

CON_NAME

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

PINK

Step4: close and reopen the pluggable database;

SQL> alter pluggable database pink close immediate;                   

Pluggable database altered.

SQL> alter pluggable database pink open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                       OPEN_MODE

------------------------------ —————

PINK                          READ WRITE

Step5:  Make sure service is created after renaming PDB on CDB ROOT

SQL> column name format a20

SQL> column pdb format a40

SQL> select name, pdb from V$SERVICES order by creation_date;

NAME                             PDB

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

SYS$BACKGROUND        CDB$ROOT

SYS$USERS                    CDB$ROOT

colourXDB                     CDB$ROOT

colour                           CDB$ROOT

green                            GREEN

pink                              PINK

6 rows selected.

Step6: Make changes to tnsnames.ora accordingly  

PINK =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.118)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pink)

    )

  )

[oracle@tnc61 admin]$ tnsping pink

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 10-FEB-2016 11:00:47

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

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.118)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pink)))

OK (10 msec)

[oracle@tnc61 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 10-FEB-2016 11:02:45

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production

Start Date                10-FEB-2016 10:22:45

Uptime                    0 days 0 hr. 39 min. 59 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/tnc61/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tnc61.ffdc.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tnc61.ffdc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/colour/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "colour" has 1 instance(s).

  Instance "colour", status READY, has 1 handler(s) for this service...

Service "colourXDB" has 1 instance(s).

  Instance "colour", status READY, has 1 handler(s) for this service...

Service "green" has 1 instance(s).

  Instance "colour", status READY, has 1 handler(s) for this service...

Service "pink" has 1 instance(s).

  Instance "colour", status READY, has 1 handler(s) for this service...

The command completed successfully

We have dynamic listener configuration, when global_name is changed PMON automatically registered with the listener :) 

—Nikhil Tatineni—

—12c: pluggable databases — 

12c: Clone pluggable database using existing Pluggable database

Currently I have only one pluggable database RED on container database

I want to create clone Pluggable database GREEN using  existing pluggable database RED 

Steps to clone or create new pluggable database using existing on cdb 

step1: Place the pluggable database in read only mode 

SQL> alter pluggable database red close;

Pluggable database altered.

SQL> alter pluggable database red open read only force;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

PDB$SEED       READ ONLY

RED                READ ONLY

[oracle@tnc61 datafile]$ ls -ltr

total 896976

-rw-r----- 1 oracle dba  20979712 Feb 10 00:00 o1_mf_temp_ccogfjdy_.dbf

-rw-r----- 1 oracle dba   5251072 Feb 10 06:43 o1_mf_users_ccogg54y_.dbf

-rw-r----- 1 oracle dba 272637952 Feb 10 06:43 o1_mf_system_ccogf8ds_.dbf

-rw-r----- 1 oracle dba 639639552 Feb 10 06:43 o1_mf_sysaux_ccogf84p_.dbf

STEP2: Make sure directory is created on the server if required and create pluggable database as follows 

[oracle@tnc61 ~]$ mkdir -p /u01/app/oracle/oradata/COLOUR/GREEN

[oracle@tnc61 ~]$ cd /u01/app/oracle/oradata/COLOUR/GREEN

[oracle@tnc61 GREEN]$ pwd

/u01/app/oracle/oradata/COLOUR/GREEN

SQL> create pluggable database green from red file_name_convert=('/u01/app/oracle/oradata/COLOUR/2B63B360CA071846E0530100007FA5FC/datafile/o1_mf_sysaux_ccogf84p_.dbf','/u01/app/oracle/oradata/COLOUR/GREEN/datafile/sysaux_01.dbf','/u01/app/oracle/oradata/COLOUR/2B63B360CA071846E0530100007FA5FC/datafile/o1_mf_system_ccogf8ds_.dbf','/u01/app/oracle/oradata/COLOUR/GREEN/datafile/system_01.dbf','/u01/app/oracle/oradata/COLOUR/2B63B360CA071846E0530100007FA5FC/datafile/o1_mf_users_ccogg54y_.dbf','/u01/app/oracle/oradata/COLOUR/GREEN/datafile/users_01.dbf','/u01/app/oracle/oradata/COLOUR/2B63B360CA071846E0530100007FA5FC/datafile/o1_mf_temp_ccogfjdy_.dbf','/u01/app/oracle/oradata/COLOUR/GREEN/datafile/temp_01.dbf');

Pluggable database created.

after creating new pluggable database, the new pluggable database will be in MOUNT Stage 

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

PDB$SEED       READ ONLY

RED                READ ONLY

GREEN            MOUNTED

STEP3: close and open old and new pluggable databases 

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

PDB$SEED       READ ONLY

RED                READ WRITE

GREEN            READ WRITE

STEP4: Add entry in listener.ora and tnsnames.ora 

GREEN =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.118)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = green)

    )

  )

STEP5: Make sure services are created for new pluggable database from v$services;

SQL> select name, pdb from V$SERVICES order by creation_date;

SQL> column name format a20

SQL> column pdb format a40

SQL> /

NAME                                 PDB

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

SYS$BACKGROUND         CDB$ROOT

SYS$USERS                    CDB$ROOT

colourXDB                     CDB$ROOT

colour                           CDB$ROOT

red                                RED

green                             GREEN

---Nikhil Tatineni--

---12c: Pluggable databases--

12c: Trigger to open all pdbs after cdb reboot

when ever container is bounced, it will close all the pluggable databases
When ever it is up, we have to manually open all pluggable databases 
we can make it automatic by deploying following trigger on container database 

create or replace trigger sys.after_startuppdbs after startup on database 
begin 
execute immediate 'alter pluggable database all open';
end after_startuppdbs;
/

In order to validate bounce the database and check the status of pluggable database before and after server reboot

status of pluggable databases before server reboot

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE

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

PDB$SEED     READ ONLY

RED              MOUNTED

Bouncing container database and checking the status of pluggable databases in following steps 

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  730714112 bytes

Fixed Size     2292672 bytes

Variable Size   549454912 bytes

Database Buffers   176160768 bytes

Redo Buffers     2805760 bytes

Database mounted.

Database opened.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED   READ ONLY

RED            READ WRITE

from above, we can confirm that trigger that we deployed on container is successfully bringing all pluggable databases after CDB reboot 

---Nikhil Tatineni--

---12c: Pluggable databases -- 

12c: open & close Pluggable databases

Command to list the all pluggable databases and open_mode on container database

donot confuse pdb$seed is the template to create new pluggable databases
we have only 1 pluggable database RED on container database and it is in mounted state 

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE

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

PDB$SEED     READ ONLY

RED              MOUNTED

command to open pluggable database 'RED'

SQL> alter pluggable database red open;

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE

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

PDB$SEED     READ ONLY

RED              READ WRITE

command to close pluggable database RED on container database

SQL> alter pluggable database red close immediate;

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE

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

PDB$SEED     READ ONLY

RED              MOUNTED

command to close all pluggable databases on container database 

SQL>  alter pluggable database all close immediate;

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE

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

PDB$SEED     READ ONLY

RED              MOUNTED

command to open all pluggable databases on container database 

SQL> alter pluggable database all open;

Pluggable database altered.

SQL>  select name, open_mode from v$pdbs;

NAME               OPEN_MODE

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

PDB$SEED       READ ONLY

RED                   READ WRITE

Note: when ever we start container database, all of our pluggable databases are in mount state. we have to open them manually or we can place a trigger on container database which will open all pluggable databases after bouncing the database on the server 

--Nikhil Tatineni--

--12c: Pluggable databases-- 

ASM: asmcmd

ASM command line utility "asmcmd" to manage asm 

Use "lsdg" to examine the disk groups.

ASMCMD>lsdg

Use the "ls" command to list the ASM directory structure

ASMCMD>ls

"cd" command to change to the AWORLD directory

ASMCMD>cd AWORLD

"du" command to evaluate size of the disk group directory

ASMCMD>du

"lsct" command to see software versions.

ASMCMD> lsct

"lsct -g" to examine the client’s use of diskgroups

ASMCMD>lsct–g

"lsdsk" shows the disks that are managed by ASM

 "iostat" to see the Reads and Writes

ASMCMD> iostat

---Nikhil Tatineni--

---Oracle : ASM ---