Category Archives: ORACLE DBA

SET PASSWORD LIFE TIME TO UNLIMITED

Set the password life time to unlimited for DEFAULT profile.
SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile=’DEFAULT’;

RESOURCE_NAME LIMIT
——————————– —————————–
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180

RESOURCE_NAME LIMIT
——————————– —————————–
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Thank You !!

 

Find Plan Hash Values from SQLID

 

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/

 

Thank You !!

DATABASE SCHEMA SIZE from the database

set linesize 150
set pagesize 5000
col owner for a155

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;

Thank you !!

 

 

 

ORA-39181: Only partial table data may be exported due to fine grain access control

The below error appeared when trying to export the Schema:

ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKFLOW_ACTION_PARAM”
. . exported “GLOGOWNER”.”WORKFLOW_ACTION_PARAM” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST”
. . exported “GLOGOWNER”.”WORKING_LIST” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST_DETAIL”

SOLUTION:

Provide the below privilege to the schema which you are trying to export.

grant EXEMPT ACCESS POLICY to <SCHEMA NAME>;

 

Thank you !!

ORA-39097 Data Pump job encountered unexpected error

ERROR:
Export: Release 10.2.0.3.0 – Production on Wednesday, 08 August, 2007 10:12:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100
Another possible error received after applying a PSU or CPU patch.
ORA-39006: internal error
ORA-39213: Metadata processing is not available

CAUSE/CHANGE:

Applied a CPU or PSU patch.

SOLUTION:

Some DataPump Metadata stored in the METANAMETRANS$ table was missing. You can verify this by selecting from the table which will likely return no rows.

SQL> connect / as sysdba
SQL> select count(*) from metanametrans$;

COUNT(*)
———-
0

While on a database where this table is correctly populated, should return many rows similar to the following run against a 10.2 database:

SQL> select count(*) from metanametrans$;

COUNT(*)
———-
2499
Run the following scripts connected as SYSDBA:
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

 

Thank You !!

ORA-19693: backup piece already included

PROBLEM:

Encounter the following errors when trying to duplicate the database to another server using the RMAN backup:

ORA-19693: backup piece E:\RMAN_BACKUP\xxxxxxxxxxx already included

10168047_748681825176303_456468755789080273_n

SOLUTION:

The location of the RMAN backup pieces at the target and auxiliary/duplicate databases are
different. So, RMAN cannot see the RMAN backup pieces, because they are stored in a different directory at the auxiliary/duplicate database server.

Try with below command which may resolve issue
RMAN> catalog start with ‘E:\RMAN_BACKUP’;

 

Thank You !!

ORA-01940: cannot drop a user that is currently connected

SQL>drop user MY_USER cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘MY_USER’ and p.addr (+) = s.paddr;
SID           SERIAL#       STATUS           SPID
———   ———-    ——–       ————
579               27        INACTIVE        17849
279                9        INACTIVE        89543

SQL> alter system kill session ‘579,29’;
System alterred.

SQL> alter system kill session ‘279,9’;
System alterred.

Now try to drop USER:

SQL>drop user MY_USER cascade;
User dropped.

Thank You !!

Delete expired archivelogs using RMAN

Below commands will helpful for deleting expired archivelog files through RMAN :

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

Now check the output with below command it should not return any list

RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>

 

Thank You !!

ORA-12547 TNS: Lost Contact

Getting the following error when trying to connect using Sql*Plus

ERROR:
$sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 30 11:59:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

CAUSE:

1) to kernel parameters settings
2) Incorrect permissions on the ORACLE executable
3) Insufficient ulimit setting for stack
4) $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
5) Oracle binaries have not been linked correctly

SOLUTION:

1) Please check the notes below that provide the required settings for kernel parameters
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64

2) Please check permissions by running:

$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should show the correct permission which is:

-rwsr-s–x 1 oracle dba

If not, then please execute the following to correct the permissions:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

3) Check the current ulimit setting for stack:

ulimit -a

Check the install guide for your specific platform and version of Oracle and set the stack appropriately.

4) Check to ensure the following two files are not 0 bytes:

$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o

If yes, rename the following file:

$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o.bad

Then, relink the oracle binary:
$ relink oracle

5) Check the alert log for any errors (ORA-00020: maximum number of processes) and solve.

select * from v$resource_limit and check maximum utilization and limit values.

RESOURCE_NAME      MAX_UTILIZATION LIMIT_VALUE
————-           ————————– ————————–
processes      350                350
sessions      380                380

6) If the above does not resolve I suggest that you shutdown the database and listener and then “relink all”

Please refer the ORACLE SUPPORT DOCUMENT ID 422173.1

 

Thank You !!

Disable Case Sensitive in Oracle Database 11g

Prior to 11g Passwords in Oracle are not case sensitive. Now from 11g Oracle passwords are Case sensitive. Parameter sec_case_sensitive_logon has been included in Oracle 11g.

sql> create user test identified by test;
sql> Grant create session to test;
SQL> show parameter logon
NAME                                       TYPE                              VALUE
—————————-     ———————-   ——————————
sec_case_sensitive_logon     boolean                           TRUE

SQL> conn test/TEST
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn test/test
Connected.

Disable Password case sensitive:

SQL> alter system set sec_case_sensitive_logon=false;
System altered.

SQL> conn test/TEST
Connected.

Thank You !!