Showing posts with label raw device. Show all posts
Showing posts with label raw device. Show all posts

Thursday, April 03, 2014

can not allocate 100% raw partition to oracle data file

My db creation failed shown below due to specify exact 100% size of raw partition to Oracle data file.

Maybe some overhead needed, but what is the size needed ?

initially



-bash-3.2$ cat cre_db.sql
STARTUP NOMOUNT
CREATE DATABASE "RAWDB1"
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 600m  reuse
undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
LOGFILE
  GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
  GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
user sys identified by "oracle123" user  system identified by "oracle123" ;
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 3 16:42:51 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

16:42:51 SYS@RAWDB1> set echo on
16:42:54 SYS@RAWDB1> @cre_db
16:42:56 SYS@RAWDB1> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  901775360 bytes
Fixed Size                  2100424 bytes
Variable Size             226493240 bytes
Database Buffers          666894336 bytes
Redo Buffers                6287360 bytes
16:42:57 SYS@RAWDB1> CREATE DATABASE "RAWDB1"
16:42:57   2      MAXLOGFILES 16
16:42:57   3      MAXLOGMEMBERS 3
16:42:57   4      MAXDATAFILES 100
16:42:57   5      MAXINSTANCES 8
16:42:57   6      MAXLOGHISTORY 292
16:42:57   7  DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
16:42:57   8  sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 600m  reuse
16:42:57   9  undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
16:42:57  10  default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
16:42:57  11  LOGFILE
16:42:57  12    GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
16:42:57  13    GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
16:42:57  14  user sys identified by "oracle123" user  system identified by "oracle123" ;
CREATE DATABASE "RAWDB1"
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Thu Apr 03 16:38:00 SGT 2014
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 500m reuse

ORA-1119 signalled during: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 500m reuse
...
Thu Apr 03 16:38:00 SGT 2014
Errors in file /software/oracle/admin/RAWDB1/udump/rawdb1_ora_24331.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01119: error in creating database file '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 3
Thu Apr 03 16:38:00 SGT 2014
Errors in file /software/oracle/admin/RAWDB1/udump/rawdb1_ora_24331.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 5799
ORA-00604: error occurred at recursive SQL level 1
ORA-01119: error in creating database file '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 3
Error 1519 happened during db open, shutting down database
USER: terminating instance due to error 1519
Instance terminated by USER, pid = 24331
ORA-1092 signalled during: CREATE DATABASE "RAWDB1"
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 600m  reuse
undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 500m reuse
default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
LOGFILE
  GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
  GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
user sys identified by *user  system identified by *...





Revise data file size and re-create


-bash-3.2$ cat cre_db.sql
STARTUP NOMOUNT
CREATE DATABASE "RAWDB1"
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 560m  reuse
undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
LOGFILE
  GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
  GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
user sys identified by "oracle123" user  system identified by "oracle123" ;
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Apr 3 16:44:33 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

16:44:33 SYS@RAWDB1>
SP2-0042: unknown command "" - rest of line ignored.
16:44:35 SYS@RAWDB1>
16:44:35 SYS@RAWDB1> set echo on
16:44:38 SYS@RAWDB1> @cre_db
16:44:41 SYS@RAWDB1> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  901775360 bytes
Fixed Size                  2100424 bytes
Variable Size             226493240 bytes
Database Buffers          666894336 bytes
Redo Buffers                6287360 bytes
16:44:41 SYS@RAWDB1> CREATE DATABASE "RAWDB1"
16:44:41   2      MAXLOGFILES 16
16:44:41   3      MAXLOGMEMBERS 3
16:44:41   4      MAXDATAFILES 100
16:44:41   5      MAXINSTANCES 8
16:44:41   6      MAXLOGHISTORY 292
16:44:41   7  DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
16:44:41   8  sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 560m  reuse
16:44:41   9  undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
16:44:41  10  default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
16:44:41  11  LOGFILE
16:44:41  12    GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
16:44:41  13    GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
16:44:41  14  user sys identified by "oracle123" user  system identified by "oracle123" ;

Database created.

Elapsed: 00:00:13.63
 
Posted by at
Labels:

Now I know why SYSTEM tablespace comes with dictionary extent manament in a vendor application

It seems the database uses raw devices, and created manually (not using dbca), when the option is not specified explicitly, by default the SYSTEM tablespace is created  as DICTIONARY managed.

It seems quite a few article list disadvantages of DICTIONARY extent management, but don't where it come from.

Below is my testing.

default setting for SYSTEM datafile



-bash-3.2$ cat cre_db.sql
STARTUP NOMOUNT
CREATE DATABASE "RAWDB1"
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse
sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 560m  reuse
undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
LOGFILE
  GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
  GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
user sys identified by "oracle123" user  system identified by "oracle123" ;
 
and
run the $ORACLE_HOME/rdbms/admin/catalog.sql and $ORACLE_HOME/rdbms/admin/catproc.sql.




17:00:32 SYS@RAWDB1> select tablespace_name , extent_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         DICTIONARY
UNDOTBS1                       LOCAL
SYSAUX                         LOCAL
TEMP                           LOCAL

4 rows selected.
 

Create non-dictionary extent management for SYSTEM tablespace


-bash-3.2$ cat cre_db_non_dict_system.sql
STARTUP NOMOUNT
CREATE DATABASE "RAWDB1"
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
DATAFILE '/software/oracle/admin/RAWDB1/conf/RAWDB1_system_raw_1000m' size 900m reuse extent management local
sysaux datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_sysaux_raw_600m'size 560m  reuse
undo tablespace "UNDOTBS1" datafile '/software/oracle/admin/RAWDB1/conf/RAWDB1_undotbs1_raw_500m' size 480m reuse
default temporary tablespace temp tempfile '/software/oracle/admin/RAWDB1/conf/RAWDB1_temp_raw_250m' size 200m reuse
LOGFILE
  GROUP 1 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_1_raw_120m')  SIZE 50M reuse ,
  GROUP 2 ('/software/oracle/admin/RAWDB1/conf/RAWDB1_redo1_2_raw_120m')  SIZE 50M reuse
user sys identified by "oracle123" user  system identified by "oracle123" ;





17:24:40 SYS@RAWDB1> select tablespace_name,  extent_management from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
UNDOTBS1                       LOCAL
SYSAUX                         LOCAL
TEMP                           LOCAL
 
Posted by at
Labels: ,
Subscribe to: Posts (Atom)