Troubleshooting

1. Troubleshooting Nitin Singh,AAO/EDP,RTI Allahabad

2. Trace Files Each server and background process can write to an associated trace file When an internal error is detected by a process, it dumps information about the error to its trace file. One special trace file is the ALERT file. It Keeps the information all internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur Nitin Singh,AAO/EDP,RTI Allahabad

3. Administrative operations, such as CREATE/ALTER/DROP DATABASE/TABLESPACE/ROLLBACK SEGMENT SQL statements and STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER SQL*DBA statements Several messages and errors relating to the functions of shared server and dispatcher processes The values of all initialization parameters when the database and instance start Nitin Singh,AAO/EDP,RTI Allahabad

4. Specifying the Location of Trace Files All trace files for background processes and the ALERT file are written to the destination specified by the initialization parameter BACKGROUND_DUMP_DEST. All trace files for server processes are written to the destination specified by the initialization parameter USER_DUMP_DEST Nitin Singh,AAO/EDP,RTI Allahabad

5. ORA-00604 error occurred at recursive SQL level num Cause:An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). Action: If the situation described in the next message on the stack can be corrected, do so; otherwise, contact Worldwide Customer Support. Such problem is accompanied by some Secondary errors like ora-01652. Also this error with secondary error will be written to trace Nitin Singh,AAO/EDP,RTI Allahabad

6. Common Oracle Errors that DBA face on day-to -day basis Nitin Singh,AAO/EDP,RTI Allahabad

7. ORA-01000 maximum open cursors exceeded Cause: A host language program attempted to open too many cursors. The maximum number of cursors per user is determined by the initialization parameter OPEN_CURSORS. Action: Modify the program to use fewer cursors. If this error occurs often, shut down ORACLE, increase the value of OPEN_CURSORS, and then restart ORACLE. This problem is generally encountered in Pro *C programs ,VB,D2K applications etc change the OPEN_CURSORS parameter for init.ora file Nitin Singh,AAO/EDP,RTI Allahabad

8. ORA-01122 data file name - failed verification check Cause:The information in the data file is inconsistent with information from the control file. This could be because the control file is from a time earlier than the data files, or the data file size does not match the size specified in the control file, or the data file is corrupted. Action:Make certain that the data files and control files are the correct files for this database, then retry the operation. This error is accompanied with other messages and are encountered during the startup of the Database. These errors are reported when we attempt to recover from a backup. Nitin Singh,AAO/EDP,RTI Allahabad

9. ORA-01545 rollback segment #'name' was not available Cause: Either: 1) A non-existent rollback segment was specified. 2) An instance tried to acquire a rollback segment that is in use or offline. 3) An attempt was made to drop a rollback segment that contains active transactions. Action: Either: 1) Check spelling and specify a valid rollback segment name. If the name of the rollback segment to be changed is unknown, reopen the database and query the data dictionary for the names of existing rollback segments. Nitin Singh,AAO/EDP,RTI Allahabad

10. 2) To start up an instance that tried to acquire this rollback segment, specify another rollback segment in the initialization parameter ROLLBACK_SEGMENTS or bring the tablespace containing the rollback segment online. To drop a rollback segment that is in use, shut down the instance using it; or if it needs recovery, find out the errors that are preventing the rolling back of the transactions, and take appropriate actions. Nitin Singh,AAO/EDP,RTI Allahabad

11. By simply taking the rollback name from the ROLLBACK_SEGMENTS parameter we can start the Database. Most probable reason is that the tablespace must be taken offline with IMMEDAITE option.. Nitin Singh,AAO/EDP,RTI Allahabad

12. ORA-1652 to 1654 01652-- No More space for temporary segment 01653-- No More space to allocate for table 01654-- No More space to allocate index 01652 -00000, "unable to extend temp segment by %s in tablespace %s" // *Cause: Failed to allocate an extent for temp segment in tablespace. // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. Nitin Singh,AAO/EDP,RTI Allahabad

13. The ORA-165X error is possible the most encountered errors by the DBA’s. It provides 2 parameters SIZE and TABLESPACE_NAME If the SYSTEM tablespace has space problem than this error will be accompanied by ORA-604 Nitin Singh,AAO/EDP,RTI Allahabad

14. ORA-01628 max # of extents (number) reached for rollback segment number Cause:An attempt was made to extend a rollback segment that already has reached its maximum size. Action:If possible, increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters. Related errors 163X 1630- for temp segment in tablespace 1631- for extents in table 1632- For extents in index Nitin Singh,AAO/EDP,RTI Allahabad

15. ORA-03113 end-of-file on communication channel This error is always followed by ORA-03114 not connected to ORACLE Cause: A call to ORACLE was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection. Action:Try again. If the message recurs and the program is user-written, check the program For such type of errors Alert log on server should be looked into. Nitin Singh,AAO/EDP,RTI Allahabad

16. ORA-04031 Out of shared memory when trying to allocate num bytes(str) Cause:More shared memory is needed than was allocated in the SGA. Action:Reduce use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameter SHARED_POOL_SIZE. Fragmentation of shared pool memory is common problem.This is faced when trying to load a big size packages,procedures so on. Nitin Singh,AAO/EDP,RTI Allahabad

17. ORA-01045 user name lacks CREATE SESSION privilege; logon denied Cause:An attempt was made to connect to a userid that does not have create session privilege. Action:If required, GRANT the user the CREATE SESSION privilege. Connect as system and give the grants required Nitin Singh,AAO/EDP,RTI Allahabad

18. ORA-01950 no privileges on tablespace 'name' Cause:The attempt to give the user a tablespace quota failed because the user does not have the necessary system privileges. Action:Either grant the user the system privileges needed to create objects in the specified tablespace, or grant the user a specific space resource in the tablespace. Alter user <username > quota 10M on <tbspnm> Nitin Singh,AAO/EDP,RTI Allahabad

19. SQL>Drop user username ORA-01922 CASCADE must be specified to drop 'name' Cause:The user owns objects that need to be dropped along with the user. Action:Use the CASCADE command. Use cascade to drop user and all objects owned by user permanently Nitin Singh,AAO/EDP,RTI Allahabad

20. Connectivity Errors All connections to ORACLE on different computer require listener process running on the server Example listener.ora and TNSnames.ora Path is OS dependent usually located at $ORACLE_HOME/network/admin Nitin Singh,AAO/EDP,RTI Allahabad

21. 12203, 00000, "TNS:unable to connect to destination" // *Cause: Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport // problems. // *Action: Verify that the service name you entered on the command line was correct. Ensure that the listener is running at the remote node and that the ADDRESS parameters specified in TNSNAMES.ORA are correct. Finally, check that all Interchanges needed to make the connection are up and running Start listener on the Server Nitin Singh,AAO/EDP,RTI Allahabad

22. 12154, 00000, "TNS:could not resolve service name" // *Cause: The service name specified is not defined correctly in the // TNSNAMES.ORA file. Enter/Update the entry for the SID for which connection is sought 12545, 00000, "Connect failed because target host or object does not exist" // *Cause: The address specified is not valid, or the program being connected to does not exist. Update the host connection entry in the TCP/IP alias. Nitin Singh,AAO/EDP,RTI Allahabad

23. ERROR: ORA-12505: TNS:listener could not resolve SID given in connect descriptor // *Action: Check to make sure that the SID specified is correct. The SIDs that are currently registered with the listener can be obtained by typing "LSNRCTL SERVICES <listener name>". These SIDs correspond to SID_NAMEs in TNSNAMES.ORA, or db_names in INIT.ORA. // *Comment: This error will be returned if the database instance has not registered with the listener; the instance may need to be started. Nitin Singh,AAO/EDP,RTI Allahabad

24. 12500, 00000, "TNS:listener failed to start a dedicated server process" // *Cause: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly. This is a O/S error . Such problem arises only when the number of oracle sessions exhaust. In SCO Unix The increasing MAXUP process solves the problem Nitin Singh,AAO/EDP,RTI Allahabad