Reduce SGA size in Oracle 10g

Due to some limitation we need to decrease the SGA size of our IDES system.-

What is SGA ?

– The shared pool
– The large pool
– The Java pool
– The buffer cache
– The Streams pool
– The Log Buffer

All this memory areas together are the SGA.

we check the parameters & found that our database is running via SPfile.

We follow the below steps to perform this action:

1. login to sql / as sysdba

SQL> show SGA – Check current SGA size.

SQL> show parameter – check whether oracle is running via SPFILE or PFILE.

SQL> create pfile from spfile; ( pfile is created in dbs or database folders)

SQL>shutdown immediate

while checking parameters we found that our SGA_MAX_SIZE = 1 GB in working pfile.(init<SID>.ora)

we decrease the value 25% of current SGA_MAX_SIZE (512MB+256MB = 768 MB)

we also decrease the shared_pool_size value & db buffer cache value to 25%.

save the file.

Now start Database with this newly PFILE.

SQL>starup PFILE=”d:oracleSID102databaseinit<SID>.ora”

if database is successfully started then convert it to SPFILE.

SQL> create spfile from pfile;

SQL> shutdown immediate

SQL> startup

Now check the value of changed SGA.

SQL> show sga

Oracle upgrade 10.2.0.4 to 11.2.0.2 in windows

Few days back i did my Oracle Upgrade from 10.2.0.1 to 11.2.0.2  in Windows 2003/ multiple Oracle homes environment. I did it successfully with the help of upgrade guide from SMP.

Oracle upgrade 11.2.0.2 requires 10.2.0.4 version. first we upgrade 10.2.0.1 to 10.2.0.4 then 11.2.0.2.

I have also prepare the notes for the same. if any one requires or stuck on any phase, do let me know.

 

SAP workprocesses can not connect to the database any more.

SAP Note 1519872 

Symptom

This note describes the configuration of an SAP installation with the SAP-pecific database user profile SAPUPROF.

Symptom:
SAP system does not start any more.
SAP workprocesses can not connect to the database any more.

SAP trace files contain errors similar to the following:
TRACE-INFO: 101:  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error ‘28001’
TRACE-INFO: 102:  [     dev trc,00000]     set_ocica() -> SQL error code 28001
TRACE-INFO: 103:  [     dev trc,00000]    >oci_get_errmsg (con_hdl=0,rc=28001)
TRACE-INFO: 104:  [     dev trc,00000]     OCIErrorGet -> SQL error code: 28001
TRACE-INFO: 105:  [     dev trc,00000]     ORA-28001: the password has expired

Other symptoms:
You get ORA-28000 or ORA-28001 or ORA-28002 errors when you try to connect to the database (i.e. as database user SYSTEM).

Reason:

  • Oracle Database Release 11.2.0.X.
  • Database user profile SAPUPROF is not configured. Check this with the following query (for user SAPSR3):
    SQL> select profile from dba_users where username = ‘SAPSR3’;
  • With 11.2, passwords of database users must be changed every 180 days for security reasons.

For full solution, Please go through this note 1519872.

SAPSR3 user locked.

Recently we find an issue that while starting SAP via SAP MMC, work process stop in Ended status. We found in developer trace that it’s a database connectivity issue. In some cases connectivity from SAP to Database does not happen due to SAPSR3 user locked.

Steps to find the issue in Windows Environment.

1. Go to CMD – R3trans -d (it ended with return code 0012)

2. Read the trans.log & search if there any user lock issue found.

if YES then go to below procedure to resolved.

1. Stop the SAP process in SAP MMC.

1. go to sqlplus via “CMD – sqlplus / as sysdba

2. execute statement to check which user(SAPSR3) is locked.

select username, account_status from dba_users;

find the list of dba users with their status. if SAPSR3 user is locked then execute this below statement to unlock.

alter user SAPSR3 account unlock;

again execute to check whether it’s successful or not.

select username, account_status from dba_users;

if successful log out from SQL & execute “R3trans -d“. it finish with RC=0000.

START SAP & it will start.

BRBACKUP/BRARCHIVE failed, warning ORA-28011: the account will expire soon; change your password now.

New LIMIT for ‘PASSWORD_LIFE_TIME’ in Oracle 11g.

One of my colleague go through this solution.  Hope this helps you.

Some thing new in 11g, If you are using please rectify.

Any user in Oracle 11g using DEFAULT profile, has a default limit for PASSWORD_LIFE_TIME is 180 days, then that will be in GRACE period (Will never expire.)
So after 180 days it will throw the Warning ORA-28011: the account will expire soon; change your password now.
As a part of this if backups or jobs are scheduled from those user IDs, will start failing after 180 days.
Note: Prior to Oracle Database 11g Release 2 (11.2), the default was UNLIMITED. Ref SAP Note 1519872
Script to find out the same is,
select LIMIT, RESOURCE_NAME from dba_profiles where  PROFILE = ‘DEFAULT’ and  RESOURCE_NAME in (‘PASSWORD_GRACE_TIME’,’PASSWORD_LIFE_TIME’);
Remedy is,
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;