This discussion is archived
9 Replies Latest reply: Nov 29, 2012 5:29 PM by mseberg RSS

alter database open resetlog with ora-01666: controlfile is from standby

user569151 Newbie
Currently Being Moderated
I have an issue here after I restore/recovered a backup from source.

On target, restore/recover all finished successfully, but alter database open resetlog with following errors:

ora-01666 .

My steps are following:

1). make a full backup as follows on source (which is a prod standby)
configure controlfile autobackup on;
configure snapshot controlfile name to '/oracle_backup/rman/snapf.f'
BACKUP DATABASE TAG 'FULL'
2). scp the backupsets over to the target
3). in rman , set dbid as the source dbid, restored the snapf.f and then catalog the lcoation.
4). then restore, recover ( all went well).
5). when I did alter database open resetlogs, it give me ora-01666 error.
6). alter database open worked, but the db is now in the standby mode and readonly .

I checked the parameters, the dbname points to the dbname in source, and db_unique_name point to the test name on target. All those two parameters were reset by RMAN Duplicate.

We don't want a standby db, we need a test environment copied from the sourc.e

How do I proceed from here?

Thanks
  • 1. Re: alter database open resetlog with ora-01666: controlfile is from standby
    mseberg Guru
    Currently Being Moderated
    Hello;

    Can you post the results of this :

    RMAN> list backup of controlfile;

    If you are up for trying the restore again I have an example here that may help :

    http://www.visi.com/~mseberg/rman/restore_database_without_catalog.html

    Best Regards

    mseberg

    Edited by: mseberg on Nov 29, 2012 6:15 PM
  • 2. Re: alter database open resetlog with ora-01666: controlfile is from standby
    user569151 Newbie
    Currently Being Moderated
    where should I issue this command?source or target?
  • 3. Re: alter database open resetlog with ora-01666: controlfile is from standby
    mseberg Guru
    Currently Being Moderated
    Target.

    But if the output appears bogus then try it on the source too.

    Best Regards

    mseberg
  • 4. Re: alter database open resetlog with ora-01666: controlfile is from standby
    user569151 Newbie
    Currently Being Moderated
    RMAN> list backup of controlfile;

    using target database control file instead of recovery catalog

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


    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    29 Full 33.95M DISK 00:00:03 29-NOV-12
    BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20121129T232835
    Piece Name: +FRA/tst/autobackup/2012_11_29/s_800485820.2404.800666919
    Standby Control File Included: Ckp SCN: 91383620735 Ckp time: 27-NOV-12

    RMAN>


    This is on target. The source db is a production standby.
  • 5. Re: alter database open resetlog with ora-01666: controlfile is from standby
    user569151 Newbie
    Currently Being Moderated
    since source is a production standby, I wonder do I have to do anything to the controlfile from autobackup? we want this test environment refreshed by production data. It should be open read write, not physical standby.
  • 6. Re: alter database open resetlog with ora-01666: controlfile is from standby
    mseberg Guru
    Currently Being Moderated
    Hello again;

    And there's the problem, the only control file is a standby one. Compare yours to this example from mine :
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    4170117 Full 9.70M DISK 00:00:03 29-NOV-12
            BP Key: 4170119 Status: AVAILABLE Compressed: NO Tag: TAG20121129T125327
            Piece Name: /u01/oradata/RECOVER2_cold/control_RECOVER2_2821_20121129.bak
    Control File Included: Ckp SCN: 1307992402301 Ckp time: 29-NOV-12
    
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    4170127 Full 9.73M DISK 00:00:01 29-NOV-12
            BP Key: 4170129 Status: AVAILABLE Compressed: NO Tag: TAG20121129T125332
            Piece Name: /u01/app/oracle/flash_recovery_area/RECOVER2/autobackup/2012_11_29/o1_mf_s_800628756_8chcsf10_.bkp
    Control File Included: Ckp SCN: 1307992402301 Ckp time: 29-NOV-12
    Notice the auto backup and y ".bak" version.

    I think you need to take another backup. If you are using Oracle 11 I have some more options.

    For example I refresh my test system using this :

    http://www.visi.com/~mseberg/rman/diff_database_duplicating.html

    Sometimes I save off the users, roles etc and replace them back afterwards.

    Later

    You might be able to save what you have by creating a trace file on the source and then using it to recreate a control file on the copy.
    If you run a backup trace command daily you can probably pull some SQL from your daily trace file like this.
    
    CREATE CONTROLFILE REUSE DATABASE "RECOVER2" NORESETLOGS  NOARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
    ...
    ...
    You can save this as say cf.sql and then :
    SQL> startup nomount 
    
    Run the SQL as shown above
    
    SQL> @cf.sql
    
    Control file created.
    
    SQL>
    
    Check the status of the database
    
    SQL> select status from v$instance;
    
    -- should be mounted
    
    If the database is mounted, can it be opened?
    
    Open the database
    
    SQL> alter database open;
    
    Database altered.
    Best Regards

    mseberg

    Edited by: mseberg on Nov 29, 2012 7:10 PM
  • 7. Re: alter database open resetlog with ora-01666: controlfile is from standby
    user569151 Newbie
    Currently Being Moderated
    How come it only has standby controlfile backup?

    On the prod standby, my backup command is like this:

    configure controlfile autobackup on;
    configure snapshot controlfile name to '/oracle_backup/rman/snapf.f';
    configure device type disk backup type to compressed backupset;
    BACKUP DATABASE TAG 'FULL';
    backup archivelog all;

    How do I be sure the next full backup has regular controlfile on?
  • 8. Re: alter database open resetlog with ora-01666: controlfile is from standby
    mseberg Guru
    Currently Being Moderated
    You can force it like this :
    allocate channel d1 type disk;
    backup format '/u01/oradata/RECOVER2_cold/%d_COLD_DB_%u' database;
    backup spfile format '/u01/oradata/RECOVER2_cold/spfile_%d_%s_%T.bak' tag = 'RECOVER2_SPFILE';
    backup current controlfile format '/u01/oradata/RECOVER2_cold/control_%d_%s_%T.bak';
    OR
    backup format '/u03/oradata/RCATALOG_rman/al_t%t_s%s_p%p' archivelog all;
    backup current controlfile format '/u03/oradata/RCATALOG_rman/sb_t%t_s%s_p%p';
    release channel d1;
    You might consider the control from trace idea I just added to my prior post.
  • 9. Re: alter database open resetlog with ora-01666: controlfile is from standby
    user569151 Newbie
    Currently Being Moderated
    ok, I will test it. Thanks, will update soon.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points