Yes, I know that u hate cold backup. But Can we run the Database in Archivelog mode and instead of hot backup I take
Cold backup everyday. Incase of recovery can we restore last
cold backup and apply the archived redo log to restore the DB witout losing any data.

If you took a previous cold backupset and tried to apply archived logs, wouldn't you need to keep the current control file? I thought the SCN in the control would have to be the most recent to be able to apply logs. Otherwise, wouldn't it think it was consistent?

We have done cold backup database,working in
NoArchiveLog Mode (after shutdown immediate) without redo log files and decided to put the database in archivelog mode on test server.
The steps were:
Test server is fully identifical the production server.
Change init.ora (add parameters for archivelog mode)
startup mount
alter database archivelog
alter database open resetlogs
And received error:
ORA-01139 RESETLOGS option only valid after incomplete recovery.
What is wrong?
Thanks, Nadya

Sorry,sorry,sorry...
We have missed statement
recover database until CANCEL
And now we have all O'KEY.

vvvvvvvvvvvvvvvvery nifty

Do i need to take hot backups of the following tablespaces:

1.Rollback
2.Temp

I currently don't as advised by a more senior 'DBA' but another DBA told me that i should backup the Rollback tbs because some instances requires linkage to the RBS data.

Last night when I was in the process of posting my question. I clicked on the Send button and was told that you were no longer receiving questions.
The questions I had meant to ask are as follows:

Hi Tom,

First, I am working with Oracle Flashback on a test database. I issued the dbms_flashback.get_system_change_number to get the current SCN. I then performed one update statement, waited a couple of minutes and executed dbms_flashback.get_system_change_number again. The SCN increased by approximately 200 integers. In previous questions you have stated that a SCN increments when a transaction completes. Has something changed in 9i? What specific processes could be causing the SCN to increment this way?

Second, I know that in a 9i database it is best to use automatic undo_management. However, how does one move back to using manual RBS's. I tried setting undo_management=manual and alter system set undo_tablespace=''; However, when I try to create a rollback segment I am told that I cannot use the System Tablespace to create a non-system segment?

Is there an easy way to get archive logs moved out of OEM?

Thank you in advance for your help.

Mike

Can u please tell about what is archieve log mode and
nonarchieve log mode and please differentiate them.

hello sir,

Normally you recommend that we should use current controle file, if we don't have then we can use old controle file and if we have not even old controle file then we have to make new controle file. But sir i think that in some scenario we should create create new controle file, may be i am wrong.

1.
Suppose i shut down my database normally and accidendly file is deleted and the controle file which i having is 15 days old. Then if i want to recover my database using this file then i have to apply 15 day's archive logs and have to open database with "Resetlogs". and i create new controle file (Because my DB is normally shutdown) then i don't need to apply archive logs and i can open my database without "Resetlogs"

Sir, even in this scenario , is it better to use old controle file, if yes then why ?

Sir, actually i have no clearity about it so please tell me that is there only drawback of using new controle file that if DBA creates a new controle file and he missed any data file and opened the database with "Resetlogs" then data will be lost.

2.
In this Scenario, suppose i have only one tablespace SYSTEM no undo or no temp ... tablespace


T1 T2 T3 T4

| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
-----------------------------------------------------
Cold Bckp bakp control Hot backup Lost System
file binray system datafile and
Control file datafile all controlfiles

T1 --> Cold Bckp

T2 --> backup control file binary

T3 --> hot backup system datafile ..

T4 --> Lost system datafile and all controlfiles

i have all archivelogs and redo logs till T4.

only have t2 controlfile ..

sir in this scnario how can i recover my database ..
[should i use_of_new_controlfile= Applicable/Not Applicable ]


Regards ...

Thanx Sir for ur reply ...

I have already gone through this link...
but i think second scenario [2] is not there .. and
Still i have some doubts..

sir .. suppose i have two redo group [ 1 mb each ] and at time "A" i had taken hot backup ... this time current sequence is 50 .. and scn 50000 . after that i issued a transaction due to which oracle switch log file 10 time mean current sequence = 60 and curr scn = 60000 .. sir while doing incompelete recovery can i issue
"Alter database until cance" and cancel recovery at any archive log between 50 and 60 .. log ...
(.. i tried this ) .. i was unable to open my database..
error --> "need more recover " sir its mean i can't open database between scn 50000 and 60000 or log 50 to 60.
then sir what is use of "until cance " clause it might be possible database will be consist at any log log switch...
and sir if i m right mean oracle can open database at any consist scn no... mean after and before any transaction .. sir if this is true ... then how can open my database if i don't know any consist scn .


2. and sir at time a hot backup was taken .. and time b controlfile was backedup .. and at time c harddisk crashed and all controlfiles and datafiles were damaged .
i have all archive log file just begore time C.

Sir can i do incompelete recover using b time controlfile
and a time datafiles ... .. mean consist time where database can open between
time "A" and "B" --> [incompelete Recovery ]

or can i do imcompelete recovery between time "B" and "C" [ just before C] ..
sir i tried this ..

i was unable to open database between "a" and "B" .. whenever i tried to open database [ using "until cancel " ] . .oracle says .. "File 1 need more recovery "

and if i tried to go between time "B" and "C" oracle tried to apply log files .. ...

sir pls help me ..

Thax for ur patience ..

In earlier versions, if I used backup controlfile to recover and if that backup controlfile indicates the status of a data file is READ ONLY, i had to take the file offline before recovery and PUT THE READONLY tablespace online after startup. Otherwise, oracle would not let me proceed with the recovery without taking the read only datafile offline. However, when I tried with version 9.2.0, it is not case. It did let me perform the recovery. Is the behavior of oracle changed with respect to read only files in 9.2.0. Please see below. Thanks for your time.


SQL> create tablespace readonly
  2  datafile 'C:\oracle\oradata\practice\readonly01.dbf' size 2M
  3  uniform size 128k;

Tablespace created.

SQL> alter tablespace readonly read only;

Tablespace altered.

SQL> connect /as sysdba;
Connected.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

SQL> col name for a55
SQL> select name,status,enabled from v$datafile;

NAME                                                    STATUS  ENABLED
------------------------------------------------------- ------- ----------
C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF                 SYSTEM  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF                   ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF               ONLINE  READ ONLY

11 rows selected.

SQL> create table t (x number)
  2  tablespace users;

Table created.

SQL> insert into t values(200);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\oradata\archivepractice\
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> shutdown abort
ORACLE instance shut down.

NOTE: I HAVE REPLACED THE CURRENT CONTROL FILES WITH BACKUP ONES.

SQL> startup
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.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile

SQL> select name,open_mode from v$database;

NAME                                                    OPEN_MODE
------------------------------------------------------- ----------
PRACTICE                                                MOUNTED

SQL> col error for a10
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
         1 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         2 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         3 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         4 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------

         5 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         6 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         7 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

         8 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ---------
                           ERROR

         9 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

        10 ONLINE  ONLINE  UNKNOWN        203235 05-JUN-03
                           ERROR

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\ORADATA\PRACTICE\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


SQL> recover database using backup controlfile; <---- IT DID NOT SAY ora 01233 THE FILE IS READ ONLY .....
ORA-00279: change 202309 generated at 06/05/2003 15:11:47 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_3.ARC
ORA-00280: change 202309 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 203233 generated at 06/05/2003 15:37:53 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_4.ARC
ORA-00280: change 203233 for thread 1 is in sequence #4
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_3.ARC' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 203235 generated at 06/05/2003 15:37:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_5.ARC
ORA-00280: change 203235 for thread 1 is in sequence #5
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_4.ARC' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_5.ARC' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.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.


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1          2  104857600          1 YES INACTIVE
       202140 05-JUN-03

         2          1          3  104857600          1 NO  CURRENT
       202142 05-JUN-03

         3          1          1  104857600          1 YES INACTIVE
       190578 05-JUN-03


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO03.LOG

         2         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO02.LOG

         1         ONLINE
C:\ORACLE\ORADATA\PRACTICE\REDO01.LOG


SQL> recover database using backup controlfile;
ORA-00279: change 203323 generated at 06/05/2003 15:38:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\ARCHIVEPRACTICE\ARCH_6.ARC
ORA-00280: change 203323 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

C:\ORACLE\ORADATA\PRACTICE\REDO02.LOG <---------- CURRENT REDOLOG FILE PATH

Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select * from t;

         X
----------
       200

SQL> col name for a55
SQL> select name,status,enabled from v$datafile;

NAME                                                    STATUS  ENABLED
------------------------------------------------------- ------- ----------
C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF                 SYSTEM  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\UNDOTBS01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\CWMLITE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\DRSYS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\EXAMPLE01.DBF                ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF                   ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\ODM01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\TOOLS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\USERS01.DBF                  ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\XDB01.DBF                    ONLINE  READ WRITE
C:\ORACLE\ORADATA\PRACTICE\READONLY01.DBF               ONLINE  READ ONLY


 

I have coldbackup and all archive log files after the coldbackup. My server is crashed, all my data is lost. I want to do complete recovery using this set of backup on other machine or atleast till last available archive log file.Is it possible? Is any document available for this?

I have created a copy of a db from Prod onto a Dev system, by simply tarring it up, transferring over the cold backup, untarring it, and starting up the db.

I need to do the same with a hot backup now. The archives are not really necessary....I just need a copy of the db, as of the last hot backup.

How would the steps differ?

Make hot backup (inc. datafiles, control files, logfiles).
Transfer all files to new system.
Restore hot backup. (Untar the files in the correct directory structure?)
Since archive logs are not needed there is no need to RECOVER UNTIL CANCEL or similar. Or just a RECOVER DATABASE command?
Would I need to open resetlogs?
And the Control file?

Are the RMAN backup sets that are no longer part of the catalog of any use? Assuming the knowledge of these backups was no longer in the current control file, and the catalog was not used or not available, is their any recoverability from old rman backup sets? I ask because I believe the answer is no, unless you had the old control file that was aware of them or had autocontrolfile backup turned on when those backup sets were taken. There is no other way to make a catalog or control file aware of the presence and contents of backup sets, right?

Respected Tom!

I got somewhat confused after reading this page.

I have a database which remains up for the whole day and I do it shutdown at 5' o clock evening and start it again at 8' o clock morning. This Oracle 9.2.0.1.0 database is running in archivelog mode on a Windows 2000 Server. I want to move this database from this machine say A to another machine say B with same disk/drive configuration. Which of the options will be suitable for me...

************
* OPTION I *
************

1) shutdown normal
2) create a database using Database Configuration Assistant onto machine B with the same database name on the same drive (name).
3) shutdown newly created database onto machine B
4) copy all control files from machine A to machine B.
5) copy all data files from machine A to machine B.
6) copy parameter file from machine A to machine B.
7) start the database on machine B.

*************
* OPTION II *
*************

1) shutdown normal
2) create a database using Database Configuration Assistant onto machine B with the same database name on the same drive name.
3) shutdown newly created database onto machine B
4) copy all control files from machine A to machine B.
5) copy all data files from machine A to machine B.
6) copy all redolog files from machine A to machine B.
7) copy parameter file from machine A to machine B.
8) start the database on machine B.

Yes, step # 6 (of Option II) is confusing me either should I restore the online redo logs from the old machine to the new one -or- not. Will there be need of any command at the end like recover database -or- just startup will be enough on machine B.

Thanks,
Prince

Thanks for your tireless efforts on this site, they are greatly appreciated.

As a close follow up to the subject of this thread, a question was posed to the DBA Forum on Metalink (Note: 501376.995) as follows:

========================

Cold vs Hot backups when doing database upgrades

We are planning to upgrade one of our larger databases from Oracle 8.1.7 to 9.2.0. Over time the window available for cold backups before and after our database upgrade initiatives has narrowed significantly and is causing real scheduling problems. Running hot backups before and after an upgrade would greatly reduce this outage and bring us back to an acceptable down time. What considerations, if any, go along with taking this position? Is there a better direction?

We are currently using RMAN to do hot backups daily on most databases and are comfortable with the processes. Thanks in advance for any input.

=========================

The response was the company line of 'Development and the documentation say to do a cold backup'

I wanted to get your feelings on the pros and cons of doing a hot backup (6 hours), a shutdown and copy archive files, and then the migration.

What pitfalls are we overlooking if any?

Thanks very much for your feedback.

Tom,

Is there a way to convert a binary control file
to text control file. I remember vaguely to have
read something like that in this site, regarding
a tricky recovery scenario.

Thanks

When a database is in UNMOUNTED state
is there a way to create a text control file
from a earlier binary control file available

This is the link I was referring to

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601495804013, <code>

I just want to set up backup and recovery screnarios
for production database and write the best practices
in backing up of databases

I remembered reading that backup of text copy of the
control file is a good practice and hence I asked the
question

Thanks very much for your time

Dear Tom,

I have personal Oracle installed on my machine. While starting oracle it is giving the following erro
ORA-00322 Log 1 of thread 1 is not the current copy
ORA-00312 online log 1 thread 1 : 'd:\orawin95\database\log2orcl.ora'

Can you please tell me the reason for this error and how to recover the database.

Dear Tom,

I have personal Oracle installed on my machine. While starting oracle it is
giving the following erro
ORA-00322 Log 1 of thread 1 is not the current copy
ORA-00312 online log 1 thread 1 : 'd:\orawin95\database\log2orcl.ora'

