No Changes,No Alterations..Explain Plan Suddenly Changed !!!!

This is a very common question which i have always heard from the CLIENTS :-

“There has been no changes,no alterations , no new indexes , no parameter changes then why the HELL is this query running slow today??Did you check the explain plan.It was running good last month…WHY NOT TODAY??”

READ THIS –

http://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/

ENJOY

ūüôā

Advertisements

Drop Database using RMAN

Its very truly said “Learning is a Continuous Process“.Never the late,today i learned something new¬† “Dropping Database using RMAN”.What was the reason for my learning!!!!!????

I had dropped the database using “drop database” command in SQL*Plus and later i had to manually delete all the archivelogs and backups of that particular database.It consumed my time.So, a friend of mine suggested using RMAN to drop the database,though little late.Immediately i started searching for it on google (don’t know what will happen if its down for even 1min)¬† and got few good links.

The Drop Database command, makes a fast and efficient cleanup of all datafiles, redologs and control files in one command.It requires to mount the database in exclusive restricted mode.So even if you execute it on WRONG database(DO NOT TRY!!!!!), no problems…you can stay alive ūüôā

But as said , this command cleaned up only the datafiles,redologs and control files.The archivelogs and the database backup (taken through RMAN) still remain and needs to be deleted manually.

So,lets try and see “DROPPING DATABASE USING RMAN”

There are 4 syntax available to drop the database using RMAN :-

1. DROP DATABASE;

2. DROP DATABASE NOPROMPT;

3. DROP DATABASE INCLUDING BACKUPS;

4. DROP DATABASE INCLUDING BACKUPS NOPROMPT;

When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

The “DROP DATABASE” command deletes the

  • Datafiles
  • Logfiles
  • Controlfiles
  • Spfile

The ” DROP DATABASE INCLUDING BACKUPS ” command in addition to the above files also removes

  • Archivelogs
  • Backup pieces generated by RMAN

Here is a demo on a test database:-

Session 1 (DB INFO):-

23:11:48 SYS @ oracle >@db_info

 DBID NAME      LOG_MODE     VERSION_T OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    FOR                           
---------- --------- ------------ --------- ---------- ---------------- -------------------- ---                           
1574601275 ORACLE    ARCHIVELOG   29-OCT-09 READ WRITE PRIMARY          SESSIONS ACTIVE      YES                           

Elapsed: 00:00:00.15

INSTANCE_NAME    INSTANCE_NUMBER HOST_NAME                        VERSION           STARTUP_T STATUS       DATABASE_STATUS
---------------- --------------- ---------------------------------------------------------------- ----------------- ---------                          
oracle                         1 ANAND                            10.2.0.4.0        18-FEB-10 OPEN         ACTIVE         

Elapsed: 00:00:00.01

NAME                      VALUE                            
-------------------- ----------                            
Fixed Size              1295896                            
Variable Size         142608872                            
Database Buffers       58720256                            
Redo Buffers            7090176                            

Elapsed: 00:00:00.03

Controlfile                  Status                        
------------------------------------------------------------ -------                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL01.CTL                UNKNOWN                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL02.CTL                UNKNOWN                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL03.CTL                UNKNOWN                       

Elapsed: 00:00:00.01

Datafile                     Status  Enabled    Size (M)   
------------------------------------------------------------ ------- ---------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORACLE\ANDY_01.DBF                  ONLINE  READ WRITE       10.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\EXAMPLE01.DBF                ONLINE  READ WRITE      102.4
D:\ORACLE\PRODUCT\10.2.0\ORACLE\LOGMNRTBS_01.DBF             ONLINE  READ WRITE       51.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\STREAMTBS_01.DBF             ONLINE  READ WRITE      102.4
D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSAUX01.DBF                 ONLINE  READ WRITE      307.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSTEM01.DBF                 SYSTEM  READ WRITE      512.0
D:\ORACLE\PRODUCT\10.2.0\ORACLE\UNDOTBS01.DBF                ONLINE  READ WRITE       56.3
D:\ORACLE\PRODUCT\10.2.0\ORACLE\USERS01.DBF                  ONLINE  READ WRITE      142.0 

8 rows selected.

Elapsed: 00:00:00.18

 Group Logfile                      Status             
---------- ------------------------------------------------------------ -------            
 1 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO01.LOG                   UNKNOWN            
 2 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO02.LOG                   UNKNOWN             
 3 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO03.LOG                   UNKNOWN            

Elapsed: 00:00:00.03
23:17:07 SYS @ oracle >select PIECE#,TAG,STATUS,START_TIME,COMPLETION_TIME,ELAPSED_SECONDS,BYTES,HANDLE from v$backup_piece where DELETED='NO';

 PIECE# TAG                              S START_TIM COMPLETIO ELAPSED_SECONDS      BYTES HANDLE                        
---------- -------------------------------- - --------- --------- --------------- ---------- --------------------          
 1 TAG20100217T232722               A 17-FEB-10 17-FEB-10             104  715161600 D:\RMAN\ORACLE_1              
 1 TAG20100217T234213               A 17-FEB-10 17-FEB-10              58  492507648 D:\RMAN\ARCH_ORACLE_1
23:22:30 SYS @ oracle >shu immediate;
23:27:42 SYS @ oracle >startup mount;

Session 2 (RMAN SESSION) :-

C:\Documents and Settings\user>rman target sys/xxxxxxx

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 18 23:28:18 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORACLE (DBID=1574601275, not open)

RMAN> drop database including backups;

database name is "ORACLE" and DBID is 1574601275
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 02/18/2010 23:28:24
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

As said earlier, Database has be in EXCLUSIVE RESTRICTED MODE.

Lets put the database in exclusive restricted mode from RMAN session only,

RMAN> sql 'alter system enable restricted session';

using target database control file instead of recovery catalog
sql statement: alter system enable restricted session

During this time the alert log updates with the below information :-

Thu Feb 18 23:28:43 2010
Stopping background process MMNL
Thu Feb 18 23:28:44 2010
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
MMON started with pid=11, OS id=1740
Thu Feb 18 23:28:45 2010
ALTER SYSTEM enable restricted session;
MMNL started with pid=12, OS id=2204

Now,lets Drop the Database

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "ORACLE" and DBID is 1574601275

Do you really want to drop all backups and the database (enter YES or NO)? yes (BY DEFAULT ITS PROMPT)

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        D:\RMAN\ORACLE_1
19      19      1   1   AVAILABLE   DISK        D:\RMAN\ARCH_ORACLE_1
deleted backup piece
backup piece handle=D:\RMAN\ORACLE_1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=D:\RMAN\ARCH_ORACLE_1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1
205     1    135     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1
206     1    136     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1
207     1    137     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1
208     1    138     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1
209     1    139     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1
210     1    140     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1
211     1    141     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1
212     1    142     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1
213     1    143     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1
214     1    144     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1
215     1    145     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1
216     1    146     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1
217     1    147     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1
218     1    148     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1
219     1    149     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1
220     1    150     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1
221     1    151     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1
222     1    152     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1
223     1    153     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1
224     1    154     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1
225     1    155     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1
226     1    156     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1
227     1    157     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1
228     1    158     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1
229     1    159     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1
230     1    160     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1
231     1    161     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1
232     1    162     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1
233     1    163     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1
235     1    164     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1
234     1    165     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1
236     1    166     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1
237     1    167     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1
238     1    168     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1
239     1    169     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1
240     1    170     A 27-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1
241     1    171     A 28-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1
242     1    172     A 30-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1
243     1    173     A 31-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1
244     1    174     A 01-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1
245     1    175     A 03-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1
246     1    176     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1
247     1    177     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1 recid=203 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1 recid=204 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1 recid=205 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1 recid=206 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1 recid=207 stamp=708477174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1 recid=208 stamp=708477177
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1 recid=209 stamp=708477957
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1 recid=210 stamp=708478193
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1 recid=211 stamp=708523128
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1 recid=212 stamp=708523129
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1 recid=213 stamp=708523194
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1 recid=214 stamp=708527072
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1 recid=215 stamp=708527201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1 recid=216 stamp=708527278
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1 recid=217 stamp=708527459
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1 recid=218 stamp=708527641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1 recid=219 stamp=708527711
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1 recid=220 stamp=708531955
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1 recid=221 stamp=708532250
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1 recid=222 stamp=708532331
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1 recid=223 stamp=708537657
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1 recid=224 stamp=708537715
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1 recid=225 stamp=708537936
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1 recid=226 stamp=708538012
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1 recid=227 stamp=708538174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1 recid=228 stamp=708538195
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1 recid=229 stamp=708538201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1 recid=230 stamp=708539593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1 recid=231 stamp=708555515
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1 recid=232 stamp=708555516
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1 recid=233 stamp=708555536
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1 recid=235 stamp=708648593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1 recid=234 stamp=708648592
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1 recid=236 stamp=708648853
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1 recid=237 stamp=709329114
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1 recid=238 stamp=709330932
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1 recid=239 stamp=709423232
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1 recid=240 stamp=709515047
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1 recid=241 stamp=709678641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1 recid=242 stamp=709728779
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1 recid=243 stamp=709857486
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1 recid=244 stamp=710029358
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1 recid=245 stamp=710118379
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1 recid=246 stamp=710118402
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1 recid=247 stamp=710118425
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

database name is "ORACLE" and DBID is 1574601275
database dropped

RMAN>

Alert Log has the following information :-

Thu Feb 18 23:39:22 2010
DROP DATABASE
Thu Feb 18 23:39:22 2010
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSTEM01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\UNDOTBS01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSAUX01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\USERS01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\EXAMPLE01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\STREAMTBS_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\ANDY_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\LOGMNRTBS_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO01.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO02.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO03.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\TEMP01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE_BLK_CHG.LOG
Thu Feb 18 23:39:39 2010
Instance terminated by USER, pid = 3456
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL01.CTL
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL02.CTL
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL03.CTL
Completed: DROP DATABASE
Thu Feb 18 23:39:45 2010
Shutting down instance (abort)
License high water mark = 4

