Ads
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
What happen when db_recovery_file_dest_size of 1048576 bytes is 100.00% used.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
db_recovery_file_dest string G:\app\Vishwakarma\flash_re
ery_area
db_recovery_file_dest_size big integer 3912M
recovery_parallelism integer 0
SQL>
SQL> alter system set db_recovery_file_dest_size=1M;
System altered.
SQL>
SQL>
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> create table test1(id int);
Table created.
SQL> insert into test1 values(12434);
1 row created.
SQL> insert into test1 values(12434);
1 row created.
SQL> insert into test1 select * from test1;
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> /
131072 rows created.
SQL> /
262144 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
C:\Users\Vishwakarma>
C:\Users\Vishwakarma>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 20 12:07:30 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option
SQL> @dbinfo
NAME LOG_MODE OPEN_MODE DATABASE_ROLE
--------- ------------ -------------------- ----------------
TEST1 ARCHIVELOG READ WRITE PRIMARY
SQL> select count(*) from test1;
COUNT(*)
----------
524288
So, after inserting 524288 row db_recovery_file_dest_size of 1048576 bytes is 100.00% used and it database gets stale for any further insert or update (DML).
AlertlLog..............................................................
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc0_2088.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC0: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Thread 1 cannot allocate new log, sequence 7
All online logs needed archiving
Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Sat May 20 12:06:28 2017
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1048576 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 g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC1: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'
Control File all section TRACE ..
To take trace of control file ..
alter session set events 'immediate trace name controlf level 9';
Trace file c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_5844.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:2105M/4085M, Ph+PgF:4645M/8169M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 36
Windows thread id: 5844, image: ORACLE.EXE (SHAD)
alter session set events 'immediate trace name controlf level 9';
Trace file c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_5844.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:2105M/4085M, Ph+PgF:4645M/8169M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 36
Windows thread id: 5844, image: ORACLE.EXE (SHAD)
How to take dump or trace of library cache || internal of library cache
To take trace or dump of library cache, enter following command on sqlplus.
alter session set events 'immediate trace name library_cache level 10';
I have create a table name pk of one column (id) and inserted with some values as given below..
create table pk(id);
insert into pk values (2121);
insert into pk values(2121);
/
/
/
12288 rows created.
First flush the shared pool for any unwanted or unrelated used space on library cache
SQL> alter system flush shared_pool;
System altered.
Then, clear the buffer cache for any preloaded dirty buffer in buffer cache
SQL> alter system flush buffer_cache;
System altered.
now set autotrace on to view optimizer execution and sql execution statistics;
set autotrace on;
here view on library cache dump after performing query on table pk ( select * from pk)
select * from pk;
2121
2121
2121
2121
2121
2121
ID
----------
2121
12288 rows selected.
SP2-0612: Error generating AUTOTRACE report
Execution Plan
----------------------------------------------------------
Plan hash value: 888312997
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12288 | 156K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PK | 12288 | 156K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
to get trace location, view location (value) from v$diag_info
select * from v$diag_info;
SQL> select * from v$diag_info;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Enabled
TRUE
1 ADR Base
c:\app\vishwakarma
1 ADR Home
c:\app\vishwakarma\diag\rdbms\orcl\orcl
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Trace
c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace
1 Diag Alert
c:\app\vishwakarma\diag\rdbms\orcl\orcl\alert
1 Diag Incident
c:\app\vishwakarma\diag\rdbms\orcl\orcl\incident
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Cdump
c:\app\vishwakarma\diag\rdbms\orcl\orcl\cdump
1 Health Monitor
c:\app\vishwakarma\diag\rdbms\orcl\orcl\hm
1 Default Trace File
c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_7556.trc
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Active Problem Count
0
1 Active Incident Count
0
11 rows selected.
check trace of library cache in trace file which has extention of .trc
you can view this using text editor or notepad and search your query.
========================================================================
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:1521M/4085M, Ph+PgF:4148M/8169M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 4928, image: ORACLE.EXE (SHAD)
*** 2017-03-18 23:44:59.808
*** SESSION ID:(9.3) 2017-03-18 23:44:59.808
*** CLIENT ID:() 2017-03-18 23:44:59.808
*** SERVICE NAME:(SYS$USERS) 2017-03-18 23:44:59.808
*** MODULE NAME:(sqlplus.exe) 2017-03-18 23:44:59.808
*** ACTION NAME:() 2017-03-18 23:44:59.808
kwqmnich: current time:: 18: 14: 59: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure
*** 2017-03-18 23:45:00.544
kwqinfy: Call kwqrNondurSubInstTsk
*** 2017-03-18 23:46:50.366
Library Cache Dump
SGA:63def5f0 Flags=2c7
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=6604
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 124631
1 6282
2 155
3 4
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
========================================================================
There many query listed with the bucket, so search for your query ..................
=======================================================================
Bucket: #=27456 Mutex=63efc898(0, 6, 0, 6)
LibraryHandle: Address=5becc958 Hash=5dc66b40 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * from pk
FullHashValue=7b8f5775eab4d2d47c16bf605dc66b40 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1573284672 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=5becca08(0, 0, 0, 0) Mutex=5becca80(9, 20, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=5becc9e8[5becc9e8,5becc9e8]
Pin=5becc9f8[5becc9c8,5becc9c8]
Timestamp: Current=03-18-2017 23:48:12
LibraryObject: Address=5e354220 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^5dc66b40 pins=0 Change=NONE
Heap=5becc8a0 Pointer=5e354308 Extent=5e3541a0 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=7815789
ChildTable: size='16'
Child: id='0' Table=5e3550d0 Reference=5e354b70 Handle=5becc758
NamespaceDump:
Parent Cursor: sql_id=7s5pzc1fwcuu0 parent=000007FF5E354308 maxchild=1 plk=n ppn=n
you can get more detail in v$sql related to hash values and the sql statement that is present in the library cache.
Subscribe to:
Posts (Atom)