controlfile problems!

[former_member215439]

During installation of SAP, i have encounter this

1.)

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file

'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

[former_member182657 · Accepted solution]

Hi James,

Could you please share what exactly the issue you're facing now.Are you getting

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

If ORA-01110,then you need to perform the recovery to make the DB consistent for further actions.Refer alert_<SID>.log file for more details on it.

If getting

ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Which i supposed the issue has already been resolved by you,anyways check for the correct path,permissions and owner ships to the directories by again referring alert_<SID>.log file.

Hope this will help you.

Regards,

[former_member1232 · Accepted solution]

Check the permissions of control files and also space available in orilogA/orilogB/sapdata1/cntrl

Thank you,

Amit Lal

[Former Member]

Hi James, check for user permissions which you are installing this system.

[JamesZ]

sapinst log shows the error below:

CJS-00087  Mapping of tables/objects to tablespace information is missing. DIAGNOSIS: Could not find file C:\usr\sap\CSN\SYS\exe\uc\NTAMD64\sapconn_role.sql

Where did you get the mentioned error?

Best regards,

James

[former_member215439]

Hi James Zhang,

SQL>Startup nomount

SQL>CREATE CONTROLFILE REUSE DATABASE "PRD" RESETLOGS;

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file

'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

[former_member215439]

Hi All,

after the recovery

startup upgrade

Total System Global Area 2.2448E+10 bytes

Fixed Size                  2265256 bytes

Variable Size            5771366232 bytes

Database Buffers         1.6643E+10 bytes

Redo Buffers               31272960 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

so i tried to resetlogs then

alter database open resetlogs;

and last i get this error

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

[JamesZ]

the previous error on control file was solved?

The latest error clearly mentions that "G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1" needs to be restored.

Best regards,
James

[former_member215439]

not yet solved..

i already recovered the datafiles and archfiles.

[former_member215439]

i tried to used *_allow_resetlogs_corruption but its not working.

[Former Member]

Hi James,

Just a quick question have you upgrade the DB or installed any new DB patche set during the installtion. In that case you need to go for an startup upgarde.

Then run below commands:-

STARTUP UPGRADE

@<oracle home>/rdbms/admin/catupgrd.sql

SHUTDOWN

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

Also can you paste the Alert_SID.log file here for the latest error.

Let us know if this helped you.

regards,

Ram

[former_member215439]

Hi Ram,

I'm doing a system copy of our production,

i have done what you said

after running the @<oracle home>/rdbms/admin/catupgrd.sql

i get this error

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

                 *

ERROR at line 1:

ORA-01722: invalid number

then i proceed to shutdown > startup mount > alter database open resetlogs and an error appear

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

[former_member215439]

Hi All,

please see the alert log below:

System parameters with non-default values:

  processes                = 250

  sessions                 = 400

  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               = 1703936

  log_checkpoint_interval  = 0

  db_files                 = 254

  _allow_resetlogs_corruption= TRUE

  log_checkpoints_to_alert = TRUE

  dml_locks                = 4000

  undo_management          = "AUTO"

  undo_tablespace          = "PSAPUNDO"

  undo_retention           = 43200

  recyclebin               = "off"

  _mutex_wait_time         = 10

  _mutex_wait_scheme       = 1

  remote_os_authent        = TRUE

  sec_case_sensitive_logon = FALSE

  remote_login_passwordfile= "EXCLUSIVE"

  job_queue_processes      = 1

  _fix_control             = "9495669:ON"

  _fix_control             = "9196440:ON"

  _fix_control             = "8937971:ON"

  _fix_control             = "6972291:ON"

  _fix_control             = "6626018:ON"

  _fix_control             = "6440977:ON"

  _fix_control             = "6430500:ON"

  _fix_control             = "6399597:ON"

  _fix_control             = "6055658:OFF"

  _fix_control             = "5705630:ON"

  _fix_control             = "5099019:ON"

  _fix_control             = "6120483:OFF"

  _fix_control             = "7168184:OFF"

  background_dump_dest     = "E:\Oracle\diag\rdbms\prd\prd\trace"

  user_dump_dest           = "E:\Oracle\diag\rdbms\prd\prd\trace"

  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_extended_cursor_sharing_rel= "NONE"

  _optimizer_adaptive_cursor_sharing= FALSE

  optimizer_index_cost_adj = 20

  _optim_peek_user_binds   = FALSE

  pga_aggregate_target     = 600M

  workarea_size_policy     = "AUTO"

  statistics_level         = "typical"

  _optimizer_use_feedback  = FALSE

Deprecated system parameters with specified values:

  remote_os_authent       

  background_dump_dest    

  user_dump_dest          

End of deprecated system parameter listing

Fri Sep 11 09:41:54 2015

PMON started with pid=2, OS id=3588

Fri Sep 11 09:41:54 2015

PSP0 started with pid=3, OS id=4032

Fri Sep 11 09:41:55 2015

VKTM started with pid=4, OS id=3828 at elevated priority

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

Fri Sep 11 09:41:55 2015

GEN0 started with pid=5, OS id=1256

Fri Sep 11 09:41:55 2015

DIAG started with pid=6, OS id=3948

Fri Sep 11 09:41:55 2015

DBRM started with pid=7, OS id=3096

Fri Sep 11 09:41:55 2015

DIA0 started with pid=8, OS id=4020

Fri Sep 11 09:41:55 2015

MMAN started with pid=9, OS id=2296

Fri Sep 11 09:41:55 2015

DBW0 started with pid=10, OS id=4088

Fri Sep 11 09:41:55 2015

LGWR started with pid=11, OS id=3448

Fri Sep 11 09:41:55 2015

CKPT started with pid=12, OS id=3108

Fri Sep 11 09:41:55 2015

SMON started with pid=13, OS id=3192

Fri Sep 11 09:41:55 2015

RECO started with pid=14, OS id=3636

Fri Sep 11 09:41:55 2015

MMON started with pid=15, OS id=3244

Fri Sep 11 09:41:55 2015

MMNL started with pid=16, OS id=748

ORACLE_BASE from environment = E:\Oracle

Fri Sep 11 09:41:55 2015

ALTER DATABASE   MOUNT

Successful mount of redo thread 1, with mount id 1953791587

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Fri Sep 11 09:42:09 2015

alter database open resetlogs

ORA-1139 signalled during: alter database open resetlogs...

i think why i get an error because of the optimizer_features_enable= "10.2.0.1"??

I appreciates your help!

Thanks,

jehizon

[former_member215439]

I have installed 11g not 10.2.0.1

[JamesZ]

Hi James,

Please attach the sapinst_dev.log.

Best regards,

James