MANUAL UPGRADATION 10gR2 to 11gR1

Pre-Requisite:

You should have enough MEMORY, server space and SWAP SPACE on Oracle database 10g, which you want to migrate.

Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

$  cp   utlu111i.sql   /tmp

For more about utlu111i.sql :

 http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select version from v$timezone_file;

          

          version

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

                   2  

here oracle 10.2.0.1 has timezone file version of 2

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.

SQL >  select * from sys.sys_tzuv2_temptab ;

           no rows selected

here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade

Step 4) Run Pre-Upgrade Utility again

Run utlrp.sql to validate the objects if there is any invalidate objects.

SQL> @?/rdbms/admin/utlrp.sql

Check for any INVALID Objects in the database before upgrade starts:

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

SQL> purge DBA_RECYCLEBIN

Gather dictionary stats to run UPGRADE process fast,

SQL> exec dbms_stats.gather_dictionary_stats;

Create pfile from spfile 

Shutdown  à shut down the database in oracle 10g.

Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.

In that make changes shown below,

 1.     Remove BDUMP, CDUMP, UDUMP and their locations

 2.     Add Diagnostic_dest=/’$ORACLE_BASE/diag’-àlocation for diagnostic dest

 3.     Change audit_file_dest location

 4.     Change compatibility to 11g and save it.

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1
[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export PATH=$ORACLE_HOME/bin:$PATH
[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Once the upgrade finishes, It will shut down the database automatically.

Now export New oracle HOME and startup the database

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export TNS_ADMIN=$ORACLE_HOME/network/admin

Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

SQL> startup 

SQL> select substr(comp_id,1,10) comp_id, substr(comp_name,1,25) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

All the components should be VALID state

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display/verify the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile invalid objects after upgrade

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

Run utluiobj.sql after the upgrade to identify any new invalid objects

Edit /etc/oratab file with new 11g Oracle home and comment out 10g oracle home

Removing a NODE from RAC

1. Delete the instance on the node to be removed
2. Clean up ASM
3. Remove the listener from the node to be removed
4. Remove the node from the database
5. Remove the node from the clusterware

You can delete the instance by using the database creation assistant (DBCA), invoke the program choose the RAC database, choose instance management and then choose delete instance, enter the sysdba user and password then choose the instance to delete.

To clean up ASM follow the below steps

1. From node 1 run the below command to stop ASM on the node to be removed
   
     srvctl stop asm -n rac3
     srvctl remove asm -n rac3
2. Now run the following on the node to be removed
   
     cd $ORACLE_HOME/admin
     rm -rf +ASM
     cd $ORACLE_HOME/dbs
     rm -f *ASM*
3. Check that /etc/oratab file has no ASM entries, if so remove them

Now remove the listener for the node to be removed

·         Login as user oracle, and set your DISPLAY environment variable, then start the Network Configuration Assistant
     $ORACLE_HOME/bin/netca

·         Choose cluster management

·         Choose listener

·         Choose Remove

·         Choose the  name as LISTENER

Next we remove the node from the database

1. Run the below script from the node to be removed
   
   cd $ORACLE_HOME/bin
   ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac3}" -local
   ./runInstaller
2. Choose to deinstall products and select the dbhome
3. Run the following from node 1
   
     cd $ORACLE_HOME/oui/bin
     ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac1,rac2,rac3}"

Lastly we remove the clusterware software

1. Run the following from node 1, you obtain the port number from remoteport section in the ons.config file in $ORA_CRS_HOME/opmn/conf 
     $CRS_HOME/bin/racgons remove_config rac3:6200
2. Run the following from the node to be removed as user root
     cd $CRS_HOME/install
     ./rootdelete.sh
3. Now run the following from node 1 as user root, obtain the node number first 
     $CRS_HOME/bin/olsnodes -n
     cd $CRS_HOME/install
     ./rootdeletenode.sh rac3,3
4. Now run the below from the node to be removed as user oracle
   
      cd $CRS_HOME/oui/bin
     ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac3}" CRS=TRUE -local
     ./runInstaller
5. Choose to deinstall software and remove the CRS_HOME
6. Run the following from node as user oracle
   
     cd $CRS_HOME/oui/bin 
     ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rac1,rac2,rac3}" CRS=TRUE
7. Check that the node has been removed, the first should report "invalid node", the second you should not see any output and the last command you should only see nodes rac1 and rac2 
   
     srvctl status nodeapps -n rac3
     crs_stat |grep -i rac3
     olsnodes -n

JOB DESCRIPTION

 1. Oracle Database DBA Activities

Installation

Pre-installation Tasks

Installing Oracle Server

Installing & Maintaining DB Tools

Uninstallation

Instance upgrade

Database Patch Installation

Database Design & Setup

