User Managed Recovery - point in time to recover(PITTR)Point-in-time recovery (PITR) in Oracle is a database recovery technique that allows you to restore a database to a specific point in time. It is a critical feature for recovering from logical errors, data corruption, or other issues that affect the database's integrity. Before PITR we should always have a backup of datafiles [oracle@tesdb TESDB]$ cd /u01/app/oracle/oradata/ [oracle@tesdb oradata]$ ll total 8 drwxr-x---. 2 oracle oinstall 4096 Oct 23 20:18 TES drwxr-x---. 2 oracle oinstall 4096 Oct 27 10:14 TESDB [oracle@tesdb TESDB]$ ll total 2537108 -rw-r--r--. 1 oracle oinstall 38 Oct 27 10:14 afiedt.buf -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control01.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control03.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control04.ctl -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo03.log -rw-r-----. 1 oracle oinstall 587210752 Oct 27 10:18 sysaux01.dbf -rw-r-----. 1 oracle oinstall 943726592 Oct 27 10:18 system01.dbf -rw-r-----. 1 oracle oinstall 33562624 Oct 25 15:35 temp01.dbf -rw-r-----. 1 oracle oinstall 356524032 Oct 27 10:18 undotbs01.dbf -rw-r-----. 1 oracle oinstall 48504832 Oct 27 10:18 users01.dbf [oracle@tesdb TESDB]$ cp * /home/oracle/backup Step 1 : Connect to user SQL> conn scott/tiger Connected. SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> select count(*) from salgrade; COUNT(*) ---------- 5 Step 2 : Create new table and insert values SQL> create table emp1 as select * from emp; Table created. SQL> insert into emp1 select * from emp; 14 rows created. SQL> / 14 rows created. SQL> commit 2 / Commit complete. Step 3 : Set timer on to note the time and insert datas SQL> set time on; 10:15:27 SQL> insert into salgrade select * from salgrade; 5 rows created. 10:17:37 SQL> / 10 rows created. 10:17:40 SQL> / 20 rows created. 10:17:41 SQL> commit; Commit complete. Step 4 : Connect as sys user to switch logs 10:17:43 SQL> conn / as sysdba Connected. 10:17:56 SQL> alter system switch logfile; System altered. 10:18:09 SQL> / System altered. Step 5 : Shut the database 10:18:13 SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. . 10:19:20 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Step 6 : Remove the datafiles [oracle@tesdb TESDB]$ cd /u01/app/oracle/oradata/TESDB [oracle@tesdb TESDB]$ ll total 2537108 -rw-r--r--. 1 oracle oinstall 38 Oct 27 10:14 afiedt.buf -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control01.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control03.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control04.ctl -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo03.log -rw-r-----. 1 oracle oinstall 587210752 Oct 27 10:18 sysaux01.dbf -rw-r-----. 1 oracle oinstall 943726592 Oct 27 10:18 system01.dbf -rw-r-----. 1 oracle oinstall 33562624 Oct 25 15:35 temp01.dbf -rw-r-----. 1 oracle oinstall 356524032 Oct 27 10:18 undotbs01.dbf -rw-r-----. 1 oracle oinstall 48504832 Oct 27 10:18 users01.dbf [oracle@tesdb TESDB]$ ll total 2537108 -rw-r--r--. 1 oracle oinstall 38 Oct 27 10:14 afiedt.buf -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control01.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control03.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control04.ctl -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo03.log -rw-r-----. 1 oracle oinstall 587210752 Oct 27 10:18 sysaux01.dbf -rw-r-----. 1 oracle oinstall 943726592 Oct 27 10:18 system01.dbf -rw-r-----. 1 oracle oinstall 33562624 Oct 25 15:35 temp01.dbf -rw-r-----. 1 oracle oinstall 356524032 Oct 27 10:18 undotbs01.dbf -rw-r-----. 1 oracle oinstall 48504832 Oct 27 10:18 users01.dbf [oracle@tesdb TESDB]$ rm -rf *.dbf [oracle@tesdb TESDB]$ ll total 645472 -rw-r--r--. 1 oracle oinstall 38 Oct 27 10:14 afiedt.buf -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control01.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control03.ctl -rw-r-----. 1 oracle oinstall 10600448 Oct 27 10:18 control04.ctl -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo01.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Oct 27 10:18 redo03.log Step 7 : Copy the datafiles from the backup [oracle@tesdb TESDB]$ cp /home/oracle/backup/*.dbf /u01/app/oracle/oradata/TESDB/ Step 8: start the database in mount stage [oracle@tesdb TESDB]$ export ORACLE_SID=tesdb [oracle@tesdb TESDB]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 27 10:36:28 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 1157627168 bytes Fixed Size 8895776 bytes Variable Size 301989888 bytes Database Buffers 838860800 bytes Redo Buffers 7880704 bytes Database mounted. Step 9 : Recover the databse using control file by mentioning the time we last inserted data SQL> recover database using backup controlfile until time '2023/10/27/10:15:27'; Media recovery complete. Step 10 : Open database with restlogs option SQL> alter database open resetlogs; Database altered. Step 11: conn as SCOTT user and check for table data SQL> conn scott/tiger Connected. SQL> select * from emp;
« Previous Next Topic » (RMAN Backup - Configuration) |