Overview of Oracle Automatic Storage Management

Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.

Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit. Within a disk group, Oracle ASM exposes a file system interface for Oracle database files. The content of files that are stored in a disk group is evenly distributed to eliminate hot spots and to provide uniform performance across the disks. The performance is comparable to the performance of raw devices.

You can add or remove disks from a disk group while a database continues to access files from the disk group. When you add or remove disks from a disk group, Oracle ASM automatically redistributes the file contents and eliminates the need for downtime when redistributing the content.

Oracle ASM also uses the Oracle Managed Files (OMF) feature to simplify database file management. OMF automatically creates files in designated locations. OMF also names files and removes them while relinquishing space when tablespaces or files are deleted.

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle ASM functionality to support customer files maintained outside of Oracle Database.

About Oracle ASM Instances

An Oracle ASM instance is built on the same technology as an Oracle Database instance. An Oracle ASM instance has a System Global Area (SGA) and background processes that are similar to those of Oracle Database. However, because Oracle ASM performs fewer tasks than a database, an Oracle ASM SGA is much smaller than a database SGA. In addition, Oracle ASM has a minimal performance effect on a server. Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances;

Oracle ASM is installed in the Oracle Grid Infrastructure home before Oracle Database is installed in a separate Oracle home. Oracle ASM and database instances require shared access to the disks in a disk group. Oracle ASM instances manage the metadata of the disk group and provide file layout information to the database instances.

Oracle ASM for Single-Instance Oracle Databases

About Mirroring and Failure Groups

Mirroring protects data integrity by storing copies of data on multiple disks. When you create a disk group, you specify an Oracle ASM disk group type based on one of the following three redundancy levels:

·         Normal for 2-way mirroring

·         High for 3-way mirroring

·         External to not use Oracle ASM mirroring, such as when you configure hardware RAID for redundancy

The redundancy level controls how many disk failures are tolerated without dismounting the disk group or losing data. The disk group type determines the mirroring levels with which Oracle creates files in a disk group.

About Oracle ASM Disks

Oracle ASM disks are the storage devices that are provisioned to Oracle ASM disk groups. Examples of Oracle ASM disks include:

·         A disk or partition from a storage array

·         An entire disk or the partitions of a disk

·         Logical volumes

·         Network-attached files (NFS)

When you add a disk to a disk group, you can assign an Oracle ASM disk name or Oracle ASM assigns the Oracle ASM disk name automatically. This name is different from the path name used by the operating system. In a cluster, a disk may be assigned different operating system device names on different nodes, but the disk has the same Oracle ASM disk name on all of the nodes. In a cluster, an Oracle ASM disk must be accessible from all of the instances that share the disk group.
Oracle ASM spreads the files proportionally across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because Oracle ASM load balances among all of the disks in a disk group, different Oracle ASM disks should not share the same physical drive.

Allocation Units

Every Oracle ASM disk is divided into allocation units (AU). An allocation unit is the fundamental unit of allocation within a disk group. A file extent consists of one or more allocation units. An Oracle ASM file consists of one or more file extents.
When you create a disk group, you can set the Oracle ASM allocation unit size with the AU_SIZE disk group attribute. The values can be 1, 2, 4, 8, 16, 32, or 64 MB, depending on the specific disk group compatibility level. Larger AU sizes typically provide performance advantages for data warehouse applications that use large sequential reads.

About Oracle ASM Files

Files that are stored in Oracle ASM disk groups are called Oracle ASM files. Each Oracle ASM file is contained within a single Oracle ASM disk group. Oracle Database communicates with Oracle ASM in terms of files. This is similar to the way Oracle Database uses files on any file system. You can store the various file types in Oracle ASM disk groups, including:

·         Control files

·         Data files, temporary data files, and data file copies

·         SPFILEs

·         Online redo logs, archive logs, and Flashback logs

·         RMAN backups

·         Disaster recovery configurations

·         Change tracking bitmaps

·         Data Pump dumpsets

Oracle ASM automatically generates Oracle ASM file names as part of file creation and tablespace creation. Oracle ASM file names begin with a plus sign (+) followed by a disk group name. You can specify user-friendly aliases for Oracle ASM files and create a hierarchical directory structure for the aliases.

