Listen Software’s How To: 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:ORANTDATABASEusr1.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:ORANTDATABASEusr1.dbf' RESIZE 70M

  Syntax for Moving a data file

  alter database datafile 
     'c:datadatafile1.dbf' offline
  		
  alter tablespace myTablespace offline
  		
  Alter database rename file
     'c:datadatafile1.dbf' to
     'd:datadatafile2.dbf';
  
  alter database datafile 
     'c:datadatafile1.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 privileges

  action=>Grant roles or privileges

  Example: Object Privileges 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

  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 been 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