So,as it can been seen from the alert log and the rman session window that all the ” Datafiles,Redo Logfiles ,Controlfiles ,Archivelogs and the Backup pieces”¬† are deleted.

Its cool, isn’t it ūüôā

NOTE:-  Starting with Oracle 10gR1 we can use RMAN to drop a database.

Rman – Archivelog Backup

I was asked by my manager to take the archivelog backup of a 10.2.0.4 database using RMAN every two hours for a production database on sun solaris.Definitely, i am not going to sit at every 2nd hour and take the backup manually.So,best was to write a shell script and put it in cron job.Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon.Now the question was “What will i write in the shell script”.I can set the crontab for every 2 hrs and i can use something like “from time ‘sysdate-1/12’ “.

RMAN> backup as compressed backupset archivelog from time 'sysdate-1/12' format '/oracle_db_storage/testb/arch_070210_%s_%p';

This will check for all the archivelogs generated from 2 hrs back to the current time and take the backup.If suppose i run the command at 11am then it will take the backup of all the archivelogs generated between 9am – 11am.So i can write the script and put it in cron job which runs every 2 hrs and my job is done.Good enough, isn’t it?????

BUT!!!!! what if the cron didn’t run at the time it was supposed to?????? Suppose the cron job didn’t run at 11am, and it ran at the next run cycle that is 1pm, will everything be the same then??? No, cause at 1pm it will backup only those archives which are generated after 11am (current time – 2hrs).I won’t have backup of the archives generated between 9am to 11am.Thats BAD !!!!!!

So, definitely i need to change the script.After few searches with oracle documents i got exactly what i needed

“not backed up integer times ”¬† :- Backs up only those archived logs that have not been backed up at least integer times.

From Oracle Document :-

notBackedUpSpec

Syntax Element Description
NOT BACKED UP Backs up only those files (of the files specified on the command) that RMAN has never backed up. This option is a convenient way to back up new files after adding them to the database.
SINCE TIME =date_string Specifies the date after which RMAN should back up files that have no backups. The date_string is either a date in the current NLS_DATE_FORMAT, or a SQL date expression such as 'SYSDATE-1'. When calculating the number of backups for a file, RMAN only considers backups created on the same device type as the current backup.This option is a convenient way to back up files that were not backed up during a previous failed backup. For example, you back up the database, but the instance fails halfway through. You can restart the backup with the NOT BACKED UP SINCE TIME clause and avoid backing up those files that you already backed up. If AS BACKUPSET is set, then this feature is only useful if RMAN generates multiple backup sets during the backup.When determining whether a file has been backed up, the SINCE date is compared with the completion time of the most recent backup. For BACKUP AS BACKUPSET, the completion time for a file in a backup set is the completion time of the entire backup set. In other words, all files in the same backup set have the same completion time.
integer TIMES Backs up only those archived logs that have not been backed up at least integer times. To determine the number of backups for a file, RMAN only considers backups created on the same device type as the current backup.

This made my day ūüôā . I tested it on my test db and it worked as needed ūüôā

The test was done on 2nd Feb 2010

[oracle@test-testa ~]$  rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 2 10:20:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: TESTA (DBID=114007228)

RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
944     1.22G      DISK        00:04:22     02-FEB-10
 BP Key: 944   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T021557
 Piece Name: /oracle_db_storage/rman/arch/ARCH_21l4vguu_1_1

 List of Archived Logs in backup set 944
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4209    1014571999 01-FEB-10 1015662867 02-FEB-10
 1    4210    1015662867 02-FEB-10 1016718444 02-FEB-10
 1    4211    1016718444 02-FEB-10 1017788958 02-FEB-10
 1    4212    1017788958 02-FEB-10 1018859257 02-FEB-10
 1    4213    1018859257 02-FEB-10 1019928942 02-FEB-10
 1    4214    1019928942 02-FEB-10 1021002663 02-FEB-10
 1    4215    1021002663 02-FEB-10 1022083432 02-FEB-10
 1    4216    1022083432 02-FEB-10 1022864419 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
945     1.28G      DISK        00:04:31     02-FEB-10
 BP Key: 945   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T021557
 Piece Name: /oracle_db_storage/rman/arch/ARCH_20l4vguu_1_1

 List of Archived Logs in backup set 945
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4201    1011043347 01-FEB-10 1011335909 01-FEB-10
 1    4202    1011335909 01-FEB-10 1011649208 01-FEB-10
 1    4203    1011649208 01-FEB-10 1011963726 01-FEB-10
 1    4204    1011963726 01-FEB-10 1012256376 01-FEB-10
 1    4205    1012256376 01-FEB-10 1012576084 01-FEB-10
 1    4206    1012576084 01-FEB-10 1012877056 01-FEB-10
 1    4207    1012877056 01-FEB-10 1013474586 01-FEB-10
 1    4208    1013474586 01-FEB-10 1014571999 01-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
946     30.82M     DISK        00:00:07     02-FEB-10
 BP Key: 946   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T032714
 Piece Name: /oracle_db_storage/testb/backup/flash_recovery_area/TESTB/backupset/2010_02_02/o1_mf_annnn_TAG20100202T032714_5pj30msg_.bkp

 List of Archived Logs in backup set 946
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4221    1022871009 02-FEB-10 1022925671 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
947     5.77M      DISK        00:00:03     02-FEB-10
 BP Key: 947   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1092_1_24l507qs

 List of Archived Logs in backup set 947
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4223    1023209017 02-FEB-10 1023215386 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
948     3.66M      DISK        00:00:02     02-FEB-10
 BP Key: 948   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1093_1_25l507r1

 List of Archived Logs in backup set 948
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4217    1022864419 02-FEB-10 1022870971 02-FEB-10
 1    4218    1022870971 02-FEB-10 1022870984 02-FEB-10
 1    4219    1022870984 02-FEB-10 1022870999 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
949     157.76M    DISK        00:00:28     02-FEB-10
 BP Key: 949   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1091_1_23l507qs

 List of Archived Logs in backup set 949
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4220    1022870999 02-FEB-10 1022871009 02-FEB-10
 1    4222    1022925671 02-FEB-10 1023209017 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
950     2.22M      DISK        00:00:01     02-FEB-10
 BP Key: 950   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084810
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1094_1_26l507ua

 List of Archived Logs in backup set 950
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4224    1023215386 02-FEB-10 1023217196 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
951     2.24M      DISK        00:00:01     02-FEB-10
 BP Key: 951   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T085623
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1096_1_28l508dn

 List of Archived Logs in backup set 951
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4225    1023217196 02-FEB-10 1023219527 02-FEB-10
 1    4226    1023219527 02-FEB-10 1023219584 02-FEB-10
 1    4227    1023219584 02-FEB-10 1023219598 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
952     8.10M      DISK        00:00:02     02-FEB-10
 BP Key: 952   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T085623
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1095_1_27l508dn

 List of Archived Logs in backup set 952
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4228    1023219598 02-FEB-10 1023219613 02-FEB-10
 1    4229    1023219613 02-FEB-10 1023228173 02-FEB-10

RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
2761    1    4201    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4201_661394635.dbf
2762    1    4202    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4202_661394635.dbf
2763    1    4203    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4203_661394635.dbf
2764    1    4204    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4204_661394635.dbf
2765    1    4205    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4205_661394635.dbf
2766    1    4206    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4206_661394635.dbf
2767    1    4207    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4207_661394635.dbf
2768    1    4208    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4208_661394635.dbf
2769    1    4209    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4209_661394635.dbf
2770    1    4210    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4210_661394635.dbf
2771    1    4211    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4211_661394635.dbf
2772    1    4212    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4212_661394635.dbf
2773    1    4213    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4213_661394635.dbf
2774    1    4214    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4214_661394635.dbf
2775    1    4215    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4215_661394635.dbf
2776    1    4216    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4216_661394635.dbf
2777    1    4217    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4217_661394635.dbf
2778    1    4218    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4218_661394635.dbf
2779    1    4219    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4219_661394635.dbf
2780    1    4220    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4220_661394635.dbf
2781    1    4221    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4221_661394635.dbf
2782    1    4222    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4222_661394635.dbf
2783    1    4223    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4223_661394635.dbf
2784    1    4224    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4224_661394635.dbf
2785    1    4225    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4225_661394635.dbf
2786    1    4226    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4226_661394635.dbf
2787    1    4227    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4227_661394635.dbf
2788    1    4228    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4228_661394635.dbf
2789    1    4229    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4229_661394635.dbf

RMAN>  sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
2761    1    4201    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4201_661394635.dbf
2762    1    4202    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4202_661394635.dbf
2763    1    4203    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4203_661394635.dbf
2764    1    4204    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4204_661394635.dbf
2765    1    4205    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4205_661394635.dbf
2766    1    4206    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4206_661394635.dbf
2767    1    4207    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4207_661394635.dbf
2768    1    4208    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4208_661394635.dbf
2769    1    4209    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4209_661394635.dbf
2770    1    4210    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4210_661394635.dbf
2771    1    4211    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4211_661394635.dbf
2772    1    4212    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4212_661394635.dbf
2773    1    4213    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4213_661394635.dbf
2774    1    4214    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4214_661394635.dbf
2775    1    4215    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4215_661394635.dbf
2776    1    4216    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4216_661394635.dbf
2777    1    4217    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4217_661394635.dbf
2778    1    4218    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4218_661394635.dbf
2779    1    4219    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4219_661394635.dbf
2780    1    4220    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4220_661394635.dbf
2781    1    4221    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4221_661394635.dbf
2782    1    4222    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4222_661394635.dbf
2783    1    4223    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4223_661394635.dbf
2784    1    4224    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4224_661394635.dbf
2785    1    4225    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4225_661394635.dbf
2786    1    4226    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4226_661394635.dbf
2787    1    4227    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4227_661394635.dbf
2788    1    4228    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4228_661394635.dbf
2789    1    4229    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4229_661394635.dbf
2790    1    4230    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4230_661394635.dbf
2791    1    4231    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4231_661394635.dbf

