Undo_Retention or ORA-01555:snapshot too old

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter. If any overwritten undo information is required for consistent read in a current long-running query, the query could fail with ORA-01555 snapshot too old error message. The ORA-01555 is caused by Oracle read consistency mechanism.

Oracles does this by reading the before image of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555: snapshot too old error occurs, rollback records needed by a reader for consistent read, are overwritten by other writers. The properties of the UNDO_RETENTION parameter are mentioned below:

• Parameter type – Integer
• Default value – 900
• Range of values – 0 to 232 – 1
• Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

Avoiding the ORA-01555: snapshot too old error

• Do not run discrete transactions while sensitive queries or transactions are running.
• Use a large optimal value on all rollback segments, to delay extent reuse.
• Use a large database block size to maximize the number of slots in the rollback segment transaction tables.
• If necessary, add extra rollback segments (undo logs) to make more transaction slots available.
• Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN.

Read More

Recover Undo Tablespace

Undo tablespace is used to store the post modification data of every transaction for rollback or read consistency or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. So, recovery of undo tablespace is more important in case of undo tablespace lost. There are few steps to recover undo tablespace:

[oracle10@localhost ~]$export_SID=luck
[oracle10@localhost ~]$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 30 09:11:10 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area   314572800 bytes
Fixed Size                            1219160 bytes
Variable Size                       96470440 bytes
Database Buffers                213909504 bytes
Redo Buffers                         2973696 bytes
Database mounted.
ORA-01157 :cannot identify/lock datafile 2 - see DBWR trace file
ORA-01110 :datafile 2: '/opt/luck/undotbs.dbf'

we cannot open the database by taking the datafile offline but we need to use 'OFFLINE FOR DROP'.Datafile once marked offline for drop can never be brought online.

SQL>alter database datafile 2 offline for drop;
Database altered.

SQL>alter database open;
Database altered.

SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Now we need to create another undo tablespace for the transactions to use and once the new undo tablespace is created, shutdown the database and edit the init.ora file and change the  parameter undo_tablespace=new undo_tablespace

SQL>create undo tablespace undotbs1 datafile '/opt/luck/undotbs1.dbf' size 20m autoextend on;
Tablespace created.

SQL>shutdown immediate;

In simple steps:
a) startup
b) error ora:01157
    error ora:01110
c) alter system set undo_management = manual scope=spfile
d) shutdown immediate
e) startup 
f ) drop undo tablespace
g) Create UNDO Tablespace UNDOTBS datafile ...
h) alter system set undo_management = auto scope=spfile
i) Shutdown
j) Startup Database

Related Post:- Undo and Rollback segment

Read More

ORA-12906 cannot drop default temporary tablespace

If you try to drop default temporary tablespace of database as it will result in error as:-

SQL>SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES;
TABLESPACE_NAME
---------------
TEMP

SQL>alter tablespace temp offline;
alter tablespace temp offline
*
Error at line 1;
ORA-12905:default temporary tablepspace cannot be offline

SQL>drop tablespace temp including contents and datafiles;
drop tablespace TEMP including contents and datafiles
*
Error at line2:
ORA-12906:cannot drop default Temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one. To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,

SQL>create temporary tablespace temp1 tempfile'/opt/nit/temp01/dbf' size 10M;
 Tablespace created.

Make this tablespace to database default temporary tablespace,
SQL>alter database default temporary tablespace temp1;
Database altered.

SQL>drop tablespace temp including contents and datafiles;
Tablespace dropped. 

Read More

Solution Of Startup Files With An Error ORA-00119 and ORA-00132

Error Description: Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'


Cause of The problem: If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.

PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.

Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.

Solution of The problem 

A)Correct the tnsnames.ora

i)Determine if the tns alias is good by using tnsping.

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name

So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')

and run tnsping utility,

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters

So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.

SQL>!vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))

iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.


B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;

2)Open the pfile and remove the LOCAL_LISTENER entry.

$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora

3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;

4)Start the database.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.

In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';

In my system,
SQL>ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';

SQL> show parameter local

NAME TYPE VALUE 

local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))

Read More

Solution OF An Error ORA-27102

Scenario of The problem: 

In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,

SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument

Reason of The problem:

The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.

Solution of The problem:

1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,

SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.

SQL> alter system set sga_target=1600M;
System altered.

SQL>shutdown

Now set ORACLE_SID and start the instance.

bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;

And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.

And start the database with the pfile.

SQL>STARTUP PFILE='pfile_name';

Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;

Read More

My oracle primary server is crashed, then how to switch all users to standby oracle database ? also how to rebuild primary database using standby database ?

SQL>RECOVER STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Activate standby database:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Check New Status from V$DATABASE VIEW .
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;
Open Database
SQL> ALTER DATABASE OPEN;
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

Read More