LaGiPusHinGWitH ---- IT

Navigation

Home |

Categories

• Computer Hacking
• Computer Diary
• Computer DataBase
• Computer Propaganda
• Computer Prophile
• Computer Security
• Computer Unix

Monthly Archives

• September 2010
• October 2009
• December 2008
• November 2008
• October 2008
• September 2008
• July 2008
• May 2008
• March 2008
• December 2007
• October 2007
• September 2007
• August 2007

Most recent entries

• Create Oracle Tablespace on SAP with BRTools
• Audit on Oracle Database
• Membuat Oracle Data Guard
• Cerita Tentang Kamu
• Membuat ulang/re-create controlfile di oracle
• Membuat RMAN dengan crontab
• Saat kau hadir kembali
• Memindahkan controlfile dan redologfile pada Oracle
• Oracle Dataguard on Standart Edition
• Adakah aku dihatimu
• Instalasi 10g di HP-UX Itanium
• Instalasi OpenOffice di Slackware 12
• Syncronize archive log
• Merubah ukuran archived Log
• Alter Block Size for tablespace

Syndicate

• RSS 2.0
• Atom

Site Credits

Powered by:
ExpressionEngine

Design by:
BlogMoxie

recreate_controlfile.txt

Pada artikel ini, akan dijelaskan bagaimana cara membuat ulang controlfile pada oracle.
Kita perlu membuat ulang controlfile pada saat akan melakukan perubahan-perubahan paramater pada
controlfile, disini saya akan melakukan perubahan pada nilai:

MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 300
MAXINSTANCES 8
MAXLOGHISTORY 292

menjadi

MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 1024
MAXINSTANCES 15
MAXLOGHISTORY 500

Langkah-langkah yang dilakukan adalah :

1. pastikan nama database yang akan kita buat ulang controlfilenya

SQL> select name from v$database;

NAME
---------
TRAINING

2. lihat path dimana controlfile berada.

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------
/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL01.CTL
/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL02.CTL
/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/CONTROL03.CTL

3. backup controlfile kedalam bentuk file text untuk membuat ulang controlfilenya

SQL> alter database backup controlfile to trace as ‘/coba2oracle/controlfile.txt’;

Database altered.

4. edit file controlfile.txt sesuai dengan kebutuhan

$ vi /coba2oracle/controlfile.txt

=============== potong ====================

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TRAINING” NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 300
MAXINSTANCES 8
MAXLOGHISTORY 292

============== potong =====================

kemudian edit file tersebut menjadi seperti dibawah ini:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TRAINING” NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 10
MAXDATAFILES 1024
MAXINSTANCES 15
MAXLOGHISTORY 500
LOGFILE
GROUP 1 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO01.LOG’ SIZE 50M,
GROUP 2 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO02.LOG’ SIZE 50M,
GROUP 3 ‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/REDO03.LOG’ SIZE 50M
DATAFILE
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/SYSTEM01.DBF’,
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/UNDOTBS01.DBF’,
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/SYSAUX01.DBF’,
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/USERS01.DBF’,
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/USERS02.DBF’,
‘/ORACLEDATA/HAPUS.DBF’,
‘/ORACLEDATA/HAPUS02.DBF’,
‘/ORACLE/PRODUCT/10.2.0/ORADATA/TRAINING/LAT01.DBF’
CHARACTER SET WE8MSWIN1252;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\TRAINING\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND OFF;

NOTE : pada file controlfile.txt terdapat 2 metode dalam pembuatan controlfile, case 1 (noresetlogs)
dan case 2 (resetlogs), untuk kasus ini hapus step2 pada case 2(resetlogs)..

Setelah itu simpan hasil perubahan dan rubah controlfile.txt menjadi controlfile.sql

$ mv controlfile.txt controlfile.sql

5. matikan database

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

6. jalankan script pada file controlfile.sql

SQL> @ /coba2oracle/controlfile.sql;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 583008656 bytes
Database Buffers 461373440 bytes
Redo Buffers 2940928 bytes

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

System altered.

Database altered.

Tablespace altered.

SQL>

NOTE : Pesan error tersebut tidak masalah, karena menunjukkan bahwa database tidak
perlu di recover.