How to Turn off / Disable User Account Expiration

Oracle User Account Expired. How to disable or turn off EXPIRE
===========================================


Issue:

One of the critical User account got expired, expire to be turned off to avoid connection issue on application connectivity

Issue Details:


1. Found User account is about to expire and it is on GRACE Period

SQL> select username,account_status,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE CREATED PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 18-OCT-11 09-APR-11 DEFAULT


2. Confirmed with profile settings that 180 expiration


SQL> select * from dba_profiles where profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7



3. To disable/Turn off the expiration, we need to set profile to UNLIMITED:


SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

4. While Checking the status, it still shows Expiry date as shown earlier, so, what could be cause or reason???


SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


5. Profile shows UNLIMITED, but why user account did not get effect???


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 UNLIMITED

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

16 rows selected.


6. Tried resetting the profile again to user, but no luck... :-(


SQL> alter user TEST profile default;

User altered.

SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


7. Tried setting account unlock...but still no luck....

SQL> alter user TEST account unlock;

User altered.

SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST';

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST EXPIRED(GRACE) 09-APR-11 18-OCT-11 DEFAULT


SOLUTION:


8. Then found that, when user is already in GRACE Period, even altering profile, will NOT take effect until the password is changed

Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value

Be careful if you plan to set the PASSWORD_LIFE_TIME parameter of CREATE PROFILE or ALTER PROFILE to a low value (for example, 1 day). If the user who is assigned this profile is concurrently logged in when you make this modification, then Oracle Database sets the user's account status from OPEN to EXPIRED(GRACE)with the warning error ORA-28002: the password will expire within n days. You may not be notified of this change because the user can still connect to the Oracle database. You can find the concurrently logged in users by querying the USERNAME column of the V$SESSION dynamic performance view.

Note the following:

If the user is not logged in when you set PASSWORD_LIFE_TIME to a low value, then the user's account status does not change when the user does log in.

You can set the PASSWORD_LIFE_TIME parameter to UNLIMITED, but this only affects accounts that have not entered their grace period. If the user has already entered the grace period, then he or she must change the password.


Ref: http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#i1007339



SQL> alter user TEST identified by test;

User altered.


SQL> select username,account_status,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='TEST'

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
------------------------------ -------------------------------- --------------- --------------- --------------- ------------------------------
TEST OPEN 09-APR-11 DEFAULT

Alert Log : Diskgroup Mounted & Dismounted

I could see the following messages in Alertlog , was curious to know the reason behind it…

Mon Oct 10 11:49:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

Mon Oct 10 11:50:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

Mon Oct 10 11:51:00 2011

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

SUCCESS: diskgroup ARCH_QA was mounted

SUCCESS: diskgroup ARCH_QA was dismounted

This is expected behavior. The messages you see are not errors (that is why they are prefixed with "SUCCESS"). The database instance is creating and then closing archive logs one at a time in the ARCH_QA disk group. Whenever a database closes its last file in a disk group, it dismounts the disk group. Whenever a database accesses a disk group when it does not have any other files open in the disk group, it mounts the disk group.

If you do not want to see the log messages frequently, you can put a mirrored control-file, or mirrored online redo, or dummy online tablespace data file on the diskgroup. This message will not be output frequently in this case. Because CKPT keeps opening a control-file, LGWR keeps opening an online redo, DBW keeps opening an online tablespace data file. So, the diskgroup is not dismounted until the database shutdown.

TailPiece: we get this messages only for respective ASM disk groups for archive logs are written and in a high number

Ref: Why FRA Diskgroup Gets Mounted/Dismounted ? [ID 603204.1]

Cross Platform Database : Best Approach

How to Move Huge Database from Sun to Linux : Best Approach

You wanna transfer your huge database cross platform sun to linux ??

Lets Look at the options:

1. Best Option : RMAN

RMAN Cross-Platform Transportable Databases and Tablespaces

2. Other Options:

Export/Import
Datapump : Expdp/Impdp
Transport tablespace

Please share your ideas,best methods/practice ....

Why there is a diff btwen DBA_SEGMENTS & DATAPUMP of particular schema size

Why there is a difference between DBA_SEGMENTS & DataPUMP of size of particular schema
======================================================================================

1. SELECT SUM(bytes)/1024/1024/1024 AS size_gig FROM DBA_SEGMENTS WHERE OWNER= 'XXX';

2. $ expdp estimate_only=y schemas=xxx

In Common, data pump export would show less value than the size computed from dba_segments/extents


ESTIMATE_ONLY - Use this parameter to estimate the required export file size without starting an actual export job.
ESTIMATE - The ESTIMATE parameter will tell you how much space your new export job is going to consume.
Estimate_only shows the export dump file size not a schema size.

Also, DBA_SEGMENTS contains INDEX segments too,

So, The following query may give you close result

select sum(bytes)/1024/1024/1024 as size_gig, segment_type
from dba_segments
where owner='ENDUR01'
group by segment_type;

How to get the Schema/User size

In SQLPLUS run any of the following scripts to find the total size occupied by a particular schema.


1.

SQL> SELECT sum(bytes)/1024/1024 FROM user_segments;


The output above query would be similar as follows:


SUM(BYTES)/1024/1024
——————–
2552.75


-- OR --

2.

SQL> SELECT tablespace_name, Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper(&User_Name)
GROUP BY owner, rollup(tablespace_name)


-- OR --

3. To be more precise on particular table which excluding index,lob segments etc

SQL> SELECT sum(bytes)/1024/1024/1024 as size_gig, segment_type
FROM dba_segments
WHERE owner='XXX'
GROUP BY segment_type;

How to kill all ORACLE Process in one command

At OS prompt, Execute the following command to kill all ORACLE process

$ kill -9 `ps -ef |grep PROD |awk '{print $2}'`

Query to find All Background_processes in the Database

Background Processes

Background processes support various types of Oracle processing

Each version of Oracle has a set of associated background processes. Each background process has a descriptor in the fixed area of the SGA. Background process descriptors are of type ksbdp. The descriptors can be seen in the level 2 GLOBAL_AREA dump for example:

ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';

Every background process descriptor has an internal (structure) name e.g. ktmprc_ and an external (display) name e.g. SMON. A list of background process descriptors together with the external and internal names can be obtained using the following query:

SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;

how to change expired status of user accounts.txt

SQL> select username,account_status from dba_users where username='DIP';

USERNAME ACCOUNT_STATUS
----------------------- ---------------
SCOTT EXPIRED & LOCKED


SQL> alter user SCOTT account unlock;

User altered.

SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED

SQL> select password from dba_users where username='SCOTT';

PASSWORD
------------------------------
CE4A36B8E06CA59C

SQL> alter user scott identified by values 'CE4A36B8E06CA59C';

User altered.

SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN

Difference Between AFN & RFN

RELATIVE FILE NO.

Relative file no is the file no. generated which belongs to only one tablespace.
Every tablespace has its own datafiles will be referred by Relative file no.
So the relative file no. can be repeated in each tablespaces.
Eg; TAblespace users(TS#1) has 2 datafiles then Relative file no. REL file no. 1 and 2 for tablespace #1.
Tablespace userdata(TS#2) has 2 datafiles then Relative file no. will be 1 and 2 for tablespace # 2.

A tablespace can have only 1022 max datafiles .. so Relative file no will be 1022 for each tablespace max.

ABSOLUTE FILE NO.

Whereas Absolute file no is Datafile No. generated uniquely at database level.

How to disable/enable schedule job and keep run_count?

You enable one or more jobs by using the ENABLE procedure or Enterprise Manager.

The effect of using this procedure is that the job will now be picked up by the
job coordinator for processing.
Jobs are created disabled by default, so you need to enable them before they
can run.
When a job is enabled, a validity check is performed. If the check fails, the
job is not enabled.

For more information refer to the link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/scheduse.
htm#sthref3569 ==> ( see Enabling Jobs and Disabling Jobs)

Regarding run_count please refer to the following metalink article:

Article-ID: Note 602729.1
Title: Mismatch in RUN_COUNT of DBA_SCHEDULER_JOBS

Dropping a Database in 10G

Dropping a Database in 10G Consists of the following steps.

echo $ORACLE_SID
sqlplus "/as sysdba"
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1077936128 bytes
Fixed Size 2034344 bytes
Variable Size 427824472 bytes
Database Buffers 633339904 bytes
Redo Buffers 14737408 bytes
SQL> alter database mount exclusive;

Database altered.

SQL> alter system enable restricted session;

System altered.

SQL> select name from v$database;

NAME
---------
TEST
SQL> drop database;

Database dropped.

Database is dropped & all related files are deleted automatically.

Please be very cautious while using this syntax in multidatabase environment.

Archiver Tracing

The init.ora parameter LOG_ARCHIVE_TRACE can be used to find problems with the archiver process. I recently used this to sort out some archiving issues with a standby database. The parameter is dynamically changable using ALTER SYSTEM.

The following values can be used. To get a combination of the required states add the numbers.
* 0 : Disables Tracing
* 1 : Records archiving of redo log files
* 2 : Records status of archiving for each destination
* 4 : Traces archival operation
* 8 : Tracks activity at the archive log destination
* 16 : Tracks in detail activity at archive log destination
* 32 : Records archive log destination parameter changes
* 64 : Tracks ARCn process state
* 128: Traces FAL server activity
* 512: Traces asynchronous log writer activity
*1024: Traces RFS client
*2048: Traces RFS/ARCn heartbeats

How do I find the overall database size?

how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/