HELP with this ORA-00020, ORA-03113, ORA-24324, ORA-01041

[former_member215439]

Hi Experts!

     OS: WS 2012 Standard R2

     System: SAP ERP 6.0

     Database: Oracle12c   

     I have a finished a BRBACKUP(online consistent) with RC 5 so I investigate why it happens and i discover that the database is down and try to start it up again. Sqlplus /nolog > conn / as sysdba then I have encounter an error ORA-00020: maximum number of processes (300) exceeded . then i search how to fix it and i found out that you need set its parameter, the case is I need to logon as sysdba first before adjusting the parameter which is oracle not allowing me. then i tried to restart the server after the reboot i have successfully connected as sysdba but after the startup a new error occur and ORA-03113: end-of-file on communication channel, and try to start it up again and throws me a new error SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0 Unsafe to proceed, ORA-03114: not connected to ORACLE.

[ i think restarting the server is my wrong move ] :((

I tried to run r3trans -d then its says:

This is r3trans version 6.24 (release 721 - 14.10.14 - 20:14:08).

unicode enabled version

R3trans=>sapparam(1c): No Profile used.

R3trans=>sapparam: SAPSYSTEMNAME neither in Profile nor in Commandline

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_o

ra_tnsname = 'PRD'"

r3trans finished (0012).

Please help!

THanks!

[former_member206552]

Good Day Elvin,

first of all you can fix the connection problem by changing the values in your pfile and start the system with a pfile.

as this is a WS please check and make sure that the oracle services is started, also paste the alert.log entries

also please have a look at this tread : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor | dbaGrid

Best Regards

Marius

[former_member215439]

Hi Sir Marius,,

Please see below:

Successful mount of redo thread 1, with mount id 1954390553

Fri Sep 18 10:12:45 2015

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 1

ALTER DATABASE CLOSE NORMAL

Fri Sep 18 10:14:33 2015

Stopping Emon pool

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Fri Sep 18 10:14:36 2015

Stopping background process VKTM

Fri Sep 18 10:14:47 2015

Instance shutdown complete

Fri Sep 18 10:17:43 2015

Adjusting the default value of parameter parallel_max_servers

from 1920 to 119 due to the value of parameter processes (200)

Starting ORACLE instance (normal) (OS id: 2248)

Fri Sep 18 10:17:43 2015

CLI notifier numLatches:73 maxDescs:475

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 48

Number of processor cores in the system is 24

Number of processor sockets in the system is 2

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

IMODE=BR

ILAT =47

LICENSE_MAX_USERS = 0

SYS auditing is enabled

NOTE: remote asm mode is local (mode 0x1; from cluster type)

NOTE: Using default ASM root directory ASM

NUMA system with 2 nodes detected

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.

Windows NT Version V6.2 

CPU                 : 48 - type 8664, 24 Physical Cores

Process Affinity    : 0x0x0000000000000000

Memory (Avail/Total): Ph:106756M/130964M, Ph+PgF:341769M/366020M

Using parameter settings in server-side spfile E:\ORACLE\PRD\12102\DATABASE\SPFILEPRD.ORA

System parameters with non-default values:

  processes                = 200

  sessions                 = 432

  event                    = "10027"

  event                    = "10028"

  event                    = "10142"

  event                    = "10183"

  event                    = "10191"

  event                    = "10995 level 2"

  event                    = "38068 level 100"

  event                    = "38085"

  event                    = "38087"

  event                    = "44951 level 1024"

  sga_max_size             = 21G

  shared_pool_size         = 1280M

  shared_pool_reserved_size= 128833290

  filesystemio_options     = "setall"

  sga_target               = 17G

  control_files            = "D:\ORACLE\PRD\ORIGLOGA\CNTRL\CNTRLPRD.DBF"

  control_files            = "D:\ORACLE\PRD\ORIGLOGB\CNTRL\CNTRLPRD.DBF"

  control_files            = "E:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF"

  control_file_record_keep_time= 30

  db_block_size            = 8192

  db_cache_size            = 1280M

  compatible               = "11.2.0.3.0"

  log_archive_dest         = "F:\oracle\PRD\oraarch\PRDarch"

  log_archive_format       = "ARC%s_%R.%T"

  log_buffer               = 2496K

  log_checkpoint_interval  = 0

  db_files                 = 254

  log_checkpoints_to_alert = TRUE

  dml_locks                = 4000

  undo_management          = "AUTO"

  undo_tablespace          = "PSAPUNDO"

  undo_retention           = 43200

  recyclebin               = "off"

  remote_login_passwordfile= "EXCLUSIVE"

  job_queue_processes      = 100

  audit_file_dest          = "E:\ORACLE\PRD\12102\RDBMS\AUDIT"

  core_dump_dest           = "E:\ORACLE\PRD\SAPTRACE\BACKGROUND"

  optimizer_features_enable= "10.2.0.1"

  sort_area_size           = 2097152

  sort_area_retained_size  = 0

  db_name                  = "PRD"

  open_cursors             = 800

  os_authent_prefix        = "OPS$"

  optimizer_index_cost_adj = 20

  pga_aggregate_target     = 600M

  workarea_size_policy     = "AUTO"

  statistics_level         = "typical"

  diagnostic_dest          = "E:\ORACLE"

NOTE: remote asm mode is local (mode 0x1; from cluster type)

Starting background process PMON

Fri Sep 18 10:17:52 2015

PMON started with pid=2, OS id=4756

Starting background process PSP0

Fri Sep 18 10:17:53 2015

PSP0 started with pid=3, OS id=3852

Starting background process VKTM

Fri Sep 18 10:17:54 2015

VKTM started with pid=4, OS id=2744 at elevated (Time Critical) priority

Fri Sep 18 10:17:54 2015

VKTM running at (5)millisec precision with DBRM quantum (100)ms

Starting background process GEN0

Fri Sep 18 10:17:55 2015

GEN0 started with pid=5, OS id=4960

Starting background process MMAN

Fri Sep 18 10:17:56 2015

MMAN started with pid=6, OS id=1340

Starting background process DIAG

Fri Sep 18 10:17:56 2015

DIAG started with pid=8, OS id=2328

Starting background process DBRM

Fri Sep 18 10:17:57 2015

DBRM started with pid=9, OS id=3544

Starting background process VKRM

Fri Sep 18 10:17:57 2015

VKRM started with pid=10, OS id=3652

Starting background process DIA0

Fri Sep 18 10:17:57 2015

DIA0 started with pid=11, OS id=1704

Starting background process DBW0

Fri Sep 18 10:17:57 2015

DBW0 started with pid=12, OS id=3552

Starting background process DBW1

Fri Sep 18 10:17:57 2015

DBW1 started with pid=13, OS id=1752

Starting background process DBW2

Fri Sep 18 10:17:57 2015

DBW2 started with pid=14, OS id=3256

Starting background process DBW3

Fri Sep 18 10:17:57 2015

DBW3 started with pid=15, OS id=4924

Starting background process DBW4

Fri Sep 18 10:17:58 2015

DBW4 started with pid=16, OS id=4528

Starting background process DBW5

Fri Sep 18 10:17:58 2015

DBW5 started with pid=17, OS id=3568

Starting background process LGWR

Fri Sep 18 10:17:58 2015

LGWR started with pid=18, OS id=796

Starting background process CKPT

Fri Sep 18 10:17:58 2015

CKPT started with pid=19, OS id=2400

Starting background process SMON

Fri Sep 18 10:17:58 2015

SMON started with pid=21, OS id=3536

Starting background process RECO

Fri Sep 18 10:17:58 2015

RECO started with pid=23, OS id=4104

Starting background process LREG

Fri Sep 18 10:17:58 2015

LREG started with pid=24, OS id=352

Starting background process PXMN

Fri Sep 18 10:17:58 2015

PXMN started with pid=25, OS id=4432

Starting background process MMON

Fri Sep 18 10:17:58 2015

MMON started with pid=26, OS id=4620

Starting background process MMNL

Fri Sep 18 10:17:58 2015

MMNL started with pid=27, OS id=2620

ORACLE_BASE from environment = E:\oracle

Fri Sep 18 10:17:58 2015

alter database mount exclusive

Fri Sep 18 10:18:01 2015

Using default pga_aggregate_limit of 2048 MB

Fri Sep 18 10:18:02 2015

Successful mount of redo thread 1, with mount id 1954377566

Fri Sep 18 10:18:02 2015

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount exclusive

alter database open

Ping without log force is disabled

.

Starting background process TMON

Fri Sep 18 10:18:03 2015

TMON started with pid=7, OS id=3572

Fri Sep 18 10:18:03 2015

LGWR: STARTING ARCH PROCESSES

Starting background process ARC0

Fri Sep 18 10:18:03 2015

ARC0 started with pid=29, OS id=3408

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

Fri Sep 18 10:18:03 2015

ARC0: STARTING ARCH PROCESSES

Starting background process ARC1

Fri Sep 18 10:18:03 2015

ARC1 started with pid=30, OS id=4516

Starting background process ARC2

Fri Sep 18 10:18:03 2015

ARC2 started with pid=31, OS id=2584

Fri Sep 18 10:18:03 2015

Unable to create archive log file 'F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001'

Fri Sep 18 10:18:03 2015

Errors in file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_ora_4908.trc:

ORA-19504: failed to create file "F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001"

ORA-27040: file create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 5) Access is denied.

ARCH: Error 19504 Creating archive log file to 'F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001'

Fri Sep 18 10:18:03 2015

Errors in file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_ora_4908.trc:

ORA-16038: log 1 sequence# 1081 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1: 'E:\ORACLE\PRD\ORIGLOGA\LOG_G1_M1.DBF'

ORA-00312: online log 1 thread 1: 'F:\ORACLE\PRD\MIRRLOGA\LOG_G1_M2.DBF'

Fri Sep 18 10:18:03 2015

System state dump requested by (instance=1, osid=4908), summary=[abnormal instance termination].

System State dumped to trace file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_diag_2328_20150918101803.trc

Fri Sep 18 10:18:03 2015

USER (ospid: 4908): terminating the instance due to error 16038

Fri Sep 18 10:18:03 2015

Dumping diagnostic data in directory=[cdmp_20150918101803], requested by (instance=1, osid=4908), summary=[abnormal instance termination].

Starting background process ARC3

Process ARC3 submission failed with error = 1092

Fri Sep 18 10:18:04 2015

Errors in file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_arc0_3408.trc:

ORA-00444: background process "ARC3" failed while starting

ORA-01092: ORACLE instance terminated. Disconnection forced

Fri Sep 18 10:18:04 2015

ARC0: Detected ARCH process failure

ARC0: Detected ARCH process failure

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

Fri Sep 18 10:18:07 2015

Instance terminated by USER, pid = 4908

Fri Sep 18 10:18:40 2015

Adjusting the default value of parameter parallel_max_servers

from 1920 to 119 due to the value of parameter processes (200)

Starting ORACLE instance (normal) (OS id: 2940)

Fri Sep 18 10:18:40 2015

CLI notifier numLatches:73 maxDescs:475

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 48

Number of processor cores in the system is 24

Number of processor sockets in the system is 2

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

IMODE=BR

ILAT =47

LICENSE_MAX_USERS = 0

SYS auditing is enabled

NOTE: remote asm mode is local (mode 0x1; from cluster type)

NOTE: Using default ASM root directory ASM

NUMA system with 2 nodes detected

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.

Windows NT Version V6.2 

CPU                 : 48 - type 8664, 24 Physical Cores

Process Affinity    : 0x0x0000000000000000

Memory (Avail/Total): Ph:106783M/130964M, Ph+PgF:341816M/366020M

Using parameter settings in server-side spfile E:\ORACLE\PRD\12102\DATABASE\SPFILEPRD.ORA

System parameters with non-default values:

  processes                = 200

  sessions                 = 432

  event                    = "10027"

  event                    = "10028"

  event                    = "10142"

  event                    = "10183"

  event                    = "10191"

  event                    = "10995 level 2"

  event                    = "38068 level 100"

  event                    = "38085"

  event                    = "38087"

  event                    = "44951 level 1024"

  sga_max_size             = 21G

  shared_pool_size         = 1280M

  shared_pool_reserved_size= 128833290

  filesystemio_options     = "setall"

  sga_target               = 17G

  control_files            = "D:\ORACLE\PRD\ORIGLOGA\CNTRL\CNTRLPRD.DBF"

  control_files            = "D:\ORACLE\PRD\ORIGLOGB\CNTRL\CNTRLPRD.DBF"

  control_files            = "E:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF"

  control_file_record_keep_time= 30

  db_block_size            = 8192

  db_cache_size            = 1280M

  compatible               = "11.2.0.3.0"

  log_archive_dest         = "F:\oracle\PRD\oraarch\PRDarch"

  log_archive_format       = "ARC%s_%R.%T"

  log_buffer               = 2496K

  log_checkpoint_interval  = 0

  db_files                 = 254

  log_checkpoints_to_alert = TRUE

  dml_locks                = 4000

  undo_management          = "AUTO"

  undo_tablespace          = "PSAPUNDO"

  undo_retention           = 43200

  recyclebin               = "off"

  remote_login_passwordfile= "EXCLUSIVE"

  job_queue_processes      = 100

  audit_file_dest          = "E:\ORACLE\PRD\12102\RDBMS\AUDIT"

  core_dump_dest           = "E:\ORACLE\PRD\SAPTRACE\BACKGROUND"

  optimizer_features_enable= "10.2.0.1"

  sort_area_size           = 2097152

  sort_area_retained_size  = 0

  db_name                  = "PRD"

  open_cursors             = 800

  os_authent_prefix        = "OPS$"

  optimizer_index_cost_adj = 20

  pga_aggregate_target     = 600M

  workarea_size_policy     = "AUTO"

  statistics_level         = "typical"

  diagnostic_dest          = "E:\ORACLE"

NOTE: remote asm mode is local (mode 0x1; from cluster type)

Starting background process PMON

Fri Sep 18 10:18:47 2015

PMON started with pid=2, OS id=4484

Starting background process PSP0

Fri Sep 18 10:18:47 2015

PSP0 started with pid=3, OS id=3532

Starting background process VKTM

Fri Sep 18 10:18:48 2015

VKTM started with pid=4, OS id=3340 at elevated (Time Critical) priority

Fri Sep 18 10:18:48 2015

VKTM running at (5)millisec precision with DBRM quantum (100)ms

Starting background process GEN0

Fri Sep 18 10:18:49 2015

GEN0 started with pid=5, OS id=3620

Starting background process MMAN

Fri Sep 18 10:18:49 2015

MMAN started with pid=6, OS id=5096

Starting background process DIAG

Fri Sep 18 10:18:49 2015

DIAG started with pid=8, OS id=1716

Starting background process DBRM

Fri Sep 18 10:18:49 2015

DBRM started with pid=9, OS id=3060

Starting background process VKRM

Fri Sep 18 10:18:50 2015

VKRM started with pid=10, OS id=4824

Starting background process DIA0

Fri Sep 18 10:18:50 2015

DIA0 started with pid=11, OS id=740

Starting background process DBW0

Fri Sep 18 10:18:50 2015

DBW0 started with pid=12, OS id=2660

Starting background process DBW1

Fri Sep 18 10:18:50 2015

DBW1 started with pid=13, OS id=1920

Starting background process DBW2

Fri Sep 18 10:18:50 2015

DBW2 started with pid=14, OS id=4548

Starting background process DBW3

Fri Sep 18 10:18:50 2015

DBW3 started with pid=15, OS id=2916

Starting background process DBW4

Fri Sep 18 10:18:50 2015

DBW4 started with pid=16, OS id=3416

Starting background process DBW5

Fri Sep 18 10:18:51 2015

DBW5 started with pid=17, OS id=3564

Starting background process LGWR

Fri Sep 18 10:18:51 2015

LGWR started with pid=18, OS id=5032

Starting background process CKPT

Fri Sep 18 10:18:51 2015

CKPT started with pid=19, OS id=4440

Starting background process SMON

Fri Sep 18 10:18:51 2015

SMON started with pid=21, OS id=3224

Starting background process RECO

Fri Sep 18 10:18:51 2015

RECO started with pid=23, OS id=2344

Starting background process LREG

Fri Sep 18 10:18:52 2015

LREG started with pid=7, OS id=4352

Starting background process PXMN

Fri Sep 18 10:18:52 2015

PXMN started with pid=24, OS id=4744

Starting background process MMON

Fri Sep 18 10:18:52 2015

MMON started with pid=25, OS id=4428

Starting background process MMNL

Fri Sep 18 10:18:52 2015

MMNL started with pid=26, OS id=1712

ORACLE_BASE from environment = E:\oracle

Fri Sep 18 10:18:52 2015

ALTER DATABASE   MOUNT

Fri Sep 18 10:18:54 2015

Using default pga_aggregate_limit of 2048 MB

Fri Sep 18 10:18:57 2015

Successful mount of redo thread 1, with mount id 1954388626

Fri Sep 18 10:18:57 2015

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Fri Sep 18 10:18:57 2015

ALTER DATABASE OPEN

Ping without log force is disabled

.

Starting background process TMON

Fri Sep 18 10:18:57 2015

TMON started with pid=28, OS id=4740

Fri Sep 18 10:18:57 2015

LGWR: STARTING ARCH PROCESSES

Starting background process ARC0

Fri Sep 18 10:18:57 2015

ARC0 started with pid=29, OS id=2448

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

Fri Sep 18 10:18:57 2015

Unable to create archive log file 'F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001'

Fri Sep 18 10:18:57 2015

Errors in file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_ora_2632.trc:

ORA-19504: failed to create file "F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001"

ORA-27040: file create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 5) Access is denied.

