•You must have the backups of all datafiles of the recovery and auxiliary tablepspace set and your desired TSPITR time is after the time of the backups for those datafiles.
•You must have control file backup before the TSPITR time.
•The control file backup must be a binary one. That is ALTER DATABASE BACKUP CONTROLFILE TO '';
•Ensure that all files constituting the recovery set tablespaces are in the recovery set on the auxiliary database; otherwise, the export phase during tablespace transport fails.
•Enough disk space must be available on the auxiliary host to accommodate the auxiliary database.
•Provide enough real memory to start the auxiliary instance.
•If the tablespace to be recovered has been renamed, ensure that the target SCN for TSPITR is after the time when the file was renamed. You cannot TSPITR a renamed tablespace to a point in time earlier than the rename. However, you can perform a DBPITR to an SCN before the rename. In this case, the tablespace reverts to its name as of the target SCN.
Showing posts with label Oracle Recovery. Show all posts
Showing posts with label Oracle Recovery. Show all posts
Wednesday, May 21, 2008
Recovering After the Loss of Online Redo Log Files
Case A: Losing a Member of a Multiplex online Redo log group.
------------------------------------------------------------------------
In this case your redo log file is multiplex that means you have more than one log member within log groups. Now if you have at least one redo log member of the group exist then database continues functional as usual. In alert log error message will be written.
In order to solve the problem,
i)Identify the redo log file that is invalid.
SQL> SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE
WHERE STATUS='INVALID';
GROUP# STATUS MEMBER
------- ----------- ---------------------
0002 INVALID /oracle/arju/redo02.log
ii)Drop the damaged member.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/arju/redo02.log/arju/redo02.log'
iii)Add a new member to the group.
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' TO GROUP 1; --FOr example to group 1.
If the file you want to add already exists, you must specify REUSE. For example:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' REUSE TO GROUP 2;
Case B:Recovering After the Loss of All Members of an Online Redo Log Group
------------------------------------------------------------------------------
If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
-If the group is inactive then it is not needed for crash recovery. Clear the archive or unarchived group.
-If the group is active then it is needed for crash recovery. In order to solve the problem attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
-If the group is current then it is the log that the database is currently writing. In order to solve the problem attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Troubleshooting:
--------------------
Determine whether the damaged group is active or inactive.
See the file name and corresponding group name of the log file.
SQL>SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
Determine which groups are active.
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
FROM V$LOG;
Losing an Inactive Online Redo Log Group
-----------------------------------------------
If the damaged redo log group is inactive and archived then issue,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
It will reinitialize the damaged log group.
b)If the damaged redo log group is inactive and not yet archived issue,
SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;
If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:
SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;
Immediately backup full database.
Losing an Active Online Redo Log Group
--------------------------------------------
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive.
And follow the procedure above as how you will respond to an inactive online redo log group.
If your ALTER SYSTEM CHEKPOINT statement failed then follow the following procedure.
a)If your database is in noarchivelog state restore database from whole consistent backup and perform incomplete recovery.
$scp .. /oracle/target/*
SQL>STARTUP MOUNT
SQL>RECOVER DATABASE UNTIL CANCEL
CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS
b)If you loss an active online redo log in archivelog mode then follow the following procedure.
ALTER DATABASE RENAME FILE '/oradata/trgt/redo01.log' TO '/tmp/redo01.log';
.
.
ALTER DATABASE OPEN RESETLOGS;
------------------------------------------------------------------------
In this case your redo log file is multiplex that means you have more than one log member within log groups. Now if you have at least one redo log member of the group exist then database continues functional as usual. In alert log error message will be written.
In order to solve the problem,
i)Identify the redo log file that is invalid.
SQL> SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE
WHERE STATUS='INVALID';
GROUP# STATUS MEMBER
------- ----------- ---------------------
0002 INVALID /oracle/arju/redo02.log
ii)Drop the damaged member.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/arju/redo02.log/arju/redo02.log'
iii)Add a new member to the group.
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' TO GROUP 1; --FOr example to group 1.
If the file you want to add already exists, you must specify REUSE. For example:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' REUSE TO GROUP 2;
Case B:Recovering After the Loss of All Members of an Online Redo Log Group
------------------------------------------------------------------------------
If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
-If the group is inactive then it is not needed for crash recovery. Clear the archive or unarchived group.
-If the group is active then it is needed for crash recovery. In order to solve the problem attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
-If the group is current then it is the log that the database is currently writing. In order to solve the problem attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Troubleshooting:
--------------------
Determine whether the damaged group is active or inactive.
See the file name and corresponding group name of the log file.
SQL>SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
Determine which groups are active.
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
FROM V$LOG;
Losing an Inactive Online Redo Log Group
-----------------------------------------------
If the damaged redo log group is inactive and archived then issue,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
It will reinitialize the damaged log group.
b)If the damaged redo log group is inactive and not yet archived issue,
SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;
If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:
SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;
Immediately backup full database.
Losing an Active Online Redo Log Group
--------------------------------------------
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive.
And follow the procedure above as how you will respond to an inactive online redo log group.
If your ALTER SYSTEM CHEKPOINT statement failed then follow the following procedure.
a)If your database is in noarchivelog state restore database from whole consistent backup and perform incomplete recovery.
$scp .. /oracle/target/*
SQL>STARTUP MOUNT
SQL>RECOVER DATABASE UNTIL CANCEL
CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS
b)If you loss an active online redo log in archivelog mode then follow the following procedure.
ALTER DATABASE RENAME FILE '/oradata/trgt/redo01.log' TO '/tmp/redo01.log';
.
.
ALTER DATABASE OPEN RESETLOGS;
User Managed Recover of Datafile when Backup is not Available
If a datafile is damaged and you don't have any backup of the datafile then still you can recover your data file if the following conditions met.
1)You have all archived redo logs available since the data file creation.
2)The control file contains the name of the lost file. That means either the control file is current or it is taken after the data file creation.
3)The datafile does not belong to SYSTEM tablespace.
To illustrate the scenario I will demonstrate the whole procedure with an example.
A)Add a datafile to a tablespace.
SQL> ALTER TABLESPACE USER_TBS ADD DATAFILE '/oradata2/data1/dbase/datafile03.dbf' SIZE 1M;
Tablespace altered.
B)Drop the datafile.
SQL> !rm /oradata2/data1/dbase/datafile03.dbf
C)Make the affect data file offline if the database is open.
SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/oradata2/data1/dbase/datafile03.dbf'
It can't rename as database currently using this file. So, make it offline.
SQL> alter database datafile '/oradata2/data1/dbase/datafile03.dbf' offline;
Database altered.
D) a new, empty datafile to replace a damaged datafile that has no corresponding backup. Here I craeted the damaged file 7 as to new location '/oradata1/arju/created_new.dbf'.
SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
Database altered.
E)Perform media recovery on the empty datafile.
SQL> RECOVER DATAFILE '/oradata1/arju/created_new.dbf';
Media recovery complete.
F)Make the datafile online.
SQL> alter database datafile '/oradata1/arju/created_new.dbf' ONLINE;
Database altered.
Now we will look at the scenario if while starting database it can't find datafile.
SQL> !rm /oradata1/arju/created_new.dbf
Shutdown immediate will fail as it can't find one datafile.
SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 88080424 bytes
Database Buffers 71303168 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
SQL> ALTER DATABASE CREATE DATAFILE '/oradata1/arju/created_new.dbf' AS '/oradata2/data1/dbase/datafile03.dbf';
Database altered.
SQL> RECOVER DATAFILE 7;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
1)You have all archived redo logs available since the data file creation.
2)The control file contains the name of the lost file. That means either the control file is current or it is taken after the data file creation.
3)The datafile does not belong to SYSTEM tablespace.
To illustrate the scenario I will demonstrate the whole procedure with an example.
A)Add a datafile to a tablespace.
SQL> ALTER TABLESPACE USER_TBS ADD DATAFILE '/oradata2/data1/dbase/datafile03.dbf' SIZE 1M;
Tablespace altered.
B)Drop the datafile.
SQL> !rm /oradata2/data1/dbase/datafile03.dbf
C)Make the affect data file offline if the database is open.
SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/oradata2/data1/dbase/datafile03.dbf'
It can't rename as database currently using this file. So, make it offline.
SQL> alter database datafile '/oradata2/data1/dbase/datafile03.dbf' offline;
Database altered.
D) a new, empty datafile to replace a damaged datafile that has no corresponding backup. Here I craeted the damaged file 7 as to new location '/oradata1/arju/created_new.dbf'.
SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
Database altered.
E)Perform media recovery on the empty datafile.
SQL> RECOVER DATAFILE '/oradata1/arju/created_new.dbf';
Media recovery complete.
F)Make the datafile online.
SQL> alter database datafile '/oradata1/arju/created_new.dbf' ONLINE;
Database altered.
Now we will look at the scenario if while starting database it can't find datafile.
SQL> !rm /oradata1/arju/created_new.dbf
Shutdown immediate will fail as it can't find one datafile.
SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 88080424 bytes
Database Buffers 71303168 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
SQL> ALTER DATABASE CREATE DATAFILE '/oradata1/arju/created_new.dbf' AS '/oradata2/data1/dbase/datafile03.dbf';
Database altered.
SQL> RECOVER DATAFILE 7;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
Tuesday, May 20, 2008
User manged Recovery After the Loss of Datafile
Losing Datafiles Whenever you are in NoArchivelog Mode:
----------------------------------------------------------------
If you are in noarchivelog mode and you loss any datafile then whether it is temporary or permanent media failure, the database will automatically shut down. If failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files. If you have permanent media failure then restore a whole database from a good backup. How to restore a database in archivelog is discussed on
http://arjudba.blogspot.com/2008/05/recovering-database-in-noarchivelog.html.
Losing Datafiles Whenever you are in Archivelog Mode:
-----------------------------------------------------------------
If the datafile that is lost is under SYSTEM tablespace or if it is a datafile contain active undo segments then database shuts down. If the failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files.
If the datafile that is lost in not under SYSTEM tablespace and not contain active undo segments then the affected datafile is gone to offline. The database remains open. In order to fix the problem take the affected tablespace offline and then recover the tablespace.
----------------------------------------------------------------
If you are in noarchivelog mode and you loss any datafile then whether it is temporary or permanent media failure, the database will automatically shut down. If failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files. If you have permanent media failure then restore a whole database from a good backup. How to restore a database in archivelog is discussed on
http://arjudba.blogspot.com/2008/05/recovering-database-in-noarchivelog.html.
Losing Datafiles Whenever you are in Archivelog Mode:
-----------------------------------------------------------------
If the datafile that is lost is under SYSTEM tablespace or if it is a datafile contain active undo segments then database shuts down. If the failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files.
If the datafile that is lost in not under SYSTEM tablespace and not contain active undo segments then the affected datafile is gone to offline. The database remains open. In order to fix the problem take the affected tablespace offline and then recover the tablespace.
User Managed database recovery when all database files lost.
In this example , you have only hot backup of the database. You have lost all datafiles , control files and redo log files. But you have archived redo log file. The backup of the database is taken as in example, User managed Hot Backup. Now you want to perform recovery of whole database. This is discussed in the following sections.
1)Restore the spfile: If you have backup then restore from that. If you don't have then I have shown different ways in order to create spfile in my topic. Search around my blog.
2)Start the database in nomount stage.
SQL>STARTUP NOMOUNT
3)Edit the spfile by Alter system if any modification needed.
show the control_files parameter in the spfile by,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata2/data1/dbase/control0
1.ctl, /oradata2/data1/dbase/c
ontrol02.ctl, /oradata2/data1/
dbase/control03.ctl
Copy the backup of control files to these location. If there is disk corruption then you can copy it in another place and change the spfile accordingly. Like I copied spfile to the location /oradata1/arju and then point that place in spfile.
SQL> !mkdir /oradata1/arju
Copy from backup location to /oradata1/arju/*
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl
Point spfile about the new location of control files.
SQL> alter system set control_files='/oradata1/arju/control01.ctl', '/oradata1/arju/control02.ctl' scope=spfile;
System altered.
Since it is static parameter so start the database with modified spfile in order to make affect.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl
4)Mount the database and see the location of the datafile.
SQL> alter database mount
2 ;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/7.dbf
6 rows selected.
5)Copy the backup datafile to these location. However you can change any location. Suppose here I am changing /oradata2/data1/dbase/users01.dbf to /oradata1/arju/user.dbf
SQL> !scp /oradata2/arju/system01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/sysaux01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/undotbs01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/users01.dbf /oradata1/arju/user.dbf
SQL> !scp /oradata2/arju/7.dbf /oradata2/7.dbf
SQL> ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' to '/oradata1/arju/user.dbf';
Database altered.
6)At time time restore is complete. So recovery is needed. As we have no online redo logs so need to do incomplete recovery.
SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123
Specify log: {=suggested | filename | AUTO | CANCEL}
-----Here I pressed ENTER.
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
If you don't have backup control file then you can do as follows,
CREATE CONTROLFILE REUSE DATABASE "DBASE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/data1/dbase/redo01.log' SIZE 4M,
GROUP 3 '/oradata2/data1/dbase/redo03.log' SIZE 50M
DATAFILE
'/oradata2/data1/dbase/system01.dbf',
'/oradata2/data1/dbase/undotbs01.dbf',
'/oradata2/data1/dbase/sysaux01.dbf',
'/oradata1/arju/user.dbf',
'/oradata2/7.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> recover database until cancel using BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123
Specify log: {=suggested | filename | AUTO | CANCEL}
---JUST I PRESS ENTER HERE
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
1)Restore the spfile: If you have backup then restore from that. If you don't have then I have shown different ways in order to create spfile in my topic. Search around my blog.
2)Start the database in nomount stage.
SQL>STARTUP NOMOUNT
3)Edit the spfile by Alter system if any modification needed.
show the control_files parameter in the spfile by,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata2/data1/dbase/control0
1.ctl, /oradata2/data1/dbase/c
ontrol02.ctl, /oradata2/data1/
dbase/control03.ctl
Copy the backup of control files to these location. If there is disk corruption then you can copy it in another place and change the spfile accordingly. Like I copied spfile to the location /oradata1/arju and then point that place in spfile.
SQL> !mkdir /oradata1/arju
Copy from backup location to /oradata1/arju/*
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl
Point spfile about the new location of control files.
SQL> alter system set control_files='/oradata1/arju/control01.ctl', '/oradata1/arju/control02.ctl' scope=spfile;
System altered.
Since it is static parameter so start the database with modified spfile in order to make affect.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl
4)Mount the database and see the location of the datafile.
SQL> alter database mount
2 ;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/7.dbf
6 rows selected.
5)Copy the backup datafile to these location. However you can change any location. Suppose here I am changing /oradata2/data1/dbase/users01.dbf to /oradata1/arju/user.dbf
SQL> !scp /oradata2/arju/system01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/sysaux01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/undotbs01.dbf /oradata2/data1/dbase
SQL> !scp /oradata2/arju/users01.dbf /oradata1/arju/user.dbf
SQL> !scp /oradata2/arju/7.dbf /oradata2/7.dbf
SQL> ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' to '/oradata1/arju/user.dbf';
Database altered.
6)At time time restore is complete. So recovery is needed. As we have no online redo logs so need to do incomplete recovery.
SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123
Specify log: {
-----Here I pressed ENTER.
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
If you don't have backup control file then you can do as follows,
CREATE CONTROLFILE REUSE DATABASE "DBASE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/data1/dbase/redo01.log' SIZE 4M,
GROUP 3 '/oradata2/data1/dbase/redo03.log' SIZE 50M
DATAFILE
'/oradata2/data1/dbase/system01.dbf',
'/oradata2/data1/dbase/undotbs01.dbf',
'/oradata2/data1/dbase/sysaux01.dbf',
'/oradata1/arju/user.dbf',
'/oradata2/7.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> recover database until cancel using BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123
Specify log: {
---JUST I PRESS ENTER HERE
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Sunday, May 18, 2008
User Managed Restore Operation in oracle Loss datafile loss controlfile loss spfile
Restore a datafile from backup is necessary whenever you disk is crushed or whenever there is user errors or someone has delete datafile. Remember that user managed restore operation is identical of the RMAN command RESTORE. If you like to restore any datafile then just copy it to the location in the parameter specified in control_files. If you like to restore any control file then just use copy command or any GUI tool in the destination which spfile or pfile points in control_files parameter.
So, in case of RMAN, RMAN>RESTORE datafile 2;
is same as of user managed $scp backup_of_the_file real_destination
In the following section I wrote in which circumstances what you have to do if you choose user managed recovery mechanism.
A)If you loss one or more datafile: If you lose one or more datafiles then copy the datafiles to the location where it was.(Equivalent to RMAN RESTORE) Or alternatively you can copy it in another location and point the control file to the new location by ALTER DATABASE RENAME FILE ' ' TO ' ';(Equivalent to RMAN SET NEWNAME ..) After copying recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file. Same as of RECOVER in RMAN.
B)If All copies of the current control file Lost: If all copies of your control files are lost then if you have backup of the control file then copy it to the location as it is located in CONTROL_FILES parameter in pfile/spfile. After copied to desired location you might have to open your database with RESETLOGS option.
If you don't have backup of control file then create a new one. This is described on
How to re-create Control file
C)If a copy of a multiplexed control file Lost: If one or two copies of control file are lost and in least one copy of the control file is intact then copy the intact control file to other locations as it was in CONTROL_FILES parameter inside pfile/spfile. If you have one good copy and you want to copy-this procedure is described on How to solve problem of inconsistent control file.If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location. Then, open the database.
D)If archived log lost that is needed for media recovery: If you have backup of archived log that is needed for recovery copy it to the location and perform recovery from there. The deleted archive logs that is not needed for media recovery(i.e the archived logs that were generated before taking backup) has no affect. But if the deleted archived log that is needed for media recovery and you have no backup then you must perform incomplete recovery (UNTIL SCN) and open the database with RESETLOGS open.
E)Spfile is Lost: If you loss your current spfile then using user managed recovery you can recover it. The detail procedure is discussed on What you will do if spfile lost Look at Solution of The problem Section
Related Documents:
-----------------------
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
So, in case of RMAN, RMAN>RESTORE datafile 2;
is same as of user managed $scp backup_of_the_file real_destination
In the following section I wrote in which circumstances what you have to do if you choose user managed recovery mechanism.
A)If you loss one or more datafile: If you lose one or more datafiles then copy the datafiles to the location where it was.(Equivalent to RMAN RESTORE) Or alternatively you can copy it in another location and point the control file to the new location by ALTER DATABASE RENAME FILE ' ' TO ' ';(Equivalent to RMAN SET NEWNAME ..) After copying recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file. Same as of RECOVER in RMAN.
B)If All copies of the current control file Lost: If all copies of your control files are lost then if you have backup of the control file then copy it to the location as it is located in CONTROL_FILES parameter in pfile/spfile. After copied to desired location you might have to open your database with RESETLOGS option.
If you don't have backup of control file then create a new one. This is described on
How to re-create Control file
C)If a copy of a multiplexed control file Lost: If one or two copies of control file are lost and in least one copy of the control file is intact then copy the intact control file to other locations as it was in CONTROL_FILES parameter inside pfile/spfile. If you have one good copy and you want to copy-this procedure is described on How to solve problem of inconsistent control file.If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location. Then, open the database.
D)If archived log lost that is needed for media recovery: If you have backup of archived log that is needed for recovery copy it to the location and perform recovery from there. The deleted archive logs that is not needed for media recovery(i.e the archived logs that were generated before taking backup) has no affect. But if the deleted archived log that is needed for media recovery and you have no backup then you must perform incomplete recovery (UNTIL SCN) and open the database with RESETLOGS open.
E)Spfile is Lost: If you loss your current spfile then using user managed recovery you can recover it. The detail procedure is discussed on What you will do if spfile lost Look at Solution of The problem Section
Related Documents:
-----------------------
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Thursday, May 15, 2008
Recover From Drop or Delete or Truncate Table in Oracle Database
It is common to hear that I accidentally deleted a table. Now how I can get back my table? Or I accidentally deleted some rows and committed. How I can get back my table to previous stage? Though it is takes almost no time to delete but sometimes it takes significant time to recover that table.
In the following section I will demonstrate possible solutions that we can have in order to recover a table.
1)If your database recyclebin parameter is on then if you dropped the table then it is best if you can recover it from recyclebin.
In order to know recylcebin on or off issue,
SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
However if you previously on and after dropping off it then still object will be stored in recyclebin unless the tablespace are in space pressure. Now in order to get back your the dropped table simply issuing,
SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
Simply an example.
SQL> drop table test2;
Table dropped.
SQL> flashback table test2 to before drop;
Flashback complete.
In order to get back more about flashback drop have a look at Flashback-Section Flashback Drop
2)Import the Table if you have good known logical backup.
If you previous export the table by exp or expdp and after that nothing is modified on the table then you can simply import your dropped table from the dump. Note that if a row in changed after taken dump that row will be not found after import. In order to know how export and import can be done please have a look at,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
3)If you erroneously updated a subset of data and you have higher UNDO RETENTION parameter settings that is your undo data is not aged out; then using flashback query restore old stage of the table. In order to know it have a look at,
Restore old data using flashback query
If you erroneously deleted data of the table and now you want to get back to a previous state of the whole table you can have a look at,
Flashback Table Part, of this link
4)Export Data up to a previous point-in-time.
You can set FLASHBACK_SCN or FLASHBACK_TIME with exp or expdp in order to export table before erroneous update or erroneous deletion of table data. After export test import it in another schema whether exported data is acceptable or not. If you are satisfied then import the data to your primary database schema. The detail procedure is discussed on Export Data up to a previous point-in-time
5)Restore and recover a subset of the database as a DUMMY database to a point-in-time. Then export the table data from that dummy database and import it into the primary database. This is very good option as only the dropped table goes back in time to before the drop.
In order to perform this operation you can have a look at RMAN DUPLICATION database in which rman will duplicate subset of database to a point-in-time of the erroneous operation of the table. And then from that database export the table. RMAN database duplication is discussed on
Performing Database Duplication.
After duplication export the data of the table by using exp or expdp utility and import it to primary database. Export and Import is discussed on,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
6)If you don't have enough undo to restore or don't have good logical backup or you don't have recylecbin parameter on(In case of drop table) or you have truncated the table but you have physical backup then you can do Tablespace point-in-time recovery. In order to know about TSPITR have a look at,
Performing TSPITR . Also have a look at its limitation performing performing. Limitations are described in Limitations of TSPITR
In case of TSPITR the entire tablespace will go back to a previous stage.
7)If you have flashback feature enabled then you can do flashback database to get back the entire database to a prior time.
Like, in mount stage run FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP-1/24/60*30; in order to get back whole database 30 minutes ago. After peforming flashback open the database READ ONLY , export the data , shutdown the database, and start the database with RESETLOGS option, import the data. The flashback database feature is discussed on,
Performing Flashback Database
8)If you are unable to perform any one of the above then you can use RMAN DBPITR to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time. DBPITR recovery is discussed on How to perform DBPITR
In the following section I will demonstrate possible solutions that we can have in order to recover a table.
1)If your database recyclebin parameter is on then if you dropped the table then it is best if you can recover it from recyclebin.
In order to know recylcebin on or off issue,
SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
However if you previously on and after dropping off it then still object will be stored in recyclebin unless the tablespace are in space pressure. Now in order to get back your the dropped table simply issuing,
SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
Simply an example.
SQL> drop table test2;
Table dropped.
SQL> flashback table test2 to before drop;
Flashback complete.
In order to get back more about flashback drop have a look at Flashback-Section Flashback Drop
2)Import the Table if you have good known logical backup.
If you previous export the table by exp or expdp and after that nothing is modified on the table then you can simply import your dropped table from the dump. Note that if a row in changed after taken dump that row will be not found after import. In order to know how export and import can be done please have a look at,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
3)If you erroneously updated a subset of data and you have higher UNDO RETENTION parameter settings that is your undo data is not aged out; then using flashback query restore old stage of the table. In order to know it have a look at,
Restore old data using flashback query
If you erroneously deleted data of the table and now you want to get back to a previous state of the whole table you can have a look at,
Flashback Table Part, of this link
4)Export Data up to a previous point-in-time.
You can set FLASHBACK_SCN or FLASHBACK_TIME with exp or expdp in order to export table before erroneous update or erroneous deletion of table data. After export test import it in another schema whether exported data is acceptable or not. If you are satisfied then import the data to your primary database schema. The detail procedure is discussed on Export Data up to a previous point-in-time
5)Restore and recover a subset of the database as a DUMMY database to a point-in-time. Then export the table data from that dummy database and import it into the primary database. This is very good option as only the dropped table goes back in time to before the drop.
In order to perform this operation you can have a look at RMAN DUPLICATION database in which rman will duplicate subset of database to a point-in-time of the erroneous operation of the table. And then from that database export the table. RMAN database duplication is discussed on
Performing Database Duplication.
After duplication export the data of the table by using exp or expdp utility and import it to primary database. Export and Import is discussed on,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
6)If you don't have enough undo to restore or don't have good logical backup or you don't have recylecbin parameter on(In case of drop table) or you have truncated the table but you have physical backup then you can do Tablespace point-in-time recovery. In order to know about TSPITR have a look at,
Performing TSPITR . Also have a look at its limitation performing performing. Limitations are described in Limitations of TSPITR
In case of TSPITR the entire tablespace will go back to a previous stage.
7)If you have flashback feature enabled then you can do flashback database to get back the entire database to a prior time.
Like, in mount stage run FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP-1/24/60*30; in order to get back whole database 30 minutes ago. After peforming flashback open the database READ ONLY , export the data , shutdown the database, and start the database with RESETLOGS option, import the data. The flashback database feature is discussed on,
Performing Flashback Database
8)If you are unable to perform any one of the above then you can use RMAN DBPITR to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time. DBPITR recovery is discussed on How to perform DBPITR
Tuesday, May 6, 2008
Restore and Recover database to a new host
In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1)In neptune machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00
3)Determine the DBID of source machine(Neptune here)
SQL> select dbid from v$database;
DBID
----------
3386862614
4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
database opened.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1)In neptune machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00
3)Determine the DBID of source machine(Neptune here)
SQL> select dbid from v$database;
DBID
----------
3386862614
4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
database opened.
Monday, May 5, 2008
Restore and Recover database in Noarchivelog Mode
Before proceed we need to know while restoring data in noarchivelog mode that,
1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.
2)Media recovery is not possible, because there are no archived logs.
3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.
In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.
A)Find the DBID of the Database from Backup Piece.
bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,
3386862614, MAXVALUE,
We got DBID here 3386862614. For more details please visit How to Discover DBID
B)Connect to target database with RMAN
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 12:50:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
C)Set the DBID.
RMAN> set dbid 3386862614
executing command: SET DBID
D)Start the database in nomount stage with dummy Parameter.
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 byte
E)Restore spfile
RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
F)Mount the Database.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 100667112 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
G)Restore controlfile.
RMAN> restore controlfile from autobackup;
Starting restore at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata2/flash_recovery_area
database name (or database unique name) used for search: DBASE1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata2/data1/dbase1/control01.ctl
output filename=/oradata2/data1/dbase1/control02.ctl
output filename=/oradata2/data1/dbase1/control03.ctl
Finished restore at 06-MAY-08
H)Mount the Database.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
I)Restore Database.
As we have flash recovery area configured so it automatically cataloged backup sets.
RMAN> restore database;
Starting restore at 06-MAY-08
Starting implicit crosscheck backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 06-MAY-08
Starting implicit crosscheck copy at 06-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
restoring datafile 00002 to /oradata2/data1/dbase1/undotbs01.dbf
restoring datafile 00003 to /oradata2/data1/dbase1/sysaux01.dbf
restoring datafile 00004 to /oradata2/data1/dbase1/users01.dbf
restoring datafile 00005 to /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
restoring datafile 00006 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
restoring datafile 00007 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
restoring datafile 00008 to /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19870: error reading backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19612: datafile 1 not restored due to missing or corrupt data
failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp tag=LEVEL_0_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-MAY-08
J)Recover Database:
If the current online logs contain all changes since the last backup , then you can run RECOVER DATABASE without specifying NOREDO. Otherwise you have to specify RECOVER DATABASE NOREDO.
RMAN> recover database;
Starting recover at 06-MAY-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 11 is already on disk as file /oradata2/data1/dbase1/redo01.log
archive log thread 1 sequence 12 is already on disk as file /oradata2/data1/dbase1/redo02.log
archive log thread 1 sequence 13 is already on disk as file /oradata2/data1/dbase1/redo03.log
archive log filename=/oradata2/data1/dbase1/redo01.log thread=1 sequence=11
archive log filename=/oradata2/data1/dbase1/redo02.log thread=1 sequence=12
archive log filename=/oradata2/data1/dbase1/redo03.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:12
Finished recover at 06-MAY-08
K)Open the database with RESETLOGS options.
RMAN> alter database open resetlogs;
database opened.
Related Documents:
-------------------
How to Discover or Find DBID
How to Restore Spfile from Backup
How to Restore Controlfile
1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.
2)Media recovery is not possible, because there are no archived logs.
3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.
In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.
A)Find the DBID of the Database from Backup Piece.
bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,
3386862614, MAXVALUE,
We got DBID here 3386862614. For more details please visit How to Discover DBID
B)Connect to target database with RMAN
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 12:50:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
C)Set the DBID.
RMAN> set dbid 3386862614
executing command: SET DBID
D)Start the database in nomount stage with dummy Parameter.
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 byte
E)Restore spfile
RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
F)Mount the Database.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 100667112 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
G)Restore controlfile.
RMAN> restore controlfile from autobackup;
Starting restore at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata2/flash_recovery_area
database name (or database unique name) used for search: DBASE1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata2/data1/dbase1/control01.ctl
output filename=/oradata2/data1/dbase1/control02.ctl
output filename=/oradata2/data1/dbase1/control03.ctl
Finished restore at 06-MAY-08
H)Mount the Database.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
I)Restore Database.
As we have flash recovery area configured so it automatically cataloged backup sets.
RMAN> restore database;
Starting restore at 06-MAY-08
Starting implicit crosscheck backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 06-MAY-08
Starting implicit crosscheck copy at 06-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
restoring datafile 00002 to /oradata2/data1/dbase1/undotbs01.dbf
restoring datafile 00003 to /oradata2/data1/dbase1/sysaux01.dbf
restoring datafile 00004 to /oradata2/data1/dbase1/users01.dbf
restoring datafile 00005 to /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
restoring datafile 00006 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
restoring datafile 00007 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
restoring datafile 00008 to /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19870: error reading backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19612: datafile 1 not restored due to missing or corrupt data
failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp tag=LEVEL_0_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-MAY-08
J)Recover Database:
If the current online logs contain all changes since the last backup , then you can run RECOVER DATABASE without specifying NOREDO. Otherwise you have to specify RECOVER DATABASE NOREDO.
RMAN> recover database;
Starting recover at 06-MAY-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 11 is already on disk as file /oradata2/data1/dbase1/redo01.log
archive log thread 1 sequence 12 is already on disk as file /oradata2/data1/dbase1/redo02.log
archive log thread 1 sequence 13 is already on disk as file /oradata2/data1/dbase1/redo03.log
archive log filename=/oradata2/data1/dbase1/redo01.log thread=1 sequence=11
archive log filename=/oradata2/data1/dbase1/redo02.log thread=1 sequence=12
archive log filename=/oradata2/data1/dbase1/redo03.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:12
Finished recover at 06-MAY-08
K)Open the database with RESETLOGS options.
RMAN> alter database open resetlogs;
database opened.
Related Documents:
-------------------
How to Discover or Find DBID
How to Restore Spfile from Backup
How to Restore Controlfile
Saturday, April 26, 2008
Requirements for Using Guaranteed Restore Points
1)The COMPATIBLE initialization parameter must be set to 10.2 or greater. You can issue
SQL>SHOW PARAMETER COMPATIBLE;
to see the current compatible settings.
2)The database must be running in ARCHIVELOG mode. Issue SQL>ARCHIVE LOG LIST; to see the mode.
3)A flash recovery area must be configured. To configure it look at Configure Flash Recovery Area
4)If flashback database is not enabled, then the database must be mounted, not open,when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).
SQL>SHOW PARAMETER COMPATIBLE;
to see the current compatible settings.
2)The database must be running in ARCHIVELOG mode. Issue SQL>ARCHIVE LOG LIST; to see the mode.
3)A flash recovery area must be configured. To configure it look at Configure Flash Recovery Area
4)If flashback database is not enabled, then the database must be mounted, not open,when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).
Subscribe to:
Posts (Atom)