Oracle BI Forum in Dubai

Oracle Business Intelligence Forum in dubai
Learn More About Oracle’s Business Intelligence Applications

At this event you will:

• Learn how Oracle Business Intelligence delivers intuitive, role-based intelligence for everyone in an organization—from frontline employees to senior management—and enables better decisions, actions, and business processes
• Get an overview of how Oracle’s hot-pluggable BI architecture addresses the challenges of heterogeneous Oracle and non-Oracle IT environments
• Gain knowledge on how to enhance the value of your business intelligence solution with Oracle Data Warehousing and Oracle Data Integrator

Click on the links of your country to register now. Or call +97143909220 for assistance

LogMiner session

Starting, using, and ending a LogMiner session
We are now ready to mine for gold, well, SQL. Suppose Scott calls you and says he deleted rows from his emp table (where empno is greater than 7900). It was a mistake, and he needs the data restored to his table. The first step is to start LogMiner and populate v$logmnr_contents. This view or “table” is what you query against to extract the SQL_REDO and SQL_UNDO statements.
There are several options as to how you gather the contents. More than likely, you’re going to know a time range as opposed to an SCN number, so knowing approximately when Scott deleted the rows is all we need from him (aside from the table name).

SQL> exec dbms_logmnr.start_logmnr( -
> dictfilename =>
‘c:\ora9i\admin\db00\file_dir\
dictionary.ora’, -
> starttime =>
to_date(’06-Jun-2004 17:30:00′,
‘DD-MON-YYYY HH24:MI:SS’), -
> endtime =>
to_date(’06-Jun-2004 17:35:00′,
‘DD-MON-YYYY HH24:MI:SS’));

PL/SQL procedure successfully completed.

Now we are ready to see what took place.
SQL> select sql_redo, sql_undo
2 from v$logmnr_contents
3 where username = ‘SCOTT’
4 and seg_name = ‘EMP’;

SQL_REDO
——————————————————————————————
SQL_UNDO
——————————————————————————————
delete from “SCOTT”.”EMP” where “EMPNO” = ‘7902′ and “ENAME” = ‘FORD’ and “JOB” = ‘ANALYST
‘ and “MGR” = ‘7566′ and “HIREDATE” = TO_DATE(’03-DEC-81′, ‘DD-MON-RR’) and “SAL” = ‘3000′
and “COMM” IS NULL and “DEPTNO” = ‘20′ and ROWID = ‘AAAHW7AABAAAMUiAAM’;

insert into “SCOTT”.”EMP”(”EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”)
values (’7902′,’FORD’,'ANALYST’,'7566′,TO_DATE(’03-DEC-81′, ‘DD-MON-RR’),’3000′,NULL,’20′);

*****************************************************************************
delete from “SCOTT”.”EMP” where “EMPNO” = ‘7934′ and “ENAME” = ‘MILLER’ and “JOB” = ‘CLERK
‘ and “MGR” = ‘7782′ and “HIREDATE” = TO_DATE(’23-JAN-82′, ‘DD-MON-RR’) and “SAL” = ‘1300′
and “COMM” IS NULL and “DEPTNO” = ‘10′ and ROWID = ‘AAAHW7AABAAAMUiAAN’;

insert into “SCOTT”.”EMP”(”EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”)
values (’7934′,’MILLER’,'CLERK’,'7782′,TO_DATE(’23-JAN-82′, ‘DD-MON-RR’),’1300′,NULL,’10′);

************************************************************************
delete from “SCOTT”.”EMP” where “EMPNO” = ‘7935′ and “ENAME” = ‘COLE’ and “JOB” = ‘LINDA’
and “MGR” = ‘7839′ and “HIREDATE” = TO_DATE(’01-MAY-04′, ‘DD-MON-RR’) and “SAL” = ‘4000′
and “COMM” IS NULL and “DEPTNO” = ‘30′ and ROWID = ‘AAAHW7AABAAAMUiAAO’;

insert into “SCOTT”.”EMP”(”EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”)
values (’7935′,’COLE’,'LINDA’,'7839′,TO_DATE(’01-MAY-04′, ‘DD-MON-RR’),’4000′,NULL,’30′);

You can see how Oracle (shading and “****” lines were added for readability) took the “delete from emp where empno > 7900″ statement and turned it into something more complex. It should be apparent that the SQL_UNDO statements are practically in a cut and paste state, ready for immediate use.
To end your LogMiner session, issue the following command:
SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

flashback query 9i+

Two powerful tools make queries on past data available now (9i+)
If you recently upgraded to 9i or 10g, you have powerful new tools for taking snapshots of data from the past.

The UNDO tablespace and its associated UNDO_RETENTION setting give you and your users:
* The ability to run reports from a certain point in time.
* The ability to use a SQL script to re-create accidentally changed or deleted data.

The syntax for performing a flashback query is AS OF. You use this clause together with a data mask,
as in SELECT deptno, sum(Sal) FROM emp GROUP BY deptno AS OF TO_DATE(’13-Jul-05′ 09:00, date HH:MM);.

To enable this feature:
* Set the parameter UNDO_MANAGEMENT=AUTO.
* Set the UNDO_RETENTION parameter to tell Oracle how many days back you need it to store old data.
* Create an UNDO tablespace with ample room to store the past data.
* Grant the FLASHBACK privilege on specific tables, or FLASHBACK ANY TABLE privilege to users and roles who need to use this feature.

Loss of Server Parameter File (spfile)

If your server parameter file (spfile) becomes corrupt, and you haven’t been creating a textual init.ora parameter file as a backup, you can pull the parameters from it using the strings command in UNIX to create an init.ora file. You will need to edit the resulting file to get rid of any garbage characters in it (but don’t worry about the “*.” characters at the beginning of the lines) and make any corrections to it before using it to start your database, but, at least you will have something to go by:

C:\> Copy the InitPROD.ora from the backup to $ORACLE_HOME\dbs

If you have been saving off a textual init.ora parameter file as a backup, you can restore that init.ora file to the $ORACLE_HOME/dbs directory in UNIX (or $ORACLE_HOME\database directory in NT).
You will need to delete the corrupt spfile before trying to restart your database, since Oracle looks for the spfile first, and the init.ora file last, to use as the parameter file when it starts up the database (or, you could leave the spfile there and use the pfile option in the startup command to point to the init.ora file). Then, once your database is up, you can recreate the spfile using the following (as sysdba):

sqlplus “/ as sysdba”
sql> create spfile from pfile;

Failure During Hot Backup:

If you have a failure while you are doing a hot backup on a tablespace, besides doing any recovery that is needed for the particular failure, you will also need to bring those tablespace datafiles back out of hot backup mode. To do this, while the database is in a mount state, do an “end backup” on each of those datafiles before opening the database.

sqlplus “/ as sysdba”
SQL> startup mount
SQL> select df.name,bk.time from v$datafile df,v$backup bk
where df.file# = bk.file# and bk.status = ‘ACTIVE’;
Shows the datafiles currently in a hot backup state.
SQL> alter database datafile
‘c:\oracle\oradata\PROD\devl_PROD_01.dbf’ end backup;
Do an “end backup” on those listed hot backup datafiles.
SQL> alter database open;