Database Administrator - SQL Server (PA) Next Step Systems US-PA-King of Prussia Post A Job | Post A Resume

Troubleshooting Tips

A DBA needs to be able to identify the types of failures that
can occur in an Oracle Database environment. Using log and trace
filesk, a DBA can diagnose backup and recovery problems and
effectively find a solution.

Type of failures

Statement failure (bad data type, insufficient space)

action=>Increase quota limits

ALTER USER USER_NAME
DEFAULT TABLESPACE USER_DATA
TEMPORARY TABLESPACE TEMPORARY_DATA
QUOTA 2M ON USER_DATA
QUOTA UNLIMITED ON USER_INDEX
QUOTA 10M ON TEMPORARY_DATA


action=>Add datafiles to a tablespace or resize datafiles

Syntax for Creating a tablespace
with and associating it with a datafile

create tablespace (tablespace_name)
DATAFILE 'C:\ORANT\DATABASE\usr1.dbf' SIZE 200M
DEFAULT STORAGE (INITIAL 500K NEXT 500K
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PERMANENT
ONLINE

Syntax for Altering the size of a datafile
actively being used by a tablespace

ALTER DATABASE
DATAFILE 'C:\ORANT\DATABASE\usr1.dbf' RESIZE 70M

Syntax for Moving a data file

alter database datafile 'c:\data\datafile1.dbf' offline

alter tablespace myTablespace offline

Alter database rename file
'c:\data\datafile1.dbf' to
'd:\data\datafile2.dbf';

alter database datafile
'c:\data\datafile1.dbf' online

select * from v$datafile;
(determine the tablespace to be taken offline)

alter tablespace myTablespace online

Take the tablespace for the datafile offline before renaming the datafile.

Insufficient priviledges

action=>Grant roles or priviledges

Example: Object Priviledges to a role

grant execute on myFunction to a_user_role;

grant select,update,delete,
insert on myTable to a_user_roles;


User Process Failure

* User performed an abnormal disconnect
* User's session was abnormally terminated
* User's program raised an address exception

PMON detects abnormal termination

PMON rolls back the transaction and
releases resources and locks.

User Error

* User drops a table
* User damages data by modification

action=>Point-in-time data recovery

Method 1 - use the imp utility to restore a table

Method 2

syntax of the recover [automatic] database

option =
until time 'YYYY-MM-DD:HH:MI:SS'
until cancel
until scn
using backup controlfile

Steps
1. Datafiles from from the last backup should be archived
2. Restore all datafiles
3. Do not restore control files, redo logs, passwords, or parameter files
4. Mount the database and recover the datafiles to a time before the failure occurs

startup MOUNT pfile='initxx.ora';

recover database until time '1999-1-1:10:30:00';

5. Open the database with resetlogs

alter database open resetlogs

6. Perform a closed database backup

shutdown normal

a. Backup archived logs from the system to prevent mixing archives from different database incarnations.

Instance failure

Start the Instance

startup pfile='initxx.ora';

Verify the instance is running

select * from V$SGA

SMON performs roll forward process
by applying changes recorded in the
online redo log files from
the last checkpoint.

Notify users the Instance is running

Media failure

Disk Drive Failure

Data File has be erased

Alert Logs

* Records informational and error messages
* All Instance startups and shutdowns
are recorded in the log
* Every Create, Alter, or Drop operation
on a rollback segment, tablespace,
or database is record in the log

The BACKGROUND_DUMP_DEST initialization
parameter tells where the alert log
and trace files are to be put.

Setting the LOG_BLOCK_CHECKSUM to be
TRUE will enable redo log block checking.

Recovery Views

after running

ALTER DATABASE RECOVERY

USE THE FOLLOWING four views to determine status

V$RECOVER_LOG,
V$RECOVER_FILE,
V$RECOVER_FILE_STATUS,
$V$RECOVER_STATUS

DB Verify

* Used to insure that a datafile is
valid before a restore

Back to the LSS "How To" Series Main Page