Extents

The contents of Oracle ASM files are stored in a disk group as a set, or collection, of extents that are stored on individual disks within disk groups. Each extent resides on an individual disk. Extents consist of one or more allocation units (AU). To accommodate increasingly larger files, Oracle ASM uses variable size extents.
Variable size extents enable support for larger Oracle ASM data files, reduce SGA memory requirements for very large databases, and improve performance for file create and open operations. The initial extent size equals the disk group allocation unit size and it increases by a factor of 4 or 16 at predefined thresholds. This feature is automatic for newly created and resized data files when specific disk group compatibility attributes are set to 11.1 or higher.

The extent size of a file varies as follows:

·        Extent size always equals the disk group AU size for the first 20000 extent sets (0 - 19999).

·        Extent size equals 4*AU size for the next 20000 extent sets (20000 - 39999).

·        Extent size equals 16*AU size for the next 20000 and higher extent sets (40000+).

Figure 1-4 shows the Oracle ASM file extent relationship with allocation units. The first eight extents (0 to 7) are distributed on four Oracle ASM disks and are equal to the AU size. After the first 20000 extent sets, the extent size becomes 4*AU for the next 20000 extent sets (20000 - 39999). This is shown as bold rectangles labeled with the extent set numbers 20000 to 20007, and so on. The next increment for an Oracle ASM extent is 16*AU (not shown in Figure 1-4).

Oracle ASM File Allocation in a Disk Group

Oracle ASM Striping

Oracle ASM striping has two primary purposes:

·         To balance loads across all of the disks in a disk group

·         To reduce I/O latency

Coarse-grained striping provides load balancing for disk groups while fine-grained striping reduces latency for certain file types by spreading the load more widely.
To stripe data, Oracle ASM separates files into stripes and spreads data evenly across all of the disks in a disk group. The fine-grained stripe size always equals 128 KB in any configuration; this provides lower I/O latency for small I/O operations. The coarse-grained stripe size is always equal to the AU size (not the data extent size).

expdp dblinks and impdp dblinks

Exporting DBlinks:


We use include parameter include=db_link to export database links from database. below is the example.


expdp \'/as sysdba\' full=y directory=dblink_dir include=db_link dumpfile=orcl_DBlink.dmp logfile=orcl_DBlink.log

For importing, we can import as below.

impdp \'/as sysdba\' full=y directory= dblink_dir dumpfile=FSPRD_DBlink.dmp logfile=FSPRD_DBlink_imp.log

or

impdp \'/as sysdba\' include=db_link directory= dblink_dir dumpfile=FSPRD_DBlink.dmp logfile=FSPRD_DBlink_imp.log

Oracle Data Pump:

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions.

Roles:

Many Data Pump Export and Import operations require the user to have the DATAPUMP_EXP_FULL_DATABASE role and/or the DATAPUMP_IMP_FULL_DATABASE role.The DATAPUMP_EXP_FULL_DATABASE role affects only export operations. The DATAPUMP_IMP_FULL_DATABASE role affects import operations and operations that use the Import SQLFILE parameter.

Key Features:

Fast Performance:

Operations performed with the new Data Pump Export and Import utilities are typically much faster than operations performed with the original Export and Import utilities. With Data Pump Export, when the direct path method of unloading is used, a single stream of data unload is about two times faster than original Export. This is because the direct path API has been modified to be even more efficient than before. Depending on the level of parallelism, the performance improvement can be even greater.

With Data Pump Import, a single stream of data load is about 15-45 times faster than original Import. This is because original Import uses only conventional mode inserts, whereas Data Pump Import uses the direct path method of loading. As with Export, the performance improvement can be even greater depending on the level of parallelism.

Improved Management Restart:

Every Data Pump operation has a master table that is created in the schema of the user running a Data Pump job. The master table maintains information about all aspects of the job, such as the current state of every object exported or imported and its location in the dump file set. In the event of a planned or unplanned job stoppage, Data Pump knows which objects were currently being worked on and whether or not they completed successfully. Therefore, all stopped Data Pump jobs can be restarted without loss of data as long as the master table and dump file set remain undisturbed while the job is stopped.

Object Selection:

A Data Pump job can exclude or include virtually any type of object and any subset of objects within a type. The following client parameters are used:

    The EXCLUDE parameter filters the metadata that is exported and imported by specifying objects and object types to be excluded from the current operation. An optional name qualifier can be used for finer selectivity within each object type specified.

    The INCLUDE parameter filters the metadata that is exported and imported by specifying objects and object types to be included for the current operation. An optional name qualifier can be used for finer selectivity within each object type specified.

    The CONTENT parameter specifies what is exported or imported: metadata only, data only, or both.

    The QUERY parameter filters data by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.

Monitoring and Estimating Capability:

In addition to the standard progress and error messages printed by the client and to the log file, the new interactive STATUS command displays cumulative status of the job, along with a description of the current operation. An estimated completion percentage for the job is also returned. The user can also specify a time period, in seconds, for an automatic status update at specific intervals.

Because more than one client can be attached to a running job, the user can start a job at work, detach from it, go home, reattach to the job at home, and monitor it throughout the evening.

The start of every export job now includes an estimate phase in which the approximate amount of all data to be unloaded is determined. This allows the user to allocate a sufficient amount of disk space for the dump file set.

Network Mode:

Data Pump Export and Import both support a network mode in which the job's source is a remote Oracle instance. When you perform an import over the network, there are no dump files involved because the source is another database, not a dump file set.

When you perform an export over the network, the source can be a read-only database on another system. Dump files are written out on the local system just as they are with a local (non-networked) export.

Default Locations for Dump, Log, and SQL Files

Because Data Pump is server-based, rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory path on the file system.

For example, the following SQL statement creates a directory object named dpump_dir1 that is mapped to a directory located at /u01/app/dumpfiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/u01/app/dumpfiles';

The reason that a directory object is required is to ensure data security and integrity. For example:

If you were allowed to specify a directory path location for an input file, you might be able to read data that the server has access to, but to which you should not.

If you were allowed to specify a directory path location for an output file, the server might overwrite a  file that you might not normally have privileges to delete.

On Unix and Windows NT systems, a default directory object, DATA_PUMP_DIR, is created at database creation or whenever the database dictionary is upgraded. By default, it is available only to privileged users.

If you are not a privileged user, before you can run Data Pump Export or Data Pump Import, a directory object must be created by a database administrator (DBA) or by any user with the CREATE ANY DIRECTORY privilege.

After a directory is created, the user creating the directory object needs to grant READ or WRITE permission on the directory to other users. For example, to allow the Oracle database to read and write files on behalf of user hr in the directory named by dpump_dir1, the DBA must execute the following command:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO ramesh;

Note that READ or WRITE permission to a directory object only means that the Oracle database will read or write that file on your behalf. You are not given direct access to those files outside of the Oracle database unless you have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories.

Original Export and Import versus Data Pump Export and Import

If you are familiar with the original Export (exp) and Import (imp) utilities, it is important to understand that many of the concepts behind them do not apply to Data Pump Export (expdp) and Data Pump Import (impdp). In particular:

Data Pump Export and Import operate on a group of files called a dump file set rather than on a single sequential dump file.

Data Pump Export and Import access files on the server rather than on the client. This results in improved performance. It also means that directory objects are required when you specify file locations.

Data Pump Export and Import use parallel execution rather than a single stream of execution, for improved performance. This means that the order of data within dump file sets and the information in the log files is more variable.

Data Pump Export and Import represent metadata in the dump file set as XML documents rather than as DDL commands. This provides improved flexibility for transforming the metadata at import time.

Data Pump Export and Import are self-tuning utilities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.

At import time there is no option to perform interim commits during the restoration of a partition. This was provided by the COMMIT parameter in original Import.

There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.

Sequential media, such as tapes and pipes, are not supported.

The Data Pump method for moving data between different database versions is different than the method used by original Export/Import. With original Export, you had to run an older version of Export (exp) to produce a dump file that was compatible with an older database version. With Data Pump, you can use the current Export (expdp) version and simply use the VERSION parameter to specify the target database version.

When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.

Data Pump Export and Import consume more undo tablespace than original Export and Import. This is due to additional metadata queries during export and some relatively long-running master table queries during import. As a result, for databases with large amounts of metadata, you may receive an ORA-01555: snapshot too old error. To avoid this, consider adding additional undo tablespace or increasing the value of the UNDO_RETENTION initialization parameter for the database.

If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.

Examples:

--------------

Export Full Database Mode

Only users with the DBA role or the DATAPUMP_EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the filedba.dmp.

Scenario:

Connect to database and create user.

Sql>create user ramesh identified by ramesh;

User created

SQL> grant connect,resource to ramesh;

Grant succeeded.

SQL>create directory dump as ‘/u01/app/backup’;

“NOTE: Check the directory exists or not physically in OS level. In not there need to create.

$mkdir /u01/app/backup”

SQL>grant read,write on directory dump to system;

SQL> conn ramesh

Enter password:

Connected.

And create some tables in the user, I created two tables as bellow

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TEST                           TABLE

TEST_TBL                       TABLE

Sql>conn sys as sysdba

Enter password:

connected

Sql>create user siva identified by siva;

User created

SQL> grant connect,resource to siva;

Grant succeeded.

SQL> conn siva

Enter password:

Connected.

Create table in this user also.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

EMPLOYEE                       TABLE

Now export full database by using full=y parameter.

$expdp system/xxxxx directory=dump dumpfile=full_db.dmp logfile=full_db.log full=y

After completing the export, drop the user.

SQL> drop user ramesh cascade;

User dropped.

SQL> drop user siva cascade;

User dropped.

Check the user existence in database;

SQL> select username from dba_users where username in ('RAMESH',’SIVA’);

no rows selected

Now import the exported dumpfile to restore the dropped user using ignore=y.

$impdp system/xxxxxx directory=dump dumpfile=full_database.dmp logfile=full_db.log table_exists_action=append full=y

Now export data imported, now check the user in the database.

SQL> select username from dba_users where username IN ('RAMESH',’SIVA’);

USERNAME

------------------------------

RAMESH

SIVA

SQL> conn ramesh

Enter password:

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TEST                           TABLE

TEST_TBL                       TABLE

User Mode

For exporting user owner parameter will be used.

Scenario:

Export the user using owner parameter

$expdp system/xxxxx directory=dump dumpfile=ramesh_data.dmp logfile=ramesh_data.log schemas=ramesh

Now drop the user in database

SQL>drop user ramesh cascade;

User dropped.

Now check the user existence.

SQL>select username from dba_users where username=’RAMESH’;

no rows selected

Now try to import the dumpfile into database, so that dropped user will come back.

[oracle@localhost ~]$ impdp system/xxxxx directory=dump dumpfile=ramesh_data.dmp logfile=ramesh.log;

Import is done

SQL> select username from dba_users where username='RAMESH';

USERNAME

------------------------------

RAMESH

SQL> select object_name,object_type from dba_objects where owner='RAMESH';

OBJECT_NAME          OBJECT_TYPE

-------------------- --------------------

TEST_TBL             TABLE

TEST                 TABLE

 Exporting table:

To export a table we use tables parameter in expdp utility.

In this example we are taking backup of the table test_tbl belongs to ramesh schema to the dump file tbl_exp.dmp.

[oracle@localhost ~]$ expdp system/xxxxx directory=dump dumpfile=tbl_exp.dmp logfile=tbl_exp.log tables=ramesh.test_tbl;

Export: Release 11.2.0.1.0 - Production on Thu Jan 8 07:44:48 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

EXP-00056: ORACLE error 28002 encountered

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Current user changed to RAMESH

. . exporting table                       TEST_TBL          5 rows exported

Export terminated successfully without warnings.

Now import the exported table into another user siva. To do this, we need to user remapschema parameters as bellow.

[oracle@localhost ~]$ impdp system/xxxxx directory=dump dumpfile=tbl_exp.dmp logfile=tbl_imp.log remap_schema=ramesh:siva tables=ramesh.test_tbl;

