Sunday, 13 September 2020

How to solve ORA-19505: failed to identify file in RMAN?


Reason: Archive log is deleted from os level and catalog is not synchronised.

Posted by Pavan DBA on January 18, 2011

DBA’s will feel bad when they got any error. It’s common for any 

Lets say you got below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch00 channel at 01/17/2011 13:01:03
ORA-19505: failed to identify file “/dwh1/oraarch/arch/1_1100721_664058960.dbf”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Don’t be panic, nothing happened to your backup or database. If you read the error, its saying particular archive logfile is not available. This may be due to file deleted at OS level due to some reason.

In this situation, do the following

1. Run crosscheck command against archivelogs

RMAN> crosscheck archivelog all;

2. If you find any archives marked as EXPIRED, then delete those expired archives using below command

RMAN> delete expired archivelog all;
This will prompt you YES or NO. If you don’t want a prompt, use below command

RMAN> delete noprompt archivelog all;

3. Take a fresh archivelog backup

RMAN> backup archivelog all; (you  can use delete input clause also)

ORA-12640: Authentication adapter initialization failed

Sometimes when starting the database with the system user you will face this issue the above error message written in ORADIM.LOG

Solution:

  1. Modify SQLNET.ORA
  2. Start database with SQLplus after the service is restarted.
  3. Start the service as specific users.

Modify SQLNET.ORA, either by removing the line

sqlnet.authentication_services=(NTS)

or by changing it to

sqlnet.authentication_services=(NONE)

Start the database manually after the Oracle database service has started, using

SQL*Plus and connecting as SYSDBA.

Start Service as a Specific User

Choose Start > Settings > Control Panel > Services.

Or Type in RUN “services.msc” to open the services dialog box.

Select the service and right click on this

Select Logon select this account

Specify the username and corresponding password.

ORA-12154: TNS: could not resolve service name

When running Oracle RMAN backup, RMAN manages to connect to the target database but fails to connect to the Recovery Catalog by giving below errors:

RMAN-04004: error from recovery catalog database:
ORA-12154: TNS:could not resolve service name
Recently I receive and issue from one user, he has created a separate tablespace and user for recovery catalog and registered database in recovery catalog, but unable to connect to catalog using CONNECT CATALOG COMMAND. When he tries to connect catalog database with @database is giving error without @database he is able to connect successfully.
Cause: TNS: could not resolve the connect identifier specified

A connection to a database or other service was requested using the connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming method configured. For Example, if the type of connect identifier used was net service name then the net service name could not be found in a naming method repository or the repository cold not be located or reached.

Solution: If you are using local naming method make sure the tnsname.ora file having the correct entry there is no syntax error.

Simply try to connect your catalog database in SQL*plus
SQL> sys/*****@orcl3
or try to ping your catalog database
cmd> tnsping catalog_DB 
If it is not working, then check your tnsnames.ora for catalog database entry there must be any thing wrong entered, correct it try to tnsping again as long as it will start pinging then try to reload listener
cmd>lsnrctl
lsnrctl> reload listener

when tns entry start pinging and listener is OK then
cmd> set oracle_sid=catalog_db
cmd> rman catalog rman/rman@catalog

ORA-10567: Redo is inconsistent with data block

Starting media recovery

Media recovery failed

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

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

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

RMAN-03002: failure of recover command at 10/03/2012 20:00:52

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start

ORA-00283: recovery session canceled due to errors

ORA-00600: internal error code, arguments: [3020], [37833581], [1], [4504], [70575], [244], [], []

ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)

ORA-10564: tablespace SDH_HRMS_DBF

ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918

One fine morning (03rd October) our DBserver suddenly break down after repairing the hardware and OS we found that database needs recovery. I used the simple restore & recovery procedure. Restore database is working fine while applying “recover database” it is able to apply all the incremental backup incase of media recovery is giving the above error. Recovery interrupted with the above error:

I am not even able to open the database, while trying to open the database. It is giving the following error:

RMAN> ALTER DATABASE OPEN;

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

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

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

RMAN-03002: failure of alter db command at 10/03/2012 20:01:30

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'

I have full backup of 30-Sep and cumulative and incremental backup of each day till 03-Oct 01 AM. Disaster happens in the morning 8 am. I found all the archive logs are available on the system till the failure.

When I check the alert log the entries are following:

Completed: alter database recover datafile list

Wed Oct 03 20:00:31 2012

alter database recover datafile list

 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14

Completed: alter database recover datafile list 1 , 2 , 3 ,

Wed Oct 03 20:00:31 2012

alter database recover if needed start

Media Recovery Start

Wed Oct 03 20:00:32 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 4502 Reading mem 0

  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO01.LOG

  Mem# 1 errs 0: E:\ORACLE\ORADATA\SADHAN\REDO01B.LOG

Wed Oct 03 20:00:33 2012

Recovery of Online Redo Log: Thread 1 Group 3 Seq 4503 Reading mem 0

  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO03.LOG

  Mem# 1 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO03B.LOG

Wed Oct 03 20:00:33 2012

Recovery of Online Redo Log: Thread 1 Group 2 Seq 4504 Reading mem 0

  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO02.LOG

  Mem# 1 errs 0: E:\ORACLE\ORADATA\SADHAN\REDO02B.LOG

Wed Oct 03 20:00:47 2012

Errors in file d:\oracle\admin\sadhan\udump\sadhan_ora_5952.trc:

ORA-00600: internal error code, arguments: [3020], [37833581],[1],[4504],[70575],[244],[],[]

ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)

ORA-10564: tablespace SDH_HRMS_DBF

ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918

Wed Oct 03 20:00:52 2012

Media Recovery failed with error 600

ORA-283 signalled during: alter database recover if needed

From the error, we see this corruption is in the user tablespace not the system tablespace. So this is not an internal transaction. Thus my doubt about Oracle bug is clear. It is not an oracle bug.

Cause: After searching I came to know this is a common error when doing recovery if you are not on patched version or higher. Prior to Oracle 10g it did not record all the changes in the redo stream and when you do recovery you get this error. So, there is an inconsistency between the information stored in the redo and the information stored in a database block being recovered.

Solution: Incomplete Recovery

You can use incomplete recovery either cancel based or point in time. As I already apply the simple restore and recovery command. So I preferred to go with the cancel based recovery and finally able to open the database.

Login to SQL database will be mount phase

SQL> recover database until cancel;

Press enter as long as you reach to the missing log

SQL> alter database open resetlogs;

Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" command to the rman if open the database using sql prompt.  

While searching to Oracle support I see so many related bugs exist regarding ORA-00600 [3020].  Below solution can be used in case problem related with system tablespace or oracle bug ORA-00600 [3020]

Solution: Try to do a manual recovery with allow 1 corruption. That is "recover database allow 1 corruption;" which will skip the bad transaction.

RMAN> recover database allow 1 corruption;

RMAN> recover database allow 1 corruption;

RMAN> recover database allow 1 corruption;

We need to repeat this command until the recovery completes.

RMAN> recover database allow 1 corruption;

Starting recover at 03-OCT-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 03-OCT-12

 After trying the above command 8-9 times if you are not able to recover the database. You can use incomplete recovery point in time just before the corruption.

CONNECT RMAN TARGET SYS/*****@SADHAN.WORD CATALOG CATALOG/*****@RMAN

run {
set until time to_date(’03-Oct-2012 07:50:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

RMAN> Alter database open Resetlogs;

ORA-02082: a loopback database link must have a connection qualifier

The issue occurs when there is a db link which is a loop back database link i.e. points to itself
For Example:
HRMS@> DROP DATABASE LINK SHAAN.WORLD;
DROP DATABASE LINK SHAAN.WORLD
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

How to Fix:
Query the Global name and note down that name.
HRMS> select * from global_name;
GLOBAL_NAME
-----------------
SHAAN.WORLD
1 row selected.

Now rename this global with some other name to fix the actual issue.
HRMS@> Alter database rename global_name to SHAAN1.XXXX;
Database altered.

Now you will able to delete the database link as the global name is seperated from the DBlink
HRMS@> DROP DATABASE LINK SHAAN.WORLD;
Database link dropped.

After dropping the dblink rename back to your global name.
HRMS@> Alter database rename global_name to SHAAN.WORLD;
Database altered

ORA-02063 remote db error and how to fix it

ORA-02063: preceding string string from stringstring

Cause: an Oracle error was received from a remote database link.
Action: refer to the preceding error message(s)

ORA-02063 usually occurs when you run a transaction that tries to query or update a remote database table.
This ORA-02063 error is always proceeded  by  other ORA error messages , which are from the remote database and are root cause for the transaction failure.

The format of the message is as follows:
ORA-02063: preceding line from 

Example:
ORA-00942: table or view does not exist
ORA-02063: preceding line from TDB21.WORLD@BILLOP

Solution: Fixing ORA-02063 errors involves  analyzing the remote database errors associated with the ORA-02063  and fixing those errors.

The following  are couple of examples with fixes:

1. In teh following example error occured in Oralce 11.1.0.7
SQL> select *
2 from products_fact@exceldb1;
from products_fact@exceldb1
*
ERROR at line 2:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Excel Driver]Optional feature not implemented
ORA-02063: preceding 2 lines from EXCELDB1

The above error was fixed by adding parameter  HS_FDS_SUPPORT_STATISTICS=FALSE   in the initialization init.ora file in  11.1.0.7.

2. In the following example, local db is Oracle 9i  and remote db is Oracle 10g.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from ABC

In this case TEMP space resource issue was in remote db and  by adding more TEMP or  by tuning the sql statement  this error is resolved.

ORA-01999: password file cannot be updated in SHARED mode

 Sometimes under specific conditions you want to change the password of the Oracle database user sys. Their is however something special with the password for the sys user which might cause you to run into a ORA-01999 error. reason for this is that your database is using a REMOTE_LOGIN_PASSWORDFILE file and this file is set to SHARED. Shared means that one or more database can share the same password file which is for example used in a RAC setup. In my case it was NOT a RAC setup it was a standalone database. So if you want to change the sys password in that case you have to change the mode from SHARED to EXCLUSIVE.

NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

# EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

# SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

The below example illustrates the error:

SQL> passw sys

Changing password for sys
New password:
Retype new password:
ERROR:
ORA-01999: password file cannot be updated in SHARED mode

Now we have 2 options to do solve this depending on the fact if your database uses a spfile or a pfile setup.

When using a spfile you can do the following:

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE' scope=spfile;

When using a pfile you will have to vi the content of your pfile.

After the settings are picked-up by the database you can change your sys password.

ORA-01861

Experience to my column: i got the below error when running in rman once

  run {
  allocate channel c1 type disk;
  set until time = '2004-05-17:05:53:27';
  restore tablespace users;
  recover tablespace users;
  }

executing command: SET until clause
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 05/17/2004 18:25:50
ORA-01861: literal does not match format string

RMAN> exit

since i got the above error, i tried setting the env variable, but again there is some error... 
this is on NT
it throws error. i need to set the env bcos i had  run the script

C:\oracle\ora81b\bin>set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

C:\oracle\ora81b\bin>set NLS_LANG=american

C:\oracle\ora81b\bin>rman

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect catalog rec_cat/rec_cat@sai

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-00604: error occurred at recursive SQL level1
ORA-02248: invalid option for ALTER SESSION

RMAN> exit

Recovery Manager complete.

C:\oracle\ora81b\bin>set NLS_LANG
NLS_LANG=american

C:\oracle\ora81b\bin>set NLS_DATE_FORMAT
NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

Once i remove the single quotation from above command it is working fine. sometimes a minor mistake
makes cost of time but giving experience

set NLS_DATE_FORMAT=dd-mon-yyyy 

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

From the following solution of above issue, we can select any one as per the oracle database version.

Solution 1: You can check for held TEMP segments with this query:
SQL> select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status

from v$session a, v$sort_usage b

where a.saddr = b.session_addr

order by b.tablespace, b.segfile#, b.segblk#, b.blocks;
Use the below command in oracle version 11g to remove a TEMP segment:
SQL>alter tablespace xxxxx coalesce;
SQL>alter tablespace TEMP coalesce;
Solution 2: remove temporary space from a tablespace
Alternatively you can use drop segment event to remove temporary space from tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';
where: x is the value for file# from Tablespace.
Solution 3: Add the temp file for Temporary tablespace
SQL>SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024

 FROM DBA_TEMP_FILES;
FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
D:\ORACLE\ORADATA\RMAN\TEMP01.DBF TEMP 40
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\TEMP01.DBF’ SIZE 80M;
Tablespace altered.
SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' ‎‎'||FREE_SPACE/1024/1024 FROM DBA_TEMP_FREE_SPACE;
SQL> SELECT tablespace_name, total_blocks, used_blocks, free_blocks FROM v$sort_segment;
SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024

FROM V$temp_space_header

GROUP BY tablespace_name;
Now, resize or add the datafile related to a Tablespace if require:
SQL> ALTER TABLESPACE rman ADD DATAFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\RTBS02.DBF' SIZE 200M;

SQL> SELECT *FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%RT%';
We can resize the existing Datafile if the mount point have enough space
ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE ‎‎200M;

Solution 4: Create a new Temporary Tablespace and make this temporary tablespace as default temporary tablespace for database.
SQL> CREATE TEMPORARY TABLESPACE TEMP02

TEMPFILE ' D:\ORACLE\ORADATA\RMAN\TEMP02.DBF'

SIZE 256M REUSE AUTOEXTEND ON NEXT ‎‎256M MAXSIZE 8192M

EXTENT MANAGEMENT LOCAL;

Tablespace created.
Now make this temporary tablespace default at Database level:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered.
Bounce the database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content.

SQL> shutdown immediate;
SQL> startup;
SQL> Drop tablespace temp including contents;
Tablespace dropped.
SQL> select *from dba_temp_files;
Now, we can see the new Temporary tablespace is 'TEMP02' with enough free space.

ORA-01555 (snap shot too old)

Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed. They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.

Scenario: User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update. Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone.

ORA-01555: Snapshot too old rollback segment too small

Possible Cause:

Rollback records needed by a reader for consistent read are overwritten by other writers.

An active database with an insufficient number of small-sized rollback segments.

A rollback segment corruption that prevents a consistent read requested by the query

A fetch across commits while your cursor is open means happen when a “commit” is use in a loop or inside a procedure frequently or inside a application frequently.

Action: Do frequent commits.

If in Automatic Undo Management mode increase undo_retention setting otherwise, use larger rollback segments.