Oracle 11g Logo

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

Error log:

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'

Workaround for this error is to provide all the available archive log files to the recovery:

SQL> recover database using backup controlfile until cancel;
...
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

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;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs;
Database altered.

Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

SQL> CREATE UNDO TABLESPACE undo2 datafile '/u01/app/oracle/oradata/RTS_NEW/undo2_df1.dbf' size 200m autoextend on maxsize 30G;
Tablespace created.
SQL> alter system set undo_tablespace = undo2 scope=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.

Now bounce your database.

SQL> shutdown immediate
SQL> startup

Cheers!! Database started successfully.

Staty Tune. 🙂

Oracle 11g Logo

How to kill Oracle RMAN backup job

We can kill RMAN backup job with the help of 2 methods, as below:

Method I:
Alter system kill session:

First, get the SID and SERIAL# from below query:

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SID        SERIAL#    SPID         CLIENT_INFO
---------- ---------- ------------ ---------------------------------
592        12         865          rman channel=full_chanel

OR

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
SID        SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
592        12         1          9115569    19258880   47.33

Use the following command to kill RMAN backup job:

SQL> alter system kill session '592,12' immediate;
system altered.

Backup job killed successfully, simultaneously you will get below mentioned error log in RMAN backup logs:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/01/2017 00:09:15
RMAN-10038: database session for channel full_chanel terminated unexpectedly

Method II:

Directly kill RMAN job from OS level with the help of “kill -9”

[oracle@PR ~]$ ps -ef | grep rman|grep -v grep
oracle 2348 3124 3 01:28 pts/1 00:00:00 rman target /
[oracle@PR ~]$ kill -9 2348

Thanks, Stay Tune. 🙂

Oracle 11g Logo

Database startup failed with ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim x bytes disk space from x limit

Error log itself self explanatory, due to limit exceeded at DB_RECOVERY_FILE_DEST_SIZE database not able to open.

Error logs:

Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50111488 bytes disk space from 2307915776 limit
ARCH: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_393_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-16038: log 3 sequence# 393 cannot be archived 
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO031.LOG'
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO032.ORA'
USER (ospid: 6196): terminating the instance due to error 16038
2017-06-27 12:51:40.592000 +05:30
Instance terminated by USER, pid = 6196

Error log itself self-explanatory, due to limit exceeded at DB_RECOVERY_FILE_DEST_SIZE database not able to open.

Solution:

Increase DB_RECOVERY_FILE_DEST_SIZE parameter size, and startup database. In my case 10G is sufficient.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH

According to Oracle suggestions in ADRCI alert logs, you have following choices to clean 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.

 

Full error logs:

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
2017-06-27 12:51:31.042000 +05:30
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
Using parameter settings in server-side spfile D:\ORACLE_FILES\DB_HOME1\DATABASE\SPFILETEST.ORA
System parameters with non-default values:
processes = 150
sessions = 248
shared_pool_size = 608M
large_pool_size = 32M
java_pool_size = 160M
nls_length_semantics = "BYTE"
control_files = "D:\DATABASE\TEST\CONTROL01.CTL"
control_files = "D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL"
control_files = "D:\DATABASE\TEST\CONTROL03.CTL"
db_block_size = 8192
db_cache_size = 608M
compatible = "11.2.0.0.0"
db_recovery_file_dest = "D:\oracle_files\flash_recovery_area"
db_recovery_file_dest_size= 2201M
dml_locks = 800
undo_tablespace = "UNDOTBS1"
undo_retention = 3600
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testXDB)"
job_queue_processes = 4
audit_file_dest = "D:\ORACLE_FILES\ADMIN\TEST\ADUMP"
audit_trail = "DB"
db_name = "test"
open_cursors = 2048
optimizer_index_cost_adj = 10
query_rewrite_enabled = "FALSE"
query_rewrite_integrity = "enforced"
pga_aggregate_target = 400M
diagnostic_dest = "D:\ORACLE_FILES"
PMON started with pid=2, OS id=3156
VKTM started with pid=3, OS id=5324 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=4, OS id=6464
DIAG started with pid=5, OS id=8932
DBRM started with pid=6, OS id=3824
PSP0 started with pid=7, OS id=8204
DIA0 started with pid=8, OS id=6920
MMAN started with pid=9, OS id=6040
DBW0 started with pid=10, OS id=4948
LGWR started with pid=11, OS id=8984
CKPT started with pid=12, OS id=7280
SMON started with pid=13, OS id=7588
RECO started with pid=14, OS id=6064
MMON started with pid=15, OS id=8720
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=16, OS id=8104
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\oracle_files
ALTER DATABASE MOUNT
2017-06-27 12:51:35.391000 +05:30
Successful mount of redo thread 1, with mount id 2245910739
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=4564
2017-06-27 12:51:36.787000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARC1 started with pid=21, OS id=2748
ARC2 started with pid=22, OS id=8640
ARC1: Archival started
ARC3 started with pid=23, OS id=3032
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 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.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50094592 bytes disk space from 2307915776 limit
ARC2: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
94_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-16038: log 1 sequence# 394 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:\DATABASE\TEST\REDO011.LOG'
ORA-00312: online log 1 thread 1: 'D:\DATABASE\TEST\REDO012.ORA'
2017-06-27 12:51:37.810000 +05:30
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc0_4564.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 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.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc0_4564.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50094592 bytes disk space from 2307915776 limit
ARC0: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
94_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 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.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50111488 bytes disk space from 2307915776 limit
ARCH: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
93_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-16038: log 3 sequence# 393 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO031.LOG'
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO032.ORA'
USER (ospid: 6196): terminating the instance due to error 16038
2017-06-27 12:51:40.592000 +05:30
Instance terminated by USER, pid = 6196

Thanks, Stay Tune. 🙂

Oracle 11g Logo

shutdown database not possible due to missing of one of the controlfile

In this case, control02.ctl was deleted intentionally in order to demonstrate below oracle error.

SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/RTS/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Solution:

Change control_files parameter to existing/available control file, i.e. control01.ctl:

SQL> alter system set control_files='/u01/app/oracle/oradata/RTS/control01.ctl' scope=spfile;
System altered.

Shutdown abort your database, because oracle won’t allow database to be shutdown with normal or immediate option.

SQL> shut abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.

Now your database is opened with existing control file, and It is highly recommended to run your Oracle database with multiple control files(called control file multiplexing) in order to reduce the risk of losing control file due to corruption, accidental removal or any possible worst. How to multiplex Oracle control file?

Thanks, Stay Tune. 🙂

Oracle 11g Logo

How to multiplex Oracle control file?

It is highly recommended to run your Oracle database with multiple control files(called control file multiplexing) in order to reduce the risk of losing control file due to corruption, accidental removal or any possible worst.

Ensure all existing control file locations:

SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/RTS/control01.ctl

Update oracle database parameter CONTROL_FILES with new location and name of control file:

alter system set control_files=
'/u01/app/oracle/oradata/RTS/control01.ctl',
'/u01/app/oracle/oradata/RTS/control02.ctl'
scope=spfile;

Note: I am demonstrating above example in test environment, that’s why the path of both control files are same, it is highly recommended to multiplex control files in a different location as well as different disks.

Shutdown database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host

Copy existing control file(i.e. control01.ctl) to new location:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR]$ ll /u01/app/oracle/oradata/RTS/control02.ctl
-rw-r-----. 1 oracle oinstall 9748480 Jun 24 17:28 /u01/app/oracle/oradata/RTS/control02.ctl

Startup database:

SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/RTS/co
                                                 ntrol01.ctl, /u01/app/oracle/o
                                                 radata/RTS/control02.ctl

Cheers!
Stay Tune. 🙂

Oracle 11g Logo

ORA-00205: error in identifying control file, check alert log for more info

To demonstrate this error, I have intentionally deleted control file (i.e.control02.ctl)

And try to startup database to stimulate exact error:

SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
ORA-00205: error in identifying control file, check alert log for more info

database throws ORA-00205 error as expected due to one of the control file not in place.

Solution:

Ensure existing control file locations with following:

SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/RTS/co
                                                 ntrol01.ctl, /u01/app/oracle/o
                                                 radata/RTS/control02.ctl

There are 2 control files mentioned in CONTROL_FILES parameter, Ensure which control file is not available.

[oracle@PR]$ ll /u01/app/oracle/oradata/RTS/control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Jun 24 03:37 /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR]$ ll /u01/app/oracle/oradata/RTS/control02.ctl
ls: cannot access /u01/app/oracle/oradata/RTS/control02.ctl: No such file or directory

So, control02.ctl is not available, issue following alter command in order to edit only one control file location in CONTROL_FILES parameter.

SQL> alter system set control_files='/u01/app/oracle/oradata/RTS/control01.ctl' scope=spfile;
System altered.

Shutdown the database and start it up:

SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.

The database is opened with one control file:

SQL> show parameter control_files; 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
control_files                        string      /u01/app/oracle/oradata/RTS/control01.ctl

Cheers!!

It is highly recommended to run your Oracle database with multiple control files(called control file multiplexing) in order to reduce the risk of losing control file due to corruption, accidental removal or any possible worst. How to multiplex Oracle control file?

Thanks, Stay Tune. 🙂

Oracle 11g Logo

ORA-01555 Snapshot Too Old

This error is all about read consistency, consider a scenario:

undo_retention is set to 15min (900 seconds), you started long running query at 9am, and the query runs for next 1 hours, any DML transaction might changed rows during query runtime and old rows (i.e. Before image) will be safe in undo tablespace for next 15min. If the same changed rows are needed by long running query after 15min then the error will appear.

Solution:

Increase undo_retention to sufficiently enough value.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_retention = 3600 scope=both;
System altered.
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1

Additionally, you need to take care about undo tablespace size by considering undo_retention, In my case, i have added the additional datafile, as below:

SQL> alter tablespace UNDOTBS1 add datafile '/oradata/datafile/undotbs_02.dbf' size 100M autoextend on next 200M maxsize 31G;

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-19625: error identifying file – ORA-27037: unable to obtain file status

Error log:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/23/2017 09:00:03
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /BACKUP/arch/ifsprod/1_153555_775842324.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN backup failed with above mentioned errors, error message itself very explanatory, required archive log missing from archive log location and RMAN didn’t get it to backup. In my case, archive logs are moved to another location in order to manage bulk space for huge database activity. Fortunately, I have found out missing one, copied to the default location and backup was successful.

But, In case it was deleted or corrupted in worst, you can issue following commands in order to continue RMAN backup.

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

OR

If you are using catalog database to maintain RMAN repository instead of controlfile, you can try following command:

RMAN> resync catalog;

Run backup again.

Note: One full database RMAN backup should consider, if you are taking RMAN transactactional or incremental backup.

Oracle 11g Logo

ORA-02062: distributed recovery received DBID %s, expected %s

Error log:

2017-06-21 11:56:07.973000 +05:30
Errors in file /oracle/app/oracle/diag/rdbms/vcbcbs/vcbcbs/trace/vcbcbs_reco_5696.trc:
ORA-02062: distributed recovery received DBID f2fc225a, expected f509547a

SQL> SELECT COMMIT# ,LOCAL_TRAN_ID, STATE, MIXED, HOST, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;
COMMIT#     LOCAL_TRAN_ID     STATE         MIX HOST          GLOBAL_TRAN_ID 
----------- ----------------- ------------- --- --------      ----------------
7072124803  10.22.2551715     collecting    no  DC-EDPSTAFF1  VBPROD.f3c3de87.10.22.2551715

 

SQL> SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE FROM DBA_2PC_NEIGHBORS;
LOCAL_TRAN_ID          IN_ DATABASE                I
---------------------- --------------------------  -
10.22.2551715          in                          N
10.22.2551715          out CBL1                    N

 

As a part of the solution, issued following command in order to purge pending distributed transaction:

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('10.22.2551715');

Execute DBMS_TRANSACTION.PURGE_MIXED ('10.22.2551715');

Thanks,

Stay tune. 🙂

Oracle 11g Logo

ORA-19587: error occurred reading 512 bytes at block number 1 + ORA-27072: File I/O error

My one of the client facing below mentioned Oracle errors, On diagnosis, we come to know there are some deleted archive logs entry still exists in RMAN repository, after cleaning all the bad entries from RMAN repository(controlfile in my case), the backup was successfully completed.

Error logs:

archived log file name=/u02/oradata/flash_recovery_area/DB1/archivelog/2017_01_21/o1_mf_1_253761_d866q0o3_.arc RECID=252905 STAMP=933861305
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 05/23/2017 12:59:27
ORA-19587: error occurred reading 512 bytes at block number 1
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1

