RMAN Faster Backup, Faster Recovery

Faster Backup, Faster Recovery

http://www.oracle.com/technology/oramag/oracle/04-nov/o64rman.html

Use Block Change Tracking

The acquired bank's data warehouse runs on Oracle Database 10g, which offers a few tools John can use to achieve his objectives. For the first objective, he decides to use the Block Change Tracking feature, new in this version. Block change tracking causes the changed database blocks to be flagged in a special file.

ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE '/u01/backup/rman/rman.trc';



SQL> alter database enable block change tracking using file '/u01/backup/tracking.trc';

SQL> alter database disable block change tracking;

During incremental backup, RMAN checks this file to see which blocks need to be backed up instead of checking all the blocks of a data file. It dramatically reduces CPU cycles and speeds up incremental backup in the process.

The block change tracking file, if present, is used automatically by the incremental backup; no special RMAN syntax is required. Using a block change tracking file saves enough time and CPU cycles for John to take incremental backups every day (or night) instead of once a week.
How big will this block change tracking file be? The size of this file does not depend on the frequency or the size of updates to the database. For John's data warehouse database, a single instance with one old backup retained, the file will be approximately 10MB for 1TB of total database size, 20MB for 2TB, and so on.

John can ensure that block change tracking is turned on and check the location and name of the file used by querying a new dictionary view:

SELECT * FROM V$BLOCK_CHANGE_TRACKING;
The output is shown in vertical format:
STATUS : ENABLED
FILENAME: /oracle/admin/SMILEY/bct.dbf
BYTES : 11599872

Here the file size is about 11MB, for John's test database of about 1TB. John can also change the location of this file. One option is to do a normal data file rename when the database is in the

MOUNTED state:

ALTER DATABASE RENAME FILE
'/oracle/admin/SMILEY/bct.dbf'
TO '/tmp/bct.dbf';

If a database shutdown is not an option, John can disable the tracking and re-enable it with a different filename as follows:
SQL>; ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE '/tmp/bct.dbf';

Database altered.

Using Incremental Merge
Now that John has a plan to address the first objective—to reduce the time required for incremental backup—he moves on to the next objective—to reduce the recovery time using only RMAN. Since incremental backups will be feasible every day thanks to the block change tracking file, John proposes a new backup strategy and schedule

BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;

This will create an RMAN backup set with the tag WEEKLY. The daily RMAN command for Monday will look like the following:

Backup Incremental LEVEL 1 TAG = MON DATABASE;
The daily command will be repeated for all other days, with appropriate tag values representing the weekday. After a week, if John queries the RMAN recovery catalog, he will see something similar to Listing 1.
Now let's examine a failure scenario with John's revised incremental backup schedule (which includes daily incremental backups). If the data file for tablespace USERS fails on Saturday, John has to restore the Level 0 backup taken (with tag WEEKLY) on Sunday and then apply all the incremental backups with keys from 36 through 46 from the output shown in Listing 1. After that he needs to apply the archived logs. The restore and recovery operations are shown in Listing 2. Note how all the incremental backups with tags MON, TUE, and so on are applied one after the other. After all of the incremental backups have been applied, the media recovery begins and applies the information in the archived logs.

If a full backup had been taken every day, the recovery would not have had to apply all the incremental backups, saving considerable time. But since that is not possible, what if John could apply the incremental backups taken on weekdays to the Level 0 backup taken earlier? This would make the Level 0 backup up-to-date with the latest day's changes and eliminate the need to apply all the subsequent incremental backups during recovery. In Oracle Database 10g, using a disk-based backup strategy, John can accomplish that using the Incremental Merge feature of the RMAN backup. With such an approach, the daily RMAN incremental backup script will look like this:

BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY
WITH TAG WEEKLY DATABASE;
RECOVER COPY OF DATABASE
WITH TAG WEEKLY;
Adding ASM


Create an ASM instance on the data warehouse server.
You do this by creating a parameter file with a special parameter named INSTANCE_TYPE=ASM and bringing up the instance. Note that there are no control files, redo log files, or data files for ASM instances.

Once the ASM instance is started, create a disk group RMANDG as follows:

CREATE DISKGROUP rmandg
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';

This assumes that John has four disks with which he wants to make an ASM group. He can expand it to as many disks as he likes. The above disk group is analogous to mirrored disks with the creation of failure groups, and each failure group is striped across two disks.
Change the flash recovery area of the database to point to the disk group:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RMANDG'

Note the plus (+) sign before the disk group name, which indicates an ASM disk group.

use RMAN to back up the recovery area from ASM to tape (at the network backup server) through the following command:

BACKUP RECOVERY AREA;
This backs up the entire recovery area, including data file backups, archived logs, and control file backups from the ASM disk group to the tape.

There is no need to keep multiple copies of the backups on tape. The following RMAN command deletes the old backup sets:

DELETE OBSOLETE DEVICE TYPE sbt;

John proposes to back up the ASM disks to tape every week, so the tapes will contain a week's worth of data. In case of a data warehouse database failure, the recovery can be quick from the SATA disks. If the disks fail, the database can also be recovered from the tape.

Reducing Traffic with Compression
John's last objective is to reduce the size of the incremental backups significantly so that they do not overwhelm the network. John addresses this issue by proposing to enable the compression feature of RMAN, which uses a proprietary algorithm to compress the backup sets. John's proposed backup scripts change a little to add the compression feature:

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL
LEVEL 1 TAG = WEEKLY DATABASE;


Incrementally Updated Backups
Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used:

RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}The RECOVER COPY... line will not do anything until the script has been running for more than 7 days. The BACKUP INCREMENTAL line will perform a complete backup (level 0) the first day it is run, with all subsequent backups being level 1 incremental backups. After 7 days, the RECOVER COPY... line will start to take effect, merging all incremental backups older than 7 days into the level 0 backup, effectively moving the level 0 backup forward. The effect of this is that you will permanently have a 7 day recovery window with a 7 day old level 0 backup and 6 level 1 incremental backups. Notice that the tag must be used to identify which incremental backups apply to which image copies.

