Oracle provides different multiple options to have a “backup& recovery” as per the organization’s requirements. Incomplete recovery is one of the types of recovery. Let’s have look on the incomplete recovery.
Check the archive log status
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9
Create a test table with some records
SQL> create table time_back_1(name varchar(100)) tablespace users;
Table created.
SQL> insert into time_back_1 values('******* 1st *******');
1 row created.
SQL> insert into time_back_1 values('******* 2nd *******');
1 row created.
SQL> commit;
Commit complete.
Begin the backup mode
SQL> alter database begin backup; Database altered. D:\>copy D:\oracle\product\10.2.0\oradata\dba01\* "D:\Hot Backup" D:\oracle\product\10.2.0\oradata\dba01\CONTROL01.CTL D:\oracle\product\10.2.0\oradata\dba01\CONTROL02.CTL D:\oracle\product\10.2.0\oradata\dba01\CONTROL03.CTL D:\oracle\product\10.2.0\oradata\dba01\REDO01.LOG D:\oracle\product\10.2.0\oradata\dba01\REDO02.LOG D:\oracle\product\10.2.0\oradata\dba01\REDO03.LOG D:\oracle\product\10.2.0\oradata\dba01\SYSAUX01.DBF D:\oracle\product\10.2.0\oradata\dba01\SYSTEM01.DBF D:\oracle\product\10.2.0\oradata\dba01\TEMP01.DBF D:\oracle\product\10.2.0\oradata\dba01\TEST01.DBF D:\oracle\product\10.2.0\oradata\dba01\UNDOTBS01.DBF D:\oracle\product\10.2.0\oradata\dba01\USERS01.DBF 12 file(s) copied.
end the backup mode
SQL> alter database end backup; Database altered.
create another table
SQL> create table time_back2(name varchar(100)) tablespace users; Table created. SQL> desc time_back2; Name Null? Type ----------------------------------------- -------- -------------------- NAME VARCHAR2(100) SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2009-02-12 14:54:42
Take the SCN and drop the tables and delete datafiles
SQL> drop table time_back; Table dropped. SQL> drop table time_back2; Table dropped. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Delete Data Files. <<<<< SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247876 bytes Variable Size 75498876 bytes Database Buffers 83886080 bytes Redo Buffers 7139328 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF'
Recover the datafile
D:\>copy /Y "D:\Hot Backup\*.DBF" D:\oracle\product\10.2.0\oradata\dba01\ D:\Hot Backup\SYSAUX01.DBF D:\Hot Backup\SYSTEM01.DBF D:\Hot Backup\TEMP01.DBF D:\Hot Backup\TEST01.DBF D:\Hot Backup\UNDOTBS01.DBF D:\Hot Backup\USERS01.DBF 6 file(s) copied. SQL> recover database until time '2009-02-12 14:54:42’; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> select * from time_back; NAME --------------------------------------------------------------------- ******* 1st ******* ******* 2nd ******* SQL> desc time_back2; Name Null? Type ----------------------------------------- -------- ----------------- NAME VARCHAR2(100)
Today’s thought
“I have had all of the disadvantages required for success.” Larry Ellison
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
