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…
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”:
The database configuration has completed successfully, without errors. I’ve accessed my new container, and I’ve been surprised by seing:
Oracle PL/SQL
1
2
3
4
5
SQL>select*fromv$pdbs;
norowsselected
SQL>
In fact, there were no pdb$seed datafiles:
Oracle PL/SQL
1
2
3
4
5
6
7
SQL>selectdistinctcon_idfromcdb_data_files;
CON_ID
----------
1
SQL>
After little investigation, I’ve found these lines in the dbca trace.log:
Oracle PL/SQL
1
2
3
4
5
[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):
In the dbca trace log the message saying that the DB will be NON-CDB disappears:
Oracle PL/SQL
1
2
3
4
[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.
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.
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.
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.
The commented command is to get the installed features after the installation. No really need to display it, it works really well.
Dynamically prepare a configuration file
The unattended installation needs some parameters prepared in a configuration file.
This is likely where you will change most of your stuff depending on your standards:
Components, paths, service accounts, you can change everything or modify the script to accept also this variables as parameters.
The full documentation about filling the configuration file is on the MSDN:
Off course you’ll need an installation media downloaded from the Microsoft customers site with the correct License Keys and mounted somewhere. (remember the $sourceDir parameter?) I’ve decided to change the path in the directory containing the media and then change it back.
The Service Pack installation has been a little more painful, normally would be simple but actually the powershell prompt is returned immediately after firing the command. So, to wait it, I’ve had to figure out the name of the process (is the executable name without the file extension .exe), get its process id and wait for that process:
By default SQLServer starts listening on a dynamic port. If you have a default and you want to configure it without opening the configuration manager, you can do it with this snipplet that I’ve copied from sirSql (thank you for sharing this).
PowerShell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
###############################
# change the TCP port at the end of the installation
"Success: SQL set to listen on TCP/IP port $port. Please restart the SQL service for changes to take effect."
}
Catch{Write-Warning"Unable to enable TCP/IP & set SQL to listen on port $port"}
}
####################
# Changing TCPport #
####################
"Changing TCP port to $port..."
changePort$hostName$instance$port
Adding your stuff at the end
Having the installation completed is in midstream. After the installation you may want to add tempfiles to your installation, modify your model database, add default accounts.
That’s up to you. If your scripts are identical you can execute them with sqlcmd.If you want to take benefit of the variables already set in the script you can execute them directly:
Well, I’ll never paste again all the content here, you can download the script HERE. Just change the file extension from .txt to .ps1.
I know it’s not a rock-solid procedure but it works well for my purposes, feel free to comment or review my script. Just, if you do some improvement on it, please share it and back-link this post!
Cheers
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.