PowerCLI: Automate Oracle DB install using Invoke-VMScript

I only recently discovered how cool and powerful is Invoke-VMScript, a VMware PowerCLI cmdlet that allows you to run scripts inside the guest VM operating system.
It requires VMware Tools installed and running inside the guest OS in order to successfully inject commands and scripts from the ESXi host to the VM and it is noteworthy to say that not all operating systems are supported by Invoke-VMScript cmdlet.
Supported ones, according to official documentation, are: Windows XP 32bit SP3, Windows Server 2003 32bit SP2, Windows Server 2003 64bit SP2, Windows 7 64bit, Windows Server 2008 R2 64bit and RHEL 5.

Invoke-VMScript syntax is:

Invoke-VMScript [-ScriptText] <String> [-VM] <VirtualMachine[]> [-HostCredential <PSCredential>] [-HostUser <String>] [-HostPassword <SecureString>] [-GuestCredential <PSCredential>] [-GuestUser <String>] [-GuestPassword <SecureString>] [-ToolsWaitSecs <Int32>] [-ScriptType <ScriptType>] [-RunAsync] [-Server <VIServer[]>] [-WhatIf] [-Confirm] [<CommonParameters>]

Where:

HostCredential: username and password for the ESXi host on which resides the virtual machine.
GuestCredential: username and password for the guest OS. The user must have administrative access.
ScriptType: what kind of script you want to invoke: PowerShell, Bat or Bash.

In this article I provide a simple PowerCLI script which allows you to deploy virtual machines from a Win2008 R2 template and perform an automated silent install of Oracle Database 11g R2.

This is just an example of Invoke-VMScript usage and this only provides a software install, neither listener neither instance are configured. This script could also be readapted/improved for RHEL installs.

How this script works:

-Connects to a vCenter or ESXi host.
-Deploys "N" virtual machines from a properly prepared (read a few lines below to find how) Win2008 template.
-Power on the virtual machine.
-Perform a silent, fully automated, install of Oracle Database 11g R2 software by using Invoke-VMScript cmdlet.

Preprequisite for this PowerCLI script are:

-A Windows 2008 R2 virtual machine
-Oracle Database 11g R2 11.2.0.X. Unzip installation files on the Desktop. This script assumes that your installation directory is placed on: C:\Users\Administrator\Desktop\database
-Response file: a response file is a text file which is used by Oracle to perform silent installs. It contains all infos that usually are inserted by the user in a common GUI installation. This script assumes that your response file is: C:\Users\Administrator\Desktop\install.rsp

This is the content of my install.rsp response file.

 oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0  
 oracle.install.option=INSTALL_DB_SWONLY  
 UNIX_GROUP_NAME=oinstall  
 INVENTORY_LOCATION=C:\Program Files\Oracle\Inventory\logs  
 ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1  
 ORACLE_BASE=C:\app\Administrator  
 oracle.install.db.InstallEdition=EE  
 oracle.install.db.isCustomInstall=false  
 oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0  
 oracle.install.db.DBA_GROUP=dba  
 oracle.install.db.OPER_GROUP=dba  
 SECURITY_UPDATES_VIA_MYORACLESUPPORT=false  
 DECLINE_SECURITY_UPDATES=true  

So, to recap: unzip Oracle DB installation files on the Desktop and place install.rsp in the same location as depicted by the following image.

This is the PowerCLI code, as usual you can also find it on my GitHub repository: Invoking Scripts in Guest-VM.ps1



Run it and deployment of virtual machines from template begins:

After script completion VMs are correctly added to vCenter Server inventory

and Oracle Database is installed inside guest OS.

To perform a full install of Oracle Database the listener and an instance must also be configured. This can be done using a response file and run using PowerCLI Invoke-VMScript.

If you need a standard listener configuration netca.rsp file provided in database\response installation directory does the job.

Listener silent install is performed by running:

 netca -silent -responseFile C:\Users\Administrator\Desktop\database\response\netca.rsp  

Similarly database instance can be added by invoking the Database Configuration Assistant (dbca) in silent install mode using a response file or passing all required configurations as parameters.

For example:

 dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl -sid orcl -gdbName orcl -memoryPercentage 30 -sysPassword welcome1 -systemPassword welcome1 -dbsnmppassword welcome1 -emConfiguration LOCAL -redoLogFileSize 100 -storageType FS -datafileDestination C:\app\Administrator\oradata -datafileJarLocation C:\app\Administrator\product\11.2.0\dbhome_1\assistants\dbca\templates -characterSet AL32UTF8 -sampleSchema false -totalMemory 512  

That's all!!

VMware: Configure Oracle DB 12c for vCloud Director

With latest 12c release Oracle radically changed how databases are managed by introducing the concept of Pluggable Database. What's that? A pluggble database, or simply "PDB", is basically a database contained in another database which acts like a root container. This brings-in multitennancy capabilities in database without having to create a different instance for every tennant.

This new concept has slightly changed the way you perform tasks such as creating users, tablespaces and managing roles so I decided to come up with this post exaplaining how to set up an Oracle Database 12c for vCloud Director.

Prior to start please have a look at official vCloud Director Configure an Oracle Database documentation.

vCloud Director requires some tasks to be performed on Oracle Database:

-Create two tablespaces: CLOUD_DATA, CLOUD_INDX
-Create a user which will be using these tablespaces
-Assign certain permissions to this user
-Set up properly database initialization parameters

In Oracle Database 12c Enteprise Manager has been replaced by Enterprise Manager Express. An extremely slimmed-out version of 'ol classic EM.
Enterprise Manager Express is enabled by default only on root container and not on PDBs. After database installation completed a screen reported Enterprise Manager Express URL, which was something similar to this:

https://<IP_Address_or_FQDN>:5500/em

Where the port 5500 can be easily customized. For example in Oracle 11g by default it was port 1158.

Following screen depicts Enterprise Manager Express console for root container:

As you can see Type reports ORCL as single instance database having 1 PDB, furthermore browsing upper screen menu no tablespace item exists. Tablespaces will be created in PDBs.

Despite all commands can be performed using SQL may be conforting having a GUI on which you can check if everything has been created/modified as expected so let's first enable Enterprise Manager Express on PDBs.

This is done issuing a SQL command so open SQLPlus and connect to database as sysdba.

 connect sys/<password>@<container> as sysdba  

SQL> connect sys/mypassword@ORCL as sysdba

Connected

To issue commands directly to PDB and not to root container you need to alter the scope of your SQL session. This can be done with following command:

 alter session set container=<PDB_name>;  

SQL> alter session set container=PDBORCL;

Session altered.

To enable Enterprise Manager Express console for PDB:

 exec dbms_xdb_config.sethttpsport(<EM_Port>);  

SQL> exec dbms_xdb_config.sethttpsport(5501);

PL/SQL procedure completed

EM console is now available even for PDB and as you can see here Tablespace menu item has appeared:

Let's now perform vCloud Director's related configurations as reported in official documentation.

Tablespace creation can be done either via Enterprise Manager Express or by issuing SQL commands. If done via SQL ensure you are in the correct PDB scope:

 alter session set container=<PDB_name>;  

Create CLOUD_DATA and CLOUD_INDX tablespaces.

Edit $ORACLE_HOME variable according to your current home directory which in my case, on a Windows Server, is:

C:\app\Administrator\

 Create Tablespace CLOUD_DATA datafile '$ORACLE_HOME/oradata/cloud_data01.dbf' size 1000M autoextend on;  
 Create Tablespace CLOUD_INDX datafile '$ORACLE_HOME/oradata/cloud_indx01.dbf' size 500M autoextend on;  

Create a new user who will access these tablespaces:

 Create user <vcloud_user> identified by <vcloud_user_password> default tablespace CLOUD_DATA;  

And assign to it the following privileges:

- CONNECT (already granted to default role)
- RESOURCE (already granted to default role)
- CREATE TRIGGER
- CREATE TYPE
- CREATE VIEW
- CREATE MATERIALIZED VIEW
- CREATE PROCEDURE
- CREATE SEQUENCE

This can be done either via SQL or via Enterprise Manager Express.

Since vcloud user relies on default profile and will inherit default profile's attributes it could be a good move to change default password expiration lease of 180 days.

Last but not least we need to size our database parameters according to the ones suggested by VMware in vCloud Director documentation.

- CONNECTIONS = 75*<Number_of_vCloud_Director_Cells>+50
- PROCESSES = CONNECTIONS
- SESSIONS = PROCESSES*1.1+5
- TRANSACTIONS = SESSIONS*1.1
- OPEN_CURSORS = SESSIONS


As usual this is feasible either via SQL or via Enterprise Manager Express.

If done via SQL synthax is the following:

 alter system set <parameter>=<value> scope=<memory_AND/OR_spfile> sid=<sid>  

For example:

alter system set open_cursors=500 scope=both sid='*';

will change open_cursors parameters to 500, this will apply to both memory (changes not consistent across reboots) and spfile (changes consistent across reboots) and will affect all instances, including nested PDBs.

Please bear in mind that some configuration parameters must be edited at root container level and cannot be modified at PDB level.

This is well shown using Enterprise Manager Express, as following image depicts, processes parameter cannot be modified at PDB level.

Editing a parameter at both root DB or PDB using Enterprise Manager Express is really simple. Just go to Configuration -> Parameters, browse for the parameter you need to edit and click Set icon.

That's all!!

Oracle Database: PRVF-0002 Could not retrieve local nodename

If you encounter this error while installing Oracle Database 12c:

PRVF-0002 Could not retrieve local nodename

Having a look at Oracle's error documentation it simply tells us:

Cause: Unable to determine local host name using Java network functions. 
Action: Ensure hostname is defined correctly using the 'hostname' command.

Here's how to solve it:

This is the standard file hosts

[root@vm1 ~]# nano /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

If you ever installed Oracle DB 11g you probably know you need to edit hosts file adding the current machine IP address and hostname. So file hosts resulted in something similar to:

[root@vm1 ~]# nano /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 
192.168.116.230 vm1 vm1.localdomain

Unfortunately in Oracle DB 12c this is not enough, to solve the error above you need to point loopback address to the machine hostname.

[root@vm1 ~]# nano /etc/hosts

127.0.0.1   vm1 vm1.localdomain localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         vm1 vm1.localdomain localhost localhost.localdomain localhost6 localhost6.localdomain6 
192.168.116.230 vm1 vm1.localdomain

That's all!!

Oracle Database: Unlock user account via SQLPlus

As stated in "Oracle Database: Change Password Expiration" when installing DB I usually change password expiration for DEFAULT profile from 180days to UNLIMITED.

This is because if you create users that uses DEFAULT profile after 180days Oracle gives you:

ORA-28001: the password has expired

So...you can edit DEFAULT profile in two ways:

1) Via Enterprise Manager Console as stated in  Oracle Database: Change Password Expiration
2) Via SQLPlus

In this post I explain how to recover a locked user via SQLPlus and set DEFAULT profile with an unlimited password expiration time.

Connect to the instance:

[oracle@oracle ~]$ sqlplus

SQL> conn sys/password@sid as sysdba

SQL> select username,account_status from dba_users;  


The above command will return all dba users and their corresponding status presenting them in a view similar to this:

USER1 LOCKED&EXPIRED
USER2 OPEN 

Now let's unlock the user(s):

SQL> alter user [user_name] identified by [password] account unlock;

Then modify DEFAULT profile setting the password expiration time to UNLIMITED:

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

That's all!!

Oracle Database: Schedule backups using exp

Despite I recommend taking backups using RMAN (Oracle Database: Schedule hot backups using RMAN) sometimes taking backups using EXP utility could be useful.

The main advantage of using EXP utility instead of RMAN is that the former (EXP) allows to take easy backups of specific tables and doesn't require the database to be in ARCHIVELOG mode to run.

Conversely RMAN it's faster than EXP because it doesen't require to examine every data block of the backup source, allow parallelized backups/restores, has a catalog for tracking backups, etc.

Set up EXP backups it's really simple, let's create a script which will be added to our crontab to perform EXP backups daily at 8PM.

I created a file called backup under /home/oracle with this content:

export DATE=$(date +"%m_%d_%y_%H:%M")

/u01/app/oracle/product/11.2.0/db_1/bin/exp username/password@instance_name file=/home/oracle/backup/backup_$DATE.dmp log=/home/oracle/backup/backup_$DATE.log statistics=none

First line creates a variable called DATE which is set as the current system date in month,day,year,hour,minute format.

Second line performs the backup calling EXP utility and passing following parameters:

username/password@instance_name tells EXP what to backup

file=/home/oracle/backup/backup_$DATE.dmp tells EXP where to save backup

log=/home/oracle/backup/backup_$DATE.log tells EXP where to save backup log

statistics=none tells EXP not to save statistics related to objects we are exporting

To schedule EXP backup using crontab:

[root@db1 ~]# crontab -e -u oracle

I will schedule backup everyday at 20:00 (8.00 PM)

00 20 * * * /home/oracle/backup

The first five fields are:

minute (0-59)
hour (0-23)
day of the month(1-31)
month of the year (1-12)
day of the week (0-6 with 0 = Sunday)

And the last parameter is absolute path to my backup script.

For more info on crontab have a look at crontab.org

NOTE: If you are unable to edit crontab file set EDITOR variable according to your preferred file editor.

In my case:

[root@db1 ~]# export EDITOR=nano

[root@db1 ~]# crontab -e -u oracle

That's all!!

Oracle Database: some SQL useful commands

In this brief post I list some SQL/SQLPlus commands I find pretty useful.

-Quick connect as sysdba:

[oracle@oracle ~]$ sqlplus / as sysdba

-Connect as sysdba to a specific db instance:

If more than one instance is running on same machine you can specify to which instance you wish to connect to using:

[oracle@oracle ~]$ sqlplus

Once logged in specify instance with this syntax:

SQL> conn sys/password@sid as sysdba

Connected.

-Drop user and user owned objects:

To drop a user and any database objects owned by user itself just use this command:

SQL> DROP  USER  user  CASCADE;

Oracle Database: Schedule hot backups using RMAN

In this article I will schedule periodic hot backups of a running database instance using rman.

To perform hot backups using rman, first you need to check if your database is in ArchiveLog mode.

[oracle@db1 ~]$ sqlplus "/ as sysdba"

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

If not change it:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

Once it's in ArchiveLog mode create the main rman script which will be called by crontab:

[oracle@db1 ~]$ vi /u01/rman_main

ORACLE_HOSTNAME=db1
export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl
export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID

/u01/app/oracle/product/11.2.0/db_1/bin/rman target=/ @/u01/rman_script

Where ORACLE_HOSTNAME is your machine hostname and ORACLE_UNQNAME and ORACLE_SID is the SID of the instance you need to backup.

[oracle@db1 ~]$ chmod a+x /u01/rman_main
Then we create the rman script itself:

[oracle@db1 ~]$ vi /u01/rman_script

run {
  allocate channel ch1 type disk format '/u01/backup%d_DB_%u_%s_%p';
  backup database;
  backup archivelog all;
  release channel ch1;
}


This will backup the database creating a file under "/u01/" directory.
In the above script %d, %u, %s, %p are substitution variables for generating unique file name, if you wish to know more about these variables have a look HERE.

