ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error :

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 1: ‘D:\ORADB\RECTCDB\system01.dbf’

Cause :

Database is in backup mode while it went down.

Solution :

Step 1 : Sqlplus  ‘/as sysdba’

Step 2 : startup mount

Step 3 : Check backup file using below command.

select * from V$BACKUP

Step 4 : The following command can be used to take all of the data files out of hot backup mode:

SQL> ALTER DATABASE END BACKUP;

Step 5 :  SQL> Alter database open;

 

Enable and Disable the dataguard steps

Disable Data Guard

On Standby Database:
1. Cancel the Recovery job with following command

alter database recover managed standby database cancel;

2. Stop the destination

alter system set log_archive_dest_state_2=DEFER;


On Primary Database:
1. Stop the standby destination for transport archives.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;


Enable the dataguard:
On Primary DB:
1. Enable the archive log transport:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

On Standby DB:
1. Start the recovery process with following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

2. Enable the log archive for switchover condition needed.

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 
Shutdown and Start the Primary / Standby database dataguard.

https://dbaclass.com/article/remove-dataguard-configuration-from-primary-database/

 

Remove Dataguard Configuration From Primary Database:

    If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps.

1.Put primary database on maximum performance mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

2. Remove below parameters from spfile of primary database:

FAL_SERVER
FAL_CLIENT
LOG_ARCHIVE_CONFIG
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n – pointing to standby database like _2 or _3
LOG_ARCHIVE_DEST_STATE_n 
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT

alter system reset LOG_ARCHIVE_CONFIG scope=spfile;
alter system reset DB_FILE_NAME_CONVERT scope=spfile;
alter system reset LOG_FILE_NAME_CONVERT scope=spfile;
alter system reset STANDBY_FILE_MANAGEMENT scope=spfile;
alter system reset FAL_SERVER scope=spfile;
alter system reset FAL_CLIENT scope=spfile;
alter system reset LOG_ARCHIVE_DEST_STATE_2 scope=spfile;
alter system reset LOG_ARCHIVE_DEST_2 scope=spfile; --- destination pointing to standby
alter system reset STANDBY_ARCHIVE_DEST scope=spfile;

3. Drop all standby log files from primary:

SELECT GROUP# FROM V$STANDBY_LOG;

alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;

4. Restart the database to reflect the parameters

shutdown immediate;
startup

Now the standby database is completely separated from primary.
If you want to use the standby database as a standalone database, then follow below steps on standby

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP

You can change dbid using NID utility( as dbid would be same as primary)

 

ORA-02396: exceeded maximum idle time, please connect again.

Resolution

To resolve the above error there are three approaches:

1.Set the idle time to unlimited on the Oracle database. 
alter profile <profile_name> limit idle_time UNLIMITED;

before

After 

 

DBMS_SCHEDULER. 

Default time zone change.

DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME

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

06/10/2021 07:09:12 CST6CDT CDT

SELECT *  FROM   dba_scheduler_global_attribute  WHERE  attribute_name = 'DEFAULT_TIMEZONE';  

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Colombo');

 

ADRCI : DIA-48216: Field Len Exceeds Max Field Length

Detect place :
1. Find path:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME LIKE 'Diag Trace%';

2. Find problematic line:
sed ‘s/\(.*\)<msg.*\(<msg.*\)/\1\2/' < log.xml | diff -w log.xml –

3. Remove it :

 RMAN Restore Preview

$ rman target /

RMAN> list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

15526   Full    6.77M      DISK        00:00:01     15-SEP-21

        BP Key: 15615   Status: AVAILABLE  Compressed: YES  Tag: TAG20210915T234616

        Piece Name: +RECO/SSLCDB/BACKUP_FILES/c-4188050246-20210915-05

  SPFILE Included: Modification time: 15-SEP-21

  SPFILE db_unique_name: SSLCDB

  Control File Included: Ckp SCN: 67374431842   Ckp time: 15-SEP-21

RMAN>  

run{

set until scn 67374431842;

restore database preview;

}

List of Archived Log Copies for database with db_unique_name SSLCDB

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

Key     Thrd Seq     S Low Time

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

92560   1    47166   A 15-SEP-21

        Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_15/thread_1_seq_47166.3007.1083368767

92562   1    47167   A 15-SEP-21

        Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_16/thread_1_seq_47167.3005.1083369733

recovery will be done up to SCN 67374431842

Media recovery start SCN is 67374208371

Recovery must be done beyond SCN 67374426191 to clear datafile fuzziness

Finished restore at 16-SEP-21

RMAN>

Restore Preview Reports RMAN-05119 and Recover Database Reports RMAN-06054 ( Multi-tenant ) (Doc ID 2233445.1)

Error seems to be a wrong notification which misleads the recovery process.  Ignore the error (or apply the patch 20315311) and proceed with the 'alter database open' after the recover database.   

Sample output of the errors 

Assuming you have valid backup of database and archivelogs, Oracle recommends validating the integrity of the backup by running 'restore database preview'.

Due to a code defect, 'restore database preview' command returns the error RMAN-05119 although valid backups exist.