Problem- ORA-01555 snapshot too old error

Impact: It affects read consistency of oracle.
Solution:

The ORA-01555 snapshot too old error can be addressed by several remedies:

1.  Re-schedule long-running queries when the system has less DML load.
2. Increasing size of rollback segments.  The ORA-01555 snapshot too old also relates to your setting for automatic undo retention
3. Don't fetch between commits.

Problem:Restoring the Server Parameter File (SPFILE) from Backup

Impact:When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file.so if spfile is missing instance cannot be started.
Solution:
If you have configured control file autobackups, the SPFILE is backed up with the control file whenever an autobackup is taken.
If you want to restore the SPFILE from the autobackup, you must first set the DBID for your database, and then use the RESTORE SPFILE FROM AUTOBACKUP command. The procedure is similar to restoring the control file from autobackup. You must first set the DBID for your database, and then use the RESTORE CONTROLFILE FROM AUTOBACKUP command:

RMAN> SET DBID 320066378;
RMAN> RUN {
    SET CONTROLFILE AUTOBACKUP FORMAT 
          FOR DEVICE TYPE DISK TO 'autobackup_format';
    RESTORE SPFILE FROM AUTOBACKUP;
    }

Problem:Restore of the Control File from Control File Autobackup

Impact:control file is lost or damage and and database cannot be started.

Solution:
If we dont have recovery catalog and want to restore the control file from autobackup, the database must be in a NOMOUNT state. You must first set the DBID for your database, and then use the RESTORE CONTROLFILE FROM AUTOBACKUP command:

RMAN> SET DBID 320066378;
RMAN> RUN {
    SET CONTROLFILE AUTOBACKUP FORMAT 
          FOR DEVICE TYPE DISK TO 'autobackup_format';
    RESTORE CONTROLFILE FROM AUTOBACKUP;
    }

Problem:Recovering from a lost tempfile

Impact: It can affect certain operations which use data in the temp files.
Solution:Restart the database. The database starts and if the original disk directory location is available, Oracle recreates all missing tempfiles.
If the temp datafiles are nopt recovered then:
recreate the temp files as shown:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE Maxsize unlimited;
 

Problem:ORA-01173:Data dictionary indicates missing data file from system tablespace

Impact:datafiles are missing in system tablespace .
Solution:
 Try to recover the database from a more recent control file or
recreate the control file, ensuring all datafiles are included for the system tablespace in the command line.

Problem:How to drop tablespace if datafile is missing

Impact: data files are missing and you cannot delete tablespace

Solution: Follow these commands
(1)startup mount
(2)alter database datafile '/path/to/filename' offline drop;
(3)alter database open;
(4)drop tablespace tbsp_name including contents cascade constraints;

Problem:Index is lost or damage

Impact: Lost or damaged index affects the database performance,queries that use indexes run slow.

Solution:
Drop the damaged index :

DROP INDEX [schema.]index [FORCE]

Recreate index:  example:

CREATE INDEX index_name ON table_name(column_name)
      TABLESPACE tablespace_name;      

Problem:system tablespace is lost or missing

Impact: database operations are affected as this tablespace contains metadata and data dictionary.

Sloution:

(1)Login in to the target database using RMAN as follows:   rman target /
(2) start the database in MOUNT state using command:  startup mount;
(3)Restore the database using the  command :  restore database;
(4)Recover the database using the below command :  recover database;
(5)Now open the database in OPEN state as follows : alter database open;      

Problem: Database instance is down.

Impact: Enterprise manager cannot be used for database management.

solution:
Alter user sysman by using command:
Alter user sysman account unlock;
Alter user sysman identified by password;

How to handle extent reached error in table or indexes?

For every table or index a maximum number of extents can be specified. If ORA_01631: max # of extents reaches in table <table name>
or
ORA_01632: max # of extents reaches in index <index name>

is encountered it means that in order to accommodate new data Oracle needs to extend a table but could not because the maximum number of extents have been reached.


For handling this with indexes : we can just rebuild the indexes whereas we cannot do this with table so we alter the extent size for a table as following:

alter table <table name> storage ( maxextents new_value );

or it can be set to unlimited as following:


alter table <table-name> storage (maxextents unlimited);


*Note
It is a good idea to use uniform extent size per tablespace - if all extents
in a tablespace are the same size there is no fragmentation.

oracle

I am an oracle student at humber, and this is my first blog entry. At humber i have been introduced to various aspects of data basing like data warehouse, security ,BI , tunning,database administration. I think my experience at humber studying oracle has been awesome .

..