Archived log generation volume and count report


Source : http://lefterhs.blogspot.co.id/2012/07/archived-log-generation-volume-and.html

V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven’t yet aged out from your control file.
We are going to use this view to generate a report, displaying some useful information about it.

BLOCKS column stores archived log’s size in blocks, BLOCK_SIZE stores block size in bytes, so if we multiply them (BLOCKS*BLOCK_SIZE) we get its size in bytes.
ARCHIVED indicates whether the log is archived and DELETED whether RMAN has deleted it.

The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven’t yet been deleted by RMAN.

Continue reading

Advertisements

Tips for DBMS_STATS


Taking statistics and have them updated is a very important task on most Oracle databases, the CBO (Cost Based Optimizer) evaluates the cost of several Execution Paths, this cost is mainly based on physical and logical Disk IO, CPU use and network IO, if our stats are not being taken or aren’t updated there is a possibility for the CBO to take a wrong decision.

Make sure you have the system parameter STATISTICS_LEVEL set to TYPICAL or ALL (default=typical), this parameter indicates the level of the statistics to be taking for both, database and OS.
Statistics can be taken with only one command for the whole database, and we can use several options to improve the quality and the time it takes to collect them.

Let’s start with the basic recommendations:
1st.- Use the option GATHER_AUTO
2nd.- If possible take them in parallel
3rd.- Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE in the ESTIMATE_PERCENT parameter

Continue reading

Bigfile Tablespaces in Oracle


Bigfile tablespaces are tablespaces with a single large datafile (up to 4G blocks). In contrast normal or smallfile tablespaces can have several datafiles, but each is limited in size. The system default is to create a smallfile tablespace. The SYSTEM and SYSAUX tablespace types are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace
Advantages of using Bigfile Tablespaces:
By allowing tablespaces to have a single large datafile the total capacity of the database is increased. An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity.It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database. Continue reading

RMAN DUPLICATE FROM Active Standby Database


Recently I have published post about issue with RMAN duplication of Oracle database when standby database was used as a target. Later in updates of mentioned post I have confirmed that with fix for BUG:11715084 everything works as expected.

Today I have decided to make post with step-by-step procedure with which I have tested mentioned feature – making standby database from standby database.
I will Simplify setup As Much As Possible(SAMAP):

  1. single machine will be used as a host for all 3 databases
  2. OMF(Oracle Managed Files) will be used for managing files location and file names
  3. FRA(Fast/Flash Recovery Area) will be used for archive logs

1) Network Setup

Because I use only single server – all modifications will be done locally.

I will configure two more databases: orcl2 and orcl3

Continue reading

PERFORMING A DATABASE CLONE USING A DATA GUARD PHYSICAL STANDBY DATABASE


Knowledge From : http://gavinsoorma.com/2012/04/performing-a-database-clone-using-a-data-guard-physical-standby-database/

A common DBA task is to perform regular clones and database refreshes of the production database for the purpose of setting up training or test or development environments.

If we are having a physical standby Data Guard environment, then we can easily offload the potentially I/O and CPU intensive backup process required for creating these clone or duplicate databases to the standby site.

Here are a few examples of using the physical Standby database in a Data Guard environment to create a clone of the primary production database.

In the first example we use RMAN to perform the backup and restore and in the second example we are using OS commands to just copy files ONLINE from Standby host to the target host.

Note that in 11g, we can take the backup of the control file from the Standby database. In 10g, we have to take the backup of the controlfile from the primary database.

Continue reading

QUERY DATAGUARD AND STANDBY STATUS


Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

——— ———— ———- ———- ———- ———-

ARCH      CLOSING               1      69479     932864        261

ARCH      CLOSING               1      69480     928768        670

ARCH      CLOSING               2      75336     933888        654

ARCH      CLOSING               2      78079     930816        842

ARCH      CLOSING               1      69475     943104         79

RFS       IDLE                  0          0          0          0

Continue reading

Just Clone Database – CREATE CONTROLFILE SET DATABASE


Just some idea, But It’s great for cloned database. You can read on Super fast Database Copying/Cloning Oracle Tips by Burleson Consulting.
This is idea for using CREATE CONTROLFILE SET DATABASE “NEW_DATABASE_NAME” RESETLOGS
Why I use it? because I use Oracle 9i (Don’t ask, why don’t I upgrade?) and No Archive Mode.

First step this , create controlfile to trace.

SQL> alter database backup controlfile to trace as ‘/tmp/ctrl.trc’;

Check in ‘/tmp/ctrl.trc’ file.

CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 256
MAXINSTANCES 1
MAXLOGHISTORY 1134
LOGFILE
GROUP 1 ‘/oradata/ORCL/redo11.log’ SIZE 20M,
GROUP 2 ‘/oradata/ORCL/redo21.log’ SIZE 20M
DATAFILE
‘/oradata/ORCL/system.dbf’,
‘/oradata/ORCL/undo.dbf’,
‘/oradata/ORCL/data01.dbf’,
‘/oradata/ORCL/index01.dbf’
CHARACTER SET US7ASCII
;

Continue reading