Re: Clone from Cold Backup
- From: Sanjeev Mellacheruvu <sanjeevorcle@xxxxxxxxx>
- To: gurenich@xxxxxxxxx
- Date: Tue, 13 Jul 2010 16:48:46 -0700
Hi,
Are the SCN's in the backed up datafiles and from controlfile show they are
in sync:
After you mount the database ,after creating controlfile what do you see:
set numwidth 15
select checkpoint_change#,count(1) from v$datafile group by
checkpoint_change#; -- info from controlfile
select checkpoint_change#,count(1) from v$datafile_header group by
checkpoint_change#; -- info from datafile headers
-- Sanjeev.
On Tue, Jul 13, 2010 at 2:19 PM, Masha Gurenich <gurenich@xxxxxxxxx> wrote:
> So, I went through the alert log - the database went down gracefully. No
> crushing, no related to shutdown errors.
> I also recreated the controlfile backing it up to trace again - the very
> same thing. I am lost. Something is really wrong here..
>
> Please, help me to brainstorm this annoying issue. I do not believe in
> magic in IT. Please, throw any, ANY ideas (even the craziest) that might
> help to track down the problem, because it's just getting ridiculous...
>
> Thanks,
> M
>
>
> On Tue, Jul 13, 2010 at 4:10 PM, Masha Gurenich <gurenich@xxxxxxxxx>wrote:
>
>> Hmmm ... I think I did something like that:
>>
>> sql> alter database backup controlfile to trace;
>> sql> exit
>> [oracle@prod]$ cd udump
>> [oracle@prod]$ ls -ltr
>>
>> That usually gives me the latest file in the directory.. Let me recreate
>> the controlfile to trace again.. Even though I did that twice while
>> troubleshooting the issue..
>>
>>
>> On Tue, Jul 13, 2010 at 3:37 PM, kathryn axelrod <kat.axe@xxxxxxxxx>wrote:
>>
>>> Is it possible that the ccf.sql script was based off of an old trace by
>>> accident?
>>>
>>>
>>>
>>> On Tue, Jul 13, 2010 at 12:07 PM, Masha Gurenich <gurenich@xxxxxxxxx>wrote:
>>>
>>>> Hi list,
>>>>
>>>> I am having a very weird problem that I am not able to resolve myself.
>>>> Please, help.
>>>>
>>>> Here are the steps that I am doing for clone the database:
>>>>
>>>> 1) taking a cold backup of source DB
>>>> 2) alter database backup controlfile to trace; (on source)
>>>> 3) edit controlfile like this and save it as ccf.sql:
>>>>
>>>> CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG
>>>> <<<blah-blah-blah>>>
>>>> CHARACTER SET AL32UTF8
>>>> ;
>>>> -- Configure RMAN configuration record 1
>>>> VARIABLE RECNO NUMBER;
>>>> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION
>>>> POLICY','TO REDUNDANCY 1');
>>>> -- Configure RMAN configuration record 2
>>>> VARIABLE RECNO NUMBER;
>>>> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
>>>> AUTOBACKUP','ON');
>>>> -- Configure RMAN configuration record 3
>>>> VARIABLE RECNO NUMBER;
>>>> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
>>>> AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/u04/rman/TEST/%F''');
>>>> -- Configure RMAN configuration record 4
>>>> VARIABLE RECNO NUMBER;
>>>> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE
>>>> TYPE DISK FORMAT ''/u04/rman/TEST/%T_%U''');
>>>> ALTER DATABASE OPEN RESETLOGS;
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_01.dbf'
>>>> SIZE 3345M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_02.dbf'
>>>> SIZE 1048576000 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2500M;
>>>>
>>>>
>>>> 4) I copy the cold backup datafiles from source to target server
>>>> 5) rename the datafiles
>>>> 6) sqlplus / as sysdba (on target)
>>>> 7) startup nomount
>>>> 8) @ccf.sql
>>>> 9) and here it comes:
>>>>
>>>> SQL> startup nomount
>>>> ORACLE instance started.
>>>>
>>>> Total System Global Area 369098752 bytes
>>>> Fixed Size 2084072 bytes
>>>> Variable Size 230687512 bytes
>>>> Database Buffers 130023424 bytes
>>>> Redo Buffers 6303744 bytes
>>>> SQL> @ccf
>>>>
>>>> Control file created.
>>>>
>>>> BEGIN
>>>> SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/u04/rman/TEST/snapcf_TEST.f');
>>>> END;
>>>>
>>>> *
>>>> *ERROR at line 1:
>>>> ORA-00236: snapshot operation disallowed: mounted control file is a
>>>> backup
>>>> ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2108
>>>> ORA-06512: at line 1*
>>>>
>>>>
>>>>
>>>> PL/SQL procedure successfully completed.
>>>>
>>>>
>>>> PL/SQL procedure successfully completed.
>>>>
>>>>
>>>> PL/SQL procedure successfully completed.
>>>>
>>>> ALTER DATABASE OPEN RESETLOGS
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01194: file 1 needs more recovery to be consistent
>>>> ORA-01110: data file 1: '/u02/oradata/TEST/system_TEST_01.dbf'
>>>>
>>>>
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_01.dbf'
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01109: database not open
>>>>
>>>>
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_02.dbf'
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01109: database not open
>>>>
>>>>
>>>> Well, duuuh, it is a backup, it was created by the backup controlfile to
>>>> trace... I was using this exact procedure for like, years.. Usually, it all
>>>> takes about 15 minutes to finish. What have happened? What did I do wrong?
>>>> What did I overlooked? I have a funny feeling that I am missing something
>>>> very simple here, but I cannot think of anything that would make any sense.
>>>> I mean, I am 101% positive that the cold backup was done correctly: the
>>>> database was shutdown, the files got copied from oradata to the cold backup
>>>> destination, the database then brought up. I use the script that makes cold
>>>> backup for 2.5 years, made hundreds of clones using this cold backup
>>>> script.. What else could it be??? Why the database is inconsistent mode?!!
>>>> I
>>>> also took all RMAN related stuff out from the ccf.sql script. Got the same
>>>> thing:
>>>>
>>>> cat ccf.sql
>>>>
>>>> CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG
>>>> <<<blah-blah>>>
>>>> CHARACTER SET AL32UTF8
>>>> ;
>>>> ALTER DATABASE OPEN RESETLOGS;
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_01.dbf'
>>>> SIZE 3345M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_02.dbf'
>>>> SIZE 1048576000 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2500M;
>>>> ~
>>>>
>>>> and here we go:
>>>>
>>>> [oracle@tds TEST]$ sqlplus / as sysdba
>>>>
>>>> SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 13 14:54:42 2010
>>>>
>>>> Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
>>>>
>>>> Connected to an idle instance.
>>>>
>>>> SQL> startup nomount
>>>> ORACLE instance started.
>>>>
>>>> Total System Global Area 369098752 bytes
>>>> Fixed Size 2084072 bytes
>>>> Variable Size 230687512 bytes
>>>> Database Buffers 130023424 bytes
>>>> Redo Buffers 6303744 bytes
>>>> SQL> @ccf
>>>>
>>>> Control file created.
>>>>
>>>> ALTER DATABASE OPEN RESETLOGS
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01194: file 1 needs more recovery to be consistent
>>>> ORA-01110: data file 1: '/u02/oradata/TEST/system_TEST_01.dbf'
>>>>
>>>>
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_01.dbf'
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01109: database not open
>>>>
>>>>
>>>> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/TEST/temp_TEST_02.dbf'
>>>> *
>>>> ERROR at line 1:
>>>> ORA-01109: database not open
>>>>
>>>>
>>>> SQL> recover database;
>>>> ORA-00283: recovery session canceled due to errors
>>>> ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
>>>>
>>>>
>>>> SQL> recover database using backup controlfile;
>>>> ORA-00279: change 862393254 generated at 07/12/2010 17:34:08 needed for
>>>> thread
>>>> 1
>>>> ORA-00289: suggestion : /u04/archive/TEST/1_1_724181643.dbf
>>>> ORA-00280: change 862393254 for thread 1 is in sequence #1
>>>>
>>>>
>>>> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>>>>
>>>> ORA-00308: cannot open archived log
>>>> '/u04/archive/TEST/1_1_724181643.dbf'
>>>> ORA-27037: unable to obtain file status
>>>> Linux-x86_64 Error: 2: No such file or directory
>>>> Additional information: 3
>>>>
>>>> I cannot understand, how the database can be in inconsistent mode if I
>>>> am using the cold backup???? I checked if something happens on the source
>>>> DB
>>>> while the cold backup is being kicked off: nope, nothing. It's done at 3AM
>>>> on Monday - nobody is in there, no cronjobs scheduled for this time -
>>>> nothing..
>>>>
>>>> Please, shed some light if you can for I am close to a liiiiitle
>>>> hysterics.. :))
>>>>
>>>> a huuuuge thanks beforehand,
>>>> M
>>>>
>>>>
>>>
>>
>
Other related posts:
