Calculate all Datafile Size Individually

Whenever you say the size of the database it means the summation of the datafiles. An oracle database consists of data files, redo logfiles, control files, temporary files. Database's size comes actually from these datafiles. To know about the size of all datafiles:

SQL>select sum(bytes)/1024/1024 from dba_data_files;

Size of all Temp Files:
SQL>select sum(bytes)/1024/1024 "Meg" from dba_temp_files;

Size of the on-line redo-logs:
SQL>select sum(bytes)/1024/1024 "Meg" from v$log;

Size of the control files:
SQL>select sum(BLOCK_SIZE*FILE_SIZE_BLOCK/1024/1024) "Meg" from v$controlfile;

To get the total size of the database:
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

Temporary Tablespace Infomation using Query

1) To know how much space is used in temporary segments:

SQL>Select extent_size*8192*used_extents/1024/1024/1024"space used in GigaByte" from v$sort_segment;
Space used in GigaByte

SQl>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_blocks, total_blocks from v$sort_segment; 

Tablespace_name Extent_size Total_extents Used_extents Free_extents Max_used_blocks Total_blocks
--------------- ----------- ------------- ------------- ----------- ---------------- ------------
Test        128         11896          4526           7370      1449216          1522688

2) To know SQL ID and Which type of Sort Segment issue:

SQL>select username,sqladdr, sql_id, tablespace, segtype, extents,blocks from v$tempseg_usage;

3) To know which SQL is using Temporary segments query:

SQL>select s.sql_text, t.username, t.tablespace, t.segtype, t.blocs, t.extents 
from v$sql s, v$tempseg_usage t
where t.sql_id=s.sql_id;

4) Information about tablespace containing sort segments:

SQL>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size from v$sort_segmrnt;

5)The users who is performng sort operation in Temp Segments:

SQL>select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid, a.serial#,a.username, a.osuser, a.status from v$session a,v$sort_usage b
where a.saddr=b.session_addr
order by b.tablespace, b.segfile#, b.segblk#,b.blocks;

Free space in Temporary Tablespace

A default temporary tablespace is specified in a database at the time of the database creation,By using DEFAULT TEMPORARY TABLESPACE in the create database statement.You can drop the default temporary tablespace,then system tablespace will be used as default temporary tablespace.Default temporary tablespace cannot make permanent or take it offline.The DBA_FREE_SPACE view allows you to show about free space in a tablespace but it shows only about permanent tablespace. You have to query V$TEMP_SPACE_HEADER view to know about the free space of temporary tablespace.You can see the use of DBA_FREE_SPACE and V$TEMP_SPACE_HEADER view as follows:

1) If you have TEST as permanent tablespace and TEMP as temporary tablespace and you run query DBA_FREE_SPACE then it will show TEST in list the list but TEMP is not listed.
SQL>select tablespace_name from dba_free_space;


4 rows selected.

2) To know information about free space of temporary tablespace

SQL>select tablespace_name.file_id,bytes_used,bytes_free from v$temp_space_header;

----------------- -------- --------- ------------
TEMP              5894714  19625687

Data Dictionary Creation In Oracle

Oracle creates data dictionary automatically when you create database by using Database Configuration Assistant method.But when you create database manually then you must run several scripts to create data dictionary objects.
There are three scripts you use to create data dictionary objects:
  • SQL>@?/rdbms/admin/catalog.sql: Creates the data dictionary and public synonyms for many of its views.
  • SQL>@?/rdbms/admin/catproc.sql: Creates all data dictionary objects required for PL/SQL.
  • SQL>@?/rdbms/admin/catclust.sql: Creates REAL Application Clusters data dictionary views.

The data dictionary base tables are the first objects created in any Oracle database. They are created in system tablespace

Optimizer Statistics

To know about the database and the objects in the database we use optiimizer statistics.In simple optimzer statistics provides information about the database and the objects in the database. For every SQL statement there is execution plan, that execution plan is decided by query optimizer.Optimzer statistics include the Table statistics, Index statistics, Column statistics, System statistics:

- Table statistics
  • Number of rows
  • Number of blocks
  • Average row length 
- Column statistics
  • Number of distinct values(NDV) in column
  • Number of nulls in column
  • Data distribution
- Index statistics 
  • Number of leaf blocks
  • Levels
  • Clustering factor 
- System statistics
  • I/O performance and utilization 
  • CPU performance and uitilization 
By using data dictionary you can view the optimizer statistics that are stored in the data dictionary and the views are:


Unregister Target Database and Reovery Catalog with RMAN

You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.

So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog.

Unregistering a Target Database from the Recovery Catalog
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.
rman target / CATALOG cat_user/cat_passwd@catdb
Recovery Manager: Release
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb1 (DBID=1489461517)
connected to recovery catalog database



Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.

Resynchronizing the Recovery Catalog
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.

starting full resync of recovery catalog
full resync complete

Related Post:-

Register Target Database and Recovery Catalog with RMAN

After creating the recovery catalog the first step in using a recovery catalog with a target database is registering the database in the recovery catalog.To register database you have to follow fews steps and they are:-
1) The recovery catalog database must be open.
2) Connect RMAN to both the target database and recovery catalog database.

rman target/catalog cat_user/cat_passwd@catdb

Recovery Manager: Release - Production

Copyright (c) 1995, 2002, Oracle Corporation, All rights reserved.

connected to target database: catdb (DBID=1489461517)
connected to recovery catalog database

3) If this target database has not registered then register the target database, it should be registered in the connected recovery catalog

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete.

Related Post:

Create Recovery Catalog

A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. Recovery catalog is optional. RMAN can be used either with or without a recovery catalog. An experienced DBA suggest that the Enterprise Manager instance schema and RMAN catalog schema  placed in the same utility database on a server separate from the main servers. There are few steps to create recovery catalog as follow:

  •     Configure Recovery Catalog
  •     Create Recovery Catalog owner
  •     Create Recovery Catalog  

 1)Create a database called cat and connect sqlplus SYS/pass@cat as SYSDBA

 2)Create a tablespace where all information of receovery catalog will be collected.

SQL>create tablespace cat_tbs datafile '/opt/cat/cattbs.dbf' size 100m;  
Tablespace created.

 3)Create a user in the recovery catalog database

SQL>create user cat_user identified by cat_passwd
 temporary tablespace temp default tablespace cat_tbs 
 quota unlimited on cat_tbs;  
User Created.

 4)Grant the recovery catalog owner role to the user.This will provide all privileges to maintain and query recovery catalog.

SQL>grant recovery_catalog_owner to cat_user;

Grant succeeded.

 5)Connect to the database that contains the catalog owner.

 rman catalog cat_user/cat_passwd@catdb

 6)Connect from the RMAN utility prompt

RMAN>connect catalog cat_user/cat_passwd
7)Run the create catalog command to create the catalog.May it take several minutes.

 RMAN>create catalog;

recovery catalog created

Related Post:-

Using tablespace with a temporary tablespace group

A tablespace group enables a user to consume temporary space from multiple tablespaces.It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
The following statement adds a tablespace to an existing group. It creates and adds tablespace temp to group1, so that group1 contains tablespaces temp2 and temp3.

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE '/opt/oracle/data/temp301.dbf' SIZE 25M
The following statement also adds a tablespace to an existing group, but in this case because tablespace temp2 already belongs to group1, it is in effect moved from group1 to group2:

Now group2 contains both temp and temp2, while group1 consists of only temp3.

You can remove a tablespace from a group as shown in the following statement:


Below syntax assigns a user to a default temporary tablespace group.



Twitter Delicious Facebook Digg Stumbleupon Favorites More