What is a Latch

Latch

Oracle SGA consists of numerous data structures such as Buffer Cache, Shared pool, Library cache, Dictionary cache, redo log buffer etc. Many oracle processes need to access these SGA data structures concurrently. Though these SGA components are shared, only one process must be able to modify it at any particular point in time. Oracle achieves this by protecting the SGA data structures with latches.

Latches do not allow multiple processes to inspect the protected data structures simultaneously. They provide only exclusive access. A latch is a kind of lock that can be very quickly acquired and freed.

When do Oracle Process obtain a latch?

Oracle sessions need to update or read from the SGA for almost all operations. For instance,

• When a session performs a physical read, it must modify a free block in the buffer cache and adjust the Buffer Cache LRU Chain.
• When a session reads a block from the SGA, it will modify the LRU chain.
• When a new SQL statement is parsed, it will be added to the library cache.
• As changes are made to blocks, entries are placed in the redo buffer.
• The DBWr and redo log writer writes buffers from buffer to disks.

Latches prevent any of these operations from colliding and possible corrupting the same area of SGA.

A process acquires a latch when working with a structure in the SGA. It continues to hold the latch for the period of time it works with the structure. The latch is dropped when the process is finished with the structure. Each latch protects a different set of data, identified by the name of the latch.

Latches are held for short periods of time and provide a mechanism for cleanup in case a holder dies abnormally while holding it. This cleaning is done using the services of PMON.

Latches Vs Locks

Request Queuing: Request for locks are queued and serviced in order, whereas there is no request queuing in latches the process requests for latch until it succeeds. Latches are serviced in random.

Management: Locks management is complex because of their support for queuing and concurrency. Latch management is simple, essentially just a single location in memory representing the state of a latch.

Because latches are very efficient Oracle uses latch to protect the data structures  that are expected to be accessed briefly and intermittently.

Thanks

Segment Availability during Shrink

The segment shrink is done online. During shrink operation, the conventional DML operations can coexist but parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase.
During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.

However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

NOTE: Shrinking space of a large segment can take a lot of time, e.g. tens of hours, and can generate lots of redo. Therefore, it is not advised to interrupt a shrink statement to prevent a possibly massive rollback.


Thanks

RMAN: Archivelogs lost

Problem: I have lost some of the archivelog files without taking backup. If I run the rman to backup available archive logs, it throws error that the archivelog_seq# is not available.

Solution: run the following command.

RMAN> change archivelog all validate;

Now you run the backup archivelog command. RMAN will backup the available archivelogs successfully.

Thanks

Drop Database in Oracle 10g

It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles, redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba
SQL> Shutdown immediate;
SQL> Startup Mount Exclusive Restrict;
SQL> Drop Database;

Thanks

Block Change Tracking

RMAN Incremental Backups backup only the blocks that were changed since the lastest base incremental backups. But RMAN had to scan the whole database to find the changed blocks. Hence the incremental backups read the whole database and writes only the changed blocks. Thus the incremental backups saves space but the reduction in the time is fairly neglegible.


Block Change Tracking (BCT) is a new feature in Oracle 10g. BCT enables RMAN to read only the blocks that were changed since the lastest base incremental backups. Hence by enabling BCT, RMAN reads only the changed blocks and writes only the changed blocks.


Without BCT, RMAN has to read every block in the database and compare the SCN in the block with the SCN in the base backup. If the block's SCN is greater than the SCN in the base backup then the block is a candidate for the new incremental backup. Usually only few blocks are changed between backups and the RMAN has to do unncessary work of reading the whole database.


BCT Stores the information about the blocks being changed inthe BlockChange Tracking File. The background process that does this logging is Change Tracking Writer (CWTR).


BlockChange Tracking File


BCT File is one per database and in the case RAC, it is shared among all the instances. BCT File is created in the location defined by the parameter DB_CREATE_FILE_DEST as OMF file.


To enable BCT


SQL> Alter Database Enable Block Change Tracking;


To disable BCT


SQL> Alter Database Disable Block Change Tracking;


To specify the BCT file location


SQL> Alter Database enable Block Change Tracking using File '/Backup/BCT/bct.ora';


A useful query,

SQL> Select Completion_time, datafile_blocks, blocks_read, blocks, used_change_tracking
From v$backup_datafile
where to_char(completion_time, 'dd/mon/yy') = to_char(sysdate, 'dd/mon/yy');

Where,
datafile_blocks is the total number of blocks in the datafile.
blocks_read is the total number of blocks read by RMAN
blocks is the total number of blocks backed up by the RMAN.
used_change_tracking if yes BCT is used, if no BCT is not used.

Thanks

RMAN Commands

The following are the commands you can type from the RMAN prompt.

{
allocate
alter
backup
beginline
blockrecover
catalog
change
connect
copy
create
crosscheck
configure
duplicate
debug
delete
drop
exit
endinline
host
library
list
mount
open
print
quit
recover
register
release
replace
report
renormalize
reset
restore
resync
rman
run
rpctest
set
setlimit
sql
spool
startup
shutdown
send
show
test
upgrade
validate

Thanks

RMAN List Command

RMAN List Command queries the target control file or the recovery catalog for the historical information on the backups. The various options you can use with List command and their uses are discussed below,

Listing Incarnations

A new incarnation of the database is created when you perform the open resetlogs operation. To view the list of database incarnations,

RMAN> List incarnation;

List Summary of Backups

The summary of backups include backupset key, the status, device type, completion time etc,

RMAN> List Backup Summary;
RMAN> List expired Backup of archivelog all summary;
RMAN> List Backup of tablespace Test summary;

List Backups of various files

It provides the summary of the backups available for each datafile, controlfile, archivelog file and spfile.

RMAN> List Backup By File;

Detailed Report

If you want the detailed report on the backups, then issue the following command.

RMAN> List Backup;

It lists the all available information about the backups.

Backups used for Recovery

To list the backups used for restore and recovery,

RMAN> list recoverable backup;

Expired Backups

The list backup shows both available and expired backups. To view only the expired backups,

RMAN> List expired Backup;
RMAN> List expired Backup summary;
RMAN> List expired Backup of Archivelog all;
RMAN> List expired Backup of datafile 10;

Listing Tablespace and Datafile Backups

RMAN> List Backup of Tablespace Test;
RMAN> List Backup of Datafile 4;

Listing Archivelog Backups

RMAN> List Archivelog all;
RMAN> List Archivelog all backedup 2 times to device type sbt;

Listing Controlfile and Spfile Backups

RMAN> List Backup of Controlfile;
RMAN> List Backup of Spfile;

The above list commands displayed information about the backusets. If you have performed Image copy backups then you must use the list copy command as shown below,

RMAN> List Copy;
RMAN> List Copy of database;
RMAN> List Copy of tablespace test;
RMAN> List Copy of archivelog all;
RMAN> List Copy of archivelog from sequence 12345;
RMAN> List Copy of archivelog from sequence 1000 until sequence 1010;
RMAN> List Copy of Controlfile;
RMAN> List Copy of Spfile;

Thanks

Connecting to RMAN

Step 1: Set the following environment variables

ORACLE_SID
ORACLE_HOME
NLS_DATE_FORMAT

Step 2: Access to privileged OS account or user with Sysdba privilege. Only users with Sysdba privilege can connect to RMAN. Hence no need to specify as sysdba while connecting to RMAN.

$ cd $ORACLE_HOME/bin
$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 20 03:19:22 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: IRIS (DBID=1460454352)

If you are using password file, then specify the username and password

$ rman target sys/syspassword

If you are accessing the target database via oracle net then use connection string as follows

$ rman target sys/syspassword@

You can also connect to RMAN in two steps first by invoking the rman

$ rman

The RMAN prompt appears

RMAN>

To connect to the target datbase..

RMAN> Connect target /
RMAN> Connect target sys/syspassword
RMAN> Connect target sys/syspassword@

Once you have connected to RMAN, you can start entering the commands. The commands can be typed in single line or in multiple lines and ends with a semicolon.

RMAN> Backup
2 database;

To add comments use the # symbol. Text after the # symbol in a line is interpreted as comments

RMAN> # Backup database
Backup database # this command will backup the database
include current controlfile; # backup control file along with database

Thanks

Remote_login_passwordfile

The password file is used to authenticate the users connecting to the database as Sysdba or Sysoper. The password file is created with the orapwd utility as follows,

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw password= entries=

Where,
file = the filename where the password is stored.
password = the password for sys user
entries = the number of users that can connect as sys user at a time. This parameter is optional.

Once the password file is created you need to set the init parameter remote_login_passwordfile for the database to use it.

The possible values for the parameter is

EXCLUSIVE - Only one database can use the password file. Multiple users can exist in the password file.

SHARED - More than one database can use the password file. However only Sys users allowed to have sysdba privilege. You will receive ORA-01999 error when you grant sysdba privilege to other users.

NONE - The password file is not used.

Thanks

ASM Views

The following V$views are used for managing ASM.

View Name	Description	Instance
V$ASM_DISKGROUP	Diskgroups discovered by ASM	DB
V$ASM_DISK	Disks discovered by ASM including disks that are not part of any Diskgroup.	DB
V$ASM_FILE	ASM files mounted by ASM instance	ASM
V$ASM_ALIAS	Filename alias of the ASM Files	ASM
V$ASM_OPERATION	Long running ASM operations	ASM
V$ASM_TEMPLATE	Template present in diskgroup	DB
V$ASM_CLIENT	Lists one row per each ASM instance for which the database instance has any open ASM files	DB

Thanks

Drop a file from diskgroup

Syntax

ALTER DISKGROUP diskgroup_name DROP FILE ‘filename’, ‘filename’..;

Note: ASM drops all the aliases associated with the filename.

Example 1

SQL> Alter Diskgroup ASMDATA DROP FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’ , ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Manage ASM Directories

In order to use ASM filename aliases, you need to create Directories.

Syntax:

To add directory
ALTER DISKGROUP diskgroup_name ADD DIRECTORY ‘filename’, ‘filename’..;

To rename directory
ALTER DISKGROUP diskgroup_name RENAME DIRECTORY ‘old_dirname’ TO ‘new_dirname’, ‘old_dirname’ TO ‘new_dirname’..;

To drop directory
ALTER DISKGROUP diskgroup_name DROP DIRECTORY ‘filename’ [FORCE or NOFORCE], ‘filename’ [FORCE or NOFORCE]..;

Where,

FORCE – To drop the directory even if it contains any alias definitions.
NOFORCE – It will not drop the directory if it contains any alias definitions

Example 1 (Add directory)

SQL> Alter Diskgroup asmdata ADD DIRECTORY ‘+ASMDATA/datafile’;

Example 2 (Rename directory)

SQL> Alter Diskgroup asmdata RENAME DIRECTORY ‘+ASMDATA/datafile’ TO ‘+ASMDATA/indexfile’;

EXAMPLE 3 (Drop directory)

SQL> Alter Diskgroup asmdata DROP DIRECTORY ‘+ASMDATA/indexfile’ FORCE;

Thanks

Mounting and Dismounting a Diskgroup

Diskgroups are automatically mounted at ASM instance startup and dismounted at ASM instance shutdown. To manually mount and dismount the diskgroups use the following statements,

Syntax:

ALTER DISKGROUP ALL or diskgroup_name MOUNT or DISMOUNT;

Example 1 (Mount a specific diskgroup)

SQL> Alter Diskgroup ASMIND Mount;

Example 2 (Dismount all diskgroups)

SQL> Alter Disgroup ALL DisMount;

Note

In the case of High and Normal Redundancy diskgroups you need all set of extents available for the diskgroups to mount. The unavailable disks may be used for redundant copies and you may mount the diskgroups without those disks. If there are sufficient disks available for the diskgroups to mount you can use the force option to mount it.

Alter Diskgroup diskgroup_name Mount Force;

The unavailable disks are taken offline. ASM drops those disks after the DISK_REPAIR_TIME expires.

In RAC ASM environments, you cannot MOUNT FORCE a disk group that is already mounted by another instance because the disks have been accessed by another instance and the disks are not locally accessible.

Thanks

Checking a Diskgroup

It checks the internal consistency of the diskgroup metadata. If any errors are found they are reported in the alert log.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name CHECK check_diskgroup_clause [REPAIR or NOREPAIR]

Where,

Check_dikgroup_clause= [ALL] or [DISK diskname] or [DISKS IN FAILGROUP failgroup_name] [FILE filename]

REPAIR = ASM attempts to repair any errors.
NOREPAIR= ASM reports the error in the alert log and does not attempt to repair it.

Example 1 (checks all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA CHECK ALL;

Example 2 (checks only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
DISK data_05,
DISK data_06 NOREPAIR;

Example 3 (Checks all the disks in the failure group).

SQL> Alter Diskgroup ASMDATA CHECK
DISKS IN FAILGROUP ctrl_1;

Example 4 (checks specific files in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’,
FILE ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Rebalance a Diskgroup

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name rebalance_diskgroup_clause

Where,

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA REBALANCE POWER 10 wait;

Thanks

Resize a Disk

Note: Resizing disks in a diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name RESIZE resize_disk_clause rebalance_diskgroup_clause

Where

resize_disk_clause =[ALL SIZE size_clause] or [DISK diskname SIZE size_clause] or [DISKS IN FAILGROUP failgroup_name SIZE size_clause]

Note: If you omit the size clause then ASM uses the size returned by the OS.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.


Example 1 (resizes all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA RESIZE ALL SIZE 100G;

Example 2 (resizes only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND RESIZE
DISK data_05 SIZE 100G,
DISK data_06
REBALANCE POWER 5;

Example 3 (resizes only specific disks in the failure group).

SQL> Alter Diskgroup ASMDATA RESIZE
DISKS IN FAILGROUP ctrl_1 SIZE 100G;

Thanks

Undrop Disk from Diskgroup

Note: To undrop a disk, the header_status from v$asm_disk view must be dropping. You cannot undrop a completely dropped disk.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP [ALL/diskgroupname, diskgroupname..] UNDROP DISKS

Example 1

SQL> Alter Diskgroup asmdata undrop disks;

Example 2

SQL> Alter Diskgroup ALL undrop disks;

Thanks

Add Disk to Diskgroup

Note: Adding new disks to diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name ADD [FAILGROUP failgroup_name] DISK add_disk_clause rebalance_diskgroup_clause

Where

Add_disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]
Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA ADD
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_05 NOFORCE,
'/u01/asmdisks/disk2' NAME data_06 NOFORCE;

Example 2

SQL> Alter Diskgroup ASMIND ADD
DISK '/dev/rhdisk33'
REBALANCE POWER 5 ;

Thanks

Dropping a Diskgroup in ASM

Instance: ASM Instance
Privilege: Sysdba
Syntax:

DROP DISKGROUP diskgroup_name INCLUDING/EXCLUDING CONTENTS;

Including Contents – Drops all the files associated with the disk group.
Excluding Contents – Drops the disk group only when it is empty. Default.

Example 1

SQL> Drop Diskgroup ASMDATA including contents;

Example 2

SQL> Drop Diskgroup ASMIND;

Thanks

Create a new Diskgroup in ASM

Instance: ASM Instance.
Privilege: Sysdba
Pre-requisite: Disks must be formatted. The path of the disk must be in the ASM_DISKSTRING. Oracle DB user must have read/write permission on the disk.

Syntax:

CREATE DISKGROUP diskgroup_name redundancy_level REDUNDANCY [FAILGROUP failgroup_name]DISK disk_clause

Where,
redundancy_level = HIGH or NORMAL or EXTERNAL
disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]

Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Example 1

CREATE DISKGROUP asm_data
NORMAL REDUNDANCY
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_01 NOFORCE
,'/u01/asmdisks/disk2' NAME data_02 NOFORCE
FAILGROUP ctrl_2
DISK
'/u03/asmdisks/disk3' NAME data_3 NOFORCE
,'/u03/asmdisks/disk4' NAME data_4 NOFORCE;

If the size clause is omitted the full disk is assigned.

Example 2

SQL> CREATE DISKGROUP ASMIND External REDUNDANCY DISK
'/dev/rhdisk6' SIZE 51200M ,
'/dev/rhdisk7' SIZE 51200M ,
'/dev/rhdisk8' SIZE 51200M ;

If the name clause is omitted the disks are given a system generated name.

Thanks