wideIT

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 The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

Temporary Tablespaces Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk. The temporary tablespace is where this takes place. Each database should have one temporary tablespace that is created when the database is created. You create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands, each of which is like it?s create tablespace counterpart. The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles. Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command as you can see in these examples: CREATE TEMPORARY TABLESPACE temp TEMPFILE ?/ora01/oracle/oradata/booktst_temp_01.

Background Process in ORACLE Database Database Writer Process (DBWn) Log Writer Process (LGWR) Checkpoint Process (CKPT) System Monitor Process (SMON) Process Monitor Process (PMON) Recoverer Process (RECO) Job Queue Processes Archiver Processes (ARCn) Queue Monitor Processes (QMNn) Database Writer Process (DBWn) The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems. When a buffer in the database buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has not been recently used according to the least recently used (LRU) algorith

What Is Undo? Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo. Undo records are used to: Roll back transactions when a ROLLBACK statement is issued Recover the database Provide read consistency Analyze data as of an earlier point in time by using Oracle Flashback Query Recover from logical corruptions using Oracle Flashback features When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another use

The Startup (nomount) Stage When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured.  For example, the sizes of all of the memory areas in Oracle are defined within the parameter file. After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started. Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage. If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage. Starting the Oracle Instance (Nomount Stage) There are some types of Oracle recovery operations that require the d

RMAN is very flexible and it offers many different types of backups.  We need to start with a list of backup types: ·           Full backup:   A full backup backs up all data files in the database, block-by-block, a standalone backup with everything you need to recover to the point in time when the full backup was collected. ·           Level 1 backup:   A level 1 backup includes only those blocks that have been changed since the “parent” backup was taken. Remember a parent backup may be either a level 0 or a level 1 backup. ·           Level 0 backup:   A level 0 incremental backup is physically identical to a full backup and it includes every data block in the file except empty blocks. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup. ·           Incremental backup:   An incremental backup can be either level 0 or level 1. There are two types of incremental back

ORACLE Export (exp) vs Datapump (expdp)    ORACLE provides two external utilities to transfer database objects from one database to another database. Traditional exports (exp /imp) are introduced before 10g. Then from 10g, ORACLE introduced datapump (expdp / impdp) as an enhancement to traditional export utility. Traditional Export (exp/ imp) This is an ORACLE database external utility, which is used to transfer database objects from one database server to another database server. It allows transferring the database objects over different platforms, different hardware and software configurations. When an export command is executed on a database, database objects are extracted with their dependency objects. That means if it extracts a table, the dependences like indexes, comments, and grants are extracted and written into an export file (binary format dump file). Following is the command to export a full database, Cmd > exp userid=username/password@exportdb_tns file=

SPFILEs provide the following advantages over PFILEs: An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs) Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted. Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine) Easy to find - stored in a central location

What is the difference between a PFILE and SPFILE: A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file. An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

PGA The Program/ Process Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. PGA stands for Process Global Area. This is memory reserved for each process that uses Oracle. It contains the context area. Oracle sets this area's size based on the values of the initialization parameters: OPEN_LINKS. The number of database links allowed open per user. DB_FILES. The number of database files allowed for the database (up to the value of MAX_DATAFILES). LOG_FILES. The maximum number of redo log file groups (up to the value of MAX_LOGFILES). The PGA also contains session-related information if MTS is not used. In environments where MTS is used, the session information is placed in the SHARED_POOL region of the SGA if the LARGE POOL (in Oracle8,8i and 9i) is not configured. This session informa

In the  database management systems  developed by the  Oracle Corporation , the  System Global Area  ( SGA ) forms the part of the system memory ( RAM ) shared by all the processes belonging to a single  Oracle database  instance. The SGA contains all information necessary for the instance operation. In general, the SGA consists of the following: dictionary cache : information about data dictionary tables, such as information about account, datafile, segment, extent, table and privileges redo log buffer: containing information about committed transactions that the database has not yet written to online redo log files the buffer_cache or "database buffer cache": holds copies of data blocks read from datafiles shared pool, the cache of parsed commonly used SQL statements, and also the data-dictionary cache containing tables, views and triggers Java pool, for parsing Java statements. large pool (including the User Global Area (UGA))

Hi, Do the following: SQL> CREATE SPFILE FROM PFILE=<pfile location with int.ora> or SQL> CREATE SPFILE=<required location to save new spfile> FROM PFILE=<pfile location with init.ora> Eg. SQL> create spfile from pfile=' C:\oraclexe\app\oracle\admin\XE\pfile\init1.ora ';

Hi, Execute the following command: SQL> CREATE PFILE FROM SPFILE=<spfile location with filename.ORA> or SQL> CREATE PFILE=<set pfile location> FROM SPFILE=<spfile location with filename.ORA> eg. SQL> create pfile from SPFILE='C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEXE.ora' SQL> create pfile ='C:\oraclexe\app\oracle\admin\XE\pfile\init.ora' from SPFILE =' C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEXE.ora';