Re: Tablespace recovery possible?

From: Krisztian Rassay <krisztian.rassay_at_sysdata.siemens.hu>
Date: Thu, 22 Feb 2001 15:08:00 +0100
Message-ID: <3A951D40.2B00407F_at_sysdata.siemens.hu>

Hi Ned, I'm trying in that way to recover tablespace "table1", but it doesn't work.
Could you take a look at my logfile?

thanks,
Chris

<pre>

$ export ORACLE_SID=aux
$ export NLS_DATE_FORMAT=...
$ rman target ember/jelszo_at_oam rcvcat rman/rman_at_sxrec auxiliary /

RMAN> run {
2>      allocate auxiliary channel  ch type 'sbt_tape'
3>              parms
'ENV=(NSR_SERVER=nsrserver,NSR_DATA_VOLUME_POOL=proba)';
4>      recover tablespace table1 until time 'Feb 22 2001 14:00:00';

5> }

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: g3
RMAN-08500: channel g3: sid=7 devtype=SBT_TAPE
RMAN-08526: channel g3: BMO v3.0

RMAN-03022: compiling command: recover

RMAN-03027: printing stored script: Memory Script {
# set the until clause

set until time 'FEB 22 2001 14:00:00';
# restore the controlfile

restore clone controlfile to clone_cf;
# replicate the controlfile

replicate clone controlfile from clone_cf;
# mount the controlfile

sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# resync catalog after controlfile restore
resync catalog;
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel g3: starting datafile backupset restore
RMAN-08502: set_count=108 set_stamp=422369938 creation_time=Feb 22 2001

12:58:58

RMAN-08021: channel g3: restoring controlfile
RMAN-08505: output filename=/home1/oam/tspitr/aux_cf.f
RMAN-08023: channel g3: restored backup piece 1
RMAN-08511: piece handle=database_TAPE_level_0.OAM3ccipmki tag=null

params=NULL
RMAN-08024: channel g3: restore complete

RMAN-03022: compiling command: replicate
RMAN-03023: executing command: replicate
RMAN-08058: replicating controlfile
RMAN-08506: input filename=/home1/oam/tspitr/aux_cf.f

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database mount clone database RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql

RMAN-03022: compiling command: resync
RMAN-03023: executing command: resync

RMAN-08002: starting full resync of recovery catalog RMAN-08004: full resync complete

RMAN-03027: printing stored script: Memory Script {
# generated tablespace point-in-time recovery script
# set the until clause

set until time 'FEB 22 2001 14:00:00';
plsql <<<-- tspitr_2
declare
  sqlstatement varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539); begin
  sqlstatement := 'alter tablespace '||' TABLE1'||' offline for recover';   krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
'/home1/oam/tspitr/db/system.dbf';
# set a destination filename for restore
set newname for datafile 2 to
'/home1/oam/db/table1.dbf';
# restore the tablespaces in the recovery set plus the auxilliary
tablespaces
restore clone datafile 1, 2;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace TABLE1, SYSTEM; alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to
lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: set

RMAN-03022: compiling command: PLSQL
RMAN-03023: executing command: PLSQL
RMAN-06162: sql statement: alter tablespace  TABLE1 offline for recover

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel g3: starting datafile backupset restore
RMAN-08502: set_count=108 set_stamp=422369938 creation_time=Feb 22 2001

12:58:58

RMAN-08089: channel g3: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to /home1/oam/tspitr/db/system.dbf
RMAN-08023: channel g3: restored backup piece 1
RMAN-08511: piece handle=database_TAPE_level_0.OAM3ccipmki tag=null

params=NULL

RMAN-08024: channel g3: restore complete
RMAN-08016: channel g3: starting datafile backupset restore
RMAN-08502: set_count=109 set_stamp=422369938 creation_time=Feb 22 2001

12:58:58

RMAN-08089: channel g3: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00002 to /home1/oam/db/table1.dbf
RMAN-08023: channel g3: restored backup piece 1
RMAN-08511: piece handle=database_TAPE_level_0.OAM3dcipmki tag=null

params=NULL
RMAN-08024: channel g3: restore complete

RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=119 stamp=422375922

filename=/home1/oam/db/table1.dbf

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database datafile  1 online
RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database datafile 2 online RMAN-03023: executing command: sql

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4) RMAN-06050: archivelog thread 1 sequence 482 is already on disk as file /home1/oam/arc/OAMT0001S0000000482.ARC
RMAN-03023: executing command: recover(4) RMAN-08515: archivelog filename=/home1/oam/arc/OAMT0001S0000000482.ARC thread=1 sequence=482
RMAN-08055: media recovery complete

RMAN-03022: compiling command: alter db
RMAN-06400: database opened

RMAN-03027: printing stored script: Memory Script {
# export the tablespaces in the recovery set
host 'exp userid =\"/_at_ as sysdba\" point_in_time_recover=y tablespaces= TABLE1 file=
tspitr_a.dmp';
# shutdown clone before import

shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"ember/jelszo_at_oam as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TABLE1 online";
sql "alter tablespace TABLE1 offline";
# resync catalog after tspitr finished

resync catalog;
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: host

Export: Release 8.1.6.0.0 - Production on Thu Feb 22 14:38:48 2001

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...

For tablespace TABLE1 ...

. exporting cluster definitions
. exporting table definitions
. . exporting table                          KONYV
. . exporting table       ALARMBUFFERTABLEBUDAPEST
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery

Export terminated successfully without warnings. RMAN-06134: host command complete

RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down

RMAN-03022: compiling command: host

Import: Release 8.1.6.0.0 - Production on Thu Feb 22 14:44:13 2001

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production

Export file created by EXPORT:V08.01.06 via conventional path About to import Tablespace Point-in-time Recovery objects... import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
IMP-00017: following statement failed with ORACLE error 1534: "BEGIN sys.dbms_pitr.commitPitr; END;"

IMP-00003: ORACLE error 1534 encountered
ORA-01534: rollback segment 'ROLL3' doesn't exist
ORA-06512: at "SYS.DBMS_PITR", line 1221
ORA-06512: at line 1

. importing EMBER's objects into EMBER
IMP-00015: following statement failed because the object already exists:

 "CREATE TABLE "KONYV" ("CIM" VARCHAR2(30), "IRO" VARCHAR2(30))  PCTFREE 10
P"
 "CTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(SEG_FILE 2 SEG_BLOCK 2

OB"
"JNO_REUSE 5941 INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCR"
"EASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TABL"
"E1""
IMP-00015: following statement failed because the object already exists: "CREATE TABLE "ALARMBUFFERTABLEBUDAPEST" ("INSEQNO" NUMBER(10, 0), "OUTSEQNO"

 "" NUMBER(10, 0), "ALARM" BLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
2"
 "55 LOGGING STORAGE(SEG_FILE 2 SEG_BLOCK 7 OBJNO_REUSE 5944 INITIAL 10240

NE"
"XT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GR"
"OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TABLE1" LOB ("ALARM") STORE AS (TA"

 "BLESPACE "TABLE1" ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE
S"
 "TORAGE(SEG_FILE 2 SEG_BLOCK 12 OBJNO_REUSE 5945 INITIAL 10240 NEXT 10240

MI"
"NEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFF"
"ER_POOL DEFAULT) INDEX(TABLESPACE "TABLE1" INITRANS 2 MAXTRANS 255 STORAGE("
"SEG_FILE 2 SEG_BLOCK 17 OBJNO_REUSE 5946 INITIAL 10240 NEXT 10240 MINEXTENT"
"S 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFF"
"ER_POOL DEFAULT)))"
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 29301: "BEGIN sys.dbms_pitr.endImport; END;"

IMP-00003: ORACLE error 29301 encountered
ORA-29301: wrong DBMS_PITR package function/procedure order
ORA-06512: at "SYS.DBMS_PITR", line 1237
ORA-06512: at line 1

Import terminated successfully with warnings. RMAN-06134: host command complete

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter tablespace  TABLE1 online
RMAN-03023: executing command: sql
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: recover
RMAN-03015: error occurred in stored script Memory Script
RMAN-03006: non-retryable error occurred during execution of command: sql
RMAN-07004: unhandled exception during command execution on channel default

RMAN-20000: abnormal termination of job step RMAN-11003: failure during parse/execution of SQL statement: alter tablespace TABLE1 online
RMAN-11001: Oracle Error: ORA-01190: controlfile or data file 2 is from before the last RESETLOGS
ORA-01110: data file 2: '/home1/oam/db/table1.dbf'

RMAN> **end-of-file**

</pre> Received on Thu Feb 22 2001 - 15:08:00 CET

This message: [ Message body ]
Next message: Brian West: "Re: INSTR challenge"
Previous message: ExCelLa: "Re: Developer Certification"
Maybe in reply to: Krisztian Rassay: "Tablespace recovery possible?"
Next in thread: Brian West: "Re: INSTR challenge"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message