Wendy and the Oracle

It’s been a while but here is a pretty bug, which occurs even in the newest Oracle Database versions:

I tried to create six materialized views on views in a remote database through a database link. (for whomever is interested: the remote database used the CDM Ruleframe with VAPIs so I was asked to select from the views instead of the tables).

Four of the six compiled nicely. The other two gave me this error (on 10.1.0.2 and 10.2.0.1):

ORA-00942: table or view does not exist
ORA-06512: at “SYS.DBMS_SNAPSHOT_UTL”, line 960
ORA-06512: at line 1

and when I tried on another database (10.2.0.3) the error turned out to be an ora-00600!

I solved this by making the materialized view complex, i.e. just putting select * from () around it.

The bug on metalink for this is here

Today I learned that even if you let tablespaces autoextend themselves, you also need to think about how much the maximum space is they are allowed to extend themselves to.

My customer complained about not being able to see their uploaded pdf files and all the tablespaces had autoextend on so I was quite confused, until I saw that one of them had reached its maximum size……

Today I wanted to run a copy of the database on my laptop on a machine at home, and this is what I did:

• I logged onto the database as SYSDBA
• I backed up my control files using ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

This resulted in the following script:

CREATE CONTROLFILE REUSE DATABASE "OLD_SID" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'E:ORACLEORADATAOLD_SIDREDO03.LOG'  SIZE 1M,
GROUP 2 'E:ORACLEORADATAOLD_SIDREDO02.LOG'  SIZE 1M,
GROUP 3 'E:ORACLEORADATAOLD_SIDREDO01.LOG'  SIZE 1M
DATAFILE
'E:ORACLEORADATAOLD_SIDSYSTEM01.DBF',
'E:ORACLEORADATAOLD_SIDRBS01.DBF',
'E:ORACLEORADATAOLD_SIDTEMP01.DBF',
'E:ORACLEORADATAOLD_SIDTOOLS01.DBF',
'E:ORACLEORADATAOLD_SIDINDX01.DBF',
'E:ORACLEORADATAOLD_SIDDR01.DBF',
'E:ORACLEORADATAOLD_SIDWORK01.DBF',
'E:ORACLEORADATAOLD_SIDTEMP02.DBF'
CHARACTER SET WE8ISO8859P1;

• I changed REUSE to SET and added the extra datafiles that the database I want to copy has.
• I shutdown the database
• I copied the datafiles from the old database to the new
• I started the database using STARTUP NOMOUNT
• I ran the create controlefile statement
• I tried to open the database using ALTER DATABASE OPEN RESETLOGS; This gave me this error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘C:ORACLEPRODUCT10.1.0ORADATAORCLSYSTEM01.DBF’

• I solved this by recovering the database using one of the redologs :

SQL> recover database using backup controlfile;

• and as the redolog I entered

C:oracleproduct10.1.0oradataorclREDO01.log
Log applied.
Media recovery complete.

• I then shut the database down and started it up again:
  alter database open resetlogs;

This worked

(EDIT)

I discovered my temp tablespace didn’t have any files, so I added one with this command:

ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:ORACLEPRODUCT10.1.0ORADATAORCLTEMP01.DBF’
SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;