Can you please tell me the reason for this error and how to recover the
database.


Followup:
[tkyte@localhost tkyte]$ oerr ora 322
00322, 00000, "log %s of thread %s is not current copy"
// *Cause: Check of log file header at database open found that an online
// log appears to be an incorrectly restored backup.
// *Action: Restore correct file or reset logs.


Please tell me the steps to be followed to reset the logs. I will be thankful to you.

To restore a databaes from a hotbackup, we usually restore the datafiles and recreate the controfile from the (control file to) trace file.

When we recover the database and apply the archives, we need to issue the command 'recover database using backup controlfile'

The controlfile is newly created here with the create controlfile command using the headers of the datafiles. The SCN's in the controlfile should therefore be the same as that in the datafile headers. What is this backup control file and where does it come from ?

Please shed some light.

Noel.

While saying 'Usually restore the datafiles and recreate the control file' I meant during restoration drills for checking validity of backups. To be in a situation where my entire machine data is lost is not something I desire especially if am not confident of my backups.

So, I understand that there is no 'Backup' controlfile other than the manually backed up binary/text controlfile and the term is used to indicate that the restored controlfile is out of sync with the database thats all. Ofcourse, it needs a manual recovery to apply archives and a resetlogs (incase of incomplete recovery).

Regards
noel.

I have been brought to site for assisting the db recovery.
We have 9.0.1 database in archive log mode.

We lost all the data files .
The backup took 3 weeks ago , and to two locations: locationA contains one datafile SYSTEM01 and locationB has all the rest files. I looked the time stamps of the files, and SYSTEM01 was a day newer than the rest of files. These datafiles are only set of we have.
All the archived logs are intact since the backup on locationB before crash.

How/can we recovery the database?
Thanks for help.

What would be the ideal backup strategy for multi-terabyte warehouse databases? I was thinking backing up these databases using cp command or RMAN may not be practical. I wanted to have your thoughts on this as you may have information about real world datawarehouse database backup strategy. Thanks.

We have SAN Storage in the backend and luckily our SW supports flashcopy
and we are also able to use the results cloning test databases

Tom,

Can you please explain me or provide some link on how to use RMAN in SAN environment and how does RMAN fit in SAN architecture.

Thanks.

<quote>it is just a database on disk<quote>

Could you please clarify on this? Thanks.

Tom,
Quote from 8i Document
</code> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#922 <code>
"
After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary since no changes can be made to it.
"


In so called READONLY tablespace, dropping objects is still allowed. This may have some problems during recovery. If I restore the readonly TBS which contains dropped objects, ALL the archived logs since latest backup of the readonly tablespace should be kept in order to replay these DROPs.

Imagine I backuped a READONLY TBS 1 yr ago, and backuped other READWRITE TBSs a week ago, and there were some DROPs happening between 1 year ago and 1 week ago. I have to keep ALL archived logs for 1 yr instead of 1 week, this sounds so badly. The benefit of backing up READONLY TBS once costs keeping much MORE archived logs. If every time some objects are dropped, a new backup of the READONLY tablespace should be taken, then it's not so practical for DBAs since we have no control whether end users will drop objects in the READONLY tablespace or not, nor do DBAs know when users do it in order to take a backup. Besides, backing up READONLY TBS once isnt true anymore.

Could you explain whether I am wrong or not? Why doesnt ORACLE make it a TRUE READ ONLY tablespace?

Hi Tom,

So much talk about different backup methods and strategies and that is all good.

However, I can't find anything about backup strategies for the RMAN catalog.

What are your recommendations for backing up the RMAN catalog, either it is residing on the same machine as the production database or is residing on a box on its own. Would you do the same level of backup for you production database eg Level 0, 1, 2 etc.

Thanks.

Regards
Hien

<<
the "so called" readonly tablespaces ARE read only (they could be on cdrom), you
can still 'drop' objects from there (we don't write to the tablespace) since the
drop affects only the data dictionary!
>>

so DROP operation is different than normal tablespace for the READONLY tablespace, for the normal tablespace, at least I can see the segments are free after dropping them and if they are locally managed, there got to be something written to the tablespace instead of dictionary to mark these space are free to use. RIGHT?

 
ops$tkyte@ORA9IR2> create tablespace foo datafile size 1m;
Tablespace created.
 
ops$tkyte@ORA9IR2> create table xxx ( x int ) tablespace foo;
Table created.
 
ops$tkyte@ORA9IR2> alter tablespace foo offline;
Tablespace altered.
 
ops$tkyte@ORA9IR2> drop table xxx;
Table dropped.
 

No, it is not any different at all.  It just doesn't update the freespace until it can and with readonly, it cannot. 

Hi Tom,

Is this true that if we carry out a backup of control file at peak hour or peak load on database, the performance is affected, if it is so please explain , if not still then please -:)


yes, tom things are improving at my work place, now we also are allowed to access internet for browsing oracle sites..

hi tom

earlier in this link you recommended backup of undo tablespace while taking hot backup whats the rael use of it and in what scenario it can be needed.
thanks

and also we need to read only tablespaces once.
but in which scenario undotablespaces come into play in recovery.

Hi Tom,
Good day to you.
Thanks for your excellent answers.
I have a few doubts.
1) Let us assume there are 4 unix boxes each containg one or more databases of 50GB size. When a user drops a table, we talk of doing an incomplete recovery in the test database and then export and import the dropped table. If the database has 10 datafiles and the average size of a datafile is 4GB, then what is the minimum space requirement in the test unix box so that recovery of any database is possible. Please explain the steps.

2) What is the role of transportable tablespace in backup and recovery

3) HOw TSPITR is effective when there are dependent objects in other tablespaces.

Thanks in advance
Natarajan

Dear TOM,

I have a hard Disk with Oracle Installed in it. Operating system is corrupted. Oracle was installed as a usual general purpose database. No specific backup strategy was defined before. There was few schemas and tablespaces created by us.

How can I recover the data from the hard disk? I have all the Datafiles, Control File and Parameter files in the Hard Disk. The machine was shut down properly (I mean Windows o/s). Pls tell me a strategy to recover the data. Or pls guide me to some links. Pls...

Thanks for your time.

Tom,
Thanks for your really good explanations. I have an administrator which proposes following backup/recovery method:
1. Once only transfer cold backup (120Gb) from production box A to disaster recovery box B (located off site).
2. Transfer archived logs from A to B on a daily basis (~4Gb) via dedicated DSL link between sites.
3. Once a week or couple of weeks perform recover until cancel on B, other time B stays closed.

What is your opinion on this disaster recovery strategy?

Thanks.

Tom, thanks for your prompt comment. To my understanding the use of DataGuard is implying purchase of two copies of Oracle EE (one for production and another for disaster recover box), which is not affordable by the company (according to management). The plan was to purchase one copy of Oracle EE for production box and than also install but do not run software on disaster recovery box, bringing it up once a week to apply new archived logs. Bearing this in mind I would like to re-ask my question if the strategy I described in previous post is the rigth way to go.

Thanks.

Thank you, Tom, for the response. Could you please clarify for me, that with suggested by you solution (usage of DataGuard), does company need to purchase Enterprise Edition for DR box or it can be SE?
If EE is required is there some strategy which will allow to have EE on production and SE on DR box?
It is important to have EE on production for utilizing of parallel query option, but DR is somewhat what should provide short term solution in case of disaster.

Thanks a lot for your help.

Hi Tom

I am working on Oracle9i on Sun5.9. Database is in no archivelog mode.
Yesterday I had deleted my control file accidentally and i don't have the backup.
Now my database is up and running without any problem but when ever it will be shutdown, i think it will not be up.

I tried to create the backup control file but it gave me error--

SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/oracle9i/orabin/dbs/cntrlorcl.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Now what should i do to overcum this problem?
Please help me out this is very crucial for me.

Thanks. 

Hi

I have only one controlfile because at the time of database creation (manual database creation), I have not specify the CONTROL_FILES parameter in initialization parameter file and it created only one control file in DBS directory.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
control_file_record_keep_time        integer     7
control_files                        string      ?/dbs/cntrl@.dbf

Regards
 

Hi Tom,

I plan to apply patch 9.2.0.5 to my 9.2.0.1 database and I will do that in my production and test environment. My question is, can I restore 9201 datafiles to 9205 database? Because sometime, for some reasons, users ask me to restore old data to the testing environment. Fyi, I do cold backup everyday to my production database.

Thanks a lot.

Hi Tom

I have checked many times but no control file exists in dbs directory and database is running fine.

what should i do now?

Regards

Hi

I have created a new control file and have restarted the db and db is running fine.
But in my database i was not able to query the V$ views because of no controlfile so i took the information from OS directly that how many datafiles and redo logs are there and what are the size of redo logs etc.

Thanks

Regarding the person whose controlfile was lost but the database still running...

On a unix file system it is possible to remove a file while processes have it open. If you have a process running with a file open, the file system leaves the file there but removes it from the directory.

An ls would indicate that the file was not there, but the file space the file uses remains in the file system and the file remains visible to the application until the file is closed.

How to install or configiure Rman . Its free? When i run Rman like below is there but some of lib files not there , my netbackup people need those libararies , please help me.

Thanks

Hi Tom,

Oracle backup&recovery docs. says "You can obtain SCN's in a number of ways, for example, from the alert log. You can then use these for purposes of recovery."

But I checked my alert log and find no such information:-

Wed Nov 10 03:29:19 2004
ARC0: Beginning to archive log# 4 seq# 2246
ARC0: Failed to archive log# 4 seq# 2246
ARC0: Beginning to archive log# 2 seq# 2250
Wed Nov 10 03:29:19 2004
ARC2: Beginning to archive log# 4 seq# 2246
ARC2: Failed to archive log# 4 seq# 2246
ARC2: Beginning to archive log# 2 seq# 2250
ARC2: Failed to archive log# 2 seq# 2250
Wed Nov 10 03:29:19 2004
ARC0: Completed archiving log# 2 seq# 2250
ARC0: STARTING ARCH PROCESSES
ARC3 started with pid=27
Wed Nov 10 03:29:20 2004
ARC0: STARTING ARCH PROCESSES COMPLETE
Wed Nov 10 03:29:20 2004
ARC3: Archival started
Wed Nov 10 03:29:22 2004
ARC0: Beginning to archive log# 4 seq# 2246
ARC0: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:23 2004
ARC3: Beginning to archive log# 4 seq# 2246
ARC3: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Thread 1 advanced to log sequence 2252
Wed Nov 10 03:29:25 2004
ARC2: Beginning to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Current log# 1 seq# 2252 mem# 0: E:\ORACLE\ORADATA\VANP\REDO01.LOG
Wed Nov 10 03:29:25 2004
ARC2: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:25 2004
Current log# 1 seq# 2252 mem# 1: E:\ORACLE\ORADATA\VANP\REDO05.LOG
Wed Nov 10 03:29:25 2004
ARC2: Beginning to archive log# 3 seq# 2251
ARC2: Completed archiving log# 3 seq# 2251
Wed Nov 10 03:29:25 2004
ARC3: Beginning to archive log# 4 seq# 2246
ARC3: Failed to archive log# 4 seq# 2246
Wed Nov 10 03:29:26 2004
ARC1: Completed archiving log# 4 seq# 2246


Is it some setting or parameter I have to set to start getting SCN information ?
Also where else can I get this?

Thanks in advance,

Regards,
RD.

P.S- even if there are other ways of obtaining the information, can you please guide as to get it through the
alert log.

reading this thread i thought it have to be possible to recreate a controlfile ("create controlfile...") using this as a backup controlfile and do an (in-)complete recovery in following. this will be a simple "recovering using a backup controlfile" situation.

question using a backup controlfile:
if some of the files (datafile + online redo's) are lost the latest scn in any of the file headers will be used as the scn to recover to.

if all of the files are gone, rman will simply take the latest backup and apply all archive logs he can find.

right?

Hi Tom,

I have just started my career as Oracle DBA I want to know the difference between these statement as well as when to use these statement i.e. under which which circumstances of recovery please explain with examples.

1. alter database recover using backup controlfile until cancel;
2. recover database using backup controlfile until cancel;
3. recover database;

Thanking You
Mayank Sood

referencing this question/answer:

...
if some of the files (datafile + online redo's) are lost the latest scn in any of the file headers will be used as the scn to recover to.
.....

not using a cancel based recovery, no, you'll have to get at least that far, but can go further.



when going further rman will take the scn out of the recovery catalog, right? otherwise he would not be able to go further. would he?

Dear Tom,

I wonder if I have rman backup sets on tape and both production server and catalog database server are unavailable, is any way to restore database from these rman backups?

Thanks.

Dear Tom,

We did not use any librarian software, rman backed up to nfs share and then data from the disk was trasferred to tape.
Could you please explain concept of restoring database after full system loss and recovery catalog been unavailable as well?

Thanks.

Tom,

As above you said that:

"If the readonly tablespace is full of dropped objects -- thats OK -- the data is "just there" but we "don't know about it""

"it is a "true" read only tablespace. we read it, we don't write it and the data
dictionary tells us how to read it. "

[1] What does this mean that "data dictionary tells us how to read it." Does this means that when we bring this tablespace online, it drops the objects from the tablespace as well or in other words will it applies the same changes to tablespace when online for dropped objects.

A related quote from Oracle 9i curriculum

"You can drop items, such as tables and indexes, from a read only tablespace, because these command affect only the data dictionary. This is possible because the DROP command updates only the data dictionary, but not the physical files that make up the tablespace. For locally managed tablespaces, the dropped segment is changed to a temporary segment, to prevent the bitmap from being updated"

[2] What is the meaning of "For locally managed tablespaces, the dropped segment is changed to a temporary segment, to prevent the bitmap from being updated" and why it is.

Hi Tom,

It is quite information in this thread. I would like to get your input on the situation I am running into.

We have TB database on Sun solaris 2.8, in version 9.2.0.4. It is no-archive mode now and we plan to change to archive mode. There are more than 50 tablespaces, with more than 800 datafiles. Couple tablespaces have more than 100GB data in it. Daily data loads is about 1GB. We plan to use RMAN in conjuction with NetBackup to backup file to tape destination. However, we may not have Netbackup for Oracle agent available on server, so we could not backup file directly to tape, and the real issue is we only have about 80GB space on disk (I can't ask for TB on disk). The only way to do backup is to backup data into stage area first and then push to tape, erase the files on disk, and do another backup, ..., iterate until all data are backed up. (I assume that RMAN will lose cross checking capability on file which is erased in disk but in tape?) Things become complicated here due to space issue, particular for the first initial backup.

I am thinking the overall strategy for this database will be something like this:

after change to archive mode, do incremental level 0 backup(it will be on monthly basis), level 1 backup on Monday and Wednesday, and level 2 backup on T/TH/F. I will prefer to use cumulative increment backup to reduce the file needed during recovery. If there is any recovery needs, the file need to be recoverd will be minimized to one level 0 file, one level 1 and couple level 2 files. All backup will be done during database open.

However it comes two issues, time and space for level 0 backup.

on test server, it took about 2minutes to backup 1.5GB in full to disk (not counting time to tape yet), which will lead to more than 10 hours for TB size. even DB is open, this window time is still too long. Ideally we would like to minimize to within 6 hours. For incremental backup, it seems to take half time of full (level 0) backup, though it is said in doc that the time is not saved for increment backup.

secondly, the disk space for level 0 backup. I just couldn't issue the command 'backup database' as we don't have space! Alternatively, we might have to do tablespace level backup and datafile backup for those tablespace > 100GB.

I am wondering with this backup strategy, if we are able to recover database when needed (especially to restore file from tape for level 0 backup, as it is said the file should be recovered from original backup destination, and we can only restore file by file, or we could not do full database recovery at all?) and what I can do to reduce backup time.

Can you shed light here? I appreciate if you could give some thoughts. Thanks!

I found this DR procedure and have a couple question as
to how RMAN is connected since the databse is not available at couple of places. I do not have a UNIX
system with catalog to test this. Could you please clarify this

ORACLE_SID=TARGBD
% export ORACLE_SID

% rman target backup_admin/backup_admin

RMAN> set dbid=2498937635 <================ Is this just
RMAN without
executing command: SET DBID parameter

RMAN> startup nomount

Oracle instance started

Total System Global Area 521228768 bytes

Fixed Size 455136 bytes
Variable Size 486539264 bytes
Database Buffers 33554432 bytes
Redo Buffers 679936 bytes

RMAN> set controlfile autobackup format for device type disk to '/db1/orabackup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 25-JAN-04

using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20040125
channel ORA_DISK_1: autobackup found: /db1/orabackup/c-2498937635-20040125-02
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=/u03/app/oradata/TARGDB/control01.ctl
output filename=/u04/app/oradata/TARGDB/control02.ctl
output filename=/u05/app/oradata/TARGDB/control03.ctl
Finished restore at 25-JAN-04

RMAN> mount database;

database mounted

RMAN> restore database; <======= Is this RMAN connected
to catalog only

Never mind about the previous posting.

Come to think of it, all the invokation must be
rman target / catalog user/password@connect_string

I am new to RMAN.

I have Oracle Database 9.2 installed on Windows 2000.

My database running in Archivelog mode.

I had taken full backup of my database running on windows 2000 through below steps.

Rman> RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\oracle\orabackup\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\oracle\orabackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIEC
ESIZE 250 M;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFNIKUNJ.ORA'; # default

Rman > backup database plus archivelog delete input;
Rman > delete noprompt force obsolete;

1). Now I would like to test restore. So I had installed oracle database 9.2 in windows xp machine without creating oracle database.

Can you please tell me steps for restoring?

2). As my database running on archivelog mode and I set autoarchiving of archive log file.

I understand that thoese archive log files are usefull for recovering point in time(am I right ?).
Means from last full backup restore it and apply archive log files.
Now I would also like to know how to apply archive log after restoring database from last full backup ?

Thanks & regards,

Tom,

This followup pulls from a couple of the above posts/replys.
I am doing test/demo recover scenarios.

Where does Oracle get the information about which archived redo logs to apply when we have created a new controlfile???

Please let me clarify...

FACT: Take hot backup of database
FACT: Take 'alter database backup controlfile to trace'
FACT: Several hours pass, several redo logs archived.
FACT: Lose USERS tablespace datafile.
FACT: Lose all copies of controlfiles.
FACT: Recreate controlfile from 'trace'
FACT: Restore hot backup of USERS datafile.
FACT: v$log_history, v$archived_log show NO ROWS....
FACT: 'recover database until cancel'
FACT: Oracle suggests one of the previously created archived redo log files ??!?!

QUESTION: Where did Oracle get this information? ... How does it know about
these archived log files, since the controlfile has been recreated... and
v$archived_log and v$log_history show no rows ?!

Thanks,

Robert.

Tom,

Excellent!
FACT: I see the checkpoint change numbers in v$datafile and v$datafile_header.
FACT: I see how Oracle gets the archived log location/name info from the init.ora file

But I cannot see how Oracle translates the checkpoint change# into the *log sequence number* (e.g. arch_nnnn.log) ??

Thanks,

Robert.

Robert,
Take a look at v$archived_log

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch311.htm#1109807 <code>


V$ARCHIVED_LOG
This view displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

Paul,

Thanks for your suggestion... but as I stated in my original follow-up... After we create a new controlfile (from 'trace'), V$ARCHIVED_LOG is 'empty' ... it has no record of any of the previously archived logs which exist on the file system.... and which Oracle will suggest (and apply) if you do a 'recover database'.

Tom showed that the datafile structures (e.g. v$datafile, v$datafile_header) contain recovery info for each datafile. But this information is stored as 'checkpoint_change#' ... NOT log sequence

My question is: Where does Oracle get the log sequence number for identifying these archived logs from v$datafile, etc. ...... How does it translate CHECKPOINT_CHANGE# into LOG SEQUENCE# (when v$archived_log is empty) ?

Thanks,

Robert.

Tom,

So are you saying that Oracle has this archived log recovery information somewhere... but does not make it available to me !? :)

How can I see this information for myself ?

I am trying to become as proficient as possible in database recover by writing scripts which query and use the recovery information in the database for all different recovery scenarios.... what can I do in this case?

Thanks,

Robert.

Hi Tom,
Had an interview recently and the person taking my interview asked me the following question:
While doing a Hot Backup using RMAN how does RMAN know backup active trabsaction taking place in a tablespace? That is say a tablespace A has been backuped up and then there were transactions inserted in to it how does RMAN back it up?

I said it does not as it has already been backed up. But then RMAN backs up the Archivelogs. He did not agree with it. So i said that it maybe DBWn signals it to backup the above mentioned tablespace again.

He still did not agree. He said that the information is logged but could not come up with a positive answer as to where the logging information is stored and by whom.

I did not get any answer in this regard from the docs and hence i seek your help and advice.
Thanks as always

Hi Tom,
Had an interview recently and the person taking my interview asked me the
following question:
While doing a Hot Backup using RMAN how does RMAN know backup active trabsaction
taking place in a tablespace? That is say a tablespace A has been backuped up
and then there were transactions inserted in to it how does RMAN back it up?

I said it does not as it has already been backed up. But then RMAN backs up the
Archivelogs. He did not agree with it. So i said that it maybe DBWn signals it
to backup the above mentioned tablespace again.

He still did not agree. He said that the information is logged but could not
come up with a positive answer as to where the logging information is stored and
by whom.

I did not get any answer in this regard from the docs and hence i seek your help
and advice.

Sorry forgot to add: We were talking about Oracle 9i

Thanks as always

Dear Tom.
I have 3 redo log groups and 2 of them has 2 members shared accross disks and 3 one has only one member. There were working 4 users adding rows to db (commit after 1000 inserts). I changed the name of member of group 3. and oracle switched to it and archived the 2nd one instanced crashed. I know this must. I tried "recover db until cancel". After it tried "alter database open resetlogs " 
Error:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'G:\ORACLE\ORADATA\TESTDB2\SYSTEM01.DBF'

I tried 
SQL> recover database using backup controlfile;
ORA-00279: change 1935398 generated at 03/16/2005 16:39:12 needed for thread 1
ORA-00289: suggestion : F:\ORACLE\TESTDB2\ARCHIVE\ARCH56.ARC
ORA-00280: change 1935398 for thread 1 is in sequence #56
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

He wants ARCH56.ARC, but I have ARCH55.ARC.

I tried alter database clear logfile group 3, I saw that oracle created the lost one again at the same size and at the same path.

Again open db resetlogs, but result is same.
Please, what can I do?
Thanks in advance. 

Tom,

Regarding my previous post above....
When using a non-current controlfile...

I want to be able to query a table/view to see this information. I am going through the exercise of writing detailed recover scripts for all database recovery scenarios.... I want to know *before-hand* which archived redo logs Oracle will suggest/apply.

I think this a noble exercise I hope this will help me to become a master of backup/recovery.

Thanks and regards,

Robert.

Sorry for the insufficient data.

My question was:

Say RMAN just finished backing up tablespace A. And is now backing up tablespace B. But while it is baking up tablespace B new transactions were inserted into Tablespace A. How are these transactions in tablespace A baked up by RMAN?
I told my interviewer that it is not and RMAN now just backs up the archivelogs. But he said that the information is logged. But could not provide the answer as to where it is logged.
Hope it is clear now.
Thanks as always.

Tom,

Thanks for your answers and help!

I agree, RMAN is very slick and in fact we use it on many of our databases

........ BUT .......

A 'real' DBA doesn't need RMAN.... a 'real' DBA understands everything that is going on under the hood, so that they he can bring ORDER to a situation of utter CHAOS with nothing more than a sql prompt and basic sql statements (and maybe some basic o/s commands).

Thanks,

Robert.

hi Tom,

i was reading the backup and recovery concepts guide in which i was not able to undertand a few things:

1)"A consistent backup of a database or part of a database is a backup in which all read/write datafiles and control files have been checkpointed with respect to the
same system change number (SCN)."



2)Oracle determines whether a restored backup is consistent by checking the datafile headers against the datafile header information contained in the control file."

i was unable to undrstand the 1) . in the 2) i was unable to understand what he is talking about that datafile headers.please explain the 1) and 2) in a detail.

3)Oracle makes the control files and datafiles consistent to the same SCN during a database thread checkpoint.

here what is this "thread checkpoint"

thanks and regards
alex

Thanks a lot:)
alex

Tom,
we have hot backup taken at day 1 and all archive from day 1 to day 10. At day 5 we dropped one empty tablespace and temp tablespace( which was not using tempfile), also at day 5 we created temporary tablespace with tempfile. Now we need to recover from day 1 to day 10. Will we have any problem with the recovery since we dropped/added some tablespaces even we have all archive files up to day 10?

If we will encounter some issue, could you tell the workaround?

Many thanks.

Tom,
Thanks very much for the response. Your answer make me confident to start the recovery
process. We don't use RMAN currently due to certain reason(not technical reason).

Regards

Hi tom,

i think tom you have to put one link on rman i.e. like most popular.

on rman link you have to put scenario's on rman and there work around.

because your experience can make difference here.

i was reading about rman ...could you please explain me what is channel( with one example) in that i am unable to understand that ..i would be thankfull to you.

i am really sorry..still unclear ...it is not getting into my head ...


thanks
Alex

Got it!!!!!!

Thanks a LOT

Alex

Dear Tom,

In one of your answer above, you said, "Never backup online redo logfiles", in another answer your said, "everything was wiped out and you've probably lost
your current online redo logs as well -- meaning YOU LOST DATA"
maybe these answers were in different scenario, can you clarify in which scenario we will lost data if we dont backup redo log files
thanks

Tom,

When we clone one production database(non archive mode)to another server using cold backup, after start up database,
we need to recovey all tempfiles by dropping (from dictionary) and adding them. When we do something like
alter database tempfile '/db/db03/oradata/prd3/temp01.tmp' drop;

We see alert.log with the message:
ORA-01258: unable to delete temporary file /db/db03/oradata/prd3/temp01.tmp
Completed: alter database TEMPFILE '/db/db03/oradata/prd3/tem

Could you confirm that we CAN ignore this ORA-01258 error
here since cold backup didn't have tempfile backed up, so Oracle couldn't find it on O/S in new server.
But Oracle actually removed the information from data dictionary in the database.

Thanks.

