Terkadang saat kita install oracle database kita sering lupa membiarkan nilai MAXDATAFILES ke nilai default yang mana nilainya adalah 1024 , klo databasenya sizenya kecil mungkin tidak ada masalah , tapi bagaimana kalau databasenya berukuran sangat besar (yang jumlah datafilenya melebihi 1024) ? tentu kita akan mendapatkan error ketika ingin menmbahkan datafile di database di karenakan batas maximum jumlah datafiles yang boleh di buat sudah tercapai .
Nah untuk mengganti nilai MAXDATAFILES ini kita harus membuat ulang controlfilenya , berikut ini adalah step-stepnya untuk mengganti nilai default dari MAXDATAFILES. disini nilainya saya ganti menjadi 20000.
Step-step create RAC controlfile secara urut (jangan lupa ganti db_files=20000)
--------------------------------------------------------------------------------------------
1. create pfile => create pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
2. Edit init99.ora => ganti cluster_database=true menjadi cluster_database=false
3. shutdown database RAC => srvctl stop database -d nama_database
4. backup redolog dan controlfile => copy ke direktory lain atau di rename
5. start db dari node 1 dengan menggunakan pfile => startup nomount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
6 execute controlfile_script => @controlfile_script
7 recover database => jalankan step2 (lihat bagian bawah )
8 add logfile untuk thread berikutnya => jalankan step 3 (lihat bagian bawah )
9 open db resetlogs dan enable thread => jalankan step 4 (lihat bagian bawah)
10 add temp tablespace => jalankan step 5 (lihat bagian bawah)
11 shutdown immediate database => shutdown immediate
12 edit init99.ora cluster_database=false menjadi cluster_database=true
13 startup mount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
14 create spfile => create spfile='/oradata1/ORCLdb/spfileORCLdb.ora' from pfile='$ORACLE_HOME/dbs/init99.ora' ;
15 shutdown database => shutdwon immediate
16 startup RAC DB => srvctl start database -d nama_database
controlfile_script
-----------------------
---STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 320
MAXLOGMEMBERS 5
MAXDATAFILES 20000
MAXINSTANCES 32
MAXLOGHISTORY 1019
LOGFILE
GROUP 1 (
'/oraredo1/ORCL/redo01a.log',
'/oraredo2/ORCLdb/redo01b.log',
'/oraredo3/ORCLdb/redo01c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 2 (
'/oraredo1/ORCLdb/redo02a.log',
'/oraredo2/ORCLdb/redo02b.log',
'/oraredo3/ORCLdb/redo02c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 3 (
'/oraredo1/ORCLdb/redo03a.log',
'/oraredo2/ORCLdb/redo03b.log',
'/oraredo3/ORCLdb/redo03c.log'
) SIZE 4096M BLOCKSIZE 512
DATAFILE
'/oradata1/ORCLdb/system01.dbf',
'/oradata1/ORCLdb/sysaux01.dbf',
'/oradata1/ORCLdb/undotbs01.dbf',
'/oradata1/ORCLdb/users01.dbf',
'/oradata2/ORCLdb/undotbs02.dbf',
'/oradata3/ORCLdb/undotbs03.dbf',
'/oradata2/ORCLdb/ods_log_01.dbf',
'/oradata2/ORCLdb/ods_apph_01.dbf',
'/oradata2/ORCLdb/ods_dim_01.dbf',
'/oradata1/ORCLdb/undotbs01_01.dbf',
'/oradata2/ORCLdb/undotbs02_01.dbf',
'/oradata3/ORCLdb/undotbs03_01.dbf',
'/oradata1/ORCLdb/undotbs01_02.dbf',
'/oradata2/ORCLdb/undotbs02_02.dbf',
'/oradata3/ORCLdb/undotbs03_02.dbf',
........................................
'/oradata6/ORCLdb/data_147.dbf',
'/oradata6/odsrepdb/data_148.dbf'
CHARACTER SET WE8MSWIN1252
;
Step ke 2
---------------
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
CANCEL
Step ke 3
-----------------
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/oraredo1/ORCLdb/redo04a.log',
'/oraredo2/ORCLdb/redo04b.log',
'/oraredo3/ORCLdb/redo04c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 5 (
'/oraredo1/ORCLdb/redo05a.log',
'/oraredo2/ORCLdb/redo05b.log',
'/oraredo3/ORCLdb/redo05c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 6 (
'/oraredo1/ORCLdb/redo06a.log',
'/oraredo2/ORCLdb/redo06b.log',
'/oraredo3/ORCLdb/redo06c.log'
) SIZE 4096M BLOCKSIZE 512 ;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 7 (
'/oraredo1/ORCLdb/redo07a.log',
'/oraredo2/ORCLdb/redo07b.log',
'/oraredo3/ORCLdb/redo07c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 8 (
'/oraredo1/ORCLdb/redo08a.log',
'/oraredo2/ORCLdb/redo08b.log',
'/oraredo3/ORCLdb/redo08c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 9 (
'/oraredo1/ORCLdb/redo09a.log',
'/oraredo2/ORCLdb/redo09b.log',
'/oraredo3/ORCLdb/redo09c.log'
) SIZE 4096M BLOCKSIZE 512 ;
Step Ke 4
-----------------------------
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE ENABLE PUBLIC THREAD 3;
Step Ke 5
-------------------
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp01.dbf'
SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp02.dbf'
SIZE 20480M REUSE AUTOEXTEND OFF;
Author : Ida Bagus Enderajana
Referensi : Dokumentasi Oracle
No comments:
Post a Comment