Sunday, May 13, 2007

Changing archive location intergrating of OMF on ASM & RMAN

While I checked the file location of the archives, I discovered unneeded aliases
 
ASMCMD [+] > cd +DATA/nplprd
ASMCMD [+DATA/nplprd] > ls -l
Type           Redund  Striped  Time             Sys  Name
                                                      1_12_621591104.dbf => +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233
                                                 Y    ARCHIVELOG/
                                                 Y    CHANGETRACKING/
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    TEMPFILE/
                                                 N    spfilenplprd.ora => +DATA/NPLPRD/PARAMETERFILE/spfile.272.621591209
 
Looking at the initialization parameter I figured the source of the problem
 
SQL> show parameter log_archive_dest_1
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+DATA/nplprd/
 
I changed the location to let oracle manage it by using Oracle Managed Files (OMF)
 
SQL> alter system set log_archive_dest_1 = 'LOCATION=+DATA' scope=both;
 
Now I had to catalog these file in their new location
 
RMAN> CATALOG START WITH '+data/nplprd/ARCHIVELOG/';
 
File Name: +data/nplprd/ARCHIVELOG/2007_05_10/thread_1_seq_12.295.622220233
 
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: +data/nplprd/ARCHIVELOG/2007_05_10/thread_1_seq_12.295.622220233
 
The file is registered twice in the catalog
 
RMAN> list archivelog all;
 

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
364     1    12      A 09-MAY-07 +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233
159     1    12      A 09-MAY-07 +DATA/nplprd/1_12_621591104.dbf
 
Removed the unwanted alias
 
ASMCMD [+DATA/nplprd] > rmalias +DATA/nplprd/1_12_621591104.dbf
 
Let RMAN catalog "know" about the new status

RMAN> crosscheck archivelog all;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 instance=nplprd1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 instance=nplprd2 devtype=DISK
validation succeeded for archived log
archive log filename=+DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233 recid=35 stamp=622465635
validation succeeded for archived log
Crosschecked 1 objects
 
validation failed for archived log
archive log filename=+DATA/nplprd/1_12_621591104.dbf recid=20 stamp=622220233
validation failed for archived log
Crosschecked 1 objects
 
Now the alias is marked as expired.

RMAN> list archivelog all;
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
364     1    12      A 09-MAY-07 +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233
159     1    12      X 09-MAY-07 +DATA/nplprd/1_12_621591104.dbf
 
The final step is just to delete this record from the RMAN
 
RMAN>  delete expired archivelog all;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 instance=nplprd1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 instance=nplprd2 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
159     1    12      X 09-MAY-07 +DATA/nplprd/1_12_621591104.dbf
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=+DATA/nplprd/1_12_621591104.dbf recid=20 stamp=622220233
deleted archive log
 
Deleted 1 EXPIRED objects
 
Looking at the results
 
RMAN>  list archivelog all;
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
364     1    12      A 09-MAY-07 +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233

No comments: