I was importing an Oracle11g into Oracle12c. I had created some of the tables in advance to remove partitioning and the import log gave me the following error:

IMP-00003: ORACLE error 20000 encountered

ORA-20000: BSG_VC_RECENT invalid partition name

ORA-06512: at "SYS.DBMS_STATS", line 7840

ORA-06512: at "SYS.DBMS_STATS", line 13507

ORA-06512: at line 1

IMP-00017: following statement failed with ORACLE error 20000:

 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '4E'; SREC.MAXVAL :="

 " '4E'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"

 "RAY(404999154965717000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARR"

 "AY(25418); SREC.EPC := 1; DBMS_STATS.SET_COLUMN_STATS(NULL,'"BSG_RECENT"','""

 "EXCLUDE"','"BSG_VC_RECENT"',NULL,NULL,1,.00000491777480525612,0,srec,2,6); "

 "END;"

To solve this problem I added statistics=none to the command line when importing.

scheduler ORA-01001: invalid cursor ORA-06512: at "SYS.DBMS_AQ_INV" ORA-08102: index key not found, obj

I enabled the event JOB_OVER_MAX_DUR. for our scheduled jobs and all by a sudden the database jammed! It was dumping lika an horse!

from the trace-files:

----- Error Stack Dump -----

ORA-01001: invalid cursor

ORA-06512: at "SYS.DBMS_AQ_INV", line 208

ORA-08102: index key not found, obj# 12482, file 1, block 28009 (2)

ORA-06512: at "SYS.DBMS_AQADM", line 1471

ORA-06512: at line 1

----- Current SQL Statement for this session (sql_id=14ur5x3gfybjy) -----

 delete from "SYS"."AQ$_SCHEDULER$_EVENT_QTAB_H" where msgid = :1 and subscriber# = :2 and  name = '0' and address# = 0

From the alert.log:

ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []

ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_AQ_INV", line 208
ORA-08102: index key not found, obj# 12482, file 1, block 28009 (2)
ORA-06512: at "SYS.DBMS_AQADM", line 1471
ORA-06512: at line 1
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1085
ORA-06512: at line 1
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1085
ORA-06512: at line 1
Dumping diagnostic data in directory=[cdmp_20130918123540], requested by (instance=1, osid=13808 (J001)), summary=[abnormal process termination].

First stop the scheduler:

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');

I tried to rebuild the index:

SQL> Alter index sys.scheduler$_event_qtab_hist rebuild online;

No difference!

I tried to rebuild the table:

SQL> alter TABLE SYS.AQ$_SCHEDULER$_EVENT_QTAB_H move online;

alter TABLE SYS.AQ$_SCHEDULER$_EVENT_QTAB_H move online

                *

ERROR at line 1:

ORA-24005: Inappropriate utilities used to perform DDL on AQ table

SYS.AQ$_SCHEDULER$_EVENT_QTAB_H

No luck there!

But wait!!! An index on an index-organized table!?! Where did that one come from?

SQL> drop index SYS.SCHEDULER$_EVENT_QTAB_HIST;

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

All works great again!

The script to reproduce the index is as follows:

CREATE INDEX SYS.SCHEDULER$_EVENT_QTAB_HIST ON SYS.AQ$_SCHEDULER$_EVENT_QTAB_H

