Error During Installation: ORA-02180: invalid option for CREATE TABLESPACE

[Former Member]

Hi all,

This is our enviroment,

Windows 2000 Service Pack 4

We are installing SAP R/3 Enterprise 4.7 Ext. 200 SR1

First we installed:

Oracle: 9.2

Patch: 9.2.0.7

SAP Central Instance

Now we are installing the DB Instance, but it gives this error:

INFO 2007-11-22 16:58:20

Working directory changed to C:\Program Files\sapinst_instdir\R3E47X2\SYSTEM\ABAP\ORA\NUC\DB.

ERROR 2007-11-22 16:58:21

CJS-00084 SQL statement or script failed.DIAGNOSIS: Error message: ORA-02180: invalid option for CREATE TABLESPACE

We are in the step 19 of 34

Create Tablespaces

Please some tips.

Regards,

Erick

[markus_doehr2 · Accepted solution]

Please check the log

ora_sql_result.log

and post the relevant lines here.

--

Markus

[Former Member]

Hi Eric,

You can also find the Create tablespace syntax in the alert_SID.log which is located under \oracle\SID\saptrace\background.

Can you provide this create tablespace command.

Thanks,

Maniss

[Former Member]

Hi! Thanks a lot for the reply.

This is our <b>ora_sql_results.log</b>

================================================================================

2007-11-23, 07:31:09 SAPINST ORACLE start logging for

connect / as sysdba ;

set newpage 0

set space 0

set pagesize 0

set linesize 32767

set markup HTML off

set heading off

set verify off

set feedback off

set trimspool on

set sqlprompt SQL>

set termout on

set verify off

set echo off

spool ora_query3_tmp0_1.res

SELECT STATUS FROM V$INSTANCE;

spool off

exit;

Output of SQL executing program:

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Nov 23 07:31:09 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.

OPEN

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SAPINST: End of output of SQL executing program D:\oracle\TVD\920/bin/sqlplus.

2007-11-23, 07:31:10 SAPINST ORACLE stop logging

================================================================================

2007-11-23, 07:31:10 SAPINST ORACLE start logging for

connect / as sysdba ;

CREATE TABLESPACE PSAP@SCHEMAID@ DATAFILE 'E:\oracle\TVD\sapdata4\psap@schemaid@_1\psap@schemaid@.data1' SIZE 2000M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;

exit;

Output of SQL executing program:

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Nov 23 07:31:10 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.

CREATE TABLESPACE PSAP@SCHEMAID@ DATAFILE 'E:\oracle\TVD\sapdata4\psap@schemaid@_1\psap@schemaid@.data1' SIZE 2000M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

*

ERROR at line 1:

ORA-02180: invalid option for CREATE TABLESPACE

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SAPINST: End of output of SQL executing program D:\oracle\TVD\920/bin/sqlplus.

SAPINST found errors.

SAPINST The current process environment may be found in sapinst_ora_environment.log.

2007-11-23, 07:31:10 SAPINST ORACLE stop logging

-

---

And this is <b>our alert_SID.log</b>

Dump file d:\oracle\tvd\saptrace\background\alert_tvd.log

Thu Nov 22 16:27:45 2007

ORACLE V9.2.0.7.0 - Production vsnsta=0

vsnsql=12 vsnxtr=3

Windows 2000 Version 5.0 Service Pack 4, CPU type 586

Thu Nov 22 16:27:45 2007

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

SCN scheme 2

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up ORACLE RDBMS Version: 9.2.0.7.0.

System parameters with non-default values:

processes = 80

sessions = 96

shared_pool_size = 251658240

sga_max_size = 546906620

shared_pool_reserved_size= 24363663

enqueue_resources = 8000

control_files = E:\oracle\TVD\origlogA\cntrl\cntrlTVD.dbf, E:\oracle\TVD\sapdata1\system_1\cntrl\cntrlTVD.dbf, D:\oracle\TVD\saparch\cntrl\cntrlTVD.dbf

db_block_size = 8192

db_cache_size = 251658240

compatible = 9.2.0

log_archive_start = TRUE

log_archive_dest = D:\oracle\TVD\oraarch\TVDarch

log_buffer = 1048576

log_checkpoint_interval = 0

