Multiplexing of Controlfiles in Oracle ASM

>> Some Points regarding controlfile importance.


>> What is a controlfile & What it contains ?

 Controlfile is a small binary file that records the physical structure of the database.

>> When Oracle database is started this is the file which oracle reads to get the names of datafiles and their locations.

Which means :

1. The database name and locations of datafiles and redolog files.

2. Timestamp of database creation.

3. Curent logsequence Number.

4. Checkpoint Information.

5. Backup set Details.

6. SCN number etc....

Note: Server Process is responsible for Continuously updating during these operations.


>> Why to Multiplex controlfiles ?

> In database we should have at least two control files, each Should stored on a different physical
  disk to safeguard the database.


>> Scenarios When to Create 'NEW' Control File.


1. All control files for the database have been permanently damaged and you do not have a control file backup.

2. You want to change one of the permanent database parameter settings originally specified in the
   CREATE DATABASE statement. These settings include the database's name and the following parameters:
   MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.


       HOW TO MULTIPLEX CONTROLFILES IN ASM ENVIRONMENT


SQL> select name from v$controlfile;

NAME
-------------------------------------------------
+DATA/kkprod/controlfile/current.262.962995555

>> Set new disk-group for new controlfile.

SQL> alter system set control_files='+DATA/kkprod/controlfile/current.262.962995555', '+DATA1' scope=spfile;

System altered.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/kkprod/controlfile/current.262.962995555

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2256784 bytes
Variable Size             536871024 bytes
Database Buffers         1543503872 bytes
Redo Buffers                5148672 bytes

>> Disk group 'DATA1' is added.

SQL> show parameter control_files;

NAME             TYPE        VALUE
-----------------------------------------------------------------------------------
control_files   string      +DATA/kkprod/controlfile/current.262.962995555, +DATA1

RMAN> restore controlfile from '+DATA/kkprod/controlfile/current.262.962995555';

Starting restore at 28-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/kkprod/controlfile/current.262.962995555
output file name=+DATA1/kkprod/controlfile/current.256.963941387

Finished restore at 28-DEC-17

C:\Windows\system32> sqlplus / as sysdba

Here you need to add both the controlfiles  from the above RMAN restore output

SQL> alter system set control_files='+DATA/kkprod/controlfile/current.262.962995555','+DATA1/kkprod/controlfile/current.256.963941387' scope=spfile;

System altered.

SQL> shut immediate

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size                  2256784 bytes
Variable Size             536871024 bytes
Database Buffers         1543503872 bytes
Redo Buffers                5148672 bytes
Database mounted.
Database opened.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME       STATUS
-------------------------------------------------
kkprod                              OPEN


SQL> select name from v$controlfile;

NAME
------------------------------------------------
+DATA/kkprod/controlfile/current.262.962995555
+DATA1/kkprod/controlfile/current.256.963941387


SQL> show parameter control_files

NAME                 TYPE        VALUE
--------------------------------------------------------------------------------
control_files       string      +DATA/kkprod/controlfile/current.262.962995555,
                                          +DATA1/kkprod/controlfile/current.256.963941387




Happy Learning...

How to Start and Stop ASM and RDBMS Instance in Oracle

>> First we need to start ASm instance then RDBMS instance.

>> For shutting down First Shut RDBMS and then ASM instance.

• Starting Sequence :-

Why ASM first because when we start ASM-instance oracle read all the parameters in spfile like
instance_type,Diskgroups names,Background Processes of ASM and then mount all the diskgroups.
After sucessfully mounted then you can start the RDBMS instance. the main reason is your all Files
are store in the ASM-Diskgroup.

C:\Windows\system32> set oracle_sid= +asm
C:\Windows\system32> sqlplus / as sysasm
Connected to an idle instance.
SQL> startup

ASM instance started
Total System Global Area    283930624 bytes
Fixed Size                             2254504 bytes
Variable Size                         256510296 bytes
ASM Cache                           25165824 bytes
ASM diskgroups mounted


SQL> select instance_name,status from v$instance;

INSTANCE_NAME         STATUS
---------------------------------------------------
+asm                               STARTED

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED

• Now Connect to RDBMS Instance :-

C:\app\Moin> set oracle_sid=kkprod
C:\app\Moin> sqlplus / as sysdba
Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area     2087780352 bytes
Fixed Size                             2256784 bytes
Variable Size                         536871024 bytes
Database Buffers                   1543503872 bytes
Redo Buffers                          5148672 bytes
Database mounted.
Database opened.

SQL> select name from v$datafile;

NAME
----------------------------------------------
+DATA/kkprod/datafile/system.266.962995733
+DATA/kkprod/datafile/sysaux.265.962995779
+DATA/kkprod/datafile/undotbs1.264.962995823
+DATA/kkprod/datafile/users.267.962995627

>> Database is UP now users can access data from these diskgroups.

• Stopping Sequence of ASM :-
• Connect to RDMS instance....

C:\app\Moin> set oracle_sid=kkprod
C:\app\Moin> sqlplus / as sysdba

SQL> select instance_name,status from v$instance;

INSTANCE_NAME            STATUS
------------------------------------------------
kkprod                                   OPEN


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


Now connect to ASM Instance :

SQL> select instance_name,status from v$instance;

INSTANCE_NAME             STATUS
------------------------------------------------
+asm                                    STARTED

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED

SQL> shut immediate
ASM diskgroups dismounted
ASM instance shutdown


Done....


If We Shut ASM Instance First, you will face this error :


SQL> shut immediate

ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 4116)

>> ASM will not shutdown with connected client. you can find which client is connected from OS level using
   ASMCMD and V$ASM_CLIENT from sqlplus.


ASMCMD> lsof

DB_Name  Instance_Name  Path
kkprod   kkprod         +data/kkprod/controlfile/current.262.962995555
kkprod   kkprod         +data/kkprod/datafile/babts.268.963661839
kkprod   kkprod         +data/kkprod/datafile/sysaux.265.962995779
kkprod   kkprod         +data/kkprod/datafile/system.266.962995733
kkprod   kkprod         +data/kkprod/datafile/undotbs1.264.962995823
kkprod   kkprod         +data/kkprod/datafile/users.267.962995627
kkprod   kkprod         +data/kkprod/datafile/users.269.963077837
kkprod   kkprod         +data/kkprod/onlinelog/group_1.261.963052723
kkprod   kkprod         +data/kkprod/onlinelog/group_2.260.963052729
kkprod   kkprod         +data/kkprod/onlinelog/group_3.259.963052735
kkprod   kkprod         +data/kkprod/tempfile/temp.263.962996209



SQL> select group_number,instance_name,db_name,status,software_version,compatible_version from v$asm_client;

GROUP_NUMBER  INSTANCE_NAME   DB_NAME    STATUS       SOFTWARE_VERSION    COMPATIBLE_VERSION
------------------------------------------------------------------------------------------
       2        kkprod        kkprod    CONNECTED        11.2.0.3.0         11.2.0.0.0



>> You can see in DB_NAME column 'kkprod' you need to shutdown this client first, nothing but database.


SQL> select name from v$database;

NAME
------------------------------
KKPROD


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


>> Connect to ASM instance.

C:\Windows\system32>sqlplus / as sysasm
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option


SQL> shut immediate
ASM diskgroups dismounted
ASM instance shutdown


Hope This Clear.


Happy Learning....

Moving Datafile From One Diskgroup To Another Diskgroup

>> Check your datafile location with their diskgroup names.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_ FILE_NAME
----------- ---------------------------------------------
USERS       +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1    +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX      +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM      +DATA/kkprod/datafile/system.266.962995733
USERS       +DATA/kkprod/datafile/users.269.963077837
BABTS       +DATA1/kkprod/datafile/babts.268.963657501

>> I want to move babts datafile from +DATA1 to +DATA diskgroup.

SQL> select name from v$asm_diskgroup;

NAME               
---------------
DATA1                                       
DATA 

>> Check the status of babts datafile which you are moving.
                                     
SQL> select name,status from v$datafile where file#='6'

NAME                                                                 STATUS
----------------------------------------------------------------- -------
+DATA1/kkprod/datafile/babts.268.963657501    ONLINE

>> Offline the datafile by alter command.

SQL> alter database datafile '+DATA1/kkprod/datafile/babts.268.963657501' offline;

Database altered.

>> When you offline the datafile oracle shows recover status.

SQL> select name,status from v$datafile where file#='6';

NAME                                          STATUS
--------------------------------------------- -------
+DATA1/kkprod/datafile/babts.268.963657501    RECOVER

>> Connect RMAN and copy datafile to another diskgroup.

RMAN> connect target /

connected to target database: KKPROD (DBID=1757363720)

RMAN> copy datafile '+DATA1/kkprod/datafile/babts.268.963657501' to '+DATA';

Starting backup at 25-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA1/kkprod/datafile/babts.268.963657501

output file name= +DATA/kkprod/datafile/babts.268.963661839 tag=TAG20171225T115039 RECID=11 STAMP=963661882

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 25-DEC-17
Starting Control File and SPFILE Autobackup at 25-DEC-17
piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-17

>> Still file location has not changed.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE FILE_NAME
---------- ---------------------------------------------
USERS      +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1   +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX     +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM     +DATA/kkprod/datafile/system.266.962995733
USERS      +DATA/kkprod/datafile/users.269.963077837
BABTS      +DATA1/kkprod/datafile/babts.268.963657501

6 rows selected.

>>> Copy datafile LOCATION from above COPY command outputfile name (+DATA/kkprod/datafile/babts.268.963661839) for renaming.


SQL> ALTER DATABASE RENAME FILE '+DATA1/kkprod/datafile/babts.268.963657501' to '+DATA/kkprod/datafile/babts.268.963661839';

Database altered.

 >> Now Diskgroup location has changed from +DATA1 TO +DATA.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE FILE_NAME
---------- ---------------------------------------------
USERS      +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1   +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX     +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM     +DATA/kkprod/datafile/system.266.962995733
USERS      +DATA/kkprod/datafile/users.269.963077837
BABTS      +DATA/kkprod/datafile/babts.268.963661839

6 rows selected.


>> SWITCH command switches the datafile to the most recent copy of the datafile. You can specify the datafile by name or number.

RMAN> switch datafile '+DATA/kkprod/datafile/babts.268.963661839' to copy;

using target database control file instead of recovery catalog
datafile 6 switched to datafile copy "+DATA/kkprod/datafile/babts.268.963661839"

>> Recover the datafile.

RMAN> recover datafile '+DATA/kkprod/datafile/babts.268.963661839';

Starting recover at 25-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=200 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-DEC-17

>> Check the status of datafile.

SQL> select name,status from v$datafile where file#='6';

NAME                                        STATUS
------------------------------------------- -------
+DATA/kkprod/datafile/babts.268.963661839   OFFLINE

>> Make this datafile online.

SQL> alter database datafile '+DATA/kkprod/datafile/babts.268.963661839' online;

Database altered.

SQL> select name,status from v$datafile where file#='6';

NAME                                        STATUS
------------------------------------------- -------
+DATA/kkprod/datafile/babts.268.963661839   ONLINE


>> For testing purpose You can create table in this datafile tablespace.


Enjoy Reading....

How to Install Oracle ASM on Windows Environment

      

 Hi Guy's 

I will show you how to create logical partitions for Installing Oracle Grid Infrastructure in Windows Environment.

•  If you want to install Oracle-ASM you need to create Logical Partitions first.

Navigation

Disk Management à You can see here empty partition in which you planned to create logical disks like [New-volume]

Right Click New Volume à Shrink Volume à (Size)

Now right click on that partition à New Simple Volume à Don’t Assign a Drive letter à Do not Format this volume  à Finish

• Repeat this step for every partition you want to create. In this below screen shot I created 2 partitions 266 & 263 GB which I indicated.

  

   

• After creating logical partitions go to ASMTOOL utility in grid location to add and stamp the disks which will recognize and managed by ORACLE-ASM.

• I have added ORCLDISKDATA0 & ORCLDISKDATA1 by using asmtool -add command.

 

•   After adding disk, query by using List command

• You can see below for 4 & 5 partitions which are mapped to DATA0 & DATA1 disk.

• Now Install grid infrastructure and define Diskgroups name.

Enjoy Reading...

How to Convert Non ASM File System to ASM File System in Oracle Windows.

Oracle DB version     = 11.2.0.3

Operating system       = Windows 2008 Server

Datafile Location        = E:\KKPROD to +DATA diskgroup


>> Note :- This is my Test Environment.

>> Check the existing location of all the files like control files,datafiles,spfiles,archivelog files.

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

E:\KKPROD\CONTROL01.CTL

E:\KKPROD\CONTROL02.CTL

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      C:\APP\MOIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEKKPROD.ORA

>> Set the controlfile location to your diskgroup where you planned to migrate control file.

SQL> alter system set control_files='+DATA' scope=spfile;

>> Set the Datafiles location to your diskgroup where you planned to migrate all your datafiles.

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

>> Shutdown immediate and open database in nomount state.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 2087780352 bytes

Fixed Size                  2256784 bytes

Variable Size             536871024 bytes

Database Buffers         1543503872 bytes

Redo Buffers                5148672 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

>> Connect from RMAN and restore controlfile, oracle will restore the controlfile in new   diskgroup location as we SET above parameter control_files='+DATA'

C:\Windows\system32>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 17 18:45:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: KKPROD (not mounted)

>> Here controlfile New location is updated in new diskgroup

RMAN> restore controlfile from 'E:\KKPROD\CONTROL01.CTL';

Starting restore at 17-DEC-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/kkprod/controlfile/current.262.962995555

Finished restore at 17-DEC-17

 >> We can mount the database.

RMAN> alter database mount;

              database mounted

>> The 'BACKUP AS COPY' command creates image copies and removes the need to extract them from a backup set.  This command works on same principles of (CP) command in linux. these files are same as datafiles which you see below output. Image copies are used to quickly switch to datafile without the need of restore. this is the reason our next step will be to 'Swtich Database to Copy'.

RMAN> backup as copy database format '+DATA';

Starting backup at 17-DEC-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=5 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=E:\KKPROD\USERS01.DBF

output file name=+DATA/kkprod/datafile/users.267.962995627 tag=TAG20171217T184707 RECID=1 STAMP=962995725

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=E:\KKPROD\SYSTEM01.DBF

output file name=+DATA/kkprod/datafile/system.266.962995733 tag=TAG20171217T184707 RECID=2 STAMP=962995772

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=E:\KKPROD\SYSAUX01.DBF

output file name=+DATA/kkprod/datafile/sysaux.265.962995779 tag=TAG20171217T184707 RECID=3 STAMP=962995815

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=E:\KKPROD\UNDOTBS01.DBF

output file name=+DATA/kkprod/datafile/undotbs1.264.962995823 tag=TAG20171217T184707 RECID=4 STAMP=962995862

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Finished backup at 17-DEC-17

Starting Control File and SPFILE Autobackup at 17-DEC-17

piece handle=E:\FRA\KKPROD\AUTOBACKUP\2017_12_17\O1_MF_S_962995260_F3F4MHM3_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 17-DEC-17

>> Switch command updates datafiles to the latest image copies.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/kkprod/datafile/system.266.962995733"

datafile 2 switched to datafile copy "+DATA/kkprod/datafile/sysaux.265.962995779"

datafile 3 switched to datafile copy "+DATA/kkprod/datafile/undotbs1.264.962995823"

datafile 4 switched to datafile copy "+DATA/kkprod/datafile/users.267.962995627"

>> Now open the database and check whether all the datafiles are switched to +DATA diskgroup.

RMAN> alter database open;

database opened

SQL> select name from v$datafile;

NAME

------------------------------------------------------------------------------

+DATA/kkprod/datafile/system.266.962995733

+DATA/kkprod/datafile/sysaux.265.962995779

+DATA/kkprod/datafile/undotbs1.264.962995823

+DATA/kkprod/datafile/users.267.962995627

>> Done, All the files are switched to +DATA diskgroup.

>> Drop the old location of tempfile.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

E:\KKPROD\TEMP01.DBF

+DATA/kkprod/tempfile/temp.263.962996209

SQL> alter tablespace temp add tempfile size 500M;

Tablespace altered.

SQL> alter database tempfile 'E:\KKPROD\TEMP01.DBF' drop including datafiles;

Database altered.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

+DATA/kkprod/tempfile/temp.263.962996209

>> Drop all the logfiles which are pointing to old location and add the new redologs.

>> All our database is migrated to ASM and now move archivelogs to ASM by clicking this link.

http://pages.di.unipi.it/ghelli/didattica/bdldoc/B19306_01/backup.102/b14191/rcmasm002.htm#BABJHIHA

Enjoy Reading...

ORA-01113 ORA-01110

Hi Guy's,

Ora-01113 occurs when Recovery needed for any file.

• There can be many reasons for these errors as per the oracle.

1. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP.
2. AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP.
3. TRYING TO ONLINE A DATAFILE OR TABLESPACE
4. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY.


In my case i am trying to make datafile online.
======================================

SQL> alter database datafile '+DATA1/kkprod/datafile/tsnew.256.963078241' OFFLINE;

Database altered.


SQL> select name,status from v$datafile where file#='6'

NAME                                                                     STATUS
-------------------------------------------------------------------------------
+DATA1/kkprod/datafile/tsnew.256.963078241    RECOVER


SQL> alter database datafile '+DATA1/kkprod/datafile/tsnew.256.963078241' ONLINE;
alter database datafile '+DATA1/kkprod/datafile/tsnew.256.963078241' ONLINE
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '+DATA1/kkprod/datafile/tsnew.256.963078241'


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

SQL> startup mount;

ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2256784 bytes
Variable Size             536871024 bytes
Database Buffers         1543503872 bytes
Redo Buffers                5148672 bytes
Database mounted.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 2642889656 generated at 12/19/2017 09:29:36 needed for thread1
ORA-00289: suggestion : E:\FRA\KKPROD\ARCHIVELOG\2017_12_19\O1_MF_1_32_%U_.ARC
ORA-00280: change 2642889656 for thread 1 is in sequence #32

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

• Apply the log here.

+DATA/kkprod/onlinelog/group_2.260.963052729

log applied.

Media recovery complete.

•  Now open the database.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.


Enjoy Reading...

MONITORING RMAN BACKUPS

SQL> SELECT sid, serial#, sofar, totalwork, opname, round(sofar/totalwork*100,2) A
          FROM v$session_longops  WHERE opname LIKE 'RMAN%'
         AND opname NOT LIKE '%aggregate%' AND totalwork != 0
         AND sofar <> totalwork;

       SID    SERIAL#      SOFAR  TOTALWORK      OPNAME               PCT_COMPLETE
---------- ---------- ---------- ---------- --------------- ------------------------------------------------
       135          7          507382       6230432        RMAN: full data         8.14        file backup



Happy Learning...

How to Check Locked Users Accounts in Oracle

SQL> select username,account_status,to_char (lock_date,'dd-mon-yyy hh24:mi:ss') from dba_users where lock_date is not null

USERNAME      ACCOUNT_STATUS   TO_CHAR(LOCK_DATE,'
------------- ---------------- -------------------------------------------------------
RNSIJO                 LOCKED(TIMED)          25-feb-020 07:10:11
ARCHIVEDB       LOCKED(TIMED)          10-dec-018 13:17:42
ALAA                   LOCKED(TIMED)           19-feb-020 10:44:41
OUTLN            EXPIRED & LOCKED         03-nov-011 06:38:16
XS$NULL        EXPIRED & LOCKED         03-nov-011 06:01:56

SQL> select username,account_status from dba_users where username like 'GH%';

USERNAME     ACCOUNT_STATUS
------------ --------------------------------
GHADA             LOCKED(TIMED)




SQL> SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';

USERNAME            ACCOUNT_STATUS              CREATED     LOCK_DATE         EXPIRY_DA
------------------------------------------------------------------------------------------------------------------------
OUTLN                      EXPIRED & LOCKED         03-NOV-11      03-NOV-11            03-NOV-11
DIP                             EXPIRED                               03-NOV-11      03-NOV-11
ORACLE_OCM        EXPIRED                               03-NOV-11      03-NOV-11
APPQOSSYS            EXPIRED                                03-NOV-11      03-NOV-11
WMSYS                    EXPIRED                                03-NOV-11      03-NOV-11
XS$NULL                EXPIRED & LOCKED            03-NOV-11      03-NOV-11            03-NOV-11
EXFSYS                   EXPIRED                                 03-NOV-11      03-NOV-11
CTXSYS                  EXPIRED                                  03-NOV-11      03-NOV-11
XDB                         EXPIRED                                  03-NOV-11      03-NOV-11
ANONYMOUS       EXPIRED                                   03-NOV-11      03-NOV-11
ORDSYS                 EXPIRED                                   03-NOV-11      03-NOV-11
ORDDATA              EXPIRED                                   03-NOV-11      03-NOV-11
ORDPLUGINS        EXPIRED                                   03-NOV-11      03-NOV-11
MDSYS                   EXPIRED                                    03-NOV-11      03-NOV-11
OLAPSYS               EXPIRED                                    03-NOV-11      03-NOV-11
OWBSYS_AUDIT EXPIRED                                     03-NOV-11      03-NOV-11
MDDATA                EXPIRED                                    03-NOV-11      03-NOV-11
FLOWS_FILES       EXPIRED                                    03-NOV-11      03-NOV-11
APEX_030200        EXPIRED                                     03-NOV-11      03-NOV-11
OWBSYS               EXPIRED                                      03-NOV-         03-NOV-11
LAB                        LOCKED(TIMED)                       11-OCT-14      07-AUG-16
DIET                       LOCKED(TIMED)                       11-OCT-14      02-OCT-16
GHADA                 LOCKED(TIMED)                       11-OCT-14       23-AUG-16
FINSAMI               LOCKED(TIMED)                       22-NOV-15      02-JAN-17



Happy Learning .....