RMAN> backup as compressed backupset archivelog all not backed up 1 times format '/oracle_db_storage/rman/arch/ARCH_%s_%p_%u';

Starting backup at 02-FEB-10
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=933 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=935 devtype=DISK
skipping archive log file /oracle_db_storage/testb/arch/1_4201_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4202_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4203_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4204_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4205_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4206_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4207_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4208_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4209_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4210_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4211_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4212_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4213_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4214_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4215_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4216_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4217_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4218_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4219_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4220_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4221_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4222_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4223_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4224_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4225_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4226_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4227_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4228_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4229_661394635.dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4230 recid=2790 stamp=709899719
channel ORA_DISK_1: starting piece 1 at 02-FEB-10
channel ORA_DISK_2: starting compressed archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=4231 recid=2791 stamp=709899719
input archive log thread=1 sequence=4232 recid=2792 stamp=709899830
channel ORA_DISK_2: starting piece 1 at 02-FEB-10
channel ORA_DISK_2: finished piece 1 at 02-FEB-10
piece handle=/oracle_db_storage/rman/arch/ARCH_1098_1_2al50dhm tag=TAG20100202T102350 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 02-FEB-10
piece handle=/oracle_db_storage/rman/arch/ARCH_1097_1_29l50dhm tag=TAG20100202T102350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 02-FEB-10

RMAN> list backup tag=TAG20100202T102350;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
953     1.18M      DISK        00:00:02     02-FEB-10
 BP Key: 953   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T102350
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1098_1_2al50dhm

 List of Archived Logs in backup set 953
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4231    1023299832 02-FEB-10 1023299888 02-FEB-10
 1    4232    1023299888 02-FEB-10 1023301176 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
954     39.53M     DISK        00:00:08     02-FEB-10
 BP Key: 954   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T102350
 Piece Name: /oracle_db_storage/rman/arch/ARCH_1097_1_29l50dhm

 List of Archived Logs in backup set 954
 Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
 ---- ------- ---------- --------- ---------- ---------
 1    4230    1023228173 02-FEB-10 1023299832 02-FEB-10

Now even if the cron didn’t run due to some reasons, the next time it runs, all the archivelogs that have not been backuped 1 times will be backed up.I won’t miss any archivelog.It was a new learning for me..

Hope this helps someone ūüôā

rman target /Recovery Manager: Release 10.2.0.3.0 РProduction on Tue Feb 2 10:20:18 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: TESTA (DBID=114007228)

RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
944     1.22G      DISK        00:04:22     02-FEB-10
BP Key: 944   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T021557
Piece Name: /oracle_db_storage/rman/arch/ARCH_21l4vguu_1_1

List of Archived Logs in backup set 944
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4209    1014571999 01-FEB-10 1015662867 02-FEB-10
1    4210    1015662867 02-FEB-10 1016718444 02-FEB-10
1    4211    1016718444 02-FEB-10 1017788958 02-FEB-10
1    4212    1017788958 02-FEB-10 1018859257 02-FEB-10
1    4213    1018859257 02-FEB-10 1019928942 02-FEB-10
1    4214    1019928942 02-FEB-10 1021002663 02-FEB-10
1    4215    1021002663 02-FEB-10 1022083432 02-FEB-10
1    4216    1022083432 02-FEB-10 1022864419 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
945     1.28G      DISK        00:04:31     02-FEB-10
BP Key: 945   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T021557
Piece Name: /oracle_db_storage/rman/arch/ARCH_20l4vguu_1_1

List of Archived Logs in backup set 945
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4201    1011043347 01-FEB-10 1011335909 01-FEB-10
1    4202    1011335909 01-FEB-10 1011649208 01-FEB-10
1    4203    1011649208 01-FEB-10 1011963726 01-FEB-10
1    4204    1011963726 01-FEB-10 1012256376 01-FEB-10
1    4205    1012256376 01-FEB-10 1012576084 01-FEB-10
1    4206    1012576084 01-FEB-10 1012877056 01-FEB-10
1    4207    1012877056 01-FEB-10 1013474586 01-FEB-10
1    4208    1013474586 01-FEB-10 1014571999 01-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
946     30.82M     DISK        00:00:07     02-FEB-10
BP Key: 946   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T032714
Piece Name: /oracle_db_storage/testb/backup/flash_recovery_area/TESTB/backupset/2010_02_02/o1_mf_annnn_TAG20100202T032714_5pj30msg_.bkp

