Database.fi

In certain cases Oracle does not start automatically on Linux after installation.
To fix this, you need to do these steps:
Modify oratab
vi /etc/oratab
change the “N” at the end => “Y”
For example:
From: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N
To: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y
Create a dbora file under /etc/init.d/
vi /etc/init.d/dbora
#!/bin/sh
# chkconfig: 345 99 10
# description: Automatic Oracle database start-stop script.
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo “Oracle startup: cannot start”
exit
fi
case “$1″ in
’start’)
# Start Oracle databases:
su – $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME”
touch /var/lock/subsys/dbora
;;
’stop’)
# Stop Oracle databases:
su – $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME”
rm -f /var/lock/subsys/dbora
;;
esac
If you modify the script above, remember to keep the 3 first commented lines (shell, chkconfig and description).
Change the privileges to appropriate level with:
chmod 750 /etc/init.d/dbora
Now add the script to chkconfig:
chkconfig –add dbora
You can check this with:

$> chkconfig –list | grep db
dbora 0:off 1:off 2:off 3:on 4:on 5:on 6:off
You’re done, verify after next reboot:
ps -ef | grep smon

If you encounter this error on Oracle it is a good possibility that you have SELinux enabled on your Linux box:

$> sqlplus / as sysdba
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied
The quick way to get Oracle working is to use setenforce:

$> setenforce 0
$> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 15:31:35 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
…
However, this solution will only work until you reboot the server.
To permanently fix this issue you need to modify the SELinux configuration file:

$> vi /etc/selinux/config
Change:
SELINUX=enforcing
To:
SELINUX=disabled
You should also be able to use permissive mode on single instances, if you change to this mode BEFORE you install Oracle:
SELINUX=permissive
CRS will not like permissive mode, so this does not apply for RAC installations.
Now SQL*Plus works:

$> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 15:31:35 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.

I have been working mostly with “normal” character set databases.
The last project however uses Unicode database (AL32UTF8) in 11.2
I ran into this strange behaviour (until I knew better):

create table test(field varchar2(10));
Table created.
SQL> insert into testi values (‘öäåÖÄÅ’);
insert into testi values (‘öäåÖÄÅ’)
*
ERROR at line 1:
ORA-12899: value too large for column “”TEST”.”FIELD” (actual: 18,
maximum: 10)
SQL> insert into test values (‘öä’);
1 row created.
So – I created a field for 10 characters (or so I thought!) and tried to store only 6 characters and failed.
What an odd thing.
Closer look tells that each character actually uses 3 bytes each:

SQL> select dump(field) from test;
DUMP(KENTTA)
——————————————————————————–
Typ=1 Len=6: 239,191,189,239,191,189
1 row selected.
All right, so it seems that there is actually space for only 10 BYTES – not characters…
After 5 seconds of furious googling the reason is clear and my world shattered, again.
The VARCHAR2(10) REALLY means VARCHAR2(10 BYTE).
What I should have always used is VARCHAR2(10 CHAR).
A new test:

create table test2 (field varchar2(10 char));
=>
SQL> insert into test2 values(‘öäåÖÄÅ’);
1 row created.
How embarrassing after so many years of working with Oracle

This is something I learned today by accident
There are times when you need to optimize physical data arrangement in database, in this case in a database table. The reasons for this vary from row migration to concurrency problems.
Here is one way to limit rows inside database table to a set value.
You must understand this is may not be the best way to accomplish this.
Of course this can be done also by setting PCTFREE to a high value, but that is not as exact science as this.
Here goes:

create table test(field number);
insert into test values(1);
commit;
alter table test minimize records_per_block;
–From this point on, number of rows per block will be limited to 1.
I learned this from Mark J. Bobak from Oracle forums.
Here are his words:
“The alter table … minimize records_per_block;
functionality sets the Hakan factor. This limits the number of rows per block to the max of the largest number of rows in any block in the table. This functionality was originally conceived to help optimize builds of bitmap indexes, but it can be used anytime you want to ‘artificially’ limit the number of rows per block.”

Just a quick reminder about maybe the most common small problem in new 11g installations / upgrades.
When entering the 11g world, remember to do something to the default setting of password life time of 180 days. If you do nothing, your users / schemas will cease to work after half an year from creation.
select profile, limit from dba_profiles where resource_name = ‘PASSWORD_LIFE_TIME’;
PROFILE LIMIT
—————————— —————————————-
DEFAULT 180
MONITORING_PROFILE DEFAULT
If you want to remove the limits altogether, here is how to do it:
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.
Verify with:
1* select profile, limit from dba_profiles where resource_name = ‘PASSWORD_LIFE_TIME’
PROFILE LIMIT
—————————— —————————————-
DEFAULT UNLIMITED
MONITORING_PROFILE DEFAULT

I had a question in training about OS authentication in Windows.
The question was about normal user authentication, not the SYSDBA authentication.
Hmm. I had never used it for authenticating normal users – so I just had to try.
First I need to know which user I am:
SQL> select UPPER(sys_context(‘userenv’,'os_user’)) from dual;
UPPER(SYS_CONTEXT(‘USERENV’,'OS_USER’))
—————————————————————
PASICITRUS\PASI PARKKONEN
SQL>
The next step is to create that user and grant user rights:
create user “PASICITRUS\PASI PARKKONEN” identified externally;
User created.
SQL> grant dba to “PASICITRUS\PASI PARKKONEN”;
Grant succeeded.
Now I can try to connect:
SQL> connect /
Connected.
Who am I..?
SQL> show user
USER is “PASICITRUS\PASI PARKKONEN”
Great, done!

If you have problems in running Enterprise Manager (aka Database Control), you may need to reinstall it.
Here is how to do it.

Run the following command: emca -deconfig dbcontrol db -repos drop

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at 3.2.2010 13:10:47
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
3.2.2010 13:13:34 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\app\oracle\cfgtoollogs\emca\orcl\em
_2010_02_03_13_10_47.log.
3.2.2010 13:13:45 oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be
erformed.
3.2.2010 13:13:50 oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
3.2.2010 13:34:40 oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at 3.2.2010 13:34:40

The process may take a while.

After the drop you can reinstall the Enterprise Manager with: emca -config dbcontrol db -repos create

C:\>emca -config dbcontrol db -repos create

STARTED EMCA at 3.2.2010 13:37:18
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. C:\app\oracle\product\11.1.0\db_1

Local hostname ……………. IP_address
Listener port number ……………. 1521
Database SID ……………. orcl
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
3.2.2010 13:38:43 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\app\oracle\cfgtoollogs\emca\orcl\emca
_2010_02_03_13_37_18.log.
3.2.2010 13:38:45 oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
3.2.2010 13:53:25 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
3.2.2010 13:53:25 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://IP_address:5500/em <<<<<<<
<<<<
3.2.2010 13:53:27 oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager
data will be encrypted. The encryption key has been placed in the file: C:\app\
oracle\product\11.1.0\db_1\10.8.0.171_orcl\sysman\config\emkey.ora. Please ens
ure this file is backed up as the encrypted data will become unusable if this fi
le is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at 3.2.2010 13:53:27

Great! Now you can try to log in: https://IP_address:5500/em