Problem's Solutions for Oracle

Thursday, February 12, 2015

Make ur life like a database

Make ur life like a database

Make ur life as deep as a database
Be always as unique as a primary key
Be productive as an index
Take others good habits as a foreign key.....

Share ur joys with others as a cluster
Keep note of your activities as good as data dictionary
Grant some privileges to others as a role
Secure the things as a dba....

Do the things in order as a rowid
Follow ur resolution as strict as a sequence
Light others life as a trigger
Be prepared for the worst as an exception....

Always be fruitful as a function
Forgive others as a rollback transaction
Always help others like checkpoint
Choose the best path like an optimizer....

Project yourself the best like a view
Don't expect anything from others(procedures) and
Be thankful to GOD as a commit transaction....

Wednesday, September 24, 2014

Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup:-

On the primary:

SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
ORCL PRIMARY UNRESOLVABLE GAP

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2076457

On the standby:

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1998045

Solution:-

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;
Database altered.

2. [Standby] Shutdown the standby database;

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck;

RMAN> backup incremental from scn 1998045 database;

Starting backup at 15-SEP-14

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
backup will be obsolete on date 22-SEP-14
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/MONEY.DBF
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/29pij5mb_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

using channel ORA_DISK_1
backup will be obsolete on date 22-SEP-14
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/2apij5p1_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-SEP-14

4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/neeraj/stdb_cont.ctl';
Database altered.

5. [Primary] Copy these files to standby host:

[oracle@localhost ~]$ cd /u01/neeraj/
[oracle@localhost neeraj]$ ls
29pij5mb_1_1 2apij5p1_1_1 stdb_cont.ctl
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$ scp * oracle@192.168.204.129:/u01/tspr
Address 192.168.204.129 maps to localhost.localdomain, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.204.129's password:
29pij5mb_1_1 100% 59MB 19.7MB/s 00:03
2apij5p1_1_1 100% 11MB 11.1MB/s 00:00
stdb_cont.ctl 100% 11MB 11.1MB/s 00:01
[oracle@localhost neeraj]$

6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 201328768 bytes
Database Buffers 864026624 bytes
Redo Buffers 4636672 bytes

7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/stdb/control01.ctl --- "rename or remove(control01_old.ctl)"

8. [Standby] Replace the controlfile with the one you just created in primary. and rename the existing file which is already exist on the standby.

$ cp /u01/tspr/stdb_cont.ctl /u01/app/oracle/oradata/stdb/control01.ctl

9.[Standby] Mount the standby database:

SQL> alter database mount standby database;

Database altered.

10 .[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

RMAN> catalog start with '/u01/tspr';

searching for all files that match the pattern /u01/tspr

List of Files Unknown to the Database
=====================================
File Name: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_1_1

11 .Recover these files:

RMAN> recover database;

Starting recover at 15-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:37:55
ORA-19870: error while restoring backup piece /u01/tspr/29pij5mb_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 6

if you will get this error then follow the step. and database must be mounted;

NOTE:- ORA-19573: CANNOT OBTAIN EXCLUSIVE ENQUEUE FOR DATAFILE

SQL> alter database recover managed standby database cancel;

Database altered.

Then run again recovery command:

RMAN> recover database;

Starting recover at 15-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
channel ORA_DISK_1: piece handle=/u01/tspr/29pij5mb_1_1 tag=TAG20140915T181954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06

starting media recovery

archived log for thread 1 with sequence 373 is already on disk as file /u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc
archived log file name=/u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc thread=1 sequence=373
unable to find archived log
archived log thread=1 sequence=374
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:50:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 374 and starting SCN of 2078522

Note:- After some time, the recovery fails with the message. This happens because we have come to the last of the archived logs.

12 .At this point exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2082113

On the primary database:

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY

Please share your suggestion and comment on this.
all the best.... :)

Thursday, August 14, 2014

Table_exists_action Parameter of Data Pump

Table_exists_action Parameter of Data Pump

Sometimes we need to import table into an existing table.If we import the table in that schemas it throws error that table already exist. If we have to reserve the old data of table and append the new data, we can use the table_exists_action parameter of data pump. Some valid keywords are (SKIP | APPEND | TRUNCATE | REPLACE).

The possible values of the following effects are:-

1.) SKIP: - says to ignore the data in the import file and leave the existing table untouched. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.By default the value is SKIP.

2.) APPEND: - loads rows from the source and leaves existing rows unchanged.

3.) TRUNCATE: - truncate the exiting rows in the table and insert the rows from the dump. The truncate option cannot be used over a db link or with a cluster table.

4.) REPLACE: - says to delete the whole table and replace both the table definition and rows from the import dmp file. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

example :-

SQL> create user rudra identified by rudra default tablespace mmoney quota unlimited on mmoney;

User created.

SQL> grant create session,resource to rudra;

Grant succeeded.

SQL> create or replace directory abc as '/u01/neeraj';

Directory created.

SQL> grant read,write on directory abc to rudra;

Grant succeeded.

SQL> conn

Enter user-name: rudra

Enter password:

Connected.

SQL> create table r1(id number,name varchar2(20));

Table created.

SQL> insert into r1 values(1,'rudra');

1 row created.

SQL> insert into r1 values(2,'manoj');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * From r1;

ID NAME

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

1 rudra

2 manoj

we take a export:-

[oracle@localhost ~]$ expdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=r1.log schemas=rudra

Export: Release 11.2.0.1.0 - Production on Wed Aug 6 20:59:34 2014

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

