ingres for oracle users
DESCRIPTION
A look at how an Oracle user can understand and get up to speed on the Ingres DatabaseTRANSCRIPT
Slide 1
Ingres for Oracle Experienced Users
Audience
People who “learned” on Oracle Oracle DBAs PL/SQL Developers Developers of applications that were on Oracle
NOT for:– Ingres experienced people– Other database DBAs and developers
Slide 2
Who am I?
Product manager at Ingres Father, adventurer and husband NOT an Ingres Expert!
– Light development and DBA activities on test and production systems… I leave it to the experts, but needed to know!
Long time Oracle user, developer and DBA– Came to the table with notions of “the way things work”– Started on Oracle 6
– Production DBA on 7.3-10g– Developed and managed dev teams on Oracle using variety of
tech stacks– OCP on versions 7, 8 and 8i
Slide 3
What they have in Common
Enterprise class databases– Large global customers supported 24x7– Enterprise experience from the front line to the CEO– Proven Track Record of mission-critical large
application deployments– Superb recoverability– Superb scalability– Superb standards support
Slide 4
Ingres Installation
Oracle Oracle Home
Ingres Ingres Install
Slide 5
• Both used as a unit for patching and upgrading• Can have multiple per OS instance• Each have a unique identifier
Ingres… The Database
Oracle “Database” Many databases Per
Install/Home
One big clump, separation by “Schema” or user
Ingres “Database” Many per Install and
Instance!
Databases separated from system tablespace via iidbdb, cross db users
Slide 6
Ingres and Users
Oracle User Typically internally defined Optionally OS user driven
Specific to db Instance
DBAs defined via roles/privs (“shadow” DBAs)
Ingres Typically OS user driven Optionally internally defined
Cross db or even Install
DBA defined at createdb Optionally GRANT DB_ADMIN
ON DATABASE <dbname> TO <user> from iidbdb
Slide 7
Ingres Connectivity
Oracle TNS Name/Service
Name Instance/Listener
Process driven
Ingres Vnode
Install-driven [II]
Slide 8
Ingres Backup and Recovery
Oracle Cold Backup
Hot Backup
Exp/imp
Archive Log Mode– By instance
Ingres OS Backup of Locs and
II_SYSTEM
ckpdb
Unloaddb/copydb
Journaling turned on– Per db, always iidbdb @
install
Slide 9
Ingres Startup and shutdown
Oracle sqlplus < startup Privileged SQL Driven
Service on Windows
Per Database
Ingres ingstart CLI/Tool Driven
Service on Window
Across databasesBefore even create or recover
Slide 10
Ingres Configuration
“init.ora” Changed via OEM and
sqlplus
config.dat Changed via terminal
application or Windows application
Changes tracked via config.log
cbf
Slide 11
Ingres Inside
Oracle DBA Views USER_*
DBA_*
V$ views
Ingres Standard Catalogs Standard Catalogs
– iitables,iicolumns, etc.
iidbdb Standard Catalogs
imadb database
Slide 12
Ingres Procedural Language
Oracle PL/SQL Triggers Stored Procedures
Ingres Procedures Rules or Triggers Database Procedures
No “Packages”
Slide 13
Ingres SQL
Oracle ANSI Standard outer Outer Joins via (+)
decode nvl
/ Explain plan
Ingres ANSI Standard outer
join syntax
case ifnull
\g QEP
Slide 14
Ingres Journal Analyzer
Oracle LogMiner Used to make sense out
of cryptic data in redo and archive logs
Ingres Journal Analyzer Used to make sense out
of cryptic data in the Transaction Log File and Journal Files
Slide 15
Getting things done
Ingres: Connectivity Tools
Oracle SQL*Plus Oracle Developer Huge selection of 3rd
Party “stuff”
Ingres sql or isql Ingres CAFE Eclipse with the DTP
– Java IDE with integrated SQL and Stored procedure development
Any JDBC, .NET, Python, or Perl IDE
SQuirrelSQL Ingres Visual Tools
– Exhaustive windows toolset
Slide 17
How to connect, really…
Terminal Connectivity Users: Ingres and others Setting my environment Termcap and TERM_INGRES
– Windows has shortcuts and GUI Tools Ingstart What is my listen address (vnode)?
– What port am I on? grep NTWK errlog.log Database?
– System databases• iidbdb• imadb
– Infodb CLI Impersonating users… the ubiquitous -u
Slide 18
How to connect, really… via JDBC
Classpath– $II_SYSTEM/ingres/lib/iijdbc.jar– Copy the file to your classpath will work too
Class: com.ingres.jdbc.IngresDriver Connection String
– jdbc:ingres://localhost:II7/demodb Username
– OS User to connect– Impersonating other users as “ingres” or the sysadmin user in
JDBC• Append ;dbms_user=mydbuser to your connect string
Slide 19
What happened? Log files and configuration
ingstop -show Alert Log = Errlog.log and more
– $II_SYSTEM/ingres/files• errlog.log – system events and errors• iircp.log – recovery/logging errors• iiacp.log – archiver errors• ckpdb.log – backup and recovery logs• config.log – configuration changes logged
What version and patches?
– $II_SYSTEM/ingres/version.rel Configuration
– $II_SYSTEM/ingres/files/config.dat
Slide 20
Changing Configuration via shell script
iisetres iigetres Warnings!
– manual changes don’t show in config.log– Don’t change config.dat by hand
• Dependent changes will not occur When?
– Initial setup of installs and databases.– Pull out relevant config for use in scripting
Slide 21
Cautions: Locking
Reads DO lock by default– Append “;CURSOR=READONLY” to your JDBC
connection string– Run the following at a SQL Prompt:
• set lockmode session where readlock = nolock;
• http://docs.ingres.com/dba/UserControlledLocking
– Does not preclude inserts, updates and deletes Learn more about Ingres Locking at docs.ingres.com My Finding
– In Oracle, where you manage redo and undo, in Ingres you manage logging and locking
Slide 22
Cautions: Tables and being a DBA
From the Ingres documentation– “The new table is owned by the user who creates it.
The owner of the table is allowed to perform certain operations on the table that other users are not. For example, only the owner (or a user with the security privilege impersonating the owner) can alter or drop a table.”
Slide 23
Learn More
This is NOT the full picture! Ingres Community – http://community.ingres.com
– Wiki– Forums– Documentation – http://docs.ingres.com– Tech Blog
• http://blogs.ingres.com/tech
Slide 24
JDBC Resources
http://docs.ingres.com/connectivity/UnderstandingJDBCConnectivity
http://community.ingres.com/wiki/Ingres_JDBC_Learn
Slide 25
Learn More
Ingres Education: In depth Instructor-Led Training for DBAs, Developers, Performance Tuning and more
Webinars and Whitepapers on Ingres.com
Slide 26
Enterprise Access for Migration from Oracle
Make your life easy… Enterprise Access provides an Oracle Gateway
– Access Oracle data directly from Ingres– Commercial Product
• Worth the cost in time and effort to enable migrations, data type mappings, etc.
Slide 27