users account locked.

IF your oracle user account get locked frequently here is the solution

In 11g onwards there is a small limitation of the password life time in default prfiles.
The users who use default profiles will get affected(locked) because of this.

In previous verions (<11g) is it by default set to unlimited, but in 11g onwards as a 
security measure its value limited to 180 days. Users a/c will lock when they reach 180
days after when they created. 

we can change the value as we want.


To find user and his profile
SELECT username, account_status,profile FROM dba_users WHERE username= 'APPUSR';

To see the profile contents/limitaions
SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';



All resource limits for DEFAULT profile is set to UNLIMITED,
but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10).

Due to this the user account keeps getting locked(timed).When we check in
the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute
for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.



What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile,
or 
create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons,

we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign
that profile to the user.

Creating a profile.


SQL>CREATE PROFILE APPUSR_DEFAULT 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 UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.


later we can chenge the values like

alter profile appusr_default set failed_login_attempts = 4;


Some parameters & their descriptions
===========================
failed_login_attempts -  This is the number of failed login attempts before locking the Oracle user account.
                                 The default is three failed attempts.

password_grace_time - This is the grace period after the password_life_time limit is exceeded. 


password_life_time -    This is how long an existing password is valid. The default here forces a password        
                                    change every 60 days.


password_lock_time –  This specifies how long to lock the account after the failed login attempts is met.
                                   Most DBA’s set this value to UNLIMITED.

password_verify_function - This allows you to specify the name of a custom password verification function.
                                      
Most confusing parameters are..

password_reuse_time  &  password_reuse_max

These two parameters must be set in conjunction with each other.
PASSWORD_REUSE_TIME :specifies the number of days before which a password cannot be reused.
PASSWORD_REUSE_MAX :specifies the number of password changes required before the current   
                                                 password can be reused.

If you specify an integer for both of these parameters,

then the user cannot reuse a password until the password has been changed the password 
the number of times specified for PASSWORD_REUSE_MAX 
during the number of days specified for PASSWORD_REUSE_TIME.

For example, if you specify

PASSWORD_REUSE_TIME       to 30
and PASSWORD_REUSE_MAX to 10,

then the user can reuse the password after 30 days if the password has already been changed 10 times.

**************

Invalid Objects

To know about invalid objects

Select * from user_objects where status like 'INVALID';

 

Invalid objects  with their compilation commands with in a schema

      select     'ALTER ' || object_type || ' ' || object_name ||' COMPILE' || '; '    
                    SCRIPT_FOR_INVALID_COMPILATION
      from       user_objects
      where     status like 'INVALID';


a small thing we have to note is:

For  packages it will show commands to compile package & package body seperatley.

There is no seperate command for compiling package body.

For compiling PACKAGES use the below command

"alter package packag_name compile" (is enough).

*****************

How to add new lines in oracle pl/sql

 

​​We can add new lines in plsql by using  ​

DBMS_OUTPUT.PUT ( CHR(10));​

​Exapmle:​

​

SQL> set serveroutput on;

declare

c1 number;

c2 number;

begin

select count(*) into c1 from dba_users;

select count(*) into c2 from dba_objects;

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.put_line('****** ********************** *****');

dbms_output.put_line('USERS:> '||c1);

dbms_output.put_line('OBJECTS:> '||c2);

DBMS_OUTPUT.put_line('****** ********************** *****');

end;

/

*****************

Rman commands

Some RMAN Queries..

FULL DATABASE BACKUP USING RMAN
================================

os> rman connect target /


run {
allocate channel disk_1 type disk;
backup format '/SAN/rman_backup/inc_0_datafiles.dbf' database;
sql 'alter system archive log current';
backup  format '/SAN/rman_backup/archives_backup.arc_bkp'  archivelog all;
backup format '/SAN/rman_backup/cntrl_backup.cntrl_bkp'  current controlfile;
release channel disk_1
     }



With Example

I started to take full rman database backup with the previous commands.

For cheking the status of rman operation, how much it is completed & approximatley when will finish etc.
by using below command.


SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';                              

Session altered.


SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 PERdone,
          sysdate + TIME_REMAINING/3600/24 Expected_to_end_at
          from v$session_longops
          where totalwork > sofar
          AND opname NOT LIKE '%aggregate%'
          AND opname like 'RMAN%';



will get o/p as like this

For knowing Sesion id and wait time,spid we can use below query

wait time:  Time waiting to get resource
spid       :  os process id, in Linux we can see by "top" command
                If needed we can kill from os level by using "kill -9 spid"



   select
       sid, spid, client_info, event,seconds_in_wait, p1, p2, p3
       from  v$process p, v$session s
       where p.addr = s.paddr
       and client_info like 'rman channel=%';

Inorder to get a over all status of rman backups use the below query

select command_id,operation,status,input_bytes,
mbytes_processed,start_time,output_device_type
from   v$rman_status order by start_time;

Tablespaces & Datafiles

Tablespaces & Their Datafiles information..

No. of Datafiles in each Tablespaces.

SELECT  Tablespace_name,COUNT(file_name) no_OF_DATAFILES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;



Tablepsaces & Their datafiles info.

SELECT  Tablespace_name,file_name, ROUND(bytes/1024000) USED_MB
FROM dba_data_files
ORDER BY 1;

Querry to f ind  Tablespaces , datafiles, auto extensiblity, usage statitics

SELECT    tablespace_name, file_name file_id, BYTES / 1024 / 1024 AS mb,
autoextensible, increment_by * 8192 / 1024 / 1042 AS next_mb,
maxbytes / 1024 / 1024 AS max_mb 
FROM    dba_data_files
ORDER BY tablespace_name, file_id;