Author Archives: Rich Soule

Using Oracle VirtualBox (or the Oracle Cloud) to Build a DBA Practice Environment

When I get some time I’ll try to change this into a series of blog posts, but for right now here’s a 141 page pdf file that covers the following in a step by step manner:

  • Install and Oracle VirtualBox
  • Create an Oracle Linux Server
  • Install and Configure Oracle Grid Infrastructure for a Standalone Server (with special ‘Rich Soule’ start and stop scripts for using ACFS file systems)
  • Install Oracle 12cR2
  • Create a Multitenant Database
  • Install and Configure Apache/Tomcat/ORDS
  • Install and Configure Oracle Application Express

You can find the pdf here: Using the Oracle Cloud (or Oracle VirtualBox) to build a DBA practice environment.

Advertisements

Oracle Database 12cR2 is here!

oracledatabase12cr2

It’s 15 days early (Oracle Support had said that it would be here on Feb 15th) which means that I’ll be able to start on my ODTUG presentation 15 days earlier than I thought.

Go to http://eDelivery.oracle.com to get the latest and greatest Oracle Database.

Happy DBAing!


Oracle APEX 5.1 Documentation Bug

The installation documentation for Oracle APEX 5.1 has the following code in it for enabling network access from the database in a pre-12c database:

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give apex_050100
  -- the "connect" privilege if apex_050100 does not have the privilege yet.
 
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'apex_050100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'apex_050100', TRUE, 'connect');
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'apex_050100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You’ll notice that the username in the above is ‘apex_050100’. If you run the above code you’ll get the following:

01435. 00000 -  "user does not exist"
*Cause:    
*Action:

Changing each instance of ‘apex_050100’ to ‘APEX_050100’ will address the error and you’ll be able to run the code.

Interestingly enough the new 12c APIs are apparently doing an UPPER on the passed in username because the following code works just fine:

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'apex_050100',
                           principal_type => xs_acl.ptype_db));
END;
/

I’ve logged a bug request with Oracle Support (of course).

Happy APEXing!


Possible ORDS 3.0.9 ‘Doc Bug’

While upgrading a server to ORDS 3.0.9 (from ORDS 3.0.5) and APEX 5.1 I noticed the following output during the ORDS upgrade…

$ java -jar ords.war install advanced
[snip...]
Jan 05, 2017 3:59:43 PM  
 INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
 Upgrading Oracle REST Data Services schema 3.0.5.124.10.54 to version 3.0.9.348.07.16
 ... Log file written to /home/apache/ords_upgrade_2017-01-05_155943_00336.log
 Upgrading ORDS schema to 3.0.6
 Completed upgrade for Oracle REST Data Services version 3.0.9.348.07.16.
Elapsed time: 00:00:16.437 

You might notice that the output has “Upgrading ORDS schema to 3.0.6” even though the version of ORDS is 3.0.9.

Of course the following query does return the right value and ORDS is the correct version:

select * from ords_metadata.ords_version;
VERSION                      
------------------------------
3.0.9.348.07.16

 


Oracle Application Express 5.1 on apex.oracle.com

It looks like we are getting even closer to an Apex 5.1 release as apex.oracle.com just got upgraded to 5.1. Application Express 5.1 has a number of really nice features in it.

oracleapex51

A simplified form wizard, new Theming and the Interactive Grid are some of the new 5.1 features. We are all really looking forward to the release of Apex 5.1.

You can test these features out at https://apex.oracle.com/ where you can get a free account to play with.


catconInit: database is not open on the default instance

If you’ve run into the catconInit: database is not open on the default instance error and didn’t immediately understand what was going on, then this post is for you.

It’s not that common of an error as you have to be in a very specific configuration to see it: You need to be using either Oracle RAC or RAC One Node and you also have to be using the Oracle Multitenant option and you have to have your ORACLE_SID environment variable set to something other than the name of the instance where you are running your command.

catcon.pl is a Perl program that takes a few arguments including the name of a SQL (or PL/SQL) script and then it runs that code in the root container and each of the online pluggable containers (which includes PDB$SEED even though PDB$SEED isn’t in read/write mode).

For example, if you attempt to remove the version of APEX that is shipped with the 12c database (APEX 4.x) from the root and pluggable containers (see this from post by Mike Dietrich on why you should do that) then you’ll need to use the apxremov_con.sql script which is a wrapper for catcon.pl. If your environment isn’t set correctly, then you’ll run into the above issue.

Let’s talk a bit about the environment.

When you create a RAC database then Oracle adds an entry into your /etc/oratab (or /var/opt/oratab if you happen to be on Solaris) that has your database name in it.I’m not sure what happens on Windows as I don’t generally run Oracle Databases on Windows servers.

If you created a two node RAC cluster with a database named orcl, you’d have the following: A database on shared storage named orcl (the database in this case referring to the actual data files and other files), and then two instances (instances being the background processes and memory structures running on each node), each running on a different node. By default those instances would be named orcl_1 and orcl_2.

The catconInit: database is not open on the default instance error happens when the above default configuration gets in the way…

When using the oraenv (on Linux/Unix with bash as your shell) or coraenv (if you are using csh) script you get prompted for an ORACLE_SID. The value you supply is looked up in the /etc/oratab file and is used to set all kinds of environment variables. If the oraenv script can’t find an entry in the /etc/oratab file then you are also prompted for an ORACLE_HOME. An example /etc/oratab entry is below:

prodcon:/u01/app/oracle/product/12.1.0.2/dbhome_1:N             # line added by Agent

So now you attempt to run your apxremov_con.sql script…

[oracle@prododa0 ~]$ . oraenv
 ORACLE_SID = [oracle] ? prodcon
 The Oracle base has been set to /u01/app/oracle
 [oracle@prododa0 ~]$ cd $ORACLE_HOME/apex
 [oracle@prododa0 apex]$ sqlplus sys@prodcon as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 22 15:31:25 2016
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Enter password:
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
 and Real Application Testing options
 
 SQL> @apxremov_con.sql
 PL/SQL procedure successfully completed.
 Performing installation in multitenant container database in the background.
 The installation progress is spooled into apxremov*_con*.log files.
 Please wait...
 catcon: ALL catcon-related output will be written to apxremov1_con_catcon_88654.lst
 catcon: See apxremov1_con*.log files for output generated by scripts
 catcon: See apxremov1_con_*.lst files for spool files, if any
 catconInit: database is not open on the default instance
 Unexpected error encountered in catconInit; exiting

The issue is that catcon.pl requires your ORACLE_SID to be set not to the database name as we did above, but instead to the instance name. You can use either a bequeath connection (sqlplus / as sysdba) or an Oracle Net connection (sqlplus sys@prodcon as sysdba) as long as your ORACLE_SID is set correctly for the node you are running the command on.

[oracle@prododa0 apex]$ . oraenv
 ORACLE_SID = [prodcon] ?
 The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@prododa0 apex]$ echo $ORACLE_HOME
 /u01/app/oracle/product/12.1.0.2/dbhome_1
 [oracle@prododa0 apex]$ . oraenv
 ORACLE_SID = [prodcon] ? prodcon_1
 ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0.2/dbhome_1
 The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@prododa0 apex]$ sqlplus sys@prodcon as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 12:25:00 2016
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Enter password:
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
 and Real Application Testing options
 SYS@prodcon AS SYSDBA> @apxremov_con.sql
 PL/SQL procedure successfully completed.
 Performing installation in multitenant container database in the background.
 The installation progress is spooled into apxremov*_con*.log files.
 Please wait...
 catcon: ALL catcon-related output will be written to apxremov1_con_catcon_5772.lst
 catcon: See apxremov1_con*.log files for output generated by scripts
 catcon: See apxremov1_con_*.lst files for spool files, if any
 catcon.pl: completed successfully
 catcon: ALL catcon-related output will be written to apxremov2_con_catcon_6339.lst
 catcon: See apxremov2_con*.log files for output generated by scripts
 catcon: See apxremov2_con_*.lst files for spool files, if any
 catcon.pl: completed successfully
 Installation completed. Log files for each container can be found in:
 apxremov*_con*.log
 You can quickly scan for ORA errors or compilation errors by using a utility like grep:
 grep ORA- *.log
 grep PLS- *.log
 SYS@prodcon AS SYSDBA>

The Oracle documentation on catcon.pl isn’t exactly clear that you can run into this issue. I’ve logged a doc bug against the documentation so hopefully this will be clearer in the future.

 

 


Oracle 12.1.2.7 Virtualized ODA Install

I’ve been working with the brand new 12.1.2.7 ODA update on an X5-2 and Oracle has made a number of changes that impact how an install and configuration is done.

12.1.2.7 is the first release that supports a ‘reflash’ of an ODA with Oracle Linux 6. Previously update 12.1.2.5 had Oracle Linux 5 and updated 12.1.2.6 had Oracle Linux 6, but the only way to move to 12.1.2.6 was to first already be at 12.1.2.5 and then patch to 12.1.2.6.

12.1.2.7 is the first release that will support (re-)deployment of an ODA in a virtualized configuration where you load the Oracle VM ISO into each ILOM and then deploy the ODA_BASE virtual machine. Note that if your ODA was at 12.1.2.6 or earlier you will still have to download the 12.1.2.7 patch to ODA_BASE after you deploy ODA_BASE to patch some of the server and storage components. Oracle has changed from the old patch –infra, –grid, –database options and now has a –server (that does some hardware and the grid infrastructure) and –storage (which patches the shared storage hardware).

One very major change which is especially important for those of us who manage virtualized ODA installs remotely is that /etc/xen/xend-config.sxp on ODA_BASE now has the following section:

# The interface for VNC servers to listen on. Defaults
# to 127.0.0.1  To restore old 'listen everywhere' behaviour
# set this to 0.0.0.0
#(vnc-listen '127.0.0.1')

This means that you when you attempt to create a VM and connect to the console the same way you did in past ODA versions you’re probably going to get something like this:

[root@oda-base-node0 ~]# oakcli show vmconsole OL7U2 
 OAKERR:8006 Error in VNC display configurations : DISPLAY=localhost:10.0

You can either modify the /etc/xen/xend-config.sxp file, or you can update the vm.cfg file for a particular VM that you’ve imported. Since the ODA will be using ACFS it might be a bit hard to find the vm.cfg file since it ends up in the hidden .ACFS directory on the shared repository ACFS file system.

I had created my VMs on a shared repository named sharedrepo so my vm.cfg file ended up here:

[root@oda-base-node0 OL7U2]# pwd 
 /u01/app/sharedrepo/sharedrepo/.ACFS/snaps/OL7U2/VirtualMachines/OL7U2 
[root@oda-base-node0 OL7U2]# vim vm.cfg

Update the vm.cfg file and add the following:

vnclisten = '0.0.0.0'

Note that vnclisten ‘0.0.0.0’ didn’t work and neither did vnclisten = 0.0.0.0. It had to be vnclisten = ‘0.0.0.0’ for the VM to start.

Now that you’ve got a ‘listen anywhere’ vnc console running, you can just use a VNC client on your workstation (I used Tiger VNC) and connect to your node0-dom0:590X where X is the number of VMs you’ve started past ODA_BASE.  ODA_BASE is at :5900 and if this is the first additional VM you’ve created you’ll be able to reach the console at :5901. Note that once you’ve done the initial network configuration of your VM you’ll probably want to take the vnclisten parameter out of the vm.cfg file.

I’m also currently working with Oracle support on an issue. We’ve got a RAC One Node multitenant database and catcon.pl won’t run scripts in the ‘root container and all pluggables’. For example:

[oracle@oda-base-node0 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b utlrp_output utlrp.sql 
 catcon: ALL catcon-related output will be written to utlrp_output_catcon_77673.lst 
 catcon: See utlrp_output*.log files for output generated by scripts 
 catcon: See utlrp_output_*.lst files for spool files, if any 
 Enter Password: 
 catconInit: database is not open on the default instance 
 Unexpected error encountered in catconInit; exiting

This is important because apxremov_con.sql is just a wrapper for catcon.pl so you can’t remove APEX from the root container (CDB$ROOT) and PDB$SEED so that new pluggable databases won’t have APEX in them. We do this so that we’ll be able to potentially have different versions of APEX in different pluggable databases in the same container database.

Oracle has opened a P1 bug on this and has actively been working this for a few days.

Happy ODAing!

Rich