Thursday, June 21, 2012

Common Oracle Errors : Cause & Action


Common Oracle Errors : Cause & Action
http://www.articles.freemegazone.com/oracleErrors.php

In this article I am listing the common oracle errors that are mostly encountered.So lets start identifying the errors their causes and the actions that should be taken when such errors are displayed..
ErrorCauseAction
1ORA ErrorIn an attempt to make a basic peer-to-peer (single protocol network) connection.
  1. Verify that the Database Is Running.
  2. Perform a Loopback Test.
  3. Check that same protocol support is installed as on the database server.
  4. Check base connectivity for underlying network transport.
  5. Check that the client computer has the tnsnames.ora and the sqlnet.ora files in the correct locations.
  6. Test the Net8 foundation layer.
2ORA-00001: unique constraint (constraint_name ) violatedTrying to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index
  1. Drop the unique constraint
  2. Change the constraint to allow duplicate values
  3. Modify your SQL so that a duplicate value is not created
3ORA-00054: resource busy and acquire with NOWAIT specifiedTrying to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword but the resource was unavailable.
  1. Wait and try the command again after a few minutes.
  2. Execute the command without the NOWAIT keyword.
4ORA-00060: deadlock detected while waiting for resourceTrying to execute a statement, but your session was deadlocked because another session had the same resource locked. The statement(s) that you tried to execute have been rolled back.
  1. You can wait a few minutes and try to re-execute the statement(s) that were rolled back.
  2. You can execute a ROLLBACK and re-execute all statements since the last COMMIT was executed.
5ORA-00200: controlfile could not be createdIt was not possible to create the controlfile.Check that there is sufficient disk space and no conflicts in filenames and try to create the controlfile again.
6ORA-00312: online log string thread string: `string`This message reports the filename for details of another message.See the associated messages for the appropriate action to take.
7ORA-00235: control file fixed table inconsistent due to concurrent updateConcurrent update activity on a control file caused a query on a control file fixed table to read inconsistent information.Retry the operation.
8ORA-00313: open failed for members of log group string of thread stringThe online log cannot be opened. May not be able to find file.See accompanying errors and make log available.
9ORA-00320: cannot read file header from log string of thread stringThe file is not available.Restore the log file.
10ORA-00321: log string of thread string, cannot update log file headerCannot write to the log file.Restore the access to the file.
11ORA-00396: error string required fallback to single-pass recoveryThe indicated error caused two-pass instance or crash recovery to fail. Recovery was retried with an alternate (slower) method to avoid the error.Correct the cause of the indicated error (also recorded) so that future instance or crash recovery can succeed with the two-pass algorithm. This usually requires making more main memory available to the recovery process.
12ORA-12154: TNS:could not resolve the connect identifier specifiedYou tried to connect to Oracle, but the service name is either missing from the TNSNAMES.ORA file or is incorrectly defined.
  1. Make sure that the TNSNAMES.ORA file exists and is in the correct directory.
  2. Make sure that the service name that you are connecting to is included in the TNSNAMES.ORA file and that it is correctly defined.
  3. Make sure that there are no syntax errors in the TNSNAMES.ORA file. For example, if there are unmatched brackets in the file, the file will be rendered unusable.
13ORA-06512: at stringline stringBacktrace message as the stack is unwound by unhandled exceptions.
  1. Fix the problem causing the exception or write an exception handler for this condition.
  2. Contact your application administrator or DBA.
14ORA-00904: invalid column nameTrying to execute a SQL statement that included an invalid column name or the column name is missing.
  1. The column name must begin with a letter.
  2. The column name can not be longer than 30 characters.
  3. The column name must be made up of alphanumeric characters or the following special characters:  $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
  4. The column name can not be a reserved word.
15ORA-01722: invalid numberYou executed an SQL statement that tried to convert a string to a number, but it was unsuccessful.
  1. Only numeric fields or character fields that contain numeric values can be used in arithmetic operations. Make sure that all expressions evaluate to numbers.
  2. If you are adding or subtracting from dates, make sure that you added/substracted a numeric value from the date.
16ORA-00600: internal error code; arguments: [string], [string], [string], [string], [string], [string], [string], [string]This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.Report as a bug - the first argument is the internal error number
17ORA-00936: missing expressionTrying to execute a SQL statement but you omitted a part of the syntax.This error most commonly occurs when you try to execute a SELECT statement and forget to the list of the columns in the SELECT statement.
18ORA-01034: Oracle not availableOracle is not started up. Possible causes may be that either the SGA requires more space than was allocated for it or the operating-system variable pointing to the instance is improperly defined.
  1. Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages.
  2. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly.
  3. See the platform specific Oracle documentation.
19ORA-12545: Connect failed because target host or object does not existThe address specified is not valid, or the program being connected to does not exist.
  1. Ensure the ADDRESS parameters have been entered correctly.
  2. Ensure that the executable for the server exists.
  3. If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again.
20ORA-00942: table or view does not exist
  1. SQL statement is executed that references a table or view that either does not exist.
  2. You do not have access to the table or view, or the table or view belongs to another schema and you didn't reference the table by the schema name.
  1. If this error occurred because the table or view does not exist, you will need to create the table or view.
  2. If this error occurred because you do not have access to the table or view, you will need to have the owner of the table/view, or a DBA grant you the appropriate privileges to this object.
  3. If this error occurred because the table/view belongs to another schema and you didn't reference the table by the schema name, you will need to rewrite your SQL to include the schema name.
21ORA-03113: end-of-file on communication channelYou encountered an unexpected end-of-file on the communication channel.
  1. Check for network problems and review the SQL*Net setup.
  2. Look in the alert.log file for any errors.
  3. Test to see whether the server process is dead and whether a trace file was generated at failure time.
22ORA-06502: PL/SQL: numeric or value errorThe executed statement resulted in an arithmetic, numeric, string, conversion, or constraint error.Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
23ORA-04031: unable to allocate num bytes of shared memory num, num, numTried to use more shared memory than was available. SGA private memory has been exhausted.
  1. Reduce your use of shared memory.
  2. Increase the SHARED_POOL_SIZE initialization parameter in the initialization file.
  3. Use the DBMS_SHARED_POOL package to pin large packages.
24ORA-01756: quoted string not properly terminatedA quoted string is not terminated with a single quote mark (')Insert the closing quote and retry the statement.
25ORA-29283: invalid file operationAn attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system.Verify file and directory access privileges on the file system, and if reading, verify that the file exists.
26ORA-00020: maximum number of processes num exceededAll process state objects are in use.
  1. Wait a few minutes and try to re-execute the statement(s).
  2. Shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.
27ORA-12203: TNS:unable to connect to destination
  1. Invalid address specified or destination is not listening.
  2. This error can also occur because of underlying network or network transport problems.
  1. Verify that the net service name you entered was correct.
  2. Verify that the ADDRESS portion of the connect descriptor which corresponds to the net service name is correct.
  3. Ensure that the destination process (for example the listener) is running at the remote node.
28ORA-01017:  invalid username/password; logon deniedLogging into Oracle with an invalid username/password combination.Enter a valid username and password combination in the correct format. If the username and password are entered together, the format is: username/password
29ORA-01403: no data found
  1. Executing a SELECT INTO statement and no rows were returned.
  2. Referencing an uninitialized row in a table.
  3. Reading past the end of file with the UTL_FILE package.
Terminate processing of the data.
30ORA-01033: ORACLE initialization or shutdown in progressAn attempt was made to log on while Oracle is being started up or shutdownWait a few minutes. Then retry the operation.
ORA-00600 : Known Issues and SolutionsOracle ORA-00600
  1. Error

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> show parameter remote_login_passwordfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
D:\>orapwd file=C:\app\nisha\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password force=y entries=5
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> show parameter remote_login_passwordfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
D:\>orapwd file=C:\app\nisha\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password force=y entries=5
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
SQL> show parameter remote_login_passwordfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
D:\>orapwd file=C:\app\nisha\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password force=y entries=5
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
D:\>orapwd file=C:\app\nisha\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password force=y entries=5
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
D:\>orapwd file=C:\app\nisha\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password force=y entries=5
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
D:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 17 20:03:04 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges
ERROR:
ORA-01031: insufficient privileges
alter session set sort_area_size = 1,048,576,000
http://onlineappsdba.com/index.php/2009/07/27/ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/
This post covers quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile and then find root cause of temp tablespace issue.
Extent - An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents make up segment.
Segment - A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. To know more click here
Temporary Tablespace - contains transient data that persists only for the duration of the session. To know more  click here
.Quick Fix for ORA-1652
References/Related
  • 793380.1  ORA-1652 Error Troubleshooting
  • 258941.1  RAC databases and ORA-1652: Unable To Extend Temp Segment By %s In Tablespace TEMP
Related Posts for DB Basics
  1. Blocks , Extents, Segments in Oracle Database : Back to Basics
  1. Tablespace and Datafiles in Oracle Database : Back to Basics
  1. Oracle Database Incarnation : Open Resetlogs , SCN
  1. ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
  1. 11g Database Installation failed with “File not found .. WFMLRSVCApp.ear”
Share This
ORA-1652: unable to extend temp segment by 128 in tablespace - http://heliodias.wordpress.com/
ORA-1652: unable to extend temp segment by 128 in tablespace
   o.object_type,
   o.object_name,
   DECODE(v.locked_mode,
          1, 'no lock',
          
          
          2, 'row share (SS)',
          
          
          3, 'row exclusive (SX)',
          
          
          4, 'shared table (S)',
          
          
          5, 'shared row exclusive (SSX)',
          
          
          6, 'exclusive (X)') lock_mode,
   v.oracle_username,
   v.os_user_name,
   v.session_id
   v.oracle_username,
   v.os_user_name,
   v.session_id
   v.oracle_username,
   v.os_user_name,
   v.session_idfrom
   all_objects o,
   v$locked_object vwhere
   o.object_id = v.object_id;


ColumnDatatypeDescription
WAITING_SESSIONNUMBERThe waiting session
HOLDING_SESSIONNUMBERThe holding session
LOCK_TYPEVARCHAR2(26)The lock type
MODE_HELDVARCHAR2(40)The mode held
MODE_REQUESTEDVARCHAR2(40)The mode requested
LOCK_ID1VARCHAR2(40)Lock ID 1
LOCK_ID2VARCHAR2(40)Lock ID 2

SQL> select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD
--------------- --------------- -------------------------- ----------------------------------------
MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
---------------------------------------- ---------- ----------
           235             364 Transaction                Exclusive
Exclusive                                    196617     151100
SQL>
WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD
--------------- --------------- -------------------------- ----------------------------------------
MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
---------------------------------------- ---------- ----------
           235             364 Transaction                Exclusive
Exclusive                                    196617     151100
SQL>


http://dbaspot.com/content/ora-00600-known-issues-solutions-263/

Oracle ORA-00600 is an issue faced by many oracle database administrators. In this FAQ,we tried to capture some of the known issues that we or our users have faced and reported on. Oracle ORA-00600 is a critical error in the oracle database and oracle support should contancted for resolution of this error. You can read more about the errors and the resolution process below.

An Oracle ORA-00600 [729] :  indicates a memory leak in the UGA. Setting diagnostic events 10262 ignores this error, that is, no ORA-00600 is produced even if a memory leak is detected.


ORA-00600 [16365] :  indicates a Half Duplex Violation. Shared servers and dispatchers cannot both be writing to the same virtual curcuit at the same time because they communicate with a half-duplex protocol (TTC protocol).

ORA-00600 [kghpih:ds] :  and ORA-00600 [17175] may occur in oracle 9.2.0.6 when two processes work on the same library cache object. fixed in oracle 9.2.0.7.. Note:300604.1

An Oracle ORA-00600 [3020]  : Bug.2322620 - RECOVERY HALTED ON PHYSICAL STANDBY WITH ORA-600 AND ORA-10564    Details:              ORA-600 [3020] can occur on recovery of LOB data.Fixed in 8.1.7.5    PAtch available. This error  occurred on our database recovery on the primary server.

ora-00600 [12333] : Most probably a network error that may have caused bad data to be received or the client application may have sent the wrong data in the network buffer.

Oracle ORA-00600 [12700] : Indicates corruption in index, table or the mapping between them. Upon this discovery, oracle marks the whole block as corrupt and users will not have normal access to the data from the corrupted block. This can also break referntial integrity and should be referred to oracle.

ORA-00600 [lxhcsn] : Shutdown may dump ORA-7445. Known Bug 2311529


More information about Oracle ORA-00600 is available at How to resolve Oracle ORA-00600 Errors

Categories:

COMMON ORACLE ERRORS

1. ORA-16766: Redo Apply unexpectedly offline
(Connect to LISTENER 118339vs3001 on port 9101 tcp (Connection timed out or failed)  SID=[XYZ])
2. ORA-1652: unable to extend temp segment by 128 in tablespace TEMP))
3. ORA-00600: internal error code, arguments: [kfncGroupUse:v11:01], [DATA01], [], [], [], [], [], [], [], [], [], []))
4. ORA-1653: unable to extend table SYSADM.PS_MESSAGE_LOG by 8192 in tablespace PTRPTS
5. ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Address not mapped to object] [0x2AAAAC888000] [] []))
6.Oracle Monitor connect failure for system - Unable to connect: ORA-24327: need explicit attach before authenticating a user
7. ORA-27037: unable to obtain file status
8. ORA-12541: TNS:no listener
9. Error connecting to database b90f: ORA-01033: ORACLE initialization or shutdown in progress
10. Error connecting to database b90:  ORA-12528: TNS:listener: all appropriate instances are blocking new connections
11. ORA-01017: invalid username/password; logon denied
12. ORA-16401: archivelog rejected by RFS
((SID=[PRDEE] MSG=process ora_ckpt_PRD - number running [ 0 ], threshold is != 1 Oracle ckpt process for PRD running under oraprdee))
13. ORA-12170: TNS:Connect timeout occurred
14. ORA-28000: the account is locked
15. Error connecting to database ARDV:  ORA-03135: connection lost contact))
16. ORA-12520: TNS:listener could not find available handler for requested type of))
17. ORA-00257: archiver error. Connect internal only, until freed.))
18. ORA-16401: archivelog rejected by RFS))
19. ORA-00060: Deadlock detected. More info in file /path/xp_ora_28433.trc.))
Event:((SID=[ARTTEST] MSG=ORA-00600: internal error code, arguments: [729], [112], [space leak], [], [], [], [], []))
20. ORA-16014: log 8 sequence# 89314 not archived, no available destinations))
21. Error connecting to DG source database riseprd_m: ORA-12170: TNS:Connect timeout occurred))
22. ORA-16766: Redo Apply unexpectedly offline
Event:((SID=[atgprd1] MSG=DG DMON:HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline regex:ORA-0*[1-9]*))
23. SNAP Succeeded but failed to put tablespaces back in online mode. SNAP  Succeeded but errors occurred putting tablespaces back in online mode [ LOG - emsg:log-oracle-alerts ]
24. Datafile /db1/oracle/oradata/tk132.dbf (TK1) is nearing the specified size limit of 8192M
25. filesys /db3 low on space, [ 8 ] percent left, threshold is 10 [ FS - fs_free_pct:/d03 ]
26. filesys /oraarchive low on space, 14 > percent left, threshold is 15 FS - fs_free_pct:/oraarchive >
27. oramon-WCGLB-timeout connecting to database after 30 seconds
oramon-doirpt-ORA-01034: ORACLE not available ORA-27101: shared memory realm does not existSVR4 Error: 2: No such file or directory (DBD ERROR: OCISessionBegin) (PRODUCTION OPERATIONAL)
28. Oracle : brbackup Database backup failed : brbackup Database backup for XIQ failed. Errors were found in /orabackup/XIQ/backup.logs/20120229.logs/brbackup.20120229_00:00:14.log. Please investigate. [ LOG - emsg:log-oracle-alerts ]
29. Event:((SID=[SPMPROD] Error connecting to database SPMPROD: ORA-12537: TNS:connection closed))
30. BlockingLocks: ttp0-BlockingLocks is [ 171 ], threshold is 150 TTMO)) 


