We know that any changes to database is recored in online redo logfiles. If your database archival mode on then online redo log files are archived which contains the database changes. With these logfile we can analysis any changes in database.
In simple we can say an undo entry provides the values of data stored before a change and the redo entry provides the values of data stored after a change. Because undo segments are database changes, they also generate redo entries. So we can get them from online redo logs and then to archived logs.
So from online redo logs and from archived redo logs we can get database redo and undo information. But online and archived logs have an unpublished format and are not human-readable. With the DBMS_LOGMNR package we can analysis redo log files and can get back undo and redo information in a human readable format.
Another scenario of use of logminer is to investigate database past in time. With Flashback Query we can get prior values of the record in the table at some point in the past but is limited to UNDO_RETENTION parameter (which is often as short as 30 minutes for an OLTP database.).So in order to analysis past activity on the database logminer is a good choice.
In this step I will show you the step by step procedure how we can use logminer.
1)Ensure that you have on at a minimal level supplemental logging
To work with logminer you must have database supplemental logging on of the source database at a minimum level.By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. To make it use you need to on it.
You can check your supplemental logging on of off by following commands,
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
In order to on it at a minimal level,
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
2)Install the DBMS_LOGMNR package.
The next step is to install DBMS_LOGMNR package. If you have already installed then ignore this steps. You can install this package by running $ORACLE_HOME/rdbms/admin/dbmslm.sql script. If you create your database with dbca then this script run automatically. So you can ignore this step. However if you create database manually with CREATE DATABASE ... command then you must run the script before using logminer. That is ,
SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql
3)Grant the EXECUTE_CATALOG_ROLE role.
The user who will do the mining task give him EXECUTE_CATALOG_ROLE. Here user is ARJU.
SQL>GRANT EXECUTE_CATALOG_ROLE TO ARJU;
4)Create the synonym. ARJU creates a public synonym:
CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
All above four steps are needed just for once.
5)Specify the scope of the mining.
Now you decide on which file you will do the analysis. You may have interest over archived redo log files or online redo log files based on your scenario. In this post suppose you have recent problem in your database and so you might show interest of your online redo log files. You can see
current online redo logs by,
SQL> SELECT distinct member LOGFILENAME FROM V$LOGFILE;
LOGFILENAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/redo01.log
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo3.log
Sometimes, you want to mine the redo log file that was most recently archived.
SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
--------------------------------------------------------------------------------
/oradata2/flash_recovery_area/DBASE/archivelog/2008_08_03/o1_mf_1_839_49bodr0k_.
arc
With the DBMS_LOGMNR.ADD_LOGFILE specify those files that you want to mine.
Here I have given the online redo log files redo01.log and redo03.log.
SQL>BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oradata2/data1/dbase/redo01.log');
DBMS_LOGMNR.ADD_LOGFILE
('/oradata2/data1/dbase/redo03.log');
END;
/
6)Start the LogMiner session and specify a dictionary.
To start the LogMiner session:
BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/
Using the OPTIONS parameter, it is specified that Oracle Database read the dictionary information to convert the object names from the online catalog while starting LogMiner.
7)Check contents from V$LOGMNR_CONTENTS view.
To get information and when DML or DDL happened in the V$LOGMNR_CONTENTS about table TEST3 we can issue
SQL> select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'TEST3';
USERNAM TIMESTAMP SEG_TY SEG_NAME TABLE_SPAC SID SERIAL# OPERATION
------- ----------------- ------ ---------- ---------- ---------- ---------- --------------------------------
08/03/08 02:57:35 TABLE TEST3 153 3272 DDL
08/03/08 02:57:41 TABLE TEST3 SYSTEM 153 3272 INSERT
08/03/08 02:57:55 TABLE TEST3 SYSTEM 153 3272 UPDATE
08/03/08 03:01:35 TABLE TEST3 SYSTEM 153 3272 UPDATE
We can get SQL_UNDO and SQL_REDO information by,
SQL> select sql_undo, sql_redo
from v$logmnr_contents
where table_name = 'TEST3' and OPERATION='UPDATE';
SQL_UNDO SQL_REDO
-------------------------------------------------- --------------------------------------------------
update "SYS"."TEST3" set "A" = '3' where "A" = '9' update "SYS"."TEST3" set "A" = '9' where "A" = '3'
and ROWID = 'AAAOKVAABAAAP8qAAA'; and ROWID = 'AAAOKVAABAAAP8qAAA';
update "SYS"."TEST3" set "A" = '9' where "A" = '10 update "SYS"."TEST3" set "A" = '10' where "A" = '9
' and ROWID = 'AAAOKVAABAAAP8qAAA'; ' and ROWID = 'AAAOKVAABAAAP8qAAA';
8)End the LogMiner session.
Use the DBMS_LOGMNR.END_LOGMNR procedure.
SQL> BEGIN
DBMS_LOGMNR.END_LOGMNR;
END;
/
PL/SQL procedure successfully completed.
Related Documents
Logminer fails with ORA-01284, ORA-00308, ORA-27047
Steps and examples to use logminer for Remote mining
Saturday, August 2, 2008
ORA-25153: Temporary Tablespace is Empty
Problem Description
Whenever you try to access temporary tablespace it fails with error ORA-25153. Suppose I want to get the user creation script of user ARJU but it fails with following message.
SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 2729
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
Cause of The Problem
The user assigned to a temporary tablespace does not exist in the database. For example the user is associated to a temporary tablespace and later someone has dropped it.
Solution of The Problem
We can see whether any temporary tablespace available in database by,
SQL> select count(*) from dba_temp_files;
COUNT(*)
----------
1
Now we see there is temporary tablespace existed in the database. But possibly user ARJU is not assigned to any temporary tablespace. We can check the user's ARJU temporary tablespace by,
SQL> select temporary_tablespace from dba_users where username='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
But in database the available temporary tablespaces are,
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP3
So TEMP which is assigned to ARJU does not existed in database and hence we got ORA-25153: Temporary Tablespace is Empty.
Now change the temporary tablespace of user ARJU.
SQL> alter user arju temporary tablespace temp3;
User altered.
Any now above query and it is ok.
SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
DBMS_METADATA.GET_DDL('USER','ARJU')
--------------------------------------------------------------------------------
CREATE USER "ARJU" IDENTIFIED BY VALUES '55E19EAC6BA480EA'
DEFAULT TABLESPACE "USER_TBS"
TEMPORARY TABLESPACE "TEMP3"
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Whenever you try to access temporary tablespace it fails with error ORA-25153. Suppose I want to get the user creation script of user ARJU but it fails with following message.
SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 2729
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
Cause of The Problem
The user assigned to a temporary tablespace does not exist in the database. For example the user is associated to a temporary tablespace and later someone has dropped it.
Solution of The Problem
We can see whether any temporary tablespace available in database by,
SQL> select count(*) from dba_temp_files;
COUNT(*)
----------
1
Now we see there is temporary tablespace existed in the database. But possibly user ARJU is not assigned to any temporary tablespace. We can check the user's ARJU temporary tablespace by,
SQL> select temporary_tablespace from dba_users where username='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
But in database the available temporary tablespaces are,
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP3
So TEMP which is assigned to ARJU does not existed in database and hence we got ORA-25153: Temporary Tablespace is Empty.
Now change the temporary tablespace of user ARJU.
SQL> alter user arju temporary tablespace temp3;
User altered.
Any now above query and it is ok.
SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
DBMS_METADATA.GET_DDL('USER','ARJU')
--------------------------------------------------------------------------------
CREATE USER "ARJU" IDENTIFIED BY VALUES '55E19EAC6BA480EA'
DEFAULT TABLESPACE "USER_TBS"
TEMPORARY TABLESPACE "TEMP3"
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Thursday, July 31, 2008
ORA-00019: maximum number of session licenses exceeded
Problem Description
Normal user can't connect to oracle database. Whenever they try to connect to database it fails with ORA-00019.
SQL> conn india/a
ERROR:
ORA-00019: maximum number of session licenses exceeded
Solution of The Problem
1)Log on to database as a DBA user.
SQL> conn arju/a
Connected.
2)Check the license_max_sessions parameter.
SQL> show parameter license_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_sessions integer 1
3)Increase the value of license_max_sessions parameter.
SQL> alter system set license_max_sessions=100;
System altered.
4)Test database to connect with the normal user.
SQL> conn india/a
Connected.
Normal user can't connect to oracle database. Whenever they try to connect to database it fails with ORA-00019.
SQL> conn india/a
ERROR:
ORA-00019: maximum number of session licenses exceeded
Solution of The Problem
1)Log on to database as a DBA user.
SQL> conn arju/a
Connected.
2)Check the license_max_sessions parameter.
SQL> show parameter license_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_sessions integer 1
3)Increase the value of license_max_sessions parameter.
SQL> alter system set license_max_sessions=100;
System altered.
4)Test database to connect with the normal user.
SQL> conn india/a
Connected.
ORA-00018, ORA-00020 maximum number of sessions exceeded
Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
or simply you can consider to increase processes parameter by,
Alter system set processes=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
or simply you can consider to increase processes parameter by,
Alter system set processes=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Wednesday, July 30, 2008
ORA-01667: cannot add any more tablespaces: limit of exceeded
If your database version is higher than 8 suppose 10.2g then when MAXDATAFILES of your control file reached then MAXDATAFILES parameter would expand automatically unless you hit bug. You can see the post here,
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
How to determine OS block size for windows or unix
In many times you probably have heard about set the oracle db block size as a multiple of the OS block size. But whenever you ask how I can determine or find OS block size for windows or Unix then many one get stopped. In fact this question is OS related. In this post I will show how I can get OS block.
1)UNIX or Linux System.
On my Solaris Machine,
SQL> !perl -e '$a=(stat ".")[11]; print $a'
8192
On my debian Linux,
$ perl -e '$a=(stat ".")[11]; print $a'
4096
On my Solaris Machine another way,
SQL> !df -g /oracle
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
Where /oracle is disk partition.
Another example,
SQL> !df -g | grep 'block size'
/ (/dev/dsk/c1d0s0 ): 8192 block size 1024 frag size
/devices (/devices ): 512 block size 512 frag size
/system/contract (ctfs ): 512 block size 512 frag size
/proc (proc ): 512 block size 512 frag size
/etc/mnttab (mnttab ): 512 block size 512 frag size
/etc/svc/volatile (swap ): 4096 block size 4096 frag size
/system/object (objfs ): 512 block size 512 frag size
/lib/libc.so.1 (/usr/lib/libc/libc_hwcap1.so.1): 8192 block size 1024 frag size
/dev/fd (fd ): 1024 block size 1024 frag size
/tmp (swap ): 4096 block size 4096 frag size
/var/run (swap ): 4096 block size 4096 frag size
/oradata1 (/dev/dsk/c1d0s4 ): 8192 block size 1024 frag size
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
/oradata2 (/dev/dsk/c1d0s5 ): 8192 block size 1024 frag size
/export/home (/dev/dsk/c1d0s7 ): 8192 block size 1024 frag size
2)Windows Machine.
If you use ntfs file system you can use
fsutil fsinfo ntfsinfo drivename: to get information of block size.
c:\>fsutil fsinfo ntfsinfo f:
NTFS Volume Serial Number : 0x1e345b64345b3e49
Version : 3.1
Number Sectors : 0x0000000004a852c0
Total Clusters : 0x0000000000950a58
Free Clusters : 0x00000000003ae433
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096 -- This is OS block size.
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a812c
Mft Zone Start : 0x0000000000800720
Mft Zone End : 0x000000000083b920
To get All possible size on windows machine use,
c:\ format /?
In the /A:size you will get the all possible size for both FAT and NTFS file system. Whenever you format a drive you can select default allocation unit as block size.
3)On HPUX issue like "vgdisplay -v /dev/vg01/lvol"
Related Documents
http://arjudba.blogspot.com/2008/06/choose-optimal-data-block-size-in.html
http://arjudba.blogspot.com/2008/06/advantages-and-disadvantages-of-using.html
1)UNIX or Linux System.
On my Solaris Machine,
SQL> !perl -e '$a=(stat ".")[11]; print $a'
8192
On my debian Linux,
$ perl -e '$a=(stat ".")[11]; print $a'
4096
On my Solaris Machine another way,
SQL> !df -g /oracle
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
Where /oracle is disk partition.
Another example,
SQL> !df -g | grep 'block size'
/ (/dev/dsk/c1d0s0 ): 8192 block size 1024 frag size
/devices (/devices ): 512 block size 512 frag size
/system/contract (ctfs ): 512 block size 512 frag size
/proc (proc ): 512 block size 512 frag size
/etc/mnttab (mnttab ): 512 block size 512 frag size
/etc/svc/volatile (swap ): 4096 block size 4096 frag size
/system/object (objfs ): 512 block size 512 frag size
/lib/libc.so.1 (/usr/lib/libc/libc_hwcap1.so.1): 8192 block size 1024 frag size
/dev/fd (fd ): 1024 block size 1024 frag size
/tmp (swap ): 4096 block size 4096 frag size
/var/run (swap ): 4096 block size 4096 frag size
/oradata1 (/dev/dsk/c1d0s4 ): 8192 block size 1024 frag size
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
/oradata2 (/dev/dsk/c1d0s5 ): 8192 block size 1024 frag size
/export/home (/dev/dsk/c1d0s7 ): 8192 block size 1024 frag size
2)Windows Machine.
If you use ntfs file system you can use
fsutil fsinfo ntfsinfo drivename: to get information of block size.
c:\>fsutil fsinfo ntfsinfo f:
NTFS Volume Serial Number : 0x1e345b64345b3e49
Version : 3.1
Number Sectors : 0x0000000004a852c0
Total Clusters : 0x0000000000950a58
Free Clusters : 0x00000000003ae433
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096 -- This is OS block size.
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a812c
Mft Zone Start : 0x0000000000800720
Mft Zone End : 0x000000000083b920
To get All possible size on windows machine use,
c:\ format /?
In the /A:size you will get the all possible size for both FAT and NTFS file system. Whenever you format a drive you can select default allocation unit as block size.
3)On HPUX issue like "vgdisplay -v /dev/vg01/lvol"
Related Documents
http://arjudba.blogspot.com/2008/06/choose-optimal-data-block-size-in.html
http://arjudba.blogspot.com/2008/06/advantages-and-disadvantages-of-using.html
Tuesday, July 29, 2008
How to determine free memory and used memory in Oracle
The summary information of the SGA is determined by,
SQL> select * from V$SGA;
NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544
To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864
COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0
13 rows selected.
To know the details of free and used memory use,
col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB
Related Documents
Memory Usage of Solaris Operating System
SQL> select * from V$SGA;
NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544
To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864
COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0
13 rows selected.
To know the details of free and used memory use,
col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB
Related Documents
Memory Usage of Solaris Operating System
ORA-38760: This database instance failed to turn on flashback database ORA-38701 ORA-27037
Problem Description
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Monday, July 28, 2008
Classes of Oracle Wait Events
Every oracle wait event belongs to a class of wait event. Here is the list of classes of oracle wait events.
1)Administrative: DBA commands cause users to wait. Example: index rebuild
2)Application: User application codes cause to wait. Example: row level lock
3)Cluster: Waits related to Real Application Cluster resources. Example: gc cr block busy
4)Commit: After issuing commit wait for redo log write confirmation. Example: log file sync
5)Concurrency: Waits for internal database resources. Example: Latch wait.
6)Configuration: Waits caused by inadequate configuration of database or instance resources Example: Undersized log file size, undersized shared pool size.
7)Idle: Waits that signify the session is inactive, that is session waiting for work. Example: SQL*Net message from client.
8)Network: Waits related to network messaging. Example: SQL*Net more data to dblink.
9)Other: Waits which should not typically occur on a system. Example:wait for EMON to spawn.
10)Scheduler: Resource Manager related waits. Example: resmgr: become active.
11)System I/O: Waits for background process IO. Example: DBWR waits for 'db file parallel write'.
12)User I/O: Waits for user IO. Example: 'db file sequential read'
Related Documents
1)Administrative: DBA commands cause users to wait. Example: index rebuild
2)Application: User application codes cause to wait. Example: row level lock
3)Cluster: Waits related to Real Application Cluster resources. Example: gc cr block busy
4)Commit: After issuing commit wait for redo log write confirmation. Example: log file sync
5)Concurrency: Waits for internal database resources. Example: Latch wait.
6)Configuration: Waits caused by inadequate configuration of database or instance resources Example: Undersized log file size, undersized shared pool size.
7)Idle: Waits that signify the session is inactive, that is session waiting for work. Example: SQL*Net message from client.
8)Network: Waits related to network messaging. Example: SQL*Net more data to dblink.
9)Other: Waits which should not typically occur on a system. Example:wait for EMON to spawn.
10)Scheduler: Resource Manager related waits. Example: resmgr: become active.
11)System I/O: Waits for background process IO. Example: DBWR waits for 'db file parallel write'.
12)User I/O: Waits for user IO. Example: 'db file sequential read'
Related Documents
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
Available Views for Oracle Wait Events
There are three dynamic performance views by which we can get information about the oracle wait events. Each of the wait events belong to a wait class.
The three dynamic performance views are,
1)V$SESSION_WAIT: This dynamic performance view displays the events for which sessions have just completed waiting or are currently waiting.
2)V$SYSTEM_EVENT: This view displays the total number of times all the sessions have waited for the events.
3)V$SESSION_EVENT: This view displays all waits for each session.
The list of all oracle wait events and their associated wait class can be found by querying,
SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
The three dynamic performance views are,
1)V$SESSION_WAIT: This dynamic performance view displays the events for which sessions have just completed waiting or are currently waiting.
2)V$SYSTEM_EVENT: This view displays the total number of times all the sessions have waited for the events.
3)V$SESSION_EVENT: This view displays all waits for each session.
The list of all oracle wait events and their associated wait class can be found by querying,
SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
Connect to database fails with ORA-09817, ORA-01075
Normal user could not connect to your database and whenever you try to connect to your database with user having SYSDBA privilege it returns error ORA-09817, ORA-01075.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 28 04:14:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Solaris-AMD64 Error: 28: No space left on device
ORA-01075: you are currently logged on
Cause of the Error: Database could not write to audit file because of Solaris-AMD64 Error: 28.No space left on device. In the partition where audit file resides has no free space.
Solution of The Problem:
The only solution is make more free space in the partition where audit file resides. You can see the audit file destination in the pfile parameter audit_file_dest. By default the location is
$ORACLE_HOME/admin/$ORACLE_SID/adump.
First you can check the available space in your partition by,
-bash-3.00$ df -h
/dev/dsk/c1d0s3 24G 24G 0K 100% /oracle
Now delete the unnecessary files in the partition.
Like rm -r /oracle/test
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 28 04:14:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Solaris-AMD64 Error: 28: No space left on device
ORA-01075: you are currently logged on
Cause of the Error: Database could not write to audit file because of Solaris-AMD64 Error: 28.No space left on device. In the partition where audit file resides has no free space.
Solution of The Problem:
The only solution is make more free space in the partition where audit file resides. You can see the audit file destination in the pfile parameter audit_file_dest. By default the location is
$ORACLE_HOME/admin/$ORACLE_SID/adump.
First you can check the available space in your partition by,
-bash-3.00$ df -h
/dev/dsk/c1d0s3 24G 24G 0K 100% /oracle
Now delete the unnecessary files in the partition.
Like rm -r /oracle/test
Sunday, July 27, 2008
How to setup Wireless Network Connection on Windows XP
Before proceed note that your OS is determine the wireless networks. That is you will see an unplugged icon in the wireless network connections. If windows can't detect your wireless driver then you need to install the driver of the wireless adaptor.
1)Go to control panel and then click Network Connections.
2)If you have driver setup either built in OS or from third party software then you will see Wireless Network Connection icon. Right Click on the Wireless Network Connection then and click Status
3)If there is no received data then click on View Wireless Network Connection.
4)AT this stage you need to be cautious. If you click Refresh Network list then no connection will be shown if other third party software is used to configure your wireless other than windows.
5)If you want other software then don't use windows one. However if you want to let windows to configure your wireless connection and after refresh network list no list is shown then Click Change Advanced Settings.
6)Select Wireless Networks Tab.
7)Just check the box "Use Windows to configure my wireless network settings."
8)Click ok.
9)Now click Refresh Network list and you will see the available connection.
10)Select your desired network and connect to wireless network.
Related Documents
http://arjudba.blogspot.com/2009/03/how-to-get-gui-from-windows-to.html
http://arjudba.blogspot.com/2009/01/copy-files-between-unix-and-windows.html
http://arjudba.blogspot.com/2008/10/who-is-connected-to-your-windows.html
http://arjudba.blogspot.com/2008/10/open-application-using-keyboard.html
http://arjudba.blogspot.com/2009/03/how-to-get-gui-from-windows-to.html
http://arjudba.blogspot.com/2009/01/copy-files-between-unix-and-windows.html
http://arjudba.blogspot.com/2008/10/who-is-connected-to-your-windows.html
http://arjudba.blogspot.com/2008/10/open-application-using-keyboard.html
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML