Google+ Followers

Pages

Thursday, November 17, 2011

MAINTAINING ONLINE REDO LOG FILES


Before adding group and members first check how many group already in the database using the view
SQL > select * from V$LOG;
SQL > select * from V$LOGFILE;

ADDING ONLINE REDO LOG FILE GROUPS
In some cases you may need to create additional log file group. To solve availability problem this can be done by SQL command.

ALTER DATABASE ADD LOGFILE GROUP 4
('D:\ORACLE\ORADATA\ORCL\REDO04A.LOG' ,
 'D:\ORACLE\ORADATA\ORCL\REDO04B.LOG')
SIZE 1M;

ADDING ONLINE REDO LOG FILE MEMBER
We can add new members to existing online redo log file groups using the following SQL command:

ALTER DATABASE ADD LOGFILE MEMBER
 'D:\ORACLE\ORADATA\ORCL\REDO04C.LOG' TO GROUP 4 ,
 'D:\ORACLE\ORADATA\ORCL\REDO03B.LOG' TO GROUP 3

When adding member use the fully specified name of the log file members,
Otherwise the files are created in a default directory of the database server.

DROPPING ONLINE REDO LOGFILE GROUPS

Online redo log file group can be dropped by the SQL command

ALTER DATABASE DROP LOGFILE GROUP 4;

RESTRICTIONS:
While deleting online redo log file group we must consider the following restrictions
  1. An instance requires at least 2 group of online redo log files.
  2. An ACTIVE and CURRENT group cannot be dropped.
  3. When an online redo log file group is dropped, the operating system file are nat deleted.

NOTE: If database is in archive mode group can not be dropped until archival process not completely done. Even group is in INACTIVE MODE

DROPPING ONLINE REDO LOG FILE MEMBERS

We can drop online redo log file member by using SQL statement

ALTER DATABASE DROP LOGFILE MEMBER
'D:\ORACLE\ORADATA\ORCL\REDO02B.LOG';

RESTRICTIONS:
While deleting online redo log file members we must consider the following restrictions

  1. We cannot drop the last valid member of any group
  2. If the group is current, you must force switch log file before you can drop the member.
  3. If the database is in archived mode and the log file group to which the member belongs is not archived, the member can not be dropped
  4. when a online redo log file member is dropped, the operation system file cannot be deleted.

REALLOCATING OR RENAMING ONLINE REDO LOG FILES

The location of online redo log files can be changed by renaming the online redo log files.
Before renaming the online redo log files, ensure that the new online redo log file can exists.
Relocate or rename online redo log files in one of the 2 following ways:
  1. Add new members and drop old members
  2. Alter database rename file command

ALTER DATABASE RENAME FILE COMMAND use following steps:

STEP # 1:
Shutdown the database
SQL > SHUTDOWN;

STEP # 2:
Copy the online redo log file to the new location in operating system

STEP # 3:
To start database at mount
SQL > STARTUP MOUNT;

STEP # 4:
Rename the online redo log file member using the
SQL >ALTER DATABASE RENAME FILE
           ‘D:\ORACLE\ORADATA\ORCL\REDO03C.LOG’
           TO
           ‘D:\ORACLE\ORADATA\REDO03B.LOG’;


CLEARING ONLINE REDO LOG FILES

An online redo log file might become corrupt while the database is open , and ultimately stop database activity because activity cannot continue.
In this situation ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file with out shutdown the database.

The command can overcome two situations where dropping online redo log file is not possible:
  • If the are only two log groups
  • The corrupt online redo log file belongs to the current group

Use the UNARCHIVED keyword in the command to clear the corrupted online file to avoid archiving.

