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.

Saturday, October 11, 2008

Log switch trigger for recovery at standby database

I have already posted blog how standby redo apply and from this thread i got a comment by soemone else from within that thread,i feel better i should post another blog in reply that comment rather replying for that comments on that thread. Comments are
Hi, So does that mean you have only partial records at STANDBY when you cancel reovery process by not allowing complete logs to be applied? you have setting of "log_archive_dest_2 string SERVICE=stby LGWR SYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)" that means it'll write directly to STANDBY DB online logs.So as soon as logswitch happens at Primary Db, it should happen on Standby DB (not necessory) as well.so every record should be applied to STANDBY DB.So why in your case Partial records are applied? just because you have canceld recovery process in middle?
Yes due to canceling recovery process at standby cause to apply partial records partial committed records those which were in standby redo log and applied at standby DB after log switched but those which were in standby redo log but not applied to standby database cause it was in redo log and didn’t applied due to not Log switches occurring). Redo logs applied to a physical standby database when a log switch occurs, they don’t Applied as they arrive on the standby site. As MRP Apply (MRP process) or SQL Apply (LSP process) applies the redo data to the Standby database using standby archived logs. See figure





Log switch trigger MRP for applying the redo data to the standby database using Archived standby log. If you just enter suffice amount of data which does not cause Log switch at primary database then this redo propagated at standby redoes log files But will not applied until or unless you or itself log switch happened at primary Database which in turn also switched standby log at standby database. Before going to demonstrate I would think better to answer yours question that yes It’s necessary a log switch at physical standby trigger to log switch at standby redo Log file. For that you can see demonstration as i am doing within my lap top. Remember in this demo real-time apply is not enabled, which data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process.

At standby

SQL> show user
USER is "SYS"

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> alter database recover managed standby database disconnect from session
  2  /

Database altered.

SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PHYSICAL STANDBY TO PRIMARY           MAXIMUM PERFORMANCE  YES

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1         31          0          0
RFS       UNKNOWN  ATTACHED              0          0          0          0

At primary

SQL> show user
USER is "SYS"

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CLOSING               1         30      16385       1481
ARCH      ARCH     CLOSING               1         30      16385       1481


SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PRIMARY          SESSIONS ACTIVE      MAXIMUM PERFORMANCE  YES


SQL> create table test (a number)
  2  /

Table created.

SQL> begin
  2    for i in 1..10000  loop
  3      insert into lalo values (i);
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         31       4097       1256
MRP0      N/A      WAIT_FOR_LOG          1         32          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         32       9770        100
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP still waiting (wait_for_log) for sequence 32 for recovery while RFS is
writing in progress via LGWR for 32 unless you dont make log switch at primary
database then MRP will not apply redo to standby database.


Come at Primary database and make log switch 

Come at primary database

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33          1          1
RFS       UNKNOWN  RECEIVING             0          0          0          0

See MRP now waiting (wait_for_log) for sequence 33 for recovery while RFS is writing in progress
via LGWR for sequence 33 unless you don’t make log switch at primary Database then MRP will not
apply redo to standby database.

Come at primary database

Lets see if i create that table that may cause at primary database to make log switch 

SQL> create table test_1 (a number)
  2  /

Table created.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33        265          2
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

No, no log switch occur at primary database still MRP waiting (wait_for_log) For sequence 33 for
recovery while RFS is writing in progress via LGWR for sequence 33, unless you don’t make log
switch implicitly/explicitly at primary database then MRP will not apply redo to standby database.

Let’s see if I cancel recovery and see table test_1 redo applied to physical standby Database

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
ERROR:
ORA-04043: object test_1 does not exist

Nop..

Put again it in recovery mode

SQL> alter database recover managed standby database disconnect from session
  2  /

Database altered.

Come at Primary database and make log switch 

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         33          1        455
MRP0      N/A      WAIT_FOR_LOG          1         34          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         34          7          3
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP now waiting (wait_for_log) for sequence 34 for recovery while RFS is writing in progress
via LGWR for sequence 34, sequence 33 has been applied it applied redo via MRP so fast which you
cannot see right now but you can observe if you make huge transaction at primary database and
monitor physical standby database you will see MRP0 with status APPLYING_LOG.

Cancel recovery and see test_1 table redo applied via MRP after making log switch

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

Tuesday, October 7, 2008

