Friday, October 17, 2008

How to Shrink the datafiles in Oracle

1. Script to generate and run to set the maxbytes of the datafiles and shrink the datafiles:
SET echo off head off serveroutput off termout off feedback off verify off space 0;
SET linesize 150;
SET pagesize 0;

COLUMN value new_val blksize;
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size'
/

SPOOL gen_setmaxbytes.sql
SELECT 'spool gen_setmaxbytes.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes = 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| maxbytes / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SPOOL gen_shrink_dbfiles.sql
SELECT 'spool gen_shrink_dbfiles.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024)
|| 'm;' cmd
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND CEIL (blocks * &&blksize / 1024 / 1024)
- CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024) > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SET echo on feedback on;

@gen_setmaxbytes.sql
@gen_shrink_dbfiles.sql

EXIT;


2. Script to generate rebuild objects at the end of the given datafile:
Note: Replace the file name with actual datafile name; which you are not able to shrink after reorganization, because some objects have extents at the end of the datafile. Check and change the below script for partition tables.

SELECT mst.altst
FROM (SELECT 'ALTER TABLE ' || owner || '.' || segment_name
|| ' MOVE;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'TABLE'
UNION
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| segment_name
|| ' REBUILD;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'INDEX'
ORDER BY bid DESC) mst;

Friday, October 3, 2008

Database and Server Inventory Management using Oracle Application Express, or APEX (Oracle HTML DB):

1. Download Oracle Application Express:
http://www.oracle.com/technology/software/products/database/xe/htdocs/102xelinsoft.html


2. Install rpm:
Login as root!!!

rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Output:
[root@dbasvr1 oraclexp]# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm
Preparing... ########################################### [100%]
1:oracle-xe-univ ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.
[root@dbasvr1 oraclexp]#


3. Run /etc/init.d/oracle-xe configure as the root user:

Output:
[root@dbasvr1 oraclexp]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Port 1521 appears to be in use by another application. Please specify a different port.
Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration: -- oracle
Confirm the password: -- oracle

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://dbasvr1:8080/apex"
[root@dbasvr1 oraclexp]#

To start the database manually, run this command:
$ /etc/init.d/oracle-xe start

To stop the database manually, use the following command:
$ /etc/init.d/oracle-xe stop


4. Login and Create users:
Login as system user and goto Administration > Manage Database Users and create below users.

hp_dba/hp_dba
vdumpa/vdumpa
guest/guest


5. Create the database inventory table in hp_dba schema to store the information:

create sequence hp_dba.db_inv_seq start with 1 increment by 1;

create table hp_dba.db_inv(
db_no number(10) primary key,
Business_Unit varchar2(40),
db_name varchar2(20),
instance_name varchar2(20),
server_name varchar2(30),
Server_config varchar2(50),
DB_version varchar2(20),
OS_version varchar2(20),
DB_Env varchar2(30),
DB_size varchar2(20),
SGA_size varchar2(20),
PGA_size varchar2(20),
backup_type varchar2(40),
backup_frequencey varchar2(40),
physical_location varchar2(20),
verified_date date,
verified_by varchar2(40),
Primary_DBA varchar2(30),
Secondary_DBA varchar2(30),
Customer_name VARCHAR2(40),
DB_Control_URL varchar2(100),
Description varchar2(100))
tablespace users;

grant select on hp_dba.db_inv to guest;
grant select, insert, update, delete on hp_dba.db_inv to vdumpa;

CREATE OR REPLACE TRIGGER hp_dba.db_inv_BI
BEFORE INSERT
ON hp_dba.db_inv REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if :new.DB_NO is null then
select db_inv_seq.nextval
into :new.DB_NO
from dual;
end if;
end;


6. Login as hp_dba user to create Application:

a. On the Database Home Page, click the Application Builder icon.
b. Click the Create button.
c. Under Create Application, select Create Application and click Next.
d. Under Create Application:
Name: Enter "dbinv".
Accept the remaining defaults(Application(100)).
Click Next.
Next, add pages to your application.
e. Under Add Page:
For Select Page Type, select Report and Form
Next to the Table Name field, click the up arrow, and then select db_inv
from the Search Dialog window.
Click Add Page.
Click Next.
f. On the Tabs panel, select Two Level of Tabs and click Next.
g. On the Shared Components panel, accept the default (No) and click Next.
h. For Authentication Scheme, Language, and User Language Preference Derived From,
accept the defaults and click Next.
i. For the theme, select Theme 2 click Next.
j. After you click Create, the following message displays at the top of the page:
Application created successfully.


7. Running Your New Application:

a. To run your application, Click the Run Application icon.
b. In the log in page, enter hp_dba for both the User Name and Password
c. Search/Edit/Create the values from this page.


8. Public guest user access link:
http://dbasvr1:8080/apex/f?p=100:1


9. Backing Up the schema data:
EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > cat /etc/oratab
XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N

EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > . oraenv
ORACLE_SID = [EX] ? XE

XE: /home/oracle > exp hp_dba/hp_dba file=exp_hp_dba.dmp

Export: Release 10.2.0.1.0 - Production on Fri Oct 17 09:00:24 2008

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


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HP_DBA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HP_DBA
About to export HP_DBA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HP_DBA's tables via Conventional Path ...
. . exporting table DB_INV 37 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.