This is a bug, a funny one, which I experienced on Linux x86 boxes where SQL*Plus would just hang, and do nothing...
That is, if the Linux x86 box has an uptime of more than 200 days, then SQL*Plus would just hang!
I experienced it on a linux x86 Oracle 10g Client installation where I had the client loging in everyday via SQL*Plus to remote boxes and do things. Suddenly it stopped. I checked top on the box where the Oracle 10g client was installed and saw SQL*Plus eating up the box alive! How strange!??
Check the stack trace:
strace $ORACLE_HOME/bin/sqlplus -V 2>&1 |less
you will see lots of jibberish...
Immediately checked metalink I found the Bug 4612267.
Note: 338461.1: SQL*Plus With Instant Client 10.2.0.1 Hangs, When System Uptime Is More Than 248 Days
Oracle support confirmed this, they asked me to patch the installation with applying a one-off patch they gave me on top of the 10.2.0.1.0 Linux Client.
Database Systems is a blog about Databases and Data of any size and shape
Pages
▼
Tuesday, 13 November 2007
Wednesday, 7 November 2007
Table of Dates
Use a PL/SQL collection TYPE to generate a date list as a database table which you can then use as time series table in your data warehouse.
1. Create a DATE_TABLE type.
2. Create the DATE_RANGE function to manipulate (load) the above type.
.
more about this function here:
http://www.adp-gmbh.ch/ora/sql/table_cast.html
3. Use the TYPE as a date table which you can then join with other tables.
Then use the "Date Table" to join it with SCOTT.EMP and get the employees which were hired during the period
1. Create a DATE_TABLE type.
SQL> create or replace TYPE "DATE_TABLE" AS TABLE OF DATE;2. Create the DATE_RANGE function to manipulate (load) the above type.
.
CREATE OR REPLACE FUNCTION date_range(from_dt IN DATE, to_dt IN DATE)
RETURN date_table AS
a_date_table date_table := date_table();
cur_dt DATE := from_dt;
BEGIN
WHILE cur_dt <= to_dt
LOOP
a_date_table.extend;
a_date_table(a_date_table.COUNT) := cur_dt;
cur_dt := cur_dt + 1;
END LOOP;
RETURN a_date_table;
END date_range;
more about this function here:
http://www.adp-gmbh.ch/ora/sql/table_cast.html
3. Use the TYPE as a date table which you can then join with other tables.
SELECT column_value DAYS
FROM TABLE(CAST(date_range('18-FEB-1981', '25-FEB-1981') AS date_table))
/
DAYS
-------------------------
18-FEB-81
19-FEB-81
20-FEB-81
21-FEB-81
22-FEB-81
23-FEB-81
24-FEB-81
25-FEB-81
Then use the "Date Table" to join it with SCOTT.EMP and get the employees which were hired during the period
SELECT d.DAYS,e.ename,e.hiredate
FROM emp e,
(SELECT column_value DAYS
FROM TABLE(CAST(date_range('18-FEB-1981', '25-FEB-1981') AS
date_table))) d
WHERE d.DAYS = e.hiredate(+)
/
DAYS ENAME HIREDATE
------------------------- ---------- -------------------------
18-FEB-81
19-FEB-81
20-FEB-81 ALLEN 20-FEB-81
21-FEB-81
22-FEB-81 WARD 22-FEB-81
23-FEB-81
24-FEB-81
25-FEB-81
Tuesday, 6 November 2007
Change the location of archivelogs
Use one of the following commands to change, online, the location of your archivelogs.
1. How to change the current location for archivelogs:
If you want to use the Flash Recovery Area (FRA) as your archivelog destination, wise to use really with RMAN as it will delete obsolete archivelogs based on the backup retention policy, then use this command. Otherwise you have to set up manual ways of deleting old obsolete archivelogs.
2. How to Use Flash Recovery Area (FRA) as your archivelog location:
(First set up flash recovery area)
The parameter USE_DB_RECOVERY_FILE_DEST is very good. It keeps things tidy. Together with the autobackup and backupset directories in the FRA it will also create a directory called archivelogs and will put the archivelogs in there!
Given the FRA as:
/usr/local/oracle/flash_recovery_area$
You get:
Very cool...
NOTE:
When you decide to use FRA for your archivelogs, you might want to move the existing archivelogs into FRA after you have set the destination as above. When you do that you will need to let RMAN know where the archivelogs are? That is where is their new location you can achieve that with the command
Docs say this: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref941
Otherwise you will probably get ane error like:
Which means that the controlfile does not know about the new location of your archivelogs.
1. How to change the current location for archivelogs:
SQL> alter system set log_archive_dest_1='LOCATION=/u20/oracle/data1';
System altered.
If you want to use the Flash Recovery Area (FRA) as your archivelog destination, wise to use really with RMAN as it will delete obsolete archivelogs based on the backup retention policy, then use this command. Otherwise you have to set up manual ways of deleting old obsolete archivelogs.
2. How to Use Flash Recovery Area (FRA) as your archivelog location:
(First set up flash recovery area)
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;
System altered.
The parameter USE_DB_RECOVERY_FILE_DEST is very good. It keeps things tidy. Together with the autobackup and backupset directories in the FRA it will also create a directory called archivelogs and will put the archivelogs in there!
Given the FRA as:
/usr/local/oracle/flash_recovery_area$
You get:
oracle@machine:/usr/local/oracle/flash_recovery_area/TESTDW0$ ls -lrt
total 12
drwxr-x--- 9 oracle oinstall 4096 2007-11-07 07:45 backupset
drwxr-x--- 9 oracle oinstall 4096 2007-11-07 07:45 autobackup
drwxr-x--- 4 oracle oinstall 4096 2007-11-07 07:45 archivelog
Very cool...
NOTE:
When you decide to use FRA for your archivelogs, you might want to move the existing archivelogs into FRA after you have set the destination as above. When you do that you will need to let RMAN know where the archivelogs are? That is where is their new location you can achieve that with the command
RMAN> catalog archivelog '/your/new/location/thearchivelogfile';
Docs say this: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref941
Otherwise you will probably get ane error like:
... specification does not match any archive log in the recovery catalog
Which means that the controlfile does not know about the new location of your archivelogs.
Friday, 2 November 2007
Innovation in Oracle 11g Database
I have attended the Ascot, UK (26/09/2007) Presentation by Andrew Sutherland, vice president, technology for Oracle EMEA and I am posting a summary of notes I have taken in that event.
Oracle 11g Innovations
Real Application Testing : Test workloads are typically simulated and are not accurate and complete representations of real world.
Database Replay : The ability to accurately and realistically rerun actual production workloads, including online user and batch workloads as well as cuncurrency dependencies and timing on a test system. No need to understand the application and write scripts to simulate load. DBAs now can test without the need to duplicate the application infrastructure/hardware. Cuts down testing cycles and time taken for tests.
Used for Database Upgrades, patches, parameter, schema changes , etc
Configuration changes such as conversion from a single instance to RAC, ASM, etc.
Storage, network, interconnect changes. Operating system, hardware migrations, patches, upgrades, parameter changes
SQL Performance Analyzer : A tool measuring the impact of environment changes on SQL execution plans in the database. A kind of a tool which integrates together previous SQL Tuning Sets (STS) and SQL Tuning Advisor (STA) tools. It produces a report outlining the benefit on the workload introduced by the change. So it gives a picture of the SQL Execution plans before the change and after the change,including the statements which regressed, seperately. The advantage of this tool, compared to the home grown approaches that dba’s have evolved over the years, is that it takes the number of executions into consideration while measuring the impact. Oracle 11G SQL Performance Analyzer takes all these factors into account while predicting the overall performance improvement and regressions and allows the oracle 11g database administrator to fix the SQL or fix the regression through SQL Plan Baselines.
Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer statistics SQL tuning actions, for example, creating SQL profiles
New Datatypes
XML, 3-D data, RFID (Radio Frequency ID), MP3, JPEG….
Binary XML -(No need to store the XML as CLOB anymore) “… If you’re mostly just going to pump the XML back out, binary seems to be the way to go. …” Binary XML allows you to store XML totally unparsed but still accessible via XPath. No encoding checks are done on loading. With no (or at least reduced) parsing, validation or conversions, you will have much less IO and much less CPU meaning much faster load times. With the combination of Secure Files this is a killer!
XMLIndex, that is a new way to improve access performance of your XML. In the past, your options were Xpath function based indexes (using extract or extractvalue) or Oracle Text. Both of those have some limitations that XML Index tries to address.
Secure Files The New BLOBs. Performance is the same as Unix File handling performance. This feature introduces a completely reengineered large object (LOB) data type to dramatically improve performance, manageability, and ease of application development. The new implementation also offers advanced, next-generation functionality such as intelligent compression, de-duplication of LOB segments and transparent encryption.
Managing Data Growth
Data Growth Facts:
DW is bogged down. The increase in the ammount of data of a system stores, is faster than the decrease of the cost of the disks. So buying cheap disks will not solve the data issues.
30 years ago the largets database was about 30GB DB has now become a 300TB DB.
Data Growth Solution:
Ability to comresss in the OLTP environments as well. Better compress ratios. Advanced Compression (Not gZIP). The compression algorithm in Oracle works with symbolic tables. It is a Logical Compression rather than a Physical one! Before compression was only for DWs now is for OLTPs.
ILM (Information Life Cycle Management) old, not-so-old, current data in different storage media with partitioning and advanced compression.
Secure Files, more efficiency with BLOBs.
Automating partitioning adds partitions on the go.
More Partitioning Options In 10g you could only do range-list and range-hash combinations now you can do many.
Higher Quality of Service
All performance figures have increased, better, faster. Faster backups, ZLIB algorithm for compression. Parallel backup of the same datafile.
MVs become “CUBE” organized MVs. PIVOT operator instead of DECODE. The new MODEL clause, MVs refresh without logs.
MMA (Maximum Availability Architecture)
Recovery Advisor (RMAN) RMAN Advisor, repairs and previews RMAN OEM GUI.
Flashback Transaction, & Dependecies
Total Recall (Flashback Data Archive, “Time Travel”) with SQL such as “AS OF” The main difference between flashback and Total Recall is that with Total Recall data will be permanently stored in an archive tablespace and will the only ages out after a user defined retention time. Oracle Total Recall enables administrators to easily maintain historical archives of changed data for compliance and business intelligence. Oracle Total Recall provides a secure, efficient, easy-to-use and application-transparent solution for long-term access to changed data.
Oracle Active Data Guard - No need to stop continous redo apply like in 10g when reading Standby Copy.
By the way and here is what is installed by default when you install Oracle 11g:
Overview of new components during Oracle 11g Release 1 (11.1) Linux x86 install:
Oracle Apex (HTMLDB) installed by default with 11g.
Oracle Configuration Manager is an available install option.
Oracle Database vault is an available install option.
Oracle Real Application Testing installed by default.
Orace SQL Developer is installed by default.
Oracle Warehouse Builder is installed by default (basic feautures only).
Oracle XML DB is installed by default for all database instances.
Oracle Ultra Search is integrated within the database, suppose installs by default.
Oracle HTTP Server available as option on seperate media.
Oracle Data Mining installs by default on Oracle Enterprise Edition.
Summary
Detailed Oracle 11g New Features Guide
http://download.oracle.com/docs/cd/B28359
Oracle 11g Innovations
Real Application Testing : Test workloads are typically simulated and are not accurate and complete representations of real world.
Database Replay : The ability to accurately and realistically rerun actual production workloads, including online user and batch workloads as well as cuncurrency dependencies and timing on a test system. No need to understand the application and write scripts to simulate load. DBAs now can test without the need to duplicate the application infrastructure/hardware. Cuts down testing cycles and time taken for tests.
Used for Database Upgrades, patches, parameter, schema changes , etc
Configuration changes such as conversion from a single instance to RAC, ASM, etc.
Storage, network, interconnect changes. Operating system, hardware migrations, patches, upgrades, parameter changes
SQL Performance Analyzer : A tool measuring the impact of environment changes on SQL execution plans in the database. A kind of a tool which integrates together previous SQL Tuning Sets (STS) and SQL Tuning Advisor (STA) tools. It produces a report outlining the benefit on the workload introduced by the change. So it gives a picture of the SQL Execution plans before the change and after the change,including the statements which regressed, seperately. The advantage of this tool, compared to the home grown approaches that dba’s have evolved over the years, is that it takes the number of executions into consideration while measuring the impact. Oracle 11G SQL Performance Analyzer takes all these factors into account while predicting the overall performance improvement and regressions and allows the oracle 11g database administrator to fix the SQL or fix the regression through SQL Plan Baselines.
Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer statistics SQL tuning actions, for example, creating SQL profiles
New Datatypes
XML, 3-D data, RFID (Radio Frequency ID), MP3, JPEG….
Binary XML -(No need to store the XML as CLOB anymore) “… If you’re mostly just going to pump the XML back out, binary seems to be the way to go. …” Binary XML allows you to store XML totally unparsed but still accessible via XPath. No encoding checks are done on loading. With no (or at least reduced) parsing, validation or conversions, you will have much less IO and much less CPU meaning much faster load times. With the combination of Secure Files this is a killer!
XMLIndex, that is a new way to improve access performance of your XML. In the past, your options were Xpath function based indexes (using extract or extractvalue) or Oracle Text. Both of those have some limitations that XML Index tries to address.
Secure Files The New BLOBs. Performance is the same as Unix File handling performance. This feature introduces a completely reengineered large object (LOB) data type to dramatically improve performance, manageability, and ease of application development. The new implementation also offers advanced, next-generation functionality such as intelligent compression, de-duplication of LOB segments and transparent encryption.
Managing Data Growth
Data Growth Facts:
DW is bogged down. The increase in the ammount of data of a system stores, is faster than the decrease of the cost of the disks. So buying cheap disks will not solve the data issues.
30 years ago the largets database was about 30GB DB has now become a 300TB DB.
Data Growth Solution:
Ability to comresss in the OLTP environments as well. Better compress ratios. Advanced Compression (Not gZIP). The compression algorithm in Oracle works with symbolic tables. It is a Logical Compression rather than a Physical one! Before compression was only for DWs now is for OLTPs.
ILM (Information Life Cycle Management) old, not-so-old, current data in different storage media with partitioning and advanced compression.
Secure Files, more efficiency with BLOBs.
Automating partitioning adds partitions on the go.
More Partitioning Options In 10g you could only do range-list and range-hash combinations now you can do many.
Higher Quality of Service
All performance figures have increased, better, faster. Faster backups, ZLIB algorithm for compression. Parallel backup of the same datafile.
MVs become “CUBE” organized MVs. PIVOT operator instead of DECODE. The new MODEL clause, MVs refresh without logs.
MMA (Maximum Availability Architecture)
Recovery Advisor (RMAN) RMAN Advisor, repairs and previews RMAN OEM GUI.
Flashback Transaction, & Dependecies
Total Recall (Flashback Data Archive, “Time Travel”) with SQL such as “AS OF” The main difference between flashback and Total Recall is that with Total Recall data will be permanently stored in an archive tablespace and will the only ages out after a user defined retention time. Oracle Total Recall enables administrators to easily maintain historical archives of changed data for compliance and business intelligence. Oracle Total Recall provides a secure, efficient, easy-to-use and application-transparent solution for long-term access to changed data.
Oracle Active Data Guard - No need to stop continous redo apply like in 10g when reading Standby Copy.
By the way and here is what is installed by default when you install Oracle 11g:
Overview of new components during Oracle 11g Release 1 (11.1) Linux x86 install:
Oracle Apex (HTMLDB) installed by default with 11g.
Oracle Configuration Manager is an available install option.
Oracle Database vault is an available install option.
Oracle Real Application Testing installed by default.
Orace SQL Developer is installed by default.
Oracle Warehouse Builder is installed by default (basic feautures only).
Oracle XML DB is installed by default for all database instances.
Oracle Ultra Search is integrated within the database, suppose installs by default.
Oracle HTTP Server available as option on seperate media.
Oracle Data Mining installs by default on Oracle Enterprise Edition.
Summary
Oracle Real Application Testing;
Oracle Advanced Compression;
Oracle Total Recall; and,
Oracle Active Data Guard.
Detailed Oracle 11g New Features Guide
http://download.oracle.com/docs/cd/B28359