Solutions:

RMAN> delete force noprompt obsolete;
RMAN> delete force noprompt expired backup;
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL time 'SYSDATE-1';

In my case, the client needed only one-day archives. change last RMAN command as per your convenience.

Oracle 11g Logo

Datapump export job failed with ORA-01555: snapshot too old: rollback segment number x with name “_SYSSMU8$” too small

The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data when the query started.
Committed “versions” of blocks are maintained along with newer uncommitted “versions” of those blocks so that queries can access data as it existed in the database at the time of the query. These are referred to as “consistent read” blocks and are maintained using Oracle undo management.

Error logs:

ORA-31693: Table data object "RPROD"."AOUP_DAILY_STBACT_SUMM_DET" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small

As a part of the solution, set undo_retention to a higher value. also, take care of the size of undo tablespace, it should be large enough.

SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
 
SQL> ALTER SYSTEM SET UNDO_RETENTION = 1800 scope=both;
System altered.
 
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1

Export was successful after above changes.

Oracle 11g Logo

Alter database open failed with ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656]

After power failure at my customer end, database unable to open with above-mentioned oracle error message. Due to power failure controlfile got logical corruption.

This Problem is caused by Storage Problem of the Database Files. The Subsystem (eg. SAN) crashed while the Database was open. The Database then crashed because the Database Files were not accessible anymore. This caused a lost Write into the Online RedoLogs and/or causing logical corruption in controlfile so Instance Recovery is not possible and raising the ORA-600.

Error log:

2017-05-08 14:11:22.391000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc (incident=1968168):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1968168/db1_ora_6324_i1968168.trc
Aborting crash recovery due to error 600
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
2017-05-08 14:11:23.773000 +05:30
Trace dumping is performing id=[cdmp_20170508141123]

Solution:

Retrive details of all the controlfiles as below:

[oracle@db1 ~]$ sqlplus / as sysdba
SQL> Show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/oradata/db1/control01.ctl,/u02/oradata/flash_recovery_area/db1/control02.ctl

Findout current redo log member when the power got failed.

SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;
MEMBE                        RGROUP#    STATUS
---------------------------- ---------- ----------------
/u02/oradata/db1/redo03.log  3          CURRENT
SQL> Shutdown abort;
ORACLE instance shut down.

Consider backup of controlfile, so that we will keep the current state of controlfile in the case of any worst.

[oracle@db1 ~]$ cp /u02/oradata/db1/control01.ctl /u02/oradata/db1/control01.ctl_backup
[oracle@db1 ~]$ cp /u02/oradata/flash_recovery_area/db1/control02.ctl /u02/oradata/flash_recovery_area/db1/control02.ctl_backup

Startup mount database:

[oracle@db1 ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> Startup mount;
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size 2212936 bytes
Variable Size 3489663928 bytes
Database Buffers 1476395008 bytes
Redo Buffers 42414080 bytes
Database mounted.

Recover database with the help of following command and input current redo log member fetched above:

SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 130697634 generated at 05/08/2017 10:59:22 needed for thread1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2017_05_08/o1_mf_1_160296_%u_.arc
ORA-00280: change 130697634 for thread 1 is in sequence #160296
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/db1/redo03.log
Log applied.
Media recovery complete.
SQL>
SQL> Alter database open resetlogs ;

Database altered.
SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ WRITE           DB1

Cheers!! Stay Tune. 🙂

ORA-12542: TNS:address already in use – Oracle 10g 64 bit

My one of the client facing ORA-12542, this error message popped up when he tried TNSPING OR to create DB-link.
Environment:
Microsoft Windows Server 2003 – 64 bit
Oracle Database 10g Enterprise Edition Release 10.2.0.4 – 64 bit

Error:

ORA-12542: TNS:address already in use

Solution:

On server side, add multiple TCP ports in listener.ora file, I have added additional TCP port i.e.1523 to the current LISTENER.

D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
   )
 )
LOG_DIRECTORY_LISTENER = G:\Listener_Trace
LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1523))
     )
   )

And on client side, map net service names to recently added listener protocol address. i.e. 1523, as below:

NEWPASS_29 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1523))
   )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = NEWPASS) 
  )
)

after made above changes to listener.ora and tnsnames.ora, TNSPING was successful and able to create DB-link.

Note:

You may face mentioned error in following cases:

  1. New installation
  2. listener.ora file has been edited manually since the last listener restart
  3. TCP port number is duplicated across the list of ADDRESS configurations in the listener.ora file.

Cheers!!

Stay Tune. 🙂

Oracle 11g Logo

ORA-08104: this index object xxxxx is being online built or rebuilt and ORA-00031: session marked for kill

My second attempt to rebuild below mentioned index failed with “ORA-08104-this index object 22624 is being online built or rebuilt”. I am getting this oracle error because my first attempt to rebuild same index failed due to abnormal session termination, it was incomplete rebuild.

Error log:

SQL> ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS;
ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS
*
ERROR at line 1:
ORA-08104: this index object 22624 is being online built or rebuilt

According to oracle support(Doc ID 375856.1) we can rid out of the issue by cleaning garbage with the help of dbms_repair.online_index_clean function. But no luck after successful execution of dbms_repair.online_index_clean function.

declare
lv_ret BOOLEAN;
begin
lv_ret := dbms_repair.online_index_clean(22624);
end;
/
PL/SQL procedure successfully completed.

Same clean-up logs popped up in ADRCI with same object no(i.e.22624) but no luck.

2016-09-27 13:21:06.373000 +05:30
online index (re)build cleanup: objn=22624 maxretry=2000 forever=0

Even, I tried dropping index and mark as unusable but unlucky again.

SQL> drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME;
drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

So, I have decided to kill that session with the help of “ALTER SYSTEM KILL SESSION”, I got require details with the help of following SQL:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, 
S.LOGON_TIME, SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID 
AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS
AND O.OBJECT_NAME = 'AOUP_TRANS_LOG';

After 60 seconds of waiting, SQL command ended up with “ORA-00031: session marked for kill” oracle error. 🙁

SQL> ALTER SYSTEM KILL SESSION '3423,39597';
ALTER SYSTEM KILL SESSION '3423,39597'
*
ERROR at line 1:
ORA-00031: session marked for kill

Finally, I got the solution by killing process at OS level:
kill -9 spid     (from above)

Thank god, Index rebuild was successful.

Object flag before killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2562

Object flag after killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2050

Have a great time ahead.

Stay Tune. 🙂

Oracle 11g Logo

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

My one of the client reported me ORA-03113 oracle error message. He is facing problem while starting up database. Usually this error occurs when connection between Client and Server process was broken OR It can be any big problem.

SQL> startup
ORACLE instance started.
Total System Global Area 8584982528 bytes
Fixed Size 2262088 bytes
Variable Size 4462742456 bytes
Database Buffers 4093640704 bytes
Redo Buffers 26337280 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

On further diagnosis of ADRCI logs, I found disk space size assigned to Flash recovery area falling short. ADRCI logs are as follows:

2016-09-12 12:46:00.960000 +05:30
Successful mount of redo thread 1, with mount id 3346245764
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=564
2016-09-12 12:46:02.333000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_2588.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 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 'D:\APP\ADMINISTRATOR\FAST_RECOVE
RY_AREA\MNSBDB\ARCHIVELOG\2016_09_12\O1_MF_1_454_%U_.ARC'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_25
88.trc:
ORA-16038: log 8 sequence# 454 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 8 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\MNSBDB\REDO08.LO
G'
USER (ospid: 2588): terminating the instance due to error 16038
ARC0: STARTING ARCH PROCESSES
2016-09-12 12:46:03.347000 +05:30
Logins disabled; aborting ARCH process startup (1092)
ARC0: Archival disabled due to shutdown: 1092
Shutting down archive processes
Archiving is disabled
System state dump requested by (instance=1, osid=2588), summary=[abnormal instan
ce termination].
System State dumped to trace file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\
trace\mnsbdb_diag_4064.trc
Dumping diagnostic data in directory=[cdmp_20160912124603], requested by (instan
ce=1, osid=2588), summary=[abnormal instance termination].
2016-09-12 12:46:05.266000 +05:30
Instance terminated by USER, pid = 2588

So, I finally assigned more size to Flash recovery area using following command, and database opened successfully.

 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 8584982528 bytes
 Fixed Size 2262088 bytes
 Variable Size 4462742456 bytes
 Database Buffers 4093640704 bytes
 Redo Buffers 26337280 bytes
 Database mounted.
SQL> alter system set db_recovery_file_dest_size=100G scope=both;
SQL> alter database open;

I strongly recommend you to monitor your every database move from ADRCI, this will easy for your to understand problem area and act accordingly.

Your comments highly appreciated.

Stay Tune. 🙂

Oracle 11g Logo

RMAN-06900 RMAN-06901 ORA-19921

Received RMAN-06900,RMAN-06901 and ORA-19921 while logged in to RMAN prompt to verify daily backups. (OS:Redhat linux 6.5)

WARNING message:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:34:39 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
RMAN>

On further diagnosing and suggested by OERR utility, I have realized there is one old active session from Aug 23, as mentioned below.

[oracle@primary ~]$ oerr ORA 19921
19921, 00000, "maximum number of %s rows exceeded"
// *Cause: The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
// table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

So lets find out old session with ps -ef:

[oracle@primary logs]$ ps -ef|grep rman
oracle 543 32693 0 10:50 pts/4 00:00:00 grep rman
oracle 19783 19782 47 Aug23 ? 1-07:13:42 rman target /

There is one old active session above, OS ID: 19783, since 23 aug.

[oracle@primary ~]$ date
Fri Aug 26 10:57:13 IST 2016
[oracle@primary logs]$ kill -9 19783

After successfully killed old session, we are no more facing mentioned list of warnings, as below:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:50:54 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN>

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-00600: internal error code, arguments:[4194] ORA-00603: ORACLE server session terminated by fatal error. PMON terminating the instance due to error 472

Oracle instance terminated immediate after instance startup due to following mentioned oracle error:

Cause:
A mismatch has been detected between Redo records and rollback (Undo) records.
We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. This error may indicate a rollback segment corruption.
Note: This may require a recovery from a database backup depending on the situation.

Adrci logs are as follows:

Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc (incident=1014790):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014790/db1_smon_11168_i1014790.trc
2016-06-30 11:13:35.355000 +05:30
Starting background process QMNC
QMNC started with pid=25, OS id=11249
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Completed: ALTER DATABASE OPEN
2016-06-30 11:13:36.558000 +05:30
db_recovery_file_dest_size of 163840 MB is 1.12% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery stopped at EOT rba 13618.106.16
Block recovery completed at rba 13618.106.16, scn 0.3980712368
Doing block recovery for file 3 block 160
Resuming block recovery (PMON) for file 3 block 160
Block recovery from logseq 13618, block 56 to scn 3980712329
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.59.16, scn 0.3980712330
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc:
ORA-01595: error freeing extent (10) of rollback segment (3))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Starting background process SMCO
SMCO started with pid=29, OS id=11268
Trace dumping is performing id=[cdmp_20160630111337]
2016-06-30 11:13:37.819000 +05:30
Starting background process CJQ0
CJQ0 started with pid=28, OS id=11276
2016-06-30 11:13:41.579000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014942):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014942/db1_j001_11285_i1014942.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j004_11291.trc:
2016-06-30 11:13:43.111000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc (incident=1014934):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014934/db1_j000_11283_i1014934.trc
Trace dumping is performing id=[cdmp_20160630111343]
2016-06-30 11:13:45.937000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014943):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014943/db1_j001_11285_i1014943.trc
2016-06-30 11:13:46.903000 +05:30
Trace dumping is performing id=[cdmp_20160630111346]
2016-06-30 11:13:48.279000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111349]
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111351]
2016-06-30 11:13:53.489000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc (incident=1014702):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014702/db1_pmon_11144_i1014702.trc
2016-06-30 11:13:55.766000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 11144): terminating the instance due to error 472
Instance terminated by PMON, pid = 11144

Workaround:
Startup database in mount mode:

SQL> startup mount;

Check current undo tablespace:

SQL> show parameter undo_tablespace;

Alter undo management to ‘MANUAL’:

SQL> alter system set undo_management='MANUAL' scope=spfile;

Bounce database to open mode, meanwhile please verify adrci logs, if there is problem.

SQL> shut immediate;
 SQL> startup;

Create new undo tablespace if everything is fine in adrci logs:

SQL> create undo tablespace newundotbs datafile '/u02/oradata/db1/newundotbs01.dbf' size 5G autoextend on next 300M maxsize 31G;

Change default undo tablespace to new one:

SQL> alter system set undo_tablespace='NEWUNDOTBS' scope=spfile;

Alter undo management to ‘AUTO’:

SQL> alter system set undo_management='AUTO' scope=spfile;

Bounce the database:

SQL> shut immediate;
SQL> startup;

Verify current undo tablespace:

SQL> show parameter undo_tablespace

done.

Thank you.
Stay Tune. 🙂

Oracle 11g Logo

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS and KUPW$WORKER.DISPATCH_WO ORA-01187: cannot read from file because it failed verification tests

Data pump export backup failed due to below mentioned oracle errors. It was failed due to verification tests failed on temp file: temp01. As a part of solution, I have delete old temporary tablespace and added new temporary tablespace with new temp file.

Data Pump Export Error Logs:

Export: Release 11.2.0.1.0 - Production on Mon Jun 20 11:47:01 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_09": system/******** directory=data_pump_bkup dumpfile=full_db_export.dmp logfile=export.log full=y
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS []
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WO
ORA-01187: cannot read from file because it failed verification te
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_09" stopped due to fatal error at 11:

Solution:

Here is article on Delete old temporary tablespace and add new temporary tablespace with new temp file.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

ORA-25153: “Temporary Tablespace is Empty” although temporary tablespace and tempfile is available

According to oracle docs, the cause of the oracle error is to attempt was made to use space in a temporary tablespace with no files(datafile). And based on that action suggested is to add tempfile to the temporary tablespace with ADD TEMPFILE command.

In my case, Temporary tablespace already exists with sufficient free space into it. So I verified DEFAULT temporary tablespace, It was set to invalid temporary tablespace name.

The issue had resolved by setting default temporary tablespace to valid one.

Following query will help you find out default temporary tablespace:

SQL> select property_name, property_value from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

Following query will help you to set default temporary tablespace:

SQL> alter database default temporary tablespace NEWTEMP;

Thanks, Comments are highly appreciated.
Stay Tune. 🙂

Oracle 11g Logo

RMAN-06026: some targets not found – aborting restore, RMAN-06023: no backup or copy of datafile X found to restore

RMAN restore would failed with RMAN-06026 and RMAN-06023 after successfully restoration of controlfile. Logs are as follows:

Error log:

RMAN> restore database;
Starting restore at 15-FEB-16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2016 14:52:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

As per oracle error RMAN-06023 means RMAN cannot find a backup for that datafile in its repository, so I cross verified RMAN backup files physically on disk as well as into RMAN repository with following RMAN commands. The backups are marked as AVAILABLE. Everything was pretty perfect.

RMAN> list backup of database summary;

AND

RMAN> list backup of datafile 1;
RMAN> list backup of datafile 2;
RMAN> list backup of datafile 3;
RMAN> list backup of datafile 4;

OR

RMAN> list backup of archivelog sequence;

After drilled down everything, I came to know that there are some backup files in FRA that are belongs to OLD INCARNATION than available backup CURRENT INCARNATION.
The problem is here, while restoring RMAN backups, RMAN implicitly perform cross verification to FRA(If FRA defined in controlfile) and if new files encounter it will be catalog(registered) automatically. This prevents database from restoring backups that belong to old CURRENT incarnation. Resulting RMAN would not allowed to restored this case. Backup incarnation and the CURRENT incarnation in controlfile should be same.

There are 3 Solutions to this error:

First: You can move old RMAN backup files(that belongs to old incarnation) to outside FRA and begin your restoration again with restoration of controlfile.

 

Second: Disabled FRA for the temporary purpose while RMAN backup being restore.

SQL> shutdown immediate;
SQL> create pfile from spfile;
comment *.db_recovery_file_dest and *.db_recovery_file_dest_size in pfile.
SQL> startup nomount pfile='.... init.ora'

Restart the restore controlfile and then restore/recover database commands…

 

Third: Resetting incarnation to the older incarnation, as below. In my case, following work around works like a charm.

RMAN> reset database to incarnation 3;
database reset to incarnation 3

After resetting database incarnation, restore database successful.

Folks, Your valuable comments are highly appreciated.

Thank you,
Stay Tune. 🙂