ORA-10873:file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '+DATA / testbk/ datafile/ system.256.897496663'

ORA-10873:file 1 needs to be either taken out of backup mode or media recovered  ORA-01110: data file 1: '+DATA / testbk/ datafile/ system.256.897496663'

Cause: ORA-10873
In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> startup
ORACLE instance started.

Total System Global Area 5127602176 bytes
Fixed Size                  2214432 bytes
Variable Size            4580181472 bytes
Database Buffers          536870912 bytes
Redo Buffers                8335360 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '+DATA/testbk/datafile/system.256.897496663'

Solution:  Issue following SQL command in order to move out database from hot backup mode:
SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in
backup mode

OR

SQL> alter tablespace system end backup;

Open database
SQL> alter database open;
Database altered.

ORA-01149: cannot shutdown - file 1 has online backup set, ORA-01110: data file 1: '+DATA / testbk/ datafile/ system.256.897496663'

ORA-01149: cannot shutdown - file 1 has online backup set, ORA-01110: data file 1: '+DATA / testbk/ datafile/ system.256.897496663'

Oracle Error: While shutdown your database you may encounter following oracle error.
SQL> shutdown immediate
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '+DATA/testbk/datafile/system.256.897496663'

Cause: If you try to shutdown database when database is in hot backup mode, you will encounter above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shut down your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection: 
Following query will help you to understand which oracle tablespace datafile is in hot backup mode.
SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;

TABLESPACE_NAME                STATUS
------------------------------ ------------------
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24INDEX                    NOT ACTIVE
SYSAUX                         NOT ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       NOT ACTIVE
USERS                          NOT ACTIVE

10 rows selected.

Note: Status of the SYSTEM datafile seems to be active. i.e. hot backup mode.


Solution:
Issue following SQL command in order to move out database from hot backup mode:
SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in
backup mode

OR

SQL> alter tablespace system end backup;


Shutdown database
SQL> shutdown abort/immediate
ORACLE instance shut down.
SQL>

Open database
SQL> startup
ORACLE instance started.

Total System Global Area 5127602176 bytes
Fixed Size                  2214432 bytes
Variable Size            4580181472 bytes
Database Buffers          536870912 bytes
Redo Buffers                8335360 bytes
Database mounted.
Database opened.
SQL>
SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;

TABLESPACE_NAME                STATUS
------------------------------ ------------------
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24DATA                     NOT ACTIVE
NBLT24INDEX                    NOT ACTIVE
SYSAUX                         NOT ACTIVE
SYSTEM                         NOT ACTIVE
UNDOTBS1                       NOT ACTIVE
USERS                          NOT ACTIVE
10 rows selected.
SQL>

Database Monitoring Script

Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;

Track Tablespace Used/Free Space:
SELECT /* + RULE */  df.tablespace_name "Tablespace",  df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Track Redo generation by day:
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log
group by trunc(completion_time) order by 1;

Track Logon time of DB user and OS user:
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND';

ORA-03113: end-of-file on communication channel

ORA-03113: end-of-file on communication channel

oracle@rtgs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 24 13:00:49 2016

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size                  2269072 bytes

Variable Size            1.1442E+10 bytes

Database Buffers         4563402752 bytes

Redo Buffers               26480640 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 3988

Session ID: 66 Serial number: 3

SOLUTION:

[oracle@rtgs trace]$ tail -f -n 500 alert_rtgsdb.log

shows last 500 row in alert log file, which tells that

ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 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.

************************************************************************

>>  Check db_recovery_file_dest_size 

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 40G

>>  Increase the size of db_recovery_file_dest_size

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 45G sCOPE=BOTH 

SID='*';

System altered.

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 45G

SQL>

SQL>

>>  database open

SQL> alter database open;

Database altered.

SQL>

>>  Use crosscheck and delete expired archive log. 

SQL> !rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 24 13:05:16 2016

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

connected to target database: RTGSDB (DBID=1759447511)

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

Oracle 11g Release 2 (11.2.0.4.0) RAC Primary to RAC Physical Standby DataGuard Configuration from RMAN Backup on HP-UX Itanium (11.31) Environment [RMAN Backup with Target Connection]

This summary is not available. Please click here to view the post.