I am happy to announce that I will be presenting Data Guard at the next Arizona Oracle User Group in Phoenix on January 26, 2017. At January’s AZORA meeting we’ll have a total of four presentations; two sessions running at the same time in different rooms.

Bring a partner to make sure your organization does not miss out on any of the content. We’ll have pizza for lunch and cookies later in the day thanks to the generosity of OneNeck IT Solutions and Viscosity North America.

Once again, Republic Services will host (thank you Republic) in two of their brand-new training rooms on the first floor.

When: January 26, 2017 (Thursday) 12:30 pm – 4:00 pm

Where: Republic Services
1st Floor Training Rooms
14400 N 87th St (AZ101 & Raintree)
Scottsdale, AZ

Agenda:

12:30 – 1:00 Registration and Pizza
1:00-1:10 Welcome
1:10-2:10 Presentations

Room 1 Biju Thomas – OneNeck IT Solutions (Oracle ACE Director)
“Oracle Database 12c New Features for 11gR2 DBA”

Room 2 Charles Kim – Viscosity North America (Oracle ACE Director)
“Bullet Proof Your Data Guard Environment”

Here’s the summary to what I will be presenting:

Compliance to industry best practices can easily be achieved. This session will disseminate fundamental Data Guard best practices and reference architectures that DBAs need to know to protect their Oracle ecosystem. The author of the Oracle Data Guard Handbook will demonstrate how DBAs should set up, configure, and monitor mission-critical Data Guard environments (including Active Data Guard).

Come see Data Guard best practices in action. The session concentrates on:
o Building the physical standby
o Monitoring and maintaining the physical standby
o Configuring Data Guard Broker
o Performing backup and recovery with RMAN
o Setting archive retention
o Performing switchovers and failovers
o Integrating Data Guard with OEM 13c
o What’s new in Oracle Data Guard 12.2

2:10-2:25 Break – Coffee & Cookies

2:25-3:25 Presentations

Room 1 Biju Thomas – OneNeck IT Solutions (Oracle ACE Director)
“Introduction to Oracle Databases in the Cloud”

Room 2 Jerry Ward – Viscosity North America
“Building Faceted Search Navigation in APEX with Oracle JET and PL/SQL Pipelines”

3:25-3:30 Wrap Up and Closing


Here’s some of my favorite Oracle Database 12.2 Multitenant new features that I can talk about at this time. Oracle Database 12c Release 2 is slated to go live sometime this year.

Pluggable databases

  • The number of PDB limits per CDB increases to 4096 rom the limit of 252 PDBs in 12.2. Even though we do not have customers who have over 252 PDBs or even venture in this high number of PDBs, Oracle raised this limit in 12.2.
  • Resource manager will have the capability to limit the memory and govern CPU and I/O.  For RAC (or RAC One Node) services associated to a PDB, oracle will not have interconnect overhead
  • In 12.2, we will no longer need to to put the PDB in read-only mode to perform hot clones.  
  • We can refresh PDBs online.  
  • We can relocate PDBs without any downtime.  We can move PDBs from one CDB to another.  Larry Ellison in OOW 2015 did a live demo and moved a PDB from on-premise to Oracle Public Cloud.  12.2 eliminates the need to put the PDB in read-only mode.
  • Proxy PDBs are introduced.  We can have a new kind of a PDB which points to a remote PDB.  The remote PDB is presented as a local PDB and in all practical purposes looks like a local PDB with all functionality is available to the Proxy PDB.
  • All new Application Containers are introduced. Oracle revolutionizes the concept of having a single master application definition for all the tenant containers. We can make changes to just location and changes will sync to all the tenant container.

Are you up on Oracle 12.1.0.2 new features? Are you considering pluggable databases? Let’s take a look at the new features that Oracle introduced in 12.1.0.2. Even though this is not a major release of Oracle, it is still packed with new features, especially in the world on multi-tenancy. The enhancements made to pluggable databases are really attractive. Here’s a brief overview of the new features in pluggable databases:

  • FDA Support for CDBs
  • PDB Containers Clause
  • PDB File Placement in OMF
  • PDB Logging Clause
  • PDB Metadata Clone
  • PDB Remote Clone
  • PDB Snapshot Cloning Additional Platform Support
  • PDB STANDBYS Clause
  • PDB State Management Across CDB Restart
  • PDB Subset Cloning

Session Title: Data Guard Attack!!
Session Number: 1580
Speakers: Charles Kim, Oracle ACE Director and Nitin Vengurlekar, Oracle ACE Director
Track: Database
Session Type: Hands-on Lab
Sub-Categorization: High Availability & Data Protection

Abstract
There is no reason why Data Guard setup, configuration, maintenance and monitoring cannot be setup As A Service. Automation is the crux of any rapid deployment and cloud models. Setting up Data Guard should be a service catalog for any DBaaS deployment.

You will also learn the new Data Guard features available in Oracle Database 12.2

Download the latest and greatest Data Guard automation toolkit from http://dbaexpert.com/dg/ prior to attending this session. We have incorporated industry best practices to the DG Toolkit. This session will disseminate fundamental Data Guard best practices and demonstrate how DBAs can automate setup, configuration, and monitoring of Data Guard environments with assistance from the Date Guard Toolkit (DG Toolkit).

In this hand-on deep dive session, we will go through step by step details of setting up Data Guard with the automation toolkit:
o Building the Physical Standby
o Monitoring and Maintaining the Physical Standby
o Configuring Data Guard Broker
o Performing Backup and Recovery with RMAN
o Setting Archive Retention
o Performing Switchovers and Failovers

Learning Objectives:

1. Most importantly, learn how to build the physical standby with ease and automation using the DG Toolkit
2. Learn how to monitor the physical standby database with DG Toolkit
3. Learn how to monitor the physical standby database with DG Toolkit

Outline / Content Structure:
Perform preliminary check prior to starting the Data Guard Build
1. Perform assessments on the source database
2. Perform assessments on the physical standby

Perform detailed steps to build the physical standby database
1. Look at building the physical standby with easy menu steps
2. Look at duplicating the physical standby database

Configure the Data Guard Broker

Perform monitoring of the Data Guard Environment
1. Monitor the physical standby for performance
2. See how far behind we are

Perform RMAN to disk configuration options


First, download and apply the patch patch: 6880880 from support.oracle.com or from https://updates.oracle.com/download/6880880.html

Download the latest PSU from Doc ID 756671.1. Unzip the PSU, cd to the directory, and check for one-off patch conflict detection and resolution.

[oracle@dal66a 20299023]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.7
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_2/oraInst.loc
OPatch version    : 12.1.0.1.7
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2015-05-16_17-46-46PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@dal66a 20299023]$ opatch lsinv
Oracle Interim Patch Installer version 12.1.0.1.7
Copyright (c) 2015, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_2/oraInst.loc
OPatch version    : 12.1.0.1.7
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2015-05-16_17-46-58PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2015-05-16_17-46-58PM.txt

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

Installed Top-level Products (1): 

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


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

Make sure that all databases are down from the Oracle Home that you are patching. Also, ensure that the database listeners are down from the same Oracle Home; otherwise, you will encounter the following error from opatch as you attempt to apply the PSU:

Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/u01/app/oracle/product/12.1.0/dbhome_2/bin/oracle
/u01/app/oracle/product/12.1.0/dbhome_2/lib/libclntsh.so.12.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2015-05-16_17-47-06PM_1.log

OPatch failed with error code 73

Shutdown all databases and listeners that you are applying the patch for

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, OLAP, Advanced Analytics and Real Application Testing options
[oracle@dal66a 20299023]$ ps -ef |grep -i tns
root        15     2  0 14:31 ?        00:00:00 [netns]
oracle    4067     1  0 16:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr LISTENER -inherit
oracle    4074     1  0 16:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr DBATOOLS -inherit
oracle    6046  3362  0 17:48 pts/0    00:00:00 grep -i tns
[oracle@dal66a 20299023]$ lsnrctl stop dbatools

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-MAY-2015 17:48:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dal66a)(PORT=1522)))
The command completed successfully


[oracle@dal66a 20299023]$ lsnrctl stop listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-MAY-2015 17:48:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dal66a)(PORT=1521)))
The command completed successfully

Now we can apply the latest PSU

[oracle@dal66a 20299023]$ opatch apply
Oracle Interim Patch Installer version 12.1.0.1.7
Copyright (c) 2015, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_2/oraInst.loc
OPatch version    : 12.1.0.1.7
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2015-05-16_17-48-29PM_1.log

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

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y



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


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

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.xdk, 12.1.0.2.0...

Patching component oracle.tfa, 12.1.0.2.0...

Patching component oracle.rdbms.util, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.xdk.parser.java, 12.1.0.2.0...

Patching component oracle.oraolap, 12.1.0.2.0...

Patching component oracle.xdk.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Patching component oracle.ldap.rsf, 12.1.0.2.0...

Patching component oracle.ldap.rsf.ic, 12.1.0.2.0...

Verifying the update...
Applying sub-patch '20299023' to OH '/u01/app/oracle/product/12.1.0/dbhome_2'
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.tfa, 12.1.0.2.0...

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0...

Patching component oracle.ldap.rsf, 12.1.0.2.0...

Patching component oracle.ldap.rsf.ic, 12.1.0.2.0...

Verifying the update...
Composite patch 20299023 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2015-05-16_17-48-29PM_1.log

OPatch succeeded.

Let’s confirm that the PSU was successfully applied

[oracle@dal66a 20299023]$ opatch lsinventory |grep ^Patch
Patch  20299023     : applied on Sat May 16 17:49:12 CDT 2015
Patch description:  "Database Patch Set Update : 12.1.0.2.3 (20299023)"

Now let’s load the modified SQL files into the database. We need to execute Datapatch to complete the post-install SQL deployment portion of the PSU

[oracle@dal66a OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Sat May 16 17:56:55 2015
Copyright (c) 2015, Oracle.  All rights reserved.

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

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 3 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    20299023 (Database Patch Set Update : 12.1.0.2.3 (20299023))

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

Validating logfiles...
Patch 20299023 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/20299023/18703022/20299023_apply_TOOLSDEV_2015May16_17_57_25.log (no errors)
SQL Patching tool complete on Sat May 16 17:57:31 2015

Look for future blog post on applying the same patch against the Oracle Grid Home. This is where the fun really begins as we will be leveraging opatchauto instead of opatch.



As of Oracle 12.1.0.2, with the Oracle Grid Infrastructure Typical Installation, the OUI will automatically create the Oracle GI Management Repository (GIMR). The Oracle GI Management Repository will be housed in the same location of where the first ASM disk group is created .. in my case, where the OCR and Vote disks will reside. It even creates the container database for it.

PastedGraphic 1


Oracle Database 12c Release 1 Patchset 1 is the terminal release for Oracle 12.1. As of Oracle OpenWorld 2014, the announcement was made that 12.1.0.2 will be the terminal release for Oracle Database 12c Release 1. Oracle also notified customers that the 12c Release 2 (12.2) will not be available until the first quarter of 2016. With that in mind, customers are encouraged to jump on the terminal release of Oracle Database 12 release 1.

In this blog post, I will focus on installing and configuring Oracle Database 12c Release 1 Patchset 1.

2014 10 04 14 44 26

Select the option that you want to install and click on Next. In my example, I am selecting to install and configure Oracle Grid Infrastructure for a standalone server.

2014 10 04 14 49 16

Select the languages that you want to install and click on the Next button

2014 10 04 14 51 29

In the Create ASM Disk Group screen, we will create a disk group called DATA. We will also:
* Click on the External Redundancy option
* Change the Allocation Unit size from 1MB to 4MB
* Select all the disks that make up the DATA Disk Group. ORCL:DATA1 to ORCL:DATA4

When we customized all the options, we will click on the Next button

2014 10 04 16 20 49

Enter the password for the SYS and ASMSNMP accounts. In our example, we will choose to enter the same password for both of the accounts. Click on the Next button to continue.

2014 10 04 16 22 09
The password that was entered did not conform to the Oracle recommended standards. We will choose to continue by clicking on the Yes button.

2014 10 04 16 23 53

We do not have an OEM 12c Cloud Control environment setup yet so we will continue by clicking on the Next button.
2014 10 04 16 27 29

Choose the OS Groups for the ASM administrator, ASM DBA, and ASM Operator. In our example, we chose to stick with one standard group since we do not have a separation of roles and responsibilities between the DBA and Cluster administrator.

2014 10 04 16 29 50
Click on Yes to continue

2014 10 04 16 33 04

If this is a fresh install, you will want to create the directories for $ORACLE_BASE on the /u01 file system and change the permissions to oracle:dba:

[root@vna01 u01]# mkdir -p /u01/app/oracle
[root@vna01 u01]# chown -R oracle:dba /u01/app

Click on Next to continue

2014 10 04 16 35 44

Set the path for the oraInventory location. The default location is good for most customers. Click on Next to continue.

2014 10 04 16 38 45

The default location for sudo is in /usr/bin directory. For this screen, you will want to change the location of sudo to be /usr/bin/sudo instead of /usr/local/bin/sudo.

If you are new to the world of sudo, you can make a simple change in the /etc/sudoers file and add the following line:

oracle	ALL=(ALL) 	ALL

This example is not encouraged. Most companies with strict sudoers control will list just the commands that the oracle user can execute on behalf of root.
Click on the next button to continue.

2014 10 04 16 42 34

We seem to have an issue with a kernel parameter called panic_on_oops. This parameter is not set in the /etc/sysctl.conf parameter file. For now, we will choose to ignore this warning and continue with the installation. Let’s continue by clicking on the Ignore All checkbox on the top right corner of the window; then click on the Next button to continue.

2014 10 04 16 45 23

We will see an warning message indicating that we’ve chosen to ignore one or more of the prerequisites. Click on the Yes button to continue.

2014 10 04 16 46 20

Finally, we arrive at the Summary screen. Before we click on the Installation button, we will choose the option to save the response file by clicking on the Save Response File button. Carefully review the options that you have chosen and click on the Install button.

2014 10 04 16 49 03

We can watch the installation progress. You can click on the Details button if you want to see some of the detailed logs of what is happening.

2014 10 04 16 54 20

Once the installation is complete, you will be prompted to auto execute the configuration script. Click on Yes to continue to execute orainstRoot.sh and root.sh as the root user or as the sudo privileged user.

2014 10 04 17 04 09

Congrats! We have successfully installed Oracle 12c Release 1 Patchset 1 Grid Infrastructure. Next, we will proceed with installing and configuring the Oracle 12c Release 1 Patchset 1 Database software.

Install Oracle Database Software

[oracle@vna01 database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 29712 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 31992 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-10-05_03-07-31AM. Please wait ...[oracle@vna01 database]$ 

2014 10 05 03 10 11

Uncheck the option for receiving security updates via Oracle Support
Click on the Next button to continue

2014 10 05 03 12 56
Click on the Yes button and confirm that you have chosen to remain uninformed of critical security updates

2014 10 05 03 14 56
Click on the Install database software only option and click on the Next button to continue. We will create a database later with the Database Configuration Assistant (dbca).

2014 10 05 03 16 27
Click on the Single instance database installation option and click on Next button to continue

2014 10 05 03 17 43
Select the languages that you want to install and click on the Next button to continue

2014 10 05 03 20 46
At the initial release of Oracle Database 12c Release 1 Patchset 1, only the Enterprise Edition was available. Standard Edition or Standard Edition One is not available as an option yet.

2014 10 05 03 24 09
Accept the default location for $ORACLE_BASE and $ORACLE_HOME since we have already installed the Grid Infrastructure and click on the Next button to continue.

2014 10 05 03 26 47
Select the OS groups for OSDBA, OSOPER, OSBACKUPDBA, OSDGDBA and OSKMDBA and click on the Next button to continue.

2014 10 05 03 31 39
We are at the Summary screen. Before we click on the Install button, we will choose the option to save the response file by clicking on the Save Response File button. Carefully review the options that you have chosen and click on the Install button.

2014 10 05 03 34 06

We can watch the installation progress. You can click on the Details button if you want to see some of the detailed logs of what is happening.

2014 10 05 03 36 50
As the root account, execute root.sh from the Database Oracle Home.

[oracle@vna01 dbhome_1]$ sudo ./root.sh
[sudo] password for oracle: 
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.1.0/dbhome_1

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.

After root.sh is successfully executed, go back to the Execute Configuration Script window and click on the OK button.

2014 10 05 03 43 37
Click on the Close button to complete the database software installation

Create the Fast Recovery Area (FRA)

Launch the ASM Configuration Assistant (asmca). Before we launch asmca, we need to source the oraenv file and setup the Linux environment for ASM.

[oracle@vna01 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? +ASM
The Oracle base has been set to /u01/app/oracle
[oracle@vna01 Desktop]$ asmca

2014 10 05 03 53 42
Click on the Create button to create the FRA disk group.

2014 10 05 03 56 01

From the Create Disk Group screen, enter FRA for the Disk Group Name and select the option for the External redundancy. Click on the Show Advanced Options and change the Allocation Unit Size (MB) from 1MB to 4MB.

2014 10 05 04 01 08
If we know for a fact that only 12.1 databases will ever go on this ASM instance, we can advance the Database Compatibility and ADVM Compatibility to 12.1.0.0.0 also. We will click on the OK button to complete the FRA disk group creation.

2014 10 05 04 02 36
While the disk group is being created, you will see the above progress window.

2014 10 05 04 03 02
We successfully created the FRA disk group. Click on the Close button to exit the screen and click on the Exit button to cleanly exit asmca.

Create a Database with dbca (using an existing Oracle seeded database template)

We need to launch the Database Configuration Assistant (dbca). Before we launch dbca, we need to source the oraenv file and setup the Oracle environment.

[oracle@vna01 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@vna01 dbhome_1]$ . oraenv
ORACLE_SID = [oracle] ? db
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base has been set to /u01/app/oracle
[oracle@vna01 dbhome_1]$ 
[oracle@vna01 dbhome_1]$ dbca

2014 10 05 03 47 39

Select the Create Database option and click on the Next button to continue

2014 10 05 04 13 42
Enter the database name and select the disk group location for the database files and fast recovery area. Choose the database character set for the database and enter the administrator password. You can choose the create the database as a Container Database (default). You can enter the name for the pluggable database name. Click on the Next button to continue.

2014 10 05 04 15 12
We receive a warning message because the administrative password does not conform to the recommended Oracle standards. Click on the Yes button to continue.

2014 10 05 04 17 57
From the Summary screen, review the options that were chosen and click on the Finish button.

2014 10 05 04 19 28
We can review the database creation progress. In the meanwhile, we can also view the Activity Log or the Alert Log.

Create a Customized Database with dbca Advanced Mode

Coming soon


In this blog I want to illustrate the benefits of deploying PDB with RAC Services.  Although the key ingredient is the Service, RAC provides the final mile for scalability and availability.  In my mind I would not implement PDB w/o RAC

Anyways here we go…

The goal is to illustrate that Database [RAC] Services integration with PDBs provides seamless management and availability.

Initially, we have only the PDB$SEED. 

SQL> select * from v$pdbs;


CON_ID       DBID     CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                              CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
2 4080865680 4080865680 F13EFFD958E24857E0430B2910ACF6FD PDB$SEED                       READ ONLY  NO  17-FEB-14 01.01.13.909 PM                                                 1720768  283115520

Let's create a PDB from the SEED (I have shown this from an earlier Blog post)

SQL> CREATE PLUGGABLE DATABASE pdbhansolo admin user hansolo identified by hansolo roles=(dba);

Pluggable database created.

Now we have the new PDB listed.

SQL> select * from v$pdbs;

CON_ID     DBID       CON_UID     GUID                            NAME                           OPEN_MODE  RES OPEN_TIME                                                              CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ----------- ----------------------------------------------------------------------- ---------- ----------
2          4080865680 4080865680 F13EFFD958E24857E0430B2910ACF6FD PDB$SEED                       READ ONLY  NO  17-FEB-14 01.01.13.909 PM                                                 1720768  283115520
         3 3403102439 3403102439 F2A023F791663F8DE0430B2910AC37F7 PDBHANSOLO                     MOUNTED        17-FEB-14 01.27.08.942 PM                                                 1846849          0

But notice that its in "MOUNTED" status. Even if I restart the whole CDB, the new PDB will not come up in OPEN READ WRITE mode. If we want to have the
PDB available on startup. Here's how we go about resolving it.

When we create or plug in a new PDB, a default Service gets created, as with previous versions, it is highly recommended not to connect to Service. Oracle took this one step forward and forced users to create a user generated Service. So let's associate a user Service with that PDB. Notice that there's a "-pdb" flag in the add service command.

$ srvctl add service -d dagobah -s hoth -pdb pdbhansolo


[oracle@rac02 ~]$ srvctl config service -d dagobah -verbose
Service name: Hoth
Service is enabled
Server pool: Dagobah
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
----> Pluggable database name: pdbhansolo
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
Preferred instances: Dagobah_1
Available instances: 

And the Service is registered with the listener

     
[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 17-FEB-2014 13:34:41

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                17-FEB-2014 12:59:46
Uptime                    0 days 0 hr. 34 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac02.viscosityna.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/product/12.1.0/db/admin/Dagobah/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "Dagobah" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "DagobahXDB" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "Hoth" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
----> Service "pdbhansolo" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "r2d2" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
The command completed successfully

Now let's test this. I close the PDB and I also stopped the CDB (probably not necessary, but what the heck :-))

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdbhansolo close;

[oracle@rac02 ~]$ srvctl stop database -d dagobah

[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 18-FEB-2014 15:36:49

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                18-FEB-2014 12:57:30
Uptime                    0 days 2 hr. 39 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX3", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
The command completed successfully


[oracle@rac02 ~]$ srvctl start database -d dagobah

[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 18-FEB-2014 15:37:39

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                18-FEB-2014 12:57:30
Uptime                    0 days 2 hr. 40 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac02.viscosityna.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/product/12.1.0/db/admin/Dagobah/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX3", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "Dagobah" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "DagobahXDB" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "Hoth" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
----> Service "pdbhansolo" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...</strong>
Service "r2d2" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac02 ~]$

SQL> select NAME,OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
-----> PDBHANSOLO                     READ WRITE

Now I can connect to this PDB using my lovely EZConnect string

sqlplus hansolo/hansolo@rac02/hoth

The key here is that RAC Service of PDBHANSOLO (non-default) becomes an important aspect of PDB auto-startup. Without the use the non-default service the PDB does not open 'read write' automatically. So where does RAC fit in here. Well if I have say a 6 node RAC cluster, I can have some PDSB-Services not started on certain nodes, this effectively prevents access to those PDB from certain nodes, thus I can have a certain pre-defined workload distribution. That's a topic for my next Blog and Oracle Users Group presentation 🙂