Javavm component invalid oracle 11g

If jvm component is invalid

Remove it

connect / as sysdba
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
@?/rdbms/admin/catnojav.sql

For better cleanup remove all java classes as well

delete from sys.obj$ o where o.type# = 29; -- classes

delete from sys.obj$ o where o.type# = 30; -- resources


Once everything is gone shutdown

shutdown;

Startup;

@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql


you might encounter error saying exfsys already exists.

Drop exfsys user from db and rerun.

If you get password verification failed.

Goto default profile in your database and alter it verify function to null

alter profile default limit password_verify_function null;

Now run

@?/rdbms/admin/catexf.sql

Once user is created reboot database

Now run

@?/rdbms/admin/utlrp.sql


You might get a warning says ordim java components not properly loaded. Other than that everything else should be valid in dba_registry

Now fix ORDIM

@?/ord/im/admin/initimj.sql

exit session

log back in and run utlrp again

@?/rdbms/admin/utlrp.sql


Now you should see everything as valid in select * from dba_registry;

don't forget to change password verify function back to normal

alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function;


Note: While doing this in the middle you may notice xml,java,spatial,ordim components will be invalid or removed. Dont worry everything will be clean after completion.

Reference:

How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)

connection management call elapsed time Wait event on oracle DB

We have instance name db1 on the exadata server. This db is being monitored by platinum gateway as we have platinum support for exadata. Because we have done a lot of testing as poc and now its time to make it production i decided to drop and rebuild the database.

After i rebuilt it, In oem i started seeing lot of concurrency waits in brown color all i can get info from oem is library cache lock and most of the sessions having this lock are related to sys user.

So i want to find out whats actually causing the issue and generated a awr report and sure enough i found the same library cache lock here.

I ran ADDM report and it found the same concurrency wait event consuming 40% of time and it said might be related to shared pool size and there are no recommendations.

So i dig deep into AWR report and found a interesting wait event on top

connection management call elapsed time

Statistic Name Time (s) % of DB Time
connection management call elapsed time 19,275.19 99.37

Looks like most of it is because of this. So i decided some application is constantly trying to connect to the db and might be giving wrong password.

In our database if any user inputs incorrect password for 5 times account gets locked except for application and monitoring profile users. So i thought to start with dbsnmp user.

I just thought of checking dba_audit_trail and found orarom user ( which is a readonly account for platinum support) is contsantly trying to login and getting ORA-01017. I got this info from audit trail.

This is because when i created the new db i used a new password for dbsnmp. So i logged into platinum support gateway and changed the password. Immediately that wait event is gone.

Setting Hugepages on Exadata

Get the script for MOS support

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

Start all DB instances on the server

Now run the script from MOS support. It will give you a recommended value. Now configure hugepages for Linux

You need to be root to do this. If this is the first time you are configuring you might need to reboot the server.

Note: Huge pages value should be greater than or equal to all the SGA's combined on the server.

You can check the hugepages config using

$ grep Huge /proc/meminfo

Now run the following as root and enter the recommended value you got from the script.

# vi /etc/sysctl.conf

vm.nr_hugepages=55425

Save it and quit.

Now run

# sysctl -p

Now verify the changes using

$ grep Huge /proc/meminfo

If you dont see any changes you will need to reboot.

While modifying this also verify hard limit and soft limit in the following file

/etc/security/limits.conf

oracle    soft     memlock 237674430
oracle    hard     memlock 237674430

Now set the following in individual db instances

Note: It is a good idea to set value to TRUE because DB will fail to start if you configure only and enough memory is not configured in Huge pages.

We have USE_LARGE_PAGES parameter on the databases. Setting this parameter will improve memory management on the database.

We have 3 values for the above parameter TRUE, FALSE and ONLY

If you set to TRUE, DB will use huge pages and will be started even if hugepages values is less than SGA of database.
If you set to FALSE, DB will not use huge pages
If you set to ONLY, DB will fail to start if huge pages cannot be used for entire SGA of database. 

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

You might get this error while dropping a schema or tablespace or objects it self.

You need to drop using sys user with following syntax

execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'kmaedq_edqstaging.AQ$_EDQ_TOPIC_TAB_G', force => true);

Just mention the tablename in queue_table including schema name.

 Now you can drop the object

Migrating database using expdp and impdp checklist

Expdp and Impdp database migration checklist

On source

Purge dba_recyclebin;

expdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp full=y parallel=8

Use toad to get the ddl for all the roles and users.

Comment out all unnecessary stuff except grants. We will run this grants script at the end of import on Target.

On Target

Create target DB.

Make sure you have same character set.

Create all the tablespaces as source DB.

Create all the roles as source.

Create all the profiles as source.

Make sure you have same temp tablespace name as source.

Once the export is completed. Copy it to the corresponding location and start import. Its better to use nfs storage so that you can skip copying.

Import only the necessary schemas and exclude all the sys related schemas.

Make sure you have a log file assigned to each schema import.

Easier way to do is create script like this on your server and run in nohup so it will run in background.

vi import.sh

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=skwh1

impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x1 table_exists_action=replace logfile=x1_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x2 table_exists_action=replace logfile=x2_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x3 table_exists_action=replace logfile=x3_import.log


nohup ./import.sh &

Once everything is imported. Verify the logs.

You might see a lot of errors related to grants. Please ignore as we will run the scripts we captured from source.

Try to troubleshoot the errors.

Now run role grants script and user grants script.

If any materialized views failed to create as part of import. Re run now by choosing table_exists_action=skip so you dont need to import whole table again.

After solving all the issues. Go to $ORACLE_HOME/rdbms/admin and run @utlrp.sql

This should compile all invalid objects.

Changing Oracle Database characterset

Here is how to change the DB characterset

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';

select * from V$nls_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

or

select * from nls_database_parameters;

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

ALTER DATABASE CHARACTER SET AL32UTF8;

SHUTDOWN IMMEDIATE;

STARTUP;

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';

For RAC

You need to stop the database. 

Start in single instance

alter system set cluster_database= false scope=spfile;

shut immediate;

startup restrict;

and follow above steps and change cluster_database=true once done and start db using srvctl normally

*********************************************************************

some times you might need to change ncharset of database.. follow steps below

Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE
STARTUP

It is always not better choice to use 'ALTER DATABASE' command to change the character set.

Make sure to take a full backup before doing this

ASM diskgroup usage

To monitor ASM Diskgroup Usage

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'



break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report


SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

Credits: Gavin Soorma



Monitoring using OEM

select collection_timestamp ,key_value DISK_GROUP,round(value/1024/1024,2) "Free TB",round((round(value/1024/1024,2)/7.57*100),2) "Used Space"
from
(select distinct target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster' and target_name='+ASM_xxxipcva-clus')
join (
 select distinct
  target_type,metric_name,metric_label,metric_column,column_label,short_name,metric_guid
 from mgmt_metrics
) using(target_type)
join mgmt_metrics_raw using(target_guid,metric_guid)
where key_value = 'RECO_DG' and collection_timestamp >= sysdate-0.5/24 and metric_label like '%Usage' and column_label like '%Usable Free%' --and ROWNUM <= 5
order by collection_timestamp desc ;