Scenario: To restore to a certain point in time (point in time recovery).
A full backup was made at 13:40. The emp1355 table was created at 13:55.
|
1 2 |
SQL> create table hr.emp1355 as select * from hr.employees 2 where 1=2; |
Enter some data (the constraints may have to be deleted)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> declare 2 begin 3 for aa in 1..10 LOOP 4 insert into hr.emp1355 (employee_id, last_name, first_name, department_id, salary) 5 values (192100 + aa, 'mehmet', 'Derviş', 1, 112600 + aa); 6 commit; 7 end loop ; 8 exception 9 when others then 10 rollback; 11 end; / SQL> commit ; |
To check
|
1 2 3 4 |
SQL> select count(*) from hr.emp1355 ; COUNT(*) ============ 10 |
Created the emp1400 table at 14:00
|
1 2 |
SQL> create table hr.emp1400 as select * from hr.employees 2 where 1=2; |
At 13:56 we want to execute a Point In Time Recovery.
- The database is shutdown and datafiles are deleted.
- There is the full backup that was made at 13:40.
- Datafiles will be restored from rman backup and then recovered from archivelogs that were generated between 13:40 and 13:56.
|
1 2 3 4 5 |
SQL> rman target / SQL> startup mount ; SQL> restore database ; SQL> recover database until time "to_date('31/05/2011 13:56:00','DD/MM/YYYY HH24:MI:SS')"; SQL> alter database open resetlogs; |
Restore is used to return to the moment that the backup was made.
Recover is then used to bring the archive logs to the last moment.
To check
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> desc hr.emp1355 Name Null? Type ----------------------- EMPLOYEE_ID NUMBER(10) FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(25) EMAIL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE DATE JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) |
|
1 2 3 |
SQL> desc hr.emp1400 ERROR: ORA-04043: object hr.emp1400 does not exist |
Setting the database so that it is possible to recover to 3 days ago
|
1 |
RMAN> recovery window of 3 days, |
Requires configuration. The backups don’t get deleted regardless of the number of backups that have been made.
Alternatively, we need the archive logs from the past 3 days.
|
1 |
RMAN> retention policy to redundancy 1 ; |
The archive logs made by the backup process are used for recovery.
Using RMAN for...
12 March 2019