Feature of Case sensitive passwords in 11g with illustration

Feature of Case sensitive passwords in 11g with illustration

In Oracle 11g database you can use case sensitive security feature with parameter

SEC_CASE_SENSITIVE_LOGON

How to enable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.


CONN / AS SYSDBA
CREATE USER test IDENTIFIED BY Test;
GRANT CONNECT TO test;
SQL> CONN test/Test
Connected.
SQL> CONN test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to TRUE then you have to enter password in exact case in which you assign the password to user and in case when you change the case of password it is not connected .

How to Disable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;



System altered.

CONN / AS SYSDBA
CREATE USER test_2 IDENTIFIED BY Test_2;
GRANT CONNECT TO test;
SQL> CONN test_2/Test_2
Connected.
SQL> CONN test_2/test_2

Connected.


Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to FALSE then you have to enter password in any case you connected .

Temporary Tablespace enhancements in oracle 11g with example

Temporary Tablespace enhancements in oracle 11g with example

oracle 11g introduce new view DBA_TEMP_FREE_SPACE for information about temprary tablespace

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                1965031424      916455424  1048576000

1 row selected.


For shrinking temporary tablespace

SQL> ALTER TABLESPACE temp SHRINK SPACE;
Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                916455424        916455424   0

1 row selected.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M;

Tablespace altered.

If you omit the KEEP clause database will shrink the tablespace or tempfile to the smallest possible size.

How To Enable Automatic Memory Management in Oracle 11G Database

How To Enable Automatic Memory Management in Oracle 11G Database

This is the new feature introduce in oracle 11g database for automatically managing the SGA and PGA

which is known as automatic memory management(AMM) .prior to oracle 11g database there is Automatich shared memory management (ASMM) in oracle 10g database which only manage SGA automatically .

For enabling and using there are two parameters in oracle 11g database which are:-

MEMORY_MAX_SIZE

MEMORY_TARGET

in which you can set MEMORY_TARGET upto the value of MEMORY_MAX_SIZE.

For enabling AMM

1. set memory_max_size to some value say 2gb

SQL>alter system set memory_max_size=2g scope=spfile;

2. set memory_target upto the value of memory_max_size say 1600m

SQL> alter system set memory_target=1600m scope=spfile;

3. set sga and pga equals to 0

SQL> alter system set pga_aggregate_target=0 scope=spfile;

SQL> alter system sga_max_size=0 scope=spfile;

4. now shutdown database

shutdown immediate

startup

now you don't need to set sga or pga manually now its oracle responsibility to set it according to the requirement

For Disabling AMM

1. set memory_target=0

SQL> alter system set memory_target=0 scope=spfile;

2. set sga and pga to some value say sga_aggregate_target to 1700m and pga_target =300m SQL> alter system sga_max_size=1700m scope=spfile;

SQL> alter system set pga_aggregate_target=300m scope=spfile;

3. now restart the database

shutdown immediate;

startup

now you have to manage and set PGA manually SGA is automatically set and all the caches are automatically set by oracle

How to solve ORA-04044: procedure, function, package, or type is not allowed here

How to solve ORA-04044: procedure, function, package, or type is not allowed here

While creating shell script some time we face error

ORA-04044: procedure, function, package, or type is not allowed here

NOTE:

this is because we are using tables containing '$' SYMBOL and when ever we use tables like v$session,v$log,v$logfile etc shell script take '$' SYMBOL as shell variables and then while reading script it gives error

ORA-04044: procedure, function, package, or type is not allowed here

SOLUTION:-

for overcome this error always use escape '\' SYMBOL before '$' SYMBOL in table name

like we are use

select * from v$session;

select * from v$log;

in shell script we have to use

select * from v\$session;

select * from v\$log;

and this will solve our error and sucessfully run our shell script.

Shell Script For Changing RedoLog File Size

Shell Script For Changing RedoLog File Size 

here we are changing redolog file size from 100m to 150m
database name =test
present redologs are 2
always remember

1.always use escape '\' before table table including '$' symbol
2.you can not drop one redo log if there are two because minimum redologs required for a instance are 2. 
3.thats why when in this script when we try to drop second group it gives error thats why there is need of third group
 4.if you want to drop a group it should be inactive , in active mode you can not drop it because active mode redo log required for recovery if database crash.
5.In this script after dropping a redolog it is also required that it is deleted from physical system also.

export ORACLE_SID=test
sqlplus "/ as sysdba" << EOI
spool /oracle/s.txt;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 2;
!rm /oracle/test/redo03.log;
alter database add logfile group 3 '/oracle/test/redo03.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 2;
!rm /oracle/test/redo2.log;
alter database add logfile group 2 '/oracle/test/redo02.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter system switch logfile;
select * from v\$logfile;
select * from v\$log;
alter system checkpoint;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 1;
!rm /oracle/test/redo1.log;
select * from v\$logfile;
select * from v\$log;
alter database add logfile group 1 '/oracle/test/redo01.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 3;
! rm /oracle/test/redo3.log;
spool off;
EOI