Oracle 10g/11g OCA Preparation Guidelines

The road map which help the people who are looking for Oracle 1og and 11g Certification exam, where to start.
What are the required steps for getting Oracle certified?
1. Select a track
2. Prepare for the test
3. Schedule the test
Step 1. Select the track
Oracle Database Administrator:

• Oracle 11g (OCA, OCP, OCM)
• Oracle 10g DBA (OCA, OCP, OCM)
• Oracle 9i DBA (OCA, OCP, OCM)

Oracle 9i or 10g Forms Developer:

• Oracle PL/SQL Developer Certified Associate
• Oracle Forms Developer Certified Professional

For the complete list, follow the Oracle university website and check where you fit in. http://www.oracle.com/education/certification/index.html?starthere.html
For Oracle 11g OCA, only two exams are required:
1. 1Z0-051 Oracle Database 11g: SQL Fundamentals I
or 1Z0-047 Oracle Database SQL Expert
2. 1Z0-052  Oracle Database 11g: Administration I

Step 2. Prepare for the test.
Recommended Ref. Oracle Books

• Oracle Database 10g OCP Certification All-In-One Exam Guide
• Sybex.Inc.OCA.Oracle.10g.Administration.I
• Oracle.Database.10g.Administration.Workshop.I (from Oracle University)

Read the Oracle Documentation Guides

• 10g Concepts
• 10g DB Admin Guide

(Download from Oracle full documentation http://tahiti.oracle.com)
Practice test

• Self-Test Software (250-300 questions)

http://www.selftestsoftware.com.
3. Schedule the test.

• Check your nearest Sylvan Prometric testing center

http://www.prometric.com

Change the Table Column Datatype with data using SQL

Follow the simple steps to change the column datatype which is also having a data inside.
Normally, it’s not possible to change the column datatype with the existing data.
Steps:
1. Add New temp Column with desired datatype.
SQL> Alter Table
Add temp datatype(size);

2. Update the data into new column.
SQL> Update
set temp =
where is not null;

3. Drop the original Column.
SQL> Alter table
Drop column ;

4. Rename the new column to original Name.
SQL> Alter table
rename column temp to ;


Note:- change the table_name, datatype, Original_columnname and size

Oracle Database Performance Tuning

For many people, Oracle database performance tuning is a hard thing and difficult to achieve. Actual user will not see any change done by the experts after the application deployment, So, I will start writing some articles on the Oracle performance tuning, required by any Application built on Oracle Database.

Most of issues of performance tuning will auto resolved, if the database design is properly done.
We will start with the database design improvements.

Close Oracle Reporting Engine Procedure

In the Application development, during running the oracle report engine every time you run the report.
Below is the code to close the reporting engine, if required by the application.



You can make this procedure using Oracle Forms Builder 6i

———————————————-
PROCEDURE close_rbe IS

  v_win_handle NUMBER;

  timer_id TIMER;

 

BEGIN

  v_win_handle := win_api_session.findAppwindow(’Reports Background Engine’, ‘rwrbe60.exe’, ‘WINDOW’, WIN_API.WINCLASS_REPORTSSERVER_V6, FALSE);

–  MESSAGE(TO_CHAR(v_win_handle), acknowledge);

  IF v_win_handle > 0 THEN

    IF win_api_session.Find3rdPartyApp (’Reports Background Engine’, ‘rwrbe60.exe’, ‘WINDOW’, TRUE, WIN_API.WINCLASS_REPORTSSERVER_V6, FALSE) THEN

      win_api_shell.sendkeys(v_win_handle, ‘%{F4}’, true);

    END IF;

  END IF;

  timer_id := Find_Timer(’Delay’);

  IF NOT Id_Null(timer_id) THEN

            Delete_Timer(timer_id);

  END IF;

  timer_id := CREATE_TIMER(’Delay’, 100, NO_REPEAT);

END;
——————————————————-

Step2: call  this procedure in the application to close oracle background reporting engine.
make sure, your application has such requirements.

DBMS_METADATA Error

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 Information in this document applies to any platform. 

SQL> select dbms_metadata.get_ddl(’TABLE’,’mytable‘,’myuser‘) from dual;

ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3688
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4544
ORA-06512: at "SYS.DBMS_METADATA", line 466
ORA-06512: at "SYS.DBMS_METADATA", line 629
ORA-06512: at "SYS.DBMS_METADATA", line 1246
ORA-06512: at line 1

Prior to 9.2.0.6, this situation is caused by:Bug.3361288 (80) AFTER
REGISTERING XML SCHEMA IN AL32UTF8 DATABASE, EXPORT FAILS WITH ORA-24324 

Solution # 1:

The recommendations are documented in:

Note.279065.1 Ext/Mod Full Export of Database fails with EXP-00056 ORA-06502 ORA-31605 ORA-22921

Solution#1. The solution is to reload the XML API:
 

Step 1. SQL> alter system enable restricted session;

No Body can login or start a new session while running the scripts

Step 2. run: (from $ORACLE_HOME/rdbms/admin):
 

catnomet.sql
rmxml.sql
to remove the xml subsystem

After that recreate it by running following scripts:
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql 

 All the scripts can be run only on testing environment and run in production at your own risk.

ORA-19206: Invalid value for query or REF CURSOR parameter

Today, while i import the schema into testing db (oracle 9i), it gives me following error,then i realize that i didn't run the catalog scripts after the db creation.

So, if you create the database by yourself, then always run these scripts under the sys schema. see the solution below:

ORA-19206: Invalid value for query or REF CURSOR parameter

What causes this error?
The queryString argument passed to DBMS_XMLGEN.newContext was not a valid query, or REF CURSOR.

Solution:
Steps to fix this:

1. connect sys/change_on_install as sysdba
2. Run the script catalog.sql, catproc.sql, catmeta.sql

@c:\oracle\ora92\rdbms\admin\catalog.sql
@c:\oracle\ora92\rdbms\admin\catproc.sql
@c:\oracle\ora92\rdbms\admin\catmeta.sql

Oracle BI Forum in Dubai

Oracle Business Intelligence Forum

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

Monday, May 5, 2008 8:30 a.m. – 12:45 p.m. Crowne Plaza Hotel Sheikh Zayed Al Nahyan Road Dubai Register Now!

Monday, May 12, 2008 8:30 a.m. – 12:45 p.m. Sheraton Hotel Olaya and Mecca Road Riyadh 11623 Register Now!

LogMiner session

Tip # 2: 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.

Tip # 1: flashback query 9i+

Tip: 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.

Oracle 9i Recovery (Loss of Server Parameter File)

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;