If you wanted to keep your image copy as up to date as possible you might do the following:

RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
}In this example the incremental backup is merged into the image copy as soon as it is completed.
Fast Incremental Backups
There are performance issues associated with incremental backups as the whole of each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query:

SELECT status FROM v$block_change_tracking;Change tracking is enabled using the ALTER DATABASE command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;


Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:

•To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

•For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

Oracle 10g Automatic Storage Management (ASM)

Initialization Parameters and ASM Instance CreationThe initialization parameters that are of specific interest for an ASM instance are:

INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered. Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.

INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Create an spfile using the contents of the init+ASM.ora file.

SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.Finally, start the instance with the NOMOUNT option.

SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
SQL>

The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.

SQL> shutdown
ASM instance shutdown
SQL>

Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:

DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST

Startup and Shutdown of ASM InstancesASM instance are started and stopped in a similar way to normal database instances.
The options for the STARTUP command are:

FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance. The options for the SHUTDOWN command are:
NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.

Administering ASM Disk Groups
Disks

Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID. In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like "disk_group_1_0001". The FORCE option can be used to move a disk from another disk group into this one.

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the

ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.

-- Add disks.

ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';

-- Drop a disk.

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.

-- Resize a specific disk.

ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.

ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.

ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;

The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Disk groups can be rebalanced manually using the REBALANCE clause of the
ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.

ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;

TemplatesTemplates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.

-- Create a new template.

ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.

ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.

ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;Available attributes include:

UNPROTECTED - No mirroring or striping regardless of the redundancy setting.
MIRROR - Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
COARSE - Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
FINE - Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

DirectoriesA directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing. The following examples show how ASM directories can be created, modified and deleted.

-- Create a directory.

ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.

ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.

ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

AliasesAliases allow you to reference ASM files using user-friendly names, rather than the fully qualified ASM filenames.

-- Create an alias using the fully qualified filename.

ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1/mydb/datafile/my_ts.342.3';


-- Create an alias using the numeric form filename.

ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';

-- Rename an alias.

ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.

ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';

Attempting to drop a system alias results in an error.
FilesFiles are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

-- Drop file using an alias.

ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.

ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.

ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

Checking MetadataThe internal consistency of disk group metadata can be checked in a number of ways using the CHECK clause of the ALTER DISKGROUP statement.

-- Check metadata for a specific file.

ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'

-- Check metadata for a specific failure group in the disk group.

ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;

-- Check metadata for a specific disk in the disk group.

ALTER DISKGROUP disk_group_1
CHECK DISK diska1;

-- Check metadata for all disks in the disk group.

ALTER DISKGROUP disk_group_1 CHECK ALL;

ASM ViewsThe ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependant database instance.
View
ASM Instance
DB Instance

V$ASM_ALIAS Displays a row for each alias present in every disk group mounted by the ASM instance. Returns no rows
V$ASM_CLIENT Displays a row for each database instance using a disk group managed by the ASM instance.
Displays a row for the ASM instance if the database has open ASM files.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
Displays a row for each disk in disk groups in use by the database instance.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM instance.
Displays a row for each disk group mounted by the local ASM instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the ASM instance.
Displays no rows.
V$ASM_OPERATION Displays a row for each file for each long running operation executing in the ASM instance.
Displays no rows.
V$ASM_TEMPLATE Displays a row for each template present in each disk group mounted by the ASM instance.

SQL and ASMASM
filenames can be used in place of conventional filenames for most Oracle file types, including controlfiles, datafiles, logfiles etc. For example, the following command creates a new tablespace with a datafile in the disk_group_1 disk group.

CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;

Migrating to ASM Using RMAN

The following method shows how a primary database can be migrated to ASM from a disk based backup:

Disable change tracking (only available in Enterprise Edition) if it is currently being used.

SQL> SHUTDOWN IMMEDIATE
Modify the parameter file of the target database as follows:

Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.

Start the database in nomount mode.

RMAN> STARTUP NOMOUNT
Restore the controlfile into the new location from the old location.

RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database.

RMAN> ALTER DATABASE MOUNT;
Copy the database into the ASM disk group.

RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
Switch all datafile to the new ASM location.

RMAN> SWITCH DATABASE TO COPY;
Open the database.

RMAN> ALTER DATABASE OPEN;
Create new redo logs in ASM and delete the old ones.

Enable change tracking if it was being used.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;


Example


1. Using RMAN, connect to the target database as follows:
[oracle@oradb1 oracle]$ rman
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN> connect target
connected to target database: SSKYDB (DBID=2290365532)

2. Using SQL, offline the tablespace that will be the migration
candidate:

RMAN> SQL "ALTER TABLESPACE EXAMPLE OFFLINE";

3. Using the “backup as copy” operation, perform the following
operation:

RMAN> BACKUP AS COPY TABLESPACE EXAMPLE FORMAT '+ASMGRP1';
Starting backup at 01-OCT-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=252 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u14/oradata/SSKYDB/example01.dbf
output filename=+ASMGRP1/sskydb/datafile/example.258.1
tag=TAG20041001T221236 recid=2 stamp=538438446
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
Finished backup at 01-OCT-04

RMAN>
4. Once the “copy complete” message is received, indicating a successful
copy, switch the tablespace to use the copied datafile:

RMAN> SWITCH TABLESPACE EXAMPLE TO COPY;
datafile 5 switched to datafile copy "+ASMGRP1/sskydb/
datafile/example.258.1"

RMAN>
5. The final step is to ONLINE the tablespace using SQL:
RMAN> SQL "ALTER TABLESPACE EXAMPLE ONLINE";

6. Verify the operation by connecting to the target database and
checking the V$ views.

Converting non-ASM datafile to ASM using
DBMS_FILE_TRANSFER stored procedure

The DBMS_FILE_TRANSFER package provides a means to copy files between two locations. In Oracle Database 10g, this procedure is used to move or copy files between ASM disk groups and is the primary utility used to instantiate an ASM Data Guard database. Using this procedure, the following

transfer scenarios are possible:

1. Copy files from one ASM disk group to another ASM disk group.
2. Copy files from an ASM disk group to an external storage media
such as a file system at the operating system level.
3. Copy files from a file system at the operating system level to an
ASM-configured disk group.
4. Copy files from a file system at the operating system level to
another location or raw device at the operating system level.
Steps to be performed to move datafiles from one location to another
using the DBMS_FILE_TRANSFER procedure are as follows:
1. Identify the datafile to be moved or copied from one location to
another:
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------
+ASMGRP1/sskydb/datafile/users.259.571954319
+ASMGRP1/sskydb/datafile/sysaux.257.571954317
+ASMGRP1/sskydb/datafile/undotbs1.258.571954317
+ASMGRP1/sskydb/datafile/system.256.571954317
+ASMGRP1/sskydb/datafile/example.264.571954419
+ASMGRP1/sskydb/datafile/undotbs2.265.571954545
+ASMGRP1/sskydb/datafile/undotbs3.266.571954547
+ASMGRP1/sskydb/datafile/sorac_data.272.572018797
+ASMGRP1/sskydb/datafile/bmf_data.273.572018897
2. Identify the destination (ASM or non-ASM) where the file will be
copied.
3. The datafile is copied to an external OCFS-based file system location.
4. Take the datafile offline:

SQL> ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' OFFLINE;
5. Copy the file to the new location by first creating a
DIRECTORY_NAME for the source and target locations and using
the following procedure:
SQL> CREATE DIRECTORY ASMSRC AS '+ASMGRP1/SSKYDB/
DATAFILE';
Directory created.
SQL> CREATE DIRECTORY OSDEST AS '/ocfs9/oradata';
Directory created.
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('ASMRC',
'BMF_DATA.273.572018897',
'OSDEST',
'BMF.dbf');
END;
/
6. Bring the datafile online:
ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' ONLINE;
7. Verify the copied file:
[oracle@oradb1 oradata]$ ls –ltr /ocfs9/oradata



Viewing Information About Automatic Storage Management
You can use these views to query information about Automatic Storage Management:

View Description
V$ASM_DISKGROUP In an ASM instance, describes a disk group (number, name, size related info, state, and redundancy type).
In a DB instance, contains one row for every ASM disk group mounted by the local ASM instance.

This view performs disk discovery every time it is queried.

V$ASM_DISK In an ASM instance, contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.
In a DB instance, contains rows only for disks in the disk groups in use by that DB instance.

This view performs disk discovery every time it is queried.

V$ASM_DISKGROUP_STAT Has the same columns as V$ASM_DISKGROUP, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISKGROUP instead.

V$ASM_DISK_STAT Has the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISK instead.

V$ASM_FILE In an ASM instance, contains one row for every ASM file in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.

V$ASM_TEMPLATE In an ASM or DB instance, contains one row for every template present in every disk group mounted by the ASM instance.

V$ASM_ALIAS In an ASM instance, contains one row for every alias present in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.

V$ASM_OPERATION In an ASM instance, contains one row for every active ASM long running operation executing in the ASM instance.
In a DB instance, contains no rows.

V$ASM_CLIENT In an ASM instance, identifies databases using disk groups managed by the ASM instance.
In a DB instance, contains one row for the ASM instance if the database has any open ASM files.

Oracle 10g Automatic Storage Management (ASM)

Initialization Parameters and ASM Instance CreationThe initialization parameters that are of specific interest for an ASM instance are:

INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Oracle 10g Automatic Storage Management (ASM)

Initialization Parameters and ASM Instance Creation

INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.


DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.


ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.


ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.


ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.


Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.


To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.

INSTANCE_TYPE=ASM

Next, using SQL*Plus connect to the ide instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Create an spfile using the contents of the init+ASM.ora file.

SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.

Finally, start the instance with the NOMOUNT option.

SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes

The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.

SQL> shutdown
ASM instance shutdown

Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:

DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST

Startup and Shutdown of ASM Instances

ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.

MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.

NOMOUNT - Starts the ASM instance without mounting any disk groups.

OPEN - This is not a valid option for an ASM instance.

Administering ASM Disk Groups

DisksDisk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

The options for the SHUTDOWN command are:

NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.

-- Add disks.

ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';

-- Drop a disk.

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Oracle Shell Scripting Unix

UNIX and Linux (Method 1)The previous methods works equally well in UNIX and Linux environments. For example, save the following

script in a file called "/u01/emp.sql".

CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a shell script called "/u01/get_emp.ksh" containing the following lines.
#!/bin/ksh

sqlplus /nolog @/u01/emp.sql

The following command makes the file executable for the file owner.

chmod u+x /u01/get_emp.ksh

The resulting shell script can be run manually from the command line, or scheduled using CRON.For RMAN, place the following

RMAN commands in a file called "/u01/cmdfile.txt".


RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;

Next create a batch file called "/u01/backup.ksh" containing the following lines.

#!/bin/ksh

rman target=/ @/u01/cmdfile.txt

This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command.

chmod u+x /u01/backup.ksh


UNIX and Linux (Method 2)

UNIX and Linux environments also allow the SQL*Plus and RMAN commands to be piped directly from the command line. For example, save the following commands in a file called "/u01/get_emp.ksh".

#!/bin/ksh

sqlplus /nolog << EOF

CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
EOF

chmod u+x /u01/get_emp.ksh

#!/bin/ksh

rman target=/ << EOF

RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
EOF

chmod u+x /u01/backup.ksh

Oracle Shell Scripting Windows

WindowsTo run an SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a

file called "C:\emp.sql".

CONNECT scott/tiger
SPOOL C:\emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a batch file called "C:\get_emp.bat" containing the following command.

