INCOMPLETE RECOVERY IN ARCHIVELOG MODE

1. User creation:
Create user kk identified by kk.
2. You check Archive or noarchivelog modelist If it is archivelog mode you change to noarchivelog mode :
Alter database noarchivelog;
3. Create new tablespace :
Create tablespace abc datafile’d:\app\oracle\oradata\orcl\abc.dbf’ size 50m;
4. Allocate space for user:
Alter user kk identified by kk default tablespace abc quota unlimited on abc account unlock;
5. Grant sessions to the users:
Grant create session ,create any table ,select any table to kk;
6. To connect the user:
Conn kk/kk
7. Create emp table and insert the values into the table.
8. Give the commit statement
9. Count the values:
Select count(*) from emp;
10.shutdown immediate
11.startup nomount
12. Backup the database:
Host copy D:\app\oracle\oradata\orcl\*.* E:\backup\ 13. alter database mount;
14. alter database open;
15. conn kk/kk
16. insert the values into the emp table
17. commit;
18. select count(*) from emp;
19. shutdown immediate
20. open the datafile abc and corrupt by physically D :\app\oracle\oradata\orcl\abc.dbf 21.open the above abc notepad ,press ctl+a delete all and then save it as same name in same place
22. startup
23. shutdown immediate
24.host copy E:\backup\*.* D:\app\oracle\oradata\orcl\
25. startup
26.conn kk/kk
27. select count(*) from emp;

COMPLETE RECOVERY IN NOARCHIVELOG MODE

1. User creation:
Create user kk identified by kk.
2. You check Archive or noarchivelog mode:
Archive log list If it is archivelog mode you change to noarchivelog mode:
Alter database noarchivelog;
3. Create new tablespace :
Create tablespace abc datafile’d:\app\oracle\oradata\orcl\abc.dbf’ size 50m;
4. Allocate space for user:
Alter user kk identified by kk default tablespace abc quota unlimited on abc account unlock;
5. Grant sessions to the users:
Grant create session ,create any table ,select any table to kk;
6. To connect the user:
Conn kk/kk
7. Create emp table and insert the values into the table.
8. Give the commit statement
9. Count the values:
Select count(*) from emp;
10.shutdown immediate
11.startup nomount
12. Backup the database:
Host copy D:\app\oracle\oradata\orcl\*.* E:\backup1\
13. alter database mount;
14. alter database open;
15. conn kk/kk
16. insert the values into the emp table
17. commit;
18. select count(*) from emp;
19. shutdown immediate
20. open the datafile abc and corrupt by physically D :\app\oracle\oradata\orcl\abc.dbf 21.open the above abc notepad ,press ctl+a delete all and then save it as same name in same place
22. startup mount
23. host copy E:\backup1\*.* D:\app\oracle\oradata\orcl\
24.select * from v$recover_file;
25. set auto recovery on
26. recover datafile 5;
27. select * from v$recover_file;
28. alter database open;
29.conn kk/kk 30 select count(*) from emp;

COMPLETE RECOVERY IN ARCHIVE LOG

=>datafiles are in open
=>database in open mode
=>no backup
=>create tablespace abc datafile’d:\app\oracle\oradata\orcl\abc.dbf’ size 50m;
=>create user prabu identified by prabu default tablespace abc quota unlimited on abc account unlock;
=>archived log list
=>select tablespace_name ,file_name from dba_data_files where tablespace_name=’ABC’;
=>select username ,default_tablespace from dba_users;
=>grant create session ,create any table,select any table to prabu;
=>conn prabu/prabu
=>create table test tablespace abc as select * from scott.emp;
=>desc dba_tables
=>select table_name ,table space_name from dba_tables where tablespace_name=’ABC’; =>select name from v$datafile;
=>select * from v$recover_file;
=>alter system switch logfile;
=>/ press enter key
=>/ press enter key
=>select * from v$recover_file;
=>alter database datafile 5 offline;
=>alter database create datafile’d:\app\oracle\oradata\orcl\abc.dbf’ size 50m;
=>select * from v$recover_file;
=>select * from v$recover_log;
=>recover automatic datafile 5;
=>select * from v$recover_file;
=>alter database datafile 5 online;
=>alter database open resetlogs;
commit and uncommitted statements can be recover.