db_files = 254

fast_start_mttr_target = 900

log_checkpoints_to_alert = TRUE

control_file_record_keep_time= 30

dml_locks = 4000

transaction_auditing = FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

remote_os_authent = TRUE

hash_join_enabled = FALSE

background_dump_dest = D:\oracle\TVD\saptrace\background

user_dump_dest = D:\oracle\TVD\saptrace\usertrace

core_dump_dest = D:\oracle\TVD\saptrace\background

optimizer_features_enable= 9.2.0

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = TVD

open_cursors = 800

pga_aggregate_target = 324848844

workarea_size_policy = AUTO

statistics_level = typical

PMON started with pid=2

DBW0 started with pid=3

LGWR started with pid=4

CKPT started with pid=5

SMON started with pid=6

RECO started with pid=7

Thu Nov 22 16:27:48 2007

ARCH: STARTING ARCH PROCESSES

ARC0 started with pid=8

ARC1 started with pid=9

Thu Nov 22 16:27:48 2007

ARC0: Archival started

Thu Nov 22 16:27:48 2007

ARCH: STARTING ARCH PROCESSES COMPLETE

Thu Nov 22 16:27:48 2007

ARC1: Archival started

Thu Nov 22 16:27:48 2007

ARC0: Becoming the 'no FAL' ARCH

Thu Nov 22 16:27:48 2007

ARC1: Becoming the heartbeat ARCH

Thu Nov 22 16:27:48 2007

ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted

Thu Nov 22 16:27:48 2007

ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted

Thu Nov 22 16:27:49 2007

alter database mount exclusive

Thu Nov 22 16:27:49 2007

ORA-00202: controlfile: 'E:\oracle\TVD\origlogA\cntrl\cntrlTVD.dbf'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

Thu Nov 22 16:27:50 2007

ORA-205 signalled during: alter database mount exclusive...

Shutting down instance (abort)

License high water mark = 1

Instance terminated by USER, pid = 1768

Thu Nov 22 16:28:05 2007

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

SCN scheme 2

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up ORACLE RDBMS Version: 9.2.0.7.0.

System parameters with non-default values:

processes = 80

sessions = 96

shared_pool_size = 251658240

sga_max_size = 546906620

shared_pool_reserved_size= 24363663

enqueue_resources = 8000

control_files = E:\oracle\TVD\origlogA\cntrl\cntrlTVD.dbf, E:\oracle\TVD\sapdata1\system_1\cntrl\cntrlTVD.dbf, D:\oracle\TVD\saparch\cntrl\cntrlTVD.dbf

db_block_size = 8192

db_cache_size = 251658240

compatible = 9.2.0

log_archive_start = TRUE

log_archive_dest = D:\oracle\TVD\oraarch\TVDarch

log_buffer = 1048576

log_checkpoint_interval = 0

db_files = 254

fast_start_mttr_target = 900

log_checkpoints_to_alert = TRUE

control_file_record_keep_time= 30

dml_locks = 4000

transaction_auditing = FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

remote_os_authent = TRUE

hash_join_enabled = FALSE

background_dump_dest = D:\oracle\TVD\saptrace\background

user_dump_dest = D:\oracle\TVD\saptrace\usertrace

core_dump_dest = D:\oracle\TVD\saptrace\background

optimizer_features_enable= 9.2.0

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = TVD

open_cursors = 800

pga_aggregate_target = 324848844

workarea_size_policy = AUTO

statistics_level = typical

PMON started with pid=2

DBW0 started with pid=3

LGWR started with pid=4

CKPT started with pid=5

SMON started with pid=6

RECO started with pid=7

Thu Nov 22 16:28:08 2007

ARCH: STARTING ARCH PROCESSES

ARC0 started with pid=8

ARC0: Archival started

ARC1 started with pid=9

Thu Nov 22 16:28:08 2007

ARCH: STARTING ARCH PROCESSES COMPLETE

Thu Nov 22 16:28:08 2007

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted

Thu Nov 22 16:28:09 2007

ARC1: Archival started

Thu Nov 22 16:28:09 2007

ARC1: Becoming the heartbeat ARCH

ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted

Thu Nov 22 16:28:11 2007

