Wednesday, March 30, 2011

Monitoring the Database Administrator’s activities

Monitoring the Database Administrator’s activities

Its agree, the DBA is the unrestricted owner of the database. An organization’s most critical information is entirely exposed and controlled by this technologists. This leaves both the DBA, and the entire organization, in a precarious position. On one hand, the DBAs are afraid they will be blamed for any information leak, while on the other hand, the organization is forced to trust a small group of professionals in its technology group.

How do you properly audit database Administrator’s activity?

An answer may be,

Not through database’s native auditing, which fails here because it is fully under the control of the DBAs, who can turn off auditing, clear the audit logs, manipulate an audit record, or even reconfigure auditing to filter their own malicious activity. An ideal audit system is intelligent enough to distinguish database administration accounts, filter out “noise” and irrelevant events, and succinctly illustrate its activities. As well, the system should write audited data to a secure location where even the DBA would not have direct control over the recorded activity.

Another way to mitigate risk is to audit and monitor DBA activities. It is necessary to limit the amount of work a DBA does on a production server.

Others approach can be,

• Provide segregation of duties

• Limit the DBA from disabling the auditing

• Limit the DBA from deleting audit records

• Limit the DBA from changing auditing configuration

Segregation of duties is the key to meaningful security and regulatory compliance. The auditing subsystem must retain integrity, and must not be manipulated by the users it is meant to monitor. The “observer” and the “observed” cannot be the same person.


Auditing level can have several shortcomings. Which can affect over all data base performance?

  1. First, since auditing is based in the database, it can detract from the system’s performance. This is especially true when you attempt to record every access to certain data; the constant reading and writing of auditing can result in substantial disk I/O on the database server, creating a bottleneck that significantly slows down database performance.
  2. Another disadvantage: space and increasing database size. And this is a costly matter for database.

  1. There is no intelligence built into the auditing feature. It takes a significant amount of effort to turn on and off AUDIT for specific activity .This creates a technologist nightmare, as any changes must be configured in live server, wasting time and introducing risk of human error.

  1. Oracle Database Auditing includes no logic to detect and highlight malicious activity, nor can this be configured. Database auditing is great at amassing a huge amount of data, but is useless in finding the evidence of malicious activity.

Tuesday, March 29, 2011

ORA-00313: open failed for members of log group 1 of thread 1

Workaround of ora-00312 and ora-00313 error :-
---------------------------------------------

shutdown immediate the database and deleted one of the online redo logs via OS command, in this case there are only 2 groups with 1 log member in each. When you try to open the database you receive the following errors:

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'


Solution of these error:-
---------------------------------


ORA-00312 and ORA-00313 occurred when a redo log file is missing by any type of media failure.

If a media failure has affected the online redo logs of a database, then the appropriate recovery Solution depends on the following:

1) The configuration of the online redo log:

i) multiplexed
ii) non-multiplexed


2) The status and types of online redo log files affected by the media failure:

SQL> select group#,thread#,status from v$log ;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
4 1 INACTIVE

and 5) unarchived

so i define it in three parts
(1)----- When deleted log file is multiplexed OR Mirrored
(2)-----when status of redolog file is INACTIVE (has been archived and not archived)
(3)-----when status of redolog file is CURRENT and ACTIVE



--------------------------------------------------------------
(1)----- When deleted log file is multiplexed OR Mirrored------
--------------------------------------------------------------


If the online redo log of a database is multiplexed, means if at least
one member of each online redo log group is not affected by the media
failure, then the database continues functioning as normal, but error
messages are written to the log writer trace file and the alert_SID.log
of the database.

Then copy & paste the one existing member, and rename it to corrupted log file name.
or
drop the damaged member and add a new member by using following command .

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M2.LOG' ;


SQL> ALTER DATABASE ADD LOGFILE MEMBER 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M3.LOG' TO GROUP 2;



live example:-
-----------------


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 2 16:37:58 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> column member format a30
SQL>
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>


After shutdown, here i delete a member of redolog group 1 , which is multiplexed .
then database is open normaly ,but a error is generated in alert log file. see bellow

alert log contents
--------------------
{ Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun Oct 02 17:29:05 2011
ALTER DATABASE OPEN
Sun Oct 02 17:29:06 2011
Errors in file g:\oracle\product\10.2.0\admin\halimdb\bdump\halimdb_lgwr_1064.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Sun Oct 02 17:29:06 2011
Thread 1 opened at log sequence 7
Current log# 4 seq# 7 mem# 0: G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO04.LOG
Successful open of redo thread 1
Sun Oct 02 17:29:06 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Oct 02 17:29:06 2011
SMON: enabling cache recovery
Sun Oct 02 17:29:07 2011
Successfully onlined Undo Tablespace 1.
Sun Oct 02 17:29:07 2011
SMON: enabling tx recovery
Sun Oct 02 17:29:07 2011
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=15, OS id=2488
Sun Oct 02 17:29:11 2011
Completed: ALTER DATABASE OPEN
Sun Oct 02 17:29:11 2011
}






SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;

GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01.LOG

1 INVALID G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01M2.LOG

2 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO02.LOG

3 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO03.LOG

4 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO04.LOG


SQL>
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01M2.LOG' ;

Database altered.

SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>



-------------------------------------------------------------------------
(2)-------------------when status of redolog file is INACTIVE------------
-------------------------------------------------------------------------



Mount the database and check v$log to see if the deleted log is INACTIVE .
If the log is INACTIVE, simply Clear the log group (in the mount state of the DB)
(alter database clear logfile group 2) ;
or
drop the log group
(alter database drop logfile group 2);
If there are only 2 log groups then it will be necessary to add another group before dropping this one.


[Note :-
1) To clear an inactive, online redo log group that has been archived
simple apply.....(in mount state of DB)
(alter database clear logfile group 2) ;

and

2) To clear an inactive, online redo log group that has not been archived
Clear the log using the UNARCHIVED keyword. For example,
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;
and Immediately back up the whole database.
so that you have a backup you can use for complete recovery without relying
on the cleared log group.
and backup control file also using
ALTER DATABASE BACKUP CONTROLFILE TO 'D:/oracle/control_file_bk/con1.f';
]





Live example:-
---------------
SQL> select group#,thread#,status from v$log
2 /

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 CURRENT

SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

[after shutdown the database, here i delete "REDO02.LOG" manually via OS command, which is INACTIVE]

SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'


SQL>
SQL>
SQL>
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE order by group#;

GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO01.LOG

2 STALE G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO02.LOG

3 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO03.LOG

4 G:\ORACLE\PRODUCT\10.2.0\ORADA
TA\HALIMDB\REDO04.LOG


SQL>
SQL>
SQL>
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO02.LOG'


SQL>
SQL>
SQL>
SQL> alter database clear logfile group 2 ;

Database altered.

SQL>
SQL>
SQL>
SQL> alter database open ;

Database altered.

SQL>
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL>

--------------------------------------------------------------------------
(3)-----------when status of redolog file is CURRENT and ACTIVE-----------
--------------------------------------------------------------------------


First Mount the database and check v$log to see if the deleted log is current;
If the log is current they should simply perform fake recovery and then
open resetlogs the Backup full database immediately .
below are the steps.......

1) connect as sysdba like..
SQL> conn / as sysdba

2) startup mount .

3) recover database until cancel.

4) alter database open resetlogs .

5) Backup the Database (full).



Live example:-
---------------

SQL> ed
Wrote file afiedt.buf

1 SELECT group# "GROUP", status, MEMBER, TYPE, is_recovery_dest_file
2 FROM v$logfile
3* ORDER BY 1, 2
SQL> /

GROUP STATUS MEMBER TYPE IS_
---------- ------- ------------------------------ ------- ---
1 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO01.LOG

2 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO02.LOG

3 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO03.LOG

4 G:\ORACLE\PRODUCT\10.2.0\ORADA ONLINE NO
TA\HALIMDB\REDO04.LOG


SQL> select group#,thread#,status from v$log
2 /

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 1 INACTIVE

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> select group#,thread#,status from v$log
2 /

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
4 1 INACTIVE

SQL>
SQL> alter system switch logfile ;

System altered.

SQL>
SQL>
SQL> select group#,thread#,status from v$log ;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT
4 1 INACTIVE

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

[after shutdown the database, here i delete "REDO03.LOG" manually via OS command, which is CURRENT ]


SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 197135236 bytes
Database Buffers 406847488 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'


SQL>
SQL> alter database clear logfile group 3 ;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL>
SQL> alter database clear logfile group 3 ;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL>
SQL>
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> recover database until cancel;
Media recovery complete.
SQL>
SQL>
SQL> alter database open
2 /
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL>

Monday, March 28, 2011

ORA-00600: internal error code, arguments: [kxsGetRuntimeLock2]

ORA-00600: internal error code, arguments: [kxsGetRuntimeLock2]
================================================================
Oracle Server - Release: 11.1 and later


Automatic Shared Memory Management (ASMM) is enabled, but no minimum size is explicitly set for parameter shared_pool_size.


The error is caused by memory configuration with ASMM enabled and no shared_pool_size set.

Solution
=========

The error is resolved by explicitly setting a minimum value for shared_pool_size.

Memory management in Oracle database

Memory management in Oracle database

=============================

===========================================

Introduction to Database Memory Components

===========================================

The basic memory structures associated with Oracle Database include:

■ System Global Area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes.

■ Program Global Area (PGA)

A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total PGA memory allocated for all background and server processes attached to an Oracle Database instance is referred to as the total instance PGA memory, and the collection of all individual PGAs is referred to as the total instance PGA, or just instance PGA.

It contains global variables and data structures and control information for a server process. example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.

The performance of complex long running queries, typical in a DSS environment, depend to a large extent on the memory available in the Program Global Area (PGA) which is also called work area.

=======================================

Evolution of Memory Management Features

=======================================

Memory management has evolved with each database release

Oracle 9i

---------

Beginning with Oracle9i, the dynamic SGA infrastructure allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. Key features being

* Dynamic Memory resizing

* DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS

* DB_nK_CACHE_SIZE for multiple block sizes

* PGA_AGGREGATE_TARGET Introduction of Automatic PGA Memory management

Oracle Database 10g

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

Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g

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

Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

The most important SGA components are the following:

Component

Description

Parameter

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.

DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

LOG_BUFFER

Shared Pool

The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.

SHARED_POOL_SIZE
SHARED_POOL_RESERVED_SIZE
RESULT_CACHE_SIZE *

Large Pool

Used for allocating session memory for shared server, Oracle XA, or parallel query buffers or for RMAN.

LARGE_POOL_SIZE

Java Pool

Java pool memory is used in server memory for all session-specific Java code and data within the JVM.

JAVA_POOL_SIZE

Streams Pool

The streams pool is used exclusively by Oracle Streams. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.

STREAMS_POOL_SIZE

* RESULT_CACHE_MAX_SIZE is new component which has been introduced as part of 11g Memory architecture. The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.Results of queries and query fragments can be cached in memory in the SQL query result cache. The database can then use cached results to answer future executions of these queries and query fragments. Similarly PL/SQL Function Result can also be cached.

You have to use RESULT_CACHE_MODE initialization parameter which determines the SQL query result cache behavior. The possible initialization parameter values are MANUAL and FORCE.

Oracle Database 11g supports various memory management methods, which are chosen by initialization parameter settings. Oracle recommends that you enable the automatic memory management method.

1. Automatic Memory Management – For Both the SGA and Instance PGA

2. Automatic Shared Memory Management – For the SGA

3. Manual Shared Memory Management – For the SGA

4. Automatic PGA Memory Management – For the Instance PGA

5. Manual PGA Memory Management – For the Instance PGA

1.Automatic Memory Management – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.

Switching to Automatic Memory Management

1)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL>SHOW PARAMETER TARGET
NAME TYPE VALUE
------------------------------ ----------- ----------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 200M
sga_target big integer 500M


Add the values of pga_aggregate_target and sga_target. In our case it would sum to 700 M

2)Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 808M

3)Change the parameter in initialization parameter file.

Using Spfile
========

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;


Using Pfile
=======
If you have started the instance with Pfile, then edit the pfile and set the parameters manually


MEMORY_MAX_TARGET = 808M
MEMORY_TARGET = 808M
SGA_TARGET =0
PGA_AGGREGATE_TARGET = 0



In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

MEMORY_MAX_TARGET is a static parameter i.e it cannot be changed Dynamically and Instance has to be bounced for modifying the value. So ensure that you have set it to appropriate value.


4)Shutdown and startup the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
mount
ORACLE instance started.

Total System Global Area 845348864 bytes
Fixed Size 1303188 bytes
Variable Size 469765484 bytes
Database Buffers 369098752 bytes
Redo Buffers 5181440 bytes
SQL> show parameter target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 808M
memory_target big integer 808M
pga_aggregate_target big integer 0
sga_target big integer 0


The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Note : - In case you set any parameter value to value which is higher then MEMORY_TARGET, then you get the following error. E.g Setting SGA_MAX_SIZE to value of 900 M results in following

SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account, see alert log for more information

00844, 00000, "Parameter not taking MEMORY_TARGET into account, see alert log for more information"
// *Cause: The parameter was larger than MEMORY_TARGET.
// *Action: Set the parameter to a lower value than MEMORY_TARGET.


Monitoring and Tuning Automatic Memory Management

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

SQL> select * from v$memory_target_advice order by memory_size;


You can also use V$MEMORY_RESIZE_OPS which has a circular history buffer of the last 800 SGA resize requests.

2. Automatic Shared Memory Management – For the SGA

If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management.This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration.


In case you have enabled Automatic Memory Management , then to switch to Automatic Shared Memory Management , please follow below procedure

SQL>Alter system set MEMORY_TARGET=0 scope=both;
SQL>Alter system set SGA_TARGET=500M scope=both;

3. Manual Shared Memory Management – For the SGA


If you want complete control of individual SGA component sizes, you can disable both automatic memory management and automatic shared memory management. In this mode, you need to set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.

In this case you set SGA_TARGET and MEMORY_TARGET to 0 and set value for other SGA components upto value of SGA_MAX_SIZE


4. Automatic PGA Memory Management – For the Instance PGA


While using Automatic memory management , PGA memory is allocated based upon value of MEMORY_TARGET. In case you enable automatic shared memory management or manual shared memory management, you also implicitly enable automatic PGA memory management.

Automatic/Manual PGA memory management is decided by initialization parameter WORKAREA_SIZE_POLICY which is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO.



With automatic PGA memory management, you set a target size for the instance PGA by defining value for parameter named PGA_AGGREGATE_TARGET and sizing of SQL work areas is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. This feature is available from 9i.

At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.




5.Manual PGA Memory Management – For the Instance PGA


In case you wish to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join) then you can enable Manual PGA Memory management.

Set WORKAREA_SIZE_POLICY value to MANUAL and also specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.


Although the Oracle Database 11g supports this manual PGA memory management method, Oracle strongly recommends that you leave automatic PGA memory management enabled.

Table below summarizes the various memory management methods

Memory Management Mode

For

You Set

Oracle Database Automatically Tunes

Automatic memory management(AMM)



SGA and PGA

- Total memory target size for the Oracle instance (Memory_Target)

- (Optional) Maximum memory size for the Oracle instance(Memory_max_Target)

- Total SGA size
- SGA component sizes
- Instance PGA size
- Individual PGA sizes

Automatic shared memory management(ASMM)

(AMM disabled)



SGA

- SGA target size (SGA_TARGET)
- (Optional) SGA maximum size
(SGA_MAX_SIZE)

SGA component sizes

Manual shared memory management

(AMM and ASMM disabled)



SGA

- Shared pool size (SHARED_POOL_SIZE)

-Buffer cache size
(DB_CACHE_SIZE or DB_BLOCK_BUFFERS)

-Java pool size
(JAVA_POOL_SIZE)

-Large pool size
(LARGE_POOL_SIZE)

- (Optional) SGA maximum size
(SGA_MAX_SIZE)


None

Automatic PGA memory management


PGA

Instance PGA target size (PGA_AGGREGATE_TARGET)

Individual PGA sizes

Manual PGA memory management

(not recommended)



PGA

Maximum work area size for each type of SQL operator


None



The Automatic Memory Management (AMM) feature uses background process named Memory Manager (MMAN). This process was introduced in 10g which assisted in Automatic Shared Memory Management (ASMM) using SGA_TARGET. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations

ORA-845: MEMORY_TARGET not supported on this system

ORA-845: MEMORY_TARGET not supported on this system
===================================================

For:-

Oracle Server Version: 11.1.0.6 to 11.2.0.2.0 - Release: 11.1 to 11.2
Linux x86-64

Problem :-
=========

On a Linux system, trying to start up an 11g instance could fail with the following error:

ORA-845: MEMORY_TARGET not supported on this system

In the alert log, you could or could not see the below messages:

ORA-04031 errors

OR

Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature.
This feature requires the /dev/shm file system to be mounted for at
Least bytes.The /dev/shm is either not mounted or is mounted
With available space less than this size.
Please fix this so that MEMORY_TARGET can work as expected.
Current available is and used is bytes.memory_target needs larger /dev/shm

If ORA-04031 is seen in the alert log, sometimes you can not establish new connections due to this problem.


======================
Cause of error
======================

AMM (Automatic Memory Management) is a new feature in 11 which manages both SGA and PGA.

MEMORY_TARGET is used instead of SGA_TARGET and MEMORY_MAX_TARGET is used instead of SGA_MAX_SIZE (defaults to MEMORY_TARGET ).

It uses /dev/shm on Linux. If max_target set over /dev/shm size, you get the error messages.



====================
Solution
====================

1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets
the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size. For example:

# mount -t tmpfs shmfs -o size=7g /dev/shm

Also, to make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=7g 0

2. This error may also occur if /dev/shm is not properly mounted. Make sure your df output is similar to the following:

$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
...
shmfs 6291456 832356 5459100 14% /dev/shm

3. If configuring AMM is not possible due to lack of space on /dev/shm mount point, you can configure ASMM instead of AMM, i.e. set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET instead of MEMORY_TARGET.

How to deinstall/remove XML Database (XDB) 9.2 and 10.2

How to deinstall/remove XML Database (XDB) 9.2 and 10.2
======================================================

THIS NOTE IS NOT FOR 11G and above.

This is for remove the XDB feature into an existing Oracle database.

==============================
XDB SCHEMA REMOVAL STEPS:
===============================

WARNING: The steps to remove the XML Database feature (XDB schema) will completely remove the XDB repository including any user defined information contained within it.
Therefore, if there are any registered schemas or XML Database items created, you will be required to recreate all of your user defined structures, data and re-register all schemas previously registered.

WARNING: If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository , or registered schema aspects of the XDB feature.

WARNING: This is not for 11g.

1. Shutdown and restart the database.

2. Connect as sysdba and run the catnoqm.sql script. Maintain a spool of the
script running:

UNIX: $ORACLE_HOME/rdbms/admin subdirectory
WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory

For Example:
SQL> set echo on
SQL> spool xdb_removal.log
SQL> @?/rdbms/admin/catnoqm.sql


3. Modify the init.ora file or spfile to include the following minimums to prepare the DB for reinstalling the XDB schema:

shared_pool_size =150M
java_pool_size =150M

4. Turn on AUTO EXTEND on the XDB tablespace.
If you do not want to do this , make sure you target a tablespace with at
least
.100 MB of free space for non-UTF8 DB
.150 MB of free space for an AL32UTF8 DB

5. Shutdown the database immediate, and startup the database normal

NOTE: Failure to restart the database at this step can cause XDB catqm.sql installation to fail with an internal error similar to the following: ORA-7445 [qmr_hdl_copy()+48]

How to install XML Database (XDB) 9.2 and 10.2

How to install XML Database (XDB) 9.2 and 10.2
==============================================

THIS NOTE IS NOT FOR 11G and above.

This is for install the XDB feature into an existing Oracle database.


==============================
XDB SCHEMA INSTALL STEPS:
==============================

1. Connect as sysdba and run the catqm.sql script. Maintain a spool of the
script running:

UNIX: $ORACLE_HOME/rdbms/admin subdirectory
WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory

The catqm.sql script requires the following parameters be passed to it when
run:
A. XDB user password
B. XDB user default tablespace (You can use any tablespace other than system undo and temp. This tablespace has to exist prior to running the script.)
C. XDB user temporary tablespace

Therefore the syntax to run catqm.sql will be:

SQL> catqm.sql A B C

For Example:
---------------
SQL> set echo on
SQL> spool xdb_install.log
SQL>@?/rdbms/admin/catqm.sql XDB XDB TEMP



The Following Step is for Release 9.2.x ONLY skip to step 3 if running 10.1.x or above
--------------------------------------------------------------------------------------


2.Reconnect to SYS again and run the following to load the XDB java library.

SQL>@?/rdbms/admin/catxdbj.sql

NOTE: In order to load the XDB java libraries in catxdbj.sql, you must first have a valid Java Virtual Machine installation in the DB (JVM) and a valid XDK.
Also, make sure that the database is started with Oracle9i Release 2 (9.2.0) compatibility or higher for database version 9.2. This script creates objects that are specific to 9.2 even though the the database requires the JVM in 10.1 and above, the itmes that the script creates are no longer built in java in 10.1 and above.


3. If the following line is not already apart of the database system parameters (init.ora/spfile).
NOTE: PLEASE REPLACE ,instanceid1,2 etc with your actual values

a. Non-RAC
dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"
b. RAC
instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"
instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"
etc ...
c.If you are not using the default Listener ensure you have set LOCAL_LISTENER in the (init.ora/spfile)
as prescribed for RAC/NON-RAC instances or the end points will not register.


4. Check for any invalid XDB owned objects:

SQL> select count(*) from dba_objects
where owner='XDB' and status='INVALID';

COUNT(*)
----------
0



5. Check DBA_REGISTRY for XDB status:
SQL> select comp_name, status, version from DBA_REGISTRY where comp_name=
'Oracle XML Database'

The results should indicate the correct version and patch in a valid status.

6. Restart database and listener to enable Oracle XML DB protocol access.



NOTE:

If for any reason the catqm.sql script fails you can repeat the steps. However doing so May result in :

ORA-04098: trigger 'SYS.XDB_INSTALLATION_TRIGGER' is invalid and failed re-validation

If this occurs follow the steps

Run the script catqm.sql

How to Manually Remove Oracle Server Software on Microsoft Windows Platforms

How to Manually Remove Oracle Server Software on Microsoft Windows Platforms
============================================================================


applies to:-
Oracle Universal Installer - Version: 11.2.0.1 to 11.2.0.2
windows 32/64 bits


========================
Solution
========================

A. Single Instance or Client Environment
=========================================

1. Ensure you are logged in as a user with Local Administrative privileges.

2. Stop all Oracle services related to the ORACLE_HOME (if any are running):

* Right click (My) Computer > Manage > Services and Applications OR Configuration > Services.
* Check the Path to Executable field in the Service's Properties if you are unsure from which ORACLE_HOME the service is running.
* Close the Services and/or Computer Management window.

3. Remove any databases using the Database Configuration Assistant (DBCA) running out of the ORACLE_HOME you are removing. Remove any listeners using Net Configuration Assistant (NetCA) running out of the ORACLE_HOME you are removing.

4. Remove the ORACLE_HOME from the OUI inventory:

* %ORACLE_HOME%\OUI\BIN\setup -detachHome ORACLE_HOME=%ORACLE_HOME%

5. Remove entries in the registry:

* Start the registry editor: choose Start > Run > regedit.
* Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ and delete that entry.
* Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC. Expand all subkeys and remove the key: "Oracle in ".
* Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services. Delete any keys with names beginning with Oracle and/or have an ImagePath string pointing to the ORACLE_HOME to be deleted or a location under it.
* Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ocfs (if present) and delete that entry.
* Close regedit.

6. Clean up the environment settings:

* Right click on (My) Computer > Properties > Advanced tab OR Advanced System Settings link > Environment Variables button.
* Under System Variables section, review all variables for any reference to the deleted ORACLE_HOME. Edit by removing the ORACLE_HOME from the string value (such as removing the home from PATH statement) or by deleting the entire variable. Common examples are CLASSPATH, ORACLE_HOME, PATH, and PERL5LIB.
* Click OK to save and exit System Properties.

7. Clean up the start menu icons:

* Right click on the Start button and choose Explore All Users.
* Expand Programs folder.
* Delete the folder Oracle - and any subfolder.

8. If this is the only ORACLE_HOME on the server, delete the Oracle folder under C:\Program Files. DO NOT delete this if there are any other Oracle products on this server.

9. Reboot the Windows server.

10. Go to the TEMP/TMP directory and delete all files and directories.

11. Go to the ORACLE_HOME location and verify all folders/files have been removed.

12. Empty the Recycle Bin to clear all files.


Please note that you should reboot the server to ensure proper cleanup of any disabled Oracle services and/or any removed items still resident in memory.






==========================
B. Clustered Environment
==========================


1. Ensure you are logged in as a user with Local Administrative privileges.

2. Stop the databases and cluster resources:

* Run on any node: “srvctl stop database -d
* Run on any node: “crsctl stop cluster -all”
* Run on all nodes: “crsctl stop crs”

3. Stop all Oracle services related to the ORACLE_HOME (if any are running):

* Right click (My) Computer > Manage > Services and Applications OR Configuration > Services.
* Check the Path to Executable field in the Service's Properties if you are unsure from which ORACLE_HOME the service is running.
* Close the Services and/or Computer Management window.

4. On one node remove any databases using the Database Configuration Assistant (DBCA) running out of the ORACLE_HOME you are removing. Remove any listeners using Net Configuration Assistant (NetCA) running out of the ORACLE_HOME you are removing.

5. Due to Bug 9602707 DEINSTALL OF GI DOES NOT RUN ROOTCRS.PL -DECONFIG IF INSTALL WAS ONLY PARTIAL (fixed in 11.2.0.2), you will have to remove the GRID configuration by running the following steps in a command prompt window:

* Run "set path=%path%;\perl\bin" to set the environment on all nodes.
* For example: set path=%path%;C:\app\11.2\grid\perl\bin
* Run "perl \crs\install\rootcrs.pl -verbose -deconfig -force" on all nodes except the last one.
* Run "perl \crs\install\rootcrs.pl -verbose -deconfig -force -lastnode" on the last node. This command will zero out OCR and Voting disk.

6. Remove the ORACLE_HOME from the OUI inventory:

* %ORACLE_HOME%\OUI\BIN\setup -detachHome ORACLE_HOME=%ORACLE_HOME%

7. Remove entries in the registry:

* Start the registry editor: choose Start > Run > regedit.
* Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ and delete that entry.
* Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC. Expand all subkeys and remove the key: "Oracle in ".
* Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services. Delete all keys where the name starts with Oracle or Ora or has an ImagePath string entry to the ORACLE_HOME to be deleted or a location under it.
* Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ocfs and delete that entry.
* Close regedit.

8. Clean up the environment settings:

* Right click on (My) Computer > Properties > Advanced tab OR Advanced System Settings link > Environment Variables button.
* Under System Variables section, review all variables for any reference to the deleted ORACLE_HOME. Edit by removing the ORACLE_HOME from the string value (such as removing the home from PATH statement) or by deleting the entire variable. Common examples are CLASSPATH, ORACLE_HOME, PATH, and PERL5LIB.
* Click OK to save and exit System Properties.

9. Clean up the start menu icons:

* Right click on the Start button and choose Explore All Users.
* Expand Programs folder.
* Delete the folder Oracle - and any subfolder.

10. If this is the only ORACLE_HOME on the server, delete the Oracle folder under C:\Program Files. DO NOT delete this if there are any other Oracle products on this server.

11. Reboot the nodes.

12. Go to the TEMP/TMP directory and delete all files and directories.

13. Go to the ORACLE_HOME locations and verify all folders/files have been removed.

14. Delete the clusterware drivers in the Windows drivers folder, usually C:\WINDOWS\System32\Drivers:

* ocfs.sys
* oracleacfs.sys
* oracleadvm.sys
* oracleoks.sys
* orafencedrv.sys
* orafenceservice.sys

15. Empty the Recycle Bin on all nodes.

16. You must reinitialize all shared disks to allow for a clean environment for reinstall from one of the nodes in the cluster. Please follow all steps in Document 341214.1 How To clean up after a Failed (or Successful) Oracle Clusterware Installation on Windows, including using logpartformat to reinitialize all shared disks.


