My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • December 2019
    M T W T F S S
    « Jun    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Blogs I Follow

Archive for the ‘Oracle Server Administration’ Category

Quick Steps to Install Oracle 19c in Windows

Posted by Sriram Sanka on June 14, 2019


 

Step 1 : Download Oracle 19c DB software for Windows using https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Step 2 : Unzip using 7-Zip to your Destination folder.

Step 3 : Navigate to the Extracted Home Folder and Click on Setup.Exe.   

 

These 3 Simple Steps will launch “OUI” for Installing 19c , go-ahead and  proceed with the Interactive ” Next ” Options.

Happy Reading !

Posted in Installation, Oracle Administration, Oracle Server Administration, Windows | Leave a Comment »

ORA-00600: Internal error code, arguments: [kcratr_nab_less_than_odr]

Posted by Sriram Sanka on March 19, 2014


Today,  I had an Issue with My Oracle DB V 11.2.0.1  and It was Corrupted due to unexpected power failures More than 10 times 😉 !.

As Its a Test DB, I don`t have  regular backups.  It was an Internal Error with ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]   As I don`t have complete backup to recover , I can recover using  ICR ( Incomplete recovery)

image1 image2

image3

 

Thanks for your time , Happy reading & Have a Good day 😉

Posted in Oracle Server Administration | Tagged: | 13 Comments »

ORA-27102: out of memory” While Creating a Database

Posted by Sriram Sanka on September 24, 2012


Today ,While Installing Oracle V 10g Using DBCA On a 32 bit Linux machine with 16 GB of RAM,(allocated 5GB as SGA ) We were encountered with this Error and unable to go further.

[oracle@db ~]$ oerr ORA 27102
27102, 00000, “out of memory”
// *Cause: Out of memory
// *Action: Consult the trace file for details
[oracle@db ~]$

 

This is Because of  On 32 Bit version,It cannot allocate more than 4 GB of Memory  to a process Directly.For this either we need to upgrade to 64 bit and start the Installation or decrease the Memory total value.

So we decreased the total memory size to 2.5 GB and It went successful without any issues.

Read Oracle Meta link Document ID 430492.1  to get  More Information on this.

 

Happy reading 😉

 

 

Posted in Oracle Server Administration | Tagged: | 6 Comments »

SCN – What, why, and how?

Posted by Sriram Sanka on January 20, 2012


Here is a good article about SCN see the link below

SCN – What, why, and how?

Posted in Oracle Server Administration | Tagged: | Leave a Comment »

ORA-06544: PL/SQL: internal error, arguments: [ph2_cly out of bounds in PH2POP.], [0], [1240], [], [], [], [], []

Posted by Sriram Sanka on January 11, 2012


Recently we upgraded Our Development Database  from Oracle V10.2.0.1 to V10.2.0.5.
Today While I was  Compiling an Invalid procedure,

ORA-06544: PL/SQL: internal error, arguments: [ph2_cly out of
bounds in PH2POP.], [0], [1240], [], [], [], [], []
Encountered.

No clues available On “alert-log” file.
Verified with Oracle Support about it and found that its a Bug{Bug 10400244} on 10.2.0.5.
Oracle Meta-link Document [ID 1358114.1] Will give some clues about it.
Fixing errors other than this Internal error will resolved this issue.

Posted in Oracle Server Administration | Tagged: , , | 11 Comments »

“racgmain” Processes

Posted by Sriram Sanka on October 3, 2011


Some good Experience for Me today.Early in the Morning ……

One of Our RAC node  filled up with 100% CPU usage.As part of Investigation We found that this was because of  racgmain  Process.

It was filled-up with more than 250 racgmain process which are consuming  CPU a lot.Due this Our System got Very slow.

To avoid this, we should apply latest patch which is > 10.2.0.5 and CRS patch too.There was a temporary workaround for this ..you can see that in Metalink Doc ID 732086.1 and ID 6196746.8

 

Posted in Oracle Server Administration, Uncategorized | Leave a Comment »

Password Hash In Oracle 11g

Posted by Sriram Sanka on June 7, 2011


Let `s discuss about The Changes Of  DBA_USERS Especially In PASSWORD Column In Oracle 11g and 10g.

DBA_USERS   Gives Us Information about all users of the database.

And it contains password hash value In its PASSWORD Column

ind>  col TABLE_NAME format a10
ind> col COLUMN_NAME format a10
ind> col COMMENTS format a25
ind>  select TABLE_NAME,COLUMN_NAME, COMMENTS
  2   from dict_columns
  3   where TABLE_NAME='DBA_USERS' and COLUMN_NAME='PASSWORD';

TABLE_NAME COLUMN_NAM COMMENTS
---------- ---------- -------------------------
DBA_USERS  PASSWORD   Encrypted password

1 row selected.

Yes..It contains the encrypted value Based On Concatenation of Username and Password
This Is How/why two Users with same password can have different Encrypted hash values.

ind> create user satya identified by satya;

User created.

ind>  create user kalyani identified by satya;

User created.

ind> select username,password
  2  from dba_users
  3  where USERNAME in ('KALYANI','SATYA');

USERNAME                       PASSWORD
------------------------------ ------------------------------
KALYANI                        E81F7CB996A56BA9
SATYA                          218ED5615AAE5F6B

2 rows selected.

ind> drop user satya;

User dropped.

ind> drop user satyab;
drop user satyab
          *
ERROR at line 1:
ORA-01918: user 'SATYAB' does not exist

ind> create user satya identified by backpain;

User created.

ind> create user satyab identified by ackpain;

User created.

ind> select username,password
  2  from dba_users
  3  where username like 'SATYA%';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SATYA                          458070F68E74206E
SATYAB                         458070F68E74206E

2 rows selected.

Here You can Observe that User SATYA and SATYAB with same hash value,
as the Concatenation of USERNAME and PASSWORD are Same.

But In Oracle 11g Oracle Wont store any password hash values in DBA_USERS.PASSWORD column.As Per the Document

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302

Password Column  is deprecated in favor of   the AUTHENTICATION_TYPE column

But You can get the password from “User$“.

SQL> create User a identified by "a";

User created.

SQL> create User b identified by "a";

User created.

SQL> select dbms_metadata.get_ddl('USER','A') from dual;

DBMS_METADATA.GET_DDL('USER','A')
--------------------------------------------------------------------------------

   CREATE USER "A" IDENTIFIED BY VALUES 'S:298EDEE1721E71B950D55CCB9ABA7EE5C596E
A6B0CCFF098E88889B98BD5;AFCC9478DFBF9029'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select dbms_metadata.get_ddl('USER','B') from dual;

DBMS_METADATA.GET_DDL('USER','B')
--------------------------------------------------------------------------------

   CREATE USER "B" IDENTIFIED BY VALUES 'S:25E85C1466288EE377681D131DF1920B33448
CD4108F8DDFC580A3315A39;9017AAA5BF2D9732'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select name, password, spare4 from sys.user$ where name ='A'
  2  ;

NAME                           PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
A                              AFCC9478DFBF9029
S:298EDEE1721E71B950D55CCB9ABA7EE5C596EA6B0CCFF098E88889B98BD5

SQL> select password,username from dba_users
  2  where length(username)=1;

PASSWORD                       USERNAME
------------------------------ ------------------------------
                               A
                               B

SQL>

You can Find My Post here at Orafaq.
http://www.orafaq.com/forum/mv/msg/171754/510127/136607/#msg_510127
For more reference
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302
Hope You enjoyed It 🙂
Sriram

Posted in Oracle Server Administration | 104 Comments »

ORA-00059: maximum number of DB_FILES exceeded

Posted by Sriram Sanka on May 27, 2011


This is all about the Relation B/W  MAXDATAFILES and DB_FILES In Oralce Database.

–This will Give Us db_files value. max no of data files that we can add.

select value from v$parameter where name = ‘db_files’;

This will give the MAXDATAFILES specified at Control file level while creating Database.

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

Lets Start with a small case. I have Created My database with DB_FILES   as 10.

C:\Windows\System32>sqlplus system/tejajun20

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

Let us have a look at the no of data files I have.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>

Let me add some data files to users tables

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
         8

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        10

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

As I have already reached the max limit 10,Adding one more file will raise an Error.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

ORA-00059:

maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.

Lets Try to increase the value to a small number.

SQL> alter system set db_files=20 scope=spfile;

System altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
20

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;

Tablespace altered.

SQL>

See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).

Lets do that And verify what `ll happen.


SQL> alter system set db_files=150 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        14
--- In another window I am adding datafiles upto the limit "100"
SQL> /

  COUNT(*)
