Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

NOTE:848387.1 - With NUMA Enabled, Database Fails To Open With ORA-600[ksbmoveme4], ORA-27300, ORA-27301, ORA-27302 Errors Reported
NOTE:300956.1 - Ora-27302: sskgxpsnd1 - Starting Instance
NOTE:314179.1 - Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302
NOTE:3411021.8 - Bug 3411021 - Oracle process may die due to ORA-27300/ORA-27301/ORA-27302 at skgxppost1
NOTE:746888.1 - ORA-27302: Failure Occurred at: skgxpvfymmtu Signalled in the Alert.log
BUG:7620133 - HIGH NUMBER OF DBW* PROCESSES SPAWNED IN 11.1.0.7
NOTE:458442.1 - 10.2.0.3 PMON Crashes on Startup on AIX 5L 5.3 ML05 -- Works on ML06
NOTE:277399.1 - DBMS_SCHEDULER Fails To Execute Jobs With Program_type EXECUTABLE On HP-UX
NOTE:392006.1 - Ora-27300 OS system dependent operation:fork failed with status: 11
NOTE:295832.1 - Unable To Create Database Ora-00603, ORA-27300
NOTE:1252265.1 - ORA-27300 ORA-27301 ORA-27302: failure occurred at: skgpalive1
NOTE:438205.1 - ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash
NOTE:453959.1 - Cannot Connect As "/ as sysdba" ORA-27140 ORA-27300 ORA-27301 ORA-27302 And ORA-27303 In Trace File
NOTE:356640.1 - ORA-27300, ORA-27301, ORA-27302: Failure Occurred At: Skgpalive1 During 'Shutdown'
NOTE:465002.1 - Database Crash With Error ORA-00490
NOTE:466370.1 - ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: OS failure message: Not enough storage ORA-27300 ORA-27302
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:371074.1 - ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
BUG:7232946 - ORA-600[KSKRECONFIGNUMA2] CAUSES INSTANCE CRASH
NOTE:580552.1 - Database Crashes With ORA-04030 ORA-07445 ORA-27300 ORA-27301 ORA-27302
NOTE:557153.1 - ORA-27370 ORA-27301 (Permission denied) When Running Job Of Type EXECUTABLE
BUG:3411021 - LMD PROCESS CRASH DUE TO ORA-27300/ORA-27301/ORA-27302 AT SKGXPPOST1
NOTE:560309.1 - Database Cannot Start Due to Lack of Memory
BUG:6991131 - ORA-27300, ORA-27301 AND ORA-27302 HAPPENED DURING SHUTDOWN USING SRVCTL
NOTE:364353.1 - ORA-00603 ORA-27504 ORA-27300 ORA-27504 in the Alert Log
NOTE:1274030.1 - Startup Instance Failed with ORA-27140 ORA-27300 ORA-27301 ORA-27302 and ORA-27303 on skgpwinit6
NOTE:949468.1 - Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28
NOTE:6629265.8 - Bug 6629265 - Intermittent ORA-27504 / ORA-27300 ioctl error 11
BUG:9059053 - ORA-00603 ORA-27504 ORA-27300 ORA-27301 ORA-27302 IN ALERT.LOG
NOTE:1038058.6 - LIST OF UNIX ERRORS AND EXPLANATIONS
NOTE:297498.1 - Resolving Instance Evictions on Windows Platforms
BUG:10010310 - ORA-27300: INVALID_PROCESS_ID FAILED WITH STATUS: 0; ORA-27302: SKGPALIVE1
NOTE:6441119.8 - Bug 6441119 - Instance crash due to ORA-27300 / ORA-27152

Create a new control file without backup

Create a new control file without backup

Start the database in NOMOUNT mode. For example, enter:

STARTUP NOMOUNT

Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option . The following example assumes that the character set is the default US7ASCII:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1 (
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
     GROUP 2 (
       '/diska/prod/sales/db/log2t1.dbf',
       '/diskb/prod/sales/db/log2t2.dbf'
     )  SIZE 100K,
DATAFILE
     '/diska/prod/sales/db/database1.dbf',
     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):

RECOVER DATABASE

Open the database after recovery completes (RESETLOGS option not required):

ALTER DATABASE OPEN;

Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;

How to change sys password in Oracle 10g

How to change sys password in Oracle 10g

To Change the ""SYS"  Password

SQL> conn sys as sysdba
Enter password: (null) no need to give anything
Connected.
 
SQL> passw system
Changing password for system
New password: (new password)
Retype new password: (new password)
Password changed
 
SQL> conn system/(new password)
Connected.
 
SQL> passw sys
Changing password for sys
New password: (new sys password )
Retype new password: (new sys password )
Password changed
 
SQL>

Package using which session

Package using which session

Trying to compile package but its not compiling and going in infinite loop.

Run this query to now which session is using package. It will give you SIDs

SELECT * FROM v$access WHERE object = 'XXXX_HR_COMMON_PKG';

Now run this command to get SERIAL# of that SID

select SERIAL# from v$session where sid='<SID>';

Now kill sessions

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Its easy :)

Huge generation of archive logs

Huge generation of archive logs

Doc ID 167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
Doc ID 1507157.1 - Master Note: Troubleshooting Redo Logs and Archiving
Doc ID 781999.1 - General Guideline For Sizing The Online Redo Log Files
Doc ID 782935.1 - Troubleshooting High Redo Generation Issues
Doc ID 199298.1 - Diagnosing Excessive Redo Generation - Limit with Nologging