I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Friday, May 23, 2008

How it is easy to enable archivelog in 10g


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     TRUE

The 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 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 file
Wed 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.dbf
SQL> 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 os
SQL> 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 5
Delete the mydata.dbf by os.And try to recover database
SQL> 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 place
C:\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 activity
SQL> 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 CURRENT
Now delete or corrupt the controlfile ,i am deleting here alls controlfile here
SQL> 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 Found
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 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 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. 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 scenario
Answer :
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 here
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> 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 WRITE
make default this newtablespace for the user scott
SQL> 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 selected
now you have new old controlfile and new datafiles do
RMAN>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 file
Mon 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 WRITE
Try to recover the datafile at RMAN as well sqlplus
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: 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 done
As 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 file
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\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 file
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\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> 

Followers

About Me

My photo
Melbourne, Victoria, Australia