sqlplus /nolog @C:\emp.sql


The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.The method is very similar when using Recovery Manager (RMAN). As an

example, place the following RMAN commands in a file called "C:\cmdfile.txt".


RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT 'C:\oracle\backup\DB10G%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;

Next create a batch file called "C:\backup.bat" containing the following command.

rman target=/ @cmdfile.txt

AutoNumber Table And Identity Functionality Trigger

Developers who are used to AutoNumber columns in MS Access or Identity columns in SQL Server often complain when they have to manually populate primary key columns using sequences.

CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Next we create a trigger to populate the ID column if it's not specified in the insert:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

Finally we can test it using the automatic and manual population methods:

SQL> INSERT INTO departments (description)

2 VALUES ('Development');
1 row created.

SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
1 row selected.

SQL> INSERT INTO departments (id, description)
2 VALUES (dept_seq.NEXTVAL, 'Accounting');
1 row created.

SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
2 Accounting
2 rows selected.

SQL>
The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT NVL(:new.id, dept_seq.NEXTVAL)
INTO :new.id
FROM dual;
-- Do more processing here.
END;
/

To overwrite any values passed in you should do the following:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

To error if a value is passed in you should do the following:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
IF :new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/

Basic Enterprise Manager EMCA

USING EM CONFIGURATION ASSISTANT

CLUSTER

emca -reconfig dbcontrol -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2

emca -config dbcontrol db -repos create -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2


$ emca –config dbcontrol db –repos create

This example shows how to remove your database from Database Control:
$ emca –deconfig dbcontrol db –repos drop

This example instructs the EM configuration assistant to enable Database Control and configure backups for your database:

$ emca –config dbcontrol db –repos create –backup

$ emca –help



The web-based Enterprise Manager tool was introduced in Oracle 10g and has significant functionality improvements over previous versions. Even so, it seems there is not a day goes by without someone asking how to solve an Enterprise Manager configuration issue, so this article explains the first things you should look at. It's not meant to be an all encompassing guide. Just a first shot.

First, check the listener is running correctly.


lsnrctl statusIf you get something like the following, your listener is not running and you need to start it using the "lsnrctl start" command.

$ lsnrctl status


LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 13-MAR-2009 13:32:49
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel5-11g.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$Next, check the Enterprise Manager service is running.

emctl start dbconsoleAssuming the console is already running, or fails to start, the next thing to do is to reconfigure Enterprise Manager. This is done using the Enterprise Manager Configuration Assistant (EMCA). For a first attempt, run the following command.

emca -config dbcontrol db -repos recreateIf it all goes well you would expect to see something like this.


$ emca -config dbcontrol db -repos recreate


STARTED EMCA at Mar 13, 2009 11:32:35 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Database Control is already configured for the database DB11G
You have chosen to configure Database Control for managing the database DB11G
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 11:33:19 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_11_32_35.log.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 3938 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 11:33:22 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 11:40:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 11:40:11 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 11:41:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 11:42:31 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 11:42:34 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:1158/em <<<<<<
Mar 13, 2009 11:42:48 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 11:42:48 AM
$If this fails, then de-configure and configure Enterprise Manager.


emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos createThe deconfig will look something like this.

$ emca -deconfig dbcontrol db -repos drop


STARTED EMCA at Mar 13, 2009 12:09:48 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:10:07 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_09_47.log.
Mar 13, 2009 12:10:08 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 12:10:31 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 12:11:56 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:12:02 PM
$The config will look something like this.
$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 13, 2009 12:37:27 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:37:54 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_37_27.log.
Mar 13, 2009 12:37:55 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 12:42:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 12:42:07 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 12:42:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:5500/em <<<<<<
Mar 13, 2009 12:44:21 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:44:21 PM



EJEMPLO


$ emca -repos create
$ emca -config dbcontrol db

STARTED EMCA at Fri May 14 10:43:22 MEST 2004
Enter the following information about the database
to be configured.

Listener port number: 1521
Database SID: AKI1
Service name: AKI1.WORLD
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
Password for dbsnmp: xxxxxxx
Password for sysman: xxxxxxx
Password for sys: xxxxxxx

---------------------------------------------------------
You have specified the following settings

Database ORACLE_HOME: /opt/oracle/product/10.1.0
Enterprise Manager ORACLE_HOME: /opt/oracle/product/10.1.0

Database host name ..........: akira
Listener port number .........: 1521
Database SID .................: AKI1
Service name .................: AKI1
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
---------------------------------------------------------
Do you wish to continue? [yes/no]: yes
AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file ../config/repository.variables ...

Now wait about 10 Minutes to complete!

M oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
M oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
M oracle.sysman.emcp.util.PortQuery findUsedPorts
INFO: Searching services file for used port
AM oracle.sysman.emcp.EMConfig getProperties
...........
...........
INFO: Starting the DBConsole ...
AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://akira:5500/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Fri May 14 10:55:25 MEST 2004

Try to connect to the database Control

http://akira:5500/em



If you look at the installed schemas, you can now find the SYSMAN schema, which is the database Control Repository.

Troubleshooting

If you have troubles to connect, check your local configuration which can be found in $ORACLE_HOME/_. For Example our DbConsole Setup Directory looks as follows:

$ pwd
/opt/oracle/product/10.1.0/akira_AKI1/sysman/config

$ ls -l
-rw-r--r-- b64InternetCertificate.txt
-rw-r--r-- emagentlogging.properties
-rw-r--r-- emd.properties
-rw-r--r-- emomsintg.xml
-rw-r--r-- emomslogging.properties
-rw-r--r-- emoms.properties
-rw-r--r-- OUIinventories.add

The most important file is emoms.properties, where you can find all the configuration parameters.

#Fri May 14 10:54:49 CEST 2004
oracle.sysman.emSDK.svlt.ConsoleServerName=
akira_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=0b878f6184e8319d
emdrep.ping.pingCommand=/bin/ping
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=AKI1.WORLD
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
oracle.sysman.emRep.dbConn.statementCacheSize=30
oracle.sysman.db.isqlplusUrl=
http\://akira\:5560/isqlplus/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerPort=5500
oracle.sysman.eml.mntr.emdRepRAC=FALSE
oracle.sysman.emSDK.emd.rt.useMonitoringCred=true
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
oracle.sysman.db.isqlplusWebDBAUrl=
http\://akira\:5560/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=akira
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=5500
oracle.sysman.eml.mntr.emdRepServer=akira
oracle.sysman.eml.mntr.emdRepSID=AKI1
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.eml.mntr.emdRepConnectDescriptor=
(DESCRIPTION\=(ADDRESS_LIST\=
(ADDRESS\=(PROTOCOL\=TCP)(HOST\=akira)(PORT\
=1521)))(CONNECT_DATA\=(SERVICE_NAME\=AKI1)))
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.db.adm.conn.statementCacheSize=2
oracle.sysman.db.perf.conn.statementCacheSize=30

Automatically start and stop the DB-Console

$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

Ejemplo Mover Tabla No particionada a una Tabla Particionada DBSMS_REDEFINITION

DBMS_REDEFINITION package.

Create and populate a small lookup table.

CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50));

ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id));

INSERT INTO lookup (id, description) VALUES (1, 'ONE');

INSERT INTO lookup (id, description) VALUES (2, 'TWO');

INSERT INTO lookup (id, description) VALUES (3, 'THREE');

COMMIT;

CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50));

DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);

-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

-- Create partitioned table.

CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));

With this interim table in place we can start the online redefinition.EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');
If no errors are reported it is safe to start the redefintion using the following command.

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/

resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation

BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/

-- Add new keys, FKs and triggers.

ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);

BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;

-- Rename all the constraints and indexes to match the original names.

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.

SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005
3 rows selected.

2 EJEMPLO

CREATE TABLE "DBO"."TEM_SEG_OFERTA" ("HGO_ID" NUMBER NOT NULL,
"OFT_ID" NUMBER(9) NOT NULL, "FSO_ID" NUMBER(2) NOT NULL,
"HGO_FCH_REGISTRO" DATE NOT NULL, "FUN_ID" NUMBER(4),
"HGO_DESCRIPCION" VARCHAR2(300 byte)
)
PARTITION BY RANGE (HGO_FCH_REGISTRO)
(PARTITION HIS_SEG_OFERTA_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2006 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2008 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2009 VALUES LESS THAN (TO_DATE('01/01/2010', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2019 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2020 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2021 VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS")
/


SQL> EXEC Dbms_Redefinition.can_Redef_Table(USER, 'HIS_SEG_OFERTA');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.start_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_redefinition.sync_interim_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEM_SEG_OFERTA', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');

PL/SQL procedure successfully completed.

SQL>

ORA-12091: cannot online redefine table with materialized views
Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.

SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END;

*
ERROR at line 1:
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1

Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.

Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;

LOG_TABLE
------------------------------
MLOG$_IN_CDR
MLOG$_OUT_CDR

SQL> select master,log_table from user_mview_logs;MASTER LOG_TABLE------------------------------ ------------------------------IN_CDR MLOG$_IN_CDROUT_CDR MLOG$_OUT_CDR
Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.

SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.

You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.
And it works.

Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table('CR_2', 'IN_CDR', 'IN_CDR_');
PL/SQL procedure successfully completed.

How does one select the LAST N rows from a table?

How does one select the LAST N rows from a table?

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows.
E
Examples:

Get the bottom 10 employees based on their salary

SELECT ename, sal FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank FROM emp ) WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries

SELECT ename, sal FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank FROM emp ) WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:

SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1) WHERE ROWNUM < 10;

Use this workaround for older (8.0 and prior) releases:

SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol <= a.maxcol)

How Select the TOP N rows from a table?

TOP N rows from a table?

After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.

Below is the examples to find the top 5 employees based on their salary.

Using RANK()

SELECT employee_name, salary FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rankFROM employee ) WHERE salary_rank <= 5;

Using Dense_Rank()

SELECT employee_name, salary FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rankFROM employee ) WHERE salary_dense_rank <= 5;

Using inner queryThis is an example of using an inner-query with an ORDER BY clause:

SELECT *FROM (SELECT * FROM employee ORDER BY salary DESC)WHERE ROWNUM < 5;

Using count distinct combination

SELECT *FROM employee eWHERE 5 >= (SELECT COUNT(DISTINCT salary)FROM employee bWHERE b.salary >= e.salary)ORDER BY salary DESC;


EJEMPLO DE SQL

select tabla,registros,fecha from (select tabla,registros,fecha,rank() OVER (order by registros desc) registros_rank
FROM CTM.AUD_REGISTRO_TABLAS
where to_date(fecha,'dd-mm-yyyy')='08-09-2009')
where registros_rank <=10
/
~

Restrict DDL on a Schema

Restrict DDL on a Schema

SQL> conn faruk/farukConnected.

SQL> create table before_trigger(a number);Table created.

SQL>conn system/aConnected.

SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_FARUK
3 BEFORE DDL
4 ON FARUK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /Trigger created.

SQL> conn faruk/farukConnected.

SQL> create table after_trigger(a number);
create table after_trigger(a number)

*

ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-21000: error number argument to raise_application_error of -30900 is out ofrangeORA-06512: at line 2

History of Oracle Corporation.

History of Oracle Corporation.



1)June 16, 1977: Oracle Corporation was incorporated in Redwood Shores, California as Software Development Laboratories (SDL) by Larry Ellison, Bob Miner and Ed Oates.


2)June 1979: SDL is renamed to Relational Software Inc. (RSI), and relocates to Sand Hill Road, Menlo Park, California. Oracle 2, the first version of the Oracle database software runs on PDP-11 and is sold to Wright-Patterson Air Force Base. The company decides to name the first version of its flagship product version 2 rather than version 1 because it believes companies may hesitate to buy the initial release of its product (and, or IBM would not divulge to render the original take).


3)October 1979: RSI actively promotes Oracle on the VAX platform (the software runs on the VAX in PDP-11 emulator mode)


4)1981 Umang Gupta joined Oracle Corporation where he wrote the first business plan for the company, and served as Vice President and General Manager of the Microcomputer Products.


5)February 1981: RSI begins developing tools for Oracle, including the Interactive Application Facility (IAF), a predecessor to Oracle*Forms.


6)Bruce Scott was one of the first employees at Oracle (then Software Development Laboratories). He co-founded Gupta Technologies (which later became Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc. Bruce was co-author and co-architect of Oracle V1, V2 and V3. He created the sample schema "SCOTT" (containing tables like EMP and DEPT) with the password defaulted to TIGER (apparently named after his cat).


7)March 1983: RSI rewrites Oracle in C for portability and Oracle version 3 is released. RSI is renamed to Oracle to more closely align with its primary product. The word Oracle was the code name of a CIA project which the founders had all worked on while at the Ampex Corporation.


8)April 1984: Received additional funding from Sequoia Capital.


9)October 1984: Oracle version 4 released, introducing read consistency.


10)November 1984: Oracle ports the database software to the PC platform. The MS-DOS version (4.1.4) of Oracle runs in only 512K of memory. Oracle for MSDOS version 5 was released in 1986 running in Protected Mode on 286 machines using a technique invented by Mike Roberts, among the first products to do so.


11)April 1985: Oracle version 5 released. It is one of the first RDBMSs to operate in client-server mode.


12)1986: Oracle version 5.1 released with support for distributed queries. Investigations into clustering begin.


13)March 12, 1986: Oracle goes public with revenues of $55 million USD.


14)August 1987: Oracle founds its Applications division, building business management software closely integrated with its database software. Oracle acquires TCI for its project management software.


15)1988: Oracle version 6 is released with support for row-level locking and hot backups. The PL/SQL procedural language engine was embedded in the database but no provision was made to store program blocks such as procedures and triggers in the database - this capability was added in v7. PL/SQL blocks could be submitted for immediate execution in the server from an environment such as SQL*Plus, or via SQL statements embedded in a host program. Separate PL/SQL engines were included in various client tools (SQL*Forms, Reports).


16)1989: Oracle moves world headquarters to Redwood Shores, California. Revenues reach US$584 million.


17)1990: In the third quarter, Oracle reports its first ever loss, hundreds of employees are laid off. Ellison hires Jeffrey O. Henley as CFO and Raymond Lane as COO.


18)June 1992: Oracle 7 released with performance enhancements, administrative utilities, application development tools, security features, the ability to persist PL/SQL program units in the database as stored procedures and triggers, and support for declarative referential integrity.


19)1993: Releases Oracle's Cooperative Development Environment (CDE) which bundles Oracle Forms, Reports, Graphics, Book.


20)1994: Oracle acquired the database-product DEC Rdb (now called Oracle Rdb) from Digital Equipment Corporation (DEC) and development is still going on. Oracle Rdb is only available on the OpenVMS platform (also a former product of DEC).


21)June 21, 1995: Oracle announces new data warehousing facilities, including parallel queries.


22)November 1995: Oracle is one of the first large software companies to announce an internet strategy when Ellison introduces the network computer concept at an IDC conference in Paris.


23)April 1997: Oracle releases the first version of Discoverer, an ad-hoc query tool for business intelligence.


24)June 1997: Oracle 8 is released with SQL object technology, internet technology and support for terabytes of data.


25)September 1997: Oracle announces its commitment to the Java platform, and introduces Oracle's Java integrated development environment, which will come to be known as Oracle JDeveloper.


26)January 1998: Oracle releases Oracle Applications 10.7 NCA. All the applications in the business software now run across the web in a standard web browser.


27)May 1998: Oracle Applications 11 is released.


28)April 1998: Oracle announces that it will integrate a Java virtual machine with Oracle Database.


29)September 1998: Oracle 8i is released.


30)October 1998: Oracle 8 and Oracle Application Server 4.0 are released on the Linux platform.


31)May 1999: Oracle releases JDeveloper 2.0, showcasing Business Components for Java (BC4J), a set of libraries and development tools for building database aware applications.


32)2000: OracleMobile subsidiary founded. Oracle 9i released.


33)May 2000: Oracle announces the Internet File System (iFS), later rebranded as Oracle Content Management SDK.


34)June 2000: Oracle9i Application Server released with support for building portals.


35)2001: Ellison announces that Oracle saved $1 billion implementing and using its own business applications.


36)2004: Oracle 10g released.


37)December 13, 2004: After a long battle over the control of PeopleSoft, Oracle announces that it has signed an agreement to acquire PeopleSoft for $26.50 per share (approximately $10.3 billion).


38)January 14, 2005: Oracle announces that it will reduce its combined workforce to 50,000, a reduction of approximately 5,000 following the PeopleSoft take over. 90% of PeopleSoft product development and product support staff will be retained.


39)March, 2005: Oracle extends its Middle East operations by opening a regional office in Amman, Jordan.


40)September 2005: Oracles announces that it has agreed to acquire Global Logistics Technologies, Inc. (private company), a global provider of logistics and transportation managements software (TMS) solutions through a cash offer.


41)September 12, 2005: Oracle announces it had agreed to buy Siebel Systems, the global leader in CRM technologies and a key player in the BI realm, for $5.8 billion.


42)October 25, 2006: Oracle announces Unbreakable Linux.


43)November 2, 2006: Oracles announces that it has agreed to acquire Stellent, Inc. (NASDAQ: STEL), a global provider of enterprise content management (ECM) software solutions, through a cash tender offer for $13.50 per share, or approximately $440 million.


