11g Receive Updates For This Category

List By Category: List By Month:

Per Page :

AWR Baseline Enhancements in Oracle Database 11g Release 1

October 28, 2011 Articles 0 1

AWR Baseline Enhancements in Oracle Database 11g Release 1

The Automatic Workload Repository (AWR) was introduced in Oracle 10g and included some simple baseline functionality. Creating a baseline allowed a specified range of snapshots to be retained, regardless of the AWR retention policy, and used for performance comparisons. This functionality, and the DBMS_WORKLOAD_REPOSITORY package that manages it, has been extended in Oracle 11g.

Note. Most of the procedures and functions in the DBMS_WORKLOAD_REPOSITORY package accept a DBID parameter, which defaults to the local database identifier. For that reason the following examples will omit this parameter.

Fixed Baselines
The Moving Window Baseline
Baseline Templates
Baseline Metric Thresholds

Fixed Baselines

The fixed, or static, baseline functionality is a little more flexible in Oracle 11g compared to that of Oracle 10g. Originally, the DBMS_WORKLOAD_REPOSITORY package included a single CREATE_BASELINE procedure allowing you to define baselines using specific snapshot IDs. It now includes overloaded procedures and functions allowing baselines to be created using start and end times, which are used to estimate the relevant snapshot IDs. The functions have the same parameter lists as the procedures, but return the baseline ID. By default baselines are kept forever, but the new expiration parameter allows them to be automatically expired after a specified number of days.

SET SERVEROUTPUT ON
DECLARE
  l_return  NUMBER;
BEGIN
  -- Using procedures.
  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_snap_id =&gt; 2490,
    end_snap_id   =&gt; 2491,
    baseline_name =&gt; 'test1_bl',
    expiration    =&gt; 60);

  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_time    =&gt; TO_DATE('09-JUL-2008 17:00', 'DD-MON-YYYY HH24:MI'),
    end_time      =&gt; TO_DATE('09-JUL-2008 18:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name =&gt; 'test2_bl',
    expiration    =&gt; NULL);

  -- Using functions.
  l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
                start_snap_id =&gt; 2492,
                end_snap_id   =&gt; 2493,
                baseline_name =&gt; 'test3_bl',
                expiration    =&gt; 30);
  DBMS_OUTPUT.put_line('Return: ' || l_return);

  l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
                start_time    =&gt; TO_DATE('09-JUL-2008 19:00', 'DD-MON-YYYY HH24:MI'),
                end_time      =&gt; TO_DATE('09-JUL-2008 20:00', 'DD-MON-YYYY HH24:MI'),
                baseline_name =&gt; 'test4_bl',
                expiration    =&gt; NULL);

  DBMS_OUTPUT.put_line('Return: ' || l_return);
END;
/

Return: 8
Return: 9

PL/SQL procedure successfully completed.

SQL&gt;

The new baselines are visible in DBA_HIST_BASELINE view.

COLUMN baseline_name FORMAT A15

SELECT baseline_id, baseline_name, START_SNAP_ID,
       TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,
       END_SNAP_ID,
       TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM   dba_hist_baseline
WHERE  baseline_type = 'STATIC'
ORDER BY baseline_id;

BASELINE_ID BASELINE_NAME   START_SNAP_ID START_SNAP_TIME   END_SNAP_ID END_SNAP_TIME
----------- --------------- ------------- ----------------- ----------- -----------------
          6 test1_bl                 2490 09-JUL-2008 17:00        2491 09-JUL-2008 18:00
          7 test2_bl                 2490 09-JUL-2008 17:00        2491 09-JUL-2008 18:00
          8 test3_bl                 2492 09-JUL-2008 19:00        2493 09-JUL-2008 20:00
          9 test4_bl                 2492 09-JUL-2008 19:00        2493 09-JUL-2008 20:00

4 rows selected.

SQL&gt;

Information about a specific baseline can be displayed by using the BASELINE_ID with the SELECT_BASELINE_DETAILS pipelined table function, or the BASELINE_NAME with the SELECT_BASELINE_METRIC pipelined table function.

SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));

SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));

Baselines are renamed using the RENAME_BASELINE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.rename_baseline(
    old_baseline_name =&gt; 'test4_bl',
    new_baseline_name =&gt; 'test5_bl');
END;
/

Baselines are dropped using the DROP_BASELINE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name =&gt; 'test1_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name =&gt; 'test2_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name =&gt; 'test3_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name =&gt; 'test5_bl');
END;
/

Enterprise Manager is probably the most convenient way to manage AWR baselines. From the “AWR Baselines” screen (Server > AWR Baselines), click the “Create” button.

Select the interval type of “Single” and click the “Continue” button.

Enter a name for the baseline and mark the start and end of the baseline by either clicking on the snapshot icons or entering the time range manually, then click the “Finish” button.

The newly created baseline is now displayed in the “AWR Baselines” screen.

The baseline is renamed or dropped by checking its “Select” box and clicking the “Edit” or “Delete” button respectively. Once the baseline is created you can schedule statistics computation by checking its “Select” box, selecting “Schedule Statistics Computation” in the drop down list, then clicking “Go” button.

The Moving Window Baseline

Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period. The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.

The current AWR retention period can be displayed by querying the RETENTION column of the DBA_HIST_WR_CONTROL view.

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

1 row selected.

SQL&gt;

The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention =&gt; 43200);  -- Minutes (= 30 Days).
END;
/

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

1 row selected.

SQL&gt;

The current moving window size is displayed by querying the DBA_HIST_BASELINE view.

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                 8

1 row selected.

SQL&gt;

The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
    window_size =&gt; 20);
END;
/

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                20

1 row selected.

SQL&gt;

Oracle recommend of window size greater than or equal to 30 days when using adaptive thresholds.

To adjust the retention periods in Enterprise Manager, click on the “Edit” button in the “Automatic Workload Repository” screen (Server > Automatic Workload Repository).

Edit the “Use Time-Based Retention” to the appropriate number of days and click the “OK” button.

Next, navigate to the “AWR baselines” screen (Server > AWR Baselines), select the “SYSTEM_MOVING_WINDOW” baseline and click the “Edit” button.

Set the appropriate “Window Size” and click the “Apply” button.

Baseline Templates

Baseline templates allow you to define baselines you would like to capture in the future. Overloads of the CREATE_BASELINE_TEMPLATE procedure define the capture of individual baselines, or repeating baselines. Creating a single baseline template is similar to creating a time-based baseline, except the time is in the future.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
    start_time    =&gt; TO_DATE('01-DEC-2008 00:00', 'DD-MON-YYYY HH24:MI'),
    end_time      =&gt; TO_DATE('01-DEC-2008 05:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name =&gt; '01_dec_008_00_05_bl',
    template_name =&gt; '01_dec_008_00_05_tp',
    expiration    =&gt; 100);
END;
/

Templates for repeating baselines are a little different as they require some basic scheduling information. The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY – SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline. Since the template will generate multiple baselines, the baseline name is derived from the BASELINE_NAME_PREFIX concatenated to the date. The following example creates a template that will run for the next six months, gathering a baseline every Monday between 00:00 and 05:00.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
   day_of_week          =&gt; 'MONDAY',
   hour_in_day          =&gt; 0,
   duration             =&gt; 5,
   start_time           =&gt; SYSDATE,
   end_time             =&gt; ADD_MONTHS(SYSDATE, 6),
   baseline_name_prefix =&gt; 'monday_morning_bl_',
   template_name        =&gt; 'monday_morning_tp',
   expiration           =&gt; NULL);
END;
/

Information about baseline templates is displayed using the DBA_HIST_BASELINE_TEMPLATE view.

SELECT template_name,
       template_type,
       baseline_name_prefix,
       start_time,
       end_time,
       day_of_week,
       hour_in_day,
       duration,
       expiration
FROM   dba_hist_baseline_template;

TEMPLATE_NAME                  TEMPLATE_ BASELINE_NAME_PREFIX           START_TIME
------------------------------ --------- ------------------------------ --------------------
END_TIME             DAY_OF_WE HOUR_IN_DAY   DURATION EXPIRATION
-------------------- --------- ----------- ---------- ----------
01_dec_008_00_05_tp            SINGLE    01_dec_008_00_05_bl            01-DEC-2008 00:00:00
01-DEC-2008 05:00:00                                         100

monday_morning_tp              REPEATING monday_morning_bl_             11-JUL-2008 14:43:36
11-JAN-2009 14:43:36 MONDAY              0          5

2 rows selected.

SQL&gt;

Notice the BASELINE_NAME_PREFIX column holds either the prefix or full baseline name depending on the type of baseline being captured.

Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name =&gt; '01_dec_008_00_05_tp');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name =&gt; 'monday_morning_tp');
END;
/

Enterprise Manager uses the same screens for creating single baseline templates as it does for creating baselines. Navigate to the “AWR Baselines” screen (Server > AWR Baselines) and click the “Create” button.

Select the interval type of “Single” and click the “Continue” button.

Enter a name for the baseline template and mark the start and end of the baseline by entering the time range manually, then click the “Finish” button. If the time range is in the future, a baseline template is automatically created, rather than a regular baseline.

To view the baseline template, click the “AWR Baseline Templates” link at the bottom of the “AWR baselines” screen.

To creating the repeating baseline templates, start the baseline creation as before, but select the interval type of “Repeating” and click the “Continue” button.

Enter the baseline name prefix, which is also used as the template name, along with the interval and scheduling details, then click the “Finish” button.

Once again, the baseline template is visible on the “AWR Baseline Templates” screen.

Baseline Metric Thresholds

Oracle 11g simplifies the definition of metric thresholds by allowing them to be applied to baselines, including the moving window baseline. The easiest way to start using baseline metric thresholds is to click the “Quick Configuration” button on the “Baseline Metric Thresholds” screen (Home > Baseline Metric Thresholds).

Select the relevant “Workload Profile” for your system, then click the “Continue” button.

Click the “Finish” button on the review screen.

The adaptive thresholds are then visible on the “Baseline Metric Thresholds” screen. Click the category name or “Edit Threshold” icon to edit the threshold settings.

If you need to amend the threshold settings, remember to click the “Apply Thresholds” button once you have finished.

Go to Detail Page

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

December 7, 2011 Articles 0 0

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here.

Assumptions
Primary Server Setup
- Logging
- Initialization Parameters
- Service Setup
- Backup Primary Database
- Create Standby Controlfile and PFILE
Standby Server Setup (Manual)
- Copy Files
- Start Listener
- Restore Backup
- Create Redo Logs
Standby Server Setup (DUPLICATE)
- Copy Files
- Start Listener
- Create Standby Redo Logs on Primary Server
- Create Standby using DUPLICATE
Start Apply Process
Test Log Transport
Protection Mode
Database Switchover
Failover
Flashback Database
Read-Only Standby and Active Data Guard
Snapshot Standby

Assumptions

You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I’ve used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
The primary server has a running instance.
The standby server has a software only installation.

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL&gt;

If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “DB11G” on the primary database.

SQL&gt; show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 DB11G

SQL&gt; show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DB11G

SQL&gt;

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “DB11G_STBY”.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';

Set suitable remote archive log destinations. In this case I’m using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and patch differences between the servers.

ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.

$ rman target=/

RMAN&gt; BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';

Create a parameter file for the standby database.

CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;

Amend the PFILE making the entries relevant for the standby database. I’m making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

Standby Server Setup (Manual)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

Make sure the listener is started on the standby server.

$ lsnrctl start

Restore Backup

Create the SPFILE form the amended PFILE.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL&gt; CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';

Restore the backup files.

$ export ORACLE_SID=DB11G
$ rman target=/

RMAN&gt; STARTUP MOUNT;
RMAN&gt; RESTORE DATABASE;

Create Redo Logs

Create online redo logs for the standby. It’s a good idea to match the configuration of the primary server.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

When using active duplicate, the standby server requires static listener configuration in a “listener.ora” file. In this case I used the following configuration.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Make sure the listener is started on the standby server.

$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary “init.ora” file.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL&gt; STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.

$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
    SET FAL_SERVER='DB11G' COMMENT 'Is primary'
  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Start Apply Process

Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:

Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default a newly created standby database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL&gt;

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Make sure managed recovery is disabled.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.

SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL&gt;

You can now do treat the standby like any read-write database.

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL&gt;

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

Go to Detail Page

DBMS_PARALLEL_EXECUTE

December 7, 2011 Articles 0 0

DBMS_PARALLEL_EXECUTE

The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks which can be run in parallel. This process involves several distinct stages.

Create a task
Split the workload into chunks
- CREATE_CHUNKS_BY_ROWID
- CREATE_CHUNKS_BY_NUMBER_COL
- CREATE_CHUNKS_BY_SQL
Run the task
- RUN_TASK
- User-defined framework
- Task control
Check the task status
Drop the task

At the end of the article there are some complete examples, using some of the techniques discussed below.

The user controlling the process needs the CREATE JOB privilege.

CONN / AS SYSDBA
GRANT CREATE JOB TO test;

The examples used in this article require the following table to be created and populated.

CONN test/test

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  num_col     NUMBER,
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level, 5) = 0 THEN 10
         WHEN MOD(level, 3) = 0 THEN 20
         ELSE 30
       END
FROM   dual
CONNECT BY level &lt;= 500000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_TAB', cascade =&gt; TRUE);

SELECT num_col, COUNT(*)
FROM   test_tab
GROUP BY num_col
ORDER BY num_col;

   NUM_COL   COUNT(*)
---------- ----------
        10     100000
        20     133333
        30     266667

SQL&gt;

Create a task

The CREATE_TASK procedure is used to create a new task. It requires a task name to be specified, but can also include an optional task comment.

BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name =&gt; 'test_task');
END;
/

Information about existing tasks is displayed using the [DBA|USER]_PARALLEL_EXECUTE_TASKS views.

COLUMN task_name FORMAT A10
SELECT task_name,
       status
FROM   user_parallel_execute_tasks;

TASK_NAME  STATUS
---------- -------------------
test_task  CREATED

SQL&gt;

The GENERATE_TASK_NAME function returns a unique task name if you do not want to name the task manually.

SELECT DBMS_PARALLEL_EXECUTE.generate_task_name
FROM   dual;

GENERATE_TASK_NAME
--------------------------------------------------------------------------------
TASK$_726

SQL&gt;

Split the workload into chunks

The workload is associated with a base table, which can be split into subsets or chunks of rows. There are three methods of splitting the workload into chunks.

CREATE_CHUNKS_BY_ROWID
CREATE_CHUNKS_BY_NUMBER_COL
CREATE_CHUNKS_BY_SQL

The chunks associated with a task can be dropped using the DROP_CHUNKS procedure.

CREATE_CHUNKS_BY_ROWID

The CREATE_CHUNKS_BY_ROWID procedure splits the data by rowid into chunks specified by the CHUNK_SIZE parameter. If the BY_ROW parameter is set to TRUE, the CHUNK_SIZE refers to the number of rows, otherwise it refers to the number of blocks.

BEGIN
  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   =&gt; 'test_task',
                                               table_owner =&gt; 'TEST',
                                               table_name  =&gt; 'TEST_TAB',
                                               by_row      =&gt; TRUE,
                                               chunk_size  =&gt; 10000);
END;
/

Once the operation is complete the task status is changed to ‘CHUNKED’.

COLUMN task_name FORMAT A10

SELECT task_name,
       status
FROM   user_parallel_execute_tasks;

TASK_NAME  STATUS
---------- -------------------
test_task  CHUNKED

SQL&gt;

The [DBA|USER]_PARALLEL_EXECUTE_CHUNKS views display information about the individual chunks.

