oracle DBA Tips (PART-II)

ORACLE DBA TIPS:- PART-2
----------------------------------
26.Retrieving Threshold Information
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

27.Viewing Alert Data
The following dictionary views provide information about server alerts:
DBA_THRESHOLDS lists the threshold settings defined for the instance.

DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.

DBA_ALERT_HISTORY lists a history of alerts that have been cleared.

V$ALERT_TYPES provides information such as group and type for each alert.

V$METRICNAME contains the names, identifiers, and other information about the
system metrics.

V$METRIC and V$METRIC_HISTORY views contain system-level metric values in
memory.

28.The following views can help you to monitor locks:

for getting information about locks, we have to run two scripts
utllockt.sql and catblock.sql
Lists the locks currently held by Oracle Database and outstanding
V$LOCK
requests for a lock or latch

Displays a session if it is holding a lock on an object for which
DBA_BLOCKERS
another session is waiting

Displays a session if it is waiting for a locked object
DBA_WAITERS

Lists all DDL locks held in the database and all outstanding
DBA_DDL_LOCKS
requests for a DDL lock

Lists all DML locks held in the database and all outstanding
DBA_DML_LOCKS
requests for a DML lock

Lists all locks or latches held in the database and all outstanding
DBA_LOCK
requests for a lock or latch

Displays a row for each lock or latch that is being held, and one
DBA_LOCK_INTERNAL
row for each outstanding request for a lock or latch

Lists all locks acquired by every transaction on the system
V$LOCKED_OBJECT


29.Process and Session Views
v$process
v$locked_object
v$session
v$sess_io
v$session_longops
v$session_wait
v$sysstat
v$resource_limit
v$sqlarea
v$latch

30.What Is a Control File?
Every Oracle Database has a control file, which is a small binary file that records the
physical structure of the database. The control file includes:
The database name

Names and locations of associated datafiles and redo log files

The timestamp of the database creation

The current log sequence number

Checkpoint information

31.The following views display information about control files:
V$DATABASE Displays database information from the control file

V$CONTROLFILE Lists the names of control files


V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections

V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter

32.Redo Log Contents
Redo log files are filled with redo records. A redo record, also called a redo entry, is
made up of a group of change vectors, each of which is a description of a change made
to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the
database, including the undo segments. Therefore, the redo log also protects rollback
data. When you recover the database using redo data, the database reads the change
vectors in the redo records and applies the changes to the relevant blocks.

33.Log Switches and Log Sequence Numbers
A log switch is the point at which the database stops writing to one redo log file and
begins writing to another. Normally, a log switch occurs when the current redo log file
is completely filled and writing must continue to the next redo log file.
You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a
log switch occurs and LGWR begins writing to it. When the database archives redo log
files, the archived log retains its log sequence number. A redo log file that is cycled
back for use is given the next available log sequence number.


34. Setting the Size of Redo Log Members
The minimum size permitted for a redo log file is 4 MB.

35.Setting the ARCHIVE_LAG_TARGET Initialization Parameter
The ARCHIVE_LAG_TARGET initialization parameter specifies the target of how many
seconds of redo the standby could lose in the event of a primary shutdown or failure if
the Oracle Data Guard environment is not configured in a no-data-loss mode. It also
provides an upper limit of how long (in seconds) the current log of the primary
database can span. Because the estimated archival time is also considered, this is not
the exact log switch time.
The following initialization parameter setting sets the log switch interval to 30 minutes
(a typical value).
ARCHIVE_LAG_TARGET = 1800
A value of 0 disables this time-based log switching functionality. This is the default
setting.
You can set the ARCHIVE_LAG_TARGET initialization parameter even if there is no
standby database. For example, the ARCHIVE_LAG_TARGET parameter can be set
specifically to force logs to be switched and archived.

36.Verifying Blocks in Redo Log Files
If you set the initialization parameter DB_BLOCK_CHECKSUM to TRUE, the database
computes a checksum for each database block when it is written to disk, including
each redo log block as it is being written to the current log. The checksum is stored the header of the block.

Oracle Database uses the checksum to detect corruption in a redo log block. The
database verifies the redo log block when the block is read from an archived log
during recovery and when it writes the block to an archive log file. An error is raised
and written to the alert log if corruption is detected.

37.Clearing a Redo Log File
A redo log file might become corrupted while the database is open, and ultimately
stop database activity because archiving cannot continue. In this situation the ALTER
DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without
shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
If there are only two log groups
■
The corrupt redo log file belongs to the current group
■
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the
statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared
redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer
recover from that backup. The database writes a message in the alert log describing the
backups from which you cannot recover.
Note:
If you clear an unarchived redo log file, you should make
another backup of the database.
If you want to clear an unarchived redo log that is needed to bring an offline
tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER
DATABASE CLEAR LOGFILE statement.

38.Viewing Redo Log Information
Displays the redo log file information from the control file
V$LOG
Identifies redo log groups and members and member status
V$LOGFILE
Contains log history information
V$LOG_HISTORY

39.You can use archived redo logs to:
Recover a database

Update a standby database

Get information about the history of a database using the LogMiner utility

40.Changing the database ARCHIVING mode:
(1) shutdown
(2) startup mount
(3) alter database archivelog;
(4) alter database open;

41.Performing Manual Archiving
ALTER DATABASE ARCHIVELOG MANUAL;
ALTER SYSTEM ARCHIVE LOG ALL;

note:When you use manual archiving mode, you cannot specify any standby databases in
the archiving destinations.

42.Understanding Archive Destination Status
Each archive destination has the following variable characteristics that determine its
status:
Valid/Invalid: indicates whether the disk location or service name information is
■
specified and valid
Enabled/Disabled: indicates the availability state of the location and whether the
■
database can use the destination
Active/Inactive: indicates whether there was a problem accessing the destination
■
Several combinations of these characteristics are possible. To obtain the current status
and other information about each destination for an instance, query the
V$ARCHIVE_DEST view.

The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization
parameter lets you control the availability state of the specified destination (n).
ENABLE indicates that the database can use the destination.
■
DEFER indicates that the location is temporarily disabled.
■
ALTERNATE indicates that the destination is an alternate.
■
The availability state of the destination is DEFER, unless there is a failure of its parent destination, in which case its state becomes ENABLE.

43.Viewing Information About the Archived Redo Log
You can display information about the archived redo logs using the following sources:
(1)Dynamic Performance Views

(2)The ARCHIVE LOG LIST Command

Dynamic Performance Views
-------------------------
Shows if the database is in ARCHIVELOG or NOARCHIVELOG
V$DATABASE
mode and if MANUAL (archiving mode) has been specified.

Displays historical archived log information from the control
V$ARCHIVED_LOG
file. If you use a recovery catalog, the RC_ARCHIVED_LOG
view contains similar information.

Describes the current instance, all archive destinations, and
V$ARCHIVE_DEST
the current value, mode, and status of these destinations.

Displays information about the state of the various archive
V$ARCHIVE_PROCESSES
processes for an instance.

Contains information about any backups of archived logs. If
V$BACKUP_REDOLOG
you use a recovery catalog, the RC_BACKUP_REDOLOG
contains similar information.

Displays all redo log groups for the database and indicates
V$LOG
which need to be archived.

Contains log history information such as which logs have
V$LOG_HISTORY
been archived and the SCN range for each archived log.

44.Bigfile Tablespaces
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks)
datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles,
but the files cannot be as large. The benefits of bigfile tablespaces are the following:
A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile
tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum
number of datafiles in an Oracle Database is limited (usually to 64K files).
Therefore, bigfile tablespaces can significantly enhance the storage capacity of an
Oracle Database.

45.Altering a Bigfile Tablespace
Two clauses of the ALTER TABLESPACE statement support datafile transparency
when you are using bigfile tablespaces:

RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile
tablespace to an absolute size, without referring to the datafile. For example:
ALTER TABLESPACE bigtbs RESIZE 80G;

AUTOEXTEND (used outside of the ADD DATAFILE clause):
With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD
DATAFILE clause. For example:
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

46.Identifying a Bigfile Tablespace
The following views contain a BIGFILE column that identifies a tablespace as a bigfile
tablespace:
DBA_TABLESPACES

USER_TABLESPACES

V$TABLESPACE

47.Temporary Tablespaces
You can view the allocation and deallocation of space in a temporary tablespace sort
segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies
the current sort users in those segments.

You also use different views for viewing information about tempfiles than you would
for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the
V$DATAFILE and DBA_DATA_FILES views.

48.Creating a Locally Managed Temporary Tablespace
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Altering a Locally Managed Temporary Tablespace
-----------------------------------------------
ALTER TABLESPACE lmtemp
ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
(or)
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

Assigning Default Temporary Tablespace:
--------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE lmttemp;

49.Multiple Temporary Tablespaces: Using Tablespace Groups:
A tablespace group enables a user to consume temporary space from multiple
tablespaces. A tablespace group has the following characteristics:

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 can specify a tablespace group name wherever a tablespace name would
appear when you assign a default temporary tablespace for the database or a
temporary tablespace for a user.

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.

50.Creating a Tablespace Group
------------------------------
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M
TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group
--------------------------------------
You can add a tablespace to an existing tablespace group by specifying the existing
group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY
TABLESPACE or ALTER TABLESPACE statement.
The following statement adds a tablespace to an existing group. It creates and adds
tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and
lmtemp3.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
SIZE 25M
TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case
because tablespace lmtemp2 already belongs to group1, it is in effect moved from
group1 to group2:

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only
tmtemp3.
You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer
any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace
----------------------------------------------------------------
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

regards,
rajeshkumar g

Related topics:
http://oracleinstance.blogspot.com/2009/12/oracle-dba-tips-for-day.html