Saturday, July 16, 2011

ASMCMD Command Line Tool in Oracle 1og

The ASMCMD tool is a command line utility that allows us to manage ASM instances and the disk structures and files within those instances. With ASMCMD we  can:
List contents of ASM disk groups
Perform searches (like directory listings)
Add or remove directories
Display space availability and utilization

Many of the commands in ASMCMD are UNIX like (i.e. ls, cd) and therefore the ASMCMD is fairly easy to learn how to use.

Starting ASMCMD
To start ASMCMD, set the ORACLE_SID to +ASM(in case of single instance) and then type ASMCMD from the command line as below :

C:\>Set ORACLE_SID=+ASM
C:\>ASMCMD


Or from UNIX:
/opt/oracle>export ORACLE_SID=+ASM
/opt/oracle>ASMCMD


When ASMCMD starts, we  will see the ASMCMD prompt as seen here:
1.ASMCMD>


we  can start ASMCMD with the –p option and it will display the directory level as shown below  :
C:\>ASMCMD -p
ASMCMD [+] >


ASMCMD Commands :    ASMCMD has a basic set of 12 commands in oracle 10.2g .We can see these commands from the ASMCMD prompt if we type  "help"  on the ASMCMD prompt .The logs are as below 
ASMCMD> help
        asmcmd [-p] [command]
The environment variables ORACLE_HOME and ORACLE_SID determine the instance to which the program connects, and ASMCMD establishes a bequeath connection to it, in the same manner as a SQLPLUS / AS SYSDBA.  The user must be a member of the SYSDBA group.


  Specifying the -p option allows the current directory to be displayed  in the command prompt, like so:
        ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >
[command] specifies one of the following commands, along with its  parameters.
        Type "help [command]" to get help on a specific ASMCMD command.
        commands:
        --------
        cd
        du
        find
        help
        ls
        lsct
        lsdg
        mkalias
        mkdir
        pwd
        rm
        rmalias


Now we will check each command on ASMCMD prompt. In my case , i have two diskgroup 'RATCAT' and 'SATMAT'.


1.) cd  –  Change directory : The cd command will move us in and out of ASM directory structures. This command works just like it's UNIX equivelent, and the special designators for previous directory ".." are available for use and use "+" to indicate the root (instead of a forward or backwards slash ( / or \)). Here are some examples:


C:\>asmcmd -p
ASMCMD [+] > cd +RATCAT        
ASMCMD [+RATCAT] > cd test1
ASMCMD [+RATCAT/test1] > cd test22
ASMCMD [+RATCAT/test1/test22] > cd ..
ASMCMD [+RATCAT/test1] > cd ..
ASMCMD [+RATCAT] > cd ..
ASMCMD [+] >


2.) du  –  Disk Used :   Display total space used for files located recursively under [dir],similar to "du -s" under UNIX; default is the current directory.  Two  values are returned, both in units of megabytes.  The first value does not take into account mirroring of the diskgroup while the second does. For instance, if a file occupies 100 MB of space, then it actually  takes up 200 MB of space on a normal redundancy diskgroup and 300 MB of space on a high redundancy diskgroup.


ASMCMD [+] > du
Used_MB        Mirror_used_MB
   2661                2661

3.)  find  –  Find a directory or file:    This command searches the specified directory and all subdirectories below it in the directory tree for the supplied name. name can be a directory name or a filename, and can include wildcard characters. dir may also include wildcards. In the output of the command, directory names are suffixed with the slash character (/) to distinguish them from filenames.


We use the -t flag to find all the files of a particular type (specified as type). For example, we can search for control files by specifying type as CONTROLFILE. Valid values for type are the following:

CONTROLFILE
DATAFILE
ONLINELOG
ARCHIVELOG
TEMPFILE
BACKUPSET
PARAMETERFILE
DATAGUARDCONFIG
FLASHBACK
CHANGETRACKING
DUMPSET
AUTOBACKUP
XTRANSPORT


ASMCMD [+ratcat] > find  +  datafile*
+RATCAT/NOIDA/DATAFILE/


ASMCMD [+ratcat] > find  +  system*
+RATCAT/NOIDA/DATAFILE/SYSTEM.256.755871363
+RATCAT/NOIDA/DATAFILE/SYSTEM.263.756485911


ASMCMD [+] > find  /  system*
+RATCAT/NOIDA/DATAFILE/SYSTEM.256.755871363
+RATCAT/NOIDA/DATAFILE/SYSTEM.263.756485911

4.)  help :  The help command provides the  help for all the ASMCMD commands. we can enter help, followed by the command name, for more detailed information on the command we are interested in.
  
ASMCMD [+] > help du


5.)  ls  :  The ls command is the list file command. This will list the directories and/or files contained within the ASM disk structure. Here are some examples of using the ls command:


ASMCMD [+] > ls
RATCAT/
SATMAT/


ASMCMD [+] > ls -l
State    Type    Rebal  Unbal  Name
MOUNTED  EXTERN  N      N      RATCAT/
MOUNTED  EXTERN  N      N      SATMAT/

6.)  lsct :  The lsct command lists all clients that are using ASM. we can specify a disk group to restrict the output or we can just allow the command to display all the clients. The output of the command displays the databases connected to the ASM instance, the version of the database connected. Also included is the compatible version of the database (set via the COMPATIBLE parameter setting) and the instance name of the instance connected to the ASM instance. Here is an example of the output from the lsct command:

ASMCMD [+] > lsct
DB_Name   Status        Software_Version  Compatible_version  Instance_Name
noida     CONNECTED           10.2.0.1.0          10.2.0.1.0           noida
noida     CONNECTED           10.2.0.1.0          10.2.0.1.0           noida


7.) lsdg : The lsdg command provides information on the disk groups that have been created in an ASM instance. This command returns a great deal of information about the disk group. Of particular interest is the free_MB column, which indicates how much space is available on the disk group. Here is an example of the output of the lsdg command:


ASMCMD [+] > lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      9088     7043                0            7043              0  RATCAT/
MOUNTED  EXTERN  N      N         512   4096  1048576      9088     8362                0            8362              0  SATMAT/


8.) mkalias : Use the mkalias command to create ASM aliases for a given ASM file name. This command is equivalent to the alter diskgroup add alias command from the SQL prompt when connected to an ASM instance. Here is an example of using the mkalias to create an alias for an ASM file:


ASMCMD [+RATCAT/NOIDA/DATAFILE] > mkalias EXAMPLE.270.756485973 exam
ASMCMD [+RATCAT/NOIDA/DATAFILE] > ls
EXAMPLE.265.755871581
EXAMPLE.270.756485973
SYSAUX.257.755871363
SYSAUX.269.756485947
SYSTEM.256.755871363
SYSTEM.263.756485911
UNDOTBS1.258.755871365
UNDOTBS1.271.756485979
USERS.259.755871365
USERS.272.756485983
exam         ---------  alias
                                         Hence we find the aliases name as "exam" 


9.) mkdir :   Mkdir allows you to create ASM directories. we can not create a directory in the root directory (+), and we can create multiple directories at one time by simply listing them one at a time on the mkdir command line separated by spaces. This command is roughly equivalent to the alter diskgroup add directory command. Here are a couple of examples of using the mkdir command:


ASMCMD [+] > mkdir +RATCAT/asm


Here we can crosscheck it by using  "ls"  command


ASMCMD [+] > ls +RATCAT
NOIDA/
aa/
asm/           ----- new directory created
spfileTEST.ora
sysaux.f
test/
test1/


ASMCMD [+] > mkdir +RATCAT/asm1 +RATCAT/asm2 +RATCAT/asm3


Again we can crosscheck it by using "ls" command


ASMCMD [+] > ls +RATCAT/
NOIDA/
aa/
asm/
asm1/            ----->new directory created
asm2/            ----->
asm3/            ----->
spfileTEST.ora
sysaux.f
test/
test1/


10.)  pwd :   Use the pwd command to determine where in the ASM directory tree we  are. Here is an example:


ASMCMD [+RATCAT/NOIDA/DATAFILE] > pwd
+RATCAT/NOIDA/DATAFILE


In case of non-interactive mode, this is more helpful


ASMCMD> pwd
+RATCAT/NOIDA/DATAFILE


11.)  rm  :   The rm command is used to remove ASM directories or files. The rm command will only remove a directory if that directory is empty. This is equivalent to the alter diskgroup drop directory command. Here is an example of removing a directory with the rm command:


ASMCMD> ls
NOIDA/
aa/
asm/
asm1/
asm2/
asm3/
spfileTEST.ora
sysaux.f
test/
test1/


