Feeds:
Posts
Comments

Posts Tagged ‘Oracle’

In Memory Database Concept….!!!!

As we are discussing the Oracle 12c new features In Memory is most popular feature of Oracle 12c.

Memory optimization is always my favorite topic since I started working on Oracle. Optimization means whole idea to wonder around SGA and tune to gain max DB performance.

And here tuning means what most used data should be read from memory instead of from hard disk.

So well tuned database almost 95% data request find required data already in MEMORY.

Now let’s talk about data storage raw / column format

Generally there are two types of databases OLTP & DSS

OLTP: Which operates on few rows but many columns and it works best on row format.

DSS: Accessing few columns of many rows, works best on the column format.

How it will be if we get both OLTP and DSS technique in single product

So here it is Oracle 12c In Memory based on dual format data store.

Generally data are stored on disk in row format only and whenever data requested for read / writes, they will be loaded into traditional Row Store (SGA – Buffer Cache). And whenever data requested for only read operation they will be populated into new In Memory Column Store. So this population includes transformation from row to columnar format.

So it means whenever transaction includes INSERTS, UPDATES or DELETES with commit, new data will be immediately and simultaneously appear row store and the In Memory Column Store. So both the store format transactionally consistent.

And most imp this approach doesn’t require more memory.

Advantages of this approach:

  • There is no need to modify / change application. All existing applications run unchanged in new architecture.
  • There is no need to modify the database. Oracle 12c In Memory option can be implemented without Database migration or table reorganization.
  • There are no limits for database or table sizes. The Oracle Database 12c In- Memory option can be used with databases and systems of any size.
  • Therefore there is no need to change the infrastructure. The new In Memory feature can be implemented on existing hardware.

This In Memory option is compatible with other features such as table compression, table encryption, and table partitioning also with RAC & Data Guard.

Now how to use this feature ….!!!!

This is very easy to use

Assign the value to new initialization parameter inmemory_size to define the size of the In Memory column store.

SQL> ALTER SYSTEM SET inmemory_size= 10G scope=spfile;

And now select table that you want to be available in te In Memory column store:

SQL> ALTER TABLE T1 INMEMORY;

And it’s done …. Ready to move!!!!

Note: As a static pool any changes to INMEMORY_SIZE will not take effect until DB instance restarted.

In Memory must have minimum size of 100MB.

INMEMORY attribute can be specified on a tablespace , table , (sub)partitioned or materialized view.

If enable for tablespace level then all tables and materialized views of respective tablespace will be enable for the IN MEMORY column store by default.

 Above statement doesn’t change or populate table data into In Memory column store.

It just tells the DB that you want the table data to be available In Memory column store at certain point in time.

But point in time means On Demand OR during DB startup time.

Here on Demand means table populated into In Memory column store whenever they are refer by any query.

OR DBA can define this job should executed during the Db startup.

SQL> ALTER TABLE TAB1 INMEMORY PRIORITY CRTICAL;

So let us discus bout this priority criteria !!!!

CRITICAL: Object is populated immediately after the DB opened.

HIGH: If space remains available in IN MEMORY column store then next objects will be this one.

MEDIUM:  These objects will be populated after CRITICAL & HIGH.

LOW: After CRITICAL , HIGH & MEDIUM.

NONE: Objects will be populated after they are scanned for the first time.

Following objects cannot be populated in to IN MEMORY

  • Any objects owned by SYS user and stored in SYSTEM or SYSAUX tablespace.
  • INDEX organized tables (IOTs).
  • Clustered tables.
  • LONG data types also not supported.
  • LOBs also not supported.

IN MEMORY Compression….!!!!

Compression is considered only as a space-saving mechanism. However, data populated into the IM column store is compressed using a new set of compression algorithms that not only help to save space but also improve query performance. The new Oracle In-Memory compression format allows queries to execute directly against the compressed columns. This means all scanning and filtering operations will execute on a much smaller amount of data. Data is only decompressed when it is required for the result set.

As I am in SAP partner company always heard SAP doing research on this / that but thought let us discuss what Oracle research team doing considering SAP HANA’s  market. So as I am working on SAP HANA let us discus about difference between Oracle Exadata & SAP HANA in next article.

Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for success, always fight even knowing your defeat is certain….!!!!

Read Full Post »

Friends after long time back to blog …. due to lots of up / down in life I was away from blog …. but promise from here onward will be in touch with you guys and will always reply your queries and suggestions …

Most of Jr. DBA while chating ask the question abt archive enable / disable query , so just coming with basic but most useful post for Jr.DBA.

Also please note , whenever we enabling the DB in archive mode as DBA we need to monitor the archive space as well.

In large database environmnet we need to take regular archive so backup , so generaly DBA use to schedule it to keep the free space in archive location.

Otherwise DB may go in hung state if archive location is fulled.

In oraganisation some time archive file is manually deleted by DBA it self or we schedule it to delete once it is backup.

This is how to enable archiving:
 

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Feb 26 09:57:43 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 – 64bit Production
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/SID/oraarch
Oldest online log sequence     2442
Current log sequence           2444

 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
Total System Global Area 289406976 bytes
 Fixed Size 1248576 bytes
 Variable Size 96469696 bytes
 Database Buffers 184549376 bytes
 Redo Buffers 7139328 bytes
 Database mounted.
 SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/SID/oraarch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
 SQL>
 
=================================================
 
This is how to disable archiving:
 
SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
Total System Global Area 289406976 bytes
 Fixed Size 1248576 bytes
 Variable Size 100664000 bytes
 Database Buffers 180355072 bytes
 Redo Buffers 7139328 bytes
 Database mounted.
 SQL> alter database noarchivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/SID/oraarch
Oldest online log sequence     26
Current log sequence           28
SQL>

 

I Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

 

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for suceess, always fight even knowing your defeat is certain….!!!!

 

Read Full Post »

Today i face this issue after Oracle 11g upgrade and posting it for you as it is.

Problem:

The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 28 11:34:29 2012

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

Connected to an idle instance.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_SID

Reason :

Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You’ll need to give it some help to find your listener.

Solution:

Simply add an entry to the servers tnsnames.ora pointing at the listener. As mention below

LISTENER_SID.WORLD=
(DESCRIPTION =

tnsping LISTENER_SID.WORLD

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 – Production on 28-SEP-2012 13:13:36

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

Used parameter files:
/oracle/SID/112_64/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU = 2768) (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = sidb00) (PORT = 1527))) (CONNECT_DATA = (SID = SID) (GLOBAL_NAME = SID.WORLD)))
OK (20 msec)

Now it is working. Also wana add one more point.

make sure that parameter by name *.local_listener=’LISTENER_SID’ in pfile as it is mention.

 I Hope this article helped to you. I am expecting your suggestions/feedback. 
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!

Read Full Post »

Note: One of our visitors  Lokesh Suryawanshi asked this question by posting a comment. Thanks Lokesh for your contribution. As promised i did patch upgrade on yesterday and here is the details of it. Keep visiting/commenting!