SELECT chunk_id, status, start_rowid, end_rowid
FROM   user_parallel_execute_chunks
WHERE  task_name = 'test_task'
ORDER BY chunk_id;

  CHUNK_ID STATUS               START_ROWID        END_ROWID
---------- -------------------- ------------------ ------------------
       287 UNASSIGNED           AAASjoAAEAAAAIwAAA AAASjoAAEAAAAI3CcP
       288 UNASSIGNED           AAASjoAAEAAAAI4AAA AAASjoAAEAAAAI/CcP
...
       450 UNASSIGNED           AAASjoAAEAAAAIIAAA AAASjoAAEAAAAIPCcP
       451 UNASSIGNED           AAASjoAAEAAAAIoAAA AAASjoAAEAAAAIvCcP

88 rows selected.

SQL&gt;

CREATE_CHUNKS_BY_NUMBER_COL

The CREATE_CHUNKS_BY_NUMBER_COL procedure divides the workload up based on a number column. It uses the specified columns min and max values along with the chunk size to split the data into approximately equal chunks. For the chunks to be equally sized the column must contain a continuous sequence of numbers, like that generated by a sequence.

BEGIN
  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name    =&gt; 'test_task',
                                                    table_owner  =&gt; 'TEST',
                                                    table_name   =&gt; 'TEST_TAB',
                                                    table_column =&gt; 'ID',
                                                    chunk_size   =&gt; 10000);
END;
/

The [DBA|USER]_PARALLEL_EXECUTE_CHUNKS views display information about the individual chunks.

SELECT chunk_id, status, start_id, end_id
FROM   user_parallel_execute_chunks
WHERE  task_name = 'test_task'
ORDER BY chunk_id;

  CHUNK_ID STATUS                 START_ID     END_ID
---------- -------------------- ---------- ----------
       600 UNASSIGNED                    1      10000
       601 UNASSIGNED                10001      20000
 ...
       648 UNASSIGNED               480001     490000
       649 UNASSIGNED               490001     500000

50 rows selected.

SQL&gt;

CREATE_CHUNKS_BY_SQL

The CREATE_CHUNKS_BY_SQL procedure divides the workload based on a user-defined query. If the BY_ROWID parameter is set to TRUE, the query must return a series of start and end rowids. If it’s set to FALSE, the query must return a series of start and end IDs.

DECLARE
  l_stmt CLOB;
BEGIN
  l_stmt := 'SELECT DISTINCT num_col, num_col FROM test_tab';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name =&gt; 'test_task',
                                             sql_stmt  =&gt; l_stmt,
                                             by_rowid  =&gt; FALSE);
END;
/

The [DBA|USER]_PARALLEL_EXECUTE_CHUNKS views display information about the individual chunks.

SELECT chunk_id, status, start_id, end_id
FROM   user_parallel_execute_chunks
WHERE  task_name = 'test_task'
ORDER BY chunk_id;

  CHUNK_ID STATUS                 START_ID     END_ID
---------- -------------------- ---------- ----------
       650 UNASSIGNED                   10         10
       651 UNASSIGNED                   30         30
       652 UNASSIGNED                   20         20

3 rows selected.

SQL&gt;

Run the task

Running a task involves running a specific statement for each defined chunk of work. The documentation only shows examples using updates of the base table, but this is not the only use of this functionality. The statement associated with the task can be a procedure call, as shown in one of the examples at the end of the article.

There are two ways to run a task and several procedures to control a running task.

RUN_TASK
User-defined framework
Task control

RUN_TASK

The RUN_TASK procedure runs the specified statement in parallel by scheduling jobs to process the workload chunks. The statement specifying the actual work to be done must include a reference to the ‘:start_id’ and ‘:end_id’, which represent a range of rowids or column IDs to be processed, as specified in the chunk definitions. The degree of parallelism is controlled by the number of scheduled jobs, not the number of chunks defined. The scheduled jobs take an unassigned workload chunk, process it, then move on to the next unassigned chunk.

DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN
  l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
                 SET    t.num_col = t.num_col + 10
                 WHERE rowid BETWEEN :start_id AND :end_id';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      =&gt; 'test_task',
                                 sql_stmt       =&gt; l_sql_stmt,
                                 language_flag  =&gt; DBMS_SQL.NATIVE,
                                 parallel_level =&gt; 10);
END;
/

The RUN_TASK procedure waits for the task to complete. On completion, the status of the task must be assessed to know what action to take next.

User-defined framework

The DBMS_PARALLEL_EXECUTE package allows you to manually code the task run. The GET_ROWID_CHUNK and GET_NUMBER_COL_CHUNK procedures return the next available unassigned chunk. You can than manually process the chunk and set its status. The example below shows the processing of a workload chunked by rowid.

DECLARE
  l_sql_stmt    VARCHAR2(32767);
  l_chunk_id    NUMBER;
  l_start_rowid ROWID;
  l_end_rowid   ROWID;
  l_any_rows    BOOLEAN;
BEGIN
  l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
                 SET    t.num_col = t.num_col + 10
                 WHERE rowid BETWEEN :start_id AND :end_id';

  LOOP
    -- Get next unassigned chunk.
    DBMS_PARALLEL_EXECUTE.get_rowid_chunk(task_name   =&gt; 'test_task',
                                          chunk_id    =&gt; l_chunk_id,
                                          start_rowid =&gt; l_start_rowid,
                                          end_rowid   =&gt; l_end_rowid,
                                          any_rows    =&gt; l_any_rows);

    EXIT WHEN l_any_rows = FALSE;

    BEGIN
      -- Manually execute the work.
      EXECUTE IMMEDIATE l_sql_stmt USING l_start_rowid, l_end_rowid;

      -- Set the chunk status as processed.
      DBMS_PARALLEL_EXECUTE.set_chunk_status(task_name =&gt; 'test_task',
                                             chunk_id  =&gt; l_chunk_id,
                                             status    =&gt; DBMS_PARALLEL_EXECUTE.PROCESSED);
      EXCEPTION
        WHEN OTHERS THEN
          -- Record chunk error.
          DBMS_PARALLEL_EXECUTE.set_chunk_status(task_name =&gt; 'test_task',
                                                 chunk_id  =&gt; l_chunk_id,
                                                 status    =&gt; DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR,
                                                 err_num   =&gt; SQLCODE,
                                                 err_msg   =&gt; SQLERRM);
    END;

    -- Commit work.
    COMMIT;
  END LOOP;
END;
/

Task control

A running task can be stopped and restarted using the STOP_TASK and RESUME_TASK procedures respectively.

The PURGE_PROCESSED_CHUNKS procedure deletes all chunks with a status of ‘PROCESSED’ or ‘PROCESSED_WITH_ERROR’.

The ADM_DROP_CHUNKS, ADM_DROP_TASK, ADM_TASK_STATUS and ADM_STOP_TASK routines have the same function as their namesakes, but they allow the operations to performed on tasks owned by other users. In order to use these routines the user must have been granted the ADM_PARALLEL_EXECUTE_TASK role.

Check the task status

The simplest way to check the status of a task is to use the TASK_STATUS function. After execution of the task, the only possible return values are the ‘FINISHED’ or ‘FINISHED_WITH_ERROR’ constants. If the status is not ‘FINISHED’, then the task can be resumed using the RESUME_TASK procedure.

DECLARE
  l_try NUMBER;
  l_status NUMBER;
BEGIN
  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status('test_task');
  WHILE(l_try &lt; 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task('test_task');
    l_status := DBMS_PARALLEL_EXECUTE.task_status('test_task');
  END LOOP;
END;
/

The status of the task and the chunks can also be queried.

COLUMN task_name FORMAT A10
SELECT task_name,
       status
FROM   user_parallel_execute_tasks;

TASK_NAME  STATUS
---------- -------------------
test_task  FINISHED

SQL&gt;

If there were errors, the chunks can be queried to identify the problems.

SELECT status, COUNT(*)
FROM   user_parallel_execute_chunks
GROUP BY status
ORDER BY status;

STATUS                 COUNT(*)
-------------------- ----------
PROCESSED                    88

SQL&gt;

The [DBA|USER]_PARALLEL_EXECUTE_TASKS views contain a record of the JOB_PREFIX used when scheduling the chunks of work.

SELECT job_prefix
FROM   user_parallel_execute_tasks
WHERE  task_name = 'test_task';

JOB_PREFIX
------------------------------
TASK$_368

SQL&gt;

This value can be used to query information about the individual jobs used during the process. The number of jobs scheduled should match the degree of parallelism specified in the RUN_TASK procedure.

COLUMN job_name FORMAT A20

SELECT job_name, status
FROM   user_scheduler_job_run_details
WHERE  job_name LIKE (SELECT job_prefix || '%'
                      FROM   user_parallel_execute_tasks
                      WHERE  task_name = 'test_task');

JOB_NAME             STATUS
-------------------- ------------------------------
TASK$_368_1          SUCCEEDED
TASK$_368_6          SUCCEEDED
TASK$_368_2          SUCCEEDED
TASK$_368_9          SUCCEEDED
TASK$_368_10         SUCCEEDED
TASK$_368_8          SUCCEEDED
TASK$_368_7          SUCCEEDED
TASK$_368_4          SUCCEEDED
TASK$_368_5          SUCCEEDED
TASK$_368_3          SUCCEEDED

10 rows selected.

SQL&gt;

Drop the task

Once the job is complete you can drop the task, which will drop the associated chunk information also.

BEGIN
  DBMS_PARALLEL_EXECUTE.drop_task('test_task');
END;
/

Complete examples

The following example shows the processing of a workload chunked by rowid.

DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name =&gt; l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   =&gt; l_task,
                                               table_owner =&gt; 'TEST',
                                               table_name  =&gt; 'TEST_TAB',
                                               by_row      =&gt; TRUE,
                                               chunk_size  =&gt; 10000);

  l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
                 SET    t.num_col = t.num_col + 10
                 WHERE rowid BETWEEN :start_id AND :end_id';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      =&gt; l_task,
                                 sql_stmt       =&gt; l_sql_stmt,
                                 language_flag  =&gt; DBMS_SQL.NATIVE,
                                 parallel_level =&gt; 10);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try &lt; 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

The following example shows the processing of a workload chunked by a number column. Notice that the workload is actually a stored procedure in this case.

CREATE OR REPLACE PROCEDURE process_update (p_start_id IN NUMBER, p_end_id IN NUMBER) AS
BEGIN
  UPDATE /*+ ROWID (dda) */ test_tab t
  SET    t.num_col = t.num_col + 10
  WHERE id BETWEEN p_start_id AND p_end_id;
END;
/

DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name =&gt; l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name    =&gt; l_task,
                                                    table_owner  =&gt; 'TEST',
                                                    table_name   =&gt; 'TEST_TAB',
                                                    table_column =&gt; 'ID',
                                                    chunk_size   =&gt; 10000);

  l_sql_stmt := 'BEGIN process_update(:start_id, :end_id); END;';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      =&gt; l_task,
                                 sql_stmt       =&gt; l_sql_stmt,
                                 language_flag  =&gt; DBMS_SQL.NATIVE,
                                 parallel_level =&gt; 10);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try &lt; 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

The following example shows a workload chunked by an SQL statement and processed by a user-defined framework.

DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_stmt     CLOB;
  l_sql_stmt VARCHAR2(32767);
  l_chunk_id NUMBER;
  l_start_id NUMBER;
  l_end_id   NUMBER;
  l_any_rows BOOLEAN;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name =&gt; l_task);

  l_stmt := 'SELECT DISTINCT num_col, num_col FROM test_tab';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name =&gt; l_task,
                                             sql_stmt  =&gt; l_stmt,
                                             by_rowid  =&gt; FALSE);

  l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
                 SET    t.num_col = t.num_col
                 WHERE num_col BETWEEN :start_id AND :end_id';

  LOOP
    -- Get next unassigned chunk.
    DBMS_PARALLEL_EXECUTE.get_number_col_chunk(task_name =&gt; 'test_task',
                                               chunk_id    =&gt; l_chunk_id,
                                               start_id    =&gt; l_start_id,
                                               end_id      =&gt; l_end_id,
                                               any_rows    =&gt; l_any_rows);

    EXIT WHEN l_any_rows = FALSE;

    BEGIN
      -- Manually execute the work.
      EXECUTE IMMEDIATE l_sql_stmt USING l_start_id, l_end_id;

      -- Set the chunk status as processed.
      DBMS_PARALLEL_EXECUTE.set_chunk_status(task_name =&gt; 'test_task',
                                             chunk_id  =&gt; l_chunk_id,
                                             status    =&gt; DBMS_PARALLEL_EXECUTE.PROCESSED);
      EXCEPTION
        WHEN OTHERS THEN
          -- Record chunk error.
          DBMS_PARALLEL_EXECUTE.set_chunk_status(task_name =&gt; 'test_task',
                                                 chunk_id  =&gt; l_chunk_id,
                                                 status    =&gt; DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR,
                                                 err_num   =&gt; SQLCODE,
                                                 err_msg   =&gt; SQLERRM);
    END;

    -- Commit work.
    COMMIT;
  END LOOP;

  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

Go to Detail Page

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

November 22, 2011 Articles 0 0

Duplicate a Database Using RMAN in Oracle Database 11g Release 2

Introduction
Backup-Based Duplication
Active Database Duplication

Introduction

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

The article assumes the duplicate database is being created on a separate server, using the same SID (DB11G) and the same file structure as the source database. Explanations of several other scenarios are available here.

Backup-Based Duplication

Create a backup of the source database, if a suitable one doesn’t already exist.

$ rman target=/

RMAN&gt; CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN&gt; BACKUP DATABASE PLUS ARCHIVELOG

All subsequent actions occur on the server running the duplicate database.

Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10

Add the appropriate entries into the “tnsnames.ora” file in the “$ORACLE_HOME/network/admin” directory to allow connections to the target database from the duplicate server.

# Added to the tnsnames.ora
DB11G-SOURCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

Create a PFILE for the duplicate database. Since we are duplicating the database onto a separate server with the same filesystem as the original, we don’t need to convert the file names. In this case, the PFILE is called “initDB11G.ora” and is placed in the “$ORACLE_HOME/dbs” directory.

# Minimum Requirement.
DB_NAME=DB11G

# Convert file names to allow for different directory structure if necessary.
#DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u01/app/oracle/oradata/NEWSID/)
#LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u02/app/oracle/oradata/NEWSID/)

Create any directories necessary for start the duplicate database.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Make the backup files from the source database available to the destination server. That can be done by either copying them to the matching location on the destination server, or placing them on a shared drive. If you are copying the files, you may want to use the following type of commands.

$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dup1:/u01/app/oracle/fast_recovery_area/DB11G/autobackup /u01/app/oracle/fast_recovery_area/DB11G

Connect to the duplicate instance.

$ ORACLE_SID=DB11G; export ORACLE_SID
$ sqlplus / as sysdba

Start the database in NOMOUNT mode.

SQL&gt; STARTUP NOMOUNT;

With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the duplicate database (AUXILIARY), but depending on the type of duplication we are doing we may optionally connect to the original database (TARGET) and/or the recovery catalog (CATALOG).

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman AUXILIARY /
$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY /
$ rman CATALOG rman/password@rman-catalog AUXILIARY /
$ rman TARGET sys/password@DB11G-SOURCE CATALOG rman/password@rman-catalog AUXILIARY /

We can then duplicate the database using one of the following commands.

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DB11G
  SPFILE
  NOFILENAMECHECK;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DB11G
  UNTIL TIME 'SYSDATE-4'
  SPFILE
  NOFILENAMECHECK;

# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;

The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.

First, and most obviously, you don’t need a backup of the source system, but it does have to be in ARCHIVELOG mode.

The passwords in the password files must match for both servers, so remember to set the correct password when creating the password file on the destination server.

Both the source and destination database servers require a “tnsnames.ora” entry for the destination database. In this case I added the following to each server. The destination server still requires the source entry shown in the previous section.

# Added to the tnsnames.ora on source and destination server.
DB11G-DESTINATION =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DB11G)
    )
  )

The destination server requires static listener configuration in a “listener.ora” file. In this case I used the following configuration. Remember to restart or reload the listener.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY sys/password@DB11G-DESTINATION

Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.

DUPLICATE DATABASE TO DB11G
  FROM ACTIVE DATABASE
  SPFILE
  NOFILENAMECHECK;

For more information see:

Duplicating a Database

Go to Detail Page

Installing and Configuring Oracle Database 11g on the Linux Platform

October 31, 2011 Articles 0 0

Introduction

This paper will walk you through the steps of installing Oracle Database 11g release 2 (Oracle version 11.2.0) in a Linux environment. About 90% of the material presented here applies to other platforms as well. Everything you read in this paper is hands on, roll-up-your-sleeves-and-get-busy material for Oracle users who want to get an Oracle database up and running quickly without reading hundreds of pages of documentation and “readme” files.

These steps are meant to get you up and running as fast as possible, while leveraging best practices in order to set up a scalable, robust database environment that offers high performance. In order to keep the steps reasonably simple this paper does not cover Real Application Clusters (RAC), nor does it cover Oracle Internet Directory (OID), Automatic Storage Management (ASM), or Grid Control.

In this paper we will install the 11.2.0.1 release of Oracle Database 11g. This is the base distribution of Oracle Database 11g release 2. Be sure to check Oracle Support’s Metalink at http://metalink.oracle.com to see if a newer patch set has been released since this paper was published. You will need a valid Customer Service Identifier (CSI) number in order to access the Metalink website.

We ran our Oracle installations on servers equipped with Intel Xeon processors running Red Hat Enterprise Linux ES release 5 (Tikanga), update 4 (x86_64). Oracle Database 11g is supported on other processor architectures and Linux distributions as well. Note that with Red Hat Enterprise Linux, the ES and AS varieties are supported while WS is not.

There are four phases to getting Oracle up and running on your server:

Prepare the server
Install the Oracle software and latest patch set
Create a database
Complete the server configuration

We will walk through these phases one at a time, detailing all the steps involved. The end result will be a very usable database that can be scaled up quite large, and an Oracle installation that follows industry-recognized best practices. Of course, every implementation is unique, and you will need to evaluate each step carefully against your particular requirements. However, this paper will get you off to a very solid start.

The author wishes to acknowledge that this paper draws heavily from the previous Database Specialists, Inc. paper “Installing and Configuring Oracle Database 10g on the Linux Platform” by Roger Schrag – thanks Roger!

Prepare the Server

These steps configure your database server so that it will be ready to accept the Oracle software and database. In this section, we will make sure your server meets Oracle’s minimum requirements, create a Linux user and group to “own” the software, and create some directories that will be used by the Oracle software and database. All of the steps in this section are run as the root user.

Make sure that your hardware platform (processor architecture) is certified by Oracle Corporation for use with Oracle Database 11g release 2, and that you have acquired the correct distribution of Oracle software for your hardware platform. As of May 2010, the supported hardware platforms for Oracle on Linux are as follows:

Supported Hardware Platforms for Oracle Database 11g release 2
x86 (Intel and AMD processor chips that adhere to the x86 32-bit architecture)
x86-64 (AMD64/EM64T and Intel processor chips that adhere to the x86-64 architecture)

The following commands can be used to identify the processor architecture on your database server:

	$ uname -m
	$ grep "model name" /proc/cpuinfo

Make sure that your Linux distribution is certified by Oracle Corporation for use with Oracle Database 11g release 2. Note that certifications vary by hardware platform. As of May 2010, the supported Linux distributions are as follows:

Supported Linux Distributions for Oracle Database 11g release 2
Oracle Enterprise Linux 5 update 2, kernel 2.6.18-92 or higher
Oracle Enterprise Linux 4 update 7
Red Hat Enterprise Linux AS/ES 5 update 2 or later
Red Hat Enterprise Linux AS/ES 4 update 7 or later
SUSE Linux Enterprise Server 11.0, kernel 2.6.27.19-5 or higher
SUSE Linux Enterprise Server 10.0 with SP2 or later, kernel 2.6.16.21-0.8 or higher
Asianux 3.0
Asianux 2.0 (update 7 required for x86)

Be sure to check Oracle Support’s Metalink for the latest certification information, because it is quite possible that Oracle Database 11g release 2 has been certified with additional Linux distributions or hardware platforms since this paper was written.

On systems running Red Hat distributions of Linux, you can use the following command to determine exactly which version and update of Red Hat you are using:

      $ cat /etc/redhat-release

Make sure that all of the required operating system packages have been installed on the database server. Which packages and versions are required will vary depending on your Linux distribution and hardware platform. The package version specified is typically a minimum, meaning that newer versions of the package are usually acceptable.

Required Packages for Red Hat Enterprise Linux 4
Oracle Enterprise Linux 4 and Asianux 2.0
(x86 Hardware Platform)

binutils-2.15.92.0.2

compat-libstdc++-33.2.3

elfutils-libelf-0.97

elfutils-libelf-devel-0.97

gcc-3.4.6

gcc-c++-3.4.6

glibc-2.3.4-2.41

glibc-common-2.3.4

glibc-devel-2.3.4

glibc-headers-2.3.4

libaio-devel-0.3.105

libaio-0.3.105

libgcc-3.4.6

libstdc++-3.4.6

libstdc++-devel-3.4.6

make-3.80

pdksh-5.2.14

sysstat-5.0.5

unixODBC-2.2.11

unixODBC-devel-2.2.11

Required Packages for Red Hat Enterprise Linux 5
Oracle Enterprise Linux 5 and Asianux 3.0
(x86 Hardware Platform)

binutils-2.17.50.0.6

compat-libstdc++-33-3.2.3

elfutils-libelf-0.125

elfutils-libelf-devel-0.125

elfutils-libelf-devel-static-0.125

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24

glibc-common-2.5

glibc-devel-2.5

glibc-headers-2.5

kernel-headers-2.6.18

ksh-20060214

libaio-0.3.106

libaio-devel-0.3.106

libgcc-4.1.2

libgomp-4.1.2

libstdc++-4.1.2

libstdc++-devel 4.1.2

make-3.81

sysstat-7.0.2

unixODBC-2.2.11

unixODBC-devel-2.2.11

Required Packages for Red Hat Enterprise Linux 4
Oracle Enterprise Linux 4 and Asianux 2.0
(x86-64 Hardware Platform)

binutils-2.15.92.0.2

compat-libstdc++-33-3.2.3 (both 32 and 64 bit required)

elfutils-libelf-0.97

elfutils-libelf-devel-0.97

expat-1.95.7

gcc-3.4.6

gcc-c++-3.4.6

glibc-2.3.4-2.41 (both 32 and 64 bit required)

glibc-common-2.3.4

glibc-devel-2.3.4

glibc-headers-2.3.4

libaio-0.3.105 (both 32 and 64 bit required)

libaio-devel-0.3.105 (both 32 and 64 bit required)

libgcc-3.4.6 (both 32 and 64 bit required)

libstdc++-3.4.6 (both 32 and 64 bit required)

libstdc++-devel 3.4.6

make-3.80

pdksh-5.2.14

sysstat-5.0.5

unixODBC-2.2.11 (both 32 and 64 bit required)

unixODBC-devel-2.2.11 (both 32 and 64 bit required)

Required Packages for Red Hat Enterprise Linux 5
Oracle Enterprise Linux 5 and Asianux 3.0
(x86-64 Hardware Platform)

binutils-2.17.50.0.6

compat-libstdc++-33-3.2.3 (both 32 and 64 bit required)

elfutils-libelf-0.125

elfutils-libelf-devel-0.125

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24 (both 32 and 64 bit required)

glibc-common-2.5

glibc-devel-2.5 (both 32 and 64 bit required)

glibc-headers-2.5

ksh-20060214

libaio-0.3.106 (both 32 and 64 bit required)

libaio-devel-0.3.106 (both 32 and 64 bit required)

libgcc-4.1.2 (both 32 and 64 bit required)

libstdc++-4.1.2 (both 32 and 64 bit required)

libstdc++-devel 4.1.2

make-3.81

sysstat-7.0.2

unixODBC-2.2.11 (both 32 and 64 bit required)

unixODBC-devel-2.2.11 (both 32 and 64 bit required)

Required Packages for SUSE Linux Enterprise Server 10.0
(x86 Hardware Platform)

binutils-2.16.91.0.5

compat-libstdc++-5.0.7

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24

glibc-devel-2.4

ksh-93r-12.9

libaio-0.3.104

libaio-devel-0.3.104

libelf-0.8.5

libgcc-4.1.2

libstdc++-4.1.2

libstdc++-devel-4.1.2

make-3.80

sysstat-8.0.4

cvudisk-1.0.2-1 (from the 11gR2 Clusterware disk)

unixODBC-2.2.11 (if intending to use ODBC)

unixODBC-devel-2.2.11 (if intending to use ODBC)

Required Packages for SUSE Linux Enterprise Server 11.0
(x86 Hardware Platform)

binutils-2.19

gcc43-4.3.3_20081022-11.18

gcc43-c++-4.3.3_20081022-11.18

gcc-4.3

gcc-c++-4.3

glibc-2.9

glibc-devel-2.9

ksh-93t

libstdc++33-3.3.3

libstdc++43-4.3.3_20081022

libstdc++43-devel-4.3.3_20081022

libaio-0.3.104

libaio-devel-0.3.104

libgcc43-4.3.3_20081022

libstdc++-devel-4.3

linux-kernel-headers-2.6.27-2.22

make-3.81

sysstat-8.1.5

Required Packages for SUSE Linux Enterprise Server 10.0
(x86-64 Hardware Platform)

binutils-2.16.91.0.5

compat-libstdc++-5.0.7

gcc-4.1.0

gcc-c++-4.1.2

glibc-2.5-24

glibc-devel-2.4

glibc-devel-32bit-2.4

ksh-93r-12.9

libaio-0.3.104

libaio-32bit-0.3.104

libaio-devel-0.3.104

libaio-devel-32bit-0.3.104

libelf-0.8.5

libgcc-4.1.2

libstdc++-4.1.2

libstdc++-devel-4.1.2

make-3.80

sysstat-8.0.4

Required Packages for SUSE Linux Enterprise Server 11.0
(x86-64 Hardware Platform)

binutils-2.19

gcc-4.3

gcc-32bit-4.3

gcc-c++-4.3

glibc-2.9

glibc-32bit-2.9

glibc-devel-2.9

glibc-devel-32bit-2.4

ksh-93t

libaio-0.3.104

libaio-32bit-0.3.104

libaio-devel-0.3.104

libaio-devel-32bit-0.3.104

libstdc++33-3.3.3

libstdc++33-32bit-3.3.3

libstdc++43-4.3.3_20081022

libstdc++43-32bit-4.3.3_20081022

libstdc++43-devel-4.3.3_20081022

libstdc++43-devel-32bit-4.3.3_20081022

libgcc43-4.3.3_20081022

libstdc++-devel-4.3

make-3.81

sysstat-8.1.5

Notes: elfutils-libelf-devel has a mutual dependency with elfutils-libelf-devel-static so they must both be installed with a single rpm command e.g. (for x86-64)

	rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.x86_64.rpm elfutils-libelf-devel-0.137-3.el5.x86_64.rpm

Also some of these packages also have pre-reqs e.g. glibc-headers requires glibc-kernheaders, gcc (x86_64) requires libgomp, glibc-headers (x86_84) required kernel-headers (x86_64), etc.You can use the following command to verify that a package has been installed:

      $ rpm -q &lt;package name&gt;

The following command will verify all of the packages required on Red Hat Enterprise Linux 5 (x86_86) systems:

       $ rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel \
	       gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers ksh \
	       libaio libaio-devel libgcc libstdc++- libstdc++-devel make \
	       sysstat unixODBC unixODBC-devel

Note that in the cases where both the 32 bit and 64 bit architecture of an RPM are required you should see the same package twice in the output. You can confirm that both have been installed with a command similar to the following:

      $ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep libaio

You will need to perform the Oracle installation from an X window environment—you cannot use a character mode environment such as an SSH or telnet session. There is a facility for performing non-interactive installations (“silent” installs), but we won’t be covering that technique here. Your X environment can be the console on the database server, but it does not need to be. You can also use a Windows X emulator like Cygwin. If the database server is in a remote location, you can use SSH to securely forward X traffic from the database server back to your desktop. You can also use VNC to install remotely. We have run installations from a Windows desktop using both Cygwin and VNC and have had no problems with either. Over slow networks, VNC seems to be faster than X.
Make sure that your hardware is sufficient. You’ll need at least 1024 Mb RAM, a swap space at least the size of physical memory (or 1.5 times the amount of physical memory if you have 2 Gb or less of RAM), and a bare minimum of 6.5 Gb of disk space. This will let you perform a “typical” Enterprise or Standard Edition software installation from CD or DVD and create a starter database. If you will be downloading the Oracle software from Oracle Technology Network at http://www.oracle.com/technology, you will need about 1.4 Gb of additional disk space to download and unpack the Oracle software. A production implementation typically requires more RAM and more disk space than the minimums listed here. The following commands will allow you to check physical memory and swap space:
```
      $ grep MemTotal /proc/meminfo
      $ grep SwapTotal /proc/meminfo
```
The Oracle installer will need access to a directory with at least 1 Gb of free space for writing temporary files during installation. Usually /tmp serves this purpose. If /tmp on your database server has less than 1 Gb of free space, then you will need to locate another directory with sufficient free space for use during the installation. You can point at this other directory by setting the TMP and TMPDIR environment variables in the oracle user’s environment

Make sure that the Linux kernel on the database server has parameters set sufficiently high for Oracle. The Oracle architecture makes extensive use of shared memory segments for sharing data among multiple processes and semaphores for handling locking. Many operating systems, including Linux, do not by default offer sufficient shared memory or semaphores for optimal Oracle performance. Thankfully, you can change kernel parameters in Linux simply by editing the /etc/sysctl.conf file and rebooting the server. The following script will show the current settings of the Linux kernel parameters relevant to Oracle:

      K="/tmp/kernelparams$$"
      /sbin/sysctl -a &gt; $K 2&gt; /dev/null
      grep aio-max-nr $K
      grep kernel.shm $K
      echo "`grep sem $K | tr '\t' ' '` # semmsl semmns semopm semmni"
      grep file-max $K
      grep ip_local_port_range $K | tr '\t' ' '
      grep rmem_default $K
      grep rmem_max $K
      grep wmem_default $K
      grep wmem_max $K
      grep aio-max-nr $K
      rm -f $K

The following table shows the purpose of each of these kernel parameters and a recommended setting to get you started:

Kernel Parameter	Setting To Get You Started	Purpose
aio-max-nr	1048576	The total number of concurrent outstanding I/O requests
shmmni	4096	Maximum number of shared memory segments
shmall	2097152	Maximum total shared memory (4 Kb pages)
shmmax	4294967295	Maximum size of a single shared memory segment. Set to either (4GB -1) or 1/2 the size of physical memory (in bytes) whichever is lower
semmsl	250	Maximum number of semaphores per set
semmns	32000	Maximum number of semaphores
semopm	100	Maximum operations per semop call
semmni	128	Maximum number of semaphore sets
file-max	6815744	Maximum number of open files
ip_local_port_range	9000 – 65500	Range of ports to use for client connections
rmem_default	262144	Default TCP/IP receive window
rmem_max	4194304	Maximum TCP/IP receive window
wmem_default	262144	Maximum TCP/IP send window
wmem_max	1048576	Maximum TCP/IP send window

