Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 167,494
  • Archives

  • ŗ•ź

  • Categories

  • Subscribe

  • Advertisements
  • Interested in Database Performance Tuning ?

    Learn Oracle Performance Tuning from experts

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘Errors’

runInstaller Error: An unexpected error has been detected by HotSpot Virtual Machine

Posted by FatDBA on January 6, 2016

Hello Everyone,
Installing your Oracle Software using GUI Method requires to call “runInstaller” script and is always an easy step if you have proper permissions, DISPLAY settings in place.
But here i would like to discuss one of the case where I’ve spent several hours to fix one of the error that occurred every-time i called runInstaller script even after setting all required permissions and DISPLAY variables.

It fails to render the installer and creates a log file under /tmp directory with below mentioned contents.

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0x0000003e2ce14d70, pid=4000, tid=140717162321680
#
# Java VM: Java HotSpot(TM) 64-Bit Server VM (1.5.0_51-b10 mixed mode)
# Problematic frame:
# C [ld-linux-x86-64.so.2+0x14d70]
#

————— T H R E A D —————

Current thread (0x000000004220d3f0): JavaThread “AWT-EventQueue-0” [_thread_in_native, id=4014]

siginfo:si_signo=11, si_errno=0, si_code=128, si_addr=0x0000000000000000

Registers:
RAX=0x0000000000000001, RBX=0x000000004216ae50, RCX=0x000000009eba2203, RDX=0x000000000fabfbff
RSP=0x00007ffb44792278, RBP=0x00007ffb447923c0, RSI=0x0000000000000000, RDI=0x0000000000000058
R8 =0x0000000000000000, R9 =0x0000000000000000, R10=0x00007ffb447921f0, R11=0x000000004216ae50
R12=0x00007ffb447923e8, R13=0x0000000041f85330, R14=0x0000000000000000, R15=0x0000000000000000
RIP=0x0000003e2ce14d70, EFL=0x0000000000010202, CSGSFS=0x0000000000000033, ERR=0x0000000000000000
TRAPNO=0x000000000000000d

Top of Stack: (sp=0x00007ffb44792278)
0x00007ffb44792278: 0000003e2ce0aaea 0000000000000000

Signal Handlers:
SIGSEGV: [libjvm.so+0x67ed60], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGBUS: [libjvm.so+0x67ed60], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGFPE: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGPIPE: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGILL: [libjvm.so+0x582020], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGUSR1: SIG_DFL, sa_mask[0]=0x00000000, sa_flags=0x00000000
SIGUSR2: [libjvm.so+0x583ed0], sa_mask[0]=0x00000000, sa_flags=0x14000004
SIGHUP: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGINT: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGQUIT: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004
SIGTERM: [libjvm.so+0x5839a0], sa_mask[0]=0x7ffbfeff, sa_flags=0x14000004

————— S Y S T E M —————

OS:Red Hat Enterprise Linux Server release 6.0 (Santiago)

uname:Linux 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 06:42:08 PST 2015 x86_64
libc:glibc 2.12 NPTL 2.12
rlimit: STACK 10240k, CORE 0k, NPROC 16384, NOFILE 65536, AS infinity
load average:0.09 0.06 0.08

CPU:total 1 em64t

Memory: 4k page, physical 2046684k(69828k free), swap 2031612k(2031612k free)

vm_info: Java HotSpot(TM) 64-Bit Server VM (1.5.0_51-b10) for linux-amd64, built on Jun 6 2013 09:59:46 by java_re with gcc 3.2.2 (SuSE Linux)

time: Sat Jan 2 23:09:21 2016
elapsed time: 2 seconds

The workaround to the problem is to set “LD_BIND_NOW” environment variable to a value “1” as shown below and re-launch the installer.

bash-4.1$ export LD_BIND_NOW=1
bash-4.1$ ./runInstaller
Starting Oracle Universal Installer…

Checking Temp space: must be greater than 120 MB. Actual 27339 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4031 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-07-01_03-29-40AM. Please wait …
bash-4.1$

This bug seems to have reported on 11.2.0.1 & 11.2.0.3.

Hope That Helps
Prashant Dixit

Advertisements

Posted in Advanced, Basics | Tagged: , | Leave a Comment »

Opatch Failed error code 73: OUI-67073: UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables”

Posted by FatDBA on March 2, 2015

Issue:
Upgrade error from 11.2.0.2 to 11.2.0.4

Error Description:
Oracle SPU / CPU patch deployment using Opatch filed with following error message.
Following executables are active :
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
Log file location: /u01/app/oracle/product/11.2.0.2/home/cfgtoollogs/opatch/opatch2014-9-14_12-10-00PM.log

OPatch failed with error code 73

Cause:
There are some files which are locked or some processes still running while applying patch . those should be avoided

Full Error in log:

[Mar 1, 2015 4:19:20 PM] Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1 at Fri Nov 22 14:10:20 CET 2014
[Mar 1, 2015 4:19:20 PM] Following executables are active:
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
[Mar 1, 2015 4:19:20 PM] Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:
Following executables are active:
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
[Mar 1, 2015 4:19:20 PM] OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
[Mar 1, 2015 4:19:20 PM] Finishing UtilSession at Fri Nov 22 14:10:20 CET 2014

Solution Description
==================================

This error is simple. Firstly make sure DB and listener are down .

Solution 1:
some processes are still in running .to find out them try
ps -ef|grep db_name
then kill each process by using kill -9 1196 command

Solution 2:
check which process is locking this library file using below command and kill it:

$ /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1: 1196m 2215m

$ kill -9 1196

now run opatch apply and it will run without any issues this time.


Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Using _ALLOW_RESETLOGS_CORRUPTION in case of corruption: How to recover & open the database ?

Posted by FatDBA on March 2, 2015

Recently while opening a TEST RAC database we found the database down and was not available, we tried to start the database but received communication errors and left us clueless. Due to some urgent POC activity scheduled on the database we started investigating the root cause right away for error.
ORA-03113: end-of-file on communication channel

[oracle@testdbdixit ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 26 01:04:23 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.
SQL> startup
ORA-03113: end-of-file on communication channel

We tried to open the database in MOUNT mode and it reached to phase easily without any error.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

But the recovery of any kind got failed because of the database running in No Archivelog Mode & being a test instance there is also not any RMAN backups configured as well … #TotalDisaster ūüė¶ ūüė¶

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL
*
ERROR at line 1:
ORA-00279: change 7311130 generated at 02/25/2015 22:00:18 needed for thread 2
ORA-00289: suggestion : +FRA
ORA-15173: entry ‘ARCHIVELOG’ does not exist in directory ‘DIXITDB’
ORA-00280: change 7311130 for thread 2 is in sequence #207

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 233
Current log sequence 234

When tried to start the cancel based incomplete recovery, it went successful but received few inconsistency errors for system datafile if we try to open the database in RESETLOGS mode.

SQL> ALTER DATABASE RECOVER CANCEL;
ALTER DATABASE RECOVER CANCEL
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘+DATA/DIXITDB/datafile/system.256.871197447’

As expected RESETLOGS mode failed too.

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: ‘+DATA/DIXITDB/datafile/system.256.871197447’

Resolution:
====================

*Note: Underscore (hidden/undocumented) parameters should only be used after consent with Oracle Support and should always tried and tested in sandbox environments before applying them in prod environment.

There is a hidden parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE which will allow us to open database even though it’s not properly recovered.
ALTER SYSTEM SET “_allow_resetlogs_corruption”= TRUE SCOPE = SPFILE;
Tip: Also change the undo_management to ‚ÄúManual‚ÄĚ

After the two changes in the spfile you can open the database with:

sqlplus “/ as sysdba”
startup force

Note: Well there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.

Previous Settings:
SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> alter system set undo_management=manual scope=spfile;
System altered.

SQL> ALTER SYSTEM SET “_allow_resetlogs_corruption”= TRUE SCOPE = SPFILE;
System altered.

SQL> shut immediate;

And after setting all the requisite parameters to defined values, we finally saw that ‘Database Opened’ message on the SQL prompt … ūüôā ūüôā

SQL> startup force;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.
Database opened.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 2

SQL> alter system switch logfile;
System altered.

And a new incarnation of the database happened after opening database in RESETLOGS mode.

SQL> select INCARNATION#,RESETLOGS_TIME,STATUS, RESETLOGS_ID from v$database_incarnation;

INCARNATION# RESETLOGS STATUS RESETLOGS_ID
———— ——— ——- ————
1 24-AUG-13 PARENT 824297850
2 09-FEB-15 PARENT 871197521
3 26-FEB-15 CURRENT 872646322

In Short:
=====================
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to ‚Äúmanual‚ÄĚ in init.ora file.
6) startup database
7) Create new undo tablespace (There is a strict need of changing the UNDO_MANAGEMENT to AUTO in Prod databases)
change undo_management to ‚ÄúAUTO‚ÄĚ and undo_tablespace to ‚ÄúNewTablespace‚ÄĚ
9) Bounce database.

Hope That Helps!
Prashant Dixit

Posted in Uncategorized | Tagged: , | 4 Comments »