44)December 15, 2006, a majority of MetaSolv stockholders approved Oracle’s acquisition of MetaSolv Software, a Leading Provider of Operations Support Systems (OSS) Software for the Communications Industry.


45)March 1, 2007: Oracle announced that it has agreed to buy Hyperion Solutions Corporation (Nasdaq: HYSL), a leading global provider of performance management software solutions, through a cash tender offer for $52.00 per share, or approximately $3.3 billion. The transaction is subject to customary conditions and is expected to close in April 2007.


46)March 22, 2007: Oracle filed a court case against its major competitor SAP AG in the Californian courts for malpractice and unfair competition. The full text of the filing can be found on the claimants web site under the heading newsroom.


47)October 12, 2007: Oracle announced that it had made a bid to buy BEA Systems for a price of $17/share, an offer that was rejected by the BEA board who felt that the company was worth more than that.


48)October 16, 2007: Oracle confirms impending departure of John Wookey, senior vice president for application development and head of its applications strategy, raising questions in the planned release and future of Oracle's Fusion Applications strategy.


49)January 16, 2008: Oracle announces it is buying BEA Systems for $19.375/Share in cash for a total of '$7.2 billion net of cash.'

H.Tonguç Yılmaz - Oracle BlogHistory of OraclePosted in Oracle Other by H.Tonguç Yılmaz on December 27th, 2006


1978 -> Oracle V1; first commercial SQL relational database management system (RDBMS), Main architect Bob Miner, ran on pdp-11 under rsx; 128Kb memory, written in assembly, separated oracle and user codesto overcome the memory limitations
1979 -> Oracle V2; written in pdp-11 assembly language, ran on vax/vms in compatibility mode
1980 -> Oracle V3; written in C, soptable source code, introduced Transactions
1984 -> Oracle V4; introduced read consistency, ported to many plathforms, first interopability between PC and server
1986 -> Oracle V5; true client-sever, vax cluster support, distributed queries
1989 -> Oracle V6; OLTP performance enhancements, online backup/recovery, row level locking, plsql language, parallel server
1993 -> Oracle V7; declarative referential integrity, stored procedures and triggers, shared SQL, parallel execution, Advanced replication
1997 -> Oracle V8; Object-relational database, three-tier architecture, partitioning
1999 -> Oracle V8i; Java in database and native java support, XML support, Oracle Internet Directory, Summary management interMedia, Data warehousing enhancements, ported to Linux, Business components for java(BC4J), WebDB introduced(eventually mature into Portal and ApEx)
2001 -> Oracle V9i; Automatic segment space management, Real Apllication Clusters, Internet security enhancements, Data Guard, Advanced globalization support, record-breaking TPC-C benchmark results, 1st to complete 3 terabyte TPC-H world record
2003 -> Oracle V10g; Enterprise Grid Computing, 64-bit Linux with IPF
2005 -> Oracle VXE; free Oracle 10gR2 database
2007 -> Oracle V11g; as announced at Openworld 2006
In a nutshell, Version Date Release Name
OracleRelease Date2 June 19793 March 19834 October 19845.0 April 19856.0 July 19887.0 June 19927.1 May 19947.2 May 19957.3 February 19968.0 June 1997 Oracle 88.1.5 February 1999 Oracle 8i Release 18.1.6 November 1999 Oracle 8i Release 28.1.7 August 2000 Oracle 8i Release 39.0.1 June 2001 Oracle 9i Release 19.2 May 2002 Oracle 9i Release 210.1 January 2004 Oracle 10g Release 110.2 July 2005 Oracle 10g Release

2007 Oracle 11g

Restore and Recover database to a new host

Restore and Recover database to a new host



In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.


1) Machine(Source)


RMAN> backup database;
Starting backup at 06-MAY-08using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbfinput datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbfinput datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbfinput datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbfinput datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbfinput datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbfinput datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbfinput datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbfchannel ORA_DISK_1: starting piece 1 at 06-MAY-08channel ORA_DISK_1: finished piece 1 at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:35Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 06-MAY-08

2)Transfer this two backup pieces to target machine(From Neptune) bash-3.00$
scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/Password:o1_mf_nnndf_TAG20080 100% *********************************************** 525 MB 00:59bash-3.00
$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/Password:o1_mf_s_654016132_42 100% *********************************************** 6976 KB 00:00
3)Determine the DBID of source machine()

SQL> select dbid from v$database;DBID----------3386862614

4)Now perform task on target machine(Saturn here).First set ORACLE_SID,-bash-3.00
$export ORACLE_SID=dbase1

Then connect to rman,-bash-3.00

$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database (not started)

5)Set DBID and restore spfile to pfile.

RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfileOracle instance started

Total System Global Area 159383552 bytesFixed Size 2019224 bytesVariable Size 67108968 bytesDatabase Buffers 83886080 bytesRedo Buffers 6369280 bytes

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkpchannel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location 6)start the instance with pfile.

RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';

Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytesVariable Size 109055720 bytesDatabase Buffers 92274688 bytesRedo Buffers 6365184 bytes

7)Restore controlfile and mount the database.

RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using channel ORA_DISK_1
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output filename=/oradata2/DBase1/control01.ctloutput filename=/oradata2/DBase1/control02.ctloutput filename=/oradata2/DBase1/control03.ctlFinished restore at 06-MAY-08

RMAN> ALTER DATABASE MOUNT;
database mountedreleased channel: ORA_DISK_1

8)From SQL*Plus determine the data file and redo log file name.

SQL> COLUMN NAME FORMAT a70SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE2 UNION3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME---------- ------------------------------------------------------------1 /oradata2/data1/dbase1/redo01.log1 /oradata2/data1/dbase1/system01.dbf2 /oradata2/data1/dbase1/redo02.log2 /oradata2/data1/dbase1/undotbs01.dbf3 /oradata2/data1/dbase1/redo03.log3 /oradata2/data1/dbase1/sysaux01.dbf4 /oradata2/data1/dbase1/users01.dbf5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.

RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiecebackup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp recid=33 stamp=65398295

RMAN> list backup;
List of Backup Sets===================
BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------32 Full 525.67M DISK 00:01:31 06-MAY-08BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkpList of Datafiles in backup set 32File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.

RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

11)Open the Database resetlogs option.

RMAN> alter database open resetlogs;

How to put dumps in multiple locations

The %U causes file name to be generated by oracle and sequentially like rest01.dmp, rest02.dmp and etc.

Below is an example which will take dump of tablespace users and each dump size will be 300K and it will span in C:\, D:\ and E:\ drive each.

SQL> create or replace directory d as 'd:';
Directory created.

SQL> create or replace directory e as 'e:';
Directory created.

SQL> create directory c as 'c:';
Directory created.

$expdp maximsg/a dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=users

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "MAXIMSG"."SYS_EXPORT_TABLESPACE_01": maximsg/******** dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=usersEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20.12 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "CR_2"."O_CDR_TEST" 310.9 KB 2166 rows. . exported "CR_2"."O_CDR" 484.4 KB 2606 rows. . exported "CR_2"."USER_IP" 126 KB 3403 rows. . exported "CR_2"."O_CDR_TEMP" 33.72 KB 361 rows. . exported "CR_2"."USERS" 42.57 KB 230 rows. . exported "SCOTT"."DEPT" 5.656 KB 4 rows. . exported "SCOTT"."EMP" 7.851 KB 15 rows. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************Dump file set for MAXIMSG.SYS_EXPORT_TABLESPACE_01 is:D:\PART1.DMPE:\PART2.DMPC:\REST01.DMPC:\REST02.DMPC:\REST03.DMPJob "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully completed at 12:16:33For dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp,first in D: drive part1.dmp will be created with sized 300K.Then in E: drive part2.dmp will be created with sized 300K.Then all remaining parts of the dump will be created in C: drive each with 300K (possibly except last one due to dump size is not multiple of 300K) and their name will be like rest01.dmp, rest02.dmp etc.

Diferencia Entre DDL, DML and DCL commands?

1)Query-select

2)DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

3)DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

4)DCL - Data Control Language. Some examples:

* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

5)TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

6)System Control Statements
These statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.

7)Embedded SQL StatementsThese statements used in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.

Updating a table based on another table

Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');

SQL> select * from table_1;
ID CODE---------- --------------------
1 First Row

SQL> select * from table_2;
ID CODE---------- --------------------
2 Second Row


Method 01:

SQL> update table_1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t1.id=t2.id) where table_1.id in(select id from table_2);
1 row updated.

SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row

Method 02:

SQL> update table_1 t1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t2.id=t1.id) where exists (select t2.code from table_2 t2 where t1.id=t2.id);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row


Method 03:

adding an unique constraint in table_2.
SQL> alter table table_2 add constraint table_2_UK UNIQUE (id);
Table altered.

SQL> update (select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id) set col1=col2;1 row updated.

SQL> select * from table_1;
ID CODE}
---------- --------------------
1 First Row
2 Second Row

Instalacion Oracle 11g Solaris 64 Bits

At least 1 GB of RAM

# /usr/sbin/prtconf grep "Memory size"

The following table describes the relationship between installed RAM and the configured swap
space requirement:

RAM Swap Space
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

To determine the size of the configured swap space, enter the following command:# /usr/sbin/swap -s

To determine whether the system architecture can run the software, enter the following command:# /bin/isainfo -kv

Between 225 and 275 MB of disk space in the /tmp directory

To determine the amount of disk space available in the /tmp directory, enter the following command:

# df -h /tmp

To determine the distribution and version of Solaris installed, enter the following command:

uname -r

The following packages (or later versions) must be installed:SUNWarc
SUNWbtool
SUNWhea
SUNWlibC
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWi1of
SUNWi1cs
SUNWi15cs
SUNWxwfnt
SUNWsprox

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \ SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

Validar si existe el grupo

grep dba /etc/group

root@afrodita # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba),102(oper)

root@afrodita # /usr/sbin/useradd -g oinstall -G dba oracle11gUX: /usr/sbin/useradd: ERROR: oracle11g is already in use. Choose another.
root@afrodita # id -a oracle11guid=101(oracle11g) gid=100(oinstall) groups=101(dba)root@afrodita # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba),102(oper)


# /usr/sbin/usermod -d /export/home/oracle11g -m oracle11g

Cambiar el Password Oracle11g

passwd -r files oracle11g


6.1 Configuring Kernel Parameters On Solaris 9

Parameter Recommended Value
noexec_user_stack
1
semsys:seminfo_semmni
100
semsys:seminfo_semmns
1024
semsys:seminfo_semmsl
256
semsys:seminfo_semvmx
32767
shmsys:shminfo_shmmax
4294967296
shmsys:shminfo_shmmni
100


# cp /etc/system /etc/system.orig

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967296
set shmsys:shminfo_shmmni=100

Enter the following command to restart the system:

# /usr/sbin/reboot


# df -k

A single file system with at least 1.2 GB of free disk space

To create the required directories and specify the correct owner, group, and permissions for them:

Creacion de Directorios

mkdir -p /u02/app/oracle

chown -R oracle11g:oinstall /u02/app/oracle

chmod -R 775 /u02/app/oracle

Oracle Administración de Logs

SQL> show parameter audit_file_dest;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest string /u01/app/oracle/admin/BDDWSGRD /adump

SQL> show parameter background_dump_dest;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------background_dump_dest string /u01/app/oracle/admin/BDDWSGRD /bdump

SQL> show parameter core_dump_dest;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------core_dump_dest string /u01/app/oracle/admin/BDDWSGRD /cdump

SQL> show parameter user_dump_dest;
NAME TYPE VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /u01/app/oracle/admin/BDDWSGRD /udump