Add DB_Files Oracle 10g & ORA-03115

Ini cerita setelah mudik ke Jawa Timur halah,, baru masuk kantor sudah dapat PR, huehehe…tapi sebenarnya ini pr lama. PR-nya adalah menambahkan data file ke dalam database oracle 10g yang selama ini jadi database utama di bagian saya.

Bagi yang sudah familiar dengan istilah data file, pasti cukup paham mengapa data file begitu penting bagi Oracle. Karena data file adalah kunci pengendali bagi penambahan space di database itu sendiri.

Awalnya saya tidak mengalami masalah ketika menggunakan script di bawah ini untuk menambahkan data file (db_file)  di salah satu server kami.

scriptnya:
sql> connect sys as sysdba;

sql> show parameter db_files;=>untuk melihat jumlah db_files awal

its showing 200.

sql> alter system set db_files = 500 scope = spfile;=>diubah ke 500

SQL> shutdown immediate

SQL> startup

SQL> show parameter db_files

Namun, ketika dijalankan di server satunya, ternyata mengalami error alias gagal. Maka, setelah dicari solusinya ketemulah cara yang lumayan agak panjang.

SQL> show parameter db_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     500
SQL> show parameter pfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\
DBS\SPFILESYSDEV.ORA
SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
sysdev

SQL> show parameter MAXDATAFILES;
SQL> create pfile from spfile
2  ;

File created.

SQL> create pfile=’C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\inisysdev.ora’ from spfile;

File created.

SQL> alter system set db_files=2000 scope=spfile;

System altered.

SQL> create pfile=’C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\inisysdev.ora2′ from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size              83889028 bytes
Database Buffers          520093696 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> show parameter db_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     500
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=’C:\oracle\product\10.2.0\db_1\dbs\inisysdev.ora’;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size              88083332 bytes
Database Buffers          515899392 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> show parameter db_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     2000
SQL> create spfile from pfile=’C:\oracle\product\10.2.0\db_1\dbs\inisysdev.ora’;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size              88083332 bytes
Database Buffers          515899392 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> show parameter db_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     2000
SQL> show parameter pfile;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILESYSDEV.ORA
SQL>

*Datafile ditambah, maka kita bisa menambahkan space di database oracle kita.

Selamat mencoba…:)
Semoga membantu bagi temen-temen yang tengah berkecimpung di dunia database🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s