----------
        48

SQL> /

  COUNT(*)
----------
        55

SQL> /

  COUNT(*)
----------
        82

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;

Tablespace altered.

SQL>
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;

Tablespace altered.

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          200

----Observe it Automatically changed..

SQL>

As Per Oracle Documents :

Consider Possible Limitations When Adding Datafiles to a Tablespace:

  1. You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
  2. Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
  3. Operating systems impose limits on the number and size of datafiles.
  4. The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

DB_FILES specifies the maximum number of database files that can be opened for this database.The maximum valid value is the maximum number of files, subject to operating system constraint,that will ever be specified for the database, including files to be added by ADD DATAFILE statements.If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database,then they should have the same value for this parameter.

For More Information Please Read:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

Posted in Oracle Server Administration | 98 Comments »

How to read Oracle Explain Plan

Posted by Sriram Sanka on May 10, 2011


The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer.A statement’s execution plan is the sequence of operations Oracle performs to run the statement.execution plans are read inside-out.if there are two statements at the same level, the first statement is executed first.

You can use “Explain plan statement for this.” which should be explained first.
The basic syntax to get explain plan is explain plan set statement_id= for

For Ex:
ind> explain plan set statement_id=’ORAFAQ’ for
2 select
3 a.empno,
4 a.ename,
5 b.dname
6 from
7 emp_sriram a,
8 dept b
9 where
10 a.deptno=b.deptno
11 /

Explained.
Now the sequential steps of this sql statement is stored in a Table called PLAN_TABLE.

Lets have a look at the table.

ind> select LPad(‘ ‘, 2*(Level-1)) || Level || ‘.’ || Nvl(Position,0)|| ‘ ‘ ||
2 Operation || ‘ ‘ || Options || ‘ ‘ || Object_Name || ‘ ‘ || Object_Type
3 || ‘ ‘ || Decode(id, 0, Statement_Id ||’ Cost = ‘ || Position) || cost
4 || ‘ ‘ || Object_Node “Query Plan”
5 from plan_table
6 start with id = 0 And statement_id=’ORAFAQ’
7 connect by prior id = parent_id
8 and statement_id=’ORAFAQ’
9 /

Query Plan
———————————————————————————————–
1.4 SELECT STATEMENT ORAFAQ Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS FULL EMP_SRIRAM TABLE 3
3.2 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0

5 rows selected.

You can Also Use DBMS_XPLAN.DISPLAY.
ind> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’ORAFAQ’,’BASIC’));

PLAN_TABLE_OUTPUT
———————————————————————————-
Plan hash value: 2868360194

—————————————————
| Id | Operation | Name |
—————————————————
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP_SRIRAM |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
—————————————————

11 rows selected.

The basic structure of this package is
dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);

Format choices are
BASIC ….. displays minimum information
TYPICAL … displays most relevant information
SERIAL …. like TYPICAL but without parallel information
ALL ……. displays all information

SQL plus autotrace also produce the explain  plan …..

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on:	Shows the execution plan as well as statistics of the statement.
set autotrace on explain:	Displays the execution plan only.
set autotrace on statistics:	Displays the statistics only.
set autotrace traceonly:	Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off:	Disables all autotrace

Now lets have a look at execution plan order ….

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”, SUBSTR(optimizer,1,6) “OPT”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER OPT
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 4-3-1 ANALYZ

5 rows selected.

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,

2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),

3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||

4 NVL(position, 0) “ORDER”

5 from plan_table

6 start with id = 0

7 and statement_id=’ORAFAQ’

8 connect by prior id = parent_id

9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER

—————————— —————————— —————————— ——-

SELECT STATEMENT COST = 4                      0-0-4

NESTED LOOPS                                                      1-0-1

TABLE ACCESS FULL EMP_SRIRAM             2-1-1

TABLE ACCESS BY INDEX ROWID DEPT      3-1-2

INDEX UNIQUE SCAN PK_DEPT                        4-3-1

5 rows selected.

On  0-0-4  it is in an order like ID and parent id nad its position.

Like wise if we can order them

00
10
21
31
43
here Level 1 has 2 childs(2,3) and  3 has 1 child(4).
So the execution steps sequential order will be 2,4,3,1
i.e
TABLE ACCESS               FULL                           EMP_SRIRAM  
INDEX                    UNIQUE SCAN                    PK_DEPT
TABLE ACCESS               BY INDEX ROWID                 DEPT  
Produce the result
SELECT STATEMENT Indicates that its a "SELECT STATEMENT"

