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

How to open standby database for read only

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

1. Start the Oracle instance for the standby database without mounting it:

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/

initsid1.ora';

2. Now Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

3. Now Open  database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for standby database for read only access when it is mounted and performing

managed recovery(MRP):

1. Firstly Cancel log apply services by:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Now Open the database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for converting Standby database from Read only to start performing managed

recovery(MRP):

1. Check all the connected sessions if there is any connected session then Terminate all

active user sessions on the standby database.

2. Now Restart log apply services:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

 2> DISCONNECT FROM SESSION;

example of converting standby database for Read Only access

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/initpri16.ora';

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

--------------------                                                           

john                                                                         

SQL> create table destination(location varchar2(100));

create table destination(location varchar2(100))

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1

ERROR:

ORA-28009: connection to sys should be as sysdba or sysoper

Warning: You are no longer connected to ORACLE.

SQL> conn

Enter user-name: sys/pwd@sid1

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

Warning: You are no longer connected to ORACLE.

SQL> show user

USER is ""

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> show user

USER is "SYS"

Steps for standby database for read only access when it is mounted and

performing managed recovery(MRP):

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

--------------------                                                           

john                                                                         

Steps for converting Standby database from Read only to start performing

managed recovery(MRP):

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

Primary Database to Standby Database switchover

Database Switchover Steps Are:

1. Firstly Convert primary database to standby

CONNECT sys/pwd@sidname AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

2. Shutdown primary database

SHUTDOWN IMMEDIATE;

3. Mount old primary database as standby database

STARTUP NOMOUNT  PFILE=/u01/app/oracle/product/9.2.0/dbs/

initsidname.ora

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM

SESSION;

4. Convert standby database to primary database

CONNECT sys/pwd@sidname AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

5. Shutdown standby database with immediate

SHUTDOWN IMMEDIATE;

6. Now Open old standby database as primary database.

STARTUP PFILE=/u01/app/oracle/product/9.2.0/dbs/initsidname.ora

example of primary database to standby database switchover

At Primary Database with sid=sid

SQL> alter database commit to switchover to standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile=/u01/app/oracle/product/9.2.0/dbs/initsid.ora

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list;

Database log mode               Archive Mode

Automatic archival             Enabled

Archive destination             /archive/sid1

Oldest online log sequence        17

Next log sequence to archive    0

Current log sequence                20

At Standby Database with sid=sid1

SQL> alter database commit to switchover primary;

alter database commit to switchover primary

ERROR at line 1:

ORA-00946: missing TO keyword

SQL> alter database commit to switchover to primary;

Database altered.

                                    *

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup pfile=/u01/app/oracle/product/9.2.0/dbs/initsid1.ora ;

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

Database mounted.

Database opened.

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME

--------------------

john