Moving Audit Table Out Of SYSTEM Tablespace

Database auditing is the process of recording, monitoring and reporting of the actions performed on a database. AUD$ is the underlying table that holds all of the system auditing information which resides in SYSTEM tablespace. We keep on deleting and truncating the Aud$ table so that it doesnot grow large. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace. 

Until 11g, the way to move out SYS.AUD$ is not supported. If  we want to do it then we to do it manually . In 11g we can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION  provided with the DBMS_MGMT package .So below are the steps .

1.) Connect to database as SYS user.

C:\> sqlplus sys/xxxx@noida  as sysdba

2.) Create a tablespace for audit file as

SQL>create tablespace aud_tbs datafile 'C:\app\Neerajs\oradata\noida/audit.dbf'  size 10M autoextend on ;

3.) Create a table inside aud_tbs tablespace as

SQL> create table aud_tab tablespace aud_tbs  as select * from sys.aud$ where 1=2 ;

4.) Rename the Original Audit table as 

SQL> rename aud$ to aud$_org ;

5.) Rename table  aud_tab  to AUD$

SQL> rename aud_tab  to aud$ ;

Hence, auditing record will be stored in the aud_tbs tablespace .Further, we can create an index on the aud_tbs table for quick access .

Enjoy     :-) 

How to get port number list of Enterprise Manager and isqlplus

 During installation, Oracle Universal Installer assigns port numbers to components from a set of default port numbers.  Many Oracle Database components and services use ports. It is important to know the port numbers used by these services, and to make sure that the same port number is not used by two services on our host.

The portlist information of enterprise manger or isqlplus or others web services are find out at the location  $ORACLE_HOME\install\portlist.ini. In my case the  following details can found in ini file .

iSQL*Plus HTTP port number =5560

Enterprise Manager Console HTTP Port (noida) = 5500

Enterprise Manager Agent Port (noida) = 1830

Enterprise Manager Console HTTP Port (delhi) = 5501

Enterprise Manager Agent Port (delhil) = 1831

Note: The ports that are in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation

Enjoy       :-) 

Oracle default port list

The following table contains Oracle default ports for different products like Oracle Database or Oracle Application Server.Changing the default ports can help to stop simple attacks but not real portscans. In the world of Oracle it is very often not possible to change the default port because the port is hardcoded. At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else.

The IANA default port number can be found here: http://www.iana.org/assignments/port-numbers

Service	Port	Product	How to change
Oracle HTTP Server listen port / Oracle HTTP Server port	80	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle Internet Directory(non-SSL)	389	Oracle Application Server
Oracle HTTP Server SSL port	443	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle Internet Directory(SSL)	636	Oracle Application Server
Oracle Net Listener / Enterprise Manager Repository port	1521	Oracle Application Server / Oracle Database	Edit listener.ora and restart listener
Oracle Net Listener	1526	Oracle Database	Edit listener.ora and restart listener
Oracle Names	1575	Oracle Database	Edit names.ora and restart names server
Oracle Connection Manager (CMAN)	1630	Oracle Connection Manager	Edit cman.ora and restart Connection Manager
Oracle JDBC for Rdb Thin Server	1701	Oracle Rdb
Oracle Intelligent Agent	1748	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1754	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1808	Oracle Application Server	snmp_rw.ora
Oracle Intelligent Agent	1809	Oracle Application Server	snmp_rw.ora
Enterprise Manager Servlet port SSL	1810	Oracle Enterprise Manager
Oracle Connection Manager Admin (CMAN)	1830	Oracle Connection Manager (CMAN)	Edit cman.ora and restart Connection Manager
Enterprise ManagerAgent port	1831	Oracle Enterprise Manager
Enterprise Manager RMI port	1850	Oracle Enterprise Manager
Oracle XMLDB FTP Port	2100	Oracle Database	change dbms_xdb.cfg_update
Oracle GIOP IIOP	2481	Oracle Database	Edit listener.ora/init.ora and restart listener/database
Oracle GIOP IIOP for SSL	2482	Oracle Database	Edit listener.ora/init.ora and restart listener/database
Oracle OC4J RMI	3201	Oracle Application Server
Oracle OC4J AJP	3301	Oracle Application Server
Enterprise Manager Reporting port	3339	Oracle Application Server	Edit oem_webstage/oem.confand restart OHS
Oracle OC4J IIOP	3401	Oracle Application Server
Oracle OC4J IIOPS1	3501	Oracle Application Server
Oracle OC4J IIOPS2	3601	Oracle Application Server
Oracle OC4J JMS	3701	Oracle Application Server
Oracle9iAS Web Cache Admin port	4000	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Invalidation port	4001	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Statistics port	4002	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle Internet Directory(SSL)	4031	Oracle Application Server
Oracle Internet Directory(non-SSL)	4032	Oracle Application Server
OracleAS Certificate Authority (OCA) - Server Authentication	4400	Oracle Application Server
OracleAS Certificate Authority (OCA) - Mutual Authentication	4401	Oracle Application Server
Oracle HTTP Server SSL port	4443	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(SSL) port	4444	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle TimesTen	4662	Oracle TimesTen
Oracle TimesTen	4758	Oracle TimesTen
Oracle TimesTen	4759	Oracle TimesTen
Oracle TimesTen	4761	Oracle TimesTen
Oracle TimesTen	4764	Oracle TimesTen
Oracle TimesTen	4766	Oracle TimesTen
Oracle TimesTen	4767	Oracle TimesTen
Oracle Enterprise Manager Web Console	5500	Oracle Enterprise Manager Web
iSQLPlus 10g	5560	Oracle i*SQLPlus
iSQLPlus 10g	5580	Oracle i*SQLPlus RMI Port
Oracle Notification Service request port	6003	Oracle Application Server
Oracle Notification Service local port	6100	Oracle Application Server
Oracle Notification Service remote port	6200	Oracle Application Server
Oracle9iAS Clickstream Collector Agent	6668	Oracle Application Server
Java Object Cache port	7000	Oracle Application Server
DCM Java Object Cache port	7100	Oracle Application Server
Oracle HTTP Server Diagnostic Port	7200	Oracle Application Server
Oracle HTTP Server Port Tunneling	7501	Oracle Application Server
Oracle HTTP Server listen port / Oracle HTTP Server port	7777	Oracle Application Server	Edit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(non-SSL) port	7779	Oracle Application Server	Webcache Admin GUI or webcache.xml
Oracle HTTP Server Jserv port	8007	Oracle Application Server
Oracle XMLDB HTTP port	8080	Oracle Database	change dbms_xdb.cfg_update
OC4J Forms / Reports Instance	8888	Oracle Developer Suite
OC4J Forms / Reports Instance	8889	Oracle Developer Suite
Oracle Forms Server 6 / 6i	9000	Oracle Application Server
Oracle SOAP Server	9998	Oracle Application Server
OS Agent	14000	Oracle Application Server
Oracle Times Ten	15000	Oracle Times Ten
Oracle Times Ten	15002	Oracle Times Ten
Oracle Times Ten	15004	Oracle Times Ten
Enjoy      :-)

Hot Backups,extras redo generated and Fractured Blocks

Today , I  have  come  across  a good  article. I  have worked  and  modify it to explained  in detail . Hope you  all  appreciate  it . Before discussing this topic, let's have an overview of  user-managed  hot backup i.e, what  happen  during  begin  backup  mode  and  end backup  mode.

When  we  begin  backup, it  freezes  the header  of the datafiles (means the SCN number will not increment  any more until the backup is ended ) . It  also  instructs  LGWR  to write  whole  blocks  to  redo the  first  time  a  block  is  touched. Writes  still  occur to  the datafile,  just  the  SCN  is  frozen. This occurs so that at recovery  time, Oracle will  know that  it  must  overwrite  all  blocks  in the backup file with  redo entries  due  to  fracturing. The original  datafiles remain up-to-date, but the backup files will  not be  because they  are  being  changed  during  backup. When  we  end  backup, it unfreezes  the header of the  datafiles  and  allows SCNs  to  be  recorded  properly  during  checkpoint.

Question:  The  oracle documentation  tells  us  that when  we  put  a  tablespace  in  backup  mode , the first  DML  in  the  session  logs  the entire  block  in  the  redo  log buffer  and  not  just the changed vectors.

1.) Can  we  simulate  an example  to  see  this  happening?

2.) What  can  be  the  purpose  of  logging  the  entire  block  the  first  time  and  not  do  the  same subsequently?

Answer:  Below, I’ve created  a simulation. Pay  attention  to  the  “redo size”  statistic in each. First, I have  updated  a  single  row  of  the  employees  table.

SQL> set  autotrace  trace  stat

SQL> update  employees  set  first_name='Stephen'   where   employee_id = 100; 

1 row updated.

Statistics

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

          0  recursive calls

          1  db block gets

          1  consistent gets

          0  physical reads

        292  redo size

        669  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>  rollback;

Rollback  complete.

Notice  the  redo  size  was  only  292  bytes, not  a  very  large  amount.  Now,  let’s  put  the  USERS tablespace  into  hot  backup  mode.

SQL> alter  tablespace  users  begin  backup;

Tablespace altered.

SQL> update  employees  set  first_name = 'Stephen'  where  employee_id = 100;

1 row updated.

Statistics

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

          0  recursive calls

          2  db block gets

          1  consistent gets

          0  physical reads

       8652  redo size

        670  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

Wow!    Quite  a  bit  of  a difference. This  time,  we  can  see  that  atleast  an  entire  block  was written to redo; 8,652  bytes  total. Let’s  run  it  one more  time, with  the  tablespace still  in  hot  backup mode.

SQL> /  

1 row updated.

Statistics

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

          0  recursive calls

          1  db block gets

          1  consistent gets

          0  physical reads

        292  redo size

        671  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

This time , it only used 292 bytes, the same as the original amount. However, to address the second question, we’re  going to attempt  changing a  different  block,  by  changing a record in the departments table  instead  of  employees.

SQL> update  departments  set  department_name = 'Test Dept'  where  department_id = 270;

1 row updated.

Statistics

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

         17  recursive calls

          1  db block gets

          5  consistent gets

          1  physical reads

       8572  redo size

        673  bytes sent via SQL*Net to client

        610  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed

The  result is that  another entire block was written to redo. In  the question, we  stated: “The  oracle documentation  tells  us  that  when  we  put  a  tablespace  in  backup mode ,  the  first  DML  in  the session  logs  the  entire  block  in  the redo log buffer and  not  just  the  changed  vectors” . This  is close, but not right on the mark.


It  is  not  the  first  DML of  the  session, but  the  first  DML to  a  block  that  is  written  to redo . However, when Oracle  writes the  first  DML  for  the  block, it   ensures  that  the  redo  logs/archive trail  contains  at  least one  full  representation of  each  block  that  is changed. Subsequent  changes will therefore  be  safe.

This  process  exists  to  resolve  block  fractures.  A  block  fracture  occurs  when  a  block  is being  read  by  the  backup, and  being  written  to  at  the  same  time  by  DBWR . Because  the  OS  (usually) reads  blocks  at  a  different  rate  than   Oracle, the  OS  copy  will   pull pieces  of  an  Oracle  block  at  a time.  What  if  the  OS  copy  pulls  half  a  block, and  while  that  is  happening,  the  block  is  changed  by  DBWR?  When  the  OS  copy  pulls  the  second  half  of  the  block  it  will  result  in  mismatched  halves,  which  Oracle  would  not  know  how  to  reconcile .

This  is  also  why  the SCN  of  the  datafile  header does  not change  when  a  tablespace  enters  hot backup  mode. The  current  SCNs  are  recorded  in  redo, but   not  in  the datafile. This  is  to  ensure  that Oracle  will  always  recover over  the  datafile  contents  with  redo  entries. When  recovery  occurs, the  fractured  datafile  block will  be  replaced  with  a  complete  block  from redo,  making  it whole  again.  After  Oracle  can  be  certain  it  has a  complete  block,  all   it  needs  are  the  vectors.

Enjoy    :-)

What is a Port ?

A port is just a interface (or for example we can imagine our telephone socket on the phone) between two computers or two software  programs. So  if  two computers want  to talk to each other, each  of them should communicate  through  a  port. If  two  software  programs want to talk  with  each  other, each of them should talk through a  port .To know more about  hardware  ports please check.  Hardware port .

A  DBA should mostly  be concerned with  software  ports. This  software  port  is  like a virtual (no physical shape) connection so  software  programs talk with  each other. The  two  well  known  ports  used by  majority  of  software  programs are  the TCP and UDP ports.

During  installation , Oracle  Universal  Installer  assigns  port  numbers  to components from  a  set  of default port numbers. Many Oracle Database components and services use ports. As  an  administrator ,  it is  important  to  know  the  port  numbers  used  by these services, and  to  make  sure  that  the  same  port number  is  not   used  by  two  services  on  our   host .

Most  port numbers are assigned during installation. Every  component and service has an allotted  port range, which  is the set  of  port  numbers  Oracle  Database  attempts to  use when  assigning a  port . Oracle  Database starts  with  the  lowest  number  in  the  range  and  performs  the  following  checks:

I.) Is  the  port  used  by  another  Oracle  Database  installation  on  the  host?
 The  installation  may  be  up or down at  the  time; Oracle  Database  can  still  detect  if  the port  is  used.

II.) Is  the  port  used  by a  process  that  is  currently  running?
This  could be  any  process  on  the  host, even  a  non-Oracle  Database  process.

If  the answer  to  any  of  the  preceding  questions  is  yes, Oracle  Database  moves  to  the  next  highest port in  the  allotted  port  range  and continues  checking  until  it   finds a  free port.

If  we change a port number, it is not updated in the portlist.ini file, so we can only rely on this file immediately after installation. if we want to see all the different port numbers click on Different Port Numbers.

When   we  install oracle enterprise manager on a  server ,oracle automatically assigns some  port numbers to it. So we can open the database control in 10g by using a link like http://servername:1158.com. Here 1158  is the port number.

So, in  this  way  different  oracle  components  uses  different port numbers and  hence we should  know what  a  port  is. Sometimes  problems  would  become because  the  port  is  blocked  by  a  firewall or  the unix administrator has not  configured the port correctly. So in those cases we have to speak to the administrator  to  see  that  the  port  is  configured  correctly.

Enjoy    :-)