Friday, August 30, 2013

ORA-28002: the password will expire within 7 days


##########################
## Error
##########################

ORA-28002: the password will expire within 7 days

### Full Error

oralin_2 @ hostp03:/etc
> sqlplus system/pass_word@oralin_gvl

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 30 06:14:48 2013

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

ERROR:
ORA-28002: the password will expire within 7 days

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

##########################
## Cause
##########################

Error occured while trying to connect using a user.

##########################
## Command Executed
##########################

conn system/pass

##########################
## Solution
##########################

###

Check User Account Status :

 select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT


Check the Profile limit set for password life time

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD 180

If you dont want to alter the profile limit set, then change the password for the user by noting it values from user$ and then reset it back.

If you wanna change the profile limit then,

Alter profile default limit password_life_time unlimited;

SQL> Alter profile default limit password_life_time unlimited;

Profile altered.

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD UNLIMITED

After changing the profile limit also the user account will be in expired status only,

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT

Now change the password you would like or use the same password to set.

Alter user system identified by pass_word;

SQL> Alter user system identified by pass_word;

User altered.

SQL> select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';


USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         OPEN                                       DEFAULT

Thursday, August 29, 2013

ORA-27211: Failed to load Media Management Library


##########################
## Error
##########################

ORA-27211: Failed to load Media Management Library

### Full Error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2013 10:09:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

##########################
#  Error Occurred
##########################

Error occured while a backup was fired in one of the database of 11.2.0.3.0 version

##########################
## Command Executed
##########################

In RMAN,

backup archivelog logseq 5432 thread 1;

##########################
## Issue Description.
##########################

### 11.2.0.3.0


RMAN> backup archivelog logseq 5432 thread 1;

Starting backup at 29-AUG-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2013 10:09:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

This database was failed over from hostp02 to hostp03 due to some issue. Backup was working fine in hostp02 server. backup is not working after failover
to hostp03 server.

While checking found that libobk.so64 library of netbackup is not linked to oracle libraries.

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ls libob*
ls: libob*: No such file or directory

##########################
## Solution
##########################

So Created a symbolic link in $ORACLE_HOME/lib location and tested the backup and it worked fine.

ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ls -ltr libob*
lrwxrwxrwx 1 oracle oinstall 36 Aug 29 09:32 libobk.so -> /usr/openv/netbackup/bin/libobk.so64
oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib

RMAN>  backup archivelog logseq 5432 thread 1;

Starting backup at 29-AUG-13
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=2799 instance=oralin_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.1 (2011020316)

Tuesday, August 27, 2013

Different Methods to find or Check whether the installed Oracle Client Software is 32 bit or 64 bit in Linux / Unix Environments


########################
 Task
########################

To find whether the installed Client Software is 32 bit or 64 bit in Linux / Unix Environments

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

=====================================================================================================================
Method 1 :
--------
Using ORACLE_HOME properties File
=====================================================================================================================


$ cat $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml | grep -i architecture

     

From above output, we can find its 64 bit, 32 means then the client software is 32 bit.

=====================================================================================================================
Method 2 :
--------
Using File OS command
=====================================================================================================================

$ file $ORACLE_HOME/bin/sqlplus

/u01/app/oracle/product/11.2.0/client_1/bin/sqlplus: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

From above output, we can find x86-64, so this is 64 bit version.

=====================================================================================================================
Method 3 :
--------
From lib directories created during installation.
=====================================================================================================================

$ ls -l $ORACLE_HOME | grep lib

drwxr-xr-x  2 oracle oinstall 4096 Jun 26 14:26 jlib
drwxr-xr-x  3 oracle oinstall 4096 Jun 26 14:26 lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib exist, then it is 64 bit client.

If you have only $ORACLE_HOME/lib you need to use method 1 as there are client versions (11.2) where $ORACLE_HOME/lib32 directory does not exist on 64-bit client installations.

=====================================================================================================================
Method 4 :
--------
Using Perl Version
=====================================================================================================================

$ perl -v

This is perl, v5.8.8 built for x86_64-linux-thread-multi

Note: Perl is installed according to the bit version of the ORACLE_HOME but not the OS bit version.

=====================================================================================================================

ORA-20600: The specified target is in the process of being deleted.



##########################
## Error
##########################

ORA-20600: The specified target is in the process of being deleted.

### Full Error

Saving instance oralin_1 to repository and agent https://host01:3872/emd/main...java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = oralin_1)(target type = oracle_database)(target guid = 31427C7C89C2C89148EB7B775E90E20D) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2283 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2720 ORA-06512: at line 1
... finished.
Saving instance oralin_2 to repository and agent https://host02:3872/emd/main... ... finished.
Properties for instance oralin have been updated.

##########################
#  Error Occurred
##########################

When trying to add “oralin_1” instance in OEM. Above errors has occurred.

##########################
## Command Executed
##########################

Add instance of a cluster in OEM

##########################
## Issue Description.
##########################

Couple of minutes back, “oralin_1” instance was deleted through OEM and the same was tried to add back.

Checking the instance registration details in OEM Repository database,

Set lines 200
col target_name for a30
select target_name, target_type, target_guid from mgmt_targets where target_name like '%oralin%' and target_type='oracle_database';

TARGET_NAME                    TARGET_TYPE                                                      TARGET_GUID
------------------------------ ---------------------------------------------------------------- --------------------------------
oralin_2                       oracle_database                                                  83F1FC27B89BAEFB9F4077620D700303


##########################
## Solution
##########################

Only the 2nd instance details were available. So tried to register the same after 2 minutes and it worked like charm.

TARGET_NAME                    TARGET_TYPE                                                      TARGET_GUID
------------------------------ ---------------------------------------------------------------- --------------------------------
oralin_2                       oracle_database                                                  83F1FC27B89BAEFB9F4077620D700303
oralin_1                       oracle_database                                                  31427C7C89C2C89148EB7B775E90E20D

Temporary Tablespace Group : ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP


Our Application team has encountered the below error while running the month end jobs. So below are the recommendation which fixed the issue.

##########################
## Error
##########################

ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP

### Full Error

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance host01.example.com:aua01p2 (2)
ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3334
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3904
ORA-06512: at line 1

##########################
#  Error Occurred
##########################

Error occured while application month end jobs are running in 10.2.0.3.0 version

##########################
## Command Executed
##########################

Application jobs

##########################
## Issue Description.
##########################

### 10.2.0.3.0

We have a temporary tablespace of size 63 GB, due to month end multiple jobs were running at the same time and it errors out with unable to extend error.

##########################
## Solution
##########################

From oracle 10g onwards, we can create a temporary tablespace group such that if one tablespace in that group ran out of space then the other available tablespace will be used.

A new group will be created when a tablespace is assigned to the group.

SQL> Select * from dba_tablespace_groups;

no rows selected

Alter tablespace AU_TEMP tablespace group au_TMP_GROUP;

Alter tablespace USER_TEMP tablespace group au_TMP_GROUP;

SQL> Select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ -------------------------
au_TMP_GROUP                  USER_TEMP
au_TMP_GROUP                  AU_TEMP

And then change the tablespace of the user who is executing the job.

SET LINES 200
COL USERNAME FOR A25
COL ACCOUNT_STATUS FOR A18
COL DEFAULT_TABLESPACE FOR A20
COL PROFILE FOR A20
col password for a25
col temporary_tablespace for a15

select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;

USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         AU_TEMP     09-DEC-04

#### Assigning the user the newly created tablespace group.

Alter user au_adhoc temporary tablespace au_tmp_group;

SQL> Alter user au_adhoc temporary tablespace au_tmp_group;

User altered.

SQL> select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;
  2
USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         au_TMP_GROUP   09-DEC-04

#### To unassign the tablespace from the temp tablespace group.

Alter tablespace USER_TEMP tablespace group '';


SQL> Select * from dba_tablespace_groups;

no rows selected

Friday, August 23, 2013

ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []


##########################
## Error
##########################

ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []

### Full Error

check rman_dup_proddb_uatdb_08062013.log for RMAN Duplication Log.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/06/2013 23:26:29
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []

##########################
#  Error Occurred
##########################

Error occured while RMAN Duplication is performed, once duplication is done and when oracle tried to create controlfile in 10.2.0.3.0 version of database

##########################
## Command Executed
##########################

In sqlplus,

Create Controlfile Statment,

##########################
## Issue Description.
##########################

### 10.2.0.3.0


Error occurred RMAN duplication of create controlfile statement in 10.2.0.3.0 version.

Problem is related to parameter MAXLOGHISTORY specified in create controlfile  command.  It's complaining that maximum should  be 65535. whereas in the create controlfile statement its 72704. due to which its failing.

This seems to be a Bug

Bug:
-------

This is unpublished bug 4877360
Abstract: APPSST SRV 10G :ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KCCSCF_1], [9], [65732], [65535]

##########################
## Solution
##########################

A possible workaround is to manually recreate the control file, and change the maxloghistory set to 65535 or lower in the script.

The bug is fixed in 10.2.0.4 and 11.1.0.6 versions.



ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as


##########################
## Error
##########################

ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as

### Full Error

Alter system set log_archive_dest_1="location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as
parameter LOG_ARCHIVE_DEST_4 destination

##########################
#  Error Occurred
##########################

Error occured while trying to modify log_archive_dest_1 parameter to resolve archive gaps issue in our standby database

##########################
## Command Executed
##########################

In Sqlplus,

Alter system set log_archive_dest_1="location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";


##########################
## Issue Description.
##########################

###

Error occurred because, there is already a destination  defined with the same physical location. so we need to define a new value.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=stdby

##########################
## Solution
##########################

Either change the value for LOG_ARCHIVE_DEST_4 to a different path or reset LOG_ARCHIVE_DEST_1 to a different value.

Alter system set log_archive_dest_4="location=/u01/app/oracle/11upgrade/stdby/rman_standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";