Check Oracle DB Growth

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 “TOTAL SIZE (MB)” from dba_Data_files

Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 “FREE SPACE (MB)” from dba_free_space

Step : 3 Calculate total size , free space and used space in tablespace
select t2.total “TOTAL SIZE”,t1.free “FREE SPACE”,(t1.free/t2.total)*100 “FREE (%)” ,(1-t1.free/t2.total)*100 “USED (%)”
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2

Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE% “
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth

Step : 8 Check everything goes fine.
select * from db_growth;

Check Result

Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format =’dd-mon-yyyy hh24:mi:ss’ ;

Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, ‘begin insert into db_growth select * from v_db_growth;commit;end;’, sysdate, ‘SYSDATE+ 24′, TRUE);
commit;
end;

Step: 11 View your dbms jobs and it’s other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth

Changing Hosts File On Windows VISTA

By default, if you try to modify your hosts file in Vista, it will not let you save it.  It tells you that you don’t have permission.  To successfully modify the hosts file, run notepad.exe as an administrator and open the file.

1)  Browse to Start -> All Programs -> Accessories
2)  Right click “Notepad” and select “Run as administrator”
3)  Click “Continue” on the UAC prompt
4)  Click File -> Open
5)  Browse to “C:\Windows\System32\Drivers\etc”
6)  Change the file filter drop down box from “Text Documents (*.txt)” to “All Files (*.*)”
7)  Select “hosts” and click “Open”
8)  Make the needed changes and close Notepad.  Save when prompted.

11G – SQL*PLUS Error logging

The SQL*Plus interface has several interesting 11g release innovations, including the new error logging feature and the incorporation of default SQL*Plus settings in the SQL*Plus executable itself instead of in the traditional glogin.sql file.

SQL*Plus Error Logging

When you’re troubleshooting code errors, it’s common to use the show errors command in SQL*Plus to identify the errors in a PL/SQL statement. Other than this, there was no way to check code errors, and the errors were not stored for later examination. In Oracle Database 11g,there’s a new SQL*Plus command called set errorlogging, which stores all errors resulting from the execution of any SQL, PL/SQL, and even SQL*Plus commands in a special error logging table.

By default, the set errorlogging command causes any query errors to be written to the default table SPERRORLOG. You can specify your own table name for the error logging table, instead of using this default table name. For each error, the error logging feature logs the following bits of information:

• The username

• The time when the error occurred

• The name of the script that contains the query, if a script was used

• A user-defined identifier

• The ORA, PLS, or SP2 error message

• The query statement that caused the error

By default, error logging is turned off, as you can see from the following query:

SQL> show errorlogging
errorlogging is OFF

You can turn error logging on with the set errorlogging command, as shown here:

SQL> set errorlogging on;

If you issue the show errorlogging command again to ensure that error logging has been successfully turned on, you’ll see something interesting:

SQL> connect hr/hr
SQL> show errorlogging
errorlogging is ON TABLE HR.SPERRORLOG

Not only does the database turn error logging on, but it also creates a new table called hr.sperrorlog to hold the error messages. The prefix to the error table is the same as the name of the schema owner who sets error logging on. In this case, we logged in as the user hr, so the error log is created in the hr schema.

The following example shows how to query the error logging table, sperrorlog, to retrieve the error messages and the SQL statements or PL/SQL code that generated those error messages:

SQL> select username,statement,message from sperrorlog;
USERNAME STATEMENT                                                       MESSAGE
——– ————————–                                                  ————————–
HR       create table employees as select * from employees ORA-00955: name is already used by an existing object

The first message indicates that the table creation statement failed because there is already a table with an identical name. The second message shows that the error was because of the presence of an invalid column name in a select statement.

11G – Active Database Duplicate

Parameters to change :

==============

DB_NAME
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
CONTROL_FILES
DB_RECOVERY_FILE_DEST
DB_BLOCK_SIZE

For example :

initdup.ora
————

*.audit_file_dest=’/u01/app/oracle/admin/dup/adump’
*.audit_trail=’db’
*.compatible=’11.1.0.0.0′
*.control_files=’/u01/database/dup/control01.ctl’,'/u01/database/dup/control02.ctl’,'/u01/database/dup/control03.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’dup’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=dupXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=419430400
*.nls_language=’ENGLISH’
*.nls_territory=’UNITED KINGDOM’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
DB_FILE_name_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’)
LOG_FILE_NAME_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’)

2. Create password file for auxillary instance.

$orapwd file=orapwdup entries=10

3. Make sure that the auxiliary instance can be connect using ORACLE NET. For this configure the tnsnames.ora accordingly. Confirm the connection to both the target and the auxiliary instance using sqlplus.

4. Start the auxiliary instance in NOMOUNT state

SQL > starup nomount pfile=’$ORACLE_HOME/dbs/initdup.ora

5. Start RMAN and connect to the database.

$ rman

RMAN> TARGET sys / sys@<targets database>;

RMAN> connect AUXILIARY sys/sys@<Auxiliary db>;

RMAN> connect CATALOG rman/rman@<Catalog db>;

6. Run the DUPLICATE command.

RMAN>DUPLICATE TARGET DATABASE TO ‘DUP’ FROM ACTIVE DATABASE DB_FILE_name_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’);

11G – Create PFILE / SPFILE from Memory

In 11G , you can create pfile or spfile from memory of running instance.

SQL> create pfile from memory;
File created.

You can also use the memory clause to create an spfile, as shown here:

SQL> create spfile from memory;
File created.

You need to have instance up and running to be able to create pfile or spfile from memory.

10G – Optimize Oracle UNDO Parameters

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying. Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries. This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO. However it is worth to tune the following important parameters The size of the UNDO tablespace The UNDO_RETENTION parameter Calculate UNDO_RETENTION for given UNDO Tabespace You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter: Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time! Actual Undo Size SELECT SUM(a.bytes) UNDO_SIZE FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = ‘UNDO’ AND c.status = ‘ONLINE’ AND b.name = c.tablespace_name AND a.ts# = b.ts#; UNDO_SIZE ———- 209715200 Undo Blocks per Second SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) UNDO_BLOCK_PER_SEC FROM v$undostat; UNDO_BLOCK_PER_SEC —————— 3.12166667 DB Block Size SELECT TO_NUMBER(value) DB_BLOCK_SIZE [KByte] FROM v$parameter WHERE name = ‘db_block_size’; DB_BLOCK_SIZE [Byte] ——————– 4096 Optimal Undo Retention 209‘715‘200 / (3.12166667 * 4′096) = 16′401 [Sec] Using Inline Views, you can do all in one query! SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / ACTUAL UNDO SIZE [MByte] ———————— 200 UNDO RETENTION [Sec] ——————– 10800 OPTIMAL UNDO RETENTION [Sec] —————————- 16401 Calculate Needed UNDO Size for given Database Activity If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity: Again, all in one query: SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / ACTUAL UNDO SIZE [MByte] ------------------------ 200 UNDO RETENTION [Sec] -------------------- 10800 NEEDED UNDO SIZE [MByte] ------------------------ 131.695313 The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

11G – Read Only Table

Prior to 11G you can have read only tablespace but not tables but from 11G you can read only tables as well.

SQL>create table test as select * from dba_registry;

SQL>alter table test read only;

The new column of DBA_TABLES would tell you that whether table is read only or not.

You can make table read write with following command.

SQL>alter table test read write;

Once you put a table in a read-only mode, you can’t issue any DML statements such as update, insert, or delete. You also can’t issue a select for update statement involving a readonly table. You can issue DDL statements such as drop table and alter table on a read-only table, however. You can use the read-only feature to prevent changes to a table’s data during maintenance operations. You can perform maintenance operations on any of the indexes that you have defined on the read-only table, prior to changing their status to read-only. You can, of course, use this feature for security reasons, where you want to grant users the ability to read but not modify table data.

9I – 10G Oracle Database Architecture

• Oracle 9I Architecture – Simple
• Oracle 9I Architecture – Complex

11G – Invisible Indexes

Suppose you want to create huge index on some table but don’t want optimizer to change its plan based on new index then you need to use 11G’s new feature called invisible index.

11G allows you to create index which would be invisible to optimizer.

• How to create invisible index

SQL> create index idx on tbl1(name) invisible;
Index created.

You can also specify a tablespace while creating the invisible index.

SQL> create index idx2 on tbl2(name) invisible tablespace users;
Index created.

The index idx2 is stored to users tablespace as just a normal index.

• Making index invisible

SQL> alter index idx1 invisible;
Index altered.

• Making index visible

SQL> alter index idx1 visible;
Index altered.

You cann’t create two index on same column even if one being invisible.

If you want optimizer to use invisible index while deciding query plan then you need to set following parameter enabled.

SQL> alter session set optimized_use_invisible_indexes=true;

SQL> alter system set optimized_use_invisible_indexes=true;

Finding Redo Log Space Contentions

• Check the status of redo logs

SQL>set linesize 130
SQL>col member for a50
SQL>col status for a12
SQL>col first_time for a15
SQL>select a.group#,to_char(b.first_time,’DDMonYY HH24:mm’) first_time,b.status,a.member,b.bytes/1024/1024 Megs from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;

GROUP# FIRST_TIME      STATUS       MEMBER                                                                MEGS
———-     —————      ————     ————————————————–              ———-
1     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo01_a.log              128
1     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo01_b.log              128
2     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo02_a.log              128
2     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo02_b.log              128
3     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo03_a.log              128
3     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo03_b.log              128
4     17Sep08 16:09   CURRENT    /export/dbredo1/oradata/dbf/redo04_a.log              128
4     17Sep08 16:09   CURRENT    /export/dbredo2/oradata/dbf/redo04_b.log              128
5     17Sep08 15:09   INACTIVE    /export/dbredo1/oradata/dbf/redo05_a.log              128
5     17Sep08 15:09   INACTIVE    /export/dbredo2/oradata/dbf/redo05_b.log              128
6     17Sep08 15:09   ACTIVE       /export/dbredo1/oradata/dbf/redo06_a.log              128

GROUP# FIRST_TIME      STATUS       MEMBER                                                   MEGS
———- ————— ———— ————————————————– ———-
6 17Sep08 15:09   ACTIVE       /export/dbredo2/oradata/dbf/redo06_b.log              128

12 rows selected.

• Find average redo switches per hour.

SQL>set feed off
SQL>col date for a10
SQL>select to_char(first_time,’DDMonYY’) as “DATE”,round((count(1)/24),0) avg_swcth_per_hour from V$LOG_HISTORY where first_time > sysdate -10 group by to_char(first_time,’DDMonYY’) order by to_date(to_char(first_time,’DDMonYY’),’DDMonYY’);

DATE       AVG_SWCTH_PER_HOUR
———-           ——————
07Sep08                     0
08Sep08                     1
09Sep08                     2
10Sep08                     2
11Sep08                     3
12Sep08                     2
13Sep08                     0
14Sep08                     0
15Sep08                     1
16Sep08                     3
17Sep08                     2

• Find log and redo buffer waits –for 10g

SQL>alter session SET NLS_DATE_FORMAT = ‘YYYY/MM/DD HH24:MI:SS’;

SQL>col event for a40 trunc
SQL>col TIME_WAITED for 999999999999

SQL>select wait_class,event,total_waits,total_timeouts,time_waited,round(time_waited/100/60,1) Time_Wait_Minutes,average_wait  from V$SYSTEM_EVENT where lower(event) like ‘%log%’or lower(event) like ‘%redo%’ order by 1;

WAIT_CLASS           EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS   TIME_WAITED TIME_WAIT_MINUTES               AVERAGE_WAIT
——————–    ——————————    ———–      ————–                   ————-          —————–                  ————
Commit                 log file sync                       45015          127                             95689               15.9            2.13
Configuration        log buffer space                 1362            23                              37210                6.2             27.32
Configuration        log file switch completion    403             0                                3641                  .6               9.04
Other                   latch: redo allocation           16               0                                0                        0               .03
Other                   LGWR wait for redo copy     2200           0                                14                      0               .01
System I/O           log file parallel write             64508         0                                115360               19.2         1.79
System I/O           Log archive I/O                     20829         0                               541                      .1             .03
System I/O           log file single write               254             0                               104                       0             .41
System I/O           log file sequential read         7835            0                               43688                  7.3           5.58

• Find Request for space contention

SQL>col name for a60
SQL>col value for 999999999999
SQL>select name,value from V$SYSSTAT where name IN (‘redo buffer allocation retries’, ‘redo log space requests’);

NAME                                                                    VALUE
————————————————————     ———-
redo buffer allocation retries                                  19723
redo log space requests                                         1900

The value of “redo log space requests” should be near 0. If this value increments consistently, processes have had to wait for space  in the buffer. This may be caused the checkpointing or log switching.  Improve thus the checkpointing or archiving process.

• Determine the miss ratio and the “immediate” miss ratio for redolog latches

SQL>SELECT substr(ln.name, 1, 20) statistic, gets, misses, immediate_gets, immediate_misses  FROM V$LATCH l, V$LATCHNAME ln WHERE ln.name in (‘redo allocation’, ‘redo copy’) and ln.latch# = l.latch#;

STATISTIC               GETS        MISSES     IMMEDIATE_GETS IMMEDIATE_MISSES
——————–       ———-    ———-    ————–          —————-
redo copy                600          0             4120777             2486
redo allocation         5236662   2940        0                       0

If the ratio of MISSES to GETS exceeds 1%,
or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_GETS + IMMEDIATE_MISSES) exceeds 1%, there is latch contention.

• Find amount of generated redo in current session.

SQL>col value for 999999999999
SQL>select n.name, t.value from v$mystat t join V$STATNAME n on t.statistic# = n.statistic# where n.name = ‘redo size’;

NAME                                                                     VALUE
—————————————————————- ————-
redo size                                                                    0