`

ONLINE REDO LOG FILE CONFIGURATION

To determine the appropriate number of online redo log files for a database, you must test different configurations.

Oracle server multiplexed group can contain different no. of members this is called asymmetric configuration (means all members in a group placed on same disk)
In this case we get temporary result because of disk failure

Oracle recommended try to built symmetric configuration (means member of any group also placed on different location)
By doing this if one member is not available the other is available and instance does not shutdown

Separate archive log file and online redo log files on different disks to reduce contention b/w the ARCn and LGWR

Data files and online files should be placed on different disk to reduce contention b/w LGWR and DBWn

SIZE OF ONLINE REDO LOG FILE
The minimum size of online redo log file is 50KB  and
Maximum size is specifying in the operating system.


SQL > Select * from V$log;

SQL > Select * from V$logfile;

In the V$log views the status column having the values

  1. UNUSED:
Indicates that the online redo log file group has never been written to
  1. CURRENT:
Indicates the current online redo log file group. This implies that the online redo log file group is active.
  1. ACTIVE:
Indicates that the online redo log file group is active but is not the current online        redo log file group. It is needed for crash recovery.
  1. INACTIVE:
Indicates that the online redo log file group is no longer needed for instance recovery.

In the V$logfile views the status column having the values

  1. INVALID:
Indicates that the file is inaccessible
  1. STALE:
Indicates that the contents of the file are incomplete
  1. DELETED:
Indicates that the file is no longer used
  1. BLANK
Indicates that the file is in use.

Tuesday, November 15, 2011

Configuring Flashback Database through SQL


In order to configure the Flashback Database feature, we need to step through a series of operations, as follows:

1. Check that our database is in the archive log mode by either querying the  $DATABASE view, or by simply issuing the following command:
SQL> ARCHIVE LOG LIST
The preceding output reveals that the database is indeed running in the archive log mode. If it isn’t, we can turn archive logging on with the ALTER DATABASE statement shown in the following code, after first shutting down the database and starting it up initially in the mount mode:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN

2. Set up a flash recovery area, as we learn in the FLASH RECOVERY AREA CHAPTER

3. Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to specify how far back you can flashback your database. The following code sets the Flashback target to 1 day (1,440 minutes):
SQL> SHOW PARAMETER RETENTION
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

4. Shut down and restart the database in the mount exclusive mode. If we are using a single instance, a simple MOUNT command can be used:
SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

5. Enable the Flashback Database feature:
SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

6. Use the ALTER DATABASE OPEN command to open the database and then confirm that the Flashback Database feature is enabled, by querying the V$DATABASE view:
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

Oracle Flashback Concept


FLASHBACK LEVELS
In Oracle Database 10g, you have access to flashback techniques at the row, table, and database levels, as follows:
Row level
We can use Flashback techniques to undo erroneous changes to individual rows. There are three types of row-level Flashback techniques, and all of them rely on undo data stored in the undo tablespace:
• Flashback Query: Allows us to view old row data based on a point in time or an SCN.
we can view the older data and, if necessary, retrieve it and undo erroneous changes.
• Flashback Versions Query: Allows us to view all versions of the same row over a period of time so that you can undo logical errors. It can also provide an audit history of changes, effectively allowing us to compare present data against historical data without performing any DML activity.
• Flashback Transaction Query: Lets us view changes made at the transaction level. This technique helps in analysis and auditing of transactions, such as when a batch job runs twice and you want to determine which objects were affected. Using this technique, we can undo changes made by an entire transaction during a specified period.
Table level
There are two main Flashback features available at the table level:
• Flashback Table: Restores a table to a point in time or to a specified SCN without restoring data files. This feature uses DML changes to undo the changes in a table. The Flashback Table feature relies on undo data.
• Flashback Drop: Allows us to reverse the effects of a DROP TABLE statement, without resorting to a point-in-time recovery. The Flashback Drop feature uses the Recycle Bin to restore a dropped table.
Database level
• Flashback Database: The Flashback Database feature allows us to restore an entire database to a point in time, thus undoing all changes since that time. For example, we can restore a dropped schema or an erroneously truncated table. Flashback Database mainly uses flashback logs to retrieve older versions of the data blocks; it also relies, to a much smaller extent, on archived redo logs to completely recover a database without restoring data files and performing traditional media recovery.

As we can see, Oracle’s Flashback technology employs a variety of techniques. The row-level Flashback techniques and Flashback Table use undo data  Flashback Drop use new concept of Recycle Bin and Flashback Database rely on the new concept of Flashback log data, respectively, to undo errors at various levels. We will focus on these techniques in this chapter.

Flashback vs.Traditional Recovery Techniques
Unlike traditional recovery techniques, the primary use of Flashback techniques isn’t to recover from a media loss, but to recover from human errors. For example, we may accidentally change the wrong set of data or drop a table. Or we may just want to query historical data and perform change analysis. In some extreme cases, we may want to revert the entire database to a previous point in time.

Note: If we have a damaged disk drive, or if there is physical corruption (not logical corruption due to application or user errors) in our database, we must still use the traditional methods of restoring backups and using archived redo logs to perform the recovery.

Traditionally, the only way to recover from human error was to employ traditional backup and restore techniques. The process of restoring the database files and then rolling forward through all the redo logs could often involve significant downtime, however, and Flashback technology offers us a much more efficient and much faster way to recover from logical errors, in most cases while the database is still online and available to users. Furthermore, Flashback techniques allow us to selectively restore certain objects. With traditional techniques, we have no choice but to recover the entire database.

FLASHBACK DATABASE
Before Oracle Database 10g, if we suffered logical database corruption, we would undertake traditional point-in-time recovery techniques, restoring data file backup copies and then using archived redo logs to advance the database forward. This was often time-consuming and cumbersome. No matter how limited the extent of the corruption, we would need to restore entire data files and apply the archived redo logs.

Note: Oracle can check data block integrity by computing checksums before writing the data blocks to disk. When the block is subsequently read again, the checksum for the data block is computed again, and if the two checksums differ, there is likely corruption in the data block. By setting the DB_BLOCK_CHECKSUM initialization parameter to FULL, we can make the database perform the check in the database buffer cache itself, thus eliminating the possibility of corruption at the physical disk level. The DB_BLOCK_CHECKSUM parameter is FALSE by default.

In Oracle Database 10g, the Flashback Database feature restores data files but without requiring backup data files and using just a fraction of the archived redo log information. A Flashback Database operation simply reverts all data files of the database to a specified previous point in time. With Flashback Database, the time it takes to recover is directly proportional to the number of changes that we need to undo. Thus, it is the size of the error and not the size of the database that determines the time it takes to recover. This means that we can recover from logical errors in a fraction of the time—perhaps as little as a hundredth of the time, depending on the size of the database— that it would take using traditional methods.

Note: Flashing back a database is possible only when there is no media failure. If you lose a data file or it becomes corrupted, you’ll have to recover using a restored data file from backups.
We can use Flashback Database in the following situations:
Ø  To retrieve a dropped schema
Ø  When a user error affects the entire database
Ø  When we truncate a table in error
Ø  When a batch job performs only partial changes

The Flashback Database feature uses flashback database logs, which are stored in the new flash recovery area, to undo changes to a point in time just before a specified target time or SCN. Since the specified target time and the actual recovery time may differ slightly, we then use archived redo logs to recover the database over the short period of time between the target time and the actual recovery time.

Once the Flashback Database feature is enabled, we simply use the FLASHBACK DATABASE command to return the database to its state at a previous time, SCN, or log sequence. We can issue the FLASHBACK DATABASE command from either RMAN or SQL*Plus. The only difference is that RMAN will automatically retrieve the necessary archived redo logs, whereas in SQL*Plus we may have to supply the archived redo logs, unless we use the SET AUTORECOVERY ON feature in SQL*Plus. We’ll take a look at the whole Flashback Database process in more detail shortly, but first let’s look at how to enable (and disable) the Flashback Database feature.

Tip Since we need the current data files in order to apply changes to them, we can’t use the Flashback Database feature in cases where a data file has been damaged or lost.


Oracle Shutdown


SHUTDOWN THE DATABASE:                                                                              
There are 4 ways to shutdown database

1- SHUTDOWN NORMAL:
  • No new connection can be made
  • Oracle server waits all users to disconnect
  • database and redo buffer written to the disk
  • Background process terminated and SGA remove from memory
  • Oracle closes and dismount database before shutdown
  • Next startup does not require recovery

2- SHUTDOWN TRANSACTIONAL:
  • No new connection can be made
  • User automatically disconnect after completing the transaction in progress
  • When all transaction finished shutdown occur immediately
  • Next startup does not require recovery
3- SHUTDOWM IMMEDIATE:
  • Current SQL statement being processed is not completed
  • Oracle server does not wait for the user who are currently connected to the database
  • Oracle closes and dismount database before shutdown.
  • Next startup does not require recovery

4- SHUTDOWN ABORT:
  • Oracle does not wait for user currently connected to the database
  • Database and redo buffers are not written to the disks
  • The instance terminated without closing the files
  • The database is not close or dismounts
  • Next startup requires instance recovery.

Oracle 10g startup


STARTING UP THE DATABASE:                                                               
There are 4 stages to startup and shutdown the database
When startup database
Shutdown à Nomount à Mount à Open

At  NOMOUNT  Stage:
An instance is typically started only in NOMOUNT mode
During Database creation,
During Re-creation of control files,
Or During certain backup and recovery scenarios.

At this stage following tasks are performed
  • Reading initialization parameter file
                   First spfileSID.ora
if not found then, spfile.ora
If not found then, initsid.ora
specifying the PFILE parameter with STARTUP overrides the default behavior.
  • Allocating SGA
  • Starting the background Process
  • opening the alertSID.log files and trace files

At  MOUNT  Stage:
  Mounting a database includes the following tasks:
  • Associating a database with instance start at nomount stage.
  • Locating and opening the control file specified in the parameter file.
  • Reading the control file to obtain the name, status and destination of
DATA FILES AND ONLINE REDO LOG FILES
  • To perform special maintenance operations
  • Renaming data files
(data files for an offline tablespace can be renamed when the database is open)
  • Enabling and disabling online redo log file archiving, flashback options.
  • Performing full Database Recovery
We usually need to start up a database in mount mode when you’re doing activities such as performing a full database recovery, changing the archive logging mode of the database, or renaming data files. Note that all three of these operations require Oracle to access the data files but can’t accommodate any user operations in the files.

At  OPEN  Stage:
The last stage of the startup process is opening the database. When the database is started in the open mode, all valid users can connect to the database and perform database operations. Prior to this stage, the general users can’t connect to the database at all. You can bring the database into the open mode by issuing the ALTER DATABASE command as follows:
SQL> ALTER DATABASE OPEN;
Database altered.
More commonly, we simply use the STARTUP command to mount and open our database all at once:
SQL> STARTUP
To open the database, the Oracle server first opens all the data files and the online redo log files, and verify that the database is consistent. If the database isn’t consistent—for example, if the SCNs in the control files don’t match some of the SCNs in the data file headers—the background process will automatically perform an instance recovery before opening the database. If media recovery rather than instance recovery is needed, Oracle will signal that a database recovery is called for and won’t open the database until you perform the recovery.
IN SHORT
Opening a database includes the following tasks:
  • Open online data files
  • Open online redo log files

NOTE If any of the data files or online redo log files are not present when you attempt to open the database, then the oracle server returns an error.

STARTUP COMMAND à use to start database

STARTUP NOMOUNT;
to move the database from  NOMOUNT TO MOUNT or from
                                                      NOMOUNT TO OPEN   use
ALTER DATABASE COMMAND 

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

Oracle 10g Parameters related to Audit


AUDIT_TRAIL

The AUDIT_TRAIL parameter turns auditing on or off for the database. If we don’t want auditing to be turned on, do nothing, since the default value for this parameter is none, or false, which disables database auditing. If we want auditing turned on, we can set the AUDIT_TRAIL parameter to any of the following values:

os: Oracle writes the audit records to an operating system audit trail, which is an operating system file, including audit records from the OS, audit records for the SYS user, and those database actions that are always automatically audited.

db: Oracle records the same type of auditing as with the os setting, but it directs all audit records to the database audit trail, which is the AUD$ table owned by SYS.

none: This value disables auditing.
db,extended: This is similar to the db setting, but also provides extended audit information like the SQLBIND and SQLTEXT columns of the SYS.AUD$ table.

In addition, you have two XML-related AUDIT_TRAIL values (new in Oracle Database 10.2):

XML: This value for audit trail enables database auditing and writes audit details to OS files in XML format.

XML,EXTENDED: This value prints all database audit records plus the SQLTEXT and SQLBIND values to OS files in the XML format.
The parameter is set as follows: AUDIT_TRAIL = db
Default value: none
Parameter type: Static
Chapter 11 provides more information about auditing actions within an Oracle database.

Tip Even if we don’t set the AUDIT_TRAIL parameter to any value, Oracle will still write audit information to an operating system file for all database actions that are audited by default. On a UNIX system, the default location for this file is the $ORACLE_HOME/rdbms/audit directory. Of course, we can specify a different directory if we wish. See Chapter 11 for more details on this feature.

AUDIT_FILE_DEST

The AUDIT_FILE_DEST parameter specifies the directory in which the database will write the audit records, when we choose the operating system as the destination with the AUDIT_TRAIL parameter by specifying AUDIT_TRAIL=os. We can also specify this parameter if we choose the XML or XML,EXTENDED options for the AUDIT_TRAIL option, since the audit records are written to operating system files in both cases.
Default value: $ORACLE_HOME/rdbms/audit
Parameter type: Dynamic. You can modify this parameter with the ALTER SYSTEM . . . DEFERRED command.

AUDIT_SYS_OPERATIONS

This parameter, if set to a value of true, will audit all actions of the SYS user and any other user with a SYSDBA or SYSOPER role and will write the details to the operating system audit trail specified by the AUDIT_TRAIL parameter. By writing the audit information to a secure operating system location, we remove any possibility of the SYS user tampering with an audit trail that is located within the database. The possible values are true and false.
Default value: false
Parameter type: Static

Oracle 10g Parameters related to Audit


AUDIT_TRAIL

The AUDIT_TRAIL parameter turns auditing on or off for the database. If we don’t want auditing to be turned on, do nothing, since the default value for this parameter is none, or false, which disables database auditing. If we want auditing turned on, we can set the AUDIT_TRAIL parameter to any of the following values:

os: Oracle writes the audit records to an operating system audit trail, which is an operating system file, including audit records from the OS, audit records for the SYS user, and those database actions that are always automatically audited.

db: Oracle records the same type of auditing as with the os setting, but it directs all audit records to the database audit trail, which is the AUD$ table owned by SYS.

none: This value disables auditing.
db,extended: This is similar to the db setting, but also provides extended audit information like the SQLBIND and SQLTEXT columns of the SYS.AUD$ table.

In addition, you have two XML-related AUDIT_TRAIL values (new in Oracle Database 10.2):

XML: This value for audit trail enables database auditing and writes audit details to OS files in XML format.

XML,EXTENDED: This value prints all database audit records plus the SQLTEXT and SQLBIND values to OS files in the XML format.
The parameter is set as follows: AUDIT_TRAIL = db
Default value: none
Parameter type: Static
Chapter 11 provides more information about auditing actions within an Oracle database.

Tip Even if we don’t set the AUDIT_TRAIL parameter to any value, Oracle will still write audit information to an operating system file for all database actions that are audited by default. On a UNIX system, the default location for this file is the $ORACLE_HOME/rdbms/audit directory. Of course, we can specify a different directory if we wish. See Chapter 11 for more details on this feature.

AUDIT_FILE_DEST

The AUDIT_FILE_DEST parameter specifies the directory in which the database will write the audit records, when we choose the operating system as the destination with the AUDIT_TRAIL parameter by specifying AUDIT_TRAIL=os. We can also specify this parameter if we choose the XML or XML,EXTENDED options for the AUDIT_TRAIL option, since the audit records are written to operating system files in both cases.
Default value: $ORACLE_HOME/rdbms/audit
Parameter type: Dynamic. You can modify this parameter with the ALTER SYSTEM . . . DEFERRED command.

AUDIT_SYS_OPERATIONS

This parameter, if set to a value of true, will audit all actions of the SYS user and any other user with a SYSDBA or SYSOPER role and will write the details to the operating system audit trail specified by the AUDIT_TRAIL parameter. By writing the audit information to a secure operating system location, we remove any possibility of the SYS user tampering with an audit trail that is located within the database. The possible values are true and false.
Default value: false
Parameter type: Static

Oracle Parameter related to Database Buffer Cache


DB_CACHE_SIZE
This parameter sets the size of the default buffer pool for those buffers that have the primary block size (this is the block size defined by DB_BLOCK_SIZE). For example, we can use a number like 1,024MB.
Default value: If we're using the SGA_TARGET parameter, the default is 0. If we aren't using
SGA_TARGET, it’s the greater of 48MB or 4MB * number of CPUs * granule size.
Parameter type: Dynamic. It can be modified with the ALTER SYSTEM command.

DB_KEEP_CACHE_SIZE
The normal behavior of the buffer pool is to treat all the objects placed in it equally. That is, any object will remain in the pool as long as free memory is available in the buffer cache. Objects are removed (aged out) only when there is no free space. When this happens, the oldest unused objects sitting in memory are removed to make space for new objects.
The use of two specialized buffer pools—the keep pool and the recycle pool—allows us to specify at object-creation time how we want the buffer pool to treat certain objects. For example, if we know that certain objects don’t really need to be in memory for a long time, we can assign them to a recycle pool, which removes the objects right after they’re used. In contrast, the keep pool always retains an object in memory if it’s created with the KEEP option. The DB_KEEP_CACHE_SIZE parameter specifies the size of the keep pool, and it’s set as follows: DB_KEEP_CACHE_SIZE = 500MB
Default value: 0; by default, this parameter is not configured.
Parameter type: Dynamic. It can be changed by using the ALTER SYSTEM command.

DB_RECYCLE_CACHE_SIZE
The DB_RECYCLE_CACHE_SIZE parameter specifies the size of the recycle pool in the buffer cache. Oracle removes objects from this pool as soon as the objects are used. The parameter is set as follows: DB_RECYCLE_CACHE_SIZE = 200MB
Default value: 0; by default, this parameter is not configured.
Parameter type: Dynamic. It can be changed by using the ALTER SYSTEM command.

DB_nK_CACHE_SIZE
If we prefer to use nonstandard-sized buffer caches, we need to specify the DB_nK_CACHE_SIZE parameter for each, as in the following two examples:
DB_4K_CACHE_SIZE=2048MB
DB_8K_CACHE_SIZE=4096MB
The values for n that can be used in this parameter are 2, 4, 8, 16, or 32.
Default value: 0
Parameter type: Dynamic. You can change this parameter’s value with the ALTER SYSTEM command.

Oracle SGA_TARGET Parameter


SGA_TARGET

The SGA_TARGET parameter determines whether our database will use automatic shared memory management. In order to use automatic shared memory management, set the SGA_TARGET parameter to a positive value. We don’t have to specify the five automatic shared memory components in our initialization file (shared pool, database buffer cache, Java pool, large pool, and streams pool). Oracle will show zero values for these when we query the V$PARAMETER view, which shows the values of all our initialization parameters. We may, however, choose minimum values for any of the five auto-memory parameters, in which case we should specify the values in the initialization file.
If we set the SGA_TARGET parameter to zero, we disable automatic shared memory management, and we have to manually set the values for all the previously mentioned SGA components. When we use automatic shared memory management by setting a value for the SGA_TARGET parameter, the memory we allocate for the manually sized components (the log buffer, the buffer cache keep pool, the buffer cache recycle pool, the nondefault-sized buffer cache pools and nKBsized buffer caches, and the fixed SGA allocations) will be deducted from the SGA_TARGET value first.
To get a quick idea about how much memory to allocate for the SGA_TARGET parameter under automatic shared memory management, run the following query:

SQL> SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;

The value for SGA_TARGET must be a minimum of 64MB, and the upper limit is dependent on
the operating system.

Default value: 0 (no automatic shared memory management)
Parameter type: Dynamic. You can use the ALTER SYSTEM command to modify the value.

Note If we set the SGA_TARGET parameter, we can leave out all the automatic SGA parametersDB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE—from our init.ora file, unless we want to ensure a minimum size for some of these, in which case we can explicitly specify them in the file.


Oracle Compatible Parameter


COMPATIBLE

The COMPATIBLE parameter allows us to use the latest Oracle database release, while making sure that the database is compatible with an earlier release.
Suppose we upgrade to the Oracle Database 10g Release 2 version, but our application developers haven’t made any changes to their Oracle9i application. In this case, we could set the COMPATIBLE parameter equal to 9.2 so the untested features of the new Oracle version won’t hurt our application. Later on, after the application has been suitably upgraded, we can reset the COMPATIBLE initialization parameter to 10.2.0, which is the default value for Oracle Database 10g Release 2 (10.2).
If, instead, we immediately raise the compatible value to 10.2, we can use all the new 10.2 features, but we won’t be able to downgrade our database to 9.2 or any other lower versions. We must understand this irreversible compatibility clearly, before we set the value for this parameter.

Default value: 10.2.0
Parameter type: Static



Types of initialization parameter file




Oracle uses a parameter file to store the initialization parameters and their settings for an instance. We can use either of the following two types of parameter files:
  1. Server parameter file (SPFILE): Dynamic persistent server parameter file SPFILE, commonly referred as spfileSID.ora A binary file that contains the initialization parameters
  2. Initialization parameter file (pfile): Static parameter file, PFILE, commonly referred as initSID.ora  A text file that contains a list of all initialization parameters.

Contents Of Parameter File

A list of instance parameters
The name of the database the instance is associated with
Allocation for memory structures of the SGA
What to do with filled online redo log files.  (Archive Destination)
The names and locations of control files.
Information about undo tablespace.

 Text initialization parameter file:  PFILE à   initsid.ora  (sid) means database name

Text File
Modified with an operating system editor
Modification made manually
Whenever we need to change the value of the parameter file we must shutdown the database.
The pfile is read only during instance startup. (Changes take effect on next startup)

For oracle 10g (R2)   à  default location E:\oracle\product\10.2.0\Db_1\database\initorcl.ora

Server parameter file:  SPFILE : à spfilesid.ora  à   (sid)  means database name

- Binary File
- Maintained by the oracle server
- Always resides on the server side having read and write by database server
- Ability to make changes persistent across SHUTDOWN and STARTUP
- Can self-tune parameter values
- Can have RMAN support for backup the initialization parameter