Dear all.. Pada suatu hari.. halah, ko pembukaannya ga enak yah... langsung aja deh, pada suatu ketika... hmm kayanya sama noraknya deh... ya udah.. suatu saat ada kondisi dimana database klien gw perlu dilakukan proses pemindahan dan perubahan pada file-file kritis pada database oracle-nya.. ingat ini file-file kritis yah... file-file tersebut adalah : - controlfile - redolog file serta menghapus dan membuat kembali tablespace UNDO dan merubah parameter undo retentionnya... dan ini lah rencana aksi kita.. kerennya sih Action Planning : A. Moving Undo Tablespace : 1 Backup file-file yang akan dipindah : control file, redo log file 2 Create new Undo Tablespace 3 Mengganti parameter Undo Tablespace ke tablespace baru 4 Delete tablespace lama beserta content dan datafile-nya B. Rubah parameter Undo Retention 1 Mengganti nilai Undo Retention pada system C. Moving online Redo Log File 1 Drop redo log file dengan status INACTIVE 2 Create redo 3 Switch logfile D. Moving Control File 1 Create pfile baru dari spfile 2 Edit pfile baru (mengganti parameter Control File) 3 Shutdown Instance 4 Moving Control file ke path yang baru 5 Create spfile dari pfile yang sudah di edit 6 Startup Mount 7 Open database Dan inilah aksinya.. hehehe SQL> !df -k Filesystem kbytes used avail capacity Mounted on /dev/dsk/c1t0d0s0 4129290 954465 3133533 24% / /proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab swap 3486720 16 3486704 1% /var/run swap 3487328 624 3486704 1% /tmp /dev/dsk/c1t0d0s3 4129290 2694240 1393758 66% /oracle /dev/dsk/c1t1d0s0 8257241 7639452 535217 94% /oradata /dev/dsk/c1t1d0s1 8257241 4144738 4029931 51% /oradata2 SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name like '%UNDO%'; FILE_NAME TABLESPACE_NAME BYTES -------------------------------------------- ------------------------------ --------- /oradata/PRODUCTION/PRODUCTION/undotbs01.dbf UNDOTBS1 209715200 SQL> col "Tablespace" for a20 col "Allocated" for 999999999999 select ddf.TABLESPACE_NAME "Tablespace", SQL> SQL> SQL> 2 ddf.BYTES "Allocated", 3 round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) "Used %", 4 round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) "Free %" 5 from (select TABLESPACE_NAME, 6 sum(BYTES) bytes 7 from dba_data_files 8 group by TABLESPACE_NAME) ddf, 9 (select TABLESPACE_NAME, 10 sum(BYTES) bytes 11 from dba_free_space 12 group by TABLESPACE_NAME) dfs 13 where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME 14 order by ddf.tablespace_name; Tablespace Allocated Used % Free % -------------------- ------------- ---------- ---------- DRSYS 39059456 98.99 1.01 EXAMPLE 152698880 99.74 .26 IDATA 209715200 .81 99.19 IINDEX 62914560 .1 99.9 INDX 26214400 .25 99.75 ODM 20971520 45.94 54.06 TBS_DDAA 4194304000 48.18 51.83 TOOLS 10485760 90.63 9.38 UNDOTBS1 209715200 1.19 98.81 USERS 26214400 .25 99.75 XDB 39976960 99.34 .66 11 rows selected. $ pwd /oradata2 $ mkdir PRODUCTION $ cd PRODUCTION $ pwd /oradata2/PRODUCTION A. Moving Undo Tablespace : SQL> create undo tablespace UNDOTBS2 datafile '/oradata2/PRODUCTION/undotbs02.dbf' size 1000M reuse autoextend ON; Tablespace created. SQL> alter system set undo_tablespace=UNDOTBS2; System altered. SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS2 SQL> drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. B. Rubah parameter Undo Retention SQL> alter system set undo_retention=1800; System altered. SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 1800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS2 SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name like '%UNDO%'; FILE_NAME TABLESPACE_NAME BYTES ------------------------------------------- ------------------------------ ---------- /oradata2/PRODUCTION/undotbs02.dbf UNDOTBS2 1048576000 Tablespace Allocated Used % Free % -------------------- ------------- ---------- ---------- DRSYS 39059456 98.99 1.01 EXAMPLE 152698880 99.74 .26 IDATA 209715200 .81 99.19 IINDEX 62914560 .1 99.9 INDX 26214400 .25 99.75 ODM 20971520 45.94 54.06 TBS_DDAA 4194304000 48.18 51.83 TOOLS 10485760 90.63 9.38 UNDOTBS2 1048576000 .13 99.87 USERS 26214400 .25 99.75 XDB 39976960 99.34 .66 11 rows selected. C. Moving online Redo Log File SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 CURRENT /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo03.log 886 SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 ('/oradata2/PRODUCTION/redo03.log') size 100M Database altered. SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 CURRENT /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 UNUSED /oradata2/PRODUCTION/redo03.log 0 SQL> alter system switch logfile; System altered. SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b whe e a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 ACTIVE /oradata/PRODUCTION/PRODUCTION/redo01.log 887 2 INACTIVE /oradata/PRODUCTION/PRODUCTION/redo02.log 885 3 CURRENT /oradata2/PRODUCTION/redo03.log 888 SQL> select a.group#,a.status,b.member,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# STATUS MEMBER SEQUENCE# ------ ---------- -------------------------------------------------- ---------- 1 ACTIVE /oradata/PRODUCTION/PRODUCTION/redo01.log 899 2 INACTIVE /oradata2/PRODUCTION/redo02.log 898 3 CURRENT /oradata2/PRODUCTION/redo03.log 900 D. Moving Control File SQL> !vi /oradata2/PRODUCTION/pfilenew.ora *.control_files='/oradata2/PRODUCTION/control01.ctl','/oradata2/PRODUCTION/control02.ctl','/oradata/PRODUCTION/PRODUCTION/control03.ctl' SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. $ mv /oradata/PRODUCTION/PRODUCTION/control01.ctl /oradata2/PRODUCTION/control01.ctl $ mv /oradata/PRODUCTION/PRODUCTION/control02.ctl /oradata2/PRODUCTION/control02.ctl SQL> create spfile from pfile='/oradata2/PRODUCTION/pfilenew.ora'; File created. SQL> startup mount; ORACLE instance started. Total System Global Area 320308312 bytes Fixed Size 730200 bytes Variable Size 285212672 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. SQL> alter database open; Database altered. SQL> select status from v$instance; STATUS ---------- OPEN $ df -k Filesystem kbytes used avail capacity Mounted on /dev/dsk/c1t0d0s0 4129290 954473 3133525 24% / /proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab swap 3500736 16 3500720 1% /var/run swap 3501344 624 3500720 1% /tmp /dev/dsk/c1t0d0s3 4129290 2899208 1188790 71% /oracle /dev/dsk/c1t1d0s0 8257241 7225908 948761 89% /oradata /dev/dsk/c1t1d0s1 8257241 5377883 2796786 66% /oradata2 Setelah proses ini selesai.. sangat disarankan untuk melakukan full backup database....