Parameter File Method

$expdp SYSTEM/password PARFILE=params.par

The params.dat file contains the following information:

Userid=’system/xxxxx’

dumpfile=/u01/app/full_db.dmp

logfile=/u01/app/full_db.log

FULL=y

=========

Upgrade database from 11.2.0.3 to 11.2.0.4
------------------------------------------------------

Step#1:
Take database backup running with 11.2.0.3 version( Preferably you can take zip of scheduled backups to save time )

Step#2:

download 11.2.0.4 from below link:

Install Oracle Database version 11.2.0.4 in New home path /u01/app/oracle/product/11.2.0.4/dbhome_1

https://support.oracle.com/epmos/faces/PatchDetail?_adf.ctrl-state=944tiroeg_29&patch_name=13390677&releaseId=80112030&patchId=13390677&languageId=0&platformId=226&_afrLoop=113795957239081



Install Oracle Database version 11.2.0.4 in New home path /u01/app/oracle/product/11.2.0.4/dbhome_1


Step#3:
*********

Before upgrade run the preupgrade tool to check the pre-requisites before proceeding upgrade which is available in new home.
Fix as per output result.
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql

SQL> col version for a10
SQL> col comp_name for a40
SQL> col comp_id for a20
SQL> set lines 1000
SQL> select comp_id,COMP_NAME,version, status from dba_registry;

COMP_ID              COMP_NAME                                VERSION    STATUS
-------------------- ---------------------------------------- ---------- ---------------------------------
CATALOG              Oracle Database Catalog Views            11.2.0.3.0 VALID
CATPROC              Oracle Database Packages and Types       11.2.0.3.0 VALID

SQL> PURGE DBA_RECYCLEBIN ;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
SQL> set feedback on
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' ;


SQL> col name for a20
SQL> col TIME for a30
SQL> col RESTORE_POINT_TIME for a30
SQL> select * from v$restore_point;
SQL>  select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE TIME                           RESTORE_POINT_TIME             PRESERVED NAME
---------- --------------------- --------- ------------ ------------------------------ ------------------------------ --------- --------------------
 709791434                     2 YES          314572800 01-NOV-15 05.50.43.000000000 A                                YES       B4_DB_UPGRADE

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14

Step#4:
********
Shutdown database and setting new environment variable (11.2.0.4) home


SQL> select name from v$database;

NAME
---------------------------
HCDMP

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !echo $ORACLE_SID
HCDMP

SQL> echo $ORACLE_HOME
SQL> !echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.3/dbhome_1

cp /u01/app/oracle/product/11.2.0.3/dbhome_1/initorcl.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/initorcl.ora

[oracle@s616183se2vl25 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@s616183se2vl25 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@s616183se2vl25 ~]$ export ORACLE_SID=orcl

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             683672704 bytes
Database Buffers          348127232 bytes
Redo Buffers                9826304 bytes
Database mounted.
Database opened.


SQL> @?/rdbms/admin/catupgrd


Step#5:
*******
Startup the upgraded database and follow post upgrade task.

[oracle@s616183se2vl25 ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 1 06:15:58 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             683672704 bytes
Database Buffers          348127232 bytes
Redo Buffers                9826304 bytes
Database mounted.
Database opened.
SQL>  @?/rdbms/admin/utlu112s

Step#6:
*******
For migrating the baseline from pre 11g database, run the below one if required.

SQL>  @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catuppst.sql


Step#7:
*******
Recompile objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step#8:
*******

For object invalidation in sys schema before upgrade the run below one.
Also run catbundle

SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql

SQL> @?/rdbms/admin/catbundle.sql psu apply

Step#9:
*******
Verify the details

SQL> set lines 1000
SQL> col action_name for a30
SQL> col ACTION_TIME for a30
SQL> col version for a30
SQL>  col NAMESPACE for a10
SQL> col comments for a30
SQL> col BUNDLE_SERIES for a30
SQL> col action for a20
SQL> select * from registry$history;

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


SQL> set serverout on
SQL> select object_name from dba_objects where status != 'VALID';

no rows selected