Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 27, 2002

Listen Software's How To: Troubleshooting Tips

By David Nishimoto

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

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM