When you are working on large data movement in Oracle, you must concern about some performance overhead and online redo log switch is one of them. Recently i am working on a migration project where i need to migrate a 19.5GB MySQL 5.0 database to ORACLE 10gR2 database. I used Oracle SQL developer to do the job and the job takes about 5 hour to complete. To do the job faster i perform some tuning stuff on ORACLE and increasing the Online Redo Log files is the most fruitful one.
Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .
1. First see the size of the current logs:
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE
Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.
2. Retrieve all the log member names for the groups:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf
3. Now drop the log group 1 and recreate it with increased size
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;
4. Check the size of the current logs:
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log
SQL> alter system switch logfile;
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;
7. Check the size of the current logs:
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT
** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this
SQL> ALTER SYSTEM CHECKPOINT;
** FOR safety reason please take a full backup of your database before performing this.
2 comments:
Hi, I just wonder whether the size of the increased log files is given correctly with "10485760".
I think it has to be "104857600".
Thanks for the step by step instruction
Andreas
yes you are write, 10485760 should be 104857600.
Post a Comment