RECOVERING THE DROPED TABLESPACE


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/anar/archive
Oldest online log sequence 14
Next log sequence to archive 15
Current log sequence 15

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
6 rows selected.

SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name=’USERS’;
OWNER TABLE_NAME
—————————— ——————————
TABLESPACE_NAME
——————————
AZDB TEST
USERS
TARUN TEST
USERS

SQL> alter tablespace system begin backup;
Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.

SQL> alter tablespace sysaux begin backup;
Tablespace altered.

SQL> alter tablespace rman_tbs begin backup;
Tablespace altered.

SQL> alter tablespace users begin backup;
Tablespace altered.

SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ cp * /home/oracle/backup/
[oracle@AZDB control]$ cd ..
[oracle@AZDB anar]$ cd datafiles/
[oracle@AZDB datafiles]$ cp * /home/oracle/backup/
[oracle@AZDB datafiles]$ cd
[oracle@AZDB ~]$ exit
exit

SQL> alter tablespace system end backup;
Tablespace altered.

SQL> alter tablespace undotbs1 end backup;
Tablespace altered.

SQL> alter tablespace sysaux end backup;
Tablespace altered.

SQL> alter tablespace rman_tbs end backup;
Tablespace altered.

SQL> alter tablespace users end backup;
Tablespace altered.

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

SQL> create table a2(c1 number) tablespace users;
Table created.
SQL> commit;
Commit complete.

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

SQL> set time on
16:31:04 SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.

16:31:52 SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ rm -rf *
[oracle@AZDB control]$ cd ..
[oracle@AZDB anar]$ cd datafiles/
[oracle@AZDB datafiles]$ rm -rf *
[oracle@AZDB datafiles]$ ls
[oracle@AZDB datafiles]$ cd /home/oracle/backup/
[oracle@AZDB backup]$ ls
c1.ctl c3.ctl c5.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
c2.ctl c4.ctl redo01.log rman01.dbf system01.dbf undotbs1.dbf
[oracle@AZDB backup]$ cp * /home/oracle/anar/datafiles/
[oracle@AZDB backup]$ cd /home/oracle/anar/datafiles/
[oracle@AZDB datafiles]$ ls
c1.ctl c3.ctl c5.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
c2.ctl c4.ctl redo01.log rman01.dbf system01.dbf undotbs1.dbf
[oracle@AZDB datafiles]$ mv *.ctl /home/oracle/anar/control/
[oracle@AZDB datafiles]$ ls
redo01.log rman01.dbf system01.dbf undotbs1.dbf
redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@AZDB datafiles]$ cd
[oracle@AZDB ~]$ exit
exit
16:38:05 SQL> set time off
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> recover database until cancel;
ORA-00279: change 641933 generated at 12/02/2008 16:18:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_15.arc
ORA-00280: change 641933 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 642243 generated at 12/02/2008 16:29:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_16.arc
ORA-00280: change 642243 for thread 1 is in sequence #16
ORA-00278: log file ‘/home/oracle/anar/archive/arch_1_672162639_15.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 642246 generated at 12/02/2008 16:29:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_17.arc
ORA-00280: change 642246 for thread 1 is in sequence #17
ORA-00278: log file ‘/home/oracle/anar/archive/arch_1_672162639_16.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 642248 generated at 12/02/2008 16:29:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_18.arc
ORA-00280: change 642248 for thread 1 is in sequence #18
ORA-00278: log file ‘/home/oracle/anar/archive/arch_1_672162639_17.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 642251 generated at 12/02/2008 16:29:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_19.arc
ORA-00280: change 642251 for thread 1 is in sequence #19
ORA-00278: log file ‘/home/oracle/anar/archive/arch_1_672162639_18.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 642253 generated at 12/02/2008 16:29:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_20.arc
ORA-00280: change 642253 for thread 1 is in sequence #20
ORA-00278: log file ‘/home/oracle/anar/archive/arch_1_672162639_19.arc’ no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
‘/home/oracle/anar/archive/arch_1_672162639_20.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 642253 generated at 12/02/2008 16:29:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/anar/archive/arch_1_672162639_20.arc
ORA-00280: change 642253 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
6 rows selected.

SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name=’USERS’;
OWNER TABLE_NAME
—————————— ——————————
TABLESPACE_NAME
——————————
SYS A1
USERS
SYS A2
USERS
AZDB TEST
USERS
OWNER TABLE_NAME
—————————— ——————————
TABLESPACE_NAME
——————————
TARUN TEST
USERS

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/anar/archive
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
RECOVERING THE DROPED REDOLOG GROUP AND REDO LOGFILES:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 15 10485760 1 NO CURRENT
763857 05-DEC-08
2 1 14 10485760 2 YES INACTIVE
757056 05-DEC-08
3 1 0 20971520 1 YES UNUSED
0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
4 1 0 20971520 1 YES UNUSED
0
SQL> select * from v$logfile;
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
2 ONLINE
/home/oracle/anar/datafiles/redo02.log
NO
1 ONLINE
/home/oracle/anar/datafiles/redo01.log
NO
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
3 ONLINE
/home/oracle/anar/datafiles/redo03.log
NO
4 ONLINE
/home/oracle/anar/datafiles/redo04.log
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
NO
2 ONLINE
/home/oracle/anar/datafiles/redo07.log
NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/anar/archive
Oldest online log sequence 14
Next log sequence to archive 15
Current log sequence 15
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
6 rows selected.
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.
SQL> alter tablespace sysaux begin backup;
Tablespace altered.
SQL> alter tablespace rman_tbs begin backup;
Tablespace altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ cp * /home/oracle/backup/
[oracle@AZDB control]$ cd ..
[oracle@AZDB anar]$ cd datafiles/
[oracle@AZDB datafiles]$ cp * /home/oracle/backup/
[oracle@AZDB datafiles]$ exit
exit
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter tablespace undotbs1 end backup;
Tablespace altered.
SQL> alter tablespace sysaux end backup;
Tablespace altered.
SQL> alter tablespace rman_tbs end backup;
Tablespace altered.
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> set time on
17:54:55 SQL> alter database drop logfile group 3;
Database altered.
17:56:16 SQL> set time off
SQL> !
[oracle@AZDB datafiles]$ rm -rf redo03.log
[oracle@AZDB datafiles]$ exit
exit
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ rm -rf *
[oracle@AZDB control]$ ls
[oracle@AZDB control]$ cd ../datafiles/
[oracle@AZDB datafiles]$ rm -rf *
[oracle@AZDB datafiles]$ cd /home/oracle/backup/
[oracle@AZDB backup]$ ls
c1.ctl c3.ctl c5.ctl redo02.log redo04.log rman01.dbf system01.dbf undotbs1.dbf c2.ctl c4.ctl redo01.log redo03.log redo07.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@AZDB backup]$ cp *.ctl /home/oracle/anar/control/
[oracle@AZDB backup]$ cp *.log /home/oracle/anar/datafiles/
[oracle@AZDB backup]$ cp *.dbf /home/oracle/anar/datafiles/
[oracle@AZDB backup]$ exit
exit
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;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
— 2 ONLINE
/home/oracle/anar/datafiles/redo02.log
NO
1 STALE ONLINE
/home/oracle/anar/datafiles/redo01.log
NO
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
3 ONLINE
/home/oracle/anar/datafiles/redo03.log
NO
4 ONLINE
/home/oracle/anar/datafiles/redo04.log
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
NO
2 ONLINE
/home/oracle/anar/datafiles/redo07.log
NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 15 10485760 1 YES INACTIVE
763857 05-DEC-08
2 1 14 10485760 2 YES INACTIVE
757056 05-DEC-08
3 1 16 20971520 1 NO CURRENT
785227 05-DEC-08
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
4 1 0 20971520 1 YES UNUSED
0
RECOVERING THE DROPED CONTROLFILE:
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> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/anar/control/c1.ctl
/home/oracle/anar/control/c2.ctl
/home/oracle/anar/control/c3.ctl
/home/oracle/anar/control/c4.ctl
/home/oracle/anar/control/c5.ctl
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
RMAN_TBS
USERS
6 rows selected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/anar/archive
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 18
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.
SQL> alter tablespace sysaux begin backup;
Tablespace altered.
SQL> alter tablespace rman_tbs begin backup;
Tablespace altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 813915 06-DEC-08
2 ACTIVE 813926 06-DEC-08
3 ACTIVE 813937 06-DEC-08
4 ACTIVE 813955 06-DEC-08
5 ACTIVE 813965 06-DEC-08
SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ cp * /home/oracle/backup
[oracle@AZDB control]$ cd ../datafiles/
[oracle@AZDB datafiles]$ cp * /home/oracle/backup
[oracle@AZDB datafiles]$ exit
exit
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter tablespace undotbs1 end backup;
Tablespace altered.
SQL> alter tablespace sysaux end backup;
Tablespace altered.
SQL> alter tablespace rman_tbs end backup;
Tablespace altered.
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> !
[oracle@AZDB ~]$ cd anar/control/
[oracle@AZDB control]$ rm -rf *
[oracle@AZDB control]$ ls
[oracle@AZDB control]$ exit
exit
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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
ORA-00205: error in identifying control file, check alert log for more info
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !
[oracle@AZDB ~]$ cd backup
[oracle@AZDB backup]$ cp *.ctl /home/oracle/anar/control/
[oracle@AZDB backup]$ exit
exit
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;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/home/oracle/anar/datafiles/system01.dbf’
ORA-01207: file is more recent than control file – old control file
SQL> !
[oracle@AZDB ~]$ cd /home/oracle/anar/datafiles/
[oracle@AZDB datafiles]$ rm -rf *
[oracle@AZDB datafiles]$ cd
[oracle@AZDB ~]$ cd backup
[oracle@AZDB backup]$ cp *.dbf /home/oracle/anar/datafiles/
[oracle@AZDB backup]$ cp *.log /home/oracle/anar/datafiles/
[oracle@AZDB backup]$ exit
exit
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/anar/control/c1.ctl
/home/oracle/anar/control/c2.ctl
/home/oracle/anar/control/c3.ctl
/home/oracle/anar/control/c4.ctl
/home/oracle/anar/control/c5.ctl
IMPORTANT VIEWS:
v$backup
v$datafile
v$dba_data_files
v$datafile_header
v$dba_tablespaces

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: