Link to home
Start Free TrialLog in
Avatar of xoxomos
xoxomos

asked on

Restore old database after resetlogs

Here is one backup record I got from a LIST BACKUP command:
Ok say I've issued the RESETLOG on Nov 3 .  I immediatedly backed the database up and almost as immediately rm*
and lost that backup.  Now I want to get back to the HAYPSDMD_1 & 2 which was a backup including controlfile.
What's the steps?



RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1012M      DISK        00:05:43     10-OCT-03
        BP Key: 1   Status: AVAILABLE   Tag: TAG20031010T163759
        Piece Name: /u02/app/oracle/backup/haypsdmd/rman/HAYPSDMD_1_1
  SPFILE Included: Modification time: 19-SEP-03
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/system01.d
bf
  2       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/undotbs01.
dbf
  3       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/cwmlite01.
dbf
  4       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/drsys01.db
f
  5       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/example01.
dbf
  6       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/indx01.dbf
  7       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/odm01.dbf
  8       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/tools01.db
f
  9       Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/users01.db
f
  10      Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/xdb01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    772M       DISK        00:13:02     10-OCT-03
        BP Key: 2   Status: AVAILABLE   Tag: TAG20031010T163759
        Piece Name: /u02/app/oracle/backup/haypsdmd/rman/HAYPSDMD_2_1
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING.db
f
  12      Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/DATA_WAREH
OUSE.dbf
  13      Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/DWH_INDEX.
dbf
  14      Full 24423550352 10-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING_IN
DEX.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1016M      DISK        00:06:46     19-OCT-03
        BP Key: 3   Status: AVAILABLE   Tag: TAG20031019T114457
        Piece Name: /u02/app/oracle/backup/haypsdmd/b_03f4995q_1_1
  SPFILE Included: Modification time: 19-SEP-03
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/system01
.dbf
  2       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/undotbs0
1.dbf
  3       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/cwmlite0
1.dbf
  4       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/drsys01.
dbf
  5       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/example0
1.dbf
  6       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/indx01.d
bf
  7       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/odm01.db
f
  8       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/tools01.
dbf
  9       Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/users01.
dbf
  10      Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/xdb01.db
f

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    783M       DISK        00:12:35     19-OCT-03
        BP Key: 4   Status: AVAILABLE   Tag: TAG20031019T114457
        Piece Name: /u02/app/oracle/backup/haypsdmd/b_04f499ii_1_1
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING.
dbf
  12      Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/DATA_WAR
EHOUSE.dbf
  13      Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/DWH_INDE
X.dbf
  14      Full 2199471501468 18-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING_
INDEX.dbf

RMAN>

Avatar of xoxomos
xoxomos

ASKER

I'm sure I resetlogs in NOV but:

SQL> select dbid, resetlogs_change#, resetlogs_time from v$database;

      DBID RESETLOGS_CHANGE# RESETLOGS
---------- ----------------- ---------
3986756888            162043 30-APR-03
Avatar of xoxomos
xoxomos

ASKER



List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HAYPSDMD 3986756888       YES 162043     30-APR-03
Avatar of seazodiac
seazodiac
Flag of United States of America image
try these steps:

$rman catalog rman/rman@<rman_db_sid> target sys/password@<target_db_id>

RMAN>Reset database;            --because you have issued resetlogs on target db.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                        set until time "TO_DATE('19-OCT-03 00:00:00',
                                                              'DD-MON-YY HH24:MI:SS')";
                        allocate channel d1 type disk;
                        restore database;
                        recover database;
                        alter database open resetlogs;
                        }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                   allocate channel d1 type disk;
                   backup database;
                  }


Good luck!
Avatar of xoxomos
xoxomos

ASKER

Ok.  I'll have to figure what's happening with the password file first. .

psdwd /u01/app/oracle/product/9.2.0.1.0/network/admin $ rman cata
log rman/rman@OEMREP target sys/Mx3dwsys@haypsdmd

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
psdwd /u01/app/oracle/product/9.2.0.1.0/network/admin $
????????????????????????????????????????????????????????????????????
I did this a couple days ago and since then the sys password does not work for that database.
RMAN> restore controlfile from '/ustage/neil/rman/HAYPSDMD_6_1';

