Powered By Blogger

Thursday, September 15, 2011

Grant privilege on V$ tables to other users

SQL> grant select on v$session to scott;
grant select on v$session to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> select owner,object_name,object_type from dba_objects where object_name = 'V$SESSION';
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC                         V$SESSION                      SYNONYM

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = 'V$SESSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$SESSION

SQL> grant select on v_$session to scott;

Grant succeeded.

All the V$ views are synonyms, we can use the above method to query and find the corresponding tables and grant access to other users.

Regards,
Navaneeth

Thursday, September 8, 2011

TNS Entry on 2 node RAC database and Listener Entry

************ Tnsnames.ora ********

INSPT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB2)
    )
  )

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB1)
    )
  )

LISTENERS_DB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
  )

CLUSTER1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 173.243.1.235)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pedb)
    )
  )

DB_REPORT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )




 ************ Listener db1 **************


# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = DB1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
  )


Wednesday, September 7, 2011

NX configuration on RHEL5 server for Acessing Linux server through GUI

Installing RPM's on linux server for configuring NX :

Nx is used as a GUI interface for acessing Linux servers. 

1. Need to install NX client on Windows machine.
2. Install Nxclient, NXnode and Nxserver RPM's on Linux server on which you need to acess through GUI.


[root@Servername /]# rpm -ivh nxclient-3.5.0-7.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxclient               ########################################### [100%]
Showing file: /usr/NX/share/documents/client/cups-info

 CUPS Printing Backend

 The NX Client set-up procedure detected that your "IPP CUPS" printing
 backend doesn't allow printing from the NX session. In order to have
 printing support in your NX system, you need to set proper permissions
 on the IPP backend. Please execute:

   chmod 755 /usr/lib/cups/backend/ipp
[root@Servername /]# rpm -ivh nxnode-3.5.0-6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxnode                 ########################################### [100%]
NX> 700 Starting: install node operation at: Tue Sep 06 15:45:08 2011.
NX> 700 Autodetected system 'redhat'.
NX> 700 Install log is '/usr/NX/var/log/install'.
NX> 700 Creating configuration in /usr/NX/etc/node.cfg.
NX> 700 Inspecting local CUPS environment.
NX> 700 Generating CUPS entries in: /usr/NX/etc/node.cfg.
NX> 700 Installation of version: 3.5.0-6 completed.
NX> 700 Bye.
[root@Servername /]# rpm -ivh nxserver-3.5.0-8.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxserver               ########################################### [100%]
NX> 700 Installing: server at: Tue Sep 06 15:45:37 2011.
NX> 700 Autodetected system: redhat.
NX> 700 Install log is: /usr/NX/var/log/install.
NX> 700 WARNING: Found file: /usr/NX/etc/keys/node.localhost.id_dsa.
NX> 700 WARNING: Skipping generation keys for NX users authentication.
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 700 Installation of NX server was completed with warnings.
NX> 700 Please review the install log '/usr/NX/var/log/install'
NX> 700 for further details.
NX> 700 Showing file: /usr/NX/share/documents/server/install-notices

Server keys

The initial login between client and server happens through a DSA key
pair, i.e. a couple of specially generated cryptographic keys, called
the private key and the public key, which allow you to establish a
secure connection, by means of SSL encryption, between NX client and
NX server.

The public part of the key-pair is provided during the installation
of the server, while the private part of the key-pair is distributed
together with the NX Client. This ensures that each NX client is able
to authenticate to the server and to start the procedure for autho-
rizing the user and negotiating the session.

If you want to create a virtual private network (VPN) instead, you
need to generate a new DSA key-pair and distribute the private part
of the key-pair to those NX clients you want authenticated to the NX
server. More information on how to generate and distribute a new DSA
key-pair is available at:

http://www.nomachine.com/ar/view.php?ar_id=AR01C00126

Creating Users

NX is configured to allow access from any system user, as long as
valid credentials are given to the user for the SSH login. NX pro-
vides an alternative authorization method, allowing system admin-
istrators to determine which users are given access to the NX fun-
ctionalities. This works by implementing a separation between the
system password and the NX password, so that, for example, it is
possible to forbid remote access to the system by any other means
except via NX and use the NX tools to implement effective accounting
of the system resources used by the user, or to share NX passwords in
an external database.

