Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Online redo log files being removed physically

greenyDec 18 2013 — edited Dec 19 2013

Grid Infra version: 11.2.0.4

RDBMS Version: 11.2.0.4

Although this is a RAC DB, this is not a RAC-specific question. Hence posting it here.

Few months back, I remember issuing a command similair to below (DROP LOGFILE GROUP ...) and the redo log files were still physically present in the diskgroup.

If I remember correctly, the file is not deleted physical so that we can use the REUSE functionality (ALTER DATABASE ADD LOGFILE MEMBER '+REDO/orcl/onlinelog/redo1b.log' reuse to group 11; ) ie. you can use the REUSE command to add the logfile of the same name which is physically present in OS Filesystem/Diksgroup to redo log group.

But today, after I issued the below command, I checked the diskgroup location from ASMCMD

SQL> alter database drop logfile group 31;

Database altered.

From ASMCMD, I can that the file has disappeared physically. Is this a new feature with 11.2.0.4 or am I missing something here ?

ASMCMD> ls +DATA/msblprd/onlinelog/group_31.548.833154995

ASMCMD-8002: entry 'group_31.548.833154995' does not exist in directory '+DATA/msblprd/onlinelog/'

Comments

TSharma-0racle

If you are using Oracle managed files, then only your files will be deleted physically from the disk but if you are not using Oracle managed files, your file will stay on disk physically and controlfile will be updated. This was the default behavior in previous version. Only Oracle managed files will be deleted from the disk.

For reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo005.htm

Aman....

You are using a file which is stored over ASM and ASM uses OMF by default. That's the reason that file got deleted physically as well.

Aman....

MANU ALPHONSE

Just to add to what Aman has said.

It is a bad practice not to let OMF decide the placement of Online redo logs because of this issue especially when you use ASM.

Executing rm command in Linux/Unix is easy but Dropping ASM aliases in the disk group can be a hassle.

This is documented.

"When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, ensure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file."

http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo.htm#ADMIN11324

BTW . You don't even need to set  db_create_online_log_dest_n to enable OMF for ORLs.

SQL> show parameter log_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                  string      +MBL_DATA

alter database add logfile thread 4

group 31 ('+MBL_DATA','+MBL_FRA') size 4096M,

group 32 ('+MBL_DATA','+MBL_FRA') size 4096M,

group 33 ('+MBL_DATA','+MBL_FRA') size 4096M,

group 34 ('+MBL_DATA','+MBL_FRA') size 4096M ;

Database altered.

And redo logs will be neatly placed as shown below

   INST     GROUP# MEMBER                                             STATUS           ARC

---------- ---------- -------------------------------------------------- ---------------- ---

         4         31 +MBL_DATA/bsblprd/onlinelog/group_31.276.832605441 UNUSED           YES

                      +MBL_FRA/bsblprd/onlinelog/group_31.297.832605445  UNUSED           YES

                   32 +MBL_DATA/bsblprd/onlinelog/group_32.547.832605451 UNUSED           YES

                      +MBL_FRA/bsblprd/onlinelog/group_32.372.832605457  UNUSED           YES

                   33 +MBL_DATA/bsblprd/onlinelog/group_33.548.832605463 UNUSED           YES

                      +MBL_FRA/bsblprd/onlinelog/group_33.284.832605469  UNUSED           YES

                   34 +MBL_DATA/bsblprd/onlinelog/group_34.549.832605475 UNUSED           YES

                      +MBL_FRA/bsblprd/onlinelog/group_34.359.832605481  UNUSED           YES

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 16 2014
Added on Dec 18 2013
#general-database-discussions
3 comments
2,187 views