log_archive_start parameter is for enabling automatic archiving in 9i and "alter database archivelog" for changing database log mode to archive mode. both log_archive_start parameter to TRUE within parameter file and "alter database archivelog" command should be executed within 9i box for turning on the archiving properly.But in 10g its two in one you can enable archivelog when yours database in mount state by executing "alter database archivelog" only. Here i try to find out how easy to enable archivelog in 10g rather in 9i.First i will show you how to enable archivelog mode in 9i then i will show you how easy it is in 10g. SQL> select banner from v$version 2 / BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production SQL> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination c:\sdsarchive Oldest online log sequence 18 Next log sequence to archive 20 Current log sequence 20 SQL> show parameter log_archive_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_start boolean TRUEThe parameter log_archive_start is true within init parameter file now make it false and then start database from this parameter file.*.log_archive_start=FALSE SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile=c:\initSDS.ora ORACLE instance started. Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> archive log list Database log mode Archive Mode Automatic archival Disabled Archive destination c:\sdsarchive Oldest online log sequence 18 Next log sequence to archive 20 Current log sequence 20 SQL> show parameter log_archive_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_start boolean FALSE SQL> SQL> alter system switch logfile 2 / System altered. though "Database log mode" is in "Archive mode" but its "Automatic archival" is disabled due to this upon log switching redo log is not getting archived within archive destination at c:\sdsarchive ,this automatic archival is enabled by log_archive_start parameter to TRUE. Now change log_archive_start parameter from FALSE to TRUE in the parameter file and start database with this parameter file. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. *.log_archive_start=TRUE SQL> startup pfile=c:\initSDS.ora ORACLE instance started. Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination c:\sdsarchive Oldest online log sequence 20 Next log sequence to archive 22 Current log sequence 22 SQL> show parameter log_archive_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_start boolean TRUE SQL> alter system switch logfile 2 / System altered.Now redo log get archived within destination c:\sdsarchive ,SQL>shutdown immediate SQL>startup mount SQL> alter database noarchivelog 2 / Database altered. SQL> alter database open 2 / Database altered. SQL> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination c:\sdsarchive Oldest online log sequence 21 Current log sequence 23 SQL> alter system switch logfile 2 / System altered. SQL> / System altered. You will not see any archive file within floder c:\sdsarchive with no archive mode but enabling automatic archival by using parameter log_archive_start to TRUE, either one of them database log mode to noarchivelog or log_archive_start set to false will not let archived the redo logs,both are necessary for enabling the archive mode properly in 9i. But the case is different within 10g ,in 10g log_archive_start is deprecated if you set this parameter TRUE within parameter file then at startup you will get error ORA- 32006. SQL> conn sys/sys as sysdba Connected. SQL> select name from v$database 2 / NAME --------- PROD SQL> select banner from v$version 2 / BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production CORE 10.1.0.2.0 Production TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production SQL> startup pfile=C:\oracle\product\10.1.0\admin\PROD\pfile\init.ora.412200817533 ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. Database opened. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 SQL> log_archive_start has no effect in 10g either you set it TRUE or FALSE.In 10g you can turn on archive log mode and automatic archival enabled only with a single command in mount mode is "alter database archivelog" SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Next log sequence to archive 16 Current log sequence 16 SQL> alter database noarchivelog 2 / Database altered. SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Current log sequence 16 SQL> alter database archivelog 2 / Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Next log sequence to archive 16 Current log sequence 16 SQL>
Friday, May 23, 2008
How it is easy to enable archivelog in 10g
Friday, May 16, 2008
ORA-01152: file 1 was not restored from a sufficiently old backup
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select name 2 from v$database 3 / NAME --------- PROD SQL> select status 2 from v$instance 3 / STATUS ------------ MOUNTED SQL> alter database open 2 / alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs 2 / alter database open resetlogs * ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF' SQL> recover database using backup controlfile until cancel ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'Check Alert Log file you will get the original name of yours database fileWed May 14 18:21:53 2008 alter database recover logfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO03.LOG' Media Recovery Log C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO03.LOG File #5 added to control file as 'UNNAMED00005'. Originally created as: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' 2 / alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 5 - new file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' not found ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified.you physically deleted the mydata.dbf file from 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'what you will do you will copy the users01.dbf to mydata.dbf by using os utility and then rename the unnamed00005 to mydata.dbfSQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' 2 / Database altered.Its wrong trick to copy user01.dbf to mydata.dbf by osSQL> recover database using backup controlfile until cancel ORA-00283: recovery session canceled due to errors ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' ORA-01122: database file 5 failed verification check ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' ORA-01251: Unknown File Header Version read for file number 5Delete the mydata.dbf by os.And try to recover databaseSQL> recover database using backup controlfile until cancel ORA-00283: recovery session canceled due to errors ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'Now you can see file mydata.dbf is missing now right trick is to create mydata.dbf from SQL.SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' 2 / Database altered. SQL> recover database using backup controlfile until cancel ORA-00279: change 456977 generated at 05/14/2008 17:23:49 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_16\O1_MF_1_1_%U_.ARC ORA-00280: change 456977 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 458022 generated at 05/14/2008 18:01:07 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_16\O1_MF_1_2_%U_.ARC ORA-00280: change 458022 for thread 1 is in sequence #2 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_14\O1_MF_1_1_42ORNO5P_.ARC' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} C:\oracle\product\10.1.0\oradata\Prod\REDO01.log Log applied. Media recovery complete. As i applied redo for the REOD01.log file it was current in my case thats why it is giving Media recovery complete. SQL> alter database open resetlogs 2 / Database altered. SQL> select substr(name,1,100) name,status 2 from v$datafile 3 / NAME STATUS ------------------------------------------------------ ------- C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF SYSTEM C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF ONLINE C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF ONLINE C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF ONLINE C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF ONLINE SQL>
Wednesday, May 14, 2008
How to perform Incomplete Recovery
RMAN> backup database; user is sys SQL> select count(*) from t 2 / COUNT(*) ---------- 0 SQL> begin 2 for i in 1..50 3 loop 4 insert into t values (i); 5 end loop; 6 end; 7 . SQL> / PL/SQL procedure successfully completed. SQL> commit 2 / Commit complete. SQL> select count(*) from t 2 / COUNT(*) ---------- 50 SQL> select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') 2 from dual 3 / TO_CHAR(SYSDATE,'DD ------------------- 13-05-2008 16:09:26 SQL> delete from t where a between 1 and 25 2 / 25 rows deleted. SQL> commit 2 / Commit complete. SQL> select count(*) from t 2 / COUNT(*) ---------- 25 RMAN> run 2> { 3> shutdown immediate; 4> startup mount; 5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')"; 6> recover database; 7> } database closed database dismounted Oracle instance shut down connected to target database (not started) Oracle instance started database mounted Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes executing command: SET until clause Starting recover at 13-MAY-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/13/2008 16:13:00 RMAN-06555: datafile 1 must be restored from backup created before 13-MAY-08 Yours datafiles are newer than 13-MAY-2008 16:09:26 which contains the committed data ,for incomplete recovery how will you apply redo?? To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover. RMAN> run 2> { 3> shutdown immediate; 4> startup mount; 5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')"; 6> restore database; 7> recover database; 8> } database dismounted Oracle instance shut down connected to target database (not started) Oracle instance started database mounted Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes executing command: SET until clause Starting restore at 13-MAY-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DB restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2008_ _13\O1_MF_NNNDF_TAG20080513T155846_42LX37BF_.BKP tag=TAG20080513T155846 channel ORA_DISK_1: restore complete Finished restore at 13-MAY-08 Starting recover at 13-MAY-08 using channel ORA_DISK_1 starting media recovery media recovery complete Finished recover at 13-MAY-08 RMAN> alter database open resetlogs 2> ; database opened SQL> conn sys/sys as sysdba Connected. SQL> desc t Name Null? Type ----------------------------------------- -------- --------------------------- A NUMBER SQL> select count(*) 2 from t 3 / COUNT(*) ---------- 50 SQL>
How to run trace?
login as: oraprod oraprod@192.168.3.8's password: Last login: Mon May 12 04:50:10 2008 from trainee1.parazelsus.pk [oraprod@dbprod ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 13 23:44:24 2008 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> conn sys/sys as sysdba Connected. SQL> grant alter session to scott 2 / Grant succeeded. SQL> conn scott/tiger Connected. SQL> set linesize 1000 SQL> alter session set sql_trace=true 2 / Session altered. SQL> select * from emp 2 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> alter session set sql_trace=false 2 / Session altered. SQL> disconnect Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> exit [oraprod@dbprod ~]$ cd /orahome/PROD/db/tech_st/10.2.0/admin/PROD_dbprod/udump [oraprod@dbprod udump]$ ls -lrt total 8928 . . . . -rw-r--r-- 1 oraprod dba 845 Apr 15 17:53 prod_ora_4267.trc -rw-r----- 1 oraprod dba 689 Apr 15 19:54 prod_ora_28473.trc -rw-r--r-- 1 oraprod dba 718 Apr 15 19:54 prod_ora_28499.trc -rw-r--r-- 1 oraprod dba 827 Apr 15 21:55 prod_ora_30662.trc -rw-r--r-- 1 oraprod dba 4471139 Apr 16 22:51 prod_ora_24132.trc -rw-r--r-- 1 oraprod dba 718 Apr 16 22:51 prod_ora_24610.trc -rw-r----- 1 oraprod dba 661 Apr 16 22:51 prod_ora_24586.trc -rw-r--r-- 1 oraprod dba 986 Apr 16 22:52 prod_ora_24612.trc -rw-r----- 1 oraprod dba 659 Apr 17 10:51 prod_ora_2315.trc -rw-r--r-- 1 oraprod dba 716 Apr 17 10:51 prod_ora_2339.trc -rw-r--r-- 1 oraprod dba 782 Apr 17 10:51 prod_ora_2341.trc -rw-r--r-- 1 oraprod dba 845 Apr 17 14:19 prod_ora_6153.trc -rw-r----- 1 oraprod dba 661 May 8 07:13 prod_ora_27379.trc -rw-r--r-- 1 oraprod dba 718 May 8 07:13 prod_ora_27403.trc -rw-r--r-- 1 oraprod dba 784 May 8 07:13 prod_ora_27405.trc -rw-r--r-- 1 oraprod dba 27354 May 13 23:46 prod_ora_4613.trc [oraprod@dbprod udump]$ tkprof prod_ora_4613.trc prod_ora_4613.txt explain=scott/tiger sys=no TKPROF: Release 10.2.0.2.0 - Production on Tue May 13 23:48:25 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. [oraprod@dbprod udump]$ ls -lrt total 8936 . . . . -rw-r----- 1 oraprod dba 661 Apr 16 22:51 prod_ora_24586.trc -rw-r--r-- 1 oraprod dba 986 Apr 16 22:52 prod_ora_24612.trc -rw-r----- 1 oraprod dba 659 Apr 17 10:51 prod_ora_2315.trc -rw-r--r-- 1 oraprod dba 716 Apr 17 10:51 prod_ora_2339.trc -rw-r--r-- 1 oraprod dba 782 Apr 17 10:51 prod_ora_2341.trc -rw-r--r-- 1 oraprod dba 845 Apr 17 14:19 prod_ora_6153.trc -rw-r----- 1 oraprod dba 661 May 8 07:13 prod_ora_27379.trc -rw-r--r-- 1 oraprod dba 718 May 8 07:13 prod_ora_27403.trc -rw-r--r-- 1 oraprod dba 784 May 8 07:13 prod_ora_27405.trc -rw-r--r-- 1 oraprod dba 27354 May 13 23:46 prod_ora_4613.trc -rw-r--r-- 1 oraprod dba 7245 May 13 23:48 prod_ora_4613.txt [oraprod@dbprod udump]$ vi prod_ora_4613.txt
Saturday, May 10, 2008
Recovery with an old controlfile/recover database using backup controlfile
Question :
I have a full backup wıth control file and archıvelogs which is taken by rman yesterday.Today my db ıs crashed at the evenıng and control fıle ıs corrupted. When I restore yesterdays controlfıle from backupset,and restore all yesterday's datafiles,can I apply todays archivelogs and apply to these datafiles? Do I lose any data in this scenerio?Answer :
When you take online/hot backup then yours backup is inconsistent , upon recovery oracle makes inconsistent backups consistent by applying all archived and online redo logs ,oracle makes recovery by reading the earliest/oldest SCN in any of the datafile headers and apply the changes from the logs back into the datafile. yes indeed controlfile has repositry about alls backup stuff like database backup as well archivelog backup.If you take backup of archivelog then it goes to control file entry ,upon restoration activity oracle reads the controlfile to ask where archivelog exist to be restored.If yours todays archivelogs are on disk not in controlfile repositry then oracle will apply these todays archivelog regardless controlfile knows or not,If the RMAN repository or controlfile indicates that no copies of a needed log sequence number exist on disk, then RMAN looks in backups and restores archived redo logs as needed to perform the media recovery,there is no concern controlfile for applying archivelogs at all,if you have todays archivelogs exist then you will lose data only which is in current redo log thats why multiplexing redo log came into ours dbackup plan.Question :
but control file is older it doesnt store the info of new archivelogs.How can I add?Answer :
Contorolfile older issue raise when you have controlfile for yesterday and datafile for today,if you restore yesterdays controlfle and restore the datafile from this yesterday controlfile and then started recovery to this yesterday controlfile then there would be no any issue to recover the database till before crash using todays archivelogs.Here you have to remember one thing you will lose only data for current redo log ,if you have redo log keep intact by multiplexing then you can recover this multiplexed current redo log data also. If yesterdays controlfile dont know todays database physical activity i.e adding datafile then archivelog data for this todays datafile applying will raise the error during recovery. Here i am going to prove that yesterdays controlfile will not be an issue for tracking the todays archivelog during recovery process and it will apply safely.
SQL> select sysdate from dual 2 / SYSDATE --------- 19-APR-08 SQL> create table a as select * from all_objects 2 / Table created. SQL> create table b as select * from all_objects 2 / Table created. SQL> create table c as select * from all_objects 2 / Table created. SQL> create table d as select * from all_objects 2 / Table created. C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008 _04_19 04/19/2008 02:37 PM <DIR> . 04/19/2008 02:37 PM <DIR> .. 04/19/2008 02:36 PM 9,754,112 O1_MF_1_7_40MH8FC9_.ARC 04/19/2008 02:37 PM 9,753,088 O1_MF_1_8_40MHCDZ4_.ARC 2 File(s) 19,507,200 bytes 2 Dir(s) 58,626,342,912 bytes free RMAN> run 2> { 3> allocate channel t1 type disk; 4> backup database; 5> release channel t1; 6> allocate channel t2 type disk; 7> backup archivelog all; 8> release channel t2; 9> } released channel: ORA_DISK_1 allocated channel: t1 channel t1: sid=125 devtype=DISK Starting backup at 19-APR-08 channel t1: starting full datafile backupset channel t1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF channel t1: starting piece 1 at 19-APR-08 channel t1: finished piece 1 at 19-APR-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP comment=NONE channel t1: backup set complete, elapsed time: 00:01:56 Finished backup at 19-APR-08 Starting Control File and SPFILE Autobackup at 19-APR-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 19-APR-08 released channel: t1 allocated channel: t2 channel t2: sid=125 devtype=DISK Starting backup at 19-APR-08 current log archived channel t2: starting archive log backupset channel t2: specifying archive log(s) in backup set input archive log thread=1 sequence=7 recid=102 stamp=652458975 input archive log thread=1 sequence=8 recid=103 stamp=652459070 input archive log thread=1 sequence=9 recid=104 stamp=652459454 channel t2: starting piece 1 at 19-APR-08 channel t2: finished piece 1 at 19-APR-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_ANNNN_TAG20080419T144414_40MHQJNO_.BKP comment=NONE channel t2: backup set complete, elapsed time: 00:00:08 Finished backup at 19-APR-08 Starting Control File and SPFILE Autobackup at 19-APR-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 19-APR-08 released channel: t2 SQL> conn sys/sys@orcl1 as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008 _04_19 04/19/2008 02:44 PM <DIR> . 04/19/2008 02:44 PM <DIR> .. 04/19/2008 02:36 PM 9,754,112 O1_MF_1_7_40MH8FC9_.ARC 04/19/2008 02:37 PM 9,753,088 O1_MF_1_8_40MHCDZ4_.ARC 04/19/2008 02:44 PM 2,507,776 O1_MF_1_9_40MHQFYX_.ARC 3 File(s) 22,014,976 bytes 2 Dir(s) 58,033,848,320 bytes free SQL> conn sys/sys@orcl1 as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.--------------------------------------------------------------------- change the server date from 19-APR-2008 to 20-APR-2008 ---------------------------------------------------------------------SQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. Database opened. SQL> select sysdate from dual 2 / SYSDATE --------- 20-APR-08 SQL> conn scott/tiger@orcl1 Connected. SQL> create table e as select * from all_objects 2 / Table created. SQL> create table f as select * from all_objects 2 / Table created. SQL> create table g as select * from all_objects 2 / Table created. SQL> create table h as select * from all_objects 2 / Table created. C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_20>dir Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008 _04_20 04/20/2008 02:53 PM <DIR> . 04/20/2008 02:53 PM <DIR> .. 04/20/2008 02:51 PM 9,756,160 O1_MF_1_10_40P4KWQ8_.ARC 04/20/2008 02:52 PM 9,751,552 O1_MF_1_11_40P4MH79_.ARC 04/20/2008 02:53 PM 9,750,016 O1_MF_1_12_40P4NK9V_.ARC 3 File(s) 29,257,728 bytes 2 Dir(s) 57,978,466,304 bytes free SQL> conn sys/sys@orcl1 as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. C:\oracle\product\10.1.0\oradata\orcl1>dir Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\orcl1 04/18/2008 06:31 PM <DIR> . 04/18/2008 06:31 PM <DIR> .. 04/20/2008 02:55 PM 2,965,504 CONTROL01.CTL 04/20/2008 02:55 PM 2,965,504 CONTROL02.CTL 04/20/2008 02:55 PM 2,965,504 CONTROL03.CTL 04/20/2008 02:55 PM 10,486,272 REDO01.LOG 04/20/2008 02:55 PM 10,486,272 REDO02.LOG 04/20/2008 02:55 PM 10,486,272 REDO03.LOG 04/20/2008 02:55 PM 251,666,432 SYSAUX01.DBF 04/20/2008 02:55 PM 461,381,632 SYSTEM01.DBF 04/20/2008 02:55 PM 26,222,592 UNDOTBS01.DBF 04/20/2008 02:55 PM 53,747,712 USERS01.DBF 10 File(s) 833,373,696 bytes 2 Dir(s) 57,978,585,088 bytes free---------------------------------------------------------------------------- Delete alls database files ----------------------------------------------------------------------------C:\oracle\product\10.1.0\oradata\orcl1>del *.* C:\oracle\product\10.1.0\oradata\orcl1\*.*, Are you sure (Y/N)? Y C:\oracle\product\10.1.0\oradata\orcl1>dir Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\orcl1 04/20/2008 02:56 PM <DIR> . 04/20/2008 02:56 PM <DIR> .. 0 File(s) 0 bytes 2 Dir(s) 58,811,949,056 bytes free C:\oracle\product\10.1.0\oradata\orcl1> SQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes ORA-00205: error in identifying controlfile, check alert log for more info C:\>rman Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys@orcl1 connected to target database: orcl1 (not mounted) RMAN> restore controlfile from autobackup 2> ; Starting restore at 20-APR-08 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area database name (or lock name space) used for search: ORCL1 channel ORA_DISK_1: autobackup found in the recovery area channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP channel ORA_DISK_1: controlfile restore from autobackup complete output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL01.CTL output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL02.CTL output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL03.CTL Finished restore at 20-APR-08 RMAN> startup mount database is already started database mounted RMAN> restore database 2> ; ORACLE error from target database: ORA-19922: there is no parent row with id 0 and level 0 ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038 ORA-06512: at line 1 RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows Starting restore at 20-APR-08 Starting implicit crosscheck backup at 20-APR-08 released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 20-APR-08 Starting implicit crosscheck copy at 20-APR-08 using channel ORA_DISK_1 Finished implicit crosscheck copy at 20-APR-08 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP tag=TAG20080419T144156 channel ORA_DISK_1: restore complete Finished restore at 20-APR-08 RMAN> recover database 2> ; Starting recover at 20-APR-08 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 9 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC archive log thread 1 sequence 10 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC archive log thread 1 sequence 11 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC thread=1 sequence=9 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC thread=1 sequence=10 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC thread=1 sequence=11 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC thread=1 sequence=12 unable to find archive log archive log thread=1 sequence=13 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 04/20/2008 15:01:51 RMAN-06054: media recovery requesting unknown log: thread 1 seq 13 lowscn 698325 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 142 19-APR-08 Backup Piece 142 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP Archive Log 102 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_7_40MH8FC9_.ARC Archive Log 103 19-APR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_8_40MHCDZ4_.ARC RMAN> alter database open resetlogs 2> ; database opened RMAN> As you can see i have redundency 1 thats why at recovery these archivelogs are not getting applied,nevertheless these 19th April archivlogs are no more required to apply logs but as you can see 20th april logs are being applied. when i connect to sql i got alls table a,b,c,d,e,f,g but not h cause h data was in redolog and was not archived before smoking alls database files. SQL> conn scott/tiger@orcl1 Connected. SQL> desc a Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc b Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc c Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc d Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc e Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc f Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc g Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> desc h ERROR: ORA-04043: object h does not exist You can see table a,b,c,d recoverd which is for 19th April data and e,f,g recoverd cause 20th April archivelogs applied to 19th April backupset with 19th April controlfile. Question :But as i have old controlfile then whats the use of recover database using backup controlfile as i know recover database using backup controlfile is used when you have old controlfile.Answer :Yes you are right but you are missing obvious that using old controlfile recovery by using recover database using backup controlfile command is used when you have old controlfile and datafiles are current.Lets see a scenario which will depictyou a clear picture when to use recover database using backup controlfile. Lets suppose i am taking cold backup daily ,my procedure is to shutdown the database and then copy alls database file to somewhere safe daily at the end of day ,one day i lost/corrupt the controlfile, i dont have current controlfile i have only controlfile backup which is backed up as cold last day.In this case i have two options either i create the controlfile by using the create controlfile command then perform recovery if required and start up the database.Other option is to restore the last cold backed up controlfile and recover database using backup controlfile. If you try to recover database without using backup controlfile command then you will get the error.ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF' ORA-01207: file is more recent than controlfile - old controlfile ORA-1122: database file 1 failed verification check Cause: The information in this file (system01.dbf) is inconsistent with information from the control file. Action: Make sure that the db files and control files are the correct files for this database. ORA-1207 file is more recent than control file - old control file Cause: The control file change sequence number in the data file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough. Action: Use the current control file or do backup controlfile recovery to make the control file current. Be sure to follow all restrictions on doing a backup controlfile recovery.
SQL> conn sys/sys as sysdba Connected. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 SQL> select group#,members,status 2 from v$log 3 / GROUP# MEMBERS STATUS ---------- ---------- ---------------- 1 1 CURRENT 2 1 UNUSED 3 1 UNUSED SQL> SQL> conn scott/tiger Connected. SQL> create table x1 as select * from all_objects 2 / Table created. SQL> create table x2 as select * from all_objects 2 / Table created. SQL> conn sys/sys as sysdba Connected. SQL> alter system switch logfile 2 / System altered. SQL> select group#,members,status 2 from v$log 3 / GROUP# MEMBERS STATUS ---------- ---------- ---------------- 1 1 ACTIVE 2 1 CURRENT 3 1 UNUSED SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>Copy the database folder to somewhere safe placeC:\oracle\product\10.1.0\oradata>copy prod coldbackup prod\CONTROL01.CTL prod\CONTROL02.CTL prod\CONTROL03.CTL prod\REDO01.LOG prod\REDO02.LOG prod\REDO03.LOG prod\SYSAUX01.DBF prod\SYSTEM01.DBF prod\TEMP01.DBF prod\UNDOTBS01.DBF prod\USERS01.DBF 11 file(s) copied. C:\oracle\product\10.1.0\oradata>Now startup the database.SQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. Database opened.You have taken cold backup, now perform some activitySQL> select group#,members,status 2 from v$log 3 / GROUP# MEMBERS STATUS ---------- ---------- ---------------- 1 1 INACTIVE 2 1 CURRENT 3 1 UNUSED SQL> conn scott/tiger Connected. SQL> SQL> create table x3 as select * from all_objects 2 / Table created. SQL> alter system switch logfile 2 / System altered. SQL> select group#,members,status 2 from v$log 3 / GROUP# MEMBERS STATUS ---------- ---------- ---------------- 1 1 INACTIVE 2 1 ACTIVE 3 1 CURRENTNow delete or corrupt the controlfile ,i am deleting here alls controlfile hereSQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> C:\oracle\product\10.1.0\oradata\Prod>del *.ctl C:\oracle\product\10.1.0\oradata\Prod>dir *.ctl Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\Prod File Not FoundNow startup the databaseSQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes ORA-00205: error in identifying controlfile, check alert log for more info SQL> shutdown abort ORACLE instance shut down. SQL>Copy the controlfile from coldbackup to yours Prod database file folder.C:\oracle\product\10.1.0\oradata>copy c:\oracle\product\10.1.0\oradata\coldbackup\*.ctl C:\oracle\product\10.1.0\oradata\prod c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL01.CTL c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL02.CTL c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL03.CTL 3 file(s) copied. C:\oracle\product\10.1.0\oradata>Now the controlfiles from cold backup to prod folder copied succesfully ,start the databaseSQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF' ORA-01207: file is more recent than controlfile - old controlfile SQL> recover database ORA-00283: recovery session canceled due to errors ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF' ORA-01207: file is more recent than controlfile - old controlfile SQL> recover database using backup controlfile until cancel ORA-00279: change 333130 generated at 05/10/2008 16:42:05 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_%U_.ARC ORA-00280: change 333130 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 334273 generated at 05/10/2008 16:48:51 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_3_%U_.ARC ORA-00280: change 334273 for thread 1 is in sequence #3 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_42C2X57F_.ARC' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} C:\oracle\product\10.1.0\oradata\Prod\REDO03.log Log applied. Media recovery complete. SQL> alter database open resetlogs 2 / Database altered.Question :But You showed me up without any new added datafile with old controlfile recovery and yours scenario with cold backup ,my case is hot backup and i have new datafile but old controlfile backup ,i lost old control file but datafiles exist on disk presumably some data files are added in database file before losing controlfile and old controlfile does not know about it how will i make recovery in this scenarioAnswer :The process would be the same in yours scenario as i mentioned above, you will have to make recovery with "recover database using backup controlfile" when you have old controlfile and new datafiles.Lets see hereRecovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys@uat connected to target database: UAT (DBID=2679299118) RMAN> list backup of controlfile 2> ; using target database controlfile instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Full 2M DISK 00:00:03 16-MAY-08 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20080516T002026 Piece Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP Controlfile Included: Ckp SCN: 408253 Ckp time: 16-MAY-08 SQL> conn sys/sys@uat as sysdba Connected. SQL> select ts#,name 2 from v$tablespace 3 / TS# NAME ---------- ------------------------------ 0 SYSTEM 1 UNDOTBS1 2 SYSAUX 4 USERS 3 TEMP SQL> select ts#,file#,substr(name,1,50) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED ---------- ---------- -------------------------------------------------- ------- ---------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE SQL> create tablespace newtablespace datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\newdatafile01.dbf' size 100M 2 / Tablespace created.make sure added tablespace datafile is online and enabled for read and write.SQL> select ts#,name 2 from v$tablespace 3 / TS# NAME ---------- ------------------------------ 0 SYSTEM 1 UNDOTBS1 2 SYSAUX 4 USERS 3 TEMP 6 NEWTABLESPACE 6 rows selected. SQL> SQL> select ts#,file#,substr(name,1,50) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED ---------- ---------- -------------------------------------------------- ------- ---------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01 ONLINE READ WRITEmake default this newtablespace for the user scottSQL> alter user scott default tablespace newtablespace 2 / User altered. SQL> conn scott/tiger@uat Connected. SQL> create table v1 as select * from all_objects 2 / Table created. SQL> create table v2 as select * from all_objects 2 / Table created. SQL> conn sys/sys@uat as sysdba Connected. SQL> alter system switch logfile 2 / System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> Here i drop the controld files C:\oracle\product\10.1.0\oradata\uat>dir *.ctl Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\uat 05/26/2008 02:10 PM 2,899,968 CONTROL01.CTL 05/26/2008 02:10 PM 2,899,968 CONTROL02.CTL 05/26/2008 02:10 PM 2,899,968 CONTROL03.CTL 3 File(s) 8,699,904 bytes 0 Dir(s) 43,394,301,952 bytes free C:\oracle\product\10.1.0\oradata\uat>del *.ctl C:\oracle\product\10.1.0\oradata\uat>dir *.ctl Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\uat File Not Found C:\>rman Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys@uat connected to target database (not started) RMAN> startup Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 05/26/2008 14:18:02 ORA-00205: error in identifying controlfile, check alert log for more info RMAN> restore controlfile from 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT \BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP' 2> ; Starting restore at 26-MAY-08 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK channel ORA_DISK_1: restoring controlfile channel ORA_DISK_1: restore complete output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL01.CTL output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL02.CTL output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL03.CTL Finished restore at 26-MAY-08 RMAN>you can see the controlfile restored for the date of 16th May,2008 and datafile are for today dated 26th May ,2008 and one of datafile named newdatafile01.dbf has been added today dated 26th MAy,2008,above restored controlfile doesnt aware about new added data file newdatafile01.dbf which exist on disk.C:\oracle\product\10.1.0\oradata\uat>dir *.dbf Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\uat 05/26/2008 02:10 PM 104,865,792 NEWDATAFILE01.DBF 05/26/2008 02:10 PM 262,152,192 SYSAUX01.DBF 05/26/2008 02:10 PM 461,381,632 SYSTEM01.DBF 05/25/2008 09:42 AM 20,979,712 TEMP01.DBF 05/26/2008 02:10 PM 26,222,592 UNDOTBS01.DBF 05/26/2008 02:10 PM 5,251,072 USERS01.DBF 6 File(s) 880,852,992 bytes 0 Dir(s) 43,392,053,248 bytes free C:\oracle\product\10.1.0\oradata\uat> RMAN> startup mount database is already started database mounted RMAN> SQL> select * from v$recover_file 2 / no rows selectednow you have new old controlfile and new datafiles doRMAN>recover database RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/26/2008 14:43:26 ORA-01119: error in creating database file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT \NEWDATAFILE01.DBF' ORA-27038: created file already exists OSD-04010:option specified, file already exists SQL> recover database ORA-01153: an incompatible media recovery is active an incompatible media recovery is active cause newdatafile01.dbf does not known by old controlfile.SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. 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-00283: recovery session canceled due to errors ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005' SQL> conn sys/sys@uat as sysdba Connected. SQL> recover database ORA-01153: an incompatible media recovery is active SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. 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-00283: recovery session canceled due to errors ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'You can also check the name of datafile unnamed00005 from alert log fileMon May 26 14:43:23 2008 alter database recover logfile 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC' Media Recovery Log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC File #5 added to control file as 'UNNAMED00005'. Originally created as: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF' Errors with log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED ---- ------ ------------------------------------------------- ------- --------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF' 2 / Database altered. SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED --- ----- ------------------------------------------------------ ------ --------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITETry to recover the datafile at RMAN as well sqlplusC:\>rman Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys@uat connected to target database: UAT (DBID=2679299118) RMAN> recover datafile 5 2> ; Starting recover at 26-MAY-08 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/26/2008 15:12:30 RMAN-06067: RECOVER DATABASE required with a backup or created controlfile SQL> recover datafile 5 ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be doneAs you can see the recovery should be done by using recover database using backup controlfile.SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> select group#,status 2 from v$log 3 / GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 CURRENT SQL> recover database using backup controlfile ORA-00279: change 1011489 generated at 05/26/2008 12:51:02 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_%U_.ARC ORA-00280: change 1011489 for thread 1 is in sequence #71 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 1014306 generated at 05/26/2008 13:33:06 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_%U_.ARC ORA-00280: change 1014306 for thread 1 is in sequence #72 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC' no longer needed for this recovery Specify log: { =suggested | filename | AUTO | CANCEL} ORA-00279: change 1016418 generated at 05/26/2008 14:00:33 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_%U_.ARC ORA-00280: change 1016418 for thread 1 is in sequence #73 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_43NZ1NQ7_.ARC' no longer needed for this recovery Specify log: { =suggested | filename | AUTO | CANCEL} ORA-00279: change 1017543 generated at 05/26/2008 14:08:47 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_%U_.ARC ORA-00280: change 1017543 for thread 1 is in sequence #74 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_43NZJZG7_.ARC' no longer needed for this recovery Specify log: { =suggested | filename | AUTO | CANCEL} ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC ORA-00280: change 1017771 for thread 1 is in sequence #75 ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_43NZM4B6_.ARC' no longer needed for this recovery Specify log: { =suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Do recovery again at this time dont specify to apply the log at O1_MF_1_75_%U_.ARC ,only apply alls redo log file instead applying O1_MF_1_75_%U_.ARC.SQL> recover database using backup controlfile ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC ORA-00280: change 1017771 for thread 1 is in sequence #75 Specify log: {=suggested | filename | AUTO | CANCEL} C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.log<--------------------------- ORA-00310: archived log contains sequence 73; sequence 75 required ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.LOG' SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED ---- ----- -------------------------------------------------- ------- -------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE now use redo01.log fileSQL> recover database using backup controlfile ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC ORA-00280: change 1017771 for thread 1 is in sequence #75 Specify log: {=suggested | filename | AUTO | CANCEL} C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG<--------------------------- ORA-00310: archived log contains sequence 74; sequence 75 required ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG' SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED --- ------ ------------------------------------------------------- ------- -------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE now use redo02.log fileSQL> recover database using backup controlfile ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC ORA-00280: change 1017771 for thread 1 is in sequence #75 Specify log: {=suggested | filename | AUTO | CANCEL} C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO02.LOG<------------------ Log applied. Media recovery complete. SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED --- ----- ---------------------------------------------------- ------- ---------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE SQL> alter database open resetlogs 2 / Database altered. SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED ---- ----- ------------------------------------------------------- ------- --------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE SQL> alter database open resetlogs 2 / Database altered. SQL> select ts#,file#,substr(name,1,55) name,status,enabled 2 from v$datafile 3 / TS# FILE# NAME STATUS ENABLED --- ----- -------------------------------------------------- ------- ---------- 0 1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF SYSTEM READ WRITE 1 2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE READ WRITE 2 3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF ONLINE READ WRITE 4 4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF ONLINE READ WRITE 6 5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE READ WRITE SQL>
Friday, May 9, 2008
how it can improve the performance of db if i make open_cursor to 500 ?
> how it can improve the performance of db if i make open_cursor to 500 ? It will not increase the performance by increasing open_cursor value rather it shows how yours application can open the cursor within a session,there is no relation of open_cursor parameter to performance directly.Nevertheless If you enable the session cached_cursor then it will let you show the cached cursor which will remain throught yours session and will imporve performance. SQL> SHOW PARAMETER session_cached_cursors NAME TYPE VALUE ------------------------------------ -------------------------------- ----------------------------- session_cached_cursors integer 0 SQL> SELECT sql_text 2 FROM v$open_cursor 3 WHERE sql_text LIKE '%KHURRAM%' 4 / no rows selected SQL> DECLARE 2 v NUMBER:=7566; 3 CURSOR c1 IS SELECT * FROM emp KHURRAM; 4 c2 c1%ROWTYPE; 5 BEGIN 6 OPEN c1; 7 LOOP 8 FETCH c1 INTO c2; 9 EXIT WHEN c1%NOTFOUND; 10 END LOOP; 11 CLOSE c1; 12 END; 13 / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> SELECT sql_text 2 FROM v$open_cursor 3 WHERE sql_text LIKE '%KHURRAM%' 4 / no rows selected SQL> ALTER SESSION SET session_cached_cursors=100 2 / Session altered. SQL> SHOW PARAMETER session_cached_cursors NAME TYPE VALUE ------------------------------------ -------------------------------- ----------------------------- session_cached_cursors integer 100 SQL> SELECT sql_text 2 FROM v$open_cursor 3 WHERE sql_text LIKE '%KHURRAM%' 4 / no rows selected I am running the 3 times cause oracle checks library cache to determine whether more than 3 parse requests have been issued on a given statement.if so,then oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into session cursor cache. SQL> DECLARE 2 v NUMBER:=7566; 3 CURSOR c1 IS SELECT * FROM emp KHURRAM; 4 c2 c1%ROWTYPE; 5 BEGIN 6 OPEN c1; 7 LOOP 8 FETCH c1 INTO c2; 9 EXIT WHEN c1%NOTFOUND; 10 END LOOP; 11 CLOSE c1; 12 END; 13 / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> SELECT sql_text 2 FROM v$open_cursor 3 WHERE sql_text LIKE '%KHURRAM%' 4 / SQL_TEXT ------------------------------------------------------------ SELECT * FROM EMP KHURRAM SQL>
Subscribe to:
Posts (Atom)
