Membuat ulang/re-create controlfile di oracle

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.

Membuat RMAN dengan crontab

Untuk artikel kali ini, saya akan membuat script RMAN yang akan dijalankan secara terjadwal
dengan menggunakan crontab…

Langkah-langkah yang dilakukan adalah:

1. membuat file fullbackup_rman.sh

* * * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log
====================================
#vi fullbackup_rman.sh

ORACLE_SID=training; export ORACLE_SID
ORACLE_HOME=/ora10g/app/oracle/OraHome_1
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:/bin:/usr/bin:/usr/ucb:/etc:.;export PATH
rman target=/ @/app1/oracle/scripts/fullbackup_script.conf -> path tempat menaruh script RMAN

================================

2. buat file fullbackup_script.conf
contoh script RMAN yang akan dijalankan.

#vi /app1/oracle/scripts/fullbackup_script.conf

run
{
allocate channel ch1 type disk;
backup
incremental level 0
filesperset 1
format ‘/app1/oracle_backup/data_%T_d%d_p%p_U%U.bak’
(database);

backup
incremental level 0
format ‘/app1/oracle_backup/cf_%T_d%d_p%p_U%U.bak’
(current controlfile);

delete noprompt obsolete;
}
exit;

============================

Cara membuat Crontab file
___________
Crontab syntax :-
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.
* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

3. membuat Crontab untuk menjalankan RMAN
_______

contoh crontab ini akan menjalankan script /app1/oracle/scripts/fullbackup_rman.sh dan membuat log di /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log
setiap hari pada jam 6:30 PM.

30 18 * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log

=================================

$ chmod 775 fullbackup_rman.sh
$ crontab -e => 30 18 * * * /app1/oracle/scripts/fullbackup_rman.sh > /app1/oracle/backup/rman_fullbackup/fullbackup_rman.log
$ crontab -l