DBA Sensation

August 15, 2012

Use RMAN to restore/recover the database to remote host

Filed under: [backup and recovery] — Tags: , , , — zhefeng @ 10:10 am

0. background
The purpose for this testing is to try restore/recover the database to remote host with rman backup
i am using 1 vmware linux box, single ORCL oracle instance on ASM storage (to make things more complicated:))
On the source db, i have a user “jehan”, a table “test” , with 3 rows as below:
SQL> select * from jehan.test;

COL1
———-
good
best
worst

1. backup source db
[oracle@myrh5 trace]$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Tue Aug 14 17:56:25 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1270514474)

RMAN> backup database;

Starting backup at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=155 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/orcl/ds01.dbf
input datafile file number=00002 name=+DATA/orcl/sysaux01.dbf
input datafile file number=00001 name=+DATA/orcl/system01.dbf
input datafile file number=00006 name=+DATA/orcl/cms01.dbf
input datafile file number=00003 name=+DATA/orcl/undotbs01.dbf
input datafile file number=00005 name=+DATA/orcl/example01.dbf
input datafile file number=00004 name=+DATA/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803 tag=TAG20120814T175642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911 tag=TAG20120814T175642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-12

2. delete source db “ORCL” in dbca

3. startup target database with no mount by using default init.ora file (note, you don’t need to have target db created beforehand)
[oracle@myrh5 trace]$ export ORACLE_SID=ORCL
[oracle@myrh5 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Aug 15 09:51:08 2012

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

connected to target database (not started)

RMAN> set dbid 1270514474;
RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/home/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2211448 bytes
Variable Size 92275080 bytes
Database Buffers 58720256 bytes
Redo Buffers 5455872 bytes

4. restore the spfile from backup to pfile
RMAN> restore spfile to pfile ‘/home/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora’ from ‘+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911′;
Starting restore at 15-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-AUG-12

5. make the path for auditing according to pfile parameters (*.audit_file_dest=’/home/u01/app/oracle/admin/orcl/adump’), you have to do this otherwise the rman can’t start database with nomount
mkdir -p /home/u01/app/oracle/admin/orcl/adump

Note: also if you want to put the control file in different path, modify the pfile for the paths now

6. now start database with no mount with pfile (which will provide the correct control file location)
RMAN> startup nomount pfile=’?/dbs/initORCL.ora’;

7. now restore the controlfile
RMAN>restore controlfile from ‘+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911’;
Starting restore at 15-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/orcl/control01.ctl
output file name=+DATA/orcl/control02.ctl
Finished restore at 15-AUG-12

8. now we have the control files, we can start start database mount
RMAN> set dbid 1270514474;

executing command: SET DBID

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

9. Let the restore begin!
RMAN> restore database;

Starting restore at 15-AUG-12
Starting implicit crosscheck backup at 15-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 15-AUG-12

Starting implicit crosscheck copy at 15-AUG-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-AUG-12

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_315.385.791317055
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_316.384.791317069
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_317.383.791317085
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_318.382.791317099
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_319.381.791317113
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_320.380.791317127
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_321.379.791317139
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_322.378.791317153
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_323.377.791317171
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_324.376.791334027
File Name: +data/ORCL/BACKUPSET/2012_08_14/ncsnf0_TAG20120814T175642_0.386.791315911

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/cms01.dbf
channel ORA_DISK_1: restoring datafile 00007 to +DATA/orcl/ds01.dbf
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803 tag=TAG20120814T175642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
Finished restore at 15-AUG-12

Note: if you want to restore the datafile to different location, have to do the path mapping like this (not for temp tablespace datafile), after restore done, also run “SWITCH DATAFILE ALL;” for updating rman catalog in control file:
set newname for datafile 1 to “/u01/oradata/system01.dbf”;
set newname for datafile 2 to “/u01/oradata/sysaux01.dbf”;

10. recover the database
RMAN> recover database;

Starting recover at 15-AUG-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 315 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_315.385.791317055
archived log for thread 1 with sequence 316 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_316.384.791317069
archived log for thread 1 with sequence 317 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_317.383.791317085
archived log for thread 1 with sequence 318 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_318.382.791317099
archived log for thread 1 with sequence 319 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_319.381.791317113
archived log for thread 1 with sequence 320 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_320.380.791317127
archived log for thread 1 with sequence 321 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_321.379.791317139
archived log for thread 1 with sequence 322 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_322.378.791317153
archived log for thread 1 with sequence 323 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_323.377.791317171
archived log for thread 1 with sequence 324 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_324.376.791334027
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_315.385.791317055 thread=1 sequence=315
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_316.384.791317069 thread=1 sequence=316
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_317.383.791317085 thread=1 sequence=317
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_318.382.791317099 thread=1 sequence=318
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_319.381.791317113 thread=1 sequence=319
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_320.380.791317127 thread=1 sequence=320
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_321.379.791317139 thread=1 sequence=321
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_322.378.791317153 thread=1 sequence=322
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_323.377.791317171 thread=1 sequence=323
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_324.376.791334027 thread=1 sequence=324
unable to find archived log
archived log thread=1 sequence=325
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/15/2012 10:40:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 325 and starting SCN of 7484710

Note: the last error is fine, before we mount the database, we can use:
alter database mount;
set until scn or set until time
to specify the scn to avoid this error.

11. open database
RMAN> alter database open resetlogs;

database opened

Note: from 11gR2, after open database resetlogs, system will automatically create online redo log file and temp datafile.

12. Verify the data
[oracle@myrh5 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 15 10:47:53 2012

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

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

SQL> select * from jehan.test;

COL1
———-
good
best
worst

March 2, 2011

Recreating spfile on ASM storage from pfile

Filed under: [backup and recovery] — Tags: , , , — zhefeng @ 2:46 pm

Sometimes when you strewed up with parameters, you need to use the pfile as stepstone to undo the changes in spfile. How does it happen if your spfile sits on ASM storage? Here is an workaround.

1. try to screw up the db parameters
SQL> show parameter memory

NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 1520M
memory_target big integer 1520M
shared_memory_address integer 0
SQL> alter system set memory_max_target=0 scope=spfile;
System altered.

2. now bounce the instance, db will complain about the new settings
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

3. in my case the spfile sits on ASM
ASMCMD> ls -l spfile*
Type Redund Striped Time Sys Name
N spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.267.744731331

4. what we need to do is creating a pfile from spfile then modify parameter back to valid value, then start db from pfile
1). With db not up, we can create pfile from spfile:
SQL> create pfile from spfile=’+DATA/orcl/spfileorcl.ora’;
2). modify the value in pfile ‘initorcl.ora’
$ vi initorcl.ora
*.memory_max_target=1583349760
3). startup db with pfile
SQL>startup mount –now it will use the pfile

5. create the new spfile to ASM storage from “good” pfile
SQL> create spfile=’+DATA/ORCL/spfileorcl.ora’ from pfile;
File created.

6. watch the file name in ASM storage has been changed, which means we just had a new spfile:
ASMCMD> ls -l spfile*
Type Redund Striped Time Sys Name
N spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.267.744733351

7. now change the pfile back to be the “bootstrap” of correct spfile
$ cat initorcl.ora
spfile=’+DATA/ORCL/spfileorcl.ora’

8. restart the database, it will pickup the correct spfile again
$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 973080728 bytes
Database Buffers 603979776 bytes
Redo Buffers 7434240 bytes
Database mounted.
Database opened.

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/orcl/spfileorcl.ora

SQL> show parameter memory

NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 1520M
memory_target big integer 1520M
shared_memory_address integer 0

June 5, 2009

export name case-sensitive tables in oracle

Filed under: 1, [backup and recovery] — zhefeng @ 3:22 pm

exp xtreme file=xtreme_tables.dmp tables=(‘\”Product\”‘,’\”Supplier\”‘,CUSTOMER,ORDERS)

March 30, 2009

keep data consistency when using oracle exp/expdp

Filed under: [backup and recovery] — Tags: , , , , — zhefeng @ 1:25 pm

When we were using old oracle exp, we usually will set exp consistent=y (default is n) to ensure the data consistency (the image taken of the data in the tables being exported represents the committed state of the table data at the same single point-in-time for all of the tables being exported.)

However, started from 10g they decomission this parameter. Today i happened to have a requirement for this and i searched meta link and found this useful piece:

The versions 10gR1 and 10gR2 additionally put the message in the expdp header:

FLASHBACK automatically enabled to preserve database integrity

Does this guarantee export consistency to a single point of time?

Cause
The message:

FLASHBACK automatically enabled to preserve database integrity

only means that some of the tables will be assigned special SCNs (needed for Streams and Logical Standby). There is no consistency guaranteed between exported tables.

The next example demonstrates this:

1. Create the environment

connect / as sysdba

create or replace directory flash as ‘/tmp’;
grant read, write on directory flash to system;

drop user usr001 cascade;
purge dba_recyclebin;

create user usr001 identified by usr001 default tablespace users temporary tablespace temp;
grant connect, resource to usr001;

connect usr001/usr001
create table part001
(
col001 number,
col002 date,
col003 varchar2(1000)
)
partition by range (col001)
(
partition p001 values less than (500001),
partition p002 values less than (1000001)
);

