Thursday, 28 January 2016

Oracle VM errata released 27 Jan 2016

Oracle VM errata

Download 7.5Gig from

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Get back to me if there are any issues so I can make a better one.

Should have told people:

(I am a developer on (sqlcl/sqldeveloper) user of (Oracle Database/jdbc/java/Oracle Linux/bash) so my biases are in that order) 

Introduction to Oracle Database? Not used them myself but...
(TODO: Need to check links from outside Oracle)
Oracle doc - Oracle Database Concepts - https://docs.oracle.com/database/121/CNCPT/toc.htm 
high level and useful view of the Oracle Database.

Oracle doc was reorganized to include
2 day dba https://docs.oracle.com/database/121/ADMQS/toc.htm
2 day developer https://docs.oracle.com/database/121/TDDDG/title.htm
guides.

Fancy new database stuff in Oracle Database 12c:
multitenant - pluggable databases.
32k varchar2
in memory option

1/(Oracle software: database 12.1.0.2.13 (i.e. patched as of October/November) (APEX/ORDS/sqldev/sqlcl/modeller/labs) - as of Christmas 2015 (or later) Oracle Linux as of November ).

2/Check the licensing (I am not a lawyer...)

3/Oracle Linux 7.1 - you might be able to (put a proxy in /etc/yum.conf if necessary) and yum update -> Oracle Linux 7.2. I had to remove some old kernels so they would not break the update to 7.2 - I think they are removed in the final VM so you should not have kernel issues.

4/Virtualbox extras - 4.0.30 - you might be able to updated to 5.x (i.e. dependencies - kernel headers installed)
4.0.30 should work - with warnings - in 5.

5/Defaults to 2GigRAM/1CPU - that is the bare minimum - update possible on import or settings after import. 

6/It port forwards from the host so:
sqlplus system/oracle@localhost:1521/orcl12c
and
sqlplus system/oracle@localhost:1521/orcl
should work on host (if you have sqlplus installed)  or guest - to connect to the guest database

7/Other new new stuff: sqlcl [startup script sql] 11 meg download (requires java) acts sort of like sqlplus 

(Download http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html or run from guest:)
sql system/oracle@localhost:1521/orcl12c
and
sql system/oracle@localhost:1521/orcl

8/ If you are not familiar with Oracle:
Example sql to confirm connection:
select 1 from dual;
[dual is a fake 1 row table in Oracle].

9/Sqldeveloper home:
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

Forum:
https://community.oracle.com/community/database/developer-tools/sql_developer

10/Other Oracle Database downloads (check the licenses) 
- other VMs
- full Oracle Database
- Oracle database XE 11,2 (Easy install/'Cut down' 'slightly more lenient' license)
- instant client including sqlplus

11/Setup decisions:

TWO_TASK=ORCL is set in .bashrc
wrapper script ~/bin/sql - not used (further down the path)
(Other wrapper scripts are to ensure .bashrc are set for PATH/Java/Database environmental variables when for example called from icon click)
APEX 5 installed on pdb (rather than cdb)
varchar2(32k) is on
Default system connection in sqldeveloper is for pdb (pluggable database).

What can go wrong:
-VM might not start properly - looks like a timing issue on slow machine. (I can eventually ssh in but UI broken) - 'ssh oracle@localhost' in and 'sudo shutdown -r now' might help.
-Overlap of icons occasionally
-Some things missing due to desire to reduce size of VM -  for example the database creation DBCA assistant removed,
-patches applied to seed  (rather than (as seen in the documentation) not patch seed and get dba to patch new pluggable databases on creation).
-ORDS startup shutdown script will be confused with two ORDS running (1 startup ords and 1 labs ords (launched from sqldeveloper) for example).

Links:
For fancy stuff (SQLDeveloper) developer/manager blogs see:

For example: javascript and aliases in sqlcl

http://krisrice.blogspot.com
http://barrymcgillin.blogspot.co.uk
http://www.thatjeffsmith.com/sql-developer/
http://dermotoneill.blogspot.co.uk/
http://totierne.blogspot.com

Thursday, 7 May 2015

