Jan 21 2013

How long did Oracle materialized view refresh run?

Category: Database,Materialized Viewsittichai @ 9:38 am

The LAST_REFRESH_DATE column of the DBA_MVIEWS or the LAST_REFRESH column of the DBA_MVIEW_REFRESH_TIMES indicates the start refresh time. But what if we’d like to find out how long the refresh of the materialized view really takes. Well, we can query the DBA_MVIEW_ANALYSIS.

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.

Both values are in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';

MVIEW_NAME               LAST_REFRESH_DATE      FULLREFRESHTIM INCREFRESHTIM
------------------------ ---------------------- -------------- -------------
MV_CHANGE_HISTORY        07-JAN-13 04.36.58 PM               0            36
MV_ITEM_HISTORY          07-JAN-13 04.36.58 PM               0             9

This shows that the recent refresh of the MV_CHANGE_HISTORY and MV_ITEM_HISTORY are the fast refreshes for 36 and 9 seconds respectively.

Put in one query to calculate and display the end time.

SELECT 
   mview_name,
   last_refresh_date "START_TIME",
   CASE
      WHEN fullrefreshtim <> 0 THEN
         LAST_REFRESH_DATE + fullrefreshtim/60/60/24
      WHEN increfreshtim <> 0 THEN
         LAST_REFRESH_DATE + increfreshtim/60/60/24
      ELSE
         LAST_REFRESH_DATE
   END "END_TIME",
   fullrefreshtim,
   increfreshtim
FROM all_mview_analysis
WHERE owner='JOHN';

MVIEW_NAME              START_TIME             END_TIME               FULLREFRESHTIM INCREFRESHTIM
----------------------- ---------------------- ---------------------- -------------- -------------
MV_CHANGE_HISTORY       07-JAN-13 04.36.58 PM  07-JAN-13 04.37.34 PM               0            36
MV_ITEM_HISTORY         07-JAN-13 04.36.58 PM  07-JAN-13 04.37.07 PM               0             9

Reference: How To Calculate MVIEW Refresh Duration? What Does DBA_MVIEWS.LAST_REFRESH_DATE and DBA_MVIEW_REFRESH_TIMES.LAST_REFRESH Indicate? [ID 1513554.1]

Tags: , , , ,


Jul 06 2012

Resizing the filesystem using Logical Volume Manager within Oracle Linux

Category: Linux,OSittichai @ 8:53 pm

Oftentimes we all run into the situation where the file system is full (or almost full) and need more space. This expansion task seems to be a lot easier when using the Logical Volume Manager (LVM) in Oracle Linux.

  • Review the current size.
[root@ol6 ~]# df -H
Filesystem                 Size   Used  Avail Use% Mounted on
/dev/mapper/vg_ol6-lv_root 27G    22G   3.8G  86% /
tmpfs                      1.3G   209M   1.1G  17% /dev/shm
/dev/sda1                  508M    97M   385M  21% /boot
Downloads                  750G   172G   578G  23% /media/sf_Downloads

Plan to add 30G to the root file system.

  • Create a partition on the newly-added disk.
[root@ol6 ~]# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x0c04311f.
Changes will remain in memory only, until you decide to write them.

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3916, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-3916, default 3916): 
Using default value 3916

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
  •  Create a physical volume on top of it.
[root@ol6 ~]# pvcreate /dev/sdf1
  Writing physical volume data to disk "/dev/sdf1"
  Physical volume "/dev/sdf1" successfully created
  • Review the current volume. Note that currently there is no free extends (noted by zero value of the “Free PE / Size”).
[root@ol6 ~]# vgdisplay
  --- Volume group ---
  VG Name               vg_ol6
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               29.51 GiB
  PE Size               4.00 MiB
  Total PE              7554
  Alloc PE / Size       7554 / 29.51 GiB
 Free PE / Size 0 / 0  
  VG UUID               2e2VHd-Mb3D-Uz0G-4Yec-tbfe-f3cI-7cvpby
  • Extend this volume with a new disk.
[root@ol6 ~]# vgextend vg_ol6 /dev/sdf1
  Volume group "vg_ol6" successfully extended
  • Check the volume again. The “Free PE / Size” is now 30G.
