DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Oracle Multitenant and custom DBCA templates

Posted on March 28, 2014 by Ludovico

Today I’ve encountered an annoying issue while adapting a few scripts for automatic database creation. I track it here so hopefully it may save a few hours of troubleshooting to someone…

I’ve used the DBCA to prepare a new template:

dbca -> Create Database -> Advanced Mode -> General Purpose -> 
Create As Container Database -> Create an Empty Container Database

1 2	dbca -> Create Database -> Advanced Mode -> General Purpose -> Create As Container Database -> Create an Empty Container Database

Then continued by customizing other options, including init parameters and datafile/logfile paths. Finally, I’ve saved it as a new template instead of creating the database.

I’ve checked the resulting .dbc and seen that there was, as expected, the parameter “enable_pluggable_database”=”true”.

Then I’ve moved the template file to my $HOME directory and tested the silent database creation with the option “-createAsContainerDatabase true”:

$ ${ORACLE_HOME}/bin/dbca -silent \
-createDatabase \
-createAsContainerDatabase true  \
-templateName /home/oracle/Template_12_EE_MULTITENANT.dbc \
-gdbname cdb1 \
-sid cdb1 \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-SYSPASSWORD *** \
-SYSTEMPASSWORD *** \
-redoLogFileSize 256 \
-initparams open_cursors=1300 \
-initparams processes=3000 \
-initparams enable_pluggable_database=true

$ ${ORACLE_HOME}/bin/dbca -silent \

-createDatabase \

-createAsContainerDatabase true \

-templateName /home/oracle/Template_12_EE_MULTITENANT.dbc \

-gdbname cdb1 \

-sid cdb1 \

-responseFile NO_VALUE \

-characterSet AL32UTF8 \

-SYSPASSWORD *** \

-SYSTEMPASSWORD *** \

-redoLogFileSize 256 \

-initparams open_cursors=1300 \

-initparams processes=3000 \

-initparams enable_pluggable_database=true

The database configuration has completed successfully, without errors. I’ve accessed my new container, and I’ve been surprised by seing:

SQL> select * from v$pdbs;

no rows selected

SQL>

SQL> select * from v$pdbs;

no rows selected

SQL>

In fact, there were no pdb$seed datafiles:

SQL> select distinct con_id from cdb_data_files;

    CON_ID
----------
         1

SQL>

SQL> select distinct con_id from cdb_data_files;

CON_ID

----------

SQL>

After little investigation, I’ve found these lines in the dbca trace.log:

[main] [ 2014-03-28 10:37:53.956 CET ] [Host.startOperation:2651]  is Template CDB false
[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2314]  Selected Template by user:=Template CEI 8K 12 EE MULTITENANT
[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2321]  The Message Id to be searched:=null
[main] [ 2014-03-28 10:37:53.957 CET ] [Host.startOperation:2663]  Template Selected is User created NON-CDB Template. Creating database as NON-CDB
[main] [ 2014-03-28 10:37:53.957 CET ] [HAUtils.getCurrentOracleHome:490]  Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

[main] [ 2014-03-28 10:37:53.956 CET ] [Host.startOperation:2651] is Template CDB false

[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2314] Selected Template by user:=Template CEI 8K 12 EE MULTITENANT

[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2321] The Message Id to be searched:=null

[main] [ 2014-03-28 10:37:53.957 CET ] [Host.startOperation:2663] Template Selected is User created NON-CDB Template. Creating database as NON-CDB

[main] [ 2014-03-28 10:37:53.957 CET ] [HAUtils.getCurrentOracleHome:490] Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

Then I’ve struggled with dbca and templates a few times before finding that, actually, the magic “enable pluggable database” is done by dbca only if the template file name is not customized.

Running the same exact command with the very same template file but renamed to $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc actually works (notice the diff at the first line):

$ diff /home/oracle/Template_12_EE_MULTITENANT.dbc $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
$ ${ORACLE_HOME}/bin/dbca -silent \
-createDatabase \
-createAsContainerDatabase true  \
-templateName General_Purpose.dbc \
-gdbname cdb1 \
-sid cdb1 \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-SYSPASSWORD *** \
-SYSTEMPASSWORD *** \
-redoLogFileSize 256 \
-initparams open_cursors=1300 \
-initparams processes=3000 \
-initparams enable_pluggable_database=true

$ diff /home/oracle/Template_12_EE_MULTITENANT.dbc $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

$ ${ORACLE_HOME}/bin/dbca -silent \

-createDatabase \

-createAsContainerDatabase true \

-templateName General_Purpose.dbc \

-gdbname cdb1 \

-sid cdb1 \

-responseFile NO_VALUE \

-characterSet AL32UTF8 \

-SYSPASSWORD *** \

-SYSTEMPASSWORD *** \

-redoLogFileSize 256 \

-initparams open_cursors=1300 \

-initparams processes=3000 \

-initparams enable_pluggable_database=true

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE
---------- ----------
         2 4086042395 4086042395 F5A8226121F93B96E0434B96780A8C91
PDB$SEED                       READ ONLY  NO
28-MAR-14 10.20.42.813 AM
   1720341  283115520

SQL> select distinct con_id from cdb_data_files;

    CON_ID
----------
         2
         1

SQL>

SQL> select * from v$pdbs;

CON_ID DBID CON_UID GUID

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

NAME OPEN_MODE RES

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

OPEN_TIME

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

CREATE_SCN TOTAL_SIZE

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

2 4086042395 4086042395 F5A8226121F93B96E0434B96780A8C91

PDB$SEED READ ONLY NO

28-MAR-14 10.20.42.813 AM

1720341 283115520

SQL> select distinct con_id from cdb_data_files;

CON_ID

----------

SQL>

I’ve also tried to cheat and use a symlink to my previous custom template, and surprisingly, it still works:

$ ls -l $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
lrwxrwxrwx 1 oracle dba 74 Mar 28 13:07 /u01/app/oracle/product/12.1.0.1/assistants/dbca/templates/General_Purpose.dbc -> /home/oracle/Template_12_EE_MULTITENANT.dbc
$

$ ls -l $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

lrwxrwxrwx 1 oracle dba 74 Mar 28 13:07 /u01/app/oracle/product/12.1.0.1/assistants/dbca/templates/General_Purpose.dbc -> /home/oracle/Template_12_EE_MULTITENANT.dbc

In the dbca trace log the message saying that the DB will be NON-CDB disappears:

[main] [ 2014-03-28 10:12:14.683 CET ] [Host.startOperation:2651]  is Template CDB false
[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2314]  Selected Template by user:=General Purpose
[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2321]  The Message Id to be searched:=GENERAL_PURPOSE
[main] [ 2014-03-28 10:12:14.683 CET ] [HAUtils.getCurrentOracleHome:490]  Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

[main] [ 2014-03-28 10:12:14.683 CET ] [Host.startOperation:2651] is Template CDB false

[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2314] Selected Template by user:=General Purpose

[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2321] The Message Id to be searched:=GENERAL_PURPOSE

[main] [ 2014-03-28 10:12:14.683 CET ] [HAUtils.getCurrentOracleHome:490] Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

So the problem is really caused by the different filename/location of the template.

IMHO it’s a kind of bug, the decision between a CDB and NON-CDB should not be taken by DBCA. Moreover, it’s not based on the content of the template, which would be logic. But today I’m late and lazy, I will not open a SR for this.

:-/

SQLServer 2008R2 unattended installation and configuration via powershell

Posted on April 22, 2013 by Ludovico

My first steps on Powershell

Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command prompt.
However, things are changing fast after Microsoft released the Powershell some years ago. It’s surprising, now Windows powershell support new best of breed features like aliases and pipelines. 😀

Today Microsoft itself recommends Windows Core installations instead of the full ones, and also SQLServer 2012 comes with a lot of new Commandlets to manage your server.

So I’ve decided to move my first steps in the Powershell world and I’ve created a script for a customer that installs and configure a SQL2008 with a single Powershell script.

It has been quite painful to complete, you can download the complete script HERE.

Taking parameters

The very first line accepts named parameters. I’ve tried to reduce the number but I’ve preferred to take, as an example, different disks for different tasks.

param ([string] $instance, [string] $collation, $ServiceAccountPassword, $saPassword, $sourceDir, $servicePackExec, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive,  $tempLogDrive, $backupDrive , $port )

1	param ([string] $instance, [string] $collation, $ServiceAccountPassword, $saPassword, $sourceDir, $servicePackExec, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive, $tempLogDrive, $backupDrive , $port )

Then I’ve put a little of interaction if some parameters are missing. In facts, I can launch my scripts without inline parameters and specify everything when prompted by the script.

######################
# Getting Parameters #
######################
if ( -not $sourceDir) { $sourceDir = Read-Host 'Source Path containing SQL2008R2 installation ? ' }
if ( -not $servicePackExec) { $servicePackExec = Read-Host 'Full Path to service pack executable [Empty for None]? ' }
if ( -not $instance) { $instance = Read-Host 'Instance Name? ' }
if ( -not $collation ) {$collation = Read-Host 'Collation [Latin1_General_CI_AS]? ' }
if ( -not $collation ) { $collation = "Latin1_General_CI_AS" }

if ( -not $port ) { $port = Read-Host 'TCP Port [50000]? ' }
if ( -not $port ) { $port = "50000" }

if ( -not $ServiceAccountPassword ) {
    [System.Security.SecureString]$ServiceAccountPassword = Read-Host "Enter the service account password: " -AsSecureString;
    [String]$syncSvcAccountPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword));
} else { [String]$syncSvcAccountPassword = $ServiceAccountPassword; }

if ( -not $saPassword ) {
    [System.Security.SecureString]$saPasswordSec = Read-Host "Enter the sa password: " -AsSecureString;
    [String]$saPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($saPasswordSec));
} else { [String]$saPassword = $ServiceAccountPassword; }

$instance = $instance.ToUpper()

######################

# Getting Parameters #

######################

if ( -not $sourceDir) { $sourceDir = Read-Host 'Source Path containing SQL2008R2 installation ? ' }

if ( -not $servicePackExec) { $servicePackExec = Read-Host 'Full Path to service pack executable [Empty for None]? ' }

if ( -not $instance) { $instance = Read-Host 'Instance Name? ' }

if ( -not $collation ) {$collation = Read-Host 'Collation [Latin1_General_CI_AS]? ' }

if ( -not $collation ) { $collation = "Latin1_General_CI_AS" }

if ( -not $port ) { $port = Read-Host 'TCP Port [50000]? ' }

if ( -not $port ) { $port = "50000" }

if ( -not $ServiceAccountPassword ) {

[System.Security.SecureString]$ServiceAccountPassword = Read-Host "Enter the service account password: " -AsSecureString;

[String]$syncSvcAccountPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword));

} else { [String]$syncSvcAccountPassword = $ServiceAccountPassword; }

if ( -not $saPassword ) {

[System.Security.SecureString]$saPasswordSec = Read-Host "Enter the sa password: " -AsSecureString;

[String]$saPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($saPasswordSec));

} else { [String]$saPassword = $ServiceAccountPassword; }

$instance = $instance.ToUpper()

Before I prompt for the drive letters for the installation paths, I display a little table with the available local disks:

function displayDrives () {
    Get-WmiObject -class "Win32_LogicalDisk" | ?{ @(2, 3) -contains $_.DriveType } | where {$_.Freespace} | select Name, VolumeName, Size, FreeSpace
}

########################
# Getting Disk Letters #
########################

$driveDisplayed=0

if ( -not $instDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $instDrive = Read-Host 'Drive letter (without colon) for Instance Installation? '
}

if ( -not $userDbDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $userDbDrive = Read-Host 'Drive letter (without colon) for User Databases? '
}

if ( -not $userLogDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $userLogDrive = Read-Host 'Drive letter (without colon) for Transaction Logs ? '
}

if ( -not $tempDbDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $tempDbDrive = Read-Host 'Drive letter (without colon) for Temp Database ? '
}

if ( -not $tempLogDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $tempLogDrive = Read-Host 'Drive letter (without colon) for Temp Logs ? '
}

if ( -not $backupDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $backupDrive = Read-Host 'Drive letter (without colon) for Backups ? '
}

function displayDrives () {

Get-WmiObject -class "Win32_LogicalDisk" | ?{ @(2, 3) -contains $_.DriveType } | where {$_.Freespace} | select Name, VolumeName, Size, FreeSpace

}

########################

# Getting Disk Letters #

########################

$driveDisplayed=0

if ( -not $instDrive ) {