When Does DBWR Write?

When Does Database Writer Write?
The DBWn background process writes to the datafiles whenever one of the following events occurs:

 A user’s Server Process has searched too long for a free buffer when reading a buffer into
the Buffer Cache.
 The number of modified and committed, but unwritten, buffers in the Database Buffer
Cache is too large.
 At a database Checkpoint event. See Chapters 10 and 11 for information on checkpoints.
 The instance is shut down using any method other than a shutdown abort.
 A tablespace is placed into backup mode.
 A tablespace is taken offline to make it unavailable or changed to READ ONLY.
 A segment is dropped.

When Does LGWR Write?

When Does Log Writer Write?

The LGWR background process writes to the current redo log group whenever one of the
following database events occurs:

 Every three seconds.
 A user commits a transaction.
 The Redo Log Buffer is one-third full.
 The Redo Log Buffer contains 1MB worth of redo information.
 Before the DBWn process whenever a database checkpoint occurs. See Chapter 10 for more
information on checkpoints.

Oracle Physical Files

Oracle Physical Files

Controlfiles: Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database.

Datafile: All application data and internal metadata.

Redo log files: Record of all changes made to the database; used for recovery.

Parameter (PFILE or SPFILE): Configuration parameters for the SGA, optional Oracle features,and background processes.

Archived logfiles: Copy of the contents of previous online redo logs, used for recovery.

Password Optional file : used to store names of users who have been
granted the SYSDBA and SYSOPER privileges.

Control Files

Control files are critical components of the database because they store important information
that is not available anywhere else. This information includes the following:

1.The name of the database
2.The names, locations, and sizes of the datafiles and redo log files
3.Information used to recover the database in the case of a disk failure or user error

The control files are created when the database is created in the locations specified in the
control_files parameter in the parameter file. Because loss of the control files negatively
impacts the ability to recover the database, most production databases multiplex their control files to multiple locations. Oracle uses the CKPT background process to automatically update each of these files as needed, keeping the contents of all copies of the control synchronized. You can use the dynamic performance view V$CONTROLFILE to display the names and locations of all the database’s control files.
A sample query of V$CONTROLFILE on a Unix system is shown here:

SQL> select name from v$controlfile;

NAME
------------------------------------
/orcl/MARS/control/control01.ctl
/orcl/MARS/control/control02.ctl
/orcl/MARS/control/control03.ctl


One thing that the control files keep track of in the database are the names, locations, and
sizes of the database datafiles. Datafiles, and their relationship to another database structure
called a tablespace.


Datafiles
Datafiles are the physical files that actually store the data that has been inserted into each table in the database. The size of the datafiles is directly related to the amount of table data that they store. Datafiles are the physical structure behind another database storage area called a tablespace. A tablespace is a logical storage area within the database. Tablespaces group logically related segments.

Common tablespaces are : SYSTEM,SYSAUX, TEMP, TOOLS,USER,UNDOTBS1


SQL> select tablespace_name from dba_tablespaces order by tablespace_name;
TABLESPACE_NAME
------------------------------
APPL_IDX

APPL_TAB
EXAMPLE
SYSAUX
SYSTEM
TEMP
UNDOTBS1
7 rows selected.

or you can try following sql command for more info.

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;

Redo Log Files
Whenever a user performs a transaction in the database, the information needed to reproduce
this transaction in the event of a database failure is automatically recorded in the Redo Log
Buffer. The contents of the Redo Log Buffer are ultimately written to the redo logs by the
LGWR background process. Because of the important role that redo logs play in Oracle’s recovery mechanism, they are usually multiplexed, or copied. This means that each redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost due to a hardware failure. Collectively, these sets of redo logs are referred to as redo log groups. Each multiplexed file within the group is called a redo log group member. Oracle automatically writes to all members of the redo log group to keep the files in sync. Each redo log group must be composed of one or more members. Each database must have a minimum
of two redo log groups because redo logs are used in a circular fashion.

SQL> select * from v$logfile ;

SQL> select * from v$log ;

5 mandatory Oracle Background Processes

System Monitor SMON: Performs instance recovery following an instance crash, coalesces
free space in the database, and manages space used for sorting

Process Monitor PMON: Cleans up failed user database connections

Database Writer DBWn* : Writes modified database blocks from the SGA’s Database Buffer Cache to the datafiles on disk

Log Writer LGWR : Writes transaction recovery information from the SGA’s Redo Log Buffer
to the online Redo Log files on disk

Checkpoint CKPT : Updates the database files following a Checkpoint Event


On Unix systems, you can view these background processes from the operating system using
the ps command, as shown here:

$ ps -ef grep MARS

oracle 3969 1 0 10:02 ? 00:00:05 ora_pmon_MARS
oracle 3971 1 0 10:02 ? 00:00:00 ora_mman_MARS
oracle 3973 1 0 10:02 ? 00:00:07 ora_dbw0_MARS
oracle 3975 1 0 10:02 ? 00:00:07 ora_lgwr_MARS
oracle 3977 1 0 10:02 ? 00:00:10 ora_ckpt_MARS
oracle 3979 1 0 10:02 ? 00:00:20 ora_smon_MARS
oracle 3981 1 0 10:02 ? 00:00:00 ora_reco_MARS
oracle 3983 1 0 10:02 ? 00:00:09 ora_cjq0_MARS
oracle 3985 1 0 10:02 ? 00:00:00 ora_d000_MARS
oracle 3987 1 0 10:02 ? 00:00:00 ora_s000_MARS
oracle 4052 1 0 10:02 ? 00:00:00 ora_qmnc_MARS
oracle 4054 1 0 10:02 ? 00:00:29 ora_mmon_MARS
oracle 4057 1 0 10:02 ? 00:00:08 ora_mmnl_MARS
oracle 4059 1 0 10:02 ? 00:01:04 ora_j000_MARS
oracle 27544 1 0 20:29 ? 00:00:00 ora_q000_MARS

This output shows that 15 background processes are running on the Unix server for the MARS
database.

Parameters in PFILE/SPFILE

many initialization parameters are used to specify the size of the SGA and its components. Any parameters not specified in the PFILE or SPFILE take on their default values. The following is an example of the contents of a typical Unix Oracle 10g PFILE that contains both basic and advanced parameters:

db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_name=MARS
background_dump_dest=/u01/app/oracle/admin/MARS/bdump
core_dump_dest=/oracle/admin/MARS/cdump
user_dump_dest=/oracle/admin/MARS/udump
control_files=(/vitaldata/c1/MARS/control01.ctl, /vitaldata/c2/MARS/control02.ctl,
/vitaldata/c3/MARS/control03.ctl)
db_recovery_file_dest=/oracle/flash_recovery_area/
db_recovery_file_dest_size=2147483648
job_queue_processes=10
compatible=10.1.0.2.0
sga_target=500M
max_sga_size=800M
processes=250
remote_login_passwordfile=EXCLUSIVE
pga_aggregate_target=25165824
sort_area_size=65536
undo_management=AUTO
undo_tablespace=UNDOTBS1


SQL> select * 2 from V$SGA;

NAME VALUE
-------------------- ----------
Fixed Size 787988
Variable Size 145750508
Database Buffers 25165824
Redo Buffers 262144


SQL> select component,current_size from v$sga_dynamic_components;

PFILE & SPFILE

Data dictionary view VS Dynamic view

Dictionary Views vs Dynamic Performance Views:

1. Dictionary Views Dynamic Performance Views
The DBA_ views usually have plural names (for
example, DBA_DATA_FILES).

The names of the V$ views are generally singular
(for example, V$DATAFILE).

2. The DBA_ views are available only when the
database is open and running.

Some V$ views are available even when the
database is not fully open and running.

3. The data contained in the DBA_ views is generally
uppercase.

The data contained in the V$ views is usually
lowercase.

4. The data contained in the DBA_ views is static
and is not cleared when the database is shut
down.

The V$ views contain dynamic statistical data
that is lost each time the database is shut
down.

Managing the Recycle Bin

what happens when the dropped objects take up all of that space?

The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.


Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.

If you want to purge the specific table named EMP from the recycle bin after its drop, you could issue

PURGE TABLE EMP;

or using its recycle bin name:

PURGE TABLE "BIN$09LuipandafgMALLLLLghg==$0";

This command will remove table TEST and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space. If, however, you want to permanently drop an index from the recycle bin, you can do so using:

purge index inx_emp_01;


which will remove the index only, leaving the copy of the table in the recycle bin.

Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace MYTBSP. You would issue:

PURGE TABLESPACE MYTBSP;


You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse-type environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:

PURGE TABLESPACE MYTBSP USER DIP;


A user such as DIP would clear his own recycle bin with

PURGE RECYCLEBIN;


You as a DBA can purge all the objects in any tablespace using

PURGE DBA_RECYCLEBIN;


As you can see, the recycle bin can be managed in a variety of different ways to meet your specific needs.

Flashback Table

The Flashback Table feature in Oracle Database 10g allows you to revive an accidentally dropped table.

First, let's see the tables in the present schema.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE


Now, we accidentally drop the table:


SQL> drop table recycletest;

Table dropped.


Let's check the status of the table now.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE


The table RECYCLETEST is gone but note the presence of the new table
BIN$04LhcpndanfgMAAAAAANPw==$0.

Here's what happened: The dropped table RECYCLETEST, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.

The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects.

Use the following command from the SQL*Plus prompt to see its content:


SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31


This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:


SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE


Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:


PURGE RECYCLEBIN;


But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:

DROP TABLE RECYCLETEST PURGE;


This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.

Other Uses of Flashback Tables

Flashback Drop Table is not limited to reversing the drop of the table. Similar to flashback queries, you can also use it to reinstate the table to a different point in time, replacing the entire table with its "past" version. For example, the following statement reinstates the table to a System Change Number (SCN) 2202666520.

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;


This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table. To find out how far you can flashback the table, you could use the versioning feature of Oracle Database 10g.