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

MOVE statement, 394

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