Tuesday, July 28, 2015

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

Cause :  This condition may be temporary, such as at instance startup. In my case instance are in nomount mode and I am trying to connect database through Primary database.

Solution : After adding SID_LIST_LISTENER in listener I have solved this problem.

SQL> col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SQL> SQL> SQL> SQL>
DESTINATION                         ERROR
----------------------------------- -----------------------------------------------------------------
USE_DB_RECOVERY_FILE_DEST
cbsdb                              ORA-12528: TNS:listener: all appropriate instances are blocking
                                    new connections

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 6.8413E+10 bytes
Fixed Size                  2238616 bytes
Variable Size            5.3687E+10 bytes
Database Buffers         1.4630E+10 bytes
Redo Buffers               93618176 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
DCDB1$
DCDB1$
DCDB1$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-JUL-2015 13:58:33

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DCDB1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                26-MAR-2015 14:39:25
Uptime                    120 days 23 hr. 19 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cbsdb" has 1 instance(s).
  Instance "cbsdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
DCDB1$ cat /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DCDB1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle/app

DCDB1$ echo $ORACLE_HOME
/u01/oracle/app/product/11.2.0/db_1
DCDB1$ echo $ORACLE_SID
cbsdb
DCDB1$ vi listener.ora
"listener.ora" 22 lines, 544 characters # listener.ora Network Configuration File: /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DCDB1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(GLOBAL_DBNAME = cbsdb)
(ORACLE_HOME = /u01/oracle/app/product/11.2.0/db_1)
(SID_NAME = cbsdb)
    )
  )
ADR_BASE_LISTENER = /u01/oracle/app

~
~"listener.ora" 22 lines, 544 characters
:wq
"listener.ora" 22 lines, 540 characters
DCDB1$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-JUL-2015 14:03:47

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/oracle/app/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DCDB1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 14:03:55
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cbsdb" has 1 instance(s).
  Instance "cbsdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

FAILED DESTINATION Switchover Status In Data Guard.

Cause: This status shown when invalid configuration exist in data guard parameter or Primary Server are not able to connect Standby Server.

Solve :  In my case primary server listeners are blocking new connection. After starting my

SQL> select name,open_mode,db_unique_name,switchover_status from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME
--------- -------------------- ------------------------------
SWITCHOVER_STATUS
--------------------
SBLCBS    READ WRITE           drsbldb
FAILED DESTINATION

SQL> col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SQL> SQL> SQL> SQL>
DESTINATION                         ERROR
----------------------------------- -----------------------------------------------------------------
USE_DB_RECOVERY_FILE_DEST
sblcbs                              ORA-12528: TNS:listener: all appropriate instances are blocking
                                    new connections


Jobs are not working/running .....

CBSDB1$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 28 23:06:10 2015

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

SQL> conn /as sysdba
Connected.
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes=1000 scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CBSDB1$



Export Dump File Using Procedure.

CREATE OR REPLACE PROCEDURE SP_DUMP_EXPORT (
P_FROM_DATE DATE,
P_TO_DATE DATE
)
IS
  L_DP_HANDLE       NUMBER;
  L_LAST_JOB_STATE  VARCHAR2(30) := 'UNDEFINED';
  L_JOB_STATE       VARCHAR2(30) := 'UNDEFINED';
  L_STS             KU$_STATUS;

  SCHEMA_NAMES VARCHAR2(300);
BEGIN

    SELECT '''' || REPLACE (WM_CONCAT (USERNAME), ',', ''',''') || ''''
      INTO SCHEMA_NAMES
      FROM DBA_USERS
     WHERE USERNAME IN ('RND', 'HR');

  L_DP_HANDLE := DBMS_DATAPUMP.OPEN(
    OPERATION   => 'EXPORT',
    JOB_MODE    => 'SCHEMA',
    REMOTE_LINK => NULL,
    JOB_NAME    => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY'),
    VERSION     => 'LATEST');

  DBMS_DATAPUMP.ADD_FILE(
    HANDLE    => L_DP_HANDLE,
    FILENAME  => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY')||'.DMP',
    DIRECTORY => 'DATA_DIR');

  DBMS_DATAPUMP.ADD_FILE(
    HANDLE    => L_DP_HANDLE,
    FILENAME  => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY')||'.LOG',
    DIRECTORY => 'DATA_DIR',
    FILETYPE  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 
   DBMS_DATAPUMP.METADATA_FILTER(L_DP_HANDLE, 'SCHEMA_EXPR', 'IN (' || SCHEMA_NAMES || ')');

  DBMS_DATAPUMP.START_JOB(L_DP_HANDLE);

  DBMS_DATAPUMP.DETACH(L_DP_HANDLE);
END;


SQL> EXEC SP_DUMP_EXPORT('01-JAN-2015','30-JAN-2015');

PL/SQL procedure successfully completed.

SQL>

Sunday, July 26, 2015

Return data from second query if first query result is empty.

This query help you to use WHEN NO_DATA_FOUND Exception feature in SQL. Here I am used three query.

1. Return result only from first query if first query return some data.
2. Return result only from Second query if first query return no data.
3. Return result only from Third query if first and second query  return no data.

Setup :

CREATE TABLE ACC_PROD
(PROD NUMBER,
 ACC NUMBER,
 RS NUMBER);

Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (1, 0, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 101, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 0, 1);
COMMIT;

Query :

WITH DATA_FIRST
     AS (SELECT *
           FROM ACC_PROD
          WHERE (PROD = 0 AND ACC = :P_ACC)),
     DATA_SECOND
     AS (SELECT *
           FROM ACC_PROD
          WHERE (PROD = :P_PROD AND ACC = 0)),
     DATA_THIRD
     AS (SELECT *
           FROM ACC_PROD
          WHERE (PROD = 0 AND ACC = 0))
SELECT * FROM DATA_FIRST
UNION ALL
SELECT *
  FROM DATA_SECOND
 WHERE NOT EXISTS (SELECT NULL FROM DATA_FIRST)
UNION ALL
SELECT *
  FROM DATA_THIRD
 WHERE     NOT EXISTS (SELECT NULL FROM DATA_FIRST)
       AND NOT EXISTS (SELECT NULL FROM DATA_SECOND)

Wednesday, July 22, 2015

Finding Operating Process With Oracle Process across by Session ID.



========== Query =========

set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15

select p.spid os_process_id,
       s.sid session_id,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et,
       p.program,
       p.terminal,
       logon_time,
       module,
       s.osuser
from v$session s, v$process p
where s.paddr= p.addr
and p.spid='&spid'
order by p.spid;

===============================

SQL> set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select p.spid os_process_id,
  2         s.sid session_id,
  3         s.serial#,
  4         s.username,
  5         s.status,
  6         s.last_call_et,
  7         p.program,
  8         p.terminal,
  9         logon_time,
 10         module,
 11         s.osuser
 12  from v$session s, v$process p
 13  where s.paddr= p.addr
 14  and p.spid='&spid'
 15  order by p.spid;
Enter value for spid: 11403790
old  14: and p.spid='&spid'
new  14: and p.spid='11403790'

OS_PROCESS_ID            SESSION_ID    SERIAL# USERNAME             STATUS   LAST_CALL_ET PROGRAM         TERMINAL        LOGON_TIM MODULE          OSUSER
------------------------ ---------- ---------- -------------------- -------- ------------ --------------- --------------- --------- --------------- ---------------
11403790                       4574      28565 PRODUSR              ACTIVE             39 oracle@DBSRV1    UNKNOWN         22-JUL-15 JDBC Thin Client


SQL>

Tuesday, July 21, 2015

Enable Block Change Tracking.

From Oracle 10.2 onward, Oracle provide block change tracking feature which is very useful to reduce RMAN incremental backup time. Prior this version oracle need to read every block and if the block has changed it was backup, for that RMAN backup job took long time.


SQL> SET LINESIZE    180;
COLUMN filename      FORMAT a40       HEAD 'File Name';
COLUMN status        FORMAT a20         HEAD 'File Status';SQL> SQL>
SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
                                         DISABLED

SQL> alter database enable block change tracking using file '/u01/app/oracle/oradata/sblcbs/block_change_file01.dbf';

Database altered.

SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
/u01/app/oracle/oradata/sblcbs/block_cha ENABLED
nge_file01.dbf


SQL>

------ Disabled Block Change Tracking ------

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
                                         DISABLED

SQL>

Monday, July 13, 2015

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Cause: Oracle password file does not exist in $ORACLE_HOME/dbs directory of AUXILIARY (Standby) Database.

Solution : Copy password file from primary database directory ($ORACLE_HOME/dbs)  to AUXILIARY (Standby) Database  directory ($ORACLE_HOME/dbs).

[oracle@bcbldb01 dbs]$ rman TARGET sys/oracle@DCCBSDB AUXILIARY sys/oracle@DCCBSDBDR

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 9 17:23:34 2015

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

connected to target database: DCCBSDB (DBID=2308296244)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges


[oracle@drdb1 dbs]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall 1536 Jun 17 13:30 orapwdccbsdb
-rw-r--r-- 1 oracle oinstall 1776 Jul  9 17:02 initdccbsdbdr.ora
-rw-rw---- 1 oracle oinstall 1544 Jul  9 17:21 hc_dccbsdbdr.dat
[oracle@drdb1 dbs]$ mv orapwdccbsdb orapwdccbsdbdr
[oracle@drdb1 dbs]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall 1536 Jun 17 13:30 orapwdccbsdbdr
-rw-r--r-- 1 oracle oinstall 1776 Jul  9 17:02 initdccbsdbdr.ora
-rw-rw---- 1 oracle oinstall 1544 Jul  9 17:21 hc_dccbsdbdr.dat
[oracle@drdb1 dbs]$ mv orapwdccbsdb orapwdccbsdbdr


[oracle@bcbldb01 dbs]$ rman TARGET sys/oracle@DCCBSDB AUXILIARY sys/oracle@DCCBSDBDR

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 9 17:35:34 2015

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

connected to target database: DCCBSDB (DBID=2308296244)
connected to auxiliary database: DCCBSDB (not mounted)

ORA-10458: standby database requires recovery

Cause: Standby database recovery not completed. In my case, standby database was stopped cause of power failure, for that archive log file was not transmit to standby server. I have found log gap in my primary database by using the following query :

SELECT INST_ID,
     SEQUENCE#,
     TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY HH24:MI:SS') "Time",
     NAME,
     STATUS,
     ARCHIVED,
     STANDBY_DEST,
     APPLIED
FROM GV$ARCHIVED_LOG
WHERE TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY') =TO_CHAR (SYSDATE, 'DD-MM-YYYY')
ORDER BY SEQUENCE#;

Solution: I have switch log file in my primary database, standby server receives archive log file and applies all of the archive logs. If the problem still exists Resolve GAP By Apply RMAN Incremental Backup.

The primary database is not able to shift redo data to standby database for that standby database are required to recover. You need to identify this reason. The following query helps you why primary DB is not shifting the redo.

You may check the following two post also.

http://rajiboracle.blogspot.com/2017/05/resolve-standby-gaps-by-applying.html

http://rajiboracle.blogspot.com/2015/09/resolve-archivelog-gaps-in-data-guard.html

col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT INST_ID, DESTINATION, ERROR FROM GV$ARCHIVE_DEST

WHERE DESTINATION IS NOT NULL;



[oracle@drdb1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 13:59:50 2015

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2228024 bytes
Variable Size             805306568 bytes
Database Buffers          469762048 bytes
Redo Buffers                8769536 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'

In Primary
---------------------------------------
[oracle@dcdb01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 14:06:08 2015

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

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

In Standby 
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1585093

Sunday, July 5, 2015

ORA-01172: recovery of thread 1 stuck at block 2438 of file 2

Cause: This error usually means that blocks of file 2 are corrupted, In this case we need to recover this blocks.

Solution : We need to recover all of corrupted block by using data file recovery or Database recovery. In my case I have recover database.

1. Data file recover.

SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>

2. Database Recovery

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
========================================================================

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  1219136 bytes
Variable Size             301991360 bytes
Database Buffers          922746880 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 2438 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
 

RMAN-04006: error from auxiliary database

[oracle@bcbldb01 ~]$ rman TARGET sys/oracle@SBLCBS AUXILIARY sys/oracle@SBLCBSDR
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 5 18:21:40 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SBLCBS (DBID=2308296244)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied
[oracle@bcbldb01 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsblcbs oracle@172.31.36.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsblcbsdr
oracle@172.31.36.69's password:
orapwsblcbs                                                                                                                           100% 1536     1.5KB/s   00:00
[oracle@bcbldb01 ~]$ rman TARGET sys/oracle@SBLCBS AUXILIARY sys/oracle@SBLCBSDR
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 5 18:21:59 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SBLCBS (DBID=2308296244)
connected to auxiliary database: SBLCBS (not mounted)
RMAN>

Saturday, July 4, 2015

Deleting Archivelog.

RMAN>DELETE ARCHIVELOG ALL;

RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM TIME 'SYSDATE-2' UNTIL TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM SEQUENCE 100;

RMAN>DELETE ARCHIVELOG UNTIL SEQUENCE 100;

RMAN>DELETE ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 150;