Starting restore at 04-NOV-03

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/haypsdmd/control01.ctl
output filename=/u01/app/oracle/oradata/haypsdmd/control02.ctl
output filename=/u01/app/oracle/oradata/haypsdmd/control03.ctl
Finished restore at 04-NOV-03
 
ASKER CERTIFIED SOLUTION
Avatar of seazodiac
seazodiac
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kong
Kong
Hello again :-)

Didn't allocating two channels, then restoring the tablespaces work?

I'll test it out on my ORCL later...

Stay tuned...
Avatar of Kong
Kong
Oh, can you paste the output of the successful rman backup if you have it, it'd help a lot.

Avatar of xoxomos
xoxomos

ASKER

You mean like this
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $ orapwd file=orapwhaypsdmd password=a10n entries=24
even that doesn't seem to work for me!
Seems i've made matters worse.  Now it won't mount and that may/may not have something to do with it.  
I do have console outputs from successful backups but they don't match the backups i can locate.  I deleted a bunch of files trying to get space which lead to this very  prediciment .


RMAN> run {
2> allocate channel Channel1 type disk
3> format '/u02/app/oracle/backup/haypsdmd/b_%u_%p_%c';
4> backup
5> (database include current controlfile);
6> }

allocated channel: Channel1
channel Channel1: sid=10 devtype=DISK

Starting backup at 19-OCT-03
channel Channel1: starting full datafile backupset
channel Channel1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u02/app/oracle/oradata/haypsdmd/system01.dbf
input datafile fno=00002 name=/u02/app/oracle/oradata/haypsdmd/undotbs01.dbf
input datafile fno=00005 name=/u02/app/oracle/oradata/haypsdmd/example01.dbf
input datafile fno=00010 name=/u02/app/oracle/oradata/haypsdmd/xdb01.dbf
input datafile fno=00006 name=/u02/app/oracle/oradata/haypsdmd/indx01.dbf
input datafile fno=00009 name=/u02/app/oracle/oradata/haypsdmd/users01.dbf
input datafile fno=00003 name=/u02/app/oracle/oradata/haypsdmd/cwmlite01.dbf
input datafile fno=00004 name=/u02/app/oracle/oradata/haypsdmd/drsys01.dbf
input datafile fno=00007 name=/u02/app/oracle/oradata/haypsdmd/odm01.dbf
input datafile fno=00008 name=/u02/app/oracle/oradata/haypsdmd/tools01.dbf
channel Channel1: starting piece 1 at 19-OCT-03
channel Channel1: finished piece 1 at 19-OCT-03
piece handle=/u02/app/oracle/backup/haypsdmd/b_03f4995q_1_1 comment=NONE
channel Channel1: backup set complete, elapsed time: 00:06:47
channel Channel1: starting full datafile backupset
channel Channel1: specifying datafile(s) in backupset
input datafile fno=00011 name=/u02/app/oracle/oradata/haypsdmd/STAGING.dbf
input datafile fno=00012 name=/u02/app/oracle/oradata/haypsdmd/DATA_WAREHOUSE.db
f
input datafile fno=00014 name=/u02/app/oracle/oradata/haypsdmd/STAGING_INDEX.dbf
input datafile fno=00013 name=/u02/app/oracle/oradata/haypsdmd/DWH_INDEX.dbf
channel Channel1: starting piece 1 at 19-OCT-03
channel Channel1: finished piece 1 at 19-OCT-03
piece handle=/u02/app/oracle/backup/haypsdmd/b_04f499ii_1_1 comment=NONE
channel Channel1: backup set complete, elapsed time: 00:12:45
Finished backup at 19-OCT-03
released channel: Channel1

RMAN>

Avatar of xoxomos
xoxomos

ASKER

I was trying to follow the a96566 Recovery Manual ....through RESETLOGS operation.  Something I did there must have made me loose the ability to mount.
Avatar of Kong
Kong
Thanks for the output, it helped a lot!

