|
DB_REPOSITORY Contrary to
traditional database lists in a spreadsheet that should be kept constantly
up-to-date, DB_REPOSITORY dynamically
retrieves its information from the
databases themselves and is, therefore, always up-to-date, unless the connection
to the databases is not possible for any reason. DB_REPOSITORY is written using
Oracle Application Express (ApEx). The starting inventory is kept in an Oracle table, which consists
of a minimum number of fields, in particular the db name, the internal database
link used by ApEx (normally the same as the db name), which project the
database is used for, the name of the DBA or TPM and the purpose or official
status of the database (for example, whether it is production, test or
whatever). Any number of fields could be added. In order for DB_REPOSITORY
to work, only the database link is essential. The application consists of seven pages: the page “Report on
DB_REPOSITORY” shows the database list; clicking of the icon on the left, the
fields can be edited. The information is kept in a table called db_repository, which has to be
maintained by a manager. This is the only handwork required.
A form allows
the input or modification of the database details; at the moment only the
official status offers a list of values to choose from, but other candidates
could be the Project and “Belongs to”.
The database dynamic list is the central
part of the application; it offers information about the list of databases
stored in the table db_repository. The rows can be sorted by db_name, hostname, purpose etc. (easily
customisable). Note that only the field db_name has to be supplied, the content
of the rest of the fields is dynamic (for example, hostname comes from
tnsnames.ora )., the values on other columns come from database tables, except
for operating system version and opatch output, which are not stored in the
Oracle databases, but have to be queried from the remote server.
The order of the columns is easily defined in ApEx; the following
screenshot shows a different order. The report can be exported
in csv format pressing on the link
purpose:
or DB version. The report
lists the patches applied to the ORACLE_HOME for the specific instance. Note
that the opatch output is not stored in any Oracle dictionary table, as it
related to the ORACLE_HOME.
When
is the report generated. The report could be refreshed at every page load, but in case the
list is long, it is probably better to force the application explicitly by
pressing the button “Refresh” on the button left. If any of the remote
databases is not available or the listener is down, a record is inserted into
the table db_in_error. Archiving
In case the database is in archive mode, clicking on the field
log_mode takes you to a graph showing the recent activity.
Rman backups Creating a chart in ApEx is quite simple and the graph of rman
backups is easily displayed, provided any is taken!
Summaries The page “general overview” offers appealing graphs of the
databases
Or distribution according to OS or Oracle version
Note that by
clicking on the pie graphs, the user is elegantly led back to the dynamic
report page showing only the clicked Oracle version or operating system. Look or theme ApEx comes with 18 predefined themes that can be applied to the
application.
The screenshots in this document were taken in theme 6 and 14. A
user can create more themes. What
is needed. Application Express 3.0.1 A web server A schema in ApEx, for example DBREP, This DBREP is also a normal oracle user in the
database where the ApEx application is installed which owns database links to
the various databases which few grants on Oracle system tables (no more than
select) |
