DBAs and Oracle DesignerWilkinson & Tierstein
DBAs, Oracle Designer, and the Development Life Cycle
Peter Wilkinson, Mercury International Limited
Leslie Tierstein, SCI World, Inc..
Introduction
This paper will show how DBAs and developers can work together to close the project life-cycle loop from initial implementation, production, and back, to analyzing new features/enhancements. The key, demonstrated by example, is that Oracle Designer is the DBA’s guide as to what should be in the database and not what is in the database. Oracle Designer will be your reference guide for all the databases you look after, recording what should be there and why it is there. You will spend (or already have spent) a lot of time and effort to get the database running smoothly; that knowledge must not be wasted if you are no longer there. The examples will focus on features of Designer that support extensions to its metadata repository and versioning the contents of that repository.
Three groups of people – DBAs, developers, and managers – are the audience for this paper. Please read one of the following “Introduction” sections to see the issues relevant to you that will be discussed.
Introduction for DBAs
You’ve heard rumors that a new application system, years in the making, is ready to go live. But not until you’ve reviewed it! After all, what do developers know about tablespaces, storage parameters, buffer pools, database privileges, or the best place for snow boarding this weekend? This could take weeks. In addition, data needs to be replicated to a server in Sydney. This could take months. Couldn’t the developers have added this information? Well, yes and no. On the surface, there doesn’t appear to be any place in Designer to store details on replication or to identify particular database users so they can be quickly located.
By using some of Oracle Designer’s more advanced capabilities and a bit of creative thinking, you can manage advanced database features and even create replication scripts. Let the development team have access to your standards and guidelines, and they will do most of your work for you. Don’t look on it as losing control but as getting two weeks of scuba diving in the barrier reef with all the time saved.
Introduction for Designers
You’ve just completed your latest project using Oracle Designer. You even managed to do configuration management including versioning and source code control. Now the application needs to be deployed to the production environment. Do you:
- Have a DBA on the team to do this?
- Go cap in hand to the senior DBA to see if your work is worthy of being installed in a production database?
- Go to your manager and “arrange” to have the application deployed?
Whichever option your company uses, do you ever find out how the implementation turned out? Were you making false assumptions when designing indexes or queries? When it comes to upgrading, do you know of additional/modified indexes that will affect your queries?
This paper will demonstrate that many of the benefits of the development team’s use of Oracle Designer also flow on to the DBA when implementing and maintaining applications and flow back to the development team when new work needs to be done.
Introduction for Managers
You’ve invested a number of years in Oracle Designer. Your business analysts, developers, and programmers are using it, standards are being adhered to, productivity is up, and the development team members work closely together. Now, with the new Repository Object Browser (ROB), you can see what’s inside Oracle Designer. But consider the DBAs. Important as they are, are they a bit aloof? Do they seem to be building little empires? Is there tension in the air when an implementation looms? Why do implementations take longer than expected? You thought the sales rep said that Designer could help with production implementations, not just development and testing. Do you worry what would happen if the senior DBA had a snow boarding accident?
The Designer Approach
Perhaps the hardest idea to come to grips with is that Oracle Designer is at the heart of the application life cycle and DBAs and junior programmers are just part of the same cycle. The techniques described here work best when Designer is used as part of a full application life cycle and not just in isolation. The DBA should expect the application to be created by the development team and not be expected to reverse engineer (design capture) the database, although initially this may need to be done.
Getting Started
Most of your time in Oracle Designer will be spent in the Design Editor, particularly in the Database Administration (DBA Admin) and Distribution tabs.
Your first task is to document the physical location of the database(s) the application will run on. This work will pay dividends when someone new starts work. You start at high level with the database server (see Figure 1) and then work down through server connections, databases, log files, redo files, tablespaces and their datafiles, and default storage parameters. A combination of reverse engineering and hand editing is required.
Figure 1. Documenting the Database Server
The next step is to set up the schemas and select the tables (and other objects) that they will own. The indexes will come with the tables. You can now allocate the tablespace, storage parameters and buffer pool to the tables and indexes. Ah! Where is the buffer pool property? Oracle provides the ability to use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. The buffer pool is defined when creating or altering a table or index. But an exhaustive search of Designer failed to turn up any place to specify the buffer pool for a particular table or index. Manually recording the buffer pools for 100 tables and 300 indexes might be useful documentation but not practical if you need to change the table/index creation scripts or use OEM to look up each table/index that needs its buffer pool changed. What to do?
User Extensions
User extensions are useful if Designer is lacking a particular property for a database feature you want to record or use, which is precisely the case for the missing buffer pool specification. Developers may already be using extensions, for example, to record bits of design information or functional requirements collected. DBAs will find extensions useful as well.
There are two types of user extensions – creating a new element type and adding properties to an existing element type. In the examples we are adding properties to existing elements. As for creating a new element type, later we will introduce an alternate approach – loading a text file defining the element into Designer.
Recording New Properties
To set up user extensions use the Repository Administration Utility (RAU). The tricky part here is to choose the correct “system defined element” type to which you will add the property. The description will match closely the headings in the Design Editor. In this case, it is TBI – Table Implementation (see Figure 2) and UIS – User Object Index Storage. Verification is limited so you need to ensure that the buffer pool property is defined correctly. Once you have entered the column attributes and published the extension, the new element property will be visible at the bottom of the property sheet for table and index implementations. A full list of Designer element types is published in the document ..\CDOC72\model\el_defs\outer_frame2_de.htm (for Designer 6i) and is available on Oracle Technet at (for Designer 9i).
Figure 2. Setting Up a User Extension
Selecting User Extensions
Now it’s time to actually use the user extensions. To do this, you need to know about the Designer repository. The repository consists of a set of Oracle database tables that contain the metadata describing your application system. You should never access these tables directly. Instead, you can retrieve and manipulate data by accessing the Designer CI_ views. The CI_ views have meaningful view and column names and are pretty well immune from changes to the base tables (which have happened with previous releases of Designer). Members of the development team may already be using the views to create custom reports. Since versioning was introduced with Designer 6i, the Designer repository and CI_ views have become more complicated in that they hold multiple versions of the information you are interested in. The code in Listing 1 will set the workarea for your SQL session to ensure that queries match those of your Designer session. (The full code is available for download).
PROMPT Connect to Designer database
CONNECT PWILKINSON@DES
SELECT name FROM sdd_workareas ORDER BY 1;
ACCEPT p_wa PROMPT 'Select Workarea from above'
EXEC jr_context.set_workarea('&p_wa')
Listing 1. Setting the Workarea
NOTE: There is no CI_ view for workareas. The contents of the CI_ views are not meaningful until a valid workarea is set.
It is also worth predefining the application system and database you will be working on. The code in Listing 2 will set the application system (p_app) and the database (p_db).
SELECT name FROM ci_application_systems ORDER BY 1;
ACCEPT p_app PROMPT 'Select Application System from above '
SELECT orad.name
FROM
ci_app_sys_databases asda
,ci_oracle_databases orad
,ci_application_systems apps
WHERE orad.id=asda.database_reference
AND apps.id=asda.application_system_reference
AND apps.name='&p_app'
ORDER BY 1;
ACCEPT p_db PROMPT 'Select Master Definition Database from above '
Listing 2. Setting the Application System and Database
You can now look at the contents of the CI_ views using SQL or PL/SQL.
Example: Using User Extensions to Generate DDL Scripts
We cannot alter the DDLscripts that Designer generates so we will create our own. We will generate an ALTER TABLE script (see Listing 3) that can be run for existing tables. SQL and PL/SQL skills are required to write your own code generator, but the logic is fairly straightforward. (The full script is available for download.)
REM Store the id of the database to save a join
VARIABLE c_orad_id NUMBER
BEGIN
SELECT orad.id INTO :c_orad_id
FROM
ci_application_systems apps
,ci_app_sys_databases asda
,ci_oracle_databases orad
WHERE asda.application_system_reference = apps.id
AND asda.database_reference = orad.id
AND apps.name = '&p_app'
AND orad.name = '&p_db';
END;
/
SPOOL s:\replication\bms_1\buffer_pools1.sql
PROMPT SPOOL s:\replication\bms_1\buffer_pools1.log
PROMPT PROMPT ALTER BUFFER POOL FOR TABLES
SELECT'PROMPT Set Buffer Pool for ' || dboi.global_synonym_name || chr(10) ||
'ALTER TABLE ' || datu.name || '.' || dboi.global_synonym_name || chr(10) ||
'STORAGE (BUFFER_POOL ' || nvl(dboi.user_defined_property_0,'default') || ');'
FROM
ci_oracle_databases orad
,ci_replication_groups repg
,ci_db_object_implementations dboi
,ci_database_users datu
WHERE orad.id = :c_orad_id
AND repg.parent_ivid = orad.ivid
AND dboi.replication_group_reference = repg.id
AND dboi.complete_flag = 'Y'
AND dboi.object_implementation_type = 'TBI'
AND dboi.global_synonym_name IS NOT NULL
AND datu.id = dboi.database_user_reference
ORDER BY datu.name,dboi.global_synonym_name;
SPOOL OFF
Listing 3. Generating an ALTER TABLE Script
The result is a series of simple, syntactically correct statements for every table you want to implement.
PROMPT Set Buffer Pool for BMS_ANALYSIS_JOBS
ALTER TABLE BMS.BMS_ANALYSIS_JOBS
STORAGE (BUFFER_POOL RECYCLE);
This script also highlights the use of Designer to show what should be in the database. Good practice should dictate that any changes made should be recorded in Designer. (Alternatively, changes could be made in Designer and scripts generated to apply the changes to the database(s).) Good communication is necessary to ensure that this type of script does not interfere with another DBA doing work on the same database.
Example: Maintaining and Tracking Users
Database security is a very hot topic. Vulnerabilities of the web servers get extensive press, but most surveys cite employees as the biggest danger. On an almost daily basis users are being added or removed or their privileges are changed. In my early DBA days, I would print a list of users and their privileges every few months. I would then write their full names next to their login-names. Over the months, I would amend it with the changes that took place. I would only replace it when it got too tatty.
By using Designer, each DBA can have access to the users for a particular application. The Comment, Description, and Notes fields (see Figure 3) can store useful information like the users’ full name, where they work, who their manager is, date started/left, and the date roles were granted or revoked. Some of these columns may be useful enough to be candidates for user extension properties, so they are easily queryable. So, for example, if database monitoring software reveals that a user with the login name GWBUSH is blocking updates to the DEFCOM table, you know exactly where to find Mr. George W. Bush.
Figure 3. User Information
Another useful tip is not to delete users from Designer. If they leave the company, record it in the Notes and change the “Complete?” property to “No”. In years to come you will be able to refer to old audit column data and know exactly who did what. The more security conscious could even create a database link to the production database, so they can run a script to diagnose any discrepancies regarding users and their assigned roles between what should be there (Designer) and what actually is there (Production).
Those who are brave can document all the users, define and grant all the roles, and give object privileges to those roles in the Designer repository. It is now possible to replace all the user information on the database. Use Designer to generate scripts that create roles, object grants and users. Write your own script to drop the existing users and finally, if you allow users to change their passwords, write a script to expire all the passwords for the users that Designer has just created. This may be drastic, but if you are doing a major installation (for example, replacing lots of tables/schemas) this may be quicker. You then know that the database and Designer are coordinated. The downside is there may be some privileges left out (i.e., not entered in Designer) which will have to be put back post-implementation, but a least you will now know about them.
SCM - Versioning and Configurations
The inclusion of software configuration management (SCM) into Designer is a major advance of recent years. A whole paper could be devoted to this, and several white papers are available on Oracle Technet. Simply put, versioning allows you to track changes made to Designer objects. This is also the mechanism that allows DBAs and developers to work on the same objects at the same time.
Designer Prerequisites -L3
Designer must be set up to use versioning. Once versioning has been activated, the project needs to be structured into workareas, which control which users have access to which components of the application. Whenever users connect to Designer, they always work in one particular workarea. A workarea is a view of one or more applications at a particular version. This allows multiple projects, with different timescales, to work on the same application.
The Designer repository owner will create a login for each DBA and at least one check-in branch. Check-in branches are used to control various types of work that may be happening to an application simultaneously. For example, a module requires an enhancement. One programmer designs the screen and is minimally concerned with back-end logic. A second programmer writes the business logic but only needs a basic screen layout. A third programmer has to fix a bug in the original business logic. Each will use a separate check-in branch to separate their changes. Later the three versions need to be merged into one for final testing.
Implementing Versioning
First, all Designer users must agree to use versioning and decide on which versioning strategy to use. Once versioning is switched on, it can’t be undone (except via a restore). The technical lead of the development team will usually make this decision as it affects developers heavily.
Each DBA must have their own username/password to control access to the workareas. There must also be at least one versioning branch for DBA usage. Although developers and DBAs can use the same branch if they are working on the same release of software, often the developers will be working on the next release and the DBAs are enhancing the current release. In this case separate branches will be required.