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
Showing posts with label How To's. Show all posts
Showing posts with label How To's. Show all posts
Tuesday, October 11, 2011
Wednesday, January 7, 2009
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;
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;
Friday, December 26, 2008
How to change the database listener port with EBS system
How to change the database listener port with EBS system
1. In dbtier xml file under/appsutil change the below line to reflect
the right port
1541
2. In/network/admin/
change the prot values manually in listener.ira and tnsnames.ora
3. Run autoconfg in db tier. if it completes perform the following
4. In apps tier xml file under/admin change the below line to reflect the
right port
1541
5. Run autoconfig in apps tier
1. In dbtier xml file under
the right port
2. In
change the prot values manually in listener.ira and tnsnames.ora
3. Run autoconfg in db tier. if it completes perform the following
4. In apps tier xml file under
right port
5. Run autoconfig in apps tier
How to enable the multi-lingual option for some modules in R12
How to enable the multi-lingual option in R12
=============================================
Refer the following Metalink notes:
Note 252422.1:Requesting Translation Synchronization Patches
Note 550206.1:Oracle Applications NLS Release Notes, Release 12.0.4
=============================================
Refer the following Metalink notes:
Note 252422.1:Requesting Translation Synchronization Patches
Note 550206.1:Oracle Applications NLS Release Notes, Release 12.0.4
Wednesday, December 10, 2008
How to change the characterset of Oracle 10g DB
Decide the character set you want to change and check whether new character is superset of old character set
1.SQL> shutdown immediate
2.SQL> startup open restrict
3.SQL> alter database character set internal_use UTF8;
4.SQL> shutdown immediate
5.SQL> startup
Refer the following Metalink Note for more details:
Note 225912.1 Changing the Database Character Set - a short overview
Note 227330.1 Character Sets & Conversion - Frequently Asked Questions
1.SQL> shutdown immediate
2.SQL> startup open restrict
3.SQL> alter database character set internal_use UTF8;
4.SQL> shutdown immediate
5.SQL> startup
Refer the following Metalink Note for more details:
Note 225912.1 Changing the Database Character Set - a short overview
Note 227330.1 Character Sets & Conversion - Frequently Asked Questions
Tuesday, December 9, 2008
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}'`
$ kill -9 `ps -ef |grep PROD |awk '{print $2}'`
Monday, November 24, 2008
How to use crontab to rsync CCM log and out files
About CRON
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix.
Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.
Crontab Commands
export EDITOR=vi specify a editor to open crontab file
crontab -e edit/create crontab file
crontab -l display your crontab file.
crontab -r remove your crontab file.
crontab -v * display the last time crontab file was edited
* This option is only available on a few systems.
Crontab syntax
A crontab file has five fields for specifying day, date and time followed by the command to be run at that interval.
* * * * * command to be executed- - - - -| | | | || | | | +----- day of week (0 - 6) (Sunday=0)| | | +------- month (1 - 12)| | +--------- day of month (1 - 31)| +----------- hour (0 - 23)+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas.
An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .
Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * At 00:30 Hrs on 1st of Jan, June & Dec.
:
0 20 * 10 1-5 At 8.00 PM every weekday (Mon-Fri) only in Oct.
:
0 0 1,10,15 * * At midnight on 1st ,10th & 15th of month
:
5,10 0 10 * 1 At 12.05,12.10 every Monday & on 10th of every month
:
Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.
Workshop Task - Establish log and out File Synchronization
In the workshop, the requirement is to synchronise the concurrent manager “log” and “out” files from the Primary to the Standby machine, every 15 minutes.
In this example, we are using the following machines:-
machine01.uk.oracle.com is machine A (Primary machine)
machine02.uk.oracle.com is machine B (Standby machine)
On the Standby machine:
mkdir -p/log
mkdir -p/out
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/out
On the Primary Machine
mkdir -p/log
mkdir -p/out
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/out
From machine A
We will be pushing files from machine A every 30 minutes, on the hour and half hour, so our entries will be:
$ crontab -e
#
## Push log and out files from this machine to machine02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc oracle@machine02: /oracle/PROD/inst/apps/PROD_machine01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#
As crontab uses “vi” syntax, save changes using :wq!
From machine B
We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:
#
## Push log and out from this machine to machine01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc oracle@machine01:/oracle/PROD/inst/apps/PROD_machine02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix.
Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.
Crontab Commands
export EDITOR=vi specify a editor to open crontab file
crontab -e edit/create crontab file
crontab -l display your crontab file.
crontab -r remove your crontab file.
crontab -v * display the last time crontab file was edited
* This option is only available on a few systems.
Crontab syntax
A crontab file has five fields for specifying day, date and time followed by the command to be run at that interval.
* * * * * command to be executed- - - - -| | | | || | | | +----- day of week (0 - 6) (Sunday=0)| | | +------- month (1 - 12)| | +--------- day of month (1 - 31)| +----------- hour (0 - 23)+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas.
An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .
Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * At 00:30 Hrs on 1st of Jan, June & Dec.
:
0 20 * 10 1-5 At 8.00 PM every weekday (Mon-Fri) only in Oct.
:
0 0 1,10,15 * * At midnight on 1st ,10th & 15th of month
:
5,10 0 10 * 1 At 12.05,12.10 every Monday & on 10th of every month
:
Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.
Workshop Task - Establish log and out File Synchronization
In the workshop, the requirement is to synchronise the concurrent manager “log” and “out” files from the Primary to the Standby machine, every 15 minutes.
In this example, we are using the following machines:-
machine01.uk.oracle.com is machine A (Primary machine)
machine02.uk.oracle.com is machine B (Standby machine)
On the Standby machine:
mkdir -p
mkdir -p
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/out
On the Primary Machine
mkdir -p
mkdir -p
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/out
From machine A
We will be pushing files from machine A every 30 minutes, on the hour and half hour, so our entries will be:
$ crontab -e
#
## Push log and out files from this machine to machine02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc oracle@machine02: /oracle/PROD/inst/apps/PROD_machine01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#
As crontab uses “vi” syntax, save changes using :wq!
From machine B
We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:
#
## Push log and out from this machine to machine01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc oracle@machine01:/oracle/PROD/inst/apps/PROD_machine02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#
Wednesday, October 29, 2008
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
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
Saturday, October 25, 2008
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
/
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
/
Subscribe to:
Posts (Atom)