16 May 2006
How to wrap a database into the support environment
New environment - Basic tasks
- Naming conventions
- Database, tablespaces, directories, files, users,...
- Security standards
- Password verifications
- Auditing requirements
- Operational requirements
- Environment configuration
- Network set-up/use of ports
- Monitoring
- Backup/recovery, DR
- Performance and manageability requirements
- File distribution
- Memory sizes and initial parameter sizing
- Project special requirements
- Application specific requests for Oracle parameters or certain set-up
- Any other database related reqs
- Basic monitoring can be:
- Database up/down
- Listener up/down
- Monitoring log errors:
- Alert log text patterns: "ORA-" "error" "cannot" "Starting" "Shutting"
- Listener log: "TNS-"
- Check that database is not hanging with creating/droping a small table
- More detailed monitoring can be quite complex, so I will list just few of the things:
- Various free spaces (file systems, tablespaces, objects)
- All cache hits: buffer caches, library and data dictionary
- Top 20 or so slow SQLs: per seconds, per disk reads, per CPU consumed(10g)
- Statistics for undo, sorts
- Waiting statistics
- Invalid objects
- Auditing information
- Recent database changes (new objects, users,..)
- Last analyzed dates
- List jobs, sort by broken, next_date
- Frequency and size of archive logs/daily
- Latches contention
- List of the recent files in /udump
- Lock all unused users
- Re-check the privileges needed
- Consider adding he password verification function
- Add password and admin_restriction to the listener
- Consider using port other than 1521 and LOCAL_LISTENER parameter
- This is not so urgent for Oracle 10g, as remote administration is not allowed in 10g, like in the previous versions
- Prevent users using "ANY" privilege to access the data dictionary
- O7_DICTIONARY_ACCESSIBILITY=FALSE
- Some applications require this to be set to TRUE (go figure!)
- Apply the security patch sets
- Oracle releases them 4 times a year
- Set up at least the auditing:
- AUDIT ALTER DATABASE;
- AUDIT ALTER SYSTEM;
- AUDIT ALTER TABLE;
- AUDIT ALTER USER;
- AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;
- AUDIT CREATE VIEW;
- AUDIT DATABASE LINK;
- AUDIT GRANT DIRECTORY;
- AUDIT INDEX;
- AUDIT NOT EXISTS;
- AUDIT PROCEDURE;
- AUDIT PROFILE;
- AUDIT PUBLIC DATABASE LINK;
- AUDIT PUBLIC SYNONYM;
- AUDIT ROLE;
- AUDIT ROLLBACK SEGMENT;
- AUDIT SEQUENCE;
- AUDIT SYNONYM;
- AUDIT SYSTEM AUDIT;
- AUDIT SYSTEM GRANT;
- AUDIT TABLE;
- AUDIT TABLESPACE;
- AUDIT TRIGGER;
- AUDIT USER;
- Regular backups
- Add block change tracking
- Exports or data pumps
- If you are exporting the statistics with dbms_stats.export_...stats then you can run the export with statistics=n
- Tape or disk online backups
- Configure the flashback area
- Define flashback
- Guarantee undo retention
- Set up the resumable parameter
- Simple solution is to switch logs regularly (with archive lag parameter) and set up the second optional archive log destination
- If you have enough space on another server, create a physical standby database
- If using Standard Edition, you will have to move the logs manually, but a bit of scripting might be worth the saving in licence costs
- If you can afford, consider Logical standby or a Streamed database
- Either set up a regular job for calculating database statistics or use only one set of statistics. This depends on the system.
- After you are sure that your statistics are good, export them into the table, so if anything goes wrong in the future analyzing you can quickly jump back to the old, good stats
- You can export multiple statistics and keep them together
- For 10g, set statistics_level=typical
- Switch, at least weekly, a log (alert, listener, ...) and preserve the old one
- Remove the old export dumps, disk backups
- Delete older audit logs in sys.aud$
- Purge the old statitics
- Unix
- Follow Unix set-up guidelines from the installation document (number of semaphores, max processes, ...)
- Add oracle to cron allowed users
- Make sure root.sh has been run to remove setuid-s
- Make changes to dbshut (add immediate, if necessary), to oraenv (list the SIDs if you have multiple databases
- I always add heaps of aliases to .profile
- Create a /sql directory and set SQL_PATH to it
- Windows
- Do not allow anti-virus or any other processes to keep redo logs open
- Preferable all Oracle files shold be exempt
- Keep checking who is in the ORA_*DBA group