ORA-01031: insufficient privileges
Oracle 11.2.0.1
Windowx XP
SQL> select * from v$pwfile_users;
-I am member of ora_dba group too.
-I am getting same error if I tries to login as command prompt as Administrator user.
-Listener is running file and db is registered with listener.
-SQLNET.ORA is having :
SQLNET.AUTHENTICATION_SERVICES= (NONE)
-db name/sid is orcl
-only one db is running on this PC
Where I am missing, please help me.
ANSWER:
The value NTS is going to make you use the O/S authentication whereas NONE is going to make you use the password file, ie.,
/ as sysdba = OS Authentication, for which SQLNET.AUTHENTICATION_SERVICES should be (NTS). And if it is NONE, then you are going to use password file authentication...
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
http://dbataj.blogspot.com/2007/02/ora-12528-tnslistener-all-appropriate.html

ORA-12528: TNS:listener: all appropriate instances are blocking new connections
It is known issue with ORACLE 10G.
Suggestion: Don't Use "@(tns_entry string)" with userid/pwd.
just export or set ORACLE_SID and connect WITHOUT "@"tns_entry.
ORA-00904: : invalid identifier
Using the report name awrrpt_1_5147_5171.txt
select output from table(dbms_workload_repository.awr_report_text( :dbid,
                        *
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-00257 Archiver Error. Connect internal only, until freed
http://www.dbmotive.com/support/oracle-error-codes/?type=ORA&errcode=00257
Your database is running in ARCHIVELOG mode:
SQL> select log_mode from v$database;
LOG_MODE
---------------------
ARCHIVELOG
Because the database is running in archive log mode, the redo log
files are archived to an archive destination.
(one of) the destination(s) have no sufficient space left to write the
archived redo logs to.
SQL> select destination from v$archive_dest;
Check if there is sufficient space left, if not, archive the archived
redo logs to tape/other disk/…
The database should free itself after this error. If this is not the
case try issuing the alter system archive log all; command connected
AS SYSDBA.
http://surachartopun.com/2008/11/ora-00604-error-occurred-at-recursive.html
ORA-00604: error occurred at recursive SQL level 1... ORA-00376:... ORA-01110:
I found error this below... and found out to solve it:
Database mounted.
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/db2/undotbs01.dbf'
Check '/oradata/db2/undotbs01.dbf' file:
$ ls -al /oradata/db2/undotbs01.dbf
-rw-r----- 1 oracle oinstall 325066752 Nov 19 11:44 /oradata/db2/undotbs01.dbf
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ NEEDS RECOVERY
_SYSSMU9_1192403689$ NEEDS RECOVERY
_SYSSMU8_1192403689$ NEEDS RECOVERY
_SYSSMU7_1192403689$ NEEDS RECOVERY
_SYSSMU6_1192403689$ NEEDS RECOVERY
_SYSSMU5_1192403689$ NEEDS RECOVERY
_SYSSMU4_1192403689$ NEEDS RECOVERY
_SYSSMU3_1192403689$ NEEDS RECOVERY
_SYSSMU2_1192403689$ NEEDS RECOVERY
_SYSSMU1_1192403689$ NEEDS RECOVERY
Check undo_tablespace parameter:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------
undo_tablespace string UNDOTBS1
Try to recover it:
SQL> recover datafile 3;
Media recovery complete.
But can't solve it:
So, Create New UNDO TABLESPACE:
SQL> CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE'/oradata/db2/undotbs02.dbf' SIZE 100M AUTOEXTEND
ON NEXT 10M MAXSIZE UNLIMITED RETENTION NOGUARANTEE;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.
SQL> shutdown immediate ;
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/db2/undotbs01.dbf'
But can not shutdown:
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ NEEDS RECOVERY
_SYSSMU9_1192403689$ NEEDS RECOVERY
_SYSSMU8_1192403689$ NEEDS RECOVERY
_SYSSMU7_1192403689$ NEEDS RECOVERY
_SYSSMU6_1192403689$ NEEDS RECOVERY
_SYSSMU5_1192403689$ NEEDS RECOVERY
_SYSSMU4_1192403689$ NEEDS RECOVERY
_SYSSMU3_1192403689$ NEEDS RECOVERY
_SYSSMU2_1192403689$ NEEDS RECOVERY
_SYSSMU1_1192403689$ NEEDS RECOVERY
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE
try to online on old file:
SQL> alter database datafile 3 online;
After that can shutdown...
SQL> shutdown
SQL> startup
.
.
.
Database mounted.
Database opened.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ OFFLINE
_SYSSMU9_1192403689$ OFFLINE
_SYSSMU8_1192403689$ OFFLINE
_SYSSMU7_1192403689$ OFFLINE
_SYSSMU6_1192403689$ OFFLINE
_SYSSMU5_1192403689$ OFFLINE
_SYSSMU4_1192403689$ OFFLINE
_SYSSMU3_1192403689$ OFFLINE
_SYSSMU2_1192403689$ OFFLINE
_SYSSMU1_1192403689$ OFFLINE
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE
But don't want Old rollback segments (OFFLINE)
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE
solved its.......

What You Can Do When Your Database Runs out of Temp Space

http://www.dbspecialists.com/files/presentations/temp_space.html

TEMPORARY (Tablespace) Insanity?

http://oratips-ddf.blogspot.in/2008/02/temporary-tablespace-insanity.html
http://www.dba-oracle.com/t_ora_1652_unable_extend_tips.htm
Increase size of the temp tablespace ( add datafile temperorily and canbe deleted later )
Increase sort_area_size and/or pga_aggregate_target
alter session set sort_area_size = 1,048,576,000
===================================================================
Here is another example of ORA-01652 from an article regarding ORA-12801.
Question:
I understand ORA-01652 is usually caused by running out of space When I run Oracle parallel query, I keep receiving ORA-01652, why?
Answer:
In this case, there was a sort in the parallel query which continues to cause ORA-01652 to be thrown.  Remember, the parallel query coordinator has receives the returned results from the parallel processes as a last step of the OPQ sort.  This being, you should be able to resolve ORA-01652 by increasing TEMP, and perhaps also the sort_area_size.  For advice on this, refer to the statement below:
If this job is running batch, you can do this with an alter session command, as this this case, to one gig:
1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
—–
/u01/oradata/VIS11i/temp01.dbf   TEMP
2. Check if there is any space available in temporary tablespace (segment)
SQL> SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
—-
output like
TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
——————————- ———- ———- ———-
TEMP                                  2548         2536       12
(in above case out of 2 GB only 12 MB is free)
.
3.  Temporary fix
a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;
or
b) Add temp datafile to temporary tablespace as
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 1024M;
.
Root Cause Analysis
1.  Identify temp segment usages per session
–
– Temp segment usage per session.
–
SQL> SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
2. Identify temp segment usages per statement
–
– Temp segment usage per statement.
–
SQL> SELECT  S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.
Previous in series Next in series
Popularity: 26% [?]
http://heliodias.wordpress.com/
Filed under: Uncategorized — heliodias @ 8:55 pm
Sometimes you have a huge space on temporary tablespace and even so some bad query use it all.
First step is investigate the normal usage of sort area, and if you see an abnormal behavior, then it´s time to find out , what´s this query.
SQL> SELECT MAX(SORTS_TOTAL/EXECUTIONS_TOTAL) FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0;
MAX(SORTS_TOTAL/EXECUTIONS_TOTAL)
———————————
                          102173
The above query will show the top temporary resource consumer. Then all you have to do is
SQL> SELECT SQL_ID FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0 AND SORTS_TOTAL/EXECUTIONS_TOTAL>=102173;
SQL_ID
————-
4w84ym20xy7da
And finally
SQL> SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’4w84ym20xy7da’;
Tuning I/O-related waits
http://ayyudba.blogspot.in/2007/10/tuning-io-related-waits.html
Find out who's locking my table with v$locked_object
http://www.xinotes.org/notes/note/699/
You run an update and it hangs. Use this query to find out who's locking the table.
select Find Oracle waiting sessions with DBA_WAITERS
http://www.xinotes.org/notes/note/714/
DBA_WAITERS shows all the sessions that are waiting for a lock.
SQL> set lin 120
Or join the v$session view to find out who it is:
select w.waiting_session, w.mode_requested, s.username, s.machine, s.osuserfrom v$session s, dba_waiters wwhere s.sid = w.holding_session;


ORA-01652:

unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-01152:

file string was not restored from a sufficiently old backup

Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.

Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.


ORA-00257:

archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.


ORA-03113:

end-of-file on communication channel

Cause: The connection between Client and Server process was broken.

Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.


ORA-19870: error reading backup piece cf_0flke56r_1_1

ORA-19507: failed to retrieve sequential file, handle="cf_0flke56r_1_1", parms=""

ORA-27029: skgfrtrv: sbtrestore returned error

ORA-19511: Error received from media manager layer, error text:


RMAN-00571,RMAN-00569,RMAN-03002,RMAN-03014,RMAN-06428


Database recovery using RMAN
SCENARIO:
In RMAN backup JAN 05 piece is available   and we have to restore to current database.
steps:
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;RMAN Database Restore
while doing the recover database step system01.dbf datafile error. recoverey is required scn is expecting
10132161201636
Problem FACED;
RMAN> recover datafile 1;
Starting recover at 28-JAN-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /Prod/DB/db/tech_st/11.1.0/dbs/arch1_4_738954119.dbf
archived log for thread 1 with sequence 7 is already on disk as file /Prod/DB/db/apps_st/data/log01b.dbf
archived log for thread 1 with sequence 8 is already on disk as file /Prod/DB/db/apps_st/data/log02a.dbf
archived log file name=/Prod/DB/db/tech_st/11.1.0/dbs/arch1_4_738954119.dbf thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2011 12:17:27
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 10132161201636
Solution:
we have set the previous scn number to restore the database scn 10132161201630
rman>run{
set until scn 10132161201630;
restore database;
recover database;
alter database open resetlogs;
}
note: it restored the database properly and recovered the database properly and its opened without errors.

Restore a Database from the backup using RMAN

RMAN is the Oracle recommended way to backup and recover an oracle database, even if you perform cold backups. Have been through a few recover scenarios with a client.
1 - Restore a datafile
2 - Restore a complete tablespace
3 - Restore a complete database
1 - Restore a data file - this is a relatively simple exercise, typically an error such as:
ORA-01116: error in opening database file 66
ORA-01110: data file 66: 'datafile path/datafilename'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
To restore:
run {
sql 'alter database datafile 66 offline';
restore datafile 66;
recover datafile 66;
sql 'alter database datafile 66 online';
}
And as if by magic the datafile is backup and running
2 - Restore a tablespace - this will be a bit more painful for users but for the DBA is again fairly simple. An error similar to the above will be noted.
The solution:
run {
sql 'alter tablespace tablespace_name offline immediate';
restore tablespace tablespace_name;
recover tablespace tablespace_name;
sql 'alter tablespace tablespace_name online';
}
And again the database is back and the users are happy.
3 - Complete database restore. Hopefully this will never happen to a dba but like the scouts it is best to be prepared. Potentially the following sort of error could be seen:
SQL> startup
ORACLE instance started.
Total System Global Area 2.3622E+10 bytes
Fixed Size 2126480 bytes
Variable Size 1.1341E+10 bytes
Database Buffers 1.2264E+10 bytes
Redo Buffers 14647296 bytes
ORA-00205: error in identifying control file, check alert log for more info
So the dba is then faced with a number of things:
1 - Very unhappy managers
2 - Very unhappy users
But as I say in all situations... DON'T PANIC!
From an rman prompt type in the following:
startup nomount;
Then
restore controlfile;
Dependent upon the controlfile autobackup you might have to specify the location of the controlfile from the flashback area. Something like the following will be seen:
Starting restore at 13-MAR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=162 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restored backup piece 1
piece handle=/path/o1_mf_s_617126064_2zfl9jy0_.bkp tag=TAG20070313T155424
channel ORA_DISK_1: restore complete
output filename=/oradata/ueltest/control01.ctl
output filename=/oradata/ueltest/control02.ctl
output filename=/oradata/ueltest/control03.ctl
Finished restore at 13-MAR-07
Now we mount the databse, again from within RMAN. Alter database mount;
database mounted
released channel: ORA_DISK_1
Then host out to the command line;
RMAN> host;
Then enter sqlplus / as sysdba.
Then run this command:
select archivelog_change#-1 from v$database;
Exit twice backtp rman
Then run the following command:
run{
set until scn (number from previous sql);
restore database;
recover database;
alter database open resetlogs;
}
But this is very important: after every recovery that has taken place the first thing to do is to take a backup of the database. 

"Hope for the best, prepare for the worst"
-Dharmendra


Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)