These two buzz words "cloud computing" have been in my attention lately and I really wanted to find out what they stand for. I got a classic and short definition from Wikipedia:
"Cloud computing is Internet-based ("cloud") development and use of computer technology ("computing"). "
see full definition....
It seems like it is time for the internet to prove what it stands for, the ultimate computer!?
Yes, from the look of things, all of the applications and databases of humankind are going to be on the internet. At the office we will not have "server rooms" any more, but just PCs with Internet browsers. Developers, will be the only kind of IT people that will stay back in the office, they will sit very near to the boss together with Business Analysts and IT Managers. Modern day developers of the future will be using the browser, and rapidly building applications, maybe with "declarative programming". Is it already hapenning? If you look at the business model of companies like Salesforce where they market concepts like "No Software!" and Software as Service (SaaS) and Oracle's hosted rapid application development environment called Oracle Apex one can easily start seeing The Clouds gathering in the sky. How about the Greenplum the Mega-Giga Titanic Datawarehouse for everyone on the Internet. A datawarehouse internet appliance where everyone can put their datawarehouse and access it from anywhere on the earth on a browser and do analysis. No Servers, No hardware, No Software just a login to a workspace on the internet and that's it. Everything you need, tools, spreadsheets will be there on the internet wating for you.
I wonder what will happen to all those other IT guys, SysAdmins, DBAs. Will they lock them up in huge data centres somewhere in the countryside? Will they be needed at all?
Tom Kyte an Oracle Expert was asked a smilar question on his website and here is Thomas Kyte's comment on Cloud Computing.
Or is it going to be as Tom Kyte says that with 'Cloud Computing', databases will just get larger and larger.
Database Systems is a blog about Databases and Data of any size and shape
Tuesday, 28 October 2008
Monday, 27 October 2008
Monitor the time an RMAN backup takes
To see what RMAN is doing now, and see what SID is doing what sort of work, and how much it has got left to do, use the following SQL. This script is good when you are trying to see how much work an RMAN Channels have got left to do. It is good to watch with the RMAN backup script log (tail -f) as the backup is hapenning. For both scripts you have to lonig as SYSDBA on the instance where the BACKUP
is or RESTORE hapenning.
is or RESTORE hapenning.
SELECT sid,
start_time,
totalwork,
sofar,
( sofar / totalwork ) * 100 pct_done
FROM v$session_longops
WHERE totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname LIKE 'RMAN%'
/
SID START_TIM TOTALWORK SOFAR PCT_DONE
---------- --------- ---------- ---------- ----------
100 27-OCT-08 1554952 1364978 87.7826454
SELECT To_char(start_time, 'dd-mon-yyyy@hh24:mi:ss') "Date",
status,
operation,
mbytes_processed
FROM v$rman_status vs
WHERE start_time > SYSDATE - 1
ORDER BY start_time
/
Date STATUS OPERATION MBYTES_PROCESSED
-------------------- ----------------------- --------------------------------- ----------------
27-oct-2008@11:40:11 FAILED RMAN 0
27-oct-2008@11:40:29 COMPLETED BACKUP 11812
27-oct-2008@12:06:30 COMPLETED BACKUP 23112
27-oct-2008@12:41:45 COMPLETED BACKUP 160
27-oct-2008@12:42:28 FAILED CONTROL FILE AUTOBACKUP 0
27-oct-2008@17:24:28 RUNNING RMAN 0
27-oct-2008@17:24:43 COMPLETED DELETE 0
27-oct-2008@17:24:51 COMPLETED CATALOG 0
27-oct-2008@17:25:16 RUNNING RESTORE 22082.875
Thursday, 25 September 2008
Partitioning using CREATE TABLE AS (CTAS) and Column Default Values
This is about the CTAS (Create Table As...) operations during the creation of partitioned tables from normal tables in an Oracle database.
The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns. This might lead to a nasty surprise if you are doing RANGE PARTITIONING and the partition key DATE column has a DEFAULT VALUE of SYSDATE in the normal table. This DEFAULT VALUE setting is NOT copied to the partitioned table!
What will happen is, if you do not specify a date explicitly in your INSERT statements you will get an 'ORA-14400: inserted partition key does not map to any partition' error. As the partition key value passed in will be NULL and the partitioned table will NOT know about the DEFAULT value.
Things can be worse if you are using a combination of CTAS to create partitioned tables from normal tables, and then you use an ALTER TABLE ... RENAME TO .. to replace your normal production tables with the new partitioned tables. Let me tell you how. If your application's INSERT statements into these new partitioned tables, do not explicitly specify the date value of the partition key, you will pass in NULLs and hit ORA-14400. Watch out you will suffer production outage!
To fix this problem, you will have to either explicitly change your code to pass in a date value for the partition key column, or alter the partitioned table after CTAS and modify the column to have a default value.
Here is a demonstration on Oracle 10g R2 of how easily this thing can happen.
Create the normal table
conn scott/tiger
Connected.
drop table big_table;
create table big_table
(id number primary key,
subject varchar2(500),
created_date date default sysdate
)
/
insert into big_table (id, subject) values (4,'tset3')
/
1 row created.
commit;
Commit complete.
Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.
drop table par_big_table
/
-- change dates below appropriately to include the SYSDATE
-- at the time you run this example.
create table par_big_table
partition by range (created_date)
(
partition p200809 values less than (to_date('01-10-2008', 'DD-MM-YYYY')),
partition p200810 values less than (to_date('01-11-2008', 'DD-MM-YYYY'))
)
as
select * from big_table
/
Now try to insert into the new partitioned table without passing the CREATED_DATE value, as you will wrongly assume the new partitioned table will have a DEFAULT VALUE. See how you get the error.
insert into par_big_table (id, subject) values (5,'test4')
/
insert into par_big_table (id, subject) values (5,'test4')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
This happens because the value the INSERT statement is passing for the CREATED_DATE column is NULL and the partitioned table doesn't have a DEFAULT VALUE for this column.
To fix this error and stop the production outage you might have caused :-) you have two choices:
* ALTER the new partitioned table and make the column to have a DEFAULT VALUE
* Change the application code to always include a value for the CREATED_DATE
Let's ALTER the table.
alter table par_big_table modify ( created_date date default sysdate);
insert into par_big_table (id, subject) values (5,'test4');
1 row created.
commit;
Commit complete.
Again watch for those DEFAULT VALUES in columns when you are creating partitioned tables with CTAS.
The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns. This might lead to a nasty surprise if you are doing RANGE PARTITIONING and the partition key DATE column has a DEFAULT VALUE of SYSDATE in the normal table. This DEFAULT VALUE setting is NOT copied to the partitioned table!
What will happen is, if you do not specify a date explicitly in your INSERT statements you will get an 'ORA-14400: inserted partition key does not map to any partition' error. As the partition key value passed in will be NULL and the partitioned table will NOT know about the DEFAULT value.
Things can be worse if you are using a combination of CTAS to create partitioned tables from normal tables, and then you use an ALTER TABLE ... RENAME TO .. to replace your normal production tables with the new partitioned tables. Let me tell you how. If your application's INSERT statements into these new partitioned tables, do not explicitly specify the date value of the partition key, you will pass in NULLs and hit ORA-14400. Watch out you will suffer production outage!
To fix this problem, you will have to either explicitly change your code to pass in a date value for the partition key column, or alter the partitioned table after CTAS and modify the column to have a default value.
Here is a demonstration on Oracle 10g R2 of how easily this thing can happen.
Create the normal table
conn scott/tiger
Connected.
drop table big_table;
create table big_table
(id number primary key,
subject varchar2(500),
created_date date default sysdate
)
/
insert into big_table (id, subject) values (4,'tset3')
/
1 row created.
commit;
Commit complete.
Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.
drop table par_big_table
/
-- change dates below appropriately to include the SYSDATE
-- at the time you run this example.
create table par_big_table
partition by range (created_date)
(
partition p200809 values less than (to_date('01-10-2008', 'DD-MM-YYYY')),
partition p200810 values less than (to_date('01-11-2008', 'DD-MM-YYYY'))
)
as
select * from big_table
/
Now try to insert into the new partitioned table without passing the CREATED_DATE value, as you will wrongly assume the new partitioned table will have a DEFAULT VALUE. See how you get the error.
insert into par_big_table (id, subject) values (5,'test4')
/
insert into par_big_table (id, subject) values (5,'test4')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
This happens because the value the INSERT statement is passing for the CREATED_DATE column is NULL and the partitioned table doesn't have a DEFAULT VALUE for this column.
To fix this error and stop the production outage you might have caused :-) you have two choices:
* ALTER the new partitioned table and make the column to have a DEFAULT VALUE
* Change the application code to always include a value for the CREATED_DATE
Let's ALTER the table.
alter table par_big_table modify ( created_date date default sysdate);
insert into par_big_table (id, subject) values (5,'test4');
1 row created.
commit;
Commit complete.
Again watch for those DEFAULT VALUES in columns when you are creating partitioned tables with CTAS.
Friday, 15 August 2008
Capture bind variables in SQL with Oracle FGA
In Oracle 10g with fine-grained auditing (FGA) it is possible to track the bind variables of your application's SQL statements. I know, Oracle 11g is out but how amazing, I am still discovering new things in Oracle 10g!
Oracle FGA is good for bind variables, if that is what you want after all. It is lightweight and more easy to use than its alternatives, for example the full SQL TRACE with the option '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'.
Another benefit of FGA is that you can choose which object you want to audit and what kind of statements, SELECT, INSERT...etc you want to audit. Whereas with the SQL TRACE you just have to accept the trace dump and the performance implications.
More good news is that in Oracle 10g you don't have to bounce the database anymore to set the initialization parameter AUDIT_TRAIL and enable auditing for your system. FGA does not require a database shutdown/restart.
Object or user auditing can be done ad-hoc and on the spot, thanks to FGA. You can audit INSERT, DELETE or just SELECT statements. Moreover, you can be object specific or user specific. For example if you wanted to audit the DML happening on a table all you have to do is to create an Audit Policy for that table. You can use the DBMS_FGA.ADD_POLICY procedure to create the policy. The modifications on the table, together with the bind variables, are captured and logged in a SYS owned log table called FGA_LOG$. How amazing!
Here is an example:
We set up an object auditing policy, which will monitor all INSERT, UPDATE and DELETE operations on the table EMP on Scott's schema.
Then connect as SCOTT the owner of the object you are auditing and do some changes on the object.
Now look at the FGA_LOG$ to see the audit entries.
WARNING : Don't forget to drop the policy once you are done, cause the auditing will go for infinite time on the object. The FGA_LOG$ table will fill up and you will waste space. You can drop the policy once you have done with it like this:
Oracle FGA is good for bind variables, if that is what you want after all. It is lightweight and more easy to use than its alternatives, for example the full SQL TRACE with the option '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'.
Another benefit of FGA is that you can choose which object you want to audit and what kind of statements, SELECT, INSERT...etc you want to audit. Whereas with the SQL TRACE you just have to accept the trace dump and the performance implications.
More good news is that in Oracle 10g you don't have to bounce the database anymore to set the initialization parameter AUDIT_TRAIL and enable auditing for your system. FGA does not require a database shutdown/restart.
Object or user auditing can be done ad-hoc and on the spot, thanks to FGA. You can audit INSERT, DELETE or just SELECT statements. Moreover, you can be object specific or user specific. For example if you wanted to audit the DML happening on a table all you have to do is to create an Audit Policy for that table. You can use the DBMS_FGA.ADD_POLICY procedure to create the policy. The modifications on the table, together with the bind variables, are captured and logged in a SYS owned log table called FGA_LOG$. How amazing!
Here is an example:
We set up an object auditing policy, which will monitor all INSERT, UPDATE and DELETE operations on the table EMP on Scott's schema.
SQL> begin
dbms_fga.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_DETECTIVES',
audit_column => 'ENAME',
statement_types => 'INSERT, UPDATE, DELETE',
audit_trail => DBMS_FGA.DB_EXTENDED
);
end;
PL/SQL procedure successfully completed.
Then connect as SCOTT the owner of the object you are auditing and do some changes on the object.
SQL> conn scott/tiger
Connected.
SQL> variable myname varchar2(50);
SQL> exec :myname :='ROBIN';
PL/SQL procedure successfully completed.
SQL> insert into emp values (9999, :myname, null, null, null, null, null,null);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> delete emp where ename = :myname;
1 row deleted.
SQL> commit;
Now look at the FGA_LOG$ to see the audit entries.
SQL> conn sys/***** as sysdba
Connected.
SQL> column ntimestamp# format a30
SQL> column lsqltext format a15
SQL> column dbuid format a15
SQL> column obj$name format a10
SQL> column lsqlbind format a15
SQL> column lsqltext format a20
SQL> select ntimestamp#, dbuid, obj$name, lsqlbind, lsqltext from sys.fga_log$;
NTIMESTAMP# DBUID OBJ$NAME LSQLBIND LSQLTEXT
------------------------------ --------------- ---------- --------------- --------------------
14-AUG-08 19.39.48.427696 PM SCOTT EMP #1(5):ROBIN insert into emp valu
es (9999, :myname, n
ull, null, null, nul
l, null,null)
14-AUG-08 19.39.56.577619 PM SCOTT EMP #1(5):ROBIN delete emp where ena
me = :myname
WARNING : Don't forget to drop the policy once you are done, cause the auditing will go for infinite time on the object. The FGA_LOG$ table will fill up and you will waste space. You can drop the policy once you have done with it like this:
SQL> conn sys/***** as sysdba
SQL>
begin
dbms_fga.drop_policy ( 'SCOTT', 'EMP','EMP_DETECTIVES');
end;
Monday, 16 June 2008
Oracle OBIEE Install on Unbreakable Linux
After visiting a seminar given by Oracle on Data Analysis and Oracle BI, I have decided to explore the Oracle OBIEE product and see it first hand. In this post I would like to share my experience of installing Oracle OBIEE on Oracle's Unbreakable Linux.
I am in the opinion that all things should be in the database and I was reluctant and skeptical about Oracle BI. Why on earth do we need another app server kind of server, a server which is half webserver and half app server and all it does is things like daily office automation tasks. After reading some marketing text about the tool I found it to claim to do things like:
A true mix of Siebel, JD Edwards and Peoplesoft one can say. Especially what is the point of having a tool like OBIEE, when you can have all this (maybe not all, but a significant portion) directly from the database with Oracle APEX in Oracle 11g served to the user via a web browser and for FREE. Having Google offering MS Word like applications on the web what is the point really of OBIEE. Is OBIEE already old technology?
Installing Oracle OBIEE on Unbreakable Linux
So, I have downloaded Unbreakable Linux from the Oracle Store here. Installed the OS on a Desktop Machine and then, downloaded Oracle Business Intelligence Enterprise Edition OBIEE and particularly the Linux version file biee_linux_x86_redhat_101333_disk1.cpio from Oracle Downloads Website.
One thing I noticed when I was reading about OBIEE is that it is "Database Agnostic", that is it doesn't need a database to operate, although you can put it in XMLDB. But this is not necessary as OBIEE can live on a file system.
That is what I did and I run the command on the downloaded file like this from my Linux prompt
Once I have correctly indicated the directory where my Java JDK 1.5 (or greater) was installed the installation was smooth and finished quickly. I have chosen the full complete installation of OBIEE from the install options and it put software in the designated Oracle BI homes.
This page tells it all. You get links to the 3 main components of your OBIEE installation on the right top corner of this page and these are:
Anyway I found the /home/oracle/OracleBI/setup directory to be full of cool xxxx.sh scripts which you can use to start stuff in OBIEE.
After successfully starting Oracle BI Server, Oracle BI Presentation Services (SAW server) I have finally managed the get all 3 links to work and started exploring the Oracle BI Infrastructure.
In another post I hope to write about my thoughts on OBIEE.
NOTES:
OBIEE Defaut User is : Administrator
OBIEE Default User password is : Administrator
Resources I used for the OBIEE installation.
I am in the opinion that all things should be in the database and I was reluctant and skeptical about Oracle BI. Why on earth do we need another app server kind of server, a server which is half webserver and half app server and all it does is things like daily office automation tasks. After reading some marketing text about the tool I found it to claim to do things like:
- Mail merge
- Cheque printing
- PDF reports
- Flash graphs
- Any format Reports
- Interactive dashboards
- Ad-hoc analysis
- Market analysis
A true mix of Siebel, JD Edwards and Peoplesoft one can say. Especially what is the point of having a tool like OBIEE, when you can have all this (maybe not all, but a significant portion) directly from the database with Oracle APEX in Oracle 11g served to the user via a web browser and for FREE. Having Google offering MS Word like applications on the web what is the point really of OBIEE. Is OBIEE already old technology?
Installing Oracle OBIEE on Unbreakable Linux
So, I have downloaded Unbreakable Linux from the Oracle Store here. Installed the OS on a Desktop Machine and then, downloaded Oracle Business Intelligence Enterprise Edition OBIEE and particularly the Linux version file biee_linux_x86_redhat_101333_disk1.cpio from Oracle Downloads Website.
One thing I noticed when I was reading about OBIEE is that it is "Database Agnostic", that is it doesn't need a database to operate, although you can put it in XMLDB. But this is not necessary as OBIEE can live on a file system.
That is what I did and I run the command on the downloaded file like this from my Linux prompt
$ cpio -idmv < biee_linux_x86_redhat_101333_disk1.cpiowhen the file extracted gave me following directory structure
/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/
/home/oracle/RH_Linux/Server/Oracle_Business_Intelligence/setup.sh
Once I have correctly indicated the directory where my Java JDK 1.5 (or greater) was installed the installation was smooth and finished quickly. I have chosen the full complete installation of OBIEE from the install options and it put software in the designated Oracle BI homes.
/home/oracle/OracleBI
/home/oracle/OracleBIData
This page tells it all. You get links to the 3 main components of your OBIEE installation on the right top corner of this page and these are:
- Application Server Control
- Oracle BI Interactive Dashboards
- Oracle BI Publisher
Anyway I found the /home/oracle/OracleBI/setup directory to be full of cool xxxx.sh scripts which you can use to start stuff in OBIEE.
After successfully starting Oracle BI Server, Oracle BI Presentation Services (SAW server) I have finally managed the get all 3 links to work and started exploring the Oracle BI Infrastructure.
In another post I hope to write about my thoughts on OBIEE.
NOTES:
OBIEE Defaut User is : Administrator
OBIEE Default User password is : Administrator
Resources I used for the OBIEE installation.
Subscribe to:
Posts (Atom)