• Physical Database Design and Implementation
• Database Setup
• Database Clean up / Removal
• DB Cloning
• DB Refresh

DB Instances

Start up and shutdown of Database instances

Setting up of Database replication / standby / clusters

Job scheduling

Monitoring

Verify all Instances are running

Look for any new alert log entries

Verify that SNMP is running

Look for Trace files

Session Monitoring

Query or Transaction monitoring

Lock and Latch monitoring

Activity / Redo Log monitoring

Job monitoring and Purging Job history

Replication / Stand by monitoring

Object Monitoring

Verify free space in tablespaces

Verify rollback segments

Maintenance

Reorganization of DB to remove fragmentation

Rebuild of indexes

Analyze of objects

Purging of DB logs and trace files

Performance Management

Verify that Server has enough resources for acceptable performance

Periodic Report Generation on Database Health

Identify bad growth projections

Identify space-bound objects

Review contention for CPU, memory, network, and disk resources

Generating statistics

Look for objects that break rules.

Ensure that all indexes use an index table space

Ensure that all tables have unique primary keys.

Look for harmful growth rates

Examine tuning opportunities.

Look for I/O Contention

Review fragmentation by investigating row chaining and other areas of fragmentation

Database Objects Creation / Review

Check for unusable indexes

User Management

User / Schema Creation

User Management

Security

Roles and Privileges Management

Look for security policy violations

Look in Net8 logs for errors and other issues

Privileges & Access controls on Database

Backup and Recovery

Verify that the database archiving to tape was successful.

Verify that the database backup was successful

Backup execution and monitoring

Archive all alert logs to history

Conduct Backup , restoration drills for backup verification

Troubleshooting

Database Connectivity issues

Slow response

Operations timed out

Dead locks

Database Hangs

Database incident and error resolution

Archiver Process Stuck

Database incident and error resolution

Consulting

Project performance into the future

Testing applicability of patches

Physical Database Design review

Reorganization

Validating Practices and Tools related to DB

Database Consolidation

Database consulting for Disaster Recovery

Database Software and Tool Evaluation

Database Backup & Recovery Design

Security Policies

Trend Analysis & Capacity Planning

Server configuration needs

Mapping user requirements to check availability or need to implement

Other Activities

Vendor Management

System Performance Monitoring

Maintaining Environment details

Shell Scripting for DB related work

ORA-00018, ORA-00020 maximum number of sessions exceeded

Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded

ORA-00020: maximum number of processes (string) exceeded

In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.

You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.

SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME VALUE
------------------------------ ----------
processes 150
sessions 

                                                        (or)

To see the no. of   PROCESSES  &   SESSIONS use command:

SQL> show   parameter  session;

SQL>  show   parameter   process;

The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,

SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';

sessions par default value
--------------------------
170

In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.

How to Solve the Problem

If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
or simply you can consider to increase processes parameter by,
Alter system set processes=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.

Similarly you can set the processes parameter.

Oracle 11g Release 2 (11.2.0) - Sept 2009

•  A separate tool, named deinstall, introduced for deinstallation and deconfiguration of Oracle products. Oracle Universal Installer no longer removes Oracle software.
• chopt tool, a command-line utility, to configure the database options. Oracle Universal Installer no longer provides the custom installation option of individual components. 

• Unusable indexes and index partitions no longer consume space in the database because they become segmentless.

• Complete IPv6 Support for JDBC Thin Clients.

• From this release, Oracle/ASM will Support 4KB Sector Disk Drives.

• Edition-based redefinition allows an application's database objects to be changed without interrupting the application's availability by making the changes in the privacy of a new edition.

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

• New SQL*Plus command SET EXITCOMMIT specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
  SET EXITC[OMMIT] {ON|OFF}

• LISTAGG Analytic Function
  This function making very easy to aggregate strings. It also allows us to order the elements in the concatenated list.
  COLUMN employees FORMAT A50
  SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;
  DEPTNO EMPLOYEES
  ---------- --------------------------------------------------
  10 CLARK,KING,MILLER
  20 ADAMS,FORD,JONES,SCOTT,SMITH
  30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 

• Oracle Database 11g Release 2, provides the new PRECEDES keyword in trigger definition which allows trigger-upon-trigger dependencies.

• Audit filename will be prefixed with the instance name and ends with a sequence number. For example:
  SID_ora_pid_seqNumber.aud or SID_ora_pid_seqNumber.xml
  An existing audit file is never appended.

• From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT.

• The initial segment creation for non partitioned tables and indexes can be delayed until data is first inserted into an object. Depending on the module usage, only a subset of the objects is really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation. 

• Flashback Data Archive support for DDL.

• In Oracle Database 11g Release 2 (11.2), support for the LZO compression algorithm on SecureFiles has been added. The new compression option is designated as COMPRESS LOW.

