Installing Oracle Configuration Manager (Disconnect Mode )

1.Oracle Configuration Manager Installation
After downloading the OCR zip file from Metalink

1.1-Log in as the ORACLE_HOME owner.
1.2-Unzip the Oracle Configuration Manager into the ORACLE_HOME directory
unzip -d $ORACLE_HOME ccr-Production-10.2.5.0.0--.zip
1.3-setup ccrcd
$ORACLE_HOME/ccr/binsetupCCR [-s] [-d -p ] [] [][]
setupCCR -s -d 15626546 serdar.turgut@fortis.com.tr TR

• The -s parameter indicates the acceptance of the Oracle Configuration Manager license agreement
• If you use the -d parameter, Oracle Configuration Manager will be installed in Disconnected Mode.
• The -d and -p parameters are mutually exclusive and cannot be used togetherCSI is the Customer Support Identifier.
• Your CSI number can be found in theProfile section of Oracle MetaLink under the Licenses link
• Country-Code is the customer’s country code (TR for Turkey)
• The -p parameter is used to specify the proxy server information needed to connect to the Internet
  Note: If you are using a Unix system and you do not have access to CRON, you may encounter problems when installing Oracle Configuration Manager on Unix systems. To work around this problem, set the environment variable CCR_DISABLE_CRON_ENTRY to any value and retry the installation. You need to do this only if CRONTAB is a restricted operation

2.Oracle Configuration Manager database installation

2.1.If you run on 9.2 database you must add $ORACLE_HOME/ccr/state directory to UTL_FILE_DIR parameter and restart it

2.2.Configure the database by running installCCRSQL.sh (Unix) or installCCRSQL.exe (Windows)

$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s -r -p

By default, the connection to the database is through OS authentication, "/as sysdba." To specify a different SYSDBA user and password, you can use these options

$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s CRCDT

Note:If the Oracle Configuration Manager account already exists, when you run the installCCRSQL.sh script, it will be dropped and re-created.
Note: If you are upgrading from a 9.x database version to a 10.x version, you must run the installCCRSQL.sh script again to record the upgraded version.

2.3 Additional Step for E-Business Suites
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh ebs_collectconfig -u

2.4 Additional Step for Oracle Enterprise Manager Grid Control$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectemrep

3.Uninstalling Oracle Configuration Manager

3.1-If the ORACLE_HOME directory contains a database, remove the Oracle
Configuration Manager user and the associated objects from the database as
follows:
SQL> @ccr/admin/scripts/dropocm.sql;
3.2. If the database is a repository for the Oracle E-Business Suite, log in to the
database as an SYSDBA user and remove the additional objects from the database
as follows:
SQL> @ccr/admin/scripts/ebs dropccr.sql
3.3. If the database is a repository for Oracle Grid Control, log in to the database as the
SYSMAN user and remove the additional objects from the database as follows:
SQL> @ccr/admin/scripts/dropemrep_collect.sql;
3.4. To stop the Scheduler and remove the service or the crontab entry, enter the
following command:
$ORACLE_HOME/ccr/bin/deployPackages -d $ORACLE_HOME/ccr/inventory/core.jar
3.5. Delete the ccr directory by entering the following command:
$rm -rf $ORACLE_HOME/ccr (On Unix)
>rmdir /s/q %ORACLE_HOME%\ccr (On Windows)

4.Collecting Configuration Data (Disconnected Mode)

4.1Collect data

$ORACLE_HOME/ccr/bin/emCCR collect

4.2 Upload data

we can upload $ORACLE_HOME/ccr/state/upload/ocmconfig.jar in SR

also a set of files are generated and stored in the "$ORACLE_HOME/ccr/state/review" directory. These files are XML and have a style sheet

Can one generate HTML reports from SQL*Plus?

One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON. This can be done by specifying -MARKUP "HTML ON" from command line, or with the "SET MARKUP HTML ON" command. Look at this example SQL Script:
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

You can deploy this file on your web site or edit it in an HTML editor (like FrontPage or Dreamweaver). Another good idea is to develop a CSS to present the data more elegantly. One can also embed HTML tags in the select statement to create hyperlinks and add more HTML features.
Ref:http://www.orafaq.com/faqplus.htm

Oracle users default password

Check the Oracle users default passwords(check_default_password.sql)

1. version <11g br="">select username "User(s) with Default Password!", account_status "Status"
   from dba_users where password in(
   'E066D214D5421CCC', -- dbsnmp
   '24ABAB8B06281B4C', -- ctxsys
   '72979A94BAD2AF80', -- mdsys
   '9AAEB2214DCC9A31', -- mdsys
   'C252E8FA117AF049', -- odm
   'A7A32CD03D3CE8D5', -- odm_mtr
   '88A2B2C183431F00', -- ordplugins
   '7EFA02EC7EA6B86F', -- ordsys
   '9B616F5489F90AD7', -- ordcommon
   '4A3BA55E08595C81', -- outln
   'F894844C34402B67', -- scott
   '3F9FBD883D787341', -- wk_proxy
   '79DF7A1BD138CF11', -- wk_sys
   '7C9BA362F8314299', -- wmsys
   '88D8364765FCE6AF', -- xdb
   'F9DA8977092B7B81', -- tracesvr
   '9300C0977D7DC75E', -- oas_public
   'A97282CE3D94E29E', -- websys
   'AC9700FD3F1410EB', -- lbacsys
   'E7B5D92911C831E1', -- rman
   'AC98877DE1297365', -- perfstat
   '66F4EF5650C20355', -- exfsys
   '84B8CBCA4D477FA3', -- si_informtn_schema
   'D4C5016086B2DC6A', -- sys
   '5638228DAF52805F', -- sys
   'D4DF7931AB130E37') -- system
   /
2. versin  >=11g
   select username "User(s) with Default Password!", account_status "Status" from dba_users where  username in (select  username from  dba_users_with_defpwd)
   /

Oracle Database Listener Security Guide

STEP 1 – SET THE LISTENER PASSWORD
Set the Listener password to stop most attacks and security issues. This is usually a simple process. You should set the password using lsnrctl, which will encrypt the password stored in listener.ora. Setting the password manually in listener.ora using the PASSWORDS_ parameter will result in the password being stored in cleartext.
LSNRCTL> set current_listener
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
LSNRCTL> set password
Password:
LSNRCTL> save_config
Check the listener.ora file to see if there is now a parameter PASSWORDS_. It is important to remember that the actual encrypted password can be used in place of the actual password prior to Oracle 10g.

STEP 2 – TURN ON LOGGING [MANDATORY]
Turn on logging for all listeners in order to capture Listener commands and brute force password attacks.
LSNRCTL> set current_listener
LSNRCTL> set password
Password:
LSNRCTL> set log_directory /network/admin
LSNRCTL> set log_file .log
LSNRCTL> set log_status on
LSNRCTL> save_config

STEP 3 – SET ADMIN_RESTRICTIONS IN LISTENER.ORA [MANDATORY]

All runtime modifications to the Listener can be disabled by setting the parameter ADMIN_RESTRICTIONS_ to ON in the listener.ora file. This parameter stops all set commands from being executed either locally or remotely. All changes must be made manually to the listener.ora file.

LISTENER.ORA
ADMIN_RESTRICTIONS_ = ON

Restart the listener using the reload command in lsnrctl for this change to take effect. Any future changes must be made in the listener.ora file, not using set commands in lsnrctl. After making any changes to the listener.ora file, use the reload command (or stop and start) in lsnrctl.

if Local OS Authentication is disabled also It must be set for 10g listener otherwise there is no need .

STEP 4 – APPLY LISTENER SECURITY PATCHES [MANDATORY]
Apply at least the January 2006 Critical Patch Update for the latest Listener security patches (as of March 2007). Critical Patch Updates are cumulative, therefore, the latest patch will contain all previous security patches for the Listener.

STEP 5 – BLOCK SQL*NET ON FIREWALLS
SQL*Net traffic should not be allowed to pass through firewalls unless absolutely necessary. Firewall filters should be designed to only allow SQL*Net traffic from known application and web servers. SQL*Net traffic from application servers in the DMZ should be permitted only to access specific database servers.

Few applications require direct SQL*Net connections to a database from the Internet. SQL*Net performs poorly over high latency networks, thus is seldom used in Internet applications. If applications do require direct SQL*Net access, configure firewall filters based on a specific host and port number.

STEP 6 – SECURE THE $TNS_ADMIN DIRECTORY [MANDATORY]

The Listener password is stored in the listener.ora file. Manually editing the file, the password can easily be removed or changed. If the password was manually added to the file, it is stored in clear text. When added through lsnrctl, it will be stored as an encrypted string.

The permissions on the listener.ora, sqlnet.ora, and protocol.ora files in the $TNS_ADMIN directory (usually $ORACLE_HOME/network/admin) should be read/write/execute for only the primary oracle account and no permissions for any other account (for UNIX and Linux 0600). The tnsnames.ora file permissions should be set to 0644 on UNIX and Linux.

chmod 644 $ORACLE_HOME/network/admin/tnsnames.ora
chmod 600 $ORACLE_HOME/network/admin


STEP 7 – SECURE TNSLSNR AND LSNRCTL [OPTIONAL]
The tnslsnr and lsnrctl executables in the $ORACLE_HOME/bin directory should be protected and file permissions should be set to 0751 on UNIX and Linux as recommended by Oracle. It is possible to change the file permissions to 0700 which would be more secure, although this should be thoroughly tested in your environment.

chmod 700 $ORACLE_HOME/bin/tnslsnr
chmod 700 $ORACLE_HOME/bin/lsnrctl


STEP 8 – REMOVE UNUSED SERVICES [MANDATORY]
Many default installations have a listener entry for PL/SQL External Procedures (ExtProc). The entry name is usually ExtProc or PLSExtProc. Often ExtProc is installed by default, but is not used. Check with your application development team or application documentation to determine if ExtProc is used.
If ExtProc is not used, remove it from the listener.ora file. There are several exploits directed at ExtProc.
Since listener.ora files are sometimes copied between instances, they may contain old and unused entries. Check all the other services to determine if they are used. Delete any services not actively used.

STEP 9 – CHANGE THE TNS PORT NUMBER FROM 1521 [OPTIONAL]
In order to help stop automated attacks and detection of the Listener in networks, the default NTS port number should be changed from 1521 to a port outside of the 1521-1550 and 1600-1699 ranges. This will provide only minimal additional security through obscurity, but may thwart an automated attack or simply scanning for Oracle Databases on port 1521.

The port number can be changed using Oracle Net Manager (netmgr) or editing the listener.ora file directly. All tnsnames.ora files on the database server and any clients must be updated to reflect the change in the port number. The database initialization parameter LOCAL_LISTENER must be set so that the database is able to dynamically register with the Listener. See Metalink Note ID 359277.1 "Changing Default Listener Port Number" for more information.

STEP 10 – SETUP VALID NODE CHECKING [OPTIONAL]

Depending on the type of application and network configuration, valid node checking can be a powerful tool to restrict most traffic from the Listener. Most web applications only require access to the Listener from the application servers and a limited number of clients for administration.

The simplest method to determine valid IP addresses for node checking is through database auditing. We recommended you always have session level auditing enabled.
For Oracle 9i/10g, the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file. For Oracle 8/8i, the lines are added to the $ORACLE_HOME/network/admin/protocol.ora file.

tcp.validnode_checking = yes
tcp.invited_nodes = (x.x.x.x name, x.x.x.x name)
tcp.excluded_nodes=( x.x.x.x name, x.x.x.x name)

Include either the invited_nodes or excluded_nodes, but do not use both. Wildcards, subnets, etc. are not valid, only individual IP addresses or host names are allowed. For more sophisticated checking, use Oracle Connection Manager.

The Listener must be stopped and started for valid node checking to become active. There is no hard limit on the number of nodes that can be included, but for a large number of entries using Oracle Connection Manager may be a better solution. If many clients require direct SQL*Net access to the database, it is often difficult to use valid node checking due to constantly changing network configurations.


STEP 11 – MONITOR THE LOGFILE [OPTIONAL]

The logfile in Step 2 may contain TNS-01169, TNS-01189, TNS-01190, or TNS-12508 errors, which may signify attacks or inappropriate activity. Using a simple shell script or management tools, monitor the logfile and generate an alert whenever these errors reaches are encountered.

Oracle Database Listener Concepts

The Listener is comprised of two binaries: (1) tnslsnr which is the Listener itself and (2) the Listener Control Utility (lsnrctl) which is used to administer the Listener on the server or remotely

The relevant files for the Listener are as follows

$ORACLE_HOME/bin/lsnrctl Listener control program
$ORACLE_HOME/network/admin/listener.ora Configuration file for the Listener
$ORACLE_HOME/network/admin/sqlnet.ora Configuration file for the Listener
$ORACLE_HOME/bin/tnslnsr Server Listener process

Listener Modes :The Listener can be configured in one of three modes (as configured in listener.ora) –
· Database Provides network access to an Oracle database instance
· PLSExtProc Method for PL/SQL packages to access operating system executables
· Executable Provides network access to operating system executables

LISTENER REMOTE MANAGEMENT

DBAs are not aware that the Listener in Oracle 8i/9i can be remotely managed using lsnrctl or a similar program from a remote machine. The Oracle 10g Listener by default cannot be remotely managed unless local OS authentication is disabled.

1-The simplest method to remotely issue commands to a Listener is to use lsnrctl with command-line parameters as such
– lsnrctl :
– lsnrctl status 192.168.1.100
– lsnrctl stop 192.168.1.100:1522
2- To set up a computer to remotely administer a Listener
- Configure the local listener.ora to resolve to the remote Listener
= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = )(Port = ) ) )
- Start from the command line lsnrctl and specify the Listener name
lsnrctl
LSNRCTL> set current_listener

The following are some examples of possible attacks against an Oracle 8i/9i Listener which has a default configuration and is not properly secured.

Execute SQL as DBA: It is possible to overwrite the ORACLE_HOME/sqlplus/admin/glogin.sql by changing the location of the log file and then sending SQL statements in Listener commands to the file. When the SQL*Plus is executed locally on the server (usually by a DBA), then the SQL statements are executed during the SQL*Plus startup.
Allow Login via rlogin:The Listener log can be used to overwrite an .rlogin file with additional host information, thus allowing an attacker access the server using rlogin.
Denial of Service (DoS) An attacker is able to –
· Stop the Listener
· Set a Listener password so that the Listener ca not be started without a password, although the DBA simply has to edit the listener.ora file and remove the password line
Denial of Service (DoS):Undermine the stability of the server and database by overwriting arbitrary files by changing the directory and filename of the log and trace files to any location accessible by the operating system account that owns the database (usually "oracle").
Denial of Service (DoS) Setting the Listener trace level to "support" may cause performance degradation on a heavily accessed database server.
Information Disclosure:Obtain detailed information on the Listener configuration and database installation such as –
· Database Service Names (e.g., SIDs)
· Database and Listener versions
· Log and trace settings including directory and file names
· Security settings
· Database server operating system
· Oracle environmental variables (ORACLE_HOME, etc.)


LISTENER EXPLOITS

1- If a password is not set on the Listener, someone who knows just a hostname and port number (default port is 1521) has full control over the Listener
2- Oracle Security Alerts:
Check the Oracle security alerts
3- Brute Forcing Listener Password:
The Listener password can easily be brute forced, since there is no automatic lockout facility and no requirements for strong passwords. Repetitive set password commands can be sent to the listener using a hacking program. If logging is enabled (set log_status on), invalid password attempts will appear with an error code of TNS-01169.
4- Passwords Transmitted in Clear Text:
Using the set password command remotely will transmit the password across the network in clear text with every command. If encryption is setup for the listener using the Advanced Security Option (ASO), then the passwords will be sent encrypted across the network. The change password command does encrypt the password when the lsnrctl program is used

ORACLE LISTENER PASSWORD

The password for the Listener is stored in the listener.ora file. If the PASSWORDS_ parameter is manually set, then the password remains in plain-text. If set using lsnrctl and the change_password command, then the password is encrypted as 8-byte string. Unlike the database, the Listener password is case-sensitive.
Prior to Oracle 10g, the encrypted password string could be substituted for the actual password when issuing the set password command. This is useful in executing scripts to stop the Listener. If a password is set for the Oracle 10g Listener, scripts must use the actual password rather than the encrypted string.

If the Listener password is set to "mypassword", then the listener.ora file will have the encrypted string. The following lsnrctl commands using either the plain-text password or encrypted string will both work prior to Oracle 10g.
Listener.ora
PASSWORDS_LISTENER = F4BAA4A006C26134
LSNRCTL> set password
Password: mypassword
LSNRCTL> set password Password: F4BAA4A006C26134

ORACLE 10G LOCAL OS AUTHENTICATION
A major change to Listener security in Oracle 10g (10.1 and 10.2) was the introduction of Local OS Authentication. By default, the Listener cannot be remotely managed and can only be managed locally by the owner of the tnslsnr process (usually oracle).

If another operating system user attempts to manage the Listener, the following message will be displayed in the Listener log file –
TNS-01190: The user is not authorized to execute the requested listener command

If someone attempts to managed the Listener remotely, the following message will be displayed in the Listener log file –

TNS-01189: The listener could not authenticate the user

Local OS Authentication can be disabled by setting the LOCAL_OS_AUTHENTICATION_ parameter in listener.ora file as such –

LOCAL_OS_AUTHENTICATION_ = OFF

When Local OS Authentication is disabled, the Listener behaves exactly as in Oracle 8i/9i. Thus, it should have a password set and ADMIN_RESTRICTIONS set to On.

LOGGING

By default, logging is not enabled (LOG_STATUS=OFF). When logging is enabled, the default directory is $ORACLE_HOME/network/admin and the log file default is .log. The logfile contains a history of listener commands issued both locally and remotely. The logfile shows a timestamp, command issued, and result code. If an Oracle error is returned, it will include the error message. The logfile does not contain passwords or other significant information. The logfile does NOT show any information related to IP address, client name, or other identifying information for remote connections. It may show the client’s current user name, but this can easily be spoofed or not provided.

The following are TNS errors that may signify an attack or inappropriate activity

TNS-01169:An attempt was made to issue a command, but a password is set
TNS-01189:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener remotely or locally by another user
TNS-01190:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener locally by another user
TNS-12508:This error occurs when an invalid command is issue (e.g., statusx instead of status) or when a set command is issued and ADMIN_RESTRICTIONS is set to no.

"Idle transactions" or "Open transactions" waiting for a long time (Long Running transactions)

"idle in transaction" means that someone did a "begin", but didn't issuea "commit" or "rollback" yet. It is often a sign of bad application design and you should contact the application developers. Since open transactions may hold locks on tables, the whole application may stop unexpectedly if transactions are left open.


Another possibility is that you've just got a huge workload, e.g. lots of concurrent access to the application so that it has to perform a lot of work, but then you should see SELECT/INSERT/UPDATE/etc. as well, not only "idle in transaction".


Also idle transactions may cause "ora-01555 snapshot too old" errors.So we must find the cause of long running transactions


· We can find the idle transaction with following sql
SELECT
s.username,s.sid,s.serial#,t.start_time,
trunc((sysdate-to_date(T.start_time,'MM/DD/YY HH24:MI:SS'))*24*60) idle_time_in_min, s.status
FROM V$session s, V$transaction t, V$rollstat r
WHERE s.saddr=t.ses_addr
and t.xidusn=r.usn
-- and (s.username like 'DS%' or s.username like 'TF%' or s.username ='DBQUERY')
and trunc((sysdate-to_date(T.start_time,'MM/DD/YY HH24:MI:SS'))*24*60) >10
order by t.start_time;



· also we can write a script that will detect idle_transactions(status<>ACTIVE) and kill then Since killing a transation is a dangerous operation.You must define the users and their spesific idle time accurately .It can run from crontab




#!/bin/ksh

hosttype=`uname`
if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile


MAX_DS_IDLE_TIME=60
MAX_EBANK_IDLE_TIME=500
MAX_DB_IDLE_TIME=500
MAX_BS_IDLE_TIME=30
OUT_FILE=$HOME/mntdir/kill_discoverer_transaction.out
OUT_FILE2=$HOME/mntdir/kill_discoverer_transaction.out2
LOG_FILE=$HOME/logdir/kill_discoverer_transaction.log

sqlplus -s / > $OUT_FILE2 <set arraysize 1
set linesize 1500
set pagesize 0
select 'MAKEGREP',s.sid'#'s.serial#'#'s.username'#' TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM\/DD\/YY HH24:MI:SS'))*24*60)
FROM V\$session s, V\$transaction t, V\$rollstat r
WHERE s.saddr=t.ses_addr AND t.xidusn=r.usn and
(((s.username like 'DS%' or s.username like 'TF%' ) AND TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_DS_IDLE_TIME) or
(s.username='EBANK_N' and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_EBANK_IDLE_TIME) or
(s.username like 'DB%' and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_DB_IDLE_TIME ) or
((s.username='DBQUERY' or s.username like 'BS%') and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_BS_IDLE_TIME ))
and s.status in ('INACTIVE','SNIPED');
exit;
EOF



cat $OUT_FILE2grep MAKEGREPawk {'print $2'}>$OUT_FILE

date >> $LOG_FILE
for l in `cat $OUT_FILE `
do
SID=`echo $lcut -d# -f1`
SERIAL=`echo $lcut -d# -f2`
USERNAME=`echo $lcut -d# -f3`
IDLE_TIME=`echo $lcut -d# -f4`

echo "$USERNAME ($SID,$SERIAL) is idle for $IDLE_TIME munites so It will be killed "tee -a $LOG_FILE
sqlplus -s / >>$LOG_FILE << EOF
ALTER SYSTEM KILL SESSION '$SID,$SERIAL';
exit;
EOF
done

Installing and checking the Automatic Undo Management (AUM)

DBAs have the choice to manage rollback segments as they used to do under versions Oracle7, Oracle8, and Oracle8i, or to let the RDBMS do it.

1-How to enable AUM

a)Create undo tablespace

create undo tablespace UNDORBS datafile '/data01/undorbs.dbf' size 2048m;

(@undo1.sql)
select TABLESPACE_NAME, CONTENTS,
EXTENT_MANAGEMENT, ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces where contents='UNDO';


b) Change init.ora (or spfile )
-Set these parameters

undo_management = AUTO
undo_retention = 900 #15 minutes
undo_tablespace = UNDORBS

-Unset rollback_segments

c)Restart database


2-Determine the size of undo tablespace and tuned undo retention

· requiered undo for UNDO_RETENTION now
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

The following query calculates the number of bytes needed: (@undo3)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));


· tuned undoretention and max query (@undo2)
select
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') starttime,
to_char(end_time,'yyyy-mm-dd hh24:mi:ss') endtime,
undoblks, maxquerylen maxqrylen,maxqueryid,
tuned_undoretention from v$undostat
order by begin_time;

· To gurantee undo_retention (e.g 1440 sec) how much undo rbs is needed in KB (@undo6)
SELECT dbms_undo_adv.required_undo_size(1440) FROM dual

3-Undo advisor

· Current Undo Info (@undo5)
set serveroutput on
DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line('UNDO Tablespace is: ' tsn);
dbms_output.put_line('UNDO Tablespace size is: ' TO_CHAR(tss));