ARCH: Error 19504 Creating archive log file to 'F:\ORACLE\PRD\ORAARCH\PRDARCH\ARC1081_0889093526.0001'

Fri Sep 18 10:18:57 2015

Errors in file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_ora_2632.trc:

ORA-16038: log 1 sequence# 1081 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1: 'E:\ORACLE\PRD\ORIGLOGA\LOG_G1_M1.DBF'

ORA-00312: online log 1 thread 1: 'F:\ORACLE\PRD\MIRRLOGA\LOG_G1_M2.DBF'

USER (ospid: 2632): terminating the instance due to error 16038

Fri Sep 18 10:18:57 2015

System state dump requested by (instance=1, osid=2632), summary=[abnormal instance termination].

System State dumped to trace file E:\ORACLE\diag\rdbms\prd\prd\trace\prd_diag_1716_20150918101857.trc

Fri Sep 18 10:18:57 2015

Dumping diagnostic data in directory=[cdmp_20150918101857], requested by (instance=1, osid=2632), summary=[abnormal instance termination].

Fri Sep 18 10:18:57 2015

ARC0: STARTING ARCH PROCESSES

Fri Sep 18 10:18:58 2015

Logins disabled; aborting ARCH process startup (1092)

ARC0: Archival disabled due to shutdown: 1092

Shutting down archive processes

Archiving is disabled

Fri Sep 18 10:19:01 2015

Instance terminated by USER, pid = 2632