Sunday, October 6, 2013

dbca errors ORA-24344 (SYS.RECOMP_CATMETA_VIEWS) , ORA-04031

 

I was trying to create database on a test VM and thought of allocating just 300MB to memory_target which was around 15% of available RAM for that VM.

Received these errors during dbca database creation:

 dbca30 dbca31 dbca32 dbca33

As ORA-04031 was raised for java pool and shared pool, its evident that the allotted memory for this instance is not adequate. As a consequence the SYS.RECOMP_CATMETA_VIEWS was having errors.

Did “Abort”, then went back to screen where memory_target can be changed. Changed it to 600MB, i.e. 30% of available RAM.

After this change these errors were not reported by dbca while creating the database.

 

HTH.

OUI “Move Log file” dialog box

 

When I tried to start OUI using “runInstaller”, first it showed a small window with hostname: hostname

ocmsfsdb17

After clicking OK, it showed a dialog box:

ocmsfsdb18

As you can see it says “A log of this session is currently saved as: /tmp/Orainstall…. Oracle recommends that if you want to keep this log, you should move it from the temporary location to a more permanent location. Specify a new log location.”

Clicked OK but nothing happened after that, OUI didn’t start the installation.

There was no clue what was happening.

I just wanted to see whether not having entry in /etc/hosts for this host is causing this strange issue. So I added the entry for this host to /etc/hosts file:

<IP> <Fully qualified hostname> <shortname>

 

THAT’S IT!!

After the change to /etc/hosts, OUI started successfully without showing above dialogs.

 

Hope this helps….

Create single instance database manually - mandb

 

1. Set environment variables

[oracle@ocmsfsdb ~]$ export ORACLE_SID=mandb
[oracle@ocmsfsdb dbhome_1]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@ocmsfsdb fast_recovery_area]$ export PATH=$PATH:$ORACLE_HOME/bin

2. Create pfile in database default location i.e. $ORACLE_HOME/dbs

[oracle@ocmsfsdb dbs]$ cat initmandb.ora
db_name=mandb
control_files='/u01/app/oracle/oradata/mandb/control01.ctl','/u01/app/oracle/fast_recovery_area/mandb/control02.ctl'
memory_target=632291328

 

3. Create the directories required to store controlfiles:

[oracle@ocmsfsdb dbs]$ cd /u01/app/oracle/oradata/
[oracle@ocmsfsdb oradata]$ mkdir mandb
[oracle@ocmsfsdb oradata]$ chmod 750 mandb

[oracle@ocmsfsdb oradata]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@ocmsfsdb fast_recovery_area]$ mkdir mandb
[oracle@ocmsfsdb fast_recovery_area]$ chmod 750 mandb

4. Connect to the instance:

[oracle@ocmsfsdb fast_recovery_area]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 6 18:34:05 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  631914496 bytes
Fixed Size                  1346784 bytes
Variable Size             377488160 bytes
Database Buffers          247463936 bytes
Redo Buffers                5615616 bytes

5. Before issuing create database, create required directories to store datafiles, redo log files.

In my case I am storing all the log files and datafiles under /u01/app/oracle/oradata/mandb and the directory is already created in step#3 above.

6. Run create database, modify it as per your directory structure: This command can be found at

http://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#i1008985

CREATE DATABASE mandb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mandb/redo01a.log','/u01/app/oracle/oradata/mandb/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/mandb/redo02a.log','/u01/app/oracle/oradata/mandb/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/mandb/redo03a.log','/u01/app/oracle/oradata/mandb/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mandb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mandb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mandb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mandb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mandb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24 

Database created.

 
Note: if passwords for sys and system are not specified then they will be set to default change_on_install and manager respectively.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

7. Run scripts to build data dictionary views:


In SQL*Plus, connect to your Oracle Database instance with the SYSDBA administrative privilege:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

In SQL*Plus, connect to your Oracle Database instance as SYSTEM user:

@?/sqlplus/admin/pupbld.sql

 


8. Add the entry for this database to /etc/oratab


The database creation manually is completed.

Create database using dbca (interactive) - idbcadb

 

Go to $ORACLE_HOME/bin and run dbca

[oracle@ocmsfsdb bin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/bin

[oracle@ocmsfsdb bin]$ ./dbca

 dbca1 dbca2 dbca3 dbca4 dbca5 dbca6

==> Invoke netca using ./netca from $ORACLE_HOME/bin

 netca1 netca2 netca3 netca4 netca5 netca6   netca7netca8netca9

Now return to dbca

 dbca8 dbca9 dbca10 dbca11

 dbca16 dbca17 dbca18 dbca19 dbca20 dbca21 dbca22 dbca23

 dbca25 dbca26 dbca27 dbca28 dbca29dbca34

Create database using dbca (silent) - sdbcadb

 

[oracle@ocmsfsdb bin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@ocmsfsdb bin]$ ./dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname sdbcadb -sid sdbcadb -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL
Enter SYS user password:
Enter SYSTEM user password:
Enter DBSNMP user password:
Enter SYSMAN user password:
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/sdbcadb/sdbcadb.log" for further details.