Here First it will read data from emp_sriram then a unique Index scan  on dept (step 4) 
which gives the rowids and based the rowid input it goes to the step 1
(we can say for understanding) and produce the result.

For More Information Please have a look at Oracle documents.

Posted in Oracle Server Administration | 2 Comments »

All about the DUAL

Posted by Sriram Sanka on May 4, 2011


The Magic table DUAL is owned by User SYS as its an dictionary object.

ind> select object_name,owner,object_type
2 from dba_objects
3 where object_name like ‘%DUAL%’;

OBJECT_NAME OWNER OBJECT_TYPE
———— ———————- ——————-
DUAL SYS TABLE
DUAL PUBLIC SYNONYM

2 rows selected.

‘Dual’ is a special table with 1X1 (1 row and 1 column) owned by sys which can be used to know the result I can say :).

ind> desc dual;
Name Null? Type
—————————————– ——– ————-
DUMMY VARCHAR2(1)

ind> desc x$dual
Name Null? Type
—————————————– ——– ———————-
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(1)

In specific cases like whenever Database is not fully up(recovery process),at that situation oracle reads the table dual from a fixed table/view

ind> select * from dual;

D

X

1 row selected.

ind> select * from x$dual;

ADDR INDX INST_ID D
——– ———- ———- –
0366CD54 0 1 X

1 row selected.

Never ever try to play with dual/dictionary objects which will ruin you DB.

Posted in Oracle Server Administration | Leave a Comment »

Oracle Fixed(/Dynamic) views

Posted by Sriram Sanka on May 4, 2011


“Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.”

Why the term “Fixed”?

Because They are fixed ..One cannot perform any DML , DDL or any operation other than “SELECT“,thats why these views/Tables are known as Fixed.

ind> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

5 rows selected.

ind> alter table x$ksmsp add sriram varchar2(30);
alter table x$ksmsp add sriram varchar2(30)
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views.

Oracle maintains some dynamic performance views which are dynamically created based on the “X$” tables.

Those are basically start with ‘V$’ and ‘GV$’ with one extra column “Instance_id”.

ind> desc v$fixed_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

ind> desc v$fixed_view_definition
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                          VARCHAR2(30)
 VIEW_DEFINITION                                    VARCHAR2(4000)

