Oracle 11g Logo

ORA-01580: error creating control backup file xxx, ORA-27040: file create error, unable to create file

After migrating server from old hardware to new hardware, there is slight changes in backup NAS drive name, due to same issue Oracle continuously writes “ORA-01580: error creating control backup file” oracle error to log file (Note: Thanks to adrci), after changing path of the snapshot control file, issue get resolved completely.

Error logs:

2017-11-23 12:37:25.136000 +05:30
Errors in file /oracle/app/oracle/diag/rdbms/vcbcbs/vcbcbs/trace/vcbcbs_m000_104616.trc:
ORA-01580: error creating control backup file /cbsrmanbackup/rman/snapcf_vcbcbs.f
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
Errors in file /oracle/app/oracle/diag/rdbms/vcbcbs/vcbcbs/trace/vcbcbs_m000_104616.trc:

Solution:

$ rman target /

The old parameter value, RMAN “show all” command output:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cbsrmanbackup/rman/snapcf_vcbcbs.f';

changed to the new backup path:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/rmanBackup/rman/snapcf_vcbcbs.f';

Thanks,
Stay Tune 🙂

Oracle 11g Logo

DBVisit Physical Standby Synchronization Stopped due to partial archivelog transferred to Disaster Recover site

While troubleshooting the problem, I was trying to apply mentioned archive log through DBVisit and every time below mentioned error message popped up, I have cross checked mentioned archive log, and it was there on required location. By checking its size, i came to know it was partially transferred, also internet service provider confirmed there were huge fluctuations in bandwidth.

DBVisit error log

Dbvisit Standby Database Technology message from DR
Message received from process: dbvisit ifsprod 
(Dbvisit Standby: 7.0.48.15006 Process id: 16216)
201711051325 - File (/ifs/oracle/dbvarchive/1_191106_775842324.arc.gz)
does not exist or is empty. Please check space and file permissions or, else please contact Dbvisit support.
Dbvisit Standby terminated.
Return code = 751
(Tracefile required if contacting Dbvisit Standby support:
/usr/local/dbvisit/standby/trace/16216_dbvisit_ifsprod_201711051325.trc
(server:DR))

Solution:

As a part of a solution, manually I have transferred above-mentioned archive log file from Primary database server to Standby database server, and database started applying archivelog successfully.

Thanks, Stay Tune. 🙂

Oracle 11g Logo

SMON: Restarting fast_start parallel rollback

My one of the client reported me database hang and slow response on update/insert queries and ERP almost stopped, on diagnosis, I came to know huge transaction report have killed before commit. So database was busy and consuming almost CPU count for recovering all uncommitted transactions.

What is Fast Start Parallel Rollback?

In fast-start parallel rollback, the background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes.

Fast start parallel rollback is mainly useful when a system has transactions that run a long time before a commit, especially parallel Inserts, Updates, Deletes operations. When SMON discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes.

There are cases where parallel transaction recovery is not as fast as serial transaction recovery because the PQ slaves may interfere with each others work by contending for the same resource. With such a transaction rollback performance may be worse in parallel when compared to a serial rollback.

Because of this contention and the perceived slowness and ‘hang’ like symptoms (the database may seem to hang, SMON and parallel query slaves may be seen to take all the available CPU), DBA intervention may be taken. If a large transaction is terminated, the cleanup may also take time.

Solution

Disable parallel roll back by setting “FAST_START_PARALLEL_ROLLBACK” parameter to FALSE:

SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE scope=both;

FAST_START_PARALLEL_ROLLBACK:

This parameter is used to specifies the degree of parallelism in order to recover terminated transactions.

Possible values:

FALSE – Parallel rollback is disabled
LOW – Limits the maximum degree of parallelism to 2 * CPU_COUNT
HIGH – Limits the maximum degree of parallelism to 4 * CPU_COUNT

Note: In case of above parameter change then transaction recovery will stop and start with new degree of parallelism

Stay Tune. 🙂

Oracle 11g Logo

Restore Oracle hot backup

Consider any worst database scenario of your life, let say we got HDD corruption and some online datafile got corrupted and not accessible due to any reason. Now we have to restore database from hot backup, as follows:

Step 1>>

Copy back all the online datafile from backup location to the actual datafile location:

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/users01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/undotbs01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/sysaux01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

Copy back control file to all the controlfile locations, refer controlfile locations from parameter file, i.e. initRTS.ora file.

cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control01.ctl
cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control02.ctl

Step 3>>

Copy back pfile or spfile file to its location:

cp /u01/bkup/manual_online_hot/initRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
cp /u01/bkup/manual_online_hot/spfileRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step 4>>

Startup database in mount mode:

SQL> startup mount;
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.

Step 5>>

Now, recover database with the help of following command and specify log: AUTO

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1117103 generated at 07/12/2017 04:39:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_90_910140016.dbf
ORA-00280: change 1117103 for thread 1 is in sequence #90
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

Step 6>>

Open database with resetlogs:

SQL> alter database open resetlogs;

Cheers!! Hot backup restored and recovered successfully.

If you are facing following error then kindly visit my blog for the solution: ORA-01194: file 1 needs more recovery to be consistent

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'

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. 🙂

Oracle 11g Logo

Query to find out progress of transaction recovery by SMON

Have you killed the long running query?

SMON is solely responsible for recovering transactions when you kill any large running query( truncate and delete ) by killing OS process or aborting database, SMON will take all possible CPU to rolling back previous state and its highly time-consuming task.
And frustrated DBA/Person will think bouncing database will resolve this problem completely, as obvious “shutdown immediate” will take equal amount of time to rollback and finally database being “aborted” by the user.
Kindly note: Shutting down or aborting database is not the solution, and won’t reduce the amount of work SMON need to be performed to complete rollback.

Following query will help you to identify the total amount of work to be rolled back by SMON, simply progress of transaction recovery. Run it multiple time.

SQL> SELECT usn, state, undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions;

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       112623     4658       107965     09-MAR-2017 17:30:16
 737       RECOVERING       275333     1755       273578     09-MAR-2017 21:49:03

<<After some time>>

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       28647      9324       19323      09-MAR-2017 17:16:50
 737       RECOVERING       240190     2792       237398     09-MAR-2017 20:33:12

Note:

On the same time, your ADRCI database log will be flooded with following:

2017-03-09 17:17:25.063000 +05:30
SMON: Restarting fast_start parallel rollback

You can improve database performance while the transactional recovery is in progress, check here.

Thanks, Stay tune. 🙂

Oracle 11g Logo

Generate AWR – Automatic Workload Repository (AWR) in oracle 11g

Many times DBA need to monitor specific loaded/heavy database related activity in order to fix database performance, for that generating AWR report is perfect solution and he will get all necessary information he needed to tune the database. This article is step by step approach to generate AWR report.

Step-I:

Before loaded activity, issue following command to take snapshot of database as start point of AWR report.

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Step-II: Perform your activity.

Perform your activity.

Step-III: Right after the activity, issue

Right after the activity, issue following command to take again snapshot of database as end point of AWR report.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Note: Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Step-IV:

Generate AWR report: (Follow the instructions and provide input acrrodingly)

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Select the appropriate instance:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
4119037639 1 PRODDB ifsprod localhost.lo
caldomain
4119037639 1 UATDB ifsuat localhost.lo
caldomain
* 4119037639 1 PRODDB ifsprod PR
Enter value for dbid: 4119037639
Enter value for inst_num: 1
Using 1 for instance number

Specify the number of days of snapshots to choose from:

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ifsprod IFSPROD 66276 26 Nov 2016 00:00 1
66277 26 Nov 2016 00:30 1
66278 26 Nov 2016 01:01 1
66279 26 Nov 2016 01:30 1
66280 26 Nov 2016 02:00 1
66281 26 Nov 2016 02:30 1
66282 26 Nov 2016 03:00 1
66283 26 Nov 2016 03:30 1
66284 26 Nov 2016 04:00 1
66285 26 Nov 2016 04:30 1
66286 26 Nov 2016 05:00 1
66287 26 Nov 2016 05:30 1
66288 26 Nov 2016 06:00 1
66289 26 Nov 2016 06:31 1
66290 26 Nov 2016 07:00 1
66291 26 Nov 2016 07:30 1
66292 26 Nov 2016 08:00 1
66293 26 Nov 2016 08:30 1
66294 26 Nov 2016 09:00 1
66295 26 Nov 2016 09:30 1
66296 26 Nov 2016 10:00 1
66297 26 Nov 2016 10:30 1
66298 26 Nov 2016 11:00 1
66299 26 Nov 2016 11:30 1
66300 26 Nov 2016 12:00 1
66301 26 Nov 2016 12:30 1
66302 26 Nov 2016 13:00 1
66303 26 Nov 2016 13:30 1
66304 26 Nov 2016 14:00 1
66305 26 Nov 2016 14:30 1
66306 26 Nov 2016 14:41 1
66307 26 Nov 2016 14:45 1

Specify the Start point and end point for the snapshot, input from above:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66306
Begin Snapshot Id specified: 66306
Enter value for end_snap: 66307
End Snapshot Id specified: 66307

Name the report:

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_66306_66307.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: AWR_between_2_manual_snapshot
.
..
...
Report written to AWR_between_2_manual_snapshot
SQL> exit

search for html format snapshot in current directory.

Thanks. Stay Tune. 🙂

Oracle 11g Logo

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

My one of the client database backup have been failed since one week due to “RMAN-06183 datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE”. Error itself is pretty explanatory. The size of the system01.dbf datafile had exceeded the RMAN parameter MAXSETSIZE, i.e. 15G

Error log:
RMAN>
Starting backup at 2016-11-03 10:41:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1008 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2016-11-03 10:41:41
channel ORA_DISK_1: finished piece 1 at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/backupset/2016_11_03/o1_mf_ncnnf_TAG20161103T104140_d1okrg13_.bkp tag=TAG20161103T104140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2016-11-03 10:41:43
Starting Control File and SPFILE Autobackup at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/autobackup/2016_11_03/o1_mf_s_926937704_d1okrjqq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-03 10:41:45
Starting backup at 2016-11-03 10:41:49
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/03/2016 10:41:49
RMAN-06183: datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE
Recovery Manager complete.

RMAN parameters:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name VCBCBS are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO 15 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cbsrmanbackup/rman/snapcf_vcbcbs.f';

Confirm current size of the system01.dbf, as below, used size is 15336.1 which is greater then 15G.

RMANTBS /vcboradata/oradata/datafile/rmantbs.dbf 1000 70.9 929.06
SYSAUX /vcboradata/oradata/datafile/sysaux01.dbf 1400 880.3 519.75
SYSTEM /vcboradata/oradata/datafile/system01.dbf 15400 15336.1 63.94
UNDOTBS1 /vcboradata/oradata/datafile/undotbs01.dbf 4725 97.3 4627.75
USERS /vcboradata/oradata/datafile/users01.dbf 50 15.9 34.13

Workaround:
Increase the size of MAXPIECESIZE RMAN parameter from 15G to 33G. (You can increase it as per your convenience)
More on MAXPIECESIZE:
This parameter is use to set limits on the size of backup pieces. You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets.

RMAN> CONFIGURE MAXSETSIZE TO 33G;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 15 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 33 G;
new RMAN configuration parameters are successfully stored

After above changes, backup successfully completed.

Have a nice time ahead.

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

How to drop database manually, without using Database Configuration Assistant(DBCA)

Before proceed for entire database deletion, kindly consider full database backup for your future reference, if required.

Connect to target database with sys user, and issue following query to check database mode:

[oracle@testserver ~]$ sqlplus / as sysdba
SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
READ WRITE           BLADE2

Normally shutdown your database:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

startup your database in mount mode with exclusive restrict, as follows:

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 1.0088E+10 bytes
Fixed Size 2215984 bytes
Variable Size 5704257488 bytes
Database Buffers 4362076160 bytes
Redo Buffers 19640320 bytes
Database mounted.

Database is in mount mode:

SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
MOUNTED              BLADE2

Issue following SQL command in order to drop database, this will drop entire database with datafiles, control files and log files,etc.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Database dropped successfully.

Thank you. Stay Tune. 🙂