RECOVERING THE DROPED TABLE IN A SCHEMA


[oracle@anar ~]$ export ORACLE_SID=AZDB
[oracle@anar ~]$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Nov 1 11:10:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219328 bytes
Variable Size 289408256 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/AZDB/archive
Oldest online log sequence 3
Next log sequence to archive 4
Current log sequence 4

SQL> select name from v$datafile;
NAME
——————————————————————————–
/home/oracle/AZDB/datafiles/system01.dbf
/home/oracle/AZDB/datafiles/undotbs1.dbf
/home/oracle/AZDB/datafiles/sysaux01.dbf
/home/oracle/AZDB/datafiles/users01.dbf

SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/AZDB/control/c1.ctl
SQL> show parameter pfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /home/oracle/oracle/product/10
.2.0/db_1/dbs/spfileAZDB.ora

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> create user anar identified by anar
2 default tablespace users;
User created.

SQL> grant dba to anar;
Grant succeeded.

SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
—————————— ——————————
TT SYSTEM
OUTLN SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
PERFSTAT SYSAUX
ANIL USERS
anar USERS
TSMSYS SYSTEM
DIP SYSTEM
DBSNMP SYSAUX
10 rows selected.

SQL> conn anar/anar
Connected.

SQL> create table test (c1 number) tablespace users;
Table created.

SQL> begin
2 for i in 1..50 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
———-
50
SQL> commit;
Commit complete.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.

SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> alter tablespace sysaux begin backup;
Tablespace altered.
SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> exit
[oracle@anar ~]$ mkdir backup
[oracle@anar ~]$ cd AZDB/
[oracle@anar AZDB]$ ls
archive bdump control datafiles dbc.sql udump
[oracle@anar AZDB]$ cd datafiles/
[oracle@anar datafiles]$ ls
redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf
[oracle@anar datafiles]$ cp *.dbf /home/oracle/backup/
[oracle@anar datafiles]$ cd ..
[oracle@anar AZDB]$ cd control/
[oracle@anar control]$ ls
c1.ctl
[oracle@anar control]$ cp *.ctl /home/oracle/backup/
[oracle@anar control]$ cd ..
[oracle@anar AZDB]$ cd archive/
[oracle@anar archive]$ ls
arch_1_669569864_11.arc arch_1_669569864_13.arc arch_1_669569864_15.arc
arch_1_669569864_12.arc arch_1_669569864_14.arc
[oracle@anar archive]$ cp *.arc /home/oracle/backup/
[oracle@anar archive]$ cd $ORACLE_HOME/dbs
[oracle@anar dbs]$ cp initAZDB.ora /home/oracle/backup/
[oracle@anar dbs]$ cp spfileAZDB.ora /home/oracle/backup/
[oracle@anar dbs]$ cd /home/oracle/backup/
[oracle@anar backup]$ ls
arch_1_669569864_11.arc arch_1_669569864_14.arc initAZDB.ora system01.dbf users01.dbf
arch_1_669569864_12.arc arch_1_669569864_15.arc spfileAZDB.ora temp01.dbf
arch_1_669569864_13.arc c1.ctl sysaux01.dbf undotbs1.dbf

[oracle@anar backup]$ cd
[oracle@anar ~]$ exit
[oracle@anar ~]$ sqlplus "/ as sysdba";
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter tablespace sysaux end backup;
Tablespace altered.
SQL> alter tablespace undotbs1 end backup;
Tablespace altered.
SQL> alter tablespace users end backup;
Tablespace altered.

SQL> conn anar/anar
Connected.

SQL> begin
2 for i in 51..100 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.

SQL> select count(*) from test;
COUNT(*)
———-
100

SQL> set time on;

13:00:47 SQL> drop table test;
Table dropped.

13:00:58 SQL> conn sys as sysdba
Enter password:
Connected.

13:01:33 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
13:02:00 SQL> exit

[oracle@anar ~]$ cd AZDB/
[oracle@anar AZDB]$ ls
archive bdump control datafiles dbc.sql udump
[oracle@anar AZDB]$ cd control/
[oracle@anar control]$ ls
c1.ctl
[oracle@anar control]$ rm -rf c1.ctl
[oracle@anar control]$ cd ..
[oracle@anar AZDB]$ cd datafiles/
[oracle@anar datafiles]$ ls
redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@anar datafiles]$ rm -rf *.dbf
[oracle@anar ]$ cd
[oracle@anar ~]$ cd backup/
[oracle@anar backup]$ ls
arch_1_669569864_11.arc arch_1_669569864_14.arc initAZDB.ora system01.dbf users01.dbf
arch_1_669569864_12.arc arch_1_669569864_15.arc spfileAZDB.ora temp01.dbf
arch_1_669569864_13.arc c1.ctl sysaux01.dbf undotbs1.dbf

[oracle@anar backup]$ cp c1.ctl /home/oracle/AZDB/control/
[oracle@anar backup]$ cp *.dbf /home/oracle/AZDB/datafiles/
[oracle@anar backup]$ cd
[oracle@anar ~]$ sqlplus / as sysdba;
13:05:48 SQL> set time off;

SQL> startup mount;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219328 bytes
Variable Size 289408256 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> recover database until cancel;
ORA-00279: change 444975 generated at 11/01/2008 12:37:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_16.arc
ORA-00280: change 444975 for thread 1 is in sequence #16
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 445724 generated at 11/01/2008 12:48:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_17.arc
ORA-00280: change 445724 for thread 1 is in sequence #17
ORA-00278: log file ‘/home/oracle/AZDB/archive/arch_1_669569864_16.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 445727 generated at 11/01/2008 12:48:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_18.arc
ORA-00280: change 445727 for thread 1 is in sequence #18
ORA-00278: log file ‘/home/oracle/AZDB/archive/arch_1_669569864_17.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 445730 generated at 11/01/2008 12:48:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_19.arc
ORA-00280: change 445730 for thread 1 is in sequence #19
ORA-00278: log file ‘/home/oracle/AZDB/archive/arch_1_669569864_18.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 445732 generated at 11/01/2008 12:48:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_20.arc
ORA-00280: change 445732 for thread 1 is in sequence #20
ORA-00278: log file ‘/home/oracle/AZDB/archive/arch_1_669569864_19.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 445735 generated at 11/01/2008 12:48:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_21.arc
ORA-00280: change 445735 for thread 1 is in sequence #21
ORA-00278: log file ‘/home/oracle/AZDB/archive/arch_1_669569864_20.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
‘/home/oracle/AZDB/archive/arch_1_669569864_21.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database until cancel;
ORA-00279: change 445735 generated at 11/01/2008 12:48:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/AZDB/archive/arch_1_669569864_21.arc
ORA-00280: change 445735 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

SQL> conn anar/anar
Connected.

SQL> select count(*) from tab;
COUNT(*)
———-
1
SQL> select count(*) from test;
COUNT(*)
———-
100
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: