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 >>> >>> >> >