Ways of connection - sqlcl and the connect command

Where it looks for connections is a bit long winded:
Check -optl if this does not quite match what you are seeing.

Variables we support:
TNS_ADMIN (for finding tnsnames.ora)
LOCAL (windows=TWO_TASK)
TWO_TASK (non windows i.e. my database connection information e.g. tnsnames.ora entry name or EZCONNECT)
ORACLE_HOME (for finding tnsnames.ora)
SQLPATH (for finding login.sql)
PATH for finding .dll
LD_LIBRARY_PATH for finding .so
ORACLE_SID used in bequeath connection by thick oci driver.


tnsnames.ora - looks for tnsnames.* for example so might pick up an old setting in a .SAV file.

connect -oci username/password
nothing given:if currently connected try same utl different connection.
nothing given:not currently connected and -oci try bequeath

try TWO_TASK/LOCAL (if these are not null go to  'something given')
try localhost:1521/orcl
try 127.0.0.1:1521:$ORACLE_SID

if something given
try LDAPCON environment variable http://totierne.blogspot.in/2015/02/introducing-ldapcon-helper-feature-for.html
try tnsnames.ora
try NET xml store of connections http://totierne.blogspot.in/2015/04/net-command-persistently-store-network.html
try passing on to the driver untouched (could be (DESCRIPTION= or hostname resolution for example)
try hostname resolution ie @hostname or @hostname:1521/hostname (in case hostname=service name) (try with or without domain eg oracle.com - or domain got from looking up resolved host)
(if -oci try thick then try thin for each possibility (there are one or two that currently do not try thick))

On no connection give error output for each attempt. (optl shows what it is trying whether it connects or not).

If password is missing prompt for it, if connection fails and there was a prompt - prompt for everything up to 3 times.
At the username prompt: user@dbname is not currently supported.

SQLCL command line options

SQLCL options:

new:
-oci - try looking for 12.1.0.2 oci 'thick' c based library
-optl - turn on logging
-sshkey -sshhost I have not used these could be related to: http://barrymcgillin.blogspot.in/2015/05/sqlcl-cloud-connections-via-secure.html seems related.

From sqlplus
-h -help - show help page
-l -login - do not reprompts for user/password on failure
-R[estrict] 1 -R 2 -R 3 - restrict sqlcl for example R 3 - do not use @
-s[ilent] - suppress console output
-v - version
/nolog - do not log in
-nologin - do not read login.sql
@file.sql - run this file and come back if it does  not end in exit
username/password@connectionidentifier as sysdba|sysbackup|sysdg|syskm|sysasm|sysoper - login ([as role] is optional)

-optl shows you logging info:
where it looks for tnsnames.* and what urls it is trying
$ ./sql -optl notauser/notapassword@notanid

SQLcl: Release 4.1.0 Release Candidate on Thu May 07 14:34:11 2015

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


May 07, 2015 2:34:11 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/home/notauser
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/etc
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid"
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid:1521/notanid"
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid
  Error Message = IO Error: Unknown host specified
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid:1521/notanid
  Error Message = IO Error: Unknown host specified

Warning: You are no longer connected to ORACLE.

now try the thick driver: -oci (throws an error because I have  not got the .so  library in the path)
./sql -oci -optl notauser/notapassword@notanid

SQLcl: Release 4.1.0 Release Candidate on Thu May 07 14:36:10 2015

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


May 07, 2015 2:36:10 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/home/notauser
May 07, 2015 2:36:10 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/etc
May 07, 2015 2:36:10 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:oci8:@notanid"
May 07, 2015 2:36:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid"
May 07, 2015 2:36:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid:1521/notanid"
  USER          = notauser
  URL           = jdbc:oracle:oci8:@notanid
  Error Message = no ocijdbc12 in java.library.path
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid
  Error Message = IO Error: Unknown host specified
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid:1521/notanid
  Error Message = IO Error: Unknown host specified

Warning: You are no longer connected to ORACLE.

Next post will be about how it attempts to connect.

Tuesday, 5 May 2015

Pause prompt works per page:

bash-4.1$ bash sql scott/[the password]@[THE DATABASE}

SQLcl: Release 4.1.0 Release Candidate on Tue May 05 16:21:03 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.x.x.x.x - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> set pause "Press any key to continue..."

SQL> set pause on

SQL> show pause
PAUSE is ON and set to "Press any key to continue..."

SQL> show pagesize
pagesize 24


SQL> select * from emp;

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                      
       800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                      
      1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                      
      1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81                      
      2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81                      
      1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81                      
      2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81                      

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87                      
      3000                    20

      7839 KING       PRESIDENT            17-NOV-81                      
      5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81                      
      1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                      
      1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81                      
       950                    30

      7902 FORD       ANALYST         7566 03-DEC-81                      
      3000                    20

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82                      
      1300                    10


 14 rows selected


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.x.x.x.x - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Wednesday, 29 April 2015

Command Line: sqlcl startup shutdown of pdb and non pdb databases (Should even work from a Mac - pure Java)

Tested against release 16th April 2015 drop.

Startup and shutdown of pluggable and non pluggable datbases.

bash-4.1$ ./sql sys@_non_pluggable__

SQLcl: Release 4.1.0 Release Candidate on

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


Connected to an Idle instance, startup command available.
SQL> startup
Started up
Database Mounted
Database Open

Total System Global Area   12345 bytes    
Fixed Size                    54321 bytes    
Variable Size               12345 bytes    
Database Buffers            54321 bytes    
Redo Buffers                 12345 bytes    
SQL> shutdown
Instance stopped
Database closed.
Database dismounted.
Database shutdown.
SQL> connect sys@__pluggable__ as sysdba
Connected

SQL> shutdown
Pluggable database closed
SQL> startup
Pluggable database opened
SQL>

One point to remember - configure your listener for remote (or 127.0.0.1 loopback) startup/shutdown if you are not using thick/bequeath Oracle client 'C' code, so the listener can find the database when the database is down.

listener.ora entry:

SID_LIST_LISTENER =
 (SID_LIST=
  (SID_DESC=
   (GLOBAL_DBNAME=THEDATABASE)
   (ORACLE_HOME=THE_ORACLE_HOME)
   (SID_NAME=THEDATABASE)
  )
 )

Should even work from a Mac - pure Java..

NET Command - persistently store network details with tab completion

Example using tab completion: (need to be in sql for tab complete to work e.g. ./sql /nolog)

SQL> net unique=localhost:1521/mysid;
SQL> net list
unique
SQL> net list unique
unique
------
localhost:1521/mysid
SQL> connect username/password@uni__TAB_FOR_COMPLETION__
SQL> connect username/password@unique

tab completion works for net shortcuts and tnsnames.ora details.

Behind the scenes net is very similar to the alias command.

**************************************************************
If anyone has useful alias command uses or wanted extensions
to alias command I would be interested to know. 
(Jeff Smith is the Product Manager if you want to go through official channels).
**************************************************************

Turloch

The Net help page:

>help net
NET
------

Command line only - not for sqldeveloper.

NET is a command which allows you to save a network details and assign it a shortcut command.
"net" - print a list of net short cuts
"net list " - list the contents of the short cut
"net name=localhost:1521/XE;" simple net command
"net drop name" - delete the short cut called name

net is single line terminated by newline
net ..=.. is terminated by ';'
net on its own gives a list of existing short cuts.

controlled by
set net on|off|readonly - default ON
   readonly means only do try to enter a net shortcut on successful connect command
set noverwrite on|off|warn - default WARN
   net overwrite: warn prints a warning if an override would otherwise happen.


Monday, 9 March 2015

Data Pump Wizard enhancement

If the master table is kept (now the default) SQL Developer 4.1 (EA1 and above) can find the log file name.

Steps - launch export/import as usual then:

1/Go to dbaview -> Data Pump -> export (or import) jobs

2/Pick a job that is 'Not Running'

3/LOG FILES output shows the Log file from the server.


Issues:
Log file viewer contains a few extra lines at the top.
State (schema/job id) is not cleared if you change connection.
Requires UTL_FILE