Manually purge scheduler jobs logs

From sqlplus as sys or system run :

exec DBMS_SCHEDULER.PURGE_LOG();

Change ports for dbconsole componets

In order to change ports for the dbconsole componets do:

Set ORACLE_HOME & ORACLE_SID

For non RAC : emca -reconfig ports -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

for RAC : emca -reconfig ports -cluster -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

Backing up CSS and OCR files

First of all find the location of the css and ocr files:

Having set up the crs home

Voting File (CSS) : crsctl query css votedisk

If the file resides on shared filesystem (NFS,OCFS etc) then the you will see the ful path e.g /u02/oradata/css/cssfile

Now copy the file : cp /u02/oradata/css/cssfile /home/oracle/cssfilebackup

CSS on raw device:crsctl query css votedisk
0. 0 /dev/rhdisk5

located 1 votedisk(s).

In order to backup the CSS:
dd if=/dev/rhdisk5 of=/home/oracle/cssbackup


OCR File : ocrcheck
Here the ocr is on shared file system.
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 6108
Available space (kbytes) : 256012
ID : 353255034
Device/File Name : /u02/oradata/ocr/ocrfile
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Here the ocr is on raw device
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048300
Used space (kbytes) : 7252
Available space (kbytes) : 1041048
ID : 752511649
Device/File Name : /dev/rhdisk3
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded


First you find out if automatic backup is running.
ocrconfig -showbackup
labdb01 2009/05/07 07:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/07 03:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/06 23:23:47 /u01/app/crs/cdata/testcluster
labdb01 2009/05/05 23:23:45 /u01/app/crs/cdata/testcluster
labdb01 2009/04/30 03:23:32 /u01/app/crs/cdata/testcluster

So you have a week of backup of ocr.
Now you can take a bakcup for the ocr for extra reduduncy :

As root : ocrconfig -export /home/oracle/ocrbackup -s online

Remeber to take ocr & voting disks backup after any changing in the cluster registry.
(Add/Remove service , node , instance etc)

Row count for all tables in a schema

Row count of the tables in a schema can be fetched from USER_TABLES.

Query Output Method: Run the DBMS_STATS.gather_schema_stats package/procedure to gather statistics for all the objects in the schema and then query the USER_TABLES for the NUM_ROWS column to fetch the row count. This is one of the well known methods.

SQL> select table_name, nvl(num_rows,0) from user_tables order by 2 desc;

XML Output Method : In this method we use the DBMS_XMLGEN written by Mathias TITLE: Dynamic in-line queries.

SQL> select table_name
,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Notes:
The inner query calls the function dbms_xmlgen to generate the output in xml format, to return one row and one value for every row fetched by the outer query.
XMLTYPE: The XMLTYPE wrapper function converts the xml output to xmlobject so XPATH functions can be used. In this case xmltype will return xmls data as

<?xml version="1.0"?>
<ROWSET>
<ROW>
 <C>200</C>
</ROW>
</ROWSET>

EXTRACTVALUE The EXTRACTVALUE is an xml function which takes arguments of XMLType and an XPath expression and returns a scalar value. In this case xpath is '/ROWSET/ROW/C'.
TO_NUMBER: function to convert to number

Using dbms_xmlgen

Oracle's DBMS_XMLGEN is a cool package to generate xml data for a simple query(single-node) or complex query(with sub-nodes)


e.g.1:
SQL>select user_firstname, user_lastname
from arm$_user
where rownum <= 11;

With DBMS_XMLGEN
SQL>select dbms_xmlgen.getxml('
select user_firstname, user_lastname
from arm$_user
where rownum <= 11') user_node
from dual;

e.g.2:
SQL>select product_id, product_name, product_createdate from product where rownum <>select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11;

With DBMS_XMLGEN with sub-nodes
SQL>select dbms_xmlgen.getxml('
select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11') with_snode
from dual;

Delete oracle logs older by x days or minutes

Note: Assuming logs,trace,aduit & etc files are at the default location.

Delete oracle logs older by x days.
$ find /$ORACLE_BASE/admin/$ORACLE_SID/bdump/*. -mtime +1 -exec rm -f {} \;

Delete oracle logs older by x minutes.
$ find /$ORACLE_BASE/admin/$ORACLE_SID/bdump/*. -mmin +120 -exec rm -f {} \;

Switch "-mtime n" Files data was last modified n*24 hours ago.
Switch "-mmin n" Files data was last modified n minutes ago.

Silent installation of Oracle XE Server

Steps to create silent installation of Oracle XE Server

To perform a silent installation of Oracle Database XE Server, you need to create a response file and a wrapper shell script to perform the silent installation. The silent installation response file handles the configuration information

To perform a silent installation:

1. After downloading the installation executable

Create a response file that contains settings for the following values:

· A valid HTTP port numeric value, so that you can connect to the Oracle Database XE graphical user interface

· A valid listener numeric port value, so that you can connect to the Oracle Database XE database

· A password value for the SYS and SYSTEM administrative user accounts

· The SYS and SYSTEM password value again, to confirm it

· Yes (y) or no (n), to specify whether you want to start Oracle Database XE automatically when the computer starts

List only the values themselves, in the order shown as follows. For example:

8080

1521

44gh992

44gh992

y

2. Create a wrapper shell script to perform the silent installation.

It should contain commands similar to the following:

#!/bin/bash

rpm -ivh  /downloads/oracle-xe-univ-10.2.0.1-1.0.i386 > /xe_logs/XEsilentinstall.log

/etc/init.d/oracle-xe configure <  /downloads/responsefile >> /xe_logs/XEsilentinstall.log

3. Run the wrapper script.

For details of the installation, see the XEsilentinstall.log file.

Link to otn.oracle.com

http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm#BABCCGCF )