The following example describes how to extract schema and table information from an Oracle 10g database, and corresponds to the “Discovery mechanism” component of the SQL Discovery feature (referred to in the 7.2 Pre-release 24-11-2008 notes at:
http://www.tideway.com/configipedia/Tideway_Foundation_7.2_Pre-release_24-11-2008 )
In order to carry out the steps below, the following details are required, to enable the database instance to be discovered:
- IP no.
- username and password, with sufficient permissions to run these queries:
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = <tablespace name>
SELECT username FROM all_users ORDER BY username
SELECT table_name FROM all_tables WHERE owner = <schema>
SELECT * FROM dba_tablespaces
- A login credential for the server running the database instance
Follow these steps:
1. Download the Oracle Database 10g Release 2 (10.2.0.4) ojdbc14.jar driver file from the following URL (note there are different versions of the same driver on this page - the one used in this example has size 1,555,682 bytes):
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html
2. Copy ojdbc14.jar to /usr/tideway/data/custom/jdbcdrivers
3. Log in as the tideway user and run the following commands:
cd ~/data/custom/jdbcdrivers
ln -s ojdbc14.jar oracleSID.jar
4. The foundation_pre.zip prerelease package itself contains ExtendedDatabasePatterns.zip, which in turn contains two pattern files. Unzip foundation_pre.zip then ExtendedDatabasePatterns.zip, and copy this pattern file to a temporary directory:
OracleExtended.tpl
5. Log in to Foundation, and navigate to the Pattern Management Browse Packages page. Follow the prompts to upload and activate OracleExtended.tpl.
6. The previous step will have created a Software Credential Group called Oracle (containing the four Oracle-specific SQL queries shown above). Navigate to this group from the top-level Discovery tab, by clicking the 2nd-level Credentials tab, then the Software Credentials 3rd-level tab. Click the Oracle group, then the Credentials tab that appears. Create a new credential with the following attributes:
Name Oracle10g_SID
Description
Username <Oracle userid>
Password <password for above user>
Database Driver Oracle 10g Driver (SID)
Database IP Address .* Match as regular expression
Port .* Match as regular expression
System ID (SID) .* Match as regex
(Note that arbitrary text can be entered for the name – if more than one credential exists for the Software Credential Group, the one at the top of the list on the Credential Group page is the one tried first by the pattern. The pattern finds the Software Credential Group via this assignment to the new group attribute:
group := “Oracle”;
)
7. Create an OS login credential for the server hosting the database instance (Discovery -> Credentials -> Login Credentials)
8. Perform a discovery of the Oracle server (remember to click the Start all Scans button first)
9. To see the database structure captured by the discovery, perform the following steps:
a. From the Recent Runs list under the Discovery Status tab, click the entry corresponding to the last discovery
b. Click the link corresponding to the number of hosts discovered (1 in this case)
c. Click the Software Instances link
d. Click the Oracle Database Server link to see the schema and table information retrieved
The following are useful sources of information for further investigation of this feature:
- the SQL Discovery section (new in this prerelease), in the Configuration Guide
- the text of the patterns referred to above, which are heavily commented to explain the new SQL Discovery syntax
Feeds
This Page (RSS)
This Page (Atom)