We added them back in after dropped them from dictionary. The message for adding them is fine. Also after adding them,
select * from v$recover_file has no row returned.

What I want you confirm is we may ignore this error message
ORA-01258: unable to delete temporary file ...

for the drop part.

Thanks.

If I directly add them in, I get error message. Please see below


SQL> ALTER TABLESPACE TEMP_DEFAULT ADD TEMPFILE '/db/db03/oradata/prd3/temp01.tmp' size 1677721600;
ALTER TABLESPACE TEMP_DEFAULT ADD TEMPFILE '/db/db03/oradata/prd3/temp01.tmp' size 1677721600
*

ERROR at line 1:
ORA-01537: cannot add data file
'/db/db03/oradata/prd3/temp01.tmp' - file already part of
database

That is why I need to drop them first.

I clone another production database by hot backup, in that case, after I open databse, I don't need to drop tempfile first, I may add tempfile without error message.

Thanks . 

without having any control file backup is it possible to recover a database(whole or partial)?

Hi Tom.

I have a script I use to backup the database. (Well, actually I have two - one for cold backup, one for hot. Let's concentrate on the hot backup here though.)

It does a SELECT ... FROM v$datafile to generate a second SQL file. This puts each tablespace into hot backup mode, does a HOST call to copy the datafile(s), and then takes the tablespace out of hot backup mode.

Is there a way to achieve this without writing to an external file and then reading it all back in? I mean, could I do something in PL/SQL that does it? What would that look like?

Is it possible to recover just a tablespace from
RMAN backup (COLD)

I actually meant cold backup that is not in archive log
mode. Because if it is in archive log mode, I might as well
take a rman hot backup.

If it is not in archive log mode , I have no other choice but to take a cold backup. Since backup set scn less than the current scn only use of this mode of backup is for just full database recovery. Is this correct

Tom,
Sorry for question in middle....

Can you explain me what is a STOP SCN concept.
and how it is helpful in instance recovery.

If I take a dump of datafile headers
DATA FILE #1:
(name #312) /u01/dbase/system_01.dbf
creation size=0 block size=8192 status=0xe head=312 tail=312 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:103486 scn: 0x020b.e2a5e40c 06/01/2005 13:21:21
Stop scn: 0xffff.ffffffff 05/28/2005 23:53:25
Creation Checkpointed at scn: 0x0000.0000000a 04/03/2003 08:34:20
thread:0 rba:(0x0.0.0)

Here I am looking at STOP SCN and unrecoverable SCN.
Can you please explain me ..

Thanks in advance
Kishor Bhalwankar

Hi,

We have a issue while recovery using backup controlfile until cancel.

1)we have mismatching name of UNDO in init file (prod/dev)
2)we restored datafiles from prod to dev frm hpt backup
3)we created controlfile
4)we recovered database using backup controlfile until cancel.
5)it was ok
6)then we gave alter database open resetlogs
7)It gave error and disconnect force as UNDO tablespaces name is not matching.

How to open database after this?

we tried many ways

1)changind UNDO tbs name in init file and trying recovery then give cancel(but its asking for sequecne 1,which is not there)
2)in mount stage offline drop undo datafiles

Please help us.

Thanks

Hi,
I have following situation..
1)I have many different system on same db box..say HR,finance(different schema...One Instance).I have taken hot backup on say 02/10/2004.Yes db is under archive log mode.

2)Now on 03/10/2004 by mistake some one uploaded lots of data in finance system.Now I want to revert back to 02/10/2004 state..BUT only for the finance schema.I want upto date data for HR(No changes in HR schema ,meaning whatever was entered on 03/10/2004 ,should be intact.).
I am using 9.2.How do I recover from this scnerio.

Cheers

Hi Tom

I have a question with regards to hotbackups.

Can we recover the database or some of the database when:

a. We got archive logs for the last 5 days.
b. Controlfiles are ok.
c. Redo is ok
d. We have the system datafile only available in backup.
e. Parameter file is ok, but there are no backups and not all datafiles available.

Something like recreate all datafiles empty and then apply the archive logs at hand.

I know the docs say that you can re-create a datafile from empty when you have all the archive logs since that datafile
was added. I was thinking if there is an option for recreating them with whatever archives at hand, say last 5 days.

Many Thanks

Kubilay

Is there a simpler way to identify the backup set
that includes controlfile (excluding the backupset
which contains autobackup controlfile)

rc_backup_set records identical for backupset with
system.dbf and the autobackup controlfile. By joining
rc_backup_set and rc_backup_piece and look for
the format of the handle is a bit tedious

Hi Tom,

Morning! I am in serious trouble i guess. We have our database 9ir2 running on Archive log mode. Every day the archive is stored in one of the mount points in Solaris. I take a backup of these archived logs thru a Cron Job at night.

Today, one of the admin guy has deleted a couple of archived log from the destination while doing some maintenance work. I dont have a backup of these archived logs yet.

Can you please suggest, how am i going to recover those lost archived logs?

I am looking at all the possible options..but none of them suggest for a recovery of a lost archived log.

Need your help ..once more.

Thanks and regards,
David.

Dear Tom,

I tried this recovery scenario, and am getting the data back as well. But I dont understand how it is doing that. Can you please explain me the logic behind this recovery..



SQL> SELECT * FROM SCOTT.TEST;

         I          J          K                                                
---------- ---------- ----------                                                
        10         20         30                                                
       100        200        300                                                
      1000       2000       3000                                                
         1          1          1                                                
         2          2          2                                                

SQL> REM - - -5 ROWS IN SCOTT.TEST
SQL> 
SQL> REM - - - TAKE THE COLD BACKUP
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> REM - - - START THE DB AND DO SOME TRANSACTION - WHICH IS NT THERE IN BKUP
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
Database mounted.
Database opened.
SQL> 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            F:\TEST\ARC
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> insert into scott.test values(3,3,3);

1 row created.

SQL> insert into scott.test values(4,4,4);

1 row created.

SQL> insert into scott.test values(5,5,5);

1 row created.

SQL> commit;

Commit complete.



--------------------THIS DATA IS NOT THERE IN THE BACKUP-----------------------

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            F:\TEST\ARC
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

SQL> REM SHUTDOWN THE DB, DELETE EVERYTHING EXCEPT THE INITTEST.ORA
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> REM DELETING......
SQL> 
SQL> startup nomount
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
SQL> 
SQL> 

SQL> REM --- COPY THE DATAFILES TO THE LOCATION...
SQL> 
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 2
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'F:\TEST\REDO01.LOG'  SIZE 100M,
 10    GROUP 2 'F:\TEST\REDO02.LOG'  SIZE 100M,
 11    GROUP 3 'F:\TEST\REDO03.LOG'  SIZE 100M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'F:\TEST\SYSTEM01.DBF',
 15    'F:\TEST\UNDOTBS01.DBF',
 16    'F:\TEST\USERS.ORA'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> 
SQL> REM -- SHIP THE NECESSARY ARCHIVES
SQL> 

SQL> recover database using backup controlfile;
ORA-00279: change 889559 generated at 08/22/2005 11:32:49 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S1.DBF 
ORA-00280: change 889559 for thread 1 is in sequence #1 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 889734 generated at 08/22/2005 11:38:20 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S2.DBF 
ORA-00280: change 889734 for thread 1 is in sequence #2 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S1.DBF' no longer needed for this 
recovery 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 889736 generated at 08/22/2005 11:38:21 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S3.DBF 
ORA-00280: change 889736 for thread 1 is in sequence #3 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S2.DBF' no longer needed for this 
recovery 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 889740 generated at 08/22/2005 11:38:27 needed for thread 1 
ORA-00289: suggestion : F:\TEST\ARC\ARCH_T1_S4.DBF 
ORA-00280: change 889740 for thread 1 is in sequence #4 
ORA-00278: log file 'F:\TEST\ARC\ARCH_T1_S3.DBF' no longer needed for this 
recovery 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00328: archived log ends at change 888991, need later change 889740 
ORA-00334: archived log: 'F:\TEST\ARC\ARCH_T1_S4.DBF' 


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery 
ORA-01110: data file 1: 'F:\TEST\SYSTEM01.DBF' 




REM..............................I THOUGHT IT HAD FAILED, SO TRIED TO RECREATE THE CTL FILE , AND NO RECOVERY ATTEMPTED THIS TIME!!.................................

SQL> shutdown immediate;
ORA-01109: database not open 


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  437330132 bytes                                       
Fixed Size                   454868 bytes                                       
Variable Size             226492416 bytes                                       
Database Buffers          209715200 bytes                                       
Redo Buffers                 667648 bytes                                       
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 2
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'F:\TEST\REDO01.LOG'  SIZE 100M,
 10    GROUP 2 'F:\TEST\REDO02.LOG'  SIZE 100M,
 11    GROUP 3 'F:\TEST\REDO03.LOG'  SIZE 100M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'F:\TEST\SYSTEM01.DBF',
 15    'F:\TEST\UNDOTBS01.DBF',
 16    'F:\TEST\USERS.ORA'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test; -------------------- GOT THE DATA BACK!!!

         I          J          K                                                
---------- ---------- ----------                                                
        10         20         30                                                
       100        200        300                                                
      1000       2000       3000                                                
         1          1          1                                                
         2          2          2                                                
         3          3          3                                                
         4          4          4                                                
         5          5          5                                                

8 rows selected.


 

--- with the above posting:

Let me summarize what i have attempted:
1. initially, there were only 5 rows in SCOTT.TEST Table.
2. i shutdown the database and took the cold backup.
3. the database is in archivelog mode.
4. i started up the database, and added 3 more rows to the SCOTT.TEST table. Committed it.
5. switched the logfiles a few number of times.
6. shutdown the database
7. deleted ALL DATAFILES, CONTROLFILES, REDOLOGFILES
8. i already have the CREATE CONTROLFILE SCRIPT for this database.
9. startup nomount the database. the parameter file is the same as before.
10. Restored the datafiles, created the controlfile using the CREATE CONTROLFILE SCRIPT.
11. Recovered the database, by applying the archivelogs.
12. i am not able to open the database, so:
13. shutdown immediate the database
14. startup nomount the database
15. create control file again
16. open the database resetlogs..
i get all the data which was in the archives but not in the backup.

My question is why didnt it open the database after i had done the recovery? why do i have to shutdown the database, and then recreate the control file to do the same?

Tom,

I have been in a similar situation many times like Puja's where

1. Create the controlfile
2. Apply all the archive logs
3. Open with resetlogs. It fails.
4. Shutdown
5. Recreate the controlfile
6. Open with reset logs. Works this time.

Your answer is a little unconvincing because there was no recovery between steps 3 and 6. Only the controlfile was recreated.

I still don't understand why it works the second time.

Hi..

I did not apply the online redo logs, because i had lost them..
After taking the cold backup, and then making some changes to the table, i had shutdown the database, and deleted ALL DATAFILES, CONTROLFILES and ONLINE REDOLOG FILES. All that I had left with me was SPFILE, ARCHIVE FILES and the trace of the CONTROLFILE. And i applied only the archive files. I could not apply the online redo log files. i had then tried to open the database with RESETLOGS, i relied on this option to create the new REDOLOG FILES..

Dear Tom,

Hi,

My demo database is in no-archive mode has 3 log files, this morning when I gave a command for startup it shows current log is corrupted. I tried clearing it but of no use. I tried recover until cancel, and then open with resetlogs -- in vain. Please guide.

regards,

Hi,

I need help on how to recover a corrupted data block in 9.0.1.

Command entered:
RMAN> backup validate database;
.....
input datafile fno=00015 name=/ora101/oradata/ts10/perfx01.dbf
.....

Got errors:
MAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00579: the following error occurred at 09/01/2005 10:17:33
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-12004: unhandled exception during command execution on channel ORA_DISK_1
RMAN-10035: exception raised in RPC: ORA-19501: read error on file "/ora101/oradata/ts10/perfx01.dbf", blockno 61121 (blocksize=8192)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 24576
Additional information: 524288
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPVALIDATE

I tried to recover the corrupted block by issuing:

RMAN> blockrecover datafile 15 block 61121;

The udump trace file shows:
No recovery needed for file 15, block 61121

I did the validation again and the corrupted block is still there.
BTW, there's no v$database_block_corruption in my database.
Is this a 9iR2 only?

Thanks,
CJ

Hi Tom,
I have got a scenario for you which i tried out at our 9205(HP-UX) development system.can you explain me why that behaviour please.
1.Database is running in Archivelog Mode.
2.I take a Backup of all the datafiles.
3.I do some transactions now.
4.After this no other transactions are happening in database.
5.Purposely i overwrite the file to which i had done transactions with my backed up file.
6.I do a alter system checkpoint with the intention that ckpt process will stamp the latest scn to control as well as all the data files.now the old file got stamped with new scn.
7.query the table to which i had done transactions i lose all the transacted data.
8.Database is just running and fine, i bounce it a few times no issues with startup or whatever.
If this is the case anyone can tamper with my database...how is it possible i am still wondering..Did Oracle Got Fooled? just give me a clue as i cant believe it.

ok assume a dba leaving the organization on bad terms, he definately has the ability to do that.Anyways my question was why is there no consistency issue on my next startup?
Thanks

I would need an example like this:


ops$tkyte@ORA9IR2> create tablespace testing datafile '/tmp/testing.dbf' size 5m;
 
Tablespace created.
 
ops$tkyte@ORA9IR2> alter tablespace testing begin backup;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> !cp /tmp/testing.dbf /tmp/backup.dbf
 
ops$tkyte@ORA9IR2> alter tablespace testing end backup;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> create table testing_tab ( x int ) tablespace testing;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into testing_tab select rownum from all_users;
 
28 rows created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> !cp /tmp/backup.dbf /tmp/testing.dbf
 
ops$tkyte@ORA9IR2> alter system checkpoint;
 
System altered.
 
ops$tkyte@ORA9IR2> select * from testing_tab where rownum = 0;
select * from testing_tab where rownum = 0
              *
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/tmp/testing.dbf'
 
 
ops$tkyte@ORA9IR2> !tail -10 $ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Completed: alter tablespace testing end backup
Mon Sep  5 10:12:22 2005
Beginning global checkpoint up to RBA [0x294.5f9a.10], SCN: 0x0000.02f7c7f1
Mon Sep  5 10:12:23 2005
Errors in file /home/ora9ir2/admin/ora9ir2/bdump/ora9ir2_ckpt_10780.trc:
ORA-01171: datafile 8 going offline due to error advancing checkpoint
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: '/tmp/testing.dbf'
ORA-01208: data file is an old version - not accessing current version
Completed checkpoint up to RBA [0x294.5f9a.10], SCN: 0x0000.02f7c7f1

<b>
Now, if I shutdown, startup, same problem - but I can recover datafile '/tmp/testing.dbf' and online the tablespace - but all of my data comes back. 


</b>

 

Hi

Running Oracle 9.2.0.6 in Linux, I am trying to rename a datafile, when I try to make tablespace offline I get this error:

SQL> ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users OFFLINE NORMAL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13


SQL> 
SQL> ALTER TABLESPACE users online;

Tablespace altered.

SQL> select * from v$recover_file;

no rows selected

How so? 

Hi

SQL> select file_name, bytes from dba_data_files 
  2  where file_name = '/u02/oradata/lnx920/users01.dbf';

FILE_NAME                                     BYTES
---------------------------------------- ----------
/u02/oradata/lnx920/users01.dbf           103546880


SQL> select name, status from v$datafile
  2  where name = '/u02/oradata/lnx920/users01.dbf';

NAME                               STATUS
---------------------------------- -------
/u02/oradata/lnx920/users01.dbf    ONLINE

SQL> select count(*) from dba_segments where tablespace_name  = 'USERS';

  COUNT(*)
----------
        93

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13


SQL> select count(*) from dba_segments where tablespace_name  = 'USERS';

  COUNT(*)
----------
        93

SQL> select name, status from v$datafile
  2  where name = '/u02/oradata/lnx920/users01.dbf';

NAME                               STATUS
---------------------------------- -------
/u02/oradata/lnx920/users01.dbf    OFFLINE

SQL> select file_name, bytes from dba_data_files 
  2  where file_name = '/u02/oradata/lnx920/users01.dbf';

FILE_NAME                                     BYTES
---------------------------------------- ----------
/u02/oradata/lnx920/users01.dbf

SQL> recover tablespace users;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter tablespace users online;

Tablespace altered.


SQL> !ls -al /u02/oradata/lnx920/users01.dbf
-rw-r--r--    1 oracle   dba      103550976 Aug 24 11:15 /u02/oradata/lnx920/users01.dbf

SQL> !date
Thu Aug 24 11:16:15 CEST 2006

SQL> select name, CHECKPOINT_CHANGE#, LAST_CHANGE# from v$datafile;

NAME                               CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------- ------------------ ------------
/u02/oradata/lnx920/system01.dbf              3825068
/u02/oradata/lnx920/data01.dbf                3825068
/u02/oradata/lnx920/drsys01.dbf               3825068
/u02/oradata/lnx920/example01.dbf             3825068
/u02/oradata/lnx920/indx01.dbf                3825068
/u02/oradata/lnx920/odm01.dbf                 3825068
/u02/oradata/lnx920/tools01.dbf               3825068
/u02/oradata/lnx920/users01.dbf               3825716
/u02/oradata/lnx920/xdb01.dbf                 3825068
/u02/oradata/lnx920/undotbs02.dbf             3825068
/u02/oradata/lnx920/data02.dbf                3825068
/u02/oradata/lnx920/dmt01.dbf                 3825068

12 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select name, CHECKPOINT_CHANGE#, LAST_CHANGE# from v$datafile;

NAME                               CHECKPOINT_CHANGE# LAST_CHANGE#
---------------------------------- ------------------ ------------
/u02/oradata/lnx920/system01.dbf              3825875
/u02/oradata/lnx920/data01.dbf                3825875
/u02/oradata/lnx920/drsys01.dbf               3825875
/u02/oradata/lnx920/example01.dbf             3825875
/u02/oradata/lnx920/indx01.dbf                3825875
/u02/oradata/lnx920/odm01.dbf                 3825875
/u02/oradata/lnx920/tools01.dbf               3825875
/u02/oradata/lnx920/users01.dbf               3825875
/u02/oradata/lnx920/xdb01.dbf                 3825875
/u02/oradata/lnx920/undotbs02.dbf             3825875
/u02/oradata/lnx920/data02.dbf                3825875
/u02/oradata/lnx920/dmt01.dbf                 3825875

12 rows selected.



The datafile is ok but gives error when offline

Also my system clock is a bit screwed but dont think that is the problem though 

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 52995511 Jun 29 2005 /u01/app/oracle/product/9.2.0/bin/oracle

I also want to note that tis only happens with users tablespace, what it puzzles me is the datafile seems 100% ok

[oracle@rac01 templates]$ sqlplus system@lnx920

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 24 18:50:31 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password: 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> 
SQL> 
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u02/oradata/lnx920/users01.dbf'
ORA-06512: at line 13

I can even resize the file!!!!

alter database datafile '/u02/oradata/lnx920/users01.dbf' resize 25M;

Database altered.

SQL> !ls -al /u02/oradata/lnx920/users01.dbf
-rw-r--r--    1 oracle   dba      26218496 Aug 24 18:55 /u02/oradata/lnx920/users01.dbf

 

try offline immediate, then bring it online, then try offline. just curious.

Hi Tom,

I have taken a cold backup one month back. Today due to some problem I have to restore each file i.e control file, datafiles, redo log files from the backup to the database location. I am having all the archivelogs from that day to today. I will be thankful if you explain how can i completely recover from this scenario.

If it's possible(which i think should be),I want to know how Oracle know from which archive log file to start and upto which archive log file it will go. I just wanna know how it is being done..From where Oracle gets the clue about the archive log numbers.

Thanks in advance.

I have read this thread (and other related ones) completely. We may have a tricky recovery scenario coming up shortly. Here is what happened:

Our datacenter is in Boca Raton, and suffered a power outage early Monday. Unfortunately, the backup generator was also damaged, and the Sunfire 6800 went on battery power. It is connected to a Hitachi SAN, on which we have a 1TB warehouse. When I found out the server would be going down, I got on quick and shut down the database. Next, I had a unix session in another window and when I did a df -k, all of the volumes on the SAN had disappeared. So the connection to the SAN was lost, but I do not know if that happened before or after I did the shutdown. They both happened within a minute of each other.

The best case is that I got the db shut down before the SAN lost connection with the server. Then I think normal instance recovery will be okay. If not, I may have a very sticky recovery. We only do a cold backup on Sunday, to tape, and no archivelogging (NOT MY IDEA!). ALL of the db files are on the SAN, but I do have one side of the redo log group (Oracle) mirrors on local storage on the Sunfire, and also a copy of the control file locally. There were jobs running when I shut down the db and/or the SAN lost connection to the Sunfire.

My question is, what process would I follow to recover, assuming that I may have to recover 400+ data files from tape. When that is done, and before trying to start up the database, should I copy the redo logs restored from tape over to the location where they are stored locally? Or should I just delete them and open the database with the resetlogs option? And should I do the same with a control file copy restored from tape where the one copy resides in local storage? I believe that the redo logs on the SAN and the ones on local storage may be different, and the same could be true with the control file copies on the SAN and on a local drive.

Any suggestions will be appreciated. The power was just restored and they are bringing up the boxes now.

Almost embarassed to admit we are in noarchivelog mode, but this is not my choice. The belief here is that they can restore from a Sunday cold backup and just "reload" all the warehouse data from the source files. There is an economic component (storage) to that as well. (Yes, I know disk is cheap, but these folks are even cheaper).

Regarding the backup itself, they insist on backing up complete file systems instead of individual files. I have been rebuffed when I tell them I would prefer to give them a script to run which will generate a list of all the files that need to be backed up (excluding the online redo logs). So the redo logs get included in the backup whether I want them or not.

Part of my question is, should I just drop the restored copies of the redo logs when the copy from tape is complete? And how about the copies on local storage that were never lost? We also have copies of the control files that will be restored, as well as a local copy that was never lost. I guess I'm asking for best advice on this situation, we do not even have the boxes up yet, and it all may be moot if I can simply get an instance recovery or do a recover database command successfully. IF neither of those happen, then I will certainly be opening a TAR while the tape backup is copied back over. This is our production warehouse.

Thanks for your reply. I know you are traveling, and I do appreciate that kind of dedication to your users.

As a newcomer to the DBA world, I've been studying on backup/recovery every day. I _must_ understand this, as I intend to lose no data, ever.

With that thought in mind, I have questions about the correct backup strategy for the RMAN repository. Currently, the RMAN repository is housed in the database rmanrepo (9.2.0.4) and a hot backup is taken weekly and the backup is stored in the RMAN repository in rmanrepo. I have asked this question to the senior DBAs here, and they have said this is no problem, as they have recovered the RMAN repository and all databases before when a disk subsystem crashed. From what I understand, they somehow recataloged all of the database backup files RMAN produced.

From my reading through the RMAN User Guide, it is very clear that this should not be setup this way. The method they recommend is to backup using the controlfile as the repository. After that, backing up the controlfile is required.

Since the senior DBAs have restored the RMAN repository before with this configuration after a major media failure, is there any reason we should switch to using the controlfile as the repository for the RMAN database only?

why fix what isn't broken and has proved to work fine for you

Well, it's not necessarily fixing something that's broken. I want to be able to easily recover from a disaster. Since the senior DBAs have recovered using our current configuration, I feel fine with keeping the configuration this way.

However, let's say for some reason the recovery catalog is corrupted or lost. If the method we are currently using adds a few hours to the restore, and also adds additional risk, then I would rather make the necessary changes for an easy recovery. Given the choice, I'll take the easy recovery over a complex one any day.

So, do you also believe that you shouldn't patch systems with security updates? I hope not. Sometimes things aren't broken, but they do need fixed, to ensure data integrity and availability.

Tom,

I'm practicing disaster recovery to an extreme extent. What I'm testing is the loss of our Unix server which houses our recovery catalog. Included in the loss is one of our small production databases. All of the databases we have run on HP-UX, and the recovery catalog points to a NAS device, which houses all of our RMAN backup sets via NFS.

During a disaster of this type, I figured I should test both restoring to Unix and to NT, since I will never know for sure what OS I will need to restore to in order to get production up quickly. This is an extreme scenario, but I'd rather test prior to experiencing it (hopefully I won't experience it, but you never know).

I'm having issues getting started on NT. I've run oradim to get the service started and created a sample init.ora to mount the database. I've been successful setting my dbid in RMAN, as well as resetting my database to a previous incarnation. In addition, I imported my recovery catalog into an instance as well, so I'm able to do 'list backup' successfully.

What I'm failing at is simply restoring anything, such as controlfile. I've copied the backup pieces to a local drive, and I'm getting errors stating the backup piece is not valid. The command I'm using is:

restore controlfile from 'e:oracle
ecoverproddb_0rv8971_1_1'

I've been reading through the Recovery Manager docs trying to piece different sections together, but I cannot come up with a solution.

Is there something special I should do when restoring to a new host _and_ using backup pieces that are residing in a different location than the recovery catalog knows?

Thanks for all your help and assistance,

Tom

Yeah, I wish I could have done the cut/paste method when I posted, but I posted late last night after I got home from work. I had been reading most of the night and figured I'd post since I was getting nowhere fast. Of course, it is being tested on a system at work with no remote access , so I'll respond with the cut/pastes on Monday.

As for the location of the RMAN backup pieces, I am deliberately putting them on a Windows box to test a restore in this scenario. Essentially I'm testing a restore when my NAS devices are unavailable and all I have is the files restored from tape to a Windows box.

Maybe I'm testing too many failures at once... :)

Well, that's what I was trying with the restore command above:

restore controlfile from 'e:\oracle\recover\<backup piece>'

And it bombed saying it wasn't a valid piece. I figured just as you said that it really wouldn't care about where it was located, as long as it was a valid file.

I'll output the list backup and other commands and paste them on Monday, but it sure has me stumped.

E:\oracle\recover>rman

Recovery Manager: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> connect catalog rman/rman@ora9206

connected to recovery catalog database

RMAN> startup mount

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/07/2005 08:07:20
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> list incarnation of database proddb;

List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3119092 3119093 proddb 3258732496 YES 1 15-OCT-04
3732678 3732679 proddb 3275501872 YES 1 23-APR-05

RMAN> set dbid= 3275501872

executing command: SET DBID

RMAN> reset database to incarnation 3732679;

database reset to incarnation 3732679 in recovery catalog

RMAN> list backup completed between '27-OCT-05' and '31-OCT-05';

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195277 Full 468M DISK 00:02:22 27-OCT-05
BP Key: 4195281 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0rh28smd_1_1
Controlfile Included: Ckp SCN: 8351646465636 Ckp time: 27-OCT-05
List of Datafiles in backup set 4195277
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_system01.dbf
8 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_imtrni01.dbf
9 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_iongd01.dbf
11 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_irefd01.dbf
12 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_irefi01.dbf
21 Full 8351646465639 27-OCT-05 /u01/oracle/proddb/proddb_monti01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195278 Full 589M DISK 00:02:43 27-OCT-05
BP Key: 4195282 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0qh28smd_1_1
List of Datafiles in backup set 4195278
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 8351646465638 27-OCT-05 /var/opt/vgdb004/u01/oracle/proddb/proddb_undotbs01.dbf
4 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_apigei01.dbf
10 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_implongi01.dbf
13 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_impltrnd01.dbf
17 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irthtrni01.dbf
18 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irtrnd01.dbf
19 Full 8351646465638 27-OCT-05 /u01/oracle/proddb/proddb_irtrni01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195279 Full 629M DISK 00:02:47 27-OCT-05
BP Key: 4195283 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0ph28smd_1_1
List of Datafiles in backup set 4195279
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_apisged01.dbf
6 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_auddata02.dbf
7 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_irnd01.dbf
14 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_itrni01.dbf
16 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_irthtrnd01.dbf
20 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_montd01.dbf
24 Full 8351646465637 27-OCT-05 /u01/oracle/proddb/proddb_xdb01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4195280 Full 1G DISK 00:06:19 27-OCT-05
BP Key: 4195284 Status: AVAILABLE Tag: TAG20051027T190027
Piece Name: /var/opt/nas2/development/backup/development_proddb_0sh28smd_1_1
List of Datafiles in backup set 4195280
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_data01.dbf
15 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_indx01.dbf
22 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_tools01.dbf
23 Full 8351646465640 27-OCT-05 /u01/oracle/proddb/proddb_users01.dbf

RMAN> host "dir *0?h28smd_1_1*";

Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/04/2005 03:12 PM 659,988,480 development_proddb_0ph28smd_1_1
11/04/2005 03:13 PM 617,652,224 development_proddb_0qh28smd_1_1
11/04/2005 03:14 PM 491,692,032 development_proddb_0rh28smd_1_1
11/04/2005 03:16 PM 1,729,110,016 development_proddb_0sh28smd_1_1
4 File(s) 3,498,442,752 bytes
0 Dir(s) 942,005,960,704 bytes free
host command complete

RMAN> restore controlfile from "e:\oracle\recover\development_proddb_0rh28smd_1_1";

Starting restore at 07-NOV-05

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2005 08:15:39
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN>host;

E:\oracle\recover>dir
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:22 AM <DIR> .
11/07/2005 08:22 AM <DIR> ..
11/04/2005 03:07 PM 656,416,768 development_proddb_0lh22o0i_1_1
11/04/2005 03:08 PM 617,037,824 development_proddb_0mh22o0i_1_1
11/04/2005 03:09 PM 490,782,720 development_proddb_0nh22o0i_1_1
11/04/2005 03:11 PM 1,618,518,016 development_proddb_0oh22o0i_1_1
11/04/2005 03:13 PM 617,652,224 development_proddb_0qh28smd_1_1
11/04/2005 03:14 PM 491,692,032 development_proddb_0rh28smd_1_1
11/04/2005 03:16 PM 1,729,110,016 development_proddb_0sh28smd_1_1
11/04/2005 03:17 PM 671,997,952 development_proddb_0th2rba9_1_1
11/04/2005 03:18 PM 629,563,392 development_proddb_0uh2rba9_1_1
11/04/2005 03:19 PM 496,394,240 development_proddb_0vh2rba9_1_1
11/04/2005 03:22 PM 1,872,707,584 development_proddb_10h2rbaa_1_1
11 File(s) 9,891,872,768 bytes
2 Dir(s) 942,665,949,184 bytes free

E:\oracle\recover>del *h28smd*

E:\oracle\recover>dir
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:23 AM <DIR> .
11/07/2005 08:23 AM <DIR> ..
11/04/2005 03:07 PM 656,416,768 development_proddb_0lh22o0i_1_1
11/04/2005 03:08 PM 617,037,824 development_proddb_0mh22o0i_1_1
11/04/2005 03:09 PM 490,782,720 development_proddb_0nh22o0i_1_1
11/04/2005 03:11 PM 1,618,518,016 development_proddb_0oh22o0i_1_1
11/04/2005 03:17 PM 671,997,952 development_proddb_0th2rba9_1_1
11/04/2005 03:18 PM 629,563,392 development_proddb_0uh2rba9_1_1
11/04/2005 03:19 PM 496,394,240 development_proddb_0vh2rba9_1_1
11/04/2005 03:22 PM 1,872,707,584 development_proddb_10h2rbaa_1_1
8 File(s) 7,053,418,496 bytes
2 Dir(s) 945,504,403,456 bytes free

E:\oracle\recover>ftp db1prod
Connected to hchp4002.
220 db1prod FTP server (Version 1.1.214.4(PHNE_29461) Thu Nov 20 06:40:06 GMT 2003) read
User (db1prod:(none)): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> bin
200 Type set to I.
ftp> prom
Interactive mode Off .
ftp> cd /var/opt/nas2/development/backup
250 CWD command successful.
ftp> mget development_proddb*h28smd_1_1
200 Type set to I.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0ph28smd_1_1 (659988480 bytes).
226 Transfer complete.
ftp: 659988480 bytes received in 57.95Seconds 11388.34Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0qh28smd_1_1 (617652224 bytes).
226 Transfer complete.
ftp: 617652224 bytes received in 56.38Seconds 10956.14Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0rh28smd_1_1 (491692032 bytes).
226 Transfer complete.
ftp: 491692032 bytes received in 44.17Seconds 11131.31Kbytes/sec.
200 PORT command successful.
150 Opening BINARY mode data connection for development_proddb_0sh28smd_1_1 (1729110016 bytes).
226 Transfer complete.
ftp: 1729110016 bytes received in 158.72Seconds 10894.16Kbytes/sec.
ftp> dir *h28smd*
200 PORT command successful.
150 Opening ASCII mode data connection for /usr/bin/ls.
-rw-rw---- 1 oracle dba 659988480 Oct 27 19:00 development_proddb_0ph28smd_1_1
-rw-rw---- 1 oracle dba 617652224 Oct 27 19:00 development_proddb_0qh28smd_1_1
-rw-rw---- 1 oracle dba 491692032 Oct 27 19:00 development_proddb_0rh28smd_1_1
-rw-rw---- 1 oracle dba 1729110016 Oct 27 19:00 development_proddb_0sh28smd_1_1
226 Transfer complete.
ftp: 381 bytes received in 0.00Seconds 381000.00Kbytes/sec.
ftp> quit
221 Goodbye.

E:\oracle\recover>dir *h28smd*
Volume in drive E is LaCie
Volume Serial Number is 9CA2-E620

Directory of E:\oracle\recover

11/07/2005 08:25 AM 659,988,480 development_proddb_0ph28smd_1_1
11/07/2005 08:26 AM 617,652,224 development_proddb_0qh28smd_1_1
11/07/2005 08:27 AM 491,692,032 development_proddb_0rh28smd_1_1
11/07/2005 08:29 AM 1,729,110,016 development_proddb_0sh28smd_1_1
4 File(s) 3,498,442,752 bytes
0 Dir(s) 942,005,960,704 bytes free

E:\oracle\recover>exit

RMAN> restore controlfile from "e:\oracle\recover\development_proddb_0rh28smd_1_1";

Starting restore at 07-NOV-05

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2005 08:33:37
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN>

Wow, I know that's long but it spells it out much easier than I could have said in words. The files were transferred to my drive via FTP in binary mode from the NAS device. I'm not sure if RMAN doesn't like the path or if it doesn't like the backup pieces. I transferred them a second time just to be sure, and it still failed, as you can see above.

Thanks for your patience and assistance.

Yes I am.

In our DR plans, we want to be able to restore to either Unix or Windows. We would rather restore to Unix, but if the disaster is significant enough, we may have to restore to Windows due to availability of machines.

After that short response, I guess it's not possible, right?

I did a cold backup with OS utilities of a database on HP-UX and attempted to recreate the controlfile on NT and it failed with this:

CREATE CONTROLFILE REUSE DATABASE "DBA101" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'e:\oracle\oradata\dba101\dba1_system01.dbf'
ORA-27047: unable to read the header block of file
OSD-04001: invalid logical block size (OS 2097152)

From what I'm gathering, Oracle stores either the filesystem type or platform in the header of each datafile. So the only cross-platform mechanism is export/import or sql copy.

So, does this mean that a database on Solaris != HP-UX != AIX != Linux as well?

Thanks for your patience and guidance.

Tom,

OS: Linux
OAS: AS: 10g Release 2 (10.1.2) 

Infrastructure and middle tier installed on the same box.
After reboot the system.
I am trying to startup database getting error as below.

SQL> connect
Enter user-name: sys as sysdba
Enter password:
Connected.


SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                   778968 bytes
Variable Size             228859176 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
ORA-00214: controlfile '/opt/oracle/oradata/orcl/control01.ctl' version 20626
inconsistent with file '/opt/oracle/oradata/orcl/control02.ctl' version 20493


Regards,
Dawar Naqvi
 

ITS DONE DO NOT WORRY ABOUT IT.

HERE WAHT I HAVE DONE.

DUE TO POWER FAILUER MY DATABASE HAS BEEN CRASHED.
SO I HAVE THREE CONTROL FILES. ONE OF IT WAS GOOD ONE, THANKS FOR ORACLE SUPPORT.

I HAVE COPY GOOD TO THE BAD ONE. 

AND ITS WORK.

oracle@appserver:~/oradata/orcl> cp control01.ctl control02.ctl

oracle@appserver:~/oradata/orcl> cp control01.ctl control03.ctl
oracle@appserver:~/oradata/orcl> sqlplus

SQL*Plus: Release 10.1.0.4.2 - Production on Fri Nov 18 17:08:42 2005

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                   778968 bytes
Variable Size             228859176 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL>


cheers,
Dawar
 

Hi

Working in Oracle 9.2.0.6 Linux

I am restoring a noarchive log database cold backup using RMAN , controlfile as the catalog and using current controlfile.

How do I do that?

In sqlplus I restore and do recover database until cancel then open with resetlogs.

In rman it doesnt work

restore database
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS;

it says

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/25/2005 11:06:35
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

:-?

The recovery scenario that Puja mentioned above on Aug. 22 seems like a generic recovery scenario.

His scenario however seemed to be an exercise in recovery and not the real thing. Of course, if this were the real thing and as fate would have it, tons of people looking over your shoulder asking you every other second "is it done yet?" one may issue the first ALTER DATABASE OPEN RESETLOGS; and panic when they see the response. At this point, one may think they did something wrong and actually go about a different course of action which really hoses up the restore instead of just shutdown, re-create controlfile and re-issue open resetlogs.

As he mentioned, there were no online redo logs available during recovery. Is issuing OPEN RESETLOGS twice expected behaviour under these circumstances?

Hi Tom,

I have a recovery scenario as given below...

T1> Hot backup taken of the whole database.
T2> A new tablespace TS05 created in the database.
T3> Database crashes and all data files got corrupted.

NOTE: All the archive logs are intact and no back up for TS05 tablespace exists.

How to get the recover the database till time T3?

Would appreciate if a step by step solution is given to the above scenario.

for the do it yourself approach and

Thanks Tom, You've always been of great help to me !!!

Tom,

If hot backup is in progress , copy all files ( data,control , redo and archive logs) to different server (svr2).
Now I am bringgin up database in svr2
startup mount
recover database (took 10 mins)
alter database open;

a. Is it possible to bring up database if all datafiles are in begin backup mode?

Thanks!

Tom,

OS: AIX version 5.2
DB: 10.1.0.4.0

I need to maintained existing server.

How to find any backup process exits?
Assume we do not have any documentation available and consultant who worked before had left.
he mentioned in his email that hot backup sholud exits and he also noted that script for automatically exp.dmp exits for one of our schema.

Now how could I find backup script? (If exits)

Is there any way to find that any "Oracle backup" exits via cron job or any other process?


Here what I did.

A)

From Oracle Enetrprise manager (HOME page) under related links---jobs

I have found no existing job exits.

B)

oracle:>ps -ef | grep rman
oracle 52554 25546 1 08:52:28 pts/0 0:00 grep rman
oracle:>ps -ef | grep exp
oracle 52556 25546 0 08:52:38 pts/0 0:00 grep exp

C)

am getting follwoing values: (for both show all and list backup)
Please note: I am connecting to the server remotely via ssh.


oracle:>rman

Recovery Manager: Release 10.1.0.4.0 - 64bit Production

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