IF aex THEN
dbms_output.put_line('Undo Autoextend is set to: TRUE');
ELSE
dbms_output.put_line('Undo Autoextend is set to: FALSE');
END IF;

dbms_output.put_line('Undo Retention is: ' TO_CHAR(unr));

IF rgt THEN
dbms_output.put_line('Undo Guarantee is set to: TRUE');
ELSE
dbms_output.put_line('Undo Guarantee is set to: FALSE');
END IF;
END;

· To gurantee undo_retention (e.g 1440 sec) how much undo rbs is needed in KB after running with current undo (@undo6)
SELECT dbms_undo_adv.required_undo_size(1440) FROM dual;
· Best possible retension with current size of Undo(@undo7)
SELECT dbms_undo_adv.best_possible_retention FROM dual;

4-Useful tips for AUM

· You cannot use UNDO tablespaces for other purposes than UNDO SEGMENTS and cannot do any operation on system generated undo segments
· Only one UNDO tablespace can be used at the instance level
alter system set undo_tablespace=undo_rbs1;

· If you choose to use AUM, you have no chance to manage any undo or rollback even on an non UNDO tablespace.

· For Real Application Clusters environments.
a)All instances within Real Application Cluster environments must run in the same undo mode.
b)Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter file.
c)Set the UNDO_TABLESPACE parameter to assign the appropriate undo tablespace to each respective instance. Each instance requires its own undo
tablespace. If you do not set the UNDO_TABLESPACE parameter, each instance uses the first available undo tablespace


· The undo segments in AUM(@undo4)
select USN,RSSIZE,HWMSIZE,OPTSIZE,SHRINKS,segment_name from v$rollstat,dba_rollback_segs where usn=segment_id;

· Automatic tuning will help to avoid ORA-01555, but if your UNDO tablespace has autoextend off, then you might get into a situation where active DML needs more space--not reusing expired UNDO segments. The database will be under space pressure and Oracle will give higher priority to finishing the DML, and not queries. In that case, users might get ORA-01555 errors, and for this special scenario, you will see the following entry in alert log

“system is under space pressure, now=XXXXXXX”

Oracle Supplemental Logging

Supplemental Logging enhancements are aimed at improving streams and other data sharing facilities.(e.g Logminer) .Includes additional information in redo stream

A-Database Supplemental Logging

Minimal supplemental logging can be enabled using:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Minimal supplemental logging can be disabled using:
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


Database supplement logging can be enabled :

1-For all columns :This option specifies that when a row is updated, all the columns of that row (except for columns of type LOB, LONG, LONG RAW, and user-defined types) are placed in the redo log file.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

All columns are included with the exception of:LONG,LOB,LONG RAW,Abstract Data Types,Collections

2-For primary key columns
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

3-For unique columns:This option causes the Oracle database to place all columns of a row's foreign key in the redo log file, if any column belonging to the foreign key is modified

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

4-For foreign key columns
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;


When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


Database supplement logging can be disabled
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To monitor database level supplemental logging:
SELECT
supplemental_log_data_min,
supplemental_log_data_pk,
supplemental_log_data_ui,
supplemental_log_data_fk,
supplemental_log_data_all
FROM v$database;


B-Log Groups:Table supplemental logging specifies, at the table level, which columns are to be supplementally logged. You can use identification key logging or user-defined conditional and unconditional supplemental log groups to log supplemental information.


Implemented as constraints.If no name specified for log group then system constraint name will be allocated e.g SYS_C005223
Log Groups can be

1-Unconditional Supplemental Log Groups : The before-images of specified columns are logged any time a row is updated, regardless of whether the update affected any of the specified columns. This can be referred to as an ALWAYS log group

To specify an unconditional supplemental log group for primary key column(s):
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
To specify an unconditional supplemental log group that includes all table columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
To specify an unconditional supplemental log group that includes selected columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3) ALWAYS;




2-Conditional Supplemental Log Groups - The before-images of all specified columns are logged only if at least one of the columns in the log group is updated

To specify a conditional supplemental log group for unique key column(s) and/or bitmap index column(s):
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
To specify a conditional supplemental log group that includes all foreign key columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
To specify a conditional supplemental log group that includes selected columns:
ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);



In Oracle 10.2, minimal supplemental logging must be enabled at database level before supplemental logging can be enabled at table level




To drop a supplemental log group:
ALTER TABLE t1 DROP SUPPLEMENTAL LOG GROUP t1_g1;
To drop supplemental logging of data use:
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Supplemental Logging views: DBA_LOG_GROUPS,DBA_LOG_GROUP_COLUMNS

How to Deinstall and Reinstall XML Database (XDB)

A-REMOVAL STEPS

1. Shutdown and restart the database
2. Connect as sysdba and run the catnoqm.sql script
SQL> set echo on
SQL> spool xdb_removal.log
SQL> @?/rdbms/admin/catnoqm.sql
3.minumums
shared_pool_size =150 MB
java_pool_size =150 MB
and
XDB tablespace must have 150m
5. Shutdown the database immediate, and startup the database normal

B-INSTALL STEPS

1. Connect as sysdba and run the catqm.sql script

The catqm.sql script requires the following parameters be passed to it when
run:
A. XDB user password
B. XDB user default tablespace
C. XDB user temporary tablespace
Therefore the syntax to run catqm.sql will be:
SQL> catqm.sql A B C
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

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';

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

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

How to disable use of Flash Recovery Area for Archivelogs and Backups

1-Archive to another file system location in addition to the Flash Recovery Area

SQL>create pfile=’init.ora’ from spfile;

add the following line to the init.ora:

log_archive_dest_n=’’ eg

log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\oradata\V102\Arch'

Restart the instance using the amended pfile and recreate the spfile:

SQL>startup pfile=’init.ora’;

SQL>create spfile from pfile;

2-Archive to another file system location instead of the Flash Recovery Area

Create a parameter file (as above)

Add the following line to the init.ora:

log_archive_dest_n (as above)

Remove the following parameter in the init.ora:

log_archive_dest_10

Restart the instance using the amended pfile and recreate the spfile

3-Avoid use of the Flash Recovery Area altogether (not recommended)
Create a parameter file (as above)

Add the following line to the init.ora:

log_archive_dest_n (as above) or

log_archive_dest=''

Remove the following parameter in the init.ora:

log_archive_dest_10

db_recovery_file_dest
db_recovery_file_dest_size

Restart the instance using the amended pfile and recreate the spfile.


Ref:Metalink Note:297397.1

Tune Checkpoint

Checkpoint

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk

Oracle writes the dirty buffers to disk only on certain conditions

-A shadow process must scan more than one-quarter of the db_block_buffer parameter.
-Every three seconds.
-When a checkpoint is produced.

A checkpoint is realized when
-Redo switches
-when (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks) is written to redo logfile
-LOG_CHECKPOINT_TIMEOUT is reached
-ALTER SYSTEM CHECKPOINT command


A checkpoint performs the following three operations:
-Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.
-The latest SCN is written (updated) into the datafile header.
-The latest SCN is also written to the controlfiles.

Tuning checkpoints involves four key initialization parameters

- FAST_START_MTTR_TARGET
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINTS_TO_ALERT



SELECT SUBSTR(NAME,1,30) nme , SUBSTR(VALUE,1,50) value
from v$parameter
where name in ('log_checkpoint_interval','log_checkpoint_timeout','fast_start_io_target','fast_start_mttr_target');





FAST_START_MTTR_TARGET :Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. Based on internal statistics, incremental checkpoint automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET.
You can select V$INSTANCE_RECOVERY the status of the estimated and target MTTR

select ESTIMATED_MTTR,TARGET_MTTR from V$INSTANCE_RECOVERY;

from above query we can decide the initial fast_start_mttr_target value

alter system set log_checkpoint_interval=0 scope=both;
alter system set log_checkpoint_timeout=0 scope=both;
alter system set fast_start_io_target=0 scope=both;
alter system set fast_start_mttr_target=30 scope=both; #30 is example
alter system set log_checkpoints_to_alert = true;

Then query the v$MTTR_TARGET_ADVICE in order to find optimal fast_start_mttr_target


Note:When you enable fast-start checkpointing, remove or disable (set to 0)
the following initialization parameters:
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- FAST_START_IO_TARGET


LOG_CHECKPOINT_TIMEOUT:LOG_CHECKPOINT_TIMEOUT specifies the amount of time, in seconds, that has passed since the incremental checkpoint at the position where the last write to the redo log occurred.

LOG_CHECKPOINT_INTERVAL :specifies the maximum number of redo blocks the incremental checkpoint target should lag the current log tail.
If FAST_START_MTTR_TARGET is specified, LOG_CHECKPOINT_INTERVAL should not be set or set to 0.
On most Unix systems the operating system block size is 512 bytes. This means that setting LOG_CHECKPOINT_INTERVAL to a value of 10,000 would
mean the incremental checkpoint target should not lag the current log tail by more than 5,120,000 (5M) bytes. . If the size of your redo log is 20M, you are taking 4 checkpoints for each log.

LOG_CHECKPOINTS_TO_ALERT
LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

REDO LOG NUMBER AND SIZE
A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint
so look alert.log or guery v$log_history the occurance of log switch
If redo logs switch every 3 minutes, you will see performance degradation. This indicates the redo logs are not sized large enough to efficiently handle the transaction load.