NOTE: Unless otherwise noted, the following steps must be performed on all nodes in the cluster.

Please note that you should reboot all the nodes to ensure proper cleanup of any disabled Oracle services and/or any removed items still resident in memory.

How to Deinstall Oracle Spatial Locator in 10g/11g manually

How to Deinstall Oracle Spatial Locator in 10g/11g manually
===========================================================

Solution
===========


note: [SPATIAL must not be installed before de-installing Locator]

SQL> connect / as sysdba
SQL> select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';


If the above query returns a result, you cannot de-install Locator because Locator is part of Spatial installation and without removing Spatial , Locator cannot be de-installed.

If the above query does not return a result, execute following steps to de-install Locator.

SQL>Conn / as sysdba
SQL>spool locator_remove.lst
SQL>@?/md/admin/mddinloc.sql
SQL> spool off

Oracle Deinstall Utility

Oracle Deinstall Utility - 11g R2
====================================

Starting with Oracle Database 11g Release 2 (11.2.0.1.0), a separate deinstallation and deconfiguration tool is available as a separate download. The deinstall tool is for deinstalling the 11.2 software. This tool can be used to deinstall an Oracle Grid Infrastructure home, Oracle Real Application Clusters (Oracle RAC) database homes, single-instance databases, database clients, and Oracle Grid Infrastructure for standalone server homes.

The deinstall tool is also installed by default when installing an Oracle 11.2 home. The deinstall tool has built-in intelligence to check for installed software, and access the files that it needs to complete the deinstallation. If the tool detects missing files, it prompts you to download a standalone version of the deinstall tool to complete a deinstallation successfully.

More details are available in:

http://download.oracle.com/docs/cd/E11882_01/em.112/e12255/oui5_cluster_environment.htm#sthref587

How To Deinstall Oracle Home In 11GR2 ?

How To Deinstall Oracle Home In 11GR2?
=======================================

Oracle Deinstall Utility - 11g R2

Starting with Oracle Database 11g Release 2 (11.2.0.1.0), a separate deinstallation and deconfiguration tool is available as a separate download. The deinstall tool is for deinstalling the 11.2 software. This tool can be used to deinstall an Oracle Grid Infrastructure home, Oracle Real Application Clusters (Oracle RAC) database homes, single-instance databases, database clients, and Oracle Grid Infrastructure for standalone server homes.

The deinstall tool is also installed by default when installing an Oracle 11.2 home. The deinstall tool has built-in intelligence to check for installed software, and access the files that it needs to complete the deinstallation. If the tool detects missing files, it prompts you to download a standalone version of the deinstall tool to complete a deinstallation successfully.

More details are available in:

http://download.oracle.com/docs/cd/E11882_01/em.112/e12255/oui5_cluster_environment.htm#sthref587

Sunday, March 27, 2011

In sun Solaris password length by default 8 character

In sun Solaris password length by default 8 character
====================================================


i have found a question. the question is bellows

How to set root password to 13 Character.
As it is 8 Characters now i want to give 13 chartectes for the root password.
I had given 13 Characters , but it is checking for 8 Character and after 8 Character
if i give wrong also passwd its logging in.. pl help me out how to fix
this on solaris sunfire systems.


Solution
=========

Using Solaris with the default configuration, your password can’t be more than 8 characters. or your password ignore the characters after 8 digit.

This is due to the default crypt algorythm used to store passwords in the /etc/shadow file.

This crypt algorythm can be changed. There are a few algorythms available by default in Solaris. To know about them, have a look at /etc/security/crypt.conf

root# !% cat /etc/security/crypt.conf
#
# Copyright 2002 Sun Microsystems, Inc. All rights reserved.
# Use is subject to license terms.
#
#ident "@(#)crypt.conf 1.1 02/06/07 SMI"
#
# The algorithm name __unix__ is reserved.

1 crypt_bsdmd5.so.1
2a crypt_bsdbf.so.1
md5 crypt_sunmd5.so.1



You may want to choose md5 to enhance the security of passwords on the server.

To do so, edit the /etc/security/policy.conf file
and modify the CRYPT_DEFAULT parameter

from

CRYPT_DEFAULT=__unix__

to

CRYPT_DEFAULT=md5