RMAN> show all

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "all, archivelog, auxiliary, auxname, backup, channel, controlfile, datafile, device, default, exclude, maxsetsize, retention, snapshot"
RMAN-01008: the bad identifier was: a
RMAN-01007: at line 1 column 6 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, upgrade, unregister, validate"
RMAN-01007: at line 1 column 7 file: standard input

********************************************************************************
****************************************************************************
RMAN> list backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "archivelog, as, backup, backupset, channel, check, copy, copies, controlfilecopy, cumulative, current, database, datafile, datafilecopy, device, diskratio, db_recovery_file_dest, db_file_name_convert, duration, filesperset, for, format, full, force, incremental, keep, (, maxsetsize, nochecksum, noexclude, nokeep, not, proxy, pool, reuse, recovery, skip, spfile, setsize, tablespace, tag, validate"
RMAN-01008: the bad identifielist
RMAN-01007: at line 1 column 8 file: standard input

D)

As a Oracle user i am getting nothing "crontab -l"
Oracle>crontab -l
>

but as a root user I am getting following values:

# crontab -l
# @(#)08 1.15.1.3 src/bos/usr/sbin/cron/root, cmdcntl, bos520 2/11/94 17:19:47
# IBM_PROLOG_BEGIN_TAG
# This is an automatically generated prolog.
#
# bos520 src/bos/usr/sbin/cron/root 1.15.1.3
#
# Licensed Materials - Property of IBM
#
# (C) COPYRIGHT International Business Machines Corp. 1989,1994
# All Rights Reserved
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
# IBM_PROLOG_END_TAG
#
# COMPONENT_NAME: (CMDCNTL) commands needed for basic system needs
#
# FUNCTIONS:
#
# ORIGINS: 27
#
# (C) COPYRIGHT International Business Machines Corp. 1989,1994
# All Rights Reserved
# Licensed Materials - Property of IBM
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
#0 3 * * * /usr/sbin/skulker
#45 2 * * 0 /usr/lib/spell/compress
#
0 01 * * * su - oracle -c "/u01/ora10g/xyz/db/bin/schemaname_export"
45 23 * * * ulimit 5000; /usr/lib/smdemon.cleanu > /dev/null
0 11 * * * /usr/bin/errclear -d S,O 30
0 15 * * * /usr/lib/ras/dumpcheck >/dev/null 2>&1
# SSA warning : Deleting the next two lines may cause errors in redundant
# SSA warning : hardware to go undetected.
01 5 * * * /usr/lpp/diagnostics/bin/run_ssa_ela 1>/dev/null 2>/dev/null
0 * * * * /usr/lpp/diagnostics/bin/run_ssa_healthcheck 1>/dev/null 2>/dev/null
# SSA warning : Deleting the next line may allow enclosure hardware errors to go undetected
30 * * * * /usr/lpp/diagnostics/bin/run_ssa_encl_healthcheck 1>/dev/null 2>/dev/null
# SSA warning : Deleting the next line may allow link speed exceptions to go undetected
30 4 * * * /usr/lpp/diagnostics/bin/run_ssa_link_speed 1>/dev/null 2>/dev/null
#


Does all above mean Oracle Backup does not exits?

I wanted to diagnose whole process to confirm Oracle backup is exits or not.

I will appreciate any feed back. thanks

Regards,
Dawar

what is your conclusion from the below command?

RMAN> list backup;
Ø
1 2202 26572119 07-DEC-05 26572549 07-DEC-05

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
53 496M DISK 00:00:17 07-DEC-05
BP Key: 88 Status: AVAILABLE Compressed: NO Tag: TAG20051207T151746
Piece Name: /u02/backup_3th5n7ff_1_1

List of Archived Logs in backup set 53
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2258 26595963 07-DEC-05 26596386 07-DEC-05
1 2259 26596386 07-DEC-05 26596809 07-DEC-05
1 2260 26596809 07-DEC-05 26597232 07-DEC-05
1 2261 26597232 07-DEC-05 26597654 07-DEC-05
1 2262 26597654 07-DEC-05 26598076 07-DEC-05
………………………………………………………………
………………………………………………………………
1 2310 26618081 07-DEC-05 26618505 07-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
54 Full 3M DISK 00:00:01 07-DEC-05
BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20051207T151938
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576428663_1sgvjckr_.bkp
Controlfile Included: Ckp SCN: 26639775 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
55 35M DISK 00:00:02 07-DEC-05
BP Key: 90 Status: AVAILABLE Compressed: NO Tag: TAG20051207T152939
Piece Name: /u02/backup_3vh5n833_1_1

List of Archived Logs in backup set 55
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2311 26618505 07-DEC-05 26618928 07-DEC-05
1 2312 26618928 07-DEC-05 26619351 07-DEC-05
1 2313 26619351 07-DEC-05 26639776 07-DEC-05
1 2314 26639776 07-DEC-05 26644839 07-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56 Full 3M DISK 00:00:01 07-DEC-05
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20051207T152941
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576430181_1sgw361k_.bkp
Controlfile Included: Ckp SCN: 26644846 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57 Full 3M DISK 00:00:00 07-DEC-05
BP Key: 92 Status: AVAILABLE Compressed: NO Tag: TAG20051207T155644
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576431804_1sgxowts_.bkp
Controlfile Included: Ckp SCN: 26646831 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
58 Full 3M DISK 00:00:00 07-DEC-05
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20051207T173016
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2005_12_07/o1_mf_s_576437416_1sh358jd_.bkp
Controlfile Included: Ckp SCN: 26650878 Ckp time: 07-DEC-05
SPFILE Included: Modification time: 22-NOV-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
59 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 94 Status: AVAILABLE Compressed: NO Tag: TAG20060104T093050
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578827850_1vr1lcnm_.bkp
Controlfile Included: Ckp SCN: 42806953 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094251
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828571_1vr28vw9_.bkp
Controlfile Included: Ckp SCN: 42815624 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
61 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 96 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094533
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828733_1vr2fy2o_.bkp
Controlfile Included: Ckp SCN: 42816026 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 97 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094627
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828787_1vr2hmv0_.bkp
Controlfile Included: Ckp SCN: 42816193 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
63 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 98 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094632
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828792_1vr2hrsd_.bkp
Controlfile Included: Ckp SCN: 42816200 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
64 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 99 Status: AVAILABLE Compressed: NO Tag: TAG20060104T094638
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578828798_1vr2hydt_.bkp
Controlfile Included: Ckp SCN: 42816207 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65 Full 3M DISK 00:00:01 04-JAN-06
BP Key: 100 Status: AVAILABLE Compressed: NO Tag: TAG20060104T134826
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843306_1vrjocjh_.bkp
Controlfile Included: Ckp SCN: 42929089 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 101 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135009
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843409_1vrjrkjw_.bkp
Controlfile Included: Ckp SCN: 42929429 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
67 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 102 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135034
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843434_1vrjsb8r_.bkp
Controlfile Included: Ckp SCN: 42929518 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 103 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135126
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843486_1vrjtyrr_.bkp
Controlfile Included: Ckp SCN: 42929884 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 104 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135155
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843515_1vrjvvvy_.bkp
Controlfile Included: Ckp SCN: 42929918 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 105 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135205
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843525_1vrjw5lb_.bkp
Controlfile Included: Ckp SCN: 42929929 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 3M DISK 00:00:00 04-JAN-06
BP Key: 106 Status: AVAILABLE Compressed: NO Tag: TAG20060104T135209
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578843529_1vrjw9fv_.bkp
Controlfile Included: Ckp SCN: 42929934 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 3M DISK 00:00:02 04-JAN-06
BP Key: 107 Status: AVAILABLE Compressed: NO Tag: TAG20060104T142508
Piece Name: /u01/ora10g/xyz/db/flash_recovery_area/SIDNAME/autobackup/2006_01_04/o1_mf_s_578845508_1vrlt664_.bkp
Controlfile Included: Ckp SCN: 42936229 Ckp time: 04-JAN-06
SPFILE Included: Modification time: 29-DEC-05

We are on 9.2.0.5 and run in noarchivelog mode, (much to my chagrin). We have a 1 TB data warehouse for which I am responsible as the Sr. DBA. We are currently migrating (in stages)from one storage array to another, and we already have several dozen files moved over to the new array. We had an incident a few days ago whereby the power supply on the new array went goofy, and needed to be replaced. I was on vacation, and my boss called me and said he had shut down the db so they could swap the power supply and reboot the server. After they did that, he could not start up the database because there were controlfile version errors. Turns out the unix folks had a system startup script that was still pointing to the old array for several mount points. So, since we had copies of the controlfiles on the old and new arrays (2 copies each) they did not match.

I was on vacation and my boss called me, and I told him to make sure the db was totally shut down, then go to the init.ora file and comment out the controlfile entries for the OLD outdated versions of the controlfiles, then do a startup mount, and if there were no errors, then alter the database open. This worked just fine.

The problem is, I want to "reclaim" the contolfile copies that are not currently active, because we now only have 2 good copies, and they are on the same set of disks. Is there a way to reclaim them without having to shut the database down and copy the current files over the outdated copies (I know the init.ora entries need to be uncommented in either case)? We have very few opportunities to shut down. I could not think of a way or find any syntax for this, but you have a lot of good tricks in your bag.

Well, it was worth a shot, I'm always learning new tricks out here. I have a cron job that backs up the controlfile every night, but I always do a manual controlfile backup after changing database structure during the day.

Hi Tom,
Suppose i lost my all copies of controlfile. And if my database is crashed, what will be solution to recover by database. I don't have any controlfiles.

Tom,

I do a full hot backup of oracle database inclusive control files and archive logs each night. In addition I switch and copy to another location archive log every 30 min. thinking that if the prod database blow up I lost max. 30 min. But what is about control file. Should I copy control file each 30 min as well or is it possible to recreate the database using backup control file and then apply archivelogs that are more recent then controlfile?

Hi Tom,
I know that before starting the cold file system backup our database must be closed. If we are taking cold file system backup when our database is open then our backup will be inconsistent. But if our database is open and there is zero activity in database(not a single transaction is going on) and suppose we are taking a file system backup(copy all the datafile with OS command), is it consistent or not. If not why?

Due to some hardware errors on the disk subsystem, Oracle automatically took 4 datafiles offline. The alert log said 'Automatically offlining ...'.

The hardware errors were fixed and the instance was restarted and mounted.

When I tried to do recover datafile '...', it said 'File nn cannot be read at this time'.

I did ALTER DATABASE DATAFILE '...' online;
recover datafile '...'
and everything was fine.

But before I did the DATAFILE ONLINE, select name,status from v$datafile showed RECOVER for that datafile.

Why? Shouldn't it say OFFLINE to indicate that the file is offline? It said RECOVER but RECOVER DATAFILE complained that it couldn't read the file. Isn't this confusing?

Comments? Thanks

Why is th

Tom, what parameter control the number of archive logs generated each day?

I have a database in test and production with the same transaction volume. But production has more archive logs files than test.

Thanks.

Hi tom,

Thanks in advance you always are very helpful for me. I've a question for you. Supose that i'm doing my first full backup of my database with RMAN (including controlfile and spfile) and i don't have the CONTROLFILE AUTOBACKUP set to ON as you can see bellow:

RMAN> list backup;


RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backups/OEMREP/%U.bck';
RMAN configuration has no stored or default parameters
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_OEMREP.f'; # default

RMAN> run {
shutdown immediate;
startup mount;
backup full database;
}2> 3> 4> 5>

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 236000320 bytes

Fixed Size 451648 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

Starting backup at 24-MAY-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/OEMREP/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/OEMREP/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/OEMREP/example01.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/OEMREP/xdb01.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/OEMREP/oem_repository.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/OEMREP/indx01.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/OEMREP/users01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/OEMREP/drsys01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/OEMREP/odm01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/OEMREP/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-06
channel ORA_DISK_1: finished piece 1 at 24-MAY-06
piece handle=/home/oracle/backups/OEMREP/08hjtf70_1_1.bck comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 24-MAY-06

RMAN> list backup;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 418M DISK 00:00:59 24-MAY-06
BP Key: 3 Status: AVAILABLE Tag: TAG20060524T215544
Piece Name: /home/oracle/backups/OEMREP/08hjtf70_1_1.bck
SPFILE Included: Modification time: 17-MAY-06
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/system01.dbf
2 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/undotbs01.dbf
3 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/drsys01.dbf
4 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/example01.dbf
5 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/indx01.dbf
6 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/odm01.dbf
7 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/tools01.dbf
8 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/users01.dbf
9 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/xdb01.dbf
10 Full 226538 24-MAY-06 /u01/app/oracle/oradata/OEMREP/oem_repository.dbf


Now, if a disaster happens and i lose everything datafiles, controlfiles and spfile, i only have the backupset and i try to recover the database with it and i do the following:

RMAN> run {
2> startup nomount;
3> restore spfile to '/u01/app/oracle/product/9.2.0/dbs/spfileOEMREP.ora' from '/home/oracle/backups/OEMREP/08hjtf70_1_1.bck';
4> startup nomount force;
5> restore controlfile from '/home/oracle/backups/OEMREP/08hjtf70_1_1.bck';
6> alter database mount;
7> }

connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/9.2.0/dbs/initOEMREP.ora'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area 97588468 bytes

Fixed Size 451828 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

