ORA-28000 ,ORA-28001, ORA-28002 : The Account locked ,expired or password will expire within xx days

ORA-28000 specifies the user's account is locked .The common reason of occurring this error is when it gets locked internally based on the profile resource limit. This error may also occur when the user has entered wrong password consequently for maximun no. of times as specified by the user's profile parameter i.e, Failed_Login_Attempts. To solve this error either wait for the Password_lock_time or the DBA can fire the below command to solve this issue :

SQL> alter user abc identified by password account unlock ;

ORA-28001 specifies the user account is expired . This error commonly occurs when the expiry time is reached . By default the expiry date for a newly created user is of 180 days . Hence to solve this issue, increase the limit of the password expiry date. For this check the profile assigned to the user and then limit the password expiry date. To solve this issue increase the password expiry periods .

SQL>select username,profile from dba_users where username='TEST' ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

ORA-28002 specifies that  the user's account is about to about to expire and the password needs to be changed. This can be solved either by changing the password or by changing the user profile. If we do want this behavior, we need to do the following:

1.) Logon to the product database as the SYSTEM user (not the application administration user).

2.) Find the profile that has the PASSWORD_LIFE_TIME set to anything but UNLIMITED.

SQL> select * from dba_profiles where RESOURCE_NAME LIKE  'PASSWORD_LIFE_TIME';

If the user name say "test" and password is also "test" then check the profile assign to the user as

SQL>select username,profile from dba_users where username='TEST' ;

Once ,we have profile, we alter the profile and password .

3.)  Alter the profiles with the following statement:

SQL> alter user test identified by test  account unlock ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

where profile_name is the name of the profile where wer need to set the password life to UNLIMITED.

This should remove the password life message.


Enjoy   :-) 

Configuring Oracle ASM On Windows

Automatic Storage Management(ASM) is oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems or files which can be made to look like disks as long as the device is raw. ASM uses its own database instance to manage the disks, it has its own processes and pfile or spfile, it uses ASM disk groups to manage disks as one logical unit. The benefits of ASM are

• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring (fine and coarse gain)
• ASM and non-ASM oracle files can coexist
• ASM is free!!!!!!!!!!!!!

Preparing Diskgroups for Automatic Storage Management

Step 1 : Create Disk Partitions :
For this sample installation I use only one Disk and create logical partitions on it. For production implementation create a primary partition on each disk.

To create partitions,

C:\> Diskpart

DISKPART> list disk

DISKPART> Select disk 0
To create a logical partition, the size is in MB.

DISKPART> Create partition logical size=1024
To create a primary partition , the size is in MB

DISKPART> Create partition primary size=1024

To view the partitions created

DISKPART> List partition

Repeat for other disks

The created volumes must be unformatted, that is, the Fs column must be blank in the following command. In the following output NTFS indicates NT File system and hence cannot be used as ASM disk. 

Stamp ASM Disks 

To use ASM, the disks must be stamped with a header. Use ASMTOOL to stamp each partition with an ASM label so that oracle can recognize these partitions as candidate disk. Navigate the oracle media to find the asmtool folder. Click the asmtoolg.exe.

Select the Add or change label dialog and press Next button.

Select the Candidate Disk and assign a asmdisk group name. Click Next.

Click Next.

Click Finish.

Repeat the process for each disk group.

Now, we have done installation and ASM  instance Creation.

Step 1:   Invoke the Oracle Universal Installer. In the Welcome Screen select Advanced Installation and click Next button.

 

Step 2: In the Select Installation Type, select the appropriate choice. In our case we select Standard Edition.

Step 3 : Specify the ASM Home details.

Click Next.

 

Step 4: Check for any errors and correct it if any. Click Next.

 

Step 5 : Choose the Configure Automatic Storage Management option and specify the SYS password. Click Next.

 

Step 6: In the next screen do the following,

1.     Specify the Disk Group name.

2.     Choose the redundancy type.

3.     Select the candidate disks you want to assign for this disk group.

Note: We must have stamped the disks before to make Oracle recognize the disks. If we have not stamped the disks already, click the stamp disks button to invoke theasmtool utility. we can then stamp the disks as above in the Stamp disks section .

Click Next

 

Step 7: Click Install.

Click the Exit button. The ASM installation is completed.
Note:We can use the DBCA to create addition disk groups.

Here we have discussed the ASM installation.Now we will see the Oracle Installation

Step 1 : Invoke the OUI. Select the Advanced installation option and then click Next.

Step 2 : Select the installation type for eg., Enterprise, standard edition etc.

Step 3: Specify the Oracle Home details. Choose a different path from ASM home.

Step 4: In the next page select the option to create the database.

Step 5: Specify a Database Name. Choose the character set.

 

Step 6: Select Database control for Database Management.

Step 7: In the select database storage option page, select ASM.

 

Step 8: In the next page click the option that best matches our requirement.

 

Step 9: Select the ASM Disk Group.

If the space available is not enough it will show the following screen.

Select the disks so that the required space is available. Click Next

Step 10: Click Install.

Click Exit. The oracle RDBMS is installed.

Reference : http://oracledba-vinod.blogspot.com/2009/12/install-oracle-10g-on-windows-xp-using.html


Enjoy       :-) 

Identify and Remove the duplicate rows in a Table

In case if we want to identify duplicates rows of a table and want to remove them from a table.  Below are the command which will  identify and remove the duplicare rows from a Table.

1.) Identify duplicates

SQL> select count(*) from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2
MINUS
select max(rowid) from ADDRESS2
GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
251

Here 251 duplicates exist – these can be deleted with command below :

2.) Removing the Rows :

SQL> delete from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2

MINUS

select max(rowid) from ADDRESS2

GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

Enjoy  :-)