To activate the NX user and password DBs, you will have to edit the
NX server configuration file by hand or use the NX Server Manager
Web tool available for download on the NoMachine Web site at:

http://www.nomachine.com/download-manager.php

Session Shadowing and Desktop Sharing

The session shadowing functionality allows you to share NX sessions
running on the node. The desktop sharing functionality instead, gives
access to the native display of the X server as if you were in front
of the monitor. By default you can access sessions in interactive mode
and upon authorization of the session owner. You can modify this beha-
viour by tuning the server configuration according to your needs, for
example by allowing access to sessions in view-only mode, or connecting
to either a suspended session or the local display via the Desktop
Manager login window.

Load Balancing

NX Advanced Server provides support for multi-node capabilities and
load balancing. In its current implementation, NX server can only
manage accounts on the host machine, so to grant access to the node
running remotely, you will need to create the user account directly
on the remote node host by issuing the NX node commands as root user.
You will also need to add the NX Server public DSA Key to the node to
allow this server to connect to the node running on the remote host.

Documentation

For further information on how to manage the configuration of your
NX system, please refer to the System Administrator's Guide available
on the NoMachine Web site at:

http://www.nomachine.com/documentation/admin-guide.php

The NoMachine Team.


NX> 700 Bye.
[root@Servername /]# service nxserver restart
Trying to restart NX server:
NX> 123 Service stopped.
NX> 153 Stopping NX server monitor.
NX> 153 NX server monitor already stopped.
NX> 122 Service started.
NX> 999 Bye.
Trying to restart NX statistics:
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 999 Bye.
[root@Servername /]#

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

Uninstall nxservice packages on RHEL5 server

[root@Servername /]# rpm -e nxserver-3.5.0-8.x86_64.rpm
error: package nxserver-3.5.0-8.x86_64.rpm is not installed
[root@Servername /]# rpm -e nxserver-3.5.0-8.x86_64
NX> 702 Pre-uninstalling: server at: Tue Sep 06 15:50:30 2011.
NX> 702 Shutting down.
NX> 702 Shutdown complete.
NX> 702 Cleaning up NX session DB.
NX> 718 Cannot stop NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 702 Cleaned up NX session DB.
NX> 702 Bye.

NX> 702 Uninstalling: server at: Tue Sep 06 15:50:33 2011.
NX> 702 Autodetected system: redhat.
NX> 702 Uninstall log is: /usr/NX/var/log/uninstall.

NX> 702 Deleting user: nx from the system.
NX> 702 Saving configuration file to: /usr/NX/etc/server.cfg.backup.
NX> 702 Saving passwords DB to: /usr/NX/etc/passwords.db.backup.
NX> 702 Saving users DB to: /usr/NX/etc/users.db.backup.
NX> 702 Saving administrators DB to: /usr/NX/etc/administrators.db.backup.
NX> 702 Saving profiles DB to: /usr/NX/etc/profiles.db.backup.
NX> 702 Saving guests DB to: /usr/NX/etc/guests.db.backup.
NX> 702 Running: chkconfig to remove init script.
NX> 702 Verifying that all init scripts have been removed.
NX> 702 Uninstallation of version: 3.5.0-8 completed.
NX> 702 Bye.

[root@Servername /]# rpm -ivh nxclient-3.5.0-7.x86_64
error: open of nxclient-3.5.0-7.x86_64 failed: No such file or directory
[root@Servername /]# rpm -e nxclient-3.5.0-7.x86_64
error: Failed dependencies:
        nxclient >= 3.0.0 is needed by (installed) nxnode-3.5.0-6.x86_64
[root@Servername /]# rpm -e nxnode-3.5.0-6.x86_64

NX> 702 Starting: uninstall operation at: Tue Sep 06 15:51:07 2011.
NX> 702 Autodetected system 'redhat'.
NX> 702 Uninstall log is '/usr/NX/var/log/uninstall'.
NX> 702 Running: chkconfig to remove init script.
NX> 702 Verifying that all init scripts have been removed.
NX> 702 Saving configuration file to: /usr/NX/etc/node.cfg.backup.
NX> 702 Version '3.5.0-6' uninstall completed.
NX> 702 Bye. 
 
[root@Servername /]#

Regards,
Navaneeth

Wednesday, July 27, 2011

Online patching in 11.2.0.1 database on windows 32 bit machine

For applying the below patch i didnt shutdown my database, this is tested on a test database on windows 32bit platform, But not sure how it behaves on a production database.

D:\app\nav6cob\product\11.2.0\dbhome_1>cd D:\siva\database\11g_patch_win32bit\10073948

D:\siva\database\11g_patch_win32bit\10073948>opatch apply
Invoking OPatch 10.2.0.2.1

Oracle interim Patch Installer version 10.2.0.2.1
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : d:\app\nav6cob\product\11.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 10.2.0.2.1
OUI version       : 11.2.0.1.0
OUI location      : d:\app\nav6cob\product\11.2.0\dbhome_1\oui
Log file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch-2011_Jul_27_12-34-00-IST_Wed.log

ApplySession applying interim patch '10073948' to OH 'd:\app\nav6cob\product\11.2.0\dbhome_1'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.1.0.7.0, higher version 11.2.0.1.0 found. ]  not present in the Oracle Home or a higher version is
d.

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'd:\app\nav6cob\product\11.2.0\dbhome_1')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '10073948' for restore. This might take a while...
Backing up files affected by the patch '10073948' for rollback. This might take a while...

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Copying file to "d:\app\nav6cob\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\uix2-install.zip"
Copying file to "d:\app\nav6cob\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\lib\uix2.jar"
ApplySession adding interim patch '10073948' to inventory

Verifying the update...
Inventory check OK: Patch ID 10073948 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10073948 are present in Oracle Home.
Execution of 'cmd /C D:\siva\database\11g_patch_win32bit\10073948\custom\scripts\post.bat -apply 10073948 ':

Return Code = 0

The local system has been patched and can be restarted.


OPatch succeeded.

D:\siva\database\11g_patch_win32bit\10073948>cd d:\app\nav6cob\product\11.2.0\dbhome_1\OPatch

D:\app\nav6cob\product\11.2.0\dbhome_1\OPatch>opatch lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : d:\app\nav6cob\product\11.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : d:\app\nav6cob\product\11.2.0\dbhome_1\oui
Log file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch2011-07-27_12-38-44PM.log

Patch history file: d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch_history.txt

Lsinventory Output file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2011-07-27_12-38-44PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  10073948     : applied on Wed Jul 27 12:35:44 IST 2011
Unique Patch ID:  13741704
   Created on 1 Sep 2010, 02:44:28 hrs PST8PDT
   Bugs fixed:
     10073948



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

OPatch succeeded.

D:\app\nav6cob\product\11.2.0\dbhome_1\OPatch>

D:\app\nav6cob\product\11.2.0\dbhome_1\BIN>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 27 12:36:42 2011

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

Enter user-name: sys/database as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 100
SQL> set lines 1000
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             255853008 bytes
Database Buffers          272629760 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

SQL> select comp_name,version,status from dba_registry;
COMP_NAME                          VERSION       STATUS
--------------------------------- ------------ -----------
OWB                                11.2.0.1.0      VALID
Oracle Application Express         3.2.1.00.10     VALID
Oracle Enterprise Manager          11.2.0.1.0      VALID
OLAP Catalog                       11.2.0.1.0      VALID
Spatial                            11.2.0.1.0      VALID
Oracle Multimedia                  11.2.0.1.0      VALID
Oracle XML Database                11.2.0.1.0      VALID
Oracle Text                        11.2.0.1.0      VALID
Oracle Expression Filter           11.2.0.1.0      VALID
Oracle Rules Manager               11.2.0.1.0      VALID
Oracle Workspace Manager           11.2.0.1.0      VALID
Oracle Database Catalog Views      11.2.0.1.0      VALID
Oracle Database Packages and Types 11.2.0.1.0      VALID
JServer JAVA Virtual Machine       11.2.0.1.0      VALID
Oracle XDK                         11.2.0.1.0      VALID
Oracle Database Java Packages      11.2.0.1.0      VALID
OLAP Analytic Workspace            11.2.0.1.0      VALID
Oracle OLAP API                    11.2.0.1.0      VALID

18 rows selected.
Regards,
Navaneeth

Wednesday, July 13, 2011

Flashback Data Archive in 11g

SQL> CREATE TABLESPACE FDB DATAFILE 'D:\APP\NAV6COB\ORADATA\ORCL11G\FDB_DATA_1.DBF' SIZE 10m;

Tablespace created.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb quota unlimited retention 1 year;
CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb quota unlimited retention 1 year
                                                            *
ERROR at line 1:
ORA-55603: Invalid Flashback Archive command


SQL> CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb retention 1 year;

Flashback archive created.

SQL>
SQL> select * from dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------
SYS        FDA_1                    1               365 13-JUL-11 12.27.06.000000000 PM 13-JUL-11 12.27.06.000000000 PM            DEFAULT
SQL>
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------------------------------------    
EMP1     SCOTT             FDA_1  SYS_FBA_HIST_73694   ENABLED

SQL> alter table emp1 flashback archive;

Table altered.

SQL>
SQL>
SQL> update emp1 set ename='NAVANEETH' where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> drop table emp1;
drop table emp1
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Managing Flashback archive:


SQL> ALTER FLASHBACK ARCHIVE fda_1 MODIFY RETENTION 2 YEAR;

Flashback archive altered.

SQL> alter flashback archive fda_1 purge all;

Flashback archive altered.

SQL> drop flashback archive fda_1;

Flashback archive dropped.

SQL>

Thursday, June 2, 2011

Transport tablespace between platforms with same endian format using EXP/IMP.

Transportable Tablespaces  This can be used by only export/import.

Please try to check the endian format before doing this TTablespace using exp/imp. The endian format should be same between the platforms , otherwise use RMAN convert as shown in the other post.  

RESTRICTIONS
----------------

1. Character set as well as national character set of the source and the target must be the same.
2. Users whose default tablespace is getting exported must exist in the target database before importing.
3. Target database must not have tablespace of the same name.
4. The tablespace must be self contained to ensure that the table is complete in case of partitioning.

We will consider a tablespace by name "N2" for the example.


EXPORT THE TABLESPACE
------------------------