(SUBSCRIBER#, NAME, ADDRESS#)

LOGGING

TABLESPACE SYSTEM

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation , DBMS_AUDIT_MGMT.init_cleanup

I was trying to enable audit trail management in our database

SQL> BEGIN
  2    IF NOT DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_AUDIT_MGMT.init_cleanup(
  4        audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  5        default_cleanup_interval => 12 /* hours */);
  6    END IF;
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1039
ORA-06512: at line 3

from the manual:

"The INIT_CLEANUP procedure sets up the audit management infrastructure and sets a default cleanup interval for the audit trail records. The procedure also moves the audit trail tables out of the SYSTEM tablespace."

What I could se the SYSAUX tablespace was allowed to grow 2Gb and the AUD$ table was only 230Mb!

Oh, maybe the DBMS_AUDIT_MGMT was looking at the real free space in the datafile (already allocated disk space)!?!

SQL> ALTER DATABASE DATAFILE '/oradata/db1_sysaux01.dbf' RESIZE 4G;

Then all by a sudden the init_cleanup runs fine!

DBMS_SCHEDULER.STOP_JOB ORA-27486 insufficient privilege

Trying to stop scheduled job as an non-dba user: myUser

exec DBMS_SCHEDULER.STOP_JOB('myJob',force => true);


ORA-27486 insufficient privilege 
ORA-06512 at "SYS.DBMS_ISCHED", line 199
ORA-06512 at "SYS.DBMS_SCHEDULER", line 557


The user is lacking the system privilege: MANAGE SCHEDULER

solution:

grant MANAGE SCHEDULER to myUser;

I was trying to apply path 11.2.0.2.11 on Grid and database homes:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp


The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322/custom/server/16459322 failed  for /oracle/11.2.0.2
The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2

In the log /opt/oracle/grid/OPatch/crs/../../cfgtoollogs/opatchauto2013-09-10_09-09-27.log

2013-09-10 09:09:39: The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2
2013-09-10 09:09:39: The opatch version check failed with following error
2013-09-10 09:09:39: bash: /oracle/11.2.0.2/OPatch/opatch: Permission denied

I discovered I had unzipped the opatch-software as root and that caused the problem. Change to oracle:oinstall :

# chown -R oracle:oinstall /oracle/11.2.0.2/OPatch

Now all is working just fine:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp

Detected Oracle Clusterware install

Using configuration parameter file: /opt/oracle/grid/crs/install/crsconfig_params

Successfully unlock /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322  apply successful for home  /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893  apply successful for home  /oracle/11.2.0.2

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Sql server 2008 R2

Trying to shrink the logfile for out Team Foundation 2010 database:



BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'


DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

I get the followin error message:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Trying the solution with sp_repldone:

USE Tfs_DefaultCollection

GO

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

exec sp_replicationdboption @dbname = N'Tfs_DefaultCollection', @optname = N'merge publish', @value = N'true'

GO

Msg 20028, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 60

The Distributor has not been installed correctly. Could not enable database for publishing.

The replication option 'merge publish' of database 'Tfs_DefaultCollection' has been set to false.

Running opentran show we have some unreplicated data, that's really strange because there is no replication setup for this database:

dbcc opentran

Transaction information for database 'Tfs_DefaultCollection'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (193362:85316:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sp_removedbreplication Tfs_DefaultCollection

Msg 1205, Level 13, State 57, Procedure sp_MSrepl_clean_replication_bit, Line 25

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

alter database Tfs_DefaultCollection set offline with rollback immediate

Msg 1205, Level 13, State 68, Line 1

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

I founbd and killed one of my own sessions...

alter database Tfs_DefaultCollection set offline with rollback immediate

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Failed to restart the current database. The current database is switched to master.

sp_removedbreplication Tfs_DefaultCollection

alter database Tfs_DefaultCollection set online with rollback immediate

Now running opentran again:

dbcc opentran

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'

DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

Success!

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

On a newly installed Oracle 11.2.0.2 instance I tried to enable archive logging:


SQL> startup mount force


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

WTF!!!

SQL> alter database recover;

Database altered.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

!?!?!?

What happens if we do a new shutdown and startup mount (without force this time)?

SQL> alter database open;

Database altered.

SQL> shutdown

SQL> startup mount

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/11.2.0.2/dbs/arch

Oldest online log sequence     45

Next log sequence to archive   46

Current log sequence           46

SQL> alter database open;

Database altered.

All well again!  :)

oracle tablespace usage SQL script

This SQL could be usefull if you would lika a list of your tablespaces and be able to see how full they are:


SELECT tablespace_name,
       ROUND (used_bytes / (1024 * 1024)) used_mb,
       ROUND (max_bytes / (1024 * 1024)) max_mb,
       ROUND ( (used_bytes) / max_bytes * 100) percent_full
  FROM (SELECT ddf.tablespace_name,
               NVL (dfs.used_bytes, 0) used_bytes,
               ddf.max_bytes
          FROM (  SELECT tablespace_name, SUM (bytes) used_bytes
                    FROM dba_segments
                GROUP BY tablespace_name) dfs,
               (  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_data_files
                GROUP BY tablespace_name
                union
                  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_temp_files
                GROUP BY tablespace_name) ddf
         WHERE ddf.tablespace_name = dfs.tablespace_name(+));





TABLESPACE_NAME                   USED_MB     MAX_MB PERCENT_FULL
------------------------------ ---------- ---------- ------------
SYSAUX                               1478      32768            5
UNDOTBS1                               70       4096            2
USERS                                  44       4096            1
SYSTEM                                669      32768            2
UNDOTBS2                               18       4096            0
TEMP                                    0       4096            0

The script also includes the temporary tablespaces like TEMP and calculates the MAX_MB from the maxsize of the datafiles and not tha actual size.

Could not start database ORA-01092: Oracle instance terminated. Disconnection forced

Oracle11g CRS with an Oracle10g database.

When testing our new RAC installation we disconnected our NetApp storage brutally from the cluster by disconnecting the network cables.
Connected it back again and restarted the both nodes of the cluster. The crs came upp fine but the database failed to start.
The log shiws the following errors:

ORA-00604: error occured at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oradata/myDb/dbfile/undotbs01.dbf'



$ . oraenv
myDb1

$ sqlplus / as sysdba

SQL> startup mount

SQL> select name, status, file# from gv$datafile;

NAME                                          STATUS                FILE#
----------------------------------   ------------------  -----
/oradata/myDb/dbfile/undotbs01.dbf   OFFLINE             2
/oradata/myDb/dbfile/undotbs02.dbf   ONLINE               3
/oradata/myDb/dbfile/system01.dbf     ONLINE                1

The undo-tablespace has suddenly gone offline!

I tried the following:

SQL> alter database recover automatic datafile 2;

SQL> alter database datafile 2 online;

SQL> alter database open;

database opened


This could also be done from RMAN:


$ rman target / nocatalog

RMAN> startup mount

RMAN> recover datafile 2;

RMAN> sql 'alter database datafile 2 online';

RMAN> alter database open;

database opened

All well again!

PRKR-1005 : adding of cluster database testdb configuration failed, PROC-5: User does not have permission to perform a cluster registry operation on this key

PRKR-1005 : adding of cluster database myDb configuration failed, PROC-5: User does not have permission to perform a cluster registry operation on this key

I was struggling trying to get a failed databas installation, Oracle 10.2.0.5 2 nodes RAC, working. The database instances could be started manually with:

Logged in as oracle

$ sqlplus / as sysdba

SQL> startup


But the registration with the crs, in this case an Oracle 11.2.0.3, did not work and I tryed everything to unregister the instances and the database from the cluster:

$ srvctl remove instance -d myDb -i myDb1

$ srvctl remove database -d myDb

I restarted the cluster and crs_stat showed no database registered with crs! But...

$ srvctl add database -d myDb -o /oracle/10.2.0.5

Just wouldn't work! The databas was already registered!

Run the following as root

This is strange but can bve fixed. First check that you have a backup of the crs config:

$ ocrconfig -showbackup

Then stop all cluster services:

$ crsctl stop cluster -all

Export the settings to a temporary file:

$ ocrconfig -export /tmp/config

Edit the file /tmp/config and remove the lines containing myDb:

DATABASE.LOG.myDb

DATABASE.LOG.myDb1.INSTANCE

DATABASE.LOG.myDb2.INSTANCE

Import the setting back again:

$ ocrconfig -import /tmp/config

Start the cluster services:

$ crsctl start cluster -all

Now you can try to register again as oracle

$ srvctl add database -d myDb /oracle/10.2.0.5

ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX REBUILD ONLINE NOLOGGING compress 2 storage (initial 8k next 8k);
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

 
To compress (use key compression on) a partitioned index you first have to create the index with the compress keyword. Drop it and recreate!
 
If you try to alter the index:

SQL> ALTER INDEX  compress;

ORA-02243: ogiltigt argument för ALTER INDEX eller ALTER MATERIALIZED VIEW


After recreation (drop and create) you can compress each partition on the index if you like:


SQL> alter index rebuild
    partition compress 2;

Oracle RAC installation guide

Googling around I found this fantastic Oracle 10G RAC installation bible/cookbook of 263(!) pages. Enjoy!
http://www.zaptron.com/chn/cookbook-v2%5B1%5D.0-10grac%20r2%20-%20asm%20-%20aix5l%20-%20san%20storage.pdf

CRS-0213: Could not register resource LISTENER

Tried the following:

$ srvctl add listener -n myRadNode1 -o /oracle/11.1.0.6/


CRS-0213: Could not register resource 'ora.myRacNode1.LISTENER_MYRACNODE1.lsnr'.
 
Had a look in /opt/oracle/crs/log/qdb3h1/crsd/crsd.log
 
2011-09-29 18:35:48.266: [  CRSRES][1078892864] Missing required resources
 
What could be wrong?

$ crs_stat -t


Name Type Target State Host
-----------------------------------------------------------
ora.myRacDb.db application ONLINE ONLINE myRacNode1
ora....b1.inst application ONLINE ONLINE myRacNode1
ora....b2.inst application ONLINE ONLINE myRacNode2
NO VIP, NO GSD, NO ONS!

$ oifcfg getif

Showed nothing!


$ oifcfg setif -global eth0/X.X.X.0:public
$ oifcfg setif -global eth2/192.168.212.0:cluster_interconnect

I had to run /opt/oracle/crs/bin/vipca as root and after that:
 
$ crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora.myRacNode1.gsd application ONLINE ONLINE myRacNode1
ora.myRacNode1.ons application ONLINE ONLINE myRacNode1
ora.myRacNode1.vip application ONLINE ONLINE myRacNode1
ora.myRacNode2.gsd application ONLINE ONLINE myRacNode2
ora.myRacNode2.ons application ONLINE ONLINE myRacNode2
ora.myRacNode2.vip application ONLINE ONLINE myRacNode2
ora.myRacDb.db application ONLINE ONLINE myRacNode1
ora....b1.inst application ONLINE ONLINE myRacNode1
ora....b2.inst application ONLINE ONLINE myRacNode2


 
I was then able to add the listeners to the crs with srvctl add listener
 

ORA-27504: IPC error creating OSD context

I was installing Oracle 11gR1 two node RAC on vmware Linux red hat enterprise 64 bits
When trying to start the newly created instance, myRacDb2, on node 2 I received the following errors:

PRKP-1001 : Error starting instance myRacDb2 on node myRacNode2
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Sep 29 15:12:19 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name:
Connected to an idle instance.
SQL> ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:gethostbyname failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpiphost2
ORA-27303: additional information: nodename myRacNode2.myDomain.com
SQL> Disconnected

I then added myRacNode1.myDomain.com and myRacNode2.myDomain.com to the /etc/hosts file with the same ip's as myRacNode1 and myRacNode2

Then I issued the:
srvctl start instance -i myRacDb2 -d myRacDb

 and voila! it worked!

 Some people tend to get this error when uninstalling Oracle 10g from their cluster. Oracle then replaces the file libskgxp11.so in ORACLE_HOME/bin folder for some reason. Please check this link for more info: https://forums.oracle.com/forums/thread.jspa?threadID=650846

Oracle: How to reorganize data in a tablespace

There is actually no easy way to reorganize the data in a tablespace in Oracle11g.
You can't just use a command like ALTER TABLESPACE REORGANIZE or REORGANIZE TABLESPACE because there is no.

TNS-12508: TNS:listener could not resolve the COMMAND given

TNS-12508: TNS:listener could not resolve the COMMAND given

this error could indicate that you have typed the wrong command in lsnrctl or it could indicate that the parameter:

 

ADMIN_RESTRICTIONS_YOURLISTENERNAME = ON

is set in listener.ora (located in $ORACLE_HOME/network/admin) and you don't have permission to adminster the listener. With this parameter set it is only the user oracle that is allowed to run commands against the listener.

Oracle RAC how to start or stop cluser database with srvctl command

These commands are useful when handling the Oracle RAC databases. They can be executed from any of the nodes in the cluster:

$ srvctl start database -d dbName
$ srvctl stop database -d dbName

$ srvctl start instance -d dbName -i dbName1

$ srvctl start instance -d dbName -i dbName2

$ srvctl stop instance -d dbName -i dbName1

$ srvctl stop instance -d dbName -i dbName2

Oracle how to create table with "invisible" name

In Oracle it is possible to create a table with no name (just space):

create table " " 
   (col1 int,
     col2 varchar2(10));


Then you can use the following to select data:


select * from " ";

Oracle: How to create random test data multiple rows easy

To create a table with 100,000 rows with random values just type:

create table tableName (columnName1 number, columnName2 number);

insert into tableName (select rownum, dbms_random.value(1,100)

                        from dual

                      connect by level <= 100000);

commit;

Oracle: how to page / paging result in select

Paging in Oracle is not as easy as one could think.
If you would like your query to return lets say 100 rows with the start at row 1300:

select * from (
  select rowum as rn, b.*
    from (select a.columnName1, a.columnName2
            from tableName a
           order by a.columnName1) b
        ) c
    where c.rn between 1300 and 1300 + 100