New Backup and Recovery Features for Oracle 11g Release 2

The following Backup and Recovery features are new in Oracle Database 11g Release 2 (11.2):

 

 

 Oracle Secure Backup (OSB) Cloud Module

 

Users can take advantage of the Internet-based data storage services offered by Amazon Simple Storage Service (Amazon S3) for their RMAN backup and recovery tasks. The OSB Cloud Module interface extends the Amazon S3 functionality and provides an easy-to-manage, cost-efficient, and scalable alternative to maintaining in-house data storage and a local, fully configured backup infrastructure.

DUPLICATE Database Command Enhancements

Users can duplicate a database without connecting to a target database. The only requirements for this operation are a connection to a catalog and an auxiliary database. This new functionality is useful when the target database is not always available.
Users can also duplicate a database without connecting to a target database and a recovery catalog. In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication. This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.
When you duplicate from a target database, RMAN determines if any excluded tablespaces contain SYS-owned objects, materialized views, and identifies tablespaces that are not self-contained before starting any duplication operations.

RMAN Tablespace Point-in-Time Recovery (TSPITR) Enhancements

TSPITR can be used to recover a dropped tablespace and to recover to a point in time before the tablespace was brought online. The latter TSPITR operation can be repeated as many times as necessary.(Before this release, TSPITR did not operate on dropped tablespaces.)

Automatic Block Repair

The enhancements to corrupt block detection make block repair more efficient and increase availability. The automatic block repair enhancement complements the Oracle Database 11g Release 1 automatic block corruption detection.
A detected corrupt block (for example, through a user's SQL query) is now automatically repaired by using a good block from a physical standby database. With automatic block repair, a user querying data in the corrupt block no longer sees the corrupt block error message displayed (ORA-01578). Instead, there is a pause while the corrupt block is repaired, and then the results of the SQL query are returned. The automatic block repair is transparent to the user unless the repair fails. Block recovery is faster because no redo must be applied to the block that is taken from the physical standby database.
Automatic block repair requires an Oracle Active Data Guard license because real-time query mode must be enabled on the physical standby database.
The operation occurs automatically but can also be explicitly invoked with the RMAN RECOVER BLOCK command.

 SET NEWNAME Options

The SET NEWNAME command is more powerful and easier to use. You can use this command on a specific tablespace or on all data files and temp files. You can also change the names for multiple files in the database.A new string format identifier "%b" makes it easier to move files around. Using SET NEWNAME with the format identifier "%b" enables you to designate a file name without its directory path. This option is particularly useful when you want to keep the names of your data files the same but move the data files to a different directory location.
 

 CONVERT DATABASE Option

A new option, SKIP UNNECESSARY DATAFILES, is now supported for the CONVERT DATABASE command. When the option is invoked, the only data files that are converted are those that require RMAN processing during transfer between the specified platforms. The rest of the data files can be used by the destination database through shared storage or path name. By skipping the conversion of data files that do not contain undo segments, overall database transport time can be reduced. You can use this option when converting at the source or converting ON DESTINATION PLATFORM.

 Expanded Backup Compression Levels

RMAN now offers a wider range of compression levels with the Advanced Compression Option (ACO). Although the existing BASIC compression option may be suitable for most environments, you may want to explore the ACO backup compression levels (LOW, MEDIUM, and HIGH) to achieve better performance or higher compression ratios.

 INCARNATION Specifier Enhancement

Incarnations may now be used to further qualify archived redo log ranges for the BACKUP, RESTORE, and LIST commands. You can now specify ALL or CURRENT or designate a particular incarnation number when listing ranges of archived logs.

TO DESTINATION 

 Syntax has been added to the BACKUP command. This addition enables you to designate a specific directory location for backups to disk and is primarily for use with the BACKUP RECOVERY AREA command. If backup optimization is enabled, then RMAN only skips backups of identical files that reside in the directory location specified by the TO DESTINATION option.

Oracle Memory Architecture

Here are some explanation about how memory works on Oracle.

Oracle Database uses memory to store information such as the following:

• Program code
  
• Information about a connected session, even if it is not currently active
  
• Information needed during program execution (for example, the current state of a query from which rows are being fetched)
  
• Information that is shared and communicated among Oracle Database processes (for example, locking information)
  
• Cached data (for example, data blocks and redo log entries) that is also permanently stored on storage devices
  
  
   

  Basic Memory Structures

  The basic memory structures associated with Oracle Database include:
  
  

  • Software code areas
    
    Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from users' programs—a more exclusive or protected location.
    
    
  • System global area (SGA)
    
    The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
    
    
  • Program global area (PGA)
    
    A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.
    
    

   
   
   
   
   
   

  Overview of the System Global Area

  The System Global Area (SGA) and the set of database processes constitute an Oracle Database instance. Oracle Database automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
  The SGA is read/write. All database background processes and all server processes that execute on behalf of users can read information contained within the instance's SGA, and several processes write to the SGA during database operation.
  Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access. This is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
  If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
  As shown in the picture, the SGA consists of a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests.
   
  Most noticable SGA components are :
   
  • Database Buffer Cache
  •  Redo Log Buffer
  • Shared Pool
  • Large Pool
  • Java Pool
  • Streams Pool
  
  

  Database Buffer Cache:

   The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.
  
  

  Redo Log Buffer:   

  The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.
  Redo entries are copied by Oracle Database processes from the user's memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.
  
  

  Shared Pool:

  The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.
  
  

  Large Pool:

  The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:
  
  

  • Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
    
    
  • I/O server processes
    
    
  • Oracle Database backup and restore operations
    
    

  By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle Database can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.
  In addition, the memory for Oracle Database backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.
  The large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
  
  
  

  Java Pool:

  Java pool memory is used in server memory for all session-specific Java code and data within the JVM. Java pool memory is used in different ways, depending on the mode in which Oracle Database is running.
  The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.
  
  

  Streams Pool:

  The streams pool is used exclusively by Oracle Streams. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.
  Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as needed when Oracle Streams is used.
  
  
  
  

  Overview of the Program Global Area

  
  Oracle Database allocates a program global area (PGA) for each server process. The PGA is used to process SQL statements and to hold logon and other session information. For the purposes of memory management, the collection of all PGAs is known as the instance PGA. Using an initialization parameter, you set the size of the instance PGA, and the database distributes memory to individual PGAs as needed.
  
  Most noticable PGA components are:
  
  

  • Session Memory
  • Private SQL Area

  
  

  Session Memory:

  Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
  
  

  Private SQL Area: 

  The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.
  The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process's PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
  
  

What are Listener.ora , Tnsnames.ora , sqlnet.ora files?

What is a Listener.ora File?

The information about this listener can be found in the listener.ora file.
This file is normally found in the $ORACLE_HOME/network/admin folder.
If the file is not here then you can search in /etc folder if you are on a unix server.

If you have RAC then you can find the name of listener in the initdbname.ora file

So the important point you have to remember is that you should compulsorily  have a listener.ora file
if your users have to connect from a different server.

What is a Tnsnames.ora File?

The tnsnames.ora file contains the information about the server where your database is present , the
port number information , the database name , the protocol used (tcp etc ).  If a user wants to
connect to a database then the software program which the user is using will look in the
tnsnames.ora file and find out all the information what we discussed before. Then the software
program uses this information and makes a connection to the database with the help of the listener.

So important point is if a user want to connect to a database he or she would definetly need a
tnsnames.ora file.

What happens if 100 users want to connect to the database? Do we need 100 tnsnames.ora files?
If the users are using a software program that is located on their machine then each user will need his
own tnsnames.ora file. If the users are connecting to a website for example www.google.com then
the user do not need to have a tnsnames.ora on his machine because the website is located on a
webserver so the tnsnames.ora file would already be in the webserver.

Some java programs do not need a tnsnames.ora file .What they do is ,they can read all this
information from a different file which they created . The java developers speak to the dba and will
get the database information. Then they store this information in a different file. Then the java
program reads this file and will connect to the database.

What is sqlnet.ora file ?

If a tnsnames.ora file has to work properly , then we need the sqlnet.ora file. The sqlnet.ora file
contains some information which the user process(the user connection) needs ,to find exactly where
a database server is located. So the user process normally reads the tnsnames.ora file and then the
sqlnet.ora file and thus will find the complete database server information, database name etc and
will talk to the listener which is present on the database server. The listener will then will see that a
conenction is opened to the database.

There are also lot of other parameters in the sqlnet.ora file that you can use.

 For UNIX users, the order in which Oracle searches locations for Oracle Net files like sqlnet.ora and tnsnames.ora are in this order:

1. $HOME for hidden files only (i.e., .sqlnet.ora and .tnsnames.ora)

2. $TNS_ADMIN

3. $HOME

4. /etc or /var/opt/oracle (depends on platform)

5. $ORACLE_HOME/network/admin

For Windows users, the search order is a bit different. It goes like this,

1. current path (associated with the running client application)

2. Environment variable TNS_ADMIN defined for the session

3. Environment variable TNS_ADMIN defined for the system

4. Windows Registry Key TNS_ADMIN

5. %ORACLE_HOME%\network\admin

CRON and Crontab on UNIX systems

What is CRON:

CRON is the time-based job scheduler in Unix-like computer operating systems. cron enables users to schedule jobs (commands or shell scripts) to run periodically at certain times or dates. It is commonly used to automate system maintenance or administration, though its general-purpose nature means that it can be used for other purposes, such as connecting to the Internet and downloading email.

We generally use cron to schedule backup procedures manually.

Setting up cron jobs in Unix and Solaris:

cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.  Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

This document covers following aspects of Unix cron jobs

1. Crontab Restrictions

2. Crontab Commands

3. Crontab file – syntax

4. Crontab Example

5. Crontab Environment

6. Disable Email

7. Generate log file for crontab activity

1. Crontab Restrictions

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use

crontab if your name does not appear in the file /usr/lib/cron/cron.deny.

If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e    Edit your crontab file, or create one if it doesn’t already exist.

crontab -l      Display your crontab file.

crontab -r      Remove your crontab file.

crontab -v      Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file

Crontab syntax :

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

*     *     *   *    *        command to be executed -     -     -   -    - |     |     |   |    | |     |     |   |    +----- day of week (0 - 6) (Sunday=0) |     |     |   +------- month (1 - 12) |     |     +--------- day of        month (1 - 31) |     +----------- hour (0 - 23) +------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Notes

A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.

B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30     18     *     *     *         rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

min	hour	day/month	month	day/week	Execution time
30	0	1	1,6,12	*	– 00:30 Hrs  on 1st of Jan, June & Dec.
0	20	*	10	1-5	–8.00 PM every weekday (Mon-Fri) only in Oct.
0	0	1,10,15	*	*	– midnight on 1st ,10th & 15th of month
5,10	0	10	*	1	– At 12.05,12.10 every Monday & on 10th of every month
:

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment

cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).

cron supplies a default environment for every shell, defining:

HOME=user’s-home-directory

LOGNAME=user’s-login-id

PATH=/usr/bin:/usr/sbin:.

SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file

To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

Here are some examples to better clarifiy the subject:

 Suppose we want to establish an automated diary (in the file / logs / log_df.log example) of free disk space (df) at specific time intervals:

            Every day at 23:30:

                30 23 * * * df >> /logs/log_df.log

            Every hour past 5 minutes:

                5 * * * * df >> /logs/log_df.log

            Every first of month at 23:30:

                30 23 1 * * df >> /logs/log_df.log

            Every monday, at 22h28:

                28 22 * * 1 df >> /logs/log_df.log

            From 2 to 5 of each month at 10:12

                12 10 2-5 * * df >> /logs/log_df.log

            Every peer day of the month at 23:59:

                59 23 */2 * * df >> /logs/log_df.log

            Every 5 minutes:

                */5 * * * * df >> /logs/log_df.log

            It is also possible to automatically execute more complex commands using a shell script. Simply declare it as a job in the cron table.