1. Make the tablespace readonly.
sqlplus> connect sys/*******
sqlplus> alter tablespace N2 read only;

2. Export the tablespace specifying the TABLESPACES and TRANSPORT_TABLESPACE clause
D:\app\product\11.2.0\dbhome_1\BIN>exp "'sys/database as sysdba'" file=d:\transport_tblsp\n2.dmp tablespaces=n2 transport_tablespace=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 2 16:49:12 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace N2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                            N21
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.


3. Transfer the meta data export dump file and the copy of the datafile(s) to the target  database. (Could be ftp or copy) as below

D:\transport_tblsp>ftp 10.417.313.812
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> put n2.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 10493952 bytes sent in 5.59Seconds 1875.93Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
226 Directory send OK.
ftp: 8 bytes received in 0.00Seconds 8000.00Kbytes/sec.
ftp> put *.dmp
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 4096 bytes sent in 0.00Seconds 4096000.00Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
n2.dmp
226 Directory send OK.
ftp: 16 bytes received in 0.00Seconds 16000.00Kbytes/sec.
ftp>


4. Import the metadata using TABLESPACES, TRANSPORT_TABLESPACE, DATAFILES clause, If more than one datafiles are available then specify all of them comma seperated.
[oracle@hostname backup]$ imp '"sys/******* as  sysdba"' file=n2.dmp TABLESPACES=N2 TRANSPORT_TABLESPACE=y datafiles=/u01/app/oracle/oradata/orcl/N2.DBF

Import: Release 11.2.0.1.0 - Production on Thu Jun 2 17:33:11 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing N2's objects into N2
. . importing table                          "N21"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


5. Make the tablespace read write, if required, at the target database
SQL> alter tablespace n2 read write;

Tablespace altered.


These below tables contains a new field PLUGGED_IN which is helpful in identifying the plugged in tabelspaces.
The value of PLUGGED_IN would be YES in dba_tablespaces and 1 in v$datafile if the tablespace/datafile was plugged in.
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;

TABLESPACE_NAME                STATUS    PLU
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
USERS                          ONLINE    NO
EXAMPLE                        ONLINE    YES
APEX                           ONLINE    NO
TEST11                         ONLINE    NO
N2                             ONLINE    YES

SQL> select name, status, plugged_in from v$datafile;

NAME                                               STATUS  PLUGGED_IN
-------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM           0
/u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE           0
/u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/users01.dbf           ONLINE           0
/u01/app/oracle/oradata/orcl/example01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/apex01.dbf            ONLINE           0
/u02/SCRIPTS/test11_data_01.dbf                    ONLINE           0
/u01/app/oracle/oradata/orcl/N2.DBF                ONLINE           1

8 rows selected.

Regards,
Navaneeth

Wednesday, June 1, 2011

Transport tablespace in oracle 11.2.0.1 between windows 32 bit to Linux 64 bit using RMAN


Transport tablespace from Windows 32 bit to linux 64bit database 11g R2 using RMAN.





Reference : 10g : Transportable Tablespaces Across Different Platforms [ID 243304.1]

1. The source and target database must use the same character set and national character set.check as below.


From source

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

From target

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0



2. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS.


>> Using RMAN CONVERT

1. Prepare for export of the tablespace.
  
    * Check that the tablespace will be self contained:
 SQL> execute sys.dbms_tts.transport_set_check('USERS', true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected
  
>>  The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export.

     SQL> alter tablespace users read only;

Tablespace altered.


2. Export the metadata.
    
>> Using the original export utility
   
exp 'sys/database as sysdba' file=D:\transport_tblsp\n1.dmp log=D:\transport_tblsp\tba_exp.log transport_tablespace=y tablespaces=n1
   
>> Using datapump exp
     
  CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;

  GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

  expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1

>> If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter:
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained then the export will fail.
3. Use V$TRANSPORTABLE_PLATFORM to  determine the endianness of each platform.You can execute the following query on each platform instance:   
SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

>> then copy the datafiles and dumpfiles with ftp or copy

D:\transport_tblsp>ftp
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): username
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> pwd
257 "/u01/app/backup"
ftp> put *.dmp
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 4096 bytes sent in 0.00Seconds 4096000.00Kbytes/sec.
ftp>





D:\app\oradata\db11g>ftp 10.417.313.812
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp>
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
n1.dmp
226 Directory send OK.
ftp: 9 bytes received in 0.00Seconds 9000.00Kbytes/sec.
ftp> pwd
257 "/u01/app/backup"
ftp> bin
200 Switching to Binary mode.
ftp> put n1.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 10493952 bytes sent in 2.36Seconds 4446.59Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.

N1.DBF
n1.dmp
226 Directory send OK.
ftp: 18 bytes received in 0.00Seconds 18000.00Kbytes/sec.
ftp> bye
221 Goodbye.

>> Convert using RMAN as below



[oracle@Hostname backup]$ rman target "'sys/sys as sysdba'"

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 1 19:34:07 2011

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

connected to target database: ORCL (DBID=1248484971)

RMAN> convert datafile '/u01/app/backup/N1.DBF' to platform="Linux x86 64-bit" db_file_name_convert='/u01/app/backup/N1.DBF','/u01/app/oracle/oradata/orcl/N1.DBF';

Starting conversion at target at 02-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/app/backup/N1.DBF
converted datafile=/u01/app/oracle/oradata/orcl/N1.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished conversion at target at 02-JUN-11

Starting Control File and SPFILE Autobackup at 02-JUN-11
piece handle=/u02/RMAN_BACKUP/controlfile_c-1248484971-20110602-02 comment=NONE
Finished Control File and SPFILE Autobackup at 02-JUN-11


4. Import the transportable tablespace
    
>> Using the original import utility

[oracle@HOSTNAME backup]$ imp  "'sys/******* as sysdba'" file=/u01/app/backup/n1.dmp log=/u01/app/backup/tba_imp.log transport_tablespace=y datafiles='datafiles='/u01/app/oracle/oradata/orcl/N1.DBF''

Import: Release 11.2.0.1.0 - Production on Wed Jun 1 17:31:14 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing NAV's objects into NAV
. . importing table                         "TAP1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@kor300949lx1 backup]$

    
>> Using datapump

  CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';
  GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
     
  impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
  TRANSPORT_DATAFILES='/tmp/....'REMAP_SCHEMA=(source:target)
  REMAP_SCHEMA=(source_sch2:target_schema_sch2)

You can use REMAP_SCHEMA if you want to change the ownership of the transported database objects.
5. Put the tablespaces in read/write mode:

  SQL> alter tablespace n1 read write;

Tablespace altered.

Regards,
Navaneeth