www.expertDBA.com

A Database Administration Blog

 Click here for Expert DBA Forum 
Pages
Categories
Archives
Calendar
September 2010
M T W T F S S
« Dec    
 12345
6789101112
13141516171819
20212223242526
27282930  
Search
Recent Posts
Meta
December 16th, 2009

ASM Hands-On Training

Posted By sanjay | Category: Oracle Knowledge Base | No Comments

Here is a nice link for the hands on training of ASM:

http://www.expertDBA.com/oracle/articles/ASM_Hands_On_Training.pdf

Note: Above mentioned training document is copied/taken from the Oracle Blog http://blogs.oracle.com.

Posted By sanjay | Category: Oracle Knowledge Base | No Comments
December 15th, 2009

Configuring Oracle ASM on Linux (using ASMlib)

Posted By sanjay | Category: Oracle, Oracle Knowledge Base | No Comments

Oracle Automatic Storage Management (ASM) on Red Hat Linux (using ASMlib)

As a first step we need to install support ASM libraries to configure the ASM. This library is provided to enable ASM I/O to Linux disks without the limitations of the standard Unix I/O API.

The ASMLib software is available from the Oracle Technology Network at http://www.oracle.com/technology/tech/linux/asmlib/index.html

The oracleasmlib package provides the actual ASM library. The oracleasm-support package provides the utilities used to get the ASM driver up and running. Both of these packages need to be installed.

The remaining packages provide the kernel driver for the ASM library. Each package provides the driver for a different kernel. You must install the appropriate package for the kernel you are running. Use the “uname -r command to determine the version of the kernel. The oracleasm kerel driver package will have that version string in its name. For example, if you were running Red Hat Enterprise Linux 4 AS, and the kernel you were using was the 2.6.9-5.0.5.ELsmp kernel, you would choose the oracleasm-2.6.9-5.0.5-ELsmp package. So, to install these packages on RHEL4 on an Intel x86 machine, you might use the command:

[root@ssk ~]# rpm -Uvh oracleasm-support-2.1.3-1.el5.i386.rpm
[root@ssk ~]# rpm -Uvh oracleasm-2.6.18-53.el5PAE-2.0.4-1.el5.i686.rpm
[root@ssk ~]# rpm -Uvh oracleasmlib-2.0.4-1.el5.i386.rpm

 If you were on a different machine, the package names would be slightly different, replacing ‘i686′ with the appropriate architecture. Use the package names relevant for your distribution.

NOTE: Distributions with the Linux 2.4 kernel still use the 1.0 kernel driver, while distributions based on the Linux 2.6 kernel use the 2.0 kernel driver. All distributions use version 2.0 of the support and library packages. See the driver matrix for more information.

Once the above steps are completed, ASMLib is now installed on the system.

 Next step is to Configure the Oracle ASM library driver:

Run the /etc/init.d/oracleasm script with the ‘configure’ option. It will ask for the user and group that default to owning the ASM driver access point. If the database was running as the ‘oracle’ user and the ‘dba’ group, the output would look like this:

[root@ssk ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets (‘[]‘).  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort. 

Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                               [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@ssk ~]#

This should load the oracleasm.o driver module and mount the ASM driver filesystem. By selecting enabled = ‘y’ during the configuration, the system will always load the module and mount the filesystem on boot.

NOTE: Reference: Oracle documentation: http://www.oracle.com/technology/tech/linux/asmlib/install.html

While configuring Oracle ASM if you are getting the following error:

Initializing the Oracle ASMLib driver:                                  [FAILED]

There could be two reasons:

  1. RPM installed for ASMLIB are not correct: Check and reinstall the correct version.
    1. a.      Use the following links:
      http://www.oracle.com/technology/tech/linux/asmlib/install.html
      http://www.oracle.com/technology/tech/linux/asmlib/index.html
  2. Other reason could be: — SELinux is turned on

SELinux would bring trouble for Oracle ASMLib, not only this, it played OCFS, OEM grid control, Oracle cluster service as well. Turn it off by disabling it. Oracle will not complain.

Solution:
Turn off SELinux or put it in permssive mode.

How-to:
1) Go to modify /etc/selinux/config in redhat or /boot/grub/grub.conf in other linux with GRUB boot loader. It looks like this:

# SELINUX= can take one of these three values:
#       enforcing – SELinux security policy is enforced.
#       permissive – SELinux prints warnings instead of enforcing.
#       disabled – SELinux is fully disabled.
SELINUX=enforcing —> change it to SELINUX=disabled
2) Reboot server to take it effect

You can set up it into permissive mode right away without reboot.

 Usage: setenforce [ Enforcing | Permissive | 1 | 0 ]

 The automatic start can be enabled or disabled with the ‘enable’ and ‘disable’ options to /etc/init.d/oracleasm:

[root@ssk ~]# /etc/init.d/oracleasm disable

[root@ssk ~]# /etc/init.d/oracleasm enable

 

[root@ssk ~]# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: done
Dropping Oracle ASMLib disks:                                                   [  OK  ]
Shutting down the Oracle ASMLib driver:                              [  OK  ]

 [root@ssk ~]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                                       [  OK  ]
Scanning the system for Oracle ASMLib disks:                       [  OK  ]

[root@ssk ~]#

 Next step is of making Disks Available to ASMLib

To create a Disk Group

[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc11
Marking disk “/dev/hdc11″ as an ASM disk: [ OK ]
[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc12
Marking disk “/dev/hdc12″ as an ASM disk: [ OK ]
[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc13
Marking disk “/dev/hdc13″ as an ASM disk: [ OK ]

Note: If above commands fails then please make sure the disk that you are specifying is not already being used or not already mounted:

To Delete a Disk Group

[root@ssk ~]# /etc/init.d/oracleasm  deletedisk VOL1
Deleting Oracle ASM disk “VOL1″ [ OK ]

To query the disks

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc11
Device “/dev/hdc11″ is marked an ASM disk with the label “VOL1″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc12
Device “/dev/hdc12″ is marked an ASM disk with the label “VOL2″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc13
Device “/dev/hdc13″ is marked an ASM disk with the label “VOL3″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc10
Device “/dev/hdc10″ is not marked as an ASM disk

 [root@ssk ~]# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk

[root@ssk ~]# /etc/init.d/oracleasm querydisk VOL4
Disk “VOL4″ does not exist or is not instantiated

[root@ssk ~]#

To list and query the existing disks

[root@ssk ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3

# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk on device [8, 2]

 When a disk is added to a RAC setup, the other nodes need to be notified about it. Run the ‘createdisk’ command on one node, and then run ’scandisks’ on every other node:

  [root@ca-test1 /]# /etc/init.d/oracleasm scandisks
  Scanning system for ASM disks                              [  OK  ]
We are now done with installation and configuration of ASMLib on Linux platform.

 Creating New Oracle ASM Instance:

 Create the password file:

[oracle@ssk ~]$ orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=changeIt entries=5

Create required directories:

[oracle@ssk ~]$ mkdir -p $ORACLE_BASE/admin/+ASM
[oracle@ssk ~]$ cd $ORACLE_BASE/admin/+ASM
[oracle@ssk +ASM]$ mkdir bdump
[oracle@ssk +ASM]$ mkdir udump
[oracle@ssk +ASM]$ mkdir cdump
[oracle@ssk +ASM]$ mkdir pfile
 

Create the init+ASM.ora file:

Using vi editor or any other editor you like, create the init+ASM.ora file under the $ORACLE_HOME/dbs
directory and add the below lines into this file.

asm_diskgroups=’DATA_GRP’
asm_diskstring=’ORCL:*’
background_dump_dest=’/u01/app/admin/+ASM/bdump’
core_dump_dest=’/u01/app/admin/+ASM/cdump’
instance_type=’asm’
large_pool_size=12M
remote_login_passwordfile=’SHARED’
user_dump_dest=’/u01/app/admin/+ASM/udump’

[oracle@ssk ~]$ cat $ORACLE_HOME/dbs/init+ASM.ora
asm_diskgroups=’PROD_DB_GRP’
asm_diskstring=’ORCL:*’
background_dump_dest=’/u01/app/admin/+ASM/bdump’
core_dump_dest=’/u01/app/admin/+ASM/cdump’
instance_type=’asm’
large_pool_size=12M
remote_login_passwordfile=’SHARED’
user_dump_dest=’/u01/app/admin/+ASM/udump’
[oracle@ssk ~]$

 

Create spfile+ASM.ora and start the instance using that file:

[oracle@ssk ~]$ export ORACLE_SID=+ASM
[oracle@ssk ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 4 21:17:35 2005

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup mount

ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
“DATA_GRP”

 

SQL> show parameter disk
 NAME                                 TYPE        VALUE
———————————— ———– ——————————
asm_diskgroups                     string      DATA_GRP
asm_diskstring                       string      ORCL:*
disk_asynch_io                       boolean     TRUE

                         

SQL> create diskgroup data_grp
failgroup f1 disk ‘ORCL:DSK1′
failgroup f2 disk ‘ORCL:DSK2′;

 Diskgroup created.   

 

SQL> set linesize 100
SQL> col name format a15
SQL> col path format a15
SQL> select name, path from v$asm_disk where name is not null;

NAME            PATH
————— —————
DSK1            ORCL:DSK1
DSK2            ORCL:DSK2

 

SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

    NAME            TYPE     TOTAL_MB   FREE_MB
    ————— —— ———- ———-
    DATA_GRP        NORMAL      59999      59897

Posted By sanjay | Category: Oracle, Oracle Knowledge Base | No Comments
September 2nd, 2009

Schema Comparision Script

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments

PROMPT Posted by Anuj Kumar Pandey
PROMPT Schema Comparison
PROMPT =================
PROMPT
PROMPT Run this script while connected to one Oracle schema. Enter the Oracle
PROMPT username, password, and SQL*Net / Net8 service name of a second schema.
PROMPT This script will compare the two schemas and generate a report of
PROMPT differences.
PROMPT
PROMPT A temporary database link and table will be created and dropped by
PROMPT this script.
PROMPT

ACCEPT schema CHAR PROMPT “Enter username for remote schema: ”
ACCEPT passwd CHAR PROMPT “Enter password for remote schema: ” HIDE
ACCEPT tnssvc CHAR PROMPT “Enter SQL*Net / Net8 service for remote schema: ”
ACCEPT dblinkname CHAR PROMPT “Enter the dblink name to be created: ”
ACCEPT rem_schema CHAR PROMPT “Enter the dblink name: ”

PROMPT

ACCEPT report CHAR PROMPT “Enter filename for report output: ”

SET FEEDBACK OFF
SET VERIFY OFF

CREATE DATABASE LINK &dblinkname CONNECT TO &schema IDENTIFIED BY &passwd USING ‘&tnssvc’;

SET TRIMSPOOL ON

SPOOL &report

SELECT SUBSTR (RPAD (TO_CHAR (SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’), 25), 1, 25)
“REPORT DATE AND TIME”
FROM SYS.dual;

COL local_schema FORMAT a35 TRUNC HEADING “LOCAL SCHEMA”
COL remote_schema FORMAT a35 TRUNC HEADING “REMOTE SCHEMA”

SELECT USER || ‘@’ || C.global_name local_schema,
A.username || ‘@’ || B.global_name remote_schema
FROM user_users@&rem_schema A, global_name@&rem_schema B, global_name C
WHERE ROWNUM = 1;

SET PAGESIZE 9999
SET LINESIZE 250
SET FEEDBACK 1

SET TERMOUT OFF

PROMPT

REM Object differences
REM ==================

COL object_name FORMAT a30

PROMPT SUMMARY OF OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
)
GROUP BY object_type
ORDER BY object_type;

PROMPT SUMMARY OF EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
)
GROUP BY object_type
ORDER BY object_type;

PROMPT OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
ORDER BY object_type, object_name;

PROMPT EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
ORDER BY object_type, object_name;

PROMPT OBJECTS IN LOCAL SCHEMA THAT ARE NOT VALID

SELECT object_name, object_type, status
FROM user_objects
WHERE status != ‘VALID’
ORDER BY object_name, object_type;

REM Table differences
REM =================

PROMPT TABLE COLUMNS MISSING FROM ONE SCHEMA
PROMPT (NOTE THAT THIS REPORT DOES NOT LIST DISCREPENCIES IN COLUMN ORDER)

(
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
WHERE table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns
WHERE table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2;

COL schema FORMAT a15
COL nullable FORMAT a8
COL data_type FORMAT a9
COL data_length FORMAT 9999 HEADING LENGTH
COL data_precision FORMAT 9999 HEADING PRECISION
COL data_scale FORMAT 9999 HEADING SCALE
COL default_length FORMAT 9999 HEADING LENGTH_OF_DEFAULT_VALUE

PROMPT DATATYPE DISCREPENCIES FOR TABLE COLUMNS THAT EXIST IN BOTH SCHEMAS

(
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns
)
MINUS
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2, 3;

REM Index differences
REM =================

COL column_position FORMAT 999 HEADING ORDER

PROMPT INDEX DISCREPENCIES FOR INDEXES THAT EXIST IN BOTH SCHEMAS

(
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
UNION ALL
(
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes@&rem_schema
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
ORDER BY 1, 2, 6;

REM Constraint differences
REM ======================

PROMPT CONSTRAINT DISCREPENCIES FOR TABLES THAT EXIST IN BOTH SCHEMAS

SET FEEDBACK OFF

CREATE TABLE temp_schema_compare
(
database NUMBER(1),
object_name VARCHAR2(30),
object_text VARCHAR2(2000),
hash_value NUMBER
);

DECLARE
CURSOR c1 IS
SELECT constraint_name, search_condition
FROM user_constraints
WHERE search_condition IS NOT NULL;
CURSOR c2 IS
SELECT constraint_name, search_condition
FROM user_constraints@&rem_schema
WHERE search_condition IS NOT NULL;
v_constraint_name VARCHAR2(30);
v_search_condition VARCHAR2(32767);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_constraint_name, v_search_condition;
EXIT WHEN c1%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
1, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_constraint_name, v_search_condition;
EXIT WHEN c2%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
2, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c2;
COMMIT;
END;
/

SET FEEDBACK 1

(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables
)
AND B.database (+) = 2
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE B.database (+) = 1
AND B.object_name (+) = A.constraint_name
)
UNION ALL
(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
AND B.database (+) = 1
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE B.database (+) = 2
AND B.object_name (+) = A.constraint_name
)
ORDER BY 1, 4, 2;

REM Database link differences
REM =========================

PROMPT DATABASE LINK DISCREPENCIES

COL db_link FORMAT a40

(
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links@&rem_schema
MINUS
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links
)
UNION ALL
(
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links
WHERE db_link NOT LIKE ‘&rem_schema.%’
MINUS
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links@&rem_schema
)
ORDER BY 1, 2;

REM Sequence differences
REM ====================

PROMPT SEQUENCE DISCREPENCIES

(
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
MINUS
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
)
UNION ALL
(
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
MINUS
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
)
ORDER BY 1, 2;

REM Private synonym differences
REM ===========================

PROMPT PRIVATE SYNONYM DISCREPENCIES

(
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
MINUS
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms
)
UNION ALL
(
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms
MINUS
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
)
ORDER BY 1, 2;

REM PL/SQL differences
REM ==================

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, text
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, text
FROM user_source
)
UNION ALL
(
SELECT name, type, line, text
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, text
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS (CASE INSENSITIVE COMPARISON)

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source
)
UNION ALL
(
SELECT name, type, line, UPPER (text)
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

REM Trigger differences
REM ===================

PROMPT TRIGGER DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT trigger_name, trigger_body
FROM user_triggers;
CURSOR c2 IS
SELECT trigger_name, trigger_body
FROM user_triggers@&rem_schema;
v_trigger_name VARCHAR2(30);
v_trigger_body VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c1%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c2%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
AND A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
AND A.table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
)
ORDER BY 1, 2, 5, 3;

REM View differences
REM ================

PROMPT VIEW DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT view_name, text
FROM user_views;
CURSOR c2 IS
SELECT view_name, text
FROM user_views@&rem_schema;
v_view_name VARCHAR2(30);
v_text VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_view_name, v_text;
EXIT WHEN c1%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_view_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_view_name, v_text;
EXIT WHEN c2%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_view_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
AND A.view_name IN
(
SELECT view_name
FROM user_views@&rem_schema
)
MINUS
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
AND A.view_name IN
(
SELECT view_name
FROM user_views
)
MINUS
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
)
ORDER BY 1, 2;

REM Job queue differences
REM =====================

COL what FORMAT a30
COL interval FORMAT a30

PROMPT JOB QUEUE DISCREPENCIES

(
SELECT what, interval, ‘Remote’ schema
FROM user_jobs@&rem_schema
MINUS
SELECT what, interval, ‘Remote’ schema
FROM user_jobs
)
UNION ALL
(
SELECT what, interval, ‘Local’ schema
FROM user_jobs
MINUS
SELECT what, interval, ‘Local’ schema
FROM user_jobs@&rem_schema
)
ORDER BY 1, 2, 3;

REM Privilege differences
REM =====================

PROMPT OBJECT-LEVEL GRANT DISCREPENCIES

(
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects
)
MINUS
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs
)
UNION ALL
(
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects@&rem_schema
)
MINUS
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
)
ORDER BY 1, 2, 3;

PROMPT SYSTEM PRIVILEGE DISCREPENCIES

(
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs@&rem_schema
MINUS
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs
)
UNION ALL
(
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs
MINUS
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs@&rem_schema
)
ORDER BY 1, 2;

PROMPT ROLE PRIVILEGE DISCREPENCIES

(
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
MINUS
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs
)
UNION ALL
(
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs
MINUS
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
)
ORDER BY 1, 2;

SPOOL OFF

SET TERMOUT ON

PROMPT
PROMPT Report output written to &report

SET FEEDBACK OFF

DROP TABLE temp_schema_compare;
DROP DATABASE LINK &rem_schema;

SET FEEDBACK 6
SET PAGESIZE 20
SET LINESIZE 80

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments
August 31st, 2009

Tablespace free space check

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments

Select substr(b.tablespace_name,1,20) TableSpace, Total_Max_MB, NVL(Total_Used_MB,0) Total_Used_MB,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0)),0) “Free_MB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/1024,2) “Free_GB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/Total_Max_MB * 100,2) “Free%”
from (select tablespace_name, round(sum(bytes/1024/1024),2) Total_Used_MB
from dba_segments
group by tablespace_name) a,
(select tablespace_name,
round(sum(decode(maxbytes,0,bytes/1024/1024,maxbytes/1024/1024)),0) Total_Max_MB,
round(sum(user_bytes/1024/1024),0) Total_User_MB
from dba_data_files
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name (+)
and ROUND((Total_Max_MB – Total_Used_MB)/Total_Max_MB * 100,2) <=100
order by “Free%” desc;

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments
August 31st, 2009

Buffers used by objects

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments

TTITLE “Total buffers used by individual objects.”
COL OWNER FOR A8
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A10
select obj.owner “OWNER”, obj.object_name “OBJECT_NAME”,
obj.object_type “OBJECT_TYPE”, COUNT(distinct bh.block#) “# Buffers”
from dba_objects obj, v$bh bh
where obj.object_id = bh.objd and
obj.owner != ‘SYS’
group by obj.owner, obj.object_name, obj.object_type
having COUNT(distinct bh.block#) >= 2
order by 4;

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments
July 28th, 2009

How to check datafile space utilization

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments

1)

set linesize 200
SELECT SUBSTR (df.NAME, 1, 65) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

2)

select d.tablespace_name, d.file_id, d.file_name, d.bytes allocated, nvl(f.bytes,0) free
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space
group by tablespace_name, file_id) f
where d.tablespace_name in (‘SYSTEM’,'SYSAUX’)
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments
July 28th, 2009

Script to use compressed utility in 10g with EXPDP

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments

#!/bin/ksh
# set -x
PATH=/oraeng/app/oracle/product/10.2.0/bin:/sbin:/usr/bin:/usr/sbin:/usr/sbin:/usr/ccs/bin:/usr/local/bin:/usr/ucb/bin: export PATH
ORACLE_HOME=/oraeng/app/oracle/product/10.2.0 ; export ORACLE_HOME
ORACLE_SID=orcl ; export ORACLE_SID
TNS_ADMIN=/oraeng/app/oracle/product/10.2.0/network/admin ; export TNS_ADMIN
TABLE_NAME=emp; export TABLE_NAME
DATE=`date “+%m/%d/%y:%H:%M:%S”` ; export DATE
PIPE_FILE=/tmp/exp_${ORACLE_SID}${TABLE_NAME}_pipe
start_time=`date “+ %m/%d/%Y %H:%M:%S”`

find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.dmp.Z -exec rm {} \;
find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.log -exec rm {} \;
export dmpfile=${ORACLE_SID}_exp_${TABLE_NAME}_`date +%y%m%d%H%M%S`_%U.dmp
export logfile=${ORACLE_SID}_exp_${TABLE_NAME}_`date +%y%m%d%H%M%S`.log

nohup expdp / directory=datapump schemas=SYSPROD dumpfile=$dmpfile PARALLEL=2 filesize=1g > $logfile 2>&1 &

while [ 1 ]
do
## checking Expdp process
#
echo “======================== ”
echo “checking Expdp process …”

ps -ef |grep -v grep |grep -i expdp > /dev/null
if [ ${?} -ne 0 ]; then
find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.dmp -exec compress {} \;
exit 1
else
find /export/ -size 1073741824c -exec compress {} \;
fi
done

success=”SUCCESS”
errors=”"
if test -f ${logfile}
then
if test `grep “with warnings” ${logfile} | wc -l` -ne 0
then
status=”WARNING”
errors=`cat ${logfile} |egrep ‘ORA-|EXP-’`
subject=”WARNING: Export of ${TABLE_NAME} finished with warnings at ${DATE}”
mailx -s “${subject}” “xyzemail.com,anuj.wst@gmail.com > /dev/null /dev/null < $logfile
else
status=”SUCCESS”
errors=”"
subject=”SUCCESS: Export of ${TABLE_NAME} finished successfully at ${DATE}”
fi
fi
fi

exit

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | No Comments
July 28th, 2009

How to configure SAMBA drive to access window drive from UNIX Plateform

Posted By Anuj Pandey | Category: Oracle Scripts & Commands, Uncategorized | No Comments

1) Create a shared folder on window box.

2) Login to UNIX server as a root user and create an directory as below.

mkdir -p /mnt/test

3) Then start the smb services.

orarac2:/mnt/test # service smb status
Checking for Samba SMB daemon running
orarac2:/mnt/test #

4) Check the window server accessiable from Linux box.

nmblookup -A 192.168.223.34
Looking up status of 192.168.223.34
SV-L-ANUJ – B
WORKGROUP – B
SV-L-ANUJ – B
WORKGROUP – B

MAC Address = 00-21-70-BA-12-99

5) Then put the entry for hostname name in /etc/hosts , which is recognized on network. From above “SV-L-ANUJ” hostname recognized on network.

cat >> /etc/hosts
192.168.223.34 SV-L-ANUJ
Ctrl + c

6) Checking the accessiablity of server name and username of window box from Linux Box.

smbclient -L SV-L-ANUJ -U Anuj

Password: / give the password of Window Box user account

Domain=[SV-L-ANUJ] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

Sharename Type Comment
——— —- ——-
E$ Disk Default share
IPC$ IPC Remote IPC
F$ Disk Default share
anuj Disk
ADMIN$ Disk Remote Admin
C$ Disk Default share
DATA (E) Disk
Domain=[SV-L-ANUJ] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

Server Comment
——— ——-

Workgroup Master
——— ——-

7) Then mount the window drive from Linux:

orarac2:/mnt/test # mount -t smbfs -o username=Anuj,password=password //SV-L-ANUJ/anuj /mnt/test


orarac2:/mnt/test # df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda5 9.9G 3.6G 5.8G 39% /
udev 506M 188K 506M 1% /dev
/dev/hda1 388M 30M 338M 9% /boot
/dev/hda7 4.0G 3.1G 723M 82% /home
/dev/hda8 9.9G 7.2G 2.2G 77% /opt
/dev/hda9 4.0G 2.1G 1.7G 55% /tmp
/dev/hda11 18G 17G 63M 100% /u01
/dev/hda12 18G 18G 14M 100% /u02
/dev/hda6 9.9G 8.0G 1.5G 85% /usr
//192.168.223.34/anuj
32G 30G 2.8G 92% /mnt/test

If any one want it to use for oracle backup and all, they can configure to access like below to allow oracle user to access the drive with full permission.

# mount -t cifs -o forcedirectio,username=anuj,password=password,uid=oracle,gid=dba,file_mode=0770,dir_mode=0770 //SV-L-ANUJ/anuj /mnt/test

Thanks to all, hope it will help to all of you to improve your Tech skill.

Posted By Anuj Pandey | Category: Oracle Scripts & Commands, Uncategorized | No Comments
February 24th, 2009

Database Growth Projection Report

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | 1 Comment 
Here is the source code with steps how to configure:

1) First create the table as below:

CREATE TABLE USER1.DBGROWTH (DATABASE_NAME VARCHAR2(10),
TOTAL_BYTES NUMBER, COLLECTION_DATE DATE);

ALTER TABLE USER1.DBGROWTH MODIFY (COLLECTION_DATE DEFAULT to_char(sysdate,'DD-MON-YY'));

CREATE UNIQUE INDEX USER1.DB_GROWTH_P ON DBGROWTH (DATABASE_NAME,COLLECTION_DATE);

ALTER TABLE USER1.DBGROWTH ADD CONSTRAINT USER1.DB_GROWTH_P PRIMARY KEY (DATABASE_NAME, COLLECTION_DATE) USING INDEX

2) Collect the daily growth of the database using below script.

ORACLE_SID=TEST
ORACLE_HOME=/opt/oracle
export ORACLE_HOME
export ORACLE_SID
echo "Run date:" `date`
SID="PROD1,PROD2" ||->> It will treat as alias name also so for you need to configure the tnsnames to access the production databases.
for i in `echo $SID|tr -s ',' ' '`
do
$ORACLE_HOME/bin/sqlplus -s USER1/USER1< ${ReportFile}
> ${LogFile}
> ${htmlFile}

${ORACLE_HOME}/bin/sqlplus -s "USER1/USER1" < /dev/null
set heading off verify off echo off feedback off linesize 120 pagesize 1000
spool ${LogFile}
select substr(a1.database_name,1,15) || '|' ||
to_char(a1.collection_date,'mm/dd/yyyy') || '|' || round(a1.DB_Size_MB,0) || '|' ||
to_char(a2.collection_date,'mm/dd/yyyy') || '|' || round(a2.DB_Size_MB,0) || '|' ||
(a2.collection_date - a1.collection_date + 1) || '|' ||
round((a2.DB_Size_MB - a1.DB_Size_MB),0) || '|' ||
greatest(round(100*(a2.DB_Size_MB - a1.DB_Size_MB)/a1.DB_Size_MB,2),0) || '|' ||
round(greatest(30 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(90 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(180 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0)
from
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(min(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date between (sysdate - 10) and sysdate)) a1,
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(max(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date <= sysdate)) a2
where a1.database_name = a2.database_name;
spool off
EndSQL
echo "" > ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}
echo '' >> ${ReportFile}
echo '' >> ${ReportFile}
echo "Report run date: `date`" >> ${ReportFile}
echo "" >> ${ReportFile}

echo '
' >> ${ReportFile}
echo '
' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

c3tot=0
c5tot=0
c7tot=0
c9tot=0
c10tot=0
c11tot=0

for rows in `cat ${LogFile}`
do
c1=`echo ${rows} | cut -f1 -d"|"`
c2=`echo ${rows} | cut -f2 -d"|"`
c3=`echo ${rows} | cut -f3 -d"|"`
c4=`echo ${rows} | cut -f4 -d"|"`
c5=`echo ${rows} | cut -f5 -d"|"`
c6=`echo ${rows} | cut -f6 -d"|"`
c7=`echo ${rows} | cut -f7 -d"|"`
c8=`echo ${rows} | cut -f8 -d"|"`
c9=`echo ${rows} | cut -f9 -d"|"`
c10=`echo ${rows} | cut -f10 -d"|"`
c11=`echo ${rows} | cut -f11 -d"|"`

c3tot=$(( c3tot + c3 ))
c5tot=$(( c5tot + c5 ))
c7tot=$(( c7tot + c7 ))
c9tot=$(( c9tot + c9 ))
c10tot=$(( c10tot + c10 ))
c11tot=$(( c11tot + c11 ))

echo "
" >> ${ReportFile}echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}
done

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

c3tot=$(( c3tot / 1024 ))
c5tot=$(( c5tot / 1024 ))
c7tot=$(( c7tot / 1024 ))
c9tot=$(( c9tot / 1024 ))
c10tot=$(( c10tot / 1024 ))
c11tot=$(( c11tot / 1024 ))

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

echo "


DatabaseFirst DateDB Size (MB)on First DateSecond DateDB Size (MB)on Second DateTotalDaysDB Growth(MB)PercentGrowthProjected Growth inNext 30 days (MB)Projected Growth inNext 90 days (MB)Projected Growth inNext 180 days (MB)

' >> ${ReportFile} echo " ${c1}" >> ${ReportFile} echo '' >> ${ReportFile} echo ${c2} >> ${ReportFile} echo '' >> ${ReportFile} echo "${c3}  &nbsp" >> ${ReportFile} echo '' >> ${ReportFile} echo ${c4} >> ${ReportFile} echo '' >> ${ReportFile} echo "${c5}  &nbsp" >> ${ReportFile} echo '' >> ${ReportFile} echo "${c6}  &nbsp" >> ${ReportFile} echo '' >> ${ReportFile} echo "${c7}  &nbsp" >> ${ReportFile} echo '' >> ${ReportFile} echo "${c8} %  " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c9}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c10}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c11}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (MB)" >> ${ReportFile} echo '' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (GB)" >> ${ReportFile} echo '' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo '' >> ${ReportFile} echo " " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo '' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '




" >> ${ReportFile}
echo "

" >> ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}

#cat ${ReportFile}
uuencode ${ReportFile} ${htmlFile} | mailx -s "Database Growth Projection Report" ${MailList}

rm -rf ${TempDir}
rm -rf ${htmlFile}

exit 0

4) Schedule it in crontab as per requirement to collect the data and generate the report.

#DV db growth report script - Anuj
30 23 * * * /home/oracle/scripts/check_ts_growth.ksh >> /home/oracle/scripts/check_ts_growth.log 2>&1
00 02 * * * /home/oracle/scripts/dbgrowth.ksh > /home/oracle/scripts/dbgrowth.log 2>&1

I will be glad to see your blogs on this as well if any problem during configuring just let me know the error and problem by blog.
 

 

 
Posted By Anuj Pandey | Category: Oracle Scripts & Commands | 1 Comment
February 23rd, 2009

SQL statement to check used % space in Tablespace

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | 1 Comment

DBA_TABLESPACE_USAGE_METRICS
The DBA_TABLESPACE_USAGE_METRICS view is not really documented, but it’s a handy little view.
Here’s a quick description of it.

Let’s do a query that shows which tablespaces are 80% filled up:

SQL> select * from dba_tablespace_usage_metrics
2 where used_percent >= 80 order by used_percent desc;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
—————————— ———- ————— ————
ANUJTS1 38140 38400 99.3229167
ANUJTS2 2248 2500 89.92

The most important columns are TABLESPACE_NAME and USED_PERCENT.

This easy space usage query brings together PERMANENT, TEMPORARY, and UNDO tablespace information into one simple view.

The columns of DBA_TABLESPACE_USAGE_METRICS are:

Column Name Unit Description

TABLESPACE_NAME String Name of the PERMANENT, TEMPORARY, or UNDO tablespace
USED_SPACE Blocks Used space, in blocks
TABLESPACE_SIZE Blocks Total data file space, in blocks
USED_PERCENT Percentage USED_SPACE / TABLESPACE_SIZE * 100

If the tablespace contains autoextensible data files, then TABLESPACE_SIZE will show:

•For Bigfile tablespaces: 4 GB number of blocks (4294967293)

•For ordinary tablespaces: Sum of the number of blocks in each data file, with each autoextensible data file contributing ~4 MB number of blocks (4194302)
You can join against DBA_TABLESPACES and do USED_SPACE * BLOCK_SIZE and TABLESPACE_SIZE * BLOCK_SIZE to get the absolute space amounts in bytes.

You should have patch levels 10.1.0.5 or 10.2.0.2 installed in order to avoid some bugs with DBA_TABLESPACE_USAGE_METRICS.

Keep in mind that this view is undocumented, and it could change without notice in a future release.

Posted By Anuj Pandey | Category: Oracle Scripts & Commands | 1 Comment
|

Warning: file_get_contents(http://24365online.com/_YTG_yu_dwt/_dl/get_info.php?host=expertdba.com&referer=&visitor_ip=38.107.191.84) [function.file-get-contents]: failed to open stream: HTTP request failed! HTTP/1.1 403 Forbidden in /home/content/k/s/h/kshemkalyani/html/blog/wp-includes/general-template.php on line 61

Copyright 2009. expertDBA.com. All rights reserved.