Anuj kumar singh Oracle DBA

if following error occurred or database hanged due to this


Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_1202.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44131328 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_%u_.arc'



RMAN delete


oracle@apt-amd-02:~> id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall)
oracle@apt-amd-02:~> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 06:56:30 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> delete force archivelog until time 'sysdate -1'; ---- delete some archive log file

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
91 1 330 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc

92 1 331 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc

93 1 332 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc RECID=91 STAMP=749717522
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc RECID=92 STAMP=749717587
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc RECID=93 STAMP=749717596
Deleted 3 objects


check all the file deleted ....

RMAN> delete force archivelog until time 'sysdate -1';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
specification does not match any archived log in the repository

Remove space between Number/string string




SQL> select TRANSLATE('12123 3434 4535 5656','0123456789 ','0123456789') from dual ;

TRANSLATE('121233
-----------------
12123343445355656

1 row selected.



SQL> select replace('I am vihaan','vihaan','anuj') from dual;


REPLACE('
---------
I am anuj

1 row selected.


To remove space


SQL> select replace('I am vihaan ',' vihaan','anuj') from dual;


REPLACE('
---------
I amanuj

1 row selected.