RMAN-06025: no backup of log thread 1 seq

RMAN> run
2> {
3> RESTORE VALIDATE CHECK LOGICAL  ARCHIVELOG ALL;
4> }

Starting restore at 08-OCT-08
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2008 11:40:16
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 31 lowscn 374582 found to restore
RMAN-06025: no backup of log thread 1 seq 30 lowscn 374398 found to restore
RMAN-06025: no backup of log thread 1 seq 29 lowscn 374337 found to restore
RMAN-06025: no backup of log thread 1 seq 28 lowscn 374159 found to restore
RMAN-06025: no backup of log thread 1 seq 27 lowscn 374073 found to restore
RMAN-06025: no backup of log thread 1 seq 26 lowscn 373814 found to restore
RMAN-06025: no backup of log thread 1 seq 25 lowscn 373756 found to restore
RMAN-06025: no backup of log thread 1 seq 24 lowscn 373318 found to restore
RMAN-06025: no backup of log thread 1 seq 23 lowscn 373184 found to restore
RMAN-06025: no backup of log thread 1 seq 22 lowscn 373004 found to restore
RMAN-06025: no backup of log thread 1 seq 21 lowscn 372946 found to restore
RMAN-06025: no backup of log thread 1 seq 20 lowscn 371604 found to restore
RMAN-06025: no backup of log thread 1 seq 19 lowscn 371598 found to restore
RMAN-06025: no backup of log thread 1 seq 18 lowscn 371198 found to restore
RMAN-06025: no backup of log thread 1 seq 17 lowscn 351033 found to restore
RMAN-06025: no backup of log thread 1 seq 16 lowscn 330912 found to restore
RMAN-06025: no backup of log thread 1 seq 15 lowscn 330901 found to restore
RMAN-06025: no backup of log thread 1 seq 14 lowscn 330754 found to restore
RMAN-06025: no backup of log thread 1 seq 13 lowscn 330749 found to restore
RMAN-06025: no backup of log thread 1 seq 12 lowscn 330449 found to restore
RMAN-06025: no backup of log thread 1 seq 11 lowscn 330444 found to restore
RMAN-06025: no backup of log thread 1 seq 10 lowscn 330119 found to restore
RMAN-06025: no backup of log thread 1 seq 9 lowscn 330114 found to restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 330052 found to restore
RMAN-06025: no backup of log thread 1 seq 7 lowscn 330046 found to restore
RMAN-06025: no backup of log thread 1 seq 6 lowscn 329222 found to restore

SQL> select sequence# from v$archived_log
  2  /


 SEQUENCE# S
---------- -
         6 D
         7 D
         8 D
         9 D
        10 D
        11 D
        12 D
        13 D
        14 D
        15 D
        16 D
        17 D
        18 D
        19 D
        20 D
        21 D
        22 D
        23 D
        24 D
        25 D
        26 D
        27 D
        28 D
        29 D
        30 D
        31 D
        32 A
        33 A
        34 A
        35 A

30 rows selected.
As you can see log sequence 6 to 31 either deleted by RMAN or deleted by OS and then crosschecked or these logs never backed up but deleted by OS and then crosschecked.Restore archivelog all will try to restore all archivelogs that are within repository (i.e controlfile). As you never backed up log sequence 6 to 31 but RMAN will try to restore those log sequence.
Workaround 1
RMAN> list archivelog all
2> ;


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
27      1    32      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_32_4GQB1ZSD_.ARC
28      1    33      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_33_4GQB51GN_.ARC
29      1    34      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_34_4GRCZ11G_.ARC
30      1    35      A 08-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_35_4GRM3Y0Z_.ARC


RMAN> run
2> {
3> RESTORE VALIDATE CHECK LOGICAL  ARCHIVELOG from sequence 32;
4> }

Starting restore at 08-OCT-08
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_32_4GQB1ZSD_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_33_4GQB51GN_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_34_4GRCZ11G_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_35_4GRM3Y0Z_.ARC
Finished restore at 08-OCT-08
Workaround 2
You can go with recvoery catalog for detail see metalink note 235973.1

Thursday, September 25, 2008

How standby redo apply