You could manually run rman_main to take hot backups, but if you like to run scheduled backups we need to add this to crontab:

[root@db1 ~]# crontab -e -u oracle

I will schedule backup everyday at 18:10 (6.10 PM)

10 18 * * * /u01/rman_main

The first five fields are:

minute (0-59)
hour (0-23)
day of the month(1-31)
month of the year (1-12)
day of the week (0-6 with 0 = Sunday)

For more info on crontab have a look at crontab.org

NOTE: At first I was unable to edit this file, so after a brief search I had to set EDITOR variable according to my preferred file editor.

In my case:

[root@db1 ~]# export EDITOR=nano

[root@db1 ~]# crontab -e -u oracle

That's all!!

Oracle Database: Start EM console for multiple instances on the same server

Today I had to manually start Enterprise Manager console for an instance running on a server which has already other instances running.

The command to start EM Console is:

emctl start dbconsole

full path of the command is:

/u01/app/oracle/product/11.2.0/db_1/bin/emctl start dbconsole


This start EM for instance referred in ORACLE_SID variable.

In case of multiple instances running on same server you can start EM console by assigning ORACLE_SID variable according to the EM you need to start.

For example if we need to start EM console for "TEST1" instance we set ORACLE_SID:

[oracle@orcl ~]# ORACLE_SID=TEST1

[oracle@orcl ~]# emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://orcl:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ........................ started.
---------------------------------Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/orcl_TEST1/sysman/log

Same thing if we need to start EM for "TEST2" instance:


[oracle@orcl ~]# ORACLE_SID=TEST2

[oracle@orcl ~]# emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://orcl:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ........................ started.
---------------------------------Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/orcl_TEST2/sysman/log


Thats'all!!

Oracle Database: Change Password Expiration

Recently I went into this problem: Oracle WebCenter Content refused to start due to a password related problem.
After an investigation it turned out that account was locked due to password expiration.

In this post I explain a preliminary step that I didn't found in any Oracle guide (correct me if I'm wrong) but it's essential prior, or just after, installing any Oracle Middleware application.

Since Middleware applications requires a database connection, as you know, before installing one you will need to use an application called "Repository Creation Utility" that will create database schemas and users, owners of these schemas.

So, let's assume you are about to install Oracle WebCenter Content schemas using RCU (abbreviation of "Repository Creation Utility")



After schema creation you need to log into Database Enterprise Manager that can be reached at a similar address:

https://DATABASE_SERVER_IP_ADDRESS:1158/em

If you browse Users and look for the ones just created, identified in my case by prefix "DEV_", you will see that they use DEFAULT profile.

This profile has a password expiration set to 180 days that we need to change to unlimited.
Go to Server -> Profiles



Select DEFAULT profile



As you can see password expiration is set to 180 days, click Edit button

and set to UNLIMITED the field "Expire (in days)".



Note that if you have some locked accounts you can unlock them simply via SQLPlus using the following commands:

alter user username account unlock;

alter user username identified by new_password;

Oracle Database: How to enable Archive Mode

Some days ago I went into this error while trying to perform a backup of a running 11gR2 database using rman:

ORA-196602: cannot backup or copy active file in NOARCHIVELOG mode

The solution was easy: ArchiveLog needs to be enabled to perform a hot backup of a running database.

Using SQLPlus connect to database.

[oracle@orcl ~]$ /u01/app/oracle/product/11.2.0/db_1/bin/sqlplus sys/password as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 09:32:39 2012

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


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Stop and dismount database and shutdown instance.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
Restart instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2228904 bytes
Variable Size            1325403480 bytes
Database Buffers          318767104 bytes
Redo Buffers                7118848 bytes
Database mounted.
Enable ArchiveLog

SQL> alter database archivelog;

Database altered.
Re-open database

SQL> alter database open;

Database altered.
If everything went fine the following command should return Archive Mode as output:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     294
Next log sequence to archive   296
Current log sequence           296