Skip to main content

Recovering database until time

Example of recovery database until time.


For checking after recovery I am going to create simple table with one row

 SQL> create user ulfet identified by ulfet;  
 User created.  
 SQL> grant dba to ulfet;  
 Grant succeeded.  
 SQL> conn ulfet/ulfet  
 Connected.  
 SSQL> create table test1 (id number);  
 Table created.  
 SQL> insert into test1 values(1);  
 1 row created.  
 SQL> commit;  
 Commit complete.


Now taking backup.

RMAN> backup database plus archivelog;

--note date

 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.03.34.646076 AM +04:00  
 --dropping table  
 SQL> drop table ulfet.test1;  
 Table dropped.  
 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.04.40.428868 AM +04:00  
 SQL>

--start recovery process

--firstly start database in nomount mode for restoring controlfile
RMAN> startup nomount  
 connected to target database (not started)  
 Oracle instance started  
 Total System Global Area   523108352 bytes  
 Fixed Size           1337632 bytes  
 Variable Size        314574560 bytes  
 Database Buffers       201326592 bytes  
 Redo Buffers          5869568 bytes  
 RMAN>

--drop control files

[oracle@dbserver oradata]$ cd mydb/  
 [oracle@dbserver mydb]$ ls  
 control01.ctl redo01.log redo03.log  system01.dbf undotbs01.dbf  
 example01.dbf redo02.log sysaux01.dbf temp01.dbf  users01.dbf  
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ rm control01.ctl   
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/  
 mydb/ MYDB/   
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/mydb/  
 [oracle@dbserver mydb]$ rm control02.ctl   
 [oracle@dbserver mydb]$

--Restoring controlfile from autoback;

RMAN> restore controlfile from autobackup;  
 Starting restore at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 recovery area destination: /u01/app/oracle/flash_recovery_area  
 database name (or database unique name) used for search: MYDB  
 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp found in the recovery area  
 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160113  
 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 channel ORA_DISK_1: control file restore from AUTOBACKUP complete  
 output file name=/u01/app/oracle/oradata/mydb/control01.ctl  
 output file name=/u01/app/oracle/flash_recovery_area/mydb/control02.ctl  
 Finished restore at 13-JAN-16

check if you want :)

[oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ ls control*  
 control01.ctl   
 [oracle@dbserver mydb]$ ls ../../flash_recovery_area/mydb/  
 control02.ctl

alter database in mount mode for restore and recovery process
 RMAN> alter database mount;  
 database mounted  
 released channel: ORA_DISK_1  
 RMAN>

--write commands for recovery process
 RMAN> run  
 {  
 set newname for database to '/u01/app/oracle/oradata/mydb/%b';  
 set until time "to_date('2016-01-13 11:03:34', 'yyyy-mm-dd hh24:mi:ss')";  
 restore database;  
 switch datafile all;  
 switch tempfile all;  
 recover database;  
 }2> 3> 4> 5> 6> 7> 8> 9>   
 executing command: SET NEWNAME  
 executing command: SET until clause  
 Starting restore at 13-JAN-16  
 Starting implicit crosscheck backup at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 Crosschecked 9 objects  
 Finished implicit crosscheck backup at 13-JAN-16  
 Starting implicit crosscheck copy at 13-JAN-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 13-JAN-16  
 searching for all files in the recovery area  
 cataloging files...  
 cataloging done  
 List of Cataloged Files  
 =======================  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901018889_c9cxlcbt_.bkp  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: starting datafile backup set restore  
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
 channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mydb/system01.dbf  
 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mydb/sysaux01.dbf  
 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mydb/undotbs01.dbf  
 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mydb/users01.dbf  
 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mydb/example01.dbf  
 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp  
 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp tag=TAG20160113T110002  
 channel ORA_DISK_1: restored backup piece 1  
 channel ORA_DISK_1: restore complete, elapsed time: 00:01:56  
 Finished restore at 13-JAN-16  
 Starting recover at 13-JAN-16  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc  
 archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/mydb/redo03.log  
 archived log file name=/u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc thread=1 sequence=2  
 archived log file name=/u01/app/oracle/oradata/mydb/redo03.log thread=1 sequence=3  
 media recovery complete, elapsed time: 00:00:02  
 Finished recover at 13-JAN-16

I have forgotten writing open table in resetlogs in end of command.
Now you can see, out database`s state is mounted, open it with resetlogs

SQL> conn /as sysdba  
 Connected.  
 SQL> select open_mode from v$database;  
 OPEN_MODE  
 --------------------  
 MOUNTED  
 SQL> alter database open resetlogs;  
 Database altered.

--Now try to check
SQL> conn ulfet/ulfet  
 Connected.  
 SQL> select * from tab;  
 TNAME             TABTYPE CLUSTERID  
 ------------------------------ ------- ----------  
 TEST1             TABLE  
 SQL> select * from test1;  
     ID  
 ----------  
      1  
 SQL>

That`s all



Comments

Post a Comment

Popular posts from this blog

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more