Pro Oracle Database 11g Administration (105 page)
operations. FGA auditing allows you to audit for SQL activities that occur at the column level. FGA auditing also allows you to perform a Boolean check on an operation, such as “if the value selected is in a range then audit the activity.”
You manage fine-grained auditing through the use of FGA policies. The DBMS_FGA package allows you to add, disable, enable, and drop FGA policies. You need execute privilege on the DBMS_FGA package to administer audit policies.
■
Note
The fine-grained auditing feature requires the Enterprise Edition of Oracle.
The follow are the steps to implement FGA:
1. Create a policy using the DBMS_FGA package. This example creates a policy for the INV table and specifies that any INSERT, UPDATE, DELETE, or SELECT statement against the SALARY column of the EMP table will be recorded in the audit trail: begin
dbms_fga.add_policy (
object_schema => 'INV',
object_name => 'EMP',
audit_column=> 'SALARY',
policy_name => 'S1_AUDIT',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
audit_trail => DBMS_FGA.DB_EXTENDED
);
end;
/
2. Verify that the policy exists by querying the DBA_AUDIT_POLICIES view: select object_schema
,object_name
,policy_name
,sel, ins, upd, del, policy_column
from dba_audit_policies;
633
CHAPTER 22 ■ DATABASE TROUBLESHOOTING
Here’s the output for this example:
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SEL INS UPD DEL POLICY_COL
-------------- -------------- -------------- ----- ----- ----- ----- ----------
INV EMP S1_AUDIT YES YES YES YES SALARY
3. To view the recorded SQL statements in the FGA audit trail, select from the DBA_FGA_AUDIT_TRAIL view:
select
db_user
,to_char(timestamp,'dd-mon-yy hh24:mi:ss') ts
,sql_text
from dba_fga_audit_trail
order by timestamp;
■
Note
The DBA_FGA_AUDIT_TRAIL view is based on the FGA_LOG$ table.
Here’s some sample output:
DB_USER TS SQL_TEXT
--------------- ------------------------ --------------------------
INV 21-jul-09 21:47:07 select * from emp
SYSTEM 21-jul-09 21:58:36 select salary from inv.emp
If you need to disable a policy, use the DISABLE_POLICY procedure:
SQL> exec dbms_fga.disable_policy('INV','EMP','S1_AUDIT');
To drop a policy, use the DROP_POLICY procedure:
SQL> exec dbms_fga.drop_policy('INV','EMP','S1_AUDIT');
As the SYS schema you can purge records from the fine-grained auditing audit table as follows: SQL> truncate table fga_log$;
■
Tip
For more details on fine-grained auditing, see Oracle’s Security Guide available on Oracle's OTN website.
Summary
A senior database administrator must be adept at efficiently determining the source of database unavailability and performance problems. Identifying and resolving problems defines a professional level DBA. Anyone can Google a topic (there's nothing worse than being on a trouble call with a manager who is Googling and recommending random solutions). Determining the appropriate solution and confidently applying it in a production database environment is where you add tremendous value.
Diagnosing issues sometimes requires some system and network administrator skills. An effective DBA must also know how to leverage the Oracle data dictionary to identify problems. As part of your 634
CHAPTER 22 ■ DATABASE TROUBLESHOOTING
strategy, you should also proactively monitor for the common sources of database unavailability. Ideally, you'll be aware of the problem before anybody else and will proactively solve the issue.
No book can cover every troubleshooting activity. This chapter includes some of the most common techniques for identifying problems and dealing with them. Often, basic operating system utilities will help you identify the source of a hung database. In almost every scenario, the alert.log and corresponding trace files should be inspected. Finding the actual root cause of a problem is often the hardest task. Use a consistent and methodical approach and you'll be much more successful in diagnosing and resolving issues.
This is the end of the book. I have tried to convey techniques and methods that will help you survive even the most chaotic database environments. To summarize these thoughts, a DBA's manifesto of sorts:
• Automate and monitor through scripts and schedulers. Be the first to know when something is broken.
• Strive for repeatability and efficiency in processes and scripts. Be consistent.
• Keep it simple. If a module is over a page long, it’s too long. Don’t implement a script or feature that another DBA won’t be able to understand or maintain.
Sometimes the simple solution
is
the correct solution.
• Remain calm regardless of the disaster. Be respectful.
• Don’t be afraid to seek or take advice. Welcome feedback and criticism. Listen to others. Entertain the thought that you might be wrong.
• Take advantage of graphical tools, but always know how to manually implement a feature.
• Expect failure, predict failure, prepare for failure. You don't know what will go wrong but you do know something will go wrong. Be happy that you prepared for failure. The best lessons are painful.
• Test and document your operating procedures. This will help you stay calm(er) and focused when in stressful database-down situations.
• Don’t write code to implement a feature that the database vendor has already provided a solution for (replication, disaster recovery, backup and recovery, and so on).
• Become proficient with SQL, procedural SQL, and OS commands. These skills separate the weak from the strong. The best DBAs posses both SA and developer expertise.
• Continually investigate new features and technology. Learning is a never-ending process. Question everything, re-evaluate, and look for a better way. Verify your solutions with repeatable peer-reviewed tests. Document and freely share your knowledge.
• Do what it takes to get the job done. You compete with the world now. Work harder and smarter.
The job of a database administrator can be quite rewarding. It can also be very painful and stressful.
Hopefully, the techniques documented in this book will get you from being mostly stressed to an occasionally happy state. Good luck.
635
■ INDEX
ALL_COL_PRIVS_RECD view, 234
ALTER INDEX statement, 295
ALLOCATE CHANNEL command, 478
ALTER INDEX...MONITORING USAGE
ALL_SYNONYMS view, 203
statement, 190
ALL_TABLES view, 214, 224
ALTER INDEX...REBUILD PARTITION
ALL_TAB_PRIVS view, 234
statement, 297
ALL_TAB_PRIVS_MADE view, 234
ALTER MATERIALIZED VIEW LOG ON
ALL_TAB_PRIVS_RECD view, 234
ALTER MATERIALIZED VIEW LOG
ALTER DATABASE ADD LOGFILE MEMBER
ON...SHRINK statement, 393
statement, 106
ALTER MATERIALIZED VIEW statement, 379
ALTER DATABASE BACKUP CONTROLFILE
statement, 433, 447
ALTER MATERIALIZED VIEW...MOVE
TABLESPACE statement, 389
ALTER DATABASE BACKUP CONTROLFILE TO
TRACE statement, 87
ALTER PROFILE statement, 126
ALTER DATABASE BACKUP statement, 89
ALTER SEQUENCE statement, 210
ALTER DATABASE BEGIN BACKUP statement,
ALTER SESSION statement, 93, 111, 247
436
ALTER SYSTEM CHECKPOINT command, 106
ALTER DATABASE CLEAR LOGFILE command,
ALTER SYSTEM command, 321
101
ALTER SYSTEM KILL SESSION statement, 617
ALTER DATABASE CLOSE statement, 45
ALTER SYSTEM SET statement, 147
ALTER DATABASE command, 499, 515
ALTER SYSTEM statement, 30, 97, 247, 422
ALTER DATABASE DATAFILE ... OFFLINE FOR
ALTER SYSTEM SWITCH LOGFILE statement,
DROP statement, 85
106
ALTER DATABASE DATAFILE ... OFFLINE
ALTER TABLE ... ADD statement, 147
statement, 85
ALTER TABLE ... DROP statement, 149
ALTER DATABASE DATAFILE ... RESIZE
ALTER TABLE ... MODIFY PARTITION
command, 82
statement, 252
ALTER DATABASE DATAFILE statement, 83–85
ALTER TABLE ... MODIFY statement, 148
ALTER DATABASE DEFAULT TEMPORARY
ALTER TABLE ... MOVE statement, 157–158
TABLESPACE statement, 34
ALTER TABLE ... RENAME statement, 149
ALTER DATABASE DROP LOGFILE GROUP
ALTER TABLE ... SHRINK SPACE statement, 157
statement, 106
ALTER TABLE statement, 134, 141, 162–163,
ALTER DATABASE DROP LOGFILE MEMBER
167, 170, 257, 293, 295, 297
statement, 107
ALTER TABLE...ADD PARTITION statement,
alter database open resetlogs command,
292
530–531
ALTER TABLE...ADD statement, 253
ALTER DATABASE OPEN statement, 442
ALTER TABLE...DROP PARTITION statement,
ALTER DATABASE RENAME FILE statement,
297
86–87, 522
ALTER TABLE...DROP statement, 254
ALTER DATABASE RENAME FILE ... TO
statement, 85
ALTER TABLE...MERGE PARTITIONS
statement, 296–297
ALTER DATABASE SET DEFAULT BIGFILE
TABLESPACE statement, 81
ALTER TABLE...MOVE PARTITION statement,
288
ALTER DATABASE statement, 83, 88
ALTER TABLE...MOVE statement, 260
ALTER DATABASE TEMPFILE...RESIZE
statement, 625
ALTER TABLE...MOVE...STORE AS statement,
253
ALTER INDEX ... REBUILD command, 158
ALTER INDEX ... RENAME TO statement, 188
638
■ INDEX
ALTER TABLESPACE ... ADD DATAFILE
ensuring that database is in, 431
statement, 82
making architectural decisions, 421
ALTER TABLESPACE ... OFFLINE IMMEDIATE
making cold backups of, 428–430
statement, 85
setting archive-redo file location, 421–426
ALTER TABLESPACE ... OFFLINE NORMAL
FRA, 425–426
statement, 83, 85
user-defined disk locations, 422–424
ALTER TABLESPACE ... OFFLINE TEMPORARY
statement, 85
using FRA for archive log files, 424–425
ALTER TABLESPACE ... RENAME DATAFILE ...
archivelog-mode databases, 441–448
TO statement, 85
incomplete recovery of, 449–450
ALTER TABLESPACE statement, 76–77, 80,
offline, 441–444
82–83, 85–86
mount mode, 442
ALTER TABLESPACE...RENAME DATAFILE
RESTORE statement, 443–444
statement, 86
restoring datafile from backups, 442–443
ALTER TABLE...SPLIT PARTITION statement,
online, 444–445
295
restoring control files, 445–448
ALTER TABLE...TRUNCATE PARTITION
arp utility, 21
statement, 299
arrow keys, scrolling with, 58
ALTER USER privilege, 118
AS BACKUPSET command, 490
ALTER USER statement, 118, 122
AS COPY command, 490
ALTER VIEW command, 198
ASH (Active Session History), 612, 615
alt_prof_dyn.sql script, 126
ASM (Automatic Storage Management), 46
-altr command, 59
ASSM (automated segment space
APP_DATA tablespace, 72
management), 247
APP_DATA_LARGE tablespace, 72
ATOMIC_REFRESH parameter, 399, 409–410
APP_DATA_SMALL tablespace, 72
ATTACH parameter, 313–315
APPEND hint, 350
attachHome option, 15
APPEND keyword, 505
attaching Oracle home, copying existing
APPEND option, 330–331
installation, 14–15
APP_INDEX tablespace, 72
attributes, segment and storage, 322–323
appinvprd.log file, 588
AUD$ table, 626, 628, 630–633
APPUSR user, 121
AUDIT ALL statement, 631
architecture, 308–310
AUDIT statement, 626–629, 631
archive-redo log destination, 465–466
AUDIT_FILE_DEST variable, 627
archive redo logs
auditing, troubleshooting, 625–635
backing up, 469
disabling, 630–631
deletion policy, 472–473
DML usage, 627–628
restoring files, 524–526
enabling, 626–627
RMAN backups of, 492–493
and fine-grained auditing, 633–635
ARCHIVE_LAG_TARGET parameter, 100, 103
logon/logoff events, 628–629
ARCHIVELOG ALL clause, 540
moving audit table, 632–633
archive.log file, 338
purging audit table, 631–632
archivelog mode, 420–430
viewing enabled actions, 629–630
backing up archive-redo log files, 428
