This content has been marked as final. Show 10 replies
An rman backup? Using the supplied backup batch or shell script file? Or just a file copy to another folder (or drive? or filesystem? (win|linux ?))
backup of data files and controlfile
Ok, that's a cold backup. If the copied files are restored, database recovery is not needed when using the backup files.
db was shut...properly
Not a big deal. For hot backups, the database has to be in archivelog mode and the important part is to also get all archivelog files created after backup mode was started, not the onlike redo files. Archivelog files are created from redo. When doing a restore generally you don't want to mess with redo- could be some transaction details in the online redo that didn't get written to archivelog files yet, the instance might need those bits in a recovery scenario.
Shutdown the database. Copy the backup files over the datafiles/controlfile(s). Startup. You're Done.
to restore the db back..
For Windows, don't stop the database service to shutdown, just use sqlplus and connect /as sysdba; shutdown immediate; or you'll see an ora-12560 (?) error when trying to connect. The windows service must be running and the instance can be shutdown and started up without touching the host services.
at cold backup we are supposed to take online redo log files backup.. in hury forgot that redologfiles are in flash directory..!!
I have missed..that..so after machine is formatted..and since i dont have the redolog files..
the database wont even mount...
So i assume redo log files are very important..
Hope the method you mentioned works..but i feel it wont..
Will try tomorrow and get back..
When i try to open the database which is in non archive mode..i get the communicaton error ..
database donot even mount..
I do have the dataf iles and control file..
If what you mentioned works nothing like ..it..
Thanks and regards
Yes that situation often results in missing critical bits. And spelling/grammar mistakes too ;)
in hury forgot
That isn't really a big deal at all with a cold backup, as long as the datafiles were backed up after a proper (not crash, not shutdown abort) close of all database datafiles.
i dont have the redolog files
A startup nomount with a controlfile create might fix that. And redo files can be taken care of in that step as well, so having the controlfile backup isn't necessary either.
database donot even mount.
A controlfile create is a fairly trivial task, for either a cold backup or hot backup scenario. Well, its an easy task after you've done the process a few times.
If you have a healthy oracle instance running any place else, preferably at 11gR2, or even a 10g instance can work as an example, get a system connection and do an `alter database backup controlfile to trace;` and the instance will create a new trace file with all SQL commands needed to rebuild the controlfile in the instance trace directory.
Have a look at the trace file, a `show parameter diag` reveals the diagnostic_dest parameter, look for the "trace" directory under that folder, for the latest *trc file. In 10g its `show parameter dump` and the trace file goes straight to the user_dump_dest directory, no digging required.
when i am trying to restore the database via controlfile backup .. the redo log file being not present it says file not found..
how can i get around with it..
since i dont have the older redo log file....
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
SIZE 50M BLOCKSIZE 512,
SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
CHARACTER SET AL32UTF8
; i dont have those 2 log files.... in the destination mentioned
how do i get around with this..
Use the Set #2. RESETLOGS case from the tracefile
Remove/comment the "RECOVER DATABASE USING BACKUP CONTROLFILE" command
Here is sample, as its from linux you will find the directory strucure little different.
-- Set #2. RESETLOGS case
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
<font color="red">-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged. </font>
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
CREATE CONTROLFILE REUSE DATABASE ORCL RESETLOGS ARCHIVELOG
GROUP 1 '/u01/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
CHARACTER SET WE8MSWIN1252
<font color=red>--RECOVER DATABASE USING BACKUP CONTROLFILE</font>
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
Edited by: yoonus on Feb 28, 2013 4:57 AM
Edited by: yoonus on Feb 28, 2013 5:03 AM
Edited by: yoonus on Feb 28, 2013 5:07 AM
Doesn't matter, the create controlfile statement specifies redo filenames- if they don't exist, the instance might gripe about it but they will get created when the database starts saving redo information.
i dont have those 2 log files.... in the destination mentioned
And if you don't like the names, fix it, i.e.:
You'll have to make the 'redo' folder, the complete <path> part has to be a valid directory. The "out of the box" setup in XE places redo files in the flash recovery area. And FRA has an arbitrary size limit, 10GB, I think. Better to point redo files to a different folder, not in the FRA location.
-- change: ... group 1 '<path>\O1_MF_1_80L7C259_.LOG' size ... , group 2 '<path>\O1_MF_2_80L7C375_.LOG' size ... -- to 'C:\oraclexe\app\oracle\oradata\XE\redo\redo01.dbf' size ... , 'C:\oraclexe\app\oracle\oradata\XE\redo\redo02.dbf' size ... -- if you want three redo files that's fine too- add <comma> group 3 ... , group 3 'C:\oraclexe\app\oracle\oradata\XE\redo\redo03.dbf' size ...
Redo files can also have multiple members, for additional protection. If at least one member of a redo group is healthy the database will continue to run. Specify additional members with:
I always name the redo files with a .dbf extension, at one time there was a system administrator that got a bit too aggressive seeking log files that "needed to be cleaned up to save some space".
group 1 ( '<path1>\redo01.dbf', '<path2>\redo01.dbf' ) size ... , group 2 ( '<path1>\redo02.dbf', '<path2>\redo02.dbf' ) size ... , group 3 ( '<path1>\redo03.dbf', '<path2>\redo03.dbf' ) size ...
Good for him, he freed up a few MB of disk space.
Bad for me, the instance crashed as soon as it tried writing to a missing redo file.