These settings will be appropriate for most systems. If you decide to configure a very large buffer cache or library cache for your database down the road, or if you choose to run a large number of databases on one server, then you may need to increase the shmall setting and possibly the shmmax setting as well. In addition, if you configure your database to allow a large number of concurrent sessions without using Oracle’s shared server architecture, then you may need to increase the semmsl and semmns settings as well.

Note that these recommended settings assume you have no other applications running on the database server that use shared memory segments or semaphores. You can view current shared memory and semaphore usage on your system with the following command:

      $ ipcs

In general, if your Linux kernel already has any of these parameters set larger than recommended here, you should not reduce the settings.

We added the following lines to the end of our /etc/sysctl.conf file:

      # Kernel parameter settings for Oracle
	fs.aio-max-nr = 1048576
	fs.file-max = 6815744
	kernel.shmall = 2097152
	kernel.shmmax = 4294967295
	kernel.shmmni = 4096
	kernel.sem = 250 32000 100 128
	net.ipv4.ip_local_port_range = 9000 65500
	net.core.rmem_default = 262144
	net.core.rmem_max = 4194304
	net.core.wmem_default = 262144
	net.core.wmem_max = 1048586

Depending upon your Linux version you can dynamically update the system values via the command

      $ /sbin/sysctl -p

or simply reboot the server for these parameters to take effect.If you are using SUSE Linux, then you must run the following command before rebooting the server to ensure that the /etc/sysctl.conf file will be read during reboot:

      $ /sbin/chkconfig boot.sysctl on

Create a Linux group that will be used by the Oracle software owner. You can call it anything you like, but the standard is “oinstall”. This group is often called the “Oracle Inventory” group. If you will be installing Oracle on multiple servers on your network, you might want to keep the groupid the same on all servers. You can create your oinstall group with a command like:
```
      $ /usr/sbin/groupadd -g 501 oinstall
```

If you are using SUSE Linux, then you must also enter the GID of the oinstall group as the value for the parameter /proc/sys/vm/hugetlb_shm_group and add vm.hugetlb_shm_group to the /etc/sysctl.conf file. Doing this grants members of the oinstall group permission to create shared memory segments. For example, where the oinstall group GID is 501 (as above) enter

      $ echo 501 &gt; /proc/sys/vm/hugetbl_shm_group

Add add the line

      vm.hugetlb_shm_group=501

Create a Linux group that will be used by Oracle database administrators. You can call it anything you like, but the standard is “dba”. Anybody who logs onto the database server with a Linux login that belongs to this group will be able to log onto all databases that run from this Oracle software installation with DBA privileges. If you will be installing Oracle on multiple servers on your network, you might want to keep the groupid the same on all servers. You can create your dba group with a command like:
```
      $ /usr/sbin/groupadd -g 502 dba
```
Create a Linux user that will be the Oracle software owner. You can call it anything you like, but the standard is “oracle”. If you will be installing Oracle on multiple servers on your network, you might want to keep the userid the same on all servers. Note that this user’s home directory will not be the ORACLE_HOME or where the actual Oracle software is installed; this user’s home directory should be in the same place as other users’ home directories.The Oracle software owner should have the oinstall group as the primary group and the dba group as a secondary group. You can create your oracle user with commands like:
```
      $ /usr/sbin/useradd -c 'Oracle software owner' -d /home/oracle \
                          -g oinstall -G dba -m -u 501 -s /bin/bash oracle
      $ passwd oracle
```
The useradd command shown here gives your oracle user the Bash shell. You could just as easily choose Korn or Bourne shell instead.

It is necessary to increase limits that the shell imposes on the oracle user for maximum number of open file descriptors and processes. Follow these steps to increase the limits: 

Add the following lines in the /etc/security/limits.conf file:

      oracle           soft    nproc   2047
      oracle           hard    nproc   16384
      oracle           soft    nofile  1024
      oracle           hard    nofile  65536

Add the following lines to the /etc/pam.d/login file, if they are not already present:

      session    required     /lib/security/pam_limits.so
      session    required     pam_limits.so

Add the following lines in the /etc/profile file (or the /etc/profile.local file on SUSE systems) if the oracle user uses the Bash, Korn or Bourne shell:

      if [ $USER = "oracle" ]; then
              if [ $SHELL = "/bin/ksh" ]; then
                    ulimit -p 16384
                    ulimit -n 65536
              else
                    ulimit -u 16384 -n 65536
              fi
              umask 022
      fi

If the oracle user uses the C shell, then add the following lines in the /etc/csh.login file (or the /etc/csh.login.local file on SUSE systems):

      if ( $USER == "oracle" ) then
              limit maxproc 16384
              limit descriptors 65536
              umask 022
      endif

Create mount points for the Oracle software and the Oracle database. Each mount point should correspond to a separate physical device or set of devices. You’ll need at least one mount point. Typically you use one mount point for the Oracle software and one or more mount points for each database. One common convention is to call the mount points /u01, /u02, and so on. Because mount points are typically owned by root and the Oracle installer will run as the oracle user and not as root, you should create some subdirectories now to avoid permission problems later. Create an app/oracle subdirectory below the software mount point, and oradata subdirectories below the mount points to be used for database files. (You can put software and a database on the same mount point if you wish.) Make these subdirectories owned by the oracle user and oinstall group, and give them 775 permissions, except for the datafile directory, give this 750 permissions. You can use commands like:
```
      $ mkdir -p /u01/app /u01/app/oracle /u01/oradata
      $ chown oracle:oinstall /u01/app /u01/app/oracle /u01/oradata
      $ chmod 775 /u01/app /u01/app/oracle
      $ chmod 750 /u01/oradata
```
If you downloaded the Oracle software Oracle Technology Network, then use unzip to unpack the distribution. If you have the software on CD or DVD, then mount the Oracle Database 11g release 2 media now.
As an optional step, it is suggested that you validate your configuration using the pre-install tests of Oracle’s Remote Diagnostic Agent (rda.sh), you can find details of doing this in Metalink note 250262.1. The procedure is to download the latest version of rda.sh and run its pre-install checklist via the command
```
  cd &lt;directory containing rda.sh&gt;
  ./rda.sh -T hcve
```
then choose the appropriate option ‘Oracle Database 11g R2 (11.2.0) Preinstall (Linux AMD64)’ option. As of May 2010 the 11gR2 pre-install option does not exist for AMD64 but does exist for Linux-x86, we expect AMD64 support will be added shortly.

Install the Oracle Software and Latest Patch Set

These steps install the Oracle software and latest patch set on your server. As of this writing, Oracle release 11.2.0.1.0 is the only version of Oracle Database 11g release 2 available for download from Oracle Technology Network or available on CD or DVD media. At some point in the future, Oracle will release latest patchsets (e.g. 11.2.0.2, 11.2.0.3, etc.) however the first such patchset has not yet been release. Therefore, we will install Oracle release 11.2.0.1.0. Once Oracle have released a patch set you should apply the patchset on top of the base 11.2.0.1.0 release. Before proceeding with the steps in this section, you should check Oracle Technology Network (http://www.oracle.com/technology) and Oracle Support’s Metalink (http://metalink.oracle.com) to see if any 11g release 2 patch sets are available.

The Oracle Universal Installer will suggest creating a database at the same time that it installs the Oracle software. Although we do not have any latest patchsets to install, we will hold off on the database creation until a later step since that is the preferred sequence of events once a 11g release 2 patch set is eventually released.

In this section, we will prepare the oracle user’s environment, run the Oracle Universal Installer and tidy up a few minor loose ends. All of the steps in this section, except where noted, are run as the oracle user.

Edit the oracle user’s login file on the database server so that the environment will be configured automatically on login. If you are using Bash shell, then edit .bash_profile. If you are using Bourne or Korn shell, then edit .profile. You can also use C shell and edit .cshrc, but the syntax will be different from the examples you see here. For now, we will hardcode certain things. But after we create a database, we will come back and eliminate all hard codings. Here is what we added to our .bash_profile for the install:

      #
      # Substitute your Oracle software mount point in the line below.
      export ORACLE_BASE=/u01/app/oracle
      #
      # Ensure that ORACLE_HOME and TNS_ADMIN are not set.
      unset ORACLE_HOME
      unset TNS_ADMIN
      #
      # If your /tmp directory has less than 1 Gb free, then edit
      # and uncomment the following three lines.
      # TMP=/mount_point_with_1Gb_free
      # TMPDIR=/same_mount_point
      # export TMP TMPDIR
      #
      # The documentation does not mention how PATH should be set.
      # The following PATH setting worked for us:
      export PATH=/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin

Log out and log back in as the oracle user from an X window so that the environment is set correctly. If you will be performing the installation from a PC or other workstation instead of using the database server’s console directly, you may wish to forward X window traffic over an SSH connection. This offers increased security (in the case of a public network) and convenience. If you will be performing the installation from a Windows PC, you can use PuTTY to forward your X window traffic by selecting the “Enable X11 forwarding” checkbox in PuTTY’s SSH Tunnels configuration screen. As an alternative to X, you may wish instead to start a VNC server on your database server by running the following command as the oracle user:
```
      $ /usr/bin/vncserver
```
With a VNC server running in this way, you can achieve an X environment by running a VNC viewer on your desktop workstation. You can shut down the VNC server by running the following command:
```
      $ /usr/bin/vncserver -kill :1
```
Make sure your DISPLAY variable is set. If you are forwarding X window traffic over an SSH connection, using VNC, or working from the server’s console directly, the DISPLAY variable should already be set for you. If your DISPLAY variable has not been set already, then you will need to set it manually to the IP address of your X server plus the X server and screen numbers. You can set your DISPLAY variable with a command like:
```
      $ export DISPLAY=myworkstation:0.0
```
If you had to set your DISPLAY variable manually in the previous step, then ensure that the X server on your workstation will allow your database server to open windows on your display. The easiest way to do this is to issue an xhost command from a session on your workstation. (Don’t get confused and issue the command in a window that is logged onto your database server.) You can issue a command like:
```
      $ xhost +mydatabaseserver
```
Ensure that the mount point you plan to use for the Oracle software has sufficient free space. For a basic Enterprise Edition and patch set installation, allow 1.6 Gb for the software mount point as a bare minimum.
Double check that you are logged in as oracle and not root, and that the environment variables have been set by the login script you prepared earlier. Then change to your home directory and start the Oracle Universal Installer with a command like one of the following:
```
      $ &lt;full path to DVD&gt;/database/runInstaller
```
or
```
      $ &lt;full path to CD&gt;/runInstaller
```
We’ll walk through the installer prompts one at a time: 
1. The Configure Security Updates window appears. If you wish to receive security updates via email then enter your email address and your Oracle support password. If not, deselect the checkbox. Click Next. If you deselected the checkbox hit ‘Yes’ when the popup warning that you have not provided an email address appears.
2. The Select Installation Option windows appears. Choose option ‘Install database software only’. Click Next.
3. The Node Selection window appears. Choose Single instance database installation and then click Next.
4. When the Select Product Languages window appears move the desired languages into the right hand panel. Click Next.
5. The Select Database Edition window appears. We will perform a “typical” install to get a basic set of Oracle software installed. You can rerun the installer again later and choose Custom to install additional products individually. For now, choose Standard Edition or Enterprise Edition. The Enterprise Edition of Oracle Database 11g has some very sophisticated features missing from Standard Edition, and the opportunity to purchase additional options that might be valuable to a large enterprise. However, the Enterprise Edition is much more expensive than Standard Edition. It is very important that you choose the edition that matches your license, as this will be difficult to fix later. The Select Options button display 6 extra cost options that can only be licensed against the Enterprise Edition. Again your specific license agreement needs to be consulted here, it is recommended that you deselect the extra cost options that your have not purchased. Click Next.
6. The Specify Installation Location window appears. Oracle provides a suggested Oracle Base Path for the Oracle home (software installation) that is about to be created. You can name this Oracle home anything you like. Beginning in Oracle Database 11g the standard for Oracle home location has changed to /<mount-point>/app/oracle/product/11.2.0/dbhome_<N>. In 10g the dbhome string was shortened to db. The naming convention, such as dbhome_1, dbhome_2 allows you to install multiple copies of the same Oracle version on one server in a standardized way. Note that we will refer back to this path frequently, calling it the Oracle home or simply $ORACLE_HOME. If you are planning to go with the path suggestion provided by Oracle, make sure there isn’t an extra occurrence of the “oracle” component in the path.When you are satisfied with the path of Oracle Base and the name and path for your Oracle home, click Next.
7. If you do not have at least 4,397MB of free disk space available in your $ORACLE_HOME you will get an error. Make sure you have enough free space available, otherwise you will not be able to proceed with the install.
8. If the Create Inventory window appears, set the inventory directory to the parent of the $ORACLE_BASE directory, remember you set the value of the $ORACLE_BASE environment variable in the login script. In the oraInventory Group Name field, select the oinstall group. Click Next. You won’t see this window if you have previously installed Oracle software on this database server. Historically it was common to have the inventory directory located as $ORACLE_BASE/oraInventory but in 11g Oracle are recommending against this configuration.
9. The Privileged Operating System Groups screen appears. Set the OSDBA Group to ‘dba’ and the OSOPER Group to ‘oinstall’. Click Next.
10. The Prerequisite Checks will run, if they all pass then the installer will continue onto the Summary window. Obviously if you have failed any prerequisite checks you should resolve them before proceeding. Assuming you made it to the Summary window Click Finish.
11. During the installation an Execute Configuration Scripts window will appear. The installation will be paused at this point, waiting for you to run scripts as root. (The first time you install Oracle software on the database server there will be two scripts to run as root, while additional installations only require one script to be run as root.) You should open another window, log in to the database server as root, review the scripts to be run thoroughly, run the scripts, and click OK in the Execute Configuration Scripts window.
12. Once the root scripts have ran the installer will display the Finish window, click Close to end the installation.
At this point we are ready to patch the Oracle software installation with the latest maintenance release available. Log onto Oracle Oracle Support’s Metalink (http://metalink.oracle.com) and download the latest patch set for Linux x86_64 or whichever processor architecture your database server uses. As of this writing, there are no patch sets available so we will skip this step, once a patch set becomes available we will update this document with the instructions for the patch set.
In addition to the latest maintenance release you should consider applying the latest ‘Critical Patch Update (CPU)’ which contains the latest security patches or the current ‘Patch Set Update (PSU)’ which contains Oracle’s recommended bug fixes in addition to the latest security patches. These patches are released quarterly. Since the instructions may differ slightly from one patch to the next so we do not cover the specifics here. You can find the latest patch and its install instructions on Oracle Support site.
In $ORACLE_HOME/bin (the bin directory under your Oracle home) you will find a shell script called oraenv. This script can be called from .bash_profile or .profile to set up a user’s environment automatically whenever they log onto the database server. We will customize the oraenv script because there are a few variables that the script should set but doesn’t. Make a backup copy of the oraenv script and then edit it, adding the following lines to the very end:

      # Begin customizations
      DBA=$ORACLE_BASE/admin
      # Substitute the locale and character set you plan to use for your
      # database in the line below. Nowadays the two main common choices are:
      #   NLS_LANG=american_america.WE8MSWIN1252 (11gR2 Unix default)
      #   NLS_LANG=american_america.AL32UTF8     (Unicode 5)
      NLS_LANG=american_america.WE8MSWIN1252
      export DBA NLS_LANG
      # End customizations

You should set NLS_LANG to match the character set of the database you will create later. The Database Configuration Assistant (dbca) now offers two primary choices, the default character set for your platform based on the upon the language setting of your operating system (WE8MSWIN1252 in our install) and AL32UTF8 which corresponds to Unicode 5.0 in 11g. Oracle recommend using AL32UTF8 for the database character set e.g. see Metalink note 333489.1. You should carefully choose your character set as it is not easy to change after the database is created.

In the same directory you’ll also find a shell script called coraenv that can be called from .cshrc. If you use C shell, you will want to back up and edit coraenv with similar changes to the oraenv script.
The root.sh script copied oraenv and coraenv from $ORACLE_HOME/bin to the /usr/local/bin directory. You just updated these scripts in $ORACLE_HOME/bin. Copy the updated versions to the /usr/local/bin directory.

Create a Database

These steps create an Oracle database. Everybody will have different needs for their database, but the steps here will yield a functional database that you can further tailor to your specific needs. In this section we will use the Database Configuration Assistant to create a database, adjust the database in order to better comply with industry-proven best practices, and configure Oracle Net. All of the steps in this section are run as the oracle user.

Set up your environment the same way you did when you ran the Oracle Universal Installer: Log in as the oracle user on the database server from an X window or VNC session, set your DISPLAY variable appropriately, and make sure that your ORACLE_BASE variable is set correctly based on your login file.
Set the ORACLE_HOME environment variable to point to your Oracle home with a command like:
```
      $ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
```
Choose a name for your Oracle instance, up to eight characters long. The instance name is easy to change at any time. However, you will want to keep the instance name the same as the database name in order to avoid confusion. Changing the database name later is possible, but not the easiest thing to do. So pick a name for the instance that you like. Set the ORACLE_SID variable accordingly with a command like:
```
      $ export ORACLE_SID=dev11ee
```
In the next step we will create a database and configure it using ‘Database Control’. In order to do this we first need a listener configured prior to creating the database, since we have just installed the software we currently have no listener so we need to configure one. Oracle Net is the networking infrastructure that allows applications running on other servers to access the database. The Oracle Net listener is a process that runs on the database server and monitors a TCP port for requests to access the database. The Oracle Net listener is configured by creating a file called listener.ora in the $ORACLE_HOME/network/admin directory. In the $ORACLE_HOME/network/admin/samples directory you will find an example listener.ora file. Unfortunately, many Oracle security exploits involve the Oracle Net listener, and therefore it is important that you configure it properly and securely. A functional listener.ora file that uses operating system authentication for securing the Oracle Net listener is as follows:
```
      #
      # Filename: listener.ora
      #
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.99)(PORT = 1521))
            )
          )
        )
```
The permissions on the listener.ora file should be 640.
Start the Oracle Net listener with the following command:
```
      $ORACLE_HOME/bin/lsnrctl start
```

Depending upon your configuration of SELinux (Security Enhanced Linux) you may receive the error message

      cannot restore segment prot after reloc: Permission denied

      /usr/sbin/sestatus

The Oracle client libraries invoked by an application wishing to access the database read configuration files called sqlnet.ora and tnsnames.ora in order to figure out how to find the Oracle Net listener and what connection parameters should be used. In the same directory where the sample listener.ora file is located, you will also find a sample sqlnet.ora and tnsnames.ora. You should create a sqlnet.ora file and a tnsnames.ora file in the same directory where you created your listener.ora file. Set the file permissions to 644. Copy these two files to all application servers or other machines that will access the database. Functional sqlnet.ora and tnsnames.ora files are as follows:
```
      #
      # Filename: sqlnet.ora
      #
      NAMES.DEFAULT_DOMAIN = dbspecialists.com
      NAMES.DIRECTORY_PATH= (TNSNAMES)

      #
      # Filename: tnsnames.ora
      #
      DEV11EE.DBSPECIALISTS.COM =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.99)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SERVICE_NAME = dev11ee.dbspecialists.com)
          )
        )
```
Now we proceed with creating the database, launch the Database Configuration Assistant with the following commands:
```
      $ cd $ORACLE_HOME/bin
      $ ./dbca
```
We’ll walk through the prompts one at a time: 
1. The dbca Welcome window appears. Click Next
2. The Operations windows appears with the ‘Create a Database’ operation selected. Click Next.
3. The Database Templates window appears. Here you choose a template (a set of default specifications) for the database you wish to create. Oracle provides templates called “General Purpose or Transaction Processing”, “Custom Database” and “Data Warehouse” Oracle has pre-built data files available for these three templates, meaning that database creation will go faster than if Oracle has to build the database from scratch. You can also choose Custom and create your own template. We will choose General Purpose here. Click Next.
4. The Database Identification window appears. Here you specify the global name and the instance name (SID) for the database. It would be nice if these fields defaulted from the ORACLE_SID environment variable, but this may or may not happen. In the Global Database Name field, enter the database name you selected, followed by a period and your domain name. For example, “dev11ee.dbspecialists.com”. The SID field will fill in automatically from the global name. Click Next.
5. The Management Options window appears. Here you indicate whether or not you wish to have the Enterprise Manager tool configured. Grid Control is Oracle’s enterprise-wide database management tool. This option will be grayed out if Grid Control infrastructure has not already been established. Database Control is a stand-alone management tool specifically configured to manage one database. If Grid Control is not present, the defaults in this window will specify to configure Database Control for this database. This will enable you to perform many database management functions for this database from a web browser. You may optionally configure Database Control to send you alerts via email and to back up the database daily. It does not hurt to choose Database Control configuration at this time—you can always shut it down later. We will not be covering the database backup feature here. Click Next.
6. The Database Credentials window appears. Every Oracle 11g database has accounts called SYS, SYSTEM, DBSNMP, and SYSMAN. You must provide passwords for each of these accounts, although you can choose to give all four the same password. It is easy to change passwords later, and members of the dba Linux group can access the database without a password and change passwords for any database account. Enter the initial passwords for these accounts and click Next. Your password should have a minimum of 8 characters in length and contain at least one upper case character, one lower case character and one digit.
7. The Database File Locations window appears. The files that make up an Oracle database can be stored on a regular file system or disks managed automatically by Oracle’s Automatic Storage Management facility. We will not be covering ASM here, so keep File System selected. Also on this screen you specify where on the file system the files that make up the database should initially reside. It will be easy to change file locations later, and database files can be spread over multiple directories. The default option on this window is to use the file location specified in the template. This is not a good idea as the location specified by the templates goes against standard conventions. Instead you should select “Use Common Location for All Database Files” and enter a mount point name followed by the oradata subdirectory, such as “/u01/oradata”. The location you enter here should match one of the directories you created in step 12 of the first section above. Click Next.
8. The Recovery Configuration window appears. A solid backup and recovery plan is absolutely necessary for any database that will hold data of any importance. However, there are many options available and needs vary greatly from one situation to the next. The flash recovery area is used by the “Flashback database” feature and also by Enterprise Manager if you chose to configure automatic database backups. Archiving, meanwhile, is necessary for databases that will be backed up while they are open. Archiving can be enabled easily at a later time. Since we are not covering backup and recovery strategies here, we will uncheck both options and click Next.
9. The Database Content window appears, it appears two tabs. Typically there will be no need for you to specify any custom scripts, and preloading the sample schemas can be helpful in a development database for seeing examples of various techniques. Make your selections and click Next.
10. The Initialization Parameters window appears. Tabs in this window let you set various initialization parameters, and a button lets you view and edit all parameters in a tabular form. Click on the Character Sets tab and select the character set for the database that matches the character set name you put into the oraenv script in an earlier step. It is hard to change the character set of a database, so make sure you are happy with your selection before proceeding. Initialization parameters, on the other hand, are easily changed later. In this window, therefore, you should make sure the character set is correct but not worry too much about the other settings. (Setting the character sets is very different from setting initialization parameters, so the fact that the Character Sets tab appears on a window entitled Initialization Parameters may be confusing.) Click Next.
11. The Database Storage window appears. Here you can review and edit the details of how the control files, online redo logs, data files, and tablespaces will be created. If you are using one of the templates that was provided, you will not be able to change very many settings. If you want to change the locations of some of the database files, you can do that here or after the database has been created. The default redo log size (50 Mb) is somewhat small, so you might want to change it. Again, you can do that here or after the database has been created. When you are satisfied with the settings, click Next.
12. The Creation Options window appears. You may choose to create the database now, generate scripts to create the database later, and/or save the settings as a template. Saving as a template allows you to create the database at a later time or create many similar databases more easily. Make your selection and click Finish.
13. A Confirmation window appears. Review all of your selections and click OK.
14. A progress window appears and database creation proceeds.
15. When database creation is complete, a window will appear which indicates the name of the database, the location of the parameter file, and the URL for accessing Enterprise Manager. Note this URL for future reference. Depending on what options you selected, additional accounts may have been created on the database besides the basic SYS, SYSTEM, DBSNMP, and SYSMAN. All additional accounts are now locked. You may click the Password Management button to unlock these accounts and set passwords if you wish, but you should only unlock an account if you have a specific reason for doing so. When you are finished, click the Exit button to exit the Database Creation Assistant.
 
While logged onto the database server as the oracle user, run the following commands to set environment variables so that you will be able to access the database easily (substitute your Oracle instance name):
```
      $ export ORACLE_SID=dev11ee
      $ export ORAENV_ASK=NO
      $ . /usr/local/bin/oraenv
```
Now we have a database you can verify that Oracle Net is configured correctly by attempting to access the database from an application server or other remote server, or by using commands like the following on the database server directly:
```
      $ sqlplus /nolog
      SQL&gt; CONNECT system@dev11ee
      Enter password: &lt;Enter SYSTEM password&gt;
```

If you receive the error ORA-12170: TNS:Connect timeout occurred you should check your Linux firewall settings, if the firewall is active you will need to open up ports 1521 and 1158.

If you would like to move any of the data files or online redo logs for this database to another directory, use commands like the following:

      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; SHUTDOWN IMMEDIATE
      SQL&gt; STARTUP MOUNT
      SQL&gt; HOST mv -i /u01/oradata/dev11ee/users01.dbf /u02/oradata/dev11ee/users01.dbf
      SQL&gt; ALTER DATABASE RENAME FILE
        2  '/u01/oradata/dev11ee/users01.dbf' TO
        3  '/u02/oradata/dev11ee/users01.dbf';
      SQL&gt; HOST mv -i /u01/oradata/dev11ee/redo01.log /u02/oradata/dev11ee/redo01.log
      SQL&gt; ALTER DATABASE RENAME FILE
        2  '/u01/oradata/dev11ee/redo01.log' TO
        3  '/u02/oradata/dev11ee/redo01.log';
      SQL&gt; ALTER DATABASE OPEN;
      SQL&gt; EXIT

Note that this procedure does not work for control files. Relocating database control files will be covered in a later step.

In databases created with supplied templates, all data files have the “auto-extend” feature turned on. This means that when a data file becomes full, it will automatically grow larger as needed. The problem with this is that an application can get out of control and fill up an entire disk partition. It also means that you need to manage your free space at the operating system level. Many DBAs prefer to manage free space at the database level by pre-allocating space to data files and not using the auto-extend feature. You may resize data files and disable auto-extend with commands like:

      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/system01.dbf' AUTOEXTEND OFF;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/sysaux01.dbf' AUTOEXTEND OFF;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/undotbs01.dbf' AUTOEXTEND OFF;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/users01.dbf' AUTOEXTEND OFF;
      SQL&gt; ALTER DATABASE TEMPFILE '/u01/oradata/dev11ee/temp01.dbf' AUTOEXTEND OFF;

      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/system01.dbf' RESIZE 850m;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/sysaux01.dbf' RESIZE 700m;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/undotbs01.dbf' RESIZE 50m;
      SQL&gt; ALTER DATABASE DATAFILE '/u01/oradata/dev11ee/users01.dbf' RESIZE 20m;
      SQL&gt; ALTER DATABASE TEMPFILE '/u01/oradata/dev11ee/temp01.dbf' RESIZE 50m;

Oracle uses a server parameter file or “spfile” to store the initialization parameters—settings that affect the instance. The default parameter settings provided by the Database Configuration Assistant are not bad, but you may want to make some changes. Unfortunately, you cannot edit the spfile. Instead, you must export the contents of the spfile to a plain text file called a “pfile”. You can then edit the pfile and convert it back to an spfile for use on your database. (This might sound confusing, but is actually pretty straightforward.)Shut down the database and export the contents of the spfile into a pfile that you can edit with commands like:
```
      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; CREATE PFILE='/home/oracle/dev11ee-params.txt'
        2  FROM SPFILE;
      SQL&gt; SHUTDOWN IMMEDIATE
```

Make a backup copy of the pfile you created in the previous step and edit the pfile to change parameters as you wish, based on your needs and your server’s capabilities. You can always change parameters again in the future, so you are not locking yourself into anything right now. Here is the pfile that we ended up with:

	*.audit_file_dest='/u01/app/oracle/admin/dev11ee/adump'
	*.audit_trail='db'
	*.compatible='11.2.0.0.0'
	*.control_files='/u01/oradata/dev11ee/control01.ctl','/u01/oradata/dev11ee/control02.ctl'
	*.db_block_size=8192
	*.db_domain='dbspecialists.com'
	*.db_name='dev11ee'
	*.diagnostic_dest='/u01/app/oracle'
	*.memory_target=442499072
	*.open_cursors=300
	*.processes=150
	*.remote_login_passwordfile='EXCLUSIVE'
	*.undo_tablespace='UNDOTBS1

The database is created with two control files. The control file is a pretty small file that contains crucial configuration and synchronization information that Oracle needs in order to locate all the files that make up the database and keep them consistent. All copies of the control file are kept identical; whatever Oracle writes to one control file it also writes to the others. (Think of it like software mirroring.) It is a good idea to move one of the control files to another location. With the database shut down, you can go ahead and move the control files around as you wish. Be sure to change the control_files entry in your pfile accordingly.
Remove the existing spfile that the Database Configuration Assistant created, and the bogus pfile that it left behind, with the following commands:
```
      $ rm -i $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
      $ rm -i $ORACLE_HOME/dbs/init.ora
```

Create a symbolic link from the location where Oracle looks for the spfile to the location where you will actually maintain the spfile:

      $ ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora \
              $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

Now convert the pfile that you edited back into an spfile that Oracle can use with the following commands:

      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; CREATE SPFILE='$ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora'
        2  FROM PFILE='/home/oracle/dev11ee-params.txt';

You are now ready to restart your database using your newly created spfile. Use the following commands to start the database and view the parameters that are in effect. These settings should match what you put in your pfile a few steps back:

      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; STARTUP
      SQL&gt; SET PAGESIZE 100
      SQL&gt; SELECT   name, value, isdefault
        2  FROM     v$parameter
        3  ORDER BY isdefault, name;

You can follow the above few steps at any time to make further changes to the parameters. However, if you only have a few changes to make, there is a much easier way than exporting the spfile into a pfile, editing the pfile, and converting back to an spfile. You can simply:
```
      $ sqlplus /nolog
      SQL&gt; CONNECT / AS SYSDBA
      SQL&gt; ALTER SYSTEM SET parameter = value
        2  SCOPE = SPFILE;
```
This will update the setting in your spfile, and the change will take effect the next time you restart the instance. Many parameters are dynamic, meaning that you can change them on the fly without restarting the instance. For dynamic parameters, you can omit the SCOPE = line above and Oracle will change the parameter setting immediately and in the spfile. If you wish to unset a parameter use the following syntax
```
	SQL&gt; ALTER SYSTEM RESET  SCOPE=SPFILE SID='*'
```
At this point you are ready to create tablespaces—logical groupings of data files—to hold your application data. You can put all of your data into one tablespace, or you can separate data into multiple tablespaces based on object type, object size, permanence, volatility, I/O volume, or any of a number of other criteria. In the past, choosing storage parameters and allocation schemes for database objects was rather tedious. Now it is quite simple because you can have Oracle do the space allocation and management automatically and it will do a pretty good job. Here is a sample tablespace creation statement for an application called “Flex”:
```
      CREATE TABLESPACE flex_data
      DATAFILE '/u02/oradata/dev11ee/flex_data01.dbf' SIZE 500m
      SEGMENT SPACE MANAGEMENT AUTO;
```
Create application roles if desired. Alternatively, you can use the default roles CONNECT, RESOURCE, and DBA.
Create your application accounts that will own the application schemas. Set the default tablespace to one of your application tablespaces designated to hold tables. Assign quotas on all of the application tablespaces where the account will need to be able to create schema objects. (You can use the keyword UNLIMITED.) You should not set any quota on the SYSTEM, SYSAUX, or TEMP tablespaces. Do not plan to create any application objects in the SYS or SYSTEM schemas, or store any application objects in the SYSTEM, SYSAUX, or TEMP tablespaces. Here is a sample application account creation statement:
```
      CREATE USER bob IDENTIFIED BY bob123
      DEFAULT TABLESPACE flex_data
      QUOTA UNLIMITED ON flex_data;
```
Grant roles and/or system privileges to the application accounts. Note that if you grant the RESOURCE role to an account, that account will also receive the UNLIMITED TABLESPACE system privilege. This will let the account create objects in any tablespace, regardless of quotas. Think very carefully before granting the DBA role or allowing any accounts that have the UNLIMITED TABLESPACE privilege. Sample statements to grant and revoke privileges are as follows:
```
      GRANT connect, resource TO bob;
      REVOKE unlimited tablespace FROM bob;
```
Review the overall security of your database. Oracle Corporation has published a very detailed 25 page listing of recommended security checks. Download it from Oracle Technology Network at http://www.oracle.com/technology/deploy/security/database-security/pdf/twp_security_checklist_database.pdf.

Complete the Server Configuration

These steps complete the configuration of your server for smooth Oracle operation. In this section we will change the oracle user’s login script to eliminate hardcoding, create individual operating system accounts for each database user, and configure the server to start the database and listeners automatically whenever the server is rebooted.

Edit the login file (.bash_profile or .profile) for the oracle user to eliminate hardcodings and call the oraenv script to set the environment instead. The following will work with Bash, Bourne, or Korn shell:
```
      # Settings for Oracle environment
      ORACLE_SID=dev11ee   # Put your instance name here
      ORAENV_ASK=NO
      export ORACLE_SID ORAENV_ASK
      . oraenv
      ORAENV_ASK=YES
```
Note that this script assumes that the /usr/local/bin directory is on your path. Also, if you use C shell then you should edit .cshrc and have it source coraenv.
Create separate Linux accounts for DBAs and database users who will log onto the database server directly. You should only log in as oracle when installing or patching software or stopping/starting the database. The Linux accounts for DBAs should be members of the dba group, and other users should not be members of the dba group. Give each of these accounts a login file like oracle’s so that their environment initializes correctly when they log in.
Edit the /etc/oratab file to verify that the entry for your database is correct. Lines starting with a pound sign are considered comments and are ignored. Each non-comment line contains the name of one Oracle instance, its Oracle home, and a Y or N. A Y indicates that the database should be started automatically on server reboot, and an N indicates that it should not. The three fields should be separated by colons. A sample /etc/oratab file looks like this:
```
      #
      # /etc/oratab
      # ===========
      #
      dev11ee:/u01/app/oracle/product/11.2.0/dbhome_1:Y
```

To make the database and listeners start up automatically when the server reboots and shut down automatically when the server shuts down, you’ll need to create a dbora file in /etc/init.d and either user the chkconfig command or manually link dbora to /etc/rc3.d and /etc/rc0.d. You’ll need to do this as the root user. First create a file called dbora in /etc/init.d as follows:

      #
      # chkconfig: 35 99 10
      # description: Start and stop the Oracle database, listener and DB Control
      #
      ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
      ORA_OWNER=oracle
      if [ ! -f $ORA_HOME/bin/dbstart ]
      then
        echo "Oracle startup: cannot start"
        exit
      fi
      case "$1" in
         'start') # Start the Oracle databases and listeners
                  su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
                  su - $ORA_OWNER -c "ORACLE_SID=dev11ee; export ORACLE_SID; $ORA_HOME/bin/emctl start dbconsole; unset ORACLE_SID"
                  touch /var/lock/subsys/dbora
                  ;;
         'stop')  # Stop the Oracle databases and listeners
                  su - $ORA_OWNER -c "ORACLE_SID=dev11ee; export ORACLE_SID; $ORA_HOME/bin/emctl stop dbconsole; unset ORACLE_SID"
                  su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
                  rm -f /var/lock/subsys/dbora
                  ;;
      esac

Set the permissions on the dbora file to 700:

      $ chmod 700 /etc/init.d/dbora

After creating the dbora file, you need to link it to /etc/rc3.d and /etc/rc0.d for startup and shutdown. You may do this manually as follows:

      $ ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
      $ ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
      $ ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
      $ ln -s /etc/init.d/dbora /etc/rc1.d/K10dbora
      $ ln -s /etc/init.d/dbora /etc/rc2.d/K10dbora
      $ ln -s /etc/init.d/dbora /etc/rc4.d/K10dbora
      $ ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

Alternatively, you may use the chkconfig command to create the links as follows:

      $ cd /etc/init.d
      $ /sbin/chkconfig --add dbora

Conclusion

This paper walks you through the intricate details of getting Oracle Database 11g release 2 up and running on a database server running Linux. It may look complicated, but that’s only because this paper goes down to a nitty-gritty level of detail.

Please keep in mind, though, that the requirements are different for every Oracle implementation. We are extremely confident that if you follow these steps to install Oracle Database 11g release 2 (Oracle version 11.2.0) on a server with Red Hat Enterprise Linux and an x86_64 hardware platform, the process will go very smoothly for you. Things should go nearly as smoothly if you use another Linux distribution certified by Oracle or another hardware platform such as x86. However, no single document can address every specific hardware configuration and every set of business needs. Please use this paper as a starting point to get Oracle up and running in your shop. To get the best performance and scalability, each system needs to be considered individually.

Go to Detail Page

Oracle Database 11g Release 1 (11.2.0.1) New Features

November 2, 2011 Articles 0 0

This chapter contains descriptions of all of the features that are new to Oracle 11g Database, Release 2. This chapter contains the following sections:

Application Development
Availability
Business Intelligence and Data Warehousing
Clustering
Database Overall
Diagnosability
Performance
Security
Server Manageability
Unstructured Data Management

1.1 Application Development

The following sections describe the new application development features for Oracle Database 11g Release 2 (11.2).

1.1.1 Oracle Application Express

The following sections describe Oracle Application Express features.

1.1.1.1 Application Date Format

You can now define a date format to be used throughout an application. This date format is used to alter the NLS_DATE_FORMAT database session setting prior to showing or submitting any page within the application. This format is used by all reports showing dates and is also picked up by form items of type “Date Picker (use Application Date Format)”.

The ability to specify a date format at the application level ensures consistency across the application. Therefore, whenever dates are displayed or input, they are in the same format.

1.1.1.2 Custom Themes

In addition to the default themes provided with Oracle Application Express, you can create your own customized themes. You can either start with one of the twenty standard themes available with Oracle Application Express and modify the underlying templates or define your own templates from scratch. Each theme consists of a set of templates defined with cascading style sheets (CSS) and HTML.

The ability to publish custom themes enables you to design a specific look and feel to meet your corporate requirements and then publish them as a theme for all other applications to use.

1.1.1.3 Declarative BLOB Support

Declarative BLOB support enables files to be declaratively uploaded in forms, and downloaded or displayed using reports. BLOB display and download can also be authored procedurally using PL/SQL.

The storing of binary large objects (BLOBs) within the database is growing in popularity due to the many advantages over storing content on disparate file systems. By incorporating declarative support for managing BLOBs into Application Express, the loading and manipulating of content is greatly simplified.

1.1.1.4 Documented JavaScript Libraries

This release includes an improved framework for advanced Oracle Application Express developers to build and leverage custom Web 2.0 capabilities, improving performance and enabling developers to create more dynamic application widgets. Oracle Application Express also includes the ability to suppress standard JavaScript and CSS files. All included JavaScript files are now compressed to improve page load time.

Many developers want to extend their applications to include additional Web 2.0 capabilities or to minimize the page weight for use on mobile devices such as iphones and smartphones. The documentation and declarative capabilities allow developers to design applications for these disparate requirements.

1.1.1.5 Enhanced Report Printing

Release 3.1 includes XML as a download format and supports multiple SQL statements.

Oracle Application Express interactive reporting provides the ability to manipulate the way in which the data is displayed on the screen. Users can also download this data in various formats including PDF, RTF, XLS and now XML.

1.1.1.6 Forms Conversion

Forms Conversion captures the design of existing Oracle Forms and automatically converts some components, primarily the user interface. Other components, such as complex triggers, need to be manually converted post-generation.

Moving to native HTML is not seamless and changes to the user interface are required to deliver optimal Web interactivity.

The Oracle Application Express Forms Conversion enables you to take advantage of Oracle Application Express dynamic HTML capabilities, including interactive reports. Given the similarities between Oracle Forms and Oracle Application Express development (both use SQL and PL/SQL), retraining requirements are also low.

1.1.1.7 Improved Security

Oracle Application Express offers a number of security enhancements. Key enhancements include the ability to declaratively encrypt session state and specify session time outs for maximum idle time and maximum session duration as well as create new password item types that enable users to enter passwords without ever saving them to session state.

Other features include reducing the privileges required by the Oracle Application Express database account, disabling database monitoring by default, and the ability to specify HTTPS for administration. In addition, administrators can now restrict password reuse. This release also includes a new Hidden and Protected item type. This item type greatly simplifies the developer’s task of protecting item session state. This, together with other minor improvements, makes the default security functionality more robust within Oracle Application Express.

The additional declarative security capabilities make it easier for developers and administrators to harden the security of their applications and the development environment. These new capabilities complement existing Oracle Application Express security features some of which include flexible authentication, authorization schemes, and URL tampering protection.

1.1.1.8 Interactive Reporting Region

Interactive Reporting Regions enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, and their own computations. Users can create multiple variations of the report and save them as named reports and download to various file formats including comma-delimited file (CSV) format, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF).

Oracle Application Express Interactive Reporting enables developers to quickly develop reports that can be manipulated by end users to meet a wide range of reporting requirements. Therefore, instead of developers having to define specific report layouts for different users or groups, they can define a common report that can be used to meet the majority of the different requirements.

1.1.1.9 Runtime-Only Installation

For testing and production instances, Oracle Application Express now supports the ability to install a runtime version of Oracle Application Express. This minimizes the installed footprint and privileges. Scripts are also provided to remove or add the developer interface from an existing instance.

The ability to implement a runtime-only environment improves application security as developers cannot inadvertently or maliciously update a production application.

1.1.2 Other General Development Features

The following sections describe new features in the areas of OCI, Pro*C, JDBC, and other development APIs.

1.1.2.1 Support WITH HOLD Option for CURSOR DECLARATION in Pro*C

The WITH HOLD option can now be specified during cursor declaration.

This new option provides easy migration of Pro*C applications.

1.1.2.2 Pro*C Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH

Oracle Call Interface (OCI) now provides Pro*C support for 8-byte native numeric host variable for INSERT and FETCH on 32-bit and 64-bit platforms.

Fusion applications need Pro*C to be able to support 8-byte native data type for bind/define while inserting or fetching data to and from a NUMBER(18) column.

1.1.2.3 Pro*COBOL Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH

Oracle Call Interface (OCI) now provides Pro*COBOL support for 8-byte native numeric host variable for INSERT and FETCH on 32-bit and 64-bit platforms.

Fusion applications need Pro*COBOL to be able to support 8-byte native data type for bind/define while inserting or fetching data to and from a NUMBER(18) column.

1.1.2.4 JDBC Support for Time Zone Patching

The JDBC driver is updated to conform with the new time zone upgrading scheme.

This feature provides a simplified time zone patching process. As a result, Java applications using the TIMESTAMP WITH TIME ZONE data type are immune to Daylight Saving Time (DST) changes.

1.1.2.5 JDBC Support for SecureFile Zero-Copy LOB I/O and LOB Prefetching

JDBC now supports SecureFile zero-copy LOB I/O and LOB prefetching.

This feature allows performant and secure Java access to structured (relational) and unstructured data.

1.1.2.6 OCI Support for 8-Byte Integer Bind/Define

Oracle Call Interface (OCI) now provides support for 8-byte integer bind/define on 32-bit and 64-bit platforms.

Fusion applications need Pro*C or Pro*COBOL to be able to support 8-byte native data type for bind/define while inserting or fetching data to and from a NUMBER(18) column. Pro*C or Pro*COBOL need this support from OCI to be able to pass it on to application developers.

1.2 Availability

The focus of this Availability section is aimed towards providing capabilities that keep the Oracle database available for continuous data access, despite unplanned failures and scheduled maintenance activities. These various capabilities form the basis of Oracle Maximum Availability Architecture (MAA), which is the Oracle blueprint for implementing a highly available infrastructure using integrated Oracle technologies.

1.2.1 Backup and Recovery

The following sections describe new features in this release that provide improvements in the area of backup and recovery.

1.2.1.1 Automatic Block Repair

Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination. In addition, RECOVER BLOCK is enhanced to restore blocks from a physical standby database. The physical standby database must be in real-time query mode.

This feature reduces time when production data cannot be accessed, due to block corruption, by automatically repairing the corruptions as soon as they are detected in real-time using good blocks from a physical standby database. This reduces block recovery time by using up-to-date good blocks from a real-time, synchronized physical standby database as opposed to disk or tape backups or flashback logs.

1.2.1.2 Backup to Amazon Simple Storage Service (S3) Using OSB Cloud Computing

Oracle now offers backup to Amazon S3, an internet-based storage service, with the Oracle Secure Backup (OSB) Cloud Module. This is part of the Oracle Cloud Computing offering.

This feature provides easy-to-manage, low cost database backup to Web services storage, reducing or eliminating the cost and time to manage an in-house backup infrastructure.

1.2.1.3 DUPLICATE Without Connection to Target Database

DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.

The benefit is improved availability of a DUPLICATE operation by not requiring connection to a target database. This is particularly useful for DUPLICATE to a destination database where connection to the target database may not be available at all times.

1.2.1.4 Enhanced Tablespace Point-In-Time Recovery (TSPITR)

Tablespace point-in-time recovery (TSPITR) is enhanced as follows:

You now have the ability to recover a dropped tablespace.
TSPITR can be repeated multiple times for the same tablespace. Previously, once a tablespace had been recovered to an earlier point-in-time, it could not be recovered to another earlier point-in-time.
DBMS_TTS.TRANSPORT_SET_CHECK is automatically run to ensure that TSPITR is successful.
AUXNAME is no longer used for recovery set data files.

This feature improves usability with TSPITR.

1.2.1.5 New DUPLICATE Options

The following are new options for the DUPLICATE command:

NOREDO
NOREDO indicates that archive logs are not applied. Because targetless DUPLICATE does not connect to the target database, it cannot check if the database is running in NOARCHIVELOG mode. It can also be used during regular duplication to force a database currently in ARCHIVELOG mode to be recovered without applying archive logs (for example, because it was in NOARCHIVELOG mode at the point-in-time it is being duplicated).
UNDO TABLESPACE <tsname> [ , <tsname> ... ]
When not connected to a recovery catalog and not connected to an open target database, RMAN cannot obtain the list of tablespaces with undo segments, therefore, you must specify them with this clause.

This feature improves the usability of the DUPLICATE command.

1.2.1.6 New SET NEWNAME Clauses and Format Options

The following are new clauses and format options for the SET NEWNAME command:

A single SET NEWNAME command can be applied to all files in a tablespace, or for all files in the database. For example:

SET NEWNAME FOR TABLESPACE &lt;tsname&gt; TO &lt;format&gt;;

Or,

SET NEWNAME FOR DATABASE TO &lt;format&gt;;

New format identifiers for SET NEWNAME...<format> are as follows:
- %U
  Unique identifier. data_D-%d_I-%I_TS-%N_FNO-%f
- %b
  UNIX base name of the original data file name. For example, if the original data file name was ORACLE_HOME/data/tbs_01.f, then %b is tbs_01.f.

The benefit is improved flexibility of RESTORE, DUPLICATE, and TSPITR.

1.2.1.7 Tablespace Checks in DUPLICATE

The DUPLICATE...TABLESPACE and DUPLICATE... SKIP TABLESPACE commands now perform the following initial checks:

Excluded tablespaces are checked to see if they contain any objects owned by SYS.
DBMS_TTS.TRANSPORT_SET_CHECK is run to ensure that the set of tablespaces being duplicated are self-contained before the actual duplicate process.

These checks are not possible for a targetless DUPLICATE as they are required to be run at the target database.

This feature improves usability of DUPLICATE. Any tablespace issues are immediately identified prior to commencement of the actual duplicate operation.

1.2.2 Online Application Maintenance and Upgrade

The following sections describe online application maintenance and upgrade features.

1.2.2.1 Edition-based Redefinition

Edition-based redefinition allows an application’s database objects to be changed without interrupting the application’s availability by making the changes in the privacy of a new edition. Every database has at least one edition. The DBA creates a new edition as a child of the existing one. The changes are made in the child edition while you continue to use the parent edition. When needed, changes to data are made safely by writing only to new columns or new tables not seen by the old edition. Editioning views expose a different projection of each changed table into each edition to allow each to see just its own columns. Crossedition triggers propagate data changes made by the old edition into the columns of the new edition. When the installation of the changes is complete, some users start to use the new edition while others drain off the old edition. Here, crossedition triggers propagate data changes made by the new edition into the columns of the old edition.

Large, mission critical applications are often unavailable for long periods of time while database objects are patched or upgraded. Edition-based redefinition allows this cost to be avoided.

1.2.2.2 Enhance CREATE or REPLACE TYPE to Allow FORCE

The FORCE option can now be used in conjunction with the CREATE or REPLACE TYPE command.

This feature provides enhanced usability and allows a CREATE or REPLACE TYPE operation to be performed even when TYPE dependent objects are present. However, if at least one TABLE dependent is present, then FORCE does not allow CREATE or REPLACE TYPE to succeed.

1.2.2.3 Fine-Grained Dependencies for Triggers

Oracle Database 11g Release 1 (11.1) brought both fine-grained dependency tracking and the new possibility that a trigger might be a dependency parent by virtue of the new FOLLOWS keyword.

In release 11.1, dependents on triggers did not have fine-grained dependency. In release 11.2, this fine-grained dependence exists. (Release 11.2 also provides the new PRECEDES keyword which also allows trigger-upon-trigger dependencies.)

1.2.2.4 IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows.

This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

1.2.3 Oracle Data Guard

The following sections describe new features in this release that provide improvements in Oracle Data Guard.

1.2.3.1 Compressed Table Support in Logical Standby Databases and Oracle LogMiner

Compressed tables (that is, tables with compression that support both OLTP and direct load operations) are supported in logical standby databases and Oracle LogMiner.

With support for this additional storage attribute, logical standby databases can now provide data protection and reporting benefits for a wider range of tables.

1.2.3.2 Configurable Real-Time Query Apply Lag Limit

A physical standby database can be open for read-only access while redo apply is active only if the Oracle Active Data Guard option is enabled. This capability is known as real-time query.

The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode.

This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.

1.2.3.3 Integrated Support for Application Failover in a Data Guard Configuration

Applications connected to a primary database can transparently failover to the new primary database upon an Oracle Data Guard role transition. Integration with Fast Application Notification (FAN) provides fast failover for integrated clients.

Flexibility and manageability of disaster recovery configurations using Oracle Data Guard is improved.

1.2.3.4 Support Up to 30 Standby Databases

The number of standby databases that a primary database can support is increased from 9 to 30 in this release.

The capability to create 30 standby databases, combined with the functionality of the Oracle Active Data Guard option, allows the creation of reader farms that can be used to offload large scale read-only workloads from a production database.

1.3 Business Intelligence and Data Warehousing

The following sections describe new Business Intelligence and Data Warehousing features for Oracle Database 11g Release 2 (11.2).

1.3.1 Improved Analytics

The following sections describe new and improved analytical capabilities in this release.

1.3.1.1 Analytic Functions 2.0

New and enhanced analytical functions are introduced in this release. A new ordered aggregate, LISTAGG, concatenates the values of the measure column. The new analytic window function NTH_VALUE (a generalization of existing FIRST_VALUE and LAST_VALUE functions) gives users the functionality of retrieving an arbitrary (or nth) record in a window.

The LAG and LEAD functions are enhanced with the IGNORE NULLS option.

The new and enhanced SQL analytical functions allow more complex analysis in the database, using (simpler) SQL specification and providing better performance.

1.3.1.2 Recursive WITH Clause

The SQL WITH clause has been extended to enable formulation of recursive queries.

Recursive WITH clause complies with the American National Standards Institute (ANSI) standard. This makes Oracle ANSI-compatible for recursive queries.

1.3.2 Improved Data Loading

The following sections describe new and improved data loading capabilities in this release.

1.3.2.1 EXECUTE Privilege for DIRECTORY Objects

EXECUTE privilege is allowed for DIRECTORY objects in this release. The ORACLE_LOADER access driver creates a process that runs a user-specified program. That program must live in a directory path specified by a directory object defined in the database. Only a user that has been given EXECUTE access to the directory object is allowed to run programs in it.

This feature allows the DBA to control who is allowed to run preprocessors as part of loading data with external tables. It also allows the DBA to restrict which programs those users can run. No existing users with access to the directory object are allowed to run any programs from that directory unless the DBA gives them EXECUTE access to that directory.

1.3.2.2 Preprocessing Data for ORACLE_LOADER Access Driver in External Tables

The syntax for the ORACLE_LOADER access driver is extended in this release to allow specification of a program to preprocess the data files that are read for the external table. The access parameters can specify the name of a directory object and the name of an executable file in that directory object. When the access driver needs to read data from a file, it creates a process that runs the specified program, passing in the name of the data file. The output from the program is passed into the access driver which parses the data into records and columns.

The initial use of this feature is by a customer who needs to load data that is stored in compressed files. The user specifies the name of the program used to decompress the file as part of the access parameters. The access driver reads the output of the decompression program.

Large customers want to load data from compressed files which requires less disk space and uses the I/O bandwidth between the disk and memory more efficiently.

1.3.3 Improved Partitioning

The following sections describe new and improved partitioning capabilities in this release.

1.3.3.1 Allow Virtual Columns in the Primary Key or Foreign Key for Reference Partitioning

Virtual columns can be used as the primary or the foreign key column of a reference partition table.

Allowing the use of virtual columns for reference partitioned tables enables an easier implementation of various business scenarios using Oracle Partitioning.

1.3.3.2 System-Managed Indexes for List Partitioning

System-managed domain indexes are now supported for list partitioned tables.

This feature provides enhanced completeness of domain-specific indexing support for partitioning to meet user requirements including Oracle XML DB. Performance of local domain indexes on list partitioned tables is improved in this release.

1.3.4 Improved Performance and Scalability

The following sections describe new and improved performance and scalability capabilities in this release.

1.3.4.1 In-Memory Parallel Execution

Traditionally, parallel execution has enabled organizations to manage and access large amounts of data by taking full advantage of the I/O capacity of the system. In-memory parallel execution harnesses the aggregated memory in a system to enhance query performance by minimizing or even completely eliminating the physical I/O needed for a parallel operation. Oracle automatically decides if an object being accessed using parallel execution benefits from being cached in the SGA (buffer cache). The decision to cache an object is based on a well defined set of heuristics including size of the object and the frequency that it is accessed. In an Oracle RAC environment, Oracle maps fragments of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle knows which buffer cache to access to find a specific part or partition of an object to answer a given SQL query.

In-memory parallel query harnesses the aggregated memory in a system for parallel operations, enabling it to scale out with the available memory for data caching as the number of nodes in a cluster increases. This new functionality optimizes large parallel operations by minimizing or even completely eliminating the physical I/O needed because the parallel operation can now be satisfied in memory.

1.3.4.2 Minimal Effort Parallel Execution – Auto Degree of Parallelism (DOP) and Queuing

When activated, Oracle determines the optimal degree of parallelism (DOP) for any given SQL operation based on the size of the objects, the complexity of a statement, and the existing hardware resources.

The database compensates for wrong or missing user settings for parallel execution, ensuring a more optimal resource consumption and overall system behavior.

1.3.4.3 The DBMS_PARALLEL_EXECUTE Package

The DBMS_PARALLEL_EXECUTE package provides subprograms to allow a specified INSERT, UPDATE, DELETE, MERGE, or anonymous block statement to be applied in parallel chunks. The statement must have two placeholders that define the start and end limit of a chunk. Typically, these are values for the rowid or a surrogate unique key in a large table. But, when an anonymous block is used, the block can interpret the values arbitrarily. The package has subprograms to define ranges that cover the specified table. These include rule-based division of a table’s rowid or key range and support user-defined methods. The SQL statement together with the set of chunk ranges define a task. Another subprogram starts the task. Each task is processed using a scheduler job and automatically commits when it completes. Progress is logged. Untried, successful, and failed chunks are flagged as such on task completion or interruption. Another subprogram allows the task to resume to try untried and failed chunks.

Many scenarios require the bulk transformation of a large number of rows. Using an ordinary SQL statement suffers from the all-or-nothing effect. In the common case, where the transformation of one row is independent of that of other rows, it is correct to commit every row that is transformed successfully and to roll back every row where the transformation fails. Some customers have implemented schemes to achieve this from scratch, using the Oracle Scheduler and suitable methods to record progress. This package provides a supported solution and adds database-wide manageability through new catalog views for parallel task metadata. The package is especially useful in online application upgrade scenarios to apply a crossedition trigger to all the rows in the table on which it is defined.

1.3.4.4 Significant Performance Improvement of On-Commit Fast Refresh

Fast refresh of a materialized view is now significantly faster due to reducing the time spent on log handling.

This provides significantly reduced maintenance time and more fast refreshes are possible.

1.3.5 Oracle Warehouse Builder

The following sections describe improvements to the extraction, transformation, and loading (ETL) capabilities available with Oracle Warehouse Builder (OWB).

1.3.5.1 Advanced Find Support in Mapping Editor

The mapping editor has been enhanced with advanced find capabilities to make it easier to locate and make updates to operators, groups, and attributes in a mapping diagram, in the Available Objects tab, and in the Selected Objects tab.

This feature enhances ETL mapping developer productivity, especially on large and complex mappings and, for example, when working with complex data sources with large numbers of tables, views, or columns.

1.3.5.2 Business Intelligence Tool Integration

Oracle Warehouse Builder (OWB) now offers metadata integration with Oracle Business Intelligence Standard Edition (Discoverer) as well as Oracle Business Intelligence Enterprise Edition.

For Oracle Business Intelligence Enterprise Edition (OBI EE), this feature allows derivation of ready-to-use physical, business model and presentation layer metadata from a data warehouse design, visualization and maintenance of the derived objects from within OWB, and deployment of the derived objects in the form of an RPD file that can be loaded into OBI EE.

Oracle Discoverer integration was added in a previous release, and includes derivation of metadata for Discoverer from the data warehouse design, and deploying those derived objects into Discoverer. In this release, similar capabilities are now available for OBI Enterprise Edition. All business intelligence application objects are modeled in OWB and can be included in lineage and impact analysis at the column level.

Customers using Oracle business intelligence tools with their Oracle data warehouses can get better answers from their warehouses faster, with no additional design or development effort.

1.3.5.3 Copy and Paste of Operators and Attributes in Mapping Editor

In the mapping editor, users can now copy and paste operators within a mapping or across mappings, including attribute settings.

This enhancement saves time and reduces errors in the development of complex ETL mappings that reuse common or similar elements.

1.3.5.4 Current Configuration Dropdown List in Design Center Toolbar

In the Design Center, there is now a dropdown list that displays the active configuration of the user.

This feature improves usability of the multi-configuration feature.

1.3.5.5 Enhanced Support for Flat File Imports

There are numerous support improvements for importing flat files, including a simplified Flat File Sampling wizard, support for multi-character and hexadecimal format delimiters and enclosures, simplified support for fixed format fields, and support for bulk flat file loads into heterogeneous targets.

Flat files are frequently used for simple and high performance data movement in ETL applications. These changes improve ETL developer productivity and provide flexible handling of flat files in more scenarios.

1.3.5.6 Enhanced Table Function Support

OWB now has improved support for table functions, including importing metadata for existing table functions, an editor for creating table functions from within OWB, and better support for table functions in mappings.

Improved support simplifies using table functions for much more flexible and powerful transformations, such as user-defined aggregations and data mining sampling operators.

1.3.5.7 Experts Available in Editor Menu

It is now possible to add OWB experts to the mapping editor menu.

This feature makes it possible to enhance and extend the functionality of the mapping editor, improving developer productivity.

1.3.5.8 Expression Editing in Operator Edit Dialog

Expressions associated with operator attributes can now be entered directly into an Operator Edit Dialog or Expression Editor, rather than requiring that these expressions be entered into a property in the Property Inspector.

Developers can finish more of their work in one place when creating operators in ETL mappings, thus improving their productivity.

1.3.5.9 Grouping and Spotlighting of Objects in Mapping Editor

In the mapping editor, users can now temporarily or permanently group objects in the mapping editor so that they are collapsed to a single icon. This hides complexity in mappings. Users can also spotlight a single operator, which temporarily hides all objects in the mapping except for those objects that connect directly to the operator.

These features improve productivity for developers working with complex mappings with large numbers of operators.

1.3.5.10 Improved Management of Locations Registered in Multiple Control Centers

The user interface for managing the registration of locations in control centers has been reworked to improve usability, especially when working with locations registered in multiple control centers.

This change improves productivity of OWB administrators responsible for managing locations across control centers.

1.3.5.11 Improved User Interface for Managing Locations

The user interface for managing OWB locations has been reworked to improve usability and support access to non-Oracle data sources using newly supported connectivity methods.

These changes improve Oracle Warehouse Builder administrator and developer productivity in heterogeneous and Oracle-only environments.

1.3.5.12 Key Lookup Operator Enhancements

Extensive changes have been made to the key lookup operator:

More efficient use of screen real estate.
Support for non-equality lookups.
Dynamic lookups, where the lookup table may be modified during the mapping execution.

These changes make the lookup operator more powerful in many situations, including improving Type 2 slowly changing dimension support.

1.3.5.13 Mapping Debugger Enhancements

There are numerous enhancements to the OWB mapping editor, including:

Improved support for watch points and enabling and disabling individual break points.
Support for user-defined type columns.
Enhanced support for numerous existing operators, such as VARRAY, EXPAND, and CONSTRUCT.
Support for key lookup and table function operators.
Support for correlated joins.
Improved cleanup of debugger-specific objects.

These enhancements improve productivity for ETL mapping developers, especially when working with complex mappings where the mapping debugger adds the most value.

1.3.5.14 New JDeveloper-Style User Interface

The Oracle Warehouse Builder Design Center user interface has been updated to use the Fusion Client Platform, the same core Integrated Development Environment (IDE) platform as Oracle JDeveloper and Oracle SQL Developer.

The advantages of this user interface include:

More efficient and flexible use of screen real estate.
Support for opening multiple editors of the same type, for example, editing several ETL mappings at once in different windows.
More consistent behavior across different parts of the OWB user interface.

This change brings Oracle Warehouse Builder Design Center in line with other development tools from Oracle. Developers experience increased productivity in the Oracle Warehouse Builder environment, which now benefits from the usability research behind the Fusion Client Platform and consistency with other Oracle products.

1.3.5.15 Operator References Included in Generated PL/SQL Code

PL/SQL code generated for OWB ETL mappings now includes detailed comments to help developers associate specific operators in a mapping with sections of the generated code.

Developers can more easily troubleshoot issues with OWB-generated code that can only be detected when the code is deployed. This additional information enhances developer productivity.

1.3.5.16 Quick Mapper

In this release, Oracle Warehouse Builder (OWB) introduces a new spreadsheet-like dialog for connecting operators in a mapping. This functionality replaces the existing auto mapping dialog.

This improvement saves developer time and reduces errors when working with operators with a large number of inputs or outputs.

1.3.5.17 Repository Browser Changes

The Repository Browser has been updated to support foldering, expose the new types of metadata associated with the release 11.2 feature set, and support OC4J 10.3.3.

These changes improve Oracle Warehouse Builder manageability.

1.3.5.18 Simplified Oracle Warehouse Builder Repository Upgrades

The repository upgrade automatically upgrades an Oracle Warehouse Builder (OWB) repository to the current release with less user intervention.

This feature simplifies the task of upgrading from one release to the next.

1.3.5.19 Support for Extracting Data From Tables Containing LONG Data Type

Oracle Warehouse Builder can now generate SQL*Plus code to extract data from database schemas supporting the deprecated LONG data type, such as occurs in PeopleSoft application data sources.

Support for LONG data types used in PeopleSoft data enables OWB users to integrate more effectively with PeopleSoft data or any other data source that uses the LONG data type.

1.3.5.20 Support for Subqueries in Join Operator

The join operator in Oracle Warehouse Builder (OWB) now supports several new behaviors related to the use of subqueries in joins:

Specifying subqueries using EXISTS, NOT EXISTS, IN, and NOT IN.
Specifying outer joins using the input role instead of the + (plus) sign.
Generating ANSI SQL syntax for all join types instead of only outer joins.

More flexible handling for join operations improves developer productivity and makes possible more flexible data transformations.

1.4 Clustering

The following sections describe new clustering features for Oracle Database 11g Release 2 (11.2).

1.4.1 Oracle Real Application Clusters Ease-of-Use

This release of Oracle Real Application Clusters (Oracle RAC) provides many features to dramatically simplify installation and on-going management of a cluster and Oracle RAC database, making it easy for the novice to adopt clustering and Oracle RAC and reap the benefits of this technology.

The following sections describe ease-of-use features for Oracle RAC.

1.4.1.1 Configuration Assistants Support New Oracle RAC Features

Database Configuration Assistant (DBCA), Database Upgrade Assistant (DBUA), and Net Configuration Assistant (NETCA) have been updated to support all of the new features of this release and provide a best practice implementation.

Configuration Assistants automate the configuration of the environment ensuring the correct steps are taken. The assistants simplify the implementation of clusters and clustered databases.

1.4.1.2 Enhanced Cluster Verification Utility

Additional functionality has been added to the Cluster Verification Utility (CVU) in regard to checking certain storage types and configurations. Furthermore, it gives more consideration to user-specific settings.

These enhancements provide easier implementation and configuration of cluster environments and improved problem diagnostics in a cluster environment.

1.4.1.3 Integration of Cluster Verification Utility and Oracle Universal Installer

The Cluster Verification Utility (CVU) is now fully integrated with the installer so that checks are done automatically for all nodes included in the installation.

This integration improves Oracle RAC manageability and deployment by ensuring that any problems with cluster setup are detected and corrected prior to installing Oracle software.

1.4.1.4 Cluster Time Service

The Cluster Time Service synchronizes the system time on all nodes in the cluster. A synchronized system time across the cluster is a prerequisite to install and successfully run an Oracle cluster.

This feature simplifies management, maintenance, and support of an Oracle cluster and an Oracle RAC environment by providing an out-of-the-box time server. It also improves the reliability of Oracle RAC environments.

1.4.1.5 Oracle Cluster Registry (OCR) Enhancements

There have been improvements in this release in the way the Oracle Cluster Registry (OCR) is accessed. These improvements include:

Faster relocation of services on node failure.
Support for up to 5 copies of the OCR for improved availability of the cluster.
Storage of OCR in Automatic Storage Management (ASM).

The tools to manage the OCR have changed to support the new management options.

These enhancements improve performance in Oracle Clusterware and Oracle Real Application Clusters environments and provide easier management of the cluster through consistent storage management automation

1.4.1.6 Grid Plug and Play (GPnP)

Grid Plug and Play (GPnP) eliminates per-node configuration data and the need for explicit add and delete nodes steps. This allows a system administrator to take a template system image and run it on a new node with no further configuration. This removes many manual operations, reduces the opportunity for errors, and encourages configurations that can be changed easily. Removal of the per-node configuration makes the nodes easier to replace, because they do not need to contain individually-managed state.

Grid Plug and Play reduces the cost of installing, configuring, and managing database nodes by making their per-node state disposable. It allows nodes to be easily replaced with regenerated state.

1.4.1.7 Oracle Restart

Oracle Restart improves the availability of your single-instance Oracle database. Oracle Restart automatically restarts the database instance, the Automatic Storage Management (ASM) instance, the listener, and other components after a hardware or software failure or whenever your database host computer restarts. Server Control (SRVCTL) is the command line interface to manage Oracle processes that are managed by Oracle Restart on a standalone server.

This feature provides improved reliability and automated management of a single-instance Oracle database and the management of any process or application running on the database server.

1.4.1.8 Policy-Based Cluster and Capacity Management

Oracle Clusterware allocates and reassigns capacity based on policies defined by you. This enables faster resource failover and dynamic capacity assignment using a policy-based management.

Policy-Based Cluster and Capacity Management allows the efficient allocation of all kinds of applications in the cluster. Various applications can be hosted on a shared infrastructure being isolated regarding their resource consumption by policies and, therefore, behave as if they were deployed in single system environments.

1.4.1.9 Improved Clusterware Resource Modeling

In this release, there are now more options for managing all types of applications and creating dependencies among them using Oracle Clusterware.

Improved Clusterware Resource Modeling enables a granular definition of dependencies among applications or processes to manage them as one entity.

1.4.1.10 Role-Separated Management

Role-separated management for Oracle Clusterware allows certain administrative tasks to be delegated to different people, representing different roles in the company. It is based on the idea of a clusterware administrator. The administrator may grant administrative tasks on a per resource basis. For example, if two databases are placed into the same cluster, the clusterware administrator can manage both databases in the cluster. But, the clusterware administrator may decide to grant different administrative privileges to each DBA responsible for one of those databases.

Role-separated management allows multiple applications and databases to share the same cluster and hardware resources, but ensures that different administration groups do not interfere with each other.

1.4.1.11 Agent Development Framework

Oracle Clusterware provides an agent framework for managing all kinds of applications with Oracle Clusterware. Using the agent framework provides optimized application startup, checking, and stopping based on user-defined scripts.

Making it easy to protect applications with Oracle Clusterware reduces costs allowing you to efficiently enable high availability for applications.

1.4.1.12 Zero Downtime Patching for Oracle Clusterware and Oracle RAC

The patching of Oracle Clusterware and Oracle Real Application Clusters can now be completed without taking the entire cluster down. Patchsets are now installed as out-of-place upgrades to the Oracle Grid infrastructure for a cluster software (Oracle Clusterware and Automatic Storage Management) and Oracle Database.

Now you can reduce your unplanned downtime of clustered databases and applications running in a cluster.

1.4.1.13 Enterprise Manager-Based Clusterware Resource Management

New in this release is an Enterprise Manager graphical user interface (GUI) to manage various Oracle Clusterware resources with full lifecycle support. In addition to allowing the creation and configuration of resources within Oracle Clusterware, it also helps to monitor and manage resources once deployed in the cluster.

Using Oracle Enterprise Manager as a GUI to monitor and manage various Oracle Clusterware resources eases the daily management in high availability environments.

1.4.1.14 Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters

Enterprise Manager provisioning introduces procedures to easily scale up or scale down Oracle Clusterware and Oracle Real Application Clusters.

Ease-of-implementation and management for a clustered database environment can be achieved through utilizing the Enterprise Manager provisioning framework.

1.4.1.15 Enterprise Manager Support for Grid Plug and Play

Oracle Enterprise Manager, the graphical user interface (GUI) for managing Oracle RAC, provides management and monitoring for the Grid Plug and Play environment.

Enterprise Manager is the standard GUI interface for Oracle Database. This integration provides an easy-to-use interface that customers are familiar with to manage Grid Plug and Play environments.

1.4.1.16 Enterprise Manager Support for Oracle Restart

Enterprise Manager provides support for Oracle Restart and the configuration with single-instance databases. This is a change in configuration, monitoring, and administration to enable Oracle Restart.

Enterprise Manager provides a graphical user interface (GUI) interface to easily manage Oracle databases. This additional functionality enables you to restart your Oracle databases.

1.4.1.17 Configuration Assistant Support for Removing Oracle RAC Installations

Database Configuration Assistant (DBCA), Database Upgrade Assistant (DBUA), and Net Configuration Assistant (NETCA) have been updated to support the complete deinstallation and deconfiguration of Oracle RAC databases and listeners.

This support improves the manageability of an Oracle RAC environment through automation of deinstallation and deconfiguration of Oracle RAC databases.

1.4.1.18 Oracle Universal Installer Support for Removing Oracle RAC Installations

The installer can clean up a failed Oracle Clusterware installation or upgrade of an environment prior to reattempting the operation. This ensures that the reattempted operation is done over a clean environment, thereby eliminating the chances of errors related to environmental inconsistencies.

Easily cleaning up an environment provides improved Oracle RAC manageability and deployment.

1.4.1.19 Improved Deinstallation Support With Oracle Universal Installer

The installation of Oracle Clusterware and Oracle RAC now have recovery points. If a failure occurs during installation, you can rollback to the closest recovery point and restart the installation once the problem has been corrected.

Installation rollback and recovery make the installation and configuration of Oracle Clusterware and Oracle RAC easier. It reduces project time lines by making it easy to recover from installation failures.

1.4.1.20 Downgrading Database Configured With DBControl

Scripts are included to support DBControl downgrade as part of database downgrade.

If an upgrade is deemed unsuccessful, the system needs to be returned to the starting release. In order to maintain the reliability of management when modifying software releases, DBControl must be at the same release as the database that it is monitoring.

1.4.1.21 Oracle Restart Integration with Oracle Universal Installer

Oracle Restart requires a separate installation from Oracle Database. This installation is the Oracle Grid infrastructure for a cluster installation for standalone servers which includes Oracle Restart and Oracle Automatic Storage Management (ASM). This allows separation of roles such that the system administrator can manage the infrastructure and the database administrator can manage the database.

Oracle Universal Installer is the tool to install Oracle software. This improves the manageability of the Oracle environment on a standalone server allowing separation of roles and improved resiliency of the Oracle software.

1.4.1.22 Out-of-Place Oracle Clusterware Upgrade

A new version of Oracle Clusterware is now installed into a separate home from the current installation. This reduces the downtime required to upgrade a node in the cluster and facilitate the provisioning of clusters within an enterprise.

The benefit is a reduction in planned outage time required for cluster upgrades which assists in meeting availability service levels. This also makes it easier to provide a standard installation across the enterprise.

1.4.1.23 OUI Support for Out-of-Place Oracle Clusterware Upgrade

You can now perform out-of-place upgrade of Oracle Clusterware software. The new version can be installed in a separate directory and pointed to during deployment.

Out-of-place upgrades provide easier Oracle RAC and grid deployment and manageability, as well as better testing for controlled application migration.

1.4.1.24 Server Control (SRVCTL) Enhancements

The server control (SRVCTL) commands have been enhanced to manage the configuration in a standalone server with Oracle Restart as well as the new style of cluster management (Policy-Based Cluster and Capacity Management).

This feature provides easier management of Oracle Database through a consistent interface which can be used from the console or scripted.

1.4.1.25 Server Control (SRVCTL) Enhancements to Support Grid Plug and Play

The command-line interface (CLI) for Oracle Clusterware and Oracle Real Application Clusters has been updated to support the new features of this release.

The CLI provides the ability to manage the cluster using a command line from a single point in the cluster and allows you to manage the cluster as a single entity. This reduces the management complexity for clusters and clustered databases. All changes to the cluster must be reflected in the management tool.

1.4.1.26 SRVCTL Support for Single-Instance Database in a Cluster

Using SRVCTL, you can register a single-instance database to be managed by Oracle Clusterware. Once registered, Oracle Clusterware starts, stops, monitors, and restarts the database instance.

This feature provides an improved management interface which makes it easy to provide higher availability for single-instance databases that are running on a server that is part of a cluster.

1.4.1.27 Universal Connection Pool (UCP) Integration with Oracle Data Guard

In this release, Java applications that use the Oracle Universal Connection Pool (UCP) for Java now have fast connection failover when the primary site fails. When Data Guard fails over or switches over to the standby database site, the connection pool cleans up connections to the primary site, terminates active transactions, and creates connections to the standby database.

This feature provides increased availability for Java applications using UCP with Oracle RAC and Oracle Data Guard. Applications can easily mask failures to the end user.

1.4.1.28 UCP Integration With Oracle Real Application Clusters

Universal Connection Pool (UCP) is the new Java connection pool. It has many features that make it easy for Java applications to manage connections to an Oracle Real Application Clusters database such as Web Session Affinity, XA Affinity, Runtime Connection Load Balancing, and Fast Connection Failover.

This feature provides a robust connection pool for Java applications with improved throughput and fast failover in an Oracle Real Application Clusters environment.

1.4.1.29 Universal Connection Pool (UCP) for JDBC

Universal Connection Pool for JDBC supersedes Implicit Connection Cache and provides the following functions:

Connection labeling, connection harvesting, logging, and statistics
Performance and stabilization enhancements
Improved diagnostics and statistics or metrics

UCP for JDBC provides advanced connection pooling functions, improved performance, and better diagnosability of connection issues.

1.4.1.30 Java API for Oracle RAC FAN High Availability Events

A new Java API allows Oracle RAC customers who are not using an Oracle connection pool to receive Fast Application Notification (FAN) events (for example, DOWN and UP) and then process these events, clean up or add connections when an instance, service or node leaves or joins