ORA-01194: file 1 needs more recovery to be consistent

[ _allow_resetlogs_corruption ]

===Problem ====

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

 RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 08/25/2021 18:25:45

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: Oracle error from auxiliary database: ORA-01152: file 3 was not restored from a sufficiently old backup

ORA-01110: data file 3: '/u03/oradata/TESTCDB/sslcdb/datafile/sysaux.291.1050880731'

or

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

====== Solution =============================

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

SQL> STARTUP MOUNT

SQL> alter database open resetlogs;

Database altered.

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

SQL> shutdown immediate

SQL> startup

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

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= FALSE SCOPE = SPFILE;

 

ORA-12516:TNS:listener could not find available handler with matching protocol stack

TNS-12516 TNS:listener could not find instance with matching protocol stack ORA-12516 TNS:listener could not find available handler with matching protocol stack TNS-12519 TNS: no appropriate service handler found ORA-12519 TNS: no appropriate service handler found  ORA-12520 TNS:listener could not find available handler for requested type of server  Cause: PMON update listener with information about instance such as load and dispatcher information. PROCESS parameter determines the maximum load for dedicated connection in database. The interval at which PMON provides SERVICE_UPDATE information differs according to the workload of the instance. The maximum interval between these service updates is 10 minutes. When the threshold exceeds the limit then listener become “Blocked” and no new session can be made since it refusing incoming connection. Once, listener gets the information from PMON that the thresholds are below the configured limit then listener resume accepting connection. Since SERVICE_UPDATE can take maximum 10 minutes, therefore, there can be a difference between the current instance load according to the listener and the actual instance load. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated.  Solution: ·         check if the number of current connections by using the following command in Unix: ps -ef | grep oracleSID | grep -v grep | wc –l   ·         Check process parameter in database   SQL> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE    11.2.0.3.0      Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 – Production   SQL> show parameter processes   NAME                TYPE        VALUE ------------------ ----------- ------- processes           integer     200    SQL> select count(*) from v$process;     COUNT(*) ----------        199   Increase process: processes=x session=(1.5 * PROCESSES) + 22  SQL> alter system set processes=300 scope=spfile;  Processes is not a dynamic parameter, so you need to restart database to take the effect.

RMAN-05541: no archived logs found in target database

 

RMAN-05541: no archived logs found in target database

Problem:

You are getting  RMAN-05541 error when duplicating database from a consistent (cold) RMAN backup.

Error Message:

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/08/2014 18:12:40 RMAN-05501: aborting duplication of target database RMAN-05541: no archived logs found in target database

Solution:

The error should be resolved by using NOREDO clause for your duplicate command.

Example:

run { set until time "to_date('08-OCT-2014 10:15:00','DD-MON-YYYY HH24:MI:SS')"; DUPLICATE DATABASE TO targetDB BACKUP LOCATION '/backup_Location' NOREDO;    }

 

RFS[4]: No standby redo logfiles created for T-1 dataguard

RFS[4]: No standby redo logfiles created for T-1

In Standby Environment of dataguard Server, We are getting following alert log error:

RFS[4]: No standby redo logfiles created for T-1
RFS[4]: Opened log for T-1.S-57586 dbid 618415567 branch 953210132


It show that No standby redo log file of thread 1 is present as per message, it also mention Thread number as 1.
On checking the Primary and Secondary database with thread column in views:

1. Check Primary Redo Thread Number:

SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;

THREAD# GROUP# SEQUENCE# BYTES     ARC STATUS
------- ------ --------- --------- --- ----------
1  	1      57586  	 209715200 NO  CURRENT  
1  	2      57584  	 209715200 YES INACTIVE  
1  	3      57585  	 209715200 YES INACTIVE  

 

2. Check Standby Thread number for Standby redo logs configured for DR Server.

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

THREAD# GROUP# SEQUENCE#  BYTES     ARC  STATUS 
------- ------ --------- ---------- ---   ----------
0       4      0         52428800  YES  UNASSIGNED  
0       5      0         52428800  YES  UNASSIGNED  
0  	6      0         52428800  YES  UNASSIGNED  
0  	7      0         52428800  YES  UNASSIGNED  
0  	8      0         52428800  YES  UNASSIGNED  
0  	9      0         52428800  YES  UNASSIGNED  

 

Note:Both Thread Number is different in value, so we need to drop and recreate the Standby with specify the THREAD caluse.

3. Stop the dataguard recovery process in Standby database:

-- for standby db which is under recovery, recovery needs to be stopped first
alter database recover managed standby database cancel;


4. Drop the existing Standby redo files:
Note: There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
In this example we configure 6 standby redo files.

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
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;


5. Create new Standby logfile with THREAD Clauses.

alter database add standby logfile thread 1 group 4 size 200m;

alter database add standby logfile THREAD 1 group 4 (‘D:\ORACLEXE\STANDBYREDO04.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 5 (‘D:\ORACLEXE\STANDBYREDO05.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 6 (‘D:\ORACLEXE\STANDBYREDO05.log’) SIZE 200M;
alter database add standby logfile THREAD 1 group 7 (‘D:\ORACLEXE\STANDBYREDO06.log’) SIZE 200M;

–Example for two members
–alter database add standby logfile THREAD 1 group 5 (‘D:\ORACLEXE\STANDBYREDO01A.log’,’D:\ORACLEXE\STANDBYREDO01B.log’) SIZE 200M;

–Example for ASM
–alter database add standby logfile THREAD 1 group 7 (‘+DATA(ONLINELOG)’,’+FRA(ONLINELOG)’) SIZE 200M;

Note: in my case standby redo size(50M) is different with primary redo size(200M). so I corrected that one also while created new standby.

6. Now verify the thread number and redo log

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;


7. Start the Recovery of the dataguard in oracle.

alter database recover managed standby database disconnect from session;

 How to delete all files in a directory with RoboCopy

Create an empty directory such as  C:\empty

In this scenario, the folder we would like to empty out is  C:\test

After creating the empty directory, you are ready to erase all the contents of your desired directory like so:

robocopy c:\empty c:\test /purge

or

robocopy c:\empty c:\test /MIR

https://tylermade.net/2017/10/06/how-to-delete-all-files-in-a-directory-with-robocopy/

 

Rebuild Index in a Oracle Schema

spool index_rebuild.sql
select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
from dba_indexes
where owner=upper('SIFSAPP');
spool off

locate broken index use:

select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes 
where index_type like '%DOMAIN%' and (domidx_status <> 'VALID' or domidx_opstatus <> 'VALID');
To rebuild the index use:

alter index INDEX_NAME rebuild;

 

ORA-03113: end-of-file on communication channel Process ID: 9097 Session ID: 365 Serial number: 1

[oracle@NVMBRCPGRHSACS01WT trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 17 14:25:17 2017

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

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 8.0973E+10 bytes

Fixed Size                  2237088 bytes

Variable Size            1.0737E+10 bytes

Database Buffers         7.0062E+10 bytes

Redo Buffers              171511808 bytes

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

Process ID: 9097

Session ID: 365 Serial number: 1

SQL>

Solution

========

startup mount;

alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 3;

alter database open;