Wednesday, April 15, 2015

OAUG COLLABORATE 2015 - Day 3

8:00 AM - 9:00 AM - Mahesh Vallampati (@mvallamp) - The Data Architect Manifesto

Work w/users to get a data/business model.
Have a functional perspective!
Store comments!!  USER_COL_COMMENTS is something I have seen recently, but this is great reinforcement that you can store comments for tables or columns for quick documentation inside the DB!
Keep column names consistent across tables org_id vs organization_id.
Favorite alias is for FND_USER, just like me!
ANSI SQL - not fun, but highly portable so another reminder I need to brush up on this.
Who columns, created by/date, last updated by/date should be mandatory!
Build your own TAB_COL_DOMAIN_LOOKUPS similar to FND_LOOKUP_VALUES to add information!
Design for the analytic - capture all the data so it can be mined later, and make sure the data model is analytic friendly.
As Data Architect you should know more than:
Developers
User
Business
Business analyst
Tester
PM
Data is now big, we have to think bigger!
Do the math (financial) - large data sets cost money so justify it, or more importantly have the business justify it!
Not enough hardware to support bad business requirements - GREAT quote!
Be savvy about algorithms.
Gartner says users spend 15% of their time analyzing data, so drive the shift of that so users can spend more time analyzing!

9:15 AM - 10:15 AM - Simon Pane (@simonpane) - Oracle Service Name Resolution - Getting Rid of the TNSNAMES.ORA File!

Simon_DBA on MOS Community - a good reminder that I need to participate more on the MOS Communities!
Net Service Name - the name after the @ symbol.
Connect Descriptor - rest of TNSNAMES file.
Tnsping "connect string" and sqlplus system/oracle@"connect string" are both valid ways to connect without using a TNSNAMES file.
Oracle Net Services stops searching when it finds the first TNSNAMES file!!
Strace -e trace=open - shows how it looks for a TNSNAMES file, including a .shadow file locally which is a great way to mess with a DBA!!
TNSNAMES is unstructured because it is not in a DB!
Biggest TNSNAMES he had seen was 700 entries, audience member had 1100 which are both just crazy!!
Management techniques to centralize, but problems can propagate just as fast too.
IFILE can be used up to 4 times which is good to know, since I know we are using that in our environment.
"Old way" does not mean "best way"!
LDAP -> OID, AD, OpenLDAP, other options as well.
EZCONNECT and a Hybrid scheme are different ways to approach this as well - EZCONNECT is required for the RAC interconnect though!
Oracle published LDAP Schema for Oracle Net Services
Idea of having a backup TNSNAMES file - just as I was thinking about it!
Going into an LDAP system is secure because there are no passwords, only stuff that is already in TNSNAMES.
OID used just for this purpose, does not not need to be licensed, using WebLogic front end, new schema, maybe new machines?  Patching/upgrades are needed as well, so is it overkill just for this?
AD benefits - register DB via Oracle Tools (DBCA or Oracle Net Manager)
AD SysAdmin handles all the AD stuff (replication, HA. Patches)!
Critical part of network infrastructure so should already be up and available 100% of the time.
Very easy to setup, and you can find out more in MOS Note 1587824.1 - Configuring MSAD for Net Naming.
Need to work with AD SAs to install/configure, extra AD permissions, 11g clients NAMES.LDAP_AUTHENTICATE_BIND = YES, anon query may be required for Unix clients which could be a no-go in your environment.
OpenLDAP - free on different platforms, master-slave replication, easy updates with yum.
Simple initial setup but need root access, requires some basic Linux skills, must customize text files, no GUI but you can get Apache Directory Studio as a GUI.
All have bulk load options, command line searching, extraction to TNSNAMES.ORA via tool or command line!
Look in $ORACLE_HOME/bin/ldap* for files that are shipped by default already and in $ORACLE_HOME/ldap too!
MOS Note ID 1671486.1 - quick switchover of OID1 to OID2, will have to find out if there is another document for quick switchover of AD1 to AD2.
Not used for RAC interconnect or persistent connections.
Not every app may support it!
HR, HR. WORLD, HR.EXAMPLE.COM, ORCL - put all your aliases in the same line in TNSNAMES
Look up these MOS Note IDs for more details: 846822.1, 395525.1, 146485.1, 1336069.1
trcroute - Oracle Trace Route utility
Sysinternals.com - Active Directory Explorer better than Active Directory Users and Computers!
ldapsearch is in $ORACLE_HOME already!
Introduces ldap.ora file on Linux which I had never seen before!

10:45 AM - 11:45 AM - Jared Still (@PerlDBA) - SQL*Net Troubleshooting - Where Do You Go after Tnsping?