Required Software and Installable are available in “ */*/Patch” directory

$HOME = /oracle/SAM

$ORACLE_HOME = /oracle/SAM/102_64

1. 10.2.0.4 Patchset (patchset file is already extracted)

2. OPatch version 10.2.0.5.0

 3. MOPatch version2.1.7

Note:- All Required Software are kept in $HOME/Patch Directory and extract files in following order

1) Mopatch-2-1-7.zip in $ORACLE_HOME directory Note:- Unzip After 10.2.0.4 patchset installation.

2) OPatch10205_Generic_v0.zip file in $ORACLE_HOME directory Note:- Unzip After 10.2.0.4 patchset installation.

3) Patchset_10204.zip file in current directory i.e. in Patch Path

Step 1.-> Check upgradation prerequisites:

1.1 Stop SAP. (No need while applying on BCV)

 1.2:- check oratab and orainventory location at “/var/opt/oracle” and entry of “SAM:/oracle/SAM/102_64: N” in oratab file, If not make an entry in oratab file.

1.3 :- To check invalid objects and Tablepsace prerequisites Sql> @?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Sql> @?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Important Note:- After running prerequisites script you seen some public objects are invalid in SYS schema and other schemas(SAPSR3). Here we are only concern about SYS objects. Which  is known and need to be compiled. Next step to run utlrp script to get compiled invalid objects.

1.2:- Run utlrp.sql to validate invalid objects.

Purpose:- To ensure there is no object invalid found in SYS schema otherwise probabilities patch up gradation failed.  Run below scripts

 Sql>@?/rdbms/admin/utlrp.sql

Result:- In most cases result should report 0 in SYS schemas.( invalid objects). IF NOT ABORT OR STOP INSTALLATION PROCESS

Again Run Prerequistes script

Sql>@?/rdbms/admin/utlu102i.sql (? Sign indicates ORACLE_HOME path)

Note:- No Objects are invalid in SYS and SAPSR3 Schemas and DB is Ready for Upgradation. Next step to Shutdown Database and related Components.

Step 2:- Shutdown database listener,OEM and all component related to Oracle

 2.1 Gracefully stop Database instance and Listener process (Don’t use abort or kill Oracle process)

2.2 OEM is not valid when you test in BCV server as this is not configured for same but in Production you have to gracefully stop.

 Sql>shutdown immediate;

Log off from sql prompt

Orap22>lsnrctl stop

 Check the oracle processes by executing command ps –ef |grep -i ora_

Check the listener process by executing command ps –ef |grep -i LIS

Step: 3 Run OPatch

 3.1 Run OPatch lsinventory to ensure ora inventory location

 Orap22>/oracle/SAM/102_64/OPatch/opatch lsinventory

Step:-4  start 10.2.0.4 Patch Binary upgradation process.

Go to Patch directory in Disk1 directory

1.1   Start XBrowser

1.2   Set Display Env type command “export DISPLAY=your Desktop/Laptop IP:0.0”

1.3   ./oracle/SAM/Patch/Disk1/runInstaller

4.4 Verified ORACLE_HOME

Purpose:- To Validate correct ORACLE_HOME where 10.2.0.4 binary is going to update.

Workaround:- If not seen correct ORACLE_HOME abort and verify oraInventory location

4.5 Installaler summary

4.6 sh /oracle/SAM/102_64/root.sh from another session as root user

a)      Go to ORACLE_HOME run command from $ Prompt sh root.sh

Step-5:-  Run MOPatch for CPU patches(SBP)

5.1) Unzip Mopatch-2-1-7.zip in $ORACLE_HOME directory from Patch location

Orap22>unzip /oracle/SAM/Patch/Mopatch-2-1-7.zip /oracle/SAM/102_64/.

5.2) Unzip OPatch 10205_Generic_v0.zip file in $ORACLE_HOME directory Patch location

Orap22>unzip /oracle/SAM/Patch/OPatch10205_Generic_v0.zip  /oracle/SAM/102_64/.

Note:-  Run OPatch version to confirm OPatch version 10.2.0.5.0 which is required to run SAP Supplied Bundle Patch

CAUTION:- Before running mopatch ensure OPatch version:10.2.0.5.0

Otherwise MOpatch run but some patch failed which required OUI version 10.2.0.5.0

Verified OPatch lsinventory

Purpose:-  To Ensure Inventory of Patches which we applied recently. Next Step to Upgrade Database.

Orap22>Sh /oracle/SAM/102_64/MOPatch/mopatch.sh –v –s SAP_102048_201105_SOL64.zip

Step6:- Upgrade Database

6.1 startup database in upgrade mode

Sql>startup upgrade

 check oracle version 10.2.0.4 after loggin into sql prompt.

6.2 Run catupgrd.sql

Execute SQL from sql command prompt

Sql>spool catupgrd.log

Sql> @?/rdbms/admin/catupgrd.sql

Step 7:- Shutdown Database and restart in normal mode.

Sql>shutdown immediate;

Sql>startup

Step 8:- Run utlrp to validate any invalid object while upgrading

Sql> @?/rdbms/admin/utlrp.sql

Step:-9 Shutdown and restart database to take new parameter in effects.

Step:-10 Start listener and OEM Test and release to user.

Coming post will be on Oracle most popular wait events and there solutions, till that enjoy the Oracle jou5rney….. 🙂

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!

Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!

Read Full Post »

Hi,today one developer got this error while connecting to new production server.

Problem:

User was not able to connect to production server throght the application the error was,

ORA-12505 Connection refused, the specified SID (retaildb) was not recognized by the Oracle server.”

C:Documents and SettingsAdministrator>tnsping Global

TNS Ping Utility for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-20
11 12:43:33

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
E:oracleRILDEVnetworkadminsqlnet.ora

TNS-03505: Failed to resolve name

C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-2011 12:44:
26

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=romavser)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0
         LOCAL SERVER
The command completed successfully

C:Documents and SettingsAdministrator>

 

And Listener.ora entry was :

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = romavser)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

Solution:

 had a service name “SAMDB”. When I tried connect to that service it always gave
ORA-12505, TNS:listener does not currently know of SID given in connect …

So I edited the listener.ora, added one more SID_DESC, restarted the listener. Now I can connect to the “SAMDB” database. Below is the updated listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:oracleSAMDEV)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = SAMDB)
      (ORACLE_HOME = E:oracleSAMDEV)
      (SID_NAME = SAMDB)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.231.38.15)(PORT = 1521))
     )
 
 )
)

Restarted the listener & error got resolved.

Read Full Post »

Dear all, yesterday I face small issue which I would like to share with you guy’s.It was temporary tablespace issue.This was development server for storage was recently changed.As mount point was changed but control file was having the previous temp file details so got the error while exporting schema.Just drop the old tempfile and created new one. Issue got resolved after that.

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 reporter_sam_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 reporter_sam_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:13:42 2011

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > SAM1_orig_exp31052011.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > y

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > y

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users …

User to be exported: (RETURN to quit) > SAM1

User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export SAM1’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

EXP-00056: ORACLE error 1157 encountered

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

EXP-00000: Export terminated unsuccessfully

bash-2.05$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jun 1 04:16:46 2011

 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

 SQL> select * from v$tempfile;

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

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

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

——————————————————————————–

         1       8.2892E+12 07-FEB-11         24          2 ONLINE  READ WRITE

         0          0   2147483648       8192

/oradata/oracle10g/oradata/temp/temp2.dbf

          3       8.2892E+12 07-FEB-11         24          1 ONLINE  READ WRITE

         0          0   3221225472       8192

/oradata/oracle10g/oradata/temp/temp1.dbf

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

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

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

——————————————————————————–

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                          *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

          3 ONLINE

/oradata/oracle10g/oradata/temp/temp1.dbf

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp1.dbf

/oradata/oracle10g/oradata/temp/temp1.dbf: No such file or directory

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp2.dbf

/oradata/oracle10g/oradata/temp/temp2.dbf: No such file or directory

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                           *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp1.dbf’ drop;

 Database altered.

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp1.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

ERROR:

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp2.dbf’ drop;

 Database altered.

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp2.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

 TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

        MB

———-

TEMP1

/DATA1/temp1.dbf

     10240

 TEMP1

/DATA1/temp2.dbf

     10240

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/DATA1/temp2.dbf

          2 ONLINE

/DATA1/temp1.dbf

 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

bash-2.05$ pwd

/DATA1

bash-2.05$ cd –

/DATA

bash-2.05$ cd sam_cxl

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 sam_cxl_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 sam_cxl_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

 Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:35:18 2011

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

 Username: / as sysdba

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

 Export file: expdat.dmp > SAm1_orig_exp31052011.dmp

 (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

 Export grants (yes/no): yes >

 Export table data (yes/no): yes >

 Compress extents (yes/no): yes >

 Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

 About to export specified users …

User to be exported: (RETURN to quit) > SAM1

 User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export ISCM’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SAM1’s tables via Conventional Path …

. . exporting table       SAM_BERTHING_GRADE_ARMS          5 rows exported

.

.

.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.

Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

As promise in Oracle 9i Architecture post here is new post for how SQL statement processe internaly. This is for them those whi are planning to start career as Oracle DBA. If they want to make clear picture of Oracle Architecture then first of all they need to understand how sql statement procees in oracle.These are the statement which we are going to run.

Sqlplus scott/tiger@prod

SQL>select * from emp;

SQL>update emp set sallary=30000 where empid=10;

SQL>commit;

So we will understand what is happening internaly

  1. Once we hit sqlplus statement as above client process(user) access sqlnet listener.
  2. Sql net listener confirms that DB is open for buisness & create server process.
  3. Server process allocates PGA.
  4. ‘Connected’ Message returned to user.
  5. SQL>select * from emp;
  6. Server process checks the SGA to see if data is already in buffer cache.
  7. If not then data is retrived from disk and copied into SGA (DB Cache).
  8. Data is returned to user via PGA & server process.
  9. Now another statement is   SQL>Update emp set sallary=30000 where empid=10;
  10. Server process (Via PGA) checks SGA to see if data is already there in buffer cache.
  11. In our situation chances are the data is still in the SGA (DB Cache).
  12. Data updated in DB cache and mark as ‘Dirty Buffer’.
  13. Update employee placed into redo buffer.
  14. Row updated message returned to user
  15. SQL>commit;
  16. Newest SCN obtained from control file.
  17. Data in DB cache is marked as ‘Updated and ready for saving’.
  18. commit palced into redo buffer.
  19. LGWR writes redo buffer contents to redo log files & remove from redo buffer.
  20. Control file is updated with new SCN.
  21. Commit complete message return to user.
  22. Update emp table in datafile & update header of datafile with latest SCN.
  23. SQL>exit;
  24. Unsaved changes are rolled back.
  25. Server process deallocates PGA.
  26. Server process terminates.
  27. After some period of time redo log are archived by ARCH process.    

Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.

Experts are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/02/17/oracle-9i-architecture/

Read Full Post »

When a SQL is submitted by a user to Oracle database, it never happens that Oracle will execute the SQL continuously at one go. Oracle process never get to work on the execution of statement without any interruptions. Often the process has to pause or wait for some event or some other resource to be released. Thus active Oracle process is doing one of the following thing at any point of time.

The process is executing the SQL statement.

The process is waiting for something (for example, a resource such as a database buffer or a latch). It could be waiting for an action such as a write to the buffer cache to complete.
That’s why the response time—the total time taken by Oracle to finish work—is correctly
defined as follows:

response time = service time + wait time

So only part of the time is spend by oracle process to actually “do” some thing. Rest of the time process just wait for some resource to get freed up. It can be waiting log writter process or database writter process or any other resources.

The wait event may also be due to unavailable buffers or latches.

Four dynamic performance views contain wait information: V$SESSION, V$SYSTEM_EVENT, V$SESSION_EVENT, and V$SESSION_WAIT. These four views list just about all the events the instance was waiting for and the duration of these waits. Understanding these wait events is essential for resolving performance issues.

There are different wait classes defined in database. Each class will contain different wait events. There are around 860 wait events defined in Oracle database 10g and are classified under different wait classes.

Some of the main wait classes includes:

  • Administrative: Waits caused by administrative commands, such as rebuilding an index, for example.
  • Application: Waits due to the application code.
  • Cluster: Waits related to Real Application Cluster management.
  • Commit: Consists of the single wait event log file sync, which is a wait caused by commits in the database.
  • Concurrency: Waits for database resources that are used for locking; for example, latches.
  • Configuration: Waits caused by database or instance configuration problems, including a low shared-pool memory size, for example.
  • Idle: Idle wait events indicate waits that occur when a session isn’t active; for example, the ‘SQL*Net message from client’ wait event.

You can see the complete list of wait classes using V$SESSION_WAIT_CLASS dynamic performance view.

Analyzing Instance Performance

You can check the percentage of time spend by the database in waiting for resource and percentage of time spend by database in actual execution.

SQL> SELECT METRIC_NAME, VALUE
             FROM V$SYSMETRIC
            WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,
           ‘Database Wait Time Ratio’) AND
           INTSIZE_CSEC =
           (select max(INTSIZE_CSEC) from V$SYSMETRIC);

METRIC_NAME                                                           VALUE
—————————————————————- ———-
Database Wait Time Ratio                                         15.6260647
Database CPU Time Ratio                                          84.3739353

If the database performance shows that ‘Database Wait Time Ratio’ is greater then ‘Database CPU Time Ratio’ or the value for ‘Database Wait Time Ratio’ is quite significant, then in that case you need to dig inside to get the information about where exactly oracle is waiting. You need to basically find the type of wait. This will give you root cause. Once you get the root cause you can work to fix the same.

you can determine the total waits and percentage of waits by wait class.

SELECT WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_TOTWAITS,
ROUND((TIME_WAITED / 100),2) TOT_TIME_WAITED,
round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
FROM
(select WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED
FROM V$SYSTEM_WAIT_CLASS
WHERE WAIT_CLASS != ‘Idle’),
(select sum(TOTAL_WAITS) SUM_WAITS,
sum(TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != ‘Idle’)
ORDER BY PCT_TIME DESC;

WAIT_CLASS           TOTAL_WAITS PCT_TOTWAITS TOT_TIME_WAITED   PCT_TIME
——————– ———– ———— ————— ———-
System I/O                180300        19.96          3008.8      49.53
Commit                     67855         7.51         1302.46      21.44
User I/O                  291565        32.28         1056.55      17.39
Application                 3637           .4          596.66       9.82
Other                      15388          1.7            67.4       1.11
Concurrency                 1264          .14           38.12        .63
Network                   343169        37.99            3.86        .06
Configuration                 22            0               1        .02

8 rows selected.

In the above output percentage of time waited (last column) is more important and gives the correct picture of the impact due to wait. Example if we see total number of Network waits are large but the actual percentage of time contributed to the wait is very less (0.06%).

The key dynamic performance tables for finding wait information are the V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, and the V$SESSION views. The first two views show the waiting time for different events.

V$SYSTEM_EVENT

The V$SYSTEM_EVENT view shows the total time waited for all the events for the entire system since the instance started up. The view doesn’t focus on the individual sessions experiencing waits, and therefore it gives you a high-level view of waits in the system. You can use this view to find out that the top instance-wide wait events are. You can calculate the top n waits in the system by dividing the event’s wait time by the total wait time for all events.

select EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS from V$SYSTEM_EVENT
where wait_class != ‘Idle’
order by time_waited desc;

EVENT                          TOTAL_WAITS TIME_WAITED WAIT_CLASS
—————————— ———– ———– ——————–
log file parallel write             128953      210308 System I/O
log file sync                        67904      130313 Commit
db file sequential read             259065       73686 User I/O
enq: TX – row lock contention          226       59080 Application
control file parallel write          28282       57929 System I/O
db file parallel write               19155       32924 System I/O
db file scattered read               31841       30925 User I/O
os thread startup                       95        3262 Concurrency
rdbms ipc reply                        485        2116 Other
PX Deq: Signal ACK                    1971        1103 Other
local write wait                       245         864 User I/O

we can get the session level waits for each event using V$SESSION_EVENT view. In this view the TIME_WAITED is the wait time per session.

V$SESSION_EVENT

select sid, EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS
from V$SESSION_EVENT
where WAIT_CLASS != ‘Idle’
order by TIME_WAITED;

  SID EVENT                          TOTAL_WAITS TIME_WAITED WAIT_CLASS
———- —————————— ———– ———– ————
390 os thread startup                       55      1918   Concurrency
393 db file sequential read              10334      4432   User I/O
396 db file parallel write                8637      14915  System I/O
397 db file parallel write               10535      18035  System I/O
394 control file parallel write          28294      57928  System I/O
395 log file parallel write             129020      210405 System I/O

As we can see from above output that session 395 is having maximum wait time because of system I/O. Here system I/O is the I/O because of background processes like DBWR and LGWR etc.

You can get all the database wait events from V$EVENT_NAME and the related meaning of all the wait events available in oracle 10g by checking this documentation link.

V$SESSION_WAIT

The third dynamic view is the V$SESSION_WAIT view, which shows the current waits or just completed waits for sessions. The information on waits in this view changes continuously based on the types of waits that are occurring in the system. The real-time information in this view provides you with tremendous insight into what’s holding up things in the database right now. The
V$SESSION_WAIT view provides detailed information on the wait event, including details such as file number, latch numbers, and block number. This detailed level of information provided by the V$SESSION_WAIT view enables you to probe into the exact bottleneck that’s slowing down the database. The low-level information helps you zoom in on the root cause of performance problems.

SQL> select sid, event, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE from v$session_wait
2  where wait_class != ‘Idle’;

       SID EVENT                          WAIT_CLASS            WAIT_TIME
———- —————————— ——————– ———-
SECONDS_IN_WAIT STATE
————— ——————-
337 SQL*Net message to client      Network                      -1
0 WAITED SHORT TIME

Here wait time -1 means that session has waited for less then 1/100th of a second.
You can get the complete wait information for a particular session using V$SESSION view. You can get SQLID of the sql, which is causing wait.

V$SESSION

For getting the wait statistics you can go with the following methodology.

  • First, look at the V$SYSTEM_EVENT view and rank the top wait events by the total amount of time waited, as well as the average wait time for that event.
  • Next, find out more details about the specific wait event that’s at the top of the list. You can check V$WAITSTAT view for the same. Check the type of wait this view is showing. If the wait is due to undo header or undo block then wait is related to undo segment.
  • Finally, use the V$SESSION view to find out the exact objects that may be the source of a problem. For example, if you have a high amount of db file scattered read-type waits, the V$SESSION view will give you the file number and block number involved in the wait events.

In V$SESSION we have a column called BLOCKING_SESSION_STATUS. IF this column value is ‘valid’, then we can presume that corresponding session is getting blocked.

V$SESSION_WAIT_HISTORY

The V$SESSION_WAIT_HISTORY view holds information about the last ten wait events for each active session. The other wait-related views, such as the V$SESSION and the V$SESSION_WAIT, show you only the wait information for the most recent wait. This may be a short wait, thus escaping your scrutiny.

SQL> select sid from v$session_wait_history
2  where wait_time = (select max(wait_time) from v$session_wait_history);

      SEQ# EVENT                            P1         P2         P3  WAIT_TIME
———- ———————— ———- ———- ———- ———-
1 rdbms ipc message            180000          0          0     175787
2 rdbms ipc message            180000          0          0     175787
3 rdbms ipc message            180000          0          0      60782
4 rdbms ipc message            180000          0          0     175787
5 rdbms ipc message            180000          0          0     138705
6 db file sequential read           1        368          1          0
7 rdbms ipc message            180000          0          0     158646
8 db file sequential read           1        368          1          0
9 db file sequential read           1         73          1          0
10 db file sequential read           1         30          1          0

Note that a zero value under the WAIT_TIME column means that the session is waiting for a specific wait event. A nonzero value represents the time waited for the last event.

V$ACTIVE_SESSION_HISTORY

The V$SESSION_WAIT view tells you what resource a session is waiting for. The V$SESSION view also provides significant wait information for active sessions. However, neither of these views provides you with historical information about the waits in your instance. Once the wait is over, you can no longer view the wait information using the V$SESSION_WAIT view. The waits are so fleeting that by the time you query the views, the wait in most times is over. The new Active Session History (ASH) feature, by recording session information, enables you to go back in time and review the history of a performance bottleneck in your database. Although the AWR provides hourly snapshots
of the instance by default, you won’t be able to analyze events that occurred five or ten minutes ago, based on AWR data. This is where the ASH information comes in handy. ASH samples the V$SESSION view every second and collects the wait information for all active sessions. An active session is defined as a session that’s on the CPU or waiting for a resource. You can view the ASH session statistics through the view  V$ACTIVE_SESSION_HISTORY, which contains a single row for each active session in your instance. ASH is a rolling buffer in memory, with older information being overwritten by new session data. Every 60 minutes,the MMON background process flushes filtered ASH data to disk, as part of the hourly AWR snapshots. If the ASH buffer is full, the MMNL background process performs the flushing of data. Once the ASH data is flushed to disk, you won’t be able to see it in the V$ACTIVE_SESSION_HISTORY view. You’ll now have to use the DBA_HIST_ACTIVE_SESS_HISTORY view to look at the historical data.

obtaining the objects with highest waits

SELECT a.current_obj#, o.object_name, o.object_type, a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
dba_objects o
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.current_obj# = o.object_id
GROUP BY a.current_obj#, o.object_name, o.object_type, a.event
ORDER BY total_wait_time;

 OBJECT_NAME                OBJECT_TYPE         EVENT                  TOTAL_WAIT_TIME
——————–       ——————- ———————– —————-
FND_LOGINS                 TABLE               db file sequential read   47480
KOTTB$                     TABLE               db file sequential read   48077
SCHEDULER$_WINDOW          TABLE               db file sequential read   49205
ENG_CHANGE_ROUTE_STEPS_TL  TABLE               db file sequential read   52534
JDR_PATHS_N1               INDEX               db file sequential read   58888
MTL_ITEM_REVISIONS_B       TABLE               SQL*Net more data to client

select p1text, p1, p2text, p2, p3text, p3, a.event
from v$active_session_history a
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.current_obj# = 1938000

P1TEXT     P1          P2TEXT     P2   P3TEXT          P3  EVENT
——–   —         ——- ——- ———— —— ————————
file#      71          block#    4389  blocks           1  db file sequential read
file#      187         block#   89977  blocks           1  db file sequential read
file#      80          block#   79301  blocks           1  db file sequential read
driver id  675562835   #bytes       1  0
file#      11          block#     831  blocks           1  db file sequential read
driver id  675562835   #bytes       1  0

so we can see few history wait events for a particular object in database. We can get the segment stats for this object. Finally we can got to some conclusing and implementaing the solution to reduce the wait. For example if it is a ‘db file sequential read’ wait then

  • Increase buffer cache size, but this wont help much. For this to do, you need to check cache miss percentages.
  • Check the query and optimize it, so that it can read less number of blocks
  • Increase freelists for that segment

some important wait events

The following query lists the most important wait events in your database in the last 15 minutes:

SELECT a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time between
sysdate – 30/2880 and sysdate
GROUP BY a.event
ORDER BY total_wait_time DESC;

EVENT                                                            TOTAL_WAIT_TIME
—————————————————————- —————
enq: TX – row lock contention                                          877360289
816854999
TCP Socket (KGAS)                                                       13787430
SQL*Net break/reset to client                                            6675324
db file sequential read                                                  2318850
control file parallel write                                              1790011
log file parallel write                                                  1411201
db file scattered read                                                     62132
os thread startup                                                          39640
null event                                                                     0

Users with the Most Waits

The following query lists the users with the highest wait times within the last 15 minutes:

SELECT s.sid, s.username,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
v$session s
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.session_id=s.sid
GROUP BY s.sid, s.username
ORDER BY total_wait_time DESC;

       SID USERNAME                       TOTAL_WAIT_TIME
———- —————————— —————
773 APPS                                 877360543
670 APPS                                 374767126
797                                       98408003
713 APPS                                  97655307
638 APPS                                  53719218
726 APPS                                  39072236
673 APPS                                  29353667
762 APPS                                  29307261
746 APPS                                  29307183
653 APPS                                  14677170
675 APPS                                  14676426

Identifying SQL with the Highest Waits

Using the following query, you can identify the SQL that’s waiting the most in your instance with in last 15 mins

SELECT a.user_id,d.username,s.sql_text,
SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a,
v$sqlarea s,
dba_users d
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.sql_id = s.sql_id
AND a.user_id = d.user_id
GROUP BY a.user_id,s.sql_text, d.username;

Read Full Post »

Older Posts »