Large Pages & use_large_pages parameter in Oracle — BMC Patrol Alerts global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”

Posted by FatDBA on December 31, 2014

All of sudden one morning we started constant alerts from one of our production system used for SAP and other  BI/BO applications which reads about Memory Used Percentage threshold breached. Being one of the very rarely used and least bust production system out of all, this was never expected from the server. I have previously written about the benefits and usage of large pages in Linux.

Summary: prashantdb053 global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”.¬† 95 <= 95.03 <= 100
Notes: prashantdb053 global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”.¬† 95 <= 95.03 <= 100 Object=MEMORY Object Class=MEMORY Parameter=MEMUsedMemPerc
BMC Impact Manager Initiated Incident

The database I have running doesn’t use large pages, as shown in the alert.log:
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 16 MB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total Shared Global Region size is 2514 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 1257 2048 KB Large Pages (2514 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************

Load statistics on the server at the time when we got the error message.

top Р07:26:24 up 30 days, 34 min,  3 users,  load average: 0.07, 0.05, 0.01
Tasks: 183 total,   1 running, 182 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.1%sy,  0.0%ni, 99.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3913512k total,  3886112k used,    27400k free,   149432k buffers
Swap:  4947960k total,   284392k used,  4663568k free,  3036064k cached

Memory Statistics on the server.

[oracle@prashantdb053 /backup/dixit/scripts]# free -m
total       used       free     shared    buffers     cached
Mem:          3821       3795         26          0        146       2965
-/+ buffers/cache:        684       3137
Swap:         4831        277       4554

[oracle@prashantdb053 /backup/dixit/scripts]# cat /proc/meminfo
MemTotal:      3913512 kB
MemFree:         26504 kB
Buffers:        149548 kB
Cached:        3036664 kB
SwapCached:       4620 kB
Active:        2250272 kB
Inactive:      1377312 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      3913512 kB
LowFree:         26504 kB
SwapTotal:     4947960 kB
SwapFree:      4663568 kB
Dirty:             132 kB
Writeback:           0 kB
AnonPages:      437440 kB
Mapped:        1668000 kB
Slab:            86592 kB
PageTables:     143008 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   6904716 kB
Committed_AS:  4125228 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    265480 kB
VmallocChunk: 34359472519 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Below stats shows that Large Page are not utlized or used.
[oracle@prashantdb053 /backup/dixit/scripts]# grep Huge /proc/meminfo
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

[oracle@prashantdb053 /backup/dixit/scripts]# vmstat -s
3913512  total memory
3884496  used memory
2248032  active memory
1376892  inactive memory
29016  free memory
149560  buffer memory
3036676  swap cache
4947960  total swap
284392  used swap
4663568  free swap
4399893 non-nice user cpu ticks
123135 nice user cpu ticks
1173193 system cpu ticks
497863320 idle cpu ticks
9827196 IO-wait cpu ticks
156731 IRQ cpu ticks
337956 softirq cpu ticks
0 stolen cpu ticks
14560315 pages paged in
286499680 pages paged out
1086328 pages swapped in
1030486 pages swapped out
2808993403 interrupts
2839798366 CPU context switches
1417413111 boot time
333424 forks

As you can see the parameter is static and requires an instance restart, so this is what I did next. Here is an interesting side effect of setting the parameter to ‚Äúauto‚ÄĚ: it doesn‚Äôt have an effect if you didn‚Äôt prepare the system for use of large pages in /etc/security/limits.conf.

07:33:48 SQL> select value,isdefault from V$PARAMETER_VALID_VALUES where name = ‘use_large_pages’;

VALUE                ISDEFAULT
——————– —————
TRUE                 TRUE
AUTO                 FALSE
ONLY                 FALSE
FALSE                FALSE

07:44:05 SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
———————————— ———– ——————————
use_large_pages                      string      TRUE

So let’s change that, but dynamically and not manually.
07:57:32 SQL> alter system set use_large_pages=auto scope=spfile;
System altered.

HugePagesTotal is still 0, which means system is still not using Large Pages, To allow oracle to lock memory you need to grant it the privilege. I had to edit /etc/security/limits.conf and set the memlock parameters.

[oracle@prashantdb053 /backup/dixit/scripts]# grep Huge /proc/meminfo
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Being a static parameter we need to bounce the database to make changes persistent.
07:58:00 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

07:58:18 SQL> startup
ORACLE instance started.

07:58:54 SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
———————————— ———– ——————————
use_large_pages                      string      AUTO

Entries from alert log which shows that it has been changed to ‘AUTO’ values and started using large pages.

****************** Large Pages Information *****************
Parameter use_large_pages = AUTO
Total Shared Global Region in Large Pages = 2048 KB (0%)
Large Pages used by this instance: 1 (2048 KB)
Large Pages unused system wide = 3 (6144 KB) (alloc incr 16 MB)
Large Pages configured system wide = 4 (8192 KB)
Large Page size = 2048 KB
Time taken to allocate Large Pages = 0.033721 sec

RECOMMENDATION:
Total Shared Global Region size is 2514 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 1253 2048 KB Large Pages (2506 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************

Finally after the change of parameter and hard coding limits system finally started using large pages and alerts coming from the Patrol Agents.

[oracle@prashantdb053 /opt/oracle/diag/rdbms/prashantdb/prashantdb/trace]# grep Huge /proc/meminfo
HugePages_Total:     4
HugePages_Free:      3
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Thanks
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

EXECUTION PLAN: “automatic DOP: skipped because of IO calibrate statistics are missing”

Posted by FatDBA on September 23, 2014

Recently during one Performance Problem i have attached one better SQL Profile to the statement which includes to add DOP (Degree Of Parallelism) to reduce the impacts of a definite FTS (Full Table Scan) but found one NOTE coming during the execution plan generation which reads
“automatic DOP: skipped because of IO calibrate statistics are missing”

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_33935’,task_owner => ‘SYS’, replace => TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.86
SQL> explain plan for select count(*) from DIXIT_EVW_ETAILQ;

Explained.

Elapsed: 00:00:00.03
SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————————————–

————————————————————————————————————————————-
Plan hash value: 584586630

—————————————————————————————————————-
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
—————————————————————————————————————-
|   0 | SELECT STATEMENT       |                  |     1 | 36776   (1)| 00:07:22 |        |      |            |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                  |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| DIXIT_EVW_ETAILQ |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWP |            |
—————————————————————————————————————-

Note
—–
   Рautomatic DOP: skipped because of IO calibrate statistics are missing
¬†¬† – SQL profile “SYS_SQLPROF_0148a0b0821b0005” used for this statement

The ‘AUTOMATIC DOP’ is skipped because I/O calibration is not run to gather the required statistics. Required statistics can be collected using DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————————-
NOT AVAILABLE

DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
–DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/

max_iops = 5944
latency = 9
max_mbps = 75

18 rows selected.

Elapsed: 00:00:00.11

Issue:
If using DBMS_RESOURCE_MANAGER.CALIBRATE_IO there are times when you might recieve beloe error message
ORA-56708: Could not find any datafiles with asynchronous i/o capability

Resolution:
Then we need to enable asynch I/O, set below two values to mentioned settings in the init.ora file.

disk_asynch_io = true
filesystemio_options = asynch

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————–
AVAILABLE

Now you can implement the DOP for the query and this way you can reduce FTS impacts.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Active Session History (ASH) performed an emergency flush. ASH Undersized ?

Posted by FatDBA on August 13, 2014

One day suddenly, I got a warning on my Warehouse system that,
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized.

If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 16777216 bytes. Both ASH size and the total number of emergency flushes since instance start-up can be monitored by running the following query:

SQL>    select total_size,awr_flush_emergency_count from v$ash_info;

TOTAL_SIZE AWR_FLUSH_EMERGENCY_COUNT
———- ————————-
33554432                         8

Reasons:
Typically some activity on system causes more active sessions, therefore filling the ASH buffers faster than usual causing this message to be displayed. This indicates the buffers might need to be increased to support peak activity on the database.
It is not a problem per session, just indicates the buffers might need to be increased to support peak activity on the database.

Fix:
Increase the size of ASH (Consider increasing around 50%) — (current_value+50% of current_value);

SQL> alter system set “_ASH_SIZE”=25165824 scope=both;
System altered.

Posted in Advanced | Tagged: | Leave a Comment »

SLOW SQL Procedure/Query: Time to use Oracle Profiler.

Posted by FatDBA on May 14, 2014

Tuning long PL/SQL programs is always a great pain but with the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Execute profload.sql script and this will install the DBMS_PROFILER package.

SQL> @profload.sql

Package created.
Grant succeeded.
Synonym created.
Library created.
Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

/*Now create the user for a test of DBMS_PROFILER */

SQL> create user plsql_prof_test identified by test;
User created.

SQL> grant connect , resource to plsql_prof_test;
Grant succeeded.

SQL> conn plsql_prof_test/test
Connected.

/*Once connected run proftab.sql which will create related tables where profiler puts its results. */

SQL> @proftab.sql
drop table plsql_profiler_data cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber
*
ERROR at line 1:
ORA-02289: sequence does not exist

Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.

Now we are all set to use the PROFILER to find areas of code causing issues.

09:00:07 SQL> execute dbms_profiler.start_profiler(‘archieve_test’);
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

09:00:24 SQL> show user
USER is “ARADMIN”

09:00:26 SQL> exec ARCHIVING.archieve_test (‘Bdsss_asssas’,30,20, ‘Change MyDesk’, 1);
PL/SQL procedure successfully completed.

Elapsed: 00:10:24.63

09:17:41 SQL> execute dbms_profiler.STOP_PROFILER;
PL/SQL procedure successfully completed.

Now when profiling is completed its the time to use one of the view ‘plsql_profiler_runs’ which always created after you execute both the two initial sripts.
Below query will provide you
– RUN ID (Will help us to investigate further)
– RUN DETAILS (Run Timings)
– Total Time

set linesize 400 pagesize 400
col run_comment format a20
set lines 10000
column run_owner format a30
column run_comment format a20
select runid,run_owner, run_date,run_total_time/1000000000 run_total_time,run_comment from plsql_profiler_runs where run_comment like ‘%chieve%’ and RUN_DATE LIKE ‘%14-04-29%’;

RUNID RUN_OWNER                      RUN_DATE          RUN_TOTAL_TIME RUN_COMMENT
———- —————————— —————– ————– ——————–
63 ARAMAEK                       14-04-25 09:00:24        1078.98 archieve_test

Now when we have the RUN_ID we can collect and fragment query which is causing issues during the run time.
Below query will help us to divide the query (Procedure) with unit numbers and sometimes total time taken.

09:21:32 SQL> select runid,unit_number,unit_type,unit_owner, unit_name, unit_timestamp, total_time from plsql_profiler_units where runid=63;

RUNID UNIT_NUMBER UNIT_TYPE                        UNIT_OWNER                       UNIT_NAME                        UNIT_TIMESTAMP    TOTAL_TIME
———- ———– ——————————– ——————————– ——————————– —————– ———-
63           1 ANONYMOUS BLOCK                  <anonymous>                      <anonymous>                      00-00-00 01:01:01         .0
63           2 ANONYMOUS BLOCK                  <anonymous>                      <anonymous>                      00-00-00 01:01:01         .0
63           3 PACKAGE BODY                     ARADMIN                          ARCHIVING                        14-03-23 12:29:38         .0
63           4 ANONYMOUS BLOCK                  <anonymous>                      <anonymous>                      00-00-00 01:01:01         .0

Elapsed: 00:00:00.00

Below query will provide total number of occurences of UNIT SEGMENTS along with total time.

COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
column unit_name format a11
column TotalTime format a10
column MinTime format a10
SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_name,
d.line#,
to_char(d.total_occur) as TotalOccur,
to_char(d.total_time) as TotalTime,
to_char(d.min_time) as MinTime,
to_char(d.max_time) as max_time
FROM   plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE  u.runid = 1
ORDER BY TotalOccur;

RUNID UNIT_NUMBER UNIT_TYPE            UNIT_NAME        LINE# TOTALOCCUR                               TOTALTIME  MINTIME    MAX_TIME
—— ———– ——————– ———– ———- —————————————- ———- ———- —————————————-

1           3 PACKAGE BODY         ARCHIVING         8741 5                                        5000       1000       1000
1           3 PACKAGE BODY         ARCHIVING         8904 5                                        136732000  25889000   29557000
1           3 PACKAGE BODY         ARCHIVING         8908 5                                        15000      3000       3000
1           3 PACKAGE BODY         ARCHIVING         8930 5                                        54969000   145000     54211000
1           3 PACKAGE BODY         ARCHIVING         8934 5                                        3000       1000       1000
1           3 PACKAGE BODY         ARCHIVING         8958 5                                        15445000   383000     13588000
1           3 PACKAGE BODY         ARCHIVING         8962 5                                        2000       1000       1000
1           3 PACKAGE BODY         ARCHIVING         8964 5                                        59000      1000       40000
1           3 PACKAGE BODY         ARCHIVING         8967 5                                        328000     38000      123000
1           3 PACKAGE BODY         ARCHIVING         8969 5                                        6000       1000       2000
1           3 PACKAGE BODY         ARCHIVING         7780 6                                        140969000  29000      140716000
1           3 PACKAGE BODY         ARCHIVING         7782 6                                        7000       1000       2000
1           3 PACKAGE BODY         ARCHIVING         8702 6                                        5000       1000       1000
1           3 PACKAGE BODY         ARCHIVING         1463 972                                      524000     1000       12000

Almost similar query above but this time sorted based on Total Time basis.

09:27:29 SQL> select runid,unit_number,  line#,total_occur, to_char(total_time), to_char(min_time), to_char(max_time) from plsql_profiler_data where runid=63
09:27:37   2  and total_time !=0 and min_time !=0 and max_time !=0 order by TOTAL_TIME DESC, MIN_TIME  DESC, MAX_TIME DESC, total_occur;

RUNID UNIT_NUMBER      LINE# TOTAL_OCCUR TO_CHAR(TOTAL_TIME)                      TO_CHAR(MIN_TIME)                        TO_CHAR(MAX_TIME)
—— ———– ———- ———– —————————————- —————————————- —————————————-
63           3       5090         220 226072292804                             34994                                    17049666731
63           3       5240          20 181066607554                             3979107078                               103125720715
63           3       4926          20 141296980613                             6541296441                               7361430883
63           3       5195          40 67123652951                              256962                                   3455214090
63           3       5093         200 2777416725                               3649463                                  130042883
63           3       5141          20 1573445706                               999                                      85397446
63           3       5040          26 1123533842                               6529040                                  260607691
63           3       4920          20 538701811                                1999706                                  116417886
63           3       5144           7 538252877                                96985                                    183333050
63           3       4903          20 511259845                                18179327                                 76123809
63           3        941         371 295843511                                527922                                   5997118
63           3       4900          20 243442214                                8460756                                  40755009
63           3       5198          20 223308174                                6913983                                  62818765
63           3       5154           4 197841917                                3578473                                  186799540
63           3       5147           4 173199539                                4495339                                  159311581
63           3       5037          46 111132663                                85987                                    18228320
63           3       5046          26 104860585                                315953                                   49972654
63           3        942         371 101599065                                101985                                   3873430
63           3       5103         200 69940718                                 111983                                   23141598
63           3       5156           4 67220118                                 3353507                                  35764742
63           3        956        2547 52771242                                 9998                                     2742596

TO FIND THE ORIGINAL TEXT OF THE OBJECT FROM ALL_SOURCE VIEW.
SELECT line || ‘ : ‘ || text FROM all_source WHERE owner = ‘ARAMAEK ‘ AND type= ‘PACKAGE BODY’ AND name¬† = ‘ARCHIVING_TEST’;

* In this case this has resulted in a 11014 lines of PACKAGE BODY named ‘ARCHIEVING_TEST’.

09:29:55 SQL> select OWNER#, OBJ#, NAME, NAMESPACE, TYPE#,STATUS from obj$¬† where name =’ARCHIVING_TEST’;

OWNER#       OBJ# NAME                            NAMESPACE      TYPE#     STATUS
———- ———- —————————— ———- ———- ———-
58      85332 ARCHIVING_TEST                               1          9          1
58      85334 ARCHIVING_TEST                               2         11          1

Now you can locate those lines from the package body which is causing issues.

select source from source$ where obj#=85334 and line=5090;
FETCH aSH INTO aStateHistoryID;

select source from source$ where obj#=85334 and line=5240;
FETCH aTR INTO aTicketRelationID;

select source from source$ where obj#=85334 and line between 5239 and 5242 /* Problem Line 5240 */;

—————————————————————————————————————————————-
OPEN aTR FOR ‘SELECT ‘ || getTableColumnName(trFormName, Request_MAST) ||’ FROM ‘ ||t_tr_table||’ WHERE ‘|| getTableColumnName(trFormName, Parent_Request_OID) || ‘ = ‘ || db_request_oid ; LOOP
FETCH aTR INTO aRecordRelationID;
EXIT WHEN aTR%NOTFOUND;
dynInsert := ‘INSERT INTO ‘||t_astr_table||’ (‘||t_tr_tableCol||’) SELECT ‘||t_tr_tableCol||’ from ‘||t_tr_table||’ where C1 = :bind_var’;

Now when we are zeroed to only few line of codes out of 11014 line of codes we can investiate the issue.
Result: After small analysis we found there is few issues with the variable TYPE declaration and issue is fixed after same has been fixed.

Thanks
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

Get DBID when Instance is in NOMOUNT mode.

Posted by FatDBA on May 5, 2014

One fine day we found that we have lost our control-file and the catalog.
RULE: To restore the controlfile, you must know the DBID. Being a UAT/Testing server we don’t have noted the DBID at any safe place.
Method/Fix: You can extract the DBID from the header of a datafile, assuming you have access to it. The database instance needs to up in NOMOUNT mode. Well, it has to be NOMOUNT because you haven’t restored the controlfile yet, a major requirement for the mount operation.

We have forced the DB to start in NOMOUNT Mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1337632 bytes
Variable Size             402654944 bytes
Database Buffers          113246208 bytes
Redo Buffers                5869568 bytes

SQL> show parameter db_name
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      sairam

Let us place a unique identifier in the trace file names in order to easily identify the trace generated which used to find the DBID of the database.
SQL> alter session set tracefile_identifier = dixit;
Session altered.

We’ll now dump one of the datafile and dump few of the DB Blocks (12 Blocks)
SQL> alter system dump datafile ‘/u01/app/oracle/oradata/sairam/xyz.dbf’ block min 1 block max 12;
System altered.

Traces generated during the ALTER SYSTEM DUMP step which contains block header information which includes DBID.

-rw-r—– 1 oracle oinstall¬† 170 May¬† 5 21:44 sairam_ora_9192_DIXIT.trm
-rw-r—– 1 oracle oinstall 177K May¬† 5 21:44 sairam_ora_9192_DIXIT.trc
[oracle@prashant trace]$ pwd
/u01/app/oracle/diag/rdbms/sairam/sairam/trace

Below is the excerpt from the trace file which contains header information
*IN BOLD WE HAVE THE DBID OF THE DATABASE.

System name:    Linux
Node name:      prashant
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: sairam
Redo thread mounted by this instance: 0 <none>
Oracle process number: 19
Unix process pid: 9192, image: oracle@prashant (TNS V1-V3)

*** 2014-05-05 21:44:16.933
*** SESSION ID:(1.3) 2014-05-05 21:44:16.933
*** CLIENT ID:() 2014-05-05 21:44:16.933
*** SERVICE NAME:() 2014-05-05 21:44:16.933
*** MODULE NAME:(sqlplus@prashant (TNS V1-V3)) 2014-05-05 21:44:16.933
*** ACTION NAME:() 2014-05-05 21:44:16.933

Start dump data block from file /u01/app/oracle/oradata/sairam/xyz.dbf minblk 1 maxblk 12
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=410122232=0x1871f7f8, Db Name=’SAIRAM’
Activation ID=0=0x0
Control Seq=22929=0x5991, File size=2560=0xa00
File Number=7, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01c00002 (1024/29360130)
scn: 0x0000.001a1cf6 seq: 0x01 flg: 0x04 tail: 0x1cf61d01
frmt: 0x02 chkval: 0xb87a type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00518600 to 0x0051A600

Thanks
Prashant Dixit

Posted in Advanced | Tagged: | Leave a Comment »

A Day tried to fix the infamous ORA-00600 [kqlnrc_1] from our DB systems.

Posted by FatDBA on February 16, 2014

ORA-00600 [kqlnrc_1] most of the times is about an invalid object in the Library Cache of the database.
An Oracle Trace file is always generated during the time of this error. Below provided are few of the steps that you can try atleast before you passon the case to Oracle Support team.

Open the trace file with an editor (The following lines are from an 11.2.0.3 trace file) :

1. Find the first ORA_600 error argument
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

2. Search the trace file for 0x143c6dad0 (its the first argument in lower case):

DDE: Problem Key ‘ORA 600 [kqlnrc_1]’ was flood controlled (0x2) (incident: 35561)
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

LibraryHandle:  Address=0x143c6dad0 Hash=cd3b674a LockMode=S PinMode=S LoadLockMode=0 Status=INVL
ObjectName:  Name=PUBLIC.TEST
FullHashValue=5c820441b24ce2df1d9a0971cd3b674a Namespace=TABLE/PROCEDURE(01) Type=SYNONYM(05) Identifier=0 OwnerIdn=2147483644
Statistics:  InvalidationCount=2 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=6 TotalPinCount=6
Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
Concurrency:  DependencyMutex=0x143c6db80(0, 8, 0, 0) Mutex=0x143c6dc00(233, 45, 0, 6)
Flags=REM/PIN/TIM/[00022801]
WaitersLists:
Lock=0x143c6db60[0x143c6db60,0x143c6db60]
Pin=0x143c6db40[0x143c6db40,0x143c6db40]
LoadLock=0x143c6dbb8[0x143c6dbb8,0x143c6dbb8]
Timestamp:  Current=04-25-2013 09:41:24
HandleReference:  Address=0x143c6dc90 Handle=(nil) Flags=[00]
ReferenceList:
Reference:  Address=0x145badf28 Handle=0x12d7be980 Flags=DEP[01]
Timestamp=04-25-2013 09:41:24 InvalidatedFrom=0
LibraryObject:  Address=0x10dbe40b0 HeapMask=0000-0001-0001-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block:¬† #=’0′ name=KGLH0^cd3b674a pins=0 Change=NONE
Heap=0x12297cc10 Pointer=0x10dbe4150 Extent=0x10dbe4030 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=0.257812 Size=4.000000 LoadTime=15174108210

3. Find the object :

SELECT object_name,object_type,owner,status
FROM dba_objects
WHERE object_name=’TEST’ and owner=’PUBLIC’;

OBJECT_NAME OBJECT_TYPE OWNER STATUS
————– ————- —————- ——-
TEST SYNONYM PUBLIC VALID
4. Compile the synonym owned by public.

As sys :

SQL> alter public synonym TEST compile;

or Recreate it

SQL> create or replace public synonym xyz for xyz@javadb@testlab;

And That’s Fixed,

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: | Leave a Comment »

RMAN Error: ORA-01152: file 1 was not restored from a sufficiently old backup & ORA-01110:

Posted by FatDBA on January 15, 2014

RMAN Scenario:
DB: Oracle 11g r2
OS: Linux – RHEL 5
Error: “ORA-01152: file 1 was not restored from a sufficiently old backup” when tried to open the database.

STATUS
==============
SQL> @db

NAME      ROLE       LOG_MODE     OPEN_MODE      DG_BROKE FORC_LGNG   CUR_SCN        CKPT_CHG        CTRLFIL_SEQ CTRLFL_CNG      ARCH_CNG       PROT_MOD
——— ———- ———— ————– ——– ———– ————– ————— ———– ————— ————– ——————–
SAIRAM     PRIMARY    ARCHIVELOG   MOUNTED        DISABLED YES         0              32913920280       795938526 32913922263     32913811789    MAXIMUM PERFORMANCE

SQL> select open_mode from v$database;
OPEN_MODE
————–
MOUNTED

This is what’s happening with the database when tried to open it.
Throwing error which reads System datafile is restored from an old backup.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/datafiles/system01.dbf’

When tried to recover the database it’s asking for sequence number 75, which is not available (Conformed, as asked sequence is not available in ARCHIVE destination).

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32913922263 generated at 01/14/2014 10:57:39 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922263 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/system01.dbf’

There is a possibility that the sequence might be struck inside of any redo log file and has not been archived.
Let’s query required sequence number (75) using one of the dynamic view v$log

SQL> select * from v$log where SEQUENCE#=’75’;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
———- ———- ———- ———- ———- ———- — —————- ————-
FIRST_TIM NEXT_CHANGE# NEXT_TIME
——— ———— ———
9          1         75  524288000        512          1 NO  CURRENT             3.2914E+10
14-JAN-14   2.8147E+14

Yes,the log is inside group number 9. Let’s check the location of the related log file using v$logfile;

SQL> SELECT * FROM V$LOGFILE where group#=’9′;

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
—————————————————————————————————-
IS_

9         ONLINE
/var/log/oracle/redo/onlinelog/redo07.log
NO

Let’s try to recover the database again.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’
ORA-01157: cannot identify/lock data file 69 – see DBWR trace file
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’

Alright, it’s asking for one datafile (69) which is unknown to the controlfile. Yes, this is right. As we have created a test datafile before that controlfile backup.
As it’s of no importance, we’d dropped the datafile.

SQL> alter database datafile 69 offline drop;
Database altered.

Let’s try again to recover the database using the log file ‘/var/log/oracle/redo/onlinelog/redo09.log’.

SQL> recover database using backup controlfile ;
ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
Log applied.
Media recovery complete.

Okay, when provided exact filename of the logfile, it has accepted it and applied and came up, hence media recovery is completed.
Now we can go and try to open the database.

SQL> alter database open resetlogs;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dbmount212/oracle/sairam/archivelog/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

And we are up!
Below provided is the list of incarnation of the database and the last line with a new incarnation created on today’s date.

SQL> select * from V$DATABASE_INCARNATION;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
———— —————– ——— ———————– ——— ——- ———— —————— ————————–
1            602821 05-MAY-10                       1 12-MAR-08 PARENT     718177612                  0 NO
2         148027929 19-MAY-10                  602821 05-MAY-10 PARENT     719411741                  1 NO
3        9199856900 28-AUG-11               148027929 29-MAY-10 PARENT     760357356                  2 NO
4        1.9412E+10 11-AUG-12              9199856900 28-AUG-11 PARENT     791021290                  3 NO
5        3.2910E+10 09-DEC-13              1.9412E+10 1-AUG-12 PARENT     833707787                  4 NO
6        3.2914E+10 15-JAN-14              3.2910E+10 09-DEC-13 CURRENT    836892925                  5 YES

6 rows selected.

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 3 Comments »

 
%d bloggers like this: