Thread: Geeks - Tip of the Day

You can restrict user login attempts using profiles.

Profiles are a named set of password and resource limits

To create a new profile first connect as sysdba and create a new profile as folllows.

Create profile password_test
limit
failed_login_attempts 3;

If the user fails to log in within three attempts, the user’s accounts will be locked for a specified period or until the dba manually unlocks them.

Create a new user and assign the profile password_test to new user.

Create user test_user identified by test_user

grant create session to test_user

alter user test_user profile password_test

You can check the status of test_user account using the following statement.

Code:

 
sql> select username, account_status 
   2 from dba_users 
   3 where username = 'test_user'; 

username account_status 
------------------------------ ----------------- 
ssapllive open

Give wrong password for three times and observe the account status.

Code:

 
sql> select username, account_status 
    2 from dba_users 
    3 where username = 'test_user'; 

username account_status 
------------------------------ ------------------------ 
test_user locked(timed)

Use the following command to unlock the account.

Alter user test_user account unlock

How to enable archive log mode?

To enable archive log mode

1. Shut down the database

2. Take full offline backup

3.create pfile from spfile if you are spfile , Using the following command sql>create pfile='c:\pfile.ora' from spfile

4. Then edit pfile or init.ora file and specify the archive log destination
log_archive_start='true'
log_archive_dest='f:\archive'
log_archive_max_processes=4

To specify multiple destinations
log_archive_dest_1=’f:\archive’
log_archive_dest_2=’e:\archive’

5.creat spfile from pfile='c:\pfile.ora'

6.create archive destination folders

7.startup mount

8.alter database archivelog;

9.archive log list;

10.alter database open;

11.sho parameter log;

12. Alter database backup controlfile to trace; - this is for safety purpose

13. Sql>shutdown immediate;

14.sql>startup.

It is recommended that you take a full backup after you brought the database in archive log mode.

Creation of linux user and Creation of Oracle directory

1. Connect as root user

For adding user to linux ,assigning dba group to username
2 useradd -d /home/username -m -g dba -s /bin/sh username

3 passwd username

enter password for username
reenter password for username

4 connect as username in linux

5 cd $HOME

For read,write privileges to username

6 chmod 777 username

7 Add env variable to .bash_profile

8 Run .bash_profile

9 check connection of sqlplus

For Creation of oracle directory

1. connect as username

2 mkdir LOGFILES

3 chmod 755 LOGFILES

4 chmod g+w LOGFILES

5 for creating directory

sqlplus > conn system/xxxxxxxx

create or replace directory LOGDIR as '/home/username/LOGFILES'
grant read,write on directory LOGDIR to scott

sqlplus > conn scott/tiger

test the simple script


DECLARE
output_file utl_file.file_type;
begin
output_file := utl_file.fopen ('LOGDIR','x.txt', 'w');
utl_file.put_line (output_file, 'XXXXXXXXXXXX');
utl_file.fclose(output_file);
Exception
when others then null;
end;


6 Test the x.txt file in LOGFILES.

Dynamic performanc views - I

Dynamic performance views are identified by the prefix v_$.
Public synonyms for these views have the prefix v$.
The v$ views provide a continually updated look at internal statistics

Instance related views
I.v$sga
It contains summary information on system global area.

Code:

 
sql> select * 
    2 from v$sga; 
Name                      Value 
-------------------- ---------- 
fixed size                 454584 
variable size             218103808 
database buffers       25165824 
redo buffers              667648

It is same as show sga in sql *plus with the exeption that it also show the total.

Code:

 
sql> show sga; 
total system global area 244391864 bytes 
fixed size                     454584 bytes 
variable size                 218103808 bytes 
database buffers           25165824 bytes 
redo buffers                 667648 bytes

Dynamic performanc views - II

II.v$sgastat
It constains detailed information on the system global area or contains information on sga segment sizes

Code:

 
sql> select * 
    2 from v$sgastat; 
pool          name             bytes 
----------- -------------------------- ---------- 
                 fixed_sga                          454584 
                 buffer_cache                     25165824 
                 log_buffer                         656384 
shared pool errors                                9612 
shared pool enqueue                            171860 
shared pool kgk heap                            3756 
shared pool kqr m po                            304168 
shared pool kqr s po                             62212 
.................... 
Shared pool message pool freequeue       665792 
shared pool ksxr pending messages que   841036 
shared pool event statistics per sess     1913520 
43 rows selected.

Query to know freespace in sga

Code:

 
sql> select * from v$sgastat w
here name = 'free memory'; 
pool            name                      bytes 
----------- -------------------------- ---------- 
shared pool free memory              151228400 
large pool   free memory              8388608 
java pool    free memory              33554432

Dynamic performanc views - III

V$instance :- It displays information of the current instance.

Code:

SQL>  SELECT   instance_name,
  2                  version,
  3                  status,
  4                  instance_role
  5      FROM     v$instance;

INSTANCE_NAME    VERSION           STATUS       INSTANCE_ROLE
---------------------------------------------------------------
orcl            9.2.0.6.0          OPEN        PRIMARY_INSTANCE

Instance_role can be used to determine if an instance is an active instance (primary instance) or a secondary instance (in a standby environment).