Starting "RUDRA"."SYS_EXPORT_SCHEMA_01": rudra/******** directory=abc dumpfile=r1.dmp logfile=r1.log schemas=rudra

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported "RUDRA"."R1" 5.453 KB 4 rows

Master table "RUDRA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RUDRA.SYS_EXPORT_SCHEMA_01 is:

/u01/neeraj/r1.dmp

Job "RUDRA"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:00:04

[oracle@localhost ~]$

Demo 1:- action_exists_append=skip (by defaults)

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:02:19 2014

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

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01": rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "RUDRA"."R1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:02:22

Demo 2:- table_exists_action=append

We have an r1 table and it contains four records.

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:06:28 2014

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

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01": rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=append

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39152: Table "RUDRA"."R1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1" 5.453 KB 4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:06:30

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:06:36 2014

Enter user-name: rudra

Enter password:

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> select * from r1;

ID NAME

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

1 rudra

2 manoj

1 rudra

2 manoj

8 rows selected.

Demo 3:- table_exists_action=truncate

Already we have 8 rows in the r1 table;

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:08:27 2014

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

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01": rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=truncate

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39153: Table "RUDRA"."R1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1" 5.453 KB 4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:08:30

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:08:36 2014

Enter user-name: rudra

Enter password:

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> select * From r1;

ID NAME

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

1 rudra

2 manoj

Demo 4:- table_exists_action= replace

Now i have inserted some values in the r1 table.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:10:24 2014

Enter user-name: rudra

Enter password:

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> select * From r1;

ID NAME

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

1 rudra

2 manoj

SQL> insert into r1 values(3,'raj');

1 row created.

SQL> insert into r1 values(4,'neha db');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from r1;

ID NAME

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

1 rudra

2 manoj

3 raj

4 neha db

6 rows selected.

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:14:31 2014

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

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01": rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=replace

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1" 5.453 KB 4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:14:33

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:14:37 2014

Enter user-name: rudra

Enter password:

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> select * from r1;

ID NAME

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

1 rudra

2 manoj

Please share your suggestion and comment on this.

all the best.... :)

Friday, August 8, 2014

Recover dropped table using RMAN tablespace point in time recovery:

Recover dropped table using RMAN Tablespace point in time recovery:

1. Copy the backup on the other server where you want to do cloning:-

[oracle@localhost backup]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 16:35:40 2014

Enter user-name: rudra

Enter password:

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> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

DEPT1 TABLE

EMP TABLE

EMP1 TABLE

LOC TABLE

SQL> drop table dept purge;

Table dropped.

[oracle@localhost ~]$ cd /u01/backup/

[oracle@localhost backup]$ ls

07pf9bgl_1_1 08pf9bgt_1_1 09pf9biv_1_1 c-820201958-20140806-03

[oracle@localhost backup]$ scp * oracle@192.168.204.131:/u01/asm

Address 192.168.204.131 maps to localhost.localdomain, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.204.131's password:

07pf9bgl_1_1 100% 59MB 19.6MB/s 00:03

08pf9bgt_1_1 100% 1023MB 4.2MB/s 04:02

09pf9biv_1_1 100% 22KB 21.5KB/s 00:00

c-820201958-20140806-03 100% 9600KB 9.4MB/s 00:00

2. Create the parameter file and passwrd file on the server where you want to do cloning:-

[oracle@localhost ~]$ vi /u01/asm/newtspr.ora

--required entry in the pfile:

db_name=newtspr

db_file_name_convert='/u01/app/oracle/oradata/tspr/','/u01/tspr/'

log_file_name_convert='/u01/app/oracle/oradata/tspr/','/u01/tspr/'

control_files='/u01/tspr/control01.ctl'

SQL> startup nomount pfile='/u01/asm/newtspr.ora'

ORACLE instance started.

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 6 18:33:54 2014

connected to auxiliary database: NEWTSPR (not mounted)

RMAN> duplicate database to newtspr backup location '/u01/asm' nofilenamecheck;

Starting Duplicate Db at 06-AUG-14

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''TSPR'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''NEWTSPR'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/asm/c-820201958-20140806-03';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''TSPR'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''NEWTSPR'' comment= ''Modifi ed by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

Starting restore at 06-AUG-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/tspr/control01.ctl

Finished restore at 06-AUG-14

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:

{

set until scn 815147;

set newname for datafile 1 to

"/u01/tspr/system01.dbf";

set newname for datafile 2 to

"/u01/tspr/sysaux01.dbf";

set newname for datafile 3 to

"/u01/tspr/undotbs01.dbf";

set newname for datafile 4 to

"/u01/tspr/users01.dbf";

set newname for datafile 5 to

"/u01/tspr/example01.dbf";

set newname for datafile 6 to

"/u01/tspr/tbs.dbf";

restore

clone database;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 06-AUG-14

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/tspr/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/tspr/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/tspr/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/tspr/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/tspr/example01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/tspr/tbs.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/asm/08pf9bgt_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/asm/08pf9bgt_1_1 tag=TAG20140806T15091 7

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:36

Finished restore at 06-AUG-14

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=854908554 file name=/u01/tspr/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=854908554 file name=/u01/tspr/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=854908554 file name=/u01/tspr/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=854908554 file name=/u01/tspr/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=11 STAMP=854908554 file name=/u01/tspr/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=12 STAMP=854908554 file name=/u01/tspr/tbs.dbf

contents of Memory Script:

{

set until scn 815147;

recover

clone database

delete archivelog;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-AUG-14

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=13

channel ORA_AUX_DISK_1: reading from backup piece /u01/asm/09pf9biv_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/asm/09pf9biv_1_1 tag=TAG20140806T15102 3

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_13_854885226.dbf thread=1 sequence=13

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_13_854885226.dbf RECID=1 STAMP=854908555

media recovery complete, elapsed time: 00:00:00

Finished recover at 06-AUG-14

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''NEWTSPR'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

sql statement: alter system set db_name = ''NEWTSPR'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEWTSPR" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/tspr/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/tspr/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/tspr/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/tspr/system01.dbf'

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/tspr/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u01/tspr/sysaux01.dbf",

"/u01/tspr/undotbs01.dbf",

"/u01/tspr/users01.dbf",

"/u01/tspr/example01.dbf",

"/u01/tspr/tbs.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/tspr/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/u01/tspr/sysaux01.dbf RECID=1 STAMP=854908565

cataloged datafile copy

datafile copy file name=/u01/tspr/undotbs01.dbf RECID=2 STAMP=854908565

cataloged datafile copy

datafile copy file name=/u01/tspr/users01.dbf RECID=3 STAMP=854908565

cataloged datafile copy

datafile copy file name=/u01/tspr/example01.dbf RECID=4 STAMP=854908565

cataloged datafile copy

datafile copy file name=/u01/tspr/tbs.dbf RECID=5 STAMP=854908565

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=854908565 file name=/u01/tspr/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=854908565 file name=/u01/tspr/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=854908565 file name=/u01/tspr/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=854908565 file name=/u01/tspr/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=854908565 file name=/u01/tspr/tbs.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 06-AUG-14

RMAN>

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 18:47:25 2014

Enter user-name: sys as sysdba

Enter password:

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> create or replace directory abc as '/u01/asm';

Directory created.

SQL> grant read, write on directory abc to public;

Grant succeeded.

SQL> conn

Enter user-name: rudra

Enter password:

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

EMP1 TABLE

LOC TABLE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ expdp rudra/rudra directory=abc dumpfile=dpt.dmp logfile=dpt.log tables=dept

Export: Release 11.2.0.1.0 - Production on Wed Aug 6 18:50:43 2014

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

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/u01/tspr/temp01.dbf'

ORA-06512: at "SYS.DBMS_LOB", line 664

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3901

ORA-06512: at line 1

Note:- Need to create the tempfile because rman does not take the tempfile backup.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 18:52:45 2014

Enter user-name: sys as sysdba

Enter password:

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> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

TBS

7 rows selected.

SQL> alter tablespace temp add tempfile '/u01/tspr/temp02.dbf' size 20m;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/tspr/temp01.dbf';

Tablespace altered.

[oracle@localhost ~]$ expdp rudra/rudra directory=abc dumpfile=dpt.dmp logfile=dpt.log tables=dept

Export: Release 11.2.0.1.0 - Production on Wed Aug 6 18:54:14 2014

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

Starting "RUDRA"."SYS_EXPORT_TABLE_01": rudra/******** directory=abc dumpfile=dpt.dmp logfile=dpt.log tables=dept

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "RUDRA"."DEPT" 5.437 KB 4 rows

Master table "RUDRA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RUDRA.SYS_EXPORT_TABLE_01 is:

/u01/asm/dpt.dmp

Job "RUDRA"."SYS_EXPORT_TABLE_01" successfully completed at 18:54:28

Copy the dumpfile to the database server :-

[oracle@localhost ~]$ cd /u01/asm/

[oracle@localhost asm]$ ls

dpt.dmp dpt.log

[oracle@localhost asm]$ scp * oracle@192.168.204.129:/u01/tspr

The authenticity of host '192.168.204.129 (192.168.204.129)' can't be established.

RSA key fingerprint is cb:57:be:e9:4f:f9:d1:1b:b2:95:8f:ac:f2:d1:05:12.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.204.129' (RSA) to the list of known hosts.

Address 192.168.204.129 maps to localhost.localdomain, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.204.129's password:

dpt.dmp 100% 92KB 92.0KB/s 00:00

dpt.log 100% 1058 1.0KB/s 00:00

On the main database server :-

[oracle@localhost backup]$ cd /u01/tspr/

[oracle@localhost tspr]$ ls

dpt.dmp dpt.log

[oracle@localhost tspr]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 20:43:31 2014

Enter user-name: sys as sysdba

Enter password:

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> create or replace directory abc as '/u01/tspr/';

Directory created.

SQL> grant read,write on directory abc to public;

Grant succeeded.

SQL> conn

Enter user-name: rudra

Enter password:

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT1 TABLE

EMP TABLE

EMP1 TABLE

LOC TABLE

NEERAJ TABLE

SQL>exit

[oracle@localhost tspr]$ impdp rudra/rudra directory=abc dumpfile=dpt.dmp logfile=dpt_imp.log tables=dept

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 20:49:38 2014

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

Master table "RUDRA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_TABLE_01": rudra/******** directory=abc dumpfile=dpt.dmp logfile=dpt_imp.log tables=dept

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."DEPT" 5.437 KB 4 rows

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Job "RUDRA"."SYS_IMPORT_TABLE_01" successfully completed at 20:49:43

[oracle@localhost tspr]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 20:50:27 2014

Enter user-name: rudra

Enter password:

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> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

DEPT1 TABLE

EMP TABLE

EMP1 TABLE

LOC TABLE

NEERAJ TABLE

6 rows selected.

SQL> select * from dept;

ID DEPTID

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

1 10

2 20

SQL>

Note:- Since we just restored this table from old backup so we would have to re-populated this table anew to reflect all the changes.

Please share your suggestion and comment on this.

all the best.... :)