List of Archived Logs in backup set 946
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4221    1022871009 02-FEB-10 1022925671 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
947     5.77M      DISK        00:00:03     02-FEB-10
BP Key: 947   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
Piece Name: /oracle_db_storage/rman/arch/ARCH_1092_1_24l507qs

List of Archived Logs in backup set 947
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4223    1023209017 02-FEB-10 1023215386 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
948     3.66M      DISK        00:00:02     02-FEB-10
BP Key: 948   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
Piece Name: /oracle_db_storage/rman/arch/ARCH_1093_1_25l507r1

List of Archived Logs in backup set 948
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4217    1022864419 02-FEB-10 1022870971 02-FEB-10
1    4218    1022870971 02-FEB-10 1022870984 02-FEB-10
1    4219    1022870984 02-FEB-10 1022870999 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
949     157.76M    DISK        00:00:28     02-FEB-10
BP Key: 949   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084620
Piece Name: /oracle_db_storage/rman/arch/ARCH_1091_1_23l507qs

List of Archived Logs in backup set 949
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4220    1022870999 02-FEB-10 1022871009 02-FEB-10
1    4222    1022925671 02-FEB-10 1023209017 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
950     2.22M      DISK        00:00:01     02-FEB-10
BP Key: 950   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T084810
Piece Name: /oracle_db_storage/rman/arch/ARCH_1094_1_26l507ua

List of Archived Logs in backup set 950
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4224    1023215386 02-FEB-10 1023217196 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
951     2.24M      DISK        00:00:01     02-FEB-10
BP Key: 951   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T085623
Piece Name: /oracle_db_storage/rman/arch/ARCH_1096_1_28l508dn

List of Archived Logs in backup set 951
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4225    1023217196 02-FEB-10 1023219527 02-FEB-10
1    4226    1023219527 02-FEB-10 1023219584 02-FEB-10
1    4227    1023219584 02-FEB-10 1023219598 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
952     8.10M      DISK        00:00:02     02-FEB-10
BP Key: 952   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T085623
Piece Name: /oracle_db_storage/rman/arch/ARCH_1095_1_27l508dn

List of Archived Logs in backup set 952
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4228    1023219598 02-FEB-10 1023219613 02-FEB-10
1    4229    1023219613 02-FEB-10 1023228173 02-FEB-10

RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
2761    1    4201    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4201_661394635.dbf
2762    1    4202    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4202_661394635.dbf
2763    1    4203    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4203_661394635.dbf
2764    1    4204    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4204_661394635.dbf
2765    1    4205    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4205_661394635.dbf
2766    1    4206    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4206_661394635.dbf
2767    1    4207    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4207_661394635.dbf
2768    1    4208    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4208_661394635.dbf
2769    1    4209    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4209_661394635.dbf
2770    1    4210    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4210_661394635.dbf
2771    1    4211    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4211_661394635.dbf
2772    1    4212    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4212_661394635.dbf
2773    1    4213    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4213_661394635.dbf
2774    1    4214    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4214_661394635.dbf
2775    1    4215    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4215_661394635.dbf
2776    1    4216    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4216_661394635.dbf
2777    1    4217    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4217_661394635.dbf
2778    1    4218    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4218_661394635.dbf
2779    1    4219    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4219_661394635.dbf
2780    1    4220    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4220_661394635.dbf
2781    1    4221    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4221_661394635.dbf
2782    1    4222    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4222_661394635.dbf
2783    1    4223    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4223_661394635.dbf
2784    1    4224    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4224_661394635.dbf
2785    1    4225    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4225_661394635.dbf
2786    1    4226    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4226_661394635.dbf
2787    1    4227    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4227_661394635.dbf
2788    1    4228    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4228_661394635.dbf
2789    1    4229    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4229_661394635.dbf

RMAN>¬† sql ‘alter system switch logfile’;

sql statement: alter system switch logfile

RMAN> sql ‘alter system switch logfile’;

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
2761    1    4201    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4201_661394635.dbf
2762    1    4202    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4202_661394635.dbf
2763    1    4203    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4203_661394635.dbf
2764    1    4204    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4204_661394635.dbf
2765    1    4205    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4205_661394635.dbf
2766    1    4206    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4206_661394635.dbf
2767    1    4207    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4207_661394635.dbf
2768    1    4208    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4208_661394635.dbf
2769    1    4209    A 01-FEB-10 /oracle_db_storage/testb/arch/1_4209_661394635.dbf
2770    1    4210    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4210_661394635.dbf
2771    1    4211    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4211_661394635.dbf
2772    1    4212    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4212_661394635.dbf
2773    1    4213    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4213_661394635.dbf
2774    1    4214    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4214_661394635.dbf
2775    1    4215    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4215_661394635.dbf
2776    1    4216    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4216_661394635.dbf
2777    1    4217    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4217_661394635.dbf
2778    1    4218    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4218_661394635.dbf
2779    1    4219    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4219_661394635.dbf
2780    1    4220    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4220_661394635.dbf
2781    1    4221    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4221_661394635.dbf
2782    1    4222    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4222_661394635.dbf
2783    1    4223    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4223_661394635.dbf
2784    1    4224    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4224_661394635.dbf
2785    1    4225    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4225_661394635.dbf
2786    1    4226    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4226_661394635.dbf
2787    1    4227    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4227_661394635.dbf
2788    1    4228    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4228_661394635.dbf
2789    1    4229    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4229_661394635.dbf
2790    1    4230    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4230_661394635.dbf
2791    1    4231    A 02-FEB-10 /oracle_db_storage/testb/arch/1_4231_661394635.dbf

RMAN> backup as compressed backupset archivelog all not backed up 1 times format ‘/oracle_db_storage/rman/arch/ARCH_%s_%p_%u’;

Starting backup at 02-FEB-10
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=933 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=935 devtype=DISK
skipping archive log file /oracle_db_storage/testb/arch/1_4201_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4202_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4203_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4204_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4205_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4206_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4207_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4208_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4209_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4210_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4211_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4212_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4213_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4214_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4215_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4216_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4217_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4218_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4219_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4220_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4221_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4222_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4223_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4224_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4225_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4226_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4227_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4228_661394635.dbf; already backed up 1 time(s)
skipping archive log file /oracle_db_storage/testb/arch/1_4229_661394635.dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4230 recid=2790 stamp=709899719
channel ORA_DISK_1: starting piece 1 at 02-FEB-10
channel ORA_DISK_2: starting compressed archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=4231 recid=2791 stamp=709899719
input archive log thread=1 sequence=4232 recid=2792 stamp=709899830
channel ORA_DISK_2: starting piece 1 at 02-FEB-10
channel ORA_DISK_2: finished piece 1 at 02-FEB-10
piece handle=/oracle_db_storage/rman/arch/ARCH_1098_1_2al50dhm tag=TAG20100202T102350 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 02-FEB-10
piece handle=/oracle_db_storage/rman/arch/ARCH_1097_1_29l50dhm tag=TAG20100202T102350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 02-FEB-10

RMAN> list backup tag=TAG20100202T102350;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
953     1.18M      DISK        00:00:02     02-FEB-10
BP Key: 953   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T102350
Piece Name: /oracle_db_storage/rman/arch/ARCH_1098_1_2al50dhm

List of Archived Logs in backup set 953
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4231    1023299832 02-FEB-10 1023299888 02-FEB-10
1    4232    1023299888 02-FEB-10 1023301176 02-FEB-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
954     39.53M     DISK        00:00:08     02-FEB-10
BP Key: 954   Status: AVAILABLE  Compressed: YES  Tag: TAG20100202T102350
Piece Name: /oracle_db_storage/rman/arch/ARCH_1097_1_29l50dhm

List of Archived Logs in backup set 954
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    4230    1023228173 02-FEB-10 1023299832 02-FEB-10

[oracle@aires-testb arch]$ ls -lrt
total 2848536
-rw-r—– 1 oracle oinstall 1312723456 Feb¬† 2 02:20 ARCH_21l4vguu_1_1
-rw-r—– 1 oracle oinstall 1370094592 Feb¬† 2 02:20 ARCH_20l4vguu_1_1
-rw-r—– 1 oracle oinstall¬†¬†¬† 6048256 Feb¬† 2 08:46 ARCH_1092_1_24l507qs
-rw-r—– 1 oracle oinstall¬†¬†¬† 3837952 Feb¬† 2 08:46 ARCH_1093_1_25l507r1
-rw-r—– 1 oracle oinstall¬† 165422592 Feb¬† 2 08:46 ARCH_1091_1_23l507qs
-rw-r—– 1 oracle oinstall¬†¬†¬† 2329600 Feb¬† 2 08:48 ARCH_1094_1_26l507ua
-rw-r—– 1 oracle oinstall¬†¬†¬† 2351616 Feb¬† 2 08:56 ARCH_1096_1_28l508dn
-rw-r—– 1 oracle oinstall¬†¬†¬† 8492544 Feb¬† 2 08:56 ARCH_1095_1_27l508dn
-rw-r—– 1 oracle oinstall¬†¬†¬† 1239040 Feb¬† 2 10:23 ARCH_1098_1_2al50dhm
-rw-r—– 1 oracle oinstall¬†¬† 41454592 Feb¬† 2 10:23 ARCH_1097_1_29l50dhm
[oracle@aires-testb arch]$

Determining the PCTFREE

There’s a nice post on “How to determine the PCTFREE” over OTN by Anurag Tibrewal.Its worth reading.

http://forums.oracle.com/forums/message.jspa?messageID=4062829#4062829

I would be using below way to determine my pctfree based on average row length.
Lets take an example.

I have a table employee. It has five column
– First_Name varchar2(40)
– Last_name varchar2(40)
– Middle_name varhcar2(30)
– Confirmation_date date
– Salary number(12)