CHECK ERROR MESSAGES IN ALERT.LOG
Check “Cannot allocate new log” and “Checkpoint not complete” messages in alert.log .
This situation may be encountered if DBWR writes too slowly, or if a log switch happens before the log is completely full,
or if log file sizes are too small.
When the database waits on checkpoints,redo generation is stopped until the log switch is done.
tune the log number and size

SYSTEM WAITS
we can query the system waits from v_$system_event or from statspacks or from 10g awr reports if log switch errors happed

select
substr(e.event, 1, 40) event,
e.time_waited,
e.time_waited / decode(
e.event,
'latch free', e.total_waits,
decode(
e.total_waits - e.total_timeouts,
0, 1,
e.total_waits - e.total_timeouts
)
) average_wait
from
sys.v_$system_event e
where event like 'log file switch%';

How to convert a tablespace to ASSM(Automatic Segment Space Managment)

The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace.
You cannot change the segment space management mode of a tablespace.

alert for flash_recovery_area

If you have take the following error in alert.log then
You have following choices to free up space from flash recovery area:1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
This means you do not have enough space in db_recovery_file_dest_sizecheck V$RECOVERY_FILE_DEST
KTP5>SELECT * FROM V$RECOVERY_FILE_DEST;
NAME--------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/kondor2/oracle/oraInventory/flash_recovery_area 2,147E+09 2,147E+09 0 85
also we can look to dba_outstanding_alerts
KTP5>SELECT object_type, message_type, message_level, 2 reason, suggested_action 3 FROM dba_outstanding_alerts;

OBJECT_TYPE MESSAGE_TYPE MESSAGE_LEVEL
---------------------------------------------------------------- ------------ -------------
REASON
----------------------------------------------------------------------------------------------------
SUGGESTED_ACTION----------------------------------------------------------------------------------------------------
RECOVERY AREA Warning 1db_recovery_file_dest_size of 2147483648 bytes is 97.37% used and has 56553472 remaining bytes availYou have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.

so we can increase DB_RECOVERY_FILE_DEST_SIZE
KTP5>alter system set DB_RECOVERY_FILE_DEST_SIZE=3g scope=both;

You may also need to consider changing your backup retention policy rman
RMAN> connect target
connected to target database: KTP5 (DBID=4032373710)
RMAN> configure retention policy to recovery window of 7 days;
RMAN> delete expired backup;

How to Enable and Disable Automatic Statistics Collection in 10G

The Automatic Statistics collection feature is enabled by default in 10G.
You can verify this by checking the following :

SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
SCHEDULED

To Disable the automatic statistics collection in 10G , you can execute the following procedure

SQL >Exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

In order to enable statistics collection you will follow following steps

SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

STATE
---------------
DISABLED

SQL> Exec DBMS_SCHEDULER.enable('GATHER_STATS_JOB');

ORACLE TNS DEFAULT PORTS

1521 :
Default port for the TNS Listener. This port number may change in the future as Oracle has officially registered ports 2483 and 2484 (SSL).
1522 – 1540:
Commonly used ports for the TNS Listener 1575 Default port for the Oracle Names Server
1630:
Default port for the Oracle Connection Manager – client connections
1830:
Default port for the Oracle Connection Manager – administrative connections
2481:
Default port for Oracle JServer/JVM listener 2482 Default port for Oracle JServer/JVM listener using SSL
2483:
New officially registered port for the TNS Listener 2484 New officially registered port for the TNS Listener using SSL

Protect the Listener by password

We can set password by two ways

a) Cleartext Password:
Add PASSWORDS_< your_listener_name > entry to your existing listener.ora file and restart the listener
PASSWORDS_listener1 = (p1,p2)

Example of a listener stop operation

LSNRCTL > set current_listener listener1
LSNRCTL > set password p1
LSNRCTL > stop

b)Encrypted Password
- Comment out PASSWORD_ line if cleartext password is set.
- Restart listener.
- Run lsnrctl
LSNRCTL > set current_listener LSNRCTL > set save_config_on_stop on
LSNRCTL > change_password
Old password: < enter >
New password: < enter_your_password >
Reenter new password: < reenter_your_password >
Example
LSNRCTL > change_password
Old password: < enter >
New password: e1
Reenter new password: e1
Just hit < enter > key for old password since no previuos password is set. The passwords you entered will not be echoed.

- Stop the listener

LSNRCTL > set password
Password: < enter_your_password_here >
LSNRCTL > stop

Example of a listener process
LSNRCTL > set password
Password: e1
LSNRCTL > stop

- Check your listener.ora file if PASSWORDS_< listener1 > exists

How to see Oracle hidden parameters

we can see the Oracle Hiden parameters with the following sql .
Don't change them without the advice of Oracle Support

SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"
FROM x$ksppi a,x$ksppcv b,x$ksppsv c
WHERE
   a.indx = b.indx
  and a.indx = c.indx
  and a.ksppinm LIKE '/_%' escape '/'
;