CREATE DATABASE TVD CONTROLFILE REUSE MAXLOGFILES 255 MAXLOGMEMBERS 3 MAXLOGHISTORY 1000 MAXDATAFILES 254 MAXINSTANCES 50 NOARCHIVELOG CHARACTER SET WE8DEC NATIONAL CHARACTER SET UTF8 DATAFILE 'E:\oracle\TVD\sapdata1\system_1\system.data1' SIZE 550M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE PSAPTEMP TEMPFILE 'E:\oracle\TVD\sapdata3\temp_1\temp.data1' SIZE 540M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M UNDO TABLESPACE PSAPUNDO DATAFILE 'E:\oracle\TVD\sapdata2\undo_1\undo.data1' SIZE 700M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M

LOGFILE GROUP 1 ('E:
oracle/TVD/origlogA/log_g11m1.dbf',

'D:
oracle/TVD/mirrlogA/log_g11m2.dbf') SIZE 50M REUSE ,

GROUP 2 ('E:
oracle/TVD/origlogB/log_g12m1.dbf',

'D:
oracle/TVD/mirrlogB/log_g12m2.dbf') SIZE 50M REUSE ,

GROUP 3 ('E:
oracle/TVD/origlogA/log_g13m1.dbf',

'D:
oracle/TVD/mirrlogA/log_g13m2.dbf') SIZE 50M REUSE ,

GROUP 4 ('E:
oracle/TVD/origlogB/log_g14m1.dbf',

'D:
oracle/TVD/mirrlogB/log_g14m2.dbf') SIZE 50M REUSE

Thu Nov 22 16:28:12 2007

Database mounted in Exclusive Mode.

Thu Nov 22 16:28:29 2007

Successful mount of redo thread 1, with mount id 1434613531

Assigning activation ID 1434613531 (0x5582771b)

Thread 1 opened at log sequence 1

Thu Nov 22 16:28:30 2007

ARC1: Media recovery disabled

Thu Nov 22 16:28:31 2007

Current log# 1 seq# 1 mem# 0: E:\ORACLE\TVD\ORIGLOGA\LOG_G11M1.DBF

Current log# 1 seq# 1 mem# 1: D:\ORACLE\TVD\MIRRLOGA\LOG_G11M2.DBF

Successful open of redo thread 1

Thu Nov 22 16:28:31 2007

ARC0: Media recovery disabled

Thu Nov 22 16:28:31 2007

SMON: enabling cache recovery

Thu Nov 22 16:28:32 2007

WARNING: Default passwords for SYS and SYSTEM will be used.

Please change the passwords.

Thu Nov 22 16:28:32 2007

create tablespace SYSTEM datafile 'E:\oracle\TVD\sapdata1\system_1\system.data1' SIZE 550M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M

EXTENT MANAGEMENT LOCAL online

Thu Nov 22 16:28:51 2007

Completed: create tablespace SYSTEM datafile 'E:\oracle\TVD\

Thu Nov 22 16:28:51 2007

create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

Thu Nov 22 16:29:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:29:10 2007

CREATE UNDO TABLESPACE PSAPUNDO DATAFILE 'E:\oracle\TVD\sapdata2\undo_1\undo.data1' SIZE 700M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M

Thu Nov 22 16:29:35 2007

Successfully onlined Undo Tablespace 1.

Completed: CREATE UNDO TABLESPACE PSAPUNDO DATAFILE 'E:\orac

Thu Nov 22 16:29:36 2007

CREATE TEMPORARY TABLESPACE PSAPTEMP TEMPFILE 'E:\oracle\TVD\sapdata3\temp_1\temp.data1' SIZE 540M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M

Completed: CREATE TEMPORARY TABLESPACE PSAPTEMP TEMPFILE 'E:

Thu Nov 22 16:29:36 2007

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTE

Thu Nov 22 16:29:39 2007

SMON: enabling tx recovery

Thu Nov 22 16:29:39 2007

Beginning local checkpoint up to RBA [0x1.538b.10], SCN: 0x0000.000019d9

Completed checkpoint up to RBA [0x1.538b.10], SCN: 0x0000.000019d9

kwqplbsc: open notifier exception err 604

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: CREATE DATABASE TVD CONTROLFILE REUSE MAXLOGFILES

Thu Nov 22 16:30:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:31:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:31:28 2007

Beginning log switch checkpoint up to RBA [0x2.2.10], SCN: 0x0000.00006489

Thread 1 advanced to log sequence 2

Thu Nov 22 16:31:28 2007

ARC1: Media recovery disabled

Thu Nov 22 16:31:29 2007

Current log# 2 seq# 2 mem# 0: E:\ORACLE\TVD\ORIGLOGB\LOG_G12M1.DBF

Current log# 2 seq# 2 mem# 1: D:\ORACLE\TVD\MIRRLOGB\LOG_G12M2.DBF

Thu Nov 22 16:32:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:33:08 2007

ARC0: Media recovery disabled

Thu Nov 22 16:33:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:33:16 2007

Completed checkpoint up to RBA [0x2.2.10], SCN: 0x0000.00006489

Thu Nov 22 16:33:26 2007

Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 0x0000.00009227

Thread 1 advanced to log sequence 3

Thu Nov 22 16:33:26 2007

ARC0: Media recovery disabled

Thu Nov 22 16:33:26 2007

Current log# 3 seq# 3 mem# 0: E:\ORACLE\TVD\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 3 mem# 1: D:\ORACLE\TVD\MIRRLOGA\LOG_G13M2.DBF

Thu Nov 22 16:34:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:35:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:35:19 2007

Completed checkpoint up to RBA [0x3.2.10], SCN: 0x0000.00009227

Thu Nov 22 16:35:28 2007

Beginning log switch checkpoint up to RBA [0x4.2.10], SCN: 0x0000.0000b0ac

Thread 1 advanced to log sequence 4

Current log# 4 seq# 4 mem# 0: E:\ORACLE\TVD\ORIGLOGB\LOG_G14M1.DBF

Thu Nov 22 16:35:28 2007

ARC1: Media recovery disabled

Thu Nov 22 16:35:28 2007

Current log# 4 seq# 4 mem# 1: D:\ORACLE\TVD\MIRRLOGB\LOG_G14M2.DBF

Thu Nov 22 16:36:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:37:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:38:08 2007

ARC0: Media recovery disabled

Thu Nov 22 16:38:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:38:51 2007

Completed checkpoint up to RBA [0x4.2.10], SCN: 0x0000.0000b0ac

Thu Nov 22 16:38:59 2007

Beginning log switch checkpoint up to RBA [0x5.2.10], SCN: 0x0000.0000e12b

Thread 1 advanced to log sequence 5

Thu Nov 22 16:38:59 2007

Current log# 1 seq# 5 mem# 0: E:\ORACLE\TVD\ORIGLOGA\LOG_G11M1.DBF

Current log# 1 seq# 5 mem# 1: D:\ORACLE\TVD\MIRRLOGA\LOG_G11M2.DBF

Thu Nov 22 16:39:00 2007

ARC0: Media recovery disabled

Thu Nov 22 16:39:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:40:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:41:08 2007

ARC1: Media recovery disabled

Thu Nov 22 16:41:09 2007

Completed checkpoint up to RBA [0x5.2.10], SCN: 0x0000.0000e12b

Thu Nov 22 16:41:18 2007

Beginning log switch checkpoint up to RBA [0x6.2.10], SCN: 0x0000.0000ee94

Thread 1 advanced to log sequence 6

Current log# 2 seq# 6 mem# 0: E:\ORACLE\TVD\ORIGLOGB\LOG_G12M1.DBF

Current log# 2 seq# 6 mem# 1: D:\ORACLE\TVD\MIRRLOGB\LOG_G12M2.DBF

Thu Nov 22 16:41:18 2007

ARC1: Media recovery disabled

Thu Nov 22 16:42:08 2007

....

....

....

Fri Nov 23 07:38:10 2007

ARC1: Media recovery disabled

Regards,

Erick

[markus_doehr2]

> SQL*Plus: Release 9.2.0.7.0 - Production on Fri Nov

> 23 07:31:10 2007

>

> Copyright (c) 1982, 2002, Oracle Corporation. All

> rights reserved.

>

> Connected.

> CREATE TABLESPACE PSAP@SCHEMAID@ DATAFILE

> 'E:\oracle\TVD\sapdata4\psap@schemaid@_1\psap@schemaid

> @.data1' SIZE 2000M REUSE AUTOEXTEND ON NEXT 20M

> MAXSIZE 10000M LOGGING ONLINE PERMANENT EXTENT

> MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE

> MANAGEMENT AUTO

> *

> 02180: invalid option for CREATE TABLESPACE

Something went wrong with your installation, the SCHEMAID is not replaced by your actual SID.

Did you install a central instance before?

--

Markus

[Former Member]

Hi Markus,

Yes we did this:

1.- Oracle Software Installation.

2.- Patch 9.2.0.7

3.- Central Instance Installation: With SAPinst SAP R3E 4.7x200 SR1 -> ABAP System -> non-Unicode -> Install a Central Instance

4.- Now we are installing our Database Instance: With SAPinst SAP R3E 4.7x200 SR1 -> ABAP System -> non-Unicode -> Install a Database Instance

What could be our problem....

When the installation prompt ask us for the Schema id name we use this:

<b>SAPR3</b> instead of the typical <b>SAP<SID></b>

Why?

The reason is because we recently upgrade this enviroment from SAP R/3 4.6B Kernel Release 46D (That used SAPR3 as the schema name, and now the 4.7 UPGRADED system is still using SAPR/3 as the schema id). We lose the upgraded system (Disk Failure) and now we need to re-install:

1.- Fisrt Install SAP 4.7

2.- Do the Oracle backup/restore (That have the data associate to SAP 4.7 Ext 200 SR1 upgraded from 4.6B, it has <b>SAPR3</b> as the database schema id. I think this works like an Inheritance. You receive the SAPR3 Schema name as an <b>inheritance from the UPGRADE</b>)

Them we found this note:

Note <b>617444</b> - Separate SCHEMA ID for database schema and tablespace name

<b>Symptom</b>

Since Release 6.10, the SAP system name (SAPSID) has been part of the database schema and tablespace name following installation.

In the case of a system copy with an Oracle backup/restore, you cannot change database schemas or tablespace names. If the target system is to receive a different SAPSID than the source system, this results in an inconvenient situation where the target system contains the SAPSID of the source system in the database schema and tablespace name.

Even though this <b>is not technically a problem</b>, it can cause confusion when you manage this type of system.

So my question is this:

1.- Can i install the Database instance using SAP<SID> as the schema id <b>name</b>.

2.- Do the Oracle backup/restore.

3.- Works with the two schema ids <b>names</b>.+ <b>SAPR/3</b> and <b>SAP<SID></b>

In others words: we are doing a Oracle Backup/Restore but we need..? to use the same SAP Schema id <b>name</b> (SAPR3) to be congruent with the <b>name</b> that comes from the backup of the upgraded system.

Best Regards,

Erick Ilarraza

[markus_doehr2]

I wonder why you install a complete system and then restore your backup?

Check

http://service.sap.com/systemcopy

how to do a systemcopy, it's not necessary to do a full installation and then manually restore everything.

--

Markus

[Former Member]

Hi Markus,

We are doing a Backup/Restore no a system copy (The system that we loose is the DEV System).

I put the note <b>617444</b> because the System Copy Oracle Backup/Restore talk about the same topic. The oracle schema id <b>name</b> problem of <b>SAPR3</b> instead of SAP<SID>

For example, if some time we loose our productive system, we aply this backup/restore strategy:

1. Install SAP System + Oracle from Zero.

2. Legato restore of the Complete offline backup.

Our problem is that we don''t have a complete mirror SAP System Backup with OS state. So we firts need to install SAP and after make the restore.

We will try to Re-Install the 4.7 System using the schema id name <b>SAP<SID></b> and after that the legato Restore. As i say you, this is a offline backup of a SAP System with SAP R/3 4.7 that was upgraded from SAP 46B an have the Schema id SAPR/3 <b>no</b> SAP<SID>.

We are really scared, because if this Restore doesn't work, neither the PRD restore will work in case of a system failure since the SAP schema id names conflict.

I will inform you how this process finish.

Best Regards,

Erick Ilarraza

[markus_doehr2]

You can't rename you schema - but you can tell the instance to use a different schema, I really don't see any real problem

--

Markus