TNS-03505 error, what do you do next?  Strace, sqlnet trace, permissions on TNS_ADMIN/tnsnames.ora
Strace man page -> can pass in CSV list!
/etc/tnsnames.ora overrides all other files because it is the first one found!
Do you have TNS_ADMIN set?
Tnsping ping can only tell you if the listener is up, not the instance!!
Tnsping //IP/junk works because it only validates listener on IP, not that junk is the right service name!
Show parameter service_name just to make sure what the service name really is, and it can be found in lsnrctl status as well.
Interesting script from Tom Kyte about who is connecting to your DB and I shared the tidbit I learned yesterday about V$DBLINK that I am very interested in checking out when returning to work!
Gv$session_connect_info - What is this view about?
Trcasst - new tool to assist with traces!
1550897.1 - Dynamic Oracle Net Server Tracing
156485.1
1007808.6
$ORACLE_HOME/rdbms/mesg/oraus.msg - Need to see this and find out what is in this file!
19324874 bug - need to use 12c thick client so it throws out overflow packets.

Noon - 12:30 PM - Biju Thomas (@biju_thomas) - Using Virtual Box and Free Online Resources to Learn Oracle Database 12c

Oracle VirtualBox - suggested to have 16GB so you can run multiple VMs!
Free license for personal and academic use.
VirtualBox.org - make sure you install the extension pack too!
www.oracle.com/oll - Great resource!
docs.oracle.com/en/database - Documents on the DB!
apex.oracle.com - For if you want to learn APEX!
Oracle By Example in OLL will walk you through many different things.
racattack.org, oracle-base.com, blogs.oracle.com - are more links to get even more knowledge!

2:00 PM - 3:00 PM - Various - OakTable: TED-Style Lightning Talks

Kyle Hailey (@virtdata) talked about DevOps!
Gene Kim - The Phoenix Project a good book that he has read 3 times!
The Goal - book - improvement not made at the constraint is an illusion - great line!
Kellyn Pot'Vin-Gorman (@DBAKevlar) talked on getting SQLT to work w/AWR warehouse
Needed to change the driving package so it will accept the DB name, then look it up.
Pete Sharman (@SharmanPete) spoke a bit about Snap Clone.
PROD -> masking & subsetting -> test master DB.
Missing link - application level cloning!
Pre/post/SQL clone scripts.
Jonah Harris (@oracleinternals) was speaking about alternate DB software!
We learned that MeetMe does 1TB relational calls a day, 30TB NOSQL calls a day!!
Alex Gorbachev (@alexgorbachev) talked about #100miles in 24/26 hours (plus overshared a bit)!
Begin with the end in mind.
Mind is the enemy!
Keep calm and carry on.
Believe. Decide. Do. - but do not be stupid - from Cary Millsap

3:15 PM - 4:15 PM - Raj Garrepally - PeopleSoft Application and System Monitoring Basics

This session was more basics about system monitoring, and maybe less about PeopleSoft monitoring than I wanted but still I was able to get something out of the only Quest session I have visited at the conference!
What to monitor?  Everything, and breaks this down further to monitor the DB, app, web tiers individually.
PeopleTools-> Integration Broker -> SOM-> Monitoring -> Asynchronous Services
paqrydefn table
He monitors the number of sessions on each web tier server because he saw degradation in service after 75 sessions per server.

4:30 PM - 5:30 PM - Brian Bouchard - Understanding the Asset Tables and how to capture data

Core Asset tables:

FA_ADDITIONS -> Additions
FA_ADDITIONS_B
FA_ADDITIONS_TL - The last two tables are FA_ADDITIONS split up because of Multi-org!
FA_BOOKS -> Fixed Asset books
FA_DISTRIBUTIONS_HISTORY -> Assignments
For last two tables you need to use TRANSACTION_HEADER_ID_OUT and DATE_INEFFECTIVE = null to find the last values for the assets.

Depreciation tables: -> populated after running Depreciation job

FA_DEPRN_DETAIL
FA_DEPRN_SUMMARY
FA_DEPRN_PERIODS - Use PERIOD_NAME to link to FA_DEPRN_DETAIL to find the BOOK_TYPE_CODE and PERIOD_COUNTER from that table!

FA_ASSET_KEYWORDS - CCID is not CCID from the GL tables!
FA_CATEGORIES_B
FA_CATEGORIES_BOOKS
FA_CATEGORIES_BOOK_DEFAULTS
FA_LOCATIONS

Other key Asset tables:

FA_BOOK_CONTROLS
FA_ASSET_INVOICES

GL tables:

GL_CODE_COMBINATIONS
GL_LEDGERS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES -> Must use GL_SL_LINK* columns to link back to SLA!
XLA_AE_HEADERS
XLA_AE_LINES

Other important tables:

PO_VENDORS
AP_INVOICES_ALL
PA_PROJECTS_ALL
PA_TASKS
HR_EMPLOYEES - I wonder why he does not link to PER_PEOPLE_F instead, so will need to research myself at work.

Key FND tables:

FND_ID_FLEX_SEGMENTS
FND_FLEX_VALUES
FND_FLEX_VALUES_TL

assetsig.oaug.org
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)