Guenadi N Jilevski's Oracle BLOG


Transportable database and transportable tablespace(s) from backup, Tablespace point in time recovery TSPITR

Transportable database and transportable tablespace(s) from backup, Tablespace point in time recovery TSPITR

Transportable tablespace feature introduced since Oracle 10g allowed a tablespace placed in a read only mode to be transported to another database and plugged in. However system and sysaux tablespaces were not transportable. Transportable database feature allows a database to be transported from one platform to another if the following conditions are met.

  • Both databases are within the list of platforms in v$trasportable_platform
  • Both platforms are with the same byte ordering. This further restricts the list of platforms.

Using the transportable database feature Oracle creates copies of the datafiles and recreates the control file, redo log files and creates the temporary tablespace file.

Transportable tablespace from backup avoids placing a tablespace in read only mode prior to the transportation and uses a backup for this purpose. TSPIRT enable us to recover a tablespace to a point in time in the past. TPSPITR is similar to the transportable tablespace from backup. In both cases an auxiliary instance is created. Using the previous target database backup an auxiliary database instance is created and restored to an auxiliary location and recovered to a point in time in the past. The auxiliary database is opened with reset logs. The tablespace set is exported with transportable tablespace mode and the files are copied to the location in case of transportable tablespace from a backup. In case of a TSPITR the tablespaces from the Auxiliary instance are plugged into the target database. In both cases the auxiliary instance is destroyed automatically upon successful completion.

The article will look at

  • Transportable database.
  • Transportable Tablespace from backup
  • Tablespace point in time Recovery (TSPITR)
    Continue reading

February 12, 2011 Posted by | oracle | 1 Comment

Oracle Virtualization – Installing Oracle VM Server 2.2.1, Oracle VM Manager 2.2.0 and Deploying Oracle RAC 11gR2 ( Oracle VM templates Linux x86 64 bit for test configuration

Oracle Virtualization – Installing Oracle VM Server 2.2.1, Oracle VM Manager 2.2.0 and Deploying Oracle RAC 11gR2 ( Oracle VM templates Linux x86 64 bit for test configuration

Oracle provides virtualization software ranging from bare metal implementations based on Oracle VM Server to a Microsoft Windows/Linux/Mac OS and Solaris deployments based on Oracle Virtual Box. Oracle VM Manager facilitates management of Oracle VM Server. In this article we will look at the installation of Oracle VM server and Oracle VM Manager on Oracle VirtualBox and will deploy Oracle RAC 11gR2 ( Oracle VM templates Linux x86 64 bit for test configuration using Oracle VM Manager. All software can be downloaded from and the templates can be downloaded as a patch from MOS (patch 10113572). The approach described in the article is applicable on any supported platform although it is using Oracle VirtualBox for testing.

Software versions used are:

  • Oracle VirtualBox 4.0.0
  • Oracle VM Server 2.2.1
  • Oracle VM Manager 2.2.0
  • OEL 5.4

The article will cover the following:

  • VirtualBox configuration for VMServer machine and VMManager machine
  • Installation of Oracle VM Server
  • Installation of Oracle VM Manager
  • Deployment of Oracle RAC 11gR2 Oracle VM templates Linux x86 64bit for test configuration

In real life Oracle VM server runs on a dedicated set of servers and is installed as a bare metal product without the need of an Operating system. Oracle VM Manager is installed on a Linux box. Oracle VirtualBox can be successfully used for testing and self-study of the new features.

Continue reading

February 11, 2011 Posted by | oracle | 38 Comments

Using DBUA for upgrade to

Using DBUA for upgrade to

This is a brief post, originating from an earlier request, related to using DBUA for database upgrade to Screenshots in the posts are on Windows platform but the concept is similar on Linux/Unix.

Start DBUA. Press Next.

Select the database to upgrade. Make sure that the database version supports direct upgrade. Here I have, and and all of them maintain direct upgrade. For details see (Oracle 11gR1 Upgrade Companion [ID 601807.1], 10g Upgrade Companion [ID 466181.1] and Oracle 11gR2 Upgrade Companion [ID 785351.1]). Here 11gR1 database is selected. If a direct upgrade is possible dbua is doing all the work for us and the screens are similar regardless of the database version as far as a direct upgrade is supported.

Accept the Warning. It is related to the Ultra Search de-support in 11gR2and the lack of ability to downgrade the OEM Database Control after the upgrade.

Select the degree of parallelism to be used for recompilation of invalid objects (equivalent of running the utlrp.sql), upgrade of the time zone and the option to backup the database.

Select the option to move the data files as part of the upgrade. Select a file system as a target location and press Next.

Review the Upgrade summary and press Finish for the upgrade to start.

The ordered list of actions and their progress appears on the screen. Wait until completion.

When 100% of the work is done press OK to see the results.

Here is a summary of the performed upgrade. Press Close to exit.

February 9, 2011 Posted by | oracle | 10 Comments

Example of VPD implementation in Oracle 10g/11g

Example of VPD implementation in Oracle 10g/11g

In the article we will have a look at the concepts and basic example of VPD implementation. VPD allows implementation of Fine Grained Access Control (FGAC) on tables and views. Idea behind FGAC is implementing a mechanism of row level access control for a user session as opposed to an access granted by Oracle roles and privileges. With FGAC a user access to a table or view can be limited only to rows that associate to particular attributes if the user accesses certain columns. The application contest and VPD policy are used. Application context is used to set the attributes to a user. VPD policy uses a policy function associated with the table returning a predicate depending on the attributes set with the application context restricting the user access to table. The VPD policy also specifies the security relevant columns that while accessed enforce the policy. Depending on the user attributes, unless the user is sys or with exempt access policy privilege, RDBMS automatically re-writes each SQL adding a predicate returned by the policy function.

In the example three users will be created. After that an application context and the corresponding package setting the user attributes will be created. In the example the procedure implementing the context will set the attribute to the user name that is used after verification against the employees table. After logon trigger will set the attribute on user logon. A security function will be associated with the policy defined on the employees table. The security function will return a predicate restricting table access only to rows for the particular employee_id as defined by the attribute of the application context.

The hr.employees table will be used and the three users will have their attributes set by the application context procedure at logon as determined by the tables’ data. The detailed steps follow.

Continue reading

February 1, 2011 Posted by | oracle | 2 Comments