Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for June, 2012

Alter database VS Alter system

Posted by FatDBA on June 30, 2012

Alter database VS Alter system

An “alter system” command is (mostly) only possible in status OPEN. The only exception from this I recall presently is  “alter system set param=value” to modify initialization parameters. That is already possible in NOMOUNT status.
“alter database” on the other hand is already possible in status MOUNT,

ALTER SYSTEM is an Instance Level command. Generally this applies for running processes, parameters etc. (however “ALTER SYSTEM DUMP” seems to be an exception).

ALTER DATABASE is a Database Level command. Generally, this applies to the physical structure of the database.

Think of an RAC environment. Most ALTER SYSTEM commands (ALTER SYSTEM DUMP is one exception) are local to the instance (although SET can set for multiple instances). The ALTER DATABASE commands are for the whole database.

Advertisement

Posted in Basics | Tagged: | Leave a Comment »

High-water Mark

Posted by FatDBA on June 30, 2012

High-water Mark —–

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

Example.
for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,  At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.

Posted in Advanced, Basics | Tagged: | Leave a Comment »

Dig AWR (Auto Workload Repository) …

Posted by FatDBA on June 28, 2012

Oracle 10g provides the AWR. The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs.

Historically people used bstat and estat to collect Oracle statistics over a time period and then compare them to each other. The bstat/estat approach was replaced with statspack available in Oracle 8i. Statspack was a package provided
by Oracle that did roughly the same thing but better. Statspack has now been surpassed in functionality by AWR which is always collecting execution statistics for future analysis and tuning performed by all of the expert components
provided by Oracle. Oracle recommends that all statspack users switch to AWR in 10g.

The metrics collected by AWR are easily obtained from v$metricname:

AWR is automatically installed and running with 10g. The new MMON process is responsible for collecting data and populating the AWR. The MMON process takes snapshots of performance data at regular intervals and inserts that data into the AWR tables. The tables containing AWR information are stored in the SYSAUX tablespace (also new in 10G) under the SYS schema. There are a ton of tables in this tablespace – 806 to be exact. However, the AWR related tables all begin with “WR”:

WRM$_WR_CONTROL       WRH$_TEMPFILE       WRI$_SQLSET_BINDS

The third letter of each table name signifies the type of data that it contains.
• I – advisory functions (SQL Advice, Space Advice, etc)
• M – metadata information
• H – historical data

The frequency of data collection is 60 minutes by default but that can be adjusted. All functionality for driving the workload repository is done via the Oracle supplied package DBMS_WORKLOAD_REPOSITORY. In 11g version, These snapshots are retained for 8 days. 7 days is the retention period in 10g.

The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control.

Posted in Advanced | Tagged: | 2 Comments »

Difference between awrrpt.sql and awrrpti.sql ?

Posted by FatDBA on June 28, 2012

Difference between awrrpt.sql and awrrpti.sql?

The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on.

AWR Report types (Most Commonly used Reports):

  • The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
  • The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
  • The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
  • The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
  • The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
  • The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
  • The awrgrpt.sql  Generate AWR Cluster aggregated statistics from all the instances.
  • The awrgdrpt.sql  report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database.

SQL> @awrsqrpt.sql —- Generate AWR Report Single Select Statement:

Run the awrsqrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids intervals and the SQL_ID for the specific SELECT statement …

To check SQL ID —

SQL> select SQL_ID  from V$SQLAREA where rownum =1 ;

SQL_ID
————-
1fkh93md0802n

Posted in Advanced, Basics, Uncategorized | Tagged: | Leave a Comment »

Oracle Storage Hierarchy

Posted by FatDBA on June 27, 2012

Image

Posted in Basics | Tagged: | Leave a Comment »

ORA-09925 – Fixed (Error during Instance Creation)

Posted by FatDBA on June 27, 2012

ERROR: ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 2: No such file or directory Additional information:  9925 ORA-01075: you are currently logged on

Resolution: Log in as Root and grant 775/777 mode (Permission) to audil_trail_dest=adump folder.

Posted in Advanced | Tagged: , | Leave a Comment »

RMAN (Do’s & Dont’s)

Posted by FatDBA on June 26, 2012

No SPFILE before control file if both are backed up at the same time.

RMAN> backup current spfile controlfile;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “spfile”: expecting one of: “controlfile”
RMAN-01007: at line 1 column 16 file: standard input

RMAN> backup current controlfile spfile;

Starting backup at 29-MAY-12
using channel ORA_DISK_1
….

================================================================
* RMAN don’t support PFILE backups. SPFILE is only support parameter type in RMAN.

* RMAN does not backup TEMP tablespaces.
That is because “TEMP” was not backed up at the outset. RMAN does not backup Temporary Tablespaces. Such tablespaces do NOT need to be backed up, and, therefore are not restored or cloned.

* Redo logs can’t be backed up using RMAN but REDO Archivelogs can.

Posted in Advanced, Basics, Uncategorized | Tagged: , | Leave a Comment »

 
%d bloggers like this: