Re: Clone from Cold Backup
- From: David Roberts <big.dave.roberts@xxxxxxxxxxxxxx>
- To: kat.axe@xxxxxxxxx
- Date: Tue, 13 Jul 2010 20:52:13 +0100
My first action would be to check the alert.log, while shutdown immediate
should produce a consistent backup, there is the possibility that an
error occurred during this stage and the database crashed leaving the data
files in an inconsistent state.
Dave
On Tue, Jul 13, 2010 at 8: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:
