How to check which PSU is installed…if any

Oracle PSUs (Patch Set Updates) are referenced by their 5-place version number.  Unfortunately they do not change version numbers in the Oracle binaries, product banners and such though (see MOS 861152.1), so here’s how to identify which PSU your ORACLE_HOME is at…

Database Server:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE'
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| egrep -i 'DATABASE PSU|DATABASE PATCH SET UPDATE'

(The first command above being for Linux)

…or using the following SQL:

select comments, version, bundle_series
from sys.registry$history
where bundle_series = 'PSU'
order by action_time;

COMMENTS                       VERSION            BUNDLE_SERIES
------------------------------ ------------------ -----------------
Patchset 11.2.0.2.0            11.2.0.3           PSU
PSU 11.2.0.3.5                 11.2.0.3           PSU

The above view is populated when catbundle.sql is executed.  If the query above ends with “ORA-00904: “BUNDLE_SERIES”: invalid identifier” then no bundle patch (PSU or CPU) has been applied.

Grid Infrastructure:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'GI PSU'

Cluster Ready Services:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'TRACKING BUG' | grep -i 'PSU'

Enterprise Manager Agent:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

Enterprise Manager OMS:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'ENTERPRISE MANAGER OMS' | grep -i 'PSU'

WebLogic Server:

. $WLS_HOME/server/bin/setWLSEnv.sh
java weblogic.version|grep PSU
Advertisements

Producing RSS from PL/SQL

First things first. The idea and the bulk of the code for this post are not mine, they are Sean Dillon’s. It’s a very cool idea that he came up with and it still works flawlessly

I needed to incorporate RSS into a project I’m working, so I grabbed Sean’s code. The problem is, it’s based on the AskTom table structure which means it won’t run on your database without immediately re-writing the query. Additionally, the nature of this code relies on a pretty lengthy query to generate the XML. I’ll admit, when I first looked at it, I thought “Wow, this is going to be more complex than I thought.” After looking at it for a little while longer, I realized it was actually very simple. Sean also included support for several versions of RSS, improving the functionality, but again, adding to the complexity.

So, I created an example table and simplified the code as much as possible to make it easier for everyone to understand. The table, “PLSQL_PACKAGES”, stores information about some of the built-in PL/SQL packages I use on a regular basis. The links in this table point back to the online Oracle Documentation.

This block of code is just the DDL for the sample table and the insert statements to populate it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
create table plsql_packages(
    id          varchar2(32),
    title       varchar2(255),
    description varchar2(4000),
    link        varchar2(1000),
    updated_by  varchar2(100),
    updated_on  date)
/
create or replace trigger  biu_plsql_packages before insert or
update on plsql_packages
for each row
begin
    if inserting then
        :new.id := sys_guid();
    end if;
        :new.updated_by := nvl(v('APP_USER'),user);
        :new.updated_on := sysdate;
end;
/
insert into plsql_packages(title,description,link)
     values ('DBMS_CRYPTO','DBMS_CRYPTO provides an interface
to encrypt and decrypt stored data, and can be used in
conjunction with PL/SQL programs running network
communications. It provides support for several
industry-standard encryption and hashing algorithms,
including the Advanced Encryption Standard (AES)
encryption algorithm. AES has been approved by the National
Institute of Standards and Technology (NIST) to replace
/
insert into plsql_packages(title,description,link)
     values ('DBMS_EPG','The DBMS_EPG package implements
the embedded PL/SQL gateway that enables a web browser to
invoke a PL/SQL stored procedure through an HTTP listener.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_epg.htm#sthref3481')
/
insert into plsql_packages(title,description,link)
     values (' OWA_UTIL','The OWA_UTIL package contains
utility subprograms for performing operations such as
getting the value of CGI environment variables, printing
the data that is returned to the client, and printing the
/
insert into plsql_packages(title,description,link)
     values ('UTL_MAIL','The UTL_MAIL package is a utility
for managing email which includes commonly used email
features, such as attachments, CC, BCC, and return receipt.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258')
/

This is the important block of code as it creates the RSS procedure. Don’t be intimidated by it though as you only need to modify a few lines. The only lines you need to customize to make it work against your own table are 4-7 and 41-43!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
create or replace procedure rss
is
    -- customizable parameters
    l_title         varchar2(255) := 'Oracle PL/SQL Packages';
    l_link          varchar2(255) := 'http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm';
    l_description   varchar2(255) := 'This is a feed of changes to PL/SQL Package Documentation';
    l_language      varchar2(255) := 'en-us';
    -- end customizable parameters
    l_version       varchar2(10)  := '2.0';
    l_clob          clob;
    l_idx           pls_integer := 1;
    l_len           pls_integer := 255;
    l_defrows       pls_integer := 10;
    l_maxrows       pls_integer := 30;
    l_desclen       pls_integer := 250;
begin
    for i in (
      select xmlelement( "rss",
               -- Begin XML Header Block
               xmlattributes( l_version as "version"),
                 xmlelement( "channel",
                   xmlforest( l_title as "title",
                              l_link as "link",
                              l_description as "description",
                              l_language as "language"),
                 -- End XML Header Block
                 -- Begin List of Individual Articles or
                 -- Items
                 xmlagg(
                     xmlelement( "item",
                       xmlelement("title", x.title),
                       xmlelement("link", x.link),
                       xmlelement("description",
                       x.description),
                       xmlelement("pubDate",
                       to_char(x.updated_on,'Dy, DD Mon RRRR hh24:mi:ss')),
                       xmlelement("guid", XMLATTRIBUTES
                       ('false' as "isPermaLink"),x.id||
                       to_char(x.updated_on,'JHH24MISS'))
                     )
                   )
                   -- End List of Individual Articles or
Items
                 )
             ) as result
        from -- Actual Database Query that
--populates the list of Items
                select id,title,link,description,
                updated_on
                  from plsql_packages
                 where rownum < (l_maxrows+1)) x)
    loop
        l_clob := xmltype.extract(i.result,'/').getclobval;
        exit;
    end loop; --i
    --- OUTPUT RESULTS
    owa_util.mime_header('application/xml', false);
    owa_util.http_header_close;
    for i in 1..ceil(dbms_lob.getlength(l_clob)/l_len) loop
        htp.prn(substr(l_clob,l_idx,l_len));
        l_idx := l_idx + l_len;
    end loop; --i
end rss;
/

Note on line 34 the “guid” element. This is an optional element (documented here) that an aggregator can use to uniquely identify the item. I’m concatenating the ID column from the table with Julian date concatenated with hours, minutes, and seconds – to_char(sysdate,‘JHH24MISS’). This means that when you update a row, the date will change causing your aggregator to see a new guid and display a new item for the changed row.The easiest way to test this procedure is using the “OWA Output” tab in SQL Developer:SQL Developer OWA Ouput

If you’re running XE or 11g and you want to call this procedure directly through the APEX DAD, you’ll need to edit the FLOWS_XXXXXX.wwv_flow_epg_include_mod_local function and comment-out the first line as well as your procedure to the IN list.

 

 

Using Puppet to Manage Oracle

This module contains custom types that can help you manage DBA objects in an Oracle database. It runs afterthe database is installed. IT DOESN’T INSTALL the Oracle database software. With this module, you can setup a database to receive an application. You can:

  • create a tablespace
  • create a user with the required grants and quota’s
  • create one or more roles
  • create one or more services

Setup

What oracle affects

The types in this module will change settings inside a Oracle Database. No changes are made outside of the database. Take the code from “https://github.com/hajee/oracle&#8221;.

Setup Requirements

To use this module, you need a running Oracle database. I can recommend Edwin Biemonds Puppet OraDb module. The Oracle module itself is based on easy_type.

Beginning with oracle module

After you have a running database, (See Edwin Biemonds Puppet OraDb module), you need to install easy_type, and this module.

1
2
puppet module install hajee/easy_type
puppet module install hajee/oracle

Usage

The module contains the following types:

tablespaceoracle_userrole and listener. Here are a couple of examples on how to use them.

listener

This is the only module that does it’s work outside of the Oracle database. It makes sure the Oracle SQL*Net listener is running.

1
2
3
4
listener {'SID':
  ensure  => running,
  require => Exec['db_install_instance'],
}

The name of the resource MUST be the sid for which you want to start the listener.

Specifying the SID

All types have a name like sid\resource. The sid is optional. If you don’t specify the sid, the type will use the first database instance from the /etc/oratab file. We advise you to use a full name, e.g. an sid and a resource name. This makes the manifest much more resilient for changes in the environment.

oracle_user

This type allows you to manage a user inside an Oracle Database. It recognises most of the options that CREATE USER supports. Besides these options, you can also use this type to manage the grants and the quota’s for this user.

1
2
3
4
5
6
7
8
9
10
oracle_user{sid/user_name:
  temporary_tablespace      => temp,
  default_tablespace        => 'my_app_ts,
  password                  => 'verysecret',
  require                   => Tablespace['my_app_ts'],
  grants                    => ['SELECT ANY TABLE', 'CONNECT', 'CREATE TABLE', 'CREATE TRIGGER'],
  quotas                    => {
                                  "my_app_ts"  => 'unlimited'
                                },
}

tablespace

This type allows you to manage a tablespace inside an Oracle Database. It recognises most of the options that CREATE TABLESPACE supports.

1
2
3
4
5
6
7
8
9
10
11
tablespace {'sid/my_app_ts':
  ensure                    => present,
  datafile                  => 'my_app_ts.dbf',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

You can also create an undo tablespace:

1
2
3
4
tablespace {'sid/my_undots_1':
  ensure                    => present,
  content                   => 'undo',
}

or a temporary taplespace:

1
2
3
4
5
6
7
8
9
10
11
12
tablespace {'sid/my_temp_ts':
  ensure                    => present,
  datafile                  => 'my_temp_ts.dbf',
  content                   => 'temporary',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

role

This type allows you to create or delete a role inside an Oracle Database. It recognises a limit part of the options that CREATE ROLE supports.

1
2
3
role {'sid/just_a_role':
  ensure    => present,
}

oracle_service

This type allows you to create or delete a service inside an Oracle Database.

1
2
3
oracle_service{'sid/my_app_service':
  ensure  => present,
}

init_param

this type allows you to manage your init.ora parameters

1
2
3
4
init_param{'sid/parameter/instance':
  ensure  => present,
  value   => 'the_value'
}

asm_diskgroup

This type allows you to manage your ASM diskgroups. Like the other Oracle types, you must specify the SID. But for this type it must be the ASM sid. Most of the times, this is +ASM1

1
2
3
4
5
6
7
8
9
10
11
asm_diskgroup {'+ASM1/REDO':
  ensure          => 'present',
  redundancy_type => 'normal',
  compat_asm      => '11.2.0.0.0',
  compat_rdbms    => '11.2.0.0.0',
  failgroups      => {
    'CONTROLLER1' => { 'diskname' => 'REDOVOL1', 'path' => 'ORCL:REDOVOL1'},
    'CONTROLLER2' => { 'diskname' => 'REDOVOL2', 'path' => 'ORCL:REDOVOL2'},
  }
}

At this point in time the type support just the creation and the removal of a diskgroup. Modification of diskgroups is not (yet) supported.

oracle_exec

this type allows you run a specific SQL statement or an sql file on a specified instance.

1
2
3
4
oracle_exec{"instance/drop table application_users":
  username => 'app_user',
  password => 'password,'
}

This statement will execute the sql statement drop table application_users on the instance names instance. There is no way the type can check if it has already done this statement, so the developer must support this by using puppet if statements.

1
2
3
4
5
oracle_exec{"instance/@/tmp/do_some_stuff.sql":
  username  => 'app_user',
  password  => 'password,'
  logoutput => on_failure,  # can be true, false or on_failure
}

This statement will run the sqlscript /tmp/do_some_stuff.sql on the instance named instance. Like the single statement variant, there is no way to check if the statement is already done. So the developer must check for this himself.

When you don’t specify the username and the password, the type will connect as sysdba.

oracle_thread

This type allows you to enable a thread. Threads are used in Oracle RAC installations. This type might not be very useful for regular use, but it is used in the Oracle RAC module.

1
2
3
oracle_thread{"instance_name/2":
  ensure  => 'enabled',
}

This enables thread 2 on instance named instance_name

Limitations

This module is tested on Oracle 11 on CentOS and Redhat. It will probably work on other Linux distributions. It will definitely not work on Windows. As far as Oracle compatibility. Most of the sql commands’s it creates under the hood are pretty much Oracle version independent. It should work on most Oracle versions.

Development

This is an open projects, and contributions are welcome.

OS support

Currently we have tested:

  • Oracle 11.2.0.2 & 11.2.0.4
  • CentOS 5.8
  • Redhat 5

It would be great if we could get it working and tested on:

  • Oracle 12
  • Debian
  • Windows
  • Ubuntu
  • ….

Oracle version support

Currently we have tested:

  • Oracle 11.2.0.2
  • Oracle 11.2.0.4

It would be great if we could get it working and tested on:

  • Oracle 12

Managable Oracle objects

Obviously Oracle has many many many more DBA objects that need management. For some of these Puppet would be a big help. It would be great if we could get help getting this module to support all of the objects.

If you have knowledge in these technologies, know how to code, and wish to contribute to this project, we would welcome the help.

Testing

Make sure you have:

  • rake
  • bundler

Install the necessary gems:

1
bundle install

And run the tests from the root of the source code:

1
rake test

Using the dcli Utility

7 Using the dcli Utility

The dcli utility executes commands across a group of servers on Oracle and returns the output.

This chapter contains the following sections:

1 Overview of the dcli Utility

The dcli utility executes commands on multiple Oracle Big Data Appliance servers in parallel, using the InfiniBand (bondib0) interface to make the connections. You can run the utility from any server.

1.1 Setting Up Passwordless SSH

The dcli utility requires a passwordless Secure Shell (SSH) between the local server and all target servers. You run the dcli utility on the local server, and the commands specified in dcli execute on the target servers.

Two scripts facilitate the use of SSH on Oracle Big Data Appliance: setup-root-ssh and remove-root-ssh. These scripts accept two options that are also used by dcli:

  • -C: Targets all the servers in a Hadoop cluster
  • -g: Targets a user-defined set of servers

To set up passwordless SSH for root:

  1. Connect to an Oracle Big Data Appliance server using PuTTY or a similar utility. Select an SSH connection type.
  2. Log in as root.
  3. Set up passwordless SSH for root across the rack:
    setup-root-ssh
    

    Or, to set up passwordless SSH across a Hadoop cluster of multiple racks:

    setup-root-ssh -C
    

    You see the message “ssh key added” from each server.

  4. You can now run any ssh command on any server in the rack without entering a password. In addition to dcli commands, you can use scp to copy files between servers.
  5. To remove passwordless SSH from root:
    remove-root-ssh
    

1.2 Basic Use of dcli

This topic identifies some of the basic options to the dcli command.

1.2.1 Getting Help

To see the dcli help page, issue the dcli command with the -h or --help options. You can see a description of the commands by issuing the dclicommand with no options.

1.2.2 Identifying the Target Servers

You can identify the servers where you want the commands to run either in the command line or in a file. For a list of default target servers, use the -toption. To change the target servers for the current command, use the -c or -g options described in below table.

The /opt/oracle/bda directory contains two files for executing commands on multiple servers:

  • rack-hosts-infiniband is the default target group of servers for the dclisetup-root-ssh, and remove-root-ssh utilities. The file initially contains the default factory IP addresses. The network configuration process changes this file to the custom IP addresses identified in the Oracle Big Data Appliance Configuration Worksheets.
  • cluster-hosts-infiniband contains the names of all servers in the Hadoop cluster created by the Mammoth Utility. A cluster can span one or more Oracle Big Data Appliance racks.

You can manually create additional files with groups of servers that you want to manage together. For example, you might manage servers 5 to 18 together, because they have no special functions like servers 1 to 4.

1.2.3 Specifying the Commands

You typically specify a command for execution on the target servers on the command line. However, you can also create a command file for a series of commands that you often issue together or for commands with complex syntax. See the -x option in below table.

You can also copy files to the target servers without executing them by using the -f option.

1.2.4 Controlling the Output Levels

You can request more information with the -v option or less information with the -n option. You can also limit the number of returned lines with the --maxlines option, or replace matching strings with the -r option.

Following are examples of various output levels using a simple example: the Linux date command.

Note:

The output from only one server (node07) is shown. The syntax in these examples issues the date command on all 18 servers.

This is the default output, which lists the server followed by the output.

# dcli date
bda1node07-adm.example.com: Tue Feb 14 10:22:31 PST 2012

The minimal output returns OK for completed execution:

# dcli -n date
OK: ['bda1node07.example.com']

Verbose output provides extensive information about the settings under which the command ran:

dcli -v dateoptions.nodes: Noneoptions.destfile: Noneoptions.file: 
Noneoptions.group: dcserversoptions.maxLines: 100000options.listNegatives: 
Falseoptions.pushKey: Falseoptions.regexp: Noneoptions.sshOptions: 
Noneoptions.scpOptions: Noneoptions.dropKey: Falseoptions.serializeOps:
 Falseoptions.userID: rootoptions.verbosity 1options.vmstatOps 
Noneoptions.execfile: Noneargv: ['/opt/oracle/bda/bin/dcli', 
'-g', 'dcservers', '-v', 'date']Success connecting to nodes: 
['bda1node07.example.com']...entering thread for 
bda1node07.example.com:execute: /usr/bin/ssh 
-l root bda1node07.example.com ' date' ...exiting thread for 
bda1node07.example.com status: 0bda1node07.example.com: 
Tue Feb 14 10:24:43 PST 2013]

2 dcli Syntax

dcli [options] [command]

Parameters

options
The options described in below table. You can omit all options to run a command on all servers.

command
Any command that runs from the operating system prompt. If the command contains punctuation marks or special characters, then enclose the command in double quotation marks.

The backslash (\) is the escape character. Precede the following special characters with a backslash on the command line to prevent interpretation by the shell. The backslash is not needed in a command file. See the -x option for information about command files.
$ (dollar sign)
' (quotation mark)
< (less than)
> (greater than)
( ) (parentheses)

Table  dcli Options

Option Description
-c nodes Specifies a comma-separated list of Oracle Big Data Appliance

servers where the command is executed

-C Uses the list of servers in /opt/oracle/bda/cluster-rack-infiniband

as the target.

-d destfile Specifies a target directory or file name for the -f option
-f file Specifies files to be copied to the user’s home directory on the

target servers. The files are not executed. See the -l option.

-g groupfile Specifies a file containing a list of Oracle Big Data Appliance

servers where the command is executed. Either server names

or IP addresses can be used in the file.

-h--help Displays a description of the commands
-k Pushes the ssh key to each server’s /root/.ssh/authorized_keys file.
-l userid Identifies the user ID for logging in to another server.

The default ID is root.

--maxlines=maxlines Identifies the maximum lines of output displayed from a

command executed on multiple servers. The default is 10,000 lines.

-n Abbreviates the output for non-error messages. Only the

server name is displayed when a server returns normal

output (return code 0).You cannot use the

-n and -r options together.

-r regexp Replaces the output with the server name for lines

that match the specified regular expression

-s sshoptions Specifies a string of options that are passed to SSH
--scp=scpoptions Specifies a string of options that are passed to

Secure Copy (SCP), when these options are different from

sshoptions

--serial Serializes execution over the servers. The default is

parallel execution.

-t Lists the target servers
--unkey Drops the keys from the authorized_key files of the target

servers

-v Displays the verbose version of all messages
--version Displays the dcli version number
--vmstat=VMSTATOPS Displays the syntax of the Linux Virtual Memory Statistics utility

(vmstat). This command returns process, virtual memory, disk,

trap, and CPU activity information.To issue a vmstat command,

enclose its options in quotation marks. For example:

--vmstat="-a 3 5"

See your Linux documentation for more information about vmstat.

-x execfile Specifies a command file to be copied to the user’s home directory

and executed on the target servers. See the -l option.

3 dcli Return Values

  • 0: The command ran successfully on all servers.
  • 1: One or more servers were inaccessible or remote execution returned a nonzero value. A message lists the unresponsive servers. Execution continues on the other servers.
  • 2: A local error prevented the command from executing.

If you interrupt the local dcli process, then the remote commands may continue without returning their output or status.

4 dcli Examples

Following are examples of the dcli utility.

This example returns the default list of target servers:

# dcli -t
Target nodes: ['bda1node01-adm.example.com', 'bda1node02-adm.example.com', 
'bda1node03-adm.example.com', 'bda1node04-adm.example.com', 
'bda1node05-adm.example.com', 'bda1node06-adm.example.com', 
'bda1node07-adm.example.com', 'bda1node08-adm.example.com', 
'bda1node09-adm.example.com']

The next example checks the temperature of all servers:

# dcli 'ipmitool sunoem cli "show /SYS/T_AMB" | grep value'

bda1node01-adm.example.com: value = 22.000 degree C
bda1node02-adm.example.com: value = 22.000 degree C
bda1node03-adm.example.com: value = 22.000 degree C
bda1node04-adm.example.com: value = 23.000 degree C

Transport tablespaces across platforms in Oracle Database 12c

Introduction:

Transportable Tablespaces helps to copy one or more tablespaces from one Oracle Database to another.
Starting with Oracle Database version 11g, transport tablespaces can be used to migrate database from one platform to another.
This includes transporting tablespaces acrosse platforms with differemt endian formats.

Oracle database 12c Realease 1 comes with the possibility to use backupsets and image copies to transport data between platforms.

The new clause “ALLOW INCONSISTENT” in a BACKUP or CONVERT command creates a cross-platform inconsistent backup of one or more tablespaces.
You can create an inconsistent backup of the tablespace when the tablespace is still in read/write mode.

The first inconsistent backup is a level 0 incremental backup. Then, you can create multiple cross-platform level 1 incremental backups.
The final cross-platform incremental backup must be a consistent backup which requires bring the tablespace in read-only mode.

In the following article, we are going to :

– Perform a Cross-Platform Transport of Tablespaces Using consistent Backupsets.
– Perform a Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets.

2- Performing Cross-Platform Transport of Tablespaces Using consistent Backupsets:

First, let’s create a new tablespace called “TBS_TRANSP”.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:00:47 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> CREATE TABLESPACE TBS_TRANSP DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  14.   
  15. Tablespace created.  
  16.   
  17. SQL> CREATE TABLE TAB_TRASNP (ID NUMBER) TABLESPACE TBS_TRANSP;  
  18.   
  19. Table created.  
  20.   
  21. SQL> insert into TAB_TRASNP values (1);  
  22.   
  23. 1 row created.  
  24.   
  25. SQL> commit;  
  26.   
  27. Commit complete.  

Pr-requisites:

Check if tablespace is self-contained; which means check for logical or physical dependencies between objects in the transportable tablespace
and those outside of the tablespace.

 
  1. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP’TRUE);  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  6.   
  7. no rows selected  
  8.   
  9. SQL>  

From the output above, we can go ahead with the procedure of transporting the tablespace as there is no violations

and the tablespace is self-contained.

Check if plateforms supported for cross-platform tablespace transport; note Microsoft Windows x86 64-bit is supported.

 
  1. SQL> set linesize 2000  
  2. SQL> set pagesize 2000  
  3. SQL> select * from v$transportable_platform;  
  4.   
  5. PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT      CON_ID  
  6. ———– —————————————————————————————————– ————– ———-  
  7.           1 Solaris[tm] OE (32-bit)                                                                               Big                     0  
  8.           2 Solaris[tm] OE (64-bit)                                                                               Big                     0  
  9.           7 Microsoft Windows IA (32-bit)                                                                         Little                  0  
  10.          10 Linux IA (32-bit)                                                                                     Little                  0  
  11.           6 AIX-Based Systems (64-bit)                                                                            Big                     0  
  12.           3 HP-UX (64-bit)                                                                                        Big                     0  
  13.           5 HP Tru64 UNIX                                                                                         Little                  0  
  14.           4 HP-UX IA (64-bit)                                                                                     Big                     0  
  15.          11 Linux IA (64-bit)                                                                                     Little                  0  
  16.          15 HP Open VMS                                                                                           Little                  0  
  17.           8 Microsoft Windows IA (64-bit)                                                                         Little                  0  
  18.           9 IBM zSeries Based Linux                                                                               Big                     0  
  19.          13 Linux x86 64-bit                                                                                      Little                  0  
  20.          16 Apple Mac OS                                                                                          Big                     0  
  21.          12 Microsoft Windows x86 64-bit                                                                          Little                  0  
  22.          17 Solaris Operating System (x86)                                                                        Little                  0  
  23.          18 IBM Power Based Linux                                                                                 Big                     0  
  24.          19 HP IA Open VMS                                                                                        Little                  0  
  25.          20 Solaris Operating System (x86-64)                                                                     Little                  0  
  26.          21 Apple Mac OS (x86-64)                                                                                 Little                  0  
  27.   
  28. 20 rows selected.  
  29.   
  30. SQL>  

Both databases must be COMPATIBLE set to 12.0 or greater.

Bring the tablespace TBS_TRANSP in read only mode prior the TTS (Trasportable TableSpace) process.

 
  1. SQL> ALTER TABLESPACE TBS_TRANSP READ ONLY;  
  2.   
  3. Tablespace altered.  
  4.   
  5. SQL>  

Perform a cross-platform Backup of the tablespace TBS_TRANSP and datapump export:

 
  1. BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  2. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  3. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  4. TABLESPACE TBS_TRANSP;  
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:02:50 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN>  CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN>  
  13.   
  14. RMAN> BACKUP TO PLATFORM ‘Microsoft Windows x86 64-bit’  
  15. FORMAT ‘/home/oracle/PDB02/tbs_transp.bck’  
  16. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_transp.dmp’  
  17. TABLESPACE TBS_TRANSP;  
  18. 2> 3> 4>  
  19.   
  20. Starting backup at 07/15/2013 06:03:50  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=271 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=21 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=270 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=32 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=262 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_cfoz is:  
  48.    EXPDP>   /opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_cfoz” successfully completed at Mon Jul 15 06:05:17 2013 elapsed 0 00:01:07  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting full datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00060 name=+DATA/ORAWISS/DATAFILE/tbs_transp.341.820821679  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:21  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:23  
  60. piece handle=/home/oracle/PDB02/tbs_transp.bck tag=TAG20130715T060353 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02  
  62. channel ORA_DISK_1: starting full datafile backup set  
  63. input Data Pump dump file=/opt/app/oracle/product/12.1/db_1/dbs/backup_tts_ORAWISS_29071.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:05:23  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:05:24  
  66. piece handle=/home/oracle/PDB02/tbs_transp.dmp tag=TAG20130715T060353 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:05:24  
  69.   
  70. RMAN>  

Copy the Dump file and the backup file to destination host. The destination directory : C:\PDB02

Restore the foreign tablespace specifying the dump file and the backup taken in the previous step:

 
  1. Restore foreign tablespace TBS_TRANSP   
  2. FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  3. FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  4. DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:08:36 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> Restore foreign tablespace TBS_TRANSP  
  13. 2> FORMAT ‘C:\PDB02\tbs_transp.dbf’  
  14. 3> FROM BACKUPSET ‘C:\PDB02\tbs_transp.bck’  
  15. 4> DUMP FILE FROM BACKUPSET ‘C:\PDB02\tbs_transp.dmp’;  
  16.   
  17. Starting restore at 15-JUL-13  
  18. using target database control file instead of recovery catalog  
  19. allocated channel: ORA_DISK_1  
  20. channel ORA_DISK_1: SID=366 device type=DISK  
  21.   
  22. channel ORA_DISK_1: starting datafile backup set restore  
  23. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  24. channel ORA_DISK_1: restoring all files in foreign tablespace TBS_TRANSP  
  25. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.bck  
  26. channel ORA_DISK_1: restoring foreign file 60 to C:\PDB02\TBS_TRANSP.DBF  
  27. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.bck  
  28. channel ORA_DISK_1: restored backup piece 1  
  29. channel ORA_DISK_1: restore complete, elapsed time: 00:00:05  
  30. channel ORA_DISK_1: starting datafile backup set restore  
  31. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  32. channel ORA_DISK_1: restoring Data Pump dump file to C:\app\wissem\product\12.1.0\dbh_1/dbs/backup_tts_CDBTST_83935.dmp  
  33. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_transp.dmp  
  34. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_transp.dmp  
  35. channel ORA_DISK_1: restored backup piece 1  
  36. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  37.   
  38. Performing import of metadata…  
  39.    IMPDP>  
  40. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  41.   
  42.    IMPDP> Master table “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully loaded/unloaded  
  43.    IMPDP> Source timezone version is +00:00 and target timezone version is -05:00.  
  44.    IMPDP> Starting “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF”:  
  45.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  46.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  47.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  48.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  49.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  50.    IMPDP> Job “SYSBACKUP”.“TSPITR_IMP_CDBTST_gtfF” successfully completed at Mon Jul 15 12:12:43 2013 elapsed 0 00:00:23  
  51. Import completed  
  52.   
  53. Finished restore at 15-JUL-13  
  54.   
  55. RMAN> exit  
  56.   
  57.   
  58. Recovery Manager complete.  
  59.   

Check if the tablespace exists.

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 12:13:53 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP;  
  13.   
  14.         ID  
  15. ———-  
  16.          1  
  17.   
  18. SQL> exit;  
  19. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  20. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  21.   
  22. C:\Users>  

Performing Cross-Platform Transport of Tablespaces Using Inconsistent Backupsets:

Note: the same per-requisites we have seen in the previous chapter must be used here as well.

First, let’s create a new tablespace called “TBS_TRANSP_2″.
We will move this tablespace from Oracle Enterprise Linux 64 bit to Windows 8 64 bit.

 
  1. SQL> CREATE TABLESPACE TBS_TRANSP_2 DATAFILE AUTOEXTEND ON MAXSIZE 100M;  
  2.   
  3. Tablespace created.  
  4.   
  5. SQL>  CREATE TABLE TAB_TRASNP_2 (ID NUMBER) TABLESPACE TBS_TRANSP_2;  
  6.   
  7. Table created.  
  8.   
  9. SQL>  insert into TAB_TRASNP_2 values(2);  
  10.   
  11. 1 row created.  
  12.   
  13. SQL> commit;  
  14.   
  15. Commit complete.  
  16.   
  17. SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘TBS_TRANSP_2’,TRUE);  
  18.   
  19. PL/SQL procedure successfully completed.  
  20.   
  21. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;  
  22.   
  23. no rows selected  
  24.   
  25. SQL>  

Let’s take a full 0 backup of the TBS_TRANSP_2.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:29:11 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 0  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2.bck’  
  17. ;  
  18.   
  19. Starting backup at 07/15/2013 06:33:26  
  20. using channel ORA_DISK_1  
  21. using channel ORA_DISK_2  
  22. using channel ORA_DISK_3  
  23. using channel ORA_DISK_4  
  24. using channel ORA_DISK_5  
  25. channel ORA_DISK_1: starting incremental level 0 datafile backup set  
  26. channel ORA_DISK_1: specifying datafile(s) in backup set  
  27. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  28. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:33:27  
  29. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:33:28  
  30. piece handle=/home/oracle/PDB02/tbs_incons_2.bck tag=TAG20130715T063327 comment=NONE  
  31. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  32. Finished backup at 07/15/2013 06:33:28  
  33.   
  34. RMAN>  

Because FOR TRANSPORT is used instead of TO PLATFORM, this cross-platform backup can be restored on any platform.
The conversion will be performed on the destination database.

Let’s insert more records into the table;

 
  1. SQL> insert into TAB_TRASNP_2 values(3);  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> commit;  
  6.   
  7. Commit complete.  

Create a cross-platform level 1 incremental backup
of the tablespace TBS_TRANSP_2 that contains the changes made since last backup.
The tablespace is still in read/write mode.

 
  1. BACKUP  
  2. FOR TRANSPORT  
  3. ALLOW INCONSISTENT  
  4. INCREMENTAL LEVEL 1  
  5. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:36:12 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. ALLOW INCONSISTENT  
  15. INCREMENTAL LEVEL 1  
  16. TABLESPACE TBS_TRANSP_2 FORMAT ‘/home/oracle/PDB02/tbs_incons_2_lvl1.bck’2> 3> 4> 5>  
  17. 6> ;  
  18.   
  19. Starting backup at 07/15/2013 06:36:26  
  20. using target database control file instead of recovery catalog  
  21. allocated channel: ORA_DISK_1  
  22. channel ORA_DISK_1: SID=18 device type=DISK  
  23. allocated channel: ORA_DISK_2  
  24. channel ORA_DISK_2: SID=271 device type=DISK  
  25. allocated channel: ORA_DISK_3  
  26. channel ORA_DISK_3: SID=29 device type=DISK  
  27. allocated channel: ORA_DISK_4  
  28. channel ORA_DISK_4: SID=263 device type=DISK  
  29. allocated channel: ORA_DISK_5  
  30. channel ORA_DISK_5: SID=28 device type=DISK  
  31. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  32. channel ORA_DISK_1: specifying datafile(s) in backup set  
  33. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  34. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:36:29  
  35. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:36:30  
  36. piece handle=/home/oracle/PDB02/tbs_incons_2_lvl1.bck tag=TAG20130715T063629 comment=NONE  
  37. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  38. Finished backup at 07/15/2013 06:36:30  
  39.   
  40. RMAN>  

To minimize application downtime, the level 0 and level 1 incremental backups created in previous steps
can be restored and applied on the destination database while the source tablespace is still in read/write mode.
When the destination database catches up with last level 1 incremental backup, you can create the final incremental
backup with the tablespace placed in read-only mode.

Let’s bring now the TABLESPACE TBS_TRANSP_2 in read only mode and perform a last incremental 1 backup
and an export datapump file
before trying to move the backups to the destination host.

 
  1. sandbox1(orawiss):/home/oracle/PDB02>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 06:39:23 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  11. and Real Application Testing options  
  12.   
  13. SQL> ALTER TABLESPACE TBS_TRANSP_2 READ ONLY;  
  14.   
  15. Tablespace altered.  
  16.   
  17. SQL> exit  
  18. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  19. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics  
  20. and Real Application Testing options  
  21. sandbox1(orawiss):/home/oracle/PDB02>  
 
  1. sandbox1(orawiss):/home/oracle/PDB02>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 06:41:10 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: ORAWISS (DBID=3257067578)  
  11.   
  12. RMAN> BACKUP  
  13. FOR TRANSPORT  
  14. INCREMENTAL LEVEL 1  
  15. TABLESPACE TBS_TRANSP_2  
  16. FORMAT ‘/home/oracle/PDB02/tbs_incons_final.bck’  
  17. DATAPUMP FORMAT ‘/home/oracle/PDB02/tbs_incons_final.dmp’  
  18. DESTINATION ‘/home/oracle/PDB02’;  
  19.   
  20. Starting backup at 07/15/2013 06:41:23  
  21. using target database control file instead of recovery catalog  
  22. allocated channel: ORA_DISK_1  
  23. channel ORA_DISK_1: SID=31 device type=DISK  
  24. allocated channel: ORA_DISK_2  
  25. channel ORA_DISK_2: SID=262 device type=DISK  
  26. allocated channel: ORA_DISK_3  
  27. channel ORA_DISK_3: SID=271 device type=DISK  
  28. allocated channel: ORA_DISK_4  
  29. channel ORA_DISK_4: SID=28 device type=DISK  
  30. allocated channel: ORA_DISK_5  
  31. channel ORA_DISK_5: SID=263 device type=DISK  
  32. Running TRANSPORT_SET_CHECK on specified tablespaces  
  33. TRANSPORT_SET_CHECK completed successfully  
  34.   
  35. Performing export of metadata for specified tablespaces…  
  36.    EXPDP>  
  37. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.  
  38.   
  39.    EXPDP> Starting “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc”:  
  40.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK  
  41.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE  
  42.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS  
  43.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER  
  44.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK  
  45.    EXPDP> Master table “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully loaded/unloaded  
  46.    EXPDP> ******************************************************************************  
  47.    EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORAWISS_urkc is:  
  48.    EXPDP>   /home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  49.    EXPDP> ******************************************************************************  
  50.    EXPDP> Datafiles required for transportable tablespace TBS_TRANSP_2:  
  51.    EXPDP>   +DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  52.    EXPDP> Job “SYSBACKUP”.“TRANSPORT_EXP_ORAWISS_urkc” successfully completed at Mon Jul 15 06:42:30 2013 elapsed 0 00:00:51  
  53. Export completed  
  54.   
  55. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  56. channel ORA_DISK_1: specifying datafile(s) in backup set  
  57. input datafile file number=00061 name=+DATA/ORAWISS/DATAFILE/tbs_transp_2.341.820823239  
  58. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:33  
  59. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:34  
  60. piece handle=/home/oracle/PDB02/tbs_incons_final.bck tag=TAG20130715T064125 comment=NONE  
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  62. channel ORA_DISK_1: starting incremental level 1 datafile backup set  
  63. input Data Pump dump file=/home/oracle/PDB02/backup_tts_ORAWISS_71586.dmp  
  64. channel ORA_DISK_1: starting piece 1 at 07/15/2013 06:42:34  
  65. channel ORA_DISK_1: finished piece 1 at 07/15/2013 06:42:35  
  66. piece handle=/home/oracle/PDB02/tbs_incons_final.dmp tag=TAG20130715T064125 comment=NONE  
  67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  68. Finished backup at 07/15/2013 06:42:35  
  69.   
  70. RMAN>  

Copy all backup / dum files to destination Windows host;

First, Restore from full backup:

 
  1. C:\Users\wissem>rman  
  2.   
  3. Recovery Manager: Release 12.1.0.1.0 – Production on Mon Jul 15 12:51:51 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.  
  6.   
  7. RMAN> CONNECT TARGET “sys AS SYSBACKUP”;  
  8.   
  9. target database Password:  
  10. connected to target database: CDBTST (DBID=918472262)  
  11.   
  12. RMAN> RESTORE  
  13. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  14. 3> FOREIGN DATAFILE 61  
  15. 4> FORMAT ‘C:\PDB02\tbs_transp_2_61.df’  
  16. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2.bck’;  
  17.   
  18. Starting restore at 15-JUL-13  
  19. using target database control file instead of recovery catalog  
  20. allocated channel: ORA_DISK_1  
  21. channel ORA_DISK_1: SID=364 device type=DISK  
  22.   
  23. channel ORA_DISK_1: starting datafile backup set restore  
  24. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  25. channel ORA_DISK_1: restoring foreign file 00061  
  26. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2.bck  
  27. channel ORA_DISK_1: restoring foreign file 61 to C:\PDB02\TBS_TRANSP_2_61.DF  
  28. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_2.bck  
  29. channel ORA_DISK_1: restored backup piece 1  
  30. channel ORA_DISK_1: restore complete, elapsed time: 00:00:04  
  31. Finished restore at 15-JUL-13  
  32.   
  33. RMAN>  

Recover from first incremental backup

 
  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_2_lvl1.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_2_lvl1.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_2_LVL1.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Recover from last incremental backup

  1. RMAN> RECOVER  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> FOREIGN DATAFILECOPY ‘C:\PDB02\tbs_transp_2_61.df’  
  4. 4> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.bck’;  
  5.   
  6. Starting restore at 15-JUL-13  
  7. using channel ORA_DISK_1  
  8.   
  9. channel ORA_DISK_1: starting datafile backup set restore  
  10. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  11. channel ORA_DISK_1: restoring foreign file C:\PDB02\tbs_transp_2_61.df  
  12. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.bck  
  13. channel ORA_DISK_1: foreign piece handle=C:\PDB02\TBS_INCONS_FINAL.BCK  
  14. channel ORA_DISK_1: restored backup piece 1  
  15. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
  16. Finished restore at 15-JUL-13  
  17.   
  18. RMAN>  

Restore the backup set containing the export dump file. The following command will create a dump file
called ‘tbs_incons_final_dest.dmp’ which be used later to import the metadata.

  1. RMAN> RESTORE  
  2. 2> FROM PLATFORM ‘Linux x86 64-bit’  
  3. 3> DUMP FILE ‘tbs_incons_final_dest.dmp’  
  4. 4> DATAPUMP DESTINATION ‘C:\PDB02’  
  5. 5> FROM BACKUPSET ‘C:\PDB02\tbs_incons_final.dmp’;  
  6.   
  7. Starting restore at 15-JUL-13  
  8. using channel ORA_DISK_1  
  9.   
  10. channel ORA_DISK_1: starting datafile backup set restore  
  11. channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
  12. channel ORA_DISK_1: restoring Data Pump dump file to C:\PDB02/tbs_incons_final_dest.dmp  
  13. channel ORA_DISK_1: reading from backup piece C:\PDB02\tbs_incons_final.dmp  
  14. channel ORA_DISK_1: foreign piece handle=C:\PDB02\tbs_incons_final.dmp  
  15. channel ORA_DISK_1: restored backup piece 1  
  16. channel ORA_DISK_1: restore complete, elapsed time: 00:00:02  
  17. Finished restore at 15-JUL-13  
  18.   
  19. RMAN>  

Create a directory object:

 
  1. C:\Users>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:03:57 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> create directory TBS_TRANSP_DIR AS ‘C:\PDB02’;  
  13.   
  14. Directory created.  
  15.   
  16. SQL> exit  
  17. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  18. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  19.   

Plug the tablespace in to the destination database. Use the Data Pump import utility
to import the dump file containing the tablespace metadata in to the destination database.

 
  1. C:\Users>impdp \“/ as sysdba\” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  2.  
  3. Import: Release 12.1.0.1.0 – Production on Mon Jul 15 13:07:17 2013 
  4.  
  5. Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 
  6.  
  7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 
  8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
  9.  
  10. WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. 
  11.  
  12. Master table “SYS“.”SYS_IMPORT_TRANSPORTABLE_01” successfully loaded/unloaded 
  13. Source timezone version is +00:00 and target timezone version is -05:00. 
  14. Starting “SYS“.”SYS_IMPORT_TRANSPORTABLE_01“:  “/******** AS SYSDBA” directory=TBS_TRANSP_DIR dumpfile=tbs_incons_final_dest.dmp transport_datafiles=’C:\PDB02\tbs_transp_2_61.df’ nologfile=Y 
  15. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 
  16. Processing object type TRANSPORTABLE_EXPORT/TABLE 
  17. Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 
  18. Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER 
  19. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 
  20. Job “SYS“.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Mon Jul 15 13:07:34 2013 elapsed 0 00:00:08  z
  21.   

CHeck the table exists with its records.

 
  1. C:\Users\wissem>sqlplus / as sysdba  
  2.   
  3. SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 15 13:08:40 2013  
  4.   
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.  
  6.   
  7.   
  8. Connected to:  
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production  
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  11.   
  12. SQL> select * from TAB_TRASNP_2;  
  13.   
  14.         ID  
  15. ———-  
  16.          2  
  17.          3  
  18.   
  19. SQL>