LaGiPusHinGWitH ---- IT

Navigation

Home |

Monthly Archives

Most recent entries

Syndicate

RSS 2.0
Atom

Site Credits

Design by:
BlogMoxie

Memindahkan controlfile dan redologfile pada Oracle

tutorial_moving.txt

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 ‘&#xUN;DO%’;

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;

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 ‘&#xUN;DO%’;

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#;

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....

Posted by roninmorgue on 10/03 at 06:52 PM
Computer DataBase • (0) Comments • (701) Trackbacks • Permalink

LaGiPusHinGWitH ---- IT

Navigation

Categories

Monthly Archives

Most recent entries

Syndicate

Site Credits