[root@ol6 ~]# vgdisplay
--- Volume group ---
VG Name vg_ol6
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 4
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 2
Open LV 2
Max PV 0
Cur PV 2
Act PV 2
VG Size 59.50 GiB
PE Size 4.00 MiB
Total PE 15233
Alloc PE / Size 7554 / 29.51 GiB
Free PE / Size 7679 / 30.00 GiB
VG UUID 2e2VHd-Mb3D-Uz0G-4Yec-tbfe-f3cI-7cvpby
  • Now let’s review the logical volume.
[root@ol6 ~]# lvdisplay
  --- Logical volume ---
  LV Path                /dev/vg_ol6/lv_root
  LV Name                lv_root
  VG Name                vg_ol6
  LV UUID                rd2d4X-vqE8-xENi-clCz-Oa0T-0R6X-RFCBDq
  LV Write Access        read/write
  LV Creation host, time , 
  LV Status              available
  # open                 1
 LV Size 25.10 GiB
  Current LE             6426
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:0

  --- Logical volume ---
  LV Path                /dev/vg_ol6/lv_swap
  LV Name                lv_swap
  VG Name                vg_ol6
  LV UUID                xM3Blz-wvpG-IUfF-WhWc-EHoI-I0xG-oeV1IR
  LV Write Access        read/write
  LV Creation host, time , 
  LV Status              available
  # open                 1
  LV Size                4.41 GiB
  Current LE             1128
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:1

We want to add additional 30G into the existing /dev/vg_ol6/lv_root. So the total size will be 55.1GB.

  • We can extend the logical volume to the needed size.
[root@ol6 ~]# lvextend -L 55.10G /dev/vg_ol6/lv_root
  Rounding size to boundary between physical extents: 55.10 GiB
  Extending logical volume lv_root to 55.10 GiB
  Insufficient free space: 5120 extents needed, but only 5119 available

You may have to adjust the size if the initial specified size is too large.

[root@ol6 ~]# lvextend -L 55G /dev/vg_ol6/lv_root
  Extending logical volume lv_root to 55.00 GiB
  Logical volume lv_root successfully resized
  • Now finally you can extend the file system.
[root@ol6 ~]# resize2fs /dev/vg_ol6/lv_root 55G
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/vg_ol6/lv_root is mounted on /; on-line resizing required
old desc_blocks = 3, new_desc_blocks = 4
Performing an on-line resize of /dev/vg_ol6/lv_root to 14417920 (4k) blocks.
The filesystem on /dev/vg_ol6/lv_root is now 14417920 blocks long.
  • The file system is resized while the system is still on-line.
[root@ol6 ~]# df -H
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_ol6-lv_root
55G 22G 33G 39% /
tmpfs 1.3G 209M 1.1G 17% /dev/shm
/dev/sda1 508M 97M 385M 21% /boot
Downloads 750G 172G 578G 23% /media/sf_Downloads

References:

Tags: , , , , , , ,


Aug 15 2011

Book Review: Oracle 11g R1/R2 Real Application Clusters Essentials by Ben Prusinski and Syed Jaffer Hussain (Packt Publishing)

Category: 11g,Databaseittichai @ 9:31 pm

Oracle 11g R1/R2 Real Application Clusters EssentialsOracle Real Application Cluster (RAC)  becomes the technology of choice for highly available and highly scalable deployment of enterprise Oracle database environment. With its innovative technology it comes with no surprise its complexity. The complexity is not only with the underlying database technology itself, but also how well you design and implement it to work with other components including operating system, storage subsystem, etc. In order to setup and maintain a successful RAC environment, it not only requires technical knowledge of database administrator (DBA), but also well collaboration and extended planning between all IT and business partners.

Few years back, there has been a movement of DBA 2.0 which says that, in addition to database knowledge,  an extended understanding of operating system, storage and/or networking is encouraged due to more tightly integrated of Oracle database technologies such as Clusterware, RAC, ASM with the underlying subsystems. I agree with this notion but not to the extent that DBAs should perform all works in those areas. (Yes, I’m aware that some DBAs do it all.) However, I think at least they should have enough understandings of all-related technologies to collaborate with those who are responsible for in order to achieve the desired setup. In many cases especially in the large companies where traditional organization division of the job description of administrators is clear cut and no overlapping, being well-rounded with all-related technologies will allow DBA to better discuss the requirements and designs and even assist with the implementation details.

Most Oracle RAC books in the market today tend to focus only on the detailed technical aspects of this database technology. This mainly is good for those Oracle professionals who at least have worked with RAC before. Few books really provide a good foundation for those who are new to this technology. One of those few which I’ve just read recently is Oracle 11g R1/R2 Real Application Clusters Essentials authored by Ben Prusinski and Syed Jaffer Hussain published by Packt Publishing.

What I really like about this book is the fact that it does not jump into the nuts and bolts of this Oracle technology right away but instead it is trying to build up good foundation starting from concept (especially high availability), architecture (of all components not just of database), design, implementation and then even some of the real-world samples. This stepping-stone approach helps readers to understand what is involved and how they should deploy this technology for the high available database.

In the chapter 1, the emphasis of the high availability concept is crucial. The authors precisely points out that high availability should be looked from users’ perspective. Many DBAs or system administrators especially novice ones have a tendency to focus solely on their respective areas. The provided sample of the availability percentage in the “high availability interpretations” section is a good reminder for a need to have an end-to-end high availability design to avoid a single point of failure (SPOF). The chapter goes on to give the definitions of many acronyms (BIA, BCP, DRP, etc.) commonly-used during the planning phase. It is quite useful to be aware of them especially when discussing with business end-users. The chapter ends with providing a whole picture of the high availability solutions (RAC, Data Guard, Streams, Application Server Clustering) from Oracle as a part of the Maximum Availability Architecture (MAA). It is worth to point out that RAC is just one (not the only) of key components for a complete high availability solution.

Chapter 2 looks into the hardware components of Oracle RAC architecture including network, storage, etc. Again, I think the authors reinforce a need to understand all these underlying technologies (at least at high level) even though DBAs might not involve in the actual implementation. But it is crucial to have this knowledge under their belts in order to have the productive discussions of the requirements and design with system administrators.

Chapter 3 goes into the clusterware installation of 11g R1/R2. Even though readers may to be able to find similar information on many web sites and blogs, this book has some extra information which makes an attempt to explain what each action does, for example, what happen when the orainstRoot.sh and root.sh run?

Chapter 4 dedicates to the Automatic Storage Management (ASM) which becomes the only clear choice for storage management in the 11g deployment. The chapter covers all aspects of ASM in both R1 and R2 including new features, configurations, management, etc.

Chapter 5 covers Clusterware management and (some) troubleshooting samples. Instead of showing all possible troubleshooting scenarios (which is impossible to include everything in a chapter or even in a book), the authors did an excellent job of giving an overview of all necessary Clusterware utilities in order to troubleshoot the issues.

Chapter 6 turns our attention to RAC database administration. The structure is similar to that of Clusterware in the chapter 3. The latter part of the chapter also covers the automatic workload management including the topic of the service which is highly recommended to utilize it (not using the default database service.)

Chapter 7 covers all aspects of backup and recovery of Oracle database including the best practices. In addition, new features of RMAN in Oracle 11g R1 and 11g R2 along with OCR and voting disk backup and recovery are discussed.

Chapter 8 tries to give an overview of performance tuning. The intention here seems to just give an overview of the concept and methods of performance tuning as well as new 11g performance features, for example, Real Application Testing (RAT) which has the Database Replay and SQL Performance Analyzer (SPA). This high-level overview focus is understandable because the scope of performance tuning is generally vast. Some of tuning metrics especially relate to the wait events are explained here.

Chapter 9 focuses on the upgrade scenarios. As we all know it is impossible to cover all possible scenarios. But the authors gives sufficient information about overall upgrade processes from 10g R2 to 11g R1 and then to 11g R2 in this chapter. Definitely consulting with Oracle support for more details on individual upgrade case is still highly recommended.

Chapter 10 is very informative on the node addition and removal. The chapter steam-lines the steps pretty well (comparing to the official Oracle document which is very confusing at the first read with the flow of information). (We just went through adding two nodes into the existing two nodes of Oracle 11g R1 two months ago. That’s why I can say that.)

Chapter 11 shows the readers the importance of the end-to-end high availability implementation. The high availability should not stop at the Oracle RAC database, but it must extend to application layer on top of it. In addition to an overview of Oracle EBS (E-Business Suite), this chapter shows the readers how to take full advantage of high availability and performance features in Oracle RAC database.

The last chapter wraps up nicely with information about other tools in the MAA tool suite namely Streams and Data Guard. It covers all various topics of thes tools including concepts, new features, best practices, and overview configuration.

The last section of this book on the additional resources and tools is a nice read and very informative. It is just like a nice dessert at the end of a good meal.

In conclusion, if you’re new to Oracle RAC technology, this is THE book for you. It should give the readers enough understanding to jump on Oracle RAC bandwagon. Advanced DBAs may find that most of the contents in the book are too familiar, but they might be attracted to the new features, comparisons between R1 and R1 as well as some of real-world samples mentioned in this book. Overall I appreciate a very well effort of Ben ad Syed for one of the most complete informative books on Oracle RAC in the market today. Well-deserved kudos to them.

Thanks to Packt Publishing for the opportunity to review this book.

Tags: , , , , , , , , , , ,


Mar 20 2011

Oracle 11g SQL Error Logging

Category: 11g,Database,SQLPlus,Toolittichai @ 8:00 am

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search ORA- for any errors. This would work fine if there are only a few scripts but it becomes cumbersome when multiple scripts are involved. In addition we’d like to be able to run the scripts on either Windows and Unix platforms, handling OS file paths for multiple platforms using spool adds unnecessary layer.

The SQL Error Logging is a new feature in 11g. This simplifies the way we capture and locate error messages as information now will be stored in database table instead of OS files.

To check whether or not the Error Logging is enabled.

SQL> show errorlogging

errorlogging is OFF

To enable the Error Logging. The default table SPERRORLOG is created.

SQL> set errorlogging on

By default, the SPERRORLOG will be created under current user. In this sample, the current schema is TEST1_USER.

SQL> show errorlogging

errorlogging is ON TABLE TEST1_USER.SPERRORLOG

Here is the structure of this table SPERRORLOG.

SQL> desc SPERRORLOG
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

To enable the Error Logging to an user defined table instead of the default SPERRORLOG table.

SQL> set errorlogging on table [schema].[table]

If non-default, the table has to be created in advance, otherwise you will get an error.

SQL> set errorlogging on table my_sperrorlog;

SP2-1507: Errorlogging table, role or privilege is missing or not accessible

Here is the syntax to create an user-defined table. If this table is created on a different schema, an insert grant to is needed for the current user.

SQL> CREATE TABLE my_sperrorlog (
username     VARCHAR(256),
timestamp    TIMESTAMP,
script       VARCHAR(1024),
identifier   VARCHAR(256),
message      CLOB,
statement    CLOB
);
SQL> set errorlogging on table my_sperrorlog

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.my_sperrorlog

Demo
Generate error #1.

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

Generate error #2.

SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1)                     option

Without commit, other sessions won’t see this information.

SQL> commit;

Commit complete.

Set an unique identifier to make it easier to identify the logging record. In sample here, the identifier is set to REL1.

SQL> set errorlogging on identifier 'REL1'

Generate error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

Check the SQL Error Logging with the identifier=’REL1′.

SQL> SELECT username, statement, message, IDENTIFIER
FROM sperrorlog where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
TEST1_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL                          tifier

Truncate the Error Logging to clear all existing rows in the error log table. This will clear out all records regardless of who creates them.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;
No rows selected

There is no set errorlogging truncate only a specified identifier. Doing below is the same set errorlogging on truncate. So basically the identifier is ignored.

SQL> set errorlogging on truncate identifier 'REL1'

But you can just delete records as the regular table.

SQL> delete sperrorlog where IDENTIFIER='REL1';

SQL> commit;

Disable Error Logging. (Log off will automatically disable it.)

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

Will it work with previous versions of Oracle database?

Since the SQL Error Logging is the feature on client, I have no reason to believe that it would not work with pre-11g databases.

The 11g SQL Plus Client to 10.2.0.1 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 10.1.0.4 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 9i Database
Unfortunately I don’t have any Oracle 9i databases.  :-)

The 11g SQL Plus Client to 8i Database

Surprisingly, I still have one 8i database left. But, well, the 11g client no longer supports the 8i database.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Related topics:

Tags: , , , , , ,


Next Page »