How to delete archive log files using PL/SQL

Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,

User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.

ORA-65500 When using DUPLICATE to Create Standby Database

Oracle Database - Enterprise Edition - Version 12.2.0.1 + RU 12.2.0.1.171017
Operation System - Oracle Solaris 11.3 SPARC

When running RMAN command DUPLICATE to create physical standby database as following:

RMAN> duplicate target database 
2>    for standby 
3>    from active database 
4>    dorecover 
5>    spfile 
6>      reset control_files 
7>      set db_unique_name='DBNEW' 
8>      set fal_server='DB01' 
9>    nofilenamecheck; 

using target database control file instead of recovery catalog

Starting Duplicate Db at 15-JAN-18
current log archived
... ...

Got error ORA-65500:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.

Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,

[oracle@dbaplus]$ srvctl config database
DBNEW

The database DBNEW does exist in the crs registry, try to remove it

[oracle@dbaplus]$ srlvctl remove database -db DBNEW
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$

Then DUPLICATE can run successfully.

RMAN got ORA-00245 while backing up RAC control file

When backing up control file for RAC databases, you could got ORA-00245 as following,

RMAN> backup device type sbt current controlfile ;

Starting backup at 2016-03-20 19:08:49
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1941 instance=racdb_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 03/20/2016 19:08:50
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

In order to have a consistent version of the control file, RMAN creates a snapshot when asked to back up the control file,
then copy the snapshot control file to backup media.  The default file name of the snapshot is system-dependent, each instance
of same RAC database has default file name,

[oracle@host01 ~]$ export ORACLE_SID=racdb_1
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016

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

connected to target database: racdb (DBID=435209035)

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_1.f'; # default


[oracle@host02]$ export ORACLE_SID=racdb_2
[oracle@host02]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016

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

connected to target database: racdb (DBID=435209035)

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_2.f'; # default

Depending on whether the RMAN session, which creates snapshot file, is connected to instance racdb_1 running on node host01 or instance racdb_2
running on node host02, the snapshot file name will be snapcf_racdb_1.f or snapcf_racdb_2.f respectively.

When RMAN backs up the control file and the snapshot file is create on other node,  ORA-00245 will be raised up. To avoid the error, the
RMAN Snapshot Control File Location has to be configured on shared storage which is accessible by all nodes in the cluster (all instances of
the RAC databases),

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';
new RMAN configuration parameters are successfully stored

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3

Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038

Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...

Try to open database:

RMAN> alter database open;

database opened

Database is opened successfully.

Got 'database incarnation not set' while using RMAN with catalog

RMAN> run {
2> allocate channel oem_backup_sbt1 type 'SBT_TAPE' format '%d_%U' parms "ENV=(NB_ORA_POLICY=oracle_rman, NB_ORA_SCHED=Default)";
3> backup as BACKUPSET tag 'BACKUP_REVQAS1_121814161014' recovery files;
4> release channel oem_backup_sbt1;
5> }
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
allocated channel: oem_backup_sbt1
channel oem_backup_sbt1: SID=162 device type=SBT_TAPE
channel oem_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

Starting backup at 2014-12-18 16:11:33
released channel: oem_backup_sbt1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/18/2014 16:11:33
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set


RMAN> exit;

Recovery Manager complete.

select * from dual;