We can query these two views …to get more Info on these views

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$SESSION'
  3  ;

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$SESSION                      select  SADDR , SID , SERIAL# , AUDSID , PADDR , U
                               SER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKW
                               AIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUS
                               ER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE
                                , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD
                               _NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_S
                               QL_ID, PREV_CHILD_NUMBER , MODULE , MODULE_HASH ,
                               ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_S
                               EQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAI
                               T_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_
                               ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHO
                               D , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STA
                               TUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATIO
                               N, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLO
                               CKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT
                               ,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,
                               WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, S
                               ECONDS_IN_WAIT,STATE,SERVICE_NAME, SQL_TRACE, SQL_
                               TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where
                                inst_id = USERENV('Instance')

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='GV$SESSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$SESSION                     select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuuds
                               es,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
                               susesow, decode(s.ksusetrn,hextoraw('00'),null,s.k
                               susetrn),decode(s.ksqpswat,hextoraw('00'),null,s.k
                               sqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0
                               ,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
                               ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.kss
                               patyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),
                                 s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ks
                               usemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksus
                               eflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?
                               '), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.k
                               susesch, 65535, to_number(null), s.ksusesch),  s.k
                               susepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepc
                               h, 65535, to_number(null), s.ksusepch),  s.ksuseap
                               p, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli,
                                s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s
                               .ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.
                               ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,
                               'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),de
                               code(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARS
                               E','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksu
                               segrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',de
                               code(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')
                               ),decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(
                               bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),de
                               code(bitand(s.ksusepxopt,32),32,'FORCED',decode(bi
                               tand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),
                               s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,429
                               4967295,'UNKNOWN',  4294967294, 'UNKNOWN',42949672
                               93,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'
                               NOT IN WAIT','VALID'),decode(s.ksuseblocker, 42949
                               67295,to_number(null),4294967294,to_number(null),
                               4294967293,to_number(null), 4294967292,to_number(n
                               ull),4294967291,  to_number(null),bitand(s.ksusebl
                               ocker, 2147418112)/65536),decode(s.ksuseblocker, 4
                               294967295,to_number(null),4294967294,to_number(nul
                               l), 4294967293,to_number(null), 4294967292,to_numb
                               er(null),4294967291,  to_number(null),bitand(s.ksu
                               seblocker, 65535)),s.ksuseseq, s.ksuseopc,e.ksledn
                               am, e.ksledp1, s.ksusep1,s.ksusep1r,e.ksledp2, s.k
                               susep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e
                               .ksledclassid,  e.ksledclass#, e.ksledclass, decod
                               e(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksuse
                               tim/10000),0,-1,round(s.ksusetim/10000))), s.ksuse
                               wtm,decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED U
                               NKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(r
                               ound(s.ksusetim/10000),0,'WAITED SHORT TIME','WAIT
                               ED KNOWN TIME')),s.ksusesvc, decode(bitand(s.ksuse
                               flg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.
                               ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.k
                               suseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x
                               $ksled e where bitand(s.ksspaflg,1)!=0 and bitand(
                               s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$FIXED_VIEW_DEFINITION        select  VIEW_NAME , VIEW_DEFINITION from GV$FIXED_
                               VIEW_DEFINITION where inst_id = USERENV('Instance'
                               )

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2   where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$FIXED_VIEW_DEFINITION       select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i,
                                x$kqfvt t where i.indx = t.indx



Conclusion:
The regular Dynamic views('V$') are created  based on 'Gv$' and these 'Gv$' views are based on 'X$' views
These are permanent tables/views.The X$ tables are generated when ever you instance started.
These tables are accessible to the sys User only.
These 'X$' are not even modifiable By the Super User 'SYS' 
Those are "FIXED"
Never ever try to expertise on these view on your production environment 
which will ruin you DB  complete. ;)

Posted in Oracle Server Administration | 16 Comments »

Put the Database in ARCHIVE mode and enable flashback mode

Posted by Sriram Sanka on December 11, 2010


SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

SQL> alter system set db_recovery_file_dest='d:\oracle\product\10.2.0\flash_recovery_area';

System altered.

.

SQL> alter system set log_archive_dest_1='location=d:\archive\sriram';

System altered.

SQL> alter system set log_archive_dest_10='location=use_db_recovery_file_dest';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 272629760 bytes
Fixed Size 1248504 bytes
Variable Size 117441288 bytes
Database Buffers 150994944 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>
SQL> alter database open;

Database altered.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 67.58 0 87
BACKUPPIECE 31.95 0 2
IMAGECOPY 0 0 0
FLASHBACKLOG .38 0 1

6 rows selected.

Posted in Oracle Server Administration | Leave a Comment »

Read the file

Posted by Sriram Sanka on December 10, 2010


A Master piece from http://laurentschneider.com

An excellent piece of code handy for DBA`s


SQL> SELECT s.sid,
2 s.serial#,
3 pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
4 '_ora_' || p.spid || '.trc' AS trace_file
5 FROM v$session s,
6 v$process p,
7 v$parameter pa
8 WHERE pa.name = 'user_dump_dest'
9 AND s.paddr = p.addr
10 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
11 /

SID SERIAL#
---------- ----------
TRACE_FILE
--------------------------------------------------------------------------------
147 2101
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP\orcl_ora_1000.trc

SQL>
SQL> sho parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCLE\UDUMP
SQL> CREATE DIRECTORY udump_dest AS
2 'D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP';

Directory created.

SQL> CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 dest_loc CLOB;
5 src_loc BFILE;
6 ret VARCHAR2 (4000);
7 BEGIN
8 src_loc := BFILENAME ('UDUMP_DEST', file_name);
9 DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
10 DBMS_LOB.createtemporary (dest_loc, TRUE);
11 DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
12 ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
13 DBMS_LOB.CLOSE (src_loc);
14 RETURN ret;
15 END;
16 /

Function created.

 

SQL>

SQL> SELECT get_tracefile (‘orcl_ora_1000.trc’) FROM DUAL;
GET_TRACEFILE(‘ORCL_ORA_1000.TRC’)——————————————————————————–Dump file d:\oracle\product\10.2.0\admin\orcle\udump\orcl_ora_1000.trcFri Dec 10 22:54:36 2010ORACLE V10.2.0.1.0 – Production vsnsta=0vsnsql=14 vsnxtr=3Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining optionsWindows XP Version V5.1 Service Pack 3CPU                 : 1 – type 586, 1 Physical CoresProcess Affinity    : 0x00000000Memory (Avail/Total): Ph:269M/959M, Ph+PgF:1265M/2314M, VA:1606M/2047MInstance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 1000, image: ORACLE.EXE (SHAD)………………….

…………………………………..ETC

Posted in Oracle Server Administration, Uncategorized | Leave a Comment »

Handy Package DBMS_UTILITY

Posted by Sriram Sanka on December 10, 2010


Really Its  great handy package for Oracle People!

We can use this in so many different scenarios.

SQL> DECLARE
2 parnam VARCHAR2(256);
3 intval BINARY_INTEGER;
4 strval VARCHAR2(256);
5 partyp BINARY_INTEGER;
6 BEGIN
7 partyp := dbms_utility.get_parameter_value('max_dump_file_size',
8 intval, strval);
9 dbms_output.put('parameter value is: ');
10 IF partyp = 1 THEN
11 dbms_output.put_line(strval);
12 ELSE
13 dbms_output.put_line(intval);
14 END IF;
15 IF partyp = 1 THEN
16 dbms_output.put('parameter value length is: ');
17 dbms_output.put_line(intval);
18 END IF;
19 dbms_output.put('parameter type is: ');
20 IF partyp = 1 THEN
21 dbms_output.put_line('string');
22 ELSE
23 dbms_output.put_line('integer');
24 END IF;
25 END;
26 /
parameter value is: UNLIMITED
parameter value length is: 9
parameter type is: string

PL/SQL procedure successfully completed.

SQL> declare
2 ver varchar2(100);
3 com varchar2(100);
4 begin
5 dbms_utility.db_version(ver,com);
6 dbms_output.put_line(ver||'-'||com);
7 end;
8 /
10.2.0.1.0-10.2.0.1.0

PL/SQL procedure successfully completed.
SQL> declare
2 id number;
3 str varchar2(40);
4 begin
5 id:=dbms_utility.get_parameter_value('compatible',id,str);
6 dbms_output.put_line(str);
7 end;
8 /
10.2.0.1.0

PL/SQL procedure successfully completed.

SQL> create or replace procedure sriram
2 as
3 begin
4 NULL;
5 end;
6 /

Procedure created.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM

SQL> exec dbms_utility.invalidate(52565);

PL/SQL procedure successfully completed.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 INVALID SRIRAM

SQL> exec dbms_utility.validate(52565);

PL/SQL procedure successfully completed.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM

SQL>

We can use This package in different situations like to compile schema objects analtze database etc…..

Posted in Oracle Server Administration | Leave a Comment »

Get the trace file name and read it

Posted by Sriram Sanka on December 10, 2010


SQL> COL trace_file FOR A75

SQL> col VALUE format a50

SQL> col name format a20

SQL> select name,value

2  from v$parameter

3  where name='user_dump_dest';

NAME                 VALUE

-------------------- --------------------------------------------------
user_dump_dest       D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP
SQL> SELECT s.sid,

            s.serial#,

            pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||

            '_ora_' || p.spid || '.trc' AS trace_file

     FROM   v$session s,

            v$process p,

            v$parameter pa

     WHERE  pa.name = 'user_dump_dest'

     AND    s.paddr = p.addr

     AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL#
---------- ----------
TRACE_FILE
---------------------------------
  143        573 D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP\sriramtest_ora_2280.trc
SQL> alter database backup controlfile to trace;

Database altered.

---You can read the file on windows using the below command
SQL> host notepad D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP\sriramtest_ora_2280.trc

Posted in Oracle Server Administration | Leave a Comment »

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Posted by Sriram Sanka on December 4, 2009


SQL> conn raghu/raghu
Connected.
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report


SQL> conn / as sysdba
Connected.
SQL> grant plustrace to raghu;
grant plustrace to raghu
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist.

SQL> @ %oracle_home%\sqlplus\admin\plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$session to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> GRANT PLUSTRACE TO raghu;

Grant succeeded.
SQL> conn raghu/raghu
Connected.

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
---------
04-DEC-09

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

Posted in Oracle Server Administration | 1 Comment »

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBAKevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

%d bloggers like this: