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;

Oracle 9i Recovery (Failure During Hot Backup)

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;

Loss of TEMP Datafile:
Symptoms: On large sorts (select distinct, order by, group by, union) that can't be done in memory, the sort will fail with "ORA-01157: cannot identify data file 3 - file not found" if the loss happened in the middle of the sort, or "ORA-01116: error in opening database file 3" if the loss happened before the sort started, along with the file name "ORA-01110: data file 3: '/u03/oradata/PROD/temp_PROD_01.dbf'". Nothing is put into the alert.log file, and no trace files will be generated for this.

sqlplus "/ as sysdba"
SQL> alter database datafile 'E:\ORACLE\ORADATA\DBORA\TEMP01.DBF’ offline;
Then, if from mount state, do an "alter database open;" here.
SQL> select file_name, bytes/1024 kbytes from dba_temp_files;
Shows the size of the TEMP datafile(s) in Kbytes.
SQL> select initial_extent/1024 kbytes from dba_tablespaces
where tablespace_name = 'TEMP';
Shows the uniform size of the extents in Kbytes.
SQL> drop tablespace temp;
SQL> delete E:\ORACLE\ORADATA\DBORA\TEMP01.DBF
SQL> create tablespace temp
tempfile ' E:\ORACLE\ORADATA\DBORA\TEMP01.DBF' size 2655232
extent management local uniform size 1024K;

you must change the path according to your physical drive paths and change the DBORA, which is the database files folder.

Oracle 9i Recovery (Loss of Control Files)

Loss of Control Files :

Symptoms: May be none until you try to shutdown and startup the database. On shutdown, if the control files were deleted, you would get
"ORA-00210: cannot open control file 'path/ctrl_PROD_01.ctl'", or, if the control files were overwritten, you would get "ORA-00201: control file version incompatible with ORACLE version" along with their names. On startup for both cases, you would get "ORA-00205: error in identifying control file 'path/ctrl_PROD_01.ctl', along with "ORA-07366: sfifi: invalid file, file does not have valid header block." if overwritten.

Action: If you have a recent up-to-date textual control file (no added datafiles) from "alter database backup controlfile to trace;" in your user_dump_dest directory, you can just edit out the control file header and run it:

sqlplus "/ as sysdba"
SQL> shutdown abort
c:\> dir c:\oracle\admin\PROD\udump\*.trc
Get the latest textual control file in user_dump_dest directory, such as prod_ora_31494.trc (the last one listed).
SQL>Edit c:\oracle\admin\PROD\udump\prod_ora_31494.trc
Edit the textual control file and remove all lines before the STARTUP NOMOUNT line, remove the lines after ALTER DATABASE OPEN, and, for Oracle 9i and above, and change all comment lines to start with dashes. The vi commands to do these (for 9i+) are usually:
:1,/STARTUP NOMOUNT/-1d
:/ALTER DATABASE OPEN/+1,$d
:1,$s/^#/--/
:wq
SQL> @ c:\oracle\admin\PROD\udump\prod_ora_31494.trc

If you don't have a textual control file, you'll need to restore all datafiles and control files, but, not online redo log files, from the last backup and do a recovery "using backup controlfile":

sqlplus "/ as sysdba"
SQL> shutdown abort
At this point, restore ALL datafiles AND control files from the last backup, along with any archivelogs that are needed since that time, but, NOT the online redo log files.
SQL> connect / as sysdba
SQL> startup mount
SQL> recover automatic database using backup controlfile;
Then, AUTO on "ORA-00308: cannot open archived log ...".
SQL> alter database open resetlogs;
Immediately shut down the database and make a BACKUP, since the old backup is unuseable after RESETLOGS is used.

Note:- that you can combine the recovery options depending on what's needed. For example, I needed to Recover up to a point in time before a datafile was added:
1- shutting down the database
2- repeat the above steps, including deleting the added datafile.
and substituting the following recover command (you won't enter AUTO as above, and, when it's finished the partial recovery, it will show "Media recovery cancelled."):

SQL> recover automatic database using backup controlfile until time '2001-07-16:13:15:00';

Oracle 9i Recovery (Tablespace Recovery)

3) Tablespace Recovery:

The Recover Tablespace option is used to recover all datafiles needing recovery in a tablespace up to the point of failure, synchronizing them with the other datafiles (complete recovery only).

C:\> sqlplus "/ as sysdba"
SQL> startup mount
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' offline;
SQL> alter database open;
SQL> alter tablespace development offline;
SQL> ocopy /u03/oradata/prod1/devl_PROD* /u03/oradata/PROD
SQL> recover automatic tablespace development;
SQL> alter tablespace development online;
Note:- development is the tablespace_name

If a write error occurs on one of the datafiles when you offline a tablespace, use
"alter tablespace tsname offline temporary;". Then, you can run a recovery on the datafile.
If all datafiles in an offlined tablespace have write errors, use "alter tablespace tsname offline immediate;". Then, you can run a recovery on the tablespace.

Oracle 9i Recovery (Datafile Recovery)

2) DATAFILE RECOVERY

Recover Datafile is performed either from the MOUNT state (after a shutdown), with the datafile ONLINE or OFFLINE, or from the OPEN state, with the datafile OFFLINE. A bad datafile must be taken offline before the database can be opened.

The basic steps for Recover Datafile from the OPEN state (except for SYSTEM):

c:\> sqlplus "/ as sysdba"
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' offline;
SQL> alter database open;
The database is now available to the users, except for that datafile.
Copy the datafile from backup.
SQL> Copy /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
SQL> Recover automatic datafile '/u03/oradata/PROD/devl_PROD_01.dbf';
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' online;

The basic steps for the MOUNT state are:

C:\> copy /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
C:\> sqlplus "/ as sysdba"
SQL> startup mount
SQL> recover automatic datafile '/u03/oradata/PROD/devl_PROD_01.dbf';
SQL> select * from v$datafile;
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' online;
SQL> alter database open;

1) Database Recovery:

First make a BACKUP of your datafiles, control files, archive log files, and initialization parameters file (init.ora) for your database that crashed.

A) Complete Recovery: Recover all datafiles needing recovery up to the point of failure. 

Check the datafiles needed recovery.

COL df# FORMAT 999

COL df_name FORMAT a20

COL tbsp_name FORMAT a10

COL status FORMAT a7

COL error FORMAT a10

SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, 

       d.STATUS, r.ERROR, r.CHANGE#, r.TIME

FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t

WHERE t.TS# = d.TS#

AND d.FILE# = r.FILE#

COL df# Clear

COL df_name Clear

COL tbsp_name Clear

COL status Clear

COL error Clear

1- Copy the datafiles that need recovery from the last backup.

2- sqlplus "/ as sysdba"
SQL> startup mount
SQL> select * from v$datafile;
Shows status of datafiles, indicating those that are offline.
SQL> alter database datafile ‘datafile path’ online;
Online the offline datafiles shown above, such as devl_PROD_01.dbf here.
SQL> set autorecovery on
Tells recovery not to ask which redo logs to use (in log_archive_dest).
SQL> recover database;
Recovers all datafiles, as needed, up to the current point in time.
SQL> alter database open;
Opens the database for user access.

You may also need to drop and recreate the TEMP tablespace after a database recovery. See Loss of TEMP Datafile on how to do this.

B) Incomplete Recovery: Recover upto point in the past before the failure occurs.

SQL> recover automatic database until time '2005-02-14:15:45:00';
Automatic is similar to Set Autorecovery On. Use 1 second before the time of failure or of the log to exclude, which, for Oracle 8.x and above, could be found using:
select to_char(a.time,'YYYY-MM-DD:HH24:MI:SS') as time, a.name from (select first_time as time, substr(name,1,58) as name, first_change# as change from v$archived_log union select first_time, 'Redo Log Group ' || to_char(group#), first_change# from v$log) a order by change;

SQL> recover database until cancel;
Accept logs until you type in CANCEL.
SQL> recover automatic database until change 43047423;
The system change numbers (SCN) contained in each archive log file is shown in Oracle 8.x and above using: select name,first_change#,next_change# - 1 from v$archived_log;
SQL> alter database open resetlogs;

Note:- immediately shut down the database and make a BACKUP

Oracle 9i Disaster Recovery

Disaster Recovery Overview
The first thing you should ALWAYS do when your database crashes is to make a BACKUP of your datafiles, control files, archive log files, and initialization parameters file (init.ora) for your database that crashed, either copying those files to tape or to another directory, or zipping them with something like GNU Zip, or making a tar file of them. DO NOT TRY TO RESTART YOUR DATABASE BEFORE BACKING UP THOSE FILES. Otherwise, you might make the situation worse.

After a crash occurs and you've made a backup, do a STARTUP when connected as sysdba. If there are problems encountered during startup, Oracle will display an error message about the first problem, which will usually give you an idea about what type of recovery scenario you will need to apply. In most cases, you will need to do a shutdown after this initial startup attempt before beginning the recovery.

There are three primary recovery options for media failure scenarios. The three recovery options are:

1) Database Recovery 2) Datafile Recovery 3) Tablespace Recovery

Backup Files

What To Back Up:

· Datafiles (for all tablespaces)

· Control Files (binary and textual versions)

· Redo Log Files (cold backups only, not hot backups)

· Archive Log Files (archived redo logs, if archivelog mode is enabled)

· Parameter Files (init.ora; like $ORACLE_HOME/dbs/initPROD.ora)

· Password Files (like $ORACLE_HOME/dbs/orapwdPROD, if used)

Note:- RedoLog Files not to be included in the Hot Backups

List the Data Files, Temp File, Control file and Redo Log files to be included later in the backup:
V$datafile; v$tempfile; v$controlfile; v$logfile; dba_data_files; dba_temp_files; v$archived_log;

SQL> SELECT t.name tab_name, substr(f.name,0,45) datafile

from v$tablespace t, v$datafile f

where t.ts# = f.ts#

order by t.name;

SQL> select name from v$tempfile;

SQL> select name from v$controlfile;
SQL> column member format a45

SQL> select group#, member from v$logfile order by group#;

To get the list of archive log files for the last 5 days
SQL> select name from v$archived_log
where trunc(completion_time) >= trunc(sysdate)-5;

Bullet proof your Oracle database

Bulletproofing Oracle database:
Yes, you really can bulletproof your database so that it is always up and available and you never lose any data from almost any disaster scenario - for a price, whether in dollars, storage, servers, and/or bandwidth. You can do things such as running parallel servers in separate locations with standby databases for quick switchovers using Oracle's Data Guard package, along with using other options that Oracle and others provide. For the rest of us with or without large budgets and staff, there are things that we, too, can do to lessen the possibility of data loss, minimize downtime and make our databases more bulletproof, such as those described below:

1. Enable archivelog mode (to reapply the changes during recovery; which is required by most of the disaster recovery scenarios detailed below).
2. Separate the archive logs from the redo logs (allocating them to separate drives; likewise for the following items).
3. Separate the redo logs and archive logs from the datafiles.
4. Multiplex (mirror) the redo log groups and members.
5. Multiplex (mirror) the control file.
6. Multiplex (mirror) the archive log files.

Oracle Backup and Recovery 7-9i

Backups
The DBA's primary job is to make sure that the data is available and accessable by the users during those times that they need it, which means that a complete and well-tested backup and recovery procedure is in place and functioning. This section covers what files need to be included in that backup, the types of backups (cold backups and hot backups), and other processing that you could perform during the backup for proactive maintenance.

What To Back Up
The following types of files making up the database and providing recovery capabilities should be backed up during one backup cycle, such as all of them during a cold backup, or all or subsets of them during a hot backup cycle, assuming the entire set is eventually backed up during that cycle.

• Datafiles (for all tablespaces)
• Control Files (binary and textual versions)
• Redo Log Files (cold backups only, not hot backups)
• Archive Log Files (archived redo logs, if archivelog mode is enabled)
• Parameter Files (init.ora; like $ORACLE_HOME/dbs/initPROD.ora)
• Password Files (like $ORACLE_HOME/dbs/orapwdPROD, if used)

The most basic way to get the names of the datafiles, control files, and redo log files through SQL is shown below, which can be used in both the startup mount state and the database open state:

SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

You can also get the tablespace name with the datafile and temp file names using:
SQL> select tablespace_name,file_name from dba_data_files order by tablespace_name;
SQL> select tablespace_name,file_name from dba_temp_files;

The list of archive log files which are currently on-line cannot be found through SQL. However, you can get the directory containing those archive logs, the format of the file names, and whether or not archiving was turned on in the init.ora parameter file using:
SQL> select name,value from v$parameter where name in ('log_archive_dest',
'log_archive_format','log_archive_start');

These archiving parameters can also be found in an sqldba session by issuing either of the following commands (but the first one might truncate the dest directory name):
SQL> show parameter archive
SQL> archive log list

To get the list of archive log files for the last 5 days (the date is when Oracle started writing to that redo log, not when it was copied to the archivelogs directory), which may or may not still be on-line, you can query the v$archived_log table in Oracle 8.x and above:
SQL> select name from v$archived_log
where trunc(completion_time) >= trunc(sysdate)-5;

Or, you can query the v$log_history table in Oracle 7.x:
SQL> select archive_name from v$log_history
where trunc(to_date(substr(time,1,8),'MM/DD/YY')) >=
trunc(sysdate)-5;

Note:- please send your commments to improve the articles.

Group by SQL Query Tuning.

Comparison of two group by Queries:
-- slower query
SQL > SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id <>

-- faster query
SQL> SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id <>

SQL> select deptno, empno, ename, sum(nvl(sal,0)) from scott.emp group by grouping sets ((deptno),(empno, ename));

-- display totals for the deptno's.
SQL> select deptno, sum(nvl(sal,0))
from scott.emp
group by grouping sets ((deptno));