ASMCMD> rm asm


ASMCMD> ls
NOIDA/
aa/
asm1/
asm2/
asm3/
spfileTEST.ora
sysaux.f
test/
test1/


The rm command is also used to remove ASM files and associated aliases. This is equivalent to the alter diskgroup drop file command. 


we can use the "*" wildcard character with the rm command too. If we use the wildcard character, Oracle will warn us to make sure that we truly want to remove the file. Here is an example


ASMCMD> rm asm*
You may delete multiple files and/or directories.
Are you sure? (y/n) y


ASMCMD> ls
NOIDA/
aa/
spfileTEST.ora
sysaux.f
test/
test1/

12.) rmalias  :  The rmalias command is used to remove ASM aliases .


ASMCMD [+RATCAT/NOIDA/DATAFILE] > ls
EXAMPLE.265.755871581
EXAMPLE.270.756485973
SYSAUX.257.755871363
SYSAUX.269.756485947
SYSTEM.256.755871363
SYSTEM.263.756485911
UNDOTBS1.258.755871365
UNDOTBS1.271.756485979
USERS.259.755871365
USERS.272.756485983
exam


ASMCMD [+RATCAT/NOIDA/DATAFILE] > rmalias exam
ASMCMD [+RATCAT/NOIDA/DATAFILE] > ls
EXAMPLE.265.755871581
EXAMPLE.270.756485973
SYSAUX.257.755871363
SYSAUX.269.756485947
SYSTEM.256.755871363
SYSTEM.263.756485911
UNDOTBS1.258.755871365
UNDOTBS1.271.756485979
USERS.259.755871365
USERS.272.756485983


There are further more new features and commands are added in ASM oracle 11g.I will cover it later on.


Enjoy   :-) 

Wednesday, July 13, 2011

Migrate ASM to NON-ASM in oracle 10g

Migrating a database back from ASM storage to non-ASM storage is similar to the original migration from NON-ASM to ASM database. We can  migrate from ASM to non-ASM storage similar to that of the NON-ASM to ASM . Here are the steps to migrate from ASM to NON-ASM.

Step 1 :  Take the RMAN Full Backup of Database (not mandatory).

Step 2 : Start  database with ASM  

Step 3 :  Create  pfile  from  spfile 

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 16:09:45 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;
NAME           OPEN_MODE
---------      ------------------
NOIDA        READ WRITE

SQL> sho parameter spfile;
NAME                          TYPE                      VALUE
------------------          -----------         ------------------------------
spfile                         string              +RATCAT/noida/spfilenoida.ora

SQL> create pfile='c:\qq.ora' from spfile;
File created.

Step 4 :  Edit pfile Parameter 
Edit the pfile to reflect controlfile name in file system location and the specify the location of udump,adump,cdump in file system location . In my case the changes are as follows : 

control_files='D:\oracle\product\10.2.0\oradata\control01.ctl','D:\oracle\product\10.2.0\oradata\control02.ctl'
core_dump_dest='D:\oracle\product\10.2.0/admin/noida/cdump'
background_dump_dest='D:\oracle\product\10.2.0/admin/noida/bdump'
user_dump_dest='D:\oracle\product\10.2.0/admin/noida/udump'

Step 5 :  Startup database at nomount 

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 16:15:41 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='C:\qq.ora' nomount;
ORACLE instance started.
Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              88081088 bytes
Database Buffers          192937984 bytes
Redo Buffers                7139328 bytes

Step 6 : Use RMAN to copy the control file from asm to non-asm 

SQL> host rman target sys/xxxx@noida
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 7 16:23:53 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: noida (not mounted)

RMAN> restore controlfile from  '+RATCAT/noida/controlfile/current.260.755871509' ;

Starting restore at 07-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL
Finished restore at 07-JUL-11

Step 7  :  Mount the Database 

RMAN> alter database mount ; 
database mounted
released channel: ORA_DISK_1

Step 8 :  Use RMAN to copy the database from ASM to NON-ASM.

RMAN> backup  as  copy  database  format  'D:\oracle\product\10.2.0\oradata\%U' ; 

Starting backup at 07-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+RATCAT/noida/datafile/system.256.755871363
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR 

tag=TAG20110707T162707 recid=2 stamp=755886461
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+RATCAT/noida/datafile/sysaux.257.755871363
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU 

tag=TAG20110707T162707 recid=3 stamp=755886480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+RATCAT/noida/datafile/example.265.755871581
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN 

tag=TAG20110707T162707 recid=4 stamp=755886495
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+RATCAT/noida/datafile/undotbs1.258.755871365
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7 

tag=TAG20110707T162707 recid=5 stamp=755886506
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+RATCAT/noida/datafile/users.259.755871365
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA 

tag=TAG20110707T162707 recid=6 stamp=755886507
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
outputfilename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CF_D-NOIDA_ID-1509813972_0AMGRQDB tag=TAG20110707T162707 

recid=7 stamp=755886508
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-JUL-11

Step 9 :  Update the controlfile 
Switch Database to specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file.

RMAN> switch database to copy ; 

datafile 1 switched to datafile copy 
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR"
datafile 2 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7"
datafile 3 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU"
datafile 4 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA"
datafile 5 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN"

RMAN>exit 

SQL> select name,open_mode from v$database;
NAME         OPEN_MODE
---------      -----------------
NOIDA        MOUNTED

SQL> alter database open;
Database altered.

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL

Step 10 :  Migrate redo-log file to file system location. 

SQL> select member from v$logfile;
MEMBER
------------------------------------------------------
+RATCAT/noida/onlinelog/group_3.263.755871523
+SATMAT/noida/onlinelog/group_3.259.755871525
+RATCAT/noida/onlinelog/group_2.262.755871519
+SATMAT/noida/onlinelog/group_2.258.755871521
+RATCAT/noida/onlinelog/group_1.261.755871513
+SATMAT/noida/onlinelog/group_1.257.755871515

6 rows selected.

SQL> select group#,sequence#,members,archived,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          2          2 YES INACTIVE
         2          3          2 YES INACTIVE
         3          4          2 NO  CURRENT

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add  logfile group 1 'D:\oracle\product\10.2.0\oradata\redo01.redo' size  50M;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile group 2  'D:\oracle\product\10.2.0\oradata\redo02.redo'  size  50M;
Database altered.

SQL> select member from v$logfile;
MEMBER
--------------------
+SATMAT/noida/onlinelog/group_3.259.755871525
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

SQL> select group#,sequence#,members,archived,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          5          1 NO  CURRENT
         2          0          1 YES UNUSED
         3          4          2 YES ACTIVE

SQL> alter system switch logfile ;
System altered.

SQL> alter database drop logfile group 3; 
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance noida (thread 1)
ORA-00312: online log 3 thread 1: '+RATCAT/noida/onlinelog/group_3.263.755871523'
ORA-00312: online log 3 thread 1: '+SATMAT/noida/onlinelog/group_3.259.755871525'

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL>alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3  'D:\oracle\product\10.2.0\oradata\redo03.redo'  size  50M;
Database altered.

SQL> select member from v$logfile;
MEMBER
--------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO03.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

Step 11 :  Recreate the tempfile

SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME                                                            TABLESPACE_NAME
-------------------------------------                               -------------------
+RATCAT/noida/tempfile/temp.264.755871559          TEMP

SQL> alter tablespace temp add tempfile 'D:\oracle\product\10.2.0\oradata\temp01.dbf' size 200m;
Tablespace altered.

SQL> alter tablespace temp drop tempfile  '+RATCAT/noida/tempfile/temp.264.755871559';
Tablespace altered.

SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME                                                                        TABLESPACE_NAME
--------------------                                                               ------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP01.DBF              TEMP

Step 12 :  Recreate spfile 

SQL> sho parameter spfile
NAME                         TYPE                      VALUE
--------------             -----------     ------------------------------
spfile                          string

SQL> create spfile from pfile='C:\qq.ora';
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              88081088 bytes
Database Buffers          192937984 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

Step 13 :  Check the database files 

SQL> sho parameter spfile
NAME          TYPE                                                       VALUE
---------      ---------     --------------------------------------------------------------------------------
spfile          string           D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILENOIDA.ORA

SQL> select file_name from dba_data_files;

FILE_NAME
----------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN

SQL> select name from v$controlfile;
NAME
-------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL

SQL> select member from v$logfile;
MEMBER
-----------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO03.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

Enjoy   :)  :)

Wednesday, July 6, 2011

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   :-) 


Saturday, July 2, 2011

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.