DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sequence number for clone db

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    7

    sequence number for clone db

    I would like to take a clone of a database and have all the necessary steps. However
    one crucial step is not entirely clear to me and that is the part when I am on the target
    server and issue a 'recover database until sequence = xxx';

    My question is where do I obtain this sequence number from?

    is it from the source database, prior to the backup?

    Can I obtain the sequence number on the target database after I have performed 'restore database'

    I am not using Dataguard but simply a restore, recover and open resetlogs

    Can anyone help provide me the SQL and explain how I obtain the necessary sequence number

    Many thanks in advance

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    When you are recovering a database the database needs to be mounted.
    Which means that Oracle can read the control files. The control files track
    what sequences are needed to do a restore. Also each data file has an SCN
    number. Combined the oldest SCN number on any data file tells Oracle when
    it can stop recovering from archive logs. Oracle may not know how many archive
    logs that are needed, but it will always know the sequence of the archive log that
    it needs next.

  3. #3
    Join Date
    Feb 2001
    Posts
    7
    hi,

    thanks for your reply

    This is what I mean

    I restore database via rman - all OK

    then I ran

    alter database rename file '/ora01/app/oracle/oradata/netlinet/redo01.log' to '+RECO/NTRAIN/ONLINELOG/redo01a.log';
    alter database rename file '/ora01/app/oracle/oradata/netlinet/redo02.log' to '+RECO/NTRAIN/ONLINELOG/redo02a.log';
    alter database rename file '/ora01/app/oracle/oradata/netlinet/redo03.log' to '+RECO/NTRAIN/ONLINELOG/redo03a.log';

    Again All OK

    then the next step is this

    rman target /

    recover database until sequence=xxx;

    my question is where do I get this sequence number from

    if I query v$log_history - I get this

    SQL> select * from v$log_history;

    RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS CON_ID
    ---------- ---------- ---------- ---------- ------------- --------- ------------ ----------------- --------- ----------
    306 884613658 1 306 3210734 09-JUL-15 3222694 1 30-MAY-15 0
    307 884620308 1 307 3222694 09-JUL-15 3232592 1 30-MAY-15 0
    308 884626704 1 308 3232592 09-JUL-15 3241211 1 30-MAY-15 0
    309 884635242 1 309 3241211 09-JUL-15 3253537 1 30-MAY-15 0
    310 884641241 1 310 3253537 09-JUL-15 3261080 1 30-MAY-15 0
    311 884641845 1 311 3261080 09-JUL-15 3262666 1 30-MAY-15 0
    312 884646463 1 312 3262666 09-JUL-15 3268218 1 30-MAY-15 0
    313 884658741 1 313 3268218 09-JUL-15 3280972 1 30-MAY-15 0
    314 884666205 1 314 3280972 10-JUL-15 3288194 1 30-MAY-15 0
    315 884676099 1 315 3288194 10-JUL-15 3300953 1 30-MAY-15 0
    316 884678629 1 316 3300953 10-JUL-15 3304348 1 30-MAY-15 0

    RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS CON_ID
    ---------- ---------- ---------- ---------- ------------- --------- ------------ ----------------- --------- ----------
    592 886806061 1 592 6370610 03-AUG-15 6376468 1 30-MAY-15 0
    593 886809991 1 593 6376468 03-AUG-15 6380790 1 30-MAY-15 0
    594 886820418 1 594 6380790 04-AUG-15 6391251 1 30-MAY-15 0
    595 886839475 1 595 6391251 04-AUG-15 6408283 1 30-MAY-15 0
    596 886846469 1 596 6408283 04-AUG-15 6414600 1 30-MAY-15 0
    597 886847132 1 597 6414600 04-AUG-15 6415252 1 30-MAY-15 0


    with the first sequence being 306 and the last sequence being 597

    And not on either of those sequences will the recover work (see below)

    <<< By using either the first or last sequence - no joy >>>


    RMAN> recover database until sequence=306;

    Starting recover at 04-AUG-15
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=244 device type=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 08/04/2015 19:33:07
    RMAN-06556: datafile 1 must be restored from backup older than SCN 3222694

    RMAN> recover database until sequence=597;

    Starting recover at 04-AUG-15
    using channel ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 08/04/2015 19:33:18
    RMAN-06556: datafile 1 must be restored from backup older than SCN 6414600

    RMAN>


    So the question is - where may I obtain a sequence number from which is valid for mew to be able to restore the database

    I am not using Dataguard - simply a backup from another database and will then be using nid to rename the database

    I ran this process / procedure last week - but was given the sequence by another person - who is away this week and I had to perform another refresh

    if you can assist that will be most appreciated

    Thanks in advance
    Last edited by tspoon; 08-04-2015 at 03:00 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    My backup script records the scn and sequence numbers before and after the backup.
    That way I know exactly what scn/sequence to use if I want a point in time recovery.
    You can apply every archive log that is available, or just a couple. The scn on the
    control file can't be greater than that of the data files. As you apply archive logs
    the scn number increases on all the datafiles.

    At some point in this process you will need to cancel the recovery and
    "alter database open resetlogs;" Then you can use nid to rename the database. It will
    also change the db_id which will help with your rman catalog since you don't want
    more than one database with the same db_id.

    try switching to sqlplus and doing the following

    Code:
    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
    AUTO
    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
    CANCEL
    alter database open resetlogs;

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    My backup script records the scn and sequence numbers before and after the backup.
    That way I know exactly what scn/sequence to use if I want a point in time recovery.
    You can apply every archive log that is available, or just a couple. The scn on the
    control file can't be greater than that of the data files. As you apply archive logs
    the scn number increases on all the datafiles.

    At some point in this process you will need to cancel the recovery and
    "alter database open resetlogs;" Then you can use nid to rename the database. It will
    also change the db_id which will help with your rman catalog since you don't want
    more than one database with the same db_id.

    try switching to sqlplus and doing the following

    Code:
    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
    AUTO
    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
    CANCEL
    alter database open resetlogs;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width