9 Replies Latest reply: Nov 29, 2012 7:29 PM by mseberg RSS

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

    user569151
      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
          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
            where should I issue this command?source or target?
            • 3. Re: alter database open resetlog with ora-01666: controlfile is from standby
              mseberg
              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
                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
                  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
                    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
                      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
                        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
                          ok, I will test it. Thanks, will update soon.