IF I would be using single byte encoding then all my character would be using 1byte.
Hence maximum bytes required to store above row would be 125 bytes. If during insert the average row length is 50 and there is frequent updatation and as a result the row may expand to an average length of 100.

My block size is 8192. Oracle stores some data on header etc. Lets assume 192 bytes(for easy calculation) are required for this overhead. Hence I can store (8192-192)=8000 bytes of user data. So during insert, I can insert (8000/50) = 160 Rows per block.

But if Oracle do that then for every updation that expands a row above 50 (say to 100), it would be not able to find any free space in its current block and hence it would have to migrate that row to some new block which has 100bytes available. To stop this we would tell oracle to reserv some space during insert itself.

Since my average row length is 100 bytes, I would insert (8000/100) = 80 rows only per block during insertion. This would require 80*50=4000 bytes of space in a block during insert and so I would tell oracle to reserve (8000-4000) bytes for future updation. This means I would tell Oracle to reserve (4000/8000) = 50% of space during insert. Hence my pctfree is 50% and number of rows on an average would be 80.

But again this would not stop row migration. This would just decrease row migration. Why?
Lets say that Oracle inserted 80 rows with 50 bytes each in a block and hence occupied 4000 bytes. Now all these 80 rows were updated to 125 bytes (Since that is maximum bytes this row can store and still if you look at the average row length of entire table it is 100 bytes only). Then in that case First 53 rows would get space in its current block but 54th block would not be able to get space in this block and would have to migrate. This would free 50 bytes of space in its current block which can be utilized by other rows else they would also migrate. So to stop this migration one solution would be to keep reserved space for all these 125 bytes (maximum length instead of average length).

In that case I would insert (8000/125) = 64 rows per block. Hence during insertion space required would be 64*50 (because 50 is average bytes required during insertion) = 3200. So my pctfree becomes ((8000-3200)/8000) = 60%.

Drawback I am wasting 10% space more just to prevent some percentage of row migration.

Does this prevent row migration?
What if I inserted some rows with 32 bytes each. This means each block can hold (3200/32) = 100 rows(With 60% pctfree). This could expand to 100*125=12500 bytes and hence again some row from this block would require migration.

Point is you cannot stop migration with pctfree you can just minimize it.
Yes there are ways to restrict the number of rows per block (Here 64 rows per block) but that means you are just wasting your space. So there seems to be a trade off between space and row migration.

Moreover you cannot do anything for row chaining. Because this arises when size of a single row is more than the size of a block.

Must Read !!!!!

Below are the links which are highly recommend to read as difficult topics are made easy to understand.

http://db-optimizer.blogspot.com/2010/01/oracle-waits-latch-cache-buffer-chains.html

http://db-optimizer.blogspot.com/2010/01/oracle-wait-buffer-busy-wait.html

http://db-optimizer.blogspot.com/2010/01/oracle-redo-log-waits.html

http://db-optimizer.blogspot.com/2010/01/oracle-standard-io-waits.html

NJOY READING ūüôā

ORA-00214 – Controlfiles inconsistent

Today’s morning started with “ORA-00214”.I get happy when i get errors or issues.Coming back to point, here i would show the steps taken to resolve it.

“An ORA-00214 is issued whenever Oracle detects an inconsistency between two mirrored copies of the control file.”

One of the UAT db was unable to mount cause of inconsistencies in the multiplexed controlfiles. It gave the below error during startup :-

sql> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1295896 bytes
Variable Size             146803176 bytes
Database Buffers           54525952 bytes
Redo Buffers                7090176 bytes
ORA-00214: controlfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL' version 17404
inconsistent with file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL03.CTL' version 17409

Steps taken to resolve it:-

1. Show parameter control_file

2. As from the above error it could been seen that the version <number> for CONTROL03.CTL is higher, compared to CONTROL01.CTL, so

sql> alter system set control_files='D:\oracle\product\10.2.0\oradata\CONTROL03.CTL' scope=spfile;

sql> shutdown immediate;

sql> startup mount; --> The mount was successful.

sql> alter database open;

Checked for is some error in alert log.Everything seemed to be ok.Changed the control_files parameter,switched the logfile groups and the made the database down.

sql> alter system set control_files='D:\oracle\product\10.2.0\oradata\CONTROL01.CTL',
'D:\oracle\product\10.2.0\oradata\CONTROL02.CTL','D:\oracle\product\10.2.0\oradata\CONTROL03.CTL' scope=spfile;

sql> alter system switch logfile;
sql> alter system switch logfile;
sql> shutdown immediate;

Copied the CONTROL03.CTL , made two copies of it ,renamed it to CONTROL01.CTL and CONTROL02.CTL and started up the database.

sql >startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1295896 bytes
Variable Size             146803176 bytes
Database Buffers           54525952 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.
sql>
sql>

NOTE:- When in step 2 the control_files parameter was set to ‘D:\oracle\product\10.2.0\oradata\CONTROL01.CTL’, SCN mismatch occurred between the redo logfiles and the controlfile and the database didn’t go to mount stage.