Fast decompression - LZO compression is 2 times faster than ZLIB. Fast compression - LZO compression is 3 times faster than ZLIB.

• IGNORE_ROW_ON_DUPKEY_INDEX hint for INSERT Statement With INSERT INTO TARGET ... SELECT ... FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted.
  
  ASM

• ASM Configuration Assistant (ASMCA) is a new tool to install and configure ASM. 

• ASM Cluster File System (ACFS) provides support for files such as Oracle binaries, Clusterware binaries, report files, trace files, alert logs, external files, and other application datafiles. ACFS can be managed by ACFSUTIL, ASMCMD, OEM, ASMCA, SQL command interface.

• ASM Dynamic Volume Manager (ADVM) provides volume management services and a standard device driver interface to its clients (ACFS, ext3, OCFS2 and third party files systems).

• ACFS Snapshots are read-only on-line, space efficient, point in time copy of an ACFS file system. ACFS snapshots can be used to recover from inadvertent modification or deletion of files from a file system.

• ASM can hold and manage OCR (Oracle Cluster Registry) file and voting file.

• ASM diskgroups can be renamed, by using renamedg command.

   ASMCMD utility can do

• ASMCMD Instance Management Commands - dsget, dsset, lsop, lspwusr, orapwusr, shutdown, spbackup,spcopy, spget, spmove, spset, startup.
• Managing diskgroups (create, mount, alter, drop) through ASMCMD Disk Group Management Commands - chdg, chkdg, dropdg, iostat, lsattr, lsdg, lsdsk, lsod, md_backup,md_restore, mkdg, mount, offline, online, rebal, remap, setattr, umount.
• User management and File access control through ASMCMD File Access Control Commands - chgrp, chmod, chown, groups, grpmod, lsgrp, lsusr, mkgrp, mkusr, passwd, rmgrp, rmusr.
• Template management through ASMCMD Template Management Commands - chtmpl, lstmpl, mktmpl, rmtmpl.
• Volume management through ASMCMD Volume Management Commands - volcreate, voldelete, voldisable, volenable, volinfo, volresize, volset, volstat.
• We can execute OS commands at asmcmd by using !, in the same we do at SQL prompt.

Dataguard

• Automatic Block Repair - Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.
• The number of standby databases that a primary database can support is increased from 9 to 30 in this release.
• Compressed table support in logical standby databases and Oracle LogMiner.

Tablespace Point-In-Time Recovery (TSPITR)

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

Oracle Scheduler

• E-mail Notification - Oracle Database 11g Release 2 (11.2) users can now get e-mail notifications on any job activity.
• File Watcher - File watcher enables jobs to be triggered when a file arrives on a given machine.

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

• A single SET NEWNAME command can be applied to all files in a database or tablespace.

SET NEWNAME FOR DATABASE TO format;
SET NEWNAME FOR TABLESPACE tsname TO format;

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

Transportable Tablespaces (TTS)

Transportable tablespaces can be used to copy a set of tables from one Oracle database to another. This approach to replication has been used for feeding data from OLTP systems to data warehouse systems, updating data warehouses and data marts from staging systems, loading data marts from central data warehouses, archiving OLTP and data warehouse systems efficiently and data publishing to internal and external customers.

You can move large amounts of data between databases just simply by moving data files from one database to another. You copy all the data files from the source database to the target database and import the data

dictionary information about the tablespaces from the source database to the target database.

Transportable Tablespace mechanism consist of combination of 2 things:

·         An export of metadata(instead of data) of tablespace

·         A copy of files belonging to tablespace 

You use transportable tablespaces mainly in the context of a data warehouse, some of the important features are

·         Moving data from the source database (OLTP) into a data warehouse database

·         Moving data from a staging database into a data warehouse database

·         Moving data from a data warehouse to a data mart

·         Performing tablespace point-in-time recovery

·         Archiving historical data

BENEFITS  OF TRANSPOTABLE TABLESPACES

·         Move  entire tablespace data

·         Supports media recovery  

·         Reduces Server Burden - When transportable tablespaces replace large export/import or other loading,a significant processing burden disappears.

·         Higher Reliability - Fewer processing steps and quicker processing reduce the opportunities for human Errors and system problems.

Limitations:

·         The source and target database must be on the same hardware platform.

·         The source and target database must have the same database block size.

·         A tablespace of the name being copied must not exist in the target database.

·         The current implementation does not support :

o    snapshot/replication

o    function-based indexes

o    scoped REF's

o    domain indexes

o    8.0-compatible advanced queues with multiple recipients.

There are 4 steps to transport a tablespace

1.     Grant the necessary privileges

2.     Make the tablespace is transportable

3.     Generate the transportable tablespace set (data dictionary information)

4.     Copy the data files to the target server

5.     Perform the tablespace import

Transporting tablespaces on different platforms :

You can transport tablespace between different server architectures, there is only one requirement both platform must have the same endian format. endian format refers to the byte ordering of the file system, they can be one of two types big or small, if they differ you must convert the data files to the format you require.

To check what format you have

Check endian format

select t.endian_format from v$transportable_platform t, v$database d where t.platform_name = d.platform_name;

You can also query the V$TRANSPORTABLE_PLATFORM view to see all the platforms that are supported, and to determine their platform names and IDs and their endian format.

SQL> select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT

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

1 Solaris[tm] OE (32-bit)                  Big

2 Solaris[tm] OE (64-bit)                  Big

3 HP-UX (64-bit)                           Big

4 HP-UX IA (64-bit)                        Big

5 HP Tru64 UNIX                            Little

6 AIX-Based Systems (64-bit)               Big

7 Microsoft Windows IA (32-bit)            Little

8 Microsoft Windows IA (64-bit)            Little

9 IBM zSeries Based Linux                  Big

10 Linux IA (32-bit)                        Little

11 Linux IA (64-bit)                        Little

12 Microsoft Windows x86 64-bit             Little

13 Linux x86 64-bit                         Little

15 HP Open VMS                              Little

16 Apple Mac OS                             Big

17 Solaris Operating System (x86)           Little

18 IBM Power Based Linux                    Big

19 HP IA Open VMS                           Little

20 Solaris Operating System (x86-64)        Little

21 Apple Mac OS (x86-64)                    Little  (from 11g R2)

The steps to transport a tablespace that are a different endian format are

1.     Ensure that the tablespaces are self-contained

2.     Make the tablespaces read-only

3.     Export the metadata using Data Pump Export

4.     Convert the data files to match the endian format

5.     Copy the converted data files to the target system

6.     Use the Data Pump Import utility to import the metadata

Steps for Migration

1-Verify Tablespace is self-contained:

      You can optionally choose whether to include referential integrity constraints as part of the transportable tablespace set. If you choose to do so, the tables required to maintain the key relationships will increase the size of the transportable tablespace set. Including referential integrity constraints is optional, because you may refer to the same tables in multiple databases. Say you plan to move a tablespace from a test database that contains a table called COUNTRY to a production database that already contains a COUNTRY table of identical country codes. Because the two databases have identical tables, you do not need to transport the referential integrity constraints. You could transport the tablespace and then reenable the referential integrity constraints in the target database once you've moved the tablespace, simplifying the creation of the transportable tablespace set. You must always export primary-key constraints.

     To find out if a tablespace set is self-contained, execute the TRANSPORT_SET_CHECK procedure as SYS User, which is in the DBMS_TTS package. This procedure takes two input parameters: the tablespace set and a Boolean flag set to TRUE if you want to include referential integrity constraints.
In this example, constraints aren't included in the AGG_DATA and AGG_INDEXES tablespace checks:
    execute DBMS_TTS.TRANSPORT_SET_CHECK ('AGG_DATA,AGG_INDEXES','FALSE');

Then execute the following to report violations:

        SELECT * FROM transport_set_violations;

2- Put Tablespace/s in Read Mode

Once you have verified that the selected tablespace set is self-contained, make the tablespaces read only, as follows:

     alter tablespace AGG_DATA read only;
alter tablespace AGG_INDEXES read only;

3- Export MetaData of Tablespace

     Next, export the metadata for the tablespaces, using the TRANSPORT_TABLESPACES and TABLESPACES parameters of the Oracle Export utility:

exp user/pwd TRANSPORT_TABLESPACE=Y TABLESPACES=(AGG_DATA,AGG_INDEXES) CONSTRAINTS=N GRANTS=Y TRIGGERS=N
 

4- Copy and Plugg in the Transportable Tablespace Set

Once you have moved the transportable tablespace set to an area the target database can access, you can plug the set into the target database. First, use the Oracle Import utility to import the exported metadata:

imp user/pwd TRANSPORT_TABLESPACE=Y DATAFILES=(agg_data.dbf,agg_indexes.dbf)

      In the import command, you specify the datafiles that are part of the transportable tablespace set. When the import has finished, all of the new tablespaces in the transportable tablespace set are in read-only mode. 

5- Modify Tablespaces to read-write Mode

      To change them to read-write mode, you issue the alter tablespace read write command in the target database:

      alter tablespace AGG_DATA read write;
 alter tablespace AGG_INDEXES read write;

      After you've made these changes, you should back up the control files of both the target and the source database:

           alter database backup controlfile to trace;

Ref:   http://www.mbjconsulting.com/oracle_links/Transportable_Tablespaces.html

Read more :  Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)