2. Populate the partitioned table: partition P001 contains 500000 rows and partition P002 contains 10 rows

connect usr001/usr001
begin
for i in 1..500010 loop
insert into part001 values (i, sysdate, lpad (to_char(i), 1000, ‘0’));
end loop;
commit;
end;

3. Start expd

#> expdp system/passwd directory=flsh dumpfile=user001_1.dmp logfile =user001_1.log schemas=usr001

4. During point 3. is running, run in a separate session:

connect usr001/usr001
delete from part001 where col001 in (500001, 500002, 500003, 500004, 500005);
commit;

This will delete 5 rows in partition P002.

5. Expdp completes with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 13:59:59

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_1.dmp logfile=exp_usr001_1.log schemas=usr001
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001”.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001”.”PART001″:”P002″ 10.50 KB 5 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_1.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:02:47

=> From partition P002 only 5 rows were exported, so the written export dump is not consistent.
Solution
To generate consistent Data Pump’s database or schema export similar to exports generated with exp parameter CONSISTENT=Y, use Data Pump parameters FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

Conform with the example above, running expdp with:

#> expdp system/passwd directory=flsh dumpfile=user001_2.dmp logfile =user001_2.log schemas=usr001 flashback_time=\”TO_TIMESTAMP \(TO_CHAR \(SYSDATE, \’YYYY-MM-DD HH24:MI:SS\’\), \’YYYY-MM-DD HH24:MI:SS\’\)\”

This will end with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 14:15:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_2.dmp logfile=exp_usr001_2.log schemas=usr001 flashback_time=”to_timestamp (to_char (sysdate, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001”.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001”.”PART001″:”P002″ 15.48 KB 10 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_2.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:16:55

=> Partition P002 contains all 10 rows, though 5 rows were deleted during expdp time. The parameter FLASHBACK_TIME guarantees the consistency.

Link:  “Doc ID: 377218.1”

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4374876471460387797::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,377218.1,1,0,1,helvetica

November 6, 2008

how to export/import data with LOB type from one user/schema to another user?

Filed under: [backup and recovery] — zhefeng @ 2:05 pm

When you are using exp/imp moving data from one user/schema to another, if the schema contains some table with lob data type, then you will get error like this:

IMP-00017: following statement failed with ORACLE error 1950:
“CREATE TABLE “TEST” (“ID” NUMBER, “NAME” CLOB) ……..
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace ‘USERS’

So how to export/import data with LOB type from one user/schema to another user?

Here are the solutions:

1. for oracle 10g and 11g, directly using datapump can avoid this issue. Datapump support lob pretty well:

export:

expdp jehan directory=DATA_PUMP_DIR schemas=(LOUVRE_121307,DENALI10G) dumpfile=cart_dp.dmp logfile=cart_expdp.log

import:

impdp system DIRECTORY=DATA_PUMP_DIR DUMPFILE=CART_DP.DMP LOGFILE=cart_impdp.log REMAP_SCHEMA=LOUVRE_121307:cartesis_LOUVRE_121307 REMAP_SCHEMA=DENALI10G:cartesis_DENALI10G REMAP_TABLESPACE=users:cartesis REMAP_TABLESPACE=TBS_DATA_DENALI10G:cartesis REMAP_TABLESPACE=CARTESIS_DATA:cartesis REMAP_TABLESPACE=TBS_DATA_LOUVRE_121307:cartesis

2. for 9i, you have to extract the lob table DDL and create it manually, then do the importing

step1: Import with INDEXFILE parameter to generate the script that can be used to modify the LOB’s tablespace clause.
$imp system/manager fromuser=user1 tables=\(test\) indexfile=create_lob_table

step2: Edit the generated script file create_lob_table.sql:

REM CREATE TABLE “USER1″.”TEST” (“ID” VARCHAR2(8) NOT NULL
REM ENABLE, “FIC_CIRCUL” BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM 255 LOGGING STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
REM 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE “USERS” LOB (“FIC_CIRCUL”) STORE AS (TABLESPACE “USERS”
REM ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE LOGGING
REM STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM … 0 rows

> Remove the REM comments
> Change the owner USER1 by the new owner USER2
> Change the tablespace USERS by the TOUSER’s tablespace USER2 in the whole statement.

step3. Run the script create_lob_table.sql to create the USER2.TEST table.

step4. Import the data only in the created table, ignoring the CREATE TABLE statement failure.

$imp user2/*** FILE=exp.dmp LOG=imp_tab.log FROMUSER=user1 TOUSER=user2 TABLES=test IGNORE=y

Create a free website or blog at WordPress.com.