If I'm not mistaken, the names of the backup files are b_03f4995q_1_1 and b_04f499ii_1_1.

Don't worry about the password, set the parameter remote_login_passwordfile = none

More info on how to restore later, busy now, sorry...

Avatar of xoxomos
xoxomos

ASKER

Ok i ran this command that made the 30-APR-03 YES and the 01-NOV-03 NO.
It was reversed before I ran that command.
RMAN> list incarnation
2> ;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
516     517     DWPRODDB 516648928        YES 1          12-SEP-03
180     181     OEMREP   3191592595       YES 157080     24-JUL-03
1       2       HAYPSDMD 3986756888       YES 162043     30-APR-03
1       287     HAYPSDMD 3986756888       NO  2199601668524 01-NOV-03

RMAN>
Avatar of seazodiac
seazodiac
Flag of United States of America image
Ok, xoxomos:

let's get some basic things out of the way.

SO i understand you have two databases : one is your backup database HAYPSDMD, the other is OEMREP.

Are these two databases on the same machine?

where did you run the command "orapwd ...."?
Avatar of seazodiac
seazodiac
Flag of United States of America image
--->Ok i ran this command that made the 30-APR-03 YES and the 01-NOV-03 NO.

what command are you running to do this? ;-)
Avatar of xoxomos
xoxomos

ASKER

No.  These two I no longer have.  
 b_03f4995q_1_1 and b_04f499ii_1_1.  Now its HAYPSDMD_6_1 and HAYPSDMD_7_1
Avatar of xoxomos
xoxomos

ASKER

Actually there are three.  OEMREP.  That's mainly for OMS stuff and now I've put RMAN catalog there.  The other two are dwtestdb and haypsdmd.  All on the same machine.
Avatar of xoxomos
xoxomos

ASKER

I was in $ORACLE_HOME/dbs when I ran that orapwd
Avatar of seazodiac
seazodiac
Flag of United States of America image
you have to make sure the ORACLE_SID is haypsdmd if you want to reset the sys password for haypsdmd.
Avatar of seazodiac
seazodiac
Flag of United States of America image
do an "ECHO $ORACLE_SID", and post your result now
Avatar of xoxomos
xoxomos

ASKER


OPW-00005: File with same name exists - please delete or rename
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $ mv orapwhaypsdmd orapwhaypsdmd_
OLD
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $ orapwd file=orapwhaypsdmd passw
ord=a10n entries=24
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $ echo $ORACLE_SID
haypsdmd
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $
Avatar of seazodiac
seazodiac
Flag of United States of America image
ok, then can you log in to the haypsdmd now? and mount it?
Avatar of xoxomos
xoxomos

ASKER

Strange, it does mount again without errors but the password does not work.  Using sys/as sysdba i just put in anything (os verified) but comming in from EM it needs the correct password.

SQL*Plus: Release 9.2.0.3.0 - Production on Thu Nov 6 15:52:55 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys /as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01109: database not open


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

Total System Global Area  466618336 bytes
Fixed Size                   731104 bytes
Variable Size             352321536 bytes
Database Buffers          100663296 bytes
Redo Buffers               12902400 bytes
Database mounted.

Avatar of xoxomos
xoxomos

ASKER

I LIED!!!.  The password is working
Avatar of seazodiac
seazodiac
Flag of United States of America image
Xoxomos:
your post is very confusing now.
from your first post, you have listed and claimed to want to go back to HAYPSDMD_1_1 and HAYPSDMD_2-1.
                                                                                                           ---------------------------------------------
Now

--->No.  These two I no longer have.  
 b_03f4995q_1_1 and b_04f499ii_1_1.  Now its HAYPSDMD_6_1 and HAYPSDMD_7_1
                                                                      -------------------------------------------------
 




1.       Full    1012M      DISK        00:05:43     10-OCT-03
        BP Key: 1   Status: AVAILABLE   Tag: TAG20031010T163759
        Piece Name: /u02/app/oracle/backup/haypsdmd/rman/HAYPSDMD_1_1
  SPFILE Included: Modification time: 19-SEP-03



2       Full    772M       DISK        00:13:02     10-OCT-03
        BP Key: 2   Status: AVAILABLE   Tag: TAG20031010T163759
        Piece Name: /u02/app/oracle/backup/haypsdmd/rman/HAYPSDMD_2_1
  List of Datafiles in backup set 2
Avatar of seazodiac
seazodiac
Flag of United States of America image
Now , back to business:

try these steps:


$rman catalog rman/rman@<rman_db_sid> target sys/password@<target_db_id>

RMAN>Reset database;            --because you have issued resetlogs on target db.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                        set until time "TO_DATE('10-OCT-03 00:00:00',
                                                              'DD-MON-YY HH24:MI:SS')";
                        allocate channel d1 type disk;
                        restore database;
                        recover database;
                        alter database open resetlogs;
                        }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                   allocate channel d1 type disk;
                   backup database;
                  }


Avatar of xoxomos
xoxomos

ASKER

haypsdmd
psdwd /u01/app/oracle/product/9.2.0.1.0/dbs $ rman catalog rman/rman@OEMREP tar
get sys/a10n@haypsdmd

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: HAYPSDMD (DBID=3986756888)
connected to recovery catalog database

RMAN> reset database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of reset command on default channel at 11/06/2003 16:04:35
RMAN-20009: database incarnation already registered

RMAN>
Avatar of xoxomos
xoxomos

ASKER

tried the rest of it.
RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    4325645712 bytes

Fixed Size                      736656 bytes
Variable Size               2415919104 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13164544 bytes

RMAN> run {
2> set until time "TO_DATE('19-OCT-03 00:00:00', 'DD-MON-YY HH24:MI:SS')";
3> allocate channel d1 type disk;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }

executing command: SET until clause

allocated channel: d1
channel d1: sid=11 devtype=DISK

Starting restore at 06-NOV-03

released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/06/2003 16:11:01
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 13 found to restore
RMAN-06023: no backup or copy of datafile 12 found to restore
RMAN-06023: no backup or copy of datafile 11 found to restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>
Avatar of xoxomos
xoxomos

ASKER

No I have only the 6 and 7.  I don't think I had the catalog then.  I believe there is some way to catalog them.
Avatar of xoxomos
xoxomos

ASKER

RMAN>catalog backup '<path/to/your/rman/backupfile>';

5. RMAN> restore database;
6. RMAN>recover database;

I'll try putting this path to the 6 and 7.
Avatar of xoxomos
xoxomos

ASKER

Doing a list backup i get

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
103     Full    1017M      DISK        00:05:32     24-OCT-03
        BP Key: 111   Status: AVAILABLE   Tag: TAG20031024T103614
        Piece Name: /u02/app/oracle/backup/haypsdmd/HAYPSDMD_6_1
  SPFILE Included: Modification time: 19-SEP-03
  List of Datafiles in backup set 103
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/system01
.dbf
  2       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/undotbs0
1.dbf
  3       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/cwmlite0
1.dbf
  4       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/drsys01.
dbf
  5       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/example0
1.dbf
  6       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/indx01.d
bf
  7       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/odm01.db
f
  8       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/tools01.
dbf
  9       Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/users01.
dbf
  10      Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/xdb01.db
f

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
104     Full    827M       DISK        00:12:04     24-OCT-03
        BP Key: 112   Status: AVAILABLE   Tag: TAG20031024T103614
        Piece Name: /u02/app/oracle/backup/haypsdmd/HAYPSDMD_7_1
  List of Datafiles in backup set 104
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING.
dbf
  12      Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/DATA_WAR
EHOUSE.dbf
  13      Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/DWH_INDE
X.dbf
  14      Full 2199601849359 24-OCT-03 /u02/app/oracle/oradata/haypsdmd/STAGING_
INDEX.dbf
SOLUTION
Avatar of Kong
Kong
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seazodiac
seazodiac
Flag of United States of America image
xoxomos:

when you issue RMAN>recover database , try to use

RMAN>recover database using backup controlfile;
Avatar of Kong
Kong
Here's what you do to recover the database to the point PRIOR to the resetlogs:

- startup nomount
- startup RMAN but do not connect
RMAN> set dbid=3986756888
RMAN> connect target /
RMAN> restore controlfile from '/u02/app/oracle/backup/haypsdmd/HAYPSDMD_6_1';
RMAN> alter database mount;
RMAN> sql 'SELECT RESETLOGS_CHANGE# FROM V$DATABASE';
            XXXXXX -- This is the value of SCN prior to resetlogs or you can get it from alert.log

Try the following first:
RMAN> RUN {
                     SET UNTIL SCN XXXXXX;
                     recover database;
                    }

If that fails:

RMAN> RUN {
                     SET UNTIL SCN XXXXXX;
                     recover datafile system01.dbf from tag=TAG20031024T103614;
                     recover datafile undotbs01.dbf from tag=TAG20031024T103614;
                     ... add all datafiles from this tag
                     recover datafile STAGING.dbf from tag=TAG20031024T103614;
                     ... add all datafiles from this tag :-/
                     recover database;
                    }

You'll then need to open resetlogs (again)...
Avatar of seazodiac
seazodiac
Flag of United States of America image
Xoxomos:
this is the complete procedure :


$rman catalog rman/rman@<rman_db_sid> target sys/password@<target_db_id>

RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                        set until time "TO_DATE('23-OCT-03 00:00:00',
                                                             'DD-MON-YY HH24:MI:SS')";
                       allocate channel d1 type disk;
                       restore database;
                       recover database using backup controlfile;
                       alter database open resetlogs;
                       }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                  allocate channel d1 type disk;
                  backup database;
                 }

Avatar of xoxomos
xoxomos

ASKER

Looking for syntax error.....
psdwd /sysadm/oracle $ rman catalog rman/rman@OEMREP target sys/a10n@haypsdm
d

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: HAYPSDMD (DBID=3986756888)
connected to recovery catalog database

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     466618336 bytes

Fixed Size                      731104 bytes
Variable Size                352321536 bytes
Database Buffers             100663296 bytes
Redo Buffers                  12902400 bytes

RMAN> run {
2> set until time "TO_DATE('23-OCT-03 00:00:00','DD-MON-YY HH24:MI:ss')";
3> allocate channel d1 type disk;
4> restore database;
5> recover database using backup controlfile;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "archivelog, che
ck, delete, from, high, noredo, ;, skip, tablespace, until"
RMAN-01008: the bad identifier was: using
RMAN-01007: at line 5 column 18 file: standard input

RMAN>
Avatar of xoxomos
xoxomos

ASKER

it doesn't like that 'using backup..''
Avatar of seazodiac
seazodiac
Flag of United States of America image
Ok, I will repost it, hopefully this time it works without a hiccup.

$rman catalog rman/rman@<rman_db_sid> target sys/password@<target_db_id>

RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                       allocate channel d1 type disk;
                       restore database;
                       recover database until time '23-OCT-03 00:00:00' using backup controlfile;
                       alter database open resetlogs;
                       }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                  allocate channel d1 type disk;
                  backup database;
                 }

Avatar of xoxomos
xoxomos

ASKER

Looking for syntax errors .........
Recovery Manager complete.
psdwd /sysadm/oracle $ rman

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> set dbid=3986756888

executing command: SET DBID

RMAN> connect target /

connected to target database:  (not mounted)

RMAN> restore controlfile from '/ustage/neil/rman/HAYPSDMD_6_1';

Starting restore at 06-NOV-03

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/haypsdmd/control01.ctl
output filename=/u01/app/oracle/oradata/haypsdmd/control02.ctl
output filename=/u01/app/oracle/oradata/haypsdmd/control03.ctl
Finished restore at 06-NOV-03

RMAN> run {
2> set until SCN 162043;
3> recover database;
4> }

executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 11/06/2003 21:49:03
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted

RMAN> run {
2> set until SCN 162043;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 06-NOV-03
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/06/2003 21:50:01
RMAN-06556: datafile 1 must be restored from backup older than scn 162043

RMAN> run {
2> set until scn 162043;
3> recover datafile system01.dbf from tag=TAG20031024T103614;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "double-quoted-s
tring, integer, single-quoted-string"
RMAN-01008: the bad identifier was: system01
RMAN-01007: at line 3 column 18 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "dot": expecting one of: "allocate, alter, backu
p, beginline, blockrecover, catalog, change, connect, copy, create, crosscheck,
configure, duplicate, debug, delete, drop, exit, endinline, host, {, library, li
st, mount, open, print, quit, recover, register, release, replace, report, renor
malize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, spool, s
tartup, shutdown, send, show, test, upgrade, validate"
RMAN-01007: at line 3 column 26 file: standard input

RMAN>
Avatar of seazodiac
seazodiac
Flag of United States of America image
Have you tried these? I fixed the syntax error in my last script in the RUN{} block.

$rman catalog rman/rman@<rman_db_sid> target sys/password@<target_db_id>

RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                       allocate channel d1 type disk;
                       restore database;
                       recover database until time '23-OCT-03 00:00:00' using backup controlfile;
                       alter database open resetlogs;
                       }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                  allocate channel d1 type disk;
                  backup database;
                 }

Avatar of xoxomos
xoxomos

ASKER

It doesn't like the using:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "archivelog, che
ck, delete, from, noredo, ;, skip"
RMAN-01008: the bad identifier was: using
RMAN-01007: at line 4 column 50 file: standard input

RMAN>

Total System Global Area     466618336 bytes

Fixed Size                      731104 bytes
Variable Size                352321536 bytes
Database Buffers             100663296 bytes
Redo Buffers                  12902400 bytes

RMAN> run {
2> allocate channel d1 type disk;
3> restore database;
4> recover database until time '29-OCT-03 00:00:00' using backup controlfile;
Avatar of seazodiac
seazodiac
Flag of United States of America image
Please be patient, xoxomos:

I think this time I got the syntax correct.

try one more time:

RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                      allocate channel d1 type disk;
                      restore database;
                      RECOVER DATABASE UNTIL TIME '2003-10-23:00:00:00' USING BACKUP CONTROLFILE;
                      alter database open resetlogs;
                      }

after you recover immediately do a reset again, then backup

RMAN>reset database;
RMAN> run{
                 allocate channel d1 type disk;
                 backup database;
                }

Avatar of xoxomos
xoxomos

ASKER

We're out of time.   The reconfiguration is finished and thry're going to restore from about a week ago.   The best I can put togather is initially I recovered until cancel, canceled and everything came up fine.  At some point after the dbf files were deleted.  I used a different backup and restored but this time used the resetlogs, immediately took a backup.  For a second time the files were deleted including the backup I took.  Again I tried using my own backup  to restore and recover, this time Oracle just kept refusing.  I do know a few more situations I need to avoid now.
Avatar of seazodiac
seazodiac
Flag of United States of America image
you did try these steps, did you? take a load of the time format, it's different, I am thinking that's why RMAN keep complainin'....


RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> run {
                      allocate channel d1 type disk;
                     restore database;
                     RECOVER DATABASE UNTIL TIME '2003-10-23:00:00:00' USING BACKUP CONTROLFILE;
                     alter database open resetlogs;
                     }

Avatar of xoxomos
xoxomos

ASKER

Time ran out.  The boss decided to re-re configure and restore from some os copies from about 10 days ago.  Still got questions about that whole SCN business.
Avatar of Kong
Kong
Sorry, I'm in Sydney...

Correction with syntax:

recover datafile system01.dbf from tag=TAG20031024T103614;

Should be:

recover datafile 1 from tag=TAG20031024T103614;

if you used the former, you would have had to quote the complete filename and path.

What happened was you were able to recover the controlfile, what you had to do next was recover the datafiles then roll the database forward. If you only recovered the controlfile then it would have had an SCN < the datafiles that's why you got: RMAN-06556: datafile 1 must be restored from backup older than scn 162043

The difference between mine & Seazodiac's solution was that he used a recovery catalogue & mine didn't - the principle were the same though.

We were close... Better luck next time.

K