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.
action=>Grant roles or privileges
Example: Object Privileges to a role
grant select,update,delete, insert on myTable to a_user_roles;
grant execute on myFunction to a_user_role;
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 drops a table
- User damages data by modification
action=>Point-in-time data recovery
Method 1 - use the imp utility to restore a table
syntax of the recover [automatic] database
- option =
- until time 'YYYY-MM-DD:HH:MI:SS'
using backup controlfile
Datafiles from from the last backup should be archived
Restore all datafiles
Do not restore control files, redo logs, passwords, or parameter files
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';
Open the database with resetlogs
alter database open resetlogs
Perform a closed database backup
Backup archived logs from the system to prevent mixing archives from different database incarnations.
Start the Instance
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
Disk Drive Failure
Data File has been erased
- 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.
ALTER DATABASE RECOVERY
USE THE FOLLOWING four views to determine status
V$RECOVER_LOG, V$RECOVER_FILE, V$RECOVER_FILE_STATUS, $V$RECOVER_STATUS
Used to insure that a datafile is valid before a restore