Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » RDBMS Server » Backup & Recovery » What is wrong? Open DB recover (ora9i, Windows)
icon5.gif  What is wrong? Open DB recover [message #335483] Tue, 22 July 2008 05:29 Go to next message
in.lukfai
Messages: 6
Registered: July 2008
Location: India
Junior Member
What is wrong?

I was doing practice about User-Manages Restore and Recovery. The scenario is:

- I have full backup(close database)
- I ran database in Archive mode

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\arc
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

- I have 2 tables:


SQL> select table_name,tablespace_name from user_tables where tablespace_name <> 'SYSTEM';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES USERDATA2
USER1 USER2_TPSP1

SQL> select t.name,d.name from v$tablespace t,v$datafile d where t.ts#=d.ts# and t.name like '%USER%';

NAME NAME
---------------------------------------------------------------------------------------------------------------
USERDATA2 C:\ORACLE\ORADATA\KATIE3\USERDATA2.DBF
USER2 C:\ORACLE\ORADATA\KATIE3\USER2.DBF
USER2_TPSP1 C:\ORACLE\ORADATA\KATIE3\U2_TBSP1.DBF


3 rows selected.

So the summary table would be:

TABLE_NAME TABLESPACE_NAME DATAFILE
------------------------------ ------------------------------
EMPLOYEES USERDATA2 C:\ORACLE\ORADATA\KATIE3\USERDATA2.DBF
USER1 USER2_TPSP1 C:\ORACLE\ORADATA\KATIE3\U2_TBSP1.DBF

- table employees has contents;
SQL> select employee_id from employees;

EMPLOYEE_ID
-----------
1
2
3
4
5
6
7
8
9
10
11

EMPLOYEE_ID
-----------
12

12 rows selected.


- I would like to drop table employees and then wanted to perform Recovering Open database so I did as the step below:

1. drop table employees:

SQL> drop table employees;

Table dropped.

SQL> select table_name,tablespace_name from user_tables where tablespace_name <> 'SYSTEM';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
USER1 USER2_TPSP1


2. Bring tablespace userdata2 offline:

SQL> alter tablespace userdata2 offline;

Tablespace altered.

3. Restored userdata2.dbf from backup.

4. Issued recover command:
SQL> recover automatic tablespace userdata2;
Media recovery complete.

5. Bring tablespace userdata2 online
SQL> alter tablespace userdata2 online;

Tablespace altered.

6. Checked whether table employees has been restored:

SQL> select * from employees;
select * from employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

That is the problem. I expected that table employees should be there but I could not find it in database. What is wrong in my part? Or I misconcept.
However, then I tried again by shutdown the database and then restore all files from backup and then open database. Everything is fine. The table employees
is in database. I don't understand. The purpose of this practice was for Recovery an Open database in archive mode. Could you please tell me what is wrong?
I read about TSPITR or the scenario is different I have to do TSPITR instead. Please advise..


Report message to a moderator
Re: What is wrong? Open DB recover [message #335490 is a reply to message #335483 ] Tue, 22 July 2008 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 19111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Quote:
That is the problem. I expected that table employees should be there but I could not find it in database. What is wrong in my part?

You recover the tablespace INCLUDING the drop statement.

Quote:
However, then I tried again by shutdown the database and then restore all files from backup and then open database.

In this case, you just restored and not recovered so database is at the time of your backup BEFORE the drop.

Regards
Michel
Report message to a moderator
Re: What is wrong? Open DB recover [message #335496 is a reply to message #335490 ] Tue, 22 July 2008 05:57 Go to previous messageGo to next message
in.lukfai
Messages: 6
Registered: July 2008
Location: India
Junior Member
[quote title=Michel Cadot wrote on Tue, 22 July 2008 16:13]
Quote:
You recover the tablespace INCLUDING the drop statement.


I do think so. So what is the best way to recover this scenario?
I spinned around like anything.

Regards,

Katie
Report message to a moderator
Re: What is wrong? Open DB recover [message #335504 is a reply to message #335496 ] Tue, 22 July 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 19111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Quote:
So what is the best way to recover this scenario?

You already have the answer: TSPITR which needs to have the space for another database.

Regards
Michel
Report message to a moderator
Re: What is wrong? Open DB recover [message #335899 is a reply to message #335504 ] Thu, 24 July 2008 02:15 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
When u drop table accidently then u can recove it from recycle bin in oracle 10g .... Shocked

Unless and until u have not purged your recycle bin ...

Note :- its recycle bin of oracle not windows o/s Very Happy

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE


Now, we accidentally drop the table:


SQL> drop table recycletest;

Table dropped.


Let's check the status of the table now.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE


=============================================================

PURGE RECYCLEBIN;


But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:

DROP TABLE RECYCLETEST PURGE;


This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.




[Updated on: Thu, 24 July 2008 02:19]

Report message to a moderator
Re: What is wrong? Open DB recover [message #335913 is a reply to message #335899 ] Thu, 24 July 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 19111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
1/ OP clearly stated he is in 9i, why replying with a 10g specific anser?

2/ Don't use IM speak as stated in forum guidelines (please read them).

Regards
Michel
Report message to a moderator
Re: What is wrong? Open DB recover [message #336593 is a reply to message #335483 ] Mon, 28 July 2008 05:42 Go to previous message
in.lukfai
Messages: 6
Registered: July 2008
Location: India
Junior Member
Thanks Michel, I am more clear now..After user recover topic then I move on to RMAN.. Smile
Report message to a moderator
Previous Topic:Is it possible to recover with only users datafile
Next Topic:sql query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ ]

Current Time: Sun Sep 7 05:52:35 CDT 2008

Total time taken to generate the page: 0.00991 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.