A log switch on the primary database triggers a log switch on the standby database, Causing ARCn processes on the standby database to archive the standby redo log Files To archive redo log files on the standby database. Then, Redo Apply (MRP process) Or SQL Apply (LSP process) applies the redo data to the standby database. If real-Time apply is enabled, Data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process. What you can conclude from above statement is that Recovery at standby database via MRP happen after log switch at standby, unless you don’t log switch you will not get Recovery (log applied data) at standby. When you explicitly log switch then MRP start its process to roll forward, roll back. Here I am going to demonstrate you that after inserting even number record I am committing the data, it’s a procedure which insert 1000000 records, during this insertion lot of log switch occurs after every log Switch occurring obliged MRP to apply redo at data file from its standby redo log Files. But the commit is also stamping to data in data file which you may validate from yours data file. What I will do I will cancel the managed recovery after 4 to 5 log switching before Completion of this procedure and will see what MRP awaked after 4 to 5 log switch And applied the standby redo logs to my data files with committed stamped. At standby I have enabled managed recovery as a background detached process.
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

No proces doing something cause at PROD (primary database) no any activity going on 
except MRP waiting for next log 924.

SQL> select process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1        924          0          0
RFS       RECEIVING             0          0          0          0



Come to Prod server...

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=stby LGWR SYNC VALID_FOR= 
                                                 (ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                 DB_UNIQUE_NAME=STBY


SQL> create table lalo (a number)
  2  /

Table created.


SQL> begin
  2   for i in 1..1000000
  3   loop
  4      insert into lalo values (i);
  5      if mod(i,2)=0 then
  6      commit;
  7      end if;
  8    end loop;
  9  end;
 10  .
SQL> /


At Standby see 

SQL> select process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        930      18433        622
MRP0      APPLYING_LOG          1        927      15132      19054
RFS       RECEIVING             0          0          0          0
RFS       WRITING               1        931      18699        356
RFS       RECEIVING             0          0          0          0

6 rows selected.

SQL>  RECOVER MANAGED STANDBY DATABASE CANCEL
Media recovery complete.

SQL> ALTER DATABASE OPEN
  2  /

Database altered.

SQL> CONN SCOTT/TIGER@STBY
Connected.
SQL> select count(*)
  2    from lalo
  3  /

  COUNT(*)
----------
    194162


Wednesday, July 9, 2008

How oracle makes media recovery

Oracle Instance shutdown with command 
SQL > Shutdown immediate

Oracle Instance shutdown with command 
SQL > SHUTDOWN ABORT


Let me know How Oracle SCN concept will work in above mentioned scenario ? 

Oracle first check controlfile checkpoint SCN number ,if checkpoint SCN in the 
datafile header earlier then controlfile checkpoint SCN then oracle need 
redo,recovery is overall the process of applying redo to data files.In more 
depth recovery will synchronize the checkpoint SCN in the data files header to 
checkpoint SCN in control file header Or in broader term we can say goal of 
recovery is to make sure the CKPT SCN in each datafile header matches the CKPT 
SCN in the control file.

How SCN Synronization will happen ? 

When you shutdown database with the immediate/normal/transactional clause then 
oralce trigger a checkpoint where oracle makes the control files and datafiles 
consistent to the same checpoint SCN during a database checkpoint.


How Oracle Database will understand what file Needs Restoration and Recovery ? 

For restoration database file oracle ,at startup oracle probe controlfile 
(metadata) of yours database ,if it does not find the database file physically 
where it knows then it needs restoration of that concerned database file.Lets 
say you take online backup,if yours database file which is not available then 
restoration comes into play,yours restored file is some time back then now 
yours existence others database file specifiaclly with control file,controlfile 
checkpoint SCN header will be ahead with restored lost database file,oracle 
decide requires recovery.

What the meaning of SCN Synronization,If Database synronize Control File SCN 
number with DBF File SCN then how it will work when we loose Control File?

Always restoration of controlfile require recovery why..

As i understand it does not apply the redo to data files but an appearance just 
like to apply redo which is not actual (you can also check this appearance by 
restoring controlfile and then make recovery at sql by recover database until 
cancel and then dont apply redo at first attempt and apply cancel),it does like 
that cause old control file which doesn't know where the redo chain ends ,it 
may comes into situation where redo log ahead to old backup control file this 
appearance of recovery make datafile and controlfile consistent by 
compeling/forcing open resetlogs.

OR 

restored control file which doesn't know where the redo chain ends ,it may 
comes into situation where redo log ahead to old backup control file, after 
restoring controlfile which does not know the current log sequence number of 
redo log ,the existing current redo log sequence number does not match to 
restored controlfile for current log sequence number ,the controlfile 
checkpoint scn # would be earlier to datafile checkpoint scn #,this appearance 
of recovery make datafile and controlfile consistent as well restored 
controlfile adopt the new resetting log sequence number by compeling/forcing 
open resetlogs.
-------------------------------------------------------------------------------

I can’t understand yet why online backup is inconsistent? I know only a restored Backup which has
data files SCN header (i.e. CKPT SCN) does not match to control files SCN header (i.e. CKPT SCN)
considered to be inconsistent backup?

You are right but more or less a backup in which some of the files in the backup contain changes
that were made after the files were check pointed. This type of backup needs recovery before it
can be made consistent.

Inconsistent backups are usually created by taking online database backups; that is the database
is open while the files are being backed up.

RMAN backup at blocks level ,whenever backup start a checkpoint also triggered  say in time "x"
at the same time "x" RMAN started to take blocks backup e.g. b1,b2,b3.

Say block b1 SCN would be 100 and CKPT SCN at the time "x" of backup started is 500, there is no
guarantee that block b1 SCN would be same throught the backup completion. It may be change and at
every change block b1 SCN will be incremented, and at some time block b1 SCN may go
beyond/greater then CKPT SCN 500, if that’s the case then  yours block b1 backup would be
inconsistent.


Here see a demo

RMAN> backup database
2> ;


Meanwhile backup is going on the other hand i am doing some actitivity

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 16 14:56:53 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options



SQL> create table v as select * from all_objects
  2  /

Table created.

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> create table v3 as select * from all_objects
  2  /

Table created.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete alls datafiles here except redo log and control files after shutting down database..

After that start up 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-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SDS\SYSTEM01.DBF'

What i will do i will restore database files from inconsistent/online/hot backup.


C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@sds

connected to target database: SDS (DBID=2656018210)

RMAN> restore database
2> ;

Starting restore at 16-JUL-08
.
.
.
Finished restore at 16-JUL-08


After restore check yours inconsistent backup would have fuzzy block

SQL> select checkpoint_change#,absolute_fuzzy_change#,recovery_fuzzy_change#
  2    from v$datafile_copy
  3  /


CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE#
------------------ ---------------------- ----------------------
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0

CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE#
------------------ ---------------------- ----------------------
            684218                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                 816527               0
16 rows selected.

SQL> 

If yours any block during backup get higher SCN then checkpoint SCN it would be considered fuzzy
block which make yours whole database backup inconsistent, oracle always requires recovery (redo)
in this situation to make inconsistent restored backup to consistent.

As far as concern about yours question that you didn’t do any activity so there would not be any
absolute fuzzy change number (block SCN > CKTP SCN), you are right but Oracle will always require
recovery

Wednesday, July 2, 2008

FRA Capacity Planning

Kindly suggest some tips and how to determine my FRA?

First oracle recommended tip is to always keep yours FRA far away from yours Database location
possibly keep it at hard another hard drive, if possible keep this hard drive with separate
controller.

For FRA sizing decide how many days database backup you could maintain within Yours hard disk,
this could be determined by yours SLA ,usually there is maintained two days backup within hard
disk either it is FRA or non FRA,the rest of database backup as well current two days backup
should be transferred to tape cartridge. Typically what I do within my environment could be
helpful for you.

I keep at least last two current days backup for the entire database at hard hard disk within
FRA,beyond 2 days and these last two current days backup I move to tape cartridge, according to
business rule I maintain 30 tape cartridge for 30 days backup to revert back in case of any harm,
I rotate the same cartridge which lapse after 30 days.

i.e JAN tape1..tape30
    FEB tape1..tape30
    MAR tape1..tape30

You can dedicate yours FRA space by cheking yours database file size not with actual data by 

select sum(bytes) from v$datafile
/

Lets say if yours database file size about 80GB then you can either dedicate
2*80+20% extra for yours FRA.I maintain my retention policy for redundancy 2
days.After taking 3rd backup my first backup obsolete and before that any
archivelog also obsolete and can be reclaimable by deleting obsolete via RMAN.

For archivelogs pace you can observe 

select sum(blocks*block_size) archivebytes 
  from v$archived_log
where completion_time > sysdate-1 and status='A'

If you maintain yours archivelog within FRA then you also need to accomodate space for archivelog
within FRA,its recommended to put alls backup stuff within one single place which provide you FRA.

If you decide to put yours archivelog within FRA then yours estimation would 
become (2*80+archivebyte) x+20% x.

The above clue will determine you what should be the size of FRA.


Why last two current day’s backup should be at hard disk (FRA flash recovery Area) why not last
one current backup or why not last three current backup or why not last x backup or why is keep
backup at hard disk at all when I move it to tape cartridge, why to waste space when company
gives me tape cartridge space for each day backup?

the reason is that if you don’t keep any backup at hard disk then it will increase yours downtime
in case of hard disk failure or any failure which should not be for a good DBA,you will have to
have move last current backup from tape cartridge to hard disk (i.e. FRA) which will take x
hours/mins to be restored at hard disk (i.e.FRA), keeping at least one last current backup at
hard disk will save yours time from tape to hard disk. But if you keep 2 last current backup then
it would be safer in case of any mishap with last current backup, you will be able to restore
prior backup to last current one backup and then apply two days log for example

If you keep two last current backup at hard disk (FRA)

JAN 1
JAN 2
JAN 3<----------hard disk fail

You will restore Jan 2 backup but unfortunately yours 2nd Jan backup corrupted for any reason
then you can go with 1st Jan backup, you will restore 1st Jan backup apply archive log till
before the hard disk failure.


If you don’t keep last two backup just keep only last current backup ,in any case yours last
current backup corrupted then again you will have to have move last current backup from tape
cartridge to hard disk (i.e. FRA) which will take x hours/mins to be restored at hard disk (i.e.
FRA),if you came across that tape cartridge last current moved backup also not restorable from
tape cartridge then you will go with previous current backup and apply archivelogs till before
hard disk failure, think isn’t good if you save x hours/min to move backup from tape to hard disk
to make yourself and yours company downtime impact for the cost of purchasing more space for
backup.

Nevertheless if you can accommodate last 3 backups or more then that then I don’t think so there
is any harm but don’t rely at all at yours hard disk backup, move backup also to reliable tape
cartridge media.


Note : Above clue is not for flashback database technology.

Monday, June 16, 2008

ORA-12705: Cannot access NLS data files or invalid environment specified / ORA-01092: ORACLE instance terminated. Disconnection forced


[oraprod@dbprod ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 19:27:00 2008
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

[oraprod@dbprod 10.2.0]$ unset NLS_LANG
[oraprod@dbprod 10.2.0]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 16:05:42 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1264892 bytes
Variable Size             654312196 bytes
Database Buffers          406847488 bytes
Redo Buffers               11317248 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

Check yours alert log file Errors in file /orahome/PROD/db/tech_st/10.2.0/admin/PROD_dbprod/udump/prod_ora_27138.trc: ORA-12701: CREATE DATABASE character set is not known Mon Jun 16 14:38:01 2008 Error 12701 happened during db open, shutting down database USER: terminating instance due to error 12701 Instance terminated by USER, pid = 27138 ORA-1092 signalled during: ALTER DATABASE OPEN... Mon Jun 16 14:48:21 2008 The above error comes after running autoconfig on database side. Cause ----- One of parameter NLS_LANG or ORA_NLS10 is not set correctly. Solution -------- check ora_nls10 path
[oraprod@dbprod udump]$ echo $ORA_NLS10 /orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data
Path should be (10g rel 2) ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata" it would more appropriate to set the path in environment variable file.
[oraprod@dbprod 10.2.0]$ vi PROD_dbprod.env #ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data" ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata"
Save and then open the terminal again.
or you can change at shell [oraprod@dbprod 10.2.0]$ echo $ORA_NLS10 /orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data [oraprod@dbprod 10.2.0]$ ORA_NLS10=/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata [oraprod@dbprod 10.2.0]$ echo $ORA_NLS10 /orahome/PROD/db/tech_st/10.2.0/nls/data/9idata [oraprod@dbprod 10.2.0]$ sqlplus sys/sys as sysdba SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 16:10:46 2008 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1264892 bytes Variable Size 654312196 bytes Database Buffers 406847488 bytes Redo Buffers 11317248 bytes Database mounted. Database opened.

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>

Followers

About Me

My photo
Melbourne, Victoria, Australia