Online database info: Oracle

Mostrando postagens com marcador Oracle. Mostrar todas as postagens

Error in invoking target 'agent nmhs' of makefile while installing oracle 11g

When installing Oracle 11g you may get the following error :-

Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.1.0/db_1/sysman/lib/ins_emagent.mk

Solution:-

Open the file it reports in error message.

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Search for the line
$(MK_EMAGENT_NMECTL)
Change it to:
$(MK_EMAGENT_NMECTL) -lnnz11

Note : edit this file while you still have the installer open, once you have changed it just click retry on the installer window.

Migrating databases from NON-ASM to ASM in oracle 12c

Migrating databases from NON-ASM to ASM in oracle 12c

Step 1.
Login to the database and write down the path of all the datafiles, tempfiles and redo log files.

SQL> select status,version,instance_name from v$instance;

STATUS VERSION INSTANCE_NAME
------------ ----------------- ----------------
OPEN 12.1.0.2.0 orclnew

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf USERS
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf UNDOTBS1
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf SYSTEM
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf SYSAUX

SQL> show parameter control_files;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCLNE
W/controlfile/o1_mf_dcldcd09_.
ctl

Step 2. Lets find out what are the disks available in ask disk group along with the free space.

SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- -------------------------
VOL1 NORMAL 9209 ORCL:VOL1
VOL2 NORMAL 9209 ORCL:VOL2
VOL3 NORMAL 9209 ORCL:VOL3

SQL> select NAME,GROUP_NUMBER,FREE_MB,TOTAL_MB from v$asm_diskgroup;

NAME GROUP_NUMBER FREE_MB TOTAL_MB
------------------------------ ------------ ---------- ----------
DATA 1 23719 27627

Now set the control file to point the ASM disk group DATA.

SQL> alter system set control_files='+DATA' scope=spfile;

System altered.

Step 3. Since our database in running on archivelog mode, lets change the db_recovery_file_dest location to ASM diskgroup DATA.

SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_SIZE=200M scope=spfile;

System altered.

Step 4. Shutdown and start the database in nomount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 440401920 bytes
Fixed Size 2925360 bytes
Variable Size 360713424 bytes
Database Buffers 71303168 bytes
Redo Buffers 5459968 bytes
SQL>

Step 5.
Connect to RMAN and restore the controlfile from the file system to the disk group DATA.

[oracle@server3 ~]$ rman target sys/redhat

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 3 15:12:56 2017

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

connected to target database: ORCLNEW (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCLNEW/controlfile/o1_mf_dcldcd09_.ctl';

Starting restore at 03-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLNEW/CONTROLFILE/current.271.937667637
Finished restore at 03-MAR-17

Step 6. After restoring the controlfile mount the database from rman.

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

Step 7. Now take the backup of the database in the ASM disk group DATA.

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 03-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_system_dcld7ty2_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/system.272.937667983 tag=TAG20170303T151941 RECID=1 STAMP=937668020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_sysaux_dcld611r_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 tag=TAG20170303T151941 RECID=2 STAMP=937668059
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_undotbs1_dcldbbm9_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 tag=TAG20170303T151941 RECID=3 STAMP=937668068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/ORCLNEW/CONTROLFILE/backup.275.937668073 tag=TAG20170303T151941 RECID=4 STAMP=937668074
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_users_dcldb9g3_.dbf
output file name=+DATA/ORCLNEW/DATAFILE/users.276.937668075 tag=TAG20170303T151941 RECID=5 STAMP=937668075
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-MAR-17
channel ORA_DISK_1: finished piece 1 at 03-MAR-17
piece handle=+DATA/ORCLNEW/BACKUPSET/2017_03_03/nnsnf0_tag20170303t151941_0.277.937668077 tag=TAG20170303T151941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-17

Step 8. Now change the NON ASM database to ASM by running the below command.
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/system.272.937667983"
datafile 3 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029"
datafile 4 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065"
datafile 6 switched to datafile copy "+DATA/ORCLNEW/DATAFILE/users.276.937668075"
RMAN>

Step 9. Now once the datafiles have been moved into ASM diskgroup we can open the database.
RMAN> alter database open;

Statement processed

Step 10. Once database has been opened determine the datafiles location

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/DATAFILE/users.276.937668075 USERS
+DATA/ORCLNEW/DATAFILE/undotbs1.274.937668065 UNDOTBS1
+DATA/ORCLNEW/DATAFILE/system.272.937667983 SYSTEM
+DATA/ORCLNEW/DATAFILE/sysaux.273.937668029 SYSAUX

SQL>

Step 11.After moving the datafiles into ASM diskgroup , we still need to move the temp tablespace and redolog files into ASM diskgroup.
SQL> select file_name,tablespace_name from dba_Temp_Files;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp TEMP

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
3 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_3_dcldchys_.log
2 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_2_dcldcgk9_.log
1 /u01/app/oracle/oradata/ORCLNEW/onlinelog/o1_mf_1_dcldcfg7_.log

Step 11. To move the temp tablespace we need to drop the existing temp files first then recreate it into ASM diskgroup

SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLNEW/datafile/o1_mf_temp_dcldd5h8_.tmp' drop including datafiles;

Database altered.

SQL> alter tablespace TEMP add tempfile '+DATA' size 150M autoextend on;

Tablespace altered.

SQL> select file_name,tablespace_name from dba_Temp_Files;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/ORCLNEW/TEMPFILE/temp.278.937669453 TEMP

SQL>

Step 12. Now move the redolog group into ASM diskgroup

For this first we will drop the inactive redo log group and then recreate it.
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE

Here log group 2 and log group 3 are INACTIVE. Hence, we can drop them and recreate the log groups 2 and 3.

Change default location of online redo log files as +DATA disk group.

SQL> alter system set db_create_online_log_dest_1='+DATA' SCOPE=BOTH;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2;

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3;

SQL>alter system switch logfile;

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1;

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
3 ONLINE +DATA/ORCLNEW/ONLINELOG/group_3.280.937672657 NO 0
2 ONLINE +DATA/ORCLNEW/ONLINELOG/group_2.279.937672551 NO 0
1 ONLINE +DATA/ORCLNEW/ONLINELOG/group_1.285.937672783 NO 0

Now finally all physical datafiles/logfiles have been moved from NON ASM TO ASM.

How to clone a database using cold backup and restore it with different SID on a new host server

Objective :-We are cloning a database running on target server and restoring it into clone server with a different sid.

Note- Make sure oracle binary is already installed on target db server.In this case the directory structure of target server is different than source server.

Database Version :- Oracle Enterprise Edition 11.2.0.1 on RHEL 6.4 on both server

Source Server: 192.168.0.102
Database sid:- prim
Hostname:-server1.soumya.com

Destination Server: 192.168.0.104
database sid: newprim
Hostname:-server2.soumya.com

1.Take backup of controlfile as trace:[SOURCE DB]

SQL> alter database backup controlfile to trace as '/u01/app/oracle/control01.sql';
Database altered.

2. check the location of datafiles[SOURCE DB]

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf

3. Shutdown the database:[SOURCE DB]
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Copy the datafiles to the target db server
[oracle@server1 ]$cd /u01/app/oracle/oradata/prim

[oracle@server1 backup]$ scp *.dbf oracle@server2:/u01/
The authenticity of host 'server2 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server2,192.168.0.104' (RSA) to the list of known hosts.
oracle@server2's password:
example01.dbf 100% 100MB 25.0MB/s 00:04
sysaux01.dbf 100% 500MB 16.7MB/s 00:30
system01.dbf 100% 670MB 19.7MB/s 00:34
temp01.dbf 100% 20MB 6.7MB/s 00:03
undotbs01.dbf 100% 55MB 27.5MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:00

Also copy the controlfile which we took backup at step 1 and transfer it into target server.

5.Make changes in the init file for target db:[TARGET DB]
We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file localtion,audit_file_dest location,diag location etc.

[oracle@server2 dbs]$ cat initnewprim.ora

newprim.__db_cache_size=138412032
newprim.__java_pool_size=4194304
newprim.__large_pool_size=4194304
newprim.__oracle_base='/u01/newapp/oracle'#ORACLE_BASE set from environment
newprim.__pga_aggregate_target=167772160
newprim.__sga_target=247463936
newprim.__shared_io_pool_size=0
newprim.__shared_pool_size=92274688
newprim.__streams_pool_size=0
*.audit_file_dest='/u01/newapp/oracle/admin/newprim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/newapp/oracle/oradata/newprim/control01.ctl','/u01/newapp/oracle/flash_recovery_area/newprim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newprim'
*.db_recovery_file_dest='/u01/newapp/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/newapp/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newprimXDB)'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Create necessary directories as per new sid

[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/admin/newprim/adump
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/oradata/newprim/
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/flash_recovery_area/newprim/

6. Now Start the database in nomount stage:[TARGET DB]
[oracle@server2 dbs]$ export ORACLE_SID=newprim

[oracle@server2 dbs]$sqlplus / as sysdba
SQL> startup nomount pfile='/u01/newapp/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 268437496 bytes
Database Buffers 138412032 bytes
Redo Buffers 4308992 bytes

7. Re-recreate the controlfile [ TARGET DB ]

Open the controlfile and remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change directory name everywhere from 'prim' to 'newprim'
:wq

The content should look like this

[oracle@server2 u01]$ vi control01.sql

CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/newprim/system01.dbf',
'/u01/app/oracle/oradata/newprim/sysaux01.dbf',
'/u01/app/oracle/oradata/newprim/undotbs01.dbf',
'/u01/app/oracle/oradata/newprim/users01.dbf',
'/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;

8.Create the controlfile at target db.

SQL>@/u01/control01.sql
Control file created.

9.Open the database in resetlog mode:

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

10.Create the temp files.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/newapp/oracle/oradata/newprim/temp_01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

How to clone a database using user managed hot backup in oracle database 11g

How to clone a database using user managed hot backup in oracle database 11g

Objective :-We are cloning a database running on target server and restoring it into clone server.

Database Version :- Oracle Enterprise Edition 11.2.0.1 on RHEL 6.4

Target Server: 192.168.0.101
Database sid:- prim

Clone Server: 192.168.0.104
database sid: newprim

****At target Database

Take online backup of database

Before taking the online backup we need to put the database into archivelog mode.

$ export ORACLE_SID=prim

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>alter database begin backup;
Database altered.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /
SQL> commit;
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1067890 01-MAY-18
2 ACTIVE 1067890 01-MAY-18
3 ACTIVE 1067890 01-MAY-18
4 ACTIVE 1067890 01-MAY-18
5 ACTIVE 1067890 01-MAY-18

SQL> exit

Now switch into the datafile directory and copy all the .dbf and .log files into the backup directory

[oracle@server1]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ cp -rpf *.log /home/oracle/bkup/
[oracle@server1 prim]$ cp -rpf *.dbf /home/oracle/bkup/

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1067890 01-MAY-18
2 NOT ACTIVE 1067890 01-MAY-18
3 NOT ACTIVE 1067890 01-MAY-18
4 NOT ACTIVE 1067890 01-MAY-18
5 NOT ACTIVE 1067890 01-MAY-18

Now take backup of controlfile
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.

Now edit the content of control file as per below.

[oracle@server1 ~]$vi /home/oracle/control.sql
Remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change folder name everywhere from 'prim' to 'newprim'
:wq

The content should look like this

CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/newprim/system01.dbf',
'/u01/app/oracle/oradata/newprim/sysaux01.dbf',
'/u01/app/oracle/oradata/newprim/undotbs01.dbf',
'/u01/app/oracle/oradata/newprim/users01.dbf',
'/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;

Now transfer all the datafiles, controlfile, redolog files into clone server using scp.

[oracle@server1 ~]$ scp control.sql oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
The authenticity of host '192.168.0.104 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.104' (RSA) to the list of known hosts.
oracle@192.168.0.104's password:
control.sql 100% 698 0.7KB/s 00:00
[oracle@server1 ~]$ cd bkup/
[oracle@server1 bkup]$ ll
total 1534024
-rw-r-----. 1 oracle oinstall 104865792 May 1 20:23 example01.dbf
-rw-r-----. 1 oracle oinstall 52429312 May 1 20:33 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 1 20:23 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 1 20:23 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 May 1 20:29 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 May 1 20:33 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 May 1 19:58 temp01.dbf
-rw-r-----. 1 oracle oinstall 57679872 May 1 20:31 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 May 1 20:33 users01.dbf
[oracle@server1 bkup]$ scp * oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
oracle@192.168.0.104's password:
example01.dbf 100% 100MB 50.0MB/s 00:02
redo01.log 100% 50MB 50.0MB/s 00:01
redo02.log 100% 50MB 25.0MB/s 00:02
redo03.log 100% 50MB 25.0MB/s 00:02
sysaux01.dbf 100% 500MB 19.2MB/s 00:26
system01.dbf 100% 670MB 12.6MB/s 00:53
temp01.dbf 100% 20MB 20.0MB/s 00:01
undotbs01.dbf 100% 55MB 55.0MB/s 00:00
users01.dbf 100% 5128KB 5.0MB/s 00:00
[oracle@server1 bkup]$ cd
[oracle@server1 ~]$ ll

****At clone server

Create necessary directory structure for clone database.
mkdir -p /u01/app/oracle/oradata/newprim/
mkdir -p /u01/app/oracle/admin/newprim/adump

export ORACLE_SID=newprim
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 268437496 bytes
Database Buffers 138412032 bytes
Redo Buffers 4308992 bytes

Now recreate control file.
SQL> @/u01/app/oracle/oradata/newprim/control.sql;

Control file created.

Now recover the database.

sql> recover database using backup controlfile until cancel;
here one by one apply all archivelog files
apply all 3 redo log files with full path until we get the msg that recovery is done.

Here its better to notedown the current redo log file and the pending archivelogs from target db at the time of backup. We need to apply only those
archives and only one current redo log file.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1067890 generated at 05/01/2018 20:23:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.
arc
ORA-00280: change 1067890 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/newprim/redo01.log
Log applied.
Media recovery complete.

Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now the process of cloning a database is complete into a new server using user managed backup.
Thanks

Oracle WebLogic Server (WLS) 11gR1 (10.3.6) Installation on RHEL 6

Download oracle weblogic server 10.3.6 from oracle site:-
# wget http://download.oracle.com/otn/nt/middleware/11g/wls/1036/wls1036_generic.jar

Also download java development kit(jdk):-

# wget http://download.oracle.com/otn-pub/java/jdk/8u121-b13/e9e7ea248e2c4826b92b3f075a80e441/jdk-8u121-linux-i586.tar.gz

Create the directories in which the Oracle weblogic software will be installed.

Add users and groups
groupadd -g 501 oinstall
groupadd -g 502 dba

useradd -u 502 -g oinstall -G dba oracle
passwd oracle

mkdir -p /u01/app/oracle/product/fmw11g
mkdir -p /u01/app/oracle/config/domains
mkdir -p /u01/app/oracle/config/applications
chown -R oracle:oinstall /u01
chmod -R 775 /u01/

#yum -y install glibc-devel.i686
yum install libaio-devel -y
yum install sysstat -y
yum install openmotif -y
yum install openmotif22 -y

[root@server1 mnt]# alternatives --config java

There are 4 programs which provide 'java'.

Selection Command
-----------------------------------------------
1 /usr/lib/jvm/jre-1.5.0-gcj/bin/java
2 /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
3 /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
*+ 4 /usr/java/jdk1.8.0_121/jre/bin/java

Enter to keep the current selection[+], or type selection number: 4

Now set the enviroments for oracle user:-
[root@server1 mnt]su - oracle
Add the following lines in .bash_profile

[oracle@server1 ]vi .bash_profile

export MW_HOME=/u01/app/oracle/product/fmw11g
export WLS_HOME=$MW_HOME/wlserver_10.3
export WL_HOME=$WLS_HOME
# Set to the appropriate JAVA_HOME.
export JAVA_HOME=/usr/java/jdk1.8.0_121
#export JAVA_HOME=/u01/app/oracle/jrockit-jdk1.6.0_45-R28.2.7-4.1.0
#export JAVA_HOME=/u01/app/oracle/jdk1.7.0_17
export PATH=$JAVA_HOME/bin:$PATH
:wq

Change permission and ownership of web
[root@server1 ]chown -Rf oracle:oinstall wls1036_generic.jar
[root@server1 ]chmod 775 wls1036_generic.jar

From oracle user:-

[oracle@server1 ] cd /u01
[oracle@server1 u01]$ $JAVA_HOME/bin/java -Xmx1024m -jar wls1036_generic.jar

The installation windown will start -> Provide middleware home directory "/u01/app/oracle/product/fmw11g" -> Uncheck the option to get oracle support via
email -> Select "i wish to remain uninformed option" -> continue "Typical installation" -> It should auto select the jdk installed home -> next -> next > finish.

How to migrate oracle users from one server to another

1. First take all the information using dbm_metadata.

set head off
set pages 0
set long 9999999

select dbms_metadata.get_ddl('USER', username) || '/' usercreate
from dba_users;

ALTER USER "SYS" IDENTIFIED BY VALUES 'S:1CCB2C8B206B3D3BA9164214F3262051E4C0
D076179455300F8674EDBC85;E100B964899CDDDF'
TEMPORARY TABLESPACE "TEMP"
/

ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:DC73635359324A39D3636017D54F482E7
F3D7CC34D0426EA3C9628FFD120;970BAA5B81930A40'
TEMPORARY TABLESPACE "TEMP"
/
CREATE USER "SOUMYA" IDENTIFIED BY VALUES 'S:3F6E037E8574BC8FB1F0CF44651E0DE9
7C8765DC0924224F19F7F43D583B;6673D7515E467AA4'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
/

To get all their roles and grants:-

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SYS') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYS') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SYS') FROM DUAL;

To script it in a single command:-
spool '/u01/app/dbusersinfo.sql'
select dbms_metadata.get_ddl( 'USER', 'HR' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'HR' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'HR' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'HR' ) from dual;
spool off

GRANT SELECT ON "HR"."D01" TO "SCOTT"

GRANT SELECT ON "HR"."FILOM" TO "SCOTT"

GRANT SELECT ON "HR"."HD01" TO "SCOTT"

GRANT SELECT ON "HR"."HSPRF" TO "SCOTT"

GRANT SELECT ON "HR"."SPRF" TO "SCOTT"

GRANT SELECT ON "HR"."HFILOM" TO "SCOTT"

GRANT "CONNECT" TO "SCOTT"

GRANT "RESOURCE" TO "SCOTT"

Now run the above output into database server where you want to migrate the users.

How to setup Oracle ASM 12c on Linux

Software used:-
1.VMWARE 10
2.Redhat enterprise linux 6.5(64 bit)
3.Oracle database 12C (64 bit)
4.Oracle Grid infrastructure 12c(64 bit)

What to Setup:-

1. Setup oracle grid infrastructure for standalone server "ASM"
2. Setup oracle Database

Update /etc/sysctl.conf
[root@server1]# vi /etc/sysctl.conf
Scroll to the bottom and add the following:

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
:wq

Run the following command to change the current kernel parameters.
/sbin/sysctl -p

Update /etc/security/limits.conf
[root@server1]# vi /etc/security/limits.conf
Scroll to the bottom and above the “# End of file” line, add:

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

:wq

Amend the "/etc/security/limits.d/90-nproc.conf" file as described below.
# Change this
* soft nproc 1024

# To this
* - soft nproc 16384

**IMPORTANT: Make sure selinux is disabled.

Packages required for oracle database installation:-

[root@server1] yum -y install binutils-2.17.50.0.6
[root@server1] yum -y install compat-libstdc++-33-3.2.3 (*)
[root@server1] yum -y install elfutils-libelf-0.125
[root@server1] yum -y install elfutils-libelf-devel-0.125 (*)
[root@server1] yum -y install gcc-4.1.2
[root@server1] yum -y install gcc-c++-4.1.2 (*)
[root@server1] yum -y install glibc-2.5-24
[root@server1] yum -y install glibc-common-2.5
[root@server1] yum -y install glibc-devel-2.5
[root@server1] yum -y install glibc-headers-2.5
[root@server1] yum -y install ksh-20060214 (*)
[root@server1] yum -y install libaio-0.3.106
[root@server1] yum -y install libaio-devel-0.3.106
[root@server1] yum -y install libgcc-4.1.2
[root@server1] yum -y install libgomp-4.1.2
[root@server1] yum -y install libstdc++-4.1.2
[root@server1] yum -y install libstdc++-devel-4.1.2
[root@server1] yum -y install make-3.81
[root@server1] yum -y install numactl-devel-0.9.8.i386 (*)
[root@server1] yum -y install sysstat-7.0.2 (*)

Check the kernel version:-
[root@server1 var]# uname -r
2.6.32-358.el6.x86_64

Now we would require to install the rpm's required for asm installation
these are the packages required for asm.

--oracleasm
--oracleasm-support
--oracleasmlib
the last 2 packages can be found from the following link
http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html

oracle kmod-oracleasm rpm download link for el6
http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/getPackage/kmod-oracleasm-2.0.6.rh1-2.el6.x86_64.rpm

[root@server3 ~]# rpm -Uvh oracleasm-support-2.1.8-1.el6.x86_64.rpm
[root@server3 ~]# rpm -Uvh kmod-oracleasm-2.0.6.rh1-2.el6.x86_64.rpm
[root@server3 ~]# rpm -Uvh oracleasmlib-2.0.4-1.el6.x86_64.rpm

Create groups:-
[root@server3 tmp]# groupadd -g 1000 oinstall
[root@server3 tmp]# groupadd -g 1200 dba
[root@server3 tmp]# useradd -g oinstall -G dba -d /home/oracle oracle

Create directory structures:-
[root@server3 u01]# mkdir -p /u01/app/oracle/product/12.1.0/grid
[root@server3 u01]# mkdir -p /u01/app/oracle/product/12.1.0/db_1

Assigning proper permission:-
[root@server3 u01]# chown -Rf oracle:oinstall /u01/
[root@server3 u01]# chmod -Rf 775 /u01/

Set up the oracle user environment
For oracle user:-
[root@server1 var]# su - oracle

[oracle@server1]#vi .bash_profile
#export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=server3.soumya.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/oracle/product/12.1.0/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/12.1.0/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATHH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'

:wq(save & exit)

[oracle@server1 ~]$ . .bash_profile

Create a file called "/home/oracle/db_env" with the following contents :-

[oracle@server1 ~]$vi /home/oracle/db_env

ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq(save & exit)

Create a file called "/home/oracle/grid_env" with the following contents:-

[oracle@server1 ~]$vi /home/oracle/gid_env
ORACLE_SID=+ASM; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq(save & exit)

[oracle@server1 ~]$ chmod 775 /home/oracle/db_env
[oracle@server1 ~]$ chmod 775 /home/oracle/grid_env

Now you will be able to switch environments between oracle and asm instance as follows.
[oracle@server3 ~]$ db_env
[oracle@server3 ~]$ echo $ORACLE_SID
orcl
[oracle@server3 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@server3 ~]$ grid_env
[oracle@server3 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/grid
[oracle@server3 ~]$ echo $ORACLE_SID
+ASM

Now we will add 3 disks using vmware.
So open vmware workstation and go to settings and add hard disk from there,add 3 different disks size of atleast 10GB each.

[root@server1]# echo "- - -"> /sys/class/scsi_host/host0/scan

******
P.S. if the above command doesnt show the newly added disk try this
[root@server1]#grep mpt /sys/class/scsi_host/host?/proc_name
/sys/class/scsi_host/host2/proc_name:mptspi

then run this
[root@server1]# echo "- - -"> /sys/class/scsi_host/host2/scan
******

using the above command we can avoid rebooting the machine to mount the hard disks.

[root@server1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa4bd7fb9.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-261, default 261):
Using default value 261

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@server1 ~]# fdisk /dev/sdc
[root@server1 ~]# fdisk /dev/sdd

[root@server1 dev]# fdisk -l
Disk /dev/sda: 64.4 GB, 64424509440 bytes
255 heads, 63 sectors/track, 7832 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0006f980

Device Boot Start End Blocks Id System
/dev/sda1 1 5737 46080000 83 Linux
/dev/sda2 * 5737 6885 9216000 83 Linux
/dev/sda3 6885 7458 4608000 83 Linux
/dev/sda4 7458 7833 3009536 5 Extended
/dev/sda5 7459 7731 2188288 82 Linux swap / Solaris
/dev/sda6 7731 7833 818176 83 Linux

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x44ac96a0

Device Boot Start End Blocks Id System
/dev/sdb1 1 261 2096451 83 Linux

Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x004b1011

Device Boot Start End Blocks Id System
/dev/sdc1 1 261 2096451 83 Linux

Disk /dev/sdd: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xf5898159

Device Boot Start End Blocks Id System
/dev/sdd1 1 261 2096451 83 Linux

Give proper ownership and permissions to the new partition:-
chown -Rf oracle:oinstall /dev/sdb1
chown -Rf oracle:oinstall /dev/sdc1
chown -Rf oracle:oinstall /dev/sdd1

chmod -Rf 664 /dev/sdb1
chmod -Rf 664 /dev/sdc1
chmod -Rf 664 /dev/sdd1

Now configure ASM and create ASM disks:-

[root@server1 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

To create ASM disks:-
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1

Now we will install grid infrastructure software.
Give proper permission to the software folder.

[root@server1 ] chown -Rf oracle:oinstall /u01/linuxamd64_12102_grid_1of2.zip
root@server1 ] chown -Rf oracle:oinstall /u01/linuxamd64_12102_grid_2of2.zip

[root@server1 u01]# unzip linuxamd64_12102_grid_1of2.zip
[root@server1 u01]# unzip linuxamd64_12102_grid_2of2.zip
[root@server1 u01]#su - oracle
[oracle@server1 u01]$ cd grid/
[oracle@server1 grid]$ sh runInstaller

Select "install and configure grid infrastructure for a standalone server" -> Next -> select 3 Disks from candidate disk option rest option will be unchanged ->
select "use same password for these accounts " and provide password -> specify os groups OSDBA-oinstall,
OSOPER-oinstall, OSASM-oinstall -> Select Install location "oracle base- /u01/app/oracle " , "software location- /u01/app/oracle/product/12.1.0/grid" -> Next and
start the installation.> execute "/u01/app/oracle/product/12.1.0/grid/root.sh" script from root user from another terminal.

I got this error while installing grid infrastructure.To fix this steps are below:-
**INFO: Read: ORA-00845: MEMORY_TARGET not supported on this system

To increase the size
# mount -o remount,size=3G /dev/shm
Verify the size
# df -h
To make permanent changes to your file system update your fstab
# vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=3G 0 0

[root@server1 u01]#

[grid@server3 app]$ sqlplus / as sysasm
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+ASM

Now we will setup oracle database .

[oracle@server1 u01]$ cd /u01/database/
[oracle@server3 u01]$ sh runInstaller

select "Create & configure a database" -> Server Class -> Single instance database installation -> Advanced Installa -> Next ->Enterprise edition ->
Select "oracle base- /u01/app/oracle " , "software location- /u01/app/oracle/product/12.1.0/db_1" -> select "general purpose" -> Global database
name- orcl , SID name - orcl -> Next -> select " Oracle automatic storage management"-> Next ->Next ->Next-> select " use same password for all accounts"->
Next -> Next -> Install

Done!!!...

Oracle Alert log monitoring using shell script

Vi /backups/alertlog_monotor.sh

#!/bin/bash
export ORACLE_SID=kmioradb
export ORAENV_ASK=NO
. /home/oracle/.bash_profile
SERNAME=oradb.kminfosystems.com
SERPUBIP=192.168.72.50
SERVER=`hostname` #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'` #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Alert_log_loc=/u01/app/oracle/diag/rdbms/kmioradb/kmioradb/trace/
# Check for the existence of ORA- in the alert log and email/page on error
egrep 'ORA-|error|TNS' $Alert_log_loc/alert_$ORACLE_SID.log |sort -u > $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt #### Output file with ORA- errors
cat $Alert_log_loc/alert_$ORACLE_SID.log >> $Alert_log_loc/archived_alert_$ORACLE_SID.log
cat /dev/null > $Alert_log_loc/alert_$ORACLE_SID.log

if [ -s "$Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt" ] ; then
cat $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " soumya@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
mv $Alert_log_loc/archived_alert_${ORACLE_SID}.log $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
gzip $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Invisible index on oracle 11g

Database version:- oracle 11.2.0

Invisible Indexes, which are basically indexes that exist and are maintained by Oracle but are "invisible" to the Cost based optimizer(CBO). Specific sessions can be set to see these
invisible indexes as necessary.The invisible index is an alternative of dropping or making an index unusable. This feature is also functional when certain modules of an application
require a specific index without affecting the rest of the application.

First, create a simple table and associated index.
SQL> conn soumya/soumya
SQL> set autotrace on
SQL> set linesize 200

SQL> create table soumya_test (Rollno number(5),name varchar2(20));
Table created.

SQL> create index soumya_test_ind on soumya_test(Rollno);
Index created.

Now insert data into the table.
SQL> begin
2 for x in 1..1000 loop
3 insert into soumya_test ( Rollno , name)
4 values ( x , default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

By default, indexes are created as "VISIBLE" .
SQL> SELECT table_name,index_name, visibility FROM user_indexes WHERE index_name = 'SOUMYA_TEST_IND';

TABLE_NAME INDEX_NAME VISIBILITY
------------------------------ ------------------------------ ----------
SOUMYA_TEST SOUMYA_TEST_IND VISIBLE

SQL>

As the index is visible, it can be considered and used by the CBO .

Here we checked our indexed column by user_ind_columns.
We are now going to check how the query will process by the oracle server.

SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

19 rows selected.

Now alter the visibility of index SOUMYA_TEST_IND.
SQL> alter index SOUMYA_TEST_IND invisible;

Index altered.

Check the index status.
SQL> select index_name,visibility from user_indexes
where table_name='SOUMYA_TEST';

INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE

As, we can see that index is invisible now oracle wont use it.
now we again check the same explain plan for same query and compare the results.

SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

In above explain plan we see oracle didn't use the index like it did in previous query.

Now change back the visibility once again.

SQL> alter index SOUMYA_TEST_IND visible;

Index altered.

any specific sessions can be altered so they can "see" these invisible indexes.
SQL> select index_name,visibility from user_indexes
2 where table_name='SOUMYA_TEST';

INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

We see that optimizer is still not using the invisilbe index so we need to query the explain plan again.

SQL> explain plan for
select * from soumya_test where Rollno in (555,726,959);
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

19 rows selected.

Now we see the invisible index is being used.

How to drop undo tablespace in oracle 11g?

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE

SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

Now try to drop the old undotablespace.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs02.dbf UNDOTBS2
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE

Last Login Time for nonsys user in oracle 12c

Sqlplus shows Last Login Time for non system users in oracle12c.

In oracle 12c a new security feature has been added which allows us to check when did a non system user logged in.

[oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:35:28 2016

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

Last Successful login time: Mon Feb 15 2016 08:35:14 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

The feature can be disabled by doing the following:-
[oracle@server3 ~]$ sqlplus -nologintime soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:45:09 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

The actual information is basically stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.

[oracle@server3 ~]$ sqlplus / as sysdba
SYS@ORA12C> col username FOR a15
SYS@ORA12C> col last_login FOR a25
SYS@ORA12C> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SYS@ORA12C> SELECT username,last_login FROM dba_users WHERE username='SOUMYA';

USERNAME LAST_LOGIN
--------------- -------------------------
SOUMYA 15.02.2016 08:45:09

SYS@ORA12C> col name FOR a15
SYS@ORA12C> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
col spare6 for a40
Session altered.

SYS@ORA12C> SELECT name,spare6 FROM USER$ WHERE name='SOUMYA';

NAME SPARE6
--------------- -------------------
SOUMYA 15.02.2016 03:15:09

oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:52:32 2016

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

Last Successful login time: Mon Feb 15 2016 08:45:09 +05:30

Expdp Backup script with retention for Windows

Oracle Version:- 11g

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file. Schedule it on task scheduler
as per your requirement.Also make sure you have 7zip installed on your machine for the zip purpose.

@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
set BACKUPPATH=C:\app\Oracle11G\Datapump
forfiles /p "C:\app\Oracle11G\Datapump" /m * /d -7 /c "cmd /c rd /s /q @path"
cd %BACKUPPATH%
mkdir "C:\app\Oracle11G\Datapump\%mydate%_%mytime%"

%ORACLE_HOME%\BIN\expdp system/system directory=BACKUP Full=Y dumpfile=backup_%mydate%_%mytime%.dmp logfile=log_%mydate%_%mytime%.log exclude=statistics
cd %BACKUPPATH%
"C:\Program Files\7-Zip\7z.exe" a -mx9 C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\backup_%mydate%_%mytime%.dmp
move C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
move C:\app\Oracle11G\Datapump\log_%mydate%_%mytime%.log C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
del backup_%mydate%_%mytime%.dmp

Oracle 11.2.0.1 download link for Windows 7 64bit

Oracle 11.2.0.1 download link for Windows 7 64bit :-

win64_11gR2_database_1of2 - https://onedrive.live.com/redir?resid=EB956C947D7F25EA!117&authkey=!AC89kV_gQ4Zh6Ug&ithint=file%2czip

win64_11gR2_database_2of2 - https://onedrive.live.com/redir?resid=EB956C947D7F25EA!118&authkey=!AIH8tdcmUzPK9XM&ithint=file%2czip

Monitoring Temporary tablespace usage using shell script

The following script will report if temporary tablespace hits 80% of its usage.

From oracle user:
[oracle@server1 ~]$ vi /home/oracle/check_temp_tbs.sql

set head off
set wrap off
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
exit;

Now lets create the shell-script which will monitor the space usage of temp tablespaces:-
From oracle user

[oracle@server1 ~]$ vi /home/oracle/alert_temp.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/check_temp_tbs.sql > /home/oracle/temptbs.txt
space=`cat /home/oracle/temptbs.txt`
if [ $space -gt 80 ]; then
mail -s echo "Attention!! Low space in Temporary Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert_temp.sh > /dev/null

P.S.Make sure mailx rpm is installed and any mailing service such as sendmail/postfix is running on the server.
To install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

What is Standby snapshot Database?

What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back
into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby
database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions
which were made in standby database while it was open in READ WRITE mode will be flushed out.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled.

Monitoring Tablespace usage using shell script

The following script will report if any tablespace (except temporary tablespace ) hits 85% of its usage.

From oracle user:
[oracle@server1 ~]$ vi /home/oracle/checktbs.sql

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 15
order by pct_free;
exit;

:wq(save & exit)

Now lets create the shell-script which will monitor the space usage of tablespaces:-
From oracle user
[oracle@server1 ~]$ vi /home/oracle/alert.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/checktbs.sql > /home/oracle/test.txt
file1=cat /home/oracle/test.txt
space=`cat /home/oracle/test.txt | wc -l`
if [ $space -gt 3 ]; then
$file1 | mail -s echo "Attention!! Low space Certain Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert.sh > /dev/null

P.S.Make sure mailx rpm is installed and any mailing service such as sendmail/postfix is running on the server.
To install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

How to find out how many times a database has been restared since its creation

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;

STARTUP_TIME
—————————————————————————
29-SEP-13 03.43.07.000 AM
29-SEP-13 03.42.31.000 AM
29-SEP-13 03.12.45.000 AM
29-SEP-13 03.11.28.000 AM
28-SEP-13 11.56.35.000 PM
07-SEP-13 10.04.07.000 PM

RMAN Full Backup script for Windows

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file and schedule it in task scheduler.

@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set RUNDIR=C:\TEMP
set LOGDIR=C:\oracle\log
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
rem set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS'
set BACKUPDIR=c:\oracle\oradata\backup\%mydate%_%mytime%
set LOGFILE=%LOGDIR%\%ORACLE_SID%_%mydate%_%mytime%.log
set ADDR='job@jobacle.nl'
set TMPLOG=%LOGDIR%\tmplog.$$
mkdir "c:\oracle\oradata\backup\%mydate%_%mytime%""

set CMDFILE=%RUNDIR%\%ORACLE_SID%.rman
echo run { > %CMDFILE%
echo CONFIGURE RETENTION POLICY TO REDUNDANCY 2; >> %CMDFILE%
echo CONFIGURE BACKUP OPTIMIZATION ON; >> %CMDFILE%
echo CONFIGURE DEFAULT DEVICE TYPE TO DISK; >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP ON; >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%BACKUPDIR%\ora_cf%%F'; >> %CMDFILE%
echo CONFIGURE DEVICE TYPE DISK PARALLELISM 2; >> %CMDFILE%
echo CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; >> %CMDFILE%
echo CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo CONFIGURE MAXSETSIZE TO UNLIMITED; >> %CMDFILE%
echo CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%BACKUPDIR%\snapcf_%ORACLE_SID%_%mydate%_%mytime%.f'; >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo SHOW ALL; >> %CMDFILE%
echo sql "alter system archive log current"; >> %CMDFILE%
echo DELETE FORCE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2'; >> %CMDFILE%
echo BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL=0 DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG; >> %CMDFILE%
echo sql "alter database backup controlfile to trace as ''%BACKUPDIR%\ctrlfile_%ORACLE_SID%_%mydate%_%mytime%''"; >> %CMDFILE%
echo CROSSCHECK BACKUP; >> %CMDFILE%
echo REPORT OBSOLETE; >> %CMDFILE%
echo DELETE FORCE NOPROMPT OBSOLETE; >> %CMDFILE%
echo } >> %CMDFILE%

echo LIST BACKUP OF DATABASE; >> %CMDFILE%
echo LIST BACKUP OF ARCHIVELOG ALL; >> %CMDFILE%
echo LIST BACKUP; >> %CMDFILE%

echo Starting the script > %LOGFILE%
%ORACLE_HOME%\bin\rman target / @%CMDFILE% msglog=%TMPLOG% >>%LOGFILE%

type %TMPLOG% >> %LOGFILE%

copy "%TMPLOG%" "%BACKUPDIR%\%mydate%_%mytime%.log"
@echo on

Transportable tablespace in oracle 11g

Oracle Version : oracle 11g R2

What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.

Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.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.
5. You cannot transport an encrypted tablespace to a platform with different endianness.

Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,

Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------- --------------
13 Linux x86 64-bit Little

Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------- --------------
13 Linux x86 64-bit Little

Now let me create a tablespace to demonstrate the feature.

Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.

SQL> CREATE USER tts_user IDENTIFIED BY tts_user DEFAULT TABLESPACE tts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;

User created.

SQL> grant connect , resource , create table to tts_user;

Grant succeeded.

SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );

SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.
SQL> select count(*) from test_tbl;

COUNT(*)
----------
10000

Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);

PL/SQL procedure successfully completed.

P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;

no rows selected

Now , put the tablespace in read only mode which we are going to move.

SQL> alter tablespace tts read only;

Tablespace altered.

Now , Export the tablespace metadata using expdp utility.

[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016

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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57

Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf 100% 10MB 10.0MB/s 00:00

[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp 100% 96KB 96.0KB/s 00:00

Now the source tablespace can be switched back to read write mode.
SQL> alter tablespace tts read write;

Tablespace altered.

At Destination DB:-

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

SQL> CONN / AS SYSDBA

SQL> CREATE USER tts_user IDENTIFIED BY tts_user;

User created.

SQL> GRANT connect , resource, create table TO tts_user;

Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'

Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015

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
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TTS READ ONLY

SQL> alter tablespace tts read write;

Tablespace altered.

SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TTS YES ONLINE

SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;

COUNT(*)
----------
10000