Starting restore at 24-MAY-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/backups/OEMREP/08hjtf70_1_1.bck
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 24-MAY-06

Oracle instance started

Total System Global Area 236000320 bytes

Fixed Size 451648 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

Starting restore at 24-MAY-06

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/OEMREP/control01.ctl
output filename=/u01/app/oracle/oradata/OEMREP/control02.ctl
output filename=/u01/app/oracle/oradata/OEMREP/control03.ctl
Finished restore at 24-MAY-06

database mounted

And now i can´t recover the datafiles that i know that they are in the backupset file because the controlfile doesn´t have the reference of the latest (the only one) backupset.

RMAN> list backup;


RMAN>

Is possible to continue the recover of the datafiles in this situation?

Thanks a lot

Daniel

do we need to take back up of these .
in case of 1.cold backup
2.hot backup

What exactly do you mean by true temporay tablespace.
Create temporary tablespace xx tempfile '../../' will do.

yes , my doubt is cleared.

Tom,

Can you recover a database if you have everything except a current control file that was taken with the other files? Can we use and older version of the control file? I did not see this scenario in the Backup & Recovery Admin guide. Thank you.

Thanks, that seems pretty easy....almost too easy....

I just don't understand how that works if you are forced to create the control file from scratch. They tell the database where the other files are, so if you create a new one, how does it know where to look for the others? Does the "reuse database" clause tell Oracle to search the file system or something?

HI, Tom,

This was wrongly posted in another thread previously, so I re-posted here.

I am confused by the following two DB recovery tests:
In both tests, I prepare the db as:
SQL> alter database backup controlfile to trace as 'c:\tt.sql' reuse;

Database altered.

SQL> alter database backup controlfile to 'c:\cf.bak' reuse;

Database altered.

SQL> alter tablespace my_ts add datafile 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF' size 
128k;

Tablespace altered.

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

Delete all the control files and the newly added datafile MYTS2.DBF.

Test 1: Recover DB using binary control file backup "cf.bak"
  I copy cf.bak to the original control file location and rename as indicated in 
init.ora.

and run the following:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' mount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile
ORA-00279: change 1092287 generated at 10/25/2006 10:19:16 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092287 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF'

ORA-01112: media recovery not started


SQL> alter database create datafile 6 as 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF';

Database altered.

SQL> recover database using backup controlfile
ORA-00279: change 1092393 generated at 10/25/2006 14:49:42 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092393 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that I have also restored the newly added datafile during the 
recovery process.

Test 2: Recover DB using traced output to re-create control file:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' nomount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 100
  5      MAXINSTANCES 16
  6      MAXLOGHISTORY 1815
  7  LOGFILE
  8    GROUP 1 'C:\ORACLE\ORADATA\MYDB\REDO01.LOG'  SIZE 10M,
  9    GROUP 2 'C:\ORACLE\ORADATA\MYDB\REDO02.LOG'  SIZE 10M,
 10    GROUP 3 'C:\ORACLE\ORADATA\MYDB\REDO03.LOG'  SIZE 10M
 11  DATAFILE
 12    'C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF',
 13    'C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF',
 14    'C:\ORACLE\ORADATA\MYDB\USERS_01.DBF',
 15    'C:\ORACLE\ORADATA\MYDB\MYTS.DBF',
 16    'C:\ORACLE\ORADATA\MYDB\TEST.DBF'
 17  CHARACTER SET US7ASCII;

Control file created.
SQL> recover database using backup controlfile
ORA-00279: change 1092488 generated at 10/25/2006 15:19:49 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092488 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that my newly added datafile is gone.

My question is, why the second test omits the new datafile? Is there any way for 
the second test to perform correctly to recover the newly added datafile?

Thank you 
 

Hi Tom,

I have a rman backup of database which is in archive mode (9207) using catalog.  I was able to restore the databsae using that backup.  When I tried to restore it again, I was not able to open the database. Here is the error:

SQL> alter database open resetlogs;
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: '/oracle3/oradata/cbdev/system01.dbf'


Since I only concerned one tablespace users, is it possible that I can create new database with the same database name as the one in rman backup, then try to restore user tablespace, So I don't need to restore system datafile in this case? It seems that it is not possible, since the users tablespace is not in controlfile or system tablespace of newly created database.  Thanks so much for your help.

Sean 

Hi Tom,

I'm going to be creating some new databases soon and I'd like to take advantage of flashback. I have some questions about how this new feature effects backups, or does it? Since you put all your files in your flashback recovery area, do you just back them up as your normally would from there? Isn't putting all your database files in one location like that bad? What if that disk blows up? Without flashback, you could disperse your files all over the place, possibly only losing one important file instead of all of them?

The only thing I know for sure is that flashback doesn't replace backups, because it's logical. Thanks.

Hi, Tom-
When we had an NT platform I quickly and easily set up real-time archivelog duplexing to another server. We are now on Solaris and need to get archivelog duplexing set up but our Solaris system administrator says that due to SOX and security requirements we can't enable NFS between two machines. Is there anyway to get this done on Solaris without setting up a standby db and without using NFS? I do plan to setup a standby db in the next few months, but we want to duplex those arch logs in the meantime.
Thanks in advance!

Hi, Tom-
I'm still getting resistance from the sysadmins. They say we are following security doc GSD331 which says "No NFS". So they are wanting to use SSH instead, but are not sure that the SSH tunnel can stay open indefinitely.
I need a "shared disk" for archlog duplexing now, and for the standby db later. What do most oracle-on-unix shops use for this--NFS? If so maybe I can talk them into an exception to the security doc.
thanks!
--Laurie

Hi,

Can you please tell me from where i can get oracle secure backup proper documents for hp-unix 11.23

Can use the DB server as admnistrator server for oracle secure backup


sunil

Hi,
Just wanted to follow up and let you know that our system admins are now looking into securing NFS.....so.....Thank you, Tom!
--Laurie M.

Hi Tom,

Pls let me know that oracle secure backup is available for HP-UX B.11.23 U ia64 3855819696 unlimited-user license OR not.


Sunil

Tom,

After restoring database using rman I found that there are no temporary datafiles in the database. Should I add tempfiles manually? Should adding temporary file be the very next step after restoring whole database (I did not do this and after five minutes got user call about application error regarding temp tablespace). Why the need to add temporary datafiles is not documanted by Oracle for recovery process?

Hi Tom,

After a point in time recovery of a tablespace,after issuing a select * on table of that tablespace, data are getting listed but on issuing select count(*) on the same table i get the following errror.

ERROR at line 1:

ORA-00376: file 9 cannot be read at this time
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/app/oracle/product/9.2.0/dbs/MISSING00009'

Can you guide me how to solve this error.

Hi Tom,

I have few questions regarding SCN and the control file

SCN on the datafile header pertains to the last transaction which modified it. So, i assume that every datafile may have different SCNs.
Would the latest SCNs of each datafile be recorded in the control file?

When the instance is shutdown, would the SCNs of the datafiles be made in sync and the control files updated accordingly?

Could you please shed some light on this?

Thanks a lot in advance,

Thank you very much.

How about the undo datafile? What SCN would be recorded in its header? The SCN corresponding to the latest checkpoint - on all undo datafiles?

Tom,

Oracle DB version: 10.2.0.1.0

We have Internal Tape Drive attached with our AIX box.

http://publib.boulder.ibm.com/infocenter/eserver/v1r3s/index.jsp?topic=/iphcd/fc6120.htm

Currently we are doing disk backup as below:

here is the script to perform backup:

. $HOME/.profile
export ORACLE_SID=sid
rman nocatalog << EOF
connect target
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;
configure controlfile autobackup on;
backup as compressed backupset database plus archivelog delete input;
delete noprompt obsolete;
exit
EOF


Now we want to backup on tape.

First, how could I read tape from the command prompt?
And how will I change my script for the tape backup.

cheers,
Dawar

Do I need to install or configure Oracle Secure backup first?

We have six database servers.

Do I need to install Oracle Secure Backup on each server?

OR

I need to install Oracle Secure Backup on only Adminstrative box. If yes, Do I need to install firts same version of Oracle DB on Adminstartive box?

http://download-east.oracle.com/docs/cd/B19306_01/install.102/b14235/obins_int.htm#BABJEDBJ

Looks like you need to install this on each box that will be in your administrative domain, including clients and media servers.

I do not see any reference to housing a database or database software on the administrative box, so it appears to be self contained.

URL should be:

http://download-east.oracle.com/docs/cd/B19306_01/install.102/b14235/obins_int.htm#BABJEDBJ

Tom, it appears the web site is transforming URLs. In the URL above, it transfomed "#B" (pound sign then B) to the bold HTML tag. I'll also put this under the "New AskTom" thread so you can have it in one placae.

Looks like it did it again. Enough said. :)

Some one informed that "OSB hasn't yet been released on AIX "

Is it correct?

My OS is AIX 5.2

Great, now everything is in bold. :)

Dawar, you can just browse to www.oracle.com go to Downloads, then Secure Backup. It lists all the operating systems supported.

From the site, it appears that:

Microsoft Windows (32-bit)
Microsoft Windows (Itanium, x64)
Linux x86
Linux x86-64
Linux Itanium
Solaris SPARC (64-bit)
HP-UX

are supported.

You are correct.

It is not supported yet.

So what should I do in my case?

My backup script is above.
I have attached tape drive with my system.
How to add tape drive in my backup script?

cheers,
Dawar

Proabbly we alreday have media manager.
I need to check with IBM (our vendor of AIX/IBM box).

If I have a media manager,

How the script will be change to write backup on tape instead on disk?

Here is my current backup script writing on to the disk:

. $HOME/.profile
export ORACLE_SID=sid
rman nocatalog << EOF
connect target
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;
configure controlfile autobackup on;
backup as compressed backupset database plus archivelog delete input;
delete noprompt obsolete;
exit
EOF

cheers,
Dawar

Done.

Done?

OS: AIX 5.2
DB: 10.2

we do not have media manager but tape attached to the box.
#lsdev -Cc tape
>rmt0 Available 09-08-00-0,0 LVD SCSI Tape Drive

I am thinking to backup as above on disk and copy to the tape.

How will it work?

Regards,
Dawar

Hi Tom,

Dut to some file system issue. We have lost all the control files,init files. only what we have is dbf files.

Now I just need to understand is there any way to recover the data from the dbf files? even if no 100% but still some thing.

Scenario:

- Cold database backup taken at Server #1. All the datafiles, log files, and control files are in the D drive (Windows).

- Try to restore the cold backup taken above to Server #2. However, server #2 only has a C drive.

What steps should be taken to restore the database?

Metalink Note 16530.1
Control file
============

A control file reflects the structure of a database at particular points
in time. It contains the checkpoint information, names of log files
and data files, header information of the files and log sequence number,
which is very important for recovery purposes. The recovery is done
only by applying the log files whose sequence number is greater than
log sequence number in the control file.

Tom, Please comment on the bold letters.

It means you cannot apply archive logs that have an SCN older than what is in the control files. Imagine you have a database whose SCN is 1000. You cannot apply archive logs during a recovery with an SCN lower, as the control file, and the datafiles if the database is successfully opened, already has the information newer than that.

But the control file always have SCN greater than the
SCNs of archived log files needed for recovery.

If I have a cold backup of a database, all control,
online redo, datafiles are consistent as of time T1,
I also have 15 archived log files from T1 to T5.
now if I restore the database by restoring all datafiles,
online redo files( I know they shouldnt be backedup) and
the control files as of T1 and rollforward by
1-STARTUP MOUNT
2-RECOVER DATABASE USING BACKUP CONTROLFILE;
3-..... accepting suggested archived log files
..... till archived log file no 7.....
4- Type CANCEL and press ENTER when prompted for archived
log file no. 8.
5- ALTER DATABASE OPEN

Will the database be consistent as of any time between
T1 and T5? Actually what I wanted to ask is that if the database is in consistent state after application of each
archived log file?

I wrote my comment above in a horrible fashion. I meant that if the database is open, then everything is in sync and you cannot apply older archives; there's no need to do so. You need to be in a mounted state to do so.

What is difference between
1-
RECOVER DATABASE USING BACKUP CONTROLFILE;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

AND

2-
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

1. Recovers database by using all archive logs in existence.
2. Allows you to perform incomplete recovery, for example, recovering just to the point before Mr. Dud truncated that important table (on accident of course).

But in 1, I can always type CANCEL and press ENTER, to
do incomplete recovery

Sir Tom kyte, please tell me what is difference between
1-
RECOVER DATABASE USING BACKUP CONTROLFILE;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER

AND

2-
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
.... pressing ENTER for some suggested files....
.... type CANCEL and press ENTER


Thanks.

Hi Tom ,

Thanks for your time , I ENJOY oracle now.

just to let you know
"Just like I won't reboot my solaris box remotely (been burned by that before -- "fschk failed,
please hit ctl-d to continue", problem is no one is there to hit ctl-d, I don't like to
shutdown/startup unattended. "

if you have ALOM ( all lights out module ) card on sun boxes
like I do , you could do the maintenance yourself via a telnet screen.

thanks

IF (1) and (2) are same then
why when I do option (1) and then
ALTER DATABASE OPEN READ ONLY, I receive
error "system01.dbf file needs media recovery"
It never erros during optin (2), I have tried this
several times on my test database.